SELECT men.* FROM(
SELECT main_query.responsibility_name “Responsibility”,
main_query.user_menu_name “Main Menu Name”,
main_query.entry_sequence “Seq”, main_query.prompt prompt,
main_query.user_function_name “Function”,
main_query.func_descrip “Function Descrip”,
main_query.sub_menu_name “SubMenu Name”, main_query.sub_seq “Sub Seq”,
main_query.sub_prompt “SubPrompt”, main_query.sub_func “SubFunction”,
main_query.sub_func_descrip “SubFunction Descrip”,
main_query.grant_flag “Grant Flag”,
main_query.resp_end_date “Resp End Date”,resp_id,menu_id
FROM (SELECT FIRST.application_id, FIRST.application_name,
FIRST.responsibility_id resp_id, FIRST.responsibility_name,
FIRST.end_date AS resp_end_date, FIRST.menu_id,
FIRST.user_menu_name, FIRST.entry_sequence, FIRST.prompt,
FIRST.function_id, ffft.user_function_name,
ffft.description AS func_descrip, FIRST.sub_menu_id,
fmv2.user_menu_name AS sub_menu_name,
fme2.entry_sequence AS sub_seq, fmet2.prompt AS sub_prompt,
fme2.function_id AS sub_func_id,
ffft2.user_function_name AS sub_func,
ffft2.description AS sub_func_descrip,
fme2.sub_menu_id AS sub_sub_menu_id, FIRST.grant_flag
FROM (SELECT fat.application_id, fat.application_name,
fr.responsibility_id, frt.responsibility_name,
fr.end_date, fr.menu_id, fmv.user_menu_name,
fme.entry_sequence, fmet.prompt, fme.sub_menu_id,
fme.function_id, fme.grant_flag
FROM apps.fnd_application_tl fat,
apps.fnd_responsibility fr,
apps.fnd_menus_vl fmv,
apps.fnd_responsibility_tl frt,
apps.fnd_menu_entries fme,
apps.fnd_menu_entries_tl fmet
WHERE fat.application_id = fr.application_id(+)
AND fr.menu_id = fmv.menu_id(+)
AND fr.responsibility_id = frt.responsibility_id(+)
AND fr.menu_id = fme.menu_id(+)
AND fme.menu_id = fmet.menu_id(+)
AND fme.entry_sequence = fmet.entry_sequence(+)
AND fmet.LANGUAGE = ‘US’
— AND fat.application_id = :p_appl_id
— AND frt.responsibility_id=50422
) FIRST,
apps.fnd_menus_vl fmv2,
apps.fnd_menu_entries fme2,
apps.fnd_menu_entries_tl fmet2,
apps.fnd_form_functions_tl ffft,
apps.fnd_form_functions_tl ffft2
WHERE FIRST.function_id = ffft.function_id(+)
AND FIRST.sub_menu_id = fmv2.menu_id(+)
AND FIRST.sub_menu_id = fme2.menu_id(+)
AND fme2.menu_id = fmet2.menu_id(+)
AND fme2.entry_sequence = fmet2.entry_sequence(+)
AND fme2.function_id = ffft2.function_id(+)) main_query
LEFT OUTER JOIN
apps.fnd_resp_functions exc
ON ( main_query.application_id = exc.application_id
AND main_query.resp_id = exc.responsibility_id
AND ( main_query.function_id = exc.action_id
OR main_query.sub_func_id = exc.action_id
OR main_query.menu_id = exc.action_id
OR main_query.sub_menu_id = exc.action_id
OR main_query.sub_sub_menu_id = exc.action_id
)
)
) men,FND_USER_RESP_GROUPS furg,FND_USER fu
where 1=1
AND fu.user_id = furg.user_id
AND furg.responsibility_id=resp_id
and fu.user_name = ‘xxxxxx’
and “Grant Flag”=’Y’
order by 1,3
Recent Posts
