static void SON_ProductcreateAndUpdate(Args _args)
{
#define.ProductNum()
#define.SearchName()
#define.StorageDim()
#define.TrackingDim()
#define.ItemId()
#define.NameAlias()
#define.ModelGroup()
#define.ItemGroup()
EcoResDistinctProduct ecoResDistinctProduct;
EcoResProductIdentifier ecoResProductIdentifier;
EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;
EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;
InventTable inventTable;
InventTableModule inventTableModule;
MCRInventTable mcrInventTable;
InventItemSetupSupplyType inventItemSetupSupplyType;
EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;
EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;
InventModelGroupItem inventModelGroupItem;
InventItemGroupItem inventItemGroupItem;
EcoResProductReleaseManagerBase releaseManager;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
FileIoPermission perm;
LongDescription number;
#File
int row,i;
Name name;
FileName filename;
InventTableModule inventTableModulePurch,inventTableModuleSales,inventTableModuleInvent;
EcoResProduct ecoResProduct;
ItemId itemId;
UnitOfMeasureSymbol purchUnit,salesUnit,inventUnit;
InventModelGroupId modelGroupId;
Name productType;
EcoResProductType ecoResProductType;
int j;
str dimValue;
DimensionDefault result;
container conValue;// = _value;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
container conAttr = ["@SON338"];
EcoResTrackingDimensionGroupItem trackingDimensionGroupItem;
EcoResTrackingDimensionGroup trackingDimensionGroup;
//ItemId itemId;
EcoResTrackingDimensionGroupName trDim;
EcoResStorageDimensionGroupItem dimensionGroupItem;
EcoResStorageDimensionGroup storageDimensionGroup;
EcoResStorageDimensionGroupName storDim;
ItemGroupId itemGroup;
// Name nameGroup;
Name lItem,lType,shipment,pos,dD,dDWarehouse,partnerIdf,ownUse;
NoYes noyes1,noyes2,noyes3,noyes4;
SON_ShipmentType son_ShipmentType;
string20 productName;
String10 productKey;
String10 nameGroup;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
perm = new FileIOPermission(filename,#io_read);
perm.assert();
info(strFmt('%1',DateTimeUtil::getSystemDateTime()));
//specify the file path that you want to read
filename = @'E:\Kavin\Additional Item upload\Additionalitemupload.xlsx';
//E:\Kavin\Additional Item upload
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
try
{
//ttsBegin;
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
if(row > 1)
{
nameGroup = '';
itemId = cells.item(row, 1).value().bStr();
productType = cells.item(row, 2).value().bStr();
productKey = cells.item(row, 3).value().bStr();
productName = cells.item(row, 4).value().bStr();
storDim = cells.item(row, 5).value().bStr();
trDim = cells.item(row, 6).value().bStr();
modelGroupId = cells.item(row, 7).value().bStr();
itemGroup = cells.item(row, 8).value().bStr();
dD = cells.item(row, 9).value().bStr();
dDWarehouse = cells.item(row, 10).value().bStr();
inventUnit = cells.item(row, 11).value().bStr();
purchUnit = cells.item(row, 12).value().bStr();
salesUnit = cells.item(row, 13).value().bStr();
lItem = cells.item(row, 14).value().bStr();
lType = cells.item(row, 15).value().bStr();
shipment = cells.item(row, 16).value().bStr();
pos = cells.item(row, 17).value().bStr();
partnerIdf = cells.item(row, 18).value().bStr();
// ownUse = cells.item(row, 19).value().bStr();
nameGroup = cells.item(row, 19).value().bStr();
select firstOnly * from inventTable
where inventTable.ItemId == itemId;
if(inventTable)
{
ttsBegin;
select firstOnly forupdate ProductType from ecoResProduct
where ecoResProduct.RecId == inventTable.Product;
if(ecoResProduct)
{
ecoResProduct.ProductType = str2enum(ecoResProductType, productType);
ecoResProduct.doUpdate();
}
ttsCommit;
ttsBegin;
conValue = conNull();
result = 0;
inventTable.selectForUpdate(true);
if (nameGroup != '')
{
conValue = [nameGroup];
for (j = 1; j <= conLen(conAttr); j++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,j));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,j);
if (dimValue != "")
{
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
if(result != 0)
{
inventTable.DefaultDimension = result;
}
}
inventTable.SON_LicensedItems = str2enum(noyes1,lItem);
inventTable.SON_LicenseType = lType;
inventTable.RAXShipmentType = str2enum(son_ShipmentType,shipment);
inventTable.SON_PosPartner = str2enum(noyes2,pos);
inventTable.RAXPartnerIdentifier = str2int(partnerIdf);
inventTable.RAXOwnUseItem = str2enum(noyes3,ownUse);
inventTable.doUpdate();
ttsCommit;
ttsBegin;
select firstOnly forUpdate * from mcrInventTable
where mcrInventTable.InventTable == inventTable.RecId;
mcrInventTable.InventTable = inventTable.RecId;
mcrInventTable.DropShipment = str2enum(noyes4,dD);
mcrInventTable.DefaultDropShipmentWarehouse = dDWarehouse;
mcrInventTable.write();
ttsCommit;
ttsBegin;
select firstOnly * from trackingDimensionGroupItem
where trackingDimensionGroupItem.ItemDataAreaId == inventTable.dataAreaId
&& trackingDimensionGroupItem.ItemId == itemId;
trackingDimensionGroupItem.selectForUpdate(true);
select firstonly RecId from trackingDimensionGroup
where trackingDimensionGroup.Name == trDim;
trackingDimensionGroupItem.ItemDataAreaId = inventTable.dataAreaId;
trackingDimensionGroupItem.ItemId = itemId;
trackingDimensionGroupItem.TrackingDimensionGroup = trackingDimensionGroup.RecId;
trackingDimensionGroupItem.write();
ttsCommit;
ttsBegin;
select firstOnly forupdate * from inventModelGroupItem
where inventModelGroupItem.ItemDataAreaId == inventTable.dataAreaId
&& inventModelGroupItem.ItemId == inventTable.ItemId;
inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;
inventModelGroupItem.ModelGroupDataAreaId = inventTable.dataAreaId;
inventModelGroupItem.ItemId = inventTable.ItemId;
inventModelGroupItem.ModelGroupId = modelGroupId;
inventModelGroupItem.write();
ttsCommit;
ttsBegin;
select firstOnly forupdate * from inventItemGroupItem
where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId
&& inventItemGroupItem.ItemId == inventTable.ItemId;
inventItemGroupItem.ItemGroupDataAreaId = inventTable.dataAreaId;
inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;
inventItemGroupItem.ItemId = inventTable.ItemId;
inventItemGroupItem.ItemGroupId = itemGroup;
inventItemGroupItem.write();
ttsCommit;
ttsBegin;
select firstonly forUpdate UnitId from inventTableModulePurch
where inventTableModulePurch.ItemId == inventTable.ItemId
&& inventTableModulePurch.ModuleType == ModuleInventPurchSales::Purch;
if(inventTableModulePurch)
{
inventTableModulePurch.UnitId = purchUnit;
inventTableModulePurch.doUpdate();
}
ttsCommit;
ttsBegin;
select firstonly forUpdate UnitId from inventTableModuleSales
where inventTableModuleSales.ItemId == inventTable.ItemId
&& inventTableModuleSales.ModuleType == ModuleInventPurchSales::Sales;
if(inventTableModuleSales)
{
inventTableModuleSales.UnitId = salesUnit;
inventTableModuleSales.doUpdate();
}
ttsCommit;
ttsBegin;
select firstonly forUpdate UnitId from inventTableModuleInvent
where inventTableModuleInvent.ItemId == inventTable.ItemId
&& inventTableModuleInvent.ModuleType == ModuleInventPurchSales::Invent;
if(inventTableModuleSales)
{
inventTableModuleInvent.UnitId = inventUnit;
inventTableModuleInvent.doUpdate();
}
ttsCommit;
ttsBegin;
select firstOnly * from dimensionGroupItem
where dimensionGroupItem.ItemDataAreaId == inventTable.dataAreaId
&& dimensionGroupItem.ItemId == itemId;
dimensionGroupItem.selectForUpdate(true);
select firstonly RecId from storageDimensionGroup
where storageDimensionGroup.Name == storDim;
dimensionGroupItem.ItemDataAreaId = inventTable.dataAreaId;
dimensionGroupItem.ItemId = itemId;
dimensionGroupItem.StorageDimensionGroup = storageDimensionGroup.RecId;
dimensionGroupItem.write();
ttsCommit;
//info(strFmt("Details updated for the ItemId :: %1 ",cells.item(row, 1).value().bStr()));
}
else
{
info(strFmt("Item not found :: %1",cells.item(row, 1).value().bStr()));
//create a new item
//Product
// itemname = "Rohit6";
// productName = "productname";
ecoResDistinctProduct.clear();
ecoResDistinctProduct.initValue();
ecoResDistinctProduct.ProductType = EcoResProductType::Item;
ecoResDistinctProduct.DisplayProductNumber = itemId;
ecoResDistinctProduct.SearchName = productName;
if (ecoResDistinctProduct.validateWrite())
{
ecoResDistinctProduct.insert();
ecoResProductIdentifier.clear();
ecoResProductIdentifier.initValue();
ecoResProductIdentifier.ProductNumber = itemId;
ecoResProductIdentifier.Product = ecoResDistinctProduct.RecId;
ecoResProductIdentifier.insert();
//Storage dimension group
ecoResStorageDimensionGroupProduct.clear();
ecoResStorageDimensionGroupProduct.initValue();
ecoResStorageDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;
ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName("SiteWH").RecId;
if (ecoResStorageDimensionGroupProduct.validateWrite())
{
ecoResStorageDimensionGroupProduct.insert();
}
//Tracking dimension group
ecoResTrackingDimensionGroupProduct.clear();
ecoResTrackingDimensionGroupProduct.initValue();
ecoResTrackingDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;
ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName("None").RecId;
if (ecoResTrackingDimensionGroupProduct.validateWrite())
{
ecoResTrackingDimensionGroupProduct.insert();
}
EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProduct.RecId, itemId,productName);//name,descripyion
//Released product
inventTable.clear();
inventTable.initValue();
inventTable.initFromEcoResProduct(ecoResDistinctProduct);
inventTable.ItemId = itemId;
inventTable.NameAlias = productName;
inventTable.RAXProductKey = productKey;
InventTable.PmfProductType = PmfProductType::None;
InventTable.SON_LicensedItems = str2enum(noyes1,lItem);
InventTable.SON_LicenseType = lType;
InventTable.RAXShipmentType = str2enum(son_ShipmentType,shipment);
InventTable.SON_PosPartner = str2enum(noyes2,pos);
InventTable.RAXPartnerIdentifier = str2int(partnerIdf);
InventTable.RAXOwnUseItem = str2enum(noyes3,ownUse);
if (nameGroup != '')
{
conValue = [nameGroup];
for (j = 1; j <= conLen(conAttr); j++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,j));
if (dimensionAttribute.RecId == 0)
{
continue;
}
dimValue = conPeek(conValue,j);
if (dimValue != "")
{
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,true);
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
if(result != 0)
{
inventTable.DefaultDimension = result;
}
}
if (inventTable.validateWrite())
{
inventTable.insert();
ttsBegin;
select firstOnly forUpdate * from mcrInventTable
where mcrInventTable.InventTable == inventTable.RecId;
mcrInventTable.InventTable = inventTable.RecId;
mcrInventTable.DropShipment = str2enum(noyes4,dD);
mcrInventTable.DefaultDropShipmentWarehouse = dDWarehouse;
mcrInventTable.write();
ttsCommit;
//Inventory model group
inventModelGroupItem.clear();
inventModelGroupItem.initValue();
inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;
inventModelGroupItem.ItemId = inventTable.ItemId;
inventModelGroupItem.ModelGroupId = modelGroupId;
inventModelGroupItem.ModelGroupDataAreaId = curext();
inventModelGroupItem.insert();
//Item group
inventItemGroupItem.clear();
inventItemGroupItem.initValue();
inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;
inventItemGroupItem.ItemId = inventTable.ItemId;
inventItemGroupItem.ItemGroupId = itemGroup;
inventItemGroupItem.ItemGroupDataAreaId = curext();
inventItemGroupItem.insert();
//Extended product details – Inventory
inventTableModule.clear();
inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.UnitId = inventUnit;
inventTableModule.ModuleType = ModuleInventPurchSales::Invent;
inventTableModule.insert();
//Extended product details – Purchase
inventTableModule.clear();
inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.UnitId = purchUnit;
inventTableModule.ModuleType = ModuleInventPurchSales::Purch;
inventTableModule.insert();
//Extended product details – Sales
inventTableModule.clear();
inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.UnitId = salesUnit;
inventTableModule.ModuleType = ModuleInventPurchSales::Sales;
inventTableModule.insert();
//Warehouse items
InventItemLocation::createDefault(inventTable.ItemId);
//Supply type setup
inventItemSetupSupplyType.clear();
inventItemSetupSupplyType.initValue();
inventItemSetupSupplyType.ItemId = inventTable.ItemId;
inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;
inventItemSetupSupplyType.insert();
//Product storage dimension group
ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);
if (ecoResStorageDimensionGroupProduct.RecId)
{
ecoResStorageDimensionGroupItem.clear();
ecoResStorageDimensionGroupItem.initValue();
ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;
ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;
ecoResStorageDimensionGroupItem.insert();
}
//Product tracking dimension group
ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);
if (ecoResTrackingDimensionGroupProduct.RecId)
{
ecoResTrackingDimensionGroupItem.clear();
ecoResTrackingDimensionGroupItem.initValue();
ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;
ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;
ecoResTrackingDimensionGroupItem.insert();
}
}
}
//create a new item
}//else ends here
}
//if (row == 2100)
//{
//break;
//}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
CodeAccessPermission::revertAssert();
application.quit();
//ttsCommit;
}
catch(Exception::Error)
{
info(strFmt('%1 - %2',DateTimeUtil::getSystemDateTime(), row));
throw error("File upload failed");
}
info("File uploaded success fully");
info(strFmt('%1',DateTimeUtil::getSystemDateTime()));
}