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

上一篇 / 下一篇 2011-05-20 16:41:41 / 个人分类：Discover

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，就拥有相关报表，不用担心人员离职与新进人员分配， 当然也跟权限规划是否合理有关系；