KAF

creation tables.sql

create table TH_KAF24H(
  id_thlista NUMBER not null,
  dt_envio NUMBER,
  qnt_reg NUMBER,
  dt_criacao DATE DEFAULT (SYSDATE)
);
comment on table TH_KAF24H is 'Controle arquivos enviados para KAF24h';
comment on column TH_KAF24H.id_thlista is 'ID PK sequencial';
comment on column TH_KAF24H.dt_envio is 'Data de envio';
comment on column TH_KAF24H.qnt_reg is 'Quantidade de registros na massa';
comment on column TH_KAF24H.dt_criacao is 'Data que foi criado o arquivo';
alter table TH_KAF24H add constraint PK_TH_KAF24H primary key (id_thlista);
grant insert, update, delete on TH_KAF24H to RL_USUARIO_LETUPDATE;
grant select on TH_KAF24H to RL_USUARIO_READONLY;
CREATE OR REPLACE PUBLIC SYNONYM TH_KAF24H FOR CM.TH_KAF24H;


create table TA_KAF(
  id_thkaf number,
  id_part number,
  cod_plan VARCHAR2(4),
  vl_cobt number,
  dt_inicbt number
);
comment on table TA_KAF is 'Tabela temporária para gera os dados para a KAF24s';
comment on column TA_KAF.id_part is 'Id do participante';
comment on column TA_KAF.id_part is 'Id do participante';
comment on column TA_KAF.cod_plan is 'Código do plano';
comment on column TA_KAF.vl_cobt is 'Valor da cobertura';
comment on column TA_KAF.dt_inicbt is 'Data de inicio de cobertura';
alter table TA_KAF add constraint FK_TA_KAF foreign key (id_thkaf) references TH_KAF24H(id_thlista); 
grant insert, update, delete on TA_KAF to RL_USUARIO_LETUPDATE;
grant select on TA_KAF to RL_USUARIO_READONLY;
CREATE OR REPLACE PUBLIC SYNONYM TA_KAF FOR CM.TA_KAF;

/*
Execute o PKG e depois
*/
GRANT EXECUTE ON PKG_KAF24F TO RL_USUARIO_READONLY; 
CREATE OR REPLACE PUBLIC SYNONYM PKG_KAF24F FOR CM.PKG_KAF24F;

PKG_KAF24F.pck

CREATE OR REPLACE PACKAGE PKG_KAF24F is

  -- Author  : Roberto Gomes
  -- Created : 25/01/2019
  -- Purpose : Package para exportar dados para envio a KAF24h
  -- http://kaf24h.com.br/

   Procedure Cria_Registros;
   Procedure Apagar_Registros(V_Id in out number);
   Procedure MarcarComoEnviado(idReg in out number);
   Procedure MONTA_CSV(idReg in number, nomearq out varchar2);

end PKG_KAF24F; 
/
CREATE OR REPLACE PACKAGE BODY PKG_KAF24F is

Procedure Cria_Registros is
  v_id number;
begin
  
   select coalesce(dt_envio, 0) into v_id from th_kaf24h k
   where k.id_thlista = (select max(id_thlista) from th_kaf24h);
   --verifica se o mês anterior já foi enviado
   --só criará um novo se o mês anterior estiver como enviado
   if v_id = 0 then
     return;
   end if; 
   

   select coalesce(max(kt.id_thlista),0) + 1  into v_id from TH_KAF24H kt;
   insert into TH_KAF24H (Id_Thlista) values (v_id);

   insert into tA_kaf
   (id_part, cod_plan, vl_cobt, dt_inicbt, id_thkaf)
    select
      distinct(par.idparticipante),
      c.tp_saf || '0' ||
      case  c.vl_cobt
        when 3500 then 1
        when 4000 then 2
        when 4500 then 3
        when 5000 then 4
        else 0
     end CODIGO_PLANO,
     c.vl_cobt,
     pro.DTINICOBERTURA, v_id
   from
      tb_cobtpropespc c
      inner join tb_cobtpropespc ces on c.id_cobt_espc = ces.id_cobt_espc
      inner join proposta pro on c.id_prop = pro.idproposta
      inner join participante par on pro.idparticipante = par.idparticipante
      inner join movcontrib_atual mov on pro.idproposta = mov.idproposta
   where
      c.cd_cobt_espc = 'SAF'
     -- and mov.mesref = 201901
      and mov.flgsituacao in (1, 2, 3)
   ;
   update TH_KAF24H
      set qnt_reg = (select count(1) from ta_kaf where id_thkaf = v_id)
   where id_thlista = v_id;
 
   commit;

end;


Procedure Apagar_Registros(V_Id in out number) is
  V_okapagar number;  
Begin
  select coalesce(dt_envio, 0) into V_okapagar from TH_KAF24H t where t.id_thlista = V_Id;
  if (V_okapagar = 0) then
     delete tA_kaf    where id_thkaf   = V_Id;
     delete TH_KAF24H where id_thlista = V_Id;
     commit;
     V_Id := 1;
  else
     V_Id := 0;
  end if;
end;

Procedure MarcarComoEnviado(idReg in out number)is
  V_okVazio number;  
Begin
  select coalesce(dt_envio, 0) into V_okVazio from TH_KAF24H t where t.id_thlista = idReg;
  if (V_okVazio = 0) then
     update TH_KAF24H 
       set dt_envio = to_char(sysdate, 'YYYYMMDD')
     where id_thlista = idReg;
     commit;
     idReg := 1;
  else
     idReg := 0;
  end if;
end;

Procedure MONTA_CSV(idReg in number, nomearq out varchar2) is
 cursor RC is 
      select  
          p.nome NOME,
          p.cpf  CPF,
          p.dtnascimento  DATA_NASCIMENTO,
          'TITILAR'  CATEGORIA,
          k.cod_plan  CODIGO_PLANO,
          p.cpf  CPF_TITULAR,
          FLCN_TO_DATE(k.dt_inicbt,'YYYYMMDD')  INICIO_VIGENCIA, 
          add_months( 
          to_date(FLCN_TO_DATE(k.dt_inicbt,'YYYYMMDD')  ), 12 ) -1 as FIM_VIGENCIA
      from 
          tA_kaf k 
          inner join participante p on k.id_part = p.idparticipante
      where
          k.id_thkaf = idReg
      ;
    
    V_LINHA VARCHAR2(255) := '';
    V_ARQUIVO UTL_FILE.FILE_TYPE;
Begin
    
    SELECT 
       'previmil_' || TO_CHAR(DT_CRIACAO, 'YYYY_MM_DD') || '.CSV' INTO NomeArq
    FROM 
       TH_KAF24H
    WHERE 
       ID_THLISTA = idReg;
    
     
    
    V_ARQUIVO := UTL_FILE.FOPEN('ARQUIVOS', NomeArq, 'W'); 
    
    --Header
    V_LINHA := '"NOME";"CPF";"DATA_NASCIMENTO";"CATEGORIA";"CODIGO_PLANO";"CPF_TITULAR";"INICIO_VIGENCIA";"FIM_VIGENCIA"';
    UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
        
    --Conteudo
    for R in RC loop
        V_LINHA :=   '"' || R.NOME || '";' ||
                     '"' || R.CPF || '";' ||
                     '"' || R.DATA_NASCIMENTO || '";' ||
                     '"' || R.CATEGORIA || '";' ||
                     '"' || R.CODIGO_PLANO || '";' ||
                     '"' || R.CPF_TITULAR || '";' ||
                     '"' || R.INICIO_VIGENCIA || '";' ||
                     '"' || R.FIM_VIGENCIA || '";'
          ;
          UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
    end loop;
    --Fim conteudo
    --Trailer
    UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
    
    UTL_FILE.FCLOSE(V_ARQUIVO);
  
End;

END PKG_KAF24F;
/