Search Results ben_pgm_f_pk




Overview
Explore in AI Chat

Overview

The BEN_PGM_F table is a core data object within the Oracle E-Business Suite Advanced Benefits module (BEN). It serves as the master repository for defining and storing benefit programs. A benefit program is a foundational concept in Advanced Benefits, acting as a container that groups related benefit plans and defines the eligibility rules, participation options, and administrative policies under which employees can enroll. This table is date-effective, as indicated by the '_F' suffix and the presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns, allowing for the management of program definitions over time. Its role is central to the configuration and operation of the benefits system, as every enrollment transaction and eligibility check ultimately traces back to a program defined in this table.

Key Information Stored

The table's structure is designed to capture the essential attributes of a benefit program. The primary key is a composite of PGM_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, enforcing data integrity for historical records. While the provided metadata does not list all columns, standard columns in such a table typically include:

  • PGM_ID: The unique system-generated identifier for the benefit program.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE: Define the date range during which this specific version of the program definition is active.
  • NAME and DESCRIPTION: The internal and display names for the program.
  • Columns controlling program behavior, such as those for enrollment deadlines, default rules, and eligibility determination methods.
  • Audit columns (e.g., CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) are standard in Oracle EBS tables.

Common Use Cases and Queries

This table is primarily referenced for configuration reporting, data validation, and integration tasks. Common operational queries involve retrieving active program definitions for a given date, such as during an open enrollment period. A typical query pattern is:
SELECT name, description FROM ben.ben_pgm_f WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date;
It is also frequently joined in reports that link programs to their constituent plans (via tables like BEN_PGM_PL_F) or to employee enrollments. Technical consultants and DBAs may query this table to validate program setups before launching enrollment campaigns or to troubleshoot data issues by verifying the effective-dated history of a specific PGM_ID. The table is less commonly queried directly by end-users, who typically interact with programs through the dedicated Oracle HRMS or Benefits Manager user interfaces.

Related Objects

BEN_PGM_F is a central hub within the Advanced Benefits data model. Key related objects include:

  • BEN_PGM_PL_F: The intersection table that associates benefit programs with specific plans, defining which plans are offered under which program.
  • BEN_PL_F: The master table for benefit plans, which are linked to programs via BEN_PGM_PL_F.
  • BEN_PRTN_ELIG_PRFL_F: Likely related for storing program-specific participation eligibility profiles.
  • BEN_POPL_F: The population table, as programs are often assigned to specific populations of employees.
  • Various Benefits APIs and seeded concurrent programs will internally reference BEN_PGM_F to process enrollments and determine eligibility.

The primary key constraint BEN_PGM_F_PK ensures referential integrity for any child tables that foreign key back to this table's PGM_ID and effective dates.