Search Results mrp_po_supply_view




Overview
Explore in AI Chat

Overview

MRP_PO_SUPPLY_VIEW is an APPS-owned database view in the Oracle E-Business Suite Master Scheduling/MRP (MRP) product. Its documented purpose is to present a consolidated "purchasing supply view," exposing purchase order and shipment supply records in a form suitable for the MRP planning engine and for reporting on incoming material. In Oracle EBS 12.1.1 and 12.2.2 the object is shipped with a status of VALID in the APPS schema.

The view is a core component of the supply side of the planning data model. Whereas demand is represented by sales orders, requisitions, and dependent demand, supply is represented by discrete records such as purchase orders, in-transit shipments, and receipts. MRP_PO_SUPPLY_VIEW normalizes these purchasing sources into a single, planner-friendly projection that carries item, organization, quantity, date, vendor, and destination attributes. Because it is a view rather than a table, it is always calculated from the current transactional state of the underlying purchasing tables, making it suitable for real-time querying inside concurrent programs, BI Publisher reports, and custom SQL.

Underlying Base Objects

The documented ETRM metadata for 12.2.2 identifies the following referenced objects: AP_SUPPLIER_SITES_ALL (synonym), FND_PROFILE (package), MRP_SYSTEM_ITEMS (synonym), MTL_UNITS_OF_MEASURE (synonym), OE_DROP_SHIP_SOURCES (synonym), PO_PO_SUPPLY_VIEW (view), PO_RCV_SUPPLY_VIEW (view), PO_REQ_SUPPLY_VIEW (view), PO_SHIP_RCV_SUPPLY_VIEW (view), PO_SHIP_SUPPLY_VIEW (view), PO_UNITEFF_PKG (package), and PO_VENDORS_VIEW (view).

The view text confirms that it is defined as a UNION ALL of two component sources. The first branch selects from PO_PO_SUPPLY_VIEW, joined to MTL_UNITS_OF_MEASURE for the primary unit of measure and to MRP_SYSTEM_ITEMS for item attributes such as COMPILE_DESIGNATOR and SHRINKAGE_RATE. This branch carries purchase order lines. The second branch selects from the shipment supply view (PO_SHIP_SUPPLY_VIEW), joined to MRP_SYSTEM_ITEMS and MTL_UNITS_OF_MEASURE, and carries in-transit shipment records. A literal discriminator column — 1 for purchase orders and 11 for shipments — distinguishes the two row types within the union.

Shrinkage is applied in both branches through MRP_PRIMARY_QUANTITY * DECODE(SIGN(ITEMS.SHRINKAGE_RATE), -1, 0, NVL(ITEMS.SHRINKAGE_RATE, 0)), producing a shrinkage-adjusted quantity alongside the raw quantity. Associated policy views (PO_RCV_SUPPLY_VIEW, PO_REQ_SUPPLY_VIEW, PO_SHIP_RCV_SUPPLY_VIEW) cover receipts and requisitions, and OE_DROP_SHIP_SOURCES participates in the drop-shipment handling path.

Key Columns

Common Use Cases and Queries

The view is typically used by supply/demand reports, exception reports for late or overdue purchase orders, MRP planner workbench supplements, and integration extracts feeding external planning systems. A basic listing of purchase order supply for an organization would take the form:

  • SELECT item_id, po_number, line_num, vendor_id, mrp_primary_quantity, mrp_expected_delivery_date FROM mrp_po_supply_view WHERE mrp_to_organization_id = :org_id ORDER BY mrp_expected_delivery_date;
  • SELECT item_id, SUM(mrp_primary_quantity) total_supply FROM mrp_po_supply_view WHERE mrp_to_organization_id = :org_id AND mrp_expected_delivery_date BETWEEN :start_date AND :end_date GROUP BY item_id;
  • SELECT po_number, line_num, expected_dock_date FROM mrp_po_supply_view WHERE item_id = :item_id AND expected_dock_date < SYSDATE ORDER BY expected_dock_date;

Because the object is a view over multiple purchasing and planning sources, queries should filter aggressively on organization and item to control execution cost, and should never be treated as an updatable entity.