Scribd
Upload
250 views2 pages

SQL Query User Responsibility Menus

This SQL query selects user information, responsibilities, applications, security groups, menus, and menu exclusions for users with names like 'SYS%' and a responsibility of 'Purchasing User'. It joins several tables to retrieve this data and filters for the current user's language and active people. The results are ordered by user name and responsibility name.

Uploaded by

raoof
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
250 views2 pages

SQL Query User Responsibility Menus

This SQL query selects user information, responsibilities, applications, security groups, menus, and menu exclusions for users with names like 'SYS%' and a responsibility of 'Purchasing User'. It joins several tables to retrieve this data and filters for the current user's language and active people. The results are ordered by user name and responsibility name.

Uploaded by

raoof
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

/* Formatted on 2016/01/22 19:53 (Formatter Plus v4.8.

0) */
SELECT fu.user_name "User Name", frt.responsibility_name "Responsibility",
fat.application_name "Application", papf.full_name "Person",
fsg.security_group_key "Security Group", fm.menu_name "Menu",
fmt.user_menu_name, ffft.user_function_name, fmet.prompt,
fmt_sub.user_menu_name sub_menu,
DECODE (frf.rule_type,
'F', 'Function',
'M', 'Menu'
) "Menu Exclusion Type",
DECODE (frf.rule_type,
'F', (SELECT user_function_name
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = frf.action_id),
'M', (SELECT menu_name
FROM fnd_menus_vl imn
WHERE imn.menu_id = frf.action_id),
TO_CHAR (frf.action_id)
) "Menu Exclusion Name",
DECODE
(frf.rule_type,
'F', (SELECT description
FROM fnd_form_functions_vl fnc
WHERE fnc.function_id = frf.action_id),
'M', (SELECT description
FROM fnd_menus_vl imn
WHERE imn.menu_id = frf.action_id),
TO_CHAR (frf.action_id)
) "Menu Exclusion Description"
FROM fnd_user_resp_groups_direct furg,
applsys.fnd_user fu,
fnd_menus fm,
fnd_menus_tl fmt,
fnd_menu_entries fme,
fnd_menu_entries_tl fmet,
fnd_form_functions_tl ffft,
fnd_menus_tl fmt_sub,
applsys.fnd_responsibility_tl frt,
applsys.fnd_responsibility fr,
applsys.fnd_application_tl fat,
applsys.fnd_application fa,
fnd_security_groups fsg,
per_all_people_f papf, --,
fnd_resp_functions frf
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.LANGUAGE = USERENV ('LANG')
AND furg.security_group_id = fsg.security_group_id(+)
AND fmt.LANGUAGE = USERENV ('LANG')
AND fu.employee_id = papf.person_id
AND fm.menu_id = fr.menu_id
AND fmt.menu_id = fr.menu_id
AND fm.menu_id = fme.menu_id
AND fme.menu_id = fmet.menu_id
and fme.entry_sequence = fmet.entry_sequence
AND fme.function_id = ffft.function_id (+)
AND fmt_sub.menu_id(+) = fme.sub_menu_id
AND fmt_sub.LANGUAGE(+) = USERENV ('LANG')
and fmet.language = userenv ('LANG')
AND ffft.LANGUAGE(+) = USERENV ('LANG')
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND fr.responsibility_id = frf.responsibility_id(+)
and fr.application_id = frf.application_id(+)
and fu.user_name like 'SYS%'
and frt.responsibility_name like 'Purchasing User'
--'General Ledger Super User'
order by fu.user_name, frt.responsibility_name;

You might also like