drdavidtaylor

SQL Query to identify Functions, Menus, Responsibilities and Users

The following query will list Menus, Responsibilities and Users for a specific Function:

SELECT fffv.user_function_name
,      fmv.menu_name
,      frv.responsibility_name
,      user_name
FROM   fnd_form_functions_vl fffv
,      fnd_menu_entries_vl fmev
,      fnd_menus_vl fmv
,      fnd_responsibility_vl frv
,      fnd_user fu
,      fnd_user_resp_groups_direct furgd
WHERE  fffv.function_name = 'SYSTEM_ADMINISTRATOR'
AND    fffv.function_id = fmev.function_id
AND    fmv.menu_id = fmev.menu_id
AND    fmv.menu_id = frv.menu_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(fu.start_date) AND NVL(TRUNC(fu.end_date),SYSDATE + 1)
AND    fu.user_id = furgd.user_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(furgd.start_date) AND NVL(TRUNC(furgd.end_date),SYSDATE + 1)
AND    frv.responsibility_id = furgd.responsibility_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(frv.start_date) AND NVL(TRUNC(frv.end_date),SYSDATE + 1)
;
Posted in Oracle ApplicationsLeave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s