/* Find in BOM */
SELECT DISTINCT
	   qry.RESULT_NAME,
       qry.LOCATION,
       qry.URL,
	   MIN(qry.display_order) OVER (PARTITION BY qry.RESULT_NAME) AS display_min
  FROM (

		/* Product Name at OS */
		SELECT pkg.PKG_NAME AS RESULT_NAME,
		       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / '|| pkg.PKG_NAME ||' '|| pv.PKG_VERSION  AS LOCATION,  
		       'ProdDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID ||'&prod_id='|| pv.PV_ID  AS URL,
		       1 AS DISPLAY_ORDER     
		  FROM OS_CONTENTS osc,
		  	   PACKAGES pkg,
		       PACKAGE_VERSIONS pv,
		       BOM_CONTENTS bc,
		       OPERATING_SYSTEMS os,
		       NETWORK_NODES nn
		 WHERE osc.OS_ID = os.OS_ID
		   AND os.NODE_ID = nn.NODE_ID
		   AND nn.NODE_ID = bc.NODE_ID
		   AND bc.BOM_ID = :BOM_ID
		   AND pv.PKG_ID = pkg.PKG_ID
		   AND osc.PROD_ID = pv.PV_ID  
		   AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )  
		    
		   
		UNION 
		    
		   
		/* Product Name at Base Env */
		SELECT pkg.PKG_NAME AS RESULT_NAME,
		       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / <br>'|| be.BASE_ENV_NAME ||'/'||pkg.PKG_NAME ||' '|| pv.PKG_VERSION  AS LOCATION,  
		       'Os_BaseConf.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID  AS URL,
		       2 AS DISPLAY_ORDER     
		  FROM BASE_ENV be,
		       BASE_ENV_CONTENTS bec,
		       OS_BASE_ENV obe,
		       NETWORK_NODES nn,
		       OPERATING_SYSTEMS os,
		       PACKAGES pkg,
		       PACKAGE_VERSIONS pv  
		 WHERE be.BOM_ID = :BOM_ID
		   AND bec.BASE_ENV_ID = be.BASE_ENV_ID
		   AND pv.PKG_ID = pkg.PKG_ID
		   AND bec.PROD_ID = pv.PV_ID
		   AND obe.BASE_ENV_ID = be.BASE_ENV_ID
		   AND obe.OS_ID = os.OS_ID
		   AND os.NODE_ID = nn.NODE_ID  
		   AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )   
		 
		   
		UNION   
		     
		   
		/* Network Node Name */
		SELECT nn.NODE_NAME AS RESULT_NAME,
		       'Location: '|| nn.NODE_NAME  AS LOCATION,  
		       'NodeDefault.asp?bom_id='|| :BOM_ID ||'&node_id='|| nn.NODE_ID  AS URL,
		       3 AS DISPLAY_ORDER     
		  FROM BOM_CONTENTS bc,
		       NETWORK_NODES nn
		 WHERE nn.NODE_ID = bc.NODE_ID
		   AND UPPER( nn.NODE_NAME ) LIKE UPPER( :KEYWORD ) 
		   AND bc.BOM_ID = :BOM_ID  
		   
		
		UNION
		
		
		/* OS Name */
		SELECT os.OS_NAME AS RESULT_NAME,
		       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME  AS LOCATION,  
		       'OsDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID  AS URL,
		       4 AS DISPLAY_ORDER     
		  FROM BOM_CONTENTS bc,
		       OPERATING_SYSTEMS os,
		       NETWORK_NODES nn
		 WHERE os.NODE_ID = nn.NODE_ID
		   AND nn.NODE_ID = bc.NODE_ID
		   AND bc.BOM_ID = :BOM_ID
		   AND UPPER( os.OS_NAME ) LIKE UPPER( :KEYWORD )    

  	   ) qry
ORDER BY display_min  