Results for “ben_auto_enrt_term_v”

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

Overview

BEN_AUTO_ENRT_TERM_V is an Oracle E-Business Suite Advanced Benefits (BEN) database view owned by the APPS schema. It exposes rows from the participant enrollment results table that represent automatic enrollments and terminations driven by the benefits eligibility engine rather than by manual or self-service elections. The view applies a restrictive filter so that only collateral or eligibility-derived enrollment results are surfaced: rows where OIPL_ID is null are included only when a matching eligibility record exists in BEN_ELIG_PER_F for the same plan, program, business group, and person, with the enrollment effective start date falling inside the eligibility period.

Because it is a view rather than a table, it carries no storage of its own and reflects the current state of the underlying enrollment and eligibility data at query time. In Oracle EBS 12.1.1 and 12.2.2 it is used primarily for reporting, reconciliation, and integration: providing a stable, denormalized projection of auto-enrollment and auto-termination activity that can be queried without reimplementing the eligibility join logic.

Underlying Base Objects

The documented base objects referenced by the view definition in 12.2.2 are:

• BEN_PRTT_ENRT_RSLT_F (SYNONYM) — the primary participant enrollment results table, aliased PEN in the view text, supplying the bulk of the projected columns.
• BEN_ELIG_PER_F (SYNONYM) — the person-level eligibility table, aliased PEP, used in the correlated EXISTS subquery to confirm that an eligibility record exists for the plan, program, business group, and person.
• BEN_ELIG_PER_OPT_F (SYNONYM) — the person eligibility option table, documented as a referenced object supporting the eligibility determination.
• BEN_OIPL_F (SYNONYM) — the original or prior plan/option list table, referenced to enforce the OIPL_ID IS NULL condition.

The view therefore sits logically on top of the enrollment results and eligibility model. Its predicate requires a valid eligibility match in addition to the enrollment result, so it returns the eligibility-confirmed subset of automatic enrollment and termination activity.

Key Columns

Important columns exposed by the view include:

• PERSON_ID, ASSIGNMENT_ID, BUSINESS_GROUP_ID — identify the participant and their assignment and business group context.
• PL_ID, PGM_ID, PL_TYP_ID, PTIP_ID, OIPL_ID — identify the plan, program, plan type, plan type in program, and original plan list associated with the result.
• ENRT_MTHD_CD — the enrollment method code, distinguishing automatic from other enrollment methods.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the dated range for which the result is valid.
• ENRT_CVG_STRT_DT / ENRT_CVG_THRU_DT — the coverage start and through dates for the enrollment.
• ENRT_OVRIDN_FLAG, ENRT_OVRID_RSN_CD, ENRT_OVRID_THRU_DT — indicate whether an override was applied, its reason, and its through date.
• SSPNDD_FLAG, RPLCS_SSPNDD_RSLT_ID — flag a suspended result and identify the result it replaces.
• PRTT_ENRT_RSLT_STAT_CD, PRTT_IS_CVRD_FLAG, NO_LNGR_ELIG_FLAG — participant result status, coverage indicator, and no-longer-eligible flag.
• BNFT_AMT, BNFT_NNMNTRY_UOM, BNFT_TYP_CD, COMP_LVL_CD — benefit amount, unit of measure, benefit type, and compensation level.
• ORGNL_ENRT_DT, ERLST_DEENRT_DT — original enrollment date and earliest de-enrollment date.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit and concurrency columns.
• PEN_ATTRIBUTE1 through PEN_ATTRIBUTE30, PEN_ATTRIBUTE_CATEGORY — descriptive flexfield attribute columns.

The view also exposes request and program context columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

Common Use Cases and Queries

Typical uses include auditing automatic enrollment and termination activity, reconciling benefit coverage against eligibility, and feeding downstream reporting or integration extracts. A standard query selecting the participant, plan, and enrollment detail is:

• SELECT person_id, pl_id, pgm_id, enrt_mthd_cd, effective_start_date, effective_end_date, enrt_cvg_strt_dt, enrt_cvg_thru_dt, prtt_is_cvrd_flag FROM ben_auto_enrt_term_v WHERE person_id = :p_person_id ORDER BY effective_start_date;
• SELECT pl_id, COUNT(*) FROM ben_auto_enrt_term_v WHERE business_group_id = :p_bg_id AND effective_start_date BETWEEN :p_start AND :p_end GROUP BY pl_id;

Because the view already enforces the eligibility EXISTS predicate and the OIPL_ID IS NULL condition, queries against it return only automatic enrollment and termination results that are backed by a valid person eligibility record, making it well suited to compliance and reconciliation reporting in 12.1.1 and 12.2.2.