Query to get responsibility name based on function name - oracle EBS

 SELECT distinct fr.responsibility_name,
       fm.user_menu_name,
       fme.prompt prompt_name,
       fff.user_function_name
FROM   apps.fnd_menu_entries_vl fme,
       apps.fnd_menus_vl fm,
       apps.fnd_form_functions_vl fff,
       apps.fnd_form_vl ff,
       apps.fnd_responsibility_vl fr
WHERE  1 = 1
AND    fff.USER_FUNCTION_NAME = 'XX_CUST_CONTACT_FUN' 
AND    fme.function_id = fff.function_id
AND    fm.menu_id = fme.menu_id
AND    fr.menu_id = fm.menu_id;

Query to get ORG_ID based on responsibility Name

 SELECT   frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.name
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE upper(frv.RESPONSIBILITY_NAME) like 'XX_RESP_NAME%'
     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
     AND fpo.profile_option_id = fpov.profile_option_id
     AND fpo.user_profile_option_name = 'MO: Operating Unit'
     AND frv.responsibility_id = fpov.level_value
ORDER BY frv.responsibility_name;

Query to get Reponsibilities attached to user

 SELECT fu. user_name, 
frv. responsibility_name, 
frv. responsibility_key, 
TO_CHAR (furgd. start_date, 'DD-MON-YYYY')"START_DATE", 
TO_CHAR (furgd. end_date, 'DD-MON-YYYY') "END_DATE" 
FROM apps.fnd_user fu,
apps.fnd_user_resp_groups_direct furgd,
apps. fnd_responsibility_vl frv
WHERE fu. user_id = furgd. user_id
AND furgd. responsibility_id = frv. responsibility_id 
--AND furgd. end_date IS NULL 
AND fu. user_name = 'TEST0101'
AND furgd. start_date <= sysdate AND NVL (furgd. end_date, sysdate + 1) > sysdate AND fu. start_date <= sysdate
AND NVL (fu. end_date, sysdate + 1) > sysdate
AND frv.start_date <= sysdate AND NVL (frv. end_date, sysdate + 1) > sysdate;
x