Tuesday 1 October 2013

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 1st post deals with loading and importing the bank statements received from the bank. The 2nd 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;

No comments:

Post a Comment