Search Results pay_element_rate_id




Overview
Explore in AI Chat

Overview

The PSB_PAY_ELEMENT_RATES table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, residing in the PSB (Public Sector Budgeting) schema. Its primary function is to store the monetary rates or percentages associated with pay elements defined in the system. These pay elements represent various compensation components, such as allowances, differentials, or bonuses. The table supports complex compensation modeling by allowing rates to be defined either as a flat amount or as a percentage of salary, contingent upon the parent pay element's definition. A critical feature is its support for effective-dated records, enabling an element or element option to have distinct rates for different time periods. Furthermore, it distinguishes between base rates applicable to all scenarios and worksheet-specific rates used for budgeting or planning exercises, providing flexibility for scenario analysis.

Key Information Stored

The table's columns are designed to capture the identity, context, value, and validity of each pay element rate. The primary key, PAY_ELEMENT_RATE_ID, serves as the unique identifier for each rate record. Foreign key columns PAY_ELEMENT_ID and PAY_ELEMENT_OPTION_ID link the rate to its parent pay element and optional sub-option, respectively. Temporal validity is managed through EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. The WORKSHEET_ID column is pivotal; a NULL value indicates a base element rate, while a non-NULL value ties the rate to a specific budget worksheet or revision for scenario planning. The core value data is stored in ELEMENT_VALUE, with ELEMENT_VALUE_TYPE defining its nature (e.g., amount or percent). Supporting columns include PAY_BASIS (e.g., HOURLY, ANNUAL) for percentage-based calculations, FORMULA_ID for complex derivations, and MAXIMUM_VALUE to cap calculated amounts.

Common Use Cases and Queries

This table is central to compensation reporting, budgeting, and audit processes. A common requirement is to retrieve the effective rate for a specific pay element at a given point in time, which involves querying for records where the date falls within the effective date range. For budgeting workflows, analysts often extract all worksheet-specific rates (WHERE WORKSHEET_ID IS NOT NULL) for a particular planning cycle. System integrations or data validation checks may involve identifying rates that exceed defined maximums or that lack a valid effective end date. A typical query pattern to find the current base rate for an element would be:

  • SELECT element_value, pay_basis FROM psb.psb_pay_element_rates WHERE pay_element_id = :p_element_id AND effective_start_date <= SYSDATE AND (effective_end_date IS NULL OR effective_end_date >= SYSDATE) AND worksheet_id IS NULL;

Related Objects

The PSB_PAY_ELEMENT_RATES table is integrated into the PSB module's data model through defined relationships. Its primary key is enforced by the unique index PSB_PAY_ELEMENT_RATES_U1 on PAY_ELEMENT_RATE_ID. Foreign key relationships are implied by the non-unique indexes, which optimize joins to parent tables. Index PSB_PAY_ELEMENT_RATES_N1 on PAY_ELEMENT_ID and EFFECTIVE_START_DATE suggests a direct relationship with a pay elements master table. Index PSB_PAY_ELEMENT_RATES_N2 on WORKSHEET_ID indicates a relationship with a worksheet or budget revision header table. Similarly, index PSB_PAY_ELEMENT_RATES_N3 on PAY_ELEMENT_OPTION_ID points to a pay element options table. While the exact parent table names are not specified in the provided metadata, these indexes clearly define the key join paths for queries involving pay element rates.