Query to find menu exclusions in oracle apps
In this post , We will be discuss about query to find menu exclusions in oracle apps. Using this SQL query , we can extract the Oracle Responsibility application menu details. We can able to find out the each menu functions and the menu , submenu exclusion for the responsibility menu.We can easily find out which are the menu functions excluded for responsibility menu in oracle apps. This is one of the useful sql query to related to oracle responsibility and menu details. Here below is the complete detail about query to find menu exclusions in oracle apps.
8 Important table used by query to find menu exclusions in oracle apps
1.fnd_application_tl
2.fnd_responsibility
3.fnd_menus_vl
4.fnd_responsibility_tl
5.fnd_menu_entries
6.fnd_menu_entries_tl
7.fnd_form_functions_vl
8.fnd_form_functions_tl
2.fnd_responsibility
3.fnd_menus_vl
4.fnd_responsibility_tl
5.fnd_menu_entries
6.fnd_menu_entries_tl
7.fnd_form_functions_vl
8.fnd_form_functions_tl
Detail SQL Query to find menu exclusions in oracle apps
Here below is the detail sql query which help to extract the complete details about oracle responsibility menu exclusions.
, 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 ))
|
| Query to find menu exclusions in oracle apps |
0 comments:
Post a Comment