[Home] [Help]
1: package body WF_NOTIFICATION as
2: /* $Header: wfntfb.pls 120.43.12010000.27 2009/02/26 01:39:33 alepe 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;
423: end if;
424: end if;
425:
426: -- ### implement as generic log in the future
427: -- if (wf_notification.debug) then
428: -- dbms_output.put_line(substrb(l_attr||'/'||l_dispname||': '||l_text,1,250));
429: -- end if;
430:
431: p1 := p2+1;
430:
431: p1 := p2+1;
432: end loop;
433:
434: if (disptype = wf_notification.doc_html) then
435: if (table_type = 'N') then
436: table_width := '100%';
437: else
438: table_width := '70%';
446: return(result);
447:
448: exception
449: when OTHERS then
450: wf_core.context('Wf_Notification','Wf_Msg_Attr',to_char(nid),attrs);
451: raise;
452: end wf_msg_attr;
453:
454:
455: -- Wf_Ntf_History
456: -- Construct Action History table for a given notification from the WF_COMMENTS table
457: -- The table consists of actions like Reassign, More Info Request and Respond and related
458: -- comments. The user can restrict the rows in the table using the following format.
459: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
460: -- Example:
461: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
462: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
463: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
457: -- The table consists of actions like Reassign, More Info Request and Respond and related
458: -- comments. The user can restrict the rows in the table using the following format.
459: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
460: -- Example:
461: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
462: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
463: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
464: --
465: -- InPut
458: -- comments. The user can restrict the rows in the table using the following format.
459: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
460: -- Example:
461: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
462: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
463: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
464: --
465: -- InPut
466: -- nid - Notification Id
459: -- WF_NOTIFICATION(HISTORY, hide_reassign, hide_requestinfo)
460: -- Example:
461: -- WF_NOTIFICATION(HISTORY, Y, Y) - Hides comments related to Reassign and More Info Reqs
462: -- WF_NOTIFICATION(HISTORY, N, Y) - Hides comments related to More Info Reqs
463: -- WF_NOTIFICATION(HISTORY) - Shows all comments related to the notification
464: --
465: -- InPut
466: -- nid - Notification Id
467: -- disptype - text/plain or text/html
493: l_hide_reassign := 'N';
494: l_hide_requestinfo := 'N';
495: end;
496:
497: Wf_Notification.GetComments2(p_nid => nid, p_display_type => disptype,
498: p_hide_reassign => l_hide_reassign,
499: p_hide_requestinfo => l_hide_requestinfo,
500: p_action_history => l_action_history);
501: return l_action_history;
601:
602: j := 1;
603: -- title
604: cells(j) := wf_core.translate('NUM');
605: if (disptype = wf_notification.doc_html) then
606: cells(j) := 'S10%:'||cells(j);
607: end if;
608: j := j+1;
609: cells(j) := wf_core.translate('NAME');
606: cells(j) := 'S10%:'||cells(j);
607: end if;
608: j := j+1;
609: cells(j) := wf_core.translate('NAME');
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');
611: cells(j) := 'S:'||cells(j);
612: end if;
613: j := j+1;
614: cells(j) := wf_core.translate('ACTION');
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('ACTION_DATE');
616: cells(j) := 'S:'||cells(j);
617: end if;
618: j := j+1;
619: cells(j) := wf_core.translate('ACTION_DATE');
620: if (disptype = wf_notification.doc_html) then
621: cells(j) := 'S:'||cells(j);
622: end if;
623: j := j+1;
624: cells(j) := wf_core.translate('NOTE');
621: cells(j) := 'S:'||cells(j);
622: end if;
623: j := j+1;
624: cells(j) := wf_core.translate('NOTE');
625: if (disptype = wf_notification.doc_html) then
626: cells(j) := 'S:'||cells(j);
627: end if;
628: j := j+1;
629:
631: for histr in hist0c(l_itype, l_ikey, l_actid) loop
632: cells(j) := to_char(histr.notification_id);
633: j := j+1;
634: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
635: if (disptype = wf_notification.doc_html) then
636: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
637: else
638: cells(j) := role_info_tbl(1).display_name;
639: end if;
632: cells(j) := to_char(histr.notification_id);
633: j := j+1;
634: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
635: if (disptype = wf_notification.doc_html) then
636: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
637: else
638: cells(j) := role_info_tbl(1).display_name;
639: end if;
640: j := j+1;
644: l_result_type := histr.result_type;
645: l_result_code := histr.activity_result_code;
646: l_action := wf_core.activity_result(l_result_type, l_result_code);
647: end if;
648: if (disptype = wf_notification.doc_html) then
649: if (l_action is null) then
650: cells(j) := 'S: ';
651: else
652: cells(j) := 'S:'||l_action;
654: else
655: cells(j) := l_action;
656: end if;
657: j := j+1;
658: if (disptype = wf_notification.doc_html) then
659: cells(j) := 'S:'||to_char(histr.act_date);
660: else
661: cells(j) := to_char(histr.act_date);
662: end if;
661: cells(j) := to_char(histr.act_date);
662: end if;
663: j := j+1;
664: begin
665: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
666: if (disptype = wf_notification.doc_html) then
667: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
668: end if;
669: cells(j) := l_note;
662: end if;
663: j := j+1;
664: begin
665: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
666: if (disptype = wf_notification.doc_html) then
667: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
668: end if;
669: cells(j) := l_note;
670: exception
663: j := j+1;
664: begin
665: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
666: if (disptype = wf_notification.doc_html) then
667: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
668: end if;
669: cells(j) := l_note;
670: exception
671: when OTHERS then
671: when OTHERS then
672: cells(j) := null;
673: wf_core.clear;
674: end;
675: if (disptype = wf_notification.doc_html) then
676: if (cells(j) is null) then
677: cells(j) := 'S: ';
678: else
679: cells(j) := 'S:'||cells(j);
687: for histr in histc(l_itype, l_ikey, l_actid) loop
688: cells(j) := to_char(histr.notification_id);
689: j := j+1;
690: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
691: if (disptype = wf_notification.doc_html) then
692: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
693: else
694: cells(j) := role_info_tbl(1).display_name;
695: end if;
688: cells(j) := to_char(histr.notification_id);
689: j := j+1;
690: wf_directory.GetRoleInfo2(histr.assigned_user, role_info_tbl);
691: if (disptype = wf_notification.doc_html) then
692: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
693: else
694: cells(j) := role_info_tbl(1).display_name;
695: end if;
696: j := j+1;
700: l_result_type := histr.result_type;
701: l_result_code := histr.activity_result_code;
702: l_action := wf_core.activity_result(l_result_type, l_result_code);
703: end if;
704: if (disptype = wf_notification.doc_html) then
705: if (l_action is null) then
706: cells(j) := 'S: ';
707: else
708: cells(j) := 'S:'||l_action;
710: else
711: cells(j) := l_action;
712: end if;
713: j := j+1;
714: if (disptype = wf_notification.doc_html) then
715: cells(j) := 'S:'||to_char(histr.act_date);
716: else
717: cells(j) := to_char(histr.act_date);
718: end if;
717: cells(j) := to_char(histr.act_date);
718: end if;
719: j := j+1;
720: begin
721: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
722: if (disptype = wf_notification.doc_html) then
723: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
724: end if;
725: cells(j) := l_note;
718: end if;
719: j := j+1;
720: begin
721: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
722: if (disptype = wf_notification.doc_html) then
723: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
724: end if;
725: cells(j) := l_note;
726: exception
719: j := j+1;
720: begin
721: l_note := Wf_Notification.GetAttrText(histr.notification_id,'WF_NOTE',TRUE);
722: if (disptype = wf_notification.doc_html) then
723: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
724: end if;
725: cells(j) := l_note;
726: exception
727: when OTHERS then
727: when OTHERS then
728: cells(j) := null;
729: wf_core.clear;
730: end;
731: if (disptype = wf_notification.doc_html) then
732: if (cells(j) is null) then
733: cells(j) := 'S: ';
734: else
735: cells(j) := 'S:'||cells(j);
753: when OTHERS then
754: raise;
755: end;
756: wf_directory.GetRoleInfo2(l_owner_role, role_info_tbl);
757: if (disptype = wf_notification.doc_html) then
758: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
759: else
760: cells(j) := role_info_tbl(1).display_name;
761: end if;
754: raise;
755: end;
756: wf_directory.GetRoleInfo2(l_owner_role, role_info_tbl);
757: if (disptype = wf_notification.doc_html) then
758: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(role_info_tbl(1).display_name);
759: else
760: cells(j) := role_info_tbl(1).display_name;
761: end if;
762: j := j+1;
759: else
760: cells(j) := role_info_tbl(1).display_name;
761: end if;
762: j := j+1;
763: if (disptype = wf_notification.doc_html) then
764: cells(j) := 'S:'||wf_core.translate('SUBMIT');
765: else
766: cells(j) := wf_core.translate('SUBMIT');
767: end if;
765: else
766: cells(j) := wf_core.translate('SUBMIT');
767: end if;
768: j := j+1;
769: if (disptype = wf_notification.doc_html) then
770: cells(j) := 'S:'||to_char(l_begin_date);
771: else
772: cells(j) := to_char(l_begin_date);
773: end if;
771: else
772: cells(j) := to_char(l_begin_date);
773: end if;
774: j := j+1;
775: if (disptype = wf_notification.doc_html) then
776: cells(j) := 'S: ';
777: else
778: cells(j) := null;
779: end if;
778: cells(j) := null;
779: end if;
780:
781: -- ### implement as generic log in the future
782: -- if (wf_notification.debug) then
783: -- dbms_output.put_line('j = '||to_char(j));
784: -- dbms_output.put_line(substrb('last cell = '||cells(j),1,254));
785: -- end if;
786:
787: -- calculate the sequence
788: -- Only after we know the number of rows, then we can put the squence
789: -- number on for each row.
790: for k in 0..i loop
791: if (disptype = wf_notification.doc_html) then
792: cells((k+1)*5+1) := 'C:'||to_char(i-k);
793: else
794: cells((k+1)*5+1) := to_char(i-k);
795: end if;
794: cells((k+1)*5+1) := to_char(i-k);
795: end if;
796: end loop;
797:
798: if (disptype = wf_notification.doc_html) then
799: table_width := '100%';
800: NTF_Table(cells=>cells,
801: col=>5,
802: type=>'H'||l_table_direction,
813:
814: return(result);
815: exception
816: when OTHERS then
817: wf_core.context('Wf_Notification', 'Wf_NTF_History', to_char(nid));
818: raise;
819: end wf_ntf_history;
820: **
821: ** End of obsoleted procedure WF_NTF_HISTORY
824: --
825: -- runFuncOnBody
826: -- NOTE
827: -- Attempt to find, parse and replace the string
828: -- WF_NOTIFICATION(F,P1,P2,...)
829: -- F = function to run
830: -- P1,P2,... = comma delimited parameter list
831: --
832: function runFuncOnBody(nid in number,
851:
852: p1:=1;
853: alldone:=false;
854: while (not alldone) loop
855: fname := 'WF_NOTIFICATION('; -- lengthb(fname) is 16
856:
857: p1 := instrb(l_body, fname, p1);
858: if (p1 <> 0) then
859: p2 := instrb(l_body, ')', p1);
870:
871: frun := substrb(func, 17, pp-17);
872:
873: -- ### implement as generic log in the future
874: -- if (wf_notification.debug) then
875: -- dbms_output.put_line('frun='||frun);
876: -- end if;
877:
878: if (frun = 'ATTRS') then
884: end if;
885:
886: -- do not replace a string with itself.
887: -- if rs is null, then there is nothing to display for Action/Notifications History
888: -- or Attributes table. We would not want WF_NOTIFICATION(ATTRS,...) or
889: -- WF_NOTIFICATION(HISTORY) to appear in the notification as is.
890: if (rs is null or rs <> func) then
891: l_body := replace(l_body, func, rs);
892: end if;
885:
886: -- do not replace a string with itself.
887: -- if rs is null, then there is nothing to display for Action/Notifications History
888: -- or Attributes table. We would not want WF_NOTIFICATION(ATTRS,...) or
889: -- WF_NOTIFICATION(HISTORY) to appear in the notification as is.
890: if (rs is null or rs <> func) then
891: l_body := replace(l_body, func, rs);
892: end if;
893:
905: return(l_body);
906:
907: exception
908: when OTHERS then
909: wf_core.context('Wf_Notification', 'runFuncOnBody', to_char(nid), disptype);
910: raise;
911: end runFuncOnBody;
912:
913: -- More Info mailer support
988: -- itemtype: string before the first colon in the context
989: -- itemkey: string between first and second colons in the contex
990: -- actid: NUMBER after the second colon
991: --
992: procedure validate_context (context IN WF_NOTIFICATIONS.CONTEXT%TYPE,
993: itemtype OUT NOCOPY varchar2,
994: itemkey OUT NOCOPY varchar2,
995: actid OUT NOCOPY number)
996: is
1020: when OTHERS then --no_data_found or invalid_number
1021: itemtype:=null;
1022: itemkey:=null;
1023: actid:=null;
1024: wf_core.context('Wf_Notification', 'validate_context', context);
1025: raise;
1026: end validate_context;
1027:
1028:
1052: -- Insure this is a valid notification.
1053: begin
1054: select 1 into dummy from sys.dual where exists
1055: (select null
1056: from WF_NOTIFICATIONS
1057: where NOTIFICATION_ID = nid);
1058: exception
1059: when no_data_found then
1060: wf_core.token('NID', to_char(nid));
1062: end;
1063:
1064: -- Insert new attribute
1065: begin
1066: insert into WF_NOTIFICATION_ATTRIBUTES (
1067: NOTIFICATION_ID,
1068: NAME,
1069: TEXT_VALUE,
1070: NUMBER_VALUE,
1084: end;
1085:
1086: exception
1087: when others then
1088: wf_core.context('Wf_Notification', 'AddAttr', to_char(nid), aname);
1089: raise;
1090: end AddAttr;
1091:
1092: --
1123: -- This is used for translating number/date strings.
1124: begin
1125: select WMA.TYPE, WMA.FORMAT
1126: into atype, format
1127: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1128: WF_MESSAGE_ATTRIBUTES WMA
1129: where WNA.NOTIFICATION_ID = nid
1130: and WNA.NAME = aname
1131: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
1141: end;
1142:
1143: -- Update attribute value in appropriate type column.
1144: if (atype = 'NUMBER') then
1145: update WF_NOTIFICATION_ATTRIBUTES
1146: set NUMBER_VALUE = decode(format,
1147: '', to_number(avalue),
1148: to_number(avalue, format))
1149: where NOTIFICATION_ID = nid
1151: elsif (atype = 'DATE') then
1152: -- 4477386 gscc date format requirement change
1153: -- do not use a cached value, this allows nls change within the
1154: -- same session to be seen right away.
1155: update WF_NOTIFICATION_ATTRIBUTES
1156: set DATE_VALUE = decode(format,
1157: '',to_date(avalue,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')),
1158: to_date(avalue, format))
1159: where NOTIFICATION_ID = nid
1161: elsif (atype = 'VARCHAR2') then
1162: -- VARCHAR2
1163: -- Set the text value directly with no translation.
1164: -- bug 1996299 - JWSMITH , changes substr to substrb for korean char
1165: update WF_NOTIFICATION_ATTRIBUTES
1166: set TEXT_VALUE = decode(format,
1167: '', avalue,
1168: substrb(avalue, 1, to_number(format)))
1169: where NOTIFICATION_ID = nid
1196: end if;
1197: end if;
1198:
1199: -- Set the text value with internal role name
1200: update WF_NOTIFICATION_ATTRIBUTES
1201: set TEXT_VALUE = rname
1202: where NOTIFICATION_ID = nid
1203: and NAME = aname;
1204: else
1203: and NAME = aname;
1204: else
1205: -- LOOKUP, FORM, URL, DOCUMENT, misc type.
1206: -- Set the text value.
1207: update WF_NOTIFICATION_ATTRIBUTES
1208: set TEXT_VALUE = avalue
1209: where NOTIFICATION_ID = nid
1210: and NAME = aname;
1211: end if;
1217: end if;
1218:
1219: -- Redenormalize if attribute being updated is #FROM_ROLE
1220: if (aname = '#FROM_ROLE') then
1221: Wf_Notification.Denormalize_Notification(nid);
1222: end if;
1223:
1224: -- Bug 2437347 raising event after DML operation on WF_NOTIFICATION_ATTRIBUTES
1225: if (aname = 'SENDER') then
1220: if (aname = '#FROM_ROLE') then
1221: Wf_Notification.Denormalize_Notification(nid);
1222: end if;
1223:
1224: -- Bug 2437347 raising event after DML operation on WF_NOTIFICATION_ATTRIBUTES
1225: if (aname = 'SENDER') then
1226: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
1227: wf_event.AddParameterToList(aname, avalue, l_parameterlist);
1228:
1230:
1231:
1232: select WN.RECIPIENT_ROLE
1233: into l_recipient_role
1234: from WF_NOTIFICATIONS WN
1235: where WN.NOTIFICATION_ID = nid ;
1236:
1237: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
1238: l_language := role_info_tbl(1).language;
1248: wf_event.addParameterToList('PK_VALUE_1', nid, l_parameterlist);
1249: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
1250: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
1251: -- Raise the event
1252: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.setattrtext',
1253: p_event_key => to_char(nid),
1254: p_parameters => l_parameterlist);
1255: end if;
1256:
1255: end if;
1256:
1257: exception
1258: when others then
1259: wf_core.context('Wf_Notification', 'SetAttrText', to_char(nid),
1260: aname, avalue);
1261: raise;
1262: end SetAttrText;
1263:
1281: wf_core.raise('WFSQL_ARGS');
1282: end if;
1283:
1284: -- Update attribute value
1285: update WF_NOTIFICATION_ATTRIBUTES
1286: set NUMBER_VALUE = avalue
1287: where NOTIFICATION_ID = nid and NAME = aname;
1288:
1289: if (SQL%NOTFOUND) then
1293: end if;
1294:
1295: exception
1296: when others then
1297: wf_core.context('Wf_Notification', 'SetAttrNumber', to_char(nid),
1298: aname, to_char(avalue));
1299: raise;
1300: end SetAttrNumber;
1301:
1319: wf_core.raise('WFSQL_ARGS');
1320: end if;
1321:
1322: -- Update attribute value
1323: update WF_NOTIFICATION_ATTRIBUTES
1324: set DATE_VALUE = avalue
1325: where NOTIFICATION_ID = nid and NAME = aname;
1326:
1327: if (SQL%NOTFOUND) then
1331: end if;
1332:
1333: exception
1334: when others then
1335: wf_core.context('Wf_Notification', 'SetAttrDate', to_char(nid),
1336: aname, to_char(avalue));
1337: raise;
1338: end SetAttrDate;
1339:
1430: buf := replace(buf, '"', l_amp||'quot;');
1431: return buf;
1432: exception
1433: when others then
1434: wf_core.context('Wf_Notification', 'SubstituteSpecialChars');
1435: raise;
1436: end SubstituteSpecialChars;
1437:
1438: --
1477: -- of another.
1478: cursor notification_attrs_cursor(nid number) is
1479: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1480: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1481: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1482: WF_MESSAGE_ATTRIBUTES_VL WMA
1483: where WNA.NOTIFICATION_ID = nid
1484: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1485: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1529:
1530: -- Bug 2843136
1531: -- Replace '&' but also '&' only if it hasn't been already substituted
1532: -- This is to prevent something like '&' from happening
1533: if (disptype = wf_notification.doc_html) then
1534: -- (instr(local_text,'&'||not_attr_row.name) = 0) AND
1535: -- (instr(value,'&') = 0)) then
1536:
1537: -- bug 6025162 - SubstituteSpecialChars function substitutes only those
1563: params := instr(value, ':');
1564: if (params <> 0) then
1565: value := not_attr_row.display_name||' ( '||
1566: substr(value, 1, params)||
1567: wf_notification.GetTextInternal(substr(value, params+1), nid,
1568: target, FALSE, FALSE, 'text/plain')||' )';
1569: end if;
1570: end if;
1571: elsif ((not_attr_row.type = 'URL') and (not urlmode) ) then
1578: params := instr(value, '?');
1579: if (params <> 0) then
1580: value := not_attr_row.display_name||' ( '||
1581: substr(value, 1, params)||
1582: wf_notification.GetTextInternal(substr(value, params+1), nid,
1583: target, TRUE, FALSE, 'text/plain')||' )';
1584: end if;
1585: end if;
1586: elsif (not_attr_row.type = 'ROLE') then
1681: end if;
1682: return(local_text);
1683: exception
1684: when others then
1685: wf_core.context('Wf_Notification','GetTextInternal', to_char(nid), disptype);
1686: raise;
1687: end GetTextInternal;
1688:
1689:
1744: return value;
1745:
1746: exception
1747: when others then
1748: wf_core.context('Wf_Notification', 'SetFrameworkAgent', url);
1749: end;
1750:
1751:
1752:
1759: -- IN:
1760: -- some_text - Text to be substituted
1761: -- nid - Notification id of notification to use for token values
1762: -- disptype - Requested display type. Valid values:
1763: -- wf_notification.doc_text - 'text/plain'
1764: -- wf_notification.doc_html - 'text/html'
1765: -- RETURNS:
1766: -- Some_text with tokens substituted.
1767: -- NOTE:
1760: -- some_text - Text to be substituted
1761: -- nid - Notification id of notification to use for token values
1762: -- disptype - Requested display type. Valid values:
1763: -- wf_notification.doc_text - 'text/plain'
1764: -- wf_notification.doc_html - 'text/html'
1765: -- RETURNS:
1766: -- Some_text with tokens substituted.
1767: -- NOTE:
1768: -- If errors are detected this routine returns some_text untouched
1791: -- of another.
1792: cursor notification_attrs_cursor(nid number) is
1793: select WNA.NAME, WMA.TYPE, WMA.FORMAT, WMA.DISPLAY_NAME,
1794: WNA.TEXT_VALUE, WNA.NUMBER_VALUE, WNA.DATE_VALUE
1795: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
1796: WF_MESSAGE_ATTRIBUTES_VL WMA
1797: where WNA.NOTIFICATION_ID = nid
1798: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
1799: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
1844: to_number(not_attr_row.format));
1845: end if;
1846:
1847: -- JWSMITH bug 1725916 - add BR to attribute value
1848: if (disptype=wf_notification.doc_html) then
1849: --Check if we already have and '&' in which
1850: --case don't resubstitute it.
1851: -- if ((instr(local_text,'&'||not_attr_row.name) = 0)
1852: -- AND (instr(value,'&') = 0)) then
1870: value := to_char(not_attr_row.number_value, not_attr_row.format);
1871: end if;
1872: elsif (not_attr_row.type = 'DATE') then
1873: --
1874: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1875: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1876: -- and wf_core.nls_date_format.
1877: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1878: , not_attr_row.format, false);
1871: end if;
1872: elsif (not_attr_row.type = 'DATE') then
1873: --
1874: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1875: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1876: -- and wf_core.nls_date_format.
1877: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1878: , not_attr_row.format, false);
1879:
1873: --
1874: -- not_attr_row.format, wf_notification_util.G_NLS_DATE_FORMAT (if nid is provided
1875: -- and matches wf_notification_util.G_NID), session user's WFDS preference,
1876: -- and wf_core.nls_date_format.
1877: value := wf_notification_util.GetCalendarDate(nid, not_attr_row.date_value
1878: , not_attr_row.format, false);
1879:
1880: elsif (not_attr_row.type = 'FORM') then
1881: -- FORM is display_name (function), with parameters of function
1884: params := instr(value, ':');
1885: if (params <> 0) then
1886: value := not_attr_row.display_name||' ( '||
1887: substr(value, 1, params)||
1888: wf_notification.GetTextInternal(substr(value,params+1), nid,
1889: target, FALSE, FALSE, 'text/plain')||' )';
1890: end if;
1891:
1892: if (disptype = wf_notification.doc_html) then
1888: wf_notification.GetTextInternal(substr(value,params+1), nid,
1889: target, FALSE, FALSE, 'text/plain')||' )';
1890: end if;
1891:
1892: if (disptype = wf_notification.doc_html) then
1893: -- Bug 4634849
1894: -- Do not display potentially harmful text
1895: begin
1896: l_dummy := wf_core.CheckIllegalChars(value,true,';<>()');
1908: -- URL is display_name (url), with parameters of url
1909: -- recursively token-substituted if needed.
1910: value := not_attr_row.text_value;
1911: target := substr(nvl(not_attr_row.format, '_top'), 1, 16);
1912: value := wf_notification.SetFrameworkAgent(value);
1913: params := instr(value, '?');
1914: if (params <> 0) then
1915: value := substr(value, 1, params)||
1916: wf_notification.GetTextInternal(substr(value,params+1), nid,
1912: value := wf_notification.SetFrameworkAgent(value);
1913: params := instr(value, '?');
1914: if (params <> 0) then
1915: value := substr(value, 1, params)||
1916: wf_notification.GetTextInternal(substr(value,params+1), nid,
1917: target, TRUE, FALSE, 'text/plain');
1918: end if;
1919:
1920: if (disptype = wf_notification.doc_html) then
1916: wf_notification.GetTextInternal(substr(value,params+1), nid,
1917: target, TRUE, FALSE, 'text/plain');
1918: end if;
1919:
1920: if (disptype = wf_notification.doc_html) then
1921: -- Bug 4634849
1922: -- Do not display potentially harmful url
1923: begin
1924: if (not wf_core.CheckIllegalChars(value,true, ';<>"')) then
2001: email_address := nvl(role_info_tbl(1).email_address,
2002: not_attr_row.text_value);
2003: end if;
2004:
2005: if (disptype = wf_notification.doc_html) then
2006:
2007: value := ''||value||'';
2008:
2009: end if;
2011: else
2012: -- All others default to text_value
2013: value := not_attr_row.text_value;
2014:
2015: if (disptype = wf_notification.doc_html) then
2016: value := wf_core.substitutespecialchars(value);
2017: end if;
2018: end if;
2019:
2039: to_char(nid)), 1, 32000);
2040: return(local_text);
2041: exception
2042: when others then
2043: wf_core.context('Wf_Notification','GetText', to_char(nid), disptype);
2044: raise;
2045: -- return(some_text);
2046: end GetText;
2047:
2156:
2157: begin
2158: select WMA.TYPE, WMA.SUBTYPE, WMA.FORMAT
2159: into atype, subtype, format
2160: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2161: WF_MESSAGE_ATTRIBUTES WMA
2162: where WNA.NOTIFICATION_ID = nid
2163: and WNA.NAME = aname
2164: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2173: end;
2174:
2175: exception
2176: when others then
2177: wf_core.context('Wf_Notification', 'GetAttrInfo', to_char(nid),
2178: aname);
2179: raise;
2180: end GetAttrInfo;
2181:
2211: -- This is used for translating number/date strings.
2212: begin
2213: select WMA.TYPE, WMA.FORMAT
2214: into atype, format
2215: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2216: WF_MESSAGE_ATTRIBUTES WMA
2217: where WNA.NOTIFICATION_ID = nid
2218: and WNA.NAME = aname
2219: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2234: select decode(format,
2235: '', to_char(WNA.NUMBER_VALUE),
2236: to_char(WNA.NUMBER_VALUE, format))
2237: into lvalue
2238: from WF_NOTIFICATION_ATTRIBUTES WNA
2239: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2240: elsif (atype = 'DATE') then
2241: --
2242: select DATE_VALUE into l_valDate
2239: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2240: elsif (atype = 'DATE') then
2241: --
2242: select DATE_VALUE into l_valDate
2243: from WF_NOTIFICATION_ATTRIBUTES WNA
2244: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2245:
2246: lvalue := wf_notification_util.GetCalendarDate(nid, l_valDate, format, false);
2247:
2242: select DATE_VALUE into l_valDate
2243: from WF_NOTIFICATION_ATTRIBUTES WNA
2244: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2245:
2246: lvalue := wf_notification_util.GetCalendarDate(nid, l_valDate, format, false);
2247:
2248: else
2249: -- VARCHAR2, LOOKUP, FORM, or URL type.
2250: select WNA.TEXT_VALUE
2248: else
2249: -- VARCHAR2, LOOKUP, FORM, or URL type.
2250: select WNA.TEXT_VALUE
2251: into lvalue
2252: from WF_NOTIFICATION_ATTRIBUTES WNA
2253: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2254:
2255: -- Recursively substitute attributes in parameter portion of
2256: -- FORM and URL type attributes.
2260: -- FORM params are after ':'
2261: params := instr(lvalue, ':');
2262: if (params <> 0) then
2263: lvalue := substr(lvalue, 1, params)||
2264: wf_notification.GetShortText(substr(lvalue,
2265: params+1), nid);
2266: end if;
2267: elsif (atype = 'URL') then
2268: -- URL params are after '?'
2268: -- URL params are after '?'
2269: params := instr(lvalue, '?');
2270: if (params <> 0) then
2271: lvalue := substr(lvalue, 1, params)||
2272: wf_notification.GetUrlText(substr(lvalue,
2273: params+1), nid);
2274: end if;
2275: end if;
2276: end if;
2287:
2288: return(lvalue);
2289: exception
2290: when others then
2291: wf_core.context('Wf_Notification', 'GetAttrText', to_char(nid), aname);
2292: raise;
2293: end GetAttrText;
2294:
2295: --
2315:
2316: begin
2317: select WNA.NUMBER_VALUE
2318: into lvalue
2319: from WF_NOTIFICATION_ATTRIBUTES WNA
2320: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2321: exception
2322: when no_data_found then
2323: wf_core.token('NID', to_char(nid));
2327:
2328: return(lvalue);
2329: exception
2330: when others then
2331: wf_core.context('Wf_Notification', 'GetAttrNumber', to_char(nid), aname);
2332: raise;
2333: end GetAttrNumber;
2334:
2335: --
2355:
2356: begin
2357: select WNA.DATE_VALUE
2358: into lvalue
2359: from WF_NOTIFICATION_ATTRIBUTES WNA
2360: where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
2361: exception
2362: when no_data_found then
2363: wf_core.token('NID', to_char(nid));
2367:
2368: return(lvalue);
2369: exception
2370: when others then
2371: wf_core.context('Wf_Notification', 'GetAttrDate', to_char(nid), aname);
2372: raise;
2373: end GetAttrDate;
2374:
2375: --
2386: -- IN:
2387: -- nid - Notification id
2388: -- aname - Attribute Name
2389: -- disptype - Requested display type. Valid values:
2390: -- wf_notification.doc_text - 'text/plain'
2391: -- wf_notification.doc_html - 'text/html'
2392: -- RETURNS:
2393: -- Referenced document in format requested.
2394: --
2387: -- nid - Notification id
2388: -- aname - Attribute Name
2389: -- disptype - Requested display type. Valid values:
2390: -- wf_notification.doc_text - 'text/plain'
2391: -- wf_notification.doc_html - 'text/html'
2392: -- RETURNS:
2393: -- Referenced document in format requested.
2394: --
2395: function GetAttrDoc(
2402: doctype varchar2(255);
2403: begin
2404:
2405: -- call the procedure to get the Document Content and return to the caller.
2406: wf_notification.GetAttrDoc2(nid, aname, disptype, document, doctype);
2407: return (document);
2408:
2409: exception
2410: when others then
2407: return (document);
2408:
2409: exception
2410: when others then
2411: wf_core.context('Wf_Notification', 'GetAttrDoc', to_char(nid), aname,
2412: disptype);
2413: raise;
2414: end GetAttrDoc;
2415:
2439: begin
2440:
2441: -- Check args
2442: if ((nid is null) or (aname is null) or
2443: (disptype not in (wf_notification.doc_text,
2444: wf_notification.doc_html))) then
2445: wf_core.token('NID', to_char(nid));
2446: wf_core.token('ANAME', aname);
2447: wf_core.token('DISPTYPE', disptype);
2440:
2441: -- Check args
2442: if ((nid is null) or (aname is null) or
2443: (disptype not in (wf_notification.doc_text,
2444: wf_notification.doc_html))) then
2445: wf_core.token('NID', to_char(nid));
2446: wf_core.token('ANAME', aname);
2447: wf_core.token('DISPTYPE', disptype);
2448: wf_core.raise('WFSQL_ARGS');
2486: -- Substitute refs to other attributes in argument
2487: -- NOTE: There is a slight chance of recursive loop here,
2488: -- if the substituted string eventually contains a reference
2489: -- back to this same docattr.
2490: procarg := Wf_Notification.GetTextInternal(procarg, nid, target, FALSE,
2491: FALSE, 'text/plain');
2492: end if;
2493:
2494: -- ### Review Note 4
2492: end if;
2493:
2494: -- ### Review Note 4
2495:
2496: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
2497: --Throw the Illegal exception when the check fails
2498:
2499: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2500: wf_log_pkg.string2(wf_log_pkg.level_statement,
2497: --Throw the Illegal exception when the check fails
2498:
2499: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2500: wf_log_pkg.string2(wf_log_pkg.level_statement,
2501: 'wf.plsql.wf_notification.GetAttrDoc2.plsqldoc_callout',
2502: 'Start executing PLSQL Doc procedure - '||procname, true);
2503: end if;
2504:
2505: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
2510: in out doctype;
2511:
2512: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
2513: wf_log_pkg.string2(wf_log_pkg.level_statement,
2514: 'wf.plsql.wf_notification.GetAttrDoc2.plsqldoc_callout',
2515: 'End executing PLSQL Doc procedure - '||procname, false);
2516: end if;
2517:
2518:
2516: end if;
2517:
2518:
2519: -- Translate doc types if needed
2520: if ((disptype = wf_notification.doc_html) and
2521: (doctype = wf_notification.doc_text)) then
2522: -- Change plain text to html by wrapping in preformatted tags
2523: document := '
'||document||'
2517:
2518:
2519: -- Translate doc types if needed
2520: if ((disptype = wf_notification.doc_html) and
2521: (doctype = wf_notification.doc_text)) then
2522: -- Change plain text to html by wrapping in preformatted tags
2523: document := '
'||document||'
2532: -- This is used for translating number/date strings.
2533: begin
2534: select WMATL.DISPLAY_NAME, NVL(WMA.FORMAT, '_blank')
2535: into display_name, target
2536: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
2537: WF_MESSAGE_ATTRIBUTES_TL WMATL, WF_MESSAGE_ATTRIBUTES WMA
2538: where WNA.NOTIFICATION_ID = nid
2539: and WNA.NAME = aname
2540: and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
2556: ** If this is a plain text request then just return the display
2557: ** name for the attribute. If it is html then get the attachment
2558: ** url link and return it.
2559: */
2560: if (disptype = wf_notification.doc_html) THEN
2561:
2562: -- Returns session user name if available
2563: username := Wfa_Sec.GetUser;
2564:
2575: end if;
2576: document := null;
2577: exception
2578: when others then
2579: wf_core.context('wf_notification', 'GetAttrDoc2', to_char(nid), aname, disptype);
2580: raise;
2581: end GetAttrDoc2;
2582:
2583: -- bug 2581129
2604: begin
2605: -- Get subject
2606: select WM.SUBJECT
2607: into local_subject
2608: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2609: where N.NOTIFICATION_ID = nid
2610: and N.MESSAGE_NAME = WM.NAME
2611: and N.MESSAGE_TYPE = WM.TYPE;
2612:
2649: function GetSubject(nid in number)
2650: return varchar2 is
2651: local_subject varchar2(240);
2652: begin
2653: return (Wf_Notification.GetSubject(nid, 'text/html'));
2654: end GetSubject;
2655:
2656: --
2657: -- GetBody
2662: -- truncates values at 1950 chars.
2663: -- IN:
2664: -- nid - Notification Id
2665: -- disptype - Requested display type. Valid values:
2666: -- wf_notification.doc_text - 'text/plain'
2667: -- wf_notification.doc_html - 'text/html'
2668: -- wf_notification.doc_attach - ''
2669: -- RETURNS:
2670: -- Substituted message body
2663: -- IN:
2664: -- nid - Notification Id
2665: -- disptype - Requested display type. Valid values:
2666: -- wf_notification.doc_text - 'text/plain'
2667: -- wf_notification.doc_html - 'text/html'
2668: -- wf_notification.doc_attach - ''
2669: -- RETURNS:
2670: -- Substituted message body
2671: -- NOTE:
2664: -- nid - Notification Id
2665: -- disptype - Requested display type. Valid values:
2666: -- wf_notification.doc_text - 'text/plain'
2667: -- wf_notification.doc_html - 'text/html'
2668: -- wf_notification.doc_attach - ''
2669: -- RETURNS:
2670: -- Substituted message body
2671: -- NOTE:
2672: -- If errors are detected this routine returns the body unsubstituted,
2696: begin
2697: -- Get body
2698: select WM.BODY, WM.HTML_BODY
2699: into local_body, local_html_body
2700: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2701: where N.NOTIFICATION_ID = nid
2702: and N.MESSAGE_NAME = WM.NAME
2703: and N.MESSAGE_TYPE = WM.TYPE;
2704:
2701: where N.NOTIFICATION_ID = nid
2702: and N.MESSAGE_NAME = WM.NAME
2703: and N.MESSAGE_TYPE = WM.TYPE;
2704:
2705: -- If user has not used WF_NOTIFICATION(HISTORY) or #HISTORY, append Action History in the
2706: -- notification body by default if this is a...
2707: -- 1. Response required notification
2708: -- 2. FYI notification with at least one Reassign action
2709: -- Query to check if the ntf is FYI or not
2709: -- Query to check if the ntf is FYI or not
2710: SELECT count(1)
2711: INTO l_resp_cnt
2712: FROM wf_message_attributes wma,
2713: wf_notifications wn
2714: WHERE wn.notification_id = nid
2715: AND wma.message_type = wn.message_type
2716: AND wma.message_name = wn.message_name
2717: AND wma.subtype = 'RESPOND'
2738: if ((l_fyi and l_comm_cnt > 0) or not l_fyi) then
2739: -- According to bug 3612609, if the user just defines #HISTORY, but does not place it in the
2740: -- message body, even then it should be used instead of default WF Action History
2741: begin
2742: l_cust_hist := Wf_Notification.GetAttrText(nid, '#HISTORY');
2743: exception
2744: when others then
2745: l_cust_hist := '';
2746: Wf_Core.Clear;
2749: -- would not be here.
2750: if (l_cust_hist is not null and upper(trim(substr(l_cust_hist, 1, 5))) = 'PLSQL') then
2751: l_action_hist := '&#HISTORY';
2752: else
2753: l_action_hist := 'WF_NOTIFICATION(HISTORY)';
2754: end if;
2755:
2756: -- Either a FYI with at least one reassign/Request Info or a Response notification.
2757: -- So, append Action History
2754: end if;
2755:
2756: -- Either a FYI with at least one reassign/Request Info or a Response notification.
2757: -- So, append Action History
2758: if (local_body is not null and instrb(local_body, 'WF_NOTIFICATION(HISTORY)') = 0 and
2759: instrb(local_body, '&#HISTORY') = 0) then
2760:
2761: local_body := local_body || Wf_Core.newline || l_action_hist;
2762: end if;
2760:
2761: local_body := local_body || Wf_Core.newline || l_action_hist;
2762: end if;
2763:
2764: if (local_html_body is not null and instrb(local_html_body, 'WF_NOTIFICATION(HISTORY)') = 0 and
2765: instrb(local_html_body, '&#HISTORY') = 0) then
2766: -- Defer adding history macro until after stripping off BODY tags
2767: l_html_hist := true;
2768: end if;
2768: end if;
2769: end if;
2770:
2771: -- Return substituted body.
2772: if (disptype = wf_notification.doc_text) then
2773: local_body := GetText(local_body, nid, disptype);
2774:
2775: -- replace the functions here
2776: local_body := runFuncOnBody(nid, local_body, disptype);
2817: end if;
2818:
2819: exception
2820: when others then
2821: wf_core.context('Wf_Notification', 'GetBody', to_char(nid), disptype);
2822: raise;
2823: end GetBody;
2824:
2825: --
2836: -- NOTE:
2837: -- If errors are detected this routine returns the body unsubstituted,
2838: -- or null if all else fails, instead of raising exceptions. It must do
2839: -- this so the routine can be pragma'd and used in the
2840: -- wf_notifications_view view.
2841: --
2842: function GetShortBody(nid in number)
2843: return varchar2 is
2844: local_body varchar2(4000);
2846: begin
2847: -- Get body
2848: select WM.BODY
2849: into local_body
2850: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
2851: where N.NOTIFICATION_ID = nid
2852: and N.MESSAGE_NAME = WM.NAME
2853: and N.MESSAGE_TYPE = WM.TYPE;
2854:
2911: GetInfo.message_name,
2912: GetInfo.priority,
2913: GetInfo.due_date,
2914: GetInfo.status
2915: from WF_NOTIFICATIONS N
2916: where N.NOTIFICATION_ID = nid;
2917: exception
2918: when no_data_found then
2919: wf_core.token('NID', to_char(nid));
2921: end;
2922:
2923: exception
2924: when others then
2925: wf_core.context('Wf_Notification', 'GetInfo', to_char(nid));
2926: raise;
2927: end GetInfo;
2928:
2929: --
2949: -- Get responder
2950: begin
2951: select WN.RESPONDER
2952: into respbuf
2953: from WF_NOTIFICATIONS WN
2954: where WN.NOTIFICATION_ID = nid;
2955: exception
2956: when no_data_found then
2957: wf_core.token('NID', to_char(nid));
2960:
2961: return(respbuf);
2962: exception
2963: when others then
2964: Wf_Core.Context('Wf_Notification', 'Responder', to_char(nid));
2965: raise;
2966: end Responder;
2967:
2968: -- AccessCheck
2983: nkey := substr(access_str, pos+1);
2984:
2985: select recipient_role
2986: into uname
2987: from WF_NOTIFICATIONS
2988: where NOTIFICATION_ID = nid
2989: and ACCESS_KEY = nkey;
2990:
2991: return uname;
3037: -- by that process.
3038: --
3039: if (callback is not null) then
3040: -- ### Review Note 3 - private function
3041: l_charcheck := WF_NOTIFICATION_UTIL.CheckIllegalChar(callback);
3042:
3043:
3044: -- BINDVAR_SCAN_IGNORE
3045: sqlbuf := 'begin '||callback||
3072: return mailpref;
3073:
3074: exception
3075: when others then
3076: Wf_Core.Context('Wf_Notification', 'GetMailPreference', role);
3077: raise;
3078: end GetMailPreference;
3079:
3080: --
3129: -- Get ntf current recipient and message
3130: begin
3131: select WN.RECIPIENT_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME
3132: into recip, msgtype, msgname
3133: from WF_NOTIFICATIONS WN
3134: where WN.NOTIFICATION_ID = nid;
3135:
3136: o_recip := recip; -- set original recipient
3137: exception
3140: wf_core.raise('WFNTF_NID');
3141: end;
3142:
3143: /* implement the above loop recursively */
3144: if (cnt > wf_notification.max_forward) then
3145: -- it means max_forward must have been exceeded. Treat as a loop error.
3146: wf_core.token('NID', to_char(nid));
3147: wf_core.raise('WFNTF_ROUTE_LOOP');
3148: end if;
3171: -- N: Allow Reassign
3172: -- B: Allow Reassign only through Routing Rule
3173: l_hide_reassign := 'N';
3174: begin
3175: l_hide_reassign := Wf_Notification.GetAttrText(nid, '#HIDE_REASSIGN');
3176: exception
3177: when others then
3178: -- Clear the error stack since we ignore the error
3179: Wf_Core.Clear;
3197: recip := rulerec.action_argument;
3198:
3199: begin
3200: -- ### implement this in next release
3201: -- Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1);
3202: Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3203: exception
3204: when others then
3205: raise badfwd;
3198:
3199: begin
3200: -- ### implement this in next release
3201: -- Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1);
3202: Wf_Notification.Forward(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3203: exception
3204: when others then
3205: raise badfwd;
3206: end;
3214: recip := rulerec.action_argument;
3215:
3216: begin
3217: -- ### implement this in next release
3218: -- Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1);
3219: Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3220: exception
3221: when others then
3222: raise badfwd;
3215:
3216: begin
3217: -- ### implement this in next release
3218: -- Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1);
3219: Wf_Notification.Transfer(nid, recip, newcomment, o_recip, cnt+1, 'RULE');
3220: exception
3221: when others then
3222: raise badfwd;
3223: end;
3225: -- RESPOND
3226: -- Query response values for this rule and set attrs accordingly
3227: for respattr in attrcurs(rulerec.rule_id) loop
3228: if (respattr.type = 'NUMBER') then
3229: Wf_Notification.SetAttrNumber(nid, respattr.name,
3230: respattr.number_value);
3231: elsif (respattr.type = 'DATE') then
3232: Wf_Notification.SetAttrDate(nid, respattr.name,
3233: respattr.date_value);
3228: if (respattr.type = 'NUMBER') then
3229: Wf_Notification.SetAttrNumber(nid, respattr.name,
3230: respattr.number_value);
3231: elsif (respattr.type = 'DATE') then
3232: Wf_Notification.SetAttrDate(nid, respattr.name,
3233: respattr.date_value);
3234: else -- All other types use text
3235: Wf_Notification.SetAttrText(nid, respattr.name,
3236: respattr.text_value);
3231: elsif (respattr.type = 'DATE') then
3232: Wf_Notification.SetAttrDate(nid, respattr.name,
3233: respattr.date_value);
3234: else -- All other types use text
3235: Wf_Notification.SetAttrText(nid, respattr.name,
3236: respattr.text_value);
3237: end if;
3238: end loop;
3239:
3237: end if;
3238: end loop;
3239:
3240: -- Complete response
3241: Wf_Notification.Respond(nid, newcomment, recip, 'RULE');
3242: else
3243: -- This must be one of:
3244: -- a. NOOP rule
3245: -- b. No routing rule found
3250: return;
3251: exception
3252: when inactive_role then
3253: begin
3254: update WF_NOTIFICATIONS set
3255: USER_COMMENT = substr(USER_COMMENT||decode(nvl(USER_COMMENT,'T'),
3256: 'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
3257: where NOTIFICATION_ID = nid;
3258: exception
3256: 'T', null, wf_core.newline)||wf_core.translate('INACTIVE_ROLE'), 1, 4000)
3257: where NOTIFICATION_ID = nid;
3258: exception
3259: when others then
3260: wf_core.context('Wf_Notification', 'Route (update comment)',to_char(nid));
3261: raise;
3262: end;
3263: when badfwd then
3264: Wf_Core.Get_Error(dummy, errmsg, dummy);
3273: 1, 4000);
3274: begin
3275: -- append newcomment to the existing comment.
3276: -- need to add a newline character if user_comment is not null.
3277: update WF_NOTIFICATIONS set
3278: USER_COMMENT = substr(USER_COMMENT||
3279: decode(nvl(USER_COMMENT,'T'),
3280: 'T', null, wf_core.newline)||
3281: Route.newcomment, 1, 4000)
3281: Route.newcomment, 1, 4000)
3282: where NOTIFICATION_ID = nid;
3283: exception
3284: when OTHERS then
3285: wf_core.context('Wf_Notification', 'Route (update comment)',
3286: to_char(nid));
3287: raise;
3288: end;
3289:
3290: when others then
3291: if (rulecurs%isopen) then
3292: close rulecurs;
3293: end if;
3294: wf_core.context('Wf_Notification', 'Route', to_char(nid));
3295: raise;
3296: end Route;
3297:
3298: --
3314: begin
3315:
3316: -- Derive item type, item key and activity id from the context
3317: -- when no ':' or just one ':', context does not conform to WF standard
3318: -- it could be sent by calling wf_notification.send directly
3319: -- in this case, we just return false to preserve the old behavior
3320: validate_context (p_context, l_item_type, l_item_key, l_actid);
3321: if (l_item_type is null or l_item_key is null or l_actid is null) then
3322: return false;
3338: return true;
3339:
3340: exception
3341: when others then
3342: Wf_Core.Context('Wf_Notification', 'First_Execution', p_context);
3343: raise;
3344: end First_Execution;
3345:
3346: -- Denormalize_Columns_Internal(PRIVATE)
3358: wna.date_value
3359: from wf_ntf_rules wnr,
3360: wf_ntf_rule_maps wnrm,
3361: wf_ntf_rule_criteria wnrc,
3362: wf_notification_attributes wna,
3363: wf_notifications wn
3364: where wnr.rule_name = wnrc.rule_name
3365: and wnrc.message_type = wn.message_type
3366: and wnr.status = 'ENABLED'
3359: from wf_ntf_rules wnr,
3360: wf_ntf_rule_maps wnrm,
3361: wf_ntf_rule_criteria wnrc,
3362: wf_notification_attributes wna,
3363: wf_notifications wn
3364: where wnr.rule_name = wnrc.rule_name
3365: and wnrc.message_type = wn.message_type
3366: and wnr.status = 'ENABLED'
3367: and wnrc.rule_name = wnrm.rule_name
3421: na(c.idx) := c.number_value;
3422: end if;
3423: end loop;
3424:
3425: update WF_NOTIFICATIONS
3426: set
3427: PROTECTED_TEXT_ATTRIBUTE1 = pta(1)
3428: ,PROTECTED_TEXT_ATTRIBUTE2 = pta(2)
3429: ,PROTECTED_TEXT_ATTRIBUTE3 = pta(3)
3489: where notification_id = p_nid;
3490:
3491: exception
3492: when OTHERS then
3493: wf_core.context('Wf_Notification', 'denormalize_columns_internal',
3494: p_item_key, p_user_key, to_char(p_nid));
3495: raise;
3496: end Denormalize_columns_internal;
3497:
3508: cursor c_notifications is
3509: select wi.item_key, wi.user_key, wn.notification_id
3510: from wf_items wi,
3511: wf_item_activity_statuses wias,
3512: wf_notifications wn
3513: where wi.item_key = wias.item_key
3514: and wi.item_type = wias.item_type
3515: and wias.notification_id = wn.group_id
3516: and (wn.message_type = p_item_type or p_item_type is null)
3609: where MESSAGE_TYPE = msg_type
3610: and MESSAGE_NAME = msg_name;
3611: begin
3612: -- Check role is valid and get mail preference
3613: mailpref := Wf_Notification.GetMailPreference(role, callback, context);
3614:
3615: -- Create new nid and insert notification
3616: select WF_NOTIFICATIONS_S.NEXTVAL
3617: into nid
3612: -- Check role is valid and get mail preference
3613: mailpref := Wf_Notification.GetMailPreference(role, callback, context);
3614:
3615: -- Create new nid and insert notification
3616: select WF_NOTIFICATIONS_S.NEXTVAL
3617: into nid
3618: from SYS.DUAL;
3619:
3620: insert into WF_NOTIFICATIONS (
3616: select WF_NOTIFICATIONS_S.NEXTVAL
3617: into nid
3618: from SYS.DUAL;
3619:
3620: insert into WF_NOTIFICATIONS (
3621: NOTIFICATION_ID,
3622: GROUP_ID,
3623: MESSAGE_TYPE,
3624: MESSAGE_NAME,
3662: -- Open and parse cursor for dynamic sql for getting attr values
3663: -- Bug 2376033 added event value in call to CB
3664: if (callback is not null) then
3665: -- ### Review Note 2
3666: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
3667: --Throw the Illegal exception when the check fails
3668:
3669:
3670: -- BINDVAR_SCAN_IGNORE
3723: else
3724: -- Bug 2580807 call with original signature for backward
3725: -- compatibility
3726: -- ### Review Note 2 - callback is from table
3727: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
3728: --Throw the Illegal exception when the check fails
3729:
3730:
3731: -- BINDVAR_SCAN_IGNORE
3755: --
3756: -- Insert notification attribute
3757: -- Bug 2376033 insert the event value
3758: --
3759: insert into WF_NOTIFICATION_ATTRIBUTES (
3760: NOTIFICATION_ID,
3761: NAME,
3762: TEXT_VALUE,
3763: NUMBER_VALUE,
3782: -- only for the first time
3783: if (First_Execution(context)) then
3784: l_send_source := 'FIRST';
3785: begin
3786: l_send_comment := Wf_Notification.GetAttrText(nid, '#SUBMIT_COMMENTS');
3787: exception
3788: when others then
3789: if(Wf_Core.Error_Name = 'WFNTF_ATTR') then
3790: Wf_Core.Clear();
3797: end if;
3798:
3799: -- If #FROM_ROLE is defined, we will get the value in this attribute
3800: begin
3801: l_from_role := Wf_Notification.GetAttrText(nid, '#FROM_ROLE');
3802: exception
3803: when OTHERS then
3804: wf_core.clear;
3805: -- Check if the notification is sent under a valid Fwk Session
3809: -- Use dummy user WF_SYSTEM as last resort
3810: if (l_from_role is null) then
3811: l_from_role := 'WF_SYSTEM';
3812: end if;
3813: Wf_Notification.SetComments(nid, l_from_role, role, 'SEND', l_send_source, l_send_comment);
3814:
3815: -- Check for auto-routing of notification just sent
3816: Wf_Notification.Route(nid, 0);
3817:
3812: end if;
3813: Wf_Notification.SetComments(nid, l_from_role, role, 'SEND', l_send_source, l_send_comment);
3814:
3815: -- Check for auto-routing of notification just sent
3816: Wf_Notification.Route(nid, 0);
3817:
3818: -- Denormalize the Notification after auto-routing is done
3819: Wf_Notification.Denormalize_Notification(nid);
3820:
3815: -- Check for auto-routing of notification just sent
3816: Wf_Notification.Route(nid, 0);
3817:
3818: -- Denormalize the Notification after auto-routing is done
3819: Wf_Notification.Denormalize_Notification(nid);
3820:
3821: -- Denormalize custom columns
3822: -- Derive item type, item key and activity id from the context
3823: if context is not null then
3835: else
3836: l_itemkey := null;
3837: l_userkey := null;
3838: end if;
3839: wf_notification.denormalize_columns_internal(l_itemkey, l_userkey, nid);
3840:
3841:
3842: -- DL: Move this to be the last step before returning the nid
3843: -- The recipient_role could be updated during auto-routing
3851: -- wf_xml.EnqueueNotification(nid);
3852:
3853: --Bug 2283697
3854: --To raise an EVENT whenever DML operation is performed on
3855: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
3856: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
3857: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
3858: wf_event.AddParameterToList('GROUP_ID',nvl(group_id,nid),l_parameterlist);
3859:
3881: if wf_event.phase_maxthreshold is null then
3882: -- means deferred mode , avoid synchronous processing of Send event
3883: wf_event.SetDispatchMode('ASYNC');
3884:
3885: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
3886: p_event_key => to_char(nid),
3887: p_parameters => l_parameterlist);
3888:
3889: wf_event.phase_maxthreshold := null;
3887: p_parameters => l_parameterlist);
3888:
3889: wf_event.phase_maxthreshold := null;
3890: else
3891: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
3892: p_event_key => to_char(nid),
3893: p_parameters => l_parameterlist);
3894:
3895: end if;
3897: return (nid);
3898:
3899: exception
3900: when others then
3901: wf_core.context('Wf_Notification', 'SendSingle', role, msg_type,
3902: msg_name, due_date, callback);
3903: raise;
3904: end SendSingle;
3905:
3983: -- Bug 7914921. The context not always comes in format itemtype:itemkey:actid
3984: if (itemtype is not null AND itemkey is not null AND actid is not null) then
3985: SELECT wn.notification_id
3986: INTO prev_nid
3987: FROM wf_notifications wn,
3988: wf_comments wc
3989: WHERE
3990: EXISTS ( SELECT /*+ NO_UNNEST */ 'x'
3991: FROM wf_item_activity_statuses_h wiash
4013:
4014: return (nid);
4015: exception
4016: when others then
4017: wf_core.context('Wf_Notification', 'Send', role, msg_type,
4018: msg_name, due_date, callback);
4019: raise;
4020: end Send;
4021:
4116:
4117: return (gid);
4118: exception
4119: when others then
4120: wf_core.context('Wf_Notification', 'SendGroup', role, msg_type,
4121: msg_name, due_date, callback);
4122: raise;
4123: end SendGroup;
4124:
4200: , wn.message_type, wn.message_name -- <7641725>
4201: into status, cb, context, old_role, -- newcomment,
4202: old_origrole,l_more_info_role,l_from_role
4203: , l_msgType, l_msgName
4204: from WF_NOTIFICATIONS WN
4205: where WN.NOTIFICATION_ID = nid
4206: for update nowait;
4207: exception
4208: when no_data_found then
4220: end if;
4221:
4222: -- Check role is valid and get mail preference
4223: begin
4224: mailpref := Wf_Notification.GetMailPreference(new_role, cb, context);
4225: exception
4226: when others then
4227: wf_core.token('ROLE', new_role);
4228: if (fmode = 'FORWARD') then
4272: tvalue := new_role;
4273: nvalue := nid;
4274: -- ### Review Note 2 - cb is from table
4275: -- BINDVAR_SCAN_IGNORE
4276: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
4277: --Throw the Illegal exception when the check fails
4278:
4279:
4280: sqlbuf := 'begin '||cb||
4317: -- BUG 2331070 CTILLEY - added update to FROM_ROLE
4318: -- Bug 2474770
4319: -- Update the more_info_role aswell
4320: if (fmode = 'TRANSFER') then
4321: update WF_NOTIFICATIONS set
4322: RECIPIENT_ROLE = ForwardInternal.new_role,
4323: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4324: 'TRANSFER', ForwardInternal.new_role,
4325: ORIGINAL_RECIPIENT),
4334: FROM_ROLE = l_from_role,
4335: MORE_INFO_ROLE = l_more_info_role
4336: where NOTIFICATION_ID = nid;
4337:
4338: Wf_Notification.SetComments(nid, l_from_role, new_role, 'TRANSFER',
4339: action_source, forward_comment);
4340:
4341: else
4342: update WF_NOTIFICATIONS set
4338: Wf_Notification.SetComments(nid, l_from_role, new_role, 'TRANSFER',
4339: action_source, forward_comment);
4340:
4341: else
4342: update WF_NOTIFICATIONS set
4343: RECIPIENT_ROLE = ForwardInternal.new_role,
4344: ORIGINAL_RECIPIENT = decode(ForwardInternal.fmode,
4345: 'TRANSFER', ForwardInternal.new_role,
4346: ORIGINAL_RECIPIENT),
4354: FROM_ROLE = l_from_role,
4355: MORE_INFO_ROLE = l_more_info_role
4356: where NOTIFICATION_ID = nid;
4357:
4358: Wf_Notification.SetComments(nid, l_from_role, new_role, 'DELEGATE',
4359: action_source, forward_comment);
4360: end if;
4361:
4362: -- Pop any messages from then outbound queue
4366: -- user goes in and does an action from the worklist.
4367: -- wf_xml.RemoveNotification(nid);
4368:
4369: -- Check for auto-routing of notification just forwarded
4370: Wf_Notification.Route(nid, cnt);
4371:
4372: -- Denormalize after all the routing is done
4373: if (cnt = 0) then
4374: Wf_Notification.Denormalize_Notification(nid);
4370: Wf_Notification.Route(nid, cnt);
4371:
4372: -- Denormalize after all the routing is done
4373: if (cnt = 0) then
4374: Wf_Notification.Denormalize_Notification(nid);
4375:
4376: -- Push the new notification to the queue
4377: -- The call to wf_xml.EnqueueNotification has been moved
4378: -- to an event subscription.
4380: end if;
4381:
4382: --Bug 2283697
4383: --To raise an EVENT whenever DML operation is performed on
4384: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4385: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4386: wf_event.AddParameterToList('NEW_ROLE',new_role,l_parameterlist);
4387: wf_event.AddParameterToList('MODE',fmode,l_parameterlist);
4388: if (user is not null) then
4398: , l_msgType||':'||l_msgName, l_parameterlist);
4399:
4400: select WN.RECIPIENT_ROLE
4401: into l_recipient_role
4402: from WF_NOTIFICATIONS WN
4403: where WN.NOTIFICATION_ID = nid;
4404:
4405: Wf_Directory.GetRoleInfo2(l_recipient_role, role_info_tbl);
4406: l_language := role_info_tbl(1).language;
4418: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
4419:
4420:
4421: --Raise the event
4422: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.reassign',
4423: p_event_key => to_char(nid),
4424: p_parameters => l_parameterlist);
4425:
4426: exception
4424: p_parameters => l_parameterlist);
4425:
4426: exception
4427: when others then
4428: wf_core.context('Wf_Notification', 'ForwardInternal', to_char(nid),
4429: new_role, fmode, forward_comment);
4430: raise;
4431: end ForwardInternal;
4432:
4452: begin
4453: ForwardInternal(nid, new_role, 'FORWARD', forward_comment, user, cnt, action_source);
4454: exception
4455: when others then
4456: wf_core.context('Wf_Notification', 'Forward', to_char(nid),
4457: new_role, forward_comment);
4458: -- This call is for enhanced error handling with respect to OAFwk
4459: wf_notification.SetUIErrorMessage;
4460: raise;
4455: when others then
4456: wf_core.context('Wf_Notification', 'Forward', to_char(nid),
4457: new_role, forward_comment);
4458: -- This call is for enhanced error handling with respect to OAFwk
4459: wf_notification.SetUIErrorMessage;
4460: raise;
4461: end Forward;
4462:
4463: --
4482: begin
4483: ForwardInternal(nid, new_role, 'TRANSFER', forward_comment, user, cnt, action_source);
4484: exception
4485: when others then
4486: wf_core.context('Wf_Notification', 'Transfer', to_char(nid),
4487: new_role, forward_comment);
4488: -- This call is for enhanced error handling with respect to OAFwk
4489: wf_notification.SetUIErrorMessage;
4490: raise;
4485: when others then
4486: wf_core.context('Wf_Notification', 'Transfer', to_char(nid),
4487: new_role, forward_comment);
4488: -- This call is for enhanced error handling with respect to OAFwk
4489: wf_notification.SetUIErrorMessage;
4490: raise;
4491: end Transfer;
4492:
4493: --
4529: -- Check the notification exists and is open
4530: begin
4531: select WN.STATUS, WN.CALLBACK, WN.CONTEXT, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.LANGUAGE
4532: into status, cb, context, l_msg_type, l_msg_name, l_language
4533: from WF_NOTIFICATIONS WN
4534: where WN.NOTIFICATION_ID = nid
4535: for update nowait;
4536: exception
4537: when no_data_found then
4543: wf_core.raise('WFNTF_NID_OPEN');
4544: end if;
4545:
4546: -- Check role is valid and get mail preference
4547: mailpref := Wf_Notification.GetMailPreference(role, cb, context);
4548:
4549: -- If no responses expected, then do not mail cancel notice
4550: -- regardless of role notification_preference setting.
4551: begin
4550: -- regardless of role notification_preference setting.
4551: begin
4552: select 1 into dummy from sys.dual where exists
4553: (select NULL
4554: from WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES WMA
4555: where WN.NOTIFICATION_ID = nid
4556: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
4557: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4558: and WMA.SUBTYPE = 'RESPOND');
4572: else
4573: l_mail := '';
4574: end if;
4575:
4576: update WF_NOTIFICATIONS set
4577: STATUS = 'CANCELED',
4578: END_DATE = sysdate,
4579: -- USER_COMMENT = CancelSingle.newcomment,
4580: MAIL_STATUS = decode(MAIL_STATUS,
4599:
4600: if (l_from_role is null) then
4601: l_from_role := 'WF_SYSTEM';
4602: end if;
4603: Wf_Notification.SetComments(nid, l_from_role, 'WF_SYSTEM', l_action, null, newcomment);
4604:
4605: -- GK: 1636402: wf_xml.RemoveNotification is not necessary
4606: -- since the message is likely to be sent by the time the
4607: -- user goes in and does an action from the worklist.
4613: -- wf_xml.EnqueueNotification(nid);
4614:
4615: --Bug 2283697
4616: --To raise an EVENT whenever DML operation is performed on
4617: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4618: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4619: wf_event.AddParameterToList('ROLE',role,l_parameterlist);
4620: wf_event.addParameterToList('Q_CORRELATION_ID', l_msg_type || ':'||
4621: l_msg_name, l_parameterlist);
4631: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
4632: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
4633:
4634: --Raise the event
4635: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.cancel',
4636: p_event_key => to_char(nid),
4637: p_parameters => l_parameterlist);
4638:
4639:
4638:
4639:
4640: exception
4641: when others then
4642: wf_core.context('Wf_Notification', 'CancelSingle', to_char(nid),
4643: role, cancel_comment);
4644: raise;
4645: end CancelSingle;
4646:
4665: -- Check the notification exists and is open
4666: begin
4667: select STATUS, RECIPIENT_ROLE
4668: into status, role
4669: from WF_NOTIFICATIONS
4670: where NOTIFICATION_ID = nid
4671: for update nowait;
4672: exception
4673: when no_data_found then
4684: CancelSingle(nid, role, cancel_comment, FALSE);
4685:
4686: exception
4687: when others then
4688: wf_core.context('Wf_Notification', 'Cancel', to_char(nid), cancel_comment);
4689: raise;
4690: end Cancel;
4691:
4692: --
4702: is
4703: -- Get all still open notifications in the group
4704: cursor group_curs is
4705: select NOTIFICATION_ID, RECIPIENT_ROLE
4706: from WF_NOTIFICATIONS
4707: where GROUP_ID = gid
4708: and status = 'OPEN'
4709: for update nowait;
4710:
4721: cancel_comment, timeout);
4722: end loop;
4723: exception
4724: when others then
4725: wf_core.context('Wf_Notification', 'CancelGroup', to_char(gid),
4726: cancel_comment);
4727: raise;
4728: end CancelGroup;
4729:
4755:
4756: cursor notification_attrs_cursor(nid number) is
4757: select WNA.NAME, WMA.TYPE, WNA.TEXT_VALUE, WNA.NUMBER_VALUE,
4758: WNA.DATE_VALUE
4759: from WF_NOTIFICATION_ATTRIBUTES WNA,
4760: WF_MESSAGE_ATTRIBUTES WMA,
4761: WF_NOTIFICATIONS WN
4762: where WNA.NOTIFICATION_ID = nid
4763: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4757: select WNA.NAME, WMA.TYPE, WNA.TEXT_VALUE, WNA.NUMBER_VALUE,
4758: WNA.DATE_VALUE
4759: from WF_NOTIFICATION_ATTRIBUTES WNA,
4760: WF_MESSAGE_ATTRIBUTES WMA,
4761: WF_NOTIFICATIONS WN
4762: where WNA.NOTIFICATION_ID = nid
4763: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
4764: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
4765: and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
4799: end if;
4800:
4801: -- kma bug2376058 digital signature support
4802: begin
4803: proxyuser := Wf_Notification.GetAttrText(nid, '#WF_PROXIED_VIA');
4804: exception
4805: when others then
4806: if (wf_core.error_name = 'WFNTF_ATTR') then
4807: -- Pass null result if no result attribute.
4818: wf_core.raise('WFNTF_DIGSIG_USER_MISMATCH');
4819: end if;
4820:
4821: -- bug 2698999 Checking if ntf's signature requirements are met
4822: if (NOT Wf_Notification.NtfSignRequirementsMet(nid)) then
4823: wf_core.token('NID', to_char(nid));
4824: wf_core.raise('WFNTF_NOT_SIGNED');
4825: end if;
4826:
4829: select N.CALLBACK, N.CONTEXT, N.STATUS, N.USER_COMMENT,
4830: N.RECIPIENT_ROLE, N.ORIGINAL_RECIPIENT,N.MORE_INFO_ROLE, N.FROM_ROLE, N.LANGUAGE
4831: into respond.callback, respond.context, respond.status, newcomment,
4832: l_recip_role,l_orig_recip_role,l_more_info_role, l_from_role, l_language
4833: from WF_NOTIFICATIONS N
4834: where N.NOTIFICATION_ID = nid
4835: for update nowait;
4836: exception
4837: when no_data_found then
4880: -- Set the approrpiate responder to context user
4881: g_context_user := l_responder;
4882:
4883: if respond.respond_comment is null then
4884: g_context_user_comment := Wf_Notification.GetAttrText(nid,'WF_NOTE',TRUE);
4885: else
4886: g_context_user_comment := respond.respond_comment;
4887: end if;
4888: g_context_recipient_role := l_recip_role;
4894: -- Call the callback in VALIDATE mode to execute the post notification
4895: -- function to perform some custom validation and reject the response by
4896: -- raising exception. If validation is already done in RESPOND mode, it
4897: -- can stay there... VALIDATE mode can be called back from outside of
4898: -- notification code also before calling the Wf_Notification.Respond API
4899: if (callback is not null) then
4900: tvalue := respond.responder;
4901: nvalue := nid;
4902: -- ### Review Note 2 - callback is from table
4900: tvalue := respond.responder;
4901: nvalue := nid;
4902: -- ### Review Note 2 - callback is from table
4903: -- Check for bug#3827935
4904: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
4905: --Throw the Illegal exception when the check fails
4906:
4907:
4908: -- BINDVAR_SCAN_IGNORE
4949: -- end if;
4950: -- end if;
4951:
4952: -- Mark notification closed
4953: update WF_NOTIFICATIONS
4954: set STATUS = 'CLOSED',
4955: MAIL_STATUS = NULL,
4956: END_DATE = sysdate,
4957: -- RESPONDER = respond.responder
4961: -- USER_COMMENT = respond.newcomment
4962: where NOTIFICATION_ID = respond.nid;
4963:
4964: -- responder should be the From role that appears in the action history
4965: Wf_Notification.SetComments(nid, l_responder, 'WF_SYSTEM', 'RESPOND',
4966: action_source, respond_comment);
4967:
4968: --Bug 2283697
4969: --To raise an EVENT whenever DML operation is performed on
4966: action_source, respond_comment);
4967:
4968: --Bug 2283697
4969: --To raise an EVENT whenever DML operation is performed on
4970: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
4971: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
4972: wf_event.AddParameterToList('RESPONDER',respond.responder,l_parameterlist);
4973:
4974: -- AppSearch
4981: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
4982: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
4983:
4984: --Raise the event
4985: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.respond',
4986: p_event_key => to_char(nid),
4987: p_parameters => l_parameterlist);
4988:
4989: -- If no callback, there is nothing else to do.
4994: --
4995: -- Open dynamic sql cursor for SET callback calls.
4996: --
4997: -- ### Review Note 2 - callback is from table
4998: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
4999: --Throw the Illegal exception when the check fails
5000:
5001:
5002: -- BINDVAR_SCAN_IGNORE
5038: -- Call callback one more time to mark activity COMPLETE.
5039: -- Send the result and notification id as the value.
5040: --
5041: begin
5042: tvalue := Wf_Notification.GetAttrText(nid, 'RESULT');
5043:
5044: exception
5045: when others then
5046: if (wf_core.error_name = 'WFNTF_ATTR') then
5056:
5057: -- Reparse and bind dynamic sql for the COMPLETE callback call.
5058: -- ### Review Note 2 - callback is from table
5059: -- Check for bug#3827935
5060: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
5061: --Throw the Illegal exception when the check fails
5062:
5063:
5064: -- BINDVAR_SCAN_IGNORE[3]
5079: -- user goes in and does an action from the worklist.
5080: -- wf_xml.RemoveNotification(nid);
5081: exception
5082: when others then
5083: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
5084: respond_comment, responder);
5085: -- This call is for enhanced error handling with respect to OAFwk
5086: wf_notification.SetUIErrorMessage;
5087: raise;
5082: when others then
5083: wf_core.context('Wf_Notification', 'Respond', to_char(nid),
5084: respond_comment, responder);
5085: -- This call is for enhanced error handling with respect to OAFwk
5086: wf_notification.SetUIErrorMessage;
5087: raise;
5088: end Respond;
5089:
5090: --
5120: -- Get callback, check for valid notification id.
5121: begin
5122: select N.CALLBACK, N.CONTEXT
5123: into TestContext.callback, TestContext.context
5124: from WF_NOTIFICATIONS N
5125: where N.NOTIFICATION_ID = nid;
5126: exception
5127: when no_data_found then
5128: wf_core.token('NID', to_char(nid));
5136:
5137: -- Open dynamic sql cursor for callback call
5138: -- ### Review Note 2 - callback is from table
5139: -- Check for bug#3827935
5140: l_charcheck := wf_notification_util.CheckIllegalChar(callback);
5141: --Throw the Illegal exception when the check fails
5142:
5143:
5144: -- BINDVAR_SCAN_IGNORE
5161: end if;
5162:
5163: exception
5164: when others then
5165: wf_core.context('Wf_Notification', 'TestContext', to_char(nid));
5166: raise;
5167: end TestContext;
5168:
5169: --
5191: --
5192: --
5193: select count(*)
5194: into l_total_pop
5195: from wf_notifications
5196: where group_id = Gid;
5197: --
5198: select count(*)
5199: into l_total_voted
5196: where group_id = Gid;
5197: --
5198: select count(*)
5199: into l_total_voted
5200: from wf_notifications
5201: where group_id = Gid
5202: and status = 'CLOSED';
5203: --
5204: select count(*)
5202: and status = 'CLOSED';
5203: --
5204: select count(*)
5205: into l_code_count
5206: from wf_notifications wfn,
5207: wf_notification_attributes wfna
5208: where wfn.group_id = Gid
5209: and wfn.notification_id = wfna.notification_id
5210: and wfn.status = 'CLOSED'
5203: --
5204: select count(*)
5205: into l_code_count
5206: from wf_notifications wfn,
5207: wf_notification_attributes wfna
5208: where wfn.group_id = Gid
5209: and wfn.notification_id = wfna.notification_id
5210: and wfn.status = 'CLOSED'
5211: and wfna.name = 'RESULT'
5240: --
5241: exception
5242: when others then
5243: --
5244: wf_core.context('Wf_Notification', 'VoteCount', to_char(gid), ResultCode );
5245: raise;
5246: --
5247: end VoteCount;
5248: --
5265: select 1
5266: into dummy
5267: from sys.dual
5268: where exists ( select null
5269: from wf_notifications
5270: where group_id = Gid
5271: and status = 'OPEN'
5272: );
5273: --
5279: return(FALSE);
5280: --
5281: when others then
5282: --
5283: wf_core.context('Wf_Notification', 'OpenNotifications', to_char(gid) );
5284: raise;
5285: --
5286: end OpenNotificationsExist;
5287:
5303: colon := instr(username, ':');
5304: if (colon = 0) then
5305: select count(1)
5306: into ncount
5307: from WF_NOTIFICATIONS WN
5308: where WN.RECIPIENT_ROLE in
5309: (select WUR.ROLE_NAME
5310: from WF_USER_ROLES WUR
5311: where WUR.USER_NAME = WorkCount.username)
5312: and WN.STATUS = 'OPEN';
5313: else
5314: select count(1)
5315: into ncount
5316: from WF_NOTIFICATIONS WN
5317: where WN.RECIPIENT_ROLE in
5318: (select WUR.ROLE_NAME
5319: from WF_USER_ROLES WUR
5320: where WUR.USER_ORIG_SYSTEM = substr(WorkCount.username, 1, colon-1)
5325:
5326: return(ncount);
5327: exception
5328: when others then
5329: wf_core.context('Wf_Notification', 'WorkCount', username);
5330: raise;
5331: end WorkCount;
5332:
5333: --
5345:
5346: -- Any existence of response attribute constitutes a response required.
5347: cursor attrs(mnid in number) is
5348: select MA.NAME
5349: from WF_NOTIFICATION_ATTRIBUTES NA,
5350: WF_MESSAGE_ATTRIBUTES_VL MA,
5351: WF_NOTIFICATIONS N
5352: where N.NOTIFICATION_ID = mnid
5353: and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
5347: cursor attrs(mnid in number) is
5348: select MA.NAME
5349: from WF_NOTIFICATION_ATTRIBUTES NA,
5350: WF_MESSAGE_ATTRIBUTES_VL MA,
5351: WF_NOTIFICATIONS N
5352: where N.NOTIFICATION_ID = mnid
5353: and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
5354: and MA.MESSAGE_NAME = N.MESSAGE_NAME
5355: and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
5374: begin
5375:
5376: select N.STATUS, N.LANGUAGE
5377: into close.status, l_language
5378: from WF_NOTIFICATIONS N
5379: where N.NOTIFICATION_ID = nid
5380: for update nowait;
5381: exception
5382: when no_data_found then
5379: where N.NOTIFICATION_ID = nid
5380: for update nowait;
5381: exception
5382: when no_data_found then
5383: wf_core.token('NID', Wf_Notification.GetSubject(nid));
5384: wf_core.raise('WFNTF_NID');
5385: end;
5386:
5387: -- Check notification is open
5385: end;
5386:
5387: -- Check notification is open
5388: if (status <> 'OPEN') then
5389: wf_core.token('NID', Wf_Notification.GetSubject(nid) );
5390: wf_core.raise('WFNTF_NID_OPEN');
5391: end if;
5392:
5393:
5394: open attrs(nid);
5395: fetch attrs into result;
5396: if (attrs%found) then
5397: -- Check response required?
5398: wf_core.token('NID', Wf_Notification.GetSubject(nid));
5399: wf_core.raise('WFNTF_NID_REQUIRE');
5400: end if;
5401:
5402: -- Mark notification closed
5399: wf_core.raise('WFNTF_NID_REQUIRE');
5400: end if;
5401:
5402: -- Mark notification closed
5403: update WF_NOTIFICATIONS
5404: set STATUS = 'CLOSED',
5405: END_DATE = sysdate,
5406: RESPONDER = close.responder
5407: where NOTIFICATION_ID = nid;
5414: -- wf_xml.RemoveNotification(nid);
5415:
5416: --Bug 2283697
5417: --To raise an EVENT whenever DML operation is performed on
5418: --WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
5419: wf_event.AddParameterToList('NOTIFICATION_ID',nid,l_parameterlist);
5420: wf_event.AddParameterToList('RESPONDER',close.responder,l_parameterlist);
5421:
5422: -- AppSearch
5429: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_parameterlist);
5430: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
5431:
5432: --Raise the event
5433: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.close',
5434: p_event_key => to_char(nid),
5435: p_parameters => l_parameterlist);
5436:
5437: exception
5435: p_parameters => l_parameterlist);
5436:
5437: exception
5438: when others then
5439: wf_core.context('Wf_Notification', 'Close', to_char(nid), responder);
5440: raise;
5441: end Close;
5442:
5443: --
5501: -- Process special '#' internal tokens. Supported tokens are:
5502: -- &#NID - Notification id
5503: --
5504: local_text := substrb(replace(local_text, '&'||'#NID',
5505: '['||wf_core.translate('WF_NOTIFICATION_ID')||']'), 1, 1950);
5506:
5507:
5508: return(local_text);
5509:
5603: dbms_lob.write(clob_loc,amt,pos,msg_string);
5604:
5605: exception
5606: when others then
5607: wf_core.context('WF_NOTIFICATION','WriteToClob');
5608: raise;
5609: end WriteToClob;
5610:
5611: --Name : GetFullBody (PUBLIC)
5613: -- and returns the message in 32K chunks in the msgbody out variable.
5614: -- Call this repeatedly until end_of_body is true.
5615: -- Call syntax is
5616: --while not (end_of_msgbody) loop
5617: -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
5618: --end loop;
5619: procedure GetFullBody (nid in number,
5620: msgbody out nocopy varchar2,
5621: end_of_body in out nocopy boolean,
5638: -- if this is the same nid as was just used in this session,
5639: -- and the message is stored as a clob (so clob_exists is not null) then
5640: -- retrieve message from the temp clob.
5641:
5642: if nid = wf_notification.last_nid
5643: and disptype = wf_notification.last_disptype
5644: and wf_notification.clob_exists is not null then
5645: wf_notification.read_Clob(msgbody, end_of_body);
5646: return;
5639: -- and the message is stored as a clob (so clob_exists is not null) then
5640: -- retrieve message from the temp clob.
5641:
5642: if nid = wf_notification.last_nid
5643: and disptype = wf_notification.last_disptype
5644: and wf_notification.clob_exists is not null then
5645: wf_notification.read_Clob(msgbody, end_of_body);
5646: return;
5647: end if;
5640: -- retrieve message from the temp clob.
5641:
5642: if nid = wf_notification.last_nid
5643: and disptype = wf_notification.last_disptype
5644: and wf_notification.clob_exists is not null then
5645: wf_notification.read_Clob(msgbody, end_of_body);
5646: return;
5647: end if;
5648:
5641:
5642: if nid = wf_notification.last_nid
5643: and disptype = wf_notification.last_disptype
5644: and wf_notification.clob_exists is not null then
5645: wf_notification.read_Clob(msgbody, end_of_body);
5646: return;
5647: end if;
5648:
5649: wf_notification.clob_exists :=null;
5645: wf_notification.read_Clob(msgbody, end_of_body);
5646: return;
5647: end if;
5648:
5649: wf_notification.clob_exists :=null;
5650: wf_notification.last_nid:=nid;
5651: wf_notification.last_disptype:=disptype;
5652: plsql_clob_exists:=null;
5653:
5646: return;
5647: end if;
5648:
5649: wf_notification.clob_exists :=null;
5650: wf_notification.last_nid:=nid;
5651: wf_notification.last_disptype:=disptype;
5652: plsql_clob_exists:=null;
5653:
5654: msgbody := wf_notification.getbody(nid,disptype);
5647: end if;
5648:
5649: wf_notification.clob_exists :=null;
5650: wf_notification.last_nid:=nid;
5651: wf_notification.last_disptype:=disptype;
5652: plsql_clob_exists:=null;
5653:
5654: msgbody := wf_notification.getbody(nid,disptype);
5655:
5650: wf_notification.last_nid:=nid;
5651: wf_notification.last_disptype:=disptype;
5652: plsql_clob_exists:=null;
5653:
5654: msgbody := wf_notification.getbody(nid,disptype);
5655:
5656: if msgbody is null
5657: or instr(msgbody,'&') = 0
5658: or plsql_clob_exists is null then
5662: else
5663:
5664: strt:=1;
5665:
5666: wf_notification.newclob(wf_notification.temp_clob,null);
5667: wf_notification.clob_exists :=1;
5668:
5669: loop
5670:
5663:
5664: strt:=1;
5665:
5666: wf_notification.newclob(wf_notification.temp_clob,null);
5667: wf_notification.clob_exists :=1;
5668:
5669: loop
5670:
5671: attr_name := null;
5674:
5675: if ampersand = 0 then
5676: if strt <= length(msgbody) then
5677:
5678: wf_notification.WriteToClob(wf_notification.temp_clob,
5679: substr(msgbody,strt,length(msgbody)));
5680: end if;
5681: exit;
5682: end if;
5685: -- would encounter an error in our logic. 1 - 1 is 0 so the call to
5686: -- substr would fail.
5687:
5688: if ((ampersand - strt) > 0) then
5689: wf_notification.writeToClob(wf_notification.temp_clob,
5690: substr(msgbody,strt,ampersand-strt));
5691:
5692: end if;
5693:
5694: -- 2691290 if the '&' is at the end of the body the notification
5695: -- will error when calling GetAttrClob API for "Invalid values for
5696: -- Arguments".
5697: if (substr(msgbody,ampersand+1,30) is not null) then
5698: wf_notification.getattrclob(nid,substr(msgbody,ampersand+1,30),disptype,
5699: wf_notification.temp_clob , attr_name);
5700: end if;
5701:
5702:
5695: -- will error when calling GetAttrClob API for "Invalid values for
5696: -- Arguments".
5697: if (substr(msgbody,ampersand+1,30) is not null) then
5698: wf_notification.getattrclob(nid,substr(msgbody,ampersand+1,30),disptype,
5699: wf_notification.temp_clob , attr_name);
5700: end if;
5701:
5702:
5703: if attr_name is not null then
5704: --it was already written to clob.
5705: strt := ampersand + 1 + length(attr_name);
5706: else
5707: --the string was not a plsqlclob
5708: wf_notification.writeToClob(wf_notification.temp_clob,'&');
5709: strt := ampersand + 1;
5710: end if;
5711:
5712: end loop;
5712: end loop;
5713:
5714:
5715: --set the clob chunk to zero. then request the next chunk in the msgbody
5716: wf_notification.clob_chunk := 0;
5717: wf_notification.read_Clob(msgbody, end_of_body);
5718:
5719: end if;
5720:
5713:
5714:
5715: --set the clob chunk to zero. then request the next chunk in the msgbody
5716: wf_notification.clob_chunk := 0;
5717: wf_notification.read_Clob(msgbody, end_of_body);
5718:
5719: end if;
5720:
5721: exception
5719: end if;
5720:
5721: exception
5722: when others then
5723: wf_core.context('WF_NOTIFICATION','GetFullBody', 'nid => '||to_char(nid),
5724: 'disptype => '||disptype);
5725: raise;
5726: end GetFullBody;
5727:
5732: -- Call this repeatedly until end_of_body is "Y". Uses string arg
5733: -- instead of boolean like GetFullBody for end_of_msg_body
5734: -- Call syntax is
5735: --while (end_of_msgbody <> 'Y') loop
5736: -- wf_notification.getfullbody(nid,msgbody,end_of_msgbody);
5737: --end loop;
5738: procedure GetFullBodyWrapper (nid in number,
5739: msgbody out nocopy varchar2,
5740: end_of_body out nocopy varchar2,
5742: is
5743: end_of_body_b boolean;
5744: begin
5745: end_of_body_b := FALSE;
5746: WF_Notification.GetFullBody(nid, msgbody, end_of_body_b, disptype);
5747: if (end_of_body_b = TRUE) then
5748: end_of_body := 'Y';
5749: else
5750: end_of_body := 'N';
5764: -- IN:
5765: -- nid - Notification id
5766: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5767: -- disptype - Requested display type. Valid values:
5768: -- wf_notification.doc_text - 'text/plain'
5769: -- wf_notification.doc_html - 'text/html'
5770: -- document - The clob into which
5771: -- aname - Attribute Name (the first part of the string that matches
5772: -- the attr list)
5765: -- nid - Notification id
5766: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5767: -- disptype - Requested display type. Valid values:
5768: -- wf_notification.doc_text - 'text/plain'
5769: -- wf_notification.doc_html - 'text/html'
5770: -- document - The clob into which
5771: -- aname - Attribute Name (the first part of the string that matches
5772: -- the attr list)
5773: --
5780: is
5781: doctype varchar2(500);
5782: begin
5783:
5784: Wf_Notification.GetAttrClob(nid, astring, disptype, document, doctype, aname);
5785:
5786: exception
5787: when others then
5788: wf_core.context('Wf_Notification', 'oldGetAttrClob', to_char(nid), aname,
5784: Wf_Notification.GetAttrClob(nid, astring, disptype, document, doctype, aname);
5785:
5786: exception
5787: when others then
5788: wf_core.context('Wf_Notification', 'oldGetAttrClob', to_char(nid), aname,
5789: disptype);
5790: raise;
5791: end GetAttrClob;
5792:
5801: -- IN:
5802: -- nid - Notification id
5803: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5804: -- disptype - Requested display type. Valid values:
5805: -- wf_notification.doc_text - 'text/plain'
5806: -- wf_notification.doc_html - 'text/html'
5807: -- document - Th clob into which
5808: -- aname - Attribute Name (the string that matches
5809: -- the attr list)
5802: -- nid - Notification id
5803: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
5804: -- disptype - Requested display type. Valid values:
5805: -- wf_notification.doc_text - 'text/plain'
5806: -- wf_notification.doc_html - 'text/html'
5807: -- document - Th clob into which
5808: -- aname - Attribute Name (the string that matches
5809: -- the attr list)
5810: --
5836: begin
5837:
5838: -- Check args
5839: if ((nid is null) or (astring is null) or
5840: (disptype not in (wf_notification.doc_text,
5841: wf_notification.doc_html))) then
5842: wf_core.token('NID', to_char(nid));
5843: wf_core.token('ASTRING', aname);
5844: wf_core.token('DISPTYPE', disptype);
5837:
5838: -- Check args
5839: if ((nid is null) or (astring is null) or
5840: (disptype not in (wf_notification.doc_text,
5841: wf_notification.doc_html))) then
5842: wf_core.token('NID', to_char(nid));
5843: wf_core.token('ASTRING', aname);
5844: wf_core.token('DISPTYPE', disptype);
5845: wf_core.raise('WFSQL_ARGS');
5853: begin
5854: -- <7443088> improved query
5855: select NAME into aname from
5856: (select WMA.NAME
5857: from WF_NOTIFICATIONS WN,
5858: WF_MESSAGE_ATTRIBUTES WMA,
5859: WF_NOTIFICATION_ATTRIBUTES NA
5860: where WN.NOTIFICATION_ID = nid
5861: and wn.notification_id = na.notification_id
5855: select NAME into aname from
5856: (select WMA.NAME
5857: from WF_NOTIFICATIONS WN,
5858: WF_MESSAGE_ATTRIBUTES WMA,
5859: WF_NOTIFICATION_ATTRIBUTES NA
5860: where WN.NOTIFICATION_ID = nid
5861: and wn.notification_id = na.notification_id
5862: and wma.name = na.name
5863: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
5874: end;
5875:
5876: if (aname is not null) then
5877: -- Retrieve key string
5878: key := wf_notification.GetAttrText(nid, aname);
5879:
5880: -- If the key is empty then return a null string
5881: if (key is not null) then
5882:
5909:
5910: if (procarg is null) then
5911: procarg := NULL;
5912: else
5913: procarg := Wf_Notification.GetTextInternal(procarg, nid, target,
5914: FALSE, FALSE, 'text/plain');
5915: end if;
5916:
5917: -- ### Review Note 1
5914: FALSE, FALSE, 'text/plain');
5915: end if;
5916:
5917: -- ### Review Note 1
5918: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
5919: --Throw the Illegal exception when the check fails
5920:
5921: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5922: wf_log_pkg.string2(wf_log_pkg.level_statement,
5919: --Throw the Illegal exception when the check fails
5920:
5921: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5922: wf_log_pkg.string2(wf_log_pkg.level_statement,
5923: 'wf.plsql.wf_notification.GetAttrClob.plsqlclob_callout',
5924: 'Start executing PLSQLCLOB Doc procedure - '||procname, true);
5925: end if;
5926:
5927: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
5932: in out doctype;
5933:
5934: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
5935: wf_log_pkg.string2(wf_log_pkg.level_statement,
5936: 'wf.plsql.wf_notification.GetAttrClob.plsqlclob_callout',
5937: 'End executing PLSQLCLOB Doc procedure - '||procname, false);
5938: end if;
5939:
5940: /* curs := dbms_sql.open_cursor;
5947: -- NOTE: There is a slight chance of recursive loop here,
5948: -- if the substituted string eventually contains a reference
5949: -- back to this same docattr.
5950:
5951: procarg := Wf_Notification.GetTextInternal(procarg, nid, target, FALSE,
5952: FALSE);
5953:
5954: sqlbuf := 'begin '||procname||'('''||procarg||''', '''||disptype||
5955: ''',:document, :doctype); end;';
5964: dbms_sql.close_cursor(curs);
5965:
5966: -- Translate doc types if needed
5967: Dont do this anymore. The plsql must take care of it.
5968: if ((disptype = wf_notification.doc_html) and
5969: (doctype = wf_notification.doc_text)) then
5970: -- Change plain text to html by wrapping in preformatted tags
5971: return('
'||document||'
5965:
5966: -- Translate doc types if needed
5967: Dont do this anymore. The plsql must take care of it.
5968: if ((disptype = wf_notification.doc_html) and
5969: (doctype = wf_notification.doc_text)) then
5970: -- Change plain text to html by wrapping in preformatted tags
5971: return('
'||document||'
5978: /* if (dbms_sql.is_open(curs)) then
5979: dbms_sql.close_cursor(curs);
5980: end if; */
5981:
5982: wf_core.context('Wf_Notification', 'GetAttrClob', to_char(nid), aname,
5983: disptype);
5984: raise;
5985: end GetAttrClob;
5986:
6022: end if;
6023:
6024: exception
6025: when others then
6026: wf_core.context('WF_NOTIFICATION','NewClob');
6027: raise;
6028: end NewClob;
6029:
6030: --Name Read_Clob
6038: begin
6039:
6040: --linenbr is always one before the line to print.
6041: --it is incremented afterwards.
6042: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
6043:
6044: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
6045:
6046: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
6040: --linenbr is always one before the line to print.
6041: --it is incremented afterwards.
6042: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
6043:
6044: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
6045:
6046: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
6047: end_of_clob := TRUE;
6048: wf_notification.clob_chunk := 0;
6042: pos:=(buff_length * nvl(wf_notification.clob_chunk,0) ) +1;
6043:
6044: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
6045:
6046: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
6047: end_of_clob := TRUE;
6048: wf_notification.clob_chunk := 0;
6049: else
6050: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
6044: dbms_lob.read(wf_notification.temp_clob,buff_length,pos,line);
6045:
6046: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
6047: end_of_clob := TRUE;
6048: wf_notification.clob_chunk := 0;
6049: else
6050: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
6051: end if;
6052:
6046: if pos+buff_length > dbms_lob.getlength(wf_notification.temp_clob) then
6047: end_of_clob := TRUE;
6048: wf_notification.clob_chunk := 0;
6049: else
6050: wf_notification.clob_chunk := wf_notification.clob_chunk +1;
6051: end if;
6052:
6053: exception
6054: when others then
6051: end if;
6052:
6053: exception
6054: when others then
6055: wf_core.context('Wf_Notification', 'Read_Clob','pos => '||to_char(pos),
6056: 'line => {'||line||'}');
6057: raise;
6058: end Read_Clob;
6059:
6070: end_of_text in out nocopy boolean) is
6071: clob_id number;
6072: attr_name varchar2(30);
6073: begin
6074: if nid = wf_notification.last_nid
6075: and wf_notification.clob_exists is not null then
6076: wf_notification.read_Clob(doctext, end_of_text);
6077: else
6078: --create a clob
6071: clob_id number;
6072: attr_name varchar2(30);
6073: begin
6074: if nid = wf_notification.last_nid
6075: and wf_notification.clob_exists is not null then
6076: wf_notification.read_Clob(doctext, end_of_text);
6077: else
6078: --create a clob
6079: wf_notification.newclob(wf_notification.temp_clob,null);
6072: attr_name varchar2(30);
6073: begin
6074: if nid = wf_notification.last_nid
6075: and wf_notification.clob_exists is not null then
6076: wf_notification.read_Clob(doctext, end_of_text);
6077: else
6078: --create a clob
6079: wf_notification.newclob(wf_notification.temp_clob,null);
6080: wf_notification.clob_exists :=1;
6075: and wf_notification.clob_exists is not null then
6076: wf_notification.read_Clob(doctext, end_of_text);
6077: else
6078: --create a clob
6079: wf_notification.newclob(wf_notification.temp_clob,null);
6080: wf_notification.clob_exists :=1;
6081:
6082: --set the clob text
6083: wf_notification.getattrclob(nid, aname,
6076: wf_notification.read_Clob(doctext, end_of_text);
6077: else
6078: --create a clob
6079: wf_notification.newclob(wf_notification.temp_clob,null);
6080: wf_notification.clob_exists :=1;
6081:
6082: --set the clob text
6083: wf_notification.getattrclob(nid, aname,
6084: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
6079: wf_notification.newclob(wf_notification.temp_clob,null);
6080: wf_notification.clob_exists :=1;
6081:
6082: --set the clob text
6083: wf_notification.getattrclob(nid, aname,
6084: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
6085:
6086: --retreive all the clob text in 32K chunks.
6087: if attr_name = aname then
6080: wf_notification.clob_exists :=1;
6081:
6082: --set the clob text
6083: wf_notification.getattrclob(nid, aname,
6084: wf_notification.doc_html, wf_notification.temp_clob , attr_name);
6085:
6086: --retreive all the clob text in 32K chunks.
6087: if attr_name = aname then
6088: -- the attribute was substituted with something in the clob so print it.
6085:
6086: --retreive all the clob text in 32K chunks.
6087: if attr_name = aname then
6088: -- the attribute was substituted with something in the clob so print it.
6089: wf_notification.clob_chunk := 0;
6090: wf_notification.read_Clob(doctext, end_of_text);
6091:
6092: else
6093: --the aname was not substituted so just print it.
6086: --retreive all the clob text in 32K chunks.
6087: if attr_name = aname then
6088: -- the attribute was substituted with something in the clob so print it.
6089: wf_notification.clob_chunk := 0;
6090: wf_notification.read_Clob(doctext, end_of_text);
6091:
6092: else
6093: --the aname was not substituted so just print it.
6094: doctext := aname;
6094: doctext := aname;
6095: end if;
6096:
6097: --finally set the global vars
6098: wf_notification.last_nid:=nid;
6099:
6100:
6101: end if;
6102:
6101: end if;
6102:
6103: exception
6104: when others then
6105: wf_core.context('Wf_Notification', 'ReadAttrClob');
6106: raise;
6107: end ReadAttrClob;
6108:
6109: --
6121: l_lang varchar2(64);
6122: l_terr varchar2(64);
6123: l_chrs varchar2(64);
6124: begin
6125: -- <7514495> no cached variable in Wf_Notification now?
6126: -- if (Wf_Notification.nls_language is not null) then
6127: -- return Wf_Notification.nls_language;
6128: -- end if;
6129:
6122: l_terr varchar2(64);
6123: l_chrs varchar2(64);
6124: begin
6125: -- <7514495> no cached variable in Wf_Notification now?
6126: -- if (Wf_Notification.nls_language is not null) then
6127: -- return Wf_Notification.nls_language;
6128: -- end if;
6129:
6130: GetNLSLanguage(l_lang, l_terr, l_chrs);
6123: l_chrs varchar2(64);
6124: begin
6125: -- <7514495> no cached variable in Wf_Notification now?
6126: -- if (Wf_Notification.nls_language is not null) then
6127: -- return Wf_Notification.nls_language;
6128: -- end if;
6129:
6130: GetNLSLanguage(l_lang, l_terr, l_chrs);
6131: return l_lang;
6154: l_nlsCalendar varchar2(64);
6155:
6156: begin
6157: -- <7514495> now uses centralized api
6158: -- if (Wf_Notification.nls_language is null) then
6159: -- tmpbuf := userenv('LANGUAGE');
6160: -- pos1 := instr(tmpbuf, '_');
6161: -- pos2 := instr(tmpbuf, '.');
6162: --
6159: -- tmpbuf := userenv('LANGUAGE');
6160: -- pos1 := instr(tmpbuf, '_');
6161: -- pos2 := instr(tmpbuf, '.');
6162: --
6163: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
6164: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
6165: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
6166: -- end if;
6167: --
6160: -- pos1 := instr(tmpbuf, '_');
6161: -- pos2 := instr(tmpbuf, '.');
6162: --
6163: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
6164: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
6165: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
6166: -- end if;
6167: --
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6161: -- pos2 := instr(tmpbuf, '.');
6162: --
6163: -- Wf_Notification.nls_language := substr(tmpbuf, 1, pos1-1);
6164: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
6165: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
6166: -- end if;
6167: --
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6169: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6164: -- Wf_Notification.nls_territory := substr(tmpbuf, pos1+1, pos2-pos1-1);
6165: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
6166: -- end if;
6167: --
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6169: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6170: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6171:
6172: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6165: -- Wf_Notification.nls_charset := substr(tmpbuf, pos2+1);
6166: -- end if;
6167: --
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6169: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6170: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6171:
6172: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6173: p_nlsTerritory => GetNLSLanguage.territory,
6166: -- end if;
6167: --
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6169: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6170: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6171:
6172: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6173: p_nlsTerritory => GetNLSLanguage.territory,
6174: p_nlsCode => GetNLSLanguage.charset,
6168: -- GetNLSLanguage.language := Wf_Notification.nls_language;
6169: -- GetNLSLanguage.territory := Wf_Notification.nls_territory;
6170: -- GetNLSLanguage.charset := Wf_Notification.nls_charset;
6171:
6172: wf_notification_util.getNLSContext( p_nlsLanguage=> GetNLSLanguage.language,
6173: p_nlsTerritory => GetNLSLanguage.territory,
6174: p_nlsCode => GetNLSLanguage.charset,
6175: p_nlsDateFormat => l_nlsDateFormat,
6176: p_nlsDateLanguage => l_nlsDateLang,
6192: l_language varchar2(30);
6193: l_territory varchar2(30);
6194: begin
6195: -- <7514495> now we use centralized api
6196: -- if (p_language = Wf_Notification.nls_language) then
6197: -- return;
6198: -- end if;
6199: --
6200: -- l_language := ''''||p_language||'''';
6203: -- DBMS_SESSION.SET_NLS('NLS_LANGUAGE', l_language);
6204: -- DBMS_SESSION.SET_NLS('NLS_TERRITORY', l_territory);
6205: --
6206: -- -- update cache
6207: -- Wf_Notification.nls_language := p_language;
6208: -- Wf_Notification.nls_territory := p_territory;
6209:
6210: wf_notification_util.SetNLSContext( -- p_nid ,
6211: p_nlsLanguage => l_language,
6204: -- DBMS_SESSION.SET_NLS('NLS_TERRITORY', l_territory);
6205: --
6206: -- -- update cache
6207: -- Wf_Notification.nls_language := p_language;
6208: -- Wf_Notification.nls_territory := p_territory;
6209:
6210: wf_notification_util.SetNLSContext( -- p_nid ,
6211: p_nlsLanguage => l_language,
6212: p_nlsTerritory => p_territory
6206: -- -- update cache
6207: -- Wf_Notification.nls_language := p_language;
6208: -- Wf_Notification.nls_territory := p_territory;
6209:
6210: wf_notification_util.SetNLSContext( -- p_nid ,
6211: p_nlsLanguage => l_language,
6212: p_nlsTerritory => p_territory
6213: -- ok not to pass next parameters
6214: -- as fnd_global.set_nls_context won't set
6220: -- p_nlsCalendar
6221: );
6222: exception
6223: when others then
6224: Wf_Core.Context('Wf_Notification', 'SetNLSLanguage', p_language, p_territory);
6225: raise;
6226: end SetNLSLanguage;
6227:
6228: --
6226: end SetNLSLanguage;
6227:
6228: --
6229: -- Denormalize_Notification
6230: -- Populate the donormalized value to WF_NOTIFICATIONS table according
6231: -- to the language setting of username provided.
6232: -- IN:
6233: -- nid - Notification id
6234: -- username - optional role name, if not provided, use the
6282: , 'BEGIN, nid='||nid||', username='||username||', langcode='||langcode);
6283: end if;
6284:
6285: -- 8286459. Get value, and always reset flag;
6286: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6287: wf_notification_util.g_allowDeferDenormalize := true;
6288:
6289: -- <7720908>
6290: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6283: end if;
6284:
6285: -- 8286459. Get value, and always reset flag;
6286: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6287: wf_notification_util.g_allowDeferDenormalize := true;
6288:
6289: -- <7720908>
6290: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6291: , l_orig_nlsDateFormat, l_orig_nlsDateLang
6286: l_canDefer := wf_notification_util.g_allowDeferDenormalize;
6287: wf_notification_util.g_allowDeferDenormalize := true;
6288:
6289: -- <7720908>
6290: wf_notification_util.getNLSContext( l_orig_lang, l_orig_nlsterritory, l_orig_nlsCode
6291: , l_orig_nlsDateFormat, l_orig_nlsDateLang
6292: , l_orig_nlsNumChars, l_orig_nlsSort, l_orig_nlsCalendar);
6293:
6294: if (l_orig_nlsCalendar is null) then
6363:
6364: begin
6365: select RECIPIENT_ROLE, FROM_ROLE
6366: into l_user, l_from_role
6367: from WF_NOTIFICATIONS
6368: where NOTIFICATION_ID = nid;
6369: exception
6370: when NO_DATA_FOUND then
6371: wf_core.token('NID', to_char(nid));
6449: wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, l_module, 'END - deferring denormalization');
6450: end if;
6451:
6452: -- Raise the event
6453: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.denormalize',
6454: p_event_key => to_char(nid),
6455: p_parameters => l_parameterlist);
6456:
6457: return;
6472: -- If FROM_ROLE has not been defined yet, we tried to draw this from
6473: -- #FROM_ROLE.
6474: if (l_from_role is NULL) then
6475: begin
6476: l_from_role := Wf_Notification.GetAttrText(nid, '#FROM_ROLE');
6477: exception
6478: when OTHERS then
6479: wf_core.clear; -- clear the error stack
6480: l_from_role := NULL;
6489:
6490:
6491: -- Subject
6492: -- skilaru 08-MAY-03 bug fix 2883247
6493: l_subject := Wf_Notification.GetSubject(nid, 'text/plain');
6494:
6495: if ( l_logSTMT ) then
6496: wf_log_pkg.String(wf_log_pkg.LEVEL_STATEMENT, l_module, 'subject('||l_subject||')');
6497: end if;
6497: end if;
6498: -- Populate the notification values
6499: --
6500: begin
6501: update WF_NOTIFICATIONS
6502: set FROM_USER = l_from_user,
6503: FROM_ROLE = nvl(l_from_role,FROM_ROLE),
6504: TO_USER = l_to_user,
6505: SUBJECT = l_subject,
6515: wf_log_pkg.String(wf_log_pkg.LEVEL_PROCEDURE, l_module, 'END');
6516: end if;
6517: exception
6518: when OTHERS then
6519: wf_core.context('Wf_Notification', 'Denormalize_Notification',
6520: to_char(nid), username);
6521: raise;
6522: end Denormalize_Notification;
6523:
6537: xmessageName varchar2(30);
6538:
6539: cursor fyiNid is
6540: select WN.NOTIFICATION_ID
6541: from WF_NOTIFICATIONS WN
6542: where MESSAGE_TYPE like xitemtype
6543: and MESSAGE_NAME like xmessageName
6544: and BEGIN_DATE<=begindate
6545: and STATUS = 'OPEN'
6557: xmessageName := nvl(messageName, '%');
6558:
6559: for c in fyiNid LOOP
6560:
6561: WF_NOTIFICATION.Close( c.NOTIFICATION_ID );
6562:
6563: end loop;
6564:
6565: end closeFYI;
6594: begin
6595: -- get the signature policy for the notification
6596: -- wf_mail.GetSignaturePolicy(nid, sig_policy);
6597: begin
6598: sig_policy := Wf_Notification.GetAttrText(nid, '#WF_SIG_POLICY');
6599: exception
6600: when others then
6601: if (wf_core.error_name = 'WFNTF_ATTR') then
6602: wf_core.clear;
6615: Elsif(sig_required = 'Y') then
6616:
6617: -- bug 2779748: Cancelled Notification does not need to be signed
6618: begin
6619: if (WF_Notification.GetAttrText(nid, 'RESULT') = '#SIG_CANCEL') then
6620: return TRUE;
6621: end if;
6622: exception
6623: when others then
6631: sig_id := WF_DIG_SIG_EVIDENCE_STORE.GetMostRecentSigID('WF_NTF', nid);
6632: begin
6633: -- #WF_SIG_ID may be defined as text or number... Now both will work
6634: -- Eventually should use GetAttrNumber
6635: l_attr_sigid := to_number(Wf_Notification.GetAttrText(nid, '#WF_SIG_ID'));
6636: exception
6637: when others then
6638: if (wf_core.error_name = 'WFNTF_ATTR') then
6639: wf_core.clear;
6659: return TRUE;
6660:
6661: exception
6662: when others then
6663: wf_core.context('Wf_Notification', 'NtfSignRequirementsMet', to_char(nid));
6664: raise;
6665: end NtfSignRequirementsMet;
6666:
6667: --
6769: SELECT callback , context ,RECIPIENT_ROLE, ORIGINAL_RECIPIENT,
6770: MORE_INFO_ROLE ,from_role, message_type, message_name
6771: into cb, context,l_recip_role , l_orig_recip_role,
6772: l_more_info_role, l_from_role, l_messageType, l_messageName
6773: FROM wf_notifications
6774: WHERE notification_id = nid;
6775:
6776: g_context_recipient_role := l_recip_role;
6777: g_context_original_recipient:= l_orig_recip_role;
6816: wf_core.token('ROLE', WF_Directory.GetRoleDisplayName(l_newRole));
6817: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE_TO_ROLE');
6818:
6819: if myusername is not null then
6820: wf_notification.SetComments(nid, username, myusername, 'ANSWER', action_source, l_sysComment);
6821: else
6822: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', action_source, l_sysComment);
6823: end if;
6824:
6818:
6819: if myusername is not null then
6820: wf_notification.SetComments(nid, username, myusername, 'ANSWER', action_source, l_sysComment);
6821: else
6822: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', action_source, l_sysComment);
6823: end if;
6824:
6825: /* implement the above loop recursively */
6826: if (cnt > wf_notification.max_forward) then
6822: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', action_source, l_sysComment);
6823: end if;
6824:
6825: /* implement the above loop recursively */
6826: if (cnt > wf_notification.max_forward) then
6827: -- it means max_forward must have been exceeded. Treat as a loop error.
6828: wf_core.token('NID', to_char(nid));
6829: wf_core.raise('WFNTF_ROUTE_LOOP');
6830: end if;
6834:
6835: if l_ruleAction = 'RESPOND' then
6836: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE');
6837: if myusername is not null then
6838: wf_notification.SetComments(nid, username, myusername, 'ANSWER', action_source, l_sysComment);
6839: else
6840: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', action_source, l_sysComment);
6841: end if;
6842: end if;
6836: l_sysComment := wf_core.translate('WFNTF_AUTO_RESPONSE');
6837: if myusername is not null then
6838: wf_notification.SetComments(nid, username, myusername, 'ANSWER', action_source, l_sysComment);
6839: else
6840: wf_notification.SetComments(nid, username, l_recip_role, 'ANSWER', action_source, l_sysComment);
6841: end if;
6842: end if;
6843: -- do not want it hung when some one is doing update.
6844: begin
6843: -- do not want it hung when some one is doing update.
6844: begin
6845: select MORE_INFO_ROLE
6846: into l_from_role
6847: from WF_NOTIFICATIONS
6848: where NOTIFICATION_ID = nid
6849: for update nowait;
6850: exception
6851: when NO_DATA_FOUND then
6860: tvalue := myusername;
6861: nvalue := nid;
6862: -- ### Review Note 2 - cb is from table
6863: --Check for bug#3827935
6864: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
6865: --Throw the Illegal exception when the check fails
6866:
6867:
6868: -- BINDVAR_SCAN_IGNORE
6885: -- if (IsValidInfoRole(nid,username)) then
6886:
6887: -- shanjgik 01-JUL-03 bug 2887130
6888: -- get mail preference of the user who will respond with more information
6889: mailpref := wf_notification.GetMailPreference (username, null, null);
6890:
6891: -- if there is a valid session, then we can update the FROM_ROLE
6892: -- and FROM_USER accurately.
6893: if (myusername is not null) then
6890:
6891: -- if there is a valid session, then we can update the FROM_ROLE
6892: -- and FROM_USER accurately.
6893: if (myusername is not null) then
6894: update WF_NOTIFICATIONS
6895: set MORE_INFO_ROLE = username,
6896: FROM_USER = mydispname,
6897: FROM_ROLE = myusername,
6898: MAIL_STATUS = decode (mailpref, 'QUERY', '',
6904:
6905: -- otherwise, we default to what it should be. Unfortunately, if it
6906: -- is a group role, we will not be able to identify which member I am.
6907: else
6908: update WF_NOTIFICATIONS
6909: set MORE_INFO_ROLE = username,
6910: FROM_USER = TO_USER,
6911: FROM_ROLE = RECIPIENT_ROLE,
6912: MAIL_STATUS = decode (mailpref, 'QUERY', '',
6916: null, '', 'MAIL')
6917: where NOTIFICATION_ID = nid;
6918: end if;
6919:
6920: Wf_Notification.SetComments(nid, myusername, username, 'QUESTION', action_source, substrb(comment,1,4000));
6921:
6922: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
6923: -- insert into WF_COMMENTS (
6924: -- NOTIFICATION_ID,
6918: end if;
6919:
6920: Wf_Notification.SetComments(nid, myusername, username, 'QUESTION', action_source, substrb(comment,1,4000));
6921:
6922: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
6923: -- insert into WF_COMMENTS (
6924: -- NOTIFICATION_ID,
6925: -- FROM_ROLE,
6926: -- FROM_USER,
6935: -- sysdate,
6936: -- 'QUESTION',
6937: -- substrb(comment,1,4000),
6938: -- LANGUAGE
6939: -- from WF_NOTIFICATIONS
6940: -- where NOTIFICATION_ID = nid;
6941:
6942: -- bug 2474562
6943: -- else
6947: end if;
6948: end if;
6949:
6950: -- if we are here, mean we are going to raise
6951: -- oracle.apps.wf.notification.question event.
6952: l_event_name := 'oracle.apps.wf.notification.question';
6953:
6954:
6955: -- answer mode
6948: end if;
6949:
6950: -- if we are here, mean we are going to raise
6951: -- oracle.apps.wf.notification.question event.
6952: l_event_name := 'oracle.apps.wf.notification.question';
6953:
6954:
6955: -- answer mode
6956: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
6962: -- answer at the same time.
6963: begin
6964: select MORE_INFO_ROLE,Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE), RECIPIENT_ROLE,FROM_ROLE
6965: into l_from_role, replyby, recipient_role,l_question_role
6966: from WF_NOTIFICATIONS
6967: where NOTIFICATION_ID = nid
6968: and MORE_INFO_ROLE is not null
6969: for update nowait;
6970:
6995: nvalue := nid;
6996: g_context_new_role := l_question_role;
6997: -- ### Review Note 2 - cb is from table
6998: -- Check for bug#3827935
6999: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
7000: --Throw the Illegal exception when the check fails
7001:
7002: -- BINDVAR_SCAN_IGNORE
7003: sqlbuf := 'begin '||cb||
7014: end if;
7015:
7016: -- shanjgik 01-JUL-03 bug 2887130
7017: -- get the recipient's(one who requested more information) mail preference
7018: mailpref := wf_notification.GetMailPreference (recipient_role, null, null);
7019:
7020: update WF_NOTIFICATIONS
7021: set FROM_USER = replyby,
7022: FROM_ROLE = l_from_role,
7016: -- shanjgik 01-JUL-03 bug 2887130
7017: -- get the recipient's(one who requested more information) mail preference
7018: mailpref := wf_notification.GetMailPreference (recipient_role, null, null);
7019:
7020: update WF_NOTIFICATIONS
7021: set FROM_USER = replyby,
7022: FROM_ROLE = l_from_role,
7023: MORE_INFO_ROLE = null,
7024: MAIL_STATUS = decode (mailpref, 'QUERY', '',
7027: 'DISABLED', 'FAILED',
7028: null, '', 'MAIL')
7029: where NOTIFICATION_ID = nid;
7030:
7031: Wf_Notification.SetComments(nid, l_from_role, recipient_role, 'ANSWER', action_source, substrb(comment,1,4000));
7032: Wf_Notification.Route(nid, 0);
7033:
7034: -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above,
7035: -- so the LANGUAGE should be current userenv('LANG').
7028: null, '', 'MAIL')
7029: where NOTIFICATION_ID = nid;
7030:
7031: Wf_Notification.SetComments(nid, l_from_role, recipient_role, 'ANSWER', action_source, substrb(comment,1,4000));
7032: Wf_Notification.Route(nid, 0);
7033:
7034: -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above,
7035: -- so the LANGUAGE should be current userenv('LANG').
7036: -- insert into WF_COMMENTS (
7048: -- sysdate,
7049: -- 'ANSWER',
7050: -- substrb(comment,1,4000),
7051: -- userenv('LANG')
7052: -- from WF_NOTIFICATIONS
7053: -- where NOTIFICATION_ID = nid;
7054:
7055: -- <
7056: -- if we are here, mean we are going to raise
7053: -- where NOTIFICATION_ID = nid;
7054:
7055: -- <
7056: -- if we are here, mean we are going to raise
7057: -- oracle.apps.wf.notification.answer event.
7058: l_event_name := 'oracle.apps.wf.notification.answer';
7059:
7060: end if;
7061:
7054:
7055: -- <
7056: -- if we are here, mean we are going to raise
7057: -- oracle.apps.wf.notification.answer event.
7058: l_event_name := 'oracle.apps.wf.notification.answer';
7059:
7060: end if;
7061:
7062: -- Send the notification through email
7063: -- wf_xml.EnqueueNotification(nid);
7064:
7065: -- Bug 2283697
7066: -- To raise an EVENT whenever DML operation is performed on
7067: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7068: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7069: wf_event.AddParameterToList('ROLE', username, l_parameterlist);
7070: wf_event.AddParameterToList('GROUP_ID', nvl(l_groupId, nid), l_parameterlist);
7071: wf_event.addParameterToList('Q_CORRELATION_ID', l_messageType||':'||
7088: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
7089:
7090:
7091: -- Raise the event
7092: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
7093: -- p_event_key => to_char(nid),
7094: -- p_parameters => l_parameterlist);
7095:
7096: -- <
7099: p_parameters => l_parameterlist);
7100:
7101: exception
7102: when OTHERS then
7103: Wf_Core.Context('Wf_Notification', 'UpdateInfo', to_char(nid), username, wl_user, action_source);
7104: raise;
7105: end UpdateInfo;
7106:
7107: -- bug 2474562
7136: -- Notification only
7137: begin
7138: select NULL, '#SYNCH'
7139: into itype, ikey
7140: from WF_NOTIFICATIONS
7141: where NOTIFICATION_ID = nid;
7142: exception
7143: when OTHERS then
7144: return(FALSE);
7167: if (itype is null and ikey = '#SYNCH') then
7168: -- this is notification only
7169: begin
7170: select 1 into ans
7171: from WF_NOTIFICATIONS
7172: where IsValidInfoRole.username in (RECIPIENT_ROLE, ORIGINAL_RECIPIENT)
7173: and NOTIFICATION_ID = nid;
7174: exception
7175: when NO_DATA_FOUND then
7187: select 1 into ans
7188: from (
7189: select /*+ leading(grp_id_view) */
7190: RECIPIENT_ROLE , ORIGINAL_RECIPIENT
7191: from WF_NOTIFICATIONS a ,
7192: ( select notification_id group_id
7193: from WF_ITEM_ACTIVITY_STATUSES
7194: where item_type = itype
7195: and item_key = ikey
7211: end if;
7212: return(TRUE);
7213: exception
7214: when OTHERS then
7215: Wf_Core.Context('Wf_Notification','IsValidInfoRole',to_char(nid),username);
7216: raise;
7217: end IsValidInfoRole;
7218:
7219: -- UpdateInfo2 - bug 2282139
7267:
7268: begin
7269: if (wf_log_pkg.level_procedure >= fnd_log.g_current_runtime_level) then
7270: wf_log_pkg.string(wf_log_pkg.level_procedure,
7271: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.Begin',
7272: 'NID: '||to_char(nid) ||', Username: '||username||
7273: ' From: '||from_email);
7274: end if;
7275:
7277: SELECT callback, context, recipient_role, original_recipient,
7278: more_info_role, from_role, status, message_type, message_name
7279: INTO cb, context, l_recip_role, l_orig_recip_role,
7280: l_more_info_role, l_from_role, l_stat, l_messageType, l_messageName
7281: FROM wf_notifications
7282: WHERE notification_id = nid;
7283:
7284: -- Donot process the request if the notification is not open.
7285: if (l_stat <> 'OPEN') then
7284: -- Donot process the request if the notification is not open.
7285: if (l_stat <> 'OPEN') then
7286: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7287: wf_log_pkg.string(wf_log_pkg.level_statement,
7288: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.not_open',
7289: 'Notification '||to_char(nid)||' is not OPEN. Returning.');
7290: end if;
7291: return;
7292: end if;
7301: end if;
7302:
7303: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7304: wf_log_pkg.string(wf_log_pkg.level_statement,
7305: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.got_user',
7306: 'Email: '||from_email||' User: '||myusername||' DispName: '||mydispname);
7307: end if;
7308:
7309: --Bug 3065814
7352: -- do not want it hung when some one is doing update.
7353: begin
7354: select MORE_INFO_ROLE, MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID
7355: into l_from_role, l_messageType, l_messageName, l_groupId
7356: from WF_NOTIFICATIONS
7357: where NOTIFICATION_ID = nid
7358: for update nowait;
7359: exception
7360: when NO_DATA_FOUND then
7364: end;
7365:
7366: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7367: wf_log_pkg.string(wf_log_pkg.level_statement,
7368: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.question',
7369: 'Updating QUESTION');
7370: end if;
7371:
7372: if (cb is not null) then
7373: tvalue := myusername;
7374: nvalue := nid;
7375: -- ### Review Note 2 - cb is from table
7376: -- Check for bug#3827935
7377: l_charcheck := wf_notification_util.CheckIllegalChar(cb);
7378: --Throw the Illegal exception when the check fails
7379:
7380: -- BINDVAR_SCAN_IGNORE
7381: sqlbuf := 'begin '||cb||
7394: -- as we donot have a user session for the mailer, the only way to
7395: -- find FROM_ROLE and FROM_USER are through the from_addr. If the
7396: -- user name and display name are not available, email address is updated
7397: /* if (myusername is not null) then
7398: update WF_NOTIFICATIONS
7399: set MORE_INFO_ROLE = username,
7400: FROM_USER = mydispname,
7401: FROM_ROLE = myusername
7402: where NOTIFICATION_ID = nid;
7403:
7404: -- otherwise, we default to what it should be. Unfortunately, if it
7405: -- is a group role, we will not be able to identify which member I am.
7406: else */
7407: update WF_NOTIFICATIONS
7408: set MAIL_STATUS = 'MAIL',
7409: MORE_INFO_ROLE = l_username,
7410: FROM_USER = TO_USER,
7411: FROM_ROLE = RECIPIENT_ROLE
7411: FROM_ROLE = RECIPIENT_ROLE
7412: where NOTIFICATION_ID = nid;
7413: /*end if; */
7414:
7415: Wf_Notification.SetComments(nid, myusername, l_username, 'QUESTION', null, substrb(comment,1,4000));
7416:
7417: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
7418: -- insert into WF_COMMENTS (
7419: -- NOTIFICATION_ID,
7413: /*end if; */
7414:
7415: Wf_Notification.SetComments(nid, myusername, l_username, 'QUESTION', null, substrb(comment,1,4000));
7416:
7417: -- LANGUAGE here is for FROM_USER which came from WF_NOTIFICATIONS above
7418: -- insert into WF_COMMENTS (
7419: -- NOTIFICATION_ID,
7420: -- FROM_ROLE,
7421: -- FROM_USER,
7430: -- sysdate,
7431: -- 'QUESTION',
7432: -- substrb(comment,1,4000),
7433: -- LANGUAGE
7434: -- from WF_NOTIFICATIONS
7435: -- where NOTIFICATION_ID = nid;
7436:
7437: end if;
7438:
7437: end if;
7438:
7439: -- <
7440: -- we are here, mean we are going to raise
7441: -- oracle.apps.wf.notification.question event.
7442: l_event_name := 'oracle.apps.wf.notification.question';
7443:
7444: -- answer mode
7445: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
7438:
7439: -- <
7440: -- we are here, mean we are going to raise
7441: -- oracle.apps.wf.notification.question event.
7442: l_event_name := 'oracle.apps.wf.notification.question';
7443:
7444: -- answer mode
7445: -- NOTE: the language here is the language of the MORE_INFO_ROLE,
7446: -- no denormalization is needed here.
7452: begin
7453: select MORE_INFO_ROLE, Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7454: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role
7455: into l_from_role, replyby, l_messageType, l_messageName, l_groupId, l_question_role
7456: from WF_NOTIFICATIONS
7457: where NOTIFICATION_ID = nid
7458: and MORE_INFO_ROLE is not null
7459: for update nowait;
7460:
7493: end if;
7494:
7495: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
7496: wf_log_pkg.string(wf_log_pkg.level_statement,
7497: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
7498: 'Updating ANSWER');
7499: end if;
7500:
7501: update WF_NOTIFICATIONS
7497: 'wf.plsql.WF_NOTIFICATION.UpdateInfo2.answer',
7498: 'Updating ANSWER');
7499: end if;
7500:
7501: update WF_NOTIFICATIONS
7502: set MAIL_STATUS = 'MAIL',
7503: FROM_USER = replyby,
7504: FROM_ROLE = l_from_role,
7505: MORE_INFO_ROLE = null
7504: FROM_ROLE = l_from_role,
7505: MORE_INFO_ROLE = null
7506: where NOTIFICATION_ID = nid;
7507:
7508: Wf_Notification.SetComments(nid, myusername, l_recip_role, 'ANSWER', null, substrb(comment,1,4000));
7509:
7510: -- LANGUAGE here is for FROM_USER which came from GetRoleDisplayName above,
7511: -- so the LANGUAGE should be current userenv('LANG').
7512: -- insert into WF_COMMENTS (
7524: -- sysdate,
7525: -- 'ANSWER',
7526: -- substrb(comment,1,4000),
7527: -- userenv('LANG')
7528: -- from WF_NOTIFICATIONS
7529: -- where NOTIFICATION_ID = nid;
7530:
7531: -- we are here, mean we are going to raise
7532: -- oracle.apps.wf.notification.answer event.
7528: -- from WF_NOTIFICATIONS
7529: -- where NOTIFICATION_ID = nid;
7530:
7531: -- we are here, mean we are going to raise
7532: -- oracle.apps.wf.notification.answer event.
7533: l_event_name := 'oracle.apps.wf.notification.answer';
7534:
7535: end if; -- End of Answer mode
7536:
7529: -- where NOTIFICATION_ID = nid;
7530:
7531: -- we are here, mean we are going to raise
7532: -- oracle.apps.wf.notification.answer event.
7533: l_event_name := 'oracle.apps.wf.notification.answer';
7534:
7535: end if; -- End of Answer mode
7536:
7537: -- Send the notification through email
7541: -- wf_xml.EnqueueNotification(nid);
7542:
7543: -- Bug 2283697
7544: -- To raise an EVENT whenever DML operation is performed on
7545: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7546: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7547:
7548: -- username MAY be a display name
7549: wf_event.AddParameterToList('ROLE', nvl(l_username, username), l_parameterlist);
7568: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
7569:
7570:
7571: -- Raise the event
7572: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
7573: -- p_event_key => to_char(nid),
7574: -- p_parameters => l_parameterlist);
7575:
7576: -- <
7579: p_parameters => l_parameterlist);
7580:
7581: exception
7582: when OTHERS then
7583: Wf_Core.Context('Wf_Notification', 'UpdateInfo2', to_char(nid), username, from_email);
7584: raise;
7585: end UpdateInfo2;
7586:
7587:
7621: l_language varchar2(30);
7622: role_info_tbl wf_directory.wf_local_roles_tbl_type;
7623:
7624: begin
7625: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7626: 'NID: '||to_char(nid));
7627:
7628: -- Do not allow reply when a question has not been asked, or it has
7629: -- already been answered. In both cases, MORE_INFO_ROLE is set to null.
7634: Wf_Directory.GetRoleDisplayName(MORE_INFO_ROLE),
7635: MESSAGE_TYPE, MESSAGE_NAME, GROUP_ID , from_role, callback, context
7636: into l_orig_recip_role, l_recipient_role, l_from_role,
7637: replyby, l_messageType, l_messageName, l_groupId, l_question_role, cb, context
7638: from WF_NOTIFICATIONS
7639: where NOTIFICATION_ID = nid
7640: and MORE_INFO_ROLE is not null
7641: for update nowait;
7642:
7679:
7680: end if;
7681:
7682:
7683: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7684: 'Updating ANSWER');
7685:
7686: update WF_NOTIFICATIONS
7687: set MAIL_STATUS = 'MAIL',
7682:
7683: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.UpdateInfoGuest',
7684: 'Updating ANSWER');
7685:
7686: update WF_NOTIFICATIONS
7687: set MAIL_STATUS = 'MAIL',
7688: FROM_USER = moreinforesponder,
7689: FROM_ROLE = moreinforesponder,
7690: MORE_INFO_ROLE = null
7689: FROM_ROLE = moreinforesponder,
7690: MORE_INFO_ROLE = null
7691: where NOTIFICATION_ID = nid;
7692:
7693: Wf_Notification.SetComments(nid, moreinforesponder, l_recipient_role, 'ANSWER', null, substrb(moreinfoanswer,1,4000));
7694: Wf_Notification.Route(nid, 0);
7695:
7696: -- Send the notification through email
7697: -- Enqueuing has been moved to a subscription for forward
7690: MORE_INFO_ROLE = null
7691: where NOTIFICATION_ID = nid;
7692:
7693: Wf_Notification.SetComments(nid, moreinforesponder, l_recipient_role, 'ANSWER', null, substrb(moreinfoanswer,1,4000));
7694: Wf_Notification.Route(nid, 0);
7695:
7696: -- Send the notification through email
7697: -- Enqueuing has been moved to a subscription for forward
7698: -- compatability. The subscription need only be enabled to use
7700: -- wf_xml.EnqueueNotification(nid);
7701:
7702: -- Bug 2283697
7703: -- To raise an EVENT whenever DML operation is performed on
7704: -- WF_NOTIFICATIONS and WF_NOTIFICATION_ATTRIBUTES table.
7705: wf_event.AddParameterToList('NOTIFICATION_ID', nid, l_parameterlist);
7706: -- skilaru 12-MAR-04 In UpdateInfo2 username would be null in Answer mode
7707: -- to keep the behaviour same just pass null as ROLE..
7708: wf_event.AddParameterToList('ROLE', null, l_parameterlist);
7726: wf_event.addParameterToList('PK_VALUE_2', l_language, l_parameterlist);
7727:
7728:
7729: -- Raise the event
7730: -- wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
7731: -- p_event_key => to_char(nid),
7732: -- p_parameters => l_parameterlist);
7733:
7734: -- <
7731: -- p_event_key => to_char(nid),
7732: -- p_parameters => l_parameterlist);
7733:
7734: -- <
7735: wf_event.Raise(p_event_name => 'oracle.apps.wf.notification.answer',
7736: p_event_key => to_char(nid),
7737: p_parameters => l_parameterlist);
7738:
7739: exception
7737: p_parameters => l_parameterlist);
7738:
7739: exception
7740: when OTHERS then
7741: Wf_Core.Context('Wf_Notification', 'UpdateInfoGuest', to_char(nid), moreinforesponder);
7742: raise;
7743: end UpdateInfoGuest;
7744:
7745:
7756: l_hide varchar2(1);
7757: begin
7758: -- Get value for #HIDE_MOREINFO attribute for the notification
7759: begin
7760: l_hide := substrb(WF_NOTIFICATION.GetAttrText(nid, '#HIDE_MOREINFO'), 1, 1);
7761: -- Bugfix 2880029 - changed sacsharm - 03/31/03
7762: -- Only if attribute value is explicitly 'Y' hide Request More Info. else
7763: -- if it is null or 'N' or any other character donot hide Request More Info.
7764: l_hide := upper(nvl(l_hide, 'N'));
7778: end;
7779: return (l_hide);
7780: exception
7781: when others then
7782: wf_core.context('Wf_Notification', 'HideMoreInfo', to_char(nid));
7783: raise;
7784: end HideMoreInfo;
7785:
7786: -- GetComments
7814: close c_ques;
7815:
7816: -- Call the GetComments2 procedure to get the Action History for only
7817: -- More Info Requests. This procedure was doing that previously
7818: Wf_Notification.GetComments2(p_nid => nid,
7819: p_display_type => display_type,
7820: p_hide_reassign => 'Y',
7821: p_hide_requestinfo => 'N',
7822: p_action_history => html_history);
7822: p_action_history => html_history);
7823:
7824: exception
7825: when others then
7826: wf_core.context('Wf_Notification', 'GetComments', to_char(nid), display_type);
7827: raise;
7828: end GetComments;
7829:
7830: --
8067:
8068: j := 1;
8069: -- Action History Title
8070: cells(j) := wf_core.translate('NUM');
8071: if (p_display_type = wf_notification.doc_html) then
8072: cells(j) := 'S5%:'||cells(j);
8073: end if;
8074:
8075: j := j+1;
8073: end if;
8074:
8075: j := j+1;
8076: cells(j) := wf_core.translate('ACTION_DATE');
8077: if (p_display_type = wf_notification.doc_html) then
8078: cells(j) := 'S15%:'||cells(j);
8079: end if;
8080:
8081: j := j+1;
8079: end if;
8080:
8081: j := j+1;
8082: cells(j) := wf_core.translate('ACTION');
8083: if (p_display_type = wf_notification.doc_html) then
8084: cells(j) := 'S10%:'||cells(j);
8085: end if;
8086:
8087: j := j+1;
8085: end if;
8086:
8087: j := j+1;
8088: cells(j) := wf_core.translate('FROM');
8089: if (p_display_type = wf_notification.doc_html) then
8090: cells(j) := 'S15%:'||cells(j);
8091: end if;
8092:
8093: j := j+1;
8091: end if;
8092:
8093: j := j+1;
8094: cells(j) := wf_core.translate('TO');
8095: if (p_display_type = wf_notification.doc_html) then
8096: cells(j) := 'S15%:'||cells(j);
8097: end if;
8098:
8099: j := j+1;
8097: end if;
8098:
8099: j := j+1;
8100: cells(j) := wf_core.translate('DETAILS');
8101: if (p_display_type = wf_notification.doc_html) then
8102: cells(j) := 'S40%:'||cells(j);
8103: end if;
8104:
8105: j := j+1;
8117:
8118: cells(j) := to_char(l_comm_rec.h_sequence);
8119:
8120: j := j+1;
8121: if (p_display_type = wf_notification.doc_html) then
8122: --
8123: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8124: else
8125: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8119:
8120: j := j+1;
8121: if (p_display_type = wf_notification.doc_html) then
8122: --
8123: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8124: else
8125: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8126: end if;
8127:
8121: if (p_display_type = wf_notification.doc_html) then
8122: --
8123: cells(j) := 'S:' || wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8124: else
8125: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_comm_rec.h_action_date, null, true);
8126: end if;
8127:
8128: j := j+1;
8129:
8138: end if;
8139: l_action_str := Wf_Core.Translate(l_action);
8140: end if;
8141:
8142: if (p_display_type = wf_notification.doc_html) then
8143: cells(j) := 'S:'||l_action_str;
8144: else
8145: cells(j) := l_action_str;
8146: end if;
8145: cells(j) := l_action_str;
8146: end if;
8147:
8148: j := j+1;
8149: if (p_display_type = wf_notification.doc_html) then
8150: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_from_user);
8151: else
8152: cells(j) := l_comm_rec.h_from_user;
8153: end if;
8146: end if;
8147:
8148: j := j+1;
8149: if (p_display_type = wf_notification.doc_html) then
8150: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_from_user);
8151: else
8152: cells(j) := l_comm_rec.h_from_user;
8153: end if;
8154:
8152: cells(j) := l_comm_rec.h_from_user;
8153: end if;
8154:
8155: j := j+1;
8156: if (p_display_type = wf_notification.doc_html) then
8157: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_to_user);
8158: else
8159: cells(j) := l_comm_rec.h_to_user;
8160: end if;
8153: end if;
8154:
8155: j := j+1;
8156: if (p_display_type = wf_notification.doc_html) then
8157: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_comm_rec.h_to_user);
8158: else
8159: cells(j) := l_comm_rec.h_to_user;
8160: end if;
8161:
8165: if (l_note = '#WF_NOTE#') then
8166: begin
8167: SELECT text_value
8168: INTO l_note
8169: FROM wf_notification_attributes
8170: WHERE notification_id = l_comm_rec.h_notification_id
8171: AND name = 'WF_NOTE';
8172: exception
8173: when no_data_found then
8173: when no_data_found then
8174: l_note := '';
8175: end;
8176: end if;
8177: if (p_display_type = wf_notification.doc_html) then
8178: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8179: end if;
8180: cells(j) := l_note;
8181:
8174: l_note := '';
8175: end;
8176: end if;
8177: if (p_display_type = wf_notification.doc_html) then
8178: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8179: end if;
8180: cells(j) := l_note;
8181:
8182: if (p_display_type = wf_notification.doc_html) then
8178: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8179: end if;
8180: cells(j) := l_note;
8181:
8182: if (p_display_type = wf_notification.doc_html) then
8183: if (cells(j) is null) then
8184: cells(j) := 'S: ';
8185: else
8186: cells(j) := 'S:'||cells(j);
8203:
8204: cells(j) := to_char(l_ntf_hist_rec.h_sequence);
8205:
8206: j := j+1;
8207: if (p_display_type = wf_notification.doc_html) then
8208: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8209: else
8210: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8211: end if;
8204: cells(j) := to_char(l_ntf_hist_rec.h_sequence);
8205:
8206: j := j+1;
8207: if (p_display_type = wf_notification.doc_html) then
8208: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8209: else
8210: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8211: end if;
8212:
8206: j := j+1;
8207: if (p_display_type = wf_notification.doc_html) then
8208: cells(j) := 'S:'|| wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8209: else
8210: cells(j) := wf_notification_util.GetCalendarDate(p_nid, l_ntf_hist_rec.h_action_date, null, true);
8211: end if;
8212:
8213: j := j+1;
8214:
8218: l_action := substr(l_action, 1, l_pos-1);
8219: end if;
8220: l_action_str := Wf_Core.Translate(l_action);
8221:
8222: if (p_display_type = wf_notification.doc_html) then
8223: cells(j) := 'S:'||l_action_str;
8224: else
8225: cells(j) := l_action_str;
8226: end if;
8225: cells(j) := l_action_str;
8226: end if;
8227:
8228: j := j+1;
8229: if (p_display_type = wf_notification.doc_html) then
8230: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_from_user);
8231: else
8232: cells(j) := l_ntf_hist_rec.h_from_user;
8233: end if;
8226: end if;
8227:
8228: j := j+1;
8229: if (p_display_type = wf_notification.doc_html) then
8230: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_from_user);
8231: else
8232: cells(j) := l_ntf_hist_rec.h_from_user;
8233: end if;
8234:
8232: cells(j) := l_ntf_hist_rec.h_from_user;
8233: end if;
8234:
8235: j := j+1;
8236: if (p_display_type = wf_notification.doc_html) then
8237: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_to_user);
8238: else
8239: cells(j) := l_ntf_hist_rec.h_to_user;
8240: end if;
8233: end if;
8234:
8235: j := j+1;
8236: if (p_display_type = wf_notification.doc_html) then
8237: cells(j) := 'S:'||Wf_Notification.SubstituteSpecialChars(l_ntf_hist_rec.h_to_user);
8238: else
8239: cells(j) := l_ntf_hist_rec.h_to_user;
8240: end if;
8241:
8240: end if;
8241:
8242: j := j+1;
8243: l_note := l_ntf_hist_rec.h_comment;
8244: if (p_display_type = wf_notification.doc_html) then
8245: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8246: end if;
8247: cells(j) := l_note;
8248:
8241:
8242: j := j+1;
8243: l_note := l_ntf_hist_rec.h_comment;
8244: if (p_display_type = wf_notification.doc_html) then
8245: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8246: end if;
8247: cells(j) := l_note;
8248:
8249: if (p_display_type = wf_notification.doc_html) then
8245: l_note := substrb(Wf_Notification.SubstituteSpecialChars(l_note), 1, 4000);
8246: end if;
8247: cells(j) := l_note;
8248:
8249: if (p_display_type = wf_notification.doc_html) then
8250: if (cells(j) is null) then
8251: cells(j) := 'S: ';
8252: else
8253: cells(j) := 'S:'||cells(j);
8268: end if;
8269:
8270: -- Sequence is now based on the rownum, not the reverse
8271: -- for k in 0..(i-1) loop
8272: -- if (p_display_type = wf_notification.doc_html) then
8273: -- cells((k+1)*6+1) := 'C:'||to_char(i-k-1);
8274: -- else
8275: -- cells((k+1)*6+1) := to_char(i-k-1);
8276: -- end if;
8276: -- end if;
8277: -- end loop;
8278:
8279: -- Construct table from the cells
8280: if (p_display_type = wf_notification.doc_html) then
8281: table_width := '100%';
8282: -- bug 7718246 - set the table border to 1 only for action history
8283: table_border := '1';
8284: NTF_Table(cells=>cells,
8307: p_action_history := l_result;
8308:
8309: exception
8310: when others then
8311: wf_core.context('Wf_Notification', 'GetComments2', to_char(p_nid), p_display_type);
8312: raise;
8313: end GetComments2;
8314:
8315: --
8325: -- IN:
8326: -- nid - Notification id
8327: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8328: -- disptype - Requested display type. Valid values:
8329: -- wf_notification.doc_text - 'text/plain'
8330: -- wf_notification.doc_html - 'text/html'
8331: -- document - The blob into which
8332: -- aname - Attribute Name (the first part of the string that matches
8333: -- the attr list)
8326: -- nid - Notification id
8327: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8328: -- disptype - Requested display type. Valid values:
8329: -- wf_notification.doc_text - 'text/plain'
8330: -- wf_notification.doc_html - 'text/html'
8331: -- document - The blob into which
8332: -- aname - Attribute Name (the first part of the string that matches
8333: -- the attr list)
8334: --
8341: is
8342: doctype varchar2(500);
8343: begin
8344:
8345: Wf_Notification.GetAttrblob(nid, astring, disptype, document, doctype, aname);
8346:
8347: exception
8348: when others then
8349: wf_core.context('Wf_Notification', 'oldGetAttrblob', to_char(nid), aname,
8345: Wf_Notification.GetAttrblob(nid, astring, disptype, document, doctype, aname);
8346:
8347: exception
8348: when others then
8349: wf_core.context('Wf_Notification', 'oldGetAttrblob', to_char(nid), aname,
8350: disptype);
8351: raise;
8352: end GetAttrblob;
8353:
8361: -- IN:
8362: -- nid - Notification id
8363: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8364: -- disptype - Requested display type. Valid values:
8365: -- wf_notification.doc_text - 'text/plain'
8366: -- wf_notification.doc_html - 'text/html'
8367: -- document - The blob into which
8368: -- aname - Attribute Name (the string that matches
8369: -- the attr list)
8362: -- nid - Notification id
8363: -- astring - the string to substitute on (ex: '&ATTR1 is your order..')
8364: -- disptype - Requested display type. Valid values:
8365: -- wf_notification.doc_text - 'text/plain'
8366: -- wf_notification.doc_html - 'text/html'
8367: -- document - The blob into which
8368: -- aname - Attribute Name (the string that matches
8369: -- the attr list)
8370: --
8396: begin
8397:
8398: -- Check args
8399: if ((nid is null) or (astring is null) or
8400: (disptype not in (wf_notification.doc_text,
8401: wf_notification.doc_html))) then
8402: wf_core.token('NID', to_char(nid));
8403: wf_core.token('ASTRING', aname);
8404: wf_core.token('DISPTYPE', disptype);
8397:
8398: -- Check args
8399: if ((nid is null) or (astring is null) or
8400: (disptype not in (wf_notification.doc_text,
8401: wf_notification.doc_html))) then
8402: wf_core.token('NID', to_char(nid));
8403: wf_core.token('ASTRING', aname);
8404: wf_core.token('DISPTYPE', disptype);
8405: wf_core.raise('WFSQL_ARGS');
8413: begin
8414: -- <7443088> improved query
8415: select NAME into aname from
8416: (select WMA.NAME
8417: from WF_NOTIFICATIONS WN,
8418: WF_MESSAGE_ATTRIBUTES WMA,
8419: WF_NOTIFICATION_ATTRIBUTES NA
8420: where WN.NOTIFICATION_ID = nid
8421: and wn.notification_id = na.notification_id
8415: select NAME into aname from
8416: (select WMA.NAME
8417: from WF_NOTIFICATIONS WN,
8418: WF_MESSAGE_ATTRIBUTES WMA,
8419: WF_NOTIFICATION_ATTRIBUTES NA
8420: where WN.NOTIFICATION_ID = nid
8421: and wn.notification_id = na.notification_id
8422: and wma.name = na.name
8423: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
8434: end;
8435:
8436: if (aname is not null) then
8437: -- Retrieve key string
8438: key := wf_notification.GetAttrText(nid, aname);
8439:
8440: -- If the key is empty then return a null string
8441: if (key is not null) then
8442:
8469:
8470: if (procarg is null) then
8471: procarg := NULL;
8472: else
8473: procarg := Wf_Notification.GetTextInternal(procarg, nid, target,
8474: FALSE, FALSE);
8475: end if;
8476:
8477: -- ### Review Note 1
8475: end if;
8476:
8477: -- ### Review Note 1
8478: -- Check for bug#3827935
8479: l_charcheck := wf_notification_util.CheckIllegalChar(procname);
8480: --Throw the Illegal exception when the check fails
8481:
8482: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8483: wf_log_pkg.string2(wf_log_pkg.level_statement,
8480: --Throw the Illegal exception when the check fails
8481:
8482: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8483: wf_log_pkg.string2(wf_log_pkg.level_statement,
8484: 'wf.plsql.wf_notification.GetAttrBlob.plsqlblob_callout',
8485: 'Start executing PLSQLBLOB Doc procedure - '||procname, true);
8486: end if;
8487:
8488: sqlbuf := 'begin '||procname||'(:p1, :p2, :p3, :p4); end;';
8493: in out doctype;
8494:
8495: if (wf_log_pkg.level_statement >= fnd_log.g_current_runtime_level) then
8496: wf_log_pkg.string2(wf_log_pkg.level_statement,
8497: 'wf.plsql.wf_notification.GetAttrBlob.plsqlblob_callout',
8498: 'End executing PLSQLBLOB Doc procedure - '||procname, false);
8499: end if;
8500:
8501: exception
8504: /* if (dbms_sql.is_open(curs)) then
8505: dbms_sql.close_cursor(curs);
8506: end if; */
8507:
8508: wf_core.context('Wf_Notification', 'GetAttrblob', to_char(nid), aname,
8509: disptype);
8510: raise;
8511: end GetAttrblob;
8512:
8512:
8513: --
8514: -- Set_NTF_Table_Direction
8515: -- Sets the default direction of notification tables
8516: -- generated through wf_notification.wf_ntf_history
8517: -- and wf_notification.wf_msg_attr
8518: procedure Set_NTF_Table_Direction(direction in varchar2)
8519: is
8520: begin
8513: --
8514: -- Set_NTF_Table_Direction
8515: -- Sets the default direction of notification tables
8516: -- generated through wf_notification.wf_ntf_history
8517: -- and wf_notification.wf_msg_attr
8518: procedure Set_NTF_Table_Direction(direction in varchar2)
8519: is
8520: begin
8521: table_direction := direction;
8523:
8524: --
8525: -- Set_NTF_Table_Type
8526: -- Sets the default table type for attr tables
8527: -- generated through wf_notification.wf_msg_attr
8528: procedure Set_NTF_Table_Type(tableType in varchar2)
8529: is
8530: begin
8531: table_type := tableType;
8543:
8544: function isFwkRegion(nid in number) return varchar2 is
8545:
8546: begin
8547: return isFwkRegion(nid, wf_notification.doc_html);
8548: end isFwkRegion;
8549:
8550:
8551: -- isFwkRegion
8568: lv_token_start number;
8569:
8570: cursor cur_hdr_region is
8571: select WNA.NAME, WNA.TEXT_VALUE
8572: from WF_NOTIFICATION_ATTRIBUTES WNA,
8573: WF_MESSAGE_ATTRIBUTES_VL WMA
8574: where WNA.NOTIFICATION_ID = nid
8575: and WMA.NAME = WNA.NAME
8576: and WMA.TYPE = 'DOCUMENT'
8594: return isFwkBody( nid, content_type);
8595: end if;
8596: exception
8597: when OTHERS then
8598: wf_core.context('Wf_Notification','isFwkRegion',to_char(nid), content_type);
8599: raise;
8600:
8601: End isFwkRegion;
8602:
8606: -- Algorithm: Function returns 'Y' if one of the following condition is met
8607: -- - If the first attribute referred in the body is of
8608: -- type 'DOCUMENT' and its value starts with 'JSP:/OA_HTML/OA.jsp?'
8609: -- - If the message body does not have any attributes refered except
8610: -- for WF_NOTIFICATION macro and simple text
8611:
8612: function isFwkBody(nid in number) return varchar2 is
8613:
8614:
8613:
8614:
8615: begin
8616: -- invoke overrided API with default as 'text/html'
8617: return isFwkBody(nid, wf_notification.doc_html);
8618:
8619: End isFwkBody;
8620:
8621: -- isFwkBody
8624: -- Algorithm: Function returns 'Y' if one of the following condition is met
8625: -- - If the first attribute referred in the body is of
8626: -- type 'DOCUMENT' and its value starts with 'JSP:/OA_HTML/OA.jsp?'
8627: -- - If the message body does not have any attributes refered except
8628: -- for WF_NOTIFICATION macro and simple text
8629: -- Auth : SSTOMAR
8630: function isFwkBody(nid in number, content_type in varchar2) return varchar2 is
8631:
8632: lv_body varchar2(32000);
8641: lv_fwk_body := 'N';
8642:
8643: select nvl(WM.BODY, ''), nvl(WM.HTML_BODY, '')
8644: into lv_body, lv_html_body
8645: from WF_NOTIFICATIONS N, WF_MESSAGES_VL WM
8646: where N.NOTIFICATION_ID = nid
8647: and N.MESSAGE_NAME = WM.NAME
8648: and N.MESSAGE_TYPE = WM.TYPE;
8649:
8654: -- as Framework based notification but If message body has simple text contains
8655: -- '& ' character without token name
8656: -- then according to below logic it will be plsql based ntf.
8657:
8658: if (content_type = wf_notification.doc_html ) then
8659: if (length(trim(lv_html_body)) > 0 ) then
8660:
8661: if (fwkTokenExist(nid, lv_html_body) = 'Y'
8662: or instr(lv_html_body, '&') = 0 ) then
8696:
8697: return lv_fwk_body;
8698: exception
8699: when OTHERS then
8700: wf_core.context('Wf_Notification','isFwkBody',to_char(nid), content_type);
8701: raise;
8702:
8703: End isFwkBody;
8704:
8713:
8714: -- Cursur to check for each message Attribute
8715: cursor cur_msg_attrs(nid number, msgToken varchar2) is
8716: select WNA.NAME, WNA.TEXT_VALUE, WMA.TYPE
8717: from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
8718: WF_MESSAGE_ATTRIBUTES_VL WMA
8719: where WNA.NOTIFICATION_ID = nid
8720: and WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID
8721: and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
8747:
8748: return lv_token_exist;
8749: exception
8750: when OTHERS then
8751: wf_core.context('Wf_Notification','fwkTokenExist',to_char(nid));
8752: raise;
8753:
8754: end fwkTokenExist;
8755:
8786:
8787: begin
8788: --skilaru 16-July-03
8789: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
8790: --mapped to WF_NOTIFICATIONS.GROUP_ID
8791: SELECT group_id
8792: INTO l_group_nid
8793: FROM wf_notifications
8794: WHERE notification_id = nid;
8789: --WF_ITEM_ACTIVITY_STATUSES.NOTIFICATION_ID is the foreing key
8790: --mapped to WF_NOTIFICATIONS.GROUP_ID
8791: SELECT group_id
8792: INTO l_group_nid
8793: FROM wf_notifications
8794: WHERE notification_id = nid;
8795:
8796: for act_status_row in act_info_statuses_cursor( l_group_nid ) loop
8797: l_itype := act_status_row.ITEM_TYPE;
8808: end if;
8809:
8810: exception
8811: when OTHERS then
8812: wf_core.context('Wf_Notification','getNtfActInfo',to_char(nid));
8813: raise;
8814:
8815: End getNtfActInfo;
8816:
8872: if url_value is null then
8873: url_value := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/');
8874: end if;
8875:
8876: url_value := url_value || wf_notification.fwk_mailer_page;
8877: url_value := url_value || '&WFRegion=NtfDetail&NtfId=' || to_char(p_nid);
8878: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
8879:
8880: if (p_contentType = wf_notification.doc_html) then
8876: url_value := url_value || wf_notification.fwk_mailer_page;
8877: url_value := url_value || '&WFRegion=NtfDetail&NtfId=' || to_char(p_nid);
8878: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
8879:
8880: if (p_contentType = wf_notification.doc_html) then
8881: -- url_value := url_value || '&OALAF=blaf&OARF=email';
8882: url_value := url_value || '&OARF=email';
8883: elsif (p_contentType = wf_notification.doc_text) then
8884: url_value := url_value || '&OALAF=oaText&OARF=email';
8879:
8880: if (p_contentType = wf_notification.doc_html) then
8881: -- url_value := url_value || '&OALAF=blaf&OARF=email';
8882: url_value := url_value || '&OARF=email';
8883: elsif (p_contentType = wf_notification.doc_text) then
8884: url_value := url_value || '&OALAF=oaText&OARF=email';
8885: end if;
8886:
8887: -- Bug 5170348
8893: url_value := url_value || '&language_code='|| lang_code;
8894:
8895: exception
8896: when others then
8897: wf_log_pkg.string(WF_LOG_PKG.LEVEL_UNEXPECTED, 'WF_NOTIFICATION.getFwkBodyURLLang',
8898: 'nid: '||to_char(p_nid)||'; language: '|| p_language);
8899: end;
8900: end if;
8901:
8914: return url_value;
8915:
8916: exception
8917: when OTHERS then
8918: wf_core.context('Wf_Notification','getFwkBodyURL2',
8919: to_char(p_nid), p_contentType,p_language,p_nlsCalendar );
8920: raise;
8921:
8922: END getFwkBodyURL2;
8993: if url_value is null then
8994: url_value := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/');
8995: end if;
8996:
8997: url_value := url_value || wf_notification.fwk_mailer_page;
8998: url_value := url_value ||'&WFRegion=NtfSummary&mailerRole=' || p_mailer_role;
8999: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9000:
9001: if (p_contentType = wf_notification.doc_html) then
8997: url_value := url_value || wf_notification.fwk_mailer_page;
8998: url_value := url_value ||'&WFRegion=NtfSummary&mailerRole=' || p_mailer_role;
8999: url_value := url_value || '&dbc=' || fnd_web_config.Database_ID;
9000:
9001: if (p_contentType = wf_notification.doc_html) then
9002: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9003: url_value := url_value || '&OARF=email';
9004: elsif (p_contentType = wf_notification.doc_text) then
9005: url_value := url_value || '&OALAF=oaText&OARF=email';
9000:
9001: if (p_contentType = wf_notification.doc_html) then
9002: -- url_value := url_value || '&OALAF=blaf&OARF=email';
9003: url_value := url_value || '&OARF=email';
9004: elsif (p_contentType = wf_notification.doc_text) then
9005: url_value := url_value || '&OALAF=oaText&OARF=email';
9006: end if;
9007:
9008: -- Append the NLS_CALENDAR to the fwk URL only for 12.1.0 or above
9022: return url_value;
9023:
9024: exception
9025: when OTHERS then
9026: wf_core.context('Wf_Notification','getSummaryURL2', p_mailer_role, p_contentType, p_nlsCalendar);
9027: raise;
9028:
9029: END getSummaryURL2;
9030:
9070: --when any exception raise the error with the corresponding notification id
9071:
9072: exception
9073: when others then
9074: wf_core.context('WF_Notification', 'GetSignatureRequired', to_char(p_nid));
9075: wf_core.token('NID', to_char(p_nid));
9076: wf_core.raise('WFMLR_INVALID_SIG_POLICY');
9077: end;
9078:
9197: );
9198:
9199: exception
9200: when others then
9201: wf_core.context('Wf_Notification', 'SetComments', to_char(p_nid), p_from_role,
9202: p_to_role, p_action, p_action_source);
9203: raise;
9204: end SetComments;
9205:
9235:
9236: begin
9237: SELECT message_type, message_name, status, mail_status, nvl(more_info_role, recipient_role) recipient_role, group_id
9238: INTO l_message_type, l_message_name, l_status, l_mail_status, l_recipient_role, l_group_id
9239: FROM wf_notifications
9240: WHERE notification_id = p_nid;
9241: exception
9242: when no_data_found then
9243: wf_core.token('NID', to_char(p_nid));
9268: wf_core.raise('WFNTF_EMAIL_NOTSENT');
9269: end if;
9270:
9271: -- Raise the event to send an e-mail
9272: UPDATE wf_notifications
9273: SET mail_status = 'MAIL'
9274: WHERE notification_id = p_nid;
9275:
9276: Wf_Event.AddParameterToList('NOTIFICATION_ID', p_nid, l_paramlist);
9293: wf_event.addParameterToList('PK_VALUE_1', p_nid, l_paramlist);
9294: wf_event.addParameterToList('PK_NAME_2', 'LANGUAGE',l_paramlist);
9295: wf_event.addParameterToList('PK_VALUE_2', l_language, l_paramlist);
9296:
9297: Wf_Event.Raise(p_event_name => 'oracle.apps.wf.notification.send',
9298: p_event_key => to_char(p_nid),
9299: p_parameters => l_paramlist);
9300:
9301: exception
9299: p_parameters => l_paramlist);
9300:
9301: exception
9302: when others then
9303: wf_core.context('Wf_Notification', 'Resend', to_char(p_nid));
9304: raise;
9305: end Resend;
9306:
9307: --
9359: when NO_DATA_FOUND then
9360: p_result_code := null;
9361: p_result_display := null;
9362: when others then
9363: wf_core.context('Wf_Notification', 'getNtfResponse', to_char(p_nid));
9364: raise;
9365: end getNtfResponse;
9366:
9367: --
9380: is
9381: --Get the nids in curs_nid which have the attribute document_id
9382: cursor curs_nid(l_doc_id varchar2,l_item_type varchar2,l_item_key varchar2) is
9383: select wfn.notification_id
9384: from wf_item_activity_statuses wfas, wf_notifications wfn , wf_notification_attributes wfna
9385: where wfna.name = '#DOCUMENT_ID'
9386: and wfna.text_value = l_doc_id
9387: and wfas.item_type = l_item_type
9388: and wfas.item_key = l_item_key
9390: and wfas.notification_id = wfn.group_id;
9391: begin
9392: for comment_curs in curs_nid(p_document_id,p_item_type,p_item_key) loop
9393: --Now loop through the cursor and set the comments
9394: wf_notification.SetComments(p_nid => comment_curs.notification_id,
9395: p_from_role => p_from_role,
9396: p_to_role => p_to_role,
9397: p_action => p_action,
9398: p_action_source => p_action_source,
9399: p_user_comment => p_user_comment);
9400: end loop;
9401: exception
9402: when others then
9403: wf_core.context('Wf_Notification', 'propagatehistory', p_item_type,p_item_key, p_document_id);
9404: raise;
9405: end;
9406:
9407: --
9445: cp_from_date date,
9446: cp_to_date date)
9447: IS
9448: SELECT notification_id
9449: FROM wf_notifications wn
9450: WHERE wn.status = 'OPEN'
9451: AND wn.mail_status = 'FAILED'
9452: AND wn.recipient_role like nvl(cp_role, '%')
9453: AND wn.message_type like nvl(cp_msg_type, '%')
9461: cp_from_date date,
9462: cp_to_date date)
9463: IS
9464: SELECT notification_id
9465: FROM wf_notifications wn
9466: WHERE wn.status = 'OPEN'
9467: AND wn.mail_status = 'ERROR'
9468: AND wn.recipient_role like nvl(cp_role, '%')
9469: AND wn.message_type like nvl(cp_msg_type, '%')
9471: AND (cp_to_date is null or wn.begin_date <= cp_to_date )
9472: AND NOT EXISTS (
9473: SELECT 1
9474: FROM wf_message_attributes wma,
9475: wf_notifications wn2
9476: WHERE wn2.notification_id = wn.notification_id
9477: AND wma.message_type = wn2.message_type
9478: AND wma.message_name = wn2.message_name
9479: AND wma.subtype = 'RESPOND'
9498: fetch c_failed_ntfs into l_nid;
9499: exit when c_failed_ntfs%NOTFOUND;
9500: begin
9501: -- Raise event
9502: Wf_Notification.Resend(l_nid);
9503:
9504: exception
9505: when others then
9506: -- ignore any error while enqueing
9519: fetch c_error_fyi_ntfs into l_nid;
9520: exit when c_error_fyi_ntfs%NOTFOUND;
9521: begin
9522: -- Raise event
9523: Wf_Notification.Resend(l_nid);
9524: exception
9525: when others then
9526: -- ignore any error while enqueing
9527: Wf_Core.Clear();
9555: if (instr(upper(g_nls_date_mask), 'HH') = 0) then
9556: g_nls_date_mask := g_nls_date_mask||' HH24:MI:SS';
9557: end if;
9558:
9559: End WF_Notification;
9560: