Import custom bank statement files

Cash and bank management / Bank statements / Import statement
New field should be added to BankStatementFormat table (and form) to identify our specific format. It could be enum (if you have several custom formats) or just checkbox like in our case:
\Data Dictionary\Tables\BankStatementFormat\Fields\VKIsG4S

\Classes\BankStatementFileImport\construct
public static BankStatementFileImport construct(BankStatementFormat _importFormat)
{
    BankStatementFileImport fileImport;

    // MOD BEGIN
    if (_importFormat.VKIsG4S)
    {
        fileImport = new VKBankStatementFileImportG4S();
    }
    else
    {
        fileImport = new BankStatementFileImport();
    }
    // MOD END

    fileImport.parmImportFormat(_importFormat);

    return fileImport;
}
Main import VKBankStatementFileImportG4S class:
class VKBankStatementFileImportG4S extends BankStatementFileImport
{
    BankStmtISODocument         bankStmtISODocument;
    BankStmtISOAccountStatement accountStatement;
    List                        transList;
    UtcDateTime                 fromDateTime;
}
protected utcDateTime convertDate(str _date, str _format)
{
    utcDateTime                         ret;
    System.Globalization.CultureInfo    culture = System.Globalization.CultureInfo::get_InvariantCulture();

    try
    {
        ret = System.DateTime::ParseExact(_date, _format, culture);
    }
    catch
    {
        // do nothing
    }

    return ret;
}
protected void doImport()
{
    // standard classes use AIF, so we need to at least initialize importResults
    importResults = new AifImportResults();

    this.readFile();
    this.processData();
}
protected  BankStmtISODocument getImportBankDocument()
{
    if (!bankStmtISODocument)
    {
        // An error occurred while importing data.
        throw error("@SYS96977");
    }

    return bankStmtISODocument;
}
protected void importHeader()
{
    BankStmtISOGroupHeader  bankStmtISOGroupHeader;
    BankStmtISOCashBalance  cashBalance;

    accountStatement.clear();
    bankStmtISODocument.clear();
    bankStmtISOGroupHeader.clear();

    bankStmtISOGroupHeader.initValue();
    bankStmtISOGroupHeader.MessageIdentification    = NumberSeq::newGetNum(BankParameters::numRefBankStatementDownloadId(), true).num();
    bankStmtISOGroupHeader.CreationDateTime         = DateTimeUtil::getSystemDateTime();
    bankStmtISOGroupHeader.insert();

    bankStmtISODocument.initValue();
    bankStmtISODocument.FormatName          = importFormat.FormatId;
    bankStmtISODocument.GroupHeader         = bankStmtISOGroupHeader.RecId;
    bankStmtISODocument.insert();

    accountStatement.initValue();
    accountStatement.BankStmtISODocument    = bankStmtISODocument.RecId;
    accountStatement.Identification         = NumberSeq::newGetNum(BankParameters::numRefBankStatementId(), true).num();
    accountStatement.AccountCurrency        = CompanyInfo::standardCurrency();
    accountStatement.BankStatementType      = BankStatementType::Reconciliation;
    accountStatement.TotalEntriesNumberOfEntries = transList.elements();
    if (fromDateTime)
    {
        accountStatement.FromDateTime       = fromDateTime;
    }
    accountStatement.insert();

    cashBalance.clear();
    cashBalance.initValue();
    cashBalance.BankStmtISOAccountStatement = accountStatement.RecId;
    cashBalance.BankStatementBalanceType    = BankStatementBalanceType::Opening;
    cashBalance.insert();

    cashBalance.clear();
    cashBalance.initValue();
    cashBalance.BankStmtISOAccountStatement = accountStatement.RecId;
    cashBalance.BankStatementBalanceType    = BankStatementBalanceType::Closing;
    cashBalance.insert();

    cashBalance.clear();
    cashBalance.initValue();
    cashBalance.BankStmtISOAccountStatement = accountStatement.RecId;
    cashBalance.BankStatementBalanceType    = BankStatementBalanceType::NetAmount;
    cashBalance.insert();
}
public void new()
{
    transList = new List(Types::Class);
    super();
}
protected void processData()
{
    ListIterator                li;
    VKStatementTransContract    contract;
    BankStmtISOReportEntry      reportEntry;

    if (transList.elements())
    {
        this.importHeader();

        li = new ListIterator(transList);
        while (li.more())
        {
            contract = li.value();

            reportEntry.clear();
            reportEntry.initValue();
            if (DebitCredit::Credit == contract.parmDebitCredit())
            {
                reportEntry.editCreditAmount(true, contract.parmAmount());
            }
            else
            {
                reportEntry.editDebitAmount(true, contract.parmAmount());
            }
            reportEntry.BankStmtISOAccountStatement = accountStatement.RecId;
            reportEntry.BookingDateTime             = contract.parmBookingDateTime();
            reportEntry.AdditionalEntryInformation  = contract.parmDescription();
            reportEntry.ReferenceNumber             = contract.parmReferenceNumber();
            reportEntry.insert();

            li.next();
        }
    }
    else
    {
        throw error("Transactions not found.");
    }
}
protected void readFile()
{
    #File
    #define.Credits('CR')
    #define.Debits('DR')
    #define.FromDate('From Date:')
    #define.DateCredited('Date Credited')
    #define.DateDebited('Date Debited')

    Io              io;
    container       recordCon;
    str             recordType;
    date            transDate;
    str             fromDateStr = '',
                    amountStr,
                    transactionType;
    boolean         transactionBegin = false;
    int             fromDateLen = strLen(#FromDate);

    VKStatementTransContract    contract;
    BankStmtISOMax500Text       description;
    BankStmtISOMax500Text       referenceNumber;
    Amount                      amount;
    BankStatementBookingDate    bookingDateTime;

    io = new CommaTextIo(initialFile, #IO_Read);

    if(!io || io.status() != IO_Status::Ok)
    {
        throw error("@SYS19358");
    }

    while (io.status() == IO_Status::Ok)
    {
        recordCon = io.read();
        if (recordCon)
        {
            transactionType = '';
            recordType      = conPeek(recordCon, 2);
            if (conLen(recordCon) > 26)
            {
                transactionType = conPeek(recordCon, 7);
                if (transactionType && transactionType != #Credits && transactionType != #Debits)
                {
                    transactionType = '';
                }
            }
            if (!recordType)
            {
                transactionBegin = false;
                continue;
            }

            if (recordType == #DateCredited || recordType == #DateDebited)
            {
                transactionBegin = true;
                continue;
            }

            if (!fromDateStr && #FromDate == subStr(recordType, 0, fromDateLen))
            {
                fromDateStr = subStr(recordType, fromDateLen + 1, strLen(recordType) - fromDateLen);
                fromDateStr = strLTrim(fromDateStr);
                fromDateTime= this.convertDate(fromDateStr, 'dd MMM yyyy');
                if (!fromDateTime)
                {
                    fromDateTime= this.convertDate(fromDateStr, 'd MMM yyyy');
                }

                continue;
            }

            if (transactionType)
            {
                transDate       = str2DateDMY(strReplace(recordType, '/', ''));
                description     = conPeek(recordCon, 4);
                referenceNumber = conPeek(recordCon, 9);
                amountStr       = conPeek(recordCon, 26);
                bookingDateTime = DateTimeUtil::newDateTime(transDate, 0, DateTimeUtil::getCompanyTimeZone());

                amountStr       = strRem(amountStr, '£,');
                amount          = str2num(amountStr);

                contract = VKStatementTransContract::construct();
                contract.parmAmount(amount);
                contract.parmBookingDateTime(bookingDateTime);
                contract.parmDebitCredit((transactionType == #Credits) ? DebitCredit::Credit : DebitCredit::Debit);
                contract.parmDescription(description);
                contract.parmReferenceNumber(referenceNumber);

                transList.addEnd(contract);
            }
        }
    }
}
Contract class for banks statement lines:
class VKStatementTransContract
{
    DebitCredit                 debitCredit;
    Amount                      amount;
    BankStatementBookingDate    bookingDateTime;
    BankStmtISOMax500Text       referenceNumber;
    BankStmtISOMax500Text       description;
}
public Amount parmAmount(Amount _amount = 0)
{
    if (!prmisDefault(_amount))
    {
        amount = _amount;
    }
    return amount;
}
public BankStatementBookingDate parmBookingDateTime(BankStatementBookingDate _bookingDateTime = DateTimeUtil::minValue())
{
    if (!prmisDefault(_bookingDateTime))
    {
        bookingDateTime = _bookingDateTime;
    }
    return bookingDateTime;
}
public DebitCredit parmDebitCredit(DebitCredit _debitCredit = DebitCredit::Credit)
{
    if (!prmisDefault(_debitCredit))
    {
        debitCredit = _debitCredit;
    }
    return debitCredit;
}
public BankStmtISOMax500Text parmDescription(BankStmtISOMax500Text _description = '')
{
    if (!prmisDefault(_description))
    {
        description = _description;
    }
    return description;
}
public BankStmtISOMax500Text parmReferenceNumber(BankStmtISOMax500Text _referenceNumber = '')
{
    if (!prmisDefault(_referenceNumber))
    {
        referenceNumber = _referenceNumber;
    }
    return referenceNumber;
}
static public VKStatementTransContract construct()
{
    return new VKStatementTransContract();
}


 

Search

About

DaxOnline.org is free platform that allows you to quickly store and reuse snippets, notes, articles related to Dynamics AX.

Authors are allowed to set their own AdSense units and "buy me a coffee" link.
Join us.

Blog Tags