REM dbdrv: none
REM $Header: wfaqrenq.sql 26.0 2005/02/28 11:51:23 dmani noship $
REM +=========================================================================+
REM |            Copyright (c) 2005, Oracle. All rights reserved.             |
REM +=========================================================================+
REM 
REM NAME
REM   wfaqrenq.sql - WorkFlow AQ Re-ENQueue script
REM
REM DESCRIPTION
REM   This script re-enqueues messages that are backed up by wfaqback.sql script.
REM   wfaqback.sql creates a WF_QUEUE_TEMP_TABLE and backs up the specified
REM   event messages. This script takes the messages from the TEMP table and
REM   re-enqueues them to the Agent. It Supports messages of WF_EVENT_T type.
REM
REM USAGE
REM   sqlplus apps/<apps pwd> @wfaqrenq.sql <Agent Name>
REM +=========================================================================+

set verify off;
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;

declare
  l_agent      wf_agent_t;
  l_rows       integer;
  l_agent_name varchar2(30) := upper('&1');
  
  TYPE t_user_data_tbl IS TABLE OF WF_EVENT_T INDEX BY BINARY_INTEGER;
  l_user_data_tbl t_user_data_tbl;

  CURSOR c_bkup_msg(p_queue_name in varchar2) IS
  SELECT user_data
  FROM   wf_queue_temp_table
  WHERE  queue = p_queue_name
  ORDER BY enq_time;

begin
  l_agent := wf_agent_t(l_agent_name, Wf_Event.local_system_name);
  l_rows := 5000;

  open c_bkup_msg(l_agent_name);

  loop
    FETCH c_bkup_msg BULK COLLECT INTO l_user_data_tbl LIMIT l_rows;
  
    exit when l_user_data_tbl.COUNT = 0;

    for i in 1..l_user_data_tbl.LAST loop
      if (upper(l_agent_name) = 'WF_ERROR') then
        Wf_Error_Qh.Enqueue(l_user_data_tbl(i), l_agent);  
      else
        Wf_Event.Enqueue(l_user_data_tbl(i), l_agent);
      end if;
    end loop;
    commit;
    l_user_data_tbl.DELETE;
  end loop;
  
  close c_bkup_msg;
end;
/
commit;
exit;