System Setup

Matching Bank Statement Lines with Transactions

Reconciliation Tolerances

Creating Miscellaneous Transactions

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:

Purge: If you select this check box, the Bank Statement Import program automatically purges all information from the Bank Statement Open Interface tables, after the information has been successfully transferred to the Bank Statement tables. If you import intra-day bank statements, the Intra-Day Bank Statement Import program automatically purges imported bank statement information from the Bank Statement Open Interface tables. You can purge bank statements without first archiving them.
Archive: If you select this check box, the Bank Statement Import program automatically archives all information from the Bank Statement Interface tables, once the information has been successfully transferred to the Bank Statement tables. If you import intra-day bank statements, the Intra-Day Bank Statement Import program automatically archives imported bank statement information from the Bank Statement Open Interface tables. You cannot archive bank statements without also purging them.

Note: You cannot use these two options to automatically archive or purge information from the Bank Statement tables or the Intra-Day Bank Statement tables. You must run the Archive/Purge Bank Statements program.

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

Exchange Rate Type: Bank Statement Cashflows and Bank Account Transfers programs select the exchange rates for the transaction currency using the exchange rate type you specify. You can choose any exchange rate type defined in Oracle General Ledger
Bank Statement Cashflow Date: Bank Statement Cashflows program selects the exchange rates for the transaction currency using the date source you specify. You can choose any of the following exchange rate dates:
- Cashflow Date: The date the cashflow is created by the Bank Statement Cashflows program.
- Bank Statement Date: The closing date of the statement.
- GL Date: The accounting date used to clear the statement.
- Cleared Date: The date the transaction cleared the bank.
Bank Transfer Rate Date: Bank Account Transfers program selects the exchange rates for the transaction currency using the date source you specify. You can choose any of the following exchange rate dates:
- Transfer Date: The date the bank account transfer occurred.
- Statement Line Date: The bank statement line date.
- Bank Statement Date: The closing date of the statement.
- GL Date: The accounting date used to clear the statement.
- Cleared Date: The date the transaction cleared the bank.
- Actual Value Date: The statement line value date.
- Cashflow Date: The date the cashflow is created from the bank account transfer.

Bank Account Transfers

Authorization: This parameter provides two options:
- Not Required: Authorization is not required for bank account transfer to be settled. Bank Account transfer is changed to settled status right after the creation.
- Required: Authorization is required for bank account transfers to be settled.

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.

Bank Account Use security grants users the access to one or more legal entities. Bank Account Use security setup is required to reconcile cashflows in bank statement reconciliation. Bank statement line can be reconciled to a cashflow only if access to the cashflow LE is granted when setting up Bank Account Use security.
Bank Account Use security setup is also required if the user does not have MOAC, Payroll or Treasury security set up, but wants to include bank accounts in Cash Forecasting, Cash Positioning, and Cash Pools. Only those bank accounts whose owner LE is registered in Bank Account Use security will be able to access these features.
Bank Account Use 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.
Treasury security defined in Oracle Treasury provides a list of legal entities that Treasury users have access to.
MOAC security provides a list of operating units that a given user has access to.
Payroll security returns a business groups that a given user has access to based on the PER_BUSINESS_GROUP_ID profile option.

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:

Bank Account Maintenance security – control bank account creation and updates
Bank Account Use security – control bank account access
Bank Account Fund Transfers security – control bank account transfers

To launch the Cash Management Security Wizard

Navigate to User Management > Roles & Role Inheritance page
Enter your role/responsibility name
Click Go.
Select the role/responsibility and click Update.
In the Update Role page, click Security Wizard.
Click Run to execute the Cash Management Security wizard.

To Set Up Security

Add Legal Entity: To add additional legal entities you like to assign to role.
Use: Select to assign the legal entity to Bank Account Use security.
Maintenance: Select to assign the legal entity to Bank Account Maintenance security.
Bank Account Transfers: Select to assign the legal entity to Bank Account Transfers security.
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:

Navigate to the Bank Transaction Codes window.
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.
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:
- Payment: Payments such as generated or recorded checks, payment batches, wire transfers, electronic funds transfers, or payroll checks.
- Receipt: Receipts such as received checks, remittance batches, direct debits, and bills of exchange.
- Miscellaneous payment: Payments not associated with supplier invoices, such as petty cash transactions directly posted to cost accounts or bank charges.
- Miscellaneous receipt: Receipts not associated with customer invoices, such as petty cash transactions directly posted to revenue accounts, such as interest received.
- Stopped: Stopped payments previously entered, generated, or cleared, such as callback of check. A stopped transaction type matches only to Voided or Stopped payments in Payables or Oracle Payroll.
- Rejected: Receipts rejected for reasons other than non-sufficient funds, such as an invalid bank account. A rejected transaction type matches only to reversed receipts in Receivables.
- NSF (Non-Sufficient Funds): Receipts rejected by the bank because the accounts on which they were drawn had non-sufficient funds. You can reverse these receipts by creating a standard reversal. Cash Management reopens the invoices you closed with the original receipt. When you match bank statement lines with transactions, an NSF transaction type only matches to reversed receipts in Receivables.
- Sweep In: Incoming funds transfer from an internal bank account. This bank transaction type identifies the originating bank account.
- Sweep Out: Outgoing funds transfer from an internal bank account. This bank transaction type identifies the receiving bank account.
Enter the code used by your bank.
Optionally enter a description of the transaction.
Enter start and end dates to determine when the bank transaction code is considered active.
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.
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.
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.
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.
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:
- Miscellaneous: Only match against miscellaneous transactions.
- Statement: Identify the statement line as a correcting entry. The statement line will match against existing statement lines. The netted amount of these lines is used to match to subledger transactions.
- Miscellaneous, Statement: First try to match against miscellaneous transactions, if there is no match, then try to match against statement lines (corrections).
- Statement, Miscellaneous: First try to match against statement lines (corrections), if there is no match, then try to match against miscellaneous transactions.
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.
Choose whether to create transactions for any miscellaneous payments and miscellaneous receipts reported on the bank statement when no transaction number is provided.
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.
Define each additional bank transaction code, following the previous steps.
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.

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

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:

BAI2
SWIFT940
User-defined

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

The bank statement information is delivered in a flat file.
The bank statement file has the following structure:
- One Header record
- One or more Line records that belong to the Header
- One Trailer record
A file may contain multiple bank statements, but each bank statement must begin with a Header record, followed by a group of Line records, and optionally end with a Trailer record.
Each record begins with an identifier indicating the record type: Header, Line, or Trailer.
The field definitions within each record type are consistent within a file.
Each record has no more than 35 fields.
Each field has no more than 255 characters. Any field that exceeds this limit must be stored in COLUMN1 of the intermediate table.

**Developing your custom SQL*Loader script**

The Bank Statement Loader program uses the SQL*Loader script to copy data from the bank statement file to the intermediate table. The intermediate table is a temporary storage of the file in the database, where a row corresponds to a record in the file and a column corresponds to a field in the record. The Bank Statement Loader program purges the table before inserting data into it. You can consult the SQL*Loader script provided by Cash Management for the French EDIFACT format for an example.
Your SQL*Loader script must reside in the $CE_TOP/bin directory, where $CE_TOP is the top directory for the Cash Management product. The name of the script must be in upper case and must end with the extension of ctl.
The SQL*Loader script needs to parse the bank statement file and exclude any extraneous records.
The SQL*Loader script must populate the REC_NO column and the REC_ID column in the intermediate table for each record loaded from the bank statement file. REC_NO indicates the order of a record in the file. You can use the SQL function of RECNUM to populate the REC_NO column. REC_ID is an identifier of the record type. Common record types found in most bank statement file formats are Header, Line, and Trailer.
The SQL*Loader script must store the data from each field as a character string in the intermediate table, except for the REC_NO, which is a number column.
If a field exceeds 255 characters, the SQL*Loader script must store the data in COLUMN 1 of the intermediate table, because COLUMN 1 can contain up to 2000 characters.
In some formats the record structure may change dynamically. For example, record ID 03 and record ID 16 in the BAI2 format have a variable number of fields. The SQL*Loader script must interpret the format and load the data into the appropriate columns in the intermediate table.
If zeros are used to fill a field, the SQL*Loader script should treat them as nulls. For example, In the French EDIFACT format, each field has a fixed position and a fixed length. If a field, such as CURRENCY_CODE, PRECISION, or BANK_TRX_ NUMBER, does not contain any value, it is filled with zeros. In this case, the French EDIFACT SQL*Loader script converts the zeros to nulls before loading the values into the intermediate table. Similarly, if a value does not fully occupy a field such as BANK_ACCOUNT_NUM, AMOUNT, BANK_TRX_NUMBER, CONTROL_BEGIN _BALANCE or CONTROL_END_BALANCE, the value is padded with leading zeros. The French EDIFACT SQL*Loader script ignores these leading zeros when loading the values into the intermediate table.
Amounts may need to be converted into a common format in the SQL*Loader script. For example, decimal points in the SWIFT940 format are not represented by periods. Since numbers stored in the ORACLE database use periods as the decimal separator, the SQL*Loader script must convert that character to a period. You can see an example of this conversion rule in the SWIFT940 SQL*Loader script. In other formats, amounts are expressed in integers qualified by a precision. If the precision varies because of the currency of the amounts, the SQL*Loader script needs to load the precision into the intermediate tables as well. See the French EDIFACT SQL*Loader script for an example.

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.

Open the Bank Statement Mappings window.
If you want to review a mapping template, choose one from the list of values, else cancel the list of values.
Create a new record for the new mapping template.
In the Name field, enter the name of the new mapping template.
In the Description field, enter the description for the new mapping template.
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.
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.
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.
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.
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.
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.
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:
- STATEMENT_NUMBER
- BANK_ACCOUNT_NUM
- STATEMENT_DATE
- ORG_ID
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.

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:

STATEMENT_NUMBER
BANK_ACCOUNT_NUM
LINE_NUMBER
TRX_DATE

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:

n a digit
a a non-digit character, like a letter or a special character
x any character, including digit, letter, or special character

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.

Select the Enabled checkbox to activate this mapping information.
Save your changes.

Using a Custom Loader Program

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

Create a loader program to map the structure of the bank statement file to the Bank Statement Open Interface tables.
- You must develop a separate loader program for each unique bank statement file structure.
- Loader programs are generally developed by your System Administrator or an Oracle consultant.
- Loader programs are usually written in SQL*Loader. However, depending on the complexity of the file format, you might choose to use one of a variety of other programming languages.
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:

Bank Statement Headers Interface Table[Symbol_arrowhorizex]contains the bank statement header information. The table is named CE_STATEMENT_HEADERS_INT.
Bank Statement Lines Interface Table[Symbol_arrowhorizex]contains the bank statement transaction lines. The table is named CE_STATEMENT_LINES_INTERFACE.

Related Topics

Bank Statement Headers Interface Table

Bank Statement Lines Interface Table

Loading Bank Statement Open Interface

Bank Statement Validation

Correcting Bank Statement Interface Errors

System Parameters

Bank Statement Open Interface

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 Lines Interface Table

Bank Statement Validation

Loading Bank Statement Open Interface

Correcting Bank Statement Interface Errors

System Parameters

Bank Statement Open Interface

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 Headers Interface Table

Loading Bank Statement Open Interface

Bank Statement Validation

Correcting Bank Statement Interface Errors

System Parameters

CE_999_INTERFACE_V Description

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:

Find Treasury settlements that are available for reconciliation
Automatically or manually reconcile Treasury settlements to bank statement lines
Find reconciled Treasury settlements
Find external transactions that are available for clearing
Find external transactions that are available for reconciliation
Manually clear external transactions
Automatically or manually reconcile external transactions to bank statement lines
Find cleared external transactions
Find reconciled external transactions

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.

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

Set up Oracle Treasury. See: Setting Up Oracle Treasury for Cash Management Integration
Define bank accounts with Treasury Use and enter Treasury bank account specific information.
Check Use Reconciliation Open Interface in the bank account's Open Interface Controls region. See: Bank Account Window Reference
In the Open Interface Controls region, define the Float Status and Clear Status parameters.
Choose the Date and Amount option for the Open interface Matching Criteria parameter.
If you want to automatically reconcile Treasury settlements, define the transaction codes for the bank accounts used in Treasury. See: Transaction Codes
Create new mapping templates to load bank statements from the bank accounts used in Treasury. See: Bank Statement Mapping
If your bank provides electronic bank statements in formats other than BAI2, map the balance information in your bank statement files to the CONTROL_END_BALANCE, CASHFLOW_BALANCE, INT_CALC_BALANCE, ONE_DAY_FLOAT, TWO_DAY_FLOAT, AVERAGE_CLOSE_LEDGER_MTD, AVERAGE_CLOSE_LEDGER_YTD, AVERAGE_CLOSE_AVAILABLE_MTD, and AVERAGE_CLOSE_AVAILABLE_YTD columns in the Bank Statement Headers Interface table. If your bank delivers BAI2 bank statements, however, you do not need to map these columns because the Bank Statement Loader program uses the pre-defined BAI2 transaction codes to locate the balance information in the bank statement files. See: Bank Statement Headers Interface Table.

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

Check the Use Reconciliation Open Interface check box in the bank account's Open Interface Controls region.
Define the Open Interface Float Status and Open Interface Clear Status system parameters. Select the Open Interface Matching Criteria system parameter.
If you want to automatically reconcile external transactions, define the transaction codes for your bank accounts.
Customize the Reconciliation Open Interface package and view.

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

CE_999_INTERFACE_V (view) - cevw.odf (physical file)
CE_999_PKG (package)
- ceab999s.pls (physical file)
- ceab999b.pls (physical file)

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:

CE_999_INTERFACE_ V - a view you define to map to your proprietary application's database, which shows all open interface transactions and their status.
CE_999_PKG - a package that contains one function and two procedures. To enable the related functionality in Cash Management, you must write the necessary routines to carry out the described package function and procedures.

The view and package are described in detail below.

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:

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.

Transaction identifiers (TRX_ID) must be unique within the view. You cannot use duplicate transaction identifiers at any time.
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:
- For payments, a payment number.
- For receipts, a receipt number.
Note: The transaction number, whether a payment or receipt number, appears throughout Cash Management - on inquiry windows, reconciliation windows, and reports.
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.

Package CE_999_PKG Description and Modifications

Package CE_999_PKG consists of a function and two procedures:

Locking function: a routine to lock transactions in your proprietary application's database before clearing or reconciliation. You might want to lock transactions so that users cannot change the amounts during the clearing or reconciliation process. Cash Management calls this function when you clear, reconcile, unclear, or unreconcile open interface transactions.
Clearing procedure: procedural logic to clear transactions in your proprietary application's database. Cash Management calls this procedure when you clear or reconcile open interface transactions. You need to implement this routine to update information about the transactions in your proprietary application. For example, the status of the cleared or reconciled transactions should be changed to the value defined in the Open Interface Clear Status system parameter.
Unclearing procedure: procedural logic to unclear transactions in your proprietary application's database. Cash Management calls this procedure when you unclear or unreconcile open interface transactions. You need to implement this routine to update information about the transactions in your proprietary application. For example, the status of the uncleared or unreconciled transactions should be changed to the value defined in the Open Interface Float Status system parameter.

Note: Cash Management does not generate accounting entries when clearing or reconciling open interface transactions. Therefore, to meet your specific needs you may want to enhance the above described functionality of the CE_999_PKG function and procedures. For example, you can use the information passed to the clearing procedure to create journal entries for the differences between recorded and cleared transaction amounts.

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

Uncomment (remove the slashes and asterisks from) the code enclosed by /*---- .... ----*/

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.

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:

Non-Oracle applications on a local database
Non-Oracle Applications on a remote database
Oracle Applications on a remote database

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.

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.
Define value sets to be used for the selection criteria segments. See: Define Value Sets for Selection Criteria.
Define the Forecast Selection Criteria Descriptive Flexfield segments for each selection criteria. See: Define Forecast Selection Criteria Descriptive Flexfield Segments.
Define the external forecast source transaction database views. See: Define External Source Views.
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

Determine the external source types that you want to include in your cash forecasts and cash positions.
Determine appropriate selection criteria for each type.
Identify the selection criteria common to all external source types.
Identify the selection criteria appropriate only for specific external source types.
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.

Navigate to the Value Sets window (Descriptive Flexfield Segments window > Segments button > Value Sets button).
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

Navigate to the Descriptive Flexfield Segments window.
Enter Oracle Cash Management in the Application field, and Forecast Selection Criteria in the Title field.
Uncheck the Freeze Flexfield Definition check box.
Refer to the worksheet you filled out in designing your descriptive flexfield.
- Include the common selection criteria that are applicable to all external source types.
- Specify external source types in the Context Field region.
- For each external source type defined as a context field value, define the criteria segments that are used by the specific external source type as the context-sensitive segments. These are the selection criteria that depend on the external source types.
- All segments must be enabled and non-required.
- Choose the value sets that you predefined in the Value Set field.
- Do not update any existing default entries.
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:

CE_FORECAST_EXT_TEMP (table) - cefextmp.sql (physical file)
CE_FORECAST_EXT_TEMP_N1 (index) - cefextmp.sql (physical file)

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

CE_FORECAST_REMOTE_SOURCES (package) - cefremts.pls and cefremtb.pls (physical files)

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:

Navigate to the External Forecast Sources window.
Choose an external forecast source Type. The list of values is the context field of the Forecast Selection Criteria Descriptive Flexfield.
Enter an external forecast source View. This field is not validated until you submit a forecast that uses the associated external forecast source type.
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:

Choose the System Administrator responsibility.
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.
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).
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.
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.
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