Results for “lf_evt_ocrd_dt”

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

Overview

BEN_REL_PER_LIFE_EVTS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) product family. It presents the set of potential life events and related-person eligibility records associated with persons who act as contacts within the benefits relationship model. In practical terms, the view surfaces individuals for whom a life event has been detected or requires processing, together with the relationship context in which that event applies.

The view is a session-aware, security-filtered construct. Its definition joins FND_SESSIONS against the current session and uses the effective date returned by USERENV('SESSIONID') to restrict person, relationship, and life event records to those valid on the session effective date. This makes the view naturally aligned with the Oracle EBS date-tracked (datetrack) architecture and suitable for transaction-level forms and concurrent reports that must respect the user's effective date and security profile.

Underlying Base Objects

The documented base objects referenced by this view are:

• BEN_LER_F (synonym) — the life event reason definition table, supplying the life event name and identifier.
• BEN_PTNL_LER_FOR_PER (synonym) — the potential life event for person table, supplying the occurrence date and the processing status of the life event.
• PER_CONTACT_RELATIONSHIPS (synonym) — the relationship table that links a contact person to a person, filtered by PERSONAL_FLAG = 'Y' and by the date range defined by DATE_START and DATE_END.
• PER_PEOPLE_F (view) — the person view supplying the full name and the effective date bounds of the person record.
• FND_SESSIONS (synonym) — provides the session effective date used throughout the WHERE clause.
• HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY (packages) — supporting packages that underpin the person view and name/security derivations.

The join structure is driven by the contact person. The view matches PIL.PERSON_ID and PIL.BUSINESS_GROUP_ID to the corresponding CSR values, and matches PIL.LER_ID to LER.LER_ID, with all business group identifiers required to agree. Life event reasons are restricted to those effective on the session date, and the potential life event statuses are limited to the detected and unprocessed categories.

Key Columns

The view exposes the following columns:

• CONTACT_PERSON_ID — the person identifier for the contact relationship record. Note the documentation lists the view text column as PERSON_ID while the column listing shows CONTACT_PERSON_ID; both refer to the same person key at the contact relationship level.
• FULL_NAME — the formatted person name sourced from PER_PEOPLE_F.
• NAME — the life event reason name from BEN_LER_F.
• LER_ID — the life event reason identifier, the primary lookup key for the event type.
• LF_EVT_OCRD_DT — the date the potential life event occurred.
• PER_IN_LER_STAT_CD — the potential life event for person status code. This is the column most commonly searched under its documented alias, PER_IN_LER_STAT_CD, and corresponds to PTNL_LER_FOR_PER_STAT_CD in the base table. The view definition restricts this column to the values 'DTCTD' (detected) and 'UNPROCD' (unprocessed).

Common Use Cases and Queries

Typical uses include identifying contact persons with open life events awaiting processing, supporting eligibility and enrollment diagnostics, and reconciling detected events against processed records. Because the view is session-scoped and status-restricted, it is well suited to operational queries rather than historical warehouse reporting.

A representative query listing detected and unprocessed life events by contact person:

• SELECT contact_person_id, full_name, name, ler_id, lf_evt_ocrd_dt, per_in_ler_stat_cd FROM apps.ben_rel_per_life_evts_v WHERE per_in_ler_stat_cd = 'DTCTD' ORDER BY lf_evt_ocrd_dt;

To review unprocessed events for a specific reason:

• SELECT full_name, lf_evt_ocrd_dt, per_in_ler_stat_cd FROM apps.ben_rel_per_life_evts_v WHERE ler_id = :p_ler_id AND per_in_ler_stat_cd = 'UNPROCD';

Because results depend on the session effective date, direct SQL executed outside a standard EBS session may return no rows; the FND_SESSIONS join requires a valid session context. Analysts should therefore run such queries through a properly initialized session or an EBS concurrent program context.