Oracle EBS工作流表Subject等字段取值为空解决方法

最新推荐文章于 2022-10-29 10:29:55 发布

sunansheng

最新推荐文章于 2022-10-29 10:29:55 发布

阅读量4.1k

点赞数 1

分类专栏： Oracle EBS 文章标签： Workflow EBS wf_notifications

本文链接：https://blog.csdn.net/sunansheng/article/details/49620789

版权

Oracle EBS 专栏收录该内容

48 篇文章 18 订阅

订阅专栏

本文来源：http://blog.csdn.net/sunansheng/article/details/49620789

问题描述

在做Oracle EBS工作流列表开发的时候，可以使用如下SQL获取系统工作流列表：

--sql已做精简
SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
 wn.notification_id,
 wn.from_user,
 decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
 decode(wn.more_info_role,
        NULL,
        wn.subject,
        fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
 wn.language,
 wn.begin_date,
 wn.due_date,
 wn.status,
 wn.priority,
 'P' AS priority_f,
 wn.recipient_role,
 wn.end_date,
 wit.display_name AS TYPE,
 wn.more_info_role,
 wn.from_role,
 wn.message_type,
 wn.message_name,
 wn.mail_status,
 wn.original_recipient
  FROM wf_notifications wn,
       wf_item_types_tl wit,
       wf_lookups_tl    wl
 WHERE wn.status = 'OPEN'
   AND wn.message_type = wit.name
   AND wit.language = userenv('LANG')
   AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
   AND wn.status = wl.lookup_code
   AND wl.language = userenv('LANG')

但是会发现有些行的From_user、TO_USER、SUBJECT等字段为空，如下图

当我们在EBS界面上面打开了工作流列表，发现这些字段又有值了。

解决方法：

我们可以通过表wf_notification_attributes取出各个字段的值。

修改后了SQL：

SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
 wn.notification_id,
 nvl(wn.from_user, cux_wx_workflow_utl.get_from_user(wn.notification_id)) from_user,
 nvl(wn.to_user, cux_wx_workflow_utl.get_role_displayname(wn.recipient_role)) to_user,
 nvl(wn.subject, cux_wx_workflow_utl.get_subject(wn.notification_id, wn.message_type)) subject,
 wn.begin_date,
 wn.recipient_role,
 wit.display_name AS TYPE,
 nvl(wn.from_role, cux_wx_workflow_utl.get_from_role(wn.notification_id)) from_role,
 cux_wx_workflow_utl.get_document_id(wn.notification_id) document_id
  FROM apps.wf_notifications wn,
       apps.wf_item_types_tl wit,
       apps.wf_lookups_tl    wl
 WHERE wn.status = 'OPEN'
   AND wn.message_type = wit.name
   AND wit.language = 'ZHS'
   AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
   AND wn.status = wl.lookup_code
   AND wl.language = userenv('LANG')
   AND wn.message_type IN ('CUXFINPR', 'CUXXPMWF')
   AND wn.message_name IN ('CUX_PRJ_REQ_APPROVE', 'CUXMSG_REQ_APPROVAL');

cux_wx_workflow_utl包关键代码：

FUNCTION get_from_user(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
    l_username VARCHAR2(250);
  BEGIN
     SELECT wf_directory.getroledisplayname(wna.text_value)
       INTO l_username
       FROM wf_notification_attributes wna
      WHERE wna.notification_id = p_notification_id
        AND wna.name = '#FROM_ROLE';
    RETURN l_username;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_from_user;
  
  FUNCTION get_from_role(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
    l_username VARCHAR2(250);
  BEGIN
     SELECT (wna.text_value)
       INTO l_username
       FROM wf_notification_attributes wna
      WHERE wna.notification_id = p_notification_id
        AND wna.name = '#FROM_ROLE';
    RETURN l_username;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_from_role;
  
  FUNCTION get_role_displayname(p_rolename IN VARCHAR2) RETURN VARCHAR2 IS
    l_username VARCHAR2(550);
  BEGIN
    l_username :=  wf_directory.getroledisplayname(p_rolename);
    RETURN l_username;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_role_displayname;
  
  FUNCTION get_subject(p_notification_id IN NUMBER,p_message_type IN VARCHAR2) RETURN VARCHAR2 IS
    l_subject VARCHAR2(2050);
  BEGIN
    SELECT wna.text_value
       INTO l_subject
       FROM wf_notification_attributes wna
      WHERE wna.notification_id = p_notification_id
        AND wna.name = 'DOCUMENT_NUMBER';
        
    IF p_message_type = 'CUXFINPR' THEN
      RETURN '付款申请 '|| l_subject ||' 请求审批';
    ELSIF p_message_type = 'CUXXPMWF' THEN
      RETURN '项目 '|| l_subject ||' 请求审批';
    ELSE
      RETURN l_subject ||' 请求审批';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_subject;
  
  FUNCTION get_document_id(p_notification_id IN NUMBER) RETURN VARCHAR2 IS
    l_subject VARCHAR2(2050);
  BEGIN
    SELECT SUBSTR(wna.text_value,INSTR(wna.text_value,'=',1)+1)
       INTO l_subject
       FROM wf_notification_attributes wna
      WHERE wna.notification_id = p_notification_id
        AND wna.name = 'OPEN_FORM_COMMAND';
        
    RETURN l_subject;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_document_id;

其它

完整的worklist的SQL语句如下，当然如果不考虑more_info_role，可以使用上面简化的SQL

SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/
 wn.notification_id,
 wn.from_user,
 decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
 decode(wn.more_info_role,
        NULL,
        wn.subject,
        fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
 wn.language,
 wn.begin_date,
 wn.due_date,
 wn.status,
 wn.priority,
 'P' AS priority_f,
 wn.recipient_role,
 wn.end_date,
 wit.display_name AS TYPE,
 wn.more_info_role,
 wn.from_role,
 wn.message_type,
 wn.message_name,
 wn.mail_status,
 wn.original_recipient
  FROM wf_notifications wn,
       wf_item_types_tl wit,
       wf_lookups_tl    wl
 WHERE wn.status = 'OPEN'
   AND wn.message_type = wit.name
   AND wit.language = userenv('LANG')
   AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
   AND wn.status = wl.lookup_code
   AND wl.language = userenv('LANG')
   AND wn.recipient_role IN (SELECT wur.role_name
                               FROM wf_user_roles wur
                              WHERE wur.user_name = :1
                                AND wur.user_orig_system = :2
                                AND wur.user_orig_system_id = :3)
   AND more_info_role IS NULL
UNION ALL
SELECT /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N6)*/
 wn.notification_id,
 wn.from_user,
 decode(wn.more_info_role, NULL, wn.to_user, wf_directory.getroledisplayname(wn.more_info_role)) AS to_user,
 decode(wn.more_info_role,
        NULL,
        wn.subject,
        fnd_message.get_string('FND', 'FND_MORE_INFO_REQUESTED') || ' ' || wn.subject) AS subject,
 wn.language,
 wn.begin_date,
 wn.due_date,
 wn.status,
 wn.priority,
 'P' AS priority_f,
 wn.recipient_role,
 wn.end_date,
 wit.display_name AS TYPE,
 wn.more_info_role,
 wn.from_role,
 wn.message_type,
 wn.message_name,
 wn.mail_status,
 wn.original_recipient
  FROM wf_notifications wn,
       wf_item_types_tl wit,
       wf_lookups_tl    wl
 WHERE wn.status = 'OPEN'
   AND wn.message_type = wit.name
   AND wit.language = userenv('LANG')
   AND wl.lookup_type = 'WF_NOTIFICATION_STATUS'
   AND wn.status = wl.lookup_code
   AND wl.language = userenv('LANG')
   AND wn.more_info_role IN (SELECT wur.role_name
                               FROM wf_user_roles wur
                              WHERE wur.user_name = :4
                                AND wur.user_orig_system = :5
                                AND wur.user_orig_system_id = :6)