Search Results per_gen_hierarchy_nodes

Overview

PER_GEN_HIERARCHY_NODES is a Human Resources (PER) module table in the HR schema of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It stores the individual nodes that make up a generic hierarchy definition, where each row represents a single member (or element) positioned within a hierarchy structure. The table is closely tied to PER_GEN_HIERARCHY_VERSIONS, which holds the versioned definitions of the hierarchies, and the nodes table holds the materialized members belonging to each version. The table contains 81 documented columns and is owned by the HR schema. Based on the foreign key structure — notably a self-referencing relationship via PARENT_HIERARCHY_NODE_ID and a dependency on The requested version header table — the heuristic Data Vault classification is satellite-leaning. This suggests modeling the table as a satellite attached to the hierarchy version hub, capturing descriptive attributes for each node, while the self-referencing parent link supports recursive hierarchical navigation.

Key Information Stored

The table is anchored by the surrogate primary key column HIERARCHY_NODE_ID, enforced through the unique index PER_GEN_HIERARCHY_NODE_PK (HIERARCHY_NODE_ID, ZD_EDITION_NAME). The presence of ZD_EDITION_NAME in the unique index reflects the 12.2.x editioning mechanism, which should be accounted for when writing queries against 12.2.2.

• HIERARCHY_NODE_ID — surrogate identifier for the node; part of the primary key.
• HIERARCHY_VERSION_ID — foreign key to PER_GEN_HIERARCHY_VERSIONS, identifying the hierarchy version to which the node belongs.
• PARENT_HIERARCHY_NODE_ID — self-referencing foreign key establishing the parent-child relationship between nodes; drives recursive traversal.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, scoping the node to a business group.
• ENTITY_ID — identifier for the underlying entity the node references.
• NODE_TYPE — classifies the node (for example, root, member, or leaf) within the generic hierarchy model.
• SEQ — ordering sequence for nodes at the same level.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — standard concurrent program audit columns identifying the process that created or updated the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — WHO columns and optimistic locking.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE30 — descriptive flexfield (DFF) segments.
• INFORMATION1–INFORMATION30, INFORMATION_CATEGORY — additional descriptive storage for internal framework use.
• IDENTIFIER_KEY — a business identifier key held on the node.

Common Use Cases and Queries

Typical use cases include reporting on hierarchy members, validating parent-child integrity, and extracting the flattened membership of a hierarchy version. A common pattern retrieves all nodes for a specific version:

• SELECT h.hierarchy_node_id, h.parent_hierarchy_node_id, h.node_type, h.seq FROM hr.per_gen_hierarchy_nodes h WHERE h.hierarchy_version_id = :version_id ORDER BY h.seq;
• Recursive traversal to reconstruct the full path from a leaf to root uses the self-join on parent_hierarchy_node_id (CONNECT BY or recursive WITH clause).
• Auditing reports join to PER_GEN_HIERARCHY_VERSIONS to obtain the hierarchy name and version, and to HR_ALL_ORGANIZATION_UNITS to resolve the business group.

Related Objects

• PER_GEN_HIERARCHY_VERSIONS — parent object; joined on HIERARCHY_VERSION_ID.
• PER_GEN_HIERARCHY_NODES (self) — recursive parent link via PARENT_HIERARCHY_NODE_ID.
• HR_ALL_ORGANIZATION_UNITS — business group reference via BUSINESS_GROUP_ID.
• PER_CAL_ENTRY_GEO_LIST — references HYPERARCHY_NODE_ID to associate calendar entry geography data with a node; joined on HIERARCHY_NODE_ID.