Day: May 4, 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 Header (SOP10110) 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 SOP10110 DATA
*/
CREATE TABLE #SOP10110_IMPORT
	(
		PRCSHID char(15)
        ,DESCEXPR char(51)
        ,NTPRONLY tinyint
        ,ACTIVE tinyint
        ,STRTDATE datetime
        ,ENDDATE datetime
        ,NOTEINDX numeric(195)
        ,PROMO tinyint
        ,CURNCYID char(15)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#IV10401_IMPORT
FROM
	'c:\temp\SOP110110.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
			#SOP10110_IMPORT AS ['Import']
		LEFT JOIN
			SOP10110 AS ['Extended Pricing Price Sheet Header'] --SO10110
				ON
					['Extended Pricing Price Sheet Header'].PRCSHID = ['Import'].PRCSHID
		WHERE
			['Extended Pricing Price Sheet Header'].PRCSHID IS NOT NULL
	)
GO

--validate customers
INSERT INTO #Errors
	(Error)
--VALUES
	(
		SELECT
			'Customer does not exist: ' + CAST(['Import'].PRCSHID AS VARCHAR(100))
		FROM
			#SOP10110_IMPORT AS ['Import']
		LEFT JOIN
			RM00101 AS ['RM Customer MSTR'] --RM Customer MSTR (RM00101)
				ON
					['RM Customer MSTR'].CUSTNMBR = ['Import'].PRCSHID
		WHERE
			['RM Customer MSTR'].CUSTNMBR IS NOT 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 SOP10110 --Extended Pricing Price Sheet Header (SOP10110)
		(
			PRCSHID
           ,DESCEXPR
           ,NTPRONLY
           ,ACTIVE
           ,STRTDATE
           ,ENDDATE
           ,NOTEINDX
           ,PROMO
           ,CURNCYID
		)
	--VALUES
		(
			SELECT DISTINCT
				PRCSHID
			   ,DESCEXPR
			   ,NTPRONLY
			   ,ACTIVE
			   ,STRTDATE
			   ,ENDDATE
			   ,NOTEINDX
			   ,PROMO
			   ,CURNCYID
			FROM
				#SOP10110_IMPORT AS ['Import']
			WHERE
				(
					SELECT
						COUNT(['Extended Pricing Price Sheet Header'].PRCSHID)
					FROM
						SOP10110 AS ['Extended Pricing Price Sheet Header'] --Extended Pricing Price Sheet Header (SOP10110)
					WHERE
						['Extended Pricing Price Sheet Header'].PRCSHID = ['Import'].PRCSHID
				) = 0
		)
GO

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

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

DROP TABLE #Errors
GO