Results for “cac_sr_schdl_exceptions”

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

Overview

PA_SCH_EXCPT_HSTRY_AR is the Oracle E-Business Suite table that stores the historical record of schedule exception requirements and assignments within the Projects (PA) module. Schedule exceptions represent deviations from a project's or resource's standard calendar, such as modified working hours, non-working days, or alternate shift definitions. The _AR suffix and the accompanying purge columns (PURGE_BATCH_ID, PURGE_RELEASE, PURGE_PROJECT_ID) indicate that this table participates in Oracle's archival and purge framework, retaining exception history so that audit and reconstruction requirements can be satisfied after operational records are moved or cleared.

When an exception is applied to a schedule — whether a project schedule, a resource calendar, or a change calendar — the resulting requirement and assignment details are written into this history table. Depending on the schedule type, differing subsets of columns are populated, which is why the table carries 33 columns covering calendar identity, day-of-week hours, shift definitions, and status.

From a dimensional modeling perspective, the mined foreign-key structure classifies this object heuristically as standalone — that is, no clear hub, link, or satellite role is implied by the documented relationships. As a modeling suggestion only, this table behaves most like a satellite of schedule exception events, since it records descriptive, time-stamped attributes of exceptions rather than defining independent business entities.

Key Information Stored

The 33 documented columns capture the full context of each historical exception. The most important are:

• SCHEDULE_EXCEPTION_ID — the business identifier linking the history row to the originating exception definition in CAC_SR_SCHDL_EXCEPTIONS. This is the principal business-key candidate.
• ASSIGNMENT_ID — the assignment (typically a project or resource assignment) affected by the exception.
• PROJECT_ID — the project against which the exception was applied.
• CALENDAR_ID / CHANGE_CALENDAR_ID / CHANGE_CALENDAR_TYPE_CODE — identify the base calendar and any change calendar used.
• EXCEPTION_TYPE_CODE and SCHEDULE_TYPE_CODE — classify the nature of the exception and the schedule context, determining which columns are populated.
• START_DATE / END_DATE — the effective window for the exception.
• SUNDAY_HOURS through SATURDAY_HOURS — seven columns giving the per-day working hours for the exception period.
• NON_WORKING_DAY_FLAG — indicates whether the exception marks a non-working day.
• NUMBER_OF_SHIFT and DURATION_SHIFT_TYPE_CODE — define shift counts and duration semantics.
• STATUS_CODE — the lifecycle state of the exception record.
• PURGE_BATCH_ID — references PA_PURGE_BATCHES_ALL, tying the row to a purge run.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns.

Note that CREATION_DATE and LAST_UPDATE_DATE carry the temporal load for history, as no separate surrogate key is documented beyond the standard WHO columns.

Common Use Cases and Queries

Typical reporting scenarios include auditing how calendar exceptions changed over time, reconstructing a project's effective working calendar for a past period, and verifying which rows were archived by a given purge batch.

• Historical exception audit by project:

SELECT h.schedule_exception_id, h.project_id, h.exception_type_code,
       h.start_date, h.end_date, h.non_working_day_flag
  FROM pa_sch_excpt_hstry_ar h
 WHERE h.project_id = :project_id
 ORDER BY h.start_date;

• Reconstructing per-day hours across a date range using the SUNDAY_HOURS through SATURDAY_HOURS columns.
• Purge reconciliation:

SELECT purge_batch_id, COUNT(*)
  FROM pa_sch_excpt_hstry_ar
 GROUP BY purge_batch_id;

Because the table is append-oriented history, queries should always filter by date or project to avoid full scans on a potentially large archive.

Related Objects

Two foreign keys are documented, and several peripheral objects are significant:

• CAC_SR_SCHDL_EXCEPTIONS — joined on SCHEDULE_EXCEPTION_ID; the source exception definitions.
• PA_PURGE_BATCHES_ALL — joined on PURGE_BATCH_ID; controls purge execution.
• PA_PROJECTS_ALL — matched via PROJECT_ID to resolve project attributes.
• PA_PURGE_PROJECTS_ALL — corresponds to PURGE_PROJECT_ID for purge-scoped projects.
• PER_ASSIGNMENTS_F / per_all_assignments_f — supplies assignment context for ASSIGNMENT_ID.
• PA_SCHEDULE_EXCEPTIONS (operational table) — the current-state counterpart archived into this history.

Together these objects let developers trace an exception from definition, through application, into archival, and finally through purge verification.