cancel
Showing results for 
Search instead for 
Did you mean: 

Query Linking

Former Member
0 Kudos
238

Hi all,

I have made below query to reflect the po's and grpo against it if any and purchase invoice against GRPO if any.
But if I am using inner join between por1 and pdn1 it is not reflecting open purchase orders and if i am using left join
than also it is not reflecting correct data.

Please Help!

Thanks-
Mona.

View Entire Topic
zal_parchem2
Active Contributor
0 Kudos

Hello Mona - I am going to agree 100% with Gordon on this one - you need to simplify your query to first get your results and then you can expand with UDFs and such.  LOL - I am going to add maybe might be best to have a bit of structure also to make it easier to read...

Other Points:

1.  You seem to be skipping some important table joins to get where you want to go - these are included below...

2.  You need to put a bit more in your LEFT OUTER JOINS with Line Number and Item Code (otherwise it flies all over the place)...

3.  I did not see any Returns or Credit Memos in your query, so I included them...these are pretty important aren't they???

4.  You need to have "FOR BROWSE" for this many tables being utilized...

5.  I would keep a clean copy of the below SQL and then slowly add new data fields to versions of the below SQL...testing frequently as you go along and keeping versions until you get to the point you want...

6.  Thanks for your thread as I now have something to post for SAP site...

Hope this helps you get on the right path...Zal

----COPY AND PASTE THE BELOW SQL---


SELECT

T2.DocNum AS 'Purch Ord',
T2.DocDate AS ' Ord Date',
T2.CardCode AS 'Vend Num',
T1.ItemCode AS 'Product',
T1.LineStatus AS 'Row Stat',
T2.Canceled AS 'Cancelled?',
T1.LineNum+1 AS 'Line',
T1.Quantity AS 'Quantity',
T1.OpenQty AS 'Open Qty',
T4.DocNum AS 'Goods Rcpt',
T4.DocDueDate AS 'Rcpt Date',
T3.LineStatus AS 'Row Stat',
T4.Canceled AS 'Cancelled?',
T3.LineNum + 1 AS 'Line',
T3.Quantity AS 'Quantity',
T6.DocNum AS 'Return',
T5.LineNum +1 AS 'Line',
T5.LineStatus AS 'Row Stat',
T6.Canceled AS 'Cancelled?',
T5.Quantity AS 'Quantity',
T8.DocNum AS 'AP Invoice',
T7.LineNum + 1 AS 'Line',
T7.Quantity AS 'Quantity',
T10.DocNum AS 'AP Cred Memo',
T9.LineNum + 1 AS 'Line',
T9.Quantity AS 'Quantity'

FROM POR1 T1

LEFT OUTER JOIN OPOR T2
ON T1.DocEntry = T2.DocEntry

LEFT OUTER JOIN PDN1 T3
ON T2.DocEntry = T3.BaseEntry
AND T1.LineNum = T3.BaseLine
AND T1.ItemCode = T3.ItemCode

LEFT OUTER JOIN OPDN T4
ON T3.DocEntry = T4.DocEntry

LEFT OUTER JOIN RPD1 T5
ON T4.DocEntry = T5.BaseEntry
AND T3.LineNum = T5.BaseLine
AND T3.ItemCode = T5.ItemCode

LEFT OUTER JOIN ORPD T6
ON T5.DocEntry = T6.DocEntry

LEFT OUTER JOIN PCH1 T7
ON T4.DocEntry = T7.BaseEntry
AND T3.LineNum = T7.BaseLine
AND T3.ItemCode = T7.ItemCode

LEFT OUTER JOIN OPCH T8
ON T7.DocEntry = T8.DocEntry

LEFT OUTER JOIN RPC1 T9
ON T8.DocEntry = T9.BaseEntry
AND T7.LineNum = T9.BaseLine
AND T7.ItemCode = T9.ItemCode

LEFT OUTER JOIN ORPC T10
ON T9.DocEntry = T10.DocEntry

ORDER BY

T2.DocNum,
T1.LineNum,
T4.DocNum,
T3.LineNum,
T6.DocNum,
T5.LineNum,
T8.DocNum,
T7.LineNum,
T10.DocNum,
T9.LineNum

FOR BROWSE

Message was edited by: Zal Parchem