Search Results pa_schedule_exceptions




Overview
Explore in AI Chat

Overview

The PA_SCHEDULE_EXCEPTIONS table in the PA (Projects) schema stores schedule day exceptions that apply to project assignments and resource requirements. Each row represents a single deviation from the standard assignment or requirement schedule, capturing the manner in which a particular calendar day or date range diverges from the default working pattern defined elsewhere in the scheduling model. This enables Oracle EBS Project Resource Management to represent partial working days, additional working hours, non-working days, and calendar shifts without altering the base calendar definition.

The table resides in the PA schema, is marked VALID, and is documented in ETRM for both Oracle EBS 12.1.1 and 12.2.2 with 29 columns. The heuristic Data Vault classification mined from the foreign key structure is standalone, which suggests modeling this entity as an independent structure rather than as a link or satellite dependent on a parent hub via composite business keys. The single documented foreign key relationship — SCHEDULE_EXCEPTION_ID referencing CAC_SR_SCHDL_EXCEPTIONS — indicates an association with a schedule exception reference, though the standalone classification implies the table's primary business identity is self-contained.

Key Information Stored

The surrogate primary key is SCHEDULE_EXCEPTION_ID, which is also the sole unique index (PA_SCHEDULE_EXCEPTIONS_U1) documented as a business-key candidate. The remaining columns partition into four functional groups:

Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_BY, LAST_UPDATE_LOGIN) are also present as documented.

Common Use Cases and Queries

Typical scenarios involve detecting assignments whose effective schedule differs from the standard calendar, reconciling planned versus actual working hours, and reporting exceptions for a given project or date range.

  • Identify all exceptions for an assignment: SELECT * FROM PA_SCHEDULE_EXCEPTIONS WHERE ASSIGNMENT_ID = :assignment_id;
  • List non-working day exceptions in a window: SELECT * FROM PA_SCHEDULE_EXCEPTIONS WHERE NON_WORKING_DAY_FLAG = 'Y' AND START_DATE BETWEEN :from_date AND :to_date;
  • Summarize daily hour overrides per weekday to compute effective capacity for a resource.
  • Report calendar substitutions via CHANGE_CALENDAR_TYPE_CODE and CHANGE_CALENDAR_ID for compliance or payroll overlap analysis.

Related Objects

The documented FK links SCHEDULE_EXCEPTION_ID to CAC_SR_SCHDL_EXCEPTIONS, which supplies the reference schedule exception definition. Additional significant related objects based on the stored foreign-key-style columns and functional role include:

  • PA_ASSIGNMENTS — joined via ASSIGNMENT_ID for assignment-level exception reporting.
  • PA_PROJECTS — joined via PROJECT_ID for project-level aggregation.
  • PA_CALENDARS — joined via CALENDAR_ID (and CHANGE_CALENDAR_ID) to resolve working patterns.
  • PA_REQUIREMENTS (or equivalent requirement structures) referenced through SCHEDULE_TYPE_CODE for requirement-side exceptions.
  • CAC_SR_SCHDL_EXCEPTIONS — the FK parent supplying the exception reference.

Where these joins are performed, ensure the appropriate lookup translations for the various CODE columns (STATUS_CODE, EXCEPTION_TYPE_CODE, CHANGE_HOURS_TYPE_CODE) are applied to produce human-readable output.