Results for “sub_assignment_id”

2 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The HRI.HRI_SUPV_HRCHY_SUMMARY table is a denormalized summary object within the Oracle Human Resources Intelligence (HRI) product family. Its documented description — "Supervisor Hierarchy Summary Table" — identifies its purpose: to persist a pre-resolved, flattened representation of the supervisor-to-subordinate reporting relationship across the enterprise, keyed by business group, supervisor, and subordinate identifiers. In Oracle EBS 12.1.1 and 12.2.2, HRI objects typically support HR analytics, workforce reporting, and hierarchical drill-downs that would otherwise require recursive traversal of PER_ASSIGNMENTS_F and related HR tables.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault model. This is a modeling suggestion only: the table carries no foreign keys to external hubs, so it does not naively resolve to a link or satellite. From a dimensional modeling perspective, the composite primary key (SUPV_BUSINESS_GROUP_ID, SUPV_PERSON_ID, SUPV_ASSIGNMENT_ID, SUB_PERSON_ID, SUB_ASSIGNMENT_ID) behaves as a link-style association between supervisor and subordinate business keys, while the remaining descriptive and audit columns act as satellite attributes. Because the physical schema is self-contained, it is best treated as a materialized hierarchy snapshot rather than a normalized hub.

Key Information Stored

The table contains 16 documented columns. The most consequential are summarized below.

  • SUPV_BUSINESS_GROUP_ID — business group of the supervisor; the leading column of both the primary key and the unique index.
  • SUPV_PERSON_ID — the supervisor's person identifier (PARTY-style key).
  • SUPV_ASSIGNMENT_ID — the supervisor's assignment identifier, distinguishing the specific assignment used to establish the hierarchy.
  • SUB_BUSINESS_GROUP_ID — business group of the subordinate, allowing cross-business-group reporting lines to be represented where configured.
  • SUB_PERSON_ID — the subordinate's person identifier.
  • SUB_ASSIGNMENT_ID — the subordinate's assignment identifier.
  • SUPV_LEVEL and SUB_LEVEL — numeric depth indicators for the supervisor and subordinate nodes within the hierarchy, enabling level-based filtering and roll-up reporting.
  • SUB_PRIMARY_ASG_FLAG — indicates whether the subordinate assignment is the primary assignment, useful for deduplicating employees with multiple assignments.
  • SUPV_LAST_PTNTL_CHANGE and SUB_LAST_PTNTL_CHANGE — timestamps of the last potential hierarchy-affecting change on the supervisor and subordinate sides, supporting incremental refresh logic.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns maintained by the WHO columns convention.

The surrogate/business key is defined by HRI_SUPV_HRCHY_SUMMARY_PK, mirrored by the unique index HRI_SUPV_HRCHY_SUMMARY_U1 over the same five columns: SUPV_BUSINESS_GROUP_ID, SUPV_PERSON_ID, SUPV_ASSIGNMENT_ID, SUB_PERSON_ID, SUB_ASSIGNMENT_ID. No separate single-column surrogate is documented.

Common Use Cases and Queries

Typical reporting patterns traverse the summary to produce span-of-control, headcount-by-manager, and multi-level roll-up analyses. A common query retrieves all direct and indirect subordinates of a given supervisor:

  • Filter on SUPV_PERSON_ID and SUPV_BUSINESS_GROUP_ID to obtain the full downstream set.
  • Restrict to SUB_PRIMARY_ASG_FLAG = 'Y' to avoid double-counting multi-assignment workers.
  • Compare SUB_LEVEL minus SUPV_LEVEL to determine reporting depth.
  • Join to PER_ALL_PEOPLE_F on SUB_PERSON_ID for assignment-level attributes, and to PER_ALL_ASSIGNMENTS_F on SUB_ASSIGNMENT_ID.

Incremental maintenance can leverage SUPV_LAST_PTNTL_CHANGE and SUB_LAST_PTNTL_CHANGE to identify rows affected by organizational restructuring since the prior refresh.

Related Objects

The metadata records no foreign keys, so related objects are inferred from HR core dependencies rather than documented referential constraints.

  • PER_ALL_PEOPLE_F — join on SUPV_PERSON_ID / SUB_PERSON_ID.
  • PER_ALL_ASSIGNMENTS_F — join on SUPV_ASSIGNMENT_ID / SUB_ASSIGNMENT_ID.
  • HR_ALL_ORGANIZATION_UNITS — resolve the business group via SUPV_BUSINESS_GROUP_ID and SUB_BUSINESS_GROUP_ID.
  • PER_JOBS / PER_POSITIONS — enrich hierarchy rows with job and position context.
  • HR_ORGANIZATION_INFORMATION — resolve hierarchy and organization classification values.
  • HRI_OLAP_* business intelligence objects — downstream analytics that consume HR summary tables.

Because the table is standalone, referential integrity with these objects must be enforced by the application layer rather than by database constraints.