drop table t_fy06_suppcon 
 ;

create table t_fy06_suppcon
  ( f_fy                char(4)              not null
  , f_tax_c             char(2)              not null
  , f_loc_c             char(2)              not null
  , f_dpt_c             char(4)              not null
  , f_prof_a            decimal(15,2)        null
  , f_prof_i            char(1)              null
  , f_cctr_c            char(6)              null
  , f_cctr_i            char(1)              null
  , f_stock_i           char(1)              null
  )
 ;

grant all on t_fy06_suppcon to corpfin_user
 ;


--
-- FED Return - Fiscal Year
--
insert into t_fy06_suppcon
select '2006'
     , 'FF'      
     , a.f_loc_c
     , a.f_dpt_c
     , sum(a.f_jv_a)
     , substr(a.db_rectyp_c,1,1)
     , ' '
     , ' '
     , b.f_stock_i
  from t_fy06_apgl      a
     , t_fy06_f_stock_i b
where a.f_survey_i  = 'Y'
  and a.db_upd_i    = 'Y'
  and a.f_fypd      like '2006%'
 group by f_loc_c
        , f_dpt_c
        , substr(db_rectyp_c,1,1)
        , b.f_stock_i
        , b.f_stock_i
/

--
-- FED Return - Calendar Year
--
insert into t_fy06_suppcon
select '2006'
     , 'FC'      
     , a.f_loc_c
     , a.f_dpt_c
     , sum(a.f_jv_a)
     , substr(a.db_rectyp_c,1,1)
     , ' '
     , ' '
     , b.f_stock_i
  from t_fy06_apgl    a
     , t_fy06_f_stock_i b
where a.f_survey_i  = 'Y'
  and a.db_upd_i    = 'Y'
  and a.f_fypd      like '2006%'
  and ((a.f_fypd between '200601' and '200607')
    or (a.f_jvent_d  >= '2005/05/30'
    and a.f_jvent_d  <= '2005/12/31'))
 group by f_loc_c
        , f_dpt_c
        , substr(db_rectyp_c,1,1)
        , b.f_stock_i
/

--
-- CA Return - Fiscal Year 
--
insert into t_fy06_suppcon
select '2006'
     , b.f_tax_c
     , a.f_loc_c
     , a.f_dpt_c
     , sum(f_jv_a) * b.f_cctr_p
     , substr(a.db_rectyp_c,1,1)
     , ' '
     , ' '
     , b.f_stock_i
  from t_fy06_apgl       a
     , t_fy06_cctrpct    b    
where a.f_survey_i  = 'Y'
  and a.db_upd_i    = 'Y'
  and a.f_loc_c     = b.f_loc_c
  and a.f_dpt_c     = b.f_dpt_c
  and b.db_rectyp_c = '2'
  and b.f_tax_c     = 'CF'
  and a.f_fypd      like '2006%'
 group by a.f_loc_c
        , a.f_dpt_c
        , substr(a.db_rectyp_c,1,1)
        , b.f_cctr_p
        , b.f_stock_i
        , b.f_tax_c    
/

--
-- CA Return - Calendar Year
--
insert into t_fy06_suppcon
select '2006'
     , b.f_tax_c
     , a.f_loc_c
     , a.f_dpt_c
     , sum(f_jv_a) * b.f_cctr_p
     , substr(a.db_rectyp_c,1,1)
     , ' '
     , ' '
     , b.f_stock_i
  from t_fy06_apgl       a
     , t_fy06_cctrpct    b    
where a.f_survey_i  = 'Y'
  and a.db_upd_i    = 'Y'
  and a.f_loc_c     = b.f_loc_c
  and a.f_dpt_c     = b.f_dpt_c
  and b.db_rectyp_c = '2'
  and b.f_tax_c     = 'CC'
  and a.f_fypd      like '2006%'
  and ((a.f_fypd between '200601' and '200607')
    or (a.f_jvent_d  >= '2005/05/30'
    and a.f_jvent_d  <= '2005/12/31'))
 group by a.f_loc_c
        , a.f_dpt_c
        , substr(a.db_rectyp_c,1,1)
        , b.f_cctr_p
        , b.f_tax_c 
        , b.f_stock_i
/

commit;

select f_tax_c
     , f_stock_i
     , to_char(sum(f_prof_a), '999,999,990.99')
from corpfin_dbo.t_fy06_suppcon
group by f_tax_c 
       , f_stock_i
/

exit;

--AND '2005/05/30' <= f_date_c
--AND '2006/05/28' >= f_date_c
--AND '2005/21/31' >= f_date_c
