FND_USER script

Purpose-

Can be used to create an FND_USER for any developer that joins the team

This simple script will set all the desired admin responsibilities and all common profile options for a given FND_USER.
Salient features of this simple script are
1. If a user is inactive, that FND_USER will be re-activated
2. If any of their Sysadmin or WF Admin or Application Developer or Functional Admin responsibilities are inactive, those responsibilities will be re-activated
3. Add commonly used responsibilities will be assigned to the user
3. Personalization, Diagnostic and Utilities profile options will be assigned to this user.
4. Password expiration will be removed from the FND_USER

In a nutshell, use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.
In case a user does not exist, then new user will be created with default password being oracle123

SCRIPT BELOW

################################################################################

Please find the script below


Save this as a SQL File, and it will prompt for parameter User Name



DECLARE
  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');
  result    BOOLEAN;
  v_user_id INTEGER;

  FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
    p_check c_check%ROWTYPE;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    IF c_check%FOUND
    THEN
      /*Yes, it exists*/
      CLOSE c_check;
      RETURN TRUE;
    END IF;
    CLOSE c_check;
    RETURN FALSE;
  END check_fu_name;

BEGIN
  IF NOT (check_fu_name(p_user_name => v_user_name))
  THEN
    fnd_user_pkg.createuser(x_user_name                  => v_user_name
                           ,x_owner                      => ''
                           ,x_unencrypted_password       => 'oracle123'
                           ,x_session_number             => v_session_id
                           ,x_start_date                 => SYSDATE - 10
                           ,x_end_date                   => SYSDATE + 100
                           ,x_last_logon_date            => SYSDATE - 10
                           ,x_description                => 'BNitin'
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000
                           ,x_employee_id                => NULL /*Change this id by running below SQL*/
                            /*   
                                                             SELECT person_id
                                                                   ,full_name
                                                             FROM   per_all_people_f
                                                             WHERE  upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
                                                             GROUP  BY person_id
                                                                      ,full_name
                                                             */
                           ,x_email_address => ' abc@gmail.com'
                           ,x_fax           => ''
                           ,x_customer_id   => ''
                           ,x_supplier_id   => '');
   dbms_output.put_line ( 'FND_USER Created' ) ;
  ELSE
    fnd_user_pkg.updateuser(x_user_name                  => v_user_name
                           ,x_owner                      => 'CUST'
                           ,x_end_date                   => fnd_user_pkg.null_date
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000);
   dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;                          
  END IF;
  SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = v_user_name;
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FND_FUNC_ADMIN'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'SYSADMIN'
                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FNDWF_ADMIN_WEB'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'APPLICATION_DEVELOPER'
                      ,security_group => 'STANDARD'
                      ,description    => 'BNitin dbawalkspot.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
 
    fnd_user_pkg.addresp(username       => v_user_name,
                       resp_app       => 'ICX',
                       resp_key       => 'PREFERENCES',
                       security_group => 'STANDARD',
                       description    => 'BNitin dbawalkspot.com',
                       start_date     => sysdate - 1,
                       end_date       => null);

 result := fnd_profile.save(x_name        => 'APPS_SSO_LOCAL_LOGIN'
                            ,x_value       => 'BOTH'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_CUSTOM_OA_DEFINTION'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

 result := fnd_profile.save(x_name        => 'FND_HIDE_DIAGNOSTICS'
                            ,x_value       => 'N'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);

                           
  COMMIT;
END;
/

################################################################################