Wednesday, November 19, 2014

FND: Query: Find Form Functions assigned to a particular APPS User

SELECT   function_name
FROM     (SELECT DISTINCT
         (       SELECT function_name
                 FROM   fnd_form_functions f
                 WHERE  f.function_id = me.function_id) function_name
          FROM   fnd_menu_entries me
          START WITH 
                 me.menu_id IN( SELECT r.menu_id
                                FROM   fnd_responsibility    r
                                ,      fnd_user_resp_groups  rg
                                ,      fnd_user              u
                                WHERE  rg.responsibility_id = r.responsibility_id   
                                AND    u.user_id = rg.user_id
                                AND    u.user_name = UPPER('&user_name') )
           CONNECT BY 
                 me.menu_id = PRIOR me.sub_menu_id)
WHERE    function_name IS NOT NULL 
MINUS
SELECT   function_name
FROM     (SELECT DISTINCT
         (       SELECT function_name
                 FROM   fnd_form_functions f
                 WHERE  f.function_id = me.function_id) function_name
          FROM   fnd_menu_entries me
          START WITH 
                 me.menu_id IN( SELECT rf.action_id
                                FROM   fnd_responsibility    r
                                ,      fnd_user_resp_groups  rg
                                ,      fnd_user              u
,      fnd_resp_functions    rf
                                WHERE  rg.responsibility_id = r.responsibility_id   
                                AND    u.user_id = rg.user_id
                                AND    u.user_name = UPPER('&user_name') 
                                AND    rf.responsibility_id = r.responsibility_id
                                AND    rf.rule_type = 'M')
           CONNECT BY 
                 me.menu_id = PRIOR me.sub_menu_id)
WHERE    function_name IS NOT NULL
MINUS
SELECT ff.function_name
FROM   fnd_responsibility    r
,      fnd_user_resp_groups  rg
,      fnd_user              u
,      fnd_resp_functions    rf
,      fnd_form_functions    ff
WHERE  rg.responsibility_id = r.responsibility_id   
AND    u.user_id = rg.user_id
AND    u.user_name = UPPER('&user_name') 
AND    rf.responsibility_id = r.responsibility_id
AND    rf.rule_type = 'F'  
AND    ff.function_id = rf.action_id

No comments:

Post a Comment