In doing some research for a client, I discovered an apparent inconsistency in the methods used within Sage 500 to calculate the values in tarCustStatus.AvgDaysToPay and NoInvcInPmtAvg.
When calculated in sparModPostPmtApplCS (called from sparModPost), the calculation (for each payment being posted) takes the current average days to pay and calculates in the new days to pay for the current invoice payment application being processed. Next, it increments NoInvcsInPmtAvg, but only if the current number of invoices in the payment average calculation is less than the Number of Invoices in Customer Average as set in AR Options. This is correct, I believe.
However, the code in sparPostAppl4 calculates the Average Days to Pay (essentially) the same way, but always increments tarCustStatus.NoInvcsInPmtAvg. What this means is that the number of invoices in tarCustStatus.NoInvcsInPmtAvg could end up exceeding the limit set in AR Options when processed by this stored procedure. (Or, so it seems.)
The good news is that the only time sparPostAppl4 is invoked, it seems, is from sparPostInvoice, and the only time the section of code that updates AvgDaysToPay gets called is when there is a payment being applied at invoice creation. For many companies, this is a relatively rare occurrence, and for companies where it is not rare, they don’t process traditional accounts receivable because all of their orders are paid in advance (with a credit card, typically). For companies in the latter operating mode, Average Days to Pay is virtually always zero in any event.
Anyway, I thought you might want to be aware of this in case something arises with one of your clients in figuring out what is going on with Average Days to Pay calculations.
As a side note: For one client, I discovered dozens of customers with a negative value in Average Days to Pay. The reason is because they have hundreds of invoice payment applications where the TranDate for the CustPmt is actually before the TranDate of the Invoice. That appears to be a business process issue, and not a failure in Sage 500. It just makes the data look a little screwy.
As a further note, the method used in sparUpdateCustAvg (called when merging customers, from sparCustomerMerge) is entirely different from the other methods used to calculate Average Days to Pay.
When you merge customers, and sparUpdateCustAvg gets called, the Average Days to Pay calculation does not look at merely customer payments to satisfy invoices. It looks only at the difference between the Invoice TranDate and ClosingDate. This means an invoice closed for any reason—including an invoice disputed or otherwise in error, and closed with an internal credit memo—gets counted into the “days to pay average” even though no payment was actually received from the customer.
The methods used in the other procedures look only at customer payments and the days between the transaction date of the invoice and the transaction date of the payment. These two very different methods could lead to very, very different results—especially if there are a lot of errors, disputes and / or adjustments on a customer’s account—that is, offsetting invoices, debit memos and credit memos.
Furthermore, the procedure employed in sparModPostPmtApplCS and sparPostAppl4 both use a rolling average calculation method. The code in these procedures does not go back and reevaluate from scratch all of the transactions that should be included in the calculation of the payment average.
These are differences of which you should be aware, of course, in case you have questions about Average Days to Pay values appearing in your Sage 500 system.