Results for “delta_code”

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

Overview

CRP_CAL_SHIFT_DELTA is a Capacity (CRP) module table in the Oracle E-Business Suite, owned by the CRP schema and documented as VALID in ETRM. Its stated purpose is to store the deltas between the primary calendar and shift calendars. When Oracle's capacity planning engine expands a calendar into individual work shifts, a base workday pattern is applied and then adjusted by exception. This table captures those adjustments at the granularity of a single calendar date within a single shift, allowing the planning engine to derive the working and non-working intervals that actually apply to a resource or department without rewriting the underlying BOM calendar definitions.

Heuristically mined from its foreign-key structure, the object is classified as a link table. This is a modeling suggestion rather than a physical designation: the table's identity is defined by the combination of a calendar/exception context and the shift/date intersection it modifies, which is the classic shape of a relationship (link) between calendar and shift hierarchies rather than an independent hub or a purely descriptive satellite.

Key Information Stored

The table contains 14 documented columns. The four columns that make up the primary key are the most significant for interpretation:

• CALENDAR_CODE — indicates the workday calendar context to which the delta applies.
• EXCEPTION_SET_ID — identifies the exception set that qualified the base calendar pattern.
• SHIFT_NUM — the shift number within the calendar day, distinguishing first, second, and subsequent shifts.
• CALENDAR_DATE — the specific date on which the delta is effective.
• DELTA_CODE — the actual delta indicator describing how the shift/date combination deviates from the primary calendar. This is the principal business-content column beyond the key.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns recording audit and user context.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context identifying the request and program that created or last refreshed the row.

The surrogate/business key is enforced by CRP_CAL_SHIFT_DELTA_PK over (CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, CALENDAR_DATE). A unique index, CRP_CAL_SHIFT_DELTA_U1, covers the same four columns, making them the documented business-key candidates; uniqueness therefore guarantees one delta row per shift per date per exception-set calendar context.

Common Use Cases and Queries

Typical usage centers on capacity planning diagnostics and calendar reconciliation. A planner may want to list all shift deltas for a calendar over a date range:

• SELECT CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, CALENDAR_DATE, DELTA_CODE FROM CRP.CRP_CAL_SHIFT_DELTA WHERE CALENDAR_CODE = :calendar_code AND CALENDAR_DATE BETWEEN :from_date AND :to_date ORDER BY CALENDAR_DATE, SHIFT_NUM;
• Joining to BOM_CALENDAR_DATES and BOM_SHIFT_DATES on the shared columns (CALENDAR_CODE, EXCEPTION_SET_ID, CALENDAR_DATE, and SHIFT_NUM where applicable) reconciles base calendar definitions against the applied deltas.
• Audit queries filtering on LAST_UPDATE_DATE or PROGRAM_ID identify which concurrent request last regenerated shift deltas, useful when capacity results appear stale.
• Reporting extracts group by DELTA_CODE to quantify how much of a calendar's capacity is being added or removed by exceptions.

Related Objects

The documented foreign keys tie this table directly to two BOM calendar objects:

• BOM_CALENDAR_DATES — referenced through CALENDAR_CODE, EXCEPTION_SET_ID, and CALENDAR_DATE; supplies the primary calendar date records being adjusted.
• BOM_SHIFT_DATES — referenced through CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, and CALENDAR_DATE; supplies the shift-level date definitions that deltas modify.
• BOM_CALENDARS / BOM_EXCEPTION_SETS — parent definitions implied by CALENDAR_CODE and EXCEPTION_SET_ID, providing the header context for the referenced dates.
• CRP capacity planning programs and concurrent requests — referenced through REQUEST_ID and PROGRAM_ID, linking each row to the process that populated it.

Because the table is a link over calendar, exception set, shift, and date, any CRP or BOM calendar process that reads or regenerates shift timings should be treated as a dependent consumer of this data.