Results for “ben_ler_chg_dpnt_cvg”

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

Overview

BEN_LER_CHG_DPNT_CVG is a reporting view in the Oracle EBS Advanced Benefits (BEN) module, owned by the APPS schema. It is designated in the ETRM metadata as a retrofitted object, indicating it was re-engineered to conform to the standard Oracle date-tracked, multi-org aware view pattern. The view presents a point-in-time snapshot of "Life Event Rule – Change Dependent Coverage" configuration records. In Advanced Benefits, Life Event Rules determine how a participant's enrollment options are modified when a qualifying life event occurs (for example, marriage, birth, or divorce). The DPNT_CVG component of the rule governs how dependent coverage is added, removed, or altered as a result of that event.

The view implements the effective-dating filter characteristic of Oracle EBS date-tracked objects: rows are only returned when the session effective date falls between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. This makes the view an appropriate source for concurrent programs, reports, and integration interfaces that must resolve the currently active configuration rather than the full historical record set.

Underlying Base Objects

The view is defined over the following documented objects:

• BEN_LER_CHG_DPNT_CVG_F (SYNONYM) — the "_F" suffix denotes this as the base (date-tracked) table holding all effective-dated versions of change-dependent-coverage life event rule records.
• FND_SESSIONS (SYNONYM) — the Applications session table, used to resolve the effective date of the current user session.

The view text selects all columns from BEN_LER_CHG_DPNT_CVG_F and applies the effective date predicate via a correlated subquery against FND_SESSIONS, matching SESSION_ID to USERENV('SESSIONID') and comparing EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to the returned EFFECTIVE_DATE. No joins to other BEN tables are performed; the view is a filtered projection of the base table only. Columns are aliased with the LDC prefix in the view definition.

Key Columns

The view exposes the following principal columns, along with a set of descriptive flexfield attributes (LDC_ATTRIBUTE_CATEGORY and LDC_ATTRIBUTE1 through LDC_ATTRIBUTE30) and audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER).

• LER_CHG_DPNT_CVG_ID — primary key identifier for the change-dependent-coverage rule record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity window for each row.
• PL_ID, PGM_ID, BUSINESS_GROUP_ID — identify the plan, program, and business group (multi-org context) to which the rule applies.
• LER_ID — the parent Life Event Rule identifier linking this coverage change to a specific life event reason.
• PTIP_ID — the plan type or program-level element to which the coverage change applies.
• ADD_RMV_CVG_CD — code indicating whether coverage is added, removed, or otherwise modified in response to the event.
• CVG_EFF_STRT_CD / CVG_EFF_END_CD — codes determining the effective start and end date of the resulting coverage.
• CVG_EFF_STRT_RL / CVG_EFF_END_RL — rule identifiers resolving how the coverage start and end dates are derived.
• LER_CHG_DPNT_CVG_RL / LER_CHG_DPNT_CVG_CD — rule and code columns qualifying the change-dependent-coverage behavior.

Common Use Cases and Queries

Typical use cases include diagnosing why a dependent's coverage was added or removed during life event processing, validating rule configuration prior to a plan year rollover, and extracting active rules for data migration or reconciliation. Because the view is session-date filtered, the query must be executed within an initialized EBS session (for example, from SQL*Plus after running FND_GLOBAL.APPS_INITIALIZE) for FND_SESSIONS to return the appropriate effective date.

A representative query retrieving all currently active change-dependent-coverage rules for a given life event rule:

• SELECT LER_CHG_DPNT_CVG_ID, LER_ID, PL_ID, PGM_ID, ADD_RMV_CVG_CD, CVG_EFF_STRT_CD, CVG_EFF_END_CD FROM BEN_LER_CHG_DPNT_CVG WHERE LER_ID = :p_ler_id;

To review the audit trail of a specific configuration record:

• SELECT LER_CHG_DPNT_CVG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY FROM BEN_LER_CHG_DPNT_CVG WHERE PL_ID = :p_pl_id ORDER BY EFFECTIVE_START_DATE;

Note that for historical analysis spanning past effective dates, the base table BEN_LER_CHG_DPNT_CVG_F should be queried directly, since the view intentionally restricts output to the current session effective date.