set define ^
;

drop table pt_t204_tb_fix
/
create table pt_t204_tb_fix
     ( f_cat_x    char(30)   null
     , f_cnt_n    integer    null
     )
/

CREATE OR REPLACE PACKAGE p204_tb_fix
IS

type p204_tb_fix_str is ref cursor return pt_t204_tb_fix%ROWTYPE;

procedure p204_tb_fix_rpt
   ( p204_tb_fix_csr IN OUT p204_tb_fix_str
   )
   ;

end p204_tb_fix;

/

show errors;

/

CREATE OR REPLACE PACKAGE BODY p204_tb_fix
    IS

PROCEDURE p204_tb_fix_rpt
   ( p204_tb_fix_csr IN OUT p204_tb_fix_str
   )
IS
 
-- Define Variables
loc           char(02);
div           char(02);
dpt           char(04); 
badge         char(08);
uidx          char(30);
uidc          char(08);
new_loc       char(02);
new_div       char(02);
new_dpt       char(04); 
new_badge     char(08);
new_uidx      char(30);
new_uidc      char(08);
recc      number;   

--
-- Update Info From Phones.         
--
CURSOR UPDATE01 
IS
select a.f_loc_c   
     , a.f_div_c  
     , a.f_dpt_c   
     , a.a_badge_n 
     , a.a_uid_x   
     , a.a_uid_c   
     , b.f_loc_c   
     , b.f_div_c  
     , b.f_dpt_c   
     , b.a_badge_n 
     , substr(b.a_uid_x,1,30)
     , b.a_uid_c   
  from t204_tb_dtbill a
     , t204_tb_phones b             
 where a.a_auth_c   = '000000'
   and substr(a.a_phone_n,1,4)  = substr(b.a_phone_n,5,4)
   and b.a_phone_n    like '721%'
   and a.f_fypd = '200512'
   and a.a_badge_n <> b.a_badge_n
   and a.a_phone_n in
('2008'
,'2041'
/*,'2088'
,'2173'
,'2191'
,'2193'
,'2342'
,'2388'
,'2440'
,'2475'
,'2633'
,'2810'
,'2857'
,'2890'
,'3110'
,'3115'
,'3267'
,'3271'
,'3332'
,'3340'
,'3375'
,'3663'
,'3731'
,'3810'
,'3893'
,'3964'
,'4051'
,'4098'
,'4253'
,'4297'
,'4346'
,'4347'
,'4448'
,'4450'
,'4549'
,'4603'
,'4658'
,'4664'
,'5168'
,'5248'
,'5333'
,'5413'
,'5439'
,'5483'
,'5503'
,'5800'
,'5922'
,'6142'
,'6179'
,'6215'
,'6330'
,'6437'
,'6457'
,'6497'
,'6730'
,'6797'
,'6816'
,'6866'
,'6941'
,'7043'
,'7127'
,'7146'
,'7200'
,'7500'
,'7555'
,'7915'
,'8079'
,'8092'
,'8180'
,'8210'
,'8500'
,'8742'
*/
)
   for update of 
       a.f_loc_c   
     , a.f_div_c  
     , a.f_dpt_c   
     , a.a_badge_n 
     , a.a_uid_x   
     , a.a_uid_c   
  ;


begin 


--
-- Update Info From Callup.
--

<<UPD01>>
begin

recc := 0;

open UPDATE01
 ;

LOOP
fetch UPDATE01 
 into loc          
    , div          
    , dpt          
    , badge        
    , uidx         
    , uidc         
    , new_loc      
    , new_div      
    , new_dpt      
    , new_badge   
    , new_uidx     
    , new_uidc   
 ;

IF UPDATE01%NOTFOUND THEN
   goto END01;
END IF;

update t204_tb_dtbill
   set f_loc_c   = new_loc
     , f_div_c   = new_div
     , f_dpt_c   = new_dpt
     , a_badge_n = new_badge
     , a_uid_x   = new_uidx 
     , a_uid_c   = new_uidc 
 where current of UPDATE01
 ;

recc := recc + 1;

END LOOP;

END UPD01;

<<END01>>
begin

close UPDATE01
 ;
 
commit
 ;

END END01;


<<ENDIT>>

begin

open p204_tb_fix_csr for
select ' Records Updated From Callup: '
     , recc    
  from dual
   ;

end ENDIT;

end p204_tb_fix_rpt;

END p204_tb_fix;
/

show errors;

/

grant execute on p204_tb_fix to commbill_user;

/
exit;
