Determining if invoice was fully paid or fully credited

[Former Member]

I'm trying to write a query on the invoices table and in cases where the invoice is Closed, I need to determine whether it was fully paid, fully credited or paid & credited.

In cases where a Credit Memo is created directly from the Invoice, I can see that the OINV PaidToDate field updates with the Document Total while the OINV PaidSum field remains at 0. So in those cases, I can easily tell that the invoice has been fully credited.

However, in cases where a Credit Memo is created separately and then manually Reconciled with the Invoice, both OINV PaidToDate and OINV PaidSum update with the Document Total. This is the same as when an invoice is Fully Paid. So I can't tell the difference between an Invoice that has been fully paid vs an Invoice that has been fully credited and then manually reconciled.

Is there any other way to determine in a query if an invoice has been fully credited or fully paid?

Thank you,

Charlotte

[former_member196647 · Accepted solution]

Hi Charlotte,

I am not sure about it myself, but you can try these things, there is a field in invoice table called ReceiptNum, which contains the document number of the incoming payment. It also contains a field called RetInvoice, which contains "Y", if a credit memo was created.

Probably combination of these would help.

Rahul

[Former Member]

I was able to query the Reconciliation Tables (OITR and ITR1) to get the info I needed. Basically I looked up to see the total Reconciled Amounts for the Invoice where the Reconciliation Type was Manual or by Credit Memo and the Reconciliation included a Credit Memo document.

(SELECT Sum(T6.[ReconSum]) FROM OINV T5 LEFT JOIN ITR1 T6 ON T5.[DocEntry]=T6.[SrcObjAbs] LEFT JOIN OITR T7 ON T6.ReconNum = T7.ReconNum LEFT JOIN ITR1 T8 ON T7.ReconNum = T8.ReconNum WHERE T8.[SrcObjTyp]=14 AND T6.[SrcObjTyp]=13 AND

(T7.[ReconType]=4 OR T7.[ReconType]=0) AND T0.[DocEntry]=T6.[SrcObjAbs]) as 'Amt Credited'

Thank you anyway for the other suggestions.

[JesperB1]

HI Charlotte,

I think these are the only tables that might give you useful information about this and this is why it is good that you are in 2007 as they don't exist in 2005.

Just watch out if the reconciliation has been cancelled, the cancellation has a different reconciliation number.

Good Luck,

Jesper

[Former Member]

Rahul: thanks for your suggestions. I checked the fields you mentioned. RetInvoice is showing N for all invoices in the system, credited or not. ReceiptNum doesn't display anything if the Receipt was applied to an Invoice using a Manual Reconciliation. So it doesn't look like these fields will differentiate the Fully credited invoices from the fully paids.

Jesper: I'm using SBO 2007A PL41 (sorry, I should have mentioned that in the original post.

Suda: The reason I need to determine this is that we sell to our customers based on a 12 month subscription for our services. So we enter the invoice for the 12 month value. Then at the end of the 12 months we call them back to see if they want to renew. In cases where we weren't able to collect the money from the client and we end up crediting the invoice back, these clients are not worthwhile to call regarding renewal. I want to eliminate these from the Query I'm doing (which is essentially a query to find all invoices eligible for Renewal).

Thanks for your help,

Charlotte

[former_member583013]

I have worked with clients who provide similar subscription based service and the solution was little different. I could share some of those with you.

I could suggest that you use one the BP properties and check it for all those customer to whom you credited the Invoice and running a report against this BP property would give you all you need.

[Former Member]

I don't think that will work. Because it could be that we credited them in the past but in the future they may become a good client again.

Thanks for the suggestion though.

Charlotte

Edited by: Charlotte Balmer on Jan 21, 2009 1:23 PM

[former_member583013]

How does my suggesstion in the previous post work for you. i.e., Using BP Properties

[jitin_chawla]

Hi,

If the version is 2007A you can check by right clicking on the Invoice and check the "Applied Transactions".

The Applied Transaction window will display your payments and Credit memos applied with the Invoice.

Check if that helps.

Regards,

Jitin

SAP Business One Forum Team

[Former Member]

I don't think using the BP Properties will work because it could be that we credited them in the past but in the future they may become a good client again.

Thanks,

Charlotte

[Former Member]

Hi Jitin,

I'm looking for a way to determine this information in a query rather than on an invoice by invoice basis. So right-clicking the invoice doesn't solve the problem.

The query is needed so that I can provide sales with a renewals list. They do not want to renew clients who never paid the invoice (ie. invoices that were fully credited).

Thank you,

Charlotte

[former_member583013]

Charlotte,

May I ask what would the purpose behind this information?

There are many more scenario's like partial payment and partial credit memo and so on. It would be difficult to have one clear cut solution to these scenario's

Suda

[JesperB1]

HI Charlotte,

Are you using version 2005 or 2007?

Let us know.

Jesper