AOL Queries

-- -----------------------------------------------------------------------------------
Query to find Concurrent program information
-- -----------------------------------------------------------------------------------

SELECT                                                    

         DISTINCT fcpt.user_concurrent_program_name, frt.responsibility_name

         frg.request_group_name,

         frgu.request_unit_type,

         frgu.request_unit_id,

         decode(instance,'CFGAPPRD','FAP1C','N'),

 FROM apps.fnd_Responsibility fr,                  

         apps.fnd_responsibility_tl frt,

         apps.fnd_request_groups frg,

         apps.fnd_request_group_units frgu,

         apps.fnd_concurrent_programs_tl fcpt

 WHERE     frt.responsibility_id = fr.responsibility_id

         AND frg.request_group_id = fr.request_group_id

         AND frgu.request_group_id = frg.request_group_id

         AND fcpt.concurrent_program_id = frgu.request_unit_id

         AND frt.language = USERENV ('LANG')

         AND fcpt.language = USERENV ('LANG')

         AND fcpt.user_concurrent_program_name LIKE

                '%XX_CONCURRENT_PROGRAM_NAME%'

         ORDER BY 2;

-- -----------------------------------------------------------------------------------
Query to get Menus & Functions attached to Responsibility
-- -----------------------------------------------------------------------------------
SELECT     LEVEL, LPAD (' ', (LEVEL - 1) * 3) || prompt, description

      FROM fnd_menu_entries_vl fme

     WHERE prompt IS NOT NULL

       AND grant_flag = 'Y'

       AND NOT EXISTS (

              SELECT 1

                FROM fnd_resp_functions frf, fnd_responsibility_vl frv

               WHERE frv.responsibility_id = frf.responsibility_id

                 AND frf.application_id = frv.application_id

                 AND frf.rule_type = 'M'

                 AND frf.action_id = NVL (fme.sub_menu_id, -1)

                 AND frv.responsibility_name = :resp_name)

       AND NOT EXISTS (

              SELECT 1

                FROM fnd_resp_functions frf, fnd_responsibility_vl frv

               WHERE frv.responsibility_id = frf.responsibility_id

                 AND frf.application_id = frv.application_id

                 AND frf.rule_type = 'F'

                 AND frf.action_id = NVL (fme.function_id, -1)

                 AND frv.responsibility_name = :xx_responsibility_name)

CONNECT BY PRIOR sub_menu_id = menu_id

       AND prompt IS NOT NULL

       AND NOT EXISTS (

              SELECT 1

                FROM fnd_resp_functions frf, fnd_responsibility_vl frv

               WHERE frv.responsibility_id = frf.responsibility_id

                 AND frf.application_id = frv.application_id

                 AND frf.rule_type = 'M'

                 AND frf.action_id = NVL (fme.menu_id, -1)

                 AND frv.responsibility_name = :xx_responsibility_name)

START WITH menu_id = (SELECT menu_id

                        FROM fnd_responsibility_vl

                       WHERE responsibility_name = :xx_responsibility_name)

  ORDER SIBLINGS BY entry_sequence;

-- -----------------------------------------------------------------------------------
Query to Find function attached to menu
-- -----------------------------------------------------------------------------------
SELECT fme.menu_id, fm.menu_name, fme.entry_sequence, fff.function_name

  FROM fnd_menu_entries_vl fme, fnd_menus_vl fm, fnd_form_functions_vl fff

 WHERE fme.menu_id = fm.menu_id

   AND fme.function_id = fff.function_id

   AND function_name LIKE 'XX_FUNCTION_NAME%';
-- -----------------------------------------------------------------------------------
 Query for finding menu exclusion for a responsibility
-- -----------------------------------------------------------------------------------
SELECT res.responsibility_name, app.application_name, res.responsibility_key,

       res.description, res.start_date, res.end_date, dat.data_group_name,

       apd.application_name, mnu.menu_name, req.request_group_name,

       apr.application_name,

       DECODE (exc.rule_type,

               'F', 'Function',

               'M', 'Menu',

               rule_type

              ) exclusion_type,

       DECODE (exc.rule_type,

               'F', (SELECT function_name || ',' || description

                       FROM fnd_form_functions_vl fnc

                      WHERE fnc.function_id = exc.action_id),

               'M', (SELECT menu_name || ',' || description

                       FROM fnd_menus_vl imn

                      WHERE imn.menu_id = exc.action_id),

               TO_CHAR (exc.action_id)

              ) excluded_menu_or_func

  FROM apps.fnd_responsibility_vl res,

       apps.fnd_application_vl app,

       apps.fnd_data_groups dat,

       apps.fnd_menus_vl mnu,

       apps.fnd_request_groups req,

       apps.fnd_application_vl apd,

       apps.fnd_application_vl apr,

       apps.fnd_resp_functions exc

 WHERE res.application_id = app.application_id

   AND res.data_group_id = dat.data_group_id

   AND res.data_group_application_id = apd.application_id

   AND res.menu_id = mnu.menu_id

   AND req.request_group_id = res.request_group_id

   AND req.application_id = res.group_application_id

   AND apr.application_id = req.application_id

   AND exc.application_id = res.application_id

   AND exc.responsibility_id = res.responsibility_id

   AND responsibility_name = 'XX_RESPONSIBILITY_NAME';  

 -- -----------------------------------------------------------------------------------
 To find responsibilities assigned to particular user
 -- -----------------------------------------------------------------------------------

SELECT resp_type, user_id, user_name, responsibility_id, responsibility_name,

       start_date, end_date, application_short_name, application_name

  FROM (SELECT 'DIRECT' resp_type, fu.user_id, fu.user_name,

               resp.responsibility_id, resp.responsibility_name,

               frd.start_date, frd.end_date, app.application_short_name,

               app.application_name

          FROM fnd_user fu,

               fnd_user_resp_groups_direct frd,

               fnd_responsibility_vl resp,

               fnd_application_vl app

         WHERE fu.user_id = frd.user_id

           AND frd.responsibility_id = resp.responsibility_id

           AND resp.application_id = app.application_id

        UNION ALL

        SELECT 'INDIRECT' resp_type, fu.user_id, fu.user_name,

               resp.responsibility_id, resp.responsibility_name,

               fri.start_date, fri.end_date, app.application_short_name,

               app.application_name

          FROM fnd_user fu,

               fnd_user_resp_groups_indirect fri,

               fnd_responsibility_vl resp,

               fnd_application_vl app

         WHERE fu.user_id = fri.user_id

           AND fri.responsibility_id = resp.responsibility_id

           AND resp.application_id = app.application_id)

 WHERE 1 = 1

   AND user_name =   'XX_USER_NAME'

                      -- Comment this if you need all user of a responsibility

   AND responsibility_name = 'XX_RESPONSIBILITY_NAME'; -- Comment this if you need all responsibilities of a user

 -- --------------------------------------------------------------------------------------
 Query to get Profile Option
 -- --------------------------------------------------------------------------------------

SELECT   user_profile_option_name, SUM (level_value)

    FROM fnd_profile_option_values fp, fnd_profile_options_vl fpv

   WHERE fp.profile_option_id = fpv.profile_option_id AND level_id = 10004

GROUP BY fpv.user_profile_option_name

  HAVING SUM (level_value) > 2;

 -- -----------------------------------------------------------------------------------
 Query to get the profile option values
 -- -----------------------------------------------------------------------------------

 SELECT   b.user_profile_option_name "Long Name",

         a.profile_option_name "Short Name",

         NVL (g.responsibility_name, c.level_value) "Level Value",

         c.profile_option_value "Profile Value", b.sql_validation

    FROM apps.fnd_profile_options a,

         apps.fnd_profile_options_vl b,

         apps.fnd_profile_option_values c,

         apps.fnd_user d,

         apps.fnd_user e,

         apps.fnd_responsibility_vl g,

         apps.fnd_application h

   WHERE 1 = 1

     AND a.profile_option_name = b.profile_option_name

     AND a.profile_option_id = c.profile_option_id

     AND a.application_id = c.application_id

     AND c.last_updated_by = d.user_id(+)

     AND c.level_value = e.user_id(+)

     AND c.level_value = g.responsibility_id(+)

     AND c.level_value = h.application_id(+)

     --

     AND c.level_id = 10003

     AND g.responsibility_name = 'XX_RESPONSIBILITY_NAME'

ORDER BY b.user_profile_option_name, c.level_id;
-- -----------------------------------------------------------------------------------
Query to find form function details
-- -----------------------------------------------------------------------------------
SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name,

                ffl.user_function_name

           FROM fnd_compiled_menu_functions cmf,

                fnd_form_functions ff,

                fnd_form_functions_tl ffl,

                fnd_responsibility r,

                fnd_responsibility_tl rtl,

                fnd_user_resp_groups urg,

                fnd_user u

          WHERE cmf.function_id = ff.function_id

            AND r.menu_id = cmf.menu_id

            AND urg.responsibility_id = r.responsibility_id

            AND rtl.responsibility_id = r.responsibility_id

            AND cmf.grant_flag = 'Y'

            AND r.application_id = urg.responsibility_application_id

            AND u.user_id = urg.user_id

            AND UPPER (ffl.user_function_name) = UPPER ('Returns')

            AND ff.function_id = ffl.function_id

       ORDER BY u.user_name;

       
-------------------------------------------------------------------------------------
Query to get all fuctions attached to responsibilities
-- -----------------------------------------------------------------------------------
SELECT DISTINCT faa.application_name application, rtl.responsibility_name,

                ffl.user_function_name, ff.function_name, ffl.description,

                ff.TYPE

           FROM fnd_compiled_menu_functions cmf,

                fnd_form_functions ff,

                fnd_form_functions_tl ffl,

                fnd_responsibility r,

                fnd_responsibility_vl rtl,

                apps.fnd_application_all_view faa

          WHERE cmf.function_id = ff.function_id

            AND r.menu_id = cmf.menu_id

            AND rtl.responsibility_id = r.responsibility_id

            AND cmf.grant_flag = 'Y'

            AND ff.function_id = ffl.function_id

            AND faa.application_id(+) = r.application_id

            AND r.end_date IS NULL

            AND rtl.end_date IS NULL

       ORDER BY rtl.responsibility_name;

-------------------------------------------------------------------------------------
Following are the FND_PROFILE values that can be used in the PL/SQL code
-- -----------------------------------------------------------------------------------
fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;  
 -- -----------------------------------------------------------------------------------
  AOL Definitions :--
 -- -----------------------------------------------------------------------------------
--CONCURRENT_PROGRAM_NAME

SELECT   user_concurrent_program_name

    FROM fnd_concurrent_programs_tl

   WHERE LANGUAGE = 'US' AND application_id = 50001

ORDER BY user_concurrent_program_name;

--FORM

SELECT   user_form_name, form_name

    FROM fnd_form_vl

   WHERE application_id = 50001

ORDER BY 1, 2;

--FORM FUNCTIONS

SELECT user_function_name

  FROM fnd_form_functions_vl

 WHERE form_id IN (SELECT form_id

                     FROM fnd_form_vl

                    WHERE application_id = 50001)

ORDER BY 1;

--LOOKUP

SELECT DISTINCT lookup_type

           FROM fnd_lookup_values_vl

          WHERE lookup_type LIKE 'XX%'

       ORDER BY lookup_type;

SELECT   lookup_type, lookup_code

    FROM fnd_lookup_values_vl

   WHERE lookup_type LIKE 'XX%' AND enabled_flag = 'Y'

ORDER BY lookup_type, lookup_code;

--REQUEST GROUPS

SELECT   fr.request_group_name, fp.user_concurrent_program_name

    FROM fnd_request_groups fr,

         fnd_request_group_units fru,

         fnd_concurrent_programs_tl fp

   WHERE fr.request_group_id = fru.request_group_id

     AND fru.request_unit_id = fp.concurrent_program_id

     AND fp.LANGUAGE = 'US'

     AND fru.request_unit_type = 'P'

     AND fr.application_id = 50001

UNION

SELECT   fr.request_group_name, frs.user_request_set_name

    FROM fnd_request_groups fr,

         fnd_request_group_units fru,

         fnd_request_sets_tl frs

   WHERE fr.request_group_id = fru.request_group_id

     AND fru.request_unit_id = frs.request_set_id

     AND frs.LANGUAGE = 'US'

     AND fru.request_unit_type = 'S'

     AND fr.application_id = 50001

ORDER BY 1, 2;

--MENUS--

SELECT fm.menu_name, prompt,

       (CASE

           WHEN sub_menu_id IS NOT NULL

              THEN (SELECT menu_name

                      FROM fnd_menus

                     WHERE menu_id = fmv.sub_menu_id)

           WHEN fmv.function_id IS NOT NULL

              THEN (SELECT user_function_name

                      FROM fnd_form_functions_tl f

                     WHERE f.function_id = fmv.function_id

                       AND f.LANGUAGE = 'US')

        END

       ) menu_function

  FROM fnd_menu_entries_vl fmv, fnd_menus fm

 WHERE fm.menu_id = fmv.menu_id AND fm.menu_name LIKE 'XX_MENU_NAME%';

-- --------------------------------------------------------------------------------------------
Form Personalization query
-- --------------------------------------------------------------------------------------------
SELECT DISTINCT a.ID, a.form_name, a.enabled, c.user_form_name,

                d.application_name, a.description, ca.action_type, ca.enabled,

                ca.object_type, ca.MESSAGE_TYPE, ca.MESSAGE_TEXT

           FROM fnd_form_custom_rules a,

                fnd_form b,

                fnd_form_tl c,

                fnd_application_tl d,

                fnd_form_custom_actions ca

          WHERE a.form_name = b.form_name

            AND b.form_id = c.form_id

            AND b.application_id = d.application_id

            --And D.Application_Id = 230 --For Order Management

            AND c.user_form_name LIKE

                         'Inventory%'

                                     --All the Forms that Start with Inventory

            AND a.enabled = 'Y'

            AND a.ID = ca.rule_id;

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

SELECT ffv.form_id, ffv.form_name, ffv.user_form_name, ffv.description,

       ffcr.description, ffcr.SEQUENCE

  FROM fnd_form_vl ffv, fnd_form_custom_rules ffcr

 WHERE ffv.form_name = ffcr.form_name AND ffcr.description LIKE '%XX%';

-- -----------------------------------------------------------------------------------------
Template and its bursting path
-- -----------------------------------------------------------------------------------------
SELECT    lt.application_short_name

       || '.'

       || lt.template_code

       || '.'

       || lt.default_language

       || '.'

       || lt.default_territory AS burst_template_xdo_location

  FROM apps.xdo_templates_b lt

 WHERE lt.template_code = 'XXAR0387';

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

Query to find XML Templates details

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

SELECT xtt.template_name "Template Name", xtb.template_code "Template Code",

       fat.application_name "Application Name",

       xddt.data_source_name "Data Definition",

       xtb.template_type_code "Template Type",

       xtb.default_language "Language", xtb.default_territory "Territory",

       xl.file_name "File Name", xl.file_data "File"

  FROM xdo_templates_tl xtt,

       xdo_templates_b xtb,

       fnd_application_tl fat,

       xdo_ds_definitions_tl xddt,

       xdo_lobs xl

 WHERE (   xtt.template_name LIKE '%'

        OR xtt.template_name LIKE 'XX_TEMPLATE_NAME%'

       );

-- -------------------------------------------------------------------------------------------
Query to Find All the fields in a particular DFF
-- -------------------------------------------------------------------------------------------

SELECT   ffv.descriptive_flexfield_name dffname,

         ffv.application_table_name tablename, ffv.title title,

         ap.application_name application, att.column_seq_num segmentnumber,

         att.form_left_prompt segmentname, att.application_column_name,

         fvs.flex_value_set_name valueset, att.required_flag

    FROM apps.fnd_descriptive_flexs_vl ffv,

         apps.fnd_descr_flex_contexts_vl ffc,

         apps.fnd_descr_flex_col_usage_vl att,

         apps.fnd_flex_value_sets fvs,

         apps.fnd_application_vl ap

   WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

     AND ap.application_id = ffv.application_id

     AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name

     AND ffv.application_id = ffc.application_id

     AND ffc.descriptive_flex_context_code = att.descriptive_flex_context_code

     AND fvs.flex_value_set_id = att.flex_value_set_id

     AND ffv.title IN ('Invoice Line Information')

ORDER BY ffv.title;

-- -------------------------------------------------------------------------------------------
Query to Find Only Required fields in a particular DFF
-- -------------------------------------------------------------------------------------------

SELECT *

  FROM (SELECT   ffv.descriptive_flexfield_name dffname,

                 ffv.application_table_name tablename, ffv.title title,

                 ap.application_name application,

                 att.column_seq_num segmentnumber,

                 att.form_left_prompt segmentname,

                 att.application_column_name,

                 fvs.flex_value_set_name valueset, att.required_flag

            FROM apps.fnd_descriptive_flexs_vl ffv,

                 apps.fnd_descr_flex_contexts_vl ffc,

                 apps.fnd_descr_flex_col_usage_vl att,

                 apps.fnd_flex_value_sets fvs,

                 apps.fnd_application_vl ap

           WHERE ffv.descriptive_flexfield_name =

                                                att.descriptive_flexfield_name

             AND ap.application_id = ffv.application_id

             AND ffv.descriptive_flexfield_name =

                                                ffc.descriptive_flexfield_name

             AND ffv.application_id = ffc.application_id

             AND ffc.descriptive_flex_context_code =

                                             att.descriptive_flex_context_code

             AND fvs.flex_value_set_id = att.flex_value_set_id

             AND ffv.title IN ('Invoice Line Information')

        ORDER BY ffv.title)

 WHERE required_flag = 'Y';

----------------------------------------------------------------------------------------
Request id and its responsibility
-- --------------------------------------------------------------------------------------

SELECT c.request_id, r.responsibility_name

  FROM fnd_responsibility_vl r, fnd_concurrent_requests c

 WHERE c.request_id = 97131851

   AND c.responsibility_application_id = r.application_id

   AND c.responsibility_id = r.responsibility_id;

-- --------------------------------------------------------------------------------------
find concurrent program usage since they have been installed -fnd_conc_prog_onsite_info
-- --------------------------------------------------------------------------------------

SELECT DISTINCT fa.application_short_name, fcpt.user_concurrent_program_name,

                fcp.concurrent_program_name concurrent_program_short_name,

                fcp.concurrent_program_id, fe.executable_name,

                fe.execution_file_name,

                (CASE

                    WHEN regexp_count (UPPER (fe.execution_file_name), '\.',

                                       1) = 2

                       THEN (SUBSTR

                                (SUBSTR

                                       (UPPER (fe.execution_file_name),

                                          INSTR

                                               (UPPER (fe.execution_file_name),

                                                '.',

                                                1,

                                                1

                                               )

                                        + 1

                                       ),

                                 1,

                                   INSTR

                                      (SUBSTR

                                          (UPPER (fe.execution_file_name),

                                             INSTR

                                                (UPPER (fe.execution_file_name),

                                                 '.',

                                                 1,

                                                 1

                                                )

                                           + 1

                                          ),

                                       '.'

                                      )

                                 - 1

                                )

                            )

                    WHEN regexp_count (UPPER (fe.execution_file_name), '\.',

                                       1) = 1

                       THEN (SUBSTR (UPPER (fe.execution_file_name),

                                     1,

                                       INSTR (UPPER (fe.execution_file_name),

                                              '.'

                                             )

                                     - 1

                                    )

                            )

                    ELSE UPPER (fe.execution_file_name)

                 END

                ) object_name,

                fcp.executable_id, fe.execution_method_code, flv.meaning,

                fcpoi.reset_date, fcpoi.last_run_date,

                fcpoi.successful_completion, fcpoi.warning_completion,

                fcpoi.error_completion,

                (  NVL (fcpoi.successful_completion, 0)

                 + NVL (fcpoi.warning_completion, 0)

                 + NVL (fcpoi.error_completion, 0)

                ) total_executions

           FROM fnd_conc_prog_onsite_info fcpoi,

                fnd_concurrent_programs_tl fcpt,

                fnd_concurrent_programs fcp,

                fnd_executables fe,

                fnd_lookup_values flv,

                fnd_application fa

          WHERE fcpoi.concurrent_program_id = fcpt.concurrent_program_id

            AND fcpoi.concurrent_program_id = fcp.concurrent_program_id

            AND fcpt.concurrent_program_id = fcp.concurrent_program_id

            AND fcp.executable_id = fe.executable_id

            AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'

            AND flv.lookup_code = fe.execution_method_code

            AND flv.LANGUAGE = 'US'

            AND fcp.application_id = fa.application_id

            AND (   fa.application_short_name LIKE

                                                  'XX_APPLICATION_SHORT_NAME%'

                 OR fe.executable_name LIKE 'XX_EXECUTABLE_NAME%'

                 OR fe.executable_name LIKE 'XX_EXECUTABLE_NAME%'

                 OR fa.application_short_name LIKE

                                                  'XX_APPLICATION_SHORT_NAME%'

                );