set pagesize 100;
set define ^
;

drop table t204_bill_trend  
/

create table t204_bill_trend  
     ( f_vndr_c     char(10)      not null
     , f_vndr_x     char(30)      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_pd08_a     decimal(15,2) null
     , f_pd09_a     decimal(15,2) null
     , f_pd10_a     decimal(15,2) null
     , f_pd11_a     decimal(15,2) null
     , f_pd12_a     decimal(15,2) null
     , f_vndr_i     char(10)      null
     )
/ 
grant all on t204_bill_trend  to commbill_user
/

commit
/

drop table temp_bill_fypd
/

create table temp_bill_fypd
     ( f_fypd    char(6)   null
     , a_seq_n   integer   null
     )
/
grant all on t204_bill_fypd to commbill_user
/

commit
/

insert into temp_bill_fypd
select f_fypd1,   1 from t204_bill_fypd
union 
select f_fypd2,   2 from t204_bill_fypd
union 
select f_fypd3,   3 from t204_bill_fypd
union 
select f_fypd4,   4 from t204_bill_fypd
union 
select f_fypd5,   5 from t204_bill_fypd
union 
select f_fypd6,   6 from t204_bill_fypd
union 
select f_fypd7,   7 from t204_bill_fypd
union 
select f_fypd8,   8 from t204_bill_fypd
union 
select f_fypd9,   9 from t204_bill_fypd
union 
select f_fypd10, 10 from t204_bill_fypd
union 
select f_fypd11, 11 from t204_bill_fypd
union 
select f_fypd12, 12 from t204_bill_fypd
/

select * from temp_bill_fypd  
order by 2
/

drop table temp_bill_trend
/

create table temp_bill_trend
     ( f_vndr_c     char(10)      not null
     , f_vndr_x     char(30)      null
     , f_vndr_i     char(10)      null
     , f_fypd       char(06)      null
     , a_seq_n      integer       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_pd08_a     decimal(15,2) null
     , f_pd09_a     decimal(15,2) null
     , f_pd10_a     decimal(15,2) null
     , f_pd11_a     decimal(15,2) null
     , f_pd12_a     decimal(15,2) null
     )
/ 
grant all on temp_bill_trend to commbill_user
/
commit
/

--
-- LDAP Charges
--

insert into temp_bill_trend          
select a.f_vndr_c
     , b.f_vndr_x  
     , b.f_vndr_i   
     , c.f_fypd  
     , c.a_seq_n 
     , a.f_txn_a + a.f_adm_a    
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
  from t204_cb_ldap_charges  a 
     , tf_vndr_c             b  
     , temp_bill_fypd        c  
 where a.f_vndr_c = b.f_vndr_c
   and a.f_fypd   = c.f_fypd
/

--
-- OOL Charges
--

insert into temp_bill_trend          
select a.f_vndr_c
     , b.f_vndr_x  
     , b.f_vndr_i   
     , c.f_fypd  
     , c.a_seq_n 
     , a.f_txn_a + a.f_adm_a    
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
     , 0
  from t204_cb_ool_charges   a 
     , tf_vndr_c             b  
     , temp_bill_fypd        c  
 where a.f_vndr_c = b.f_vndr_c
   and a.f_fypd   = c.f_fypd
/


update temp_bill_trend
   set f_pd01_a = f_txn_a
 where a_seq_n =  1
/

update temp_bill_trend
   set f_pd02_a = f_txn_a
 where a_seq_n =  2 
/

update temp_bill_trend
   set f_pd03_a = f_txn_a
 where a_seq_n =  3 
/

update temp_bill_trend
   set f_pd04_a = f_txn_a
 where a_seq_n =  4 
/

update temp_bill_trend
   set f_pd05_a = f_txn_a
 where a_seq_n =  5 
/

update temp_bill_trend
   set f_pd06_a = f_txn_a
 where a_seq_n =  6 
/

update temp_bill_trend
   set f_pd07_a = f_txn_a
 where a_seq_n =  7 
/

update temp_bill_trend
   set f_pd08_a = f_txn_a
 where a_seq_n =  8 
/

update temp_bill_trend
   set f_pd09_a = f_txn_a
 where a_seq_n =  9 
/

update temp_bill_trend
   set f_pd10_a = f_txn_a
 where a_seq_n = 10 
/

update temp_bill_trend
   set f_pd11_a = f_txn_a
 where a_seq_n = 11 
/

update temp_bill_trend
   set f_pd12_a = f_txn_a
 where a_seq_n = 12 
/

insert into t204_bill_trend  
select f_vndr_c   
     , f_vndr_x   
     , 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)  
     , sum(f_pd08_a)  
     , sum(f_pd09_a)  
     , sum(f_pd10_a)  
     , sum(f_pd11_a)  
     , sum(f_pd12_a)  
     , f_vndr_i   
  from temp_bill_trend
 group by f_vndr_c   
        , f_vndr_x   
        , f_vndr_i   
/

commit
/

exit;
