Search Results mtl_negative_view

Overview

MTL_NEGATIVE_VIEW is a read-only dictionary-style view owned by the APPS schema in Oracle E-Business Suite (documented as VALID under release 12.2.2, and applicable to 12.1.1). It belongs to the Inventory (INV) product and is defined over the on-hand quantities detail data. As its description states, the view presents a listing of on-hand records whose aggregated primary transaction quantity is negative.

Unlike a physical table, the view stores no data of its own; it derives its results dynamically from the underlying transaction detail each time it is queried. Its role in Oracle EBS reporting and integration is to isolate the subset of on-hand balances that have driven inventory below zero. Because negative on-hand is generally treated as an exception condition within Inventory, this view provides a convenient, self-contained data source for exception reporting, reconciliation, data-quality monitoring, and downstream extracts. It allows DBAs, support analysts, and reporting tools to identify negatively-positioned inventory without writing the grouping and filtering logic themselves.

Underlying Base Objects

Per the documented ETRM metadata, MTL_NEGATIVE_VIEW references a single base object, exposed through a synonym: MTL_ONHAND_QUANTITIES_DETAIL. This table is the transactional on-hand detail store that records individual quantity movements and balances for each on-hand position. The view aggregates rows from this detail table using SUM(PRIMARY_TRANSACTION_QUANTITY), grouped by organization, item, revision, subinventory, locator, and lot number, and retains only those groups whose summed quantity falls below zero via a HAVING clause.

In practice, MTL_ONHAND_QUANTITIES_DETAIL itself summarizes transactions against MTL_ONHAND_QUANTITIES, and both ultimately trace back to material transactions. However, the documented definition of MTL_NEGATIVE_VIEW is limited to its reference to MTL_ONHAND_QUANTITIES_DETAIL, and that reference is the definitive base object for the view's logic. Because the view is owned by APPS and defined over a shared inventory table, access should be granted through the standard APPS synonym rather than by querying any base object directly.

Key Columns

• ORGANIZATION_ID — Identifies the inventory organization in which the negative on-hand position exists.
• INVENTORY_ITEM_ID — The internal identifier of the affected inventory item.
• REVISION — The item revision at which the negative balance occurs, where revision control applies.
• SUBINVENTORY_CODE — The subinventory holding the negative quantity.
• LOCATOR_ID — The specific locator (storage position) associated with the negative position.
• LOT_NUMBER — The lot or batch number of the affected material, where lot control applies.
• QUANTITY — The summed primary transaction quantity for the grouped position, always returned as a negative value because of the HAVING condition.

Because the grain is the full combination of organization, item, revision, subinventory, locator, and lot, each row corresponds to a uniquely identifiable on-hand position that is in a negative state.

Common Use Cases and Queries

The primary use case is exception reporting: locating negative on-hand balances for investigation and correction. A typical query retrieves all negative positions for an organization, optionally joining to item and organization views for descriptive context:

SELECT organization_id, inventory_item_id, subinventory_code, quantity
FROM apps.mtl_negative_view
WHERE organization_id = :p_org_id
ORDER BY inventory_item_id, subinventory_code;

Analysts may aggregate at the item level to quantify the total negative exposure per item:

SELECT inventory_item_id, SUM(quantity) total_negative
FROM apps.mtl_negative_view
WHERE organization_id = :p_org_id
GROUP BY inventory_item_id;

Additional scenarios include feeding downstream reconciliation extracts, monitoring data quality after data conversion or interface loads, and supporting cycle-count or inventory-accuracy investigations. Because rows are grouped only by the documented six dimensions, results should be interpreted at the subinventory/locator/lot level rather than as a single organization-wide total. Query performance is governed by the underlying MTL_ONHAND_QUANTITIES_DETAIL aggregation; filtering by ORGANIZATION_ID is recommended.