Query To Get Project Change Order Approval Action History

SELECT ROWNUM,

  ACTION_DATE,

  ACTION,

  from_user,

  from_role,

  to_user,

  to_role,

  Details,

  SEQUENCE,

  NOTIFICATION_ID,

  ACTION_TYPE

FROM

  (SELECT ACTION_DATE,

    ACTION,

    from_user,

    from_role,

    to_user,

    to_role,

    Details,

    SEQUENCE,

    NOTIFICATION_ID,

    ACTION_TYPE

  FROM

    (SELECT c.comment_date DATE1,

      TO_CHAR(c.comment_date,'DD-MON-RRRR HH24:MI:SS') action_date,

      c.action action,

      c.from_user from_user,

      c.from_role from_role,

      c.to_user to_user,

      c.to_role to_role,

      c.user_comment Details,

      C.SEQUENCE SEQUENCE,

      C.NOTIFICATION_ID NOTIFICATION_ID,

      C.ACTION_TYPE ACTION_TYPE

    FROM WF_NOTIFICATIONS WFN,

      pa_wf_processes pa,

      wf_item_activity_statuses wfitems,

      wf_comments c

    WHERE wfitems.notification_id = wfn.group_id

    AND wfitems.NOTIFICATION_ID   = C.NOTIFICATION_ID

    AND pa.item_type              = wfitems.item_type

    AND pa.item_key               = wfitems.item_key

    AND pa.entity_key1            = nvl(:p_project_id,pa.entity_key1)

    AND pa.item_type             IN ('PAPROWF','PAWFCISC')

    UNION ALL

    SELECT c.comment_date DATE1,

      TO_CHAR(c.comment_date,'DD-MON-RRRR HH24:MI:SS') action_date,

      c.action action,

      c.from_user from_user,

      c.from_role from_role,

      c.to_user to_user,

      c.to_role to_role,

      c.user_comment Details,

      C.SEQUENCE SEQUENCE,

      C.NOTIFICATION_ID NOTIFICATION_ID,

      C.ACTION_TYPE ACTION_TYPE

    FROM WF_NOTIFICATIONS WFN,

      pa_wf_processes pa,

      WF_ITEM_ACTIVITY_STATUSES_H wfitems,

      wf_comments c

    WHERE wfitems.notification_id = wfn.group_id

    AND wfitems.NOTIFICATION_ID   = C.NOTIFICATION_ID

    AND pa.item_type              = wfitems.item_type

    AND pa.item_key               = wfitems.item_key

    AND pa.entity_key1            = nvl(:p_project_id,pa.entity_key1 )

    AND pa.item_type             IN ('PAPROWF','PAWFCISC')

    )

  ORDER BY DATE1 DESC,

    notification_id,

    sequence

  );