Results for “ben_pl_typ_f”

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

Overview

BEN_PL_TYP_F is the plan type definition table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. In the benefits data model, a plan type serves as the highest-level categorization construct for benefits offerings, grouping individual plans (such as medical, dental, life, and disability programs) under a common enrollment, display, and processing framework. The table stores the descriptive attributes, enrollment rules, and display formatting options that govern how a plan type behaves across the self-service and administrative benefits interfaces.

From a Data Vault modeling perspective, the metadata characterizes BEN_PL_TYP_F as a standalone object, meaning no foreign key dependencies were mined from the FK structure. A heuristic classification would therefore suggest treating this as a hub-like reference entity keyed by the plan type identifier, with the dated attributes behaving as satellite-style descriptive data. The presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns confirms that the table is date-tracked, supporting multiple effective-dated versions of the same plan type.

Key Information Stored

The table contains fifty-six documented columns in the 12.2.2 physical schema. The most significant include:

• PL_TYP_ID — Surrogate identifier for the plan type, functioning as the leading component of the primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range columns that complete the primary key and enable historical versioning.
• NAME — The user-facing plan type name displayed in enrollment flows and reports.
• BUSINESS_GROUP_ID — Identifies the business group (HR organization) that owns the plan type definition.
• OPT_DSPLY_FMT_CD / OPT_TYP_CD — Codes controlling how options are displayed and the type of options offered.
• COMP_TYP_CD — Compensation type code relevant to plan processing.
• PL_TYP_STAT_CD — Status code indicating whether the plan type is active or inactive.
• MX_ENRL_ALWD_NUM / MN_ENRL_RQD_NUM — Maximum and minimum enrollment counts permitted, with companion flags such as NO_MX_ENRL_NUM_DFND_FLAG and NO_MN_ENRL_NUM_DFND_FLAG indicating whether those limits are defined.
• SHORT_CODE / SHORT_NAME — Abbreviated identifiers used in IVR and compact displays.
• LEGISLATION_CODE / LEGISLATION_SUBGROUP — Localization attributes governing statutory compliance.
• PTP_ATTRIBUTE_CATEGORY and PTP_ATTRIBUTE1 through PTP_ATTRIBUTE30 — A thirty-column descriptive flexfield segment block for customer-defined extensibility.
• ZD_EDITION_NAME — Editioning column introduced in 12.2, included in the unique index BEN_PL_TYP_F_PK alongside PL_TYP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.

The documented primary key is BEN_PL_TYP_F_PK, defined over PL_TYP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE; the unique index in 12.2.2 additionally incorporates ZD_EDITION_NAME.

Common Use Cases and Queries

Typical reporting and integration scenarios include retrieving active plan types for a business group, validating enrollment limits, and driving option display logic. A representative query pattern is:

• SELECT pt.pl_typ_id, pt.name, pt.pl_typ_stat_cd FROM ben_pl_typ_f pt WHERE pt.business_group_id = :p_bg_id AND SYSDATE BETWEEN pt.effective_start_date AND pt.effective_end_date
• Joining the table to plan-level objects through PL_TYP_ID to produce hierarchical benefit catalogs.
• Filtering on OPT_DSPLY_FMT_CD or OPT_TYP_CD to reproduce self-service enrollment rendering.
• Querying PTP_ATTRIBUTE segments to expose customer-specific plan type metadata in extracts and interfaces.

Related Objects

Although BEN_PL_TYP_F is classified as standalone with no mined foreign keys, it participates logically in the broader BEN schema. Significant related objects include:

• BEN_PL_F — The plan table, joined on PL_TYP_ID, which references the parent plan type.
• BEN_PL_TYP_OPT_F — Plan type option definitions linked through PL_TYP_ID.
• BEN_PGM_F — Program definitions that may aggregate plan types.
• BEN_PL_TYP_F_TL — The translation table supplying language-specific NAME values.
• HR_ORG_UNIT — Source of BUSINESS_GROUP_ID validation.
• FND_LOOKUPS — Lookup validation for codes such as OPT_TYP_CD and PL_TYP_STAT_CD.
• BEN_PL_TYP_API — PL/SQL API used to insert, update, and date-track plan type records programmatically.