VIEW: APPS.IC_ITEM_INV_V

Source

View Text - Preformatted

SELECT l.item_id,l.lot_no,l.sublot_no, l.lot_id,s.lot_status,l.lot_created,l.expire_date,l.qc_grade, b.whse_code,b.location,b.loct_onhand,b.loct_onhand2,0,0 from ic_lots_mst l,ic_loct_inv b,ic_lots_sts s WHERE l.item_id = b.item_id and l.inactive_ind=0 and l.lot_id = b.lot_id and b.lot_status = s.lot_status(+) and NVL(s.order_proc_ind,1)=1 AND NVL(s.rejected_ind,0) = 0 and b.loct_onhand > 0 union all select t.item_id ,l.lot_no ,l.sublot_no ,t.lot_id ,t.lot_status ,l.lot_created ,l.expire_date ,l.qc_grade ,t.whse_code ,t.location ,0 ,0 ,t.trans_qty commit_qty ,t.trans_qty2 commit_qty2 from ic_lots_mst l ,ic_tran_pnd t ,ic_item_mst i where i.item_id = l.item_id and i.item_id = t.item_id and l.inactive_ind = 0 and t.lot_id = l.lot_id and t.delete_mark = 0 and t.completed_ind = 0 and t.trans_qty < 0

View Text - HTML Formatted

SELECT L.ITEM_ID
, L.LOT_NO
, L.SUBLOT_NO
, L.LOT_ID
, S.LOT_STATUS
, L.LOT_CREATED
, L.EXPIRE_DATE
, L.QC_GRADE
, B.WHSE_CODE
, B.LOCATION
, B.LOCT_ONHAND
, B.LOCT_ONHAND2
, 0
, 0
FROM IC_LOTS_MST L
, IC_LOCT_INV B
, IC_LOTS_STS S
WHERE L.ITEM_ID = B.ITEM_ID
AND L.INACTIVE_IND=0
AND L.LOT_ID = B.LOT_ID
AND B.LOT_STATUS = S.LOT_STATUS(+)
AND NVL(S.ORDER_PROC_IND
, 1)=1
AND NVL(S.REJECTED_IND
, 0) = 0
AND B.LOCT_ONHAND > 0 UNION ALL SELECT T.ITEM_ID
, L.LOT_NO
, L.SUBLOT_NO
, T.LOT_ID
, T.LOT_STATUS
, L.LOT_CREATED
, L.EXPIRE_DATE
, L.QC_GRADE
, T.WHSE_CODE
, T.LOCATION
, 0
, 0
, T.TRANS_QTY COMMIT_QTY
, T.TRANS_QTY2 COMMIT_QTY2
FROM IC_LOTS_MST L
, IC_TRAN_PND T
, IC_ITEM_MST I
WHERE I.ITEM_ID = L.ITEM_ID
AND I.ITEM_ID = T.ITEM_ID
AND L.INACTIVE_IND = 0
AND T.LOT_ID = L.LOT_ID
AND T.DELETE_MARK = 0
AND T.COMPLETED_IND = 0
AND T.TRANS_QTY < 0