Query to get the Request Groups & Request Sets in Oracle Apps

 SELECT *

  FROM (SELECT frg.request_group_name

, (CASE

WHEN (NVL (frg.created_by, 0) > 99) THEN 'Y'

ELSE 'N'

END) custom_request_group

, (SELECT user_name

  FROM fnd_user

WHERE user_id = frg.created_by) req_group_owner

, fcpt.user_concurrent_program_name

, fcpt.concurrent_program_name

, (SELECT application_name

  FROM fnd_application_vl

WHERE application_id = fcpt.application_id) program_appl_name

, (SELECT application_short_name

  FROM fnd_application_vl

WHERE application_id = fcpt.application_id) program_appl_short_name

, (SELECT user_name

  FROM fnd_user

WHERE user_id = fcpt.created_by) program_created_by

, (CASE

WHEN (NVL (fcpt.created_by, 0) > 99) THEN 'Y'

ELSE 'N'

END)

custom_program_unit

FROM fnd_request_groups frg

,fnd_request_group_units frgu

,fnd_concurrent_programs_vl fcpt

WHERE frgu.request_group_id = frg.request_group_id

  AND fcpt.concurrent_program_id = frgu.request_unit_id

  AND NVL (fcpt.enabled_flag, 'Y') = 'Y')

 WHERE custom_program_unit = 'Y'

UNION

SELECT *

  FROM (SELECT frg.request_group_name

, (CASE

WHEN (NVL (frg.created_by, 0) > 99) THEN 'Y'

ELSE 'N'

END) custom_request_group

, (SELECT user_name

  FROM fnd_user

WHERE user_id = frg.created_by) req_group_owner

, fcpt.user_request_set_name

, fcpt.request_set_name

, (SELECT application_name

  FROM fnd_application_vl

WHERE application_id = fcpt.application_id) req_set_appl_name

, (SELECT application_short_name

  FROM fnd_application_vl

WHERE application_id = fcpt.application_id) req_set_appl_short_name

, (SELECT user_name

  FROM fnd_user

WHERE user_id = fcpt.created_by)

req_set_created_by

, (CASE

WHEN (NVL (fcpt.created_by, 0) > 99) THEN 'Y'

ELSE 'N'

END)

custom_request_set

FROM fnd_request_groups frg

,fnd_request_group_units frgu

,fnd_request_sets_vl fcpt

WHERE frgu.request_group_id = frg.request_group_id

  AND fcpt.request_set_id = frgu.request_unit_id

  AND NVL (fcpt.end_date_active, SYSDATE) >= SYSDATE)

 WHERE custom_request_set = 'Y'

ORDER BY 1

,2

,3

,4;