Results for “mtl_onhand_quantity_temp”

19 results




Overview
Explore in AI Chat

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_ONHAND_QUANTITY_TEMP is a temporary (staging) table owned by the INV schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. Its documented purpose is to support on-hand quantity queries by holding an intermediate result set of on-hand balances, typically for a single session or a bounded transaction context. Rather than acting as a permanent transactional store such as MTL_ONHAND_QUANTITIES_DETAIL, this table functions as a working area into which on-hand rows are populated, filtered, joined, or aggregated before being returned to a calling form, concurrent program, or report.

Because the table carries the standard EBS audit columns and a set of generic typed columns, it is transient in nature. Rows are not intended to represent long-lived business facts. Under a heuristic Data Vault classification mined from its foreign-key structure, MTL_ONHAND_QUANTITY_TEMP is best modeled as a link suggestion: it associates an inventory item, an organization, a subinventory, and a locator, and carries a measured quantity. It is not a hub (no single, durable business key independent of context) and not a true satellite (it holds volatile, session-scoped measures rather than descriptive history).

Key Information Stored

The table contains 31 documented columns. The most significant for interpretation and joins are:

  • SEQ_NUM — a sequence/ordinal value; functions as a surrogate identifier for rows within a given population pass.
  • ORGANIZATION_ID — the inventory organization that scopes every on-hand row; a mandatory business-key component.
  • INVENTORY_ITEM_ID — the item whose on-hand quantity is being reported; part of the business key.
  • SUBINVENTORY — the subinventory code within the organization.
  • LOCATOR_ID — the stock locator (row/rack/bin) for the balance.
  • QUANTITY — the on-hand quantity value for the item at that location.
  • REVISION — the item revision, where revision control applies.
  • LOT_NUMBER — the lot for lot-controlled items.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns, useful for identifying when a staging pass populated the table.
  • C_COLUMN1–C_COLUMN8, N_COLUMN1–N_COLUMN5, D_COLUMN1–D_COLUMN5 — generic character, numeric, and date flex columns reserved for caller-specific selection or grouping criteria.

A practical business-key candidate is the combination of ORGANIZATION_ID, INVENTORY_ITEM_ID, SUBINVENTORY, and LOCATOR_ID (with REVISION and LOT_NUMBER where applicable); no surrogate primary-key constraint is documented beyond SEQ_NUM.

Common Use Cases and Queries

Typical usage involves populating the table for a specific item/organization context, then querying it. A representative pattern joins the staging rows back to the master tables:

  • Item availability lookup — filter by INVENTORY_ITEM_ID and ORGANIZATION_ID to return quantities by SUBINVENTORY and LOCATOR_ID.
  • Location-based reporting — join LOCATOR_ID to MTL_ITEM_LOCATIONS to present human-readable bin names.
  • Subinventory roll-up — aggregate QUANTITY by SUBINVENTORY after joining to MTL_SECONDARY_INVENTORIES.
  • Lot and revision drill-down — group by LOT_NUMBER and REVISION to reconcile detailed balances.

A sample query might select ITEM.ITEM_NUMBER, T.SUBINVENTORY, T.LOCATOR_ID, T.LOT_NUMBER, T.QUANTITY from MTL_ONHAND_QUANTITY_TEMP T join MTL_SYSTEM_ITEMS_B ITEM on ITEM.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID and ITEM.ORGANIZATION_ID = T.ORGANIZATION_ID, filtered on T.ORGANIZATION_ID. Because the table is temporary, reports should treat its contents as session-scoped and re-populate as needed.

Related Objects

The foreign-key metadata documents three direct parent relationships, joined on ORGANIZATION_ID in each case:

  • MTL_ITEM_LOCATIONS — joined via LOCATOR_ID and ORGANIZATION_ID, resolves bin-level detail.
  • MTL_SECONDARY_INVENTORIES — joined via SUBINVENTORY and ORGANIZATION_ID, provides subinventory attributes.
  • MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID and ORGANIZATION_ID, supplies item master descriptions.

Related analytic objects in the same functional area include MTL_ONHAND_QUANTITIES_DETAIL, MTL_ONHAND_QUANTITIES (the on-hand quantity view), and the Inventory on-hand inquiry APIs that consume staging results. These complement the temporary table but are not joined by documented foreign keys.