Results for “psb_pay_element_rates”

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

Overview

PSB_PAY_ELEMENT_RATES is a transaction and configuration table within the Public Sector Budgeting (PSB) module of Oracle E-Business Suite, documented as VALID in the PSB schema for releases 12.1.1 and 12.2.2. The table stores the rates associated with a pay element or a specific pay element option. In the Public Sector Budgeting workflow, pay elements represent the discrete compensation components — salary, fringe, overtime, and similar components — that budgeting personnel apply when constructing position budgets and worksheet calculations. PSB_PAY_ELEMENT_RATES captures the numeric or formula-driven values attached to those elements so that budget worksheets can compute costed amounts consistently across a budget cycle.

From a Data Vault modeling perspective, the mined relationship structure suggests this object behaves as a link: it resolves relationships among a pay element, an optional pay element option, a worksheet, and a formula, while also carrying descriptive attributes such as effective dates and element values. The heuristic classification is a modeling suggestion only; the table is a conventional third-normal-form EBS table with a single-column surrogate primary key.

Key Information Stored

The table contains nineteen documented columns. The most significant are:

• PAY_ELEMENT_RATE_ID — the surrogate primary key, enforced by PSB_PAY_ELEMENT_RATES_PK and the unique index PSB_PAY_ELEMENT_RATES_U1. There is no separate documented business-key unique index; the surrogate key is the sole unique identifier.
• PAY_ELEMENT_ID — foreign key to PSB_PAY_ELEMENTS, identifying the pay element to which the rate belongs.
• PAY_ELEMENT_OPTION_ID — foreign key to PSB_PAY_ELEMENT_OPTIONS, identifying an optional refinement of the pay element when the rate varies by option.
• WORKSHEET_ID — foreign key to PSB_WORKSHEETS, scoping the rate to a specific budgeting worksheet.
• FORMULA_ID — foreign key to PSB_FORMULAS, used when the rate is derived by formula rather than entered as a static amount.
• ELEMENT_VALUE_TYPE and ELEMENT_VALUE — the type and value of the rate, supporting both direct numeric entry and calculated results.
• PAY_BASIS — indicates the basis on which the element value applies (for example, flat amount versus percentage or per-period computation).
• MINIMUM_VALUE, MID_VALUE, and MAXIMUM_VALUE — the range bounds applied to the rate for validation or defaulting.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — date-range columns governing when the rate row is active.
• CURRENCY_CODE — the currency in which monetary values are expressed.
• LAST_UPDATE_DATE, CREATED_BY, and CREATION_DATE — standard EBS audit columns supporting change tracking and concurrency.

Common Use Cases and Queries

Typical reporting and reconciliation scenarios include verifying which rates apply to a given pay element for an active date range, extracting all rates scoped to a worksheet, and identifying formula-driven rates for audit. A representative query joins the table to its parent element and worksheet:

• SELECT r.pay_element_rate_id, e.pay_element_id, r.effective_start_date, r.effective_end_date, r.element_value_type, r.element_value, r.currency_code FROM psb_pay_element_rates r, psb_pay_elements e WHERE r.pay_element_id = e.pay_element_id AND r.worksheet_id = :worksheet_id;
• Filtering by SYSDATE BETWEEN effective_start_date AND effective_end_date to isolate currently effective rates.
• Joining to PSB_FORMULAS on formula_id to audit calculated rates, and to PSB_PAY_ELEMENT_OPTIONS on pay_element_option_id to report option-specific variations.
• Aggregating element_value by pay_element_id and currency_code to support budget cost rollups and variance analysis.

Related Objects

The following objects are documented as directly related through foreign-key relationships:

• PSB_PAY_ELEMENTS — referenced via PAY_ELEMENT_ID; the parent definition of the pay element.
• PSB_PAY_ELEMENT_OPTIONS — referenced via PAY_ELEMENT_OPTION_ID; option-level refinements of an element.
• PSB_FORMULAS — referenced via FORMULA_ID; supplies calculation logic for derived rates.
• PSB_WORKSHEETS — referenced via WORKSHEET_ID; the budgeting worksheet context.
• PSB_POSITION_ASSIGNMENTS — references this table through PAY_ELEMENT_RATE_ID, meaning position assignments consume specific pay element rates during budget computation.

Together these relationships place PSB_PAY_ELEMENT_RATES at the center of the PSB costing model, linking element definitions, worksheet instances, formula logic, and position-level assignments.