Search Results ben_pl_typ_opt_typ_f_pk

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

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:

• PL_TYP_OPT_TYP_ID — surrogate identifier for the record; part of the composite primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the validity window; both are part of the primary key.
• PL_TYP_OPT_TYP_CD — the user-visible code for the plan-type/option-type relationship, a business-key candidate.
• PL_TYP_ID — foreign key to the plan type definition.
• OPT_ID — foreign key to the option type (the enrollment option).
• BUSINESS_GROUP_ID — the enterprise business group that owns the configuration.
• LEGISLATION_CODE and LEGISLATION_SUBGROUP — legislative context for localisation.
• PON_ATTRIBUTE_CATEGORY and PON_ATTRIBUTE1 through PON_ATTRIBUTE30 — a 30-column descriptive flexfield used for client-specific extensions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard WHO audit and optimistic-locking columns.

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:

• BEN_PL_TYP_F — plan type definition; joined on PL_TYP_ID.
• BEN_OPT_TYP_F — option type definition; joined on OPT_ID.
• BEN_PL_TYP_OPT_F — the plan-type-to-plan linkage that complements this option-type relationship.
• BEN_PL_F — plan definition, reached indirectly through plan type.
• BEN_PGM_F — program definition, the parent of plan type.
• FND_DESCR_FLEX_COLUMN_USAGES and FND_FLEX_VALUES — resolve the PON_ATTRIBUTE descriptive flexfield columns.
• HR_ALL_ORGANIZATION_UNITS and FND_ID_FLEX_STRUCTURES — supply business-group and legislative context.

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.