Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
Joel 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:

Enjoy!

4Penny.net
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