Search Results ben_pl_typ




Overview
Explore in AI Chat

Overview

BEN_PL_TYP is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. The view presents the "Plan Type" entity, which defines the high-level classification of benefit programs — for example, Medical, Dental, Life Insurance, or Flexible Spending Account. Plan Types serve as the parent construct under which individual plans and options are organized, and they control enrollment behavior such as maximum allowable enrollments and minimum required enrollments. The object is documented as "Retrofitted," reflecting its migration into the standard ETRM-documented inventory while retaining the original BEN schema design.

The view is a date-effective (datetrack) wrapper. Rather than exposing every historical row stored in the base table, it returns only the row that is valid as of the current session's effective date, making it suitable for runtime reporting, concurrent programs, and integration interfaces that must respect effective dating semantics.

Underlying Base Objects

The view definition selects from two documented base objects, both referenced through APPS synonyms:

  • BEN_PL_TYP_F — the plan type entity table, aliased PTP in the view text. This is the primary source of all data columns.
  • FND_SESSIONS — the Oracle Application Object Library session table, used to resolve the effective date for the current session.

The date-effective filter is implemented in the WHERE clause: the view returns rows 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 date. The session effective date is obtained by a correlated subquery against FND_SESSIONS keyed on USERENV('SESSIONID'). All columns are propagated directly from the base table, including the thirty descriptive flexfield attribute columns.

Key Columns

Common Use Cases and Queries

The view is typically joined to plan-level views such as BEN_PL and to enrollment tables when building benefits eligibility or enrollment reports. A representative query listing active plan types visible in the current session is:

  • SELECT pl_typ_id, name, short_name, pl_typ_stat_cd FROM apps.ben_pl_typ WHERE pl_typ_stat_cd = 'A' ORDER BY name;
  • SELECT pt.name plan_type, p.name plan FROM apps.ben_pl_typ pt, apps.ben_pl p WHERE p.pl_typ_id = pt.pl_typ_id;
  • Restricting by legislation: ... WHERE legislation_code = :p_legislation.

Because the view hides ineffective-dated rows, it should be used for current-state reporting. Historical or future-dated analysis requires querying BEN_PL_TYP_F directly with explicit date predicates.