VirtualAge-ETL-SQL-padrao » Histórico » Versão 16

« Anterior - Versão 16/50 (diff) - Próximo » - Versão atual
paulo, 09/12/2022 08:34 h

Virtual Age API V1 - ETL¶

Índice

GEOvendas
Força de Vendas
Business to Business (B2B)
CRM

GEO - Representantes¶

select
        '1' as codEmpresa
        , rep.cdpessoa as codRepres 
        , rep.nmpessoa as nome
        , regexp_replace(pt.nrtelefone, '[^0-9]', '', 'g') as telefone
        , pm.dsemail as email    
        , pe.nmlogradouro as endereco
        , pe.dsbairro as bairro
        , pe.cdcep as CEP
        , pe.NMMUNICIPIO as Cidade
        , pe.ufsigla as UF
        , coalesce(im.codibge,'1200351') as codIBGE --definir um codIbge padrão com o cliente para quando for NULL
        , case when (rep.ininativo = 'TRUE') then 0 else 1 end as situacao
        , null as CodRepresAgrupador
        , null as telefone2
        , null as telefone3
        , null as codigoAdministrador
        , null as cnpj
        , null as codAgrupadorEscritorio
        , null as flagAgrupadorMaster
        , null as percComissa
        , null as codigoPessoaFisica
        , rep.nmpessoa as nomePessoaFisica
        , null as senhaAPi
        , null as Origem
        , rep.nmpessoa as fantasia
        , null as empresaIntegracaoPedido
        , null as marcas
        , null as flagPermiteVendaPorFamilia    
from    representantevaentity rep
left join 
        pessoaenderecovaentity pe 
on pe.cdpessoa = rep.cdpessoa 
left join 
        vpesconsultavaentity vpc 
on vpc.cdpessoa = rep.cdpessoa
left join 
        etlcidade im 
on UPPER(im.siglaUF) = UPPER(pe.ufsigla) 
and (
       (REPLACE(REPLACE(UPPER(im.nome), ' ', ''), '''', '') = REPLACE(REPLACE(UPPER(pe.nmmunicipio), ' ', ''), '''', '')) 
    OR (REPLACE(REPLACE(UPPER(im.nome), ' ', ''), '''', 'O') = REPLACE(REPLACE(UPPER(pe.nmmunicipio), ' ', ''), '''', ''))
        )
left join 
        pessoatelefonevaentity pt 
on pt.cdpessoa = rep.cdpessoa and pt.inpadrao = 'TRUE'
left join 
        pessoaemailvaentity pm 
on pm.cdpessoa = rep.cdpessoa and pm.inpadrao = 'TRUE'

GEO - Representante Gerente¶

Documentação: Representante Gerente

Adicionar SQL

GEO - Municípios do Representante¶

Documentação: Municípios do Representante

Adicionar SQL

GEO - Municípios do ERP¶

Documentação: Municípios do ERP

Adicionar o sql

GEO - Clientes e Prospects¶

Documentação: Clientes e Prospects

select 
        '1' codEmpresa
        , COALESCE(im.codibge, '4127403') as codIBGE --CONFIRMAR COM O CLIENTE UM IBGE PADRÃO
        , p.cdpessoa as codCliente
        , 1 as tipo /* 1 - Cliente, 2 - Prospect*/
        , p.nmpessoa as nome
        , (case when (p.ininativo = 'TRUE') then 0 else 1 end) as situacao
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1), '0') as codRepres1
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1 offset 1), '0') as codRepres2
        , coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1 offset 2), '0') as codRepres3
        , regexp_replace(pt.nrtelefone, '[^0-9]', '', 'g') as telefone
        , pm.dsemail as email
        , pe.nmlogradouro as endereco
        , pe.dsbairro as bairro
        , pe.cdcep as CEP
        , (case when v.dataCadastro is null then p.dtcadastro else v.dataCadastro end) as dataCadastro
        , coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj) as cnpjCpf
        , pmc.dsemail as emailCobranca
        , pe.dsbairro as nomeDistritoBairro
        , pts.nrtelefone as celular
        , pj.nrinscestl as inscricaoEstadual
        , pe.nrlogradouro as enderecoNumero
        , pe.dscomplemento as complemento
        , null as IndicePontualidade
        , null as telefone2
        , null as telefoneCobranca
        , case when vpc.cdcoligador = '0' then null else vpc.cdcoligador end as grupoCliente
        , case when vpc.cdcoligador = '0' then null else vpc.cdcoligador end as codClientePrincipal
        , null as situacaoEdt
        , (select pg.nmpessoa from pessoavaentity pg where pg.cdempresa = vpc.cdempresacad and pg.cdpessoa = vpc.cdcoligador) as nomeGrupoCliente
        , (case when coalesce(titulos_em_atraso,0) > 0 then 1 else 0 end) as flagInadimplente
        , (pe.cdmunicipio || ' - ' || pe.nmmunicipio) as cidade
        , pe.ufsigla as uf
        , pj.nmfantasia as nomeFantasia
        , null as ramoAtividade
        , null as dataPrimeiraCompra
        , null as dataUltimaCompra
        , null as maiorCompra
        , null as maiorAcumulado
        , null as codClienteERP
        , null as codRamoAtividade
        , null as limiteCredito
        , null as ultimoFaturamento
        , null as dataReativacao
        , null as codSitCreditoERP
        , null as descSitCreditoERP
        , null as motivoInativo
        , null as diasVencimentoTitulo
        , null as socios
        , null as cidadeGrupoCliente
        , null as classificacaoERP
        , null as instagram
        , null as facebook
        , null as marcasLiberadas
        , pmc.dsemail as emailNfe
FROM    pessoavaentity p
inner join 
        pessoaenderecovaentity pe 
on      pe.cdempresa = p.cdempresa and pe.cdpessoa = p.cdpessoa 
inner join 
        vpesconsultavaentity vpc 
on      vpc.cdpessoa = p.cdpessoa     
LEFT join 
        ETLCidade im 
on      im.codCidade = pe.cdMunicipio
LEFT join 
        pessoatelefonevaentity pt 
on      pt.cdempresa = p.cdempresa 
AND     pt.cdpessoa = p.cdpessoa 
AND     pt.id = (       SELECT  MIN(pt2.id) 
                        FROM    pessoatelefonevaentity pt2 
                        WHERE   pt.cdempresa = pt2.cdempresa 
                        AND     pt.cdpessoa = pt2.cdpessoa
                )
left join 
        pessoaemailvaentity pm 
on      pm.cdempresa = p.cdempresa 
and     pm.cdpessoa = p.cdpessoa 
and     pm.inpadrao = 'TRUE'
left join 
        pessoaemailvaentity pmc 
on      pmc.cdempresa = p.cdempresa 
and     pmc.cdpessoa = p.cdpessoa 
and     pmc.cdtipoemail = 3 -- CONFIRMAR QUAL TIPO DE E-MAIL É O EMAIL DE NFE/COBRANCA
left join 
        pessoatelefonevaentity pts 
on      pts.cdempresa = p.cdempresa 
and     pts.cdpessoa = p.cdpessoa 
and     pts.cdtipofone = 4 -- CONFIRMAR COM O CLIENTE QUAL É O CÓDIGO DO TELEFONE CELULAR
left join 
        pessoajuridicavaentity pj 
on      pj.cdempresa = p.cdempresa 
and     pj.cdpessoa = p.cdpessoa     
left join 
        (select codcliente::int as codcliente
                , codempresa::int as codempresa
                , count(*) as titulos_em_atraso 
        from etltitulo 
        where situacao = '1' 
        and (dataVencimento::date + 28) < (current_date) -- CONFIRMAR COM O CLIENTE QUANTOS DIAS CONSIDERA INADIMPLENTE
        group by codcliente
                , codempresa having sum(valor) > 3000) as inad -- CONFIRMAR COM O CLIENTE SE INADIMPLENTE É ACIMA DE 3 MIL REAIS DEVENDO
on      inad.codcliente = p.cdpessoa 
and     inad.codempresa = p.cdempresa
left join 
        (SELECT v.cdcliente, MIN(v.dtpedido) as dataCadastro FROM vendavaentity v GROUP BY v.cdcliente) as v 
on      v.cdcliente = p.cdpessoa 
where   vpc.incliente = 'TRUE'
AND     coalesce((select rep.cdrepresentant from pessoareprclientevaentity rep where rep.cdempresa = p.cdempresa and rep.cdpessoa = p.cdpessoa limit 1), '0') is not null
and     coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj) is not null
AND     pe.cdcep <> '99999999'
AND     length(coalesce(
                (select pf.nrcpf as cpfCnpj from pessoafisicavaentity pf where pf.cdempresa = p.cdempresa and pf.cdpessoa = p.cdpessoa
                 union
                 select pj.nrcnpj as cpfCnpj from pessoajuridicavaentity pj where pj.cdempresa = p.cdempresa and pj.cdpessoa = p.cdpessoa
                ), p.nrcpfcnpj)) >= 14 -- CONFIRMAR COM O CLIENTE SE É PARA PUXAR SÓ CNPJ

GEO - Cliente Dos Representantes¶

Documentação: Cliente Dos Representantes

select '1' codempresa,  
       cdpessoa,
       cdrepresentant 
from pessoareprclientevaentity

GEO - Marcas do Cliente¶

Documentação: Marcas do Cliente

Adicionar SQL

GEO - Coleções¶

Documentação: Coleções

select 
        '1' as codEmpresa
        , cdclassificacao as codColecao
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE COLEÇÃO COM O CLIENTE
order by dsclassificacao

GEO - Linhas de Produto¶

Documentação: Linhas de Produto

select 
        '1' as codEmpresa
        , cdclassificacao as codLinha
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE LINHA COM O CLIENTE
order by dsclassificacao

GEO - Marcas¶

Documentação: Marcas

select 
        '1' as codEmpresa
        , cdclassificacao as codMarca
        , dsclassificacao as nome
FROM    produtoclassificacaovaentity
where   cdtipoclass = 300 -- CONFIRMAR CÓDIGO DE MARCA COM O CLIENTE
order by dsclassificacao

GEO - Vendas¶

Documentação: Vendas

select
        '1' as codEmpresa
        , v.colecao as codColecao
        , v.dtpedido as dataReferencia
        , v.codlinha as codLinha
        , v.codRepres as codRepres
        , v.codcliente as codCliente
        , v.codibge as codIBGE
        , v.uf as UF
        , sum(v.valorBruto) as valorBruto
        , sum(v.valorliquido) as valorLiquido
        , sum(v.valorDesconto) as valorDesconto
        , sum(v.valorComissao) as valorComissao
        , sum(v.prazomedioxvalor) as prazoMedioXValor
        , sum(v.qtsolicitada) as quantidade
        , v.nomeCliente as nomeCliente
        , v.cdpedido as codPedido
        , null as codProduto
        , null as campoCoringa1
        , null as campoCoringa2
        , null as campoCoringa3
        , v.dataprevfaturamento as dataPrevFaturamento
        , null as nomeDistritoBairro
        , v.grupocliente as grupoCliente
        , null as valorRentabilidade
        , null as dataUltFaturamento
        , null as valorFaturado
        , null as valorSaldo
        , v.codMarca as codMarca
        , v.nomegrupocliente as nomeGrupoCliente
        , v.dtemissao as dataEmissao
        , null as codRepresAgrupador
        , null as flagPolitica
        , null as codTabPreco
        , null as qtdeItens
        , null as qtdeEmQuilos
        , null as cidadeGrupoCliente
        , null as campoCoringa4
        , null as campoCoringa5
        , null as campoCoringa6
        , null as situacaoPedido
        , null as dataUltimaNF
        , null as numeroUltimaNF
        , null as pedidoCliente
        , null as pedidoRepresentante
        , null as percComissaoAtendente
FROM    (
select '1' as codEmpresa
      , coalesce((select pc.cdclassificacao from produtoclassificacaovaentity pc where pc.cdproduto = vi.cdproduto and pc.cdtipoclass = 300 limit 1), 'SEM COLECAO')  as colecao
      ,  cast(v.dtpedido as date)
      , '99999' as codlinha
      , v.cdrepresentant as codRepres
      , v.cdcliente as codCliente
      , CASE WHEN im.codibge = '99999999' or im.codibge is null THEN '4211900' ELSE im.codibge END as codIBGE
      , pe.ufsigla as uf      
      , ROUND(CAST ((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario AS numeric) ,4) AS valorBruto
      , ROUND(CAST (((((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario) * ((100 - v.prDesconto) / 100) ) * ((100 - vi.prDesconto) / 100)) AS numeric), 4) AS valorLiquido --virtual age corta o desconto pela metade quanto é meia nota
      , ROUND(CAST (((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario) - ((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario) * ((100 - v.prDesconto) / 100) AS numeric), 4) AS valorDesconto
      , ROUND(CAST((ROUND(CAST(((((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario) * ((100 - v.prDesconto) / 100) ) * ((100 - vi.prDesconto) / 100)) AS numeric), 4)) * ((v.prComissaoFat + v.prComissaoRec) / 100) AS numeric), 4) AS valorComissao
      , ROUND(CAST (((((vi.qtSolicitada + vi.qtextra - vi.qtCancelada) * vi.vlUnitario) * ((100 - v.prDesconto) / 100) ) * ((100 - vi.prDesconto) / 100)) * (v.nrprzmedio)  AS numeric), 4) AS prazomedioxvalor
      , (vi.qtSolicitada + vi.qtextra - (COALESCE(vi.qtCancelada, 0))) AS qtSolicitada
      , null cdproduto 
      , vi.cdPedido cdPedido
      , p.nmpessoa nomeCliente
      , v.cdpedido codPedido
      , vi.cdproduto as codProduto
      , null coringa1
      , null coringa2
      , null coringa3
      , cast(v.dtprevbaixa as date) dataprevfaturamento
      , null as nomeDistrito
      , vpc.cdcoligador as grupoCliente
      , 0.0 as valorRentabilidade
      , null dtaUltimoFaturamento
      , null valorFaturado
      , null as valorSaldo
      , '99999' as codMarca
      , coalesce((select pg.nmpessoa from pessoavaentity pg where pg.cdempresa = vpc.cdempresacad and pg.cdpessoa = vpc.cdcoligador),'Sem Grupo') as nomeGrupoCliente
      , cast(v.dtpedido as date) dtEmissao
      , null codRepresAgrupador
      , null flagPolitica
from 
        vendaitemvaentity vi
left join 
        vendavaentity v 
on      v.cdempresa = vi.cdempresa 
and     v.cdpedido = vi.cdpedido 
left join 
        pessoavaentity p 
on      p.cdpessoa = v.cdcliente
left join 
        vpesconsultavaentity vpc 
on      vpc.cdpessoa = p.cdpessoa
left join 
        pessoaenderecovaentity pe 
on      pe.cdpessoa = p.cdpessoa and pe.nrsequencia = ( select min(pen.nrsequencia) 
                                                        from pessoaenderecovaentity pen 
                                                        where pe.cdpessoa = pen.cdpessoa)
LEFT join 
        ETLCidade im 
on      im.codCidade = pe.cdMunicipio
where   v.tpsituacao not in (6) --remove os cancelados
and     vpc.incliente = 'TRUE'
AND     cast(v.dtpedido as date) >= current_date - 365
) v
group by  v.colecao
        , v.dtpedido
        , v.codlinha
        , v.codcliente
        , v.codRepres
        , v.codibge
        , v.uf
        , v.nomeCliente
        , v.cdpedido
        , v.dataprevfaturamento
        , v.grupocliente
        , v.nomegrupocliente
        , v.codMarca
        , v.dtemissao

Incremental:

select
        '1' as codEmpresa
        , v.cdPedido cdPedido
FROM    vendavaentity v
WHERE   cast(v.dtpedido as date) >= current_date - 365
GROUP BY v.cdPedido

GEO - Faturamento - Contrato¶

Documentação: Faturamento - Contrato

Adicionar SQL

GEO - Meta do Representante¶

Documentação: Meta do Representante

Adicionar SQL

GEO - Meta do Escritório¶

Documentação: Meta do Escritório

Adicionar SQL

GEO - Estoque Centralizado¶

Documentação: Estoque Centralizado

Adicionar SQL

GEO - Clientes Inadimplentes¶

Documentação: Clientes Inadimplentes

Adicionar SQL

GEO - Condição de Venda¶

Documentação: Não tem ainda

select 
        '1' as codEmpresa
        , codCondPagto as codPagamento
        , nome as nome
        , quantidade as qtdeParcelas
        , round(CAST(prazoMedio as numeric),2) as prazoMedio
        , situacao as situacao
        , 0 as Parcelamento
        , null as Acrescimo
        , null as Desconto
FROM    ETLCondPagto

Força de Vendas¶

FV - Tabela de Preço¶

Documentação: Tabela de Preço

select 
        '1' as codEmpresa
        , codTabelaPreco as codTabela
        , descricao as descricao
        , null as dataMinimaFaturamento
        , null as dataMaximaFaturamento
        , null as percDescontoShowRoom
        , null as dataVigenciaInicial
        , null as dataVigenciaFinal
        , null as codColecao
        , null as codSegmento
        , null as codTabelaPrecoPrincipal
        , null as desconsiderarDescAcr
        , null as codEmpresaDestino
        , null as currency
        , null as codTipoPedido
FROM    ETLtabelapreco
WHERE   codEmpresa = :p1
AND     codTabelaPreco = :p2

FV - Tabela de Preço por Representante¶

Documentação: Tabela de Preço por Representante

Adicionar SQL

FV - Tabela de Preço - Produto¶

Documentação: Tabela de Preço - Produto

Adicionar SQL

FV - Tabela de Preço - Política - Desconto Comercial¶

Documentação: Tabela de Preço - Política - Desconto Comercial

Adicionar SQL

FV - Tabela de Preço - Política - Desconto Financeiro¶

Documentação: Tabela de Preço - Política - Desconto Financeiro

Adicionar SQL

FV - Tabela de Preço - Política - Desconto Antecipação¶

Documentação: Tabela de Preço - Política - Desconto Antecipação

Adicionar SQL

FV - Tabela de Preço - Política - Prazo Adicional¶

Documentação: Tabela de Preço - Política - Prazo Adicional

Adicionar SQL

FV - Tabela de Preço - Política - Desconto Volume¶

Documentação: Tabela de Preço - Política - Desconto Volume

Adicionar SQL

FV - Tabela de Preço - Política - Preços por volume¶

Documentação: Tabela de Preço - Política - Preços por volume

Adicionar SQL

FV - Títulos Em Aberto¶

Documentação: Títulos Em Aberto

select 
        titulo.titulo as titulo
        , titulo.notaFiscal as notaFiscal
        , titulo.dataEmissao as dataEmissao
        , titulo.dataVencimento as dataVecto
        , titulo.valor as valor
        , titulo.nomePortador as nomePortador
        , titulo.codCliente as codCliente
        , pessoa.nmPessoa as nomeCliente
        , null as codgrupocliente
        , null as moeda
FROM    ETLTitulo titulo
LEFT OUTER JOIN 
        PessoaVAEntity pessoa 
ON      CAST(pessoa.cdPessoa as text) = titulo.codCliente
WHERE   cast((select rep.cdrepresentant 
                from pessoareprclientevaentity rep 
                where rep.cdempresa = pessoa.cdempresa 
                and rep.cdpessoa = pessoa.cdpessoa limit 1) as text) = :p2
AND     ((titulo.codCliente = :p3) OR (:p3 IS NULL))
and     situacao= '1'

FV - Condição de Venda¶

Documentação: Condição de Venda

select 
        '1' as codEmpresa
        , codCondPagto as codPagamento
        , nome as nome
        , quantidade as qtdeParcelas
        , round(CAST(prazoMedio as numeric),2) as prazoMedio
        , situacao as situacao
        , 0 as Parcelamento
        , null as Acrescimo
        , null as Desconto
FROM    ETLCondPagto