Most Frequently Used Oracle Workflow Queries!!

workflow queries for debugging,oracle workflow debugging queries

As an oracle apps technical consultant, we often deal with Oracle Workflow issues.To debug these issues we found following queries to be of immense use:

SQL Query To Find Out All Workflow Items For A Given Item Type:


SELECT item_type,
       item_key,
       to_char(begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       to_char(end_date,
               'DD-MON-RR HH24:MI:SS') end_date,
       root_activity activity
  FROM apps.wf_items
 WHERE item_type = '&item_type'
   AND end_date IS NULL
 ORDER BY to_date(begin_date,
                  'DD-MON-YYYY hh24:mi:ss') DESC;

SQL Query To Find Out All Notifications Sent By A Particular Workflow:
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type =  '&item_type'
and  wias.item_key =  '&item_key';
/

SQL Query To Find the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key:

SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/

SQL Query To Find All Errored Workflow Activities For A Particular Item Type/ Item Key:

SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/

SQL Query To Find Out Errored Process Activity Statuses For A Particular Item Type/Item Key:

SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/

SQL Query To Find Out The Errored Activities For A Particular Item Type/Item Key:

SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/

SQL Query To Find Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper('&item_type')
   AND item_key = nvl('&item_key',
                      item_key)
/

SQL Query To Find Out Number Of Deferred Workflow Activities:

SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = 'DEFERRED'
      --AND was.item_type = 'REQAPPRV'
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;

SQL Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses

SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';                          

SQL Query To Find Records That Are Pending In Each Of The Workflow Agent Listener Queues;

SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
       ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
       ' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            'and corrid like ''' || t.correlation_id || ''' ',
            NULL) || 'having count(*)>0;'
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';

SQL Query To Track The Status Of Notifications/Business Events That Are Waiting To Be Processed/That Have Errored Out:

SELECT a.user_data.geteventname(),
       decode(a.state,
              0,
              '0 = Ready',
              1,
              '1 = Delayed',
              2,
              '2 = Retained/Processed',
              3,
              '3 = Exception',
              to_char(a.state)) state,
       a.user_data.PARAMETER_LIST,
       a.user_data.event_data,
       a.user_data.event_key,
       a.*
  FROM apps.wf_deferred a
 WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
   AND rownum < 10;  

COMMENTS

BLOGGER: 2
Loading...
Name

Account Payables,1,Advance Table In OAF,1,AME,1,AOL Concepts,19,AP Holds,1,Apps Initialization,1,Blanket Purchase Orders,1,Blanket Releases,1,Careers,9,Common Errors and Solutions,2,Concurrent Programs,10,Contract Purchase Orders,1,Corporate Culture,3,Corporate Ladder,3,Customer Interface,3,Customization,1,De-Normalization,1,EAM,1,EBS Tutorials,10,Education,16,Emterprise Asset Management,1,English,1,Enterprise Asset Management,1,Entrepreneur,1,Error Fixes,1,Executables,2,Extension,1,fnd_user,1,FNDLOAD,1,Global Agreements,1,Guest Posts,7,Handy Scripts,63,Health Tips,1,Important Apps Queries,1,Infographic,3,Interview Preparation,62,Interview Q/A,68,Inventory Org ID,1,Iproc,4,Iprocurement,4,IT,3,jDeveloper,1,Job Search,1,Key Tables In Oracle Apps,5,Learning English,1,MDS,1,Metadata Tables,2,Multi Org Architecture,2,Normalization,1,O2C,1,OAF Interview,50,OAF Personalization,1,OAF Tutorials,52,Operating Unit,1,Oracle 10g,1,Oracle 11g,1,Oracle Access Manager,1,Oracle ADF,1,Oracle Alerts,2,Oracle AP,12,Oracle Apex,1,Oracle APIs,4,Oracle Application Object Library,19,Oracle Applications,10,Oracle Approvals Management,1,Oracle Apps,4,Oracle Apps DBA,2,Oracle Apps Interviews,36,Oracle Apps Tips,9,Oracle Apps Tutorials,98,Oracle AR,18,Oracle Assets,2,Oracle BAM,1,Oracle BI Publisher,1,Oracle BPEL,1,Oracle BPM,1,Oracle Dabase Structure,2,Oracle Database Architecture,2,Oracle Database Key Tables,3,Oracle EAM,2,Oracle EBS,5,Oracle Financials,2,Oracle Forms And Reports,17,Oracle Forms Interview Questions,6,Oracle Forms Tutorials,6,Oracle GL,4,Oracle Holds,1,Oracle HRMS,10,Oracle Human Resources,1,Oracle Inventory,4,Oracle Payables,1,Oracle Payroll,1,Oracle POTutorials,1,Oracle Purchasing,7,Oracle Reports Interview,9,Oracle Reports Tutorials,3,Oracle SOA,1,Oracle System Administrator,4,Oracle Workflow,4,Order Management,2,Organization ID,3,Organizations,3,Performance Tuning,2,Personalization,1,PL/SQL Block,1,PL/SQL Interview Questions,11,PL/SQL Scripts,44,PL/SQL Tutorials,53,Planned Purchase Orders,1,PO,1,PO Action History,1,PO_ACTION_HISTORY,1,POracle Purchasing,1,PR,1,Procedures And Functions,2,Profile Options,2,Purchase Orders,1,Purchase Requisition,2,Receipts,1,Request Groups,1,Responsibility,1,Shell Script,1,SOA Tutorials,4,Social Media,1,Software Industry,2,Software Tools,1,SQL And PL/SQL Interview,38,SQL Interview,52,SQL Scripts,38,SQL Tutorials,43,Standard Purchase Orders,1,Troubleshooting,1,Unexpected error,1,Unix/Linux Commands,3,User,1,Value Sets,1,Weblogic,1,XML Publisher,4,
ltr
item
Oracle Apps Guy: Most Frequently Used Oracle Workflow Queries!!
Most Frequently Used Oracle Workflow Queries!!
Most Frequently Used Oracle Workflow Queries!!As an oracle apps technical consultant, we often deal with Oracle Workflow issues.To debug these issues we found following queries to be of immense use:
http://1.bp.blogspot.com/-L4m8SieSbCI/Vht46OROq5I/AAAAAAAANpI/e_zIdsZU25o/s1600/oracle-workflow-most-important-queries.jpg
http://1.bp.blogspot.com/-L4m8SieSbCI/Vht46OROq5I/AAAAAAAANpI/e_zIdsZU25o/s72-c/oracle-workflow-most-important-queries.jpg
Oracle Apps Guy
https://www.oracleappsguy.com/2015/10/oracle-workflow-important-queries.html
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/
https://www.oracleappsguy.com/2015/10/oracle-workflow-important-queries.html
true
4186973960539901882
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share. STEP 2: Click the link you shared to unlock Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy