Subversion Repositories DevTools

/* Work In Progress by User */
SELECT usr.USER_ID,
           usr.FULL_NAME,
           usr.USER_EMAIL,
           COUNT(usr.USER_ID) AS COUNTER
  FROM (
  
                /* Get WIP Packages */
        SELECT wip.PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
                  FROM WORK_IN_PROGRESS wip,
                           RELEASE_TAGS rt,
                           PACKAGE_VERSIONS pv
                 WHERE wip.RTAG_ID = rt.RTAG_ID
                   AND rt.OFFICIAL != 'Y'
                   AND wip.PV_ID = pv.PV_ID
                   AND pv.DLOCKED != 'Y'
                
                UNION 
                   
                /* Get WIP Patches */
        SELECT pp.PATCH_ID AS PV_ID, rt.RTAG_ID, pv.MODIFIER_ID
                  FROM RELEASE_CONTENT rc,
                           RELEASE_TAGS rt,
                           PACKAGE_PATCHES pp,
                           PACKAGE_VERSIONS pv
                 WHERE rc.RTAG_ID = rt.RTAG_ID
                   AND rt.OFFICIAL != 'Y'       
                   AND pp.PV_ID = rc.PV_ID
                   AND pp.PATCH_ID = pv.PV_ID
                   AND pv.DLOCKED != 'Y'
                   AND pv.IS_OBSOLETE IS NULL
                           
                 
           ) rel,
           USERS usr
 WHERE rel.MODIFIER_ID = usr.USER_ID   
 AND usr.IS_DISABLED IS NULL 
 AND usr.user_id = :USER_ID
 GROUP BY  usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL
 ORDER BY UPPER(usr.FULL_NAME)