SQL Query to list all Oracle Reports assigned to a Responsibility

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

/**************************************************************************
 *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';

/************************************************************************** *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';