Results for “msc_item_sourcing”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MSC.MSC_ITEM_SOURCING is a table within the Oracle Advanced Supply Chain Planning (ASCP) schema that stores sourcing information for items used by Oracle Advanced Planning and Scheduling. It defines how a planned item is procured, manufactured, or transferred, capturing the rules, priorities, and allocation percentages that the planning engine applies when generating supply recommendations. The table also holds sourcing information for In-Line Vehicle Sequencing (ILVS) in the Supply Chain Hub product.

Within the ETRM classification model, this object is best described as a link table rather than a hub or satellite. Heuristic analysis of its foreign key structure shows that it resolves relationships among several distinct business entities: items, organizations, suppliers, supplier sites, customers, sourcing rules, assignments, trading partners, and warehouse zones. Each row therefore represents an intersection of sourcing dimensions rather than a single master entity, making the link classification a sound modeling suggestion.

Key Information Stored

The table encompasses 59 documented columns. The most significant columns fall into several functional groups.

• Planning context: PLAN_ID identifies the plan in which the sourcing definition is evaluated, while ORGANIZATION_ID and SR_INSTANCE_ID scope the record to a specific organization and source instance.
• Item and source identity: INVENTORY_ITEM_ID identifies the planned item, and SOURCE_ORGANIZATION_ID (with SR_INSTANCE_ID2) identifies the internal source organization when the source is a transfer.
• Supplier and customer: SUPPLIER_ID, SUPPLIER_SITE_ID, CUSTOMER_ID, and CUSTOMER_SITE_ID capture external sourcing and demand-side references, while ZONE_ID supports warehouse zone sourcing.
• Sourcing logic: SOURCE_TYPE, SOURCING_RULE_ID, SOURCING_RULE_TYPE, ASSIGNMENT_ID, ASSIGNMENT_SET_ID, ASSIGNMENT_TYPE, and ASSIGNMENT_SET_TYPE link the row to the sourcing and assignment framework.
• Priority and allocation: RANK, ALLOCATION_PERCENT, and CIRCULAR_SRC govern the ordering and percentage split among competing sources.
• Time and logistics: EFFECTIVE_DATE, DISABLE_DATE, SHIP_METHOD, AVG_TRANSIT_LEAD_TIME, and CONVERSION_FACTOR define validity windows and transit characteristics.
• Transfer attributes: MIN_TRANSFER_QTY, MAX_TRANSFER_QTY, TRANSFER_TYPE, TRANSFER_LOT_MULTIPLE, and FIXED_TRANSFER_QTY constrain transfer quantities.
• Audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_ID, and the ATTRIBUTE1 through ATTRIBUTE15 descriptive flexfield columns.

The unique index MSC_ITEM_SOURCING_U1 constitutes the business-key candidate, spanning PLAN_ID, ORGANIZATION_ID, SR_INSTANCE_ID, INVENTORY_ITEM_ID, SOURCE_ORGANIZATION_ID, SR_INSTANCE_ID2, SUPPLIER_ID, SUPPLIER_SITE_ID, EFFECTIVE_DATE, SHIP_METHOD, CUSTOMER_ID, CUSTOMER_SITE_ID, ZONE_ID, ASSIGNMENT_SET_TYPE, RANK, CIRCULAR_SRC, and ITEM_TYPE_VALUE. No separate surrogate primary key column is documented.

Common Use Cases and Queries

Typical usage centers on validating sourcing logic, auditing allocation percentages, and diagnosing planning exceptions. A common pattern joins sourcing rows to the item master and suppliers to produce a sourcing report for a given plan.

SELECT s.inventory_item_id, s.organization_id, s.source_type,
       s.supplier_id, s.rank, s.allocation_percent
  FROM msc.msc_item_sourcing s
 WHERE s.plan_id = :plan_id
   AND s.organization_id = :org_id
   AND TRUNC(SYSDATE) BETWEEN s.effective_date
       AND NVL(s.disable_date, TRUNC(SYSDATE)+1);

Analysts also aggregate allocation percentages by item to verify that approved sources total 100 percent, and they filter on CIRCULAR_SRC to detect circular sourcing definitions that the planning engine cannot resolve. Reporting on SOURCE_TYPE distributions distinguishes buy, make, and transfer strategies across the supply network.

Related Objects

• MSC_SYSTEM_ITEMS — joined on INVENTORY_ITEM_ID (and PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID) to retrieve item attributes.
• MSC_SOURCING_RULES — joined on SOURCING_RULE_ID to obtain the governing sourcing rule definition.
• MSC_SR_ASSIGNMENTS — joined on ASSIGNMENT_ID to resolve assignment sets and their scope.
• MSC_TRADING_PARTNERS — joined on SUPPLIER_ID to identify the supplier entity.
• MSC_TRADING_PARTNER_SITES — joined on SUPPLIER_SITE_ID to identify the supplier site.
• WMS_ZONES_B — joined on ZONE_ID to resolve warehouse zone definitions used in zone-based sourcing.

These relationships confirm the table's role as a central link connecting item, supplier, assignment, and rule entities within the ASCP data model.