Thursday, April 26, 2012

BANK STATEMENT OPEN INTERFACE in Oracle Apps Cash Management

 

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:

clip_image001

 

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

Part 4: Sample Script to submit Bank Statement loader program and capture the status of the concurrent programs

Thursday, April 26, 2012 by Team search · 0

Steps to Implement Bank Statement Open Interface

Step 1: Define Internal Bank: Create your bank and bank account
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.
clip_image002

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.
clip_image004
clip_image006
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

The parameters are:-
·         Process Option - "Load". Or “Load and Import” or “Load, Import, and AutoReconciliation”
The Bank Statement Loader program can be executed in one of these three modes:
1. Load
2. Load and Import
3. Load, Import and Auto Reconciliation
Option 1 is only to run the loader program. In option 2, the Bank Statement Import program starts after the Bank Statement Loader completes successfully. In option 3, both the Bank Statement Import program and the Auto Reconciliation program are launched after successful completion of the Bank Statement Loader program. In either case, if the Bank Statement Loader program completes with errors or warnings, the concurrent request terminates and no further process is started.

·         Mapping Name - Pick the one created in the Bank Statement Mapping section, or any of the standard ones.
·         Data File Name - This is the name of the data file. Typical convention is to use the .dat extension.
Example: TEST.dat
·         Directory Path - If the data file is placed in the $CE_TOP/bin directory, leave this parameter blank.
Otherwise, enter the entire path of the directory where the data file is located. Do NOT use any environment variables in the pathname.
·         Display Debug - Defaults to "N", but it is recommended to set it to “Y" to aid in debugging issues.
·         Enter the bank branch name and the bank account number you want to load from the bank statement file. If nothing is entered, the entire file is loaded. Otherwise, only statements belonging to the bank branch or the bank account are loaded.
·         If you selected either the option of “Load and Import” or “Load,Import, and AutoReconciliation”, enter the GL Date.
·         If you selected the option of “Load, Import and AutoReconciliation”, enter the Receivables Activity and NSF Handling. Also enter the Payment Method if you specified a bank account number.
·         If multiple files have been loaded, but only one is to be imported, use Statement Date or Statement Number range parameters to limit the import job. 

This process kicks off the below additional concurrent programs:

Run SQL*Loader- - This program takes the data from the data file and loads it into the CE_STMT_INT_TMP table. This program has no output, but the last page of the log file shows the number of records loaded and those which were 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. This program has no output file and the log file is not helpful 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, there could be an error message or warning, but generally it shows a normal output even if there were problems encountered in the process. The log file is not helpful in debugging issues.

Bank Statement Import
On successful completion, 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

Auto Reconciliation
On successful completion, 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 manual reconciliation of the transaction.
This concurrent program launches one other concurrent program:
Auto Reconciliation Execution Report – Now the same report shows exceptions in matching up the imported bank statement transactions with the existing AP, AR, GL, Payroll and Miscellaneous transactions in the system. It gives descriptive reasons why the line was not able to be reconciled automatically.

 

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

Part 4: Sample Script to submit Bank Statement loader program and capture the status of the concurrent programs



by Team search · 0

TABLES INVOLVED IN BANK STATEMENT OPEN INTERFACE IN CASH MANAGEMENT



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

Script to Submit Bank Statement Loader Program and Capture Status of Submitted Programs

In this part, let us see the sample procedure to submit a bank statement loader program and capture the status of programs submitted as part of Bank Statement Import

Flowchart:

Sample Bank Statement Submission and capture 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

Part 4: Sample Script to submit Bank Statement loader program and capture the status of the concurrent programs

by Team search · 1

Tuesday, April 24, 2012

SUB LEDGER ACCOUNTING (SLA) - Features Overview



In this post, I gave a summarized and quick overview of Sub ledger Accounting. This may be useful for beginners.  In General, Sub ledger Accounting is introduced to make the accounting process easier and efficient. Lets see some of the features of SLA,

1. Setups:

Journal Entry Setups: Oracle SLA gives users the control over the definition of their journal entries. 
Users can define the following components,
  1. GL Dates
  2. Entry Descriptions
  3. Line Descriptions
  4. Amounts
  5. Accounts: Rules can be created for the entire account combination or for individual segments.
  6. Journal Lines
  7. Reconciliation References 
Setups can be easily copied and altered to modify seeded definitions. Also for each setup SLA stores information to determine whether it was seeded or customized by the user. This eases the customers’ worry about overwriting their setups and take advantage of the upgrades. 

2. Accounting:  This is a major feature of SLA around which all its intricacies and advantages revolve.

Application Accounting: All the journal entry setups which generate accounting for transactions of an application can be grouped together. These are called application accounting definitions. For example, all the setups to provide a product’s representation in the inventory accounting can be grouped and created as an application accounting definition.

Date ranges can be specified for the definition so that it is flexible enough to stand any changes. Also the definitions can be locked preventing any updates. 

Multiple Accounting Representations: For the same sub ledger transactions multiple accounting representations can be created and stored in a separate ledger. This meets the requirements which are mutually exclusive.

Draft Accounting: Users can create accounting in draft mode. They can view and report on accounting without saving it. The setups or transaction data can be changed if they are not satisfied with the created accounting.

This approach can be used for each journal entry allowing corrections iteratively before committing the final accounting. It minimizes the need to correct the journal entries and facilitates clean audit.

Online Accounting: By this users have the ability to create, view, transfer and post accounting in GL when entering transactions into Oracle sub ledger applications like Oracle Payables and Oracle Receivables. Same accounting rules and validation are used for both offline and online accounting. Sub ledger Accounting therefore supports straight through processing from the
Sub ledger transaction to General Ledger balances. 

Replacement Accounts: If an account is disabled, Oracle SLA replaces the disabled account with the replacement account and continues processing. This improves efficiency and minimizes user intervention.

Transaction Account Builder: It provides a flexible mechanism to derive default accounts for transactions. Rules can be created for either an account combination or individual segments.

Errors accounting and reporting: SLA creates journal as completely as possible, though there are conditions that make it invalid. These are not eligible for posting and transfer to GL. But these can be useful for troubleshooting setup and information issues.

3. On-line Inquiries:

Oracle SLA provides for multiple inquiries to view accounting information. It takes advantage of the Oracle personalization framework that allows users to customize their view of the accounting using any of the attributes of the journal entry and to save predefined searches.

Standard Reports: Oracle SLA reports are built to allow users to take full advantage of the features of Oracle XML Publisher. It delivers data definitions and XML Publisher templates for the following reports.

1. Journal Entries Report: This report provides detailed journal entry information on a transaction-by-transaction basis for a period or period range.

2. Account Analysis Report: This report provides drill-down information about the movement on a particular account or account range for a period or period range.

3. Third Party Balances Report: This report provides balances and account activity information for suppliers and customers for a period or period range.

4. Open Account Balances Listing: This report gives users the ability to net account activity across all the journals related to a document, and to reconcile the outstanding amounts with the GL balances. This report replaces existing product functionality such as the Payables Trial Balance.


Benefits of R12 Sub ledger Accounting


Enhanced Reporting Currency:  Multiple Reporting Currencies functionality is enhanced to support all journal sources. Reporting set of books is now known as reporting currencies. Every journal that is posted in the primary currency of a ledger can be automatically converted into one or more reporting currencies. We can choose to convert any journal sources and categories.

Speedy Process: SLA improves efficiency by speeding period close, simplifying business and regulatory changes and making acquisitions easier.

Maintenance: Minimization of maintenance and elimination of duplication makes accounting policies easier to implement, maintain and hence, control.

Global Accounting Engine: SLA replaces the Global Accounting Engine and further extends its functionality by providing customizable accounting rules via a flexible and robust accounting rules setup.


Tuesday, April 24, 2012 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.