APPS.EGO_MASS_UPDATE_PVT SQL Statements

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

Line: 66

    SELECT  mtl_system_items_intf_sets_s.NEXTVAL
    INTO  l_set_process_id
    FROM  dual;

Line: 81

    SELECT DISTINCT organization_id
    INTO l_in_context_organization_id
    FROM ego_massupdate_item_tmp
    WHERE batch_id = p_batch_id;

Line: 91

    INSERT INTO mtl_system_items_interface
		(  process_flag
           	 , set_process_id
          	 , transaction_type
          	 , inventory_item_id
          	 , item_number -- added for Item-Org assignment across master orgs
          	 , description -- added for Item-Org assignment across master orgs
          	 , organization_id
          	 , primary_uom_code
          	 , primary_unit_of_measure
          	 , cost_of_sales_account
          	 , encumbrance_account
          	 , sales_account
          	 , expense_account
          	 , last_update_date
          	 , last_updated_by
          	 , creation_date
          	 , created_by
          	 , last_update_login
          	 , request_id
          	 , program_application_id
          	 , program_id
          	 , program_update_date
          	 )
    SELECT 	1, --Process_Flag
		l_set_process_id, --SET_PROCESS_ID
		'CREATE', --TXN_TYPE
		emit.inventory_item_id, --ITEM_ID
		emit.item_number,
		emit.description,
		emot.organization_id_child, --ORG_ID
	 	NVL( (SELECT msib.primary_uom_code
	        	FROM mtl_system_items_b msib, mtl_parameters mp
			WHERE msib.organization_id = mp.master_organization_id
			AND mp.organization_id = emot.organization_id_child
			AND msib.inventory_item_id = emit.inventory_item_id),
             	      (SELECT msib2.primary_uom_code
			FROM mtl_system_items_b msib2
			WHERE msib2.organization_id = emit.organization_id
			AND msib2.inventory_item_id = emit.inventory_item_id)
			) UOM_CODE,
	 	NVL( (SELECT msib.primary_unit_of_measure -- Can We avoid this second select
	        	FROM mtl_system_items_b msib, mtl_parameters mp
			WHERE msib.organization_id = mp.master_organization_id
			AND mp.organization_id = emot.organization_id_child
			AND msib.inventory_item_id = emit.inventory_item_id),
             	     (SELECT msib2.primary_unit_of_measure
			FROM mtl_system_items_b msib2
			WHERE msib2.organization_id = emit.organization_id
			AND msib2.inventory_item_id = emit.inventory_item_id)
			), --PUOM
		mp.cost_of_sales_account,
		mp.encumbrance_account,
		mp.sales_account,
		mp.expense_account,
        	SYSDATE,
        	FND_GLOBAL.user_id,
        	SYSDATE,
        	FND_GLOBAL.user_id,
        	FND_GLOBAL.login_id,
        	FND_GLOBAL.conc_request_id,
        	FND_GLOBAL.prog_appl_id,
        	FND_GLOBAL.conc_program_id,
        	SYSDATE
    FROM ego_massupdate_item_tmp emit,
	 ego_massupdate_org_tmp emot,
	 mtl_parameters mp
    WHERE NOT EXISTS
	  (SELECT '1'
	  FROM mtl_system_items_b msib
	  WHERE msib.organization_id = emot.organization_id_child
	  AND msib.inventory_item_id = emit.inventory_item_id)
    AND emit.selected_flag = 'Y'
    AND emot.org_selected_flag = 'Y'
    AND mp.organization_id = emot.organization_id_child
    AND emot.batch_id = p_batch_id
    AND emot.batch_id = emit.batch_id;

Line: 181

	-- Can we avoid this select and directly use the seq value
	-- If used directly then error 'Exact fetch return more than one row' occurs
	-- because for each row the sequence value is being incremented

/*	SELECT  mtl_system_items_intf_sets_s.NEXTVAL
	INTO  l_batch_set_process_id
	FROM  dual;

Line: 189

	UPDATE mtl_system_items_interface
	SET set_process_id = l_batch_set_process_id
	WHERE set_process_id = l_set_process_id
	AND ROWNUM <= l_max_batch_size;

Line: 195

	-- If no more rows are left to be updated then exit the loop
	IF (SQL%ROWCOUNT = 0) THEN
	  	Write_Debug('REQUEST HAS BEEN SUBMITTED FOR ALL BATCHES');

Line: 208

       	INSERT INTO mtl_item_revisions_interface
          	 ( inventory_item_id
          	 , item_number -- added for Item-Org assignment across master orgs
          	 , description -- added for Item-Org assignment across master orgs
          	 , organization_id
          	 , revision
          	 , implementation_date
          	 , effectivity_date
          	 , transaction_id
          	 , process_flag
          	 , transaction_type
          	 , set_process_id
          	 , last_update_date
          	 , last_updated_by
          	 , creation_date
          	 , created_by
          	 , last_update_login
          	 , request_id
          	 , program_application_id
          	 , program_id
          	 , program_update_date
          	 )
	SELECT
		   msii.inventory_item_id
          	 , msii.item_number
          	 , msii.description
          	 , msii.organization_id
		 , mp.starting_revision
		 , SYSDATE
		 , SYSDATE
		 , MTL_SYSTEM_ITEMS_INTERFACE_S.nextval --- TRANSACTION_ID
		 , 1
		 , 'CREATE'
		 , l_set_process_id   --l_batch_set_process_id
		 , SYSDATE
          	 , FND_GLOBAL.user_id
          	 , SYSDATE
          	 , FND_GLOBAL.user_id
          	 , FND_GLOBAL.login_id
          	 , FND_GLOBAL.conc_request_id
          	 , FND_GLOBAL.prog_appl_id
          	 , FND_GLOBAL.conc_program_id
          	 , SYSDATE
	FROM mtl_system_items_interface msii,
	     mtl_parameters mp
	WHERE msii.set_process_id = l_set_process_id
	AND mp.organization_id = msii.organization_id;

Line: 347

  DELETE ego_massupdate_item_tmp where to_date(to_char (creation_date,'hh24:mi:ss
  dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
  dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');

Line: 351

  DELETE ego_massupdate_org_tmp where to_date(to_char (creation_date,'hh24:mi:ss
  dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
  dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');