·
4 min read

Customizing the Bank reconciliation report to filter currency revaluation transactions

Hello,

When the Foreign currency revaluation process is run, Dynamics AX adjusts the amounts in the General Ledger accounts to recognize possible differences between the exchange rate for the original transaction currency, and the exchange rate that is used during the conversion to the accounting currency.

As per the current design, Bank accounts are not adjusted, this process only adjusts the General Ledger accounts. As a consequence, differences due to these adjustments may appear in the Bank reconciliation report and complicate the task of the end user.

For Dynamics AX 2009, there is a simple way to filter the currency revaluation transactions.

In this version, the Bank reconciliation report is based on the information contained in the LEDGERTRANS table. In this table we have a field named TRANSTYPE that can be used to filter the currency revaluation transactions.

So, let’s modify the query BankLedgerTransReconciliation in the AOT to filter these transactions out (TRANSTYPE = 9 – ExchAdjustment):

  1. Open the AOT (Control + D)
  2. Go to Query > BankLedgerTransReconciliation
  3. Expand Data Sources > LedgerTrans, right-click Ranges > Add range
  4. Set the new range properties as follow:
    1. Name: PostingType
    2. Field: PostingType
    3. Value: !9
  5. Click Save
  6. Right-click query BankLedgerTransReconciliation > Compile

 

Of course, this won’t allow you to re-evaluate bank accounts, but will only remove differences due to foreign currency revaluation from the Bank reconciliation report.

Now let’s say you have the same requirement for Dynamics AX 2012. In this case the solution is not that straightforward. The BankLedgerTransReconciliation query still exists but it is not used by the report anymore. Also, the LEDGERTRANS table has been deprecated.

Using Trace Parser you will see that the transactions we need to filter out are inserted by the following code: BankReport_LedgerReconciliation::insertNonSourceDocumentSummary.

The insertNonSourceDocumentSummary method is declared in the LedgerReconciliation class, which is extended by the BankReport_LedgerReconciliation class. You can use the Type hierarchy browser Add-In from the AOT to find this out quickly. This method is declared as Private (see Method modifiers for more information), so you need to change it to Protected first to be able to override it:

protected void insertNonSourceDocumentSummary()

Next right-click the BankReport_LedgerReconciliation class in the AOT, select Override method > insertNonSourceDocumentSummary, and create your own version, excluding the currency revaluation transactions (in bold below):

protected void insertNonSourceDocumentSummary()
{
    GeneralJournalEntry                 generalJournalEntry;
    GeneralJournalAccountEntry          generalJournalAccountEntry;
    SubledgerJournalAccountEntry        subledgerJournalAccountEntry;
    FiscalCalendarPeriod                fiscalCalendarPeriod;
    DimensionAttributeValueCombination  davc;
    MainAccount                         mainAccount;

    insert_recordset ledgerReconciliationLedgerTmpSummary
    (
        AccountingCurrencyAmount,
        MainAccount,
        MainAccountId,
        MainAccountName
    )
    select sum(AccountingCurrencyAmount) from generalJournalAccountEntry
        group by davc.MainAccount,
            mainAccount.MainAccountId,
            mainAccount.Name
        where generalJournalAccountEntry.PostingType != LedgerPostingType::ExchRateGain
            && generalJournalAccountEntry.PostingType != LedgerPostingType::ExchRateLoss
    join generalJournalEntry
        where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry
            && generalJournalEntry.Ledger == Ledger::current()
            && generalJournalEntry.AccountingDate >= fromDate
            && generalJournalEntry.AccountingDate <= toDate
            && generalJournalEntry.SubledgerVoucher >= fromVoucher
            && generalJournalEntry.SubledgerVoucher <= toVoucher
            && generalJournalEntry.SubledgerVoucherDataAreaId == curext()
            && generalJournalEntry.SubledgerVoucherDataAreaId
    join fiscalCalendarPeriod
        where fiscalCalendarPeriod.RecId == generalJournalEntry.FiscalCalendarPeriod
            && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating
    join MainAccount from davc
        where davc.RecId == generalJournalAccountEntry.LedgerDimension
    join MainAccountId, Name from mainAccount
            where mainAccount.RecId == davc.MainAccount
    exists join LedgerReconcileAccountsTmp
        where LedgerReconcileAccountsTmp.MainAccount == davc.MainAccount
    notExists join subledgerJournalAccountEntry
            where subledgerJournalAccountEntry.GeneralJournalAccountEntry == generalJournalAccountEntry.RecId;
}

In this version, the Bank reconciliation report will use the information contained in the GENERALJOURNALACCOUNTENTRY table among others. In this table we have a field named POSTINGTYPE that can be used to filter the currency revaluation transactions.

 

I hope this is helpful!

Bertrand

 

Notice:

“Microsoft provides programming examples for illustration only, without warranty expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This mail message assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.” This is not an officially tested solution by Microsoft and should be fully tested before implementation. 

Bertrand Desmarest

Related posts