set pagesize 100;

drop table t_fy06_wagesummary
 ;

create table t_fy06_wagesummary 
  ( f_fy                char(4)              not null
  , f_cat_x             char(100)            not null
  , f_tax_c             char(2)              not null
  , f_wages_a           decimal(15,2)        not null
  , f_stock_a           decimal(15,2)        not null
  )
 ;

grant all on t_fy06_wagesummary to corpfin_user
 ;


insert into t_fy06_wagesummary
select b.f_fy
     , '(A) PAYROLL W2 WAGES  ' 
     , 'F'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
 from t_fy06_wages b
 group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select b.f_fy
     , '(A) PAYROLL W2 WAGES  ' 
     , 'C'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
 from t_fy06_wages b
where b.a_loc_c = 'CA'
 group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select b.f_fy
     , '(B) PAYROLL WAGES MATCHING HR-SAP'
     , 'F'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
  from corpfin_dbo.t_fy06_wages   b
 where exists
   (select * from corpfin_dbo.t_fy06_empcctr a
where a.f_fy    = b.f_fy
  and a.f_tax_c = 'FF'
  and a.a_sapid_n = b.a_sapid_n )
group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select b.f_fy
     , '(B) PAYROLL WAGES MATCHING HR-SAP'
     , 'C'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
  from corpfin_dbo.t_fy06_wages   b
 where exists
   (select * from corpfin_dbo.t_fy06_empcctr a
where a.f_fy    = b.f_fy
  and a.f_tax_c = 'FF'
  and a.a_sapid_n = b.a_sapid_n )
  AND b.a_loc_c = 'CA'
group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select b.f_fy
     , '(C) PAYROLL WAGES NOT IN SAP-HR'
     , 'F'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
from corpfin_dbo.t_fy06_wages   b
where not exists
   (select * from corpfin_dbo.t_fy06_empcctr a
where a.f_fy    = b.f_fy
  and a.f_tax_c = 'FF'
  and a.a_sapid_n = b.a_sapid_n )
group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select b.f_fy
     , '(C) PAYROLL WAGES NOT IN SAP-HR'
     , 'C'||substr(b.f_tax_c,1,1)         
     , sum(b.f_wages_a)
     , sum(b.f_stock_a)
from corpfin_dbo.t_fy06_wages   b
where not exists
   (select * from corpfin_dbo.t_fy06_empcctr a
where a.f_fy    = b.f_fy
  and a.f_tax_c = 'FF'
  and a.a_sapid_n = b.a_sapid_n )
  AND b.a_loc_c = 'CA'
group by b.f_fy, b.f_tax_c
/

insert into t_fy06_wagesummary
select f_fy
     , '(D) EMPWAGES (COMBINE PAYROLL WITH HR-SAP)' 
     , f_tax_c
     , sum(f_wages_a)
     , sum(f_stock_a)
from corpfin_dbo.t_fy06_empwages 
 group by f_fy, f_tax_c
/

insert into t_fy06_wagesummary
select f_fy
     , '(E) EMPWAGES - EXCLUDED SALES LOCATIONS'
     , f_tax_c
     , sum(f_wages_a)
     , sum(f_stock_a)
  from t_fy06_empwages
 where not exists
    (select * from t_fy06_cctr
      where t_fy06_cctr.f_loc_c = t_fy06_empwages.f_loc_c
        and t_fy06_cctr.f_dpt_c = t_fy06_empwages.f_dpt_c)
 group by f_fy, f_tax_c
/    

insert into t_fy06_wagesummary
select f_fy
     , '(F) EMPWAGES - LOCATIONS INCLUDED IN SURVEY'
     , f_tax_c
     , sum(f_wages_a)
     , sum(f_stock_a)
  from t_fy06_empwages
 where exists
    (select * from t_fy06_cctr
      where t_fy06_cctr.f_loc_c = t_fy06_empwages.f_loc_c
        and t_fy06_cctr.f_dpt_c = t_fy06_empwages.f_dpt_c)
 group by f_fy, f_tax_c
order by 2, 1
/   

insert into t_fy06_wagesummary
select f_fy
     , '(G) EMPWAGES - NOT-SURVEYED COST CENTERS'        
     , f_tax_c
     , sum(f_wages_a)
     , sum(f_stock_a)
  from t_fy06_empwages
 where exists
    (select * from t_fy06_cctr
      where t_fy06_cctr.f_loc_c = t_fy06_empwages.f_loc_c
        and t_fy06_cctr.f_dpt_c = t_fy06_empwages.f_dpt_c
        and t_fy06_cctr.db_stat_c in ('Z','X','N'))
 group by f_fy, f_tax_c
order by 2, 1
/    

insert into t_fy06_wagesummary
select f_fy
     , '(H) EMPWAGES - SURVEYED COST CENTERS'        
     , f_tax_c
     , sum(f_wages_a)
     , sum(f_stock_a)
  from t_fy06_empwages
 where exists
    (select * from t_fy06_cctr
      where t_fy06_cctr.f_loc_c = t_fy06_empwages.f_loc_c
        and t_fy06_cctr.f_dpt_c = t_fy06_empwages.f_dpt_c
        and t_fy06_cctr.db_stat_c not in ('Z','X','N'))
 group by f_fy, f_tax_c
order by 2, 1
/   



select f_fy
     , f_tax_c
     , f_cat_x as CATEGORY
     , to_char(sum((f_wages_a)), '999,999,990.99') as W2_WAGES_STOCK
     , to_char(sum((f_wages_a - f_stock_a)), '999,999,990.99') as WAGES
     , to_char(sum((f_stock_a)), '999,999,990.99') as STOCK
  from corpfin_dbo.t_fy06_wagesummary 
 group by f_fy, f_tax_c, f_cat_x
 order by 1, 2, 3
/
exit;

