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 values in the IVR10015 table for existing items.

/*
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 #IVR10015_IMPORT
	(
		ITEMNMBR VARCHAR(30)
		,[EFFECTIVEDATE_I] VARCHAR(100)
		,[ITEMFULFILLMETHOD] INTEGER
		,[ITEMSTATUS_I] INTEGER
		,[MAKEBUYCODE_I] INTEGER
		,[CALCMRP_I] TINYINT
	)
GO

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

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

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
		-- UPDATE if Email Details present
	UPDATE
		['Item Engineering File']
	SET
		EFFECTIVEDATE_I = IMPORT.EFFECTIVEDATE_I
		,ITEMFULFILLMETHOD = IMPORT.ITEMFULFILLMETHOD
		,ITEMSTATUS_I = IMPORT.ITEMSTATUS_I
		,MAKEBUYCODE_I = IMPORT.MAKEBUYCODE_I
		,CALCMRP_I = IMPORT.CALCMRP_I
	FROM
		IVR10015 AS ['Item Engineering File'] --IVR10015
	INNER JOIN
		#IVR10015_IMPORT AS IMPORT
			ON
				IMPORT.ITEMNMBR = ['Item Engineering File'].ITEMNMBR
GO

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

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

DROP TABLE #Errors
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 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

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 items from Item Master (IV00101) and creates a Dynamics GP macro which can be played back to delete the items. It was created as some items were imported during an implementation and then it was decided to change how items were configured requiring all current items to be removed; there was no transactional data entered so a clean delete was still possible.

/*
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
	'# DEXVERSION=18.00.0028.000 2 2
CheckActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' 
  TypeTo field ''Item Number'' , ''' + RTRIM(ITEMNMBR) + '''
  MoveTo field Inactive  # ''FALSE''
  MoveTo field ''Delete Button'' 
  ClickHit field ''Delete Button'' 
# Are you sure you want to delete this record?
NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
  ClickHit field OK 
NewActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' 
'
FROM
	IV00101