Results for “rate_matrix_node_id”

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

Overview

PQH_RATE_MATRIX_NODES is a Public Sector HR (PQH) table owned by the HR schema that stores the individual nodes comprising a rate matrix hierarchy. Rate matrices in Oracle EBS Public Sector payroll are used to derive pay rates from a set of criteria — grade, step, longevity, location, bargaining unit, or other eligibility attributes — and each node in the hierarchy represents a distinct position or branch point in that decision structure. The table holds the structural definition of the matrix (parent/child relationships, level, criteria references), while the associated rate values are stored separately in rate and node-value tables.

Based on the foreign key topology mined from the ETRM metadata, this object is hub-leaning in Data Vault terms. It is referenced by two dependent tables (PQH_RATE_MATRIX_RATES_F and PQH_RT_MATRIX_NODE_VALUES) rather than driving its own foreign keys outward, which is consistent with a hub that anchors business keys while satellites carry descriptive and effectivity attributes. As a modeling suggestion, treat RATE_MATRIX_NODE_ID and the SHORT_CODE/BUSINESS_GROUP_ID pair as the durable business keys of the hub, and treat the descriptive and audit columns as satellite content.

Key Information Stored

The table contains 17 documented columns. The most significant are:

• RATE_MATRIX_NODE_ID — the surrogate primary key (PQH_RATE_MATRIX_NODES_PK). Primary identifier used by all dependent tables.
• SHORT_CODE — the user-facing node code; combined with BUSINESS_GROUP_ID in unique index PQH_RATE_MATRIX_NODES_UK1, making it the principal business-key candidate.
• BUSINESS_GROUP_ID — the operating unit/business group that owns the node, providing multi-tenancy partitioning.
• NODE_NAME — descriptive label for the matrix node.
• PARENT_NODE_ID — self-referencing column that establishes the parent/child (hierarchy) relationship between nodes; the root node typically has a null value.
• LEVEL_NUMBER — depth of the node within the hierarchy, used to order traversal and reporting.
• CRITERIA_SHORT_CODE — identifies the criterion (grade, step, longevity, etc.) applied at this node.
• ELIGY_PRFL_ID — reference to an eligibility profile that determines which employees qualify for the node.
• PL_ID — links the node to the pricing/rate matrix plan definition it belongs to.
• LEGISLATION_CODE — country legislation context for the node.
• OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.
• ZD_EDITION_NAME — editioning column present in the 12.2.2 schema, included in both unique indexes.

Common Use Cases and Queries

Typical reporting scenarios include reconstructing a matrix hierarchy from the root downward, listing all nodes under a given parent, and joining node metadata to rate values.

Retrieve all nodes for a business group in hierarchical order:

SELECT rmn.rate_matrix_node_id, rmn.short_code, rmn.node_name,
       rmn.level_number, rmn.parent_node_id
FROM   hr.pqh_rate_matrix_nodes rmn
WHERE  rmn.business_group_id = :p_bg_id
ORDER  BY rmn.level_number, rmn.short_code;

Walk the parent/child chain using a hierarchical query:

SELECT rmn.short_code, rmn.node_name, rmn.level_number,
       LEVEL AS depth, SYS_CONNECT_BY_PATH(rmn.short_code, '/') AS path
FROM   hr.pqh_rate_matrix_nodes rmn
START  WITH rmn.parent_node_id IS NULL
CONNECT BY PRIOR rmn.rate_matrix_node_id = rmn.parent_node_id
       AND PRIOR rmn.business_group_id = rmn.business_group_id;

Join nodes to their effective rate rows for a given date:

SELECT rmn.short_code, rmn.node_name, rmr.rate_value
FROM   hr.pqh_rate_matrix_nodes rmn,
       hr.pqh_rate_matrix_rates_f rmr
WHERE  rmn.rate_matrix_node_id = rmr.rate_matrix_node_id
AND    :eff_date BETWEEN rmr.effective_start_date AND rmr.effective_end_date;

These patterns are used for rate matrix audit reports, pay validation, and troubleshooting why a specific employee received a particular derived rate.

Related Objects

The following objects directly reference or depend on PQH_RATE_MATRIX_NODES via the documented foreign keys:

• PQH_RATE_MATRIX_RATES_F — references RATE_MATRIX_NODE_ID; holds the effective-dated rate values that apply at each node.
• PQH_RT_MATRIX_NODE_VALUES — references RATE_MATRIX_NODE_ID; stores the criteria/value pairings associated with a node.

Additional objects that logically participate in matrix processing — eligibility profiles, pricing/rate plan definitions (referenced through PL_ID), and the payroll rate derivation processes that consume these nodes — are accessed indirectly through the identifiers stored here. Analysts should treat PQH_RATE_MATRIX_NODES as the structural hub of the rate matrix model, with the rates and node values as the satellite detail.