Scribd
Upload
389 views

How To Add Responsibility To USER Using PL

This document provides instructions for adding responsibilities to Oracle Application users using PL/SQL. It includes a script to create a new FND_USER with System Administrator rights and a script to add a specific responsibility, like "Receivables Manager", to an existing user. The key steps are: 1. Run a SQL query to find the responsibility application short name and key for the responsibility you want to add. 2. Use the fnd_user_pkg.addresp procedure to add the responsibility, specifying the user name, responsibility application, key, and other parameters. 3. Commit the transaction to save the changes.

Uploaded by

Venkatesan Ramamoorthy
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
389 views

How To Add Responsibility To USER Using PL

This document provides instructions for adding responsibilities to Oracle Application users using PL/SQL. It includes a script to create a new FND_USER with System Administrator rights and a script to add a specific responsibility, like "Receivables Manager", to an existing user. The key steps are: 1. Run a SQL query to find the responsibility application short name and key for the responsibility you want to add. 2. Use the fnd_user_pkg.addresp procedure to add the responsibility, specifying the user name, responsibility application, key, and other parameters. 3. Commit the transaction to save the changes.

Uploaded by

Venkatesan Ramamoorthy
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

How To Add Responsibility to USER using pl/sql?

Oracle Applications
If you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API. I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production). Please note that:1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in. 2. The password will be set to oracle 3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser 4. You need a COMMIT. I have not included the commit within this script. 5. When running this script, you will be prompted to enter a user name. Using the pl/sql you can add responsibility to any USER without having Application System Administrator Rights. First, get value for Responsablity_Application_Short_Name and Responsibility_Key Parameters you need to run following sql Statement by Using APPS User Name SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID ORDER BY FRV.RESPONSIBILITY_NAME To add Responsibility of Receivables Manager to User Jiltin BEGIN fnd_user_pkg.addresp (JILTIN,AR,RECEIVABLES_MANAGER,STANDARD,Add Responsibility to USER using pl/sql,SYSDATE,SYSDATE + 100); COMMIT; DBMS_OUTPUT.put_line (Responsibility Added Successfully); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( Responsibility is not added due to SQLCODE SUBSTR (SQLERRM, 1, 100)); ROLLBACK; END; Another sample script to add System Administrator

DECLARE v_session_id INTEGER := userenv(sessionid); v_user_name VARCHAR2(30) := upper(&Enter_User_Name); BEGIN Note, can be executed only when you have apps password. Call the procedure to Creaet FND User fnd_user_pkg.createuser(x_user_name => v_user_name ,x_owner => ,x_unencrypted_password => oracle ,x_session_number => v_session_id ,x_start_date => SYSDATE 10 ,x_end_date => SYSDATE + 100 ,x_last_logon_date => SYSDATE 10 ,x_description => www.notesbit.com ,x_password_date => SYSDATE 10 ,x_password_accesses_left => 10000 ,x_password_lifespan_accesses => 10000 ,x_password_lifespan_days => 10000 ,x_employee_id => 30 /*Change this id by running below SQL*/ /* SELECT person_id ,full_name FROM per_all_people_f WHERE upper(full_name) LIKE % upper(full_name) % GROUP BY person_id ,full_name */ ,x_email_address => admin@notesbit.com ,x_fax => ,x_customer_id => ,x_supplier_id => ); fnd_user_pkg.addresp(username => v_user_name ,resp_app => SYSADMIN ,resp_key => SYSTEM_ADMINISTRATOR ,security_group => STANDARD ,description => Auto Assignment ,start_date => SYSDATE 10 ,end_date => SYSDATE + 1000); END; / Following are the reference for the pl/sql package begin fnd_user_pkg.addresp( &User_Name, /*Application User Name */ &Responsablity_Application_Short_Name, /*get from Query Below */ &Responsibility_Key,/*get from Query Below */

&Security_Group, /* Most of cases it is STANDARD so you can hard code it */ &Description, /* Any comments you want */ &Start_Date, /* Sysdate From Today */ &End_Date ); /* Sysdate + 365 Rights for Next One Year*/ commit; dbms_output.put_line(Responsibility Added Successfully); exception when others then dbms_output.put_line( Responsibility is not added due to SQLCODE substr(SQLERRM, 1, 100)); Rollback; end; /

You might also like