Query: Invoices Paid with Date

[Former Member]

I'm hunting for a query that i'm sure was already written. Looking for a query with the following definitions:

Invoice Number

Invoiced Amount

Paid Date/Closed Date (closed date is not when the invoice was set to "close" but when payment was applied that accounts as last/final payment)

any thoughts gurus?

Thanks,

Rich

[Former Member]

I found this query:

SELECT T2.DocNum, T2.CardCode,T2.CardName, T2.NumAtCard,

T1.DocDate,T2.DocTotal,T0.SumApplied

FROM [dbo].[RCT2] T0 INNER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum INNER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry

WHERE (T0.SumApplied > 0) and

(T1.DocDate Between '[%0]' AND '[%1]')

it's pretty much what I need but the problem is the posting date of payment and payment value is not accurate. I'm assuming because some of our payers are consolidated payers and not necessarily the same as on the invoice.

Is there a workaround?

[Former Member]

here's a picture of the above query results and the actual results in B1.

the 1st row document total is correct. the invoice is paid by the invoiced parties master payer account as shown in the 2nd image.

[b1 query and actual |http://tinypic.com/r/1z582oj/7]

Edited by: Rich b. on Aug 29, 2011 9:36 PM

[Former Member]

Hi Rib,

The 2nd image has wrong link in the query. It can not be right. You need to post the screenshot for Incoming Payment.

Thanks,

Gordon

[Former Member]

Agree.

Below query seems to be working:

SELECT t2.docnum,t2.cardname,t2.docstatus, t2.doctotal as [Invoiced Amount], t0.docdate as [Payment Date]

FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum INNER JOIN OINV T2 ON T1.DocEntry = T2.DocEntry WHERE t0.docdate >=[%1] and t0.docdate <= [%2] and t2.docstatus = 'c' and t2.receiptnum=t1.docnum

[Former Member]

T0.DocEntry = T1.DocNum is not right.

Try T0.DocNum = T1.DocNum

[Former Member]

That link is created by Sap query generator.

[query|http://tinypic.com/r/2hwnio/7]

[Former Member]

This is only a special case in your database. Once they are not the same anymore, you will have trouble from query result. The link must be corrected in order to give the correct logic.

[Former Member]

Hi Richman,

i found this query very useful.How can i include the check number used to pay and the account pay