Managing Bank Statements

Entering Bank Statements

Before you can reconcile transactions against a bank statement, you need to enter the bank statement information into Oracle Cash Management. You can accomplish this task by doing one of the following:

Loading the Bank Statement Open Interface Tables

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 use the Bank Statement Open Interface to load prior day bank statements for reconciliation. You can also load intra-day bank statement information for cash positioning into the Bank Statement Open Interface tables using the Intra-Day Bank Statement Loader program, by using a custom loader, or by using the automatic bank transmission feature.

The Bank Statement Loader Program

The Bank Statement Loader program is a platform-independent solution for mapping and transferring bank statement information from an electronic bank statement into the open interface tables in Oracle Cash Management.

The Bank Statement Loader program runs in two phases. In the first phase, the program uses a SQL*Loader script to insert data from the bank statement file into the intermediate table. The SQL*Loader script understands the format of the file and parses the data accordingly. The intermediate table is simply a representation of the bank statement file in the database, where a row corresponds to a record in the file and a column corresponds to a field in the record.

In the second phase, the Bank Statement Loader program uses a set of mapping rules to transfer data from the intermediate table to the Bank Statement Open Interface tables. These rules map the structure of the bank statement file to the open interface tables. These rules may also perform simple pattern searches and data transformations.

The loading of BAI2 and SWIFT940 file formats is mostly automated. Since Oracle Cash Management provides the SQL*Loader scripts and mapping templates for these two formats, you can run the Bank Statement Loader program with minimal setup. You simply need to modify the default mapping information to match the variations your bank has made to the standards. In addition, the Bank Statement Loader program can accept other formats, provided you have created the corresponding SQL*Loader script and mapping information.

You can execute the Bank Statement Loader program in one of the following modes:

If you choose the option of Load and Import, the Bank Statement Import program starts after the Bank Statement Loader program completes successfully. If you choose the option of Load, Import, and AutoReconciliation, both the Bank Statement Import program and the AutoReconciliation program launch after successful completion of the Bank Statement Loader program. In either case, if the Bank Statement Loader program completes with errors or warnings, the concurrent request terminates. You can review the exceptions encountered by the Bank Statement Loader program using the Bank Statement Loader Execution Report.

To Run the Bank Statement Loader Program

Prerequisites

To run the Bank Statement Loader Program:

  1. Navigate to the Requests window.

  2. Submit a concurrent request for the Bank Statement Loader program.

  3. Select one of the following options:

    • Load

    • Load and Import

    • Load, Import, and AutoReconciliation

  4. Enter the name of the mapping template.

  5. Enter the name of the bank statement file.

  6. Enter the directory path in which the bank statement file is located.

  7. Enter the bank branch name and the bank account number you want to load from the bank statement file. If nothing is entered, the entire file is loaded, else only statements belonging to the bank branch or the bank account are loaded.

  8. If you selected either the option of Load and Import or Load, Import, and AutoReconciliation, enter the GL Date.

  9. If you selected the option of Load, Import and AutoReconciliation, enter the Receivable Activity and NSF Handling. Also enter the Payment Method if you specified a bank account number.

  10. Submit the program.

  11. Check the SQL*Loader log file and the Bank Statement Loader Execution Report to see any exceptions generated during the loading process.

    See: Bank Statement Loader Execution Report

  12. If you selected either the option of Load and Import or Load, Import and AutoReconciliation, review the results of the import process.

    See: Reviewing Bank Statement Interface Errors

  13. If you selected the option of Load, Import and AutoReconciliation, review the results of the AutoReconciliation program and correct the reconciliation errors.

    See: Reviewing Reconciliation Errors

  14. If you did not run the AutoReconciliation program, or you are unable to correct the reconciliation errors, you can manually reconcile the bank statements.

  15. After you have reconciled your bank statements, you can run the following Cash Management reports to ensure they contain accurate information:

    • Cash in Transit Report

    • GL Reconciliation Report

    • Bank Statement Summary Report

    • Transactions Available for Reconciliation Report

Bank Statement Loader Program Parameters

Process Option: You can choose one of the three following options:

  1. Load

  2. Load and Import

  3. Load, Import, and AutoReconciliation

If you choose either Load and Import or Load, Import, and AutoReconciliation, a separate process will be submitted for each bank statement after the loader program completes successfully.

Mapping Name: Name of the mapping rules that should be used

Data File Name: Name of the bank statement file to load from

Directory Path: Location of the bank statement file. The length defaults to 50 characters, but can be set up to 240 characters. If none is entered, the Bank Statement Loader program looks for the bank statement file in the directory of $CE_TOP/bin.

Bank Branch Name: If the bank statement file contains information for multiple bank branches, you can ask the Bank Statement Loader program to load only bank statements from a specific bank branch.

Bank Account Number: If the bank statement file contains information for multiple bank accounts, you can ask the Bank Statement Loader program to load only bank statements from a specific bank account.

GL Date: If you selected either the option of Load and Import or Load, Import, and AutoReconciliation, enter the GL Date. This is the default date for all General Ledger journal entries created by AutoReconciliation. The date must be in an open or future-enterable Payables or Receivables period.

Receivable Activity: If you selected the option of Load, Import and AutoReconciliation, enter the Receivable Activity to create Receivables miscellaneous receipts for differences that AutoReconciliation encounters when matching remittance batches or NSF transactions within tolerances.

Payment Method: If you selected the option of Load, Import and AutoReconciliation and have specified a Bank Account Number, enter the Payment Method to create Receivables miscellaneous receipts for differences that AutoReconciliation encounters when matching remittance batches or NSF transactions within tolerances.

NSF Handling: If you selected the option of Load, Import and AutoReconciliation, enter the NSF Handling for handling any NSF or rejected receipts on the bank statement. The valid actions are:

Using a Custom Loader Program

First, create your custom loader program. Then:

  1. Run the custom loader program against the bank statement file to populate the Bank Statement Open Interface tables.

  2. Verify that the bank statement information was loaded into the open interface tables correctly.

    You can use the Bank Statement Interface window to ensure that bank statement header information is correctly inserted into the CE_STATEMENT_HEADERS_INT table.

    You can use the Bank Statement Lines Interface window to ensure that bank statement transaction lines are correctly inserted into the CE_STATEMENT_LINES_INTERFACE table.

    The Bank Statement Interface and Bank Statement Lines Interface windows show you the statement header and lines information in the open interface tables. You can correct the information using these windows, or you can correct the original bank statement file and reload it using your custom loader program.

Automatic Bank Transmission Feature

You can set up Cash Management to automatically retrieve previous day and intra-day bank statements and payment exceptions from your bank. The automatic bank transmission feature includes the following components:

Retrieving Bank Statements and Payment Exceptions

Prerequisites

Refer to the Oracle Payables User Guide for more information.

To retrieve bank statements

  1. Navigate to the Requests window.

  2. Submit a concurrent request for the Retrieve Bank Statement program.

  3. Select one of the following options:

    • Download

    • Download and Load

    • Download, Load, and Import

    • Download, Load, Import, and AutoReconciliation

      Note: You do not have the option to perform AutoReconciliation with intra-day bank statements.

  4. Enter the Transmission Code you defined in the Bank Transmission Details window. This code determines the set of transmission detail parameters the program will use.

  5. Enter the remaining submission parameters.

Note: If you choose an option to download and load bank statements, a mapping template name is required. If you select the option of Download, Load, Import, and AutoReconciliation, you must also enter values for the GL Date Source, Receivable Activity and NFS Handling. The GL Date Source determines the date for all General Ledger journal entries created by AutoReconciliation. The date must be in an open or future-enterable Payables or Receivables period. You can choose System Date, Statement Date, or User Entered Date as the GL Date Source. If you choose User Entered Date, you must enter the GL Date.

To retrieve payment exceptions

  1. Navigate to the Requests window.

  2. Submit a concurrent request for the Retrieve Payment Exceptions program.

  3. Enter the Transmission Code you entered in the Bank Transmission Details window. This code determines the set of transmission detail parameters the program will use.

Intra-Day Bank Statement Loader Program

You can use the Intra-Day Bank Statement Loader program to load bank statements updated with the latest transactions that occur on the day the statements are created. If the intra-day bank statements include the time they are created, the Intra-Day Bank Statement Loader program can also load the timestamp information.

You can run the Intra-Day Bank Statement Loader in one of two modes:

If you choose the Load option, the program loads the bank statement information into the Bank Statement Open Interface tables, and you can make corrections to the statements prior to importing them into Oracle Cash Management. Alternatively, you can choose the Load and Import option to automatically start the Intra-Day Bank Statement Import program after the Intra-Day Bank Statement Loader program completes successfully. If the Intra-Day Bank Statement Loader program encounters any errors or warnings, the concurrent request stops. You can review the exceptions using the Bank Statement Loader Execution Report.

Submitting the Intra-Day Bank Statement Loader Program

Prerequisites

To submit the Intra-Day Bank Statement Loader Program

  1. Navigate to the Requests window.

  2. Submit the Intra-Day Bank Statement Loader program.

  3. Select one of the following options:

    • Load

    • Load and Import

  4. Enter the name of the mapping template.

  5. Enter the name of the bank statement file.

  6. Enter the directory path in which the bank statement file is located.

  7. If you want to limit loading records from a particular bank branch and branch account, then specify the bank branch name and the bank account number, else the program will import all records in the bank statement file.

  8. Submit the program.

  9. Check the SQL*Loader log file and the Bank Statement Loader Execution Report to see any exceptions generated during the loading process.

Receiving XML Bank Statements

You can set up XML Gateway to receive XML bank statements automatically and securely from your bank. The bank delivers the prescheduled bank statements in the IFX XML format to the XML Gateway. The XML Gateway validates the message using the IFX specifications and predefined Cash Management mapping rules. Upon successful validation, the XML Gateway populates the Bank Statement Open Interface tables and launches the Bank Statement Import program to transfer the bank statement information to Cash Management. If the validation fails, the XML Gateway sends e-mail notifications to the system administrator and the bank contact. In addition, the system notifies the designated cash manager of the status of the import process.

Setting Up Your System to Receive XML Bank Statements

Prerequisites

Setting up a dummy user to receive XML statements

Your bank will send the XML statements to a dummy user, so that you do not expose the internal user name to the bank.

  1. Log on as System Administrator.

  2. Navigate to the Users window.

  3. Enter cexml for the User Name.

  4. Enter Dummy User for CE XML Data Transmission for the Description.

  5. Enter the password cexml. The password is case sensitive.

Setting up a user in Cash Management to receive XML statements

  1. Log on as System Administrator.

  2. Navigate to the Users window

  3. Add Cash Management, Cash Management Notifications, XML Gateway, System Administrator, Workflow Administrator, Payables Manager, and Receivables Manager Responsibilities.

  4. In the E-mail field, enter a valid e-mail address for the person who will receive error notifications.

  5. Save your work.

Running the Notification Mailer

Check the status of the Notification Mailer. If it isn't running, submit the Notification Mailer program so users can receive e-mail notifications. To run this program:

  1. Log on as System Administrator.

  2. Navigate to the Requests window.

  3. Submit the Notification Mailer.

XML Gateway Trading Partner Setup

Refer to the Oracle XML Gateway User's Guide for setup instructions.

If your current version is Financials Family Pack D, the Bank field will be replaced by the Company Admin E-mail field.

The following table shows the values to enter for each parameter:

In this field Enter
Trading Partner Type Bank
Trading Partner Site The bank site
Bank Bank name
Transaction Type BANKSVCRS
Transaction Sub-Type DEPACCTSTMTINQRS
Standard Code IFX
External Transaction Type BANKSVCRS
External Transaction Sub-Type DEPACCTSTMTINQRS
Direction IN
Map CE_BSI_IFX12_IN
XML Header The code that is decided by your party and your bank for your Party Site ID

The following table shows the values that default for each parameter:

In this field These values will default
Transaction Type BANKSVCRS
Transaction Sub-Type DEPACCTSTMTINQRS
Standard Code IFX
External Transaction Type BANKSVCRS
External Transaction Sub-Type DEPACCTSTMTINQRS
Direction IN

Bank Setup

Note: If your current version is Financials Family Pack D, please disregard this step.

Processing XML Bank Statements

  1. The bank creates the XML message detailing the previous day bank account activities.

  2. The bank sends the XML message to your enterprise.

  3. The Oracle Transport Agent (OTA) receives the message, and checks the message for transmission errors. If no errors are found, Oracle Transport Agent sends an HTTP success response to the bank server. If errors are found, then Oracle Transport Agent sends an HTTP failure response to the bank.

  4. The XML Gateway validates the received file for the XML message structure, the IFX specifications, and predefined Cash Management mapping rules.

  5. If there are data errors in the message, XML Gateway sends the bank an error notification.

  6. If XML Gateway identifies system/process related errors, it sends the System Administrator an error notification. The System Administrator can check the error message notification for the error detail and correct the problem. Once the problem has been corrected, the System Administrator can then reply to the Oracle Workflow error by:

    • choosing the Reprocess option. This will begin the XML processing again, starting with step 4 .

    • choosing the Abort option. This will stop the system from attempting to process the statement.

  7. XML Gateway populates the Bank Statement Open Interface tables with the XML message according to the mapping rules.

  8. If Cash Management identifies data errors, the system sends a data error notification to the Cash Manager. The Cash Manager can correct the data errors in the Bank Statement Open Interface window. Possible data errors include:

    • no bank ID found

    • multiple bank accounts found

    • duplicate bank statement exists

  9. The Bank Statement Import program launches automatically once the bank statement is successfully processed.

  10. The Cash Manager receives either a success or a failure notification indicating whether the bank statement import process completed successfully or not.

  11. The Cash Manager can check and correct any import errors before trying to import the bank statement again.

  12. If the Cash Manager receives a success notification regarding the import process, then the bank statement was correctly imported into the Cash Management tables, and it is now ready for reconciliation.

Related Topics

Bank Statement Open Interface

Importing Bank Statements

About Bank Statement Validation

Reviewing Bank Statement Interface Errors

Correcting Bank Statement Interface Errors

Reconciling Bank Statements Automatically

Archiving and Purging Imported Bank Statements

Importing Bank Statements

If you load electronic bank statements into the Bank Statement Open Interface tables, you must import the bank statement information into the Cash Management Bank Statement tables before you can reconcile the statement detail lines.

Oracle Cash Management's Bank Statement Import program validates the information in the Bank Statement Open Interface tables and imports the validated bank statement information. If you import bank statements from bank accounts that are used by Oracle Treasury, the Bank Statement Import program also updates the bank account balances seen in Oracle Treasury. Additionally, the Bank Statement Import program can launch the AutoReconciliation program to perform an automatic reconciliation after the import process completes.

If you load intra-day bank statements, you must use the Intra-Day Bank Statement Import program to validate and import the information into the Cash Management Intra-Day Bank Statement tables.

After you have successfully imported the intra-day bank statements, you can include the information in your cash positioning in Oracle Treasury. Since intra-day information is not used for reconciliation, you cannot view the imported intra-day bank statements in Cash Management windows or reports. However, you can archive or purge the bank statements. When you import multiple intra-day bank statements within the same day, the Intra-Day Bank Statement Import program deletes the existing bank statement before importing the new bank statement.

You can use one of several ways to import data from the Bank Statement Open Interface tables into the Bank Statement tables.

To load and import a bank statement in the same request:

  1. Navigate to the Submit Request window.

  2. Select the Bank Statement Loader program.

  3. Choose the option of Load and Import.

  4. Specify the other parameters as appropriate.

  5. Submit your request.

    See: To Run the Bank Statement Loader Program.

To load, import, and reconcile a bank statement in the same request:

  1. Navigate to the Submit Request window.

  2. Select the Bank Statement Loader program.

  3. Choose the option of Load, Import and AutoReconciliation.

  4. Specify the other parameters as appropriate.

  5. Submit your request.

    See: To Run the Bank Statement Loader Program.

To import a bank statement only, without AutoReconciliation:

  1. Navigate to the Submit Request window. Alternatively, you may submit the request directly from the Bank Statement Interface window by selecting AutoReconciliation from the Oracle Applications Tools menu.

  2. Select the Bank Statement Import program.

  3. Enter the Bank Account Number for the statement you want to import.

  4. Specify the range of statement numbers in the Statement Number From and Statement Number To fields.

  5. Specify dates for the Statement Date From, Statement Date To, and GL Date fields.

    The GL Date is the default date for all General Ledger journal entries generated by AutoReconciliation. The date must be in an open or future-enterable Payables or Receivables period.

  6. Submit your request. The Bank Statement Import program validates the data in the Bank Statement Interface tables, imports the validated information, and prints an AutoReconciliation Execution Report.

  7. Review and correct any import validation errors, then repeat the steps above.

To import and reconcile a bank statement in the same request:

  1. Navigate to the Submit Request window.

  2. Select the Bank Statement Import and AutoReconciliation program.

    Note: Alternatively, you may submit the request directly from the Reconcile Bank Statements window or Bank Statement Interface window by selecting AutoReconciliation from the Oracle Applications Tools menu.

  3. Enter the Bank Account Number for the statement you want to import.

  4. Specify the range of statement numbers in the Statement Number From and Statement Number To fields.

  5. Specify dates for the Statement Date From, Statement Date To, and GL Date fields.

  6. Select a Receivable Activity and a Payment Method to create Miscellaneous Receipts for differences that AutoReconciliation encounters when matching remittance batches or NSF transactions within tolerances.

  7. Specify how to handle any NSF transactions that are in your bank statement. Choose:

    • No Action: to handle manually

    • Reverse: to reverse receipt and reopen the invoices

    • Debit Memo Reverse: to create a debit memo that replaces the item closed by the original receipt

  8. Submit your request. The AutoReconciliation program validates the data in the Bank Statement Open Interface tables, imports the validated information, reconciles the bank statement, and prints an AutoReconciliation Execution Report.

    Note: You can get an AutoReconciliation Execution Report at any time by submitting a request using the Submit Request window.

  9. Review and correct any import validation errors, then repeat steps above. Once any import validation errors have been corrected, the AutoReconciliation program submits a request to reconcile the bank statement.

To load and import an intra-day bank statement in the same request:

  1. Navigate to the Submit Request window.

  2. Select the Intra-Day Bank Statement Loader program

  3. Choose the option of Load and Import.

  4. Specify the other parameters as appropriate.

  5. Submit your request.

See: To Submit the Intra-Day Bank Statement Loader Program

To import an intra-day bank statement only

  1. Navigate to the Submit Request window. Alternatively, you may submit the request directly from the Bank Statement Interface window by selecting AutoReconciliation from the Tools menu.

  2. Select the Intra-Day Bank Statement Import program.

  3. Enter the Bank Account Number for the statement you want to import.

  4. Specify the range of statement numbers in the Statement Number From and Statement Number To fields. The list of values displays only intra-day bank statements that are within the range.

  5. Specify dates for the Statement Date From and Statement Date To fields.

  6. Submit your request. The Intra-Day Bank Statement Import program validates the data in the Bank Statement Interface tables, imports the validated information, and prints an AutoReconciliation Execution Report.

  7. Review and correct any import validation errors, then repeat the steps above.

Related Topics

About Bank Statement Validation

Reviewing Bank Statement Interface Errors

Correcting Bank Statement Interface Errors

Loading Bank Statement Open Interface

Reconciling Bank Statements Automatically

About Bank Statement Open Interface

Multi-Currency Handling

Archiving and Purging Imported Bank Statements

About Bank Statement Validation

The Bank Statement Import program validates the information you load into the Bank Statement Open Interface tables. If the data passes validation, the program imports it into the Cash Management Bank Statement tables. If any records in a statement fail validation, the program does not import the statement and assigns the statement an Error status.

You can use the AutoReconciliation Execution Report or the View Reconciliation Errors window to review any import validation errors. You can use the Bank Statement Interface window to correct any import validation errors.

Bank Statement Header Validation

Import validation requires that the imported bank statement header information pass the following checks:

Control Total Validation

Many bank statements provide control totals in the statement header for error-checking purposes. The Bank Statement Header Interface table has seven control total columns. The Bank Statement Import program verifies the values in these columns as follows. If the program encounters exceptions with the control totals, it generates warning messages but proceeds to import the statement.

The control totals are as follows:

CONTROL_BEGIN_BALANCE: The statement opening balance. No validation is done for this entry.

CONTROL_END_BALANCE: The statement closing balance. The closing balance should satisfy the following equation:

CONTROL_END_BALANCE = CONTROL_BEGIN_BALANCE + CONTROL_TOTAL_CR - CONTROL_TOTAL_DR.

CONTROL_TOTAL_DR: Payment control total in the statement header should match the total payment amount on the statement lines. Payments are statement lines that have a bank transaction code with a type of Payment, Misc. Payment, NSF or Rejected.

CONTROL_TOTAL_CR: Receipt control total in the statement header should match the total receipt amount on the statement lines. Receipts are statement lines that have a bank transaction code with a type of Receipt, Misc Receipt, or Stop.

CONTROL_DR_LINE_COUNT: Number of payment lines in the statement should match the payment line control total in the statement header. Payments are statement lines that have a bank transaction code with a type of Payment, Misc. Payment, NSF or Rejected.

CONTROL_CR_LINE_COUNT: Number of receipt lines in the statement should match the receipt line control total in the statement header. Credits are statement lines that have a bank transaction code with a type of Receipt, Misc. Receipt, or Stop.

CONTROL_LINE_COUNT: Number of lines in the statement should match the line control total in the statement header.

Statement Line Validation

Values populated in the CE_STATEMENT_LINES_INTERFACE table must not contain any trailing spaces.

Currency Code: If you enter a currency code on the statement line, it must be a currency that is defined in the system. The currency code does not affect the matching or calculations.

Bank Transactions Codes: Bank Transaction Codes, if entered, must be defined in Cash Management.

Exchange Rate Type: The Exchange Rate Type must be a valid type as defined in Oracle General Ledger.

Amount: An amount must be entered for the statement line.

Multi-Currency Validation

AutoReconciliation checks each bank statement line for a valid currency and exchange rate type. During reconciliation, the program checks the following once a matching transaction is found:

Related Topics

System Parameters

Bank Statement Open Interface

Loading Bank Statement Open Interface

Importing Bank Statements

Reviewing Bank Statement Interface Errors

Correcting Bank Statement Interface Errors

Reconciling Bank Statements Automatically

Multi-Currency Handling

Archiving and Purging Imported Bank Statements

Country Specific Bank Account Validations

Reviewing Bank Statement Interface Errors

You can use the Bank Statement Interface window to review import validation errors that resulted when you imported bank statement information from the Bank Statement Open Interface tables. In addition, you can review the AutoReconciliation Execution Report that is printed automatically by the Bank Statement Import program.

You can review all import and validation errors for a statement, or you can review only those errors that occurred for a specific statement line.

Note: Only import validation errors are available from the Bank Statement Interface window. To review reconciliation-related errors, use the Reconcile Bank Statements window.

If you encounter import errors, you can correct the data one of three ways:

Prerequisite

To review bank statement interface errors online:

  1. Navigate to the Bank Statement Interface window.

  2. Query the statement whose interface errors you want to review.

  3. Choose Errors. You see the Bank Statement Interface Errors window containing the list of interface errors.

  4. Choose Done to exit.

    Tip: If you prefer to review the errors in a printed report, the AutoReconciliation Execution Report is run automatically when you run either of the programs that include Bank Statement Import.

Related Topics

Statement Interface Window Reference

Statement Interface Window Reference

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

Bank Name: The name of the bank.

Bank Branch: The name of the bank branch.

Currency: The currency of the bank account.

Intra-Day: Indicates whether or not the statement is an intra-day bank statement.

Statement Number: The number of the bank statement. If you do not use sequential numbering, the default statement number is the statement date.

Statement Date: The closing date of the bank statement. The date also includes the timestamp information provided by your bank. On intra-day bank statements, the timestamp indicates how recent the information is.

Status: The status of the bank statement:

Check Digits: Field for bank account validation.

Creation Date: The creation date of the bank statement. This is the date that the bank statement was created in Oracle Cash Management, not the date that your bank created the bank statement. If the intra-day bank statement does not contain timestamp information, you can use this date to determine how recent the information is.

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

Receipts Amount: The total amount of receipts on the statement.

Receipts Lines: The total number of receipt lines on the statement.

Payments Amount: The total amount of payments on the statement.

Payments Lines: The total number of payment lines on the statement.

Closing: The closing balance on the bank statement.

Lines: The total number of lines on the statement.

Available: The available balance on the statement.

Value Dated: The balance on the statement used to calculate interest.

One Day Float: The one day float balance on the statement. When you import bank statements from a shared bank account, this balance is used to update the One Day Float Balance in Oracle Treasury.

Two Day Float Balance: The two day float balance on the statement. When you import bank statements from a shared bank account, this balance is used to update the Two Day Float Balance in Oracle Treasury.

Reviewing Interface Errors Online for a Specific Statement Line

To review interface errors online for a specific statement line:

  1. Navigate to the Bank Statement Interface window.

  2. Query the statement whose interface errors you want to review.

  3. Choose Lines to see the Bank Statement Line Interface window.

  4. Select the line whose errors you want to review.

  5. Choose Errors.

    You see the Bank Statement Interface Line Errors window containing the list of interface errors for the selected line.

  6. Choose Done to exit.

Bank Statement Line Interface Window Reference

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

Date: The closing date of the bank statement.

Line Number: The line number provided by the bank. If the bank does not provide line numbers, you can enter line numbers in a sequence.

Code: The code that the bank uses to identify a transaction.

Number: The transaction number, such as the receipt number, check number, or payment batch name. Automatic Reconciliation uses this number to identify transactions when matching the bank statement line to a transaction or batch.

Date: The date that the transaction cleared the bank. The default is the statement date.

Amounts Tabbed Region

Amount: The amount of the transaction, in the bank account currency. This amount must be within tolerance for AutoReconciliation to succeed (even if the netted value of the Amount and Charges column equals the amount of the system transaction).

Charges: The amount of any bank charges associated with the transaction. This field is not required.

Original Amount: The statement line amount, in its original currency. This amount is used in foreign exchange transactions when the currency of the transaction is different from the currency of the bank account.

Exchange Tabbed Region

Curr: The currency of the transaction.

Date: The date associated with the exchange rate that was used.

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

Rate: The exchange rate specified by the bank for this transaction.

Reference Tabbed Region

Agent: The customer or supplier associated with the transaction.

Agent Bank Account: The bank account number of the customer or supplier associated with the transaction.

Invoice: The invoice number associated with the transaction.

Description Tabbed Region

Effective Date: The value date for the transaction.

Description: Any other information provided by the bank.

Related Topics

Importing Bank Statements

Reconciling Bank Statements Automatically

Correcting Bank Statement Interface Errors

About Bank Statement Open Interface

Archiving and Purging Imported Bank Statements

Correcting Bank Statement Interface Errors

If any record in a statement fails validation, the Bank Statement Import program does not import the statement, and assigns it an Error status. You can review import validation errors from the Bank Statement Interface window, or by reviewing the AutoReconciliation Execution Report.

Interface errors may occur for a variety of reasons. The following is a list of the most common causes of bank statement interface errors:

Instead of reloading the bank statement file, you may want to directly modify the information in the Bank Statement Interface tables by using the Bank Statement Interface and the Bank Statement Interface Lines window. You can update all bank statement header and line information as well as any optional descriptive flexfield content.

To correct the bank statement interface header information:

  1. Navigate to the Bank Statement Interface window.

  2. Query the Statement you want to correct.

  3. Edit the bank statement header information as necessary, including the Control Balances.

  4. Save your work.

  5. Run the Bank Statement Import program to import the corrected bank statement information from the open interface tables. If this is an intra-day bank statement, run the Intra-Day Bank Statement Import program.

    Note: You can submit the Bank Statement Import program or the Intra-Day Bank Statement Import program directly from the Bank Statement Interface window. Choose AutoReconciliation from the Tools menu.

To correct the bank statement interface lines:

  1. Navigate to the Bank Statement Interface window.

  2. Query the Statement you want to correct.

  3. Choose the Lines button.

  4. Edit the bank statement transaction information. You can edit the Line Number, transaction Code, transaction Number, and Date.

  5. In the Amounts tabbed region, edit the transaction Amount, bank Charges, and Original Amount.

  6. In the Exchange tabbed region, edit exchange rate information, including Currency code, Exchange Date, Exchange rate Type, and Exchange Rate.

  7. In the Reference tabbed region, edit the Agent (customer or supplier), Invoice number, and Agent Bank Account associated with each line.

  8. In the Description tabbed region, edit the Value Date and Description for each line.

  9. Save your work.

  10. Run the Bank Statement Import program to import the corrected bank statement information from the open interface tables.

Related Topics

Loading Bank Statement Open Interface

Importing Bank Statements

About Bank Statement Validation

Reviewing Bank Statement Interface Errors

Reconciling Bank Statements Automatically

Archiving and Purging Imported Bank Statements

Entering Bank Statements Manually

In addition to loading bank statement information automatically with the Bank Statement Open Interface, you can enter bank statement information manually. You can enable users to update information on a statement that was previously entered manually or loaded automatically, even if the statement is already reconciled.

When you enter a bank statement manually, you enter the bank statement header and the transaction lines. You can reconcile transaction lines as you enter them, or you can reconcile the bank statement (manually or automatically) after you enter all the transaction lines and save your work.

Note: Cash Management assumes that you enter statements in chronological order, and uses this order to calculate cumulative bank account balances.

Prerequisites

To enter a bank statement manually:

  1. Navigate to the Find Bank Statements window. Choose New.

  2. Choose the New button to display the Bank Statement window.

  3. Enter the following bank header information: Account Number, Account Name, Bank Name, Bank Branch, and Currency.

  4. Enter the Statement Date.

  5. If you use manual document sequence numbers, enter the Statement Document Number.

    Note: If you use automatic document sequence numbers, Cash Management creates the Document Number for the statement automatically. You cannot edit an automatically assigned Document Number.

  6. Enter the Statement Number.

  7. Enter the GL Date - the default is the Statement Date. The GL Date must be in an open or a future-enterable period in Receivables or Payables.

  8. Optionally, enter Bank Statement Control Totals:

    Enter the opening and closing balances as needed. Enter control totals for Receipts and Payments, as well as the number of statement lines for each.

    Note: You can compare the Control Total amounts to those displayed by Cash Management in the Line Totals tabbed region of the Statements window, to ensure the completeness and correctness of your entered bank statement.

  9. Choose Lines to begin entering your statement lines. Optionally, you can save the header information now and enter the statement lines later.

  10. Save your work.

Bank Statement Window Reference

Account Number: The bank account number. Note that you can update this value but if you have already reconciled bank statement lines, it will not update them.

Account Name: The bank account name.

Bank Name: The name of the bank.

Bank Branch: The name of the bank branch.

Date: The closing date of the statement.

Currency: The currency of the bank account.

Document Number: The document number that appears on the bank statement. If you use automatic document sequence numbers, Cash Management creates the document number for the statement automatically. You cannot edit a document number that is assigned automatically.

Statement Number: The statement number. The default statement number is the statement date.

GL Date: The date that the transaction posts to the General Ledger. The default is the statement date. The GL Date must be in an open or future-enterable period in Oracle Receivables or Oracle Payables.

Check Digits: Enter Check Digits for bank account validation.

Control Totals and Line Totals: The Control Totals and Line Totals tabbed regions of the window contain the same fields. You can compare these fields to ensure that the bank statement that you entered is complete and correct.

Opening Balance: The beginning balance for the statement, as provided by the bank. The default is the closing balance of the previous statement.

Receipts Amount: The total amount of receipts on the statement.

Receipts Lines: The total number of receipt lines on the statement.

Payments Amount: The total amount of payments on the statement.

Payments Lines: The total number of payment lines on the statement.

Closing Balance: The closing balance on the statement.

Available Balance: The available balance on the statement.

Value Dated Balance: The value dated balance on the statement. This balance is used to calculate your interest.

One Day Float: The one day float balance on the statement.

Two Day Float: The two day float balance on the statement.

Complete: Check this field to indicate that the bank statement is complete and to prevent any changes to it. You may want to freeze a bank statement after completing the reconciliation process. However, you can mark or unmark the statement as complete at any time.

Unreconciled Amount: The total amount on the statement that is unreconciled. This amount is the net of unreconciled receipts and unreconciled payments.

Unreconciled Lines: The number of unreconciled lines on the statement.

Related Topics

Bank Statements

Sequential Document Numbering

Entering Bank Statement Lines

Reconciling Bank Statements Automatically

Reconciling Bank Statements Manually

Creating Reconciled Bank Statement Lines

Country Specific Bank Account Validations

Entering Bank Statement Lines

To enter bank statement lines:

  1. Navigate to the Find Bank Statements window.

  2. Choose Find to see a list of all statements you have already entered.

  3. Select the statement whose header information you've already entered, then choose Review to see the Bank Statement window.

    Note: If you have not already entered the bank statement header information, see Entering Bank Statements Manually.

  4. Choose Lines to enter the bank statement lines.

  5. Enter the Line Number.

  6. Enter the transaction Type for the line, or select a type from the poplist.

  7. Optionally, if you plan to automatically reconcile the bank statement once you finish entering it, for each transaction line enter the Bank Transaction Code and:

    • Transaction Number, or

    • Invoice Number (see step 11 below) and Agent Bank Account

    Note: For Payroll EFT payments, the transaction number should be entered in the order specified by the Payroll matching order for the transaction code and separated by "/".

  8. In the Amounts tabbed region, enter transaction amount details.

    • Enter the Transaction Date. The default is the statement date.

    • Enter the Value Date.

    • Enter the transaction Amount. The transaction type determines whether the amount is a payment or receipt.

  9. In the Exchange tabbed region, enter exchange information for foreign currency transactions.

    • Enter the Original Currency Code and Exchange Date.

    • Enter an exchange rate Type or select one from the available list of values.

    • If you select exchange rate type of User, enter the exchange Rate. If you use a predefined rate type, Cash Management picks up the exchange rate automatically if you do not enter one.

  10. In the Reference tabbed region, enter the Agent (customer or supplier), Invoice number, and Agent Bank Account associated with the transaction.

  11. In the Description tabbed region, enter a description of the transaction and any Bank Charges related to the transaction.

  12. Enter each additional transaction line.

  13. Save your work.

Bank Statement Interface Lines Window Reference

Account: The number of the bank account associated with the transaction.

Date: The closing date of the bank statement.

Line: The line number provided by the bank. If the bank does not provide line numbers, you can enter line numbers in a sequence.

Type: The transaction type:

Code: The code that the bank uses to identify a transaction.

Number: The transaction number.

Amounts Tabbed Region

Transaction Date: The date the bank cleared the transaction. The default is the statement date.

Value Date (formerly Effective Date): The date when cash activity (payments or receipts) is recognized in a bank account. This date is important for interest calculations on a bank account. The statement line Value Date can be provided by the bank or entered manually. It is used to populate the Actual Value Date in Oracle Payables and Oracle Receivables.

Amount: The amount of the transaction, in the bank account currency. This amount must be within tolerance for autoreconciliation to occur (even if the netted value of the Amount and Charges column equals the subledger transaction amount).

Amount Reconciled: The amount of the transaction that was reconciled.

Charges: The amount of any bank charges associated with the transaction. This field is not required. Cash Management derives the amount reconciled by adding the Charges and Amount columns.

Original Amount: The statement line amount, in its original currency. This amount is used in foreign exchange transactions when the currency of the transaction is different from the currency of the bank account.

Exchange Tabbed Region

Currency: The currency of the transaction.

Date: The date associated with the exchange rate that was used.

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

Rate: The exchange rate specified by the bank for this transaction.

Original Amount: The statement line amount, in its original currency. This amount is used in foreign exchange transactions when the currency of the transaction is different from the currency of the bank account.

Reference Region

Agent: The name of the customer or supplier associated with the transaction.

Agent Bank Account: The bank account of the customer or supplier associated with this transaction.

Invoice: The invoice number associated with the transaction.

Description Tabbed Region

Status: The status of the transaction, reconciled or unreconciled.

Description: The customer-related text provided by the bank.

Related Topics

Bank Statements

Entering Bank Statements Manually

Reconciling Bank Statements Manually

Creating Reconciled Bank Statement Lines

Reconciling Bank Statements Automatically

Updating Bank Statements

You can update any bank statement, whether imported or entered manually in Cash Management.

Prerequisites

To update a bank statement:

  1. Navigate to the Find Bank Statements window.

  2. Enter the query criteria for the statement you want to update, or leave the criteria blank to query all existing statements.

  3. Choose Find to see the Reconcile Bank Statements folder window containing the statements that meet your query criteria.

    Note: You can modify the Reconcile Bank Statements folder window to customize your query capabilities on statements you have entered. Refer to the Oracle Applications User's Guide for more information on modifying and saving folders.

  4. Select the statement you want to update, and choose Review.

  5. Update bank statement header and line information as necessary. You can change any bank statement information. You can also add information, such as transaction lines.

  6. Save your work.

Related Topics

Entering Bank Statements Manually

Reconciling Bank Statements Automatically

Reconciling Bank Statements Manually

Changing the Reconciliation Status

Marking a Bank Statement as Complete

Creating Cashflows from Bank Files

You may also create cash management cashflows to reflect banking activity and transactions not previously entered in your system.

For more information, see: Cashflows.