Search Results ben_dsgn_rqmt_f




Overview
Explore in AI Chat

Overview

BEN_DSGN_RQMT_F is a core configuration table in the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. The table stores the designation requirements that govern how a participant may designate a beneficiary, dependent, or other eligible person for a benefit plan or program. Each row captures a rule set that constrains the number of designations permitted, the eligibility conditions that must be satisfied, and the relationship codes that qualify, all keyed to a specific plan, option, or plan-in-organization context.

The documented primary key is BEN_DSGN_RQMT_F_PK, composed of DSGN_RQMT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. This is a date-tracked (datetracked) effective-dated table, which means multiple versions of the same requirement can coexist, each with its own effective window. The physical schema exposes 51 columns, and the ETRM data vault classification for this object is standalone, meaning it carries no enforced foreign key relationships to other tables in the mined reference model. From a modeling perspective, this suggests treating BEN_DSGN_RQMT_F as a self-contained satellite-type entity keyed by its own surrogate identifier rather than as a hub or link in a Data Vault model, though the multiple attribute columns also support a satellite interpretation.

Key Information Stored

The following columns represent the most operationally significant data held in the table:

  • DSGN_RQMT_ID – Surrogate primary key that uniquely identifies a designation requirement record across its effective-dated versions.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – Together with DSGN_RQMT_ID, these form the composite primary key and define the validity window of each requirement version.
  • BUSINESS_GROUP_ID – Identifies the business group (legislative context) to which the requirement belongs.
  • DSGN_TYP_CD – The designation type code indicating whether the requirement applies to beneficiaries, dependents, or other designation categories.
  • GRP_RLSHP_CD – Group relationship code defining the qualifying relationship between the participant and the designated person.
  • OIPL_ID, PL_ID, OPT_ID – Foreign references to the plan, option-in-plan, and option identifiers that scope the requirement to a particular benefit offering.
  • NO_MN_NUM_DFND_FLAG / NO_MX_NUM_DFND_FLAG – Flags indicating whether a minimum or maximum number of designations has been defined.
  • MN_DPNTS_RQD_NUM / MX_DPNTS_ALWD_NUM – Numeric values for the minimum number of dependents required and the maximum number allowed.
  • CVR_ALL_ELIG_FLAG – Indicates whether the requirement covers all eligible persons.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN – Standard Oracle EBS audit columns (WHO columns) for change tracking.
  • OBJECT_VERSION_NUMBER – Optimistic locking column used by the OAF/Forms layer to detect concurrent updates.
  • DDR_ATTRIBUTE_CATEGORY and DDR_ATTRIBUTE1 through DDR_ATTRIBUTE30 – Descriptive flexfield (DFF) segments for customer-defined extensions.

Common Use Cases and Queries

BEN_DSGN_RQMT_F is typically queried during benefits configuration validation, during enrollment processing when the application must enforce designation limits, and in reporting that audits designated beneficiary/dependent rules. A typical query retrieves the currently effective requirement for a given plan and designation type:

  • Effective-dated lookup: SELECT DSGN_RQMT_ID, MN_DPNTS_RQD_NUM, MX_DPNTS_ALWD_NUM FROM BEN.BEN_DSGN_RQMT_F WHERE BUSINESS_GROUP_ID = :p_bg_id AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
  • Requirement scoping by plan: join OIPL_ID / PL_ID / OPT_ID to plan configuration tables to retrieve designation rules applying to a specific option.
  • Audit reporting: filter on LAST_UPDATE_DATE and LAST_UPDATED_BY to track who changed designation rules and when.

Because the effective-date columns participate in the primary key, all runtime queries should always constrain on the effective window to avoid returning historical or future-dated versions.

Related Objects

Although the ETRM data vault heuristic classifies this table as standalone, in practice BEN_DSGN_RQMT_F interacts with the broader Advanced Benefits configuration and enrollment model. Significant related objects include:

  • BEN_PL_F / BEN_OIPL_F – referenced via PL_ID and OIPL_ID to link the requirement to a plan or plan-in-organization.
  • BEN_OPT_F – referenced via OPT_ID for option-level designation rules.
  • BEN_PTIP_F – related through the same plan/option framework to associate the requirement with plan types.
  • BEN_ELIGY_PRFL_F – eligibility profiles that interact with CVR_ALL_ELIG_FLAG and relationship code constraints.
  • BEN_PL_GD_DFN_F – plan and option grouping definitions used alongside designation requirements.
  • BEN_PRTT_ENRT_RSLT_F – participant enrollment results that are validated against the designation rules defined here.
  • PER_BUSINESS_GROUPS – source of the BUSINESS_GROUP_ID legislative context.

Developers extending the object should treat the DFF columns (DDR_ATTRIBUTE_CATEGORY and DDR_ATTRIBUTE1–30) as the supported extension point rather than adding custom columns to the base table.