def s=31810;
SELECT
  to_char(c.card_id) AS c0,
  c.first_name       AS c1,
  c.second_name      AS c2,
  c.last_name        AS c3,
  c.phone            AS c4,
  c.cell_phone       AS c5,
  r0.rvc_name        AS c6,
  r0.key_number      AS c7,
  r0.clockin         AS c8,
  r0.exp_dep_hour    AS c9,
  '' AS c10,
  '' AS c11,
  c.update_date AS c12
FROM cards c,
  (SELECT
     r.card_id, r.clockin, r.exp_dep_hour, rvc.name AS rvc_name, rk.lag AS key_number
   FROM registration r, rvc rvc,
     (SELECT reg_id, LISTAGG(key_number, ',') WITHIN GROUP (ORDER BY reg_id) lag FROM registration_keys GROUP BY reg_id) rk
       WHERE rvc.rvc_id = r.rvc_id AND rvc.POS_RVC=0 AND r.clockout IS NULL AND rk.reg_id(+) = r.reg_id) r0
WHERE c.is_archived != '1' AND r0.card_id (+) = c.card_id
  AND (
   ( (
      c.card_id IN (SELECT card_id FROM CARD_XTRA WHERE MAGSTRIPE = &s)
      OR c.CARD_ID IN (SELECT t.card_id FROM TICKETS t WHERE t.RELEASE_TM IS NULL AND t.CODE = &s)
      OR c.card_id IN (SELECT r1.card_id FROM registration r1, REGISTRATION_KEYS rk1
        WHERE r1.REG_ID = rk1.REG_ID AND r1.CLOCKOUT IS NULL AND r1.clockin < sysdate AND (rk1.KEY_CODE=&s OR rk1.KEY_NUMBER = &s))))
  OR LOWER(c.last_name || ' ' || c.first_name) LIKE LOWER(&s || '%') OR c.cell_phone = &s OR c.phone = &s OR c.magstripe = &s)
ORDER BY 4, 2, 3