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 uploads a text file using BULK INSERT to create manufacturing routings. There is error handling built in to check if the item, machine or route exists and will throw an error if issues are found; routings are only inserted if there are no 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 TEMP ERROR TABLE
*/
CREATE TABLE #Errors
(
Error VARCHAR(1000)
,ROW_ID INT IDENTITY
)
GO
/*
CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #ROUTING_IMPORT
(
ROUTINGNAME_I VARCHAR(31) NOT NULL,
RTSEQDES_I VARCHAR(101) NOT NULL,
ITEMNMBR VARCHAR(31) NOT NULL,
RTSEQNUM_I VARCHAR(11) NOT NULL,
OPCODE_I VARCHAR(7) NOT NULL,
MACHINEID_I VARCHAR(11) NOT NULL,
SETUPTIME_I NUMERIC(19, 5) NOT NULL,
MACHINETIME_I NUMERIC(19, 5) NOT NULL,
RUNTIME_I NUMERIC(19, 5) NOT NULL,
LABORTIME_I NUMERIC(19, 5) NOT NULL,
QUEUETIME_I NUMERIC(19, 5) NOT NULL,
CYCLETIME_I NUMERIC(19, 5) NOT NULL,
MOVETIME_I NUMERIC(19, 5) NOT NULL,
LABORCODE_I VARCHAR(11) NOT NULL,
LABORCODE2_I VARCHAR(11) NOT NULL,
NUMOFEMP SMALLINT NOT NULL,
NUMBEROFMACHINES_I SMALLINT NOT NULL,
WIPOPPERMOSTARTQTY SMALLINT NOT NULL,
)
GO
/*
BULK INSERT
*/
BULK INSERT
#ROUTING_IMPORT
FROM
'c:\temp\ItemRouting.txt'
WITH
(FIELDTERMINATOR = '\t'
,ROWTERMINATOR = '\n'
,FIRSTROW = 2
)
GO
/*
VALIDATE DATA
*/
--validate parent item
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Item Number does not exist: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
FROM
#ROUTING_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 machine id
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Machine does not exist: ' + CAST(['Import'].MACHINEID_I AS VARCHAR(100))
FROM
#ROUTING_IMPORT AS ['Import']
LEFT JOIN
MM010032 AS ['Machine Master'] --MM010032
ON
['Machine Master'].MACHINEID_I = ['Import'].MACHINEID_I
WHERE
['Machine Master'].MACHINEID_I IS NULL
)
GO
--validate existing bom
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Routing for Item already exists: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
FROM
#ROUTING_IMPORT AS ['Import']
LEFT JOIN
RT010001 AS ['routing_mstr'] --RT010001
ON
['routing_mstr'].ITEMNMBR = ['Import'].ITEMNMBR
AND
['routing_mstr'].ROUTINGNAME_I = ['Import'].ROUTINGNAME_I
WHERE
['routing_mstr'].ITEMNMBR IS NOT NULL
)
GO
--validate existing bom lines
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Routing for Item already exists: ' + CAST(['Import'].ITEMNMBR AS VARCHAR(100))
FROM
#ROUTING_IMPORT AS ['Import']
LEFT JOIN
RT010130 AS ['routing_line'] --RT010130
ON
['routing_line'].ITEMNMBR = ['Import'].ITEMNMBR
AND
['routing_line'].ROUTINGNAME_I = ['Import'].ROUTINGNAME_I
AND
['routing_line'].RTSEQNUM_I = ['Import'].RTSEQNUM_I
WHERE
['routing_line'].ITEMNMBR IS NOT NULL
)
GO
/*
INSERT NEW DATA IF NO ERRORS
*/
--insert bom header
IF (SELECT COUNT(*) FROM #Errors) = 0
-- Insert if no Email Details
INSERT INTO RT010001 --RT010001
(
ROUTINGNAME_I
,ITEMNMBR
,RTPRIMARY_I
,REVISIONLEVEL_I
,RTSTATUSDDL_I
,NOTEINDX
)
--VALUES
(
SELECT DISTINCT
ROUTINGNAME_I
,ITEMNMBR
,1 --RTPRIMARY_I
,'' --REVISIONLEVEL_I
,4 --RTSTATUSDDL_I
,0 --NOTEINDX
FROM
#ROUTING_IMPORT AS ['Import']
WHERE
(
SELECT
COUNT(['routing_mstr'].ITEMNMBR)
FROM
RT010001 AS ['routing_mstr']
WHERE
['routing_mstr'].ITEMNMBR = ['Import'].ITEMNMBR
AND
['routing_mstr'].ROUTINGNAME_I = ['Import'].ROUTINGNAME_I
) = 0
)
GO
-- insert bom lines
IF (SELECT COUNT(*) FROM #Errors) = 0
-- Insert if no Email Details
INSERT INTO RT010130 --RT010130
(
ROUTINGNAME_I
,RTSEQNUM_I
,ITEMNMBR
,RTSEQTYPE_I
,RTSEQDES_I
,WCID_I
,OPCODE_I
,RTGWCID_I
,RTMCID_I
,MACHINEID_I
,SETUPTIME_I
,MACHINETIME_I
,RUNTIME_I
,LABORTIME_I
,QUEUETIME_I
,CYCLETIME_I
,MOVETIME_I
,PERCENTCOMPLETE_I
,QUANTITY_I
,LABORCODE_I
,LABORCODE2_I
,RTPARNUM_I
,RTNEXNUM_I
,DWGNUM_I
,TOOLID_I
,WAITHOURS_I
,MULTIPLEEMPLOYEEOP_I
,NUMOFEMP
,LASTSEQUENCETODAY_I
,TAPENUMBER_I
,USERDEF1
,USERDEF2
,NOTEINDX
,MFGNOTEINDEX_I
,USERID
,CREATDDT
,CREATETIME_I
,CHANGEDATE_I
,CHANGETIME_I
,AUTOBACKFLUSHLABOR_I
,AUTOBACKMACHINE_I
,NUMBEROFMACHINES_I
,NUMBEROFCREWS_I
,QAQCNEEDED_I
,POOFFSETDAYS
,WIPOPPERMOSTARTQTY
)
--VALUES
(
SELECT
ROUTINGNAME_I
,RTSEQNUM_I
,ITEMNMBR
,0 --RTSEQTYPE_I
,RTSEQDES_I
,'MAIN' --WCID_I
,OPCODE_I
,'' --RTGWCID_I
,'' --RTMCID_I
,MACHINEID_I
,SETUPTIME_I
,MACHINETIME_I
,RUNTIME_I
,LABORTIME_I
,QUEUETIME_I
,CYCLETIME_I
,MOVETIME_I
,0 --PERCENTCOMPLETE_I
,0 --QUANTITY_I
,LABORCODE_I
,LABORCODE2_I
,'' --RTPARNUM_I
,'' --RTNEXNUM_I
,'' --DWGNUM_I
,'' --TOOLID_I
,0 --WAITHOURS_I
,0 --MULTIPLEEMPLOYEEOP_I
,NUMOFEMP
,0 --LASTSEQUENCETODAY_I
,'' --TAPENUMBER_I
,'' --USERDEF1
,'' --USERDEF2
,0 --NOTEINDX
,0 --MFGNOTEINDEX_I
,USER_ID()
,FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000') --CREATDDT
,FORMAT(GETDATE(), '1900-01-01 HH:mm:ss.000') --CREATETIME_I
,'1900-01-01 00:00:00.000' --CHANGEDATE_I
,'1900-01-01 00:00:00.000' --CHANGETIME_I
,1 --AUTOBACKFLUSHLABOR_I
,1 --AUTOBACKMACHINE_I
,NUMBEROFMACHINES_I
,0 --NUMBEROFCREWS_I
,0 --QAQCNEEDED_I
,0 --POOFFSETDAYS
,WIPOPPERMOSTARTQTY
FROM
#ROUTING_IMPORT AS ['Import']
WHERE
(
SELECT
COUNT(['routing_line'].ITEMNMBR)
FROM
RT010130 AS ['routing_line']
WHERE
['routing_line'].ITEMNMBR = ['Import'].ITEMNMBR
AND
['routing_line'].ROUTINGNAME_I = ['Import'].ROUTINGNAME_I
AND
['routing_line'].RTSEQNUM_I = ['Import'].RTSEQNUM_I
) = 0
)
GO
/*
OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
SELECT Error FROM #Errors ORDER BY ROW_ID
GO
/*
DROP TEMP TABLES
*/
DROP TABLE #ROUTING_IMPORT
GO
DROP TABLE #Errors
GO
Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.