Results for “prg_attribute1”

34 results




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

Overview

BEN_PL_REGN_F is a legislative regulations table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It stores the set of legislative regulations that a benefits plan must adhere to, effectively binding a plan (PL_ID) to a reporting group (RPTG_GRP_ID) and a regulatory plan type (REGY_PL_TYP_CD). Because benefits offerings in the United States and other legislated jurisdictions are subject to statutory constraints — nondiscrimination testing, highly compensated employee treatment, and similar compliance rules — this table captures the regulation configuration that drives eligibility and compliance processing for a given plan.

The object is date-tracked: it carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE as part of its primary key (BEN_PL_REGN_F_PK), so multiple temporal versions of the same regulation record may coexist. The "_F" suffix denotes a date-effective (datetrack) table, consistent with the EBS convention that such tables include WHO columns and an OBJECT_VERSION_NUMBER for optimistic locking. Under the heuristic Data Vault classification mined from its foreign-key structure, this table is satellite-leaning: it hangs off a parent business entity and records descriptive, time-variant attributes, so it is best modeled as a satellite keyed to its plan or regulation hub rather than as a hub or link in its own right.

Key Information Stored

The table contains 50 documented columns. Its primary key is composed of PL_REGN_ID (the surrogate identifier), EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE; the same three columns form the business-key candidate exposed by the unique index BEN_PL_REGN_F_PK. Among the remaining attributes, the most significant are:

Common Use Cases and Queries

The table is primarily consumed by benefits compliance and reporting logic rather than by day-to-day enrollment screens. Typical queries resolve the currently effective regulation row for a plan at a point in time, honoring the datetrack columns:

  • Current regulation for a plan: SELECT * FROM ben.ben_pl_regn_f WHERE pl_id = :p_plan AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Compliance rule extraction: select HGHLY_COMPD_DET_RL, KEY_EE_DET_RL, CNTR_NNDSCRN_RL, CVG_NNDSCRN_RL, and FIVE_PCT_OWNR_RL for a plan to audit nondiscrimination and highly compensated configuration.
  • Reporting-group analysis: join to BEN_RPTG_GRP on RPTG_GRP_ID to list all plans sharing a reporting group and their regulation types.
  • Change history: group by PL_REGN_ID ordered by EFFECTIVE_START_DATE to reconstruct the datetrack history of regulation assignments.
  • Flexfield reporting: query PRG_ATTRIBUTE_CATEGORY with PRG_ATTRIBUTE1–30 to surface client-configured regulation attributes.

Related Objects

  • BEN_RPTG_GRP — referenced through the documented foreign key BEN_PL_REGN_F.RPTG_GRP_ID; the parent reporting-group entity.
  • BEN_PL_F — the benefits plan date-effective table, joined on PL_ID to resolve plan attributes.
  • BEN_REGN_F — the legislative regulation definition, joined on REGN_ID.
  • BEN_PL_REGN_F_PK — the primary-key/unique index enforcing uniqueness of PL_REGN_ID with the effective date range.
  • BEN_PL_TYP_F / BEN_PGM_F — plan type and program parents used to traverse the benefits hierarchy alongside this table.

Because the record set is small and configuration-oriented, BEN_PL_REGN_F is usually read-only in production and is best accessed through the delivered Advanced Benefits plan configuration forms and the BEN plan APIs that maintain the datetracked regulation rows.