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