Tuesday, 21 October 2014

Import General Ledger Opening Balances In Axapta 2012

Here I'm sharing code to import GL Opening Balances Lines.

Note : The below code import only Lines.


-> Copy and paste below code in AOT->Jobs(Compile Job before using)
->open excel and fill the data according to the below format and save in CSV format
->Run job and browse file and press ok

Csv Format :

column1 - GL Journal Number
column2 - MainAccountNum
column3 - Dimension1
column4 - Dimension2
column5 - Dimension3
column6 - Debit Amount
column7 - Credit Amount
column8 - Transaction Date
Column9 - Description


//Code Starts From Here- @ Rahul Talasila

static void GL_OpeningBalances(Args _args)
{
    CommaIO                   csvFile;
    container                 readCon, accPattern,offSetPattern, dimPattern1;
    counter                   icount,inserted;
    Dialog                    dialog;
    DialogField               dfFileName;
    FileName                  fileName;
    AxLedgerJournalTable      axLedgerJournalTable ;
    AxLedgerJournalTrans      axLedgerJournalTrans;
    MainAccount               mainAccount;
    MainAccountNum            mainAccountNum;
    DimensionDynamicAccount   ledgerdimension;
    str transdate;
    ;
     inserted =0;
    #File
    dialog     = new Dialog("Pick the file");
   // dialog
    dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
    dialog.filenameLookupFilter(["All files", #AllFiles]);
    if(dialog.run())
    {
       filename =  dfFileName.value();
    }
       csvFile  =  new CommaIO(filename,'r');

    if(csvFile)
    {
        ttsBegin;
        while (csvFile.status() == IO_Status::OK)
        {
          readCon  = csvFile.read();
          if(readCon)
            {
                MainAccountNum = conPeek(readCon,2);
                mainAccount = mainAccount::findByMainAccountId(MainAccountNum);

                if(!mainAccount)
                {
                    info(strFmt("%1",MainAccountNum));
                }
                transdate = conPeek(readCon, 8);
                axLedgerJournalTable = new AxLedgerJournalTable();
                axLedgerJournalTrans = new AxLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(conPeek(readCon, 1));
                axLedgerJournalTrans.parmTransDate(str2Date(transdate,123));
                axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Ledger);
                accPattern = [conPeek(readCon,2) + conPeek(readCon,3) + conPeek(readCon,4) + conPeek(readCon,5), conPeek(readCon,2),conPeek(readCon,3),conPeek(readCon,4),conPeek(readCon,5)];
                axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accPattern));
                axLedgerJournalTrans.parmDefaultDimension(LedgerJournalTable::find(conPeek(readCon, 1)).DefaultDimension);
                axLedgerJournalTrans.parmOffsetAccountType(LedgerJournalACType::Ledger);
                axLedgerJournalTrans.parmAmountCurDebit(conPeek(readCon, 6));
                axLedgerJournalTrans.parmAmountCurCredit(conPeek(readCon, 7));
                axLedgerJournalTrans.parmTxt(conPeek(readCon,9));
                axLedgerJournalTrans.save();
                icount++;
                inserted++;
            }
        }
         ttsCommit;
    }
  info(strfmt("%1 records Inserted out of %2",inserted,icount));
}


Note : Based on ledger account dimension structure your account pattern will change

@ Rahul Talasila

No comments:

Post a Comment

Sorting data in a form datasource in Dynamics Ax 2012 / D365F&O X++

In Microsoft Dynamics AX, you can sort a form datasource when the form is loaded. On the form, override the init method. After the call...