How to Delete Responsibility From User

System Administrator assigned a wrong responsibility to a user. To "remove" this responsibility from that user, the only way, as provided by Oracle, is to end-date this responsibility from this user. 

The reason behind of not allowing to delete responsibility from user is that: if a user owns a particular responsibility, this user can do certain business transactions as this responsibility provided, and leave a trail in the tables LAST_UPDATED_BY column for the data this user altered. For auditing purpose (and other finger-pointing excuses), we need to know who, when, how this transaction is created, and more importantly what is business purpose and logic behind of this transaction. So we should keep the responsibility record in order to explain why this user is able to do such business transactions.

Back to System Administrator that he assigned a wrong responsibility to a user, and he found it out once he pressed Ctrl-S. He wants to remove this record...but there has no way to do it in the GUI (Users Form) or OA framework pages (User Management). 

In terms of API, there has a FND_USER_PKG.DELREP exists but this stored procedure will end-date the responsibility, instead of deleting this responsibility from user.

In here I present a way to remove this incorrect responsibility from database. I incorporate this method with Form Personalization so that System Administrator can easily remove any responsibility from user without opening a SQL*Plus prompt.

You can load this personalization file by FNDLOADER, or create these 2 rules manually as shown below.

Rule: Add Menu Item when a Responsibility is selected

Rule: Invoke stored procedure when menu item is selected

Part 3 - Testing

Query a user, select a responsibility and select "Delete Selected Responsibility" from Menu

Click "OK" to confirm the deletion, or "Cancel" to leave.

 

Part 2 - Create Form Personalization 

CREATE OR REPLACE PROCEDURE APPS.XX_DELETE_RESP_FROM_USER (p_userID IN NUMBER, p_respID IN NUMBER, p_commit IN BOOLEAN) IS
BEGIN
FOR RS IN (
SELECT t2.USER_NAME,
t3.RESPONSIBILITY_KEY
FROM WF_LOCAL_USER_ROLES t1
, FND_USER            t2
, FND_RESPONSIBILITY  t3
WHERE t1.ROLE_NAME LIKE 'FND_RESP|%' || t3.RESPONSIBILITY_KEY || '|%'
AND t1.USER_NAME         = t2.USER_NAME
AND t2.USER_ID           = p_userID
AND t3.RESPONSIBILITY_ID = p_respID
) LOOP
DELETE FROM WF_LOCAL_USER_ROLES
WHERE USER_NAME=RS.USER_NAME
AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
AND ROLE_ORIG_SYSTEM_ID=p_respID;

DELETE FROM WF_USER_ROLE_ASSIGNMENTS
WHERE USER_NAME=RS.USER_NAME
AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
AND ROLE_ORIG_SYSTEM_ID=p_respID;

END LOOP;
IF p_commit = TRUE THEN
COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, 'Unable to delete this responsiiblity from user.');
END;
/