Search Results per_gen_hier_node_type_pk

Overview

PER_GEN_HIER_NODE_TYPES is a configuration table owned by the HR schema within the PER (Human Resources) product family of Oracle E-Business Suite. It defines the generic hierarchy node types that the EBS hierarchy framework uses to model parent-child relationships across Oracle HRMS structures, such as organization hierarchies, position hierarchies, and other flexible tree-based constructs introduced in Release 12.1.1 and carried forward into 12.2.2.

Rather than storing the hierarchy instances themselves, the table stores the metadata that governs which node types may participate in a given hierarchy, what parent/child combinations are permitted, and how child values are validated against a value set. This makes it a foundational reference object that other hierarchy tables depend on when resolving valid structural relationships.

From a Data Vault modeling perspective, the metadata classifies PER_GEN_HIER_NODE_TYPES as standalone, suggesting no enforced foreign-key relationships to other documented hierarchy tables. A standalone classification implies the object can be treated as a reference or hub-like entity holding descriptive hierarchy-type attributes, rather than a link connecting multiple business entities.

Key Information Stored

The table is documented with 14 physical columns. The most operationally significant are:

• HIER_NODE_TYPE_ID — the surrogate primary key, uniquely identifying each hierarchy node type and backed by the PER_GEN_HIER_NODE_TYPE_PK constraint.
• BUSINESS_GROUP_ID — scopes the node type to a specific business group, supporting multi-tenant separation of HR configuration.
• IDENTIFIER_KEY — the user-facing or integration-facing key that labels the node type for lookups and external references.
• HIERARCHY_TYPE — indicates which hierarchy framework the node type belongs to, governing its permitted usage.
• PARENT_NODE_TYPE and CHILD_NODE_TYPE — define the allowed structural relationship between a parent and child within the hierarchy.
• CHILD_VALUE_SET — references the value set used to validate allowable child values.
• OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
• Audit columns LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE — capture the standard who/when trail.
• ZD_EDITION_NAME — the editioning column introduced with the 12.2.2 online patching architecture; it also participates in the unique index PER_GEN_HIER_NODE_TYPE_PK (HIER_NODE_TYPE_ID, ZD_EDITION_NAME), making the pair the effective business-key candidate.

Common Use Cases and Queries

Typical scenarios include diagnosing why a hierarchy cannot be built, listing valid node types for a business group, and validating parent-child pairings during data conversion or integration load.

Enumerate node types for a business group:

SELECT hier_node_type_id, identifier_key, hierarchy_type
FROM   hr.per_gen_hier_node_types
WHERE  business_group_id = :p_bg_id
AND    zd_edition_name = 'SET1';

Find permitted parent-child combinations for a hierarchy type:

SELECT parent_node_type, child_node_type, child_value_set
FROM   hr.per_gen_hier_node_types
WHERE  hierarchy_type = :p_hier_type
ORDER  BY parent_node_type, child_node_type;

Detect duplicate identifier keys within a business group prior to migration:

SELECT identifier_key, COUNT(*)
FROM   hr.per_gen_hier_node_types
WHERE  business_group_id = :p_bg_id
GROUP  BY identifier_key
HAVING COUNT(*) > 1;

Because 12.2.2 uses editioned tables, always filter on zd_edition_name (typically 'SET1') to avoid returning multiple edition rows.

Related Objects

• PER_GEN_HIER_NODES — the instance-level table storing actual hierarchy nodes that resolve their type through HIER_NODE_TYPE_ID.
• PER_HIERARCHY_TYPES (as applicable) — supplies the values referenced by HIERARCHY_TYPE.
• FND_FLEX_VALUE_SETS — the source of validation for CHILD_VALUE_SET.
• HR_ALL_ORGANIZATION_UNITS and PER_ALL_POSITIONS — organizational and positional entities whose hierarchies are modeled using these node types.
• PER_BUSINESS_GROUPS — provides BUSINESS_GROUP_ID context.
• PER_GEN_HIER_NODE_TYPE_PK — the primary key constraint and unique index (HIER_NODE_TYPE_ID, ZD_EDITION_NAME).

Together these objects form the hierarchy configuration substrate used by HRMS tree structures across EBS.