Oracle Workflow Queries

--> 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;