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.
Part 1 - Create Stored Procedure
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; /
Part 2 - Create Form Personalization
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 |
Query a user, select a responsibility and select "Delete Selected Responsibility" from Menu |
Click "OK" to confirm the deletion, or "Cancel" to leave. |
No comments :
Post a Comment