Search Results psb_pay_element_options_n1

Overview

The PSB.PSB_PAY_ELEMENT_OPTIONS table is a core data structure within Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the Public Sector Budgeting (PSB) module. It serves as a master repository for defining the permissible values or choices associated with a pay element. Pay elements represent components of compensation, such as allowances or salary bases. This table enables the configuration of pay elements that require a predefined list of selectable options or a structured table of values, moving beyond a simple single-value definition. Its primary role is to support complex compensation setups, particularly for salary elements where compensation is tied to a graded structure of grades and steps.

Key Information Stored

The table's columns are designed to capture the identity, relationship, and specific value of each pay element option. The PAY_ELEMENT_OPTION_ID (NUMBER) is the primary key, serving as the unique system identifier for each record. The PAY_ELEMENT_ID (NUMBER) is a critical foreign key that links the option back to its parent pay element defined elsewhere. The NAME (VARCHAR2) column stores the descriptive text for the option. For salary-related elements, the GRADE_STEP (NUMBER) and SEQUENCE_NUMBER (NUMBER) columns define the hierarchical position and order within a salary grade or step table. The table also includes standard Oracle EBS "Who" columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) for auditing data creation and modifications.

Common Use Cases and Queries

A primary use case is generating a list of all available options for a specific pay element, often for validation in compensation entry screens or payroll calculations. For example, to retrieve all options for a pay element with ID 1001, ordered for display, one would execute: SELECT NAME, GRADE_STEP, SEQUENCE_NUMBER FROM PSB.PSB_PAY_ELEMENT_OPTIONS WHERE PAY_ELEMENT_ID = 1001 ORDER BY SEQUENCE_NUMBER;. Another common scenario involves reporting on the complete salary grade structure, joining this table to the master pay element definition. Troubleshooting or data validation scripts frequently use the PAY_ELEMENT_OPTION_ID to trace specific compensation values back to their source definition. The unique index on PAY_ELEMENT_OPTION_ID ensures data integrity for these lookups.

Related Objects

Based on the provided metadata, the PSB.PSB_PAY_ELEMENT_OPTIONS table has a clear dependency relationship with a parent table that defines the master pay element. The PAY_ELEMENT_ID column functions as a foreign key, although the specific referenced table (e.g., PSB.PSB_PAY_ELEMENTS or similarly named) is not listed in the excerpt. The table is referenced by the APPS schema synonym named PSB_PAY_ELEMENT_OPTIONS, which is the standard access point for all application code and queries. The existence of a non-unique index (PSB_PAY_ELEMENT_OPTIONS_N1) on the PAY_ELEMENT_ID column optimizes queries that filter or join on this relationship. There are no other referenced dependencies documented in the provided excerpt.