Subversion Repositories DevTools

/* Patches */ 
SELECT qry.*
  FROM (   
  
                 SELECT pp.PATCH_ID AS PPATCH_ID,
                                0 AS PATCH_ID,
                        pp.INSTALL_ORDER,
                pkg.PKG_NAME,
                pv.PKG_VERSION,
                pv.COMMENTS,
                pv.OBSOLETE_COMMENTS,
                pv.DLOCKED,
                DECODE( pv.dlocked, 'Y', pv.modified_stamp, NULL ) AS release_stamp, 
                pv.IS_PATCH,
                pv.is_obsolete,
                usr.FULL_NAME,
                usr.USER_EMAIL
                   FROM PACKAGE_PATCHES pp,
                    PACKAGES pkg,
                PACKAGE_VERSIONS pv,
                USERS usr
                  WHERE pp.PV_ID = :PV_ID
            AND pv.PKG_ID = pkg.PKG_ID
            AND pp.PATCH_ID = pv.PV_ID
            AND pv.MODIFIER_ID = usr.USER_ID
                        AND ( ( NVL( pv.IS_OBSOLETE, 'N' ) = 'N' AND :HIDE_OBSOLETE = 'Y' ) OR
                                  ( NVL( pv.IS_OBSOLETE, 'N' ) = NVL( pv.IS_OBSOLETE, 'N' ) AND :HIDE_OBSOLETE = 'N' ) 
                                ) 
                        
                        
          
                UNION  
                   
        
                SELECT pdep.PV_ID AS PPATCH_ID,
                           pdep.DPV_ID AS PATCH_ID,
                       pp.INSTALL_ORDER AS INSTALL_ORDER,
               pkg.PKG_NAME,
               pv.PKG_VERSION,
               pv.COMMENTS,
               pv.OBSOLETE_COMMENTS,
               pv.DLOCKED,
               DECODE( pv.dlocked, 'Y', pv.modified_stamp, NULL ) AS release_stamp, 
               pv.IS_PATCH,
               pv.is_obsolete,
               usr.FULL_NAME,
               usr.USER_EMAIL
                  FROM PACKAGE_DEPENDENCIES pdep,
                           (
                         SELECT pp.PV_ID, pp.PATCH_ID, pp.INSTALL_ORDER
                                   FROM PACKAGE_PATCHES pp
                                  WHERE pp.PV_ID = :PV_ID 
                       ) pp,
               PACKAGES pkg,
               PACKAGE_VERSIONS pv,
               USERS usr
                 WHERE pdep.PV_ID = pp.PATCH_ID     
           AND pv.PKG_ID = pkg.PKG_ID
           AND pdep.DPV_ID = pv.PV_ID
           AND pv.MODIFIER_ID = usr.USER_ID
                   AND ( ( NVL( pv.IS_OBSOLETE, 'N' ) = 'N' AND :HIDE_OBSOLETE = 'Y' ) OR
                                  ( NVL( pv.IS_OBSOLETE, 'N' ) = NVL( pv.IS_OBSOLETE, 'N' ) AND :HIDE_OBSOLETE = 'N' ) 
                                ) 
           
                ) qry  
 ORDER BY qry.INSTALL_ORDER, qry.PATCH_ID