Oracle EBS Workflow Notifications and Errored Activity Query Generator

SELECT ias.item_key,
       pa.process_name,
       pa.instance_label,
       pa.activity_name,
       ias.activity_status,
       ias.error_name,
       SUBSTR(ias.error_message, 1, 400) AS error_message,
       ias.begin_date,
       pa.process_name || ':' || pa.instance_label AS retry_activity
FROM   wf_item_activity_statuses ias
JOIN   wf_process_activities pa
       ON pa.instance_id = ias.process_activity
WHERE  ias.item_type = 'OEOL'
AND    ias.activity_status = 'ERROR'
AND    ias.item_key = NVL('[Item key (optional for queries, required for retry)]', ias.item_key)
AND    ias.begin_date >= SYSDATE - 7
ORDER  BY ias.begin_date DESC;

SELECT n.notification_id,
       n.message_type,
       n.message_name,
       n.subject,
       n.begin_date,
       n.mail_status,
       n.original_recipient
FROM   wf_notifications n
WHERE  n.recipient_role = 'SYSADMIN'
AND    n.status = 'OPEN'
AND    n.begin_date >= SYSDATE - 7
ORDER  BY n.begin_date DESC;

SELECT n.mail_status,
       COUNT(*) AS notifications
FROM   wf_notifications n
WHERE  n.status = 'OPEN'
AND    n.mail_status IN ('MAIL', 'FAILED', 'ERROR')
GROUP  BY n.mail_status
ORDER  BY 2 DESC;

-- WF_DEFERRED is an Advanced Queuing queue table; STATE 0 = ready, 1 = waiting (delayed), 2 = processed, 3 = expired
SELECT q.corrid,
       q.state,
       COUNT(*) AS messages,
       MIN(q.enq_time) AS oldest_enqueued
FROM   applsys.wf_deferred q
GROUP  BY q.corrid, q.state
ORDER  BY 3 DESC;

SET SERVEROUTPUT ON
DECLARE
  l_status VARCHAR2(30);
  l_result VARCHAR2(240);
BEGIN
  wf_engine.itemstatus(itemtype => 'OEOL',
                       itemkey  => '[Item key (optional for queries, required for retry)]',
                       status   => l_status,
                       result   => l_result);
  DBMS_OUTPUT.PUT_LINE('Before retry: ' || l_status || ' / ' || l_result);

  wf_engine.handleerror(itemtype => 'OEOL',
                        itemkey  => '[Item key (optional for queries, required for retry)]',
                        activity => 'PROCESS_NAME:INSTANCE_LABEL',
                        command  => 'RETRY',
                        result   => NULL);
  COMMIT;

  wf_engine.itemstatus(itemtype => 'OEOL',
                       itemkey  => '[Item key (optional for queries, required for retry)]',
                       status   => l_status,
                       result   => l_result);
  DBMS_OUTPUT.PUT_LINE('After retry: ' || l_status || ' / ' || l_result);
END;
/