Tuesday, October 21, 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

1 comment:

  1. Very helpful post! Importing GL opening balances can be tricky, and this step-by-step example with CSV formatting makes it much easier to follow. On a related note, if you’re also exploring modern app development concepts, understanding what is redux thunk can give you similar clarity when handling async operations in frontend frameworks. Thanks for sharing such practical insights!

    ReplyDelete

What is the primary purpose of using a Solution in Microsoft Power Platform & ALM?

As organizations embrace low-code development with Microsoft Power Platform , it becomes essential to manage and govern apps, flows, and dat...