Search Results ben_popl_enrt_typ_cycl_f




Overview
Explore in AI Chat

Overview

BEN_POPL_ENRT_TYP_CYCL_F is a date-tracked (effective-dated) table in the BEN schema of Oracle Advanced Benefits (Oracle EBS 12.1.1 and 12.2.2). The suffix "_F" denotes a date-effective ("F" for "effFective") table, meaning every logical row is versioned across EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. Its documented purpose is to record the enrollment cycle allowed for a program or plan: which enrollment type cycles a given program (PGM_ID) or plan (PL_ID) may use, and under which attributes. It is a configuration and setup table rather than a transactional one, and is typically maintained during benefits plan design through the Advanced Benefits setup flows.

The ETRM metadata classifies the object heuristically as standalone in Data Vault terms. This should be read as a modeling suggestion: the table carries no mined foreign-key links to other hub or link entities in the documented relationship data. A practical Data Vault treatment would place it as a satellite or reference structure keyed on its business attributes, given the absence of enforced relationships, but the metadata does not assert a hub/link classification.

Key Information Stored

The table defines 44 columns. The documented primary key is BEN_POPL_ENRT_TYP_CYCL_F_PK, composed of POPL_ENRT_TYP_CYCL_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.

  • POPL_ENRT_TYP_CYCL_ID — the surrogate (system-generated) identifier for the enrollment type cycle record; the leading column of the primary key and the value users reference when searching for "popl_enrt_typ_cycl_id".
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective boundaries that complete the primary key and version each logical record. Datetracked queries must filter on these to return the row valid at a point in time.
  • BUSINESS_GROUP_ID — the HR business group (enterprise) owning the configuration, used for multi-organization security.
  • ENRT_TYP_CYCL_CD — the enrollment type cycle code, the business attribute identifying the cycle (e.g., a named enrollment window or periodic cycle).
  • PL_ID — the plan identifier; populated when the cycle applies at plan level.
  • PGM_ID — the program identifier; populated when the cycle applies at program level.
  • PET_ATTRIBUTE_CATEGORY — the descriptive flexfield context for the row.
  • PET_ATTRIBUTE1 … PET_ATTRIBUTE30 — the descriptive flexfield segments used by implementers to capture customer-specific cycle attributes.
  • OBJECT_VERSION_NUMBER — optimistic-locking version for concurrent update control.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns for change tracking.

Common Use Cases and Queries

The table is queried to determine which enrollment cycle is valid for a program or plan, to audit cycle configuration, and to report setup data. A typical current-version lookup joins plan and program:

  • Retrieve all cycles for an enterprise: SELECT enrt_typ_cycl_cd, pl_id, pgm_id, effective_start_date, effective_end_date FROM ben.ben_popl_enrt_typ_cycl_f WHERE business_group_id = :p_bg_id, filtered by TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date.
  • Find the cycle for a specific plan: filter on pl_id = :p_pl_id with the effective-date predicate above.
  • As-of-date reporting uses the same predicate with a bind variable rather than SYSDATE to reproduce historical configuration.
  • Extract flexfield values by selecting PET_ATTRIBUTE_CATEGORY and the relevant PET_ATTRIBUTE segments for customer-specific reporting.

Because the object is date-effective, queries that omit the effectiveness filter return every historical version and inflate result sets — a common cause of duplicate rows in Fast Formula or extract logic.

Related Objects

The ETRM metadata documents no foreign-key relationships for this table. Based on the column semantics, the most significant objects commonly referenced alongside it are:

  • BEN_PGM_F — the program definition, joined on PGM_ID, to resolve program names.
  • BEN_PL_F — the plan definition, joined on PL_ID, to resolve plan names.
  • BEN_ENRT_TYP_CYCL (logical enrollment type cycle definition) — referenced through ENRT_TYP_CYCL_CD as the code source.
  • BEN_POPL_ENRT_TYP_CYCL_F_PK — the unique index implementing the primary key and enforcing datetrack uniqueness.
  • HR_ALL_ORGANIZATION_UNITS — joined on BUSINESS_GROUP_ID to resolve business group identity for security and reporting.
  • FND_FLEX_VALUES — used to resolve PET_ATTRIBUTE segment values against the flexfield value sets.