Search Results mrp_sr_assignments

Overview

The MRP_SR_ASSIGNMENTS table is a core data repository within the Oracle E-Business Suite (EBS) Master Scheduling/MRP module. It serves as the definitive store for sourcing assignment rules, which are critical for the planning engine's supply allocation logic. These rules define how the system determines the appropriate source—such as a specific supplier, a manufacturing organization, or a transfer-from organization—to fulfill a planned order, purchase requisition, or internal requisition. The table's role is to provide a flexible framework for enforcing business policies on sourcing based on combinations of item, category, customer, and ship-to location, thereby directly influencing material flow and procurement strategies within the MRP and Master Scheduling processes.

Key Information Stored

The table stores the rule definitions and their qualifying criteria. The primary identifier is the ASSIGNMENT_ID. Each record is grouped under an ASSIGNMENT_SET_ID, linking to a named set of sourcing rules. Key determinant columns include INVENTORY_ITEM_ID and ORGANIZATION_ID for item-specific rules, and CATEGORY_ID and CATEGORY_SET_ID for category-based assignments. To enable customer-centric sourcing, it holds CUSTOMER_ID and SHIP_TO_SITE_ID. The core sourcing directives are stored in columns such as SOURCE_ORGANIZATION_ID (for transfers), VENDOR_ID (for purchases), and SOURCE_TYPE (defining the nature of the source). Additional attributes like ASSIGNMENT_TYPE, RANK, and EFFECTIVE_DATE columns manage rule precedence and validity periods.

Common Use Cases and Queries

A primary use case is analyzing and troubleshooting the sourcing recommendations generated by an MRP plan run. Planners often query this table to verify which active rule is being applied for a specific item or customer. Common SQL patterns include listing all assignments within a set, or finding the effective rule for a given combination of parameters at a specific point in time. For reporting, joins with item (MTL_SYSTEM_ITEMS_B), customer (RA_CUSTOMERS), and site (RA_SITE_USES_ALL) tables are standard to translate IDs into meaningful names. Administrators may query the table to audit or clean up obsolete rules before migrating assignment sets to a production environment.

• Listing all sourcing rules for a specific assignment set: SELECT * FROM MRP_SR_ASSIGNMENTS WHERE ASSIGNMENT_SET_ID = &SET_ID ORDER BY RANK;
• Identifying the source for a specific item-organization-customer combination: SELECT SOURCE_TYPE, VENDOR_ID, SOURCE_ORGANIZATION_ID FROM MRP_SR_ASSIGNMENTS WHERE INVENTORY_ITEM_ID = &ITEM_ID AND ORGANIZATION_ID = &ORG_ID AND CUSTOMER_ID = &CUST_ID AND SYSDATE BETWEEN EFFECTIVE_DATE_FROM AND NVL(EFFECTIVE_DATE_TO, SYSDATE) AND ASSIGNMENT_SET_ID = &SET_ID;

Related Objects

The MRP_SR_ASSIGNMENTS table maintains integral relationships with several fundamental EBS tables, as documented by its foreign key constraints. It is a child of the MRP_ASSIGNMENT_SETS table via ASSIGNMENT_SET_ID, which groups individual rules. For item and category data, it references MTL_ITEM_CATEGORIES (on INVENTORY_ITEM_ID, ORGANIZATION_ID, CATEGORY_SET_ID) and MTL_CATEGORIES_B (on CATEGORY_ID). The ORGANIZATION_ID column references MTL_PARAMETERS to validate the inventory organization. For customer-centric rules, it links to RA_CUSTOMERS (on CUSTOMER_ID) and RA_SITE_USES_ALL (on SHIP_TO_SITE_ID) to define the customer ship-to location. These relationships ensure data integrity and enable comprehensive joins for operational and analytical queries.