Skip to Content
0
Former Member
Feb 11, 2009 at 09:39 PM

Query help on Goods Receipt Query with AP Invoice

813 Views

Looking for a little help on a query. I would like to list all the goods receipts for a given date range and then display the AP Invoice information (if its been copied to an AP Invoice). I think my problem is in my where clause, I plagerized an SAP query to show GR and AP from a PO as a start. SBO 2005 SP01. Any help would be great appreciated. Thanks

SELECT distinct 'GR',

D0.DocStatus,

D0.DocNum ,

D0.DocDate,

D0.DocDueDate,

D0.DocTotal,

'AP',

I0.DocStatus,

I0.DocNum ,

I0.DocDate,

I0.DocDueDate,

I0.DocTotal,

I0.PaidToDate

FROM

((OPDN D0 inner Join PDN1 D1 on D0.DocEntry = D1.DocEntry)

full outer join

(OPCH I0 inner join PCH1 I1 on I0.DocEntry = I1.DocEntry)

on (I1.BaseType=20 AND D1.DocEntry = I1.BaseEntry AND D1.LineNum=I1.BaseLine))

WHERE

(D1.BaseType=22 AND D1.DocDate>='[%0]' AND D1.DocDate<='[%1]')

OR (I1.BaseType=20 AND I1.BaseEntry IN

(SELECT Distinct DocEntry

FROM PDN1 WHERE BaseType=22 AND DocDate>='[%0]' AND DocDate<='[%1]'))