exit;
drop table t_fy06_apgl    
 ;

create table t_fy06_apgl    
  ( f_fypd              char(6)              not null
  , f_jv_n              char(10)             not null
  , f_loc_c             char(2)              not null
  , f_div_c             char(2)              not null
  , f_prmacct_c         char(4)              not null
  , f_subacct_c         char(3)              not null
  , f_dpt_c             char(4)              not null
  , f_jv_x              char(50)             null
  , f_jvsrc_c           char(8)              null
  , f_jvent_d           char(10)             null
  , f_jv_a              decimal(15,2)        null
  , f_part_n            char(24)             null
  , f_po_n              char(9)              null
  , f_poln_n            char(3)              null
  , f_porls_n           char(5)              null
  , db_rectyp_c         char(10)             null
  , f_subacct_x         char(30)             null
  , f_survey_i          char(1)              null
  , f_dpt_x             char(30)             null
  , db_upd_x            char(8)              null
  , db_upd_d            char(10)             null
  , db_upd_t            char(8)              null
  , db_upd_i            char(1)              null
  , a_seq_n             int                  null
  )
 ;

grant all on t_fy06_apgl  to corpfin_user
 ;

insert into t_fy06_apgl
select f_fypd
     , f_jv_n
     , f_loc_c
     , f_div_c
     , f_prmacct_c
     , f_subacct_c
     , f_dpt_c
     , f_jv_x
     , f_jvsrc_c
     , '20'||substr(f_jvent_d,5,2)||'/'||substr(f_jvent_d,1,2)||'/'||substr(f_jvent_d,3,2)
     , f_jv_a
     , 'G/L JV' 
     , '~'
     , '~'
     , '~'
     , db_rectyp_c
     , f_subacct_x
     , f_survey_i
     , ' '
     , db_upd_x
     , db_upd_d
     , db_upd_t
     , db_upd_i
     , 0
  from temp_fy06_suppcon a
 where not exists
     ( select * from temp_fy06_jvnbr b
        where a.f_jv_n  = b.f_jv_n )
--AND a.f_dpt_c = '4441'
/

commit;

insert into t_fy06_apgl
select f_pdend_d
     , f_jv_n
     , f_loc_c
     , f_div_c
     , f_prmacct_c
     , f_subacct_c
     , f_dpt_c
     , f_vndr_x
     , f_jvtyp_c
     , '20'||substr(f_inv_d,1,2)||'/'||substr(f_inv_d,3,2)||'/'||substr(f_inv_d,5,2)
     , f_jv_a
     , f_part_n
     , f_po_n
     , f_poln_n
     , f_porls_n
     , '~'
     , '~'
     , 'N'
     , ' '
     , ' '
     , ' '
     , ' '
     , ' '
     , 0
  from corpfin_dbo.t_fy06_ap a
where f_pdend_d like '2006%'
  and f_subacct_c in (select distinct f_subacct_c
                        from corpfin_dbo.temp_fy06_suppcon )
  and exists
    ( select * from t_fy06_cctr b
       where a.f_loc_c  = b.f_loc_c
         and a.f_dpt_c  = b.f_dpt_c
         and b.db_stat_c <> 'Z' ) 
--AND a.f_dpt_c = '4441'
/

exit;
