VIEW: APPS.GMI_LOTS_LIST_V

Source

View Text - Preformatted

SELECT i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, t.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created) creation_date, l.qc_grade, t.orgn_code, v.vendor_no FROM ic_tran_pnd t, ic_item_mst i, ic_lots_mst l, po_vend_mst v, po_recv_dtl rd WHERE t.item_id = i.item_id AND t.completed_ind = 1 AND l.item_id = t.item_id AND l.lot_id = t.lot_id AND l.lot_id <> 0 AND t.doc_type = 'RECV' AND rd.recv_id(+) = t.doc_id AND rd.line_id(+) = t.line_id AND v.vendor_id(+) = rd.shipvend_id UNION ALL SELECT i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, t.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created) creation_date, l.qc_grade, t.orgn_code, v.vendor_no FROM ic_tran_cmp t, ic_item_mst i, ic_lots_mst l, po_vend_mst v, po_recv_dtl rd WHERE t.item_id = i.item_id AND l.item_id = t.item_id AND l.lot_id = t.lot_id AND l.lot_id <> 0 AND t.doc_type = 'RECV' AND rd.recv_id(+) = t.doc_id AND rd.line_id(+) = t.line_id AND v.vendor_id(+) = rd.shipvend_id UNION ALL SELECT i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, t.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created) creation_date, l.qc_grade, t.orgn_code, v.vendor_no FROM ic_tran_pnd t, ic_item_mst i, ic_lots_mst l, po_vend_mst v WHERE t.item_id = i.item_id AND t.completed_ind = 1 AND l.item_id = t.item_id AND l.lot_id = t.lot_id AND l.lot_id <> 0 AND t.doc_type <> 'RECV' AND v.vendor_id(+) = l.shipvend_id UNION ALL SELECT i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, t.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created) creation_date, l.qc_grade, t.orgn_code, v.vendor_no FROM ic_tran_cmp t, ic_item_mst i, ic_lots_mst l, po_vend_mst v WHERE t.item_id = i.item_id AND l.item_id = t.item_id AND l.lot_id = t.lot_id AND l.lot_id <> 0 AND t.doc_type <> 'RECV' AND v.vendor_id(+) = l.shipvend_id

View Text - HTML Formatted

SELECT I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, T.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED) CREATION_DATE
, L.QC_GRADE
, T.ORGN_CODE
, V.VENDOR_NO
FROM IC_TRAN_PND T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
, PO_RECV_DTL RD
WHERE T.ITEM_ID = I.ITEM_ID
AND T.COMPLETED_IND = 1
AND L.ITEM_ID = T.ITEM_ID
AND L.LOT_ID = T.LOT_ID
AND L.LOT_ID <> 0
AND T.DOC_TYPE = 'RECV'
AND RD.RECV_ID(+) = T.DOC_ID
AND RD.LINE_ID(+) = T.LINE_ID
AND V.VENDOR_ID(+) = RD.SHIPVEND_ID UNION ALL SELECT I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, T.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED) CREATION_DATE
, L.QC_GRADE
, T.ORGN_CODE
, V.VENDOR_NO
FROM IC_TRAN_CMP T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
, PO_RECV_DTL RD
WHERE T.ITEM_ID = I.ITEM_ID
AND L.ITEM_ID = T.ITEM_ID
AND L.LOT_ID = T.LOT_ID
AND L.LOT_ID <> 0
AND T.DOC_TYPE = 'RECV'
AND RD.RECV_ID(+) = T.DOC_ID
AND RD.LINE_ID(+) = T.LINE_ID
AND V.VENDOR_ID(+) = RD.SHIPVEND_ID UNION ALL SELECT I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, T.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED) CREATION_DATE
, L.QC_GRADE
, T.ORGN_CODE
, V.VENDOR_NO
FROM IC_TRAN_PND T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
WHERE T.ITEM_ID = I.ITEM_ID
AND T.COMPLETED_IND = 1
AND L.ITEM_ID = T.ITEM_ID
AND L.LOT_ID = T.LOT_ID
AND L.LOT_ID <> 0
AND T.DOC_TYPE <> 'RECV'
AND V.VENDOR_ID(+) = L.SHIPVEND_ID UNION ALL SELECT I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, T.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED) CREATION_DATE
, L.QC_GRADE
, T.ORGN_CODE
, V.VENDOR_NO
FROM IC_TRAN_CMP T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
WHERE T.ITEM_ID = I.ITEM_ID
AND L.ITEM_ID = T.ITEM_ID
AND L.LOT_ID = T.LOT_ID
AND L.LOT_ID <> 0
AND T.DOC_TYPE <> 'RECV'
AND V.VENDOR_ID(+) = L.SHIPVEND_ID