Results for “ben_ler_f”

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

Overview

BEN_LER_F is the Oracle Advanced Benefits (BEN) foundation table that stores Life Event Reason definitions. A life event reason represents a specific, configurable cause that can trigger a life event — for example, a marriage, birth of a child, divorce, or change in employment status — and drives eligibility recalculation, plan enrollment opportunities, and coverage adjustments within a benefits program. Each row represents one life event reason, defined and owned by a specific business group, and is effective-dated so that historical configurations can be retained while current processing rules evolve.

The primary key of the table is BEN_LER_F_PK, composed of LER_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The ETRM documentation classifies this object heuristically as a standalone Data Vault model, meaning it has been mined as neither a pure hub, link, nor satellite. In practice, LER_ID behaves as the natural business key of the life event reason, while the effective dates provide the temporal (satellite-like) dimension; the table may therefore be treated as a hub of LER_ID combined with an effective-dated satellite holding descriptive attributes.

Key Information Stored

The table contains 62 documented columns in the 12.2.2 physical schema. The most significant columns are:

• LER_ID — Surrogate/business identifier of the life event reason; part of BEN_LER_F_PK.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Effective-dating range controlling which configuration is active; both are part of the primary key.
• NAME and SHORT_NAME / SHORT_CODE — User-facing and short display identifiers for the reason.
• BUSINESS_GROUP_ID — Owning business group, enforcing multi-tenant separation of benefit configurations.
• LER_ATTRIBUTE_CATEGORY and LER_ATTRIBUTE1…LER_ATTRIBUTE30 — Descriptive flexfield (DFF) segments for customer-defined extension attributes.
• LER_STAT_CD — Status code governing whether the reason is active for processing.
• TYP_CD and LF_EVT_OPER_CD — Event type and operational handling code that determine how the reason is processed.
• CM_APLY_FLAG, OVRIDG_LE_FLAG, QUALG_EVT_FLAG, CK_RLTD_PER_ELIG_FLAG — Flags controlling whether the reason applies to compensation, overrides existing life events, qualifies as a qualifying event, and checks related person eligibility.
• LER_EVAL_RL — Evaluation rule regulating how the life event is evaluated.
• WHN_TO_PRCS_CD, TMLNS_PERD_CD, TMLNS_EVAL_CD, TMLNS_PERD_RL, TMLNS_DYS_NUM — Codes and rules governing when to process and the timeliness period for the event.
• OCRD_DT_DET_CD, PTNL_LER_TRTMT_CD, SLCTBL_SLF_SVC_CD, SS_PCP_DISP_CD — Occurred-date determination, potential treatment, self-service selectability, and participant display controls.
• LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, OBJECT_VERSION_NUMBER — Standard WHO/audit and concurrency columns.

The unique index BEN_LER_F_PK is the business-key candidate. No surrogate-only key exists that excludes the effective dates, so any lookup by LER_ID must be constrained on the effective range to return a single row.

Common Use Cases and Queries

Functional analysts, benefits administrators, and reporting developers query BEN_LER_F to list available life event reasons, drive prompt lists in self-service, and resolve reason codes to display names. A typical query returns the current effective row:

• Active reasons for a business group: SELECT ler_id, name, short_name FROM ben_ler_f WHERE business_group_id = :bg AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date AND ler_stat_cd = 'A';
• Reason lookup by ID: join LER_ID with an effective-date predicate so that effective-dated versions are correctly resolved.
• Reporting join: BEN_LER_F is joined to life event transaction tables on LER_ID to label each captured event with its reason name for benefit event extracts and reconciliation reports.
• Configuration auditing: extract LER_ATTRIBUTE1…30 with the DFF category to review customer-specific extensions.

Because the table carries no foreign-key links in the metadata, joins to dependent objects are performed on LER_ID rather than enforced referential constraints.

Related Objects

The following objects most significantly depend on or relate to BEN_LER_F, joined on LER_ID:

• BEN_LER_F_RL / BEN_LER_R_USG — Life event reason usage and rule tables that reference the reason for eligibility processing.
• BEN_LER_OPT_F — Life event reason options, keyed by LER_ID.
• BEN_LF_EVENTS_F — Life events facing rows that reference the reason that triggered them.
• BEN_LER_ACTN_R — Actions associated with the reason, applied when the event occurs.
• BEN_PTNL_LER_FOR_ELIG_F — Potential life event eligibility definitions using LER_ID.
• BEN_ELIG_* tables — Eligibility configuration joined back to the reason through usage rules.
• BEN_LER_F_VL / _TL — Translated views providing the language-dependent display name.
• FFS / BEN APIs — Advanced Benefits setup APIs that create and maintain life event reasons through standard interfaces.