Skip to Content

Query linking AP, Goods receipt, PO not showing where PO is copied directly to AP

Experts,

I have this query that need to show AP Invoices and related linked PO's. This works, except it does not show the PO # where the AP Invoice is directly copied from a PO.

You assistance will be greatly appreciated.

SELECT 
T0.[CardCode] AS 'Customer/Vendor Code', 
T0.[CardName] AS 'Customer/Vendor Name', 
T0.[DocType] AS 'Invoice Type',
T0.[DocNum] AS 'AP Invoice Number', 
T6.[DocNum] AS 'PO Number',
T0.[DocDate] AS 'Posting Date', 
T0.[NumAtCard] AS 'Vendor Reference',
T0.[CreateDate] AS 'Creation Date', 
T1.[ItemCode] AS 'Item Code',
T1.[Dscription] AS 'Item/Service Description', 
T1.[LineTotal] AS 'Row Total', 
T8.[Segment_0] AS 'Segment 0', 
T8.[Segment_1] AS 'Segment 1', 
T8.[Segment_2] AS 'Segment 2', 
T8.[Segment_3] AS 'Segment 3', 
T8.[AcctName] AS 'Account Name' 


FROM OPCH T0


LEFT JOIN PCH1 T1 ON T0.[DocEntry]=T1.[DocEntry]


LEFT JOIN PDN1 T5 ON T1.[BaseEntry]=T5.[DocEntry] AND T1.BaseLine = T5.LineNum


LEFT JOIN OPDN T3 ON T3.[DocEntry]=T5.[DocEntry]


LEFT JOIN POR1 T7 ON T5.[BaseEntry]=T7.[DocEntry] AND T5.BaseLine = T7.LineNum


LEFT JOIN OPOR T6 ON T7.[DocEntry]=T6.[DocNum]


INNER  JOIN OACT  T8  ON  T1.[AcctCode] = T8.[AcctCode]


WHERE T1.[DocDate] >= (CONVERT(DATETIME, [%0], 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, [%1], 112) )
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 12 at 03:25 PM

    Hi

    if you want to obtain the purchase invoices copied from a purchase order you can add a new left join between invoices lines and order lines, and a new condition to filter the object source.

    Or you can use the union all clausule to obtain the information. Remember you have to filter by document source.

    Kind regards

    Agustín

    Add comment
    10|10000 characters needed characters exceeded

    • Hi

      try this SQL query:

      Select  T4.[CardCode] AS 'Customer/Vendor Code', T4.[CardName] AS 'Customer/Vendor Name', 
      T4.[DocNum] AS [ARInvoiceDocnum], T2.[DocNum] AS [GRPODocNum], T0.[DocNum] AS [PODocNum]
      FROM
      OPOR T0 INNER  JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry 
      -- INNER JOIN OPDN T2 ON T1.TrgetEntry = T2.DocEntry 
      INNER JOIN PDN1 T3 ON T1.DocEntry = T3.BaseEntry AND T1.LineNum = T3.BaseLine 
      	and T3.basetype = '22' 
      INNER JOIN OPCH T4 ON T3.TrgetEntry = T4.DocEntry 
      INNER JOIN PCH1 T5 ON T3.DocEntry = T5.BaseEntry AND T3.LineNum = T5.BaseLine 
      	and T5.basetype = '20'
      WHERE T4.[DocDate] >= (CONVERT(DATETIME, [%0], 112)) AND T4.[DocDate] <= (CONVERT(DATETIME, [%1], 112))
      ORDER BY  T4.[DocNum]
      

      Agustín