Results for “statement_line_amount”

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

Overview

CE_999_REVERSAL_V is an Oracle E-Business Suite view owned by the APPS schema and delivered as part of the Cash Management (CE) product. Its documented purpose is to expose open interface transactions for unclearing. In practice, the view acts as a presentation and selection layer over the Cash Management open interface, returning bank statement reconciliation lines that are eligible to be reversed, or "uncleared," from a reconciliation workflow.

The object is classified with a status of VALID and is a standard, seeded database view rather than a custom artifact. Its naming convention follows the CE_999_INTERFACE family used by Cash Management for open interface processing, where the "999" prefix denotes interface-stage data rather than posted reconciliation records. The view is therefore a reporting and integration aid: it allows the calling application or a report to identify which transactions currently satisfy the conditions for unclearing without directly replicating the complex join and filter logic embedded in the view text.

Underlying Base Objects

The view is defined over six documented base objects, combining interface, bank, system parameter, and reconciliation data:

• CE_999_INTERFACE_V (VIEW) — the primary driver, supplying the interface transaction rows.
• CE_BANK_ACCTS_GT_V (VIEW) — supplies bank account identifiers, account numbers, names, currencies, and the reconciliation status used in the filter.
• CE_BANK_BRANCHES_V (VIEW) — supplies bank branch and bank name information.
• CE_STATEMENT_RECONCILS_ALL (SYNONYM) — referenced in a NOT EXISTS subquery to exclude transactions already reconciled.
• CE_SYSTEM_PARAMETERS (SYNONYM) — provides the set of books and legal entity context.
• GL_SETS_OF_BOOKS (VIEW) — joined to obtain the functional (set of books) currency for amount conversion decisions.

The view joins these objects on set of books, bank branch, bank account, and legal entity, and it filters records so that only transactions whose status matches the bank account's reconciliation status are returned. The NOT EXISTS clause explicitly removes any transaction that already has a current, matched reconciliation record of reference type 'ROI_LINE', ensuring the result set represents only unclearing candidates.

Key Columns

The view projects a large column list. Notable columns include:

• ROW_ID — the interface row identifier.
• MULTI_SELECT — a literal flag ('N') used by multi-select UI flows.
• BANK_ACCOUNT_ID, TRX_ID — foreign keys to the bank account and transaction.
• TRX_TYPE, TYPE_MEANING — transaction type code and its display meaning.
• TRX_NUMBER, TRX_DATE, CLEARED_DATE — transaction identifiers and dates.
• CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED — monetary values, with DECODE logic selecting functional, bank, or foreign amounts depending on the transaction currency relative to the set of books and bank account currency.
• GL_DATE, STATUS_DSP, STATUS — accounting date and status indicators.
• BANK_ACCOUNT_NUM, BANK_ACCOUNT_NAME, BANK_NAME — descriptive bank and account attributes.
• MATURITY_DATE, BATCH_NAME, AGENT — additional descriptive fields, included with NULL or placeholder values where not applicable.

Common Use Cases and Queries

The principal use case is identifying transactions that can be uncleared. A typical query filters by bank account and status:

SELECT trx_id, trx_number, trx_type, currency_code,
       amount, amount_cleared, cleared_date, status
FROM   apps.ce_999_reversal_v
WHERE  bank_account_id = :p_bank_account_id
AND    status = 'CLEARED';

Reporting use cases include reconciling unclearing candidates against bank statement lines and producing audit extracts of transactions pending reversal. Because the view already enforces the NOT EXISTS exclusion against current matched reconciliations, callers can rely on its output for reversal staging without reimplementing that logic. Queries are typically restricted by BANK_ACCOUNT_ID, TRX_DATE, or STATUS to keep result sets manageable, and access should be granted through the standard APPS synonym rather than the underlying interface views.