Search Results ben_ttl_prtt_rt_f




Overview
Explore in AI Chat

Overview

BEN_TTL_PRTT_RT_F is a date-tracked (non-"_TL") configuration table owned by the BEN schema within the Oracle E-Business Suite Advanced Benefits (BEN) module. It stores the maximum and minimum allowed number of enrolled participants for a given compensation object, along with the rules that determine whether an excess or shortfall disqualifies the object from a benefit plan. The table is part of the Benefits plan-design and rate/eligibility configuration layer rather than the transactional enrollment layer.

Its name follows the EBS convention where the "_F" suffix denotes a datetrack (effective-dated) table. Effective dating allows the business to change participant-count rules over time without losing the history that governed past plan years. The ETRM 12.2.2 documented schema lists 50 columns, and the primary key BEN_TTL_PRTT_RT_PK is composed of TTL_PRTT_RT_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.

The heuristic Data Vault classification mined from the foreign-key structure is standalone, meaning the object shows no strongly mined parent-child links in the documented relationship data. In Data Vault terms this suggests modeling the table as an independent hub or satellite, with the effective date range acting as a natural change-tracking dimension, rather than as an integrated link table.

Key Information Stored

The most significant columns in this table are:

  • TTL_PRTT_RT_ID — the surrogate identifier for the participant-rate rule definition; combined with the effective dates it forms the primary key BEN_TTL_PRTT_RT_PK.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack validity window that governs when the rule applies.
  • BUSINESS_GROUP_ID — the business group (enterprise) that owns the configuration row.
  • EXCLD_FLAG — indicates whether the participant-count rule is excluded or applies as an exclusion criterion.
  • MN_PRTT_NUM — the minimum participant count permitted for the compensation object.
  • MX_PRTT_NUM — the maximum participant count permitted.
  • NO_MN_PRTT_NUM_APLS_FLAG — controls whether a minimum-participant rule is enforced at all.
  • NO_MX_PRTT_NUM_APLS_FLAG — controls whether a maximum-participant rule is enforced at all.
  • VRBL_RT_PRFL_ID — links the rule to a variable rate profile where participant-count thresholds drive rate variation.
  • PRTT_DET_CD / PRTT_DET_RL — coded participant-detail and associated rule fields used by the benefits engine.
  • ORDR_NUM — ordering/sequencing attribute for evaluation.
  • LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard EBS audit and optimistic-locking columns.
  • TTP_ATTRIBUTE_CATEGORY and TTP_ATTRIBUTE1–30 — the standard descriptive flexfield (DFF) context and segment columns.

The surrogate key (TTL_PRTT_RT_ID plus effective dates) should be distinguished from the business key: the ETRM metadata documents only one unique index, BEN_TTL_PRTT_RT_PK, so the surrogate PK is itself the only documented uniqueness candidate.

Common Use Cases and Queries

Typical scenarios include auditing participant-count eligibility rules for a compensation object, verifying active rules for a given plan year, and extracting DFF values for downstream reporting. The datetrack predicate must always be applied, for example:

  • Fetch the currently effective minimum and maximum for a rule: SELECT ttl_prtt_rt_id, mn_prtt_num, mx_prtt_num FROM ben_ttl_prtt_rt_f WHERE ttl_prtt_rt_id = :p_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date.
  • Enumerate all rules for a business group: SELECT ttl_prtt_rt_id, ordr_num, exld_flag FROM ben_ttl_prtt_rt_f WHERE business_group_id = :bg AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date ORDER BY ordr_num.
  • Compare historical rule versions: SELECT ttl_prtt_rt_id, effective_start_date, mn_prtt_num, mx_prtt_num, no_mx_prtt_num_apls_flag FROM ben_ttl_prtt_rt_f WHERE ttl_prtt_rt_id = :p_id ORDER BY effective_start_date DESC.
  • Pull flexfield context: SELECT ttp_attribute_category, ttp_attribute1, ttp_attribute2 FROM ben_ttl_prtt_rt_f WHERE ttl_prtt_rt_id = :p_id.

These queries support compliance reporting on participant-count minimums, reconciliation against enrollment counts, and diagnosis of rate discrepancies driven by VRBL_RT_PRFL_ID thresholds.

Related Objects

The ETRM metadata classifies this table as standalone, so no mined foreign-key parents are documented. Practically, the following objects are the most significant neighbours and are joined on the columns noted:

  • BEN_VRBL_RT_PRFL_F — the variable rate profile referenced through VRBL_RT_PRFL_ID.
  • BEN_PL_F — the benefit plan that consumes the participant-count rules through the rate profile.
  • BEN_PGM_F — the program grouping related plans and their participant rules.
  • BEN_COMP_OBJ_F — compensation objects whose eligibility depends on these rules.
  • BEN_PTIP_F — plan-type-level configuration linked through the rate profile.
  • BEN_ELIG_*_F — the eligibility determinate tables that reference compensation objects governed by these rules.
  • BEN_RT_GRP_F / BEN_RT_GRP_RL_F — rate groups and rate-group rules that resolve the effective rate for a participant count.
  • BEN_TTL_PRTT_RT_F child indexes — the unique index BEN_TTL_PRTT_RT_PK is the sole documented access path.
  • FND_FLEX_VALUES_VL — describes the ttp_attribute DFF segments stored in the table.

All joins should enforce the datetrack overlap condition between effective_start_date and effective_end_date so that rule lookups return the single version valid on the transaction date.