Search Results comp_object




Overview
Explore in AI Chat

Overview

The Oracle EBS view APPS.BEN_DEENROLL_V is a benefits reporting view used to identify participant enrollment results that are no longer actively in force — commonly described as "disenrollments" or terminated coverage. It is defined in the Advanced Benefits (BEN) module and returned as a synonym-owned object under the APPS schema. The view derives its rows exclusively from the participant enrollment result table BEN_PRTT_ENRT_RSLT_F, joined to the plan and program definition tables, and filtered to return enrollments whose coverage has ended and which are not voided in a way that warrants suppression.

The view is typically consumed in benefit extraction routines, reconciliation reports, and interface/outbound feeds where the business requires visibility into coverage that has lapsed. Because it exposes the surrogate identifier PRTT_ENRT_RSLT_ID, it is also useful as a driving query for downstream lookups against activity and coverage tables.

Underlying Base Objects

The metadata documents the view as defined over the following base objects (all exposed as synonyms):

  • BEN_PRTT_ENRT_RSLT_F — the participant enrollment result table, aliased PEN; the primary source of enrollment rows.
  • BEN_PGM_F — program definition, aliased PGM; supplies the program name.
  • BEN_PL_F — plan definition, aliased PLN; supplies the plan name.
  • BEN_OIPL_F and BEN_OPT_F — used in the scalar subquery that resolves the optional/compensation object (referred to as COMP_OBJECT) through the OIPL_ID link.
  • FND_SESSIONS — aliased F; constrains the result to the effective date of the current user session via USERENV('SESSIONID').

The joins to PGM and PLN are outer joins (denoted by the (+) operator) on the respective program and plan identifiers, with additional outer-join date-bracketing on PEN.EFFECTIVE_START_DATE so that a session-effective plan or program version is matched.

Key Columns

  • COMP_OBJECT — a concatenated label of program name, plan name, and (when OIPL_ID is present) the resolved option name, providing a human-readable enrollment description.
  • COVERAGE_END_DATE — sourced from ENRT_CVG_THRU_DT, the date coverage terminates.
  • PERSON_ID and BUSINESS_GROUP_ID — the participant and the business group context for the row.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack validity of the enrollment result record.
  • PRTT_ENRT_RSLT_ID — the primary identifier for the participant enrollment result, suitable for joining back to BEN tables.

The WHERE clause filters out current coverage by excluding rows whose coverage-end date equals 12/31/4712, and it excludes rows at the PLANFC and PLANIMP compensation levels. Status logic retains null-status rows and voided rows only where coverage start exceeds coverage end.

Common Use Cases and Queries

Typical uses include reporting lapsed participant coverage for a business group, reconciliation of terminated enrollments, and outbound feeds. A representative query:

  • SELECT person_id, comp_object, coverage_end_date FROM apps.ben_deenroll_v WHERE business_group_id = :p_bg ORDER BY coverage_end_date;
  • SELECT prtt_enrt_rslt_id, comp_object FROM apps.ben_deenroll_v WHERE coverage_end_date BETWEEN :p_from AND :p_to;

Because the view depends on FND_SESSIONS and USERENV('SESSIONID'), it returns rows only when executed within a valid, dated session context. Reports or interfaces that call it outside such a context may return no data and should establish the appropriate session date before querying.