Results for “saturday_hours”

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

Overview

PA_SCHEDULES_HISTORY is a table in the PA (Projects) schema of Oracle E-Business Suite, documented as VALID in ETRM for releases 12.1.1 and 12.2.2. The table stores copies of changed schedule records. In the Oracle Projects scheduling model, the operational records reside in PA_SCHEDULES; whenever a schedule row is inserted, updated, or deleted, Oracle Projects writes a corresponding versioned copy into PA_SCHEDULES_HISTORY. The table therefore functions as an audit and versioning repository for schedule data, preserving the pre-change or post-change state of a schedule so that historical effort distributions, calendar assignments, and approval statuses remain available for reporting and reconciliation.

The ETRM metadata classifies this object heuristically as a link table within a Data Vault modeling view, based on its foreign key structure. This classification is a modeling suggestion: the table carries surrogate keys and multiple foreign key references (project, assignment, status), which is characteristic of a link-style relationship record rather than a pure descriptive satellite. Practitioners should treat this classification as guidance rather than as an Oracle-declared attribute.

Key Information Stored

The physical schema documents 26 columns. The most significant are summarized below.

• SCHEDULE_ID — Identifier of the schedule. Together with CHANGE_ID it forms the primary key PA_SCHEDULES_HISTORY_PK and the unique index PA_SCHEDULES_HISTORY_U1 (SCHEDULE_ID, CHANGE_ID).
• CHANGE_ID — Surrogate versioning identifier distinguishing successive historical versions of the same schedule; the second half of the composite primary key.
• ASSIGNMENT_ID — Reference to the project assignment associated with the schedule; foreign key to PA_PROJECT_ASSIGNMENTS and PA_ASSIGNMENTS_HISTORY.
• PROJECT_ID — Reference to the project owning the schedule; foreign key to PA_PROJECTS_ALL.
• STATUS_CODE — Status of the schedule version; foreign key to PA_PROJECT_STATUSES.
• CALENDAR_ID — Calendar applied to the schedule, driving the working-day pattern.
• SCHEDULE_TYPE_CODE — Classification of the schedule (for example, the distribution or pattern type).
• START_DATE / END_DATE — Effective date range of the schedule version.
• MONDAY_HOURS through SUNDAY_HOURS — Daily scheduled hours for each day of the week, representing the effort distribution.
• LAST_APPROVED_FLAG — Indicates whether this version represents the last approved schedule.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent-program audit context identifying the process that created the version.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

The surrogate versus business-key distinction is important: CHANGE_ID and SCHEDULE_ID together form the technical primary key, while SCHEDULE_ID and CHANGE_ID also constitute the business-key candidate exposed by unique index PA_SCHEDULES_HISTORY_U1.

Common Use Cases and Queries

The primary use case is historical effort reconstruction — reporting how a schedule's daily hour distribution changed over time for a given assignment or project. A representative query retrieves all versions of a schedule in chronological order:

• SELECT schedule_id, change_id, project_id, start_date, end_date, monday_hours, tuesday_hours, last_approved_flag FROM pa.pa_schedules_history WHERE schedule_id = :schedule_id ORDER BY change_id;
• SELECT h.schedule_id, h.change_id, h.project_id, h.assignment_id, h.status_code, h.start_date, h.end_date FROM pa.pa_schedules_history h JOIN pa.pa_projects_all p ON h.project_id = p.project_id WHERE h.project_id = :project_id;
• SELECT h.schedule_id, h.change_id, h.last_approved_flag, h.program_id, h.request_id, h.creation_date FROM pa.pa_schedules_history h WHERE h.last_approved_flag = 'Y' AND h.assignment_id = :assignment_id;

Typical reporting scenarios include audit trails for schedule approvals, trend analysis of project staffing patterns, reconciliation of schedule changes against concurrent program runs, and investigative queries when an assignment's scheduled hours appear inconsistent with the live PA_SCHEDULES record.

Related Objects

The following objects are most significant in relation to PA_SCHEDULES_HISTORY, using the documented foreign key relationships:

• PA_SCHEDULES — The operational source table whose changed rows are copied into this history table; joins on SCHEDULE_ID.
• PA_PROJECT_ASSIGNMENTS — Referenced via ASSIGNMENT_ID; provides the assignment context for each schedule version.
• PA_ASSIGNMENTS_HISTORY — Also referenced via ASSIGNMENT_ID, linking assignment-level history to schedule history.
• PA_PROJECTS_ALL — Referenced via PROJECT_ID; supplies project-level attributes for reporting.
• PA_PROJECT_STATUSES — Referenced via STATUS_CODE; resolves the status of each historical schedule version.
• PA_CALENDARS — Related through CALENDAR_ID for calendar-level detail (not a documented FK, but a practical join).

These relationships make PA_SCHEDULES_HISTORY a central versioning link between project, assignment, and status entities in Oracle Projects.