Oracle Application – Top useful SQL Queries

Query 1: Select responsibility name along with application name

SELECT application_short_name ,

frt.responsibility_id, frt.responsibility_name

FROM apps.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 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

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;

Query to display module wise reports

SELECT   fa.application_short_name applshortname,
              fcpv.user_concurrent_program_name concprogramname,
              fav.application_name applicationname,
              fcpv.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,
              fnd_application_vl fav
WHERE   fcpv.application_id = fa.application_id
and        fa.application_id=fav.application_id
and        fa.application_short_name='PER'
ORDER BY 1

FND tables for Reports

select * from fnd_concurrent_programs where CONCURRENT_PROGRAM_NAME='POXRQOBO'

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where object_name like'FND_CONCURRENT_P%' --AND OBJECT_TYPE='TABLE%'

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where  OBJECT_TYPE='TABLE' AND object_name like'FND_CONCURRENT_P%'--OBJECT_TYPE='TABLE%'

SELECT * FROM FND_EXECUTABLES

SELECT EXECUTABLE_NAME,EXECUTION_FILE_NAME FROM FND_EXECUTABLES

SELECT * FROM FND_CONCURRENT_REQUESTS

SELECT * FROM FND_REQUEST_GROUPS

SELECT REQUEST_GROUP_NAME,DESCRIPTION FROM FND_REQUEST_GROUPS

SELECT * FROM FND_RESPONSIBILITY   NATURAL JOIN FND_REQUEST_GROUPS

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where  OBJECT_TYPE='TABLE' AND object_name like'FND_RES%'

SELECT B.FND_RESPONSIBILITY,B.REQUEST_GROUP_NAME FROM FND_RESPONSIBILITY  A NATURAL JOIN FND_REQUEST_GROUPS B

Concurrent Program Details Query

select request_id,        
fcpt.user_concurrent_program_name,      
  completion_text,        
actual_start_date,        
actual_completion_date,
  to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
  from fnd_concurrent_requests fcr,  
  fnd_concurrent_programs fcp,        
  fnd_concurrent_programs_tl fcpt
  where fcr.concurrent_program_id = fcp.concurrent_program_id  
  and fcp.concurrent_program_id = fcpt.concurrent_program_id  
  and fcr.actual_start_date > sysdate - 1  
  order by actual_completion_date - actual_start_date desc;