REM +======================================================================+
REM | Copyright (c) 1998 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM   NAME
REM       wfbkgbld.sql  - Utility script to rebuild the background queue
REM   USAGE
REM       sqlplus <wf_account>/<wf_account> @wfbkgbld <wf_schema>
REM   NOTES
REM       This script rebuilds the deferred queue 
REM      
REM   23/08/2003 KMA        Bug 3734595 - Remove wf_message_payload_t
REM                         and convert wf_payload_t from system to
REM                         workflow user for transportable tablespace.

/**
    this will drop all data from your background queue and rebuild it 
    do NOT run this script if running multiple application installs   
    **WARNING** ONLY run this if you have single install of workflow  

    BOUNCE DATABASE PRIOR TO RUNNING TO ENSURE NO USERS/PROCESSES **/

WHENEVER SQLERROR EXIT FAILURE ROLLBACK
/* $Header: wfbkgbld.sql 26.4 2004/12/08 04:16:45 kma ship $ */

REM  dbdrv: none

prompt Abort sqlplus if not ready to continue
define enter_a_value_to_continue ='&continue_now '

define schema = '&1'

declare 
  q_version varchar2(200);
  event     	        wf_payload_t;
  dequeue_options	dbms_aq.dequeue_options_t;
  message_properties    dbms_aq.message_properties_t;
  msg_id                raw(16);
  q_name    varchar2(60);

  cursor curs_waiting is
     select msgid 
     from &schema..wf_deferred_table_m
     where q_name='WF_DEFERRED_QUEUE_M' and state=1;
  cursor curs_deferred is
    select 
         CWIAS.ITEM_TYPE, 
         CWIAS.ITEM_KEY,
         CWIAS.PROCESS_ACTIVITY,
         greatest(round((CWIAS.BEGIN_DATE  - sysdate)*86400+0.5),0) delay
    from WF_ITEM_ACTIVITY_STATUSES CWIAS, WF_PROCESS_ACTIVITIES CWPA,
         WF_ACTIVITIES CWA, WF_ITEMS WI,
         WF_PROCESS_ACTIVITIES PWPA, WF_ITEM_ACTIVITY_STATUSES PWIAS
    where CWIAS.ACTIVITY_STATUS = 'DEFERRED'
    and CWIAS.PROCESS_ACTIVITY = CWPA.INSTANCE_ID
    and CWPA.ACTIVITY_ITEM_TYPE = CWA.ITEM_TYPE
    and CWPA.ACTIVITY_NAME = CWA.NAME
    and CWIAS.ITEM_TYPE = WI.ITEM_TYPE
    and CWIAS.ITEM_KEY = WI.ITEM_KEY
    and WI.BEGIN_DATE >= CWA.BEGIN_DATE
    and WI.BEGIN_DATE < nvl(CWA.END_DATE, WI.BEGIN_DATE+1)
    and CWPA.PROCESS_NAME = PWPA.ACTIVITY_NAME
    and CWPA.PROCESS_ITEM_TYPE = PWPA.ACTIVITY_ITEM_TYPE
    and PWPA.INSTANCE_ID = PWIAS.PROCESS_ACTIVITY
    and PWIAS.ITEM_TYPE = CWIAS.ITEM_TYPE
    and PWIAS.ITEM_KEY = CWIAS.ITEM_KEY
    and PWIAS.ACTIVITY_STATUS <> 'SUSPEND'
    and (CWA.FUNCTION_TYPE is not null or CWA.FUNCTION_TYPE = 'PLSQL')
    order by CWIAS.BEGIN_DATE, CWIAS.EXECUTION_TIME;

begin 
    q_name := wf_queue.DeferredQueue;

    -- 1) dequeue  everything in WAITING STATE 
    dequeue_options.dequeue_mode := dbms_aq.REMOVE;
    dequeue_options.wait := dbms_aq.NO_WAIT;
    -- delete absolutely everything
    -- if corltn is   '%' then only deletes where corltn like '%%'
    dequeue_options.correlation := null;
    dequeue_options.navigation  := dbms_aq.FIRST_MESSAGE;
    dequeue_options.consumer_name := wf_queue.account_name;
    
    for wait_rec in curs_waiting loop
      dequeue_options.msgid := wait_rec.msgid;
      dbms_aq.dequeue
      (
	queue_name => q_name,
	dequeue_options => dequeue_options,
	message_properties => message_properties,
	payload => event,
	msgid => msg_id
      ); 
    end loop;
    

    -- 2) Now dequeue everything else from the background queue
    begin
    dequeue_options.msgid := null;
    dequeue_options.dequeue_mode := dbms_aq.REMOVE;
    dequeue_options.wait := dbms_aq.NO_WAIT;
    -- delete absolutely everything
    -- if corltn is   '%' then only deletes where corltn like '%%'
    dequeue_options.correlation := null;
    dequeue_options.navigation  := dbms_aq.FIRST_MESSAGE;
    dequeue_options.consumer_name := wf_queue.account_name;

    LOOP
        msg_id:=null;
	dbms_aq.dequeue
	(
	  queue_name => q_name,
	  dequeue_options => dequeue_options,
	  message_properties => message_properties,
	  payload => event,
	  msgid => msg_id
	);

    END LOOP;
    exception -- timeout will fall to here
    when others then null; 
    end;

    -- 3) (re)-seed everything to the queue 
    for def_rec in curs_deferred loop
    	   wf_queue.enqueue_event
              (queuename=>q_name,
               itemtype=> def_rec.item_type,
               itemkey=>def_rec.item_key,
               actid=>def_rec.process_activity,
               delay=>def_rec.delay,
               message_handle=>msg_id);

           -- update the message handle           
           update wf_item_activity_statuses
           set outbound_queue_id = msg_id 
           where item_type       = def_rec.item_type
           and item_key          = def_rec.item_key
           and process_activity  = def_rec.process_activity;

    end loop;

end;
/
commit;
exit;