Results for “ben_le_clsn_n_rstr”

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

Overview

BEN_LE_CLSN_N_RSTR is a table in the BEN schema (Oracle Advanced Benefits) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is "Life event collision or restrictions." The table records the outcome of processing life events that either collide with one another or are subject to enrollment restrictions — for example, when two life events overlap and only certain plans may be changed, or when an election is blocked, overridden, or made pending during a life event. Each row captures a snapshot-style payload of enrollment, eligibility, rate, and coverage data associated with a specific participant processing result and life event.

From a Data Vault modeling perspective, the metadata's heuristic classification is satellite-leaning, derived from the foreign key structure. This suggests treating the table as a descriptive satellite of a core business entity (the participant/life-event relationship anchored by BEN_PER_IN_LER), rather than as an independent hub or a pure link table. The presence of the BKUP_TBL_ID, BKUP_TBL_TYP_CD, and PER_IN_LER_ID combination in the primary key supports this descriptive, context-carrying interpretation.

Key Information Stored

The physical schema documents 185 columns; the most functionally significant groups are summarized below. The surrogate/composite primary key is BEN_LE_CLSN_N_RSTR_PK (BKUP_TBL_ID, PER_IN_LER_ID, BKUP_TBL_TYP_CD, OBJECT_VERSION_NUMBER). No separate unique business-key index beyond this primary key is documented, so these four columns also serve as the business-key candidates for identifying a row.

• Identity and linkage: BKUP_TBL_ID and BKUP_TBL_TYP_CD identify the backup/restriction record and its type; PER_IN_LER_ID ties the row to the participant's life-event relationship in BEN_PER_IN_LER; OBJECT_VERSION_NUMBER provides optimistic locking.
• Enrollment context: ENRT_BNFT_ID, ENRT_RT_ID, PRTT_ENRT_RSLT_ID, PRTT_ENRT_RSLT_STAT_CD, and PL_ID anchor the benefit, rate, and plan participation result involved.
• Eligibility and election: ELIG_PER_ID, ELIG_PER_ELCTBL_CHC_ID, OPT_ID, ELIG_FLAG, CVRD_FLAG, and INELG_RSN_CD describe eligibility status and any ineligibility reason.
• Coverage and effective dates: EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ENRT_CVG_STRT_DT, ENRT_CVG_THRU_DT, CVG_STRT_DT, and CVG_THRU_DT bound the valid period of the restriction or collision.
• Override and restriction controls: OVRDN_FLAG, ENRT_OVRIDN_FLAG, ENRT_OVRID_RSN_CD, DSTR_RSTCN_FLAG, SSPNDD_FLAG, and NO_LNGR_ELIG_FLAG record whether an election was overridden or restricted.
• Descriptive attributes: ADDL_INSTRN_TXT and the LCR_ATTRIBUTE1–LCR_ATTRIBUTE30 flexible attribute columns hold supplemental information.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.

Common Use Cases and Queries

Typical use cases focus on diagnosing why a participant's life-event election was blocked, overridden, or placed in pending status, and on auditing collision outcomes. A common query joins the table back to BEN_PER_IN_LER to contextualize each restriction by participant and life event:

• Restriction audit: SELECT r.PER_IN_LER_ID, r.BKUP_TBL_TYP_CD, r.INELG_RSN_CD, r.OVRDN_FLAG, r.EFFECTIVE_START_DATE FROM BEN_LE_CLSN_N_RSTR r WHERE r.OVRDN_FLAG = 'Y'.
• Collision reporting by plan: filter on PL_ID, ENRT_BNFT_ID, or ENRT_RT_ID and aggregate by BKUP_TBL_TYP_CD to identify which plan/enrollment combinations most frequently trigger restrictions.
• Pending/overridden elections: queries on PRTT_ENRT_RSLT_STAT_CD, SSPNDD_FLAG, and CVG_PNDG_FLAG support reconciliation during open enrollment cycles.
• Period-based analysis: constrain on EFFECTIVE_START_DATE/EFFECTIVE_END_DATE to report restrictions active within a benefit period.
• Flex attribute extraction: the LCR_ATTRIBUTE columns support custom descriptor reporting where standard columns are insufficient.

Related Objects

The most significant related objects are the foreign-key parents and the eligibility/enrollment entities referenced by the table's many ID columns.

• BEN_PER_IN_LER — joined on BEN_LE_CLSN_N_RSTR.PER_IN_LER_ID = BEN_PER_IN_LER.PER_IN_LER_ID; the primary participant life-event relationship.
• BEN_ELIG_PER_ELCTBL_CHC — joined on ELIG_PER_ELCTBL_CHC_ID; the eligible electable choice context.
• BEN_ENRT_RT — joined on ENRT_RT_ID; enrollment rate details.
• BEN_ENRT_BNFT — joined on ENRT_BNFT_ID; the enrolled benefit record.
• BEN_COMP_LVL_FCTR — joined on COMP_LVL_FCTR_ID; compensation-level factor used in rate or eligibility logic.
• BEN_PRTT_ENRT_RSLT — referenced via PRTT_ENRT_RSLT_ID for participation enrollment results.
• BEN_PL_F / BEN_PL — referenced via PL_ID for the plan involved in the collision or restriction.

These relationships confirm the table's role as a satellite-like record describing restriction and collision events against the participant life-event hub, with enrollment, eligibility, and rate links providing the surrounding business context.