Interview Questions

Monday, August 21, 2017

How to released items from excel through X++

//Excel Format:
//Item Number                Item Name                Search Name

static void ReleasedProductsCompanyWise_JOB(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FilenameOpen                    filename;
    str             itemnumber;
    int row = 1; // if the excel has the header
 
    EcoResProduct ecoResProduct;
    InventTable inventTable;
    InventTableModule inventTableModule;
    NumberSequenceTable numberSequenceTable;
    ItemId itemId;
    InventItemSetupSupplyType inventItemSetupSupplyType;

    EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;
    EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;
    EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;
    EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    filename  = "****.xlsx; // complete file path (You will find it in property window of excel)
 
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    //Iterate through cells and get the values
    do
    {
        //Incrementing the row line to next Row
        row++;
 
        itemnumber = cells.item(row,1).value().bStr();  
 
     
        select firstOnly ecoResProduct where EcoResProduct.DisplayProductNumber ==  cells.item(row,1).value().bStr();  //itemnumber //Audio system
        if(!inventTable::exist(itemnumber))
        {    
             
        //while select * from ecoResProduct
        changecompany ('DAT')
        {
        ttsBegin;
        inventTable = null;
        inventTableModule = null;
        inventItemSetupSupplyType = null;
        ecoResStorageDimensionGroupProduct = null;
        ecoResTrackingDimensionGroupProduct = null;
        ecoResStorageDimensionGroupItem = null;
        ecoResTrackingDimensionGroupItem = null;

        numberSequenceTable = InventParameters::numRefItemId().numberSequenceTable();
        //get item id from:
        //1. Product number if number seq for item ID is not set up or manual or return blank value
        if (!numberSequenceTable.RecId || numberSequenceTable.Manual)
        {
        itemId = ecoResProduct.productNumber();
        }
        else //number sequence auto, get a number
        {
        itemId = NumberSeq::newGetNumFromId(numberSequenceTable.RecId).num();
        }

        inventTable.initValue();
        inventTable.initFromEcoResProduct(ecoResProduct);
        inventTable.ItemId = ItemId;
        inventTable.NameAlias = ecoResProduct.SearchName;
        inventTable.insert(true);

        // Create inventTableModules

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Invent;
        inventTableModule.insert();

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Purch;
        inventTableModule.insert();

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Sales;
        inventTableModule.insert();

        //Create inventItemLocation
        InventItemLocation::createDefault(inventTable.ItemId);

        // Creates a new item default order type for the product that is released.

        inventItemSetupSupplyType.initValue();
        inventItemSetupSupplyType.ItemId = inventTable.ItemId;
        inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;
        inventItemSetupSupplyType.insert();

        //create relationship tables to dimension groups.

        ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResProduct.RecId);
        ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResProduct.RecId);

        if (ecoResStorageDimensionGroupProduct.RecId)
        { // mandatory storage dimension group for the product
        ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
        ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;
        ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;
        ecoResStorageDimensionGroupItem.insert();
        }

        if (ecoResTrackingDimensionGroupProduct.RecId)
        { // mandatory tracking dimension group for the product
        ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
        ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;
        ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;
        //ecoResTrackingDimensionGroupItem.insert();
        }
     
        ttsCommit;
        info(strfmt("Product successfully released to ‘DAT’ legal entity"));
            }
        }
        // Loads the next row into the variant type and validating that its is empty or not
        type = cells.item(row+1, 1).value().variantType();
        }

        while (type != COMVariantType::VT_EMPTY);
     
        // quits the application
        application.quit();
 

}

No comments:

Post a Comment