Search Results msc_int_ascp_exception_details




Overview
Explore in AI Chat

Overview

The MSC.MSC_INT_ASCP_EXCEPTION_DETAILS table is a staging and integration interface object within the Oracle Advanced Supply Chain Planning (ASCP) module. It resides in the MSC schema, which is the primary repository for planning data, plan definitions, and integration interfaces used by the ASCP engine in Oracle E-Business Suite 12.1.1 and 12.2.2. The table is classified as VALID and serves as a landing area for exception detail records that describe plan violations, capacity alerts, and constraint-related exceptions generated or consumed during the planning cycle.

In practice this object supports the inbound interface between external or upstream planning processes and the ASCP exception reporting framework. Records are keyed to a specific plan instance and organization, and carry categorized exception codes that downstream exception dashboards and planner workbenches consume. A heuristic Data Vault classification derived from the foreign key structure suggests this object is best modeled as a standalone entity, with no native parent-child hubs or satellite dependencies captured through FK relationships beyond the single reference to WMS_ZONES_B. This suggests it should be treated as an independent reference or staging table rather than a foundational transactional hub.

Key Information Stored

The table contains 12 documented columns. The most significant for functional and technical use are:

  • PLAN_ID — Identifies the ASCP plan instance to which the exception detail belongs. This is the principal business-key candidate for scoping queries to a specific plan run.
  • ORGANIZATION_ID — The inventory organization context for the exception, enabling multi-org filtering.
  • INSTANCE_ID — Typically links to the parent interface or planning instance header row.
  • ZONE_ID — A foreign key to WMS_ZONES_B, tying the exception to a warehouse zone when the violation is zone-based (e.g., capacity or put-away constraints).
  • ALERTCATEGORY — Classifies the type of exception (e.g., capacity, material, resource).
  • VIOLATIONCODE — The specific code identifying the nature of the violation.
  • ITEMID — The inventory item associated with the exception.
  • DEPARTMENTID — The department or resource context, relevant for capacity-related violations.
  • QUANTITY — The magnitude of the exception (units, capacity, or demand).
  • PERCENTAGE — A normalized measure of the violation severity or utilization.
  • PERIODSTART — The beginning of the planning bucket during which the exception occurs.
  • PERIODEND — The end of the planning bucket for the exception window.

No explicit surrogate primary key is documented in the ETRM metadata; the combination of PLAN_ID, ORGANIZATION_ID, INSTANCE_ID, and VIOLATIONCODE typically behaves as the unique business key.

Common Use Cases and Queries

Planners and integration developers query this table to extract exceptions for a given plan and organization, to feed exception workbenches, and to drive alerting. A typical retrieval aggregates exception counts by category and period:

  • Filtering exceptions for a plan run: SELECT * FROM MSC.MSC_INT_ASCP_EXCEPTION_DETAILS WHERE PLAN_ID = :plan_id AND ORGANIZATION_ID = :org_id;
  • Summarizing violations by category: SELECT ALERTCATEGORY, VIOLATIONCODE, COUNT(*) FROM MSC.MSC_INT_ASCP_EXCEPTION_DETAILS GROUP BY ALERTCATEGORY, VIOLATIONCODE;
  • Zone-based exception analysis by joining to WMS_ZONES_B on ZONE_ID.
  • Time-bucketed reporting using PERIODSTART and PERIODEND to isolate exceptions per planning bucket.

Related Objects

  • WMS_ZONES_B — Referenced via the ZONE_ID foreign key for zone capacity and constraint exceptions.
  • MSC_PLANS — Supplies PLAN_ID context.
  • MSC_SYSTEM_ITEMS / MTL_SYSTEM_ITEMS_B — Resolves ITEMID to item attributes.
  • HR_ALL_ORGANIZATION_UNITS / MTL_PARAMETERS — Resolves ORGANIZATION_ID and DEPARTMENTID.
  • MSC_EXCEPTIONS — The runtime exception table that consumes or parallels staged records.
  • MSC_INT_ASCP_* interface family tables — Sibling staging tables in the same integration layer.