Search Results ce_available_transactions_tmp




Overview
Explore in AI Chat

Overview

CE_AVAILABLE_TRANSACTIONS_TMP is a temporary (staging) table in the Cash Management (CE) module of Oracle E-Business Suite, owned by the CE schema. It holds a working set of candidate bank transactions — receipts, payments, miscellaneous transactions, and journal entries — that are eligible for reconciliation or clearing against bank statement lines. The table is populated on demand by concurrent programs and forms such as the Bank Statement reconciliation and Cash Management transaction inquiry flows, and its contents are typically scoped to a query session or user, then discarded once the action completes.

The physical schema comprises 75 columns in release 12.2.2, with a mandatory ROW_ID column and a broad set of descriptive, currency, date, and reconciliation-status attributes. Because the table is transient and session-scoped, it should not be treated as a source of truth; persistent transaction data resides in CE_BANK_ACCT_TRANSACTIONS, AR_CASH_RECEIPTS, and AP payment tables.

From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as standalone. That classification is a modeling suggestion only: the table behaves as a query-result cache rather than a hub, link, or satellite, and no natural business key uniquely identifies its rows across sessions. ROW_ID functions as the surrogate key, but it is not a durable business key.

Key Information Stored

The most significant columns fall into four groups:

Business-key candidates are weak here. CASH_RECEIPT_ID, APPLICATION_ID, RECEIPT_CLASS_ID, RECEIPT_METHOD_ID, LEGAL_ENTITY_ID, ORG_ID, and CODE_COMBINATION_ID provide join paths to persistent entities rather than a unique key. Foreign-key relationships documented in the metadata include ROW_ID to CS_SYSTEMS_ALL_B_TEMP, RECEIPT_CLASS_ID to AR_RECEIPT_CLASSES, RECEIPT_METHOD_ID to AR_RECEIPT_METHODS, and LEGAL_ENTITY_ID to FV_LEGAL_ENTITIES. Standard audit columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY are also present.

Common Use Cases and Queries

Typical usage centers on identifying reconcilable items and driving the reconciliation UI. A frequent pattern filters by bank account and currency:

  • SELECT TRX_NUMBER, TRX_TYPE, AMOUNT, CURRENCY_CODE, TRX_DATE, STATUS_DSP FROM CE_AVAILABLE_TRANSACTIONS_TMP WHERE BANK_ACCOUNT_ID = :p_account AND CURRENCY_CODE = :p_currency;
  • Joining to AR_RECEIPT_METHODS and AR_RECEIPT_CLASSES on RECEIPT_METHOD_ID and RECEIPT_CLASS_ID to report receipts by method or class.
  • Summing AMOUNT_CLEARED and ACTUAL_AMOUNT_CLEARED by CLEARED_DATE for clearing and float analysis.
  • Filtering RECONCILED_STATUS_FLAG or COUNT_CLEARED to isolate unreconciled candidates before posting a bank statement batch.

Because the table is temporary, these queries must run within the session or concurrent request that populated it. Loading is normally performed by Cash Management programs, and the records are purged after use.

Related Objects

The table intersects with the following principal objects, joined on the columns indicated:

  • AR_RECEIPT_METHODS via RECEIPT_METHOD_ID.
  • AR_RECEIPT_CLASSES via RECEIPT_CLASS_ID.
  • FV_LEGAL_ENTITIES via LEGAL_ENTITY_ID.
  • CS_SYSTEMS_ALL_B_TEMP via ROW_ID.
  • CE_BANK_ACCT_TRANSACTIONS and CE_BANK_ACCOUNTS via TRX_ID / BANK_ACCOUNT_ID for persistent transaction and account detail.
  • AR_CASH_RECEIPTS via CASH_RECEIPT_ID for receipt-level attributes.
  • Cash Management reconciliation APIs and concurrent programs, such as the Automatic Reconciliation and Bank Statement Import processes, which read this staging content.