Index: openacs-4/packages/notifications/tcl/sweep-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/tcl/sweep-procs-oracle.xql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/notifications/tcl/sweep-procs-oracle.xql	20 Sep 2002 21:19:00 -0000	1.9
+++ openacs-4/packages/notifications/tcl/sweep-procs-oracle.xql	16 Jan 2003 13:53:10 -0000	1.10
@@ -5,41 +5,44 @@
 
     <fullquery name="notification::sweep::cleanup_notifications.select_notification_ids">
         <querytext>
-            select notification_id
+            select notifications.notification_id
             from notifications
-            where sysdate - notif_date > 2
-              and not exists (select notifications.notification_id
-                              from notifications,
-                                   notification_requests,
-                                   notification_user_map
-                              where notifications.type_id = notification_requests.type_id
-                              and notifications.object_id = notification_requests.object_id
-                              and notifications.notification_id = notification_user_map.notification_id(+)
-                              and sent_date is null)
+            minus
+            select nnr.notification_id
+            from (select notification_id, user_id
+                  from notifications, notification_requests
+                  where notifications.type_id = notification_requests.type_id
+                    and notifications.object_id = notification_requests.object_id) nnr,
+              notification_user_map
+            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
         </querytext>
     </fullquery>
 
     <fullquery name="notification::sweep::sweep_notifications.select_notifications">
         <querytext>
-            select notifications.notification_id,
-                   notif_subject,
-                   notif_text,
-                   notif_html,
-                   notification_requests.user_id,
-                   notification_requests.type_id,
-                   notification_requests.delivery_method_id,
-                   notifications.response_id
-            from notifications,
-                 notification_requests,
-                 notification_user_map
-            where notifications.type_id = notification_requests.type_id
-            and interval_id = :interval_id
-            and notifications.object_id = notification_requests.object_id
-            and notifications.notification_id = notification_user_map.notification_id(+)
-            and sent_date is null
-            and notifications.notif_date <= 
-            (sysdate - (select n_seconds / 3600 / 24 from notification_intervals where interval_id= :interval_id))
-            order by notification_requests.user_id, notification_requests.type_id
+            select nnr.*
+            from (select notifications.notification_id,
+                    notifications.notif_subject,
+                    notifications.notif_html,
+                    notification_requests.user_id,
+                    notification_requests.type_id,
+                    notification_requests.delivery_method_id,
+                    notification_requests.request_id,
+                    notifications.response_id,
+                    notifications.notif_date
+                  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 acs_objects.object_id = nnr.request_id
+              and acs_objects.creation_date <= nnr.notif_date
+            order by nnr.user_id, nnr.type_id
         </querytext>
     </fullquery>