Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfntfv.sql

REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfntfv.sql - WorkFlow NoTiFication Views
REM +======================================================================+

set verify off
define hdr = "$Header: wfntfv.sql 26.7 2003/02/07 08:45:16 vshanmug ship $"

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

/*
** WF_MESSAGES
*/
create or replace force view WF_MESSAGES_VL ( 
    ROW_ID,
    TYPE,
    NAME,
    PROTECT_LEVEL,
    CUSTOM_LEVEL,
    DEFAULT_PRIORITY,
    READ_ROLE,
    WRITE_ROLE,
    DISPLAY_NAME,
    DESCRIPTION,
    SUBJECT,
    BODY,
    HTML_BODY
  ) as select /* $Header: wfntfv.sql 26.7 2003/02/07 08:45:16 vshanmug ship $ */
    B.ROWID ROW_ID,
    B.TYPE,
    B.NAME,
    B.PROTECT_LEVEL,
    B.CUSTOM_LEVEL,
    B.DEFAULT_PRIORITY,
    B.READ_ROLE,
    B.WRITE_ROLE,
    T.DISPLAY_NAME,
    T.DESCRIPTION,
    T.SUBJECT,
    T.BODY,
    T.HTML_BODY
  from WF_MESSAGES B, WF_MESSAGES_TL T
  where B.TYPE = T.TYPE
  and B.NAME = T.NAME
  and T.LANGUAGE = userenv('LANG');

/*
** WF_MESSAGE_ATTRIBUTES
*/
create or replace force view WF_MESSAGE_ATTRIBUTES_VL ( 
    ROW_ID,
    MESSAGE_TYPE,
    MESSAGE_NAME,
    NAME,
    SEQUENCE,
    TYPE,
    SUBTYPE,
    VALUE_TYPE,
    PROTECT_LEVEL,
    CUSTOM_LEVEL,
    FORMAT,
    TEXT_DEFAULT,
    NUMBER_DEFAULT,
    DATE_DEFAULT,
    DISPLAY_NAME,
    DESCRIPTION,
    ATTACH
  ) as select /* $Header: wfntfv.sql 26.7 2003/02/07 08:45:16 vshanmug ship $ */
    B.ROWID ROW_ID,
    B.MESSAGE_TYPE,
    B.MESSAGE_NAME,
    B.NAME,
    B.SEQUENCE,
    B.TYPE,
    B.SUBTYPE,
    B.VALUE_TYPE,
    B.PROTECT_LEVEL,
    B.CUSTOM_LEVEL,
    B.FORMAT,
    B.TEXT_DEFAULT,
    B.NUMBER_DEFAULT,
    B.DATE_DEFAULT,
    T.DISPLAY_NAME,
    T.DESCRIPTION,
    B.ATTACH
  from WF_MESSAGE_ATTRIBUTES B, WF_MESSAGE_ATTRIBUTES_TL T
  where B.MESSAGE_NAME = T.MESSAGE_NAME
  and B.MESSAGE_TYPE = T.MESSAGE_TYPE
  and B.NAME = T.NAME
  and T.LANGUAGE = userenv('LANG');

/*
** WF_NOTIFICATIONS_VIEW
*/
create or replace force view WF_NOTIFICATIONS_VIEW (
       ROW_ID,
       NOTIFICATION_ID,
       GROUP_ID,
       MESSAGE_TYPE,
       MESSAGE_NAME,
       RECIPIENT_ROLE,
       STATUS,
       ACCESS_KEY,
       MAIL_STATUS,
       PRIORITY,
       BEGIN_DATE,
       END_DATE,
       DUE_DATE,
       USER_COMMENT,
       CALLBACK,
       CONTEXT,
       SUBJECT,
       MESSAGE,
       RECIPIENT_ROLE_NAME,
       STATUS_NAME
) as select /* &&hdr */
       N.ROWID ROW_ID,
       N.NOTIFICATION_ID,
       N.GROUP_ID,
       N.MESSAGE_TYPE,
       N.MESSAGE_NAME,
       N.RECIPIENT_ROLE,
       N.STATUS,
       N.ACCESS_KEY,
       N.MAIL_STATUS,
       N.PRIORITY,
       N.BEGIN_DATE,
       N.END_DATE,
       N.DUE_DATE,
       N.USER_COMMENT,
       N.CALLBACK,
       N.CONTEXT,
       WF_NOTIFICATION.GETSUBJECT(N.NOTIFICATION_ID) SUBJECT,
       WF_NOTIFICATION.GETSHORTBODY(N.NOTIFICATION_ID) MESSAGE,
       WF_DIRECTORY.GETROLEDISPLAYNAME(N.RECIPIENT_ROLE) RECIPIENT_ROLE_NAME,
       L.MEANING STATUS_NAME
from   WF_NOTIFICATIONS N, WF_LOOKUPS L
where  L.LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS'
and    L.LOOKUP_CODE = N.STATUS;

/*
** Response attributes for a notification group
*/
create or replace force view WF_NOTIFICATION_ATTR_RESP_V
(
group_id,
recipient_role,
recipient_role_display_name,
attribute_name,
attribute_display_name,
attribute_value,
attribute_display_value,
message_type,
message_name
)
as
select 	/* $Header: wfntfv.sql 26.7 2003/02/07 08:45:16 vshanmug ship $ */
        wfn.group_id,
	wfn.recipient_role,
	wf_directory.GetRoleDisplayName(Recipient_role),
	wfna.name,
	wfma.display_name,
	wfna.text_value,
	wf_notification.GetShortText('&'||wfna.name,wfn.notification_id) 
            attr_value,
	wfn.message_type,
	wfn.message_name
from 	wf_notifications wfn,
     	wf_notification_attributes wfna,
     	wf_message_attributes_vl wfma
where	wfn.status		= 'CLOSED'
and     wfn.message_type	= wfma.message_type
and     wfn.message_name 	= wfma.message_name
and	wfn.notification_id	= wfna.notification_id
and     wfna.name 		= wfma.name
and	wfma.subtype		= 'RESPOND';
--
--
/*
** WF_WORKLIST_V
*/
create or replace force view WF_WORKLIST_V
(
ROW_ID,
NID,
PRIORITY,
MESSAGE_TYPE,
RECIPIENT_ROLE,
SUBJECT,
BEGIN_DATE,
DUE_DATE,
END_DATE,
DISPLAY_STATUS,
STATUS,
ORIGINAL_RECIPIENT,
ITEM_TYPE,
MESSAGE_NAME,
FROM_USER,
TO_USER,
LANGUAGE,
MORE_INFO_ROLE
) as select /* &&hdr */
  WN.ROWID,
  WN.NOTIFICATION_ID,
  WN.PRIORITY,
  WIT.DISPLAY_NAME,
  WN.RECIPIENT_ROLE,
  WN.SUBJECT,
  WN.BEGIN_DATE,
  WN.DUE_DATE,
  WN.END_DATE,
  WL.MEANING,
  WN.STATUS,
  WN.ORIGINAL_RECIPIENT,
  WN.MESSAGE_TYPE,
  WN.MESSAGE_NAME,
  WN.FROM_USER,
  WN.TO_USER,
  WN.LANGUAGE,
  WN.MORE_INFO_ROLE
 from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_TL WIT, WF_LOOKUPS_TL WL
 where 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')
/

/*
** WF_HA_NTF_V for high availability replay
*/
create or replace force view WF_HA_NTF_V (
ROW_ID,Item_type,Item_key,NOTIFICATION_ID,GROUP_ID,MESSAGE_TYPE,MESSAGE_NAME,
RECIPIENT_ROLE,STATUS,ACCESS_KEY,MAIL_STATUS,PRIORITY,
BEGIN_DATE,END_DATE,DUE_DATE,RESPONDER,USER_COMMENT,CALLBACK,CONTEXT,
ORIGINAL_RECIPIENT,FROM_USER,TO_USER,SUBJECT,LANGUAGE,MORE_INFO_ROLE
)as
select  N.ROWID ROW_ID, I.Item_type, I.Item_key,
        N.NOTIFICATION_ID, N.GROUP_ID, N.MESSAGE_TYPE,N.MESSAGE_NAME,
        N.RECIPIENT_ROLE, N.STATUS, N.ACCESS_KEY, N.MAIL_STATUS,
        N.PRIORITY, N.BEGIN_DATE, N.END_DATE, N.DUE_DATE,
        N.RESPONDER, N.USER_COMMENT, N.CALLBACK, N.CONTEXT,
        N.ORIGINAL_RECIPIENT, N.FROM_USER, N.TO_USER, N.SUBJECT,
        N.LANGUAGE, N.MORE_INFO_ROLE
   from wf_notifications N, WF_ITEMS I
        where N.group_id in
        (select notification_id
         from wf_item_activity_statuses IAS
         where IAS.ITEM_TYPE = I.Item_type
         and IAS.ITEM_KEY = I.Item_key
         UNION ALL
         select notification_id
         from wf_item_activity_statuses_h IASH
         where IASH.ITEM_TYPE = I.Item_type
         and IASH.ITEM_KEY = I.Item_key);

/*
** WF_HA_NTFA_V for high availability replay
*/
create or replace force view WF_HA_NTFA_V 
(
ROW_ID,Item_type,Item_key,NOTIFICATION_ID,NAME,TEXT_VALUE,NUMBER_VALUE,
DATE_VALUE
)as
select  NA.ROWID ROW_ID, I.Item_type, I.Item_key,
        NA.NOTIFICATION_ID, NA.NAME,
        NA.TEXT_VALUE, NA.NUMBER_VALUE, NA.DATE_VALUE
   from wf_notification_attributes NA, wf_notifications N, WF_ITEMS I
   where NA.notification_id = N.notification_id
     and N.group_id in
        (select notification_id
         from wf_item_activity_statuses IAS
         where IAS.ITEM_TYPE = I.Item_type
         and IAS.ITEM_KEY = I.Item_key
         UNION ALL
         select notification_id
         from wf_item_activity_statuses_h IASH
         where IASH.ITEM_TYPE = I.Item_type
         and IASH.ITEM_KEY = I.Item_key);

commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de