由erp function找erp user

1. 找出user/define的function id

select user_function_name, function_id

from FND_FORM_FUNCTIONS_VL

where user_function_name like 'Users';

=> 86

2. 找出該function id 對應的menu id

select menu_id, menu_name, user_menu_name, description

from fnd_menus_vl

where menu_id in

(select menu_id

from fnd_menu_entries_vl

where function_id = 86);

=> menu_id = 1009879,67869

3. 找出該menu id的main menu (有可能step 2的menu是sub-menu，而非main menu)

select level,

       (select user_menu_name from fnd_menus_vl where menu_id = fnd_menu_entries_vl.menu_id) as menu_name,

       menu_id,

       sub_menu_id,

       function_id,

       prompt,

       description

from fnd_menu_entries_vl

start with sub_menu_id in (1009879,67869)

connect by prior menu_id = sub_menu_id;

將1009879,67869, menu_id and sub_menu_id 集合起來，去除重複的部份

=>

1001683

67860

67864

67869

68717

69399

69401

69402

4. 由menu id找 user name

select b.USER_NAME,

       d.APPLICATION_SHORT_NAME,

       d.APPLICATION_NAME,

       c.RESPONSIBILITY_NAME

from FND_USER_RESP_GROUPS_DIRECT a,

     FND_USER b,

     FND_RESPONSIBILITY_VL c,

     FND_APPLICATION_VL d

where b.USER_ID = a.USER_ID

and c.RESPONSIBILITY_ID = a.RESPONSIBILITY_ID

and c.APPLICATION_ID = a.RESPONSIBILITY_APPLICATION_ID

and d.APPLICATION_ID = a.RESPONSIBILITY_APPLICATION_ID

and (a.end_date is NULL or a.end_date > sysdate)

and (b.end_date is NULL or b.end_date > sysdate)

and (c.end_date is NULL or c.end_date > sysdate)

and b.user_id > 10

and c.menu_id in (

1001683

,67860

,67864

,67869

,68717

,69399

,69401

,69402

)

order by user_name,application_short_name;

=> 找出ERP username