Subversion Repositories DevTools

Rev

Rev 7468 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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,
7493 dpurdie 15 
               TO_CHAR(rt.created_stamp,'DD-Mon-YYYY HH24:MI:SS') AS created_stamp,
4080 dpurdie 16 
               U2.full_name as creator,
7493 dpurdie 17 
               TO_CHAR(rt.official_stamp,'DD-Mon-YYYY HH24:MI:SS') AS OFFICIAL_STAMP,
5177 dpurdie 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,
5394 dpurdie 21 
               NVL(LX.LXRSERVER, 'N') AS LXRSERVER ,
7468 dpurdie 22 
               NVL(RT.S3MANIFEST, 'N') AS S3MANIFEST ,
7493 dpurdie 23 
			   CASE when (TRUNC(SYSTIMESTAMP) - TRUNC(rt.created_stamp) <= 20) AND rt.creator_id = :USER_ID AND rt.OFFICIAL != 'S' THEN 1 ELSE 0 END as userCanDelete,
6576 dpurdie 24 
               TO_CHAR(rm.timestamp,'DD-Mon-YYYY HH24:MI:SS') AS lastModified ,
25 
               EXTRACT(DAY FROM (SYSTIMESTAMP - rm.timestamp)) as lastModified_DAYS
4080 dpurdie 26 
		 FROM RELEASE_TAGS rt
27 
               left outer join users U1 on rt.official_id = U1.user_id
28 
               left outer join users U2 on rt.creator_id = U2.user_id
5251 dpurdie 29 
               left outer join lxr_state lx ON rt.RTAG_ID = lx.RTAG_ID
6576 dpurdie 30 
               left outer join release_modified rm ON  rt.RTAG_ID = rm.RTAG_ID
4080 dpurdie 31 
		 WHERE rt.PROJ_ID = :PROJ_ID
7493 dpurdie 32 
         /*TREE_FILTER*/
119 ghuddy 33 
  		) rel
34 
START WITH rel.PARENT_RTAG_ID = 0
35 
CONNECT BY PRIOR rel.RTAG_ID = rel.PARENT_RTAG_ID
36 
ORDER SIBLINGS BY rel.DISPLAY_ORDER
37