Results for “prtt_enrt_rslt_stat_cd”

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

Overview

BEN_PRTT_ENRT_RSLT is an APPS-owned database view within the Oracle Advanced Benefits (BEN) product family. Its name derives from "participant enrollment result," reflecting its purpose of exposing participant-level benefit enrollment outcomes. In Oracle EBS Release 12.1.1 and 12.2.2, this view serves as the effective-dated, session-aware interface layer over the underlying enrollment results table, and it is catalogued in the ETRM reference documentation with a status of VALID and a "Retrofitted" description note.

The view plays a dual role in Oracle EBS reporting and integration. First, it provides a read-consistent projection of benefit enrollment results in which only rows effective for the current application session date are visible. Second, it presents a stable, simplified column interface that report developers and integration builders can query without having to replicate the effective date filtering logic that governs the base table. Because BEN_PRTT_ENRT_RSLT is a view rather than a table, it stores no data of its own; all content is derived at runtime from the base objects.

Underlying Base Objects

The ETRM metadata records two referenced base objects for this view: BEN_PRTT_ENRT_RSLT_F (referenced as a SYNONYM) and FND_SESSIONS (referenced as a SYNONYM). BEN_PRTT_ENRT_RSLT_F is the enrollment results entity that stores participant enrollment, coverage, and benefit amount data using effective start and end dates. FND_SESSIONS is the Oracle Application Object Library table that tracks session-level context, most notably the effective date used to drive date-tracked (datetracked) queries.

The view definition selects column-by-column from BEN_PRTT_ENRT_RSLT_F and applies a correlated date filter: rows are returned only where EFFECTIVE_START_DATE is less than or equal to the session effective date and EFFECTIVE_END_DATE is greater than or equal to that same session effective date. The session effective date is obtained through a subquery against FND_SESSIONS keyed by USERENV('SESSIONID'), the current database session identifier. This construction is the standard Advanced Benefits date-tracked view pattern and ensures that queries see the enrollment result as of the session's effective date rather than the raw system date.

Key Columns

• PRTT_ENRT_RSLT_ID — Primary identifier for the participation enrollment result row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-track columns that define the row's validity and drive the view's session-based filtering.
• BUSINESS_GROUP_ID — Identifies the business group (HR security organization) owning the record.
• PERSON_ID / ASSIGNMENT_ID — The participant and their assignment to which the enrollment result applies.
• PGM_ID / PL_ID / PTIP_ID / PL_TYP_ID / OIPL_ID — Identifiers for the program, plan, plan type, option in plan, and related benefit entities.
• LER_ID / PER_IN_LER_ID — Identifies the life event or life event reason associated with the enrollment result.
• BNFT_AMT / UOM / BNFT_NNMNTRY_UOM / BNFT_TYP_CD — Benefit amount, unit of measure, and benefit type classification.
• PRTT_IS_CVRD_FLAG / SSPNDD_FLAG / NO_LNGR_ELIG_FLAG — Flags indicating whether the participant is covered, suspended, or no longer eligible.
• ORGNL_ENRT_DT / ENRT_MTHD_CD / ENRT_CVG_STRT_DT / ENRT_CVG_THRU_DT / ENRT_OVRID_THRU_DT — Original enrollment date, enrollment method, coverage start and through dates, and override through date.
• ENRT_OVRIDN_FLAG / ENRT_OVRID_RSN_CD / ERLST_DEENRT_DT — Enrollment override indicator, override reason, and earliest de-enrollment date.
• COMP_LVL_CD / BNFT_ORDR_NUM / PRTT_ENRT_RSLT_STAT_CD — Compensation level, benefit ordering sequence, and the result status code.
• PEN_ATTRIBUTE1 through PEN_ATTRIBUTE30 — Descriptive flexfield attribute columns available for customer-defined data.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — Standard WHO and concurrency control columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit columns identifying the process that wrote the row.

Common Use Cases and Queries

Because the view automatically restricts rows to the current session effective date, it is the preferred source for reports, extracts, and integrations that require the "as-of-today" state of participant enrollment results. Typical scenarios include benefits enrollment audits, coverage verification extracts, life event reconciliation reports, and interfaces feeding payroll or third-party carriers.

A basic listing of currently effective enrollment results for a specific person:

• SELECT prtt_enrt_rslt_id, person_id, assignment_id, pl_id, ptip_id, bnft_amt, uom, enrt_cvg_strt_dt, enrt_cvg_thru_dt, prtt_is_cvrd_flag FROM apps.ben_prtt_enrt_rslt WHERE person_id = :p_person_id;

Identifying participants no longer eligible or currently suspended:

• SELECT person_id, pl_id, no_lngr_elig_flag, sspndd_flag, prtt_enrt_rslt_stat_cd FROM apps.ben_prtt_enrt_rslt WHERE no_lngr_elig_flag = 'Y' OR sspndd_flag = 'Y';

Joining to the base table to compare the session-filtered view against all effective-dated versions of a result:

• SELECT v.prtt_enrt_rslt_id, v.pl_id, f.effective_start_date, f.effective_end_date FROM apps.ben_prtt_enrt_rslt v, apps.ben_prtt_enrt_rslt_f f WHERE v.prtt_enrt_rslt_id = f.prtt_enrt_rslt_id AND v.person_id = :p_person_id;

Joining to FND_SESSIONS to inspect the effective date governing the view's filter for the current session:

• SELECT effective_date FROM fnd_sessions WHERE session_id = USERENV('SESSIONID');

In all cases, developers should treat BEN_PRTT_ENRT_RSLT as a read-only reporting object and perform any data maintenance against the underlying BEN_PRTT_ENRT_RSLT_F entity through the supported Advanced Benefits APIs rather than through direct DML.