Search Results msc_supplier_capacities




Overview
Explore in AI Chat

Overview

MSC_SUPPLIER_CAPACITIES is a planning table in the MSC schema, owned by the Advanced Supply Chain Planning (ASCP) module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores supplier capacity information for planned items within an MPS, MRP, or DRP plan. Each row defines the capacity available from a specific supplier, for a specific item, across a defined date range, expressed through the FROM_DATE and TO_DATE columns. The table is populated during the planning data collection and plan execution cycle, allowing planners to model supplier-side constraints and to compare sourced demand against the capacity a supplier can actually commit to.

From a dimensional modeling perspective, the heuristic Data Vault classification for this object is satellite-leaning. This reflects the presence of descriptive, plan-scoped attributes (capacity values, date ranges, supplier and item keys) attached to a larger planning entity context rather than to a purely independent business hub.

Key Information Stored

The primary key is MSC_SUPPLIER_CAPACITIES_PK, defined on PLAN_ID and TRANSACTION_ID. These two columns act as the surrogate key that uniquely identifies each capacity record within a plan. Two business-key candidates are documented as unique indexes: MSC_SUPPLIER_CAPACITIES_U1 (PLAN_ID, TRANSACTION_ID, NEW_PLAN_ID, SIMULATION_SET_ID) and MSC_SUPPLIER_CAPACITIES_U2 (PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, FROM_DATE, SUPPLIER_ID, SUPPLIER_SITE_ID, NEW_PLAN_ID, SIMULATION_SET_ID). Together these define the natural grain of a supplier capacity record across source instance, organization, item, supplier, supplier site, and date range.

The most significant columns include:

  • PLAN_ID and TRANSACTION_ID — the composite primary key identifying the plan and the individual capacity row.
  • INVENTORY_ITEM_ID — the planned item whose capacity is being modeled.
  • SUPPLIER_ID and SUPPLIER_SITE_ID — the supplier and supplier site providing the capacity.
  • ORGANIZATION_ID and USING_ORGANIZATION_ID — the inventory and consuming organizations in scope.
  • SR_INSTANCE_ID — identifies the source instance from which the record was collected.
  • FROM_DATE and TO_DATE — the effective date range over which the capacity value applies.
  • CAPACITY — the quantity of capacity available from the supplier within the date range.
  • PARENT_ID — a self-referencing link used to relate nested capacity records.
  • NEW_PLAN_ID, NEW_PLAN_LIST, and SIMULATION_SET_ID — used to scope records to copies or simulations of a plan.
  • REFRESH_NUMBER, STATUS, APPLIED, and COLLECTED_FLAG — control and lifecycle attributes indicating collection and application state.

Standard EBS audit columns (LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_ID, and the ATTRIBUTE1–15 flexfields) are also present.

Common Use Cases and Queries

Planners and reporting developers use this table to analyze supplier capacity against plan requirements, to identify constrained suppliers, and to feed sourcing and capacity reports. A typical query joins supplier and item master data to the capacity rows for a given plan:

  • Retrieve total supplier capacity for a plan: SELECT INVENTORY_ITEM_ID, SUPPLIER_ID, FROM_DATE, TO_DATE, CAPACITY FROM MSC_SUPPLIER_CAPACITIES WHERE PLAN_ID = :plan_id.
  • Identify capacity records that have not yet been applied: filter on APPLIED = 'N' OR STATUS IS NULL.
  • Detect date-range overlaps for an item/supplier combination by comparing FROM_DATE and TO_DATE across sibling rows.
  • Reconcile collected capacity against the source instance using SR_INSTANCE_ID and COLLECTED_FLAG.

Because the grain is a plan-scoped supplier capacity window, aggregate queries should group by PLAN_ID, SUPPLIER_ID, SUPPLIER_SITE_ID, and INVENTORY_ITEM_ID to avoid double counting across overlapping ranges.

Related Objects

The FK structure in the metadata shows self-referencing relationships: MSC_SUPPLIER_CAPACITIES.PLAN_ID references MSC_SUPPLIER_CAPACITIES, and MSC_SUPPLIER_CAPACITIES.PARENT_ID references MSC_SUPPLIER_CAPACITIES. This self-referential design ties child capacity records to a parent row within the same table. Beyond this, the table logically integrates with other ASCP planning entities, including the plan header (MSC_PLANS, keyed by PLAN_ID), organization and item master tables referenced through ORGANIZATION_ID and INVENTORY_ITEM_ID, and supplier/supplier site master data through SUPPLIER_ID and SUPPLIER_SITE_ID. Reporting typically joins these supporting tables to resolve descriptive names for supplier, item, and organization dimensions. Substitution and sourcing rules tables in ASCP may also reference supplier capacity when resolving sourcing decisions during plan execution.