Oracle Fusion Cash Management: Key Tables and Sample SQL Queries

Oracle Fusion Cash Management (CE) plays a critical role in managing and reconciling an organization’s bank accounts, cash positions, and liquidity. 

This post will help in understanding the core Cash Management tables and relationships is essential for building reports and integrations.

In this blog post, we will discuss about :

• Core Cash Management Tables

• Table Relationships

• Sample SQL Queries

• Common Use Cases

---

Key Oracle Fusion Cash Management Tables

Table Name	Description
CE_BANK_ACCOUNTS	Stores bank account definitions (shared with Payables).
CE_BANK_ACCT_USES_ALL	Links bank accounts to uses (Payables, Receivables, Payroll, etc.).
CE_STATEMENT_HEADERS	Stores bank statement header details.
CE_STATEMENT_LINES	Contains transaction lines from bank statements (debits/credits).

Table Relationships

CE_BANK_ACCOUNTS → CE_BANK_ACCT_USES_ALL → CE_STATEMENT_HEADERS → CE_STATEMENT_LINES
                                                                                             

---

Sample SQL Queries

---

1. List of Bank Accounts

SELECT
  cba.bank_account_id,
  cba.bank_account_name,
  cba.bank_account_num,
  cba.currency_code,
  cba.bank_id,
  cba.branch_id
FROM
  ce_bank_accounts cba
WHERE
  cba.enabled_flag = 'Y';

---

2. Bank Account Uses (e.g., Payables/Receivables)

SELECT
  cba.bank_account_name,
  cau.uses_type,
  cau.start_date,
  cau.end_date
FROM
  ce_bank_acct_uses_all cau,
  ce_bank_accounts cba 
WHERE cau.bank_account_id = cba.bank_account_id
AND
  cau.uses_type IN ('PAYABLES', 'RECEIVABLES');

---

3. Bank Statement with Line Items

SELECT
  csh.statement_header_id,
  csh.statement_num,
  csh.bank_account_id,
  csl.line_number,
  csl.trx_date,
  csl.amount,
  csl.trx_code,
  csl.description
FROM
  ce_statement_headers csh,
  ce_statement_lines csl 
WHERE csh.statement_header_id = csl.statement_header_id
AND
  csh.creation_date >= TO_DATE('2024-01-01','YYYY-MM-DD');

---

4. Cash Balance by Bank Account

SELECT
  cb.bank_account_id,
  cb.balance_date,
  cb.balance_amount,
  cb.currency_code
FROM
  ce_balances cb
WHERE
  cb.balance_date = TO_DATE('2024-06-30', 'YYYY-MM-DD');

---

Real-World Use Cases

• ✅ Cash Position Reporting: Determine current balances across accounts.

• 🔁 Bank Reconciliation: Track unreconciled transactions or mismatches.

• 🏦 Bank Statement Review: Analyze incoming/outgoing transactions from the bank feed.

---

Tips

• Check CE_BALANCES for real-time vs as-of-date balances.

• Use CE_STATEMENT_LINES for tracking bank fees, interest, or ACH deposits.

• For multi-currency environments, ensure you're filtering by currency_code.

---