REM dbdrv: none
REM $Header: wfntfqup.sql 26.8 2005/02/17 10:34:50 dmani ship $
REM *******************************************************************
REM NAME
REM   wfntfqup.sql - WorkFlow Message Queue Update
REM
REM DESCRIPTION
REM   Purges the wf_notification_out outbound message queue and 
REM   repopulates from the WF_NOTIFICATION table.
REM   Manual Script
REM
REM   **************************** WARNING ****************************
REM   DO NOT RUN THIS SCRIPT UNLESS DIRECTED BY ORACLE SUPPORT!!
REM   *****************************************************************
REM
REM USAGE
REM   sqlplus <usr>/<passwd>@db @wfntfqup <APPSusr> <APPSpw> <FNDusr>
REM
REM MODIFICATION LOG:
REM   12/6/2002 SMAYZE Created based on wfmqupd.sql
REM *******************************************************************

set pagesize 999
set linesize 80 
set arraysize 1
set timing on;

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

CONNECT &1/&2

declare 
   no_queue exception;
   pragma EXCEPTION_INIT(no_queue, -24010);
   
   queue_exists exception;
   pragma EXCEPTION_INIT(queue_exists, -24006);

   queue_table_exists exception;
   pragma EXCEPTION_INIT(queue_table_exists, -24001);

   subscriber_exist exception;
   pragma EXCEPTION_INIT(subscriber_exist, -24034);

   l_firstNotification NUMBER;
   l_lastNotification  NUMBER;

   cursor c_ntf is
   select notification_id, group_id, recipient_role, message_type, status
   from wf_notifications
   where status in ('OPEN', 'CANCELED')
     and mail_status in ('MAIL', 'INVALID')
     and notification_id >= l_firstNotification
     and notification_id <= l_lastNotification
   order by notification_id;

   r_ntf c_ntf%ROWTYPE;

   l_commit_level integer := 500;
   l_enq          integer;
   l_xcount       integer;

   l_wf_schema    varchar2(320);
   l_agent_name   varchar2(200);
   l_table_name   varchar2(200);
   l_queue_name   varchar2(200);
   l_exception_queue_name   varchar2(200);

   l_parameterlist wf_parameter_list_t;
   l_agent_hdl sys.aq$_agent;

begin
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
                        'BEGIN');

   -- Purge the WF_DEFERRED queue using the procedure WF_QUEUE.CLEAN_EVT 
   -- with the following Correlation IDs:
   -- 1) oracle.apps.wf.notification.send
   -- 2) oracle.apps.wf.notification.reassign
   -- 3) oracle.apps.wf.notification.cancel

   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Purging WF_DEFERRED with oracle.apps.wf.notification.send Correlation');
   wf_queue.clean_evt('WF_DEFERRED','oracle.apps.wf.notification.send');
   
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Purging WF_DEFERRED with oracle.apps.wf.notification.reassign Correlation');
   wf_queue.clean_evt('WF_DEFERRED','oracle.apps.wf.notification.reassign');
   
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Purging WF_DEFERRED with oracle.apps.wf.notification.cancel Correlation');
   wf_queue.clean_evt('WF_DEFERRED','oracle.apps.wf.notification.cancel');
   
   l_wf_schema := upper('&&3');
   
   -- Purge the WF_NOTIFICATION_OUT queue
   l_agent_name := 'WF_NOTIFICATION_OUT';
   l_queue_name :=  l_wf_schema||'.'||l_agent_name;
   l_exception_queue_name :=  l_wf_schema||'.'||'AQ$_'||l_agent_name||'_E';
   l_table_name :=  l_wf_schema||'.'||l_agent_name;

   -- Stop the queue
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Stopping the WF_NOTIFICATION_OUT queue');
   dbms_aqadm.stop_queue(queue_name => l_queue_name);
   
   begin -- Trap exception. Exception queue may no yet exist
      dbms_aqadm.stop_queue(queue_name => l_exception_queue_name);
   exception
      when no_queue then
         null; -- Ignore
      when others then
         raise_application_error(-20000, 'Oracle Error Mkr1= '
                                 ||to_char(sqlcode)||' - '||sqlerrm);
   end;
   
   -- Remove the queue
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Dropping the WF_NOTIFICATION_OUT queue');
   dbms_aqadm.drop_queue(queue_name => l_queue_name);
   
   begin -- Trap exception. Exception queue may no yet exist
      dbms_aqadm.drop_queue(queue_name => l_exception_queue_name);
   exception
      when no_queue then
         null; -- Ignore
      when others then
         raise_application_error(-20000, 'Oracle Error Mkr2= '
                                 ||to_char(sqlcode)||' - '||sqlerrm);
   end;
   
   
   -- Drop the table
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
                        'Dropping the WF_NOTIFICATION_OUT table');
   dbms_aqadm.drop_queue_table(l_table_name);
   
   -- Recreate the queue table
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Recreating the WF_NOTIFICATION_OUT table');
   begin
      dbms_aqadm.create_queue_table
	 (
	  queue_table          => l_table_name,
	  queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
	  sort_list            => 'PRIORITY,ENQ_TIME',
	  multiple_consumers   => TRUE,
	  comment              => 'Workflow JMS Topic',
	  compatible           => '8.1'
	  );
   exception
      when queue_table_exists then
	 null;
      when others then
         raise_application_error(-20000, 'Oracle Error Mkr3= '
                                 ||to_char(sqlcode)||' - '||sqlerrm);
   end;
   
   -- Recreate the queue 
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Recreating the WF_NOTIFICATION_OUT queue');
   begin
      dbms_aqadm.create_queue
	 (
          queue_name            => l_queue_name,
          queue_table           => l_table_name,
          max_retries           => 5,
          retry_delay           => 3600,
          retention_time        => 86400,
          comment               => 'Workflow JMS Topics'
	  );
      
   exception
      when queue_exists then
	 null;
      when others then
	 raise_application_error(-20000, 'Oracle Error Mkr4= '
				 ||to_char(sqlcode)||' - '||sqlerrm);
   end;
   
   -- Start the topic
   
   begin
      dbms_aqadm.start_queue(queue_name => l_queue_name);
     
   exception
      when others then
	 raise_application_error(-20000, 'Oracle Error Mkr5= '
				 ||to_char(sqlcode)||' - '||sqlerrm);
   end;
   
   
   -- Add a subscriber
   begin
      l_agent_hdl := sys.aq$_agent('WF_NOTIFICATION_OUT',null,0);
      dbms_aqadm.add_subscriber(queue_name =>l_queue_name,
				subscriber=>l_agent_hdl, rule=>'1=1');
   exception
      when subscriber_exist then
	 -- ignore if we already added this subscriber.
	 dbms_aqadm.alter_subscriber(queue_name => l_queue_name,
				     subscriber=>l_agent_hdl,
				     rule=>'1=1');
   end;
  
  
   -- Now enqueue all of the valid notifications
   -- We can not make any assumptions on the number of 
   -- transactions to process or the length of time that this will take
   -- and whether or not there are still notifications being enqueued.
   
   SELECT MAX(notification_id)
   INTO l_lastNotification
   FROM wf_notifications;
  
   l_enq := 0;
   l_firstNotification := 0;

   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'Enqueuing messages from wf_notifications');
   loop
      l_xcount := 0;
      open c_ntf;
      loop
         fetch c_ntf into r_ntf;
         exit when c_ntf%NOTFOUND or l_xcount >= l_commit_level;
	 
         -- wf_xml.enqueueNotification(r_ntf.notification_id);
         l_parameterList := wf_parameter_list_t();
         wf_event.AddParameterToList('NOTIFICATION_ID',r_ntf.notification_id,
                                     l_parameterlist);
         wf_event.AddParameterToList('ROLE', r_ntf.recipient_role, 
                                     l_parameterlist);
         wf_event.AddParameterToList('GROUP_ID', nvl(r_ntf.group_id,
						     r_ntf.notification_id),
                                     l_parameterlist);
         wf_event.addParameterToList('Q_CORRELATION_ID', r_ntf.message_type,
       			             l_parameterlist);
	 
         --Raise the event
         --  If the STATUS is OPEN then oracle.apps.wf.notification.send
         --  event is raised else if the status is CANCELED then
         --  oracle.apps.wf.notification.cancel is raised
         wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
			   'Enqueuing message '||to_char(r_ntf.notification_id));
         if(r_ntf.status = 'OPEN') then
	    wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
			   p_event_key  => to_char(r_ntf.notification_id),
			   p_parameters => l_parameterlist);
         elsif(r_ntf.status = 'CANCELED') then
	    wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.cancel',
			   p_event_key  => to_char(r_ntf.notification_id),
			   p_parameters => l_parameterlist);	    
         end if;
	 
         l_enq := l_enq + 1;
         l_xcount := l_xcount + 1;
      end loop;
      exit when c_ntf%NOTFOUND;
      l_firstNotification := r_ntf.notification_id;
      close c_ntf;
      commit;
   end loop;
   commit;
   
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFNTFQUP',
		     'END');
   
exception
   when others then
      raise_application_error(-20000, 'Oracle Error = '
			      ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

set verify on
commit;
exit;