Skip to Content

GRPO Query to show PO Date

Hi all

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Feb 11, 2019 at 04:24 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 11, 2019 at 04:44 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 23, 2019 at 10:48 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.