SQL Query to get Activity Statuses for all Workflow Activities of a given Item Type and Item Key in Oracle APPS

SELECT *

  FROM(

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items         wi

 WHERE wias.item_type = 'XXITEMTP'

   AND wias.item_key = 'XXITEMTP001'

   AND wias.process_activity = wpa.instance_id

   AND wpa.activity_name = wac.name

   AND wpa.activity_item_type = wac.item_type

   AND wpa.process_name = wap.name

   AND wpa.process_item_type = wap.item_type

   AND wpa.process_version = wap.version

   AND wi.item_type = 'XXITEMTP'

   AND wi.item_key = wias.item_key

   AND wi.begin_date >= wac.begin_date

   AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

UNION ALL

SELECT execution_time

      ,TO_CHAR(wias.begin_date, 'DD-MON-RR HH24:MI:SS') begin_date

  ,wap.display_name || '/' || wac.display_name activity

  ,wias.activity_status status

  ,wias.activity_result_code activity_result

  ,wias.assigned_user ass_user

  FROM wf_item_activity_statuses_h wias

      ,wf_process_activities wpa

      ,wf_activities_vl wac

      ,wf_activities_vl wap

      ,wf_items wi

  WHERE wias.item_type = 'XXITEMTP'

    AND wias.item_key = 'XXITEMTP001'

    AND wias.process_activity = wpa.instance_id

    AND wpa.activity_name = wac.name

    AND wpa.activity_item_type = wac.item_type

    AND wpa.process_name = wap.name

    AND wpa.process_item_type = wap.item_type

    AND wpa.process_version = wap.version

    AND wi.item_type = 'XXITEMTP'

    AND wi.item_key = wias.item_key

    AND wi.begin_date >= wac.begin_date

    AND wi.begin_date < nvl(wac.end_date, wi.begin_date + 1)

)

  ORDER BY begin_date

          ,execution_time