Results for “mrp_item_sourcing_pk”

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

Overview

The MRP_ITEM_SOURCING table, owned by the MRP schema, stores sourcing information for planned items within Oracle E-Business Suite Release 12.1.1 and 12.2.2. It defines where an item in a given organization is procured or manufactured from, capturing either an internal source organization (for transfer-based sourcing) or an external supplier and supplier site (for buy-based sourcing). This information is fundamental to the planning engines in Master Scheduling/MRP and Advanced Supply Chain Planning, which use source assignments and sourcing rules to generate planned orders, distribution requirements, and supply recommendations.

From a Data Vault modeling perspective, the mined heuristic classification for MRP_ITEM_SOURCING is satellite-leaning. This reflects the table's role as a descriptive, attribute-bearing record attached to the composite business key of item, organization, source, and effective date, rather than a pure transactional link or a standalone hub. It captures versioned sourcing attributes over time, consistent with satellite semantics.

Key Information Stored

The table contains 25 documented columns. The most significant are grouped below.

• Business key and uniqueness: COMPILE_DESIGNATOR, ORGANIZATION_ID, INVENTORY_ITEM_ID, SOURCE_ORGANIZATION_ID, VENDOR_ID, VENDOR_SITE_ID, and EFFECTIVE_DATE form the primary key (MRP_ITEM_SOURCING_PK) and the unique index MRP_ITEM_SOURCING_U1. These columns collectively identify a sourcing record for an item within an organization as of a given effective date.
• Source definition: SOURCE_TYPE distinguishes internal versus external sourcing; SOURCE_ORGANIZATION_ID identifies the supplying inventory organization; VENDOR_ID and VENDOR_SITE_ID identify the supplier and supplier site.
• Sourcing rules: SOURCING_RULE_ID, SOURCING_RULE_TYPE, ASSIGNMENT_ID, ASSIGNMENT_SET_ID, and ASSIGNMENT_TYPE link the record to sourcing rule and assignment definitions that drive planning logic.
• Quantity and priority: ALLOCATION_PERCENT, QUANTITY, RANK, and HISTORICAL_ALLOC_QTY quantify allocation or priority across multiple sources.
• Timing and logistics: EFFECTIVE_DATE and DISABLE_DATE bound the validity window; AVG_TRANSIT_LEAD_TIME and SHIP_METHOD capture transit and shipping attributes.
• Audit columns: LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, and LAST_UPDATE_LOGIN track row-level change history.

Common Use Cases and Queries

Typical uses include validating sourcing assignments before a planning run, reporting approved sources per item, and auditing allocation percentages across multiple sources. A common query pattern retrieves effective sourcing for a specific item and organization:

• SELECT inventory_item_id, source_organization_id, vendor_id, vendor_site_id, allocation_percent, source_type, effective_date FROM mrp_item_sourcing WHERE organization_id = :org_id AND inventory_item_id = :item_id AND (disable_date IS NULL OR disable_date > SYSDATE) AND effective_date <= SYSDATE;
• Reporting queries join to MRP_SYSTEM_ITEMS to resolve item descriptions and segmentation attributes.
• Planning diagnostics compare ALLOCATION_PERCENT against QUANTITY to detect sourcing conflicts, while effective-dated queries support point-in-time sourcing analysis.

Related Objects

The documented foreign key relationship and typical dependencies include:

• MRP_SYSTEM_ITEMS — referenced via INVENTORY_ITEM_ID, ORGANIZATION_ID, and COMPILE_DESIGNATOR; provides item master and planning attributes.
• MRP_SOURCING_RULES and related assignment tables — referenced through SOURCING_RULE_ID and ASSIGNMENT_ID.
• MTL_SYSTEM_ITEMS_B — the underlying item master for validated item and organization combinations.
• PO_VENDORS and PO_VENDOR_SITES_ALL — resolve VENDOR_ID and VENDOR_SITE_ID for supplier details.
• MRP_ITEM_SOURCING is consumed by planning and sourcing APIs and concurrent programs that generate supply plans and distribution orders.