- First create a temp table to hold user responsibilities to be inactivated.
- Then insert record into temp tables, you may prepare these insert scripts in excel spreadsheet using concatenate function instead writing one by one
- Execute following script to end date all of the users responsibilities listed in temp table
1 2 3 4 |
CREATE TABLE xxcb_user_resp ( user_name VARCHAR2(240), resp_name VARCHAR2(240) ) |
1 2 3 |
insert into xxcb_user_resp values('ABUONPASTORE','LU AP Supervisor'); insert into xxcb_user_resp values('ABUONPASTORE','LU Internet Expenses'); commit; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
SET SERVEROUTPUT ON; -- ----------------------------------------------------------------- -- End Date Responsibility from Oracle FND User -- ----------------------------------------------------------------- DECLARE -- v_user_name VARCHAR2(240); v_responsibility_name VARCHAR2(240); l_user_name VARCHAR2(240); l_resp_name VARCHAR2(240); v_application_name VARCHAR2(100) := NULL; v_responsibility_key VARCHAR2(100) := NULL; v_security_group VARCHAR2(100) := NULL; -- CURSOR cur_user_resp IS SELECT * FROM xxcb_user_resp; -- BEGIN FOR i IN cur_user_resp LOOP v_user_name := i.user_name; v_responsibility_name := i.resp_name; v_application_name := NULL; v_responsibility_key := NULL; v_security_group := NULL; -- SELECT fa.application_short_name, fr.responsibility_key, frg.security_group_key INTO v_application_name,v_responsibility_key,v_security_group FROM fnd_responsibility fr, fnd_application fa, fnd_security_groups frg, fnd_responsibility_tl frt WHERE fr.application_id = fa.application_id AND fr.data_group_id = frg.security_group_id AND fr.responsibility_id = frt.responsibility_id AND frt.language = userenv('LANG') AND frt.responsibility_name = v_responsibility_name; -- fnd_user_pkg.delresp( username => v_user_name, resp_app => v_application_name, resp_key => v_responsibility_key, security_group => v_security_group ); -- COMMIT; dbms_output.put_line('Responsiblity ' || v_responsibility_name || ' is removed from the user ' || v_user_name || ' Successfully'); END LOOP; -- i invoices -- EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered while deleting responsibilty from the user and the error is ' || sqlerrm); END; / |
Thanks Yogesh
