[Home] [Help]
1: package body WF_NOTIFICATION as
2: /* $Header: wfntfb.pls 120.113.12020000.8 2013/03/20 04:46:53 alsosa ship $ */
3:
4: --
5: -- Constants
13: -- session, caused by Routing Rules
14: g_comments_seq pls_integer := 0;
15:
16: -- logging variable
17: g_plsqlName varchar2(35) := 'wf.plsql.WF_NOTIFICATION.';
18:
19: --
20: -- Private Variables
21: --
265: rs := rs||wf_core.newline||''||wf_core.newline||'';
266:
267: exception
268: when OTHERS then
269: wf_core.context('Wf_Notification', 'NTF_Table',to_char(col),l_type);
270: raise;
271: end NTF_Table;
272:
273: --
338: NA.NUMBER_VALUE,
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
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
359: end;
360:
361: if (l_type = 'DATE') then
362: --
363: -- l_format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided and matches
364: -- wf_notification_util.G_NID), session user's WFDS preference, wf_core.nls_date_format.
365: l_text := wf_notification_util.GetCalendarDate(nid, l_datev, l_format, false);
366: elsif (l_type = 'NUMBER') then
367: if (l_format is null) then
360:
361: if (l_type = 'DATE') then
362: --
363: -- l_format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided and matches
364: -- wf_notification_util.G_NID), session user's WFDS preference, wf_core.nls_date_format.
365: l_text := wf_notification_util.GetCalendarDate(nid, l_datev, l_format, false);
366: elsif (l_type = 'NUMBER') then
367: if (l_format is null) then
368: l_text := to_char(l_numberv);
361: if (l_type = 'DATE') then
362: --
363: -- l_format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided and matches
364: -- wf_notification_util.G_NID), session user's WFDS preference, wf_core.nls_date_format.
365: l_text := wf_notification_util.GetCalendarDate(nid, l_datev, l_format, false);
366: elsif (l_type = 'NUMBER') then
367: if (l_format is null) then
368: l_text := to_char(l_numberv);
369: else
398: end if;
399:
400: -- make sure the text does not carry any HTML chars... though NUMBER is safe
401: -- others possibly could carry.
402: if (disptype = wf_notification.doc_html) then
403: l_text := substrb(Wf_Core.SubstituteSpecialChars(l_text), 1, 4000);
404: end if;
405:
406: -- display
404: end if;
405:
406: -- display
407: if (l_dispname is not null) then
408: if (disptype = wf_notification.doc_html) then
409: l_dispname := substrb(Wf_Core.SubstituteSpecialChars(l_dispname), 1, 80);
410: if (table_type = 'N') then
411: cells(i) := 'E:'||l_dispname;
412: i := i+1;
425:
426: p1 := p2+1;
427: end loop;
428:
429: if (disptype = wf_notification.doc_html) then
430: if (table_type = 'N') then
431: table_width := '100%';
432: else
433: table_width := '70%';
441: return(result);
442:
443: exception
444: when OTHERS then
445: wf_core.context('Wf_Notification','Wf_Msg_Attr',to_char(nid),attrs);
446: raise;
447: end wf_msg_attr;
448:
449:
450: -- Wf_Ntf_History
451: -- Construct Action History table for a given notification from the WF_COMMENTS table
452: -- The table consists of actions like Reassign, More Info Request and Respond and related
453: -- comments. The user can restrict the rows in the table using the following format.
454: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
455: -- Example:
456: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
457: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
458: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
452: -- The table consists of actions like Reassign, More Info Request and Respond and related
453: -- comments. The user can restrict the rows in the table using the following format.
454: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
455: -- Example:
456: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
457: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
458: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
459: --
460: -- InPut
453: -- comments. The user can restrict the rows in the table using the following format.
454: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
455: -- Example:
456: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
457: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
458: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
459: --
460: -- InPut
461: -- nid - Notification Id
454: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
455: -- Example:
456: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
457: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
458: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
459: --
460: -- InPut
461: -- nid - Notification Id
462: -- disptype - text/plain or text/html
488: l_hide_reassign := 'N';
489: l_hide_requestinfo := 'N';
490: end;
491:
492: Wf_Notification.GetComments2(p_nid => nid, p_display_type => disptype,
493: p_hide_reassign => l_hide_reassign,
494: p_hide_requestinfo => l_hide_requestinfo,
495: p_action_history => l_action_history);
496: return l_action_history;
596:
597: j := 1;
598: -- title
599: cells(j) := wf_core.translate('NUM');
600: if (disptype = wf_notification.doc_html) then
601: cells(j) := 'S10%:'||cells(j);
602: end if;
603: j := j+1;
604: cells(j) := wf_core.translate('NAME');
601: cells(j) := 'S10%:'||cells(j);
602: end if;
603: j := j+1;
604: cells(j) := wf_core.translate('NAME');
605: if (disptype = wf_notification.doc_html) then
606: cells(j) := 'S:'||cells(j);
607: end if;
608: j := j+1;
609: cells(j) := wf_core.translate('ACTION');
606: cells(j) := 'S:'||cells(j);
607: end if;
608: j := j+1;
609: cells(j) := wf_core.translate('ACTION');
610: if (disptype = wf_notification.doc_html) then
611: cells(j) := 'S:'||cells(j);
612: end if;
613: j := j+1;
614: cells(j) := wf_core.translate('ACTION_DATE');
611: cells(j) := 'S:'||cells(j);
612: end if;
613: j := j+1;
614: cells(j) := wf_core.translate('ACTION_DATE');
615: if (disptype = wf_notification.doc_html) then
616: cells(j) := 'S:'||cells(j);
617: end if;
618: j := j+1;
619: cells(j) := wf_core.translate('NOTE');
616: cells(j) := 'S:'||cells(j);
617: end if;
618: j := j+1;
619: cells(j) := wf_core.translate('NOTE');
620: if (disptype = wf_notification.doc_html) then
621: cells(j) := 'S:'||cells(j);
622: end if;
623: j := j+1;
624:
626: for histr in hist0c(l_itype, l_ikey, l_actid) loop
627: cells(j) := to_char(histr.notification_id);
628: j := j+1;
629: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
630: if (disptype = wf_notification.doc_html) then
631: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
632: else
633: cells(j) := role_info_tbl(1).display_name;
634: end if;
627: cells(j) := to_char(histr.notification_id);
628: j := j+1;
629: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
630: if (disptype = wf_notification.doc_html) then
631: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
632: else
633: cells(j) := role_info_tbl(1).display_name;
634: end if;
635: j := j+1;
639: l_result_type := histr.result_type;
640: l_result_code := histr.activity_result_code;
641: l_action := wf_core.activity_result(l_result_type, l_result_code);
642: end if;
643: if (disptype = wf_notification.doc_html) then
644: if (l_action is null) then
645: cells(j) := 'S: ';
646: else
647: cells(j) := 'S:'||l_action;
649: else
650: cells(j) := l_action;
651: end if;
652: j := j+1;
653: if (disptype = wf_notification.doc_html) then
654: cells(j) := 'S:'||to_char(histr.act_date);
655: else
656: cells(j) := to_char(histr.act_date);
657: end if;
656: cells(j) := to_char(histr.act_date);
657: end if;
658: j := j+1;
659: begin
660: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
661: if (disptype = wf_notification.doc_html) then
662: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
663: end if;
664: cells(j) := l_note;
657: end if;
658: j := j+1;
659: begin
660: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
661: if (disptype = wf_notification.doc_html) then
662: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
663: end if;
664: cells(j) := l_note;
665: exception
658: j := j+1;
659: begin
660: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
661: if (disptype = wf_notification.doc_html) then
662: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
663: end if;
664: cells(j) := l_note;
665: exception
666: when OTHERS then
666: when OTHERS then
667: cells(j) := null;
668: wf_core.clear;
669: end;
670: if (disptype = wf_notification.doc_html) then
671: if (cells(j) is null) then
672: cells(j) := 'S: ';
673: else
674: cells(j) := 'S:'||cells(j);
682: for histr in histc(l_itype, l_ikey, l_actid) loop
683: cells(j) := to_char(histr.notification_id);
684: j := j+1;
685: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
686: if (disptype = wf_notification.doc_html) then
687: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
688: else
689: cells(j) := role_info_tbl(1).display_name;
690: end if;
683: cells(j) := to_char(histr.notification_id);
684: j := j+1;
685: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
686: if (disptype = wf_notification.doc_html) then
687: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
688: else
689: cells(j) := role_info_tbl(1).display_name;
690: end if;
691: j := j+1;
695: l_result_type := histr.result_type;
696: l_result_code := histr.activity_result_code;
697: l_action := wf_core.activity_result(l_result_type, l_result_code);
698: end if;
699: if (disptype = wf_notification.doc_html) then
700: if (l_action is null) then
701: cells(j) := 'S: ';
702: else
703: cells(j) := 'S:'||l_action;
705: else
706: cells(j) := l_action;
707: end if;
708: j := j+1;
709: if (disptype = wf_notification.doc_html) then
710: cells(j) := 'S:'||to_char(histr.act_date);
711: else
712: cells(j) := to_char(histr.act_date);
713: end if;
712: cells(j) := to_char(histr.act_date);
713: end if;
714: j := j+1;
715: begin
716: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
717: if (disptype = wf_notification.doc_html) then
718: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
719: end if;
720: cells(j) := l_note;
713: end if;
714: j := j+1;
715: begin
716: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
717: if (disptype = wf_notification.doc_html) then
718: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
719: end if;
720: cells(j) := l_note;
721: exception
714: j := j+1;
715: begin
716: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
717: if (disptype = wf_notification.doc_html) then
718: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
719: end if;
720: cells(j) := l_note;
721: exception
722: when OTHERS then
722: when OTHERS then
723: cells(j) := null;
724: wf_core.clear;
725: end;
726: if (disptype = wf_notification.doc_html) then
727: if (cells(j) is null) then
728: cells(j) := 'S: ';
729: else
730: cells(j) := 'S:'||cells(j);
748: when OTHERS then
749: raise;
750: end;
751: wf_directory.GetRoleInfo2(l_owner_role, role_info_tbl);
752: if (disptype = wf_notification.doc_html) then
753: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
754: else
755: cells(j) := role_info_tbl(1).display_name;
756: end if;
749: raise;
750: end;
751: wf_directory.GetRoleInfo2(l_owner_role, role_info_tbl);
752: if (disptype = wf_notification.doc_html) then
753: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
754: else
755: cells(j) := role_info_tbl(1).display_name;
756: end if;
757: j := j+1;
754: else
755: cells(j) := role_info_tbl(1).display_name;
756: end if;
757: j := j+1;
758: if (disptype = wf_notification.doc_html) then
759: cells(j) := 'S:'||wf_core.translate('SUBMIT');
760: else
761: cells(j) := wf_core.translate('SUBMIT');
762: end if;
760: else
761: cells(j) := wf_core.translate('SUBMIT');
762: end if;
763: j := j+1;
764: if (disptype = wf_notification.doc_html) then
765: cells(j) := 'S:'||to_char(l_begin_date);
766: else
767: cells(j) := to_char(l_begin_date);
768: end if;
766: else
767: cells(j) := to_char(l_begin_date);
768: end if;
769: j := j+1;
770: if (disptype = wf_notification.doc_html) then
771: cells(j) := 'S: ';
772: else
773: cells(j) := null;
774: end if;
776: -- calculate the sequence
777: -- Only after we know the number of rows, then we can put the squence
778: -- number on for each row.
779: for k in 0..i loop
780: if (disptype = wf_notification.doc_html) then
781: cells((k+1)*5+1) := 'C:'||to_char(i-k);
782: else
783: cells((k+1)*5+1) := to_char(i-k);
784: end if;
783: cells((k+1)*5+1) := to_char(i-k);
784: end if;
785: end loop;
786:
787: if (disptype = wf_notification.doc_html) then
788: table_width := '100%';
789: NTF_Table(cells=>cells,
790: col=>5,
791: type=>'H'||l_table_direction,
802:
803: return(result);
804: exception
805: when OTHERS then
806: wf_core.context('Wf_Notification', 'Wf_NTF_History', to_char(nid));
807: raise;
808: end wf_ntf_history;
809: **
810: ** End of obsoleted procedure WF_NTF_HISTORY
813: --
814: -- runFuncOnBody
815: -- NOTE
816: -- Attempt to find, parse and replace the string
817: -- WF_NOTIFICATION(F,P1,P2,...)
818: -- F = function to run
819: -- P1,P2,... = comma delimited parameter list
820: --
821: function runFuncOnBody(nid in number,
840:
841: p1:=1;
842: alldone:=false;
843: while (not alldone) loop
844: fname := 'WF_NOTIFICATION('; -- lengthb(fname) is 16
845:
846: p1 := instrb(l_body, fname, p1);
847: if (p1 <> 0) then
848: p2 := instrb(l_body, ')', p1);
868: end if;
869:
870: -- do not replace a string with itself.
871: -- if rs is null, then there is nothing to display for Action/Notifications History
872: -- or Attributes table. We would not want WF_NOTIFICATION(ATTRS,...) or
873: -- WF_NOTIFICATION(HISTORY) to appear in the notification as is.
874: if (rs is null or rs <> func) then
875: l_body := replace(l_body, func, rs);
876: end if;
869:
870: -- do not replace a string with itself.
871: -- if rs is null, then there is nothing to display for Action/Notifications History
872: -- or Attributes table. We would not want WF_NOTIFICATION(ATTRS,...) or
873: -- WF_NOTIFICATION(HISTORY) to appear in the notification as is.
874: if (rs is null or rs <> func) then
875: l_body := replace(l_body, func, rs);
876: end if;
877:
889: return(l_body);
890:
891: exception
892: when OTHERS then
893: wf_core.context('Wf_Notification', 'runFuncOnBody', to_char(nid), disptype);
894: raise;
895: end runFuncOnBody;
896:
897: -- More Info mailer support
941: -- Bug 13060615. Check if the expected user is the one who responded via e-mail
942: if (preferred_name is not null) then
943: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
944: wf_log_pkg.string(wf_log_pkg.level_statement,
945: 'wf.plsql.WF_NOTIFICATION.GetUserfromEmail',
946: 'Obtaining user name using preferred name '||preferred_name);
947: end if;
948: if WF_DIRECTORY.UserActive(preferred_name) then
949: WF_DIRECTORY.GetRoleInfo2(preferred_name, l_role_info_tbl);
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
1059: when OTHERS then --no_data_found or invalid_number
1060: itemtype:=null;
1061: itemkey:=null;
1062: actid:=null;
1063: wf_core.context('Wf_Notification', 'validate_context', context);
1064: raise;
1065: end validate_context;
1066:
1067:
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));
1101: end;
1102:
1103: -- Insert new attribute
1104: begin
1105: insert into WF_NOTIFICATION_ATTRIBUTES (
1106: NOTIFICATION_ID,
1107: NAME,
1108: TEXT_VALUE,
1109: NUMBER_VALUE,
1123: end;
1124:
1125: exception
1126: when others then
1127: wf_core.context('Wf_Notification', 'AddAttr', to_char(nid), aname);
1128: raise;
1129: end AddAttr;
1130:
1131: --
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
1180: end;
1181:
1182: -- Update attribute value in appropriate type column.
1183: if (atype = 'NUMBER') then
1184: update WF_NOTIFICATION_ATTRIBUTES
1185: set NUMBER_VALUE = decode(format,
1186: '', to_number(avalue),
1187: to_number(avalue, format))
1188: where NOTIFICATION_ID = nid
1190: elsif (atype = 'DATE') then
1191: -- 4477386 gscc date format requirement change
1192: -- do not use a cached value, this allows nls change within the
1193: -- same session to be seen right away.
1194: update WF_NOTIFICATION_ATTRIBUTES
1195: set DATE_VALUE = decode(format,
1196: '',to_date(avalue,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')),
1197: to_date(avalue, format))
1198: where NOTIFICATION_ID = nid
1200: elsif (atype = 'VARCHAR2') then
1201: -- VARCHAR2
1202: -- Set the text value directly with no translation.
1203: -- bug 1996299 - JWSMITH , changes substr to substrb for korean char
1204: update WF_NOTIFICATION_ATTRIBUTES
1205: set TEXT_VALUE = decode(format,
1206: '', avalue,
1207: substrb(avalue, 1, to_number(format)))
1208: where NOTIFICATION_ID = nid
1235: end if;
1236: end if;
1237:
1238: -- Set the text value with internal role name
1239: update WF_NOTIFICATION_ATTRIBUTES
1240: set TEXT_VALUE = rname
1241: where NOTIFICATION_ID = nid
1242: and NAME = aname;
1243: else
1242: and NAME = aname;
1243: else
1244: -- LOOKUP, FORM, URL, DOCUMENT, misc type.
1245: -- Set the text value.
1246: update WF_NOTIFICATION_ATTRIBUTES
1247: set TEXT_VALUE = avalue
1248: where NOTIFICATION_ID = nid
1249: and NAME = aname;
1250: end if;
1256: end if;
1257:
1258: -- Redenormalize if attribute being updated is #FROM_ROLE
1259: if (aname = '#FROM_ROLE') then
1260: Wf_Notification.Denormalize_Notification(nid);
1261: end if;
1262:
1263: -- Bug 2437347 raising event after DML operation on WF_NOTIFICATION_ATTRIBUTES
1264: if (aname = 'SENDER') then
1259: if (aname = '#FROM_ROLE') then
1260: Wf_Notification.Denormalize_Notification(nid);
1261: end if;
1262:
1263: -- Bug 2437347 raising event after DML operation on WF_NOTIFICATION_ATTRIBUTES
1264: if (aname = 'SENDER') then
1265: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
1266: wf_event.AddParameterToList(aname, avalue, l_parameterlist);
1267:
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;
1287: wf_event.addParameterToList('PK_VALUE_1', nid, l_parameterlist);
1288: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
1289: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
1290: -- Raise the event
1291: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.setattrtext',
1292: p_event_key => to_char(nid),
1293: p_parameters => l_parameterlist);
1294: end if;
1295:
1294: end if;
1295:
1296: exception
1297: when others then
1298: wf_core.context('Wf_Notification', 'SetAttrText', to_char(nid),
1299: aname, avalue);
1300: raise;
1301: end SetAttrText;
1302:
1320: wf_core.raise('WFSQL_ARGS');
1321: end if;
1322:
1323: -- Update attribute value
1324: update WF_NOTIFICATION_ATTRIBUTES
1325: set NUMBER_VALUE = avalue
1326: where NOTIFICATION_ID = nid and NAME = aname;
1327:
1328: if (SQL%NOTFOUND) then
1332: end if;
1333:
1334: exception
1335: when others then
1336: wf_core.context('Wf_Notification', 'SetAttrNumber', to_char(nid),
1337: aname, to_char(avalue));
1338: raise;
1339: end SetAttrNumber;
1340:
1358: wf_core.raise('WFSQL_ARGS');
1359: end if;
1360:
1361: -- Update attribute value
1362: update WF_NOTIFICATION_ATTRIBUTES
1363: set DATE_VALUE = avalue
1364: where NOTIFICATION_ID = nid and NAME = aname;
1365:
1366: if (SQL%NOTFOUND) then
1370: end if;
1371:
1372: exception
1373: when others then
1374: wf_core.context('Wf_Notification', 'SetAttrDate', to_char(nid),
1375: aname, to_char(avalue));
1376: raise;
1377: end SetAttrDate;
1378:
1469: buf := replace(buf, '"', l_amp||'quot;');
1470: return buf;
1471: exception
1472: when others then
1473: wf_core.context('Wf_Notification', 'SubstituteSpecialChars');
1474: raise;
1475: end SubstituteSpecialChars;
1476:
1477: --
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
1569:
1570: -- Bug 2843136
1571: -- Replace '&' but also '&' only if it hasn't been already substituted
1572: -- This is to prevent something like '&' from happening
1573: if (disptype = wf_notification.doc_html) then
1574: -- (instr(local_text,'&'||not_attr_row.name) = 0) AND
1575: -- (instr(value,'&') = 0)) then
1576:
1577: -- bug 6025162 - SubstituteSpecialChars function substitutes only those
1597: -- end if;
1598:
1599: -- <
1600: -- from GETURLTEXT, GETSHORTTEXT and getShortBody etc..
1601: value := wf_notification_util.getCalendarDate(nid,
1602: not_attr_row.date_value,
1603: not_attr_row.format, false);
1604:
1605: elsif (not_attr_row.type = 'FORM') then
1610: params := instr(value, ':');
1611: if (params <> 0) then
1612: value := not_attr_row.display_name||' ( '||
1613: substr(value, 1, params)||
1614: wf_notification.GetTextInternal(substr(value, params+1), nid,
1615: target, FALSE, FALSE, 'text/plain')||' )';
1616: end if;
1617: end if;
1618: elsif ((not_attr_row.type = 'URL') and (not urlmode) ) then
1625: params := instr(value, '?');
1626: if (params <> 0) then
1627: value := not_attr_row.display_name||' ( '||
1628: substr(value, 1, params)||
1629: wf_notification.GetTextInternal(substr(value, params+1), nid,
1630: target, TRUE, FALSE, 'text/plain')||' )';
1631: end if;
1632: end if;
1633: elsif (not_attr_row.type = 'ROLE') then
1728: end if;
1729: return(local_text);
1730: exception
1731: when others then
1732: wf_core.context('Wf_Notification','GetTextInternal', to_char(nid), disptype);
1733: raise;
1734: end GetTextInternal;
1735:
1736:
1791: return value;
1792:
1793: exception
1794: when others then
1795: wf_core.context('Wf_Notification', 'SetFrameworkAgent', url);
1796: end;
1797:
1798: --
1799: -- GetText
1817: return varchar2
1818: is
1819: begin
1820: -- Calling original GetText logic to substitute all tokens
1821: return wf_notification.GetText2(some_text, nid, disptype, true);
1822: end GetText;
1823:
1824: --
1825: -- GetText2 (INTERNAL ONLY)
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
1908: to_number(not_attr_row.format));
1909: end if;
1910:
1911: -- JWSMITH bug 1725916 - add BR to attribute value
1912: if (disptype=wf_notification.doc_html) then
1913: -- bug 6025162 - SubstituteSpecialChars function substitutes only those
1914: -- characters that really require subsstitution. Any valid occurences
1915: -- will be retained. No validation required from calling program.
1916:
1929: value := to_char(not_attr_row.number_value, not_attr_row.format);
1930: end if;
1931: elsif (not_attr_row.type = 'DATE') then
1932: --
1933: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1934: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1935: -- and wf_core.nls_date_format.
1936: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1937: , not_attr_row.format, false);
1930: end if;
1931: elsif (not_attr_row.type = 'DATE') then
1932: --
1933: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1934: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1935: -- and wf_core.nls_date_format.
1936: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1937: , not_attr_row.format, false);
1938:
1932: --
1933: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1934: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1935: -- and wf_core.nls_date_format.
1936: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1937: , not_attr_row.format, false);
1938:
1939: elsif (not_attr_row.type = 'FORM') then
1940: -- FORM is display_name (function), with parameters of function
1943: params := instr(value, ':');
1944: if (params <> 0) then
1945: value := not_attr_row.display_name||' ( '||
1946: substr(value, 1, params)||
1947: wf_notification.GetTextInternal(substr(value,params+1), nid,
1948: target, FALSE, FALSE, 'text/plain')||' )';
1949: end if;
1950:
1951: if (disptype = wf_notification.doc_html) then
1947: wf_notification.GetTextInternal(substr(value,params+1), nid,
1948: target, FALSE, FALSE, 'text/plain')||' )';
1949: end if;
1950:
1951: if (disptype = wf_notification.doc_html) then
1952: -- Bug 4634849
1953: -- Do not display potentially harmful text
1954: begin
1955: l_dummy := wf_core.CheckIllegalChars(value,true,';<>()');
1967: -- URL is display_name (url), with parameters of url
1968: -- recursively token-substituted if needed.
1969: value := not_attr_row.text_value;
1970: target := substr(nvl(not_attr_row.format, '_top'), 1, 16);
1971: value := wf_notification.SetFrameworkAgent(value);
1972: params := instr(value, '?');
1973: if (params <> 0) then
1974: value := substr(value, 1, params)||
1975: wf_notification.GetTextInternal(substr(value,params+1), nid,
1971: value := wf_notification.SetFrameworkAgent(value);
1972: params := instr(value, '?');
1973: if (params <> 0) then
1974: value := substr(value, 1, params)||
1975: wf_notification.GetTextInternal(substr(value,params+1), nid,
1976: target, TRUE, FALSE, 'text/plain');
1977: end if;
1978:
1979: if (disptype = wf_notification.doc_html) then
1975: wf_notification.GetTextInternal(substr(value,params+1), nid,
1976: target, TRUE, FALSE, 'text/plain');
1977: end if;
1978:
1979: if (disptype = wf_notification.doc_html) then
1980: -- Bug 4634849
1981: -- Do not display potentially harmful url
1982: begin
1983: if (not wf_core.CheckIllegalChars(value,true, ';<>"')) then
2065: email_address := nvl(role_info_tbl(1).email_address,
2066: not_attr_row.text_value);
2067: end if;
2068:
2069: if (disptype = wf_notification.doc_html) then
2070:
2071: value := ''||value||'';
2072:
2073: end if;
2075: else
2076: -- All others default to text_value
2077: value := not_attr_row.text_value;
2078:
2079: if (disptype = wf_notification.doc_html) then
2080: value := wf_core.substitutespecialchars(value);
2081: end if;
2082: end if;
2083:
2106: to_char(nid)), 1, 32000);
2107: return(local_text);
2108: exception
2109: when others then
2110: wf_core.context('Wf_Notification','GetText2', to_char(nid), disptype);
2111: raise;
2112: -- return(some_text);
2113: end GetText2;
2114:
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
2240: end;
2241:
2242: exception
2243: when others then
2244: wf_core.context('Wf_Notification', 'GetAttrInfo', to_char(nid),
2245: aname);
2246: raise;
2247: end GetAttrInfo;
2248:
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
2301: select decode(format,
2302: '', to_char(WNA.NUMBER_VALUE),
2303: to_char(WNA.NUMBER_VALUE, format))
2304: into lvalue
2305: from WF_NOTIFICATION_ATTRIBUTES WNA
2306: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2307: elsif (atype = 'DATE') then
2308: --
2309: select DATE_VALUE into l_valDate
2306: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2307: elsif (atype = 'DATE') then
2308: --
2309: select DATE_VALUE into l_valDate
2310: from WF_NOTIFICATION_ATTRIBUTES WNA
2311: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2312:
2313: lvalue := wf_notification_util.GetCalendarDate(nid, l_valDate, format, false);
2314:
2309: select DATE_VALUE into l_valDate
2310: from WF_NOTIFICATION_ATTRIBUTES WNA
2311: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2312:
2313: lvalue := wf_notification_util.GetCalendarDate(nid, l_valDate, format, false);
2314:
2315: else
2316: -- VARCHAR2, LOOKUP, FORM, or URL type.
2317: select WNA.TEXT_VALUE
2315: else
2316: -- VARCHAR2, LOOKUP, FORM, or URL type.
2317: select WNA.TEXT_VALUE
2318: into lvalue
2319: from WF_NOTIFICATION_ATTRIBUTES WNA
2320: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2321:
2322: -- Recursively substitute attributes in parameter portion of
2323: -- FORM and URL type attributes.
2327: -- FORM params are after ':'
2328: params := instr(lvalue, ':');
2329: if (params <> 0) then
2330: lvalue := substr(lvalue, 1, params)||
2331: wf_notification.GetShortText(substr(lvalue,
2332: params+1), nid);
2333: end if;
2334: elsif (atype = 'URL') then
2335: -- URL params are after '?'
2335: -- URL params are after '?'
2336: params := instr(lvalue, '?');
2337: if (params <> 0) then
2338: lvalue := substr(lvalue, 1, params)||
2339: wf_notification.GetUrlText(substr(lvalue,
2340: params+1), nid);
2341: end if;
2342: end if;
2343: end if;
2354:
2355: return(lvalue);
2356: exception
2357: when others then
2358: wf_core.context('Wf_Notification', 'GetAttrText', to_char(nid), aname);
2359: raise;
2360: end GetAttrText;
2361:
2362: --
2382:
2383: begin
2384: select WNA.NUMBER_VALUE
2385: into lvalue
2386: from WF_NOTIFICATION_ATTRIBUTES WNA
2387: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2388: exception
2389: when no_data_found then
2390: wf_core.token('NID', to_char(nid));
2394:
2395: return(lvalue);
2396: exception
2397: when others then
2398: wf_core.context('Wf_Notification', 'GetAttrNumber', to_char(nid), aname);
2399: raise;
2400: end GetAttrNumber;
2401:
2402: --
2422:
2423: begin
2424: select WNA.DATE_VALUE
2425: into lvalue
2426: from WF_NOTIFICATION_ATTRIBUTES WNA
2427: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2428: exception
2429: when no_data_found then
2430: wf_core.token('NID', to_char(nid));
2434:
2435: return(lvalue);
2436: exception
2437: when others then
2438: wf_core.context('Wf_Notification', 'GetAttrDate', to_char(nid), aname);
2439: raise;
2440: end GetAttrDate;
2441:
2442: --
2453: -- IN:
2454: -- nid - Notification id
2455: -- aname - Attribute Name
2456: -- disptype - Requested display type. Valid values:
2457: -- wf_notification.doc_text - 'text/plain'
2458: -- wf_notification.doc_html - 'text/html'
2459: -- RETURNS:
2460: -- Referenced document in format requested.
2461: --
2454: -- nid - Notification id
2455: -- aname - Attribute Name
2456: -- disptype - Requested display type. Valid values:
2457: -- wf_notification.doc_text - 'text/plain'
2458: -- wf_notification.doc_html - 'text/html'
2459: -- RETURNS:
2460: -- Referenced document in format requested.
2461: --
2462: function GetAttrDoc(
2469: doctype varchar2(255);
2470: begin
2471:
2472: -- call the procedure to get the Document Content and return to the caller.
2473: wf_notification.GetAttrDoc2(nid, aname, disptype, document, doctype);
2474: return (document);
2475:
2476: exception
2477: when others then
2474: return (document);
2475:
2476: exception
2477: when others then
2478: wf_core.context('Wf_Notification', 'GetAttrDoc', to_char(nid), aname,
2479: disptype);
2480: raise;
2481: end GetAttrDoc;
2482:
2505: begin
2506:
2507: -- Check args
2508: if ((nid is null) or (aname is null) or
2509: (disptype not in (wf_notification.doc_text,
2510: wf_notification.doc_html))) then
2511: wf_core.token('NID', to_char(nid));
2512: wf_core.token('ANAME', aname);
2513: wf_core.token('DISPTYPE', disptype);
2506:
2507: -- Check args
2508: if ((nid is null) or (aname is null) or
2509: (disptype not in (wf_notification.doc_text,
2510: wf_notification.doc_html))) then
2511: wf_core.token('NID', to_char(nid));
2512: wf_core.token('ANAME', aname);
2513: wf_core.token('DISPTYPE', disptype);
2514: wf_core.raise('WFSQL_ARGS');
2552: -- Substitute refs to other attributes in argument
2553: -- NOTE: There is a slight chance of recursive loop here,
2554: -- if the substituted string eventually contains a reference
2555: -- back to this same docattr.
2556: procarg := Wf_Notification.GetTextInternal(procarg, nid, target, FALSE,
2557: FALSE, 'text/plain');
2558: end if;
2559:
2560: -- ### Review Note 4
2558: end if;
2559:
2560: -- ### Review Note 4
2561:
2562: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
2563: --Throw the Illegal exception when the check fails
2564:
2565: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2566: wf_log_pkg.string2(wf_log_pkg.level_statement,
2563: --Throw the Illegal exception when the check fails
2564:
2565: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2566: wf_log_pkg.string2(wf_log_pkg.level_statement,
2567: 'wf.plsql.wf_notification.GetAttrDoc2.plsqldoc_callout',
2568: 'Start executing PLSQL Doc procedure - '||procname, true);
2569: end if;
2570:
2571: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
2580: exception
2581: when others then
2582: if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
2583: wf_log_pkg.string(wf_log_pkg.level_error,
2584: 'wf.plsql.wf_notification.GetAttrDoc2.plsqldoc_api',
2585: 'Error executing PLSQL Doc API - '||procname||' -> '||sqlerrm);
2586: end if;
2587:
2588: -- Bug 10130433: Throwing the WF error 'WFNTF_GEN_DOC' with all the error information
2595: end;
2596:
2597: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2598: wf_log_pkg.string2(wf_log_pkg.level_statement,
2599: 'wf.plsql.wf_notification.GetAttrDoc2.plsqldoc_callout',
2600: 'End executing PLSQL Doc procedure - '||procname, false);
2601: end if;
2602:
2603: -- Bug 8552982, 8916583 - Call GetText2 to substitute any further tokens in DOCUMENT
2602:
2603: -- Bug 8552982, 8916583 - Call GetText2 to substitute any further tokens in DOCUMENT
2604: -- attr content but excluding further DOCUMENT type attibutes within it. We don't
2605: -- support DOCUMENTs within a DOCUMENT
2606: document := wf_notification.GetText2(document, nid, disptype, false);
2607:
2608: -- Translate doc types if needed
2609: if ((disptype = wf_notification.doc_html) and
2610: (doctype = wf_notification.doc_text)) then
2605: -- support DOCUMENTs within a DOCUMENT
2606: document := wf_notification.GetText2(document, nid, disptype, false);
2607:
2608: -- Translate doc types if needed
2609: if ((disptype = wf_notification.doc_html) and
2610: (doctype = wf_notification.doc_text)) then
2611: -- Change plain text to html by wrapping in preformatted tags
2612: document := '
'||document||'
2606: document := wf_notification.GetText2(document, nid, disptype, false);
2607:
2608: -- Translate doc types if needed
2609: if ((disptype = wf_notification.doc_html) and
2610: (doctype = wf_notification.doc_text)) then
2611: -- Change plain text to html by wrapping in preformatted tags
2612: document := '
'||document||'
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
2642: ** If this is a plain text request then just return the display
2643: ** name for the attribute. If it is html then get the attachment
2644: ** url link and return it.
2645: */
2646: if (disptype = wf_notification.doc_html) THEN
2647:
2648: -- Returns session user name if available
2649: username := Wfa_Sec.GetUser;
2650:
2661: end if;
2662: document := null;
2663: exception
2664: when others then
2665: wf_core.context('wf_notification', 'GetAttrDoc2', to_char(nid), aname, disptype);
2666: raise;
2667: end GetAttrDoc2;
2668:
2669: -- bug 2581129
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:
2735: function GetSubject(nid in number)
2736: return varchar2 is
2737: local_subject varchar2(240);
2738: begin
2739: return (Wf_Notification.GetSubject(nid, 'text/html'));
2740: end GetSubject;
2741:
2742: --
2743: -- GetBody
2748: -- truncates values at 1950 chars.
2749: -- IN:
2750: -- nid - Notification Id
2751: -- disptype - Requested display type. Valid values:
2752: -- wf_notification.doc_text - 'text/plain'
2753: -- wf_notification.doc_html - 'text/html'
2754: -- wf_notification.doc_attach - ''
2755: -- RETURNS:
2756: -- Substituted message body
2749: -- IN:
2750: -- nid - Notification Id
2751: -- disptype - Requested display type. Valid values:
2752: -- wf_notification.doc_text - 'text/plain'
2753: -- wf_notification.doc_html - 'text/html'
2754: -- wf_notification.doc_attach - ''
2755: -- RETURNS:
2756: -- Substituted message body
2757: -- NOTE:
2750: -- nid - Notification Id
2751: -- disptype - Requested display type. Valid values:
2752: -- wf_notification.doc_text - 'text/plain'
2753: -- wf_notification.doc_html - 'text/html'
2754: -- wf_notification.doc_attach - ''
2755: -- RETURNS:
2756: -- Substituted message body
2757: -- NOTE:
2758: -- If errors are detected this routine returns the body unsubstituted,
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:
2787: where N.NOTIFICATION_ID = nid
2788: and N.MESSAGE_NAME = WM.NAME
2789: and N.MESSAGE_TYPE = WM.TYPE;
2790:
2791: -- If user has not used WF_NOTIFICATION(HISTORY) or #HISTORY, append Action History in the
2792: -- notification body by default if this is a...
2793: -- 1. Response required notification
2794: -- 2. FYI notification with at least one Reassign action
2795: -- Query to check if the ntf is FYI or not
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'
2824: if ((l_fyi and l_comm_cnt > 0) or not l_fyi) then
2825: -- According to bug 3612609, if the user just defines #HISTORY, but does not place it in the
2826: -- message body, even then it should be used instead of default WF Action History
2827: begin
2828: l_cust_hist := Wf_Notification.GetAttrText(nid, '#HISTORY');
2829: exception
2830: when others then
2831: l_cust_hist := '';
2832: Wf_Core.Clear;
2835: -- would not be here.
2836: if (l_cust_hist is not null and upper(trim(substr(l_cust_hist, 1, 5))) = 'PLSQL') then
2837: l_action_hist := '&#HISTORY';
2838: else
2839: l_action_hist := 'WF_NOTIFICATION(HISTORY)';
2840: end if;
2841:
2842: -- Either a FYI with at least one reassign/Request Info or a Response notification.
2843: -- So, append Action History
2840: end if;
2841:
2842: -- Either a FYI with at least one reassign/Request Info or a Response notification.
2843: -- So, append Action History
2844: if (local_body is not null and instrb(local_body, 'WF_NOTIFICATION(HISTORY)') = 0 and
2845: instrb(local_body, '&#HISTORY') = 0) then
2846:
2847: local_body := local_body || Wf_Core.newline || l_action_hist;
2848: end if;
2846:
2847: local_body := local_body || Wf_Core.newline || l_action_hist;
2848: end if;
2849:
2850: if (local_html_body is not null and instrb(local_html_body, 'WF_NOTIFICATION(HISTORY)') = 0 and
2851: instrb(local_html_body, '&#HISTORY') = 0) then
2852: -- Defer adding history macro until after stripping off BODY tags
2853: l_html_hist := true;
2854: end if;
2854: end if;
2855: end if;
2856:
2857: -- Return substituted body.
2858: if (disptype = wf_notification.doc_text) then
2859: local_body := GetText(local_body, nid, disptype);
2860:
2861: -- replace the functions here
2862: local_body := runFuncOnBody(nid, local_body, disptype);
2903: end if;
2904:
2905: exception
2906: when others then
2907: wf_core.context('Wf_Notification', 'GetBody', to_char(nid), disptype);
2908: raise;
2909: end GetBody;
2910:
2911: --
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));
3007: end;
3008:
3009: exception
3010: when others then
3011: wf_core.context('Wf_Notification', 'GetInfo', to_char(nid));
3012: raise;
3013: end GetInfo;
3014:
3015: --
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));
3046:
3047: return(respbuf);
3048: exception
3049: when others then
3050: Wf_Core.Context('Wf_Notification', 'Responder', to_char(nid));
3051: raise;
3052: end Responder;
3053:
3054: -- AccessCheck
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;
3123: -- by that process.
3124: --
3125: if (callback is not null) then
3126: -- ### Review Note 3 - private function
3127: l_charcheck := WF_NOTIFICATION_UTIL.CheckIllegalChar(callback);
3128:
3129:
3130: -- BINDVAR_SCAN_IGNORE
3131: sqlbuf := 'begin '||callback||
3158: return mailpref;
3159:
3160: exception
3161: when others then
3162: Wf_Core.Context('Wf_Notification', 'GetMailPreference', role);
3163: raise;
3164: end GetMailPreference;
3165:
3166: --
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
3231: wf_core.raise('WFNTF_NID');
3232: end;
3233:
3234: /* implement the above loop recursively */
3235: if (cnt > wf_notification.max_forward) then
3236: -- it means max_forward must have been exceeded. Treat as a loop error.
3237: wf_core.token('NID', to_char(nid));
3238: wf_core.raise('WFNTF_ROUTE_LOOP');
3239: end if;
3262: -- N: Allow Reassign
3263: -- B: Allow Reassign only through Routing Rule
3264: l_hide_reassign := 'N';
3265: begin
3266: l_hide_reassign := Wf_Notification.GetAttrText(nid, '#HIDE_REASSIGN');
3267: exception
3268: when others then
3269: -- Clear the error stack since we ignore the error
3270: Wf_Core.Clear;
3291: select OWNER_ROLE into l_wf_owner
3292: from WF_ITEMS
3293: where ITEM_TYPE=msgtype
3294: and ITEM_KEY=l_item_key;
3295: l_from_role := Wf_Notification.GetAttrText(nid => Route.nid,
3296: aname=>'#FROM_ROLE',
3297: ignore_notfound=>TRUE);
3298: if recip in (l_wf_owner, l_from_role) then
3299: raise badfwd;
3308: -- for another forward.
3309:
3310: begin
3311: -- ### implement this in next release
3312: -- Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1);
3313: Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3314: exception
3315: when others then
3316: raise badfwd;
3309:
3310: begin
3311: -- ### implement this in next release
3312: -- Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1);
3313: Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3314: exception
3315: when others then
3316: raise badfwd;
3317: end;
3324: -- for another transfer.
3325:
3326: begin
3327: -- ### implement this in next release
3328: -- Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1);
3329: Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3330: exception
3331: when others then
3332: raise badfwd;
3325:
3326: begin
3327: -- ### implement this in next release
3328: -- Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1);
3329: Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3330: exception
3331: when others then
3332: raise badfwd;
3333: end;
3335: -- RESPOND
3336: -- Query response values for this rule and set attrs accordingly
3337: for respattr in attrcurs(rulerec.rule_id) loop
3338: if (respattr.type = 'NUMBER') then
3339: Wf_Notification.SetAttrNumber(nid, respattr.name,
3340: respattr.number_value);
3341: elsif (respattr.type = 'DATE') then
3342: Wf_Notification.SetAttrDate(nid, respattr.name,
3343: respattr.date_value);
3338: if (respattr.type = 'NUMBER') then
3339: Wf_Notification.SetAttrNumber(nid, respattr.name,
3340: respattr.number_value);
3341: elsif (respattr.type = 'DATE') then
3342: Wf_Notification.SetAttrDate(nid, respattr.name,
3343: respattr.date_value);
3344: else -- All other types use text
3345: Wf_Notification.SetAttrText(nid, respattr.name,
3346: respattr.text_value);
3341: elsif (respattr.type = 'DATE') then
3342: Wf_Notification.SetAttrDate(nid, respattr.name,
3343: respattr.date_value);
3344: else -- All other types use text
3345: Wf_Notification.SetAttrText(nid, respattr.name,
3346: respattr.text_value);
3347: end if;
3348: end loop;
3349:
3347: end if;
3348: end loop;
3349:
3350: -- Complete response
3351: Wf_Notification.Respond(nid, newcomment, recip, 'RULE');
3352: else
3353: -- This must be one of:
3354: -- a. NOOP rule
3355: -- b. No routing rule found
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
3366: 'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
3367: where NOTIFICATION_ID = nid;
3368: exception
3369: when others then
3370: wf_core.context('Wf_Notification', 'Route (update comment)',to_char(nid));
3371: raise;
3372: end;
3373: when badfwd then
3374: Wf_Core.Get_Error(dummy, errmsg, dummy);
3376: if (newcomment is not null) then
3377: newcomment := newcomment||wf_core.newline;
3378: end if;
3379: if recip in (l_wf_owner, l_from_role) then
3380: Wf_Notification.SetComments(nid, o_recip, recip, rulerec.action,
3381: 'RULE', Wf_Core.Translate('OWNER_ROUTE_FAIL'));
3382: else
3383: Wf_Core.Token('TO_ROLE', WF_Directory.GetRoleDisplayName(recip));
3384: newcomment := substrb(newcomment||
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)
3395: Route.newcomment, 1, 4000)
3396: where NOTIFICATION_ID = nid;
3397: exception
3398: when OTHERS then
3399: wf_core.context('Wf_Notification', 'Route (update comment)',
3400: to_char(nid));
3401: raise;
3402: end;
3403:
3404: when others then
3405: if (rulecurs%isopen) then
3406: close rulecurs;
3407: end if;
3408: wf_core.context('Wf_Notification', 'Route', to_char(nid));
3409: raise;
3410: end Route;
3411:
3412: --
3428: begin
3429:
3430: -- Derive item type, item key and activity id from the context
3431: -- when no ':' or just one ':', context does not conform to WF standard
3432: -- it could be sent by calling wf_notification.send directly
3433: -- in this case, we just return false to preserve the old behavior
3434: validate_context (p_context, l_item_type, l_item_key, l_actid);
3435: if (l_item_type is null or l_item_key is null or l_actid is null) then
3436: return false;
3452: return true;
3453:
3454: exception
3455: when others then
3456: Wf_Core.Context('Wf_Notification', 'First_Execution', p_context);
3457: raise;
3458: end First_Execution;
3459:
3460: -- Denormalize_Columns_Internal(PRIVATE)
3472: wna.date_value
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'
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)
3603: where notification_id = p_nid;
3604:
3605: exception
3606: when OTHERS then
3607: wf_core.context('Wf_Notification', 'denormalize_columns_internal',
3608: p_item_key, p_user_key, to_char(p_nid));
3609: raise;
3610: end Denormalize_columns_internal;
3611:
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
3724: where MESSAGE_TYPE = msg_type
3725: and MESSAGE_NAME = msg_name;
3726: begin
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
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,
3779: -- Open and parse cursor for dynamic sql for getting attr values
3780: -- Bug 2376033 added event value in call to CB
3781: if (callback is not null) then
3782: -- ### Review Note 2
3783: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
3784: --Throw the Illegal exception when the check fails
3785:
3786:
3787: -- BINDVAR_SCAN_IGNORE
3843: else
3844: -- Bug 2580807 call with original signature for backward
3845: -- compatibility
3846: -- ### Review Note 2 - callback is from table
3847: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
3848: --Throw the Illegal exception when the check fails
3849:
3850:
3851: -- BINDVAR_SCAN_IGNORE
3875: --
3876: -- Insert notification attribute
3877: -- Bug 2376033 insert the event value
3878: --
3879: insert into WF_NOTIFICATION_ATTRIBUTES (
3880: NOTIFICATION_ID,
3881: NAME,
3882: TEXT_VALUE,
3883: NUMBER_VALUE,
3902: -- only for the first time
3903: if (First_Execution(context)) then
3904: l_send_source := 'FIRST';
3905: begin
3906: l_send_comment := Wf_Notification.GetAttrText(nid, '#SUBMIT_COMMENTS');
3907: exception
3908: when others then
3909: if(Wf_Core.Error_Name = 'WFNTF_ATTR') then
3910: Wf_Core.Clear();
3917: end if;
3918:
3919: -- If #FROM_ROLE is defined, we will get the value in this attribute
3920: begin
3921: l_from_role := Wf_Notification.GetAttrText(nid, '#FROM_ROLE');
3922: exception
3923: when OTHERS then
3924: wf_core.clear;
3925: -- Check if the notification is sent under a valid Fwk Session
3929: -- Use dummy user WF_SYSTEM as last resort
3930: if (l_from_role is null) then
3931: l_from_role := 'WF_SYSTEM';
3932: end if;
3933: Wf_Notification.SetComments(nid, l_from_role, role, 'SEND', l_send_source, l_send_comment);
3934:
3935: -- Check for auto-routing of notification just sent
3936: Wf_Notification.Route(nid, 0);
3937:
3932: end if;
3933: Wf_Notification.SetComments(nid, l_from_role, role, 'SEND', l_send_source, l_send_comment);
3934:
3935: -- Check for auto-routing of notification just sent
3936: Wf_Notification.Route(nid, 0);
3937:
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
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;
3945: wf_event.AddParameterToList('IS_DUPLICATE','TRUE',l_parameterlist);
3946: end if;
3947:
3948: -- Denormalize the Notification after auto-routing is done
3949: Wf_Notification.Denormalize_Notification(nid);
3950:
3951: -- Denormalize custom columns
3952: -- Derive item type, item key and activity id from the context
3953: if context is not null then
3965: else
3966: l_itemkey := null;
3967: l_userkey := null;
3968: end if;
3969: wf_notification.denormalize_columns_internal(l_itemkey, l_userkey, nid);
3970:
3971:
3972: -- DL: Move this to be the last step before returning the nid
3973: -- The recipient_role could be updated during auto-routing
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:
4011: if wf_event.phase_maxthreshold is null then
4012: -- means deferred mode , avoid synchronous processing of Send event
4013: wf_event.SetDispatchMode('ASYNC');
4014:
4015: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
4016: p_event_key => to_char(nid),
4017: p_parameters => l_parameterlist);
4018:
4019: wf_event.phase_maxthreshold := null;
4017: p_parameters => l_parameterlist);
4018:
4019: wf_event.phase_maxthreshold := null;
4020: else
4021: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
4022: p_event_key => to_char(nid),
4023: p_parameters => l_parameterlist);
4024:
4025: end if;
4027: return (nid);
4028:
4029: exception
4030: when others then
4031: wf_core.context('Wf_Notification', 'SendSingle', role, msg_type,
4032: msg_name, due_date, callback);
4033: raise;
4034: end SendSingle;
4035:
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);
4069: actid number;
4070: col1 pls_integer;
4071: col2 pls_integer;
4072: prev_nid pls_integer;
4073: l_proc_name varchar2(100) := 'wf.plsql.wf_notification.Send';
4074: begin
4075: if ((role is null) or (msg_type is null) or (msg_name is null)) then
4076: wf_core.token('ROLE', role);
4077: wf_core.token('TYPE', msg_type);
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
4148:
4149: return (nid);
4150: exception
4151: when others then
4152: wf_core.context('Wf_Notification', 'Send', role, msg_type,
4153: msg_name, due_date, callback);
4154: raise;
4155: end Send;
4156:
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:
4251:
4252: return (gid);
4253: exception
4254: when others then
4255: wf_core.context('Wf_Notification', 'SendGroup', role, msg_type,
4256: msg_name, due_date, callback);
4257: raise;
4258: end SendGroup;
4259:
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
4362: end if;
4363:
4364: -- Check role is valid and get mail preference
4365: begin
4366: mailpref := Wf_Notification.GetMailPreference(new_role, cb, context);
4367: exception
4368: when others then
4369: wf_core.token('ROLE', new_role);
4370: if (fmode = 'FORWARD') then
4414: tvalue := new_role;
4415: nvalue := nid;
4416: -- ### Review Note 2 - cb is from table
4417: -- BINDVAR_SCAN_IGNORE
4418: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
4419: --Throw the Illegal exception when the check fails
4420:
4421:
4422: sqlbuf := 'begin '||cb||
4450: validate_context (context => ForwardInternal.context,
4451: itemtype => l_item_type,
4452: itemkey => l_item_key,
4453: actid => l_act_id);
4454: hash_from_role := Wf_Notification.GetAttrText(nid => ForwardInternal.nid,
4455: aname=>'#FROM_ROLE',
4456: ignore_notfound=>TRUE);
4457: select OWNER_ROLE into l_wf_owner
4458: from WF_ITEMS
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),
4498: MORE_INFO_ROLE = l_more_info_role,
4499: SENT_DATE = SYSDATE
4500: where NOTIFICATION_ID = nid;
4501:
4502: Wf_Notification.SetComments(nid, l_from_role, new_role, 'TRANSFER',
4503: action_source, forward_comment);
4504:
4505: else
4506: update WF_NOTIFICATIONS set
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),
4519: MORE_INFO_ROLE = l_more_info_role,
4520: SENT_DATE = SYSDATE
4521: where NOTIFICATION_ID = nid;
4522:
4523: Wf_Notification.SetComments(nid, l_from_role, new_role, 'DELEGATE',
4524: action_source, forward_comment);
4525: end if;
4526:
4527: -- Pop any messages from then outbound queue
4531: -- user goes in and does an action from the worklist.
4532: -- wf_xml.RemoveNotification(nid);
4533:
4534: -- Check for auto-routing of notification just forwarded
4535: Wf_Notification.Route(nid, cnt);
4536:
4537: -- Denormalize after all the routing is done
4538: if (cnt = 0) then
4539: Wf_Notification.Denormalize_Notification(nid);
4535: Wf_Notification.Route(nid, cnt);
4536:
4537: -- Denormalize after all the routing is done
4538: if (cnt = 0) then
4539: Wf_Notification.Denormalize_Notification(nid);
4540:
4541: -- Push the new notification to the queue
4542: -- The call to wf_xml.EnqueueNotification has been moved
4543: -- to an event subscription.
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;
4583: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
4584:
4585:
4586: --Raise the event
4587: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.reassign',
4588: p_event_key => to_char(nid),
4589: p_parameters => l_parameterlist);
4590:
4591: exception
4589: p_parameters => l_parameterlist);
4590:
4591: exception
4592: when others then
4593: wf_core.context('Wf_Notification', 'ForwardInternal', to_char(nid),
4594: new_role, fmode, forward_comment);
4595: raise;
4596: end ForwardInternal;
4597:
4617: begin
4618: ForwardInternal(nid, new_role, 'FORWARD', forward_comment, user, cnt, action_source);
4619: exception
4620: when others then
4621: wf_core.context('Wf_Notification', 'Forward', to_char(nid),
4622: new_role, forward_comment);
4623: -- This call is for enhanced error handling with respect to OAFwk
4624: wf_notification.SetUIErrorMessage;
4625: raise;
4620: when others then
4621: wf_core.context('Wf_Notification', 'Forward', to_char(nid),
4622: new_role, forward_comment);
4623: -- This call is for enhanced error handling with respect to OAFwk
4624: wf_notification.SetUIErrorMessage;
4625: raise;
4626: end Forward;
4627:
4628: --
4647: begin
4648: ForwardInternal(nid, new_role, 'TRANSFER', forward_comment, user, cnt, action_source);
4649: exception
4650: when others then
4651: wf_core.context('Wf_Notification', 'Transfer', to_char(nid),
4652: new_role, forward_comment);
4653: -- This call is for enhanced error handling with respect to OAFwk
4654: wf_notification.SetUIErrorMessage;
4655: raise;
4650: when others then
4651: wf_core.context('Wf_Notification', 'Transfer', to_char(nid),
4652: new_role, forward_comment);
4653: -- This call is for enhanced error handling with respect to OAFwk
4654: wf_notification.SetUIErrorMessage;
4655: raise;
4656: end Transfer;
4657:
4658: --
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
4708: wf_core.raise('WFNTF_NID_OPEN');
4709: end if;
4710:
4711: -- Check role is valid and get mail preference
4712: mailpref := Wf_Notification.GetMailPreference(role, cb, context);
4713:
4714: -- If no responses expected, then do not mail cancel notice
4715: -- regardless of role notification_preference setting.
4716: begin
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,
4764:
4765: if (l_from_role is null) then
4766: l_from_role := 'WF_SYSTEM';
4767: end if;
4768: Wf_Notification.SetComments(nid, l_from_role, 'WF_SYSTEM', l_action, null, newcomment);
4769:
4770: -- GK: 1636402: wf_xml.RemoveNotification is not necessary
4771: -- since the message is likely to be sent by the time the
4772: -- user goes in and does an action from the worklist.
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);
4796: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
4797: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
4798:
4799: --Raise the event
4800: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.cancel',
4801: p_event_key => to_char(nid),
4802: p_parameters => l_parameterlist);
4803:
4804:
4803:
4804:
4805: exception
4806: when others then
4807: wf_core.context('Wf_Notification', 'CancelSingle', to_char(nid),
4808: role, cancel_comment);
4809: raise;
4810: end CancelSingle;
4811:
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
4849: CancelSingle(nid, role, cancel_comment, FALSE);
4850:
4851: exception
4852: when others then
4853: wf_core.context('Wf_Notification', 'Cancel', to_char(nid), cancel_comment);
4854: raise;
4855: end Cancel;
4856:
4857: --
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:
4886: cancel_comment, timeout);
4887: end loop;
4888: exception
4889: when others then
4890: wf_core.context('Wf_Notification', 'CancelGroup', to_char(gid),
4891: cancel_comment);
4892: raise;
4893: end CancelGroup;
4894:
4911: response_found boolean;
4912: l_status varchar2(10);
4913:
4914: begin
4915: wf_notification.respond2(nid, respond_comment, responder, action_source, response_found);
4916:
4917: if (response_found) then
4918: wf_notification.complete(nid);
4919: end if;
4914: begin
4915: wf_notification.respond2(nid, respond_comment, responder, action_source, response_found);
4916:
4917: if (response_found) then
4918: wf_notification.complete(nid);
4919: end if;
4920:
4921: exception
4922: when others then
4919: end if;
4920:
4921: exception
4922: when others then
4923: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
4924: respond_comment, responder);
4925: wf_core.clear;
4926: -- This call is for enhanced error handling with respect to OAFwk
4927: wf_notification.SetUIErrorMessage;
4923: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
4924: respond_comment, responder);
4925: wf_core.clear;
4926: -- This call is for enhanced error handling with respect to OAFwk
4927: wf_notification.SetUIErrorMessage;
4928: raise;
4929: end Respond;
4930:
4931: --
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));
4977:
4978: -- Open dynamic sql cursor for callback call
4979: -- ### Review Note 2 - callback is from table
4980: -- Check for bug#3827935
4981: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
4982: --Throw the Illegal exception when the check fails
4983:
4984:
4985: -- BINDVAR_SCAN_IGNORE
5002: end if;
5003:
5004: exception
5005: when others then
5006: wf_core.context('Wf_Notification', 'TestContext', to_char(nid));
5007: raise;
5008: end TestContext;
5009:
5010: --
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'
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'
5052: and wfna.name = 'RESULT'
5081: --
5082: exception
5083: when others then
5084: --
5085: wf_core.context('Wf_Notification', 'VoteCount', to_char(gid), ResultCode );
5086: raise;
5087: --
5088: end VoteCount;
5089: --
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: --
5120: return(FALSE);
5121: --
5122: when others then
5123: --
5124: wf_core.context('Wf_Notification', 'OpenNotifications', to_char(gid) );
5125: raise;
5126: --
5127: end OpenNotificationsExist;
5128:
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
5161:
5162: return(ncount);
5163: exception
5164: when others then
5165: wf_core.context('Wf_Notification', 'WorkCount', username);
5166: raise;
5167: end WorkCount;
5168:
5169: --
5181:
5182: -- Any existence of response attribute constitutes a response required.
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
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
5215: where N.NOTIFICATION_ID = nid
5216: for update nowait;
5217: exception
5218: when no_data_found then
5219: wf_core.token('NID', Wf_Notification.GetSubject(nid));
5220: wf_core.raise('WFNTF_NID');
5221: end;
5222:
5223: -- Check notification is open
5221: end;
5222:
5223: -- Check notification is open
5224: if (status <> 'OPEN') then
5225: wf_core.token('NID', Wf_Notification.GetSubject(nid) );
5226: wf_core.raise('WFNTF_NID_OPEN');
5227: end if;
5228:
5229:
5230: open attrs(nid);
5231: fetch attrs into result;
5232: if (attrs%found) then
5233: -- Check response required?
5234: wf_core.token('NID', Wf_Notification.GetSubject(nid));
5235: wf_core.raise('WFNTF_NID_REQUIRE');
5236: end if;
5237:
5238: -- Mark notification STATUS as 'CLOSED' and MAIL_STATUS as NULL
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
5266: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
5267: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
5268:
5269: --Raise the event
5270: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.close',
5271: p_event_key => to_char(nid),
5272: p_parameters => l_parameterlist);
5273:
5274: exception
5272: p_parameters => l_parameterlist);
5273:
5274: exception
5275: when others then
5276: wf_core.context('Wf_Notification', 'Close', to_char(nid), responder);
5277: raise;
5278: end Close;
5279:
5280: --
5338: -- Process special '#' internal tokens. Supported tokens are:
5339: -- &#NID - Notification id
5340: --
5341: local_text := substrb(replace(local_text, '&'||'#NID',
5342: '['||wf_core.translate('WF_NOTIFICATION_ID')||']'), 1, 1950);
5343:
5344:
5345: return(local_text);
5346:
5440: dbms_lob.write(clob_loc,amt,pos,msg_string);
5441:
5442: exception
5443: when others then
5444: wf_core.context('WF_NOTIFICATION','WriteToClob');
5445: raise;
5446: end WriteToClob;
5447:
5448: --Name : GetFullBody (PUBLIC)
5450: -- and returns the message in 32K chunks in the msgbody out variable.
5451: -- Call this repeatedly until end_of_body is true.
5452: -- Call syntax is
5453: --while not (end_of_msgbody) loop
5454: -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
5455: --end loop;
5456: procedure GetFullBody (nid in number,
5457: msgbody out nocopy varchar2,
5458: end_of_body in out nocopy boolean,
5475: -- if this is the same nid as was just used in this session,
5476: -- and the message is stored as a clob (so clob_exists is not null) then
5477: -- retrieve message from the temp clob.
5478:
5479: if nid = wf_notification.last_nid
5480: and disptype = wf_notification.last_disptype
5481: and wf_notification.clob_exists is not null then
5482: wf_notification.read_Clob(msgbody, end_of_body);
5483: if end_of_body then
5476: -- and the message is stored as a clob (so clob_exists is not null) then
5477: -- retrieve message from the temp clob.
5478:
5479: if nid = wf_notification.last_nid
5480: and disptype = wf_notification.last_disptype
5481: and wf_notification.clob_exists is not null then
5482: wf_notification.read_Clob(msgbody, end_of_body);
5483: if end_of_body then
5484: wf_notification.last_nid := 0; --resetting to an inexistent value
5477: -- retrieve message from the temp clob.
5478:
5479: if nid = wf_notification.last_nid
5480: and disptype = wf_notification.last_disptype
5481: and wf_notification.clob_exists is not null then
5482: wf_notification.read_Clob(msgbody, end_of_body);
5483: if end_of_body then
5484: wf_notification.last_nid := 0; --resetting to an inexistent value
5485: end if;
5478:
5479: if nid = wf_notification.last_nid
5480: and disptype = wf_notification.last_disptype
5481: and wf_notification.clob_exists is not null then
5482: wf_notification.read_Clob(msgbody, end_of_body);
5483: if end_of_body then
5484: wf_notification.last_nid := 0; --resetting to an inexistent value
5485: end if;
5486: return;
5480: and disptype = wf_notification.last_disptype
5481: and wf_notification.clob_exists is not null then
5482: wf_notification.read_Clob(msgbody, end_of_body);
5483: if end_of_body then
5484: wf_notification.last_nid := 0; --resetting to an inexistent value
5485: end if;
5486: return;
5487: end if;
5488:
5485: end if;
5486: return;
5487: end if;
5488:
5489: wf_notification.clob_exists :=null;
5490: wf_notification.last_nid:=nid;
5491: wf_notification.last_disptype:=disptype;
5492: plsql_clob_exists:=null;
5493:
5486: return;
5487: end if;
5488:
5489: wf_notification.clob_exists :=null;
5490: wf_notification.last_nid:=nid;
5491: wf_notification.last_disptype:=disptype;
5492: plsql_clob_exists:=null;
5493:
5494: msgbody := wf_notification.getbody(nid,disptype);
5487: end if;
5488:
5489: wf_notification.clob_exists :=null;
5490: wf_notification.last_nid:=nid;
5491: wf_notification.last_disptype:=disptype;
5492: plsql_clob_exists:=null;
5493:
5494: msgbody := wf_notification.getbody(nid,disptype);
5495:
5490: wf_notification.last_nid:=nid;
5491: wf_notification.last_disptype:=disptype;
5492: plsql_clob_exists:=null;
5493:
5494: msgbody := wf_notification.getbody(nid,disptype);
5495:
5496: if msgbody is null
5497: or instr(msgbody,'&') = 0
5498: or plsql_clob_exists is null then
5502: else
5503:
5504: strt:=1;
5505:
5506: wf_notification.newclob(wf_notification.temp_clob,null);
5507: wf_notification.clob_exists :=1;
5508:
5509: loop
5510:
5503:
5504: strt:=1;
5505:
5506: wf_notification.newclob(wf_notification.temp_clob,null);
5507: wf_notification.clob_exists :=1;
5508:
5509: loop
5510:
5511: attr_name := null;
5514:
5515: if ampersand = 0 then
5516: if strt <= length(msgbody) then
5517:
5518: wf_notification.WriteToClob(wf_notification.temp_clob,
5519: substr(msgbody,strt,length(msgbody)));
5520: end if;
5521: exit;
5522: end if;
5525: -- would encounter an error in our logic. 1 - 1 is 0 so the call to
5526: -- substr would fail.
5527:
5528: if ((ampersand - strt) > 0) then
5529: wf_notification.writeToClob(wf_notification.temp_clob,
5530: substr(msgbody,strt,ampersand-strt));
5531:
5532: end if;
5533:
5534: -- 2691290 if the '&' is at the end of the body the notification
5535: -- will error when calling GetAttrClob API for "Invalid values for
5536: -- Arguments".
5537: if (substr(msgbody,ampersand+1,30) is not null) then
5538: wf_notification.getattrclob(nid,substr(msgbody,ampersand+1,30),disptype,
5539: wf_notification.temp_clob , attr_name);
5540: end if;
5541:
5542:
5535: -- will error when calling GetAttrClob API for "Invalid values for
5536: -- Arguments".
5537: if (substr(msgbody,ampersand+1,30) is not null) then
5538: wf_notification.getattrclob(nid,substr(msgbody,ampersand+1,30),disptype,
5539: wf_notification.temp_clob , attr_name);
5540: end if;
5541:
5542:
5543: if attr_name is not null then
5544: --it was already written to clob.
5545: strt := ampersand + 1 + length(attr_name);
5546: else
5547: --the string was not a plsqlclob
5548: wf_notification.writeToClob(wf_notification.temp_clob,'&');
5549: strt := ampersand + 1;
5550: end if;
5551:
5552: end loop;
5552: end loop;
5553:
5554:
5555: --set the clob chunk to zero. then request the next chunk in the msgbody
5556: wf_notification.clob_chunk := 0;
5557: wf_notification.read_Clob(msgbody, end_of_body);
5558: if end_of_body then
5559: wf_notification.last_nid := 0; --resetting to an inexistent value
5560: end if;
5553:
5554:
5555: --set the clob chunk to zero. then request the next chunk in the msgbody
5556: wf_notification.clob_chunk := 0;
5557: wf_notification.read_Clob(msgbody, end_of_body);
5558: if end_of_body then
5559: wf_notification.last_nid := 0; --resetting to an inexistent value
5560: end if;
5561: end if;
5555: --set the clob chunk to zero. then request the next chunk in the msgbody
5556: wf_notification.clob_chunk := 0;
5557: wf_notification.read_Clob(msgbody, end_of_body);
5558: if end_of_body then
5559: wf_notification.last_nid := 0; --resetting to an inexistent value
5560: end if;
5561: end if;
5562:
5563: exception
5561: end if;
5562:
5563: exception
5564: when others then
5565: wf_core.context('WF_NOTIFICATION','GetFullBody', 'nid => '||to_char(nid),
5566: 'disptype => '||disptype);
5567: raise;
5568: end GetFullBody;
5569:
5574: -- Call this repeatedly until end_of_body is "Y". Uses string arg
5575: -- instead of boolean like GetFullBody for end_of_msg_body
5576: -- Call syntax is
5577: --while (end_of_msgbody <> 'Y') loop
5578: -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
5579: --end loop;
5580: procedure GetFullBodyWrapper (nid in number,
5581: msgbody out nocopy varchar2,
5582: end_of_body out nocopy varchar2,
5584: is
5585: end_of_body_b boolean;
5586: begin
5587: end_of_body_b := FALSE;
5588: WF_Notification.GetFullBody(nid, msgbody, end_of_body_b, disptype);
5589: if (end_of_body_b = TRUE) then
5590: end_of_body := 'Y';
5591: else
5592: end_of_body := 'N';
5606: -- IN:
5607: -- nid - Notification id
5608: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5609: -- disptype - Requested display type. Valid values:
5610: -- wf_notification.doc_text - 'text/plain'
5611: -- wf_notification.doc_html - 'text/html'
5612: -- document - The clob into which
5613: -- aname - Attribute Name (the first part of the string that matches
5614: -- the attr list)
5607: -- nid - Notification id
5608: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5609: -- disptype - Requested display type. Valid values:
5610: -- wf_notification.doc_text - 'text/plain'
5611: -- wf_notification.doc_html - 'text/html'
5612: -- document - The clob into which
5613: -- aname - Attribute Name (the first part of the string that matches
5614: -- the attr list)
5615: --
5622: is
5623: doctype varchar2(500);
5624: begin
5625:
5626: Wf_Notification.GetAttrClob(nid, astring, disptype, document, doctype, aname);
5627:
5628: exception
5629: when others then
5630: wf_core.context('Wf_Notification', 'oldGetAttrClob', to_char(nid), aname,
5626: Wf_Notification.GetAttrClob(nid, astring, disptype, document, doctype, aname);
5627:
5628: exception
5629: when others then
5630: wf_core.context('Wf_Notification', 'oldGetAttrClob', to_char(nid), aname,
5631: disptype);
5632: raise;
5633: end GetAttrClob;
5634:
5643: -- IN:
5644: -- nid - Notification id
5645: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5646: -- disptype - Requested display type. Valid values:
5647: -- wf_notification.doc_text - 'text/plain'
5648: -- wf_notification.doc_html - 'text/html'
5649: -- document - Th clob into which
5650: -- aname - Attribute Name (the string that matches
5651: -- the attr list)
5644: -- nid - Notification id
5645: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5646: -- disptype - Requested display type. Valid values:
5647: -- wf_notification.doc_text - 'text/plain'
5648: -- wf_notification.doc_html - 'text/html'
5649: -- document - Th clob into which
5650: -- aname - Attribute Name (the string that matches
5651: -- the attr list)
5652: --
5678: begin
5679:
5680: -- Check args
5681: if ((nid is null) or (astring is null) or
5682: (disptype not in (wf_notification.doc_text,
5683: wf_notification.doc_html))) then
5684: wf_core.token('NID', to_char(nid));
5685: wf_core.token('ASTRING', aname);
5686: wf_core.token('DISPTYPE', disptype);
5679:
5680: -- Check args
5681: if ((nid is null) or (astring is null) or
5682: (disptype not in (wf_notification.doc_text,
5683: wf_notification.doc_html))) then
5684: wf_core.token('NID', to_char(nid));
5685: wf_core.token('ASTRING', aname);
5686: wf_core.token('DISPTYPE', disptype);
5687: wf_core.raise('WFSQL_ARGS');
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
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
5704: and wma.name = na.name
5705: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
5716: end;
5717:
5718: if (aname is not null) then
5719: -- Retrieve key string
5720: key := wf_notification.GetAttrText(nid, aname);
5721:
5722: -- If the key is empty then return a null string
5723: if (key is not null) then
5724:
5760:
5761: if (procarg is null) then
5762: procarg := NULL;
5763: else
5764: procarg := Wf_Notification.GetTextInternal(procarg, nid, target,
5765: FALSE, FALSE, 'text/plain');
5766: end if;
5767:
5768: -- ### Review Note 1
5765: FALSE, FALSE, 'text/plain');
5766: end if;
5767:
5768: -- ### Review Note 1
5769: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
5770: --Throw the Illegal exception when the check fails
5771:
5772: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5773: wf_log_pkg.string2(wf_log_pkg.level_statement,
5770: --Throw the Illegal exception when the check fails
5771:
5772: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5773: wf_log_pkg.string2(wf_log_pkg.level_statement,
5774: 'wf.plsql.wf_notification.GetAttrClob.plsqlclob_callout',
5775: 'Start executing PLSQLCLOB Doc procedure - '||procname, true);
5776: end if;
5777:
5778: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
5787: exception
5788: when others then
5789: if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
5790: wf_log_pkg.string(wf_log_pkg.level_error,
5791: 'wf.plsql.wf_notification.GetAttrClob.plsqlclob_api',
5792: 'Error executing PLSQLCLOB Doc API - '||procname|| ' -> '||sqlerrm);
5793: end if;
5794:
5795: -- Bug 10130433: Throwing the WF error 'WFNTF_GEN_DOC' with all the error information
5802: end;
5803:
5804: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5805: wf_log_pkg.string2(wf_log_pkg.level_statement,
5806: 'wf.plsql.wf_notification.GetAttrClob.plsqlclob_callout',
5807: 'End executing PLSQLCLOB Doc procedure - '||procname, false);
5808: end if;
5809:
5810: exception
5808: end if;
5809:
5810: exception
5811: when others then
5812: wf_core.context('Wf_Notification', 'GetAttrClob', to_char(nid), aname,
5813: disptype);
5814: raise;
5815: end GetAttrClob;
5816:
5852: end if;
5853:
5854: exception
5855: when others then
5856: wf_core.context('WF_NOTIFICATION','NewClob');
5857: raise;
5858: end NewClob;
5859:
5860: --Name Read_Clob
5868: begin
5869:
5870: --linenbr is always one before the line to print.
5871: --it is incremented afterwards.
5872: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
5873:
5874: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
5875:
5876: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
5870: --linenbr is always one before the line to print.
5871: --it is incremented afterwards.
5872: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
5873:
5874: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
5875:
5876: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
5877: end_of_clob := TRUE;
5878: wf_notification.clob_chunk := 0;
5872: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
5873:
5874: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
5875:
5876: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
5877: end_of_clob := TRUE;
5878: wf_notification.clob_chunk := 0;
5879: else
5880: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
5874: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
5875:
5876: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
5877: end_of_clob := TRUE;
5878: wf_notification.clob_chunk := 0;
5879: else
5880: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
5881: end if;
5882:
5876: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
5877: end_of_clob := TRUE;
5878: wf_notification.clob_chunk := 0;
5879: else
5880: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
5881: end if;
5882:
5883: exception
5884: when others then
5881: end if;
5882:
5883: exception
5884: when others then
5885: wf_core.context('Wf_Notification', 'Read_Clob','pos => '||to_char(pos),
5886: 'line => {'||line||'}');
5887: raise;
5888: end Read_Clob;
5889:
5900: end_of_text in out nocopy boolean) is
5901: clob_id number;
5902: attr_name varchar2(30);
5903: begin
5904: if nid = wf_notification.last_nid
5905: and wf_notification.clob_exists is not null then
5906: wf_notification.read_Clob(doctext, end_of_text);
5907: else
5908: --create a clob
5901: clob_id number;
5902: attr_name varchar2(30);
5903: begin
5904: if nid = wf_notification.last_nid
5905: and wf_notification.clob_exists is not null then
5906: wf_notification.read_Clob(doctext, end_of_text);
5907: else
5908: --create a clob
5909: wf_notification.newclob(wf_notification.temp_clob,null);
5902: attr_name varchar2(30);
5903: begin
5904: if nid = wf_notification.last_nid
5905: and wf_notification.clob_exists is not null then
5906: wf_notification.read_Clob(doctext, end_of_text);
5907: else
5908: --create a clob
5909: wf_notification.newclob(wf_notification.temp_clob,null);
5910: wf_notification.clob_exists :=1;
5905: and wf_notification.clob_exists is not null then
5906: wf_notification.read_Clob(doctext, end_of_text);
5907: else
5908: --create a clob
5909: wf_notification.newclob(wf_notification.temp_clob,null);
5910: wf_notification.clob_exists :=1;
5911:
5912: --set the clob text
5913: wf_notification.getattrclob(nid, aname,
5906: wf_notification.read_Clob(doctext, end_of_text);
5907: else
5908: --create a clob
5909: wf_notification.newclob(wf_notification.temp_clob,null);
5910: wf_notification.clob_exists :=1;
5911:
5912: --set the clob text
5913: wf_notification.getattrclob(nid, aname,
5914: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
5909: wf_notification.newclob(wf_notification.temp_clob,null);
5910: wf_notification.clob_exists :=1;
5911:
5912: --set the clob text
5913: wf_notification.getattrclob(nid, aname,
5914: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
5915:
5916: --retreive all the clob text in 32K chunks.
5917: if attr_name = aname then
5910: wf_notification.clob_exists :=1;
5911:
5912: --set the clob text
5913: wf_notification.getattrclob(nid, aname,
5914: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
5915:
5916: --retreive all the clob text in 32K chunks.
5917: if attr_name = aname then
5918: -- the attribute was substituted with something in the clob so print it.
5915:
5916: --retreive all the clob text in 32K chunks.
5917: if attr_name = aname then
5918: -- the attribute was substituted with something in the clob so print it.
5919: wf_notification.clob_chunk := 0;
5920: wf_notification.read_Clob(doctext, end_of_text);
5921:
5922: else
5923: --the aname was not substituted so just print it.
5916: --retreive all the clob text in 32K chunks.
5917: if attr_name = aname then
5918: -- the attribute was substituted with something in the clob so print it.
5919: wf_notification.clob_chunk := 0;
5920: wf_notification.read_Clob(doctext, end_of_text);
5921:
5922: else
5923: --the aname was not substituted so just print it.
5924: doctext := aname;
5924: doctext := aname;
5925: end if;
5926:
5927: --finally set the global vars
5928: wf_notification.last_nid:=nid;
5929:
5930:
5931: end if;
5932:
5931: end if;
5932:
5933: exception
5934: when others then
5935: wf_core.context('Wf_Notification', 'ReadAttrClob');
5936: raise;
5937: end ReadAttrClob;
5938:
5939: --
5951: l_lang varchar2(64);
5952: l_terr varchar2(64);
5953: l_chrs varchar2(64);
5954: begin
5955: -- <7514495> no cached variable in Wf_Notification now?
5956: -- if (Wf_Notification.nls_language is not null) then
5957: -- return Wf_Notification.nls_language;
5958: -- end if;
5959:
5952: l_terr varchar2(64);
5953: l_chrs varchar2(64);
5954: begin
5955: -- <7514495> no cached variable in Wf_Notification now?
5956: -- if (Wf_Notification.nls_language is not null) then
5957: -- return Wf_Notification.nls_language;
5958: -- end if;
5959:
5960: GetNLSLanguage(l_lang, l_terr, l_chrs);
5953: l_chrs varchar2(64);
5954: begin
5955: -- <7514495> no cached variable in Wf_Notification now?
5956: -- if (Wf_Notification.nls_language is not null) then
5957: -- return Wf_Notification.nls_language;
5958: -- end if;
5959:
5960: GetNLSLanguage(l_lang, l_terr, l_chrs);
5961: return l_lang;
5984: l_nlsCalendar varchar2(64);
5985:
5986: begin
5987: -- <7514495> now uses centralized api
5988: -- if (Wf_Notification.nls_language is null) then
5989: -- tmpbuf := userenv('LANGUAGE');
5990: -- pos1 := instr(tmpbuf, '_');
5991: -- pos2 := instr(tmpbuf, '.');
5992: --
5989: -- tmpbuf := userenv('LANGUAGE');
5990: -- pos1 := instr(tmpbuf, '_');
5991: -- pos2 := instr(tmpbuf, '.');
5992: --
5993: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
5994: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
5995: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
5996: -- end if;
5997: --
5990: -- pos1 := instr(tmpbuf, '_');
5991: -- pos2 := instr(tmpbuf, '.');
5992: --
5993: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
5994: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
5995: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
5996: -- end if;
5997: --
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5991: -- pos2 := instr(tmpbuf, '.');
5992: --
5993: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
5994: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
5995: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
5996: -- end if;
5997: --
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5999: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
5994: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
5995: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
5996: -- end if;
5997: --
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5999: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6000: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6001:
6002: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
5995: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
5996: -- end if;
5997: --
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5999: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6000: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6001:
6002: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6003: p_nlsTerritory => GetNLSLanguage.territory,
5996: -- end if;
5997: --
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5999: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6000: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6001:
6002: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6003: p_nlsTerritory => GetNLSLanguage.territory,
6004: p_nlsCode => GetNLSLanguage.charset,
5998: -- GetNLSLanguage.language := Wf_Notification.nls_language;
5999: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6000: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6001:
6002: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6003: p_nlsTerritory => GetNLSLanguage.territory,
6004: p_nlsCode => GetNLSLanguage.charset,
6005: p_nlsDateFormat => l_nlsDateFormat,
6006: p_nlsDateLanguage => l_nlsDateLang,
6022: l_language varchar2(30);
6023: l_territory varchar2(30);
6024: begin
6025: -- <7514495> now we use centralized api
6026: -- if (p_language = Wf_Notification.nls_language) then
6027: -- return;
6028: -- end if;
6029: --
6030: -- l_language := ''''||p_language||'''';
6033: -- DBMS_SESSION.SET_NLS('NLS_LANGUAGE', l_language);
6034: -- DBMS_SESSION.SET_NLS('NLS_TERRITORY', l_territory);
6035: --
6036: -- -- update cache
6037: -- Wf_Notification.nls_language := p_language;
6038: -- Wf_Notification.nls_territory := p_territory;
6039:
6040: wf_notification_util.SetNLSContext( -- p_nid ,
6041: p_nlsLanguage => l_language,
6034: -- DBMS_SESSION.SET_NLS('NLS_TERRITORY', l_territory);
6035: --
6036: -- -- update cache
6037: -- Wf_Notification.nls_language := p_language;
6038: -- Wf_Notification.nls_territory := p_territory;
6039:
6040: wf_notification_util.SetNLSContext( -- p_nid ,
6041: p_nlsLanguage => l_language,
6042: p_nlsTerritory => p_territory
6036: -- -- update cache
6037: -- Wf_Notification.nls_language := p_language;
6038: -- Wf_Notification.nls_territory := p_territory;
6039:
6040: wf_notification_util.SetNLSContext( -- p_nid ,
6041: p_nlsLanguage => l_language,
6042: p_nlsTerritory => p_territory
6043: -- ok not to pass next parameters
6044: -- as fnd_global.set_nls_context won't set
6050: -- p_nlsCalendar
6051: );
6052: exception
6053: when others then
6054: Wf_Core.Context('Wf_Notification', 'SetNLSLanguage', p_language, p_territory);
6055: raise;
6056: end SetNLSLanguage;
6057:
6058: --
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
6112: , 'BEGIN, nid='||nid||', username='||username||', langcode='||langcode);
6113: end if;
6114:
6115: -- 8286459. Get value, and always reset flag;
6116: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6117: wf_notification_util.g_allowDeferDenormalize := true;
6118:
6119: -- <7720908>
6120: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6113: end if;
6114:
6115: -- 8286459. Get value, and always reset flag;
6116: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6117: wf_notification_util.g_allowDeferDenormalize := true;
6118:
6119: -- <7720908>
6120: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6121: , l_orig_nlsDateFormat, l_orig_nlsDateLang
6116: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6117: wf_notification_util.g_allowDeferDenormalize := true;
6118:
6119: -- <7720908>
6120: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6121: , l_orig_nlsDateFormat, l_orig_nlsDateLang
6122: , l_orig_nlsNumChars, l_orig_nlsSort, l_orig_nlsCalendar);
6123:
6124: if (l_orig_nlsCalendar is null) then
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));
6279: wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, l_module, 'END - deferring denormalization');
6280: end if;
6281:
6282: -- Raise the event
6283: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.denormalize',
6284: p_event_key => to_char(nid),
6285: p_parameters => l_parameterlist);
6286:
6287: return;
6302: -- If FROM_ROLE has not been defined yet, we tried to draw this from
6303: -- #FROM_ROLE.
6304: if (l_from_role is NULL) then
6305: begin
6306: l_from_role := Wf_Notification.GetAttrText(nid, '#FROM_ROLE');
6307: exception
6308: when OTHERS then
6309: wf_core.clear; -- clear the error stack
6310: l_from_role := NULL;
6319:
6320:
6321: -- Subject
6322: -- skilaru 08-MAY-03 bug fix 2883247
6323: l_subject := Wf_Notification.GetSubject(nid, 'text/plain');
6324:
6325: if ( l_logSTMT ) then
6326: wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, l_module, 'subject('||l_subject||')');
6327: end if;
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,
6345: wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, l_module, 'END');
6346: end if;
6347: exception
6348: when OTHERS then
6349: wf_core.context('Wf_Notification', 'Denormalize_Notification',
6350: to_char(nid), username);
6351: raise;
6352: end Denormalize_Notification;
6353:
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'
6387: xmessageName := nvl(messageName, '%');
6388:
6389: for c in fyiNid LOOP
6390:
6391: WF_NOTIFICATION.Close( c.NOTIFICATION_ID );
6392:
6393: end loop;
6394:
6395: end closeFYI;
6424: begin
6425: -- get the signature policy for the notification
6426: -- wf_mail.GetSignaturePolicy(nid, sig_policy);
6427: begin
6428: sig_policy := Wf_Notification.GetAttrText(nid, '#WF_SIG_POLICY');
6429: exception
6430: when others then
6431: if (wf_core.error_name = 'WFNTF_ATTR') then
6432: wf_core.clear;
6445: Elsif(sig_required = 'Y') then
6446:
6447: -- bug 2779748: Cancelled Notification does not need to be signed
6448: begin
6449: if (WF_Notification.GetAttrText(nid, 'RESULT') = '#SIG_CANCEL') then
6450: return TRUE;
6451: end if;
6452: exception
6453: when others then
6461: sig_id := WF_DIG_SIG_EVIDENCE_STORE.GetMostRecentSigID('WF_NTF', nid);
6462: begin
6463: -- #WF_SIG_ID may be defined as text or number... Now both will work
6464: -- Eventually should use GetAttrNumber
6465: l_attr_sigid := to_number(Wf_Notification.GetAttrText(nid, '#WF_SIG_ID'));
6466: exception
6467: when others then
6468: if (wf_core.error_name = 'WFNTF_ATTR') then
6469: wf_core.clear;
6489: return TRUE;
6490:
6491: exception
6492: when others then
6493: wf_core.context('Wf_Notification', 'NtfSignRequirementsMet', to_char(nid));
6494: raise;
6495: end NtfSignRequirementsMet;
6496:
6497: --
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
6639: tvalue := myusername;
6640: nvalue := nid;
6641: -- ### Review Note 2 - cb is from table
6642: --Check for bug#3827935
6643: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
6644: --Throw the Illegal exception when the check fails
6645:
6646:
6647: -- BINDVAR_SCAN_IGNORE
6664: -- if (IsValidInfoRole(nid,username)) then
6665:
6666: -- shanjgik 01-JUL-03 bug 2887130
6667: -- get mail preference of the user who will respond with more information
6668: mailpref := wf_notification.GetMailPreference (username, null, null);
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
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,
6697: null, '', 'MAIL')
6698: where NOTIFICATION_ID = nid;
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,
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
6727: -- end if;
6728: end if;
6729:
6730: /* implement the above loop recursively */
6731: if (cnt > wf_notification.max_forward) then
6732: -- it means max_forward must have been exceeded. Treat as a loop error.
6733: wf_core.token('NID', to_char(nid));
6734: wf_core.raise('WFNTF_ROUTE_LOOP');
6735: end if;
6734: wf_core.raise('WFNTF_ROUTE_LOOP');
6735: end if;
6736:
6737: -- Calling RouteMoreInfo to check and handle if there are any Routing Rules
6738: wf_notification.RouteMoreInfo(nid, myusername, action_source, cnt);
6739:
6740: -- if we are here, mean we are going to raise
6741: -- oracle.apps.wf.notification.question event.
6742: l_event_name := 'oracle.apps.wf.notification.question';
6737: -- Calling RouteMoreInfo to check and handle if there are any Routing Rules
6738: wf_notification.RouteMoreInfo(nid, myusername, action_source, cnt);
6739:
6740: -- if we are here, mean we are going to raise
6741: -- oracle.apps.wf.notification.question event.
6742: l_event_name := 'oracle.apps.wf.notification.question';
6743:
6744:
6745: -- answer mode
6738: wf_notification.RouteMoreInfo(nid, myusername, action_source, cnt);
6739:
6740: -- if we are here, mean we are going to raise
6741: -- oracle.apps.wf.notification.question event.
6742: l_event_name := 'oracle.apps.wf.notification.question';
6743:
6744:
6745: -- answer mode
6746: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
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:
6791: nvalue := nid;
6792: g_context_new_role := l_question_role;
6793: -- ### Review Note 2 - cb is from table
6794: -- Check for bug#3827935
6795: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
6796: --Throw the Illegal exception when the check fails
6797:
6798: -- BINDVAR_SCAN_IGNORE
6799: sqlbuf := 'begin '||cb||
6810: end if;
6811:
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,
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,
6824: 'DISABLED', 'FAILED',
6825: null, '', 'MAIL')
6826: where NOTIFICATION_ID = nid;
6827:
6828: Wf_Notification.SetComments(nid, l_from_role, recipient_role, 'ANSWER', action_source, substrb(comment,1,4000));
6829:
6830: -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above,
6831: -- so the LANGUAGE should be current userenv('LANG').
6832: -- insert into WF_COMMENTS (
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
6849: -- where NOTIFICATION_ID = nid;
6850:
6851: -- <
6852: -- if we are here, mean we are going to raise
6853: -- oracle.apps.wf.notification.answer event.
6854: l_event_name := 'oracle.apps.wf.notification.answer';
6855:
6856: end if;
6857:
6850:
6851: -- <
6852: -- if we are here, mean we are going to raise
6853: -- oracle.apps.wf.notification.answer event.
6854: l_event_name := 'oracle.apps.wf.notification.answer';
6855:
6856: end if;
6857:
6858: -- Bug 8509185. Need to make clob_exists null so that GetFullBody reads the
6856: end if;
6857:
6858: -- Bug 8509185. Need to make clob_exists null so that GetFullBody reads the
6859: -- notification entirely
6860: wf_notification.clob_exists := null;
6861:
6862: -- Send the notification through email
6863: -- wf_xml.EnqueueNotification(nid);
6864:
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||':'||
6888: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
6889:
6890:
6891: -- Raise the event
6892: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
6893: -- p_event_key => to_char(nid),
6894: -- p_parameters => l_parameterlist);
6895:
6896: -- <
6899: p_parameters => l_parameterlist);
6900:
6901: exception
6902: when OTHERS then
6903: Wf_Core.Context('Wf_Notification', 'UpdateInfo', to_char(nid), username, wl_user, action_source);
6904: raise;
6905: end UpdateInfo;
6906:
6907: --
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
7032:
7033: if (l_callback_function is not null) then
7034: tvalue := l_session_user;
7035: nvalue := p_nid;
7036: l_charcheck := wf_notification_util.CheckIllegalChar(l_callback_function);
7037: l_anon_block := 'begin '||l_callback_function||
7038: '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;';
7039:
7040: execute immediate l_anon_block using
7048:
7049: end if;
7050:
7051: -- get mail preference of the user who will respond with more information
7052: l_mail_preference := wf_notification.GetMailPreference (p_new_user, null, null);
7053:
7054: -- if there is a valid session, then we can update the FROM_ROLE
7055: -- and FROM_USER accurately.
7056:
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,
7069: 'SUMHTML','',
7070: 'DISABLED', 'FAILED',
7071: null, '', 'MAIL')
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,
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,
7099: 'DISABLED', 'FAILED',
7100: null, '', 'MAIL')
7101: where NOTIFICATION_ID = p_nid;
7102: end if;
7103: Wf_Notification.SetComments(p_nid, l_session_user, p_new_user, 'TRANSFER_QUESTION', p_action_source, substrb(p_comment,1,4000));
7104: end if;
7105:
7106: --Calling RouteMoreInfo API to check whether there are any Routing rules for the recipient
7107: -- implement the loop recursively
7104: end if;
7105:
7106: --Calling RouteMoreInfo API to check whether there are any Routing rules for the recipient
7107: -- implement the loop recursively
7108: if (p_count > wf_notification.max_forward) then
7109: -- it means max_forward must have been exceeded. Treat as a loop error.
7110: wf_core.token('NID', to_char(p_nid));
7111: wf_core.raise('WFNTF_ROUTE_LOOP');
7112: end if;
7109: -- it means max_forward must have been exceeded. Treat as a loop error.
7110: wf_core.token('NID', to_char(p_nid));
7111: wf_core.raise('WFNTF_ROUTE_LOOP');
7112: end if;
7113: wf_notification.RouteMoreInfo(p_nid, p_wl_user, p_action_source, p_count);
7114: end if;
7115:
7116: -- if we are here, mean we are going to raise
7117: -- oracle.apps.wf.notification.question event.
7113: wf_notification.RouteMoreInfo(p_nid, p_wl_user, p_action_source, p_count);
7114: end if;
7115:
7116: -- if we are here, mean we are going to raise
7117: -- oracle.apps.wf.notification.question event.
7118: l_event_name := 'oracle.apps.wf.notification.question';
7119:
7120: -- Need to make clob_exists null so that GetFullBody reads the
7121: -- notification entirely
7114: end if;
7115:
7116: -- if we are here, mean we are going to raise
7117: -- oracle.apps.wf.notification.question event.
7118: l_event_name := 'oracle.apps.wf.notification.question';
7119:
7120: -- Need to make clob_exists null so that GetFullBody reads the
7121: -- notification entirely
7122: wf_notification.clob_exists := null;
7118: l_event_name := 'oracle.apps.wf.notification.question';
7119:
7120: -- Need to make clob_exists null so that GetFullBody reads the
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);
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||':'||
7150: p_parameters => l_parameterlist);
7151:
7152: exception
7153: when OTHERS then
7154: Wf_Core.Context('Wf_Notification', 'TransferMoreInfo', to_char(p_nid), p_new_user, p_wl_user, p_action_source);
7155: raise;
7156: end TransferMoreInfo;
7157:
7158: --
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;
7227: wf_core.token('ROLE', WF_Directory.GetRoleDisplayName(l_newRole));
7228: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE_TO_ROLE');
7229:
7230: -- implement the above loop recursively
7231: if (p_count > wf_notification.max_forward) then
7232: -- it means max_forward must have been exceeded. Treat as a loop error.
7233: wf_core.token('NID', to_char(p_nid));
7234: wf_core.raise('WFNTF_ROUTE_LOOP');
7235: end if;
7241: if l_ruleAction = 'RESPOND' then -- if there is a vacation rule for Response
7242: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE');
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,
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,
7255: 'DISABLED', 'FAILED',
7256: null, '', 'MAIL')
7257: where NOTIFICATION_ID = p_nid;
7258:
7259: Wf_Notification.SetComments(p_nid, l_more_info_role, l_recip_role, 'ANSWER', p_action_source, substrb(l_sysComment,1,4000));
7260: end if;
7261: end if;
7262:
7263: exception
7261: end if;
7262:
7263: exception
7264: when OTHERS then
7265: Wf_Core.Context('Wf_Notification', 'RouteMoreInfo', to_char(p_nid), l_newRole, p_wl_user, p_action_source);
7266: raise;
7267: end RouteMoreInfo;
7268:
7269: -- bug 2474562
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
7373: end if;
7374: return(TRUE);
7375: exception
7376: when OTHERS then
7377: Wf_Core.Context('Wf_Notification','IsValidInfoRole',to_char(nid),username);
7378: raise;
7379: end IsValidInfoRole;
7380:
7381: -- UpdateInfo2 - bug 2282139
7442:
7443: begin
7444: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7445: wf_log_pkg.string(wf_log_pkg.level_procedure,
7446: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.Begin',
7447: 'NID: '||to_char(nid) ||', Username: '||username||
7448: ' From: '||from_email);
7449: end if;
7450:
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
7459: -- Donot process the request if the notification is not open.
7460: if (l_stat <> 'OPEN') then
7461: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7462: wf_log_pkg.string(wf_log_pkg.level_statement,
7463: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.not_open',
7464: 'Notification '||to_char(nid)||' is not OPEN. Returning.');
7465: end if;
7466: return;
7467: end if;
7480: end if;
7481:
7482: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7483: wf_log_pkg.string(wf_log_pkg.level_statement,
7484: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.got_user',
7485: 'Email: '||from_email||' User: '||myusername||' DispName: '||mydispname);
7486: end if;
7487:
7488: --Bug 3065814
7546: -- Routing rule defined
7547: wf_core.token('ROLE', WF_Directory.GetRoleDisplayName(l_newRole));
7548: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE_TO_ROLE');
7549: if myusername is not null then
7550: wf_notification.SetComments(nid, username, myusername, 'ANSWER', null, l_sysComment);
7551: else
7552: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', null, l_sysComment);
7553: end if;
7554:
7548: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE_TO_ROLE');
7549: if myusername is not null then
7550: wf_notification.SetComments(nid, username, myusername, 'ANSWER', null, l_sysComment);
7551: else
7552: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', null, l_sysComment);
7553: end if;
7554:
7555: /* implement the above loop recursively */
7556: if (cnt > wf_notification.max_forward) then
7552: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', null, l_sysComment);
7553: end if;
7554:
7555: /* implement the above loop recursively */
7556: if (cnt > wf_notification.max_forward) then
7557: -- it means max_forward must have been exceeded. Treat as a loop error.
7558: wf_core.token('NID', to_char(nid));
7559: wf_core.raise('WFNTF_ROUTE_LOOP');
7560: end if;
7565:
7566: if l_ruleAction = 'RESPOND' then
7567: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE');
7568: if myusername is not null then
7569: wf_notification.SetComments(nid, username, myusername, 'ANSWER', null, l_sysComment);
7570: else
7571: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', null, l_sysComment);
7572: end if;
7573: end if;
7567: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE');
7568: if myusername is not null then
7569: wf_notification.SetComments(nid, username, myusername, 'ANSWER', null, l_sysComment);
7570: else
7571: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', null, l_sysComment);
7572: end if;
7573: end if;
7574:
7575: -- do not want it hung when some one is doing update.
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
7587: end;
7588:
7589: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7590: wf_log_pkg.string(wf_log_pkg.level_statement,
7591: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.question',
7592: 'Updating QUESTION');
7593: end if;
7594:
7595: if (cb is not null) then
7596: tvalue := myusername;
7597: nvalue := nid;
7598: -- ### Review Note 2 - cb is from table
7599: -- Check for bug#3827935
7600: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
7601: --Throw the Illegal exception when the check fails
7602:
7603: -- BINDVAR_SCAN_IGNORE
7604: sqlbuf := 'begin '||cb||
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,
7635: SENT_DATE = SYSDATE
7636: where NOTIFICATION_ID = nid;
7637: /*end if; */
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 (
7636: where NOTIFICATION_ID = nid;
7637: /*end if; */
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,
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:
7662: end if;
7663:
7664: -- <
7665: -- we are here, mean we are going to raise
7666: -- oracle.apps.wf.notification.question event.
7667: l_event_name := 'oracle.apps.wf.notification.question';
7668:
7669: -- answer mode
7670: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
7663:
7664: -- <
7665: -- we are here, mean we are going to raise
7666: -- oracle.apps.wf.notification.question event.
7667: l_event_name := 'oracle.apps.wf.notification.question';
7668:
7669: -- answer mode
7670: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
7671: -- no denormalization is needed here.
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:
7718: end if;
7719:
7720: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7721: wf_log_pkg.string(wf_log_pkg.level_statement,
7722: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
7723: 'Updating ANSWER');
7724: end if;
7725:
7726: update WF_NOTIFICATIONS
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,
7730: MORE_INFO_ROLE = null,
7731: SENT_DATE = SYSDATE
7732: where NOTIFICATION_ID = nid;
7733:
7734: Wf_Notification.SetComments(nid, myusername, l_recip_role, 'ANSWER', null, substrb(comment,1,4000));
7735:
7736: -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above,
7737: -- so the LANGUAGE should be current userenv('LANG').
7738: -- insert into WF_COMMENTS (
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.
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.
7759: l_event_name := 'oracle.apps.wf.notification.answer';
7760:
7761: end if; -- End of Answer mode
7762:
7755: -- where NOTIFICATION_ID = nid;
7756:
7757: -- we are here, mean we are going to raise
7758: -- oracle.apps.wf.notification.answer event.
7759: l_event_name := 'oracle.apps.wf.notification.answer';
7760:
7761: end if; -- End of Answer mode
7762:
7763: -- Send the notification through email
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);
7794: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
7795:
7796:
7797: -- Raise the event
7798: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
7799: -- p_event_key => to_char(nid),
7800: -- p_parameters => l_parameterlist);
7801:
7802: -- <
7805: p_parameters => l_parameterlist);
7806:
7807: exception
7808: when OTHERS then
7809: Wf_Core.Context('Wf_Notification', 'UpdateInfo2', to_char(nid), username, from_email);
7810: raise;
7811: end UpdateInfo2;
7812:
7813:
7847: l_language varchar2(30);
7848: role_info_tbl wf_directory.wf_local_roles_tbl_type;
7849:
7850: begin
7851: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7852: 'NID: '||to_char(nid));
7853:
7854: -- Do not allow reply when a question has not been asked, or it has
7855: -- already been answered. In both cases, MORE_INFO_ROLE is set to null.
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:
7905:
7906: end if;
7907:
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',
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,
7916: MORE_INFO_ROLE = null,
7917: SENT_DATE = SYSDATE
7918: where NOTIFICATION_ID = nid;
7919:
7920: Wf_Notification.SetComments(nid, moreinforesponder, l_recipient_role, 'ANSWER', null, substrb(moreinfoanswer,1,4000));
7921:
7922: -- Send the notification through email
7923: -- Enqueuing has been moved to a subscription for forward
7924: -- compatability. The subscription need only be enabled to use
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);
7952: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
7953:
7954:
7955: -- Raise the event
7956: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
7957: -- p_event_key => to_char(nid),
7958: -- p_parameters => l_parameterlist);
7959:
7960: -- <
7957: -- p_event_key => to_char(nid),
7958: -- p_parameters => l_parameterlist);
7959:
7960: -- <
7961: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.answer',
7962: p_event_key => to_char(nid),
7963: p_parameters => l_parameterlist);
7964:
7965: exception
7963: p_parameters => l_parameterlist);
7964:
7965: exception
7966: when OTHERS then
7967: Wf_Core.Context('Wf_Notification', 'UpdateInfoGuest', to_char(nid), moreinforesponder);
7968: raise;
7969: end UpdateInfoGuest;
7970:
7971:
7982: l_hide varchar2(1);
7983: begin
7984: -- Get value for #HIDE_MOREINFO attribute for the notification
7985: begin
7986: l_hide := substrb(WF_NOTIFICATION.GetAttrText(nid, '#HIDE_MOREINFO'), 1, 1);
7987: -- Bugfix 2880029 - changed sacsharm - 03/31/03
7988: -- Only if attribute value is explicitly 'Y' hide Request More Info. else
7989: -- if it is null or 'N' or any other character donot hide Request More Info.
7990: l_hide := upper(nvl(l_hide, 'N'));
8004: end;
8005: return (l_hide);
8006: exception
8007: when others then
8008: wf_core.context('Wf_Notification', 'HideMoreInfo', to_char(nid));
8009: raise;
8010: end HideMoreInfo;
8011:
8012: -- GetComments
8040: close c_ques;
8041:
8042: -- Call the GetComments2 procedure to get the Action History for only
8043: -- More Info Requests. This procedure was doing that previously
8044: Wf_Notification.GetComments2(p_nid => nid,
8045: p_display_type => display_type,
8046: p_hide_reassign => 'Y',
8047: p_hide_requestinfo => 'N',
8048: p_action_history => html_history);
8048: p_action_history => html_history);
8049:
8050: exception
8051: when others then
8052: wf_core.context('Wf_Notification', 'GetComments', to_char(nid), display_type);
8053: raise;
8054: end GetComments;
8055:
8056: --
8293:
8294: j := 1;
8295: -- Action History Title
8296: cells(j) := wf_core.translate('NUM');
8297: if (p_display_type = wf_notification.doc_html) then
8298: cells(j) := 'S5%:'||cells(j);
8299: end if;
8300:
8301: j := j+1;
8299: end if;
8300:
8301: j := j+1;
8302: cells(j) := wf_core.translate('ACTION_DATE');
8303: if (p_display_type = wf_notification.doc_html) then
8304: cells(j) := 'S15%:'||cells(j);
8305: end if;
8306:
8307: j := j+1;
8305: end if;
8306:
8307: j := j+1;
8308: cells(j) := wf_core.translate('ACTION');
8309: if (p_display_type = wf_notification.doc_html) then
8310: cells(j) := 'S10%:'||cells(j);
8311: end if;
8312:
8313: j := j+1;
8311: end if;
8312:
8313: j := j+1;
8314: cells(j) := wf_core.translate('FROM');
8315: if (p_display_type = wf_notification.doc_html) then
8316: cells(j) := 'S15%:'||cells(j);
8317: end if;
8318:
8319: j := j+1;
8317: end if;
8318:
8319: j := j+1;
8320: cells(j) := wf_core.translate('TO');
8321: if (p_display_type = wf_notification.doc_html) then
8322: cells(j) := 'S15%:'||cells(j);
8323: end if;
8324:
8325: j := j+1;
8323: end if;
8324:
8325: j := j+1;
8326: cells(j) := wf_core.translate('DETAILS');
8327: if (p_display_type = wf_notification.doc_html) then
8328: cells(j) := 'S40%:'||cells(j);
8329: end if;
8330:
8331: j := j+1;
8346: j := j+1;
8347:
8348: -- Bug 9173224, Added by David on March 02,2010
8349: -- Convert server datetime to local datetime according to the client timezone.
8350: l_comm_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_comm_rec.h_action_date);
8351:
8352: if (p_display_type = wf_notification.doc_html) then
8353: --
8354: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8348: -- Bug 9173224, Added by David on March 02,2010
8349: -- Convert server datetime to local datetime according to the client timezone.
8350: l_comm_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_comm_rec.h_action_date);
8351:
8352: if (p_display_type = wf_notification.doc_html) then
8353: --
8354: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8355: else
8356: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8350: l_comm_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_comm_rec.h_action_date);
8351:
8352: if (p_display_type = wf_notification.doc_html) then
8353: --
8354: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8355: else
8356: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8357: end if;
8358:
8352: if (p_display_type = wf_notification.doc_html) then
8353: --
8354: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8355: else
8356: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8357: end if;
8358:
8359: j := j+1;
8360:
8369: --end if;
8370: l_action_str := Wf_Core.Translate(l_action);
8371: end if;
8372:
8373: if (p_display_type = wf_notification.doc_html) then
8374: cells(j) := 'S:'||l_action_str;
8375: else
8376: cells(j) := l_action_str;
8377: end if;
8376: cells(j) := l_action_str;
8377: end if;
8378:
8379: j := j+1;
8380: if (p_display_type = wf_notification.doc_html) then
8381: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_from_user);
8382: else
8383: cells(j) := l_comm_rec.h_from_user;
8384: end if;
8377: end if;
8378:
8379: j := j+1;
8380: if (p_display_type = wf_notification.doc_html) then
8381: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_from_user);
8382: else
8383: cells(j) := l_comm_rec.h_from_user;
8384: end if;
8385:
8383: cells(j) := l_comm_rec.h_from_user;
8384: end if;
8385:
8386: j := j+1;
8387: if (p_display_type = wf_notification.doc_html) then
8388: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_to_user);
8389: else
8390: cells(j) := l_comm_rec.h_to_user;
8391: end if;
8384: end if;
8385:
8386: j := j+1;
8387: if (p_display_type = wf_notification.doc_html) then
8388: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_to_user);
8389: else
8390: cells(j) := l_comm_rec.h_to_user;
8391: end if;
8392:
8396: if (l_note = '#WF_NOTE#') then
8397: begin
8398: SELECT text_value
8399: INTO l_note
8400: FROM wf_notification_attributes
8401: WHERE notification_id = l_comm_rec.h_notification_id
8402: AND name = 'WF_NOTE';
8403: exception
8404: when no_data_found then
8404: when no_data_found then
8405: l_note := '';
8406: end;
8407: end if;
8408: if (p_display_type = wf_notification.doc_html) then
8409: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8410: end if;
8411: cells(j) := l_note;
8412:
8405: l_note := '';
8406: end;
8407: end if;
8408: if (p_display_type = wf_notification.doc_html) then
8409: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8410: end if;
8411: cells(j) := l_note;
8412:
8413: if (p_display_type = wf_notification.doc_html) then
8409: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8410: end if;
8411: cells(j) := l_note;
8412:
8413: if (p_display_type = wf_notification.doc_html) then
8414: if (cells(j) is null) then
8415: cells(j) := 'S: ';
8416: else
8417: cells(j) := 'S:'||cells(j);
8437: j := j+1;
8438:
8439: -- Bug 9173224, Added by David on March 02,2010
8440: -- Convert server datetime to local datetime according to the client timezone.
8441: l_ntf_hist_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_ntf_hist_rec.h_action_date);
8442:
8443: if (p_display_type = wf_notification.doc_html) then
8444: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8445: else
8439: -- Bug 9173224, Added by David on March 02,2010
8440: -- Convert server datetime to local datetime according to the client timezone.
8441: l_ntf_hist_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_ntf_hist_rec.h_action_date);
8442:
8443: if (p_display_type = wf_notification.doc_html) then
8444: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8445: else
8446: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8447: end if;
8440: -- Convert server datetime to local datetime according to the client timezone.
8441: l_ntf_hist_rec.h_action_date := wf_notification_util.GetLocalDateTime(l_ntf_hist_rec.h_action_date);
8442:
8443: if (p_display_type = wf_notification.doc_html) then
8444: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8445: else
8446: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8447: end if;
8448:
8442:
8443: if (p_display_type = wf_notification.doc_html) then
8444: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8445: else
8446: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8447: end if;
8448:
8449: j := j+1;
8450:
8454: --l_action := substr(l_action, 1, l_pos-1);
8455: --end if;
8456: l_action_str := Wf_Core.Translate(l_action);
8457:
8458: if (p_display_type = wf_notification.doc_html) then
8459: cells(j) := 'S:'||l_action_str;
8460: else
8461: cells(j) := l_action_str;
8462: end if;
8461: cells(j) := l_action_str;
8462: end if;
8463:
8464: j := j+1;
8465: if (p_display_type = wf_notification.doc_html) then
8466: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_from_user);
8467: else
8468: cells(j) := l_ntf_hist_rec.h_from_user;
8469: end if;
8462: end if;
8463:
8464: j := j+1;
8465: if (p_display_type = wf_notification.doc_html) then
8466: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_from_user);
8467: else
8468: cells(j) := l_ntf_hist_rec.h_from_user;
8469: end if;
8470:
8468: cells(j) := l_ntf_hist_rec.h_from_user;
8469: end if;
8470:
8471: j := j+1;
8472: if (p_display_type = wf_notification.doc_html) then
8473: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_to_user);
8474: else
8475: cells(j) := l_ntf_hist_rec.h_to_user;
8476: end if;
8469: end if;
8470:
8471: j := j+1;
8472: if (p_display_type = wf_notification.doc_html) then
8473: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_to_user);
8474: else
8475: cells(j) := l_ntf_hist_rec.h_to_user;
8476: end if;
8477:
8476: end if;
8477:
8478: j := j+1;
8479: l_note := l_ntf_hist_rec.h_comment;
8480: if (p_display_type = wf_notification.doc_html) then
8481: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8482: end if;
8483: cells(j) := l_note;
8484:
8477:
8478: j := j+1;
8479: l_note := l_ntf_hist_rec.h_comment;
8480: if (p_display_type = wf_notification.doc_html) then
8481: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8482: end if;
8483: cells(j) := l_note;
8484:
8485: if (p_display_type = wf_notification.doc_html) then
8481: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8482: end if;
8483: cells(j) := l_note;
8484:
8485: if (p_display_type = wf_notification.doc_html) then
8486: if (cells(j) is null) then
8487: cells(j) := 'S: ';
8488: else
8489: cells(j) := 'S:'||cells(j);
8504: end if;
8505:
8506: -- Sequence is now based on the rownum, not the reverse
8507: -- for k in 0..(i-1) loop
8508: -- if (p_display_type = wf_notification.doc_html) then
8509: -- cells((k+1)*6+1) := 'C:'||to_char(i-k-1);
8510: -- else
8511: -- cells((k+1)*6+1) := to_char(i-k-1);
8512: -- end if;
8512: -- end if;
8513: -- end loop;
8514:
8515: -- Construct table from the cells
8516: if (p_display_type = wf_notification.doc_html) then
8517: table_width := '100%';
8518: -- bug 7718246 - set the table border to 1 only for action history
8519: table_border := '1';
8520: NTF_Table(cells=>cells,
8543: p_action_history := l_result;
8544:
8545: exception
8546: when others then
8547: wf_core.context('Wf_Notification', 'GetComments2', to_char(p_nid), p_display_type);
8548: raise;
8549: end GetComments2;
8550:
8551: --
8561: -- IN:
8562: -- nid - Notification id
8563: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8564: -- disptype - Requested display type. Valid values:
8565: -- wf_notification.doc_text - 'text/plain'
8566: -- wf_notification.doc_html - 'text/html'
8567: -- document - The blob into which
8568: -- aname - Attribute Name (the first part of the string that matches
8569: -- the attr list)
8562: -- nid - Notification id
8563: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8564: -- disptype - Requested display type. Valid values:
8565: -- wf_notification.doc_text - 'text/plain'
8566: -- wf_notification.doc_html - 'text/html'
8567: -- document - The blob into which
8568: -- aname - Attribute Name (the first part of the string that matches
8569: -- the attr list)
8570: --
8577: is
8578: doctype varchar2(500);
8579: begin
8580:
8581: Wf_Notification.GetAttrblob(nid, astring, disptype, document, doctype, aname);
8582:
8583: exception
8584: when others then
8585: wf_core.context('Wf_Notification', 'oldGetAttrblob', to_char(nid), aname,
8581: Wf_Notification.GetAttrblob(nid, astring, disptype, document, doctype, aname);
8582:
8583: exception
8584: when others then
8585: wf_core.context('Wf_Notification', 'oldGetAttrblob', to_char(nid), aname,
8586: disptype);
8587: raise;
8588: end GetAttrblob;
8589:
8597: -- IN:
8598: -- nid - Notification id
8599: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8600: -- disptype - Requested display type. Valid values:
8601: -- wf_notification.doc_text - 'text/plain'
8602: -- wf_notification.doc_html - 'text/html'
8603: -- document - The blob into which
8604: -- aname - Attribute Name (the string that matches
8605: -- the attr list)
8598: -- nid - Notification id
8599: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8600: -- disptype - Requested display type. Valid values:
8601: -- wf_notification.doc_text - 'text/plain'
8602: -- wf_notification.doc_html - 'text/html'
8603: -- document - The blob into which
8604: -- aname - Attribute Name (the string that matches
8605: -- the attr list)
8606: --
8632: begin
8633:
8634: -- Check args
8635: if ((nid is null) or (astring is null) or
8636: (disptype not in (wf_notification.doc_text,
8637: wf_notification.doc_html))) then
8638: wf_core.token('NID', to_char(nid));
8639: wf_core.token('ASTRING', aname);
8640: wf_core.token('DISPTYPE', disptype);
8633:
8634: -- Check args
8635: if ((nid is null) or (astring is null) or
8636: (disptype not in (wf_notification.doc_text,
8637: wf_notification.doc_html))) then
8638: wf_core.token('NID', to_char(nid));
8639: wf_core.token('ASTRING', aname);
8640: wf_core.token('DISPTYPE', disptype);
8641: wf_core.raise('WFSQL_ARGS');
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
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
8658: and wma.name = na.name
8659: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
8670: end;
8671:
8672: if (aname is not null) then
8673: -- Retrieve key string
8674: key := wf_notification.GetAttrText(nid, aname);
8675:
8676: -- If the key is empty then return a null string
8677: if (key is not null) then
8678:
8705:
8706: if (procarg is null) then
8707: procarg := NULL;
8708: else
8709: procarg := Wf_Notification.GetTextInternal(procarg, nid, target,
8710: FALSE, FALSE);
8711: end if;
8712:
8713: -- ### Review Note 1
8711: end if;
8712:
8713: -- ### Review Note 1
8714: -- Check for bug#3827935
8715: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
8716: --Throw the Illegal exception when the check fails
8717:
8718: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8719: wf_log_pkg.string2(wf_log_pkg.level_statement,
8716: --Throw the Illegal exception when the check fails
8717:
8718: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8719: wf_log_pkg.string2(wf_log_pkg.level_statement,
8720: 'wf.plsql.wf_notification.GetAttrBlob.plsqlblob_callout',
8721: 'Start executing PLSQLBLOB Doc procedure - '||procname, true);
8722: end if;
8723:
8724: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
8733: exception
8734: when others then
8735: if (wf_log_pkg.level_error >= fnd_log.g_current_runtime_level) then
8736: wf_log_pkg.string(wf_log_pkg.level_error,
8737: 'wf.plsql.wf_notification.GetAttrBLOB.plsqlblob_api',
8738: 'Error executing PLSQLBLOB Doc API - '||procname||' -> '||sqlerrm);
8739: end if;
8740:
8741: -- Bug 10130433: Throwing the WF error 'WFNTF_GEN_DOC' with all the error information
8748: end;
8749:
8750: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8751: wf_log_pkg.string2(wf_log_pkg.level_statement,
8752: 'wf.plsql.wf_notification.GetAttrBlob.plsqlblob_callout',
8753: 'End executing PLSQLBLOB Doc procedure - '||procname, false);
8754: end if;
8755:
8756: exception
8754: end if;
8755:
8756: exception
8757: when others then
8758: wf_core.context('Wf_Notification', 'GetAttrblob', to_char(nid), aname,
8759: disptype);
8760: raise;
8761: end GetAttrblob;
8762:
8762:
8763: --
8764: -- Set_NTF_Table_Direction
8765: -- Sets the default direction of notification tables
8766: -- generated through wf_notification.wf_ntf_history
8767: -- and wf_notification.wf_msg_attr
8768: procedure Set_NTF_Table_Direction(direction in varchar2)
8769: is
8770: begin
8763: --
8764: -- Set_NTF_Table_Direction
8765: -- Sets the default direction of notification tables
8766: -- generated through wf_notification.wf_ntf_history
8767: -- and wf_notification.wf_msg_attr
8768: procedure Set_NTF_Table_Direction(direction in varchar2)
8769: is
8770: begin
8771: table_direction := direction;
8773:
8774: --
8775: -- Set_NTF_Table_Type
8776: -- Sets the default table type for attr tables
8777: -- generated through wf_notification.wf_msg_attr
8778: procedure Set_NTF_Table_Type(tableType in varchar2)
8779: is
8780: begin
8781: table_type := tableType;
8793:
8794: function isFwkRegion(nid in number) return varchar2 is
8795:
8796: begin
8797: return isFwkRegion(nid, wf_notification.doc_html);
8798: end isFwkRegion;
8799:
8800:
8801: -- isFwkRegion
8818: lv_token_start number;
8819:
8820: cursor cur_hdr_region is
8821: select WNA.NAME, WNA.TEXT_VALUE
8822: from WF_NOTIFICATION_ATTRIBUTES WNA,
8823: WF_MESSAGE_ATTRIBUTES_VL WMA
8824: where WNA.NOTIFICATION_ID = nid
8825: and WMA.NAME = WNA.NAME
8826: and WMA.TYPE = 'DOCUMENT'
8844: return isFwkBody( nid, content_type);
8845: end if;
8846: exception
8847: when OTHERS then
8848: wf_core.context('Wf_Notification','isFwkRegion',to_char(nid), content_type);
8849: raise;
8850:
8851: End isFwkRegion;
8852:
8856: -- Algorithm: Function returns 'Y' if one of the following condition is met
8857: -- - If the first attribute referred in the body is of
8858: -- type 'DOCUMENT' and its value starts with 'JSP:/OA_HTML/OA.jsp?'
8859: -- - If the message body does not have any attributes refered except
8860: -- for WF_NOTIFICATION macro and simple text
8861:
8862: function isFwkBody(nid in number) return varchar2 is
8863:
8864:
8863:
8864:
8865: begin
8866: -- invoke overrided API with default as 'text/html'
8867: return isFwkBody(nid, wf_notification.doc_html);
8868:
8869: End isFwkBody;
8870:
8871: -- isFwkBody
8874: -- Algorithm: Function returns 'Y' if one of the following condition is met
8875: -- - If the first attribute referred in the body is of
8876: -- type 'DOCUMENT' and its value starts with 'JSP:/OA_HTML/OA.jsp?'
8877: -- - If the message body does not have any attributes refered except
8878: -- for WF_NOTIFICATION macro and simple text
8879: -- Auth : SSTOMAR
8880: function isFwkBody(nid in number, content_type in varchar2) return varchar2 is
8881:
8882: lv_body varchar2(32000);
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:
8904: -- as Framework based notification but If message body has simple text contains
8905: -- '& ' character without token name
8906: -- then according to below logic it will be plsql based ntf.
8907:
8908: if (content_type = wf_notification.doc_html ) then
8909: if (length(trim(lv_html_body)) > 0 ) then
8910:
8911: if (fwkTokenExist(nid, lv_html_body) = 'Y'
8912: or instr(lv_html_body, '&') = 0 ) then
8946:
8947: return lv_fwk_body;
8948: exception
8949: when OTHERS then
8950: wf_core.context('Wf_Notification','isFwkBody',to_char(nid), content_type);
8951: raise;
8952:
8953: End isFwkBody;
8954:
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
8997:
8998: return lv_token_exist;
8999: exception
9000: when OTHERS then
9001: wf_core.context('Wf_Notification','fwkTokenExist',to_char(nid));
9002: raise;
9003:
9004: end fwkTokenExist;
9005:
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;
9058: end if;
9059:
9060: exception
9061: when OTHERS then
9062: wf_core.context('Wf_Notification','getNtfActInfo',to_char(nid));
9063: raise;
9064:
9065: End getNtfActInfo;
9066:
9123: if url_value is null then
9124: url_value := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/');
9125: end if;
9126:
9127: url_value := url_value || wf_notification.fwk_mailer_page;
9128: url_value := url_value || '&WFRegion=NtfDetail&NtfId=' || to_char(p_nid);
9129: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9130:
9131: if (p_contentType = wf_notification.doc_html) then
9127: url_value := url_value || wf_notification.fwk_mailer_page;
9128: url_value := url_value || '&WFRegion=NtfDetail&NtfId=' || to_char(p_nid);
9129: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9130:
9131: if (p_contentType = wf_notification.doc_html) then
9132: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9133: url_value := url_value || '&OARF=email';
9134: elsif (p_contentType = wf_notification.doc_text) then
9135: url_value := url_value || '&OALAF=oaText&OARF=email';
9130:
9131: if (p_contentType = wf_notification.doc_html) then
9132: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9133: url_value := url_value || '&OARF=email';
9134: elsif (p_contentType = wf_notification.doc_text) then
9135: url_value := url_value || '&OALAF=oaText&OARF=email';
9136: end if;
9137:
9138: -- Bug 5170348
9144: url_value := url_value || '&language_code='|| lang_code;
9145:
9146: exception
9147: when others then
9148: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.getFwkBodyURLLang',
9149: 'nid: '||to_char(p_nid)||'; language: '|| p_language);
9150: end;
9151: end if;
9152:
9164: return url_value;
9165:
9166: exception
9167: when OTHERS then
9168: wf_core.context('Wf_Notification','getFwkBodyURL2',
9169: to_char(p_nid), p_contentType,p_language,p_nlsCalendar );
9170: raise;
9171:
9172: END getFwkBodyURL2;
9244: if url_value is null then
9245: url_value := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/');
9246: end if;
9247:
9248: url_value := url_value || wf_notification.fwk_mailer_page;
9249: url_value := url_value ||'&WFRegion=NtfSummary&mailerRole=' || p_mailer_role;
9250: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9251:
9252: if (p_contentType = wf_notification.doc_html) then
9248: url_value := url_value || wf_notification.fwk_mailer_page;
9249: url_value := url_value ||'&WFRegion=NtfSummary&mailerRole=' || p_mailer_role;
9250: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9251:
9252: if (p_contentType = wf_notification.doc_html) then
9253: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9254: url_value := url_value || '&OARF=email';
9255: elsif (p_contentType = wf_notification.doc_text) then
9256: url_value := url_value || '&OALAF=oaText&OARF=email';
9251:
9252: if (p_contentType = wf_notification.doc_html) then
9253: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9254: url_value := url_value || '&OARF=email';
9255: elsif (p_contentType = wf_notification.doc_text) then
9256: url_value := url_value || '&OALAF=oaText&OARF=email';
9257: end if;
9258:
9259: l_nls_calendar := nvl(p_nlsCalendar, wf_core.nls_calendar);
9270: return url_value;
9271:
9272: exception
9273: when OTHERS then
9274: wf_core.context('Wf_Notification','getSummaryURL2', p_mailer_role, p_contentType, p_nlsCalendar);
9275: raise;
9276:
9277: END getSummaryURL2;
9278:
9318: --when any exception raise the error with the corresponding notification id
9319:
9320: exception
9321: when others then
9322: wf_core.context('WF_Notification', 'GetSignatureRequired', to_char(p_nid));
9323: wf_core.token('NID', to_char(p_nid));
9324: wf_core.raise('WFMLR_INVALID_SIG_POLICY');
9325: end;
9326:
9445: );
9446:
9447: exception
9448: when others then
9449: wf_core.context('Wf_Notification', 'SetComments', to_char(p_nid), p_from_role,
9450: p_to_role, p_action, p_action_source);
9451: raise;
9452: end SetComments;
9453:
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);
9541: wf_event.addParameterToList('PK_VALUE_1', p_nid, l_paramlist);
9542: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_paramlist);
9543: wf_event.addParameterToList('PK_VALUE_2', l_language, l_paramlist);
9544:
9545: Wf_Event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
9546: p_event_key => to_char(p_nid),
9547: p_parameters => l_paramlist);
9548:
9549: exception
9547: p_parameters => l_paramlist);
9548:
9549: exception
9550: when others then
9551: wf_core.context('Wf_Notification', 'Resend', to_char(p_nid));
9552: raise;
9553: end Resend;
9554:
9555: --
9607: when NO_DATA_FOUND then
9608: p_result_code := null;
9609: p_result_display := null;
9610: when others then
9611: wf_core.context('Wf_Notification', 'getNtfResponse', to_char(p_nid));
9612: raise;
9613: end getNtfResponse;
9614:
9615: --
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
9638: and wfas.notification_id = wfn.group_id;
9639: begin
9640: for comment_curs in curs_nid(p_document_id,p_item_type,p_item_key) loop
9641: --Now loop through the cursor and set the comments
9642: wf_notification.SetComments(p_nid => comment_curs.notification_id,
9643: p_from_role => p_from_role,
9644: p_to_role => p_to_role,
9645: p_action => p_action,
9646: p_action_source => p_action_source,
9647: p_user_comment => p_user_comment);
9648: end loop;
9649: exception
9650: when others then
9651: wf_core.context('Wf_Notification', 'propagatehistory', p_item_type,p_item_key, p_document_id);
9652: raise;
9653: end;
9654:
9655: --
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'
9746: fetch c_failed_ntfs into l_nid;
9747: exit when c_failed_ntfs%NOTFOUND;
9748: begin
9749: -- Raise event
9750: Wf_Notification.Resend(l_nid);
9751:
9752: exception
9753: when others then
9754: -- ignore any error while enqueing
9767: fetch c_error_fyi_ntfs into l_nid;
9768: exit when c_error_fyi_ntfs%NOTFOUND;
9769: begin
9770: -- Raise event
9771: Wf_Notification.Resend(l_nid);
9772: exception
9773: when others then
9774: -- ignore any error while enqueing
9775: Wf_Core.Clear();
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'
9865:
9866: cursor notification_attrs_cursor(nid number) is
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
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
9907: end if;
9908:
9909: -- kma bug2376058 digital signature support
9910: begin
9911: proxyuser := Wf_Notification.GetAttrText(nid, '#WF_PROXIED_VIA');
9912: exception
9913: when others then
9914: if (wf_core.error_name = 'WFNTF_ATTR') then
9915: -- Pass null result if no result attribute.
9926: wf_core.raise('WFNTF_DIGSIG_USER_MISMATCH');
9927: end if;
9928:
9929: -- bug 2698999 Checking if ntf's signature requirements are met
9930: if (NOT Wf_Notification.NtfSignRequirementsMet(nid)) then
9931: wf_core.token('NID', to_char(nid));
9932: wf_core.raise('WFNTF_NOT_SIGNED');
9933: end if;
9934:
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
9993: -- Set the approrpiate responder to context user
9994: g_context_user := l_responder;
9995:
9996: if respond2.respond_comment is null then
9997: g_context_user_comment := Wf_Notification.GetAttrText(nid,'WF_NOTE',TRUE);
9998: else
9999: g_context_user_comment := respond2.respond_comment;
10000: end if;
10001: g_context_recipient_role := l_recip_role;
10007: -- Call the callback in VALIDATE mode to execute the post notification
10008: -- function to perform some custom validation and reject the response by
10009: -- raising exception. If validation is already done in RESPOND mode, it
10010: -- can stay there... VALIDATE mode can be called back from outside of
10011: -- notification code also before calling the Wf_Notification.Respond API
10012: if (callback is not null) then
10013: tvalue := respond2.responder;
10014: nvalue := nid;
10015: -- ### Review Note 2 - callback is from table
10013: tvalue := respond2.responder;
10014: nvalue := nid;
10015: -- ### Review Note 2 - callback is from table
10016: -- Check for bug#3827935
10017: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
10018: --Throw the Illegal exception when the check fails
10019:
10020:
10021: -- BINDVAR_SCAN_IGNORE
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
10074: -- USER_COMMENT = respond2.newcomment
10075: where NOTIFICATION_ID = respond2.nid;
10076:
10077: -- responder should be the From role that appears in the action history
10078: Wf_Notification.SetComments(nid, l_responder, 'WF_SYSTEM', 'RESPOND',
10079: action_source, respond_comment);
10080:
10081: --Bug 2283697
10082: --To raise an EVENT whenever DML operation is performed on
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
10094: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
10095: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
10096:
10097: --Raise the event
10098: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.respond',
10099: p_event_key => to_char(nid),
10100: p_parameters => l_parameterlist);
10101:
10102: -- If no callback, there is nothing else to do.
10107: --
10108: -- Open dynamic sql cursor for SET callback calls.
10109: --
10110: -- ### Review Note 2 - callback is from table
10111: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
10112: --Throw the Illegal exception when the check fails
10113:
10114:
10115: -- BINDVAR_SCAN_IGNORE
10139: end loop;
10140:
10141: exception
10142: when others then
10143: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
10144: respond_comment, responder);
10145: -- This call is for enhanced error handling with respect to OAFwk
10146: wf_notification.SetUIErrorMessage;
10147: raise;
10142: when others then
10143: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
10144: respond_comment, responder);
10145: -- This call is for enhanced error handling with respect to OAFwk
10146: wf_notification.SetUIErrorMessage;
10147: raise;
10148: end Respond2;
10149:
10150:
10151: --
10152: -- process_response
10153: -- ER 10177347: Determines that the notification response has to be
10154: -- processed in synschronous mode or defer mode, calls the respond()
10155: -- or respond2() API and enqueues the event into WF_NOTIFICATION_IN queue
10156: -- accordingly based on the value of the parameter 'defer_response'
10157: -- IN
10158: -- nid Notification ID
10159: -- respond_comment Respond Comment
10166: action_source in varchar2 default null,
10167: defer_response in varchar2 default null)
10168: is
10169:
10170: l_eventname varchar2(60) := 'oracle.apps.wf.notification.wl.response.message';
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:
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:
10183:
10184: begin
10185:
10186: -- Get the item type of the notification
10187: WF_NOTIFICATION.GetInfo(nid,
10188: l_dummy_var,
10189: l_itemType,
10190: l_dummy_var,
10191: l_dummy_var,
10207: if(defer_response = 'ALL' or
10208: (defer_response = 'SPECIFIC' and l_lookupCode_cnt > 0)) then
10209:
10210: -- all response processing except callback in COMPLETE mode
10211: WF_NOTIFICATION.Respond2(nid, respond_comment, responder, action_source, response_found);
10212:
10213: -- prepare event payload
10214: l_agent := wf_agent_t(null, null);
10215: l_agent.name := 'WF_NOTIFICATION_IN';
10211: WF_NOTIFICATION.Respond2(nid, respond_comment, responder, action_source, response_found);
10212:
10213: -- prepare event payload
10214: l_agent := wf_agent_t(null, null);
10215: l_agent.name := 'WF_NOTIFICATION_IN';
10216: l_agent.SYSTEM := wf_event.local_system_name;
10217:
10218: wf_event_t.initialize(l_event);
10219: l_event.event_name := l_eventname;
10236: wf_event_ojmstext_qh.enqueue( l_event, l_agent);
10237:
10238: else
10239: -- default synchronous processing
10240: wf_notification.respond(nid, respond_comment, responder, action_source);
10241:
10242: end if;
10243:
10244:
10243:
10244:
10245: exception
10246: when others then
10247: wf_core.context('Wf_Notification', 'process_response', to_char(nid),
10248: respond_comment, responder);
10249: wf_core.clear;
10250: -- This call is for enhanced error handling with respect to OAFwk
10251: wf_notification.SetUIErrorMessage;
10247: wf_core.context('Wf_Notification', 'process_response', to_char(nid),
10248: respond_comment, responder);
10249: wf_core.clear;
10250: -- This call is for enhanced error handling with respect to OAFwk
10251: wf_notification.SetUIErrorMessage;
10252: raise;
10253:
10254: end process_response;
10255:
10279: -- Call callback in COMPLETE mode to mark activity COMPLETE.
10280: -- Send the result and notification id as the value.
10281: --
10282: begin
10283: tvalue := Wf_Notification.GetAttrText(p_nid, 'RESULT');
10284:
10285: exception
10286: when others then
10287: if (wf_core.error_name = 'WFNTF_ATTR') then
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
10307: wf_core.token('NID', to_char(p_nid));
10308: wf_core.raise('WFNTF_NID');
10309: end;
10310:
10311: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
10312:
10313: sqlbuf := 'begin '||callback||
10314: '(:p1, :p2, :p3, :p4, :p5, :p6, :p7); end;';
10315: execute immediate sqlbuf using
10325: exception
10326: --This exception alone raise to caller
10327: when resource_busy then
10328: --Raise this exception to caller
10329: wf_core.context('WF_NOTIFICATION', 'Complete', to_char(p_nid));
10330: raise;
10331:
10332: end Complete;
10333:
10342: end if;
10343:
10344:
10345:
10346: End WF_Notification;
10347: