Search Results msc_supplier_capacities_u1

Overview

MSC.MSC_SUPPLIER_CAPACITIES is a transactional planning table within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) schema, owned by the MSC application. As documented in ETRM, the table "stores the capacity information for planned items within an MPS, MRP, or DRP plan," with each row capturing the capacity supplied by a specific supplier for a given item across a defined date range. It is populated during the planning data refresh and consumed by the planning engine when supplier capacity constraints are enabled for sourcing rules. In Oracle EBS 12.1.1 and 12.2.2, this table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10.

From a Data Vault modeling perspective, the mined heuristic classification for this object is satellite-leaning — meaning it primarily describes a business entity (supplier capacity) over time rather than functioning as a pure hub or link. This is a modeling suggestion only, and reflects the fact that the table carries descriptive measures (CAPACITY, FROM_DATE, TO_DATE) keyed against a composite of transactional and planning identifiers rather than serving as an isolated reference entity.

Key Information Stored

The table contains 44 columns in the documented 12.2.2 schema. The most operationally significant columns are:

• PLAN_ID and TRANSACTION_ID — together form the primary key (MSC_SUPPLIER_CAPACITIES_PK); TRANSACTION_ID is a system-generated unique row identifier within a plan.
• SR_INSTANCE_ID, ORGANIZATION_ID, and INVENTORY_ITEM_ID — identify the source application instance, the owning organization, and the planned inventory item.
• SUPPLIER_ID and SUPPLIER_SITE_ID — identify the supplier and supplier site providing the capacity.
• FROM_DATE and TO_DATE — the validity window for the stated capacity.
• CAPACITY — the numeric capacity quantity available from the supplier during that window.
• PARENT_ID — reference to a parent transaction identifier, used to nest capacity records.
• USING_ORGANIZATION_ID and REFRESH_NUMBER — planning refresh context.
• Standard Who and Concurrent Who columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_ID, etc.) for audit and concurrent program traceability.

Two unique indexes act as business-key candidates. MSC_SUPPLIER_CAPACITIES_U1 covers PLAN_ID and TRANSACTION_ID. MSC_SUPPLIER_CAPACITIES_U2 — the index the user searched for — is a unique index on PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID, INVENTORY_ITEM_ID, FROM_DATE, SUPPLIER_ID, SUPPLIER_SITE_ID, and (in the 12.2.2 documented schema) NEW_PLAN_ID and SIMULATION_SET_ID. This composite uniquely identifies a supplier's capacity for an item at a point in time within a given plan and simulation context.

Common Use Cases and Queries

Typical usage centers on reporting supplier capacity constraints, validating the planning refresh output, and reconciling multi-org supplier commitments. A representative query joins the table to the plan header and item master:

SELECT s.plan_id, s.supplier_id, s.supplier_site_id, s.inventory_item_id, s.from_date, s.to_date, s.capacity FROM msc.msc_supplier_capacities s WHERE s.plan_id = :p_plan_id AND s.organization_id = :p_org ORDER BY s.supplier_id, s.from_date;

Analysts frequently filter on CAPACITY > 0 to isolate active constraint rows, or compare FROM_DATE/TO_DATE overlaps to detect duplicate or conflicting capacity windows for the same supplier-item combination — a validation facilitated by index U2. Because the table is refresh-driven, queries narrowed by REFRESH_NUMBER or PLAN_ID avoid stale data.

Related Objects

• MSC.MSC_SUPPLIER_CAPACITIES — self-referencing FK via PLAN_ID (and PARENT_ID semantics), supporting hierarchical or parent-child capacity rows.
• MSC.MSC_PLANS — the plan definition table joined on PLAN_ID.
• MSC.MSC_SYSTEM_ITEMS_B — inventory item master, joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MSC.MSC_SUPPLIERS — supplier reference joined on SUPPLIER_ID and SUPPLIER_SITE_ID.
• PO.PO_VENDORS / PO.PO_VENDOR_SITES_ALL — base supplier and site tables in the Purchasing schema.
• MSC.MSC_SR_INSTANCES — source instance definitions keyed by SR_INSTANCE_ID.
• MSC.MSC_SUPPLIER_CAPACITIES_U2 — the searched unique index, used primarily by internal planning joins and validation logic.