Results for “mrp_item_sr_levels_v”

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

Overview

MRP_ITEM_SR_LEVELS_V is an APPS-owned database view within the MRP (Master Scheduling/MRP) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to present inventory item sourcing levels—resolving the sourcing rules and assignment sets that determine how an item is replenished at a given organization. The view effectively answers the question "at which sourcing level is this item sourced, and from where?" for each item/organization combination.

The view plays a key role in reporting and integration because it flattens the multi-table structure of Oracle's sourcing rule engine into a single, query-friendly result set. Downstream logic—supply chain planning, inter-org transfers, and ATP-style sourcing lookups—depends on this consolidated picture of sourcing assignments. The view is defined as a UNION ALL of at least two query branches: one covering item-, organization-, item-org-, and site-level assignments, and a second covering category-based assignments. This union is what allows a single query against the view to return sourcing levels for both directly assigned items and items that inherit sourcing through category assignment.

Underlying Base Objects

According to the documented ETRM metadata for 12.2.2, MRP_ITEM_SR_LEVELS_V references the following base objects (exposed as synonyms in the APPS schema):

• MRP_SOURCING_RULES — the sourcing rule header, filtered to STATUS = 1 (active rules).
• MRP_SR_ASSIGNMENTS — the assignment of a sourcing rule to a site, organization, item, item-org combination, or category.
• MRP_SR_RECEIPT_ORG — receipt organization detail, carrying the effective and disable dates for a sourcing rule.
• MRP_SR_SOURCE_ORG — the source organization from which supply is drawn.
• MTL_INTERORG_SHIP_METHODS — the shipping method linking source and destination organizations.
• MTL_ITEM_CATEGORIES — used in the category branch of the UNION ALL, to resolve category-based assignments.
• MTL_SYSTEM_ITEMS — the item master, supplying INVENTORY_ITEM_ID and ORGANIZATION_ID.

The view joins these objects through a chain of outer and inner joins that map sourcing rules to receipt organizations, then to source organizations and shipping methods. The documented hint block references INDEX(MSRA MRP_SR_ASSIGNMENTS_U2), which is the index the view was tuned to use on the MRP_SR_ASSIGNMENTS table—this is the same index the user searched for under the name "mrp_sr_assignments_u2."

Key Columns

The view exposes the following notable columns:

• INVENTORY_ITEM_ID — the item identifier, taken from MTL_SYSTEM_ITEMS (or from the category branch via MTL_ITEM_CATEGORIES).
• ORGANIZATION_ID — the destination organization for which the sourcing level applies.
• ASSIGNMENT_SET_ID — identifies the assignment set that owns the sourcing rule assignment.
• SOURCE_ORGANIZATION_ID — the supplying (source) organization from MRP_SR_SOURCE_ORG.
• EFFECTIVE_DATE and DISABLE_DATE — the validity window of the receipt organization record, useful for filtering active sourcing.
• SOURCING_LEVEL — a derived, encoded value produced by nested DECODE expressions over MSRA.ASSIGNMENT_TYPE and MSR.SOURCING_RULE_TYPE. It numerically represents whether sourcing is defined at the site, organization, item, item-org, or category level.

The SOURCING_LEVEL column is the analytical heart of the view: it normalizes the many assignment-type permutations into a comparable numeric value.

Common Use Cases and Queries

Typical scenarios include validating sourcing rule coverage for a set of items, troubleshooting why a planned order picked a particular source organization, and auditing assignment sets. A representative query is:

• SELECT inventory_item_id, organization_id, sourcing_level, source_organization_id FROM apps.mrp_item_sr_levels_v WHERE organization_id = :org_id AND inventory_item_id = :item_id;
• SELECT organization_id, source_organization_id, COUNT(*) FROM apps.mrp_item_sr_levels_v WHERE SYSDATE BETWEEN effective_date AND NVL(disable_date, SYSDATE + 1) GROUP BY organization_id, source_organization_id;
• SELECT sourcing_level, COUNT(*) FROM apps.mrp_item_sr_levels_v GROUP BY sourcing_level ORDER BY sourcing_level;

Because the view uses UNION ALL and complex DECODE logic, queries benefit from filtering on INVENTORY_ITEM_ID and ORGANIZATION_ID to allow the optimizer to exploit MRP_SR_ASSIGNMENTS_U2, as the embedded hint intends.