Oracle apps menu hierarchy query
In this post , We will be discuss about Oracle apps menu hierarchy query. This SQL Query will help to extract the Oracle application Form Menu details in hierarchical view. Using this Query , We can able to find out the Each responsibility attached Menu name and Under that menu we can able to find out the submenu and function details. We can also find out the details of menu exclusion in oracle apps using this sql query. This is one of the usefully sql query to extract Application Form Menu. Here below is the complete detail about Oracle apps menu hierarchy query.
 |
| Oracle apps menu hierarchy query |
8 Important table used by Oracle apps menu hierarchy query
These are one of the important tables used to developed the menu hierarchy query in oracle apps.
1.fnd_menu_entries_tl
2.fnd_form_functions_vl
3.fnd_responsibility_tl
4.fnd_menu_entrie
5.fnd_form_functions_tl
2.fnd_form_functions_vl
3.fnd_responsibility_tl
4.fnd_menu_entrie
5.fnd_form_functions_tl
6.fnd_application_tl
7.fnd_responsibility
8.fnd_menus_vl
SELECT main_query.application_name "App Name"7.fnd_responsibility
8.fnd_menus_vl
Detail Oracle apps menu hierarchy query
, 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"
, DECODE( exc.rule_type
, 'F', ( SELECT 'Ex F: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND main_query.function_id = exc.action_id ) )
exclude_func
, DECODE( exc.rule_type
, 'F', ( SELECT 'Ex SF: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND main_query.sub_func_id = exc.action_id ) )
exclude_sub_func
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex M: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND main_query.menu_id = exc.action_id ) )
exclude_menu
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex SM: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND main_query.sub_menu_id = exc.action_id ) )
exclude_sub_menu
, DECODE( exc.rule_type
, 'M', ( SELECT 'Ex SSM: ' || exc.action_id
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = exc.action_id
AND main_query.sub_sub_menu_id = exc.action_id ) )
exclude_sub_sub_menu
FROM ( SELECT FIRST.application_id
, FIRST.application_name
, FIRST.responsibility_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) 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.responsibility_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 ))
|
| Oracle apps menu hierarchy query |
0 comments:
Post a Comment