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:
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.
create view view_Current_Payables_Aging_Detail as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Current_Payables_Aging_Detail -- Apr 24, 2011 - 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/ -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 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.DUEDATE, 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.DUEDATE, 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.DUEDATE, 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.DUEDATE, 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.
Follow this link:
SQL view for current Payables aging detail in Dynamics GP
April 24, 2013
Â· Victoria Yudin Â· No Comments
Tags: dynamics, Dynamics GP, extender, featured, fixed-assets, gp reports code, gp sql scripts, gp sql view, management, payables, reports-viewer, upgrade Â· Posted in: Blogs I Follow Total Views: 369
My great friend, Leslie Vail – The Dynamics Confessor, has released a great series of posts about the Report Writer functions available in Microsoft Dynamics GP.
These Report Writer functions are Dexterity global functions which are exposed to the Report Writer so they can be included in calculated fields as user defined functions.Â There are many functions available in the system, but most have not been documented well.
Over time, the Microsoft Dynamics GP Support Team has created Knowledge Base (KB) articles which demonstrate how to use a number of the common Report Writer functions in the system. Leslie has researched and gathered the articles together for us.
Here are some related articles from this blog:Â
Thanks Leslie for compiling your great series.Â
See Mark, it is not just me who can see the power of the built-in Microsoft Dynamics GP Report Writer.
April 24, 2013
Â· David Musgrave Â· No Comments
Tags: application, Customizations, Dex.ini, Dexterity, extender, featured, General Ledger, Inventory, security, Smartlist Builder, windows Â· Posted in: Blogs I Follow Total Views: 335
For those of you not aware, RapidStart is all-new with this release of Dynamics GP 2013.Â It’s no longer the product it had been, it’s all new and works like you do.Â It has some great new features, like allowing you to save configurations after you have created them, allowing you to build a library of configurations, saving you a ton of time later.
And the story isn’t only about partners – customers are benefitting as well.Â We have customers now using the tool to roll out additional companies in their system, and they are seeing an 80% reduction in the time that it takes to configure a new company.
RapidStart Configuration allows you to save the work in-process, and complete sections as you get the information in – just the way a consultant works.Â And RapidStart Migration allows you to bring in data from QuickBooks, Peachtree or from a specially formatted Excel spreadsheet that we provide.
What is really exciting is customers are now demanding that partners use this exciting tool so that they can put the hours where they are of higher value – Reporting, Business Intelligence, and other ERP tweaks that add much more value than configurations.
So, how does that get us to blackberries?Â You have to read Gloria’s blog to find out!
I’m sure most of you know already that Service Pack 1 is now available!Â Now is a great time to plan your upgrades.
What’s coming up next?
- Business Portal for Microsoft Dynamics GP 2013 on SharePoint 2010 will be coming soon.Â (Either this week or next week)Â Support for SharePoint 2013 will come at a later time.
- Latin American Spanish and French-Canadian will be coming in May.Â This translation will be Service Pack 1.
- Our strategy is to release service packs every 6 months or so.Â We have not announced an official date of the next service pack.Â We will also have a Feature Pack in the future.
For detailed information on the service pack, check out this blog post.
Collapse detailed report into summary view – Management Reporter RU5. MSDN Blogs > Dynamics Corporate Performance Management > Collapse detailed report into summary view – Management Reporter RU5 …
See original article:
Collapse detailed report into summary view … – MSDN Blogs
We have just released a new build of Extender Standard for Microsoft Dynamics GP 2013 that will allow you to upgrade from version Dynamics GP 10 as well as Dynamics GP 2010. This essentially means that anyone that was on our standard versions of Extender can now upgrade to the 2013 build.
Testing on the Extender Enterprise 2013 version is still going on and things are looking good for the June 1st release date.
Additionally, we have released the new version of SmartView External that can be accessed outside of Dynamics GP. This version will work for Dynamics GP 10, 2010 and 2013 and is a blazing fast way of accessing your SmartLists and grouping them with easy drag and drop.
Director of Product Management
Originally posted here:
2013 Product Release Updates: Extender & SmartView
If you are planning to upgrade to GP 2013, please consider to analyze your customizations carefully as there might be some changes between the application versions, I been in a situation were we upgraded from GP 2010 R2 to GP 2010 and the customer has a customized report for GL Transaction Entry Inquiry Report, the report didnâ€™t work properly as the core table of the Report has been changed.
This is the only thing I found so far, but I wanted to let you know that you might need to plan redoing the customizations of this report upon upgrading to GP 2013.