Workflow Troubleshooting SQL queries

Workflow Troubleshooting SQL queries

SELECT DISTINCT rs.text, r.display_name
  FROM wf_resources rs, wf_local_roles r
 WHERE rs.name = 'WF_ADMIN_ROLE' AND rs.text = r.name;

 select *
 from WF_ITEM_ACTIVITY_STATUSES_V
 where (user_key='110133340' or ITEM_KEY like '8834663-7239%')
 and activity_status_code!='COMPLETE'
 order by execution_time;

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
where role_name='USER.NAME';

SELECT * FROM WF_USER_ROLES
where role_name='USER.NAME';

SELECT * FROM WF_ROLES
where name='USER.NAME';

SELECT distinct orig_system FROM WF_ROLES;

SELECT * FROM WF_ITEMS;

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_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_ITEM_TYPES;

SELECT * FROM WF_LOOKUPS_TL;

  SELECT *
    FROM WF_NOTIFICATIONS
   WHERE MESSAGE_TYPE = 'ERP'
ORDER BY BEGIN_DATE DESC;

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 name,
         num_active,
         num_error,
         num_defer,
         num_suspend,
         num_complete,
         num_purgeable,
         wit.*
    FROM WF_ITEM_TYPES wit
   WHERE   num_active
         + num_error
         + num_defer
         + num_suspend
         + num_complete
         + num_purgeable != 0
ORDER BY wit.num_active DESC;

SELECT *
  FROM WF_ITEM_TYPES_TL
 WHERE 1 = 1 AND language = 'US' AND NAME = 'REQAPPRV';

       --         AND DISPLAY_NAME = 'PO Approval';

  SELECT A.ITEM_KEY,
         B.ACTIVITY_NAME,
         A.ACTIVITY_STATUS,
         A.ACTIVITY_RESULT_CODE,
         A.ASSIGNED_USER,
         A.BEGIN_DATE,
         A.END_DATE,
         A.*
    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 LIKE 'REQAPPRV'                --'XXLVMGMT' --XXOMFSF
         AND A.ACTIVITY_STATUS != 'COMPLETE'
         AND A.begin_date >= TRUNC (SYSDATE) - 1
-- AND A.ITEM_KEY = 64077
--       AND ACTIVITY_NAME IN
--              ('PLANNING', 'PURCHASING', 'MFGFINANCE', 'CSD', 'TAX')
ORDER BY A.item_key;                                                                                   --, b.instance_id;

SELECT *
  FROM wf_notifications
 WHERE notification_id = 61394049;

  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;

SELECT * FROM wf_deferred_table_m;