View: GMI_LOTS_DEST_V

Product:	GMI - Process Manufacturing Inventory
Description:	This view is used to populate the Where Used tab of the Lot Genealogy Inquiry when the navigation mode is Where Used
Implementation/DBA Data:	APPS.GMI_LOTS_DEST_V

View Text

SELECT PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, PRODUCT.TRANS_DATE
, PRODUCT.LINE_TYPE
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
, ITEM.ITEM_NO PRODUCT_ITEM_NO
, ITEM.ITEM_DESC1 PRODUCT_ITEM_DESC1
, ITEM.ITEM_UM PRODUCT_ITEM_UM
, ITEM.ITEM_UM2 PRODUCT_ITEM_UM2
, ITEM.INV_TYPE
, ITEM.INV_CLASS
, LOT.LOT_NO PRODUCT_LOT_NO
, LOT.SUBLOT_NO PRODUCT_SUBLOT_NO
, SUM(PRODUCT.TRANS_QTY) SUM_TRANS_QTY1
, SUM(PRODUCT.TRANS_QTY2) SUM_TRANS_QTY2
, PRODUCT.WHSE_CODE
, PRODUCT.LOCATION
, PRODUCT.QC_GRADE
, PRODUCT.LOT_STATUS
FROM IC_TRAN_PND PRODUCT
, ( SELECT ITEM_ID
, LOT_ID
, DOC_ID
FROM IC_TRAN_PND
WHERE DOC_TYPE = 'PROD'
AND COMPLETED_IND = 1
AND LINE_TYPE = -1 GROUP BY ITEM_ID
, LOT_ID
, DOC_ID HAVING SUM(TRANS_QTY) <> 0 ) INGRED
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE PRODUCT.DOC_TYPE = 'PROD'
AND PRODUCT.LINE_TYPE IN (1
, 2)
AND PRODUCT.COMPLETED_IND = 1
AND PRODUCT.LOT_ID <> 0
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.LOT_ID <> PRODUCT.LOT_ID
AND ITEM.ITEM_ID = PRODUCT.ITEM_ID
AND LOT.ITEM_ID = PRODUCT.ITEM_ID
AND LOT.LOT_ID = PRODUCT.LOT_ID
AND PRODUCT.DOC_ID IN (SELECT BATCH_ID
FROM PM_BTCH_HDR PM
, FM_FORM_MST FM
WHERE PRODUCT.DOC_ID = PM.BATCH_ID
AND PM.FORMULA_ID=FM.FORMULA_ID) GROUP BY PRODUCT.ITEM_ID
, PRODUCT.LOT_ID
, PRODUCT.TRANS_DATE
, PRODUCT.LINE_TYPE
, INGRED.ITEM_ID
, INGRED.LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, ITEM.INV_TYPE
, ITEM.INV_CLASS
, LOT.LOT_NO
, LOT.SUBLOT_NO
, PRODUCT.WHSE_CODE
, PRODUCT.LOCATION
, PRODUCT.QC_GRADE
, PRODUCT.LOT_STATUS HAVING SUM(PRODUCT.TRANS_QTY) <> 0 UNION ALL SELECT PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, PRODUCT.TRANS_DATE
, PRODUCT.LINE_TYPE
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
, ITEM.ITEM_NO PRODUCT_ITEM_NO
, ITEM.ITEM_DESC1 PRODUCT_ITEM_DESC1
, ITEM.ITEM_UM PRODUCT_ITEM_UM
, ITEM.ITEM_UM2 PRODUCT_ITEM_UM2
, ITEM.INV_TYPE
, ITEM.INV_CLASS
, LOT.LOT_NO PRODUCT_LOT_NO
, LOT.SUBLOT_NO PRODUCT_SUBLOT_NO
, SUM(PRODUCT.TRANS_QTY) SUM_TRANS_QTY1
, SUM(PRODUCT.TRANS_QTY2) SUM_TRANS_QTY2
, PRODUCT.WHSE_CODE
, PRODUCT.LOCATION
, PRODUCT.QC_GRADE
, PRODUCT.LOT_STATUS
FROM IC_TRAN_CMP PRODUCT
, ( SELECT ITEM_ID
, LOT_ID
, DOC_ID
FROM IC_TRAN_CMP
WHERE DOC_TYPE = 'PROD'
AND LINE_TYPE = -1 GROUP BY ITEM_ID
, LOT_ID
, DOC_ID HAVING SUM(TRANS_QTY) <> 0 ) INGRED
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE PRODUCT.DOC_TYPE = 'PROD'
AND PRODUCT.LINE_TYPE IN (1
, 2)
AND PRODUCT.LOT_ID <> 0
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.LOT_ID <> PRODUCT.LOT_ID
AND ITEM.ITEM_ID = PRODUCT.ITEM_ID
AND LOT.ITEM_ID = PRODUCT.ITEM_ID
AND LOT.LOT_ID = PRODUCT.LOT_ID
AND PRODUCT.DOC_ID IN (SELECT BATCH_ID
FROM PM_BTCH_HDR PM
, FM_FORM_MST FM
WHERE PRODUCT.DOC_ID = PM.BATCH_ID
AND PM.FORMULA_ID=FM.FORMULA_ID) GROUP BY PRODUCT.ITEM_ID
, PRODUCT.LOT_ID
, PRODUCT.TRANS_DATE
, PRODUCT.LINE_TYPE
, INGRED.ITEM_ID
, INGRED.LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, ITEM.INV_TYPE
, ITEM.INV_CLASS
, LOT.LOT_NO
, LOT.SUBLOT_NO
, PRODUCT.WHSE_CODE
, PRODUCT.LOCATION
, PRODUCT.QC_GRADE
, PRODUCT.LOT_STATUS HAVING SUM(PRODUCT.TRANS_QTY) <> 0

Columns