Queries

1. Getting Responsibility from Function Name
--> for one level
  select frt.responsibility_id, frt.responsibility_name from fnd_responsibility_tl frt,
                fnd_responsibility fr,
                fnd_form_functions_tl fff,
                fnd_menu_entries fme
         where frt.language = 'US'
         and frt.responsibility_id  = fr.responsibility_id
         and  fff.function_id = fme.function_id
         and fme.menu_id = fr.menu_id
         and fme.sub_menu_id is NULL
         and fff.user_function_name like '%Organization%Access%';

2. From Sub Menus also

SELECT frt.*
  FROM (SELECT     LEVEL, menu_id, sub_menu_id
              FROM fnd_menu_entries
             WHERE sub_menu_id IS NOT NULL AND function_id IS NULL
        START WITH sub_menu_id IN (
                      SELECT menu_id
                        FROM fnd_menu_entries fme, fnd_form_functions_tl fff
                       WHERE fme.function_id = fff.function_id
                         AND fff.user_function_name LIKE
                                                       '%Organization%Access%')
        CONNECT BY PRIOR sub_menu_id = menu_id) a,
       fnd_responsibility fr,
       fnd_responsibility_tl frt
 WHERE a.menu_id = fr.menu_id
   AND frt.responsibility_id = fr.responsibility_id
   AND frt.LANGUAGE = 'US';


http://oracle.anilpassi.com/some-scripts-contributed-by-ahmad-bilal-16.html

-- Query to inventory org access responsibility wise
SELECT   row_id, organization_code, application_name, responsibility_name,
         comments, organization_name, disable_date, organization_id,
         resp_application_id, responsibility_id, last_update_date,
         last_updated_by, creation_date, created_by, last_update_login,
         request_id, program_application_id, program_id, program_update_date
    FROM org_access_v
ORDER BY organization_code
;

--> query to concurrent prg info
SELECT fcpl.user_concurrent_program_name, fcpl.concurrent_program_id,
       fcp.concurrent_program_name, fe.executable_id, fe.execution_file_name,
       fe.executable_name ,  fa.application_short_name
  FROM fnd_concurrent_programs_tl fcpl,
       fnd_concurrent_programs fcp,
       fnd_executables fe,
       fnd_application fa
 WHERE user_concurrent_program_name LIKE '%XXAT%Inv%Adj%'
   AND LANGUAGE = 'US'
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fe.executable_id = fcp.executable_id
   and fa.application_id = fe.application_id;

-->Query to get the conc prg default parameteres


 SELECT       MAX(DECODE(application_column_name, 'ATTRIBUTE1',default_value, NULL))  argument1,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE2',default_value, NULL))  argument2,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE3',default_value, NULL))  argument3,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE4',default_value, NULL))  argument4,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE5',default_value, NULL))  argument5,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE6',default_value, NULL))  argument6,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE7',default_value, NULL))  argument7,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE8',default_value, NULL))  argument8,
                          MAX(DECODE(application_column_name, 'ATTRIBUTE9',default_value, NULL))  argument9
             INTO         l_pr_argu_1, l_pr_argu_2, l_pr_argu_3, l_pr_argu_4,
                          l_pr_argu_5, l_pr_argu_6, l_pr_argu_7, l_pr_argu_8,
                          l_pr_argu_9
             FROM   FND_DESCR_FLEX_COL_USAGE_VL
             WHERE (APPLICATION_ID=20004)
             AND  (DESCRIPTIVE_FLEXFIELD_NAME='$SRS$.XXAT_PRICAT_OUTBOUND_CP') order by column_seq_num;

--  query to get fnd_conc_req_schedule

SELECT fcpl.user_concurrent_program_name, fcpl.concurrent_program_id,
       fcp.concurrent_program_name, fe.executable_id, fe.execution_file_name,
       fe.executable_name ,  fa.application_short_name
  FROM fnd_concurrent_programs_tl fcpl,
       fnd_concurrent_programs fcp,
       fnd_executables fe,
       fnd_application fa
 WHERE user_concurrent_program_name LIKE '%%'
   AND LANGUAGE = 'US'
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fe.executable_id = fcp.executable_id
   and fa.application_id = fe.application_id
   ;

---
Value set query (table type)

select v.*
  from FND_FLEX_VALUE_SETS s, FND_FLEX_VALIDATION_TABLES v
 where s.flex_value_set_name = 'YOUR_VS_NAME'
   and s.flex_value_set_id = v.flex_value_set_id

==================
Query to get Profile values at all levels
SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')
ORDER BY short_name;