Query to get list of Users and Responsibilities got access to a Concurrent Program through Request Group in Oracle Applications

Query to get list of users and responsibilities that got access to a Concurrent Program through Request Group

SELECT   fcpt.user_concurrent_program_name, 
         frt.responsibility_name,
         SUBSTR (fu.description, 1, 49) user_name
    FROM apps.fnd_request_group_units frgu,
         apps.fnd_concurrent_programs_tl fcpt,
         apps.fnd_request_groups frg,
         apps.fnd_responsibility fr,
         apps.fnd_responsibility_tl frt,
         apps.fnd_user_resp_groups_all furg,
         apps.fnd_user fu
   WHERE fcpt.user_concurrent_program_name LIKE :p_user_concurrent_program_name
     AND frgu.unit_application_id = fcpt.application_id
     AND frgu.request_unit_id = fcpt.concurrent_program_id
     AND frg.application_id = frgu.application_id
     AND frg.request_group_id = frgu.request_group_id
     AND fr.request_group_id = frg.request_group_id
     AND fr.application_id = frg.application_id
     AND frt.responsibility_id = fr.responsibility_id
     AND frt.application_id = fr.application_id
     AND furg.responsibility_id = fr.responsibility_id
     AND furg.user_id = fu.user_id
     AND NVL (furg.end_date, SYSDATE) > SYSDATE - 1
     AND NVL (fu.end_date, SYSDATE) > SYSDATE - 1
     AND (SYSDATE BETWEEN fr.start_date AND fr.end_date OR fr.end_date IS NULL)
ORDER BY fcpt.user_concurrent_program_name, frgu.request_group_id


***Please leave a comment***

Query to get the Concurrent Requests of a Concurrent Program in Oracle Applications

Below the Query to get the Concurrent Requests of given Concurrent Program

    SELECT   fcpt.user_concurrent_program_name,

           fcr.request_id,

           fcr.parent_request_id,

           fu.description,

           frt.responsibility_name,

           phase_lookup.meaning,

           status_lookup.meaning,

           fcr.requested_start_date,

           fcr.actual_start_date,

           fcr.actual_completion_date,

           fcr.argument_text,

           fcr.logfile_name,

           fcr.outfile_name

    FROM   apps.fnd_concurrent_programs_tl fcpt,

           apps.fnd_concurrent_requests fcr,

           apps.fnd_lookups status_lookup,

           apps.fnd_lookups phase_lookup,

           apps.fnd_user fu,

           apps.fnd_responsibility_tl frt

   WHERE       fcpt.concurrent_program_id = fcr.concurrent_program_id

           AND fcpt.application_id = fcr.program_application_id

           AND fcr.status_code = status_lookup.lookup_code

           AND status_lookup.lookup_type = 'CP_STATUS_CODE'

           AND fcr.phase_code = phase_lookup.lookup_code

           AND phase_lookup.lookup_type = 'CP_PHASE_CODE'

           AND fcr.requested_by = fu.user_id

           AND fcr.responsibility_application_id = frt.application_id

           AND fcr.responsibility_id = frt.responsibility_id

           AND frt.language = USERENV('LANG')  

           AND fcpt.user_concurrent_program_name =

                 :P_User_Concurrent_Program_Name
           AND fcpt.language = USERENV('LANG')  

ORDER BY   actual_completion_date DESC

***Please leave a comment***