Showing posts with label Apps Queries. Show all posts
Showing posts with label Apps Queries. Show all posts

Sunday, March 15, 2015

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_actionsapplsys.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

Tuesday, March 10, 2015

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;