OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » Query to display the submenu's of a responsibilties for a particular user

Home » Applications » Oracle Fusion Apps & E-Business Suite » Query to display the submenu's of a responsibilties for a particular user (Oracle Apps 11i )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query to display the submenu's of a responsibilties for a particular user [message #582697]

Mon, 22 April 2013 00:23

raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI

Junior Member

Hi All,

Query to display the submenu's of a responsibilties for a particular user.Input parameter will be user name.

My username is ravi123 and the responsibilty attached to my user is "XML Publisher Administrator".Output should be as below.

User Responsibility_Name Prompt
Ravi123 XML Publisher Administrator Templates
Ravi123 XML Publisher Administrator Data Definitions
Ravi123 XML Publisher Administrator Administration
Ravi123 XML Publisher Administrator Run
Ravi123 XML Publisher Administrator Set

Regards
Ravi

Report message to a moderator

Re: Query to display the submenu's of a responsibilties for a particular user [message #582792 is a reply to message #582697]

Mon, 22 April 2013 11:00

raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI

Junior Member

Hi All,

Any reply?

Regards
Ravi

Report message to a moderator

Re: Query to display the submenu's of a responsibilties for a particular user [message #584126 is a reply to message #582792]

Wed, 08 May 2013 21:25

raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI

Junior Member

Hi All,

I am using below query to display the menu list,but its fetching etc menu's like logout,Help,Close window.

SELECT fmet.menu_id,
fmet.prompt,
fffl.user_function_name,
ffv.form_name,
ffv.form_id,
ffv.user_Form_name,
fffl.function_id
FROM apps.fnd_menu_entries_vl fmet,
apps.fnd_form_functions_vl fffl,
apps.fnd_form_vl ffv,
(SELECT DISTINCT fme.menu_id
FROM APPS.FND_MENU_ENTRIES FME
START WITH fme.menu_id = 77402 --menu_id of xml publisher responsibility
CONNECT BY prior fme.sub_menu_id = fme.menu_id
) fminline
WHERE 1 =1
AND FFFL.FUNCTION_ID = FMET.FUNCTION_ID
AND ffv.form_id (+) = fffl.form_id
AND FMET.MENU_ID = FMINLINE.MENU_ID
/*AND NOT EXISTS
(SELECT 1
FROM fnd_resp_functions frf
WHERE(frf.responsibility_id = 24195
AND frf.application_id = 603 )
AND frf.action_id in(fffl.function_id,fmet.menu_id)
)*/
AND fmet.prompt IS NOT NULL;

Regards
Ravi

Report message to a moderator

Re: Query to display the submenu's of a responsibilties for a particular user [message #584331 is a reply to message #584126]

Mon, 13 May 2013 02:49

raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI

Junior Member

Hi All,

Any reply?

Regards
Ravi

Report message to a moderator

Re: Query to display the submenu's of a responsibilties for a particular user [message #584724 is a reply to message #584331]

Fri, 17 May 2013 02:28

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Check this

SELECT fnu.user_name
      ,fr.responsibility_name
      ,ff.user_function_name
      ,fm.prompt
FROM fnd_user fnu
    ,fnd_user_resp_groups fur
    ,fnd_responsibility_vl fr
    ,fnd_form_functions_vl ff
    ,(SELECT function_id,prompt,SYS_CONNECT_BY_PATH(menu_id,'/') menu
      FROM fnd_menu_entries_vl 
      WHERE prompt IS NOT NULL
      AND function_id IS NOT NULL
      CONNECT BY PRIOR sub_menu_id = menu_id
      AND PRIOR prompt IS NOT NULL) fm -- this is the difference
WHERE fnu.user_id = fur.user_id
AND fur.responsibility_id = fr.responsibility_id
AND fr.menu_id = SUBSTR(fm.menu,2,INSTR(fm.menu,'/',2,1)-2)
AND fm.function_id = ff.function_id
AND fnu.user_name = '&user_name'

As there is no filter on the inner sql on menu entries, this sql may have a performance hit.
If you don\t want to use user name and want to correct the previous sql, check the comment as difference.

By
Vamsi

Report message to a moderator

Previous Topic:	How to apply Single receipt against multiple invoices for Different Customers
Next Topic:	Errors..

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 20:33:17 CST 2025