2

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 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
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
vnguyen
  • 327
  • 1
  • 3
  • 16
  • How does that ODLN.DocNum relate to the existing tables/columns? – Cade Roux Mar 07 '17 at 02:11
  • The Delivery Doc verifies that the the PO did get placed at the time of the SO submission. – vnguyen Mar 07 '17 at 02:20
  • How does that table relate to the others so we can figure out the join? – Cade Roux Mar 07 '17 at 02:22
  • are you saying a PO may not get a doc num until the SO? in that case left join the ODLN to PO. – RoMEoMusTDiE Mar 07 '17 at 02:36
  • You can insert the other table OLDN after from make sure their is a related field within the old one. – Vijunav Vastivch Mar 07 '17 at 03:37
  • @maSTArHiAn: is there a query I can run to find the common field between ODLN and OPOR? – vnguyen Mar 09 '17 at 00:54
  • check the schemas or just run a top 1 from both tables. not that i know of a query but don't waste your time on that just check them physically. – RoMEoMusTDiE Mar 09 '17 at 01:26
  • So this is what my information_schema.key_column_usage lists: TABLE_NAME: ODLN; COLUMN_NAME: DocEntry; ORDINAL_POSITION. What does this mean? How can I use this info to proceed? – vnguyen Mar 09 '17 at 01:49
  • what is the field that links the PO and ODLN? or PO and SO to ODLN? – RoMEoMusTDiE Mar 09 '17 at 20:21
  • @maSTArHiAn @ CadeRoux Knowing SAP B1, it is easier to read the question. The OP question is exactly what you are asking, and as such the OP can not answer it. The documentation (if you could call it that) for SAP B1 is ambiguous, at best, making questions such as this common place. We just don't have a big SAP community on SO. – CWilson Apr 14 '17 at 00:54

1 Answers1

0

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

I would follow the money.

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

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.

CWilson
  • 425
  • 10
  • 28