In Ax 2012, there were 2 methods (that I have found until now) to upload BOM,
- Using DMF (Data Migration FrameWork) or DIXF (Data Import Export FrameWork)
- Using Customize Modules
there are pros and cons on each methods,
In this article we will coverage both of those methods, but please read it with cautious because it works on my development server that doesn’t mean works also on your server
Before we go any further, I’m going to make item PC Set which consists of RAM and Harddrive
Using DMF to Upload BOM
we’re going to use DMF, using this method wasn’t bad at all, it cut cost for customizing form, but be prepared of this DMF because we’re going need some tools
first tool is DataImportExportFrameWork.zip (you will need this if you’re using ax 2012, ax 2012 FP) or if you were using AX 2012 R2 you can find from CU7 installation
after you install the DataImportExportFrameWork, check the “Data Import/Export FrameWork Service”, “AOS Component” and “Client Component”
after you finished install these update, restart the AOS and reopen your AX client, you will find a new menu’s modules called “Data Import Export Framework” (see the picture below)
Click on Setup>”Data Import Export Framework Parameters”
If the installation success, it will shows like the picture above, see the Service Connection url, remember to open the “7000” port on your firewall, and create a folder on “C:\DMF”
click on Setup > “Source Data Formats”
I’m going to create new source name called CSVC with the parameters above, it means we will upload a CSV file with comma as delimited
Now we’re gonna continue to create a processing group, go to Common > Processing Group, and create new group name called “BOM Upload”
click “BOM Upload” and click Entities and input the line like picture below (remember to sequence the line)
click generate source file on “BOM Version”
choose the fields that your gonna upload, click “generate sample file” to see the sample, fill the file (this is my example file)
do the same thing with BOM (this is my example file)
after we finished to create the entities, we continued to “Get Staging Data”
then click “copy data to target” to copy from staging to tables, pick the jobs, and press ok
after upload success, user can continue to approved and activated the bom, the reason that Im not showing you auto activate and auto approve because we need user to re-verify the bom
USING MODULES UPLOAD
So, we already showed how to upload using BOM, but the steps was too scary for common users, except the user was already familiar with the bom forms
We do have alternatif, we gonna use a modules to upload BOM, I have upload the xpo here
I am using MeEdtGuidId as extended Data Type as Temporary Key, HpgTbTmpBOMUpload is a temporary table before inserted to BOMVersion, BOM and BOMTable (see the download files)
this is the source code:
static void ExcelImport()
{
//RF 20190723 - upload bom, req pak jaya
Dialog dialog;
DialogField dialogFileName;
Filename filename;
SysOperationProgress simpleProgress;
Container filterCriteria;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
COMVariantType type;
int row = 1, countrow;
#File
#avifiles
BOMTable _BomTable;
BOMVersion _BomVersion;
BOM _Bom;
HpgTbTmpBOMUpload _temp, _temp2, _temp3;
InventDim _InventDim;
InventTable _item;
str startNewLine;
int counterNewLine;
MeEdtGuidId GuidId;
HpgCsBOMUpload _app = new HpgCsBOMUpload();
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new Dialog("Importing Excel File");
dialogFileName = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
filterCriteria = ['*.xls'];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
filename = dialogFileName.value();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
_app.deleteTmpTable(GuidId);
throw error(strFmt("File cannot be opened."));
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
GuidId = Winapi::createGUID();
select forupdate _temp where _temp.GuidId == GuidId;
simpleProgress = new SysOperationProgress();
simpleProgress.setCaption("Retrieving from XLS");
simpleProgress.setAnimation(#AviUpdate);
do
{
row++;
simpleProgress.setText(strFmt("Row %", row));
_temp.clear();
_temp.GuidId = GuidId;
_temp.B1BOMId = cells.item(row, 1).value().bStr();
if(_temp.B1BOMId != startNewLine)
{
startNewLine = _temp.B1BOMId;
counterNewLine = 1;
}
_temp.B1BomName = cells.item(row, 2).value().bStr();
_temp.B1ItemGroupId = cells.item(row, 3).value().bStr();
_temp.B1InventSiteId = cells.item(row, 4).value().bStr();
_temp.B2StartDate = cells.item(row, 5).value().date();
_temp.B2EndDate = cells.item(row, 6).value().date();
_temp.B2FromQty = cells.item(row, 7).value().double();
_temp.B2ItemId = cells.item(row, 8).value().bStr();
_temp.B2ConfigId = cells.item(row, 9).value().bStr();
_temp.B2SizeId = cells.item(row, 10).value().bStr();
_temp.B2ColorId = cells.item(row, 11).value().bStr();
_temp.B2InventSiteId = cells.item(row, 12).value().bStr();
_temp.B3LineNum = counterNewLine;
_temp.B3BOMType = _app.getBomType(cells.item(row, 13).value().bStr());
_temp.B3BOMConsumpType = _app.getBOMConsumpType(cells.item(row, 14).value().bStr());
_temp.B3ItemId = cells.item(row, 15).value().bStr();
_temp.B3BOMQty = cells.item(row, 16).value().double();
_temp.B3BOMQtySerie = cells.item(row, 17).value().double();
_temp.B3InventSiteId = cells.item(row, 18).value().bStr();
_temp.B3WMSLocationId = cells.item(row, 19).value().bStr();
_temp.B3ConfigId = cells.item(row, 20).value().bStr();
_temp.B3SizeId = cells.item(row, 21).value().bStr();
_temp.B3ColorId = cells.item(row, 22).value().bStr();
_temp.B3UnitId = cells.item(row, 23).value().bStr();
_temp.B3InventLocationId = cells.item(row, 24).value().bStr();
_temp.insert();
type = cells.item(row+1, 1).value().variantType();
counterNewLine++;
} while (type != COMVariantType::VT_EMPTY);
application.quit();
countrow =0;
select _temp where _temp.GuidId == GuidId;
if(_temp)
{
//generate BOM Table
while select _temp
group by _temp.B1BOMId, _temp.B1BomName, _temp.B1InventSiteId,
_temp.B1ItemGroupId
where _temp.GuidId == GuidId
{
select forUpdate _BomTable where _BomTable.BOMId==_temp.B1BOMId;
if(!_BomTable)
{
_BomTable.clear();
_BomTable.BOMId = _temp.B1BOMId;
_BomTable.Name = _temp.B1BomName;
_BomTable.SiteId = _temp.B1InventSiteId;
_BomTable.ItemGroupId = _temp.B1ItemGroupId;
_BomTable.insert();
}
if(_BomTable.Approved)
{
error(strFmt("BOM tidak bisa diupload karena sudah approved, kode bom %1", _temp.B1BOMId));
}
else
{
delete_from _BomVersion where _BomVersion.BOMId == _temp.B1BOMId;
delete_from _BOM where _BOM.BOMId == _temp.B1BOMId;
//bom version
select forupdate _BomVersion where _BomVersion.BOMId == _temp.B1BOMId;
while select _temp2
group by
_temp2.B2StartDate, _temp2.B2EndDate, _temp2.B2FromQty,
_temp2.B2ItemId, _temp2.B2ConfigId, _temp2.B2SizeId,
_temp2.B2ColorId, _temp2.B2InventSiteId
where _temp2.GuidId == GuidId
&& _temp2.B1BOMId == _temp.B1BOMId
{
_BomVersion.clear();
_BomVersion.BOMId = _temp.B1BOMId;
_BomVersion.Name = _temp.B1BomName;
_BomVersion.FromDate = _temp2.B2StartDate;
_BomVersion.ToDate = _temp2.B2EndDate;
_BomVersion.FromQty = _temp2.B2FromQty;
_BomVersion.ItemId = _temp2.B2ItemId;
_Item = InventTable::find(_BomVersion.ItemId);
if(_Item)
{
if(_Item.configActive())
{
_inventDim.configId = _temp2.B2ConfigId;
}
if(_Item.sizeActive())
{
_inventDim.InventSizeId = _temp2.B2SizeId;
}
if(_Item.colorActive())
{
_inventDim.InventColorId = _temp2.B2ColorId;
}
_inventDim.InventSiteId = _temp2.B2InventSiteId;
_inventDim = InventDim::findOrCreate(_inventDim);
_BomVersion.InventDimId = _inventDim.inventDimId;
}
_BomVersion.insert();
}
//bom
select forUpdate _BOM where _Bom.BOMId==_temp.B1BOMId;
while select _temp3 where _temp3.B1BOMId==_temp.B1BOMId
&& _temp3.GuidId == GuidId
{
countrow++;
simpleProgress.setText(strFmt("Insert %1 of %2", countrow, row));
simpleProgress.setCount(countrow);
_Bom.clear();
_Bom.BOMId = _temp3.B1BOMId;
_Bom.BOMConsump = _temp3.B3BOMConsumpType;
_Bom.BOMQty = _temp3.B3BOMQty;
_Bom.BOMQtySerie = _temp3.B3BOMQtySerie;
_Bom.BOMType = _temp3.B3BOMType;
_Bom.ItemId = _temp3.B3ItemId;
_Item = InventTable::find(_Bom.ItemId);
if(_Item)
{
if(_Item.configActive())
{
_inventDim.configId = _temp3.B3ConfigId;
}
if(_Item.sizeActive())
{
_inventDim.InventSizeId = _temp3.B3SizeId;
}
if(_Item.colorActive())
{
_inventDim.InventColorId = _temp3.B3ColorId;
}
_inventDim.InventSiteId = _temp3.B3InventSiteId;
_InventDim.wMSLocationId = _temp3.B3WMSLocationId;
_InventDim.InventLocationId = _temp3.B3InventLocationId;
_inventDim = InventDim::findOrCreate(_inventDim);
_Bom.InventDimId = _inventDim.inventDimId;
}
_Bom.LineNum = _temp3.B3LineNum;
_Bom.UnitId = _temp3.B3UnitId;
_Bom.insert();
}
}
}
}
_app.deleteTmpTable(GuidId);
info(strFmt("upload done for %1 rows", row-1));
}How to use this modules?
Goto Inventory and Warehouse Management > Common > Bill of Material
please see my xls files here
so, that’s it, long article, I hope this article is helpful to all