添加交货信息以在SAP Crystal Reports中进行查询 [英] Add delivery info to query in SAP Crystal Reports

问题描述

以下是将采购订单链接到销售订单的查询。我的理解是，为了在此报告中包含交货单号，我需要再添加一个表- ODLN （因此，还有一个名为 Delivery Doc＃的字段，也称为[ODLN.DocNum ]）。我的问题是我不确定如何在下面的查询中加入ODLN而不弄乱任何内容。

Below is a query linking Purchase Orders to Sales Orders. My understanding is that in order to include delivery doc # to this report, I need to add one more table - ODLN (so there would be an additional field titled "Delivery Doc#" aka [ODLN.DocNum]). My problem is I'm not sure how to join ODLN in the below query without messing anything up.

ODLN.DocNum几乎可以验证该PO确实在SO提交时被放置了。

ODLN.DocNum pretty much verifies that the PO did get placed at the time of the SO submission.

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

推荐答案

从注释中可以很明显地看出，您不确定要使用什么字段将ODLN联接到当前查询中。

From comments, it is clear that you aren't sure what fields to use to join the ODLN to your current query.

我会遵循这笔钱。

如果我没记错的话，ODLN.TransId会加入OJDT.TransId，OJDT.BaseRef会加入OPOR.DocNum。

If I recall correctly, ODLN.TransId joins to OJDT.TransId, and OJDT.BaseRef joins to OPOR.DocNum.

您的问题中不清楚的是，您是否只希望拥有交货单的采购订单，仅那些没有交货单的采购订单，还是所有采购订单（是否订购）？由具有传递文件的用户）。一旦知道了这一点，我们就可以告诉您是使用left还是inner，如何处理Null，等等。但是坦率地说，在SAP方面，这一部分微不足道。

What is unclear from your question is if you only want those POs that have a Delivery Doc, only those that don't, or all POs (ordered, or not, by those that have Delivery Docs). Once we know that, we can tell you whether to use left or inner, how to handle Null, etc. But frankly, when it comes to SAP, that part is trivial.

这篇关于添加交货信息以在SAP Crystal Reports中进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！