Search Results pqh_routing_list_members




Overview
Explore in AI Chat

Overview

The PQH_ROUTING_LIST_MEMBERS table is a core data object within the Oracle E-Business Suite Public Sector Human Resources (PQH) module. It functions as the junction table that defines the composition of a routing list, which is a structured sequence of approvers or reviewers used in workflow processes. Specifically, it stores the association between a routing list, the predefined roles within the system, and the individual users assigned to those roles. This table is essential for managing the hierarchical or sequential approval flows required for various HR transactions, such as position management, budget approvals, or personnel actions in a public sector context. Its integrity is maintained through primary and unique key constraints, ensuring that each member entry within a routing list is uniquely identified and correctly associated.

Key Information Stored

The table's structure is designed to link three primary entities: the routing list, the role, and the user. The key columns include ROUTING_LIST_MEMBER_ID, which serves as the unique system-generated primary key for each record. The ROUTING_LIST_ID is a foreign key linking to the PQH_ROUTING_LISTS table, identifying the specific approval list. The ROLE_ID is a foreign key to the PQH_ROLES table, defining the functional role (e.g., "Budget Manager," "HR Director") within the routing sequence. The USER_ID column holds the identifier for the specific individual from the FND_USER table assigned to fulfill that role for the given list. A unique key constraint on the combination of ROUTING_LIST_ID, ROLE_ID, and USER_ID prevents duplicate assignments within the same list.

Common Use Cases and Queries

A primary use case is auditing and reporting on the approval hierarchy for a specific transaction. For instance, an administrator may need to generate a list of all approvers for a particular routing list. A common query involves joining to the PQH_ROUTING_LISTS and FND_USER tables to display user-friendly information. Another critical scenario is during workflow runtime, where the system queries this table to determine the next recipient in the approval chain based on the current routing list member. Troubleshooting approval bottlenecks often involves querying this table to verify user assignments are active and correct. A sample SQL pattern to retrieve member details for a list named 'POSITION_APPROVAL' would be:

  • SELECT prlm.*, prl.name, fu.user_name FROM pqh_routing_list_members prlm JOIN pqh_routing_lists prl ON prlm.routing_list_id = prl.routing_list_id JOIN fnd_user fu ON prlm.user_id = fu.user_id WHERE prl.name = 'POSITION_APPROVAL' ORDER BY prlm.role_id;

Related Objects

The PQH_ROUTING_LIST_MEMBERS table is centrally connected to several other key PQH tables, as defined by its foreign key relationships. It is a child table of PQH_ROUTING_LISTS (via ROUTING_LIST_ID) and PQH_ROLES (via ROLE_ID). Crucially, it acts as a parent table in two significant ways. First, it is referenced by PQH_ATTRIBUTE_RANGES (via ROUTING_LIST_MEMBER_ID), which likely stores conditional routing rules or data thresholds specific to a list member. Second, it has a bidirectional relationship with the PQH_ROUTING_HISTORY table, which logs the progression of an item through the workflow. The history table records both the member who forwarded an item (FORWARDED_BY_MEMBER_ID) and the member to whom it was sent (FORWARDED_TO_MEMBER_ID), both foreign keys back to ROUTING_LIST_MEMBER_ID in this table.