Query to get users who have a specific responsibility is attached to them in Oracle apps

Hi! This post shows you the query to fetch the users who have a particular responsibility to them.

SELECT fu.user_name,

  frt.responsibility_name,

  TO_CHAR(furg.start_date,'DD-MON-YYYY') start_date,

  furg.end_date,

  fu.DESCRIPTION 

FROM fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

WHERE fu.user_id                 = furg.user_id

AND frt.responsibility_id        = furg.responsibility_id

AND frt.application_id           = furg.responsibility_application_id

AND NVL(furg.end_date,sysdate+1) > sysdate

AND NVL(frt.end_date,sysdate +1) > sysdate

AND NVL(fu.end_date,sysdate  +1) > sysdate

AND frt.responsibility_name      = :p_resp_name;

Happy learning. Cheers!!

Query to find concurrent program responsibility in Oracle apps

Hi, This post will show the query to retrieve responsibility details for a concurrent program in Oracle apps.

Query to find concurrent program responsibility:

SELECT frt.responsibility_name,

               frg.request_group_name,

               frg.description

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

 AND   fcpt.user_concurrent_program_name LIKE <Concurrent program name>;

Cheers!!