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
