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 user to get the next Temporary Creditor ID (making sure the selected number hasn;t been used already); it uses the uf_AZRCRV_GetAlpha and uf_AZRCRV_GetNumber functions posted over the last couple of articles in this series.

There may be an “official” way of getting the next Temporary Creditor ID, but I wasn’t able to determine what that was, so this script was created.

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_ISC_SW_GetNextTemporaryVendorID', N'P') IS NOT NULL
    DROP PROCEDURE usp_ISC_SW_GetNextTemporaryVendorID
GO
-- create stored proc
CREATE PROCEDURE [dbo].[usp_ISC_SW_GetNextTemporaryVendorID]
/*
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).
*/
AS
	SET NOCOUNT ON

	BEGIN TRAN
		
		-- select next number from table into variable
		DECLARE @NXTVNDID VARCHAR(15)
		
		DECLARE @AlphaPart VARCHAR(15)
		DECLARE @NumberPart VARCHAR(15)

		DECLARE @NewNXTVNDID AS VARCHAR(15)

		DECLARE @LOOP AS INTEGER = 1
		WHILE (@LOOP >= 1)
		BEGIN
			
			SET @NXTVNDID = (SELECT NXTVNDID FROM PM40100) --PM Setup File (PM40100)

			SELECT @AlphaPart = dbo.uf_AZRCRV_GetAlpha(@NXTVNDID)

			SELECT @NumberPart = dbo.uf_AZRCRV_GetNumber(@NXTVNDID)

			SELECT @LOOP = COUNT(0) FROM PM00200 WHERE VENDORID = @NXTVNDID --PM Vendor Master File (PM00200)

			SET @NumberPart = @NumberPart + 1

			SET @NewNXTVNDID = @AlphaPart + REPLICATE('0', LEN(@NXTVNDID) - LEN(@AlphaPart) - LEN(@NumberPart)) + CONVERT(VARCHAR(20), @NumberPart)
		
			UPDATE
				PM40100
			SET
				NXTVNDID = @NewNXTVNDID
		END

		SELECT @NXTVNDID AS NXTVNDID
		
	COMMIT TRAN
	
	-- return variable containing next number
	RETURN
GO
GRANT EXECUTE ON usp_ISC_SW_GetNextTemporaryVendorID 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 selects all Saes transactions at a status of work and returns the ship to address of the transaction and the default from the customer card.

/*
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
	['Sales Transaction Work'].CUSTNMBR AS 'Customer Number'
	,['Sales Transaction Work'].CUSTNAME AS 'Customer Name'
	,['Sales Transaction Work'].PRSTADCD AS 'Trx Address Code'
	,['Customer Address Master'].ADRSCODE AS 'Cust  Address Code'
	,['Sales Transaction Work'].ShipToName AS 'Trx Ship To Name'
	,['Customer Address Master'].ShipToName AS 'Cust Ship To Name'
	,['Sales Transaction Work'].ADDRESS1 AS 'Trx Address 1'
	,['Customer Address Master'].ADDRESS1 AS 'Cust Address 1'
	,['Sales Transaction Work'].ADDRESS2 AS 'Trx Address 2'
	,['Customer Address Master'].ADDRESS2 AS 'Cust Address 2'
	,['Sales Transaction Work'].CITY AS 'Trx City'
	,['Customer Address Master'].CITY AS 'Cust City'
	,['Sales Transaction Work'].STATE AS 'Trx State'
	,['Customer Address Master'].STATE AS 'Cust State'
FROM
	SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100)
INNER JOIN
	RM00101 AS ['Customer Master'] --RM Customer MSTR (RM00101)
		ON
			['Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
INNER JOIN
	RM00102 AS ['Customer Address Master'] --Customer Master Address File (RM00102)
		ON
			['Customer Address Master'].CUSTNMBR = ['Customer Master'].CUSTNMBR
		AND
			['Customer Address Master'].ADRSCODE = ['Customer Master'].PRSTADCD

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 updates all sales transactions at a status of work by setting the Ship To Name to the Customer Name where the transaction ship to name is blank.

/*
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).
*/
UPDATE
	['Sales Transaction Work']
SET
	['Sales Transaction Work'].ShipToName = ['Sales Transaction Work'].CUSTNAME
FROM
	SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100)
WHERE
	['Sales Transaction Work'].ShipToName = ''
AND
	['Sales Transaction Work'].SOPTYPE = 2
AND
	['Sales Transaction Work'].CUSTNMBR NOT LIKE 'SAM%'
AND
	LEN(['Sales Transaction Work'].CUSTNAME) > 0

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