In my previous post, Average Days to Pay Calculation (SQL Code), I had provided a SQL stored procedure that calculates a customer’s ADTP for a given point of time (between two dates). While this was perfect, it does not include fully applied but open invoices.
Some of the readers (particularly Tim and Steve Pena) requested to amend the script to consider open invoices that are fully applied. An invoice remains open even after fully applied only when we do not run paid transaction removal (PTR). I wanted to work on this script as soon as possible, but somehow I could not.
Better late than never, isn’t it?
Please find the link below to download the SQL procedure that calculates a customer’s ADTP for a given point of time, but looks at both history (RM30101) and open (RM20101) tables, take invoices that satisfy following criteria:
- Invoices that are fully applied.
- If invoices are in history table, by default, current transaction amount would be zero.
- If invoices are in open table, then take those invoices whose receivable outstanding amount is zero.
- Invoices that are not voided.
- Invoices that have a document amount, meaning non-zero.
I have verified this script against some sample customers for whom, invoices were either in history (RM30101) or in open (RM20101) or in both.
As always, feedbacks are welcome.
VAIDY
Dynamics GP - Learn & Discuss 
Vaidy,
Is the column Amount_days_outstanding the same as average days to pay? Thanks for the script. this is very helpful.
tim
LikeLike