VIEW: APPS.PMIFV_LOT_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), l.qc_grade, t.orgn_code, o.orgn_name, vnd.vendor_number, vnd.vendor_name, vnd.vendor_id, ' ', ' ', 0, ' ', 0, rh.receipt_num FROM ic_tran_pnd t, ic_item_mst i, ic_lots_mst l, po_vendor_sites_all vs, po_vendors_view vnd, RCV_SHIPMENT_LINES rd, sy_orgn_mst o, RCV_SHIPMENT_HEADERS rh, RCV_TRANSACTIONS rcvtran WHERE l.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 = 'PORC' AND rd.shipment_header_id(+) = t.doc_id AND rcvtran.transaction_id(+) = t.line_id AND rcvtran.shipment_line_id = rd.shipment_line_id AND vs.vendor_site_id(+) = rh.vendor_site_id AND vs.vendor_id = vnd.vendor_id(+) AND o.orgn_code(+) = t.orgn_code AND rh.shipment_header_id(+) = rd.shipment_header_id GROUP BY 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), l.qc_grade, t.orgn_code, o.orgn_name, rh.receipt_num, vnd.vendor_number, vnd.vendor_name, vnd.vendor_id HAVING SUM(trans_qty) > 0 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), l.qc_grade, t.orgn_code, o.orgn_name, v.vendor_no, v.vendor_name, v.vendor_id, ' ', ' ', 0, ' ', 0, rh.recv_no FROM ic_tran_pnd t, ic_item_mst i, ic_lots_mst l, po_vend_mst v, po_recv_dtl rd, sy_orgn_mst o, po_recv_hdr rh WHERE l.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 AND o.orgn_code(+) = t.orgn_code AND rh.recv_id(+) = rd.recv_id GROUP BY 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), l.qc_grade, t.orgn_code, o.orgn_name, rh.recv_no, v.vendor_no, v.vendor_name, v.vendor_id HAVING SUM(trans_qty) > 0 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, o.orgn_name, v.vendor_no, v.vendor_name, v.vendor_id, b.batch_no, ' ', 0, ' ', 0, null FROM ic_tran_pnd t, ic_item_mst i, ic_lots_mst l, po_vend_mst v, sy_orgn_mst o, gme_batch_header b WHERE l.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 = 'PROD' AND v.vendor_id(+) = l.shipvend_id AND o.orgn_code(+) = t.orgn_code AND b.batch_id = t.doc_id GROUP BY 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), l.qc_grade, t.orgn_code, o.orgn_name, b.batch_no, v.vendor_no, v.vendor_name, v.vendor_id HAVING SUM(trans_qty) > 0 UNION ALL SELECT i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, l.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created) creation_date, l.qc_grade, TO_CHAR(NULL), TO_CHAR(NULL), v.vendor_no, v.vendor_name, v.vendor_id, ' ', ' ', 0, ' ', 0, TO_CHAR(NULL) FROM ic_item_mst i, ic_lots_mst l, po_vend_mst v WHERE l.item_id = i.item_id AND l.lot_id <> 0 AND v.vendor_id(+) = l.shipvend_id AND NOT EXISTS ( SELECT 'RECV OR PROD' FROM ic_tran_pnd t 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 t.doc_type IN ('RECV','PROD', 'PORC') GROUP BY t.item_id, t.lot_id, t.doc_id HAVING SUM(trans_qty) > 0 ) GROUP BY i.item_no, i.item_desc1, i.item_id, i.inv_class, i.inv_type, l.lot_id, l.lot_no, l.sublot_no, TRUNC(l.lot_created), l.qc_grade, v.vendor_no, v.vendor_name, v.vendor_id WITH READ ONLY

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)
, L.QC_GRADE
, T.ORGN_CODE
, O.ORGN_NAME
, VND.VENDOR_NUMBER
, VND.VENDOR_NAME
, VND.VENDOR_ID
, ' '
, ' '
, 0
, ' '
, 0
, RH.RECEIPT_NUM
FROM IC_TRAN_PND T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VENDOR_SITES_ALL VS
, PO_VENDORS_VIEW VND
, RCV_SHIPMENT_LINES RD
, SY_ORGN_MST O
, RCV_SHIPMENT_HEADERS RH
, RCV_TRANSACTIONS RCVTRAN
WHERE L.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 = 'PORC'
AND RD.SHIPMENT_HEADER_ID(+) = T.DOC_ID
AND RCVTRAN.TRANSACTION_ID(+) = T.LINE_ID
AND RCVTRAN.SHIPMENT_LINE_ID = RD.SHIPMENT_LINE_ID
AND VS.VENDOR_SITE_ID(+) = RH.VENDOR_SITE_ID
AND VS.VENDOR_ID = VND.VENDOR_ID(+)
AND O.ORGN_CODE(+) = T.ORGN_CODE
AND RH.SHIPMENT_HEADER_ID(+) = RD.SHIPMENT_HEADER_ID GROUP BY 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)
, L.QC_GRADE
, T.ORGN_CODE
, O.ORGN_NAME
, RH.RECEIPT_NUM
, VND.VENDOR_NUMBER
, VND.VENDOR_NAME
, VND.VENDOR_ID HAVING SUM(TRANS_QTY) > 0 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)
, L.QC_GRADE
, T.ORGN_CODE
, O.ORGN_NAME
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID
, ' '
, ' '
, 0
, ' '
, 0
, RH.RECV_NO
FROM IC_TRAN_PND T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
, PO_RECV_DTL RD
, SY_ORGN_MST O
, PO_RECV_HDR RH
WHERE L.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
AND O.ORGN_CODE(+) = T.ORGN_CODE
AND RH.RECV_ID(+) = RD.RECV_ID GROUP BY 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)
, L.QC_GRADE
, T.ORGN_CODE
, O.ORGN_NAME
, RH.RECV_NO
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID HAVING SUM(TRANS_QTY) > 0 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
, O.ORGN_NAME
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID
, B.BATCH_NO
, ' '
, 0
, ' '
, 0
, NULL
FROM IC_TRAN_PND T
, IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
, SY_ORGN_MST O
, GME_BATCH_HEADER B
WHERE L.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 = 'PROD'
AND V.VENDOR_ID(+) = L.SHIPVEND_ID
AND O.ORGN_CODE(+) = T.ORGN_CODE
AND B.BATCH_ID = T.DOC_ID GROUP BY 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)
, L.QC_GRADE
, T.ORGN_CODE
, O.ORGN_NAME
, B.BATCH_NO
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID HAVING SUM(TRANS_QTY) > 0 UNION ALL SELECT I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, L.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED) CREATION_DATE
, L.QC_GRADE
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID
, ' '
, ' '
, 0
, ' '
, 0
, TO_CHAR(NULL)
FROM IC_ITEM_MST I
, IC_LOTS_MST L
, PO_VEND_MST V
WHERE L.ITEM_ID = I.ITEM_ID
AND L.LOT_ID <> 0
AND V.VENDOR_ID(+) = L.SHIPVEND_ID
AND NOT EXISTS ( SELECT 'RECV OR PROD'
FROM IC_TRAN_PND T
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 T.DOC_TYPE IN ('RECV'
, 'PROD'
, 'PORC') GROUP BY T.ITEM_ID
, T.LOT_ID
, T.DOC_ID HAVING SUM(TRANS_QTY) > 0 ) GROUP BY I.ITEM_NO
, I.ITEM_DESC1
, I.ITEM_ID
, I.INV_CLASS
, I.INV_TYPE
, L.LOT_ID
, L.LOT_NO
, L.SUBLOT_NO
, TRUNC(L.LOT_CREATED)
, L.QC_GRADE
, V.VENDOR_NO
, V.VENDOR_NAME
, V.VENDOR_ID WITH READ ONLY