Skip to Content

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

Apr 12 at 02:53 PM


avatar image


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.

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' 


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) )
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Agustin Marcos Cividanes Apr 12 at 03:25 PM


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


10 |10000 characters needed characters left characters exceeded