APPS.GMI_LOT_TRACE_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 7

  INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
                             ,ITEM_ID
                             ,LOT_ID
                             ,COMP_NO
                             ,LEVEL_NO
                             ,CIR_FLG)
         values (trace_id,
                 pitem_id,
                 plot_id,
                 comp_no,
                 1,
                 null);

Line: 34

V_selectstmt   VARCHAR2(500);

Line: 53

    V_selectstmt :=' SELECT  INGRED_ITEM_ID item_id,INGRED_LOT_ID lot_id , HAS_CHILD  '||
                   ' FROM GMI_LOTS_SOURCE_BOM_V SRC '||
                   ' WHERE DOC_ID IN (SELECT BATCH_ID FROM PM_BTCH_HDR PM, FM_FORM_MST FM ' ||
                   ' WHERE BATCH_ID=SRC.DOC_ID AND PM.FORMULA_ID=FM.FORMULA_ID ) AND PRODUCT_ITEM_ID = ' || pitem_id  ||
                   '    AND PRODUCT_LOT_ID =' ||plot_id;

Line: 59

    V_selectstmt :=' SELECT  PRODUCT_ITEM_ID item_id,PRODUCT_LOT_ID lot_id , HAS_CHILD  '||
                   ' FROM GMI_LOTS_DEST_BOM_V  DEST '||
                   ' WHERE DOC_ID IN (SELECT BATCH_ID FROM PM_BTCH_HDR PM, FM_FORM_MST FM ' ||
                   ' WHERE BATCH_ID=DEST.DOC_ID AND PM.FORMULA_ID=FM.FORMULA_ID ) AND INGRED_ITEM_ID = '|| pitem_id  ||
                   '    AND INGRED_LOT_ID =' ||plot_id;

Line: 68

  DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);

Line: 104

         INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
                             ,ITEM_ID
                             ,LOT_ID
                             ,COMP_NO
                             ,LEVEL_NO
                             ,CIR_FLG)
         values (trace_id,
                 v_item_id,
                 v_lot_id,
                 comp_no,
                 lvl,
                 null);

Line: 118

         INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
                             ,ITEM_ID
                             ,LOT_ID
                             ,COMP_NO
                             ,LEVEL_NO
                             ,CIR_FLG)
         values (trace_id,
                 v_item_id,
                 v_lot_id,
                 comp_no,
                 lvl,
                 '*');

Line: 132

     INSERT INTO GMI_LOT_TRACE (LOT_TRACE_ID
                             ,ITEM_ID
                             ,LOT_ID
                             ,COMP_NO
                             ,LEVEL_NO
                             ,CIR_FLG)
         values (trace_id,
                 v_item_id,
                 v_lot_id,
                 comp_no,
                 lvl,
                 null);

Line: 155

	select doc_id
	from ic_tran_pnd
	where item_id = fv_item_id and lot_id = fv_lot_id
	and doc_type = 'PROD' and line_type in (1,2)
	and completed_ind = 1 and delete_mark = 0
	group by doc_id
	having sum(trans_qty) > 0;

Line: 168

			select count(*) into lv_ingred_count
			from (
				select item_id, lot_id
				from ic_tran_pnd
				where doc_type = 'PROD'
				and doc_id = prod.doc_id
				and completed_ind = 1
				and delete_mark = 0
				and lot_id <> 0
				and line_type = -1
				group by item_id, lot_id
				having sum(trans_qty) < 0
			);

Line: 202

	select doc_id
	from ic_tran_pnd
	where item_id = fv_item_id and lot_id = fv_lot_id
	and doc_type = 'PROD' and line_type = -1
	and completed_ind = 1 and delete_mark = 0
	group by doc_id
	having sum(trans_qty) < 0;

Line: 215

			select count(*) into lv_product_count
			from (
			select item_id, lot_id
			from ic_tran_pnd
			where doc_type = 'PROD'
			and doc_id = ingred.doc_id
			and completed_ind = 1
			and delete_mark = 0
			and lot_id <> 0
			and line_type in (1,2)
			group by item_id, lot_id
			having sum(trans_qty) > 0);

Line: 248

	select doc_id
	from ic_tran_cmp
	where item_id = fv_item_id and lot_id = fv_lot_id
	and doc_type = 'PROD' and line_type in (1,2)
	group by doc_id
	having sum(trans_qty) > 0;

Line: 260

			select count(*) into lv_ingred_count
			from (
				select item_id, lot_id
				from ic_tran_cmp
				where doc_type = 'PROD'
				and doc_id = prod.doc_id
				and lot_id <> 0
				and line_type = -1
				group by item_id, lot_id
				having sum(trans_qty) < 0
			);

Line: 292

	select doc_id
	from ic_tran_cmp
	where item_id = fv_item_id and lot_id = fv_lot_id
	and doc_type = 'PROD' and line_type = -1
	group by doc_id
	having sum(trans_qty) < 0;

Line: 304

			select count(*) into lv_product_count
			from (
			select item_id, lot_id
			from ic_tran_cmp
			where doc_type = 'PROD'
			and doc_id = ingred.doc_id
			and lot_id <> 0
			and line_type in (1,2)
			group by item_id, lot_id
			having sum(trans_qty) > 0);