Finding AR Balance Report Details

G
  • garyb
  • Known Participant
  • 61 replies

Currently using E10.2.500.13

We have a case where we are giving a cash rebate to a certain customer, based on their current total AR balance.  With this rebate, we need to break in down to the line level, as some part groups receive a larger rebate percentage than other groups.

We use the Aged Receivables Report to get the total current AR balance, however I cannot find any report that gives any detail information.

Is there a way to get which invoice lines are applicable to the current open invoices with a balance?  Even if this is done with SQL, I’m good with that.  So far I have not found any detail records in the tables when it comes to open AR balances.

Thanks.

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com

5 replies

T
Userlevel 1

Hi Gary,

I believe a simple BAQ will hep you to have the detail of your open AR by Line.

Using those two tables : 

 

 

You can also create two BAQ one with the “Open Invoices” and one “with the Lines”. Then you add them on a Dashboard and when you select the open invoice from the first BAQ automatically it will show you the line that belongs to this open invoice.

The advantage of the BAQ is that you can call them by REST and expose them in Excel for example, so you can use this to automatically create the calculation of your Rebate.

I hope the above help you,

Thierry

Platinum Services Europe

tco@pse.be

 

Thierry Cools . Managing Partner Platinum Europe SA (Platinum Services Europe) Rixensart (Genval) BE www.platinum-services-europe.eu
G

Thanks Thierry. 

I have done what you suggested.  The issue we have is what to do when the customer makes a partial/short payment against an invoice.  As far as I know, when you apply the cash payment you cannot specify which lines the payment is for, let alone knowing what lines the customer is paying.

For example, an invoice has a total of 70500 and has one cash application of 70000, which leaves a 500 balance.  We do not know what lines are still unpaid, and we are not giving the rebate for lines already paid for.

I think this may be the case of telling AR that it’s not possible to know which lines are left to be paid.

Thanks again for your suggestion.

 

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com
T
Userlevel 1

Hi Gary, I understand. Yes, you are correct. in standard, you can not specify what line you are paying.

One solution, will be to use a user defined field in the payment process and ask the person that input the cash receipt to specify what line is paid and then use that field in the BAQ I suggest you to do.

 

If it can not be done at the cash receipt moment, an other solution will be to list all the Invoices that are partially paid and their lines and using an “Updatable” BAQ update an UD field to specify the line paid or unpaid.

I hope this can help,

Thierry 

Thierry Cools . Managing Partner Platinum Europe SA (Platinum Services Europe) Rixensart (Genval) BE www.platinum-services-europe.eu
G

Thanks.  We’d do that if we could get our customers to specify which lines are being paid.  😃  They just supply the invoice number (or multiple invoice numbers) with the payment. 

I will probably just give a list of the lines for all open invoices and let AR do the rest how they want.

Gary Browning Tube Forgings of America Inc / The Zidell Companies garyb@tubeforgings.com
T
Userlevel 1

So, in that case a dashboard with an Updatable BAQ will be the best solution, so your AR department can call the custmer and ask what line has been paid.

 

Have a nice Friday !

 

Thierry

Thierry Cools . Managing Partner Platinum Europe SA (Platinum Services Europe) Rixensart (Genval) BE www.platinum-services-europe.eu

Reply


Terms & Conditions