Subversion Repositories DevTools

-- =====================================================
-- WorkNotInProgressByUser.sql
-- Keep in step with 'UserDangle.sql'
--
--   Used by index.asp to list UnRelease Package Versions 
--   modified by the user, but not in use anywhere
-- =====================================================
SELECT usr.USER_ID,
       usr.FULL_NAME,
       usr.USER_EMAIL,
       usr.USER_NAME,
       COUNT(usr.USER_ID) AS COUNTER
  FROM (
        /* UnReleased packages not in use */
        SELECT pv.PV_ID, pv.MODIFIER_ID
          FROM PACKAGE_VERSIONS pv
         WHERE pv.pv_id not in (select pv_id from PLANNED )
           AND pv.pv_id not in (select pv_id from WORK_IN_PROGRESS )
           AND pv.pv_id not in (select pv_id from RELEASE_CONTENT )
           AND pv.pv_id not in (select rtd_id from RUNTIME_DEPENDENCIES)
           AND pv.pv_id not in (select pv_id from PACKAGE_PROCESSES )
           AND pv.pv_id not in (select prod_id from DEPLOYMENT_MANAGER.OS_CONTENTS )
           AND pv.pv_id not in (select prod_id from DEPLOYMENT_MANAGER.BASE_ENV_CONTENTS )
           AND pv.dlocked        != 'Y'

       ) rel,
       USERS usr
 WHERE rel.MODIFIER_ID = usr.USER_ID   
 GROUP BY  usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL,usr.USER_NAME
-- order by UPPER(usr.FULL_NAME )
order by COUNTER DESC