Queries históricas · parte 11

### QUERY: IASC Backlog.sql
Data: 2024-07-18 08:53:58
Tópicos: OUTROS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; bi_brrj_cus.bt_brrj_relatorio_de_cadastro
JOINs: 5
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
,ind_serv_executado
,G.municipality__c
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro G on G.accountcontract__c = A.numero_cliente
where municipality__c IN ([LISTA_DE_VALORES_OMITIDA]) and ultima_etapa = 'true' and Status_ordem IN ('ABERTA', 'SUSPENSA')
and cluster_ordem = 'INICIATIVA CLIENTE'
```

### QUERY: Faturamento de Coletivas.sql
Data: 2024-07-11 09:27:44
Tópicos: FATURAMENTO
Empresas detectadas: ENEL RJ
Objetos: global_brasil_rio.bt_global_billing_brazil_rio; bi_brrj_bill.bt_brrj_cip_faturado; bi_brrj_bill.bt_brrj_icg_compliance; bi_brrj_coll.bt_brrj_faturamento; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; global_brasil_rio.bt_global_billing_concepts_brazil_rio; dp_brrj.tb_capilaridade_credit_recovery
JOINs: 6
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
drop table if exists coletiva;
create temporary table coletiva as (
select fk_asset_id,cdc_aggregated_document_id, count (fk_asset_id) as qtd from global_brasil_rio.bt_global_billing_brazil_rio
where cdc_aggregated_document_id IN (
'[VALOR_OMITIDO]')
group by 1,2);
select distinct
B.cdc_aggregated_document_id as Conta_contrato_Coletiva,
G.orgao_controlador,
icg_numero_Cliente as Conta_Contrato,
F.numero_fatura,
doc_impressao,
D.identitynumber__c as Documento,
nr_medidor,
D.coordinatex__c,
D.coordinatey__c,
D.distributionaddress__c as Endereço,
D.neighbourhood__c as Bairro,
icg_municipio as Municipio,
D.postal_code__c as CEP,
cast(Consumo_ponta as decimal(17,2)) as consumo_ponta,
cast(Consumo_FP as decimal(17,2)) as consumo_FP,
cast(C.consumo_ativo as decimal(17,2)) as Consumo,
cast(consumo_reativo_fp as decimal(17,2)) as consumo_reativo_fp,
cast(consumo_reativo_hp as decimal(17,2)) as consumo_reativo_hp,
cast(demanda_ultrapassada_fp as decimal(17,2)) as demanda_ultrapassada_fp,
cast(demanda_ultrapassada_hp as decimal(17,2)) as demanda_ultrapassada_hp,
cast(demanda_lida_fp as decimal(17,2)) as demanda_lida_fp,
cast(demanda_lida_hp as decimal(17,2)) as demanda_lida_hp,
cast(demanda_faturada_fp as decimal(17,2)) as demanda_faturada_fp,
cast(demanda_faturada_hp as decimal(17,2)) as demanda_faturada_hp,
cast(TE as decimal(17,2)) as TE,
cast(TUSD as decimal(17,2)) as TUSD,
cast(IMPOSTOS as decimal(17,2)) as Imposto,
cast(Valor_consumo_reativo_FP as decimal(17,2)) as Valor_consumo_reativo_FP,
cast(Valor_consumo_reativo_NP as decimal(17,2)) as Valor_consumo_reativo_NP,
cast(Valor_Consumo_FP as decimal(17,2)) as Valor_Consumo_FP,
cast(Valor_Consumo_Ponta as decimal(17,2)) as Valor_Consumo_Ponta,
cast((E.TE + E.TUSD + E.IMPOSTOS+Valor_consumo_reativo_FP+Valor_consumo_reativo_NP+Valor_Consumo_FP+Valor_Consumo_Ponta) as decimal(17,2)) as Valor_consumo,
cast(Valor_demanda as decimal(17,2)) as Valor_demanda,
cast(Valor_TE_Ponta as decimal(17,2)) as Valor_TE_Ponta,
cast(VALOR_TE_FP as decimal(17,2)) as VALOR_TE_FP,
cast(E.bandeira_amarela as decimal(17,2)) as bandeira_amarela,
cast(E.bandeira_vermelha as decimal(17,2)) as bandeira_vermelha,
cast((E.bandeira_amarela + E.bandeira_vermelha) as decimal(17,2)) as valor_eventual,
cast(F.juros_fatura as decimal(17,2)) as Juros,
cast(F.multa_fatura as decimal(17,2)) as Multa,
cast(F.valor_da_fatura as decimal(17,2)) as Valor_total_fatura,
icg_referencia as referencia,
F.Data_Vencimento,
'NULL' as COD_BARRAS,
'NULL' as COD_BARRAS_AGRUPAMENTO,
F.grupo as Grupo,
F.tensao as tensao,
case
WHEN F.tipo_documento_faturamento = 'REFATURADO' then (
select left(F2.DATA_FATURAMENTO,10)
from bi_brrj_bill.bt_brrj_cip_faturado F2
where F.CONTA_CONTRATO = F2.conta_contrato
and F2.referencia_faturamento = F.referencia_faturamento
and F2.tipo_documento_faturamento = 'FATURADO'
order by F2.data_faturamento desc
limit 1
)
else F.data_faturamento
end as Data_Faturamento,
icg_tipo_fatura as tipo_fatura,
case
when tipo_fatura = 'FA' then 'Fatura Normal'
when tipo_fatura = 'EP' then 'Fatura Cancelada'
when tipo_fatura = 'RF' then '[VALOR_OMITIDO]'
end as Legenda_Tipo_Fatura,
cast(icg_cons_activo_livre as decimal(17,2)) as Consumo_Livre,
case
when icg_cons_activo_cativo <> 0 then 'Regulado'
else 'Livre'
end as Mercado,
case
when D.mov_out = '31/12/9999' then 'Ativo'
else 'Inativo'
end as Status,
cast(F.valor_cip_faturada as decimal(17,2)) as CIP,
cast(C.valor_icms as decimal(17,2)) as Icms,
cast(C.valor_pis as decimal(17,2)) as Pis,
cast(C.valor_cofins as decimal(17,2)) as cofins,
cast(C.valor_toi as decimal(17,2)) as Valor_Toi,
cast(retencao_ir as decimal(17,2)) as retencao_ir
from
bi_brrj_bill.bt_brrj_icg_compliance A
inner join
coletiva B on B.fk_asset_id = A.icg_numero_cliente
left join
bi_brrj_coll.bt_brrj_faturamento C on C.corr_facturacion = A.ICG_CORR_FACTURACION
left join
bi_brrj_cus.bt_brrj_relatorio_de_cadastro D on D.accountcontract__c = A.icg_numero_Cliente
left join (
select
left(fk_bill_id,16) as FATURA,
sum(case when lds_local_concept = 'ENERGIA ATIVA FORNECIDA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as TE,
sum(case when lds_local_concept = 'TUSD' then vad_concept_issued_amount_due_date_tax else 0 end) as TUSD,
sum(case when lds_local_concept = 'IMPOSTOS' then vad_concept_issued_amount_due_date_tax else 0 end) as IMPOSTOS,
sum(case when lds_local_concept = 'ADICIONAL BAND.VERMELHA' then vad_concept_issued_amount_due_date_tax else 0 end) as BANDEIRA_VERMELHA,
sum(case when lds_local_concept = 'ADICIONAL BAND.AMARELA' then vad_concept_issued_amount_due_date_tax else 0 end) as BANDEIRA_AMARELA,
sum(case when lds_local_concept_id IN ('WHTAX','[VALOR_OMITIDO]') then vad_concept_billed_amount_no_tax * -1 else 0 end) as retencao_ir,
sum(case when lds_local_concept = 'CONSUMO PONTA' then vad_concept_billed_quantity else 0 end) as Consumo_Ponta,
sum(case when lds_local_concept = 'CONSUMO FORA PONTA' then vad_concept_billed_quantity else 0 end) as Consumo_FP,
sum(case when lds_local_concept = 'CONSUMO PONTA' then vad_concept_issued_amount_due_date_tax else 0 end) as valor_Consumo_Ponta,
sum(case when lds_local_concept = 'CONSUMO FORA PONTA' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_Consumo_FP,
sum(case when lds_local_concept = 'CONSUMO REATIVO EXCEDENTE FP' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_consumo_reativo_FP,
sum(case when lds_local_concept = 'CONSUMO REATIVO EXCEDENTE NP' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_consumo_reativo_NP,
sum(case when lds_local_concept IN ('DEMANDA', 'DEMANDA ATIVA') then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_demanda,
sum(case when lds_local_concept = 'ENERGIA ATV FORN PONTA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as VALOR_TE_PONTA,
sum(case when lds_local_concept = 'ENERGIA ATV FORN F PONTA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as VALOR_TE_FP
from
global_brasil_rio.bt_global_billing_concepts_brazil_rio
group by
left(fk_bill_id,16)
) E on E.FATURA = A.ICG_CORR_FACTURACION
left join (
select
numero_fatura,
conta_contrato,
doc_impressao,
referencia_faturamento,
consumo_reativo_fp,
consumo_reativo_hp,
demanda_ultrapassada_fp,
demanda_ultrapassada_hp,
demanda_lida_fp,
demanda_lida_hp,
demanda_faturada_fp,
demanda_faturada_hp,
leitura_anterior,
tipo_ligacao,
tipo_documento_faturamento,
juros_fatura,
multa_fatura,
valor_da_fatura,
nr_medidor,
consumo_ativo_fp,
consumo_ativo_hp,
valor_cip_faturada,
grupo,
tensao,
left(data_vencimento_fatura,10) as Data_Vencimento,
left(data_faturamento,10) as data_faturamento
from
bi_brrj_bill.bt_brrj_cip_faturado A
where
not exists (
select 1
from bi_brrj_bill.bt_brrj_cip_faturado B
where
tipo_documento_faturamento = 'ESTONO PLENO'
and B.numero_fatura = A.numero_fatura
)
) F on F.numero_fatura = A.icg_corr_facturacion
left join
dp_brrj.tb_capilaridade_credit_recovery G on G.numero_cliente = icg_numero_Cliente
where
F.numero_fatura is not null and tipo_fatura <> 'EP'
and icg_referencia >= '2023/07';
```

### QUERY: SEDUC.sql
Data: 2024-07-01 10:27:28
Tópicos: OUTROS
Empresas detectadas: ENEL RJ
Objetos: global_brasil_rio.bt_global_billing_brazil_rio; bi_brrj_bill.bt_brrj_cip_faturado; bi_brrj_bill.bt_brrj_icg_compliance; bi_brrj_coll.bt_brrj_faturamento; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; global_brasil_rio.bt_global_billing_concepts_brazil_rio; dp_brrj.tb_capilaridade_credit_recovery
JOINs: 6
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
drop table if exists coletiva;
create temporary table coletiva as (
select fk_asset_id,cdc_aggregated_document_id, count (fk_asset_id) as qtd from global_brasil_rio.bt_global_billing_brazil_rio
group by 1,2);
select distinct
B.cdc_aggregated_document_id as Conta_contrato_Coletiva,
G.orgao_controlador,
icg_numero_Cliente as Conta_Contrato,
F.numero_fatura,
D.identitynumber__c as Documento,
nr_medidor,
D.coordinatex__c,
D.coordinatey__c,
D.distributionaddress__c as Endereço,
D.neighbourhood__c as Bairro,
icg_municipio as Municipio,
D.postal_code__c as CEP,
Consumo_ponta,
Consumo_FP,
cast(C.consumo_ativo as decimal(17,2)) as Consumo,
consumo_reativo_fp,
consumo_reativo_hp,
demanda_ultrapassada_fp,
demanda_ultrapassada_hp,
demanda_lida_fp,
demanda_lida_hp,
demanda_faturada_fp,
demanda_faturada_hp,
TE,
TUSD,
IMPOSTOS,
Valor_consumo_reativo_FP,
Valor_consumo_reativo_NP,
Valor_Consumo_FP,
Valor_Consumo_Ponta,
(E.TE + E.TUSD + E.IMPOSTOS+Valor_consumo_reativo_FP+Valor_consumo_reativo_NP+Valor_Consumo_FP+Valor_Consumo_Ponta) as Valor_consumo,
Valor_demanda,
Valor_TE_Ponta,
VALOR_TE_FP,
E.bandeira_amarela,
E.bandeira_vermelha,
(E.bandeira_amarela + E.bandeira_vermelha) as valor_eventual,
cast(F.juros_fatura as decimal(17,2)) as Juros,
cast(F.multa_fatura as decimal(17,2)) as Multa,
cast(F.valor_da_fatura as decimal(17,2)) as Valor_total_fatura,
icg_referencia as referencia,
F.Data_Vencimento,
'NULL' as COD_BARRAS,
'NULL' as COD_BARRAS_AGRUPAMENTO,
F.grupo as Grupo,
F.tensao as tensao,
case
WHEN F.tipo_documento_faturamento = 'REFATURADO' then (
select left(F2.DATA_FATURAMENTO,10)
from bi_brrj_bill.bt_brrj_cip_faturado F2
where F.CONTA_CONTRATO = F2.conta_contrato
and F2.referencia_faturamento = F.referencia_faturamento
and F2.tipo_documento_faturamento = 'FATURADO'
order by F2.data_faturamento desc
limit 1
)
else F.data_faturamento
end as Data_Faturamento,
icg_tipo_fatura as tipo_fatura,
case
when tipo_fatura = 'FA' then 'Fatura Normal'
when tipo_fatura = 'EP' then 'Fatura Cancelada'
when tipo_fatura = 'RF' then '[VALOR_OMITIDO]'
end as Legenda_Tipo_Fatura,
cast(icg_cons_activo_livre as decimal(17,2)) as Consumo_Livre,
case
when icg_cons_activo_cativo <> 0 then 'Regulado'
else 'Livre'
end as Mercado,
case
when D.mov_out = '31/12/9999' then 'Ativo'
else 'Inativo'
end as Status,
cast(C.valor_icms as decimal(17,2)) as Icms,
cast(C.valor_pis as decimal(17,2)) as Pis,
cast(C.valor_cofins as decimal(17,2)) as cofins,
cast(C.valor_toi as decimal(17,2)) as Valor_Toi,
cast(retencao_ir as decimal(17,2)) as retencao_ir
from
bi_brrj_bill.bt_brrj_icg_compliance A
inner join
coletiva B on B.fk_asset_id = A.icg_numero_cliente
left join
bi_brrj_coll.bt_brrj_faturamento C on C.corr_facturacion = A.ICG_CORR_FACTURACION
left join
bi_brrj_cus.bt_brrj_relatorio_de_cadastro D on D.accountcontract__c = A.icg_numero_Cliente
left join (
select
left(fk_bill_id,16) as FATURA,
sum(case when lds_local_concept = 'ENERGIA ATIVA FORNECIDA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as TE,
sum(case when lds_local_concept = 'TUSD' then vad_concept_issued_amount_due_date_tax else 0 end) as TUSD,
sum(case when lds_local_concept = 'IMPOSTOS' then vad_concept_issued_amount_due_date_tax else 0 end) as IMPOSTOS,
sum(case when lds_local_concept = 'ADICIONAL BAND.VERMELHA' then vad_concept_issued_amount_due_date_tax else 0 end) as BANDEIRA_VERMELHA,
sum(case when lds_local_concept = 'ADICIONAL BAND.AMARELA' then vad_concept_issued_amount_due_date_tax else 0 end) as BANDEIRA_AMARELA,
sum(case when lds_local_concept_id IN ('WHTAX','[VALOR_OMITIDO]') then vad_concept_billed_amount_no_tax * -1 else 0 end) as retencao_ir,
sum(case when lds_local_concept = 'CONSUMO PONTA' then vad_concept_billed_quantity else 0 end) as Consumo_Ponta,
sum(case when lds_local_concept = 'CONSUMO FORA PONTA' then vad_concept_billed_quantity else 0 end) as Consumo_FP,
sum(case when lds_local_concept = 'CONSUMO PONTA' then vad_concept_issued_amount_due_date_tax else 0 end) as valor_Consumo_Ponta,
sum(case when lds_local_concept = 'CONSUMO FORA PONTA' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_Consumo_FP,
sum(case when lds_local_concept = 'CONSUMO REATIVO EXCEDENTE FP' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_consumo_reativo_FP,
sum(case when lds_local_concept = 'CONSUMO REATIVO EXCEDENTE NP' then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_consumo_reativo_NP,
sum(case when lds_local_concept IN ('DEMANDA', 'DEMANDA ATIVA') then vad_concept_issued_amount_due_date_tax else 0 end) as Valor_demanda,
sum(case when lds_local_concept = 'ENERGIA ATV FORN PONTA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as VALOR_TE_PONTA,
sum(case when lds_local_concept = 'ENERGIA ATV FORN F PONTA TE' then vad_concept_issued_amount_due_date_tax else 0 end) as VALOR_TE_FP
from
global_brasil_rio.bt_global_billing_concepts_brazil_rio
group by
left(fk_bill_id,16)
) E on E.FATURA = A.ICG_CORR_FACTURACION
left join (
select
numero_fatura,
conta_contrato,
referencia_faturamento,
consumo_reativo_fp,
consumo_reativo_hp,
demanda_ultrapassada_fp,
demanda_ultrapassada_hp,
demanda_lida_fp,
demanda_lida_hp,
demanda_faturada_fp,
demanda_faturada_hp,
leitura_anterior,
tipo_ligacao,
tipo_documento_faturamento,
juros_fatura,
multa_fatura,
valor_da_fatura,
nr_medidor,
consumo_ativo_fp,
consumo_ativo_hp,
valor_cip_faturada,
grupo,
tensao,
left(data_vencimento_fatura,10) as Data_Vencimento,
left(data_faturamento,10) as data_faturamento
from
bi_brrj_bill.bt_brrj_cip_faturado A
where
not exists (
select 1
from bi_brrj_bill.bt_brrj_cip_faturado B
where
tipo_documento_faturamento = 'ESTONO PLENO'
and B.numero_fatura = A.numero_fatura
)
) F on F.numero_fatura = A.icg_corr_facturacion
left join
dp_brrj.tb_capilaridade_credit_recovery G on G.numero_cliente = icg_numero_Cliente
where
F.numero_fatura is not null and tipo_fatura <> 'EP'
and icg_referencia >= '2023/07' and icg_numero_cliente IN ([LISTA_DE_VALORES_OMITIDA])
```

### QUERY: Query Dashboard Ordens com Compensações.sql
Data: 2024-06-26 15:52:38
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; dp_brrj.bt_brrj_sucursal; bi_brrj_cus.bt_brrj_clientes_cliente; dp_brrj.e2e_base_k1; dp_brrj.e2e_base_k2; global_brasil_rio.bt_global_billing_brazil_rio; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; global_brasil_rio.bt_global_billing_concepts_brazil_rio
JOINs: 15
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,A.numero_ordem
,case
when H.compensacao > 0 then cast(H.compensacao as decimal(17,2)) else 0
end as compensacao
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
,G.Polo
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
left join dp_brrj.bt_brrj_sucursal G on G.sucursal = A.sucursal
left join (select distinct
numero_ordem,
des_servico,
status_ordem as status_ordem_c,
numero_cliente as numero_cliente_c,
fk_asset_id,
sds_accounting_period,
data_inicio,
data_atual,
pv,
pr,
situacao as situacao_c,
tipo_cliente,
tipo_ligacao,
tipo as tipo_anexo_IV,
k1,
k2,
TUSD,
round((k1 + k2 * TUSD * (log(nullif(pv, 0)) - log(nullif(pr, 0))/ log(10))),2) as compensacao
from (
SELECT distinct
o.numero_ordem,
s.status_ordem,
o.numero_cliente,
o.des_servico,
x.fk_asset_id,
x.sds_accounting_period,
cast(o.data_ingresso as DATE) as data_inicio,
TO_CHAR((DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day'), 'YYYYMMDD') as data_atual,
(CURRENT_DATE - CAST(data_ingresso AS DATE)) AS pv,
cast(d.pr as int),
o.situacao,
c.tipo_cliente,
c.tipo_ligacao,
d.tipo,
b.k1,
k.k2,
z.TUSD
FROM bi_brrj_cus.bt_brrj_clientes_ordem_servico o
LEFT JOIN bi_brrj_cus.bt_brrj_clientes_cliente c
ON c.numero_cliente = o.numero_cliente
LEFT JOIN DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS d
ON d.chave = o.tipo_ordem||o.cod_servico||RTRIM(o.des_servico) and d.cluster_ordem = 'INICIATIVA CLIENTE'
left join DP_BRRJ.tb_aux_depara_estado_ordens s
ON o.estado = s.estado
LEFT JOIN dp_brrj.e2e_base_k1 b
ON b.codigo = c.tipo_cliente||c.tipo_ligacao
LEFT JOIN dp_brrj.e2e_base_k2 k
ON k.codigo = c.tipo_cliente||d.tipo
LEFT JOIN (select B.accountcontract__c as fk_asset_id, max(sds_accounting_period) as sds_accounting_period
from global_brasil_rio.bt_global_billing_brazil_rio A
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro B on B.id_asset = A.fk_asset_id
group by 1) x
ON x.fk_asset_id = o.numero_cliente
LEFT JOIN (select B.accountcontract__c as fk_asset_id, sds_accounting_period, SUM(vad_concept_billed_amount_no_tax) * 0.755 AS TUSD
from global_brasil_rio.bt_global_billing_concepts_brazil_rio A
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro B on B.id_asset = A.fk_asset_id
where lds_local_concept LIKE '%TUSD%'
group by 1,2) z
ON z.fk_asset_id = o.numero_cliente and x.sds_accounting_period = z.sds_accounting_period
where d.negocio = 'MERCADO' and d.regulada = 'Sim'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
)) H on H.numero_ordem = A.numero_ordem
where ultima_etapa = true and A.numero_ordem IN ()
```

### QUERY: Serviços em loja por atendente.sql
Data: 2024-06-20 10:21:56
Tópicos: ORDENS_SERVICOS, CANAIS_ATENDIMENTO
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_requestqlik; dp_brrj.bt_carteira_lojas_rj; dp_brrj.bt_de_para_motivos_requestqlik
JOINs: 2
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select A.br_atendente,
case
when B.localidade is null then 'Não Carteirizado'
else B.Localidade
end
as lojas,
A.canal_oficial,
A.ano,
A.mes,
A.ano||A.mes as anomes,
A.tipo_caso,
A.motivo,
A.submotivo,
C.motivo_tratado,
count(numero_caso) as qtd
from bi_brrj_act.bt_brrj_requestqlik A
left join dp_brrj.bt_carteira_lojas_rj B on B.chave = br_atendente ||A.ano||A.mes
LEFT JOIN
dp_brrj.bt_de_para_motivos_requestqlik C ON C.chave = A.motivo || A.submotivo
where ano >= '2023' and canal_oficial IN ('Lojas', 'Whatsapp Lojas', '[VALOR_OMITIDO]')
group by
1,2,3,4,5,6,7,8,9,10
```

### QUERY: SEFAZ 3.0.sql
Data: 2024-06-18 08:34:28
Tópicos: SEFAZ
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_bill.bt_brrj_cip_faturado; bi_brrj_bill.bt_brrj_icg_compliance; bi_brrj_coll.bt_brrj_faturamento; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_cus.bt_brrj_conta_contrato; dp_brrj.tb_capilaridade_credit_recovery; global_brasil_rio.bt_global_billing_concepts_brazil_rio
JOINs: 8
Sinais legados: SELECT*=True | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select distinct
D.Fatura_Coletiva
,cast(F.consumo_faturado as decimal(17,2)) Consumo
,C.identitynumber__c Documento
,C.postal_code__c CEP
,cast(H.vad_concept_issued_amount_due_date_tax as decimal(17,2)) as VALOR_CONSUMO
,cast(J.vad_concept_issued_amount_due_date_tax as decimal(17,2)) as VALOR_EVENTUAL
,cast(F.juros_fatura as decimal(17,2)) as Juros
,cast(F.multa_fatura as decimal(17,2)) as Multa
,icg_referencia referencia
,left(F.data_vencimento_fatura,10) as Data_Vencimento
,cast(F.valor_da_fatura as decimal(17,2)) Valor_Faturado
,'NULL' as COD_BARRAS
,'NULL' as COD_BARRAS_AGRUPAMENTO
,F.grupo as Tensao
,case
WHEN
F.tipo_documento_faturamento = 'REFATURADO' then (select left(F2.DATA_FATURAMENTO,10)
from bi_brrj_bill.bt_brrj_cip_faturado F2
where F.CONTA_CONTRATO = F2.conta_contrato
and F2.referencia_faturamento = F.referencia_faturamento and F2.tipo_documento_faturamento = 'FATURADO'
order by F2.data_faturamento desc limit 1)
else
left(F.data_faturamento,10) end as Data_Faturamento
,G.orgao_controlador
,icg_numero_Cliente Conta_Contrato
,F.numero_fatura
,nr_medidor
,C.coordinatex__c
,C.coordinatey__c
,C.distributionaddress__c Endereço
,C.neighbourhood__c Bairro
,icg_municipio Municipio
,icg_tipo_fatura tipo_fatura
,case
when tipo_fatura = 'FA' then 'Fatura Normal'
when tipo_fatura = 'EP' then 'Fatura Cancelada'
when tipo_fatura = 'RF' then '[VALOR_OMITIDO]'
end
as Legenda_Tipo_Fatura
,cast(icg_cons_activo_livre as decimal(17,2)) Consumo_Livre
,case when icg_cons_activo_cativo <> 0 then 'Regulado' else 'Livre' end Mercado
,case when C.mov_out = '31/12/9999' then 'Ativo' else 'Inativo' end Status
,cast(B.valor_icms as decimal(17,2)) as Icms
,cast(B.valor_pis as decimal(17,2)) as Pis
,cast(B.valor_cofins as decimal(17,2)) as cofins
,cast(B.valor_toi as decimal(17,2)) as Valor_Toi
,cast(retencao_ir*-1 as decimal(17,2)) as retencao_IR
from bi_brrj_bill.bt_brrj_icg_compliance A
left join bi_brrj_coll.bt_brrj_faturamento B on B.corr_facturacion = A.ICG_CORR_FACTURACION and ORIGEM = 'FATURADO'
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro C on C.accountcontract__c = icg_numero_Cliente
left join bi_brrj_cus.bt_brrj_conta_contrato D on D.conta_contrato = A.icg_numero_Cliente
left join (select *
from bi_brrj_bill.bt_brrj_cip_faturado A
where not exists (select 1 from bi_brrj_bill.bt_brrj_cip_faturado B where tipo_documento_faturamento = 'ESTONO PLENO' and B.numero_fatura = A.numero_fatura)) F on numero_fatura = A.icg_corr_facturacion
left join dp_brrj.tb_capilaridade_credit_recovery G on G.numero_cliente = icg_numero_Cliente
left join (select
left(fk_bill_id,16) as FATURA,
SUM(vad_concept_issued_amount_due_date_tax) as vad_concept_issued_amount_due_date_tax
from global_brasil_rio.bt_global_billing_concepts_brazil_rio H
where lds_local_concept IN ('IMPOSTOS','ENERGIA ATIVA FORNECIDA TE','TUSD') group by FATURA) H on left(H.FATURA,16) = A.icg_corr_facturacion
left join (select
left(fk_bill_id,16) as FATURA,
SUM(vad_concept_issued_amount_due_date_tax) as vad_concept_issued_amount_due_date_tax
from global_brasil_rio.bt_global_billing_concepts_brazil_rio
where lds_local_concept IN ('ADICIONAL BAND.%') group by FATURA) J on left(J.FATURA,16) = A.icg_corr_facturacion
left join (select left(fk_bill_id,16) as fatura,sum(vad_concept_billed_amount_no_tax) as Retencao_IR from global_brasil_rio.bt_global_billing_concepts_brazil_rio
where lds_local_concept_id IN ('WHTAX','[VALOR_OMITIDO]') group by 1) K on K.Fatura = A.icg_corr_facturacion
where F.numero_fatura is not null and icg_referencia >= '2023/06' and icg_numero_cliente IN ([LISTA_DE_VALORES_OMITIDA])
```

### QUERY: Compensações de Troca.sql
Data: 2024-06-13 10:29:18
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_requestqlik; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_bill.bt_brrj_base_faturamento; dp_brrj.e2e_tabela_cliente_comp
JOINs: 6
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select
r.numero_caso as caso,
r.submotivo as motivo,
c.accountcontract__c as conta_contrato,
c.pointofdeliverynumber__c as ponto_de_fornecimento,
c.segmenttype__c as segmento,
c.zone__c as zona,
r.data_criacao as data_criacao_caso,
r.data_fechamento_caso,
c.createddate_contract as data_criacao_contrato,
c.createddate_asset as dt_criacao_asset,
c.mov_in as dt_movin,
c.clienteativo,
c.mov_out as dt_movout,
min(fecha_facturacion) as prim_faturamento,
min(fecha_lectura) as prim_leitura,
z.accountcontract__c
from bi_brrj_act.bt_brrj_requestqlik r
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro c on c.pointofdeliverynumber__c = r.numero_ponto_de_fornecimento
left join bi_brrj_bill.bt_brrj_base_faturamento f on f.numero_cliente = c.accountcontract__c and r.data_criacao >= c.createddate_asset
join dp_brrj.e2e_tabela_cliente_comp x on r.numero_caso = x.caso
join ( with aa as(select cc.accountcontract__c, cc.pointofdeliverynumber__c, createddate_asset,
row_number() over (partition by cc.pointofdeliverynumber__c order by cast(cc.createddate_asset as date) asc) as rwn
from bi_brrj_cus.bt_brrj_relatorio_de_cadastro cc
join bi_brrj_act.bt_brrj_requestqlik rr on cc.pointofdeliverynumber__c = rr.numero_ponto_de_fornecimento and cc.createddate_asset >= rr.data_criacao
join dp_brrj.e2e_tabela_cliente_comp tc on tc.caso = rr.numero_caso)
select aa.accountcontract__c, aa.pointofdeliverynumber__c from aa where rwn = 1 group by 1,2) z on z.accountcontract__c = c.accountcontract__c and z.pointofdeliverynumber__c = c.pointofdeliverynumber__c
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,16
```

### QUERY: Analise troca.sql
Data: 2024-06-05 16:21:02
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; global_brasil_rio.bt_global_asset_brazil_rio
JOINs: 6
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
drop table if exists TROCA;
create temporary table TROCA as
select nro_caso as CASO,
left(data_ingresso,4) as Ano_troca,
substring(data_ingresso,6,2) as mes_troca,
ano_troca||mes_troca as anomes_troca,
NUMERO_ORDEM,
TIPO_SERVICO
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
where
anomes_troca >= '[VALOR_OMITIDO]'
and COD_SERVICO IN ('1TC',
'1TL',
'EPX',
'TTX',
'JTC',
'JT1')
and NUMERO_ORDEM is not null;
select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
,G.lds_asset_city as Municipio
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
left join global_brasil_rio.bt_global_asset_brazil_rio G on G.fk_external_asset_id = A.numero_cliente
left join TROCA T on T.caso = nro_caso
where ultima_etapa = true and anomes >= '[VALOR_OMITIDO]'
and nro_caso IN (select caso from TROCA)
order by A.numero_ordem, corr_visita ASC;
```

### QUERY: Carteira B2G e B2B CE.sql
Data: 2024-06-04 16:16:28
Tópicos: OUTROS
Empresas detectadas: ENEL CE
Objetos: dp_brce.e2e_base_clientes_b2bg
JOINs: 0
Sinais legados: SELECT*=True | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select * from dp_brce.e2e_base_clientes_b2bg
```

### QUERY: War Room Contingencias.sql
Data: 2024-05-31 19:05:44
Tópicos: OUTROS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_requestqlik; dp_brrj.bt_de_para_motivos_requestqlik
JOINs: 1
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select distinct
numero_ponto_de_fornecimento as instalacao,
ano||mes as anomes,
left(data_criacao,10) as data_atendimento,
tipo_caso,
A.motivo,
A.submotivo,
C.motivo_tratado,
os_procedencia,
canal_caso,
canal_oficial,
tipocanal,
count(numero_caso) as qtd
FROM
bi_brrj_act.bt_brrj_requestqlik A
LEFT JOIN
dp_brrj.bt_de_para_motivos_requestqlik C ON C.chave = A.motivo || A.submotivo
where left(data_criacao,7) >= '2023-07'
and numero_ponto_de_fornecimento IN ()
GROUP BY
1,2,3,4,5,6,7,8,9,10,11;
```

### QUERY: Ordens por encerramento.sql
Data: 2024-05-13 13:03:54
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; bi_brrj_cus.bt_brrj_grandes_ordem_servico
JOINs: 8
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select
'RJ' as Distribuidora
,'Grupo B' as Grupo_Tensao
,ano
,mes
,ano_encerramento
,mes_encerramento
,des_servico
,descricao_etapa
,AREA_RESPONSAVEL
,Responsavel
,area_responsavel_etapa
,responsavel_etapa
,negocio
,regulada
,artigo
,status_prazo
,farol_prazo
,Controle_Prazo
,status_ordem
,count(numero_ordem) as qtd
from
(select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,left(A.data_estado,4) as ano_encerramento
,substring(A.data_estado,6,2) as mes_encerramento
,ano_encerramento||mes_encerramento as anomes_encerramento
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,D.area_responsavel_etapa
,D.responsavel_etapa
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
where ultima_etapa = true and cluster_ordem = 'INICIATIVA CLIENTE' and status_prazo is not null and ano is not null)
group by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
union all
select
'RJ' as Distribuidora
,'Grupo A' as Grupo_Tensao
,ano
,mes
,ano_encerramento
,mes_encerramento
,des_servico
,descricao_etapa
,AREA_RESPONSAVEL
,Responsavel
,area_responsavel_etapa
,responsavel_etapa
,negocio
,regulada
,artigo
,status_prazo
,farol_prazo
,Controle_Prazo
,status_ordem
,count(numero_ordem) as qtd
from
(select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,left(A.data_estado,4) as ano_encerramento
,substring(A.data_estado,6,2) as mes_encerramento
,ano_encerramento||mes_encerramento as anomes_encerramento
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,D.area_responsavel_etapa
,D.responsavel_etapa
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_grandes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_grandes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
where ultima_etapa = true and cluster_ordem = 'INICIATIVA CLIENTE' and status_prazo is not null and ano is not null)
group by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
```

### QUERY: Monitorar DP.sql
Data: 2024-05-07 15:03:34
Tópicos: OUTROS
Empresas detectadas: NAO_IDENTIFICADA
Objetos: 
JOINs: 3
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
SELECT
n.nspname AS schema_name
, pg_get_userbyid(c.relowner) AS table_owner
, c.relname AS table_name
, c.reltuples as n_rows
, CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END
AS table_type
FROM pg_class As c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_description As d
ON (d.objoid = c.oid AND d.objsubid = 0)
WHERE c.relkind IN ('r', 'v')
and table_owner = '[VALOR_OMITIDO]'
ORDER BY c.reltuples desc,n.nspname, c.relname
;
```

### QUERY: Base Clientes RJ.sql
Data: 2024-05-07 09:19:22
Tópicos: CADASTRO_CLIENTE
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_relatorio_de_cadastro
JOINs: 0
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=True | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
with Customer as (SELECT
row_number() over(order by accountcontract__c) as Numero
,name_account
,accountcontract__c
,pointofdeliverynumber__c
,celular
,fixo
,mainphone__c
,secondaryphone__c
,email
,primaryemail__c
,secondaryemail__c
,distributionaddress__c
,neighbourhood__c
,municipality__c
,identitynumber__c
,postal_code__c
from bi_brrj_cus.bt_brrj_relatorio_de_cadastro
where segmenttype__c =' B')
Select name_account
,accountcontract__c
,pointofdeliverynumber__c
,celular
,fixo
,mainphone__c
,secondaryphone__c
,email
,primaryemail__c
,secondaryemail__c
,distributionaddress__c
,neighbourhood__c
,municipality__c
,identitynumber__c
,postal_code__c
From customer where Numero <= 1000000
```

### QUERY: Clientes Ativos por Município.sql
Data: 2024-04-22 10:56:38
Tópicos: CADASTRO_CLIENTE
Empresas detectadas: NAO_IDENTIFICADA
Objetos: global_brasil_rio.bt_global_asset_brazil_rio
JOINs: 0
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select lds_asset_city, count(cdc_pod_id) as qtd
from
(select row_number () over (partition by asset.pk_asset_id, asset.pk_contract_id
ORDER BY asset.dte_validity_start_date desc,
asset.dte_asset_end_date asc)
as r,asset.*
from global_brasil_rio.bt_global_asset_brazil_rio as asset
where asset.dte_validity_start_date < current_date) as a
where 1=1 and r = 1
and (a.dte_asset_end_date > current_date or a.dte_asset_end_date is null)
and a.dte_asset_start_date < current_date
group by
1
order by qtd desc
```

### QUERY: Modelo Preditivo - Priorização de Ordens.sql
Data: 2024-04-21 00:48:10
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_requestqlik; bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; dp_brrj.bt_brrj_sucursal; bi_brrj_cus.bt_brrj_grandes_ordem_servico
JOINs: 25
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
DROP TABLE IF EXISTS _variables;
CREATE TEMPORARY TABLE _variables AS (
SELECT
(SELECT TO_CHAR((DATE_TRUNC('MONTH', CURRENT_DATE)), 'YYYY-MM') as anomes)::varchar(7) as anomes,
(SELECT TO_CHAR((DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 month'), 'YYYY-MM') as anomes_M1)::varchar(7) as anomes_M1,
(SELECT TO_CHAR((DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '2 month'), 'YYYY-MM') as anomes_M2)::varchar(7) as anomes_M2);
DROP TABLE IF EXISTS Rec_iguais;
CREATE TEMPORARY TABLE Rec_iguais AS (
SELECT cta_contrato, submotivo, COUNT(numero_caso) as Rec_reincidentes
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE tipo_caso = 'Reclamação'
AND dataingresso >= CURRENT_DATE - INTERVAL '6' MONTH AND expurgado = 'false'
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND cta_contrato IS NOT null
GROUP BY 1, 2
HAVING COUNT(numero_caso) >= 2
);
DROP TABLE IF EXISTS Rec_diversas;
CREATE TEMPORARY TABLE Rec_diversas AS (
SELECT cta_contrato, COUNT(DISTINCT submotivo) as Rec_diversas
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE tipo_caso = 'Reclamação'
AND dataingresso >= CURRENT_DATE - INTERVAL '6' MONTH AND expurgado = 'false'
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND cta_contrato IS NOT null
GROUP BY 1
HAVING Rec_diversas >= 3
);
DROP TABLE IF EXISTS inf_repetidas;
CREATE TEMPORARY TABLE inf_repetidas AS (
SELECT cta_contrato, COUNT(DISTINCT submotivo) as inf_repetidas
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE tipo_caso = 'Informação'
AND dataingresso >= CURRENT_DATE - INTERVAL '30' DAY AND expurgado = 'false'
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
and submotivo not IN ('[VALOR_OMITIDO]')
AND cta_contrato IS NOT null
GROUP BY 1
HAVING inf_repetidas >= 3
);
DROP TABLE IF EXISTS N3;
CREATE TEMPORARY TABLE N3 AS (
SELECT cta_contrato, COUNT(DISTINCT submotivo) as Aneel
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE dataingresso >= CURRENT_DATE - INTERVAL '13' MONTH AND expurgado = 'false'
AND canal_caso LIKE '%ANEEL%'
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND cta_contrato IS NOT null
GROUP BY 1
);
DROP TABLE IF EXISTS Judicial;
CREATE TEMPORARY table Judicial AS (
SELECT cta_contrato, COUNT(DISTINCT submotivo) as Judicial
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE dataingresso >= CURRENT_DATE - INTERVAL '13' MONTH AND expurgado = 'false'
AND canal_caso IN ('19-JUDICIAL',
'36-JURIDICO - PROCESSO JUDICIAL',
'34-JUDICIAL')
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND cta_contrato IS NOT null
GROUP BY 1
);
DROP TABLE IF EXISTS OUVIDORIA;
CREATE TEMPORARY TABLE OUVIDORIA AS (
SELECT cta_contrato, COUNT(DISTINCT submotivo) as QTD_OUVIDORIA
FROM bi_brrj_act.bt_brrj_requestqlik A
left join _variables B on B.anomes = left(data_criacao,7)
WHERE dataingresso >= CURRENT_DATE - INTERVAL '13' MONTH AND expurgado = 'false'
and canal_caso IN ([LISTA_DE_VALORES_OMITIDA])
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND cta_contrato IS NOT null
GROUP BY 1
HAVING QTD_OUVIDORIA >= 2
);
DROP TABLE IF EXISTS CRITICOS;
CREATE TEMPORARY TABLE CRITICOS AS (
SELECT DISTINCT
A.cta_contrato,
A.ano,
A.mes,
A.tipo_caso,
A.motivo,
A.submotivo,
A.numero_caso,
A.data_criacao,
numero_da_ordem_ou_atividade as Ordem,
A.status,
CASE WHEN B.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS Rec_iguais,
CASE WHEN C.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS Rec_diversas,
CASE WHEN D.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS inf_repetidas,
CASE WHEN F.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS Ouvidoria,
CASE WHEN G.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS Judicial,
CASE WHEN E.cta_contrato IS NOT NULL THEN 1 ELSE 0 END AS Aneel,
CASE
WHEN Rec_iguais <> 0 THEN true
WHEN rec_diversas <> 0 THEN true
WHEN inf_repetidas <> 0 THEN true
WHEN aneel <> 0 THEN true
WHEN Ouvidoria <> 0 THEN true
WHEN Judicial <> 0 THEN true
ELSE false
END AS Flag_critico,
CASE WHEN rec_iguais <> 0 THEN 15 ELSE 0 END AS peso_rec_iguais,
CASE WHEN rec_diversas <> 0 THEN 15 ELSE 0 END AS peso_rec_diversas,
CASE WHEN inf_repetidas <> 0 THEN 10 ELSE 0 END AS peso_inf_repetidas,
CASE WHEN ouvidoria <> 0 THEN 20 ELSE 0 END AS peso_ouvidoria,
CASE WHEN judicial <> 0 THEN 20 ELSE 0 END AS peso_judicial,
CASE WHEN aneel <> 0 THEN 20 ELSE 0 END AS peso_aneel,
CAST(peso_rec_iguais AS int) + CAST(peso_rec_diversas AS int) + CAST(peso_inf_repetidas AS int) + CAST(peso_ouvidoria AS int) + CAST(peso_judicial AS int) + CAST(peso_aneel AS int) AS Nota_F,
CASE
WHEN nota_F < 15 THEN 'Verde'
WHEN Nota_f = 15 AND CAST(peso_rec_iguais AS int) = 15 THEN 'Laranja'
WHEN Nota_f = 15 AND CAST(peso_rec_diversas AS int) = 15 THEN 'Amarelo'
WHEN Nota_F = 20 AND CAST(peso_judicial AS int) = 0 AND peso_aneel = 0 THEN 'Amarelo'
WHEN Nota_F BETWEEN 20 AND 31 AND CAST(peso_judicial AS int) = 0 AND CAST(peso_aneel AS int) = 0 THEN 'Laranja'
WHEN Nota_F BETWEEN 20 AND 31 AND CAST(peso_ouvidoria AS int) = 0 THEN 'Vermelho'
WHEN nota_F = 20 AND CAST(peso_ouvidoria AS int) = 0 THEN 'Laranja'
WHEN nota_f = 25 AND CAST(rec_iguais AS int) >= 0 THEN 'Laranja'
WHEN nota_F = 35 THEN 'Laranja'
WHEN nota_f = 20 AND CAST(peso_ouvidoria AS int) = 0 THEN 'Vermelho'
WHEN nota_f >= 60 THEN 'Vermelho III'
WHEN nota_f >= 50 THEN 'Vermelho II'
WHEN nota_f > 35 THEN 'Vermelho'
END AS Cluster
FROM
bi_brrj_act.bt_brrj_requestqlik A
LEFT JOIN
Rec_iguais B ON B.cta_contrato = A.cta_contrato
LEFT JOIN
Rec_diversas C ON C.cta_contrato = A.cta_contrato
LEFT JOIN
inf_repetidas D ON D.cta_contrato = A.cta_contrato
LEFT JOIN
N3 E ON E.cta_contrato = A.cta_contrato
LEFT JOIN
OUVIDORIA F ON F.cta_contrato = A.cta_contrato
LEFT JOIN
JUDICIAL G ON G.CTA_CONTRATO = A.CTA_CONTRATO
LEFT JOIN
_variables H ON left(H.anomes,4) = A.ano
WHERE
A.motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
AND LEFT(A.data_criacao, 7) >= H.anomes_m1
AND A.tipocanal IN ('Humano')
AND flag_critico = 'true'
AND tipo_caso IN ('Solicitação', 'Reclamação', 'RSME')
AND Ordem IS NOT null);
select distinct
'Grupo B' as Grupo
,cluster as cluster_Cliente
,a.anomes
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,negocio
,D.regulada
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,G.Polo
,ind_serv_executado
,ind_encerra_ordem
,ind_def_tec_client
,ind_def_tec_empres
,ind_pendencia
,ind_efeito_tempo
,ind_movimenta_med
,ind_procedente
,Rec_iguais
,Rec_diversas
,inf_repetidas
,Ouvidoria
,Judicial
,Aneel
,peso_rec_iguais
,peso_rec_diversas
,peso_inf_repetidas
,peso_ouvidoria
,peso_judicial
,peso_aneel
,Nota_F
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
left join dp_brrj.bt_brrj_sucursal G on G.sucursal = A.sucursal
INNER join CRITICOS H on H.NUMERO_CASO = NRO_CASO
where ultima_etapa = true and CLUSTER_ORDEM = 'INICIATIVA CLIENTE' and Status_ordem <> 'FECHADA'
union all
select distinct
'Grupo A' as Grupo
,cluster as cluster_Cliente
,a.anomes
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,negocio
,D.regulada
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,conve
[TRUNCADO PARA BASE DE CONHECIMENTO]
```

### QUERY: Teste dados corte.sql
Data: 2024-04-19 13:19:36
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_corte; bi_brrj_act.bt_brrj_retorno
JOINs: 0
Sinais legados: SELECT*=True | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select distinct desc_motivo_corte, count(distinct numero_cliente) as qtd from bi_brrj_act.bt_brrj_corte
where aaaamm_execucao = '[VALOR_OMITIDO]' and corte_religa = 'C' and APS = 'S' and tramite IN ([LISTA_DE_VALORES_OMITIDA])
and num_ordem_serv_crt <> ''
group by 1 order by qtd desc
select * from bi_brrj_act.bt_brrj_retorno limit 10
select * from bi_brrj_act.bt_brrj_corte limit 10
```

### QUERY: Cortados e Negativados Dunning.sql
Data: 2024-04-19 09:45:08
Tópicos: COBRANCA
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_act.bt_brrj_retorno
JOINs: 0
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select
left(dt_acao,7) as anomes_acao,
segmentacao_cliente as segmento,
case
when fl_toi = 'X' then 'Sim' else 'Não'
end as toi,
cd_retorno,
desc_retorno,
case
when cd_acao IN ('NGT') then 'Negativado'
when cd_acao IN ('C_MD','R_PO','C_ND','C_PO','R_ND','C_RM','R_RM','R_MD','R_CH','C_CH') then 'Cortado'
end as Acao,
count(numero_ordem) as qtd
from bi_brrj_act.bt_brrj_retorno
where left(dt_acao,4) >= ('2023')
and cd_acao IN ('C_MD','R_PO','C_ND','C_PO','R_ND','C_RM','R_RM','R_MD','R_CH','C_CH','NGT')
and fl_produtivo = 'S'
and cd_status <> 'ST04'
group by
1,2,3,4,5,6
```

### QUERY: Persona Troca CE.sql
Data: 2024-04-16 10:45:34
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL CE, ENEL RJ
Objetos: global_brasil_ceara.bt_global_asset_brazil_ceara; bi_brce_act.bt_brce_requestqlik; dp_brrj.bt_de_para_motivos_requestqlik; bi_brce_bill.bt_brce_inadim; bi_brce_cus.bt_brce_relatorio_de_cadastro; bi_brce_bill.bt_brce_parcel_macro
JOINs: 10
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
drop table if exists Consulta_DACC_EBILL;
create temporary table Consulta_DACC_EBILL as (
select
ROW_NUMBER() OVER (PARTITION BY cdc_pod_id ORDER BY dte_validity_start_date DESC) as ordem,
fk_external_asset_id,
cdc_pod_id,
fk_local_customer_id,
sds_tension_group,
fln_top_large,
sds_aggregate_cluster_local,
lds_local_segment,
lds_asset_city,
dte_ebill_activation_date,
dte_ebill_deactivation_date,
mds_ebill_deactivation_reason,
fln_risk_areas,
fln_automatic_debit,
dte_validity_start_date
from global_brasil_ceara.bt_global_asset_brazil_ceara
);
drop table if exists EBILLING;
create temporary table EBILLING as (
select fk_external_asset_id
,cdc_pod_id
,case
when mds_ebill_deactivation_reason is null or mds_ebill_deactivation_reason = ''
then 1
else 0
end as Fatura_digital
,case
when fln_automatic_debit = true then 1 else 0
end as fln_automatic_debit
from Consulta_DACC_EBILL
where
ordem = '1');
DROP TABLE IF EXISTS Trocas;
CREATE TEMPORARY TABLE Trocas AS (
select distinct
cta_contrato,
A.numero_ponto_de_fornecimento as Pod,
k.tipo_conta,
DATE_PART('year', sysdate) - DATE_PART('year', birthdate__c) - CASE
WHEN DATE_PART('month', sysdate) < DATE_PART('month', birthdate__c) THEN 1
WHEN DATE_PART('month', sysdate) = DATE_PART('month', birthdate__c) AND DATE_PART('day', sysdate) < DATE_PART('day', birthdate__c) THEN 1
ELSE 0
END AS idade,
case
when idade isnull then 'Sem Cluster'
when idade <= 30 then 'Ate 30 anos'
when idade <= 60 then 'Entre 31 e 60 anos'
when idade > 60 then 'Acima de 60 anos'
else 'Sem Cluster'
end as Faixa_idade,
case
when qtd-1 < 0 then 0
when qtd-1 >= 1 then qtd-1
else 0
end as qtd_uc,
K.ne__status__c_asset as status_asset,
K.status_contract as Status_Contrato,
case
when K.status_contract = 'Inactivated' and K.ne__status__c_asset = '[VALOR_OMITIDO]' then 'Inativo'
when K.status_contract = 'Activated' and K.ne__status__c_asset = '[VALOR_OMITIDO]' then 'ASF'
when K.status_contract = 'Activated' and K.ne__status__c_asset = 'Active' then 'ACF'
when K.status_contract = 'Activated' and K.ne__status__c_asset = 'In Progress' then 'Em Desativação'
when K.status_contract = 'Inactivated' and K.ne__status__c_asset = 'In Progress' then 'Em Desativação'
end as estado_fornecimento,
'Normal' as tipologia,
case
when casos > 0 then 'Digital' else 'Analogico'
end as Perfil,
Fatura_digital,
fln_automatic_debit as Debito_automatico,
case
when divida_UC is not null and D.ultimo_vencimento < data_criacao then 1 else 0
end as Divida_UC1,
case
when divida_uc1 = 1 then divida_uc else 0
end
as valor_divida_uc,
case
when divida_CPF is not null and E.ultimo_vencimento < data_criacao then 1 else 0
end as Divida_CPF1,
case
when divida_cpf1 = 1 then divida_cpf else 0
end
as valor_divida_cpf,
case
when F.pod isnull then 0 else 1
end as Parcelamento,
numero_caso,
data_criacao as data_troca,
criacao_PN,
case
when cast(data_criacao as date) - 5 > cast(criacao_PN as date) then 1 else 0
end as PN_anterior_troca,
ano||mes as anomes,
id_conta_salesforce,
tipo_caso,
A.motivo,
B.motivo_tratado,
canal_oficial
FROM
bi_brce_act.bt_brce_requestqlik A
LEFT JOIN
dp_brrj.bt_de_para_motivos_requestqlik B ON B.chave = A.motivo || A.submotivo
LEFT JOIN
(select conta_contrato, min(data_vencimento) as ultimo_vencimento, sum(valor_deb_venc) as divida_UC from bi_brce_bill.bt_brce_inadim
where ano_mes_selecao >= (select max(ano_mes_selecao)-3 from bi_brce_bill.bt_brce_inadim)
group by 1
having divida_UC > 0) D on D.conta_contrato = A.cta_contrato
LEFT JOIN
(select accountid,min(data_vencimento) as ultimo_vencimento, sum(valor_deb_venc) as divida_CPF from bi_brce_bill.bt_brce_inadim A
left join bi_brce_cus.bt_brce_relatorio_de_cadastro B on B.parceiro = A.num_cliente
where ano_mes_selecao >= (select max(ano_mes_selecao)-3 from bi_brce_bill.bt_brce_inadim)
group by 1
having divida_CPF > 0) E on E.accountid = A.id_conta_salesforce
LEFT JOIN
(SELECT
CASE
WHEN instalacao IS NULL OR TRIM(instalacao) = '' THEN NULL
ELSE CAST(CAST(instalacao AS INT) AS VARCHAR)
END AS Pod,
desc_estado_parcel
FROM
bi_brce_bill.bt_brce_parcel_macro
WHERE
desc_estado_parcel IN ('Vigente', 'Solicitado')
AND pod IS NOT NULL) F on F.pod = A.numero_ponto_de_fornecimento
LEFT JOIN
(select accountid, min(left(createddate_account,19)) as criacao_PN from bi_brce_cus.bt_brce_relatorio_de_cadastro
group by 1) G on G.accountid = id_conta_salesforce
LEFT JOIN
(select accountid, count(accountcontract__C) as qtd from bi_brce_cus.bt_brce_relatorio_de_cadastro
group by 1) H on H.accountid = A.id_conta_salesforce
LEFT JOIN
(select numero_ponto_de_fornecimento, count(numero_caso) as casos from bi_brce_act.bt_brce_requestqlik
where ano >= '2023'
and canal_oficial IN ('Area Logada', 'Area Não Logada', 'App', 'Whatsapp')
and submotivo not IN ('[VALOR_OMITIDO]')
group by 1) I on I.numero_ponto_de_fornecimento = A.numero_ponto_de_fornecimento
LEFT JOIN
EBILLING J on J.cdc_pod_ID = A.numero_ponto_de_fornecimento
LEFT JOIN
(select distinct accountcontract__C, ne__status__c_asset, status_contract, tipo_conta, instalacao,birthdate__c from bi_brce_cus.bt_brce_relatorio_de_cadastro) K on K.instalacao = A.numero_ponto_de_fornecimento
where ano IN ('2024') AND mes IN ('1','2','3') and A.submotivo IN ([LISTA_DE_VALORES_OMITIDA])
and tipo_caso = 'Solicitação' and expurgado = 'false' and cta_contrato is not null);
SELECT
cta_contrato,
Pod,
tipo_conta,
idade,
faixa_idade,
qtd_uc,
status_asset,
Status_Contrato,
estado_fornecimento,
tipologia,
Perfil,
Fatura_digital,
Debito_automatico,
Divida_UC1 as divida_uc,
Divida_CPF1 as divida_cpf,
Parcelamento,
numero_caso,
data_troca,
criacao_PN,
PN_anterior_troca,
anomes,
id_conta_salesforce,
tipo_caso,
motivo,
motivo_tratado,
canal_oficial,
valor_divida_uc,
valor_divida_cpf
FROM
Trocas
WHERE
tipo_conta = 'B2C';
```

### QUERY: Persona Troca RJ.sql
Data: 2024-04-16 09:41:52
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: global_brasil_rio.bt_global_asset_brazil_rio; bi_brrj_act.bt_brrj_requestqlik; dp_brrj.bt_de_para_motivos_requestqlik; bi_brrj_bill.bt_brrj_inadim; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_bill.bt_brrj_parcel_macro
JOINs: 10
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
drop table if exists Consulta_DACC_EBILL;
create temporary table Consulta_DACC_EBILL as (
select
ROW_NUMBER() OVER (PARTITION BY cdc_pod_id ORDER BY dte_validity_start_date DESC) as ordem,
fk_external_asset_id,
cdc_pod_id,
fk_local_customer_id,
sds_tension_group,
fln_top_large,
sds_aggregate_cluster_local,
lds_local_segment,
lds_asset_city,
dte_ebill_activation_date,
dte_ebill_deactivation_date,
mds_ebill_deactivation_reason,
fln_risk_areas,
fln_automatic_debit,
dte_validity_start_date
from global_brasil_rio.bt_global_asset_brazil_rio
);
drop table if exists EBILLING;
create temporary table EBILLING as (
select fk_external_asset_id
,cdc_pod_id
,case
when mds_ebill_deactivation_reason is null or mds_ebill_deactivation_reason = ''
then 1
else 0
end as Fatura_digital
,case
when fln_automatic_debit = true then 1 else 0
end as fln_automatic_debit
from Consulta_DACC_EBILL
where
ordem = '1');
DROP TABLE IF exists status_UC;
DROP TABLE IF EXISTS Trocas;
CREATE TEMPORARY TABLE Trocas AS (
select distinct
cta_contrato,
A.numero_ponto_de_fornecimento as Pod,
k.tipo_conta,
DATE_PART('year', sysdate) - DATE_PART('year', birthdate__c) - CASE
WHEN DATE_PART('month', sysdate) < DATE_PART('month', birthdate__c) THEN 1
WHEN DATE_PART('month', sysdate) = DATE_PART('month', birthdate__c) AND DATE_PART('day', sysdate) < DATE_PART('day', birthdate__c) THEN 1
ELSE 0
END AS idade,
case
when idade isnull then 'Sem Cluster'
when idade <= 30 then 'Ate 30 anos'
when idade <= 60 then 'Entre 31 e 60 anos'
when idade > 60 then 'Acima de 60 anos'
else 'Sem Cluster'
end as Faixa_idade,
case
when qtd-1 < 0 then 0
when qtd-1 >= 1 then qtd-1
else 0
end as qtd_uc,
K.ne__status__c_asset as status_asset,
K.status_contract as Status_Contrato,
case
when K.status_contract = 'Inactivated' and K.ne__status__c_asset = '[VALOR_OMITIDO]' then 'Inativo'
when K.status_contract = 'Activated' and K.ne__status__c_asset = '[VALOR_OMITIDO]' then 'ASF'
when K.status_contract = 'Activated' and K.ne__status__c_asset = 'Active' then 'ACF'
when K.status_contract = 'Activated' and K.ne__status__c_asset = 'In Progress' then 'Em Desativação'
when K.status_contract = 'Inactivated' and K.ne__status__c_asset = 'In Progress' then 'Em Desativação'
end as estado_fornecimento,
'Normal' as tipologia,
case
when casos > 0 then 'Digital' else 'Analogico'
end as Perfil,
Fatura_digital,
fln_automatic_debit as Debito_automatico,
case
when divida_UC is not null and D.ultimo_vencimento < data_criacao then 1 else 0
end as Divida_UC1,
case
when divida_uc1 = 1 then divida_uc else 0
end
as valor_divida_uc,
case
when divida_CPF is not null and E.ultimo_vencimento < data_criacao then 1 else 0
end as Divida_CPF1,
case
when divida_cpf1 = 1 then divida_cpf else 0
end
as valor_divida_cpf,
case
when F.pod isnull then 0 else 1
end as Parcelamento,
numero_caso,
data_criacao as data_troca,
criacao_PN,
case
when cast(data_criacao as date) - 5 > cast(criacao_PN as date) then 1 else 0
end as PN_anterior_troca,
ano||mes as anomes,
id_conta_salesforce,
tipo_caso,
A.motivo,
B.motivo_tratado,
canal_oficial
FROM
bi_brrj_act.bt_brrj_requestqlik A
LEFT JOIN
dp_brrj.bt_de_para_motivos_requestqlik B ON B.chave = A.motivo || A.submotivo
LEFT JOIN
(select conta_contrato, min(data_vencimento) as ultimo_vencimento, sum(valor_deb_venc) as divida_UC from bi_brrj_bill.bt_brrj_inadim
where ano_mes_selecao >= (select max(ano_mes_selecao)-3 from bi_brrj_bill.bt_brrj_inadim)
group by 1
having divida_UC > 0) D on D.conta_contrato = A.cta_contrato
LEFT JOIN
(select accountid,min(data_vencimento) as ultimo_vencimento, sum(valor_deb_venc) as divida_CPF from bi_brrj_bill.bt_brrj_inadim A
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro B on B.parceiro = A.num_cliente
where ano_mes_selecao >= (select max(ano_mes_selecao)-3 from bi_brrj_bill.bt_brrj_inadim)
group by 1
having divida_CPF > 0) E on E.accountid = A.id_conta_salesforce
LEFT JOIN
(SELECT
CASE
WHEN instalacao IS NULL OR TRIM(instalacao) = '' THEN NULL
ELSE CAST(CAST(instalacao AS INT) AS VARCHAR)
END AS Pod,
desc_estado_parcel
FROM
bi_brrj_bill.bt_brrj_parcel_macro
WHERE
desc_estado_parcel IN ('Vigente', 'Solicitado')
AND pod IS NOT NULL) F on F.pod = A.numero_ponto_de_fornecimento
LEFT JOIN
(select accountid, min(left(createddate_account,19)) as criacao_PN from bi_brrj_cus.bt_brrj_relatorio_de_cadastro
group by 1) G on G.accountid = id_conta_salesforce
LEFT JOIN
(select accountid, count(accountcontract__C) as qtd from bi_brrj_cus.bt_brrj_relatorio_de_cadastro
group by 1) H on H.accountid = A.id_conta_salesforce
LEFT JOIN
(select numero_ponto_de_fornecimento, count(numero_caso) as casos from bi_brrj_act.bt_brrj_requestqlik
where ano >= '2023'
and canal_oficial IN ('Area Logada', 'Area Não Logada', 'App', 'Whatsapp')
and submotivo not IN ('[VALOR_OMITIDO]')
group by 1) I on I.numero_ponto_de_fornecimento = A.numero_ponto_de_fornecimento
LEFT JOIN
EBILLING J on J.cdc_pod_ID = A.numero_ponto_de_fornecimento
LEFT JOIN
(select distinct accountcontract__C, ne__status__c_asset, status_contract, tipo_conta, instalacao,birthdate__c from bi_brrj_cus.bt_brrj_relatorio_de_cadastro) K on K.instalacao = A.numero_ponto_de_fornecimento
where ano IN ('2024') AND mes IN ('1','2','3') and A.submotivo IN ([LISTA_DE_VALORES_OMITIDA])
and tipo_caso = 'Solicitação' and expurgado = 'false' and cta_contrato is not null);
SELECT
cta_contrato,
Pod,
tipo_conta,
idade,
faixa_idade,
qtd_uc,
status_asset,
Status_Contrato,
estado_fornecimento,
tipologia,
Perfil,
Fatura_digital,
Debito_automatico,
Divida_UC1 as divida_uc,
Divida_CPF1 as divida_cpf,
Parcelamento,
numero_caso,
data_troca,
criacao_PN,
PN_anterior_troca,
anomes,
id_conta_salesforce,
tipo_caso,
motivo,
motivo_tratado,
canal_oficial,
valor_divida_uc,
valor_divida_cpf
FROM
Trocas
WHERE
tipo_conta = 'B2C';
```

### QUERY: SEFAZ 3.0 Conferencia.sql
Data: 2024-04-07 22:55:06
Tópicos: SEFAZ
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_bill.bt_brrj_cip_faturado; bi_brrj_bill.bt_brrj_icg_compliance; bi_brrj_coll.bt_brrj_faturamento; bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_cus.bt_brrj_conta_contrato; dp_brrj.tb_capilaridade_credit_recovery; global_brasil_rio.bt_global_billing_concepts_brazil_rio
JOINs: 8
Sinais legados: SELECT*=True | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select referencia, sum(valor_faturado) as faturamento from (select distinct
D.Fatura_Coletiva
,cast(F.consumo_faturado as decimal(17,2)) Consumo
,C.identitynumber__c Documento
,C.postal_code__c CEP
,cast(H.vad_concept_issued_amount_due_date_tax as decimal(17,2)) as VALOR_CONSUMO
,cast(J.vad_concept_issued_amount_due_date_tax as decimal(17,2)) as VALOR_EVENTUAL
,cast(F.juros_fatura as decimal(17,2)) as Juros
,cast(F.multa_fatura as decimal(17,2)) as Multa
,icg_referencia referencia
,left(F.data_vencimento_fatura,10) as Data_Vencimento
,cast(F.valor_da_fatura as decimal(17,2)) Valor_Faturado
,'NULL' as COD_BARRAS
,'NULL' as COD_BARRAS_AGRUPAMENTO
,F.grupo as Tensao
,case
WHEN
F.tipo_documento_faturamento = 'REFATURADO' then (select left(F2.DATA_FATURAMENTO,10)
from bi_brrj_bill.bt_brrj_cip_faturado F2
where F.CONTA_CONTRATO = F2.conta_contrato
and F2.referencia_faturamento = F.referencia_faturamento and F2.tipo_documento_faturamento = 'FATURADO'
order by F2.data_faturamento desc limit 1)
else
left(F.data_faturamento,10) end as Data_Faturamento
,G.orgao_controlador
,icg_numero_Cliente Conta_Contrato
,F.numero_fatura
,nr_medidor
,C.coordinatex__c
,C.coordinatey__c
,C.distributionaddress__c Endereço
,C.neighbourhood__c Bairro
,icg_municipio Municipio
,icg_tipo_fatura tipo_fatura
,case
when tipo_fatura = 'FA' then 'Fatura Normal'
when tipo_fatura = 'EP' then 'Fatura Cancelada'
when tipo_fatura = 'RF' then '[VALOR_OMITIDO]'
end
as Legenda_Tipo_Fatura
,cast(icg_cons_activo_livre as decimal(17,2)) Consumo_Livre
,case when icg_cons_activo_cativo <> 0 then 'Regulado' else 'Livre' end Mercado
,case when C.mov_out = '31/12/9999' then 'Ativo' else 'Inativo' end Status
,cast(B.valor_icms as decimal(17,2)) as Icms
,cast(B.valor_pis as decimal(17,2)) as Pis
,cast(B.valor_cofins as decimal(17,2)) as cofins
,cast(B.valor_toi as decimal(17,2)) as Valor_Toi
,cast(retencao_ir*-1 as decimal(17,2)) as retencao_IR
from bi_brrj_bill.bt_brrj_icg_compliance A
left join bi_brrj_coll.bt_brrj_faturamento B on B.corr_facturacion = A.ICG_CORR_FACTURACION and ORIGEM = 'FATURADO'
left join bi_brrj_cus.bt_brrj_relatorio_de_cadastro C on C.accountcontract__c = icg_numero_Cliente
left join bi_brrj_cus.bt_brrj_conta_contrato D on D.conta_contrato = A.icg_numero_Cliente
left join (select *
from bi_brrj_bill.bt_brrj_cip_faturado A
where not exists (select 1 from bi_brrj_bill.bt_brrj_cip_faturado B where tipo_documento_faturamento = 'ESTONO PLENO' and B.numero_fatura = A.numero_fatura)) F on numero_fatura = A.icg_corr_facturacion
left join dp_brrj.tb_capilaridade_credit_recovery G on G.numero_cliente = icg_numero_Cliente
left join (select
left(fk_bill_id,16) as FATURA,
SUM(vad_concept_issued_amount_due_date_tax) as vad_concept_issued_amount_due_date_tax
from global_brasil_rio.bt_global_billing_concepts_brazil_rio H
where lds_local_concept IN ('IMPOSTOS','ENERGIA ATIVA FORNECIDA TE','TUSD') group by FATURA) H on left(H.FATURA,16) = A.icg_corr_facturacion
left join (select
left(fk_bill_id,16) as FATURA,
SUM(vad_concept_issued_amount_due_date_tax) as vad_concept_issued_amount_due_date_tax
from global_brasil_rio.bt_global_billing_concepts_brazil_rio
where lds_local_concept IN ('ADICIONAL BAND.%') group by FATURA) J on left(J.FATURA,16) = A.icg_corr_facturacion
left join (select left(fk_bill_id,16) as fatura,sum(vad_concept_billed_amount_no_tax) as Retencao_IR from global_brasil_rio.bt_global_billing_concepts_brazil_rio
where lds_local_concept_id IN ('WHTAX','[VALOR_OMITIDO]') group by 1) K on K.Fatura = A.icg_corr_facturacion
where F.numero_fatura is not null and icg_referencia >= '2023/04' and icg_numero_cliente IN ([LISTA_DE_VALORES_OMITIDA]))
group by 1
order by referencia desc
```

### QUERY: Dash de Ordens por data de encerramento.sql
Data: 2024-03-27 14:05:06
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos; bi_brrj_cus.bt_brrj_grandes_ordem_servico
JOINs: 8
Sinais legados: SELECT*=False | DISTINCT=True | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select
'RJ' as Distribuidora
,'Grupo B' as Grupo_Tensao
,ano as ano_abertura
,mes as mes_abertura
,anomes as anomes_abertura
,ano_encerramento
,mes_encerramento
,anomes_encerramento
,des_servico
,descricao_etapa
,AREA_RESPONSAVEL
,Responsavel
,area_responsavel_etapa
,responsavel_etapa
,negocio
,regulada
,artigo
,status_prazo
,farol_prazo
,Controle_Prazo
,status_ordem
,count(numero_ordem) as qtd
from
(select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,left(A.data_estado,4) as ano_encerramento
,substring(A.data_estado,6,2) as mes_encerramento
,ano_encerramento||mes_encerramento as anomes_encerramento
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,D.area_responsavel_etapa
,D.responsavel_etapa
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
where ultima_etapa = true and cluster_ordem = 'INICIATIVA CLIENTE' and status_prazo is not null and ano is not null)
group by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
union all
select
'RJ' as Distribuidora
,'Grupo A' as Grupo_Tensao
,ano as ano_abertura
,mes as mes_abertura
,anomes as anomes_abertura
,ano_encerramento
,mes_encerramento
,anomes_encerramento
,des_servico
,descricao_etapa
,AREA_RESPONSAVEL
,Responsavel
,area_responsavel_etapa
,responsavel_etapa
,negocio
,regulada
,artigo
,status_prazo
,farol_prazo
,Controle_Prazo
,status_ordem
,count(numero_ordem) as qtd
from
(select distinct
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,left(A.data_estado,4) as ano_encerramento
,substring(A.data_estado,6,2) as mes_encerramento
,ano_encerramento||mes_encerramento as anomes_encerramento
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then '-'
when trim(A.numero_ordem_relac) =' ' then '-'
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,case when numero_cliente isnull then 0
else numero_cliente end as Numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,D.area_responsavel_etapa
,D.responsavel_etapa
,negocio
,D.regulada
,D.artigo
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,case
when left(sysdate,10) = left(data_fim_regulada,10) and Status_ordem = 'ABERTA' then 'Vence Hoje'
when sysdate > data_fim_regulada and Status_ordem = 'ABERTA' then 'Vencida'
when data_fim_regulada <= sysdate+7 AND Status_ordem = 'ABERTA' then 'Vence na Semana'
when Status_ordem <> 'ABERTA' then '-'
else 'A Vencer'
end as Controle_Prazo
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when situacao = 'N' then 'Dentro do Prazo'
when situacao = '' then 'Dentro do Prazo'
when situacao = 'X' then 'Alerta'
when situacao = 'A' then 'Fora do Prazo'
end as Status_Prazo
,case
when situacao IN ('N', 'X', '') then 'DP'
when situacao = 'A' then 'FP'
end as Farol_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
,last_update as Atualizacao
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
sucursal,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_grandes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_grandes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
where ultima_etapa = true and cluster_ordem = 'INICIATIVA CLIENTE' and status_prazo is not null and ano is not null)
group by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
```

### QUERY: Analise rec troca religa.sql
Data: 2024-03-26 11:06:04
Tópicos: ORDENS_SERVICOS
Empresas detectadas: NAO_IDENTIFICADA
Objetos: 
JOINs: 0
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql

```

### QUERY: Clientes que acessam digital e não possuem eBill.sql
Data: 2024-03-22 19:34:52
Tópicos: CADASTRO_CLIENTE, CANAIS_ATENDIMENTO
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_act.bt_brrj_requestqlik; global_brasil_rio.bt_global_asset_brazil_rio
JOINs: 2
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
DROP TABLE IF EXISTS Ativos;
CREATE TEMPORARY TABLE ativos as
(select accountcontract__c, tipo_conta from bi_brrj_cus.bt_brrj_relatorio_de_cadastro
where clienteativo = '1');
DROP TABLE IF EXISTS Lojas;
CREATE TEMPORARY TABLE lojas AS (
SELECT cta_contrato,canal_oficial, COUNT(numero_caso) as qtd
FROM bi_brrj_act.bt_brrj_requestqlik A
WHERE expurgado = 'false' and canal_oficial IN ('APP', 'Area Logada', 'Area Nao Logada')
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
GROUP BY 1,2
);
DROP TABLE IF EXISTS Nvisitas;
CREATE TEMPORARY TABLE Nvisitas as (
SELECT a.accountcontract__c
FROM ativos a
LEFT JOIN lojas l ON a.accountcontract__c = l.cta_contrato
WHERE l.cta_contrato IS null);
drop table if exists Consulta_DACC_EBILL;
create temporary table Consulta_DACC_EBILL as (
select
ROW_NUMBER() OVER (PARTITION BY cdc_pod_id ORDER BY dte_validity_start_date DESC) as ordem,
fk_external_asset_id,
cdc_pod_id,
fk_local_customer_id,
sds_tension_group,
fln_top_large,
sds_aggregate_cluster_local,
lds_local_segment,
lds_asset_city,
dte_ebill_activation_date,
dte_ebill_deactivation_date,
mds_ebill_deactivation_reason,
fln_risk_areas,
fln_automatic_debit,
dte_validity_start_date,
sds_asset_status_local
from global_brasil_rio.bt_global_asset_brazil_rio
);
drop table if exists EBILL;
create temporary table EBILL as (
select fk_external_asset_id
,cdc_pod_id
,fk_local_customer_id
,sds_asset_status_local
,sds_tension_group
,sds_aggregate_cluster_local
,lds_local_segment
,lds_asset_city
,dte_ebill_activation_date
,dte_ebill_deactivation_date
,mds_ebill_deactivation_reason
,case
when dte_ebill_activation_date isnull then 'N'
when mds_ebill_deactivation_reason is null or mds_ebill_deactivation_reason = '' and dte_ebill_activation_date is not null
then 'S'
else 'N'
end as Fatura_digital
,fln_risk_areas
,fln_automatic_debit
,fln_top_large
,dte_validity_start_date
from Consulta_DACC_EBILL
where
ordem = '1');
select fatura_digital, mds_ebill_deactivation_reason, count(fatura_digital) as qtd from (select accountcontract__c
,mds_ebill_deactivation_reason
,B.Fatura_digital from Nvisitas A
left join EBILL B on B.fk_external_asset_id = A.accountcontract__c)
group by
1,2;
```

### QUERY: Clientes ativos que nunca foram em loja.sql
Data: 2024-03-20 20:19:28
Tópicos: CADASTRO_CLIENTE, CANAIS_ATENDIMENTO
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_relatorio_de_cadastro; bi_brrj_act.bt_brrj_requestqlik
JOINs: 1
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=True | UPDATE=False | DELETE=False | INSERT=False | DROP=True
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
DROP TABLE IF EXISTS Ativos;
CREATE TEMPORARY TABLE ativos as
(select accountcontract__c, tipo_conta from bi_brrj_cus.bt_brrj_relatorio_de_cadastro
where clienteativo = '1');
DROP TABLE IF EXISTS Lojas;
CREATE TEMPORARY TABLE lojas AS (
SELECT cta_contrato,canal_oficial, COUNT(numero_caso) as qtd
FROM bi_brrj_act.bt_brrj_requestqlik A
WHERE expurgado = 'false'
AND motivo NOT IN ('MOT001-Sol Registro Aviso Emergencial')
GROUP BY 1,2
);
SELECT tipo_conta, COUNT(DISTINCT a.accountcontract__c) AS qtd
FROM ativos a
LEFT JOIN lojas l ON a.accountcontract__c = l.cta_contrato
WHERE l.cta_contrato IS null
group by 1;
```

### QUERY: Religação.sql
Data: 2024-03-07 10:54:02
Tópicos: ORDENS_SERVICOS
Empresas detectadas: ENEL RJ
Objetos: bi_brrj_cus.bt_brrj_clientes_ordem_servico; dp_brrj.tb_aux_depara_estado_ordens; dp_brrj.tb_aux_depara_tipo_servicos
JOINs: 4
Sinais legados: SELECT*=False | DISTINCT=False | WITH/CTE=False | CREATE=False | UPDATE=False | DELETE=False | INSERT=False | DROP=False
Uso na base: Referência histórica de lógica/negócio; validar no catálogo e aplicar regras atuais do agente antes de reutilizar.
SQL de referência normalizada:
```sql
select
corr_visita
,ultima_etapa
,ano
,mes
,anomes
,A.numero_ordem
,case
when trim(A.numero_ordem_relac) ='' then null
when trim(A.numero_ordem_relac) =' ' then null
else A.numero_ordem_relac end as numero_ordem_relac
,D.cluster_ordem
,numero_cliente
,nro_caso as numero_caso
,A.Tipo_Ordem
,A.cod_servico
,A.des_servico
,cod_etapa
,descricao_etapa
,cod_retorno
,descricao_retorno
,case
when A.cod_retorno IN ('0005',
'0008',
'0009',
'0010',
'0015',
'0016',
'0020',
'0033',
'0045',
'0058',
'0059',
'0061') then 'Improdutivo' else 'Produtivo'
end as Status_Retorno
,D.AREA_RESPONSAVEL
,D.Responsavel
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.ESTADO_DA_ORDEM else A.ESTADO end AS ESTADO
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.DESCRICAO_ORDEM else descricao end as Estado_Ordem
,case when A.numero_ordem_relac > 0 and descricao = 'ABERTA' or descricao = 'FECHADA' then E.STATUS_DA_ORDEM else c.status_ordem end as status_ordem
,convert(varchar(19),Data_ingresso) as Data_abertura
,convert(varchar(19),data_fim_regulada) as data_fim_regulada
,left(data_visita,10) as Data_visita
,case when A.numero_ordem_relac > 0 and E.data_exec_visita <> '' then E.data_estado
when A.data_exec_visita <> '' then left(A.data_exec_visita,10)||' '||convert(varchar(8),right(A.hora_exec_visita,8))
else null end as data_execucao_visita
,case
when status_ordem = 'CANCELADA' then '[VALOR_OMITIDO]'
when Status_ordem = 'SUSPENSA' then 'Ordem Suspensa'
when situacao IN ('N', 'X') then 'DP'
when situacao = 'A' then 'FP'
end as Status_Prazo
,convert(varchar(19),A.Data_estado) as data_estado
,observacoes
,observacao_exe
,upper(left(rol_ingresso,12)) as rol_ingresso
,upper(left(rol_visita,12)) as rol_visita
,tempo_max_servico
,case
when descricao_retorno is null or descricao_retorno = '' then '[VALOR_OMITIDO]'
when ind_serv_executado = 'S' then '[VALOR_OMITIDO]'
when ind_encerra_ordem = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_client = 'S' then '[VALOR_OMITIDO]'
when ind_def_tec_empres = 'S' then '[VALOR_OMITIDO]'
when ind_pendencia = 'S' then 'Suspensa'
else '[VALOR_OMITIDO]'
end as acao_retorno
,case
when ind_efeito_tempo = 'P' then 'Para Tempo'
when ind_efeito_tempo = 'N' then 'Nao Afeta'
when ind_efeito_tempo = 'Z' then 'Zera Tempo'
else ''
end as efeito_tempo_descricao
from
(
select corr_visita,
data_estado,
case
when ROW_NUMBER() OVER (PARTITION BY NUMERO_ORDEM ORDER BY corr_visita DESC) = 1
then true
else false
end as Ultima_Etapa,
left(data_ingresso,4) as Ano,
substring(data_ingresso,6,2) as mes,
ano||mes as anomes,
data_ingresso,
estado,
etapa,
nro_gac,
numero_cliente,
numero_ordem,
numero_ordem_relac,
observacao_exe,
observacoes,
rol_ingresso,
rol_visita,
tipo_ordem,
tipo_servico,
cod_retorno,
CASE
WHEN data_visita <> ''
THEN data_visita
ELSE NULL
END AS data_visita,
CASE
WHEN data_exec_visita <> ''
THEN data_exec_visita||' '||convert(varchar(8),right(hora_exec_visita,8))
ELSE NULL
END AS data_exec_visita,
hora_exec_visita,
case
when data_fim_regulada <> ''
then data_fim_regulada
else left(convert(varchar(19),DATE_TRUNC('day',cast(tempo_max_servico / 24 AS INT) + cast(data_ingresso AS DATE))),10)||' '||convert(varchar(8),right(data_ingresso,8))
end as data_fim_regulada,
situacao,
dias,
horas,
minutos,
segundos,
tipo,
cod_servico,
des_servico,
ind_tempo,
tempo_max_servico,
cod_etapa,
descricao_etapa,
descricao_retorno,
ind_serv_executado,
ind_encerra_ordem,
ind_def_tec_client,
ind_def_tec_empres,
ind_pendencia,
ind_efeito_tempo,
ind_movimenta_med,
ind_procedente,
nro_caso,
last_update,
sysdate as data_referencia
from Bi_brrj_cus.bt_brrj_clientes_ordem_servico
) A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS C on C.ESTADO = A.ESTADO
left join DP_BRRJ.TB_AUX_DEPARA_TIPO_SERVICOS D on D.CHAVE = A.tipo_ordem||A.cod_servico||RTRIM(A.des_servico)
left join (select numero_ordem, data_estado, numero_ordem_relac, case when data_exec_visita <> '' then data_exec_visita else null end as data_exec_visita, hora_exec_visita,
B.DESCRICAO as DESCRICAO_ORDEM, B.status_ordem as STATUS_DA_ORDEM, B.ESTADO as ESTADO_DA_ORDEM from Bi_brrj_cus.bt_brrj_clientes_ordem_servico A
left join DP_BRRJ.TB_AUX_DEPARA_ESTADO_ORDENS B on B.ESTADO = A.ESTADO) E on E.NUMERO_ORDEM = A.numero_ordem_relac
where A.tipo_servico IN ('JJQ',
'R02',
'RAP',
'REA',
'REL',
'REU',
'RJU',
'VET',
'RME',
'RCR') and A.numero_ordem IN ([LISTA_DE_VALORES_OMITIDA])
order by
a.numero_ordem
,corr_visita ASC
```