Import/ Update Financial dimension through Excel or Table's data

Import/Update Financial dimension through excel data

 

 

 

 

Job: static void importFinancialDimension(Args _args)
{
SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

Name name;

FileName filename;

CustAccount custAccount;

CustTable custTable;

int row;


AxeDataRecord _dataRecord;

Description busStreem,CostCenter,Country,Department,Importer,IntOrder,Region,SubPrg,TradePartner;

    DimensionAttributeValue             dimAttrBusStreemVal,dimAttrCostcenterVal,dimAttrCountryVal,dimAttrDepartmentVal,dimAttrImporterVal,

dimAttrIntOrderVal,dimAttrRegionVal,dimAttrSubPrgVal,dimAttrTradePartnerVal;

    DimensionAttribute                  dimAttrBusStreem,dimAttrCostcenter,dimAttrCountry,dimAttrDepartment,dimAttrImporter,

dimAttrIntOrder,dimAttrRegion,dimAttrSubPrg,dimAttrTradePartner;

    DimensionAttributeValueSetStorage  davss;

    boolean                             isValidDimension = true;



    DimensionDefault                    DefaultDimension;

    davss = DimensionAttributeValueSetStorage::find(DefaultDimension);

    dimAttrBusStreem        =   DimensionAttribute::findByName('Business_Stream');

dimAttrCostcenter       =   DimensionAttribute::findByName('CostCenter');

    dimAttrCountry          =   DimensionAttribute::findByName('Country');

dimAttrDepartment       =   DimensionAttribute::findByName('Department');

dimAttrImporter         =   DimensionAttribute::findByName('Importer');

dimAttrIntOrder         =   DimensionAttribute::findByName('InternalOrder');

dimAttrRegion           =   DimensionAttribute::findByName('Region');

dimAttrSubPrg           =   DimensionAttribute::findByName('SubProgram');

dimAttrTradePartner  =   DimensionAttribute::findByName('TradingPartners');



application = SysExcelApplication::construct();

workbooks = application.workbooks();

//specify the file path that you want to read

//filename = "C:\\Users\\v-kunaya\\Documents\\Vimal\\DIXF\\Final data imported into MST\\Customer_PrimaryAddress_FinDim1.xlsx";

filename = "C:\\Users\\v-kunaya\\Documents\\Vimal\\DIXF\\Final data imported into MST\\Customer_PrimaryAddressAX2009_FinDim1.xlsx";

//filename = "C:\\DMF\\DIXF_vimsin\\Ready to Import\\Imported on 26102015\\Customer_PrimaryAddress_FinDim7.xlsx";

try


{

workbooks.open(filename);

}

catch (Exception::Error)


{

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


}
workbook = workbooks.item(1);


worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number which you can see like Sheet1


cells = worksheet.cells();
do


{

row++;
  DefaultDimension = 0;

custAccount = cells.item(row, 1).value().bStr();

if (custAccount =="")

custAccount = any2str(cells.item(row, 1).value().double());

busStreem = cells.item(row, 2).value().bStr();

if (busStreem == "")

busStreem = any2str(cells.item(row, 2).value().double());

CostCenter = cells.item(row, 3).value().bStr();

if (CostCenter == "")

CostCenter = any2str(cells.item(row, 3).value().double());

Country = cells.item(row, 4).value().bStr();

if (Country == "")

Country = any2str(cells.item(row, 4).value().double());

Department = cells.item(row, 5).value().bStr();

if (Department == "")

Department = any2str(cells.item(row, 5).value().double());

Importer = cells.item(row, 6).value().bStr();

if (Importer == "")

Importer = any2str(cells.item(row, 6).value().double());

IntOrder = cells.item(row, 7).value().bStr();

if (IntOrder == "")

IntOrder = any2str(cells.item(row, 7).value().double());

Region = cells.item(row, 8).value().bStr();

if (Region == "")

Region = any2str(cells.item(row, 8).value().double());

SubPrg = cells.item(row, 9).value().bStr();

if (SubPrg == "")

SubPrg = any2str(cells.item(row, 9).value().double());

TradePartner = cells.item(row, 10).value().bStr();

if (TradePartner == "")

TradePartner = any2str(cells.item(row, 10).value().double());

dimAttrBusStreemVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrBusStreem, busStreem, false, true);

dimAttrCostcenterVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCostcenter, CostCenter, false , true);

    dimAttrCountryVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCountry, Country, false, true);

dimAttrDepartmentVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrDepartment, Department, false, true);

dimAttrImporterVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrImporter, Importer, false, true);

dimAttrIntOrderVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrIntOrder, IntOrder, false, true);

dimAttrRegionVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrRegion, Region, false, true);

dimAttrSubPrgVal   = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrSubPrg, SubPrg, false, true);

dimAttrTradePartnerVal = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrTradePartner, TradePartner, false, true);

    if (dimAttrBusStreemVal)
    {
       davss.addItem(dimAttrBusStreemVal);
    }     if (dimAttrCostcenterVal)


    {

        davss.addItem(dimAttrCostcenterVal);

    }

if (dimAttrCountryVal)


    {

        davss.addItem(dimAttrCountryVal);

    }

     if (dimAttrDepartmentVal)


    {

        davss.addItem(dimAttrDepartmentVal);

    }

if (dimAttrImporterVal)


    {

        davss.addItem(dimAttrImporterVal);

    }

     if (dimAttrIntOrderVal)


    {

        davss.addItem(dimAttrIntOrderVal);

    }

if (dimAttrRegionVal)


    {

        davss.addItem(dimAttrRegionVal);

    }

     if (dimAttrSubPrgVal)


    {

        davss.addItem(dimAttrSubPrgVal);

    }

if (dimAttrTradePartnerVal)


    {

        davss.addItem(dimAttrTradePartnerVal);

    }

if (dimAttrBusStreemVal || dimAttrCostcenterVal || dimAttrCountryVal || dimAttrDepartmentVal || dimAttrImporterVal


|| dimAttrIntOrderVal || dimAttrRegionVal || dimAttrSubPrgVal || dimAttrTradePartnerVal)

    {

        DefaultDimension =   davss.save();

    }
     else

         DefaultDimension = 0;

   ttsBegin;

select forupdate custTable

where custTable.AccountNum == custAccount;

if(custTable && custTable.DefaultDimension == 0)


{

custTable.DefaultDimension = DefaultDimension;

custTable.update();

info(strFmt("%1 is updated",custAccount));


}
else

info(strFmt("%1 is not updated",custAccount));

ttsCommit;

 
type = cells.item(row+1, 1).value().variantType();



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



application.quit();

}

Import/Update Financial dimension through available table's data

Job: public DimensionDefault findDefaultDimId(AxeDataRecord _dataRecord)

{

    DimensionAttributeValue             dimCostcenterVal,dimDepartmentVal;

    DimensionAttribute                  dimAttrCostcenter,dimAttrDepartment;

    DimensionAttributeValueSetStorage   davss;

    boolean                             isValidDimension = true;

    DimensionDefault                    DefaultDimension; 

    davss = DimensionAttributeValueSetStorage::find(DefaultDimension);

    dimAttrCostcenter           =   DimensionAttribute::findByName('CostCenter');

    dimAttrDepartment           =   DimensionAttribute::findByName('Department'); 

   dimCostcenterVal      =   DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrCostcenter, _dataRecord.value('Dimension2_'), false , true);

    dimDepartmentVal      =   DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimAttrDepartment, _dataRecord.value('Dimension') , false, true);

    if(dimCostcenterVal )

    {

        davss.addItem(dimCostcenterVal);

    }

    if (dimDepartmentVal)

    {

        davss.addItem(dimDepartmentVal);

    }

    if(dimCostcenterVal || dimDepartmentVal)

    {

        DefaultDimension =   davss.save();

 

        return DefaultDimension;

    }

    else

        return 0;

}