Search Results mrp_item_task_sr_levels_v

Overview

MRP_ITEM_TASK_SR_LEVELS_V is an APPS-owned database view within the Oracle E-Business Suite Master Scheduling/MRP product family. It exposes inventory item sourcing assignments at the item-organization level, presenting the sourcing rules, source organizations, vendors, and allocation details that govern how a given inventory item is supplied to a receiving organization. The view is a curated projection over the core sourcing rule and assignment tables, joining item master attributes to sourcing rule definitions so that downstream planning, procurement, and reporting logic can query a flattened result set rather than navigating the underlying normalized structures.

Within Oracle EBS 12.1.1 and 12.2.2, the view is marked VALID in the ETRM data dictionary and is typically consumed by planning engines, supply chain reporting, and custom integrations that need to resolve sourcing hierarchies for make-or-buy decisions. Its naming convention ("TASK_SR_LEVELS") indicates it is intended to surface a sourcing level attribute — the literal value 1 SOURCING_LEVEL in the view text confirms it represents the first (item-org) level of a sourcing resolution model.

Underlying Base Objects

The documented base objects referenced by MRP_ITEM_TASK_SR_LEVELS_V are MRP_PLAN_ORGANIZATIONS_V (a view), MRP_SOURCING_RULES, MRP_SR_ASSIGNMENTS, MRP_SR_RECEIPT_ORG, MRP_SR_SOURCE_ORG, MTL_ITEM_CATEGORIES, MTL_PARAMETERS, and MTL_SYSTEM_ITEMS. The view text joins these through a hint-driven nested loop plan that indexes MRP_SR_ASSIGNMENTS_U2, MRP_SOURCING_RULES_U1, MRP_SR_RECEIPT_ORG_U2, MRP_SR_SOURCE_ORG_U2, and MTL_SYSTEM_ITEMS_U1.

In practice, MRP_PLAN_ORGANIZATIONS_V supplies the plan organization context; MTL_SYSTEM_ITEMS provides the primary item definition (planning code, make/buy code, UOM, item flags, BOM item type); MRP_SR_ASSIGNMENTS links items to sourcing rules; MRP_SOURCING_RULES defines the rule header and type; MRP_SR_RECEIPT_ORG supplies receiving-organization effective and disable dates; MRP_SR_SOURCE_ORG supplies source organization, vendor, vendor site, allocation percentage, rank, and source type; and a self-join on MTL_SYSTEM_ITEMS (aliased COMP_ITEMS) resolves the source organization's item record. The view also references MTL_ITEM_CATEGORIES and MTL_PARAMETERS in the metadata, consistent with category-based assignment resolution and organization parameter defaults.

Key Columns

• INVENTORY_ITEM_ID, ORGANIZATION_ID — the item and receiving organization context.
• ASSIGNMENT_TYPE, ASSIGNMENT_SET_ID, ASSIGNMENT_ID — identify the sourcing assignment; the predicate ASSIGNMENT_TYPE = 6 /* ITEM-ORG */ restricts output to item-organization level assignments.
• SOURCING_RULE_ID, SOURCING_RULE_NAME, SOURCING_RULE_TYPE, DESCRIPTION — the owning sourcing rule and its classification.
• SOURCE_ORGANIZATION_ID, VENDOR_ID, VENDOR_SITE_ID — the resolved source (internal org or external supplier).
• ALLOCATION_PERCENT, RANK, SOURCE_TYPE — the split percentage, preference rank, and type of the source.
• EFFECTIVE_DATE, DISABLE_DATE — validity window for the receiving organization assignment.
• MRP_PLANNING_CODE, PLANNING_MAKE_BUY_CODE, BOM_ITEM_TYPE, INVENTORY_ITEM_FLAG, ENG_ITEM_FLAG, PRIMARY_UOM_CODE — planning and item attributes inherited from MTL_SYSTEM_ITEMS.
• COMPILE_DESIGNATOR — plan identifier inherited from MRP_PLAN_ORGANIZATIONS_V.
• SOURCING_LEVEL — literal constant 1, denoting the item-org sourcing tier.

Common Use Cases and Queries

Typical uses include auditing sourcing assignments for a specific item, reconciling allocation percentages across sources, and feeding planning or procurement extracts. A representative query retrieves all active sources for an item in a given organization:

SELECT sourcing_rule_name, source_organization_id, vendor_id,
allocation_percent, rank, effective_date, disable_date
FROM apps.mrp_item_task_sr_levels_v
WHERE inventory_item_id = :item_id
AND organization_id = :org_id
AND effective_date <= SYSDATE
AND (disable_date IS NULL OR disable_date > SYSDATE)
ORDER BY rank, allocation_percent DESC;

Another frequent pattern joins the view to MTL_SYSTEM_ITEMS to enrich output with segment descriptions, or aggregates ALLOCATION_PERCENT by sourcing rule to validate that percentages total 100. Because the view is pre-filtered to item-org assignments (ASSIGNMENT_TYPE = 6), developers should not expect category-level or global assignments to appear; those must be sourced from the base MRP_SR_ASSIGNMENTS table directly. When embedding the view in concurrent programs or OBIEE/BI Publisher data models, applying the ORGANIZATION_ID and effective-date predicates early is advisable, as the underlying nested-loop plan is index-driven and benefits from selective filters.