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

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

Read original post SQL Scripts for Microsoft Dynamics GP: Update Min Order Qty and Average Lead Time on Vendor Item From Text File at azurecurve|Ramblings of an IT Professional