-- =====================================================
-- WorkInProgressByUser.sql
-- Keep in step with 'UserWip.sql'
--
--   Used by index.asp to list Releases and Package Versions 
--   that the user is working on
-- =====================================================
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 In ('N','R','C')
		   AND wip.PV_ID = pv.PV_ID
		   AND pv.DLOCKED != 'Y'
       AND pv.MODIFIER_ID = :USER_ID
		
		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,
         WORK_IN_PROGRESS wip
		 WHERE rc.RTAG_ID = rt.RTAG_ID
		   AND rt.OFFICIAL In ('N','R','C')	
		   AND pp.PV_ID = rc.PV_ID
		   AND pp.PATCH_ID = pv.PV_ID
		   AND pv.DLOCKED != 'Y'
		   AND pv.IS_OBSOLETE IS NULL
       AND pv.MODIFIER_ID = :USER_ID
		   AND wip.RTAG_ID = rt.RTAG_ID 
       AND wip.PV_ID = pp.PATCH_ID	   
		 
  	   ) rel,
  	   USERS usr
 WHERE rel.MODIFIER_ID = usr.USER_ID   
 GROUP BY  usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL

