select ARFId,RecBillNo,FALLAMOUNTFOR,
SUM(FCURWRITTENOFFAMOUNTFOR) as FCURWRITTENOFFAMOUNTFOR,
SUM(FNRECEIPTAMOUNT) as FNRECEIPTAMOUNT,
SUM(FCHARGEOFFAMOUNT) AS FCHARGEOFFAMOUNT,FCHARGEOFFBILLNO
from
(select AR.FID AS ARFId,PML.FTARGETBILLNO as RecBillNo,AR.FALLAMOUNTFOR,
CASE WHEN AR.FALLAMOUNTFOR < 0 then -1 * abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0)))
else abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) end as FCURWRITTENOFFAMOUNTFOR,
case when AR.FWRITTENOFFSTATUS = 'C' then 0 else AR.FALLAMOUNTFOR - SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0)) end FNRECEIPTAMOUNT,
0 AS FCHARGEOFFAMOUNT,N'' AS FCHARGEOFFBILLNO
from T_AR_RECMACTHLOGENTRY PML INNER JOIN T_AR_RECMacthLog PM ON PM.FID = PML.FID And PM.FISACTIVATION<>'0'
INNER JOIN T_AR_RECEIVABLE AR ON AR.FID=PML.FSRCBILLID WHERE PML.FSOURCEFROMID='AR_receivable' AND (PML.FTARGETFROMID='AR_RECEIVEBILL' OR PML.FTARGETFROMID='AR_REFUNDBILL')
/*and(exists(Select 1 from T_AR_RECEIVABLEENTRY ARE inner join {0} Flow on Flow.FRECID=ARE.FENTRYID WHERE ARE.FID=AR.FID union all select 1 from T_AR_BillingMatchLogENTRY blog inner join {0} Flow on Flow.FRECID=blog.FTARGETENTRYID inner join T_AR_RECMACTHLOGENTRY PML on blog.FSRCBillId=PML.FSRCBillId where blog.FSOURCEFROMID='AR_receivable' and blog.FTARGETFROMID='AR_receivable'))*/
group by AR.FID,PML.FTARGETBILLNO,AR.FALLAMOUNTFOR,AR.FWRITTENOFFSTATUS
Union All --应收正 - 应收负的特殊核销
select AR.FID AS RecId,N'' as RecBillNo,AR.FALLAMOUNTFOR, 0 as FCURWRITTENOFFAMOUNTFOR,0 AS FNRECEIPTAMOUNT, CASE WHEN AR.FALLAMOUNTFOR < 0 then -1 * abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) else abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) end as FCHARGEOFFAMOUNT,PML.FSRCBILLNO AS FCHARGEOFFBILLNO from T_AR_RECMACTHLOGENTRY PML INNER JOIN T_AR_RECMacthLog PM ON PM.FID = PML.FID And PM.FISACTIVATION<>'0' INNER JOIN T_AR_RECEIVABLE AR ON AR.FID=PML.FTARGETBILLID WHERE PML.FTARGETFROMID='AR_receivable' AND PML.FSOURCEFROMID = 'AR_receivable'
/*and exists(Select 1 from T_AR_RECEIVABLEENTRY ARE inner join {0} Flow on Flow.FRECID=ARE.FENTRYID WHERE ARE.FID=AR.FID )*/
group by AR.FID,PML.FSRCBILLNO,AR.FALLAMOUNTFOR,AR.FWRITTENOFFSTATUS
Union All
--除了应收正 - 应收负的特殊核销[为了便于维护，此处不与上面（应收正，应收付核销条件）合并]
select AR.FID AS RecId,N'' as RecBillNo,AR.FALLAMOUNTFOR, 0 as FCURWRITTENOFFAMOUNTFOR,0 AS FNRECEIPTAMOUNT,CASE WHEN AR.FALLAMOUNTFOR < 0 then -1 * abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) else abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) end as FCHARGEOFFAMOUNT,PML.FSRCBILLNO AS FCHARGEOFFBILLNO from T_AR_RECMACTHLOGENTRY PML INNER JOIN T_AR_RECMacthLog PM ON PM.FID = PML.FID And PM.FISACTIVATION<>'0' INNER JOIN T_AR_RECEIVABLE AR ON AR.FID=PML.FTARGETBILLID WHERE PML.FTARGETFROMID='AR_receivable' AND PML.FSOURCEFROMID <> 'AR_receivable' AND PML.FSOURCEFROMID <> 'AR_RECEIVEBILL' AND PML.FSOURCEFROMID<>'AR_REFUNDBILL'
/*and exists(Select 1 from T_AR_RECEIVABLEENTRY ARE inner join {0} Flow on Flow.FRECID=ARE.FENTRYID WHERE ARE.FID=AR.FID) */
--预收款做特殊核销时，应收冲预收时不纳入冲销金额
AND Not Exists (SELECT 1 FROM T_AR_MATCKENTRY TARME INNER JOIN T_AR_RECMACTHLOGENTRY TARR ON TARR.FSRCROWID = TARME.FENTRYID AND TARR.FSOURCETYPE = 'b9b2335770b84a3aa9b09b22767cd7e3' WHERE PML.FENTRYID=TARR.FENTRYID AND TARME.FMATCHTYPE = '3' ) group by AR.FID,PML.FSRCBILLNO,AR.FALLAMOUNTFOR,AR.FWRITTENOFFSTATUS
Union All --应付与应收匹配核销
select AR.FID AS RecId,N'' as RecBillNo,AR.FALLAMOUNTFOR, 0 as FCURWRITTENOFFAMOUNTFOR,0 AS FNRECEIPTAMOUNT, CASE WHEN AR.FALLAMOUNTFOR < 0 then -1 * abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) else abs(SUM(ISNULL(PML.FCURWRITTENOFFAMOUNTFOR,0))) end as FCHARGEOFFAMOUNT,PML.FSRCBILLNO AS FCHARGEOFFBILLNO from T_AP_PAYMATCHLOGENTRY PML INNER JOIN T_AP_PAYMATCHLOG PM ON PM.FID = PML.FID And PM.FISACTIVATION<>'0' INNER JOIN T_AR_RECEIVABLE AR ON AR.FID=PML.FTARGETBILLID WHERE PML.FTARGETFROMID='AR_receivable'
/*and exists(Select 1 from T_AR_RECEIVABLEENTRY ARE inner join {0} Flow on Flow.FRECID=ARE.FENTRYID WHERE ARE.FID=AR.FID) */
group by AR.FID,PML.FSRCBILLNO,AR.FALLAMOUNTFOR,AR.FWRITTENOFFSTATUS
--预收款处理
Union All select ARM2.ARFID,ARM1.FSRCBILLNO as RecBillNo,ARM2.FALLAMOUNTFOR, sum(ISNULL(ARM1.FMATCHAMOUNTFOR,0)) as FCURWRITTENOFFAMOUNTFOR, 0 AS FNRECEIPTAMOUNT, 0 as FCHARGEOFFAMOUNT,N'' as FCHARGEOFFBILLNO from T_AR_MatckEntry ARM1 inner join (select DISTINCT ARMat.FID,ARMat.FSRCBILLID as ARFID,ARMat.FSRCBILLNO as ARBillNO,ar.FALLAMOUNTFOR from T_AR_MatckEntry ARMat inner join T_AR_RECEIVABLE ar on ARMat.FSRCBILLID=ar.FID and FMATCHTYPE='3' and FPURPOSEID=0) ARM2 on ARM1.FID=arm2.FID where ARM1.FPURPOSEID=20011 and FMATCHTYPE='3'
/*and exists(Select 1 from T_AR_RECEIVABLEENTRY ARE inner join {0} Flow on Flow.FRECID=ARE.FENTRYID WHERE ARE.FID=ARM2.ARFID )*/
group by ARM2.ARFID,ARM1.FSRCBILLNO,ARM2.FALLAMOUNTFOR
--追加最新一种场景，预收款1200，订单1200，应收1000，开票1200，应收-开票核销1000，产生200应收调整单，应收调整单200与预收款1200核销200 at 20170314 by CXF\r\n
UNION ALL SELECT AR.FID as ARFID, N'' RECBILLNO, AR.FALLAMOUNTFOR,0 FCURWRITTENOFFAMOUNTFOR,0 FNRECEIPTAMOUNT, SUM(ISNULL(RME.FCURWRITTENOFFAMOUNTFOR, 0)) FCHARGEOFFAMOUNT, RME.FSRCBillNo FCHARGEOFFBILLNO FROM T_AR_BILLINGMATCHLOGENTRY BME inner join t_ar_receivablePlan rcp on rcp.FId=BME.FSrcBillId INNER JOIN T_AR_RECMACTHLOGENTRY RME on rcp.FEntryId=RME.FTargetEntryId INNER JOIN T_AR_RECEIVABLE AR on AR.FID=BME.FTargetBillId /*INNER JOIN {0} Flow on Flow.FRecid=BME.FTargetEntryId*/ Where BME.FTargetFromid='AR_receivable' AND BME.FIsadiBill='1' and RME.Ftargetfromid='AR_receivable' AND RME.Fisadibill ='1' GROUP BY AR.FID, RME.FSRCBILLNO, AR.FALLAMOUNTFOR, AR.FWRITTENOFFSTATUS)
TMatch Group by TMatch.ARFId,TMatch.RecBillNo,TMatch.FALLAMOUNTFOR,FCHARGEOFFBILLNO;
----查询所有的应收单收款、未结算等信息
