Skip to Content
0

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

Apr 12 at 02:53 PM

15

avatar image

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) )
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
1

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

Share
10 |10000 characters needed characters left characters exceeded