--
--                                                  
--  Job j204_mgr_email1 -  fill table t204_mgr_email_ids   
--                                                  
--

drop table t204_mgr_email_ids
/

create table t204_mgr_email_ids
     ( a_uid_c    varchar(08)   null
     , a_badge_n  char(10)      null
     , a_uid_x    varchar(25)   null
     , a_email_x  varchar(25)   null
     , f_loc_c    char(02)      null
     , f_div_c    char(02)      null
     , f_dpt_c    char(04)      null
     )
/


insert into t204_mgr_email_ids
select b.a_uid03_c
     , b.a_badge03_c
     , b.a_uid03_x
     , ' '
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
  from t204_mgr_summary     a
     , genfin_dbo.t951_dpt  b
 where a.f_loc_c       = b.f_loc_c
   and a.f_div_c       = b.f_div_c
   and a.f_dpt_c       = b.f_dpt_c
   and b.a_uid03_c    <> ' '
   and b.a_uid03_c    <> 'TBLHSC'
   and b.a_uid03_c    <> 'CGJEUK'
union
select b.a_uid04_c
     , b.a_badge04_c
     , b.a_uid04_x
     , ' '
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
  from t204_mgr_summary     a
     , genfin_dbo.t951_dpt  b
 where a.f_loc_c       = b.f_loc_c
   and a.f_div_c       = b.f_div_c
   and a.f_dpt_c       = b.f_dpt_c
   and b.a_uid04_c    <> ' '
   and b.a_uid04_c    <> 'TBLHSC'
   and b.a_uid04_c    <> 'CGJEUK'
union
select b.a_uid09_c
     , b.a_badge09_c
     , b.a_uid09_x
     , ' '
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
  from t204_mgr_summary     a
     , genfin_dbo.t951_dpt  b
 where a.f_loc_c       = b.f_loc_c
   and a.f_div_c       = b.f_div_c
   and a.f_dpt_c       = b.f_dpt_c
   and b.a_uid09_c    <> ' '
   and b.a_uid09_c    <> 'TBLHSC'
   and b.a_uid09_c    <> 'CGJEUK'
/

update t204_mgr_email_ids
   set a_email_x = rtrim(a_uid_c)||'@nsc.com'
/

select distinct a_uid_c, a_uid_x, a_email_x
  from t204_mgr_email_ids
/

select 'Number of valid Manager ids: ', count(distinct a_uid_c)
  from t204_mgr_email_ids
/ 

exit;
