[Workflow] Purge => WFERROR

 [Script] 

select wit.name "[WF] Name",

       wit.display_name "[WF] Display Name",

       wit.persistence_type "Persistence Type",

       decode(wi.end_date, null, 'OPEN', 'CLOSED') "Status",

       count(*) "Count"

from   wf_items_v       wi,

       wf_item_types_vl wit

where  wi.item_type = wit.name

and    wi.item_type = 'WFERROR'

group  by wit.name,

          wit.display_name,

          wit.persistence_type,

          wit.persistence_days,

          decode(wi.end_date, null, 'OPEN', 'CLOSED')

[Respond for open notifications]

SET serveroutput ON;

DECLARE

  vCommitCount    NUMBER := 1;

  vCommitInterval NUMBER := 5000;

  CURSOR curWF

  (

    pMessageType       IN VARCHAR2 DEFAULT 'WFERROR',

    pStatus            IN VARCHAR2 DEFAULT 'OPEN',

    pOriginalRecipient IN VARCHAR2 DEFAULT 'SYSADMIN',

    pDaysBefore        IN NUMBER DEFAULT 60

  ) IS

    SELECT notification_id

    FROM   wf_notifications

    WHERE  message_type = pMessageType

    AND    status = pStatus

    AND    original_recipient = pOriginalRecipient

    AND    begin_date < SYSDATE - pDaysBefore;

BEGIN

  FOR recWF IN curWF

  LOOP

    wf_notification.respond(nid => recWF.notification_id);

    IF vCommitCount = vCommitInterval THEN

      COMMIT;

      vCommitCount := 1;

    ELSE

      vCommitCount := vCommitCount + 1;

    END IF;

  END LOOP;

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    dbms_output.put_line(dbms_utility.format_error_stack);

END;

[Purge] 

--Concurrent : Purge Obsolete Workflow Runtime Data