abacus

SQL view for current Receivables aging in Dynamics GP


I have created a variation on my view that shows all unapplied Receivables transactions to show customer aging in buckets. This is only looking at functional currency and will return data in summary, meaning one row per customer with a balance. I am hard-coding the aging using the default aging setup installed with GP, which is aging by due date and using the following buckets:

  • Current
  • 31 to 60 Days
  • 61 to 90 Days
  • 91 and Over

If you would like to use different aging buckets, just follow the examples in my code.

You can find more Receivables code here, or links to additional reporting resources on my GP Reports page.


create view view_Current_Receivables_Aging_Summary
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Receivables_Aging_Summary
-- Created Jan 25, 2012 by Victoria Yudin, Flexible Solutions Inc
-- For updates see http://victoriayudin.com/gp-reports/
-- Shows current AR aging with hard-coded aging buckets
-- Tables used:
--     CM - RM00101 - Customer Master
--     CS - RM00103 – Customer Master Summary
--     RM - RM20101 - Open Transactions
-- Updated May 1, 2013 to fix aging for credit docs
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
CM.CUSTNMBR Customer_ID, CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms, CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,

sum(case
when RM.RMDTYPAL < 7 then RM.CURTRXAM
else RM.CURTRXAM * -1
end) Total_Due,

sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) < 31 
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) < 31 
     and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [Current],

sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60 
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60 
     and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [31_to_60_Days],

sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90 
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90 
     and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
else 0
end) [61_to_90_Days],

sum(case
when DATEDIFF(d, RM.DUEDATE, getdate()) > 90 
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
when DATEDIFF(d, RM.DOCDATE, getdate()) > 90 
     and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
else 0
end) [91_and_Over],

CS.LASTPYDT Last_Payment_Date,
CS.LPYMTAMT Last_Payment_Amount

from RM20101 RM

inner join RM00101 CM
     on RM.CUSTNMBR = CM.CUSTNMBR
inner join RM00103 CS
     on RM.CUSTNMBR = CS.CUSTNMBR

where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0

group by CM.CUSTNMBR, CM.CUSTNAME, CM.PYMTRMID, CM.CUSTCLAS, 
         CM.PRCLEVEL, CS.LASTPYDT,CS.LPYMTAMT

-- add permissions for DYNGRP
GO
grant select on view_Current_Receivables_Aging_Summary to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

45 Responses to “SQL view for current Receivables aging in Dynamics GP”

  1. Hi Victoria, It’s posible display the Total_Due per month? I mean, Jan_total_due, Feb_total_due, ect…, by the way I love your blog.

    Like

    • Hi Julio,

      Per month of what? Invoice Date, Due Date, something else? :-)

      -Victoria

      Like

      • Thanks for your fast reply, I’m looking for the Total Due amount at the end of a previous month using the Due Date. for example

        SUM(CASE WHEN month(RM.DUEDATE) = 1 AND RM.RMDTYPAL 6 THEN RM.CURTRXAM * – 1 ELSE 0 END) AS [Jan Total Due]

        this is your code:

        sum(casewhen RM.RMDTYPAL < 7 then RM.CURTRXAMelse RM.CURTRXAM * -1end) Total_Due

        Regards,

        Like

        • Ups!!! sorry for the large letters, I don’t know what happen.

          Like

          • Julio, no problem, I fixed it.

            Sorry, I am still not 100% with you. Do you mean you want the aging as of a particular date? (So not the current aging, but the historical aging?) Or do you just want to sum in buckets based on the month of the due date?

            -Victoria

            Like

  2. Victoria,

    Your website has saved me SO much time and effort – thank you so much for putting this information out there for us.
    Is there a way to have this same data format for Detailed Aging? Basically, I am being asked to create the Receivables Historical Aged Trial Balance in Smartlist.

    Thank you,
    Jeff

    Like

    • Hi Jeff,

      I am very glad that my blog has helped you! I have a variation on this view that lists all open receivables transactions. You could use this view in a SmartList, however, just to make sure we’re on the same page…this is not a ‘historical’ aging – this is current.

      If you need to recreate the historical aging, you’re looking at a lot more work and you may not be able to do this in a SmartList, because SmartList does not have a way to enter a ‘parameter’ which is what you need to let the user specify the historical aging date. This blog post by Mark Polino lists some available resources for the historical aging that you might find useful.

      -Victoria

      Like

      • Hello,
        Thanks for this. Will this give me the option to have a subtotal at the end of each customer? Would I not need to do some sort of union in order to get the sub-totals / totals at the end of the details for each customer?
        Regards,

        Like

    • Hi Victoria,
      Can we have a view that gets the aging buckets with the unposted SOP invoices.
      Thanks,

      Like

      • Hi Ray,

        Not sure what you mean – you want this view to also include unposted invoices from SOP? Or something else?

        -Victoria

        Like

        • HI Victoria,
          Yes this is what I mean, I want to have detail trail balance for receivables including the unposted invoices from SOP.

          Like

          • Hi Ray,

            Gotcha. That is certainly something that can be done, and I’ve done a variation on that in the past for a customer statement. However, I would not want to change this view, as most people would not want to see unposted transactions as part of their aging. I will add it to my list of requests for the blog, but no promises. If you need this right away, this is something that we can create for you as a consulting project, let me know.

            -Victoria

            Like

  3. I used the sql code and it worked great! Thanks so much for sharing it. The one question that I have is how I could create parameters that would allow users to view only those customers who are past due. I was thinking of creating a temp table that would hold the different buckets and use that for the available values that would be used in the parameter. Do you have any thoughts?

    Thanks,

    Jon

    Like

    • Jon,

      I am not sure what you mean, do you want to give the user a list of ‘buckets’ to pick from as a parameter? If so, since the buckets are hardcoded in my example, and there are only a few of them, why not just hardcode the values?

      If I misunderstood, can you give me an example? Also, what reporting tool are you going to be using for this?

      -Victoria

      Like

  4. Hi Victoria,

    I’m working to a report that will give the un-billed and billed amount of a customer. But as of got the information I gather did not match on what GP report return. Hope you can give us information what possible tables and relation to each other.

    Thank you..

    jhunn

    Like

    • Hi Jhunn,

      Unbilled transactions can be in a lot of different places. Where are you entering them? (What window in GP?)

      -Victoria

      Like

      • Hi Victoria,

        All in sales module, most RM table was here. but when i try to check on Customer Payment Summary the amount did not match on my report.

        I don’t know if i missed some table of there’s a problem with the data i got.

        Like

        • Hi Jhunn,

          The Sales series has 3 modules: Sales Order Processing (SOP), Receivables Managements (RM) and Invoicing (IVC). The Invoicing module is not used much by anyone, but still important to keep in mind just in case you are using it. Most companies use the SOP module to enter most of their sales transactions, with only some transactions entered in the RM module. SOP transactions only show up in the RM module once they are posted. Until then, they are only seen in the SOP tables. However, the Customer Payment Summary window only looks at the RM tables.

          So…all that to say that if you are using the SOP module, then I would not recommend looking at the Unposted Sales on the Customer Payment Summary window because they will not be showing you the entire picture.

          -Victoria

          Like

  5. Hello Victoria,

    I found your post with the help of the MS Dynamics 2010 GP web site forums. I was wondering if the SQL view you defined here is the typical method for obtaining AR aging data from GP for reporting purposes.

    I am new to GP. My company has a home built application that uses some of the data from the GP database, and in this case we have a report that was built from query that joins directly to some of the tables that are used in your view definition. Is this the normal method for obtaining AR aging data from GP for reporting purposes? I was told by an IT manager in my company that we should be using either eConnect, or some other GP web service to obtain the aging data from GP, instead of joining directly to its db tables. The direct join is clearly more efficient that retrieving these data through something like eConnect, but I think the assumption was that eConnect is a more secured, and therefore a better approach than a direct query. Can AR aging data be obtained through eConnect or some other method, and if so, what method do you recommend?

    Any help is appreciated.

    Thanks,

    Harold

    Like

    • Hi Harold,

      I am not sure there is one answer for this, as a lot of times the best solution will depend on many other factors. I would recommend posting your question on the GP Community Forum to get some opinions from the GP community.

      -Victoria

      Like

      • Hi Victoria,

        Thank you for the reply, and your suggestion. I do have one more question – is it possible to obtain the aging data stored in tables RM00101, RM20101, and RM30101 – using eConnect? We will likely want to retrieve all of the data in these tables for now, as our original report joined to those tables without a where clause. It may be that out application performs the filtering of results from the report query.

        Thanks again

        Like

  6. What would it take to get this to show originating currency?

    Like

  7. Dear Victoria

    I am printing the RM Historical Aged Trial Balance Summary. So when I give age as of 31/12/2011, I get a figure and if I give the age of for eg., 31/12/2015, I get a lesser figure. So, as I am a regular visitor of your site, I copied this script and exported excel. This give me another figure. What can be the problem. Mainly my issue is the difference happening in the report when the age as of date is different. I run the aging as on 29/02/2012. But still the figures differes. I am using GP 10 SP 3.

    Thanks in advance.

    Like

    • Hi Ktarahman,

      When you run a historical aged trial balance (HATB) as of a particular date the report you get is only as of that date. So on Dec. 31, 2011 the report would show that your customers owed you X at that time. Transactions posted in January of 2012 would make the same report on Jan. 31, 2012 have a different number…it would be X less whatever your customers have paid in January plus whatever new invoices you posted in January.

      The view in this post is not restricting by any date, so it will give you every open receivables transaction in the system, whether it’s in the past or the future. I would suspect that if you run the HATB with an aging date of 12/31/2999 (yes, 2999, that’s not a typo) you will get the same results as this view. If not, there may be some data cleanup needed or you may have transactions with dates greater than 12/31/2999.

      -Victoria

      Like

  8. Victoria,

    I didn’t reblog your script, but had no problem copying it with the braces and it runs fine. I have made a modication to your script which you may be interested in. Basically I changed a couple of the selections to match the AGPERAMT buckets in GP – where AGPERAMT1 is Current and AGPERAMT2 is 1-30 Days.

    Example:

    sum(CASE 
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) > 0 
    and RM.RMDTYPAL < 7 THEN RM.CURTRXAM
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) > 0 
    and RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1  
    ELSE 0  
    END) as [Past_Due],
       
    sum(CASE 
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) < 1 
    and RM.RMDTYPAL < 7 THEN RM.CURTRXAM  
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) < 1 
    and RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1  
    ELSE 0  
    END) as [Current_Due],  --AGPERAMT_1
    
    sum(CASE 
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30  
    and RM.RMDTYPAL < 7 THEN RM.CURTRXAM  
    WHEN DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30
    and RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1  
    ELSE 0  
    END) as [Days_1_to_30],  --AGPERAMT_2
    

    Like

  9. Victoria, Thanks for the reply and update. I did use the copy functionality to copy the script and the code failed without the brackets for the column name. I am on SQL Server 2008 R2. :)
    Regarding the aging buckets, what you say makes perfect sense. :).
    Thanks for the good script.

    Like

    • Hi Siva,

      I just looked at the reblog of my post on your blog and I see that all the brackets around the column names are stripped out. :-( As I mentioned before, WordPress has some special functionality for posting code…perhaps that is causing this not to be picked up properly when it is reblogged? Is it possible to create a link back to my blog for the code instead of reblogging the whole post? Maybe that would avoid a lot of issues. I see this is not isolated, for example, you will have the same problem with this one.

      I just tested copying the code directly from my blog using both Google Chrome and IE 9.0 with SQL 2008 R2 and am not seeing any issue with the brackets.

      -Victoria

      Like

  10. Victoria, My two cents to this.

    I executed this query and got the following error.
    “Msg 102, Level 15, State 1, Procedure view_Current_Receivables_Aging_Summary, Line 35
    Incorrect syntax near ’31′.”.
    It looks like you will need to enclose the alias names within brackets (i.e.) like [91_and_Over] instead of just 91_and_Over, since the column name cannot begin with a number or special character. It has to be an alphabet.

    Also, you have determined the aging bucket by calculating the date difference between the current date and the due date. Instead you can make use of the aging bucket column in RM20101 (AGNGBUKT) which can be joined with the RM40201 table to display the aging bucket for the specific record. This way, we can make use of the standard aging options in GP (either by doc date or due date) and once we run the aging process, we would be able to run the query to get the same result as the GP Current Open TB. :)

    Like

    • Hi Siva, thanks for your comments.

      For the error – I do have brackets around the column names and am not able to duplicate what you’re seeing. :-( Are you looking at the code on my blog directly, or somewhere else? Also, how are you copying the code? I have recently started using special functionality available on WordPress for posting code to try to avoid any issues with special characters. So when looking at it on my blog, there are 4 little gadgets at the top right of the code section – if you click the second one, that will copy all the code properly to your clipboard. Can you please let me know if that works for you? I have seen issues in the past with various browsers (cough, typically IE) stripping out some of the formatting, so I just want to make sure this is not a recurring issue for you and others. I also just noticed some extra spacing in the code…not sure where that came from, but I took it out…maybe that will help, as well.

      For your other comment – I actually hard coded the aging buckets on purpose. I have found that often when I get asked for this type of report, the company asking either (a) wants something slightly different from what their aging setup in GP is or (b) wants to not have to run the aging routine in GP to move the data to the correct buckets prior to running the report. So I wanted to provide an easy example on how to set up your own aging buckets – this way, if someone wants to create 10 aging buckets or just 2, they know how to code it. :-) Hope that makes sense.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. Microsoft Dynamics GP - Excel AR Trial Balance with SQL (with video) - Belinda, the GP CSI - Microsoft Dynamics GP - Microsoft Dynamics Community - November 11, 2013

    […] http://victoriayudin.com/2012/01/25/sql-view-for-current-receivables-aging-in-dynamics-gp/ […]

    Like

  2. Doing the “Impossible” | Q Factor’s Blog - February 17, 2012

    [...] have found some attempts to build SQL views for Trial Balance reports, most notably by Victoria Yudin, but these are typically for open A/R and not historical. Ideally you would build a SQL view, then [...]

    Like

  3. SQL view for current Receivables aging in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - January 26, 2012

    [...] Comments 0 Victoria gives us a new SQL view for current Receivables aging in Dynamics GP [...]

    Like

  4. Interesting Findings & Knowledge Sharing » SQL view for current Receivables aging in Dynamics GP - January 25, 2012

    [...] reading here: SQL view for current Receivables aging in Dynamics GP VN:F [1.9.13_1145]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F [...]

    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,470 other followers

%d bloggers like this: