SELECT usr.full_name, 
       usr.user_email, 
	   pkg.pkg_name, 
	   pv.pkg_version, 
	   pv.pv_id, 
	   usr.user_id, 
	   :RTAG_ID AS rtag_id,
	   /*ORA_SYSDATETIME*/ AS date_time_stamp
  FROM package_versions pv,
       packages pkg,
       users usr,
       (
	   	
		(
        /* Users to be notified with ready to build packages */
        SELECT rel.pv_id, pv.owner_id
          FROM release_content rel,
               package_versions pv
         WHERE rel.pv_id = pv.pv_id
           AND rel.rtag_id = :RTAG_ID
           AND rel.pkg_state IN (/*PKG_STATE_MAJOR_READY*/, /*PKG_STATE_MINOR_READY*/)
           /* -------- Do not email current user! -------- */
           AND pv.owner_id != :CURRENT_USER   
           /* -------------------------------------------- */ 
        MINUS   
        /* Disregard packages with unofficial dependencies */
        SELECT pv.pv_id, pv.owner_id
          FROM package_dependencies dep,
               package_versions dpv,
               release_content rc,
               package_versions pv,
               (
                SELECT pv.pkg_id, pv.v_ext
                  FROM release_content rc,
                       package_versions pv
                 WHERE rc.pv_id = pv.pv_id
                   AND rc.rtag_id = :RTAG_ID
                   AND pv.dlocked = 'N'
                   AND rc.pkg_state = /*PKG_STATE_OK*/
               ) nloc
         WHERE dep.dpv_id = dpv.pv_id
           AND dep.pv_id = rc.pv_id
           AND rc.rtag_id = :RTAG_ID
           AND rc.pkg_state IN (/*PKG_STATE_MAJOR_READY*/, /*PKG_STATE_MINOR_READY*/)
           AND NVL(dpv.v_ext,'|LINK_A_NULL|') = NVL(nloc.v_ext,'|LINK_A_NULL|')
           AND dpv.pkg_id = nloc.pkg_id
           AND rc.pv_id = pv.pv_id
		)   
		   
        MINUS        
        /* Disregard users notified in last 8 hours */
        SELECT nh.pv_id, nh.user_id
          FROM notification_history nh
         WHERE nh.rtag_id = :RTAG_ID
           AND TO_DATE( TO_CHAR( nh.date_time_stamp, 'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS') > 
               TO_DATE( TO_CHAR( SYSDATE-(8/24),'DD-MM-YYYY HH24:MI:SS' ), 'DD-MM-YYYY HH24:MI:SS')
       ) utn
 WHERE utn.pv_id = pv.pv_id
   AND pv.pkg_id = pkg.pkg_id
   AND pv.owner_id = usr.user_id       
ORDER BY UPPER(usr.full_name) 