Search Results msc_sources_v




Overview
Explore in AI Chat

Overview

MSC_SOURCES_V is a view in the APPS schema owned by the Advanced Supply Chain Planning (MSC) product within Oracle E-Business Suite 12.1.1 and 12.2.2. It presents inventory item sourcing information as configured through Oracle's sourcing rules and assignment sets. The view consolidates the sourcing hierarchy for a given inventory item and organization, exposing the resolved source — whether an internal organization, an external supplier, or a customer — together with allocation percentages, ranking, transit lead times, and effective dates. Its most notable behavior is the filtering logic embedded in its definition: an analytic MIN function over the partition (ORGANIZATION_ID, INVENTORY_ITEM_ID, SR_INSTANCE_ID, ASSIGNMENT_SET_ID) identifies the minimum sourcing level for each item/organization combination, and the outer query retains only rows that match that minimum level. This produces a flattened, de-duplicated result set representing the winning sourcing rule per item and organization, which is what planning engines and supply chain reports typically require.

Because the view already resolves assignment-set joins and sourcing-level precedence, it shields consumers from the more complex joins required against the underlying sourcing tables directly. In reporting and integration contexts, it functions as the authoritative read-only interface for "where does this item come from" questions.

Underlying Base Objects

The documented base objects referenced by MSC_SOURCES_V are:

  • MSC_ASSIGNMENT_SETS (synonym) — provides ASSIGNMENT_SET_ID and links sourcing rule instances (SR_INSTANCE_ID) to assignment sets.
  • MSC_ITEM_SOURCING_LEVELS_V (view) — supplies the core sourcing row attributes, including item, organization, sourcing rule, source type, allocation, rank, and the SOURCING_LEVEL column used for the minimum-level filter.

The join condition is MAS.SR_INSTANCE_ID = MISLV1.SR_INSTANCE_ID AND MAS.ASSIGNMENT_SET_ID = NVL(MISLV1.ASSIGNMENT_SET_ID, MAS.ASSIGNMENT_SET_ID). The NVL fallback ensures that when a sourcing level row has no explicit assignment set, the parent assignment set from MSC_ASSIGNMENT_SETS is used, preserving referential integrity across the two objects. The view therefore depends on the validity of both objects; if MSC_ITEM_SOURCING_LEVELS_V becomes invalid, MSC_SOURCES_V becomes invalid in turn.

Key Columns

Common Use Cases and Queries

Typical uses include validating item sourcing setups, auditing allocation percentages across approved sources, and feeding sourcing data into planning extracts or custom reports.

List all approved sources for a specific item and organization:

SELECT inventory_item_id, organization_id, source_type, source_organization_id, vendor_id, allocation_percent, rank, time_uom_code, effective_date, disable_date FROM msc_sources_v WHERE inventory_item_id = :item_id AND organization_id = :org_id AND TRUNC(SYSDATE) BETWEEN effective_date AND NVL(disable_date, TRUNC(SYSDATE)+1);

Retrieve transit lead times with their unit of measure for external sources:

SELECT inventory_item_id, organization_id, vendor_id, vendor_site_id, avg_transit_lead_time, time_uom_code, ship_method FROM msc_sources_v WHERE source_type = 'SUPPLIER' AND organization_id = :org_id ORDER BY inventory_item_id, rank;

Because the view applies the minimum sourcing level filter, consumers should not expect to see every configured sourcing level row; only the lowest-level (highest-precedence) rows per item, organization, and assignment set are returned. Reports requiring the full hierarchy should query MSC_ITEM_SOURCING_LEVELS_V directly.