SQL Scripts for Microsoft Dynamics GP: Generate Standard Cost Update Macro from Text File Import

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 imports a tab delimited text file and generates a Dynamics GP macro which can be played back to update the standard costs.

There is built in error handling to validate the supplied item numbers exist.

/*
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 #COST_IMPORT
	(
		ITEMNMBR VARCHAR(100)
		,STDCOST NUMERIC(19,2)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#COST_IMPORT
FROM
	'C:\Temp\STDCOST.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n'
	,FIRSTROW = 2
)
GO

/*
	VALIDATE DATA
*/
--validate ITEM
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
		FROM
			#COST_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

/*
	CREATE MACRO OUTPUT IF NO ERROR
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
SELECT DISTINCT
	'# DEXVERSION=18.00.0028.000 2 2
CheckActiveWin dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' window ''Item_Rollup'' 
  TypeTo field ''(L) Temp Control Number'' , ''' + ['Import'].ITEMNMBR +  '''
  MoveTo field ''NO_CALC_MAT''  # ''FALSE''' + CASE WHEN BM.PPN_I IS NULL THEN + '
# This item doesn''t have a Manufacturing bill of materials and isn''t a component of one.  When you perform a rollup and revalue, costs entered here will overwrite the standard cost displayed in Item Maintenance.  
NewActiveWin dictionary ''Manufacturing''  form DiaLog window DiaLog 
  ClickHit field OK 
NewActiveWin dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' window ''Item_Rollup''' ELSE + '' END + '
  ClickHit field ''NO_CALC_MAT''  # ''TRUE''
  TypeTo field ''Mat Cost''[5] , ''' + CAST(['Import'].STDCOST AS VARCHAR(20)) + '''
  MoveTo field ''Mat Cost Eff Date''[5] 
  TypeTo field ''Mat Cost Eff Date''[5] , ''01012021''
  MoveTo field ''NO_CALC_MAT_FIX_OHD''  # ''FALSE''
  CommandExec dictionary ''Manufacturing''  form ''IC_COST_IV_Item_Rollup'' command ''IC_Save_T_w_Item_Rollup_f_IC_COST_IV_Item_Rollup'' '
FROM
	#COST_IMPORT AS ['Import']
LEFT JOIN
	BM010115 AS BM
		ON
			BM.PPN_I = ['Import'].ITEMNMBR
		OR
			BM.CPN_I = ['Import'].ITEMNMBR
GO

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

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

DROP TABLE #Errors
GO