1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creating User and Adding responsibility from backend

Discussion in 'Oracle Apps Technical' started by niraj.mca03, Sep 29, 2014.

  1. niraj.mca03

    niraj.mca03 Guest

    Introduction

    Script to User and ADD responsibility from backend


    The code

    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;
     
     
  2. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Niraj,

    Nice info, for additional flexibity this function can be modified by adding below piece of code to derive Responsibility_key and Application_short_name using Responsibility Name as input from user.

    Code (SQL):
     SELECT    FR.responsibility_key,
                FA.application_short_name
        INTO    l_resp_key ,
                l_resp_app
        FROM    FND_RESPONSIBILITY_TL    FRT,
                FND_RESPONSIBILITY        FR,
                FND_APPLICATION            FA
        WHERE    FRT.responsibility_id   = FR.responsibility_id
        AND        FRT.application_id      = FA.application_id
        AND        FRT.responsibility_name = l_resp_name ;
    Thanks & Regards,
    Yowan Cristo