Search Results sub_ro_organization_level

Overview

APPS.HRIBV_ORG_HRCHY_ROLLUP is a read-only Oracle E-Business Suite view that exposes a flattened, three-level roll-up of the organization hierarchy defined in Oracle HRMS. It combines the top-level hierarchy definition, its version, an organization at one level, and a nested sub-organization at the next level, presenting them as denormalized rows suitable for reporting. The "IV" in the name, combined with the WITH READ ONLY clause, indicates an informational view intended for query access rather than maintenance; it has no DML capability and does not support the "Instead-Of" triggers needed for updates. In release 12.1.1 and 12.2.2 the view is owned by the APPS schema and is used primarily by HRMS and CRM reporting components, HR extracts, and custom dashboards that need to traverse the organization hierarchy without writing recursive PL/SQL.

The user search term "sub_ro_organization_level" maps directly to a column exposed by this view, indicating the user is likely resolving a report or query that references that column and seeking to understand its origin, semantics, and valid values.

Underlying Base Objects

The view is defined over five referenced objects plus two calls into packaged logic:

• PER_ORGANIZATION_STRUCTURES — the hierarchy master, supplying the hierarchy name (OST.NAME) and the primary-structure indicator.
• PER_ORG_STRUCTURE_VERSIONS — the versioned definition of a hierarchy, supplying version number and effective DATE_FROM / DATE_TO.
• HRI_ORG_HRCHY_SUMMARY — referenced twice (aliases OSH and SOSH), providing the expanded organization nodes and their levels.
• HR_ALL_ORGANIZATION_UNITS_TL — the translated organization names, joined for both the organization (ORGT) and the sub-organization (SORGT).
• HR_BIS (package) — supplies BIS_DECODE_LOOKUP('YES_NO', …) for the primary flag.
• HR_GENERAL (package) — supplies END_OF_TIME, used to substitute a sentinel date for NULL DATE_TO values.

The joins chain versions to summary rows and then self-join the summary on OSH.SUB_ORGANIZATION_ID = SOSH.ORGANIZATION_ID, which is what produces the sub-organization and sub-ROLLUP organization levels in a single row.

Key Columns

• HIERARCHY_NAME, HRCHY_PRIMARY_FLAG, HRCHY_PRIMARY_FLAG_CODE — hierarchy identity and primary indicator.
• HIERARCHY_VERSION, HIERARCHY_VERSION_DATE_FROM, HIERARCHY_VERSION_DATE_TO, HRCHY_VERSION_DATE_TO_NN — versioning metadata; the _NN column replaces a NULL end date with HR_GENERAL.END_OF_TIME for date-range comparisons.
• ORGANIZATION_NAME, ORGANIZATION_LEVEL, ORGANIZATION_ID, ORG_BUSINESS_GROUP_ID — the higher-level organization in the roll-up.
• SUB_ORGANIZATION_NAME, SUB_ORGANIZATION_LEVEL, SUB_ORGANIZATION_ID, SUB_ORG_BUSINESS_GROUP_ID — the intermediate organization.
• SUB_RO_ORGANIZATION_LEVEL, SUB_RO_ORGANIZATION_ID, SUB_RO_ORG_BUSINESS_GROUP_ID — the innermost level; SUB_RO_ORGANIZATION_LEVEL is the column referenced by the user's search and gives the depth of the lowest organization in the row.

Common Use Cases and Queries

Typical uses include validating hierarchy depth, flattening an organization tree for extracts, and filtering on a specific level. A simple query returning all three levels for the current version:

• SELECT hierarchy_name, organization_name, sub_organization_name, sub_ro_organization_id, sub_ro_organization_level FROM apps.hribv_org_hrchy_rollup WHERE sub_ro_organization_level <= 3 ORDER BY hierarchy_name, organization_level;

To locate a specific sub-organization, filter on SUB_RO_ORGANIZATION_ID or restrict by effective dates using HRCHY_VERSION_DATE_TO_NN >= SYSDATE. Because the view is read-only, it is suitable for BI Publisher data models, Discoverer workbooks, and OBIEE federated queries, but should not be used as a target for DML. For performance, ensure queries constrain on HIERARCHY_NAME or ORG_HIERARCHY_ID to limit the self-join over HRI_ORG_HRCHY_SUMMARY.