Useful Oracle Apps Queries

Below are a list of SQL Queries that will be useful during your day to day activities.

1. Concurrent Programs Assigned to a Request Group

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_name = ‘&request_group_name’;

2. Check if the concurrent program is assigned to a given responsibility

SELECT fcpv.concurrent_program_name SHORT_NAME
,fcpv.user_concurrent_program_name CONC_PROG_NAME
,frg.request_group_name REQ_GROUP_NAME
,frv.responsibility_name
FROM fnd_concurrent_programs_vl fcpv
,fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = frgu.request_unit_id
AND fcpv.enabled_flag = ‘Y’
AND frgu.request_group_id = frg.request_group_id
AND frg.request_group_id = frv.request_group_id
AND frv.responsibility_name = ‘&resp_name’
AND fcpv.user_concurrent_program_name = ‘&con_prg_name’;

3. List of Menus Excluded from a given Responsibility

SELECT frv.responsibility_name
,fmv.user_menu_name
FROM fnd_resp_functions frf
,fnd_menus_vl fmv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’M’
AND frf.action_id = fmv.menu_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

4. List of Functions Excluded from a given responsibility

SELECT frv.responsibility_name
,fffv.user_function_name
FROM fnd_resp_functions frf
,fnd_form_functions_vl fffv
,fnd_responsibility_vl frv
WHERE frf.rule_type =’F’
AND frf.action_id = fffv.function_id
AND frf.responsibility_id = frv.responsibility_id
AND frv.responsibility_name = ‘&resp_name’;

5. Concurrent Program Name, Responsibility Name and User Name for a concurrent request id

SELECT fcr.request_id
,frv.responsibility_name
,fcpv.concurrent_program_name PROG_SHORT_NAME
,fcpv.user_concurrent_program_name CON_PROG_NAME
,fu.user_name REQUESTED_BY
FROM fnd_concurrent_requests fcr
,fnd_concurrent_programs_vl fcpv
,fnd_user fu
,fnd_responsibility_vl frv
WHERE fcpv.concurrent_program_id = fcr.concurrent_program_id
AND fu.user_id = fcr.requested_by
AND frv.responsibility_id = fcr.responsibility_id
AND fcr.request_id = &req_id;