Search Results ce_available_trx_tmp_n1

Overview

CE.CE_AVAILABLE_TRANSACTIONS_TMP is a global temporary table in the Cash Management (CE) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It serves as a transient working area used by Cash Management reconciliation and transaction-search functionality to materialize the set of bank transactions that are candidates for manual or automatic reconciliation, clearing, and review. Because it is defined as a Global Temporary Table with a SYS$SESSION duration, the data it holds is visible only to the session that inserted it and is automatically purged at session termination; concurrent users therefore maintain fully isolated result sets without contention.

From a heuristic Data Vault modeling perspective, this object behaves as a standalone staging or work table rather than a persistent hub, link, or satellite. Its lifetime is bound to a single reconciliation or query session, and its columns are denormalized descriptions (account names, customer names, vendor names, status displays) populated for presentation rather than for durable reference-integrity storage. It should be treated as a transient satellite-like staging surface, not as a system of record.

Key Information Stored

The table exposes 75 documented columns. The most functionally significant are:

• ROW_ID — a ROWID column acting as the physical locator; it is the join anchor back to underlying staging structures such as CS_SYSTEMS_ALL_B_TEMP.
• SEQ_ID — the surrogate sequencing key; a unique index (CE_AVAILABLE_TRX_TMP_U1) is defined on this column, making it the primary business-key candidate despite being declared NONUNIQUE at the catalog level.
• TRX_ID, TRX_NUMBER, TRX_TYPE, TYPE_MEANING — the transaction identity and classification (payment, receipt, miscellaneous). A supporting index (CE_AVAILABLE_TRX_TMP_N2) exists on TRX_NUMBER.
• STATUS, STATUS_DSP, RECONCILED_STATUS_FLAG — the reconciliation state of the row. The RECONCILED_STATUS_FLAG column is precisely the attribute users query when filtering reconciled versus unreconciled transactions.
• AMOUNT, AMOUNT_CLEARED, ACTUAL_AMOUNT_CLEARED, COUNT_CLEARED — monetary and clearing aggregates used to derive remaining reconcilable balances.
• BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM, BANK_NAME, CE_BANK_ACCT_USE_ID — the bank account context.
• CURRENCY_CODE, BANK_CURRENCY_CODE, EXCHANGE_RATE, EXCHANGE_RATE_TYPE — currency and conversion attributes.
• GL_DATE, TRX_DATE, CLEARED_DATE, VALUE_DATE, DEPOSIT_DATE — the core date dimensions.
• APPLICATION_ID, ORG_ID, LEGAL_ENTITY_ID — multi-org and application tenancy. APPLICATION_ID is indexed by CE_AVAILABLE_TRX_TMP_N1.

Common Use Cases and Queries

The dominant use case is transaction search and reconciliation review within the Cash Management workbench. Analysts filter by RECONCILED_STATUS_FLAG to isolate unreconciled items:

SELECT trx_number, status_dsp, amount, amount_cleared, currency_code
FROM   ce.ce_available_transactions_tmp
WHERE  reconciled_status_flag = 'N'
AND    bank_account_id = :p_bank_account_id;

Reconciliation matching typically aggregates cleared amounts against transaction amounts to compute open balances, and joins to CE_BANK_ACCT_USE_ID for account-use context. Reporting extracts commonly group by bank account, currency, and status for aging summaries of outstanding reconcilable items, while session-scoped ETL pipelines may stage candidate transactions here before writing to persistent reconciliation tables.

Related Objects

• CS_SYSTEMS_ALL_B_TEMP — joined on ROW_ID; supplies the source transaction staging rows.
• AR_RECEIPT_CLASSES — joined on RECEIPT_CLASS_ID; receipt classification reference.
• AR_RECEIPT_METHODS — joined on RECEIPT_METHOD_ID; receipt payment-method reference.
• FV_LEGAL_ENTITIES — joined on LEGAL_ENTITY_ID; legal entity ownership context.
• CE.CE_STATEMENT_LINES and CE.CE_BANK_ACCOUNTS — joined conceptually via TRX_ID, BANK_ACCOUNT_ID, and CE_BANK_ACCT_USE_ID for reconciliation against the bank statement.