Create and update Update User API

CREATE USER

In a business entity using Oracle Applications as and when new users want to access the system, we need to create individual accounts for the users.

One of the options is to create the user manually from the System Administrator Responsibility, but in most business scenarios the business will have the user details stored in the database which can be picked up by a script , which will subsequently call the API to create multiple users in a single program run.

The generic API provided by Oracle to achieve it is : fnd_user_pkg.createuser , fnd_user_pkg is the Package and create user is the procedure which performs the intended operation.

Given below is a sample anonymous block which can be used to create a user in the system.

DECLARE

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('Ashwini');

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                      => 'CUST'

                                    ,x_unencrypted_password       => '123456'

                                    ,x_session_number             => null

                                    ,x_start_date                 => SYSDATE - 10

                                    ,x_end_date                   => null

                                    ,x_last_logon_date            => null

                                    ,x_description                => 'proracleapps.blogspot.com'

                                    ,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           => ''

                                    ,x_customer_id   => ''

                                    ,x_supplier_id   => '');

  EXCEPTION

  WHEN OTHERS

  THEN

     dbms_output.put_line('Exception Occurred in Processing ');

     dbms_output.put_line('Oracle Error '||SQLERRM);

END;

The above script create a user with username  'ASHWINI' and password '123456' in the system. In case the username is already in user the API will throw an error.

If no user exists with the username a new user is created with this user name and password.

When the user tries to login with the username he is prompted to change the password and set his new password.

Depending on the business requirements other parameter values can also be passed in.

UPDATE USER

Once the users have been created in the oracle system, there might be scenarios when there is a requirement to update some properties of the user.
 Oracle Provides an API for the functionality: fnd_user_pkg.updateuser

Given below is a sample script to perform the intended functionality

declare

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('test2');

begin

fnd_user_pkg.UpdateUser (

  x_user_name                  => v_user_name,

  x_owner                      => 'CUST',

  x_unencrypted_password       => '456789',  --new password

  x_session_number             => v_session_id,

  x_start_date                 => null,

  x_end_date                   => null, -- populated when disabling the user

  x_last_logon_date            => null,

  x_description                => null,

  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_old_password               => 'asdfg'  --old password

);

  EXCEPTION

  WHEN OTHERS

  THEN

     dbms_output.put_line('Exception Occurred in Processing ');

     dbms_output.put_line('Oracle Error '||SQLERRM);

END;

In case the user exists in the system the API will successfully update the uses properties. In case no user with the provided username exists in the system Oracle will throw an error.