How to import data into LedgerJournals through Excel in AX2012

 

The following code helps to import LedgerJournals data in AX2012

 

 

class  GeneralLedgersDataUpload  extends  Runbase

{

    DialogField                     dialogFilename;

    FilenameOpen                fileName;

 

 

    #define.CurrentVersion(1)

    #localmacro.CurrentList

        filename

    #endmacro

}

 

protected Object dialog()

{

    DialogRunBase   dialog = new DialogRunBase("Ledgers Data import", this);

 

    ;

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));

    dialog.filenameLookupFilter(["@SYS28576","*.xlsx"]);

    dialog.filenameLookupTitle("Upload from Excel");

    dialogFilename.value(filename);

    dialog.caption("Excel Upload");

    return dialog;

}

 

 

public boolean getFromDialog()

{

;

    filename        = dialogFilename.value();

 

    if(!filename)

    {

    throw error("@SYS112406");

    }

    return true;

}

 

 

public container pack()

{

    ;

    return [#CurrentVersion,#CurrentList];

} 

 

 

public boolean unpack(container _packedClass)

{

    Integer     version     = conpeek(_packedClass,1);

    ;

    switch (version)

    {

    case #CurrentVersion:

    [version,#CurrentList]      = _packedClass;

    break;

 

    default :

 

    return false;

    }

    return true;

}

 

 

public static GL_LedgersDataUpload construct()

{

    return  new  GeneralLedgersDataUpload();

}

 

 

public static void main(Args _args)

{

    GeneralLedgersDataUpload   ExcelUpload  =  GeneralLedgersDataUpload::construct();

    ;

    if (ExcelUpload.prompt())

    {

        //Call function ExcelUpload.

        ExcelUpload.ImportDatafromExcel();

 

    }

}

 

 

Public void ImportDatafromExcel()

{

    SysExcelApplication application;

    SysExcelWorkbooks   workbooks;

    SysExcelWorkbook    workbook;

    SysExcelWorksheets  worksheets;

    SysExcelWorksheet   worksheet;

    SysExcelCells       cells;

    COMVariantType      type;

    int                                row;

    str                               c,test; 

 

    AxLedgerJournalTable ledgerheader           = new AxLedgerJournalTable();

    AxLedgerJournalTrans ledgertransactions  = new AxLedgerJournalTrans();

 

    boolean                                            temp,duplicateRecCheck;

    NoYes                                                noYesEnum;

 

    LedgerJournalId                               oldnum, newnum;

    LedgerJournalACType                     ledgerJournalACType,offsetACType;

    ledgerJournalACType                      tmpACType,tmpOffsetACType;

    AccountNum                                     tmpAccount,offsetAccount;

    LedgerDimensionAccount              ledgerDim;

    CustTable                                              _custTable;

    AssetTable                                        _assetTable;

    AssetBookTable                               _assetBookTable;

    AssetBook                                         _assetBook;

    str                                                     department;

    LedgerAccountContract               ledgerAccountContract;

 

    DimensionAttributeValueContract attributeValueContract;

    DimensionStorage        dimensionStorage;

 

 

    ;

    duplicateRecCheck = false;

    temp    = false;

    row     = 2;

 

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    workbooks.open(fileName,0,true);

 

    try

    {

        workbooks.open(filename,0,0,2);

    }

    catch (Exception::Error)

    {

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

    }

 

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

 

    //do

   // breakpoint;

    while(cells.item(row,1).value().toString()!= "VT_EMPTY")

   // while (type != COMVariantType::VT_EMPTY)

    {

 

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

        if(oldnum != newnum)

        {

            ledgerheader.parmJournalName( cells.item(row,2).value().bStr());

            ledgerheader.parmJournalNum( cells.item(row,3).value().bStr());

            ledgerheader.parmName( cells.item(row,4).value().bStr());

            ledgerheader.currentRecord().insert();

        }

 

            ledgertransactions.parmJournalNum(cells.item(row,3).value().bStr());

            ledgertransactions.parmTransDate(cells.item(row,5).value().date());

            ledgertransactions.parmVoucher( cells.item(row,6).value().bStr());

            tmpACType = str2enum(ledgerJournalACType,cells.item(row,7).value().bStr());

            ledgertransactions.parmAccountType(tmpACType);

 

            ledgertransactions.parmCurrencyCode(cells.item(row,10).value().bStr());

            ledgertransactions.parmExchRate(cells.item(row,11).value().double());

            ledgertransactions.parmAmountCurDebit(cells.item(row,12).value().double());

            ledgertransactions.parmAmountCurCredit(cells.item(row,13).value().double());

            ledgertransactions.parmTxt(cells.item(row,14).value().bStr());

            tmpOffsetACType =  str2enum(offsetACType,cells.item(row,15).value().bStr());

            ledgertransactions.parmOffsetAccountType(tmpOffsetACType);

 

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

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

            ledgerDim = 0;

 

            if(tmpACType == LedgerJournalACType::Cust)

            {

                    ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount,LedgerJournalACType::Cust);

                    ledgertransactions.parmLedgerDimension(ledgerDim);

                    _custTable = CustTable::find(tmpAccount);

                    ledgertransactions.parmDefaultDimension(_custTable.DefaultDimension);

 

            }

 

            else if(tmpACType == LedgerJournalACType::Vend)

            {

                ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount, LedgerJournalACType::Vend);

                ledgertransactions.parmLedgerDimension(ledgerDim);

                ledgertransactions.parmExchRate(cells.item(row,11).value().double() * 100);

            }

 

            else if(tmpACType == LedgerJournalACType::FixedAssets)

            {

                ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount, LedgerJournalACType::FixedAssets);

                ledgertransactions.parmLedgerDimension(ledgerDim);

 

                _assetTable = AssetTable::find(tmpAccount);

 

                select * from _assetBook where _assetBook.AssetId   == _assetTable.AssetId;

                select _assetBookTable where _assetBookTable.BookId == _assetBook.BookId;

                ledgertransactions.parmDefaultDimension(_assetTable.defaultDimension(_assetBook.BookId));

            }

 

            if(tmpACType == LedgerJournalACType::Ledger)

            {

                ledgerAccountContract = new LedgerAccountContract();

                ledgerAccountContract.parmValues(new List(Types::Class));

                department = 'MainAccount';

 

                ledgerAccountContract.parmMainAccount(tmpAccount);

               if (department)

                {

                    attributeValueContract = new DimensionAttributeValueContract();

                    attributeValueContract.parmName("MainAccount");

                    attributeValueContract.parmValue(tmpAccount);

                    ledgerAccountContract.parmValues().addEnd(attributeValueContract);

                }

 

                dimensionStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);

                ledgertransactions.parmLedgerDimension(dimensionStorage.save());

 

            }

 

            if(tmpoffsetACType == LedgerJournalACType::Ledger)

            {

                ledgerAccountContract = new LedgerAccountContract();

                ledgerAccountContract.parmValues(new List(Types::Class));

                department = 'MainAccount';

 

                ledgerAccountContract.parmMainAccount(offsetAccount);

                if (department)

                {

                    attributeValueContract = new DimensionAttributeValueContract();

                    attributeValueContract.parmName("MainAccount");

                    attributeValueContract.parmValue(offsetAccount);

                    ledgerAccountContract.parmValues().addEnd(attributeValueContract);

                }

 

                dimensionStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);

                ledgertransactions.parmOffsetLedgerDimension(dimensionStorage.save());

 

            }

 

            ledgertransactions.currentRecord().insert();

            temp = true;

            oldnum =  ledgerheader.parmJournalName( cells.item(row,2).value().bStr());

 

        row++;

       }

 

    if(temp == true)

    {

    info(strfmt("Uploaded successfully."));

    }

    else

    {

    info(strfmt("Upload fail."));

    }

 

    application.quit();

}

 

 

 

PO creation on Button click

The following is to create the  PO for the journals on the button click event for the selected posted journals on ledgerJournalTable form which has TradeAgreement

Step 1 : Created one field TradeAgreement  (TradeAgreement is associated with items on its master form) on LedgerJournalTable.

Step 2  : Create one edit method on LedgerJournalTable forms datasource Level to select the journals for PO creation.

The following is the method

public edit Noyes createPO(boolean _set, LedgerJournalTable _table, NoYes _value)

{

    LedgerJournalTable    updatePOCreation;

    ; 

    LedgerJournalTable_ds.allowEdit(true);

    if (_set)

    {

        if(_table.Posted  == Noyes::No)

        {

            throw error("Please choose posted record");

        }

        if(_value)

        { 

            setRecordId.add(_table.RecId);

            _value =  NOYes::Yes;

        } 

        else

        {

            setRecordId.remove(_table.RecId);

            _value =    NOYes::Yes;

        }

    }

    return setRecordId.in(_table.RecId);

}

Drag this method on to the Design --> Overview --> Grid

Step 3 :  Create a button and call the Class (which has PO Creation code) on its click() for selected journals

 

void clicked()

{

    SS_POCreate                     creationOfPO;

    boolean                               created;

    LedgerJournalTable              _ledgerJournalTable;

    SetEnumerator                      setRetention, updateRetention;

    SSRetentionReversal             RetentionReversal;

    ;

    super();

    creationOfPO    =   new SS_POCreate();

    RetentionReversal   =   SSRetentionReversal::construct();

    setRetention            =    Set::create(setRecordId.pack()).getEnumerator();

    updateRetention     = Set::create(setRecordId.pack()).getEnumerator();

    if(!setRecordId.empty())

    {

        ttsbegin;

        while(setRetention.moveNext())

        {

            select _ledgerJournalTable where  _ledgerJournalTable.RecId == setRetention.current();

            creationOfPO.createPO(_ledgerJournalTable.JournalNum);

            created  = true;

        }

        ttscommit;

    }

    if(created)

    {

        info("PO's created sucessfully");

    }

    else

    {

        info("PO's creation failed");

    }

}

Step 4 : The PO creation class should be as following

class SS_POCreate

{

    PurchId         purchaseId;

    PurchTable      purchaseTable;

    PurchLine       purchaseLines;

    boolean         created;

}

Void  createPO(LedgerJournalId     _journal)

{

    SS_TradeAgreementId     tradeAgreementId;

    PurchTable              purchTableCur;

    ;

    tradeAgreementId  = LedgerJournalTable::find(_journal).SS_TradeAgreementId;

    select purchaseTable where purchaseTable.SS_JournalNo == _journal;

    if(purchaseTable.RecId)

    {

        throw error (strfmt("PO is already created for the Journal : %1 , so unable to create again for the same",_journal));

    }

    else

    {

       ttsbegin;

        if(tradeAgreementId)

        { 

            purchaseTable.PurchId        = NumberSeq::newGetNumFromCode(NumberSequenceReference::find(extendedTypeNum(purchid)).NumberSequence,true,false).num(); 

            purchaseTable.OrderAccount   = SS_CategoryTradeAgreement::find(tradeAgreementId).Vendor;

            purchaseTable.InvoiceAccount = SS_CategoryTradeAgreement::find(tradeAgreementId).Vendor;

            purchaseTable.PurchaseType   = PurchaseType::Purch;

            purchaseTable.PurchStatus    = PurchStatus::Backorder;

            purchaseTable.PurchTypeCode  = "Stock";

            purchaseTable.initFromVendTable(VendTable::find(SS_CategoryTradeAgreement::find(tradeAgreementId).Vendor));

            purchaseTable.PurchOrderType = PurchOrderType::Regular;

            purchaseTable.SS_JournalNo   = _journal;

            purchaseTable.CurrencyCode   = SS_CategoryTradeAgreement::find(tradeAgreementId).Currency;

            purchaseTable.insert();

            if(purchaseTable.PurchId)

            {

               this.createPOLines(purchaseTable.PurchId ,tradeAgreementId);

            }

            ttscommit;

        }

    }

}

void createPOLines(PurchId   _purchId ,SS_TradeAgreementId   _tradeAgreementId)

{

    InventDim             _inventDim;

    InventItemLocation    inventItemLocation;

    Inventlocation        inventLocation;

    InventTable           inventTable;

    ;

    purchaseLines.clear();

    purchaseLines.PurchId    = _purchId;

    purchaseLines.ItemId     = SS_CategoryTradeAgreement::find(_tradeAgreementId).ItemId;

    inventTable              = inventTable::find(purchaseLines.ItemId);

    purchaseLines.PurchUnit  = inventTable.purchUnitId();

    purchaseLines.PurchQty              = 1;

    purchaseLines.PurchPrice            = SS_CategoryTradeAgreement::find(_tradeAgreementId).Rate;

    purchaseLines.CurrencyCode          = SS_CategoryTradeAgreement::find(_tradeAgreementId).Currency;

    purchaseLines.PurchStatus           = PurchStatus::Backorder;

    purchaseLines.PurchaseType          = PurchaseType::Purch;

    purchaseLines.LineAmount            = purchaseLines.PurchQty * purchaseLines.PurchPrice;

    purchaseLines.LineNum               = PurchLine::lastLineNum(_purchId) + 1.0;

    select firstonly InventItemLocation

        where InventItemLocation.ItemId == purchaseLines.ItemId

        &&   InventItemLocation.inventDimId != "AllBlank";

    if(InventItemLocation.RecId)

    {

          _inventDim.InventSiteId    = InventLocation::find(InventDim::find(InventItemLocation.inventDimId).InventLocationId).InventSiteId;

          _inventDim.InventLocationId = InventDim::find(InventItemLocation.inventDimId).InventLocationId;

    purchaseLines.InventDimId =   InventDim::findOrCreate(_inventDim).inventDimId;

    purchaseLines.insert();

    }

    else

    {

    throw error(strfmt("Warehouse item setup could not find for the item :%1 ",purchaseLines.ItemId));

    }

}

static  SS_POCreate construct()

{

    return new SS_POCreate();

}