-- =====================================================
-- 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

