View: GMI_LOTS_COMP_V

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

View Text

SELECT PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, INGRED.TRANS_DATE
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, SUM(INGRED.TRANS_QTY)*(-1) SUM_TRANS_QTY1
, SUM(INGRED.TRANS_QTY2)*(-1) SUM_TRANS_QTY2
, INGRED.WHSE_CODE
, INGRED.LOCATION
, INGRED.QC_GRADE
, INGRED.LOT_STATUS
FROM ( SELECT DOC_ID
, ITEM_ID
, LOT_ID
FROM IC_TRAN_PND
WHERE DOC_TYPE = 'PROD'
AND LINE_TYPE IN (1
, 2)
AND COMPLETED_IND = 1
AND LOT_ID <> 0 GROUP BY DOC_ID
, ITEM_ID
, LOT_ID HAVING SUM(TRANS_QTY) <> 0 ) PRODUCT
, IC_TRAN_PND INGRED
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE INGRED.DOC_TYPE = 'PROD'
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.COMPLETED_IND = 1
AND INGRED.LINE_TYPE = -1
AND INGRED.LOT_ID <> PRODUCT.LOT_ID
AND ITEM.ITEM_ID = INGRED.ITEM_ID
AND LOT.ITEM_ID = INGRED.ITEM_ID
AND LOT.LOT_ID = INGRED.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
, INGRED.ITEM_ID
, INGRED.LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM_UM
, ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, INGRED.WHSE_CODE
, INGRED.LOCATION
, INGRED.QC_GRADE
, INGRED.LOT_STATUS
, INGRED.TRANS_DATE HAVING SUM(INGRED.TRANS_QTY) <> 0 UNION ALL SELECT PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, INGRED.TRANS_DATE
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM.ITEM_UM
, ITEM.ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, SUM(INGRED.TRANS_QTY)*(-1) SUM_TRANS_QTY1
, SUM(INGRED.TRANS_QTY2)*(-1) SUM_TRANS_QTY2
, INGRED.WHSE_CODE
, INGRED.LOCATION
, INGRED.QC_GRADE
, INGRED.LOT_STATUS
FROM ( SELECT DOC_ID
, ITEM_ID
, LOT_ID
FROM IC_TRAN_CMP
WHERE DOC_TYPE = 'PROD'
AND LINE_TYPE IN (1
, 2)
AND LOT_ID <> 0 GROUP BY DOC_ID
, ITEM_ID
, LOT_ID HAVING SUM(TRANS_QTY) <> 0 ) PRODUCT
, IC_TRAN_CMP INGRED
, IC_ITEM_MST ITEM
, IC_LOTS_MST LOT
WHERE INGRED.DOC_TYPE = 'PROD'
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.LINE_TYPE = -1
AND INGRED.LOT_ID <> PRODUCT.LOT_ID
AND ITEM.ITEM_ID = INGRED.ITEM_ID
AND LOT.ITEM_ID = INGRED.ITEM_ID
AND LOT.LOT_ID = INGRED.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
, INGRED.ITEM_ID
, INGRED.LOT_ID
, ITEM.ITEM_NO
, ITEM.ITEM_DESC1
, ITEM_UM
, ITEM_UM2
, LOT.LOT_NO
, LOT.SUBLOT_NO
, INGRED.WHSE_CODE
, INGRED.LOCATION
, INGRED.QC_GRADE
, INGRED.LOT_STATUS
, INGRED.TRANS_DATE HAVING SUM(INGRED.TRANS_QTY) <> 0

Columns