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 the manufacturing cost accounts on the items using the accounts on the linked classes; this script was created for a client where it was found that classes had been updated and the items still had the wrong codes.

/*
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
	[IC COST Item Master']
SET 
	ICIVCLIXI = ['IC COST Item Class Setup'].ICIVCLIXI
	,ICIVCLABOHIXI = ['IC COST Item Class Setup'].ICIVCLABOHIXI
	,ICIVCOGSLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSLABOHVARIX_I
	,ICIVCCIXI = ['IC COST Item Class Setup'].ICIVCCIXI
	,ICIVCMACHOHIXI = ['IC COST Item Class Setup'].ICIVCMACHOHIXI
	,ICIVCOGSMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSMACHOHVARIX_I
	,ICIVCMIXI = ['IC COST Item Class Setup'].ICIVCMIXI
	,ICIVCMATOHIXI = ['IC COST Item Class Setup'].ICIVCMATOHIXI
	,ICIVCOGSMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVCOGSMATOHVARIX_I
	,ICIVCOIXI = ['IC COST Item Class Setup'].ICIVCOIXI
	,ICIVMFIXI = ['IC COST Item Class Setup'].ICIVMFIXI
	,ICIVMUIXI = ['IC COST Item Class Setup'].ICIVMUIXI
	,ICIVMATUSAGEVAROHIX_I = ['IC COST Item Class Setup'].ICIVMATUSAGEVAROHIX_I
	,ICIVMATUSAGEVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVMATUSAGEVAROHVARIX_I
	,ICIVMVIXI = ['IC COST Item Class Setup'].ICIVMVIXI
	,ICIVPVIXI = ['IC COST Item Class Setup'].ICIVPVIXI
	,ICIVROIXI = ['IC COST Item Class Setup'].ICIVROIXI
	,ICIVSCIXI = ['IC COST Item Class Setup'].ICIVSCIXI
	,ICIVLVIXI = ['IC COST Item Class Setup'].ICIVLVIXI
	,ICIVUPPV_I = ['IC COST Item Class Setup'].ICIVUPPV_I
	,ICIVLABVAROHIX_I = ['IC COST Item Class Setup'].ICIVLABVAROHIX_I
	,ICIVLABVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVLABVAROHVARIX_I
	,ICIVMEVIXI = ['IC COST Item Class Setup'].ICIVMEVIXI
	,ICIVMACHEFFVAROHIX_I = ['IC COST Item Class Setup'].ICIVMACHEFFVAROHIX_I
	,ICIVMACHEFFVAROHVARIX_I = ['IC COST Item Class Setup'].ICIVMACHEFFVAROHVARIX_I
	,ICIVOVIXI = ['IC COST Item Class Setup'].ICIVOVIXI
	,ICIVWLIXI = ['IC COST Item Class Setup'].ICIVWLIXI
	,ICIVWLOHIXI = ['IC COST Item Class Setup'].ICIVWLOHIXI
	,ICIVWIPLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPLABOHVARIX_I
	,ICIVWCIXI = ['IC COST Item Class Setup'].ICIVWCIXI
	,ICIVWMACHOHIXI = ['IC COST Item Class Setup'].ICIVWMACHOHIXI
	,ICIVWIPMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPMACHOHVARIX_I
	,ICIVWMIXI = ['IC COST Item Class Setup'].ICIVWMIXI
	,ICIVWMATOHIXI = ['IC COST Item Class Setup'].ICIVWMATOHIXI
	,ICIVWIPMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVWIPMATOHVARIX_I
	,ICIVWOIXI = ['IC COST Item Class Setup'].ICIVWOIXI
	,ICIVILIXI = ['IC COST Item Class Setup'].ICIVILIXI
	,ICIVILABOHIXI = ['IC COST Item Class Setup'].ICIVILABOHIXI
	,ICIVINVLABOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVLABOHVARIX_I
	,ICIVIMXI = ['IC COST Item Class Setup'].ICIVIMXI
	,ICIVIMACHOHIXI = ['IC COST Item Class Setup'].ICIVIMACHOHIXI
	,ICIVINVMACHOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVMACHOHVARIX_I
	,ICIVIMATOHIXI = ['IC COST Item Class Setup'].ICIVIMATOHIXI
	,ICIVINVMATOHVARIX_I = ['IC COST Item Class Setup'].ICIVINVMATOHVARIX_I
FROM
	CT00102 AS [IC COST Item Master'] --CT00102
INNER JOIN
	CT40401 AS ['IC COST Item Class Setup'] --CT40401
		ON
			['IC COST Item Class Setup'].ITMCLSCD = [IC COST Item Master'].ITMCLSCD
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 imports a tab delimited file and updates the Min Order Qty and Average Lead Time on Vendor Item card; there is error handling built in to ensure the vendor item exists. If there are errors they are returned to the user; vendori item cards are only updated if there are no errors.

/*
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 IV00103 DATA
*/
CREATE TABLE #IV00103_IMPORT
	(
		ITEMNMBR VARCHAR(300)
		,ITEMDESC VARCHAR(300)
		,VENDORID VARCHAR(300)
		,MINORQTY NUMERIC(19,5)
		,AVRGLDTM INTEGER
	)
GO

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

/*
	VALIDATE DATA
*/
--validate price sheet header
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item/Vendor does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100)) + '/' + CAST(['Import'].VENDORID AS VARCHAR(100))
		FROM
			#IV00103_IMPORT AS ['Import']
		LEFT JOIN
			IV00103 AS ['Item Vendor Master'] --Item Vendor Master (IV00103)
				ON
					['Item Vendor Master'].ITEMNMBR = ['Import'].ITEMNMBR
				AND
					['Item Vendor Master'].VENDORID = ['Import'].VENDORID
		WHERE
			['Item Vendor Master'].ITEMNMBR IS NULL
	)
GO

/*
	UPDATE DATA IF NO ERRORS
*/
-- update item/vendors
IF (SELECT COUNT(*) FROM #Errors) = 0
	-- update if item/vendor combination exists
	UPDATE
		['Item Vendor Master']
	SET
		MINORQTY = ['Import'].MINORQTY
		,AVRGLDTM = ['Import'].AVRGLDTM
	FROM
		IV00103 AS ['Item Vendor Master'] --Item Vendor Master (IV00103)
	INNER JOIN
		#IV00103_IMPORT AS ['Import']
			ON
				['Item Vendor Master'].ITEMNMBR = ['Import'].ITEMNMBR
			AND
				['Item Vendor Master'].VENDORID = ['Import'].VENDORID
GO

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

/*
	DROP TEMP TABLES
*/
DROP TABLE #IV00103_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 is the customer equivalent of the ,a href=https://www.azurecurve.co.uk/2021/07/validate-and-insert-update-vendor-emails-in-microsoft-dynamics-gp-from-a-text-file/’>vendor script I posted a while ago; it uses BULK INSERT to upload a text file and updates the email addresses on the supplied customer and address combinations.

/*
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 #CUSTOMER_IMPORT
	(
		CUSTNMBR VARCHAR(30)
		,ADRSCODE VARCHAR(30)
		,EmailToAddress VARCHAR(230)
		,EmailCcAddress VARCHAR(230)
		,EmailBccAddress VARCHAR(230)
	)
GO

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

/*
	VALIDATE DATA
*/
--validate parent item
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Customer Address does not exist: ' + CAST(['Import'].CUSTNMBR AS VARCHAR(100)) + ' / ' + CAST(['Import'].ADRSCODE AS VARCHAR(100))
		FROM
			#CUSTOMER_IMPORT AS ['Import']
		LEFT JOIN
			RM00101 AS ['RM Customer Address Master'] --RM Customer MSTR (RM00101)
				ON
					['RM Customer Address Master'].CUSTNMBR = ['Import'].CUSTNMBR
				AND
					['RM Customer Address Master'].ADRSCODE = ['Import'].ADRSCODE
		WHERE
			['RM Customer Address Master'].ADRSCODE IS NULL
		AND
			['Import'].EmailToAddress IS NOT NULL
	)
GO

/*
	INSERT/UPDATE EMAIL DATA IF NO ERRORS
*/
--insert emails
IF (SELECT COUNT(*) FROM #Errors) = 0
	BEGIN
		-- UPDATE if Email Details present
		UPDATE
			SY
		SET
			SY.EmailToAddress = SY_I.EmailToAddress
			,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
			,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
		FROM
			SY01200 SY --Internet Addresses (SY01200)
		INNER JOIN
			#CUSTOMER_IMPORT AS SY_I
				ON
					SY_I.CUSTNMBR = SY.Master_ID
				AND
					SY.Master_Type = 'CUS'
				AND
					SY.ADRSCODE = SY_I.ADRSCODE
		WHERE
			SY_I.EmailToAddress IS NOT NULL

		-- Insert if no Email Details
		INSERT INTO SY01200
			(
				Master_Type
				,Master_ID
				,ADRSCODE
				,INETINFO
				,EmailToAddress
				,EmailCcAddress
				,EmailBccAddress
			)

			(
				SELECT
					'CUS'
					,CUSTNMBR
					,ADRSCODE
					,''
					,EmailToAddress
					,ISNULL(EmailCcAddress, '')
					,ISNULL(EmailBccAddress, '')
				FROM
					#CUSTOMER_IMPORT AS SY_I
				WHERE
					SY_I.EmailToAddress IS NOT NULL
				AND
					(SELECT
						COUNT(Master_ID)
					FROM
						SY01200
					WHERE
						Master_Type = 'CUS'
					AND
						Master_ID = SY_I.CUSTNMBR
					AND
						ADRSCODE = SY_I.ADRSCODE) = 0
					)
	END
GO

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

/*
	DROP TEMP TABLES
*/
DROP TABLE #CUSTOMER_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 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