REM $Header: wf260dat.sql 26.10 2004/12/08 03:43:26 kma ship $
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=upg \
REM dbdrv: checkfile:~PROD:~PATH:~FILE \
REM dbdrv: &un_apps &pw_apps &un_fnd &pw_fnd
REM +======================================================================+
REM | Copyright (c) 1998 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM   NAME
REM       wf260dat.sql - WorkFlow 2.6.0 Data Upgrade Script
REM
REM   DESCRIPTION
REM       Upgrade Workflow 2.5/2.6 -> Workflow 2.6.2 data.
REM
REM   USAGE
REM       sqlplus apps/apps @wf260dat APPS APPS APPLSYS APPS
REM       sqlplus wfinst/wfinst @wf260dat WFINST WFINST WFINST WFINST
REM
REM   NOTES
REM       Rebuild the Deferred Queue to be multi-consumer queue.
REM       Note that the enqueue order is not preserved.
REM       This script rebuilds the deferred queue tagging the schema 
REM       name to the correlation which is required in 2.6
REM
REM   MODIFICATION LOG:
REM       01/2002 JWSMITH BUG 2001012 - Increased l_wf_schema to varchar2(320)
REM *******************************************************************

set arraysize 1
set verify off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;


REM As FND User
WHENEVER SQLERROR EXIT FAILURE;
connect &3/&4

REM Continue in case of error where constraints aready exist
WHENEVER SQLERROR CONTINUE;

WHENEVER SQLERROR EXIT FAILURE;

declare
  queue_table_exists exception;
  pragma EXCEPTION_INIT(queue_table_exists, -24001);
begin
  begin 
   dbms_aqadm.create_queue_table
     (
	queue_table          => 'WF_DEFERRED_TABLE_M',
	queue_payload_type   => 'WF_PAYLOAD_T',
	sort_list	     => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
	comment	             => 'Workflow Deferred Queue',
        compatible           => '8.1'
     );

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

declare
  queue_exists exception;
  pragma EXCEPTION_INIT(queue_exists, -24006);
BEGIN
 begin
  dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_DEFERRED_QUEUE_M',
	  queue_table		=> 'WF_DEFERRED_TABLE_M',
	  comment		=> 'Workflow Deferred Queue'
	);

  exception
    when queue_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
END;
/

begin

  dbms_aqadm.start_queue(queue_name => 'WF_DEFERRED_QUEUE_M');

  exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr3= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

begin

  if (upper('&&1') <> upper('&&3')) then
    dbms_aqadm.grant_queue_privilege(
      privilege=>'ALL',
      queue_name => 'WF_DEFERRED_QUEUE_M',
      grantee=>'&&1');
  end if;

  exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr4= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

WHENEVER SQLERROR EXIT FAILURE;
REM As APPS
connect &1/&2

REM
REM Check if old queue exists
REM Not exist means it has already been migrated.  Do nothing.
REM Exit with status succeed
REM
WHENEVER SQLERROR EXIT;
declare
  cnt number;
begin
  select count(1) into cnt
    from dba_queue_tables
   where owner = '&&3'
     and queue_table = 'WF_DEFERRED_TABLE';

  if (cnt = 0) then
    raise_application_error(-20000, 'Deferred Queue has already been migrated.');
  end if;

  select count(1) into cnt
    from dba_queues
   where owner = '&&3'
     and name = 'WF_DEFERRED_QUEUE';

  if (cnt = 0) then
    raise_application_error(-20000, 'Deferred Queue has already been migrated. Please drop the queue table WF_DEFERRED_TABLE.');
  end if;
end;
/

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
REM
REM migrate old queue to new queue
REM
declare 
   dequeue_timeout exception;
   pragma EXCEPTION_INIT(dequeue_timeout, -25228);

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

   type subscriber_tbl_type is table of varchar2(30) index by binary_integer;

   l_commit_level integer :=  500;
   l_wf_schema    varchar2(320);
   l_old_queue   varchar2(200);
   i              pls_integer := 1;
   id             pls_integer := 1;

   l_dequeue_options dbms_aq.dequeue_options_t;
   l_message_properties dbms_aq.message_properties_t;
   l_message_handle RAW(16) := NULL;
   l_payload system.wf_payload_t;
   l_navigation binary_integer;
   l_subscriber varchar2(30);
   l_enqueue_options dbms_aq.enqueue_options_t;
   l_new_queue varchar2(30);
   l_agent sys.aq$_agent;
   subscribers subscriber_tbl_type;
   match boolean;
   l_delay number;
   l_state number;

   -- WF_DEFERRED_TABLE is indexed by Q_NAME, STATE, ENQ_TIME
   -- We can only take care of state:
   -- 0: READY
   -- 1: WAIT
   -- Do nothing for messages that have been processed or
   -- that are in the exception queue.
   cursor msgidc is
     select MSGID, STATE
       from &&3..WF_DEFERRED_TABLE
      where Q_NAME = 'WF_DEFERRED_QUEUE'
        and STATE < 2
      order by STATE, ENQ_TIME;

begin
   -- Move from WF_DEFERRED_QUEUE to WF_DEFERRED_QUEUE_M
   l_dequeue_options.dequeue_mode := dbms_aq.REMOVE;
   l_dequeue_options.wait := dbms_aq.NO_WAIT;
   l_dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;

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

   l_old_queue := l_wf_schema||'.WF_DEFERRED_QUEUE';
   l_new_queue  := l_wf_schema||'.WF_DEFERRED_QUEUE_M';

   -- disable enqueue to the old queue
   dbms_aqadm.stop_queue(
        queue_name=>l_old_queue,
        wait=>FALSE);

   dbms_aqadm.start_queue(
        queue_name=>l_old_queue,
        enqueue=>FALSE,
        dequeue=>TRUE);

   -- Add the first subscriber to initialize the table
   subscribers(i) := upper('&1');
   l_agent := sys.aq$_agent(subscribers(i),'',0);
   begin
     dbms_aqadm.add_subscriber(queue_name=>l_new_queue,
                               subscriber=>l_agent,
                               rule=>'CORRID like '''||subscribers(i)||'%''');
   exception
     when subscriber_exist then
       null;  -- ignore if we already added this subscriber.
   end;

   -- Moving the queue
   <<outer_loop>>
   loop
     open msgidc;

     <<inner_loop>>
     loop
       fetch msgidc into l_dequeue_options.msgid, l_state;

       if (msgidc%notfound) then
         exit outer_loop;
       end if;

       begin
         dbms_aq.Dequeue(queue_name => l_old_queue,
                       dequeue_options => l_dequeue_options,
                       message_properties => l_message_properties,
                       payload => l_payload,
                       msgid => l_message_handle);
       exception
         when dequeue_timeout then
           exit inner_loop;  -- should not happen, but just in case.
         when others then
           raise_application_error(-20000, 'Oracle Error Rebuild1= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
       end;

       -- find subscriber name
       l_subscriber := substrb(l_message_properties.correlation, 1, instrb(l_message_properties.correlation,l_payload.itemtype)-1);

       -- this is very old type of correlation, convert it to new form.
       -- default subscriber to the first one.
       if (l_subscriber is null) then
         l_subscriber := subscribers(1);
         l_message_properties.correlation := l_subscriber||l_message_properties.correlation;
       end if;

       -- add subscriber
       match := false;
       for j in subscribers.FIRST..subscribers.LAST loop
         if (l_subscriber = subscribers(j)) then
           match := true;
           exit;
         end if;
       end loop;
       if (not match) then
         i := i+1;
         subscribers(i) := l_subscriber;

         l_agent := sys.aq$_agent(l_subscriber,'',0);
         dbms_aqadm.add_subscriber(queue_name=>l_new_queue,
                               subscriber=>l_agent,
                               rule=>'CORRID like '''||l_subscriber||'%''');
       end if;

       -- recalculate delay
       -- if message state is READY, the new delay should be 0.
       if (l_state = 0 and l_message_properties.delay > 0) then
         l_message_properties.delay := 0;
       elsif (l_message_properties.delay > 0) then
         l_delay := l_message_properties.delay - round((sysdate - l_message_properties.enqueue_time)*24*60*60 - 0.5);

         -- max value of delay (binary integer) is (2**31)-1
         if (l_delay < 0) then
           l_delay := 0;
         elsif (l_delay >= power(2,31)) then
           l_delay := power(2,31)-1;  -- overflow
         end if;
         l_message_properties.delay := l_delay;
       end if;

       -- enqueue
       dbms_aq.enqueue(queue_name=>l_new_queue,
                     enqueue_options=>l_enqueue_options,
                     message_properties=>l_message_properties,
                     payload=>l_payload,
                     msgid=>l_message_handle);

       l_dequeue_options.navigation   := dbms_aq.NEXT_MESSAGE;

       exit inner_loop when (msgidc%rowcount = l_commit_level);
     end loop inner_loop;

     if (msgidc%ISOPEN) then
       close msgidc;
     end if;

     commit;

   end loop outer_loop;

   if (msgidc%ISOPEN) then
     close msgidc;
   end if;

   commit;

exception
  when OTHERS then
    if (msgidc%ISOPEN) then
      close msgidc;
    end if;
    raise;
end;
/

REM
REM Drop the old queue table if it is empty
REM
declare
  cnt  number;
begin
  select count(1) into cnt from &&3..WF_DEFERRED_TABLE;

  if (cnt = 0) then
    dbms_aqadm.stop_queue(queue_name=>'&&3..WF_DEFERRED_QUEUE',
                          wait=>FALSE);

    dbms_aqadm.drop_queue(queue_name=>'&&3..WF_DEFERRED_QUEUE');

    dbms_aqadm.drop_queue_table(queue_table=>'&&3..WF_DEFERRED_TABLE',
                                force=>TRUE);
  end if;

exception
  when OTHERS then
    null;  -- ignore any error
end;
/

REM Now update the Queue Version
REM This is needed to prevent any old wf260dat.sql from running.
REM The script only operates on LANGUAGE 'US'.
begin
  update WF_RESOURCES
     set TEXT = '26.17'
   where NAME = 'WF_QUEUE_VERSION';

  if (sql%rowcount = 0) then
    insert into WF_RESOURCES (
      TYPE,
      NAME,
      ID,
      TEXT,
      PROTECT_LEVEL,
      CUSTOM_LEVEL,
      LANGUAGE,
      SOURCE_LANG
    ) values (
      'WFTKN',
      'WF_QUEUE_VERSION',
      0,
      '26.17',
      0,
      0,
      'US',
      'US'
    );
  end if;

end;
/

commit;
exit;