Results for “enrt_cvg_strt_dt_meaning”

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

Overview

BEN_ENRT_PERD_FOR_PL_D is a validation and reporting view owned by the APPS schema within the BEN (Advanced Benefits) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents denormalized enrollment period information associated with a benefits plan, resolving internal foreign keys into user-readable lookup meanings and formula names. The view is an "_D" (description/display) companion to the underlying "_F" base table, BEN_ENRT_PERD_FOR_PL_F. Its primary role is to expose enrollment period rules that govern when participants may enroll in a specific plan, translating coded values into descriptive text suitable for forms, concurrent program output, and ad-hoc reporting. Because it derives display values from HR_LOOKUPS and FF_FORMULAS_F rather than storing them redundantly, the view is a canonical source for reporting on enrollment period configuration without re-implementing lookup logic in each query.

Underlying Base Objects

The view is defined over several documented base objects, joined primarily through outer joins so that incomplete configuration records still appear. The central table is BEN_ENRT_PERD_FOR_PL_F, keyed by ENRT_PERD_FOR_PL_ID, which links an enrollment period to a plan. Supporting objects include:

• BEN_ENRT_PERD_FOR_PL_F — the driving base table holding the plan-specific enrollment period definitions and all foreign keys.
• BEN_ENRT_PERD — supplies the actual start and end dates (STRT_DT, END_DT) of the enrollment period.
• BEN_PL_F — provides the plan name via PL.NAME.
• HR_LOOKUPS — accessed four times to resolve the enrollment coverage start/end codes and the rate start/end codes into their MEANING values, filtered by lookup types BEN_ENRT_CVG_STRT, BEN_ENRT_CVG_END, BEN_RT_STRT, and BEN_RT_END.
• FF_FORMULAS_F — accessed four times to resolve formula IDs into FORMULA_NAME, with date-effective joins against the record's EFFECTIVE_START_DATE.
• FND_USER — resolves LAST_UPDATED_BY into a user identity.

The ETRM listing also references HR_API (PACKAGE) as an associated object, though the view text itself relies on the tables above.

Key Columns

• ROW_ID — the ROWID of the base table row, useful for direct row access and updates.
• ENRT_PERD_FOR_PL_ID — primary key identifying the enrollment-period-to-plan association.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective range of the configuration record.
• ENRT_CVG_STRT_DT.MEANING / ENRT_CVG_END_DT.MEANING — descriptive coverage start and end rules.
• RT_STRT_DT.MEANING / RT_END_DT.MEANING — descriptive rate start and end rules.
• FORMULA_NAME columns — the formulas that derive coverage and rate dates.
• Concatenated date string — the enrollment period start and end dates formatted as DD-MON-YYYY for display.
• PL.NAME — the associated plan name.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns.

Common Use Cases and Queries

Typical uses include auditing which plans have enrollment periods defined, verifying coverage and rate date rules, and reconciling configuration before open enrollment. A representative query:

• SELECT plan_name, enrollment_period, enrt_cvg_strt_meaning FROM ben_enrt_perd_for_pl_d WHERE effective_start_date = (SELECT MAX(effective_start_date) FROM ben_enrt_perd_for_pl_d);
• Filter by plan to review active rules: SELECT * FROM ben_enrt_perd_for_pl_d WHERE pl_name = :plan AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
• Audit changes: SELECT * FROM ben_enrt_perd_for_pl_d ORDER BY last_update_date DESC;

The view should be treated as read-only, as it aggregates date-effective rows across multiple base tables.