Results for “clsd_dt”

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

Overview

BEN_PER_IN_LER is a core transactional table within the Oracle Advanced Benefits (BEN) module. Its documented description is "Person having a life event," which identifies its role precisely: each row represents a person for whom a Life Event has been detected, processed, or is pending processing. In Oracle EBS Advanced Benefits, life events such as marriage, birth, divorce, or a change in employment status trigger eligibility re-evaluation, enrollment opportunity windows, and rate recalculation. BEN_PER_IN_LER is the anchor record that ties a person to the specific life event occurrence that initiated the benefits processing cycle.

The table resides in the BEN schema and holds 62 columns in the documented 12.2.2 physical schema. Its primary key is the surrogate identifier PER_IN_LER_ID, enforced through the unique index BEN_PER_IN_LER_PK. Heuristic Data Vault classification derived from the foreign-key structure suggests a hub-leaning model: BEN_PER_IN_LER has only one outbound foreign key and is referenced by more than thirty downstream tables, a pattern consistent with a central entity of record rather than a pure association or descriptive satellite. Downstream tables such as BEN_ELIG_PER_F, BEN_PRTT_ENRT_RSLT_F, and BEN_PRTT_PREM_F all carry PER_IN_LER_ID, confirming that this table functions as the parent hub for eligibility, enrollment, and premium processing activity associated with a detected life event.

Key Information Stored

The most significant columns fall into three groups. Identifying and relationship columns include PER_IN_LER_ID (surrogate primary key), BUSINESS_GROUP_ID (the HR business group in which the event applies), PERSON_ID (the person experiencing the life event), LER_ID (the life event reason definition), and PTNL_LER_FOR_PER_ID, which points to the parent record in BEN_PTNL_LER_FOR_PER and links this event back to a potential life event identified for the person. LF_EVT_OCRD_DT records the actual date the life event occurred, while TRGR_TABLE_PK_ID identifies the source transaction row that triggered detection.

Processing status and lifecycle columns include PER_IN_LER_STAT_CD (the current state of the life event record) and PRVS_STAT_CD (the prior state, retained for audit and troubleshooting). Operational timestamps PROCD_DT, STRTD_DT, VOIDD_DT, BCKT_DT, and CLSD_DT capture when the record was processed, started, voided, rolled back, and closed, while NTFN_DT records notification. BCKT_PER_IN_LER_ID supports rollback and reprocessing by pointing to a prior or bucket version of the record. WS_MGR_ID, ASSIGNMENT_ID, GROUP_PL_ID, and MGR_OVRID_PERSON_ID identify the worker's assignment and any manager override involved.

The table carries Oracle's standard WHO and concurrency columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and OBJECT_VERSION_NUMBER. The 30 PIL_ATTRIBUTE columns plus PIL_ATTRIBUTE_CATEGORY provide a flexible descriptive flexfield for customer-defined life event data. The documented unique index BEN_PER_IN_LER_PK (PER_IN_LER_ID) is the only business-key candidate available, confirming that PER_IN_LER_ID is the primary business identifier.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include identifying all persons with an open life event, tracking events still to be processed in a batch cycle, and auditing the state progression of a record between detection and closure. A representative query retrieves active life events joined to the person and life event reason:

• Filter by PER_IN_LER_STAT_CD to find records pending processing or in error.
• Join PERSON_ID to PER_ALL_PEOPLE_F to resolve the person's name and employee number.
• Join LER_ID to BEN_LER_F to obtain the life event description.
• Use LF_EVT_OCRD_DT to analyze events by occurrence date for a given BUSINESS_GROUP_ID.
• Join to BEN_PRTT_ENRT_RSLT_F or BEN_PRTT_PREM_F via PER_IN_LER_ID to trace eligibility and premium outcomes back to the originating event.

A frequently used pattern is a status variance report comparing PER_IN_LER_STAT_CD with PRVS_STAT_CD over a date range using LAST_UPDATE_DATE, which highlights events that have been reprocessed or voided. Because the table participates in batch life event processing, REQUEST_ID and PROCD_DT are effective filters when investigating concurrent program behavior during a benefits open enrollment cycle.

Related Objects

The most consequential related objects, based on documented foreign-key relationships, are as follows:

• BEN_PTNL_LER_FOR_PER — the sole parent referenced by BEN_PER_IN_LER through PTNL_LER_FOR_PER_ID, linking detected events to potential life events for the person.
• BEN_ELIG_PER_F — references BEN_PER_IN_LER via PER_IN_LER_ID and stores eligibility results calculated for the person's life event.
• BEN_PRTT_ENRT_RSLT_F — enrollment results produced for a participant as a direct consequence of processing the life event.
• BEN_PRTT_PREM_F — premium amounts associated with the participant and life event.
• BEN_PRTT_RT_VAL — rate values for the participant; uniquely it references BEN_PER_IN_LER twice, via PER_IN_LER_ID and ENDED_PER_IN_LER_ID.
• BEN_PER_CM_F — person communications generated in response to the life event.
• BEN_BATCH_LER_INFO — batch life event processing information that references PER_IN_LER_ID.
• BEN_ELIG_PER_ELCTBL_CHC and BEN_PIL_ELCTBL_CHC_POPL — eligibility records for elective choices populated for the person in life event context.
• BEN_PRTT_ENRT_ACTN_F — enrollment actions recorded for the participant and life event.

Several read-only views, including BEN_PER_IN_LER_RBV, BEN_PRTT_RT_VAL_RBV, and BEN_ELIG_PER_F_RBV, reference the table and are typically used for reporting and inquiry rather than transaction entry. The breadth of these dependencies confirms that BEN_PER_IN_LER is a central hub in the Advanced Benefits processing model, and that its integrity directly affects eligibility, enrollment, and premium processing across the module.