SQL view for current Receivables aging detail in Dynamics GP


Another reader request brings us this new script. Below is a view for current Receivables aging in detail. This code is only looking at functional currency and will return one row per open (unpaid) receivables transaction. I am hard-coding the aging using the following buckets and aging by due date:

  • Current
  • 0 to 30 Days
  • 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 and change the labels and number of days.

Additional resources:


create view view_Current_Receivables_Aging_Detail
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Receivables_Aging_Detail
-- Created May 1, 2012 by Victoria Yudin, Flexible Solutions Inc
-- For updates see https://victoriayudin.com/gp-reports/
-- Shows current AR aging in detail w/ hard-coded aging buckets
-- Tables used:
--     CM - RM00101 - Customer Master
--     RM - RM20101 - Open Transactions
-- Updated Aug 12, 2013 to add 0 to 30 days bucket
-- Updated Sep 20, 2013 to add Last Payment Date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.PYMTRMID Customer_Terms,
CM.CUSTCLAS Customer_Class,
CM.PRCLEVEL Price_Level,
case RM.RMDTYPAL
  when 1 then 'Sale / Invoice'
  when 3 then 'Debit Memo'
  when 4 then 'Finance Charge'
  when 5 then 'Service Repair'
  when 6 then 'Warranty'
  when 7 then 'Credit Memo'
  when 8 then 'Return'
  when 9 then 'Payment'
  else 'Other'
  end Document_Type,
RM.DOCNUMBR Document_Number,
RM.DOCDATE Document_Date,
RM.DUEDATE Due_Date,
S.LASTPYDT Last_Payment_Date,
case
  when RM.RMDTYPAL < 7 then RM.ORTRXAMT
  else RM.ORTRXAMT * -1
  end Document_Amount,
case
  when RM.RMDTYPAL < 7 then RM.CURTRXAM
  else RM.CURTRXAM * -1
  end Unapplied_Amount,
case
  when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
  when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0
     and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
  else 0
  end [Current],
case 
  when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30
     and RM.RMDTYPAL < 7 then RM.CURTRXAM
  when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 30
     and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
  else 0
  end [0_to_30_Days],
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],
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],
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]

from RM20101 RM

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

left outer join RM00103 S
     on RM.CUSTNMBR = S.CUSTNMBR

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

-- add permissions for DYNGRP
GO
grant select on view_Current_Receivables_Aging_Detail 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.

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

  1. After running your view it returns different amount for the current period compare to what is being returned by customer summary master table RM00103 – such that field ‘AGPERAMT_1’. I believe this field is meant to bring outstanding amount for current period 0-30 days.

    Like

    • Rakesh,

      This is expected. The RM00103 table only get updated when you run the Aging Routine inside GP and the calculations use the settings on your Receivables Management Setup window. My code has hard-coded calculations for the aging buckets – this is on purpose so that you can have as many aging buckets as you want and use whatever logic you want for them.

      -Victoria

      Like

  2. Hi Victoria,

    I don’t know if you’re still checking these comments, but I’m hoping so.

    First of all, thank you so much for your website. It has been a tremendous help.

    Secondly, is there a way of using the aging buckets set up each month in GP under Sales, Setup, Receivables? I have been using this code to set up the aging, but with the way we change our buckets I would have to either go in and manually change the buckets each month and re-run, or set up a separate query for each month. I found other queries online that pull from the setup, but I am having issues combining them with this query.

    Like

    • Hi Heidi,

      There is a way to do it, sure. I actually did not do it on purpose because most people do not want these to mimic the GP buckets and want to add more buckets or different ones.

      May I ask why you are changing these in GP all the time? That’s pretty unusual in my experience.

      -Victoria

      Like

      • Thank you so much for your response. We bill monthly, always dated the last day of the month. We do our aging based on the doc date, and then the first bucket is based on the most current month of invoices.

        For example what we use for the aging as of February 28, and then all the way until the new invoices are processed on March 31 is as follows:
        Current: 0-27 (for February dated invoices)
        31-61: 28-58 (for January dated invoices)
        62-92: 59-89 (for December dated invoices)
        93 and over: 90-999 (for anything dated November and older)

        Then as of March 31 until the new invoices are processed for April 30:
        Current: 0-30 (for March dated invoices)
        31-61: 31-58 (for February dated invoices)
        62-92: 59-89 (for January dated invoices)
        93 and over: 90-999 (for anything dated December and older

        My work around was going to be to create this view for each month, which would work all the time except in a leap year, but I thought maybe if there was a way to pull directly from the periods in GP, it would be easier. We update those buckets in GP monthly.

        I’d love to hear your thoughts.

        Like

        • Hi Heidi,

          Thanks for the explanation, that makes sense. Certainly one way you could do it would be to update the buckets in GP each month and use those in the code, like you’re suggesting. Another would be to simply code in the buckets based on the invoice dates. (And call the buckets something like Last Month, 2 Months, 3 Months, and Older.) That should make it dynamic and you would not have to change the buckets in GP (unless you need that for other reasons) and to also only have one piece of code, it would even work for leap years.

          If you need help with coding that, it is something I can do as a consulting project, let me know.

          -Victoria

          Like

          • Thank you! I will see if I can figure it out. If not, I might contact you for a quote about this and a few other items we’re looking for related to this project.

            Like

  3. I needed to have a backdated aging so I am trying it as a table valued function as below:

    create Function [dbo].[TRM_Receivables_Aging_Detail_With_Offset](@DateOffset INTEGER)
    RETURNS Table
    as

    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
    — Shows current AR aging in detail w/ hard-coded aging buckets
    — Allows for a date offset parameter
    — Tables used:
    — CM – RM00101 – Customer Master
    — RM – RM20101 – Open Transactions
    — Created Dec 14, 2016 based on a view from Victoria Yudin
    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

    RETURN(
    select
    CM.CUSTNMBR Customer_ID,
    CM.CUSTNAME Customer_Name,
    CM.PYMTRMID Customer_Terms,
    CM.CUSTCLAS Customer_Class,
    CM.PRCLEVEL Price_Level,
    case RM.RMDTYPAL
    when 1 then ‘Sale / Invoice’
    when 3 then ‘Debit Memo’
    when 4 then ‘Finance Charge’
    when 5 then ‘Service Repair’
    when 6 then ‘Warranty’
    when 7 then ‘Credit Memo’
    when 8 then ‘Return’
    when 9 then ‘Payment’
    else ‘Other’
    end Document_Type,
    RM.DOCNUMBR Document_Number,
    RM.DOCDATE Document_Date,
    RM.DUEDATE Due_Date,
    S.LASTPYDT Last_Payment_Date,
    case
    when RM.RMDTYPAL < 7 then RM.ORTRXAMT
    else RM.ORTRXAMT * -1
    end Document_Amount,
    case
    when RM.RMDTYPAL < 7 then RM.CURTRXAM
    else RM.CURTRXAM * -1
    end Unapplied_Amount,
    case
    when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) <= 0
    and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) <= 0
    and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
    else 0
    end [Current],
    case
    when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 1 and 30
    and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 1 and 30
    and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else 0
    end [0_to_30_Days],
    case
    when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 31 and 60
    and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 31 and 60
    and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else 0
    end [31_to_60_Days],
    case
    when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) between 61 and 90
    and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) between 61 and 90
    and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else 0
    end [61_to_90_Days],
    case
    when DATEDIFF(d, RM.DUEDATE, getdate() – @DateOffset) > 90
    and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate() – @DateOffset) > 90
    and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
    else 0
    end [91_and_Over]

    from RM20101 RM

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

    left outer join RM00103 S
    on RM.CUSTNMBR = S.CUSTNMBR

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

    Like

  4. I’m getting the following error :Incorrect syntax near ‘GO’.

    could you please help.

    Like

    • Eugene,

      I just copied all the code into a new query in SQL and ran it and didn’t get any errors. Did you make any changes to the code I posted? If not, can you please copy the last 5 lines of your code in here so I can take a look?

      -Victoria

      Like

  5. When attempting to add the Parent ID to this script I keep getting the Customer Number. How can I get it so that I will get the correct Parent information.
    The column that is showing properly is CPRCSTNM

    Kevin

    Like

    • Kevin,

      What is probably happening is that you’re seeing open transactions at the parent level – for those, the Customer ID and Parent ID will be the same. For example, I added the following after line 18:
      CM.CPRCSTNM Parent_ID,
      and the results look like this:
      sample with parent ID

      The PLAZAONE0001 National Account has WORLDENT0001 as a child, but the parent customer itself also has open transactions.

      Hope that helps.
      -Victoria

      Like

      • Hi Victoria,

        Thanks for getting back to me on the Child Parent issue.

        For the Document types that are Credit Memo & Payments would you know of a way to reflect these amounts as negative in the results?

        Thanks for your assistance.
        Kevin

        Like

      • Hi Victoria
        I want to update the AR w/Options report that shows the Net Amount due for Open Invoices. For Example.. There is an Invoice for 1000.00 the Cash Receipt applied to this invoice is 500.00 so on the AR Aging w/options it shows the Invoice Number and amount and then the cash receipt amount underneath the invoice. We would like to have it show as net amount due like below.

        Date Invoice # Net amount due
        on Invoice
        9/30/16 123 500.00

        Can you give me any ideas how to update the exiting AR w/options or will I need to create one and what tool would you use?

        Thank you!

        Like

        • Hi Tammy,

          I cannot offer any help with Report Writer reports, sorry. Whenever possible, I use other reporting tools so as to avoid Report Writer as much as possible. I post all of this code on my blog to help others do the same. 🙂

          -Victoria

          Like

  6. Thank you so much for posting these views, very helpful,
    Would it be possible to update using Doc Date vs Due Date for Aging columns?

    Like

  7. Hi Victoria,

    We are using the above view to create an AR aging report. But the due date is not showing up correctly in the report. It is showing same as the Document Date. The due date should be calculated based on the payment terms right ? It is not being calculated correctly for most of the records. Could you please let me know how I can get the correct due date

    Many thanks,

    Like

    • Hi Usha,

      This view does not actually calculate due dates. It uses the due date stored in your Dynamics GP for each transaction. For Credit Memos, Returns and Payments, there is no ‘Due Date’ in GP, so the Document Date is used.

      Hope that helps clarify.
      -Victoria

      Like

  8. Looking to have this as a summary, the pulling open orders into this as well, from the SOP10100 file. I cannot seem to get it to sum correctly, even before I tackle the SOP10100.

    Any suggestions?

    Here’s what I have:

    select
    CM.CUSTNMBR Customer_ID,
    CM.CPRCSTNM Corp_Account,
    sum(case
    when RM.RMDTYPAL < 7 then RM.ORTRXAMT
    else RM.ORTRXAMT * -1
    end) Document_Amount,
    sum(case
    when RM.RMDTYPAL < 7 then RM.CURTRXAM
    else RM.CURTRXAM * -1
    end) Unapplied_Amount,
    sum(case
    when DATEDIFF(d, RM.DUEDATE, getdate()) <= 0 and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) <= 0 and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
    else 0
    end) [Current],
    sum(case
    when DATEDIFF(d, RM.DUEDATE, getdate()) between 1 and 30 and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) between 1 and 30 and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else 0
    end) [0_to_30_Days],
    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]
    from RM20101 RM inner join RM00101 CM
    on RM.CUSTNMBR = CM.CUSTNMBR
    left outer join RM00103 S
    on RM.CUSTNMBR = S.CUSTNMBR where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0
    GROUP by RM.CUSTNMBR

    Like

  9. Hi Victoria,

    Love these! Is there any way to specify a specific date as opposed using todays dates?

    Like

    • Hi Adam,

      Thanks for your kind words! Sounds like you are looking for a ‘historical’ aged trial balance instead of a ‘current’ one. 🙂 That requires much more complicated code and typically a SQL stored procedure, not a view. It’s certainly doable, but not something I will be sharing on my blog in the foreseeable future. If you are interested in having this created for you as a consulting project, please let me know.

      -Victoria

      Like

  10. Hi Victoria
    I am grateful every week for your SQL views – save me SO much time!
    I have use the view you have for Payables for Multicompany Open Invoices – do you have anything for AR/SOP -showing Open Invoices for Receivables for multiple companies?
    Many thanks
    Sarah

    Like

  11. Hi Victoria,

    Is it possible to Have the Last Payment Date Included? Our company is always looking for the Last Payment received by a customer.

    Like

  12. Is there a way to add a state field in this report? I need to be able to see my open AR by state.

    Thanks!! 🙂

    Like

  13. I need a historical aging in smartlist or excel, is this something you could do?

    Like

    • Hi Beth,

      Historical aging is not something you can do in SmartList, as there is no way to pass the aging date to the code in a SmartList. I can create a historical aging in Excel as a consulting project. If you are interested, please let me know and I will contact you via email.

      -Victoria

      Like

  14. Thank you Victoria…this is MOST helpful!

    Like

Trackbacks/Pingbacks

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

    […] Continue reading on Source Blog […]

    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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: