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):
- Open the AOT (Control + D)
- Go to Query > BankLedgerTransReconciliation
- Expand Data Sources > LedgerTrans, right-click Ranges > Add range
- Set the new range properties as follow:
- Name: PostingType
- Field: PostingType
- Value: !9
- Click Save
- 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.