API to Create User

This Script will prompt for user name and employee name.

DECLARE

   v_session_id      INTEGER                         := USERENV ('sessionid');

   v_user_name       VARCHAR2 (30)                    := UPPER ('&User_Name');

   v_employee_name   per_all_people_f.full_name%TYPE      := '&employee_name';

   v_employee_id     NUMBER;

   v_email_address   per_all_people_f.email_address%TYPE;

BEGIN

   BEGIN

      SELECT   person_id, email_address

          INTO v_employee_id, v_email_address

          FROM per_all_people_f

         WHERE UPPER (full_name) LIKE UPPER ('%v_employee_name%')

      GROUP BY person_id, email_address;

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         DBMS_OUTPUT.put_line (   'Employee '

                               || v_employee_name

                               || ' does not exist'

                              );

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Unexpected Error ' || SQLERRM);

         DBMS_OUTPUT.put_line ('while selected person_id');

   END;

   fnd_user_pkg.createuser (x_user_name                       => v_user_name,

                            x_owner                           => '',

                            x_unencrypted_password            => 'welcome1',

                            x_session_number                  => v_session_id,

                            x_start_date                      => SYSDATE - 10,

                            x_end_date                        => SYSDATE + 100,

                            x_last_logon_date                 => SYSDATE - 10,

                            x_description                     =>    v_user_name

                                                                 || ' Created using API',

                            x_password_date                   => SYSDATE - 10,

                            x_password_accesses_left          => 10000,

                            x_password_lifespan_accesses      => 10000,

                            x_password_lifespan_days          => 10000,

                            x_employee_id                     => v_employee_id,

                            x_email_address                   => v_email_address,

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

                         start_date          => SYSDATE - 10,

                         end_date            => SYSDATE + 1000

                        );

-- Created by Suresh Vaishya

Commit;

END;

/