Oracle Apps Knowledge Sharing

                  Below script will help you to delegate any open workflow notification from PLSQL

Tested Instance: R12

Script:

  CREATE FUNCTION xxwf_transfer( pin_noitification_id  IN NUMBER ,
                                 piv_new_user          IN VARCHAR2
                               )
  RETURN VARCHAR2 
  IS                                        
    lv_role_name           VARCHAR2(100);
    le_stop_transfer       EXCEPTION;
    lv_return_text         VARCHAR2(500);
    position_              NUMBER;
  BEGIN  
  
    -- Check for WF_ROLES
    position_ := 10;
    BEGIN
      SELECT role_name 
        INTO lv_role_name 
        FROM wf_user_roles
       WHERE role_orig_system = 'PER'
         AND role_orig_system_id = (SELECT employee_id 
                                      FROM fnd_user 
                                     WHERE user_name = piv_new_user
                                   );
    EXCEPTION
      WHEN NO_DATA_FOUND THEN  
        lv_return_text := 'No Valid Role-'||piv_new_user; 
        RAISE le_stop_transfer;     
    END;
  
    position_ := 20;  
    -- Calling API
    wf_notification.forward
      (
        pin_noitification_id,
        lv_role_name,
        'Backend Delegation'
      );   

    RETURN 'Sucessfully Completed'; 
    
  EXCEPTION
    WHEN le_stop_transfer THEN
      RETURN lv_return_text;
    WHEN OTHERS THEN
      lv_return_text := 'Exception at postion '||position_||'. Error: '||SQLERRM;
      RETURN lv_return_text;
  END xxwf_transfer;

Test Script:

SET SERVEROUTPUT ON;
DECLARE 
 lv_message VARCHAR2(1000);
BEGIN 
 lv_message := xxwf_transfer(123456,'TESTUSER');
 DBMS_OUTPUT.put_line(lv_message);
END;