Search Results psbfv_pay_elements

Overview

PSBFV_PAY_ELEMENTS is a read-only Oracle EBS view belonging to the PSB (Public Sector Budgeting) product family, which is documented in ETRM as obsolete in release 12.1.1 and 12.2.2. The view exposes pay element definitions used by the Public Sector Budgeting data extract process, presenting them in a flattened, reporting-friendly form that resolves several coded columns against Oracle Application Object Library (FND) lookup types. It is intended for querying, exporting, and integration scenarios where downstream consumers require descriptive pay element metadata—such as element value type meanings, processing type meanings, and salary type meanings—rather than raw lookup codes.

The view is defined with a WITH READ ONLY clause, meaning no DML is permissible against it. Its role is purely declarative: it joins PSB_PAY_ELEMENTS to PSB_DATA_EXTRACTS so that each pay element row is reported alongside its parent data extract name. Because PSB is obsolete, the view should be regarded as a legacy artifact; its schema remains for backward compatibility and historical reporting rather than for active configuration.

Underlying Base Objects

The ETRM metadata documents no explicitly declared base objects, but the embedded view text clearly identifies two underlying tables:

• PSB_PAY_ELEMENTS PE — the primary table holding pay element definitions, including flags, date ranges, value types, and the foreign key PAY_ELEMENT_ID.
• PSB_DATA_EXTRACTS DE — the parent table holding data extracts, joined on DATA_EXTRACT_ID.

The join condition PE.DATA_EXTRACT_ID = DE.DATA_EXTRACT_ID establishes a one-to-many relationship: a single data extract may have many pay elements. Lookup translations are not physical joins but are expressed as virtual columns prefixed with _LA:, which instruct the Oracle EBS lookup-resolution layer to translate stored codes into meanings using FND_LOOKUPS. This design keeps the view normalized while delivering human-readable output.

Key Columns

The view exposes 25 columns. The most significant include:

• NAME — the pay element name from PSB_PAY_ELEMENTS.
• DATA_EXTRACT_NAME — the name of the associated data extract.
• ELEMENT_VALUE_TYPE, PROCESSING_TYPE, PERIOD_TYPE, PROCESS_PERIOD_TYPE, MAX_ELEMENT_VALUE_TYPE, SALARY_TYPE — exposed as virtual lookup columns (with the _LA: prefix in the underlying text) resolving to MEANING values via PSB-specific FND lookup types.
• OVERWRITE_FLAG, REQUIRED_FLAG, FOLLOW_SALARY, SALARY_FLAG — Y/N style control flags governing extract behavior.
• MAX_ELEMENT_VALUE — the ceiling value for the element.
• OPTION_FLAG — a flag controlling optionality of the pay element, the column most commonly targeted in ad hoc queries.
• PAY_ELEMENT_ID, BUSINESS_GROUP_ID, DATA_EXTRACT_ID — identifiers for the element, its owning business group, and its parent extract.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — standard audit columns.

Common Use Cases and Queries

Typical uses include auditing pay element configuration, verifying which elements are mandatory or optional on a given extract, and feeding extracted definitions into external budgeting or payroll integration processes.

To list all pay elements and their optionality for a specific extract:

• SELECT v.NAME, v.OPTION_FLAG, v.REQUIRED_FLAG FROM PSBFV_PAY_ELEMENTS v WHERE v.DATA_EXTRACT_NAME = :extract_name ORDER BY v.NAME;

To identify optional elements (OPTION_FLAG set) across all extracts:

• SELECT v.DATA_EXTRACT_NAME, v.NAME, v.OPTION_FLAG FROM PSBFV_PAY_ELEMENTS v WHERE v.OPTION_FLAG = 'Y';

To resolve one element by its surrogate key:

• SELECT v.NAME, v.SALARY_TYPE, v.MAX_ELEMENT_VALUE FROM PSBFV_PAY_ELEMENTS v WHERE v.PAY_ELEMENT_ID = :pay_element_id;

Because the view is read-only and PSB is obsolete, queries should always include a business group or extract filter to keep result sets bounded, and callers should not expect new functionality or future enhancements to be delivered against this object.