Period End Close- Oracle Financials

Payables

1) Ensure all invoices are Validated

2) Ensure all invoices are Accounted

3) Payables will transfer only those invoices which are in the status of Validated.

4) Run the following reports:

Unaccounted Transactions Report- Gives you the details of Unaccounted transactions for the period

Payable Accounting Process Report- For Invoices pending to be accounted

Payables Transfer to General Ledger - Transfer the invoices and payment details to General Ledger

Mass Additions Report- Transfer the asset related invoices into Oracle Fixed Assets module

5) Close the Period

Receivables

1) Ensure all transactions are completed

2) Ensure all Receipts are entered and status changed to Cleared

3) Once everything is completed, Click on Navigation Control>General Ledger and run the report. This will transfer the details to General Ledger

4) Close the Period

Assets

1) Ensure additions, adjustments, retirements and others details are completed for all the assets in a particular period

2) Ensure current period additions are transferred from Accounts payable thru Post Mass Additions

3) Run Depreciation Projection report to see the depreciation for the current period. Randomly check the depreciation amount for different categories

4) Run the report Create Journal Entries process which will transfer entries to General Ledger

Cash Management

1) Enter the Bank statement details for each bank in Oracel Cash Management module

2) Reconcile the Payments and Receipts details manually

3) Then Run below reports

Bank Statement Detail Report- This report will show transactions for each bank

GL Reconciliation Report

General Ledger

1) Make sure all the journals are transferrred to General Ledger. Check whether all the details transferred from sub-ledger

2) Review the Journals and Post the details once all the details are correct

3) Run the Trial Balance Report

R12 Banks Setup Step by Step in oracle apps

Many of you might have known it earlier, but people who don’t know, setup of Banks are moved into the Cash Management Module. The short name for Cash Management is CE.  In this Article, we will discuss how to Create Banks / Bank Branches , step by step.

Responsibility – Cash Management Super User
Navigation – Setup – Banks – Banks


Clicking on this opens an OAF page

Click on Create – opens the following.

Enter the necessary details required and click Save and Next.

Update the Bank Addresses by clicking on Create

Enter the information and click on Finish

Click on Save and Next

Click on Create Contact .
Enter the necessary information required.


Click Apply once the details are entered.

Click on Finish.  The bank details are here in this screen.

Bank is Created Successfully. Now we will see how to create a Branch underneath the Bank which we just Created.
Click on Create Branch icon

Click on Continue from the below screen

In the below screen enter the Branch Details as shown

Click Save and Next.

Click on Create button in the above screen to add the Branch’s address

Enter the address and then click Apply.

Click Save and Next

Click on Create Contact button to add the Contact Details


Click on Apply

Click on Finish.

To Create Account – click on the Icon below…

Click on Create button from the below screen

Enter the details in the screen below

Click on Continue

Enter the necessary details in the screen above

Click on Next

Click on Save and Next

Cash Pooling in Cash Management - R12

Cash Pooling Techniques are used by the Organizations to optimize the funds by consolidating bank balances across multiple bank accounts.

Benefits of Cash Pooling Techniques:

- Minimizes the idle funds by consolidating balances
- Helps to decrease external borrowing costs and increase investment returns
- Allows users to group bank accounts into pooling structures to manage funds effectively

Oracle supports following types of cash pools:

1. Self-Initiated Physical Cash Pools:

When Organizations want to monitor individual bank account balances manually and then physically move cash to or from their accounts based on their preferences, Self-Initiated Physical Cash Pools structure can be used.

We can define the rules in pool definitions, to automatically determine when bank account transfers should be made and for what amounts.

2. Bank-Initiated Physical Cash Pools, or Zero Balance Accounts(ZBA’s):

Bank-Initiated Physical Cash Pools are used When Organizations want to sweep all end-of-day balances automatically to or from the main accounts.

This kind of services will leave zero balances at the end of way. That is the reason, Bank-Initiated Physical Cash Pools are often called as Zero Balance Accounts.

3. Notional Cash Pools:

If Organizations want to track the net balances across all accounts along with individual accounts, then Notional Cash Pools will be used.



In 11i, this functionality was available to Oracle Treasury users, but now it is supported by Oracle Cash Management in R12.

Cash Pooling Techniques are used by the Organizations to optimize the funds by consolidating bank balances across multiple bank accounts.

Benefits of Cash Pooling Techniques:

- Minimizes the idle funds by consolidating balances
- Helps to decrease external borrowing costs and increase investment returns
- Allows users to group bank accounts into pooling structures to manage funds effectively

Oracle supports following types of cash pools:

1. Self-Initiated Physical Cash Pools:

When Organizations want to monitor individual bank account balances manually and then physically move cash to or from their accounts based on their preferences, Self-Initiated Physical Cash Pools structure can be used.

We can define the rules in pool definitions, to automatically determine when bank account transfers should be made and for what amounts.

2. Bank-Initiated Physical Cash Pools, or Zero Balance Accounts(ZBA’s):

Bank-Initiated Physical Cash Pools are used When Organizations want to sweep all end-of-day balances automatically to or from the main accounts.

This kind of services will leave zero balances at the end of way. That is the reason, Bank-Initiated Physical Cash Pools are often called as Zero Balance Accounts.

3. Notional Cash Pools:

If Organizations want to track the net balances across all accounts along with individual accounts, then Notional Cash Pools will be used.



In 11i, this functionality was available to Oracle Treasury users, but now it is supported by Oracle Cash Management in R12.

Query to fetch Trading partner Details of Bank Branch for EDI Payments

SELECT DISTINCT cba.bank_account_name, 

                cvv.bank_branch_name, 

                bktph.tp_code

           FROM ce_bank_accounts cba,

                ce_bank_branches_v cvv,

                hz_contact_points hcp,

                ece_tp_headers bktph,

                ece_tp_details bktpd

          WHERE hcp.owner_table_id     = cba.bank_branch_id

            AND cvv.branch_party_id    = cba.bank_branch_id

            AND bktph.tp_header_id     = hcp.edi_tp_header_id(+)

            AND bktpd.tp_header_id     = bktph.tp_header_id(+)

            AND bktpd.document_id      = 'PYO'

            AND hcp.owner_table_name   = 'HZ_PARTIES'

            AND hcp.contact_point_type = 'EDI'

            AND bktpd.edi_flag         = 'Y';

TABLES INVOLVED IN BANK STATEMENT OPEN INTERFACE IN CASH MANAGEMENT

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.

Sample Script to Submit Bank Statement Loader Program

sample procedure to submit a bank statement loader program and capture the status of programs submitted as part of Bank Statement Import

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;