Results for “member_cd”

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

Overview

The APPS.PQH_ROUTING_CATEGORIES_V view is a denormalized reporting and integration layer within the Public Sector HR (PQH) module of Oracle E-Business Suite, available under both release 12.1.1 and 12.2.2. It exposes the routing configuration that governs how approval transactions are dispatched to members of a routing list, position structure, or supervisory hierarchy. Where the base table PQH_ROUTING_CATEGORIES stores raw identifiers, this view resolves those identifiers into readable names and derives a routing member category code, making it suitable for concurrent-program extracts, OAF/ADF pages, and downstream reporting tools such as Oracle BI Publisher, Discoverer, and custom SQL reports. The view is owned by the APPS schema and is marked VALID in the ETRM repository, confirming it is a supported dictionary object against which customer queries can be safely written. Its particular relevance to searches for override_role_id stems from the fact that the view exposes both the raw foreign key and its resolved display name, simplifying role-based override auditing.

Underlying Base Objects

The view text is a single SELECT over PQH_ROUTING_CATEGORIES (aliased RCT) with outer joins to five reference objects. PQH_ROUTING_LISTS supplies routing list names, PQH_ROLES resolves override roles, FND_USER resolves override users, PER_POSITION_STRUCTURES resolves position-structure names, and HR_ALL_POSITIONS resolves override positions. All joins are outer (+) joins, so a routing category row with no override role, user, or position still appears, with NULL names. Additional logic invokes the HR_GENERAL package function DECODE_LOOKUP for the PQH_SUPERVISORY_HIERARCHY lookup and PQH_UTILITY.DECODE_ASSIGNMENT_NAME for assignment display text. ETRM also records PQH_TCT_WIZARD_PKG as a referenced object, reflecting the wizard-driven maintenance path that creates and updates routing categories.

Key Columns

• ROUTING_CATEGORY_ID — Primary key of the underlying routing category row.
• TRANSACTION_CATEGORY_ID — Identifies the transaction type to which the routing rule applies.
• MEMBER_CD — Derived code ('S', 'P', or 'R') indicating whether routing targets the supervisory hierarchy, a position structure, or a routing list.
• LIST_ID / LIST_NAME — The resolved routing list or position structure and its display name.
• OVERRIDE_ROLE_ID / OVERRIDE_ROLE_NAME — The PQH role used to redirect approval to a specific role, with its display name.
• OVERRIDE_USER_ID / OVERRIDE_USER_NAME — The FND_USER override target and username.
• OVERRIDE_POSITION_ID / OVERRIDE_POSITION_NAME — Position-level override and its resolved name.
• OVERRIDE_ASSIGNMENT_ID / OVERRIDE_ASSIGNMENT_NAME — Assignment-level override resolved via PQH_UTILITY.
• ENABLE_FLAG, DEFAULT_FLAG, DELETE_FLAG — Status and behavior indicators controlling rule activation.
• OBJECT_VERSION_NUMBER, audit columns — Optimistic-locking and WHO columns.

Common Use Cases and Queries

The most frequent query pattern involves locating active routing rules that override approvers by role. To extract all enabled categories with a role override:

SELECT routing_category_id, transaction_category_id, list_name, override_role_id, override_role_name
FROM apps.pqh_routing_categories_v
WHERE enable_flag = 'Y' AND override_role_id IS NOT NULL;

Because the view joins PQH_ROLES with an outer join, roles deleted from the routing list reference still yield a row with a NULL OVERRIDE_ROLE_NAME, which supports orphan-reference validation. A second common use is auditing effective routing scope by member type:

SELECT member_cd, COUNT(*) FROM apps.pqh_routing_categories_v GROUP BY member_cd;

Returning 'S', 'P', and 'R' distributions respectively for hierarchy, position-structure, and list-based routing. A third pattern joins the view to transaction category lookups to produce a full approval matrix extract for SOX or internal audit reporting. Performance is generally acceptable for read-only reporting, but queries should filter on ENABLE_FLAG and TRANSACTION_CATEGORY_ID to avoid full scans of PQH_ROUTING_CATEGORIES given the six-way outer join.