/* 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)
