Workflow

Query to identify the workflow admin responsibility that can access all workflows in system

select rtl.responsibility_name,r.responsibility_key
from
(
select substr (wr.text,instr(wr.text,'|',1,2)+1,instr(wr.text,'|',1,3)-(instr(wr.text,'|',1,2)+1)) resp_key
from apps.WF_RESOURCES wr
WHERE wr.NAME = 'WF_ADMIN_ROLE'
and wr.language = 'US'
) rk,
apps.fnd_responsibility r,
apps.fnd_responsibility_tl rtl
where rk.resp_key = r.responsibility_key
and rtl.responsibility_id = r.responsibility_id
and rtl.language = userenv('LANG');

Query to check workflow mailer status

select component_name,component_status,last_update_date
from apps.fnd_svc_components;

Query to check workflow deferred queue for an item type

SELECT NVL(SUBSTR(wfe.user_data.itemtype, 1, 50), 'NULL - No Value') itemtype,
       DECODE(WFE.STATE,
              0,
              '0 = Ready',
              1,
              '1 = Delayed',
              2,
              '2 = Retained',
              3,
              '3 = Exception',
              TO_CHAR(SUBSTR(WFE.STATE, 1, 12))) STATE,
       COUNT(*) COUNT,
       MIN(enq_time+(2/24)) min_enq_time,
       MAX(enq_time+(2/24)) max_enq_time
  FROM  apps.wf_deferred_table_m WFE
 WHERE wfe.user_data.itemtype = 'OEOL'
 GROUP BY wfe.user_data.itemtype, WFE.STATE
ORDER BY 1;

Query to get subscriptions for a business event

SELECT E.NAME,
       E.STATUS EVENT_STATUS,
       S.STATUS SUBSCRIPTION_STATUS,
       NVL(S.PHASE, 0) SUBSCRIPTION_PHASE,
       (CASE 
            WHEN NVL(s.phase,0) <= 99 THEN 'Synchronous'
            ELSE 'Asynchronous'
            END
       )synchronous_or_Asynchronous,
       S.LICENSED_FLAG SUBSCRIPTION_LICENSED_FLAG,
       E.LICENSED_FLAG EVENT_LICENSED_FLAG,
       s.rule_function,
       wf_process_type,
       wf_process_name,
       substr(s.rule_function,1,INSTR(s.rule_function,'.',1,1)-1) plsql_pkg,
       (SELECT status
         FROM dba_objects
        WHERE object_name = upper(substr(s.rule_function,1,INSTR(s.rule_function,'.',1,1)-1))
          AND object_type LIKE 'PACKAGE BODY'
          AND owner NOT LIKE 'XX%'
       ) pkg_status
  FROM apps.WF_EVENTS E, 
       apps.WF_EVENT_SUBSCRIPTIONS S
 WHERE 1 = 1
   AND E.NAME LIKE 'oracle.apps.fnd.concurrent.request.completed' --event name
   AND S.EVENT_FILTER_GUID = E.GUID
   AND s.rule_function LIKE 'xxpickcsliprpt.%' --package name
  ORDER BY e.name;