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

2021年09月15日 阅读数:1
这篇文章主要向大家介绍用户与职责与请求关系语句,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

--本SQL也能够简单理解为 获取用户能够提交什么请求,(请求能够简单理解为报表,但请求不是报表,包含关系)并发

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

  SELECT fu.user_id函数

       , fu.user_namespa

       , fu.start_datecode

       , fu.end_dateorm

       , fu.descriptionip

       , fe.last_nameit

       , fr.responsibility_nameio

       , fr.description                                                                                                                         --职责描述ast

       , 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