CE Bank Statement Upload
CE Bank Statement Upload loads bank statements into Oracle Cash Management directly from Excel – one statement line per row – and runs Oracle’s standard Bank Statement Import for you. Statement header details repeat on every row and the header is created once per statement. Optionally the upload also runs Oracle’s AutoReconciliation in the same step, so the imported lines are matched against your open receipts, payments and journals and cleared automatically.
When to use it
- Load a daily, weekly or monthly bank statement exported from your bank portal.
- Import and reconcile a statement in a single step, instead of running the import and reconciliation programs separately.
- Load statements for several bank accounts, or several statements, in one file.
- Correct and re-load statement lines that failed a previous import.
- Replace manual keying of statements into the Cash Management form, or a custom statement loader.
Before you start
- Blitz Report is installed and you are signed in to a responsibility authorized for Cash Management.
- The bank account already exists in Cash Management and its bank statement transaction codes are defined – the Transaction Code list shows the codes belonging to the account you enter.
- The statement date falls in an open or future-enterable period.
- If you want to use AutoReconciliation, your Cash Management transaction codes and matching rules are configured – the matching itself is Oracle’s, and it only matches what those rules cover.
- Every row has the five mandatory values: Account Number, Statement Number, Statement Date, Transaction Date and Amount.
Step 1 – Open the upload and choose a mode
Run CE Bank Statement Upload from the Blitz Report menu and choose an Upload Mode:
- Create – an empty template for entering a new statement.
- Create, Update – additionally downloads statement lines that are still waiting in the bank statement interface because they did not import, so you can correct them and load them again. Any new rows you add are still created.
Step 2 – Set the parameters and download the template
| Parameter | Purpose |
|---|---|
| Upload Mode | Create (empty template) or Create, Update (statement lines still pending in the interface are downloaded for correction). Default is Create. |
| AutoReconciliation | Set to Yes to run Oracle’s AutoReconciliation immediately after the import, matching the imported lines against your open transactions. Default is No (import only). |
| GL Date | Accounting date used for the import and for the accounting created when lines are reconciled. Leave blank to use the latest statement date in your file. |
| Account Number | In Create, Update mode, restricts the downloaded lines to one bank account. |
| Statement Number | In Create, Update mode, restricts the downloaded lines to one statement. |
Click Download to open the Excel template.
Step 3 – Enter the statement header
On each row enter the Account Number (pick from the list), the Statement Number and the Statement Date – all three are required. Currency defaults from the bank account and only needs changing for a foreign-currency statement. Control Begin Balance and Control End Balance are optional; enter them if you want the statement checked against the balances printed on the bank’s statement.
These header values repeat on every line of the same statement – the statement header itself is created only once, from the first row that references it. To load several statements, or statements for several accounts, simply continue in the same file with different header values.
Step 4 – Enter the statement lines
For each line enter the Transaction Date and Amount (both required) and the Transaction Code, which is picked from the codes defined for that bank account. Amounts are always entered as positive values – whether a line is a receipt or a payment is determined by the type of its transaction code, exactly as in the Cash Management form.
The remaining columns are optional and help reconciliation find a match: Bank Transaction Number (for example the cheque or payment reference), Transaction Description, Invoice Text, Customer Text and Value Date. Leave Line Number blank to have the lines numbered automatically in file order, or enter your own numbers to match the bank’s line numbering.
You do not need to work out the credit and debit control totals – they are calculated for you from the entered lines and their transaction code types.
Step 5 – Validate and Save
Click Validate and Save. This checks for missing required values and runs the upload’s own checks – that the bank account exists and that the statement has not already been imported – then saves the file. Correct anything it flags before continuing.
Step 6 – Upload and view the result
Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which stages the statement, runs the Bank Statement Import and, if you chose it, AutoReconciliation. When it finishes, a result report opens showing each line as success or error.
What’s produced
- The bank statement and its lines in Cash Management, available in the Enter/Reconcile Bank Statements form and in the standard Cash Management reconciliation reports.
- If AutoReconciliation was set to Yes, matched lines are reconciled and the underlying receipts and payments are cleared, exactly as if you had run Oracle’s Bank Statement Import and AutoReconciliation program.
- A result report listing every line with a status and a message – on success the line’s reconciliation status (Reconciled or Unreconciled), and on failure the validation or Cash Management import message.
Common questions
Do I have to enter line numbers?
No. Leave Line Number blank and the lines are numbered automatically in the order they appear in the file. Enter your own numbers only if you want them to match the bank’s line numbering.
Should payments be entered as negative amounts?
No. Enter all amounts as positive values. Whether a line increases or decreases the balance is determined by the type of the transaction code you select.
What exactly does AutoReconciliation match?
It is Oracle’s own AutoReconciliation, so it uses the transaction codes and matching rules already configured in your Cash Management setup. Where those rules cover a line it is reconciled automatically; anything they do not cover simply comes through as unreconciled for you to handle in the normal way, so nothing is ever matched incorrectly.
What happens if I load the same statement twice?
It is rejected. A statement number that has already been imported for that bank account is flagged when you validate the file, and again by the import if it gets that far.
Can I fix lines that failed to import?
Yes. Run the upload again in Create, Update mode – lines still sitting in the interface are downloaded so you can correct them. When you upload the corrected file it replaces the earlier attempt and imports it again.
Can several bank accounts or statements be loaded in one file?
Yes. Each row carries its own account and statement details, so a single file can contain several statements and several bank accounts.
Can this run automatically without anyone opening Excel?
Yes. Blitz Upload is a standard Oracle concurrent program, so it can be scheduled to run periodically and pick up statement files that are placed in a designated directory, writing a result file back for each one.
Can I delete a statement with this upload?
No. It creates statements and lines only. Remove a statement in Cash Management if you need to load it again.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| Account Number is required / Statement Number is required / Statement Date is required / Transaction Date is required / Amount is required | One of the mandatory values is blank on the row. | Fill in all five mandatory values on every row. |
| Bank account does not exist | The account number is not defined in Cash Management, or is not accessible to your responsibility. | Pick the account from the list rather than typing it. |
| Bank account number exists more than once and cannot be identified uniquely | The same bank account number is defined more than once, so the statement cannot be assigned to one account. | Ask your Cash Management administrator which account should receive the statement; the duplicate account setup needs to be resolved first. |
| Statement is already imported for bank account | A statement with that number has already been imported for the account. | Use the bank’s actual statement number for a new statement, or remove the existing statement in Cash Management before loading it again. |
| The statement already exists | The same duplicate detected by the Cash Management import. | As above. |
| Check the opening and closing balances in the control totals | The Control Begin Balance and Control End Balance you entered do not agree with the statement lines. | Correct the balances, or leave both blank to skip the balance check. |
| Transaction Code is invalid | The transaction code is not one of the codes defined for that bank account. | Pick the code from the dropdown, or have the code added to the bank account’s transaction codes. |
select null action_, null status_, null message_, null request_id_, null modified_columns_, x.* from ( select cshi.bank_account_num bank_account_number, cshi.statement_number, cshi.statement_date, cshi.currency_code currency, cshi.control_begin_balance, cshi.control_end_balance, csli.line_number, csli.trx_date transaction_date, csli.trx_code transaction_code, csli.amount, csli.bank_trx_number bank_transaction_number, csli.trx_text transaction_description, csli.invoice_text, csli.customer_text, csli.effective_date value_date, :auto_reconcile auto_reconcile, :gl_date gl_date, rowidtochar(cshi.rowid) header_row_id, rowidtochar(csli.rowid) line_row_id, row_number() over (order by cshi.bank_account_num,cshi.statement_number,csli.line_number) upload_row from ce_statement_headers_int cshi, ce_statement_lines_interface csli where 1=1 and nvl(cshi.record_status_flag,'I')<>'T' and cshi.bank_account_num=csli.bank_account_num(+) and cshi.statement_number=csli.statement_number(+) ) x |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Upload Mode |
| LOV | |
| AutoReconciliation | LOV Oracle | ||
| GL Date | Date | ||
| Account Number |
| LOV | |
| Statement Number |
| Char |