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:
Using the Bank Statement Loader program
Using a custom loader program
Manually entering the information
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 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:
Load
Load and Import
Load, Import, and AutoReconciliation
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.
Create a SQL*Loader script for the format of your bank statement file, if it is neither BAI2 nor SWIFT940.
Define a mapping template for the format of your bank statement file.
Place your bank statement file in a directory.
Navigate to the Requests window.
Submit a concurrent request for the Bank Statement Loader program.
Select one of the following options:
Load
Load and Import
Load, Import, and AutoReconciliation
Enter the name of the mapping template.
Enter the name of the bank statement file.
Enter the directory path in which the bank statement file is located.
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.
If you selected either the option of Load and Import or Load, Import, and AutoReconciliation, enter the GL Date.
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.
Submit the program.
Check the SQL*Loader log file and the Bank Statement Loader Execution Report to see any exceptions generated during the loading process.
If you selected either the option of Load and Import or Load, Import and AutoReconciliation, review the results of the import process.
If you selected the option of Load, Import and AutoReconciliation, review the results of the AutoReconciliation program and correct the reconciliation errors.
If you did not run the AutoReconciliation program, or you are unable to correct the reconciliation errors, you can manually reconcile the bank statements.
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
Process Option: You can choose one of the three following options:
Load
Load and Import
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:
No Action to handle manually.
Reverse to reverse receipt and reopen the invoices.
Debit Memo Reversal to create a debit memo that replaces the item closed by the original receipt.
First, create your custom loader program. Then:
Run the custom loader program against the bank statement file to populate the Bank Statement Open Interface tables.
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.
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:
Retrieve Bank Statement Program: The Retrieve Bank Statement program retrieves bank statement files from your bank and transmits them to your local directory. The program then sends a transmission status e-mail notification to the designated user defined in the Bank Transmission Details window. When you run this program, you have the option to download, load, import, and reconcile bank statements in the same concurrent request. In addition, you can define a submission schedule to automate the transmission of these files.
Retrieve Intra-Day Bank Statement Program: The Retrieve Intra-Day Bank Statement program retrieves intra-day bank statement files from your bank and transmits them to your local directory. The program then sends a transmission status e-mail notification to the designated user defined in the Bank Transmission Details window. When you run this program, you have the option to download, load, and import intra-day bank statements in the same concurrent request. In addition, you can define a submission schedule to automate the transmission of these files.
Retrieve Payment Exceptions Program: The Retrieve Payment Exceptions program retrieves payment exceptions reports from your bank and transmits them to your local directory. The program sends a transmission status e-mail notification to the designated user defined in the Bank Transmission Details window. You can view the transmission history and report details using the Transmissions window. See: Payment Exceptions Report
Obtain bank transmission specifications from your bank.
Write a customized Java class file.
Define workflow roles and e-mail addresses.
Add the local file directories to the init.ora file.
In the Bank Transmission Details window, assign transmission code and enter transmission information supplied by your bank. In the Statement Transmission Details region, select workflow roles, identify file directories, and specify file naming details.
If you choose to download and load bank statements in the same request, the bank statement mapping rules must be defined. See: Bank Statement Mapping
Refer to the Oracle Payables User Guide for more information.
Navigate to the Requests window.
Submit a concurrent request for the Retrieve Bank Statement program.
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.
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.
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.
Navigate to the Requests window.
Submit a concurrent request for the Retrieve Payment Exceptions program.
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.
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:
Load
Load and Import
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.
Set up Oracle Treasury for integration with Oracle Cash Management. See: Setting Up Oracle Treasury for Oracle Cash Management Integration.
Create a SQL*Loader script for the format of your bank statement, if it is neither BAI2 nor SWIFT940.
Define a mapping template for the format of your bank statement file. See: Bank Statement Mapping.
Save your bank statement file in a directory.
Navigate to the Requests window.
Submit the Intra-Day Bank Statement Loader program.
Select one of the following options:
Load
Load and Import
Enter the name of the mapping template.
Enter the name of the bank statement file.
Enter the directory path in which the bank statement file is located.
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.
Submit the program.
Check the SQL*Loader log file and the Bank Statement Loader Execution Report to see any exceptions generated during the loading process.
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.
Oracle XML Gateway is installed. See: Oracle XML Gateway User's Guide
Oracle Advanced Queuing is installed.
Oracle Workflow Business Event System is installed. See: Oracle Workflow Business Event System User's Guide
Both the bank and your system should use Oracle Transport Agent. If the bank is not using Oracle Transport Agent, the bank needs to integrate its communication software to conform to the Oracle Transport Agent message protocol.
Your bank will send the XML statements to a dummy user, so that you do not expose the internal user name to the bank.
Log on as System Administrator.
Navigate to the Users window.
Enter cexml for the User Name.
Enter Dummy User for CE XML Data Transmission for the Description.
Enter the password cexml. The password is case sensitive.
Log on as System Administrator.
Navigate to the Users window
Add Cash Management, Cash Management Notifications, XML Gateway, System Administrator, Workflow Administrator, Payables Manager, and Receivables Manager Responsibilities.
In the E-mail field, enter a valid e-mail address for the person who will receive error notifications.
Save your work.
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:
Log on as System Administrator.
Navigate to the Requests window.
Submit the Notification Mailer.
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 |
In the Banks setup window, enter the bank administrator's e-mail address in the XML Administrator E-mail field.
Note: If your current version is Financials Family Pack D, please disregard this step.
The bank creates the XML message detailing the previous day bank account activities.
The bank sends the XML message to your enterprise.
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.
The XML Gateway validates the received file for the XML message structure, the IFX specifications, and predefined Cash Management mapping rules.
If there are data errors in the message, XML Gateway sends the bank an error notification.
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.
XML Gateway populates the Bank Statement Open Interface tables with the XML message according to the mapping rules.
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
The Bank Statement Import program launches automatically once the bank statement is successfully processed.
The Cash Manager receives either a success or a failure notification indicating whether the bank statement import process completed successfully or not.
The Cash Manager can check and correct any import errors before trying to import the bank statement again.
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
About Bank Statement Validation
Reviewing Bank Statement Interface Errors
Correcting Bank Statement Interface Errors
Reconciling Bank Statements Automatically
Archiving and Purging Imported 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.
Navigate to the Submit Request window.
Select the Bank Statement Loader program.
Choose the option of Load and Import.
Specify the other parameters as appropriate.
Submit your request.
Navigate to the Submit Request window.
Select the Bank Statement Loader program.
Choose the option of Load, Import and AutoReconciliation.
Specify the other parameters as appropriate.
Submit your request.
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.
Select the Bank Statement Import program.
Enter the Bank Account Number for the statement you want to import.
Specify the range of statement numbers in the Statement Number From and Statement Number To fields.
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.
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.
Review and correct any import validation errors, then repeat the steps above.
Navigate to the Submit Request window.
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.
Enter the Bank Account Number for the statement you want to import.
Specify the range of statement numbers in the Statement Number From and Statement Number To fields.
Specify dates for the Statement Date From, Statement Date To, and GL Date fields.
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.
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
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.
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.
Navigate to the Submit Request window.
Select the Intra-Day Bank Statement Loader program
Choose the option of Load and Import.
Specify the other parameters as appropriate.
Submit your request.
See: To Submit the Intra-Day Bank Statement Loader Program
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.
Select the Intra-Day Bank Statement Import program.
Enter the Bank Account Number for the statement you want to import.
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.
Specify dates for the Statement Date From and Statement Date To fields.
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.
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
Archiving and Purging Imported Bank Statements
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.
Import validation requires that the imported bank statement header information pass the following checks:
The bank account must be defined.
If the bank account number is not unique within the system, you must provide a bank and branch name in the Bank Statement Headers Interface table.
The bank statement currency must be the currency defined for the bank account.
If the bank account is used by Oracle Treasury, you must have access to the company that owns the Treasury bank account.
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.
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.
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:
For transactions:
Bank statement line currency must be the same as the transaction currency. (Your bank statement currency may be different.)
For batches:
All transactions in the batch must use the same currency.
Statement line currency is the same as the batch currency.
For both transactions and batches:
For user exchange rates, each transaction must include the Exchange Rate, and the Rate Type must be User.
For predefined rates (such as Corporate or Spot), each transaction must include the Rate Type and Exchange Rate Date.
Related Topics
Loading Bank Statement Open Interface
Reviewing Bank Statement Interface Errors
Correcting Bank Statement Interface Errors
Reconciling Bank Statements Automatically
Archiving and Purging Imported Bank Statements
Country Specific Bank Account Validations
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:
Use the Bank Statement Interface and Bank Statement Interface Lines window.
Repair the Bank Statement Open Interface tables manually.
Repair, reload, and re-import the original bank statement file.
Import bank statement information from the Bank Statement Open Interface tables. See: Importing Bank Statements
Query the statement whose interface errors you want to review.
Choose Errors. You see the Bank Statement Interface Errors window containing the list of interface errors.
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
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:
Corrected: Errors corrected after input.
Error: Errors detected during input.
New: New statement, not imported.
Transferred: Successfully imported.
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.
Query the statement whose interface errors you want to review.
Choose Lines to see the Bank Statement Line Interface window.
Select the line whose errors you want to review.
Choose Errors.
You see the Bank Statement Interface Line Errors window containing the list of interface errors for the selected line.
Choose Done to exit.
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.
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.
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.
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.
Effective Date: The value date for the transaction.
Description: Any other information provided by the bank.
Related Topics
Reconciling Bank Statements Automatically
Correcting Bank Statement Interface Errors
About Bank Statement Open Interface
Archiving and Purging Imported Bank Statements
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:
Incorrect setup information in Oracle Cash Management, such as bank transaction codes, currencies, or bank account information.
Solution: Update the Cash Management setup information and reload the bank statement file.
Errors in the bank statement file.
Solution: Obtain a corrected transmission file from your bank and reload it.
Exceptions from the Bank Statement Loader program or the Intra-Day Bank Statement Loader program.
Solution: Review the Bank Statement Loader Execution Report and resolve the exceptions by correcting the SQL*Loader script, the mapping template, or the bank statement file. Reload the file.
Problem in your custom loader program.
Solution: Modify the custom loader program and reload the bank statement file. After you have reloaded the file, import the bank statement file again.
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.
Query the Statement you want to correct.
Edit the bank statement header information as necessary, including the Control Balances.
Save your work.
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.
Navigate to the Bank Statement Interface window.
Query the Statement you want to correct.
Choose the Lines button.
Edit the bank statement transaction information. You can edit the Line Number, transaction Code, transaction Number, and Date.
In the Amounts tabbed region, edit the transaction Amount, bank Charges, and Original Amount.
In the Exchange tabbed region, edit exchange rate information, including Currency code, Exchange Date, Exchange rate Type, and Exchange Rate.
In the Reference tabbed region, edit the Agent (customer or supplier), Invoice number, and Agent Bank Account associated with each line.
In the Description tabbed region, edit the Value Date and Description for each line.
Save your work.
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
About Bank Statement Validation
Reviewing Bank Statement Interface Errors
Reconciling Bank Statements Automatically
Archiving and Purging Imported Bank Statements
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.
Define your banks.
If you plan to use the Bank Statement Import program, you need to define bank transaction codes for the bank account.
Set up Receivables activities and payment methods for miscellaneous transactions.
If you want to use document sequences, you or your System Administrator must set up sequential document numbering for Cash Management.
Note: For document sequencing, the effective date is the statement date.
If you have enabled the Allow Adding Lines to Imported Statements feature in the System Parameters window, you can add lines to automatically-loaded statements.
Navigate to the Find Bank Statements window. Choose New.
Choose the New button to display the Bank Statement window.
Enter the following bank header information: Account Number, Account Name, Bank Name, Bank Branch, and Currency.
Enter the Statement Date.
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.
Enter the Statement Number.
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.
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.
Choose Lines to begin entering your statement lines. Optionally, you can save the header information now and enter the statement lines later.
Save your work.
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
Reconciling Bank Statements Automatically
Reconciling Bank Statements Manually
Creating Reconciled Bank Statement Lines
Country Specific Bank Account Validations
Navigate to the Find Bank Statements window.
Choose Find to see a list of all statements you have already entered.
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.
Choose Lines to enter the bank statement lines.
Enter the Line Number.
Enter the transaction Type for the line, or select a type from the poplist.
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 "/".
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.
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.
In the Reference tabbed region, enter the Agent (customer or supplier), Invoice number, and Agent Bank Account associated with the transaction.
In the Description tabbed region, enter a description of the transaction and any Bank Charges related to the transaction.
Enter each additional transaction line.
Save your work.
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.
Payment: Payments, such as generated or recorded checks, wire transfers, and electronic funds transfers.
Receipt: Receipts, such as received checks, direct debits, and bills of exchange.
Miscellaneous Payment: Payments not associated with supplier invoices, such as petty cash transactions posted directly to cash accounts.
Miscellaneous Receipt: Receipts not associated with customer invoices, such as petty cash transactions posted directly to revenue accounts.
Stopped: Stopped payments previously entered, generated, or cleared, such as callback of a check. A stopped transaction type matches only to voided or stopped payments in Oracle Payables and 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 Oracle Receivables.
NSF: Receipts rejected by the bank because the accounts on which they were drawn had non-sufficient funds.
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.
Code: The code that the bank uses to identify a transaction.
Number: The transaction number.
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.
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.
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.
Status: The status of the transaction, reconciled or unreconciled.
Description: The customer-related text provided by the bank.
Related Topics
Entering Bank Statements Manually
Reconciling Bank Statements Manually
Creating Reconciled Bank Statement Lines
Reconciling Bank Statements Automatically
You can update any bank statement, whether imported or entered manually in Cash Management.
Import or enter a bank statement.
Make sure the bank statement is not marked as Complete. See: Marking a Bank Statement as Complete.
Enter the query criteria for the statement you want to update, or leave the criteria blank to query all existing statements.
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.
Select the statement you want to update, and choose Review.
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.
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
You may also create cash management cashflows to reflect banking activity and transactions not previously entered in your system.
For more information, see: Cashflows.