Search Results ben_py_bss_rt_d




Overview
Explore in AI Chat

Overview

BEN_PY_BSS_RT_D is an APPS-owned database view in the Oracle Advanced Benefits (BEN) module, documented as VALID and classified as "Retrofitted" within the ETRM reference for Oracle EBS 12.1.1 and 12.2.2. It exposes configuration data for pay basis rate records that govern how variable rate profiles are associated with pay bases for benefits calculations. The suffix "_D" denotes a descriptive, denormalized view: rather than surfacing internal foreign key identifiers, it resolves them into human-readable names and meanings suitable for reporting, concurrent program output, and integration extracts. In the Advanced Benefits data model, variable rate profiles determine the rate or amount applied for a benefit plan or option, while pay bases define the earnings or compensation elements against which those rates are evaluated. BEN_PY_BSS_RT_D presents the intersection of these two concepts, allowing functional users and technical developers to inspect the pay basis rate setup without navigating multiple base tables. Because it is a view rather than a table, it carries no storage of its own and always reflects the current committed state of the underlying transactional tables.

Underlying Base Objects

The view is defined over a join of several documented base objects, all referenced through APPS synonyms unless noted. The driving table, BEN_PY_BSS_RT_F, is the base table holding pay basis rate records; the "_F" suffix indicates it is a date-tracked (datetrack) table, which is why the view exposes EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. BEN_VRBL_RT_PRFL_F supplies the variable rate profile and is joined on VRBL_RT_PRFL_ID. PER_PAY_BASES supplies pay basis definitions and is joined on PAY_BASIS_ID. FND_USER is joined on LAST_UPDATED_BY to resolve the updating user, and HR_LOOKUPS is joined with LOOKUP_TYPE = 'YES_NO' to translate EXCLD_FLAG into a meaningful description. The join to BEN_VRBL_RT_PRFL_F is date-effective aware: the view constrains PBR.EFFECTIVE_START_DATE between the profile's effective start and end dates, ensuring the correct profile version is reported. All joins to the descriptive tables are outer joins (denoted by the "(+)" operator), so a pay basis rate row is returned even when a related profile, pay basis, lookup, or user record is missing. The ETRM metadata also lists HR_API (PACKAGE) among referenced objects, reflecting the HR datetrack API infrastructure that underpins the effective-dated tables in this join.

Key Columns

  • ROW_ID — The ROWID of the underlying BEN_PY_BSS_RT_F row, providing a direct handle to the base record.
  • PY_BSS_RT_ID — Primary key of the pay basis rate record in the base table.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — The datetrack effective period of the pay basis rate row.
  • VRBL_RT_PRFL_NAME — Name of the associated variable rate profile, resolved from BEN_VRBL_RT_PRFL_F.NAME.
  • PAY_BASIS_NAME — Name of the associated pay basis, resolved from PER_PAY_BASES.NAME.
  • EXCLD_MEANING — Decoded meaning of the exclusion flag, resolved from HR_LOOKUPS.MEANING for the YES_NO lookup type.
  • ORDR_NUM — Ordering sequence number applied to the pay basis rate record.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — Audit columns showing when and by which FND_USER the record was last modified.

Common Use Cases and Queries

Typical uses include auditing benefits rate setup, validating pay basis associations before a payroll or benefits run, and feeding downstream extracts with readable descriptions rather than internal IDs. Because the view is date-effective aware, queries are usually filtered by a reference date.

  • List all pay basis rates with their profile and pay basis names as of today:
    SELECT py_bss_rt_id, vrbl_rt_prfl_name, pay_basis_name, excld_meaning, ordr_num FROM ben_py_bss_rt_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date ORDER BY pay_basis_name, ordr_num;
  • Find rates that are flagged for exclusion:
    SELECT py_bss_rt_id, vrbl_rt_prfl_name, pay_basis_name FROM ben_py_bss_rt_d WHERE excld_meaning = 'Yes';
  • Audit recently changed setup:
    SELECT py_bss_rt_id, pay_basis_name, last_updated_by, last_update_date FROM ben_py_bss_rt_d WHERE last_update_date >= TRUNC(SYSDATE) - 7;
  • Report all rate rows associated with a specific variable rate profile:
    SELECT pay_basis_name, ordr_num, effective_start_date, effective_end_date FROM ben_py_bss_rt_d WHERE vrbl_rt_prfl_name = :profile_name ORDER BY effective_start_date DESC;

As with any APPS view, queries should be run with the appropriate MO or responsibility security context and, for performance, should restrict the effective date range so the optimizer can use the datetrack indexes on BEN_PY_BSS_RT_F.