Wednesday, July 8, 2009

Query to fetch Error workflow details for 'HRSSA' workflow

The below query will show you the error workflow for a particular time period for the item type 'HRSSA'. You can also fetch the error for other workflows too, for that instead of 'HRSSA' you need to give the item type for which you want to fetch the data.

SELECT ias.begin_date, ias.item_key, ac.NAME activity,
ias.activity_result_code RESULT, ias.error_name error_name,
ias.error_message error_message
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities ac,
wf_activities ap,
wf_items i
WHERE ias.item_type = 'HRSSA'
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 = 'HRSSA'
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)
and trunc(i.begin_date) between to_date(:begin_date) and trunc(:end_date)
ORDER BY ias.begin_date DESC

No comments: