Results for “benbv_prtt_enrt_rslt_v”

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

Overview

The view BENBV_PRTT_ENRT_RSLT_V is a business view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the Advanced Benefits (BEN) product. It identifies the plans, and the options within those plans, in which a participant is enrolled — whether through explicit election by the participant or administrator, or through system default and automatic enrollment processing. The view is a "BV" (business view) that wraps a single underlying table and exposes its columns with plain-language decode values resolved dynamically through HR_BIS.BIS_DECODE_LOOKUP.

This view is commonly used as a reporting and integration layer: it presents enrollment results in a denormalized, decode-friendly form suitable for extracts, OBIEE/BI Publisher reports, and inbound/outbound interfaces. The view enforces Multi-Org security by filtering records to the business group resolved via the security profile, using NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PEN.BUSINESS_GROUP_ID).

Underlying Base Objects

The documented metadata specifies the base object as the synonym BEN_PRTT_ENRT_RSLT_F (the participant enrollment results fact table) and the package HR_BIS. The view is essentially a straight projection: SELECT ... FROM BEN_PRTT_ENRT_RSLT_F PEN WHERE PEN.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PEN.BUSINESS_GROUP_ID). Every field originates from BEN_PRTT_ENRT_RSLT_F; HR_BIS.BIS_DECODE_LOOKUP is called only to translate coded columns into descriptive values.

Key Columns

• PRTT_ENRT_RSLT_ID — Primary key identifying the participant enrollment result row.
• PERSON_ID, ASSIGNMENT_ID, PER_IN_LER_ID — Participant and assignment/eligibility context.
• PL_ID, OIPL_ID, PTIP_ID, PL_TYP_ID — The plan and option-in-plan identifiers describing what the participant is enrolled in.
• ENRT_MTHD_CD — Method of enrollment (explicit election vs. system default/automatic). Decoded via BEN_ENRT_RSLT_MTHD.
• PRTT_ENRT_RSLT_STAT_CD — Enrollment result status. Decoded via BEN_PRTT_ENRT_RSLT_STAT.
• SSPNDD_FLAG — Suspended flag. Decoded via YES_NO.
• RPLCS_SSPNDD_RSLT_ID — The enrollment result ID of the record whose suspension this row replaces. This is the column returned by the searched term rplcs_sspndd_rslt_id and links a replacement enrollment back to the suspended result it supersedes.
• BNFT_AMT, BNFT_NNMNTRY_UOM, UOM, BNFT_ORDR_NUM — Benefit amount, unit of measure, and ordering.
• ENRT_CVG_STRT_DT, ENRT_CVG_THRU_DT, ERLST_DEENRT_DT, ENRT_OVRID_THRU_DT — Coverage start/through dates, earliest de-enrollment date, and override through date.
• PRTT_IS_CVRD_FLAG, ENRT_OVRIDN_FLAG, NO_LNGR_ELIG_FLAG — Status flags (covered, overridden, no longer eligible).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective boundaries for the record.
• WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE.

Common Use Cases and Queries

A frequent requirement is to find the replacement relationship created by suspension processing, i.e., joining a current enrollment result to the suspended result it replaces:

• Locate replacement rows by filtering on RPLCS_SSPNDD_RSLT_ID.
• Report suspended enrollments via SSPNDD_FLAG = 'Y'.
• Distinguish default enrollments from explicit elections using ENRT_MTHD_CD.

Sample query listing current enrollments for a person:

SELECT pen.person_id,
       pen.pl_id,
       pen.oipl_id,
       hr_bis.bis_decode_lookup('BEN_PRTT_ENRT_RSLT_STAT', pen.prtt_enrt_rslt_stat_cd) status,
       pen.enrt_cvg_strt_dt,
       pen.enrt_cvg_thru_dt,
       pen.sspndd_flag,
       pen.rplcs_sspndd_rslt_id
FROM   apps.benbv_prtt_enrt_rslt_v pen
WHERE  pen.person_id = :p_person_id
AND    TRUNC(SYSDATE) BETWEEN pen.effective_start_date AND pen.effective_end_date;

Query identifying replacement enrollments and their superseded suspended results:

SELECT current.prtt_enrt_rslt_id,
       current.person_id,
       current.pl_id,
       current.rplcs_sspndd_rslt_id,
       prior.prtt_enrt_rslt_id  suspended_rslt_id
FROM   apps.benbv_prtt_enrt_rslt_v current,
       apps.benbv_prtt_enrt_rslt_v prior
WHERE  current.rplcs_sspndd_rslt_id = prior.prtt_enrt_rslt_id;

Because the view resolves coded columns to descriptions and applies the security-profile business group filter, it is the preferred object for ad-hoc reporting and interface extraction over the raw BEN_PRTT_ENRT_RSLT_F table.