REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=dat+3 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
REM $Header: wfmqupd.sql 26.12 2003/01/17 12:11:07 smayze ship $
REM *******************************************************************
REM NAME
REM   wfmqupd.sql - WorkFlow Message Queue Update
REM
REM DESCRIPTION
REM   Purges the outbound message queue and repopulates from the 
REM   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 @wfmqupd
REM
REM MODIFICATION LOG:
REM   01/2002 JWSMITH BUG 2001012 - Increased l_wf_schema to varchar2(320)
REM   12/2002 SMAYZE  Stubbed out and replaced with wfntfqup.sql
REM   01/2003 SMAYZE  Moved the WF_SMTP_O_x_QUEUE handling from wfntfqup.sql
REM *******************************************************************

set pagesize 999
set linesize 80
set arraysize 1

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

declare 

   dequeue_disabled exception;
   pragma EXCEPTION_INIT(dequeue_disabled, -25226);

   no_queue exception;
   pragma EXCEPTION_INIT(no_queue, -24010);

   l_wf_schema    varchar2(320);
   l_queue_name   varchar2(200);
   l_table_name   varchar2(200);
   i              integer;
   l_protocol     varchar2(200);
   l_iobound      varchar2(200);
   l_queue_count  number;


begin

   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'BEGIN');
   -- Remove all notifications from the queue

   select protocol, inbound_outbound, queue_count
      into l_protocol, l_iobound, l_queue_count
   from wf_queues
      where protocol = 'SMTP'
        and INBOUND_OUTBOUND = 'OUTBOUND'
        and DISABLE_FLAG = 'N';

   l_wf_schema := wf_core.translate('WF_SCHEMA');

   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Purging generic queues');
   -- Drop the exception queue
   for i in 1..l_queue_count loop
      -- Build the queue name from the information we know.

      l_queue_name := l_wf_schema||'.AQ$_WF_'||l_protocol||'_'||
                      substrb(l_iobound,1,1)||
                      '_'||to_char(i)||'_TABLE_E';
      -- sometimes exception queue was disabled for no apparent reason
      -- enable it regardless what dba_queues says.
      dbms_aqadm.start_queue(
        queue_name=>l_queue_name,
        enqueue=>FALSE,
        dequeue=>TRUE);

      wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Dropping Exception queue '||l_queue_name);
      -- Stop the queue.
      dbms_aqadm.stop_queue(queue_name => l_queue_name);
      dbms_aqadm.drop_queue(queue_name => l_queue_name);

   end loop;

   /*
   ** Drop the WF_SMTP_O_x_QUEUE queue
   */
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Dropping main queue');
   for i in 1..l_queue_count loop
      -- Build the queue name from the information we know.
      l_queue_name := l_wf_schema||'.WF_'||l_protocol||'_'||
                      substr(l_iobound,1,1)||'_'||to_char(i)||'_QUEUE';
      wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Purging queue '||l_queue_name);

      dbms_aqadm.stop_queue(queue_name => l_queue_name);
      dbms_aqadm.drop_queue(queue_name => l_queue_name);

   end loop;

   /*
   ** Drop the WF_SMTP_O_x_TABLE queue table
   */
   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Dropping queue table');
   for i in 1..l_queue_count loop

      -- Build the queue name from the information we know.
      l_table_name := l_wf_schema||'.WF_'||l_protocol||'_'||
                      substr(l_iobound,1,1)||'_'||to_char(i)||'_TABLE';

      wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Dropping table '||l_table_name);

      dbms_aqadm.drop_queue_table(l_table_name);

   end loop;
   delete WF_QUEUES
   where PROTOCOL = 'SMTP'
     and INBOUND_OUTBOUND = 'OUTBOUND';

   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'Recreating one queue for legacy purposes');
   WF_QUEUE.CREATE_GENERIC_QUEUE('SMTP','OUTBOUND',
                                 'SMTP OUTBOUND Mailer Queue', 1);


   wf_log_pkg.string(WF_LOG_PKG.LEVEL_STATEMENT, 'WFMQUPD',
                        'END');
end;
/

set verify on

commit;
exit;