[Home] [Help]
339: NA.DATE_VALUE
340: into l_dispname, l_type, l_format, l_textv, l_numberv, l_datev
341: from WF_MESSAGE_ATTRIBUTES_VL MA,
342: WF_NOTIFICATION_ATTRIBUTES NA,
343: WF_NOTIFICATIONS N
344: where NA.NAME = l_attr
345: and NA.NOTIFICATION_ID = nid
346: and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
347: and N.MESSAGE_TYPE = MA.MESSAGE_TYPE
988: -- itemtype: string before the first colon in the context
989: -- itemkey: string between first and second colons in the contex
990: -- actid: NUMBER after the second colon
991: --
992: procedure validate_context (context IN WF_NOTIFICATIONS.CONTEXT%TYPE,
993: itemtype OUT NOCOPY varchar2,
994: itemkey OUT NOCOPY varchar2,
995: actid OUT NOCOPY number)
996: is
1052: -- Insure this is a valid notification.
1053: begin
1054: select 1 into dummy from sys.dual where exists
1055: (select null
1056: from WF_NOTIFICATIONS
1057: where NOTIFICATION_ID = nid);
1058: exception
1059: when no_data_found then
1060: wf_core.token('NID', to_char(nid));
1123: -- This is used for translating number/date strings.
1124: begin
1125: select WMA.TYPE, WMA.FORMAT
1126: into atype, format
1127: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1128: WF_MESSAGE_ATTRIBUTES WMA
1129: where WNA.NOTIFICATION_ID = nid
1130: and WNA.NAME = aname
1131: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
1230:
1231:
1232: select WN.RECIPIENT_ROLE
1233: into l_recipient_role
1234: from WF_NOTIFICATIONS WN
1235: where WN.NOTIFICATION_ID = nid ;
1236:
1237: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
1238: l_language := role_info_tbl(1).language;
1477: -- of another.
1478: cursor notification_attrs_cursor(nid number) is
1479: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1480: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1481: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1482: WF_MESSAGE_ATTRIBUTES_VL WMA
1483: where WNA.NOTIFICATION_ID = nid
1484: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1485: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1791: -- of another.
1792: cursor notification_attrs_cursor(nid number) is
1793: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1794: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1795: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1796: WF_MESSAGE_ATTRIBUTES_VL WMA
1797: where WNA.NOTIFICATION_ID = nid
1798: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1799: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2156:
2157: begin
2158: select WMA.TYPE, WMA.SUBTYPE, WMA.FORMAT
2159: into atype, subtype, format
2160: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2161: WF_MESSAGE_ATTRIBUTES WMA
2162: where WNA.NOTIFICATION_ID = nid
2163: and WNA.NAME = aname
2164: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2211: -- This is used for translating number/date strings.
2212: begin
2213: select WMA.TYPE, WMA.FORMAT
2214: into atype, format
2215: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2216: WF_MESSAGE_ATTRIBUTES WMA
2217: where WNA.NOTIFICATION_ID = nid
2218: and WNA.NAME = aname
2219: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2532: -- This is used for translating number/date strings.
2533: begin
2534: select WMATL.DISPLAY_NAME, NVL(WMA.FORMAT, '_blank')
2535: into display_name, target
2536: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2537: WF_MESSAGE_ATTRIBUTES_TL WMATL, WF_MESSAGE_ATTRIBUTES WMA
2538: where WNA.NOTIFICATION_ID = nid
2539: and WNA.NAME = aname
2540: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2604: begin
2605: -- Get subject
2606: select WM.SUBJECT
2607: into local_subject
2608: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2609: where N.NOTIFICATION_ID = nid
2610: and N.MESSAGE_NAME = WM.NAME
2611: and N.MESSAGE_TYPE = WM.TYPE;
2612:
2696: begin
2697: -- Get body
2698: select WM.BODY, WM.HTML_BODY
2699: into local_body, local_html_body
2700: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2701: where N.NOTIFICATION_ID = nid
2702: and N.MESSAGE_NAME = WM.NAME
2703: and N.MESSAGE_TYPE = WM.TYPE;
2704:
2709: -- Query to check if the ntf is FYI or not
2710: SELECT count(1)
2711: INTO l_resp_cnt
2712: FROM wf_message_attributes wma,
2713: wf_notifications wn
2714: WHERE wn.notification_id = nid
2715: AND wma.message_type = wn.message_type
2716: AND wma.message_name = wn.message_name
2717: AND wma.subtype = 'RESPOND'
2836: -- NOTE:
2837: -- If errors are detected this routine returns the body unsubstituted,
2838: -- or null if all else fails, instead of raising exceptions. It must do
2839: -- this so the routine can be pragma'd and used in the
2840: -- wf_notifications_view view.
2841: --
2842: function GetShortBody(nid in number)
2843: return varchar2 is
2844: local_body varchar2(4000);
2846: begin
2847: -- Get body
2848: select WM.BODY
2849: into local_body
2850: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2851: where N.NOTIFICATION_ID = nid
2852: and N.MESSAGE_NAME = WM.NAME
2853: and N.MESSAGE_TYPE = WM.TYPE;
2854:
2911: GetInfo.message_name,
2912: GetInfo.priority,
2913: GetInfo.due_date,
2914: GetInfo.status
2915: from WF_NOTIFICATIONS N
2916: where N.NOTIFICATION_ID = nid;
2917: exception
2918: when no_data_found then
2919: wf_core.token('NID', to_char(nid));
2949: -- Get responder
2950: begin
2951: select WN.RESPONDER
2952: into respbuf
2953: from WF_NOTIFICATIONS WN
2954: where WN.NOTIFICATION_ID = nid;
2955: exception
2956: when no_data_found then
2957: wf_core.token('NID', to_char(nid));
2983: nkey := substr(access_str, pos+1);
2984:
2985: select recipient_role
2986: into uname
2987: from WF_NOTIFICATIONS
2988: where NOTIFICATION_ID = nid
2989: and ACCESS_KEY = nkey;
2990:
2991: return uname;
3129: -- Get ntf current recipient and message
3130: begin
3131: select WN.RECIPIENT_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME
3132: into recip, msgtype, msgname
3133: from WF_NOTIFICATIONS WN
3134: where WN.NOTIFICATION_ID = nid;
3135:
3136: o_recip := recip; -- set original recipient
3137: exception
3250: return;
3251: exception
3252: when inactive_role then
3253: begin
3254: update WF_NOTIFICATIONS set
3255: USER_COMMENT = substr(USER_COMMENT||decode(nvl(USER_COMMENT,'T'),
3256: 'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
3257: where NOTIFICATION_ID = nid;
3258: exception
3273: 1, 4000);
3274: begin
3275: -- append newcomment to the existing comment.
3276: -- need to add a newline character if user_comment is not null.
3277: update WF_NOTIFICATIONS set
3278: USER_COMMENT = substr(USER_COMMENT||
3279: decode(nvl(USER_COMMENT,'T'),
3280: 'T', null, wf_core.newline)||
3281: Route.newcomment, 1, 4000)
3359: from wf_ntf_rules wnr,
3360: wf_ntf_rule_maps wnrm,
3361: wf_ntf_rule_criteria wnrc,
3362: wf_notification_attributes wna,
3363: wf_notifications wn
3364: where wnr.rule_name = wnrc.rule_name
3365: and wnrc.message_type = wn.message_type
3366: and wnr.status = 'ENABLED'
3367: and wnrc.rule_name = wnrm.rule_name
3421: na(c.idx) := c.number_value;
3422: end if;
3423: end loop;
3424:
3425: update WF_NOTIFICATIONS
3426: set
3427: PROTECTED_TEXT_ATTRIBUTE1 = pta(1)
3428: ,PROTECTED_TEXT_ATTRIBUTE2 = pta(2)
3429: ,PROTECTED_TEXT_ATTRIBUTE3 = pta(3)
3508: cursor c_notifications is
3509: select wi.item_key, wi.user_key, wn.notification_id
3510: from wf_items wi,
3511: wf_item_activity_statuses wias,
3512: wf_notifications wn
3513: where wi.item_key = wias.item_key
3514: and wi.item_type = wias.item_type
3515: and wias.notification_id = wn.group_id
3516: and (wn.message_type = p_item_type or p_item_type is null)
3612: -- Check role is valid and get mail preference
3613: mailpref := Wf_Notification.GetMailPreference(role, callback, context);
3614:
3615: -- Create new nid and insert notification
3616: select WF_NOTIFICATIONS_S.NEXTVAL
3617: into nid
3618: from SYS.DUAL;
3619:
3620: insert into WF_NOTIFICATIONS (
3616: select WF_NOTIFICATIONS_S.NEXTVAL
3617: into nid
3618: from SYS.DUAL;
3619:
3620: insert into WF_NOTIFICATIONS (
3621: NOTIFICATION_ID,
3622: GROUP_ID,
3623: MESSAGE_TYPE,
3624: MESSAGE_NAME,
3851: -- wf_xml.EnqueueNotification(nid);
3852:
3853: --Bug 2283697
3854: --To raise an EVENT whenever DML operation is performed on
3855: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
3856: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
3857: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
3858: wf_event.AddParameterToList('GROUP_ID',nvl(group_id,nid),l_parameterlist);
3859:
3983: -- Bug 7914921. The context not always comes in format itemtype:itemkey:actid
3984: if (itemtype is not null AND itemkey is not null AND actid is not null) then
3985: SELECT wn.notification_id
3986: INTO prev_nid
3987: FROM wf_notifications wn,
3988: wf_comments wc
3989: WHERE
3990: EXISTS ( SELECT /*+ NO_UNNEST */ 'x'
3991: FROM wf_item_activity_statuses_h wiash
4200: , wn.message_type, wn.message_name -- <7641725>
4201: into status, cb, context, old_role, -- newcomment,
4202: old_origrole,l_more_info_role,l_from_role
4203: , l_msgType, l_msgName
4204: from WF_NOTIFICATIONS WN
4205: where WN.NOTIFICATION_ID = nid
4206: for update nowait;
4207: exception
4208: when no_data_found then
4317: -- BUG 2331070 CTILLEY - added update to FROM_ROLE
4318: -- Bug 2474770
4319: -- Update the more_info_role aswell
4320: if (fmode = 'TRANSFER') then
4321: update WF_NOTIFICATIONS set
4322: RECIPIENT_ROLE = ForwardInternal.new_role,
4323: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4324: 'TRANSFER', ForwardInternal.new_role,
4325: ORIGINAL_RECIPIENT),
4338: Wf_Notification.SetComments(nid, l_from_role, new_role, 'TRANSFER',
4339: action_source, forward_comment);
4340:
4341: else
4342: update WF_NOTIFICATIONS set
4343: RECIPIENT_ROLE = ForwardInternal.new_role,
4344: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4345: 'TRANSFER', ForwardInternal.new_role,
4346: ORIGINAL_RECIPIENT),
4380: end if;
4381:
4382: --Bug 2283697
4383: --To raise an EVENT whenever DML operation is performed on
4384: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4385: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4386: wf_event.AddParameterToList('NEW_ROLE',new_role,l_parameterlist);
4387: wf_event.AddParameterToList('MODE',fmode,l_parameterlist);
4388: if (user is not null) then
4398: , l_msgType||':'||l_msgName, l_parameterlist);
4399:
4400: select WN.RECIPIENT_ROLE
4401: into l_recipient_role
4402: from WF_NOTIFICATIONS WN
4403: where WN.NOTIFICATION_ID = nid;
4404:
4405: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
4406: l_language := role_info_tbl(1).language;
4529: -- Check the notification exists and is open
4530: begin
4531: select WN.STATUS, WN.CALLBACK, WN.CONTEXT, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.LANGUAGE
4532: into status, cb, context, l_msg_type, l_msg_name, l_language
4533: from WF_NOTIFICATIONS WN
4534: where WN.NOTIFICATION_ID = nid
4535: for update nowait;
4536: exception
4537: when no_data_found then
4550: -- regardless of role notification_preference setting.
4551: begin
4552: select 1 into dummy from sys.dual where exists
4553: (select NULL
4554: from WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES WMA
4555: where WN.NOTIFICATION_ID = nid
4556: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
4557: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4558: and WMA.SUBTYPE = 'RESPOND');
4572: else
4573: l_mail := '';
4574: end if;
4575:
4576: update WF_NOTIFICATIONS set
4577: STATUS = 'CANCELED',
4578: END_DATE = sysdate,
4579: -- USER_COMMENT = CancelSingle.newcomment,
4580: MAIL_STATUS = decode(MAIL_STATUS,
4613: -- wf_xml.EnqueueNotification(nid);
4614:
4615: --Bug 2283697
4616: --To raise an EVENT whenever DML operation is performed on
4617: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4618: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4619: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
4620: wf_event.addParameterToList('Q_CORRELATION_ID', l_msg_type || ':'||
4621: l_msg_name, l_parameterlist);
4665: -- Check the notification exists and is open
4666: begin
4667: select STATUS, RECIPIENT_ROLE
4668: into status, role
4669: from WF_NOTIFICATIONS
4670: where NOTIFICATION_ID = nid
4671: for update nowait;
4672: exception
4673: when no_data_found then
4702: is
4703: -- Get all still open notifications in the group
4704: cursor group_curs is
4705: select NOTIFICATION_ID, RECIPIENT_ROLE
4706: from WF_NOTIFICATIONS
4707: where GROUP_ID = gid
4708: and status = 'OPEN'
4709: for update nowait;
4710:
4757: select WNA.NAME, WMA.TYPE, WNA.TEXT_VALUE, WNA.NUMBER_VALUE,
4758: WNA.DATE_VALUE
4759: from WF_NOTIFICATION_ATTRIBUTES WNA,
4760: WF_MESSAGE_ATTRIBUTES WMA,
4761: WF_NOTIFICATIONS WN
4762: where WNA.NOTIFICATION_ID = nid
4763: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4764: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4765: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
4829: select N.CALLBACK, N.CONTEXT, N.STATUS, N.USER_COMMENT,
4830: N.RECIPIENT_ROLE, N.ORIGINAL_RECIPIENT,N.MORE_INFO_ROLE, N.FROM_ROLE, N.LANGUAGE
4831: into respond.callback, respond.context, respond.status, newcomment,
4832: l_recip_role,l_orig_recip_role,l_more_info_role, l_from_role, l_language
4833: from WF_NOTIFICATIONS N
4834: where N.NOTIFICATION_ID = nid
4835: for update nowait;
4836: exception
4837: when no_data_found then
4949: -- end if;
4950: -- end if;
4951:
4952: -- Mark notification closed
4953: update WF_NOTIFICATIONS
4954: set STATUS = 'CLOSED',
4955: MAIL_STATUS = NULL,
4956: END_DATE = sysdate,
4957: -- RESPONDER = respond.responder
4966: action_source, respond_comment);
4967:
4968: --Bug 2283697
4969: --To raise an EVENT whenever DML operation is performed on
4970: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4971: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4972: wf_event.AddParameterToList('RESPONDER',respond.responder,l_parameterlist);
4973:
4974: -- AppSearch
5120: -- Get callback, check for valid notification id.
5121: begin
5122: select N.CALLBACK, N.CONTEXT
5123: into TestContext.callback, TestContext.context
5124: from WF_NOTIFICATIONS N
5125: where N.NOTIFICATION_ID = nid;
5126: exception
5127: when no_data_found then
5128: wf_core.token('NID', to_char(nid));
5191: --
5192: --
5193: select count(*)
5194: into l_total_pop
5195: from wf_notifications
5196: where group_id = Gid;
5197: --
5198: select count(*)
5199: into l_total_voted
5196: where group_id = Gid;
5197: --
5198: select count(*)
5199: into l_total_voted
5200: from wf_notifications
5201: where group_id = Gid
5202: and status = 'CLOSED';
5203: --
5204: select count(*)
5202: and status = 'CLOSED';
5203: --
5204: select count(*)
5205: into l_code_count
5206: from wf_notifications wfn,
5207: wf_notification_attributes wfna
5208: where wfn.group_id = Gid
5209: and wfn.notification_id = wfna.notification_id
5210: and wfn.status = 'CLOSED'
5265: select 1
5266: into dummy
5267: from sys.dual
5268: where exists ( select null
5269: from wf_notifications
5270: where group_id = Gid
5271: and status = 'OPEN'
5272: );
5273: --
5303: colon := instr(username, ':');
5304: if (colon = 0) then
5305: select count(1)
5306: into ncount
5307: from WF_NOTIFICATIONS WN
5308: where WN.RECIPIENT_ROLE in
5309: (select WUR.ROLE_NAME
5310: from WF_USER_ROLES WUR
5311: where WUR.USER_NAME = WorkCount.username)
5312: and WN.STATUS = 'OPEN';
5313: else
5314: select count(1)
5315: into ncount
5316: from WF_NOTIFICATIONS WN
5317: where WN.RECIPIENT_ROLE in
5318: (select WUR.ROLE_NAME
5319: from WF_USER_ROLES WUR
5320: where WUR.USER_ORIG_SYSTEM = substr(WorkCount.username, 1, colon-1)
5347: cursor attrs(mnid in number) is
5348: select MA.NAME
5349: from WF_NOTIFICATION_ATTRIBUTES NA,
5350: WF_MESSAGE_ATTRIBUTES_VL MA,
5351: WF_NOTIFICATIONS N
5352: where N.NOTIFICATION_ID = mnid
5353: and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
5354: and MA.MESSAGE_NAME = N.MESSAGE_NAME
5355: and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
5374: begin
5375:
5376: select N.STATUS, N.LANGUAGE
5377: into close.status, l_language
5378: from WF_NOTIFICATIONS N
5379: where N.NOTIFICATION_ID = nid
5380: for update nowait;
5381: exception
5382: when no_data_found then
5399: wf_core.raise('WFNTF_NID_REQUIRE');
5400: end if;
5401:
5402: -- Mark notification closed
5403: update WF_NOTIFICATIONS
5404: set STATUS = 'CLOSED',
5405: END_DATE = sysdate,
5406: RESPONDER = close.responder
5407: where NOTIFICATION_ID = nid;
5414: -- wf_xml.RemoveNotification(nid);
5415:
5416: --Bug 2283697
5417: --To raise an EVENT whenever DML operation is performed on
5418: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
5419: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
5420: wf_event.AddParameterToList('RESPONDER',close.responder,l_parameterlist);
5421:
5422: -- AppSearch
5853: begin
5854: -- <7443088> improved query
5855: select NAME into aname from
5856: (select WMA.NAME
5857: from WF_NOTIFICATIONS WN,
5858: WF_MESSAGE_ATTRIBUTES WMA,
5859: WF_NOTIFICATION_ATTRIBUTES NA
5860: where WN.NOTIFICATION_ID = nid
5861: and wn.notification_id = na.notification_id
6226: end SetNLSLanguage;
6227:
6228: --
6229: -- Denormalize_Notification
6230: -- Populate the donormalized value to WF_NOTIFICATIONS table according
6231: -- to the language setting of username provided.
6232: -- IN:
6233: -- nid - Notification id
6234: -- username - optional role name, if not provided, use the
6363:
6364: begin
6365: select RECIPIENT_ROLE, FROM_ROLE
6366: into l_user, l_from_role
6367: from WF_NOTIFICATIONS
6368: where NOTIFICATION_ID = nid;
6369: exception
6370: when NO_DATA_FOUND then
6371: wf_core.token('NID', to_char(nid));
6497: end if;
6498: -- Populate the notification values
6499: --
6500: begin
6501: update WF_NOTIFICATIONS
6502: set FROM_USER = l_from_user,
6503: FROM_ROLE = nvl(l_from_role,FROM_ROLE),
6504: TO_USER = l_to_user,
6505: SUBJECT = l_subject,
6537: xmessageName varchar2(30);
6538:
6539: cursor fyiNid is
6540: select WN.NOTIFICATION_ID
6541: from WF_NOTIFICATIONS WN
6542: where MESSAGE_TYPE like xitemtype
6543: and MESSAGE_NAME like xmessageName
6544: and BEGIN_DATE<=begindate
6545: and STATUS = 'OPEN'
6769: SELECT callback , context ,RECIPIENT_ROLE, ORIGINAL_RECIPIENT,
6770: MORE_INFO_ROLE ,from_role, message_type, message_name
6771: into cb, context,l_recip_role , l_orig_recip_role,
6772: l_more_info_role, l_from_role, l_messageType, l_messageName
6773: FROM wf_notifications
6774: WHERE notification_id = nid;
6775:
6776: g_context_recipient_role := l_recip_role;
6777: g_context_original_recipient:= l_orig_recip_role;
6843: -- do not want it hung when some one is doing update.
6844: begin
6845: select MORE_INFO_ROLE
6846: into l_from_role
6847: from WF_NOTIFICATIONS
6848: where NOTIFICATION_ID = nid
6849: for update nowait;
6850: exception
6851: when NO_DATA_FOUND then
6890:
6891: -- if there is a valid session, then we can update the FROM_ROLE
6892: -- and FROM_USER accurately.
6893: if (myusername is not null) then
6894: update WF_NOTIFICATIONS
6895: set MORE_INFO_ROLE = username,
6896: FROM_USER = mydispname,
6897: FROM_ROLE = myusername,
6898: MAIL_STATUS = decode (mailpref, 'QUERY', '',
6904:
6905: -- otherwise, we default to what it should be. Unfortunately, if it
6906: -- is a group role, we will not be able to identify which member I am.
6907: else
6908: update WF_NOTIFICATIONS
6909: set MORE_INFO_ROLE = username,
6910: FROM_USER = TO_USER,
6911: FROM_ROLE = RECIPIENT_ROLE,
6912: MAIL_STATUS = decode (mailpref, 'QUERY', '',
6918: end if;
6919:
6920: Wf_Notification.SetComments(nid, myusername, username, 'QUESTION', action_source, substrb(comment,1,4000));
6921:
6922: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
6923: -- insert into WF_COMMENTS (
6924: -- NOTIFICATION_ID,
6925: -- FROM_ROLE,
6926: -- FROM_USER,
6935: -- sysdate,
6936: -- 'QUESTION',
6937: -- substrb(comment,1,4000),
6938: -- LANGUAGE
6939: -- from WF_NOTIFICATIONS
6940: -- where NOTIFICATION_ID = nid;
6941:
6942: -- bug 2474562
6943: -- else
6962: -- answer at the same time.
6963: begin
6964: select MORE_INFO_ROLE,Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), RECIPIENT_ROLE,FROM_ROLE
6965: into l_from_role, replyby, recipient_role,l_question_role
6966: from WF_NOTIFICATIONS
6967: where NOTIFICATION_ID = nid
6968: and MORE_INFO_ROLE is not null
6969: for update nowait;
6970:
7016: -- shanjgik 01-JUL-03 bug 2887130
7017: -- get the recipient's(one who requested more information) mail preference
7018: mailpref := wf_notification.GetMailPreference (recipient_role, null, null);
7019:
7020: update WF_NOTIFICATIONS
7021: set FROM_USER = replyby,
7022: FROM_ROLE = l_from_role,
7023: MORE_INFO_ROLE = null,
7024: MAIL_STATUS = decode (mailpref, 'QUERY', '',
7048: -- sysdate,
7049: -- 'ANSWER',
7050: -- substrb(comment,1,4000),
7051: -- userenv('LANG')
7052: -- from WF_NOTIFICATIONS
7053: -- where NOTIFICATION_ID = nid;
7054:
7055: -- <
7056: -- if we are here, mean we are going to raise
7063: -- wf_xml.EnqueueNotification(nid);
7064:
7065: -- Bug 2283697
7066: -- To raise an EVENT whenever DML operation is performed on
7067: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7068: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7069: wf_event.AddParameterToList('ROLE', username, l_parameterlist);
7070: wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist);
7071: wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType||':'||
7136: -- Notification only
7137: begin
7138: select NULL, '#SYNCH'
7139: into itype, ikey
7140: from WF_NOTIFICATIONS
7141: where NOTIFICATION_ID = nid;
7142: exception
7143: when OTHERS then
7144: return(FALSE);
7167: if (itype is null and ikey = '#SYNCH') then
7168: -- this is notification only
7169: begin
7170: select 1 into ans
7171: from WF_NOTIFICATIONS
7172: where IsValidInfoRole.username in (RECIPIENT_ROLE, ORIGINAL_RECIPIENT)
7173: and NOTIFICATION_ID = nid;
7174: exception
7175: when NO_DATA_FOUND then
7187: select 1 into ans
7188: from (
7189: select /*+ leading(grp_id_view) */
7190: RECIPIENT_ROLE , ORIGINAL_RECIPIENT
7191: from WF_NOTIFICATIONS a ,
7192: ( select notification_id group_id
7193: from WF_ITEM_ACTIVITY_STATUSES
7194: where item_type = itype
7195: and item_key = ikey
7277: SELECT callback, context, recipient_role, original_recipient,
7278: more_info_role, from_role, status, message_type, message_name
7279: INTO cb, context, l_recip_role, l_orig_recip_role,
7280: l_more_info_role, l_from_role, l_stat, l_messageType, l_messageName
7281: FROM wf_notifications
7282: WHERE notification_id = nid;
7283:
7284: -- Donot process the request if the notification is not open.
7285: if (l_stat <> 'OPEN') then
7352: -- do not want it hung when some one is doing update.
7353: begin
7354: select MORE_INFO_ROLE, MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID
7355: into l_from_role, l_messageType, l_messageName, l_groupId
7356: from WF_NOTIFICATIONS
7357: where NOTIFICATION_ID = nid
7358: for update nowait;
7359: exception
7360: when NO_DATA_FOUND then
7394: -- as we donot have a user session for the mailer, the only way to
7395: -- find FROM_ROLE and FROM_USER are through the from_addr. If the
7396: -- user name and display name are not available, email address is updated
7397: /* if (myusername is not null) then
7398: update WF_NOTIFICATIONS
7399: set MORE_INFO_ROLE = username,
7400: FROM_USER = mydispname,
7401: FROM_ROLE = myusername
7402: where NOTIFICATION_ID = nid;
7403:
7404: -- otherwise, we default to what it should be. Unfortunately, if it
7405: -- is a group role, we will not be able to identify which member I am.
7406: else */
7407: update WF_NOTIFICATIONS
7408: set MAIL_STATUS = 'MAIL',
7409: MORE_INFO_ROLE = l_username,
7410: FROM_USER = TO_USER,
7411: FROM_ROLE = RECIPIENT_ROLE
7413: /*end if; */
7414:
7415: Wf_Notification.SetComments(nid, myusername, l_username, 'QUESTION', null, substrb(comment,1,4000));
7416:
7417: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
7418: -- insert into WF_COMMENTS (
7419: -- NOTIFICATION_ID,
7420: -- FROM_ROLE,
7421: -- FROM_USER,
7430: -- sysdate,
7431: -- 'QUESTION',
7432: -- substrb(comment,1,4000),
7433: -- LANGUAGE
7434: -- from WF_NOTIFICATIONS
7435: -- where NOTIFICATION_ID = nid;
7436:
7437: end if;
7438:
7452: begin
7453: select MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7454: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role
7455: into l_from_role, replyby, l_messageType, l_messageName, l_groupId, l_question_role
7456: from WF_NOTIFICATIONS
7457: where NOTIFICATION_ID = nid
7458: and MORE_INFO_ROLE is not null
7459: for update nowait;
7460:
7497: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
7498: 'Updating ANSWER');
7499: end if;
7500:
7501: update WF_NOTIFICATIONS
7502: set MAIL_STATUS = 'MAIL',
7503: FROM_USER = replyby,
7504: FROM_ROLE = l_from_role,
7505: MORE_INFO_ROLE = null
7524: -- sysdate,
7525: -- 'ANSWER',
7526: -- substrb(comment,1,4000),
7527: -- userenv('LANG')
7528: -- from WF_NOTIFICATIONS
7529: -- where NOTIFICATION_ID = nid;
7530:
7531: -- we are here, mean we are going to raise
7532: -- oracle.apps.wf.notification.answer event.
7541: -- wf_xml.EnqueueNotification(nid);
7542:
7543: -- Bug 2283697
7544: -- To raise an EVENT whenever DML operation is performed on
7545: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7546: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7547:
7548: -- username MAY be a display name
7549: wf_event.AddParameterToList('ROLE', nvl(l_username, username), l_parameterlist);
7634: Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7635: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role, callback, context
7636: into l_orig_recip_role, l_recipient_role, l_from_role,
7637: replyby, l_messageType, l_messageName, l_groupId, l_question_role, cb, context
7638: from WF_NOTIFICATIONS
7639: where NOTIFICATION_ID = nid
7640: and MORE_INFO_ROLE is not null
7641: for update nowait;
7642:
7682:
7683: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7684: 'Updating ANSWER');
7685:
7686: update WF_NOTIFICATIONS
7687: set MAIL_STATUS = 'MAIL',
7688: FROM_USER = moreinforesponder,
7689: FROM_ROLE = moreinforesponder,
7690: MORE_INFO_ROLE = null
7700: -- wf_xml.EnqueueNotification(nid);
7701:
7702: -- Bug 2283697
7703: -- To raise an EVENT whenever DML operation is performed on
7704: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7705: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7706: -- skilaru 12-MAR-04 In UpdateInfo2 username would be null in Answer mode
7707: -- to keep the behaviour same just pass null as ROLE..
7708: wf_event.AddParameterToList('ROLE', null, l_parameterlist);
8413: begin
8414: -- <7443088> improved query
8415: select NAME into aname from
8416: (select WMA.NAME
8417: from WF_NOTIFICATIONS WN,
8418: WF_MESSAGE_ATTRIBUTES WMA,
8419: WF_NOTIFICATION_ATTRIBUTES NA
8420: where WN.NOTIFICATION_ID = nid
8421: and wn.notification_id = na.notification_id
8641: lv_fwk_body := 'N';
8642:
8643: select nvl(WM.BODY, ''), nvl(WM.HTML_BODY, '')
8644: into lv_body, lv_html_body
8645: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
8646: where N.NOTIFICATION_ID = nid
8647: and N.MESSAGE_NAME = WM.NAME
8648: and N.MESSAGE_TYPE = WM.TYPE;
8649:
8713:
8714: -- Cursur to check for each message Attribute
8715: cursor cur_msg_attrs(nid number, msgToken varchar2) is
8716: select WNA.NAME, WNA.TEXT_VALUE, WMA.TYPE
8717: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
8718: WF_MESSAGE_ATTRIBUTES_VL WMA
8719: where WNA.NOTIFICATION_ID = nid
8720: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
8721: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
8786:
8787: begin
8788: --skilaru 16-July-03
8789: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
8790: --mapped to WF_NOTIFICATIONS.GROUP_ID
8791: SELECT group_id
8792: INTO l_group_nid
8793: FROM wf_notifications
8794: WHERE notification_id = nid;
8789: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
8790: --mapped to WF_NOTIFICATIONS.GROUP_ID
8791: SELECT group_id
8792: INTO l_group_nid
8793: FROM wf_notifications
8794: WHERE notification_id = nid;
8795:
8796: for act_status_row in act_info_statuses_cursor( l_group_nid ) loop
8797: l_itype := act_status_row.ITEM_TYPE;
9235:
9236: begin
9237: SELECT message_type, message_name, status, mail_status, nvl(more_info_role, recipient_role) recipient_role, group_id
9238: INTO l_message_type, l_message_name, l_status, l_mail_status, l_recipient_role, l_group_id
9239: FROM wf_notifications
9240: WHERE notification_id = p_nid;
9241: exception
9242: when no_data_found then
9243: wf_core.token('NID', to_char(p_nid));
9268: wf_core.raise('WFNTF_EMAIL_NOTSENT');
9269: end if;
9270:
9271: -- Raise the event to send an e-mail
9272: UPDATE wf_notifications
9273: SET mail_status = 'MAIL'
9274: WHERE notification_id = p_nid;
9275:
9276: Wf_Event.AddParameterToList('NOTIFICATION_ID', p_nid, l_paramlist);
9380: is
9381: --Get the nids in curs_nid which have the attribute document_id
9382: cursor curs_nid(l_doc_id varchar2,l_item_type varchar2,l_item_key varchar2) is
9383: select wfn.notification_id
9384: from wf_item_activity_statuses wfas, wf_notifications wfn , wf_notification_attributes wfna
9385: where wfna.name = '#DOCUMENT_ID'
9386: and wfna.text_value = l_doc_id
9387: and wfas.item_type = l_item_type
9388: and wfas.item_key = l_item_key
9445: cp_from_date date,
9446: cp_to_date date)
9447: IS
9448: SELECT notification_id
9449: FROM wf_notifications wn
9450: WHERE wn.status = 'OPEN'
9451: AND wn.mail_status = 'FAILED'
9452: AND wn.recipient_role like nvl(cp_role, '%')
9453: AND wn.message_type like nvl(cp_msg_type, '%')
9461: cp_from_date date,
9462: cp_to_date date)
9463: IS
9464: SELECT notification_id
9465: FROM wf_notifications wn
9466: WHERE wn.status = 'OPEN'
9467: AND wn.mail_status = 'ERROR'
9468: AND wn.recipient_role like nvl(cp_role, '%')
9469: AND wn.message_type like nvl(cp_msg_type, '%')
9471: AND (cp_to_date is null or wn.begin_date <= cp_to_date )
9472: AND NOT EXISTS (
9473: SELECT 1
9474: FROM wf_message_attributes wma,
9475: wf_notifications wn2
9476: WHERE wn2.notification_id = wn.notification_id
9477: AND wma.message_type = wn2.message_type
9478: AND wma.message_name = wn2.message_name
9479: AND wma.subtype = 'RESPOND'