Day: January 19, 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 allows a user to insert National Accounts from a CSV file. I created this script fairly recently for a client who was implementing the National Accounts module and had a lot of accounts to link together.

The script validates the parent and child accounts and will not update if there are any 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 TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #NA_IMPORT
	(
		PARENTCUSTNMBR VARCHAR(100)
		,CHILDCUSTNMBR VARCHAR(100)
	)
GO

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

/*
	VALIDATE DATA
*/
-- validate parent customers
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Parent Customer does not exist: ' + CAST(NA_I.PARENTCUSTNMBR AS VARCHAR(100))
		FROM
			#NA_IMPORT AS NA_I
		LEFT JOIN
			RM00101 AS ['RM Customer Master File'] --RM Customer MSTR (RM00101)
				ON
					['RM Customer Master File'].CUSTNMBR = NA_I.PARENTCUSTNMBR
		WHERE
			['RM Customer Master File'].CUSTNMBR IS NULL
	)
GO

-- validate child customers
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Child Customer does not exist: ' + CAST(NA_I.CHILDCUSTNMBR AS VARCHAR(100))
		FROM
			#NA_IMPORT AS NA_I
		LEFT JOIN
			RM00101 AS ['RM Customer Master File'] --RM Customer MSTR (RM00101)
				ON
					['RM Customer Master File'].CUSTNMBR = NA_I.CHILDCUSTNMBR
		WHERE
			['RM Customer Master File'].CUSTNMBR IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS - add Parent to parent customer master
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	UPDATE
		SY
	SET
		SY.CPRCSTNM = ISNULL(NA_I.PARENTCUSTNMBR, '')
	FROM
		RM00101 SY --RM Customer MSTR (RM00101)
	INNER JOIN
		(SELECT DISTINCT PARENTCUSTNMBR FROM #NA_IMPORT) AS NA_I
			ON
				NA_I.PARENTCUSTNMBR = SY.CUSTNMBR
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS - add Parent to child customer master
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	UPDATE
		SY
	SET
		SY.CPRCSTNM = ISNULL(NA_I.PARENTCUSTNMBR, '')
	FROM
		RM00101 SY --RM Customer MSTR (RM00101)
	INNER JOIN
		#NA_IMPORT AS NA_I
			ON
				NA_I.CHILDCUSTNMBR = SY.CUSTNMBR
GO

/*
	INSERT NEW DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	INSERT INTO RM00105 --RM National Accounts Master (RM00105)
		(
			CPRCSTNM,NAALLOWRECEIPTS,NACREDITCHECK,NAFINANCECHARGE,NAHOLDINACTIVE,NADEFPARENTVEN,NOTEINDX,CREATDDT,MODIFDT
		)

		(
			SELECT DISTINCT 
				PARENTCUSTNMBR,1,0,0,0,0,0,FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000'),FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')
			FROM
				#NA_IMPORT 
			WHERE
				(SELECT
					COUNT(CPRCSTNM)
				FROM
					RM00105 --RM National Accounts Master (RM00105)
				WHERE
					CPRCSTNM = #NA_IMPORT.PARENTCUSTNMBR) = 0
				)
GO

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

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

DROP TABLE #Errors
GO