How to Delete Responsibility

In what situation we need to delete a responsibility?

We can disable a responsibility, end-date the users' responsibility, change the responsibility name with the words "do not use", nullify the menu attached to this responsibility, or whatever means to make a responsibility obsolete.

However, we cannot change the Responsibility Key for that incorrect responsibility.
And more importantly, I have to reuse this responsibility key for the correct responsibility. Since the we have no way to change the Responsibility Key, the alternative is to get rid of this incorrect value from the system.

Using the following script you will able to delete a responsibility from the system, using the API FND_RESPONSIBILITY_PKG.DELETE_ROW. This script will abort if such responsibility is used by any FND user or referenced in other places (e.g. HR SIT & EIT Security).

This script DOES NOT remove a responsibility from a user. Please read my next blog for this kind of removal.

SET SERVEROUTPUT ON

DECLARE
  -- Change these 2 variables to fit your needs
  p_respKey      VARCHAR2 (100) := [RESP. KEY TO BE DELETED];
  p_ForceDelete  BOOLEAN        := [TRUE|FALSE];  
 
  num_respID     NUMBER;
  num_appID      NUMBER;
  var_appName    VARCHAR2 (100);
  var_sqlStmt    VARCHAR2 (1000);
  var_respName   VARCHAR2 (100);
  num_count      NUMBER;
  boo_proceed    BOOLEAN := TRUE;
BEGIN
  SELECT A.RESPONSIBILITY_NAME
       , B.RESPONSIBILITY_ID
       , B.APPLICATION_ID
       , C.APPLICATION_NAME
    INTO var_respName
       , num_respID
       , num_appID
       , var_appName
    FROM FND_RESPONSIBILITY_TL A
       , FND_RESPONSIBILITY B
       , FND_APPLICATION_TL C
   WHERE A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
     AND A.LANGUAGE = 'US'
     AND B.RESPONSIBILITY_KEY = p_respKey
     AND C.LANGUAGE = 'US'
     AND C.APPLICATION_ID = B.APPLICATION_ID;

  DBMS_OUTPUT.PUT_LINE ('Resp. ID  : ' || num_respID);
  DBMS_OUTPUT.PUT_LINE ('Resp. Name: ' || var_respName);
  DBMS_OUTPUT.PUT_LINE ('Application ID   : ' || num_appID);
  DBMS_OUTPUT.PUT_LINE ('Application Name : ' || var_appName);
  DBMS_OUTPUT.PUT_LINE ('-------------------------------------------');
  DBMS_OUTPUT.PUT_LINE ('Scanning tables...');

  FOR RS IN (  SELECT a.owner
                    , a.table_name
                 FROM dba_tab_columns a
                    , dba_objects b
                WHERE a.column_name = 'RESPONSIBILITY_ID'
                  AND a.table_name = b.object_name
                  AND b.object_type = 'TABLE'
                  AND a.table_name NOT IN ('FND_RESPONSIBILITY', 'FND_RESPONSIBILITY_TL')
             ORDER BY 1
                    , 2) LOOP
    var_sqlStmt   := 'SELECT COUNT(*) FROM ' || RS.OWNER || '.' || RS.TABLE_NAME || ' WHERE RESPONSIBILITY_ID = ' || num_respID;

    EXECUTE IMMEDIATE var_sqlStmt INTO num_count;

    IF num_count > 0 THEN
      boo_proceed   := FALSE;
      DBMS_OUTPUT.PUT_LINE (RS.TABLE_NAME || ' (' || num_count || ')');
    END IF;
  END LOOP;

  SELECT COUNT (*)
    INTO num_count
    FROM WF_LOCAL_USER_ROLES
   WHERE ROLE_ORIG_SYSTEM_ID = num_respID
     AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
     AND ROLE_NAME LIKE 'FND_RESP%';

  IF num_count > 0 THEN
    boo_proceed   := FALSE;
    DBMS_OUTPUT.PUT_LINE ('WF_LOCAL_USER_ROLES (' || num_count || ')');
  END IF;

  SELECT COUNT (*)
    INTO num_count
    FROM WF_USER_ROLE_ASSIGNMENTS
   WHERE ROLE_ORIG_SYSTEM_ID = num_respID
     AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
     AND ROLE_NAME LIKE 'FND_RESP%';

  IF num_count > 0 THEN
    boo_proceed   := FALSE;
    DBMS_OUTPUT.PUT_LINE ('WF_USER_ROLE_ASSIGNMENTS (' || num_count || ')');
  END IF;

  DBMS_OUTPUT.PUT_LINE ('-------------------------------------------');

  IF boo_proceed = TRUE OR p_ForceDelete = TRUE THEN
    FND_RESPONSIBILITY_PKG.DELETE_ROW (num_respID
                                     , num_appID);
    DBMS_OUTPUT.PUT_LINE ('Responsibility ' || var_respName || ' deleted successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE ('Responsibility ' || var_respName || ' cannot be deleted.');
  END IF;
  
  EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Responsibility key ' || p_respKey || ' is not found.');  
END;
/