Close or Cancel Workflow Notifications

/* Formatted on 2/11/2018 9:40:59 AM (QP5 v5.185.11230.41888) */
Closing OR Cancelling workflow Notifications.
FOLLOWING IS A pl/SQL script TO CANCEL OR CLOSE workflow notifications THROUGH wf_notifications API.  THE script has 2 PARAMETERS (recipient_role, close_type), FOR e.g. you want TO CLOSE notifications FOR specific USER THEN pass parameter OF USER AND TYPE AS CLOSE OR CANCEL FOR cancelling notification.

PL/SQL Script
-- ****************************************************************
-- Filename -  WFCloseNotifications.sql
-- Purpose  -  Script of cancel or close workflow notification
-- Module   -  Workflow Administrator
-- ****************************************************************

DECLARE
   -- declare Cursor
   CURSOR get_notif
   IS
      SELECT *
        FROM wf_notifications
       WHERE recipient_role = '&UserName' AND status = 'OPEN';

   -- Local variables here
   i   INTEGER           number(10):=1;
  notification_type   varchar2(10):= '&nType';
begin
 dbms_output.put_line('Executing Procedure');
  -- open cursor to check open notifications which needs to be closed
  for c_get_notif in get_notif
   loop
    begin

    -- Check notificaiton type (close or cancel)
    -- CLOSE  -  This will close information notifications only, all action required notification will not be close
    -- CANCEL -  This will cancel notification, action required notification such as Approve/Reject will be cancelled and cannot be rollback, users will not be able to take actiion on such notification later
    if upper(notification_type)='CLOSE' then
      wf_notification.Close(c_get_notif.notification_id, c_get_notif.recipient_role);
      i:=i+1;
   elsif upper(notification_type)='CANCEL' then
      wf_notification.cancel(c_get_notif.notification_id, c_get_notif.recipient_role);
      i:=i+1;
    end if;

    exception
     when others then
      null;
    end;
   end loop;

   commit;
   dbms_output.put_line(to_char(i)||' records effected');
   dbms_output.put_line('Procedure Executed Successfully');
end;