Results for “ptnl_ler_for_per_src_cd_m”

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

Overview

BENBV_PTNL_LER_FOR_PER_V is a read-only view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It is delivered as part of the Advanced Benefits (BEN) product family and exposes the set of potential life events (LERs) associated with a person. In the Oracle EBS data model, a "potential life event" represents a life event that the system has identified as applicable to a participant but that has not necessarily been detected, processed, voided, or otherwise finalized. The view therefore serves as the reporting and integration surface for enrollment-related event recognition logic, feeding benefit self-service pages, concurrent processes, and downstream extracts that must evaluate which life events a person may act upon.

The view is defined with the WITH READ ONLY clause, meaning it cannot be used as the target of DML. This is significant because it confirms the object is intended purely for query access; any update to the underlying potential-life-event records must be performed through the BEN product's PL/SQL application programming interfaces rather than directly against this view.

Underlying Base Objects

The view is defined over two documented objects:

• BEN_PTNL_LER_FOR_PER — the base table holding potential life event records. The view is a synonym-referenced projection of this table, aliased as PTN in the defining SQL.
• HR_BIS — the Business Intelligence System package, invoked in the view's WHERE clause through HR_BIS.GET_SEC_PROFILE_BG_ID and in the SELECT list through HR_BIS.BIS_DECODE_LOOKUP.

Two mechanisms tie the view to its base table and to the security model. First, row filtering is applied via BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PTN.BUSINESS_GROUP_ID), which restricts the result set to the business group resolved from the caller's security profile; where no such profile value is available, all business groups are returned. Second, the coded status and source columns are translated on the fly using BIS_DECODE_LOOKUP against the lookup types BEN_PTNL_LER_FOR_PER_STAT and BEN_PTNL_LER_FOR_PER_SRC, producing the _M (meaning) columns alongside the raw codes.

Key Columns

The view exposes a comprehensive set of identifier, date, status, and audit columns:

• Date columns: LF_EVT_OCRD_DT (life event occurred date), DTCTD_DT (detected date), PROCD_DT (processed date), UNPROCD_DT (unprocessed date), VOIDD_DT (voided date), MNL_DT (manual date), MNLO_DT (manual override date), NTFN_DT (notification date), and PROGRAM_UPDATE_DATE.
• Status and source: PTNL_LER_FOR_PER_STAT_CD with decoded _M column, and PTNL_LER_FOR_PER_SRC_CD with decoded _M column.
• Identifiers: PTNL_LER_FOR_PER_ID (primary identifier), BUSINESS_GROUP_ID, LER_ID (life event reason), PERSON_ID, ENRT_PERD_ID (enrollment period), REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, CSD_BY_PTNL_LER_FOR_PER_ID (caused by a related potential life event), and TRGR_TABLE_PK_ID.
• Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, and CREATION_DATE.

Common Use Cases and Queries

Typical reporting scenarios include identifying unprocessed life events for a participant, auditing manual overrides, and reconciling detected versus processed events. The query below retrieves active potential life events for a specific person, decoding status and source and applying the same business-group security logic used by the view itself:

SELECT p.PERSON_ID,
       p.LER_ID,
       p.PTNL_LER_FOR_PER_STAT_CD,
       p.PTNL_LER_FOR_PER_STAT_CD_M,
       p.PTNL_LER_FOR_PER_SRC_CD_M,
       p.LF_EVT_OCRD_DT,
       p.DTCTD_DT,
       p.PROCD_DT,
       p.VOIDD_DT,
       p.MNL_DT,
       p.MNLO_DT
  FROM APPS.BENBV_PTNL_LER_FOR_PER_V p
 WHERE p.PERSON_ID = :p_person_id
   AND p.PROCD_DT IS NULL
   AND p.VOIDD_DT IS NULL
 ORDER BY p.LF_EVT_OCRD_DT DESC;

A second common pattern counts potential life events by status for a business group, useful for benefit administrators monitoring workflow backlogs:

SELECT p.PTNL_LER_FOR_PER_STAT_CD_M AS status_meaning,
       COUNT(*) AS event_count
  FROM APPS.BENBV_PTNL_LER_FOR_PER_V p
 GROUP BY p.PTNL_LER_FOR_PER_STAT_CD_M;

Because the view is read-only and security-filtered, it is safe to expose directly to BI Publisher reports and custom concurrent programs. Note that the security predicate resolves at query time, so performance depends on the HR_BIS.GET_SEC_PROFILE_BG_ID function returning a single business group; where it returns NULL, the predicate broadens to all business groups.