Search Results roi_line




Overview
Explore in AI Chat

Overview

APPS.CE_ARCH_RECONCILIATIONS is a security-constrained reporting view over the Cash Management reconciliation archive table CE_ARCH_RECONCILIATIONS_ALL. It exposes archived bank statement reconciliation records — the rows that link a bank statement line to the subledger or general ledger activity it was reconciled against — while enforcing Oracle E-Business Suite data-access rules for the querying responsibility. The view is the archival counterpart of the live reconciliation views in Oracle Cash Management and is used wherever reconciled statement history must be reported without re-opening the operational tables.

The view is defined in the APPS schema and is intended for read-only consumption by reports, extracts, and integration queries. Because the underlying table is an "_ALL" table, the view carries no Multi-Org policy of its own; instead, row filtering is performed explicitly in the view's WHERE clause through Oracle's security profile infrastructure.

In Oracle EBS 12.1.1, this object relies on the Multi-Org (MO) security model driven by CE_SECURITY_PROFILES_V and MO_GLOBAL. In 12.2.2, the same view remains valid but is additionally aligned with the Multi-Org Access Control (MOAC) and Legal Entity access model through FND_ACCESS_CONTROL_UTIL, FND_GLOBAL, FND_PROFILE, and XTR_USER_ACCESS, which is why the predicate references both operating-unit and legal-entity organization identifiers.

Underlying Base Objects

The documented base objects are:

  • CE_ARCH_RECONCILIATIONS_ALL (referenced through a SYNONYM) — the single physical source of rows. All twenty columns listed in the view text are drawn from this table.
  • CE_SECURITY_PROFILES_V — supplies the set of organization IDs and organization types the current user is authorized to see. It is referenced twice in the view text: once as alias OU for BUSINESS_GROUP and OPERATING_UNIT organizations, and once as alias LE for LEGAL_ENTITY organizations.
  • FND_ACCESS_CONTROL_UTIL, FND_GLOBAL, FND_PROFILE, MO_GLOBAL, XTR_USER_ACCESS — supporting packages that resolve the effective user, responsibility, operating unit, and legal entity context used to evaluate the security predicates.

Structurally the view is a thin projection: it does not join additional descriptive tables, so referential lookups (for example, to statement lines, journal headers, or legal entities) must be performed by the consuming query.

Key Columns

  • STATEMENT_LINE_ID — identifier of the bank statement line being reconciled.
  • REFERENCE_TYPE — the class of item the statement line was reconciled to. Documented values include JE_LINE (journal entry line), ROI_LINE, and STATEMENT. This column is significant because it participates directly in the security predicate: rows of type JE_LINE, ROI_LINE, and STATEMENT are returned regardless of organization filtering, so answers to searches involving "roi_line" are not suppressed by operating-unit or legal-entity restrictions.
  • REFERENCE_ID — the identifier of the referenced object, interpreted according to REFERENCE_TYPE.
  • JE_HEADER_ID — the general ledger journal header associated with the reconciliation, where applicable.
  • ORG_ID — the operating unit that owns the archived record.
  • LEGAL_ENTITY_ID — the legal entity associated with the record, used by the legal-entity branch of the security predicate.
  • AMOUNT — the reconciled amount for the record.
  • REFERENCE_STATUS, STATUS_FLAG, ACTION_FLAG, CURRENT_RECORD_FLAG, AUTO_RECONCILED_FLAG — state and lifecycle indicators; CURRENT_RECORD_FLAG distinguishes the active version of an archived record from superseded versions, and AUTO_RECONCILED_FLAG identifies rows created by automatic reconciliation.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit context for the process that created or updated the row.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns.

Common Use Cases and Queries

Typical uses include reconciliation history extracts, audit evidence for archived statements, and diagnostics of how a statement line was cleared. The most direct application of the documented metadata is isolating ROI_LINE references, which are exempt from organization filtering:

SELECT car.statement_line_id, car.reference_id, car.je_header_id, car.amount, car.org_id, car.legal_entity_id, car.current_record_flag FROM apps.ce_arch_reconciliations car WHERE car.reference_type = 'ROI_LINE';

To review the current, authoritative archived reconciliation rows for the user's authorized organizations:

SELECT car.statement_line_id, car.reference_type, car.reference_id, car.amount, car.auto_reconciled_flag FROM apps.ce_arch_reconciliations car WHERE car.current_record_flag = 'Y' AND car.org_id = :org_id;

To trace journal-referenced reconciliations, joining to GL for descriptive detail is required, since the view stores only identifiers:

SELECT car.statement_line_id, car.je_header_id, gjh.name, car.amount FROM apps.ce_arch_reconciliations car, apps.gl_je_headers gjh WHERE car.je_header_id = gjh.je_header_id AND car.reference_type = 'JE_LINE' AND car.org_id = :org_id;

When troubleshooting why records appear or disappear for a given user, the effective predicate should be evaluated against CE_SECURITY_PROFILES_V, since the view's result set is the union of rows matching the user's operating-unit/legal-entity authorizations and all rows whose REFERENCE_TYPE is JE_LINE, ROI_LINE, or STATEMENT.