860
So Im building a query that looks at data in GP to reconcile invoices with outstanding balances. Disregarding invoices for a moment and starting with just the AR tables (RM10301, RM20101, RM30101 ) , the total on the Aging report (doing just a summary) is not even close. What is the aging report doing other than the following:
( Debits + Invoices ) – (Credits + Payments)
*This post is locked for comments
Thanks Victoria. I'm a .net developer, not an accountant. I have a resource to use for help with GP and after talking to them, I confirmed what I suspected - and what you just mentioned as well. That, the historical report is doing way more than what I was, so coming up with the same numbers was going to be nigh impossible.
Long story short, were getting help 
The title of your post says 'historical aged trial balance', but your post talks about outstanding balances, making it seem like you're looking for the current balance. These are very different.
If you're looking to duplicate the historical aged trial balance in SQL on your own, you have a LOT of work ahead of you. There is a lot of very complicated logic based on a number of parameters and additional tables.
If you're looking for the current balance, you only need the RM20101 table. RM10301 is unposted transactions and RM30101 are historical transactions, neither are part of the current balance.
For the current balance, in RM20101, look at the CURTRXAM, add the CRTRXAM from any transaction with RMDTYPAL < 7 and subtract all other transactions. That should total your current aging.
As Deanne mentioned, there are a number of scripts on my blog that can give you more help with the current aging.
You should look at www.victoriayudin.com
She has all the sql you could ever want!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (