Tag: SQL Scripts for Microsoft Dynamics GP

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who was having problems with the Next Voucher Number in the PM Setup File (PM40100) table being set back to am old value and needed a way to set it to the correct value. It uses the uf_AZRCRV_GetAlpha and uf_AZRCRV_GetNumber functions while selecting the highest current voucher number to write away the next number after this.

The client had a prefix of PINV on later invoices so the select uses this to select the correct highest number; the script was run automatically on a schedule using SQL Server Agent.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DECLARE @NTVCHNUM AS VARCHAR(20)

SELECT
	TOP 1
		@NTVCHNUM = dbo.uf_ISC_SW_GetAlpha(CNTRLNUM) + CAST(dbo.uf_ISC_SW_GetNumber(CNTRLNUM) + 1 AS VARCHAR(15))
	FROM
		PM00400
	WHERE
		CNTRLTYP = 0
	AND
		LEFT(CNTRLNUM, 2) = 'PINV'
	ORDER BY
		CNTRLNUM DESC

UPDATE
	PM40100
SET
	NTVCHNUM = @NTVCHNUM
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script contains a SQL view which reports on sales transactions from one year compared against the prior year; it returns the number of transactions, number of items, costs, sales price and profit margins.

-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_SalesReportWithYearPriorComparison', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_SalesReportWithYearPriorComparison
GO
-- create view
CREATE VIEW uv_AZRCRV_SalesReportWithYearPriorComparison AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
WITH cteSalesReportData AS
	(
		SELECT
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM') as YearMth
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].XTNDPRCE
					ELSE
						['Sales Transaction Amounts History'].XTNDPRCE * -1
					END
			) AS 'Sales In Period'
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].EXTDCOST
					ELSE
						['Sales Transaction Amounts History'].EXTDCOST * -1
					END
			) AS 'COGS In Period'
			,SUM(['Sales Transaction Amounts History'].MARGINPERC) / SUM(['Sales Transaction Amounts History'].NUMBER) AS 'Margin %'
			,SUM(['Sales Transaction Amounts History'].QTYTOINV) AS 'Quantity'
			,COUNT(['Sales Transaction History'].SOPNUMBE) AS 'TrxCount'
		FROM
			IV00101 AS ['Item Master']
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,LOCNCODE
					,ITEMNMBR
					,CMPNTSEQ
					,XTNDPRCE
					,EXTDCOST
					,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
					,QTYTOINV
					,1 AS NUMBER
				FROM
					SOP10200
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,LOCNCODE
						,ITEMNMBR
						,CMPNTSEQ
						,XTNDPRCE
						,EXTDCOST
						,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
						,QTYTOINV
						,1 AS NUMBER
					FROM
						SOP30300
			) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].ITEMNMBR = ['Item Master'].ITEMNMBR
				AND
					['Sales Transaction Amounts History'].CMPNTSEQ = 0
				AND
					['Sales Transaction Amounts History'].SOPTYPE IN (3,4)
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,DOCID
					,DOCDATE
				FROM
					SOP10100
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,DOCID
						,DOCDATE
					FROM
						SOP30200
			) AS ['Sales Transaction History']
				ON
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
				AND
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
		GROUP BY
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM')
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
	)

SELECT
	DB_NAME() AS 'Company'
	,['This Year'].LOCNCODE AS 'Site'
	,['This Year'].YearMth
	,['This Year'].ITEMNMBR AS 'Item Number'
	,['This Year'].ITEMDESC AS 'Item Description'
	,['This Year'].ITMCLSCD AS 'Item Class'
	,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
	,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
	,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
	,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
	,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
	,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
	,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
	,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
	,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % This Year'
	,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % Last Year'
FROM
	cteSalesReportData AS ['This Year']
LEFT JOIN
	cteSalesReportData AS ['Last Year']
		ON
			['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
		AND
			['Last Year'].YearMth = ['This Year'].YearMth - 100 -- subtract 100 from 202003 to get March last year
		AND
			['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
WHERE
	['This Year'].YearMth IS NOT NULL
UNION
	SELECT
		DB_NAME() AS 'Company'
		,['Last Year'].LOCNCODE AS 'Site'
		,['Last Year'].YearMth + 100 AS YearMth
		,['Last Year'].ITEMNMBR AS 'Item Number'
		,['Last Year'].ITEMDESC AS 'Item Description'
		,['Last Year'].ITMCLSCD AS 'Item Class'
		,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
		,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
		,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
		,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
		,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
		,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
		,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
		,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
		,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % This Year'
		,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % Last Year'
	FROM
		cteSalesReportData AS ['Last Year']
	LEFT JOIN
		cteSalesReportData AS ['This Year']
			ON
				['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
			AND
				['Last Year'].YearMth = ['This Year'].YearMth -100 -- subtract 100 from 202003 to get March last year
			AND
				['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
	WHERE
		['This Year'].YearMth IS NULL
	AND
		['Last Year'].YearMth <= FORMAT(GETDATE(), 'yyyyMM') - 100
GO

-- grant select permissions to DYNGRP
GRANT SELECT ON uv_AZRCRV_SalesReportWithYearPriorComparison TO DYNGRP
GO