Skip to Content
0
Feb 05, 2021 at 07:23 PM

SAB B1 Query - All PO's raised since a specific date, linked to GRPO

21 Views

Hi,

I need a query that shows me all Po's raised since a certain date (EG 01/01/2020 until now), across all suppliers, but excludes any cancelled PO's.

At line per PO level, I need it to show original quantity, outstanding quantity, and also if a line is linked to a Goods Receipt PO and the detail of those as well.

I have this so far but does this duplicates the PO document number and does not link tot he GRPO.

T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[DocNum], T1.[LineNum], T1.[ItemCode], T1.[Dscription], T1.[U_Qty_Units], T1.[Quantity], T1.[U_Qty_MC], T1.[OpenQty], T2.[DocNum], T2.[DocDate],T3.[Quantity], T3.[OpenQty]

OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry], OPDN T2 INNER JOIN PDN1 T3 ON T2.[DocEntry] = T3.[DocEntry]

T0.[CANCELED] = 'N' AND T0.[DocDate] >'01/10/2020' AND T2.[DocDate] >'01/01/2020'