Skip to Content
Sep 27, 2019 at 09:38 AM

Query for SO, PO, and GRPO delivery different dates.


Hi Experts,


What should I put in Query Generator to display in my report the Sales Order, Purchase Order and GRPO different dates.

Please see my codes below and see excel file attached for my acquired details to show in my report,

PS: I want to display the dates which is the PO item is delivered to us. Please Help.

SELECT T0.[DocNum] as "SO No.", T0.[CardName] as "Customer Name", T0.[DocDate] as "SO Posting Date", T1.[ItemCode] as "Item No.", T1.[Dscription] as "Item Description", T1.[ShipDate] as "Del. Rqstd Date by Customer", T1.[Quantity] as "Required Quantity", T4.[OnHand], T4.[IsCommited], T2.[DocNum] as "PO No.", T2.[CardName] as "Supplier Name", T2.[DocDate] as "PO Date", T3.[Quantity] as "PO Qty", T3.[ShipDate] as "Required Del Date by Us", T6.[ActDelDate] as "Date delivered to us"

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry left join OPOR T2 on T2.[DocNum] = T1.[PoTrgNum] left JOIN POR1 T3 ON T2.DocEntry = T3.DocEntry INNER JOIN OITM T4 ON T1.[ItemCode] = T4.[ItemCode] left JOIN OPDN T5 ON T5.[DocNum] = T3.[PoTrgNum] left JOIN PDN1 T6 ON T5.DocEntry = T6.DocEntry

WHERE T1.[LineStatus] = 'o' or T3.[LineStatus] = 'o' and T0.canceled = 'n' or T2.CANCELED = 'n'

GROUP BY T0.[DocNum], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[ShipDate], T1.[Quantity], T4.[OnHand], T4.[IsCommited], T2.[DocNum], T2.[CardName],T2.[DocDate], T3.[Quantity], T3.[ShipDate], T6.[ActDelDate]

Hope to see a positive answer..

Thank you,