Skip to content

Instantly share code, notes, and snippets.

@JRGGRoberto

JRGGRoberto/Oracle_Qry_where_case.sql

Last active December 20, 2018 16:56
Show Gist options
  • Save JRGGRoberto/a4426c470f0cf116d482b13940ed476e to your computer and use it in GitHub Desktop.
Save JRGGRoberto/a4426c470f0cf116d482b13940ed476e to your computer and use it in GitHub Desktop.
Download ZIP
Oracle SQL -2
Raw
Oracle_Qry_where_case.sql
select
'.config' doc,
case when af.dtliberacao >= t.tp_prms then 1 else 0 end Imprime
from tb_prms_glob t,
assistfinanc af
where
t.nm_prms = 'KIT_IMPRESSAO'
and af.numcontratoemp = :NUMCONTRATO
union
select * from
(select
f.doc, f.Imprime
from
assistfinanc af
inner join averbadora av on af.idaverbadora = av.idaverbadora
inner join afprodutor t on af.numcontratoemp = t.numcontratoemp
inner join (select col1 Nov_Par, col2 OrgGrupo, col3 Imprime, cd_cod1 Doc, dtini, dtfim
from faixas f where f.idtabela = 2103) f on
(case av.idgrupo
when 1 then 1
when 2 then 2
when 3 then 3
else 5 end ) = f.OrgGrupo
where
(case t.flgtpvenda
when 2 then 1
when 4 then 1
when 3 then 2
end ) = f.Nov_Par and
af.numcontratoemp = :NUMCONTRATO and
af.dtliberacao between f.dtini and f.dtfim
order by f.doc);
Raw
PKG_EPHARMA_pck.sql
CREATE OR REPLACE PACKAGE PKG_EPHARMA is
-- Author : Roberto Gomes
-- Created : 19/10/2018
-- Purpose : Package para exportar dados para envio a E-Pharma
Procedure Cria_Registros(V_qntR OUT number);
Procedure Apagar_Registros(Id in out number);
Procedure Monta_RegsBenef(idReg in number, nomeArq OUT varchar2);
Procedure Monta_RegsEnder(idReg in number, nomeArq OUT varchar2);
Procedure MontaArquivos(idReg in number,
nomeArqB OUT varchar2,
nomeArqE OUT varchar2,
nomeArqC OUT varchar2);
Procedure MarcarComoEnviado(idReg in number);
end PKG_EPHARMA; --a
/
CREATE OR REPLACE PACKAGE BODY PKG_EPHARMA is
codCliente CONSTANT varchar2(6) := '003563'; --'001042';
NomeArq varchar2(21);
function PAD (P_Campo varchar2, P_Mask varchar2, P_Tam pls_integer) return varchar2 is
Temp varchar2(1500);--(1024);
begin
if P_Mask = '9' then
Temp:= lpad(P_Campo, P_Tam, '0');
else
Temp:= RPAD(P_Campo, P_Tam); --Fnc_ColocaBranco(P_Campo, P_Tam);
end if;
return Temp;
end;
function dt_formt(P_dt in number) return varchar2 is
vn_dtform varchar2(8);
begin
select REPLACE(FLCN_TO_DATE(P_dt,'YYYYMMDD'),'/', '') into vn_dtform from dual;
return vn_dtform;
end dt_formt;
Function Monta_Header(V_DtArq in varchar2, V_HArq in varchar2 )
RETURN varchar2 is
V_LINHA VARCHAR2(160) := '';
seq_Arq number;
Begin
-- Des cric?es : NomeCampo In Fin Tp Siz Obr Conteudo
V_LINHA := '00' || -- 1 Tipo Registro 1 2 9 2 s 00:
'0310' || -- 2 Vers?o 3 6 9 4 s constante '0310'
'002944' || -- 3 Codigo agregador 7 12 9 6 s constante '002103'
seq_Arq || -- 4 Sequencial do arq 13 18 9 6 s
V_DtArq || -- 5 Data d Gerac arq 19 28 X 10 s DD/MM/AAAA
V_HArq || -- 6 Hora d Gerac arq 29 33 X 5 s HH:MM
'C' || -- 7 Tip de Carga 34 34 X 1 s Constante 'C'
PAD(' ','X',126) || -- 8 Filer 35 154 X 120 s
-- PAD(V_NReg,'9',6) -- 9 Num registro 155 160 9 6 s N seq do reg do arquivo - iniciando em 000001
PAD(1,'9',6) -- 9 Num registro 155 160 9 6 s N seq do reg do arquivo - iniciando em 000001
;
return V_LINHA;
End;
Function Monta_Trailer(V_TotalReg in number)-- , V_NReg in number)
RETURN varchar2 is
V_LINHA VARCHAR2(160) := '';
Begin
-- Des cric?es : NomeCampo In Fin Tp Siz Obr Conteudo
V_LINHA := '99' || -- 1 Tipo Registro 1 2 9 2 s Constante '99'
PAD(V_TotalReg + 1, '9', 6) || -- 2 Total reg+Head+Tra 3 8 9 6 s Total de registro incluindo Header e Trailer
PAD(' ','X',146) || -- 3 Filer 9 154 X 146 s -- vazio
PAD(V_TotalReg + 1, '9', 6) -- 4 N seq do reg arq 155 160 9 6 s Numero sequencial do registro no arquivo -
;
return V_LINHA;
End;
Procedure Monta_RegsBenef (idReg in number, nomeArq OUT varchar2) is
cursor RCursor is
select
distinct(par.cpf),
trim(par.nome) as nome,
TO_DATE(par.dtnascimento, 'YYYYMMDD') DTNASC,
trim(par.sexo) as sexo,
eph.matphar
from ta_epharma eph
left join participante par on eph.idparticipante = par.idparticipante
where
eph.ativo = 1 and
eph.id_thpharma = idReg
;
V_LINHA VARCHAR2(255) := '';
V_ARQUIVO UTL_FILE.FILE_TYPE;
Y number;
dtInicio VARCHAR2(10);
V_hora varchar2(5);
Begin
select
to_char(sysdate, 'DD/MM/YYYY'), To_char(sysdate,'hh24:mi')
into dtInicio, V_hora
from dual;
Y := 1;
NomeArq := 'P' || codCliente || lpad(idReg, 6, '0') || '.002.txt';
V_ARQUIVO := UTL_FILE.FOPEN('ARQUIVOS', NomeArq, 'W');
--Header
V_LINHA := Monta_Header(dtInicio, V_hora);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
--Conteudo
for R in RCursor loop
Y := Y + 1;
-- Descric?es : NomeCampo In Fin Tp Siz Obr Conteudo
V_LINHA := '01' || --01 Tipo Registro 1 2 9 2 s 01
'I' || --02 Ac?o 3 3 X 1 s I inclus?o / A Alterac?o /R Readminss?o
codCliente || --03 Codigo cliente 4 9 9 6 s constante 001042
'054501' || --04 Codigo beneficio 10 15 9 6 s zeros a esquerda Constante - '008980'
PAD(r.nome,'X',50) || --05 Nome do cliente 16 65 X 50 s
PAD(r.matphar,'X',20) ||-- 06 Num da matricula 66 85 X 20 s
'00' || --07 Cod d beneficiario 86 87 9 2 s - '00' constante
r.dtnasc || --08 Data de nascimento 88 97 X 10 s DD/MM/AAAA
' ' || --09 Cod estrutura org 98 111 X 14 n branco
r.sexo || --10 sexo 112 112 X 1 s
dtInicio || --11 Inicio de vig 113 122 X 10 s Data de envio do arquivo
' ' || --12 Termino de vig 123 132 X 10 n branco
r.cpf || --13 CPF do benef 133 143 9 11 n
' ' || --14 Filer 144 154 X 11 s
PAD(Y,'9',6) --15 Num Seq 155 160 9 6 s
;
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
end loop;
--Fim conteudo
--Trailer
V_LINHA := Monta_Trailer(Y);--, idReg);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
UTL_FILE.FCLOSE(V_ARQUIVO);
end;
---------------------------------------------
Procedure Monta_RegsCancelados(idReg in number, nomeArq OUT varchar2) is
cursor RCursor is
select
epa.matphar
from
ta_epharma epa
where
epa.ativo = 0
and epa.id_thpharma = idReg;
V_LINHA VARCHAR2(255) := '';
V_ARQUIVO UTL_FILE.FILE_TYPE;
Y number;
dtInicio VARCHAR2(10);
V_hora varchar2(5);
Begin
select
to_char(sysdate, 'DD/MM/YYYY'), To_char(sysdate,'hh24:mi')
into dtInicio, V_hora
from dual;
Y := 1;
NomeArq := 'P' || codCliente || lpad(idReg, 6, '0') || '.006.txt';
V_ARQUIVO := UTL_FILE.FOPEN('ARQUIVOS', NomeArq, 'W');
--Header
V_LINHA := Monta_Header(dtInicio, V_hora);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
--Conteudo
for R in RCursor loop
Y := Y + 1;
-- Descric?es : NomeCampo In Fin Tp Siz Obr Conteudo
V_LINHA := '06' || --01 Tipo Registro 1 2 9 2 s 01
codCliente || --03 Codigo cliente 3 8 9 6 s constante 001042
'054501' || --04 Codigo beneficio 9 14 9 6 s zeros a esquerda Constante - '008980'
PAD(r.matphar,'X',20) ||-- 06 Num da matricula 15 34 X 20 s
dtInicio || --08 Data de cancelament 35 44 X 10 s DD/MM/AAAA
PAD(' ','X',110) || --14 Filer 45 154 X 110 s
PAD(Y,'9',6) --15 Num Seq 155 160 9 6 s
;
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
end loop;
--Fim conteudo
--Trailer
V_LINHA := Monta_Trailer(Y);--, idReg);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
UTL_FILE.FCLOSE(V_ARQUIVO);
end;
--------------------------------------------------
Procedure Monta_RegsEnder(idReg in number, nomeArq OUT varchar2) is
cursor RCursor is
select
ta.matphar,
FNC_SIMPLIFICA_TEXTO(enr.endereco) endereco,
nvl(enr.numero,0) numero,
decode(enr.complemento,null,' ',FNC_SIMPLIFICA_TEXTO(enr.complemento)) complemento,
FNC_SIMPLIFICA_TEXTO(enr.bairro) bairro,
cid.descricao cidade, uf.codigo uf, enr.cep
from
ta_epharma ta
left join enderecos enr on ta.idparticipante = enr.idparticipante and enr.flgativo = 1 and enr.flgcorresp = 1
left join municipio cid on enr.idmunicipio = cid.idmunicipio
left join tabgerais uf on cid.uf = uf.idtabela and uf.idtptabela = 1
where
ta.ativo = 1 and
ta.id_thpharma = idReg
;
V_LINHA VARCHAR2(160) := '';
V_ARQUIVO UTL_FILE.FILE_TYPE;
Y number;
dtInicio VARCHAR2(10);
V_hora varchar2(5);
Begin
select
to_char(sysdate, 'DD/MM/YYYY'), To_char(sysdate,'hh24:mi')
into dtInicio, V_hora
from dual;
Y := 1;
NomeArq := 'P' || codCliente || lpad(idReg, 6, '0') || '.003.txt';
V_ARQUIVO := UTL_FILE.FOPEN('ARQUIVOS', NomeArq, 'W');
--Header
V_LINHA := Monta_Header(dtInicio, V_hora);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
--Conteudo
for R in RCursor loop
Y := Y + 1;
-- Descric?es : NomeCampo In Fin Tp Siz Obr Conteudo
V_LINHA := '02' || --01 Tipo Registro 1 2 9 2 s 02
codCliente || --02 Codigo cliente 3 8 9 6 s Preenchido com zeros, informado pela e-Pharma
'054501' || --03 Codigo Benefi 9 14 9 6 s Preenchido com zeros, informado pela e-Pharma ?
PAD(r.matphar,'X',20) || --04 Mat do titular 15 34 X 20 s
PAD(r.endereco,'X',40) || --05 Endereco do client 35 74 X 40 s
PAD(r.Numero,'X', 5) || --06 Numero 75 79 X 5 s
PAD(r.Complemento,'X',10) || --07 Complemento 80 89 X 10 s
PAD(r.bairro,'X',20) || --08 Bairro 90 109 X 20 s
PAD(r.cidade,'X',30) || --09 Cidade 110 139 X 30 s
r.uf || --10 UF 140 141 X 2 s
r.cep || --11 CEP 142 149 X 8 s
' ' || --12 Filer 150 154 X 5 s
PAD(Y,'9',6) --13 Num Seq 155 160 9 6 s
;
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
end loop;
--Fim conteudo
--Trailer
V_LINHA := Monta_Trailer(Y); --, idReg);
UTL_FILE.PUT_LINE(V_ARQUIVO, V_LINHA);
UTL_FILE.FCLOSE(V_ARQUIVO);
end;
Procedure Cria_Registros(V_qntR OUT number) is
--
V_Seq number;
V_Hora varchar2(5);
V_Data varchar2(10);
v_qnt number;
v_enviado number;
begin
select count(1) into v_qnt from th_epharma;
if v_qnt > 0 then
select nvl(ep.dt_envio, -1) into v_enviado
from th_epharma ep
where ep.id_thpharma = (select max(a.id_thpharma) from th_epharma a);
if v_enviado = -1 then
V_qntR := -1;
return;
end if;
end if;
select nvl(max(id_thpharma),0) + 1,
to_char(sysdate,'hh24:mi'),
to_char(sysdate, 'DD/MM/YYYY')
into V_Seq, V_Hora, V_Data
from th_epharma;
--
V_seq := V_Seq;
--
insert into th_epharma (id_thpharma) values (V_seq);
insert into tmp_acomp(id) values (0);
--Insert Proprosta ativas
insert into TT_EPHARMA(id_part, tp_cont, contrat, Ativo)
select
par.idparticipante, 'pro' tipo, max(pro.numproposta) numproposta, 1 as ativo
from
proposta pro
inner join participante par on pro.idparticipante = par.idparticipante
inner join v_regagagrup v on pro.idregiao = v.IDS
inner join regiao reg on v.IDS = reg.idregiao
where
pro.motivocanc = 1 and
reg.codigo in (1, 3, 16) -- / 1 RJ, 3 SP, 16 Recife
and par.flgsituacao not in (1395, 1396) -- SUSPENSO POR ÓBITO / -- CANCELADO POR ÓBITO
and not exists (select 1 from tb_epharma e where e.idparticipante = par.idparticipante)
group by par.idparticipante, par.cpf, par.nome, 'tipo' ;
--Insert AF ativas
insert into TT_EPHARMA(id_part, tp_cont, contrat, Ativo)
select
par.idparticipante, 'af' tipo, max(asf.numcontrato) numproposta, 1 as ativo
from
assistfinanc asf
inner join PARTICIPANTE par on asf.idparticipante = par.idparticipante
inner join v_regagagrup v on asf.idregiao = v.IDS
inner join regiao reg on v.IDS = reg.idregiao
where
asf.motivocanc = 1
and par.flgsituacao not in (1395, 1396)
and reg.codigo in (1, 3, 16) -- / 1 RJ, 3 SP, 16 Recife
AND NOT EXISTS
(SELECT 1 FROM TT_EPHARMA t where t.id_part = par.idparticipante )
and not exists (select 1 from tb_epharma e where e.idparticipante = par.idparticipante)
and exists
(select 1
from AMORTIZACAO amrt
where
amrt.numcontratoemp = asf.numcontratoemp and
amrt.dtrecebimento >= 20171101)
group by par.idparticipante, par.cpf, par.nome, 'tipo' ;
--Insert canceladas Af e Propostas
--Insere cancelamentos Proposta
insert into TT_EPHARMA(id_part, tp_cont, contrat, Ativo)
select
tb.idparticipante, tb.tp_cont, tb.contrato, 0
from
tb_epharma tb
where
not exists (select 1 from proposta pro
where
pro.idparticipante = tb.idparticipante
and tb.tp_cont = 'pro'
and pro.motivocanc = 1 )
and not exists (select 1 from assistfinanc asf
where
asf.idparticipante = tb.idparticipante
and tb.tp_cont = 'af'
and asf.motivocanc = 1 )
and tb.flg_ativo = 1
;
for R in ( select
lpad(par.codparticipante, 7,'0') || '-' || lpad(par.idparticipante, 5,'0') matphar,
par.idparticipante, ep.tp_cont, ep.contrat, ep.ativo
from
TT_EPHARMA ep
inner join participante par on ep.id_part = par.idparticipante
) LOOP
insert into ta_epharma
(id_thpharma, matphar, idparticipante, ativo, tp_cont, contrato)
values
(V_seq, r.matphar, r.idparticipante, r.ativo, r.tp_cont, r.contrat);
end Loop R;
--
--
select count(1)
into V_qntR
from ta_epharma
where id_thpharma = V_seq;
--
if nvl(V_qntR,0) > 0 then
update th_epharma th
set th.qnt_reg = V_qntR
where th.id_thpharma = V_seq;
--
commit;
else
rollback;
end if;
end;
Procedure Apagar_Registros(Id in out number) is
V_qnt number;
Begin
select count(1) into V_qnt
from th_epharma
where Id = (select max(id_thpharma) from th_epharma);
if V_qnt > 0 then
delete ta_epharma where id_thpharma = Id;
delete th_epharma where id_thpharma = Id;
commit;
end if;
Id := V_qnt;
end;
Procedure MontaArquivos(idReg in number,
nomeArqB OUT varchar2,
nomeArqE OUT varchar2,
nomeArqC OUT varchar2)
is
Begin
Monta_RegsBenef(idReg, nomeArqB);
Monta_RegsEnder(idReg, nomeArqE);
Monta_RegsCancelados(idReg, nomeArqC);
end;
Procedure MarcarComoEnviado(idReg in number) is
V_DtEnv number;
begin
V_dtEnv:= Pkg_FuncGerais.SysDate_Int;
for R in ( select
ep.matphar, ep.idparticipante, ep.ativo, ep.tp_cont, ep.contrato
from
ta_epharma ep
where ep.id_thpharma = idReg
) LOOP
if r.ativo = 1 then
insert into tb_epharma
(idparticipante, flg_ativo, dt_inclusao, tp_cont, contrato)
values (r.idparticipante, 1, V_DtEnv, r.tp_cont, r.contrato);
else
update tb_epharma set
flg_ativo = 0,
dt_alteracao = V_DtEnv
where idparticipante = r.idparticipante;
end if;
end Loop R;
update th_epharma aa set
aa.dt_envio = V_DtEnv
where aa.id_thpharma = idReg;
commit;
end;
END PKG_EPHARMA;
/
Raw
PKG_PASS_SEG_pck.sql
CREATE OR REPLACE PACKAGE PKG_PASS_SEG is
-- Author : Roberto Gomes
-- Created : 09:15 08/05/2018
-- Purpose : Package com conjunto de operac?es para gerar arquivos de envio para seguradora
-- Cria massa de movimentac?o
Procedure CRIAR_MASSA( P_MES_VIT IN VARCHAR,
P_IDUSUARIO IN NUMBER,
P_TP_SEG IN VARCHAR,
P_QTDREGS OUT NUMBER,
P_IDSEGURADORA IN NUMBER);
-- Exclui registros de movimentac?o
procedure APAGAR_MASSA(P_IDHST_PASS in number);
-- Exclui registros de pagamento
procedure APAGAR_MASSA_PAG(P_IDSEGPGTO in number);
procedure GERA_ADSAO_ENVIO(P_IDHST_PASS in number);
-- Ler arq retorno [MOV e PAG] depende header
-- Procedure LER_ARQUIVO(P_IDHST_PASS in number, l_filename in varchar2, P_TeveErro out number);
Procedure LER_ARQUIVO_03(P_IDHST_PASS in number, l_filename in varchar2, P_TeveErro out number);
Procedure LER_ARQUIVO_11(P_IDHST_PASS in number, l_filename in varchar2, P_TeveErro out number);
-- Cria massa de pagamentos
Procedure Cria_Massa_financ(P_IDHST_PASS in number, P_IDUSUARIO IN NUMBER);
procedure GERA_PAG_ENVIO(P_IDHST_PASS in number);
--cria a massa inconsistente de pagamento
Procedure Cria_Massa_financ2(P_IDHST_PASS in number, P_IDUSUARIO IN NUMBER);
end PKG_PASS_SEG;
/
CREATE OR REPLACE PACKAGE BODY PKG_PASS_SEG is
Vs_MensagErros varchar2(4096);
function PAD (P_Campo varchar2, P_Mask varchar2, P_Tam pls_integer) return varchar2 is
Temp varchar2(1500);--(1024);
begin
if P_Mask = '9' then
Temp:= lpad(P_Campo, P_Tam, '0');
else
Temp:= RPAD(P_Campo, P_Tam); --Fnc_ColocaBranco(P_Campo, P_Tam);
end if;
return Temp;
end;
Procedure ESCREVE_ARQUIVO(P_clob CLOB, P_filename varchar2, P_type Char) is
l_file UTL_FILE.FILE_TYPE;
l_buffer VARCHAR2(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
JRGG varchar2(32767);
BEGIN
l_file := UTL_FILE.fopen('ARQUIVOS', P_filename, P_type, 32767);
LOOP
DBMS_LOB.read (P_clob, l_amount, l_pos, l_buffer);
JRGG := utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(l_buffer));
UTL_FILE.put(l_file, JRGG);
l_pos := l_pos + l_amount;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.fclose(l_file);
WHEN OTHERS THEN
UTL_FILE.fclose(l_file);
RAISE;
UTL_FILE.fclose(l_file);
END;
function dt_formt(P_dt in number) return varchar2 is
vn_dtform varchar2(8);
begin
select REPLACE(FLCN_TO_DATE(P_dt,'YYYYMMDD'),'/', '') into vn_dtform from dual;
return vn_dtform;
end dt_formt;
-- Cria massa de movimentac?o
procedure CRIAR_MASSA (P_MES_VIT IN VARCHAR,
P_IDUSUARIO IN NUMBER,
P_TP_SEG IN VARCHAR,
P_QTDREGS OUT NUMBER,
P_IDSEGURADORA IN NUMBER) IS
/*
Junho 2018
Roberto Gomes
Este procedimento foi baseado no PRC_SEGURADORA, com altereac?o
do filtro MES_VIG no lugar do MESREF.
*/
V_grupos varchar2(8);
CURSOR C_SEG(P_MES_VIT IN VARCHAR, P_IDSEGURADORA IN NUMBER, V_grupos IN VARCHAR) IS
select
IDPROPOSTA, IDPROPCOBERT, NUMPROPOSTA, NU_CERT, CPF,
VLRECEBIDO, VLPREMIO, VLCAPITAL,
IDPARTICIPANTE, IDGRUPO, IDPLANOCOM, CODIGO, ID2,
TABCALCCOBERT, DTNASCIMENTO, flgsituacao
from
tt_pass_seg;
-- vlrecebido,
-- vlpremio,
-- CURSORES
RC_SEG C_SEG%ROWTYPE;
-- CONTROLES DE EXECUCAO
V_INSTRUCAO VARCHAR2(4096);
V_FLGEXECUCAO PLS_INTEGER;
V_IDCONTPROC PLS_INTEGER;
V_TAXA number;
V_DTCALC PLS_INTEGER;
V_VLCAPITAL number;
V_ARQV VARCHAR2(35);
V_SERIAL NUMBER;
V_IDHST_PASS_SEG NUMBER;
PROCEDURE INSERETABSEGURO IS
BEGIN
INSERT INTO TA_PASS_SEG (
idth_pass_seg,
numproposta,
nu_cert,
cpf,
vlrecebido,
vlpremio,
vlcapital,
idgrupo,
codigo,
idparticipante,
tp_operacao,
idpropcobert,
Flgsituacao
)
VALUES (
V_IDHST_PASS_SEG,
RC_SEG.NUMPROPOSTA,
RC_SEG.NU_CERT,
RC_SEG.CPF,
RC_SEG.VLRECEBIDO,
RC_SEG.VLPREMIO,
V_VLCAPITAL,
RC_SEG.IDGRUPO,
RC_SEG.CODIGO,
RC_SEG.IDPARTICIPANTE,
RC_SEG.ID2,
RC_SEG.IDPROPCOBERT,
RC_SEG.Flgsituacao
);
END INSERETABSEGURO;
-- PROCEDURE CALCULA
PROCEDURE CALCULOSDOSEGURO IS
BEGIN
V_DTCALC:= FNC_INCMES(P_MES_VIT, 1) || '01';
-- CALCULO DE PREMIO, TAXA E VALOR DO CAPITAL DO SEGURADO
IF (RC_SEG.ID2 = 'AL') or (RC_SEG.ID2 = '__')
or (RC_SEG.ID2 = 'CA')THEN-- SO PEGA VALORES DAS ALTERAC?ES
V_TAXA := FNC_BUSCATAXASEGUR(RC_SEG.IDPROPOSTA, RC_SEG.TABCALCCOBERT, RC_SEG.DTNASCIMENTO, V_DTCALC);
V_VLCAPITAL:= ROUND(((RC_SEG.VLPREMIO * 1000)/V_TAXA), 2);
ELSE
V_VLCAPITAL := RC_SEG.VLCAPITAL;
END IF;
-- FIM CALCULO
END CALCULOSDOSEGURO;
-- ROTINA PRINCIPAL
BEGIN
IF P_TP_SEG = 'AP' THEN
V_grupos := '549';
ELSE
V_grupos := '547, 548';
END IF;
-- limpa a tabela // apesar de ser um tt oracle, esta com a configurac?o
-- on commit preserve rows;
PRC_TRUNCATE('TT_PASS_SEG');
--alimenta tabela temporaria
insert into TT_PASS_SEG
select -- INCLUS?O INICIO
mov.idproposta, mov.idpropcobert,
pro.numproposta, null nu_cert, PAR.CPF,
mov.vlpgto vlrecebido,
mov.vlprev vlpremio,
tar.vlcobert vlcapital,
pro.idparticipante, pla.idgrupo, pro.idplanocom, pla.codigo,
'IN' id2, pcb.tabcalccobert, par.dtnascimento, mov.flgsituacao
from
proposta pro
inner join planocomercial pla on pro.idplanocom = pla.idplanocom
inner join propcobert ppc on pro.idproposta = ppc.idproposta
inner join cobertura cob on ppc.idcobertura = cob.idcobertura
inner join participante par on pro.idparticipante = par.idparticipante
inner join movcontrib_atual mov on ppc.idpropcobert = mov.idpropcobert and mov.mes_vig = P_MES_VIT
inner join tarifa tar on ppc.idpropcobert = tar.idpropcobert
INNER JOIN PRODCOBERT PCB ON ( PPC.IDPRODUTO = PCB.IDPRODUTO
AND PPC.IDCOBERTURA = PCB.IDCOBERTURA)
where
cob.flgespecial = 1
and pro.mescancelamento = 999999
and substr(pro.dtinicobertura,1,6) = P_MES_VIT --<
and mov.mes_vig||'01' = pro.dtinicobertura
and not exists (select r.id_propcobt
from tb_repasspara r
where r.nu_cert is not null
and r.id_propcobt = ppc.idpropcobert
)
and tar.flgmotivo = 1
AND (
(P_TP_SEG='AP' AND (PLA.idgrupo = 549)) OR
(P_TP_SEG='VG' AND (PLA.idgrupo in (547, 548)) )
)
-- INCLUS?O FIM
;
INSERT INTO TT_PASS_SEG -- INSERE TODO O RESTO PARA DEPOIS CLASSIFICAR
SELECT
PPC.IDPROPOSTA, R.ID_PROPCOBT, PRO.NUMPROPOSTA, R.NU_CERT,PAR.CPF,
mov.vlpgto VLRECEBIDO, mov.vlprev VLPREMIO,
R.VLCAPITAL, PAR.IDPARTICIPANTE,
R.IDGRUPO, PRO.IDPLANOCOM, PLA.CODIGO,
'__' id2, pcb.tabcalccobert, par.dtnascimento, mov.flgsituacao
FROM
TB_REPASSPARA R
INNER JOIN PROPCOBERT PPC ON R.ID_PROPCOBT = PPC.IDPROPCOBERT
INNER JOIN PROPOSTA PRO ON PPC.IDPROPOSTA = PRO.IDPROPOSTA
INNER JOIN PARTICIPANTE PAR ON PRO.IDPARTICIPANTE = PAR.IDPARTICIPANTE
INNER JOIN PLANOCOMERCIAL PLA ON PRO.IDPLANOCOM = PLA.IDPLANOCOM
INNER JOIN PRODCOBERT PCB ON ( PPC.IDPRODUTO = PCB.IDPRODUTO
AND PPC.IDCOBERTURA = PCB.IDCOBERTURA)
inner join movcontrib_atual mov on ppc.idpropcobert = mov.idpropcobert
WHERE
R.TP_PLA = P_TP_SEG
AND R.FLG_ATIVO = 1 -- LISTA AS PROPOSTAS QUE EST?O NA BASE QUE N?O FORAM CANCELADAS
AND MOV.MES_VIG = (SELECT MAX(AUX.MES_VIG)
FROM MOVCONTRIB_ATUAL AUX
WHERE AUX.MES_VIG <= P_MES_VIT
AND AUX.IDPROPCOBERT = PPC.IDPROPCOBERT);
-- CONTA QUANTIDADE DE REGISTRO PROCESSADOS
select count(1) into P_QTDREGS from TT_PASS_SEG;
V_INSTRUCAO:= ' SELECT '||P_QTDREGS ||' from dual ';
V_IDCONTPROC := PKG_CTRLPROC.INICIALIZA(1, V_INSTRUCAO, P_IDUSUARIO,P_QTDREGS, 'PASSE PARA SEGURADORA'); --TIPOPROC,SQLCOUNT,IDUSUARIO,QTDREGS
IF P_QTDREGS = 0 THEN
RETURN;
END IF;
-- FIM CONTA
--- CLASSIFICA OS REGISTROS
for CANC1 in (
select
c.nu_cert,
case pro.motivocanc
when '20' then 'CA'
when '3' then 'CA'
when '4' then 'CA'
when '8' then 'CI'
else 'CA'
end id2
from
tb_repasspara c
inner join propcobert ppc on c.id_propcobt = ppc.idpropcobert
inner join proposta pro on ppc.idproposta = pro.idproposta
inner join cobertura cob on ppc.idcobertura = cob.idcobertura
inner join planocomercial pla on pro.idplanocom = pla.idplanocom
where
c.nu_cert is not null
and c.flg_ativo = 1
and exists (select 1 from
movcontrib_atual mov
where
ppc.idpropcobert = mov.idpropcobert
and mov.mes_vig <= P_MES_VIT --< DESTA MANEIRA LISTA OS CANCELADOS QUE FORAM MARCADOS DEPOIS DO ENVIO DO MES ANTERIO
and mov.mes_vig >= 201805 -- Data inicio
and mov.flgsituacao in (4,5)
)
and c.tp_pla = P_TP_SEG
)
LOOP
UPDATE TT_PASS_SEG
SET ID2 = CANC1.ID2
WHERE NU_CERT = CANC1.NU_CERT;
END LOOP;
--------------------------
for CANC2 in (
select
c.nu_cert,
case pro.motivocanc
when '20' then 'CA'
when '3' then 'CA'
when '4' then 'CA'
when '8' then 'CI'
else 'CA'
end id2
from
tb_repasspara c
inner join propcobert ppc on c.id_propcobt = ppc.idpropcobert
inner join proposta pro on ppc.idproposta = pro.idproposta
where
c.nu_cert is not null
and c.flg_ativo = 1
and ppc.flgsituacao = 2
and ppc.mesexcfip <= P_MES_VIT
and P_MES_VIT > (select max (aux.mes_vig)
from movcontrib_atual aux
where aux.idpropcobert = ppc.idpropcobert)
and exists (select 1 from
movcontrib_atual mov
where
ppc.idpropcobert = mov.idpropcobert
and mov.mes_vig < P_MES_VIT --< DESTA MANEIRA LISTA OS CANCELADOS QUE FORAM MARCADOS DEPOIS DO ENVIO DO MES ANTERIO
and mov.mes_vig >= 201805 -- Data inicio
and mov.flgsituacao not in (4,5)
)
and c.tp_pla = P_TP_SEG
)
LOOP
UPDATE TT_PASS_SEG SET ID2 = CANC2.ID2 WHERE NU_CERT = CANC2.NU_CERT;
END LOOP;
-----------------------------------------------
for ALTERADOS in (
select
rep.nu_cert
from
tb_repasspara rep
inner join propcobert ppc on rep.id_propcobt = ppc.idpropcobert
inner join prodcobert pcb on ( ppc.idproduto = pcb.idproduto
and ppc.idcobertura = pcb.idcobertura)
inner join tarifa tar on ppc.idpropcobert = tar.idpropcobert
inner join movcontrib_atual mov on ppc.idpropcobert = mov.idpropcobert
inner join cobertura cob on ppc.idcobertura = cob.idcobertura
where
rep.nu_cert is not null
and rep.flg_ativo = 1
and cob.flgespecial = 1
and mov.mes_vig = P_MES_VIT -- <-----
and tar.situacao <> 5
and mov.mesref between tar.mesinitarifa and tar.mesfimtarifa
and mov.flgsituacao not in (4,5)
-- and rep.vlpremio <> tar.vlcontrib
and rep.vlpremio <> mov.vlprev
and rep.tp_pla = P_TP_SEG
)
LOOP
UPDATE TT_PASS_SEG SET ID2 = 'AL' WHERE NU_CERT = ALTERADOS.NU_CERT;
END LOOP;
-- !! Veja isso!!!
UPDATE TT_PASS_SEG
SET VLPREMIO = VLRECEBIDO
WHERE
FLGSITUACAO = 2 or
(FLGSITUACAO in( 4 , 5) and VLRECEBIDO > 0);
-- ARQUIVO DE MOVIMENTO - PARA OS PAGOS, VALOR PREVISTO DEVE SER IGUAL AO PAGO
--- FIM DA CLASSIFICAC?O DOS RESGISTROS
V_IDHST_PASS_SEG := LEULTSEQUENCIA('IDTH_PASS_SEG');
insert into TH_PASS_SEG(
IDTH_PASS_SEG, MES_VIG, DTSIMULACAO, TP_SEG, IDSEGURADORA, IDUSUARIO)
VALUES(V_IDHST_PASS_SEG, P_MES_VIT, tO_CHAR(SYSDATE,'YYYYMMDD'), P_TP_SEG, P_IDSEGURADORA, P_IDUSUARIO);
-- PROCESSAMENTO INTERNO
OPEN C_SEG(P_MES_VIT, P_IDSEGURADORA, V_grupos);
FETCH C_SEG INTO RC_SEG;
IF C_SEG%FOUND THEN
LOOP
CALCULOSDOSEGURO;
INSERETABSEGURO;
FETCH C_SEG INTO RC_SEG;
PKG_CTRLPROC.VERIFICA_STATUS(V_IDCONTPROC,C_SEG%NOTFOUND,V_FLGEXECUCAO);
EXIT WHEN V_FLGEXECUCAO > 0;
END LOOP;
COMMIT;
END IF;
CLOSE C_SEG;
PKG_CTRLPROC.FINALIZA(V_IDCONTPROC,V_FLGEXECUCAO);
V_ARQV := '01PROSEG' || P_TP_SEG;
V_SERIAL := LEULTSEQUENCIA(V_ARQV);
UPDATE TH_PASS_SEG
SET ARQUIVO = V_ARQV || '_' || DTSIMULACAO || '_' || lpad(V_SERIAL,10, '0') || '.TXT',
QTDREGPROC = P_QTDREGS
WHERE IDTH_PASS_SEG = V_IDHST_PASS_SEG;
COMMIT;
-- FIM PROCESSAMENTO
END;
-- Exclui registros de movimentac?o
procedure APAGAR_MASSA(P_IDHST_PASS in number) is
vTpSeg varchar2(2);
Begin
SELECT TP_SEG INTO vTpSeg
FROM TH_PASS_SEG
WHERE IDTH_PASS_SEG = P_IDHST_PASS;
DELETE TA_PASS_SEG WHERE IDTH_PASS_SEG = P_IDHST_PASS;
DELETE TH_PASS_SEG WHERE IDTH_PASS_SEG = P_IDHST_PASS;
UPDATE SEQUENCIAL
SET IDSEQUENCIA = IDSEQUENCIA - 1
WHERE NOME = '01PROSEG' || vTpSeg;
COMMIT;
END;
-- Exclui registros de pagamento
procedure APAGAR_MASSA_PAG(P_IDSEGPGTO in number) is
vTpSeg varchar2(2);
Begin
SELECT TP_SEG INTO vTpSeg
FROM TH_PASS_SEGPGTO
WHERE ID_SEGPGTO = P_IDSEGPGTO;
DELETE FROM TA_PASS_SEGFINAC WHERE ID_SEGPGTO = P_IDSEGPGTO;
DELETE FROM TH_PASS_SEGPGTO WHERE ID_SEGPGTO = P_IDSEGPGTO;
UPDATE SEQUENCIAL
SET IDSEQUENCIA = IDSEQUENCIA - 1
WHERE NOME = '10PROSEG' || vTpSeg;
COMMIT;
END;
procedure GERA_ADSAO_ENVIO(P_IDHST_PASS in number) is
cursor C_ADSAO_ENV is
select
pro.idproposta, pro.numproposta, ta.cpf, par.nome, par.dtnascimento, par.sexo,
ta.vlcapital, (ta.vlpremio * 100) vlpremio,
case
when (trep.nu_cert is null) then pro.dtinicobseguro
else to_number(trep.dt_env_inic || '01')
end dtinscricao,
pro.dtconfirmacao, pro.dtinicobseguro,
pro.dtsituacao, pla.codigo, pla.idplanocom, FNC_SIMPLIFICA_TEXTO(enr.endereco) endereco,
nvl(enr.numero,0) numero,
decode(enr.complemento,null,' ',FNC_SIMPLIFICA_TEXTO(enr.complemento)) complemento,
FNC_SIMPLIFICA_TEXTO(enr.bairro) bairro,
cid.descricao cidade, uf.codigo uf, enr.cep, nvl(enr.email,' ') email,
(nvl(dpg.salario,0) * 100) salario,
nvl(trep.nu_cert, 0) certf,
CASE ta.idgrupo
WHEN 547 THEN 1267942 --1267470 --1251589 547 - Planos Antigos VG
WHEN 548 THEN 1267942 --1267470 --1251589 548 - Planos Novos VG
WHEN 549 THEN 1267464 --1251717 549 - Planos 70 AP
END id28,
--De: 1267942 Para: 1267470 Email alfa 4julho2018
-- CASE ta.idgrupo
-- WHEN 547 THEN 2
-- WHEN 548 THEN 1
-- WHEN 549 THEN 1
-- END
1 id29, -- ficou como constante (Inf. e-mail Gilza 22junho)
case ta.tp_operacao
when 'IN' then
CASE ta.idgrupo
WHEN 548 THEN -- aplicado apenas para planos NOVOS
CASE
WHEN (pkg_funcgerais.calc_idade(par.dtnascimento, to_char(last_day(to_date(thp.mes_vig || '01', 'yyyymmdd')),'yyyymmdd')) < 14) THEN 1
WHEN (pkg_funcgerais.calc_idade(par.dtnascimento, to_char(last_day(to_date(thp.mes_vig || '01', 'yyyymmdd')),'yyyymmdd')) < 66) THEN 2
ELSE 3 END
ELSE 1
END
ELSE
(select a.id30 from tb_repasspara a where a.id_propcobt = ppc.idpropcobert )
end id30,
/* valido para apenas para o primeiro envio, depois, valido o bloco de cima
CASE ta.idgrupo
WHEN 547 THEN 1 -- 'VG' 547 - Planos Antigos VG
WHEN 548 THEN 2 -- 'VG' 548 - Planos Novos VG
WHEN 549 THEN 1 -- 'AP' 549 - Planos 70
END id30,
*/
CASE ta.idgrupo
WHEN 547 THEN 'VG'
WHEN 548 THEN 'VG'
WHEN 549 THEN 'AP'
END id25,
decode(tp_operacao, '__','AL',tp_operacao) op
from
ta_pass_seg ta
inner join propcobert ppc on ta.idpropcobert = ppc.idpropcobert
inner join proposta pro on ppc.idproposta = pro.idproposta
inner join participante par on ta.idparticipante = par.idparticipante
inner join planocomercial pla on pro.idplanocom = pla.idplanocom
left join enderecos enr on ta.idparticipante = enr.idparticipante and enr.flgativo = 1 and enr.flgcorresp = 1
left join municipio cid on enr.idmunicipio = cid.idmunicipio
left join tabgerais uf on cid.uf = uf.idtabela and uf.idtptabela = 1
inner join pagproposta pgp on pgp.idproposta = pro.idproposta
inner join dadospagador dpg on dpg.iddadospagador = pgp.iddadospagador
inner join th_pass_seg thp on ta.idth_pass_seg = thp.idth_pass_seg
left join tb_repasspara trep on ta.idpropcobert = trep.id_propcobt
where
ta.idth_pass_seg = P_IDHST_PASS
and ta.cod_err not in (-2, 0)
;
Content cLob;
V_Total_Regs number(8);
V_data varchar2(8);
V_ddmmaa varchar2(8);
V_ddmmaaaa varchar2(8);
V_seq number;
V_filename varchar2(15);
begin
SELECT IDSEQUENCIA , to_char(sysdate,'yyyymmdd'), to_char(sysdate,'ddmmyy'), to_char(sysdate,'ddmmyyyy')
INTO V_seq, V_data, V_ddmmaa, V_ddmmaaaa
FROM SEQUENCIAL
WHERE NOME = '01PROSEG' || (select tp_seg from th_pass_seg where idth_pass_seg = P_IDHST_PASS);
-- V_filename := '01PROSEG_' || V_data || '_' || pad(V_seq,'9',10) || '.TXT';
V_filename := 'ARQUIVO_';
-- HEADER INICO
Content :=
-- ID CAMPO TIPO TAM INI FIM
'H'|| -- 1 R IDENTIFICAC?O DO REGISTRO C 001 001 001
pad(V_seq,'9',10) || -- 2 R NUMERO SEQUENCIAL DO ARQUIVO N 010 002 011 ** criar func?o get_seq
'01' || -- 3 R IDENTIFICAC?O DO LAYOUT N 002 012 013
V_ddmmaaaa || -- 4 R DATA GERAC?O DO ARQUIVO D 008 014 021
PAD(777,'9',10) || -- 5 R CODIGO DO PARCEIRO DEFN PELA SEGURAD N 010 022 031 N? CONTRATO **
pad('PROSEG','X',30) || -- 6 R NOME DO PARCEIRO (ESTIPULANTE) C 030 032 061 ******
pad(' ','X',1439); -- 7 O FILLER C 1439 062 1500
ESCREVE_ARQUIVO(Content || chr(10), V_filename, 'w');
V_Total_Regs := 0;
for R in C_ADSAO_ENV loop
Content :=
'D' || -- 1 R D C 1 1 1
r.op || -- 2 R Tipo operac?o C 2 2 3 ******** QRY
'F' || -- 3 R Tipo de cliente C 1 4 4
pad(r.cpf,'9', 15) || -- 4 R CPF N 15 5 19
pad(r.nome,'X', 40) || -- 5 R NOME CLIENTE C 40 20 59
pad(' ', 'X', 10) || -- 6 O FILLER C 10 60 69 *********
dt_formt(r.dtnascimento)|| -- 7 R DT NASCIMENTO D 8 70 77
r.sexo || -- 8 R SEXO C 1 78 78
pad(r.endereco,'X',40) || -- 9 O ENDERECO C 40 79 118 Rpad(*e.endereco,40, ' ')
pad(' ','X',10) || -- 10 O FILLER C 10 119 128 *********
pad(r.numero,'9', 5) || -- 11 O NUMERO N 5 129 133 ** Lpad(e.numero,5, '0')
pad(r.complemento,'X',30)||-- 12 O COMPLEMENTO C 30 134 163 **** e.complemento
pad(r.bairro,'X',20) || -- 13 O BAIRRO C 20 164 183 -- e.bairro
pad(' ','X',20) || -- 14 O FILLER C 20 184 203 *********
pad(r.cidade,'X',40) || -- 15 O CIDADE C 40 204 243 mu.descricao
pad(r.uf,'X', 2) || -- 16 O UF C 2 244 245 tu.codigo
pad(r.cep,'9', 8) || -- 17 O CEP N 8 246 253 e.cep
pad(0,'9', 2) || -- 18 O DDD CELULAR N 2 254 255 - GET 2 FIRST REPLACE(e.telefone,' ', '') ||
pad(0,'9', 8) || -- 19 O CELULAR N 8 256 263 - REPLACE(e.telefone,' ', '') ||
pad(0,'9', 2) || -- 20 O DDD TEL RESID N 2 264 265 - GET 2 FIRST REPLACE(e.telefone,' ', '') ||
pad(0,'9', 8) || -- 21 O TEL RESID N 8 266 273 - REPLACE(e.telefone,' ', '') ||
pad(0,'9', 2) || -- 22 O DDD TEL COMERC N 2 274 275 - GET 2 FIRST REPLACE(e.telefone,' ', '') ||
pad(0,'9', 8) || -- 23 O TEL COMERC N 8 276 283 - REPLACE(e.telefone,' ', '') ||
pad(' ','X',50) || -- 24 O EMAL C 50 284 333 - e.email
r.id25 || -- 25 R CATEGORIA SEG C 2 334 335 - *** REALIZAR A QUERY!!!!
pad(' ','X',5) || -- 26 R UTILIZAR D-PARA C 5 336 340 - DEIXAR EM BRANCO ? sim
pad(r.vlcapital * 100,'9', 12)|| -- 27 -->
-- 27 R FAIXA DO SEGURO N 12 341 352 - ok!!!
pad(r.id28,'9', 20) || -- 28 R N.CONTRATO N 20 353 372 - ALFA X ESTIPULANTE **** ONDE PEGO ISSO???
pad(r.id29,'9', 20) || -- 29 R N.Sub CONTRATO N 20 373 392 - *** ONDE PEGO ISSO???
pad(r.id30,'9', 20)|| -- 30 R N. DO PLANO N 20 393 412 - ***DEFINIDO PLELA SEGURADORA/ COD DE REF UTILIZADO PELA SEGURADORA COMO REF PARA ESTE SEGURO
pad(r.vlpremio,'9', 12) || -- 31 R VALOR DO PREMIO N 12 413 424
dt_formt(r.dtinscricao) || -- 32 R DATA ADSAO SEG D 8 425 432 --
-- '01052018' || -- 32 R DATA ADSAO SEG D 8 425 432 --
'M' || -- 33 R Peridiocidade C 1 433 433 - M mensal / B Bimestral / T Trimestral / S semanal / A anual
pad(' ','X',559) || -- 34 O FILLER C 559 434 992 *********
pad(r.certf ,'9', 10) || -- 35 R CODIG CERTIF N 10 993 1002 - *** IDENTIFICADOR UNICO DA SEGURADORA - PEDIR PARA ALFA
pad(' ','X',30) || -- 36 O COD DE SORTEIO C 30 1003 1032 - *** COD PRODUTO DE SORTEIO
pad('0','9', 10) || -- 37 O N SERIE LOTERIA N 10 1033 1042 - * NUMERO DE SERIE DA LOTERIA N/A
pad('0','9', 10) || -- 38 O NUMERO SORTEIO N 10 1043 1052 - * NUMERO DE SERIE DA LOTERIA N/A
-- pad(r.numproposta,'9', 10)||-- 39 R ID UNIC PARC N 10 1053 1062 - * Informado pela Critiane em 17/05/2018 / numcContrato informado pelo Zodra em 29/05/2018
--remoc?o do Filler e aumento do tamanho no n?proposta - Solicitac?o Zodra via Mail em 6NOV2018
pad(r.numproposta,'9', 25)||-- 39 R ID UNIC PARC N 10 1053 1062 - * Informado pela Critiane em 17/05/2018 / numcContrato informado pelo Zodra em 29/05/2018
-- pad(' ','X',15) || -- 40 R FILLER C 15 1063 1077 *****
dt_formt(r.dtinscricao) || -- 41 R DATA VENDA D 8 1078 1085 -- dt venda
V_ddmmaaaa || -- 42 R DATA OPERC?O D 8 1086 1093 -- dt DA OPRAC?O, DATA QUE HOUVE INTERAC?O DESTE REGISTRO ???????
'N' || -- 43 O CONSUGUE S/N C 1 1094 1094
pad(' ','X',40) || -- 44 O NOME DO CONJUGUE C 40 1095 1134
pad(' ','X',15) || -- 45 O CPF DO CONJUGUE N 15 1135 1149
'01011901' || -- 46 O DT NASC DO CONJU N 8 1150 1157
pad(' ','X',82) || -- 47 O FILLER C 82 1158 1239
pad(' ','X',15) || -- 48 O CPF VENDEDOR C 15 1240 1254
pad(' ','X',50) || -- 49 O NOME VENDEDOR C 50 1255 1304
pad(' ','X',70) || -- 50 O FILLER C 70 1305 1374
'01052018' || -- 51 R DT INIC VIG SEG D 8 1375 1382
'30042019'|| -- 52 R DT FINAL VIG SEG D 8 1383 1390
pad(' ','X',10) || -- 53 O NUM PASSAPORTE C 10 1391 1400
'000' || -- 54 O PESO DO TITULAR N 3 1401 1403 --2 CASAS SEM VIRGULA
'000' || -- 55 O PESO DO CONJUGUE N 3 1404 1406
'000' || -- 56 O ALTURA DO TITULAR N 3 1407 1409 -- EM CM SEM VIRGULA
'000' || -- 57 O ALTURA DO CONJUGU N 3 1410 1412 -- EM CM SEM VIRGULA
'N' || -- 58 O DSP - S/N C 1 1413 1413 -- ***??
pad(' ','X', 12) || -- 59 O COD CBO PROFISS N 12 1414 1425 *****
pad(r.salario, '9', 10) || -- 60 O RENDA TITULAR N 10 1426 1435 *****
'N' || -- 61 O ERROR PREENCHIMEN C 1 1436 1436 --- S/ N
'01011901' || -- 62 R DATA INIC VIG FAT D 8 1437 1444 *************????
'01011901' || -- 63 O DATA FIM VIG FATU D 8 1445 1452 *************????
pad('0','9', 15) || -- 64 O NOSSO NUMERO FATU N 15 1453 1467
pad(' ','X',33) || -- 65 O FILLER C 33 1468 1500
chr(10); -- carriage return line feed
ESCREVE_ARQUIVO(Content, V_filename, 'a');
V_Total_Regs := V_Total_Regs + 1;
end loop;
-- TRAILLER
Content := 'T'|| Lpad(V_Total_Regs, 8, '0') || Rpad(' ',1491,' ');
ESCREVE_ARQUIVO(Content, V_filename, 'a');
END;
-- Cria massa de pagamentos
Procedure Cria_Massa_financ(P_IDHST_PASS in number, P_IDUSUARIO IN NUMBER) is -- procedure interna
v_sequenc number;
v_TpSeg VARCHAR2(2);
v_MESVIG NUMBER;
v_DT number;
v_total number;
V_ARQV VARCHAR2(35);
V_SERIAL NUMBER;
Begin
-- Pega os dados da tabela de movimentac?o
SELECT T.MES_VIG, T.TP_SEG
INTO v_MESVIG, v_TpSeg
FROM TH_PASS_SEG T
WHERE T.IDTH_PASS_SEG = P_IDHST_PASS;
v_DT := to_number(to_char(sysdate, 'YYYYMMDD'));
v_sequenc := LEULTSEQUENCIA('ID_SEGPGTO');
INSERT INTO TH_PASS_SEGPGTO (ID_SEGPGTO, MES_VIG, TP_SEG, DTGERACAO, IDUSUARIO, PASSO)
VALUES (v_sequenc, v_MESVIG, v_TpSeg, v_DT, P_IDUSUARIO, 0);
-- Inclus?o dos pagamentos
INSERT INTO TA_PASS_SEGFINAC
(id_segpgto, NU_CERT, NUMPROPOSTA,VLRECEBIDO,
VLCAPITAL, DTPGTO, TP_OPERACAO, n_parc)
Select
v_sequenc
,r.nu_cert
,pro.numproposta
,mov.vlpgto as vlrecebido
,r.vlcapital
,mov.dtpgto
,3 as tp_operacao -- Repasse
,c.nu_parc_coseg as n_parc
from
tb_repasspara r
inner join movcontrib_atual mov on r.id_propcobt = mov.idpropcobert
inner join tb_movi_coseg c on mov.idmovcontrib = c.id_movicntb
inner join proposta pro on mov.idproposta = pro.idproposta
where ( (r.flg_ativo = 1)
-- Propostas canceladas na Alfa, mas que o pagamento precisa ser informado por conta do
-- pagamento de beneficio.
-- Sempre que houver a mesma necessidade, basta descomentar o codigo abaixo e informar as propostas.
/*
OR ( r.flg_ativo = 0
and pro.numproposta in (296157,254557,162394))
*/
)
and r.nu_cert is not null
and mov.mes_vig <= v_MESVIG
and nvl(mov.dtpgto,0) > 0 -- E para considerar a data de pagamento e n?o a de classificac?o
and mov.flgsituacao in (2, 3)
and c.flg_env_pgto = 0
and r.tp_pla = v_TpSeg;
-- Inclus?o dos estornos
/* INSERT INTO TA_PASS_SEGFINAC
(id_segpgto, NU_CERT, NUMPROPOSTA,VLRECEBIDO,
VLCAPITAL, DTPGTO, TP_OPERACAO, n_parc)
Select
v_sequenc
,r.nu_cert
,pro.numproposta
,mov.vlpgto as vlrecebido
,r.vlcapital
,mov.dtpgto
,2 as tp_operacao -- Estorno
,c.nu_parc_coseg as n_parc
from
tb_repasspara r
inner join movcontrib_atual mov on (r.id_propcobt = mov.idpropcobert)
inner join tb_movi_coseg c on (mov.idmovcontrib = c.id_movicntb)
inner join proposta pro on (mov.idproposta = pro.idproposta)
where r.flg_ativo = 0
and r.nu_cert is not null
and r.tp_pla = v_TpSeg
and c.flg_env_pgto = 1
and pro.motivocanc = '4' -- Situac?o de obito
and mov.mes_vig > pro.mescancelamento
and mov.mes_vig <= v_MESVIG
and nvl(mov.dtpgto,0) > 0 -- E para considerar a data de pagamento e n?o a de classificac?o
and mov.flgsituacao in (4,5);
*/
SELECT COUNT(1) INTO V_TOTAL
FROM TA_PASS_SEGFINAC
WHERE ID_SEGPGTO = V_SEQUENC;
V_ARQV := '10PROSEG' || v_TpSeg;
V_SERIAL := LEULTSEQUENCIA(V_ARQV);
UPDATE TH_PASS_SEGPGTO
SET QTDREG = V_TOTAL,
ARQUIVO = V_ARQV || '_' || DTGERACAO || '_' || LPAD(V_SERIAL, 10, '0') || '.TXT'
WHERE ID_SEGPGTO = V_SEQUENC;
COMMIT;
end;
Procedure LER_ARQUIVO_11(P_IDHST_PASS in number, l_filename in varchar2, P_TeveErro out number) is
V_ARQUIVO UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'ARQUIVOS';
V_LINHA VARCHAR2(32767) := '';
V_tp_lay char(2);
V_1CARAC CHAR;
v_COD_SEG varchar2(15);
v_NU_CERT varchar2(15);
v_COD_RET varchar2(3);
v_MSG_IMP varchar2(50);
v_DT_IMPO varchar2(8);
v_NUM_PARC NUMBER;
v_NU_LOTE NUMBER;
v_QntReg number;
v_QNT_INF number;
v_TP_TRAN VARCHAR(1);
-- v_RETORN_ACEIT VARCHAR(1);
BEGIN
V_ARQUIVO := UTL_FILE.fopen(l_location, l_filename, 'R', 32767);
UTL_FILE.get_line(V_ARQUIVO, V_LINHA, 32767); -- LER 1? LINHA
V_tp_lay := SUBSTR(V_LINHA,12,2); -- VERIFICA O TIPO DE LAYOUT 3 MOVIMENTAC?O / 11 PAGAMENTOS
v_QntReg := 0;
IF V_tp_lay = '11' THEN
PRC_TRUNCATE('TA_ARQ_RECB_LAYT_PGTO');
LOOP
UTL_FILE.get_line(V_ARQUIVO, V_LINHA, 32767);
V_1CARAC := SUBSTR(V_LINHA,1,1);
IF V_1CARAC = 'D' THEN -- VERIFICA SE E A LINHA DE TRAILLER, SE N?O FOR...
-- alterar para a tabela TA_ARQ_RECB_LAYT_PGTO
v_COD_SEG := SUBSTR(V_LINHA, 2, 15); -- COD DE SEGURO - PARCEIRO
v_NU_CERT := SUBSTR(V_LINHA, 17, 15); -- NUM CERTIFICADO
v_NUM_PARC := SUBSTR(V_LINHA, 32, 5); -- NUM PARCELA
v_COD_RET := SUBSTR(V_LINHA, 37, 3); -- COD RETORNO IMPORTACAO
v_MSG_IMP := SUBSTR(V_LINHA, 40, 50); -- MSG DE RETORNO
v_DT_IMPO := SUBSTR(V_LINHA, 90, 8); -- DT IMPORT
v_NU_LOTE := SUBSTR(V_LINHA, 136, 10); -- NUM LOTE PARCEIRO
v_TP_TRAN := SUBSTR(V_LINHA, 146, 1); -- TIPO DE TRANSAC?O
-- v_RETORN_ACEIT := SUBSTR(V_LINHA, 162, 3); -- TIP RETORNO AUTOMATICO - SOMENTOA PARA AUTOMATICO
INSERT into
TA_ARQ_RECB_LAYT_PGTO
(COD_SEG, NU_CERT, NUM_PARC, COD_RET, MSG_IMP, DT_IMPO, NU_LOTE, TP_TRAN, RETORN_ACEIT)
values (v_COD_SEG, v_NU_CERT, v_NUM_PARC, v_COD_RET, v_MSG_IMP, v_DT_IMPO, v_NU_LOTE, v_TP_TRAN, 0);--v_RETORN_ACEIT);
v_QntReg := v_QntReg + 1;
ELSIF V_1CARAC = 'T' THEN
v_QNT_INF := SUBSTR(V_LINHA, 2, 8); -- validac?o reforcada,
IF v_QNT_INF = v_QntReg THEN
COMMIT;
EXIT;
ELSE
ROLLBACK;
EXIT;
END IF;
END IF;
END LOOP;
For y in (
Select
ta.nu_cert, ta.num_parc, ta.cod_ret, ta.msg_imp
from
TA_ARQ_RECB_LAYT_PGTO ta
) Loop
update
ta_pass_segfinac z
set
z.cod_ret = y.cod_ret,
z.msg_imp = y.msg_imp
where
z.nu_cert = y.nu_cert and
z.n_parc = y.num_parc and
z.id_segpgto = P_IDHST_PASS;
End Loop;
for R in (
select mov.id_propcobt
,pg.num_parc
,mov.idmovicoseg
,tfn.tp_operacao
from ta_arq_recb_layt_pgto pg
inner join ta_pass_segfinac tfn on ( pg.nu_cert = tfn.nu_cert
and pg.num_parc = tfn.n_parc)
inner join TB_REPASSPARA rp on (tfn.nu_cert = rp.nu_cert)
inner join tb_movi_coseg mov on ( rp.id_propcobt = mov.id_propcobt
and pg.num_parc = mov.nu_parc_coseg)
where tfn.id_segpgto = P_IDHST_PASS
and tfn.tp_operacao in (2,3) -- Estorno ou Repasse do pagamento
and tfn.cod_ret = 0
)
loop
UPDATE TB_MOVI_COSEG
SET FLG_ENV_PGTO = CASE R.TP_OPERACAO
WHEN 2 THEN 2 -- Pagamento estornado
WHEN 3 THEN 1 -- Pagamento enviado
END
WHERE IDMOVICOSEG = R.IDMOVICOSEG;
end loop;
update th_pass_segpgto th
set th.processado = 1,
th.qtdregok = (select count(1)
from ta_pass_segfinac ta
where ta.id_segpgto = P_IDHST_PASS
and ta.id_segpgto = th.id_segpgto
and ta.cod_ret = 0)
where th.id_segpgto = P_IDHST_PASS;
select sum(p.cod_ret) into P_TeveErro from ta_pass_segfinac p where p.id_segpgto = P_IDHST_PASS;
IF P_TeveErro > 0 THEN
P_TeveErro := 1;
update th_pass_segpgto set passo = 1 where id_segpgto = P_IDHST_PASS;
ELSE
update th_pass_segpgto set passo = 2 where id_segpgto = P_IDHST_PASS;
END IF;
COMMIT;
ELSE
DBMS_OUTPUT.put_line('LAYOUT NAO REGISTRADO');
END IF;
UTL_FILE.fclose(V_ARQUIVO);
exception
when Utl_File.Invalid_Operation THEN
Vs_MensagErros := 'Operac?o invalida no arquivo';
Utl_File.Fclose(V_ARQUIVO);
rollback;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Write_Error THEN
Vs_MensagErros := 'Erro de gravac?o no arquivo';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Invalid_Path THEN
Vs_MensagErros := 'Diretorio invalido';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Invalid_Mode THEN
Vs_MensagErros := 'Modo de acesso invalido';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Others then
Vs_MensagErros := 'Problemas na gerac?o do arquivo';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
END;
Procedure GERA_PAG_ENVIO(P_IDHST_PASS in number) is
cursor C_PAG_ENV is
select *
from ta_pass_segfinac
where id_segpgto = P_IDHST_PASS;
Content cLob;
V_Total_Regs number(8);
V_data varchar2(8);
V_ddmmyyyy varchar2(8);
V_seq number;
V_filename varchar2(15);
begin
SELECT IDSEQUENCIA , to_char(sysdate,'yyyymmdd'), to_char(sysdate,'ddmmyyyy')
INTO V_seq, V_data, V_ddmmyyyy
FROM SEQUENCIAL
WHERE NOME = (Select '10PROSEG' || t.tp_seg from th_pass_segpgto t where t.id_segpgto = P_IDHST_PASS);
V_filename := 'ARQ_pgto';
-- HEADER INICO
Content :=
-- ID CAMPO TIPO TAM INI FIM
'H'|| -- 1 R IDENTIFICAC?O DO REGISTRO C 01 001 001
'0000000777' || -- 2 R ID EMPRESA - ID CONTRATO C 10 002 011 ???
'0000000777' || -- 3 R ID SISTEMA - ID CONTRATO C 10 012 021 ???
V_ddmmyyyy || -- 4 R DT DO LOTE/MOVIMENTO D 08 022 029
'1' || -- 5 R OPERAC?O - 1 RETORNO DE OPERACOES C 01 030 030
PAD(V_seq,'9',8) || -- 6 R N LOTE - UNICO SEQUENCIAL N 08 031 038
'10' || -- 7 R ID LAYOUT = 10 - CONSTANT N 02 039 040
PAD(1,'9',10) || -- 8 R CODIGO DE ORIGEM N 10 041 050 ?????
pad('PROSEG','X',30)|| -- 9 R NOME DA ORIGEM - CONST 'PREVIMIL' C 30 051 080
pad(' ','X',920); -- 10 O FILLER C 920 081 1000
ESCREVE_ARQUIVO(Content || chr(10), V_filename, 'w');
V_Total_Regs := 0;
for R in C_PAG_ENV loop
Content :=
-- id desc tp L ini fim
'D'|| -- 1 R IDENT DO REG C 1 1 1
r.tp_operacao || -- 2 R TIPO DE OPERAC?O C 1 2 2 - Opc?es 1 Cobranca \2 Estorno \ 3 Repasse \ 4 Credito \ 5 Fatura
'1' || -- 3 R TIPO COBRANCA C 1 3 3 - Opc?es 1 Debito em conta / 2 Cart?o de Credito *** N?O E NECESSARIO PREENCHMIENTO
PAD(r.nu_cert,'9',30)|| -- 4 R ID ALFASEG (NUM CERTIFICADO) N 30 4 33 ??????
PAD(r.numproposta,'9',30)||-- 5 R ID ALFASEG (NUM CONTRATO) N 30 34 063 ??????
pad(r.n_parc ,'9',5) || -- 6 R PARCELA EXECUTADa 1 5 64 68 - p/ tp seg VG numro da fatura. p/ demais, n parcela
-- dt_formt(r.dtinscricao)||-- 7 O DT PAGAMENTO (SE HOUVE) D 8 69 76 *********
dt_formt(r.dtpgto) || -- 7 O DT PAGAMENTO (SE HOUVE) D 8 69 76 *********
pad(' ','X',10) || -- 8 R COD RETORNO -TB ERRO 1 C 10 77 86
pad(' ','X',50) || -- 9 R MENSAGEM RETORNO - TB ERRO 1 C 50 87 136
pad(' ','X',30) || -- 10 O COD DO PRODUTO- NUM REF C 30 137 166
pad(' ','X',30) || -- 11 O NOME PRODUTO - NOM ABREV C 30 167 196
pad(' ','X',30) || -- 12 O COD EMPRESA DO PRODUTO /N?R# C 30 197 226
pad('PROSEG','X',30) || -- 13 O NOME DA EMPRESA / ABREVIA C 30 227 256
dt_formt(r.dtpgto) || -- 14 O DT DE REPASSE C 8 257 264
pad(r.vlcapital * 100,'9', 15) || --15
-- 15 O VALOR DA OPERAC?O N 15 256 279
pad(r.vlrecebido * 100,'9', 15) || --16
-- 16 O VALOR DO REPASSE N 15 280 294
dt_formt(r.dtpgto) || -- 17 O DT DE VENCIMENTO D 8 295 302
dt_formt(r.dtpgto) || -- 18 O DT DE CREDITO D 8 303 310
pad(1,'9', 20) || -- 19 O P/ VG COD DO SUB EST. ** N 20 311 330 PARA OS DEMAIS - DO ESTIPULANTE
'01052018' || -- 20 O TP SEG VG = DT INICIO D 8 331 338 DE VIGENCIA DA FATURA. P/ OS DEMAIS, NULO
'30042019' || -- 21 O TP SEG VG = DT FIM D 8 339 346 DE VIGENCIA DA FATURA. P/ OS DEMAIS, NULO
PAD(0,'9',15) || -- 22 O NOSSO NUMERO N 15 347 361 ****
PAD(' ','X',20) || -- 23 O CODIGO CONTRATO N 20 362 381
pad(' ','X',619) || -- 24 O FILLER C 619 382 1000
chr(10); -- carriage return line feed
ESCREVE_ARQUIVO(Content, V_filename, 'a');
V_Total_Regs := V_Total_Regs + 1;
end loop;
-- TRAILLER
Content := 'T'|| Lpad(V_Total_Regs, 8, '0') || Rpad(' ',991,' ');
ESCREVE_ARQUIVO(Content, V_filename, 'a');
END;
Procedure LER_ARQUIVO_03(P_IDHST_PASS in number, l_filename in varchar2, P_TeveErro out number) is
V_ARQUIVO UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'ARQUIVOS';
V_LINHA VARCHAR2(32767) := '';
V_tp_lay char(2);
V_1CARAC CHAR;
v_COD_SEG varchar2(15);
v_NU_CERT varchar2(15);
v_COD_RET varchar2(3);
v_MSG_IMP varchar2(50);
v_DT_IMPO varchar2(8);
v_ID30 NUMBER;
V_DTNASC NUMBER;
v_QntReg number;
v_QNT_INF number;
V_TP_PLA VARCHAR2(2); -- AP ou VG
BEGIN
V_ARQUIVO := UTL_FILE.fopen(l_location, l_filename, 'R', 32767);
UTL_FILE.get_line(V_ARQUIVO, V_LINHA, 32767); -- LER 1? LINHA
V_tp_lay := SUBSTR(V_LINHA,12,2); -- VERIFICA O TIPO DE LAYOUT 3 MOVIMENTAC?O / 11 PAGAMENTOS
v_QntReg := 0;
IF V_tp_lay = '03' THEN
PRC_TRUNCATE('TA_ARQ_RECB_LAYT_MOV');
LOOP
UTL_FILE.get_line(V_ARQUIVO, V_LINHA, 32767);
V_1CARAC := SUBSTR(V_LINHA,1,1);
IF V_1CARAC = 'D' THEN -- VERIFICA SE E A LINHA DE TRAILLER, SE N?O FOR...
v_COD_SEG := SUBSTR(V_LINHA, 2, 15); -- COD DE SEGURO - PARCEIRO
v_NU_CERT := SUBSTR(V_LINHA,17, 15); -- NUM CERTIFICADO
v_COD_RET := SUBSTR(V_LINHA,37, 3); -- COD RETORNO IMPORTACAO
v_MSG_IMP := SUBSTR(V_LINHA,40, 50); -- MSG DE RETORNO
v_DT_IMPO := SUBSTR(V_LINHA,90, 8); -- DT IMPORT
IF (trim(v_MSG_IMP) = 'OK') THEN
v_COD_RET := 0;
END IF;
INSERT into
TA_ARQ_RECB_LAYT_MOV
(COD_SEG, NU_CERT, COD_RET, MSG_IMP, DT_IMPO)
values (v_COD_SEG, v_NU_CERT, v_COD_RET, v_MSG_IMP, v_DT_IMPO);
v_QntReg := v_QntReg + 1;
ELSIF V_1CARAC = 'T' THEN
v_QNT_INF := SUBSTR(V_LINHA, 2, 8);
IF v_QNT_INF = v_QntReg THEN
COMMIT;
EXIT;
ELSE
ROLLBACK;
EXIT;
END IF;
END IF;
END LOOP;
for R in (SELECT T.*, P.IDPROPOSTA, C.IDPROPCOBERT, TA.TP_OPERACAO, TH.MES_VIG,
TA.VLRECEBIDO, TA.VLPREMIO, TA.VLCAPITAL,
TH.IDSEGURADORA, TA.IDGRUPO, TA.IDPARTICIPANTE
FROM TA_ARQ_RECB_LAYT_MOV T
INNER JOIN PROPOSTA P ON (P.NUMPROPOSTA = T.COD_SEG)
INNER JOIN PROPCOBERT C ON (C.IDPROPOSTA = P.IDPROPOSTA)
INNER JOIN TA_PASS_SEG TA ON ( TA.NUMPROPOSTA = P.NUMPROPOSTA
AND TA.IDPROPCOBERT = C.IDPROPCOBERT
AND TA.IDTH_PASS_SEG = P_IDHST_PASS)
INNER JOIN TH_PASS_SEG TH ON (TA.IDTH_PASS_SEG = TH.IDTH_PASS_SEG))
LOOP
UPDATE TA_PASS_SEG S
SET S.COD_ERR = R.COD_RET,
S.MSG_IMP = R.MSG_IMP
WHERE S.NUMPROPOSTA = R.COD_SEG
AND S.IDTH_PASS_SEG = P_IDHST_PASS;
UPDATE TH_COBT_COSEG C
SET C.NU_CERT = R.NU_CERT
WHERE C.ID_PROPCOBT = R.IDPROPCOBERT;
IF R.TP_OPERACAO = 'IN' THEN -- INCLUS?O
SELECT P.DTNASCIMENTO INTO V_DTNASC FROM PARTICIPANTE P WHERE P.IDPARTICIPANTE = R.IDPARTICIPANTE;
IF (R.IDGRUPO = 548) THEN -- aplicado apenas para planos NOVOS
IF (pkg_funcgerais.calc_idade(V_DTNASC, to_char(last_day(to_date(R.MES_VIG || '01', 'yyyymmdd')),'yyyymmdd')) < 14) THEN
v_ID30 := 1;
ELSIF (pkg_funcgerais.calc_idade(V_DTNASC, to_char(last_day(to_date(R.MES_VIG || '01', 'yyyymmdd')),'yyyymmdd')) < 66) THEN
v_ID30 := 2;
END IF;
ELSE
v_ID30 := 1;
END IF;
SELECT TP_SEG INTO V_TP_PLA FROM TH_PASS_SEG WHERE IDTH_PASS_SEG = P_IDHST_PASS;
INSERT INTO TB_REPASSPARA
(ID_PROPCOBT, ID_SEG, NU_CERT, VLRECEBIDO, VLPREMIO, VLCAPITAL,
DT_ENV, FLG_ATIVO, ID30, IDGRUPO, DT_ENV_INIC, TP_PLA)
VALUES
(R.IDPROPCOBERT, R.IDSEGURADORA, R.NU_CERT, R.VLRECEBIDO, R.VLPREMIO, R.VLCAPITAL,
R.MES_VIG, 1, v_ID30, R.IDGRUPO, R.MES_VIG, V_TP_PLA);
ELSIF R.TP_OPERACAO IN ('AL', '__') THEN -- ALTERAC?O
UPDATE TB_REPASSPARA T
SET
T.VLRECEBIDO = R.VLRECEBIDO,
T.VLPREMIO = R.VLPREMIO,
T.VLCAPITAL = R.VLCAPITAL,
T.DT_ENV = R.MES_VIG
WHERE
T.NU_CERT = R.NU_CERT;
ELSIF R.TP_OPERACAO IN ('CA', 'CI') THEN -- CANCELAMENTO
UPDATE TB_REPASSPARA T
SET
T.DT_ENV = R.MES_VIG,
T.FLG_ATIVO = 0
WHERE
T.NU_CERT = R.NU_CERT;
END IF;
END LOOP;
update th_pass_seg th
set th.processado = 1,
th.qtdregok = (select count(1)
from ta_pass_seg ta
where ta.idth_pass_seg = P_IDHST_PASS
and ta.idth_pass_seg = th.idth_pass_seg
and ta.cod_err = 0)
where th.idth_pass_seg = P_IDHST_PASS;
Select sum(cod_err) into P_TeveErro from ta_pass_seg p where p.idth_pass_seg = P_IDHST_PASS;
IF P_TeveErro > 0 THEN
P_TeveErro := 1;
update th_pass_seg set passo = 1 where idth_pass_seg = P_IDHST_PASS;
commit;
ELSE
update th_pass_seg set passo = 2 where idth_pass_seg = P_IDHST_PASS;
END IF;
COMMIT;
ELSE
DBMS_OUTPUT.put_line('Arquivo errado');
END IF;
UTL_FILE.fclose(V_ARQUIVO);
exception
when Utl_File.Invalid_Operation THEN
Vs_MensagErros := 'Operac?o invalida no arquivo';
Utl_File.Fclose(V_ARQUIVO);
rollback;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Write_Error THEN
Vs_MensagErros := 'Erro de gravac?o no arquivo';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Invalid_Path THEN
Vs_MensagErros := 'Diretorio invalido';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Utl_File.Invalid_Mode THEN
Vs_MensagErros := 'Modo de acesso invalido';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
when Others then
Vs_MensagErros := 'Problemas na gerac?o do arquivo';
Utl_File.Fclose(V_ARQUIVO);
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, Vs_MensagErros || ' : ' || SQLERRM);
END;
Procedure Cria_Massa_financ2(P_IDHST_PASS in number, P_IDUSUARIO IN NUMBER) is -- procedure interna
v_sequenc number;
V_TPplan VARCHAR2(2);
v_MESVIG NUMBER;
v_DT number;
v_total number;
Begin
PRC_TRUNCATE('TA_PASS_SEGF_ERR');
insert into TA_PASS_SEGF_ERR(nu_cert)
select nu_cert from ta_pass_segfinac t where t.id_segpgto = P_IDHST_PASS;
commit;
select
max (p.id_segpgto) + 1,
to_number(to_char(sysdate, 'YYYYMMDD'))
into
v_sequenc, v_DT
from th_pass_segpgto p;
SELECT T.MES_VIG, T.TP_SEG
INTO v_MESVIG, V_TPplan
FROM TH_PASS_SEG T
WHERE T.IDTH_PASS_SEG = P_IDHST_PASS;
insert into th_pass_segpgto (id_segpgto, mes_vig, tp_seg, dtgeracao, idusuario, passo) values
(v_sequenc, v_MESVIG, V_TPplan, v_DT, P_IDUSUARIO, 0);
INSERT INTO -- insert de pagamentos
ta_pass_segfinac
(id_segpgto, NU_CERT, NUMPROPOSTA,VLRECEBIDO,
VLCAPITAL, DTPGTO, TP_OPERACAO, n_parc)
Select
v_sequenc,
r.nu_cert,
pro.numproposta,
mov.vlpgto vlrecebido,
r.vlcapital,
mov.dtpgto, 3 tp_cobranca, -- 3 = repasse
c.nu_parc_coseg n_parc
from
tb_repasspara r
inner join movcontrib_atual mov on r.id_propcobt = mov.idpropcobert
inner join tb_movi_coseg c on mov.idmovcontrib = c.id_movicntb
inner join proposta pro on mov.idproposta = pro.idproposta
-- APENA OS INCONSISTENTES
INNER JOIN TA_PASS_SEGF_ERR ERR ON r.nu_cert = ERR.Nu_Cert
where
r.flg_ativo = 1
and r.nu_cert is not null
and mov.mes_vig <= v_MESVIG
and nvl(mov.dtpgto,0) > 0 -- E para considerar a data de pagamento e n?o a de classificac?o
and mov.flgsituacao in (2, 3)
and c.flg_env_pgto = 0
and r.tp_pla = V_TPplan;
select count(1) into v_total from ta_pass_segfinac where id_segpgto = v_sequenc;
update th_pass_segpgto
set qtdreg = v_total,
arquivo = '10PROSEG' || V_TPplan || '_' || v_DT || '_' || lpad(v_sequenc, 10, '0') || '.TXT'
where id_segpgto = v_sequenc;
commit;
end;
END PKG_PASS_SEG;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment