Discoverer:查询报表被user和Responsibility直接拥有

1. 对应sql：

  SELECT CASE
         WHEN TRUNC(SYSDATE) BETWEEN FU.START_DATE AND
              NVL(FU.END_DATE, TRUNC(SYSDATE)) THEN
          FU.USER_NAME
         ELSE
          EEU.EU_USERNAME
       END USER_NAME,
       fu.description,

       ED.DOC_NAME
  FROM DISADMIN.EUL5_EUL_USERS    EEU,
       DISADMIN.EUL5_ACCESS_PRIVS EAP,
       DISADMIN.EUL5_DOCUMENTS    ED,
       FND_USER                   FU
 WHERE ED.DOC_NAME like 'PO%'
   AND ED.DOC_CREATED_BY = '#1110'
   AND EAP.GD_DOC_ID = ED.DOC_ID
   AND EAP.AP_EU_ID = EEU.EU_ID
   AND EEU.EU_ROLE_FLAG = 0 --用户
   AND TO_CHAR(FU.USER_ID) = SUBSTR(EEU.EU_USERNAME, 2)
UNION ALL
SELECT CASE
         WHEN TRUNC(SYSDATE) BETWEEN R.START_DATE AND
              NVL(R.END_DATE, TRUNC(SYSDATE)) THEN
          R.RESPONSIBILITY_NAME
         ELSE
          EEU.EU_USERNAME
       END USER_NAME,
       null description,
       ED.DOC_NAME
  FROM DISADMIN.EUL5_EUL_USERS    EEU,
       DISADMIN.EUL5_ACCESS_PRIVS EAP,
       DISADMIN.EUL5_DOCUMENTS    ED,
       FND_RESPONSIBILITY_VL      R
 WHERE ED.DOC_NAME = 'PO%'
   AND ED.DOC_CREATED_BY = '#1110'
   AND EAP.GD_DOC_ID = ED.DOC_ID
   AND EAP.AP_EU_ID = EEU.EU_ID
   AND EEU.EU_ROLE_FLAG = 1 --职责
   AND TO_CHAR(R.RESPONSIBILITY_ID) =
       SUBSTR(EEU.EU_USERNAME, 2, INSTR(EEU.EU_USERNAME, '#', 1, 2) - 2)
   AND TO_CHAR(R.APPLICATION_ID) =
       SUBSTR(EEU.EU_USERNAME, INSTR(EEU.EU_USERNAME, '#', 1, 2) + 1)

2.建议点：

  A.报表分配跟职责相关，可以减轻管理员工作量，

     因为user拥有了那些Responsibility，就拥有相关报表，不用担心人员离职与新进人员分配，   当然也跟权限规划是否合理有关系；

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

转载于:http://blog.itpub.net/41594/viewspace-695844/