Search Results hr_dm_hierarchies_pk




Overview
Explore in AI Chat

Overview

HR.HR_DM_HIERARCHIES is a metadata-driven control table within the Oracle E-Business Suite Human Resources schema. It is part of the HR Data Migration (HR_DM) framework, a set of interface and staging objects that govern how legacy or external data is transformed, ordered, and loaded into HRMS tables during conversion and migration projects. The table records the relationships between other HR_DM tables so that the migration engine can resolve dependencies before inserting data. Its documented purpose is threefold: to capture parent/child relationships where the child table is not striped by a Business Group ID column; to record Application Object Library (AOL) identifiers such as the ID_FLEX_NUM value held in HR_SOFT_CODING_KEYFLEX; and to model self-referencing hierarchies such as those found in PER_ALL_POSITIONS, where SUCCESSOR_POSITION_ID and RELIEF_POSITION_ID point back to POSITION_ID in the same table.

The object resides in the APPS_TS_INTERFACE tablespace and holds 14 documented columns in the 12.2.2 schema, with a corresponding FND Design Data entry of PER.HR_DM_HIERARCHIES. Based on its foreign key structure, a Data Vault heuristic would suggest classifying this object as a link, since it principally records associations between entity tables rather than acting as a standalone hub or a descriptive satellite.

Key Information Stored

The surrogate primary key is HIERARCHY_ID, a system-generated NUMBER(15) column enforced by the unique index HR_DM_HIERARCHIES_PK. The business content of each row is defined by the relationship it describes:

Common Use Cases and Queries

The primary use case is troubleshooting and auditing migration sequencing. Because the framework must insert all rows with self-referencing columns nulled, then update them after the inserts complete, developers frequently query this table to understand the load order. A typical query joins the table to HR_DM_TABLES twice:

SELECT h.hierarchy_id, h.hierarchy_type, h.column_name, h.parent_column_name, h.parent_id_column_name, c.table_name child_table, p.table_name parent_table FROM hr.hr_dm_hierarchies h, hr.hr_dm_tables c, hr.hr_dm_tables p WHERE h.table_id = c.table_id AND h.parent_table_id = p.table_id AND h.hierarchy_type = :type;

Additional scenarios include validating that lookup values for HIERARCHY_TYPE exist in FND_LOOKUP_VALUES before a conversion run, identifying tables whose parent/child links are absent from the design (a common cause of FK violations during load), and generating dependency graphs to order interface processing.

Related Objects

  • HR.HR_DM_TABLES — Referenced twice, via TABLE_ID and PARENT_TABLE_ID; supplies table names and metadata for both sides of every relationship.
  • FND_SECURITY_GROUPS — Referenced through SECURITY_GROUP_ID.
  • FND_LOOKUP_TYPES / FND_LOOKUP_VALUES — Store the HR_DM_HIERARCHY_TYPE lookup that drives HIERARCHY_TYPE.
  • PER_ALL_POSITIONS — Canonical example of a self-referencing hierarchy (SUCCESSOR_POSITION_ID, RELIEF_POSITION_ID) modeled by this table.
  • HR_SOFT_CODING_KEYFLEX — Source of AOL identifiers such as ID_FLEX_NUM recorded through this relationship table.
  • TDS packages — Consume SQL_ORDER to derive cursor ordering during data migration.