Results for “ben_ler_chg_ptip_enrt_f”

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

Overview

BEN_LER_CHG_PTIP_ENRT_F is a transactional configuration table in the Oracle Advanced Benefits (BEN) module. It stores the rules that determine which plan types (PTIP) are available, defaulted, or changed within a program enrollment when a participant experiences a qualifying life event (LER). In Oracle EBS 12.1.1 and 12.2.2 the table is registered in the BEN schema with a status of VALID and a documented physical schema of 52 columns. It is a date-tracked (non-dated-effective "F" suffix) table, meaning records carry EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns used to version configuration changes over time consistent with Oracle HRMS date-tracking conventions.

The mined relationship metadata classifies this object heuristically as standalone (no foreign key dependencies materialized in the mined structure). For data vault modeling purposes, it is best treated as a satellite candidate attached to a life-event/plan-type business key, since it captures descriptive attributes about enrollment behavior tied to a parent life event rather than acting as a pure hub or link.

Key Information Stored

The primary key is BEN_LER_CHG_PTIP_ENRT_PK, composed of LER_CHG_PTIP_ENRT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This is the surrogate identifier; the combination of life event and plan type context forms the effective business key. The most significant columns include:

• LER_CHG_PTIP_ENRT_ID — surrogate primary key identifier.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-tracked versioning bounds.
• LER_ID — the life event definition that triggers the enrollment change.
• PTIP_ID — the plan type in program associated with the rule.
• CRNT_ENRT_PRCLDS_CHG_FLAG — whether the current enrollment precludes the change.
• STL_ELIG_CANT_CHG_FLAG — whether still-eligible participants cannot change.
• DFLT_FLAG — default indicator for the plan type entry.
• DFLT_ENRT_CD / DFLT_ENRT_RL — default enrollment code and rule.
• ENRT_CD / ENRT_RL / ENRT_MTHD_CD — enrollment code, rule, and method.
• TCO_CHG_ENRT_CD — code governing change per "time change occurred".
• BUSINESS_GROUP_ID — partition by business group.
• OBJECT_VERSION_NUMBER — optimistic locking (12.2.x audit column).
• LAST_UPDATE_DATE / LAST_UPDATED_BY / CREATION_DATE / CREATED_BY — standard audit columns.

The 30 LCT_ATTRIBUTE columns are open descriptive flexfield segments holding implementation-specific qualifiers.

Common Use Cases and Queries

Typical use cases include life-event configuration reporting, troubleshooting why a plan type does or does not default during a life event, and validating enrollment-change rules before a benefits open enrollment cycle. A representative query resolves the configuration for a given life event:

• SELECT p.LER_ID, p.PTIP_ID, p.DFLT_FLAG, p.ENRT_CD, p.DFLT_ENRT_CD FROM BEN.BEN_LER_CHG_PTIP_ENRT_F p WHERE p.LER_ID = :ler_id AND SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE;
• Reporting by business group: filter on BUSINESS_GROUP_ID and current effective dates.
• Auditing: compare OBJECT_VERSION_NUMBER and LAST_UPDATE_DATE to detect stale configuration.

Related Objects

The mined relationship data classifies this object as standalone, so no direct foreign keys are materialized. Logical associations based on documented columns include BEN_LER_F (via LER_ID) and BEN_PTIP_F or BEN_PL_TYP_F (via PTIP_ID), which supply the life event and plan type definitions. Enrollment results flow into BEN_ENRT_BNFT_F and BEN_ENRT_PER_F, while program configuration referencing plan types resides in BEN_PGM_F / BEN_PGM_ENRT_F. Change codes referenced by ENRT_CD and TCO_CHG_ENRT_CD correspond to BEN_ENRT_CD_F. DATE-tracked join columns (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) must be applied consistently across these related tables.