----List of all responsibilities application wise
SELECT (SELECT application_short_name FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;

--- List menu attached with responsibilities
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 = ‘20538&rsquo*/
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';

---List responsibility attached to a for each and every user
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR
(r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) 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 SUBSTR (user_name, 1, 30), SUBSTR (a.application_name,
1, 50), SUBSTR (r.responsibility_name, 1, 60);

--List responsibility and attached request group
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 to view value of a profile option at user level
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name FROM fnd_user WHERE user_id =
v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE
user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o,
fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id=1058 and  o.profile_option_id =
v.profile_option_id AND o.application_id = v.application_id AND start_date_active <=
SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND
o.profile_option_name = t.profile_option_name AND level_id = 10004 AND t.LANGUAGE IN
(SELECT language_code FROM apps.fnd_languages WHERE installed_flag = 'B'
UNION SELECT nls_language FROM fnd_languages WHERE installed_flag= 'B') ORDER BY user_profile_option_name;


--Query used for audit point of view i.e. when a profile is changed and by whom user
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name FROM fnd_user WHERE user_id =
v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE
user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o,
fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id =
v.profile_option_id AND o.application_id = v.application_id AND start_date_active <=
SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND
o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN
(SELECT language_code FROM fnd_languages WHERE installed_flag = 'B'
UNION SELECT nls_language FROM fnd_languages WHERE installed_flag= 'B') ORDER BY user_profile_option_name;

/*Forms Personalization Listing [For More detail on form Personalization Use Following Tables (Rule_id) 
is reference key for these tables applsys.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,
(SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by)
"Created By " FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID
= ffft.function_id ORDER BY 1;

--Query used 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;

--List all Functions
SELECT function_id, user_function_name, creation_date, description FROM
applsys.fnd_form_functions_tl order by user_function_name;

--List all Request 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;

--View all types of request Applicationwise
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;

---View all requests tagged to a Request group
select fcpv.USER_CONCURRENT_PROGRAM_NAME,'Single'
  from apps.fnd_request_groups frg, fnd_request_group_units frgu,fnd_concurrent_programs_vl fcpv
 where frg.request_group_name = 'TCS Helpdesk Superuser'
   and frg.requesT_group_id = frgu.requesT_group_id and frgu.request_unit_type='P' and fcpv.concurrent_program_id=frgu.request_unit_id
   union
select frs.user_REQUEST_SET_NAME,'Set'
  from apps.fnd_request_groups frg, fnd_request_group_units frgu,fnd_request_sets_vl frs
 where frg.request_group_name = 'TCS Helpdesk Superuser'
   and frg.requesT_group_id = frgu.requesT_group_id and frgu.request_unit_type='S' and frs.REQUEST_SET_ID=frgu.request_unit_id
   
   order by 2 desc;   





--Application wise request counting
SELECT fa.application_short_name, 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,
COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv,
fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY
fa.application_short_name, fcpv.execution_method_code ORDER BY 1;

--This query will shows report processing time of all requests submitted
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;

--List of Patches Applied;
SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches
ORDER BY creation_date DESC;


---Queries related to requests
select * from fnd_request_set_programs;
select * from fnd_request_groups a where a.request_group_id ;

select * from fnd_request_sets where request_set_id= 497 and request_set_name='FNDRSSUB1296'; 

select * from fnd_request_set_stages_tl where request_set_id=497;

select * from fnd_request_set_stages_tl where upper(user_stage_name) like ('PRC: Generate Draft Revenue for a Single Project');

select * from fnd_request_set_stages where request_set_id=1296;
select * from fnd_request_sets_vl where request_set_name='FNDRSSUB1296';
select * from FND_REQUEST_SETS_TL a where a.request_set_id=1296;
select * from FND_REQUEST_SETS_TL a where a.user_request_set_name like '%TCS%PA%Generate%Revenue%and%';