Bank Statement Upload and Reconciliation in Oracle Cash Management

Following are the key programs that are used to load and Import Bank Statement into Oracle.

• Bank Statement Loader
• Run SQL*Loader- BAI2
• Load Bank Statement Data
• Bank Statement Loader Execution Report
• Bank Statement Import & AutoReconciliation
• AutoReconciliation Execution Report

 

Step 1: Receive Bank Statement

In order to load the bank statement, place it in the $CE_TOP/bin/ directory and run the Bank Statement Loader program.

We used BAI2 file provided by Bank.

Step2: Load the bank statement in Oracle

1. In order to load the bank statement, place it in the $CE_TOP/bin/ directory and run the Bank Statement Loader program.
2. Run the concurrent program “Bank Statement Loader” program, important parameters for this program is explained below:

Ø Process Option – The below values can be chosen:

Load

Load and Import

Load, Import, and Auto Reconciliation

Ø Mapping Name – Choose the bank statement mapping created for the bank

Ø Data File Name – Specify the data filename

Ø Directory Path – Enter the directory path if different from $CE_TOP/bin/

Ø Bank Branch name

Ø Bank account number

Ø GL Date – This is the default date for all General Ledger journal entries created by Auto Reconciliation. The date must be in an open or future-enterable Payables or Receivables period.

Bank statement loader will spawn below requests:

o Run SQL*Loader- BAI2

o Load Bank Statement Data

o Bank Statement Loader Execution Report

o Bank Statement Import

o Bank Statement Import Execution Report

Technical Details: Table details during Load and Import

Stage 1: SQL Loader will load the contents of the bank Flat file to the temporary table CE_STMT_INT_TMP

This is a temporary table and data from the flat file is inserted into this directly without any validations. Run below query to find out the data loaded into the tables for above test case.

Select * from CE_STMT_INT_TMP;

Stage 2: Loader moves data from CE_STMT_INT_TMP to:

CE_STATEMENT_HEADERS_INT – bank statement details for importing

CE_STATEMENT_LINES_INTERFACE – bank statement line details for open interface

Use below queries for the same:

Select * from CE_STATEMENT_HEADERS_INT where bank_account_num = ‘123456789’ and statement_number = ‘140718’;

The ce_statement_headers_int stores these information related to the statement.

Note the record_status_flag which will indicate if the statement has been imported to base tables. The different values in the field indicate below:

Value Meaning

—– ————-

N     New

C      Corrected

T      Transferred

E       Error

Name	Comments
STATEMENT_NUMBER	Statement number
BANK_ACCOUNT_NUM	Bank account number
STATEMENT_DATE	Statement date
BANK_NAME	Bank name
BANK_BRANCH_NAME	Bank branch name
CONTROL_BEGIN_BALANCE	Beginning balance of the bank statement for control purposes
CONTROL_TOTAL_DR	Total payment amount of the bank statement that can be compared to the actual payment entry totals for control purposes
CONTROL_TOTAL_CR	Total receipt amount of the bank statement that can be compared to the actual receipt entry totals for control purposes
CONTROL_END_BALANCE	Ending balance of the statement for control purpose (CONTROL_BEGIN_BALANCE – CONTROL_TOTAL_DR + CONTROL_TOTAL_CR)
CONTROL_DR_LINE_COUNT	Total payment line count of the statement that can be compared to the actual number of payments entered for control purposes
CONTROL_CR_LINE_COUNT	Total receipt line count of the statement that can be compared to the actual number of receipts entered for control purposes
CONTROL_LINE_COUNT	Total line count of the statement that can be compared to the actual number of lines entered for control purposes
RECORD_STATUS_FLAG	Statement upload status Value Meaning —– ————- N New C Corrected T Transferred E Error
CURRENCY_CODE	Bank statement currency code
CHECK_DIGITS	Holds any check digits that result from bank account number validation in FBS
CASHFLOW_BALANCE	Cash flow Balance
INT_CALC_BALANCE	Interest Calculated Balance
INTRA_DAY_FLAG	Flag to indicate whether this statement is an Intra-Day statement or not
ONE_DAY_FLOAT	One-day float balance
TWO_DAY_FLOAT	Two-day float balance
AVERAGE_CLOSE_LEDGER_MTD	Average closing ledger month to date balance
AVERAGE_CLOSE_LEDGER_YTD	Average closing ledger year to date balance
AVERAGE_CLOSE_AVAILABLE_MTD	Average closing available month to date balance
AVERAGE_CLOSE_AVAILABLE_YTD	Average closing available year to date balance
SUBSIDIARY_FLAG	Flag to indicate whether this is a subsidiary bank account statement

Select * from CE_STATEMENT_LINES_INTERFACE where bank_account_num = ‘123456789’ and statement_number = ‘140718’;

Fields and description are given below:

Name	Comments
BANK_ACCOUNT_NUM	Bank account number
STATEMENT_NUMBER	Statement number
LINE_NUMBER	Statement line number
TRX_DATE	Line transaction date, used to update AP check’s cleared date and AR receipt’s cleared date if EFFECTIVE_DATE is null
TRX_CODE	Bank transaction code
EFFECTIVE_DATE	Statement line effective date, used to update AR receipt’s clearing date
TRX_TEXT	Statement line description
INVOICE_TEXT	Invoice number for finding available transactions by invoice numbers
BANK_ACCOUNT_TEXT	Supplier/customer bank account number for finding available transactions by supplier/customer bank account numbers
AMOUNT	Statement line amount
CURRENCY_CODE	Statement line currency code
USER_EXCHANGE_RATE_TYPE	Currency conversion rate type
EXCHANGE_RATE_DATE	Statement line effective date, used to update AR receipt’s clearing date
EXCHANGE_RATE	Currency conversion rate
ORIGINAL_AMOUNT	Statement line amount in currency code
CHARGES_AMOUNT	Bank charges amount
BANK_TRX_NUMBER	Transaction number that identifies the transaction to be matched against the statement line
CUSTOMER_TEXT	Customer number for finding available transactions by customer numbers

Step2: View the imported statements in Oracle

1. To view the imported bank statement:

Navigate to Cash Management > Bank Statements > Bank statements and Reconciliation

Search with Statement number/Account number and click on Find

Technical Details

1. The bank statement data is stored into below tables after importing them.

CE_STATEMENT_HEADERS – table stores bank statement header information

CE_STATEMENT_LINES – table stores the bank statement lines information.

2. The fields and description in Table CE_STATEMENT_HEADERS are given below:

Select * from CE_STATEMENT_HEADERS where statement_number = ‘140718’;

The AUTO_LOADED_FLAG indicates if the statement is loaded via the interface tables or not.

Name	Comments
STATEMENT_HEADER_ID	Statement header identifier
BANK_ACCOUNT_ID	Bank account identifier
STATEMENT_NUMBER	Statement number
STATEMENT_DATE	Statement date
AUTO_LOADED_FLAG	Flag to indicate whether the statement was loaded automatically via open interface, Y or N
GL_DATE	Date used for GL accounting entries generated during the reconciliation process
CONTROL_BEGIN_BALANCE	Beginning balance of the bank statement for control purpose
CONTROL_TOTAL_DR	Total payment amount of the bank statement that can be compared to the actual payment entry totals for control purpose
CONTROL_TOTAL_CR	Total receipt amount of the bank statement that can be compared to the actual receipt entry totals for control purpose
CONTROL_END_BALANCE	Ending balance of the statement for control purpose (CONTROL_BEGIN_BALANCE – CONTROL_TOTAL_DR + CONTROL_TOTAL_CR)
CONTROL_DR_LINE_COUNT	Total payment line count of the statement that can be compared to the actual number of payments entered for control purposes
CONTROL_CR_LINE_COUNT	Total receipt line count of the statement that can be compared to the actual number of receipts entered for control purposes
CURRENCY_CODE	Bank statement currency code
STATEMENT_COMPLETE_FLAG	Flag to indicate whether the statement is complete or not, Y or N
DOC_SEQUENCE_ID	Document sequence identifier
DOC_SEQUENCE_VALUE	Document number
CHECK_DIGITS	Holds any check digits that result from bank account number validation in FBS
CASHFLOW_BALANCE	Cashflow Balance
INT_CALC_BALANCE	Interest Calculated Balance
ONE_DAY_FLOAT	One-day float balance
TWO_DAY_FLOAT	Two-day float balance

3. Before the statement lines are reconciled the STATUS field in CE_STATEMENT_LINES would be populated as UNRECONCILED.

4. Fields and description in Table CE_STATEMENT_LINES are given below:

Select * from CE_STATEMENT_LINES where statement_header_id in (select statement_header_id from CE_STATEMENT_HEADERS where statement_num = ‘140718’);

Name	Comments
STATEMENT_LINE_ID	System-assigned statement line identifier
STATEMENT_HEADER_ID	Statement header identifier
LINE_NUMBER	Statement line number
TRX_DATE	Line transaction date, used to update AP check’s cleared date and AR receipt’s cleared date if EFFECTIVE_DATE is null
TRX_TYPE	Transaction type, lookup type BANK_TRX_TYPE
AMOUNT	Statement line amount
STATUS	Statement line status – Reconciled/Unreconciled
TRX_CODE_ID	Bank transaction code identifier
EFFECTIVE_DATE	Statement line effective date, used to update AR receipt’s clearing date
BANK_TRX_NUMBER	Transaction number that identifies the transaction to be matched against the statement line
TRX_TEXT	Statement line description
CUSTOMER_TEXT	Customer number for finding available transactions by customer numbers
INVOICE_TEXT	Invoice number for finding available transactions by invoice number
BANK_ACCOUNT_TEXT	Supplier/customer bank account number for finding available transactions by supplier/customer numbers
CURRENCY_CODE	Statement line currency code
EXCHANGE_RATE_TYPE	Currency conversion rate type
EXCHANGE_RATE	Currency conversion rate
EXCHANGE_RATE_DATE	Date used in determining the currency exchange rate
ORIGINAL_AMOUNT	Statement line amount in currency code
CHARGES_AMOUNT	Bank charges amount
RECONCILE_TO_STATEMENT_FLAG	Flag to indicate whether this statement line is reconciled to another statement line, Y or Null
JE_STATUS_FLAG	Flag to indicate the status of the journal entry for this statement line. (C-JE Created S-Success E-Error)
ACCOUNTING_DATE	The accounting date as entered in the JE that is created by the statement line
GL_ACCOUNT_CCID	The CCID of the GL account for which this statement line has created a journal entry

Reference : Doc ID 1969727.1

 

Thanks Yogesh