Hi, I’ve worked with SAP for a while, but only started writing queries recently. Obviously, Gordon Du's book has been enormously helpful.
I’m attempting to create a unified ‘Goods Receipt’ report on our purchasing, meaning I’d like to see data from the original PO as well as the final AP invoice. The problem is that, historically, our receiving process is not consistent. We haven’t always gone from PO to Goods Receipt PO to AP Invoice. In some cases we’ve gone directly from PO to AP Invoice. This means that BaseEntry in OPCH sometimes references a GR PO#, and sometimes a PO#.
I was able to create a report with OPOR and OPDN (and more or less the same report for OPOR and OPCH).
SELECT
T2.[DocNum] AS 'PO#',
T2.[CardCode],
T2.[CardName],
T2.[DocDate] as 'PO Date',
T2.[DocDueDate] AS 'Planned PO Del. Date',
T0.[ItemCode],
T0.[Dscription],
T0.[Quantity] AS 'Qty from PO',
T0.[LineTotal] AS 'PO Cost',
T3.[DocNum] AS 'Del. Note #',
T1.[Quantity] AS 'Delivered Qty',
T1.[LineTotal] AS 'Del. Cost',
T3.[DocDueDate] AS 'Delivery Date'
FROM dbo.POR1 T0
LEFT JOIN dbo.PDN1 T1 ON T0.[DocEntry] = T1.[BaseEntry]
AND T0.[LineNum] = T1.[BaseLine]
INNER JOIN dbo.OPOR T2 ON T0.[DocEntry] = T2.[DocEntry]
INNER JOIN dbo.OPDN T3 on T3.[DocEntry] = T1.[DocEntry]
WHERE T3.[DocDueDate] >= [%0] AND T3.[DocDueDate] <= [%1]
ORDER BY T3.DocNum ASC
I know I need to create some sort of conditional link (if OPCH BaseType is PO, link on to POR1, if it’s a GRPO, link to PDN1) but haven’t been able to pull it off so far.
I can get most of the information I need by using the AP Invoice only. This is fine for the POs that went straight to AP Invoice, in that AP invoice date reflects when the inventory was received, but when the document flow is PO to GR PO to AP Invoice, sometimes the GR PO date is much earlier than AP invoice date, and I want to know the exact date that product on specific POs entered our inventory.
My workaround so far has been multiple queries (POs + GR POs, GR POs + AP Invoice, POs + AP Invoice) and unifying the data in Excel, but I’d love to make it a single query.
(I know I’ll have to incorporate the AP credits as well for accurate numbers, but one step at a time).
Thanks, any help is appreciated.