Search Results ben_elig_py_bss_prte_f




Overview
Explore in AI Chat

Overview

BEN_ELIG_PY_BSS_PRTE_F is an Advanced Benefits (BEN) transactional table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores pay basis eligibility criteria within benefit eligibility profiles. It identifies the pay basis required for inclusion or exclusion of a person or compensation object when evaluating whether an employee qualifies for a benefit program, plan, or option. Specifically, the table defines pay-basis-level rules that allow the eligibility engine to include or exclude participants based on the pay basis assigned to their compensation object.

The table is date-tracked and follows the standard EBS effective-dating pattern through EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. It resides in the BEN schema and forms part of the eligibility criteria group of tables used by the Benefits eligibility engine, alongside criteria tables for employment category, service, compensation, and other attributes.

Based on the heuristic Data Vault classification provided in the metadata, BEN_ELIG_PY_BSS_PRTE_F is classified as a standalone table. From a Data Vault modeling perspective, this suggests treating the table as a satellite containing descriptive eligibility attributes, with the natural business key (ELIG_PY_BSS_PRTE_ID plus effective dates) serving as the grain anchor, rather than as a hub or link.

Key Information Stored

The table contains 47 documented columns. The most significant are summarized below.

  • ELIG_PY_BSS_PRTE_ID – Surrogate primary key that uniquely identifies each pay basis eligibility rule record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – Date-tracked validity range for the rule, forming part of the composite primary key.
  • ELIGY_PRFL_ID – Foreign key to the parent eligibility profile (BEN_ELIGY_PRFL_F) to which this pay basis criterion belongs.
  • PAY_BASIS_ID – Foreign key referencing PER_PAY_BASES, identifying the pay basis considered by the rule. This is the core business column of the table.
  • EXCLD_FLAG – Inclusion/exclusion indicator determining whether the criterion includes or excludes matching pay bases.
  • BUSINESS_GROUP_ID – Identifies the business group (operating unit) context for the rule.
  • ORDR_NUM – Ordering sequence used when evaluating multiple criteria within an eligibility profile.
  • CRITERIA_SCORE / CRITERIA_WEIGHT – Scoring and weighting values used when the eligibility profile supports scored or weighted evaluation logic.
  • EPB_ATTRIBUTE_CATEGORY, EPB_ATTRIBUTE1 … EPB_ATTRIBUTE30 – Descriptive flexfield columns (30 attribute segments plus a category) reserved for customer-defined extensibility.
  • OBJECT_VERSION_NUMBER – Optimistic locking version column for concurrent update control.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE – Standard audit columns.

The unique index BEN_ELIG_PY_BSS_PRTE_F_PK is defined on ELIG_PY_BSS_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, establishing the surrogate-plus-dates combination as the business-key candidate. The only documented foreign key relationship is PAY_BASIS_ID → PER_PAY_BASES.

Common Use Cases and Queries

Typical uses include diagnosing why a participant was included or excluded from a benefit plan, reporting on the pay bases configured for a given eligibility profile, and validating configuration during implementation or support.

To retrieve active pay basis criteria for a specific eligibility profile:

  • SELECT ELIG_PY_BSS_PRTE_ID, PAY_BASIS_ID, EXCLD_FLAG, ORDR_NUM
    FROM BEN_ELIG_PY_BSS_PRTE_F
    WHERE ELIGY_PRFL_ID = :p_profile_id
    AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

To resolve the pay basis name joined from the lookup source:

  • SELECT p.PAY_BASIS_NAME, c.EXCLD_FLAG
    FROM BEN_ELIG_PY_BSS_PRTE_F c, PER_PAY_BASES p
    WHERE c.PAY_BASIS_ID = p.PAY_BASIS_ID
    AND c.ELIGY_PRFL_ID = :p_profile_id;

These queries support reconciliation, interface troubleshooting, and audit reporting where pay-basis-driven eligibility must be explained.

Related Objects

  • PER_PAY_BASES – Referenced via PAY_BASIS_ID; supplies the pay basis definition.
  • BEN_ELIGY_PRFL_F – Parent eligibility profile table linked through ELIGY_PRFL_ID.
  • BEN_ELIG_PY_BSS_PRTE – Translated/related view or table mirroring the _F table for the eligibility criterion.
  • BEN_ELIG_PY_BSS_PRTE_F_PK – Unique index supporting the primary key.
  • BEN_ELIG_PY_BSS_PRTE_F consumer views — Denormalized eligibility profile views used in the Benefits self-service flows.
  • BEN_ELIG_PY_BSS_PRTE_F DFF context — the EPB_ATTRIBUTE_CATEGORY and segment columns integrate with the descriptive flexfield definition registered against the table.