Query for PO -> AP Invoice

[Former Member]

Experts, I have a tough question for you...... Well atleast for me.

Scenario: Query to try and find AP invoices that are not linked to a goods receipt, but are instead linked straight to a PO. How can this be done?

Thanks

[Former Member · Accepted solution]

Hi,

Try:

SELECT DisTinct T2.DocNum PO#, T0.DocNum,T0.DocDate,T0.CardName,T0.DocTotal

from opch t0

INNER JOIN POR1 T1 ON T1.TrgetEntry=T0.DocEntry AND T1.TargetType = '18'

INNER JOIN OPOR T2 ON T2.DocEntry=T1.DocEntry

Thanks,

Gordon

[Former Member · Accepted solution]

This worked perfectly....

Thanks to all who shared your thoughts!

[Former Member]

Hi

Try this, It eliminates the CreditMemos if any.

SELECT Distinct T2.DocNum as [PO Document Number] ,T0.DocNum as [AP Invoice Doc Num],T0.DocDate As [AP Posting Date],T0.CardCode,T0.CardName ,T0.DocTotal as [AP Document Total]

FROM OPCH T0 INNER JOIN PCH1 T1 on T0.DocEntry=T1.DocEntry INNER JOIN OPOR T2 on T2.DocEntry =T1.BaseEntry

WHERE T1.BaseType='22' and T1.LineStatus='O' and T0.DocDate >= [%0] and T0.DocDate <= [%1]

Thanks

Chakrapani Bandaru

Edited by: chakrapani bandaru on Aug 19, 2011 12:27 PM

[former_member206488]

Hi,

Try this to get list of AP invoices Based on PO within specified date range:

SELECT Distinct  T2.DocNum as [PO Document Number] ,T0.DocNum as [AP Invoice Doc Num],T0.DocDate As [AP Posting Date],T0.CardCode,T0.CardName ,T0.DocTotal as [AP Document Total]
FROM OPCH T0 
	INNER JOIN PCH1 T1 on T0.DocEntry=T1.DocEntry 
	INNER JOIN OPOR T2 on T2.DocEntry =T1.BaseEntry 
WHERE T1.BaseType='22' and  T0.DocDate >= [%0] and T0.DocDate <= [%1] 

Thanks,

Neetu