Search Results ce_statement_lines_v




Overview
Explore in AI Chat

Overview

CE_STATEMENT_LINES_V is a reportable view owned by the APPS schema in Oracle E-Business Suite, defined within the Cash Management (CE) product family. It exposes bank statement line records stored in CE_STATEMENT_LINES, enriched with decoded lookup meanings and derived amount and exchange rate columns, so that concurrent programs, Oracle Reports layouts, and custom integrations can consume statement line data without embedding decode logic or joining lookup tables directly. The view carries the ETRM description "- Retrofitted," indicating that its definition was migrated into the current documentation set and that it remains a supported, valid database object in both release 12.1.1 and 12.2.2. In the reconciliation workflow, the view supplies the line-level detail that supports automatic and manual matching of bank statement lines to cash transactions, and it is the conventional source for statement line inquiry, bank reconciliation reporting, and downstream extracts.

Underlying Base Objects

The documented dependency list identifies the objects over which the view is constructed:

  • CE_STATEMENT_LINES (synonym) — the primary base entity, providing statement line identifiers, transaction dates, types, codes, amounts, charges, exchange rate attributes, reconciliation flags, status, and the descriptive flexfield columns ATTRIBUTE1 through ATTRIBUTE15.
  • CE_STATEMENT_HEADERS (synonym) — the parent statement header, linked through STATEMENT_HEADER_ID.
  • CE_LOOKUPS (view) — supplies decoded meanings for TRX_TYPE and STATUS, aliased in the view text as L and L2 respectively, producing the TRX_TYPE_DSP and STATUS_DSP columns.
  • GL_DAILY_CONVERSION_TYPES (synonym) — resolves the exchange rate type code stored on the line into the user-facing USER_CONVERSION_TYPE value.
  • CE_BANK_ACCTS_GT_V (view) — a global temporary bank account view participating in the view definition.
  • CE_AUTO_BANK_MATCH (package) — invoked within the SELECT list to supply inverse-rate handling logic.

The view therefore sits one level above the statement line table, adding presentation semantics rather than new persistent data.

Key Columns

The view carries the ROWID of the base row as ROW_ID and the primary key as STATEMENT_LINE_ID, with STATEMENT_HEADER_ID preserving the parent relationship. Identification and descriptive attributes include LINE_NUMBER, TRX_DATE, BANK_TRX_NUMBER, TRX_TEXT, CUSTOMER_TEXT, INVOICE_TEXT, and BANK_ACCOUNT_TEXT.

Amount columns comprise AMOUNT, CHARGES_AMOUNT, and ORIGINAL_AMOUNT. Two derived columns, DEBIT_AMOUNT and CREDIT_AMOUNT, are produced by DECODE expressions over TRX_TYPE: MISC_DEBIT, DEBIT, REJECTED, and NSF populate DEBIT_AMOUNT, while MISC_CREDIT, CREDIT, and STOP populate CREDIT_AMOUNT; other transaction types yield NULL in both columns.

Currency and rate information is exposed through EXCHANGE_RATE, EXCHANGE_RATE_TYPE, EXCHANGE_RATE_DATE, CURRENCY_CODE, and the derived EXCHANGE_RATE_DSP. The inverse-rate logic calls CE_AUTO_BANK_MATCH.GET_INVERSE_RATE and, when it returns 'Y', replaces a non-zero rate with its reciprocal. TRX_CURRENCY_TYPE is defined as a literal NULL placeholder.

Status and audit columns include STATUS with its decoded STATUS_DSP, RECONCILE_TO_STATEMENT_LINE (sourced from RECONCILE_TO_STATEMENT_FLAG), CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, and LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical scenarios include reconciliation status reporting, unreconciled line extracts for treasury review, and drill-down from statement headers to their lines. A representative query retrieving unreconciled lines for a currency follows:

  • SELECT statement_line_id, statement_header_id, line_number, trx_date, trx_type_dsp, bank_trx_number, amount, debit_amount, credit_amount, status_dsp
  • FROM ce_statement_lines_v
  • WHERE statement_header_id = :p_header_id
  • AND reconcile_to_statement_line = 'N'
  • AND currency_code = :p_currency
  • ORDER BY line_number;

Aggregation by transaction type is equally common, for example summing DEBIT_AMOUNT and CREDIT_AMOUNT grouped by TRX_TYPE_DSP to reconcile statement totals against the header OPENING_BALANCE and CLOSING_BALANCE. Because the view resolves lookup meanings and inverse rates internally, consumers should avoid re-joining CE_LOOKUPS on the same columns to prevent duplicate decoding, and should treat TRX_CURRENCY_TYPE as non-functional since it is defined as a constant NULL.