Search Results pay_us_earnings_types_v




Overview
Explore in AI Chat

Overview

PAY_US_EARNINGS_TYPES_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) module. Its purpose is to present, in a single denormalized result set, the earnings-related element types defined in a US payroll environment. As documented in the ETRM metadata, the view "retrieves a list of earnings elements: such as Supplemental Earnings, Imputed Earnings, Non-payroll Payments."

The view is a read-only query construct rather than a table, and it carries no data of its own. It is designed to surface the intersection of three configuration domains: element types, element classifications, and their translated (language-specific) name columns. Because it resolves names using USERENV('LANG'), it returns descriptions in the session's current language, which makes it suitable for both localized reporting and integration extracts. In EBS 12.1.1 and 12.2.2 the object is documented with a status of VALID in APPS, and there is no difference in the underlying definition between the two releases as reflected in the ETRM 12.2.2 metadata.

Underlying Base Objects

The view is defined over four base objects, all accessed through APPS synonyms, consistent with standard EBS naming and the _F (date-effective) and _TL (translated) table conventions:

The joins are performed on ELEMENT_TYPE_ID between the element type tables and their translated counterparts, and on CLASSIFICATION_ID between the classification tables. The result is filtered to classifications named EARNINGS, SUPPLEMENTAL EARNINGS, IMPUTED EARNINGS, NON-PAYROLL PAYMENTS, and ALIEN/EXPAT EARNINGS. Rows whose element names contain the suffixes "SPECIAL FEATURES" or "SPECIAL INPUTS" are explicitly excluded, and a DISTINCT is applied to suppress duplicate rows.

Key Columns

  • ELEMENT_NAME — the translated, user-facing name of the earnings element.
  • ELEMENT_TYPE_ID — the primary key of the element type; the practical join key to payroll element and input value tables.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range of the element type, enabling point-in-time filtering.
  • CLASSIFICATION_NAME — the translated classification, one of the five earnings categories above.
  • CLASSIFICATION_ID — the surrogate key of the classification.

Common Use Cases and Queries

Typical uses include validating that earnings elements are configured with the correct classification, driving payroll reporting extracts, supporting data conversion and reconciliation scripts, and populating LOVs in custom concurrent programs.

List all earnings elements with their classification:

  • SELECT element_name, element_type_id, classification_name FROM apps.pay_us_earnings_types_v ORDER BY classification_name, element_name;

Restrict to supplemental earnings only:

  • SELECT element_name, element_type_id FROM apps.pay_us_earnings_types_v WHERE classification_name = 'SUPPLEMENTAL EARNINGS';

Point-in-time query for elements effective on a given date:

  • SELECT element_name, effective_start_date, effective_end_date FROM apps.pay_us_earnings_types_v WHERE SYSDATE BETWEEN effective_start_date AND effective_end_date;