Useful Queries for Oracle Applications 11i

1) Query to get list of responsibilities.
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;

2) Query to get Menus Associated with responsibility

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’
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';

3) Query to get submenus and Function attached to this Main menu.

SELECT c.prompt, c.description
FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c
WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'AS185112_TEST';

4) Query to get assigned responsibility to a 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);

5) Query to get responsibility and attached request groups

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;

6) Query to get modified profile options.

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;

7) Query to get the details of Form Personalization
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;

8) Query to get Patch Level.
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;

9) Query to get all Functions
SELECT function_id, user_function_name, creation_date, description
FROM applsys.fnd_form_functions_tl
y order by user_function_name;

10 Query to get 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;

11) Query to get all request with application
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

12) Query to Count Module Wise Report
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;

13) Query to calculate request time
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.descriptio
n']',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;

14) Query to check responsibility assigned to a specific 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
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

15) Query to check Current Applied Patch
SELECT patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC;

16) Query for Warning users for Apache bounce.
SELECT ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address) AS email_address
FROM per_people_x ppx, fnd_user fu, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppx.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST')
AND fu.user_name != 'PASSIA' --exclude anil
GROUP BY ppx.full_name
,fu.user_name
,nvl(ppx.email_address
,fu.email_address)
ORDER BY 2;

17) Query to Get instance ID of server.
SELECT NVL (LPAD (profile_option_value, 4, '0'), '0000')
FROM fnd_profile_option_values
WHERE application_id = 1
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'NCR_ERP_INSTANCE_IDENTIFIER');

18) Query to get Concurrent program name and its parameter.

SELECT fcpl.user_concurrent_program_name ,
fcp.concurrent_program_name ,
par.end_user_column_name ,
par.form_left_prompt prompt ,
par.enabled_flag , par.required_flag ,
par.display_flag
FROM
fnd_concurrent_programs fcp ,
fnd_concurrent_programs_tl fcpl ,
fnd_descr_flex_col_usage_vl par
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = &conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' fcp.concurrent_program_name

19) Query to get all the active sessions.
select s.sid
--, s.serial#
, s.inst_id
, to_char(logon_time ,'mm/dd hh24:mi:ss') lt,
s.username, s.osuser
-- , p.spid
, s.program
, s.machine
, s.module
, s.action
, s.status
, s.*
from gv$session s
--, gv$process p
where
s.username is not null
and s.status = 'ACTIVE'
--order by 3 '
--and (s.module like '%MSC%' or s.module like 'MRP%' or s.module like 'MSR%')
--and s.program like 'BMCC%'
-- and s.module like '%NCR%'
--and s.module like 'INCOIN%'
and s.module like 'ITEMLOAD%'
order by 3

20) Query to get Sql statement running for any locked Program.

select /*+ ORDERED */
sql_text
-- , s.*
from gv$session s, gv$sqltext t
where t.address = s.sql_address
and s.inst_id = t.inst_id
and s.sid = &sid
and s.inst_id = &inst
order by s.inst_id, t.piece
/