Monday 3 October 2016

Project & Change Order Approval Action History Query

/* Formatted on 10/3/2016 8:21:02 PM (QP5 v5.114.809.3010) */
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 = :p_project_id
                               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 = :p_project_id
                               AND pa.item_type IN ('PAPROWF', 'PAWFCISC'))
          ORDER BY   DATE1 DESC, notification_id, sequence);

No comments:

Post a Comment