create or replace procedure sp_fco_pi_tot_pensiones(p_compania in char,
                                                         p_anio     in number,
                                                         p_mes      in number,
                                                         p_sucursal in char) is
  v_rec_rt03 fco_integrada_rt03%rowtype;
  v_sec number := 0;

  cursor c1(c_compania char, c_anio number, c_mes number, c_sucursal char) return fco_integrada_rt03%ROWTYPE is
    select a.compania,
           a.anio,
           a.mes,
           a.sucursal,
           a.cod_afp,
           a.secuencia,
           nvl(b.registro_fiscal, ' '),
           nvl(b.digito_verificacion, 0),
           a.aportes,
           a.voluntarios,
           a.voluntarios_cia,
           a.aportes_fsp,
           0,
           0,
           0,
           0,
           a.afiliados
      from (select compania,
                   anio,
                   mes,
                   nvl(c_sucursal, ' ') sucursal,
                   substr(cod_afp, 1, 6) cod_afp,
                   0 secuencia,
                   sum(nvl(aporte_afp, 0)) aportes,
                   sum(nvl(voluntario_afp, 0)) voluntarios,
                   sum(nvl(voluntario_afp_cia, 0)) voluntarios_cia,
                   sum(nvl(aporte_fsp, 0)) aportes_fsp,
                   count(*) afiliados
              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)
             group by compania, anio, mes, cod_afp) a,
           vw_fco_administradoras b
     where a.compania = b.compania
       and b.tipo in ('AFP', 'FPO')
       and a.cod_afp = substr(b.codigo, 1, 6);

begin

  open c1(p_compania, p_anio, p_mes, p_sucursal);
  loop
    fetch c1
      into v_rec_rt03;
    exit when c1%notfound;
    v_sec := v_sec + 1;
    v_rec_rt03.secuencia := v_sec;
    insert into fco_integrada_rt03 
    ( compania,
      anio,
      mes,
      sucursal,
      cod_afp,
      secuencia,
      nit,
      dv,
      suma_aportes,
      suma_voluntarios,
      suma_voluntarios_cia,
      suma_fsp,
      dias_mora,
      int_mora_aportes,
      int_mora_fsp,
      total_pagar,
      total_afiliados)
    values 
    ( v_rec_rt03.compania,
      v_rec_rt03.anio,
      v_rec_rt03.mes,
      v_rec_rt03.sucursal,
      v_rec_rt03.cod_afp,
      v_rec_rt03.secuencia,
      v_rec_rt03.nit,
      v_rec_rt03.dv,
      v_rec_rt03.suma_aportes,
      v_rec_rt03.suma_voluntarios,
      v_rec_rt03.suma_voluntarios_cia,
      v_rec_rt03.suma_fsp,
      v_rec_rt03.dias_mora,
      v_rec_rt03.int_mora_aportes,
      v_rec_rt03.int_mora_fsp,
      v_rec_rt03.total_pagar,
      v_rec_rt03.total_afiliados);
  end loop;
  close c1;

end sp_fco_pi_tot_pensiones;
/
