Subversion Repositories DevTools

--      Generate a table of build machines that are available
--      Exclude those that users cannot configure
--              Mark as selected any machine that matches :bmcon_id
--              Mark as disabled any machine that is already in use by :rtag_id, except :bmcon_id
--
--      Generate data suitable for filling in a MultiCombo Box

SELECT AllMachines.BMCON_ID, AllMachines.display_name, AllMAchines.GBE_VALUE, AllMachines.DESCRIPTION,
CASE 
    WHEN AllMachines.selected is not NULL THEN 'selected available'
    WHEN InUse.BMCON_ID is not NULL THEN 'disabled'
    ELSE 'available'
END as state,
AllMachines.selected, NVL2(InUse.BMCON_ID, ' disabled', ' available') AS available 
FROM  
  
  (SELECT bm.bmcon_id, display_name, gbe_value,  
    COALESCE(SUBSTR(description,1,INSTR(description,CHR(10),1,1)),description) AS description,  
    DECODE(:bmcon_id, bm.bmcon_id, 'selected', NULL) AS SELECTED  
  FROM build_machine_config bm, gbe_machtype gb  
  WHERE bm.gbe_id = gb.gbe_id  
  AND (bm.active  = 'Y' OR bm.bmcon_id  = :bmcon_id )  
  ) AllMachines,  
    
  (SELECT BMCON_ID  
  FROM release_config rc, gbe_machtype gbe  
  WHERE rc.rtag_id = :rtag_id  
  AND gbe.gbe_id   = rc.gbe_id
  ) InUse  
    
WHERE AllMachines.BMCON_ID = InUse.BMCON_ID(+)  
ORDER BY UPPER(display_name)