Search Results pgm_stat_cd




Overview
Explore in AI Chat

Overview

BEN_PGM_F is the foundational table of the Oracle Advanced Benefits (BEN) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of a benefit program — the top-level container that groups together the plan types, plans, options, eligibility rules, and enrollment parameters that an organization offers to its employees. Every other configuration object in Advanced Benefits ultimately hangs off a row in this table, making BEN_PGM_F the anchor of the benefits setup hierarchy. The table is date-tracked: it uses the standard EBS effective dating pattern, so multiple versions of the same program can coexist as historical, current, and future rows distinguished by their effective date ranges.

From a Data Vault modeling perspective, the metadata classifies BEN_PGM_F as a standalone object, which suggests treating it as a hub rather than a dependent link or satellite. In practice, however, the effective-dated columns and the large attribute set mean it behaves much like a hub combined with a versioned satellite. The primary key BEN_PGM_F_PK is composed of PGM_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming that PGM_ID alone is not unique — it repeats once per dated version.

Key Information Stored

The table contains 116 documented columns. The most significant include the surrogate identifier and version keys:

The unique index BEN_PGM_F_PK is the only documented business-key candidate. PGM_ATTRIBUTE1 through PGM_ATTRIBUTE30 provide the standard descriptive flexfield extension columns.

Common Use Cases and Queries

Typical usage centres on selecting the currently effective version of a program and joining it to plan-level setup. Because of date tracking, every query should filter on SYSDATE between the effective dates:

  • Retrieve the active program definition: 
    SELECT pgm_id, name, pgm_stat_cd, legislation_code
FROM   ben.ben_pgm_f
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
  • Enumerate all dated versions of a single program to audit history: 
    SELECT pgm_id, effective_start_date, effective_end_date, name
FROM   ben.ben_pgm_f
WHERE  pgm_id = :p_pgm_id
ORDER  BY effective_start_date;
  • List programs for a specific business group and legislation, used in configuration reports and LOV queries.
  • Reporting on default programs via DFLT_PGM_FLAG, or on unrestricted-enrollment programs via ALWS_UNRSTRCTD_ENRT_FLAG.

Because PGM_ID recurs per version, developers must never assume it is a unique key in ad-hoc joins; the correct join predicate always includes the effective date range overlap condition.

Related Objects

BEN_PGM_F is referenced as a parent by the plan-level and program-derivative setup tables in the BEN schema. The most significant related objects join on PGM_ID (with effective-date correlation):

  • BEN_PGM_PL_TYP_F — associates plan types with a program.
  • BEN_PGM_PL_F — associates individual plans to a program.
  • BEN_PGM_OPT_F — program-to-option relationships.
  • BEN_PGM_ENRT_RT_F — enrollment and rate configuration for the program.
  • BEN_PGM_ELIG_F — eligibility rules defined at program level.
  • BEN_ELIGY_PRFL_F — eligibility profiles consumed by the program.
  • BEN_PGM_LOV and related views — runtime LOV and validation views exposing program values.
  • BEN_PGM_F is also read by the enrollment and Life Events processing APIs, which require the effective program row when participants enroll or change elections.

Any interface writing to these children must first establish the correct effective-dated PGM_ID row in BEN_PGM_F, since the primary key dependency flows downward through the benefits setup hierarchy.