- create or replace procedure sys_cron.JOB_SAU_REDE_CREDENCIADA is
- CURSOR CurPrinc IS
- /*************************************************************************************************************************/
- /* Criado em 06/07/2016 por iDA */
- /* A finalidade deste processo e enviar arquivo CVS da REDE CREDENCIADA */
- /* */
- /* Este processo devera ser executado todo dia */
- /*************************************************************************************************************************/
- --Corrigido 01/07/2019 - SEN47400PFC
- --Corrigido 30/04/2020 - E40106
- select
- '"' || trim(cesp.cod_especialid) || '"' codigo_cbo,
- '"' || trim(pl.cod_plano) || '"' codigo_plano,
- CASE
- WHEN ccl.COD_CLASSE in (20) THEN '"'||'4'||'"'
- WHEN ccl.COD_CLASSE in (31) THEN '"'||'15'||'"'
- WHEN ccl.COD_CLASSE in (25) THEN '"'||'26'||'"'
- WHEN ccl.COD_CLASSE in (25) THEN '"'||'26'||'"'
- WHEN ccl.COD_CLASSE in (27) THEN '"'||'28'||'"'
- WHEN ccl.COD_CLASSE in (24) THEN '"'||'13'||'"'
- WHEN ccl.COD_CLASSE in (23) THEN '"'||'12'||'"'
- WHEN ccl.COD_CLASSE in (41) THEN '"'||'40'||'"'
- ELSE '"'||trim(ccl.cod_classe) ||'"'
- END AS classe_prestador,
- '"' || trim(cv.cod_convenente) || '"' codigo_legado,
- '"' || trim(cv.nom_convenente) || '"' nome_prestador,
- '"' || trim(cv.cod_sexo) || '"' sexo,
- '"' || trim(cend.nom_logradouro) || '"' endereco,
- '"' || trim(cend.num_logradouro) || '"' numero,
- '"' || trim(REPLACE(cend.des_complemento,'"','')) || '"' complemento,
- '"' || trim(cend.nom_bairro) || '"' bairro,
- '"' || trim(cend.cod_munici) || '"' codigo_municipio,
- '"' || trim(cend.cod_estado) || '"' codigo_UF,
- '"' || trim(cend.num_cep) || '"' cep,
- '"' || trim(eec.cod_especialid) || '"' Especialidade,
- CASE
- WHEN cend.NUM_DDD is null or cend.num_telefone is null THEN '"' || '' || '"'
- else '"' || trim(cend.NUM_DDD) || trim(cend.num_telefone) || '"'
- end as telefone_primario,
- CASE
- WHEN cend.NUM_DDD2 is null or cend.num_telefone2 is null THEN '"' || '' || '"'
- else '"' || trim(cend.NUM_DDD2) || trim(cend.num_telefone2) || '"'
- end as telefone_secundario,
- '"' || '' || '"' e_mail,
- '"' || trim(cv.des_site) || '"' site,
- '"' || '' || '"' log_marca,
- '"' || trim(cv.num_cgc_cpf) || '"' CNPJ,
- '"' || '' || '"' priorizacao,
- '"' || trim(cend.loc_latitude) || '"' latitude,
- '"' || trim(cend.loc_longitude) || '"' longitude,
- '"' || trim(cend.num_seq_end) || '"' sequencial_endereco,
- TO_CHAR(cv.dat_prev_subst, 'DD/MM/RRRR') dt_bloqueio ,
- '"' || '' || '"' motivo_bloqueio,
- '"' || (select trim(decode(hc.tip_hist, 1, 0, hc.tip_hist))
- from tb_hist_conv hc
- where hc.cod_convenente = cv.cod_convenente
- and hc.num_seq_hist =
- (select max(hcv.num_seq_hist)
- from tb_hist_conv hcv
- where hcv.cod_convenente = hc.cod_convenente)) || '"' operacao,
- '"' || '1.2' || '"' Versao_layout,
- '"' || trim(REPLACE(TRIM(cv.nom_razao_social),CHR(10),' ')) || '"' razao_social,
- '"' || trim(ccl.cod_tipo_conselho) || '"' sigla_conselho,
- '"' || trim(cv.cod_uf_ident_prof) || '"' uf_conselho_regional,
- '"' || trim(cv.num_identidade_prof) || '"' numero_conselho_regional,
- '"' || trim(cv.nom_responsavel) || '"' responsavel_tecnico,
- '"' || '' || '"' facebook, -- verificar onde busca essa informac?o
- '"' || '' || '"' twitter, -- verificar onde busca essa informac?o
- '"' || TRIM(cv.des_observacao) || '"' observacoes, -- verificar onde busca essa informac?o
- '"' || '' || '"' acessibilidade, -- verificar onde busca essa informac?o
- '"' || '' || '"' detalhe_acessibilidade, -- verificar onde busca essa informac?o
- '"' || '' || '"' atend_24_horas, -- verificar onde busca essa informac?o
- '"' || '' || '"' link_agenda_online, -- verificar onde busca essa informac?o
- CASE
- WHEN cv.IDC_REDE_REF = 'S' and cesp.cod_especialid not in (555,666)THEN
- '"'||'2'||'"'
- WHEN cv.IDC_REDE_REF = 'N' and cesp.cod_especialid in (555,666) OR (cv.des_observacao collate binary_ai like '%PROJETO REDE ESPECIAL%') THEN
- '"'||'1'||'"'
- else
- '"'||'3' ||'"'
- END AS secao_resultado ,
- '"' || '' || '"' regime_atendimento, -- verificar onde busca essa informac?o
- '"' || '' || '"' CNPJ_SBT, -- verificar onde busca essa informac?o
- '"' || '' || '"' CODCBO_SBT,
- '"' || '' || '"' CLASSE_SBT,
- '"' || '' || '"' SEQEND_SBT,
- '"' || '' || '"' EMPRESA_ID,
- '"' || '' || '"' RESERVADO,
- TO_CHAR(cv.dat_ini_contrato, 'DD/MM/RRRR') DT_INI_ATD,
- to_char(add_months(sysdate, -6), 'dd/mm/yyyy') Hoje_6_menos_meses,
- TO_char(SYSDATE, 'DD/MM/yyyy') hoje,
- '1' teste
- from tb_conv_plano cvp
- join tb_convenente cv
- on cv.cod_convenente = cvp.cod_convenente
- and cv.sit_convenente = '1'
- and cv.cod_classe not in (9, 32, 34, 35, 38, 11, 33, 37,10,14,29)
- join tb_end_conven cend
- on cend.cod_convenente = cv.cod_convenente
- and cend.idc_end_corresp = 'N'
- and cend.idc_desativado = 'N'
- --FAZ TRAZER APENAS UM ENDETECO DE PRESTADOR
- /* and cend.num_seq_end = (select nvl(max(cend2.num_seq_end), 1) from tb_end_conven cend2
- where cend2.cod_convenente = cend.cod_convenente
- and cend2.idc_end_corresp = 'N'
- and cend2.idc_desativado = 'N')*/
- join tb_esp_conven cesp
- on cesp.cod_convenente = cv.cod_convenente
- and cesp.idc_catalogo = 'S'
- join tb_classe_conveniado ccl
- on ccl.cod_classe = cv.cod_classe
- join tb_plano pl
- on pl.cod_plano = cvp.cod_plano
- join (select p.cod_plano
- from tb_plano p,
- tb_regulamento r,
- tb_cobertura c
- where p.cod_plano = r.cod_plano
- and r.cod_cobertura = c.cod_cobertura
- and r.cod_cobertura = 101
- and COALESCE(TO_NUMBER(REGEXP_REPLACE(P.NUM_REGISTRO_PLANO,'[^0-9]+','')),0) <> 0
- and p.cod_plano not in (1,171,172,173,174,175)--lhsia 16/04/2019 n?o enviar extensive - retirar comentario em 09/2019
- and p.cod_plano in (select pp.cod_plano
- from tb_particip_plano pp
- where pp.cod_plano = p.cod_plano
- and pp.sit_partic_plano = 'A')) pl
- on pl.cod_plano = cvp.cod_plano
- --PJED
- left join att.FATTBLEECENDESPCONV eec
- on eec.cod_convenente = cv.cod_convenente
- and eec.cod_especialid = cesp.cod_especialid
- and eec.num_seq_end = cend.num_seq_end
- where pl.num_registro_plano is not null
- and (cesp.cod_especialid not in
- (122, 121, 131, 155, 123, 124, 641, 852, 858, 125, 126, 127, 128, 129, 154, 156, 851, 132, 850)
- or cesp.cod_especialid = 122 and cv.cod_convenente in (402064, 403822))
- and (cv.cod_convenente <> 402636
- or (cv.cod_convenente = 402636 and pl.cod_plano in (76, 82, 85, 86)))
- and not exists (select ' '
- from tb_conv_plano s
- where s.cod_convenente = cv.cod_convenente
- and s.cod_plano in (4, 79, 179))
- or cv.cod_convenente in (402064, 403822)
- union
- select
- '"' || trim(cesp.cod_especialid) || '"' codigo_cbo,
- '"' || trim(pl.cod_plano) || '"' codigo_plano,
- '"' || decode(ccl.cod_classe, 14, '10', 29, '10', trim(ccl.cod_classe)) || '"' classe_prestador,
- '"' || trim(cv.cod_convenente) || '"' codigo_legado,
- '"' || trim(cv.nom_convenente) || '"' nome_prestador,
- '"' || trim(cv.cod_sexo) || '"' sexo,
- '"' || trim(cend.nom_logradouro) || '"' endereco,
- '"' || trim(cend.num_logradouro) || '"' numero,
- '"' || trim(cend.des_complemento) || '"' complemento,
- '"' || trim(cend.nom_bairro) || '"' bairro,
- '"' || trim(cend.cod_munici) || '"' codigo_municipio,
- '"' || trim(cend.cod_estado) || '"' codigo_UF,
- '"' || trim(cend.num_cep) || '"' cep,
- '"' || trim(eec.cod_especialid) || '"' Especialidade,
- CASE
- WHEN cend.NUM_DDD is null and cend.num_telefone is null THEN '"' || '' || '"'
- else '"' || trim(cend.NUM_DDD) || trim(cend.num_telefone) || '"'
- end as telefone_primario,
- CASE
- WHEN cend.NUM_DDD2 is null and cend.num_telefone2 is null THEN '"' || '' || '"'
- else '"' || trim(cend.NUM_DDD2) || trim(cend.num_telefone2) || '"'
- end as telefone_secundario,
- '"' || '' || '"' e_mail,
- '"' || trim(cv.des_site) || '"' site,
- '"' || '' || '"' log_marca,
- '"' || trim(cv.num_cgc_cpf) || '"' CNPJ,
- '"' || '' || '"' priorizacao,
- '"' || trim(cend.loc_latitude) || '"' latitude,
- '"' || trim(cend.loc_longitude) || '"' longitude,
- '"' || trim(cend.num_seq_end) || '"' sequencial_endereco,
- TO_CHAR(cv.dat_prev_subst, 'DD/MM/RRRR') dt_bloqueio ,
- '"' || '' || '"' motivo_bloqueio,
- '"' || (select trim(decode(hc.tip_hist, 1, 0, hc.tip_hist))
- from tb_hist_conv hc
- where hc.cod_convenente = cv.cod_convenente
- and hc.num_seq_hist =
- (select max(hcv.num_seq_hist)
- from tb_hist_conv hcv
- where hcv.cod_convenente = hc.cod_convenente)) || '"' operacao,
- '"' || '3.2' || '"' Versao_layout,
- '"' || trim(REPLACE(TRIM(cv.nom_razao_social),CHR(10),' ')) || '"' razao_social,
- '"' || trim(ccl.cod_tipo_conselho) || '"' sigla_conselho,
- '"' || trim(cv.cod_uf_ident_prof) || '"' uf_conselho_regional,
- '"' || trim(cv.num_identidade_prof) || '"' numero_conselho_regional,
- '"' || trim(cv.nom_responsavel) || '"' responsavel_tecnico,
- '"' || '' || '"' facebook, -- verificar onde busca essa informac?o
- '"' || '' || '"' twitter, -- verificar onde busca essa informac?o
- '"' || TRIM(cv.des_observacao) || '"' observacoes, -- verificar onde busca essa informac?o
- '"' || '' || '"' acessibilidade, -- verificar onde busca essa informac?o
- '"' || '' || '"' detalhe_acessibilidade, -- verificar onde busca essa informac?o
- '"' || '' || '"' atend_24_horas, -- verificar onde busca essa informac?o
- '"' || '' || '"' link_agenda_online, -- verificar onde busca essa informac?o
- CASE
- WHEN cv.IDC_REDE_REF = 'S' and cesp.cod_especialid not in (555,666) THEN '"'||'2'||'"'
- WHEN cv.IDC_REDE_REF = 'N' and cesp.cod_especialid in (555,666) OR (cv.des_observacao collate binary_ai like '%PROJETO REDE ESPECIAL%')
- THEN '"'||'1'||'"'
- ELSE '"'||'3' ||'"'
- END AS secao_resultado,
- '"' || '' || '"' regime_atendimento, -- verificar onde busca essa informac?o
- '"' || '' || '"' CNPJ_SBT, -- verificar onde busca essa informac?o
- '"' || '' || '"' CODCBO_SBT,
- '"' || '' || '"' CLASSE_SBT,
- '"' || '' || '"' SEQEND_SBT,
- '"' || '' || '"' EMPRESA_ID,
- '"' || '' || '"' RESERVADO,
- TO_CHAR(cv.dat_ini_contrato, 'DD/MM/RRRR') DT_INI_ATD,
- to_char(add_months(sysdate, -6), 'dd/mm/yyyy') Hoje_6_menos_meses ,
- TO_char(SYSDATE, 'DD/MM/yyyy') hoje,
- '2' teste
- From tb_convenente cv,
- tb_end_conven cend,
- tb_esp_conven cesp,
- tb_especialid es,
- tb_conv_plano pl,
- municipio mu,
- tb_classe_conveniado ccl,
- tb_plano p,
- att.FATTBLEECENDESPCONV eec
- Where cv.cod_convenente = cend.cod_convenente
- and cv.cod_convenente = cesp.cod_convenente
- and cesp.cod_especialid = es.cod_especialid
- and cv.cod_convenente = pl.cod_convenente
- and cend.cod_munici = mu.cod_munici
- and cv.cod_classe = ccl.cod_classe
- and cend.idc_end_corresp = 'N'
- and cesp.idc_catalogo = 'S'
- and cv.sit_convenente = '1'
- and cend.idc_desativado = 'N'
- and es.cod_especialid in
- (121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 850, 852, 154, 158, 156, 851)
- and cv.cod_classe in (10, 14, 29)
- and cv.cod_convenente not in (402064, 403822)
- and pl.cod_plano = p.cod_plano
- and p.cod_programa = 2
- and exists (select ' ' from tb_particip_plano pp
- where pp.cod_plano = p.cod_plano
- and pp.sit_partic_plano = 'A')
- and (cv.cod_convenente (+) = eec.cod_convenente
- and cesp.cod_especialid (+) = eec.cod_especialid
- and cesp.cod_especialid(+) = es.cod_especialid
- and cesp.cod_convenente(+) = cend.cod_convenente
- and cend.num_seq_end (+) = eec.num_seq_end)
- order by 5 ;
- strLinha Varchar2(1024);
- strArquivo Varchar2(80);
- strDiretorio Varchar2(80);
- sgGlobalName varchar2(1000);
- sgHostName varchar2(100);
- processou varchar2(1);
- fHandle UTL_FILE.FILE_TYPE;
- BEGIN
- -- Select DIR_DESTINO into strDiretorio from OPPORTUNITY.TB_PARAM_GERAL;
- -- strDiretorio := 'DIR_WORK';
- select global_name into sgGlobalName from global_name;
- if upper(sgGlobalName) = 'FCESPP.WORLD' then
- select substr(host_name,1,9) into sgHostName from v$instance;
- if lower(sgHostName) = 'fcespracp' then
- sgGlobalName := 'PROD';
- Select DIR_DESTINO into strDiretorio from OPPORTUNITY.TB_PARAM_GERAL;
- elsif upper(sgHostName) = 'FCESPORAH' then
- sgGlobalName := 'NEWTST';
- Select DIR_DESTINO into strDiretorio from OPPORTUNITY.TB_PARAM_GERAL;
- else
- sgGlobalName := 'AMBIENTE DE TESTE';
- end if;
- elsif upper(sgGlobalName) = 'FCESPP.NEWTST' then
- sgGlobalName := 'NEWTST';
- Select DIR_DESTINO into strDiretorio from OPPORTUNITY.TB_PARAM_GERAL;
- elsif upper(sgGlobalName) = 'NEWDEV.WORLD' then
- sgGlobalName := 'NEWDEV';
- strDiretorio:='DIR_WORK';
- end if;
- strArquivo := 'gm_ms_redecred_' || TO_CHAR(SYSDATE, 'yyyymmdd') || '_' ||
- to_char(SYSDATE, 'hh24miss') || '.csv';
- fHandle := utl_file.fopen(strDiretorio, strArquivo, 'w');
- UTL_FILE.FFLUSH(fHandle);
- strLinha := '"codigo_cbo";"codigo_plano";"classe_prestador";"codigo_legado";"nome_prestador";"sexo";"endereco";"numero";"complemento";"bairro";"codigo_municipio";"codigo_uf";"cep";"telefone_primario";"telefone_secundario";"email";"site_url";"nome_logomarca";"cpf_cnpj";"prioridade";"latitude";"longitude";"sequencial_endereco";"data_bloqueio";"motivo_bloqueio";"operacao";"versao_layout";"razao_social";"sigla_conselho_regional";"uf_conselho_regional";"numero_conselho_regional";"nome_responsavel_tecnico";"facebook";"twitter";"observacoes";"acessibilidade";"detalhe_acessibilidade";"atend_24_horas";"link_agenda_online";"secao_resultado";"regime_atendimento";"cpf_cnpj_subst";"codigo_CBO_subst";"classe_prestador_subst";"sequencial_endereco_subst";"empresa_id";"reservado1";"dt_inicio_atend"' ||
- chr(13) || chr(10);
- utl_file.put(fHandle, strLinha);
- For cPrinc in CurPrinc Loop
- strLinha := cPrinc.codigo_cbo || ';';
- strLinha := StrLinha || cPrinc.codigo_plano || ';';
- strLinha := StrLinha || cPrinc.classe_prestador || ';';
- strLinha := StrLinha || cPrinc.codigo_legado || ';';
- strLinha := StrLinha || cPrinc.nome_prestador || ';';
- strLinha := StrLinha || cPrinc.sexo || ';';
- strLinha := StrLinha || cPrinc.endereco || ';';
- strLinha := StrLinha || cPrinc.numero || ';';
- strLinha := StrLinha || cPrinc.complemento || ';';
- strLinha := StrLinha || cPrinc.bairro || ';';
- strLinha := StrLinha || cPrinc.codigo_municipio || ';';
- strLinha := StrLinha || cPrinc.codigo_UF || ';';
- strLinha := StrLinha || cPrinc.cep || ';';
- strLinha := StrLinha || cPrinc.telefone_primario || ';';
- strLinha := StrLinha || cPrinc.telefone_secundario || ';';
- strLinha := StrLinha || cPrinc.e_mail || ';';
- strLinha := StrLinha || cPrinc.site || ';';
- strLinha := StrLinha || cPrinc.log_marca || ';';
- strLinha := StrLinha || cPrinc.CNPJ || ';';
- strLinha := StrLinha || cPrinc.priorizacao || ';';
- strLinha := StrLinha || cPrinc.latitude || ';';
- strLinha := StrLinha || cPrinc.longitude || ';';
- strLinha := StrLinha || cPrinc.sequencial_endereco || ';';
- strLinha := StrLinha || '"'||cPrinc.dt_bloqueio || '"'|| ';';
- strLinha := StrLinha || cPrinc.motivo_bloqueio || ';';
- strLinha := StrLinha || cPrinc.operacao || ';';
- strLinha := StrLinha || cPrinc.Versao_layout || ';';
- strLinha := StrLinha || cPrinc.razao_social || ';';
- strLinha := StrLinha || cPrinc.sigla_conselho || ';';
- strLinha := StrLinha || cPrinc.uf_conselho_regional || ';';
- strLinha := StrLinha || cPrinc.numero_conselho_regional || ';';
- strLinha := StrLinha || cPrinc.responsavel_tecnico || ';';
- strLinha := StrLinha || cPrinc.facebook || ';';
- strLinha := StrLinha || cPrinc.twitter || ';';
- strLinha := StrLinha || cPrinc.observacoes || ';';
- strLinha := StrLinha || cPrinc.acessibilidade || ';';
- strLinha := StrLinha || cPrinc.detalhe_acessibilidade || ';';
- strLinha := StrLinha || cPrinc.atend_24_horas || ';';
- strLinha := StrLinha || cPrinc.link_agenda_online || ';';
- strLinha := StrLinha || cPrinc.secao_resultado || ';';
- strLinha := StrLinha || cPrinc.regime_atendimento || ';';
- strLinha := StrLinha || cPrinc.CNPJ_SBT || ';';
- strLinha := StrLinha || cPrinc.CODCBO_SBT || ';';
- strLinha := StrLinha || cPrinc.CLASSE_SBT || ';';
- strLinha := StrLinha || cPrinc.SEQEND_SBT || ';';
- strLinha := StrLinha || cPrinc.EMPRESA_ID || ';';
- strLinha := StrLinha || cPrinc.RESERVADO || ';';
- strLinha := StrLinha || '"'||cPrinc.DT_INI_ATD || '"'|| ';' || chr(13) || chr(10);
- utl_file.put(fHandle, convert(strLinha, 'UTF8'));
- utl_file.fflush(fHandle);
- -- utl_file.put_line(fHandle, strLinha);
- End Loop;
- utl_file.fclose(fHandle);
- EXCEPTION
- when others then
- DBMS_OUTPUT.put_line(SQLCODE || '-' || SQLERRM);
- END; --