Bank Reconciliation in Oracle Apps R12: Cash Management

Quick Answer: Bank reconciliation in Oracle Apps R12 runs through Oracle Cash Management in three stages. Load the statement (Bank Statement Loader for BAI2, SWIFT940 or CAMT files, or enter it by hand), reconcile it (the AutoReconciliation program matches lines to Payables, Receivables, cashflow and journal transactions within your tolerances), then clear what is left manually in the Bank Statement Lines window. Finish by transferring accounting from Payables and Receivables to General Ledger and running the GL Reconciliation Report.

Bank reconciliation in Oracle E-Business Suite R12 is a Cash Management task: a bank statement is brought into the system, each statement line is matched to a payment, receipt, cashflow or journal, matched transactions are cleared, and the leftovers are dealt with by hand. The steps below follow Oracle's Cash Management User Guide for Release 12.2; the R12.1 guide describes the same programs and windows.

How is reconciliation organized in R12 Cash Management?

Oracle splits the work into matching and clearing. Matching ties a statement line to a transaction using a transaction number (a check or deposit number, for example), the bank account, the amount and the currency. Clearing then marks the matched Payables payments, Receivables receipts, cashflows, miscellaneous transactions and open-interface transactions as cleared. General Ledger journal entries and Payroll payments are matched but not cleared.

The end-to-end cycle, in the order Oracle documents it:

1. Set up the bank account's reconciliation options, tolerances and bank transaction codes.
2. Load or enter the bank statement.
3. Import it into Cash Management tables (validated by Bank Statement Import).
4. Run AutoReconciliation.
5. Review errors and reconcile the remaining lines manually.
6. Optionally mark the statement complete.
7. Transfer accounting to General Ledger and post.
8. Run the GL Reconciliation Report.

If you are on Oracle Fusion Cloud Financials rather than E-Business Suite, treat this as background only. Fusion is a separate application with its own screens and rule setup, so use its own guide in the Oracle Help Center.

How do bank statements get into Oracle R12?

Bank Statement Loader

When your bank supplies a flat file, the Bank Statement Open Interface is the intake. Oracle says loading BAI2, SWIFT940 and CAMT files is "mostly automated" because Cash Management ships SQLLoader scripts and mapping templates for them; you adjust the default mapping to match your bank's variations of the standard. Any other layout needs its own SQLLoader script and a mapping template, which is the route for a bank that only offers CSV.

To run it, open the Requests window and submit the Bank Statement Loader program, then pick a process option:

• Load: fills the interface tables only, so you can correct data before import.
• Load and Import: starts Bank Statement Import once the load succeeds.
• Load, Import, and AutoReconciliation: also launches AutoReconciliation.

The parameters include the data file name, an optional bank branch name and bank account number (leave both blank to load the whole file), a GL Date for the import options, and Receivable Activity, Payment Method and NSF Handling for the full option. If the loader ends with errors or warnings, the request stops and the Bank Statement Loader Execution Report lists the exceptions.

Fixing interface errors

Bank Statement Import validates what the loader placed in CE_STATEMENT_HEADERS_INT and the lines table. Rejected statements stay in the interface, where the Bank Statement Interface window shows the header and line errors so you can correct and resubmit them. XML statements can also arrive through XML Gateway in IFX format, which populates the same tables and launches the import.

Manual entry

Small accounts with a printed or PDF statement can be keyed in. Open the Find Bank Statements window, find or create the statement header, choose Review, then Lines. Each line takes a line number, a transaction type, the amounts (transaction date, value date, amount), optional exchange details, a reference (agent, invoice number, agent bank account) and a description. If you want AutoReconciliation to pick the lines up later, enter the Bank Transaction Code and a transaction number on each one.

What does AutoReconciliation actually match?

Submit the AutoReconciliation program from Submit Request, or use the Oracle Applications Tools menu while you are in the Reconcile Bank Statements window or the Bank Statement Interface window. Required inputs are the Bank Branch and the GL Date; you can narrow by bank account, statement number range and statement date range, choose an organization, and decide how NSF or rejected receipts are handled (No Action, Reverse, or Debit Memo Reverse).

For Payables, Oracle's guide describes this match order for individual payments: payment number first, then invoice number with supplier bank account, then invoice number with supplier name. Payment batches such as EFT runs match on the Payment Instruction ID. In every case the amount must fall inside your tolerance.

How tolerances work

Tolerances are set as a percentage, an amount, or both, and Oracle uses whichever is smaller. With an amount tolerance of 70 and a percentage of 10%, a statement line of 1,000 gives a percentage figure of 100, so 70 wins and the program looks for a transaction between 930 and 1,070. Differences inside tolerance on Payables payments and cashflows post to Bank Charges or Bank Errors depending on the bank account's Tolerance Differences setting; Receivables differences go to the Receivables Bank Charges account. General Ledger journals only auto-reconcile when the amounts are identical, and Payroll payments get no tolerance at all.

Review results in the AutoReconciliation Execution Report, which is produced automatically, and in the Bank Statement Detail Report, which shows reconciled and unreconciled lines. You can also open the statement, choose Lines, then Errors, to see the Reconciliation Errors window.

How do you reconcile the leftover lines manually?

Manual reconciliation has no matching restrictions, which makes it the place for split deposits, bank-initiated items and anything with a missing reference.

1. Open Find Bank Statements, query the statement and choose Find.
2. In Reconcile Bank Statements, select it and choose Review, then Lines.
3. Select a line and choose Available to search for payments, receipts, cashflows, journal entries, open-interface items or batches by party, number range, date range or amount.
4. Tick the matching transactions. Recalculate (optional) pushes any difference into the Charges and Errors fields based on your tolerance settings.
5. Reconcile, then move to the next line.
6. Create miscellaneous transactions for bank fees or interest that have no subledger entry. Oracle notes these are handled as Receivables receipts, so Receivables must be installed and set up. You can also create Payables payments or Receivables receipts from here.
7. Reverse NSF or rejected items.
8. Optionally tick Complete in the Bank Statement window to freeze the statement. You can unmark it later.

One prerequisite catches teams out: if you do not use AutoLockbox and the statement contains direct deposits or bank transfers, those receipts must exist in Receivables before Cash Management can reconcile them.

Closing the loop with General Ledger

Cash Management does not post its own journals. Accounting created during reconciliation (miscellaneous receipts, bank charges, errors) is transferred from Payables and Receivables, and then Journal Import creates the entries in General Ledger for posting. Reconciling directly to GL journals creates no extra accounting.

Run the GL Reconciliation Report last. It compares the GL cash account balance with the bank statement's closing balance and lists adjustments for unreconciled receipts, payments, journals and bank errors, in Summary or Detail format. Oracle warns it is only accurate after all statements are reconciled, subledger entries are transferred and journals are posted. It also expects the GL cash account to belong to just one bank account.

When the bank only sends PDFs

Some smaller banks and foreign subsidiaries cannot produce BAI2, SWIFT940 or CAMT. You then have three options: key the statement in manually, ask the bank for an electronic format, or build a user-defined loader. For the third, you need a consistent file first. QuickBankConvert can turn PDF statements into CSV or Excel rows with date, description, amount and running balance; your Oracle team would still need to write the SQL*Loader control file and mapping template for that layout. The file only removes the retyping. For background on the SWIFT format itself, see our MT940 to Excel guide.

Related guides

• NetSuite bank reconciliation: the same matching-and-clearing logic in a cloud ERP.
• Dynamics 365 advanced bank reconciliation: Microsoft's equivalent, with its own matching rules.
• Automate bank reconciliation: where rules-based matching saves time and where it does not.