| 119 |
ghuddy |
1 |
SELECT rel.*,
|
|
|
2 |
LEVEL AS HIERARCHY
|
|
|
3 |
FROM (
|
|
|
4 |
|
|
|
5 |
SELECT rt.RTAG_ID,
|
|
|
6 |
rt.RTAG_NAME,
|
|
|
7 |
rt.DISPLAY_ORDER,
|
|
|
8 |
DECODE( rt.PARENT_RTAG_ID,
|
| 5177 |
dpurdie |
9 |
rt.RTAG_ID, 0, rt.PARENT_RTAG_ID ) AS PARENT_RTAG_ID,
|
|
|
10 |
DECODE( rt.official ,
|
|
|
11 |
'Y', CASE WHEN TRUNC (SYSDATE - rt.official_stamp) > 60 THEN 'O' ELSE 'Y' END,
|
|
|
12 |
rt.official ) as OFFICIAL,
|
| 183 |
brianf |
13 |
rt.DESCRIPTION,
|
|
|
14 |
rt.ASSOC_MASS_REF,
|
| 4804 |
dpurdie |
15 |
TO_CHAR(rt.created_stamp,'DD-Mon-YYYY') AS created_stamp,
|
| 4080 |
dpurdie |
16 |
U2.full_name as creator,
|
| 5177 |
dpurdie |
17 |
TO_CHAR(rt.official_stamp,'DD-Mon-YYYY') AS OFFICIAL_STAMP,
|
|
|
18 |
TRUNC (SYSDATE - rt.official_stamp) as OFFICIAL_STAMP_DAYS,
|
| 5207 |
dpurdie |
19 |
DECODE(U1.full_name, NULL, 'Unknown', U1.full_name )as modifier,
|
| 5251 |
dpurdie |
20 |
RT.LXR,
|
|
|
21 |
NVL(LX.LXRSERVER, 'N') AS LXRSERVER
|
| 4080 |
dpurdie |
22 |
FROM RELEASE_TAGS rt
|
|
|
23 |
left outer join users U1 on rt.official_id = U1.user_id
|
|
|
24 |
left outer join users U2 on rt.creator_id = U2.user_id
|
| 5251 |
dpurdie |
25 |
left outer join lxr_state lx ON rt.RTAG_ID = lx.RTAG_ID
|
| 4080 |
dpurdie |
26 |
WHERE rt.PROJ_ID = :PROJ_ID
|
| 119 |
ghuddy |
27 |
) rel
|
|
|
28 |
START WITH rel.PARENT_RTAG_ID = 0
|
|
|
29 |
CONNECT BY PRIOR rel.RTAG_ID = rel.PARENT_RTAG_ID
|
|
|
30 |
ORDER SIBLINGS BY rel.DISPLAY_ORDER
|
|
|
31 |
|