SELECT
    DISTINCT rf.numreserva,
    INITCAP(pe1.nome) AS NAME,
    dp.numdocumento AS DOC,
    td.NOMEDOCUMENTO AS DOC_TYPE,
    td.iddocumento as DOC_TYPE_ID,
    CASE WHEN REGEXP_LIKE(LOWER(pe1.email), '^[a-z0-9._-]+@[a-z0-9._-]+\.[a-z]{2,4}$')
        THEN LOWER(pe1.email)
        ELSE null
    END AS EMAIL_VALIDATED,
    LOWER(pe1.email) AS EMAIL_NOT_VALIDATED,
    tp.DDI,
    tp.DDD,
    tp.TIPO,
    tp.NUMERO,
    pf.datanasc as BORN_AT,
    INITCAP(pa2.NOMEPAIS) as NACIONALIDADE,
    INITCAP(ep.logradouro) AS ADDRESS,
    ep.numero AS NUM,
    INITCAP(ep.bairro) AS NEIGHBOOR,
    LTRIM(
        coalesce(INITCAP(ep.logradouro), ' ') || ', '||
        coalesce(INITCAP(ep.numero), ' ') || ' - ' ||
        coalesce(INITCAP(ep.bairro), ' ')
    ) as COMPLETE_ADDRESS,
    ep.cep AS POSTAL_CODE,
    INITCAP(ci.nome) AS CITY,
    UPPER(ES.CODESTADO) AS UF,
    INITCAP(es.nomeestado) AS ESTATE,
    INITCAP(pa.nomepais) AS COUNTRY,
    ori.descricao AS SOURCE,
    vc.descricao AS VEHICLE,
    rf.numreserva AS BOOK_NUMBER,
    mh.datachegreal AS CHECKIN,
    mh.datapartreal AS CHECKOUT,
    pe2.nome AS HOTEL
FROM
    reservasfront rf
    INNER JOIN movimentohospedes mh
        INNER JOIN pessoa pe1
            LEFT OUTER JOIN docpessoa dp
                LEFT OUTER JOIN tipodocpessoa td
                ON (dp.iddocumento = td.iddocumento)
            ON (pe1.idpessoa = dp.idpessoa)
            LEFT OUTER JOIN pessoafisica pf
                LEFT OUTER JOIN pais pa2
                ON (pa2.IDPAIS = pf.IDNACIONALIDADE)
            ON (pe1.idpessoa = pf.idpessoa)
            LEFT OUTER JOIN endpess ep
                LEFT OUTER JOIN TELENDPESS tp
                ON (tp.IDENDERECO = ep.idendereco AND tp.NUMERO IS NOT NULL)
                LEFT OUTER JOIN cidades ci
                    LEFT OUTER JOIN estado es
                        LEFT OUTER JOIN pais pa
                        ON es.idpais = pa.idpais
                    ON (ci.idestado = es.idestado)
                ON (ep.idcidades = ci.idcidades)
            ON (pe1.idendresidencial = ep.idendereco)
        ON mh.idhospede = pe1.idpessoa
    ON rf.idreservasfront = mh.idreservasfront
    INNER JOIN hotel ho
      INNER JOIN pessoa pe2
         INNER JOIN empresaprop epr
         ON (pe2.idpessoa = epr.idpessoa)
      ON (ho.idpessoa = pe2.idpessoa)
    ON rf.idhotel = ho.idhotel
    INNER JOIN origemreserva ori
      ON (rf.idorigem = ori.idorigem)
    INNER JOIN veiculoscomunica vc
      ON (rf.idveiculos = vc.idveiculos)
WHERE ho.idhotel BETWEEN 1 AND 7
    AND rf.poollista = 'N'
    AND mh.datachegreal IS NOT NULL
    AND rf.statusreserva BETWEEN 2 AND 3
ORDER BY rf.numreserva;