-- Create table
drop table adam.FCO_INTEGRADA_RT06;
drop public synonym fco_integrada_rt06;
create table adam.FCO_INTEGRADA_RT06
(
  COMPANIA        CHAR(4) not null,
  ANIO            NUMBER not null,
  MES             NUMBER not null,
  SUCURSAL        VARCHAR2(10) not null,
  COD_CCF         CHAR(10) not null, -- corregido jcl jul-03-07
  SECUENCIA       NUMBER not null,
  NIT             VARCHAR2(16) not null,
  DV              CHAR(1) not null,
  SUMA_APORTES    NUMBER(10) not null,
  DIAS_MORA       NUMBER not null,
  INT_MORA        NUMBER(10) not null,
  TOTAL_PAGAR     NUMBER,
  TOTAL_AFILIADOS NUMBER(6) not null
)
tablespace TS_ADAMDATOS
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table adam.FCO_INTEGRADA_RT06
  add constraint PK_FCO_INTEGRADA_RT06 primary key (COMPANIA,ANIO,MES,SUCURSAL,COD_CCF)
  using index 
  tablespace TS_ADAMINDICES
;
-- Grant/Revoke object privileges 
create public synonym fco_integrada_rt06 for adam.fco_integrada_rt06;
grant all on fco_integrada_rt06 to adama;
create or replace procedure adam.sp_fco_pi_tot_ccf(p_compania in char,
                                                   p_anio     in number,
                                                   p_mes      in number,
                                                   p_sucursal in char) is
  v_rec_rt06 fco_integrada_rt06%rowtype;
  v_sec number := 0;

  cursor c1(c_compania char, c_anio number, c_mes number, c_sucursal char) return fco_integrada_rt06%rowtype is
    select a.compania,
           a.anio,
           a.mes,
           nvl(a.sucursal, ' '),
           a.cod_ccf,
           a.secuencia,
           nvl(b.registro_fiscal, ' '),
           nvl(b.digito_verificacion, 0),
           a.aportes,
           0,
           0,
           0,
           a.afiliados
      from (select compania,
                   anio,
                   mes,
                   nvl(c_sucursal, ' ') sucursal,
                   cod_ccf, -- corregido JCL Jul-04-07
                   0 secuencia,
                   sum(nvl(aporte_ccf, 0)) aportes,
                   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_ccf) a,
           vw_fco_administradoras b
     where a.compania = b.compania
       and b.tipo = 'CCF'
       and a.cod_ccf = b.codigo; -- corregido JCL Jul-04-07 substr(b.codigo, 1, 6);

begin

  open c1(p_compania, p_anio, p_mes, p_sucursal);
  loop
    fetch c1
      into v_rec_rt06;
    exit when c1%notfound;
    v_sec := v_sec + 1;
    v_rec_rt06.secuencia := v_sec;
    insert into fco_integrada_rt06 values v_rec_rt06;
  end loop;
  close c1;

end sp_fco_pi_tot_ccf;
/
