View: OP_TRAN_TMP_V

Product:	GML - Process Manufacturing Logistics
Description:	View to populate Pick Lots screen from shipment form
Implementation/DBA Data:	APPS.OP_TRAN_TMP_V

View Text

SELECT T.SESSION_ID
, T.ITEM_ID
, T.WHSE_CODE
, MAX(T.LINE_ID) LINE_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) ONHAND_QTY
, SUM(T.LOCT_ONHAND2) ONHAND_QTY2
, SUM(-COMMIT_QTY) COMMIT_QTY
, SUM(-COMMIT_QTY2) COMMIT_QTY2
, SUM(T.LOCT_ONHAND) + SUM(COMMIT_QTY) AVAIL_QTY
, SUM(T.LOCT_ONHAND2) + SUM(COMMIT_QTY2) AVAIL_QTY2
, SUM(-ALLOC_QTY) QTY1
, DECODE(SUM(-ALLOC_QTY2)
, 0
, NULL
, SUM(-ALLOC_QTY2)) QTY2
, COUNT(*) NUMB_TRANS_LINE
FROM OP_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.ITEM_ID
, T.WHSE_CODE
, LOT_NO
, SUBLOT_NO
, T.LOT_ID
, LOT_CREATED
, EXPIRE_DATE
, QC_GRADE
, T.LOCATION
, I.LOT_STATUS
, NETTABLE_IND
, SHIPPING_IND
, REJECTED_IND HAVING SUM(T.LOCT_ONHAND) + SUM(COMMIT_QTY) > 0 OR SUM(-ALLOC_QTY) > 0 ORDER BY QTY1 DESC

Columns