Date invoice is paid in full

[Former Member]

How can I query the A/R invoice table and determine the date an invoice was paid in full?

[Former Member]

Closed.

[Former Member]

Closed.

[marcella_rivi]

Dear Cindy,

A first step could be checking the ORCT table. This table stores the information regarding the payments. You can select the payment for a particular business partner and select the document from the OINV table.

You can start from something like that:

SELECT T0.DocNum, T0.DocType, T0.Canceled, T0.DocDate, T0.DocDueDate, T1.DocNum, T1.DocType, T1.DocDate, T1.DocDueDate FROM ORCT T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.ReceiptNum WHERE T0.CardCode = 'c1008' and T1.CardCode = 'c1008'

Kind Regards,

Marcella Rivi

SAP Business One Forums Team

[former_member583013]

Is this for invoices which have paid through mutiple payments?

Are you looking at getting the latest date of all the payment which closed the invoice?

[Former Member]

Yes, Suda, I need to know when the last payment was made that fully paid the invoice.

I am looking at commisioned sales and I don't want to pay commission for a sale to a customer who doesn't pay his bill.

[former_member583013]

One of the simple ways is to click on the Payment Means Icon (on the tool bar) When you are viewing the Invoice..it will display all the Payments and their dates...this should give you the date for the latest payment..

[Former Member]

But that doesn't give me the answer I need for thousands of invoices paid off in a month.

I assumed that each invoice would have a 'date invoice paid off' field as in MS Dynamics, a product I've worked with previously, but no.

I think I'm going to have to look at the payments table and find the max(docdate) of payments that were applied to invoice where the invoice is paid in full (oinv.doctotal - oinv.paidtodate = 0)

[former_member583013]

YES, if you want this as a report...then yes..the approach you mentioned is exactly right..

[Former Member]

Hi Cindy,

If you want the exact date for the invoice paid in full, only query invoice table can not achieve that. If you just want to know which invoice is paid in full to date then a simple query is enough like:

Select T0.DocNum, T0.DocDate, T0.DocTotal From OINV T0 Where T0.PaidToDate = T0.DocTotal

Thanks,

Gordon