View: PMIBV_LOT_LIST_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	Lot List View used by Lot Genealogy workbook
Implementation/DBA Data:	APPS.PMIBV_LOT_LIST_V

View Text

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

Columns