[Home] [Help]
28: -- Queue owner
29: g_qowner varchar2(30) := Wf_Core.Translate('WF_SCHEMA');
30:
31: -- Queue name constants
32: WFD_NTF_OUT varchar2(30) := 'WF_NOTIFICATION_OUT';
33: WFD_NTF_IN varchar2(30) := 'WF_NOTIFICATION_IN';
34: WFD_DEFERRED varchar2(30) := 'WF_DEFERRED';
35: WFD_ERROR varchar2(30) := 'WF_ERROR';
36: WFD_PROV_OUT varchar2(30) := 'WF_PROV_OUT';
29: g_qowner varchar2(30) := Wf_Core.Translate('WF_SCHEMA');
30:
31: -- Queue name constants
32: WFD_NTF_OUT varchar2(30) := 'WF_NOTIFICATION_OUT';
33: WFD_NTF_IN varchar2(30) := 'WF_NOTIFICATION_IN';
34: WFD_DEFERRED varchar2(30) := 'WF_DEFERRED';
35: WFD_ERROR varchar2(30) := 'WF_ERROR';
36: WFD_PROV_OUT varchar2(30) := 'WF_PROV_OUT';
37: WFD_PROV_IN varchar2(30) := 'WF_PROV_IN';
40:
41: g_group_ntf boolean := FALSE;
42: --
43: -- Get_Table
44: -- Implemented from WF_NOTIFICATION.NTF_Table procedure.
45: -- Returns a Vertical or Horizontal Headered table
46:
47: procedure Get_Table(p_cells in tdType,
48: p_cols in pls_integer,
146: end Get_Table;
147:
148: --
149: -- Get_Table
150: -- Implemented from WF_NOTIFICATION.NTF_Table procedure.
151: -- Returns a Vertical or Horizontal Headered table
152:
153: procedure Get_Table(p_cells in tdType,
154: p_cols in pls_integer,
275: callback cb,
276: context ctx,
277: responder resp,
278: subject subj
279: from wf_notifications
280: where notification_id = p_nid;
281:
282: l_ntf_rec c_ntf%rowtype;
283: l_cells tdType;
289: l_terr varchar2(30);
290: l_orig_sys varchar2(30);
291: l_orig_id number;
292: l_install varchar2(1);
293: doctype varchar(30) := WF_NOTIFICATION.doc_html;
294:
295: begin
296: l_cells(1) := 'WH:Notification Item Information';
297: Get_table(l_cells, 1, 'H', l_result);
326: l_orig_sys, l_orig_id, l_install);
327:
328: -- Set the document type based on the notification preference.
329: if l_npref = 'MAILTEXT' then
330: doctype := WF_NOTIFICATION.doc_text;
331: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
332: doctype := WF_NOTIFICATION.doc_html;
333: end if;
334:
328: -- Set the document type based on the notification preference.
329: if l_npref = 'MAILTEXT' then
330: doctype := WF_NOTIFICATION.doc_text;
331: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
332: doctype := WF_NOTIFICATION.doc_html;
333: end if;
334:
335: l_cells(18) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
336: -- l_cells(18) := Wf_Notification.IsFwkBody(p_nid);
331: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
332: doctype := WF_NOTIFICATION.doc_html;
333: end if;
334:
335: l_cells(18) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
336: -- l_cells(18) := Wf_Notification.IsFwkBody(p_nid);
337: -- End bug 5529150
338:
339: l_cells(19) := to_char(l_ntf_rec.begin_date, 'DD-MON-YYYY HH24:MI:SS');
332: doctype := WF_NOTIFICATION.doc_html;
333: end if;
334:
335: l_cells(18) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
336: -- l_cells(18) := Wf_Notification.IsFwkBody(p_nid);
337: -- End bug 5529150
338:
339: l_cells(19) := to_char(l_ntf_rec.begin_date, 'DD-MON-YYYY HH24:MI:SS');
340: l_cells(20) := to_char(l_ntf_rec.end_date, 'DD-MON-YYYY HH24:MI:SS');
374: l_temp varchar2(32000);
375: l_cnt number := 0;
376: cursor c_grp_ntf is
377: select NOTIFICATION_ID, RECIPIENT_ROLE, STATUS, MAIL_STATUS, BEGIN_DATE, END_DATE
378: from WF_NOTIFICATIONS
379: where GROUP_ID=p_nid and
380: GROUP_ID<>NOTIFICATION_ID;
381: i number := 0;
382:
438: l_temp varchar2(32000);
439:
440: CURSOR user_curs IS
441: SELECT wur.user_name
442: FROM wf_user_roles wur, wf_notifications wn
443: WHERE wur.role_name = wn.recipient_role
444: AND wn.notification_id = p_nid;
445:
446: l_cells tdType;
538: l_cells(9) := '5%:Installed';
539:
540: SELECT recipient_role
541: INTO l_role
542: FROM wf_notifications
543: WHERE notification_id = p_nid;
544:
545: Wf_Directory.GetRoleInfoMail(l_role, l_dname, l_email, l_npref, l_lang, l_terr,
546: l_orig_sys, l_orig_id, l_install);
754: l_cells(9) := '5%:Installed';
755:
756: SELECT more_info_role
757: INTO l_role
758: FROM wf_notifications
759: WHERE notification_id = p_nid;
760:
761: if (l_role is not null) then
762:
814: wra.type,
815: nvl(nvl(wra.text_value, to_char(wra.number_value)), to_char(wra.date_value)) value
816: FROM wf_routing_rules wr,
817: wf_routing_rule_attributes wra,
818: wf_notifications wn
819: WHERE wr.rule_id = wra.rule_id (+)
820: AND (wr.role = wn.recipient_role or wr.action_argument = wn.recipient_role)
821: AND wn.notification_id = p_nid;
822:
885: wma.text_default) value,
886: wma.format format
887: from wf_message_attributes wma,
888: wf_message_attributes_tl wmat,
889: wf_notifications wn
890: where wma.message_name = wmat.message_name
891: and wma.message_type = wmat.message_type
892: and wma.name = wmat.name
893: and wmat.language = userenv('LANG')
952: select name name,
953: number_value num_val,
954: date_value date_val,
955: text_value txt_val
956: from wf_notification_attributes
957: where notification_id = p_nid;
958:
959: begin
960: dbms_lob.Trim(l_temp, 0);
1037: i := 4;
1038:
1039: select message_type, message_name, language
1040: into l_msg_type, l_msg_name, l_lang_code
1041: from wf_notifications
1042: where notification_id = p_nid;
1043:
1044: begin
1045:
1476: l_cells.DELETE;
1477:
1478: select status, mail_status, message_type, message_name, recipient_role
1479: into l_status, l_mstatus, l_msg_type, l_msg_name, l_recip_role
1480: from wf_notifications
1481: where notification_id = p_nid;
1482:
1483: if (l_mstatus not in ('MAIL')) then
1484: wf_mail.test_flag := TRUE;
1574: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1575: l_result := l_result||'E-mail Message Template in TEXT format. ('||l_nls_lang||'_'||l_nls_terr||')';
1576: l_result := l_result||'
';
1577: if (l_txt_body is not null) then
1578: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1579: else
1580: l_result := '(not defined)';
1581: end if;
1582: l_result := l_result||'
1585: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1586: l_result := l_result||'E-mail Message Template in HTML format. ('||l_nls_lang||'_'||l_nls_terr||')';
1587: l_result := l_result||'
';
1588: if (l_htm_body is not null) then
1589: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1590: else
1591: l_result := l_result||'(not defined)';
1592: end if;
1593: l_result := l_result||'
1639: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1640: l_result := l_result||'Notification Message Definition in TEXT format. ('||l_nls_lang||'_'||l_nls_terr||')';
1641: l_result := l_result||'
';
1642: if (l_txt_body is not null) then
1643: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1644: else
1645: l_result := l_result||'(not defined)';
1646: end if;
1647: l_result := l_result||'
1650: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1651: l_result := l_result||'Notification Message Definition in HTML format. ('||l_nls_lang||'_'||l_nls_terr||')';
1652: l_result := l_result||'
';
1653: if (l_htm_body is not null) then
1654: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1655: else
1656: l_result := l_result||'(not defined)';
1657: end if;
1658: l_result := l_result||'
1822: --l_result := l_result||wf_core.newline;
1823: l_result := l_result||'
';
1824:
1825: if (l_txt_body is not null) then
1826: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1827: else
1828: l_result := '(not defined or user language ' || l_user_lang ||' is not installed )';
1829: end if;
1830:
Line 1840: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1836: l_user_lang||'_'||l_user_terr||')
';
1838:
1839: if (l_htm_body is not null) then
1840: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1841: else
1842: l_result := l_result||'(not defined)';
1843: end if;
1844:
Line 1891: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1887: --l_result := l_result||wf_core.newline;
1888: l_result := l_result||'
';
1889:
1890: if (l_txt_body is not null) then
1891: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1892: else
1893: l_result := '(not defined or user language ' || l_ses_lang ||' is not installed )';
1894: end if;
1895:
Line 1905: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1901: l_ses_lang||'_'||l_ses_terr||')
1902: l_result := l_result||'';
1903:
1904: if (l_htm_body is not null) then
1905: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1906: else
1907: l_result := l_result||'(not defined)';
1908: end if;
1909:
Line 1966: p_event_name := 'oracle.apps.wf.notification.send';
1962:
1963: l_before number;
1964: l_time_taken varchar2(100);
1965: begin
1966: p_event_name := 'oracle.apps.wf.notification.send';
1967:
1968: p_event_key := p_nid;
1969:
1970: l_cells(1) := 'WH:Generate Notification Message';
Line 1982: from wf_notifications
1978:
1979: begin
1980: select recipient_role, message_type
1981: into l_role, l_msg_type
1982: from wf_notifications
1983: where notification_id = p_nid;
1984: exception
1985: when others then
1986: wf_core.context('WF_DIAGNOSTICS', 'Fetch Role', to_char(p_nid));
Line 2019: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2015: loop
2016: l_result := NULL;
2017: if (l_amount > l_chunksize) then
2018: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2019: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2020: l_amount := l_amount - l_chunksize;
2021: l_offset := l_offset + l_chunksize;
2022: l_buffer := NULL;
2023: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2026: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2022: l_buffer := NULL;
2023: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2024: else
2025: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2026: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2027: exit;
2028: end if;
2029: end loop;
2030:
Line 2046: -- Get the XML for oracle.apps.wf.notification.summary.send event
2042: end Get_Ntf_Message;
2043:
2044:
2045: -- Get_Summary_Ntf_Message
2046: -- Get the XML for oracle.apps.wf.notification.summary.send event
2047: -- Output of WF_XML.generate function
2048: --
2049: procedure Get_Summary_Ntf_Message(p_role in varchar2,
2050: p_value in out nocopy clob)
Line 2077: p_event_name := 'oracle.apps.wf.notification.summary.send';
2073: l_time_taken varchar2(100);
2074: begin
2075:
2076: -- SSTOMAR
2077: p_event_name := 'oracle.apps.wf.notification.summary.send';
2078: -- event key would be Role name.
2079: p_event_key := p_role;
2080:
2081: l_cells(1) := 'WH:Generate Summary Notification Message';
Line 2123: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2119: loop
2120: l_result := NULL;
2121: if (l_amount > l_chunksize) then
2122: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2123: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2124: l_amount := l_amount - l_chunksize;
2125: l_offset := l_offset + l_chunksize;
2126: l_buffer := NULL;
2127: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2130: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2126: l_buffer := NULL;
2127: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2128: else
2129: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2130: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2131: exit;
2132: end if;
2133: end loop;
2134:
Line 2603: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2599: end Get_Mailer_TOC;
2600:
2601: --
2602: -- Get_Ntf_Msg_From_Out
2603: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2604: --
2605: procedure Get_Ntf_Msg_From_Out(p_nid in varchar2,
2606: p_corr_id in varchar2,
2607: p_value in out nocopy clob)
Line 2617: from wf_notification_out nout
2613:
2614:
2615: cursor cout is
2616: select nout.user_data.text_lob lob
2617: from wf_notification_out nout
2618: where instr(nout.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
2619: and (p_corr_id is null or nout.corrid like p_corr_id)
2620: order by ENQ_TIME;
2621:
Line 2630: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT';
2626: l_buffer varchar2(32767);
2627:
2628: begin
2629:
2630: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT';
2631: Get_Table(l_cells, 1, 'H', l_result);
2632: l_cells.DELETE;
2633:
2634: l_result := l_result||'
';
Line 2637: ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_OUT';
2633:
2634: l_result := l_result||'
';
2635: l_result := l_result||'';
2636: l_result := l_result||'Notification Message from WF_NOTIFICATION_OUT ';
2638:
2639: for l_lob_rec in cout loop
2640: l_doc := l_lob_rec.lob;
2641:
Line 2656: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2652: loop
2653: l_result := NULL;
2654: if (l_amount > l_chunksize) then
2655: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2656: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2657: l_amount := l_amount - l_chunksize;
2658: l_offset := l_offset + l_chunksize;
2659: l_buffer := NULL;
2660: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2663: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2659: l_buffer := NULL;
2660: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2661: else
2662: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2663: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2664: exit;
2665: end if;
2666: end loop;
2667:
Line 2683: 'from WF_NOTIFICATION_OUT for nid '||p_nid;
2679: exception
2680: when others then
2681: l_cells(1) := '10%:Note';
2682: l_cells(2) := 'Error when fetching Notification Message ' ||
2683: 'from WF_NOTIFICATION_OUT for nid '||p_nid;
2684: l_cells(3) := '10%:Error';
2685: l_cells(4) := sqlerrm;
2686: Get_Table(l_cells, 2, 'V', l_result);
2687: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2692: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2688: end Get_Ntf_Msg_From_Out;
2689:
2690: --
2691: -- Get_Ntf_Msg_From_Out
2692: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2693: --
2694: procedure Get_Ntf_Msg_From_Out(p_nid in number,
2695: p_value in out nocopy clob)
2696: is
Line 2709: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2705:
2706:
2707: --
2708: -- Get_Summary_Msg_From_Out
2709: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2710: --
2711: procedure Get_Summary_Msg_From_Out(p_role in varchar2,
2712: p_value in out nocopy clob)
2713: is
Line 2732: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT AQ';
2728: l_offset pls_integer;
2729: l_buffer varchar2(32767);
2730:
2731: begin
2732: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT AQ';
2733: Get_Table(l_cells, 1, 'H', l_result);
2734: l_cells.DELETE;
2735:
2736: l_result := l_result||'
';
Line 2740: ' WF_NOTIFICATION_OUT AQ ';
2736: l_result := l_result||'
';
2737: l_result := l_result||'';
2738: l_result := l_result||'XML for Summary Message from ' ||
2740: ' WF_NOTIFICATION_OUT AQ ';
2741: l_result := l_result||'
';
2742:
2743: -- SQL string to get XML from wf_notification_out queue
2744: l_sql_str := ' select tab.user_data.text_lob '||
Line 2743: -- SQL string to get XML from wf_notification_out queue
2739: ' color='||th_fontcolor||'>XML for Summary Message from ' ||
2740: ' WF_NOTIFICATION_OUT AQ ';
2741: l_result := l_result||'
';
2742:
2743: -- SQL string to get XML from wf_notification_out queue
2744: l_sql_str := ' select tab.user_data.text_lob '||
2745: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2746: ' where instr(tab.user_data.get_string_property(''ROLE_NAME''), :p1) > 0 ' ||
2747: ' order by tab.enq_time desc';
Line 2745: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2741: l_result := l_result||'
';
2742:
2743: -- SQL string to get XML from wf_notification_out queue
2744: l_sql_str := ' select tab.user_data.text_lob '||
2745: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2746: ' where instr(tab.user_data.get_string_property(''ROLE_NAME''), :p1) > 0 ' ||
2747: ' order by tab.enq_time desc';
2748:
2749: open c_ntf_out for l_sql_str using p_role;
Line 2770: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2766: l_result := NULL;
2767:
2768: if (l_amount > l_chunksize) then
2769: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2770: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2771: l_amount := l_amount - l_chunksize;
2772: l_offset := l_offset + l_chunksize;
2773: l_buffer := NULL;
2774: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2777: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2773: l_buffer := NULL;
2774: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2775: else
2776: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2777: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2778: exit;
2779: end if;
2780: end loop;
2781:
Line 2799: ' WF_NOTIFICATION_OUT for role '||p_role;
2795: exception
2796: when others then
2797: l_cells(1) := '10%:Note';
2798: l_cells(2) := 'Error when fetching Summary Ntf. Message from ' ||
2799: ' WF_NOTIFICATION_OUT for role '||p_role;
2800: l_cells(3) := '10%:Error';
2801: l_cells(4) := sqlerrm;
2802: Get_Table(l_cells, 2, 'V', l_result);
2803: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2808: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2804: end Get_Summary_Msg_From_Out;
2805:
2806: --
2807: -- Get_Ntf_Msg_From_In
2808: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2809: --
2810: procedure Get_Ntf_Msg_From_In(p_nid in varchar2,
2811: p_corr_id in varchar2,
2812: p_value in out nocopy clob)
Line 2821: from wf_notification_in nin
2817: i pls_integer := 1;
2818:
2819: cursor cin is
2820: select nin.user_data.text_lob lob
2821: from wf_notification_in nin
2822: where instr(nin.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
2823: and (p_corr_id is null or upper(nin.corrid) like upper(p_corr_id))
2824: order by ENQ_TIME;
2825:
Line 2833: l_cells(1) := 'WH:Content from WF_NOTIFICATION_IN';
2829: l_offset pls_integer;
2830: l_buffer varchar2(32767);
2831:
2832: begin
2833: l_cells(1) := 'WH:Content from WF_NOTIFICATION_IN';
2834: Get_Table(l_cells, 1, 'H', l_result);
2835: l_cells.DELETE;
2836:
2837: l_result := l_result||'
';
Line 2840: ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_IN';
2836:
2837: l_result := l_result||'
';
2838: l_result := l_result||'';
2839: l_result := l_result||'Notification Message from WF_NOTIFICATION_IN ';
2841:
2842: for l_lob_rec in cin loop
2843: l_doc := l_lob_rec.lob;
2844: if (i=1) then
Line 2856: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2852: loop
2853: l_result := NULL;
2854: if (l_amount > l_chunksize) then
2855: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2856: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2857: l_amount := l_amount - l_chunksize;
2858: l_offset := l_offset + l_chunksize;
2859: l_buffer := NULL;
2860: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2863: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2859: l_buffer := NULL;
2860: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2861: else
2862: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2863: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2864: exit;
2865: end if;
2866: end loop;
2867:
Line 2882: l_cells(2) := 'Error when fetching Notification Message from WF_NOTIFICATION_IN for nid '||p_nid;
2878:
2879: exception
2880: when others then
2881: l_cells(1) := '10%:Note';
2882: l_cells(2) := 'Error when fetching Notification Message from WF_NOTIFICATION_IN for nid '||p_nid;
2883: l_cells(3) := '10%:Error';
2884: l_cells(4) := sqlerrm;
2885: Get_Table(l_cells, 2, 'V', l_result);
2886: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2891: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2887: end Get_Ntf_Msg_From_In;
2888:
2889: --
2890: -- Get_Ntf_Msg_From_In
2891: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2892: --
2893: procedure Get_Ntf_Msg_From_In(p_nid in number,
2894: p_value in out nocopy clob)
2895: is
Line 2904: -- using both wf_notification_attributes and wf_item_activity_statuses tables
2900: END Get_Ntf_Msg_From_In;
2901:
2902: --
2903: -- Bug 6677333: Modified the logic so that error message will be retrieved
2904: -- using both wf_notification_attributes and wf_item_activity_statuses tables
2905: --
2906: -- Get_Error_Ntf_Details
2907: -- Gets the Details of the Error Notification, if any
2908: function Get_Error_Ntf_Details(p_event_name in varchar2,
Line 2926: from wf_notifications wfn, wf_notification_attributes wfa, wf_notification_attributes wfna
2922: l_ntf_id number;
2923:
2924: cursor c_ntf is
2925: select wfn.notification_id nid, to_char(begin_date, 'DD-MON-YYYY HH:MI:SS') dt, subject ntf_sub
2926: from wf_notifications wfn, wf_notification_attributes wfa, wf_notification_attributes wfna
2927: where wfn.notification_id = wfa.notification_id and wfn.notification_id = wfna.notification_id
2928: and wfn.message_type = 'WFERROR'
2929: and wfn.message_name in ('DEFAULT_EVENT_ERROR' , 'DEFAULT_EVENT_EXT_ERROR')
2930: and wfa.name = 'EVENT_NAME'
Line 2939: from wf_notification_attributes
2935:
2936: cursor c_ntf_attr(p_nid in number) is
2937: select decode(name, 'ERROR_NAME', 'Error', 'ERROR_MESSAGE', 'Error Message',
2938: 'ERROR_STACK', 'Error Stack', name) name, text_value value
2939: from wf_notification_attributes
2940: where notification_id = p_nid
2941: and name in ('ERROR_NAME', 'ERROR_MESSAGE', 'ERROR_STACK');
2942:
2943: c_ntf_rec c_ntf%rowtype;
Line 2960: FROM wf_notifications wn,
2956:
2957: SELECT wn.notification_id, to_char(wn.begin_date, 'DD-MON-YYYY HH:MI:SS'), subject,
2958: error_message, error_name, error_stack
2959: INTO l_nid, l_date, l_subject, l_err_msg, l_err_name, l_err_stack
2960: FROM wf_notifications wn,
2961: wf_item_activity_statuses wias
2962: WHERE wn.notification_id = l_ntf_id
2963: AND wias.notification_id = l_ntf_id
2964: AND wn.message_type = wias.item_type
Line 3006: -- sent using wf_notification.send() API
3002: end if;
3003:
3004: exception
3005: -- just handle no_data_found exception in case notification is
3006: -- sent using wf_notification.send() API
3007: when no_data_found then null;
3008:
3009: end;
3010:
Line 3088: from wf_notifications
3084:
3085: begin
3086: select null
3087: into l_dummy
3088: from wf_notifications
3089: where notification_id = p_nid;
3090: exception
3091: when NO_DATA_FOUND then
3092: l_cells(1) := 'WH:Notification Id '||p_nid||' has been purged and is not available in the Database.';
Line 3143: from WF_NOTIFICATIONS
3139: -- which mail status is null and no
3140: begin
3141: select count(1)
3142: into l_group_cnt
3143: from WF_NOTIFICATIONS
3144: where GROUP_ID=p_nid;
3145: end;
3146: if l_group_cnt>1 then
3147: g_group_ntf := TRUE;
Line 3214: p_event_name => 'oracle.apps.wf.notification%',
3210: -- Queue Statuses
3211: -- Deferred queue status
3212: l_temp_result := '
'
3213: ||Get_Event_Queue_Status(p_queue_name => WFD_DEFERRED,
3214: p_event_name => 'oracle.apps.wf.notification%',
3215: p_event_key => to_char(p_nid))
3216: ||wf_core.newline;
3217: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 3224: p_event_name => 'oracle.apps.wf.notification%',
3220:
3221: -- Error Queue Status
3222: l_temp_result := '
'
3223: ||Get_Event_Queue_Status(p_queue_name => WFD_ERROR,
3224: p_event_name => 'oracle.apps.wf.notification%',
3225: p_event_key => to_char(p_nid))
3226: ||wf_core.newline;
3227: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 3233: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
3229: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3230:
3231: -- Error Notification Details
3232: l_temp_result := '
'
3233: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
3234: p_event_key => p_nid)
3235: ||wf_core.newline;
3236: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 3243: p_event_name => 'oracle.apps.wf.notification%',
3239:
3240: -- Notification OUT Queue Status
3241: l_temp_result := '
'
3242: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
3243: p_event_name => 'oracle.apps.wf.notification%',
3244: p_event_key => to_char(p_nid))
3245: ||wf_core.newline;
3246: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 3253: p_event_name => 'oracle.apps.wf.notification%',
3249:
3250: -- Notification IN Queue Status
3251: l_temp_result := '
'
3252: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_IN,
3253: p_event_name => 'oracle.apps.wf.notification%',
3254: p_event_key => to_char(p_nid))
3255: ||wf_core.newline;
3256: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 3276: -- XML Message for the notification from WF_NOTIFICATION_OUT
3272: l_temp_result := 'Go to top
'||wf_core.newline;
3274: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3275:
3276: -- XML Message for the notification from WF_NOTIFICATION_OUT
3277: l_temp_result := '
'||wf_core.newline;
3278: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3279: Get_Ntf_Msg_From_Out(p_nid, l_value);
3280: l_temp_result := 'Go to top
'||wf_core.newline;
3282: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3283:
3284: -- XML Message for the notification from WF_NOTIFICATION_IN
3285: l_temp_result := '
'||wf_core.newline;
3286: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3287: Get_Ntf_Msg_From_In(p_nid, l_value);
3288: l_temp_result := '
3464: '
' || s.NAME || ' ' sys_name,
3465: ' ' || a.status ||' ' status
3466: FROM WF_AGENTS a, WF_SYSTEMS s
3467: WHERE a.system_guid = s.guid
3468: AND a.name IN ('WF_CONTROL', 'WF_NOTIFICATION_IN', 'WF_NOTIFICATION_OUT',
3469: 'WF_DEFERRED', 'WF_ERROR');
3470:
3471: cursor c_sub is
3472: SELECT e.name EVENT_NAME,
Line 3481: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3477: DECODE(sub.out_agent_guid, NULL, 'Not Defined',
3478: oa.name || '@' || oas.name)) OUT_AGENT,
3479: sub.status STATUS
3480: FROM WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
3481: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3482: 'oracle.apps.fnd.cp.gsc.bes.control.group',
3483: 'oracle.apps.wf.notification.summary.send')
3484: AND e.guid = sub.event_filter_guid(+)
3485: AND sub.licensed_flag(+) = 'Y'
Line 3483: 'oracle.apps.wf.notification.summary.send')
3479: sub.status STATUS
3480: FROM WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
3481: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3482: 'oracle.apps.fnd.cp.gsc.bes.control.group',
3483: 'oracle.apps.wf.notification.summary.send')
3484: AND e.guid = sub.event_filter_guid(+)
3485: AND sub.licensed_flag(+) = 'Y'
3486: AND e.licensed_flag = 'Y'
3487: AND sub.system_guid = s.guid(+)
Line 4575: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4571: l_temp_result := '
' ||Get_Summary_Ntf_Role_Info(p_role)||wf_core.newline;
4572: l_temp_result := l_temp_result || '
'||wf_core.newline;
4573: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4574:
4575: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4576: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4577: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4578:
4579: -- Notification OUT Queue Status
Line 4577: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4573: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4574:
4575: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4576: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4577: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4578:
4579: -- Notification OUT Queue Status
4580: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4581: -- then need to show all of them OR XML part of each one.
Line 4580: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4576: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4577: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4578:
4579: -- Notification OUT Queue Status
4580: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4581: -- then need to show all of them OR XML part of each one.
4582:
4583: -- show number of rows in wf_notification_out AQ
4584: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
Line 4583: -- show number of rows in wf_notification_out AQ
4579: -- Notification OUT Queue Status
4580: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4581: -- then need to show all of them OR XML part of each one.
4582:
4583: -- show number of rows in wf_notification_out AQ
4584: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4585: p_event_name => 'oracle.apps.wf.notification%',
4586: p_event_key => p_role || '%')||wf_core.newline;
4587:
Line 4585: p_event_name => 'oracle.apps.wf.notification%',
4581: -- then need to show all of them OR XML part of each one.
4582:
4583: -- show number of rows in wf_notification_out AQ
4584: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4585: p_event_name => 'oracle.apps.wf.notification%',
4586: p_event_key => p_role || '%')||wf_core.newline;
4587:
4588: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4589:
Line 4603: -- XML Message for a role from WF_NOTIFICATION_OUT
4599: l_temp_result := '
'||wf_core.newline;
4600: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4601: Get_Summary_Ntf_Message(p_role, l_value );
4602:
4603: -- XML Message for a role from WF_NOTIFICATION_OUT
4604: l_temp_result := '
'||wf_core.newline;
4605: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4606: Get_Summary_Msg_From_Out(p_role, l_value);
4607:
Line 4681: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
4677: -- Error Notification Details
4678: -- Check whether wf_error aq will have ALERT in case it failes or not.
4679: --
4680: l_temp_result := '
'
4681: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
4682: p_event_key => p_idstring)
4683: ||wf_core.newline;
4684: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4685:
Line 4689: p_event_name => 'oracle.apps.wf.notification%',
4685:
4686: -- Notification OUT Queue Status
4687: l_temp_result := '
'
4688: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4689: p_event_name => 'oracle.apps.wf.notification%',
4690: p_event_key => to_char(p_idstring) || '%',
4691: p_corr_id => l_user || ':' || p_module || '%')
4692: ||wf_core.newline;
4693:
Line 4709: -- XML Message for the alert from WF_NOTIFICATION_OUT
4705:
4706: l_temp_result := '
'||wf_core.newline;
4707: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4708:
4709: -- XML Message for the alert from WF_NOTIFICATION_OUT
4710: Get_Ntf_Msg_From_Out(p_idstring, l_user || ':' || p_module || '%', l_value);
4711:
4712: l_temp_result := '
'||wf_core.newline;
4713: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
Line 4715: -- XML Message for the notification from WF_NOTIFICATION_IN
4711:
4712: l_temp_result := '
'||wf_core.newline;
4713: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4714:
4715: -- XML Message for the notification from WF_NOTIFICATION_IN
4716: Get_Ntf_Msg_From_In(p_idstring, l_user || ':' || p_module || '%', l_value);
4717:
4718: l_temp_result := '
'||wf_core.newline;
4719: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);