Wednesday, October 16, 2019

Find Responsibilities that can run a concurrent program

Below query tells which R12 responsibilities can run a particular concurrent program, such as "Purge Signon Audit data":

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 = 'Purge Signon Audit data'
ORDER BY 1,    2,    3,    4;