Subversion Repositories DevTools

SELECT DISTINCT pkg.pkg_name,
       pv.pkg_version,
       pv.is_autobuildable,
       pv.pv_id,
       pm.code_lines,
       pm.branches,
       (CASE WHEN ut.test_types_fk IS NOT NULL AND ut.test_types_fk != 1 THEN 'Y' ELSE 'N' END) AS unit_tested, 
       autotest_qry.autotested,
       (select count(*) from test_run tr where tr.build_id = cbi.build_id) as test_count,
       TO_CHAR( modified_stamp,'DD-Mon-YYYY HH24:MI:SS' ) AS modified_stamp,
       (CASE WHEN pv.is_autobuildable != 'Y' THEN 'Manual' ELSE  (CASE bi.reason WHEN 'R' THEN 'Ripple' WHEN 'N' THEN 'New Version' ELSE 'Unknown' END ) END) as reason,
       (CASE WHEN pv.pv_id = :PV_ID THEN  1 ELSE 0 END )as inRelease,
       TRUNC( ORA_SYSDATE - modified_stamp ) as ageDays
  FROM package_metrics pm,
       package_versions pv,
       packages pkg,
       unit_tests ut,
       (SELECT ut.pv_id, 'Y' AS autotested FROM unit_tests ut WHERE ut.test_types_fk = 7 ) autotest_qry,
       ( select pv_id, max(build_id) as build_id  from BUILD_INSTANCES bi
          where bi.state in ('C')
          and bi.reason not in ('T')
          group by pv_id
        ) cbi,
       BUILD_INSTANCES bi
 WHERE pv.pkg_id = pkg.pkg_id
   AND pv.pv_id IN (SELECT pv_id FROM package_versions pv1 CONNECT BY NOCYCLE PRIOR pv1.last_pv_id = pv1.pv_id START WITH pv1.pv_id = :PV_ID )
   AND pm.pv_id (+) = pv.pv_id
   AND ut.pv_id (+) = pv.pv_id
   AND autotest_qry.pv_id (+) = pv.pv_id
   and cbi.pv_id(+) = pv.pv_id
   and cbi.build_id = bi.build_id(+)
 ORDER BY pv_id DESC