Subversion Repositories DevTools

/* Unused Packages */
SELECT dpkg.PKG_NAME,
       latest.PKG_VERSION,
       latest.PV_ID AS LATEST_PV_ID
  FROM ( 
        /* Release content reference */      
        SELECT pv.PV_ID, pv.PKG_ID, pv.V_EXT, pv.PKG_VERSION
                  FROM RELEASE_CONTENT rc,
                           PACKAGE_VERSIONS pv
                 WHERE rc.PV_ID = pv.PV_ID
                   AND rc.RTAG_ID = :RTAG_ID
           ) latest,
       PACKAGE_DEPENDENCIES dep,
       PACKAGE_VERSIONS dpv,
       PACKAGES dpkg
 WHERE dep.DPV_ID = dpv.PV_ID
   AND dep.PV_ID = :PV_ID
   AND dpv.PKG_ID = dpkg.PKG_ID
   AND dpv.PKG_ID = latest.PKG_ID 
   AND NVL ( dpv.V_EXT, '|LINK_A_NULL|' ) = NVL ( latest.V_EXT, '|LINK_A_NULL|' )   
   AND dpv.PV_ID != latest.PV_ID     

MINUS 

/* Remove Already used patche dependencies */   
SELECT pkg.PKG_NAME, pv.PKG_VERSION, pv.PV_ID
  FROM PACKAGE_PATCHES pp,
           PACKAGE_DEPENDENCIES pdep,
       PACKAGES pkg,
       PACKAGE_VERSIONS pv
 WHERE pp.PATCH_ID = pdep.PV_ID
   AND pp.PV_ID = :PV_ID
   AND pv.PKG_ID = pkg.PKG_ID
   AND pdep.DPV_ID = pv.PV_ID