Skip Headers

Oracle Cash Management User Guide
Release 12.1
Part Number E13483-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

System Setup

This chapter tells you how to set up Cash Management after you have installed. It includes an overview with list of setup steps and corresponding detailed instructions.

This chapter covers the following topics:

Setting Up Oracle Cash Management

This section includes the checklist of steps to implement your Cash Management system. The checklist shows you where to read about each setup step detail. The Window Name(s) column shows you in which window(s) you perform the step, and the specific responsibility or product where the window is available. The Required column shows whether the step is required, optional, or conditionally required.

Before you set up Cash Management, you should set up Oracle Application responsibilities and users for the implementation. See: Defining a Responsibility, Oracle Applications System Administrator's Guide - Security.

Cash Management provides nine responsibilities: Cash Management, Bank Reconciliation, Cash Forecasting, Cash Accountant, Cash Analyst, Cash Management Back Office Administrator, Cash Management Manager, Cash Management Setup, and Cash Management Master User. Refer to the Cash Management Window Navigation Paths for the default window navigation paths available for these responsibilities.

Cash Management uses information from Oracle General Ledger, Oracle Payables, Oracle Payroll, Oracle Treasury, and Oracle Receivables for bank statements reconciliation; and from Oracle General Ledger, Oracle Order Management, Oracle Payables, Oracle Payroll, Oracle Purchasing, Oracle Projects, Oracle Sales, Oracle Treasury, and Oracle Receivables for cash forecasting. You must perform certain setup steps in these applications before you can use Cash Management with them.

Important: The following tasks describe only those setup actions that you need to perform to integrate Cash Management with other Oracle Applications. Additional setup steps for these applications are unrelated to Cash Management. See the user's guide for the product for detailed information on setting up each Oracle Applications product.

Related Product Setup Steps

The following steps may need to be performed to implement Oracle Cash Management. These steps are discussed in detail in the Setting Up sections of other Oracle product user and implementation guides. Refer to the preface section.

You must set up underlying Oracle Applications Technology

The setup steps in this chapter tell you how to implement the parts of Oracle Applications specific to Oracle Cash Management. You need to complete several setup steps, including:

For more information, see: Setup Tasks, Oracle Applications System Adminstrator's Guide - Security.

Also, if your product uses Oracle Workflow, for example, to manage the approval of business documents or to derive Accounting Flexfield values through the Account Generator, you need to set up Oracle Workflow.

General Ledger Setup Steps

See: General Ledger Setup, Oracle General Ledger Implementation Guide for help in completing the following setup steps:

  1. Define your chart of accounts. See: Defining Accounts, Oracle General Ledger Implementation Guide.

  2. Define your accounting period types and accounting calendar periods. See: Period Types, Oracle General Ledger Implementation Guide and Calendars, Oracle General Ledger Implementation Guide.

  3. Enable currencies that you plan to use. See: Defining Currencies, Oracle General Ledger User Guide.

  4. Define a ledger. Specify a ledger name and assign it a calendar, ledger currency, and a chart of accounts structure. See:Defining Ledgers, Oracle General Ledger Implementatation Guide.

  5. Define a transaction calendar to determine which days are business days to be used in cash forecasting. See: Defining Transaction Calendars, Oracle General Ledger Implementatation Guide.

Payables Setup Steps

See: Setting Up Oracle Payables, Oracle Payables Implementation Guide for help in completing the following setup steps:

  1. Choose a primary ledger for Oracle Payables. See: Choosing a Ledger, Oracle Payables Implementation Guide.

  2. Set the Payables Option: Account for Payment. See: Reports Payables Options, Oracle Payables Implementation Guide.

  3. Open your Payables accounting periods. See: Controlling the Status of Payables Periods, Oracle Payables Implementation Guide.

  4. Define your bank accounts, and assign GL accounts. Do this if you have not already performed this step as a Receivables Setup step. See: Setting Up Cash Management to Use with Oracle Payables or Setting Up Cash Management to Use with Oracle Receivables. If you use Oracle Payables, assign accounts for Payables payment documents.

Receivables Setup Steps

See: Setup Steps, Oracle Receivables Implementation Guide for help in completing the following setup steps:

  1. Choose a ledger for Oracle Receivables. See: Setting Up Cash Management to Use with Oracle Receivables.

  2. Open your Receivables accounting periods. See: Opening and Closing Accounting Periods, Oracle Receivables User Guide.

  3. Define a receipt class for miscellaneous receipts. See: Setting Up Cash Management to Use with Oracle Receivables.

  4. Define receivables activities for miscellaneous transactions. See: Setting Up Cash Management to Use with Oracle Receivables.

  5. Set up receipt sources. See: Setting Up Cash Management to Use with Oracle Receivables

  6. Define your bank accounts, and assign GL accounts. Do this if you have not already performed this step as a Payables Setup step. See: Setting Up Cash Management to Use with Oracle Payables or Setting Up Cash Management to Use with Oracle Receivables. If you use Oracle Payables, assign accounts for Payables payment documents.

Cash Management Setup Checklist

This checklist summarizes each of the steps you follow to implement Cash Management. It includes setup steps for data that may be shared with other Oracle Applications, which Cash Management requires. If you have already defined this information when setting up other Oracle Applications, you can skip those steps. This shared data includes:

Since some setup steps build upon information you define in other steps, you should perform them in the order listed.

After you complete some setup steps, you can submit reports to review your work and confirm that you have successfully completed the step. Such as, after you complete entering bank transaction codes, you can submit the Bank Transaction Codes Listing. See: Bank Transaction Codes Listing. The following table contains the Cash Management Setup Checklist:

Step No. Step Required
1 Define your chart of accounts. If you use Multiple Organization Support, perform this step for each organization. See: Defining Accounts, Oracle General Ledger User Guide. Required
2 Define your accounting period types and accounting calendar periods. If you use Multiple Organization Support, perform this step for each organization. See: Period Types, Oracle General Ledger User Guide and Calendars, Oracle General Ledger User Guide. Required
3 Enable currencies that you plan to use. See: Defining Currencies, Oracle General Ledger User Guide. Optional
4 Define a ledger. Specify a ledger name and assign it a calendar, ledger currency, and a chart of accounts structure. If you use Multiple Organization Support, perform this step for each organization. See: Defining Ledgers, Oracle General Ledger User Guide. Required
5 Define a transaction calendar to determine which days are business days to be used in cash forecasting. If you use Multiple Organization Support, perform this step for each organization. See: Defining Transaction Calendars, Oracle General Ledger User Guide. Optional
6 Define Bank Account Maintenance and Access security in Cash Management Security Wizard. This step is required before you set up or use bank accounts. See: Cash Management Security, Oracle Payables User Guide. Required
7 Choose a primary ledger for Oracle Payables. If you use Multiple Organization Support, perform this step for each organization. See: Choosing a Ledger, Oracle Payables User Guide. Required
8 Set the Payables Option: Account for Payment. The options are When Payment is Issued and When Payment Clears. Select at least the When Payment Clears option. If you use Multiple Organization Support, perform this step for each organization. See: Payment Accounting Payables Options, Oracle Payables User Guide. Required
9 Open your Payables accounting periods. If you use Multiple Organization Support, perform this step for each organization. See: Controlling the Status of Payables Periods, Oracle Payables User Guide. Required
10 Define your bank accounts with Receivable Use, and assign GL accounts and Receivables control options. Do this if you have not already completed this step as a Receivables setup step. See: Bank Account Setup Required
11 Choose a ledger for Oracle Receivables. If you use Multiple Organization Support, perform this step for each organization. See: Setting Up Cash Management to Use with Oracle Receivables. Required
12 Open your Receivables accounting periods. If you use Multiple Organization Support, perform this step for each organization. See: Opening and Closing Accounting Periods, Oracle Receivables User Guide. Required
13 Define a receipt class for miscellaneous receipts. If you use Multiple Organization Support, perform this step for each organization. See: Setting Up Cash Management to Use with Oracle Receivables. Required
14 Define Receivables activities for miscellaneous transactions. If you use Multiple Organization Support, perform this step for each organization. See: Setting Up Cash Management to Use with Oracle Receivables. Required
15 Set up receipt sources. If you use Multiple Organization Support, perform this step for each organization. See: Setting Up Cash Management to Use with Oracle Receivables Required
16 Define your bank accounts with Payable Use, and assign GL accounts and Payables control options. Do this if you have not already performed this step as a Payables setup step. See: Bank Account Setup. If you use Oracle Payables, assign accounts for Payables payment documents. Required
17 Define your bank accounts with Payroll Use. Do this if you need to reconcile Payroll transactions or include Payroll transactions in Cash Forecasting and Cash Positioning. Optional
18 In Oracle Payroll, create Payment Methods and assign bank account defined with Payroll Use. See: Setting Up Oracle Payroll for Oracle Cash Management. Optional
19 Set 'CE: Mask Internal Bank Account Numbers' profile option. Do this to mask bank account number in display. See: Profile Options in Cash Management. Optional
20 Define system parameters. If you deal with multiple legal entities, perform this step for each legal entity. See: System Parameters. Required
21 Define bank transaction codes for each bank account. This step is required to perform bank statement import and Autoreconciliation. See: Bank Transaction Codes. Optional
22 Set up the Bank Statement Open Interface. See: Bank Statement Open Interface. Optional
23 Set up the Reconciliation Open Interface. See: Reconciliation Open Interface. Optional
24 Set up the External Cashflow Open Interface, including the Forecast Selection Criteria Descriptive Flexfield. See: External Cashflow Open Interface and Associate External Forecast Source Types with Views. Optional
25 Define cash forecasting templates. See: Creating Cash Forecast Templates. Optional
26 Define cash position worksheets. See: Viewing Cash Positions in Discoverer. Optional
27 Set up sequential document numbering for bank statements. If you use Multiple Organization Support, perform this step for each organization. See: Sequential Document Numbering. Optional
28 Define request sets. See: Request Submission, Oracle Applications User Guide or Request Sets, Oracle Applications Developer's Guide. Optional
29 Define descriptive flexfields. See: Defining Descriptive Flexfields, Oracle Applications Flexfield Guide. Optional
30 Define Cashflow Subtype. Optional
31 Define Payment Templates. Optional
32 Define Bank Statement Cashflow Mappings. Optional

Related Topics

Cash Management Window Navigation Paths

Setting Up Oracle Payables for Cash Management Integration

Setting Up Oracle Receivables for Cash Management Integration

Setting Up Oracle Payroll for Cash Management Integration

Setting Up Oracle Payables for Oracle Cash Management Integration

The following setup steps are necessary for Oracle Cash Management to integrate with Oracle Payables. Confirm that these steps are completed before proceeding with your Cash Management implementation.

Important: Even if you do not have Oracle Payables fully installed, you must have a shared install of Oracle Payables to be able to use Oracle Cash Management. However, in this case, you do not need to perform the Payables-specific steps listed here.

Prerequisite:

To set up Oracle Payables for Cash Management integration:

  1. Choose a primary ledger for each responsibility when you set up your Payables application. You set this primary ledger in the Choose Ledger window in Oracle Payables.

    Payables requires you to choose a ledger for your operating unit (in a multi-organization environment) or for your installation (in a single organization environment). See: Choosing a Ledger, Oracle Payables User Guide.

  2. The Account for Payment option in the Payables Options window has the following two check boxes:

    You can select one or both options. To account for payments that you reconcile in Oracle Cash Management, you must select at least When Payment Clears. If you select both options, you can use a cash clearing account and create accounting entries for bank charges, errors, and gains/losses. If you do not enable this option, you can still reconcile payments in Cash Management, but you cannot create reconciliation accounting entries.

    See: Accounting Options Payables Options, Oracle Payables Implementation Guide..

  3. The Account for Gain/Loss option in the Payables Options window has the following two check boxes:

    You can select one or both options. Payables will automatically enable When Payment Clears when you select Account for Payment When Payment Clears. You must enable this option to account for the gain or loss between the estimated ledger currency payment amount and the actual ledger payment currency amount that was recorded when the bank disbursed funds for the payment.

    See: Accounting Options Payables Options, Oracle Payables Implementation Guide.

  4. Open your Payables accounting periods. See: Controlling the Status of AP Accounting Periods, Oracle Payables User Guide.

Related Topics

Payables Options, Oracle Payables User Guide

Setting Up Oracle Payables or Oracle Receivables for Oracle Cash Management Integration

The following setup steps are necessary for Oracle Cash Management to integrate with both Oracle Payables and Oracle Receivables, and can be performed using either of these products. Confirm that these steps are completed before proceeding with your Cash Management implementation.

To set up Oracle Payables or Oracle Receivables for Cash Management Integration:

  1. In the Banks and Bank Accounts windows, define each bank account that you will be reconciling. In the Account Use field, select Internal.

  2. In the GL Accounts tab of the Bank Accounts window, enter, for each bank account, the GL accounts you want to use to account for transactions that you reconcile.

  3. Cash

  4. Cash Clearing

  5. Bank Charges

  6. Bank Errors (Payables only)

  7. Confirmed Receipts (Receivables only)

  8. The accounts you defined for the bank account default to any Payables payment documents and Receivables payment methods you define. You can override these default accounts in the Payment Documents window when you define payment documents for the bank account.

  9. For each remittance bank account, assign a remittance account.

Related Topics

Oracle Payables Reconciliation Accounting

Setting Up Oracle Receivables for Oracle Cash Management Integration

The following setup steps are necessary for Oracle Cash Management to integrate with Oracle Receivables. Confirm that these steps are completed before proceeding with your Cash Management implementation.

Important: If you do not have Oracle Receivables fully installed, you must have a shared install of Oracle Receivables to be able to use Oracle Cash Management. However, in this case, you do not need to perform the Receivables-specific steps listed here, unless you want to be able to create miscellaneous transactions.

Prerequisite:

To set up Oracle Receivables for Cash Management Integration:

  1. Choose a ledger for each responsibility, when you set up your Receivables application. You indicate this ledger in the Name field of the Accounting tab in the System Options window.

    Receivables requires you to choose a ledger for your operating unit (in a multi-organization environment) or for your installation (in a single organization environment).

  2. Define a receipt class for miscellaneous transactions with a Creation Method of Manual and Require Remittance set to No.

    This receipt class is optional, but is required if you plan to create miscellaneous receipts (or payments) from Cash Management.

  3. Define receivable activities for miscellaneous transactions, if you want to create miscellaneous transactions from Cash Management. For example, define receivables activities with a type of Miscellaneous Cash or Bank Error.

    When you define a receivables activity with the type Miscellaneous Cash, you must also assign either an account or a distribution set to use for the miscellaneous transaction accounting entries.

    Note: Miscellaneous transactions include both miscellaneous receipts and miscellaneous payments. Miscellaneous payments are handled as negative miscellaneous receipts in Receivables, rather than as payments in Payables.

  4. Define receipt sources that you assign to receipts. When you define a receipt source, you can enter a default receipt class and payment method. Receipt sources assign numbers to batches and determine where deposits get remitted.

Related Topics

Accounting System Options, Oracle Receivables User Guide

Receipt Classes , Oracle Receivables Implementation Guide

Receivables Activities, Oracle Receivables Implementation Guide

Receipt Sources, Oracle Receivables Implementation User Guide

Receipt Methods, Oracle Receivables User Guide

Oracle Receivables Reconciliation Accounting

Setting Up Oracle Payroll for Oracle Cash Management Integration

The following setup steps are necessary for Oracle Cash Management to integrate with Oracle Payroll. Confirm that these steps are completed before proceeding with your Cash Management implementation.

To set up Oracle Payroll for Cash Management integration:

  1. In the Define Organization window, define an organization and classify it as all of the following: business group, legal entity, HR organization, or operating unit.

  2. (This step needs to be performed only if the assignment has not already been made during the initial setup.) Assign the business group to the HR: Security profile option for your Payroll responsibility. (The organization defined in step 1 creates the same unique identity for all the four classifications).

    Important: If you are using Oracle Payroll, the Organization should have already been classified as a Business Group & HR Organization. You need to classify the same Organization as a Legal Entity and Operating Unit also. However, step 2 needs to be performed only if the assignment has not already been made during initial setup.

  3. In Cash Management, ensure that bank accounts you like to use for Payroll reconciliation have Payroll Use defined with correct business groups.

  4. In the Organizational Payment Method window, create payment methods. Bank accounts you assign to your payment methods are used for Payroll reconciliation in your Cash Management responsibility. To use the bank accounts in Cash Management, provide the following:

  5. To use the Journal Entry Line reconciliation feature from your Payroll bank accounts in Cash Management, specify a cash account and ledger. The ledger you specify here should be the same ledger you assign to the GL: Ledger Name profile option for your Cash Management responsibility.

  6. In Cash Management, you can view reconcile Payroll transactions whose bank accounts are set up with Payroll Use and the business group assigned in the Bank Account Use is the one you have in HR: Security profile option.

  7. Set up the following bank transactions codes in Cash Management for the bank account defined in Payroll: Payments and Stopped Payments.

Setting Up Oracle Treasury for Oracle Cash Management Integration

The following setup steps are necessary for Oracle Cash Management to integrate with Oracle Treasury. Confirm that these steps are completed before proceeding with your Cash Management implementation.

To set up Oracle Treasury for Cash Management Integration:

  1. Define the legal entities for your companies. See: Treasury Setup Checklist, Oracle Treasury User Guide.

  2. Define your company information. See:Company Profiles, Oracle Treasury User Guide.

  3. Assign companies to your users. See: Company Profiles, Oracle Treasury User Guide.

  4. Define your counterparties and link them to bank branches defined in Cash Management. This will enable the Treasury Use checkbox on the bank account. See: Counterparty Profiles, Oracle Treasury User Guide.

  5. Define the bank accounts with Treasury Use and enter Treasury bank account specific information in Oracle Cash Management.

System Parameters

Use the System Parameters window to configure your Cash Management system to meet your business needs. For example, system parameters determine the default options for manual reconciliation windows and the control settings for the AutoReconciliation program.

Prerequisites

To set Cash Management system parameters:

  1. Navigate to the System Parameters window.

  2. Select your basic system parameters. For details on any field, see: System Parameters Window Reference.

    Note: All options, except for Ledger, can be updated.

  3. Select the System Control tab, then choose your control system parameters.

  4. Select the Cash Management Transactions tab, then choose your cash management transactions parameters.

  5. Save your work.

Related Topics

About Oracle Cash Management

Overview of Setting Up

Setting Up Oracle Cash Management to Use with Oracle Payables or Oracle Receivables

Reconciling Bank Statements Automatically

Matching Bank Statement Lines with Transactions

Reconciliation Tolerances

Importing Bank Statements

Creating Miscellaneous Transactions

Archiving and Purging Imported Bank Statements

System Parameters Window Reference

You can set the following Cash Management options from the System Parameters window.

Basic System Parameters

Legal Entity: The name of legal entity that system parameters are applied to.

Ledger: The name of the Ledger for Cash Management accounting transactions. This value defaults from the GL Data Access Sets profile option.

Cash Management supports multiple ledgers. Each legal entity is tied to a ledger. If you deal with multiple legal entities, you set up System Parameters for each legal entity.

Begin Date: Cash Management will not display transactions dated earlier than this date.

Reconciliation Controls

Show Cleared Transactions: If you select this check box, cleared transactions remain available for reconciliation, else only uncleared transactions will appear for reconciliation.

Show Void Payments: You must select this check box if you want voided payments available for reconciliation.

Allow Adding Lines to Imported Statements: If you do not select this check box, you cannot add lines to automatically loaded bank statements.

General Parameters

Archive/Purge: You can set the following options to automatically archive or purge imported bank statement information:

Lines Per Commit: This is the number of lines that AutoReconciliation will save at a time. It controls the number of transaction rows that AutoReconciliation locks at any given time. The greater the number of lines, the faster AutoReconciliation processes. However, the number of lines you can specify is limited by the amount of memory available in your system.

Signing Authority Approval

Through the Signing Authority Approval system parameter, you can decide if you want to manually approve signing authority or use a workflow process. The following options are:

Workflow Enabled: This option enables the seeded business event to change the status on the Bank Account Signing Authority window from Pending to Approved, when a record is entered and saved.

Manually Controlled: This option allows the user to configure the workflow approval process.

Cash Management Transactions Parameters

Accounting

Bank Account Transfers

Cash Management Security

Bank Account Security

Two different security mechanisms are provided to secure bank accounts: Bank Account Maintenance security and Bank Account Access security. At a high level, Bank Account Maintenance security secures the creation and update of bank account data, whereas Bank Account Access security secures user access of the bank accounts.

Bank Account Maintenance Security

In Bank Account Maintenance security setup, you can assign one or more legal entities to each role (responsibility). In the creation flow, the Bank Account Owner LOV will display only the legal entities that are assigned to your role in this security setup. In the update flow, users can only query the bank accounts whose owner is assigned to your role.

Note: When a user is assigned to multiple roles with multiple Bank Account Maintenance Security setups, all security rules are defined for the user even though the user may access the application through only one particular role.

Bank Account Maintenance Security is implemented using the UMX data security model. A setup wizard called Cash Management Security Wizard facilitates the setup procedure. See: Cash Management Security Wizard.

Bank Account Access Security

The Bank Account Access security rule feature is built on top of Bank Account Access Setup and Cash Management Security Profiles. Bank Account Access setup provides a list of organizations that have access to a given bank account; whereas Cash Management Security Profiles provides a list of organizations an application user has access to. If there is at least one common organization between Bank Account Access setup and Cash Management Security Profile then users will be granted access to the bank account. For example, if bank account 101 has account access setup with operating unit OU1, and if user OPERATIONS has access to OU1 in the profile, then the user can view and use bank account 101.

Bank Account Access Setup

Bank Account Access setup defines the uses (Payables, Receivables, Payroll, and Treasury) of bank accounts and organizations that have access for each use. One or more operating units can be assigned to Payables or Receivables Use. Only one legal entity can be assigned for Treasury Use. The legal entity that is assigned to Treasury use is always the same as bank account owner. Lastly, one or more business groups can be assigned for Payroll Use.

Cash Management Security Profile

Cash Management Security Profiles provides a list of organizations (OU/LE/BG) that the user has access to based on following four security rules: Bank Account Use security, Treasury security, MOAC security and Payroll security.

Security in Bank Statement Reconciliation

Users can create, update or view bank statements as long as the user has access to the statement’s bank account based on bank account access security. However, in order to reconcile transactions a user must have the transaction’s organization defined in MOAC, Payroll, Treasury or Bank Account Use security.

Security in Cash Forecasting, Cash Positioning and Cash Pool

Bank Account Access security is applied to these features to restrict the bank accounts that the user has access to.

Bank Account Transfer Security

In Bank Account Transfer security setup, you can assign one or more legal entities to each role (responsibility). In Bank Account Transfers, the source and destination bank account LOVs will list only those bank accounts whose owners are assigned to your role in Bank Account Transfer security setup.

The Bank Account Maintenance Security is implemented using the UMX data security model.

Cash Management Security Wizard

Using the Cash Management Security wizard, an administrator can assign multiple legal entities to a role or roles to set up the following three securities:

To launch the Cash Management Security Wizard

  1. Navigate to User Management > Roles & Role Inheritance page

  2. Enter your role/responsibility name

  3. Click Go.

  4. Select the role/responsibility and click Update.

  5. In the Update Role page, click Security Wizard.

  6. Click Run to execute the Cash Management Security wizard.

To Set Up Security

  1. Add Legal Entity: To add additional legal entities you like to assign to role.

  2. Use: Select to assign the legal entity to Bank Account Use security.

  3. Maintenance: Select to assign the legal entity to Bank Account Maintenance security.

  4. Bank Account Transfers: Select to assign the legal entity to Bank Account Transfers security.

  5. Click Apply to save the change or Cancel to remove the changes.

Bank Transaction Codes

If you want to load electronic bank statements or use Cash Management's AutoReconciliation feature, you must define, for each bank account, the transaction codes that your bank uses to identify different types of transactions on its statements. You should define a bank transaction code for each code that you expect to receive from your bank. Payment and Receipt codes may be defined more than once if they apply to transactions from multiple sources.

You can enter effective date range fields, Start Date and End Date, so that you can make a bank transaction code inactive. You can also delete codes that have not been used, in case you make a mistake in creating one.

You can easily view the bank transaction codes you have created here by submitting the Bank Transaction Codes Listing.

To define a bank transaction code:

  1. Navigate to the Bank Transaction Codes window.

  2. In the Find Bank window, select the bank, whose codes you are defining. Alternatively, you can query the bank account number. The system displays the Bank Transaction Codes window, which includes bank account, bank information, and a region for entering transaction codes.

  3. For each transaction code you are defining, select a transaction type from the list of values. The transaction type determines how Cash Management will match and account for transactions with that code.

    Bank statement lines are coded to identify the transaction type the line represents. Since each bank might use a different set of transaction codes, you need to map each code a particular bank uses, to one of the following Cash Management transaction types.

    You can select from the following list of values:

  4. Enter the code used by your bank.

  5. Optionally enter a description of the transaction.

  6. Enter start and end dates to determine when the bank transaction code is considered active.

  7. Enter the number of float days that you want Cash Management to add to or subtract from the statement date to create an anticipated value date for automatic lockbox receipts.

  8. Enter a Transaction Source for payment and receipt transactions. Choose Journal from the list of values to reconcile statement lines, with the assigned transaction code, to General Ledger journals. Choose Open Interface to reconcile statement lines to settlements in Oracle Treasury or external transactions in the Reconciliation Open Interface. Choose Payables Payments or Receivables Receipts to reconcile statement lines to transactions in Oracle Payables or Oracle Receivables. Choose Payroll Payments to reconcile statement lines to transactions in Oracle Payroll. Choose Cash Management to reconcile statement lines to cashflows in Oracle Cash Management.

  9. Enter the Reconciliation Sequence number. If the same code with bank transaction type Payment or Receipt is entered more than once with different transaction sources this number is used by the AutoReconciliation program to determine the order in which to search the transaction sources for a matching transaction.

  10. Optionally select a value for the Payroll Payment Format field to reconcile statement lines, with the assigned transaction code, to Payroll EFT payments. The Payroll Matching Order field is automatically populated based on the value selected in the Payroll Payment Format field. It is populated after a Payroll Payment Format has been selected. This field indicates the order in which you need to set up the format for the BANK_TRX_NUMBER in the bank statement mapping template.

  11. If the transaction type is Miscellaneous Receipt or Miscellaneous Payment, use the Matching Against field to determine the order of matching and the type of transactions to match.

    Since the same transaction code may be used for matching against both miscellaneous transactions and/or correcting statement errors, you need to indicate, for miscellaneous payments and miscellaneous receipts, the transaction type to match. If you use a transaction code for both miscellaneous transactions and correcting statement errors, you can also specify the matching sequence. You can choose from the following values to indicate how to use this bank transaction code:

  12. Choose the correction method your bank uses when correcting bank errors: Reversal, Adjustment, or Both.

    This field is only applicable for those Miscellaneous Receipt or Miscellaneous Payment transaction codes that may be used to match to correction statement lines.

  13. Choose whether to create transactions for any miscellaneous payments and miscellaneous receipts reported on the bank statement when no transaction number is provided.

  14. If you selected the Create option in the previous field, specify the Receivables Activity type and payment method for any miscellaneous transactions (receipts or payments) you create from within Cash Management.

  15. Define each additional bank transaction code, following the previous steps.

  16. Save your work.

To copy and purge bank transaction codes:

Two new concurrent programs have been introduced which allow you manipulate in bulk the Bank Transaction Code setups for multiple accounts.

  1. Copy Bank Transaction Codes: This copies all the bank transaction codes which are set up for one account to a set of destination accounts. Any transaction codes which exist for the destination accounts are not overridden.

  2. Purge Bank Transaction Codes: This will delete the bank transaction codes for a set of bank accounts. Any transaction codes which are being referenced in statements or statement cashflow mappings will not be removed.

Related Topics

About Bank Statements

Overview of Setting Up

Reconciling Bank Statements Automatically

Bank Transaction Codes Listing

Bank Statement Open Interface

If your bank provides account statements in a flat file, using a defined format like BAI2 or SWIFT940, you can use the Bank Statement Open Interface to load this information into Oracle Cash Management. You can load previous-day bank statements to reconcile against system transactions, and intra-day bank statements for cash positioning.

You can load bank statement information into the Bank Statement Open Interface tables using the Bank Statement Loader program or a custom loader program. Once you populate the open interface tables, you can run the Bank Statement Import program to validate and transfer the bank statement information into the Cash Management Bank Statement tables. If you import bank statements from bank accounts that are used by Oracle Treasury, the Bank Statement Import program also automatically updates the bank account balances seen in Oracle Treasury. After the bank statement information has been successfully transferred, you can purge the open interface tables.

You can load intra-day bank statements into the Bank Statement Open Interface tables using the Intra-Day Bank Statement Loader program or a custom loader program. Once you populate the open interface tables, you can run the Intra-Day Bank Statement Import program to validate and transfer the bank statement information into the Cash Management Intra-Day Bank Statement tables. After the bank statement information has been successfully transferred, you can purge the open interface tables.

Bank Statement File Formats

The Bank Statement Loader program and the Intra-Day Bank Statement Loader program can load the following three bank statement file formats:

Before you can use the Bank Statement Loader program or the Intra-Day Bank Statement Loader program, you must complete the setup required for each format that you want to load.

Setup for BAI2

The Bank Statement Loader program uses a predefined SQL*Loader script to load BAI2 bank statement files. However, since each bank adopts the BAI2 standard slightly differently, you need to map the BAI2 format to the Bank Statement Open Interface tables. You can create the mapping information from the BAI2 mapping template. See: Bank Statement Mapping

The BAI2 SQL*Loader script is located in $CE_TOP/bin/BAI2.ctl. The mapping rules for the BAI2 format are listed in BAI2 Mapping Template.

Setup for SWIFT940 Format

The Bank Statement Loader program uses a predefined SQL*Loader script to load SWIFT940 bank statement files. However, since each bank adopts the SWIFT940 standard slightly differently, you need to map the SWIFT940 format to the Bank Statement Open Interface tables. You can create the mapping information from the SWIFT940 mapping template. See: Bank Statement Mapping

The SWIFT940 SQL*Loader script is located in $CE_TOP/bin/SWIFT940.ctl. The mapping rules for the SWIFT940 format are listed in SWIFT940 Mapping Template.

After you load SWIFT940 bank statement files into the open interface tables, you may need to define new bank transaction codes in Cash Management. SWIFT940 transaction codes represent the type of transaction. For example, TRF represents transfers. However, SWIFT940 transaction codes do not contain information about the debit or credit nature of the transaction. Instead, the Debit/Credit Mark field is used to differentiate debit and credit entries, where D means debit and C means credit. When the Bank Statement Loader program populates the TRX_CODE column in the Bank Statement Lines Interface table, it appends the Debit/Credit Mark to the transaction code to form a new code. For example, debit transfers are identified as TRFD and credit transfers as TRFC. You must set up these new transaction codes before you can import the bank statement information. See: Bank Transaction Codes.

Setup for User-Defined Format

If your bank uses a format other than BAI2 or SWIFT940, you need to develop a SQL*Loader script and create a new mapping template for that format. You may want to consult the sample SQL*Loader script created for the French EDIFACT standard, located in $CE_TOP/bin/EDIFACT.ctl. The mapping template, EDIFACT-France, is also available for your reference. The mapping rules for the French EDIFACT format are listed in EDIFACT - France Mapping Template.

Prerequisites of the bank statement file

Developing your custom SQL*Loader script

Creating a New Mapping Template

Define new mapping rules using the Bank Statement Mappings window. See: Bank Statement Mapping

Bank Statement Mapping

Use the Bank Statement Mappings window to map the structure of your bank statement file to the Bank Statement Open Interface tables. You need to define the mapping for each unique file structure. The default mapping templates for BAI2, SWIFT940, and French EDIFACT are provided by Cash Management and cannot be changed. You can create new mapping templates by copying the existing templates.

  1. Open the Bank Statement Mappings window.

  2. If you want to review a mapping template, choose one from the list of values, else cancel the list of values.

  3. Create a new record for the new mapping template.

  4. In the Name field, enter the name of the new mapping template.

  5. In the Description field, enter the description for the new mapping template.

  6. In the Control File Name field, enter the SQL*Loader script that should be used with this new mapping template. You can choose an existing script from the list of values or enter a new script that you have created. If your bank uses the BAI2 format, choose BAI2.ctl. If your bank uses the SWIFT940 format, choose SWIFT940.ctl. If your bank uses the French EDIFACT format, choose EDIFACT.ctl. If your bank uses a user-defined format, enter the SQL*Loader script you created.

  7. In the Precision field, enter a precision for the amounts if they are expressed as integers and have the same precision within the bank statement file. The Bank Statement Loader program uses the precision to convert integer amounts into decimal numbers. For example, an amount expressed as 10000 with a precision of 2 is interpreted and loaded as 100.00. Leave the precision blank if amounts are already decimal numbers or if they have different precisions. In BAI2, the default precision is 2. In SWIFT940, precision is not used because amounts are already decimal numbers. In French EDIFACT, precision is specified for each amount on the header and line records.

  8. In the Date Format field, enter the date format that is used in the bank statement file. You must enter a format that can be recognized by the Oracle database. The default format for BAI2 and SWIFT940 is YYMMDD. The default format for French EDIFACT is DDMMYY.

  9. In the Timestamp Format field, enter the time format that is used in the bank statement file. This information is typically available on intra-day bank statements only. You must enter a format that can be recognized by the Oracle database. The default format for BAI2 is HH24MI. No defaults are provided for other file formats. Although the BAI2 standard uses the timestamp format of HHMM, where HH represents hours in a 24-hour format and MM represents minutes, you cannot use this format, because the Oracle database interprets MM as months instead of minutes.

  10. In the Bank File Format Type field, enter the format type of the bank statement file. You can enter a new format or choose from a list of previously defined formats. If you have not defined a new format, the seeded choices are BAI2, SWIFT940, and EDIFACT-France. Once you create a mapping template for a new format, that format is available in the list of values. If your bank uses the BAI2 format, choose BAI2. If your bank uses the SWIFT940 format, choose SWIFT940. If your bank uses the French EDIFACT format, choose EDIFACT-France. If your bank uses a user-defined format, enter that format.

  11. Choose the Populate button. If you choose an existing format as the Bank File Format Type, the rules defined in the mapping template for that format are automatically entered in the Header and Lines tabbed regions. If multiple mapping templates are defined for that format, you must select one from the list of values. However, if you are defining a new format, only the column names are entered in the Header and Lines tabbed regions.

    Unlike others, PRECISION and STATEMENT_TIMESTAMP are not columns in the Bank Statement Headers Interface table. The Bank Statement Loader program uses the precision to convert integer amounts into decimal numbers. The Intra-Day Bank Statement Loader program appends the timestamp to the STATEMENT_DATE column in the Bank Statement Headers Interface table.

    You should map the PRECISION column only if amounts are expressed in integers and have different currencies. In BAI2, the PRECISION column is not used because the default precision of 2 is defined on the mapping template. In SWIFT940, the PRECISION column is not used because all amounts are already decimal numbers. In French EDIFACT, the PRECISION column is used to convert the amounts for the AMOUNT column. If you do not enter a value for the PRECISION column, the value in the PRECISION column of the Bank Statement Headers Interface table is used.

    You must map the STATEMENT_TIMESTAMP column if you want to load timestamp information from your intra-day bank statements. Since your bank may provide multiple intra-day bank statements within the same day, you can use the timestamp to determine how recent the information is. In the BAI2 mapping template, the STATEMENT_TIMESTAMP column is mapped to Record ID 02 and Position 5.

  12. The Headers tabbed region lists all the columns in the Bank Statement Headers Interface table. Define the mapping for the columns according to the file format used by your bank. The four required columns in the table are:

    You must map all of the four columns, except for the ORG_ID column. The ORG_ID column is populated automatically by the Bank Statement Loader program.

    Unlike others, PRECISION is not a column in the Bank Statement Headers Interface table. The Bank Statement Loader program uses this precision to convert integer amounts into decimal numbers. You should map this column only if amounts are expressed in integers and have different currencies.

    In BAI2, the PRECISION column is not used because the default precision of 2 is defined on the mapping template. In SWIFT940, the PRECISION column is not used because all amounts are already decimal numbers. In French EDIFACT, the PRECISION column is used to convert amounts for the CONTROL_BEGIN_BALANCE column, the CONTROL_END_BALANCE column, the CASHFLOW_BALANCE column, the INT_CALC_BALANCE column, the ONE_DAY_FLOAT column, the TWO_DAY_FLOAT column, the AVAILABLE BALANCE column, the VALUE_DATED_BALANCE column, the AVERAGE_CLOSE_LEDGER_MTD column, the AVERAGE_CLOSE_LEDGER_YTD column, the AVERAGE_CLOSE__AVAILABLE_MTD column, and the AVERAGE_AVAILABLE_YTD column. If you do not enter a value for the PRECISION column, the precision you defined on the mapping template is used.

    For each column, you need to specify the location of the source data for this column in the bank statement file. In the Record ID field, enter the type of the record, whether it is a header, line, or trailer. In the Position field, enter the placement of the field within the record, relative to the beginning of the record. For example, in the BAI2 mapping template, the BANK_ACCOUNT_NUM column is mapped to Record ID 03 and Position 1, meaning the data for this column is copied from the first field of a record that has 03 as the identifier.

    You can map the same field to two different columns. For example, in the BAI2 mapping template, the STATEMENT_NUMBER column and the STATEMENT_DATE column are both mapped to Record ID 02 and Position 4.

    You can map part of a field to a column if the field contains information other than the source data for this column. In the Format field, enter the format in which the source data appears. The Bank Statement Loader program searches for this pattern in the field and copies the data if a match is found. If multiple matches are found, the Bank Statement Loader program selects the first match.

    The data in a field may come in one of several possible formats. In the Format field, you can enter multiple formats and separate them by commas. The Bank Statement Loader program applies the formats in the order they are specified and stops when a match is found. If multiple formats need to be applied, the Concatenate Format checkbox should be checked for the BANK_TRX_NUMBER field on the mapping template. If only one matching format is needed, do not check the Concatenate Format checkbox.

    A format may contain an indicator. An indicator is a token that always appears with, before, or after the data. If you expect to find spaces between the indicator and the source data, you must explicitly specify these spaces in the format. The chart in step 13 describes the types of formats supported by the Bank Statement Loader program.

    In the Include Indicator field, select the checkbox if you want to copy the indicator to the Bank Statement Headers Interface table. If you leave the checkbox blank, the Bank Statement Loader program excludes the indicator.

  13. The Lines tabbed region lists all the columns in the Bank Statement Lines Interface table. Define the mapping for the column according to the file format used by your bank. The four required columns in the table are:

    You must map the TRX_DATE column. The Bank Statement Loader program copies the values for the STATEMENT__NUMBER column and the BANK_ACCOUNT_NUM column from the respective columns in the Bank Statement Headers Interface table, and automatically generates a sequential value for the LINE_NUMBER column.

    For each column, you need to specify the location of the source data for this column in the bank statement file. In the Record ID field, enter the type of the record, whether it is a header, line, or trailer. In the Position field, enter the placement of the field within the record, relative to the beginning of the record.

    In most formats, the position should be a positive number. In the BAI2 format, however, the position can be negative, meaning that a field can be found only by counting its position from the end of the record. For example, the number of fields in record ID 16 varies depending upon information such as Funds Type. However, Text, Customer Ref No, and Bank Ref No are always located at the end of the record, where Text is the last field, Customer Ref No is the second to the last field, and Bank Ref No is the third to the last field. In the BAI2 mapping template, these fields are represented by negative positions. The BANK_TRX_NUMBER column and the TRX_TEXT column are mapped to the Text field as Record ID 16 and Position -1. The INVOICE_TEXT column is mapped to the Customer Ref No field as Record ID 16 and Position -2. The CUSTOMER_TEXT column is mapped to the Bank Ref No field as Record ID 16 and Position -3.

    You can map the same field to two different columns. For example, in the BAI2 mapping template, the BANK_TRX_NUMBER column and the TRX_TEXT column are both mapped to Record ID 16 and Position -1.

    You can map part of a field to a column if the field contains information other than the source data for this column. In the Format field, enter the format in which the source data appears. For example, in the BAI2 and SWIFT940 mapping template, the BANK_TRX_NUMBER column has the default format of Example (xxx). You should change this value to the actual format used by your bank. The Bank Statement Loader program searches for this pattern in the field and copies the data if a match is found. If multiple matches are found, the Bank Statement Loader program selects the first match.

    The data in a field may come in one of several possible formats. In the Format field, you can enter multiple formats and separate them by commas. The Bank Statement Loader program applies the formats in the order they are specified and stops when a match is found. If multiple formats need to be applied, the Concatenate Format checkbox should be checked for the BANK_TRX_NUMBER field on the mapping template. If only one matching format is needed, do not check the Concatenate Format checkbox. For example, the format for the BANK_TRX_ NUMBER should be entered in the order specified in the Payroll Matching Order field when defining a transaction code for matching Payroll EFT payments. Additionally, you should check the Concatenate Format checkbox.

    A format may contain an indicator. An indicator is a token that always appears with, before, or after the data. If you expect to find spaces between the indicator and the source data, you must explicitly specify these spaces in the format. In the example of the BANK_TRX_NUMBER column, the indicator is Example, followed by a space. In the Include Indicator field, select the checkbox if you want to copy the indicator to the Bank Statement Lines Interface table. If you leave the checkbox blank, the Bank Statement Loader program excludes the indicator.

    The Bank Statement Loader program supports both fixed and variable length formats. The notations used to define fixed length formats are:

    Variable formats use only the ~ notation, which stands for any number of alphanumeric and special characters. While fixed length formats may come with or without an indicator, variable length formats must be accompanied by an indicator. If you use either the fixed length format without an indicator or the variable format to extract the source data from a field that contains multiple segments of information, these segments must be delimited by white spaces.

    The following table summarizes the different types of formats and gives an example of each:

    Type Description Format
    Fixed length with indicator The information in the field has an indicator and the length of the data is fixed. The format must consist of the indicator and a pair of parentheses containing the pattern of the data.
    Example:
    If the Transaction Number starts with CR or DR and is made up of six digits followed by one character, the correct format to define is: CR (nnnnnnx), DR (nnnnnnx)
    This is an example of multiple formats. If the data is CR 1234561, the Transaction Number populated in the Bank Statement Lines Interface table is 1234561. If the data is DR 654321G, the Transaction Number populated is 654321G.
    Fixed length without indicator The information in the field has no indicator and the length of the data is fixed The format must be a pair of parentheses containing the pattern of the data.
    Example:
    If the Transaction Number has six digits followed by one character, the correct format to define is: (nnnnnnx). This is an example of a single format. If the data is 1234561, the Transaction Number populated in the Bank Statement Lines Interface table is 1234561.
    Variable length with indicator The information in the field has an indicator and the length of the data is variable. The format must consist of an indicator and a pair of parentheses containing the ~ notation.
    Example:
    If the Transaction Number starts with CR or DR followed by a space, and the length of the data is variable, the correct format to define is CR (~), DR (~). This is an example of multiple formats. If the data is CR 99999, the Transaction Number populated in the Bank Statement Lines Interface table is 99999. If the data is DR 654G, the Transaction Number populated is 654G.
    Variable length without indicator The information in the field is variable. This scenario cannot be supported.
  14. Select the Enabled checkbox to activate this mapping information.

  15. Save your changes.

Using a Custom Loader Program

The following steps describe how to use a custom loader program:

  1. Create a loader program to map the structure of the bank statement file to the Bank Statement Open Interface tables.

  2. Use your loader program to populate the Bank Statement Open Interface tables.

Bank Statement Open Interface Tables

The Bank Statement Open Interface consists of two tables:

Related Topics

Bank Statement Headers Interface Table

Bank Statement Lines Interface Table

Loading Bank Statement Open Interface

Bank Statement Validation

Importing Bank Statements

Correcting Bank Statement Interface Errors

Reconciling Bank Statements Automatically

System Parameters

Archiving and Purging Imported Bank Statements

Bank Statement Headers Interface Table

The CE_STATEMENT_HEADERS_INT table must contain exactly one record for each bank account within a bank statement. If a bank statement contains more than one account, define a header record for each of the accounts on the statement.

The following table shows the columns contained in the CE_STATEMENT_HEADERS_INT table:

Column Name Null? Type
STATEMENT_NUMBER N CHAR
BANK_NAME Y CHAR
BANK_BRANCH_NAME Y CHAR
BANK_ACCOUNT_NUM N CHAR
STATEMENT_DATE N DATE
CONTROL_BEGIN_BALANCE Y NUM
CONTROL_END_BALANCE Y NUM
CONTROL_TOTAL_DR Y NUM
CONTROL_TOTAL_CR Y NUM
CONTROL_DR_LINE_COUNT Y NUM
CONTROL_CR_LINE_COUNT Y NUM
CONTROL_LINE_COUNT Y NUM
RECORD_STATUS_FLAG Y CHAR
CURRENCY_CODE Y CHAR
ORG_ID Y NUM
CHECK_DIGITS Y CHAR
CASHFLOW_BALANCE Y NUM
INT_CALC_BALANCE Y NUM
ONE_DAY_FLOAT Y NUM
TWO_DAY_FLOAT Y NUM
INTRA_DAY_FLAG Y CHAR
AVERAGE_CLOSE_LEDGER_MTD Y NUM
AVERAGE_CLOSE_LEDGER_YTD Y NUM
AVERAGE_CLOSE_AVAILABLE_MTD Y NUM
AVERAGE_CLOSE_AVAILABLE_YTD Y NUM

Required Columns

You must provide values for all of the not-null columns in the CE_STATEMENT_HEADERS_INT table. Enter values in the following required columns:

STATEMENT_NUMBER: The identifier for the statement, as provided by the bank. If the bank does not assign a statement number, you may use the statement date, or any value that will be unique within the bank account.

BANK_ACCOUNT_NUM: The bank account number, as defined in Oracle Receivables, Oracle Payables, or Oracle Payroll.

STATEMENT_DATE: The closing date for the statement.

ORG_ID: The organization ID, required only for multiple organization environments.

Optional Columns

You may enter values in the following optional columns of the CE_STATEMENT_HEADERS_INT table:

BANK_NAME: The name of the bank, as defined in Oracle Receivables, Oracle Payables, or Oracle Payroll.

BANK_BRANCH_NAME: The name of the bank branch, as defined in Receivables and/or Payables.

CONTROL_BEGIN_BALANCE: The beginning balance for the statement, as provided by the bank.

CONTROL_END_BALANCE: The closing balance for the statement, as provided by the bank. In the BAI2 format, this is the Ledger Balance.

CONTROL_TOTAL_DR: Total Debits for the statement.

CONTROL_TOTAL_CR: Total Credits for the statement.

CONTROL_DR_LINE_COUNT: Number of debit lines for the statement.

CONTROL_CR_LINE_COUNT: Number of credit lines for the statement.

CONTROL_LINE_COUNT: Total number of lines for the statement.

RECORD_STATUS_FLAG: Set to E if there is an error in the record. Set to N if the record is new.

CURRENCY_CODE: The currency code for the bank account.

CHECK_DIGITS: Numbers used to validate the authenticity of a bank account number.

CASHFLOW_BALANCE:The available balance on the statement. In the BAI2 format, this is the Closing Available Balance.

INT_CALC_BALANCE: The balance on the statement that you want to use to calculate your interest. (Now called the Value Dated Balance.) In the BAI2 format, this is the Closing Available Balance.

ONE_DAY_FLOAT: The one-day float balance on the statement.

TWO_DAY_FLOAT: The two-day float balance on the statement.

INTRA_DAY_FLAG: Set to No if this is a previous-day bank statement. Set to Yes if this is an intra-day bank statement.

AVERAGE_CLOSE_LEDGER_MTD: The average closing ledger balance month to date.

AVERAGE_CLOSE_LEDGER_YTD: The average closing ledger balance year to date.

AVERAGE_CLOSE_AVAILABLE_MTD: The average closing available balance month to date.

AVERAGE_CLOSE_AVAILABLE_YTD: The average closing available balance year to date.

Related Topics

Bank Statement Open Interface

Bank Statement Lines Interface Table

Bank Statement Validation

Loading Bank Statement Open Interface

Importing Bank Statements

Correcting Bank Statement Interface Errors

Reconciling Bank Statements Automatically

System Parameters

Archiving and Purging Imported Bank Statements

Bank Statement Lines Interface Table

The CE_STATEMENT_LINES_INTERFACE table contains the transactions from the bank statement.

The following table shows the columns contained in the CE_STATEMENT_LINES_INTERFACE table:

Column Name Null? Type
BANK_ACCOUNT_NUM N CHAR
STATEMENT_NUMBER N CHAR
LINE_NUMBER N NUMBER
TRX_DATE N DATE
TRX_CODE Y CHAR
EFFECTIVE_DATE Y DATE
TRX_TEXT Y CHAR
INVOICE_TEXT Y CHAR
AMOUNT Y NUMBER
CURRENCY_CODE Y CHAR
EXCHANGE_RATE Y NUMBER
EXCHANGE_RATE_DATE Y DATE
USER_EXCHANGE_RATE_TYPE Y CHAR
ORIGINAL_AMOUNT Y NUMBER
CHARGES_AMOUNT Y NUMBER
BANK_TRX_NUMBER Y CHAR
CUSTOMER_TEXT Y CHAR
BANK_ACCOUNT_TEXT Y CHAR

Required Columns

You must provide values for all of the not-null columns in the CE_STATEMENT_LINES_INTERFACE table. Enter values in the following required columns:

BANK_ACCOUNT_NUM: For each statement line, enter the bank account number, as defined in the header record in CE_STATEMENT_HEADERS_INT.

STATEMENT_NUMBER: For each statement line, enter the bank statement number, as defined in the header record in CE_STATEMENT_HEADERS_INT.

LINE_NUMBER: For each statement line, enter the line number, as provided by the bank. If the bank does not provide line numbers, you may use a sequence to assign line numbers. The line numbers must be unique within the bank statement.

TRX_DATE: The date of the bank transaction.

Optional Columns

You may enter values in the following optional columns of the CE_STATEMENT_LINES_INTERFACE table:

TRX_CODE: The Bank Transaction Code that the bank uses to identify the type of banking transaction.

EFFECTIVE_DATE: The value date for the transaction.

TRX_TEXT: Any transaction-related text provided by the bank.

INVOICE_TEXT: This is the statement line invoice number, which is used to match statement lines to invoice numbers for payments in Payables and receipts in Receivables.

AMOUNT: The amount of the transaction, in the bank account currency.

CURRENCY_CODE: The currency code for the transaction.

EXCHANGE_RATE: The exchange rate specified by the bank for the transaction.

EXCHANGE_RATE_DATE: The date associated with the exchange rate used.

USER_EXCHANGE_RATE_TYPE: The exchange rate type (defined in Oracle General Ledger) used for this transaction.

ORIGINAL_AMOUNT: The amount of the transaction, expressed in its original currency.

CHARGES_AMOUNT: The amount of bank charges in the bank account currency.

BANK_TRX_NUMBER: The Bank Transaction Number. This is the payment document number or deposit number, which AutoReconciliation uses to match the bank transaction with a transaction or batch. If you do not provide a transaction number AutoReconciliation may import the record, but cannot reconcile it.

CUSTOMER_TEXT: Any customer-related text provided by the bank. AutoReconciliation does not use this information; it is for reference use only.

BANK_ACCOUNT_TEXT: Agent's bank account. AutoReconciliation uses this information to match transactions along with INVOICE_TEXT.

Related Topics

Bank Statement Open Interface

Bank Statement Headers Interface Table

Loading Bank Statement Open Interface

Bank Statement Validation

Importing Bank Statements

Correcting Bank Statement Interface Errors

Reconciling Bank Statements Automatically

System Parameters

Archiving and Purging Imported Bank Statements

Defining Conversion Rate Types, Oracle General Ledger User Guide

Reconciliation Open Interface

Using the Reconciliation Open Interface, you can reconcile settlements created in Oracle Treasury with your bank statements. You can also clear and reconcile payments and receipts that originate in applications other than Oracle Applications. For example, if you use a proprietary accounts receivable system with General Ledger and Cash Management, you can clear and reconcile your receipts by using the Cash Management Reconciliation Open Interface. In Cash Management windows and reports, Open Interface transactions include both Treasury settlements and external transactions.

The Cash Management Reconciliation Open Interface allows you to:

Related Topics

Preparing to Use Reconciliation Open Interface

CE_999_INTERFACE_V Description

Package CE_999_PKG Description and Modifications

Program Submission

After following the setup steps, you can run the AutoReconciliation program, or clear or reconcile open interface transactions manually. You do not run the Reconciliation Open Interface as a separate program. Cash Management automatically retrieves the necessary information from the open interface view (CE_999_INTERFACE_ V) for matching bank statement lines to external transactions.

Related Topics

Reconciling Bank Statements Automatically

Reconciling Bank Statements Manually

Manually Clearing and Unclearing

Finding Available Transactions, Batches, and Journal Entries for Reconciliation

Using Reconciliation Open Interface to Reconcile Treasury Settlements

Prerequisites to Reconciling Treasury Settlements

You can automatically or manually reconcile Treasury settlements. You can also unreconcile a Treasury settlement after it is reconciled. However, you cannot clear or unclear Treasury settlements.

Using Reconciliation Open Interface to Reconcile External Transactions

Prerequisites to Reconciling External Transactions

The Reconciliation Open Interface objects include the following view and package, which you would have customized to work in your environment:

When you upgrade Oracle Cash Management, AutoInstall replaces the objects in the $CE_TOP/admin/sql directory with the default ones provided by Oracle Cash Management, except for the cevw.odf which replaces what is in the $CE_TOP/admin/odf directory. If you have customized versions of the view and packages located in the $CE_TOP/admin/sql and the $CE_TOP/admin/odf directory with the same physical file names, you must make backup copies of these objects or move them to another directory before upgrading.

Note: If any of the above objects are patched, the patched objects will be located in $CE_TOP/patch/115/sql, except for odf files which will be located in the $CE_TOP/patch/115/odf. When the patch is applied, it will override your customized files if they are located in that directory with the same names.

Customizing the Reconciliation Open Interface Package and View

To use Cash Management's Reconciliation Open Interface, you must modify certain components of the Oracle database, which are provided when Cash Management is installed.

Warning: We strongly recommend that any modifications to your Oracle database or to your proprietary application's database and programs be made only by qualified individuals. If you are not sure whether you are qualified to make such modifications, contact an Oracle consultant.

When you install Cash Management, one template view and one package are added to your Oracle database:

The view and package are described in detail below.

Related Topics

Package CE_999_PKG Description and Modifications

CE_999_INTERFACE_V Description

Generally, you define CE_999_INTERFACE_V as a view to your proprietary application's database, and implement it to show all open interface transactions and their status. The CE_999_INTERFACE_V view should include available transactions, cleared transactions, as well as transactions reconciled to bank statements that have not been purged or archived.

Cash Management provides a template of this view in your Oracle database, to allow you to define your view more easily. Note that many elements of this table or view must join to other Oracle Financials reference tables. For example, you need to indicate a particular bank account, which must be defined in CE_BANK_ACCOUNTS.

Alternatively, if your proprietary system does not support real-time access from an Oracle database, you can create a table named CE_999_INTERFACE_V, then use SQL*Loader or another batch mechanism to populate the table with information about your open interface transactions.

The following table contains the key to table abbreviations:

Abbreviation Meaning
Req. Payment
Cat. Receipt
C Constant Value
D Defined in required tables
U User definable
M Mandatory join to defined tables
O Optional join to defined tables
S System Required

The following table contains the column specifications for CE_999_INTERFACE_V :

  Column Name Type Req. Cat. Description/Value
1 ROW_ID ROWID Yes S Transaction row ID.
2 TRX_ID NUMBER Yes U Transaction internal identifier (see note 2).
3 BANK_ACCOUNT_ID NUMBER Yes M CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID.
4 TRX_TYPE VARCHAR2 Yes C Transaction type of PAYMENT or CASH (see note 3).
5 TRX_TYPE_DSP VARCHAR2 Yes U Displayed, translated transaction type.
6 TRX_NUMBER VARCHAR2 Yes U Transaction number (see note 4).
7 TRX_DATE DATE Yes U Date of the transaction.
8 CURRENCY_CODE VARCHAR2 Yes D Transaction currency code.
9 STATUS VARCHAR2 Yes U Status of the transaction.
10 STATUS_DSP VARCHAR2 Yes U Displayed, translated transaction status.
11 EXCHANGE_RATE_TYPE VARCHAR2 No O NULL or GL_DAILY_CONVERSION_TYPES.CONVERSION_TYPE Required if transaction is foreign.
12 EXCHANGE_RATE_DATE DATE No U Date of exchange rate. Required if transaction is foreign.
13 EXCHANGE_RATE NUMBER No U Exchange rate. Required if transaction is foreign.
14 AMOUNT NUMBER Yes U Transaction amount (transaction currency).
15 CLEARED_AMOUNT NUMBER No U Cleared transaction amount (transaction currency).
16 CHARGES_AMOUNT NUMBER No U Transaction bank charges amount (transaction currency).
17 ERROR_AMOUNT NUMBER No U Transaction error amount (transaction currency).
18 ACCTD_AMOUNT NUMBER No U Transaction amount (ledger currency).
19 ACCTD_CLEARED_AMOUNT NUMBER No U Transaction cleared amount (ledger currency).
20 ACCTD_CHARGES_AMOUNT NUMBER No U Transaction charges amount (ledger currency).
21 ACCTD_ERROR_AMOUNT NUMBER No U Transaction error amount (ledger currency).
22 GL_DATE DATE Yes U GL date.
23 CLEARED_DATE DATE No U Date the transaction is cleared.
24 creation_date DATE Yes S Standard Who Column.
25 created_by NUMBER Yes S Standard Who Column.
26 last_update_date DATE Yes S Standard Who Column.
27 last_updated_by NUMBER Yes S Standard Who Column.

Notes:

  1. The following table contains notes for specific columns:

    Column Number Note
    3 The bank account must be defined in AP_BANK_ACCOUNTS view.
    8 The currency code must be defined in FND_CURRENCIES table.
    9 The STATUS must be defined in one of the following fields in CE_SYSTEM_PARAMETERS table: OPEN_INTERFACE_FLOAT_STATUS, or OPEN_INTERFACE_CLEAR_STATUS.
    11 If used, the exchange rate type must be defined in GL_DAILY_CONVERSION_TYPES table.
  2. Transaction identifiers (TRX_ID) must be unique within the view. You cannot use duplicate transaction identifiers at any time.

  3. Transaction numbers (TRX_NUMBER), used for matching by the automatic reconciliation program when the Open Interface Matching Criteria system parameter is set to Transaction Number, should be:

    Note: The transaction number, whether a payment or receipt number, appears throughout Cash Management - on inquiry windows, reconciliation windows, and reports.

  4. Available, cleared, and reconciled transactions: Cash Management uses the Open Interface Float Status and Open Interface Clear Status system parameters to determine whether a transaction is available for clearing or reconciliation. Cash Management maintains information of transactions that have been reconciled to bank statements, but you must customize the CE_999_PKG package to update the status of the transactions in your proprietary application's database. The status is displayed in Cash Management's forms and reports.

    To find available transactions for clearing or reconciliation, Cash Management selects transactions in the CE_999_INTERFACE_V view with the Open Interface Float Status that have not been reconciled to a bank statement line. During reconciliation, if you have enabled the Show Cleared Transactions check box in the System Parameters window, Cash Management also finds transactions that have been cleared but not reconciled.

    To find cleared transactions, Cash Management selects transactions with the Open Interface Clear Status that have not been reconciled to a bank statement line. To find reconciled transactions, Cash Management selects transactions with the Open Interface Clear Status that have been reconciled to a bank statement line.

Related Topics

Using Reconciliation Open Interface to Reconcile External Transactions

Package CE_999_PKG Description and Modifications

Package CE_999_PKG consists of a function and two procedures:

Cash Management provides an empty version of CE_999_PKG in your Oracle database. You must modify this package and write the above described routines if you want to update data in your proprietary application or generate reconciliation accounting entries.

We have included examples of functions in the CE_999 package that you can use or customize.

The remainder of this section describes information you will need before you can write the function and procedures.

Note: CE_999_PKG is called by Cash Management after each transaction is processed, rather than in batch mode. Accordingly, your function and procedures should be written to handle transactional processing.

Locking Function

The locking function is named CE_999_PKG.LOCK_ROW. The function must accept the following parameters passed from Cash Management.

The following table contains parameters the function must accept that are passed from Cash Management:

Parameter Name Data Type Description
X_CALL_MODE VARCHAR2 'U' if the transaction is to be cleared or reconciled. 'M" if the transaction is to be uncleared or unreconciled..
X_TRX_TYPE VARCHAR2 The transaction type (TRX_TYPE)
X_TRX_ROWID ROWID ROWID of the transaction

Clearing Procedure

The clearing procedure is named CE_999_PKG.CLEAR.The following tables shows the parameters the procedure must accept that are passed from Cash Management. All of the parameters, except for X_BANK_CURRENCY, come from CE_999_INTERFACE_V:

Parameter Name Data Type Column in CE_999_INTERFACE_V
X_trx_ID NUMBER TRX_ID
X_trx_type VARCHAR2 TRX_TYPE (value is PAYMENT or CASH)
X_STATUS VARCHAR2 STATUS (value comes from ce_bank_accounts.recon_oi_cleared_statu)
X_trx_NUMBER VARCHAR2 trx_NUMBER
X_trx_date DATE trx_date
X_trx_CURRENCY VARCHAR2 CURRENCY_CODE
X_gl_date DATE gl_date
X_BANK_currency VARCHAR2 AP_BANK_ACCOUNTS.BANK_currency_code
X_cleared_amount NUMBER cleared_AMOUNT
X_charges_amount NUMBER charges_amount
X_errors_amount NUMBER error_amount
X_exchange_date DATE exchange_rate_date
X_exchange_type VARCHAR2 exchange_rate_type
X_exchange_rate NUMBER exchange_rate

Use the passed parameters to code the procedural logic needed to clear transactions in your proprietary application's database. You need to implement this routine to generate reconciliation accounting entries or update data in the open interface transactions source application. Enter the same value in X_STATUS_DSP as you entered in X_STATUS.

Unclearing Procedure

The unclearing procedure is named CE_999_PKG.UNCLEAR. The following tables shows the parameters the procedure must accept that are passed from Cash Management. All of the parameters come from CE_999_INTERFACE_V.

Parameter Name Data Type Column in CE_999_INTERFACE_V
X_trx_id NUMBER trx_id
X_trx_type VARCHAR2 trx_type
X_STATUS VARCHAR2 STATUS (value comes from ce_bank_accounts.recon_oi_float_status)
X_trx_date DATE trx_date
X_gl_date DATE gl_date

Use the passed parameters to code the procedural logic needed to unclear transactions in your proprietary application's database. You need to implement this routine to reverse reconciliation accounting entries or update data in the open interface transactions source application. Enter the same value in X_STATUS_DSP as you entered in X_STATUS.

Related Topics

Preparing to Use Reconciliation Open Interface

CE_999_INTERFACE_V Description

Installing External Cash Flow Open Interface Objects in Remote Databases

This step is required only if you are upgrading and planning to use External Cash Flow Open Interface in a distributed database environment. You must install the necessary Cash Forecasting objects in each remote database with your Cash Forecasting sources in your APPS schema.

The following table contains the necessary Cash Forecasting objects:

Object Physical File Name Directory
CE_FORECAST_EXT_TEMP (table) cefextmp.sql $CE_TOP/sql
CE_FORECAST_EXT_TEMP_NI (index) cefextmp.sql $CE_TOP/sql
CE_FORECAST_REMOTE_SOURCES (package) cefremts.pls $CE_TOP/admin/sql
CE_FORECAST_REMOTE_SOURCES (package) cefremtb.pls  

Type the following commands:

$cd $CE_TOP/sql
$sqlplus <APPS username>/<APPS password>@remote database @cefextmp.sql
$cd $CE_TOP/admin/sql
$sqlplus <APPS username>/<APPS pasword>@remote database @cefremts.pls
$sqlplus <APPS username>/<APPS password>@remote database @cefremtb.pls

External Cashflow Open Interface

The Cash Management External Cashflow Open Interface allows you to utilize the following external sources of data as cash inflow and outflow data for flexible cash forecasting and cash positioning:

Providing an Open Interface to Forecast or Position from External Applications

Two source transaction types, Open Interface Inflow and Open Interface Outflow, allow you to include external application transaction sources for cash forecasting and cash positioning. Having two separate source transaction types for external source transactions gives you an easy way to indicate whether the transaction source consists of cash receipts (inflow) or disbursements (outflow).

The External Cashflow Open Interface collects cash flow amounts from your external systems, and the Cash Forecasting and Cash Positioning modules summarize and calculate the data to include in your cash forecasts and cash positions along with other Oracle Applications data.

See: Preparing to use the External Cashflow Open Interface.

Forecasting and Positioning in a Distributed Environment

You can include transactions located on a remote database in your cash forecasts and cash positions. For each Oracle Application or non-Oracle application source type on a distributed database, you need to specify a database link.

Related Topics

About Cash Forecasting

Cash Forecasting

Creating Cash Forecast Templates

Generating Cash Forecasts from Templates

Program Submission

After following the setup steps in this document, you can generate a cash forecast and cash position and automatically retrieve information available from the external sources you have defined. (You do not run the External Cashflow Open Interface as a separate program.)

Preparing to Use the External Cashflow Open Interface

To use the External Cashflow Open Interface effectively, you need to perform these steps in the following sequence. Each step has substeps that are explained in the referenced section.

  1. For each external source transaction type, you define its name, such as External Payments, or External Receipts. Then identify the valid selection criteria. See: Define External Source Types and Selection Criteria.

  2. Define value sets to be used for the selection criteria segments. See: Define Value Sets for Selection Criteria.

  3. Define the Forecast Selection Criteria Descriptive Flexfield segments for each selection criteria. See: Define Forecast Selection Criteria Descriptive Flexfield Segments.

  4. Define the external forecast source transaction database views. See: Define External Source Views.

  5. Associate the external source types with the views, and if necessary, the name of the database link. See: Associate External Forecast Source Types With Views.

Define External Source Types and Selection Criteria

  1. Determine the external source types that you want to include in your cash forecasts and cash positions.

  2. Determine appropriate selection criteria for each type.

  3. Identify the selection criteria common to all external source types.

  4. Identify the selection criteria appropriate only for specific external source types.

  5. Design the use of criteria segments.

    Use the Descriptive Flexfields worksheets located in the appendix of the Oracle Applications Flexfields Guide to help you with your design.

Define Value Sets for Selection Criteria

You need to define value sets that determine the values users can enter for selection criteria for open interface inflow and outflow source types when defining forecast templates and positioning worksheets.

  1. Navigate to the Value Sets window (Descriptive Flexfield Segments window > Segments button > Value Sets button).

  2. Define your value set and give it a validation type, and enter the appropriate validation information to suit your selection criteria.

Define Forecast Selection Criteria Descriptive Flexfield Segments

  1. Navigate to the Descriptive Flexfield Segments window.

  2. Enter Oracle Cash Management in the Application field, and Forecast Selection Criteria in the Title field.

  3. Uncheck the Freeze Flexfield Definition check box.

  4. Refer to the worksheet you filled out in designing your descriptive flexfield.

  5. Check the Freeze Flexfield Definition check box and save your work.

Define External Source Views

Define the External Source Transaction View as a view that will access your proprietary application's database to include all open interface transactions that you want to select for cash forecasting and cash positioning.

Alternatively, if your proprietary application system does not support real-time access from an Oracle database, you can create a table for the External Source Transaction View, and use SQL*Loader or another batch mechanism to populate the table with information from the non-Oracle system transactions.

Cash forecasting and Cash positioning share the same Open Interface setup. However, you can only use the Cash Flow Open Interface feature for cash positioning if the External Transaction view includes the BANK_ACCOUNT_ID column. The BANK_ACCOUNT_ID column is optional when you use the Open Interface for cash forecasting.

Column specifications for the External Source Transaction View are outlined in the following table:

Column Name Type Required? Description/Value
ROW_ID ROWID Yes Row ID
TRANSACTION_AMOUNT NUMBER Yes Amount in transaction currency
FUNCTIONAL_AMOUNT NUMBER Yes Amount in ledger currency
CURRENCY_CODE VARCHAR2(15) Yes Transaction currency code, must be valid currency code defined in FND_CURRENCIES
FUNCTIONAL_CURRENCY VARCHAR2(15) Yes ledger currency code, must be valid currency code defined in FND_CURRENCIES
CASH_ACTIVITY_DATE DATE Yes Projected cash transaction date, date when the transaction has become or will become a cash transaction (e.g. check date)
BANK_ACCOUNT_ID NUMBER No Bank Account ID, must be valid bank account id stored in BANK_ACCOUNT_ID column of AP_BANK_ACCOUNTS_ALL table
CRITERIA1...15 VARCHAR2(150) No Selection criteria

Note: Organization ID is not included as a default criterion. However, if you want to select transactions for a specific organization, you can define it as a common selection criterion for all external source types.

If your environment includes a distributed source transaction type, either you must import remote data to the local database, or you must establish a database link from the local database to the remote database. See: Associate External Forecast Source Types with Views.

Using Remote Databases

If you are planning to use the Cash External Cashflow Open Interface in a distributed database environment, your database administrator must install the necessary Cash Forecasting objects to each of the remote databases with your External Cashflow sources in your APPS account.

These objects are located in the $CE_TOP/sql directory:

These objects are located in the $CE_TOP/admin/sql directory:

If any of the above objects are patched for Release 11, the patched objects will be located in $CE_TOP/patch/110/sql. Therefore, apply the objects from the $CE_TOP/patch/110/sql directory instead of from the $CE_TOP/admin/sql and $CE_TOP/sql directories.

Use of Indexes on Views

To maximize performance when generating cash forecasts and cash positions that collect data using the External Cashflow Open Interface, we recommend that you create appropriate indexes on the tables accessed by the views you create. The columns that are frequently used as selection criteria, such as cash activity date, should be defined as indexes.

Associate External Forecast Source Types With Views

You need to associate your external source types with an external source transaction view, and if that view is on a remote database, then you need to specify the database link as well.

To associate external forecast source types and views:

  1. Navigate to the External Forecast Sources window.

  2. Choose an external forecast source Type. The list of values is the context field of the Forecast Selection Criteria Descriptive Flexfield.

  3. Enter an external forecast source View. This field is not validated until you submit a forecast that uses the associated external forecast source type.

  4. Optionally, enter a Database Link Name, if the source type is on a remote database. The list of values includes currently available links, but you can enter others as this field is not validated until you submit a forecast that uses the associated external forecast source type.

Related Topics

Planning Your Descriptive Flexfields, Oracle Applications Flexfields Guide

Defining Descriptive Flexfield Structures, Oracle Applications Flexfields Guide

Descriptive Flexfield Segments Window, Oracle Applications Flexfields Guide

Context Field Values, Oracle Applications Flexfields Guide

Defining Value Sets, Oracle Applications Flexfields Guide

Creating Cash Forecast Templates

Sequential Document Numbering

Cash Management supports Oracle Application's Document Sequences feature. With this feature enabled, you can have Cash Management assign sequential document numbers to your bank statements.

To set up sequential document numbering:

  1. Choose the System Administrator responsibility.

  2. Define Document Flexfield: You define and enable the Document Flexfield, which may consist of two, three or four segments. Possible segments include Application, Category, Ledgers and Method.

  3. Define Document Sequences: You enter a sequence Name and enter Oracle Cash Management as the Application that "owns" the sequence. You can also specify Effective From and To dates and the sequence numbering Type (Automatic, Gapless, or Manual).

  4. Define Document Categories: You define one document category for each bank account for which you want to use sequential numbering. In the Code field you enter the bank account name. In the Table Name field you enter CE_STATEMENT_HEADERS.

  5. Assign Document Sequences to Documents: In the Sequence Assignments window you must map your Document Categories to a Document Sequence. You can also specify a Start and End Date for the assignment.

  6. Set the Sequential Numbering System Profile Option: You or your System Administrator must enable sequential numbering for Cash Management. Use the System Profile Values window to set the Sequential Numbering profile option at the Application level. Choose Cash Management as the Application. Query the Sequential Numbering profile name. You can select from Always Used, Partially Used, or Not Used.

    Note: If you select Always Used, then to have miscellaneous receipts automatically created during the AutoReconciliation process, you must create and assign a sequence to the payment method for the receipt. The sequence numbering type must be Automatic.

Related Topics

Sequence Assignments Window, Oracle Applications System Administrator's Guide

Document Sequences, Oracle Applications System Administrator's Guide

Entering Bank Statements Manually

Reconciling Bank Statements Automatically

Cash Transaction Subtypes

Cash Transaction Subtypes allows you to categorize cashflows for reporting purposes. Once you create a subtype using Cash Transaction Subtypes > Setup > Transactions, you can assign it to either Bank Account Transfers or Bank Statement Cashflows. You can also build a hierarchy between the subtypes by entering a subtype parent name.

Set Up Cash Transaction Subtypes

Use the following steps to set up Cash Transaction Subtypes:

  1. Navigate to the Cash Management Subtypes page, select the Create button.

  2. Apply new information in the Update Cash Transaction Subtypes page.

  3. Enter a Subtype Name (required), for example, Rent or Travel and Entertainment.

  4. Enter a Short Name.

  5. Enter Subtype Parent Name.

  6. Select a Status from the list of values.

  7. Click Apply.