GRPO Query to show PO Date

[kedalenechong]

Hi all

How to connect GRPO Query with OPDN and PDN1 Tables to OPOR Table to show Purchase Order Date?

[kvbalakumar]

Hi Kedalene,

In general, the best approach to link base document is

[Target line].BaseEntry = [Base line].DocEntry AND

[Target line].BaseLine = [Base line].LineNum AND

[Base line].DocEntry = [Base Header].DocEntry AND

[Target line].BaseType = [Base Header].ObjType

In your case, it is

PDN1.BaseEntry = POR1.DocEntry and

PDN1.BaseLine = POR1.LineNum and

POR11.DocEntry = OPOR.DocEntry and

PDN1.BaseType = OPOR.ObjType

Regards,

Bala

[msundararaja_perumal]

Hello,

Always link the row tables with base document's docentry, itemcode,linenum to target document's baseentry,itemcode,baseline and vice versa.

As there can be same item repeated more than once in a single document, linking the row is also quite important.

Thanks.

[azeemhafeez]

Hi Kedalene Chong,

Your required query is:

SELECT T1.[DocNum] as 'PO#', T1.[DocDate] as 'PO Dt.', T3.[CardName] as 'Vendor', T2.[ItemCode], T2.[Dscription] as 'Item Name', ROUND(T0.[Quantity],1) as 'PO Qty.', T0.[unitMsr] as 'U', T0.[Price] as 'PO Rate', T3.[DocNum] as 'GRPO#', T3.[DocDate] as 'GRPO Dt.', ROUND(T2.[Quantity],1) as 'GRPO Qty.', T2.[WhsCode] FROM POR1 T0 FULL JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry FULL JOIN PDN1 T2 ON T1.DocEntry = T2.BaseEntry AND T0.ItemCode = T2.ItemCode FULL JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry ORDER BY T1.DocEntry,T2.ItemCode, T3.DocEntry

Hope it will benefit you.

Regards,

Azeem