/* Latest Recent Released Products */
SELECT pv.pv_id AS PROD_ID,
	   pkg.pkg_name,
	   pv.pkg_version,
	   DECODE ( pkglist.prod_id,
	   		  	NULL, NULL,
				'disabled' ) AS used
  FROM PACKAGE_VERSIONS pv,
  	   PACKAGES pkg,
	   (
	   
	    SELECT lpkg.pv_id, upkg.prod_id
		  FROM (
				/* Get Distinct list of latest packages */
				SELECT DISTINCT MAX(pv.PV_ID) OVER ( PARTITION BY pv.pkg_id || pv.v_ext ) AS PV_ID
				  FROM RELEASE_TAGS rt,
				  	   RELEASE_CONTENT rc,
					   PACKAGE_VERSIONS pv,
				  	   (
					    /* Get all Branches */
					    SELECT vtw.VTREE_ID
						  FROM VTREES_WORLD vtw
						 WHERE vtw.WORLD_ID IN ( SELECT vtw.WORLD_ID FROM VTREES_WORLD vtw WHERE vtw.VTREE_ID = :RM_VTREE_FK )
						 UNION
					    SELECT TO_NUMBER( :RM_VTREE_FK ) AS VTREE_ID FROM DUAL
					   ) wo
				 WHERE wo.VTREE_ID = rt.VTREE_ID
				   AND rc.RTAG_ID = rt.RTAG_ID
				   AND rc.PV_ID = pv.pv_id
				   AND pv.DLOCKED = 'Y'
				   AND rc.BASE_VIEW_ID = :RM_PRODUCTS_BASE_VIEW
				   AND TO_DATE( pv.MODIFIED_STAMP, 'DD-MM-YYYY') > TO_DATE ( SYSDATE - :DAYS_BACK_IN_TIME, 'DD-MM-YYYY' )
				   AND NVL( pv.V_EXT, 'LINK_A_NULL') NOT IN ( SELECT NVL( pe.ext_name, 'LINK_A_NULL') AS V_EXT  
															    FROM PROJECT_EXTENTIONS pe  
															   WHERE pe.proj_id != :RM_PROJECTS_FK )
		  	    ) lpkg,
		  	   (
			    /* Products already used in BOMs */													   
				SELECT DISTINCT osc.PROD_ID
				  FROM BRANCHES br,
				  	   BOMS bo,
					   BOM_CONTENTS bc,
					   OPERATING_SYSTEMS os,
					   OS_CONTENTS osc
				 WHERE br.BRANCH_ID IN ( SELECT bo.BRANCH_ID FROM BOMS bo WHERE bo.BOM_ID = :BOM_ID )
				   AND bo.BRANCH_ID = br.BRANCH_ID
				   AND bc.BOM_ID = bo.BOM_ID
				   AND bc.NODE_ID = os.NODE_ID
				   AND osc.OS_ID = os.OS_ID		
			   	) upkg
		 WHERE lpkg.PV_ID = upkg.PROD_ID (+)
		
		

												   
										   													   
		   
	   ) pkglist
 WHERE pv.pkg_id = pkg.pkg_id
   AND pkglist.pv_id = pv.pv_id
ORDER BY UPPER(pkg.pkg_name)   

   