SELECT DISTINCT wi.item_type item_type, wi.item_key item_key,
wpa.process_name
|| ':'
|| wpa.instance_label error_process_activity_label,
wf_engine.getitemattrtext ('WFERROR',
wi.item_key,
'ERROR_ITEM_TYPE'
) errant_item_type,
wf_engine.getitemattrtext ('WFERROR',
wi.item_key,
'ERROR_ITEM_KEY'
) errant_item_key
FROM wf_items wi,
wf_item_activity_statuses wias,
wf_item_activity_statuses wias1,
wf_process_activities wpa
WHERE wi.item_type = 'WFERROR'
AND wi.end_date IS NULL
AND TO_NUMBER (wf_engine.getitemattrtext ('WFERROR',
wi.item_key,
'ERROR_ACTIVITY_ID'
)
) = wias.process_activity
AND wf_engine.getitemattrtext ('WFERROR',
wi.item_key,
'ERROR_ITEM_TYPE'
) = wias.item_type(+)
AND wf_engine.getitemattrtext ('WFERROR',
wi.item_key,
'ERROR_ITEM_KEY'
) = wias.item_key(+)
AND wias.activity_status(+) <> 'ERROR'
AND wias1.item_type = wi.item_type
AND wias1.item_key = wi.item_key
AND wias1.end_date IS NULL
AND wias1.notification_id IS NOT NULL
AND wias1.process_activity = wpa.instance_id;