Search Results msc_reservations




Overview
Explore in AI Chat

Overview

The MSC_RESERVATIONS table is a core data object within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) module. It serves as the central repository for material reservation records generated by the planning engine. Reservations represent a formal allocation of specific on-hand or in-transit inventory to a particular demand, such as a sales order or a work order. This table is critical for managing supply chain visibility and ensuring that planned allocations are tracked and respected throughout the execution process. Its data is populated and managed by the ASCP engine during plan runs and is essential for generating feasible plans that account for material availability constraints.

Key Information Stored

The table stores the detailed attributes of each reservation. The primary key uniquely identifies a reservation using a combination of PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, and TRANSACTION_ID. Key columns include TRANSACTION_ID, which is the unique identifier for the reservation record itself, and DEMAND_SOURCE_HEADER_ID and DEMAND_SOURCE_LINE_ID, which link the reservation to the specific sales order or work order demand. The SUPPLY_SOURCE_HEADER_ID and SUPPLY_SOURCE_LINE_ID columns identify the supply transaction, such as a purchase order or a planned order, fulfilling the demand. Critical material information is stored in INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUBINVENTORY. The table also holds quantities (e.g., ORDERED_QUANTITY, ALLOCATED_QUANTITY), dates, and flags indicating the reservation type and status.

Common Use Cases and Queries

A primary use case is analyzing plan output to review material allocations. Planners often query this table to understand which supplies are reserved for specific high-priority demands or to identify demands that remain unfulfilled. Common reporting includes reservation summaries by item, organization, or demand source. A typical query pattern joins MSC_RESERVATIONS to MSC_SYSTEM_ITEMS for item details and to demand/supply source tables for contextual information.

  • Sample Query Pattern: SELECT mr.inventory_item_id, msi.item_name, mr.demand_source_header_id, mr.allocated_quantity FROM msc_reservations mr, msc_system_items msi WHERE mr.plan_id = :p_plan_id AND mr.inventory_item_id = msi.inventory_item_id AND mr.organization_id = msi.organization_id AND mr.sr_instance_id = msi.sr_instance_id AND msi.plan_id = msi.sr_instance_id;
  • Use Case: Troubleshooting planning exceptions related to material shortages by checking if reservations exist for critical items.

Related Objects

The MSC_RESERVATIONS table maintains defined foreign key relationships with other core MSC planning tables, ensuring referential integrity for item and subinventory data.

  • MSC_SYSTEM_ITEMS: Joined via INVENTORY_ITEM_ID, PLAN_ID, SR_INSTANCE_ID, and ORGANIZATION_ID to retrieve item master attributes like description and planning make/buy code.
  • MSC_SUB_INVENTORIES: Joined via SUBINVENTORY, PLAN_ID, SR_INSTANCE_ID, and ORGANIZATION_ID to validate and reference subinventory details.

While not listed in the provided metadata, this table is also intrinsically related to demand source tables (e.g., MSC_SALES_ORDERS, MSC_DEMANDS) and supply source tables (e.g., MSC_SUPPLIES) through the DEMAND_SOURCE_* and SUPPLY_SOURCE_* columns, which are typically joined on LINE_ID and HEADER_ID fields within the same PLAN_ID and SR_INSTANCE_ID context.