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;

Notifications for Approval going to POSITION but not to the person attached on that Position

How do we Debug the Issue when Notifications for Approval is going to POSITION but not to the person attached on that Position

1) Below are the list of Approval Notification which has to be fired to the Person attached to that position

Recipient Role: "POS:24656"

Query for Reference:

SELECT   *
  FROM   apps.wf_notifications
 WHERE   RECIPIENT_ROLE = 'POS:24656' AND status = 'OPEN'



2) Extract the details of the Employee Attached to the Position to which approval Notification should be fired
i.e. User Name of Employee

Query for Reference:

SELECT   fu.user_name
  FROM   apps.fnd_user fu, per_people_x per, per_assignments_x paaf
 WHERE       per.person_id = fu.employee_id
         AND per.person_id = paaf.person_id
         AND paaf.assignment_status_type_id IN (1, 2)
         AND paaf.assignment_type = 'E'
         AND paaf.primary_flag = 'Y'
         AND paaf.position_id = 24656

3) Now we need to check if the role (Position) is assigned to each of the Workflow local tables which is used to send  notification based on the Position attached. The following query would help you to trace the same

a) Status Should be ACTIVE

SELECT   *
  FROM   apps.wf_local_roles
 WHERE   name = 'F29483';

b) Check if the Role is Active and  Attached as per the Position of the Employee e.g: POS:24656
SELECT   *
  FROM   apps.wf_local_user_roles
 WHERE       user_name LIKE '%29483%'
         AND role_name LIKE 'POS%'
         AND user_orig_system = 'PER'

c) Check if the Role is Active and  Attached as per the Position of the Employee e.g: POS:24656
SELECT   *
  FROM   apps.wf_user_roles
 WHERE       user_name LIKE '%29483%'
         AND role_name LIKE 'POS%'
         AND user_orig_system = 'PER'

d) Check if the Role is Active and  Attached as per the Position of the Employee e.g: POS:24656
SELECT   *
  FROM   apps.wf_user_role_assignments
 WHERE   user_name LIKE '%29483%' AND role_name LIKE 'POS%'

4) Run the below seeded Program's if any of the above query doesn't show the roles assigned

a) Request Name: Synchronize WF LOCAL tables
Parameters:
Orig System: "POS"
Raise Errors: "Yes"

b) Request Name: Workflow Directory Services User/Role Validation

Parameters:

User Name: <Desired User Name>

Fix Dangling Users: "Yes"

Add/Missing User/Role Assignment: "Yes"

Update WHO Columns in WF Tables: "Yes"