rem HEADER
rem   $Header: wfbkgchk.sql 26.4 2001/11/05 20:03:34 ctilley ship $
rem NAME
rem   wfbkgchk.sql - WorkFlow Background Check
rem USAGE
rem   sqlplus apps/apps@db @wfbkgchk APPLSYS
rem DESCRIPTION
rem   Displays a status report on background work waiting to be processed.

REM  dbdrv: none


prompt  Please provide the workflow schema account 

def schema = '&1'

set verify off serveroutput on 
set lines 110 pages 66
col ITEM_KEY   for a30 Head "ItemKey"
col ITEM_TYPE  for a8  Head "ItemType"
col LABEL      for a30 Head "Activity Label"
col BEGIN_DATE for a18 head "Begin Date"
col DUE_DATE   for a18 head "Due Date"
col STATUS     for a8  head "Status"

prompt Defered and Wait activities in WF's Status Table ...

    select CWIAS.ITEM_TYPE ITEM_TYPE, CWIAS.ITEM_KEY ITEM_KEY, 
           CWPA.instance_id ACTID,
	   CWPA.INSTANCE_LABEL LABEL,
	   to_char(CWIAS.BEGIN_DATE, 'DD-MON-YY HH24:MI:SS') BEGIN_DATE,
	   decode(greatest(CWIAS.BEGIN_DATE ,sysdate),cwias.begin_date,'WAIT','DEFERRED') STATUS
    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.BEGIN_DATE <= SYSDATE -- uncomment this line for just deferred activities
    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'
order by 	   decode(greatest(CWIAS.BEGIN_DATE ,sysdate),cwias.begin_date,'WAIT','DEFERRED') 
/

prompt Timeout Violations...
select s.item_type, s.item_key, 
       s.activity_status,
       pa.instance_label label,              
       to_char(s.due_date, 'DD-MON-YY HH24:MI:SS') DUE_DATE
from  wf_item_activity_statuses s,
      wf_process_activities pa
where s.process_activity = pa.instance_id                                      
and   s.due_date < sysdate      
and   s.activity_status not in ('COMPLETE', 'ERROR')
/



-- this loops through all the queues
declare
	   
   event     	            system.wf_payload_t;
   dequeue_options	    dbms_aq.dequeue_options_t;
   message_properties	    dbms_aq.message_properties_t;
   i number; 
   j number; 
   msg_id    raw(16); 
   queuename varchar2(30);

begin 
  j:=1;
  loop
     if j=1 then
	queuename:=wf_queue.DeferredQueue;
     elsif j=2 then 
	queuename:=wf_queue.OutboundQueue;
     elsif j=3 then 
	queuename:=wf_queue.InboundQueue;
     end if;

     dbms_output.enable(1000000) ;
     begin
	dbms_output.put_line('The Following events are in  '||queuename);

	dequeue_options.dequeue_mode := dbms_aq.BROWSE;
	dequeue_options.wait := dbms_aq.NO_WAIT;
	dequeue_options.correlation := null;
        if (j = 1) then
          dequeue_options.consumer_name := wf_queue.account_name;
        else
          dequeue_options.consumer_name := null;
        end if;

	i:=1;

	dequeue_options.navigation   := dbms_aq.FIRST_MESSAGE;
	dbms_aq.dequeue
	(
	  queue_name => queuename,
	  dequeue_options => dequeue_options,
	  message_properties => message_properties,
	  payload => event,
	  msgid => msg_id
	);
	dbms_output.put_line('Msg     Correlation ItemType:ItemKey:Actid QueueHandleId');
	dbms_output.put_line('Msg 1 = '||
	       nvl(message_properties.correlation,'<null>')||' '||event.itemtype||':'||event.itemkey
	       ||' '||event.actid||' '||rawtohex(msg_id));


	LOOP
	   i:=i+1;
	   dequeue_options.navigation   := dbms_aq.NEXT_MESSAGE;
	   dbms_aq.dequeue
	   (
	     queue_name => queuename,
	     dequeue_options => dequeue_options,
	     message_properties => message_properties,
	     payload => event,
	     msgid => msg_id
	   );
	   dbms_output.put_line('Msg '||to_char(i)||' = '||
		  nvl(message_properties.correlation,'<null>')||' '||event.itemtype||':'||event.itemkey
		  ||' '||event.actid||' '||rawtohex(msg_id));
	 END LOOP;

	 EXCEPTION -- timeout will fall to here
	 when others then null; --dbms_output.put_line(sqlerrm);
     end;

     j:=j+1;
     EXIT when j>3;

   END LOOP;

end;
/

prompt WAIT activities still waiting to be processed in the queue
declare 
  q_version varchar2(200);
  event     	        system.wf_payload_t;
  dequeue_options	dbms_aq.dequeue_options_t;
  message_properties    dbms_aq.message_properties_t;
  msg_id                raw(16);
  i number;
  cursor curs_waiting is
     select msgid,corrid,user_data,delay
     from &schema..wf_deferred_table_m
     where q_name='WF_DEFERRED_QUEUE_M' and state=1;

begin 
    i:=0;
    for wait_rec in curs_waiting loop
      i := i+1;
      event:=wait_rec.user_data;
      dbms_output.put_line('Msg '||to_char(i)||' = '||
		  nvl(wait_rec.corrid,'<null>')||' '||event.itemtype||':'||event.itemkey
		  ||' '||event.actid||' '||to_char(wait_rec.delay, 'DD-MON-YY HH24:MI:SS'));
    end loop;
end;
/
prompt Errored activities on the queue (there should not be any)
declare 
  q_version varchar2(200);
  event     	        system.wf_payload_t;
  dequeue_options	dbms_aq.dequeue_options_t;
  message_properties    dbms_aq.message_properties_t;
  msg_id                raw(16);
  i number;
  cursor curs_waiting is
     select msgid,corrid,user_data
     from &schema..wf_deferred_table_m
     where q_name='WF_DEFERRED_QUEUE_M' and state > 1; --0=READY 1==WAIT
begin 
    i:=0;
    for wait_rec in curs_waiting loop
      i := i+1;
      event:=wait_rec.user_data;
      dbms_output.put_line('Msg '||to_char(i)||' = '||
		  nvl(wait_rec.corrid,'<null>')||' '||event.itemtype||':'||event.itemkey
		  ||' '||event.actid||' '||rawtohex(wait_rec.msgid));

    end loop;
end;
/

commit;
exit;