Search Results actl_prem_name




Overview
Explore in AI Chat

Overview

BEN_PRTT_PREM_D is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, part of the BEN (Advanced Benefits) product family. The view exposes participant premium records from the BEN_PRTT_PREM_F table, enriching each row with descriptive names resolved from several reference and lookup tables. Its primary purpose is to translate the raw foreign-key identifiers stored on the premium fact table—such as currency codes, actual premium identifiers, plan identifiers, and option-in-plan identifiers—into human-readable labels suitable for concurrent programs, Oracle Discoverer workbooks, BI Publisher reports, and ad-hoc SQL. The "_D" suffix conventionally denotes a "descriptive" or "display" view in the ETRM (E-Business Tables and Views Reference) catalog, signaling that the object is intended for read-only query consumption rather than transactional DML.

Underlying Base Objects

Per the documented 12.2.2 metadata, BEN_PRTT_PREM_D joins nine referenced objects. Eight are synonyms (BEN_ACTL_PREM_F, BEN_OIPL_F, BEN_OPT_F, BEN_PER_IN_LER, BEN_PL_F, BEN_PRTT_ENRT_RSLT_F, BEN_PRTT_PREM_F) that resolve to the corresponding BEN base tables, plus two views (FND_CURRENCIES_VL and FND_USER_VIEW) from the Foundation (FND) application. The driving table is BEN_PRTT_PREM_F (aliased PPE), the participant premium fact table. BEN_PER_IN_LER (PIL) supplies the person-in-learner context and is an inner join via PER_IN_LER_ID and BUSINESS_GROUP_ID. All other joins are outer (+) joins, meaning premium rows are retained even when the associated actual premium definition, enrollment result, plan, option, currency, or user record is absent. This outer-join design prevents data loss in reporting when reference data has been end-dated or removed.

Key Columns

  • ROW_ID — the ROWID of the underlying BEN_PRTT_PREM_F row, useful for pinpointing the source record.
  • PRTT_PREM_ID — primary key of the participant premium record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective range of the premium row.
  • STD_PREM_MEANING — the currency name resolved from FND_CURRENCIES_VL; despite the column alias "STD_PREM_MEANING", the join maps PPE.STD_PREM_UOM to CURRENCY_CODE, so this column effectively describes the standard premium unit (currency) of measure.
  • STD_PREM_VAL — the standard premium value or amount.
  • ACTL_PREM_NAME — the descriptive name of the actual premium definition (from BEN_ACTL_PREM_F). This is the column users most frequently search for, since it labels the premium type.
  • PRTT_ENRT_RSLT_PLAN — the plan name resolved via BEN_PL_F through the enrollment result.
  • PRTT_ENRT_RSLT_OIPL — the option-in-plan name resolved through BEN_OIPL_F and BEN_OPT_F.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY / PROGRAM_UPDATE_DATE — audit columns; LAST_UPDATED_BY is translated to a user name via FND_USER_VIEW.

Common Use Cases and Queries

The view supports premium reconciliation, participant benefit statements, and audit reporting. A typical query filters by plan or participant and selects the descriptive columns:

SELECT prtt_prem_id, actl_prem_name, std_prem_meaning, std_prem_val, prtt_enrt_rslt_plan, prtt_enrt_rslt_oipl, last_update_date FROM apps.ben_prtt_prem_d WHERE prtt_enrt_rslt_plan = :plan RETURNING effective_start_date >= SYSDATE;

Because it is a view over date-effective (datetrack) tables, callers should always qualify results with effective dates to avoid returning historical or future-dated versions. The outer joins mean that ACTL_PREM_NAME, PRTT_ENRT_RSLT_PLAN, or PRTT_ENRT_RSLT_OIPL may be NULL; reports should handle these gracefully. The absence of DISTINCT in the definition also means joins to BEN_PRTT_ENRT_RSLT_F can fan out rows if enrollment results are versioned, so client SQL should apply appropriate effective-date predicates. The view is read-only and should never be used as a target for DML; updates must be issued against BEN_PRTT_PREM_F directly through the supported Benefits APIs.