Search Results mtl_reservations_view

Overview

MTL_RESERVATIONS_VIEW is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Inventory (INV) module. It is documented in ETRM 12.2.2 with the status VALID and an accompanying description of "Not used," indicating that the object is retained for backward compatibility and does not carry a functional role in the current application code path. Despite this designation, the view remains a valid queryable object in both 12.1.1 and 12.2.2 and exposes a curated projection of reservation data.

The view presents one row per active reservation detail record, drawn from the MTL_DEMAND table. It is not a table or a materialized view; no data is stored, so all results are computed at query time against the underlying demand records. Its principal value in reporting and integration is that it pre-filters MTL_DEMAND to reservation rows only and renames several columns to more intuitive identifiers, reducing the filtering logic that downstream queries must otherwise repeat.

Underlying Base Objects

The view is defined over a single referenced base object, MTL_DEMAND (SYNONYM), which is the core Inventory table that stores all demand and reservation records for an organization. The view applies four static predicates to that table:

• RESERVATION_TYPE = 2 — restricts output to reservation-type demand records.
• ROW_STATUS_FLAG = 1 — restricts output to active, non-deleted rows.
• PARENT_DEMAND_ID IS NOT NULL — restricts output to detail-level demand rows that have a parent demand record.
• PRIMARY_UOM_QUANTITY - COMPLETED_QUANTITY != 0 — excludes rows whose reservation quantity has been fully consumed, so only outstanding reservations are returned.

Because the view references only MTL_DEMAND, any transaction that inserts, updates, or deletes demand records is immediately reflected in subsequent queries. No join to MTL_SYSTEM_ITEMS, MTL_ITEM_LOCATIONS, or MTL_LOT_NUMBERS is embedded; descriptive attributes such as item, locator, and lot are returned only in coded form.

Key Columns

The view exposes thirteen columns. The DEMAND_SOURCE_HEADER_ID column is of particular interest to users searching on that term, as it identifies the header record of the originating demand source (for example, a sales order header or a move order header). Related columns are DEMAND_SOURCE_TYPE_ID (aliased from DEMAND_SOURCE_TYPE), DEMAND_SOURCE_LINE_ID (cast from DEMAND_SOURCE_LINE to a number), DEMAND_SOURCE_NAME, and DEMAND_SOURCE_DELIVERY. Together these form the full demand-source key. Rounding out the projection are ORGANIZATION_ID, INVENTORY_ITEM_ID, SUPPLY_SOURCE_TYPE_ID, PRIMARY_RESERVATION_QUANTITY (computed as PRIMARY_UOM_QUANTITY - COMPLETED_QUANTITY), REVISION, LOT_NUMBER, SUBINVENTORY_CODE (from SUBINVENTORY), and LOCATOR_ID.

Common Use Cases and Queries

The view is typically used to report outstanding reservations against a demand source, to reconcile reserved quantities before pick, and to feed integration extracts. A representative query locating reservations by demand source header is:

• SELECT organization_id, inventory_item_id, demand_source_header_id, demand_source_line_id, primary_reservation_quantity FROM mtl_reservations_view WHERE demand_source_header_id = :header_id;
• SELECT inventory_item_id, subinventory_code, locator_id, primary_reservation_quantity FROM mtl_reservations_view WHERE organization_id = :org_id AND inventory_item_id = :item_id;

Because the view carries no indexes, performance depends on the underlying MTL_DEMAND indexes, and queries should always be constrained by ORGANIZATION_ID and INVENTORY_ITEM_ID or by the demand source columns.