SAP business one查询报表-应收发票

最新推荐文章于 2023-05-08 00:33:56 发布

IceLee021

最新推荐文章于 2023-05-08 00:33:56 发布

阅读量309

点赞数

分类专栏： SAP 文章标签： sql

本文链接：https://blog.csdn.net/IceLee021/article/details/128239507

版权

SAP 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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])