Totals of PO, AP Invoice by GRPO date query

[karolisr]

Hi all

I have a query, but it needs some amending.

1) I can't see the items which were changed in GRPO, which are different to the PO.

2) Could status be added to the Invoice section and if they are cancelled

3) The query doesn't pick up if the GRPO due date was changed to earlier date

The query is:

SELECT P1.DocNum as 'GRPO No', P1.DocDate as 'GRPO Date', P1.DocDueDate as 'GRPO Due Date', P1.CardName as 'Vendor', T1.ItemCode'PO itemcode', T1.Dscription, T1.Quantity as 'GRPO Qty', T1.LineTotal as 'GRPO LineTotal', P2.DocNum as 'PO No', P2.DocDate as 'PO Date', T3.Quantity as 'PO Qty', T3.LineTotal as 'PO LineTotal', T3.OpenQty as 'PO Open Qty', P5.DocNum as 'GoodsReturn No', P5.DocDate as 'GoodsReturn Date', T5.Quantity as 'GoodsReturn Qty', T5.LineTotal as 'GoodsReturn LineTotal', P3.DocNum as 'AP No', P3.DocDate as 'AP Date', T4.Quantity as 'AP Qty', T4.LineTotal as 'AP LineTotal', P6.DocNum as 'AP_CreditMemo No', P6.DocDate as 'AP_CreditMemo Date', T6.Quantity as 'AP_CreditMemo Qty', T6.LineTotal as 'AP_CreditMemo LineTotal' FROM POR1 T3 LEFT OUTER JOIN PDN1 T1 on T1.baseentry =T3.docentry AND T1.BaseLine=t3.LineNum AND T1.BaseType='22' LEFT OUTER JOIN PCH1 T4 on T4.BaseEntry = T1.DocEntry AND T4.BaseLine=t1.LineNum AND T4.BaseType='20' LEFT OUTER JOIN RPD1 T5 on T5.BaseEntry = T1.DocEntry AND T5.BaseLine=t1.LineNum AND T5.BaseType='20' LEFT OUTER JOIN RPC1 T6 on T6.BaseEntry = T4.DocEntry AND T6.BaseLine=t4.LineNum AND T6.BaseType='18' LEFT OUTER JOIN OPOR P2 ON T3.DocEntry = P2.DocEntry LEFT OUTER JOIN OPDN P1 ON T1.DocEntry = P1.DocEntry LEFT OUTER JOIN OPCH P3 on T4.DocEntry = P3.DocEntry LEFT OUTER JOIN ORPD P5 on T5.DocEntry = P5.DocEntry LEFT OUTER JOIN ORPC P6 on T6.DocEntry = P6.DocEntry LEFT OUTER JOIN NNM1 P4 on P2.series = P4.series WHERE P1.[DocDate] between [%0] AND [%1] AND P2.[CANCELED] <> 'Y' AND T3.[LinManClsd] <> 'Y'

Thank you!

[jerryjanda]

Hi, Karolis:

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members. You should consider using the "Insert Code" feature/button in the question interface.

Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Kind regards,

--Jerry