--Query to get the Workflow
Status
SELECT wfs.item_key,
wfa.display_name process,
wfa1.display_name activity,
--apps.wf_core.activity_result(wfa1.result_type,wfs.activity_result_code)
-- result,
lkp.meaning act_status,
wfs.notification_id notif,
wfp.process_name i_process,
wfp.activity_name i_activity,
TO_CHAR (wfs.begin_date, 'dd-mon-rr_hh24:mi:ss') begin_d,
TO_CHAR (wfs.end_date, 'dd-mon-rr_hh24:mi:ss') end_d,
wfs.error_name error_name,
wfs.due_date
FROM apps.wf_item_activity_statuses wfs,
apps.wf_process_activities wfp,
apps.wf_activities_vl wfa,
apps.wf_activities_vl wfa1,
apps.wf_lookups lkp
WHERE 1 = 1
AND wfs.item_type = 'OEOH'
AND wfs.item_key = TO_CHAR (342168)
AND wfs.process_activity = wfp.instance_id
AND wfp.process_item_type = wfa.item_type
AND wfp.process_name = wfa.name
AND wfp.process_version = wfa.version
AND wfp.activity_item_type = wfa1.item_type
AND wfp.activity_name = wfa1.name
AND wfa1.version =
(SELECT MAX (version)
FROM apps.wf_activities wf2
WHERE wf2.item_type = wfp.activity_item_type
AND wf2.name = wfp.activity_name)
AND lkp.lookup_type = UPPER ('wfeng_status')
AND lkp.lookup_code = wfs.activity_status
ORDER BY wfs.item_key, wfs.begin_date, execution_time;
--> Select all workflow
items for a given item type
SELECT item_type,
item_key,
TO_CHAR (begin_date, 'DD-MON-RR HH24:MI:SS') begin_date,
TO_CHAR (end_date, 'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = 'ARCMREQ' --Enter Internal Name Of Your Work Flow
AND end_date IS NULL
ORDER BY TO_DATE (begin_date, 'DD-MON-YYYY hh24:mi:ss') DESC;
--> Notifications sent by
a given workflow
SELECT wn.notification_id
nid,
wn.context,
wn.GROUP_ID,
wn.status,
wn.mail_status,
wn.MESSAGE_TYPE,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
FROM wf_notifications wn, wf_item_activity_statuses
wias
WHERE wn.GROUP_ID = wias.notification_id
AND wias.item_type = 'ARCMREQ' --Enter Internal Name Of Your Work Flow
AND wias.item_key IN ('241') --Enter Tem Key of Your
Workflow Item, you can keep this blank and check for Subject of notification
/
--> Find the Activity
Statuses for all workflow activities of a given item type and 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,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'ARCMREQ' --Enter Internal Name Of Your Work Flow
AND ias.item_key = '66' ----Enter Tem Key of Your Workflow Item
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 = 'ARCMREQ'
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)
UNION ALL
SELECT execution_time,
TO_CHAR (ias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h
ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'ARCMREQ' ----Enter Internal Name Of
Your Work Flow
AND ias.item_key = '66' --Enter Tem Key of Your Workflow Item
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 = 'ARCMREQ' --Enter Internal Name Of Your Work Flow
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)
ORDER BY 2, 1
/
--> Get a list of all
Errored Workflow Activities for a given item type/ item key
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses
ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'ARCMREQ' ----Enter Internal Name Of
Your Work Flow
AND ias.item_key = '66' --Enter Tem Key of Your Workflow Item
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 = 'ARCMREQ' ----Enter Internal Name Of
Your Work Flow
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)
ORDER BY ias.execution_time
/
--> Error Process Errored
Activities
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses
ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
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.parent_item_type = 'ARCMREQ'
AND i.parent_item_key = '66'
AND i.begin_date >= ac.begin_date
AND i.begin_date < NVL (ac.end_date, i.begin_date + 1)
ORDER BY ias.execution_time
/
--> Attribute Values
SELECT NAME attr_name,
NVL (text_value, NVL (TO_CHAR (number_value), TO_CHAR (date_value)))
VALUE
FROM wf_item_attribute_values
WHERE item_type = UPPER ('ARCMREQ') AND item_key = NVL ('221', item_key)
/
--> Count of all workflow
deferred activities based
SELECT COUNT (1), was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
AND was.item_type = 'ARCMREQ'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
-----The below script will
launch or kickoff the desired workflow from PL/SQL code
DECLARE
v_itemtype VARCHAR2 (50);
v_itemkey VARCHAR2 (50);
v_process VARCHAR2 (50);
v_userkey VARCHAR2 (50);
BEGIN
v_itemtype := 'DEMOIT';
v_itemkey := '1233';
v_userkey := '1233';
v_process := 'DEMOPROCESS';
WF_ENGINE.Threshold := -1;
WF_ENGINE.CREATEPROCESS (v_itemtype, v_itemkey, v_process);
wf_engine.setitemuserkey (v_itemtype, v_itemkey, v_userkey);
wf_engine.setitemowner (v_itemtype, v_itemkey, 'SYSADMIN');
WF_ENGINE.STARTPROCESS (v_itemtype, v_itemkey);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;