Hello @vinieme12 , thank you for your help.
We are testing other ways of writing the script.
When we load the two tables, [ExtratoDetalhado] and [TITULARES] without creating the field "ID_CBAC", the two tables are loaded with all available rows. When we create the ID_CBAC field, the loaded lines are limited to the common data in the "ID_CBAC" field.
What we are currently using, and which is giving the same problem is this:
Table [ExtratoDetalhado] (full script):
ExtratoDetalhadoBruto:
LOAD
RowNo() as Linha,
num(Left(FileName(),6), '000000') as caso,
NUMERO_CASO as NrCaso,
if(len(NUMERO_BANCO)=0,0,PurgeChar(NUMERO_BANCO,' /-.,')) as Banco,
if(len(NUMERO_AGENCIA)=0,0,PurgeChar(NUMERO_AGENCIA,' /-.,')) as Agencia,
if(len(NUMERO_CONTA)=0,0,PurgeChar(NUMERO_CONTA,' /-.,')) as Conta,
upper(NOME_BANCO) as Nomebanco,
TIPO as TipoContaNum,
if (TIPO=1,'C. Corrente',
if (TIPO=2,'C. Poupança',
if (TIPO=3,'C. Investimento',
if (TIPO=4,'Outros'))))
as TipoConta,
upper(NOME_TITULAR) as Investigado,
Num (PurgeChar(CPF_CNPJ_TITULAR,' /-.'), '00000000000') as CPFCNPJInvestigado,
CNAB,
PurgeChar(DESCRICAO_LANCAMENTO,'/-.,') as Operacao,
DATA_LANCAMENTO as Data,
NUMERO_DOCUMENTO as NrDoc,
NUMERO_DOCUMENTO_TRANSACAO as NrDocTransacao,
if(len(LOCAL_TRANSACAO)>0,LOCAL_TRANSACAO,'Não Identificado') as LocalTransacao,
VALOR_TRANSACAO as Valor,
NATUREZA_LANCAMENTO as Tipolancamento,
// insere zero nos CPFs em branco ou 999999999
if(len(CPF_CNPJ_OD)<=1 or CPF_CNPJ_OD='99999999999999' or CPF_CNPJ_OD='0' or len(CPF_CNPJ_OD)=0,'NÃO IDENTIFICADO', num(PurgeChar(CPF_CNPJ_OD,' /-.'),'00000000000' )) as CPFCNPJOD,
if(len(CPF_CNPJ_OD)<=1 or CPF_CNPJ_OD='99999999999999' or CPF_CNPJ_OD='0' or len(CPF_CNPJ_OD)=0,'NÃO IDENTIFICADO', Text(num(PurgeChar(CPF_CNPJ_OD,' /-.'),'00000000000'))) as CPFOD_txt,
if(len(CPF_CNPJ_OD)<=1 or CPF_CNPJ_OD='99999999999999' or CPF_CNPJ_OD='0' or len(CPF_CNPJ_OD)=0,'NÃO IDENTIFICADO', Text(num(PurgeChar(CPF_CNPJ_OD,' /-.'),'00000000000000'))) as CNPJOD_txt,
// insere zeros
if(len(NUMERO_BANCO_OD)=0,0,PurgeChar(NUMERO_BANCO_OD,' /-.,')) as BancoOD,
if(len(NUMERO_AGENCIA_OD)=0,0,PurgeChar(NUMERO_AGENCIA_OD,' /-.,')) as AgenciaOD,
if(len(NUMERO_AGENCIA_OD)=0,0,PurgeChar(NUMERO_AGENCIA_OD,' /-.,')) as #AgenciaIndice,
if(len(NUMERO_CONTA_OD)=0,0,PurgeChar(NUMERO_CONTA_OD,' /-.,')) as ContaOD,
if(len(NUMERO_CONTA_OD)=0,0,PurgeChar(NUMERO_CONTA_OD,' /-.,')) as #ContaIndice,
NOME_PESSOA_OD,
TIPO_PESSOA_OD as TipoOD,
OBSERVACAO as Observacao,
NOME_ENDOSSANTE_CHEQUE as EndossanteCheque,
DOC_ENDOSSANTE_CHEQUE as DocEndossanteCheque,
Year(DATA_LANCAMENTO) as Ano,
Month(DATA_LANCAMENTO) as Mes
FROM [lib://Dados Cargas SIMBA - LAB-LD/Cifra/$(caso)-ExtratoDetalhado.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);
ExtratoDetalhado:
LOAD
Linha,
caso,
NrCaso,
num(caso) as num_caso,
Banco,
Agencia,
Conta,
Num(Evaluate(Conta)) as num_conta,
Nomebanco,
TipoConta,
TipoContaNum,
Investigado,
CPFCNPJInvestigado,
$(formataCPFCNPJ(CPFCNPJInvestigado,'00000000000')) as CPF_CNPJ_TITULAR_formatado,
if (len(Text(CPFCNPJInvestigado) <=11 and CPFCNPJInvestigado > 192),'P. Física','P. Jurídica') as TipoInvestigado,
CNAB,
Operacao,
Data,
NrDoc,
NrDocTransacao,
LocalTransacao,
Valor,
Tipolancamento,
CPFCNPJOD,
//formatado
$(formataCPFCNPJ(CPFCNPJOD)) as CPF_CNPJ_OD_formatado,
BancoOD,
AgenciaOD,
#AgenciaIndice,
ContaOD,
#ContaIndice,
// é Investigado sim ou não
ApplyMap('StatusInvestigadoOD', caso & '-' & CPFCNPJOD,'N') as StatusInvestigadoOD,
caso & '-' & CPFCNPJOD as StatusInvestigadoODTESTE,
// CPFCNPJ Origem ou Destino identificados ?
if(CPFCNPJOD = 'NÃO IDENTIFICADO','N','S') as CPFCNPJODIdentificado, // marca os CPFsCNPJs que não foram identificados
// DESCRIÇÃO DE ORIGEM OU DESTINO
// origem destino igual ao titular
if(CPFCNPJInvestigado = CPFCNPJOD, // se origem/destino for igual ao investigado
Investigado, // Pega o nome do titular como PESSOAOD
if (len(PurgeChar(NOME_PESSOA_OD,' /-.,'))>0,upper(NOME_PESSOA_OD),
// '** SEM CPF/CNPJ-'&(upper(DESCRICAO_LANCAMENTO)) // existe informação no campo pega o nome // caso contrário pega a descrição do lançamento
'NÃO IDENTIFICADO'
)
) as PessoaOD,
TipoOD,
// encontra tipo de pessoa investigada
if (TipoOD = 2, 'P. Jurídica', if (TipoOD = 1, 'P. Física', 'Não Identificado')) as TipoPessoaOD,
Observacao,
EndossanteCheque,
DocEndossanteCheque,
Ano,
Mes
Resident ExtratoDetalhadoBruto;
Join
CNAB:
LOAD
CODIGO as CNAB,
DESCRICAO as Descricao_CNAB
FROM [lib://Dados Cargas SIMBA - LAB-LD/RAT/cnab.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
BancoSemMovimentacao:
mapping
LOAD
NUMERO_BANCO,
'S' as StatusBancoMovimentacao
FROM [lib://Dados Cargas SIMBA - LAB-LD/Cifra/$(caso)-DadosCadastrais.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);
DadosCadastrais:
LOAD
PurgeChar(CPF_CNPJ,' .-/,') as CPFCNPJ_DC,
if (len(PurgeChar(CPF_CNPJ,' .-/,')) <=11,'P. Física','P. Jurídica') as TipoInvestigado_DC,
upper(NOME) as NOME_DC,
mid(INVESTIGADO,1,1) as StatusInvestigado_DC,
NUMERO_BANCO as NUMERO_BANCO_DC,
NOME_BANCO as NOME_BANCO_DC,
TIPO_DOCUMENTO as TIPO_DOCUMENTO_DC,
NUMERO_DOCUMENTO as NUMERO_DOCUMENTO_DC,
ENDERECO as ENDERECO_DC,
CIDADE as CIDADE_DC,
UF as UF_DC,
CEP as CEP_DC,
TELEFONE as TELEFONE_DC,
PAIS as PAIS_DC,
VALOR_RENDA as VALOR_RENDA_DC,
DATA_ATUALIZACAO_RENDA as DATA_ATUALIZACAO_RENDA_DC,
num(Left(FileName(),6)) as caso,
PurgeChar(CPF_CNPJ,' .-/,') as chave
FROM [lib://Dados Cargas SIMBA - LAB-LD/Cifra/$(caso)-DadosCadastrais.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);
LancamentoCreditos1:
NoConcatenate LOAD
// num(Left(FileName(),6))as caso,
Data as Data1,
Ano as Ano1,
Mes as Mes1,
num(CPFCNPJInvestigado, '00000000000') as CPFCNPJInvestigado1,
Investigado as Investigado1,
upper(Nomebanco) as Nomebanco1,
Tipolancamento as Tipolancamento1,
Operacao as LancamentoGraf1,
Descricao_CNAB as CNAB1,
Valor as Valor1
Resident ExtratoDetalhado;
LancamentoCreditos2:
NoConcatenate LOAD
// num(Left(FileName(),6)) as caso,
Data as Data2,
Ano as Ano2,
Mes as Mes2,
num(CPFCNPJInvestigado, '00000000000') as CPFCNPJInvestigado2,
Investigado as Investigado2,
upper(Nomebanco) as Nomebanco2,
Tipolancamento as Tipolancamento2,
Operacao as LancamentoGraf2,
Descricao_CNAB as CNAB2,
Valor as Valor2
Resident ExtratoDetalhado;
// end sub
drop table ExtratoDetalhadoBruto;
Left Join (ExtratoDetalhado)
Load Linha,
num_caso&'|'&Banco&'|'&Agencia&'|'&num_conta&'|'&TipoContaNum as ID_CBAC
Resident ExtratoDetalhado;
Table [TITULARES] (full script):
Qualify *;
[TITULARES]:
SELECT
ROW_NUMBER() OVER(ORDER BY CASO.NUMERO) as LINHA,
//Campos tabela CASO
CASO.NUMERO AS NUM_CASO_TXT,
TO_NUMBER(CASO.NUMERO) AS NUM_CASO,
//Campos tabela TITULARIDADE
TITULARIDADE."ID_CONTA",
TITULARIDADE."TIPO",
TITULARIDADE."PESSOA_INVESTIGADA",
//Campos tabela PESSOA
PESSOA."CPF_CNPJ",
TO_NUMBER(REGEXP_REPLACE(PESSOA.CPF_CNPJ, '[^0-9]','' )) AS CPF_CNPJ_LIMPO,
UPPER(PESSOA."NOME") as NOME,
PESSOA.TIPO AS TIPO_PESSOA,
//Campos tabela CCS_INVESTIGADO
UPPER(CASE WHEN TITULARIDADE."PESSOA_INVESTIGADA" = 1
THEN CCS_INVESTIGADO."NOME_SRF"
ELSE PESSOA."NOME" END) AS "NOME_AJUSTADO", //Carrega nomes RFB dos investigados
//Campos tabela CONTA, AGENCIA, BANCO
CONTA."TIPO" AS "TIPO_CONTA",
(CASE CONTA."TIPO"
WHEN 1 THEN 'Corrente'
WHEN 2 THEN 'Poupança'
WHEN 3 THEN 'Investimento'
ELSE 'Outras' END) AS "TIPO_CONTA_DESC",
CONTA."MOVIMENTACAO_CONTA",
TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(CONTA."NUMERO", 'X', '0'), '[^0-9]','' )) "NUMERO_CONTA_LIMPO", //Transforma campo NUMERO_CONTA de texto para número, limpando dígitos não numéricos
AGENCIA."NUMERO" AS "NUMERO_AGENCIA",
TO_NUMBER(REGEXP_REPLACE(AGENCIA."NUMERO", '[^0-9]','' )) AS "NUMERO_AG_LIMPO",
AGENCIA."NUMERO_BANCO",
UPPER(BANCO."NOME") AS NOME_BANCO,
//Cria campo para servir de chave composta NumeroCaso|NumCPFCNPJ
TO_NUMBER(CASO.NUMERO) || TO_NUMBER(REGEXP_REPLACE(PESSOA.CPF_CNPJ, '[^0-9]','' )) AS ID_TESTE
FROM "SIMBA_CONVENIO"."CASO"
//FROM "SIMBA_CONVENIO"."TITULARIDADE"
INNER JOIN ATENDIMENTO ON TO_NUMBER(CASO.NUMERO) = TO_NUMBER(ATENDIMENTO.NUMERO_CASO)
INNER JOIN TITULARIDADE ON ATENDIMENTO.ID = TITULARIDADE.ID_ATENDIMENTO
INNER JOIN PESSOA ON TITULARIDADE."ID_PESSOA" = PESSOA."ID"
INNER JOIN CONTA ON TITULARIDADE."ID_CONTA" = CONTA."ID"
INNER JOIN AGENCIA ON CONTA."ID_AGENCIA" = AGENCIA."ID"
INNER JOIN BANCO ON TO_NUMBER(BANCO."NUMERO") = TO_NUMBER(AGENCIA."NUMERO_BANCO")
LEFT JOIN CCS_INVESTIGADO ON TO_NUMBER(CCS_INVESTIGADO.CPF_CNPJ) = TO_NUMBER(REGEXP_REPLACE(PESSOA.CPF_CNPJ, '[^0-9]','' ))
// WHERE TITULARIDADE."ID_ATENDIMENTO" IN (SELECT ATENDIMENTO."ID"
// FROM "SIMBA_CONVENIO"."ATENDIMENTO"
// WHERE TO_NUMBER(ATENDIMENTO."NUMERO_CASO") = 527)
//AND CONTA."MOVIMENTACAO_CONTA" IN (1,2)
WHERE CONTA."MOVIMENTACAO_CONTA" IN (1,2)
;
Unqualify *;
// BEGIN ==== Cria campo chave composta para conectar à tabela Extrato Detalhado ====
Left Join (TITULARES)
Load TITULARES.LINHA,
TITULARES.NUM_CASO&'|'&TITULARES.NUMERO_BANCO&'|'&TITULARES.NUMERO_AG_LIMPO&'|'&TITULARES.NUMERO_CONTA_LIMPO&'|'&TITULARES.TIPO_CONTA as ID_CBAC
Resident TITULARES;
