Results for “ben_clpse_lf_evt_d”

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

Overview

BEN_CLPSE_LF_EVT_D is a denormalized reporting view owned by the APPS schema within the Oracle Advanced Benefits (BEN) module. It presents collapse life event definitions — the configurable rules that determine how multiple, closely spaced life events are collapsed into a single processing event for benefits eligibility and enrollment purposes. The view exists primarily to translate internal surrogate keys and audit identifiers into a form suitable for concurrent programs, extract reports, and integration interfaces, so that external consumers can retrieve life-event collapse parameters without joining to the underlying date-tracked (datetracked) base table directly.

In EBS 12.1.1 and 12.2.2 the object remains a VALID view in the APPS schema. Its principal utility is read access: it is not a transactional entity, and it should never be used as a DML target. The name suffix "_D" follows the Oracle Applications convention of denoting a descriptive or reporting layer realized as a view rather than a physical table.

Underlying Base Objects

The documented view text drives the object from BEN_CLPSE_LF_EVT_F, the datetracked ("_F" for the current/future effective row segment) base table that stores collapse life event definitions. The view exposes the base table's ROWID as ROW_ID, giving consumers a stable physical row locator, and includes the effective dating columns and the tolerance-days attribute carried on the base table.

The definition additionally joins FND_USER using an outer join (FUSER.USER_ID (+) = CLP.LAST_UPDATED_BY). This resolves the numeric LAST_UPDATED_BY identifier into a usable user context and ensures that rows are still returned when the updating user record no longer exists or the identifier is null. The documented ETRM metadata also lists BEN_LER_F, FF_FORMULAS_F, and the HR_GENERAL package as referenced base objects, reflecting the wider collapse-rule configuration (life event reasons and associated formula-based logic) that supports this feature area, together with the HR_GENERAL date-tracking utility used to maintain effective-dated rows.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_CLPSE_LF_EVT_F row, used as the view's row identifier.
• CLPSE_LF_EVT_ID — the primary surrogate key of the collapse life event definition; this is the column most frequently searched for by name (clpse_lf_evt_id) and is the join key to related BEN configuration tables.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating bounds that control when a given collapse rule is in force; queries against current data must filter on SYSDATE falling between these dates.
• TLRNC_DYS_NUM — the tolerance number of days; defines the window within which qualifying life events are considered duplicates and collapsed into one event.
• LAST_UPDATE_DATE — audit timestamp of the most recent change.
• LAST_UPDATED_BY — the numeric user identifier of the last updater, resolvable through FND_USER.

Common Use Cases and Queries

The view is typically queried when auditing or reporting on life-event collapse configuration, when diagnosing why a participant's overlapping life events were or were not collapsed, or when migrating collapse rules between environments. A representative query retrieving all currently effective collapse definitions follows:

SELECT c.Row_ID, c.Clpse_Lf_Evt_ID, c.Tlrnc_Dys_Num, c.Effective_Start_Date, c.Effective_End_Date FROM apps.BEN_CLPSE_LF_EVT_D c WHERE SYSDATE BETWEEN c.Effective_Start_Date AND c.Effective_End_Date ORDER BY c.Clpse_Lf_Evt_ID;

To trace the identity behind a definition's audit columns, join or select through the resolved user field:

SELECT c.Clpse_Lf_Evt_ID, c.Tlrnc_Dys_Num, c.Last_Update_Date, u.User_Name FROM apps.BEN_CLPSE_LF_EVT_D c, apps.FND_User u WHERE u.User_ID = c.Last_Updated_By AND c.Clpse_Lf_Evt_ID = :p_clpse_lf_evt_id;

Because the view is a reporting projection, it should be combined with BEN_LER_F and FF_FORMULAS_F when a full picture of collapse logic is required, and it should be queried through the Apps database user to guarantee correct synonym and security-group resolution.