SQL Scripts for Microsoft Dynamics GP: Update Min Order Qty and Average Lead Time on Vendor Item From Text File

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