Search Results secondary_transaction_quantity




Overview
Explore in AI Chat

Overview

The WMS_ONHAND_AND_LOADED_QTY_V view is a reporting and integration object owned by the APPS schema within the Warehouse Management (WMS) module of Oracle E-Business Suite. It presents a consolidated, union-based picture of inventory that is physically on hand alongside inventory that has been picked, staged, and loaded onto equipment but has not yet been confirmed as shipped — that is, material in transit. Its primary design intent is to normalize both categories of quantity into a single queryable structure so that downstream logic can compute effective available or committed stock without joining multiple transactional tables.

A defining characteristic of the view is its sign convention. Records drawn from the on-hand source carry positive quantities, while transit records are deliberately negated so that a straight summation across the view yields a meaningful net figure. The documentation states that all transit records will have a negative quantity, so performing a sum on this view will produce a combined total. This makes the view suited to availability reporting, load-verification dashboards, and integrations that must reconcile on-hand balances against dispatched-but-unshipped material.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms:

The union combines a straightforward projection from on-hand detail with a more complex projection joining dispatched tasks to temporary transaction and lot records via TRANSACTION_TEMP_ID. This join pattern reflects the WMS dispatch workflow, where loaded material resides in temporary tables until shipment confirmation.

Key Columns

  • ORGANIZATION_ID — the inventory organization context for each row.
  • INVENTORY_ITEM_ID — the item identifier.
  • REVISION — item revision, where revision control applies.
  • LOT_NUMBER — lot designation; sourced directly from on-hand detail or via the lot temp table for transit rows.
  • COST_GROUP_ID — cost group classification for valuation-aware reporting.
  • CONTAINERIZED_FLAG — derived flag indicating whether the material is associated with an LPN or container.
  • SUBINVENTORY_CODE and LOCATOR_ID — physical stocking and locator placement.
  • QUANTITY — primary transaction quantity; positive for on-hand, negated for loaded/transit records.
  • SECONDARY_TRANSACTION_QUANTITY — the secondary unit of measure quantity, mirroring the same positive/negative convention. This is the column most relevant to searches involving secondary quantities, and it carries the identical sign behavior as the primary quantity.
  • QTY_TYPE — discriminator literal equal to 'ONHAND' or 'LOADED', enabling easy filtering and aggregation by source.

Common Use Cases and Queries

Typical usages include reconciling on-hand inventory against loaded-but-unshipped material, validating dispatch completion, and supplying net availability figures to external systems. A simple query filtering by item and organization illustrates the aggregation pattern:

  • SELECT inventory_item_id, organization_id, SUM(quantity) net_qty, SUM(secondary_transaction_quantity) net_secondary FROM apps.wms_onhand_and_loaded_qty_v WHERE organization_id = :org AND inventory_item_id = :item GROUP BY inventory_item_id, organization_id;
  • SELECT qty_type, SUM(quantity) FROM apps.wms_onhand_and_loaded_qty_v WHERE organization_id = :org GROUP BY qty_type; — separates positive on-hand from negative transit contributions.
  • SELECT subinventory_code, locator_id, secondary_transaction_quantity FROM apps.wms_onhand_and_loaded_qty_v WHERE qty_type = 'LOADED' AND inventory_item_id = :item; — inspects secondary quantities for in-transit loads.

Because transit rows are negative, consumers must be aware that a filtered query on QTY_TYPE = 'LOADED' alone returns negative values; summing without the QTY_TYPE filter is the intended usage for net position reporting.