SQL Scripts for Microsoft Dynamics GP: Insert Extended Pricing Price Sheet Work

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 Work (IV10401) 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 IV10402 DATA
*/
CREATE TABLE #IV10402_IMPORT
	(
		PRCSHID char(15)
        ,EPITMTYP char(1)
        ,ITEMNMBR char(31)
        ,UOFM char(9)
        ,QTYFROM numeric(195)
        ,QTYTO numeric(195)
        ,PSITMVAL numeric(195)
        ,EQUOMQTY numeric(195)
        ,QTYBSUOM numeric(195)
        ,SEQNUMBR int
	)
GO

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

/*
	VALIDATE DATA
*/
--validate price sheet header
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Price Sheet already exists: ' + CAST(['Import'].PRCSHID AS VARCHAR(100))
		FROM
			#IV10402_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 NOT NULL
	)
GO

--validate items
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
		FROM
			#IV10402_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 price sheet rows
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Item ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100)) + ' does not exist on the price sheet: ' + CAST(['Import'].PRCSHID AS VARCHAR(100))
		FROM
			#IV10402_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 NULL
	)
GO

--validate price sheet row price breaks
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'rOW ' + CAST(['Import'].SEQNUMBR AS VARCHAR(100)) + ' already exists on the price sheet: ' + CAST(['Import'].PRCSHID AS VARCHAR(100)) + ' for item ' +  CAST(['Import'].ITEMNMBR AS VARCHAR(100))
		FROM
			#IV10402_IMPORT AS ['Import']
		LEFT JOIN
			IV10402 AS ['Extended Pricing Price Sheet UofM Work'] --Extended Pricing Price Sheet UofM Work (IV10402)
				ON
					['Extended Pricing Price Sheet UofM Work'].PRCSHID = ['Import'].PRCSHID
				AND
					['Extended Pricing Price Sheet UofM Work'].ITEMNMBR = ['Import'].ITEMNMBR
				AND
					['Extended Pricing Price Sheet UofM Work'].SEQNUMBR = ['Import'].SEQNUMBR 
		WHERE
			['Extended Pricing Price Sheet UofM Work'].ITEMNMBR IS NOT NULL
	)
GO

-- validate unit of measure
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Unit of Measure does not exist: ' + CAST(['Import'].UOFM AS VARCHAR(100))
		FROM
			#IV10402_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'].UOFM
		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 IV10402 --Extended Pricing Price Sheet UofM Work (IV10402)
		(
			PRCSHID
           ,EPITMTYP
           ,ITEMNMBR
           ,UOFM
           ,QTYFROM
           ,QTYTO
           ,PSITMVAL
           ,EQUOMQTY
           ,QTYBSUOM
           ,SEQNUMBR
		)
	--VALUES
		(
			SELECT DISTINCT
				PRCSHID
			   ,EPITMTYP
			   ,ITEMNMBR
			   ,UOFM
			   ,QTYFROM
			   ,QTYTO
			   ,PSITMVAL
			   ,EQUOMQTY
			   ,QTYBSUOM
			   ,SEQNUMBR
			FROM
				#IV10402_IMPORT AS ['Import']
			WHERE
				(
					SELECT
						COUNT(['Extended Pricing Price Sheet Header'].PRCSHID)
					FROM
						IV10402 AS ['Extended Pricing Price Sheet Header'] --Extended Pricing Price Sheet UofM Work (IV10402)
					WHERE
						['Extended Pricing Price Sheet Header'].PRCSHID = ['Import'].PRCSHID
					AND
						['Extended Pricing Price Sheet Header'].ITEMNMBR = ['Import'].ITEMNMBR
					AND
						['Extended Pricing Price Sheet Header'].SEQNUMBR = ['Import'].SEQNUMBR
				) = 0
		)
GO

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

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

DROP TABLE #Errors
GO