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

问题描述

以下是将采购订单链接到销售订单的查询.我的理解是，为了在此报告中包含交付文档#，我需要再添加一个表 - ODLN(因此会有一个名为交付文档#"的附加字段，又名 [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.

您的问题不清楚的是，您是否只想要那些具有交货单据的 PO，只想要那些没有交货单的 PO，或所有 PO(由具有交货单据的那些订购或不订购).一旦我们知道了，我们就可以告诉你是使用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屋！