Search Results reconcile_to_statement_line

Overview

APPS.CE_STATEMENT_LINES_V is a reporting and integration view over the bank statement line entity in Oracle Cash Management. It exposes the detail rows of bank statements previously loaded and stored in CE_STATEMENT_LINES, resolving foreign-key identifiers to their display meanings and pre-computing several derived columns used by Oracle EBS forms, concurrent programs, and reconciliation logic. The view presents each statement line with its transaction date, transaction type and code, amounts, exchange-rate information, and descriptive text fields, alongside a set of descriptive flexfield columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) and audit columns.

The view’s principal value is that it removes lookup translation work from the caller. Transaction type codes such as MISC_DEBIT, DEBIT, REJECTED, NSF, and SWEEP_OUT are mapped to user-facing meanings, and the view additionally publishes DEBIT_AMOUNT and CREDIT_AMOUNT columns that normalize statement lines into a debit/credit presentation. For users searching on misc_debit, this is the primary location: the string appears in the DECODE expressions that populate these two calculated columns.

Underlying Base Objects

The view is defined over the following documented base objects:

• CE_STATEMENT_LINES (synonym) — the driving table, aliased SL, supplying the line-level attributes.
• CE_STATEMENT_HEADERS (synonym) — the statement header referenced by STATEMENT_HEADER_ID.
• CE_LOOKUPS (view) — aliased L for transaction type meanings and L2 for status meanings.
• GL_DAILY_CONVERSION_TYPES (synonym) — aliased GCT to resolve EXCHANGE_RATE_TYPE to USER_CONVERSION_TYPE.
• CE_BANK_ACCTS_GT_V (view) — bank account context used in the join path.
• CE_AUTO_BANK_MATCH (package) — its GET_INVERSE_RATE function is invoked inside the EXCHANGE_RATE_DSP expression.

Rows in CE_STATEMENT_LINES are the source of truth; the lookup and conversion-type joins merely decorate those rows for presentation. Because the view calls a PL/SQL package function, it is not purely relational and cannot be used in certain parallel or restricted contexts.

Key Columns

• STATEMENT_LINE_ID / STATEMENT_HEADER_ID / LINE_NUMBER — line identity and parent statement.
• TRX_DATE, EFFECTIVE_DATE, TRX_TYPE, TRX_TYPE_DSP — transaction timing, code, and display meaning.
• AMOUNT, CHARGES_AMOUNT, ORIGINAL_AMOUNT — statement line amounts in the statement currency.
• EXCHANGE_RATE, EXCHANGE_RATE_DSP, EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE — rate and its inverse, governed by the CE_AUTO_BANK_MATCH.GET_INVERSE_RATE setting.
• DEBIT_AMOUNT / CREDIT_AMOUNT — calculated by DECODE on TRX_TYPE. MISC_DEBIT, DEBIT, REJECTED, NSF, and SWEEP_OUT populate DEBIT_AMOUNT; MISC_CREDIT, CREDIT, STOP, and SWEEP_IN populate CREDIT_AMOUNT.
• STATUS, STATUS_DSP — reconciliation status and its meaning.
• RECONCILE_TO_STATEMENT_LINE — flag controlling reconciliation behavior.
• ATTRIBUTE1–ATTRIBUTE15, ATTRIBUTE_CATEGORY — descriptive flexfield content.

Common Use Cases and Queries

The view is commonly used for bank statement line inquiries, reconciliation reporting, and interfaces that need translated values without re-implementing lookup joins. A typical query isolating miscellaneous debits, the term behind the misc_debit search, is:

• SELECT statement_line_id, trx_date, trx_type_dsp, amount, debit_amount, credit_amount FROM ce_statement_lines_v WHERE trx_type = 'MISC_DEBIT' AND trx_date BETWEEN :from_date AND :to_date;
• SELECT statement_header_id, SUM(debit_amount), SUM(credit_amount) FROM ce_statement_lines_v GROUP BY statement_header_id;
• SELECT statement_line_id, trx_code_id_dsp, status_dsp, reconcile_to_statement_line FROM ce_statement_lines_v WHERE status_dsp = 'Unreconciled';

For high-volume batch processing, direct querying of CE_STATEMENT_LINES with explicit lookup joins is often preferable, since the view invokes a package function per row.