create or replace procedure updateprod(
  p_pers_id  in  wrk.cntrctr.pers_id%type)
is
  v_active_ind  wrk.cntrctr.active_ind%type;
begin
  select active_ind
    into v_active_ind
    from wrk.cntrctr
   where pers_id = p_pers_id;
  
  if v_active_ind = 'A' then
    update wrk.cntrctr
       set move_date = sysdate
     where pers_id = p_pers_id;
 
    delete from prod.cntrctr
     where p.pers_id = p_pers_id;
 
    insert into prod.cntrctr
      select *
        from wrk.cntrctr
       where pers_id = p_pers_id;

    insert into prod.comm_number
      select *
        from wrk.comm_number
       where pers_id = p_pers_id;
  else
    delete from prod.cntrctr
     where pers_id = p_pers_id;
  end if;
 
  prod.rm_cntrctr_extract15(p_pers_id);
   
  commit;
 
exception
  when others then
    -- WRKX.ERROR_HANDLER.PROCESS_ERROR(sqlerrm,sqlcode,PERSID);
    dbms_output.put_line(sqlerrm);
end;
 
create or replace procedure rm_cntrctr_extract15(
  p_pers_id  in  cntrctr.pers_id%type)
is
  v_fax1  prod.tablea.fax1%type;
begin
  delete 
    from prod.tablea
   where pers_id = p_pers_id;
 
  for mycntrctr in (select a.pers_id,
                           a.last_name,
                           a.first_name,
                           a.mi,
                           a.prefix
                      from cntrctr a,
                           dictionary b
                     where b.trans_code = a.rep_status
                       and b.tab_name = 'CNTRCTR'
                       and b.col_name = 'REP_STATUS'
                       and a.pers_id = p_pers_id) loop
 
    v_fax1 := null;
 
    for myf1comm_number in (select *
                              from comm_number
                             where pers_id = mycntrctr.pers_id
                               and comm_type = 'FAX'
                               and sort_no = 1) loop
      v_fax1 := myf1comm_number.comm_no || ' ' || myf1comm_number.ext;
    end loop;
 
    insert into prod.tablea
         values (mycntrctr.pers_id,
                 mycntrctr.last_name,
                 mycntrctr.first_name,
                 mycntrctr.mi,
                 mycntrctr.prefix,
                 v_fax1);
 
  end loop;
exception
  --WHEN OTHERS THEN
   -- WRKX.ERROR_HANDLER.PROCESS_ERROR(sqlerrm,sqlcode,PERSID);
  when others then
    dbms_output.put_line(sqlerrm);
end;

create or replace procedure rm_cntrctr_extract15(
  p_pers_id  in  cntrctr.pers_id%type)
is
begin
  delete 
    from prod.tablea
   where pers_id = p_pers_id;
 
  insert into prod.tablea
    select a.pers_id,
           a.last_name,
           a.first_name,
           a.mi,
           a.prefix,
           cn.comm_no || ' ' || cn.ext
      from cntrctr a,
           dictionary b,
           comm_number cn
     where a.pers_id = p_pers_id
       and b.trans_code = a.rep_status
       and b.tab_name = 'CNTRCTR'
       and b.col_name = 'REP_STATUS'
       and cn.pers_id = a.pers_id
       and cn.comm_type = 'FAX'
       and cn.sort_no = 1;
exception
  when others then
    dbms_output.put_line(sqlerrm)
    raise;
end;