API to Cancel Workflow, Purge Notification and Rollback Transaction

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM (SELECT transaction_id,

                     process_name,

                     item_type,

                     item_key,

                     transaction_effective_date,

                     (SELECT text_value

                        FROM apps.wf_item_attribute_values v

                       WHERE ITEM_KEY = hat.item_key

                             AND NAME = 'ERROR_MESSAGE_TEXT')

                        error_details

                FROM hr_api_transactions hat

               WHERE status = 'E' AND item_type = 'HRSSA')

       WHERE error_details IN

                ('ORA-20001: Your Leave is Overlapping with another Leave raised !!!',

                 'ORA-20001: Leave can not start on a weekend',

                 'ORA-20001: HR_7155_OBJECT_INVALID:');

   l_cancel_workflow   VARCHAR2 (1) := 'N';

BEGIN

   FOR i IN c1

   -- Block to Cancel Workflow

   LOOP

      l_cancel_workflow := 'N';

      BEGIN

         wf_engine.

          abortprocess (itemtype   => i.item_type,

                        itemkey    => i.item_key,

                        process    => i.process_name);

         COMMIT;

         l_cancel_workflow := 'Y';

         DBMS_OUTPUT.

          put_line ('Item Key has been Aborted/Cancelled: ' || i.item_key);

      EXCEPTION

         WHEN OTHERS

         THEN

            l_cancel_workflow := 'N';

            DBMS_OUTPUT.

             put_line (

               'Cancel Workflow Exception: ' || SQLERRM || '- ' || i.item_key);

      END;

      IF l_cancel_workflow = 'Y'

      THEN

         -- Block to Purge Notification

         BEGIN

            wf_purge.total (itemtype => i.item_type, itemkey => i.item_key);

            DBMS_OUTPUT.put_line ('Notification Purged : ' || i.item_key);

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.

                put_line (

                     'Purge Notification Exception: '

                  || SQLERRM

                  || '- '

                  || i.item_key);

         END;

         -- Block to Rollback Transaction

         BEGIN

            hr_transaction_api.rollback_transaction (i.transaction_id);

            DBMS_OUTPUT.

             put_line ('Transaction Rolled Back : ' || i.transaction_id);

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.

                put_line (

                     'Purge Notification Exception: '

                  || SQLERRM

                  || '- '

                  || i.item_key);

         END;

      END IF;

   END LOOP;

END;