Query To Find Out Workflow Error out Process Activity Status 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,
       wias.activity_status status,
      wias.activity_result_code RESULT,
       wias.assigned_user ass_user
  FROM wf_item_activity_statuses wias,
       wf_process_activities     wpa,
       wf_activities_vl          wac,
       wf_activities_vl          wap,
       wf_items                  wi
 WHERE wias.item_type = wi.item_type
   AND wias.item_key = wi.item_key
   AND wias.process_activity = wpa.instance_id
   AND wpa.activity_name = wac.name
   AND wpa.activity_item_type = wac.item_type
   AND wpa.process_name = wap.name
   AND wpa.process_item_type = wap.item_type
   AND wpa.process_version = wap.version
   AND wi.parent_item_type = ‘&item_type’
   AND wi.parent_item_key = ‘&item_key’
   AND wi.begin_date >= wac.begin_date
   AND wi.begin_date < nvl(wac.end_date,
                          wi.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(wias.begin_date,
               ‘DD-MON-RR HH24:MI:SS’) begin_date,
       wap.display_name || ‘/’ || wac.display_name activity,
       wias.activity_status status,
       wias.activity_result_code RESULT,
       wias.assigned_user ass_user
  FROM wf_item_activity_statuses_h wias,
       wf_process_activities       wpa,
       wf_activities_vl            wac,
       wf_activities_vl           wap,
       wf_items                    wi
 WHERE wias.item_type = wi.item_type
   AND wias.item_key = wi.item_key
   AND wias.process_activity = wpa.instance_id
   AND wpa.activity_name = wac.name
   AND wpa.activity_item_type = wac.item_type
   AND wpa.process_name = wap.name
   AND wpa.process_item_type = wap.item_type
   AND wpa.process_version = wap.version
   AND wi.parent_item_type = ‘&item_type’
   AND wi.parent_item_key = ‘&item_key’
   AND wi.begin_date >= wac.begin_date
   AND wi.begin_date < nvl(wac.end_date,
                          wi.begin_date + 1)
 ORDER BY 2,
          1
/
  • September 24, 2018 | 15 views
  • Comments