Map Responsibilities to Applications/Modules



Query :


ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'; 
SELECT 
    frt.responsibility_name,
    fat.application_name AS module_name,
    m.menu_name,
    fme.prompt AS menu_entry_prompt,
    fft.user_function_name AS function_name
FROM 
    fnd_responsibility fr
JOIN 
    fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND frt.language = 'US'
JOIN 
    fnd_application_tl fat ON fr.application_id = fat.application_id AND fat.language = 'US'
JOIN 
    fnd_menus_vl m ON fr.menu_id = m.menu_id
JOIN 
    fnd_menu_entries_vl fme ON m.menu_id = fme.menu_id
LEFT JOIN 
    fnd_form_functions_tl fft ON fme.function_id = fft.function_id AND fft.language = 'US'
WHERE 
    UPPER(frt.responsibility_name) LIKE '%CUST%'  -- Use '%XX%' or your prefix
ORDER BY 
    frt.responsibility_name, function_name;



Include Assigned Users

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'; 
SELECT 
    fu.user_name,
    frt.responsibility_name,
    fat.application_name AS module_name,
    m.menu_name,
    fme.prompt AS menu_entry_prompt,
    fft.user_function_name AS function_name
FROM 
    fnd_user_resp_groups_direct ur
JOIN 
    fnd_user fu ON ur.user_id = fu.user_id
JOIN 
    fnd_responsibility fr ON ur.responsibility_id = fr.responsibility_id
JOIN 
    fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND frt.language = 'US'
JOIN 
    fnd_application_tl fat ON fr.application_id = fat.application_id AND fat.language = 'US'
JOIN 
    fnd_menus_vl m ON fr.menu_id = m.menu_id
JOIN 
    fnd_menu_entries_vl fme ON m.menu_id = fme.menu_id
LEFT JOIN 
    fnd_form_functions_tl fft ON fme.function_id = fft.function_id AND fft.language = 'US'
WHERE 
    UPPER(frt.responsibility_name) LIKE '%CUST%' -- Or use '%XX%', '%ZZ%', etc.
ORDER BY 
    fu.user_name, frt.responsibility_name, function_name;


In-build Standard

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'; 

SELECT 
    frt.responsibility_name,
    fft.user_function_name,
    fff.form_name,
    std_app.application_short_name AS standard_module_short_code,
    std_appt.application_name AS standard_module_name
FROM 
    fnd_responsibility fr
JOIN 
    fnd_responsibility_tl frt ON fr.responsibility_id = frt.responsibility_id AND frt.language = 'US'
JOIN 
    fnd_menus_vl m ON fr.menu_id = m.menu_id
JOIN 
    fnd_menu_entries_vl fme ON m.menu_id = fme.menu_id
JOIN 
    fnd_form_functions ff ON fme.function_id = ff.function_id
JOIN 
    fnd_form_functions_tl fft ON ff.function_id = fft.function_id AND fft.language = 'US'
LEFT JOIN 
    fnd_form fff ON ff.form_id = fff.form_id
LEFT JOIN 
    fnd_application std_app ON fff.application_id = std_app.application_id
LEFT JOIN 
    fnd_application_tl std_appt ON std_app.application_id = std_appt.application_id AND std_appt.language = 'US'
WHERE 
    UPPER(frt.responsibility_name) LIKE '%CUST%' -- or your prefix like '%XX%'
ORDER BY 
    frt.responsibility_name, standard_module_name;





Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment