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

Hi! This post shows you the query to fetch all the users who have a specific responsibility attached 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      ='Order Management Superuser';

Here, 'Order Management Superuser' is the responsibility and the query would fetch the users who have this responsibility attached to them.

Query to find the location of Application top in Oracle

Hi! This post shows the query to find the location of the application top in Oracle.

SELECT distinct fec.variable_name, fec.value

   FROM fnd_env_context fec

 WHERE fec.variable_name like '%ONT_TOP%' 

ORDER BY fec.variable_name;

Here, ONT_TOP is the application top. Similarly, other paths can be found by providing the application top name including the custom top.

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!!

Query to find RDF report path in Oracle apps

Hi, This post shows you how to find the RDF report path in server in Oracle apps R12. The query below retrieves the report path along with application name which takes execution file name is the parameter.

SELECT fa.application_name,

               '$'||basepath||'/'||'reports/US' Reports_Path,

   FROM apps.fnd_executables_vl fe, 

                apps.fnd_application_vl fa 

WHERE fe.execution_method_code =' P' 

      AND fe.application_id = fa.application_id

      AND fe.execution_file_name = 'XXSAMPLEFILE';                  --Your file name without extension

Happy learning!!