Monday, July 13, 2009

Query to find the workflow override address in test instances

SELECT fsc.component_name, fsc.component_status, fsc.startup_mode,
fscpv.parameter_value
FROM fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv,
fnd_svc_components fsc
WHERE fscpt.display_name = 'Test Address'
AND fscpt.parameter_id = fscpv.parameter_id
AND fscpv.component_id = fsc.component_id;

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