Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / AR Average Days to Pay...
Microsoft Dynamics SL (Archived)

AR Average Days to Pay calculation

(0) ShareShare
ReportReport
Posted on by Gail J-N 1,000

I've been digging into the AR "Average Days to pay" calculation that displays on the customer maintenance screen.   This field is actually the field AR_Balances.AvgDayToPay, and appears to be updated either when an invoice is closed for a customer, or when the AR integrity check is run (Rebuild History from documents or Correct customer balances ).   

Documentation of the process shows two flaws from our perspective:
- Partially paid but still open invoices are excluded
- Dates originating from a small balance writeoff document are included

Has anyone out there customized the calculation of average days to pay to correct either of these issues?   How did you do it?

BTW:   I hadn't caught that in our current version (V7.0 SP1) you can now set the Avg Days to pay data retention period.   Don't know when that changed but it's a nice feature.    Of course, you have to run an integrity check after changing the balance to get it update for the new retention period!   The process to update Avg Days to Pay does NOT run as part of closing at this point so it appears that I need to automate that myself.

Thanks in advance!

Gail J-N

 

*This post is locked for comments

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    RE: AR Average Days to Pay calculation

    Hi Steve,

    First, I need to correct my reply when I said that the Average days to Pay is not recalculated by the AR Integrity Check.  It is recalculated by running the Rebuild AR History from Documents.  

    There is no hot-fix for bug 17890.  There are workarounds such as running the stored procedure, running Rebuild AR History from Documents, or manipulating the setting in AR Setup.  I don't have an ETA of when it will be fixed. I apologize for the inconvenience.  I also apologize for misinforming you about the AR Integrity Check.

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    RE: AR Average Days to Pay calculation

    Carolyn.  

    I tested changing the Avg Days to Pay setting in AR Setup and that worked. We started by reviewing a few customers including the three we performed our own manual detailed analysis of invoices/payments.  Our current setting was 9 so I increased it to 10, saved and then back to 9 and saved.

    I then reviewed the customers ADP against what we had document prior to the change. In some cases, the change was minor + or - 2 days.  However, in a few cases, it was significant lower.  In the case of the customer who complained, the ADP was 18 days less (63 down to 45) .  45 was consistent with our manual analysis of their payment history.  

    Although we can schedule a manual process for updating the ADP using the above work around, do you know if this has been addressed in a HF for bug 17890?

    Thanks again!  

    Steve

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    RE: AR Average Days to Pay calculation

    Hi Steve,

    I don't have an explanation of why the NbrInvcPaid is off.  Have you ever refreshed the calculation using the stored procedure?  I also know that if you change the Nbr Pds in Avg Days to Pay setting in AR Setup, that also causes the system to recalculate the Avg Days to Pay.  

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    RE: AR Average Days to Pay calculation

    Carolyn,

    Thank you for the prompt response.  

    We ran some manual calculations for a few customers and observed that the ADP in SL was significantly larger than our manual calculations. While reviewing the fields in AR_Balances for one customers I noticed that the NbrInvcPaid was 122. In AR setup, our Nbr of Pds in Avg Days to Pay is 9.   There is no way this customer produced 122 invoices in 9 months.  So we built a spreadsheet of invoices for the past 5 years for this customer (AR Setup Years to Retain History is 5)

    In the last 5 years, we created 69 invoices for this customer. I must be missing something?

    Steve

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    RE: AR Average Days to Pay calculation

    Hi Steve,

    I would go ahead and use the stored procedure.  The AR Integrity Check doesn't recalculate the Average Days to Pay that I know of.  

  • Steve Wier Profile Picture
    Steve Wier 944 on at
    RE: AR Average Days to Pay calculation

    What is the best way to recalc the AR Average Days to Pay in SL2011?  Is execute P08990_arbalupd still a valid option or should I use AR Integrity check and if so, which option is recommended?

  • Carolyn Kolpien Profile Picture
    Carolyn Kolpien on at
    Re: AR Average Days to Pay calculation

    Below is some information on the Average Days to Pay calculation.

    A.  Partial payments are not included in the 'Avg Days to Pay' value displayed in Customer Maintenance.

    1. The Average Days to Pay calculation computes only on closed documents.

    2. The Invoice Document Date is compared to the date of the payment and the calculation of the number of days to pay is made.

    3. For example, if an Invoice is entered for $100 with a July 25 document date and a partial payment of $10 is made on August 1, the Average Days to Pay field is not affected. If another payment for $90 is made on August 20, the invoice is now closed and the Average Days to Pay field will reflect 26 days for that invoice.

    B - Verify the Avg Days to Pay calculation using fields in the AR_Balances table.

    1. AR_Balances.NbrInvcPaid is the number of invoices paid within the retention period for calculating average days to pay. This field is set during the closing process and when an invoice is paid completely in the Payment Application (08.030.00) dialog box or in the Release AR Batches (08.400.00) process for auto-apply customers.

    2. AR_Balances.PaidInvcDays is the total days outstanding for invoices paid within the retention period for average days to pay. This is also updated in closing and is updated by the release process for invoices paid completely in the Payment Application (08.030.00) dialog box or in the Release AR Batches (08.400.00) process for auto-apply customers.

    3. The AR_Balances.AvgDayToPay field is calculated from the AR_Balances.PaidInvcDays value divided by the AR_Balances.NbrInvcPaid value.

    C.  The Average Days to Pay is not recalculated upon closing the AR module in GL.  This is documented by bug 17890.  To recalculate the Average Days to Pay manually, run the following statement on the application database in SQL Server:

    execute P08990_arbalupd

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans