create or replace procedure sp_fco_pi_tot_salud(p_compania in char,
                                                     p_anio     in number,
                                                     p_mes      in number,
                                                     p_sucursal in char,
                                                     p_tipo_planilla in char) is
  v_rec_rt04 fco_integrada_rt04%rowtype;
  v_sec number := 0;
  vvc_nro_aut_ige varchar2(15);
  vdc_vlr_aut_ige number(10);
  vvc_nro_aut_lma varchar2(15);
  vdc_vlr_aut_lma number(20);
  vvc_nro_radicacion varchar2(10);
  vdc_sf_aportes number(10);
  vdc_sf_upc number(10);

  cursor c1(c_compania char, c_anio number, c_mes number, c_sucursal char, c_tipo_planilla char) return fco_integrada_rt04%ROWTYPE is
    select a.compania,
           a.anio,
           a.mes,
           a.sucursal,
           TRIM(a.tipo_planilla),
           TRIM(a.cod_EPS),
           a.secuencia,
           NVL(TRIM(b.registro_fiscal), ' '),
           NVL(b.digito_verificacion, 0),
           a.aportes,
           a.UPC,
           a.nro_aut_IGE,
           a.suma_aut_IGE,
           a.nro_aut_LMA,
           a.suma_aut_LMA,
           0,
           0,
           0,
           0,
           0,
           0,
           ' ',
           0,
           0,
           0,
           0,
           0,
           a.Fosyga,
           a.afiliados,
           0
      from (SELECT compania,
                   anio,
                   mes,
                   nvl(c_sucursal, ' ') sucursal,
                   c_tipo_planilla tipo_planilla,
                   cod_EPS, --substr(cod_EPS, 1, 6) cod_eps, jcl nov-08-06
                   0 secuencia,
                   SUM(NVL(aporte_EPS, 0)) aportes,
                   SUM(NVL(UPC_adicional, 0)) upc,
                   MAX(NVL(nro_aut_IGE, 0)) nro_aut_IGE,
                   SUM(NVL(vlr_aut_IGE, 0)) suma_aut_IGE,
                   MAX(NVL(nro_aut_LMA, 0)) nro_aut_LMA,
                   SUM(NVL(vlr_aut_LMA, 0)) suma_aut_LMA,
                   SUM(NVL(IBC_EPS, 0)) * 0.01 Fosyga,
                   COUNT(*) afiliados
              FROM fco_integrada_det d
             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_EPS) a,
           vw_fco_administradoras b
     where a.compania = b.compania
       AND b.tipo = 'EPS'
       AND a.cod_EPS = b.codigo; --substr(b.codigo, 1, 6);jcl nov-08-06

begin

  open c1(p_compania, p_anio, p_mes, p_sucursal, p_tipo_planilla);
  loop
    fetch c1
      into v_rec_rt04;
    exit when c1%notfound;
    v_sec := v_sec + 1;
    v_rec_rt04.secuencia := v_sec;

   begin
   SELECT nro_aut_ige, suma_aut_ige, nro_aut_lma, suma_aut_lma,
   nro_radicacion, saldo_favor_aportes, saldo_favor_upc
   into vvc_nro_aut_ige, vdc_vlr_aut_ige, vvc_nro_aut_lma, vdc_vlr_aut_lma,
   vvc_nro_radicacion, vdc_sf_aportes, vdc_sf_upc
   FROM fco_autorizaciones_tot
   WHERE compania = p_compania
   AND anio = p_anio
   AND mes = p_mes
   AND (sucursal = p_sucursal or p_sucursal = ' ' or p_sucursal is null)
   AND tipo_planilla = p_tipo_planilla
   AND cod_EPS = v_rec_rt04.cod_EPS;
   exception when others then
       vvc_nro_aut_ige := '';
       vvc_nro_aut_lma := '';
       vvc_nro_radicacion := '';
       vdc_vlr_aut_ige := 0;
       vdc_vlr_aut_lma := 0;
       vdc_sf_aportes := 0;
       vdc_sf_upc := 0;
   end;

    if v_rec_rt04.nro_aut_ige = ' ' then
       v_rec_rt04.nro_aut_ige := nvl(vvc_nro_aut_ige, ' ');
    end if;
    if v_rec_rt04.nro_aut_lma = ' ' then
       v_rec_rt04.nro_aut_lma := nvl(vvc_nro_aut_lma, ' ');
    end if;
    if v_rec_rt04.nro_radicacion = ' ' then
       v_rec_rt04.nro_radicacion := nvl(vvc_nro_radicacion, ' ');
    end if;
    v_rec_rt04.suma_aut_ige := nvl(v_rec_rt04.suma_aut_ige, 0) + nvl(vdc_vlr_aut_ige, 0);
    v_rec_rt04.suma_aut_lma := nvl(v_rec_rt04.suma_aut_lma, 0) + nvl(vdc_vlr_aut_lma, 0);
    v_rec_rt04.saldo_favor_aportes := nvl(v_rec_rt04.saldo_favor_aportes, 0) + nvl(vdc_sf_aportes, 0);
    v_rec_rt04.saldo_favor_upc := nvl(v_rec_rt04.saldo_favor_upc, 0) + nvl(vdc_sf_upc, 0);

    insert into fco_integrada_rt04 values v_rec_rt04;
  end loop;
  close c1;

end sp_fco_pi_tot_salud;
/
