Workflow Tables

WORKFLOW TABLES

SELECT * FROM WF_ITEMS

SELECT * FROM WF_ITEM_ACTIVITY_STATUSES

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS

SELECT * FROM WF_USER_ROLES

SELECT * FROM WF_ROLES

SELECT * FROM WF_ITEM_ATTRIBUTES

SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES

SELECT * FROM WF_ITEM_ATTRIBUTES_TL

SELECT * FROM WF_ACTIVITIES

SELECT * FROM WF_ACTIVITIES_TL

SELECT * FROM WF_ACTIVITY_ATTRIBUTES

SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL

SELECT * FROM WF_ACTIVITY_TRANSITIONS

SELECT * FROM WF_DEFERRED--WF_CONTROL

SELECT * FROM WF_ITEM_TYPES_TL

SELECT * FROM WF_LOOKUPS_TL

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES

SELECT * FROM WF_MESSAGES

SELECT * FROM WF_MESSAGES_TL

SELECT * FROM WF_MESSAGE_ATTRIBUTES

SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL

SELECT * FROM WF_ETS

SELECT * FROM WF_PROCESS_ACTIVITIES


SELECT *
  FROM wf_activity_attr_values
 WHERE NAME LIKE '%MASTER%'
   AND process_activity_id IN (SELECT *                    -- PROCESS_ACTIVITY
                                 FROM wf_item_activity_statuses
                                WHERE item_type = 'ERP' AND item_key = '63865')


SELECT   *
    FROM wf_notifications
   WHERE MESSAGE_TYPE = 'ERP'
ORDER BY begin_date DESC

LIST OF ACTIVITIES FOR AN ITEMTYPE 

SELECT a.item_key, b.activity_name, a.activity_status, a.activity_result_code,
       a.assigned_user, a.begin_date, a.end_date
  FROM wf_item_activity_statuses a, wf_process_activities b
 WHERE a.process_activity = b.instance_id(+)
   AND b.process_item_type = a.item_type
   AND a.item_type = 'ERP'
   AND a.item_key = 64077
   AND activity_name IN
                       ('PLANNING', 'PURCHASING', 'MFGFINANCE', 'CSD', 'TAX')
                      

TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING

SELECT   b.activity_name,
         TRUNC (SYSDATE) - TRUNC (begin_date) pending_from_no_of_days,
         COUNT (b.activity_name) total_pending
    FROM wf_item_activity_statuses a, wf_process_activities b
   WHERE a.process_activity = b.instance_id
     AND b.process_item_type = a.item_type
     AND a.item_type = 'ERP'
--AND A.ITEM_KEY = 1131
     AND end_date IS NULL
     AND activity_status != 'ERROR'
     AND activity_name IN
                       ('PLANNING', 'PURCHASING', 'MFGFINANCE', 'CSD', 'TAX')
GROUP BY activity_name, TRUNC (SYSDATE) - TRUNC (begin_date)
ORDER BY activity_name, pending_from_no_of_days



LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS 

SELECT SUM (total_pending) pending_less_than_5days
  FROM (SELECT   b.activity_name,
                 TRUNC (SYSDATE) - TRUNC (begin_date) pending_from_no_of_days,
                 COUNT (b.activity_name) total_pending
            FROM wf_item_activity_statuses a, wf_process_activities b
           WHERE a.process_activity = b.instance_id
             AND b.process_item_type = a.item_type
             AND a.item_type = 'ERP'
           --AND A.ITEM_KEY = 1131
             AND end_date IS NULL
             AND activity_status != 'ERROR'
             AND activity_name IN
                       ('PLANNING', 'PURCHASING', 'MFGFINANCE', 'CSD', 'TAX')
        GROUP BY activity_name, TRUNC (SYSDATE) - TRUNC (begin_date)
        ORDER BY activity_name, pending_from_no_of_days) five_days
 WHERE five_days.pending_from_no_of_days < 5