| 5503 |
dpurdie |
1 |
-- Generate a table of build machines that are available
|
|
|
2 |
-- Exclude those that users cannot configure
|
| 7278 |
dpurdie |
3 |
-- Mark as selected any machine that matches :bmcon_id
|
|
|
4 |
-- Mark as disabled any machine that is already in use by :rtag_id, except :bmcon_id
|
| 5503 |
dpurdie |
5 |
--
|
|
|
6 |
-- Generate data suitable for filling in a MultiCombo Box
|
|
|
7 |
|
| 7279 |
dpurdie |
8 |
SELECT AllMachines.BMCON_ID, AllMachines.display_name, AllMAchines.GBE_VALUE, AllMachines.DESCRIPTION,
|
|
|
9 |
CASE
|
|
|
10 |
WHEN AllMachines.selected is not NULL THEN 'selected available'
|
|
|
11 |
WHEN InUse.BMCON_ID is not NULL THEN 'disabled'
|
|
|
12 |
ELSE 'available'
|
|
|
13 |
END as state,
|
|
|
14 |
AllMachines.selected, NVL2(InUse.BMCON_ID, ' disabled', ' available') AS available
|
| 5503 |
dpurdie |
15 |
FROM
|
|
|
16 |
|
|
|
17 |
(SELECT bm.bmcon_id, display_name, gbe_value,
|
|
|
18 |
COALESCE(SUBSTR(description,1,INSTR(description,CHR(10),1,1)),description) AS description,
|
|
|
19 |
DECODE(:bmcon_id, bm.bmcon_id, 'selected', NULL) AS SELECTED
|
|
|
20 |
FROM build_machine_config bm, gbe_machtype gb
|
|
|
21 |
WHERE bm.gbe_id = gb.gbe_id
|
|
|
22 |
AND (bm.active = 'Y' OR bm.bmcon_id = :bmcon_id )
|
|
|
23 |
) AllMachines,
|
|
|
24 |
|
|
|
25 |
(SELECT BMCON_ID
|
|
|
26 |
FROM release_config rc, gbe_machtype gbe
|
|
|
27 |
WHERE rc.rtag_id = :rtag_id
|
| 7278 |
dpurdie |
28 |
AND gbe.gbe_id = rc.gbe_id
|
| 5503 |
dpurdie |
29 |
) InUse
|
|
|
30 |
|
|
|
31 |
WHERE AllMachines.BMCON_ID = InUse.BMCON_ID(+)
|
|
|
32 |
ORDER BY UPPER(display_name)
|
|
|
33 |
|
|
|
34 |
|
| 7279 |
dpurdie |
35 |
|