Results for “order_by”

50+ results




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

Overview

PAYLV_PAYMENT_TYPE_V is a payroll reference view owned by the APPS schema in Oracle E-Business Suite (validated against 12.1.1 and 12.2.2). It exposes the list of payment types defined in Oracle Payroll, translated into the session's runtime language, for use in reporting, integration, and Oracle Application Framework (OAF) list-of-values regions. The suffix "LV" denotes its design intent as a List of Values source: it returns a clean, denormalized set of rows suitable for direct binding to a selection control.

The view is notable for a synthetic sentinel row. Through a UNION ALL with DUAL, it always appends a placeholder record where PAYMENT_TYPE_ID equals -1 and PAYMENT_TYPE_NAME is NULL. This pattern is standard in ETRM LOV views, allowing the calling form or page to display a blank or "all" style entry without corrupting the identity of real payment types.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms: PAY_PAYMENT_TYPES, PAY_PAYMENT_TYPES_TL, and DUAL.

  • PAY_PAYMENT_TYPES — the transactional, language-independent table holding the surrogate PAYMENT_TYPE_ID and related attributes.
  • PAY_PAYMENT_TYPES_TL — the translation table holding PAYMENT_TYPE_NAME per installed language, joined on PAYMENT_TYPE_ID and filtered by LANGUAGE = USERENV('LANG').
  • DUAL — the single-row utility table used to inject the sentinel row.

The join between PAY_PAYMENT_TYPES and PAY_PAYMENT_TYPES_TL is a one-to-many translation join keyed on PAYMENT_TYPE_ID. The USERENV('LANG') predicate restricts output to the caller's session language, meaning the same query can return different names for different users. The view is created WITH READ ONLY, so it can never be used as a DML target.

Key Columns

  • PAYMENT_TYPE_NAME — the translated, display-ready name of the payment type. This is the column normally bound to an LOV display field.
  • PAYMENT_TYPE_ID — the numeric primary key from PAY_PAYMENT_TYPES. For the sentinel row it is hard-coded to -1.
  • ORDER_BY — an alias of the payment type name, selected purely to give the view a convenient, deterministic sort column. It is worth noting that this column replicates PAYMENT_TYPE_NAME rather than providing an independent ordinal; ordering by it sorts rows alphabetically by translated name.

Common Use Cases and Queries

Typical consumers include custom payroll reports, concurrent programs validating a user-supplied payment type, and OAF/Forms LOV regions that need a sorted, language-aware list. Because the caller searched for "order_by", the ORDER_BY column is likely the focal point: it exists to let developers write a self-documenting ORDER BY clause against the view itself, rather than repeating the underlying name column.

  • Populating an LOV sorted by display name:

SELECT payment_type_id, payment_type_name
FROM apps.paylv_payment_type_v
ORDER BY order_by;

  • Validating a user-entered payment type and excluding the sentinel:

SELECT payment_type_name
FROM apps.paylv_payment_type_v
WHERE payment_type_id = :p_id
AND payment_type_id <> -1;

  • Filtering out blank rows before loading a lookup set:

SELECT payment_type_id, payment_type_name
FROM apps.paylv_payment_type_v
WHERE payment_type_id > 0;

Because the view is READ ONLY and its LANGUAGE predicate depends on session context, reports should be run under a session whose language matches the expected reporting locale. Developers should also remember the sentinel row when writing joins or aggregations, since an unfiltered COUNT will include the -1 placeholder.