Search Results ben_dpnt_othr_ptip_rt_d




Overview
Explore in AI Chat

Overview

BEN_DPNT_OTHR_PTIP_RT_D is a reporting view owned by the APPS schema in Oracle Advanced Benefits (BEN). It exposes a denormalized, human-readable projection of dependent/other participant rate records associated with Participant Timing and Eligibility Profile (PTIP) definitions. The trailing "_D" suffix identifies it as a descriptive or display-oriented view, as distinguished from the transactional base table. Its purpose is to present rate records in a form suitable for concurrent programs, Oracle Reports, OBIEE extracts, and ad hoc SQL, translating internal foreign keys into recognizable business labels such as program, plan type, and variable rate profile names.

The view is deployed in both Oracle EBS 12.1.1 and 12.2.2 and carries a VALID status in the ETRM repository. It does not store data; it derives its result set at runtime from six underlying objects joined on their primary keys and constrained by an effective-date predicate.

Underlying Base Objects

The view is defined over the following documented objects, each referenced through an APPS synonym:

The effective-date predicate restricts returned rows to those where the rate record's EFFECTIVE_START_DATE falls between the PTIP's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, ensuring only currently applicable rate definitions are surfaced.

Key Columns

  • DPNT_OTHR_PTIP_RT_ID — primary key of the dependent/other PTIP rate record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-track boundaries for the rate row.
  • EXCLD_FLAG — indicates whether the record is excluded from eligibility or rate processing.
  • ORDR_NUM — sequencing value used to order evaluation of the rate records.
  • PTIP — concatenated display of program name and plan type name (PGM.NAME || '-' || PLTYP.NAME), providing a readable identifier for the associated PTIP.
  • VRBL_RT_PRFL_NAME — the name of the variable rate profile linked to the record.
  • LAST_UPDATE_DATE — audit timestamp of the most recent change.
  • LAST_UPDATED_BY — user ID of the last updater; paired with FND_USER.USER_NAME in the view text for display purposes.

Common Use Cases and Queries

Typical uses include auditing which dependent or other-participant rate records are attached to a given PTIP, reconciling variable rate profile assignments, and building extracts for benefits configuration reviews or data migration comparisons between 12.1.1 and 12.2.2.

Example: list active rate records with readable labels.

  • SELECT ptip, vrbl_rt_prfl_name, ordr_num, excld_flag, effective_start_date, effective_end_date FROM apps.ben_dpnt_othr_ptip_rt_d WHERE effective_end_date = TO_DATE('4712-12-31','YYYY-MM-DD') ORDER BY ptip, ordr_num;

Example: identify records updated by a specific user.

  • SELECT d.dpnt_othr_ptip_rt_id, d.ptip, f.user_name, d.last_update_date FROM apps.ben_dpnt_othr_ptip_rt_d d, apps.fnd_user f WHERE d.last_updated_by = f.user_id AND d.last_update_date > SYSDATE - 30;

Because the view performs multi-table joins and date filtering at runtime, queries against it can be costly on large benefits configurations; appropriate indexes on the underlying _F tables and selective predicates on program or plan type are recommended.