Search Results alloc_qty2

Overview

GMI_PICK_LOTS_V is a read-only view owned by the APPS schema and registered as VALID in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the GMI (Process Manufacturing Inventory) product family and, per the ETRM metadata, exists "to populate pick lots screen from shipment form." In practice the view flattens the temporary transaction and inventory lot-status data that the Process Manufacturing shipping flow requires when an operator must choose specific lots and sub-lots to satisfy an outbound shipment line. Rather than exposing raw staging tables, it presents aggregated, filter-ready rows that carry identifying keys (SESSION_ID, TRANS_ID, LINE_ID, LINE_DETAIL_ID), lot attributes, and computed quantity columns. Because the view pre-aggregates on-hand, committed, and allocated quantities with a GROUP BY and restricts its output with a HAVING clause, it is equally useful for operational screens and for reporting on pickable inventory tied to shipment activity.

Underlying Base Objects

The documented view text joins three referenced objects, all exposed to APPS as synonyms:

• GMI_TRAN_TMP — the driving staging/reference table aliased T, supplying session, transaction, item, warehouse, line, lot, location, reason code, vendor lot, and the on-hand/allocated/committed quantity measures.
• IC_LOCT_INV — the location on-hand inventory table aliased I, joined on WHSE_CODE, ITEM_ID, LOT_ID, and LOCATION, and contributing LOT_STATUS.
• IC_LOTS_STS — the lot-status definition table aliased L, outer-joined on LOT_STATUS (the (+) operator) so that rows survive even when no status description exists.

An additional filter, EXPIRE_DATE >= SYSDATE, excludes lots past their expiry date, ensuring only currently pickable stock is returned. The grouping keys cover every non-aggregated column except the summed quantities, and the HAVING predicate retains rows where net on-hand plus committed quantity is at least 0.00001 or where allocated quantity is positive.

Key Columns

• SESSION_ID, TRANS_ID, LINE_ID, LINE_DETAIL_ID — contextual keys linking each row to the shipment/pick session and its detail lines.
• ITEM_ID, WHSE_CODE, LOCATION — item and warehousing identifiers, including the specific stock location.
• LOT_NO, SUBLOT_NO, LOT_ID, VENDOR_LOT_NO — lot and sub-lot identifiers, including the supplier lot reference.
• LOT_CREATED, EXPIRE_DATE, QC_GRADE — lot dating and quality attributes.
• LOT_STATUS — the numeric status from IC_LOCT_INV, decodable through IC_LOTS_STS.
• NETTABLE_IND, SHIPPING_IND, REJECTED_IND — flags governing whether a lot is nettable, shippable, or rejected. The searched term shipping_ind maps to this column, indicating lot eligibility for shipping.
• ONHAND_QTY, ONHAND_QTY2 — summed on-hand quantities in primary and secondary units.
• CONFIRM_QTY, CONFIRM_QTY2 — allocated quantities (sign-inverted via ALLOC_QTY) for confirmation.
• AVAIL_QTY, AVAIL_QTY2, QTY1, QTY2 — on-hand plus committed quantities, and additional quantity derivations.
• NUMB_TRANS_LINE, REASON_CODE — transaction line count and reason code.

Common Use Cases and Queries

Typical scenarios include validating which lots are eligible to ship for a given session, reconciling available versus allocated quantity, and reporting on shippable lots by status. A representative query lists pickable lots for a session:

• SELECT ITEM_ID, WHSE_CODE, LOT_NO, SUBLOT_NO, LOT_STATUS, SHIPPING_IND, ONHAND_QTY, AVAIL_QTY FROM APPS.GMI_PICK_LOTS_V WHERE SESSION_ID = :session_id AND WHSE_CODE = :whse_code ORDER BY LOT_NO, SUBLOT_NO;
• SELECT LOT_NO, SUM(AVAIL_QTY) FROM APPS.GMI_PICK_LOTS_V WHERE SHIPPING_IND = 'Y' AND WHSE_CODE = :whse_code GROUP BY LOT_NO;
• SELECT LOT_NO, LOT_STATUS, EXPIRE_DATE, NUMB_TRANS_LINE FROM APPS.GMI_PICK_LOTS_V WHERE TRANS_ID = :trans_id;

Because the view aggregates and filters dynamically, queries should always supply session, warehouse, or transaction predicates to control cost against GMI_TRAN_TMP.