Subversion Repositories DevTools

-- Used by ALL Packages in this release
--      :RTAG_ID
--      :PV_ID
--
-- All the package-vesrions that use a specified package-version in a specified release
--
With small_pd as ( select * from package_dependencies where pv_id in (select pv_id from release_content where rtag_id = :RTAG_ID )),
aa as (select * from (
    select LEVEL as ll, pv_id from small_pd pd connect by prior pv_id = dpv_id start with dpv_id=:PV_ID
    UNION
    SELECT 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
) )
select t.*,  p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED, rc.deprecated_state, pv.build_time, rc.PKG_STATE, usr.FULL_NAME, usr.USER_EMAIL
from (select MAX(ll) as buildLevel, pv_id
      from aa group by pv_id
     ) t, packages p, package_versions pv, release_content rc, USERS usr 
     where t.pv_id = pv.pv_id and pv.pkg_id =  p.pkg_id AND rc.rtag_id = :RTAG_ID and rc.pv_id =  pv.pv_id AND pv.MODIFIER_ID = usr.USER_ID
order by buildLevel, upper(pkg_name)