SELECT
DISTINCT
INITCAP(LOWER(a.desc_nombr)) AS nombre,
INITCAP(LOWER(a.desc_appat)) AS apellido,
CASE
  WHEN tipo_docum = 'DNI' THEN 1
  WHEN tipo_docum = 'LE' THEN 18
  WHEN tipo_docum = 'LC' THEN 19
  WHEN tipo_docum = 'PAS' THEN 90
  WHEN tipo_docum = 'CI' THEN 2
  WHEN tipo_docum = 'DNT' THEN 1
END AS tipo_documento,
a.nro_docum AS nro_doc,
b.codigo_postal,
CASE
  WHEN b.correo_electronico IS NULL THEN 'Sin Mail'
  WHEN b.correo_electronico = '' THEN 'Sin Mail'
  ELSE b.correo_electronico
END AS email,
CASE
  WHEN b.telefono IS NULL THEN 'Sin Teléfono'
  WHEN b.telefono = '' THEN 'Sin Teléfono'
  ELSE b.telefono
END,
INITCAP(LOWER(c.desc_localidad)) AS localidad,
CASE
  WHEN e.codigoescalafon = 'NODO' THEN 'ND'
  WHEN e.codigoescalafon = 'AUTO' THEN 'AS'
END AS claustro
FROM mapuche.dh01 a
INNER JOIN mapuche.dha1 b ON (a.nro_legaj = b.nro_persona) AND (b.domprincipal = true)
INNER JOIN mapuche.dha4 c ON (b.codigo_postal = c.codigo_postal) AND (b.codigo_provincia = c.codigo_provincia)
INNER JOIN mapuche.dh03 d ON (a.nro_legaj = d.nro_legaj)
INNER JOIN mapuche.dh11 e ON (d.codc_categ = e.codc_categ)
WHERE a.tipo_estad = 'A'
AND (d.fec_baja > NOW() OR d.fec_baja IS NULL)
AND e.codigoescalafon IN ('NODO', 'AUTO')
AND c.codigo_repetidos = '00'
ORDER BY apellido ASC