用户与职责与请求关系语句

--本SQL也可以简单理解为 获取用户可以提交什么请求，（请求可以简单理解为报表，但请求不是报表，包含关系）

/* Formatted on 2018/3/15 10:17:03 (QP5 v5.256.13226.35538) */

  SELECT fu.user_id

       , fu.user_name

       , fu.start_date

       , fu.end_date

       , fu.description

       , fe.last_name

       , fr.responsibility_name

       , fr.description                                                                                                                         --职责描述

       , fr.start_date

       , fr.end_date

       , frg.request_group_name                                                                                                             ---- 请求组名称

       , frg.description request_desc                                                                                                       ---- 请求组描述

       , fr.menu_id                                                                                                                        ---- 菜单  ID

       , request_unit_type                                                                                                                   ---- 请求类型

       , fcp.user_concurrent_program_name                                                                                                   ---请求并发程序名

       , DECODE (fcp.execution_method_code

               , 'H', '主机'

               , 'S', '立即'

               , 'J', 'Java 存储过程'

               , 'K', 'Java 并发程序'

               , 'M', '多语言功能'

               , 'P', 'Oracle Reports'

               , 'I', 'PL/SQL 存储过程'

               , 'B', '请求集阶段函数'

               , 'A', '派生'

               , 'L', 'SQL*Loader 程序'

               , 'Q', 'SQL*Plus'

               , 'E', 'Perl 并发程序')

            program_type

    FROM fnd_user fu

       , hr_employees fe

       , fnd_user_resp_groups_direct ugd

       , fnd_responsibility_vl fr

       , fnd_request_groups frg

       , fnd_request_group_units frgu

       , fnd_concurrent_programs_vl fcp

   WHERE 1 = 1

     --AND TO_CHAR (fu.creation_date, 'yyyy') >= '2008'

     AND fu.employee_id = fe.employee_id(+)                                                                                                  --用户与职员关系

     AND fu.user_id = ugd.user_id

     AND ugd.responsibility_id = fr.responsibility_id

     AND ugd.responsibility_application_id = fr.application_id                                                                           --- 以上用户与职责关系

     AND fr.request_group_id = frg.request_group_id(+)

     AND fr.group_application_id = frg.application_id(+)                                                                               --- 以上是请求组和职责关系

     AND frgu.application_id(+) = frg.application_id

     AND frg.request_group_id = frgu.request_group_id(+)                                                                              --- 以上是请求组中间表与职责

     AND fcp.concurrent_program_id = frgu.request_unit_id

     AND frgu.unit_application_id = fcp.application_id

     AND user_name = 'SYSADMIN'                                                                                               --- 'SYSADMIN' 登录用户名,可变量

ORDER BY user_id, responsibility_name