Creating Users

Creating User in Back end

DECLARE
  v_user_name  VARCHAR2(30):=UPPER('&Enter_User_Name');
  v_password   VARCHAR2(30):='&Enter_Password';
  v_session_id INTEGER     := USERENV('sessionid');
BEGIN
  fnd_user_pkg.createuser (
    x_user_name => v_user_name,
    x_owner => NULL,
    x_unencrypted_password => v_password,
    x_session_number => v_session_id,
    x_start_date => SYSDATE,
    x_end_date => NULL
  );
  COMMIT;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
END;

Creating Responsibility for the USER

BEGIN
fnd_user_pkg.addresp ('&Enter_User_Name','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD'
                     ,'Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 365);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;

Copying User Responsibilities from One User to Other User


DECLARE
   v_user_name        VARCHAR2 (20) := '&Enter_User_Name';
   v_description      VARCHAR2 (300);
   v_appl_shrt_name   VARCHAR2 (20);
   v_resp_key         VARCHAR2 (50);

   CURSOR add_resp
   IS
      SELECT *
        FROM FND_USER_RESP_GROUPS_DIRECT fur
       WHERE user_id = (SELECT user_id
                          FROM fnd_user
                         WHERE user_name = '&From_User_Name');

BEGIN
   FOR i IN add_resp
   LOOP
      SELECT fa.application_short_name,
             frv.responsibility_key,
             frv.description
        INTO v_appl_shrt_name, v_resp_key, v_description
        FROM FND_RESPONSIBILITY_VL frv, fnd_application fa
       WHERE     frv.application_id = fa.application_id
             AND fa.application_id = i.responsibility_application_id
             AND frv.responsibility_id = i.RESPONSIBILITY_ID;

      BEGIN
         fnd_user_pkg.addresp (username         => v_user_name,
                               resp_app         => v_appl_shrt_name,
                               resp_key         => v_resp_key,
                               security_group   => 'STANDARD',
                               description      => v_description,
                               start_date       => SYSDATE,
                               end_date         => NULL);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('ERROR:' || SQLERRM);
      END;

      DBMS_OUTPUT.put_line (
            'The responsibility '
         || v_description
         || ' is added to the user '
         || v_user_name);
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Responsibility IS NOT added due to '
         || SQLCODE
         || '; '
         || SUBSTR (SQLERRM, 1, 250));
      ROLLBACK;
END;

Removing End Dates for User Responsibilities

DECLARE

p_user_name VARCHAR2 (50) := 'RANREDDY';
l_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;

CURSOR USER_RESP(cp_user_id NUMBER)
IS
(SELECT responsibility_application_id, responsibility_id,start_date
--INTO v_application_id, v_responsibility_id
FROM FND_USER_RESP_GROUPS_DIRECT
WHERE user_id = cp_user_id
and end_date is not null);

BEGIN

BEGIN
select user_id
into l_user_id
from fnd_user
where user_name = p_user_name;

for i in user_resp(l_user_id) loop

fnd_user_resp_groups_api.update_assignment
(user_id => l_user_id,
responsibility_id => i.responsibility_id,
responsibility_application_id => i.responsibility_application_id,
security_group_id => 0,
start_date => i.start_date,
end_date => NULL,
description => NULL
);

DBMS_OUTPUT.put_line
('The End Date has been removed from responsibility'||i.responsibility_id);
COMMIT;

end loop;

END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unhandled Exp: '||SQLERRM);

END;