
SELECT pv.PV_ID AS PROD_ID,
	   pkg.PKG_NAME,
	   pv.PKG_VERSION,
	   DECODE( prc.PROD_ID,
	   		   NULL, '', 'disabled') AS USED,
	   DECODE( pv.DLOCKED,
	   		   'Y', 'LIMG_PRODUCT',
			   'LIMG_PRODUCT_UNPACKAGED' ) AS ICON,
	   pp.PROC_ID, prc.PKG_HEALTH_TAG
  FROM RELEASE_CONTENT rc,
	   PACKAGES pkg,
	   PACKAGE_VERSIONS pv,
	   RELEASE_MANAGER.PACKAGE_PROCESSES pp,
	   (
	   	SELECT osc.PROD_ID, osc.OS_ID
		  FROM OS_CONTENTS osc
		 WHERE osc.OS_ID = :OS_ID
	   ) osc,
	   (
	   SELECT prc.PROD_ID, prc.PROC_ID, prc.PKG_HEALTH_TAG
	   	 FROM PROCESSES_CONFIG prc
		 WHERE prc.OS_ID = :OS_ID 
	   ) prc
 WHERE rc.RTAG_ID = :RTAG_ID_FK
   AND pv.PKG_ID = pkg.PKG_ID
   AND rc.PV_ID = PV.PV_ID
   AND rc.BASE_VIEW_ID = :enumPRODUCTS_BASE_VIEW_ID 		
   AND osc.PROD_ID (+) = rc.PV_ID
   AND pp.PV_ID = osc.PROD_ID
   AND prc.PROC_ID (+) = pp.PROC_ID	
 ORDER BY UPPER(prc.PKG_HEALTH_TAG), UPPER(pkg.PKG_NAME), UPPER(pv.PKG_VERSION)