12i Cash Management new features - PART 1

I. Bank Account Balances and Interest Calculations:

In prior releases, bank account balances were only available as a part of the bank account statement. The bank account interest calculation was only available for bank accounts set up in Treasury. In Release 12, the functionality to keep track of the multiple bank account balance types and calculate accrued interest is available to all internal bank accounts set up in the centralized bank account model.

Not only can you enter the balances manually, but also you can import them automatically at the same time when the bank statement is imported. In addition to the actual historic balances, you can keep track of the projected balances. Such balances can be entered manually or copied over from the Cash Position. You can then create reports that will compare the actual balances versus projected, and you can accomplish it in either an onscreen report or via XML Publisher. Finally, to simplify the bank account interest calculation, you can create reusable interest rate schedules that will contain the interest rates and other interest calculation parameters. Interest calculation features will work not only for stand-alone bank accounts but also for the notional cash pools as well.

Bank Account Balances and Interest Calculations - Benefits:
The new bank account model allows you to view bank account balances independent of the bank statement, calculate accumulated interest on the fly, and create customized balance reports.

Bank Account Balances and Interest Calculations Maintenance:
Once you have defined your bank accounts in the centralized bank account model, query them in the bank account balance page and manage historical or projected amounts.

Bank Account Balances and Interest Calculations Setup:
To obtain a balance report, create a report layout or a view and generate a report based on that. To calculate interest on the bank account balance, create the interest rate schedules, tie them to the bank accounts and use the interest calculator page to view the accumulated interest.

II. Bank Account Transfers:

In Release 12, you are able to create bank account transfers in Cash Management. The transfers can be initiated, approved, settled and accounted for. The settlement is done through the Payments application, while the accounting is done though the Subledger Accounting engine.

Bank Account Transfers Description:
Bank Account Transfers can be created manually by the user in the system. In addition, if there are any physical cash pools defined in the system, the transfers can be created automatically when the cash leveling process is run or when a bank statement with ZBA sweep lines is processed.

With manual transfers, you have the option of creating and using a payment template. The template will default the transfer information, such as the source and destination bank accounts, currency, and payment method, and can be used in the same fashion as a repetitive or semi-repetitive wire template created by your bank.

In cases when the settlement of the bank account transfer does not have to be initiated by the system (for example, for ZBA bank account transfers that the bank processes on its own), there is an option to exclude such a transfer from the settlement process and only create the accounting entries.

Finally, the UMX security model lets you define who can create bank account transfers for which legal entities. The settlement authorization function is also separate from the transfer creation, so you can implement the separation of duties for bank account transfer management.

Bank Account Transfers - Benefits:
The bank account transfer functionality enriches the Cash Management functionality so that you could take action on the projected closing balances calculated by the system. The seamless integration with the Payments application allows you to send payment instructions to the bank in a variety of payment formats and the integration with the Subledger Accounting allows you to use flexible journal creation rules.

Bank Account Transfers Process:
•Responsibility: Cash Management
•Navigation: Cash Management > Bank Account Transfers
Once the setup is in place, you can start creating the bank account transfer. If the system parameter requires authorization, the bank account transfer must be authorized before it is available for settlement or journal creation. Otherwise, you can proceed to settle or journalize the bank account transfer immediately after creation and validation.

The Payments application formats your payment request and sends it to the bank. Any exceptions in the payment process are communicated back in the form of an error status. If settlement of the bank account transfer errors out, you can see the reason so that the cause of the error can be rectified and the bank account transfer recreated. If the payment is processed without any exception, you see a successful payment status returned.

The subledger accounting process creates journal entries according to your setup and you can drill down to view these journal entries.

Bank Account Transfers – Dependencies and Interactions:
The bank account transfer feature depends on Payments application in cases where the settlement of the bank account transfers is required. There is also a dependency on the Intercompany setup when funds are transferred between different legal entities. Finally, all of the accounting activity for bank account transfers happens in the Subledger Accounting framework.

Bank Account Transfers Setup:
•Responsibility: Cash Management
•Navigation: Setup:System Parameters > (T) Cash Management Transactions
If you are using the cash leveling or ZBA features, the setup starts with the new system profile. Then, optionally, you can set up Transaction Subtypes and Payment Templates for bank account transfers. The Payment Templates are required if you intend to send the payment instructions to the bank to process the bank account transfer. The Transaction Subtypes are optional and can be used for reporting purposes.

Bank Account Transfers Setup – Set System Profile:
The new system profile option CE: Bank Account Transfers defines where the cash transfers will be created as a result of the cash pool activity. If you choose Cash Management, then the cash transfers created by the cash leveling or ZBA sweep activity are created in Cash Management using the Bank Account Transfer framework. If you choose Treasury, then these cash transfers are 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). Before Release 12, Bank Account Transfers could only be created in Treasury. This functionality is preserved but now you have a choice.

III. Subledger Accounting:

Subledger Accounting provides a common flexible framework for creating journal entries for Bank Account Transfers and Bank Statement Cash Flows in Cash Management. Prior to Release 12, Cash Management produced journal entries for bank statement activity based on simple rules and sent them to the General Ledger interface. In Release 12, in addition to the bank statement activity, a new source of accounting entries is available – bank account transfers – and the rules for journal entry creation are more flexible and sophisticated. Finally, you can now view all the journal entries produced by Cash Management events in Cash Management.

Subledger Accounting for Cash Management:
In Cash Management Release 12, bank account transfers and bank statement cash flows are the two objects that can produce accounting events. Once the events are created and the accounting program is run, the journal entry setup and the accounting configurations are referenced to produce journal entries. The journal entries are then transferred to GL. GL has visibility into the source transactions and Cash Management users can drill down from the transaction level to the journal entry details.

Subledger Accounting - Benefits:
The Subledger Accounting feature allows multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements. In addition, with subledger accounting you retain the most granular level of detail in the journal entries, with different summarization options in the General Ledger, allowing full audit and reconciliation

Subledger Accounting Key Concepts :
Here are some key subledger accounting concepts:
•Event model is defined in SLA for each subledger represents the transaction/document types and the lifecycle of each transaction:
-Event class classifies transaction types
-Event type defines possible actions on each event class with possible accounting significance.

The journal creation rules are defined per event class/event type. In Cash Management, there are two event classes: Bank Account Transfer and Bank Statement Cash Flow. An accounting event for a Bank Account Transfer, for example, would be the creation or cancellation of a bank account transfer. So, any time a bank account transfer is created, an accounting event is created as well. Based on the rule setup, there may or may not be a resulting journal entry. You may set up rules to generate journal entries for some events, but not for others.

Transaction object and sources are the data model for each subledger that contains the transaction attributes/information made available to be used during journal rule setup and journal entry generation.

Bank Account Model in Release 12i

Here is a summary of the new features introduced by 12i in the area of Bank Account Model:

Bank Account Model Definition:
The new bank account model allow you to define and keep track of all bank accounts in the e-Business Suite in one place and explicitly grant account access to multiple operating units/functions and users. Bank accounts for internal use in Cash Management, Payables, Receivables, Treasury and Payroll are consolidated in Release 12. If you were using internal bank accounts in prior releases, all bank accounts will be migrated into the centralized bank account model automatically during the upgrade.

UMX-based Security:
The CE UMX Security Wizard is available under the User Management (UMX) responsibility. The wizard helps you quickly define the legal entities available for a role. To launch the wizard, log in as System Administrator, then in the User Management responsibility, go to Roles & Role Inheritance.

Bank Account Model Interaction :
In previous releases, Oracle Accounts Payable owned banks, bank branches and bank accounts. Such bank accounts could also be used by Accounts Receivable. Payroll and Treasury, however, had their own bank account models. In Release 12, banks and bank branches are created as Trading Community Architecture parties. The bank accounts are associated with Bank Branches but reside within the Cash Management application. During the bank account creation, you will be able to define in which applications this bank account can be used.

Bank Account Model Benefits:
The new model reduces the number of access points to manage bank accounts by providing a centralized user interface where all internal bank accounts can be set up. Bank account access in the new model can be granted to multiple operating units, thus eliminating the redundant duplicate bank account setup under different operating units in case these operating units share the same bank account. This simplifies the reconciliation process since now one bank account is the system corresponds to one bank account at the bank.

Cash Management Security Components:
Set up security for access to Cash Management activities by using components from different applications. The specific steps required to secure access to your bank accounts depend on the type of access you want to secure, the applications that you use for your business, and your organization structure.
•Use the CE UMX Security Wizard to grant a role (responsibility) the ability to perform bank account activities in the following areas:
-Use: View bank statements, reconcile cashflow transactions, work with cash forecasting, cash positioning, and cash pools
-Maintenance: Create and update bank accounts
-Bank Account Transfers: Transfers funds from and to internal bank accounts.
•When creating bank accounts, specify the legal entity that owns the account and the organizations that the account is used for (Payables, Receivables, Treasury, Payroll).
•Create security profiles to define the list of organizations that a user can access. Set up security profiles in the following applications as needed:
-MOAC: to define the list of operating units a user can access for Payables and Receivables and be able to reconcile AP and AR bank statement lines.
-Cash Management: to define the list of organizations a user can access to reconcile cashflow transactions. This security profile also allows users to access bank accounts for cash forecasting, cash positioning, and cash pools if no other security profiles are set up.
-Treasury: to define the legal entity that the Treasury user can access and be able to reconcile Treasury bank statement lines.
-Payroll: to define the business groups a user can access and be able to reconcile payroll transactions.

Bank Account Model Setup and Process:
•Responsibility: Cash Management
•Navigation: Setup:Banks > Banks
The key setup steps are:
•Define Banks in Oracle Cash Management.
•Define Bank Branches in Oracle Cash Management.
If you intend to use this bank account in Oracle Treasury as a company bank account, switch to that application, define Bank Counterparties and link them to Bank Branches that you have created. If you do not intend to use this bank account in Oracle Treasury, skip this step and proceed straight to the Bank Account definition.
At this point the system verifies your privileges for bank account maintenance. If you are authorized to maintain bank accounts for this legal entity, you will be able to create your Bank Accounts in Oracle Cash Management.
If you do not intend to use this bank account in Payroll, then the setup process is complete for you. If you intend to use this bank account in Payroll, switch to that application and add this bank account to the organizational payment method.

Implementation Considerations:
The bank new account model has several dependencies on other applications and centralized features.
•There is a dependency on the Trading Community Architecture because this is where the banks and the bank branches are stored.
•The organizational hierarchy comes from Human Resources.
•Finally, the General Ledger accounts that are assigned to the bank accounts are defined in General Ledger.
•If you are using Payroll or Treasury, then the bank account set up depends on those applications as well.

AP Check fraud prevention in Oracle Apps - Payee Match with Positive Pay

Client and Business Case – A large multinational corporation wanted to implement a very tight check fraud prevention tool while implementing Oracle Payables. This was because of the reason that with today’s technology, it has become much easier for individuals to commit check fraud. The corporation was concerned with the recent increase in check fraud activity perpetrated against itself, hence they needed a more effective fraud-fighting tool within Oracle Apps.

Solution - In order to reduce Client’s susceptibility to check fraud, Payee Match was implemented. Payee Match is a tool used by corporations and financial institutions to match the amount and serial numbers on checks as well as the name of the payee given out in the “Payee Zone” on the check.

Payee Match service works independently of the Positive Pay Service. It matches all the data in the “Payee Zone” on the face of Client’s checks. This service usually works in conjunction with the Positive Pay service.While Positive Pay verifies the amount and the serial numbers of the check Payee Match provides a third line of defense against check fraud by verifying the payee name and address. Payee Match matches a file, sent by the Client, to the checks presented for payment at the bank. The Client will have the choice to pay or reject any non-matched items.

Payee Match generates pay or no pay alerts on the following data elements:
a) Issued date
b) Serial Number
c) Amount
d) Transit Number
e) Bank Identifier
f) Account Number
g) Payee Name
h) Payee Address.

High level Solution Steps:
1. Develop a Custom Positive Payee match Report in Oracle to include all the data elements of Payee Match as per the structure required by the Client.
2. Run the Positive Payee Match Report
3. Format the text file generated by the Positive Payee Match Report as per the format agreed to with the banks.
4. Custom Scheduler Process --FTP the file to the desired location in the server as per the bank’s specification
5. Bank will tabulate and send the results of payments by payee detailing the count for each pay cycle in a text file to the desired location on the FTP Server.
6. Custom Scheduler Process will grab the text file sent by bank from the designated location and sends it to the desired location on the Oracle server.

Detailed User Procedures:
In order to achieve the payee match solution the following steps need to be performed:
1. Development of a custom report on the lines of the standard Positive Payee Match report in Oracle.
2. Run the report and format the resulting text file as per the bank’s format.
3. Custom Scheduler Program will extract the file from the oracle server and FTP it to the FTP server from where the bank can download the same. This will be on a daily basis with at least one pay cycle per day throughout the week beginning from Sunday and ending on Friday.
4. Need to create an image map of Client check stock; anything inside the image map is called the “Payee Zone”. The Payee Zone is read using OCR technology and converted into a text string, with each element being separated by a space. All punctuation and white space is removed from the string. All information contained in the Payee Name Zone of the physical check must be included in the fields “Check Sequence 1” through “Check Sequence 10” in order of appearance on the physical check (top to bottom, left to right.)
5. Once the scanned data is converted to a text string, the matching software combines all of the Check Sequence fields from the Client issued file into a string, and separates each field element with a space. It then compares this string to the scanned data from the check. This comparison identifies any discrepancies that may have been caused by fraudulent activity. If any discrepancies are detected between the two strings, the check is examined manually. If it is determined that the mismatch is not caused by a computer error, then Client is notified of the discrepancy with a pay / no pay decision, via email or fax.

6. The Bank will upload the file with the count of the payments in the particular pay cycle to the FTP server from where the custom scheduler program can retrieve and send it over to the designated location in the Oracle server.

Overview of Bank Reconciliation using Oracle Cash Management

Bank Reconciliation Process - Bank accounts are the assets of the company and must be explained as part of the audit requirements. Bank reconciliation can reveal fraud as well as errors. Reconciliation is the process of explaining the difference between two balances which could be due to legitimate reasons like timing differences.

Bank Reconciliation uses the following formula:
Bank Account Balance in Oracle Financials + Items on Bank Statement, but not in Financials
Items in Financials, but not on Bank Statement = Balance as per Bank Statement

Both types of differences are to be analyzed to determine whether the source of discrepancy was error or a legitimate difference and action initiated accordingly.

Legitimate differences occur due to the following reasons:
1. Timing differences – Check Issued but not presented or Checks Deposited but not cleared.
2. Bank charges and other items unknown till the bank statement is received.
3. Fees for currency conversion
4. Unprocessed Transactions
These differences once identified have to be eliminated by making appropriate accounting entries.

Reconciliation Process:
Bank transactions entered directly into GL or generated from Payables, Receivables or Payroll can be reconciled with CM.
1. Loading the Bank Statement:
The transactions can be reconciled manually or automatically by loading an electronic statement directly into CM. Loading is done using the bank statement open interface where a bank statement in the requisite flat file format is uploaded into CM tables. The automatic reconciliation looks for certain match criteria to determine whether a transaction and a bank statement line are one and the same.

2. Reconciling Journal Entries:
The journal entries entered directly in GL can be reconciled with the bank statement. The Auto reconciliation program matches a journal line description with the bank statement line transaction number.

3. Reconciling Payments:
Supplier payments entered in Payables can be reconciled to the bank statement lines in CM. The payment status against each check is updated as “Reconciled”.

4. Reconciling Receipts:
Receipts created in Receivables can also be reconciled to the bank statement lines in CM. CM updates the status of the receipts to “Reconciled” and creates appropriate accounting entries for transferring to GL. Payables and Receivables can generate reconciliation accounting entries for cash clearing, bank charges and foreign currency gain or loss.

5. Reconciling other Transactions:
Certain transactions like bank charges, interest credits, specific exchange rate applied against foreign currency transactions, customer receipts returned due to bounces etc, are known only when the bank statement is received. These would not have been initiated from Oracle Applications. CM is the primary point of entry for these transactions.

Importing Bank Statements and Validation:
Use CM’s Reconciliation programs to:
•Validate the information in the bank statement open interface tables
•Import the validated bank statement information
•Perform an automatic reconciliation after the import process completes

The AutoReconciliation program performs the following validations on loading bank statement information into the bank statement open interface tables:
•Bank statement header validation
•Control total validation
•Statement line validation
•Multicurrency validation

Reconciling Bank Statements Automatically:
Use AutoReconciliation program to automatically reconcile any bank statement in Oracle CM. There are three versions:
1. AutoReconciliation: Use this program to reconcile any bank statement that has already been entered in CM.
2. Bank Statement Import: Use this program to import an electronic bank statement after loading the bank file with a SQL*Loader script.
3. Bank Statement Import and AutoReconciliation: Use this program to import and reconcile a bank statement in the same run. After the program has been run, review the AutoReconciliation Execution Report to identify any reconciliation errors that need to be corrected and re-run the program again if corrections are done.

Lockbox Reconciliation Extension

Client Industry – Applicable to all clients implementing/ implemented AR Auto Lockbox

Business Case – Client wants to be able to handle lockbox reconciliation advance cash receipts and receipt matching in EBS by reading from the receipt lines present in the electronic version of the bank statements received from the bank. Client needs an automated way to match the detailed lockbox receipts in the Oracle A/R module to the summary level deposit information processed through the BAI2 file in the Oracle Cash Management module

Solution – A custom auto lockbox reconcile extension was developed which will match the detailed lockbox receipts in the Oracle A/R module to the summary level deposit information processed through the BAI2 file in the Oracle Cash Management module. The Cash Management module matches the receipt transaction in the A/R module to the BAI2 bank statement file and creates an appropriate accounting entry in General Ledger when the amounts are different.

An additional custom form developed will store all the accounting reference into the GL Account code column for discount, charge back, bank charges item types for both lockbox and credit / debit card settlements. The Custom Lockbox Reconciliation program will derive the account and create an accounting entry in General Ledger to reconcile the receipt.

Detailed Approach - When BAI2 files are received from the Bank they are processed in the Cash Management module. The Custom Lockbox Reconciliation will match the sum of all the receipt amount for the AR receipts batch in (AR_Cash_Receipts) with the net amount received in the Bank.

On a daily basis, the lockbox reconciliation extension will process and check the bank statement transaction details and match based on the unique identifiers with receipts in Receivables, it will create an accounting in GL for difference amount of receipt from bank deposit and accounting entries for any charge.