View: GMI_PICK_LOTS_V

Product:	GMI - Process Manufacturing Inventory
Description:	View to populate pick lots screen from shipment form
Implementation/DBA Data:	APPS.GMI_PICK_LOTS_V

View Text

SELECT T.SESSION_ID
, T.TRANS_ID
, T.ITEM_ID
, T.WHSE_CODE
, T.LINE_ID
, T.LINE_DETAIL_ID
, LOT_NO
, SUBLOT_NO
, T.LOT_ID
, LOT_CREATED
, EXPIRE_DATE
, QC_GRADE
, T.LOCATION
, I.LOT_STATUS
, NETTABLE_IND
, SHIPPING_IND
, REJECTED_IND
, SUM(T.LOCT_ONHAND)
, SUM(T.LOCT_ONHAND2)
, DECODE(TRANS_ID
, 0
, 0
, SUM((-1)*ALLOC_QTY))
, DECODE(TRANS_ID
, 0
, 0
, DECODE(SUM((-1)*ALLOC_QTY2)
, 0
, NULL
, SUM((-1)*ALLOC_QTY2)))
, SUM(T.LOCT_ONHAND) + SUM(COMMIT_QTY)
, SUM(T.LOCT_ONHAND2) + SUM(COMMIT_QTY2)
, SUM((-1)*ALLOC_QTY)
, DECODE(SUM((-1)*ALLOC_QTY2)
, 0
, NULL
, SUM((-1)*ALLOC_QTY2))
, COUNT(*) NUMB_TRANS_LINE
, T.REASON_CODE
, T.VENDOR_LOT_NO
FROM GMI_TRAN_TMP T
, IC_LOCT_INV I
, IC_LOTS_STS L
WHERE T.WHSE_CODE = I.WHSE_CODE
AND T.ITEM_ID = I.ITEM_ID
AND EXPIRE_DATE >= SYSDATE
AND I.LOT_ID = T.LOT_ID
AND I.LOCATION = T.LOCATION
AND I.LOT_STATUS = L.LOT_STATUS (+) GROUP BY T.SESSION_ID
, T.TRANS_ID
, T.ITEM_ID
, T.VENDOR_LOT_NO
, T.WHSE_CODE
, T.LINE_ID
, T.LINE_DETAIL_ID
, LOT_NO
, SUBLOT_NO
, T.LOT_ID
, LOT_CREATED
, EXPIRE_DATE
, QC_GRADE
, T.LOCATION
, I.LOT_STATUS
, NETTABLE_IND
, SHIPPING_IND
, REJECTED_IND
, T.REASON_CODE HAVING SUM(T.LOCT_ONHAND) + SUM(COMMIT_QTY) >= 0.00001 OR SUM((-1)*ALLOC_QTY) > 0

Columns