Results for “ben_ptnl_ler_for_per_stat”

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

Overview

APPS.BENBV_PTNL_LER_FOR_PER_V is a read-only Oracle EBS view that exposes potential Life Event Record (LER) data associated with a person in Oracle Advanced Benefits (OAB). The suffix convention _BV indicates a business view, and the view's single-table SELECT with a security predicate confirms it is a reporting and integration-facing object rather than a transactional entity. It presents the contents of the underlying BEN_PTNL_LER_FOR_PER table, which stores potential life events that have been detected for a participant but not yet fully processed into an actual life event.

The view supports the letter-generation, notification, and self-service processing flows within Benefits by surfacing detection, processing, and void timestamps alongside status and source codes. It is frequently joined to person, enrollment period, and program context to drive the "What's New" and life event change pages in Oracle Self-Service Benefits and to feed concurrent programs that evaluate eligibility.

Underlying Base Objects

The view is defined over a single base object, BEN_PTNL_LER_FOR_PER (referenced as a synonym), and calls the package HR_BIS to perform lookup decoding. Unlike many EBS reporting views, it does not join to PER_ALL_PEOPLE_F, BEN_LER_F, or other tables inside its definition; the table itself carries the person and LER identifiers as foreign-key columns.

The HR_BIS.GET_SEC_PROFILE_BG_ID function applies the session user's security profile business group. The WHERE clause PTN.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PTN.BUSINESS_GROUP_ID) restricts rows to the caller's accessible business group, and the view is created WITH READ ONLY, preventing DML through the view.

Key Columns

• PTNL_LER_FOR_PER_ID — Primary key of the potential LER row; used to correlate with downstream processing.
• LER_ID — Identifies the life event record being detected.
• PERSON_ID — The participant for whom the potential life event applies.
• ENRT_PERD_ID — Enrollment period context for the detection.
• PTNL_LER_FOR_PER_STAT_CD — Status code of the potential LER (e.g., unprocessed, processed, voided). This is the column the user's search term ben_ptnl_ler_for_per_stat references.
• PTNL_LER_FOR_PER_SRC_CD — Source of the detection (e.g., manual, program-driven).
• LF_EVT_OCRD_DT — Date the life event occurred.
• DTCTD_DT, PROCD_DT, UNPROCD_DT, VOIDD_DT, MNL_DT, MNLO_DT — Detection, processing, unprocessing, voiding, and manual override timestamps.
• NTFN_DT — Notification date.
• TRGR_TABLE_PK_ID, CSD_BY_PTNL_LER_FOR_PER_ID — Triggering record and causal link identifiers.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — Concurrent program context that created or last updated the row.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE.

The view also exposes decoded descriptions via HR_BIS.BIS_DECODE_LOOKUP for both the status and source codes, using the lookups BEN_PTNL_LER_FOR_PER_STAT and BEN_PTNL_LER_FOR_PER_SRC.

Common Use Cases and Queries

Typical usage includes identifying participants with unprocessed detections, auditing voided or manually overridden potential life events, and diagnosing notification failures. A representative query retrieving pending detections for a person is:

• SELECT PTNL_LER_FOR_PER_ID, LER_ID, PERSON_ID, PTNL_LER_FOR_PER_STAT_CD, LF_EVT_OCRD_DT, DTCTD_DT FROM APPS.BENBV_PTNL_LER_FOR_PER_V WHERE PERSON_ID = :p_person_id AND PTNL_LER_FOR_PER_STAT_CD = 'UNPROCESSED';
• SELECT STATUS_DECODE, COUNT(*) FROM APPS.BENBV_PTNL_LER_FOR_PER_V GROUP BY STATUS_DECODE;
• SELECT * FROM APPS.BENBV_PTNL_LER_FOR_PER_V WHERE VOIDD_DT IS NOT NULL AND TRUNC(VOIDD_DT) >= TRUNC(SYSDATE)-30;

Because the view enforces business group security and is read only, it is safe to expose to reporting tools, BI Publisher data models, and integration interfaces querying Oracle Benefits data in EBS 12.1.1 and 12.2.2.