Month: April 2023

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 will return all of the numeric characters from a string; if you have a string of TEMP0001A99 it will return 000199.

-- drop FUNCTION if it exists
IF OBJECT_ID (N'uf_AZRCRV_GetNumber', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetNumber
GO
--CREATE FUNCTION
CREATE FUNCTION uf_AZRCRV_GetNumber(@string NVARCHAR(100))
/*
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).
*/
RETURNS NVARCHAR(100)
BEGIN
  RETURN
  (
		SELECT CAST(
			(
				SELECT
					CASE WHEN SUBSTRING(@string, n, 1) LIKE '[0-9]' THEN
						SUBSTRING(@string, n, 1)
					ELSE
						''
					END
				FROM
					(
						SELECT
							number
						FROM
							master..spt_values
						WHERE
							type = 'P' AND number BETWEEN 1 AND 100
					) AS Nums(n)
				WHERE
					n <= LEN(@string) FOR XML PATH('')
			) AS NVARCHAR(100)
		)  
	);
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetNumber TO DYNGRP
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 will return all of the alpha characters from a string; if you have a string of TEMP0001A it will return TEMPA.

-- drop FUNCTION if it exists
IF OBJECT_ID (N'uf_AZRCRV_GetAlpha', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetAlpha
GO
--CREATE FUNCTION
CREATE FUNCTION uf_AZRCRV_GetAlpha(@string NVARCHAR(100))
/*
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).
*/
RETURNS NVARCHAR(100)
BEGIN
  RETURN
	(
		SELECT CAST(
			(
				SELECT
					CASE WHEN SUBSTRING(@string, n, 1) LIKE '[^0-9]' THEN
						SUBSTRING(@string, n, 1)
					ELSE
						''
					END
				FROM
					(
						SELECT
							number
						FROM
							master..spt_values
						WHERE
							type = 'P' AND number BETWEEN 1 AND 100
					) AS Nums(n)
				WHERE
					n <= LEN(@string) FOR XML PATH('')
			) AS NVARCHAR(100)
		)  
	);
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetAlpha TO DYNGRP
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 returns a list of items with serial numbers which were purchased and the linked sales transaction.

CREATE VIEW uv_AZRCRV_AllocatedSerialNumbers 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).
*/
SELECT
	['Purchasing Receipt Line Quantities'].PONUMBER
	,['Purchasing Receipt Line Quantities'].VENDORID
	,['Purchasing Receipt Line Quantities'].POPRCTNM
	,['Sales Serial/Lot Work and History'].ITEMNMBR
	,[Purchasing Serial Lot History'].SERLTNUM
	,['Sales Serial/Lot Work and History'].SOPTYPE
	,CASE ['Sales Serial/Lot Work and History'].SOPTYPE
		WHEN 1 THEN	'Quote'
		WHEN 2 THEN	'Order'
		WHEN 3 THEN	'Invoice'
		WHEN 5 THEN	'Back Order'
		ELSE ''
	END AS 'Type'
	,['Sales Serial/Lot Work and History'].SOPNUMBE
	,['Sales Transactions'].DOCDATE
	,['Sales Transactions'].DOCID
FROM
	POP30330 AS [Purchasing Serial Lot History']
INNER JOIN
	POP10500 AS ['Purchasing Receipt Line Quantities']
		ON
			['Purchasing Receipt Line Quantities'].POPRCTNM = [Purchasing Serial Lot History'].POPRCTNM
		AND
			['Purchasing Receipt Line Quantities'].RCPTLNNM = [Purchasing Serial Lot History'].RCPTLNNM
LEFT JOIN
	SOP10201 AS ['Sales Serial/Lot Work and History']
		ON
			['Sales Serial/Lot Work and History'].SERLTNUM = [Purchasing Serial Lot History'].SERLTNUM
LEFT JOIN
	(
		SELECT
			SOPNUMBE
			,SOPTYPE
			,DOCID
			,DOCDATE
			,CUSTNMBR
			,CUSTNAME
		FROM
			SOP10100 AS ['Sales Transaction Work']
		UNION ALL
			SELECT
				SOPNUMBE
				,SOPTYPE
				,DOCID
				,DOCDATE
				,CUSTNMBR
				,CUSTNAME
			FROM
				SOP30200 AS ['Sales Transaction History']
	) AS ['Sales Transactions']
		ON
			['Sales Transactions'].SOPNUMBE = ['Sales Serial/Lot Work and History'].SOPNUMBE
		AND
			['Sales Transactions'].SOPTYPE = ['Sales Serial/Lot Work and History'].SOPTYPE
GO

GRANT SELECT ON uv_AZRCRV_AllocatedSerialNumbers TO DYNGRP
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 was created for a client to run to rename the company database with a prefix and add when they copied the databases from production to either the dev or test servers; to allow for a single script to be used for both systems, the script includes an IF statement.

/*
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 @CompanyNamePrefix AS VARCHAR(4)

IF @@SERVERNAME = 'SQL-DEV-01\GP' -- dev
	SET @CompanyNamePrefix = 'DEV'
IF @@SERVERNAME = 'SQL-TEST-01\GP' -- test
	SET @CompanyNamePrefix = 'TEST: '
	
UPDATE
	['Company Master']
SET
	CMPNYNAM = LEFT(@CompanyNamePrefix + ': ' + CAST(RTRIM(['Company Master'].CMPNYNAM) + ' ' AS CHAR(150)), 150)
FROM
	DYNAMICS..SY01500 AS ['Company Master'] [[Company Master (SY01500)
WHERE
	INTERID = DB_NAME()
AND
	LEFT(['Company Master'].CMPNYNAM, 3) <> @CompanyNamePrefix
AND
	LEFT(['Company Master'].CMPNYNAM, 4) <> @CompanyNamePrefix