Search Results msc_item_sourcing_levels_v




Overview
Explore in AI Chat

Overview

MSC_ITEM_SOURCING_LEVELS_V is a view owned by the APPS schema in Oracle E-Business Suite, part of the MSC (Advanced Supply Chain Planning) product. As its description states, the view "picks up all the sources at various levels for an item." In planning terms, sourcing rules determine where an item is procured or transferred from, and they can be defined at several assignment levels — item, category, organization, or global. This view consolidates those sourcing assignments into a single, flattened result set, exposing the item, the assignment level, the sourcing rule, the source organization or supplier, allocation percentages, ranking, and lead-time attributes.

The view is primarily a reporting and integration object. Planning users, custom reports, and data-extraction routines reference it to inspect how sourcing is configured across the planning instance without having to join the underlying MSC sourcing tables manually. Because it joins the assignment, sourcing rule, source organization, receipt organization, system items, and inter-org ship method tables, it provides a one-stop representation of the effective sourcing picture for a planned item.

Underlying Base Objects

The documented base objects referenced by the view are all MSC synonyms: MSC_SR_ASSIGNMENTS, MSC_SYSTEM_ITEMS, MSC_SOURCING_RULES, MSC_SR_RECEIPT_ORG, MSC_SR_SOURCE_ORG, and MSC_INTERORG_SHIP_METHODS. The ETRM 12.2.2 metadata also lists MSC_ITEM_CATEGORIES and MSC_TRADING_PARTNERS among the referenced objects.

  • MSC_SR_ASSIGNMENTS (MSRA) — the core driver, holding each sourcing assignment and its assignment type, set, and level.
  • MSC_SYSTEM_ITEMS (ITEMS) — supplies the inventory item and organization, filtered to PLAN_ID = -1 (the master planning instance).
  • MSC_SOURCING_RULES (MSR) — provides the sourcing rule type, name, and description.
  • MSC_SR_SOURCE_ORG (SOURCE_ORG) — the source organization, source type, partner (vendor) and site identifiers, allocation percent, and rank.
  • MSC_SR_RECEIPT_ORG (RECEIPT_ORG) — the receiving organization together with effective and disable dates.
  • MSC_INTERORG_SHIP_METHODS (SHIP) — the shipping method, average in-transit lead time, and time UOM for inter-org transfers.

The view joins these with outer joins on the ship method and uses NVL on SR_INSTANCE_ID and ORGANIZATION_ID to align shipping data to the item's planning instance and organization.

Key Columns

Common Use Cases and Queries

Typical uses include auditing sourcing rule setup, validating allocation percentages across levels, and feeding downstream planning or integration reports. A basic query lists all sources for a given item:

  • SELECT inventory_item_id, organization_id, sourcing_level, sourcing_rule_name, source_organization_id, vendor_id, allocation_percent, rank FROM msc_item_sourcing_levels_v WHERE inventory_item_id = :item_id;
  • SELECT sourcing_level, COUNT(*) FROM msc_item_sourcing_levels_v GROUP BY sourcing_level; — summarizes how many assignments exist at each sourcing level.
  • SELECT sourcing_rule_name, SUM(allocation_percent) FROM msc_item_sourcing_levels_v GROUP BY sourcing_rule_name HAVING SUM(allocation_percent) <> 100; — detects allocation totals that do not sum to 100 percent.

Because the view is read-only and built over planning tables, it is safe for reporting but should not be used for data maintenance; sourcing configuration changes must be made through the planning application or its public APIs.