Search Results msc_safety_stocks




Overview
Explore in AI Chat

Overview

MSC_SAFETY_STOCKS is a table within the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores safety stock levels used by Oracle Advanced Supply Chain Planning (ASCP) during the planning process. Safety stock represents a buffer of inventory held to protect against demand variability and supply uncertainty. ASCP uses these records to compute planned orders and to satisfy the safety stock requirement across each planning period.

The table is period-based: for each combination of plan, organization, item, and period start date, it records the safety stock quantity that the planning engine must respect. This makes it a critical input to the MRP/DRP calculations performed by the ASCP engine and to any plan simulation or comparison.

From a modeling perspective, the heuristic Data Vault classification is satellite-leaning. This suggests treating MSC_SAFETY_STOCKS as a satellite table attached to a hub of item/organization/plan combination, capturing changing safety stock attributes over time (keyed by PERIOD_START_DATE). It is not a pure transaction link, since it primarily stores descriptive measures (quantities, percentages, days of supply) rather than relationships between business entities.

Key Information Stored

The table contains 38 documented columns. The most important include:

The documented primary key is MSC_SAFETY_STOCKS_PK on (SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, PLAN_ID, PERIOD_START_DATE). A second unique index, MSC_SAFETY_STOCKS_U1, extends the business key to include PROJECT_ID, TASK_ID, PLANNING_GROUP, UNIT_NUMBER, and NEW_PLAN_ID, indicating these columns together form the true business key for planning purposes. The surrogate PK alone does not capture project/task/planning group granularity.

Common Use Cases and Queries

Typical scenarios include reconciling safety stock between the source system and a plan, analyzing safety stock variability components, and validating that a plan honored the configured safety stock.

Example: retrieve safety stock for a given plan, organization, and item across periods:

SELECT period_start_date, safety_stock_quantity, target_safety_stock
FROM   msc.msc_safety_stocks
WHERE  plan_id = :plan_id
AND    organization_id = :org_id
AND    inventory_item_id = :item_id
ORDER BY period_start_date;

Example: sum safety stock by item to compare against planning group targets:

SELECT inventory_item_id, SUM(safety_stock_quantity) total_ss
FROM   msc.msc_safety_stocks
WHERE  plan_id = :plan_id
GROUP BY inventory_item_id;

Reporting use cases include a safety stock trend report across periods, a variability breakdown report using the demand/manufacturing/transit/supplier percent columns, and exception reports where the achieved days of supply falls short of target. Plan comparison queries can join on NEW_PLAN_ID or SIMULATION_SET_ID to compare simulated versus baseline safety stock.

Related Objects

  • MSC_SYSTEM_ITEMS — joined on INVENTORY_ITEM_ID; provides item master context for the safety stock record.
  • CZ_ITEM_TYPES — joined on ITEM_TYPE_ID; supplies item type classification.
  • MSC_PLANS — provides plan definition referenced by PLAN_ID.
  • MSC_SAFETY_STOCKS_PK / MSC_SAFETY_STOCKS_U1 — the primary and unique indexes enforcing uniqueness per period and planning granularity.
  • MSC_ITEM_SAFETY_STOCKS (where present) — related safety stock configuration.
  • ASCP planning engine — consumes MSC_SAFETY_STOCKS as a direct input to planned order generation.
  • MSC_SAFETY_STOCK_HISTORY (where available) — historical archive of safety stock levels.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to trace the plan run that created or refreshed the record.

The foreign key relationships to MSC_SYSTEM_ITEMS and CZ_ITEM_TYPES anchor the table to the item dimension, while the composite unique index enforces the period and planning hierarchy that ASCP relies upon.