SQL View to Return Sales By Customer By Fiscal Year

Microsoft Dynamics GPThe last post was a SQL view to return purchases by vendor by fiscal year in an update of a prior script which only returned the information by the calendar year; I did a similar view at the time for the Sales series which also returned information for the calendar year. This post is an update of that one to return the information linking into the Financial Calendar setup to return the sales by customer by financial year.

IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,['Receivables Transactions'].DOCDATE AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,YEAR1 AS DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	INNER JOIN
		SY40101
			ON
				DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,YEAR1 AS DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		INNER JOIN
			SY40101
				ON
					DOCDATE BETWEEN FSTFSCDY AND LSTFSCDY
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR >= ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,['Receivables Transactions'].DOCDATE
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear 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.

SQL View to Return Purchases By Vendor By Fiscal Year

Microsoft Dynamics GPI 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 (http://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.

SQL Snippet: Generate Row Numbers

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

In thi spost, I am going to show how use ROW_NUMBER to generate a unique row number. There are three examples of code.

This first example, the simplest of the three, shows how to generate a unique number for each row in the recordset:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

The second example, shows a row number can be assigned to the lines of each transaction (this is accomplished :

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	PONUMBER
	,ORD
	,ITEMNMBR
	,ITEMDESC
	,ROW_NUMBER() OVER(PARTITION BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
	POP30110
GO

This final example, takes the first example and shows how we can select a range of row numbers (this is useful if you are selecting data to display on a page):

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
WITH POTRX AS
	(SELECT
		PONUMBER
		,ORD
		,ITEMNMBR
		,ITEMDESC
		,ROW_NUMBER() OVER(ORDER BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
	FROM
		POP30110)
SELECT
	*
FROM
	POTRX
WHERE
	CUSTOM_ROW_ID BETWEEN 40 AND 59
GO

SQL Function To Return Workflow Approval Status

Microsoft Dynamics GPIf you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.

We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.

CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(14)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
				'Submitted'
			WHEN ['Workflow History'].Workflow_Action = 2 THEN
				'Resubmitted'
			WHEN ['Workflow History'].Workflow_Action = 3  THEN
				'Approved'
			WHEN ['Workflow History'].Workflow_Action = 4 THEN
				'Task Complete'
			WHEN ['Workflow History'].Workflow_Action = 5 THEN
				'Rejected'
			WHEN ['Workflow History'].Workflow_Action = 6 THEN
				'Delegated'
			WHEN ['Workflow History'].Workflow_Action = 7 THEN
				'Recalled'
			WHEN ['Workflow History'].Workflow_Action = 8 THEN
				'Escalated'
			WHEN ['Workflow History'].Workflow_Action = 9 THEN
				'Edit'
			WHEN ['Workflow History'].Workflow_Action = 10 THEN
				'Final Approved'
			END
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO

GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
GO

This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.

View To Return Sales Orders (Work Status) Requiring An Assembly

Microsoft Dynamics GPI have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).

I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.


CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://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
	CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN
		SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3)
	ELSE
		RTRIM(SOP102.SOPNUMBE)
	END +
	CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN
		'_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2))
	ELSE
		''
	END AS 'Assembly'
	,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number'
	,RTRIM(SOP102.ITEMNMBR) AS 'Item Number'
	,CASE WHEN SOP102.UOFM = 'EACH' THEN
		CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10))
	ELSE
		CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10))
	END AS 'Quantity'
	,SOP102.UOFM AS 'UofM'
	,SOP101.BACHNUMB AS 'Sales Batch'
FROM
	SOP10200 SOP102 WITH (NOLOCK)
INNER JOIN
	SOP10100 SOP101 WITH (NOLOCK)
		ON
			SOP101.SOPNUMBE = SOP102.SOPNUMBE
		AND
			SOP101.SOPTYPE = SOP102.SOPTYPE
INNER JOIN
	BM00101 BM101 WITH (NOLOCK)
		ON
			BM101.ITEMNMBR = SOP102.ITEMNMBR
		AND
			BM101.Bill_Status = 1
LEFT JOIN
	BM10200 BM102 WITH (NOLOCK)
		ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
LEFT JOIN
	BM30200 BM302 WITH (NOLOCK)
		ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
WHERE
	SOP102.SOPTYPE = 2
AND
	BM102.TRX_ID IS NULL
AND
	BM302.TRX_ID IS NULL
GO

GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP