Import Product Variants With master Through Excel using Class in Ax 2012 R3

Import Product Variants With master Through Excel using Class in Ax 2012 R3

//This Class is writen by Harshal Kamble
Ans:

class


MRKPProductImport

{

#AviFiles

SysOperationProgress progress;
// = new SysOperationProgress();

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelWorkSheet workSheetInventTableModule;

SysExcelCells cellsInventTableModule;

SysExcelCells cells;

COMVariantType type;

COMVariantType typeModule;

FileName filename;

EcoResProductDisplayProductNumber productNumber;


// EcoResProductType productType;


// EcoResProductSubtype productSubtype;

EcoResProductName productName;

EcoResDescription description;

EcoResProductSearchName productSearchName;

Name size,color,style,productDimensionGroup,storageDimensionGroup,productSubtype,productType;

UnitOfMeasureSymbol uom;

}
private


EcoResProductMaster createProductMaster()

{

EcoResProductMaster ecoResProductMaster;

EcoResProductIdentifier ecoResProductIdentifier;

EcoResProductDimensionGroupProduct ecoResProductDimensionGroupProduct;

EcoResProductMasterModelingPolicy ecoResProductMasterModelingPolicy;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;

EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;

EcoResConfiguration ecoResConfiguration;

EcoResSize ecoResSize;
//

EcoResColor ecoResColor;
//

EcoResProductMasterConfiguration ecoResProductMasterConfiguration;

EcoResProductMasterSize ecoResProductMasterSize;
//

EcoResProductMastercolor ecoResProductMastercolor;
//

EcoResProductType resProductType;

;

ecoResProductMaster.clear();

ecoResProductMaster.initValue();

ecoResProductMaster.ProductType =
str2enum(resProductType,productType);

ecoResProductMaster.DisplayProductNumber = productNumber;

ecoResProductMaster.WW_Category = WW_Category::Stock;

ecoResProductMaster.SearchName = productSearchName;

ecoResProductMaster.VariantConfigurationTechnology = EcoResVariantConfigurationTechnologyType::PredefinedVariants;


if (ecoResProductMaster.validateWrite())

{

ecoResProductMaster.insert();

ecoResProductIdentifier.clear();

ecoResProductIdentifier.initValue();

ecoResProductIdentifier.ProductNumber = productNumber;

ecoResProductIdentifier.Product = ecoResProductMaster.RecId;

ecoResProductIdentifier.insert();


// Product dimension group

ecoResProductDimensionGroupProduct.clear();

ecoResProductDimensionGroupProduct.initValue();

ecoResProductDimensionGroupProduct.initFromProduct(ecoResProductMaster);

ecoResProductDimensionGroupProduct.ProductDimensionGroup = EcoResProductDimensionGroup::findByDimensionGroupName(productDimensionGroup).RecId;


if(ecoResProductDimensionGroupProduct.validateWrite())

{

ecoResProductDimensionGroupProduct.insert();

}


// Storage dimension group

ecoResStorageDimensionGroupProduct.clear();

ecoResStorageDimensionGroupProduct.initValue();

ecoResStorageDimensionGroupProduct.Product = ecoResProductMaster.RecId;

ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(storageDimensionGroup).RecId;


if(ecoResStorageDimensionGroupProduct.validateWrite())

{

ecoResStorageDimensionGroupProduct.insert();

}


// Tracking dimension group

ecoResTrackingDimensionGroupProduct.clear();

ecoResTrackingDimensionGroupProduct.initValue();

ecoResTrackingDimensionGroupProduct.Product = ecoResProductMaster.RecId;

ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName(
"None").RecId;


if(ecoResTrackingDimensionGroupProduct.validateWrite())

{

ecoResTrackingDimensionGroupProduct.insert();

}


// Product modeling policy

ecoResProductMasterModelingPolicy.clear();

ecoResProductMasterModelingPolicy.initValue();

ecoResProductMasterModelingPolicy.ProductMaster = ecoResProductMaster.RecId;


if(ecoResProductMasterModelingPolicy.validateWrite())

{

ecoResProductMasterModelingPolicy.insert();

}

EcoResProductTranslation::createOrUpdateTranslation(ecoResProductMaster.RecId,ecoResProductMaster.SearchName, ecoResProductMaster.SearchName);


// size

ecoRessize = EcoRessize::findByName(size);
//ecoResProductMaster.SearchName);


if (!ecoRessize)

{

ecoRessize.clear();

ecoRessize.initValue();

ecoRessize.Name = size;


if(ecoRessize.Name)

ecoRessize.insert();

}


// color

ecoRescolor = ecoRescolor::findByName(color);
//ecoResProductMaster.SearchName);


if (!ecoRescolor)

{

ecoRescolor.clear();

ecoRescolor.initValue();

ecoRescolor.Name = color;


if(ecoRescolor.Name)

ecoRescolor.insert();

}


// Size assigned to product master

ecoResProductMastersize.clear();

ecoResProductMastersize.initValue();

ecoResProductMastersize.Size = ecoRessize.RecId;

ecoResProductMastersize.sizeProductDimensionAttribute = EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(
fieldNum(InventDim, InventSizeId));

ecoResProductMastersize.sizeProductMaster = ecoResProductMaster.RecId;


if(ecoRessize.Name)

ecoResProductMastersize.insert();


// color assigned to product master

ecoResProductMasterColor.clear();

ecoResProductMasterColor.initValue();

ecoResProductMasterColor.Color = ecoResColor.RecId;

ecoResProductMasterColor.ColorProductDimensionAttribute = EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(
fieldNum(InventDim, InventColorId));

ecoResProductMasterColor.ColorProductMaster = ecoResProductMaster.RecId;


if(ecoRescolor.Name)

ecoResProductMasterColor.insert();

}


return ecoResProductMaster;

}

private


void createProductVariants(EcoResProductMaster _ecoResProductMaster)

{

EcoResDistinctProductVariant ecoResDistinctProductVariant;

EcoResProductIdentifier ecoResProductIdentifier;

EcoResProductVariantConfiguration ecoResProductVariantConfiguration;

EcoResProductVariantColor ecoResProductVariantColor;

EcoResProductVariantSize ecoResProductVariantSize;

EcoResProductVariantStyle ecoResProductVariantStyle;

InventTable inventTable;

InventTableModule inventTableModule;

InventItemSetupSupplyType inventItemSetupSupplyType;

EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;

EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;

EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;

InventModelGroupItem inventModelGroupItem;

InventItemGroupItem inventItemGroupItem;

InventDim inventDim;

InventDimCombination inventDimCombination;

inventTable inventTable_loc;

EcoResSize ecoRessize;

EcoResColor ecoResColor;

EcoResStyle ecoResStyle;

;

ecoRessize = EcoResSize::findByName(size);


if (!ecoRessize)

{

ecoRessize.clear();

ecoRessize.initValue();

ecoRessize.Name = size;


if(ecoRessize.Name)

ecoRessize.insert();

}

ecoResColor = ecoResColor::findByName(color);


if (!ecoResColor)

{

ecoResColor.clear();

ecoResColor.initValue();

ecoResColor.Name = color;


if(ecoResColor.Name)

ecoResColor.insert();

}

ecoResStyle = ecoResStyle::findByName(style);


if (!ecoResStyle)

{

ecoResStyle.clear();

ecoResStyle.initValue();

ecoResStyle.Name = style;


if(ecoResStyle.Name)

ecoResStyle.insert();

}

ecoResDistinctProductVariant.clear();

ecoResDistinctProductVariant.initValue();

ecoResDistinctProductVariant.DisplayProductNumber = EcoResProductNumberBuilderVariant::buildFromProductNumberAndDimensions(

_ecoResProductMaster.productNumber(),

EcoResProductVariantDimValue::getDimensionValuesContainer(
"",size,color,style));

ecoResDistinctProductVariant.SearchName = _ecoResProductMaster.SearchName;

ecoResDistinctProductVariant.ProductType = _ecoResProductMaster.ProductType;

ecoResDistinctProductVariant.ProductMaster = _ecoResProductMaster.RecId;

ecoResDistinctProductVariant.insert();


///Product variant size

ecoResProductVariantsize.clear();

ecoResProductVariantsize.initValue();

ecoResProductVariantsize.initFromDistinctProductVariant(ecoResDistinctProductVariant);

ecoResProductVariantsize.ProductDimensionAttribute = EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(
fieldNum(InventDim, InventSizeId));

ecoResProductVariantsize.Size = EcoResSize::findByName(size).RecId;


if(EcoResSize::findByName(size).Name)

ecoResProductVariantsize.insert();


/// ///Product variant color




ecoResProductVariantColor.clear();

ecoResProductVariantColor.initValue();

ecoResProductVariantColor.initFromDistinctProductVariant(ecoResDistinctProductVariant);

ecoResProductVariantColor.ProductDimensionAttribute = EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(
fieldNum(InventDim, InventColorId));

ecoResProductVariantColor.Color = EcoResColor::findByName(color).RecId;


if(EcoResColor::findByName(color).Name)

ecoResProductVariantColor.insert();


/// ///Product variant style




ecoResProductVariantStyle.clear();

ecoResProductVariantStyle.initValue();

ecoResProductVariantStyle.initFromDistinctProductVariant(ecoResDistinctProductVariant);

ecoResProductVariantStyle.ProductDimensionAttribute = EcoResProductDimensionAttribute::inventDimFieldId2DimensionAttributeRecId(
fieldNum(InventDim, InventStyleId));

ecoResProductVariantStyle.style = EcoResStyle::findByName(style).RecId;


if(EcoResStyle::findByName(style).Name)

ecoResProductVariantStyle.insert();


//Product variant translation




EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProductVariant.RecId,productName,description);


//Released product

inventTable.clear();

inventTable.initValue();

inventTable.initFromEcoResProduct(_ecoResProductMaster);


/////////////////////


// inventTable.ItemId = EcoResProductIdentifier::findByProduct(_ecoResProductMaster.RecId).ProductNumber;

ecoResProductIdentifier = EcoResProductIdentifier::findByProduct(_ecoResProductMaster.RecId);

inventTable.ItemId = ecoResProductIdentifier.ProductNumber;

inventTable.NameAlias = _ecoResProductMaster.SearchName;

inventTable.PmfProductType = PmfProductType::None;


if (inventTable.validateWrite() && !(inventTable::find(ecoResProductIdentifier.ProductNumber).ItemId))

{

inventTable.insert();


//Inventory model group

inventModelGroupItem.clear();

inventModelGroupItem.initValue();

inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventModelGroupItem.ItemId = inventTable.ItemId;

inventModelGroupItem.ModelGroupId =
"FIFO";

inventModelGroupItem.ModelGroupDataAreaId =
curext();

inventModelGroupItem.insert();


//Item group


//inventItemGroupItem.clear();


//inventItemGroupItem.initValue();


//inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;


//inventItemGroupItem.ItemId = inventTable.ItemId;


//inventItemGroupItem.ItemGroupId = cells.item(row, 8).value().bStr();//"Parts";


//inventItemGroupItem.ItemGroupDataAreaId = curext();


//inventItemGroupItem.insert();


//Extended product details - Inventory

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Invent;

inventTableModule.insert();


//Extended product details - Purchase

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Purch;

inventTableModule.UnitId = uom;

inventTableModule.insert();


//Extended product details - Sales

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Sales;

inventTableModule.UnitId = uom;

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(_ecoResProductMaster.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(_ecoResProductMaster.RecId);


if (ecoResTrackingDimensionGroupProduct.RecId)

{

ecoResTrackingDimensionGroupItem.clear();

ecoResTrackingDimensionGroupItem.initValue();

ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;

ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;

ecoResTrackingDimensionGroupItem.insert();

}

}

inventDim.clear();

inventDim.InventSizeId = EcoResSize::findByName(size).Name;

inventDim.InventColorId = EcoResColor::findByName(color).Name;

inventDim.InventStyleId = EcoResStyle::findByName(style).Name;

inventDim = InventDim::findOrCreate(inventDim);


//Released product variant

inventDimCombination.clear();

inventDimCombination.initValue();

inventDimCombination.DistinctProductVariant = ecoResDistinctProductVariant.RecId;

inventDimCombination.ItemId = inventTable.ItemId;

inventDimCombination.InventDimId = inventDim.InventDimId;

inventDimCombination.insert();


select forUpdate inventTable_loc


where inventTable_loc.ItemId == inventTable.ItemId;

inventTable_loc.StandardInventColorId = EcoResColor::findByName(color).Name;

inventTable_loc.StandardInventSizeId = EcoResSize::findByName(size).Name;

inventTable_loc.StandardInventStyleId = EcoResStyle::findByName(style).Name;


if (inventTable_loc.validateWrite())

{

inventTable_loc.update();

}

　

}

　public name parmColor(Name _color)


{

color = _color;


return color;

}

public


EcoResDescription parmDescription(EcoResDescription _description)

{

description = _description;


return description;

}


public


Name parmProductDimensionGroup(Name _productDimensionGroup)

{

productDimensionGroup = _productDimensionGroup;


return productDimensionGroup;

}


public


EcoResProductName parmProductName(EcoResProductName _productName)

{

productName = _productName;


return productName;

}

public


EcoResProductDisplayProductNumber parmProductNumber(EcoResProductDisplayProductNumber _productNumber)

{

productNumber = _productNumber;


return productNumber;

}

public


EcoResProductSearchName parmProductSearchName(EcoResProductSearchName _productSearchName)

{

productSearchName = _productSearchName;


return productSearchName;

}

public


Name parmProductSubtype(Name _productSubtype)

{

productSubtype = _productSubtype;


return productSubtype;

}

public


Name parmProductType(Name _productType)

{

productType = _productType;


return productType;

}

public


Name parmSize(Name _size)

{

size = _size;


return size;

}

public


Name parmStorageDimensionGroup(Name _storageDimensionGroup)

{

storageDimensionGroup = _storageDimensionGroup;


return storageDimensionGroup;

}

public


Name parmStyle(Name _style)

{

style = _style;


return style;

}

public


UnitOfMeasureSymbol parmUOM(UnitOfMeasureSymbol _uom)

{

uom = _uom;


return uom;

}


private


void readExcel()

{


int row = 1,i;

EcoResProductMaster ecoResProductMaster;

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

filename =
"C:\\Users\\SUP-TNGUYEN\\Desktop\\Product variant\\test product masters-1.xls";


try

{

workbooks.open(filename);

}


catch (Exception::Error)

{


throw error("File cannot be opened.");

}


ttsBegin;

workbook = workbooks.item(
1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(
1);

cells = worksheet.cells();


do

{

row++;


if(row > 1)

{


//this.parmProductNumber(cells.item(row, 4).value().bStr());

this.parmProductNumber(
int2str(cells.item(row, 4).value().double()));

this.parmProductType(cells.item(row,
1).value().bStr());

this.parmProductSubtype(cells.item(row,
3).value().bStr());

this.parmProductName(cells.item(row,
7).value().bStr());

this.parmProductSearchName(cells.item(row,
8).value().bStr());

this.parmDescription(cells.item(row,
10).value().bStr());

this.parmProductDimensionGroup(cells.item(row,
11).value().bStr());

this.parmStorageDimensionGroup(cells.item(row,
13).value().bStr());

this.parmSize(cells.item(row,
14).value().bStr());

this.parmColor(cells.item(row,
15).value().bStr());

this.parmStyle(cells.item(row,
16).value().bStr());

this.parmUOM(cells.item(row,
17).value().bStr());


if(productSubtype == 'Product master')

{

ecoResProductMaster = this.createProductMaster();

}


if(ecoResProductMaster)

{

row++;


for(i = row;((cells.item(i,3).value().bStr() != 'Product master') && (cells.item(i,3).value().bStr() != '')) ;i++)

{

this.parmProductName(cells.item(i,
4).value().bstr());

this.parmDescription(cells.item(i,
10).value().bStr());

this.parmProductSearchName(cells.item(i,
8).value().bStr());

this.parmSize(cells.item(i,
14).value().bStr());

this.parmColor(cells.item(i,
15).value().bStr());

this.parmStyle(cells.item(i,
16).value().bStr());

this.createProductVariants(ecoResProductMaster);

}

row = i;

}

}

}
while (type != COMVariantType::VT_EMPTY);

application.quit();

info(
"Done");


ttscommit;

}


static


server void main(Args args)
{
MRKPProductImport productImport;
;
productImport =
new MRKPProductImport();
productImport.readExcel();
}