Results for “ben_bnft_rstrn_ctfn_f”

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

Overview

BEN_BNFT_RSTRN_CTFN_F is a date-tracked (effective-dated) table in the BEN schema belonging to the Oracle Advanced Benefits (BEN) module. It stores the certifications that a participant must satisfy in order to meet level restrictions applied to a compensation object. In the Oracle Benefits configuration model, a compensation object is any benefit offering, option, or plan that a participant may elect, and level restrictions determine eligibility tiers or participation rules for that object. This table records which certification, and under what conditions, is required to clear a given restriction level.

The table carries the "benefit relationship" semantics typical of Advanced Benefits configuration store: it defines a rule relating a certification type to a restriction context. Its heuristic Data Vault classification, mined from the foreign-key structure, is standalone. As a modeling suggestion, this indicates that the table is not a conventional hub, link, or satellite in a Kimball-style vault design but rather a self-contained configuration record keyed by its own effective-dated identity; integrators modeling Benefits configuration for warehousing should treat it as an independent reference entity rather than a dependent satellite of a parent hub.

Key Information Stored

The table contains 45 documented columns in the ETRM 12.2.2 physical schema. The most significant are:

• BNFT_RSTRN_CTFN_ID — the surrogate primary key for the restriction-certification record; it forms the leading column of the composite primary key BEN_BNFT_RSTRN_CTFN_F_PK.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the date-track columns that, together with the surrogate key, complete the primary key and enable historical versioning of the restriction rule.
• BUSINESS_GROUP_ID — the enterprise or business group that owns the configuration row; the standard multi-tenant discriminator in HR/Benefits tables.
• RQD_FLAG — a flag indicating whether the certification is required for the associated restriction.
• ENRT_CTFN_TYP_CD — the enrollment certification type code, identifying which certification is being referenced.
• CTFN_RQD_WHEN_RL — the rule that determines when the certification requirement is triggered.
• PL_ID — the plan identifier that ties the certification requirement to a specific benefit plan.
• BRC_ATTRIBUTE_CATEGORY and BRC_ATTRIBUTE1 through BRC_ATTRIBUTE30 — the descriptive flexfield (DFF) segment columns for capturing client-specific certification detail.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — the standard Oracle WHO columns supporting audit and optimistic locking via OBJECT_VERSION_NUMBER.

The documented unique index BEN_BNFT_RSTRN_CTFN_F_PK (BNFT_RSTRN_CTFN_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the business-key candidate establishing record uniqueness across effective dates.

Common Use Cases and Queries

Typical reporting and integration scenarios include retrieving the certifications currently required for a plan or restriction level, and reconstructing historical requirement sets as of a given date.

• Current certifications required for a plan: select from BEN_BNFT_RSTRN_CTFN_F where PL_ID equals the target plan and SYSDATE between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, filtering on RQD_FLAG = 'Y'.
• Point-in-time reconstruction: constrain EFFECTIVE_START_DATE <= :as_of_date AND EFFECTIVE_END_DATE >= :as_of_date to reproduce the configuration as it stood historically — essential for benefits eligibility audits.
• Certification-type analysis: group by ENRT_CTFN_TYP_CD to determine which certifications drive the most level restrictions across plans.
• DFF extraction: query BRC_ATTRIBUTE_CATEGORY to identify which flexfield context is in use, then pivot relevant BRC_ATTRIBUTEn segments for client-specific requirements.
• Change tracking: use LAST_UPDATE_DATE, LAST_UPDATED_BY, and OBJECT_VERSION_NUMBER to drive incremental extracts into a reporting warehouse.

Related Objects

Because the table is classified as standalone, it has no mined foreign-key dependencies; relationships are therefore inferred functionally through shared configuration keys.

• BEN_BNFT_RSTRN_F — the benefit restriction table that this certification requirement qualifies, joined through restriction/level identifiers.
• BEN_PL_F — the plan definition table, joined on PL_ID, providing plan-level context.
• BEN_ENRT_CTFN_TYP — the enrollment certification type lookup referenced by ENRT_CTFN_TYP_CD.
• BEN_BENEFIT_ACTION — the runtime benefits action table where certification requirements are enforced during enrollment.
• FND_FLEX_VALUES_VL / FND_FLEX_VALUE_SETS — the value sets backing the BRC_ATTRIBUTE DFF segments.
• Advanced Benefits PL/SQL APIs such as BEN_RSTRN_CTFN_API (where present) that create and maintain these restriction-certification records programmatically.