Index: openacs-4/packages/news/sql/oracle/news-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/news/sql/oracle/news-create.sql,v
diff -u -r1.7 -r1.8
--- openacs-4/packages/news/sql/oracle/news-create.sql	30 Sep 2003 12:10:08 -0000	1.7
+++ openacs-4/packages/news/sql/oracle/news-create.sql	6 Oct 2003 12:11:23 -0000	1.8
@@ -248,7 +248,8 @@
 
 
     function status (
-        news_id in cr_news.news_id%TYPE
+        publish_date     in cr_revisions.publish_date%TYPE,
+        archive_date     in cr_news.archive_date%TYPE
     ) return varchar2;
 
 
@@ -540,42 +541,27 @@
     -- the status function returns information on the puplish or archive status
     -- it does not make any checks on the order of publish_date and archive_date
     function status (
-        news_id in cr_news.news_id%TYPE
+        publish_date     in cr_revisions.publish_date%TYPE,
+        archive_date     in cr_news.archive_date%TYPE
     ) return varchar2
     is
-        v_archive_date date;
-        v_publish_date date;
     begin
-        -- populate variables
-        select archive_date into v_archive_date 
-        from   cr_news 
-        where  news_id = news.status.news_id;
-        --
-        select publish_date into v_publish_date
-        from   cr_revisions
-        where  revision_id = news.status.news_id;
-        
-        -- if publish_date is not null the item is approved, otherwise it is not
-        if v_publish_date is not null then
-            if v_publish_date > sysdate  then
+
+        if publish_date is not null then
+            if publish_date > sysdate  then
                 -- to be published (2 cases)
-                -- archive date could be null if it has not been decided when to archive
-                if v_archive_date is null then 
-                    return 'going live in ' || 
-                    round(to_char(v_publish_date - sysdate),1) || ' days';
+                if archive_date is null then 
+                    return 'going_live_no_archive';
                 else 
-                    return 'going live in ' || 
-                    round(to_char(v_publish_date - sysdate),1) || ' days' ||
-                    ', archived in ' || round(to_char(v_archive_date - sysdate),1) || ' days';
+                    return 'going_live_with_archive';
                 end if;  
             else
                 -- already released or even archived (3 cases)
-                if v_archive_date is null then
-                     return 'published, not scheduled for archive';
+                if archive_date is null then
+                     return 'published_no_archive';
                 else
-                    if v_archive_date - sysdate > 0 then
-                         return 'published, archived in ' || 
-                         round(to_char(v_archive_date - sysdate),1) || ' days';
+                    if archive_date - sysdate > 0 then
+                        return 'published_with_archive';
                     else 
                         return 'archived';
                     end if;
@@ -820,7 +806,7 @@
     ps.first_names || ' ' || ps.last_name as item_creator,
     ao.creation_date,
     ci.live_revision,
-    news.status(cn.news_id) as status
+    news.status(cr.publish_date, cn.archive_date) as status
 from 
     cr_items ci, 
     cr_revisions cr,
@@ -885,9 +871,12 @@
     cr.mime_type as mime_type,
     cn.package_id,
     ao.creation_date as creation_date,
-    news.status(news_id) as status,
-    case when exists (select 1 from cr_news where news_id = revision_id 
-         and news.status(news_id) = 'unapproved') then 1 else 0 end 
+    news.status(cr.publish_date, cn.archive_date) as status,
+    case when exists (select 1 
+                      from cr_revisions cr2
+                      where cr2.revision_id = cn.news_id
+                        and cr2.publish_date is null
+                     ) then 1 else 0 end 
          as
          approval_needed_p,
     ps.first_names || ' ' || ps.last_name as item_creator,
@@ -941,7 +930,7 @@
     decode(cr.mime_type,'text/html','t','f') as html_p,
     cr.publish_date,
     cn.archive_date,
-    news.status(cr.revision_id) as status,
+    news.status(cr.publish_date, cn.archive_date) as status,
     ci.name as item_name,
     ps.person_id as creator_id,
     ps.first_names || ' ' || ps.last_name as item_creator