rem HEADER
rem   $Header: wfver.sql 26.26 2004/10/20 07:17:29 anachatt ship $
rem NAME
rem   wfver.sql - WorkFlow VERsion display
rem USAGE
rem   @wfver
rem DESCRIPTION
rem   Displays version information for all WF source.
rem   Manual Script
rem
rem   dbdrv: none
rem
SET VERIFY OFF;
SET SERVEROUTPUT ON SIZE 1000000;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
set pagesize 999
set linesize 80
set arraysize 1
rem

col COMPONENT_TYPE   for a20 head "Type"
col CLASS_NAME       for a40 head "Class"
col COMPONENT_NAME   for a35 head "Name"
col COMPONENT_STATUS for a20 head "Status"
col STATUS_INFO      for a10 head "Info"

col DETAILS          for a70 head "Status Details"
col NAME             for a30 head "Name"
col TYPE             for a12 head "Type"
col STATUS           for a10 head "Status"
col VERSION          for a30 head "Version"
col VIEW_TEXT        for a35 head "View Text"
col VALUE            for a35 head "Value"
col OWNER            for a7  head "Owner"

col QUEUE_NAME       for a19 head "Name"
col QUEUE_STATUS     for a7  head "Status"
col RETENTION        for a9  head "Retention"
col ENQUEUE          for a7  head "Enqueue"
col DEQUEUE          for a7  head "Dequeue"
col RETRIES          for a7  head "Retries"
col RETRY_DELAY      for a7  head "Delay"


COL QUEUE              format a20     head "Queue Name"
COL INDEX_NAME         format a25     head "Index Name"
COL COLUMN_NAME        format a10     head "Column"
COL LAST_ANALYZED      format a17     head "Last Analyzed"

col EVENT_NAME       for a50 head "Name"
col EVENT_TYPE       for a7 head "Type"
col EVENT_STATUS     for a10 head "Status"
col LICENSED         for a8  head "Licensed"

col ITT_NAME         for a8  head "Name"
col DISPLAY_NAME     for a30 head "Display Name"
col LANGUAGE         for a5  head "Lang"

col AGENT_NAME       FOR a30 head "Agent Name"
col SYSTEM_NAME      FOR a30 head "System Name"

col RULE_FUNCTION    FOR a30 head "Subscription Rule Function"
col OUT_AGENT        FOR a30  head "Subscription Out Agent"
col XML_VERSION      FOR a51 head "Version"

variable owner_name varchar2(30);

begin
  --take the value into bind rather than do the 
  --API call each time to get the token value
  :owner_name := wf_core.translate('WF_SCHEMA');
end;
/

prompt Server Version (last official install)
select TEXT Version
from   WF_RESOURCES
where  TYPE = 'WFTKN' and NAME = 'WF_VERSION'
and    LANGUAGE = 'US'
/
prompt
prompt Local System Name 
prompt
declare
  l_cnt number;
  l_sys_name VARCHAR2(30);
begin
  select count(1) into l_cnt
  from  WF_RESOURCES
  where  TYPE = 'WFTKN' and NAME = 'WF_SYSTEM_GUID'
  and    LANGUAGE = 'US';
  
  if (l_cnt > 0) THEN
    SELECT WF_EVENT.LOCAL_SYSTEM_NAME INTO l_sys_name FROM dual;
    
    dbms_output.put_line('The local System is ASSIGNED to ' || l_sys_name );
  else
    dbms_output.put_line('Local System is UNASSIGNED');
  end if;
end;
/
prompt
prompt Local System Status
select TEXT Status 
from   WF_RESOURCES
where  TYPE = 'WFTKN' and NAME = 'WF_SYSTEM_STATUS'
and    LANGUAGE = 'US'
/
prompt Database Instance Details
select instance_name NAME, version, database_status STATUS 
from v$instance
/
prompt Database Init.ora Parameter Values
select name NAME, value Value
from v$parameter
where upper(name) in ('AQ_TM_PROCESSES','JOB_QUEUE_PROCESSES','JOB_QUEUE_INTERVAL', 
                      'UTL_FILE_DIR', 'NLS_LANGUAGE', 'NLS_TERRITORY')
order by 1
/

prompt XML Parser version
select WF_EVENT_XML.XMLVersion() XML_VERSION
from sys.dual
/

prompt XML Parser PL/SQL Package Installation Status
select 'INSTALLED and '||status||' in '||owner||' Schema' DETAILS 
from all_objects
where object_name = 'XMLDOM'
and object_type = 'PACKAGE'
/
prompt XML Parser Schema Java Class Version
select object_name||' INSTALLED and '||status||' in '||owner||' Schema' DETAILS
from all_objects
where object_type = 'JAVA RESOURCE'
and object_name like '%xmlparser%'
/
REM Owner must be either be Workflow Schema or the current user schema
prompt Queue Information
select q.OWNER OWNER, q.NAME QUEUE_NAME, o.STATUS QUEUE_STATUS, q.ENQUEUE_ENABLED ENQUEUE, q.DEQUEUE_ENABLED DEQUEUE, to_char(q.MAX_RETRIES) RETRIES, q.RETENTION, to_char(q.RETRY_DELAY) RETRY_DELAY
from   ALL_QUEUES q, DBA_OBJECTS o
where (q.NAME = o.OBJECT_NAME and o.object_type = 'QUEUE') 
and   (q.NAME LIKE 'WF%' OR q.NAME LIKE 'ECX%')
and   (q.owner = :owner_name OR q.owner = user)
and    q.owner = o.owner
order by q.NAME
/
prompt Queue Index List Information

break on queue skip 1 on index_name on dist_keys on last_analyzed

select q.name QUEUE, i.index_name INDEX_NAME, i.LAST_ANALYZED ,
       ic.COLUMN_NAME
  from ALL_QUEUES q,
       ALL_INDEXES i,
       ALL_IND_COLUMNS ic
 where i.index_name = ic.index_name
   and i.table_name = q.queue_table
   and (q.name like 'WF%' or q.name like 'ECX%')
   and (q.owner = :owner_name OR q.owner = user)
   and (i.owner = :owner_name OR q.owner = user)
  order by q.name, i.index_name, ic.column_position
/
clear breaks


prompt Queue Subscriber Rule View Status 

declare
  l_sub_count number;
  l_queue_name  VARCHAR2(30);
  l_name        VARCHAR2(30);
  l_ADDRESS  VARCHAR2(1024);
  l_PROTOCOL  NUMBER;

  type subinfo_t is ref cursor;
  c_subinfo subinfo_t;

  cursor  c_queues
  is
    select owner , object_name
    from   dba_objects
    where  (object_name like 'AQ$_WF%_S'
       or  object_name like 'AQ$_ECX%_S') 
    AND (object_name not like 'AQ$_WF_CONTROL%_S')
    AND (owner = :owner_name or owner = user);

begin

  dbms_output.put_line('--------------------------------------------------------------------------');

  for l_queue in c_queues loop

    begin

      l_sub_count := 0;
      --Here we take relevant information of the view AQ$<queue_name>_s 
      --only though we have more info on underlying AQ$_<queue_name>_s
      --We use AQ$_<queue_name>_s to avoid issues with 9.2.0.2 db
      open c_subinfo for
        'select  QUEUE_NAME,NAME ,ADDRESS ,PROTOCOL from '||l_queue.owner||'.'||l_queue.object_name;


      dbms_output.put_line('Queue_Name Name  Address  Protocol ');

      dbms_output.put_line('--------------------------------------------------');

      dbms_output.put_line(l_queue.object_name||' Subscriber Information:');
      dbms_output.put_line('--------------------------------------------------');

      loop
         fetch c_subinfo into l_QUEUE_NAME, L_NAME, L_ADDRESS, l_PROTOCOL ;

          exit when c_subinfo%notfound;

          l_sub_count := l_sub_count + 1;
          dbms_output.put_line('Queue Name : '||l_QUEUE_NAME ||', Name : '||L_NAME||', Address : '||L_ADDRESS||', Protocol : '||l_PROTOCOL);
      end loop;
      close c_subinfo;


      if l_sub_count = 0 then
        dbms_output.put_line('(none)');
      end if;

      dbms_output.put_line('--------------------------------------------------');

    exception
      when others then
        if c_subinfo%isopen then
          close c_subinfo;
        end if;
    end;
    dbms_output.put_line('.');
  end loop; 
exception
  when others then
  if c_queues%isopen then
    close c_queues;
  end if;

  if c_subinfo%isopen then
    close c_subinfo;
  end if;
end;
/


prompt Queue Subscriber View Rules
prompt

declare
  l_name  varchar2(30);
  l_rule  varchar2(4000);
  l_rule_count number;

  type rules_t is ref cursor;
  c_rules rules_t;
  curs_920  rules_t;

  cursor  c_subscribers
  is
    select owner, object_name
    from dba_objects 
    where object_name like 'AQ$WF%_R'
       or object_name like 'AQ$ECX%_R' 
      and (owner = :owner_name or owner = user)
    order by 1, 2;

  l_object  varchar2(1000);
  l_sql     varchar2(1000);
  l_version   VARCHAR2(17);

begin
  --Check the version here
  select   version
  into     l_version
  from     v$instance ;

  for r_subscriber in c_subscribers loop

    begin

      l_rule_count := 0;
      open c_rules for
        'select name, rule ' ||
        'from ' || r_subscriber.owner || '.' || r_subscriber.object_name;


        dbms_output.put_line(r_subscriber.object_name || ' Rules:');
        dbms_output.put_line('----------------------------------');

        loop
          fetch c_rules into l_name, l_rule;
          exit when c_rules%notfound;

          l_rule_count := l_rule_count + 1;

          dbms_output.put_line('Name: ' || l_name || ', Rule: ' || l_rule);
        end loop;

      close c_rules;

      if l_rule_count = 0 then
        --Check the db version if its 9.2.0.x we need
        --to workaround and check the dba tables aswell
        if (instr(l_version,'9.2.0') > 0) then

          --Lets create the cursor statement here
          --For that first get the subscriber object corresponding to the rule
          l_object:= 'AQ$_'||substr(r_subscriber.object_name,4,length(r_subscriber.object_name));
          l_object := substr(l_object,1,length(l_object)-1)||'S';


          l_sql := 'SELECT  s.name NAME , rule_condition RULE FROM  '||
               r_subscriber.owner||'.'||l_object||
               ' s , dba_rules r   WHERE (bitand(s.subscriber_type, 1) = 1)  '||

               'AND  s.rule_name =   r.rule_name and r.rule_owner = '||
               ''''||r_subscriber.owner||'''';

          OPEN curs_920 FOR l_sql ;
            LOOP
              FETCH curs_920 INTO l_name , l_rule;
              EXIT WHEN curs_920%NOTFOUND;
              -- process record
              l_rule_count := l_rule_count + 1;
              dbms_output.put_line('Name: ' || l_name || ', Rule: ' || l_rule);
              END LOOP;

           CLOSE curs_920;

           if (l_rule_count = 0) then
             dbms_output.put_line('(none)');
           end if;

      else
        dbms_output.put_line('(none)');
      end if;
    end if; 
      dbms_output.put_line('.');
    exception 
      when others then
        if c_rules%isopen then
          close c_rules;
        end if;
    end;
  end loop;

exception when others then

  if c_subscribers%isopen then
    close c_subscribers;
  end if;

  if c_rules%isopen then
    close c_rules;
  end if;
end;
/

DECLARE
  l_owner VARCHAR2(30);
  l_object_name VARCHAR2(30);
  l_sub_count NUMBER;
  l_type VARCHAR2(30);
  type subinfo_t is ref cursor;
  c_subinfo subinfo_t;

begin

  --Add the owner clause
  SELECT owner, object_name INTO l_owner, l_object_name
  FROM   dba_objects
  WHERE  object_name LIKE 'AQ$_WF_CONTROL%_S'
  AND    owner = :owner_name;
  
  dbms_output.put_line(l_owner || '.' || l_object_name||' Subscriber Information:');
      
  BEGIN
      OPEN c_subinfo for
        'select  count(1), subscriber_type from '|| l_owner||'.'||l_object_name 
        || ' group by subscriber_type ';
      
      DBMS_OUTPUT.PUT_LINE(' SUBSCRIBER TYPE ' || '     ' || ' COUNT ');
      dbms_output.put_line('--------------------------------------------------');
      LOOP
         FETCH c_subinfo INTO l_sub_count, l_type;
         EXIT WHEN c_subinfo%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(l_type  || '         ' || l_sub_count);
      END LOOP;
      
      CLOSE c_subinfo;

      dbms_output.put_line('--------------------------------------------------');

  EXCEPTION
     WHEN others then
        if c_subinfo%isopen then
          close c_subinfo;
        end if;
  END; 
exception
  when NO_DATA_FOUND then
       DBMS_OUTPUT.PUT_LINE('Warning: Database Object for Control Queue subscriber doesnot exist ');     
end;
/
prompt Invalid PL/SQL Objects
select UO.OBJECT_NAME NAME, 
       UO.OBJECT_TYPE TYPE,
       UO.STATUS STATUS
from   USER_OBJECTS UO
where  (UO.OBJECT_NAME like 'WF%' OR UO.OBJECT_NAME like 'ECX%' OR UO.OBJECT_NAME like 'FND_SVC_%')
and    UO.OBJECT_TYPE in ('PACKAGE', 'PACKAGE BODY')
and    UO.STATUS <> 'VALID'
order by 1, 2
/

prompt PL/SQL Version Information
select US.NAME NAME,
       US.TYPE TYPE,
       substr(US.TEXT, instr(US.TEXT, '$Header')+9, 
	      instr(substr(US.TEXT, instr(US.TEXT, '$Header')+9), 
		    ' ', 1, 3)-1) VERSION
from   USER_SOURCE US
where  (US.NAME like 'WF%' OR US.NAME like 'ECX%' OR US.NAME like 'FND_SVC_%')
and    US.TYPE in ('PACKAGE', 'PACKAGE BODY')
and    US.TEXT like '%$Header%'
order by 1, 2
/

prompt View Version Information
select UO.OBJECT_NAME NAME, 
       UO.STATUS STATUS, 
       UV.TEXT VIEW_TEXT
from   USER_OBJECTS UO, USER_VIEWS UV
where  (UO.OBJECT_NAME like 'WF%' OR UO.OBJECT_NAME like 'ECX%' OR UO.OBJECT_NAME like 'FND_SVC_%')
and    UO.OBJECT_TYPE in ('VIEW')
and    UV.VIEW_NAME (+) = UO.OBJECT_NAME
order by 2, 1
/

prompt Workflow Definition (WFT) Version Information
SELECT name itt_name, display_name, language,
       substr(description, instr(description, '$Header:', 1)+9,
             instr(substr(description, instr(description, '$Header:')+9), ' ', 1, 3)) version
FROM   wf_item_types_tl
WHERE  instr(description, '$Header:', 1) > 0
AND    (name like 'WF%' OR name like 'ECX%')
UNION ALL
SELECT name itt_name, display_name, language,
       substr(description, instr(description, '$Revision:', 1)+11,
             instr(substr(description, instr(description, '$Revision:')+11), ' ', 1, 1)) version
FROM   wf_item_types_tl
WHERE  instr(description, '$Revision:', 1) > 0
AND    (name like 'WF%' OR name like 'ECX%')
/

prompt
prompt Generated Static Engine Calls status
prompt If package WF_FUNCTION_CALL is INVALID please recompile the
prompt file $FND_TOP/patch/115/sql/wffncalb.pls and make sure there are no invalid
prompt objects left
select UO.OBJECT_NAME NAME, 
       UO.OBJECT_TYPE TYPE,
       UO.STATUS STATUS
from   USER_OBJECTS UO
where  (UO.OBJECT_NAME like 'WF_ENGINE%' or UO.OBJECT_NAME = 'WF_FUNCTION_CALL')
and    UO.OBJECT_TYPE in ('PACKAGE', 'PACKAGE BODY')
order by 1, 2

/

PROMPT Service Component Agent Information

SELECT a.NAME AGENT_NAME , s.NAME SYSTEM_NAME, a.status STATUS
FROM   WF_AGENTS a, WF_SYSTEMS s
WHERE  a.system_guid = s.guid
AND    a.name IN ('WF_CONTROL', 'WF_NOTIFICATION_IN', 'WF_NOTIFICATION_OUT', 
                  'WF_DEFERRED', 'WF_ERROR')
/

prompt Service Component Control Events
select E.NAME EVENT_NAME, E.TYPE EVENT_TYPE, E.STATUS EVENT_STATUS, decode(E.LICENSED_FLAG,'Y','Yes','No') LICENSED
from WF_EVENTS E
where (E.NAME like 'oracle.apps.fnd.cp.gsc.%' OR
       E.NAME like 'oracle.apps.fnd.wf.mailer.Mailer.%' OR
       E.NAME like 'oracle.apps.fnd.wf.bes.AgentListener.%')
order by 1, 2
/

prompt Notification Subsystem Events
select E.NAME EVENT_NAME, E.TYPE EVENT_TYPE, E.STATUS EVENT_STATUS, decode(E.LICENSED_FLAG,'Y','Yes','No') LICENSED
from WF_EVENTS E
where (E.NAME like 'oracle.apps.wf.notification.%')
order by 1, 2

/

PROMPT Service Component Control Event Subscriptions
SELECT e.name EVENT_NAME, DECODE(sub.guid, NULL, 'Subscription Not Defined', 
                                           DECODE(sub.rule_function, NULL, 'Not  Defined', sub.rule_function || '@' || s.name)) RULE_FUNCTION,
       DECODE(sub.guid, NULL, 'Subscription Not Defined', 
                        DECODE(sub.out_agent_guid, NULL, 'Not Defined',
                                                   oa.name || '@' || oas.name)) OUT_AGENT,
       sub.status STATUS
FROM   WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
WHERE  e.NAME IN  ('oracle.apps.wf.notification.send.group',
                   'oracle.apps.fnd.cp.gsc.bes.control.group',
                   'oracle.apps.wf.notification.summary.send')
AND    e.guid = sub.event_filter_guid(+) 
AND    sub.licensed_flag(+) = 'Y' 
AND    e.licensed_flag = 'Y' 
AND    sub.system_guid = s.guid(+)
AND    oa.guid(+) = sub.out_agent_guid
AND    oa.system_guid = oas.guid(+)
ORDER BY e.name
/
prompt Service Component Types
select SCTB.COMPONENT_TYPE COMPONENT_TYPE, SCTB.COMPONENT_CLASS_NAME CLASS_NAME
from FND_SVC_COMP_TYPES_B SCTB
order by 1

/

prompt Service Components
select SC.COMPONENT_TYPE, SC.COMPONENT_NAME, SC.COMPONENT_STATUS
from FND_SVC_COMPONENTS SC
order by 1, 2

/

prompt Notification Subsystem Queue Info
prompt This requires package WF_QUEUE to be valid
prompt ______________________________________________________________________________

declare
  l_readyc          NUMBER;
  l_waitc           NUMBER;
  l_processedc      NUMBER;
  l_expiredc        NUMBER;
  l_undeliverablec  NUMBER;
  l_errorc          NUMBER;
begin

    WF_QUEUE.getCntMsgSt
        (p_agent        => 'WF_DEFERRED',
         p_ready        => l_readyc,
         p_wait         => l_waitc,
         p_processed    => l_processedc,
         p_expired      => l_expiredc,
         p_undeliverable => l_undeliverablec,
         p_error        => l_errorc);
    dbms_output.put_line('WF_DEFERRED messages ready:' || l_readyc || ', waiting:' || l_waitc || ', expired:'|| l_expiredc || ', undeliverable:' || l_undeliverablec || ', processed:' || l_processedc);

    WF_QUEUE.getCntMsgSt
        (p_agent        => 'WF_NOTIFICATION_OUT',
         p_ready        => l_readyc,
         p_wait         => l_waitc,
         p_processed    => l_processedc,
         p_expired      => l_expiredc,
         p_undeliverable => l_undeliverablec,
         p_error        => l_errorc);
    dbms_output.put_line('WF_NOTIFICATION_OUT messages ready:' || l_readyc || ', waiting:' || l_waitc || ', expired:'|| l_expiredc || ', undeliverable:' || l_undeliverablec || ', processed:' || l_processedc);

    WF_QUEUE.getCntMsgSt
        (p_agent        => 'WF_NOTIFICATION_IN',
         p_ready        => l_readyc,
         p_wait         => l_waitc,
         p_processed    => l_processedc,
         p_expired      => l_expiredc,
         p_undeliverable => l_undeliverablec,
         p_error        => l_errorc);
    dbms_output.put_line('WF_NOTIFICATION_IN messages ready:' || l_readyc || ', waiting:' || l_waitc || ', expired:'|| l_expiredc || ', undeliverable:' || l_undeliverablec || ', processed:' || l_processedc);

    WF_QUEUE.getCntMsgSt
        (p_agent        => 'WF_ERROR',
         p_ready        => l_readyc,
         p_wait         => l_waitc,
         p_processed    => l_processedc,
         p_expired      => l_expiredc,
         p_undeliverable => l_undeliverablec,
         p_error        => l_errorc);
    dbms_output.put_line('WF_ERROR messages ready:' || l_readyc || ', waiting:' || l_waitc || ', expired:'|| l_expiredc || ', undeliverable:' || l_undeliverablec || ', processed:' || l_processedc);

end;
/

@@wfverapp.sql

set verify on
commit;
exit;