In response to a reader request, I have created this new view based on my Vendor Yearly Totals view that will show monthly totals for your Dynamics GP Vendors for all years.

This view will show both the numbers and names of the months and assumes that you have 12 periods corresponding to the calendar months. To get results for a particular year you can run the following query against this view after creating it:

select * from view_Vendor_Monthly_Totals
where [Year] = 2011 --change the year as desired

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_Monthly_Totals
AS

/***************************************************************
view_Vendor_Monthly_Totals
Created Dec 9, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates please see http://victoriayudin.com/gp-reports/
Shows totals for all AP vendors per month and year
Assumes periods are calendar months
Results shown for calendar months and functionaly currency
***************************************************************/

SELECT VT.VENDORID Vendor_ID,
       VM.VENDNAME Vendor_Name,
       VM.VNDCLSID Class_ID,
       case VM.VENDSTTS
          when 1 then 'Active'
          when 2 then 'Inactive'
          when 3 then 'Temporary'
          end Vendor_Status,
       case VM.TEN99TYPE
          when 1 then 'Not a 1099 Vendor'
          when 2 then 'Dividend'
          when 3 then 'Interest'
          when 4 then 'Miscellaneous'
          end [1099_Type],
       VM.PYMTRMID Payment_Terms_ID,
       VT.PERIODID Period,
       datename(month, DATEADD(month, VT.PERIODID, -1 )) [Month],
       VT.YEAR1 [Year],
       sum(VT.AMBLDLIF) Amount_Billed,
       sum(VT.AMTPDLIF) Amount_Paid,
       sum(VT.TEN99ALIF) [1099_Amount],
       sum(VT.FINCHLIF) Finance_Charges,
       sum(VT.WROFSLIF) Writeoffs,
       sum(VT.RTRNSLIF) [Returns],
       sum(VT.TRDTKLIF) Trade_Discounts,
       sum(VT.DISAVLIF) Term_Discounts_Avail,
       sum(VT.DISTKNLF) Term_Discounts_Taken,
       sum(VT.DISLSTLF) Term_Discounts_Lost,
       sum(VT.Withholding_LIFE) Withholding,
       sum(VT.NOINVLIF) Num_Of_Invoices,
       sum(VT.NFNCHLIF) Num_Of_Finance_Charges,
       VM.ADDRESS1 Address_1,
       VM.ADDRESS2 Address_2,
       VM.ADDRESS3 Address_3,
       VM.CITY City,
       VM.[STATE] [State],
       VM.ZIPCODE Zip_Code,
       VM.COUNTRY Country,
       VM.TXIDNMBR Tax_ID

FROM PM00202 VT

INNER JOIN PM00200 VM
       ON VT.VENDORID = VM.VENDORID

WHERE VT.HISTTYPE = 0

GROUP BY VT.VENDORID, VM.VENDNAME, VM.VNDCLSID, VM.VENDSTTS, VM.TEN99TYPE,
		 VM.PYMTRMID, VT.PERIODID, VT.YEAR1, VM.ADDRESS1, VM.ADDRESS2,
         VM.ADDRESS3, VM.CITY, VM.[STATE], VM.ZIPCODE, VM.COUNTRY, VM.TXIDNMBR

GO
GRANT SELECT ON view_Vendor_Monthly_Totals 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: featured, GP Reports code, GP SQL view, Payables, SQL code