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;

Query to find runtime of a concurrent program

The following query finds total run-time (in minutes) for a concurrent program. Thus, with a little modification to this query, you can track which concurrent programs take (very) long time to complete, and may need performance tuning.

Change the concurrent program name (tl.user_concurrent_program_name, see below) according to your search criteria. In this example, my concurrent program is "Autoinvoice Import Program". You can also uncomment the "&Start_Date" line to get the list for a specific date.

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

-- Query to find runtime for a concurrent program

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

SELECT /*+ rule */

       rq.parent_request_id                   "Parent Req. ID",

       rq.request_id                          "Req. ID",

       tl.user_concurrent_program_name        "Program Name",

       rq.actual_start_date                   "Start Date",

       rq.actual_completion_date              "Completion Date",

       ROUND((rq.actual_completion_date -

           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      

  FROM applsys.fnd_concurrent_programs_tl  tl,

       applsys.fnd_concurrent_requests     rq

 WHERE tl.application_id        = rq.program_application_id

   AND tl.concurrent_program_id = rq.concurrent_program_id

   AND tl.LANGUAGE              = USERENV('LANG')

   AND rq.actual_start_date IS NOT NULL

   AND rq.actual_completion_date IS NOT NULL

   AND tl.user_concurrent_program_name = 'Autoinvoice Import Program'  -- <change it>

   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date

 ORDER BY rq.request_id DESC;

Query to find Concurrent Programs associated with a Value Set

Following query finds all the concurrent program(s) that are associated with a value set. Thus, before you want to make any change to that value set, you should be able to find what concurrent programs will be affected by your modification, so you can notify the concerned parties.

Change the value set name (ffvs.flex_value_set_name, see below) according to your search criteria. In this example, I used "XX_TAMS_CONV_TYPES" as my value set name.

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

-- Query to find Concurrent Programs associated with a Value Set

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

SELECT 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",

       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"

  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 ffvs.flex_value_set_name           =  'XX_TAMS_CONV_TYPES'  -- <change it>

 ORDER BY fcpl.user_concurrent_program_name;

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 parameters and value sets associated with 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 "TAMS 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  =  'TAMS AR Conversion Program'  -- <change it>

 ORDER BY fdfcuv.column_seq_num;

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 theAPPLICATION_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;