VIEW: APPS.GMI_LOTS_BOM_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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