Search Results op_tran_tmp_v




Overview
Explore in AI Chat

Overview

OP_TRAN_TMP_V is a PL/SQL view owned by the APPS schema within the GML — Process Manufacturing Logistics product family. It carries a VALID status in both Oracle E-Business Suite 12.1.1 and 12.2.2. The view's documented purpose is to populate the Pick Lots screen that is invoked from the shipment form, meaning it supplies the set of lot, sublot, and locator combinations available for selection when a user is picking material against a shipment. Rather than serving as a persistent reporting entity, it functions as an operational data source that shapes the user interface at the moment of pick confirmation; the SESSION_ID column confirms that its content is scoped to the current form session. Because it aggregates inventory transaction staging rows and joins them to on-hand and lot-status data, it is also frequently referenced in ad hoc reporting and integration queries that need pick-eligible inventory by lot.

Underlying Base Objects

The view is defined over three base objects, all accessed through APPS synonyms:

  • OP_TRAN_TMP — the driving table (aliased T), holding session-scoped transaction staging rows with item, warehouse, lot, location, on-hand, commit, and allocation quantities.
  • IC_LOCT_INV — the locator-level inventory table (aliased I), joined on warehouse, item, lot, and location to supply LOT_STATUS and lot attributes.
  • IC_LOTS_STS — the lot status definition table (aliased L), outer-joined on LOT_STATUS to translate the status code into its descriptive attributes.

Rows are restricted to lots whose EXPIRE_DATE is on or after the current SYSDATE, and the result set is filtered so that only lots with net available quantity (on-hand plus commitments) greater than zero, or with outstanding allocation quantity, are returned. Aggregation is grouped by session, item, warehouse, lot, sublot, locator, and the status and indicator columns, then ordered by allocation quantity descending so the most committed lots appear first.

Key Columns

  • SESSION_ID / LINE_ID — identify the originating form session and source transaction line; LINE_ID is the maximum line for the grouped lot.
  • ITEM_ID, WHSE_CODE, LOT_NO, SUBLOT_NO, LOT_ID, LOCATION — the inventory key identifying the exact stock position.
  • LOT_CREATED, EXPIRE_DATE, QC_GRADE — lot lifecycle and quality attributes used for selection and sorting.
  • LOT_STATUS — the status code from IC_LOCT_INV, resolvable through IC_LOTS_STS.
  • NETTABLE_IND, SHIPPING_IND, REJECTED_IND — status indicators carried through from the underlying lot status record. REJECTED_IND is the flag most commonly searched in this context, indicating lots placed in a rejected status; users filter on it to isolate or exclude rejected material from picking.
  • ONHAND_QTY, COMMIT_QTY, AVAIL_QTY — primary and secondary on-hand, committed, and available quantities (the *_QTY2 columns represent the secondary unit of measure).
  • QTY1 (SUM of ALLOC_QTY, negated), QTY2, NUMB_TRANS_LINE — allocated quantity and a count of contributing transaction lines.

Common Use Cases and Queries

The primary use case is the Pick Lots screen, which reads this view for the current session. Reporting and integration scenarios include listing available lots for an item, auditing rejected lots, and reconciling allocations. A typical query filtering on the searched indicator is:

  • SELECT item_id, lot_no, sublot_no, location, lot_status, rejected_ind, onhand_qty, avail_qty FROM apps.op_tran_tmp_v WHERE rejected_ind = 'Y' ORDER BY avail_qty DESC;
  • Restricting by warehouse and item: WHERE whse_code = :p_whse AND item_id = :p_item.
  • Isolating pickable stock: filtering avail_qty > 0 and rejected_ind = 'N'.

Because the query references SYSDATE and is session-scoped, callers should expect results to vary with the active session and to exclude expired lots automatically.