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).
- Compare Voids in RM and SOP modules Shows a list of all transactions voided in one, but not both modules.
- 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.
Dynamics GP Receivables SQL Stored Procedures
- Unapplied AR Transactions by Customer Name Unapplied/open AR transactions with a calculation of how old they are; parameter is ‘Customer name contains’.
Hi Victoria, Really appreciate the work you have done. I want to build an aged trial balance report for my Customer/Vendor “CV” transactions but could not find it anywhere. Do you have any coding for that. I just want to see all the transactions from my customers, who are also the vendors, combined in one ledger. So I don’t need to open separate trial balance reports from RM/PM modules.
LikeLike
Hi Arslan,
I don’t have anything that combines vendors and customers. For what it’s worth – this is not a common ask. In fact, I can’t remember ever being asked for this before. Nor is it easy, as there are 2 completely separate sets of tables that are needed for this. You can certainly find the AR and AP code separately on my blog and then combine them to get the results you need.
-Victoria
LikeLike
Hi Victoria;
You mention you have code for an AR aged trial balance? I wasn’t able to find it in your blog. Would you be able to point me in the right direction? Our users would like to be able to export a trial balance into Excel. Thank you!
LikeLike
Hi Merri,
That code took me an incredibly long time to create, so I do not publish it for free. If you’re interested in purchasing it, please let me know.
-Victoria
LikeLike
That makes sense! I’ll have to check with the decision makers on that. Would you be able to send me a quote?
LikeLike
Hi Merri,
I will email you directly about this.
-Victoria
LikeLike
Victoria,
Thank you so very much for your amazing site!!
I’m using the AR Distributions view as the basis for a SmartList, but for some reason there’s an unposted batch that is not listed. I’m not sure where to start looking because some unposted batches do appear. If you need more information in order to respond, please let me know. Thanks!
LikeLike
Hi Holly,
The results for that code should only be including posted transactions. Not sure why you’re seeing unposted stuff in there, Was my code possibly altered when the SmartList was created? Or were other things added to it in SmartList?
-Victoria
LikeLike
Victoria –
I’ve been challenged with producing a report which shows the last activity for customers. I took the one you created for vendors, modified it slightly to which I think will work, but am looking for another set of eyes to confirm. Do you think this will work?
select
rtrim(c.CUSTNMBR) Customer_ID,
rtrim(c.CUSTNAME) Customer_Name,
max(coalesce(d.DOCDATE, ‘1/1/1900’)) Last_Activity,
c.CREATDDT Created_Date,
Case c.Inactive
When 0 Then ‘Active’
When 1 Then ‘Inactive’
end as Customer_Status
from RM00101 c
left outer join
(select CUSTNMBR, DOCDATE from RM10301
union
select CUSTNMBR, DOCDATE from RM20101
union
select CUSTNMBR, DOCDATE from RM30101) d
on c.CUSTNMBR = d.CUSTNMBR
group by c.CUSTNMBR, c.CUSTNAME, c.CREATDDT, c.INACTIVE
LikeLike
Hi Sean,
This looks good to me. 🙂
-Victoria
LikeLike
Victoria,
Thanks for all the years of great info. Had a new one that looking for guidance on…..looking at AR Current Bucket. Want to breakdown by month due for the next 6 months, and then anything over 6 months going into a futures bucket. Have a client w Terms way out in the future….so current bucket has millions in it…but trying to get a management report to show when the AR is actually due. So not really a Cash flow….more a of an expected Cash inflow……Hope that makes sense. Have you ever done anything like this? Assuming I could model after some of the Sales by month logic you have in others. That’s the path I am going to go down…but thought I would ask in case you have already done. Gotta love how every business is different!!
LikeLike
Hi Steve,
I think you can use something like my current aging code (https://victoriayudin.com/2013/05/01/sql-view-for-current-receivables-aging-detail-in-dynamics-gp/) and just simply change the aging bucket logic to whatever makes sense for this particular situation. If you need help with the specific coding, let me know.
It’s always like this, no matter what code you have, each company will need something a little different. I pretty much expect this now.
-Victoria
LikeLike
Hi Victoria,
I have found some invaluable information in your blogs. Thank you and keep publishing as it has been extremely helpful to me and many others as well.
I have a question for you. I have been entering additional Credit Memos details in the record level notes as there is really nowhere else on this screen to enter them. I would also like to print this additional data in my printed form. Is there a way to include them on the printed credit memo using report writer?
LikeLike
Hi Jackie,
Thanks for your kind words. Unfortunately, I can’t help with this question, as I have a rule to not use Report Writer if I can help it. You might be able to get help with this on the Dynamics GP Community Forum. Lots of smart folks there. 🙂
-Victoria
LikeLike
Victoria,
Can you think of a reason why the code here would produce different results than the GP Aged Trial Balance with Options report? The total is spot on but the aging buckets seem to be off from one another.
Thank you
LikeLike
Sean,
I believe the GP Aged Trial Balance with Options report is using the aging saved in GP for each transaction. So that will only get updated when you run the Aging routine. Also, you might have some specific aging settings in the Receivables module that are different from what I am doing in the code. My code is ‘manually’ calculating the aging. That’s nice on one hand, as it gives you a lot more control and does not force you to have to run the again routine in GP. On the other hand, you get this issue where the totals are off. Hope that helps explain it.
-Victoria
LikeLike
Victoria,
I appreciate the details. There is a substantial difference in one of the aging buckets so I am going to dig through the data between the 2 reports and see if I can discern any of the specific characteristics that caused the mismatch. If I find anything helpful that could be useful for others I will reply back.
Again thank you for the feedback and quick response.
LikeLike
Victoria, where can I find the Originating Currency in A/R tables? I want to modify your view to show amounts in Euro Currency for our European customers only. Thanks in advance.
LikeLike
Carlos,
The MC020102 table has the Multicurrency details for receivables transactions.
-Victoria
LikeLike
Victoria –
Thanks so much for your SQL code for AR and AP agings. These have been a lifesaver for us!
We wanted to see if you also had information on how to export copies of invoices sent to a folder on our network (currently we used the GP email feature with the pdf of the invoice as an attachment). Right now the only copy we seem to have is in the email and we would like to make the invoices available for others to see (who aren’t GP users, but who manage the customer relationship). We would also need a copy of the customer name as part of the filename, since the pdf files from the email only have our company name and the invoice number (which would make it hard to search). Is this possible?
Thank you!
Debbie
LikeLike
Hi Debbie,
Sorry, I am not aware of a way to do this. 😦
-Victoria
LikeLike
Hoping you might have some direction for us on customizing the “RM Summary Aged Trial Balance – Options” report. We are trying to implement a “future due” bucket (which is not currently supported). This report uses a “RM Document Temporary File” table. Any idea what GP table(s) and/or stored procedure(s) are used to build that table?
LikeLike
Hi Mark,
I think it would be very difficult to do this directly in GP. What I would recommend instead is creating your own custom report that adds the future bucket using something like SmartList, Excel or another reporting tool. Since the RM Summary Aged Trial Balance – Options is not a historical report, it should be pretty straightforward. You can use my code here as a starting point.
-Victoria
LikeLike
Hi Victoria,
Do you know where we can add a custom range in the Receivables Trial Balance Report Options window? We would like to add the range SITE ID to see if we can extract A/R reports by SITE ID.
LikeLike
Hi Leigh,
I suspect it would take a pretty serious customization to accomplish this. You might be a lot better off with custom reports to accomplish what you need.
-Victoria
LikeLike
Hi Victoria,
Would you give me a recommendation on where/how to retrieve this information. My company need to regularly see the Aging Detail with any outstanding SOP_Number and any/all associated Item_Numbers for that customer. I’m thinking that this will have to be done in 2 views and then combine them somehow. Any suggestions? Is there anything out there currently that I can use to achieve this?
Customer_ID, Doc_Type, SopNum, ItemNum, DocDate, DueDate, DocAmt, UnappliedAmt, Current, 0 to 30, etc.
LikeLike
Debbie,
There is nothing in GP to show this, you’ll have to create a custom report. If you have SmartList Builder or SmartList Designer you can do it there…otherwise, you could do this in Excel or some other reporting tool. In any case, I would create one SQL view to get the information.
-Victoria
LikeLike
Ok, thanks Victoria. That’s what I thought. Just wanted to make sure there wasnt something there already. Thanks again!!
LikeLike
Hi Victoria –
Thanks so much for maintaining your site. It’s a great source of information – especially considering the sparse resources available to a GP new-comer.
I have what I think will be a relatively simple question. I need to identify all invoices by way of an SQL query where the sum total of all payments received is less than the total amount due on the invoice. I can write the query but I’m not sure where all I need to look to get all invoices and all payments with the right selection criteria. I’m not asking you to write the query for me but if you could point me in the right direction in terms of tables and criteria (hmm – that’s pretty close to writing the query for me, isn’t it?). In any case, I would really appreciate your insight.
thanks in advance,
dgg
LikeLike
DGG,
Are you looking to get what GP says is due on each transaction? Or are you looking to recalculate it to make sure it’s correct?
If the former, then you can just look at the CURTRXAM in the RM20101 table. If a transaction is not in that table, it’s either not posted yet, or historical, meaning it’s fully paid.
If the latter, you can use something like my AR Apply Detail view to see all the details of the apply transactions.
Hope that helps,
-Victoria
LikeLike
Thanks for your response. The first scenario more closely resembles my goal. I have a data replication process I’m writing to bring invoice data into Salesforce for all invoices with an unpaid balance. The data resides in GP and a GP add-on product by the name of 1Staff (aka, PAM). Accurate selection criteria / sources and marrying the data up has proven to be a bit challenging but I’ve been able to find what I need with the exception of the “open” invoices. I had already found your “view_Current_Receivables_Aging_Detail” but I’ll take a look at your “AR Apply Detail” view also.
thanks again,
dgg
LikeLike
Hi Victoria
I just found your site, it is great! I am not really versed in SQL but I have followed how create view in SQL. Is there a way yo export the view after it is run to excel or cvs file?
LikeLike
Hi Herb,
If you are returning results to grid in SQL Server Management Studio, you can simply copy and paste the result from the grid to Excel.
-Victoria
LikeLike
Hi Victoria,
Found out what the issue was. Aging in our reporting is based on doc date.
LikeLike
Victoria,
I have greatly benefitted from your blog posts, so a big THANK YOU for all the work you do ! Had a quick question on AR aging.
The AR aging reports in GP are aged based on document date or posting date. Is there a way to get aging reports (current and historical) based on days overdue, i.e using the due date? We are trying to build a dashboard to show trend of invoices overdue aged in 0-30, 31-60, 61-90 and >90 day buckets,
Thanks !!
David
LikeLike
Hi David,
You can change your Dynamics GP aging reports to age by the due date instead of the document date – that is a setting on the Receivables Management Setup window (Microsoft Dynamics GP | Tools | Setup | Sales | Receivables). If you decide to change the setting, you will want to get all users out of GP and run Reconcile before running a trial balance report.
Hope that helps,
-Victoria
LikeLike
Thanks Victoria. It did help.
-David
LikeLike
Hi Victoria-
I’m new to GP, but not to SQL.
Suffice it to say, I’m not familiar enough with the table relationships yet, so I apologize in advance for asking a silly question.
Second- I’m not an accountant, but I am trying to pull together a report for my management team.
Question: does gp store EOM Balance data for each customer? By year and month ideally- but this is simple to filter down. We’re trying to see if our customers are using our organization like a bank- leveraging/using their existing credit against their purchases. (We’re in manufacturing.)
The thought is to see if any relationship exists between monthly sales figures and monthly “debt” figures-
I’ve found a few reports you produced which are great! Yet they show existing unapplied balances. Presumably, when these balances hit “0”, those “query” records will no longer exist, and the end of month balance owing will also become “0”. We hope to capture the end of month balances as a snapshot in time, to compare how they move against existing sales, and be able to look historically for the last two years (we’ve had GP since 2009). My hope is this record keeping device exists.
I hope my question is clear enough-
Thank you for your time, and thank you for what you do. 🙂
It’s extremely helpful.
~Cheers and best wishes,
LikeLike
Hi Matt,
Dynamics GP does not store end of month balances for customers. However, it does have the ability to print a historical aged trial balance report, which will give you the balance for each customer (and the details behind it) for any date you want. Your GP users should (hopefully) already know about this and can show you this. Hope that helps. 🙂
-Victoria
LikeLike
Victoria, Do you have any code for the historical aged? I will find it…but I just heard about this site and as I am working on a new project in a new location…this would be much more efficient.
Thanks…this site looks awesome!
Debbie
LikeLike
Hi Debbie,
Thanks for your kind words! I do have code for a receivables aged historical trial balance, but it took a huge amount of work to develop and typically needs to be a bit customized each time, so I do not plan on posting it on my blog. If you’re interested in having that done as a consulting project, please let me know.
-Victoria
LikeLike
Hello Victoria,
I trust that you are well. Firstly thank you for all the details you have provided so far. Much appreciated.
Do you perhaps have a view that I could use in Smartlist builder that will be able to give me subtotals / totals per ageing bucket at the end of each customer?
Regards,
Jay
LikeLike
Jay,
I think you are looking for my SQL view for current Receivables aging. Note that the aging buckets are hardcoded, so if you want something different, you would need to change the formulas accordingly.
-Victoria
LikeLike
Victoria,
Could you please send me the Detail and Summary AR Historical Aged TB’s?
LikeLike
Hi Rossangel,
If you are looking to have these reports created for you, we can discuss your requirements and I can let you know what it would cost to put that together as a consulting project. Please let me know if you are interested in that.
-Victoria
LikeLike
Hi Victoria,
Is there any better A/R Invoice Apply process? We have thousands of Invoices in GP for which we would like to apply the payment and the Autoapply process picks up the oldest transactions first if at all they were not cleared. If you could please let me know anything better that would be great.
Thanks,
LikeLike
Far,
GP only allows auto-apply by oldest Due Date or Document Number. There is a tool available for free from the Microsoft Professional Services team called ‘RM Auto Apply’ that may give you some more flexibility. I have not used it, so I cannot say for sure. You would need to ask your GP Partner to download it for you.
-Victoria
LikeLike
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!
LikeLike
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
LikeLike
Thank you Victoria! How do I contact you directly? Or would you please email me?
Gina
LikeLike
Sometimes we receive payment from customers before we have had the chance to create an invoice. Therefore when we receive their payment, we create a payment for the customer using the Cash Receipt Window (Trx-Sale-Cash Receipt). However, since there is no Invoice to applied it to we leave the payment unapplied. Normally, this would mean that when we run an aging report, the customer account would show up with an unapplied payment. However these unapplied payment have not been showing up in the latest report we have made.
How can we get these unapplied payments to show in our aging report?
LikeLike
So, to make sure I understand, you created a custom report for this? If so, I am not sure how someone can help you with it without seeing it… If I am misunderstanding, can you please explain in more detail?
-Victoria
LikeLike
Hi victoria,
I create an A/R report by going to Report-Sales-Trial Balance-Historical Aged Trial Balance.
LikeLike
Luis,
The out-of-the-box AR Historial Aged Trial Balance report should include these transactions. What options are you selecting in the Exclude section when you run the report?
-Victoria
LikeLike
Victoria,
The boxes that are selected to be excluded are “Fully Paid Docs”, “Zero Balance”, and “No Activity”
LikeLike
Luis,
I typically choose to exclude the following:
Can you try it with those and see if that makes a difference?
-Victoria
LikeLike
Victoria,
Thank you for your help.
I tried the making a report with the exclusions you suggested and the only difference is that invoices that have a payment document applied to them have reappear. This is not the items I was hoping to get back.
I want the unapplied payment documents to still show up. As you mentioned these transactions would normally show up without us having to choose any other options.
LikeLike
Luis, not sure what to tell you – my out-of-the-box report shows unapplied cash receipts properly. Either there is an issue with your report or with the data. I would recommend asking your GP Partner or Dynamics GP support for help on this.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Thanks for the update John!
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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’ ?
LikeLike
Joe,
DOCNUMBR and RMDTYPAL are enough to get a unique transaction, you do not need TRXSORCE.
-Victoria
LikeLike
Thanks, Victoria. This is working like a champ!
LikeLike
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
LikeLike
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
LikeLike
Hello Victoria
Do you have a Sales/RM SQL view like the ■”All Posted Transactions” including unposted transactions please?
Thanks
LikeLike
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
LikeLike
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
LikeLike
Hi victoria
Do you have a view that would represent the aged trial balance with detail report.
thanks
Vic
LikeLike
Vic,
The closest would be my Unapplied Transactions view – you may need to add some filters to it.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Hello Victoria,
I just love your site. I come here almost everyday and there is always something new to learn.
I have a question about RM Apply To Doc SQL View. It is in functional currency. Is there any way to include the Originating Currency in the report? We have a lot of customers in Europe and they pay in EURO. We should be able to answer their questions on exchange rates.
Thanks a lot for your help in advance
-Paul Chacko
LikeLike
Hi Paul,
Thanks for your kind words about my blog!
I was not sure which view you meant, are you looking at the AR Apply Detail view? Or just all of them in general? I do not have too many requests for multicurrency, so it’s not something that I have readily available and it would require additional work and testing. I can put this on the wish list, but in all honesty, it’s not something that I foresee adding any time soon.
If you are interested in having this created for you as a consulting project, please let me know.
-Victoria
LikeLike
Hi Paul – I know it was some time ago but did you ever get this View created for multicurrency – I am needing the same thing for USD vs CAD which is our functional currency.
Jacqui
LikeLike
Hi Jacqui,
I have modified Victoria’s original SQL view to get the report I wanted and it seems to be working fine. Haven’t heard any complaints from the users.
Would you like me to send it to you? Let me know. My work email is pchacko@vidyo.com
Thanks
Paul Chacko
LikeLike