Wednesday, February 1, 2017

Query to create user from backend oracle apps and ADD responsibility

Script to User and ADD responsibility from backend

Code (SQL):
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;
Script to ADD responsibility

Code (SQL):
BEGIN
fnd_user_pkg.addresp ('&Enter_User_Name','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
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;

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...