Query Linking

[Former Member]

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.

[zal_parchem2 · Accepted solution]

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

[Former Member]

I am afraid the differences are coming from your UDF. Try my simplified query without UDF to see.

[zal_parchem2]

Gordon - hello FFF!!!

I have a few days off - read this...getting ready to find your poll...

http://scn.sap.com/docs/DOC-18702

[Former Member]

I have read it. So far, the new SCN has improved gradually from the day 1.

[Former Member]

Try:

Select Distinct T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum] 'GRPO#',T3.[Quantity],T1.[Dscription] as 'Description',T6.[NumatCard] as 'Commercial Invoice No.'

From OPOR t0

INNER JOIN POR1  T1 ON T0.DocEntry = T1.DocEntry

left  join PDN1  T3 on t0.docEntry=T3.Baseentry

left JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry

left  join PCH1 T5 on t5.Baseentry=T4.DocEntry

left JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry

where  T0.[CardCode] LIKE '%[%0]%'

[Former Member]

Gordon,

Thanks but the data reflecting is not proper

Example -

there is one purchase order with 6 line items against which there is one GRPo but by using above query it is reflecting
12 times which is not correct.

Thanks-
Mona.

[Former Member]

It is the same if you use group by. Try to find what are the differences between the similar row.

You may try to reorder it by Commercial Invoice # to find if that help.

[Former Member]

Hi Gordon,

I found the difference it is due to the difference in exchange rate.

What can i do to get the accurate result?

Thanks-
Mona.

[Former Member]

The only option is to omit those columns with different values.

[Former Member]

Gordon,

Now here I am confused the exchange rate is different at the time of PO and GRPO and the difference is in line total which i have not included in the query.

Thanks-
Mona.

[Former Member]

Hi Mona,

Post your query please.

Thanks,

Gordon

[Former Member]

Select T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum],T3.[Quantity],T1.[Dscription] as 'Description'

,T1.[u_Ship_Date] as ' Expected Ship Date',T0.[U_Status], T6.[NumatCard] as 'Commercial Invoice No.',T0.[u_File_No],T0.[u_prodo_number] as 'Production Order', T0.[U_SO_Number] as 'Sales Order'  From OPOR t0 INNER JOIN [dbo].[POR1]  T1 ON T0.DocEntry = T1.DocEntry left  join [dbo].[PDN1]  T3 on t0.docEntry=T3.Baseentry inner JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry left  join PCH1 T5 on t5.Baseentry=T4.DocEntry inner JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry where 

T0.[CardCode] = 'DU1000' or T0.[CardCode] = 'WU1020'

Group By t0.docnum,T1.[Quantity],T0.[DocDate],T0.[DocTotal], T4.[DocNum], T1.[u_Ship_Date],T4.[DocNum],T3.[Quantity],T4.[DocDate],T4.DocTotal, T6.[DocNum], T6.[DocDate],T6.[DocTotal],T0.[Cardname],T4.[Cardname],T6.[Cardname],T0.[CardCode],T6.[NumatCard],T1.[Dscription],

T0.[u_prodo_number], T0.[U_SO_Number],T0.[DocDueDate],T0.[u_File_No],T0.[U_Status],T0.[DocStatus] order by T0.[Cardname],T0.[DocStatus],T1.[u_Ship_Date]

[KennedyT21]

HI Mona,

Try This

Select Distinct T0.[Cardname] as 'CHKD', T0.[DocNum] as 'PO#',T0.[DocStatus] as 'PO Status', T1.[Quantity],T4.[DocNum] 'GRPO#',T3.[Quantity],T1.[Dscription] as 'Description',T6.[NumatCard] as 'Commercial Invoice No.'

From OPOR t0 INNER JOIN POR1  T1 ON T0.DocEntry = T1.DocEntry

left   outer join PDN1  T3 on t0.docEntry=T3.Baseentry

left  outer JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry

left   outer join PCH1 T5 on t5.Baseentry=T4.DocEntry

Left  outer JOIN OPCH T6 ON T5.DocEntry = T6.DocEntry

where  T0.[CardCode] LIKE '%[%0]%'

Regards

Kennedy