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

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

Read original post SQL Scripts for Microsoft Dynamics GP: Insert Extended Pricing Price Sheet Work at azurecurve|Ramblings of an IT Professional