Month: March 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 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

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