Points: 10450

5/25/2012 10:09:54 AM

RM Historical Aged Trial Balance

This script is worth the price of admission.

I continue to get requests for variations on the RM Aged Trial Balance report. The idea with an RM Aging is that you need to first get a query of all the documents (open and history, invoice and payment) and then get a query of the applies - carefully noting the apply date.

Then you can pick a point in time, get all docs prior to that date and apply all the applies prior to that date.

You have to handle trade discounts and writeoffs. These two we bundle as 'reductions'; they're not attached to the document but to the apply itself.

The query below is a variation to the usual theme, the client wanted a 'starting balance', 'ending balance', and all transactions in between.

The end result looks like this:

Version: All
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables