Search Results pa_schedule_exceptions_hist_u1

Overview

PA.PA_SCHEDULE_EXCEPT_HISTORY is an Oracle EBS Projects (PA) schema table that stores the audit history of schedule exceptions applied to assignments, requirements, and project schedules. When a user initiates a schedule change — for example, altering working hours, adjusting a calendar, or shifting duration — the application writes an exception record into this table for future audit and reporting purposes. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are held in APPS_TS_TX_IDX.

In Data Vault modeling terms, this object is classified heuristically as standalone. It functions more like a satellite-style history table: each row captures a snapshot of exception attributes at the point an exception is applied to a schedule, keyed by a system-generated identifier rather than a composite business key. Modelers should treat SCHEDULE_EXCEPTION_ID as the driving identifier and the audit columns as descriptive context.

Key Information Stored

The table contains 30 documented columns. The following are the most operationally significant:

• SCHEDULE_EXCEPTION_ID — System-generated number uniquely identifying the schedule exception. This is the surrogate primary key and the single column supporting the unique index PA_SCHEDULE_EXCEPTIONS_HIST_U1.
• ASSIGNMENT_ID — Identifier of the assignment or requirement; populated for OPEN_ASSIGNMENT and STAFFED_ASSIGNMENT schedule types. Indexed by the non-unique index PA_SCHEDULE_EXCEPTIONS_HIST_N1.
• CALENDAR_ID — Identifier of the calendar against which the schedule is defined.
• PROJECT_ID — Identifier of the project; populated for OPEN_ASSIGNMENT, STAFFED_ASSIGNMENT, and PROJECT schedule types.
• SCHEDULE_TYPE_CODE — Lookup code indicating the entity type for which the schedule exists.
• STATUS_CODE — Status of the assignment or requirement at the time of the exception.
• EXCEPTION_TYPE_CODE — Lookup code describing the nature of the schedule change initiated by the user.
• START_DATE / END_DATE — Effective range over which the exception applies.
• MONDAY_HOURS through SUNDAY_HOURS — Per-day hour allocations capturing the modified working pattern.
• CHANGE_HOURS_TYPE_CODE, DURATION_SHIFT_TYPE_CODE, DURATION_SHIFT_UNIT_CODE, NUMBER_OF_SHIFT — Codes and quantities that describe shift and duration changes.
• CHANGE_CALENDAR_TYPE_CODE / CHANGE_CALENDAR_ID — Describe calendar substitution exceptions.
• CHANGE_ID — Correlates the exception to a change event; indexed together with ASSIGNMENT_ID.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_BY, LAST_UPDATE_LOGIN capture WHO columns.

Common Use Cases and Queries

Typical uses center on audit reconstruction and payroll or costing reconciliation. A common query retrieves the exception history for an assignment within a date window:

SELECT schedule_exception_id, exception_type_code,
       start_date, end_date, monday_hours, tuesday_hours
FROM   pa.pa_schedule_except_history
WHERE  assignment_id = :assignment_id
AND    start_date >= :from_date
ORDER BY creation_date;

Reporting scenarios include reconciling applied exceptions against the source schedule, tracing which user applied a calendar change, and analyzing how often shift exceptions occur per project. Because CHANGE_ID is indexed alongside ASSIGNMENT_ID, joins back to the originating change event are efficient.

Related Objects

• CAC_SR_SCHDL_EXCEPTIONS — Referenced through SCHEDULE_EXCEPTION_ID, providing the originating exception definition.
• PA_ASSIGNMENTS — Joined on ASSIGNMENT_ID to resolve assignment detail.
• PA_PROJECTS — Joined on PROJECT_ID to resolve project context.
• PER_ASSIGNMENTS_F — Correlates assignment identifiers to HR assignment records.
• PA_SCHEDULE_EXCEPTIONS — The live exception table whose mutations drive history inserts.
• PA_CALENDARS — Resolves CALENDAR_ID and CHANGE_CALENDAR_ID.