Search Results sub_absolute_level




Overview
Explore in AI Chat

Overview

HRI_CL_ORG_CC_MGR_SUPH_V is an APPS-owned database view within the Oracle EBS Human Resources Intelligence (HRI) product family. It exposes organizational cost-center hierarchy data joined to supervisor/supervisee (line-manager) relationships derived from HRMS security and hierarchy structures. The view is primarily consumed by HRI analytical dashboards, HR workforce reporting, and integration extracts that require a flattened, query-ready representation of manager-to-subordinate chains scoped by organization cost centers.

Its design supports Oracle EBS 12.1.1 and 12.2.2, where HRI views serve as semantic layers over the HRMS base schema, hiding multi-table joins from end-user reporting tools such as Oracle BI, Discoverer, and custom BI Publisher reports. The view carries effective-dated supervisor relationships, enabling point-in-time workforce analysis.

Underlying Base Objects

The view is defined over three referenced objects:

The join predicates connect cost centers to the organization alias person and then to the supervisor hierarchy, producing a manager-scoped cost-center hierarchy. No further base tables are documented in the ETRM metadata.

Key Columns

  • ID — cost-center/organization identifier from the CC view.
  • VALUE — descriptive name or code of the cost center.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — validity window of the supervisor relationship.
  • SUP_PERSON_ID — the supervising (manager) person's identifier.
  • SUB_PERSON_ID — the subordinate person's identifier.
  • SUB_RELATIVE_LEVEL — relative depth of the subordinate within its reporting line.
  • SUB_ABSOLUTE_LEVEL — absolute depth of the subordinate within the full supervisor hierarchy, independent of the selected root. This column matches the search term "sub_absolute_level" and is typically used to distinguish top-level managers (low values) from deep-line reports (higher values) for filtering or aggregation.

Common Use Cases and Queries

Typical uses include headcount-by-manager reporting, cost-center roll-ups, span-of-control analysis, and effective-dated organizational snapshots.

List managers at a specific absolute level:

SELECT id, value, sup_person_id, sub_person_id, sub_absolute_level
FROM apps.hri_cl_org_cc_mgr_suph_v
WHERE sub_absolute_level = 1
AND sysdate BETWEEN effective_start_date AND effective_end_date;

Count direct reports per cost center:

SELECT value, COUNT(DISTINCT sub_person_id) headcount
FROM apps.hri_cl_org_cc_mgr_suph_v
WHERE sysdate BETWEEN effective_start_date AND effective_end_date
GROUP BY value;

Trace a subordinate's reporting depth:

SELECT sup_person_id, sub_person_id, sub_relative_level, sub_absolute_level
FROM apps.hri_cl_org_cc_mgr_suph_v
WHERE sub_person_id = :p_person_id;

When querying, always apply the effective-date window to avoid double-counting records across overlapping supervisor versions.