set pagesize 100;

drop table t_fy06_empwages 
 ;

create table t_fy06_empwages
  ( f_fy                char(4)              not null
  , f_tax_c             char(2)              not null
  , a_sapid_n           char(8)              not null
  , a_uid_x             char(30)             not null
  , a_loc_c             char(2)              not null
  , f_loc_c             char(2)              not null
  , f_dpt_c             char(4)              not null
  , f_days_p            decimal(4,2)         not null
  , a_wages_a           decimal(15,2)        not null
  , a_stock_a           decimal(15,2)        not null
  , f_wages_a           decimal(15,2)        not null
  , f_stock_a           decimal(15,2)        not null
  )
 ;

grant all on t_fy06_empwages to corpfin_user
 ;

-- Federal Return (Calendar Year)
insert into t_fy06_empwages
select a.f_fy
     , a.f_tax_c   
     , a.a_sapid_n
     , a.a_uid_x
     , b.a_loc_c
     , a.f_loc_c
     , a.f_dpt_c 
     , a.f_days_p
     , b.f_wages_a
     , b.f_stock_a
     , (a.f_days_p * b.f_wages_a)
     , (a.f_days_p * b.f_stock_a)
from corpfin_dbo.t_fy06_empcctr a
   , corpfin_dbo.t_fy06_wages   b
where a.f_fy    = b.f_fy 
  and a.f_tax_c = 'FC'
  and b.f_tax_c = 'CY'       
  and a.a_sapid_n = b.a_sapid_n
/

-- Federal Return (Fiscal Year)
insert into t_fy06_empwages
select a.f_fy
     , a.f_tax_c   
     , a.a_sapid_n
     , a.a_uid_x
     , b.a_loc_c
     , a.f_loc_c
     , a.f_dpt_c 
     , a.f_days_p
     , b.f_wages_a
     , b.f_stock_a
     , (a.f_days_p * b.f_wages_a)
     , (a.f_days_p * b.f_stock_a)
from corpfin_dbo.t_fy06_empcctr a
   , corpfin_dbo.t_fy06_wages   b
where a.f_fy    = b.f_fy 
  and a.f_tax_c = 'FF'
  and b.f_tax_c = 'FY'       
  and a.a_sapid_n = b.a_sapid_n
/

-- CA Return (Calendar Year)
insert into t_fy06_empwages
select a.f_fy
     , a.f_tax_c   
     , a.a_sapid_n
     , a.a_uid_x
     , b.a_loc_c
     , a.f_loc_c
     , a.f_dpt_c 
     , a.f_days_p
     , b.f_wages_a
     , b.f_stock_a
     , (a.f_days_p * b.f_wages_a)
     , (a.f_days_p * b.f_stock_a)
from corpfin_dbo.t_fy06_empcctr a
   , corpfin_dbo.t_fy06_wages   b
where a.f_fy    = b.f_fy 
  and a.f_tax_c = 'CC'
  and b.f_tax_c = 'CY'
  and b.a_loc_c = 'CA'
  and a.a_sapid_n = b.a_sapid_n
/

-- CA Return (Fiscal Year)
insert into t_fy06_empwages
select a.f_fy
     , a.f_tax_c   
     , a.a_sapid_n
     , a.a_uid_x
     , b.a_loc_c
     , a.f_loc_c
     , a.f_dpt_c 
     , a.f_days_p
     , b.f_wages_a
     , b.f_stock_a
     , (a.f_days_p * b.f_wages_a)
     , (a.f_days_p * b.f_stock_a)
from corpfin_dbo.t_fy06_empcctr a
   , corpfin_dbo.t_fy06_wages   b
where a.f_fy    = b.f_fy 
  and a.f_tax_c = 'CF'
  and b.f_tax_c = 'FY'
  and b.a_loc_c = 'CA'
  and a.a_sapid_n = b.a_sapid_n
/

commit;

select b.f_tax_c
 , to_char(sum(b.f_wages_a), '999,999,990.99')
 , to_char(sum(b.f_stock_a), '999,999,990.99')
 from t_fy06_empwages b
 group by b.f_tax_c
/


--
-- PCT by CCTR for allocation of Supplies and Contracts
--
drop table t_fy06_cctrpct 
 ;

create table t_fy06_cctrpct  
  ( f_fy                char(4)              not null
  , db_rectyp_c         char(1)              not null
  , f_tax_c             char(2)              not null
  , f_stock_i           char(1)              not null
  , f_loc_c             char(2)              not null
  , f_dpt_c             char(4)              not null
  , f_cf_a              decimal(15,2)        not null
  , f_ff_a              decimal(15,2)        not null
  , f_cctr_p            decimal(6,4)         not null
  )
 ;

grant all on t_fy06_cctrpct to corpfin_user
 ;

--
-- Fiscal Year = CF / FF +- Stock  
--

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CF'    
     , 'Y'
     , f_loc_c
     , f_dpt_c
     , sum(f_wages_a)
     , 0
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'CF'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CF'    
     , 'N'
     , f_loc_c
     , f_dpt_c
     , sum(f_wages_a - f_stock_a)
     , 0
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'CF'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CF'   
     , 'Y'
     , f_loc_c
     , f_dpt_c
     , 0
     , sum(f_wages_a)
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'FF'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CF'   
     , 'N'
     , f_loc_c
     , f_dpt_c
     , 0
     , sum(f_wages_a - f_stock_a)
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'FF'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

commit;

--
-- Calendar Year = CC / FC +- Stock  
--

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CC'    
     , 'Y'
     , f_loc_c
     , f_dpt_c
     , sum(f_wages_a)
     , 0
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'CC'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CC'    
     , 'N'
     , f_loc_c
     , f_dpt_c
     , sum(f_wages_a - f_stock_a)
     , 0
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'CC'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CC'   
     , 'Y'
     , f_loc_c
     , f_dpt_c
     , 0
     , sum(f_wages_a)
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'FC'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

insert into t_fy06_cctrpct
select f_fy
     , '1'
     , 'CC'   
     , 'N'
     , f_loc_c
     , f_dpt_c
     , 0
     , sum(f_wages_a - f_stock_a)
     , 0
  from t_fy06_empwages b
 where f_tax_c = 'FC'
 group by f_fy
        , f_loc_c
        , f_dpt_c
/

commit;


insert into t_fy06_cctrpct
select f_fy
     , '2'
     , f_tax_c 
     , f_stock_i
     , f_loc_c
     , f_dpt_c
     , sum(f_cf_a)
     , sum(f_ff_a)
     , 0.0000
  from t_fy06_cctrpct  b
 group by f_fy
        , f_tax_c
        , f_stock_i
        , f_loc_c
        , f_dpt_c
/

update t_fy06_cctrpct
   set f_cctr_p = f_cf_a / f_ff_a
 where db_rectyp_c = '2'
   and f_cf_a > 0
   and f_ff_a > 0
/

commit;


select f_fy       
     , db_rectyp_c   
     , f_tax_c       
     , f_stock_i     
     , f_loc_c       
     , f_dpt_c       
     , to_char(f_cf_a, '999,999,990.99')
     , to_char(f_ff_a, '999,999,990.99')
     , to_char(f_cctr_p, '990.9999')
  from t_fy06_cctrpct
 where db_rectyp_c = '2'
   and f_cctr_p <> 0
 order by 1, 2, 3, 4, 5
/


exit;
