set define ^
;

drop table t204_web_fypd
/

create table t204_web_fypd
     ( f_fypd1   char(6)   null
     , f_fypd2   char(6)   null
     , f_fypd3   char(6)   null
     , f_fypd4   char(6)   null
     , f_fypd5   char(6)   null
     , f_fypd6   char(6)   null
     , f_fypd7   char(6)   null
     )
/
commit
/

insert into t204_web_fypd
select max(f_fypd) , ' ' , ' ' , ' ' , ' ' , ' ' , ' '
  from t204_cb_ool_charges
/

update t204_web_fypd  
   set f_fypd2 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd1)
/

update t204_web_fypd  
   set f_fypd3 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd2)
/

update t204_web_fypd  
   set f_fypd4 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd3)
/

update t204_web_fypd  
   set f_fypd5 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd4)
/

update t204_web_fypd  
   set f_fypd6 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd5)
/

update t204_web_fypd  
   set f_fypd7 = (select max(f_fypd) 
                    from genfin_dbo.t740_fypd
                   where f_fypd < f_fypd6)
/

drop table temp_web_fypd
/

create table temp_web_fypd
     ( f_fypd    char(6)   null
     , a_seq_n   char(1)   null
     )
/
grant all on t204_web_fypd to commbill_user
/

commit
/

insert into temp_web_fypd
select f_fypd1, '1' from t204_web_fypd
union 
select f_fypd2, '2' from t204_web_fypd
union 
select f_fypd3, '3' from t204_web_fypd
union 
select f_fypd4, '4' from t204_web_fypd
union 
select f_fypd5, '5' from t204_web_fypd
union 
select f_fypd6, '6' from t204_web_fypd
union 
select f_fypd7, '7' from t204_web_fypd
/

select * from t204_web_fypd  
/

select * from temp_web_fypd  
order by 2
/

drop table temp_web_summary
/

create table temp_web_summary
     ( f_bill_x     char(20)      not null
     , a_badge_n    char(10)      null
     , f_key_c      char(15)      null
     , a_uid_x      varchar(50)   null
     , a_uid_c      char(8)       null
     , f_loc_c      char(2)       null
     , f_div_c      char(2)       null
     , f_dpt_c      char(4)       null
     , f_fypd       char(6)       null
     , a_seq_n      char(1)       null
     , f_txn_a      decimal(15,2) null
     , f_pd01_a     decimal(15,2) null
     , f_pd02_a     decimal(15,2) null
     , f_pd03_a     decimal(15,2) null
     , f_pd04_a     decimal(15,2) null
     , f_pd05_a     decimal(15,2) null
     , f_pd06_a     decimal(15,2) null
     , f_pd07_a     decimal(15,2) null
     , f_vndr_c     char(10)      null
     )
/ 
grant all on temp_web_summary to commbill_user
/
commit
/

drop table t204_web_summary         
/

create table t204_web_summary         
     ( f_bill_x     char(20)      not null
     , a_badge_n    char(10)      null
     , f_key_c      char(15)      null
     , a_uid_x      varchar(50)   null
     , a_uid_c      char(8)       null
     , f_loc_c      char(2)       null
     , f_div_c      char(2)       null
     , f_dpt_c      char(4)       null
     , f_pd01_a     decimal(15,2) null
     , f_pd02_a     decimal(15,2) null
     , f_pd03_a     decimal(15,2) null
     , f_pd04_a     decimal(15,2) null
     , f_pd05_a     decimal(15,2) null
     , f_pd06_a     decimal(15,2) null
     , f_pd07_a     decimal(15,2) null
     , a_supbadge_n char(10)      null
     , a_supuid_c   char(8)       null
     , a_supuid_x   varchar(50)   null
     , a_ldapid_c   char(8)       null
     , f_vndr_c     char(10)      null
     )
/

grant all on t204_web_summary to commbill_user
/
commit
/

--
-- LDAP Charges
--

insert into temp_web_summary          
select a.f_vndr_c
     , a.a_badge_n 
     , a.a_uid_c 
     , a.a_uid_x
     , a.a_uid_c
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
     , a.f_fypd
     , b.a_seq_n
     , a.f_txn_a + a.f_adm_a    
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , a.f_vndr_c   
  from t204_cb_ldap_charges  a 
     , temp_web_fypd         b 
 where a.f_fypd = b.f_fypd
/

--
-- OOL Charges
--

insert into temp_web_summary          
select a.f_vndr_c
     , a.a_badge_n
     , a.a_phone_n 
     , a.a_uid_x
     , a.a_uid_c
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
     , a.f_fypd
     , b.a_seq_n
     , a.f_txn_a + a.f_adm_a
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , a.f_vndr_c   
  from t204_cb_ool_charges   a 
     , temp_web_fypd         b 
 where a.f_fypd = b.f_fypd
/

update temp_web_summary          
   set  f_bill_x = 'Wcom-DIA  '
 where  f_bill_x = 'WCOM-DIA  '
/

update temp_web_summary          
   set  f_bill_x = 'Cellphone '
 where  f_bill_x = 'ATT-TDMA  '
/

update temp_web_summary          
   set  f_bill_x = 'GSM Cell  '
 where  f_bill_x = 'ATT-GSM   '
/

update temp_web_summary          
   set  f_bill_x = 'Pager-Sky '
 where  f_bill_x = 'SKYTEL    '
/

update temp_web_summary          
   set  f_bill_x = 'Pager-Arch'
 where  f_bill_x = 'ARCH      '
/

update temp_web_summary          
   set  f_bill_x = 'AT&T Teleconference '        
 where  f_bill_x = 'ATT-TCONF '
/

update temp_web_summary          
   set  f_bill_x = 'Verizon Wireless '        
 where  f_bill_x = 'VERIZON'
/

update temp_web_summary          
   set  f_bill_x = 'AT&T Dial-In Access '        
 where  f_bill_x = 'ATT-DIALIN'
/


-- 
-- Desktop Phones
--
insert into temp_web_summary          
select 'Desktop Phone'               
     , a.a_badge_n        
     , a.a_phone_n
     , a.a_uid_x
     , a.a_uid_c 
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
     , a.f_fypd  
     , b.a_seq_n 
     , sum(a.f_call_a)
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 'DESKTOP'    
  from t204_tb_dtbill  a
     , temp_web_fypd   b 
 where a.f_fypd = b.f_fypd
 group by a.a_badge_n
        , a.a_phone_n
        , a.a_uid_x
        , a.a_uid_c
        , a.f_loc_c
        , a.f_div_c
        , a.f_dpt_c
        , a.f_fypd  
        , b.a_seq_n 
/

-- 
-- AT&T Credit Cards
--
insert into temp_web_summary          
select 'AT&T Credit Cards'
     , a.a_badge_n
     , a.a_ccard_n
     , a_uid_x
     , a.a_uid_c
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
     , a.f_fypd  
     , b.a_seq_n
     , sum(a.f_call_a)
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 'CREDITCARD' 
  from t204_tb_ccbill  a
     , temp_web_fypd   b 
 where a.f_fypd = b.f_fypd
 group by a.a_badge_n
        , a.a_ccard_n
        , a.a_uid_x
        , a.a_uid_c  
        , a.f_loc_c
        , a.f_div_c
        , a.f_dpt_c
        , a.f_fypd 
        , b.a_seq_n
/

--
-- CINGULAR WIRELESS
--
insert into temp_web_summary          
select 'Cingular Wireless'        
     , 'CING-W'    
     , a.f_loc_c||a.f_div_c||a.f_dpt_c
     , 'Cingular Cellphone'
     , a.f_loc_c||a.f_div_c||a.f_dpt_c
     , a.f_loc_c
     , a.f_div_c
     , a.f_dpt_c
     , a.f_fypd
     , b.a_seq_n
     , a.f_txn_a
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 'CINGULAR'
  from t204_ap_detail  a
     , temp_web_fypd   b 
 where a.f_fypd = b.f_fypd
-- and a.f_vndr_c <> '000206081'
   and a.f_vndr_x like 'CINGULAR WIRELESS%'
/ 


update temp_web_summary
   set f_pd01_a = f_txn_a
 where a_seq_n = '1'
/

update temp_web_summary
   set f_pd02_a = f_txn_a
 where a_seq_n = '2'
/

update temp_web_summary
   set f_pd03_a = f_txn_a
 where a_seq_n = '3'
/

update temp_web_summary
   set f_pd04_a = f_txn_a
 where a_seq_n = '4'
/

update temp_web_summary
   set f_pd05_a = f_txn_a
 where a_seq_n = '5'
/

update temp_web_summary
   set f_pd06_a = f_txn_a
 where a_seq_n = '6'
/

update temp_web_summary
   set f_pd07_a = f_txn_a
 where a_seq_n = '7'
/

insert into t204_web_summary
select f_bill_x   
     , a_badge_n  
     , f_key_c    
     , a_uid_x    
     , a_uid_c    
     , f_loc_c    
     , f_div_c    
     , f_dpt_c    
     , sum(f_pd01_a)  
     , sum(f_pd02_a)  
     , sum(f_pd03_a)  
     , sum(f_pd04_a)  
     , sum(f_pd05_a)  
     , sum(f_pd06_a)  
     , sum(f_pd07_a)  
     , ' '
     , ' '
     , ' '
     , a_uid_c    
     , f_vndr_c   
from temp_web_summary
group by f_bill_x   
     , a_badge_n  
     , f_key_c    
     , a_uid_x    
     , a_uid_c    
     , f_loc_c    
     , f_div_c    
     , f_dpt_c    
     , f_vndr_c   
/

commit
/


variable c refcursor
exec p204_web_summary.p204_web_summary_rpt ( :c )
print :c
/

update t204_web_summary
   set f_loc_c = 'ZM'     
 where f_loc_c = 'EM'           
/ 

update t204_web_summary
   set f_loc_c = 'ZG'     
 where f_loc_c = 'SG'           
/ 

--select a_uid_c, a_badge_n, a_uid_x, f_bill_x, f_pd01_a from t204_web_summary
select f_bill_x
     , f_vndr_c
     , count(*)
  from t204_web_summary 
 group by f_bill_x
        , f_vndr_c
/
exit;
