以下是将采购订单链接到销售订单的查询。我的理解是,为了将交付文档# 包含到此报告中,我需要再添加一张表 -ODLN(因此会有一个标题为“Delivery Doc#”又名 [ODLN.DocNum] 的附加字段)。我的问题是我不确定如何在下面的查询中加入 ODLN 而不搞乱任何事情。
ODLN.DocNum 几乎可以验证 PO 确实在提交 SO 时已放置。
SELECT DISTINCT
o.CardName AS 'Customer Name'
,(isnull(c1.Street,'') + ', ' + isnull(c1.Block,'') + ', ' + isnull(c1.City,'') + ', ' + isnull(c1.[State],'') + ' ' + isnull(c1.ZipCode,'')) AS 'Customer Address'
,cpr.[Name] AS 'Customer Contact'
,cpr.Tel1 AS 'Customer Phone'
,cpr.E_MailL AS 'Customer Email'
,o.DocNum AS 'Sales Order #'
,p.DocNum AS 'PO # to Barracuda'
,l.ItemCode AS 'SKU'
,l.Dscription AS 'Desc'
,l.Quantity AS 'Qty'
,l.Price
,s.SlpName AS 'Sales Rep'
FROM
ORDR o
INNER JOIN RDR1 l ON o.DocEntry = l.DocEntry
LEFT JOIN POR1 p1 ON l.DocEntry = p1.BaseEntry AND l.LineNum = p1.BaseLine
LEFT JOIN OPOR p ON p1.DocEntry = p.DocEntry
INNER JOIN OCRD c ON o.CardCode = c.CardCode
INNER JOIN CRD1 c1 ON c.CardCode = c1.CardCode AND c.BillToDef = c1.[Address]
LEFT JOIN OCPR cpr ON c.CntctPrsn = cpr.[Name] AND c.CardCode = cpr.CardCode
INNER JOIN OITM itm ON l.ItemCode = itm.ItemCode
INNER JOIN OITB i ON itm.ItmsGrpCod = i.itmsGrpCod
INNER JOIN OSLP s ON o.SlpCode = s.SlpCode
WHERE
o.Canceled = 'N'
AND c1.AdresType = 'B'
AND i.ItmsGrpCod = 109
AND o.DocDate BETWEEN '6/01/2014 00:00:00.000' AND '9/30/2014 00:00:00.000'
ORDER BY
o.DocNum