VIEW: APPS.GMI_PICK_LOTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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