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,
                                                         p_tipo_planilla 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, c_tipo_planilla char) return fco_integrada_rt03%ROWTYPE is
    select a.compania,
           a.anio,
           a.mes,
           a.sucursal,
           TRIM(a.tipo_planilla),
           TRIM(a.cod_afp),
           a.secuencia,
           nvl(TRIM(b.registro_fiscal), ' '),
           nvl(b.digito_verificacion, 0),
           a.aportes,
           a.voluntarios,
           a.voluntarios_cia,
           a.aportes_fsp_sol,
           a.aportes_fsp_sub,
           0,
           0,
           0,
           0,
           0,
           a.afiliados
      from (select compania,
                   anio,
                   mes,
                   nvl(c_sucursal, ' ') sucursal,
                   c_tipo_planilla tipo_planilla,
                   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_sol,
                   sum(nvl(aporte_fsp_subsistencia, 0)) aportes_fsp_sub,
                   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)
               and tipo_planilla = c_tipo_planilla
             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, p_tipo_planilla);
  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 values v_rec_rt03;
  end loop;
  close c1;

end sp_fco_pi_tot_pensiones;
/
