List of Program and Request Set attached to a Responsibility

List of Program and Request Set attached to a Responsibility


Below query can be used to extract the list of concurrent program and Request setname attached to a given Responsibiltiy

SELECT    request_group_name,
               DECODE (request_unit_type, 'P', 'Program',
                               'S', 'Request Set', 'no')  request_unit_type,
               user_concurrent_program_name program_name
FROM      fnd_request_groups a,
               fnd_request_group_units b,
               fnd_concurrent_programs_vl c
WHERE    a.request_group_id = b.request_group_id
AND         b.request_unit_id = c.concurrent_program_id
AND         request_unit_type = 'P'
AND         a.request_group_id IN
                     (SELECT request_group_id
                      FROM fnd_responsibility
                      WHERE responsibility_id IN
                                 (SELECT responsibility_id
                                   FROM fnd_responsibility_tl
                                   WHERE responsiblity_name = '&Resp_name'
                                    AND LANGUAGE = 'US')
                       )
UNION
SELECT   request_group_name,
               DECODE (request_unit_type, 'P', 'Program', 'S', 'Request Set', 'no') request_unit_type,
                 user_request_set_name program_name
FROM      fnd_request_groups a,
                 fnd_request_group_units b,
                 fnd_request_sets_tl c
WHERE   a.request_group_id = b.request_group_id
AND        b.request_unit_id = c.request_set_id
AND        request_unit_type = 'S'
AND        a.request_group_id IN
                     (SELECT request_group_id
                       FROM fnd_responsibility
                       WHERE responsibility_id IN
                                   (SELECT responsibility_id
                                    FROM fnd_responsibility_tl
                                    WHERE responsiblity_name = '&Resp_name'
                                    AND LANGUAGE = 'US')
                      );