Results for “superior_id”

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

Overview

The PO_EMPLOYEE_HIERARCHIES_ALL table, owned by the PO (Purchasing) schema, stores employee and position hierarchy relationships used throughout Oracle E-Business Suite Purchasing and related procurement flows. It defines the reporting structure between an employee and their superior within a given position structure and operating unit, enabling approval routing, delegation, and document-signing hierarchies. Because it is an "_ALL" table, records are partitioned by ORG_ID (operating unit) and BUSINESS_GROUP_ID, and the same physical table serves multiple organizations under Multi-Org access control.

From a heuristic Data Vault modeling perspective, this object is best characterized as a link candidate: it primarily captures many-to-many relationships between employees (via EMPLOYEE_ID and SUPERIOR_ID) and position structures (POSITION_STRUCTURE_ID), rather than acting as an independent hub or descriptive satellite. The relationship metadata classifies it as standalone, indicating no documented foreign keys to other tables within the mined schema, so referential integrity is effectively enforced at the application layer rather than through database constraints.

Key Information Stored

The table contains 17 documented columns and is identified by the primary key constraint PO_EMPLOYEE_HIERARCHIES_PK on the composite of EMPLOYEE_ID, SUPERIOR_ID, POSITION_STRUCTURE_ID, and ORG_ID. A separate unique index, PO_EMPLOYEE_HIERARCHIES_U1, provides a business-key candidate over EMPLOYEE_ID, SUPERIOR_ID, POSITION_STRUCTURE_ID, BUSINESS_GROUP_ID, and ORG_ID.

• EMPLOYEE_ID — the subordinate employee whose hierarchy relationship is recorded.
• SUPERIOR_ID — the employee acting as the superior (approver or manager) for the subordinate.
• POSITION_STRUCTURE_ID — identifies the position hierarchy structure to which the relationship belongs.
• EMPLOYEE_POSITION_ID and SUPERIOR_POSITION_ID — the positions held by the employee and superior respectively.
• SUPERIOR_LEVEL — the numeric level of the superior within the hierarchy, supporting approval-routing lookups.
• ORG_ID — the operating unit that owns the record, driving Multi-Org security.
• BUSINESS_GROUP_ID — the HR business group context for the employee relationship.
• LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns capturing who created or changed the row and when. Additional program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) track the concurrent program that last processed the record.

Common Use Cases and Queries

This table is typically queried to resolve approval hierarchies for purchasing documents, to determine a superior's identity for workflow routing, and to trace reporting lines across position structures. Analysts frequently join it to HR employee tables to display names, and to position tables to reconstruct organizational charts.

• Resolve a superior for an employee: SELECT superior_id FROM po_employee_hierarchies_all WHERE employee_id = :emp AND org_id = :org AND position_structure_id = :ps.
• List direct reports: SELECT employee_id, superior_level FROM po_employee_hierarchies_all WHERE superior_id = :mgr AND org_id = :org.
• Validate hierarchy integrity: confirm that each EMPLOYEE_ID/SUPERIOR_ID/POSITION_STRUCTURE_ID/ORG_ID combination is unique under the primary key and business-key index.
• Audit changes: filter on LAST_UPDATE_DATE or REQUEST_ID to identify rows modified by a specific concurrent process.

Because the object is classified as standalone with no documented foreign keys, queries should validate relationships at the application or join level rather than relying on database-enforced constraints.

Related Objects

Although the mined FK metadata classifies this table as standalone, application and reporting usage commonly references the following related objects through the shared key columns:

• PER_ALL_PEOPLE_F — joined on EMPLOYEE_ID and SUPERIOR_ID to resolve employee names and attributes.
• PER_ALL_POSITIONS (or PER_ALL_POSITIONS_F) — joined on EMPLOYEE_POSITION_ID and SUPERIOR_POSITION_ID for position details.
• PER_POSITION_STRUCTURES — joined on POSITION_STRUCTURE_ID to identify the hierarchy structure.
• HR_ALL_ORGANIZATION_UNITS — joined on ORG_ID / BUSINESS_GROUP_ID for organization context.
• PO_EMPLOYEE_HIERARCHIES_PK / _U1 — the primary key and unique index constraints that enforce record integrity.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the concurrent program that populated or updated the rows.

In EBS 12.1.1 and 12.2.2 the physical definition (17 columns, PO schema) is consistent, and the table remains central to Purchasing approval-hierarchy resolution.