Day: March 16, 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 uses BULK INSERT to update the item resource planning fields on Item Quantity Master (IV00102).

/*
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).
*/
/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #IV00102_IMPORT
	(
		ITEMNMBR VARCHAR(30)
		,SHRINKAGEFACTOR NUMERIC(19,5) -- Shrinkage Factor
		,PRCHSNGLDTM NUMERIC(19,5) -- Purchasing Lead Time
		,MNFCTRNGFXDLDTM NUMERIC(19,5) -- Manufacturing Fixed Lead Time
		,MNFCTRNGVRBLLDTM NUMERIC(19,5) -- Manufacturing Variable Lead Time
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#IV00102_IMPORT
FROM
	'c:\temp\ItemResourcePlanning.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item does not exist: ' + CAST(IMPORT.ITEMNMBR AS VARCHAR(100))
		FROM
			#IV00102_IMPORT AS IMPORT
		LEFT JOIN
			IV00101 AS ['Item Master'] --Item Master (IV00101)
				ON
					['Item Master'].ITEMNMBR = IMPORT.ITEMNMBR
		WHERE
			['Item Master'].ITEMNMBR IS NULL
	)
GO

INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item/Site assignment does not exist: ' + CAST(IMPORT.ITEMNMBR AS VARCHAR(100)) +  ' / MAIN'
		FROM
			#IV00102_IMPORT AS IMPORT
		LEFT JOIN
			IV00102 AS ['Item Quantity Master '] --Item Quantity Master (IV00102)
				ON
					['Item Quantity Master '].ITEMNMBR = IMPORT.ITEMNMBR
				AND
					['Item Quantity Master '].LOCNCODE = 'MAIN'
		WHERE
			['Item Quantity Master '].ITEMNMBR IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
		-- UPDATE if Email Details present
	UPDATE
		['Item Quantity Master ']
	SET
		SHRINKAGEFACTOR = IMPORT.SHRINKAGEFACTOR
		,PRCHSNGLDTM = IMPORT.PRCHSNGLDTM
		,MNFCTRNGFXDLDTM = IMPORT.MNFCTRNGFXDLDTM
		,MNFCTRNGVRBLLDTM = IMPORT.MNFCTRNGVRBLLDTM
		,INCLDDINPLNNNG = 2
		,REPLENISHMENTMETHOD = 2
		,INCLDMRPMOVEIN = 2
		,INCLDMRPMOVEOUT = 2
		,INCLDMRPCANCEL = 2
	FROM
		IV00102 AS ['Item Quantity Master '] --Item Quantity Master (IV00102)
	INNER JOIN
		#IV00102_IMPORT AS IMPORT
			ON
				IMPORT.ITEMNMBR = ['Item Quantity Master '].ITEMNMBR
	WHERE
		['Item Quantity Master '].LOCNCODE = 'MAIN'
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #IV00102_IMPORT
GO

DROP TABLE #Errors
GO