Subversion Repositories DevTools

/* 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