Search Results frfs_val_det_cd




Overview
Explore in AI Chat

Overview

BEN_PL_F is the Plan table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, validated against releases 12.1.1 and 12.2.2. It is the master definition table for benefit plans — the configurable programs such as medical, dental, life, savings, and flexible spending account offerings through which an organization delivers benefits to its eligible population. Every plan an employee can enroll in, decline, or be automatically assigned is anchored to a row in this table, and the extensive flag, code, and rule columns that make up the record define the plan's enrollment behavior, eligibility handling, cost, coverage, and funding characteristics.

The documented object resides in the BEN schema and is owned by the Advanced Benefits product. It is flagged VALID and carries a documented physical footprint of 202 columns in the ETRM 12.2.2 schema. The heuristic Data Vault classification derived from its foreign-key structure is standalone, suggesting the table operates as an independent reference or hub-style entity rather than as a dependent link or satellite. In data-warehouse terms, this indicates BEN_PL_F can be treated as a primary source entity for plan master data, with other structures referencing it rather than the reverse.

Key Information Stored

The surrogate primary key is BEN_PL_F_PK, composed of PL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This composite key reflects the date-tracked, effective-dated design used throughout Advanced Benefits: a single plan identifier can hold multiple time-sliced versions, and the effective date range distinguishes them. The documented unique index BEN_PL_F_PK also includes ZD_EDITION_NAME, which is the edition column used by the Oracle data-model editioning feature and should be treated as part of the full business-key candidate set.

Among the 202 documented columns, the most significant include:

  • PL_ID — the plan identifier, part of the primary key and the principal join column to dependent objects.
  • NAME — the user-facing plan name presented in enrollment and reporting interfaces.
  • PL_CD — the short plan code used in configuration, interfaces, and extracts.
  • PL_TYP_ID — reference to the plan type (medical, dental, life, etc.), driving much downstream logic.
  • BUSINESS_GROUP_ID — partitions the plan to the owning business group, essential for multi-organization deployments.
  • PL_STAT_CD — the plan status code controlling whether the plan is active, inactive, or otherwise restricted.
  • ENRT_CD and ENRT_RL — the enrollment code and rule that govern how participants elect the plan.
  • SVGS_PL_FLAG — indicates whether the plan is a savings plan, distinguishing it from insurance-style offerings.
  • WVBL_FLAG — waiver capability, relevant for plans where coverage may be waived.
  • ALWS_QDRO_FLAG and ALWS_QMCSO_FLAG — control acceptance of qualified domestic relations and medical child support orders.
  • PRMRY_FNDG_MTHD_CD — the primary funding method for the plan.
  • LEGISLATION_CODE and LEGISLATION_SUBGROUP — localize the plan to statutory requirements.
  • DFLT_TO_ASN_PNDG_CTFN_RL / _CD — defines default-to-assignment behavior pending certification.
  • Standard WHO columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and OBJECT_VERSION_NUMBER — provide audit and optimistic-locking support.

Common Use Cases and Queries

The most frequent access pattern is retrieving the currently effective definition of a plan. Because of the effective-dated key, queries must constrain SYSDATE within the effective range:

  • Plan lookup by business group and status to populate enrollment screens or interface staging tables.
  • Extracting plan catalogs for carrier/vendor feeds, filtering on PL_TYP_ID, PL_STAT_CD, and LEGISLATION_CODE.
  • Reporting on plan configuration completeness, for example listing plans where enrollment rules or funding methods are not yet defined.
  • Auditing changes over time by comparing successive effective-dated rows for the same PL_ID.

A representative query pattern joins BEN_PL_F to BEN_PL_TYP_F on PL_TYP_ID for a typed plan listing, and to BEN_PL_F_TL (the translation table) on PL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE where localized names are required. Always include ZD_EDITION_NAME handling if querying an edition-enabled environment.

Related Objects

The following objects are most significant in relation to BEN_PL_F:

  • BEN_PL_TYP_F — joined on PL_TYP_ID; defines the plan type classification.
  • BEN_PL_F_TL — joined on PL_ID and the effective date columns; holds translated plan names and descriptions.
  • BEN_PL_OPT_F — joined on PL_ID; defines plan options and rates within each plan.
  • BEN_ELIG_... programs and BEN_PG_... — eligibility and program definitions that include plans for participant evaluation.
  • BEN_PRTT_ENRT_RSLT_F — participant enrollment results, joined on the plan identifier.
  • BEN_PER_BNF_PL_F / BEN_PL_BNF_F — beneficiary and plan-benefit relationships.
  • BEN_FRFS_F — flexible spending account definitions linked to benefit plans.
  • BEN_PL_F APIs such as the plan maintenance PL/SQL packages in the BEN schema — used for programmatic creation and updates rather than direct DML.

Because BEN_PL_F is date-effective and edition-aware, any integration touching it should filter on both the temporal key and, where applicable, ZD_EDITION_NAME to avoid duplicate or historical rows.