Import ledger, AP and AR invoice journal lines

Recently, I came across a requirement in which users want to import the ledger, AP and AR invoice journal lines from a pre-defined excel format in general journal AX2012R2. So I developed a functionality hope that helps you

Below snap shot shows the flat list of all objects in this project.

t1

 Below snap shot shows the UI.

t2

Below is the list of all functions created in a class.

t3

class SLSalesPurchLedgerJourLinesImport extends RunBase
{
    int                                 row;
    int                                 col;
    LedgerJournalId                     journalNum;
    DialogRunbase                       dialog;
    DialogGroup                         dialogGrp;
    FilePath                            filePath;
    DialogField                         dialogFieldsalesPurchLedger,
                                        dialogFieldFilePath;
    SLSalesPurLedger                    salesPurLedger;

    SysExcelApplication                 application;
    SysExcelWorkbooks                   workbooks;
    SysExcelWorkbook                    workbook;
    SysExcelWorksheets                  worksheets;
    SysExcelWorksheet                   worksheet;
    SysExcelCells                       cells;
    COMVariantType                      type;
    str                                 mainAccount;
    LedgerJournalTrans                  ledgerJournalTrans;
    LedgerJournalACType                 accountType;
    LedgerDimensionAccount              ledgerDim;
    DimensionStorage                    dimensionStorage;
    DimensionServiceProvider            dimensionServiceProvider;
    LedgerAccountContract               LedgerAccountContract;
    DimensionAttributeValueContract     ValueContract;
    List                                ListValueContract;

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        filePath,
        salesPurLedger
    #ENDMACRO
}

private void clearList(List _list)
{
    ListIterator iterator;
    str          value;

    iterator = new ListIterator(_list);

    while (iterator.more())
    {
        value = iterator.value();

        if (true) // check value
        {
            iterator.delete();
        }
        else
        {
            iterator.next();
        }
    }
}

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
    switch (_cv.variantType())
    {
        case (COMVariantType::VT_BSTR):
        return _cv.bStr();

        case (COMVariantType::VT_R4):
        return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_R8):
        return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DECIMAL):
        return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DATE):
        return date2str(_cv.date(),123,2,1,2,1,4);

        case (COMVariantType::VT_EMPTY):
        return "";

        default:
        throw error(strfmt("@SYS26908", _cv.variantType()));
    }
    return "";
}

private void createCustInvoiceJourLines()
{
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Cust)
            {
                throw error('The sheet does not only contain the customer invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,18).value()));

            ledgerDim = 0;
            ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,7).value()), LedgerJournalACType::Cust);
            ledgerJournalTrans.LedgerDimension = ledgerDim;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = 18;
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;

                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }

                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Import Failed.');
    }
}

private void createLedgerInvoiceJourLines()
{
    int     dynamicCol;
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row, 6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Ledger)
            {
                throw error('The sheet does not contain only the ledger invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row, 18).value()));

            ledgerDim = 0;

            col = 6;
            mainAccount = '';
            if(!ListValueContract.empty())
                this.clearList(ListValueContract);
            do
            {
                col ++;

                if(cells.item(row, col).value().bStr() == '')
                {
                    continue;
                }

                ValueContract = new DimensionAttributeValueContract();
                ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                ListValueContract.addEnd(ValueContract);

                mainAccount = this.COMVariant2Str(cells.item(1 , col + 1).value());
            }
            while (mainAccount != 'MainAccount');

            dynamicCol = col + 9;
            ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
            LedgerAccountContract.parmValues(ListValueContract);

            dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
            ledgerJournalTrans.LedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,18).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,18).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = dynamicCol;
                mainAccount = '';
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;
                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }
                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row + 1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Import Failed.');
    }
}

private void createVendInvoiceJourLines()
{
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Vend)
            {
                throw error('The sheet does not contain only the vendor invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,18).value()));

            ledgerDim = 0;

            ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,7).value()), LedgerJournalACType::Vend);
            ledgerJournalTrans.LedgerDimension = ledgerDim;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = 18;
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;

                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }

                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Upload Failed.');
    }
}

public Object dialog()
{
    FormBuildControl    setupGroupControl;
    FormRun             formRun;
    #Excel
    dialog = super();

    dialog.alwaysOnTop(true);
    dialog.windowType(FormWindowType::Standard);
    dialogGrp = dialog.addGroup('Import journal lines');
    dialogFieldFilePath = dialog.addFieldValue(extendedTypeStr(FilenameOpen), filePath,  "File path",    "");
    dialog.filenameLookupFilter(["@SYS28576",#XLSX]);
    dialog.filenameLookupTitle("Upload from EXCEL");

    dialogFieldsalesPurchLedger = dialog.addFieldValue(extendedTypeStr(SLSalesPurLedger), salesPurLedger,  "Account Type",    "");

    return dialog;

}

private Num generateVoucherNum()
{
    LedgerJournalName   ledgerJournalName;
    NumberSeq           numberseq;
    select firstOnly ledgerJournalName
        where ledgerJournalName.JournalType == LedgerJournalType::Daily;

    return new JournalVoucherNum(JournalTableData::newTable(LedgerJournalTable::find(journalNum))).getNew(true);

}

public boolean getFromDialog()
{
    boolean ret;

    ret = super();

    salesPurLedger = dialogFieldsalesPurchLedger.value();
    filePath       = dialogFieldFilePath.value();

    return ret;
}

public boolean init()
{
    boolean ret;

    ret = super();

    row                             =    1;
    application                     =    SysExcelApplication::construct();
    workbooks                       =    application.workbooks();

    return ret;
}
private void initilizeExcel()
{
    try
    {
        workbooks.open(filePath);
    }
    catch (Exception::Error)
    {
        throw error('File cannot be opened.');
    }

    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();
}

public void new()
{
    super();

    dimensionServiceProvider = new DimensionServiceProvider();
    LedgerAccountContract    = new LedgerAccountContract();
    ListValueContract        = new List(Types::Class);
}

container pack()
{
    return [#CurrentVersion,#CurrentList];
}

public LedgerJournalId parmJournalNum(LedgerJournalId _journalNum = journalNum)
{
    journalNum = _journalNum;

    return journalNum;
}

public void run()
{
    super();

    switch (salesPurLedger)
    {
        case SLSalesPurchLedger::Customer :
            this.createCustInvoiceJourLines();
            break;

        case SLSalesPurchLedger::Vendor :
            this.createVendInvoiceJourLines();
            break;

        case SLSalesPurchLedger::Ledger :
            this.createLedgerInvoiceJourLines();
            break;
    }

}

boolean unpack(container _packedClass)
{
    Integer     version = conpeek(_packedClass,1);

    switch (version)
    {
        case #CurrentVersion:
            [version,#CurrentList] = _packedClass;
            break;
        default :
            return false;
    }

    return true;
}

public static void main(Args args)
{
    SLSalesPurchLedgerJourLinesImport   salesPurchLedgerJourLinesImport;
    LedgerJournalTable                  ledgerJournalTable;

    if (args && args.record())
    {
        ledgerJournalTable = args.record();
        salesPurchLedgerJourLinesImport = new SLSalesPurchLedgerJourLinesImport();
        salesPurchLedgerJourLinesImport.init();
        salesPurchLedgerJourLinesImport.parmJournalNum(ledgerJournalTable.JournalNum);
        if(salesPurchLedgerJourLinesImport.prompt())
        {
            salesPurchLedgerJourLinesImport.run();
        }
    }
    else
    {
        throw error('arguments are missing');
    }
}

t4The above snap shot method must be written to the import button click event.

Now I’m going to show you the predefined excel templates these templates can accommodate up to 10 ledger dimensions for the account type ledger. Make sure that when you create the excel template the data is defined on the correct alphabetical columns and the dimension column names must be the same as the dimensions you created in the system and they should be in reverse order than you enter it in segmented entry field in the system as below.

 Customer Template

t5

Ledger Template

t6

Vendor Template

t7

 

About the author

: Taimoor is a senior software engineer in Systems Limited. He has a vast experience working with Dynamics AX2012R2, AX2009 and AX4.0. He does things like AX development and customization, client support, upgrading, integration, installation and configuration, debugging and performance enhancement. He is also experienced in working with different clients based in different regions. Other than this, he likes to play and watch cricket and hang out.

COMMENTS (9)
Reply

Hi,
After implementing the code I receive various error Messages.
I am not sure if it is because of the Import Excel-file or because of a code error.
Would it be possible that you sent me the templates and the xpo to my email?
Thanks,
Ludwig

    Hello!
    Thank you for leaving a comment.
    Unfortunately, the xpo file and templates are proprietary in nature and we cannot share them with you. However, if you like, we can help you with your implementation needs. Please let me know a few more details about your project? Thanks!

Reply

Ludwig, you can share the error messages you are getting, hope we will help you out. Thanks.

Reply

I like it but there is an important think missing.
Sales Tax/VAT

Reply

Thank you Carsten.

Reply

I would to appricate such great effort to support the other as usual ‚ very helpful and detailed article as well ‚ we are waiting more ‚ thank you .

Hisham

That is great job and work, congratulations aimoor Hussain. You have a new perspective to create invoice lines, specially in OffsetLedgerDimension field, well thats depends a requeriments bussines, for me calling LedgerjournalTrans.paymModeModified() method do all standar bussines rules for set value into OffsetLedgerDimension field.

Reply

Thanks Ismail.

Reply

Hi Taimoor Hussain

Can you share the complete template files with me.

Thanks in advance

Leave a Comment

Name (required)
Email (required)
Comment (required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>