REM $Header: wfevquc2.sql 26.2 2005/01/06 07:08:59 dmani noship $
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_IN \
REM dbdrv: &un_fnd &pw_fnd WF_IN &un_apps 5 0 604800
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_OUT \
REM dbdrv: &un_fnd &pw_fnd WF_OUT &un_apps 5 0 604800
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_ERROR \
REM dbdrv: &un_fnd &pw_fnd WF_ERROR &un_apps 5 3600 0
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_DEFERRED \
REM dbdrv: &un_fnd &pw_fnd WF_DEFERRED &un_apps 5 3600 86400
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_REPLAY_IN \
REM dbdrv: &un_fnd &pw_fnd WF_REPLAY_IN &un_apps 5 0 604800
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+2 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE:AQ_WF_REPLAY_OUT \
REM dbdrv: &un_fnd &pw_fnd WF_REPLAY_OUT &un_apps 5 0 604800
REM ***********************************************************************
REM   NAME
REM       wfevquc2.sql - 
REM   DESCRIPTION
REM       Creates Advanced Queues for the event system queue handler
REM   USAGE  
REM       sqlplus apps/pwd @wfevquc2 APPLSYS APPS <qname> APPS <retry> <delay> <retention>
REM    - Replaces wfevquec.sql to resolve backward compatibility issues due
REM      to dbdrv changes.
REM ***********************************************************************/
REM Queues require storage and consequently must be run in the base account
REM (autopatch will run all scripts in apps account)

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

WHENEVER SQLERROR EXIT FAILURE;
connect &&1/&&2

WHENEVER SQLERROR CONTINUE;

WHENEVER SQLERROR EXIT FAILURE;

REM============================================================
REM
REM Create New Queue Table
REM
REM============================================================
declare
  queue_table_exists exception;
  pragma EXCEPTION_INIT(queue_table_exists, -24001);
begin

  -- dbms_output.put_line('===================================');
  -- dbms_output.put_line('Creating WF Event Queue Tables ');
  -- dbms_output.put_line('===================================');
 
  begin 
   dbms_aqadm.create_queue_table
     (
	queue_table          => '&&3',
	queue_payload_type   => '&&4..WF_EVENT_T',
	sort_list	     => 'PRIORITY,ENQ_TIME',
	comment	             => 'Workflow event system queue',
        multiple_consumers   => TRUE,
	compatible	     => '8.1'
     );

  exception
    when queue_table_exists then 
      null;
    when others then
        -- dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        -- dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr1= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
  end;
end;
/

REM====================================================================
REM
REM Create New Queue
REM
REM====================================================================

declare
  queue_exists exception;
  pragma EXCEPTION_INIT(queue_exists, -24006);
BEGIN

  -- dbms_output.put_line('=================================');
  -- dbms_output.put_line('Creating all Workflow Queues');
  -- dbms_output.put_line('=================================');

 begin
  dbms_aqadm.create_queue
	(
	  queue_name		=> '&&3',
	  queue_table		=> '&&3',
	  comment		=> 'Workflow event system default queue',
          max_retries           => &&5,
          retry_delay           => &&6,
          retention_time        => &&7 
	);

  exception
    when queue_exists then 
       -- alter the queue to update the values
       begin
          dbms_aqadm.alter_queue
              (
                queue_name      =>  '&&3',
                max_retries     =>  &&5,
                retry_delay     =>  &&6,
                retention_time  =>  &&7
              );
       exception
          when others then
             raise_application_error(-20000, 'Oracle Error Mkr4= '
                                    ||to_char(sqlcode)||' - '||sqlerrm); 
       end;
    when others then
        -- dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        -- dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr4= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
END;
/

REM===================================================================
REM
REM Start Queue
REM
REM===================================================================

declare
  l_queue_name varchar2(128); 
begin
  l_queue_name := '&&3';
  -- dbms_output.put_line('============================');
  -- dbms_output.put_line('Starting all Workflow Queues ');
  -- dbms_output.put_line('============================');

  dbms_aqadm.start_queue(queue_name => l_queue_name);

  -- dbms_output.put_line('Enabling the Exception Queue for Dequeue ');
  if(l_queue_name in ('WF_DEFERRED','WF_ERROR')) then
     dbms_aqadm.start_queue(queue_name => 'AQ$_' || l_queue_name || '_E',
			    enqueue    => FALSE);
  end if;
  exception
    when others then
        -- dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        -- dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr7= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

REM===================================================================
REM
REM Grant Queue Privilege
REM
REM===================================================================

declare
begin

  -- dbms_output.put_line('======================================');
  -- dbms_output.put_line('Granting Privilege for Workflow Queues ');
  -- dbms_output.put_line('======================================');

  -- cannot grant privilege to myself
  if (upper('&&1') <> upper('&&4')) then
    dbms_aqadm.grant_queue_privilege(
      privilege=>'ALL',
      queue_name => '&&3',
      grantee=>'&&4');
  end if;

  exception
    when others then
        -- dbms_output.put_line('Oracle Server Error = '||to_char(sqlcode));
        -- dbms_output.put_line('Oracle Server Message = '||sqlerrm);
        raise_application_error(-20000, 'Oracle Error Mkr8= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

commit;
exit;