View: GMI_LOTS_BOM_V

Product:	GMI - Process Manufacturing Inventory
Description:	View of Product document id plus product and ingredient lot id and item id.
Implementation/DBA Data:	APPS.GMI_LOTS_BOM_V

View Text

SELECT PRODUCT.DOC_ID
, PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
FROM IC_TRAN_PND PRODUCT
, IC_TRAN_PND INGRED
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_TYPE = 'PROD'
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.COMPLETED_IND = 1
AND INGRED.LINE_TYPE = -1
AND INGRED.LOT_ID <> 0
AND INGRED.LOT_ID <> PRODUCT.LOT_ID GROUP BY PRODUCT.DOC_ID
, PRODUCT.ITEM_ID
, PRODUCT.LOT_ID
, INGRED.ITEM_ID
, INGRED.LOT_ID HAVING SUM(INGRED.TRANS_QTY) <> 0
AND SUM(PRODUCT.TRANS_QTY) <> 0 UNION ALL SELECT PRODUCT.DOC_ID
, PRODUCT.ITEM_ID PRODUCT_ITEM_ID
, PRODUCT.LOT_ID PRODUCT_LOT_ID
, INGRED.ITEM_ID INGRED_ITEM_ID
, INGRED.LOT_ID INGRED_LOT_ID
FROM IC_TRAN_CMP PRODUCT
, IC_TRAN_CMP INGRED
WHERE PRODUCT.DOC_TYPE = 'PROD'
AND PRODUCT.LINE_TYPE IN (1
, 2)
AND PRODUCT.LOT_ID <> 0
AND INGRED.DOC_TYPE = 'PROD'
AND INGRED.DOC_ID = PRODUCT.DOC_ID
AND INGRED.LINE_TYPE = -1
AND INGRED.LOT_ID <> 0
AND INGRED.LOT_ID <> PRODUCT.LOT_ID GROUP BY PRODUCT.DOC_ID
, PRODUCT.ITEM_ID
, PRODUCT.LOT_ID
, INGRED.ITEM_ID
, INGRED.LOT_ID HAVING SUM(INGRED.TRANS_QTY) <> 0
AND SUM(PRODUCT.TRANS_QTY) <> 0

Columns