Search Results sub_organization_level




Overview
Explore in AI Chat

Overview

The view PJI_DIS_ORG_HIERARCHY_V (documented as PJI_DIS_ORG_HRCHY_V) is a Discoverer-oriented reporting object delivered under the PJI – Project Intelligence product family, a module now classified as obsolete in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose in the ETRM metadata is to provide a "Discoverer view of organization hierarchy data," meaning it was intended as a flattened, denormalized data source for Oracle Business Intelligence Discoverer worksheets and ad hoc analyses on organizational structures. For users searching on the term "organization_level," this view is particularly relevant because it explicitly exposes both ORGANIZATION_LEVEL and SUB_ORGANIZATION_LEVEL as selected columns, making it a direct reference point for hierarchies defined through level attributes rather than simple parent-child relationships.

The view was designed to reconcile the denormalized hierarchy storage used by Project Intelligence with the multilingual organization names maintained in the HR organization units tables. Its presence in the ETRM catalog serves as documentation of a legacy integration point rather than a supported current component.

Underlying Base Objects

According to the documented view text, the query is constructed over the following base objects:

  • PJI_ORG_DENORM — aliased as ORG, the denormalized Project Intelligence organization table that supplies organization IDs and level information.
  • HR_ALL_ORGANIZATION_UNITS_TL — aliased twice as HOU1 and HOU2, the translation (TL) table for HR organization units that provides the translated NAME values.

The joins are equi-joins between ORG.ORGANIZATION_ID and HOU1.ORGANIZATION_ID, and between ORG.SUB_ORGANIZATION_ID and HOU2.ORGANIZATION_ID. Both name lookups are filtered by HOUx.LANGUAGE = USERENV('LANG'), ensuring names are returned in the session's language. ETRM records the view as not implemented in the reference database and lists no additional referenced base objects beyond those shown in the view text.

Key Columns

  • ORGANIZATION_ID — Identifier of the parent/primary organization in the hierarchy.
  • ORGANIZATION_NAME — Translated name of the parent organization, sourced from HOU1.NAME.
  • SUB_ORGANIZATION_ID — Identifier of the subordinate organization.
  • SUB_ORGANIZATION_NAME — Translated name of the subordinate organization, sourced from HOU2.NAME.
  • ORGANIZATION_LEVEL — The hierarchy level assigned to the primary organization in PJI_ORG_DENORM.
  • SUB_ORGANIZATION_LEVEL — The hierarchy level assigned to the subordinate organization.
  • SUB_RELATIVE_LEVEL — A computed column expressing the difference SUB_ORGANIZATION_LEVEL - ORGANIZATION_LEVEL, giving the relative depth between the two organizations.

Common Use Cases and Queries

This view would typically be used to report on organization roll-ups, level depth, and parent-child relationships within Project Intelligence Discoverer workbooks. Because it is obsolete, queries should be treated as reference patterns rather than production assets.

A representative query selecting parent and child organizations together with their levels is:

  • SELECT ORGANIZATION_ID, ORGANIZATION_NAME, SUB_ORGANIZATION_ID, SUB_ORGANIZATION_NAME, ORGANIZATION_LEVEL, SUB_ORGANIZATION_LEVEL, SUB_RELATIVE_LEVEL
  • FROM PJI_DIS_ORG_HRCHY_V
  • WHERE ORGANIZATION_LEVEL < SUB_ORGANIZATION_LEVEL
  • ORDER BY ORGANIZATION_NAME, SUB_RELATIVE_LEVEL;

Given the obsolete status and the fact that the view is not implemented in the reference database, alternative current objects such as the HR organization hierarchy views should be evaluated for any replacement reporting requirement.