Search Results old_item_sourcing_levels_v

Overview

The view OLD_ITEM_SOURCING_LEVELS_V belongs to the Oracle Master Scheduling/MRP (MRP) module and is documented in ETRM as an "Inventory items sources view." Its purpose is to expose sourcing information for inventory items managed within Oracle EBS 12.1.1 and 12.2.2, consolidating assignment records, sourcing rules, receipt organization details, source organizations, and inter-org shipping methods into a single, queryable structure. This view is designated "OLD" because it represents a legacy definition of item sourcing levels that predates or coexists alongside newer sourcing APIs and views in the sourcing rule framework.

Within Oracle EBS reporting and integration, this view acts as a denormalized read model over the sourcing rule assignment data model. Reporting layers, custom extracts, and integration points that need to resolve an item's sourcing level — the assignment type and the sourcing rule that applies to a given item and organization — can query this view rather than joining the underlying MRP sourcing tables manually. The view is especially relevant where sourcing rules are assigned at the ITEM-ORG level, as indicated by the ASSIGNMENT_TYPE = 6 filter in its defining text.

Underlying Base Objects

The documentation states that this view is "Not implemented in this database," and no referenced base objects are recorded in the ETRM metadata. However, the view text embedded in the documentation reveals its full dependency set. The view is constructed as a UNION ALL of multiple branches, each of which joins the same family of base tables:

• MRP_SR_ASSIGNMENTS (aliased MSRA) — sourcing rule assignments, filtered to ASSIGNMENT_TYPE = 6 (ITEM-ORG) in the first branch.
• MTL_SYSTEM_ITEMS (aliased ITEMS) — the item master, providing INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MRP_SOURCING_RULES (aliased MSR) — the sourcing rule header, filtered to STATUS = 1 (active).
• MRP_SR_RECEIPT_ORG (aliased RECEIPT_ORG) — receipt organization assignments tied to a sourcing rule, supplying EFFECTIVE_DATE and DISABLE_DATE.
• MRP_SR_SOURCE_ORG (aliased SOURCE_ORG) — source organization, vendor, and allocation details, supplying SOURCE_ORGANIZATION_ID, VENDOR_ID, VENDOR_SITE_ID, ALLOCATION_PERCENT, and RANK.
• MTL_INTERORG_SHIP_METHODS (aliased SHIP) — shipping method data such as INTRANSIT_TIME, SHIP_METHOD, and TIME_UOM_CODE.

The joins are outer joins on the shipping method relative to source organization, and the cost-based hints (ORDERED, specific indexes such as MRP_SR_ASSIGNMENTS_N3, MRP_SOURCING_RULES_U1, and USE_NL) indicate the view is tuned for nested-loop access across these sourcing tables.

Key Columns

The view exposes the following significant columns, each derived from the base objects listed above:

• INVENTORY_ITEM_ID, ORGANIZATION_ID — identify the item and the organization context.
• ASSIGNMENT_TYPE, ASSIGNMENT_SET_ID, ASSIGNMENT_ID — identify the sourcing assignment and its level.
• SOURCING_RULE_ID, SOURCING_RULE_NAME, SOURCING_RULE_TYPE, DESCRIPTION — describe the applicable sourcing rule.
• SOURCE_ORGANIZATION_ID, VENDOR_ID, VENDOR_SITE_ID, SOURCE_TYPE — define the source of supply.
• ALLOCATION_PERCENT, RANK — express the sourcing split and priority among sources.
• EFFECTIVE_DATE, DISABLE_DATE — validity window for the receipt organization assignment.
• SHIP_METHOD, INTRANSIT_TIME, TIME_UOM_CODE — inter-org shipping characteristics.
• SOURCING_LEVEL — a literal value of 1 in the documented branch, denoting the sourcing level for the assignment.
• CATEGORY_ID, CUSTOMER_ID, SHIP_TO_SITE_ID — additional assignment context.

Common Use Cases and Queries

Typical uses include auditing which sourcing rules apply to items, validating allocation percentages and ranks across source organizations, and feeding planning or order-promising logic. A representative query retrieves sourcing details for a specific item and organization:

SELECT sourcing_rule_name,
       source_organization_id,
       vendor_id,
       allocation_percent,
       rank,
       effective_date,
       disable_date
FROM   old_item_sourcing_levels_v
WHERE  inventory_item_id = :item_id
AND    organization_id  = :org_id;

Note that since the ETRM metadata records this view as not implemented in the database and no referenced base objects are documented, availability must be verified against the target instance before use; where absent, equivalent queries should be written directly against the MRP sourcing tables shown in the view definition. The user's search term, mtl_parameters_u1, relates to the MTL_PARAMETERS unique index and is not part of this view's defining text, so it is not exposed by this object.