Search Results sub_absolute_level

Overview

PJI_DIS_SUP_HRCHY_V is an Oracle E-Business Suite reporting view owned by the APPS schema and defined in the Project Intelligence (PJI) product family. It is described in the ETRM documentation as a "Discoverer view of resource hierarchy data." Its purpose is to expose supervisory relationships between resources in a flattened, ready-to-query format so that Oracle Discoverer workbooks and other reporting tools can traverse the management hierarchy without embedding complex self-joins against the HR hierarchy tables.

The view resolves, for each supervisor/subordinate pairing, both the absolute position of each party in the primary hierarchy and the relative distance between them. By joining the hierarchy source to PER_ALL_PEOPLE_F twice — once for the supervisor and once for the subordinate — it enriches the relational hierarchy data with the person names and the effective-dating context of both records. It is a read-only object and does not store data; all values are derived at runtime.

Underlying Base Objects

The view text documented in ETRM defines PJI_DIS_SUP_HRCHY_V over three objects:

• HRI_CS_SUPH_V — aliased as MGR, the primary hierarchy source. This supplies the level columns, person and assignment identifiers, and effective dates. The join restricts results to rows where PRIMARY_HIERARCHY_FLAG_CODE = 'Y', so only the primary supervisory hierarchy is reported.
• PER_ALL_PEOPLE_F — aliased as SUP, providing the supervisor's FULL_NAME.
• PER_ALL_PEOPLE_F — aliased as SUB, providing the subordinate's FULL_NAME.

The two PER_ALL_PEOPLE_F instances are date-constrained joins: the MGR.EFFECTIVE_START_DATE must fall between the SUP and SUB rows' own EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. This date-bounded relationship ensures the names returned are the ones valid at the point in time represented by the hierarchy row.

Key Columns

• SUP_ABSOLUTE_LEVEL / SUB_ABSOLUTE_LEVEL — the depth of the supervisor and subordinate within the primary hierarchy, measured from the top.
• SUB_RELATIVE_LEVEL — the number of levels between the subordinate and supervisor, useful for span-of-control and roll-up calculations.
• SUP_PERSON_ID / SUB_PERSON_ID — the surrogate person identifiers from PER_ALL_PEOPLE_F for each party; SUB_PERSON_ID is the column referenced by users searching on "sub_person_id."
• SUP_ASSIGNMENT_ID / SUB_ASSIGNMENT_ID — the assignments associated with each person.
• SUP_NAME / SUB_NAME — the surnames of supervisor and subordinate, respectively, derived from FULL_NAME in the two PER_ALL_PEOPLE_F aliases.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date range for which this supervisor/subordinate relationship is valid.

Common Use Cases and Queries

Typical applications include org-chart reporting, span-of-control analysis, and feeding upstream project resource data into a Discoverer workbook. Because the view already supplies names, level positions, and effective dates, most queries filter directly on it rather than re-joining HR tables.

To list all direct reports of a given manager for a specific date:

SELECT sub_person_id, sup_name, sub_name
FROM   apps.pji_dis_sup_hrchy_v
WHERE  sup_person_id = :p_sup_person_id
AND    sub_relative_level = 1
AND    :p_effective_date BETWEEN effective_start_date AND effective_end_date;

To identify everyone inside a supervisor's chain of command:

SELECT sup_absolute_level, sub_absolute_level, sub_relative_level,
       sup_name, sub_name
FROM   apps.pji_dis_sup_hrchy_v
WHERE  sup_person_id = :p_sup_person_id
ORDER  BY sub_relative_level;

When querying on the column the user searched for, restrict on SUB_PERSON_ID (for a named subordinate's supervisor record) or SUP_PERSON_ID (for a named supervisor's report list). Because the object is a view and its source carries no documented base-table index guidance, performance filters should be applied on the effective dates and person identifiers to limit the underlying PER_ALL_PEOPLE_F scans.

Oracle Proprietary, Confidential Information — Legal Notices apply to all content described herein.