Workflow Status Monitor Activities History Query

SELECT

  wias.process_activity                                AS PROCESS_ACTIVITY,

  wias.activity_status                                 AS ACTIVITY_STATUS,

  wa.type                                              AS ACTIVITY_TYPE,

  wa.display_name                                      AS ACTIVITY_DISPLAY_NAME,

  wa2.display_name                                     AS PARENT_ACTIVITY_DISPLAY,

  wpa.activity_name                                    AS ACTIVITY_NAME,

  wpa.process_name                                     AS PARENT_ACTIVITY,

  wias.item_type                                       AS ITEM_TYPE,

  wias.item_key                                        AS ITEM_KEY,

  wi.user_key                                          AS USER_KEY,

  wias.notification_id                                 AS NOTIFICATION_ID,

  wias.assigned_user                                   AS ASSIGNED_USER,

  wf_notification.getsubject(wias.notification_id)     AS SUBJECT,

  wf_fwkmon.getroleemailaddress(wias.assigned_user)    AS ROLE_EMAIL_ADDRESS,

  wf_directory.getroledisplayname2(wias.assigned_user) AS ROLE_DISPLAY_NAME,

  wl.meaning                                           AS

  ACTIVITY_STATUS_DISPLAY,

  wl2.meaning               AS ACTIVITY_TYPE_DISPLAY,

  wa.result_type            AS RESULT_TYPE,

  wpa.default_result        AS DEFAULT_RESULT,

  wias.due_date             AS DUE_DATE,

  wpa.instance_label        AS INSTANCE_LABEL,

  wias.begin_date           AS BEGIN_DATE,

  wias.end_date             AS END_DATE,

  wa.description            AS DESCRIPTION,

  wa.version                AS VERSION,

  wa.rerun                  AS RERUN,

  wa.expand_role            AS EXPAND_ROLE,

  wa.function               AS FUNCTION,

  wa.cost                   AS COST,

  wa.function_type          AS FUNCTION_TYPE,

  wa.runnable_flag          AS RUNNABLE_FLAG,

  wa.message                AS MESSAGE,

  wa.error_item_type        AS ERROR_ITEM_TYPE,

  wit.display_name          AS ERROR_ITEM_TYPE_DISPLAY,

  wa.error_process          AS ERROR_PROCESS,

  wpa.perform_role_type     AS PERFORM_ROLE_TYPE,

  wpa.perform_role          AS PERFORM_ROLE,

  wpa.user_comment          AS USER_COMMENT,

  wias.activity_result_code AS ACTIVITY_STATUS_RESULT_CODE,

  wf_core.activity_result(wa.result_type, DECODE(wias.activity_result_code,

  '#NULL', 'NULL', wias.activity_result_code)) AS ACT_STAT_RESULT_DISP,

  wf_core.activity_result(wa.result_type, DECODE(wpa.default_result, '#NULL',

  'NULL', wpa.default_result)) AS ACT_USAGE_RESULT_DISP,

  fl.meaning                   AS EXPAND_ROLE_DISPLAY,

  fl3.meaning                  AS FUNCTION_TYPE_DISPLAY,

  wlt.display_name             AS RESULT_TYPE_DISPLAY,

  fl2.meaning                  AS RERUN_DISPLAY,

  fl4.meaning                  AS PERFORM_ROLE_TYPE_DISPLAY,

  wpa.start_end                AS START_END,

  fl5.meaning                  AS START_END_DISPLAY,

  DECODE(waav.value_type, 'CONSTANT', TO_CHAR(waav.number_value),

  waav.text_value) AS TIMEOUT

FROM

  wf_item_activity_statuses wias,

  wf_process_activities wpa,

  wf_activities_vl wa,

  wf_activities_vl wa2,

  wf_items wi,

  wf_lookups wl,

  wf_lookups wl2,

  fnd_lookups fl,

  fnd_lookups fl2,

  fnd_lookups fl3,

  fnd_lookups fl4,

  fnd_lookups fl5,

  wf_item_types_vl wit,

  wf_lookup_types wlt,

  wf_activity_attr_values waav

WHERE

  wias.item_type           = wi.item_type

AND wias.item_key          = wi.item_key

AND wias.process_activity  = wpa.instance_id

AND wpa.activity_name      = wa.name

AND wpa.activity_item_type = wa.item_type

AND wi.begin_date BETWEEN wa.begin_date AND NVL(wa.end_date, wi.begin_date)

AND wpa.process_name      = wa2.name

AND wpa.process_item_type = wa2.item_type

AND wpa.process_version   = wa2.version

AND wias.activity_status  = wl.lookup_code

AND wl.lookup_type        = 'WFENG_STATUS'

AND wa.type               = wl2.lookup_code

AND wl2.lookup_type       = 'WFENG_ACTIVITY_TYPE'

AND wa.error_item_type    = wit.name

AND wa.expand_role        = fl.lookup_code

AND fl.lookup_type        = 'YES_NO'

AND wa.result_type        = wlt.lookup_type (+)

AND wa.rerun              = fl2.lookup_code

AND fl2.lookup_type       = 'FND_WF_ON_REVISIT'

AND DECODE(wa.type, 'FUNCTION', NVL(wa.function_type, 'PLSQL'), wa.function_type) = fl3.lookup_code (+)

AND 'FND_WF_FUNCTION_TYPE' = fl3.lookup_type (+)

AND wpa.start_end          = fl5.lookup_code (+)

AND 'FND_WF_START_END'     = fl5.lookup_type (+)

AND wpa.perform_role_type  = fl4.lookup_code

AND fl4.lookup_type        = 'FND_WF_VALUE_SOURCE'

AND wias.process_activity  = waav.process_activity_id (+)

AND '#TIMEOUT'             = waav.name (+)

AND wias.item_type        = 'REQAPPRV'

AND wias.item_key         = '146381433430'

--AND wias.process_activity = :3