/* Product Name Search */

SELECT DISTINCT
	   qry.pkg_id,
	   qry.pkg_name,
	   MIN(qry.display_order) OVER (PARTITION BY qry.pkg_name) AS display_min,
	   DECODE( qry.pkg_name, 
	   		   ':PROD_NAME', 'checked' ) AS checked
  FROM (
		/* Show Product with exact match */
		SELECT pkg.pkg_id,
			   pkg.pkg_name,
			   1 AS display_order
		  FROM PACKAGES pkg
		 WHERE pkg.PKG_NAME = ':PROD_NAME'
		
		UNION
		
		/* Show Product with exact match (case ignored) */ 
		SELECT pkg.pkg_id,
			   pkg.pkg_name,
			   2 AS display_order
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) = UPPER(':PROD_NAME') 
		 
		UNION
		
		/* Show Product starting with name  */ 
		SELECT pkg.pkg_id,
			   pkg.pkg_name,
			   3 AS display_order
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER(':PROD_NAME%')  
		
		 
		UNION
		
		/* Show Product with name   */ 
		SELECT pkg.pkg_id,
			   pkg.pkg_name,
			   4 AS display_order
		  FROM PACKAGES pkg
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER('%:PROD_NAME%')   	   
  	   ) qry
ORDER BY display_min
