SQL view for vendor yearly totals in Dynamics GP

January 4, 2012 by Victoria Yudin 20 Comments

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 2009 through 2014 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 https://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 functional currency is shown
-- Note that Returns are assumed as 'on account', if this is not
--      typically the case, Returns should be excluded
-- Updated Jul 23, 2013 to add Vendor Class and Payment Terms
-- Updated Jan 28, 2013 to remove 2007 and add 2013
-- Updated Mar 25, 2014 to remove 2008 and add 2014
-- Updated Sep 19, 2018 to change years to 2013 through 2018
-- Updated Dec 22, 2021 to change years to 2017 through 2022
-- **************************************************************

SELECT
P.VENDORID Vendor_ID,
V.VENDNAME Vendor_Name,
V.VNDCLSID Vendor_Class,
V.PYMTRMID Payment_Terms,

sum(case when year(P.DOCDATE) = 2022 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2022 Billed],
sum(case when year(P.DOCDATE) = 2022 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2022 Paid],
sum(case when year(P.DOCDATE) = 2021 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2021 Billed],
sum(case when year(P.DOCDATE) = 2021 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2021 Paid],
sum(case when year(P.DOCDATE) = 2020 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2020 Billed],
sum(case when year(P.DOCDATE) = 2020 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2020 Paid],
sum(case when year(P.DOCDATE) = 2019 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2019 Billed],
sum(case when year(P.DOCDATE) = 2019 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2019 Paid],
sum(case when year(P.DOCDATE) = 2018 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2018 Billed],
sum(case when year(P.DOCDATE) = 2018 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2018 Paid],
sum(case when year(P.DOCDATE) = 2017 and P.DOCTYPE < 6
   then P.DOCAMNT else 0 end) [2017 Billed],
sum(case when year(P.DOCDATE) = 2017 and P.DOCTYPE = 6
   then P.DOCAMNT else 0 end) [2017 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, V.VNDCLSID, V.PYMTRMID

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.
Share this:
Email
Print
Tweet
Like Loading...


	Related

Dynamics GP, GP Reports code, GP SQL scripts, Payables SQL code

Dynamics GP, GP Reports code, GP SQL view, Payables, SQL code, year end close

Happy New Year – 2012

SQL view for customer yearly totals in Dynamics GP

20 Responses to “SQL view for vendor yearly totals in Dynamics GP”

Joyce Reply November 20, 2018 at 6:40 pm

Hi Victoria,

Thanks for the query. However, when I ran the query above it has error message:
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ‘2012’.
“sum(case when P.DOCTYPE 2012” – This line is just before the “Union All”

And
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ‘)’.

” AND year(DOCDATE) > 2012) P” – the close bracket before P does not have an open bracket to pair with.

Something EW missing in these 2 lines. Can you help?
I would like to create a report to view annual orders we placed on each vendor.

Thanks
Joyce

LikeLike
- Victoria Yudin Reply November 21, 2018 at 6:34 am
  
  Hi Joyce,
  
  Thanks for letting me know there was a problem. Somehow a few lines went missing. I have updated the code on the blog post above. Please let me know if you run into any other problems.
  
  -Victoria
  
  LikeLike
  - Joyce Reply November 21, 2018 at 9:45 pm
    
    Hi Victoria,
    
    The corrected SQL looks great. Do you know which table I should link to in order to show the originating amount? We would like to see how much USD we spent on each overseas vendors when our functional amount is CAD. I found table PM10100 has the originating amount (USD) but not sure how to link it. Would you please advise?
    
    LikeLike
    - Victoria Yudin Reply November 22, 2018 at 5:43 am
      
      Hi Joyce,
      
      The Multicurrency information for Payables transactions is in the MC020103 table. You can link to that table from the other transaction tables by the voucher number (VCHRNMBR) and doc type (DOCTYPE).
      
      -Victoria
      
      LikeLike
Hetal Patel Reply November 14, 2014 at 9:49 am

Victoria
Thanks, I followed all the required steps and created the view, and smartlist, followed the direction for adding permission for smartlist to default user and role.

Still I can only view data if I logged in to GP with SA credential, any suggestion on what am I doing wrong?

LikeLike
- Victoria Yudin Reply November 30, 2014 at 4:07 pm
  Hi Hetal,
  
  If this is your first SmartList based on a SQL view, you will need to give permissions to your users to see SmartLists based on SQL views. Here are the settings under Security Tasks:
  
  Product: SmartList Builder
  
  Type: SmartList Builder Permissions
  
  Series: SmartList Builder Permissions or SmartList Builder (depends on the GP version/Service Pack)
  
  Operations: View SmartLists with SQL Tables
  
  -Victoria
  
  LikeLike
Tahnee Reply July 10, 2014 at 8:23 am

Hi Victoria,

How can I take the above and break it out by date range. I need specific date parameters for example 01-01-2014 thru 05-31-2014.

Thank you for all the help!

LikeLike
- Tahnee Reply July 10, 2014 at 4:39 pm
  
  Sorry, I was being an idiot… to many auditor requests today…my brain is mush! I have it!
  
  LikeLike
Mike Reply January 15, 2014 at 1:32 pm

I wish I had found this before I spent the last 2 hours trying to figure out why my numbers weren’t matching GP. (Hint: I hadn’t included PM20000 in my report) You’ve once again saved me from considerable frustration and unnecessary work. Who knows how much longer I would have spent trying to figure this out. Thank you so much for this excellent blog, Victoria!

LikeLiked by 1 person
Mark Reply October 17, 2013 at 10:14 am

I modified your SQL slightly to always give me the most recent years of data:

sum(case when year(P.DOCDATE) = year(getdate()) and P.DOCTYPE < 6
then P.DOCAMNT else 0 end) [CY Billed],
sum(case when year(P.DOCDATE) = year(getdate())-1 and P.DOCTYPE < 6
then P.DOCAMNT else 0 end) [CY-1 Billed],
…etc

In my report I then created a formula that displays the year number for the appropriate column.

Now I don't have to modify and re-create the view each new year.

Thanks for this website! It's been a GREAT resource for me!

LikeLike
- Victoria Yudin Reply October 17, 2013 at 11:04 am
  
  Hi Mark,
  
  That’s perfect! Thanks for sharing. 🙂
  
  -Victoria
  
  LikeLike
Peter Reply November 13, 2012 at 2:15 pm

The SQL view for vendor yearly totals in Dynamics GP is very handy! Can it be expanded to also show the number of invoices billed for the current year and Life to Date? The view will give the totals as per the Vendor Yearly Summary Inquiry window where you have the Amount Billed (1st line) and the Number of Invoices (last line).

LikeLike
- Victoria Yudin Reply November 15, 2012 at 7:07 am
  Hi Peter,
  
  You can add the following code after line 45 to accomplish this:
  
  ,sum(case when P.DOCTYPE = 1 and year(DOCDATE) = year(getdate()) then 1 else 0 end) [Invoices Cur Yr], sum(case when P.DOCTYPE = 1 then 1 else 0 end) [Invoices Life]
  
  This will only add up the invoices (doc type of 1). To determine the current year it will look at the current date on the SQL server.
  
  -Victoria
  
  Note: after the updates made Jul 23, 2013, this code would need to be added after line 50, instead.
  
  LikeLike