Workflow Process to Disable Multiple EIT/Self Service Transaction at one Go

Purpose: Restrict Employee to raise Multiple EIT Transaction at one go

 Probable Solution as said below

Step 1: Copy Seeded Workflow “Change Extra Information” and define yours

Step 2: Create Database Function 

CREATE OR REPLACE PROCEDURE xxhr_check_trans_raised (

   itemtype   IN            VARCHAR2,

   itemkey    IN            VARCHAR2,

   actid      IN            NUMBER,

   funmode    IN            VARCHAR2,

   result        OUT NOCOPY VARCHAR2)

IS

   l_cnt   NUMBER := 0;

BEGIN

   IF (funmode = 'RUN')

   THEN

      SELECT COUNT (*)

        INTO l_cnt

        FROM hr_api_transactions hat

       WHERE hat.item_key = itemkey;

     

      IF l_cnt = 1

      THEN

         result := 'COMPLETE:' || 'FAILURE';

      ELSE

         result := 'COMPLETE:' || 'SUCCESS';

      END IF;

   ELSIF (funmode = 'CANCEL')

   THEN

      NULL;

   END IF;

END xxhr_check_trans_raised;

Step 3: Alter the Workflow as show in the below screens

Step 3.1:  Create New Function in Workflow as shown in the below screen 

Step 3.2

Step 3.3: Alter the above-created workflow as shown below

API to Assign Roles - Assigned via User Management using SYSADMIN

DECLARE

   CURSOR c1

   IS

      SELECT *

        FROM wf_roles

       WHERE display_name like 'Approvals%'

       and parent_orig_system = 'UMX';

   l_user_name   VARCHAR2 (100) := 'BIJOY';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         wf_local_synch.propagateuserrole (p_user_name   => l_user_name,

                                           p_role_name   => i.name);

         COMMIT;

         DBMS_OUTPUT.put_line (

            i.display_name || ' has been assigned to ' || l_user_name);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               i.display_name || '--  Failure --> ' || SQLERRM);

      END;

   END LOOP;

END;

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;