OhBoyItsWorking
commented
1 month ago
/* *****ESSE AQUI É O SCRIPT MAIS ATUAL POSSIVEL, USAR APENAS ESSE ***** */
spool C:\output\cad.txt
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
--update c_cad set email = 'jean _cb@brturbo.com.br' where cpf = '040.028.879-60';
select '00000CADv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from c_cad fetch first 1 rows only;
select 'CAD01'
|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| '0000'
--isenta_iof sempre F... não vou implementar no case abaixo...
|| case fisica when 'F' then '01' else '03' end --'classificacao da pessoa, tipo fisica, fisica imune....'
|| fisica
|| substr(nome||rpad(' ',80,' '),1,80)
|| substr(apelido||rpad(' ',26,' '),1,26)--rpad(' ',26,' ')
|| substr(email||rpad(' ',150,' '),1,150)
|| to_char(dt_nivel,'YYYYMMDD') -- data da ultima analise cadastral
|| case serasa when 'F' then 'N' else 'S' end
|| 'O'--tipo do imovel?!!?!?!?!?!
|| '000000000' --custo mensal do imovel
|| '0000' --tempo de residencia no imovel
|| nivel --cast(1 as varchar) --risco do cooperado --antigo nr_original
|| case enquadra_pep when 'F' then '0' else '1' end
|| '0' -- US Person
|| '0' -- nao eh produtor rural
as "output_file" from c_cad where 1 = case when fisica = 'J' and cgc = '00.000.000/0000-00' then 0 else 1 end;
select 'CAD02'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| '0000' --posto sede
|| case c.est_civil when 'Divorciada' then 'D' when 'Solteira' then 'S'
when 'Solteiro' then 'S' when 'Casado' then 'C' when 'SOLTEIRO' then 'S'
when 'Viúva' then 'V' when 'Casada' then 'C' when 'União Estável' then 'U'
when 'Desquitado' then 'O' when 'Divorciado' then 'D' when 'Viúvo' then 'V' else 'O' end
|| case when lower(c.est_civil) like 'solteir%' then 'N' else 'C' end
|| c.sexo --subestimei os manos nesse campo aqui ein?!?!?! desgurpe
|| '00' -- não foi encontrado o numero de dependentes
|| to_char(c.nascimento,'YYYYMMDD')
|| substr(coalesce(c.naturalidade,'CURITIBA')||rpad(' ',20,' '),1,20)
|| case c.nacionalidade when 'Brasileira' then 'BR'
when 'BRASILEIRA' then 'BR'
when 'Brasileiro' then 'BR'
when 'PERUANA' then 'PE'
when 'VENEZUELA' then 'VE'
else 'BR'
end --mds... mmmmmeeeeeeeeeeeuuuuu deus.... ajuda
|| substr(lpad('0',14,'0')||regexp_replace(cu.salario*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',15,'0')||regexp_replace(c.ci, '[^0-9]', ''),-15,15)
|| substr(c.ORG_EXP||rpad(' ',10,' '),1,10)
|| to_char(coalesce(c.DT_CI,DATE '1900-01-01'),'YYYYMMDD')
|| substr(cu.profissao||rpad(' ',15,' '),1,15) --FALTA: 'REG_PROFISSIONAL'
|| '14' --FALTA: Orgao profissional
|| 'PR' --FALTA: UF do orgao/
|| substr(c.PAI||rpad(' ',65,' '),1,65)
|| substr(c.MAE||rpad(' ',65,' '),1,65)
|| '003646'--substr(cu.profissao||rpad(' ',6,' '),1,6) --FALTA: CBO da profissao
|| substr(c.conjugue||rpad(' ',65,' '),1,65)
|| to_char(coalesce(c.DT_NASC_CONJUGE,DATE '1900-01-01'),'YYYYMMDD')
|| substr(c.PROFCONJUGE||rpad(' ',6,' '),1,6) --FALTA: CBO do conjuge --PROFISSAO_CONJUGE2
|| substr(replace(replace(c.CPFCONJUGE,'.',''),'-','')||rpad(' ',11,' '),1,11)
|| substr(c.CICONJUGE||rpad(' ',12,' '),1,12)
|| to_char(coalesce(c.DT_CICONJUGE, DATE '1900-01-01'),'YYYYMMDD')
|| substr(c.ORG_EXPCONJUGE||rpad(' ',10,' '),1,10)
|| rpad(' ',30,' ')--substr(c.PROFISSAO_CONJUGE2||rpad(' ',30,' '),1,30)
|| substr(c.MAE_CONJUGE||rpad(' ',65,' '),1,65)
|| substr(c.PAI_CONJUGE||rpad(' ',65,' '),1,65)
|| substr(c.EMAIL_CONJUGE||rpad(' ',60,' '),1,60)
|| substr(lpad('0',14,'0')||regexp_replace(c.RENDACONJUGE*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',2,'0')||coalesce(grau,0),-2,2) -- precisa ajustar o grau
|| lpad('0',9,'0') --matricula vinculada, não encontramos nada ainda
|| 'O' --tipo de vinculo
|| lpad('0',14,'0') -- pensao... tudo nulo na tabela
|| '000' -- mesma coisa pra quantidade de meses
|| coalesce(ci.uf,'PR') --mesma gambiarra da naturalidade
|| substr(lpad('0',2,'0')|| c.UFORG,-2,2) --lembrando que é obrigatorio
|| to_char(coalesce(cu.DT_SAL, DATE '1900-01-01'),'MMYYYY')
|| '1' -- só tem rg aqui...
|| c.porte -- precisa validar as faixas
|| '0' -- nao tem representante, nao tem modalidade
|| lpad('0',14,'0') -- ta vindo o patrimonio
|| upper(cu.profissao)
as "output_file" from c_cad c
left join c_caduni cu on c.conta = cu.conta
left join c_cidade ci on upper(coalesce(c.naturalidade,'CURITIBA')) = upper(c.nome)
where fisica = 'F'
order by c.conta;
select 'CAD03'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| '0000' --posto sede
|| tipo_inst
|| '2062'
|| to_char(coalesce(c.NASCIMENTO,DATE '1900-01-01'),'YYYYMMDD')
|| rpad(' ',15,' ')
|| '00000000'
|| rpad(' ',20,' ')
|| '00000000'
|| substr(c.INSC_ESTADUAL||rpad(' ',9,' '),1,9)
|| rpad(' ',9,' ')
|| substr(lpad('0',14,'0')||replace(to_char(coalesce(c.vrfaturamento,0)),'.',''),-14,14)
|| '000'|| to_char(case porte when 0 then 1 else porte end)
|| '0001' --tipo controle
|| rpad(' ',7,' ')
|| '000000' --ano mes faturamento
|| '0000'
|| ' '
|| '0000'
|| 'S'
|| '000000000'
|| ' '
|| ' '
|| ' '
as "output_file" from c_cad c
left join c_caduni cu on c.conta = cu.conta
left join c_cidade ci on upper(coalesce(c.naturalidade,'CURITIBA')) = upper(c.nome)
where fisica = 'J'
and cgc <> '00.000.000/0000-00'
order by c.conta;
select 'CAD04'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| 'R'
|| substr(coalesce(endok.endereco,c.endereco) || rpad(' ',60,' '),1,60)
|| substr(lpad('0',10,'0')||replace(to_char(coalesce(endok.end_num,c.end_num,0)),'.',''),-10,10)
|| substr(coalesce(endok.compl_end,c.compl_end) || rpad(' ',30,' '),1,30) --|| rpad(' ',30,' ')
|| substr(b.nome||rpad(' ',30,' '),1,30)
|| substr(replace(c.cep,'-','')||lpad('0',8,'0'),1,8)
|| ci.cod_municipio_ibge
|| rpad(' ',10,' ')
|| 'S'
as "output_file" from c_cad c
left join (select conta, max(anomes) ano_mes from c_sda group by conta) endatu on endatu.conta = c.conta
left join c_sda endok on endok.conta = endatu.conta and endok.anomes = endatu.ano_mes
left join c_caduni cu on c.conta = cu.conta
left join c_cidade ci on c.cod_cid = ci.cod_cid
left join c_bairro b on c.cod_bai = b.cod_bai
where 1 = case when c.fisica = 'F' then 1 else case when c.cgc is not null or c.cgc <> '00.000.000/0000-00' then 1 else 0 end end
order by c.conta;
select 'CAD05'
|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| '1'
|| '0' || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),1,2)
|| substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),3,10),-10,10)
as "output_file" from c_cad c
where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000'
and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05'
|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| '2'
|| '0' || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),1,2)
|| substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),3,10),-10,10)
as "output_file" from c_cad c
where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000'
and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05'
|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| '3'
|| '0' || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),1,2)
|| substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),3,10),-10,10)
as "output_file" from c_cad c
where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000'
and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05'
|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| '3'
|| '0' || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),1,2)
|| substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),3,10),-10,10)
as "output_file" from c_cad c
where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000'
and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end;
select distinct 'CAD10'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| '0000'
|| substr(s.nome||rpad(' ',65,' '),1,65)
|| substr(lpad('0',12,'0')||regexp_replace(s.ci, '[^0-9]', ''),-12,12)
|| s.fisica--case s.cpf when null then 'J' else 'F' end
|| substr(lpad('0',14,'0')||regexp_replace(coalesce(s.cpf, s.cnpj), '[^0-9]', ''),-14,14)
|| substr(lpad('0',5,'0')||regexp_replace(s.perc_rateio*100, '[^0-9]', ''),-5,5)
|| '2135' --anexo 4?
|| to_char(coalesce(s.nascimento,DATE '1900-01-01'),'YYYYMMDD')
|| '00000'
|| '0000000000'
|| substr(s.email||rpad(' ',50,' '),1,50)
|| '1'
as "output_file" from c_cad c
join c_socio s on s.conta = c.conta
;
select 'CAD11'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| '0000'
|| case a.situacao when 'Alienado' then 'A' else 'L' end
|| substr(a.modelo||rpad(' ',150,' '),1,150)
|| substr(a.modelo||rpad(' ',150,' '),1,150)
|| coalesce(to_char(a.ano_fab),'1900')
|| substr(lpad('0',14,'0')||regexp_replace(a.valor*100, '[^0-9]', ''),-14,14)
|| substr(coalesce(a.num_chassi,'')||rpad(' ',20,' '),1,20)
|| substr(lpad('0',15,'0')||coalesce(a.renavan,''),1,15)
|| coalesce(to_char(a.ano_mod),'1900')
--|| substr(a.num_placa||rpad(' ',8,' '),1,8)
as "output_file" from cm_auto a
join c_cad c on a.conta = c.conta
;
select 'CAD12'
|| replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','')
|| '0000'
|| case a.tipo when 'Livre' then 'L' when 'Financiado' then 'F' when 'Alienado' then 'H' else 'L' end
|| case lower(a.denominacao) when 'casa' then '1' when 'outros' then '7' when 'terreno' then '3' when 'SOBRADO' then '1' when 'apartamento' then '2' else '7' end
|| substr(lpad('0',9,'0')||regexp_replace(a.area*100, '[^0-9]', ''),-9,9)
|| substr(a.endereco||rpad(' ',60,' '),1,60)
|| substr(lpad('0',14,'0')||regexp_replace(a.valor*100, '[^0-9]', ''),-14,14)
|| substr(a.reg_mat||rpad(' ',10,' '),1,10)
|| substr(lpad('0',9,'0')||regexp_replace(a.area*100, '[^0-9]', ''),-9,9)
|| substr(a.reg_mat||rpad(' ',15,' '),1,15)
|| substr('cri_livro:'||coalesce(a.cri_livro,'null')||'. onus_esp: '||coalesce(a.onus_esp,'null')||'. declarado: '||a.declarado||rpad(' ',250,' '),1,250)
|| d.cod_municipio_ibge
as "output_file" from cr_imou a
join c_cad c on a.conta = c.conta
join c_cidade d on d.cod_cid = a.cod_cid
;
select '999990000000001' from c_cad fetch first 1 rows only;
spool off;
spool C:\output\cap.txt
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
select '00000CAPv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from c_cad fetch first 1 rows only;
select 'CAP01'
--|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','')
|| substr(lpad('0',9,'0')||regexp_replace(coalesce(c.conta, cc.cod_integra), '[^0-9]', ''),-9,9)
|| '0000'
|| substr(lpad('0',14,'0')||regexp_replace(case c2.fisica when 'F' then c2.cpf else c2.cgc end, '[^0-9]', ''),-14,14)
|| '000000000'
|| '000000001'
|| '0001'
|| substr(lpad('0',14,'0')||regexp_replace(sc.sd*100, '[^0-9]', ''),-14,14)
|| '00000000000000'
|| 'A'--c.status
|| to_char(c.data_inc,'YYYYMMDD')
|| to_char(coalesce(c2.demissao, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',9,'0')||regexp_replace(coalesce(cc.cod_integra, c.conta), '[^0-9]', ''),-9,9)
as "output_file"
from cc_cadassoc c
join c_cad c2 on c.conta = c2.conta
join (select conta, max(anomes) ult_data from a_sda group by conta) ud on ud.conta = c2.conta
join a_sda sc on sc.conta = ud.conta and sc.anomes = ud.ult_data
--join cc_cad nt on nt.contac = c2.conta
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = c.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = c.conta and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
order by c2.conta;
select 'CAP04'
|| substr(lpad('0',9,'0')||regexp_replace(coalesce(c.conta, cc.cod_integra), '[^0-9]', ''),-9,9)
|| regexp_replace(sc.anomes, '[^0-9]', '')
|| substr(lpad('0',14,'0')||regexp_replace(sc.sd*100, '[^0-9]', ''),-14,14)
as "output_file"
from cc_cadassoc c
join c_cad c2 on c.conta = c2.conta
join a_sda sc on sc.conta = c.conta
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = c.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = c.conta and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
order by c2.conta;
select '999990000000001' from c_cad fetch first 1 rows only;
spool off;
spool C:\output\cco.txt
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
select '00000CCOv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from c_cad fetch first 1 rows only;
select 'CCO01'
|| regexp_replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end, '[^0-9]', '')
|| '0000'
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| '1'
|| to_char(coalesce(cc.dt_abert, date '1900-01-01'), 'YYYYMMDD')
|| '1' -- Confirmar Modalidades de contas
|| to_char(cc.ativo) --Confirmar situacaoes de conta
|| '0'--quantidade de taloes em estoque
|| '19000101'
|| to_char(coalesce(e2.dt_fim, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(e2.lim_chesp*100, '[^0-9]', ''),-14,14)
|| substr(rpad('0',10,'0')||regexp_replace(e2.id_chesp, '[^0-9]', ''),-10,10)
|| substr(lpad('0',04,'0')||regexp_replace(round((power(1+(e2.val_juros/100),12)-1)*100,2), '[^0-9]', ''),-04,04)
|| 'N' -- Renovacao automatica do cheque especial
|| to_char(coalesce(cc.ult_lanc_cheq, date '1900-01-01'), 'YYYYMMDD')
--|| substr(lpad('0',14,'0')||regexp_replace(sal.saldo_ad*100, '[^0-9]', ''),-14,14)
|| '00000000000000'
|| '19000101' --data de utilizacao do cheque especial nao encontrada
|| coalesce(substr(regexp_replace(cc.cliente_desde, '[^0-9]', ''),3,4)||substr(regexp_replace(cc.cliente_desde, '[^0-9]', ''),1,2),'190001')||'01' --mds mano...
|| substr(lpad('0', 4,'0')||regexp_replace(round((power(1+(e2.CET/100),12)-1)*100,2), '[^0-9]', ''),-04,04)
|| 'N'-- Tarifa cheque especial nao encontrada
|| 'N' --conta compe propra, pedir identificacao ao Paulo
-- || '00000000000000'
-- || '00000000000000'
|| substr(lpad('0',14,'0')||regexp_replace(sal.saldo*100, '[^0-9-]', ''),-14,14)
|| substr(lpad('0',14,'0')||regexp_replace(sal.saldo_bloq*100, '[^0-9]', ''),-14,14)
as "output_file"
from c_cad c
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = c.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = c.conta --and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
left join (select contac, max(dt_inicio) dt_ini from cc_chesp group by contac) e on a.contac = e.contac
left join cc_chesp e2 on e2.contac = e.contac and e2.dt_inicio = e.dt_ini
--left join cc_chesp esp on esp.contac = cc.contac
left join cc_saldo sal on sal.contac = cc.contac and sal.data in (select max(data) from cc_saldo)
--where substr(lpad('0',9,'0')||regexp_replace(c.contac, '[^0-9]', ''),-9,9) = '183590011'
--where cc.cod_integra like '%70972%'
order by c.conta;
select 'CCO02'
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| to_char(coalesce(dt_emissao, date '1900-01-01'), 'YYYYMMDD')
|| substr(rpad('0',5,'0')||regexp_replace(cod_talao, '[^0-9]', ''),-4,4)
|| substr(rpad('0',6,'0')||regexp_replace(num_cheque, '[^0-9]', ''),-6,6)
|| substr(rpad('0',6,'0')||regexp_replace(num_cheque + num_folhas, '[^0-9]', ''),-6,6)
|| substr('00'||regexp_replace(num_folhas, '[^0-9]', ''),-2,2)
|| case cod_situacao when 4 then '5' when 2 then '4' when 5 then '4'
when 8 then '5' else '6' end --as 'sittalao' --nome da tabela de situacao talao
|| substr(rpad('0',3,'0')||regexp_replace(serie, '[^0-9]', ''),-3,3)
from cc_talao c
inner join cc_cad cc on cc.contac = c.contac
where (cc.cod_integra is not null and regexp_replace(cc.cod_integra, '[^0-9]', '') > 0);
-- and cc.cod_integra like '%51877%';
select 'CCO03'
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, tl.contac), '[^0-9X]', ''),-11,11)
|| substr(rpad('0',5,'0')||regexp_replace(ch.cod_talao, '[^0-9]', ''),-4,4)
|| substr(rpad('0',3,'0')||regexp_replace(tl.serie, '[^0-9]', ''),-3,3)
|| substr(rpad('0',6,'0')||regexp_replace(ch.num_cheque, '[^0-9]', ''),-6,6)
|| 'B'
|| substr('00'||regexp_replace(prox_motdev, '[^0-9]', ''),-2,2)
|| '00'
|| substr(rpad('0',14,'0')||regexp_replace(ch.valor, '[^0-9]', ''),-14,14)
|| to_char(coalesce(ch.dt_atualizado, date '1900-01-01'), 'YYYYMMDD')
from cc_cheque ch
inner join cc_talao tl on tl.cod_talao = ch.cod_talao
inner join cc_cad cc on cc.contac = tl.contac
where (cc.cod_integra is not null and regexp_replace(cc.cod_integra, '[^0-9]', '') > 0);
-- and cc.cod_integra like '%51877%';
/* SEGUNDOS TITULARES */
select 'CCO04'
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| regexp_replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end, '[^0-9]', '')
|| '2'
as "output_file"
from c_cad c
join cc_cadassoc a on a.titular = 'F' and a.conta = c.conta --and um.dt_inicio = a.data_inc
left join cc_cad cc on cc.contac = a.contac
--left join cc_chesp esp on esp.contac = cc.contac
--where substr(lpad('0',9,'0')||regexp_replace(c.contac, '[^0-9]', ''),-9,9) = '183590011'
--where cc.cod_integra like '%64333%'
order by c.conta;
select '999990000000001' from c_cad fetch first 1 rows only;
spool off;
spool C:\output\apl.txt;
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
select '00000APLv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from c_cad fetch first 1 rows only;
select 'APL01'
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| substr(lpad('0',4,'0')|| case cod_la when 16 then 1 when 20 then 2
when 21 then 3 when 23 then 4 when 24 then 5 when 25 then 6
when 26 then 7 when 32 then 8 when 33 then 9 when 36 then 10
when 37 then 11 when 38 then 12 when 39 then 13 else 99 end,-4,4)
|| substr(lpad('0',11,'0')||regexp_replace(contrato, '[^0-9]', ''),-11,11) --CAMPO e string e tem hifen no caralho ta coluna
|| substr(lpad('0',14,'0')||regexp_replace(valor*100, '[^0-9]', ''),-14,14)
|| to_char(coalesce(dt_emissao, date '1900-01-01'), 'YYYYMMDD')
|| to_char(coalesce(prev_resg, date '1900-01-01'), 'YYYYMMDD')
|| '00000000'--data do vencimento da carencia - confirmar
|| substr(lpad('0',5,'0')||regexp_replace(round(coalesce(fator,case cod_tbtaxa when 27 then 150 when 28 then 180 when 30 then 70 else 100 end),2)*100, '[^0-9]', ''),-5,5)--Percentual do indicador
|| substr(lpad('0',20,'0')||regexp_replace(coalesce(ap.id_cetip,''), '[^0-9]', ''),-20,20)
|| substr(lpad('0',18,'0')||regexp_replace(coalesce(valor_pu*100,0), '[^0-9]', ''),-18,18)
|| '00000000'--data da ultimo movimentacao, aparentemente nao tem
|| substr(lpad('0',14,'0')||regexp_replace(ap.saldo*100, '[^0-9]', ''),-14,14) -- confirmar se e esse valor que vai aqui
|| substr(lpad('0',14,'0')||regexp_replace(ctb_correcao, '[^0-9]', ''),-14,14) -- confirmar se valor esta correto
|| '000000'--confirmando onde esta a configuracao com a cooperativa
|| '00000000'-- data da ultima capitalização confirmar
from f_sda ap
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = ap.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = ap.conta and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
left join f_taxa tx on tx.cod_taxa = ap.cod_tbtaxa
where anomes = (select max (anomes) from f_sda)
and ap.saldo > 0
order by ap.contrato;
select '999990000000001' from c_cad fetch first 1 rows only;
spool off;
spool C:\output\emp.txt;
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
select '00000EMPv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'02' from c_cad fetch first 1 rows only;
select 'EMP01'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) -- Retirar hifen
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4)
|| to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(valor*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4)
|| to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(valor*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4)
|| '00000'
|| substr(lpad('0',5,'0')||regexp_replace(tx.jncap, '[^0-9]', ''),-5,5)
|| '00000' -- Juros de mora
|| case seg_prestamista when 'T' then 'S' else 'N' end-- Tratar Verdadeiro ou falso
|| to_char(coalesce(venc_fim, date '1900-01-01'), 'YYYYMMDD')
|| 'A'
|| emp.nv_atual
|| '00000' --multa por atraso
|| substr(lpad('0',34,'0')||regexp_replace(ipoc, '[^0-9]', ''),-34,34)--IPOC
|| 'A' -- Situacao da operacao
|| '20230228' --data da ultima movimentacao
|| '00000000' --Data do prejuizo
|| substr(lpad('0',14,'0')||regexp_replace(nsaldo.total*100, '[^0-9]', ''),-14,14)
|| 'N' -- Remetido ao juridico
|| '00000000' --data do envio a juridico
|| '00000000000000000000000000000000' -- numero bordero
|| '000000000' -- numero do contrato renegociado
|| '0000' --cod da linha renegociada
|| '00000000' -- DAta da renegociacao
|| substr(lpad('0',14,'0')||regexp_replace(cad.cgc, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4)
as rest from e_ctopen emp
inner join e_taxa tx on tx.cod_taxa = emp.cod_taxa
inner join c_cad cad on cad.conta = emp.conta
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = emp.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = emp.conta and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato,
sum(saldo) as total
from e_ctopen emp
group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo
on nsaldo.contrato = substr(lpad('0',10,'0')||
regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
where substr(emp.contrato,-3,3) = '000' and refaut = 'F' and nsaldo.total> 0
union all
select 'EMP01'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) -- Retirar hifen
--|| substr(lpad('0',9,'0')||regexp_replace(coalesce(cc.cod_integra, emp.conta), '[^0-9]', ''),-11,11)
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4) -- Confirmar se esse e o codigo da linha de credito mesmo
|| to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(coalesce(empj.valor_original,emp.valor)*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4)
|| to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(coalesce(empj.valor_original,emp.valor)*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4)
|| '00000'
|| substr(lpad('0',5,'0')||regexp_replace(tx.jncap, '[^0-9]', ''),-5,5)
|| '00000' -- Juros de mora
|| case seg_prestamista when 'T' then 'S' else 'N' end-- Tratar Verdadeiro ou falso
|| to_char(coalesce(venc_fim, date '1900-01-01'), 'YYYYMMDD')
|| 'A'
|| emp.nv_atual
|| '00000' --multa por atraso
|| substr(lpad('0',34,'0')||regexp_replace(ipoc, '[^0-9]', ''),-34,34)--IPOC
|| 'A' -- Situacao da operacao
|| '20230228' --data da ultima movimentacao
|| to_char(coalesce(empj.data, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(nsaldo.total*100, '[^0-9]', ''),-14,14)
|| 'N' -- Remetido ao juridico
|| '00000000' --data do envio a juridico
|| '00000000000000000000000000000000' -- numero bordero
|| '000000000' -- numero do contrato renegociado
|| '0000' --cod da linha renegociada
|| '00000000' -- DAta da renegociacao
|| substr(lpad('0',14,'0')||regexp_replace(cad.cgc, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4)
as rest from e_ctclos emp
inner join e_taxa tx on tx.cod_taxa = emp.cod_taxa
inner join c_cad cad on cad.conta = emp.conta
join (select conta, max(data_inc) dt_inicio from cc_cadassoc where titular = 'T' group by conta) um on um.conta = emp.conta
join cc_cadassoc a on a.titular = 'T' and a.conta = emp.conta and um.dt_inicio = a.data_inc
join cc_cad cc on cc.contac = a.contac
inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato,
sum(saldo) as total
from e_ctclos emp
group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo
on nsaldo.contrato = substr(lpad('0',10,'0')||
regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
left join e_sda_baixas empj on empj.anomes = '2024/07' and empj.contrato = emp.contrato
where substr(emp.contrato,-3,3) = '000' and refaut = 'F' and (nsaldo.total> 0 or empj.contrato is not null)
union all
select 'EMP01'
|| substr(lpad('0',10,'0')||regexp_replace(id_chesp, '[^0-9]', ''),-10,10)
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc2.cod_integra, cc.contac), '[^0-9]', ''),-11,11)
--|| substr(lpad('0',9,'0')||regexp_replace(coalesce(cc.cod_integra, cc2.contac), '[^0-9]', ''),-11,11)
|| '8888' --cheque especiais
|| to_char(coalesce(dt_inicio, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(lim_chesp, '[^0-9]', ''),-14,14)
|| '0001'
|| to_char(coalesce(dt_inicio, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(lim_chesp, '[^0-9]', ''),-14,14)
|| '0001'
|| '00000'
|| substr(lpad('0',5,'0')||regexp_replace(val_juros, '[^0-9]', ''),-5,5)
|| '00000N'
|| to_char(coalesce(dt_fim, date '1900-01-01'), 'YYYYMMDD')
|| 'A A00000'
|| ' SEM IPOC'
|| 'A'
|| to_char(coalesce(empj.dt_ult_pgto, date '1900-01-01'), 'YYYYMMDD')
|| to_char(coalesce(empj.data, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(empj.valor_original, '[^0-9]', ''),-14,14)
|| 'N' -- Remetido ao juridico
|| '00000000' --data do envio a juridico
|| '00000000000000000000000000000000' -- numero bordero
|| '000000000' -- numero do contrato renegociado
|| '0000' --cod da linha renegociada
|| '00000000' -- DAta da renegociacao
|| '00000000000000' -- cnpj????
|| '0000'
from cc_chesp cc
join e_sda_baixas empj on empj.anomes = '2024/07' and empj.contrato = cc.contac
join cc_cad cc2 on cc2.contac = cc.contac;
--inner join e_lf lc on lc.cod_lf = emp.cod_lf
--EMP 02 PRICE APENAS
select 'EMP02'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
|| substr(lpad('0',4,'0')||regexp_replace(parcela, '[^0-9]', ''),-4,4)
|| to_char(coalesce(data_original, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(prestacao_original*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',14,'0')||regexp_replace((valor_pgto)*100, '[^0-9]', ''),-14,14) --tem q ver com o paulo se tem pagamento parcial.
|| 'A' -- situacao da parcela, confirmar
|| substr(lpad('0',7,'0')||regexp_replace(0*100, '[^0-9]', ''),-7,7) --Percentual saldo devedor
|| substr(lpad('0',14,'0')||regexp_replace(jncap*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Juros da correcao da parcela
|| to_char(coalesce(data_ant, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Valor Rendas a apropriar
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Valor do seguro
from e_parcelas_open emp
inner join e_ctopen emp2 on emp.contrato = emp2.contrato
inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(emp2.contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato2,
sum(saldo) as total
from e_ctopen emp2
group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo
on nsaldo.contrato2 = substr(lpad('0',10,'0')|| regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
--where substr(emp.contrato,-3,3) <> '000' and gerada = 'T' and nsaldo.total > 0
where substr(emp.contrato,-3,3) <> '000' and nsaldo.total > 0 and emp.prestacao_original > 0
--and emp2.valorlib <> 0
AND emp2.saldo > 0
and emp2.cod_lf not in (10,15,17,18,20,29,31,32,42,44,45,46,47,48,49,52,55)
--and emp2.contrato like '%181501%'
union all
select 'EMP02'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
|| substr(lpad('0',4,'0')||regexp_replace(parcela, '[^0-9]', ''),-4,4)
|| to_char(coalesce(data_original, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(emp.prestacao*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',14,'0')||regexp_replace((amortizacao_pg + jncap_pg + jcap_pg)*100, '[^0-9]', ''),-14,14) --tem q ver com o paulo se tem pagamento parcial.
|| 'A' -- situacao da parcela, confirmar
|| substr(lpad('0',7,'0')||regexp_replace(0*100, '[^0-9]', ''),-7,7) --Percentual saldo devedor
|| substr(lpad('0',14,'0')||regexp_replace(jncap*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Juros da correcao da parcela
|| to_char(coalesce(data_ant, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Valor Rendas a apropriar
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Valor do seguro
from e_parcelas_open emp
join (select distinct substr(c.contrato,0,8) contrato
from e_ctopen c
where cod_lf in (10,15,17,18,20,29,31,32,42,44,45,46,47,48,49,52,55)
) b on b.contrato = substr(emp.contrato,0,8)
left join (select substr(contrato,0,8) contrato, parc_ger from e_ctopen) n
on substr(emp.contrato,0,8) = n.contrato and emp.parcela = parc_ger
where n.contrato is null and parc_ger is null
and emp.prestacao_original > 0
order by emp.contrato, parcela;
--EMP03
select 'EMP03'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
|| substr(lpad('0',14,'0')||regexp_replace(aval.cpfcgc, '[^0-9]', ''),-14,14) -- Tratar pontos e virgulas
from e_laval aval
join e_ctopen emp on emp.contrato = aval.contrato
inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato,
sum(saldo) as total
from e_ctopen emp
group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo
on nsaldo.contrato = substr(lpad('0',10,'0')||
regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10)
where substr(emp.contrato,-3,3) = '000' and refaut = 'F' order by emp.contrato;
select '999990000000001' from c_cad fetch first 1 rows only;
spool off;
/* TITULOS EM PREJUIZO DIRETO DO 3040 */
spool C:\Users\Administrator\Documents\Credicentro\emp_prejuizo.txt;
set pagesize 32000
SET ARRAYSIZE 1
SET HEADING OFF
set echo off
set verify off
set feedback off
set headings off
Set Newpage none
select * from B_DOC3040 where dtbase = '2024/08' and idx = '11' and classop = 'HH'
select * from cc_cad
select * from cc_cadassoc
select 'EMP01'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)), '[^0-9]', ''),-10,10) -- Retirar hifen
|| substr(lpad('0',11,'0')||regexp_replace(coalesce(cc.cod_integra, cc.contac), '[^0-9X]', ''),-11,11)
|| substr(lpad('0',4,'0')||regexp_replace('0001', '[^0-9]', ''),-4,4)
|| to_char(coalesce(dtcontrt, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(vlrcontrt*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace('1', '[^0-9]', ''),-4,4)
|| to_char(coalesce(dtvencop, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(vlrcontrt*100, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace('1', '[^0-9]', ''),-4,4)
|| '00000'
|| substr(lpad('0',5,'0')||regexp_replace('0', '[^0-9]', ''),-5,5)
|| '00000' -- Juros de mora
|| case 'N' when 'T' then 'S' else 'N' end-- Tratar Verdadeiro ou falso
|| to_char(coalesce(dtvencop, date '1900-01-01'), 'YYYYMMDD')
|| 'A'
|| '9'
|| '00000' --multa por atraso
|| substr(lpad('0',34,'0')||regexp_replace(ipoc, '[^0-9]', ''),-34,34)--IPOC
|| 'P' -- Situacao da operacao
|| '20230228' --data da ultima movimentacao
|| '00000000' --Data do prejuizo
|| substr(lpad('0',14,'0')||regexp_replace(vlrcontrt*100, '[^0-9]', ''),-14,14)
|| 'N' -- Remetido ao juridico
|| '00000000' --data do envio a juridico
|| '00000000000000000000000000000000' -- numero bordero
|| '000000000' -- numero do contrato renegociado
|| '0000' --cod da linha renegociada
|| '00000000' -- DAta da renegociacao
|| substr(lpad('0',14,'0')||regexp_replace(c.conta, '[^0-9]', ''),-14,14)
|| substr(lpad('0',4,'0')||regexp_replace('0000', '[^0-9]', ''),-4,4)
as rest from B_DOC3040 emp
inner join c_cad c on c.cpf = emp.conta
inner join cc_cadassoc ca on ca.conta = c.conta
inner join cc_cad cc on cc.contac = ca.contac
where dtbase = '2024/08' and idx = '11' and classop = 'HH'
select 'EMP02'
|| substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)), '[^0-9]', ''),-10,10)
|| '0001'
|| to_char(coalesce(dtcontrt, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(vlrcontrt*100, '[^0-9]', ''),-14,14)
|| '00000000000000' --tem q ver com o paulo se tem pagamento parcial.
|| 'I' -- situacao da parcela, confirmar
|| substr(lpad('0',7,'0')||regexp_replace(0*100, '[^0-9]', ''),-7,7) --Percentual saldo devedor
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Juros da Parcela
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Juros da correcao da parcela
|| to_char(coalesce(dtcontrt, date '1900-01-01'), 'YYYYMMDD')
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) -- Valor Rendas a apropriar
|| substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Valor do seguro
from B_DOC3040 emp
where dtbase = '2024/08' and idx = '11' and classop = 'HH';
spool off;
brunosiena
spool /u01/userhome/oracle/Documents/cad.txt set pagesize 32000 SET ARRAYSIZE 1
SET HEADING OFF
set echo off set verify off set feedback off set headings off Set Newpage none
--update coopesf.c_cad set email = 'jean _cb@brturbo.com.br' where cpf = '040.028.879-60';
select '00000CADv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from coopesf.c_cad fetch first 1 rows only;
select 'CAD01' || replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || '0000' --isenta_iof sempre F... não vou implementar no case abaixo... || case fisica when 'F' then '01' else '03' end --'classificacao da pessoa, tipo fisica, fisica imune....' || fisica || substr(nome||rpad(' ',80,' '),1,80) || substr(apelido||rpad(' ',26,' '),1,26)--rpad(' ',26,' ') || substr(email||rpad(' ',150,' '),1,150) || to_char(dt_nivel,'YYYYMMDD') -- data da ultima analise cadastral || case serasa when 'F' then 'N' else 'S' end || 'O'--tipo do imovel?!!?!?!?!?! || '000000000' --custo mensal do imovel || '0000' --tempo de residencia no imovel || nivel --cast(1 as varchar) --risco do cooperado --antigo nr_original || case enquadra_pep when 'F' then '0' else '1' end || '0' -- US Person || '0' -- nao eh produtor rural as "output_file" from coopesf.c_cad where 1 = case when fisica = 'J' and cgc = '00.000.000/0000-00' then 0 else 1 end;
select 'CAD02' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || '0000' --posto sede || case c.est_civil when 'Divorciada' then 'D' when 'Solteira' then 'S' when 'Solteiro' then 'S' when 'Casado' then 'C' when 'SOLTEIRO' then 'S' when 'Viúva' then 'V' when 'Casada' then 'C' when 'União Estável' then 'U' when 'Desquitado' then 'O' when 'Divorciado' then 'D' when 'Viúvo' then 'V' else 'O' end || case when lower(c.est_civil) like 'solteir%' then 'N' else 'C' end || c.sexo --subestimei os manos nesse campo aqui ein?!?!?! desgurpe || '00' -- não foi encontrado o numero de dependentes || to_char(c.nascimento,'YYYYMMDD') || substr(coalesce(c.naturalidade,'CURITIBA')||rpad(' ',20,' '),1,20) || case c.nacionalidade when 'Brasileira' then 'BR' when 'BRASILEIRA' then 'BR' when 'Brasileiro' then 'BR' when 'PERUANA' then 'PE' when 'VENEZUELA' then 'VE' else 'BR' end --mds... mmmmmeeeeeeeeeeeuuuuu deus.... ajuda || substr(lpad('0',14,'0')||regexp_replace(cu.salario100, '[^0-9]', ''),-14,14) || substr(lpad('0',15,'0')||regexp_replace(c.ci, '[^0-9]', ''),-15,15) || substr(c.ORG_EXP||rpad(' ',10,' '),1,10) || to_char(coalesce(c.DT_CI,DATE '1900-01-01'),'YYYYMMDD') || substr(cu.profissao||rpad(' ',15,' '),1,15) --FALTA: 'REG_PROFISSIONAL' || '14' --FALTA: Orgao profissional || 'PR' --FALTA: UF do orgao/ || substr(c.PAI||rpad(' ',65,' '),1,65) || substr(c.MAE||rpad(' ',65,' '),1,65) || '003646'--substr(cu.profissao||rpad(' ',6,' '),1,6) --FALTA: CBO da profissao || substr(c.conjugue||rpad(' ',65,' '),1,65) || to_char(coalesce(c.DT_NASC_CONJUGE,DATE '1900-01-01'),'YYYYMMDD') || substr(c.PROFCONJUGE||rpad(' ',6,' '),1,6) --FALTA: CBO do conjuge --PROFISSAO_CONJUGE2 || substr(replace(replace(c.CPFCONJUGE,'.',''),'-','')||rpad(' ',11,' '),1,11) || substr(c.CICONJUGE||rpad(' ',12,' '),1,12) || to_char(coalesce(c.DT_CICONJUGE, DATE '1900-01-01'),'YYYYMMDD') || substr(c.ORG_EXPCONJUGE||rpad(' ',10,' '),1,10) || rpad(' ',30,' ')--substr(c.PROFISSAO_CONJUGE2||rpad(' ',30,' '),1,30) || substr(c.MAE_CONJUGE||rpad(' ',65,' '),1,65) || substr(c.PAI_CONJUGE||rpad(' ',65,' '),1,65) || substr(c.EMAIL_CONJUGE||rpad(' ',60,' '),1,60) || substr(lpad('0',14,'0')||regexp_replace(c.RENDACONJUGE*100, '[^0-9]', ''),-14,14) || substr(lpad('0',2,'0')||coalesce(grau,0),-2,2) -- precisa ajustar o grau || lpad('0',9,'0') --matricula vinculada, não encontramos nada ainda || 'O' --tipo de vinculo || lpad('0',14,'0') -- pensao... tudo nulo na tabela || '000' -- mesma coisa pra quantidade de meses || coalesce(ci.uf,'PR') --mesma gambiarra da naturalidade || substr(lpad('0',2,'0')|| c.UFORG,-2,2) --lembrando que é obrigatorio || to_char(coalesce(cu.DT_SAL, DATE '1900-01-01'),'MMYYYY') || '1' -- só tem rg aqui... || c.porte -- precisa validar as faixas || '0' -- nao tem representante, nao tem modalidade || lpad('0',14,'0') -- ta vindo o patrimonio || upper(cu.profissao) as "output_file" from coopesf.c_cad c left join coopesf.c_caduni cu on c.conta = cu.conta left join coopesf.c_cidade ci on upper(coalesce(c.naturalidade,'CURITIBA')) = upper(c.nome) where fisica = 'F' order by c.conta;
select 'CAD03' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || '0000' --posto sede || tipo_inst || '2062' || to_char(coalesce(c.NASCIMENTO,DATE '1900-01-01'),'YYYYMMDD') || rpad(' ',15,' ') || '00000000' || rpad(' ',20,' ') || '00000000' || substr(c.INSC_ESTADUAL||rpad(' ',9,' '),1,9) || rpad(' ',9,' ') || substr(lpad('0',14,'0')||replace(to_char(coalesce(c.vrfaturamento,0)),'.',''),-14,14) || '000'|| to_char(case porte when 0 then 1 else porte end) || '0001' --tipo controle || rpad(' ',7,' ') || '000000' --ano mes faturamento || '0000' || ' ' || '0000' || 'S' || '000000000' || ' ' || ' ' || ' ' as "output_file" from coopesf.c_cad c left join coopesf.c_caduni cu on c.conta = cu.conta left join coopesf.c_cidade ci on upper(coalesce(c.naturalidade,'CURITIBA')) = upper(c.nome) where fisica = 'J' and cgc <> '00.000.000/0000-00' order by c.conta;
select 'CAD04' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || 'R' || substr(coalesce(endok.endereco,c.endereco) || rpad(' ',60,' '),1,60) || substr(lpad('0',10,'0')||replace(to_char(coalesce(endok.end_num,c.end_num,0)),'.',''),-10,10) || substr(coalesce(endok.compl_end,c.compl_end) || rpad(' ',30,' '),1,30) --|| rpad(' ',30,' ')
|| substr(b.nome||rpad(' ',30,' '),1,30) || substr(replace(c.cep,'-','')||lpad('0',8,'0'),1,8) || ci.cod_municipio_ibge || rpad(' ',10,' ') || 'S' as "output_file" from coopesf.c_cad c left join (select conta, max(anomes) ano_mes from coopesf.c_sda group by conta) endatu on endatu.conta = c.conta left join coopesf.c_sda endok on endok.conta = endatu.conta and endok.anomes = endatu.ano_mes left join coopesf.c_caduni cu on c.conta = cu.conta left join coopesf.c_cidade ci on c.cod_cid = ci.cod_cid left join coopesf.c_bairro b on c.cod_bai = b.cod_bai where 1 = case when c.fisica = 'F' then 1 else case when c.cgc is not null or c.cgc <> '00.000.000/0000-00' then 1 else 0 end end order by c.conta;
select 'CAD05' || replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || '1' || '0' || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),1,2) || substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) as "output_file" from coopesf.c_cad c where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel1 is null or c.tel1 in ('','( ) - ') then '000000000000' else c.tel1 end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000' and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05' || replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || '2' || '0' || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),1,2) || substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) as "output_file" from coopesf.c_cad c where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_comerc is null or c.tel_comerc in ('','( ) - ') then '000000000000' else c.tel_comerc end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000' and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05' || replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || '3' || '0' || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),1,2) || substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) as "output_file" from coopesf.c_cad c where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_cel is null or c.tel_cel in ('','( ) - ') then '000000000000' else c.tel_cel end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000' and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end
union all
select 'CAD05' || replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || '3' || '0' || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),1,2) || substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) as "output_file" from coopesf.c_cad c where substr(lpad('0',10,'0') || substr(replace(replace(replace(replace(case when c.tel_whatsapp is null or c.tel_whatsapp in ('','( ) - ') then '000000000000' else c.tel_whatsapp end,'(',''),')',''),'-',''),' ',''),3,10),-10,10) <> '0000000000' and 1 = case when fisica = 'F' then 1 else case when cgc is not null or cgc <> '00.000.000/0000-00' then 1 else 0 end end;
select 'CAD10' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || '0000' || substr(s.nome||rpad(' ',65,' '),1,65) || substr(lpad('0',12,'0')||regexp_replace(s.ci, '[^0-9]', ''),-12,12) || s.fisica--case s.cpf when null then 'J' else 'F' end || substr(lpad('0',14,'0')||regexp_replace(coalesce(s.cpf, s.cnpj), '[^0-9]', ''),-14,14) || substr(lpad('0',5,'0')||regexp_replace(s.perc_rateio*100, '[^0-9]', ''),-5,5) || '2135' --anexo 4? || to_char(coalesce(s.nascimento,DATE '1900-01-01'),'YYYYMMDD') || '00000' || '0000000000' || substr(s.email||rpad(' ',50,' '),1,50) || '1' as "output_file" from coopesf.c_cad c join coopesf.c_socio s on s.conta = c.conta ;
select 'CAD11' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || '0000' || case a.situacao when 'Alienado' then 'A' else 'L' end || substr(a.modelo||rpad(' ',150,' '),1,150) || substr(a.modelo||rpad(' ',150,' '),1,150) || coalesce(to_char(a.ano_fab),'1900') || substr(lpad('0',14,'0')||regexp_replace(a.valor*100, '[^0-9]', ''),-14,14) || substr(coalesce(a.num_chassi,'')||rpad(' ',20,' '),1,20) || substr(lpad('0',15,'0')||coalesce(a.renavan,''),1,15) || coalesce(to_char(a.ano_mod),'1900') --|| substr(a.num_placa||rpad(' ',8,' '),1,8) as "output_file" from coopesf.cm_auto a join coopesf.c_cad c on a.conta = c.conta ;
select 'CAD12' || replace(replace(replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end,'.',''),'/',''),'-','') || '0000' || case a.tipo when 'Livre' then 'L' when 'Financiado' then 'F' when 'Alienado' then 'H' else 'L' end || case lower(a.denominacao) when 'casa' then '1' when 'outros' then '7' when 'terreno' then '3' when 'SOBRADO' then '1' when 'apartamento' then '2' else '7' end || substr(lpad('0',9,'0')||regexp_replace(a.area100, '[^0-9]', ''),-9,9) || substr(a.endereco||rpad(' ',60,' '),1,60) || substr(lpad('0',14,'0')||regexp_replace(a.valor100, '[^0-9]', ''),-14,14) || substr(a.reg_mat||rpad(' ',10,' '),1,10) || substr(lpad('0',9,'0')||regexp_replace(a.area*100, '[^0-9]', ''),-9,9) || substr(a.reg_mat||rpad(' ',15,' '),1,15) || substr('cri_livro:'||coalesce(a.cri_livro,'null')||'. onus_esp: '||coalesce(a.onus_esp,'null')||'. declarado: '||a.declarado||rpad(' ',250,' '),1,250) || d.cod_municipio_ibge as "output_file" from coopesf.cr_imou a join coopesf.c_cad c on a.conta = c.conta join coopesf.c_cidade d on d.cod_cid = a.cod_cid ;
select '999990000000001' from coopesf.c_cad fetch first 1 rows only;
spool off;
spool /u01/userhome/oracle/Documents/cap.txt set pagesize 32000 SET ARRAYSIZE 1 SET HEADING OFF set echo off set verify off set feedback off set headings off Set Newpage none select '00000CAPv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from coopesf.c_cad fetch first 1 rows only;
select 'CAP01' --|| replace(replace(replace( case fisica when 'F' then '000'|| cpf else cgc end,'.',''),'/',''),'-','') || substr(lpad('0',9,'0')||regexp_replace(c.conta, '[^0-9]', ''),-9,9) || '0000' || substr(lpad('0',14,'0')||regexp_replace(case c2.fisica when 'F' then c2.cpf else c2.cgc end, '[^0-9]', ''),-14,14) || '000000000' || '000000001' || '0001' || substr(lpad('0',14,'0')||regexp_replace(sc.sd*100, '[^0-9]', ''),-14,14) || '00000000000000' || 'A'--c.status || to_char(c.data_inc,'YYYYMMDD') || '0000000000000000' as "output_file" from COOPESF.cc_cadassoc c join coopesf.c_cad c2 on c.conta = c2.conta join (select conta, max(anomes) ult_data from coopesf.a_sda group by conta) ud on ud.conta = c2.conta join coopesf.a_sda sc on sc.conta = ud.conta and sc.anomes = ud.ult_data order by c2.conta;
select 'CAP04' || substr(lpad('0',9,'0')||regexp_replace(c.conta, '[^0-9]', ''),-9,9) || regexp_replace(sc.anomes, '[^0-9]', '') || substr(lpad('0',14,'0')||regexp_replace(sc.sd*100, '[^0-9]', ''),-14,14) as "output_file" from COOPESF.cc_cadassoc c join coopesf.c_cad c2 on c.conta = c2.conta join coopesf.a_sda sc on sc.conta = c.conta order by c2.conta;
select '999990000000001' from coopesf.c_cad fetch first 1 rows only; spool off;
spool /u01/userhome/oracle/Documents/cco.txt set pagesize 32000 SET ARRAYSIZE 1 SET HEADING OFF set echo off set verify off set feedback off set headings off Set Newpage none
select '00000CCOv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from coopesf.c_cad fetch first 1 rows only;
select 'CCO01' || regexp_replace( case c.fisica when 'F' then '000'|| c.cpf else c.cgc end, '[^0-9]', '') || '0000' || substr(lpad('0',11,'0')||regexp_replace(c.conta, '[^0-9]', ''),-11,11) || '1' || to_char(coalesce(cc.dt_abert, date '1900-01-01'), 'YYYYMMDD') || '1' -- Confirmar Modalidades de contas || to_char(cc.ativo) --Confirmar situacaoes de conta || '0'--quantidade de taloes em estoque || '19000101' || to_char(coalesce(e2.dt_fim, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(e2.lim_chesp100, '[^0-9]', ''),-14,14) || substr(rpad('0',10,'0')||regexp_replace(e2.id_chesp, '[^0-9]', ''),-10,10) || substr(lpad('0',04,'0')||regexp_replace(round((power(1+(e2.val_juros/100),12)-1)100,2), '[^0-9]', ''),-04,04) || 'N' -- Renovacao automatica do cheque especial || to_char(coalesce(cc.ult_lanc_cheq, date '1900-01-01'), 'YYYYMMDD') --|| substr(lpad('0',14,'0')||regexp_replace(sal.saldo_ad100, '[^0-9]', ''),-14,14) || '00000000000000' || '19000101' --data de utilizacao do cheque especial nao encontrada || coalesce(substr(regexp_replace(cliente_desde, '[^0-9]', ''),3,4)||substr(regexp_replace(cliente_desde, '[^0-9]', ''),1,2),'190001')||'01' --mds mano... || substr(lpad('0', 4,'0')||regexp_replace(round((power(1+(e2.CET/100),12)-1)100,2), '[^0-9]', ''),-04,04) || 'N'-- Tarifa cheque especial nao encontrada || 'N' --conta compe propra, pedir identificacao ao Paulo -- || '00000000000000' -- || '00000000000000' || substr(lpad('0',14,'0')||regexp_replace(sal.saldo100, '[^0-9]', ''),-14,14) || substr(lpad('0',14,'0')||regexp_replace(sal.saldo_bloq100, '[^0-9]', ''),-14,14)
as "output_file" from coopesf.c_cad c join (select conta, max(data_inc) dt_inicio from coopesf.cc_cadassoc where titular = 'T' group by conta) um on um.conta = c.conta join coopesf.cc_cadassoc a on a.titular = 'T' and a.conta = c.conta and um.dt_inicio = a.data_inc join coopesf.cc_cad cc on cc.contac = a.contac left join (select contac, max(dt_inicio) dt_ini from coopesf.cc_chesp group by contac) e on a.contac = e.contac left join coopesf.cc_chesp e2 on e2.contac = e.contac and e2.dt_inicio = e.dt_ini --left join coopesf.cc_chesp esp on esp.contac = cc.contac left join coopesf.cc_saldo sal on sal.contac = cc.contac and sal.data in (select max(data) from coopesf.cc_saldo) --where substr(lpad('0',9,'0')||regexp_replace(c.contac, '[^0-9]', ''),-9,9) = '183590011' order by c.conta;
select 'CCO02' || substr(lpad('0',11,'0')||regexp_replace(contac, '[^0-9]', ''),-11,11) || to_char(coalesce(dt_emissao, date '1900-01-01'), 'YYYYMMDD') || substr(rpad('0',5,'0')||regexp_replace(cod_talao, '[^0-9]', ''),-5,5) || substr(rpad('0',6,'0')||regexp_replace(num_cheque, '[^0-9]', ''),-6,6) || substr(rpad('0',6,'0')||regexp_replace(num_cheque + num_folhas, '[^0-9]', ''),-6,6) || substr('00'||regexp_replace(num_folhas, '[^0-9]', ''),-2,2) || case cod_situacao when 4 then '5' when 2 then '4' when 5 then '4' when 8 then '5' else '6' end --as 'sittalao' --nome da tabela de situacao talao || substr(rpad('0',3,'0')||regexp_replace(serie, '[^0-9]', ''),-3,3) from cc_talao;
select 'CCO03' || substr(lpad('0',11,'0')||regexp_replace(tl.contac, '[^0-9]', ''),-11,11) || substr(rpad('0',5,'0')||regexp_replace(ch.cod_talao, '[^0-9]', ''),-5,5) || substr(rpad('0',3,'0')||regexp_replace(tl.serie, '[^0-9]', ''),-3,3) || substr(rpad('0',6,'0')||regexp_replace(ch.num_cheque, '[^0-9]', ''),-6,6) || 'B' || substr('00'||regexp_replace(prox_motdev, '[^0-9]', ''),-2,2) || '00' || substr(rpad('0',14,'0')||regexp_replace(ch.valor, '[^0-9]', ''),-14,14) || to_char(coalesce(ch.dt_atualizado, date '1900-01-01'), 'YYYYMMDD') from cc_cheque ch inner join cc_talao tl on tl.cod_talao = ch.cod_talao;
select 'CCO05' || '0000' || substr(lpad('0',11,'0')||regexp_replace(c.contac, '[^0-9]', ''),-11,11) || substr(lpad('0',9,'0')||regexp_replace(c.cod_integra, '[^0-9]', ''),-9,9) || '1' || '00001' || 'F' || '1' from cc_cad c where c.cod_integra > '0' order by c.contac;
select '999990000000001' from coopesf.c_cad fetch first 1 rows only;
spool off;
spool /u01/userhome/oracle/Documents/apl.txt; set pagesize 32000 SET ARRAYSIZE 1 SET HEADING OFF set echo off set verify off set feedback off set headings off Set Newpage none
select '00000APLv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'01' from coopesf.c_cad fetch first 1 rows only;
select 'APL01' || substr(lpad('0',9,'0')||regexp_replace(conta, '[^0-9]', ''),-9,9) || substr(lpad('0',4,'0')|| case cod_la when 16 then 1 when 20 then 2 when 21 then 3 when 23 then 4 when 24 then 5 when 25 then 6 when 26 then 7 when 32 then 8 when 33 then 9 when 36 then 10 when 37 then 11 when 38 then 12 when 39 then 13 else 99 end,-4,4) || substr(lpad('0',11,'0')||regexp_replace(contrato, '[^0-9]', ''),-11,11) --CAMPO e string e tem hifen no caralho ta coluna || substr(lpad('0',14,'0')||regexp_replace(valor100, '[^0-9]', ''),-14,14) || to_char(coalesce(dt_emissao, date '1900-01-01'), 'YYYYMMDD') || to_char(coalesce(dt_resgate, date '1900-01-01'), 'YYYYMMDD') || '00000000'--data do vencimento da carencia - confirmar || substr(lpad('0',5,'0')||regexp_replace(round(coalesce(fator,case cod_tbtaxa when 27 then 150 when 28 then 180 when 30 then 70 else 100 end),2)100, '[^0-9]', ''),-5,5)--Percentual do indicador || substr(lpad('0',20,'0')||regexp_replace(coalesce(ap.id_cetip,''), '[^0-9]', ''),-20,20) || substr(lpad('0',18,'0')||regexp_replace(coalesce(valor_pu100,0), '[^0-9]', ''),-18,18) || '00000000'--data da ultimo movimentacao, aparentemente nao tem || substr(lpad('0',14,'0')||regexp_replace(saldo100, '[^0-9]', ''),-14,14) -- confirmar se e esse valor que vai aqui || substr(lpad('0',14,'0')||regexp_replace(ctb_correcao, '[^0-9]', ''),-14,14) -- confirmar se valor esta correto || '000000'--confirmando onde esta a configuracao com a cooperativa || '00000000'-- data da ultima capitalização confirmar from coopesf.f_sda ap left join coopesf.f_taxa tx on tx.cod_taxa = ap.cod_tbtaxa where anomes = (select max (anomes) from COOPESF.f_sda) order by ap.contrato;
select '999990000000001' from coopesf.c_cad fetch first 1 rows only; spool off;
spool C:\output\emp.txt; set pagesize 32000 SET ARRAYSIZE 1 SET HEADING OFF set echo off set verify off set feedback off set headings off Set Newpage none
select '00000EMPv10605097757000169' || to_char(dt_nivel,'YYYYMMDD') ||'02' from c_cad fetch first 1 rows only; select 'EMP01' || substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) -- Retirar hifen || substr(lpad('0',9,'0')||regexp_replace(emp.conta, '[^0-9]', ''),-11,11) || substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4) -- Confirmar se esse e o codigo da linha de credito mesmo || to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(valor100, '[^0-9]', ''),-14,14) || substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4) || to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(valor100, '[^0-9]', ''),-14,14) || substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4) || '00000' || substr(lpad('0',5,'0')||regexp_replace(tx.jncap, '[^0-9]', ''),-5,5) || '00000' -- Juros de mora || case seg_prestamista when 'T' then 'S' else 'N' end-- Tratar Verdadeiro ou falso || to_char(coalesce(venc_fim, date '1900-01-01'), 'YYYYMMDD') || 'A' || nv_atual || '00000' --multa por atraso || substr(lpad('0',34,'0')||regexp_replace(ipoc, '[^0-9]', ''),-34,34)--IPOC || 'A' -- Situacao da operacao || '20230228' --data da ultima movimentacao || '00000000' --Data do prejuizo || substr(lpad('0',14,'0')||regexp_replace(nsaldo.total100, '[^0-9]', ''),-14,14) || 'N' -- Remetido ao juridico || '00000000' --data do envio a juridico || '00000000000000000000000000000000' -- numero bordero || '000000000' -- numero do contrato renegociado || '0000' --cod da linha renegociada || '00000000' -- DAta da renegociacao || substr(lpad('0',14,'0')||regexp_replace(cad.cgc, '[^0-9]', ''),-14,14) as rest from e_ctopen emp inner join e_taxa tx on tx.cod_taxa = emp.cod_taxa inner join c_cad cad on cad.conta = emp.conta inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato, sum(saldo) as total from e_ctopen emp group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo on nsaldo.contrato = substr(lpad('0',10,'0')|| regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) where substr(emp.contrato,-3,3) = '000' and refaut = 'F' and nsaldo.total> 0 union all select 'EMP01' || substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) -- Retirar hifen || substr(lpad('0',9,'0')||regexp_replace(emp.conta, '[^0-9]', ''),-11,11) || substr(lpad('0',4,'0')||regexp_replace(emp.cod_lf, '[^0-9]', ''),-4,4) || to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(valor100, '[^0-9]', ''),-14,14) || substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4) || to_char(coalesce(venc_ini, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(valor100, '[^0-9]', ''),-14,14) || substr(lpad('0',4,'0')||regexp_replace(num_parc, '[^0-9]', ''),-4,4) || '00000' || substr(lpad('0',5,'0')||regexp_replace(tx.jncap, '[^0-9]', ''),-5,5) || '00000' -- Juros de mora || case seg_prestamista when 'T' then 'S' else 'N' end-- Tratar Verdadeiro ou falso || to_char(coalesce(venc_fim, date '1900-01-01'), 'YYYYMMDD') || 'A' || nv_atual || '00000' --multa por atraso || substr(lpad('0',34,'0')||regexp_replace(ipoc, '[^0-9]', ''),-34,34)--IPOC || 'A' -- Situacao da operacao || '20230228' --data da ultima movimentacao || '00000000' --Data do prejuizo || substr(lpad('0',14,'0')||regexp_replace(nsaldo.total100, '[^0-9]', ''),-14,14) || 'N' -- Remetido ao juridico || '00000000' --data do envio a juridico || '00000000000000000000000000000000' -- numero bordero || '000000000' -- numero do contrato renegociado || '0000' --cod da linha renegociada || '00000000' -- DAta da renegociacao || substr(lpad('0',14,'0')||regexp_replace(cad.cgc, '[^0-9]', ''),-14,14) as rest from e_ctclos emp inner join e_taxa tx on tx.cod_taxa = emp.cod_taxa inner join c_cad cad on cad.conta = emp.conta inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato, sum(saldo) as total from e_ctclos emp group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo on nsaldo.contrato = substr(lpad('0',10,'0')|| regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) where substr(emp.contrato,-3,3) = '000' and refaut = 'F' and nsaldo.total> 0; --inner join e_lf lc on lc.cod_lf = emp.cod_lf
--EMP 02 select 'EMP02' || substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) || substr(lpad('0',4,'0')||regexp_replace(parcela, '[^0-9]', ''),-4,4) || to_char(coalesce(data_original, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(prestacao_original100, '[^0-9]', ''),-14,14) || substr(lpad('0',14,'0')||regexp_replace(amortizacao_original100, '[^0-9]', ''),-14,14) --tem q ver com o paulo se tem pagamento parcial. || 'A' -- situacao da parcela, confirmar || substr(lpad('0',7,'0')||regexp_replace(0100, '[^0-9]', ''),-7,7) --Percentual saldo devedor || substr(lpad('0',14,'0')||regexp_replace(jncap100, '[^0-9]', ''),-14,14) -- Juros da Parcela || substr(lpad('0',14,'0')||regexp_replace(0100, '[^0-9]', ''),-14,14) --Juros da correcao da parcela || to_char(coalesce(data_ant, date '1900-01-01'), 'YYYYMMDD') || substr(lpad('0',14,'0')||regexp_replace(0100, '[^0-9]', ''),-14,14) -- Valor Rendas a apropriar || substr(lpad('0',14,'0')||regexp_replace(0*100, '[^0-9]', ''),-14,14) --Valor do seguro from e_parcelas_open emp inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(emp2.contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato2, sum(saldo) as total from e_ctopen emp2 group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo on nsaldo.contrato2 = substr(lpad('0',10,'0')|| regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) where substr(emp.contrato,-3,3) <> '000' and gerada = 'T' and nsaldo.total > 0 order by emp.contrato, parcela;
--EMP03 select 'EMP03' || substr(lpad('0',10,'0')||regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) || substr(lpad('0',14,'0')||regexp_replace(aval.cpfcgc, '[^0-9]', ''),-14,14) -- Tratar pontos e virgulas from e_laval aval join e_ctopen emp on emp.contrato = aval.contrato inner join (select substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10) as contrato, sum(saldo) as total from e_ctopen emp group by substr(lpad('0',10,'0')||regexp_replace(substr(contrato,0,length(contrato)-3), '[^0-9]', ''),-10,10)) nsaldo on nsaldo.contrato = substr(lpad('0',10,'0')|| regexp_replace(substr(emp.contrato,0,length(emp.contrato)-3), '[^0-9]', ''),-10,10) where substr(emp.contrato,-3,3) = '000' and refaut = 'F' --and emp.contrato like '%00182126%' and nsaldo.total > 0
select '999990000000001' from c_cad fetch first 1 rows only; spool off;