Search Results psp_schedule_hierarchy




Overview
Explore in AI Chat

Overview

The PSP_SCHEDULE_HIERARCHY table is a core data structure within the Oracle E-Business Suite (EBS) Labor Distribution module (PSP). It serves as the master definition table for schedule hierarchy levels, which are critical for organizing and processing labor cost distributions. The table's primary role is to define the relationship between a specific labor schedule, its associated financial and organizational contexts (Set of Books and Business Group), and the grouping of earning elements. It acts as a central hub, linking high-level schedule configuration to the detailed schedule lines that contain actual distribution rules.

Key Information Stored

The table stores configuration metadata that governs how labor costs are allocated. Its primary key is the unique identifier, SCHEDULE_HIERARCHY_ID. The table's critical foreign key columns define its relationships: ASSIGNMENT_ID, BUSINESS_GROUP_ID, and SET_OF_BOOKS_ID link to the parent PSP_SCHEDULES table, ensuring the hierarchy is tied to a specific schedule within the correct organizational and financial context. The ELEMENT_GROUP_ID links to PSP_ELEMENT_GROUPS, associating a specific set of earnings elements with this hierarchy level. This structure allows a single labor schedule to have multiple, organized hierarchy levels for detailed cost allocation across different element groups.

Common Use Cases and Queries

This table is central to inquiries and reports regarding the setup of labor distribution schedules. A common use case is validating the complete hierarchy structure for a schedule to troubleshoot distribution issues. Developers and functional consultants often query this table to understand which earning element groups are assigned to a schedule and their hierarchical order. A typical diagnostic SQL pattern joins PSP_SCHEDULE_HIERARCHY with PSP_SCHEDULES and PSP_ELEMENT_GROUPS.

SELECT sh.schedule_hierarchy_id,
       s.schedule_name,
       eg.element_group_name
FROM   psp_schedule_hierarchy sh,
       psp_schedules s,
       psp_element_groups eg
WHERE  sh.assignment_id = s.assignment_id
AND    sh.business_group_id = s.business_group_id
AND    sh.set_of_books_id = s.set_of_books_id
AND    sh.element_group_id = eg.element_group_id
AND    s.schedule_name = '&SCHEDULE_NAME';

This query retrieves all hierarchy levels and their associated element groups for a given schedule, which is essential for setup audits and impact analysis during changes.

Related Objects

PSP_SCHEDULE_HIERARCHY has integral relationships with several key EBS tables. It is a child of the PSP_SCHEDULES table, from which it inherits its core assignment and context. It is also a child of GL_SETS_OF_BOOKS and HR_ALL_ORGANIZATION_UNITS for financial and organizational validation. Its relationship with PSP_ELEMENT_GROUPS is crucial for defining the scope of earnings. Most importantly, it is a parent table to PSP_SCHEDULE_LINES, which holds the detailed distribution percentages and destination accounts for each element within the group defined in the hierarchy. This relationship means that every distribution rule in PSP_SCHEDULE_LINES must belong to a defined hierarchy level.