SAP business one查询报表-应收发票
SELECT '应收发票' as 单据类型,T0.[U_SS_InvoNo] ,t0.docnum,T0.[docentry], T0.[DocType], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[U_SO001], T0.[NumAtCard] 合约号
,T0.[CardName], T0.[DocTotal]-T0.[VatSumSy] as 不含税,T0.[VatSumSy] ,T0.[DocTotal] ,t0.U_kpno_1,t0.U_KPDATE_1,t0.U_kpamounts_1,t0.U_kpno_2,t0.U_KPDATE_2,t0.U_kpamounts_2,t0.U_kpno_3,t0.U_KPDATE_3,t0.U_kpamounts_3,t0.U_kpno_4,t0.U_KPDATE_4,t0.U_kpamounts_4,(T0.[DocTotal]-isnull(t0.U_kpamounts_1,0)-isnull(t0.U_kpamounts_2,0)-isnull(t0.U_kpamounts_3,0)-isnull(t0.U_kpamounts_4,0)) as 未开票金额, CASE A.[BaseType] WHEN '13' THEN '贷项' when '16' then '贷项' end 上下标识类型 , A.DocNum 上下标识单号
FROM OINV T0 left join(SELECT T0.[DocNum] ,T1.[BaseRef] , T1.[BaseType] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] where T1.[BaseType] in ('13','16') group by T0.[DocNum] ,T1.[BaseRef] , T1.[BaseType])A on A.BaseRef=t0.docnum
WHERE T0.[CANCELED]='N' and isnull(T0.[NumAtCard],0) not like '%作废%' and (T0.[DocDate]>=[%0] and T0.[DocDate]<=[%1])
union all
SELECT '应收贷项' as 单据类型,T0.[U_SS_InvoNo] ,t0.docnum,T0.[docentry], T0.[DocType],T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[U_SO001], T0.[NumAtCard] 合约号
,T0.[CardName], -(T0.[DocTotal]-T0.[VatSumSy]) as 不含税,-T0.[VatSumSy],-T0.[DocTotal] ,t0.U_kpno_1,t0.U_KPDATE_1,t0.U_kpamounts_1,t0.U_kpno_2,t0.U_KPDATE_2,t0.U_kpamounts_2,t0.U_kpno_3,t0.U_KPDATE_3,t0.U_kpamounts_3,t0.U_kpno_4,t0.U_KPDATE_4,t0.U_kpamounts_4,(T0.[DocTotal]-isnull(t0.U_kpamounts_1,0)-isnull(t0.U_kpamounts_2,0)-isnull(t0.U_kpamounts_3,0)-isnull(t0.U_kpamounts_4,0)) as 未开票金额, CASE A.[BaseType] WHEN '13' THEN '发票' when '16' then '退货' end 上下标识类型 ,A.BaseRef 上下标识单号
FROM ORIN T0 left join (SELECT T0.[DocNum] ,T1.[BaseRef] , T1.[BaseType] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] where T1.[BaseType] in ('13','16') group by T0.[DocNum] ,T1.[BaseRef] , T1.[BaseType])A on A.docnum=t0.docnum
WHERE T0.[CANCELED]='N' and isnull(T0.[NumAtCard],0) not like '%作废%' and (T0.[DocDate]>=[%0] and T0.[DocDate]<=[%1])
文章来自于网络,如果侵犯了您的权益,请联系站长删除!