Skip to Content

Purchase Order History Query

Hello,

Can I get a query where in against a Purchase Order, what are the different GRPO's made and against that GRPO the A/P invoice which were created?

Selection criteria will be PO Doc Number.

Kalli

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Posted on Dec 11, 2011 at 05:33 PM

    Dear Kallicharan,

    Please find the following query

    SELECT T0.[DocNum] as 'PO NO', T0.[DocDate] as 'PO DATE',

    T0.[CardCode] as 'Party Code', T0. [CardName] as 'Name Of Party',T0.[DocStatus] as 'PO Status',

    T0.[DocTotal] as 'Total Amount' ,

    IsNull((Select Sum(IsNull(DocTotal,0)) From OPDN Where DocEntry In

    (Select Distinct DocEntry from PDN1 Where BaseEntry=T0.DocEntry And

    BaseType=T0.ObjType)),0) +

    IsNull((Select Sum(IsNull(DocTotal,0)) From OPCH Where DocEntry In

    (Select Distinct DocEntry from PCH1 Where BaseEntry=T0.DocEntry And

    BaseType=T0.ObjType)),0) 'GR/INV Amt'

    FROM OPOR T0 WHERE T0.[DocNum]>='[%0]' AND T0.[DocNum]<='[%0]'

    Regards,

    Kamlesh

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Kamlesh

      Changed the selection criteria a little and it worked, but this is not the report I want.

      Iam sorry If I did not convey what the output I want is.

      I want the following columns

      PO Num - PO Date - Vendor Name- NumatCard - GRPO No. - GRPO Date -- AP Doc Number -- AP Invoice Date. --AP Invoice Amt

      There will be scenarios where against 1 PO there can be Multiple GRPO's also

      Rgds

      Kalli

  • author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2011 at 07:28 AM

    Hi Kalli........

    Please try this........

    Select * From OPOR T0 Inner Join POR1 T1 On T0.DocEntry=T1.DocEntry 
    LEFT JOIN PDN1 T2 On T2.BaseEntry=T1.DocEntry LEFT Join OPDN T3 On 
    T2.DocEntry=T3.DocEntry LEFT JOIN PCH1 T4 On T0.BaseEntry=T3.DocEntry 
    LEFT Join OPCH T5 On T4.DocEntry=T5.DocEntry

    Regards,

    Rahul

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Ok....

      Try this.....

      Select * From OPOR T0 Inner Join POR1 T1 On T0.DocEntry=T1.DocEntry 
      LEFT JOIN PDN1 T2 On T2.BaseEntry=T1.DocEntry LEFT Join OPDN T3 On 
      T2.DocEntry=T3.DocEntry LEFT JOIN PCH1 T4 On T4.BaseEntry=T3.DocEntry 
      LEFT Join OPCH T5 On T4.DocEntry=T5.DocEntry

      Regards,

      Rahul

  • Posted on Dec 12, 2011 at 10:15 AM

    Hi Kalli........

    Please try this........

    SELECT

    T0.[DocNum] as 'PO NO', T0.[DocDate] as 'PO DATE',

    P1.[GRPODocNum] as 'GRPO NO',

    P1.[GRPODocDate] as 'GRPO DATE',

    P1.[INVOICENO] as 'INVOICE NO',

    P1.[INVOICEDATE] as 'INVOICE DATE',

    T0.[CardCode] as 'Party Code',

    T0.[CardName] as 'Name Of Party',T0.[DocStatus] as 'PO Status', T0.[VatSum],

    T0.[DocTotal] as 'Total Amount' , T0.[Comments] as 'Remarks',

    IsNull(

    (

    Select Sum(IsNull(VatSum,0))

    From OPDN

    Where DocEntry In (

    Select Distinct DocEntry

    from PDN1

    Where BaseEntry=T0.DocEntry

    And BaseType=T0.ObjType

    )

    ),0) +

    IsNull(

    (

    Select Sum(IsNull(VatSum,0))

    From OPCH T3

    Where DocEntry In (

    Select Distinct DocEntry

    from PCH1

    Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType

    )

    ),0) 'GR/INV TaxAmt',

    IsNull(

    (

    Select Sum(IsNull(DocTotal,0))

    From OPDN

    Where DocEntry In (

    Select Distinct DocEntry

    from PDN1

    Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType

    )

    ),0) +

    IsNull(

    (

    Select Sum(IsNull(T3.DocTotal,0))

    From OPCH T3

    Where DocEntry In (

    Select Distinct DocEntry

    from PCH1

    Where BaseEntry=T0.DocEntry And BaseType=T0.ObjType

    )

    ),0) 'GR/INV Amt' ,

    (select DocTotal from OPCH where DOCENTRY= INVENTRY) 'INV Amt'

    FROM OPOR T0

    inner join (SELECT DISTINCT

    T2.DOCENTRY PODocEntry,

    T3.DOCENTRY INVEntry,

    (select DocNum from OPCH where DocEntry = T3.DOCENTRY) INVOICENO,

    (select DocDate from OPCH where DocEntry = T3.DOCENTRY) INVOICEDATE,

    T1.DocEntry GRPOEntry,

    T0.DocNum GRPODocNum,

    T0.DocDate GRPODocDate

    FROM OPDN T0

    INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN POR1 T2 ON (T2.DOCENTRY = T1.BASEENTRY AND T2.LINENUM = T1.BASELINE)

    INNER JOIN PCH1 T3 ON (T1.DOCENTRY = T3.BASEENTRY AND T1.LINENUM = T3.BASELINE)

    ) P1 on T0.docentry = P1.PODocEntry

    WHERE (T0.[Series] ='10')

    and (T0.[DocDate]>='[%0]' AND T0.[DocDate]<='[%1]')

    order by T0.[DocNum]

    Regards

    Kamlesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2011 at 03:48 PM

    Hi Kalli,

    Try:

    SELECT Distinct

    T0.DocNum as 'PO NO',

    T0.DocDate as 'PO DATE',

    T0.Cardname as 'Vendor Name',

    T0.NumAtCard as 'Vendor Ref',

    T2.DocNum as 'GRPO NO',

    T2.DocDate as 'GRPO DATE',

    T4.DocNum as 'AP Invoice NO',

    T4.DocDate as 'AP Invoice DATE',

    T4.DocTotal as 'AP Invoice Amount'

    FROM OPOR T0

    LEFT JOIN PDN1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType=22

    LEFT JOIN OPDN T2 ON T2.DocEntry=T1.DocEntry

    LEFT JOIN PCH1 T3 ON T3.BaseEntry=T2.DocEntry AND T3.BaseType=20

    LEFT JOIN OPCH T4 ON T4.DocEntry=T3.DocEntry

    WHERE T0.DocDate Between [%0\] AND [%1\]

    Thanks,

    Gordon

    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.