Results for “ben_elig_qual_titl_prte_f”

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

Overview

BEN_ELIG_QUAL_TITL_PRTE_F is a date-tracked (the _F suffix denotes "flex" / effective-dated) table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores eligibility qualification title and qualification-type participation records that define which person titles or qualifications are eligible for, or excluded from, a given eligibility profile. Each row is a participation rule ("prte" = participation rule entry) attached to an eligibility profile (ELIGY_PRFL_ID), enabling Benefits administrators to drive plan and program eligibility based on an employee's job title (TITLE) or a specific qualification (QUALIFICATION_TYPE_ID). The record is fully date-effective, so eligibility criteria can change over time without overwriting history.

The primary key BEN_ELIG_QUAL_TITL_PRTE_PK spans ELIG_QUAL_TITL_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. Based on the mined foreign-key structure, the table is classified heuristically as a standalone object in Data Vault terms: it is treated as a satellite-like, date-stamped detail record that hangs off the eligibility profile business key rather than acting as a pure hub or link. This classification is a modeling suggestion and reflects the absence of a documented enforced parent relationship in the extracted metadata, not a confirmed architectural constraint.

Key Information Stored

The table exposes 48 documented columns. The most significant are:

• ELIG_QUAL_TITL_PRTE_ID — surrogate identifier forming part of the primary key; the unique handle for a participation rule row.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective bounds, also part of the primary key; together with the ID they constitute the business-key candidate exposed by the unique index BEN_ELIG_QUAL_TITL_PRTE_PK.
• ELIGY_PRFL_ID — the eligibility profile to which the rule belongs; the primary business foreign-key linkage.
• BUSINESS_GROUP_ID — the operating unit / business group that owns the record, supporting multi-organization data separation.
• EXCLD_FLAG — indicates whether the title or qualification is included (N) or excluded (Y) from the eligibility profile.
• TITLE — the person title value evaluated by the rule.
• QUALIFICATION_TYPE_ID — the qualification type evaluated by the rule.
• ORDR_NUM — sequencing used to control evaluation order of competing rules.
• CRITERIA_SCORE / CRITERIA_WEIGHT — scoring attributes that feed eligibility ranking and weighted evaluation logic.
• EQT_ATTRIBUTE_CATEGORY and EQT_ATTRIBUTE1–30 — descriptive flexfield (DFF) segments capturing extensible, client-specific attributes.
• OBJECT_VERSION_NUMBER — optimistic locking control for concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard Oracle who/when audit columns.

The surrogate identifier (ELIG_QUAL_TITL_PRTE_ID) should not be confused with the business-key candidate, which is the composite of the ID plus the two effective dates.

Common Use Cases and Queries

Typical reporting needs include determining, for a given eligibility profile, all titles and qualification types that grant or deny eligibility, and identifying which profiles a specific title participates in. Analysts commonly join this table to the eligibility profile header and to the date-effective participation framework to reconstruct point-in-time eligibility rules.

• List active participation rules for a profile:

  SELECT elig_qual_titl_prte_id, title, qualification_type_id, excld_flag, ordr_num
  FROM ben.ben_elig_qual_titl_prte_f
  WHERE eligy_prfl_id = :p_profile
  AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

• Find profiles affected by a specific title:

  SELECT DISTINCT eligy_prfl_id
  FROM ben.ben_elig_qual_titl_prte_f
  WHERE title = :p_title
  AND SYSDATE BETWEEN effective_start_date AND effective_end_date
  AND NVL(excld_flag,'N') = 'N';

• Audit history—query without the date predicate or filter on EFFECTIVE_END_DATE to trace rule changes over time.
• Read the EQT_ATTRIBUTE flexfield columns to surface client-defined eligibility attributes in extracts or BI reports.

Because the table is date-effective, all production queries should constrain on the effective dates to avoid returning superseded rows.

Related Objects

• BEN_ELIG_PRFL_F — the eligibility profile header; join on ELIGY_PRFL_ID to resolve the profile context.
• BEN_ELIG_QUAL_TITL_PRTE — the corresponding date-effective base/flex view exposing the same business rows for form and API access.
• BEN_ELIGY_PRFL (eligibility profile) family of tables — the parent construct of participation rules.
• PER_TITLES / job-title definitions — source of the TITLE values referenced by the rule.
• Qualification type definitions (BEN qualification/eligibility setup entities) — source of QUALIFICATION_TYPE_ID values.
• Benefits eligibility evaluation APIs (e.g., the BEN eligibility engine and BEN_ELIG APIs) — consume these rows at eligibility run time.
• FND/FLEX DFF definition tables — resolve EQT_ATTRIBUTE_CATEGORY and the EQT_ATTRIBUTE1–30 descriptive flexfield segments.

These references reflect the documented metadata; additional lookups may exist through the eligibility engine logic that is not captured in the extracted FK structure.