Microsoft Dynamics AX 2012 Xpp – BOMs Import
Purpose: The purpose of this document is to illustrate how to write a required minimum X++ code in Microsoft Dynamics AX 2012 in order to import BOMs (Bill of materials).
Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some imports more complex. However the structure of tables comprising BOMs didn't change. Please note that after you import BOMs you may need to perform approval and activation of BOM and BOM versions, this can also be done as a part of initial import.
Solution: Appropriate tables buffers (BOMTable, BOMVersion, BOM) will be used when writing X++ code in Microsoft Dynamics AX 2012 in order to import BOMs. Alternatively AxBC classes may be used instead of table buffers.
Data Model:
Table Name | Table Description |
BOMTable | The BOMTable table contains all of the bills of materials. A bill of materials is associated with a site and an item group. For each bill of materials, the information is stored about whether it has been approved and by whom. |
BOMVersion | The BOMVersion table contains all the BOM versions. This table connects to the BOMTable table in order to specify the BOM to which the version refers, and it connects to the InventTable table to specify to which item the BOM version is assigned. The BOMVersion table also connects to the InventDim table to specify a site for the BOM version. Additionally, the BOMVersion table stores information about the approval and activation for each BOM version. |
BOM | The BOM table contains the bill of materials lines. A BOM line connects to an item to consume and a BOM version to which the line applies. |
Data model diagram:
Development:
ttsBegin: Use ttsBegin to start a transaction.
clear: The clear method clears the contents of the record.
initValue: The initValue method initializes the fields of the record.
initFrom*: The initFrom* methods usually populate the fields of the child record based on the fields on the parent record. Example is initFromSalesTable method on SalesLine table.
validateWrite: The validateWrite method checks whether the record can be written.
write: The write method writes the record to the database.
insert: The insert method inserts the record into the database.
doInsert: The doInsert method inserts the record into the database. Calling doInsert ensures that any X++ code written in the insert method of the record is not executed. Calling insert always executes the X++ code written in the insert method of the record.
ttsCommit: Use ttsCommit to commit a transaction.
One prerequisite that I'll need for import is to assign a worker to my current AX User, this is needed because I'll approve and activate BOM and BOM Version right after their creation
User – User relations
Source code:
static void BOMXppImport(Args _args) { BOMTable bomTable; BOMVersion bomVersion; BOM bom; InventDim inventDim; BOMApprove bomApprove; BOMVersionApprove bomVersionApprove; BOMVersionActivate bomVersionActivate; BOMId bomId; BOMEndSchedConsump bomEndSchedConsump; ProdFlushingPrincipBOM prodFlushingPrincip; WrkCtrConsumption wrkCtrConsumption; BOMType bomType; BOMConsumpType bomConsumpType; ItemId itemId = "D0001", bomItemId = "M0001"; BOMQty bomQty = 1; NoYes calculation; BOMMeasureHeight height; BOMMeasureWidth width; BOMMeasureDepth depth; BOMMeasureDensity density; BOMMeasureConstant constant; BOMRoundUp bomRoundUp; BOMRoundUpQty bomRoundUpQty; BOMPosition position; OprNumBOM oprNum; StartDate fromDate = today(); EndDate todate = today(); VendAccount vendId; UnitOfMeasureSymbol unitID = "ea"; ConfigGroupId configGroupId; BOMFormula bomFormula; BOMQtySerie bomQtySerie; ItemBOMId itemBOMId; ItemRouteId itemRouteId; PBAId itemPBAId; ScrapVar scrapVar; ScrapConst scrapConst; InventDimId inventDimId = InventDim::inventDimIdBlank(); InventSiteId siteId = "1"; InventLocationId warehouseId = "11"; BOMId newBOMId() { NumberSeq numberSeq; ; numberSeq = BOMTable::numberSeq(); numberSeq.used(); return numberSeq.num(); } ; ttsBegin; bomId = newBOMId(); bomTable.clear(); bomTable.initValue(); bomTable.bomId = bomId; bomTable.SiteId = siteId; bomTable.Name = "Alex" + bomId; bomTable.ItemGroupId = InventTable::find(itemId).itemGroupId(); //bomTable.Approved = NoYes::Yes; //bomTable.Approver = HcmWorker::userId2Worker(curUserId()); if (bomTable.validateWrite()) { bomTable.insert(); bomVersion.clear(); bomVersion.initValue(); bomVersion.ItemId = itemId; //bomVersion.Approved = NoYes::Yes; //bomVersion.Approver = HcmWorker::userId2Worker(curUserId()); //bomVersion.Active = NoYes::Yes; bomVersion.initFromBOMTable(bomTable); if (bomVersion.validateWrite()) { bomVersion.insert(false);// do not check for circularity bom.clear(); bom.initValue(); bom.bomId = bomId; bom.LineNum = BOM::lastLineNum(bomId) + 1; bom.EndSchedConsump = bomEndSchedConsump; bom.ProdFlushingPrincip = prodFlushingPrincip; bom.WrkCtrConsumption = wrkCtrConsumption; bom.bomConsump = bomConsumpType; bom.initFromInventTable(InventTable::find(bomItemId)); bom.bomType = bomType; bom.bomQty = bomQty; bom.Calculation = NoYes::Yes; bom.Height = height; bom.Width = width; bom.Depth = depth; bom.Density = density; bom.Constant = constant; bom.RoundUp = bomRoundUp; bom.RoundUpQty = bomRoundUpQty; bom.Position = position; bom.OprNum = oprNum; bom.FromDate = fromDate; bom.ToDate = todate; bom.VendId = vendId; bom.UnitId = unitID; bom.ConfigGroupId = configGroupId; bom.Formula = bomFormula; bom.bomQtySerie = bomQtySerie; bom.ItemBOMId = itemBOMId; bom.ItemRouteId = itemRouteId; bom.ItemPBAId = itemPBAId; bom.ScrapVar = scrapVar; bom.ScrapConst = scrapConst; inventDim.clear(); inventDim.InventSiteId = siteId; inventDim.InventLocationId = warehouseId; bom.InventDimId = InventDim::findOrCreate(inventDim).inventDimId; if (bom.validateWrite()) { bom.insert(); } } } ttsCommit; if (bomTable) { bomApprove = bomApprove::newBOMTable(bomTable); bomApprove.parmApprover(HcmWorker::userId2Worker(curUserId())); bomApprove.run(); } if (bomVersion) { bomVersionApprove = BOMRouteVersionApprove::newBOMVersion(bomVersion); bomVersionApprove.parmApproveBOM(true); bomVersionApprove.parmRemove(false); bomVersionApprove.parmApprover(HcmWorker::userId2Worker(curUserId())); bomVersionApprove.run(); bomVersionActivate = BOMRouteVersionActivate::newBOMVersion(bomVersion); bomVersionActivate.run(); } } |
Result:
BOM line
Please note that I removed approval and activation from the existing BOM Version prior to importing a new one to avoid overlap (based on dates/dimensions) validation error
BOM
Please note that you can create your BOM structure once and then assign to a different BOM Versions (for different items) multiple times. Or you may choose to create a new BOM structure for each BOM Version (for particular item) depending on your business requirements
BOM line
Note: Microsoft Dynamics AX 2012 Demo Data (Company USMF) was used for this example
Version: Microsoft Dynamics AX 2012 RTM, R2, R3
Summary: In this document I explained how to write a required minimum X++ code in Microsoft Dynamics AX 2012 in order to import BOMs. Appropriate table buffers were used when writing X++ code in Microsoft Dynamics AX 2012. This approach can be very handy for POC's or in situation with always changing requirements. You can also use Microsoft Dynamics AX Excel Add-in to import relatively small amounts of data. Please consider using DIXF (Data Import Export Framework) for import of significant amounts of data when performance is an important consideration. DIXF (Data Import Export Framework) provides a standard template for import of BOMs.
Author: Alex Anikiev, PhD, MCP
Tags: Dynamics ERP, Dynamics AX 2012, X++, Xpp, Data Import, Data Conversion, Data Migration, BOMs, Bill of materials
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.