Advanced bank reconciliation – A practical view

Documentation https://learn.microsoft.com/en-us/dynamics365/finance/accounts-payable/import-bai2-er

UBS Camt.053 Implentation guidelines Dokumente Zahlungsverkehr | UBS Schweiz

Functional overview

Advanced bank reconciliation provides the following functionality:

• Import Camt.053 Bank statement (I could not get MT940 to work and did not test BAI2)
• Match statement to existing bank transactions based on customizable matching rules
• Create new bank transaction based on customizable matching rules
  • The offset of bank transactions is Ledger only (no customer or vendor transaction)
  • The offset account selection is linked to the statement transaction codes provided by the bank

Process

Overview

• IMPORTANT: Do not import a bank statement before the previous bank statement is reconciled and posted.
• Import (Camt.053) Bank statement
• Create Reconciliation (Manual or automatic)
• Run matching rules (Manual or automatic)
• Match remaining transaction and check automatically matched transactions
• Mark as reconciled
• Return to statement to post the new transactions

Bank account

Links to “Bank statements” (for Bank statement import and posting) and “Bank reconciliation” (for reconciliation)

Import the bank statement

IMPORTANT: Do not import a bank statement before the previous bank statement is reconciled and posted.

Bank account > Reconcile > Bank Statements

Create Reconciliation

Bank account > Reconcile > Bank statements

Depending on the setup the statement was created automatically during import of the statement or you can create it manually here

Reconciliation Worksheet

• Check result
• Match manually if needed
• When done, “Mark as reconciled”
• Note: If Transactions are missing, they might be in future dates. In this case go back to the Bank reconciliation overview and change the Cut-off date. After the matching is completed, return the date back to the Statement date.

Post statement

• Go back to bank statement
• Check Accounting date – Must be Bank Statement Date (this posting of the new transactions will occur on this date)
• Post

Customizations

The above processes did not work without some minor customization

Customization by Electronic reporting definition

ISO.053 without IBAN as Identifier

Issue: the HSBC UK CAMT.053 file provideds <Routing number> + <Account number> in the <Acct>.<Id>.<Othr> field; the standard implementation expects only <Account number> in the field. This caused the error “The bank statement %1 was not imported because bank account %2 was not identified in the import file.”

Solution: Use electronic reporting to trim the account number:
Designer for Model mapping “ABR Camt.053 format mapping”  (NOT format)

Differentiate “OTHR” Payments

Issue: Direct debits are all identified as “OTHR” Payments but need to be posted in different ways. The standard only allows differentiations by Statement transaction code which ist defined by the bank.

Solution: Using Electronic reporting add the first three characters of the EndToEndId to the Statement transaction code enabling further differentiation of the OTHR payments

Designer for Model mapping “ABR Camt.053 format mapping”  (NOT format); format/Document/BkToCstmrStmt/Stmt/Ntry/NtryDtls/TxDtls

CASE(@.BkTxCd.IsMatched,
True, IF(  @.BkTxCd.Data.Domn.IsMatched,
CONCATENATE(
@.BkTxCd.Data.Domn.Data.Cd.Str,
@.BkTxCd.Data.Domn.Data.Fmly.Cd.Str,
@.BkTxCd.Data.Domn.Data.Fmly.SubFmlyCd.Str,
IF(TEXT(@.BkTxCd.Data.Domn.Data.Fmly.SubFmlyCd.Str) = "OTHR", LEFT(@.Refs.Data.EndToEndId.Data.Str, 3), "")),
IF(@.BkTxCd.Data.Prtry.IsMatched,
@.BkTxCd.Data.Prtry.Data.Cd.Str,"")
),
IF(format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Domn.IsMatched,
CONCATENATE(
format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Domn.Data.Cd.Str,
format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Domn.Data.Fmly.Cd.Str,
format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Domn.Data.Fmly.SubFmlyCd.Str,
IF(TEXT(format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Domn.Data.Fmly.SubFmlyCd.Str) = "OTHR", LEFT(@.Refs.Data.EndToEndId.Data.Str, 3), "")),
IF(format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Prtry.IsMatched,
format.Document.BkToCstmrStmt.Stmt.Ntry.BkTxCd.Prtry.Data.Cd.Str,""))
)

Import field “AddtlNtryInf” from statement to “Description” (AdditionalEntryInfo) field

Issue: The “Description” field (AdditionalEntryInformation) is populate by the “EndToEndId” which contains too little information in the imported Bank statement. The Bank statement file contains more information in the “AddtlNtryInf” field.

Solution: Using Electronic reporting

• Change the mapping of EndToEndId: Replace with AddtlNtryInf if available (this will map to BankStmtISOReportEntry.AdditionalEntryInformation)
• Change the mapping of BankDocumentNumber: Add the original EndToEndId value here (this will map the value to BankStmtISOReportEntry.BankDocumentNumber)

Customization in code

Allow change of posting date

Issue: The standard always posts new statement transaction to today (AccountingDate is set to today() during initialization)

Solution: In Code add new edit method to allow change of AccountingDate of BankStmtISOAccountStatement

Allow change of cutoff date

Issue: Allow change of cutoff date, to include transactions that were posted after the bank statement. This is actually possible in standard, however after matching the change should be reversed to link the reconciliations to the correct statement.

Solution: In Code disable validation of CutOffDate changes on BankReconciliationHeader and create edit method to allow change even after reconciliation.