Bank Statement Interface
Bank Reconciliation is required for auditing purposes. Reconciliation is the process of determining the difference between two balances, one on you bank account statements and one on the bank accounts in oracle financials.
Bank reconciliation is a process that explains the difference between the bank balance shown in an organization’s bank statement, as supplied by the bank, and the corresponding amount shown in the organization’s own accounting records at a particular point in time.Bank Reconciliation is required for auditing purposes. Reconciliation is the process of determining the difference between two balances, one on you bank account statements and one on the bank accounts in oracle financials.
Such differences may occur, for example, because
- a cheque issued by the organization has not been presented to the bank,
- a banking transaction, such as a credit received, or a charge made by the bank, has not yet been recorded in the organization’s books
- either the bank or the organization itself has made an error
Bank statement Import Process Flow
Set Up required for using Bank statement Import & Reconciliation:
Step 1: Bank Account
Create the bank account and bank branch in Accounts payables.
Step 2: Transaction Code
Transaction code for the bank should be set up corresponding to all the codes that might possibly appear in the data file. The codes have a type (Payment, receipt, stopped, rejected, miscellaneous payment etc). These differ from bank to bank, and thus are client specific.For example:
Code Description Source
CQ Check AP Payment
DD Direct debit AR Receipt
Step 3: Bank statement mapping
Step 4: Creating the data file
Data file should be of following structure
Record 01 - Mandatory---------------------This should be your first record in the file. It should be of the following format:01,<bank originator ID>,<bank customer ID>,<file creation date>,<file creation time>,<file identification number>,<physical record length>,<block size>,<version number>/
01,Wellfargo,,20111028,0720,01,080,,2
Record 02 - Mandatory---------------------This should be the second record in the file. It should be of the following format:02,<bank customer ID>,<bank originator ID>,<group status>,<as of date>,<as of time>,<currency code>,<as of date modifier>/
02,PRJ,201110280720-WS,1,20111028,,USD,
The group status can be 1 for "update", 2 for "deletion", 3 for "correction" and 4 for "test only".Record 03 - Mandatory---------------------This should be the third record in the file. It should be of the following format:03,<bank account number>,<currency code>,<type code>,<sign><amount>,<item count>,<funds type>/
03,811089812
Record 16 – Mandatory---------------------The record type 16 will likely have many records, which should be the fourth and subsequent records. Each will be of the following format:16,<transaction code>,<amount>,<funds type>,<bank ref #>,<customer ref #>,<text>16,CQ,2406,,CHECK,CHECK,CHECK,,20021235,
16,CQ,4704,,CHECK,CHECK,CHECK,,20021290,
16,CQ,7733,,CHECK,CHECK,CHECK,,20020803,
16,CQ,9705,,CHECK,CHECK,CHECK,,20020567,
16,CQ,11958,,CHECK,CHECK,CHECK,,20019597,
16,CQ,13201,,CHECK,CHECK,CHECK,,20019014,
16,CQ,18507,,CHECK,CHECK,CHECK,,20020272,
16,CQ,101015,,CHECK,CHECK,CHECK,,20021322,
16,CQ,191189,,CHECK,CHECK,CHECK,,20020459,
Record 49 - Mandatory---------------------This record should follow the last 16 record for the given bank account. It should be of the following format:49,<account control total>,<number of records for account>/49,360418,11
Record 99 - Mandatory---------------------This will be the last record in your file. It should be of the following format:99,<file control total>,<number of groups>,<number of records>/Concurrent programs to Run
--------------------------You have the option of running the concurrent programs in a variety of ways.When testing, it is recommended to run each program one at a time, so that you
can see where in the process you are in case an error occurs. Once you have the setup completed, you can run all of the programs in one step.
1. Bank Statement LoaderThe required parameters to this job are the following:Process Option – Choose "Load".Mapping Name - Pick the one you created in the Bank Statement Mapping section,or if you used a standard one, pick that.
Data File Name - This is whatever you named your data file. Typical convention is to use the .dat extension. Example: bofa123101.dat
Directory Path - If you placed your data file in the $CE_TOP/bin directory, leave this parameter blank. Otherwise, you must provide the entire directory path to your data file. Do NOT use any environment variables in your pathname.
Bad example: $CE_TOP/out/bofa123101.datGood example: /amer/oracle/crmus01/crmus01appl/ce/11.5.0/out/ bofa123101.datDisplay Debug - Defaults to "N", but it is recommended to set it to "Y" to aid in debugging issues. This job will kick off three additional concurrent programs:
Run SQL*Loader- <format name> - This program takes the data from your data file and loads it into the CE_STMT_INT_TMP table. This program has no output, but you can see on the last page of the log file how many records got loaded and how many got rejected.
Load Bank Statement Data - This program takes the data from the CE_STMT_INT_TMP table and loads it into the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables. There is no output for this program, and the log file is useless in debugging.
Bank Statement Loader Execution Report - This program provides some information about what the previous programs did. It has an output report, which has minimal value. Occasionally, you will get a good error message or warning, but typically it reports everything is fine even if there was an issue. The log file is not helpful in debugging issues.
2. Bank Statement Import
The required parameters to this job are the following:Bank Branch Name - Provide the bank branch name that you setup in the Bank Account Setup section.
GL Date - Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter. The date must in an open period in both AP and AR.
Note: If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.
If successful, this program moves records from the CE_STATEMENT_HEADERS_INTERFACE and CE_STATEMENT_LINES_INTERFACE tables into the CE_STATEMENT_HEADERS and CE_STATEMENT_LINES tables.
This concurrent program will launch one other concurrent program:AutoReconciliation Execution Report - Although misleadingly named, this report is useful. It will show exceptions which may have occurred during the import.
3. AutoReconciliation
The required parameters to this job are the following:Bank Branch Name - Provide the bank branch name that you set up in the Bank Account section.
GL Date - Although this parameter is not marked as required, the import will often fail if you do not provide a value for this parameter. The date must in an open period in both AP and AR. Note: If you have multiple files loaded, but only want to import one of them, use the Statement Date or Statement Number range parameters to limit the import job.
If successful, this program will reconcile the imported bank statement lines to outstanding AP and AR transactions. If unsuccessful, it will mark the bank statement line with an error and allow you to manually reconcile the transaction.
This concurrent program will launch one other concurrent program: AutoReconciliation Execution Report - This time this same report shows exceptions in matching up the imported bank statement transactions with the existing AP and AR transactions in the system. It gives descriptive reasons why the line was not able to be reconciled automatically.
=======================================================
Procedure to delete Bank statements from Cash Management
Step 1: Un-Reconcile all the bank statements which have been loaded incorrectly.
Please run the “Unreconcile Bank Statement” for each BANK_ACCOUNT_NUMBER and STATEMENT NUMBER combination.
Navigation:
• Corresponding Super User Responsibilities
• View
• Requests
• Submit concurrent program “Unreconcile Bank Statement”
Step2: Delete the Bank statements those have been incorrectly loaded into CM. We will make use of the Custom Purge program that is already available in CM super user responsibility.
Run “PHS Purge/Archive Bank Statements” to purge Unreconciled statements. Pass Parameters as Below.
Note: 1. The program need to be run for every bank branch in Bank Statement.
2. Statement Date parameter value should be same as Statement Date in the Data File.
3. Statement Number Parameter value should be the same as the STATEMENT_NUMBER value present in CE_STATEMENT_HEADERS_ALL table.
Step 3: Delete the statement lines and headers open interface tables for the statements
Step 3.1: Unwanted records for statements which are to be re-loaded need to be archived and deleted from CE_STATEMENT_HEADERS_INT_ALL, CE_STATEMENT_LINES_INTERFACE interface tables by running the Standard Oracle Concurrent program: Archive/Purge Bank Statements
This will need to be executed on all the Org IDs depending if they have data on the interface table that no longer needs to be loaded.
Navigation:
• Corresponding Super User Responsibility
• View
• Requests
• Submit Request
• Archive/Purge Bank Statements
On the parameters:
• Archive/Purge Objects: Interface
• Statement Type: Both
• Interface Statement Status: All
• Archive/Purge Option: Archive/Purge
• Archive Option: Append
• Bank Branch Name: This depends on the data you want to remove on the interface table
• Bank Account Number: This depends on the data you want to remove on the interface table
• Statement Date From: The minimum date of the statement period you want to archive and delete from the interface tables
• Statement Date To: The maximum date of the statement period you want to archive and delete from the interface tables
Below is a snapshot:
The deletion will be performed via the back-end process using the following scripts.
DELETE FROM
CE.CE_STATEMENT_HEADERS_INT_ALL WHERE
statement_number =
DELETE FROM
CE.CE_STATEMENT_LINES_INTERFACE
WHERE statement_number =
Step 3.3: Clean-up the CE.CE_STMT_INT_TMP table before performing a new CM run.
DELETE FROM
CE.CE_STMT_INT_TMP;