Sage 100

v2017 ADV PU2 - AR Invoice History inquiry - Balance field. Where is Sage pulling this information from?? Client is wanting way to print invoice from history showing that the invoice is paid in full. Information doesn't appear to be in the AR Invoice History Header table. Where should I be looking? TIA

I believe it is AR Open Invoice as that is the table with balances.

My bet is AR_TransactionPaymentHistory. I will test.

It's in AR Open Invoice as Michele mentioned.

Close, but I don't think there is a cigar there yet. Looking at another one from 10/19/16. Panel shows .00 balance due. There is nothing in open invoice table and nothing in transaction history.

NOT AR_TransactioPaymrntHistory. I belive Michelle is correct. There is a balance field in AR_OpenInvoice. Seems strange though because AR_InvoiceHistory is retained indefnitely.

Jeff... I just changed the ""balance"" field in AR_OpenInvoice and it changed the balance field in AR_InvoiceHistory for the invoice I changed.

Try changing AR_OpenInvoice field 34 in DFDM in a test company.

The balance field in AR Invoice History may just be a screen display. For example, if there is a record in AR_OpenInvoice it uses field 34 else, if not, it is calculated in another manner such as using the records in AR_TransactionHistory or a calculation based on the fields in AR_InvoiceHistoryHeader.

Let's assume the invoice has been purged from the open invoice table, which is the case for my 10/19/16 invoice noted above. Where would the number come from then? I am thinking it is coming from multiple tables,

That's why I thought it was odd coming from AR_OpenInvoice for that reason. Open Invoice records get purged whereas the invoice history is kept indefinitely.

Exactly, but a paid in full invoice from last year with no details in open invoice and transactionPayment still show paid in full. Strangely, if the open invoice was paid with a credit card, the **reprinted** invoice shows paid in full. However, if paid with non CC funds, it shows as balance due when reprinting the invoice..... Puzzling

A lot of discussion. The balance is from AR_OpenInvoice table. If the record isn't there, then clearly it's already paid and balance is 0. Standard form always prints invoices paid by credit card as 0 (assuming you haven't deleted that part of the logic of the form).

@RobertWhite That makes sense. The open invoice record will remain unless the balance is zero.

@JeffSchwenk Regarding your comment ""Let's assume the invoice has been purged from the open invoice table, which is the case for my 10/19/16 invoice noted above. Where would the number come from then? "". You don't see a balance in AR Invoice History AND the AR_OpenInvoice record has been deleted do you? As @RobertWhite mentioned if the AR_OpenInvoice record has been deleted, the balance should be 0. It seems the balance displayed in AR Invoice History is not always the AR_OpenInvoice.balance field that is displayed. If the record exists in AR_OpenInvoice then the balance field is displayed, else, if a record does not exist in AR_OpenInvoice, a 0 is displayed.

Why doesn't your customer send their customer a statement??

@ThereseLogeais - Good question. They requested that I somehow print ""Paid in Full"" on the invoice when they reprinted it from history. Sometimes the invoice is completely paid via deposits in advance and sometimes not.

Reprinting an invoice is simply that - reprinting it. If they want to know the balance, that's what statements are for.

I agree with Therese, however, clients want what they want. You will have to take into consideration any payments, credit memos, and invoice adjustments. Or simply look at the AR_OpenInvoice file to see if the invoice has a balance. If it doesn't exist in this file, then the balance due is zero.

I suppose I could buy them a rubber stamp..........

You could also put in a parameter to print Paid in Full if they wish, e.g. Paid in full? If yes, print ""PAID IN FULL"" else """". They would have to choose it however....

You could join AR_InvoiceHistoryHeader to AR_OpenInvoice. If a record is found in AR_OpenInvoice and balance <> 0 then don't print Paid in Full, else print Paid in Full.