Import Inventory BOM

Integration Manager for Dynamics GP doesn’t provide the ability to import the Inventory Bill of Materials (BOM). To overcome this limitation and to import Inventory BOMs’ into GP, We can make use of table import method instead.

Importing Bill of Materials requires two tables BOM00101, BOM00111. To import the BOMs’ (Cards | Inventory | Bill of materials) using Table Import, Follow the steps below

1. Prepare 2 CSV Files for the import. One is BOM Header and the other file is BOM Components.
2. BOM Header must have the following fields.
   • Item Number – This is the parent code and must be an inventory item existing in the inventory. This is called Bill Number
   • Bill Status – Status of the Bill. Set it to 1 as constant. Status of 1 accord Active status for the BOM
   • Status Ord – Assign constant value of  1 for active bills.
   • BM Stock method – Assign 1 for Build if necessary, 2 for stock, 3 for Build status
   • Effective Date – Leave the date blank or fill the date with the date
   • Obsolete Date – Leave the date blank
   • U of M – Assign the same UOM assigned for the parent code in Inventory
   • Note Index – Leave the field until import. Once imported, update the Note Index using sp_getNextnote index to update the note fields.
   • Created Date – Provide the date of creation
   • Modified Date – Provide the modified date
3. BOM Components would have the following fields
   • Item Number – Parent Code of BOM. Same as the Item Number in BOM Header file.
   • Bill Status – Status of the bill same as BOM Header Bill status
   • Status Ord – Same as Status Ord for BOM Header
   • Ord – This is the unique identifier for BOM components for each parent code. GP increments each line item by 16384. So If 1 BOM has 3 lines, each line Ord will be 16384, 32768, 49152. Next BOM will again start with 16384 so on. We can easily achieve this in Excel to assign numbers.
   • Component Item Number – Provide the component Item Number. This must be an item number in Inventory.
   • Component Status – Assign 1 for Active status
   • Comp State – BOM Component state. Always Assign constant 5 as value though there are other values as any other value doesn’t show up the components in the Bill at least when I tested it.
   • Cost Type – Assign 1 for Per Unit cost or 2 for Setup cost
   • Design QTY – Provide the quantity of component
   • Scrap % – Provide the value or leave blank
   • Effective Date – Provide the date or leave blank
   • Obsolete Date – provide the date or leave blank
   • U of M – Provide the component U of M. Make sure this is same as the one set for the component item in Inventory.
   • Note Index – leave this for import and update the field after import using the stored procedure for get next note index.
4. Once the 2 files are ready for import, Remove the Labels in each file as the Table import should not have any headers such as Item Number, U of M so on otherwise it treats that also as data.
5. Go to Cards | Inventory | Bill of Materials | Tools | Integrate | Table Import and select Bill of Materials Header. Provide the Source file, Definition ID, Destination Table, Mappings so on something that looks like below. Once mapped, Click Import.

6. After importing BOM Header, Again Go to Cards | Inventory | Bill of Materials | Tools | Integrate | Table Import and select Bill of Materials Component. Provide all the required fields and mappings same as what we did for BOM header. After mapping it looks something like this. Click Import.

7. Once imported, Run the Stored procedure for note index for both BOM header as well as component to update the Note index field.

I’ll publish a couple of FAQs’ on this BOM Import in my next post.