Results for “ce_cp_sub_open_bal_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CE_CP_SUB_OPEN_BAL_V view is an Oracle E-Business Suite object owned by the APPS schema within the Cash Management (CE) product. It exposes opening and historical balance information for Treasury subsidiary bank accounts, derived by joining the bank account definition to the bank account balance table. The objects documented here are valid in both Oracle EBS 12.1.1 and 12.2.2.

The view plays a reporting and integration role rather than a transactional one. It presents a denominator-style dataset for Treasury cash positioning, where subsidiary accounts (those with an ACCOUNT_CLASSIFICATION of SUBSIDIARY) are read alongside their periodic LEDGER_BALANCE, AVAILABLE_BALANCE, VALUE_DATED_BALANCE, ONE_DAY_FLOAT, and TWO_DAY_FLOAT values. The name prefix CE_CP indicates a Cash Positioning context, and the suffix _SUB_OPEN_BAL indicates subsidiary opening balances.

Underlying Base Objects

The view is defined over two base objects, documented as synonyms in this environment:

• CE_BANK_ACCOUNTS (aliased BA) — the bank account master, supplying the owner organization, account number, account classification, and currency.
• CE_BANK_ACCT_BALANCES (aliased BB) — the periodic balance store, supplying the balance date and balance figures.

The join is on BB.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID, filtered by BA.ACCOUNT_CLASSIFICATION = 'SUBSIDIARY'. A window function, LAG(BB.BALANCE_DATE, 1, SYSDATE+10000) OVER (PARTITION BY BA.BANK_ACCOUNT_NUM ORDER BY BB.BALANCE_DATE DESC), derives the next statement date within each account. Balance measures are wrapped in NVL(…,0) to suppress nulls.

Key Columns

• LEGAL_ENTITY_ID — the account owner organization identifier (ACCOUNT_OWNER_ORG_ID in the view text).
• ACCOUNT_NUMBER — the bank account number (BANK_ACCOUNT_NUM).
• CURRENCY_CODE — the account currency.
• STATEMENT_DATE — the balance date (BB.BALANCE_DATE) for the row.
• NEXT_STMT_DATE — the prior row's balance date, produced by the LAG analytic.
• LEDGER_BALANCE — the ledger (book) balance for the account and statement date; returns 0 when null.
• CASHFLOW_BALANCE — mapped to AVAILABLE_BALANCE, the available balance.
• INT_CALC_BALANCE — mapped to VALUE_DATED_BALANCE, the value-dated balance.
• ONE_DAY_FLOAT and TWO_DAY_FLOAT — float amounts for one- and two-day settlement.

Common Use Cases and Queries

Typical scenarios include reconstructing subsidiary opening balances by currency, comparing ledger versus available balances, and computing float-adjusted cash positions for Treasury reporting. A representative query filtered on the requested balance term is shown below.

• Retrieve the latest ledger balance per subsidiary account:
  SELECT account_number, currency_code, statement_date, ledger_balance FROM ce_cp_sub_open_bal_v WHERE ledger_balance <> 0 ORDER BY statement_date DESC;
• Filter for a specific legal entity and currency:
  SELECT * FROM ce_cp_sub_open_bal_v WHERE legal_entity_id = :p_org_id AND currency_code = :p_currency;
• Compare ledger, available, and value-dated balances:
  SELECT account_number, statement_date, ledger_balance, cashflow_balance, int_calc_balance FROM ce_cp_sub_open_bal_v;

Because the view returns balances for all subsidiary accounts without an effective-date filter on the balance table, queries should apply their own date predicates against statement_date to isolate a reporting period.