Dynamics GP Receivables SQL Views
- All Posted Transactions Shows all posted AR transactions with friendly column names.
- All Posted Transactions Same as above, but without the friendly column names…in a PDF file.
- AR Apply Detail Lists all AR Payments, Credit Memos and Returns and their apply detail.
- Commissions Details SOP and RM commissions (for posted and unposted transactions).
- Current Receivables Aging Detail Shows one row per unpaid transaction with hard-coded aging buckets aged by due date.
- Current Receivables Aging Summary Shows one row per customer with a balance with hard-coded aging buckets aged by due date.
- Customer Shipping Addresses All details of the address selected as the Ship To address for each customer
- Customer Yearly Totals Shows calendar year totals for all receivables transactions – results are one row per customer with the years in columns.
- GL Distributions for AR Transactions Shows GL distribution details for all posted Receivables transactions.
- Unapplied Transactions Unapplied AR transactions with a calculation of how old they are.
Victoria, do you have anything for historical AR aging? The Current Receivables Aging Detail is perfect for this client…but they also need Historical Aging and grouped by National Account. Thanks!
Hi Gina,
I have created many historical aging reports, but they are a lot of work and are not something I am planning to post on the blog. If you’re interested in having a report created as a consulting project, please let me know.
-Victoria
Victoria, is there anything you have already posted which would produce a view that shows all receivables transactions, posted and unposted with commission amounts? GP report only shows posted invoices and there is no option to include unposted.
Hi Mark,
I have not done anything with commissions yet, but that is a good idea. Are you using Receivables transactions or SOP transactions?
-Victoria
I started off using the RM tables before I realized that the unposted invoices reside in the SOP tables. I back tracked and tried joining SOP10100 and SOP30200 filtering on SOPTYPE = 3 or 4 but I was not having any luck. Also, wouldn’t it not be a good idea to include credit memos which are in the RM tables – if a price adjustment to an item is entered you would not necessarily use sales transaction entry to make the adjustment.
-Mark
Mark,
I guess that would depend on how you are entering your transactions.
There is no one answer to this. What columns would you be looking to show?
-Victoria
Grouped by SalespersonID. Columns needed – Territory, Doc Number, Doc Date, Sales Amt, Comm Amt, Non-Comm Amt, Sales %, Comm %.
Invoices are normally handled through Sales Transaction Entry, however, if there is a price adjustment we are entering via Receivables Transaction Entry.
Mark,
You’ll need to do the grouping on your own, since I wanted to post something that was as generic as possible, however, I think my latest blog post will help: SQL view for commissions details in Dynamics GP.
-Victoria
I’ve been browsing around for a way to mass update Salesperson IDs on customer cards. I know PSTL has a Salesperson modifier but it doesn’t work for existing Salesperson IDs. Basically looking for a way to say “ASALESGUY” on all Customers needs to be replaced with “BSALESGUY”. Historical data doesn’t matter. It just needs to update all current customers with another Salesperson ID. I guess I could use Integration Manager after sorting out all the “ASALESGUY” IDs and doing an insert/update with “BSALESGUY” instead?
Thank you,
John
Hi John,
Yes, I believe using an Update integration with Integration Manager will do this. I’ll be honest, this is one change that I typically accomplish directly in SQL because it’s just easier and faster. Three things to keep in mind:
-Victoria
Thanks Victoria, I think I’ll go the SQL route. I thought about that too but didn’t know if it would mess up any other links to other tables. I’ll use the customer master and customer address master tables in SQL to make the change since it will be much faster. Thank you so much for your input and suggestions.
John
Oh, and just in case anyone else found this to make this change, you might want to check your table RM00303 to make sure the old salesperson ID isn’t assigned to any Territory IDs as well.
Thanks for the update John!
-Victoria
Hey Victoria,
I want to fashion an AR Aging report with 4 buckets. I can start with the canned GP aging. I have an SSRS solution and I have created a custom stored procedure based on the one the GP report is based on, ‘seeRMAgeTrailBalance’. I have no problem creating the 4 buckets but my controller would like me to add a parameter to run the report by the fist segment of the corresponding GL account associated with the transaction. I’m not an accountant so I don’t really know the correct terminology. I am quite SQL savvy however and will have no problem implementing the report once I know what the heck I’m doing. FYI: The first segment of our GL account is a division identifier. The canned report is based on RM20101, the open transactions table. I don’t know what table I would need to join to get the account index. Once I know that I think I can handle the rest. Thanks.
Hi Joe,
I believe you should be able to accomplish this with a combination of two of my views (or at least by looking at the code for them and combining some of the logic):
-Victoria
Victoria,
Actually I did look at GL Distributions for AR Transactions. The join looks like this:
FROM RM20101 RO
LEFT OUTER JOIN
RM10101 DO
ON RO.RMDTYPAL = DO.RMDTYPAL
AND RO.DOCNUMBR = DO.DOCNUMBR
LEFT OUTER JOIN
GL00105 G
ON DO.DSTINDX = G.ACTINDX
Shouldn’t there be a third join between RM10101 and RM20101 like this ‘and RO.TRXSORCE = DO.TRXSORCE’ ?
Joe,
DOCNUMBR and RMDTYPAL are enough to get a unique transaction, you do not need TRXSORCE.
-Victoria
Thanks, Victoria. This is working like a champ!
Hi Victoria,
This may be out of topic but I was wondering if you have any idea on how to add a running balance of outstanding amount on the Receivables Statement (GP Report Writer) for customers?
Any help would be greatly appreciated.
Thanks,
Saras
Sorry Saras,
This is not the best place to ask Report Writer questions, I would recommend asking this on the GP Forum: https://community.dynamics.com/forums/32.aspx.
-Victoria
Hello Victoria
Do you have a Sales/RM SQL view like the ■”All Posted Transactions” including unposted transactions please?
Thanks
Hi Lulu,
Do you mean from Receivables (as opposed to SOP)? I don’t have a view for unposted Receivables transactions, but can create one if that would help you. Do you want just transactions entered on the Receivables Transaction Entry window, or Cash Receipts as well?
-Victoria
Good point Victoria!
I’d forgetten about GP’s structure for SOP/RM information. We only post from SOP so my request is not a valid one. I have an SQL view for SOP transctions.
Thank yo for yo
Hi victoria
Do you have a view that would represent the aged trial balance with detail report.
thanks
Vic
Vic,
The closest would be my Unapplied Transactions view – you may need to add some filters to it.
-Victoria
Hello Victoria
I have the daunting & very interesting task fo reconciling our AR sublegder to our GL. I’ve been searching your blog for a suitable SQL view, however, I’ve yet to come across something.
You have a SQlL view that will return the data from the AR tables and the GL tables where the Customer ID and document number is the same perhaps?
I’ve tested the Reconcile to GL functionality in GP itself but I dont agree with the results! Any ideas which tables this report is using?
Any help will be appreciated,
Thanks in advance,
Lulu
Lulu,
I am not aware of any SQL code published that will do this, it’s actually quite difficult to put together everything needed for a true reconciliation. There is a product out there called The Closer that does a much better job than the GP reconcile functionality. If this is a recurring need, you might want to consider it.
-Victoria
Hello again Victoria
Thank you for your responses and help as always. I’ll have a look at the product you mentioned.
May I check with you how you & other consultants in the field are proposing to do this task to clients please? Do you run a Customer’s Aging report and then compare that manually to a General Ledger report? This is the way I was taught however I might not be aware of a more efficient way of doing things.
Lulu,
There is no one answer to this question, as a lot depends on the specific details, situation and tools available. And most likely every consultant has their own way of doing this – I cannot speak as to what others do. My personal method is typically a combination of SmartList and SQL searches, but it is not something that I have put together into any kind of write-up that I can easily share. There are two things I would recommend if you are doing this manually, without the help of any tools like the GP reconcile functionality of the The Closer:
1. Always use the Historical Aged Trial Balance and check all the settings on it to make sure they make sense for reconciling to the GL.
2. Figure out what period and year you started to not reconcile – go all the way back to the beginning if you have to. If there were beginning AR balances imported into GP and they did not match the GL, how can you expect to reconcile today? If the AR and GL have always reconciled and you can pinpoint the period when they stopped reconciling you can narrow down the time frame of transactions you have to examine significantly, saving a lot of time.
-Victoria
Victoria,
Your SQL views are very helpful. There is no other source like yours for GP database help on the web! Truely grateful for the help I have found on your blog.
Do you have a way to view the unapplied transactions at a certain point in time? Wondering if I could see the transactions unapplied as of a past month-end so I could compare it to today.
Thank you for all the great SQL!
Jay
Jay,
Thanks for the kind words, I am glad my SQL views are helpful.
A point in time report is what’s typically referred to as a ‘historical aged trial balance’ (HATB). While possible to recreate in SQL , this is a very difficult and time consuming process and I don’t know of any reliable scripts for this posted anywhere. Because of this, most people use the built in GP reports for the HATB. If you absolutely need it in SQL, then you could create a ‘wrapper’ around the GP stored procedure(s) for these reports, but that’s going to take some effort and testing also.
-Victoria
Victoria,
Your SQL queries are a great help. You have saved me plenty of work re-creating the wheel. I am hoping you can help me figure out how to do a new query. Basically I need a query that returns “Short pays”. When the customer send payment for an invoice but does not pay in full. In our business (freight logistics) this happens quite frequently due to freight carrier charge disputes. We need to be able to identify the short pays so that we can research and keep track of these. Your AR Apply Detail query gets most of the information I need except how to tell if it was a short pay (Not for the full amount of the invoice).
Any help would be greatly appreciated.
Thanks,
Keith
Hi Keith,
I just added a column at the end of the AR Apply Detail view to bring in the original amount of the Apply To transaction. I think this should accomplish what you need.
-Victoria
That is great. Thank you.
One more question. Does Dynamics stored balance due in the tables anywhere or is it calculated at runtime? I so far have not found a location for balance due. This would be a way for me to tell if the short was paying off the balance due or not. If the short pay is paying off the balance then I do not need to research them.
Thanks again,
Keith
Keith,
Are you saying you want to identify if the payment is paying off the balance at the time? So…if an invoice was originally short paid and now the balance is being paid, that should not be flagged, only the original short pay? If so, the only way I can think to do this easily would be to look at the Date Invoice Paid Off and compare it to the Payment Date. This logic will only work if those 2 days are the same. I just added this at the end of the view for you.
You would need to examine your data to see if this logic works. Otherwise, you’d need to add some more complicated logic to your report comparing the amount paid so far to the amount of the current payment being evaluated to determine if it’s a final payment or a short pay.
Hope that helps.
-Victoria
You ROCK!
As luck would have it, it looks like our data will support this logic. I appreciate all your help. I was going crazy looking for balance due field.
Keith
Victoria –
I’ve been following your blog and have been a fan for some time now. You always manage to bring something “extra” to the table and for that, we’re all grateful.
I was wondering if you have created or come upon a resource that would provide historical AR balances (say at month end) for a selected date range. It seems to me that running a historical AR aging report for each month end for a year (or more) might be a little tedious.
What do you think?
Regards,
Ken Levy
Hi Ken,
Thanks for the kind words!
Recreating a historical AR (or AP or Inventory, for that matter) report is a pretty difficult process and I do not know of any free resource available for this. The reason for this is that there is a huge amount of logic that goes into creating these reports. So while running the GP report may be a bit tedious if you need to do it over and over, it’s probably still a lot easier than having to recreate the logic that goes into it.
If you’re good with SQL Server coding, you could create a stored procedure that calls the existing GP stored procedure for this report, but I would expect this to be quite a bit of work as well (and I have not done this myself).
-Victoria