Search Results ben_ler_chg_pgm_enrt_f




Overview
Explore in AI Chat

Overview

BEN_LER_CHG_PGM_ENRT_F is a date-tracked (datetrack) table in the BEN schema of Oracle Advanced Benefits (Oracle E-Business Suite 12.1.1 and 12.2.2). Its documented purpose is to determine program enrollment outcomes when a participant experiences a life event. Specifically, the table stores the configuration that governs how a life event (LER) affects enrollment in a benefits program (PGM) — including the enrollment method, default enrollment rules, and whether the participant retains the ability to change program enrollment after the event is processed.

In Oracle Advanced Benefits, life events drive a participant through a Life Event Enrollment (LEE) process. During that process the application evaluates change rules that determine which programs, plans, and options the participant may or must enroll in, drop from, or default into. BEN_LER_CHG_PGM_ENRT_F is the physical storage of those program-level change rules for a given life event, effective-dated via EFFECTIVE_START_DATE and EFFECTIVE_END_DATE so that rule configurations are preserved historically.

Heuristically, the mined FK structure classifies this object as a standalone format. From a Data Vault modeling perspective this is a satellite-style object: it holds descriptive, effective-dated attributes describing the relationship between a life event and a program, rather than acting as a hub or link between independent business keys.

Key Information Stored

The primary key is BEN_LER_CHG_PGM_ENRT_F_PK, composed of LER_CHG_PGM_ENRT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. LER_CHG_PGM_ENRT_ID is the surrogate identifier for the rule row, while the effective date range supplies the datetrack dimension required for historical and future-dated configuration.

Business-key candidates documented via unique indexes include the same composite (LER_CHG_PGM_ENRT_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). The most significant descriptive columns include:

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include identifying which programs a given life event controls, and verifying default enrollment behavior. A representative query joins the rule to its program and life event context:

  • Listing rules for a life event: SELECT ler_chg_pgm_enrt_id, pgm_id, enrt_cd, enrt_mthd_cd, enrt_rl FROM ben_ler_chg_pgm_enrt_f WHERE ler_id = :p_ler_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Checking default enrollment configuration for a program: filter on PGM_ID and DFLT_ENRT_CD to confirm the fallback enrollment when participants do not elect.
  • Effective-dating audits: compare multiple datetracked rows for the same LER_CHG_PGM_ENRT_ID to trace configuration changes over time.
  • Life event enrollment rule analysis: aggregate rows by ENRT_MTHD_CD to profile how often elect/waive/default methods are used per business group.

Because the table uses datetrack, queries should always constrain on the effective date range to obtain the rule version applicable on the date of interest.

Related Objects

  • BEN_LER_F — the life event definition table; join on LER_ID to resolve the event name and type.
  • BEN_PGM_F — the benefits program definition; join on PGM_ID to retrieve program name and description.
  • BEN_LER_CHG_PGM_ENRT_R — the rule detail table; related through LER_CHG_PGM_ENRT_ID, holding rule conditions associated with each enrollment rule.
  • BEN_LER_CHG_ENRT_CTFN_F — certification requirements tied to life event enrollment changes.
  • BEN_LER_PL_CHG_CVRD_F — plan-level change rules evaluated downstream of program enrollment during the LEE process.
  • BEN_PGM_ENRT_RL_F — program enrollment rule definitions referenced by ENRT_RL expressions.
  • BEN_LER_CHG_PGM_ENRT_F_PK — the primary key index used for direct surrogate-key access.

These objects are typically accessed together in PL/SQL or OAF-based Life Event Enrollment flows, in which program change rules are loaded and evaluated to build the participant's enrollment options.