Search Results ben_pl_typ_opt_typ_f




Overview
Explore in AI Chat

Overview

BEN_PL_TYP_OPT_TYP_F is a dated (effective-dated) table in the BEN schema of Oracle Advanced Benefits, one of the HRMS family of products in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the relationship between a plan type (PL_TYP_ID) and the option types (OPT_ID) that a participant may elect within that plan type. In practical terms, it is the control record that tells the Benefits engine which enrollment options are permissible for a given plan type and during which effective date range that permissibility holds.

The table is date-tracked: every logical row is versioned by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, and the primary key BEN_PL_TYP_OPT_TYP_F_PK is composed of PL_TYP_OPT_TYP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. Because new versions are inserted rather than updated in place, a single plan-type-to-option-type pairing may appear across multiple rows.

The documented Data Vault classification is standalone. Consistent with that classification, the table functions as a self-contained reference or hub-like entity: it carries its own surrogate key and business key and does not participate in the link structures that typically connect two or more hubs. The classification is heuristic and should be treated as a modeling suggestion when designing downstream datamarts.

Key Information Stored

The most significant columns are:

The surrogate key is PL_TYP_OPT_TYP_ID; the unique index BEN_PL_TYP_OPT_TYP_F_PK defines the business-key candidate when combined with the effective dates and the code.

Common Use Cases and Queries

Typical uses include validating which option types are offered for a plan type, constructing enrollment eligibility reports, and reconciling setup between environments. A common pattern is an as-of-date query that returns the currently active pairing:

  • SELECT pl_typ_opt_typ_cd, pl_typ_id, opt_id FROM ben_pl_typ_opt_typ_f WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date AND business_group_id = :p_bg_id;
  • Join to the plan type and option type definitions to resolve names for reporting.
  • Compare effective-dated versions to audit when an option type was added or removed from a plan type.
  • Drive BI Publisher or OBIEE extracts that feed benefits enrollment dashboards.

Related Objects

The most significant related objects are the plan type and option type definitions that this table references, together with the standard Benefits enrollment and lookup tables that consume the configuration:

These join paths, anchored on PL_TYP_OPT_TYP_ID, PL_TYP_ID, and OPT_ID, provide the reference topology needed for benefits configuration and enrollment reporting in Oracle EBS 12.1.1 and 12.2.2.