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 find Application Short Name of a module

The following query lists all the applications related information. This query can be used to find the APPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.) that are often used for downloading FNDLOAD LDT files, adding responsibility to a user and many more.

You can uncomment the FAT.APPLICATION_NAME condition (very bottom line of the query) to learn about a particular module. In this case, I used "Payables".

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

-- Query to find all APPLICATION (module) information

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

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name",

       fa.basepath                 "Basepath"

  FROM fnd_application     fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   -- AND fat.application_name = 'Payables'  -- <change it>

 ORDER BY fat.application_name;

Query to find Parameters and Value Sets associated with a Concurrent Program

Following query finds the parameters and the value sets that are associated with a Concurrent Program. Change concurrent program name (fcpl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program name is "XX AR Conversion Program".

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

-- Query to find Parameters and Value Sets associated with a Concurrent Program

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

SELECT fcpl.user_concurrent_program_name  "Concurrent Program Name",

       fcp.concurrent_program_name        "Program Short Name",

       fdfcuv.column_seq_num              "Column Seq #",

       fdfcuv.end_user_column_name        "Parameter Name",

       fdfcuv.form_left_prompt            "Prompt Name",

       fdfcuv.enabled_flag                "Enabled Flag",

       fdfcuv.required_flag               "Required Flag",

       fdfcuv.display_flag                "Display Flag",

       fdfcuv.flex_value_set_id           "Value Set ID",

       ffvs.flex_value_set_name           "Value Set Name",

       flv.meaning                        "Default Type",

       fdfcuv.default_value               "Default Value"

  FROM fnd_concurrent_programs      fcp,

       fnd_concurrent_programs_tl   fcpl,

       fnd_descr_flex_col_usage_vl  fdfcuv,

       fnd_flex_value_sets          ffvs,

       fnd_lookup_values            flv

 WHERE fcp.concurrent_program_id          =  fcpl.concurrent_program_id

   AND fdfcuv.descriptive_flexfield_name  =  '$SRS$.' || fcp.concurrent_program_name

   AND ffvs.flex_value_set_id             =  fdfcuv.flex_value_set_id

   AND flv.lookup_type(+)                 =  'FLEX_DEFAULT_TYPE'

   AND flv.lookup_code(+)                 =  fdfcuv.default_type

   AND fcpl.LANGUAGE                      =  USERENV('LANG')

   AND flv.LANGUAGE(+)                    =  USERENV('LANG')

   AND fdfcuv.enabled_flag                =  'Y'

   AND fcpl.user_concurrent_program_name  =  'XX AR Conversion Program'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;

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;