Query to find all responsibilities of a user

The following query finds all the responsibilities that are assigned to a user. This query can be useful if you want to know if a user has a particular responsibility or any responsibility that has been end dated. However, if you just want to see the current "Active" responsibilities of the user, uncomment the "FURG.END_DATE" condition (very bottom line of the query).

In the following example, I used "AMOHSIN" as my user name to list all my responsibilities.

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

-- Query to find all responsibilities of a user

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

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND 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 UPPER(fu.user_name)      =  UPPER('AMOHSIN')  -- <change it>

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

 ORDER BY frt.responsibility_name;

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;

Query to add SysAdmin responsibility to a user

The following query adds the System Administrator (SYSADMIN) responsibility to a user. The query needs to be run by APPS.

In the following example, I used 'AMOHSIN' as my username.

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

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

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

BEGIN

  

   FND_USER_PKG.ADDRESP(

      USERNAME        =>  'AMOHSIN',              -- User Name -- <change it>

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

      RESP_KEY        =>  'SYSTEM_ADMINISTRATOR', -- Responsibility Key

      SECURITY_GROUP  =>  'STANDARD',

      DESCRIPTION     =>  NULL,

      START_DATE      =>  SYSDATE,

      END_DATE        =>  NULL);

  

   COMMIT;

  

   DBMS_OUTPUT.PUT_LINE('SYSADMIN Responsibility successfully added');

    

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('SYSADMIN responsibility not added due to ' || SQLERRM);

      ROLLBACK;

END;