V_INTEGRACAO_CLIENTES

gistfile1.sql

CREATE OR REPLACE VIEW V_INTEGRACAO_CLIENTES AS 
with REFERENCIAS as(
    SELECT 
        CODCLI,
        json_arrayagg(
        JSON_OBJECT(
            'preferencial' VALUE NULL,
            'nome' VALUE PCCLIREF.CONTATOREFER,
            'cargo' VALUE NULL,
            'telefone' VALUE PCCLIREF.TELREFER,
            'celular' VALUE PCCLIREF.TELREFER,
            'email' VALUE NULL,
            'excluir' VALUE NULL,
            'ativo' VALUE NULL
            )
    ) AS DADOS
    FROM PCCLIREF 
    group by codcli
)
SELECT 
tipofj AS TIPO,
FANTASIA AS NOMEORIGEM,
REGEXP_REPLACE(CGCENT, '[^0-9]', '') AS DOCUMENTO,
PCUSUARI.tipovend AS tipoRelacionamento,
NULL AS TOKEN,
JSON_OBJECT(
    'codigoSistemaGestao' VALUE PCCLIENT.CODCLI,
    'nome' VALUE PCCLIENT.CLIENTE,
    'razaoSocial' VALUE DECODE(PCCLIENT.tipofj,'J',PCCLIENT.CLIENTE,NULL),
    'rg' VALUE DECODE(PCCLIENT.tipofj,'F',PCCLIENT.CGCENT,NULL),
    'inscricaoEstadual' value DECODE(PCUSUARI.tipovend,'J',PCCLIENT.ieEnt,NULL),
    'dataNascimento' value DECODE(PCUSUARI.tipovend,'J',PCCLIENT.DTCADASTRO,PCCLIENT.DTNASC),
    'cedente' value DECODE(PCUSUARI.tipovend,'C','S','N'),
    'pais' value DECODE(PCUSUARI.tipovend,'E',PCPAIS.DESCRICAO,NULL),
    'complemento' value DECODE(PCUSUARI.tipovend,'E',PCCLIENT.COMPLEMENTOENT,NULL),
    'dataClienteDesde' value DECODE(PCUSUARI.tipovend,'C',PCCLIENT.DTCADASTRO,NULL),
    'contribuinteIcms' value PCCLIENT.CONTRIBUINTE,
    'dataClienteDesde' value NULL,
    'codigoColigado' value NULL,
    'nomeColigado' value NULL
) as pessoa,
JSON_OBJECT(
    'cidade' value PCCLIENT.MUNICENT,
    'uf' value DECODE(PCUSUARI.tipovend,'E','EX',PCCLIENT.ESTENT),
    'rua' value PCCLIENT.ENDERENT,
    'bairro' value PCCLIENT.BAIRROENT,
    'cep' value PCCLIENT.CEPENT,
    'numero' value  PCCLIENT.NUMEROENT,
    'complemento' value PCCLIENT.COMPLEMENTOENT,
    'tipoEndereco' value 5,
    'confirmado' value null,
    'apelido' value null
    ) AS endereco,
REFERENCIAS.DADOS AS contatos,
JSON_ARRAY(JSON_OBJECT(
    'complemento' VALUE null,
    'dataCadastro' VALUE null,
    'login' VALUE null,
    'excluir' VALUE null
)) AS informacoesComplementares,
  JSON_ARRAY(
    JSON_OBJECT(
        'codigo' VALUE NULL,
        'desassociar' VALUE NULL,
        'dataDesbloqueio' VALUE NULL
    )
) AS bloqueios,
 JSON_OBJECT(
    'codigoSistemaGestao' VALUE NULL,
    'desassociar' VALUE NULL
) AS grupoEconomico,
 JSON_OBJECT(
    'tipo' VALUE NULL,
    'documento' VALUE NULL,
    'desassociar' VALUE NULL
) AS representante,
JSON_ARRAY(
    JSON_OBJECT(
        'dataOperacao' VALUE NULL,
        'valorFaturamento' VALUE NULL
    )
) AS faturamentoDeclarados,
JSON_OBJECT(
    'valorElegivel' VALUE NULL,
    'prazo' VALUE NULL,
    'endividamento' VALUE NULL,
    'share' VALUE NULL
) AS indiceFaturamentoResumido,
JSON_OBJECT(
    'periodo' VALUE NULL,
    'valor' VALUE NULL,
    'liquidez' VALUE NULL,
    'recompra' VALUE NULL
) AS contasReceberAnaliticoInformacoesConsolidadas,
JSON_OBJECT(
    'valoresAVencer' VALUE NULL,
    'valorVencido' VALUE NULL
) AS contasReceberAnaliticoValoresEmAberto,
JSON_ARRAY(
    JSON_OBJECT(
        'uf' VALUE NULL,
        'numeroRegistro' VALUE NULL,
        'tipoRegistroProfissional' VALUE NULL,
        'ativo' VALUE NULL,
        'codigoAreaAtuacao' VALUE NULL
    )
) AS dadosProfissionais,
JSON_ARRAY(
    JSON_OBJECT(
        'pergunta' VALUE NULL,
        'resposta' VALUE NULL,
        'dataValidade' VALUE NULL
    )
) AS respostasquestionarios
FROM PCCLIENT
JOIN PCUSUARI ON PCUSUARI.CODUSUR = PCCLIENT.CODUSUR1
JOIN PCPAIS ON PCPAIS.CODPAIS = PCCLIENT.CODPAIS
LEFT JOIN REFERENCIAS ON REFERENCIAS.CODCLI = PCCLIENT.CODCLI;