R12 Add User and Add Responsibility

Add User

DECLARE
   v_user_name     VARCHAR2 (100) := 'KARTHIKEYANC.GDC';
   v_owner              VARCHAR2 (50)   := NULL;
   v_password        VARCHAR2 (100) := 'Oracle123';
   v_description     VARCHAR2 (100) := 'NEW USER';
BEGIN
   fnd_user_pkg.createuser
                           (x_user_name                               => v_user_name,
                            x_owner                                        => v_owner,
                            x_unencrypted_password         => v_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;
   DBMS_OUTPUT.put_line ('User ' || v_user_name || ' is created successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
                (   'Error encountered while creating user and the error is '|| SQLERRM
                );
END;

Verify User Creation

SELECT  fu.user_name,
               frt.responsibility_name
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'karthikeyanc.gdc'

Add Responsibility

DECLARE
   v_user_name                  VARCHAR2 (100) := 'KARTHIKEYANC.GDC';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
   v_description                  VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                    
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
      AND    frt.LANGUAGE = USERENV ('LANG')
      AND    frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.addresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group,
                                              description        => v_description,
                                              start_date          => SYSDATE,
                                              end_date            => NULL
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while attaching responsibilty to the user and the error is '
          || SQLERRM
         );
END;     

Verify Responsibility

SELECT fa.application_short_name,
              fr.responsibility_key,
              frg.security_group_key,
              frt.description
  FROM fnd_responsibility fr,
              fnd_application fa,
              fnd_security_groups frg,
              fnd_responsibility_tl frt
 WHERE fr.application_id = fa.application_id
   AND    fr.data_group_id = frg.security_group_id
   AND    fr.responsibility_id = frt.responsibility_id
   AND    frt.LANGUAGE = USERENV ('LANG')
   AND    frt.responsibility_name = 'System Administrator';