Search Results hr_dm_hierarchies




Overview
Explore in AI Chat

Overview

HR_DM_HIERARCHIES is a Human Resources (PER) module table in the Oracle E-Business Suite HR schema. It functions as a metadata repository that stores the hierarchical relationships between other tables registered within the HR data management framework. Specifically, it records parent-child relationships between tables, allowing the application to understand how the various data structures interrelate. This metadata is essential for data migration, archival, and purging operations, where the correct processing order of related tables must be derived from the defined hierarchy.

Based on the foreign key structure mined from the ETRM metadata, the heuristic Data Vault classification for this object is a link. This classification suggests the table operates as an associative entity that resolves many-to-many or parent-to-child relationships between records in other tables, rather than serving as a standalone hub of business entities or a descriptive satellite. The table contains fourteen documented columns and is owned by the HR schema, with a status of VALID in both Oracle EBS 12.1.1 and 12.2.2 environments.

Key Information Stored

The table is anchored by the surrogate primary key HIERARCHY_ID, enforced through the unique index HR_DM_HIERARCHIES_PK. This column uniquely identifies each recorded hierarchy relationship and is the documented business-key candidate for the table.

The presence of both TABLE_ID and PARENT_TABLE_ID, each referencing HR_DM_TABLES, confirms the self-referencing link pattern that this table models. The SECURITY_GROUP_ID foreign key aligns the metadata with Oracle's standard security group partitioning.

Common Use Cases and Queries

This table is primarily consulted by the HR data management framework during data migration, purge, and archive processing. It enables dependent logic to order table operations correctly so that parent records are handled before child records. Typical queries resolve the full relationship chain between a given table and its parent.

  • Retrieving all relationships for a specific table: SELECT * FROM HR.HR_DM_HIERARCHIES WHERE TABLE_ID = :p_table_id ORDER BY SQL_ORDER;
  • Joining to HR_DM_TABLES to obtain table names: SELECT h.HIERARCHY_ID, 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;
  • Filtering by security group for multi-org environments: SELECT * FROM HR.HR_DM_HIERARCHIES WHERE SECURITY_GROUP_ID = :p_security_group_id;
  • Determining processing order within a hierarchy: order results by SQL_ORDER ascending.

Reporting use cases include generating dependency maps for data migration projects, auditing the metadata configuration for consistency, and validating that every registered child table has an appropriate parent linkage before undertaking a purge operation.

Related Objects

The following objects are most significant in relation to HR_DM_HIERARCHIES, based on the documented foreign key and primary key relationships:

  • HR_DM_TABLES — Referenced twice via HR_DM_HIERARCHIES.TABLE_ID and HR_DM_HIERARCHIES.PARENT_TABLE_ID; provides the table names that this hierarchy links.
  • FND_SECURITY_GROUPS — Referenced via HR_DM_HIERARCHIES.SECURITY_GROUP_ID; enforces data segregation across business groups.
  • HR.HR_DM_HIERARCHIES_PK — Primary key index on HIERARCHY_ID, ensuring uniqueness.
  • The broader HR data management (DM) metadata tables that share the HR_DM prefix, which together describe the migration and purge configuration for HR data sets.

Together these objects form the metadata backbone that Oracle EBS uses to understand and traverse table relationships during HR data management operations.