Results for “ben_elig_othr_ptip_prte_f”

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

Overview

BEN_ELIG_OTHR_PTIP_PRTE_F is an Oracle Advanced Benefits (BEN) table that records the eligibility criteria an employee or other compensation object must satisfy across other plan types in order to be included in or excluded from a given compensation object. In the Oracle EBS 12.1.1 and 12.2.2 data models, this table forms part of the eligibility profile framework that drives participation processing, plan enrollment, and compensation object calculation.

The table is owned by the BEN schema and is registered in the Enterprise Technology Reference Model (ETRM) with 46 documented columns. Its documented primary key is BEN_ELIG_OTHR_PTIP_PRTE_PK, defined on the combination of ELIG_OTHR_PTIP_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, confirming that it is a date-tracked (effective-dated) entity. A single foreign key is documented: BUSINESS_GROUP_ID references HR_ALL_ORGANIZATION_UNITS, tying each row to a specific business group for multi-tenant and legislative data separation.

Based on the mined foreign key structure, the Data Vault classification for this object is satellite-leaning. In Data Vault terms, this should be viewed as a modeling suggestion: the table behaves as an attribute-bearing satellite hanging off a business key lineage (the eligibility profile / plan type interaction) rather than as an independent hub or a pure link table.

Key Information Stored

The table stores configuration-level eligibility rules rather than transactional enrollment data. The most significant columns are:

• ELIG_OTHR_PTIP_PRTE_ID — Surrogate primary key component; uniquely identifies the eligibility restriction row (in combination with the effective dates).
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range columns that, together with the surrogate key, form the documented unique index BEN_ELIG_OTHR_PTIP_PRTE_PK and represent the business-key candidate for the row.
• PTIP_ID — Identifies the plan type in plan (PTIP) associated with the "other plan type" eligibility condition. This is the core reference that defines which other plan type is being evaluated.
• ELIGY_PRFL_ID — Foreign reference to the eligibility profile to which this restriction belongs, linking the row into the broader eligibility profile construct.
• EXCLD_FLAG — Indicates whether the rule causes inclusion or exclusion with respect to the compensation object.
• ONLY_PLS_SUBJ_COBRA_FLAG — Controls whether the criteria apply only to persons subject to COBRA, a common benefits-compliance constraint.
• BUSINESS_GROUP_ID — Foreign key to HR_ALL_ORGANIZATION_UNITS; scopes the row to a business group.
• ORDR_NUM — Ordering sequence used when multiple eligibility restrictions must be evaluated in a defined order.
• EOY_ATTRIBUTE_CATEGORY and EOY_ATTRIBUTE1 through EOY_ATTRIBUTE30 — Descriptive flexfield (DFF) context and segment columns for customer-defined attributes.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle who-columns supporting concurrency control and audit.

Common Use Cases and Queries

Typical usage centers on benefits configuration validation and eligibility troubleshooting. Administrators and technical consultants query this table to determine which other plan types are required for a compensation object, and whether that requirement is inclusive or exclusive.

A representative query retrieves all restrictions for a given eligibility profile:

• SELECT e.elig_othr_ptip_prte_id, e.ptip_id, e.exclD_flag, e.ordr_num FROM ben.ben_elig_othr_ptip_prte_f e WHERE e.eligy_prfl_id = :p_profile_id AND TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date ORDER BY e.ordr_num;

To resolve the owning business group and enforce multi-org security, the query is joined to HR_ALL_ORGANIZATION_UNITS on BUSINESS_GROUP_ID. Reporting use cases include generating eligibility rule matrices, auditing exclusion flags across plan types, and validating that DFF attributes (EOY_ATTRIBUTE_CATEGORY and segments) are populated consistently. Because the table is effective-dated, any production query should filter on the effective date range to avoid returning historical or future-dated rule versions.

Related Objects

The following objects are most closely associated with BEN_ELIG_OTHR_PTIP_PRTE_F:

• HR_ALL_ORGANIZATION_UNITS — Referenced via the documented foreign key BEN_ELIG_OTHR_PTIP_PRTE_F.BUSINESS_GROUP_ID, supplying the business group context.
• BEN_ELIG_OTHR_PTIP_PRTE_PK — The primary key / unique index that enforces uniqueness across ELIG_OTHR_PTIP_PRTE_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE.
• BEN_ELIGY_PRFL — Parent eligibility profile definition, referenced through ELIGY_PRFL_ID.
• BEN_PTIP (plan type in plan) — Referenced through PTIP_ID to identify the other plan type used in the rule.
• BEN_ELIG_*_PRTE_F family tables — Sibling eligibility restriction tables (for example, restriction tables keyed by other criteria) that share the same eligibility profile parent and effective-dating conventions.

Together these objects form the eligibility rule graph that the Advanced Benefits engine evaluates during participation and compensation object processing.