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.

  1. 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
  1. Before the statement lines are reconciled the STATUS field in CE_STATEMENT_LINES would be populated as UNRECONCILED.
  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s