Category: GP (Great Plains)

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 creates a table function which will split a string based on the supplied delimiter; it was created for a user who split their item number into segments, separated with hyphens:

/*
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).
*/
IF OBJECT_ID('[dbo].[uf_AZRCRV_SplitString]') IS NOT NULL
BEGIN
	DROP FUNCTION [dbo].[uf_AZRCRV_SplitString];
END;
GO

CREATE FUNCTION [dbo].[uf_AZRCRV_SplitString](
    @String VARCHAR(8000),
    @Delimiter VARCHAR(10)
)
RETURNS @ReturnTable TABLE(
    id int IDENTITY(1,1) NOT NULL,
    part varchar(50) NULL
)
AS
BEGIN
	Declare @iSpaces int
	Declare @part varchar(50)
	
	Set @String = RTRIM(@String)
	
	--initialize spaces
	Select @iSpaces = charindex(@Delimiter, @String, 0)
	While @iSpaces > 0
	
	Begin
		Select @part = substring(@String, 0, charindex(@Delimiter, @String, 0))
		
		Insert Into @ReturnTable(part)
			Select @part
		
		Select @String = substring(@String,charindex(@Delimiter, @String, 0) + len(@Delimiter),len(@String) - charindex(' ', @String, 0))
		
		Select @iSpaces = charindex(@Delimiter, @String, 0)
	end
	
	If len(@String) > 0
		Insert Into @ReturnTable
			Select @String
	
    RETURN 
END
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 the top level BOM for a manufacturing order.

/*
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
	['Mfg Order Master'].MANUFACTUREORDER_I
	,['Mfg Order Master'].ITEMNMBR
	,'CUSTOMER ITEM NUMBER' AS CustomerItemNumber
	,['Item Master'].ITEMDESC
	,['Mfg Order Master'].ENDQTY_I
	,['Inventory U of M Schedule Setup'].BASEUOFM
	,'DRAWING NUMBER' AS DrawingNumber
	,'REVISION' AS Revision
	,'SPEC NUMBER AND ISSUE' AS SpecificationAndIssue
	,['Bill Of Material Line File'].CPN_I AS ComponentS
FROM
	WO010032 AS ['Mfg Order Master'] --WO010032
INNER JOIN
	IV00101 AS ['Item Master'] --Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Mfg Order Master'].ITEMNMBR
INNER JOIN
	IV40201 AS ['Inventory U of M Schedule Setup'] --Inventory U of M Schedule Setup (IV40201)
		ON
			['Inventory U of M Schedule Setup'].UOMSCHDL = ['Item Master'].UOMSCHDL
INNER JOIN
	BM010115 AS ['Bill Of Material Line File'] --BM010115
		ON
			['Bill Of Material Line File'].PPN_I = ['Mfg Order Master'].ITEMNMBR

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

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