SELECT order_office_code,
                     priority,
                     MIN (application_date) application_date
                FROM xps.application a
                     LEFT JOIN
                     (SELECT o.unit_code
                        FROM xps.prior_sched s
                             JOIN xps.priority_schedule_unit o
                                ON (s.id = o.prior_schedule_id)
                       WHERE     SYSDATE BETWEEN s.SCHEDULE_FROM
                                             AND s.SCHEDULE_TO
                             AND priority_level = -1) v
                        ON a.order_office_code LIKE v.UNIT_CODE || '%'
               WHERE     status_id = 'b'
                     AND v.UNIT_CODE IS NULL
                     AND priority > 0
            GROUP BY order_office_code, priority
            ORDER BY 2 DESC, 3 ASC;

ON a.order_office_code LIKE v.UNIT_CODE || '%'

AND v.UNIT_CODE IS NULL

AND NOT EXISTS (SELECT 1
                FROM xps.prior_sched s
                JOIN xps.priority_schedule_unit o
                ON (s.id = o.prior_schedule_id)
                WHERE SYSDATE BETWEEN s.SCHEDULE_FROM AND s.SCHEDULE_TO
                AND priority_level = -1
                AND a.order_office_code LIKE o.UNIT_CODE || '%'
               )