Results for “pqh_attribute_ranges_v2”

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

Overview

PQH_ATTRIBUTE_RANGES_V2 is an APPS-owned database view within the Public Sector HR (PQH) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents attribute range definitions together with their associated routing members, exposing a denormalized result set that links range records to the routing categories, positions, assignments, or routing list members against which they apply. The view is a reporting and integration artifact rather than a transactional entity; it carries no DML of its own and is intended for read-only consumption.

The object is particularly relevant to consumers investigating approval routing configuration. Because the view exposes an APPROVER_FLAG column, it is commonly queried when administrators or developers need to determine which routing members are designated as approvers within a given attribute range — the search term "approver_flag" maps directly to this column. The view is marked VALID in the ETRM repository and is documented as an ETRM view metadata object.

The "V2" suffix indicates a revised definition, typically superseding an earlier version with corrected join semantics or expanded column coverage. Its role in EBS reporting is to provide a stable, query-friendly projection over the underlying attribute range and routing list member data without requiring the consumer to replicate the DECODE and outer-join logic embedded in the view text.

Underlying Base Objects

The view is defined over a primary synonym and a secondary view, joined with an outer join, and additionally references two PL/SQL packages in its SELECT list. The documented base objects are:

• PQH_ATTRIBUTE_RANGES (SYNONYM) — aliased as RNG; supplies the driving rows, including ROUTING_CATEGORY_ID, ROUTING_LIST_MEMBER_ID, POSITION_ID, ASSIGNMENT_ID, RANGE_NAME, APPROVER_FLAG, ENABLE_FLAG, and DELETE_FLAG.
• PQH_ROUTING_LIST_MEMBERS_V (VIEW) — aliased as RLM; supplies routing list member attributes such as USER_ID, ROLE_NAME, and USER_NAME used to derive the display name.
• HR_GENERAL (PACKAGE) — invoked as HR_GENERAL.DECODE_POSITION to resolve a position identifier into a readable position name.
• PQH_UTILITY (PACKAGE) — invoked as PQH_UTILITY.DECODE_ASSIGNMENT_NAME to resolve an assignment identifier into a readable assignment name.

The two packages are not joined relations; they are called inline to translate internal identifiers into user-readable labels. The relationship to PQH_ATTRIBUTE_RANGES is one of projection and filtering, while the relationship to PQH_ROUTING_LIST_MEMBERS_V is an outer join keyed on ROUTING_LIST_MEMBER_ID, permitting ranges without a routing list member to remain in the result set.

Key Columns

• ROUTING_CATEGORY_ID — the routing category to which the range belongs; the primary grouping key for most queries.
• ROUTING_MEMBER_ID — a DECODE-derived identifier that resolves to ROUTING_LIST_MEMBER_ID when present, otherwise to POSITION_ID, otherwise to ASSIGNMENT_ID. This gives a single normalized identifier across the three possible member types.
• ROUTING_MEMBER_NAME — the human-readable member name, derived via PQH_UTILITY.DECODE_ASSIGNMENT_NAME, HR_GENERAL.DECODE_POSITION, or a concatenation of ROLE_NAME and USER_NAME from the routing list member view.
• RANGE_NAME — the descriptive name of the attribute range.
• APPROVER_FLAG — indicates whether the associated routing member acts as an approver for the range. This is the column most frequently targeted by searches for "approver_flag".
• ENABLE_FLAG — indicates whether the range is currently active.
• DELETE_FLAG — a soft-delete indicator defaulted to 'N' via NVL, so rows are never returned as NULL for this column.

Common Use Cases and Queries

Typical scenarios include auditing approval routing configuration, listing approvers by routing category, and extracting ranges for integration into external workflow or reporting systems. The following query lists enabled approver-designated ranges for a routing category:

• SELECT routing_category_id, routing_member_name, range_name, approver_flag, enable_flag FROM apps.pqh_attribute_ranges_v2 WHERE approver_flag = 'Y' AND enable_flag = 'Y' AND delete_flag = 'N' ORDER BY routing_category_id, range_name;

To enumerate all members for a specific category regardless of approver status:

• SELECT routing_member_id, routing_member_name, range_name, approver_flag FROM apps.pqh_attribute_ranges_v2 WHERE routing_category_id = :category_id AND delete_flag = 'N';

Because the view performs DISTINCT and outer-join processing, it is suitable for direct reporting but should not be used as a high-volume transactional source. Consumers should apply ENABLE_FLAG and DELETE_FLAG filters to exclude inactive or logically deleted ranges.