Average Days To Pay Calculation – History & Open (SQL Script)


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.

RM_AverageDaysToPay.sql

VAIDY

Advertisements

One thought on “Average Days To Pay Calculation – History & Open (SQL Script)

  1. Vaidy,
    Is the column Amount_days_outstanding the same as average days to pay? Thanks for the script. this is very helpful.

    tim

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s