/**************************************************************************
*PURPOSE: Query to list all Reports assigned to a Responsibility *
*PARAMETERS: Responsibility Name *
*AUTHOR: Shailender Thallam *
**************************************************************************/
SELECT fcpl.user_concurrent_program_name "REPORT NAME",
fnrtl.responsibility_name, frg.request_group_name,
fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
and frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
AND fnrtl.responsibility_name LIKE '&Responsibility_Name'
-- Example Resp. Name : Inventory, Vision Operations (USA)
AND fnrtl.language = 'US'
AND fcpl.language = 'US';