/* Unused patches */
SELECT mp.PATCH_ID, pkg.PKG_NAME, pv.PKG_VERSION 
  FROM PACKAGES pkg,
  	   PACKAGE_VERSIONS pv,
       (

        /* All patches from pacth parent */
		SELECT pp.PV_ID, pp.PATCH_ID
		  FROM (
		  		SELECT DISTINCT dep.DPV_ID
				  FROM PACKAGE_DEPENDENCIES dep
				 WHERE dep.PV_ID = :PV_ID
		       ) parent_pp,
		       PACKAGE_PATCHES pp,
			   PACKAGE_VERSIONS pv
		 WHERE pp.PV_ID = parent_pp.DPV_ID
 		   AND pp.PATCH_ID = pv.PV_ID
		   AND pv.IS_OBSOLETE IS NULL       
		
		MINUS 
		 
		/* Currently used patches */
		SELECT orig.PV_ID, orig.PATCH_ID
		  FROM PACKAGE_PATCHES pp,
		  	   PACKAGE_DEPENDENCIES dep,
		       PACKAGE_PATCHES orig
		 WHERE pp.PATCH_ID = dep.PV_ID
		   AND pp.PV_ID = :PV_ID
		   AND orig.PATCH_ID = dep.DPV_ID  
 
       ) mp 
 WHERE mp.PATCH_ID = pv.PV_ID
   AND pv.PKG_ID = pkg.PKG_ID    
   AND pv.DLOCKED = 'Y'  
   ORDER BY pv.PKG_VERSION 
      