Search Results subro_sup_relative_level

Overview

The APPS.PJI_DIS_ORG_DENORM view PJI_DIS_ORG_ROHRCHY_V is a Project Intelligence (PJI) Discoverer view that exposes organization hierarchy data enriched with subordinate rollup information. It is designed as a reporting-facing object, providing a denormalized representation of the parent-child-grandchild relationship across organization units so that Discoverer workbooks and BI Publisher reports can traverse multi-level hierarchies without issuing recursive queries. The view is shipped in the APPS schema and is marked VALID in both Oracle EBS 12.1.1 and 12.2.2.

The view's distinguishing characteristic is the set of derived "relative level" columns that compute the numeric distance between organizations at different tiers of the hierarchy. This makes it particularly suited to analyses where the depth of subordination between an organization and its sub-organizations must be quantified rather than merely joined. The object supports the "subro_sup_relative_level" concept by exposing the arithmetic difference between sub-organization level and parent organization level directly as a column.

Underlying Base Objects

The view is defined over four base objects, all of which are documented in the ETRM metadata:

• PJI_ORG_DENORM — referenced twice, aliased as SUP and SUB. This denormalized organization table stores organization IDs and their assigned hierarchy levels, and is the principal source of the level arithmetic in the view.
• HR_ALL_ORGANIZATION_UNITS_TL — the multilingual organization definition table, referenced three times as HOU1, HOU2, and HOU3, supplying the translatable organization names.

The joins establish the hierarchy: SUP.SUB_ORGANIZATION_ID = SUB.ORGANIZATION_ID links a parent's sub-organization to the same organization appearing as a parent in the second instance, producing a three-tier chain (superior → sub → subordinate). Each organization name is resolved from the TL table using USERENV('LANG') to return only the session-language row.

Key Columns

• SUP_ORGANIZATION_ID / SUP_ORGANIZATION_NAME — the top-level (superior) organization.
• SUB_ORGANIZATION_ID / SUB_ORGANIZATION_NAME — the intermediate sub-organization.
• SUBRO_ORGANIZATION_ID / SUBRO_ORGANIZATION_NAME — the subordinate organization at the lowest tier.
• SUP_ORGANIZATION_LEVEL, SUB_ORGANIZATION_LEVEL, SUBRO_ORGANIZATION_LEVEL — the hierarchy level values for each tier as stored in PJI_ORG_DENORM.
• SUB_RELATIVE_LEVEL — SUP.SUB_ORGANIZATION_LEVEL - SUP.ORGANIZATION_LEVEL, the relative depth from the superior to the sub-organization.
• SUBRO_SUP_RELATIVE_LEVEL — SUB.SUB_ORGANIZATION_LEVEL - SUP.ORGANIZATION_LEVEL, the relative depth from the superior directly to the subordinate.
• SUBRO_SUB_RELATIVE_LEVEL — SUB.SUB_ORGANIZATION_LEVEL - SUB.ORGANIZATION_LEVEL, the relative depth from the sub-organization to the subordinate.

Common Use Cases and Queries

Typical uses include organization rollup reporting, security-driven hierarchy filtering, and level-based aggregation in Discoverer or custom concurrent programs.

Example — retrieve all hierarchy rows for a given superior organization, ordered by relative depth:

SELECT sup_organization_id,
       sup_organization_name,
       sub_organization_id,
       sub_organization_name,
       subro_organization_id,
       subro_organization_name,
       subro_sup_relative_level
FROM   apps.pji_dis_org_rohrchy_v
WHERE  sup_organization_id = :p_org_id
ORDER BY subro_sup_relative_level;

Example — count subordinates at each relative level beneath a superior:

SELECT subro_sup_relative_level,
       COUNT(DISTINCT subro_organization_id) subro_count
FROM   apps.pji_dis_org_rohrchy_v
GROUP BY subro_sup_relative_level
ORDER BY subro_sup_relative_level;

Because the view resolves names through the multilingual TL table using the session language, callers should set the language environment appropriately before querying to ensure organization names return in the expected locale.