R12 White Paper on Automatic Creation of Bank Account Transfer Through Sweep Transaction and Cash Leveling Program

Oracle Applications White Paper R12 White Paper on Automatic Creation of Bank Account Transfer through Sweep Transaction/Cash Leveling Program Author: Asharani Krishnappa, Rupali Ghosh, Meha Agrawal Reviewed By: Vinutha Ram Manohar Creation Date: 15-OCT-2015 Last Updated: 15-OCT-2015 Version: 1.0 Status: FINAL

Oracle/Client Oracle/Client Confidential - For internal use only

Page 1

Contents 1.

2.

OBJECTIVE OBJECTIVE ................................. ............................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................ ........................ .......... 3 1.1

SCOPE OF THE DOCUMENT...... .......... ........... .......... ........... .......... ........... .......... ........... .......... .......... . 3

1.2

TERMS USED.............. USED............................ ............................. ............................. ............................ ............................ ............................ ............................ ............................ ........................ ..........3

INTRODUCTION INTRODUCTION ................................. ............................................... ............................ ............................ ............................ ............................ ............................ ............................. ............................. ..............3 2.1 BENEFITS OF CASH CASH POOLING TECHNIQUES: .................... .......... .......... ........... .......... .......... ........... ...... 4 2.2 BUSINESS BUSINESS SCENARIO SCENARIO .......................... ........................................ ............................ ............................. ............................. ............................ ............................ ............................ ................... .....4

3.

STEPS FOR CREATING BAT THROUGH SWEEP TRANSACTION GENERATION PROGRAM ........... .... 4 3.1 GENERAL GENERAL SETUP ........................... ......................................... ............................ ............................. ............................. ............................ ............................ ............................ .......................... ............4 3.2 BANK ACCOUNT TRANSFER/CASH FLOW USING SWEEP TRANSACTION GENERATION PROGRAM PROGRAM .......................... ......................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................. ... 7 3.3 BANK ACCOUNT TRANSFER USING GENERATE CASH LEVELING PROGRAM ...... .......... ........... 22

4.TECHNICAL 4.TECHNICAL ASPECT ASPECT .......................... ......................................... ............................. ............................ ............................ ............................ ............................ ............................ ............................ ................ 34 5. REFERENCES REFERENCES ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................ ........................... .............35


Page 2

1. OBJECTIVE To provide: 1. An overview of the Cash P ooling functionality. 2. Step by step guide to complete the bank account transfer through ‘Sweep Transaction Generation Program P rogram’’ and ‘Cash Leveling Program’ Program’. 3. Brief overview of the table level data at different stages of the bank account transfer business transactions.

1.1 SCOPE OF THE DOCUMENT This paper will cover: 1.

Reconciling Bank-initiated Sweep Transactions

2.

Step by step guide to create Cashflows using following programs:



Generate Sweep Transactions



Cash Leveling

3.

Reconciliation Reconciliation & Accounting of Cashflows generated

This paper will not cover: 1.

Setting up Bank Accounts

2.

Explanation of different bank statement loading formats.

3.

Technical aspects of loading and importing bank statement.

4.

Creation of manual cash flow using bank account transfer web page.

5.

Creation of BAT from Treasury module

1.2 TERMS USED 1

CM Responsibility: Cash Management Responsibility

2.

BAT: Bank Account Transfer

2. INTRODUCTION Cash Pooling Techniques are used by the Organizations to optimize the funds by consolidating internal bank balances across multiple bank accounts. It is usually performed on a daily b asis. This technique is employed by by companies holding holding funds at financial institutions. institutions . Cash poolin Cash pooling g allows companies to to combine their their credit and and debit positions in various various accounts into one account. You can create the following two types of cash p ools: 

similar to zero balancing without the actual funds Notional Cash Pool: This type of cash pool is used for cash leveling similar movement.



Physical Cash Pool: This type of cash pool is used for cash leveling wherein the user can initiate fund transfers or

mirror cash transfers performed by the bank.


Page 3

Oracle supports following types of Physical cash pools: 

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 b ased on their preferences. 

Bank-Initiated Physical Cash Pools: 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 why Bank-Initiated Physical Cash Pools are often called as Zero Balance Accounts.

2.1 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.

NOTE :In 11i, this functionality was available for Oracle Treasury users, but now it is supported by Oracle Cash management in

R12.

2.2 BUSINESS SCENARIO To demonstrate the Bank Account Transfer feature using cash pooling, the following example is demonstrated: 

Create a Cash Pool with concentrated bank account and subaccounts.



Transfer funds between concentrated bank account and subaccount.



Generate Payment Instruction.



Reconcile the transaction



Create Accounting

3. STEPS FOR CREATING BAT THROUGH SWEEP TRANSACTION GENERATION PROGRAM This feature allows user to create account transfers between internal bank accounts automatically through physical cash pools. The related cash flows are stored in Oracle Cash Management for reporting purposes. This will give Cash Manager opportunity to invest all idle cash and will eliminate interest charges for bank accounts with a negative balance.

3.1 GENERAL SETUP 3.1.1 Setting profile option CE: BANK ACCOUNT TRANSFER The system profile option "CE: Bank Account Transfers" defines where the cash transfers will be created as a result o f the cash pool activity. If you choose Cash Management, then the cash transfers created by the cash leveling or ZBA sweep activity will be created in Cash Management using the Bank Account Transfer framework. Navigation: System Administrator -> Profile ->System -> CE: Bank Account Transfer

Oracle/Client Confidential - For internal use only

Page 4

Other value for profile CE: Bank Account Transfers is ‘Treasury’. If user choo se this value, then these cash transfers will be created in Treasury using Inter-Account Transfers (if both bank accounts belong to the same legal entity) or Intercompany Funding transactions (if bank accounts belong to different legal entities).

3.1.2 CREATION OF BANK ACCOUNT (CONCENTRATED AND SUBACCOUNTS) Navigation: Cash Management Responsibility -> Setup -> Bank -> Bank Accounts In below screenshots, one Concentrated Bank Account and 4 Sub Accounts are created.


Page 5


Page 6

3.2 BANK ACCOUNT TRANSFER/CASH FLOW US ING SWEEP TRANSACTION GENERATION PROGRAM 3.2.1 CREATE A PHYSICAL CASH POOL 1.

Navigation: Cash Management Responsibility -> Cash Pool

2.

Select Physical Cash Pool.

3.

In the Create Physical Cash Pool Header Information page, enter a unique name for your cash pool.

4.

In the Concentration Type field, select either One Account or Two Accounts to indicate if the cash pool will have one or two concentration accounts.

5.

In the Effective Date From field, enter the first date that the physical cash pool is active, such as the first date the cash leveling transactions can be created.

6.

In the Effective Date To field, enter the last date that the physical cash` pool is active.

7.

In the Bank Initiated Sweeps field, select Yes .

NOTE :All bank accounts in the cash pool have to share the same currency.


Page 7

8.

Select Next to assign sub accounts.

9.

Select next to enter target balances if required.

Note: If target balances for sub accounts included in the cash pool have already been defined in the bank account setup, they will default here.

10. Select next and enter payment method in bank account transfer attributes page


Page 8

11. Click Finish to save the cash pool.

3.2.2 DEFINE TRANSACTION CODES FOR CONCENTRATION BANK ACCOUNT. Bank account mapping template will consider these transaction codes while loading bank statement. Navigation: CM -> Setup -> Bank -> Transaction Codes

3.2.3 DEFINE MAPPING TEMPLATE User can use any of the existing mapping templates, In this scenario seeded BAI2 template is used. 1.

Header mapping template details.


Page 9

2.

Lines mapping template details.

3.2.4 LOAD THE BANK STATEMENT FOR CONCENTRATION ACCOUNT. Sample data file has been provided below


Page 10

3.2.5 RUN THE BANK STATEMENT LOADER TO LOAD AND IMPORT BANK STATEMENT. 1. Statement Loader

2. Import Statement


Page 11

3.2.6 REVIEW THE BANK STATEMENT LOADED Navigation: CM Responsibility=>Bank Statements=>Bank Statements and Reconciliation 1.

Query the Bank Statement and click on Lines.


Page 12

2.

Review the line details.

3.

Click on Reference Tab and Enter the Agent Bank Account.

Note : Agent Bank account should be the sub account. Sweep Transaction generation will not work if this column is blank. Bank

Account transfer can happen between a concentrated bank account and subaccount or vice versa but transfer between two concentrated bank account or two sub account is not supported.

3.2.7 RUN SWEEP TRANSACTION GENERATION PROGRAM TO GENERATE BANK ACCOUNT TRANSFERS 1.

Run Sweep Transaction Generation Program.


Page 13

2.

Sweep Transaction Generation Output.

3.2.8: REVIEW THE BANK ACCOUNT TRANSFER Navigation: CM responsibility=>Bank account transfer. Query the Transfer number 522 from Sweep Transaction Generation Output.

NOTE:Bank account transfer created through this program will have ‘Settled’ status, Here settlement through o racle payment is

not requirement. Oracle/Client Confidential - For internal use only

Page 14

3.2.9: REVIEW THE CASH FLOW GENERATED FOR BANK ACCOUNT TRANSFER Navigation: Bank Account Transfer=>Query Bank account transfer number=>Click on Transfer number=>View Cashflows 1.

Query the Transfer number 522 from Sweep Transaction Generation Output.

NOTE: Status of the Cash Flow is ‘Created’.

3.2.10 RECONCILE THE CASH FLOW AGAINST THE STATEMENT LINE UPLOADED Cashflow number 481 (Cash Outflow) 1.

Navigation: CM Responsibility=>Bank Statements=>Bank Statements and Reconciliation

2.

Query concentrated Bank Account 96630469675

3.

Click on Review


Page 15

4.

Click on Lines

5.

Click On “Sweep Out “ statement line

6.

Click on Available button to find the cash flows

7.

Enter the Cash Flow number 481 generated for BAT (Refer screen sh ot 3.2.9) as shown below:


Page 16

8.

Select the record and Reconcile

9.

Review the status of the Statement line as ‘Reconciled’.

Note: You can also submit Auto Reconciliation Program to r econcile the statement Lines Similarly to Reconcile the Cashflow number 482(Cash Inflow), bank statement for Sub account ‘96630469676’ should be loaded. Please refer above steps to load bank statement 1.

Navigation: CM Responsibility=>Bank Statements=>Bank Statements and Reconciliation

2.

Query concentrated Bank Account 96630469676

3.

Click on Review => Lines


Page 17

4.

Click On “Sweep In “ statement line

5.

Click on Available button to find the cash flows

6.

Enter the Cash Flow number 482 generated for BAT (Refer screen shot 3.2.9) as shown below:

7.

Select the record and Reconcile


Page 18

8.

Review the status of the Statement line as ‘Reconciled’.

Finally review the status of Cashflows, It is ‘Reconciled’.


Page 19

3.2.11: RUN CREATE ACCOUNTING Navigation: View =>Request=>Submit

3.2.12: REVIEW ACCOUNTING GENERATED FOR RECONCILED CASHFLOW Navigation: CM Responsibility=>Bank Account Transfer=>Search Transfer number 522 =>Click on Transfer Number=>View Cashflows => Click on Cashflow number.

NOTE: Status of the Cash Flow is ‘Reconciled’


Page 20

Click on View Accounting Details.

Click on View Journal Entries, to review Journals.

NOTE: For Bank Account Transfer Created Event, accounting entry will not b e created. This is standard functionality.


Page 21

Review Journals Entries.

3.3 BANK ACCOUNT TRANSFER USING GENERATE CASH LEVELING PROGRAM This feature allows user to create account transfers between internal bank accounts automatically through ph ysical cash pools. The related cash flows are stored in Oracle Cash Management for reporting purposes. For physical cash pools that do not have bank initiated sweeps, user has option to generate Intercompany Funding Transactions and/or Inter-Account transfers from the cash leveling proposal. These transfers can then be included in the payment file and sent to the bank.

3.3.1 CREATE PHYSICAL CASH POOL Navigation: Cash Management Responsibility -> Cash Pool The process to create a cash pool is same as explained in section 3.2.1

NOTE: The Bank Initiated Sweep should be No for this process


Page 22

3.3.2 : CREATE CASH POSITIONING Navigation: CM responsibility => Cash positioning 1.

Create Cash Position Worksheet

A worksheet contains cash position specifications to determine the presentation of sources (rows) and bank accounts (columns) for cash position. Cash position worksheets can be customized by s pecifying the sources and transaction source defaults or user can choose to manually set the parameters for transaction source. User can save the worksheet and use it to generate Daily cash position. a.

Enter Worksheet Name.

b.

Cash Pools => Click on Set Manually.

Note : Since Cash Pool is used as a source to generate daily cash balances, user needs to enter the Cash Pool name.


Page 23

c.

2.

Enter the Cash pool name ‘Cash Pool Leveling’ (Refer Screenshot 3.3.1) and click on ‘Apply’.

Generate Cash Position a.

Click on Generate Cash position


Page 24

b.

In View By section : Select Cash pool and Click on Apply

Note: Cash Position will list all the balances of the queried bank account. When a transfer is initiated from here it should automatically take the relevant amount for the bank account from Cash Position results.If your cash p osition includes cash pools, you can select to generate a cash leveling proposal from the Cash Position Results Page, in the Bank Account View Page or Cash Pool View Page.

c.

Click On Generate Cash Leveling Proposal


Page 25

d.

Enter the Transfer Amount and Click on Apply

e.

Click on Generate.


Page 26

NOTE:The Generate Cash Leveling Fund Transfers concurrent program is initiated

Check the status of request in Concurrent Request window.


Page 27

View the Cash Leveling Execution Report output.

Note the Bank account transfer number 542, 544.

3.3.3 REVIEW THE BANK ACCOUNT TRANSFER CREATED Navigation: CM responsibility=>Bank account transfer. Query the Transfer number 542 from Cash Leveling Execution Report output.

NOTE: The bank account transfer created through Cash Leveling program will always have the status as “Settlement in Progress”. This means it requires settlement through Oracle Payments. Oracle/Client Confidential - For internal use only

Page 28

3.3.4 SETTLE THE BANK ACCOUNT TRANSFER 1.

Navigate to Funds Disbursement Responsibility.

2.

Click on Take action button for the PPR created against the Bank account transfer.

Note : The PPR number can be found from below query: select Payment_request_number from CE_PAYMENT_TRANSACTIONS where TRXN_REFERENCE_NUMBER = &Bank_transfer_number

3.

Enter the Payment Process Pro file name, Select action ‘Run Payment Process’ and click on Go.


Page 29

4.

Run create Electronic Payment Instruction program (based on what is the Processing Type of Payment Process Profile)

5.

Click continue


Page 30

6.

Click Submit Button

7.

Review the Bank Account Transfer status

NOTE: For more details on settlement of BAT, refer Oracle metalink doc R12 White Paper on Bank Account Transfer for

Oracle Cash Management (Doc ID 1276861.1)


Page 31

3.3.5 : RECONCILE THE CASH FLOWS Navigate: CM Responsibility=>Manual Clearing=>Clear Transactions 1.

Enter the source Bank Account Number.

2.

In Transaction tab Enter the Cash Flow Number and click on Find.

Navigation to get Cash Flow Number: Bank Account Transfer=>Query Bank account transfer number 542 =>Click on Transfer number=>View Cashflows


Page 32

3.

Clear the transactions

4.

After the Cash Flows are cleared, run Create accounting as explained above in 3.2.11


Page 33

4.TECHNICAL ASPECT 1.

2.

3.

The bank statements are stored in following interface tables: a.

CE_STATEMENT_HEADERS_INT: Unique identifier is STATEMENT_NUMBER ,STATEMENT_HEADER_ID.

b.

CE_STATEMENT_LINES_INTERFACE : Unique identifier is STATEMENT_LINE_ID.

Base Table for bank statement data: a.

CE_STATEMENT_HEADERS : Unique identifier is STATEMENT_NUMBER and STATEMENT_HEADER_ID.

b.

CE_STATEMENT_LINES : Unique identifier is STATEMENT_LINE_ID.

Reconciliation tables: CE_STATEMENT_RECONCILS_ALL : Unique identifier is REFERENCE_ID(Cashflow_id/Check_id/receipt_id).

REFERENCE_TYPE: Determines whether the transaction is a Payment/receipt/cashflow.

4.

Bank account transfer is stored in below table: CE_PAYMENT_TRANSACTIONS: Unique identifier is TRXN_REFERENCE_NUMBER, this stores the BAT number.

SETTLE_BY_SYSTEM_FLAG: Determines whether the settlement is required through oracle Payments. TRXN_STATUS_CODE: Determines the status o f BAT. CASHPOOL_ID: Determines the cashpool through which BAT was created. For manual BAT, cashpool_id will be NULL

5.

Cashflows are stored in below table: CE_CASHFLOWS : Unique identifier is CASHFLOW_ID.

SOURCE_TRXN_TYPE: Determines the source o f cashflow.

6.

Accounting details of Cashflow are stored in below table CE_CASHFLOW_ACCT_H : Unique identifier is Cashflow_id and Event_id.

7.

Cashpool tables CE_CASHPOOLS: Stores the cashpool header details. Unique Identifier is CASHPOOL_ID CE_CASHPOOL_SUB_ACCTS: Stores the Cashpool sub-account information. Unique identifier is

CASHPOOL_SUB_ACCT_ID CE_PROPOSED_TRANSFERS: Stores Information about proposed cash leveling transfers. This table will be purged

after all Cash leveling fund transfers(For a particular Cash Leveling Proposal) is complete. Unique Identifier is PROPOSED_TRANSFER_ID


Page 34

R12 White Paper on Automatic Creation of Bank Account Transfer Through Sweep Transaction and Cash Leveling Program

Recommend Documents