DECLARE cursor add_resp is SELECT DISTINCT APPL.APPLICATION_SHORT_NAME , RESP.RESPONSIBILITY_KEY , SEC.SECURITY_GROUP_KEY FROM FND_RESPONSIBILITY_VL RESP , FND_APPLICATION_VL APPL , FND_SECURITY_GROUPS_VL SEC , FND_USER_RESP_GROUPS_DIRECT RESPG , fnd_user fu WHERE RESP.APPLICATION_ID = APPL.APPLICATION_ID AND SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID AND RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID AND RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID and FU.USER_ID = RESPG.USER_ID AND fu.user_name = 'SYSADMIN'; -- Change the username with the one you want to copy from lc_user_name varchar2(100) := 'JISIP'; -- Change the username with the one you want to assign the responsibilies to BEGIN FOR i in add_resp loop fnd_user_pkg.addresp ( username => lc_user_name, resp_app => i.APPLICATION_SHORT_NAME ,resp_key => i.RESPONSIBILITY_KEY ,security_group => i.SECURITY_GROUP_KEY ,description => NULL ,start_date => SYSDATE ,end_date => NULL ); end loop; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); END; / SHOW ERR;
Take note that you would need enough database privilege to the APPS schema to run this script.
Update! May 23, 2019:
I tried the same script for E-Business Suite 12.2.5 but it didn't work. I made a few changes and this did the trick:
set SERVEROUTPUT ON; DECLARE cursor add_resp is SELECT DISTINCT APPL.APPLICATION_SHORT_NAME , RESP2.RESPONSIBILITY_KEY , SEC.SECURITY_GROUP_KEY FROM FND_RESPONSIBILITY_tl RESP , FND_APPLICATION APPL , FND_SECURITY_GROUPS SEC , FND_USER_RESP_GROUPS RESPG , FND_RESPONSIBILITY RESP2 WHERE RESP.RESPONSIBILITY_NAME = 'System Administrator' and RESP2.RESPONSIBILITY_id = RESP.RESPONSIBILITY_id AND RESP.APPLICATION_ID = APPL.APPLICATION_ID AND SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID AND RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID AND RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID; lc_user_name varchar2(100) := 'XXSAMPLE'; v_count number := 0; BEGIN FOR i in add_resp loop fnd_user_pkg.addresp ( username => lc_user_name, resp_app => i.APPLICATION_SHORT_NAME ,resp_key => i.RESPONSIBILITY_KEY ,security_group => i.SECURITY_GROUP_KEY ,description => NULL ,start_date => SYSDATE ,end_date => NULL ); v_count := v_count + 1; end loop; DBMS_OUTPUT.PUT_LINE('Responsibilities Added to '|| lc_user_name ||':'|| v_count ); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); END; / SHOW ERR;
For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)
No comments:
Post a Comment