oracle8.1.6
select nnr.*
from (select notifications.notification_id,
notifications.notif_subject,
notifications.notif_text,
notifications.notif_html,
notifications.file_ids,
notification_requests.user_id,
notification_requests.object_id,
notification_requests.type_id,
notification_requests.delivery_method_id,
notification_requests.request_id,
notifications.response_id,
notifications.notif_date,
notifications.notif_user
from notifications, notification_requests
where notifications.type_id = notification_requests.type_id
and notifications.object_id = notification_requests.object_id
and notification_requests.interval_id = :interval_id) nnr,
notification_user_map, acs_objects
where nnr.notification_id = notification_user_map.notification_id(+)
and nnr.user_id = notification_user_map.user_id(+)
and notification_user_map.sent_date is null
and (nnr.notif_date is null or nnr.notif_date < sysdate)
and acs_objects.object_id = nnr.request_id
and acs_objects.creation_date <= nnr.notif_date
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = nnr.object_id
and ppm.privilege = 'read'
and ppm.party_id = nnr.user_id)
order by nnr.user_id, nnr.type_id, nnr.notif_date
select request_id
from notification_requests
where not exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = notification_requests.object_id
and ppm.privilege = 'read'
and ppm.party_id = notification_requests.user_id)