Search Results primary_transaction_quantity




Overview
Explore in AI Chat

Overview

APPS.MTL_NEGATIVE_VIEW is a reporting view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that isolates inventory balances which have fallen below zero. It is defined over the on-hand quantities detail table and aggregates transaction quantities at the granularity of organization, item, revision, subinventory, locator, and lot. The defining characteristic of the view is its HAVING clause, which filters the aggregated result set to only those combinations where the summed primary transaction quantity is less than zero. In effect, the view returns a list of every inventory position that currently carries a negative on-hand balance.

Because negative on-hand quantities are an exception condition in Oracle Inventory, this view is typically used as a diagnostic and monitoring artifact rather than as a transactional source. It supports reconciliation, data-fix, and audit activities across the supply chain modules.

Underlying Base Objects

The view is owned by APPS and is defined over a single documented base object: MTL_ONHAND_QUANTITIES_DETAIL, accessed through a synonym. MTL_ONHAND_QUANTITIES_DETAIL is the transactional detail table that stores individual on-hand quantity records, including the primary transaction quantity that drives the view's aggregation. The view does not introduce any joins or outer tables; it is a straightforward aggregation and filter over that detail table. Consequently, the view reflects the raw state of on-hand data and inherits the performance and consistency characteristics of the underlying table.

Key Columns

  • ORGANIZATION_ID — The inventory organization under which the balance is held; the leading key in the grouping.
  • INVENTORY_ITEM_ID — The unique identifier of the inventory item with the negative balance.
  • REVISION — The item revision, where revision control applies.
  • SUBINVENTORY_CODE — The subinventory in which the negative quantity is recorded.
  • LOCATOR_ID — The specific stock locator within the subinventory.
  • LOT_NUMBER — The lot associated with the balance, for lot-controlled items.
  • QUANTITY — The aggregated SUM(PRIMARY_TRANSACTION_QUANTITY) for the grouped combination, restricted by the HAVING clause to values strictly less than zero.

The QUANTITY column is the primary output and is a direct alias of the sum of PRIMARY_TRANSACTION_QUANTITY, making that underlying column the natural subject of user searches and investigative queries.

Common Use Cases and Queries

The most common use is exception reporting: identifying which item, subinventory, locator, and lot combinations are negative so that receiving, issuing, or transfer discrepancies can be corrected. A basic query retrieving all negative positions is:

  • SELECT organization_id, inventory_item_id, revision, subinventory_code, locator_id, lot_number, quantity FROM apps.mtl_negative_view ORDER BY organization_id, inventory_item_id;

To narrow the result to a specific organization or item, standard predicates on ORGANIZATION_ID and INVENTORY_ITEM_ID are applied. Because the view already returns only negative sums, no additional HAVING filter is required at query time. Common operational scenarios include validating the outcome of a period-close on-hand snapshot, tracing the source of a negative balance reported in inventory valuation, and feeding exception dashboards or reconciliation scripts. When the negative positions are identified, corrective action typically involves adjustments or back-dated transactions against MTL_ONHAND_QUANTITIES_DETAIL, after which re-querying the view confirms resolution.