Queries related to concurrent programs, request sets and programs attached to responsibilities

SQL Query to find Request Set for the given concurrent program

SELECT

   DISTINCT  user_request_set_name

FROM

    fnd_request_sets_tl rs,

    fnd_request_set_programs rsp,

    fnd_concurrent_programs_tl cp

WHERE

    rsp.concurrent_program_id = cp.concurrent_program_id

    AND   rs.request_set_id = rsp.request_set_id

    AND   upper(user_concurrent_program_name) = upper('Program name') 

Query to find the list of responsibilities to which the concurrent program is attached 

SELECT frt.responsibility_name, frg.request_group_name,frgu.request_unit_type,frgu.request_unit_id,

    fcpt.user_concurrent_program_name

    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,

    fnd_request_groups frg, fnd_request_group_units frgu,

    fnd_concurrent_programs_tl fcpt

    WHERE frt.responsibility_id = fr.responsibility_id

    AND frg.request_group_id = fr.request_group_id

    AND frgu.request_group_id = frg.request_group_id

    AND fcpt.concurrent_program_id = frgu.request_unit_id

    AND frt.LANGUAGE = USERENV('LANG')

    AND fcpt.LANGUAGE = USERENV('LANG')

    AND fcpt.user_concurrent_program_name = :conc_prg_name

    ORDER BY 1,2,3,4

Query to find Request set attached to responsibility in oracle apps

SELECT DISTINCT frt.responsibility_name,

   frg.request_group_name,

   frsv.user_request_set_name,

   frsv.request_set_name

         FROM fnd_responsibility fr,

              fnd_responsibility_tl frt,

              fnd_request_groups frg,

              fnd_request_group_units frgu,

              fnd_request_sets_vl frsv

        WHERE fr.request_group_id = frg.request_group_id

          AND fr.responsibility_id = frt.responsibility_id

          AND frg.request_group_id = frgu.request_group_id

          AND frgu.request_unit_id = frsv.request_set_id

          AND frsv.request_set_name=:P_REQUEST_SET_NAME