Search Results msc_undo_details




Overview
Explore in AI Chat

Overview

MSC_UNDO_DETAILS is a table owned by the MSC schema within Oracle Advanced Supply Chain Planning (ASCP), a module of Oracle E-Business Suite available in releases 12.1.1 and 12.2.2. As stated in the ETRM documentation, the table "describes the details of changes made to plan output." It functions as the change-log layer of the ASCP undo framework: when a planner edits a plan — for example, rescheduling a supply, firming a planned order, or adjusting a quantity — the plan engine records a summary header in MSC_UNDO_SUMMARY and stores the column-level delta for each affected field in MSC_UNDO_DETAILS. This enables targeted rollback of manual planning changes without regenerating the entire plan.

From a Data Vault modeling perspective, the mined FK structure classifies this object as satellite-leaning. Its foreign key to MSC_UNDO_SUMMARY positions it as a dependent, descriptive child of a parent transaction record rather than an independent hub or a many-to-many link. The UNDO_ID column carries that parent reference, making this table the detail satellite of the undo header.

Key Information Stored

The documented schema for 12.2.2 contains 14 columns. The most significant are:

The unique index MSC_UNDO_DETAILS_U1 on (UNDO_ID, COLUMN_CHANGED) is the primary business-key candidate, enforcing one delta row per changed column per undo transaction. The surrogate key is not separately documented beyond this composite.

Common Use Cases and Queries

The principal use case is auditing and reversing manual plan edits. Planners and supply chain analysts query this table to determine exactly which fields were changed on a plan and to reconstruct prior values. A typical join retrieves the header context for each change:

  • Change audit for a plan: SELECT d.UNDO_ID, d.COLUMN_CHANGED_TEXT, d.OLD_VALUE, d.NEW_VALUE, d.CREATED_BY, d.CREATION_DATE FROM MSC_UNDO_DETAILS d JOIN MSC_UNDO_SUMMARY s ON s.UNDO_ID = d.UNDO_ID WHERE d.PLAN_ID = :plan_id ORDER BY d.CREATION_DATE DESC.
  • Reverse-lookup of a specific field: filter on COLUMN_CHANGED to trace every modification to a given attribute across undo transactions.
  • User activity reporting: group by CREATED_BY and truncate CREATION_DATE to profile which planners are modifying plan output and how frequently.
  • Rollback verification: before or after an undo operation, compare OLD_VALUE_ID and NEW_VALUE_ID to confirm the intended restoration.

Because the table can grow quickly in high-volume planning environments, queries should always constrain on UNDO_ID or PLAN_ID rather than scanning the full history.

Related Objects

The FK metadata identifies MSC_UNDO_SUMMARY as the direct parent, joined on MSC_UNDO_DETAILS.UNDO_ID = MSC_UNDO_SUMMARY.UNDO_ID; this is the mandatory companion table, supplying the transaction-level context (plan, user, and timestamp) for each detail row. Because MSC_UNDO_DETAILS stores only change deltas rather than full plan records, reporting almost always requires joining back to MSC_UNDO_SUMMARY. In practice, the plan entities whose columns are captured in COLUMN_CHANGED — such as MSC_PLAN_ORDERS and related ASCP plan output tables — provide the operational context, while PLAN_ID links the change history to a specific plan definition. Together, MSC_UNDO_SUMMARY and MSC_UNDO_DETAILS form the complete ASCP undo audit model, with the summary serving as the header and this table as its detail satellite.