APPS.GMP_ITEMS_PKG SQL Statements

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

Line: 43

  insert_statement        VARCHAR2(4096);

Line: 60

    last_update_date      DATE,
    last_updated_by       NUMBER(15),
    last_update_login     NUMBER(15));

Line: 75

  retrieval_cursor := 'DELETE FROM gmp_item_wps '
                   || ' WHERE plant_code = :p_plant_code ';

Line: 81

                'SELECT iim.item_no, iim.item_id, iim.item_um, mum.uom_code, '
	        || '    iim.lot_ctl, iim.item_desc1, msi.inventory_item_id, '
                || '    iwm.mtl_organization_id, '
		|| '    pwe.whse_code, decode(sum(pwe.replen_ind), 0, 0, 1), '
                || '    decode(sum(pwe.consum_ind), 0, 0, 1), '
		|| '    pwe.plant_code, iim.creation_date, iim.created_by, '
                || '    iim.last_update_date, '
		|| '    iim.last_updated_by, NULL '
                || 'FROM   ic_item_mst iim,'
		|| '       sy_uoms_mst sou,'
		|| '       ps_whse_eff pwe,'
		|| '       ic_whse_mst iwm,'
		|| '       mtl_system_items  msi,'
		|| '       mtl_units_of_measure mum '
		|| 'WHERE '
		|| '       iim.delete_mark = 0 AND '
		|| '       iim.inactive_ind = 0 AND '
       		|| '       iim.noninv_ind = 0 AND ' /* B3542453 - sowsubra - Added to pull in only inventoried items
                                                       and hence avoid passing the non-inventory items to the WPS*/
		|| '       iim.item_no = msi.segment1 AND '
		|| '       iwm.mtl_organization_id = msi.organization_id AND '
                || '       pwe.plant_code = :p_plant_code AND '
		|| '       pwe.whse_code = iwm.whse_code AND '
		|| '       sou.unit_of_measure = mum.unit_of_measure AND '
                || '       sou.delete_mark = 0 AND '
		|| '       iim.item_um = sou.um_code AND '
		|| '       iim.experimental_ind = 0 AND '
		|| '       ( '
		|| '         pwe.whse_item_id IS NULL OR '
		|| '         pwe.whse_item_id = iim.whse_item_id OR '
		|| '         ( '
		|| '           pwe.whse_item_id = iim.item_id AND '
		|| '           iim.item_id <> iim.whse_item_id '
		|| '         ) '
		|| '       ) '
		|| 'GROUP BY '
		|| '       iim.item_id, iim.item_no, '
                || '       iim.item_desc1, iim.item_um, '
                || '       iim.lot_ctl, pwe.whse_code, '
		|| '       pwe.plant_code, mum.uom_code, '
                || '       msi.inventory_item_id, '
                || '       iwm.mtl_organization_id, '
		|| '       iim.creation_date, iim.created_by, '
                || '       iim.last_update_date, '
                || '       iim.last_updated_by ';

Line: 129

  insert_statement :=
                  'INSERT INTO gmp_item_wps '
			   || '( '
			   || '  item_no, item_id, item_um, uom_code,'
                           || '  lot_control, item_desc1, '
			   || '  aps_item_id, organization_id, whse_code, '
                           || '  replen_ind, consum_ind, '
			   || '  plant_code, creation_date, created_by, '
                           || '  last_update_date, '
			   || '  last_updated_by, last_update_login '
			   || ') '
			   || 'VALUES '
			   || '(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10, '
                           || ' :p11,:p12,:p13,:p14,:p15,:p16,:p17)';

Line: 148

    EXECUTE IMMEDIATE insert_statement USING
         gmp_item_aps_rec.item_no,
	 gmp_item_aps_rec.item_id,
	 gmp_item_aps_rec.item_um,
	 gmp_item_aps_rec.uom_code,
	 gmp_item_aps_rec.lot_control,
	 gmp_item_aps_rec.item_desc1,
	 gmp_item_aps_rec.aps_item_id,
	 gmp_item_aps_rec.organization_id,
	 gmp_item_aps_rec.whse_code,
         gmp_item_aps_rec.replen_ind,
	 gmp_item_aps_rec.consum_ind,
	 gmp_item_aps_rec.plant_code,
	 SYSDATE,
	 gmp_item_aps_rec.created_by,
         SYSDATE,
	 gmp_item_aps_rec.last_updated_by,
	 0;

Line: 182

  SELECT count(*)
  INTO   v_item_count
  FROM   gmp_item_wps;