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

33

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Agustin,

Thank you! Does document source mean Object Type?

My issue is now that I get this query to work only if a PO was copied to a GRPO to a AP invoice, not the PO's copied directly to a AP Invoice and standalone Invoices.

Your help is appreciated,

Marli

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 LEFT  JOIN

POR1 T1 ON T0.DocEntry=T1.DocEntry LEFT  JOIN

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

PDN1 T3 ON T1.DocEntry=T3.BaseEntry and
T1.LineNum=T3.BaseLine LEFT  JOIN

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

PCH1 T5 ON T3.DocEntry=T5.BaseEntry and
T3.LineNum=T5.BaseLine 

WHERE T4.[DocDate] >= (CONVERT(DATETIME, [%0], 112)
)  AND  T4.[DocDate] <= (CONVERT(DATETIME, [%1], 112) )

ORDER BY  T4.[DocNum]
0

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

1