Oracle Apps Knowledge Sharing

Test Instance: R12.2.5

Query:

SELECT
   workflowitemeo.item_type,
   workflowitemeo.item_key,
   workflowitemeo.begin_date,
   workflowitemeo.end_date,
   workflowitemtypeeo.display_name,
   wf_fwkmon.getitemstatus(workflowitemeo.item_type, 
                                         workflowitemeo.item_key, 
                                         workflowitemeo.end_date, 
                                         workflowitemeo.root_activity, 
                                         workflowitemeo.root_activity_version 
                                         ) AS workflow_status,
   ias.error_name error_name,
   ias.error_message error_message,
   ias.error_stack error_stack 
FROM
   wf_items workflowitemeo,
   wf_item_types_vl workflowitemtypeeo,
   wf_activities_vl activityeo,
   wf_item_activity_statuses ias 
WHERE
   ias.activity_status = 'ERROR' 
   AND ias.item_type = workflowitemeo.item_type 
   AND ias.item_key = workflowitemeo.item_key 
   AND workflowitemeo.item_type = workflowitemtypeeo.name 
   AND activityeo.item_type = workflowitemeo.item_type 
   AND activityeo.name = workflowitemeo.root_activity 
   AND activityeo.version = workflowitemeo.root_activity_version 
   AND workflowitemeo.begin_date > SYSDATE - 1 
   AND workflowitemeo.item_type <> 'WFERROR';

        Below script will help you to cancel any workflow notification from Database

Tested Instance: R12

Script:

CREATE OR REPLACE FUNCTION xxwf_cancel (pin_noitification_id  IN NUMBER)
RETURN VARCHAR2 
IS
BEGIN
  wf_notification.cancel
       (nid            => pin_noitification_id,
        cancel_comment => 'This notification is cancelled from Backend.'
       );
  COMMIT;   
  RETURN 'Action Completed Successfully';  
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'Error: ' || SQLERRM;
END xxwf_cancel;

Test Script:

DECLARE 
 lv_message VARCHAR2(1000);
BEGIN 
 lv_message := xxwf_cancel(2906622);
 DBMS_OUTPUT.put_line(lv_message);
END;

                  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;