Receivables SQL Code


Dynamics GP Receivables SQL Views


Dynamics GP Receivables SQL Stored Procedures

75 Responses to “Receivables SQL Code”

  1. 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

  2. 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

    • 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

  3. 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

    • 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

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

  5. 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

    • 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

  6. 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

    • 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

    • 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

      • 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

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

          Like

          • 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

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

              Like

              • 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

                • 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

                  • 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

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

    • 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

      • 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

  8. 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

    • 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

      • 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

  9. 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

  10. 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

  11. Hello Victoria

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

    Thanks

    Like

    • 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

      • 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

  12. Hi victoria

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

    thanks
    Vic

    Like

  13. 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

    • 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

      • 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

        • 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

  14. 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

    • 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

  15. 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

    • 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

      • 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

        • 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

          • 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

  16. 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

    • 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

      • 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

        • 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

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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,626 other followers

%d bloggers like this: