Subversion Repositories DevTools

-- All the packages that a specific package will depend on - directly and indirectly
--      :RTAG_ID
--      :PV_ID
--
-- All the package-versions that feed into package-version
-- Release information is not relevent
--
--  Does not include the root package
--      But setting 'start with pv_id=' to 'start with dpv_id=' will include the base package
With aa as (
    select distinct LEVEL as ll, pd.dpv_id from package_dependencies pd connect by prior dpv_id = pv_id start with pv_id=:PV_ID
    UNION
    SELECT 0 AS ll, to_number(:PV_ID) AS dpv_id FROM dual
)
select t.*,  p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED,pv.pkg_id, rc.deprecated_state, pv.build_time, rc.PKG_STATE, usr.FULL_NAME, usr.USER_EMAIL
from (select MIN(ll) as buildLevel, dpv_id as pv_id
      from aa group by dpv_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)