Search Results ben_regn_f_pk

Overview

BEN_REGN_F is the regulations table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. The table stores the legislative and regulatory definitions that govern benefit program eligibility, enrollment rules, and plan configuration across a business group. Regulations in Oracle Advanced Benefits represent jurisdiction-specific requirements—such as statutory eligibility waiting periods, mandatory coverage rules, or government-mandated plan provisions—that the application evaluates during benefits processing. Records are date-tracked using effective start and end dates, enabling historical and future-dated regulatory configurations to coexist. The table resides in the BEN schema and is documented as VALID in the ETRM repository.

From a Data Vault modeling perspective, the mined FK structure classifies BEN_REGN_F as a standalone object. This suggests it could be modeled as a hub (or a hub with an attached satellite) keyed on the regulation identifier, because no foreign-key dependencies to other tables were identified. The heuristic nature of this classification should be validated against actual application usage before adopting it in a warehouse design.

Key Information Stored

The table contains 44 documented columns. The most significant are:

• REGN_ID — Surrogate identifier for the regulation record. It participates in the composite primary key.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — Date-tracked validity bounds forming the remainder of the primary key, enabling multiple versions of a regulation over time.
• NAME — The descriptive name of the regulation.
• STTRY_CITN_NAME — The statute or citation name associated with the regulation, typically the legal reference text.
• BUSINESS_GROUP_ID — The business group (legislative data group) that owns the regulation, supporting multi-tenant configuration within a single instance.
• REG_ATTRIBUTE_CATEGORY and REG_ATTRIBUTE1 through REG_ATTRIBUTE30 — The descriptive flexfield (DFF) columns that hold customer-specific or configuration-specific attributes beyond the seeded regulation fields.
• ORGANIZATION_ID — Organization context for the regulation record, where organization-level partitioning applies.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the application to detect concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle EBS audit columns recording who created and last modified each row.

The unique index BEN_REGN_F_PK on (REGN_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) identifies the business-key candidate. REGN_ID alone serves as the logical surrogate key, while the date pair supplies the temporal dimension of the primary key.

Common Use Cases and Queries

Typical scenarios include auditing the regulatory setup for a business group, extracting the current active regulation definitions for a given date, and reconciling DFF values configured by implementation teams. A query to retrieve currently effective regulations for a business group follows:

• SELECT regn_id, name, sttry_citn_name FROM ben.ben_regn_f WHERE business_group_id = :p_bg_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• SELECT regn_id, name, effective_start_date, effective_end_date FROM ben.ben_regn_f WHERE regn_id = :p_regn_id ORDER BY effective_start_date; — retrieves the full date-tracked history of a single regulation.
• DFF extraction: SELECT regn_id, reg_attribute_category, reg_attribute1 FROM ben.ben_regn_f WHERE reg_attribute_category IS NOT NULL;

Reporting use cases include regulatory compliance reports, configuration migration extracts, and Data Vault loads where the table supplies a hub plus an effective-dated satellite.

Related Objects

The mined relationship data for BEN_REGN_F is standalone, meaning no foreign keys were identified within the documented FK structure. Consequently, related objects are inferred from the BEN module context rather than from declared constraints:

• BEN_REGN_F_PK — the primary key index on REGN_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.
• BEN_BUSINESS_GROUPS (or the business group entity referenced by BUSINESS_GROUP_ID) — governs the ownership scope of each regulation.
• BEN_PRTT_ENRT_RSLT_F and related participation/enrollment result tables — consume regulation definitions during eligibility evaluation.
• BEN_PL_F and BEN_PGM_F — plan and program definitions that reference applicable regulations.
• HR_ORGANIZATION_UNITS — referenced through ORGANIZATION_ID for organization-level context.
• Public BEN APIs that create or maintain regulation records, invoked through the Advanced Benefits setup and configuration flows.

Because the FK structure is documented as standalone, any joins to dependent objects should be verified against the specific deployment's configuration and the relevant BEN API documentation before being relied upon in production reporting.