Search Results ben_dpnt_othr_ptip_rt_f




Overview
Explore in AI Chat

Overview

BEN_DPNT_OTHR_PTIP_RT_F is a dated, effective-dated rate definition table within the Oracle Advanced Benefits (BEN) module. It stores the rate that applies to a dependent who is enrolled in a plan type other than the one being processed, evaluated within the context of a specific program. In practical terms, this table captures the cross-plan-type dependent rate rules that drive dependent coverage pricing when a participant's dependent is covered under a separate plan type within the same benefits program.

The table resides in the BEN schema and, in Oracle EBS 12.1.1 and 12.2.2, is classified as VALID. It carries a DateTrack-enabled design, evidenced by the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns as part of its primary key. From a Data Vault modeling perspective, the documented metadata describes this object as standalone (no foreign keys mined from the FK structure), which suggests it is best modeled as a standalone reference or rate table rather than as a hub or link in a conventional Data Vault sense. Where integration with other entities is required, relationships are established through logical business keys (such as PTIP_ID) rather than enforced foreign keys.

Key Information Stored

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

  • DPNT_OTHR_PTIP_RT_ID — Surrogate identifier for the rate record. It forms part of the primary key.
  • EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — DateTrack effective-dating columns. Together with DPNT_OTHR_PTIP_RT_ID, they constitute the primary key BEN_DPNT_OTHR_PTIP_F_PK, which is also the unique index and the business-key candidate.
  • PTIP_ID — Identifies the plan type in program (PTIP) context to which the dependent rate rule applies.
  • VRBL_RT_PRFL_ID — References the variable rate profile that supplies the actual rate value for this dependent cross-plan-type rule.
  • EXCLD_FLAG — Indicates whether the record is excluded from processing.
  • ORDR_NUM — Ordering sequence used to control the priority or display order of related rate rules.
  • BUSINESS_GROUP_ID — Identifies the business group (legislative/data group) owning the record, a standard multi-tenancy discriminator in HR and Benefits tables.
  • DOT_ATTRIBUTE_CATEGORY and DOT_ATTRIBUTE1 through DOT_ATTRIBUTE30 — Descriptive flexfield (DFF) context and attribute columns used for customer-specific extensions.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard WHO audit columns.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used during DML.

The surrogate primary key is DPNT_OTHR_PTIP_RT_ID combined with the effectivity dates; the business-key candidate is identical, as documented in the unique index BEN_DPNT_OTHR_PTIP_RT_F_PK.

Common Use Cases and Queries

This table is most often queried when reconciling or reporting dependent rate pricing across plan types within a benefits program. Typical scenarios include validating which variable rate profile applies to a dependent enrolled in another plan type, auditing effective-dated rate changes, and extracting DFF values for integration or analytics.

A representative query retrieving active rates for a given plan type in program context:

  • SELECT dpnt_othr_ptip_rt_id, ptip_id, vrbl_rt_prfl_id, ordr_num, exclud_flag FROM ben.ben_dpnt_othr_ptip_rt_f WHERE ptip_id = :p_ptip_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Reporting use cases include rate-change history (ordering by effective_start_date for a given PTIP_ID), DFF-based segmentation (filtering on DOT_ATTRIBUTE_CATEGORY and associated attributes), and business group scoping (restricting on BUSINESS_GROUP_ID).

Related Objects

Although the metadata classifies this table as standalone with no mined foreign keys, its business context links it logically to the following Advanced Benefits objects through shared business keys:

Because no enforced foreign keys exist, joins must be constructed manually on the documented business-key columns above, and DateTrack effectivity must be applied on both sides of any temporal join.