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.