Search Results hri_cs_suph_x_v




Overview
Explore in AI Chat

Overview

APPS.HRIBV_SUPV_HRCHY_ROLLUP_X is a read-only reporting view in Oracle E-Business Suite 12.1.1 and 12.2.2 that presents a fully denormalised, two-level roll-up of the supervisor hierarchy. It joins the supervisor hierarchy extraction view to itself, self-referencing HRI_CS_SUPH_X_V on the subordinate assignment, so that each output row describes a three-party relationship: a supervisor, that supervisor's direct subordinate, and the subordinate's own subordinate. This "roll-up" structure allows report developers to traverse two levels of the reporting chain in a single query without recursive self-joins or CONNECT BY clauses.

The view is part of the Oracle HRMS intelligence/analytics layer and is typically consumed by HR reporting extracts, hierarchy validation programs, and third-party integration that needs to flatten supervisory chains into a tabular format. It is defined WITH READ ONLY, so it cannot be used as the target of DML and is intended purely for query access. Note that the object name is frequently confused with its underlying source, HRI_CS_SUPH_X_V, which is the object users searching for hierarchy views will most commonly encounter.

Underlying Base Objects

ETRM 12.2.2 documents the following referenced base objects:

  • HRI_CS_SUPH_X_V (VIEW) — the single physical source, aliased twice as SVL (the supervisor/subordinate pair) and SSVL (the subordinate's own subordinate).
  • HR_ALL_ORGANIZATION_UNITS_TL (SYNONYM) — supplies the supervisor's business group name, joined on ORGANIZATION_ID = SUP_BUSINESS_GROUP_ID and filtered to USERENV('LANG').
  • PER_PEOPLE_X (VIEW) — provides supervisor and subordinate person names/employee numbers via two aliases (PER, PER2).
  • HR_BIS (PACKAGE) — BIS_DECODE_LOOKUP('YES_NO', ...) decodes the primary-assignment flag into a display value.
  • HR_GENERAL, HR_PERSON_NAME, HR_SECURITY (PACKAGEs) — referenced by the underlying hierarchy view for name formatting and row-level security predicates.

The HR_SECURITY dependency is significant: rows returned are implicitly filtered by the querying user's security profile, so two users may see different result sets against the same SQL.

Key Columns

The SUB_RO_ (sub roll-up) columns carry NULLs for leaf employees, which is the standard mechanism for identifying the bottom of a reporting chain.

Common Use Cases and Queries

Typical scenarios include validating that every manager of a manager has a documented second-level report, building organisation-chart extracts, and flattening supervisory spans for workforce analytics.

Count of second-level reports per supervisor:

  • SELECT supervisor_person_name, COUNT(DISTINCT sub_ro_person_id) FROM apps.hribv_supv_hrchy_rollup_x GROUP BY supervisor_person_name;

Identify supervisors whose subordinates have no further reports:

  • SELECT supervisor_employee_number, subordinate_person_name FROM apps.hribv_supv_hrchy_rollup_x WHERE sub_ro_assignment_id IS NULL AND supervisor_level < 5;

Business-group-scoped three-level listing, filtered by the supervisor's organisation:

  • SELECT supervisor_business_group, supervisor_person_name, subordinate_person_name FROM apps.hribv_supv_hrchy_rollup_x WHERE supervisor_business_group_id = :p_bg_id ORDER BY supervisor_level, subordinate_level;

Because the view is read-only and already contains decoded, denormalised values, it is best suited to direct extraction and reporting rather than as a staging or update target.