PO Vendor Name in GRPO Query

[kedalenechong]

Hi All

Please advise how to add Purchase Order Vendor Name to this GRPO Query?

SELECT T0.[CreateDate] as 'GRPO Create Date', T1.[DocEntry] as 'GRPO Doc No.', T0.[DocDate] 'GRPO Date', T0.[DocStatus] as 'Doc Status', T0.[U_BLNO] as 'B/L No.', T0.[NumAtCard] as 'DO No.', T0.[U_CONTREF], T0.[U_WSLIP], T0.[U_SUPWSLIP] as 'Supplier Weighslip No.', T0.[U_RECWSLIP] as 'Receiver Weighslip No.', T0.[CardCode] as 'Vendor Code', T0.[CardName] as 'Vendor Name', T1.[ItemCode], T1.[Dscription], T1.[Quantity] as 'GRPO Qty', T0.[U_PO_VW] as 'Supplier Qty', T0.[U_OTHERWEIGHT] as 'Receiver Qty', T1.[WhsCode], T1.[BaseDocNum] as 'PO No.', T0.[U_LOGREMARKS] as 'Logistics Remarks', T2.[DocDate] as 'GR Date', T2.[DocEntry] as 'GR Doc No.', T2.[Quantity] as 'GR Qty' FROM OPDN T0 LEFT JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN RPD1 T2 ON T1.DocEntry = T2.BaseEntry AND T1.ItemCode = T2.ItemCode WHERE T0.[CardName] = [%0] AND T1.[BaseDocNum] = [%1]

ORDER BY T0.[DocDate], T1.[DocEntry]

Kedalene Chong

[Simo_Anddam · Accepted solution]

Hi Kedalene, 
To add the Purchase Order Vendor Name to the query, you need to join the Purchase Order OPOR table (for header-level details

SELECT 
    T0.[CreateDate] AS 'GRPO Create Date', 
    T1.[DocEntry] AS 'GRPO Doc No.', 
    T0.[DocDate] AS 'GRPO Date', 
    T0.[DocStatus] AS 'Doc Status', 
    T0.[U_BLNO] AS 'B/L No.', 
    T0.[NumAtCard] AS 'DO No.', 
    T0.[U_CONTREF], 
    T0.[U_WSLIP], 
    T0.[U_SUPWSLIP] AS 'Supplier Weighslip No.', 
    T0.[U_RECWSLIP] AS 'Receiver Weighslip No.', 
    T0.[CardCode] AS 'Vendor Code', 
    T0.[CardName] AS 'Vendor Name', 
    T1.[ItemCode], 
    T1.[Dscription], 
    T1.[Quantity] AS 'GRPO Qty', 
    T0.[U_PO_VW] AS 'Supplier Qty', 
    T0.[U_OTHERWEIGHT] AS 'Receiver Qty', 
    T1.[WhsCode], 
    T1.[BaseDocNum] AS 'PO No.', 
    T3.[CardName] AS 'PO Vendor Name', 
    T0.[U_LOGREMARKS] AS 'Logistics Remarks', 
    T2.[DocDate] AS 'GR Date', 
    T2.[DocEntry] AS 'GR Doc No.', 
    T2.[Quantity] AS 'GR Qty'
FROM 
    OPDN T0
LEFT JOIN 
    PDN1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN 
    RPD1 T2 ON T1.DocEntry = T2.BaseEntry AND T1.ItemCode = T2.ItemCode
LEFT JOIN 
    OPOR T3 ON T1.BaseEntry = T3.DocEntry
WHERE 
    T0.[CardName] = [%0] 
    AND T1.[BaseDocNum] = [%1]

[jitin_chawla]

Hi,

The field can be added by joining the row table of the GRPO row table i.e PDN1 to the PO header table i.e OPOR checking the BaseType and the BaseEntry.

However, do we have any circumstance or Business Scenario where a PO has a different Vendor Name to the GRPO Vendor Name where they are based on each other? I am not sure. 

If they are based, then Vendor Name in PO = Vendor Name in GRPO.

If they are different, then there would be no link in PO and the GRPO.

If PO and GRPO have same vendor, then you can use the name field from GRPO. Not sure (again based on business process)

regards,

Jitin

[kedalenechong]

Hi Jitin This happens when our client change Vendor Name in BP Master Data before Copying PO to GRPO.