Search Results sup_name




Overview
Explore in AI Chat

Overview

APPS.PJI_DIS_SUP_HRCHY_V is a public Oracle E-Business Suite view owned by the APPS schema and registered under FND Design Data as PJI.PJI_DIS_SUP_HRCHY_V. It exposes the HR supervisor hierarchy — commonly referred to as the manager hierarchy — in a flattened, reportable form. Unlike the underlying Oracle HRMS supervisor structures, which are date-effective against the primary active assignment of each person, this view presents a deliberately shallow two-level hierarchy: a supervisor row, a subordinate row, and the association between them. The view carries a status of VALID and is documented as useful for custom reporting and other data requirements, meaning Oracle supports direct query access even though the view is not part of a shipped concurrent program or form. The view makes no distinction between assignment statuses beyond what the base hierarchy object enforces, so results reflect the active, primary assignments as of the effective dating held in the source data.

Underlying Base Objects

The view is defined over two documented dependencies. The primary structural source is APPS.HRI_CS_SUPH_V, the HR Intelligence supervisor hierarchy view, which supplies the hierarchical supervisor-to-subordinate relationships and the absolute and relative level numbering. That hierarchy object is date effective and resolves the supervisor of a person from the HR primary active assignment, which is why the PJI view carries EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. The second dependency is PER_ALL_PEOPLE_F, the date-tracked person table, which supplies the person-level attributes used to derive the supervisor and subordinate display names. The view is read-only and, per the documented dependency list, is not referenced by any other database object, confirming it is a terminal reporting object rather than an intermediate construct used by other Oracle code.

Key Columns

  • SUP_ABSOLUTE_LEVEL — NUMBER(15). Absolute hierarchy level of the supervisor within the supervisor hierarchy.
  • SUB_ABSOLUTE_LEVEL — NUMBER(15). Absolute hierarchy level of the subordinate; in a two-level hierarchy this sits one level below the supervisor.
  • SUB_RELATIVE_LEVEL — NUMBER(15). The subordinate's level expressed relative to the supervisor, typically 1 for a direct report.
  • SUP_PERSON_ID — NUMBER(15). PERSON_ID of the supervisor.
  • SUB_PERSON_ID — NUMBER(15). PERSON_ID of the subordinate.
  • SUP_ASSIGNMENT_ID — NUMBER(15). ASSIGNMENT_ID used to resolve the supervisor.
  • SUB_ASSIGNMENT_ID — NUMBER(15). ASSIGNMENT_ID used to resolve the subordinate.
  • SUP_NAME — VARCHAR2(240). Display name of the supervisor; this is the column most frequently targeted by searches on "sup_name".
  • SUB_NAME — VARCHAR2(240). Display name of the subordinate.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — DATE. Date-effective boundaries inherited from the HR supervisor hierarchy, allowing point-in-time reconstruction of the reporting line.

Common Use Cases and Queries

Typical uses include building supervisor roll-up reports for project or resource staffing, rendering "reports to" fields in custom OAF or BI Publisher output, and reconciling assignment-level data against the HR supervisor chain. Because the view is date effective, queries should restrict EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to the reporting period of interest. A simple direct-report listing follows:

SELECT sup_name, sub_name
FROM   apps.pji_dis_sup_hrchy_v
WHERE  sub_relative_level = 1
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

To locate a specific supervisor by name, constrain SUP_NAME using a case-insensitive comparison on the uppercased value, for example WHERE UPPER(sup_name) = 'SMITH, JOHN'. Joining back to PER_ALL_PEOPLE_F or PER_ALL_ASSIGNMENTS_F on the person or assignment identifiers allows enrichment with employee number, organization, or job attributes. Because the hierarchy is limited to two levels, multi-level roll-ups require repeated self-joins or recursive processing outside the view.