Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement