How Bank Statements Import Data is Processed

Use the Create Bank Statements in Spreadsheet template to create and import bank statements for any non-standard format into Oracle Fusion Cash Management.

To access the template, complete the following steps:

1. Navigate to the File-Based Data Import for Oracle Financials Cloud guide.

2. In the Table of Contents, click File-Based Data Imports.

3. Click Import Bank Statements.

4. In the File Links section, click the link to the Excel template.

Follow these guidelines when preparing your data in the worksheet:

• Enter the required information for each column. Refer to the tool tips on each column header for detailed instructions.

• Don't change the order of the columns in the template.

• You can hide or skip the columns you don't use, but don't delete them.

Settings That Affect the Import Cash Management Bank Statements

Follow these guidelines when preparing your data in the worksheet:

• Enter the required information for each column. Refer to the tool tips on each column header for detailed instructions.

• Don't change the order of the columns in the template. Changing the order of the columns will cause the load process to fail.

• You can hide or skip the columns you don't use, but don't delete them. Deleting columns will cause the load process to fail.

• Each interface table represents a separate Excel sheet.

• The first row in each sheet contains column headers that represent the interface table columns. The columns are in the order that the control file expects them to be in the data file.

• Each column header contains (should this be tool tip) bubble text about the expected data type and, in some cases, instruction text.

• You must enter data that conforms to what the control file can accept and process for the associated database column.

• The format for date fields must be entered as MM/DD/YYYY.

• Amount columns must not contain a thousand separators and must use a period (.) as the decimal separator.

• Columns that must be whole numbers have data validation to allow only whole numbers.

• Enter positive amounts and use the Debit orCredit indicator column to distinguish debit and credit amounts.

• Columns have been formatted, where applicable, to match the expected data type to eliminate data entry errors.

• For columns that require internal ID values, refer to the bubble text for additional guidance about finding these values.

• For the Balance Code field, the valid values are from the lookup codes defined in lookup type CE_INTERNAL_BALANCE_CODES You can access the lookup types from the Manage Cash Management Lookups page. Enter the Balance Code in the spreadsheet.

The following are the Balance Codes delivered by Cash Management:

Balance Code	Meaning
CLAV	Closing available
CLBD	Closing booked
OPAV	Opening available
OPBD	Opening booked

For the Transaction Type field, the valid values are from the lookup codes defined in lookup type CE_TRX_TYPE. You can access the lookup types from the Manage Cash Management Lookups page. Enter the transaction type meaning in the spreadsheet.

Transaction Type	Transaction Type Meaning
ACH	Automated clearing house
BKA	Bank adjustment
BKF	Fee
CHK	Check
EFT	Electronic funds transfer
INT	Interest
LBX	Lockbox
MSC	Miscellaneous
ZBA	Zero balance

For Transaction Code, enter valid codes defined in Manage Bank Statement Transaction Codes task. The following are examples of transaction codes:

Transaction Code	Description
100	Check-Payroll
115	Lockbox Deposit
475	Check paid
698	Miscellaneous Fee

How Import Cash Management Bank Statements is Processed

Once the bank statement information has been prepared, complete these steps in order:

Task	Action	Results
Generate the CSV file.	Click the Generate CSV File button.	A .zip file is created containing 6 CSV files. This creates a .zip file containing six CSV files.
Transfer the .zip file.	Navigate to Tools > File Import and Export.	Transfers the .zip file to the WebCenter document repository. Select "fin$/cashManagement$/import$" for the Account.
Load and import the data.	Run the program: Load Interface File for Import.	In the Process Details screen, for the Import Process, select Import Bank Statements from a Spreadsheet. For the Data File, select the name of the .ZIP file that was transferred into the (fin$/cashManagement$/import$) repository in the UCM. The data from the .ZIP file will be loaded to the following interface tables: CE_STATEMENT_HEADERS_INT, CE_STATEMENT_LINES_INT, CE_STMT_BALANCES_INT, CE_STMT_BAL_AVALBTY_INT, CE_STMT_LINES_AVALBTY_INT, CE_STMT_LINES_CHARGES_INT After the load is successfully completed, the Import process imports the data from the interface tables into the corresponding bank statement tables. You are able to review the imported statements from the Manage Bank Statements page, the entry type is spreadsheet, then proceed with either manual or autoreconciliation.
Review import errors.	Review ESS Job Log files, fix the errors in the spreadsheet and restart the process again.	If there are errors during import after a successful load, the entire import fails and no records will be imported into bank statement tables from the interface tables. You can review the import errors from the ESS job log files. You must purge the current load from the Bank Statement and Reconciliation dashboard, fix the errors in the spreadsheet, and restart the process again.