Upload Journal from csv file in AX 2012

• ClassDeclaration

public class UploadJournal_byUser extends RunBase

{

    CommaIo                     csvFile;

    Filename                    filename;

    container                   readCon;

    counter                     inserted;

    LedgerJournalACType         ledgerJournalACType;

    LedgerJournalType           ledgerJournalType;

    ledgerJournalName           ledgerJournalName;

    LedgerJournalTable          ledgerJournalTable;

    LedgerJournalTrans          ledgerJournalTrans;

    Ledgerjournalnameid         ledgerJournalNameId;

    args                        args;

    NoYesID                     NoYes;

    DialogField                 dialogFilename, dialogcheckbox, dialogJournalnameId;

    #define.CurrentVersion(2)

    #localmacro.CurrentList

        filename,

        inserted,

    #endmacro

    #localmacro.ListVersion1

        filename,

        inserted

    #endmacro

    #File

}

• Method 1:

public Object dialog()

{

    DialogRunbase       dialog = super();

    ;

    dialog.caption("Journal Upload");

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

    dialogFilename.value(filename);

    dialog.filenameLookupFilter(["All files", #AllFiles]);

    dialogJournalnameId = dialog.addFieldvalue(extendedtypestr(LedgerJournalNameid),"JV","JournalName");

    dialogJournalnameId.allowEdit(false);

    dialogcheckbox      = dialog.addFieldValue(enumstr(NoYes),"","Reversing","");

    return dialog;

}

• Method-2:

public boolean getFromDialog()

{

    filename = dialogFilename.value();

    ledgerjournalnameid = dialogJournalnameId.Value();

    NoYes    = dialogcheckbox.value();

    return true;

}

• Method-3:

// <Summary>

// this logic is used to create 'JV' type Journal Voucher, Next Voucher and upload data from csv file to DAX 2012

// this is for Nominal ledgers only <as per current requirement>

// If requirement would be changed then any one can remove all comments and

// activate logic for ledgerJournalACType::* for which they want.

// <Summary>

void run()

{

    boolean                             first = true;

    ledgerJournalACType                 accType;

    TransDate                           transDate, firstOfMonth;

    //TransDate                           invoiceDate;

    //TransDate                           documentDate;

    str                                 accountType;

    LedgerAccount                       ledgerAccount;

    //VendAccount                         vendAccount;

    //CustAccount                         custAccount;

    //BankAccount                         bankAccount;

    //AssetId                             assetId;

    //ProjId                              projiD;

    //ProjCategoryCostId                  categoryId;

    CostPrice                           CostPrice;

    Qty                                 qty;

    //ProjLinePropertyId                  linePropertyiD;

    str                                 description;

    AmountCur                           amountCurDebit;

    AmountCur                           amountCurCredit;

    CurrencyCode                        currencyCode;

    str                                 costCenter;

    str                                 department;

    InvoiceId                           invoiceId;

    DocumentNum                         documentNum;

    PostingProfile                      postingprofile;

    container                           dimm;

    DimensionAttributeValueCombination  dimensionAttributeValueCombination;

    DimensionAttributeValueCombination  ledgerDimension, dimensionAttributeValueCombinationrecid;

    NumberSequenceTable                 numberSequenceTable;

    Voucher                             voucher;

    AxLedgerJournalTrans                trans = new AxLedgerJournalTrans();

    AxLedgerJournalTable                table = new AxLedgerJournalTable();

    AxLedgerJournalTable_CustVend       tableCustVend = new AxLedgerJournalTable_CustVend();

    AxLedgerJournalTrans_CustPaymJour   transCustPaym = new AxLedgerJournalTrans_CustPaymJour();

    str                                 dimensn;

    Struct                              struct = new Struct();

    container                           conledgerDimension;

    DimensionDefault                    dimensionDefault;

    ExchRate                            exchRate;

    //LedgerJournalTrans_Project          LedgerJournalTransProject;

    //LedgerJournalTrans_Asset            ledgerJournalTransAssets;

    //assetTable                          assetTable;

    //AssetBook                           assetBook;

    str                                 chequeNo;

    ledgerJournalEngine                 ledgerJournalEngine;

    ;

    csvFile = new CommaIO(filename, 'r');

    if (LedgerJournalName::find(ledgerjournalnameid).JournalType != ledgerJournalType::Daily)

    {

        table = tableCustVend;

        trans = transCustPaym;

    }

    try

    {

        if (csvFile)

        {

            ttsbegin;

            if(first)

            {

                table.parmJournalName(LedgerJournalNameId);

                if(noyes == noyes::Yes)

                {

                    table.parmReverseEntry(noyes::Yes);

                    table.parmReverseDate(firstOfMonth);

                }

                table.save();

                first = false;

            }

            numberSequenceTable = NumberSequenceTable::find(LedgerJournalName::find(LedgerJournalNameId).NumberSequenceTable);

            voucher =  NumberSeq::newGetVoucherFromCode(numberSequenceTable.NumberSequence).voucher();

            while (csvFile.status() == IO_Status::OK)

            {

                ledgerAccount = '';

                //vendAccount = '';

                //custAccount = '';

                //bankAccount = '';

                amountCurDebit = 0;

                amountCurCredit = 0;

                readCon = csvFile.read();

                if (readCon)

                {

                    accType = conPeek(readCon,1);

                    if (accType == ledgerJournalACType::Ledger)

                    {

                        transdate = conPeek(readCon,2);

                        ledgerAccount = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = conPeek(readCon,5);

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        costCenter = conPeek(readcon,8);

                        department = conPeek(readcon,9);

                        documentNum = conPeek(readCon,12);

                        if (costcenter != "" && department != "")

                        {

                            struct.add('Department', department);

                            struct.add('costcenter', costcenter);

                            conledgerDimension += struct.fields();

                            conledgerDimension += struct.fieldName(1);

                            conledgerDimension += struct.valueIndex(1);

                            conledgerDimension += struct.fieldName(2);

                            conledgerDimension += struct.valueIndex(2);

                            dimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(conledgerDimension);

                            select dimensionAttributeValueCombinationrecid

                                where dimensionAttributeValueCombinationrecid.RecId == DimensionDefault;

                            dimensn = strFmt("%1-%2-%3,%1,2,department,%2,costcenter,%3",ledgerAccount,department,costcenter);

                            dimm = str2con(dimensn);

                            trans.parmAccountType(LedgerJournalACTypE::Ledger);

                            trans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(dimm));

                        }

                        else if(costcenter == "" && department == "")

                        {

                            dimm = [ledgeraccount,ledgeraccount,"",""];

                            trans.parmAccountType(LedgerJournalACTypE::Ledger);

                            trans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(dimm));

                        }

                        if(noyes == noyes::Yes)

                            firstOfMonth = nextMth(DateStartMth(TransDate));

                        else

                            firstOfMonth = datenull();

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        trans.parmExchRate(Currency::exchRate(currencyCode,transDate));

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit);

                        trans.parmInvoice(invoiceId);

                        trans.parmDocumentNum(documentNum);

                        if(noyes == noyes::Yes)

                        {

                            trans.parmReverseEntry(NoYes::Yes);

                            trans.parmReverseDate(firstOfMonth);

                        }

                        trans.currentRecord().insert();

                        inserted++;

                        info(strFmt("%1 inserted",inserted));

                    }

                    /*else if (accType == ledgerJournalACType::Cust)

                    {

                        trans.parmAccountType(LedgerJournalACTypE::Cust);

                        transdate = conPeek(readCon,2);

                        custAccount = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = conPeek(readCon,5);

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        invoiceId = conPeek(readCon,10);

                        invoiceDate = conPeek(readCon,11);

                        postingprofile = conPeek(readcon,14);

                        exchRate = conPeek(readcon,15);

                        trans.parmLedgerDimension(DimensionStorage::getDynamicAccount(custAccount,acctype));

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        trans.parmExchRate(exchrate);

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit) ;

                        trans.parmInvoice(invoiceId);

                        trans.parmDue(invoiceDate);

                        trans.parmPostingProfile(postingProfile);

                        trans.parmApprover(HcmWorker::userId2Worker('NNourrice'));

                        trans.parmApproved(NoYes::Yes);

                        trans.currentRecord().insert();

                        inserted++;

                        info(strFmt("%1 inserted",inserted));

                    }*/

                    /*else if (accType == ledgerJournalACType::Vend)

                    {

                        trans.parmAccountType(LedgerJournalACTypE::Vend);

                        transdate = conPeek(readCon,2);

                        vendAccount = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = conPeek(readCon,5);

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        invoiceId = conPeek(readCon,10);

                        postingprofile = conPeek(readcon,14);

                        exchRate = conPeek(readcon,15);

                        trans.parmLedgerDimension(DimensionStorage::getDynamicAccount(vendAccount,acctype));

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        trans.parmExchRate(exchRate);

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit) ;

                        trans.parmInvoice(invoiceId);

                        trans.parmDue(invoiceDate);

                        trans.parmPostingProfile(postingProfile);

                        trans.parmApprover(HcmWorker::userId2Worker('NNourrice'));

                        trans.parmApproved(NoYes::Yes);

                        trans.currentRecord().insert();

                        inserted++;

                        info(strFmt("%1 inserted",inserted));

                    }*/

                    /*else if (accType == ledgerJournalACType::Bank)

                    {

                        trans.parmAccountType(LedgerJournalACType::Bank);

                        transdate = conPeek(readCon,2);

                        bankAccount = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = conPeek(readCon,5);

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        documentNum = conPeek(readCon,12);

                        trans.parmLedgerDimension(DimensionStorage::getDynamicAccount(bankAccount,acctype));

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        trans.parmExchRate(Currency::exchRate(currencyCode,transDate));

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit);

                        trans.parmDocumentNum(documentNum);

                        trans.currentRecord().insert();

                        inserted++;

                        info(strFmt("%1 inserted",inserted));

                    }*/

                    /*else if (accType == ledgerJournalACType::Project)

                    {

                        trans.parmAccountType(LedgerJournalACTypE::Project);

                        transdate = conPeek(readCon,2);

                        projiD = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = 'SCR';

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        if (amountCurDebit && !amountCurCredit)

                        {

                            CostPrice = amountCurDebit;

                            qty = 1;

                        }

                        else

                        {

                            CostPrice = amountCurCredit;

                            qty = -1;

                        }

                        categoryId = 'Expense';

                        linePropertyiD = 'No charge';

                        trans.parmLedgerDimension(DimensionStorage::getDynamicAccount(projiD,acctype));

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        Currency::exchRate(currencyCode,transDate);

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit) ;

                        trans.currentRecord().insert();

                        LedgerJournalTransProject.clear();

                        LedgerJournalTransProject.ProjId         = projId;

                        LedgerJournalTransProject.CategoryId     = categoryid;

                        LedgerJournalTransProject.LinePropertyId = linePropertyiD;

                        LedgerJournalTransProject.Qty = qty;

                        LedgerJournalTransProject.CostPrice = CostPrice;

                        LedgerJournalTransProject.RefRecId = trans.currentRecord().RecId;

                        LedgerJournalTransProject.SalesCurrencyId = currencyCode;

                        LedgerJournalTransProject.insert();

                        inserted++;

                        info(strFmt("%1 inserted",inserted));

                    }*/

                    /*else if (accType == ledgerJournalACType::FixedAssets)

                    {

                        ledgerAccount = '';

                        trans.parmAccountType(LedgerJournalACTypE::FixedAssets);

                        transdate = conPeek(readCon,2);

                        assetid = conPeek(readCon,3);

                        description = conPeek(readcon,4);

                        currencycode = conPeek(readCon,5);

                        amountCurDebit = conPeek(readCon,6);

                        amountCurCredit = conPeek(readCon,7);

                        ledgerAccount = conPeek(readCon,8);

                        dimm = [ledgeraccount,ledgeraccount,"",""];

                        trans.parmAccountType(LedgerJournalACTypE::Ledger);

                        trans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(dimm));

                        trans.parmLedgerDimension(DimensionStorage::getDynamicAccount(assetid,acctype));

                        trans.parmJournalNum(table.ledgerJournalTable().JournalNum);

                        trans.parmVoucher(voucher);

                        trans.parmCurrencyCode(currencyCode);

                        trans.parmExchRate(Currency::exchRate(currencyCode,transDate));

                        trans.parmTxt(strLRTrim(description));

                        trans.parmTransDate(TransDate);

                        trans.parmAmountCurDebit(amountCurDebit);

                        trans.parmAmountCurCredit(amountCurCredit) ;

                        trans.parmPostingProfile(AssetParameters::find().PostingProfile);

                        trans.parmCompany(curext());

                        trans.parmOffsetCompany(curext());

                        trans.currentRecord().insert();

                        assetTable=assetTable::find(assetid);

                        select assetBook where assetBook.AssetId == assetid;

                        ledgerJournalTransAssets.clear();

                        ledgerJournalTransAssets.BookId         = assetBook.BookId;

                        ledgerJournalTransAssets.TransType      = AssetTransTypeJournal::DepreciationAdj;

                        ledgerJournalTransAssets.AssetId        = assetid;

                        ledgerJournalTransAssets.Company        = curext();

                        ledgerJournalTransAssets.RefRecId       = trans.currentRecord().RecId;

                        ledgerJournalTransAssets.insert();

                        inserted++;

                    }*/

                }

            }

            ttsCommit;

        }

    }

    catch(Exception::Error)

    {

        info(strFmt("%1 %2",Exception::Error,inserted));

    }

    args = new Args();

    args.record(LedgerJournalTable::find(table.ledgerJournalTable().JournalNum ));

    new MenuFunction(MenuItemDisplayStr(LedgerJournalTable),MenuItemType::Display).run(args);

}

• Method-4:

static void main(Args  args)

{

    UploadJournal_byUser        uploadJournal;

    ;

    uploadJournal =  new UploadJournal_byUser();

    if (uploadJournal.prompt())

    {

        uploadJournal.run();

    }

}