求SQL語句_Responsibility對應Function明細

以下為我們寫的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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/741178/viewspace-132933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/741178/viewspace-132933/