Results for “ws_mgr_id”

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

Overview

BENBV_PER_IN_LER_V is an APPS-owned, read-only view in the Oracle E-Business Suite Advanced Benefits (BEN) module. Its purpose is to present all life events recorded for a person, and to identify which of those life events, if any, is currently in progress for the specified person. In release 12.1.1 and 12.2.2, the view is registered as VALID and is exposed under the APPS schema for reporting and integration consumption.

The view is a business-intelligence projection rather than a transactional entity. It flattens the underlying person-in-life-event record set and decorates it with decoded status values, a group plan name resolved through a PL/SQL utility, and a descriptive flexfield reference token ('_DF:BEN:BEN_PER_IN_LER:PIL'). This makes it suitable for concurrent programs, Oracle Discoverer/BIP data models, and custom SQL used by benefits administrators.

Underlying Base Objects

The view text selects exclusively from BEN_PER_IN_LER (aliased PIL), which is the base table storing person-specific life event occurrences. It invokes two supporting packages: BEN_BIS_UTILS.GET_GROUP_PL_NAME to resolve the group plan name for a given GROUP_PL_ID and life event occurrence date, and HR_BIS.BIS_DECODE_LOOKUP to translate BEN_PER_IN_LER_STAT status codes into their underlying meanings. HR_BIS.GET_SEC_PROFILE_BG_ID is used in the WHERE clause to enforce the business-group security profile.

The SELECT ... FROM BEN_PER_IN_LER PIL WHERE PIL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PIL.BUSINESS_GROUP_ID) WITH READ ONLY structure means the view automatically filters by the caller's security profile when one is set, and otherwise returns all business groups. The WITH READ ONLY clause prevents DML through the view.

Key Columns

The view exposes date columns that bracket the life event lifecycle: LF_EVT_OCRD_DT (the event occurrence date), PROCD_DT, STRTD_DT, VOIDD_DT, BCKT_DT, CLSD_DT, NTFN_DT, and MGR_OVRID_DT. Status columns include PER_IN_LER_STAT_CD and PRVS_STAT_CD, each paired with a decoded _M column via HR_BIS.BIS_DECODE_LOOKUP against the BEN_PER_IN_LER_STAT lookup.

The user's search term, MGR_OVRID_PERSON_ID, is a documented ID column on the view. It identifies the person associated with the manager override on the life event, complementing WS_MGR_ID. The ID set also contains PER_IN_LER_ID (primary identifier), BUSINESS_GROUP_ID, LER_ID, PERSON_ID, PTNL_LER_FOR_PER_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, BCKT_PER_IN_LER_ID, TRGR_TABLE_PK_ID, ASSIGNMENT_ID, and GROUP_PL_ID. WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) provide audit context, and GROUP_PLAN holds the resolved plan name. The token column "_DF:PIL" carries the descriptive flexfield reference.

Common Use Cases and Queries

Typical scenarios include identifying in-progress life events for a person, auditing manager overrides, and confirming event closure or notification dates for a benefits cycle. The following query returns in-progress events for a person, including the manager override person:

• SELECT per_in_ler_id, person_id, lf_evt_ocrd_dt, per_in_ler_stat_cd_m, mgr_ovrid_person_id, group_plan FROM apps.benbv_per_in_ler_v WHERE person_id = :p_person_id AND per_in_ler_stat_cd = 'INPROG';
• SELECT person_id, per_in_ler_id, mgr_ovrid_dt, mgr_ovrid_person_id, ws_mgr_id FROM apps.benbv_per_in_ler_v WHERE mgr_ovrid_person_id IS NOT NULL AND lf_evt_ocrd_dt BETWEEN :p_from AND :p_to;
• SELECT per_in_ler_stat_cd, per_in_ler_stat_cd_m, COUNT(*) FROM apps.benbv_per_in_ler_v WHERE business_group_id = :p_bg GROUP BY per_in_ler_stat_cd, per_in_ler_stat_cd_m;

Because the view is read only and applies the security-profile predicate internally, no additional business-group filter is required when the session has an applicable HR security profile. Reported results should be interpreted alongside BEN_PER_IN_LER, from which all rows are sourced.