Tuesday, July 3, 2007

Check Work Flow Activity In Error

I have used This Query to check Error in Purchase Order Work flow(Mean How Much PO stuck in work flow)

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;