Thursday, April 26, 2012
Introduction:
Oracle Cash Management provides an open interface for loading electronic bank statement files into the bank statement tables. The Bank Statement Open Interface consists of two interface tables and an import program. The tables contain information about the bank statement header and bank statement lines and the Bank Statement Import program transfers information from the open interface tables to the bank statement tables.
To simplify the implementation of Oracle Cash Management, the Bank Statement Open Interface has been enhanced to offer a complete solution for loading bank statement information from an external file. Using the new Bank Statement Loader feature, data can be quickly and easily loaded from BAI2 and SWIFT940 bank statement files into the Bank Statement Open Interface tables without any programming. The Bank Statement Loader also supports user-defined formats like French EDIFACT. Once data is populated into the open interface tables, the Bank Statement Import program is used to transfer the data to the base bank statement tables.
Graphical Representation of Import Process:
Part1: Introduction and Graphical Representation of Bank Statement Import Process
Part 2: Step to implement Bank Statement Open Interface - Setups
Part 3: Table Details related to Open Interface Bank Statement
Thursday, April 26, 2012 by Team search · 0
Step 2: Define Bank Transaction Code:
In order to load electronic bank statements or use Cash Management’s Auto Reconciliation feature, Transaction codes has to be defined for each bank account. The transaction codes that your bank uses to identify different types of transactions on its statements. Define the bank transaction code for each code that you expect to receive from your bank with effective date range fields, Start Date and End Date, so that you can make a bank transaction code inactive.
Step 3: Define Mapping Rule
Set up your Bank Statement Mapping in Cash Management. It is a good idea to copy the seeded mappings to your own so that you can modify it freely. When you enter the form, it will prompt you to find a mapping. Cancel that dialog. When the find window disappears, give your new format a name and description. Use the existing control file and supply the desired date format. Define the precision and choose the appropriate format type. Then click on Populate and save. This will copy the default mapping to your new name. Then we are free to modify it.
Step 4: Placing of Statement File
Keep the bank statement file in the desired inbound folder. For example, $CE_TOP/DATA_IN
Step 5: Submission of Bank Statement import Program
Part1: Introduction and Graphical Representation of Bank Statement Import Process
Part 2: Step to implement Bank Statement Open Interface - Setups
Part 3: Table Details related to Open Interface Bank Statement
by Team search · 0
In this post, let us the see the tables related to Bank Statement Open Interface.
TABLE
|
DESCRIPTION
|
CE_BANK_STMT_INT_MAP
|
Stores
the definitions of the mapping templates
|
CE_BANK_STMT_MAP_HDR
|
This
table maps the columns the Bank Statement
Headers
Interface table (CE_STATEMENT_HEADERS_INT_ALL) to the columns in the
intermediate table (CE_STMT_INT_TMP).
|
CE_BANK_STMT_MAP_LINE
|
Maps the
columns in the Bank Statement Lines Interface table (CE_STATEMENT_LINES_INTERFACE)
to the columns in the intermediate table (CE_STMT_INT_TMP).
|
CE_TRANSACTION_CODES
|
This table stores pre-determined codes between
you and your bank to identify the types of transactions for matching
statement lines. For automatically created statement lines, a transaction
code defines the rules for creating these statement lines. Each transaction
code is associated with a bank account.
CREATE_MISC_TRX_FLAG specifies whether a miscellaneous transaction, such as charges, should be created for statement lines during the automatic reconciliation process. This table corresponds to the Bank Transaction Codes form. |
CE_STMT_INT_TMP
|
Intermediate
table, which stores the information loaded from a bank statement file. This
table is populated by the SQL*Loader script.
|
CE_SQLLDR_ERRORS
|
Records
the errors encountered by the Bank Statement Loader program when loading data
from the bank statement file into the intermediate table
|
CE_STATEMENT_HEADERS_INT_ALL
|
The CE_STATEMENT_HEADERS_INT_ALL table stores
information about bank statement details for importing. Each row contains the
bank statement number, bank account number, control balances, and other
statement- related information. After populating this table, you can run the
Bank Statement Import program to transfer the statement information into the
CE_STATEMENT_HEADERS_ALL table.
The Bank Statement Interface form allows you to modify the statement interface information or to correct any errors encountered while uploading the data. |
CE_STATEMENT_LINES_INTERFACE
|
This table stores information about bank
statement line details for open interface. Each row contains the bank
statement number, bank account number, statement line amount, and others.
After populating this table, you can run the Bank Statement Import program to
transfer the statement line information into the CE_STATEMENT_LINES table.
The Bank Statement Interface form allows you to modify the statement line interface information or correct any errors encountered while uploading the data. |
CE_HEADER_INTERFACE_ERRORS
|
This table stores information about errors that
occurred while importing bank statements. Each row includes the statement
number, bank account number, and error message name for retrieving the actual
error messages. A record is written to this table for each error while
running the Bank Statement Import program.
You can review the errors on the Bank Statement Interface form or on the AutoReconciliation Execution report |
CE_LINE_INTERFACE_ERRORS
|
This table stores information about errors that
occurred while importing bank statement lines. Each row includes statement
number, statement line number, bank account number, and error message name
for retrieving the actual error messages. A record is written to this table
for each error while running the Bank Statement Import program.
You can review the errors on the Bank Statement Interface form or on the AutoReconciliation Execution report. |
CE_ARCH_INTERFACE_HEADERS
|
This table stores archived statement interface
information. Each row in this table corresponds to an archived
CE_STATEMENT_HEADERS_INT_ALL record. This table is populated when you run the
Archive/Purge Bank Statements program and choose to archive, or by the
AutoReconciliation program once you enable your system options to
automatically purge and archive statement interface tables.
|
CE_STATEMENT_HEADERS_ALL
|
This table stores bank statements. Each row in this table
contains the statement name, statement date, GL date, bank account
identifier, and other information about the statement. This table corresponds
to the Bank Statement window of the Bank Statements form.
Once you have marked your statement as complete, the STATEMENT_COMPLETE_FLAG is set to Y, and you can no longer modify or update the statement. AUTO_LOADED_FLAG is set to Y when your statement is uploaded from the interface table using the Bank Statement Import program |
CE_STATEMENT_LINES
|
This table stores information about bank statement lines. Each
row in this table stores the statement header identifier, statement line
number, associated transaction type, and transaction amount associated with
the statement line.
This table corresponds to the Bank Statement Lines window of the Bank Statements form |
CE_STATEMENT_RECONCILS_ALL
|
This table stores information about
reconciliation history or audit trail. Each row represents an action
performed against a statement line.
|
CE_RECONCILIATION_ERRORS
|
This table stores information about errors that
occurred while reconciling a bank statement. Each row includes the statement
line identifier and error message name for retrieving the actual error
messages. A record is written to this table for each error while running the
AutoReconciliation program.
You can review the errors on the Bank Statements form or on the AutoReconciliation Execution report. |
by Team search · 1
Flowchart:
Source Code:
CREATE OR REPLACE PROCEDURE XXX_REUSB_BANK_SUB_CAP ( piv_file_name IN VARCHAR2, pin_request_id IN NUMBER, piv_file_path IN VARCHAR2, piv_load_option IN VARCHAR2, piv_map_temp IN VARCHAR2, piv_bank_br_num IN VARCHAR2, piv_accnt_num IN VARCHAR2, piv_gl_date IN VARCHAR2 ) AS ln_req_id NUMBER := 0; ln_user_id NUMBER := 0; ln_resp_id NUMBER := 0; ln_appl_id NUMBER := 0; lb_result BOOLEAN; lv_phase VARCHAR2 (100); lv_status VARCHAR2 (100); lv_dev_phase VARCHAR2 (100); lv_dev_status VARCHAR2 (100); lv_message VARCHAR2 (100); lv_srequest_status VARCHAR2 (10); lv_statuscode VARCHAR2 (50); ln_req_id2 NUMBER := 0; lv_submission_error VARCHAR2 (500); lv_conc_prog VARCHAR2 (300); lv_load_status VARCHAR2 (20) DEFAULT 'SUCCESS'; BEGIN DBMS_OUTPUT.put_line ('STATEMENT FILENAME :' || piv_file_name); DBMS_OUTPUT.put_line (' ****** START OF BANK STATEMENT LOADER PROGRAM STATUS ******'); BEGIN SELECT requested_by, responsibility_application_id, responsibility_id INTO ln_user_id, ln_appl_id, ln_resp_id FROM fnd_concurrent_requests WHERE request_id = pin_request_id; fnd_global.apps_initialize (ln_user_id, ln_resp_id, ln_appl_id ); COMMIT; END; BEGIN DBMS_OUTPUT.put_line( 'Submit the concurrent progam "Bank Statement Loader".' ); ln_req_id := fnd_request.submit_request (application => 'CE', program => 'CESQLLDR', description => NULL, start_time => NULL, sub_request => FALSE, argument1 => piv_load_option, argument2 => piv_map_temp, argument3 => piv_file_name, argument4 => piv_file_path, argument5 => piv_bank_br_num, argument6 => piv_accnt_num, argument7 => piv_gl_date, argument8 => NULL, argument9 => NULL, argument10 => NULL, argument11 => 'N', argument12 => NULL, argument13 => NULL ); IF ln_req_id = 0 THEN lv_submission_error := fnd_message.get; DBMS_OUTPUT.put_line ( ' ERROR: Bank Statement Loader not submitted "' || lv_submission_error || '"' ); lv_load_status :='FAILED'; ELSE COMMIT; DBMS_OUTPUT.put_line ( ' Request Submitted : ' || ln_req_id ); LOOP lb_result := fnd_concurrent.wait_for_request (ln_req_id, 10, 10, lv_phase, lv_status, lv_dev_phase, lv_dev_status, lv_message ); IF lb_result AND lv_dev_phase = 'COMPLETE' THEN EXIT; END IF; END LOOP; END IF; END; IF lv_dev_phase = 'COMPLETE' THEN DBMS_OUTPUT.put_line ( ' Bank Statement Loader Request Completed with status ' || lv_dev_status ); IF lv_dev_status IN ('ERROR') THEN lv_load_status :='FAILED'; END IF; FOR cur_req IN (SELECT a.request_id, a.logfile_name, a.outfile_name, a.concurrent_program_id, b.user_concurrent_program_name conc_prog FROM fnd_concurrent_requests a, fnd_concurrent_programs_tl b WHERE a.parent_request_id = ln_req_id AND a.concurrent_program_id = b.concurrent_program_id AND b.LANGUAGE = 'US' ORDER BY a.request_id) LOOP DBMS_OUTPUT.put_line ( ' ' || cur_req.conc_prog || ' -> REQUEST SUBMITTED ' || cur_req.request_id ); LOOP lb_result := fnd_concurrent.wait_for_request (cur_req.request_id, 10, 10, lv_phase, lv_status, lv_dev_phase, lv_dev_status, lv_message ); IF lb_result THEN IF lv_dev_phase = 'COMPLETE' THEN DBMS_OUTPUT.put_line ( ' ' || cur_req.conc_prog || ' has completed successfully' ); EXIT; END IF; END IF; END LOOP; IF UPPER (lv_dev_status) IN ('ERROR', 'WARNING') THEN DBMS_OUTPUT.put_line ( ' ' || cur_req.conc_prog || ' has completed with status ' || UPPER (lv_dev_status) ); lv_load_status :='FAILED'; ELSIF UPPER (lv_dev_status) IN ('NORMAL') AND cur_req.conc_prog = 'Load Bank Statement Data' THEN FOR cur_req_in IN (SELECT a.request_id, a.logfile_name, a.outfile_name, a.concurrent_program_id, b.user_concurrent_program_name conc_prog FROM fnd_concurrent_requests a, fnd_concurrent_programs_tl b WHERE a.parent_request_id = cur_req.request_id AND a.concurrent_program_id = b.concurrent_program_id AND b.LANGUAGE = 'US' ORDER BY a.request_id) LOOP DBMS_OUTPUT.put_line ( ' ' || cur_req_in.conc_prog || ' -> REQUEST SUBMITTED ' || cur_req_in.request_id ); LOOP lb_result := fnd_concurrent.wait_for_request (cur_req_in.request_id, 10, 10, lv_phase, lv_status, lv_dev_phase, lv_dev_status, lv_message ); IF lb_result THEN IF lv_dev_phase = 'COMPLETE' THEN DBMS_OUTPUT.put_line ( ' ' || cur_req_in.conc_prog || ' has completed with status ' || UPPER (lv_dev_status) ); EXIT; END IF; END IF; END LOOP; IF UPPER (lv_dev_status) IN ('ERROR', 'WARNING') AND cur_req_in.conc_prog LIKE 'Bank Statement Import%AutoReconciliation' THEN lv_load_status :='FAILED'; ELSIF UPPER (lv_dev_status) IN ('NORMAL') AND cur_req_in.conc_prog LIKE 'Bank Statement Import%AutoReconciliation' THEN BEGIN SELECT a.request_id, b.user_concurrent_program_name conc_prog INTO ln_req_id2, lv_conc_prog FROM fnd_concurrent_requests a, fnd_concurrent_programs_tl b WHERE a.parent_request_id = cur_req_in.request_id AND a.concurrent_program_id = b.concurrent_program_id AND b.LANGUAGE = 'US'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ( ' AutoReconcilation Execution report not called for:' || cur_req_in.request_id ); WHEN OTHERS THEN DBMS_OUTPUT.put_line ( ' AutoReconcilation Execution report not called for:' || cur_req_in.request_id ); END; DBMS_OUTPUT.put_line ( ' ' || lv_conc_prog || ' -> REQUEST SUBMITTED ' || ln_req_id2 ); LOOP lb_result := fnd_concurrent.wait_for_request (ln_req_id2, 10, 10, lv_phase, lv_status, lv_dev_phase, lv_dev_status, lv_message ); IF lb_result THEN IF lv_dev_phase = 'COMPLETE' THEN DBMS_OUTPUT.put_line ( ' ' || lv_conc_prog || ' has completed with status ' || UPPER (lv_dev_status) ); EXIT; END IF; END IF; END LOOP; END IF; END LOOP; END IF; END LOOP; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (' ' || 'OTHERS EXCEPTION' || SQLERRM (SQLCODE)); END XXX_REUSB_BANK_SUB_CAP;
Part1: Introduction and Graphical Representation of Bank Statement Import Process
Part 2: Step to implement Bank Statement Open Interface - Setups
Part 3: Table Details related to Open Interface Bank Statement
by Team search · 1
Tuesday, April 24, 2012
- GL
Dates
- Entry
Descriptions
- Line
Descriptions
- Amounts
- Accounts:
Rules can be created for the entire account combination or for individual
segments.
- Journal
Lines
- Reconciliation References
Benefits of R12 Sub ledger Accounting
Tuesday, April 24, 2012 by Team search · 0