APPS.EAM_WORKBENCH_TREE SQL Statements

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

Line: 6

   * and insert those into the temp table under a group id which is returned.
   * It returns -1 if nothing found.
   */

  function find_all_asset_numbers(p_org_id number,
                                  p_instance_id number,
                                  p_location_id number,
                                  p_category_id number,
                                  p_owning_dept_id number,
                                  p_asset_group_id number,
                                  p_asset_number varchar2,
				  p_transferred_asset varchar2,
				  p_set_name_id  number) return number is
    l_group_id number;

Line: 28

    select eam_asset_explosion_temp_s.nextval
      into l_group_id from dual;

Line: 32

    INSERT INTO eam_asset_explosion_temp(
        group_id,
        asset_group_id,
        asset_number,
        low_level_code)
        SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
          FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,eam_org_maint_defaults  eomd
         WHERE msi.eam_item_type = 1 AND msi.inventory_item_id = cii.inventory_item_id
	   AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
	   AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
	   AND msi.organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id
	   AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
           AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
           AND (p_category_id IS NULL OR cii.category_id = p_category_id)
           AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
	   AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
	   AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
	   AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
	   AND eomd.organization_id(+) = p_org_id
	   AND (p_set_name_id is null OR
			(
		           (cii.instance_id,3) in
			    (select maintenance_object_id,maintenance_object_type
			     from eam_pm_schedulings where set_name_id = p_set_name_id )
			 ) );

Line: 64

	    INSERT INTO eam_asset_explosion_temp(
		group_id,
		asset_group_id,
		asset_number,
		low_level_code)
		SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		  FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,
		       (select * from eam_org_maint_defaults where organization_id = p_org_id) eomd
		 WHERE msi.eam_item_type = 1
		 AND msi.inventory_item_id = cii.inventory_item_id
		   AND msi.organization_id = cii.last_vld_organization_id
		   AND msi.serial_number_control_code <> 1
		   AND nvl(cii.active_start_date, sysdate-1) <= sysdate
		   AND nvl(cii.active_end_date, sysdate+1) >= sysdate
		   AND msi.organization_id = mp.organization_id
		   AND mp.maint_organization_id <> p_org_id
		   AND cii.instance_id = eomd.object_id (+)
		   AND eomd.object_type (+) = 50
		   AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
		   AND (p_category_id IS NULL OR cii.category_id = p_category_id)
		   AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
		   AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
		   AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
		   AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
		   AND (p_set_name_id is null OR
			(
		           (cii.instance_id,3) in
			    (select maintenance_object_id,maintenance_object_type
			     from eam_pm_schedulings where set_name_id = p_set_name_id )
			 ) )
		   AND EXISTS (SELECT 1
				FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 106

      	INSERT INTO eam_asset_explosion_temp(
        		group_id,
        		asset_group_id,
        		asset_number,
        		low_level_code)
                 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		   FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
		  WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
		    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;

Line: 124

		INSERT INTO eam_asset_explosion_temp(
				group_id,
				asset_group_id,
				asset_number,
				low_level_code)
			 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
			   FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
			  WHERE p_instance_id = mena.network_object_id
			    AND mena.maintenance_object_id = cii.instance_id
			    AND cii.last_vld_organization_id = mp.organization_id
			    AND mp.maint_organization_id <> p_org_id
			    AND EXISTS (SELECT 1
				FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 147

      	INSERT INTO eam_asset_explosion_temp(
        		group_id,
        		asset_group_id,
        		asset_number,
        		low_level_code)
                 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		   FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
		  WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
		    AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
		    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;

Line: 165

		INSERT INTO eam_asset_explosion_temp(
				group_id,
				asset_group_id,
				asset_number,
				low_level_code)
			 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
			   FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
			  WHERE ciin.serial_number = p_asset_number
			    AND ciin.inventory_item_id = p_asset_group_id
			    AND ciin.instance_id = mena.network_object_id
			    AND mena.maintenance_object_id = cii.instance_id
			    AND cii.last_vld_organization_id = mp.organization_id
			    AND mp.maint_organization_id <> p_org_id
			    AND EXISTS (SELECT 1
				FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 271

        select eam_asset_explosion_temp_s.nextval
          into l_group_id from dual;

Line: 274

        INSERT INTO eam_asset_explosion_temp(
            group_id,
            asset_group_id,
            asset_number,
            low_level_code)
        SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
          FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,eam_org_maint_defaults eomd
         WHERE msi.eam_item_type in (1,3) AND msi.inventory_item_id = cii.inventory_item_id
	   AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
	   AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
	   AND msi.organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id
	   AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
           AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
           AND (p_category_id IS NULL OR cii.category_id = p_category_id)
           AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
	   AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
	   AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
	   AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
	   AND (p_set_name_id is null OR
			(
		           (cii.instance_id,3) in
			    (select maintenance_object_id,maintenance_object_type
			     from eam_pm_schedulings where set_name_id = p_set_name_id )
			 ) )
	   AND eomd.organization_id(+) = p_org_id;

Line: 308

		INSERT INTO eam_asset_explosion_temp(
		    group_id,
		    asset_group_id,
		    asset_number,
		    low_level_code)
		SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		  FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,
		       (select * from eam_org_maint_defaults where organization_id = p_org_id) eomd
		 WHERE msi.eam_item_type in (1,3) AND msi.inventory_item_id = cii.inventory_item_id
		   AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
		   AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
		   AND msi.organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
		   AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
		   AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
		   AND (p_category_id IS NULL OR cii.category_id = p_category_id)
		   AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
		   AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
		   AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
		   AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
		   AND (p_set_name_id is null OR
			(
		           (cii.instance_id,3) in
			    (select maintenance_object_id,maintenance_object_type
			     from eam_pm_schedulings where set_name_id = p_set_name_id )
			 ) )
		   AND EXISTS (SELECT 1
				FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 345

        	INSERT INTO eam_asset_explosion_temp(
        		group_id,
        		asset_group_id,
        		asset_number,
        		low_level_code)
                 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		   FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
		  WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
		    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;

Line: 362

			INSERT INTO eam_asset_explosion_temp(
				group_id,
				asset_group_id,
				asset_number,
				low_level_code)
			 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
			   FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
			  WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
			    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
			   AND EXISTS (SELECT 1
					FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 382

      	         INSERT INTO eam_asset_explosion_temp(
        		group_id,
        		asset_group_id,
        		asset_number,
        		low_level_code)
                 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
		   FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
		  WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
		    AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
		    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;

Line: 400

			 INSERT INTO eam_asset_explosion_temp(
				group_id,
				asset_group_id,
				asset_number,
				low_level_code)
			 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
			   FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
			  WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
			    AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
			    AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
			   AND EXISTS (SELECT 1
				FROM wip_discrete_jobs
				WHERE organization_id = p_org_id
				  AND maintenance_object_id = cii.instance_id) ;

Line: 461

   /* This procedure is used to delete the session data from eam_asset_explosion_temp
      table. This is added for the bug #2688078
   */
      procedure clear_eam_asset(p_group_id IN NUMBER) is
       PRAGMA AUTONOMOUS_TRANSACTION;

Line: 469

           delete from eam_asset_explosion_temp where  group_id = p_group_id;

Line: 482

              DELETE
              FROM eam_asset_explosion_temp
              WHERE  group_id = p_global_group_ids(i);