Results for “average_close_available_mtd”

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

Overview

CE_BANK_ACCT_BALANCES is a Cash Management (CE) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores internal bank account balances. Each row captures a point-in-time balance snapshot for a bank account defined in CE_BANK_ACCOUNTS, allowing the application to persist ledger, available, and value-dated balances together with calculated float and period-to-date average figures. This table underpins the Bank Account Balances and Cash Position windows, and it is the source from which the Cash Management workbench derives historical balance trends and forecasting inputs.

From a dimensional modeling perspective, the documented foreign key relationship (BANK_ACCOUNT_ID referencing CE_BANK_ACCOUNTS) and the mined Data Vault classification suggest a satellite-leaning structure. The table does not appear to function as an independent hub; instead it behaves as a descriptive satellite attached to the CE_BANK_ACCOUNTS business key, with BALANCE_DATE acting as the driving date attribute for each balance record.

Key Information Stored

The primary key is CE_BANK_ACCT_BALANCES_PK, defined on the surrogate column BANK_ACCT_BALANCE_ID, which is also the sole documented unique index (CE_BANK_ACCT_BALANCES_U1). The remaining significant columns include:

• BANK_ACCOUNT_ID — foreign key to CE_BANK_ACCOUNTS; identifies the internal bank account to which the balance belongs.
• BALANCE_DATE — the effective date of the balance snapshot; the natural business date for historical queries.
• LEDGER_BALANCE — the accounting ledger balance for the account on the balance date.
• AVAILABLE_BALANCE — the balance available for use, reflecting holds or uncleared items.
• VALUE_DATED_BALANCE — the value-dated balance used for cash positioning.
• ONE_DAY_FLOAT and TWO_DAY_FLOAT — float amounts representing timing differences between ledger and available balances.
• AVERAGE_CLOSE_LEDGER_MTD and AVERAGE_CLOSE_LEDGER_YTD — month-to-date and year-to-date average closing ledger balances.
• AVERAGE_CLOSE_AVAILABLE_MTD and AVERAGE_CLOSE_AVAILABLE_YTD — month-to-date and year-to-date average closing available balances.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER — standard WHO audit and optimistic locking columns maintained by the EBS framework.

Common Use Cases and Queries

Typical scenarios include daily cash positioning, historical balance trending, and float analysis across internal bank accounts. A representative query retrieves the most recent balance for each account:

• SELECT b.bank_account_id, b.balance_date, b.ledger_balance, b.available_balance FROM ce_bank_acct_balances b WHERE b.balance_date = (SELECT MAX(b2.balance_date) FROM ce_bank_acct_balances b2 WHERE b2.bank_account_id = b.bank_account_id)
• Joining to CE_BANK_ACCOUNTS on BANK_ACCOUNT_ID to obtain the account number and name for reporting.
• Aggregating AVERAGE_CLOSE_LEDGER_MTD and AVERAGE_CLOSE_LEDGER_YTD by account for treasury dashboards.
• Comparing VALUE_DATED_BALANCE against LEDGER_BALANCE to quantify float exposure.

Because balanced data is date-keyed, queries should always filter on BALANCE_DATE to avoid mixing snapshots from different reporting periods.

Related Objects

The most significant related objects, based on the documented foreign key and Cash Management domain usage, include:

• CE_BANK_ACCOUNTS — the parent table; joined via CE_BANK_ACCT_BALANCES.BANK_ACCOUNT_ID = CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID.
• CE_BANK_BRANCHES and CE_BANKS — reached through CE_BANK_ACCOUNTS to resolve bank and branch attributes for balance reporting.
• CE_STATEMENT_HEADERS and CE_STATEMENT_LINES — bank statement data reconciled against account balances.
• CE_CASH_POSITIONS and related Cash Management views — reporting entities that consume balance snapshots.
• GL_JE_LINES / GL_BALANCES — general ledger sources compared against ledger balances during reconciliation.
• AP_CHECKS_ALL and AR_CASH_RECEIPTS_ALL — transaction sources influencing available and float balances.