Search Results hri_org_hrchy_summary




Overview
Explore in AI Chat

Overview

HRI_ORG_HRCHY_SUMMARY is a summary table owned by the HRI schema, which corresponds to the Human Resources Intelligence module. This module is documented as obsolete in Oracle EBS 12.1.1 and 12.2.2, meaning that the table is retained for backward compatibility, historical reporting, or is no longer actively populated by current application logic. Despite its obsolete status, the object remains VALID in the data dictionary and continues to expose its documented physical structure of fifteen columns.

Functionally, the table stores a flattened, denormalized representation of organizational hierarchy relationships. Each row captures a pairing between an organization and a sub-organization within a specific version of an organization structure, together with the level information needed to reconstruct or report on hierarchical depth relationships without recursively traversing PER_ORGANIZATION_STRUCTURES. The Organization Structure Version identifier ties each summary row to a point-in-time snapshot of the hierarchy, which is significant in Oracle HRMS where organization structures are versioned over time.

From a heuristic Data Vault modeling perspective, the table is classified as standalone, with no downstream child tables referencing it. A reasonable modeling suggestion is to treat it as a satellite-like summary attached to the organization structure version and organization hub keys, rather than as a true hub or link, because its grain is defined by the composite business key and it carries descriptive level attributes rather than pure relationship associations.

Key Information Stored

The composite primary key is enforced by HRI_ORG_HRCHY_SUMMARY_PK, defined over ORG_STRUCTURE_VERSION_ID, ORGANIZATION_ID, and SUB_ORGANIZATION_ID. A separate unique index, HRI_ORG_HRCHY_SUMMARY_U1, mirrors the same three columns, confirming this triplet as the business-key candidate that uniquely identifies a hierarchy summary record.

  • ORG_STRUCTURE_VERSION_ID — Identifies the versioned organization structure snapshot to which the summary row belongs.
  • ORGANIZATION_ID — The parent (or containing) organization in the hierarchy relationship.
  • SUB_ORGANIZATION_ID — The subordinate organization nested beneath ORGANIZATION_ID.
  • ORGANIZATION_LEVEL — The hierarchy depth assigned to the parent organization.
  • SUB_ORGANIZATION_LEVEL — The hierarchy depth of the subordinate organization.
  • SUB_ORG_RELATIVE_LEVEL — The relative depth of the sub-organization with respect to the parent.
  • ORG_BUSINESS_GROUP_ID — Business group context for the parent organization.
  • SUB_ORG_BUSINESS_GROUP_ID — Business group context for the sub-organization.
  • ORGANIZATION_STRUCTURE_ID — Foreign key to PER_ORGANIZATION_STRUCTURES, linking the summary to the underlying structure definition.
  • LAST_PTNTL_CHANGE — Timestamp or marker for the most recent potential change event, supporting incremental refresh of summary data.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns tracking record creation and modification.

Common Use Cases and Queries

The primary use case is hierarchical reporting across organization structures without recursive SQL. A typical query returns all sub-organizations beneath a given parent for a specific structure version:

  • SELECT SUB_ORGANIZATION_ID, SUB_ORGANIZATION_LEVEL FROM HRI_ORG_HRCHY_SUMMARY WHERE ORG_STRUCTURE_VERSION_ID = :version AND ORGANIZATION_ID = :org_id;
  • Flattening a hierarchy for BI Publisher or Discoverer reports that require level-based rollups.
  • Reconstructing organization trees for a historical point in time by joining on ORG_STRUCTURE_VERSION_ID.
  • Identifying relative depth differences to validate hierarchy configuration or data quality.
  • Feeding HR analytics dashboards in the HRI product area, acknowledging the obsolete status of the module.

Because the module is obsolete, new development should prefer current HRMS hierarchy APIs; this table is best used for maintenance or migration of legacy reporting.

Related Objects

  • PER_ORGANIZATION_STRUCTURES — Referenced via the foreign key HRI_ORG_HRCHY_SUMMARY.ORGANIZATION_STRUCTURE_ID. Join column: ORGANIZATION_STRUCTURE_ID.
  • HR_ALL_ORGANIZATION_UNITS — Source for ORGANIZATION_ID and SUB_ORGANIZATION_ID descriptive details.
  • HR_ORGANIZATION_UNITS — Base organization definition table underlying the hierarchy.
  • PER_BUSINESS_GROUPS — Provides business group context for ORG_BUSINESS_GROUP_ID and SUB_ORG_BUSINESS_GROUP_ID.
  • FND_FLEX_VALUES / HR_LOOKUPS — Commonly joined to translate level values into display labels.
  • HRI_ORG_HRCHY_SUMMARY_PK / HRI_ORG_HRCHY_SUMMARY_U1 — Constraints and indexes enforcing uniqueness.
  • PER_ORG_STRUCTURE_ELEMENTS / PER_ORG_STRUCTURE_VERSIONS — Versioning parents that define ORG_STRUCTURE_VERSION_ID semantics.