Monday, May 8, 2017

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

  );

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect