+- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com)
+-- Forum: Oracle Applications (http://www.oraerp.com/forum-39.html)
+--- Forum: Oracle Tutorials, Articles, eBooks, Tools & Tips (http://www.oraerp.com/forum-12.html)
+--- Thread: Oracle Cash Management Reconciliation of Bank Statements Automatically (/thread-71273.html)
Oracle Cash Management Reconciliation of Bank Statements Automatically - Rubab Ali - 12-20-2014
Oracle Cash Management Reconciliation of Bank Statements Automatically
You can use the Auto Reconciliation program to automatically reconcile any bank statement in Oracle Cash Management. There are three versions of the program:
· Auto Reconciliation: Use this program to reconcile any bank statement that has already been entered in Cash Management.
· Bank Statement Import: Use this program to import an electronic bank statement after loading the bank statement file into the Bank Statement Open Interface tables..
· Bank Statement Import and Auto Reconciliation: Use this program to import and reconcile a bank statement in the same run.
You can run these programs from the Submit Request window, the Reconcile Bank Statements window, or the Bank Statement Interface window. You can also import and automatically reconcile a bank statement from the Bank Statement Loader program, by using the option of Load, Import and Auto Reconciliation.
After you run the program, you can review the Auto Reconciliation Execution Report to identify any reconciliation errors that need to be corrected. This report is produced automatically, or you can run it whenever needed. You can also review reconciliation errors online.
After you automatically reconcile a bank statement and correct any reconciliation errors, you can run the Bank Statement Detail Report or use the View Bank Statements window to review reconciled and unreconciled statement lines. If lines remained unreconciled, you can:
• Update the bank statement and rerun the Auto Reconciliation program.
• Reconcile the statement lines manually.
Once you have reviewed your reconciliation results, you can transfer the accounting entries to your general ledger from Oracle Payables, Oracle Receivables, and Oracle Cash Management. If you use Oracle General Ledger, you can reconcile you General
Ledger cash account balance to your bank account balance by running the GL Reconciliation Report.
Prerequisites of the bank statement file
· The bank statement information is delivered in a flat file.
· The bank statement file has the following structure:
o One Header record
o One or more Line records that belong to the Header
o One Trailer record
· A file may contain multiple bank statements, but each bank statement must begin with a Header record, followed by a group of Line records, and optionally end with a Trailer record.
· Each record begins with an identifier indicating the record type: Header, Line, or Trailer.
· The field definitions within each record type are consistent within a file.
· Each record has no more than 35 fields.
· Each field has no more than 255 characters. Any field that exceeds this limit must be stored in COLUMN1 of the intermediate table.
Bank Statement File Formats
The Bank Statement Loader program and the Intra-Day Bank Statement Loader program can load the following three bank statement file formats:
· BAI2
· SWIFT940
· User-defined
Before you can use the Bank Statement Loader program or the Intra-Day Bank Statement Loader program, you must complete the setup required for each format that you want to load.
1- Setup for BAI2
The Bank Statement Loader program uses a predefined SQL Loader script to load BAI2 bank statement files. However, since each bank adopts the BAI2 standard slightly differently, you need to map the BAI2 format to the Bank Statement Open Interface tables. You can create the mapping information from the BAI2 mapping template.
2- Setup for SWIFT940 Format
The Bank Statement Loader program uses a predefined SQL Loader script to load SWIFT940 bank statement files. However, since each bank adopts the SWIFT940 standard slightly differently, you need to map the SWIFT940 format to the Bank Statement Open Interface tables. You can create the mapping information from the SWIFT940 mapping template.
After you load SWIFT940 bank statement files into the open interface tables, you may need to define new bank transaction codes in Cash Management. SWIFT940 transaction codes represent the type of transaction. For example, TRF represents transfers. However, SWIFT940 transaction codes do not contain information about the debit or credit nature of the transaction. Instead, the Debit/Credit Mark field is used to differentiate debit and credit entries, where D means debit and C means credit. When the Bank Statement Loader program populates the TRX_CODE column in the Bank Statement Lines Interface table, it appends the Debit/Credit Mark to the transaction code to form a new code. For example, debit transfers are identified as TRFD and credit transfers as TRFC. You must set up these new transaction codes before you can import the bank statement information.
3- Setup for User-Defined Format
If your bank uses a format other than BAI2 or SWIFT940, you need to develop a SQL Loader script and create a new mapping template for that format. You may want to consult the sample SQL Loader script created for the French EDIFACT standard, located in $CE_TOP/bin/EDIFACT.ctl. The mapping template, EDIFACT-France, is also available for your reference. The mapping rules for the French EDIFACT format are listed in EDIFACT - France Mapping Template.
Bank Statement Open Interface
If your bank provides account statements in a flat file, using a defined format like BAI2 or SWIFT940, you can use the Bank Statement Open Interface to load this information into Oracle Cash Management. You can load previous-day bank statements to reconcile against system transactions, and intra-day bank statements for cash positioning.
You can load bank statement information into the Bank Statement Open Interface tables using the Bank Statement Loader program or a custom loader program. Once you populate the open interface tables, you can run the Bank Statement Import program to validate and transfer the bank statement information into the Cash Management Bank Statement tables. If you import bank statements from bank accounts that are used by Oracle Treasury, the Bank Statement Import program also automatically updates the bank account balances seen in Oracle Treasury. After the bank statement information has been successfully transferred, you can purge the open interface tables.