以下為我們寫的SQL語句,但衹能撈取到Menu表中的第一層,各位XDJM能不能幫忙看看,
能不能撈到Menu表中最下一層Function。先謝啦!
select c.user_name as login_name,
d.full_name as employee_name,
-- f.name as department_name,
pv.job_name,
pv.name position_name,
-- a.user_id as user_id,
-- a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
fs.menu_name,
fm.prompt,
fm.description,
g.application_short_name
from apps.FND_USER_RESP_GROUPS a,
apps.FND_RESPONSIBILITY_VL b,
apps.fnd_user c,
apps.hr_employees d,
apps.per_assignments_f e,
apps.PER_POSITIONS_V pv,
apps.hr_all_organization_units_tl f,
apps.fnd_menu_entries_vl fm,
apps.fnd_menus fs,
-- APPS.fnd_menu_entries FV,
apps.fnd_application g
where fm.prompt is not null
and a.user_id = c.user_id
-- and c.user_id=nvl(:P_USER_ID,c.user_id)
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and fm.MENU_ID=fs.menu_id
and e.ORGANIZATION_ID = f.organization_id
and b.MENU_ID=fm.menu_id
and fm.SUB_MENU_ID not in (select frf.action_id
from fnd_resp_functions frf
where frf.responsibility_id=b.RESPONSIBILITY_ID
and frf.rule_type='M')
and c.end_date is null
and b.end_date is null
and a.responsibility_id = b.RESPONSIBILITY_ID
-- and b.RESPONSIBILITY_ID=nvl(:P_RESP_ID,b.RESPONSIBILITY_ID)
and e.position_id=pv.position_id
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.APPLICATION_ID = g.application_id
--and fm.language=userenv('lang')
and f.language=userenv('lang')
order by c.description, c.user_name, a.responsibility_id
能不能撈到Menu表中最下一層Function。先謝啦!
select c.user_name as login_name,
d.full_name as employee_name,
-- f.name as department_name,
pv.job_name,
pv.name position_name,
-- a.user_id as user_id,
-- a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
fs.menu_name,
fm.prompt,
fm.description,
g.application_short_name
from apps.FND_USER_RESP_GROUPS a,
apps.FND_RESPONSIBILITY_VL b,
apps.fnd_user c,
apps.hr_employees d,
apps.per_assignments_f e,
apps.PER_POSITIONS_V pv,
apps.hr_all_organization_units_tl f,
apps.fnd_menu_entries_vl fm,
apps.fnd_menus fs,
-- APPS.fnd_menu_entries FV,
apps.fnd_application g
where fm.prompt is not null
and a.user_id = c.user_id
-- and c.user_id=nvl(:P_USER_ID,c.user_id)
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and fm.MENU_ID=fs.menu_id
and e.ORGANIZATION_ID = f.organization_id
and b.MENU_ID=fm.menu_id
and fm.SUB_MENU_ID not in (select frf.action_id
from fnd_resp_functions frf
where frf.responsibility_id=b.RESPONSIBILITY_ID
and frf.rule_type='M')
and c.end_date is null
and b.end_date is null
and a.responsibility_id = b.RESPONSIBILITY_ID
-- and b.RESPONSIBILITY_ID=nvl(:P_RESP_ID,b.RESPONSIBILITY_ID)
and e.position_id=pv.position_id
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.APPLICATION_ID = g.application_id
--and fm.language=userenv('lang')
and f.language=userenv('lang')
order by c.description, c.user_name, a.responsibility_id
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/741178/viewspace-132933/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/741178/viewspace-132933/