I am new to oracle istore , Now i am work with oracle Responsibility and menu creation process in oracle 11i. I need the sql query for getting function name and its submenu name based on the Selected responsibility. This query will return the all the submenu and its function details.
The below query is working for me
SELECT UNIQUE u.user_id, u.user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
WHERE u.USER_NAME = 'useridid'
AND g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)
which i got from