Bank Statement Load & Import process in Cash Management

Cash Management module is used to reconcile all financial incoming and outgoing transactions related to Oracle. As the actual financial transactions have to be between supplier and customer banks it is necessary to get the bank statements on a regular basis and match the bank statement against all payments and receipts, etc.  If for some reason certain transactions could not be reconciled then probing both the statement and the transaction in Oracle will bring out the discrepancy. Hence this is also very effective internal audit mechanism for the organization implementing Oracle.

This is a 2 part post. the current and 1^st post deals with loading and importing the bank statements received from the bank. The 2^nd post illustrates how to reconcile the statements.

 

Load Bank Statement

Login to Cash Management Administrator responsibility

Navigation: View > Requests 

Run the program name, Bank Statement Loader

Bank Statement Loader program

Enter the Parameters

Program parameters

Available values in Process Option parameter LOV

Process Option values

Drop the bank statement files on the file server. Note the location of the files as this to entered as a parameter.

Bank Statement files on server

Enter parameters for the Bank Statement Loader program for 1 statement file

Bank statement loader with parameters

All the requests fired for the Load program

Bank statement loader requests

The request name, Run SQL*Loader- SWIFT940, gives the output to say how many records have been loaded into Oracle. 

The program gives an output like,

Run SQL*Loader – SWIFT 940 output

 

At this stage the records are loaded from the file into a staging table named, CE_STMT_INT_TMP.

Querying this table by, select * from CE_STMT_INT_TMP, will give us

CE_STMT_INT_TMP records

The program Load Bank Statement Data validates the data against CE_STMT_INT_TMP table with the Mapping Name (This is the parameter entered at the beginning. The setup is given in Setup Steps in a later post). The statement moves to CE_STATEMENT_LINES_INTERFACE table.

SELECT *

  FROM ce_statement_lines_interface

WHERE bank_account_num =’0110005173′AND trx_date =TO_DATE(’25-Dec-2011′,‘DD-MON-YYYY’)AND statement_number =’0025/001 – 25-DEC-11′

CE_STATEMENT_LINES_INTERFACE records

The final request, Bank Statement Loader Execution Report, gives the overall status of the program. The output looks like the following,

Bank Statement Loader execution report output

 

Import Bank Statement

There are multiple ways to import Bank Statements.

Step 1: Bank Statement Import program

Login to Cash Management Administrator responsibility

Navigation: View > Requests

Bank statement import program

The utility of this program is that Bank Statements can be imported in bulk for a bank account and multiple statement numbers and dates.

Step 2: Bank Statement Interface form

Login to Cash Management Administrator responsibility

Navigation: Bank Statements > Bank Statement Interface

Bank Statement menu

Bank Statement Interface form showing list of accounts on opening

Select a Bank Account

We can select the bank account from here or if we cancel the list of values shown we come to the following form.

Select a Bank Account

We shall now query for the account number and select the Statement according the date loaded.

Bank Account selected

Click on Lines button to see the lines for this statement

Statement lines

Close this window and click on Tools > AutoReconciliation on the menu

Now the statement will imported

Login to Cash Management Administrator responsibility

Navigation: Bank Statements > Bank Statement Interface

Bank Statement Interface form menu

A popup window will appear showing 3 options, Import/reconcile/Import or reconcile. The option “Import” is selected and Submit button is pressed. If the option Import and Reconcile is selected then AutoReconciliation program will be executed after importing the statement. AutoReconciliation is explained in another post.

Import statement lines

The concurrent programs executed for Statement Import is shown below. The last program to run is Bank Statement Import Execution Report output

Import requests

The Bank Statement Import Execution Report output gives the overall status of the Import process. The report is shown below.

Statement import execution report output

Tables

Once the Import program ends, the statement moves from CE_STATEMENT_LINES_INTERFACE table to CE_STATEMENT_HEADERS_ALL, CE_STATEMENT_LINES_ALL tables.

SELECT *

  FROMce_statement_lines_interface

WHERE bank_account_num =’0110005173′AND trx_date =TO_DATE(’25-Dec-2011′,‘DD-MON-YYYY’)AND statement_number =’0025/001 – 25-DEC-11′

CE_STATEMENT_LINES_INTERFACE table

 Now are ready to reconcile the statement we have loaded and imported. In the next post I shall demonstrate how the reconciliation process takes place.

         =================  *************************  ================

Now I've simplied the proc as much as possible and reduced my file to only two lines. So basically I only have an IF, ELSE, END IF. The file looks like this:
SH200806200225222122222000 074222228648163879C2008062001894
TD20080620000000026871428C00000000002222235307C00000000006088

This is what gets inserted into the temp table(Can't show you all the columns otherwise it won't look nice to read). As you can see below, it's duplicating the same values for 'SH' and TD. But for TD, only C14 and C15 should be inserted.

REC C1 C2 C3 C4     C14     C15
1 SH 20080620 0225 222122222         
2 TD 20080620 0225 222122222 000000026871428     200806200000000

The dbms_output looks like this:
column1SH
column220080620
column30225
column4222122222
column5000
column6
column7074
column8222228648163879
column9C
column1020080620
column110189
column1TD
column14000000026871428
column15200806200000000
no_data_found

And the proc I'm running is exactly like this:
PROCEDURE bank_stmt_load(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_file_name VARCHAR2) IS
f_datfile utl_file.file_type; /* file handler for incoming data file */
v_buffer VARCHAR2(150); /* buffer to hold single line data from flat file */
v_sqlerrm VARCHAR2(240);
v_run_id NUMBER := Fnd_Global.conc_request_id;
v_sttemp ce_stmt_int_tmp%ROWTYPE;
v_counter NUMBER;
v_filename VARCHAR2(40);
v_seq NUMBER;
v_org_id NUMBER;
e_custom EXCEPTION;
v_msg VARCHAR2(250);
l_org_id NUMBER;
v_dm VARCHAR2(2);

BEGIN
v_org_id :=Fnd_Profile.value('ORG_ID');
v_filename := LTRIM(RTRIM('My_file.txt'));
f_datfile := utl_file.fopen(g_dir_name,v_filename,'R');
v_counter := 1;
     DELETE FROM ce.ce_stmt_int_tmp;
LOOP
BEGIN
utl_file.get_line(f_datfile ,v_buffer);

v_sttemp.rec_no := v_counter;
v_sttemp.rec_id_no := 'L';
     v_sttemp.column1 := substr(v_buffer ,1 ,2 ); --record type
     v_dm := v_sttemp.column1;
                    dbms_output.put_line('column1'||v_sttemp.column1);
     IF v_dm = 'SH' THEN
v_sttemp.column2 := substr(v_buffer ,3 ,8); --statement date
                    dbms_output.put_line('column2'||v_sttemp.column2);
v_sttemp.column3 := substr(v_buffer ,11 ,4); --branch number
                    dbms_output.put_line('column3'||v_sttemp.column3);
v_sttemp.column4 := substr(v_buffer ,15 ,9); --account number
                    dbms_output.put_line('column4'||v_sttemp.column4);
     v_sttemp.column5 := substr(v_buffer ,24 ,3); --account type
                    dbms_output.put_line('column5'||v_sttemp.column5);
     v_sttemp.column6 := substr(v_buffer ,27 ,10); --office code
                    dbms_output.put_line('column6'||v_sttemp.column6);
     v_sttemp.column7 := substr(v_buffer ,37 ,3); --statement number
                    dbms_output.put_line('column7'||v_sttemp.column7);
     v_sttemp.column8 := substr(v_buffer ,40 ,15); --opening balance
                    dbms_output.put_line('column8'||v_sttemp.column8);
v_sttemp.column9 := substr(v_buffer ,55 ,1); --cr or dr indicator
                    dbms_output.put_line('column9'||v_sttemp.column9);
v_sttemp.column10 := substr(v_buffer ,56 ,8); --date of first entry
                    dbms_output.put_line('column10'||v_sttemp.column10);
v_sttemp.column11 := substr(v_buffer ,64 ,4); --total number of entries
               dbms_output.put_line('column11'||v_sttemp.column11);
     v_sttemp.column12 := v_sttemp.column6 || v_sttemp.column5; -- used for calculation
v_counter := v_counter + 1;
ELSE
     v_sttemp.column14 := SUBSTR(v_buffer ,11 ,15);
          dbms_output.put_line('column14'||v_sttemp.column14);
     v_sttemp.column15 := SUBSTR(v_buffer ,3 ,15); --Amount 
               dbms_output.put_line('column15'||v_sttemp.column15);
         
END IF;
    
INSERT INTO ce_stmt_int_tmp
(rec_no
,rec_id_no
,column1
,column2
,column3
,column4
,column5
,column6
,column7
,column8
,column9
,column10
,column11
,column12
     ,column14
     ,column15)
VALUES
(v_sttemp.rec_no
,v_sttemp.rec_id_no
,LTRIM(RTRIM(v_sttemp.column1))
,LTRIM(RTRIM(v_sttemp.column2))
,LTRIM(RTRIM(v_sttemp.column3))
,LTRIM(RTRIM(v_sttemp.column4))
,LTRIM(RTRIM(v_sttemp.column5))
,LTRIM(RTRIM(v_sttemp.column6))
,LTRIM(RTRIM(v_sttemp.column7))
,LTRIM(RTRIM(v_sttemp.column8))
,LTRIM(RTRIM(v_sttemp.column9))
,LTRIM(RTRIM(v_sttemp.column10))
,LTRIM(RTRIM(v_sttemp.column11))
,LTRIM(RTRIM(v_sttemp.column12))
     ,LTRIM(RTRIM(v_sttemp.column14))
     ,LTRIM(RTRIM(v_sttemp.column15)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('no_data_found');
EXIT;
END;
END LOOP;
     COMMIT;
utl_file.fclose_all;
END bank_stmt_load;