briefcase invoice

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 http://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.

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

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

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

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

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

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

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

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

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

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

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

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

%d bloggers like this: