Query to add any responsibility to a user

The following query adds a particular responsibility to a particular user. This query needs to be run by APPS.

The query will prompt for a "User Name" and "Reponsibility Name" that need to be added to that user. The query first finds the RESPONSIBILITY_KEY and APPLICATION_SHORT_NAME for that responsibility, and then adds it to the user using Oracle's FND_USER_PKG.ADDRESP function.

In the following example, I used 'AMOHSIN' as my username, and added "System Administrator" to my responsibilities. Change these two input parameters as per your requirement.

-------------------------------------------------------------------------------

-- Query to add a responsibility to a user, using FND_USER_PKG.ADDRESP

-------------------------------------------------------------------------------

DECLARE

   v_username         fnd_user.user_name%TYPE;

   v_resp_key         fnd_responsibility.responsibility_key%TYPE;

   v_apps_short_name  fnd_application.application_short_name%TYPE;

   v_resp_name        fnd_responsibility_tl.responsibility_name%TYPE;

      

BEGIN

   v_username   :=  '&USER_NAME';  -- eg. 'AMOHSIN'

   v_resp_name  :=  '&RESP_NAME';  -- eg. 'System Administrator'

  

   -------------------------------------------------------------

   -- find APPLICATION_SHORT_NAME and RESPONSIBILITY_KEY for

   -- the Responsibility that need to be added

   -------------------------------------------------------------

   SELECT fr.responsibility_key,

          fa.application_short_name

     INTO v_resp_key,

          v_apps_short_name

     FROM applsys.fnd_responsibility_tl      frt,

          applsys.fnd_responsibility         fr,

          applsys.fnd_application_tl         fat,

          applsys.fnd_application            fa

    WHERE fr.responsibility_id     =  frt.responsibility_id

      AND fa.application_id        =  fat.application_id

      AND fr.application_id        =  fat.application_id

      AND frt.language             =  USERENV('LANG')

      AND fat.language             =  USERENV('LANG')

      AND frt.responsibility_name  =  v_resp_name;

     

   -------------------------------------------------------------

   -- if found, then add it to the user; else jump into exception

   -------------------------------------------------------------

   FND_USER_PKG.ADDRESP(

      USERNAME        =>  UPPER(v_username), -- User Name: 'AMOHSIN'

      RESP_APP        =>  v_apps_short_name, -- Apps Short Name: 'SYSADMIN'

      RESP_KEY        =>  v_resp_key,        -- Resp Key: 'SYSTEM_ADMINISTRATOR'

      SECURITY_GROUP  =>  'STANDARD',

      DESCRIPTION     =>  NULL,

      START_DATE      =>  SYSDATE,

      END_DATE        =>  NULL);

 

   COMMIT;

 

   DBMS_OUTPUT.PUT_LINE(v_resp_name || ' responsibility added successfully for ' || v_username);

   

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(v_resp_name || ' responsibility not added for ' || v_username);

      DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SUBSTR(SQLERRM, 1, 100));

      ROLLBACK;

END;