Subversion Repositories DevTools

/* Release Version List */
SELECT qry.*
   FROM (
            SELECT rel.*,
                   LEVEL AS HIERARCHY
              FROM (
                    SELECT rt.RTAG_ID,
                           rt.RTAG_NAME,
                           rt.DISPLAY_ORDER,
                           DECODE( rt.PARENT_RTAG_ID,
                                   rt.RTAG_ID, 0, rt.PARENT_RTAG_ID ) AS PARENT_RTAG_ID,
                           DECODE( rt.official ,
                                   'Y', CASE WHEN TRUNC (SYSDATE - rt.official_stamp) > 60 THEN 'O' ELSE 'Y' END, 
                                   rt.official ) as OFFICIAL,
                           rt.DESCRIPTION,
                           rt.ASSOC_MASS_REF,
                           TO_CHAR(rt.created_stamp,'DD-Mon-YYYY HH24:MI:SS') AS created_stamp,
                           U2.full_name as creator,
                           TO_CHAR(rt.official_stamp,'DD-Mon-YYYY HH24:MI:SS') AS OFFICIAL_STAMP,
                           TRUNC (SYSDATE - rt.official_stamp) as OFFICIAL_STAMP_DAYS,
                           DECODE(U1.full_name, NULL, 'Unknown', U1.full_name )as modifier,
                           RT.LXR,
                           NVL(LX.LXRSERVER, 'N') AS LXRSERVER,
                           NVL(RT.S3MANIFEST, 'N') AS S3MANIFEST ,
                           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,
                           TO_CHAR(rm.timestamp,'DD-Mon-YYYY HH24:MI:SS') AS lastModified ,
                           EXTRACT(DAY FROM (SYSTIMESTAMP - rm.timestamp)) as lastModified_DAYS
                     FROM RELEASE_TAGS rt
                        left outer join users U1 on rt.official_id = U1.user_id
                        left outer join users U2 on rt.creator_id = U2.user_id
                        left outer join lxr_state lx ON rt.RTAG_ID = lx.RTAG_ID
                        left outer join release_modified rm ON  rt.RTAG_ID = rm.RTAG_ID
                     WHERE rt.PROJ_ID = :PROJ_ID
                    ) rel
            START WITH rel.PARENT_RTAG_ID = 0
            CONNECT BY PRIOR rel.RTAG_ID = rel.PARENT_RTAG_ID
            ORDER SIBLINGS BY rel.DISPLAY_ORDER
         ) qry
 WHERE qry.OFFICIAL IN (/*SHOW_FILTER*/)