Search Results enrollment_code

Overview

The BEN_LER_CHG_PLIP_ENRT_D view is a date-tracked reporting object within Oracle Advanced Benefits (BEN), owned by the APPS schema. Its documented description — "Life Event Plan in Program Enrollment Date Track View" — indicates that it exposes the enrollment configuration a participant receives when a specific life event triggers a change to a plan held within a program. In the Oracle EBS 12.1.1 and 12.2.2 data model, the _D suffix on a BEN object conventionally signals a date-tracked (effective-dated) view that flattens the multiple effective-dated segments of the underlying _F table into a single logical row for that record's effective range. This view is therefore intended for reporting, extract, and integration purposes rather than for transactional maintenance, and it allows administrators and downstream systems to resolve what enrollment method, default, and enrollment restrictions apply for a life event/plan-in-program combination at a given point in time.

Underlying Base Objects

The view is defined over the following documented base objects:

• BEN_LER_CHG_PLIP_ENRT_F — the driving table holding the life event change record for plan-in-program enrollment, aliased LPR.
• BEN_LER_F — the life event definition, aliased LER, joined on LER_ID.
• BEN_PLIP_F — the plan-in-program definition, aliased PLIP, joined on PLIP_ID.
• BEN_PL_F — the plan definition, aliased PL, joined on PL_ID through PLIP.
• FND_USER — aliased FUSER, outer-joined on LAST_UPDATED_BY to resolve the updating user name.
• HR_GENERAL — a database package whose DECODE_LOOKUP function translates stored lookup codes into their display meanings.

All joins are outer joins, and the date-tracking logic ensures that the plan-in-program and life event rows overlap the enrollment record's effective dates (each EFFECTIVE_START_DATE BETWEEN its parent's start and end).

Key Columns

• LER_CHG_PLIP_ENRT_ID — primary identifier of the change record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked effective range of the record.
• PLAN_NAME and LER_NAME — decoded plan (PL.NAME) and life event (LER.NAME) descriptions.
• ENROLLMENT_CODE — the decoded value of ENRT_CD via the BEN_ENRT lookup; this is the column most frequently referenced by users searching on "enrollment_code".
• ENRT_METHOD_CODE — decoded enrollment method from BEN_ENRT_MTHD.
• TCO_CHG_ENRT_CD — decoded "change enrollment" code from BEN_TCO_CHG_ENRT.
• DEFAULT_ENRT_CD and DEFAULT_ENRT_RULE — decoded default enrollment code (BEN_DFLT_ENRT) and its rule.
• ENRT_RULE, AUTO_ENRT_MTHD_RL — enrollment and automatic enrollment method rules.
• CRNT_ENRT_PRCLDS_CHG_FLAG, DEFAULT_FLAG, STL_ELIG_CANT_CHG_FLAG — Yes/No flags indicating whether current enrollment precludes change, whether the row defines a default, and whether eligibility cannot be changed.
• LAST_UPDATE_DATE and LAST_UPDATED_BY — audit columns, with the user name exposed through FUSER.USER_NAME (displayed as the user name in the view text, documented internally as LAST_UPDATED_BY).

Common Use Cases and Queries

The view supports auditing and reconciliation of life event enrollment behavior, migration of enrollment configuration between environments, and reporting on which enrollment method or default applies to a given plan-life event combination. A typical query resolving enrollment codes by date is:

• SELECT LER_CHG_PLIP_ENRT_ID, PLAN_NAME, LER_NAME, ENROLLMENT_CODE, ENRT_METHOD_CODE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE FROM APPS.BEN_LER_CHG_PLIP_ENRT_D WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
• SELECT ENROLLMENT_CODE, COUNT(*) FROM APPS.BEN_LER_CHG_PLIP_ENRT_D GROUP BY ENROLLMENT_CODE; — to inventory enrollment code usage.
• SELECT PLAN_NAME, LER_NAME, DEFAULT_FLAG, DEFAULT_ENRT_CD FROM APPS.BEN_LER_CHG_PLIP_ENRT_D WHERE DEFAULT_FLAG = 'Yes'; — to identify defaulted enrollments.

Always apply the effective date range in the predicate to obtain the correct date-tracked row, and restrict to the APPS schema with appropriate privileges.