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

本文来源：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 NULLUNION ALLSELECT /*+ 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)