Hello,
I am looking for a query which shows the PO Qty, if it was received on a GRPO the received Qty, and it the item was not received. So far I have the query below but it only shows items that are linked to GRPOs, it does not show me all the items on the Purchase Order. How to do I change this Query to reflect all items on the PO along with the GRPO information? Should I use a union all instead?
SELECT
T2.[WhsCode] as "StoreNum", T1.[DocNum] as "PO Num", T1.[Segment] as 'Seg' ,
T2.[ItemCode], T2.[Dscription], T2.[CodeBars], T2.Vendornum,
T2.[Quantity] as 'PO Qty', t3.Quantity as 'Recd Qty', SUM(T2.[Quantity] - t3.Quantity) as 'Qty Diff', T2.[PriceBefDi], T2.[LineTotal], T2.[OpenQty] as 'PO Qty Open', T3.OpenQty, T2.[LineStatus] as 'Status',
T1.[CardCode] as "Vendor", T1.[CardName] as 'VendorName',
T1.[DocDate] as "PO Date", T1.[Address2], T1.[U_BuyerRem] ,T1.[Comments]
FROM [dbo].[OPOR] T1 with (nolock)
INNER JOIN POR1 T2 with (nolock) ON T1.[DocEntry] = T2.[DocEntry]
Inner Join PDN1 T3 on T3.[BaseEntry] = t1.docentry and T3.[BaseLine] = T2.[LineNum]
INNER JOIN OPDN T4 ON T3.DocEntry = T4.DocEntry
WHERE
T1.[DocNum] = [%0]
Group By T2.[WhsCode], T1.[DocNum],T1.[Segment],T2.[ItemCode], T2.[Dscription], T2.[CodeBars], T2.Vendornum,
T2.[Quantity], t3.Quantity,T2.[PriceBefDi], T2.[LineTotal], T2.[OpenQty], T2.[LineStatus],
T1.[CardCode], T1.[CardName],
T1.[DocDate] , T1.[Address2], T1.[U_BuyerRem] ,T1.[Comments],T3.OpenQty
ORDER BY T2.[WhsCode], T2.[ItemCode]
Any help would be greatly appreciated. Thank you !