(FAQ)应收余额应付余额查询

【问题】: (FAQ)应收余额应付余额查询
版本: 7.5
模块: 应收管理
【答复】:
insert into VT10UFCD28X61B5WUACT7SV1V (FCompanyId, FCustomerId, FDebit, FCredit, FBalance) select tSum.FCompanyId FCompanyId, tSum.FCustomerId FCustomerId, sum(tSum.FDebit) FDebit, sum(tSum.FCredit) FCredit, sum(tSum.FBalance) FBalance from ( select t1.FCompanyId FCompanyId, t1.FAsstActId FCustomerId, sum(t1.FBeginBalanceLocal) FDebit, 0 FCredit, sum(t1.FBeginBalanceLocal) FBalance from t_ap_apBalance t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId and t1.FPeriodId = t2.FCurrentPeriodId where 1=1 and t1.FAsstActId in ('往来户ID') and t1.FBalType = 70 group by t1.FCompanyId, t1.FAsstActId union all select t1.FCompanyId FCompanyId, t1.FAsstActId FCustomerId, 0 FDebit, sum(t1.FBeginBalanceLocal) FCredit, 0-sum(t1.FBeginBalanceLocal) FBalance from t_ar_arBalance t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId and t1.FPeriodId = t2.FCurrentPeriodId where t1.FAsstActId in ('往来户ID') and t1.FBalType = 80 group by t1.FCompanyId, t1.FAsstActId ) tSum group by tSum.FCompanyId, tSum.FCustomerId insert into VT10UFCD28X61B5WUACT7SV1V (FCompanyId, FCustomerId, FDebit, FCredit, FBalance) select tBiz.FCompanyId FCompanyId, tBiz.FCustomerId FCustomerId, 0 FDebit, sum(tBiz.FCredit)-sum(tBiz.FDebit) FCredit, sum(tBiz.FAmt) FBalance from ( SELECT t1.FCompanyId, t1.FAsstActID FCustomerId, t1.FNumber, 0 FDebit, t1.FAmountLocal FCredit, 0-t1.FAmountLocal FAmt FROM T_AR_OTHERBILL t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and t1.FBillStatus = 3 and FAsstActID in ('往来户ID') and FBillDate >= t2.FBeginDate AND t1.FIsBizBill = 0 union all SELECT t1.FCompanyId, t1.FPayerID FCustomerId, t1.FNumber, ISNULL(t1.FLocalAmount,0) FDebit, 0 FCredit, t1.FLocalAmount FAmt FROM T_CAS_ReceivingBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and t1.FBillStatus >= 14 and t1.FSourceType = 100 and FPayerID in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussentID FCustomerId, t1.FNumber, ISNULL(t1.FBadAmountsLocal,0) FDebit, 0 FCredit, t1.FBadAmountsLocal FAmt FROM T_AR_BadAcct t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and t1.FAuditorID is not null and FAcctCussentID in ('往来户ID') and FBadAcctDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Main FCredit, 0-t1.FThisVerificateAmtLoc_Main FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 111 or t1.FVerificationType = 112 ) and t1.FIsSameCurrency = 1 and t1.FIsSameCode = 1) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, ISNULL(t1.FThisVerificateAmtLoc_Main,0) FDebit, t1.FThisVerificateAmtLoc_Main FCredit, 0 FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 111 or t1.FVerificationType = 112 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Second FCredit, 0-t1.FThisVerificateAmtLoc_Second FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 111 or t1.FVerificationType = 112 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, ISNULL(t1.FThisVerificateAmtLoc_Main,0) FDebit, 0 FCredit, t1.FThisVerificateAmtLoc_Main FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 or t1.FVerificationType = 103 or t1.FVerificationType = 109) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Second FCredit, 0-t1.FThisVerificateAmtLoc_Second FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 101 or t1.FVerificationType = 107 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Main FCredit, 0-t1.FThisVerificateAmtLoc_Main FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, ISNULL(t1.FThisVerificateAmtLoc_Second,0) FDebit, 0 FCredit, t1.FThisVerificateAmtLoc_Second FAmt FROM T_AP_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 203 or t1.FVerificationType = 209 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Second FCredit, 0-t1.FThisVerificateAmtLoc_Second FAmt FROM T_AP_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all select A.FCompanyId FCompanyId, B.FAsstActId FCustomerId, A.FNumber, ISNULL(A.FAmountLocal,0) FDebit, 0 FCredit, A.FAmountLocal FAmt From T_AR_OTHERBILL A INNER JOIN T_AR_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FAsstActId in ('往来户ID') and A.FBillDate >= t2.FBeginDate and B.FBillStatus = 3 and (A.FIsReverseBill = 0 and A.FIsTransBill = 1 and A.FBillStatus = 3 ) union all select A.FCompanyId FCompanyId, B.FPayerID FCustomerId, A.FNumber, 0 FDebit, A.FLocalAmount FCredit, 0-A.FLocalAmount FAmt From T_CAS_ReceivingBill A INNER JOIN T_CAS_ReceivingBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FPayerID in ('往来户ID') and A.FBizDate >= t2.FBeginDate and B.FSourceType= 100 and B.FBillStatus>= 14 AND A.FSourceType= 100 and A.FBillStatus>= 14 and A.FIsTransBill = 1 union all select A.FCompanyId FCompanyId, B.FAsstActID FCustomerId, A.FNumber, ISNULL(A.FLocalAmount,0) FDebit, 0 FCredit, A.FLocalAmount FAmt From T_CAS_PaymentBill A INNER JOIN T_AR_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FAsstActID in ('往来户ID') and A.FBizDate >= t2.FBeginDate and A.FSourceType= 101 and A.FBillStatus>= 15 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1 ) tBiz group by tBiz.FCompanyId, tBiz.FCustomerId insert into VT10UFCD28X61B5WUACT7SV1V (FCompanyId, FCustomerId, FDebit, FCredit, FBalance) select tBiz.FCompanyId FCompanyId, tBiz.FCustomerId FCustomerId, sum(tBiz.FDebit)-sum(tBiz.FCredit) FDebit, 0 FCredit, sum(tBiz.FAmt) FBalance from ( SELECT t1.FCompanyId, t1.FAsstActID FCustomerId, t1.FNumber, t1.FAmountLocal FDebit, 0 FCredit, t1.FAmountLocal FAmt FROM T_AP_OTHERBILL t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and t1.FBillStatus = 3 and FAsstActID in ('往来户ID') and FBillDate >= t2.FBeginDate AND t1.FIsBizBill = 0 union all SELECT t1.FCompanyId, t1.FPayeeID FCustomerId, t1.FNumber, 0 FDebit, t1.FLocalAmount FCredit, 0-t1.FLocalAmount FAmt FROM T_CAS_PaymentBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and t1.FBillStatus >= 15 and t1.FSourceType = 101 and FPayeeID in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Main FCredit, 0-t1.FThisVerificateAmtLoc_Main FAmt FROM T_AP_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 or t1.FVerificationType = 203 or t1.FVerificationType = 209) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, t1.FThisVerificateAmtLoc_Second FDebit, 0 FCredit, t1.FThisVerificateAmtLoc_Second FAmt FROM T_AP_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 201 or t1.FVerificationType = 207 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Main FCustomerId, t1.FNumber, t1.FThisVerificateAmtLoc_Main FDebit, 0 FCredit, t1.FThisVerificateAmtLoc_Main FAmt FROM T_AP_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 204 or t1.FVerificationType = 210 or t1.FVerificationType = 211 or t1.FVerificationType = 212) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Main in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, 0 FDebit, t1.FThisVerificateAmtLoc_Second FCredit, 0-t1.FThisVerificateAmtLoc_Second FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 103 or t1.FVerificationType = 109 ) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all SELECT t1.FCompanyId, t1.FAcctCussID_Second FCustomerId, t1.FNumber, t1.FThisVerificateAmtLoc_Second FDebit, 0 FCredit, t1.FThisVerificateAmtLoc_Second FAmt FROM T_AR_VerificationBill t1 inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on t1.FCompanyId = t2.FCompanyId where 1=1 and ((t1.FVerificationType = 104 or t1.FVerificationType = 110 or t1.FVerificationType = 113 or t1.FVerificationType = 114) and ((t1.FIsSameCode = 1 AND t1.FIsSameCurrency = 0) or t1.FIsSameCode = 0)) and FAcctCussID_Second in ('往来户ID') and FBizDate >= t2.FBeginDate union all select A.FCompanyId FCompanyId, B.FAsstActId FCustomerId, A.FNumber, 0 FDebit, A.FAmountLocal FCredit, 0-A.FAmountLocal FAmt From T_AP_OTHERBILL A INNER JOIN T_AP_OTHERBILL B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FAsstActId in ('往来户ID') and A.FBillDate >= t2.FBeginDate and B.FBillStatus = 3 and (A.FIsReverseBill = 0 and A.FIsTransBill = 1 and A.FBillStatus = 3 ) union all select A.FCompanyId FCompanyId, B.FPayeeID FCustomerId, A.FNumber, 0 FDebit, A.FLocalAmount FCredit, 0-A.FLocalAmount FAmt From T_CAS_PaymentBill A INNER JOIN T_CAS_PaymentBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FPayeeID in ('往来户ID') and A.FBizDate >= t2.FBeginDate and B.FSourceType= 101 and B.FBillStatus>= 15 AND A.FSourceType= 101 and A.FBillStatus>= 15 and A.FIsTransBill = 1 union all select A.FCompanyId FCompanyId, B.FAsstActID FCustomerId, A.FNumber, 0 FDebit, A.FLocalAmount FCredit, 0-A.FLocalAmount FAmt From T_CAS_ReceivingBill A INNER JOIN T_AP_OtherBill B ON A.FSourceBillID = B.FID and A.FCompanyId = B.FCompanyId inner join (select t1.FCompanyId, t1.FCurrentPeriodId, t4.FBeginDate from T_BD_SystemStatusCtrol t1 left join T_BD_SystemStatus t2 on t1.FSystemStatusId = t2.FId left join t_org_company t3 on t1.FCompanyId = t3.FId left join t_bd_period t4 on t1.FCurrentPeriodId = t4.FId where t3.FId in ('公司ID') and t2.FName = 13 and t1.FCurrentPeriodId is not null) t2 on A.FCompanyId = t2.FCompanyId and B.FCompanyId = t2.FCompanyId where 1=1 and A.FCurrencyId = B.FCurrencyId and B.FAsstActID in ('往来户ID') and A.FBizDate >= t2.FBeginDate and A.FSourceType= 100 and A.FBillStatus>= 14 AND B.FBillStatus= 3 and A.FIsTransOtherBill = 1 ) tBiz group by tBiz.FCompanyId, tBiz.FCustomerId