Subversion Repositories DevTools

SELECT pkg.pkg_name, comp.*, usr.FULL_NAME, usr.user_email
  FROM PACKAGES pkg, 
           USERS usr,
           (   
                SELECT dpv.pv_id AS pv_id, -1 AS old_pv_id, dpv.pkg_id, NULL AS pkg_version, dpv.pkg_version AS previous_version, 2 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 = :PV_ID
                                   AND olddep.pv_id = :OLD_PV_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_PV_ID
                   AND dpv.pv_id = diff.previous_pv_id (+)  
                   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)
                UNION
                SELECT dpv.pv_id AS pv_id, 0 AS old_pv_id, dpv.pkg_id, dpv.pkg_version, NULL AS previous_version, 3 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 = :PV_ID
                                   AND olddep.pv_id = :OLD_PV_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 = :PV_ID
                   AND dpv.pv_id = diff.current_pv_id (+)  
                   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)   
                UNION
                SELECT newdpv.pv_id AS pv_id,
                       olddep.dpv_id AS old_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, 1 ) 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 = :PV_ID
                   AND olddep.pv_id = :OLD_PV_ID
                   AND newdpv.pkg_id = olddpv.pkg_id 
                   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
                ) comp
 WHERE comp.pkg_id = pkg.pkg_id  
   AND comp.modifier_id = usr.user_id
ORDER BY UPPER(pkg.pkg_name)