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,
           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.MODIFIER_ID = :USER_ID
           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