emeadaxsupport: Customizing the Bank reconciliation report to filter currency revaluation transactions

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...valuation.aspx
==============

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 = fromVoucher
<span style="color: #333399; font-family: verdana,geneva; font-size: small;"> && generalJournalEntry.SubledgerVoucher