Search Results pa_schedule_exceptions_u1

Overview

PA.PA_SCHEDULE_EXCEPTIONS is a transactional table in the Oracle Projects (PA) schema that stores schedule day exceptions for assignments and requirements. Each row represents a deviation from a nominal project or resource schedule, whether that deviation originates from a calendar exception or from a deliberate update made to the schedule after its creation. The table therefore functions as the exception ledger for Oracle Projects scheduling, capturing the days and hours that differ from the underlying calendar or assignment baseline.

The object is owned by the PA schema, carries FND Design Data reference PA.PA_SCHEDULE_EXCEPTIONS, and is stored in the APPS_TS_INTERFACE tablespace with PCT Free 10. It is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. The ETRM physical schema records 29 columns. From a Data Vault modeling perspective, the heuristic classification for this object is standalone, meaning it does not participate in the described FK structure as a dependent link or satellite in the classical sense; it is best treated as an independent hub-like entity keyed on its own surrogate identifier, with the scheduling attributes acting as descriptive context.

Key Information Stored

The table is anchored by its surrogate primary key and a small set of business-meaningful identifiers and codes:

• SCHEDULE_EXCEPTION_ID — System-generated number that uniquely identifies the schedule exception. This column is enforced by the unique index PA_SCHEDULE_EXCEPTIONS_U1, making it the documented business-key candidate and the primary lookup path for the record.
• CALENDAR_ID — Identifier of the calendar for which the schedule is created.
• ASSIGNMENT_ID — Identifier of the assignment or requirement for which the schedule is created; populated for schedule_type_code values OPEN_ASSIGNMENT and STAFFED_ASSIGNMENT. A non-unique index, PA_SCHEDULE_EXCEPTIONS_N1, exists on this column, reflecting its use as the principal access path for assignment-centric reporting.
• PROJECT_ID — Identifier of the project for which the schedule is created; populated for OPEN_ASSIGNMENT, STAFFED_ASSIGNMENT, and PROJECT schedule types.
• SCHEDULE_TYPE_CODE — Code indicating the entity for which the schedule is created (for example, assignment, requirement, or project).
• STATUS_CODE — Status of the assignment or requirement associated with the exception.
• EXCEPTION_TYPE_CODE — Code describing the type of schedule change initiated by the user.
• RESOURCE_CALENDAR_PERCENT — Daily percentage of the resource calendar applicable to the exception.
• NON_WORKING_DAY_FLAG — Flag indicating non-working days within the schedule.
• START_DATE / END_DATE — Date boundaries over which the exception applies.
• MONDAY_HOURS through SUNDAY_HOURS — Per-weekday hour values that quantify the exception on each day of the week.
• DURATION_SHIFT_TYPE_CODE, DURATION_SHIFT_UNIT_CODE, NUMBER_OF_SHIFT — Attributes describing duration shifts applied to the schedule.
• CHANGE_CALENDAR_TYPE_CODE / CHANGE_CALENDAR_ID — Attributes identifying a calendar change associated with the exception.
• CHANGE_HOURS_TYPE_CODE — Code describing the nature of an hours change.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_BY, LAST_UPDATE_LOGIN) provide auditability.

Common Use Cases and Queries

Typical uses include auditing schedule deviations for a project or assignment, reconciling resource calendar exceptions against planned hours, and reporting non-working days for capacity analysis. A common query retrieves all exceptions for a given assignment:

• SELECT schedule_exception_id, calendar_id, project_id, exception_type_code, start_date, end_date, non_working_day_flag FROM pa.pa_schedule_exceptions WHERE assignment_id = :assignment_id ORDER BY start_date;
• SELECT schedule_type_code, exception_type_code, COUNT(*) FROM pa.pa_schedule_exceptions GROUP BY schedule_type_code, exception_type_code;
• SELECT b.project_number, e.* FROM pa.pa_schedule_exceptions e, pa.pa_projects_all b WHERE e.project_id = b.project_id AND e.non_working_day_flag = 'Y';

Reporting scenarios commonly join to project and assignment entities to attribute exceptions to a project number or assignment name, and filter by SCHEDULE_TYPE_CODE to isolate project-level versus assignment-level exceptions.

Related Objects

The documented FK metadata links PA_SCHEDULE_EXCEPTIONS.SCHEDULE_EXCEPTION_ID to CAC_SR_SCHDL_EXCEPTIONS, indicating a relationship through the shared schedule exception identifier. In practice, the most significant related objects are:

• PA.PA_PROJECTS_ALL — joined on PROJECT_ID for project context.
• PA.PA_ASSIGNMENTS (and requirement equivalents) — joined on ASSIGNMENT_ID for assignment-level detail.
• PA.PA_CALENDARS / calendar definitions — joined on CALENDAR_ID to resolve working-day rules.
• CAC_SR_SCHDL_EXCEPTIONS — referenced through SCHEDULE_EXCEPTION_ID per the documented FK relationship.
• PA.PA_SCHEDULE_EXCEPTIONS_U1 — the unique index on SCHEDULE_EXCEPTION_ID, the primary access path and the object most often referenced by the search term.
• PA.PA_SCHEDULE_EXCEPTIONS_N1 — the non-unique index on ASSIGNMENT_ID supporting assignment-based lookups.
• PA.PA_SCHEDULE_EXCEPTIONS_API — the public PL/SQL API commonly used to create, update, and delete schedule exceptions rather than direct DML.