[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
1027: -- itemtype: string before the first colon in the context
1028: -- itemkey: string between first and second colons in the contex
1029: -- actid: NUMBER after the second colon
1030: --
1031: procedure validate_context (context IN WF_NOTIFICATIONS.CONTEXT%TYPE,
1032: itemtype OUT NOCOPY varchar2,
1033: itemkey OUT NOCOPY varchar2,
1034: actid OUT NOCOPY number)
1035: is
1091: -- Insure this is a valid notification.
1092: begin
1093: select 1 into dummy from sys.dual where exists
1094: (select null
1095: from WF_NOTIFICATIONS
1096: where NOTIFICATION_ID = nid);
1097: exception
1098: when no_data_found then
1099: wf_core.token('NID', to_char(nid));
1162: -- This is used for translating number/date strings.
1163: begin
1164: select WMA.TYPE, WMA.FORMAT
1165: into atype, format
1166: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1167: WF_MESSAGE_ATTRIBUTES WMA
1168: where WNA.NOTIFICATION_ID = nid
1169: and WNA.NAME = aname
1170: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
1269:
1270:
1271: select WN.RECIPIENT_ROLE
1272: into l_recipient_role
1273: from WF_NOTIFICATIONS WN
1274: where WN.NOTIFICATION_ID = nid ;
1275:
1276: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
1277: l_language := role_info_tbl(1).language;
1517: -- of another.
1518: cursor notification_attrs_cursor(nid number) is
1519: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1520: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1521: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1522: WF_MESSAGE_ATTRIBUTES_VL WMA
1523: where WNA.NOTIFICATION_ID = nid
1524: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1525: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1858: -- of another.
1859: cursor notification_attrs_cursor(nid number) is
1860: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1861: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1862: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1863: WF_MESSAGE_ATTRIBUTES_VL WMA
1864: where WNA.NOTIFICATION_ID = nid
1865: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1866: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
2223:
2224: begin
2225: select WMA.TYPE, WMA.SUBTYPE, WMA.FORMAT
2226: into atype, subtype, format
2227: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2228: WF_MESSAGE_ATTRIBUTES WMA
2229: where WNA.NOTIFICATION_ID = nid
2230: and WNA.NAME = aname
2231: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2278: -- This is used for translating number/date strings.
2279: begin
2280: select WMA.TYPE, WMA.FORMAT
2281: into atype, format
2282: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2283: WF_MESSAGE_ATTRIBUTES WMA
2284: where WNA.NOTIFICATION_ID = nid
2285: and WNA.NAME = aname
2286: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2618: -- This is used for translating number/date strings.
2619: begin
2620: select WMATL.DISPLAY_NAME, NVL(WMA.FORMAT, '_blank')
2621: into display_name, target
2622: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2623: WF_MESSAGE_ATTRIBUTES_TL WMATL, WF_MESSAGE_ATTRIBUTES WMA
2624: where WNA.NOTIFICATION_ID = nid
2625: and WNA.NAME = aname
2626: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2690: begin
2691: -- Get subject
2692: select WM.SUBJECT
2693: into local_subject
2694: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2695: where N.NOTIFICATION_ID = nid
2696: and N.MESSAGE_NAME = WM.NAME
2697: and N.MESSAGE_TYPE = WM.TYPE;
2698:
2782: begin
2783: -- Get body
2784: select WM.BODY, WM.HTML_BODY
2785: into local_body, local_html_body
2786: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2787: where N.NOTIFICATION_ID = nid
2788: and N.MESSAGE_NAME = WM.NAME
2789: and N.MESSAGE_TYPE = WM.TYPE;
2790:
2795: -- Query to check if the ntf is FYI or not
2796: SELECT count(1)
2797: INTO l_resp_cnt
2798: FROM wf_message_attributes wma,
2799: wf_notifications wn
2800: WHERE wn.notification_id = nid
2801: AND wma.message_type = wn.message_type
2802: AND wma.message_name = wn.message_name
2803: AND wma.subtype = 'RESPOND'
2922: -- NOTE:
2923: -- If errors are detected this routine returns the body unsubstituted,
2924: -- or null if all else fails, instead of raising exceptions. It must do
2925: -- this so the routine can be pragma'd and used in the
2926: -- wf_notifications_view view.
2927: --
2928: function GetShortBody(nid in number)
2929: return varchar2 is
2930: local_body varchar2(4000);
2932: begin
2933: -- Get body
2934: select WM.BODY
2935: into local_body
2936: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2937: where N.NOTIFICATION_ID = nid
2938: and N.MESSAGE_NAME = WM.NAME
2939: and N.MESSAGE_TYPE = WM.TYPE;
2940:
2997: GetInfo.message_name,
2998: GetInfo.priority,
2999: GetInfo.due_date,
3000: GetInfo.status
3001: from WF_NOTIFICATIONS N
3002: where N.NOTIFICATION_ID = nid;
3003: exception
3004: when no_data_found then
3005: wf_core.token('NID', to_char(nid));
3035: -- Get responder
3036: begin
3037: select WN.RESPONDER
3038: into respbuf
3039: from WF_NOTIFICATIONS WN
3040: where WN.NOTIFICATION_ID = nid;
3041: exception
3042: when no_data_found then
3043: wf_core.token('NID', to_char(nid));
3069: nkey := substr(access_str, pos+1);
3070:
3071: select recipient_role
3072: into uname
3073: from WF_NOTIFICATIONS
3074: where NOTIFICATION_ID = nid
3075: and ACCESS_KEY = nkey;
3076:
3077: return uname;
3186: inactive_role exception; -- Notification not routed if role is inactive
3187: errmsg varchar2(4000);
3188: dummy varchar2(4000);
3189: l_hide_reassign varchar(1);
3190: l_context WF_NOTIFICATIONS.CONTEXT%TYPE;
3191: l_item_key WF_ITEMS.ITEM_KEY%TYPE;
3192: l_act_id number;
3193: l_wf_owner WF_ITEMS.OWNER_ROLE%TYPE;
3194: l_from_role WF_NOTIFICATIONS.FROM_ROLE%TYPE;
3190: l_context WF_NOTIFICATIONS.CONTEXT%TYPE;
3191: l_item_key WF_ITEMS.ITEM_KEY%TYPE;
3192: l_act_id number;
3193: l_wf_owner WF_ITEMS.OWNER_ROLE%TYPE;
3194: l_from_role WF_NOTIFICATIONS.FROM_ROLE%TYPE;
3195:
3196: cursor rulecurs is
3197: select WRR.RULE_ID, WRR.ACTION, WRR.ACTION_ARGUMENT, WRR.RULE_COMMENT
3198: from WF_ROUTING_RULES WRR
3220: -- Get ntf current recipient and message
3221: begin
3222: select WN.RECIPIENT_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.CONTEXT
3223: into recip, msgtype, msgname, l_context
3224: from WF_NOTIFICATIONS WN
3225: where WN.NOTIFICATION_ID = nid;
3226:
3227: o_recip := recip; -- set original recipient
3228: exception
3360: return;
3361: exception
3362: when inactive_role then
3363: begin
3364: update WF_NOTIFICATIONS set
3365: USER_COMMENT = substr(USER_COMMENT||decode(nvl(USER_COMMENT,'T'),
3366: 'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
3367: where NOTIFICATION_ID = nid;
3368: exception
3387: end if;
3388: begin
3389: -- append newcomment to the existing comment.
3390: -- need to add a newline character if user_comment is not null.
3391: update WF_NOTIFICATIONS set
3392: USER_COMMENT = substr(USER_COMMENT||
3393: decode(nvl(USER_COMMENT,'T'),
3394: 'T', null, wf_core.newline)||
3395: Route.newcomment, 1, 4000)
3473: from wf_ntf_rules wnr,
3474: wf_ntf_rule_maps wnrm,
3475: wf_ntf_rule_criteria wnrc,
3476: wf_notification_attributes wna,
3477: wf_notifications wn
3478: where wnr.rule_name = wnrc.rule_name
3479: and wnrc.message_type = wn.message_type
3480: and wnr.status = 'ENABLED'
3481: and wnrc.rule_name = wnrm.rule_name
3535: na(c.idx) := c.number_value;
3536: end if;
3537: end loop;
3538:
3539: update WF_NOTIFICATIONS
3540: set
3541: PROTECTED_TEXT_ATTRIBUTE1 = pta(1)
3542: ,PROTECTED_TEXT_ATTRIBUTE2 = pta(2)
3543: ,PROTECTED_TEXT_ATTRIBUTE3 = pta(3)
3622: cursor c_notifications is
3623: select wi.item_key, wi.user_key, wn.notification_id
3624: from wf_items wi,
3625: wf_item_activity_statuses wias,
3626: wf_notifications wn
3627: where wi.item_key = wias.item_key
3628: and wi.item_type = wias.item_type
3629: and wias.notification_id = wn.group_id
3630: and (wn.message_type = p_item_type or p_item_type is null)
3714: col1 pls_integer;
3715: col2 pls_integer;
3716: l_language varchar2(30);
3717: role_info_tbl wf_directory.wf_local_roles_tbl_type;
3718: l_new_rcpt_role WF_NOTIFICATIONS.RECIPIENT_ROLE%TYPE;
3719:
3720: cursor message_attrs_cursor(msg_type varchar2, msg_name varchar2) is
3721: select NAME, TYPE, SUBTYPE, VALUE_TYPE,
3722: TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT
3727: -- Check role is valid and get mail preference
3728: mailpref := Wf_Notification.GetMailPreference(role, callback, context);
3729:
3730: -- Create new nid and insert notification
3731: select WF_NOTIFICATIONS_S.NEXTVAL
3732: into nid
3733: from SYS.DUAL;
3734:
3735: insert into WF_NOTIFICATIONS (
3731: select WF_NOTIFICATIONS_S.NEXTVAL
3732: into nid
3733: from SYS.DUAL;
3734:
3735: insert into WF_NOTIFICATIONS (
3736: NOTIFICATION_ID,
3737: GROUP_ID,
3738: MESSAGE_TYPE,
3739: MESSAGE_NAME,
3938: --Bug 10243065. If the RECIPIENT_ROLE changed then the notification was
3939: -- either delegated or transfered. We need to flag this sutuation
3940: select RECIPIENT_ROLE
3941: into l_new_rcpt_role
3942: from WF_NOTIFICATIONS
3943: where NOTIFICATION_ID=nid;
3944: if SendSingle.Role<>l_new_rcpt_role then
3945: wf_event.AddParameterToList('IS_DUPLICATE','TRUE',l_parameterlist);
3946: end if;
3981: -- wf_xml.EnqueueNotification(nid);
3982:
3983: --Bug 2283697
3984: --To raise an EVENT whenever DML operation is performed on
3985: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
3986: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
3987: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
3988: wf_event.AddParameterToList('GROUP_ID',nvl(group_id,nid),l_parameterlist);
3989:
4059: send_comment in varchar2,
4060: priority in number)
4061: return number is
4062: dummy pls_integer;
4063: nid WF_NOTIFICATIONS.NOTIFICATION_ID%TYPE;
4064: rorig_system varchar2(30);
4065: rorig_system_id WF_LOCAL_ROLES.ORIG_SYSTEM_ID%TYPE;
4066: --Bug 4050078
4067: itemtype varchar2(8);
4115: if (itemtype is not null AND itemkey is not null AND actid is not null) then
4116: -- bug 8729116. Need to select only one row
4117: SELECT max(wn.notification_id)
4118: INTO prev_nid
4119: FROM wf_notifications wn,
4120: wf_comments wc
4121: WHERE
4122: EXISTS ( SELECT 'x' -- 8554209
4123: FROM wf_item_activity_statuses_h wiash
4179: send_comment in varchar2,
4180: priority in number)
4181: return number is
4182: dummy pls_integer;
4183: nid WF_NOTIFICATIONS.NOTIFICATION_ID%TYPE;
4184: gid WF_NOTIFICATIONS.GROUP_ID%TYPE;
4185:
4186: rorig_system varchar2(30);
4187: rorig_system_id WF_LOCAL_ROLES.ORIG_SYSTEM_ID%TYPE;
4180: priority in number)
4181: return number is
4182: dummy pls_integer;
4183: nid WF_NOTIFICATIONS.NOTIFICATION_ID%TYPE;
4184: gid WF_NOTIFICATIONS.GROUP_ID%TYPE;
4185:
4186: rorig_system varchar2(30);
4187: rorig_system_id WF_LOCAL_ROLES.ORIG_SYSTEM_ID%TYPE;
4188:
4342: , wn.message_type, wn.message_name -- <7641725>
4343: into status, cb, context, old_role, -- newcomment,
4344: old_origrole,l_more_info_role,l_from_role
4345: , l_msgType, l_msgName
4346: from WF_NOTIFICATIONS WN
4347: where WN.NOTIFICATION_ID = nid
4348: for update nowait;
4349: exception
4350: when no_data_found then
4480: -- BUG 2331070 CTILLEY - added update to FROM_ROLE
4481: -- Bug 2474770
4482: -- Update the more_info_role aswell
4483: if (fmode = 'TRANSFER') then
4484: update WF_NOTIFICATIONS set
4485: RECIPIENT_ROLE = ForwardInternal.new_role,
4486: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4487: 'TRANSFER', ForwardInternal.new_role,
4488: ORIGINAL_RECIPIENT),
4502: Wf_Notification.SetComments(nid, l_from_role, new_role, 'TRANSFER',
4503: action_source, forward_comment);
4504:
4505: else
4506: update WF_NOTIFICATIONS set
4507: RECIPIENT_ROLE = ForwardInternal.new_role,
4508: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4509: 'TRANSFER', ForwardInternal.new_role,
4510: ORIGINAL_RECIPIENT),
4545: end if;
4546:
4547: --Bug 2283697
4548: --To raise an EVENT whenever DML operation is performed on
4549: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4550: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4551: wf_event.AddParameterToList('NEW_ROLE',new_role,l_parameterlist);
4552: wf_event.AddParameterToList('MODE',fmode,l_parameterlist);
4553: if (user is not null) then
4563: , l_msgType||':'||l_msgName, l_parameterlist);
4564:
4565: select WN.RECIPIENT_ROLE
4566: into l_recipient_role
4567: from WF_NOTIFICATIONS WN
4568: where WN.NOTIFICATION_ID = nid;
4569:
4570: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
4571: l_language := role_info_tbl(1).language;
4694: -- Check the notification exists and is open
4695: begin
4696: select WN.STATUS, WN.CALLBACK, WN.CONTEXT, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.LANGUAGE
4697: into status, cb, context, l_msg_type, l_msg_name, l_language
4698: from WF_NOTIFICATIONS WN
4699: where WN.NOTIFICATION_ID = nid
4700: for update nowait;
4701: exception
4702: when no_data_found then
4715: -- regardless of role notification_preference setting.
4716: begin
4717: select 1 into dummy from sys.dual where exists
4718: (select NULL
4719: from WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES WMA
4720: where WN.NOTIFICATION_ID = nid
4721: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
4722: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4723: and WMA.SUBTYPE = 'RESPOND');
4737: else
4738: l_mail := '';
4739: end if;
4740:
4741: update WF_NOTIFICATIONS set
4742: STATUS = 'CANCELED',
4743: END_DATE = sysdate,
4744: -- USER_COMMENT = CancelSingle.newcomment,
4745: MAIL_STATUS = decode(MAIL_STATUS,
4778: -- wf_xml.EnqueueNotification(nid);
4779:
4780: --Bug 2283697
4781: --To raise an EVENT whenever DML operation is performed on
4782: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4783: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4784: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
4785: wf_event.addParameterToList('Q_CORRELATION_ID', l_msg_type || ':'||
4786: l_msg_name, l_parameterlist);
4830: -- Check the notification exists and is open
4831: begin
4832: select STATUS, RECIPIENT_ROLE
4833: into status, role
4834: from WF_NOTIFICATIONS
4835: where NOTIFICATION_ID = nid
4836: for update nowait;
4837: exception
4838: when no_data_found then
4867: is
4868: -- Get all still open notifications in the group
4869: cursor group_curs is
4870: select NOTIFICATION_ID, RECIPIENT_ROLE
4871: from WF_NOTIFICATIONS
4872: where GROUP_ID = gid
4873: and status = 'OPEN'
4874: for update nowait;
4875:
4961: -- Get callback, check for valid notification id.
4962: begin
4963: select N.CALLBACK, N.CONTEXT
4964: into TestContext.callback, TestContext.context
4965: from WF_NOTIFICATIONS N
4966: where N.NOTIFICATION_ID = nid;
4967: exception
4968: when no_data_found then
4969: wf_core.token('NID', to_char(nid));
5032: --
5033: --
5034: select count(*)
5035: into l_total_pop
5036: from wf_notifications
5037: where group_id = Gid;
5038: --
5039: select count(*)
5040: into l_total_voted
5037: where group_id = Gid;
5038: --
5039: select count(*)
5040: into l_total_voted
5041: from wf_notifications
5042: where group_id = Gid
5043: and status = 'CLOSED';
5044: --
5045: select count(*)
5043: and status = 'CLOSED';
5044: --
5045: select count(*)
5046: into l_code_count
5047: from wf_notifications wfn,
5048: wf_notification_attributes wfna
5049: where wfn.group_id = Gid
5050: and wfn.notification_id = wfna.notification_id
5051: and wfn.status = 'CLOSED'
5106: select 1
5107: into dummy
5108: from sys.dual
5109: where exists ( select null
5110: from wf_notifications
5111: where group_id = Gid
5112: and status = 'OPEN'
5113: );
5114: --
5146: l_orig_system_id);
5147:
5148: select count(1)
5149: into ncount
5150: from WF_NOTIFICATIONS WN,
5151: (select WUR.ROLE_NAME
5152: from WF_USER_ROLES WUR
5153: where WUR.USER_NAME = WorkCount.username
5154: and WUR.USER_ORIG_SYSTEM = l_orig_system
5183: cursor attrs(mnid in number) is
5184: select MA.NAME
5185: from WF_NOTIFICATION_ATTRIBUTES NA,
5186: WF_MESSAGE_ATTRIBUTES_VL MA,
5187: WF_NOTIFICATIONS N
5188: where N.NOTIFICATION_ID = mnid
5189: and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
5190: and MA.MESSAGE_NAME = N.MESSAGE_NAME
5191: and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
5210: begin
5211:
5212: select N.STATUS, N.LANGUAGE
5213: into close.status, l_language
5214: from WF_NOTIFICATIONS N
5215: where N.NOTIFICATION_ID = nid
5216: for update nowait;
5217: exception
5218: when no_data_found then
5235: wf_core.raise('WFNTF_NID_REQUIRE');
5236: end if;
5237:
5238: -- Mark notification STATUS as 'CLOSED' and MAIL_STATUS as NULL
5239: update WF_NOTIFICATIONS
5240: set STATUS = 'CLOSED',
5241: MAIL_STATUS = NULL,
5242: END_DATE = sysdate,
5243: RESPONDER = close.responder
5251: -- wf_xml.RemoveNotification(nid);
5252:
5253: --Bug 2283697
5254: --To raise an EVENT whenever DML operation is performed on
5255: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
5256: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
5257: wf_event.AddParameterToList('RESPONDER',close.responder,l_parameterlist);
5258:
5259: -- AppSearch
5695: begin
5696: -- <7443088> improved query
5697: select NAME into aname from
5698: (select WMA.NAME
5699: from WF_NOTIFICATIONS WN,
5700: WF_MESSAGE_ATTRIBUTES WMA,
5701: WF_NOTIFICATION_ATTRIBUTES NA
5702: where WN.NOTIFICATION_ID = nid
5703: and wn.notification_id = na.notification_id
6056: end SetNLSLanguage;
6057:
6058: --
6059: -- Denormalize_Notification
6060: -- Populate the donormalized value to WF_NOTIFICATIONS table according
6061: -- to the language setting of username provided.
6062: -- IN:
6063: -- nid - Notification id
6064: -- username - optional role name, if not provided, use the
6193:
6194: begin
6195: select RECIPIENT_ROLE, FROM_ROLE
6196: into l_user, l_from_role
6197: from WF_NOTIFICATIONS
6198: where NOTIFICATION_ID = nid;
6199: exception
6200: when NO_DATA_FOUND then
6201: wf_core.token('NID', to_char(nid));
6327: end if;
6328: -- Populate the notification values
6329: --
6330: begin
6331: update WF_NOTIFICATIONS
6332: set FROM_USER = l_from_user,
6333: FROM_ROLE = nvl(l_from_role,FROM_ROLE),
6334: TO_USER = l_to_user,
6335: SUBJECT = l_subject,
6367: xmessageName varchar2(30);
6368:
6369: cursor fyiNid is
6370: select WN.NOTIFICATION_ID
6371: from WF_NOTIFICATIONS WN
6372: where MESSAGE_TYPE like xitemtype
6373: and MESSAGE_NAME like xmessageName
6374: and BEGIN_DATE<=begindate
6375: and STATUS = 'OPEN'
6592: SELECT callback , context ,RECIPIENT_ROLE, ORIGINAL_RECIPIENT,
6593: MORE_INFO_ROLE ,from_role, message_type, message_name
6594: into cb, context,l_recip_role , l_orig_recip_role,
6595: l_more_info_role, l_from_role, l_messageType, l_messageName
6596: FROM wf_notifications
6597: WHERE notification_id = nid;
6598:
6599: g_context_recipient_role := l_recip_role;
6600: g_context_original_recipient:= l_orig_recip_role;
6622: -- do not want it hung when some one is doing update.
6623: begin
6624: select MORE_INFO_ROLE
6625: into l_from_role
6626: from WF_NOTIFICATIONS
6627: where NOTIFICATION_ID = nid
6628: for update nowait;
6629: exception
6630: when NO_DATA_FOUND then
6669:
6670: -- if there is a valid session, then we can update the FROM_ROLE
6671: -- and FROM_USER accurately.
6672: if (myusername is not null) then
6673: update WF_NOTIFICATIONS
6674: set MORE_INFO_ROLE = username,
6675: FROM_USER = mydispname,
6676: FROM_ROLE = myusername,
6677: SENT_DATE = SYSDATE,
6684:
6685: -- otherwise, we default to what it should be. Unfortunately, if it
6686: -- is a group role, we will not be able to identify which member I am.
6687: else
6688: update WF_NOTIFICATIONS
6689: set MORE_INFO_ROLE = username,
6690: FROM_USER = TO_USER,
6691: FROM_ROLE = RECIPIENT_ROLE,
6692: SENT_DATE = SYSDATE,
6699: end if;
6700:
6701: Wf_Notification.SetComments(nid, myusername, username, 'QUESTION', action_source, substrb(comment,1,4000));
6702:
6703: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
6704: -- insert into WF_COMMENTS (
6705: -- NOTIFICATION_ID,
6706: -- FROM_ROLE,
6707: -- FROM_USER,
6716: -- sysdate,
6717: -- 'QUESTION',
6718: -- substrb(comment,1,4000),
6719: -- LANGUAGE
6720: -- from WF_NOTIFICATIONS
6721: -- where NOTIFICATION_ID = nid;
6722:
6723: -- bug 2474562
6724: -- else
6752: -- answer at the same time.
6753: begin
6754: select MORE_INFO_ROLE,Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), RECIPIENT_ROLE,FROM_ROLE
6755: into l_from_role, replyby, recipient_role,l_question_role
6756: from WF_NOTIFICATIONS
6757: where NOTIFICATION_ID = nid
6758: and MORE_INFO_ROLE is not null
6759: for update nowait;
6760:
6812: -- shanjgik 01-JUL-03 bug 2887130
6813: -- get the recipient's(one who requested more information) mail preference
6814: mailpref := wf_notification.GetMailPreference (recipient_role, null, null);
6815:
6816: update WF_NOTIFICATIONS
6817: set FROM_USER = replyby,
6818: FROM_ROLE = l_from_role,
6819: MORE_INFO_ROLE = null,
6820: SENT_DATE = SYSDATE,
6844: -- sysdate,
6845: -- 'ANSWER',
6846: -- substrb(comment,1,4000),
6847: -- userenv('LANG')
6848: -- from WF_NOTIFICATIONS
6849: -- where NOTIFICATION_ID = nid;
6850:
6851: -- <
6852: -- if we are here, mean we are going to raise
6863: -- wf_xml.EnqueueNotification(nid);
6864:
6865: -- Bug 2283697
6866: -- To raise an EVENT whenever DML operation is performed on
6867: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
6868: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
6869: wf_event.AddParameterToList('ROLE', username, l_parameterlist);
6870: wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist);
6871: wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType||':'||
6989: SELECT callback, context, recipient_role, original_recipient,
6990: more_info_role ,from_role, message_type, message_name
6991: into l_callback_function, l_context,l_recip_role , l_orig_recip_role,
6992: l_more_info_role, l_from_role, l_messageType, l_messageName
6993: FROM wf_notifications
6994: WHERE notification_id = p_nid;
6995:
6996: -- Setting the Global Context Variables here
6997: g_context_recipient_role := l_recip_role;
7017: -- Check if anyone else is updating the row
7018: begin
7019: select MORE_INFO_ROLE
7020: into l_from_role
7021: from WF_NOTIFICATIONS
7022: where NOTIFICATION_ID = p_nid
7023: for update nowait;
7024: exception
7025: when NO_DATA_FOUND then
7058: -- then we set the FROM_ROLE as the user for which there is a
7059: --routing rule
7060: if (p_routing_rule_user is not null) then
7061: l_routing_rule_user_display := Wf_Directory.GetRoleDisplayName(p_routing_rule_user);
7062: update WF_NOTIFICATIONS
7063: set MORE_INFO_ROLE = p_new_user,
7064: FROM_USER = l_routing_rule_user_display,
7065: FROM_ROLE = p_routing_rule_user,
7066: SENT_DATE = SYSDATE,
7072: where NOTIFICATION_ID = p_nid;
7073: Wf_Notification.SetComments(p_nid, p_routing_rule_user, p_new_user, 'TRANSFER_QUESTION', p_action_source, substrb(p_comment,1,4000));
7074: else
7075: if (l_session_user is not null) then
7076: update WF_NOTIFICATIONS
7077: set MORE_INFO_ROLE = p_new_user,
7078: FROM_USER = l_session_user_display,
7079: FROM_ROLE = l_session_user,
7080: SENT_DATE = SYSDATE,
7087:
7088: -- otherwise, we default to what it should be. Unfortunately, if it
7089: -- is a group role, we will not be able to identify which member I am.
7090: else
7091: update WF_NOTIFICATIONS
7092: set MORE_INFO_ROLE = p_new_user,
7093: FROM_USER = TO_USER,
7094: FROM_ROLE = RECIPIENT_ROLE,
7095: SENT_DATE = SYSDATE,
7121: -- notification entirely
7122: wf_notification.clob_exists := null;
7123:
7124: -- To raise an EVENT whenever DML operation is performed on
7125: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7126: wf_event.AddParameterToList('NOTIFICATION_ID', p_nid, l_parameterlist);
7127: wf_event.AddParameterToList('ROLE', p_new_user, l_parameterlist);
7128: wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, p_nid), l_parameterlist);
7129: wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType||':'||
7203:
7204: -- Getting information pertaining to this NID
7205: select recipient_role, more_info_role, message_type, message_name
7206: into l_recip_role , l_more_info_role, l_messageType, l_messageName
7207: FROM wf_notifications
7208: WHERE notification_id = p_nid;
7209:
7210: open rulecurs;
7211: fetch rulecurs INTO l_ruleAction,l_newRole;
7243: l_more_info_role_display := Wf_Directory.GetRoleDisplayName(l_more_info_role);
7244: -- get mail preference of the user who will respond with more information
7245: l_mail_preference := wf_notification.GetMailPreference (l_recip_role, null, null);
7246:
7247: update WF_NOTIFICATIONS
7248: set FROM_USER = l_more_info_role_display,
7249: FROM_ROLE = l_more_info_role,
7250: MORE_INFO_ROLE = null,
7251: SENT_DATE = SYSDATE,
7298: -- Notification only
7299: begin
7300: select NULL, '#SYNCH'
7301: into itype, ikey
7302: from WF_NOTIFICATIONS
7303: where NOTIFICATION_ID = nid;
7304: exception
7305: when OTHERS then
7306: return(FALSE);
7329: if (itype is null and ikey = '#SYNCH') then
7330: -- this is notification only
7331: begin
7332: select 1 into ans
7333: from WF_NOTIFICATIONS
7334: where IsValidInfoRole.username in (RECIPIENT_ROLE, ORIGINAL_RECIPIENT)
7335: and NOTIFICATION_ID = nid;
7336: exception
7337: when NO_DATA_FOUND then
7349: select 1 into ans
7350: from (
7351: select /*+ leading(grp_id_view) */
7352: RECIPIENT_ROLE , ORIGINAL_RECIPIENT
7353: from WF_NOTIFICATIONS a ,
7354: ( select notification_id group_id
7355: from WF_ITEM_ACTIVITY_STATUSES
7356: where item_type = itype
7357: and item_key = ikey
7452: SELECT callback, context, recipient_role, original_recipient,
7453: more_info_role, from_role, status, message_type, message_name
7454: INTO cb, context, l_recip_role, l_orig_recip_role,
7455: l_more_info_role, l_from_role, l_stat, l_messageType, l_messageName
7456: FROM wf_notifications
7457: WHERE notification_id = nid;
7458:
7459: -- Donot process the request if the notification is not open.
7460: if (l_stat <> 'OPEN') then
7575: -- do not want it hung when some one is doing update.
7576: begin
7577: select MORE_INFO_ROLE, MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID
7578: into l_from_role, l_messageType, l_messageName, l_groupId
7579: from WF_NOTIFICATIONS
7580: where NOTIFICATION_ID = nid
7581: for update nowait;
7582: exception
7583: when NO_DATA_FOUND then
7617: -- as we donot have a user session for the mailer, the only way to
7618: -- find FROM_ROLE and FROM_USER are through the from_addr. If the
7619: -- user name and display name are not available, email address is updated
7620: /* if (myusername is not null) then
7621: update WF_NOTIFICATIONS
7622: set MORE_INFO_ROLE = username,
7623: FROM_USER = mydispname,
7624: FROM_ROLE = myusername
7625: where NOTIFICATION_ID = nid;
7626:
7627: -- otherwise, we default to what it should be. Unfortunately, if it
7628: -- is a group role, we will not be able to identify which member I am.
7629: else */
7630: update WF_NOTIFICATIONS
7631: set MAIL_STATUS = 'MAIL',
7632: MORE_INFO_ROLE = l_username,
7633: FROM_USER = TO_USER,
7634: FROM_ROLE = RECIPIENT_ROLE,
7638:
7639: Wf_Notification.SetComments(nid, myusername, l_username, 'QUESTION', null, substrb(comment,1,4000));
7640: Wf_Notification.Route(nid, 0);
7641:
7642: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
7643: -- insert into WF_COMMENTS (
7644: -- NOTIFICATION_ID,
7645: -- FROM_ROLE,
7646: -- FROM_USER,
7655: -- sysdate,
7656: -- 'QUESTION',
7657: -- substrb(comment,1,4000),
7658: -- LANGUAGE
7659: -- from WF_NOTIFICATIONS
7660: -- where NOTIFICATION_ID = nid;
7661: end if;
7662: end if;
7663:
7677: begin
7678: select MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7679: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role
7680: into l_from_role, replyby, l_messageType, l_messageName, l_groupId, l_question_role
7681: from WF_NOTIFICATIONS
7682: where NOTIFICATION_ID = nid
7683: and MORE_INFO_ROLE is not null
7684: for update nowait;
7685:
7722: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
7723: 'Updating ANSWER');
7724: end if;
7725:
7726: update WF_NOTIFICATIONS
7727: set MAIL_STATUS = 'MAIL',
7728: FROM_USER = replyby,
7729: FROM_ROLE = l_from_role,
7730: MORE_INFO_ROLE = null,
7750: -- sysdate,
7751: -- 'ANSWER',
7752: -- substrb(comment,1,4000),
7753: -- userenv('LANG')
7754: -- from WF_NOTIFICATIONS
7755: -- where NOTIFICATION_ID = nid;
7756:
7757: -- we are here, mean we are going to raise
7758: -- oracle.apps.wf.notification.answer event.
7767: -- wf_xml.EnqueueNotification(nid);
7768:
7769: -- Bug 2283697
7770: -- To raise an EVENT whenever DML operation is performed on
7771: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7772: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7773:
7774: -- username MAY be a display name
7775: wf_event.AddParameterToList('ROLE', nvl(l_username, username), l_parameterlist);
7860: Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7861: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role, callback, context
7862: into l_orig_recip_role, l_recipient_role, l_from_role,
7863: replyby, l_messageType, l_messageName, l_groupId, l_question_role, cb, context
7864: from WF_NOTIFICATIONS
7865: where NOTIFICATION_ID = nid
7866: and MORE_INFO_ROLE is not null
7867: for update nowait;
7868:
7908:
7909: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7910: 'Updating ANSWER');
7911:
7912: update WF_NOTIFICATIONS
7913: set MAIL_STATUS = 'MAIL',
7914: FROM_USER = moreinforesponder,
7915: FROM_ROLE = moreinforesponder,
7916: MORE_INFO_ROLE = null,
7926: -- wf_xml.EnqueueNotification(nid);
7927:
7928: -- Bug 2283697
7929: -- To raise an EVENT whenever DML operation is performed on
7930: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7931: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7932: -- skilaru 12-MAR-04 In UpdateInfo2 username would be null in Answer mode
7933: -- to keep the behaviour same just pass null as ROLE..
7934: wf_event.AddParameterToList('ROLE', null, l_parameterlist);
8649: begin
8650: -- <7443088> improved query
8651: select NAME into aname from
8652: (select WMA.NAME
8653: from WF_NOTIFICATIONS WN,
8654: WF_MESSAGE_ATTRIBUTES WMA,
8655: WF_NOTIFICATION_ATTRIBUTES NA
8656: where WN.NOTIFICATION_ID = nid
8657: and wn.notification_id = na.notification_id
8891: lv_fwk_body := 'N';
8892:
8893: select nvl(WM.BODY, ''), nvl(WM.HTML_BODY, '')
8894: into lv_body, lv_html_body
8895: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
8896: where N.NOTIFICATION_ID = nid
8897: and N.MESSAGE_NAME = WM.NAME
8898: and N.MESSAGE_TYPE = WM.TYPE;
8899:
8963:
8964: -- Cursur to check for each message Attribute
8965: cursor cur_msg_attrs(nid number, msgToken varchar2) is
8966: select WNA.NAME, WNA.TEXT_VALUE, WMA.TYPE
8967: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
8968: WF_MESSAGE_ATTRIBUTES_VL WMA
8969: where WNA.NOTIFICATION_ID = nid
8970: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
8971: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
9036:
9037: begin
9038: --skilaru 16-July-03
9039: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
9040: --mapped to WF_NOTIFICATIONS.GROUP_ID
9041: SELECT group_id
9042: INTO l_group_nid
9043: FROM wf_notifications
9044: WHERE notification_id = nid;
9039: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
9040: --mapped to WF_NOTIFICATIONS.GROUP_ID
9041: SELECT group_id
9042: INTO l_group_nid
9043: FROM wf_notifications
9044: WHERE notification_id = nid;
9045:
9046: for act_status_row in act_info_statuses_cursor( l_group_nid ) loop
9047: l_itype := act_status_row.ITEM_TYPE;
9483:
9484: begin
9485: SELECT message_type, message_name, status, mail_status, nvl(more_info_role, recipient_role) recipient_role, group_id
9486: INTO l_message_type, l_message_name, l_status, l_mail_status, l_recipient_role, l_group_id
9487: FROM wf_notifications
9488: WHERE notification_id = p_nid;
9489: exception
9490: when no_data_found then
9491: wf_core.token('NID', to_char(p_nid));
9516: wf_core.raise('WFNTF_EMAIL_NOTSENT');
9517: end if;
9518:
9519: -- Raise the event to send an e-mail
9520: UPDATE wf_notifications
9521: SET mail_status = 'MAIL'
9522: WHERE notification_id = p_nid;
9523:
9524: Wf_Event.AddParameterToList('NOTIFICATION_ID', p_nid, l_paramlist);
9628: is
9629: --Get the nids in curs_nid which have the attribute document_id
9630: cursor curs_nid(l_doc_id varchar2,l_item_type varchar2,l_item_key varchar2) is
9631: select wfn.notification_id
9632: from wf_item_activity_statuses wfas, wf_notifications wfn , wf_notification_attributes wfna
9633: where wfna.name = '#DOCUMENT_ID'
9634: and wfna.text_value = l_doc_id
9635: and wfas.item_type = l_item_type
9636: and wfas.item_key = l_item_key
9693: cp_from_date date,
9694: cp_to_date date)
9695: IS
9696: SELECT notification_id
9697: FROM wf_notifications wn
9698: WHERE wn.status = 'OPEN'
9699: AND wn.mail_status = 'FAILED'
9700: AND wn.recipient_role like nvl(cp_role, '%')
9701: AND wn.message_type like nvl(cp_msg_type, '%')
9709: cp_from_date date,
9710: cp_to_date date)
9711: IS
9712: SELECT notification_id
9713: FROM wf_notifications wn
9714: WHERE wn.status = 'OPEN'
9715: AND wn.mail_status = 'ERROR'
9716: AND wn.recipient_role like nvl(cp_role, '%')
9717: AND wn.message_type like nvl(cp_msg_type, '%')
9719: AND (cp_to_date is null or wn.begin_date <= cp_to_date )
9720: AND NOT EXISTS (
9721: SELECT 1
9722: FROM wf_message_attributes wma,
9723: wf_notifications wn2
9724: WHERE wn2.notification_id = wn.notification_id
9725: AND wma.message_type = wn2.message_type
9726: AND wma.message_name = wn2.message_name
9727: AND wma.subtype = 'RESPOND'
9813:
9814: SELECT count(1)
9815: INTO l_resp_attr_cnt
9816: FROM wf_message_attributes wma,
9817: wf_notifications wn
9818: WHERE wn.notification_id = nid
9819: AND wma.message_type = wn.message_type
9820: AND wma.message_name = wn.message_name
9821: AND wma.subtype = 'RESPOND'
9867: select WNA.NAME, WMA.TYPE, WNA.TEXT_VALUE, WNA.NUMBER_VALUE,
9868: WNA.DATE_VALUE
9869: from WF_NOTIFICATION_ATTRIBUTES WNA,
9870: WF_MESSAGE_ATTRIBUTES WMA,
9871: WF_NOTIFICATIONS WN
9872: where WNA.NOTIFICATION_ID = nid
9873: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
9874: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
9875: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
9937: select N.CALLBACK, N.CONTEXT, N.STATUS, N.USER_COMMENT,
9938: N.RECIPIENT_ROLE, N.ORIGINAL_RECIPIENT,N.MORE_INFO_ROLE, N.FROM_ROLE, N.LANGUAGE
9939: into respond2.callback, respond2.context, respond2.status, newcomment,
9940: l_recip_role,l_orig_recip_role,l_more_info_role, l_from_role, l_language
9941: from WF_NOTIFICATIONS N
9942: where N.NOTIFICATION_ID = nid
9943: for update nowait;
9944: exception
9945: when no_data_found then
10062: -- end if;
10063: -- end if;
10064:
10065: -- Mark notification closed
10066: update WF_NOTIFICATIONS
10067: set STATUS = 'CLOSED',
10068: MAIL_STATUS = NULL,
10069: END_DATE = sysdate,
10070: -- RESPONDER = respond2.responder
10079: action_source, respond_comment);
10080:
10081: --Bug 2283697
10082: --To raise an EVENT whenever DML operation is performed on
10083: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
10084: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
10085: wf_event.AddParameterToList('RESPONDER',respond2.responder,l_parameterlist);
10086:
10087: -- AppSearch
10171: l_event wf_event_t;
10172: l_parameterlist wf_parameter_list_t := wf_parameter_list_t() ;
10173: l_agent wf_agent_t := null ;
10174:
10175: l_itemType WF_NOTIFICATIONS.MESSAGE_TYPE%TYPE := null;
10176: l_lookupCode_cnt number := 0;
10177: l_lookupType FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE := 'WF_NTF_RESP_DEFER_ITEM_TYPES';
10178: l_ntf_status WF_NOTIFICATIONS.STATUS%TYPE;
10179:
10174:
10175: l_itemType WF_NOTIFICATIONS.MESSAGE_TYPE%TYPE := null;
10176: l_lookupCode_cnt number := 0;
10177: l_lookupType FND_LOOKUP_VALUES.LOOKUP_TYPE%TYPE := 'WF_NTF_RESP_DEFER_ITEM_TYPES';
10178: l_ntf_status WF_NOTIFICATIONS.STATUS%TYPE;
10179:
10180: l_dummy_var varchar2(400);
10181: response_found boolean;
10182:
10298:
10299: begin
10300: select N.CALLBACK, N.CONTEXT
10301: into Complete.callback, Complete.context
10302: from WF_NOTIFICATIONS N
10303: where N.NOTIFICATION_ID = p_nid
10304: for update nowait;
10305: exception
10306: when no_data_found then