Hiding Open Bank Documents from the Bank Reconciliation Worksheet
The Bank Reconciliation Worksheet is used to match transactions from a previously imported bank statement, with bank document transactions received or posted in Dynamics AX 2012. The documents that appear on this form cannot be modified and are available only for the purpose of reconciling.
In the event of a bank error, there may be two bank statement lines listed in the imported bank statement and the lines must be reconciled. This can be achieved using Matching rules with “Clear reversal statement lines” actions as explained here.
In an example where a Dynamics AX user posted a Vendor payment journal by mistake. The only way to revert the transaction is to post another Vendor payment journal, with the amount reversed. The problem is that these transactions can never be reconciled and will appear forever in the Bank Reconciliation Worksheet, making the reconciliation process more complicated over the time.
Technically, the transactions appear because ISRECONCILED = 0 in the BANKDOCUMENTTABLE. So, updating this field would remove the transactions from the window.
To be able to identify the transactions that do not need to be reconciled you may have to look for them in the BANKACCOUNTTRANS table first, and then update the BANKDOCUMENTTABLE based on the corresponding SOURCERECID.
For example (values are just for illustration):
select recid, * from BANKACCOUNTTRANS
select recid, * from BANKDOCUMENTTABLE where SOURCERECID in (35637200327, 35637200328)
update BANKDOCUMENTTABLE set ISRECONCILED = 1 where recid in (5637144883, 5637144884)
Make sure to test this properly before updating the production environment.
If you don’t feel comfortable with the idea of updating the database directly, which is perfectly understandable and actually a good thing, there is an alternative. Since you can match one statement line (Bank side) to many bank documents (AX side), you can mark the documents when reconciling any statement line:
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.
