Receivables SQL Code


Dynamics GP Receivables SQL Views

Dynamics GP Receivables SQL Stored Procedures

112 Responses to “Receivables SQL Code”

  1. Arslan Khalid Reply February 12, 2022 at 5:44 am

    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.

    Like

    • Victoria Yudin Reply February 13, 2022 at 6:38 am

      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

      Like

  2. Holly Reply September 5, 2019 at 2:53 pm

    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!

    Like

    • Victoria Yudin Reply September 5, 2019 at 5:14 pm

      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

      Like

  3. Sean Reynolds Reply July 6, 2018 at 10:31 am

    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

    Like

  4. steve larson Reply June 5, 2018 at 10:42 am

    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!!

    Like

  5. Jackie McNab Reply February 13, 2017 at 11:02 am

    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?

    Like

  6. Sean Hunter Reply June 24, 2015 at 2:39 pm

    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

    Like

    • Victoria Yudin Reply June 24, 2015 at 2:43 pm

      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

      Like

      • Sean Hunter Reply June 24, 2015 at 2:58 pm

        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.

        Like

    • Carlos Reply July 1, 2015 at 11:02 am

      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.

      Like

  7. Debbie Reply June 17, 2015 at 9:08 pm

    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

    Like

  8. Mark Malone Reply May 31, 2015 at 7:51 pm

    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?

    Like

    • Victoria Yudin Reply June 1, 2015 at 5:25 am

      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

      Like

  9. Leigh Reply May 21, 2015 at 4:48 pm

    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.

    Like

  10. Debbie Jones Reply February 24, 2015 at 1:25 pm

    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.

    Like

    • Victoria Yudin Reply February 24, 2015 at 1:48 pm

      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

      Like

  11. dgg Reply January 20, 2015 at 10:40 am

    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

    Like

    • Victoria Yudin Reply January 20, 2015 at 1:20 pm

      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

      Like

      • dgg Reply January 21, 2015 at 12:01 pm

        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

        Like

  12. Herb Reply September 1, 2014 at 8:33 am

    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?

    Like

  13. David Reply August 20, 2014 at 4:00 pm

    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

    Like

    • Victoria Yudin Reply August 21, 2014 at 3:13 am

      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

      Like

  14. matt Reply June 30, 2014 at 8:05 pm

    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,

    Like

    • Victoria Yudin Reply July 1, 2014 at 2:20 am

      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

      Like

      • Debbie Reply February 15, 2015 at 4:00 pm

        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

        Like

        • Victoria Yudin Reply February 16, 2015 at 5:23 am

          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

          Like

  15. Jay Reply November 4, 2013 at 7:46 am

    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

    Like

  16. Far Reply October 9, 2013 at 7:41 pm

    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,

    Like

    • Victoria Yudin Reply October 10, 2013 at 5:00 am

      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

      Like

  17. Gina Hoener Reply May 13, 2013 at 5:03 pm

    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!

    Like

    • Victoria Yudin Reply May 14, 2013 at 4:55 am

      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

      Like

    • joey b Reply June 17, 2013 at 3:33 pm

      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?

      Like

      • Victoria Yudin Reply June 17, 2013 at 5:05 pm

        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

        Like

        • Luis Reply June 17, 2013 at 7:40 pm

          Hi victoria,
          I create an A/R report by going to Report-Sales-Trial Balance-Historical Aged Trial Balance.

          Like

          • Victoria Yudin Reply June 18, 2013 at 8:23 am

            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

            Like

            • Luis Reply June 19, 2013 at 12:47 pm

              Victoria,
              The boxes that are selected to be excluded are “Fully Paid Docs”, “Zero Balance”, and “No Activity”

              Like

              • Victoria Yudin Reply June 19, 2013 at 1:10 pm

                Luis,

                I typically choose to exclude the following:

                • Fully Paid Documents
                • Zero Balanace
                • Unposted Applied Credit Documents
                • Multicurrency (leave this unchecked if you need to see multicurrency info)

                Can you try it with those and see if that makes a difference?

                -Victoria

                Like

                • Luis Reply June 19, 2013 at 2:03 pm

                  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.

                  Like

                  • Victoria Yudin Reply June 19, 2013 at 3:10 pm

                    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

                    Like

  18. Mark Reply July 17, 2012 at 5:01 pm

    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.

    Like

    • Victoria Yudin Reply July 18, 2012 at 7:40 am

      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

      Like

      • Mark Reply July 18, 2012 at 11:07 am

        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

        Like

  19. John Olley Reply June 20, 2012 at 10:45 am

    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

    Like

    • Victoria Yudin Reply June 20, 2012 at 11:43 am

      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:

      1. Always backup your data first
      2. If you have any unposted transactions with the ‘old’ salesperson ID, they will not be updated when you change the customer master data.
      3. If you are storing salesperson ID’s for customer addresses, you will also need to update those (they are stored in table RM00102).

      -Victoria

      Like

      • John Olley Reply June 21, 2012 at 10:37 am

        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

        Like

  20. Joe Spitler Reply February 27, 2012 at 7:10 pm

    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.

    Like

  21. Saras Reply May 11, 2011 at 1:05 am

    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

    Like

  22. Lulu Reply February 8, 2011 at 8:43 am

    Hello Victoria

    Do you have a Sales/RM SQL view like the ■”All Posted Transactions” including unposted transactions please?

    Thanks

    Like

    • Victoria Yudin Reply February 8, 2011 at 9:07 am

      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

      Like

      • Lulu Reply February 10, 2011 at 9:50 am

        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

        Like

  23. Vic Reply November 11, 2010 at 1:36 pm

    Hi victoria

    Do you have a view that would represent the aged trial balance with detail report.

    thanks
    Vic

    Like

  24. Lulu Reply September 28, 2010 at 9:28 am

    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

    Like

    • Victoria Yudin Reply September 30, 2010 at 3:17 am

      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

      Like

      • Lulu Reply October 3, 2010 at 2:18 am

        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.

        Like

        • Victoria Yudin Reply October 3, 2010 at 6:37 am

          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

          Like

  25. Jay Reply August 12, 2010 at 5:50 pm

    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

    Like

    • Victoria Yudin Reply August 13, 2010 at 5:19 am

      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

      Like

  26. Keith Yohn Reply August 11, 2010 at 5:57 pm

    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

    Like

    • Victoria Yudin Reply August 12, 2010 at 6:07 am

      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

      Like

      • Keith Yohn Reply August 12, 2010 at 10:21 am

        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

        Like

        • Victoria Yudin Reply August 12, 2010 at 10:31 am

          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

          Like

          • Keith Yohn Reply August 12, 2010 at 10:42 am

            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

            Like

  27. Ken Levy Reply August 2, 2010 at 10:42 am

    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

    Like

    • Victoria Yudin Reply August 2, 2010 at 11:01 am

      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

      Like

      • Paul Chacko Reply July 19, 2013 at 12:28 pm

        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

        Like

        • Victoria Yudin Reply July 19, 2013 at 1:13 pm

          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

          Like

        • Jaacqui Reply March 19, 2015 at 11:55 am

          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

          Like

          • Paul Chacko Reply March 19, 2015 at 1:12 pm

            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

            Like

Trackbacks/Pingbacks

  1. SQL stored procedure for all open AR transactions by customer name | Victoria Yudin - November 24, 2014

    […] Receivables SQL Code […]

    Like
  2. Identify what modules your Dynamics GP sales data originates in | Victoria Yudin - April 29, 2014

    […] Receivables SQL Views […]

    Like
  3. SQL script for comparing voids between RM and SOP in Dynamics GP | Victoria Yudin - April 28, 2014

    […] Receivables SQL Views […]

    Like
  4. SQL view for current Receivables aging detail in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - May 1, 2013

    […] Receivables SQL views […]

    Like
  5. SQL view for current Receivables aging detail in Dynamics GP | Victoria Yudin - May 1, 2013

    […] Receivables SQL Views […]

    Like
  6. SQL view for commissions details in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - July 19, 2012

    […] Receivables SQL views […]

    Like
  7. SQL view for commissions details in Dynamics GP | Victoria Yudin - July 19, 2012

    […] Receivables SQL Views […]

    Like
  8. SQL view for all unapplied Receivables transactions in Dynamics GP | Victoria Yudin - March 15, 2012

    […] Receivables SQL Views […]

    Like
  9. SQL view for current Receivables aging in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 25, 2012

    […] can find more Receivables code here, or links to additional reporting resources on my GP Reports […]

    Like
  10. SQL view for current Receivables aging in Dynamics GP | Victoria Yudin - January 25, 2012

    […] Receivables SQL Views […]

    Like
  11. SQL view for customer yearly totals in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - January 9, 2012

    […] more Dynamics GP Receivables code, take a look at my Receivables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and […]

    Like
  12. SQL view for customer yearly totals in Dynamics GP | Victoria Yudin - January 9, 2012

    […] Receivables SQL Views […]

    Like
  13. Customer shipping addresses in Dynamics GP | Victoria Yudin - February 24, 2011

    […] Receivables SQL Views […]

    Like
  14. Customer shipping addresses in Dynamics GP - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - February 18, 2011

    […] see other SQL views for Dynamics GP receivables data, take a look at the Receivables SQL Views page. For other Dynamics GP views and reporting reporting information, check out the GP Reports […]

    Like

Leave a comment