Posts tagged with: Finance and Accounting

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