CREATE OR ALTER VIEW TBEMPRESA_COBRANCA(
    CODIGO,
    ENDERECO,
    NUMERO,
    COMPLEMENTO,
    BAIRRO,
    CEP,
    CIDADE,
    UF,
    ENTREGA_ENDERECO,
    ENTREGA_NUMERO,
    ENTREGA_COMPLEMENTO,
    ENTREGA_BAIRRO,
    ENTREGA_CEP,
    ENTREGA_CIDADE,
    ENTREGA_UF)
AS
select
    *
from(
    select
    
    s.id codigo,
    FN_ELIPSES(e.logradouro ,50) as endereco,
    FN_ELIPSES(e.numero ,5) as numero,
    FN_ELIPSES(e.complemento ,20) as complemento,
    FN_ELIPSES(e.bairro ,20) as bairro,
    FN_ELIPSES(fn_remover(e.cep) ,8) as cep,
    FN_ELIPSES(e.cidade ,40) as cidade,
    FN_ELIPSES(e.estado ,2) as uf,
    FN_ELIPSES(f.logradouro ,50) as entrega_endereco,
    FN_ELIPSES(f.numero ,5) as entrega_numero,
    FN_ELIPSES(f.complemento ,20) as entrega_complemento,
    FN_ELIPSES(f.bairro ,20) as entrega_bairro,
    FN_ELIPSES(fn_remover(f.cep) ,8) as entrega_cep,
    FN_ELIPSES(f.cidade ,40) as entrega_cidade,
    FN_ELIPSES(f.estado ,2) as entrega_uf
    
    
    from CLIENTES s, ENDERECOS_CADASTRO e
    left join ENDERECOS_CADASTRO f on f.gerador = e.gerador and f.endereco = 0
    where e.gerador = s.gerador and e.endereco = 0
    
    union all
    
    select
    s.id codigo,
    FN_ELIPSES(e.logradouro ,50) as endereco,
    FN_ELIPSES(e.numero ,5) as numero,
    FN_ELIPSES(e.complemento ,20) as complemento,
    FN_ELIPSES(e.bairro ,20) as bairro,
    FN_ELIPSES(fn_remover(e.cep) ,8) as cep,
    FN_ELIPSES(e.cidade ,40) as cidade,
    FN_ELIPSES(e.estado ,2) as uf,
    FN_ELIPSES(f.logradouro ,50) as entrega_endereco,
    FN_ELIPSES(f.numero ,5) as entrega_numero,
    FN_ELIPSES(f.complemento ,20) as entrega_complemento,
    FN_ELIPSES(f.bairro ,20) as entrega_bairro,
    FN_ELIPSES(fn_remover(f.cep) ,8) as entrega_cep,
    FN_ELIPSES(f.cidade ,40) as entrega_cidade,
    FN_ELIPSES(f.estado ,2) as entrega_uf
    
    
    from CLIENTES s, ENDERECOS_CADASTRO e
    left join ENDERECOS_CADASTRO f on f.gerador = e.gerador and f.endereco = 1
    where e.gerador = s.gerador and e.endereco = 1

    union all
    
    select
    s.id + 10000 codigo,
    FN_ELIPSES(e.logradouro ,50) as endereco,
    FN_ELIPSES(e.numero ,5) as numero,
    FN_ELIPSES(e.complemento ,20) as complemento,
    FN_ELIPSES(e.bairro ,20) as bairro,
    FN_ELIPSES(fn_remover(e.cep) ,8) as cep,
    FN_ELIPSES(e.cidade ,40) as cidade,
    FN_ELIPSES(e.estado ,2) as uf,
    FN_ELIPSES(f.logradouro ,50) as entrega_endereco,
    FN_ELIPSES(f.numero ,5) as entrega_numero,
    FN_ELIPSES(f.complemento ,20) as entrega_complemento,
    FN_ELIPSES(f.bairro ,20) as entrega_bairro,
    FN_ELIPSES(fn_remover(f.cep) ,8) as entrega_cep,
    FN_ELIPSES(f.cidade ,40) as entrega_cidade,
    FN_ELIPSES(f.estado ,2) as entrega_uf
    
    
    from representantes s, ENDERECOS_CADASTRO e
    left join ENDERECOS_CADASTRO f on f.gerador = e.gerador and f.endereco = 0
    where e.gerador = s.gerador and e.endereco = 0
)
;

