【无标题】

01003 销售出库明细（汇总）查询–扣减销售退货、应收贷项

• SELECT T0.DocNum, T2.slpname ‘销售员’, T11.GroupName ‘客户类别’, T0.CardCode, T0.CardName, T4.Name ‘销售类型’, T0.SupplCode ‘SO号’, T0.TaxDate ‘SO单据日期’, T0.DocDate ‘过账日期’, T0.DocDueDate ‘要求发货日期’, CASE when (T1.OpenQty =0 and t0.CANCELED=‘N’ and t1.LineStatus= ‘C’ and T1.InvntSttus <> ‘O’ ) then ‘已出完货’ when (T1.Quantity > t1.OpenQty and t1.OpenQty>0) then ‘部分出货’ when t1.Quantity =t1.OpenQty then ‘还未出货’ when (T1.OpenQty =0 and (t0.CANCELED=‘Y’ or T0.DocStatus = ‘C’) and T0.InvntSttus = ‘O’ ) then ‘订单已取消’ end ‘单据状态’, T1.LineNum+1 ‘行号’, T1.ItemCode ‘物料编号’, T1.Dscription ‘物料描述’, T1.CodeBars ‘条形码/SKU’, T1.Quantity ‘订单数量’, ( select sum(T02.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType ) ‘销售交货数量’, (select sum(T05.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join RRR1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join ORRR T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RDN1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORDN T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType) ‘销售退货数量’, (select sum(T07.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join INV1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join OINV T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RRR1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORRR T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ left join RIN1 T07 on T07.BaseEntry = T05.DocEntry and T07.BaseLine = T05.LineNum and T07.BaseType = T05.ObjType inner join ORIN T08 on T07.DocEntry = T08.DocEntry and T08.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType) ‘应收贷项数量’, isnull(( select sum(T02.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType ), 0) - isnull((select sum(T05.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join RRR1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join ORRR T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RDN1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORDN T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType), 0) - isnull((select sum(T07.Quantity) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join INV1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join OINV T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RRR1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORRR T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ left join RIN1 T07 on T07.BaseEntry = T05.DocEntry and T07.BaseLine = T05.LineNum and T07.BaseType = T05.ObjType inner join ORIN T08 on T07.DocEntry = T08.DocEntry and T08.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType), 0) ‘最终出库数量’, T1.unitMsr ‘单位’, T1.U_AOGADDRES ‘送货地址’, T1.PriceAfVAT ‘含税价’, T3.Rate ‘税率’, T1.Price ‘不含税价’, T1.GTotal ‘行含税总计’, isnull(( select sum(T02.GTotal) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType ), 0) - isnull((select sum(T05.GTotal) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join RRR1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join ORRR T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RDN1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORDN T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType), 0) - isnull((select sum(T07.GTotal) from ODLN T01 Inner Join DLN1 T02 ON T01.DocEntry = T02.DocEntry left join INV1 T03 on T03.BaseEntry = T02.DocEntry and T03.BaseLine = T02.LineNum and T03.BaseType = T02.ObjType inner join OINV T04 on T04.DocEntry = T03.DocEntry and T04.CANCELED = ‘N’ left join RRR1 T05 on T05.BaseEntry = T03.DocEntry and T05.BaseLine = T03.LineNum and T05.BaseType = T03.ObjType inner join ORRR T06 on T05.DocEntry = T06.DocEntry and T06.CANCELED = ‘N’ left join RIN1 T07 on T07.BaseEntry = T03.DocEntry and T07.BaseLine = T03.LineNum and T07.BaseType = T03.ObjType inner join ORIN T08 on T07.DocEntry = T08.DocEntry and T08.CANCELED = ‘N’ where T01.CANCELED=‘N’ and T02.BaseEntry = T1.DocEntry and T02.BaseLine = T1.LineNum and T02.BaseType = T1.ObjType), 0) ‘最终出库金额’, T1.U_OrderNumLine ‘客户订单编号’ FROM ORDR T0 Inner Join RDR1 T1 ON T0.DocEntry = T1.DocEntry Left Join OSLP T2 on T0.SlpCode = T2.SlpCode Inner Join OVTG T3 on T1.VatGroup = T3.Code Left Join [@AVA_STYPE] T4 on T4.Code=T0.U_SType Inner Join OCRD T10 on T10.CardCode = T0.CardCode Left Join OCRG T11 on T10.GroupCode = T11.GroupCode WHERE 1=1 and T0.CANCELED=‘N’ and (T1.lineStatus =[%1] or ‘[%1]’= ’ ‘) and (T0.TaxDate >=[%2] or ‘[%2]’=’’) and (T0.TaxDate <=[%3] or ‘[%3]’=’’) and (T0.CardCode LIKE ‘%[%4]%’ or ‘[%4]’=’’) and (T0.CardName LIKE ‘%[%5]%’ or ‘[%5]’=’’) and (T1.ItemCode LIKE ‘%[%6]%’ or ‘[%6]’=’’) and (T1.Dscription LIKE ‘%[%7]%’ or ‘[%7]’=’’) and (T2.SlpName LIKE ‘%[%8]%]’ or ‘[%8]’=’’) order by T0.DocNum ,T1.LineNum + 1

01022 当日修改的销售价格清单

https://zhuanlan.zhihu.com/p/87012265