Results for “whole_day_flag”

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

Overview

CAC_SR_EXCEPTIONS_B is a foundational table in the JTF (CRM Foundation) schema that stores exception definitions used by Oracle's Service Request scheduling and resource management framework. Within Oracle E-Business Suite 12.1.1 and 12.2.2, this table provides the base-entity record for exceptions that govern how service schedules, period categories, and calendar events are applied across CRM service operations. Each row represents a distinct exception header that can be referenced by translation, detail, and schedule-assignment tables elsewhere in the CAC schema family.

Structurally, CAC_SR_EXCEPTIONS_B is the "_B" (base) component of a translated entity pattern common throughout EBS, meaning the non-translatable business attributes reside here while language-dependent text is stored separately in CAC_SR_EXCEPTIONS_TL. The table is identified by the primary key constraint CAC_SR_EXCEPTIONS_B_PK on EXCEPTION_ID, and a unique index CAC_SR_EXCEPTIONS_B_U1 also on EXCEPTION_ID. Based on the foreign key topology — where this table is referenced by three child tables and itself references two parent tables — the heuristic Data Vault classification is hub-leaning. This suggests modeling EXCEPTION_ID as a business hub key, with dependent attributes and relationships treated as satellites or links in an analytical warehouse context.

Key Information Stored

The table contains 16 documented columns. The most significant include:

• EXCEPTION_ID — Surrogate primary key and the unique business-key candidate; the join anchor for all child relationships.
• EXCEPTION_GROUP and EXCEPTION_TYPE — Classify the exception into functional groupings and behavior categories used by scheduling logic.
• PERIOD_CATEGORY_ID — Foreign key to CAC_SR_PERIOD_CATS_B, defining the period category to which the exception applies.
• TEMPLATE_ID — Foreign key to CAC_SR_TEMPLATES_B, tying the exception to a scheduling template.
• START_DATE_TIME and END_DATE_TIME — Define the effective time window during which the exception is active.
• WHOLE_DAY_FLAG — Indicates whether the exception spans a full day rather than a partial time range.
• HR_CAL_EVENT_TYPE and HR_CAL_EVENT_ID — Link the exception to Oracle HRMS calendar events, enabling integration with human-resources calendars.
• OBJECT_VERSION_NUMBER — Supports optimistic locking for concurrent updates, a standard EBS 12.2.2 pattern.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture standard WHO-column auditing.

Common Use Cases and Queries

Typical uses include resolving which exceptions apply to a given period category or template, reconciling exceptions against HR calendar events, and reporting on exception coverage windows.

• Listing all exceptions for a period category:

  SELECT e.excp.exception_id, e.exception_type
  FROM jtf.cac_sr_exceptions_b e
  WHERE e.period_category_id = :p_period_category_id;

• Joining to translations for a readable name:

  SELECT b.exception_id, t.name
  FROM jtf.cac_sr_exceptions_b b,
       jtf.cac_sr_exceptions_tl t
  WHERE b.exception_id = t.exception_id
    AND t.language = USERENV('LANG');

• Identifying exceptions tied to HR calendar events for a date range:

  SELECT exception_id, hr_cal_event_id
  FROM jtf.cac_sr_exceptions_b
  WHERE hr_cal_event_type IS NOT NULL
    AND start_date_time >= :p_from
    AND end_date_time <= :p_to;

Related Objects

The following objects participate in documented foreign key relationships with CAC_SR_EXCEPTIONS_B:

• CAC_SR_PERIOD_CATS_B — Parent; joined via PERIOD_CATEGORY_ID.
• CAC_SR_TEMPLATES_B — Parent; joined via TEMPLATE_ID.
• CAC_SR_EXCEPTIONS_TL — Child; joined via EXCEPTION_ID for translated descriptive text.
• CAC_SR_EXCP_DETAILS — Child; joined via EXCEPTION_ID for exception detail lines.
• CAC_SR_SCHDL_EXCEPTIONS — Child; joined via EXCEPTION_ID, associating exceptions with schedules.

Together these objects form the exception-handling sub-model of the JTF scheduling framework.