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.

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

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

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

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

    • Hi Ana,

      This is certainly possible. You would need to combine this view with my GL Distributions for AP Transactions view. This will, of course, result in multiple lines for each transaction…so it may be a little strange for the users in something like SmartList or Excel. If you need help with the code, please send me an email and we can talk about that.

      -Victoria

      Like this

  3. Victoria,

    Is there a similar script for AR?

    Thanks,

    Mark

    Like this

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

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

  5. This is perfect. Thanks Victoria!

    -John

    Like this

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 this

  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 this

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

%d bloggers like this: