[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select MA.DISPLAY_NAME,
MA.TYPE,
MA.FORMAT,
NA.TEXT_VALUE,
NA.NUMBER_VALUE,
NA.DATE_VALUE
into l_dispname, l_type, l_format, l_textv, l_numberv, l_datev
from WF_MESSAGE_ATTRIBUTES_VL MA,
WF_NOTIFICATION_ATTRIBUTES NA,
WF_NOTIFICATIONS N
where NA.NAME = l_attr
and NA.NOTIFICATION_ID = nid
and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
and N.MESSAGE_TYPE = MA.MESSAGE_TYPE
and N.MESSAGE_NAME = MA.MESSAGE_NAME
and MA.NAME = NA.NAME;
select MEANING
into l_text
from WF_LOOKUPS
where LOOKUP_TYPE = l_format
and LOOKUP_CODE = l_textv;
select IAS.NOTIFICATION_ID, IAS.ASSIGNED_USER, A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE, nvl(IAS.END_DATE, IAS.BEGIN_DATE) ACT_DATE, IAS.EXECUTION_TIME
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEM_TYPES IT,
WF_ITEMS I
where IAS.ITEM_TYPE = x_item_type
and IAS.ITEM_KEY = x_item_key
and IAS.PROCESS_ACTIVITY = x_actid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
and I.ITEM_TYPE = IT.NAME
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE;
select IAS.NOTIFICATION_ID, IAS.ASSIGNED_USER, A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE, nvl(IAS.END_DATE, IAS.BEGIN_DATE) ACT_DATE, IAS.EXECUTION_TIME
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEM_TYPES IT,
WF_ITEMS I
where IAS.ITEM_TYPE = x_item_type
and IAS.ITEM_KEY = x_item_key
and IAS.PROCESS_ACTIVITY = x_actid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and I.BEGIN_DATE between A.BEGIN_DATE and nvl(A.END_DATE, I.BEGIN_DATE)
and I.ITEM_TYPE = IT.NAME
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
order by IAS.BEGIN_DATE desc , IAS.EXECUTION_TIME desc;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
into l_itype, l_ikey, l_actid
from WF_ITEM_ACTIVITY_STATUSES
where notification_id = nid;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
into l_itype, l_ikey, l_actid
from WF_ITEM_ACTIVITY_STATUSES_H
where notification_id = nid;
select OWNER_ROLE, BEGIN_DATE
into l_owner_role, l_begin_date
from WF_ITEMS
where ITEM_TYPE = l_itype
and ITEM_KEY = l_ikey;
select NAME
into user_name
from (select wlr.NAME,
decode (wlr.ORIG_SYSTEM, 'PER', 1, 'FND_USR', 2, 3) ORIG_SYS_ORDER
from WF_LOCAL_ROLES wlr, WF_USER_ROLE_ASSIGNMENTS_V wurav
where wurav.USER_NAME = wlr.NAME and
wurav.ROLE_NAME = preferred_name and
upper(wlr.EMAIL_ADDRESS)=l_email
order by ORIG_SYS_ORDER)
where rownum<2;
select 1 into dummy from sys.dual where exists
(select null
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid);
insert into WF_NOTIFICATION_ATTRIBUTES (
NOTIFICATION_ID,
NAME,
TEXT_VALUE,
NUMBER_VALUE,
DATE_VALUE
) values (
nid,
aname,
'',
'',
''
);
select WMA.TYPE, WMA.FORMAT
into atype, format
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES WMA
where WNA.NOTIFICATION_ID = nid
and WNA.NAME = aname
and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WNA.NAME = WMA.NAME;
update WF_NOTIFICATION_ATTRIBUTES
set NUMBER_VALUE = decode(format,
'', to_number(avalue),
to_number(avalue, format))
where NOTIFICATION_ID = nid
and NAME = aname;
update WF_NOTIFICATION_ATTRIBUTES
set DATE_VALUE = decode(format,
'',to_date(avalue,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')),
to_date(avalue, format))
where NOTIFICATION_ID = nid
and NAME = aname;
update WF_NOTIFICATION_ATTRIBUTES
set TEXT_VALUE = decode(format,
'', avalue,
substrb(avalue, 1, to_number(format)))
where NOTIFICATION_ID = nid
and NAME = aname;
SELECT name
INTO rname
FROM wf_role_lov_vl
WHERE upper(display_name) = upper(avalue)
AND rownum = 1;
update WF_NOTIFICATION_ATTRIBUTES
set TEXT_VALUE = rname
where NOTIFICATION_ID = nid
and NAME = aname;
update WF_NOTIFICATION_ATTRIBUTES
set TEXT_VALUE = avalue
where NOTIFICATION_ID = nid
and NAME = aname;
select WN.RECIPIENT_ROLE
into l_recipient_role
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid ;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
update WF_NOTIFICATION_ATTRIBUTES
set NUMBER_VALUE = avalue
where NOTIFICATION_ID = nid and NAME = aname;
update WF_NOTIFICATION_ATTRIBUTES
set DATE_VALUE = avalue
where NOTIFICATION_ID = nid and NAME = aname;
select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.NAME = WNA.NAME
order by decode(wma.type,'URL',length(WNA.NAME),length(WNA.NAME)+1000) desc;
select MEANING
into value
from WF_LOOKUPS
where LOOKUP_TYPE = not_attr_row.format
and LOOKUP_CODE = not_attr_row.text_value;
select WR.DISPLAY_NAME
into value
from WF_ROLES WR
where WR.NAME = not_attr_row.text_value
and WR.ORIG_SYSTEM NOT IN ('HZ_PARTY','POS','ENG_LIST','AMV_CHN',
'HZ_GROUP','CUST_CONT');
select WR.DISPLAY_NAME
into value
from WF_ROLES WR
where WR.ORIG_SYSTEM = substr(not_attr_row.text_value, 1, colon-1)
and WR.ORIG_SYSTEM_ID = substr(not_attr_row.text_value, colon+1)
and WR.NAME = not_attr_row.text_value;
select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.NAME = WNA.NAME
order by decode(wma.type,'URL',length(WNA.NAME),length(WNA.NAME)+1000) desc;
select MEANING
into value
from WF_LOOKUPS
where LOOKUP_TYPE = not_attr_row.format
and LOOKUP_CODE = not_attr_row.text_value;
select WMA.TYPE, WMA.SUBTYPE, WMA.FORMAT
into atype, subtype, format
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES WMA
where WNA.NOTIFICATION_ID = nid
and WNA.NAME = aname
and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.NAME = WNA.NAME;
select WMA.TYPE, WMA.FORMAT
into atype, format
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES WMA
where WNA.NOTIFICATION_ID = nid
and WNA.NAME = aname
and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WNA.NAME = WMA.NAME;
select decode(format,
'', to_char(WNA.NUMBER_VALUE),
to_char(WNA.NUMBER_VALUE, format))
into lvalue
from WF_NOTIFICATION_ATTRIBUTES WNA
where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
select DATE_VALUE into l_valDate
from WF_NOTIFICATION_ATTRIBUTES WNA
where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
select WNA.TEXT_VALUE
into lvalue
from WF_NOTIFICATION_ATTRIBUTES WNA
where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
select WNA.NUMBER_VALUE
into lvalue
from WF_NOTIFICATION_ATTRIBUTES WNA
where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
select WNA.DATE_VALUE
into lvalue
from WF_NOTIFICATION_ATTRIBUTES WNA
where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
select WMATL.DISPLAY_NAME, NVL(WMA.FORMAT, '_blank')
into display_name, target
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_TL WMATL, WF_MESSAGE_ATTRIBUTES WMA
where WNA.NOTIFICATION_ID = nid
and WNA.NAME = aname
and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
and WN.MESSAGE_NAME = WMATL.MESSAGE_NAME
and WN.MESSAGE_TYPE = WMATL.MESSAGE_TYPE
and WNA.NAME = WMATL.NAME
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WNA.NAME = WMA.NAME
and WMATL.LANGUAGE = userenv('LANG');
select WM.SUBJECT
into local_subject
from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
where N.NOTIFICATION_ID = nid
and N.MESSAGE_NAME = WM.NAME
and N.MESSAGE_TYPE = WM.TYPE;
SELECT count(1)
FROM wf_comments
WHERE action_type in ('REASSIGN', 'QA')
AND notification_id = nid
AND rownum = 1;
select WM.BODY, WM.HTML_BODY
into local_body, local_html_body
from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
where N.NOTIFICATION_ID = nid
and N.MESSAGE_NAME = WM.NAME
and N.MESSAGE_TYPE = WM.TYPE;
SELECT count(1)
INTO l_resp_cnt
FROM wf_message_attributes wma,
wf_notifications wn
WHERE wn.notification_id = nid
AND wma.message_type = wn.message_type
AND wma.message_name = wn.message_name
AND wma.subtype = 'RESPOND'
AND rownum = 1;
select WM.BODY
into local_body
from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
where N.NOTIFICATION_ID = nid
and N.MESSAGE_NAME = WM.NAME
and N.MESSAGE_TYPE = WM.TYPE;
select
N.RECIPIENT_ROLE,
N.MESSAGE_TYPE,
N.MESSAGE_NAME,
N.PRIORITY,
N.DUE_DATE,
N.STATUS
into
GetInfo.role,
GetInfo.message_type,
GetInfo.message_name,
GetInfo.priority,
GetInfo.due_date,
GetInfo.status
from WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = nid;
select WN.RESPONDER
into respbuf
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid;
select recipient_role
into uname
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
and ACCESS_KEY = nkey;
select WRR.RULE_ID, WRR.ACTION, WRR.ACTION_ARGUMENT, WRR.RULE_COMMENT
from WF_ROUTING_RULES WRR
where WRR.ROLE = recip
and sysdate between nvl(WRR.BEGIN_DATE, sysdate-1) and
nvl(WRR.END_DATE, sysdate+1)
and nvl(WRR.MESSAGE_TYPE, msgtype) = msgtype
and nvl(WRR.MESSAGE_NAME, msgname) = msgname
order by WRR.MESSAGE_TYPE, WRR.MESSAGE_NAME;
select WRRA.NAME, WRRA.TEXT_VALUE, WRRA.NUMBER_VALUE, WRRA.DATE_VALUE,
WMA.TYPE
from WF_ROUTING_RULE_ATTRIBUTES WRRA, WF_ROUTING_RULES WRR,
WF_MESSAGE_ATTRIBUTES WMA
where WRRA.RULE_ID = ruleid
and WRRA.RULE_ID = WRR.RULE_ID
and WRR.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WRR.MESSAGE_NAME = WMA.MESSAGE_NAME
and WRRA.NAME = WMA.NAME;
select WN.RECIPIENT_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.CONTEXT
into recip, msgtype, msgname, l_context
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid;
select OWNER_ROLE into l_wf_owner
from WF_ITEMS
where ITEM_TYPE=msgtype
and ITEM_KEY=l_item_key;
update WF_NOTIFICATIONS set
USER_COMMENT = substr(USER_COMMENT||decode(nvl(USER_COMMENT,'T'),
'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
where NOTIFICATION_ID = nid;
wf_core.context('Wf_Notification', 'Route (update comment)',to_char(nid));
update WF_NOTIFICATIONS set
USER_COMMENT = substr(USER_COMMENT||
decode(nvl(USER_COMMENT,'T'),
'T', null, wf_core.newline)||
Route.newcomment, 1, 4000)
where NOTIFICATION_ID = nid;
wf_core.context('Wf_Notification', 'Route (update comment)',
to_char(nid));
SELECT count(1)
INTO l_count
FROM wf_item_activity_statuses_h
WHERE item_type = l_item_type
AND item_key = l_item_key
AND process_activity = l_actid
AND rownum = 1;
select to_number(
substr(wnrm.column_name,instr(wnrm.column_name,'UTE')+3)) idx,
wnrm.column_name,
wna.text_value,
wna.number_value,
wna.date_value
from wf_ntf_rules wnr,
wf_ntf_rule_maps wnrm,
wf_ntf_rule_criteria wnrc,
wf_notification_attributes wna,
wf_notifications wn
where wnr.rule_name = wnrc.rule_name
and wnrc.message_type = wn.message_type
and wnr.status = 'ENABLED'
and wnrc.rule_name = wnrm.rule_name
and wnrm.attribute_name = wna.name
and wna.notification_id = wn.notification_id
and wn.notification_id = p_nid
order by wnr.phase;
update WF_NOTIFICATIONS
set
PROTECTED_TEXT_ATTRIBUTE1 = pta(1)
,PROTECTED_TEXT_ATTRIBUTE2 = pta(2)
,PROTECTED_TEXT_ATTRIBUTE3 = pta(3)
,PROTECTED_TEXT_ATTRIBUTE4 = pta(4)
,PROTECTED_TEXT_ATTRIBUTE5 = pta(5)
,PROTECTED_TEXT_ATTRIBUTE6 = pta(6)
,PROTECTED_TEXT_ATTRIBUTE7 = pta(7)
,PROTECTED_TEXT_ATTRIBUTE8 = pta(8)
,PROTECTED_TEXT_ATTRIBUTE9 = pta(9)
,PROTECTED_TEXT_ATTRIBUTE10 = pta(10)
,PROTECTED_FORM_ATTRIBUTE1 = pfa(1)
,PROTECTED_FORM_ATTRIBUTE2 = pfa(2)
,PROTECTED_FORM_ATTRIBUTE3 = pfa(3)
,PROTECTED_FORM_ATTRIBUTE4 = pfa(4)
,PROTECTED_FORM_ATTRIBUTE5 = pfa(5)
,PROTECTED_URL_ATTRIBUTE1 = pua(1)
,PROTECTED_URL_ATTRIBUTE2 = pua(2)
,PROTECTED_URL_ATTRIBUTE3 = pua(3)
,PROTECTED_URL_ATTRIBUTE4 = pua(4)
,PROTECTED_URL_ATTRIBUTE5 = pua(5)
,PROTECTED_DATE_ATTRIBUTE1 = pda(1)
,PROTECTED_DATE_ATTRIBUTE2 = pda(2)
,PROTECTED_DATE_ATTRIBUTE3 = pda(3)
,PROTECTED_DATE_ATTRIBUTE4 = pda(4)
,PROTECTED_DATE_ATTRIBUTE5 = pda(5)
,PROTECTED_NUMBER_ATTRIBUTE1= pna(1)
,PROTECTED_NUMBER_ATTRIBUTE2= pna(2)
,PROTECTED_NUMBER_ATTRIBUTE3= pna(3)
,PROTECTED_NUMBER_ATTRIBUTE4= pna(4)
,PROTECTED_NUMBER_ATTRIBUTE5= pna(5)
,TEXT_ATTRIBUTE1 = ta(1)
,TEXT_ATTRIBUTE2 = ta(2)
,TEXT_ATTRIBUTE3 = ta(3)
,TEXT_ATTRIBUTE4 = ta(4)
,TEXT_ATTRIBUTE5 = ta(5)
,TEXT_ATTRIBUTE6 = ta(6)
,TEXT_ATTRIBUTE7 = ta(7)
,TEXT_ATTRIBUTE8 = ta(8)
,TEXT_ATTRIBUTE9 = ta(9)
,TEXT_ATTRIBUTE10 = ta(10)
,FORM_ATTRIBUTE1 = fa(1)
,FORM_ATTRIBUTE2 = fa(2)
,FORM_ATTRIBUTE3 = fa(3)
,FORM_ATTRIBUTE4 = fa(4)
,FORM_ATTRIBUTE5 = fa(5)
,URL_ATTRIBUTE1 = ua(1)
,URL_ATTRIBUTE2 = ua(2)
,URL_ATTRIBUTE3 = ua(3)
,URL_ATTRIBUTE4 = ua(4)
,URL_ATTRIBUTE5 = ua(5)
,DATE_ATTRIBUTE1 = da(1)
,DATE_ATTRIBUTE2 = da(2)
,DATE_ATTRIBUTE3 = da(3)
,DATE_ATTRIBUTE4 = da(4)
,DATE_ATTRIBUTE5 = da(5)
,NUMBER_ATTRIBUTE1= na(1)
,NUMBER_ATTRIBUTE2= na(2)
,NUMBER_ATTRIBUTE3= na(3)
,NUMBER_ATTRIBUTE4= na(4)
,NUMBER_ATTRIBUTE5= na(5)
,ITEM_KEY = p_item_key
,USER_KEY = p_user_key
where notification_id = p_nid;
select wi.item_key, wi.user_key, wn.notification_id
from wf_items wi,
wf_item_activity_statuses wias,
wf_notifications wn
where wi.item_key = wias.item_key
and wi.item_type = wias.item_type
and wias.notification_id = wn.group_id
and (wn.message_type = p_item_type or p_item_type is null)
and wn.status = nvl(p_status, 'OPEN')
and (wn.recipient_role = p_recipient or p_recipient is null)
order by wi.item_type;
select NAME, TYPE, SUBTYPE, VALUE_TYPE,
TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT
from WF_MESSAGE_ATTRIBUTES
where MESSAGE_TYPE = msg_type
and MESSAGE_NAME = msg_name;
select WF_NOTIFICATIONS_S.NEXTVAL
into nid
from SYS.DUAL;
insert into WF_NOTIFICATIONS (
NOTIFICATION_ID,
GROUP_ID,
MESSAGE_TYPE,
MESSAGE_NAME,
RECIPIENT_ROLE,
ORIGINAL_RECIPIENT,
STATUS,
ACCESS_KEY,
MAIL_STATUS,
PRIORITY,
BEGIN_DATE,
SENT_DATE,
END_DATE,
DUE_DATE,
-- USER_COMMENT,
CALLBACK,
CONTEXT
) select
sendsingle.nid,
nvl(sendsingle.group_id, sendsingle.nid),
sendsingle.msg_type,
sendsingle.msg_name,
sendsingle.role,
sendsingle.role,
'OPEN',
wf_core.random,
decode(sendsingle.mailpref, 'QUERY', '',
'SUMMARY', '',
'SUMHTML', '',
'DISABLED', 'FAILED',
null, '', 'MAIL'),
nvl(SendSingle.priority, WM.DEFAULT_PRIORITY),
sysdate,
sysdate,
null,
sendsingle.due_date,
-- sendsingle.send_comment,
sendsingle.callback,
sendsingle.context
from WF_MESSAGES WM
where WM.TYPE = sendsingle.msg_type
and WM.NAME = sendsingle.msg_name;
insert into WF_NOTIFICATION_ATTRIBUTES (
NOTIFICATION_ID,
NAME,
TEXT_VALUE,
NUMBER_VALUE,
DATE_VALUE,
EVENT_VALUE
) values (
nid,
attr_name,
attr_tvalue,
attr_nvalue,
attr_dvalue,
attr_evalue
);
select RECIPIENT_ROLE
into l_new_rcpt_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID=nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select 1 into dummy from sys.dual where exists
(select null
from WF_MESSAGES M
where M.TYPE = msg_type
and M.NAME = msg_name);
SELECT max(wn.notification_id)
INTO prev_nid
FROM wf_notifications wn,
wf_comments wc
WHERE
EXISTS ( SELECT 'x' -- 8554209
FROM wf_item_activity_statuses_h wiash
WHERE wiash.notification_id= wn.notification_id
AND wiash.item_type = wn.message_type
AND wiash.item_type = itemtype
AND wiash.item_key = itemkey
AND wiash.process_activity = actid)
AND wn.status = 'CLOSED'
AND wn.notification_id = wc.notification_id
AND wc.to_role = 'WF_SYSTEM'
AND wc.action_type = 'RESPOND';
UPDATE wf_comments
SET to_role = role,
to_user = nvl(Wf_Directory.GetRoleDisplayname(role), role)
WHERE notification_id = prev_nid
AND to_role = 'WF_SYSTEM'
AND action_type = 'RESPOND';
select WUR.USER_NAME
from WF_USER_ROLES WUR
where WUR.ROLE_ORIG_SYSTEM = rorig_system
and WUR.ROLE_ORIG_SYSTEM_ID = rorig_system_id
and WUR.ROLE_NAME = role;
select 1 into dummy from sys.dual where exists
(select null
from WF_MESSAGES M
where M.TYPE = msg_type
and M.NAME = msg_name);
select WN.STATUS, WN.CALLBACK, WN.CONTEXT, -- , WN.USER_COMMENT
WN.RECIPIENT_ROLE, WN.ORIGINAL_RECIPIENT,WN.MORE_INFO_ROLE,
WN.FROM_ROLE
, wn.message_type, wn.message_name -- <7641725>
into status, cb, context, old_role, -- newcomment,
old_origrole,l_more_info_role,l_from_role
, l_msgType, l_msgName
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid
for update nowait;
select OWNER_ROLE into l_wf_owner
from WF_ITEMS
where ITEM_TYPE=l_item_type
and ITEM_KEY=l_item_key;
update WF_NOTIFICATIONS set
RECIPIENT_ROLE = ForwardInternal.new_role,
ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
'TRANSFER', ForwardInternal.new_role,
ORIGINAL_RECIPIENT),
-- USER_COMMENT = ForwardInternal.newcomment,
MAIL_STATUS = decode(ForwardInternal.mailpref,
'QUERY', '',
'SUMMARY', '',
'SUMHTML', '',
'DISABLED', 'FAILED',
null, '', 'MAIL'),
ACCESS_KEY = wf_core.random,
FROM_ROLE = l_from_role,
MORE_INFO_ROLE = l_more_info_role,
SENT_DATE = SYSDATE
where NOTIFICATION_ID = nid;
update WF_NOTIFICATIONS set
RECIPIENT_ROLE = ForwardInternal.new_role,
ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
'TRANSFER', ForwardInternal.new_role,
ORIGINAL_RECIPIENT),
-- USER_COMMENT = ForwardInternal.newcomment,
MAIL_STATUS = decode(ForwardInternal.mailpref,
'QUERY', '',
'SUMMARY', '',
'SUMHTML', '',
'DISABLED', 'FAILED',
null, '', 'MAIL'),
FROM_ROLE = l_from_role,
MORE_INFO_ROLE = l_more_info_role,
SENT_DATE = SYSDATE
where NOTIFICATION_ID = nid;
select WN.RECIPIENT_ROLE
into l_recipient_role
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select WN.STATUS, WN.CALLBACK, WN.CONTEXT, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.LANGUAGE
into status, cb, context, l_msg_type, l_msg_name, l_language
from WF_NOTIFICATIONS WN
where WN.NOTIFICATION_ID = nid
for update nowait;
select 1 into dummy from sys.dual where exists
(select NULL
from WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES WMA
where WN.NOTIFICATION_ID = nid
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WMA.SUBTYPE = 'RESPOND');
update WF_NOTIFICATIONS set
STATUS = 'CANCELED',
END_DATE = sysdate,
-- USER_COMMENT = CancelSingle.newcomment,
MAIL_STATUS = decode(MAIL_STATUS,
'ERROR', 'ERROR',
-- if this was never sent, dont bother sending cancelation
'MAIL', '',
decode(CancelSingle.mailpref,
'QUERY', '',
'SUMMARY', '',
'SUMHTML', '',
'DISABLED', 'FAILED',
null, '', l_mail))
where NOTIFICATION_ID = nid;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select STATUS, RECIPIENT_ROLE
into status, role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
for update nowait;
select NOTIFICATION_ID, RECIPIENT_ROLE
from WF_NOTIFICATIONS
where GROUP_ID = gid
and status = 'OPEN'
for update nowait;
select N.CALLBACK, N.CONTEXT
into TestContext.callback, TestContext.context
from WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = nid;
select count(*)
into l_total_pop
from wf_notifications
where group_id = Gid;
select count(*)
into l_total_voted
from wf_notifications
where group_id = Gid
and status = 'CLOSED';
select count(*)
into l_code_count
from wf_notifications wfn,
wf_notification_attributes wfna
where wfn.group_id = Gid
and wfn.notification_id = wfna.notification_id
and wfn.status = 'CLOSED'
and wfna.name = 'RESULT'
and wfna.text_value = ResultCode;
select 1
into dummy
from sys.dual
where exists ( select null
from wf_notifications
where group_id = Gid
and status = 'OPEN'
);
select count(1)
into ncount
from WF_NOTIFICATIONS WN,
(select WUR.ROLE_NAME
from WF_USER_ROLES WUR
where WUR.USER_NAME = WorkCount.username
and WUR.USER_ORIG_SYSTEM = l_orig_system
and WUR.USER_ORIG_SYSTEM_ID = l_orig_system_id
) wur
where ( (WN.MORE_INFO_ROLE is null
and WN.RECIPIENT_ROLE = WUR.ROLE_NAME)
or (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) )
and WN.STATUS = 'OPEN';
select MA.NAME
from WF_NOTIFICATION_ATTRIBUTES NA,
WF_MESSAGE_ATTRIBUTES_VL MA,
WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = mnid
and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
and MA.MESSAGE_NAME = N.MESSAGE_NAME
and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
and MA.NAME = NA.NAME
and MA.SUBTYPE = 'RESPOND';
select N.STATUS, N.LANGUAGE
into close.status, l_language
from WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = nid
for update nowait;
update WF_NOTIFICATIONS
set STATUS = 'CLOSED',
MAIL_STATUS = NULL,
END_DATE = sysdate,
RESPONDER = close.responder
where NOTIFICATION_ID = nid;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select WMA.NAME, WMA.DISPLAY_NAME, WMA.TYPE
from WF_MESSAGE_ATTRIBUTES_VL WMA
where WMA.MESSAGE_TYPE = c_message_type
and WMA.MESSAGE_NAME = c_message_name
order by length(WMA.NAME) desc;
SELECT SUBJECT
INTO local_text
FROM wf_messages_vl
WHERE type = message_type
AND name = message_name;
select WMA.NAME, WMA.DISPLAY_NAME, WMA.TYPE
from WF_MESSAGE_ATTRIBUTES_VL WMA
where WMA.MESSAGE_TYPE = c_message_type
and WMA.MESSAGE_NAME = c_message_name
order by length(WMA.NAME) desc;
SELECT SUBJECT
INTO local_text
FROM wf_messages_vl
WHERE type = message_type
AND name = message_name;
select NAME into aname from
(select WMA.NAME
from WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES WMA,
WF_NOTIFICATION_ATTRIBUTES NA
where WN.NOTIFICATION_ID = nid
and wn.notification_id = na.notification_id
and wma.name = na.name
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.TYPE = 'DOCUMENT'
and instr( upper(astring) ,wma.name) = 1
and upper(na.text_value) like 'PLSQLCLOB:%'
order by length(wma.name) desc)
where rownum=1;
role_info_tbl.DELETE;
select NLS_LANGUAGE
into l_language
from WF_LANGUAGES
where CODE = langcode;
select RECIPIENT_ROLE, FROM_ROLE
into l_user, l_from_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
update WF_NOTIFICATIONS
set FROM_USER = l_from_user,
FROM_ROLE = nvl(l_from_role,FROM_ROLE),
TO_USER = l_to_user,
SUBJECT = l_subject,
LANGUAGE = userenv('LANG')
where NOTIFICATION_ID = nid;
select WN.NOTIFICATION_ID
from WF_NOTIFICATIONS WN
where MESSAGE_TYPE like xitemtype
and MESSAGE_NAME like xmessageName
and BEGIN_DATE<=begindate
and STATUS = 'OPEN'
and not exists (
select NULL
from WF_MESSAGE_ATTRIBUTES WMA
where WMA.MESSAGE_TYPE = WN.MESSAGE_TYPE
and WMA.MESSAGE_NAME = WN.MESSAGE_NAME
and WMA.SUBTYPE = 'RESPOND');
procedure UpdateInfo(nid in number,
username in varchar2,
comment in varchar2,
wl_user in varchar2,
action_source in varchar2,
cnt in number)
is
resource_busy exception;
g_context_user := UpdateInfo.wl_user;
myusername := UpdateInfo.wl_user;
g_context_user_comment := updateinfo.comment;
SELECT callback , context ,RECIPIENT_ROLE, ORIGINAL_RECIPIENT,
MORE_INFO_ROLE ,from_role, message_type, message_name
into cb, context,l_recip_role , l_orig_recip_role,
l_more_info_role, l_from_role, l_messageType, l_messageName
FROM wf_notifications
WHERE notification_id = nid;
select MORE_INFO_ROLE
into l_from_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
for update nowait;
wf_core.raise('WFNTF_BEING_UPDATED');
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = username,
FROM_USER = mydispname,
FROM_ROLE = myusername,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (mailpref, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = nid;
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = username,
FROM_USER = TO_USER,
FROM_ROLE = RECIPIENT_ROLE,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (mailpref, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = nid;
select MORE_INFO_ROLE,Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), RECIPIENT_ROLE,FROM_ROLE
into l_from_role, replyby, recipient_role,l_question_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
and MORE_INFO_ROLE is not null
for update nowait;
wf_core.raise('WFNTF_BEING_UPDATED');
update WF_NOTIFICATIONS
set FROM_USER = replyby,
FROM_ROLE = l_from_role,
MORE_INFO_ROLE = null,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (mailpref, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
Wf_Core.Context('Wf_Notification', 'UpdateInfo', to_char(nid), username, wl_user, action_source);
end UpdateInfo;
SELECT callback, context, recipient_role, original_recipient,
more_info_role ,from_role, message_type, message_name
into l_callback_function, l_context,l_recip_role , l_orig_recip_role,
l_more_info_role, l_from_role, l_messageType, l_messageName
FROM wf_notifications
WHERE notification_id = p_nid;
select MORE_INFO_ROLE
into l_from_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = p_nid
for update nowait;
wf_core.raise('WFNTF_BEING_UPDATED');
-- ### This notification is being updated currently, please
-- ### try again in a brief moment.
end;
-- if there is a valid session, then we can update the FROM_ROLE
-- and FROM_USER accurately.
-- If there is a transfer action due to a vacation rule
-- then we set the FROM_ROLE as the user for which there is a
--routing rule
if (p_routing_rule_user is not null) then
l_routing_rule_user_display := Wf_Directory.GetRoleDisplayName(p_routing_rule_user);
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = p_new_user,
FROM_USER = l_routing_rule_user_display,
FROM_ROLE = p_routing_rule_user,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (l_mail_preference, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = p_nid;
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = p_new_user,
FROM_USER = l_session_user_display,
FROM_ROLE = l_session_user,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (l_mail_preference, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = p_nid;
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = p_new_user,
FROM_USER = TO_USER,
FROM_ROLE = RECIPIENT_ROLE,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (l_mail_preference, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = p_nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select action, action_argument
from wf_routing_rules
where role = l_more_info_role
and nvl(message_type, l_messageType) = l_messageType
and nvl(message_name,l_messageName) = l_messageName
and sysdate between nvl(begin_date, sysdate-1) and
nvl(end_date, sysdate+1);
select recipient_role, more_info_role, message_type, message_name
into l_recip_role , l_more_info_role, l_messageType, l_messageName
FROM wf_notifications
WHERE notification_id = p_nid;
update WF_NOTIFICATIONS
set FROM_USER = l_more_info_role_display,
FROM_ROLE = l_more_info_role,
MORE_INFO_ROLE = null,
SENT_DATE = SYSDATE,
MAIL_STATUS = decode (l_mail_preference, 'QUERY', '',
'SUMMARY', '',
'SUMHTML','',
'DISABLED', 'FAILED',
null, '', 'MAIL')
where NOTIFICATION_ID = p_nid;
select ITEM_TYPE, ITEM_KEY
into itype, ikey
from WF_ITEM_ACTIVITY_STATUSES
where NOTIFICATION_ID = nid;
select ITEM_TYPE, ITEM_KEY
into itype, ikey
from WF_ITEM_ACTIVITY_STATUSES_H
where NOTIFICATION_ID = nid;
select NULL, '#SYNCH'
into itype, ikey
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid;
select 1 into ans
from WF_ITEMS
where ITEM_TYPE = itype
and ITEM_KEY = ikey
and OWNER_ROLE = IsValidInfoRole.username;
select 1 into ans
from WF_NOTIFICATIONS
where IsValidInfoRole.username in (RECIPIENT_ROLE, ORIGINAL_RECIPIENT)
and NOTIFICATION_ID = nid;
select 1 into ans
from (
select /*+ leading(grp_id_view) */
RECIPIENT_ROLE , ORIGINAL_RECIPIENT
from WF_NOTIFICATIONS a ,
( select notification_id group_id
from WF_ITEM_ACTIVITY_STATUSES
where item_type = itype
and item_key = ikey
union all
select notification_id group_id
from WF_ITEM_ACTIVITY_STATUSES_H
where item_type = itype
and item_key = ikey
) grp_id_view
where grp_id_view.group_id = a.group_id
) recipient_view
where (recipient_view.RECIPIENT_ROLE = IsValidInfoRole.username
or recipient_view.ORIGINAL_RECIPIENT = IsValidInfoRole.username)
and rownum < 2;
procedure UpdateInfo2(nid in number,
username in varchar2,
from_email in varchar2,
comment in varchar2)
is
resource_busy exception;
cnt number := 1; --Used to call UpdateInfo() for the first time, that means one time.
select action, action_argument
from wf_routing_rules
where role = username
and nvl(message_type, l_messageType) = l_messageType
and nvl(message_name,l_messageName) = l_messageName
and sysdate between nvl(begin_date, sysdate-1) and
nvl(end_date, sysdate+1);
'wf.plsql.WF_NOTIFICATION.UpdateInfo2.Begin',
'NID: '||to_char(nid) ||', Username: '||username||
' From: '||from_email);
SELECT callback, context, recipient_role, original_recipient,
more_info_role, from_role, status, message_type, message_name
INTO cb, context, l_recip_role, l_orig_recip_role,
l_more_info_role, l_from_role, l_stat, l_messageType, l_messageName
FROM wf_notifications
WHERE notification_id = nid;
'wf.plsql.WF_NOTIFICATION.UpdateInfo2.not_open',
'Notification '||to_char(nid)||' is not OPEN. Returning.');
'wf.plsql.WF_NOTIFICATION.UpdateInfo2.got_user',
'Email: '||from_email||' User: '||myusername||' DispName: '||mydispname);
g_context_user_comment := updateinfo2.comment;
SELECT name
INTO l_username
FROM wf_role_lov_vl
WHERE upper(display_name) = upper(username)
AND rownum = 1;
UpdateInfo(nid,l_newRole,g_context_user_comment,myusername,null, cnt);
select MORE_INFO_ROLE, MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID
into l_from_role, l_messageType, l_messageName, l_groupId
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
for update nowait;
wf_core.raise('WFNTF_BEING_UPDATED');
'wf.plsql.WF_NOTIFICATION.UpdateInfo2.question',
'Updating QUESTION');
update WF_NOTIFICATIONS
set MORE_INFO_ROLE = username,
FROM_USER = mydispname,
FROM_ROLE = myusername
where NOTIFICATION_ID = nid;
update WF_NOTIFICATIONS
set MAIL_STATUS = 'MAIL',
MORE_INFO_ROLE = l_username,
FROM_USER = TO_USER,
FROM_ROLE = RECIPIENT_ROLE,
SENT_DATE = SYSDATE
where NOTIFICATION_ID = nid;
select MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role
into l_from_role, replyby, l_messageType, l_messageName, l_groupId, l_question_role
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
and MORE_INFO_ROLE is not null
for update nowait;
wf_core.raise('WFNTF_BEING_UPDATED');
'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
'Updating ANSWER');
update WF_NOTIFICATIONS
set MAIL_STATUS = 'MAIL',
FROM_USER = replyby,
FROM_ROLE = l_from_role,
MORE_INFO_ROLE = null,
SENT_DATE = SYSDATE
where NOTIFICATION_ID = nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
Wf_Core.Context('Wf_Notification', 'UpdateInfo2', to_char(nid), username, from_email);
end UpdateInfo2;
procedure UpdateInfoGuest(nid in number,
moreinforesponder in varchar2 default null,
moreinfoanswer in varchar2 default null)
is
resource_busy exception;
wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
'NID: '||to_char(nid));
select ORIGINAL_RECIPIENT, RECIPIENT_ROLE, MORE_INFO_ROLE,
Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role, callback, context
into l_orig_recip_role, l_recipient_role, l_from_role,
replyby, l_messageType, l_messageName, l_groupId, l_question_role, cb, context
from WF_NOTIFICATIONS
where NOTIFICATION_ID = nid
and MORE_INFO_ROLE is not null
for update nowait;
g_context_user := updateinfoguest.moreinforesponder;
g_context_user_comment := updateinfoguest.moreinfoanswer;
wf_core.raise('WFNTF_BEING_UPDATED');
wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
'Updating ANSWER');
update WF_NOTIFICATIONS
set MAIL_STATUS = 'MAIL',
FROM_USER = moreinforesponder,
FROM_ROLE = moreinforesponder,
MORE_INFO_ROLE = null,
SENT_DATE = SYSDATE
where NOTIFICATION_ID = nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
Wf_Core.Context('Wf_Notification', 'UpdateInfoGuest', to_char(nid), moreinforesponder);
end UpdateInfoGuest;
SELECT user_comment
FROM wf_comments
WHERE notification_id = nid
AND action in ('QUESTION', 'QUESTION_WA', 'QUESTION_RULE')
ORDER BY comment_date desc;
select rownum H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION,
H_COMMENT, H_ACTION_DATE from
(select H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION,
H_COMMENT, H_ACTION_DATE from
(select
99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
l_wf_system H_TO_USER,
A.RESULT_TYPE H_ACTION_TYPE,
IAS.ACTIVITY_RESULT_CODE H_ACTION,
'#WF_NOTE#' H_COMMENT,
nvl(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and IAS.ACTIVITY_RESULT_CODE IS NOT NULL
and IAS.ACTIVITY_RESULT_CODE not in( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
and I.BEGIN_DATE between A.BEGIN_DATE
and nvl(A.END_DATE, I.BEGIN_DATE)
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
union all
select
99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
l_wf_system H_TO_USER,
A.RESULT_TYPE H_ACTION_TYPE,
IAS.ACTIVITY_RESULT_CODE H_ACTION,
'#WF_NOTE#' H_COMMENT,
nvl(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and IAS.ACTIVITY_RESULT_CODE IS NOT NULL
and IAS.ACTIVITY_RESULT_CODE not in( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
and I.BEGIN_DATE between A.BEGIN_DATE
and nvl(A.END_DATE, I.BEGIN_DATE)
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
union all
select C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
'#WF_COMMENTS#' H_ACTION_TYPE,
C.ACTION H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
and C.ACTION not in ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
union all
select C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
'#WF_COMMENTS#' H_ACTION_TYPE,
C.ACTION H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
and C.ACTION not in ('RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
)
order by H_ACTION_DATE, H_NOTIFICATION_ID, H_SEQUENCE
);
select rownum H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION,
H_COMMENT, H_ACTION_DATE from
(select H_SEQUENCE, H_NOTIFICATION_ID, H_FROM_USER, H_TO_USER, H_ACTION_TYPE, H_ACTION,
H_COMMENT, H_ACTION_DATE from
(select C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
C.ACTION_TYPE H_ACTION_TYPE,
C.ACTION H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
and C.ACTION_TYPE in ('REASSIGN', 'QA')
union all
select C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
C.ACTION_TYPE H_ACTION_TYPE,
C.ACTION H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C
where IAS.ITEM_TYPE = l_item_type
and IAS.ITEM_KEY = l_item_key
and IAS.PROCESS_ACTIVITY = l_actid
and IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
and C.ACTION_TYPE in ('REASSIGN', 'QA')
)
order by H_ACTION_DATE, H_NOTIFICATION_ID, H_SEQUENCE
);
SELECT item_type, item_key, process_activity
INTO l_item_type, l_item_key, l_actid
FROM wf_item_activity_statuses
WHERE notification_id = p_nid;
SELECT item_type, item_key, process_activity
INTO l_item_type, l_item_key, l_actid
FROM wf_item_activity_statuses_h
WHERE notification_id = p_nid;
SELECT text_value
INTO l_note
FROM wf_notification_attributes
WHERE notification_id = l_comm_rec.h_notification_id
AND name = 'WF_NOTE';
select NAME into aname from
(select WMA.NAME
from WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES WMA,
WF_NOTIFICATION_ATTRIBUTES NA
where WN.NOTIFICATION_ID = nid
and wn.notification_id = na.notification_id
and wma.name = na.name
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.TYPE = 'DOCUMENT'
and instr( upper(astring) ,wma.name) = 1
and upper(na.text_value) like 'PLSQLBLOB:%'
order by length(wma.name) desc)
where rownum=1;
select WNA.NAME, WNA.TEXT_VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = nid
and WMA.NAME = WNA.NAME
and WMA.TYPE = 'DOCUMENT'
and WNA.NAME = '#HDR_REGION';
select nvl(WM.BODY, ''), nvl(WM.HTML_BODY, '')
into lv_body, lv_html_body
from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
where N.NOTIFICATION_ID = nid
and N.MESSAGE_NAME = WM.NAME
and N.MESSAGE_TYPE = WM.TYPE;
select WNA.NAME, WNA.TEXT_VALUE, WMA.TYPE
from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
WF_MESSAGE_ATTRIBUTES_VL WMA
where WNA.NOTIFICATION_ID = nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WMA.TYPE = 'DOCUMENT'
and WMA.NAME = WNA.NAME
and instr( msgToken, WMA.NAME ) = 1
order by length(WMA.NAME) desc;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES
where notification_id = group_nid;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY
from WF_ITEM_ACTIVITY_STATUSES_H
where notification_id = group_nid;
SELECT group_id
INTO l_group_nid
FROM wf_notifications
WHERE notification_id = nid;
select code into lang_code from wf_languages where nls_language=p_language;
select SIG_REQUIRED,FWK_SIG_FLAVOR,EMAIL_SIG_FLAVOR, RENDER_HINT
into p_sig_required,p_fwk_sig_flavor,p_email_sig_flavor,p_render_hint
from WF_SIGNATURE_POLICIES
where sig_policy=UPPER(TRIM(v_sig_policy));
INSERT INTO wf_comments (
sequence,
notification_id,
from_role,
from_user,
to_role,
to_user,
comment_date,
action,
action_type,
proxy_role,
user_comment,
language
) VALUES (
l_seq_num,
p_nid,
l_from_role,
l_from_user,
p_to_role,
l_to_user,
sysdate,
l_action,
l_action_type,
l_proxy_user,
p_user_comment,
userenv('LANG')
);
SELECT message_type, message_name, status, mail_status, nvl(more_info_role, recipient_role) recipient_role, group_id
INTO l_message_type, l_message_name, l_status, l_mail_status, l_recipient_role, l_group_id
FROM wf_notifications
WHERE notification_id = p_nid;
UPDATE wf_notifications
SET mail_status = 'MAIL'
WHERE notification_id = p_nid;
select code into l_language from wf_languages where nls_language = l_language;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_paramlist);
select A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE
into l_result_type, p_result_code
from WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
where IAS.NOTIFICATION_ID = p_nid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and I.BEGIN_DATE between A.BEGIN_DATE
and nvl(A.END_DATE, I.BEGIN_DATE)
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE;
select A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE
into l_result_type, p_result_code
from WF_ITEM_ACTIVITY_STATUSES IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
where IAS.NOTIFICATION_ID = p_nid
and IAS.ITEM_TYPE = I.ITEM_TYPE
and IAS.ITEM_KEY = I.ITEM_KEY
and IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
and I.BEGIN_DATE between A.BEGIN_DATE
and nvl(A.END_DATE, I.BEGIN_DATE)
and PA.ACTIVITY_NAME = A.NAME
and PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE;
select wfn.notification_id
from wf_item_activity_statuses wfas, wf_notifications wfn , wf_notification_attributes wfna
where wfna.name = '#DOCUMENT_ID'
and wfna.text_value = l_doc_id
and wfas.item_type = l_item_type
and wfas.item_key = l_item_key
and wfn.notification_id = wfna.notification_id
and wfas.notification_id = wfn.group_id;
SELECT notification_id
FROM wf_notifications wn
WHERE wn.status = 'OPEN'
AND wn.mail_status = 'FAILED'
AND wn.recipient_role like nvl(cp_role, '%')
AND wn.message_type like nvl(cp_msg_type, '%')
-- No date conversion is required on wn.begin_date
AND (cp_from_date is null or wn.begin_date >= cp_from_date)
AND (cp_to_date is null or wn.begin_date <= cp_to_date ) ;
SELECT notification_id
FROM wf_notifications wn
WHERE wn.status = 'OPEN'
AND wn.mail_status = 'ERROR'
AND wn.recipient_role like nvl(cp_role, '%')
AND wn.message_type like nvl(cp_msg_type, '%')
AND (cp_from_date is null or wn.begin_date >= cp_from_date)
AND (cp_to_date is null or wn.begin_date <= cp_to_date )
AND NOT EXISTS (
SELECT 1
FROM wf_message_attributes wma,
wf_notifications wn2
WHERE wn2.notification_id = wn.notification_id
AND wma.message_type = wn2.message_type
AND wma.message_name = wn2.message_name
AND wma.subtype = 'RESPOND'
AND rownum = 1);
SELECT count(1)
INTO l_resp_attr_cnt
FROM wf_message_attributes wma,
wf_notifications wn
WHERE wn.notification_id = nid
AND wma.message_type = wn.message_type
AND wma.message_name = wn.message_name
AND wma.subtype = 'RESPOND'
AND rownum = 1;
select WNA.NAME, WMA.TYPE, WNA.TEXT_VALUE, WNA.NUMBER_VALUE,
WNA.DATE_VALUE
from WF_NOTIFICATION_ATTRIBUTES WNA,
WF_MESSAGE_ATTRIBUTES WMA,
WF_NOTIFICATIONS WN
where WNA.NOTIFICATION_ID = nid
and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
and WNA.NAME = WMA.NAME
and WMA.SUBTYPE = 'RESPOND';
select N.CALLBACK, N.CONTEXT, N.STATUS, N.USER_COMMENT,
N.RECIPIENT_ROLE, N.ORIGINAL_RECIPIENT,N.MORE_INFO_ROLE, N.FROM_ROLE, N.LANGUAGE
into respond2.callback, respond2.context, respond2.status, newcomment,
l_recip_role,l_orig_recip_role,l_more_info_role, l_from_role, l_language
from WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = nid
for update nowait;
update WF_NOTIFICATIONS
set STATUS = 'CLOSED',
MAIL_STATUS = NULL,
END_DATE = sysdate,
-- RESPONDER = respond2.responder
-- For responses through e-mail, this helps strip off unwanted parts from e-mail like
-- "John Doe" and have only email:[email protected]
RESPONDER = l_responder
-- USER_COMMENT = respond2.newcomment
where NOTIFICATION_ID = respond2.nid;
wf_event.AddParameterToList('CHANGE_TYPE', 'INSERT',l_parameterlist);
select count(1)
into l_lookupCode_cnt
from fnd_lookup_values
where lookup_type = l_lookupType
and lookup_code = l_itemType
and security_group_id = 0
and view_application_id = 0 ; -- for FND product view application id is 0
select N.CALLBACK, N.CONTEXT
into Complete.callback, Complete.context
from WF_NOTIFICATIONS N
where N.NOTIFICATION_ID = p_nid
for update nowait;