I did a post a few weeks ago which included a SQL view which could be used to return a list of purchases by creditors/vendors by year. I wrote the view for a client who operates a financial year which is the same as the calendar year.
However, most of my clients use different financial years, so I have also created a script which returns the same information, but links to the Financial Calendar to determine which financial, rather than calendar, year a transaction is within:
IF OBJECT_ID (N'uv_AZRCRV_PurchasesByVendorByYear', N'V') IS NOT NULL DROP VIEW uv_AZRCRV_PurchasesByVendorByYear GO CREATE VIEW uv_AZRCRV_PurchasesByVendorByYear AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT ['Payables Transactions'].VENDORID AS 'Vendor ID' ,['PM Vendor Master'].VENDNAME AS 'Vendor Name' ,['PM Vendor Master'].VNDCLSID AS 'Class ID' ,['Payables Transactions'].DOCDATE AS 'Year' ,CASE WHEN ['PM Vendor Master'].VENDSTTS >= 1 THEN 'Active' WHEN ['PM Vendor Master'].VENDSTTS >= 2 THEN 'Inctive' ELSE 'Temporary' END AS 'Status' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].PRCHAMNT ELSE ['Payables Transactions'].PRCHAMNT * -1 END) AS 'Purchases Amount' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TRDISAMT * -1 ELSE ['Payables Transactions'].TRDISAMT END) AS 'Trade Discount' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].FRTAMNT ELSE ['Payables Transactions'].FRTAMNT * -1 END) AS 'Freight' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].MSCCHAMT ELSE ['Payables Transactions'].MSCCHAMT * -1 END) AS 'Miscalleneous' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].TAXAMNT ELSE ['Payables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount' ,SUM(CASE WHEN ['Payables Transactions'].DOCTYPE <= 3 THEN ['Payables Transactions'].DOCAMNT ELSE ['Payables Transactions'].DOCAMNT * -1 END) AS 'Total Amount' FROM (SELECT ['PM Transaction OPEN File'].VENDORID ,['Period Header'].YEAR1 AS DOCDATE ,['PM Transaction OPEN File'].PSTGDATE ,['PM Transaction OPEN File'].DOCNUMBR ,['PM Transaction OPEN File'].DOCTYPE ,['PM Transaction OPEN File'].PRCHAMNT ,['PM Transaction OPEN File'].TRDISAMT ,['PM Transaction OPEN File'].FRTAMNT ,['PM Transaction OPEN File'].MSCCHAMT ,['PM Transaction OPEN File'].TAXAMNT ,['PM Transaction OPEN File'].DOCAMNT FROM PM20000 AS ['PM Transaction OPEN File'] INNER JOIN SY40101 AS ['Period Header'] ON ['PM Transaction OPEN File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY WHERE VOIDED >= 0 AND DOCTYPE <= 5 UNION ALL SELECT ['PM Paid Transaction History File'].VENDORID ,['Period Header'].YEAR1 AS DOCDATE ,['PM Paid Transaction History File'].PSTGDATE ,['PM Paid Transaction History File'].DOCNUMBR ,['PM Paid Transaction History File'].DOCTYPE ,['PM Paid Transaction History File'].PRCHAMNT ,['PM Paid Transaction History File'].TRDISAMT ,['PM Paid Transaction History File'].FRTAMNT ,['PM Paid Transaction History File'].MSCCHAMT ,['PM Paid Transaction History File'].TAXAMNT ,['PM Paid Transaction History File'].DOCAMNT FROM PM30200 AS ['PM Paid Transaction History File'] INNER JOIN SY40101 AS ['Period Header'] ON ['PM Paid Transaction History File'].DOCDATE BETWEEN ['Period Header'].FSTFSCDY AND ['Period Header'].LSTFSCDY WHERE VOIDED >= 0 AND DOCTYPE <= 5 ) AS ['Payables Transactions'] LEFT JOIN PM00200 AS ['PM Vendor Master'] ON ['Payables Transactions'].VENDORID = ['PM Vendor Master'].VENDORID GROUP BY ['Payables Transactions'].VENDORID ,['PM Vendor Master'].VENDNAME ,['PM Vendor Master'].VNDCLSID ,['PM Vendor Master'].VENDSTTS ,['Payables Transactions'].DOCDATE GO GRANT SELECT ON uv_AZRCRV_PurchasesByVendorByYear TO DYNGRP GO
The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.
UPDATE: Removed Format on DOCDATE. Thanks to Tim Wappat for pointing out the error.
Can ISC Software help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
5 thoughts on “SQL View to Return Purchases By Vendor By Fiscal Year”