Search Results alloc_qty2




Overview
Explore in AI Chat

Overview

GMI_PICK_LOTS_V is a public Oracle E-Business Suite view owned by the APPS schema, registered under the FND design data path GMI.GMI_PICK_LOTS_V, and shipped with a VALID status in both release 12.1.1 and 12.2.2. Its documented purpose is to populate the Pick Lots screen invoked from the shipment form in Oracle Process Manufacturing. The view exposes, for a given user session and reservation context, the set of lot and sublot combinations that hold available inventory, together with the quantities required to support picking decisions. Because it is a public view, Oracle documents it as suitable for custom reporting and other read-only data requirements.

The view is a display and reporting construct rather than a transactional entity. It denormalizes lot master attributes, on-hand balances, reservation detail, and lot status control flags into a single row per lot/sublot reservation line, keyed in part by the user's session identifier. This makes it a convenient single source for lot selection screens, shipment-related inquiries, and custom extensions that must reconcile on-hand, committed, and allocated quantities per lot.

Underlying Base Objects

The documented referenced base objects for the 12.2.2 metadata are three synonyms that resolve to OPM base tables: GMI_TRAN_TMP, IC_LOCT_INV, and IC_LOTS_STS.

  • GMI_TRAN_TMP — The transaction temporary table, which holds the working reservation and transaction context for the current session. It supplies SESSION_ID, TRANS_ID, and the order line and line detail identifiers that scope each row to a specific picking operation.
  • IC_LOCT_INV — The lot location inventory table, which records inventory balances by lot, sublot, warehouse, and location. It provides the on-hand, committed, and allocated quantity measures (ONHAND_QTY, CONFIRM_QTY, AVAIL_QTY, QTY1, and their dual-unit counterparts) plus WHSE_CODE and LOCATION.
  • IC_LOTS_STS — The lot status table, which defines status codes and their associated control flags. It supplies LOT_STATUS, along with the NETTABLE_IND, SHIPPING_IND, and REJECTED_IND indicators that determine whether a lot may be picked and shipped.

Lot master attributes such as LOT_NO, SUBLOT_NO, LOT_ID, LOT_CREATED, EXPIRE_DATE, and QC_GRADE are joined in so that the pick screen can present identifying and quality information alongside the quantities.

Key Columns

  • SESSION_ID, TRANS_ID, LINE_ID, LINE_DETAIL_ID — Session and reservation context linking each row to the transaction working temporary record.
  • ITEM_ID, WHSE_CODE, LOCATION — Item and physical inventory location of the lot/sublot.
  • LOT_NO, SUBLOT_NO, LOT_ID — Lot and sublot identifiers, including the surrogate LOT_ID.
  • LOT_CREATED, EXPIRE_DATE, QC_GRADE — Lot creation date, expiration date, and quality grade.
  • LOT_STATUS, NETTABLE_IND, SHIPPING_IND, REJECTED_IND — Status code and control flags. A lot is shippable only when it is nettable, flagged as available for shipping, and not rejected.
  • ONHAND_QTY, ONHAND_QTY2 — Total quantity on hand in the primary and secondary item units of measure.
  • CONFIRM_QTY, CONFIRM_QTY2 — The view's central answer to the "confirm_qty" search. The ETRM comment defines it as the total committed quantity of inventory for the lot/sublot in item_um1, with CONFIRM_QTY2 expressing the same committed quantity in item_um2.
  • AVAIL_QTY, AVAIL_QTY2 — On-hand quantity plus committed quantity in each unit of measure.
  • QTY1, QTY2 — Total quantity allocated to the lot/sublot for the reservation.

Common Use Cases and Queries

Custom reports built on GMI_PICK_LOTS_V typically retrieve the candidate lots for reservation, compare available against committed and allocated quantities, and filter out lots that are rejected or blocked from shipping. A representative query follows.

  • Displaying committed and available quantities per lot for a session: 
    SELECT item_id, whse_code, lot_no, sublot_no,
       onhand_qty, confirm_qty, avail_qty
  FROM apps.gmi_pick_lots_v
 WHERE session_id = :p_session_id
   AND shippable_flag_check = 1;
  • Listing only shippable lots, applying the documented status rules: 
    SELECT lot_no, sublot_no, lot_status, avail_qty
  FROM apps.gmi_pick_lots_v
 WHERE nettable_ind = 1
   AND shipping_ind = 1
   AND rejected_ind = 0;
  • Reviewing committed quantity by item and warehouse for a transaction: 
    SELECT trans_id, item_id, whse_code,
       SUM(confirm_qty) committed_um1,
       SUM(confirm_qty2) committed_um2
  FROM apps.gmi_pick_lots_v
 WHERE trans_id = :p_trans_id
 GROUP BY trans_id, item_id, whse_code;

Because the view is scoped by SESSION_ID, custom code should always filter on the appropriate session or transaction key. Where exact column behavior is uncertain, the corresponding IC_LOCT_INV and GMI_TRAN_TMP records should be interrogated directly.