Search Results mrp_exception_details_v




Overview
Explore in AI Chat

Overview

MRP_EXCEPTION_DETAILS_V is a view owned by the APPS schema within the Oracle E-Business Suite Master Scheduling/MRP product family. It is documented as VALID in ETRM for both 12.1.1 and 12.2.2. The view provides what the documentation describes as an "enhanced exception message" — a presentation layer over the base planning exception data that resolves internal identifiers into business-meaningful values.

The base table MRP_EXCEPTION_DETAILS stores planning exceptions generated by the MRP, MPS, and DRP planning runs, keyed by numeric exception type codes and generic attribute columns (NUMBER1, NUMBER2, CHAR1, DATE1). These codes and generic slots are not directly interpretable. MRP_EXCEPTION_DETAILS_V performs the decoding: it maps exception type codes to lookup meanings, converts inventory item identifiers into item numbers, resolves organization identifiers into organization codes, and derives project and task context. The view therefore functions as the canonical reporting and integration interface for exception messages, shielding consumers from the internal encoding of the exception table.

Underlying Base Objects

The view is defined over MRP_EXCEPTION_DETAILS (SYNONYM) as its driving table, aliased MED in the view text. It is enriched by joins to several planning and manufacturing objects, all exposed as APPS synonyms for the corresponding base tables:

In addition to these synonyms, the view invokes three PL/SQL components at runtime: MRP_EXCEPTION_SC (specifically the ITEM_NUMBER function) to translate an inventory item identifier into an item number, MRP_GET_PROJECT (LOOKUP_MEANING, PROJECT, and TASK functions) to resolve exception codes into lookup meanings and to convert project and task identifiers into display names, and MRP_GET_ORDER for order-level context. Because these are function calls embedded in the SELECT list, the view is not a simple join; row-level function execution occurs per returned row, which has performance implications.

Key Columns

The view exposes the primary key and audit columns of the underlying exception record — EXCEPTION_ID, COMPILE_DESIGNATOR, ORGANIZATION_ID, INVENTORY_ITEM_ID, EXCEPTION_TYPE, and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN). Beyond these, the decoded columns include:

  • A derived organization code, returned as NULL when EXCEPTION_TYPE is 28, otherwise sourced from MTL_PARAMETERS.ORGANIZATION_CODE.
  • A derived item number produced by MRP_EXCEPTION_SC.ITEM_NUMBER, returning NULL when INVENTORY_ITEM_ID is NULL or -1.
  • An exception type meaning resolved through MRP_GET_PROJECT.LOOKUP_MEANING against the MRP_EXCEPTION_CODE_TYPE lookup.
  • Derived numeric attributes (NUMBER1/NUMBER2) mapped to project and task identifiers for exception types 17, 18, and 19, with additional DECODE branches supplying project and task display values via MRP_GET_PROJECT.PROJECT and MRP_GET_PROJECT.TASK. For exception type 19, the resolution depends on whether DATE1 is null, selecting either MRP_RECOMMENDATIONS or MRP_ONHAND_QUANTITIES context.
  • A derived planning group value (CHAR1) for the same exception types 17, 18, and 19.

Common Use Cases and Queries

Typical usage includes exception workbenches, custom planner dashboards, and data extracts feeding external planning or BI systems. A basic query lists open exceptions for an organization with their decoded meanings:

  • SELECT exception_id, organization_code, item_number, exception_type FROM mrp_exception_details_v WHERE organization_id = :org_id;
  • SELECT exception_id, item_number, exception_meaning FROM mrp_exception_details_v WHERE exception_type IN (17, 18, 19);

Because project and task resolution is exception-type dependent, consumers should filter on EXCEPTION_TYPE when interpreting the derived project and task columns. Given the embedded PL/SQL calls, queries should filter on ORGANIZATION_ID and COMPILE_DESIGNATOR wherever possible to limit the number of rows passed through the function-based decoding, and large unfiltered extracts should be scheduled outside peak planning windows.