Subversion Repositories DevTools

SELECT pkg.pkg_name, comp.*, usr.FULL_NAME, usr.user_email
  FROM PACKAGES pkg, 
           RM_USERS usr,
           (  
           
            /* REMOVED ONLY */
                SELECT dpv.pv_id AS pv_id, dpv.pkg_id, NULL AS pkg_version, dpv.pkg_version AS previous_version, -1 AS status, dpv.MODIFIER_ID 
                  FROM PACKAGE_VERSIONS dpv,
                           PACKAGE_DEPENDENCIES dep,
                       (
                        SELECT olddpv.pv_id AS previous_pv_id, newdpv.pv_id AS current_pv_id 
                                  FROM PACKAGE_VERSIONS newdpv,
                                           PACKAGE_DEPENDENCIES newdep,
                                       PACKAGE_VERSIONS olddpv,
                                           PACKAGE_DEPENDENCIES olddep
                                 WHERE newdep.DPV_ID = newdpv.PV_ID 
                                   AND olddep.DPV_ID = olddpv.PV_ID
                                   AND newdep.pv_id = :PROD_ID
                                   AND olddep.pv_id = :OLD_PROD_ID
                                   AND newdpv.pkg_id = olddpv.pkg_id 
                                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )      
                       ) diff
                 WHERE dep.DPV_ID = dpv.PV_ID
                   AND dep.pv_id = :OLD_PROD_ID
                   AND dpv.pv_id = diff.previous_pv_id (+)  
                   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)   
           
                   
                UNION
                
                /* NEW ONLY*/
                SELECT dpv.pv_id AS pv_id, dpv.pkg_id, dpv.pkg_version, NULL AS previous_version, 1 AS status, dpv.MODIFIER_ID
                  FROM PACKAGE_VERSIONS dpv,
                           PACKAGE_DEPENDENCIES dep,
                       ( 
                        SELECT olddpv.pv_id AS previous_pv_id, newdpv.pv_id AS current_pv_id 
                                  FROM PACKAGE_VERSIONS newdpv,
                                           PACKAGE_DEPENDENCIES newdep,
                                       PACKAGE_VERSIONS olddpv,
                                           PACKAGE_DEPENDENCIES olddep
                                 WHERE newdep.DPV_ID = newdpv.PV_ID 
                                   AND olddep.DPV_ID = olddpv.PV_ID
                                   AND newdep.pv_id = :PROD_ID
                                   AND olddep.pv_id = :OLD_PROD_ID  
                                   AND newdpv.pkg_id = olddpv.pkg_id 
                                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' ) 
                   AND :PROD_ID != :OLD_PROD_ID
                       ) diff
                 WHERE dep.DPV_ID = dpv.PV_ID
                   AND dep.pv_id = :PROD_ID
                   AND dpv.pv_id = diff.current_pv_id (+)  
                   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)          
           
                   
                UNION
                
                /* UPDATED and NOCHANGE only */
                SELECT newdpv.pv_id AS pv_id, newdpv.pkg_id, newdpv.pkg_version,
                           DECODE( newdpv.pv_id, olddpv.pv_id, NULL, olddpv.pkg_version ) AS previous_version,
                           DECODE( newdpv.pv_id, olddpv.pv_id, 0, 2 ) AS status, newdpv.MODIFIER_ID
                  FROM PACKAGE_VERSIONS newdpv,
                           PACKAGE_DEPENDENCIES newdep,
                       PACKAGE_VERSIONS olddpv,
                           PACKAGE_DEPENDENCIES olddep
                 WHERE newdep.DPV_ID = newdpv.PV_ID 
                   AND olddep.DPV_ID = olddpv.PV_ID
                   AND newdep.pv_id = :PROD_ID
                   AND olddep.pv_id = :OLD_PROD_ID
                   AND newdpv.pkg_id = olddpv.pkg_id 
                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )     
           AND :PROD_ID != :OLD_PROD_ID
           
                   
                ) comp
 WHERE comp.pkg_id = pkg.pkg_id  
   AND comp.modifier_id = usr.user_id
ORDER BY UPPER(pkg.pkg_name)