Search Results sub_relative_level

Overview

FII_CS_SUPHRO_V1 is a reporting view shipped within the Oracle E-Business Suite Financial Intelligence (FII) product family, part of the broader Oracle HR Intelligence / Daily Business Intelligence (DBI) analytics layer. It exposes a "supervisor hierarchy and relationship" structure derived from the HRI_CS_SUPH collection, joining two instances of that hierarchy table on assignment identifiers to produce absolute and relative level information spanning a supervisor, a subordinate, and a subordinate-of-subordinate relationship.

The view is documented in ETRM metadata for E-Business Suite 12.1.1 and 12.2.2. Its role is analytic rather than transactional: it presents flattened hierarchy positioning data that reporting engines, dashboards, and downstream ETL can consume directly. The presence of absolute level, relative level, and effective date attributes makes it suitable for point-in-time supervisory reporting.

Underlying Base Objects

The documented view text references a single base object family: HRI_CS_SUPH, aliased twice as SHS_SUP (the supervisor-side row) and SHS_SUB (the subordinate-side row). Neither FII-specific base tables nor additional reference objects appear in the documented definition, and the ETRM metadata notes "Referenced base objects: none documented."

The join condition links SHS_SUB.SUP_ASSIGNMENT_ID to SHS_SUP.SUB_ASSIGNMENT_ID, meaning a higher-level row's own supervisor assignment is matched to a lower-level row's subordinate assignment. A second predicate enforces temporal overlap, requiring either the subordinate's effective start date to fall within the supervisor's effective range or the supervisor's start to fall within the subordinate's range. This overlap logic is what makes the view yield time-bounded hierarchy relationships rather than a static tree.

Implementation status is recorded as "Not implemented in this database," indicating the view may be present in a target instance only after the relevant HR Intelligence/FII product patches are applied and the corresponding collection tables are populated by the ETL.

Key Columns

• SUP_ABSOLUTE_LEVEL / SUB_ABSOLUTE_LEVEL — Absolute depth of the supervisor and subordinate rows within the hierarchy, taken directly from HRI_CS_SUPH's SUP_LEVEL/SUB_LEVEL.
• SUB_RELATIVE_LEVEL — The subordinate's relative level with respect to the supervisor.
• SUBRO_ABSOLUTE_LEVEL / SUBRO_SUP_RELATIVE_LEVEL / SUBRO_SUB_RELATIVE_LEVEL — Levels for the subordinate-of-subordinate row, including its relationship to both the subordinate and the supervisor; SUBRO_SUB_RELATIVE_LEVEL is computed as SHS_SUB.SUB_LEVEL - SHS_SUP.SUP_LEVEL.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Intersected validity window, calculated with GREATEST of starts and LEAST of ends.
• PRIMARY_HIERARCHY_FLAG_CODE — Hard-coded literal 'Y'.
• SUP_PERSON_ID, SUP_ASSIGNMENT_ID, SUB_PERSON_ID, SUB_ASSIGNMENT_ID, SUBRO_PERSON_ID, SUBRO_ASSIGNMENT_ID — Person and assignment identifiers for each participant. The commonly searched sub_person_id corresponds to SHS_SUP.SUB_PERSON_ID, the person holding the subordinate assignment.
• Business group IDs — SUP, SUB, and SUBRO business group context.
• LAST_CHANGE_DATE — Latest update timestamp via GREATEST of both rows' LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical usage includes supervisory chain reporting, span-of-control analysis, and identifying third-level relationships without recursive SQL, since the view already supplies both SUB and SUBRO columns.

Retrieving directly reported subordinates for a given manager:

SELECT SUP_PERSON_ID, SUB_PERSON_ID, SUB_ASSIGNMENT_ID,
       SUB_RELATIVE_LEVEL, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
FROM   FII_CS_SUPHRO_V1
WHERE  SUP_ASSIGNMENT_ID = :p_supervisor_assignment_id
AND    SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Locating the supervisor of a named employee by person identifier:

SELECT SUP_PERSON_ID, SUP_ASSIGNMENT_ID, SUB_RELATIVE_LEVEL
FROM   FII_CS_SUPHRO_V1
WHERE  SUB_PERSON_ID = :p_sub_person_id
AND    PRIMARY_HIERARCHY_FLAG_CODE = 'Y';

Because the view performs an internal self-join over overlapped date ranges, queries against it can be expensive on large populations; filtering on assignment or person identifiers first, and constraining EFFECTIVE dates, generally produces the most efficient execution plans.