Results for “forwarded_to_role_id”

26 results




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

Overview

PQH_ROUTING_HISTORY is an Oracle EBS transactional table owned by the HR schema and delivered as part of the PQH (Public Sector HR) product family. It records the audit trail of the routing and approver process: each row represents a single "hop" — a discrete forwarding event — as a transaction moves through the routing list toward approval or rejection. Because routing configurations, membership lists, role assignments, and position hierarchies can all change over time, this table preserves point-in-time evidence of who forwarded a transaction, to whom, from which role or position, and when.

The ETRM metadata classifies this object as a satellite-leaning table using a heuristic Data Vault model. In practice it functions as a satellite attached to the transaction/detail transaction hub, with foreign keys into routing configuration dimensions (categories, list members, roles, position structure versions) and a dependent child table (PQH_ROUTING_HIST_ATTRIBS) holding the pre-identified descriptive attributes captured with new values. This shape is characteristic of an append-oriented history table rather than a reference or master table.

Key Information Stored

The surrogate primary key is ROUTING_HISTORY_ID, enforced by unique index PQH_ROUTING_HISTORY_PK. It is the only documented unique index, so no separate business-key candidate is published in the ETRM metadata; business identity is effectively composite in nature (transaction plus hop context) but is not constrained as such.

Common Use Cases and Queries

The primary use case is approver audit and escalation reporting: reconstructing the full path a transaction followed, identifying who held the document at each point, and measuring queue latency. A typical reconstruction query joins the table to itself or filters by transaction:

  • SELECT routing_history_id, forwarded_by_user_id, forwarded_to_user_id, forwarded_by_role_id, forwarded_to_role_id, user_action_cd, approval_cd, notification_date FROM pqh_routing_history WHERE transaction_id = :p_transaction_id ORDER BY creation_date.
  • Elapsed-time analysis: compare NOTIFICATION_DATE across consecutive hops (ordered by ROUTING_HISTORY_ID or CREATION_DATE) to derive time-in-queue per approver and per role.
  • Volume and bottleneck reporting: aggregate hop counts grouped by FORWARDED_TO_ROLE_ID or FORWARDED_BY_POSITION_ID to identify roles or positions that accumulate transactions.
  • Attribute enrichment: join to PQH_ROUTING_HIST_ATTRIBS on ROUTING_HISTORY_ID to retrieve the pre-identified attributes stored alongside each hop.
  • Configuration drift analysis: join to PQH_ROUTING_CATEGORIES and PQH_TRANSACTION_CATEGORIES to compare routing behavior across categories, and to PER_POS_STRUCTURE_VERSIONS to confirm which hierarchy version was active.

Related Objects

  • PQH_ROUTING_HIST_ATTRIBS — child table; join on ROUTING_HISTORY_ID to obtain supplementary attributes for each hop.
  • PQH_ROUTING_CATEGORIES — referenced via ROUTING_CATEGORY_ID.
  • PQH_TRANSACTION_CATEGORIES — referenced via TRANSACTION_CATEGORY_ID.
  • PQH_ROUTING_LIST_MEMBERS — referenced twice, via FORWARDED_BY_MEMBER_ID and FORWARDED_TO_MEMBER_ID.
  • PQH_ROLES — referenced twice, via FORWARDED_BY_ROLE_ID and FORWARDED_TO_ROLE_ID.
  • PER_POS_STRUCTURE_VERSIONS — referenced via POS_STRUCTURE_VERSION_ID, providing hierarchy version context.
  • PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F — implicit participants resolved through the forwarded-by/to assignment and user columns for approver name reporting.