approved stamp

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
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- 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:
--	 http://victoriayudin.com/gp-reports/
-- For other payables SQL scripts:
--	 http://victoriayudin.com/gp-reports/payables-sql-views/
-- Updated Apr 25, 2013 to fix aging buckets for credit docs
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
VM.VENDORID Vendor_ID,
VM.VENDNAME Vendor_Name,
VM.VNDCLSID Vendor_Class,
VM.PYMTRMID Vendor_Terms,
case P.DOCTYPE
  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.DUEDATE Due_Date,

P.TRXDSCRN [Description],

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

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

case
  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],

case
  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],

case
  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],

case
  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
   on P.VENDORID = VM.VENDORID

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

-- add permissions for DYNGRP
GO
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.

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

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

    Like

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

      -Victoria

      Like

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

    Regards,

    Sanjay Parab.

    Like

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

      -Victoria

      Like

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

    Like

  4. Victoria,

    Is there a similar script for AR?

    Thanks,

    Mark

    Like

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

    Rafael
    Maracaibo, Venezuela

    Like

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

      -Victoria

      Like

  6. This is perfect. Thanks Victoria!

    -John

    Like

Trackbacks/Pingbacks

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

    Like

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

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

%d bloggers like this: