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'