Subversion Repositories DevTools

 SELECT rt.rtag_id,
        bm.display_name,
        rl.rcon_id,
        rl.pause,
        rl.current_run_level,
        rl.current_pkg_id_being_built, 
        rt.build_age, 
        rc.daemon_mode, 
        TRUNC (SYSDATE - rl.LAST_BUILD) as last_build_days, 
        TO_CHAR(rl.LAST_BUILD, 'DD-Mon-YYYY') as last_build, 
    CASE WHEN (rl.rcon_id IS NULL) THEN -1 -- unavailable
         WHEN (rc.bmcon_id IS NULL) THEN -1 -- unavailable
         WHEN (rt.official in ('A','Y','P') ) THEN -1 -- unavailable
         WHEN (rl.pause = 1)       THEN 1 -- paused
         WHEN (rl.pause = 2)       THEN 2 -- disabled
         WHEN ((TRUNC(86400*(SYSDATE - rl.keep_alive)) > 600 ) AND (rl.current_pkg_id_being_built IS NULL)) THEN -2 -- daemon timed out
         WHEN (rl.current_run_level <> 3) AND (rl.pause IS NULL) THEN 0 -- enabled and not engaged
         WHEN (rl.current_run_level = 3) AND (rl.pause IS NULL) THEN 3 -- enabled and engaged
         ELSE -1 -- unavailable
    END AS status,
    NVL(bm.ACTIVE,'U') as active
  FROM release_tags rt, release_config rc, run_level rl, build_machine_config bm
  WHERE rt.proj_id = :PROJ_ID
  AND rt.rtag_id = rc.rtag_id
  AND rc.rcon_id = rl.rcon_id (+)
  AND rc.bmcon_id = bm.bmcon_id(+)
ORDER BY bm.display_name, rc.rcon_id