Search Results msc_exception_details_v




Overview
Explore in AI Chat

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

Overview

MSC_EXCEPTION_DETAILS_V is an APPS-owned database view within the MSC – Advanced Supply Chain Planning product family, valid in Oracle E-Business Suite 12.1.1 and 12.2.2. According to its documented purpose, the view "picks up all the valid exception details," presenting a consolidated, presentation-ready read of planning exceptions generated by Advanced Supply Chain Planning (ASCP) and related planning engines. Exception details record the conditions a planner must act on — late supplies, excess inventory, shortage conditions, resource overloads, and similar constraint violations — and this view resolves those raw records into human-readable form by joining descriptive attributes such as organization codes, item names, item descriptions, category names, buyer and planner identifiers, and lookup meanings.

Because it is a view rather than a table, MSC_EXCEPTION_DETAILS_V carries no independent storage and inherits data integrity from its underlying base objects. Its role in reporting and integration is to provide a single, denormalized access point for exception-driven reporting, custom concurrent programs, Oracle Business Intelligence Publisher (BI Publisher) data models, and downstream integrations that need exception information without reimplementing the complex decoding logic embedded in the view definition.

Underlying Base Objects

The documented base objects referenced by MSC_EXCEPTION_DETAILS_V are:

  • MSC_EXCEPTION_DETAILS (synonym) — the primary source of exception records, supplying exception detail IDs, plan IDs, exception types, organization IDs, inventory item IDs, and the generic NUMBER and CHAR attribute columns used for type-specific values.
  • MSC_FULL_PEGGING (synonym) — joined to resolve pegging relationships, particularly project and task references used by certain exception types.
  • MSC_DEMANDS and MSC_SUPPLIES (synonyms) — demand and supply records against which exceptions are evaluated.
  • MSC_PLANS (synonym) — plan definitions providing plan context.
  • MSC_SYSTEM_ITEMS (synonym) — item-level attributes.
  • MSC_ITEM_CATEGORIES (synonym) — category set, category, and category name information.
  • MSC_GET_NAME (package) — a utility package invoked directly inside the view's SQL, exposing functions such as FROM_ORG, ORG_CODE, ITEM_NAME, ITEM_DESC, and LOOKUP_MEANING to translate internal identifiers into display values.

The view's structure reflects a decode-heavy design: many columns are conditionally derived based on MED.EXCEPTION_TYPE, so the same physical column returns different logical values depending on the exception being reported.

Key Columns

  • EXCEPTION_DETAIL_ID — Primary identifier of the exception detail record.
  • PLAN_ID — The plan in which the exception was generated.
  • ORGANIZATION_ID — The organization affected; suppressed (returned as NULL) for exception type 28.
  • EXCEPTION_TYPE — The numeric exception code; the view maps this to a meaning via MSC_GET_NAME.LOOKUP_MEANING('MRP_EXCEPTION_CODE_TYPE', ...).
  • INVENTORY_ITEM_ID — The item concerned, with a decode returning the pegged item (from MSC_FULL_PEGGING) when the value is -1.
  • Item name and description columns — Resolved either from the pegged item or from the MSC_SYSTEM_ITEMS-based item record, using MSC_GET_NAME.ITEM_NAME and ITEM_DESC.
  • BUYER_NAME and PLANNER_CODE — Planning responsibility attributes sourced from the item record.
  • CATEGORY_SET_ID, SR_CATEGORY_ID, CATEGORY_NAME — Category classification of the item.
  • Organization description column — Derived via MSC_GET_NAME.FROM_ORG for exception types 38, 39, 50, and 51, and via ORG_CODE otherwise.
  • Project and task columns — Populated conditionally for exception types 18, 17, 19, 24, 26, and 68, drawing from the exception record or the pegging rows (MR.PROJECT_ID, MR.TASK_ID, MGR.PROJECT_ID, MGR.TASK_ID).
  • Planning group column — Returned for exception types 17, 18, and 19 from the pegging record, defaulting to '0'.
  • Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include building planner exception worklists, feeding supplier or item-level exception dashboards, and extracting pegged project/task context for exception types that require it. The FIRST_ROWS hint in the view text indicates it is optimized for interactive, incremental retrieval.

A basic query listing exceptions for a plan:

  • SELECT exception_detail_id, plan_id, organization_id, exception_type, inventory_item_id FROM msc_exception_details_v WHERE plan_id = :plan_id;

A more complete planner-facing query, filtering to exceptions with item context:

  • SELECT exception_detail_id, plan_id, organization_id, inventory_item_id, exception_type FROM msc_exception_details_v WHERE plan_id = :plan_id AND organization_id = :org_id AND exception_type IN (17, 18, 19);

Because organization_id is NULL for exception type 28, filters on organization must account for that type explicitly. Plan IDs and exception type codes are the most selective predicates available, and constraining both is recommended for performance.