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

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

Adding Mutiple Responsibilty to User from Back End in Oracle Apps