eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.GMI_LOTS_COMP_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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