Search Results crp_cal_shift_delta

Overview

The CRP_CAL_SHIFT_DELTA table is a core data structure within the Oracle E-Business Suite (EBS) Capacity (CRP) module. Its primary function is to store and manage the calculated differences, or "deltas," between a primary workday calendar and the specific shift calendars derived from it. In manufacturing and capacity planning, a primary calendar defines standard working days, while shift calendars define the precise working hours for specific shifts on those days. This table acts as a junction, enabling the system to efficiently track and apply the unique scheduling exceptions and adjustments defined at the shift level against the baseline of the primary calendar. Its existence is critical for accurate capacity planning, detailed production scheduling, and resource utilization analysis, as it provides the granular shift-level calendar data required for these processes.

Key Information Stored

The table's structure is designed to uniquely identify a specific shift on a specific date within a given calendar context. Its primary key consists of four columns: CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, and CALENDAR_DATE. This composite key ensures each record represents a unique shift-day instance. The CALENDAR_CODE and EXCEPTION_SET_ID link to the master calendar definition (BOM_CALENDAR_DATES). The SHIFT_NUM identifies the specific shift within that calendar, and the CALENDAR_DATE is the applicable date. While the exact "delta" data columns are not explicitly detailed in the provided metadata, this table typically stores calculated values such as net shift start and end times, capacity seconds, or flags indicating how the shift deviates from the standard day defined in the primary calendar. The foreign key relationships confirm it validates its calendar and shift references against the foundational BOM_CALENDAR_DATES and BOM_SHIFT_DATES tables.

Common Use Cases and Queries

This table is primarily accessed for detailed capacity and scheduling inquiries. A common use case is generating a report of all shift-level exceptions for a given production line or department over a date range to analyze overtime or downtime patterns. Another critical use is during the scheduling engine's runtime, where it queries this table to retrieve precise available capacity per shift for finite scheduling. A typical analytical query might join this table with resource definitions to assess planned utilization.

Sample Query Pattern:
SELECT ccsd.calendar_code, ccsd.shift_num, ccsd.calendar_date
FROM crp.crp_cal_shift_delta ccsd
WHERE ccsd.calendar_code = 'PROD_CAL'
AND ccsd.calendar_date BETWEEN TO_DATE('01-JAN-2024', 'DD-MON-YYYY') AND SYSDATE
ORDER BY ccsd.calendar_date, ccsd.shift_num;

Related Objects

The CRP_CAL_SHIFT_DELTA table is centrally linked to the foundational manufacturing calendar tables, as defined by its foreign keys. Its two primary parent tables are BOM_CALENDAR_DATES and BOM_SHIFT_DATES. The relationship with BOM_CALENDAR_DATES ensures the calendar date is valid within the defined exception set. The relationship with BOM_SHIFT_DATES ensures the shift number is valid for that calendar. This table is a key source for views and programs within the CRP module that present or process shift-level capacity information. While not listed in the metadata, it is logically related to other CRP tables like CRP_PLANNING_LINES and CRP_ORDER_RESOURCES, which consume shift-level capacity data for planning calculations.