/* Process Name Search */

SELECT DISTINCT
	   qry.proc_id,
	   qry.proc_name,
                 qry.run_as,
	   qry.pkg_owner,
   	   qry.is_interface,	
	   MIN(qry.display_order) OVER (PARTITION BY qry.proc_name) AS display_min,
	   DECODE( qry.proc_name, 
	   		   ':PROCESS_NAME', 'checked' ) AS checked
  FROM (
		/* Show Process with exact match */
		SELECT prc.proc_id,
			   prc.proc_name,
			   prc.run_as,
			   prc.pkg_owner,
			   prc.is_interface,		
			   1 AS display_order
		  FROM PROCESSES prc
		 WHERE prc.PROC_NAME = ':PROCESS_NAME'
		
		UNION
		
		/* Show Process with exact match (case ignored) */ 
		SELECT prc.proc_id,
			   prc.proc_name,
			   prc.run_as,
			   prc.pkg_owner,
			   prc.is_interface,	
			   2 AS display_order
		  FROM PROCESSES prc
		 WHERE UPPER(prc.PROC_NAME) = UPPER(':PROCESS_NAME') 
		 
		UNION
		
		/* Show Process starting with name  */ 
		SELECT prc.proc_id,
			   prc.proc_name,
			   prc.run_as,
			   prc.pkg_owner,
			   prc.is_interface,	
			   3 AS display_order
		  FROM PROCESSES prc
		 WHERE UPPER(prc.PROC_NAME) LIKE UPPER(':PROCESS_NAME%')  
		
		 
		UNION
		
		/* Show Process with name   */ 
		SELECT prc.proc_id,
			   prc.proc_name,
			   prc.run_as,
			   prc.pkg_owner,
			   prc.is_interface,	
			   4 AS display_order
		  FROM PROCESSES prc
		 WHERE UPPER(prc.PROC_NAME) LIKE UPPER('%:PROCESS_NAME%')   	   
  	   ) qry
ORDER BY display_min