There has been a lot of talk lately about the year end close Payables and Receivables. I feel like I have spent the last week or two justifying my reasoning for not needing to perform the year end close for Payables and Receivables to many of my customers and blog readers. (For more on this, please see my Year end close in Dynamics GP blog post.)

My main reasoning has always been that the only thing accomplished by the year end close is updating the ‘amounts since last close’ year-to-date and last-year totals and since that can be easily (and maybe even better?) accomplished by a custom report, why waste time on the year end close for these modules in GP? However, where is this custom report?

For Payables – you now have 2 choices.  I previously published this view that results in one row per vendor per year with the columns being the different possible totals GP tracks for vendors. This is often useful for comparing year to year information for one vendor at a time.

However, many people have asked to see this in columns representing years, so they can see all the vendor totals at the same time for all the years. That’s what you have below. I included separate columns for amounts billed and paid – please take a look at the notes (in green) for more details on the logic. The code below will give you 2007 through 2012 calendar years as well as ‘life-to-date’ totals. You can add or remove years as needed following my example.

I will be posting a similar view for Receivables in the next few days, so keep an eye out. For more Dynamics GP Payables code, take a look at my Payables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and coding tips.

CREATE VIEW view_Vendor_Totals_by_Year
AS

-- ****************************************************************
-- Created Jan 4, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please check http://victoriayudin.com/gp-reports/
-- Includes all posted payables transactions in GP
-- Document dates and calendar years are used for groupings
-- Voided transactions are excluded
-- Billed amount adds Invoices, Finance Charges and Misc Charges
--      and subtracts Credit Memos and Returns
-- Only fucntional currency is shown
-- Note that Returns are assumed as 'on account', if this is not
--      typically the case, Returns should be excluded
-- ****************************************************************

SELECT
P.VENDORID [Vendor ID],
V.VENDNAME [Vendor Name],
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2012 Billed],
sum(case when year(P.DOCDATE) = 2012 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2012 Paid],
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2011 Billed],
sum(case when year(P.DOCDATE) = 2011 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2011 Paid],
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2010 Billed],
sum(case when year(P.DOCDATE) = 2010 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2010 Paid],
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2009 Billed],
sum(case when year(P.DOCDATE) = 2009 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2009 Paid],
sum(case when year(P.DOCDATE) = 2008 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2008 Billed],
sum(case when year(P.DOCDATE) = 2008 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2008 Paid],
sum(case when year(P.DOCDATE) = 2007 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2007 Billed],
sum(case when year(P.DOCDATE) = 2007 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2007 Paid],
sum(case when P.DOCTYPE < 6 then P.DOCAMNT else 0 end) [Life Billed],
sum(case when P.DOCTYPE = 6 then P.DOCAMNT else 0 end) [Life Paid]

FROM --all posted payables transactions, exclude voids
 (SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1 else DOCAMNT end DOCAMNT
 FROM PM20000
 WHERE VOIDED = 0
 UNION ALL
 SELECT VENDORID, DOCTYPE, DOCDATE, PSTGDATE,
 case when DOCTYPE in (4,5) then DOCAMNT *-1 else DOCAMNT end DOCAMNT
 FROM PM30200
 WHERE VOIDED = 0) P

INNER JOIN -- vendor master
 PM00200 V
 ON V.VENDORID = P.VENDORID

GROUP BY P.VENDORID, V.VENDNAME

GO
GRANT SELECT ON view_Vendor_Totals_by_Year 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.


Filed under: Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code Tagged: Dynamics GP, featured, GP Reports code, GP SQL view, Payables, SQL code, year end close