create or replace procedure sp_fco_pi_tot_esap(p_compania in char,
                                                    p_anio     in number,
                                                    p_mes      in number,
                                                    p_sucursal in char) is
  v_rec_rt09 fco_integrada_rt09%rowtype;

  cursor c1(c_compania char, c_anio number, c_mes number, c_sucursal char) return fco_integrada_rt09%rowtype is
    select compania,
           anio,
           mes,
           nvl(c_sucursal, ' ') sucursal,
           1 secuencia,
           '899999054' nit,
           '7' dv,
           sum(nvl(aporte_esap, 0)) valor,
           0,
           0,
           null
      from fco_integrada_det
     where compania = c_compania
       and anio = c_anio
       and mes = c_mes
       and (sucursal = c_sucursal or c_sucursal is null)
       and nvl(aporte_esap, 0) <> 0
     group by compania, anio, mes;

begin

  open c1(p_compania, p_anio, p_mes, p_sucursal);
  loop
    fetch c1
      into v_rec_rt09;
    exit when c1%notfound;
    insert into fco_integrada_rt09 
    (compania,
    anio,
    mes,
    sucursal,
    secuencia,
    nit,
    dv,
    valor,
    dias_mora,
    int_mora,
    total_pagar)
     values 
     (v_rec_rt09.compania,
    v_rec_rt09.anio,
    v_rec_rt09.mes,
    v_rec_rt09.sucursal,
    v_rec_rt09.secuencia,
    v_rec_rt09.nit,
    v_rec_rt09.dv,
    v_rec_rt09.valor,
    v_rec_rt09.dias_mora,
    v_rec_rt09.int_mora,
    v_rec_rt09.total_pagar);
  end loop;
  close c1;

end sp_fco_pi_tot_esap;
/
