Day: May 9, 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 inserts data into the Extended Pricing Price Sheet UofM Work (IV10402) table and does verification for errors before inserting the data.

/*
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).
*/
/*
Created by Ian Grieve of ISC Software (https://www.isc-software.com/).
*/

/*
	CREATE TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR IV10401 DATA
*/
CREATE TABLE #IV10401_IMPORT
	(
		PRCSHID char(15)
        ,SEQNUMBR int
        ,EPITMTYP char(1)
        ,ITEMNMBR char(31)
        ,BRKPTPRC tinyint
        ,ACTIVE tinyint
        ,BASEUOFM char(9)
        ,PRODTCOD char(1)
        ,PROMOTYP smallint
        ,PROMOLVL smallint
	)
GO

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

/*
	VALIDATE DATA
*/
--validate price sheet header
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Price Sheet does not exist: ' + CAST(['Import'].PRCSHID AS VARCHAR(100))
		FROM
			#IV10401_IMPORT AS ['Import']
		LEFT JOIN
			SOP10110 AS ['Extended Pricing Price Sheet Header'] --Extended Pricing Price Sheet Header (SOP10110)
				ON
					['Extended Pricing Price Sheet Header'].PRCSHID = ['Import'].PRCSHID
		WHERE
			['Extended Pricing Price Sheet Header'].PRCSHID IS NULL
	)
GO

--validate price sheet rows
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100)) + ' already exists on the price sheet: ' + CAST(['Import'].PRCSHID AS VARCHAR(100))
		FROM
			#IV10401_IMPORT AS ['Import']
		LEFT JOIN
			IV10401 AS ['Extended Pricing Price Sheet Work'] --Extended Pricing Price Sheet Work (IV10401)
				ON
					['Extended Pricing Price Sheet Work'].PRCSHID = ['Import'].PRCSHID
				AND
					['Extended Pricing Price Sheet Work'].ITEMNMBR = ['Import'].ITEMNMBR
		WHERE
			['Extended Pricing Price Sheet Work'].ITEMNMBR IS NOT NULL
	)
GO

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

-- validate unit of measure
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Unit of Measure does not exist: ' + CAST(['Import'].BASEUOFM AS VARCHAR(100))
		FROM
			#IV10401_IMPORT AS ['Import']
		LEFT JOIN
			IV40202 AS ['Inventory U of M Schedule Detail Setup'] --Inventory U of M Schedule Detail Setup (IV40202)
				ON
					['Inventory U of M Schedule Detail Setup'].UOFM = ['Import'].BASEUOFM
		WHERE
			['Inventory U of M Schedule Detail Setup'].UOFM IS NULL
	)
GO

/*
	INSERT NEW DATA IF NO ERRORS
*/
--insert price sheet header
IF (SELECT COUNT(*) FROM #Errors) = 0
	-- Insert if no Email Details
	INSERT INTO IV10401 --Extended Pricing Price Sheet Work (IV10401)
		(
			PRCSHID
           ,SEQNUMBR
           ,EPITMTYP
           ,ITEMNMBR
           ,BRKPTPRC
           ,ACTIVE
           ,BASEUOFM
           ,PRODTCOD
           ,PROMOTYP
           ,PROMOLVL
		)
	--VALUES
		(
			SELECT DISTINCT
				PRCSHID
			   ,SEQNUMBR
			   ,EPITMTYP
			   ,ITEMNMBR
			   ,BRKPTPRC
			   ,ACTIVE
			   ,BASEUOFM
			   ,PRODTCOD
			   ,PROMOTYP
			   ,PROMOLVL
			FROM
				#IV10401_IMPORT AS ['Import']
			WHERE
				(
					SELECT
						COUNT(['Extended Pricing Price Sheet Header'].PRCSHID)
					FROM
						IV10401 AS ['Extended Pricing Price Sheet Header'] --Extended Pricing Price Sheet Work (IV10401)
					WHERE
						['Extended Pricing Price Sheet Header'].PRCSHID = ['Import'].PRCSHID
					AND
						['Extended Pricing Price Sheet Header'].ITEMNMBR = ['Import'].ITEMNMBR
				) = 0
		)
GO

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

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

DROP TABLE #Errors
GO