How To Add Responsibility to USER using pl/sql? – Oracle Applications
- How do you check the profile option values using sql – Oracle Applications?
- Oracle Applications : Steps for Creating New Operating Unit in a multi org set up.
- List Reponsibilities for an user
- Oracle Applications : How To Setup a Collections User Account?
- Oracle Applications – Find responsibilities associated with the users – using sql
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
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
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
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
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;
/
This is my first time comment at your blog.
Good recommended website.
useful scripts..Thansk a lot.
Wonderful!
Hi,
What happens the day after end_date for a responsability ?
Will the user have access the day after end_date ?
Thank you.
Luc Martineau
while running this script i got following error:
‘Application Object Library was unable access your ORACLE password.’
What may be the cause and what should be the action?
Are you running as apps user or some other user? If apps, it should not say this. If other other user , it may likely say. second, what is the version of oracle application you use?
nice,very nice
ome other user? If apps, it should not say this. If other other user , it may likely say. second, what is the version of oracle application you us
Excellent job.Very useful script.Awesome presentation. Thanks Thanks a lot.
it is very nice