Adding Mutiple Responsibilty to User from Back End in Oracle Apps

Adding Mutiple Responsibilty to User from Back End in Oracle Apps

This script can to used to assign multiple responsibilities to a user in Oracle Apps

SCRIPT:

/***************************************************************************

-- AUTHOR NAME : Himanshu Singh

-- DATE WRITTEN : 09-AUG-2021

-- DESCRIPTION : This Script would add the Responsibility to the User.

-- MAINTAINANCE HISTORY :

-- DATE NAME REMARKS

 --------- --------- ---------

 ***************************************************************************/

DECLARE

 v_user_name VARCHAR2 (20) := 'HISINGH';

 v_description VARCHAR2 (100)

 := 'Adding Responsibility to user using script';

 v_appl_shrt_name VARCHAR2 (20);

 v_appl_name VARCHAR2 (50);

 v_resp_key VARCHAR2 (50);

 l_count NUMBER := 0;

 -- List of Responsibility that would be added

 CURSOR cur_resp_names

 IS

 SELECT 'Application developer' RESP_NAME FROM DUAL

 UNION

 SELECT 'iProcurement' FROM DUAL

 UNION

 SELECT 'Functional Administrator' FROM DUAL

 UNION

 SELECT 'Discoverer Superuser' FROM DUAL

 UNION

 SELECT 'System Administrator' FROM DUAL

 UNION

 SELECT 'Alert Manager' FROM DUAL

 UNION

 SELECT 'Payables Manager' FROM DUAL

 UNION

 SELECT 'Purchasing Super User' FROM DUAL

 UNION

 SELECT 'Receivables Manager' FROM DUAL

 UNION

 SELECT 'Order Management Super User' FROM DUAL;

BEGIN

 FOR rec IN cur_resp_names

 LOOP

 SELECT fav.application_short_name,

 fav.application_name,

 frv.responsibility_key

 INTO v_appl_shrt_name, v_appl_name, v_resp_key

 FROM FND_APPLICATION_VL fav, FND_RESPONSIBILITY_VL frv

 WHERE frv.application_id = fav.application_id

 AND frv.responsibility_name = rec.RESP_NAME;

 -- Standard Package call that would add the Responsibility against the User

 fnd_user_pkg.addresp (username => v_user_name,

 resp_app => v_appl_shrt_name,

 resp_key => v_resp_key,

 security_group => 'STANDARD',

 description => v_description,

 start_date => SYSDATE,

 end_date => NULL);

 DBMS_OUTPUT.

 put_line (

 'The responsibility '

 || rec.RESP_NAME

 || ' is added to the user '

 || v_user_name);

 l_count := l_count + 1;

 END LOOP;

 COMMIT;

 DBMS_OUTPUT.PUT_LINE ('Total Number Of Resp Added: ' || l_count);

EXCEPTION

 WHEN OTHERS

 THEN

 DBMS_OUTPUT.

 put_line (

 'Responsibility IS NOT added due to '

 || SQLCODE

 || '; '

 || SUBSTR (SQLERRM, 1, 250));

 ROLLBACK;

END;

/

Note: Please note, you can also create a temporary table and put all responsibility name in that and call that in cursor.

If you like please follow and comment