Oracle EBS-SQL (SYS-22):sysadmin_用户职责查询.sql

select fu.user_name 用户名,

         fu.description 用户说明,

         frv.RESPONSIBILITY_NAME 职责名称,

         REQUEST_GROUP_NAME 报表组,

         (select  ppf.FULL_NAME

            from apps.per_people_f  ppf

          where fu.employee_id = ppf.PERSON_ID and trunc(sysdate)  between  trunc(ppf.EFFECTIVE_START_DATE)

             and trunc(ppf.EFFECTIVE_END_DATE)) 人员

from  applsys.FND_USER fu     ,

         apps.FND_USER_RESP_GROUPS_DIRECT  fur     ,

         apps.FND_RESPONSIBILITY_VL frv     ,

         apps.FND_REQUEST_GROUPS  frg    

where fu.user_id = fur.user_id and frv.RESPONSIBILITY_ID = fur.RESPONSIBILITY_ID

    and nvl(frv.END_DATE,sysdate) >= sysdate

    and nvl(fur.END_DATE,sysdate) >= sysdate

    and nvl(FU.END_DATE,sysdate)  >= sysdate
    and frv.REQUEST_GROUP_ID = frg.REQUEST_GROUP_ID(+)

    and (frv.RESPONSIBILITY_NAME like '10%' or frv.RESPONSIBILITY_NAME like '20%')

order by fu.user_name,

        frv.RESPONSIBILITY_NAME