Here are the SQL Scripts to create application user, reset application user’s password and adding responsibility thru SQL Plus / Toad / SQL Developer. Connect as APPS to execute the scripts.
- Creating application user
— creating application user
DECLARE
l_user_name VARCHAR2(100) := UPPER(‘VMITTAKANTI‘); — Username
l_password VARCHAR2(30) := ‘Welcome1‘; — Password
l_session_id INTEGER := USERENV(‘sessionid’);
BEGIN
fnd_user_pkg.createuser (x_user_name => l_user_name,
x_owner => NULL,
x_unencrypted_password => l_password,
x_session_number => l_session_id,
x_start_date => SYSDATE,
x_end_date => NULL);
COMMIT;
dbms_output.put_line(‘User: ‘||l_user_name|| ‘ created successfully’);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to create User – Error: ‘||SQLCODE||’ ‘||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END;
2. Reset Application User’s password
— Reset application user’s password
DECLARE
x boolean;
BEGIN
x := FND_USER_PKG.ChangePassword(‘VMITTAKANTI‘,’Welcome2‘); — Pass username and password
COMMIT;
IF x THEN
dbms_output.put_line(‘Password changed successfully’);
ELSE
dbms_output.put_line(‘Unsuccessful’);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Password Reset Exception: ‘||SQLCODE||’ ‘||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END;
3. Adding responsibility to the application user
— Adding responsibility to the application user
BEGIN
fnd_user_pkg.addresp(‘VMITTAKANTI‘,’SYSADMIN‘,’SYSTEM_ADMINISTRATOR‘,’STANDARD’,’Add Responsibility to USER using pl/sql’, SYSDATE, SYSDATE + 365); — Pass Username, Resp Application Short Name and Resp Key
COMMIT;
dbms_output.put_line(‘Responsibility added successfully’);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Responsibility is not added – Exception: ‘|| SQLCODE|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;