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 BOMs. There is error handling built in to check if the item or BOM exists and will throw an error if issues are found; BOMs 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 #BOM_IMPORT
(
PPN_I VARCHAR(30)
,CPN_I VARCHAR(30)
,BOMSEQ_I INTEGER
,QUANTITY_I NUMERIC(19,5)
,UOFM VARCHAR(9)
)
GO
/*
BULK INSERT
*/
BULK INSERT
#BOM_IMPORT
FROM
'c:\temp\ItemBOM.txt'
WITH
(FIELDTERMINATOR = '\t'
,ROWTERMINATOR = '\n'
,FIRSTROW = 2
)
GO
/*
VALIDATE DATA
*/
--validate parent item
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Parent Item does not exist: ' + CAST(['Import'].PPN_I AS VARCHAR(100))
FROM
#BOM_IMPORT AS ['Import']
LEFT JOIN
IV00101 AS ['Item Master'] --Item Master (IV00101)
ON
['Item Master'].ITEMNMBR = ['Import'].PPN_I
WHERE
['Item Master'].ITEMNMBR IS NULL
)
GO
-- validate component item
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'Component Item ' + CAST(['Import'].CPN_I AS VARCHAR(100)) + ' does not exist for ' + CAST(['Import'].PPN_I AS VARCHAR(100))
FROM
#BOM_IMPORT AS ['Import']
LEFT JOIN
IV00101 AS ['Item Master'] --Item Master (IV00101)
ON
['Item Master'].ITEMNMBR = ['Import'].CPN_I
WHERE
['Item Master'].ITEMNMBR IS NULL
)
GO
--validate existing bom
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'BOM for Item already exists: ' + CAST(['Import'].PPN_I AS VARCHAR(100))
FROM
#BOM_IMPORT AS ['Import']
LEFT JOIN
BM010415 AS ['BOM Revision'] --BM010415
ON
['BOM Revision'].ITEMNMBR = ['Import'].PPN_I
WHERE
['BOM Revision'].ITEMNMBR IS NOT NULL
)
GO
--validate existing bom lines
INSERT INTO #Errors
(Error)
--VALUES
(
SELECT
'BOM for Item already exists: ' + CAST(['Import'].PPN_I AS VARCHAR(100))
FROM
#BOM_IMPORT AS ['Import']
LEFT JOIN
BM010115 AS ['Bill Of Material Line File'] --BM010115
ON
['Bill Of Material Line File'].PPN_I = ['Import'].PPN_I
WHERE
['Bill Of Material Line File'].PPN_I 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
#BOM_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 bom header
IF (SELECT COUNT(*) FROM #Errors) = 0
-- Insert if no Email Details
INSERT INTO BM010415 --BM010415
(
ITEMNMBR
,BOMCAT_I
,BOMNAME_I
,REVISIONLEVEL_I
,EFFECTIVEDATE_I
,BACKFLUSHITEM_I
,BOMTYPE_I
,LOCNCODE
,WCID_I
,Net_Phantom_Inventory
,CHANGEDATE_I
,CHANGEBY_I
,MFGNOTEINDEX3_I
)
--VALUES
(
SELECT DISTINCT
PPN_I -- ITEMNMBR
,1 --BOMCAT_I
,'' --BOMNAME_I
,1 --REVISIONLEVEL_I
,'1900-01-01 00:00:00.000' --EFFECTIVEDATE_I
,0 --BACKFLUSHITEM_I
,1 --BOMTYPE_I
,'' --LOCNCODE
,'' --WCID_I
,0 --Net_Phantom_Inventory
,FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000') --CHANGEDATE_I
,USER_ID() --CHANGEBY_I
,0 --MFGNOTEINDEX3_I
FROM
#BOM_IMPORT AS ['Import']
WHERE
(
SELECT
COUNT(['BOM Revision'].ITEMNMBR)
FROM
BM010415 AS ['BOM Revision']
WHERE
['BOM Revision'].ITEMNMBR = ['Import'].PPN_I
) = 0
)
GO
-- insert bom lines
IF (SELECT COUNT(*) FROM #Errors) = 0
-- Insert if no Email Details
INSERT INTO BM010115 --BM010115
(
PPN_I
,CPN_I
,BOMCAT_I
,BOMNAME_I
,BOMTYPE_I
,BOMSEQ_I
,POSITION_NUMBER
,SUBCAT_I
,SUBNAME_I
,SUB_REV_LEVEL_SEQ_I
,QUANTITY_I
,OPTPERCENT_I
,SCRAPPERCENT_I
,FLOORSTOCK_I
,EFFECTIVEINDATE_I
,EFFECTIVEOUTDATE_I
,ALTERNATE_I
,ALTERNATEPARTFOR_I
,ALT_FOR_BOM_SEQ_I
,LEADTIMEOFFSET_I
,LEADTIMEOFFSETINC_I
,BOMUSERDEF1_I
,BOMUSERDEF2_I
,BOMSINGLELOT_I
,BOMENGAPPROVAL_I
,WCID_I
,LOCNCODE
,BACKFLUSHITEM_I
,CHANGEDATE_I
,USERID
,OPTIONED_ITEM_I
,ACTUAL_CONSUMED_CHECK_I
,FIXED_QTY_I
,UOFM
,U_Of_M_2
,QTYBSUOM
,OFFSET_FROM_I
,MFGNOTEINDEX_I
,MFGNOTEINDEX2_I
)
--VALUES
(
SELECT
PPN_I
,CPN_I
,1 --BOMCAT_I
,'' --BOMNAME_I
,1 --BOMTYPE_I
,BOMSEQ_I --BOMSEQ_I
,BOMSEQ_I --POSITION_NUMBER
,1 --SUBCAT_I
,'' --SUBNAME_I
,0 --SUB_REV_LEVEL_SEQ_I
,QUANTITY_I
,0 --OPTPERCENT_I
,0 --SCRAPPERCENT_I
,0 --FLOORSTOCK_I
,'1900-01-01 00:00:00.000' --EFFECTIVEINDATE_I
,'1900-01-01 00:00:00.000' --EFFECTIVEOUTDATE_I
,0 --ALTERNATE_I
,'' --ALTERNATEPARTFOR_I
,0 --ALT_FOR_BOM_SEQ_I
,0 --LEADTIMEOFFSET_I
,0 --LEADTIMEOFFSETINC_I
,'' --BOMUSERDEF1_I
,'' --BOMUSERDEF2_I
,0 --BOMSINGLELOT_I
,0 --BOMENGAPPROVAL_I
,'' --WCID_I
,'MAIN' --LOCNCODE
,0 --BACKFLUSHITEM_I
,FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000') --CHANGEDATE_I
,USER_ID()
,0 --OPTIONED_ITEM_I
,0 --ACTUAL_CONSUMED_CHECK_I
,0 --FIXED_QTY_I
,UOFM
,UOFM --U_Of_M_2
,1 --QTYBSUOM
,1 --OFFSET_FROM_I
,0 --MFGNOTEINDEX_I
,0 --MFGNOTEINDEX2_I
FROM
#BOM_IMPORT AS ['Import']
WHERE
(
SELECT
COUNT(['Bill Of Material Line File'].PPN_I)
FROM
BM010115 AS ['Bill Of Material Line File']
WHERE
['Bill Of Material Line File'].PPN_I = ['Import'].PPN_I
AND
['Bill Of Material Line File'].CPN_I = ['Import'].CPN_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 #BOM_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.