Search Results invfv_inventory_reservations




Overview
Explore in AI Chat

Overview

APPS.INVFV_INVENTORY_RESERVATIONS is a validity-status VIEW in the Oracle E-Business Suite Inventory (INV) module, owned by the APPS schema. In the ETRM metadata it is classified as a retrofitted object, indicating that it was ported into the 12.1.1 / 12.2.2 environment to preserve compatibility with earlier reporting and integration interfaces. Its purpose is to expose inventory reservation detail — that is, material demand recorded against an item, subinventory, and locator — in a single flattened row per reservation line, suitable for reporting tools, concurrent programs, and third-party integrations.

The view presents reservation information in the "Fulfillment" style: it reports the quantity reserved, the quantity issued, and the quantity still outstanding (pending issue) for each demand line. Because it consolidates organization, item, sales order, transaction source, locator, revision, and lot attributes, it removes the need for consumers to join MTL_DEMAND and the surrounding reference tables themselves. ETRM lists the object status as VALID, which is the expected state for a retrofitted view that has been successfully compiled against the current database link and privilege configuration.

Underlying Base Objects

The documented referenced base objects for the view are: GL_CODE_COMBINATIONS, HR_ALL_ORGANIZATION_UNITS, INV_CONVERT (package), MTL_DEMAND, MTL_GENERIC_DISPOSITIONS, MTL_ITEM_LOCATIONS, MTL_PARAMETERS, MTL_SALES_ORDERS, MTL_SYSTEM_ITEMS, and MTL_TXN_SOURCE_TYPES. The view text confirms these relationships directly:

  • MTL_DEMAND (M) is the driving table, supplying reservation rows. The view filters on M.RESERVATION_TYPE = 2 and requires a positive unissued balance, expressed as (M.PRIMARY_UOM_QUANTITY - M.COMPLETED_QUANTITY) > 0.
  • MTL_SYSTEM_ITEMS joins on ORGANIZATION_ID and INVENTORY_ITEM_ID, providing PRIMARY_UOM_CODE for conversion and the "_KF:INV:MSTK:MSI" key flexfield reference.
  • MTL_ITEM_LOCATIONS joins with the outer-join operator on INVENTORY_LOCATION_ID and ORGANIZATION_ID, so reservations without a locator are still returned.
  • HR_ALL_ORGANIZATION_UNITS and MTL_PARAMETERS supply the organization name and organization code.
  • MTL_TXN_SOURCE_TYPES resolves DEMAND_SOURCE_TYPE to its descriptive TRANSACTION_SOURCE_TYPE_NAME.
  • MTL_SALES_ORDERS joins on DEMAND_SOURCE_HEADER_ID for sales-order-driven demand.
  • INV_CONVERT is invoked as the INV_UM_CONVERT function to translate completed quantity into the primary unit of measure.
  • GL_CODE_COMBINATIONS and MTL_GENERIC_DISPOSITIONS appear in the account and disposition-driven UNION ALL branches referenced in the excerpt (DEMAND_SOURCE_TYPE 3).

The view is a UNION ALL of several branches, one per demand source type — 2, 8, and 12 in the first branch, 3 in the second — reflecting account, sales order, and other reservation origins.

Key Columns

  • DEMAND_ID — primary identifier of the reservation/demand line.
  • ORGANIZATION_ID / NAME / ORGANIZATION_CODE — warehouse identity from the organization and parameters tables.
  • INVENTORY_ITEM_ID — item reference; accompanied by the "_KF:INV:MSTK:MSI" flexfield token.
  • REQUIREMENT_DATE — date the reserved material is required.
  • DEMAND_SOURCE_TYPE / MTS.TRANSACTION_SOURCE_TYPE_NAME — type of demand driving the reservation.
  • DEMAND_SOURCE_HEADER_ID, SALES_ORDER_ID — source header; aliased to SALES_ORDER_ID for order-driven demand.
  • ACCOUNT_ID / ACCOUNT_ALIAS_ID — account identifiers, returned as NULL in the sales-order branch.
  • UOM / RESERVED_QUANTITY — the reservation unit of measure and the line item quantity reserved.
  • ISSUED_QUANTITY — completed quantity converted to the primary UOM via INV_CONVERT.
  • PENDING_ISSUE_QUANTITY — reserved quantity minus issued quantity; the open balance to fulfill.
  • REVISION, LOT_NUMBER, SUBINVENTORY, LOCATOR_ID — the physical placement and control attributes of the reserved material.
  • USER_LINE_NUM, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — audit and line-ordering columns.

Common Use Cases and Queries

Typical uses include open reservation reporting, order fulfillment reconciliation, and inventory commitment extracts for downstream planning systems. A representative query:

  • SELECT organization_code, inventory_item_id, sales_order_id, lot_number, subinventory, reserved_quantity, pending_issue_quantity FROM apps.invfv_inventory_reservations WHERE organization_id = :org_id AND requirement_date < SYSDATE + 7;
  • SELECT demand_source_type, SUM(reserved_quantity), SUM(pending_issue_quantity) FROM apps.invfv_inventory_reservations GROUP BY demand_source_type;
  • Filters on LOT_NUMBER, SUBINVENTORY, or REVISION isolate specific material for cycle counting or picking.

Because the view embeds the security predicate '_SEC:M.ORGANIZATION_ID' IS NOT NULL, consumers must be aware that organization-level security is enforced at query time, and results will be restricted to the organizations the querying responsibility is permitted to see.