Application Object Library (AOL) Queries

Query to search Responsibility by Concurrent program name

SELECT frt.responsibility_name, frg.request_group_name, frg.description

  FROM fnd_request_groups frg,

       fnd_request_group_units frgu,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_responsibility_tl frt,

       fnd_responsibility frs

 WHERE     frgu.unit_application_id = fcp.application_id

       AND frgu.request_unit_id = fcp.concurrent_program_id

       AND frg.request_group_id = frgu.request_group_id

       AND frg.application_id = frgu.application_id

       AND fcpt.source_lang = USERENV ('LANG')

       AND fcp.application_id = fcpt.application_id

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND frs.application_id = frt.application_id

       AND frs.responsibility_id = frt.responsibility_id

       AND frt.source_lang = USERENV ('LANG')

       AND frs.request_group_id = frg.request_group_id

       AND frs.application_id = frg.application_id

       AND fcp.concurrent_program_name = <shortname>

       AND fcpt.user_concurrent_program_name LIKE <User concurrent program>>

Query to get the request group for a given responsibility

SELECT DISTINCT

       FRT.RESPONSIBILITY_NAME,

       FMT.USER_MENU_NAME,

       FAT.APPLICATION_NAME,

       (SELECT REQUEST_GROUP_NAME

          FROM APPS.FND_REQUEST_GROUPS FRG

         WHERE     FRG.APPLICATION_ID = FR.APPLICATION_ID

               AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID)

          REQUET_GRP

  FROM APPS.FND_RESPONSIBILITY_TL FRT,

       APPS.FND_RESPONSIBILITY FR,

       APPS.FND_MENUS_TL FMT,

       APPS.FND_MENUS FM,

       APPS.FND_APPLICATION_TL FAT,

       APPS.FND_APPLICATION FA

 WHERE     FRT.RESPONSIBILITY_ID(+) = FR.RESPONSIBILITY_ID

       AND FRT.RESPONSIBILITY_NAME = '&Responsibility name'

       AND FR.MENU_ID = FMT.MENU_ID

       AND FR.MENU_ID = FM.MENU_ID

       AND FAT.APPLICATION_ID = FA.APPLICATION_ID

       AND FA.APPLICATION_ID = FR.APPLICATION_ID

       AND FRT.LANGUAGE = 'US'

       AND FAT.LANGUAGE = 'US';

Query 1: Select responsibility name along with application name

SELECT application_short_name, frt.responsibility_id, frt.responsibility_name

  FROM fnd_responsibility_tl frt, fnd_application fa

 WHERE fa.application_id = frt.application_id;

Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, c.user_menu_name

  FROM apps.fnd_responsibility_tl a,

       apps.fnd_responsibility b,

       apps.fnd_menus_tl c,

       apps.fnd_menus d,

       apps.fnd_application_tl e,

       apps.fnd_application f

 WHERE     a.responsibility_id(+) = b.responsibility_id

       AND a.responsibility_id = &resp_id

       AND b.menu_id = c.menu_id

       AND b.menu_id = d.menu_id

       AND e.application_id = f.application_id

       AND f.application_id = b.application_id

       AND a.LANGUAGE = 'US';

Query 3: Get User name and related assigned responsibilities

  SELECT DISTINCT u.user_id,

                  u.user_name user_name,

                  r.responsibility_name responsiblity,

                  a.application_name application

    FROM fnd_user u,

         fnd_user_resp_groups g,

         fnd_application_tl a,

         fnd_responsibility_tl r

   WHERE     g.user_id(+) = u.user_id

         AND g.responsibility_application_id = a.application_id

         AND a.application_id = r.application_id

         AND g.responsibility_id = r.responsibility_id

ORDER BY 1;

Query 4: Get Request Group associate with Responsibility Name

  SELECT responsibility_name responsibility,

         request_group_name,

         frg.description

    FROM fnd_request_groups frg, fnd_responsibility_vl frv

   WHERE frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name

Query 5: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tablesapplsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

  SELECT ffft.user_function_name "User Form Name",

         ffcr.SEQUENCE,

         ffcr.description,

         ffcr.rule_type,

         ffcr.enabled,

         ffcr.trigger_event,

         ffcr.trigger_object,

         ffcr.condition,

         ffcr.fire_in_enter_query

    FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft

   WHERE ffcr.ID = ffft.function_id

ORDER BY 1;

Query 6: Query to view the patch level status of all modules

SELECT a.application_name,

       DECODE (b.status,

               ‘I’, ‘Installed’,

               ‘S’, ‘Shared’,

               ‘N / A’)

          status,

       patch_level

  FROM apps.fnd_application_vl a, apps.fnd_product_installations b

 WHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibility

  SELECT responsibility_name,

         frg.request_group_name,

         fcpv.user_concurrent_program_name,

         fcpv.description

    FROM fnd_request_groups frg,

         fnd_request_group_units frgu,

         fnd_concurrent_programs_vl fcpv,

         fnd_responsibility_vl frv

   WHERE     frgu.request_unit_type = ‘P’

         AND frgu.request_group_id = frg.request_group_id

         AND frgu.request_unit_id = fcpv.concurrent_program_id

         AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 8: SQL to view all requests who have attached to a responsibility

  SELECT responsibility_name,

         frg.request_group_name,

         fcpv.user_concurrent_program_name,

         fcpv.description

    FROM fnd_request_groups frg,

         fnd_request_group_units frgu,

         fnd_concurrent_programs_vl fcpv,

         fnd_responsibility_vl frv

   WHERE     frgu.request_unit_type = ‘P’

         AND frgu.request_group_id = frg.request_group_id

         AND frgu.request_unit_id = fcpv.concurrent_program_id

         AND frv.request_group_id = frg.request_group_id

ORDER BY responsibility_name;

Query 9: SQL to view all types of request Application wise

  SELECT fa.application_short_name,

         fcpv.user_concurrent_program_name,

         description,

         DECODE (fcpv.execution_method_code,

                 'B', 'Request Set Stage Function',

                 'Q', 'SQL*Plus',

                 'H', 'Host',

                 'L', 'SQL*Loader',

                 'A', 'Spawned',

                 'I', 'PL/SQL Stored Procedure',

                 'P', 'Oracle Reports',

                 'S', 'Immediate',

                 fcpv.execution_method_code)

            exe_method,

         output_file_type,

         program_type,

         printer_name,

         minimum_width,

         minimum_length,

         concurrent_program_name,

         concurrent_program_id

    FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

   WHERE fcpv.application_id = fa.application_id

ORDER BY description

Query 10: SQL to view concurrent request processing time, quite useful

  SELECT f.request_id,

         pt.user_concurrent_program_name user_concurrent_program_name,

         f.actual_start_date actual_start_date,

         f.actual_completion_date actual_completion_date,

            FLOOR (

                 (  (f.actual_completion_date - f.actual_start_date)

                  * 24

                  * 60

                  * 60)

               / 3600)

         || '

          HOURS '

         || FLOOR (

                 (  (  (f.actual_completion_date - f.actual_start_date)

                     * 24

                     * 60

                     * 60)

                  -   FLOOR (

                           (  (f.actual_completion_date - f.actual_start_date)

                            * 24

                            * 60

                            * 60)

                         / 3600)

                    * 3600)

               / 60)

         || ' MINUTES '

         || ROUND (

               (  (  (f.actual_completion_date - f.actual_start_date)

                   * 24

                   * 60

                   * 60)

                -   FLOOR (

                         (  (f.actual_completion_date - f.actual_start_date)

                          * 24

                          * 60

                          * 60)

                       / 3600)

                  * 3600

                - (  FLOOR (

                          (  (  (f.actual_completion_date - f.actual_start_date)

                              * 24

                              * 60

                              * 60)

                           -   FLOOR (

                                    (  (  f.actual_completion_date

                                        - f.actual_start_date)

                                     * 24

                                     * 60

                                     * 60)

                                  / 3600)

                             * 3600)

                        / 60)

                   * 60)))

         || ' SECS '

            time_difference,

         DECODE (

            p.concurrent_program_name,

            'ALECDC', p.concurrent_program_name || '[' || f.description || ']',

            p.concurrent_program_name)

            concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'Running',

                 'C', 'Complete',

                 f.phase_code)

            Phase,

         f.status_code

    FROM apps.fnd_concurrent_programs p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests f

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('Lang')

         AND f.actual_start_date IS NOT NULL

ORDER BY f.actual_completion_date - f.actual_start_date DESC;

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and Responsibility name like ‘%General%Ledger%’

  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,

         DECODE (pov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Resp',

                 10004, 'User')

            Option_Level,

         DECODE (pov.level_id,

                 10001, 'Site',

                 10002, appl.application_short_name,

                 10003, resp.responsibility_name,

                 10004, u.user_name)

            Level_Value,

         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value

    FROM fnd_profile_option_values pov,

         fnd_responsibility_tl resp,

         fnd_application appl,

         fnd_user u,

         fnd_profile_options pro,

         fnd_profile_options_tl pro1

   WHERE     pro1.user_profile_option_name LIKE ('%Ledger%')

         AND pro.profile_option_name = pro1.profile_option_name

         AND pro.profile_option_id = pov.profile_option_id

         AND resp.responsibility_name LIKE '%General%Ledger%' /* comment this line if you need to check profiles for all responsibilities */

         AND pov.level_value = resp.responsibility_id(+)

         AND pov.level_value = appl.application_id(+)

         AND pov.level_value = u.user_id(+)

ORDER BY 1, 2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,

         DECODE (pov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Resp',

                 10004, 'User')

            Option_Level,

         DECODE (pov.level_id,

                 10001, 'Site',

                 10002, appl.application_short_name,

                 10003, resp.responsibility_name,

                 10004, u.user_name)

            Level_Value,

         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value

    FROM fnd_profile_option_values pov,

         fnd_responsibility_tl resp,

         fnd_application appl,

         fnd_user u,

         fnd_profile_options pro,

         fnd_profile_options_tl pro1

   WHERE     pro.profile_option_name = pro1.profile_option_name

         AND pro.profile_option_id = pov.profile_option_id

         AND resp.responsibility_name LIKE '%General%Ledger%'

         AND pov.level_value = resp.responsibility_id(+)

         AND pov.level_value = appl.application_id(+)

         AND pov.level_value = u.user_id(+)

ORDER BY 1, 2;

Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application.