Microsoft Dynamics AX 2012 Xpp - Routes 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 Routes.
Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some imports more complex. The structure of tables comprising core Routes data didn't change, however the data model related to resource requirements did change and was enhanced. Please note that after you import Routes you may need to perform approval and activation of Route and Route versions, this can also be done as a part of initial import.
Solution: Appropriate tables buffers (RouteTable, RouteVersion, Route) will be used when writing X++ code in Microsoft Dynamics AX 2012 in order to import Routes. Alternatively AxBC classes may be used instead of table buffers.
Data Model:
Table Name | Table Description |
RouteTable | The RouteTable table contains routes, which in turn contain information that is required to guide an item through production. Each route includes operations to perform, the sequence for the operations, resources involved in completing the operations, and standard times for the setup and run of the production. |
RouteVersion | The RouteVersion table contains versions for the routes defined in the RouteTable table. A route version connects to a route and additionally has parameters that define whether the route version is valid from a certain date or for a certain quantity. A route version needs to be approved to be used. Therefore, each route version record is started and approved by the property that is stored. |
Route | The Route table contains the operations that represent the routes. Each record contains information about an operation that belongs to a route. Each operation connects to an operation stored in the RouteOprTable table. |
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 RouteXppImport(Args _args) { RouteTable routeTable; RouteVersion routeVersion; Route route; Route routePrevious; RouteOpr routeOpr; InventDim inventDim; RouteApprove routeApprove; RouteVersionApprove routeVersionApprove; RouteVersionActivate routeVersionActivate; ItemId itemId = "D0001"; RouteId routeId; OprNum oprNum; OprNumNext oprNumNext; RouteOprId oprId = "Testing"; RouteOprId nextOprId; RouteAccErrorPct accError; RouteOprPriority oprPriority; RouteErrorPct errorPct; SchedJobLinkType linkType; JmgJobPayType payType; RouteOprTimeQueueBefore queueTimeBefore; RouteOprTimeSetup setupTime; RouteOprTimeProcess processTime = 1; RouteOprQtyProcessNumOf processPerQty; RouteOprTimeTransport transpTime; RouteOprTimeQueueAfter queueTimeAfter; RouteOprQtyTransferBatch transferBatch; RouteHourFactor toHours; RouteCostCategoryIdSetup setupCategoryId; RouteCostCategoryIdProcess processCategoryId; RouteCostCategoryIdQty qtyCategoryId; RouteOprType routeOprType; PropertyId propertyId; RouteGroupId routeGroupId = "Proc"; RouteFormulaFactor formulaFactor1; RouteFormula formula; WrkCtrIdCost wrkCtrIdCost; RefRecId activity; InventSiteId siteId = "1"; RouteId newRouteId() { NumberSeq numberSeq; ; numberSeq = RouteTable::numberSeq(); numberSeq.used(); return numberSeq.num(); } OprNum findMaxOprNum(RouteId _routeIdTmp) { Route routeTmp; select maxof(OprNum) from routeTmp where routeTmp.RouteId == _routeIdTmp; return routeTmp.OprNum; } ; ttsBegin; routeId = newRouteId(); routeTable.clear(); routeTable.initValue(); routeTable.RouteId = routeId; routeTable.Name = "Alex" + routeId; routeTable.ItemGroupId = InventTable::find(itemId).itemGroupId(); //routeTable.Approved = NoYes::Yes; //routeTable.Approver = HcmWorker::userId2Worker(curUserId()); if (routeTable.validateWrite()) { routeTable.insert(); routeVersion.clear(); routeVersion.initValue(); routeVersion.ItemId = itemId; //routeVersion.Approved = NoYes::Yes; //routeVersion.Approver = HcmWorker::userId2Worker(curUserId()); //routeVersion.Active = NoYes::Yes; routeVersion.initFromRouteTable(routeTable); inventDim.clear(); inventDim.InventSiteId = siteId; routeVersion.InventDimId = InventDim::findOrCreate(inventDim).inventDimId; if (routeVersion.validateWrite()) { routeVersion.insert(); route.clear(); route.initValue(); // update previous operation which has their next operation set to the current operation select firstonly forupdate routePrevious index hint OperationIdx order by routePrevious.OprNum desc where routePrevious.RouteId == routeId; route.RouteId = routeTable.RouteId; if (oprNum) { route.OprNum = oprNum; } else { if (route.OprNum == 0) { route.OprNum = findMaxOprNum(routeTable.RouteId) + 10; route.write(); } } if (routePrevious.RecId != 0 && routePrevious.OprNumNext == 0) { routePrevious.OprNumNext = route.OprNum; routePrevious.update(); } if (oprNumNext) { route.OprNumNext = oprNumNext; } else { if (nextOprId) { route.OprNumNext = findMaxOprNum(routeTable.RouteId) + 10; } } if (oprId) route.OprId = oprId; route.AccError = accError; route.OprPriority = oprPriority; route.ErrorPct = errorPct; route.LinkType = linkType; route.JobPayType = payType; routeOpr.clear(); routeOpr.initValue(); routeOpr.OprId = oprId; routeOpr.QueueTimeBefore = queueTimeBefore; routeOpr.SetupTime = setupTime; routeOpr.ProcessTime = processTime; routeOpr.ProcessPerQty = processPerQty; routeOpr.TranspTime = transpTime; routeOpr.QueueTimeAfter = queueTimeAfter; routeOpr.TransferBatch = transferBatch; routeOpr.ToHours = toHours; routeOpr.SetUpCategoryId = setupCategoryId; routeOpr.ProcessCategoryId = processCategoryId; routeOpr.QtyCategoryId = qtyCategoryId; routeOpr.RouteType = routeOprType; routeOpr.PropertyId = propertyId; routeOpr.RouteGroupId = routeGroupId; routeOpr.FormulaFactor1 = formulaFactor1; routeOpr.Formula = formula; routeOpr.WrkCtrIdCost = wrkCtrIdCost; routeOpr.RouteCode = RouteAll::Route; routeOpr.RouteRelation = route.RouteId; routeOpr.ItemCode = TableGroupAll::Table; routeOpr.ItemRelation = itemId; routeOpr.SiteId = siteId; if (route.validateWrite()) { route.write(); } if (routeOpr.validateWrite()) { routeOpr.write(); } } } ttsCommit; if (routeTable) { routeApprove = routeApprove::newRouteTable(routeTable); routeApprove.parmApprover(HcmWorker::userId2Worker(curUserId())); routeApprove.run(); } if (routeVersion) { routeVersionApprove = BOMRouteVersionApprove::newRouteVersion(routeVersion); routeVersionApprove.parmApproveRoute(true); routeVersionApprove.parmRemove(false); routeVersionApprove.parmApprover(HcmWorker::userId2Worker(curUserId())); routeVersionApprove.run(); routeVersionActivate = BOMRouteVersionActivate::newRouteVersion(routeVersion); routeVersionActivate.run(); } } |
Result:
Infolog
Please note the infolog which indicates that cost categories have not been assigned and no resource requirements have been specified. You can modify the script above to handle cost categories, in fact it would take a longer effort to accommodate for resource requirements of desired type (resource type, resource group, resource, capability, skill, course, certificate, title)
Route
Please note that I removed approval and activation from the existing Route Version prior to importing a new one to avoid overlap (based on dates/dimensions) validation error
Route details
Please note that you can create your Route structure once and then assign to a different Route Versions (for different items) multiple times. Or you may choose to create a new Route structure for each Route Version (for particular item) depending on your business requirements
Route
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 Routes. 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 Routes.
Author: Alex Anikiev, PhD, MCP
Tags: Dynamics ERP, Dynamics AX 2012, X++, Xpp, Data Import, Data Conversion, Data Migration, Routes
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.