SQL view for current Payables aging detail in Dynamics GP

I have received a few requests for a detailed version my Current Payables Aging Summary view. Here it is. This code is only looking at functional currency and will return one row per open (unpaid) payables transaction. 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 and change the labels and number of days.

Additional resources:

create view view_Current_Payables_Aging_Detail

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Current_Payables_Aging_Detail
-- Apr 24, 2013 - Victoria Yudin, Flexible Solutions, Inc.
-- Shows current AP aging
-- Functional currency only
-- Aging by due date
-- For updates please see:
--	 https://victoriayudin.com/gp-reports/
-- For other payables SQL scripts:
--	 https://victoriayudin.com/gp-reports/payables-sql-views/
-- Updated Apr 25, 2013 to fix aging buckets for credit docs
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,
  when 1 then 'Invoice'
  when 2 then 'Finance Charge'
  when 3 then 'Misc Charge'
  when 4 then 'Return'
  when 5 then 'Credit Memo'
  when 6 then 'Payment'
  end Document_Type,
P.DOCNUMBR Document_Number,
P.DOCDATE Document_Date,
P.PSTGDATE GL_Posting_Date,

P.TRXDSCRN [Description],

  when P.DOCTYPE < 4 then P.DOCAMNT
  else P.DOCAMNT * -1
  end Document_Amount,

  when P.DOCTYPE < 4 then P.CURTRXAM
  else P.CURTRXAM * -1
  end Unapplied_Amount,

  when datediff(d, P.DUEDATE, getdate()) < 31
       and P.DOCTYPE < 4 then P.CURTRXAM
  when datediff(d, P.DOCDATE, getdate()) < 31
       and P.DOCTYPE > 3 then P.CURTRXAM * -1
  else 0
  end [Current],

  when datediff(d, P.DUEDATE, getdate()) between 31 and 60
       and P.DOCTYPE < 4 then P.CURTRXAM
  when datediff(d, P.DOCDATE, getdate()) between 31 and 60
       and P.DOCTYPE > 3 then P.CURTRXAM * -1
  else 0
  end [31_to_60_Days],

  when datediff(d, P.DUEDATE, getdate()) between 61 and 90
       and P.DOCTYPE < 4 then P.CURTRXAM
  when datediff(d, P.DOCDATE, getdate()) between 61 and 90
       and P.DOCTYPE > 3 then P.CURTRXAM * -1
  else 0
  end [61_to_90_Days],

  when datediff(d, P.DUEDATE, getdate()) > 90
       and P.DOCTYPE < 4 then P.CURTRXAM
  when datediff(d, P.DOCDATE, getdate()) > 90
       and P.DOCTYPE > 3 then P.CURTRXAM * -1
  else 0
  end [91_and_Over]

from PM00200 VM  --vendor master
inner join PM20000 P  --open payables

where P.CURTRXAM <> 0 and P.VOIDED = 0

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

28 Responses to “SQL view for current Payables aging detail in Dynamics GP”

  1. Victoria,
    How difficult to add multi-currency information? Willing to contract you to create if neccessary.


  2. Hi there,

    I was running this view and notice AP Aged Trial balance is not same as this smartlist coming out of this view. it just put up amount under current bucket instead of 30-60days.

    For example: Vendor ABC = Balance of $10 goes under “31-60” days as per out of box aged trial balance report. Whereas Smartlist show $10 under “Current”.

    Not sure why documents are falling under different buckets. As per current setup of company on our end aging is calculated of “Document Date”

    Please advice!



  3. Hi Victoria,

    Sorry to resurrect an old thread.

    Our accounts team need to replicate the HATB as it would have been at previous month ends.

    Your code works great for aging open payables transactions. But I need to somehow include paid historic transactions, as the report function does. So to replicate as exactly what the report does but to have the data in a raw tabular format.

    Even if the SQL view cant pass a date, I can get past that if I have all the data

    Any help would be appreciated.



    • Hi Mark,

      The code for a historical version of this report is incredibly more complicated and is not something that I will be posting on my blog because of the amount of work it took to create. Also because it needs to be slightly different for each company. If you are interested in having this created as a custom report for you, please let me know.



  4. Hello Victoria, We use this view that you created in order to pull the paybles info to an Excel report and it works perfectly with minor changes to fit our needs. We would like to add “Comment 1” or “Note” to this report to see some important info for the vendor with the invoices outstanding. We can not find a field name code for the Comment fields in the Vendor record. We thought we could just add a field name code to the view above similar to lines 18-21 above. Are you able to tell us if there a” VM.” field name for a comment or note field in the Vendor record? Thank you for any suggestions!


    • Hi Allison,

      First step is to understand what data you’re trying to add. If you’re looking in GP – what window is the data on and where exactly is it? It sounds like you’re looking for the Note that is entered next to the Vendor ID on the Vendor Maintenance window. Is that right? Or are you looking for Comment 1 (at the bottom of the same window)? Or both? 🙂 If that’s not it, please give me some more details.



      • Thank you, Victoria, for responding! We are looking for either or both. I looked at the fields available on the header of the vendor card and could not find the actual field name for either. My A/P dept wants to be able to see a note they type in to give vendor info when we pull an Excel report from this “view”. Without knowing the field and table, I can’t add this info to the view. We would love to have the table/field name for both such as VM.VENDORID. Thank you for considering my request!


        • Hi Allison,

          Comment 1 is in the VM table (PM00200), it’s called COMMENT1. So you can just add VM.COMMENT1, that should work. For the Note, you would need to link another table, you can use the following code after my line 81:
          left outer join SY03900 N

          then add a line in the select section (like after line 21) with:

          Hope that helps,


          • Victoria — I am sure it will! Of course I could have guessed the Comment 1 field was VM.COMMENT1 but I couldn’t find it listed in the Field Names so I thought it might not be available to export. I am looking forward to adding this as well as the note info above. You are amazing and I appreciate your work on this! The work I ordinarily do is in Management Reporter report writing. I taught myself how to use Integration Manager and I love what it can do. I would like to get more into SQL but have not been able to do so until recently. I found your blog and Belinda Allen’s blog/video on how to utilize this view and followed it completely. Now our A/P department is quite happy with their new report! Thank you so much! It is obvious your expertise is far beyond mine but I have written some pretty awesome reports in Management Reporter. Let me know if there is anything I can ever do for you!

            Highest regards,



  5. Hi Victoria,
    Your SQL queries have been a true-godsend to me, I hope you know how appreciated you are! I’m not sure if this is the right place to post this…but do you have a query that shows aging as of a current date? For example, I could enter a date in the past and see what the aging looked like on that particular day. I think I know conceptually what needs to happen and have built a basic framework but I’m falling short on the execution!
    As always, thanks for your insight and help –


    • Hi Jess,

      Glad my SQL queries have helped! 🙂 It sounds like you are looking for a historical trial balance (HATB) report. I have created a number of different HATB reports in the past, and they usually take some effort. While the concept is somewhat straightforward, like you say, the execution, especially when combined with how the data is stored in GP, is not so straightforward. If you’re interested in having something like this created for you as a consulting project, please let me know.



  6. Hello Victoria

    Is there in view available for Historical Aged Summary Report? Can I add two columns Opening Balance and net change between Opening Balance and Current Period?


    Sanjay Parab.


    • Hi Sanjay,

      It doesn’t quite work like that. For a ‘historical’ report, you need to be able to pass in a parameter, since the data returned and calculations made need to be ‘as of’ a particular date. There is no way to pass a parameter to a SQL view. So you would need to create a stored procedure to accomplish this.



  7. Hi Victoria! This view is exactly what we need to review our open AP on a weekly basis since we are trying to set a pay schedule using the invoice due date. I have added 2 of our custom fields and also the VCHRNMBR; but wondering if it is possible to also add the GL Account Number, Account Name, and Distribution Type?


  8. Victoria,

    Is there a similar script for AR?




  9. Rafael Villalobos Reply April 25, 2013 at 9:47 am

    Excellent post, but there is one detail, when the document type is greater than 3 can not be used the field DUEDATE because AGING BUCKET always be greater than 90 days. I’ve use DOCDATE instead.

    Thank you very much for everything you do for the Dynamics Community

    Maracaibo, Venezuela


    • Rafael,

      You are exactly right, thank you very much for catching that. I actually thought I had fixed that, the change must not have updated properly. It should be fixed now.



  10. This is perfect. Thanks Victoria!




  1. SQL view for Current Payables Aging Details | DynamicAccounting.net - April 29, 2013

    […] Victoria Yudin offers a SQL view for current payables aging with detail. […]


  2. SQL view for current Payables aging detail in Dynamics GP – 4/24, Victoria Yudin | Partner Compete - April 24, 2013

    […] Continue reading on Source Blog […]


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

%d bloggers like this: