Product: | PMI - Process Manufacturing Intelligence (Obsolete) |
---|---|
Description: | This view is used to access inventory events involving each lot. |
Implementation/DBA Data: | APPS.PMIFV_LOT_EVENT_V |
SELECT T.ITEM_ID PRODUCT_ITEM_ID
, T.LOT_ID PRODUCT_LOT_ID
, T.TRANS_DATE
, T.ORGN_CODE
, T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, SUM(T.TRANS_QTY) SUM_TRANS_QTY1
, SUM(T.TRANS_QTY2) SUM_TRANS_QTY2
, T.WHSE_CODE
, T.LOCATION
, T.QC_GRADE
, T.LOT_STATUS
, T.REASON_CODE
, T.COMPLETED_IND
FROM IC_TRAN_PND T
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE T.LOT_ID <> 0
AND ITEM.ITEM_ID = T.ITEM_ID
AND LOT.ITEM_ID = T.ITEM_ID
AND LOT.LOT_ID = T.LOT_ID GROUP BY T.ITEM_ID
, T.LOT_ID
, T.TRANS_DATE
, T.ORGN_CODE
, T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM_UM
, ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, T.WHSE_CODE
, T.LOCATION
, T.QC_GRADE
, T.LOT_STATUS
, T.REASON_CODE
, T.COMPLETED_IND UNION ALL SELECT T.ITEM_ID PRODUCT_ITEM_ID
, T.LOT_ID PRODUCT_LOT_ID
, T.TRANS_DATE
, T.ORGN_CODE
, T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, SUM(T.TRANS_QTY) SUM_TRANS_QTY1
, SUM(T.TRANS_QTY2) SUM_TRANS_QTY2
, T.WHSE_CODE
, T.LOCATION
, T.QC_GRADE
, T.LOT_STATUS
, T.REASON_CODE
, 1
FROM IC_TRAN_CMP T
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE T.LOT_ID <> 0
AND ITEM.ITEM_ID = T.ITEM_ID
AND LOT.ITEM_ID = T.ITEM_ID
AND LOT.LOT_ID = T.LOT_ID GROUP BY T.ITEM_ID
, T.LOT_ID
, T.TRANS_DATE
, T.ORGN_CODE
, T.DOC_TYPE
, T.DOC_ID
, T.LINE_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM_UM
, ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, T.WHSE_CODE
, T.LOCATION
, T.QC_GRADE
, T.LOT_STATUS
, T.REASON_CODE WITH READ ONLY