Results for “psp_schedule_hierarchy”

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

Overview

PSP_SCHEDULE_HIERARCHY is a table within the PSP (Labor Distribution) product in Oracle E-Business Suite, documented as VALID in releases 12.1.1 and 12.2.2. It stores schedule hierarchy level information about a schedule, sitting functionally between the schedule header records and the individual schedule lines that drive labor cost distribution. In the Oracle Labor Distribution model, schedules group element assignments under an element group and set of books, and the hierarchy level provides the structural context by which those assignments are aggregated and costed. The table therefore supports the definition, validation, and processing of labor distribution schedules.

From a dimensional modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification. The table carries descriptive and classification attributes (element type, element group, scheduling type) attached to a schedule entity, rather than acting purely as a junction between two hubs. This classification is a modeling suggestion based on the documented FK topology, not a statement of Oracle's physical design intent.

Key Information Stored

The table is owned by the PSP schema and contains 12 documented columns. The most significant are:

• SCHEDULE_HIERARCHY_ID — the surrogate primary key, enforced by PSP_SCHEDULES_HIERARCHY_PK and uniquely indexed by PSP_SCHEDULE_HIERARCHY_U1. This is the column propagated to dependent tables.
• ELEMENT_GROUP_ID — foreign key to PSP_ELEMENT_GROUPS, identifying the element group to which the hierarchy level belongs.
• ASSIGNMENT_ID — foreign key to PSP_SCHEDULES, tying the hierarchy row to its parent schedule.
• ELEMENT_TYPE_ID — identifies the element type associated with the hierarchy level, providing the classification dimension for costing.
• SCHEDULING_TYPES_CODE — the scheduling type code that distinguishes the type of schedule hierarchy record being defined.
• SET_OF_BOOKS_ID — foreign key to GL_SETS_OF_BOOKS, anchoring the record to a ledger.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, anchoring the record to a business group.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard EBS who-columns, essential for data reconciliation and audit reporting.

The unique index PSP_SCHEDULE_HIERARCHY_U1 covers only the surrogate key; no separate business-key candidate is documented, so uniqueness of a hierarchy level is effectively managed through the combination of ASSIGNMENT_ID, ELEMENT_GROUP_ID, ELEMENT_TYPE_ID, and SCHEDULING_TYPES_CODE in practice.

Common Use Cases and Queries

Typical uses include reconciling schedule hierarchy definitions before running labor distribution, reporting element group coverage by ledger and business group, and diagnosing distribution variances. A representative query joins the hierarchy to its parent schedule and element group:

• SELECT h.schedule_hierarchy_id, h.assignment_id, h.element_group_id, h.element_type_id, h.scheduling_types_code FROM psp_schedule_hierarchy h WHERE h.set_of_books_id = :ledger_id;
• Joining to PSP_SCHEDULE_LINES on SCHEDULE_HIERARCHY_ID to verify that each hierarchy level has detail lines: SELECT h.schedule_hierarchy_id, COUNT(l.schedule_line_id) FROM psp_schedule_hierarchy h, psp_schedule_lines l WHERE h.schedule_hierarchy_id = l.schedule_hierarchy_id GROUP BY h.schedule_hierarchy_id;
• Filtering by BUSINESS_GROUP_ID to scope a report to a specific organization unit, or by ELEMENT_TYPE_ID to analyze distribution by element type.

Related Objects

The most significant related objects, based on documented foreign-key relationships, are:

• PSP_SCHEDULES — referenced through PSP_SCHEDULE_HIERARCHY.ASSIGNMENT_ID; the parent schedule entity.
• PSP_ELEMENT_GROUPS — referenced through ELEMENT_GROUP_ID; defines the element grouping for the hierarchy level.
• GL_SETS_OF_BOOKS — referenced through SET_OF_BOOKS_ID; the ledger context.
• HR_ALL_ORGANIZATION_UNITS — referenced through BUSINESS_GROUP_ID; the business group context.
• PSP_SCHEDULE_LINES — references this table via PSP_SCHEDULE_LINES.SCHEDULE_HIERARCHY_ID; holds the individual schedule lines under each hierarchy level.
• PSP_SCH_PERIOD_TOTAL — references this table via PSP_SCH_PERIOD_TOTAL.SCHEDULE_HIERARCHY_ID; stores period-level totals derived from the hierarchy.

Together these objects form the schedule-side data model consumed by Labor Distribution processing and reporting in Oracle EBS.