Search Results wms_loaded_quantities_v




Overview
Explore in AI Chat

Overview

WMS_LOADED_QUANTITIES_V is an APPS-owned database view in the Oracle E-Business Suite Warehouse Management (WMS) module. It exposes dispatch-staged inventory quantities as recorded in the material transaction interface tables, presenting a consolidated picture of item quantities that have been "loaded" onto dispatched tasks but not yet posted to the perpetual inventory. The view is defined with QTY_TYPE hard-coded as 'LOADED', which distinguishes it within the family of WMS quantity views used by the warehouse execution screens and label-printing flows. Because it is a view and not a table, it carries no stored data; its contents are derived at query time and reflect the transient state of the transaction interface for tasks that are actively being dispatched.

Its principal role is reporting and integration support: it allows implementers and custom code to inspect exactly what a warehouse operator has loaded for a given dispatch, including LPN assignments, lot and serial references, and both primary and secondary quantities, without having to write the multi-table join themselves.

Underlying Base Objects

The view is documented as being defined over three base objects, all referenced through APPS synonyms:

  • WMS_DISPATCHED_TASKS — the driving table, aliased W. The view filters this table with STATUS = 4 and TASK_TYPE <> 2, restricting output to dispatched tasks in the relevant status and excluding the excluded task type.
  • MTL_MATERIAL_TRANSACTIONS_TEMP — the transaction interface table, aliased T. It is inner-joined to WMS_DISPATCHED_TASKS on TRANSACTION_TEMP_ID, supplying item, revision, subinventory, locator, cost group, and LPN information for each pending transaction row.
  • MTL_TRANSACTION_LOTS_TEMP — the lot/serial transaction interface table, aliased L. It is outer-joined (L.TRANSACTION_TEMP_ID = T.TRANSACTION_TEMP_ID (+)) so that transactions without lot or serial detail are still returned.

The join therefore returns one row per pending transaction interface record that belongs to a qualifying dispatched task.

Key Columns

  • ORGANIZATION_ID, INVENTORY_ITEM_ID, REVISION — identify the warehouse, item, and revision for the loaded quantity.
  • LOT_NUMBER — sourced from the lot interface table; null when no lot applies.
  • COST_GROUP_ID — the cost group carried on the pending transaction.
  • CONTAINERIZED_FLAG — a derived value computed with DECODE(NVL(LPN_ID, NVL(CONTENT_LPN_ID,-1)), -1, 2, 1), indicating whether the quantity is containerized; a value of 2 indicates no LPN association, and 1 indicates an LPN is present.
  • SUBINVENTORY_CODE and LOCATOR_ID — the physical stocking location the quantity is currently attributed to.
  • QUANTITY — the primary quantity, taking the lot interface value where available (NVL(L.PRIMARY_QUANTITY, T.PRIMARY_QUANTITY)).
  • QTY_TYPE — literal 'LOADED', used to union or label this view against companion quantity views.
  • TRANSACTION_TEMP_ID — the link key to WMS_DISPATCHED_TASKS and to the underlying interface rows.
  • CONTENT_LPN_ID, LPN_ID, TRANSFER_LPN_ID — the license plate identifiers for the container, its content, and any transfer container.
  • SERIAL_TRANSACTION_TEMP_ID — the serial number reference from the lot interface table.
  • SECONDARY_QUANTITY — the secondary UOM quantity, again preferring the lot interface value (NVL(L.SECONDARY_QUANTITY, T.SECONDARY_TRANSACTION_QUANTITY)).

Common Use Cases and Queries

Typical uses include auditing what is currently staged for dispatch, validating LPN and lot assignments before a ship confirm, and feeding custom label or integration processes that need loaded quantities.

SELECT organization_id,
       inventory_item_id,
       lot_number,
       subinventory_code,
       locator_id,
       quantity,
       secondary_quantity,
       containerized_flag,
       lpn_id,
       content_lpn_id
  FROM apps.wms_loaded_quantities_v
 WHERE organization_id = :org_id
   AND inventory_item_id = :item_id;

Because the view reads the temporary interface tables, its rows exist only while dispatch transactions are pending. Integrators should query it within the dispatch window and treat results as transient; once transactions post to MTL_MATERIAL_TRANSACTIONS, the rows disappear from this view. Filtering on LPN_ID or CONTAINERIZED_FLAG is the usual way to isolate containerized versus loose loaded quantities for a dispatch.