Tables in Cash Management R12

CE_BANK_ACCOUNTS:

CE_BANK_ACCOUNTS contains Legal Entity Level bank account information. Each bank account must be affiliated with one bank branch.  

CE_BANK_ACCT_USES_ALL:

CE_BANK_ACCT_USES_ALL stores Operating Unit level bank account use information.

CE_GL_ACCOUNTS_CCID: 

CE_GL_ACCOUNTS_CCID stores information about code combination ids per your bank account uses

 CE_INTEREST_RATES:

This table stores interest rate information  

CE_INTEREST_SCHEDULES:

This table stores interest schedule information

 CE_BANK_ACCT_BALANCES:

This table stores the internal bank account balances. 

CE_PROJECTED_BALANCES:

This table stores the projected balances of internal bank accounts.

CE_CASHFLOWS:

Table for storing Cashflows

CE_PAYMENT_TRANSACTIONS: 

Table for storing Bank Account Transfer 


CE_PAYMENT_TEMPLATES:

Table for storing payment templates 


CE_TRXNS_SUBTYPE_CODES:

Table for storing transaction subtype codes 


CE_CONTACT_ASSIGNMENTS:

This table contains the information about which level  (bank, branch, account) the contact is assigned to.

CE_AP_PM_DOC_CATEGORIES: 

This table stores payment document categories and payment methods for bank account uses

CE_PAYMENT_DOCUMENTS: 

This table stores payment document information.

CE_SECURITY_PROFILES_GT:

CE.CE_SECURITY_PROFILES_GT is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period.

CE_AVAILABLE_TRANSACTIONS_TMP: 

CE.CE_AVAILABLE_TRANSACTIONS_TMP is a global temporary table. The current session is able see data that it placed in the table but other sessions cannot. Data in the table is temporary. It has a data duration of SYS$SESSION. Data is removed at the end of this period.

CE_CHECKBOOKS: 

This table stores payment check book information.

CE_STATEMENT_RECONCILS_ALL: 

The CE_STATEMENT_RECONCILS_ALL table stores information about reconciliation history or audit trail. Each row represents an action performed against a statement line.


CE_ARCH_RECONCILIATIONS_ALL:

The CE_ARCH_RECONCILIATIONS_ALL table stores information about archived statement reconciliation details. A row in this table corresponds to an archived CE_STATEMENT_RECONCILES_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.


CE_ARCH_HEADERS:

The CE_ARCH_HEADERS_ALL table stores archived statement header information. Each row in this table corresponds to an archived CE_STATEMENT_HEADERS_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.

CE_ARCH_INTRA_HEADERS: 

The CE_ARCH_INTRA_HEADERS_ALL table stores archived intra-day statement header 
information. Each row in this table corresponds to an archived CE_INTRA_STMT_HEADERS_ALL record. This table is populated when you run the Archive/Purge Bank Statements program and choose to archive.

CE_ARCH_INTERFACE_HEADERS: 

The CE_ARCH_INTERFACE_HEADERS_ALL 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_INTRA_STMT_HEADERS:

The CE_INTRA_STMT_HEADERS_ALL table stores intra-day bank statement header information. Each row in this table contains the statement date, statement number, bank account identifier, and other information about the intra-day statement.

CE_STATEMENT_HEADERS: 

The CE_STATEMENT_HEADERS_ALL 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_CASHPOOLS:

This table stores header information about the cash pool 


CE_STATEMENT_LINES:

The CE_STATEMENT_LINES 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.

Cash Management Reconciliation

1) Run the program- Bank Statement Loader to load data in following Open Interface tables

CE_STATEMENT_HEADERS_INT_ALL (Stores Information about bank statement Header details)
CE_STATEMENT_LINES_INTERFACE (Stores Information about bank statement Line details)

2) Run the program- Bank Statement Import to load data in following Base tables from Interface tables.

CE_STATEMENT_HEADERS
CE_STATEMENT_LINES

3) Error information is stored in following tables while running Bank Statement Import

CE_HEADER_INTERFACE_ERRORS
CE_LINE_INTERFACE_ERRORS

4) Run the program- AutoReconciliation which in turn calls AutoReconciliation Execution Report (CEXINERR)
(AutoReconciliation --> CE_AUTO_BANK_REC.statement --> CE_AUTO_BANK_MATCH.match_process)

5) Error information is stored in following table while running AutoReconciliation Program

CE_RECONCILIATION_ERRORS

6) Reconciliation history or audit trail is stored in table- CE_STATEMENT_RECONCILS_ALL. Each row represents an action performed against a statement line.

7) Transactions from all the views below are consolidated into CE_AVAILABLE_TRANSACTIONS_V for bank statement reconcillation

CE_101_TRANSACTIONS_V -- GL journal entry lines 

CE_185_TRANSACTIONS_V -- Treasury Transactions

CE_200_TRANSACTIONS_V -- AP payments

CE_222_TRANSACTIONS_V -- AR cash receipts 

CE_260_TRANSACTIONS_V -- Bank statement lines available for reconciling bank errors

CE_801_TRANSACTIONS_V -- Payroll payments

References:
http://www.oracleerp4u.com/2010/10/cash-management-reconciliation.html

IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

Below post will explain the step involved in importing an external bank account in Oracle Apps R12.

STEP 1: CREATE PARTY for BANK in TCA

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank

SCRIPT: 

Test Instance: R12.1.1

Script:

set serveroutput on;

DECLARE

  

   v_error_reason             VARCHAR2 (2000);  

   v_msg_data                 VARCHAR2 (1000);

   v_msg_count                NUMBER;

   v_return_status            VARCHAR2 (100); 

   v_extbank_rec_type         iby_ext_bankacct_pub.extbank_rec_type;

   x_response                 iby_fndcpt_common_pub.result_rec_type;  

   x_bank_id                  NUMBER;

  

BEGIN

   v_error_reason                           := NULL;

   v_return_status                          := NULL;

   v_msg_count                              := NULL;

   v_msg_data                               := NULL;

   v_extbank_rec_type.object_version_number := 1.0;

   v_extbank_rec_type.bank_name             := 'TEST SHARE';

   v_extbank_rec_type.bank_number           := '14589';

   v_extbank_rec_type.institution_type      := 'BANK';

   v_extbank_rec_type.country_code          := 'US';

   v_extbank_rec_type.description           := 'Create via API';

  

   iby_ext_bankacct_pub.create_ext_bank

                    (p_api_version        => 1.0,

                     p_init_msg_list      => fnd_api.g_true,

                     p_ext_bank_rec       => v_extbank_rec_type,

                     x_bank_id            => x_bank_id,

                     x_return_status      => v_return_status,

                     x_msg_count          => v_msg_count,

                     x_msg_data           => v_msg_data,

                     x_response           => x_response

                    );

  

   DBMS_OUTPUT.put_line ('v_return_status = '||v_return_status);

   DBMS_OUTPUT.put_line ('v_msg_count = '||v_msg_count);

   DBMS_OUTPUT.put_line ('v_msg_data = '||v_msg_data);

   DBMS_OUTPUT.put_line ('x_bank_id = '||x_bank_id);

   DBMS_OUTPUT.put_line ('x_response.Result_Code = ' || x_response.result_code);

   DBMS_OUTPUT.put_line (   'x_response.Result_Category = '

                         || x_response.result_category

                        );

   DBMS_OUTPUT.put_line (   'x_response.Result_Message = '

                         || x_response.result_message

                        );

   IF v_return_status <> fnd_api.g_ret_sts_success

   THEN

      IF v_msg_count >= 1

      THEN

         FOR i IN 1 .. v_msg_count

         LOOP

            IF v_error_reason IS NULL

            THEN

               v_error_reason :=

                  SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),

                          1,

                          255

                         );

            ELSE

               v_error_reason :=

                     v_error_reason

                  || ' ,'

                  || SUBSTR (fnd_msg_pub.get (p_encoded =>fnd_api.g_false),

                             1,

                             255

                            );

            END IF;

            DBMS_OUTPUT.put_line ('BANK API ERROR-' || v_error_reason);

         END LOOP;

      END IF;

   END IF;

END;

STEP2: CREATE PARTY for BANK BRANCH in TCA

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

Test Instance: R12.1.1

Script:

SET SERVEROUTPUT ON;

DECLARE

   p_api_version           NUMBER                 := 1.0;

   p_init_msg_list         VARCHAR2 (1)           := 'F'; 

   x_return_status         VARCHAR2 (2000);

   x_msg_count             NUMBER (5);

   x_msg_data              VARCHAR2 (2000);

   x_response              iby_fndcpt_common_pub.result_rec_type;

   p_ext_bank_branch_rec   iby_ext_bankacct_pub.extbankbranch_rec_type;

   v_bank_id               NUMBER := 208787; -- EXISTING BANK PARTY ID

   x_branch_id             NUMBER;

   p_count                 NUMBER;

BEGIN

   DBMS_OUTPUT.put_line ('BEFORE BANK BRANCH API');

   p_ext_bank_branch_rec.bch_object_version_number := 1.0;

   p_ext_bank_branch_rec.branch_name := 'TEST BANK BRANCH';

   p_ext_bank_branch_rec.branch_type := 'ABA';

   p_ext_bank_branch_rec.bank_party_id := v_bank_id; 

  

   IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH

                (p_api_version              => p_api_version,

                 p_init_msg_list            => p_init_msg_list,

                 p_ext_bank_branch_rec      => p_ext_bank_branch_rec,

                 x_branch_id                => x_branch_id,

                 x_return_status            => x_return_status,

                 x_msg_count                => x_msg_count,

                 x_msg_data                 => x_msg_data,

                 x_response                 => x_response

                );

   DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);

   DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);

   DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);

   DBMS_OUTPUT.put_line ('x_branch_id = ' || x_branch_id);

   DBMS_OUTPUT.put_line ('x_response.Result_Code = ' || x_response.result_code);

   DBMS_OUTPUT.put_line (   'x_response.Result_Category = '

                         || x_response.result_category

                        );

   DBMS_OUTPUT.put_line (   'x_response.Result_Message = '

                         || x_response.result_message

                        );

   IF x_msg_count = 1

   THEN

      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);

   ELSIF x_msg_count > 1

   THEN

      LOOP

         p_count := p_count + 1;

         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next,fnd_api.g_false);

         IF x_msg_data IS NULL

         THEN

            EXIT;

         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

   END IF;

END;

STEP3: CREATE ADDRESS for BANK BRANCH as LOCATION in TCA

API Involved: HZ_LOCATION_V2PUB.CREATE_LOCATION

Below wrapper script will help you create a valid Location in the table HZ_LOCATIONS.

Test Instance: R12.1.3

API: HZ_LOCATION_V2PUB.CREATE_LOCATION

Note: Value for created_by_module must be a value defined in lookup type HZ_CREATED_BY_MODULES in the table FND_LOOKUP_VALUES

SCRIPT:

SET SERVEROUTPUT ON;

DECLARE

 p_location_rec   HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;

 x_location_id    NUMBER;

 x_return_status  VARCHAR2(2000);

 x_msg_count      NUMBER;

 x_msg_data       VARCHAR2(2000);

BEGIN

 p_location_rec.country           := 'US';

 p_location_rec.address1          := 'Shareoracleapps';

 p_location_rec.city              := 'san Mateo';

 p_location_rec.postal_code       := '94401';

 p_location_rec.state             := 'CA';

 p_location_rec.created_by_module := 'BO_API';

 DBMS_OUTPUT.PUT_LINE('Calling the API hz_location_v2pub.create_location');

 HZ_LOCATION_V2PUB.CREATE_LOCATION

           (

             p_init_msg_list => FND_API.G_TRUE,

             p_location_rec  => p_location_rec,

             x_location_id   => x_location_id,

             x_return_status => x_return_status,

             x_msg_count     => x_msg_count,

             x_msg_data      => x_msg_data);

 IF x_return_status = fnd_api.g_ret_sts_success THEN

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Creation of Location is Successful ');

    DBMS_OUTPUT.PUT_LINE('Output information ....');

    DBMS_OUTPUT.PUT_LINE('x_location_id: '||x_location_id);

    DBMS_OUTPUT.PUT_LINE('x_return_status: '||x_return_status);

    DBMS_OUTPUT.PUT_LINE('x_msg_count: '||x_msg_count);

    DBMS_OUTPUT.PUT_LINE('x_msg_data: '||x_msg_data);   

ELSE

    DBMS_OUTPUT.put_line ('Creation of Location failed:'||x_msg_data);

    ROLLBACK;

    FOR i IN 1 .. x_msg_count

    LOOP

      x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line( i|| ') '|| x_msg_data);

    END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('Completion of API');

END;

/

STEP4: CREATE PARTY SITE for BANK BRANCH with LOCATION created in above step

API Involved: HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

DESCRIPTION: This routine is used to create a Party Site for a party. Party Site relates an existing party from the HZ_PARTIES table with an address location from the HZ_LOCATIONS table.The API creates a record in the HZ_PARTY_SITES table. You can create multiple party sites with multiple locations and mark one of those party sites as identifying for that party. The identifying party site address components are denormalized into the

HZ_PARTIES table. If orig_system is passed in, the API also creates a record in the HZ_ORIG_SYS_REFERENCES table to store the mapping between the source system reference and the TCA primary key.

API:   HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

BASE TABLES AFFECTED :  HZ_PARTY_SITES

TEST INSTANCE : R12.1.3

NOTES:

Enter the values for Party Id and Location Id as valid values from HZ_PARTIES and HZ_LOCATIONS respectively.

SELECT party_id    FROM hz_parties;

SELECT location_id FROM hz_locations;

SCRIPT:

SET SERVEROUTPUT ON;

DECLARE

p_party_site_rec    HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;

x_party_site_id     NUMBER;

x_party_site_number VARCHAR2(2000);

x_return_status     VARCHAR2(2000);

x_msg_count         NUMBER;

x_msg_data          VARCHAR2(2000);

BEGIN

-- Setting the Context --

mo_global.init('AR');

fnd_global.apps_initialize ( user_id      => 1318

                            ,resp_id      => 50559

                            ,resp_appl_id => 222);

mo_global.set_policy_context('S',204);

fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters

p_party_site_rec.party_id                 := 530682;

p_party_site_rec.location_id              := 28215;

p_party_site_rec.identifying_address_flag := 'Y';

p_party_site_rec.created_by_module        := 'BO_API';

DBMS_OUTPUT.PUT_LINE('Calling the API hz_party_site_v2pub.create_party_site');

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE

                   (

                    p_init_msg_list     => FND_API.G_TRUE,

                    p_party_site_rec    => p_party_site_rec,

                    x_party_site_id     => x_party_site_id,

                    x_party_site_number => x_party_site_number,

                    x_return_status     => x_return_status,

                    x_msg_count         => x_msg_count,

                    x_msg_data          => x_msg_data

                           );

IF x_return_status = fnd_api.g_ret_sts_success THEN

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Creation of Party Site is Successful ');

    DBMS_OUTPUT.PUT_LINE('Output information ....');

    DBMS_OUTPUT.PUT_LINE('Party Site Id     = '||x_party_site_id);

    DBMS_OUTPUT.PUT_LINE('Party Site Number = '||x_party_site_number);

ELSE

    DBMS_OUTPUT.put_line ('Creation of Party Site failed:'||x_msg_data);

    ROLLBACK;

    FOR i IN 1 .. x_msg_count

    LOOP

      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line( i|| ') '|| x_msg_data);

    END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('Completion of API');

END;

/

STEP 5: CREATE BANK ACCOUNT in IBY using BANK_ID, BRANCH_ID created in STEP1&2

API involved: IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

Test Instance: R12.1.1

Script:

SET SERVEROUTPUT ON;

DECLARE

   p_api_version         NUMBER               := 1.0;

   p_init_msg_list       VARCHAR2(1)          := 'F';

   x_return_status       VARCHAR2(2000);

   x_msg_count           NUMBER(5);

   x_msg_data            VARCHAR2(2000);

   x_response            iby_fndcpt_common_pub.result_rec_type;

   p_ext_bank_acct_rec   iby_ext_bankacct_pub.extbankacct_rec_type;

   v_supplier_party_id   NUMBER := 55816;  -- EXISTING SUPPLIERS/CUSTOMER     

                                              PARTY_ID

   v_bank_id             NUMBER := 208587; -- EXISTING BANK PARTY ID

   v_bank_branch_id      NUMBER := 278411; -- EXISTING BRANCH PARTY ID

   x_acct_id             NUMBER;

   p_count               NUMBER;

BEGIN

   p_ext_bank_acct_rec.object_version_number    := 1.0;

   p_ext_bank_acct_rec.acct_owner_party_id      := v_supplier_party_id;

   p_ext_bank_acct_rec.bank_account_name        := 'XXTEST BANK ACCNT';

   p_ext_bank_acct_rec.bank_account_num         := 14278596531;

   p_ext_bank_acct_rec.alternate_acct_name      := 'XXTEST BANK ACCNT ALT';

   p_ext_bank_acct_rec.bank_id                  := v_bank_id;

   p_ext_bank_acct_rec.branch_id                := v_bank_branch_id;

   p_ext_bank_acct_rec.start_date               := SYSDATE;

   p_ext_bank_acct_rec.country_code             := 'US';

   p_ext_bank_acct_rec.currency                 := 'USD';

   p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';

   p_ext_bank_acct_rec.payment_factor_flag      := 'N';

   IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT

                (p_api_version            => p_api_version,

                 p_init_msg_list          => p_init_msg_list,

                 p_ext_bank_acct_rec      => p_ext_bank_acct_rec,

                 x_acct_id                => x_acct_id,

                 x_return_status          => x_return_status,

                 x_msg_count              => x_msg_count,

                 x_msg_data               => x_msg_data,

                 x_response               => x_response

                );

 

   DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);

   DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);

   DBMS_OUTPUT.put_line ('x_msg_data = ' || x_msg_data);

   DBMS_OUTPUT.put_line ('x_acct_id = ' || x_acct_id);

   DBMS_OUTPUT.put_line ('x_response.Result_Code = ' || x_response.result_code);

   DBMS_OUTPUT.put_line (   'x_response.Result_Category = '

                         || x_response.result_category

                        );

   DBMS_OUTPUT.put_line (   'x_response.Result_Message = '

                         || x_response.result_message

                        );

   IF x_msg_count = 1

   THEN

      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);

   ELSIF x_msg_count > 1

   THEN

      LOOP

         p_count := p_count + 1;

         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next,fnd_api.g_false);

         IF x_msg_data IS NULL

         THEN

            EXIT;

         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);

      END LOOP;

   END IF;

END;