Useful OA Queries

Here some of quite useful regular sql queries in oracle applications.

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 types of request Application wise/* 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;