What Is BOM ?
You can create different versions of an existing bill of materials (BOM). You can then attach the BOM to one or more items.
queryRun = new QueryRun(query);
while(queryRun.next())
{
//info(strFmt("main line - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOMVersion.InventDimId = ObjInventDim.inventDimId;
}
}
qbds.clearRanges();
//BOM version Configuration,Site,style,colour,size end
You can create different versions of an existing bill of materials (BOM). You can then attach the BOM to one or more items.
Create a BOM
-
Click Inventory management > Common > Bills of materials.
-
Press CTRL+N to create a new BOM.
-
Enter information to identify the BOM.
-
Click Lines to open the BOM line form.
-
Select the item number, and enter the quantity to create additional BOM lines for any component items that make up the BOM.
-
Close the form.
-
Click Approve to open the Approve bill of materials form.
- Select an employee name, and then click OK.
Create a BOM version
-
To create another version of the new BOM, press CTRL+N in the Versions grid to create a new line.
-
Select the relevant item number, and enter dates in the From date and To date fields to control the validity period of the BOM version. Use the From qty. field to set a minimum item quantity for using the BOM version.
-
Click Approve to approve the BOM version.
- Select the Active check box to make the BOM version active.
Here I have create code for import BOM through code. for import if you have single BOMTable line, Single BOM version line, and Multiple BOM lines then you can import it or else through code changes you can also change import code how to import and what format you need to import BOM.
Import Code:
#AviFiles
#File
COM excelCOM;
SysOperationProgress simpleProgress;
int lastRow;
FileIOPermission permission;
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelWorkSheet workSheetInventTableModule;
SysExcelCells cellsInventTableModule;
SysExcelCells cells;
COMVariant file;
COMVariantType type,type1,type2;
COMVariantType typeModule;
int row,countinsert,row1;
FileName fileName;
Container filterCriteria;
FileIoPermission perm;
dialogField dialogFilename;
dialog dialog;
NoYes NoYes;
boolean VefiryFlag,ImportData;
str 60 Vefiry;
int ii;
#File
COM excelCOM;
SysOperationProgress simpleProgress;
int lastRow;
FileIOPermission permission;
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelWorkSheet workSheetInventTableModule;
SysExcelCells cellsInventTableModule;
SysExcelCells cells;
COMVariant file;
COMVariantType type,type1,type2;
COMVariantType typeModule;
int row,countinsert,row1;
FileName fileName;
Container filterCriteria;
FileIoPermission perm;
dialogField dialogFilename;
dialog dialog;
NoYes NoYes;
boolean VefiryFlag,ImportData;
str 60 Vefiry;
int ii;
dev_ApplBomTemplate Objdev_ApplBomTemplate;
BOM ObjBOM,ObjBOM1,ObjBOM2;
BOMTable ObjBOMTable,ObjBOMTable1;
InventDim ObjInventDim;
BOMVersion ObjBOMVersion,ObjBOMVersion1;
InventSite ObjInventSite;
dev_ImportedCV Objdev_ImportedCV;
BOMType BOMType;
BOM ObjBOM,ObjBOM1,ObjBOM2;
BOMTable ObjBOMTable,ObjBOMTable1;
InventDim ObjInventDim;
BOMVersion ObjBOMVersion,ObjBOMVersion1;
InventSite ObjInventSite;
dev_ImportedCV Objdev_ImportedCV;
BOMType BOMType;
Query query;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr,qbr1,qbr2,qbr3,qbr4,qbr5,qbr6;
QueryRun queryRun;
QueryBuildDataSource qbds;
QueryBuildRange qbr,qbr1,qbr2,qbr3,qbr4,qbr5,qbr6;
str A,B,C,D,E,G,H,I,J,K,L,P,R,S,T,U,V,W,X,AA,AB,AC,AD,AE;
real F,Q,O,Y,Z;
date M,N;
boolean FlagBOMTable,FlagBOMVersion,FlagBOM1Line,FlagBOMOtherLine;
str WorkerId;
super();
real F,Q,O,Y,Z;
date M,N;
boolean FlagBOMTable,FlagBOMVersion,FlagBOM1Line,FlagBOMOtherLine;
str WorkerId;
super();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
file = new COMVariant();
workbooks = application.workbooks();
file = new COMVariant();
//Dialog Start
dialog = new dialog();
dialog.caption("select a file for BOM import");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"File Name");
filterCriteria = ['xlsx','*.xlsx','xls','*.xls'];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if(dialog.closedOk())
{
filename = dialogFileName.value();
}
//Dialog end
dialog = new dialog();
dialog.caption("select a file for BOM import");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen),"File Name");
filterCriteria = ['xlsx','*.xlsx','xls','*.xls'];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if(dialog.closedOk())
{
filename = dialogFileName.value();
}
//Dialog end
//Permission start
permission = new fileIOpermission(filename,#io_read);
permission.assert();
//Permission end
//Workbook open start
try
{
workbooks.open(fileName);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
//Workbook open end
permission = new fileIOpermission(filename,#io_read);
permission.assert();
//Permission end
//Workbook open start
try
{
workbooks.open(fileName);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
//Workbook open end
//worksheet start
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
if (worksheet)
{
cells = worksheet.cells();
}
else
{
throw warning('File cannot be opened.');
}
//worksheet end
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
if (worksheet)
{
cells = worksheet.cells();
}
else
{
throw warning('File cannot be opened.');
}
//worksheet end
//Select last row of excel sheet start
excelCOM = cells.comObject();
excelCOM = excelCOM.SpecialCells(11);
lastRow = excelCOM.Row();
//Select last row of excel sheet end
excelCOM = cells.comObject();
excelCOM = excelCOM.SpecialCells(11);
lastRow = excelCOM.Row();
//Select last row of excel sheet end
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'Reading data from ExcelSheet...', lastRow);
VefiryFlag = true;
ImportData = true;
//main try catch start
ttsBegin;
try
{
//Varify Excel sheet columns Start
if(VefiryFlag)
{
row++;
//ttsbegin;
//Progessbar for verifying start
simpleProgress.incCount();
simpleprogress.setText("Varifying Excel sheet");
simpleprogress.update(true);
//Progessbar for verifying end
VefiryFlag = true;
ImportData = true;
//main try catch start
ttsBegin;
try
{
//Varify Excel sheet columns Start
if(VefiryFlag)
{
row++;
//ttsbegin;
//Progessbar for verifying start
simpleProgress.incCount();
simpleprogress.setText("Varifying Excel sheet");
simpleprogress.update(true);
//Progessbar for verifying end
//fatching fields start
if (row == 1)
{
Vefiry = any2str(cells.item(row, ii+1).value().bStr());
while select * from Objdev_ApplBomTemplate
order by Objdev_ApplBomTemplate.SerialNo asc
{
if(Objdev_ApplBomTemplate.Name == Vefiry)
{
ii++;
Vefiry = any2str(cells.item(row, ii+1).value().bStr());
}
else
{
warning(strFmt("Excel Not Match from %1th row",ii));
VefiryFlag = false;
application.quit();
break;
}
}
}
//fatching fields end
//ttscommit;
}
//Varify Excel sheet columns end
if (row == 1)
{
Vefiry = any2str(cells.item(row, ii+1).value().bStr());
while select * from Objdev_ApplBomTemplate
order by Objdev_ApplBomTemplate.SerialNo asc
{
if(Objdev_ApplBomTemplate.Name == Vefiry)
{
ii++;
Vefiry = any2str(cells.item(row, ii+1).value().bStr());
}
else
{
warning(strFmt("Excel Not Match from %1th row",ii));
VefiryFlag = false;
application.quit();
break;
}
}
}
//fatching fields end
//ttscommit;
}
//Varify Excel sheet columns end
//Importing Item Start
if(ImportData == true && VefiryFlag == true)
{
row = 1;
//ttsbegin;
//do while start
do
{
row++;
if(ImportData == true && VefiryFlag == true)
{
row = 1;
//ttsbegin;
//do while start
do
{
row++;
//Clear objects start
A='';B='';C='';D='';E='';G='';H='';I='';J='';K='';L='';P='';R='';S='';T='';U='';V='';W='';X='';AA='';AB='';AC='';AD='';AE='';
F=0;Q=0;O=0;Y=0;Z=0;
M=datenull(); N=datenull();
Objdev_ApplBomTemplate.clear();
ObjBOM.clear();
ObjBOMTable.clear();
ObjInventDim.clear();
ObjBOMVersion.clear();
ObjInventSite.clear();
//qbds.clearRanges();
//Clear objects end
A='';B='';C='';D='';E='';G='';H='';I='';J='';K='';L='';P='';R='';S='';T='';U='';V='';W='';X='';AA='';AB='';AC='';AD='';AE='';
F=0;Q=0;O=0;Y=0;Z=0;
M=datenull(); N=datenull();
Objdev_ApplBomTemplate.clear();
ObjBOM.clear();
ObjBOMTable.clear();
ObjInventDim.clear();
ObjBOMVersion.clear();
ObjInventSite.clear();
//qbds.clearRanges();
//Clear objects end
//progressbar for importing Item start
simpleProgress.incCount();
simpleprogress.setText(strfmt("Generated BOM - %1 remaining %2", row-1,lastRow-row));
simpleprogress.update(true);
//progressbar for importing Item end
simpleProgress.incCount();
simpleprogress.setText(strfmt("Generated BOM - %1 remaining %2", row-1,lastRow-row));
simpleprogress.update(true);
//progressbar for importing Item end
//importing Item by rows start
if (row > 1)
{
//Fatching data from Excel start
A = cells.item(row, 1).value().bStr();//Sr No.
B = any2str(cells.item(row, 2).value().bStr());//BOM Identification
C = any2str(cells.item(row, 3).value().bStr());//BOM Name
D = any2str(cells.item(row, 4).value().bStr());//Site
E = any2str(cells.item(row, 5).value().bStr());//Item Group
F = cells.item(row, 6).value().double();//Approved by
G = any2str(cells.item(row, 7).value().bStr());//Approved (Yes/No)
H = any2str(cells.item(row, 8).value().bStr());//BOM Item Number
AE = any2str(cells.item(row, 9).value().bStr());//BOM Item Configuration
I = any2str(cells.item(row, 10).value().bStr());//BOM Item Size
J = any2str(cells.item(row, 11).value().bStr());//BOM Item Color
K = any2str(cells.item(row, 12).value().bStr());//BOM Item Style
L = any2str(cells.item(row, 13).value().bStr());//BOM Item Site
M = cells.item(row, 14).value().date();//From Date
N = cells.item(row, 15).value().date();//To Date
O = cells.item(row, 16).value().double();//From Qty.
P = any2str(cells.item(row, 17).value().bStr());//Active (Yes/No)
Q = cells.item(row, 18).value().double();//Approved by
R = any2str(cells.item(row, 19).value().bStr());//Approved (Yes/No)
S = any2str(cells.item(row, 20).value().bStr());//Item Number
T = any2str(cells.item(row, 21).value().bStr());//Configuration
U = any2str(cells.item(row, 22).value().bStr());//Size
V = any2str(cells.item(row, 23).value().bStr());//Color
W = any2str(cells.item(row, 24).value().bStr());//Style
X = any2str(cells.item(row, 25).value().bStr());//Warehouse
Y = cells.item(row, 26).value().double();//Per Series
Z = cells.item(row, 27).value().double();//Quantity
AA = any2str(cells.item(row, 28).value().bStr());//Unit
AB = any2str(cells.item(row, 29).value().bStr());//Line Type (Item / Pegged supply)
AC = any2str(cells.item(row, 30).value().bStr());//Sub-BOM formula
AD = any2str(cells.item(row, 31).value().bStr());//Configuration Group
//Fatching data from Excel end
if (row > 1)
{
//Fatching data from Excel start
A = cells.item(row, 1).value().bStr();//Sr No.
B = any2str(cells.item(row, 2).value().bStr());//BOM Identification
C = any2str(cells.item(row, 3).value().bStr());//BOM Name
D = any2str(cells.item(row, 4).value().bStr());//Site
E = any2str(cells.item(row, 5).value().bStr());//Item Group
F = cells.item(row, 6).value().double();//Approved by
G = any2str(cells.item(row, 7).value().bStr());//Approved (Yes/No)
H = any2str(cells.item(row, 8).value().bStr());//BOM Item Number
AE = any2str(cells.item(row, 9).value().bStr());//BOM Item Configuration
I = any2str(cells.item(row, 10).value().bStr());//BOM Item Size
J = any2str(cells.item(row, 11).value().bStr());//BOM Item Color
K = any2str(cells.item(row, 12).value().bStr());//BOM Item Style
L = any2str(cells.item(row, 13).value().bStr());//BOM Item Site
M = cells.item(row, 14).value().date();//From Date
N = cells.item(row, 15).value().date();//To Date
O = cells.item(row, 16).value().double();//From Qty.
P = any2str(cells.item(row, 17).value().bStr());//Active (Yes/No)
Q = cells.item(row, 18).value().double();//Approved by
R = any2str(cells.item(row, 19).value().bStr());//Approved (Yes/No)
S = any2str(cells.item(row, 20).value().bStr());//Item Number
T = any2str(cells.item(row, 21).value().bStr());//Configuration
U = any2str(cells.item(row, 22).value().bStr());//Size
V = any2str(cells.item(row, 23).value().bStr());//Color
W = any2str(cells.item(row, 24).value().bStr());//Style
X = any2str(cells.item(row, 25).value().bStr());//Warehouse
Y = cells.item(row, 26).value().double();//Per Series
Z = cells.item(row, 27).value().double();//Quantity
AA = any2str(cells.item(row, 28).value().bStr());//Unit
AB = any2str(cells.item(row, 29).value().bStr());//Line Type (Item / Pegged supply)
AC = any2str(cells.item(row, 30).value().bStr());//Sub-BOM formula
AD = any2str(cells.item(row, 31).value().bStr());//Configuration Group
//Fatching data from Excel end
//Item Import start
try
{
//BOM Table insert start
//Code for import start
ObjBOMTable.clear();
ObjBOMTable.initValue();
//Bom identification start
if(B!=''){ObjBOMTable.BOMId = B;}
//Bom identification end
try
{
//BOM Table insert start
//Code for import start
ObjBOMTable.clear();
ObjBOMTable.initValue();
//Bom identification start
if(B!=''){ObjBOMTable.BOMId = B;}
//Bom identification end
//BOM name start
if(C!=''){ObjBOMTable.Name = C;}
//BOM name end
if(C!=''){ObjBOMTable.Name = C;}
//BOM name end
//BOM Site start
if(D!='')
{
if(InventSite::find(D).SiteId == D){ObjBOMTable.SiteId = InventSite::find(D).SiteId;}
}
//BOM Site end
if(D!='')
{
if(InventSite::find(D).SiteId == D){ObjBOMTable.SiteId = InventSite::find(D).SiteId;}
}
//BOM Site end
//BOM Item group start
if(E!='')
{
if(InventItemGroup::find(E).ItemGroupId == E){ObjBOMTable.ItemGroupId = InventItemGroup::find(E).ItemGroupId;}
}
//BOM Item group end
if(E!='')
{
if(InventItemGroup::find(E).ItemGroupId == E){ObjBOMTable.ItemGroupId = InventItemGroup::find(E).ItemGroupId;}
}
//BOM Item group end
//BOM Approver start
if(F!=0)
{
if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).PersonnelNumber)
{
ObjBOMTable.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).RecId;
ObjBOMTable.Approved = NoYes::Yes;
}
else{ObjBOMTable.Approved = NoYes::No;}
}
else{ObjBOMTable.Approved = NoYes::No;}
//BOM Approver end
if(F!=0)
{
if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).PersonnelNumber)
{
ObjBOMTable.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(F)),6, "0")).RecId;
ObjBOMTable.Approved = NoYes::Yes;
}
else{ObjBOMTable.Approved = NoYes::No;}
}
else{ObjBOMTable.Approved = NoYes::No;}
//BOM Approver end
if(ObjBOMTable.validateWrite())
{
ObjBOMTable.insert();
}
else
{
FlagBOMTable = true;
//info(strFmt(" from bom table %1",FlagBOMTable));
}
//BOM Table insert end
{
ObjBOMTable.insert();
}
else
{
FlagBOMTable = true;
//info(strFmt(" from bom table %1",FlagBOMTable));
}
//BOM Table insert end
//BOM version insert start
ObjBOMVersion.clear();
ObjBOMVersion.initFromBOMTable(ObjBOMTable);
ObjBOMVersion.BOMId = ObjBOMTable.BOMId;
ObjBOMVersion.initValue();
//BOM Item number start
if(H!='')
{
if(InventTable::find(H).ItemId)
{
ObjBOMVersion.ItemId = H;
}
}
ObjBOMVersion.clear();
ObjBOMVersion.initFromBOMTable(ObjBOMTable);
ObjBOMVersion.BOMId = ObjBOMTable.BOMId;
ObjBOMVersion.initValue();
//BOM Item number start
if(H!='')
{
if(InventTable::find(H).ItemId)
{
ObjBOMVersion.ItemId = H;
}
}
//BOM Item number end
//BOM from date start
if(M!=systemDateGet())
{ObjBOMVersion.FromDate = M;}
//BOM from date end
if(M!=systemDateGet())
{ObjBOMVersion.FromDate = M;}
//BOM from date end
//BOM Todate start
if(N!=systemDateGet())
{ObjBOMVersion.ToDate = N;}
//BOM Todate end
if(N!=systemDateGet())
{ObjBOMVersion.ToDate = N;}
//BOM Todate end
//BOM From Qty start
if(O!=0)
{
if(O != 1){ObjBOMVersion.FromQty = O;}
}
//BOM From Qty end
if(O!=0)
{
if(O != 1){ObjBOMVersion.FromQty = O;}
}
//BOM From Qty end
//BOM version Configuration,Site,style,colour,size start
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
if(L != ''){qbr.value(L);}else{qbr.value(sysquery::valueEmptyString());}
if(L != ''){qbr.value(L);}else{qbr.value(sysquery::valueEmptyString());}
qbr1 = qbds.addRange(fieldNum(InventDim,configId));
if(AE!=''){qbr1.value(AE);}else{qbr1.value(sysquery::valueEmptyString());}
if(AE!=''){qbr1.value(AE);}else{qbr1.value(sysquery::valueEmptyString());}
qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
if(J != ''){qbr2.value(J);}else{qbr2.value(sysquery::valueEmptyString());}
if(J != ''){qbr2.value(J);}else{qbr2.value(sysquery::valueEmptyString());}
qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
qbr3.value(sysquery::valueEmptyString());
qbr3.value(sysquery::valueEmptyString());
qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
qbr4.value(sysquery::valueEmptyString());
qbr4.value(sysquery::valueEmptyString());
qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
if(I!=''){qbr5.value(queryValue(strFmt("%1",I)));}else{qbr5.value(sysquery::valueEmptyString());}
if(I!=''){qbr5.value(queryValue(strFmt("%1",I)));}else{qbr5.value(sysquery::valueEmptyString());}
qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
if(K!=''){qbr6.value(K);}else{qbr6.value(sysquery::valueEmptyString());}
if(K!=''){qbr6.value(K);}else{qbr6.value(sysquery::valueEmptyString());}
queryRun = new QueryRun(query);
while(queryRun.next())
{
//info(strFmt("main line - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOMVersion.InventDimId = ObjInventDim.inventDimId;
}
}
qbds.clearRanges();
//BOM version Configuration,Site,style,colour,size end
//BOM version approved start
if(Q!=0)
{
if(ObjBOMTable.Approved == true)
{
if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).PersonnelNumber)
{
ObjBOMVersion.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).RecId;
ObjBOMVersion.Approved = true;
//BOM version active start
if(P!='')
{ObjBOMVersion.Active = str2enum(NoYes,P);}
//BOM version active end
if(Q!=0)
{
if(ObjBOMTable.Approved == true)
{
if(HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).PersonnelNumber)
{
ObjBOMVersion.Approver = HcmWorker::findByPersonnelNumber(strRFix(int2str(real2int(Q)),6, "0")).RecId;
ObjBOMVersion.Approved = true;
//BOM version active start
if(P!='')
{ObjBOMVersion.Active = str2enum(NoYes,P);}
//BOM version active end
//if(ObjBOMVersion.Approver != 0 && ObjBOMVersion.Approved == true)
//{
//update_recordSet ObjBOMVersion1
//setting Approver = ObjBOMVersion.Approver,
//Approved = ObjBOMVersion.Approved,
//Active = ObjBOMVersion.Active
//where ObjBOMVersion1.BOMId == ObjBOMVersion.BOMId;
//}
}
else{ObjBOMVersion.Approved = false;}
}
}
else{ObjBOMVersion.Approved = false;}
//BOM version approved end
//{
//update_recordSet ObjBOMVersion1
//setting Approver = ObjBOMVersion.Approver,
//Approved = ObjBOMVersion.Approved,
//Active = ObjBOMVersion.Active
//where ObjBOMVersion1.BOMId == ObjBOMVersion.BOMId;
//}
}
else{ObjBOMVersion.Approved = false;}
}
}
else{ObjBOMVersion.Approved = false;}
//BOM version approved end
if(ObjBOMVersion.validateWrite())
{
ObjBOMVersion.insert();
}
else
{
FlagBOMVersion = true;
//info(strFmt(" from bom version %1",FlagBOMVersion));
}
//BOM version insert end
{
ObjBOMVersion.insert();
}
else
{
FlagBOMVersion = true;
//info(strFmt(" from bom version %1",FlagBOMVersion));
}
//BOM version insert end
//BOM insert start
ObjBOM.clear();
ObjBOM.initValue();
ObjBOM.BOMId = ObjBOMTable.BOMId;
//BOM item start
if(S!='')
{
if(InventTable::find(S).ItemId)
{
ObjBOM.ItemId = InventTable::find(S).ItemId;
}
}
//BOM item end
ObjBOM.clear();
ObjBOM.initValue();
ObjBOM.BOMId = ObjBOMTable.BOMId;
//BOM item start
if(S!='')
{
if(InventTable::find(S).ItemId)
{
ObjBOM.ItemId = InventTable::find(S).ItemId;
}
}
//BOM item end
//BOM per series start
if(Y !=0)
{
if(Y!=1)
{
ObjBOM.BOMQtySerie = Y;
}
}
//BOM per series end
if(Y !=0)
{
if(Y!=1)
{
ObjBOM.BOMQtySerie = Y;
}
}
//BOM per series end
//BOM Quamtity start
if(Z != 0)
{
ObjBOM.BOMQty = Z;
}
//BOM Quamtity end
if(Z != 0)
{
ObjBOM.BOMQty = Z;
}
//BOM Quamtity end
//BOM Unit start
if(AA !='')
{
if(UnitOfMeasure::findBySymbol(AA).Symbol)
{
ObjBOM.UnitId = AA;
}
}
//BOM Unit end
if(AA !='')
{
if(UnitOfMeasure::findBySymbol(AA).Symbol)
{
ObjBOM.UnitId = AA;
}
}
//BOM Unit end
//BOM Type start
if(AB!='')
{
if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
{
ObjBOM.BOMType = str2enum(BOMType,AB);
}
}
//BOM Type end
if(AB!='')
{
if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
{
ObjBOM.BOMType = str2enum(BOMType,AB);
}
}
//BOM Type end
//BOM Sub-Formula start
if(AC!='')
{
if(ObjBOMTable.BOMId != AC)
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
else
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
}
//BOM Sub-Formula end
if(AC!='')
{
if(ObjBOMTable.BOMId != AC)
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
else
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
}
//BOM Sub-Formula end
//BOM ConfigGroupId start
if(AD != '')
{
if(ConfigGroup::find(AD).ConfigGroupId)
{
ObjBOM.ConfigGroupId = AD;
}
}
//BOM ConfigGroupId end
if(AD != '')
{
if(ConfigGroup::find(AD).ConfigGroupId)
{
ObjBOM.ConfigGroupId = AD;
}
}
//BOM ConfigGroupId end
//BOM Configuration,style,colour,size,warehouse start
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}
if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}
qbr1 = qbds.addRange(fieldNum(InventDim,configId));
if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}
if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}
qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
if(V != ''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}
if(V != ''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}
qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}
if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}
qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
qbr4.value(sysquery::valueEmptyString());
qbr4.value(sysquery::valueEmptyString());
qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
if(U!=''){qbr5.value(queryValue(strFmt("%1",U)));}else{qbr5.value(sysquery::valueEmptyString());}
if(U!=''){qbr5.value(queryValue(strFmt("%1",U)));}else{qbr5.value(sysquery::valueEmptyString());}
qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}
if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}
queryRun = new QueryRun(query);
while(queryRun.next())
{
//info(strFmt("Main sublie - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOM.InventDimId = ObjInventDim.inventDimId;
}
}
qbds.clearRanges();
//BOM Configuration,style,colour,size,warehouse end
while(queryRun.next())
{
//info(strFmt("Main sublie - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOM.InventDimId = ObjInventDim.inventDimId;
}
}
qbds.clearRanges();
//BOM Configuration,style,colour,size,warehouse end
if(ObjBOM.validateWrite())
{
ObjBOM.insert();
}
else
{
FlagBOM1Line = true;
//info(strFmt(" from bom 1 line %1",FlagBOM1Line));
}
//BOM insert end
{
ObjBOM.insert();
}
else
{
FlagBOM1Line = true;
//info(strFmt(" from bom 1 line %1",FlagBOM1Line));
}
//BOM insert end
//BOM Line Do While start
//S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
row1 = row;
do
{
S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
row1++;
//S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
row1 = row;
do
{
S='';T='';U ='';V='';W ='';X='';Y=0;Z=0;AA='';AB='';AC='';AD='';
row1++;
//Getting eub lines start
S = any2str(cells.item(row1, 20).value().bStr());//Item Number
T = any2str(cells.item(row1, 21).value().bStr());//Configuration
U = any2str(cells.item(row1, 22).value().bStr());//Size
V = any2str(cells.item(row1, 23).value().bStr());//Color
W = any2str(cells.item(row1, 24).value().bStr());//Style
X = any2str(cells.item(row1, 25).value().bStr());//Warehouse
Y = cells.item(row1, 26).value().double();//Per Series
Z = cells.item(row1, 27).value().double();//Quantity
AA = any2str(cells.item(row1, 28).value().bStr());//Unit
AB = any2str(cells.item(row1, 29).value().bStr());//Line Type (Item / Pegged supply)
AC = any2str(cells.item(row1, 30).value().bStr());//Sub-BOM formula
AD = any2str(cells.item(row1, 31).value().bStr());//Configuration Group
//Getting eub lines end
S = any2str(cells.item(row1, 20).value().bStr());//Item Number
T = any2str(cells.item(row1, 21).value().bStr());//Configuration
U = any2str(cells.item(row1, 22).value().bStr());//Size
V = any2str(cells.item(row1, 23).value().bStr());//Color
W = any2str(cells.item(row1, 24).value().bStr());//Style
X = any2str(cells.item(row1, 25).value().bStr());//Warehouse
Y = cells.item(row1, 26).value().double();//Per Series
Z = cells.item(row1, 27).value().double();//Quantity
AA = any2str(cells.item(row1, 28).value().bStr());//Unit
AB = any2str(cells.item(row1, 29).value().bStr());//Line Type (Item / Pegged supply)
AC = any2str(cells.item(row1, 30).value().bStr());//Sub-BOM formula
AD = any2str(cells.item(row1, 31).value().bStr());//Configuration Group
//Getting eub lines end
//importing Item by rows start
if (row1 > row)
{
//BOM Sub line insert start
//BOM insert start
ObjBOM.clear();
ObjBOM.initValue();
ObjBOM.BOMId = ObjBOMTable.BOMId;
//BOM item start
if(S!='')
{
if(InventTable::find(S).ItemId)
{
ObjBOM.ItemId = InventTable::find(S).ItemId;
}
}
//BOM item end
if (row1 > row)
{
//BOM Sub line insert start
//BOM insert start
ObjBOM.clear();
ObjBOM.initValue();
ObjBOM.BOMId = ObjBOMTable.BOMId;
//BOM item start
if(S!='')
{
if(InventTable::find(S).ItemId)
{
ObjBOM.ItemId = InventTable::find(S).ItemId;
}
}
//BOM item end
//BOM per series start
if(Y !=0)
{
if(Y!=1)
{
ObjBOM.BOMQtySerie = Y;
}
}
//BOM per series end
if(Y !=0)
{
if(Y!=1)
{
ObjBOM.BOMQtySerie = Y;
}
}
//BOM per series end
//BOM Quamtity start
if(Z != 0)
{
ObjBOM.BOMQty = Z;
}
//BOM Quamtity end
if(Z != 0)
{
ObjBOM.BOMQty = Z;
}
//BOM Quamtity end
//BOM Unit start
if(AA !='')
{
if(UnitOfMeasure::findBySymbol(AA).Symbol)
{
ObjBOM.UnitId = AA;
}
}
//BOM Unit end
if(AA !='')
{
if(UnitOfMeasure::findBySymbol(AA).Symbol)
{
ObjBOM.UnitId = AA;
}
}
//BOM Unit end
//BOM Type start
if(AB!='')
{
if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
{
ObjBOM.BOMType = str2enum(BOMType,AB);
}
}
//BOM Type end
if(AB!='')
{
if(AB == enum2str(BOMType::Item) || AB == enum2str(BOMType::PeggedSupply))
{
ObjBOM.BOMType = str2enum(BOMType,AB);
}
}
//BOM Type end
//BOM Sub-Formula start
if(AC!='')
{
if(ObjBOMTable.BOMId != AC)
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
else
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
}
//BOM Sub-Formula end
if(AC!='')
{
if(ObjBOMTable.BOMId != AC)
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
else
{
ObjBOM.ItemBOMId = ObjBOMTable.BOMId;
}
}
//BOM Sub-Formula end
//BOM ConfigGroupId start
if(AD != '')
{
if(ConfigGroup::find(AD).ConfigGroupId)
{
ObjBOM.ConfigGroupId = AD;
}
}
//BOM ConfigGroupId end
if(AD != '')
{
if(ConfigGroup::find(AD).ConfigGroupId)
{
ObjBOM.ConfigGroupId = AD;
}
}
//BOM ConfigGroupId end
//BOM Configuration,style,colour,size,warehouse start
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
query = new Query();
qbds = query.addDataSource( tableNum(InventDim));
qbr = qbds.addRange(fieldNum(InventDim,InventSiteId));
if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}
if(D!=''){qbr.value(D);}else{qbr.value(sysquery::valueEmptyString());}
qbr1 = qbds.addRange(fieldNum(InventDim,configId));
if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}
if(T!=''){qbr1.value(T);}else{qbr1.value(sysquery::valueEmptyString());}
qbr2 = qbds.addRange(fieldNum(InventDim,InventColorId));
if(V!=''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}
if(V!=''){qbr2.value(V);}else{qbr2.value(sysquery::valueEmptyString());}
qbr3 = qbds.addRange(fieldNum(InventDim,InventLocationId));
if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}
if(X!=''){qbr3.value(X);}else{qbr3.value(sysquery::valueEmptyString());}
qbr4 = qbds.addRange(fieldNum(InventDim,inventSerialId));
qbr4.value(sysquery::valueEmptyString());
qbr4.value(sysquery::valueEmptyString());
qbr5 = qbds.addRange(fieldNum(InventDim,InventSizeId));
if(U!=''){qbr5.value(U);}else{qbr5.value(sysquery::valueEmptyString());}
if(U!=''){qbr5.value(U);}else{qbr5.value(sysquery::valueEmptyString());}
qbr6 = qbds.addRange(fieldNum(InventDim,InventStyleId));
if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}
if(W!=''){qbr6.value(W);}else{qbr6.value(sysquery::valueEmptyString());}
queryRun = new QueryRun(query);
while(queryRun.next())
{
//info(strFmt("sublie - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOM.InventDimId = ObjInventDim.inventDimId;
}
}
//BOM Configuration,style,colour,size,warehouse end
while(queryRun.next())
{
//info(strFmt("sublie - %1",query.dataSourceNo(1).toString()));
ObjInventDim = queryRun.get(tableNum(inventDim));
if(ObjInventDim.inventDimId)
{
ObjBOM.InventDimId = ObjInventDim.inventDimId;
}
}
//BOM Configuration,style,colour,size,warehouse end
if(ObjBOM.validateWrite())
{
ObjBOM.insert();
}
else
{
FlagBOMOtherLine = true;
//info(strFmt(" from bom Other line %1",FlagBOMOtherLine));
}
//BOM insert end
//BOM sub line insert end
}
{
ObjBOM.insert();
}
else
{
FlagBOMOtherLine = true;
//info(strFmt(" from bom Other line %1",FlagBOMOtherLine));
}
//BOM insert end
//BOM sub line insert end
}
type1 = cells.item(row1+1, 2).value().variantType();
type2 = cells.item(row1+1, 20).value().variantType();
//importing Item by rows end
}while (type1 == COMVariantType::VT_EMPTY && type2 != COMVariantType::VT_EMPTY);
//BOM Line Do While end
row = row1;
//Code for import end
type2 = cells.item(row1+1, 20).value().variantType();
//importing Item by rows end
}while (type1 == COMVariantType::VT_EMPTY && type2 != COMVariantType::VT_EMPTY);
//BOM Line Do While end
row = row1;
//Code for import end
}
catch(Exception::Error)
{
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
return;
}
//Item Import End
catch(Exception::Error)
{
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
return;
}
//Item Import End
if(FlagBOMTable == true || FlagBOMVersion == true || FlagBOM1Line == true || FlagBOMOtherLine == true)
{
setPrefix("BOM not imported successfully.");
info(strFmt("%1",ObjBOMTable.BOMId));
{
setPrefix("BOM not imported successfully.");
info(strFmt("%1",ObjBOMTable.BOMId));
delete_from ObjBOMVersion1 where ObjBOMVersion1.BOMId == ObjBOMVersion.BOMId;
//while select * from ObjBOM1 where ObjBOM1.BOMId == ObjBOM.BOMId
//{
//delete_from ObjBOM2 where ObjBOM2.BOMId == ObjBOM1.BOMId;
//}
delete_from ObjBOMTable1 where ObjBOMTable1.BOMId == ObjBOMTable.BOMId;
//while select * from ObjBOM1 where ObjBOM1.BOMId == ObjBOM.BOMId
//{
//delete_from ObjBOM2 where ObjBOM2.BOMId == ObjBOM1.BOMId;
//}
delete_from ObjBOMTable1 where ObjBOMTable1.BOMId == ObjBOMTable.BOMId;
FlagBOMTable = false;
FlagBOMVersion = false;
FlagBOM1Line = false;
FlagBOMOtherLine = false;
}
else
{
//dev_ImportedCV Insert Start
Objdev_ImportedCV.AccountNum = ObjBOMTable.BOMId;
Objdev_ImportedCV.Name = ObjBOMTable.Name;
Objdev_ImportedCV.Type = "BOM";
Objdev_ImportedCV.insert();
//dev_ImportedCV Insert End
FlagBOMVersion = false;
FlagBOM1Line = false;
FlagBOMOtherLine = false;
}
else
{
//dev_ImportedCV Insert Start
Objdev_ImportedCV.AccountNum = ObjBOMTable.BOMId;
Objdev_ImportedCV.Name = ObjBOMTable.Name;
Objdev_ImportedCV.Type = "BOM";
Objdev_ImportedCV.insert();
//dev_ImportedCV Insert End
countinsert++;
setPrefix("BOM imported successfully.");
info(strFmt("%1",ObjBOMTable.BOMId));
}
}
type = cells.item(row+1, 1).value().variantType();
//importing Item by rows end
}while (type != COMVariantType::VT_EMPTY);
//do while end
//ttscommit;
}
//Importing Item End
}
catch(Exception::Error)
{
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
}
ttsCommit;
info(strFmt("Total BOM %1 Inserted Successfully",countinsert));
//main try catch end
setPrefix("BOM imported successfully.");
info(strFmt("%1",ObjBOMTable.BOMId));
}
}
type = cells.item(row+1, 1).value().variantType();
//importing Item by rows end
}while (type != COMVariantType::VT_EMPTY);
//do while end
//ttscommit;
}
//Importing Item End
}
catch(Exception::Error)
{
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
}
ttsCommit;
info(strFmt("Total BOM %1 Inserted Successfully",countinsert));
//main try catch end
Thanks for comments.....