Friday, April 10, 2015

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.


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect