Treasury-Loaded Bank Account Information Report
Bank account and statement information loaded into Cash Management from an external treasury system — daily balances, statement lines, and transaction codes by bank account — so finance can reconcile against positions captured outside Oracle.
Sample build of the Treasury-Loaded Bank Account Information Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Bank Account | Statement Date | Opening | Closing | Lines | Transaction Code |
|---|---|---|---|---|---|
| 1000-2100-000 | 2026-04-30 | Sample | Sample | Sample | Sample |
| 1000-5400-000 | 2026-03-31 | — | — | — | — |
| 1000-1410-000 | 2026-02-28 | Sample | Sample | Sample | Sample |
| 2000-2100-000 | 2026-01-31 | — | — | — | — |
| 1000-6300-000 | 2025-12-31 | Sample | Sample | Sample | Sample |
| 1000-2100-000 | 2026-04-30 | Sample | Sample | Sample | Sample |
The report reads the bank balances and statement lines loaded from the external treasury system per account.
Seven bank accounts have a gap in their loaded statements — a missing day — so reconciliation breaks on the days with no statement.
Confirm the treasury feed ran for every account every day; a missing daily load is the usual reason a bank rec shows an unexplained gap.
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
- CE_BANK_ACCT_USES_ALL
- FUN_ORGANIZATIONS_V
- CE_STMT_BALANCES
- CE_STATEMENT_LINES
- CE_BANK_ACCOUNTS
- CE_STATEMENT_HEADERS
- CE_TRANSACTION_CODES
- CE_LOOKUPS
Show / hide SQL
<![CDATA[select
(select listagg (FOU.BU_NAME,', ') within group ( order by bau.org_id )
from ce_bank_acct_uses_all bau , fun_organizations_v fou
where bau.bank_account_id = ch.bank_account_id
and bau.org_id = fou.bu_id
and trunc(ch.statement_date) <= nvl(END_DATE,ch.statement_date)) BU_NAME,
cba.bank_account_num,
cba.bank_account_name,
ch.statement_number statement_number,
to_char(ch.statement_date,'DD-Mon-YYYY','nls_date_language=American') STATEMENT_CREATION_DATE, -- STATEMENT_DATE,
(select sum(BALANCE_AMOUNT) from ce_stmt_balances where statement_header_id = ch.statement_header_id
and bank_account_id =cba.bank_account_id
and balance_code = 'OPBD' ) BEGIN_BALANCE,
(select sum(BALANCE_AMOUNT) from ce_stmt_balances where statement_header_id = ch.statement_header_id
and bank_account_id =cba.bank_account_id
and balance_code = 'CLBD' ) END_BALANCE,
(select sum (Amount) from ce_statement_lines where flow_indicator = 'DBIT' and statement_header_id = ch.statement_header_id ) TOTAL_DEBIT,
(select sum (Amount) from ce_statement_lines where flow_indicator = 'CRDT' and statement_header_id = ch.statement_header_id ) TOTAL_CREDIT,
cl.line_number line_number,
cl.trx_type TRX_TYPE,
cl.amount AMOUNT,
l1.meaning recon_status, --cl.RECON_STATUS , --cl.status STATUS,
cl.CHECK_NUMBER, --cl.bank_trx_number BANK_TRX_NUMBER,
ctc.TRX_CODE , --cl.trx_code TRX_CODE,
CL.ADDENDA_TXT TRX_TEXT,-- cl.trx_text TRX_TEXT,
CH.CURRENCY_CODE -- cl.currency_code CURRENCY_CODE
from
ce_bank_accounts cba,
ce_statement_headers ch,
ce_statement_lines cl,
ce_transaction_codes ctc,
ce_lookups l1
where 1=1
and cba.bank_account_id = ch.bank_account_id
and ch.statement_header_id = cl.statement_header_id
and cl.trx_code_id = ctc.transaction_code_id
and to_char(ch.statement_date, 'MM/DD/YYYY') between to_char(:p_start_date, 'MM/DD/YYYY') and to_char(:p_end_date, 'MM/DD/YYYY')
and ( cba.bank_account_num in (:p_bank_account_num) or 'all' in ('all' || :p_bank_account_num) )
and ( ch.statement_number in (:p_statement_number) or 'all' in ('all' || :p_statement_number))
and l1.lookup_type = 'LINE_RECON_STATUS'
and l1.lookup_code = cl.RECON_STATUS
order by 1,2,5,4,10
--and ch.STATEMENT_HEADER_ID ='300000036853290']]>The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
| Element | Type | Definition |
|---|---|---|
| FUN_ORGANIZATIONS_V | dimension | dimension |
| CE_STMT_BALANCES | dimension | dimension |
| CE_STATEMENT_LINES | dimension | dimension |
| CE_BANK_ACCOUNTS | dimension | dimension |
| Amount | measure | measure |
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| CE_BANK_ACCT_USES_ALL | Setup / configuration table — joined for reference, not exposed for analytics | |
| FUN_ORGANIZATIONS_V | Setup / configuration table — joined for reference, not exposed for analytics | |
| CE_STMT_BALANCES | 2 | 1 |
| CE_STATEMENT_LINES | 23 | 3 |
| CE_BANK_ACCOUNTS | 9 | 12 |
| CE_STATEMENT_HEADERS | 7 | 4 |
| CE_TRANSACTION_CODES | Setup / configuration table — joined for reference, not exposed for analytics | |
| CE_LOOKUPS | Setup / configuration table — joined for reference, not exposed for analytics | |