Search Results hribv_org_hrchy_rollup




Overview
Explore in AI Chat

Overview

HRIBV_ORG_HRCHY_ROLLUP is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the HRI (Human Resources Intelligence) product family. The object has a status of VALID under both 12.1.1 and 12.2.2, and it is designed to present a flattened, denormalized representation of the organization hierarchy defined in Oracle HRMS. It exposes hierarchy structures, their versions and effective dates, and the rolled-up organization and sub-organization relationships associated with each structure version.

The view is not a transactional object; it is a semantic layer intended for reporting, extraction, and integration consumption. Analysts and developers query it to reconstruct a hierarchy as it existed for a given version, and to associate organizations within that hierarchy with their parent/sub-organization placements. Because the view surfaces both surrogate identifiers and decoded descriptive names, it supports drill-down reporting and data warehouse loading without requiring joins back to the HRMS base tables.

Underlying Base Objects

The documented base objects for this view are PER_ORGANIZATION_STRUCTURES (SYNONYM), PER_ORG_STRUCTURE_VERSIONS (SYNONYM), HRI_ORG_HRCHY_SUMMARY (SYNONYM), HR_ALL_ORGANIZATION_UNITS_TL (SYNONYM), plus the packages HR_BIS and HR_GENERAL. All joins are equijoins keyed on identifier columns:

  • PER_ORGANIZATION_STRUCTURES supplies the hierarchy definition, linked to versions on ORGANIZATION_STRUCTURE_ID.
  • PER_ORG_STRUCTURE_VERSIONS supplies version attributes, joined via ORG_STRUCTURE_VERSION_ID.
  • HRI_ORG_HRCHY_SUMMARY appears twice (aliased OSH and SOSH), joined on ORG_STRUCTURE_VERSION_ID and on SUB_ORGANIZATION_ID = ORGANIZATION_ID, producing the organization-to-sub-organization rollup.
  • HR_ALL_ORGANIZATION_UNITS_TL appears three times (BGRT, ORGT, SORGT) to resolve the business group, organization, and sub-organization names, filtered by USERENV('LANG').
  • HR_BIS.BIS_DECODE_LOOKUP decodes the PRIMARY_STRUCTURE_FLAG into a YES_NO value.
  • HR_GENERAL.END_OF_TIME supplies the default upper bound when DATE_TO is null.

Key Columns

Common Use Cases and Queries

The most frequent scenario is locating the hierarchy version effective for a given date, which is why HIERARCHY_VERSION_DATE_FROM is a common search term. A typical query filters on that column:

SELECT HIERARCHY_NAME, HIERARCHY_VERSION, HIERARCHY_VERSION_DATE_FROM, HIERARCHY_VERSION_DATE_TO, ORGANIZATION_NAME, ORGANIZATION_LEVEL, SUB_ORGANIZATION_NAME FROM APPS.HRIBV_ORG_HRCHY_ROLLUP WHERE HIERARCHY_VERSION_DATE_FROM <= :p_effective_date AND HRCHY_VERSION_DATE_TO_NN >= :p_effective_date ORDER BY HIERARCHY_NAME, HIERARCHY_VERSION, ORGANIZATION_LEVEL;

Other uses include extracting the full hierarchy for a specific version, listing all organizations under a business group, or feeding HR data marts with a point-in-time hierarchy snapshot. Because the view handles null end-dates via HRCHY_VERSION_DATE_TO_NN, it is well suited to "as-of" reporting without additional NVL logic in the consuming query.