如何通过销售发票和日记帐事务信息进行销售交货与退货信息分析
问：我的销售业务的流程为：销售订单--销售交货--销售发票--销售收款，如何通过销售发票信息和日记帐分录事务交易信息
进行销售交货与退货信息的分析。
答：查询语句如下所示，在Sbo 2005B PL38环境下测试通过。
select a.DocEntry,b.DocEntry 发票号码,c.DocEntry 订单号码, l.cardCode,l.cardname,a.ItemCode, a.Dscription, a.Quantity, a.Price, a.LineTotal, a.WhsCode, w.whsName, d.DocEntry 退货单号, d.Quantity 退货数, d.Price 退货单价, d.LineTotal 退货金额
FROM DLN1 a inner join (SELECT Distinct T0.DocEntry, T0.BaseEntry FROM INV1 T0 inner join OINV T1 on T0.DocEntry=T1.DocEntry AND T0.BaseType=15
AND (T1.CardCode='[%0]' or IsNull('[%0]','')='') AND (T1.DocEntry=[%1] or IsNull([%1],0)=0) AND (T1.TransId=[%2] or IsNull([%2],0)=0)
) b on a.DocEntry=b.BaseEntry left join ORDR c on a.BaseEntry=c.DocEntry and a.BaseType=17
left join RDN1 d on a.DocEntry=d.baseEntry AND a.ItemCode=d.ItemCode and d.baseType=15
inner join OWHS w on a.whsCode=w.whscode inner join ODLN l on a.docEntry=l.docEntry
WHERE (a.ItemCode='[%3]' or IsNull('[%3]','')='')
FROM DLN1 a inner join (SELECT Distinct T0.DocEntry, T0.BaseEntry FROM INV1 T0 inner join OINV T1 on T0.DocEntry=T1.DocEntry AND T0.BaseType=15
AND (T1.CardCode='[%0]' or IsNull('[%0]','')='') AND (T1.DocEntry=[%1] or IsNull([%1],0)=0) AND (T1.TransId=[%2] or IsNull([%2],0)=0)
) b on a.DocEntry=b.BaseEntry left join ORDR c on a.BaseEntry=c.DocEntry and a.BaseType=17
left join RDN1 d on a.DocEntry=d.baseEntry AND a.ItemCode=d.ItemCode and d.baseType=15
inner join OWHS w on a.whsCode=w.whscode inner join ODLN l on a.docEntry=l.docEntry
WHERE (a.ItemCode='[%3]' or IsNull('[%3]','')='')
本文转自foresun 51CTO博客，原文链接：http://blog.51cto.com/foresun/86180，如需转载请自行联系原作者
