Results for “enrt_cd_m”

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

Overview

BENBV_LER_CHG_OIPL_ENRT_V is an APPS-owned, read-only database view in the Oracle Advanced Benefits (BEN) product. Its documented purpose is to identify the impact of a life event reason (LER) on the election changes a person may or may not make for an option in a plan. The view answers a specific configuration question that recurs throughout benefits setup and testing: given a life event reason and a plan option, does the participant have latitude to elect, default, or automatically enroll, and can a currently enrolled election carry forward?

Because it is a reporting view rather than a base table, it exposes the configuration defined in the underlying life event change rule table in a flattened, security-filtered form suitable for extracts, OTBI-style ad hoc reporting, and integration or conversion validation. The view name follows Oracle's naming convention for BEN views (BENBV_ prefix), which signals that the object is intended for query and reporting use rather than transactional DML.

Underlying Base Objects

The view is defined over a single base object, BEN_LER_CHG_OIPL_ENRT_F, referenced through a synonym, and additionally calls the HR_BIS package for translated lookup decoding. The LER_CHG_OIPL_ENRT_F table stores the intersection of a life event reason (LER_ID), a plan option (OIPL_ID), and the enrollment rules that govern the participant's ability to change elections on that option when the LER is processed.

The view text applies two important transformations on top of the base table. First, it constrains rows by business group using the NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, LOP.BUSINESS_GROUP_ID) predicate, so only data for the session user's security profile business group is returned. Second, the view is declared WITH READ ONLY. Both attributes are consistent with the reporting posture of the view and with Oracle's multi-tenant data security model in EBS 12.1.1 and 12.2.2.

Key Columns

• Date-enabled keys: EFFECTIVE_START_DATE and EFFECTIVE_END_DATE make this a date-tracked (datetrack) view; queries should normally filter or join with a date to obtain the version in force.
• Rule foreign keys: ENRT_RL, DFLT_ENRT_RL, and AUTO_ENRT_MTHD_RL link to the enrollment, default enrollment, and automatic enrollment rules that drive behavior. These are typically joined to rule definitions for full detail.
• AUTO_ENRT_FLAG: This is the column users search for. It is a YES/NO indicator controlling whether automatic enrollment applies to the option for the given life event reason. The following column, AUTO_ENRT_FLAG_M, is the decoded meaning produced by HR_BIS.BIS_DECODE_LOOKUP('YES_NO', ...).
• ENRT_CD / DFLT_ENRT_CD: Coded values describing the enrollment and default enrollment treatment, each paired with a companion decoded column (the _M columns) from the lookups BEN_ENRT and BEN_DFLT_ENRT.
• CRNT_ENRT_PRCLDS_CHG_FLAG: Indicates whether a current enrollment precludes change for the option under this life event reason.
• STL_ELIG_CANT_CHG_FLAG: Marks options where eligibility is still being evaluated and change is not permitted.
• DFLT_FLAG: Indicates whether the option is defaulted.
• Descriptive flexfield: The literal column "_DF:LOP" exposes the BEN_LER_CHG_OIPL_ENRT_F descriptive flexfield context.
• Identity and WHO columns: LER_CHG_OIPL_ENRT_ID, BUSINESS_GROUP_ID, OIPL_ID, LER_ID, plus CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY.

Common Use Cases and Queries

Typical uses include auditing which options are automatically enrolled for a life event reason, verifying configuration after a patch or setup migration, and feeding downstream extracts. Because the view is date-tracked and filtered by business group security, a query should select the version effective on a given date.

To identify options impacted by a life event reason and whether auto enrollment is switched on:

• SELECT oipl_id, ler_id, auto_enrt_flag, auto_enrt_flag_m, enrt_cd_m FROM apps.benbv_ler_chg_oipl_enrt_v WHERE ler_id = :p_ler_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To review all change restrictions for an option:

• SELECT oipl_id, ler_id, crnt_enrt_prclds_chg_flag_m, stl_elig_cant_chg_flag_m, dflt_flag_m FROM apps.benbv_ler_chg_oipl_enrt_v WHERE oipl_id = :p_oipl_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the view is READ ONLY and limited by business group security, it should never be used for DML. For questions that require rule-level detail, join the ENRT_RL, DFLT_ENRT_RL, and AUTO_ENRT_MTHD_RL columns back to their respective rule tables.