Search Results commit_qty2




Overview
Explore in AI Chat

Overview

APPS.OP_TRAN_TMP_V is a reporting and integration view in Oracle EBS Release 12.1.1 and 12.2.2 that consolidates transaction staging data from the OP_TRAN_TMP table with on-hand inventory and lot status information. The view is owned by the APPS schema and is defined over three synonyms: OP_TRAN_TMP, IC_LOCT_INV, and IC_LOTS_STS. Its primary purpose is to present a summarized, availability-aware picture of inventory transactions as they relate to specific warehouse, item, lot, sublot, and locator combinations, scoped by a session identifier. It is closely associated with Oracle's inventory transaction manager and materials management modules, where staged transactions are assembled prior to validation and processing.

The view aggregates quantities at the lot and locator grain, applying sign conventions that invert committed and allocated quantities. Specifically, COMMIT_QTY is exposed as SUM(-COMMIT_QTY), meaning the stored staging value is negated when surfaced through the view. This same convention applies to COMMIT_QTY2, QTY1, and QTY2 (derived from ALLOC_QTY and ALLOC_QTY2).

Underlying Base Objects

The view is defined with the following FROM clause:

  • OP_TRAN_TMP (SYNONYM) — the transaction staging table (aliased as T), which supplies session, item, warehouse, lot, locator, on-hand, commit, and allocation quantities.
  • IC_LOCT_INV (SYNONYM) — the locator inventory table (aliased as I), which supplies lot status, nettable, shipping, and rejected indicators, joined on warehouse code, item, lot, and location.
  • IC_LOTS_STS (SYNONYM) — the lot status table (aliased as L), joined with an outer join (+) on lot status from IC_LOCT_INV, providing lot status attributes.

The join conditions enforce EXPIRE_DATE >= SYSDATE, so only non-expired lot inventory is returned. The GROUP BY clause consolidates rows by session, item, warehouse, lot, lot creation data, expiry, grade, location, and status flags.

Key Columns

Common Use Cases and Queries

A typical query retrieves committed quantity for a given staging session or item:

  • SELECT ITEM_ID, WHSE_CODE, LOT_NO, COMMIT_QTY, AVAIL_QTY FROM APPS.OP_TRAN_TMP_V WHERE SESSION_ID = :session_id;
  • SELECT ITEM_ID, SUM(COMMIT_QTY) FROM APPS.OP_TRAN_TMP_V WHERE WHSE_CODE = :whse GROUP BY ITEM_ID;
  • SELECT * FROM APPS.OP_TRAN_TMP_V WHERE COMMIT_QTY > 0 ORDER BY QTY1 DESC;

The view is used to reconcile reservation and commit activity during allocation, to verify available-to-promise quantities before picking, and to feed downstream integrations. The ORDER BY QTY1 DESC and HAVING clauses in the view definition ensure only rows with positive availability or allocation are returned.