--
--                                                  
--  Job j204_tb_email1 -  fill table t204_tb_email_ids   
--                                                  
--

drop table t204_tb_email_ids
/

create table t204_tb_email_ids
     ( f_call_i   char(2)       null
     , a_uid_c    varchar(06)   null
     , a_badge_n  char(08)      null
     , a_uid_x    varchar(25)   null
     )
/

create table temp_tb_email_ids
     ( f_call_i   char(2)       null
     , a_badge_n  char(08)      null
     , a_uid_c    varchar(06)   null
     , a_uid1_c   varchar(10)   null
     , a_uid2_c   varchar(15)   null
     )
/

truncate table t204_tb_email_ids
/

truncate table temp_tb_email_ids
/


insert into temp_tb_email_ids
select distinct 'DT' 
     , b.a_badge_n
     , substr(b.a_uid_c,1,6)
     , b.a_uid_c
     , a.a_node_c
  from genfin_dbo.t221_callup    a
     , t204_tb_dtbill b
 where b.a_badge_n   = a.a_badge_n(+)
   and b.a_uid_c   > ' '
   and b.a_uid_c  <> 'TBLHSC'
   and b.a_uid_c  <> 'CGJEUK'
   and b.a_badge_n   not like 'EXT000%'
   and b.f_fypd = ( select f_fypd from genfin_dbo.t740_fypd
                     where to_char(sysdate - 5, 'YYYY/MM/DD')
                           between f_beg_d and f_end_d )
/

insert into temp_tb_email_ids
select 'CC' 
     , b.a_badge_n
     , substr(b.a_uid_c,1,6)
     , b.a_uid_c
     , a.a_node_c
  from genfin_dbo.t221_callup    a
     , t204_tb_ccbill b
 where b.a_badge_n   = a.a_badge_n(+)
   and b.a_uid_c   > ' '
   and b.a_uid_c  <> 'TBLHSC'
   and b.a_uid_c  <> 'CGJEUK'
   and b.f_fypd = ( select f_fypd from genfin_dbo.t740_fypd
                     where to_char(sysdate - 5, 'YYYY/MM/DD')
                           between f_beg_d and f_end_d )
/

update temp_tb_email_ids
   set a_uid1_c    = a_uid_c
     , a_uid2_c    = 'nsc.com'
 where a_uid1_c = ' '
    or a_uid1_c is null
/

update temp_tb_email_ids
   set a_uid1_c    = a_uid_c
     , a_uid2_c    = 'nsc.com'
 where rtrim(a_uid2_c)   = 'SCMH1'
    or rtrim(a_uid2_c)   = 'scmh1'
/
insert into t204_tb_email_ids
select distinct f_call_i
     , a_uid_c
     , a_badge_n
     , a_uid1_c || '@' || a_uid2_c
  from temp_tb_email_ids
 order by 3
/

drop table temp_tb_email_ids
/

select 'Number of valid user ids: ', f_call_i, count(*)
  from t204_tb_email_ids
 group by f_call_i
/ 

exit;
