We have created invoices for a customer and the customer has paid all but the retainage on 2 of the invoices. How can I get the report to pull in the Inital amount, the Paid to date amount and the Unreleased retainage? My report currently only has the initial invoice amount.
Solved
How can you bring in the amount a customer has paid against an open invoice?
Best answer by Steve Milner
@aaronslim, your spreadsheet shows 0.00 in Original Retainage and Unreleased Retainage on both invoices, and Total Amount equals Amount. So Acumatica has no retainage recorded on those two. Whatever the customer held back is part of the regular Balance.
That export is the Invoices and Memos list, and it has a Balance column that's hidden by default. Open the gear at the top left of the grid (Column Configuration) and check Balance. Paid to date is Amount minus Balance. It covers everything applied and released against the invoice, so an applied credit memo counts along with customer payments.
If you'd rather see paid to date as its own column than subtract in Excel, build a generic inquiry on ARInvoice and add a result column with this formula:
=[ARInvoice.CuryOrigDocAmt]-[ARInvoice.CuryDocBal]
You'll see a Total Paid field on ARInvoice too. I'd leave it out. It counts applications that haven't been released yet, so it can run ahead of what's actually been paid.
On invoices that do carry retainage, Amount excludes it, Total Amount adds it back, and Unreleased Retainage holds it until you release it. Once released, the retainage bills on its own invoice with Retainage Document checked and Orig. Ref. Nbr. pointing back to the original. Payments against it land on that invoice.
+6@aaronslim, your spreadsheet shows 0.00 in Original Retainage and Unreleased Retainage on both invoices, and Total Amount equals Amount. So Acumatica has no retainage recorded on those two. Whatever the customer held back is part of the regular Balance.
That export is the Invoices and Memos list, and it has a Balance column that's hidden by default. Open the gear at the top left of the grid (Column Configuration) and check Balance. Paid to date is Amount minus Balance. It covers everything applied and released against the invoice, so an applied credit memo counts along with customer payments.
If you'd rather see paid to date as its own column than subtract in Excel, build a generic inquiry on ARInvoice and add a result column with this formula:
=[ARInvoice.CuryOrigDocAmt]-[ARInvoice.CuryDocBal]
You'll see a Total Paid field on ARInvoice too. I'd leave it out. It counts applications that haven't been released yet, so it can run ahead of what's actually been paid.
On invoices that do carry retainage, Amount excludes it, Total Amount adds it back, and Unreleased Retainage holds it until you release it. Once released, the retainage bills on its own invoice with Retainage Document checked and Orig. Ref. Nbr. pointing back to the original. Payments against it land on that invoice.
Translate
Thank you
I’m trying to follow your last paragraph regarding retainage. So in my updated query you can see the balance remaining of $12,242.71. Why would this not be in “Retainage” column as well? Does this have something to do with how the data is input originally?
Translate
+6@aaronslim, glad it helped. One correction first: it was the Total Paid field I'd skip, not paid to date. Amount minus Balance is the right paid-to-date number, so keep that one.
On your question, yes, it comes down to how the invoices were entered. Retainage isn't Acumatica's name for whatever the customer didn't pay. It's set on the invoice lines when the invoice is created, with Apply Retainage checked. Original Retainage then holds that amount and Amount is reduced by it. Your two invoices went in without it, so the $12,242.71 the customer held back is just an unpaid balance as far as Acumatica can tell.
Apply Retainage locks once an invoice is released, so you can't add it to these two now. Going forward, turn on Apply Retainage in the retainage settings on the customer record (Customers, AR303000), and new invoices for that customer will default to it. The default percent comes from the project's Retainage (%) when the invoice has a project, and from the customer's Retainage Percent when it doesn't.
If you need these two to show the retainage, the help's fix for changing retainage on a released invoice is Reverse and Apply to Memo, then a new invoice with the right retainage. Both already have payments applied, so try it on a test tenant first.
Translate
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.