DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, January 14, 2017

Query to create user and assign the responsibility for the existing User

DECLARE
v_user_name VARCHAR2 (100) := upper('&Enter_User_Name');
v_description VARCHAR2 (100) := 'New User';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 CURSOR user_cur IS
 select a.application_short_name, r.responsibility_key
 from fnd_responsibility_vl r, fnd_application_vl a where
 r.application_id =a.application_id
 and R.responsibility_ID IN (SELECT RESPONSIBILITY_ID FROM fnd_user_resp_groups WHERE USER_ID=&from_userid AND END_dATE IS NULL);
user_rec user_cur%ROWTYPE;
BEGIN
 fnd_user_pkg.createuser
(x_user_name => v_user_name,
 x_owner => NULL,
 x_unencrypted_password => '&input_password',
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => NULL,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;
 OPEN user_cur;
 LOOP
 FETCH user_cur INTO user_rec;
 EXIT WHEN user_cur%NOTFOUND;
 fnd_user_pkg.addresp(username => v_user_name
 ,resp_app => user_rec.application_short_name
 ,resp_key => user_rec.responsibility_key
 ,security_group => 'STANDARD'
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);
 END LOOP;
 CLOSE user_cur;
commit;
END;
/

No comments: