Search Results msc_st_reservations




Overview
Explore in AI Chat

Overview

MSC_ST_RESERVATIONS is a staging (interface) table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) product family. Its documented role is to hold raw reservation data extracted from one or more source systems so that the ASCP collection program can validate, transform, and load that data into the production planning table MSC_RESERVATIONS. During a planning data collection run, records are first written to the staging table, checked for correctness and referential integrity, flagged with processing status, and only then promoted to the main table where the planning engine consumes them. Because it is transient by design, the table typically contains rows only for the duration of an active collection cycle; contents are overwritten or purged between cycles based on the refresh identifier.

The Data Vault classification supplied in the metadata is standalone — a heuristic suggestion rather than an enforced model. Under a Data Vault design, this object would most plausibly be modeled as a satellite attached to a reservation hub or link, since it carries descriptive reservation attributes and processing metadata rather than defining new business keys. The single documented foreign key, MSC_ST_RESERVATIONS.DISPOSITION_ID referencing AHL_PRD_DISPOSITIONS_B, reflects the ASCP dependency on disposition (quality/returns) reference data.

Key Information Stored

The documented physical schema for 12.2.2 contains 55 columns. The most operationally significant are:

No surrogate primary key is documented in the supplied metadata; the practical unique identifier is the combination of SR_INSTANCE_ID, REFRESH_ID and TRANSACTION_ID (or ST_TRANSACTION_ID where rows are sourced without a native transaction key). SELECTED identifiers such as SALES_ORDER_NUMBER, LINE_NUM, SUPPLY_SOURCE_HEADER_ID and SUPPLY_SOURCE_TYPE_ID function as business-key candidates for tracing back to the originating order.

Common Use Cases and Queries

Typical uses are: diagnosing collection failures, reconciling staged versus loaded reservation counts, and building custom extracts when standard ASCP collections do not cover a user requirement.

-- Rows that failed validation in the latest refresh
SELECT TRANSACTION_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
       RESERVED_QUANTITY, PROCESS_FLAG, ERROR_TEXT
  FROM MSC.MSC_ST_RESERVATIONS
 WHERE REFRESH_ID = :p_refresh_id
   AND PROCESS_FLAG <> 1;

-- Staged vs. loaded comparison by item/organization
SELECT ORGANIZATION_CODE, ITEM_NAME, COUNT(*) staged_qty
  FROM MSC.MSC_ST_RESERVATIONS
 WHERE SR_INSTANCE_ID = :p_instance
 GROUP BY ORGANIZATION_CODE, ITEM_NAME;

Other common scenarios include verifying that DISPOSITION_ID values resolve to AHL_PRD_DISPOSITIONS_B before load, checking reservations suppressed by AVAILABLE_TO_MRP, and auditing which concurrent request populated a given batch via BATCH_ID or REQUEST_ID.

Related Objects

  • MSC_RESERVATIONS — the destination table; the staging table exists solely to feed it after validation.
  • AHL_PRD_DISPOSITIONS_B — joined on DISPOSITION_ID; the only documented foreign key target.
  • MSC_ST_INVENTORY_ITEMS / MSC_ST_ORGANIZATIONS — referenced implicitly via INVENTORY_ITEM_ID and ORGANIZATION_ID to resolve item and organization names.
  • MSC_ST_SUPPLY / MSC_ST_DEMANDS — companion staging tables collected under the same REFRESH_ID and SR_INSTANCE_ID.
  • MSC_SYSTEM_ITEMS_B — for item description and planning attribute lookups.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to identify the collection program run.