Results for “coverage_end_date”

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

Overview

BEN_DEENROLL_V is a read-only database view owned by the APPS schema within Oracle E-Business Suite Advanced Benefits (BEN). It is flagged as VALID in the ETRM metadata and is exposed as a synonym across the BEN product schema. The view presents a filtered, human-readable projection of participant enrollment results, specifically targeting enrollments that are scheduled for or in the process of disenrollment. The naming convention — DEENROLL, for "disenrollment" — reflects this focus.

The view joins participant enrollment result rows against plan, program, and option definition tables to produce a concatenated descriptive string (COMP_OBJECT) that identifies the specific compensation object associated with the enrollment. Only enrollments whose coverage-through date is not the Oracle high date (12/31/4712) and whose enrollment status code is null are returned, which effectively isolates enrollment records that have a defined termination or coverage end point rather than open-ended active enrollments. Records at PLANFC (plan flex credit) and PLANIMP (plan implicit) compensation levels are excluded.

Because it filters on the current FND session effective date through the FND_SESSIONS table, the view is session-context sensitive and returns data as of the effective date of the active user session, which aligns with Oracle Advanced Benefits' date-tracked processing model.

Underlying Base Objects

The ETRM references the following base objects, all accessed via synonyms from the APPS schema:

• BEN_PRTT_ENRT_RSLT_F — the primary date-tracked table of participant enrollment results; the view's driving table (alias PEN).
• BEN_PGM_F — program definitions (alias PGM), outer-joined on PGM_ID.
• BEN_PL_F — plan definitions (alias PLN), outer-joined on PL_ID.
• BEN_OIPL_F — option-in-plan definitions (alias OIPL), referenced in the scalar subquery.
• BEN_OPT_F — option definitions (alias OPT), referenced in the scalar subquery.
• FND_SESSIONS — the Forms session table, used to obtain the current session identifier and effective date via USERENV('SESSIONID').

The outer joins (+) on program and plan allow enrollments that lack a matching definition row for the session effective date to remain in the result set, with null name components in the concatenated string. The compensation object subquery is correlated on PEN.OIPL_ID and constrained by the session effective date against both the option-in-plan and option definition effectivity ranges.

Key Columns

• COMP_OBJECT — concatenation of program name, plan name, and the resolved option name (when an OIPL_ID exists); the primary descriptive identifier for the enrollment.
• COVERAGE_END_DATE — alias for ENRT_CVG_THRU_DT; the date on which coverage terminates.
• PERSON_ID — the participant's person identifier, linking to PER_PEOPLE_F.
• BUSINESS_GROUP_ID — the HR business group owning the enrollment.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked effectivity range of the enrollment result row itself.
• PRTT_ENRT_RSLT_ID — the primary key of the underlying enrollment result, useful for drill-down joins.

Common Use Cases and Queries

Typical uses include identifying participants with upcoming coverage terminations for notifications, reconciliation of disenrollment processing, and benefit statement or audit extracts. A representative query:

• SELECT person_id, comp_object, coverage_end_date FROM apps.ben_deenroll_v WHERE coverage_end_date BETWEEN SYSDATE AND SYSDATE + 30 ORDER BY coverage_end_date;
• SELECT business_group_id, COUNT(*) FROM apps.ben_deenroll_v GROUP BY business_group_id;
• SELECT * FROM apps.ben_deenroll_v WHERE person_id = :p_person_id;

Because results depend on the active FND session effective date, queries executed outside a Forms session context typically return no rows; programmatic callers should establish the appropriate session or query the base enrollment table directly with explicit date criteria.