Cash Management
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.
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 Loader
The 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.dat
Good example: /amer/oracle/crmus01/crmus01appl/ce/11.5.0/out/ bofa123101.dat
Display 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.
