oracle中subject是什么意思,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 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)