Workflow: SQL Query to get workflow notification errors within a certain period

                                         The below SQL query is used to get workflow notification errors within a certain period for a certain Item_type, you can replace the item_type with yours in the below query.

SELECT   ias.begin_date, ias.item_key, ac.NAME activity,
         ias.activity_result_code RESULT, ias.error_name error_name,
         ias.error_message error_message
    FROM wf_item_activity_statuses ias,
         wf_process_activities pa,
         wf_activities ac,
         wf_activities ap,
         wf_items i
   WHERE ias.item_type = 'HRSSA'
     AND ias.activity_status = 'ERROR'
     AND ias.process_activity = pa.instance_id
     AND pa.activity_name = ac.NAME
     AND pa.activity_item_type = ac.item_type
     AND pa.process_name = ap.NAME
     AND pa.process_item_type = ap.item_type
     AND pa.process_version = ap.VERSION
     AND i.item_type = 'HRSSA'
     AND i.item_key = ias.item_key
     AND i.begin_date >= ac.begin_date
     AND i.begin_date < NVL (ac.end_date, i.begin_date + 1)
--     AND TRUNC (i.begin_date) BETWEEN TO_DATE (:begin_date) AND TRUNC (:end_date)
ORDER BY ias.begin_date DESC;

How to Get Single Employee with Multiple Rules in WorkFlow Query

/* Formatted on 7/18/2014 9:39:03 AM (QP5 v5.115.810.9015) */
SELECT orig_system,
       description,
       orig_system_id,
       name "Login Name",
       display_name "Employee Name"
FROM wf_local_roles wfr1
WHERE (wfr1.orig_system <> 'WF_LOCAL_ROLES' AND wfr1.orig_system_id <> 0)
      AND EXISTS
            (SELECT 'row found'
             FROM wf_local_roles wfr2
             WHERE (wfr2.orig_system <> 'WF_LOCAL_ROLES'
                    AND wfr2.orig_system_id <> 0)
                   AND wfr2.orig_system_id = wfr1.orig_system_id
                   AND wfr2.orig_system = wfr1.orig_system
                   AND wfr2.name <> wfr1.name)
ORDER BY orig_system

Work Flow STATUS Checking Query

select count(*) from WF_NOTIFICATIONS WN
WHERE 1 = 1
AND TRUNC(TO_DATE(WN.BEGIN_DATE)) = TRUNC(TO_DATE(SYSDATE)-1)
AND END_DATE IS NULL
AND MESSAGE_TYPE like 'LAA%'
AND STATUS = 'OPEN'
AND  1=1

Workflow tables in oracle apps

wf_item_types
wf_item_types_tl
wf_items
wf_notifications
wf_item_activity_statuses
wf_item_activity_statuses_v
wf_local_roles
wf_roles
wf_events

Work flow table with link

SELECT   wn.notification_id,
         aerh.invoice_num,
         TO_CHAR (wn.begin_date, 'DD-MON-YYYY hh:mi:ss') begin_date,
         wn.to_user,
         wn.from_user,
         wr2.display_name preparer,
         wr3.display_name reported_for,
         wn.status,
         wn.mail_status mail_status,
         aerh.expense_status_code,
         WR.email_address,
         aerh.total,
         aerh.report_submitted_date,
         aerh.description
  FROM   wf_notifications wn,
         wf_item_activity_statuses was,
         wf_items wi,
         ap_expense_report_headers_all aerh,
         WF_ROLES WR,
         wf_roles wr2,
         fnd_user fu,
         wf_roles wr3
 WHERE       1 = 1
         AND wn.notification_id = was.notification_id
         AND was.item_type = wi.item_type
         AND was.item_key = wi.item_key
         AND wn.status IN ('OPEN', 'CANCELED')
         AND wn.message_name = 'OIE_REQ_EXPENSE_REPORT_APPRVL'
         AND aerh.invoice_num = wi.user_key
         AND aerh.expense_status_code IN ('PENDMGR', 'RESOLUTN')
         AND WN.recipient_role = WR.name(+)
         AND wi.owner_role = wr2.name
         AND aerh.employee_id = fu.employee_id
         AND fu.user_name = wr3.name(+)