VIEW: APPS.GMI_LOTS_DEST_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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