[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';
39: WFD_JAVA_ERROR varchar2(30) := 'WF_JAVA_ERROR';
40:
41: --
42: -- Get_Table
43: -- Implemented from WF_NOTIFICATION.NTF_Table procedure.
44: -- Returns a Vertical or Horizontal Headered table
45:
46: procedure Get_Table(p_cells in tdType,
47: p_cols in pls_integer,
145: end Get_Table;
146:
147: --
148: -- Get_Table
149: -- Implemented from WF_NOTIFICATION.NTF_Table procedure.
150: -- Returns a Vertical or Horizontal Headered table
151:
152: procedure Get_Table(p_cells in tdType,
153: p_cols in pls_integer,
272: status stat,
273: mail_status m_stat,
274: callback cb,
275: context ctx
276: from wf_notifications
277: where notification_id = p_nid;
278:
279: l_ntf_rec c_ntf%rowtype;
280: l_cells tdType;
286: l_terr varchar2(30);
287: l_orig_sys varchar2(30);
288: l_orig_id number;
289: l_install varchar2(1);
290: doctype varchar(30) := WF_NOTIFICATION.doc_html;
291:
292: begin
293: l_cells(1) := 'WH:Notification Item Information';
294: Get_table(l_cells, 1, 'H', l_result);
320: l_orig_sys, l_orig_id, l_install);
321:
322: -- Set the document type based on the notification preference.
323: if l_npref = 'MAILTEXT' then
324: doctype := WF_NOTIFICATION.doc_text;
325: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
326: doctype := WF_NOTIFICATION.doc_html;
327: end if;
328:
322: -- Set the document type based on the notification preference.
323: if l_npref = 'MAILTEXT' then
324: doctype := WF_NOTIFICATION.doc_text;
325: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
326: doctype := WF_NOTIFICATION.doc_html;
327: end if;
328:
329: l_cells(16) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
330: -- l_cells(16) := Wf_Notification.IsFwkBody(p_nid);
325: elsif l_npref in ('MAILHTML', 'MAILHTM2', 'MAILATTH') then
326: doctype := WF_NOTIFICATION.doc_html;
327: end if;
328:
329: l_cells(16) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
330: -- l_cells(16) := Wf_Notification.IsFwkBody(p_nid);
331: -- End bug 5529150
332:
333: l_cells(17) := to_char(l_ntf_rec.begin_date, 'DD-MON-YYYY HH24:MI:SS');
326: doctype := WF_NOTIFICATION.doc_html;
327: end if;
328:
329: l_cells(16) := WF_NOTIFICATION.isFwkRegion(p_nid, doctype);
330: -- l_cells(16) := Wf_Notification.IsFwkBody(p_nid);
331: -- End bug 5529150
332:
333: l_cells(17) := to_char(l_ntf_rec.begin_date, 'DD-MON-YYYY HH24:MI:SS');
334: l_cells(18) := to_char(l_ntf_rec.end_date, 'DD-MON-YYYY HH24:MI:SS');
364: l_temp varchar2(32000);
365:
366: CURSOR user_curs IS
367: SELECT wur.user_name
368: FROM wf_user_roles wur, wf_notifications wn
369: WHERE wur.role_name = wn.recipient_role
370: AND wn.notification_id = p_nid;
371:
372: l_cells tdType;
464: l_cells(9) := '5%:Installed';
465:
466: SELECT recipient_role
467: INTO l_role
468: FROM wf_notifications
469: WHERE notification_id = p_nid;
470:
471: Wf_Directory.GetRoleInfoMail(l_role, l_dname, l_email, l_npref, l_lang, l_terr,
472: l_orig_sys, l_orig_id, l_install);
680: l_cells(9) := '5%:Installed';
681:
682: SELECT more_info_role
683: INTO l_role
684: FROM wf_notifications
685: WHERE notification_id = p_nid;
686:
687: if (l_role is not null) then
688:
740: wra.type,
741: nvl(nvl(wra.text_value, to_char(wra.number_value)), to_char(wra.date_value)) value
742: FROM wf_routing_rules wr,
743: wf_routing_rule_attributes wra,
744: wf_notifications wn
745: WHERE wr.rule_id = wra.rule_id (+)
746: AND (wr.role = wn.recipient_role or wr.action_argument = wn.recipient_role)
747: AND wn.notification_id = p_nid;
748:
811: wma.text_default) value,
812: wma.format format
813: from wf_message_attributes wma,
814: wf_message_attributes_tl wmat,
815: wf_notifications wn
816: where wma.message_name = wmat.message_name
817: and wma.message_type = wmat.message_type
818: and wma.name = wmat.name
819: and wmat.language = userenv('LANG')
878: select name name,
879: number_value num_val,
880: date_value date_val,
881: text_value txt_val
882: from wf_notification_attributes
883: where notification_id = p_nid;
884:
885: begin
886: dbms_lob.Trim(l_temp, 0);
1298: l_cells.DELETE;
1299:
1300: select status, mail_status, message_type, message_name, recipient_role
1301: into l_status, l_mstatus, l_msg_type, l_msg_name, l_recip_role
1302: from wf_notifications
1303: where notification_id = p_nid;
1304:
1305: if (l_mstatus not in ('MAIL')) then
1306: wf_mail.test_flag := TRUE;
1396: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1397: l_result := l_result||'E-mail Message Template in TEXT format. ('||l_nls_lang||'_'||l_nls_terr||')';
1398: l_result := l_result||'
';
1399: if (l_txt_body is not null) then
1400: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1401: else
1402: l_result := '(not defined)';
1403: end if;
1404: l_result := l_result||'
1407: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1408: l_result := l_result||'E-mail Message Template in HTML format. ('||l_nls_lang||'_'||l_nls_terr||')';
1409: l_result := l_result||'
';
1410: if (l_htm_body is not null) then
1411: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1412: else
1413: l_result := l_result||'(not defined)';
1414: end if;
1415: l_result := l_result||'
1452: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1453: l_result := l_result||'Notification Message Definition in TEXT format. ('||l_nls_lang||'_'||l_nls_terr||')';
1454: l_result := l_result||'
';
1455: if (l_txt_body is not null) then
1456: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1457: else
1458: l_result := '(not defined)';
1459: end if;
1460: l_result := l_result||'
1463: ' size='||th_fontsize||' color='||th_fontcolor||'>';
1464: l_result := l_result||'Notification Message Definition in HTML format. ('||l_nls_lang||'_'||l_nls_terr||')';
1465: l_result := l_result||'
';
1466: if (l_htm_body is not null) then
1467: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1468: else
1469: l_result := l_result||'(not defined)';
1470: end if;
1471: l_result := l_result||'
1635: --l_result := l_result||wf_core.newline;
1636: l_result := l_result||'
';
1637:
1638: if (l_txt_body is not null) then
1639: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1640: else
1641: l_result := '(not defined or user language ' || l_user_lang ||' is not installed )';
1642: end if;
1643:
Line 1653: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1649: l_user_lang||'_'||l_user_terr||')
';
1651:
1652: if (l_htm_body is not null) then
1653: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1654: else
1655: l_result := l_result||'(not defined)';
1656: end if;
1657:
Line 1704: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1700: --l_result := l_result||wf_core.newline;
1701: l_result := l_result||'
';
1702:
1703: if (l_txt_body is not null) then
1704: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_txt_body);
1705: else
1706: l_result := '(not defined or user language ' || l_ses_lang ||' is not installed )';
1707: end if;
1708:
Line 1718: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1714: l_ses_lang||'_'||l_ses_terr||')
1715: l_result := l_result||'';
1716:
1717: if (l_htm_body is not null) then
1718: l_result := l_result||Wf_Notification.SubstituteSpecialChars(l_htm_body);
1719: else
1720: l_result := l_result||'(not defined)';
1721: end if;
1722:
Line 1779: p_event_name := 'oracle.apps.wf.notification.send';
1775:
1776: l_before number;
1777: l_time_taken varchar2(100);
1778: begin
1779: p_event_name := 'oracle.apps.wf.notification.send';
1780:
1781: p_event_key := p_nid;
1782:
1783: l_cells(1) := 'WH:Generate Notification Message';
Line 1795: from wf_notifications
1791:
1792: begin
1793: select recipient_role, message_type
1794: into l_role, l_msg_type
1795: from wf_notifications
1796: where notification_id = p_nid;
1797: exception
1798: when others then
1799: wf_core.context('WF_DIAGNOSTICS', 'Fetch Role', to_char(p_nid));
Line 1832: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1828: loop
1829: l_result := NULL;
1830: if (l_amount > l_chunksize) then
1831: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
1832: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1833: l_amount := l_amount - l_chunksize;
1834: l_offset := l_offset + l_chunksize;
1835: l_buffer := NULL;
1836: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 1839: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1835: l_buffer := NULL;
1836: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1837: else
1838: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
1839: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1840: exit;
1841: end if;
1842: end loop;
1843:
Line 1859: -- Get the XML for oracle.apps.wf.notification.summary.send event
1855: end Get_Ntf_Message;
1856:
1857:
1858: -- Get_Summary_Ntf_Message
1859: -- Get the XML for oracle.apps.wf.notification.summary.send event
1860: -- Output of WF_XML.generate function
1861: --
1862: procedure Get_Summary_Ntf_Message(p_role in varchar2,
1863: p_value in out nocopy clob)
Line 1890: p_event_name := 'oracle.apps.wf.notification.summary.send';
1886: l_time_taken varchar2(100);
1887: begin
1888:
1889: -- SSTOMAR
1890: p_event_name := 'oracle.apps.wf.notification.summary.send';
1891: -- event key would be Role name.
1892: p_event_key := p_role;
1893:
1894: l_cells(1) := 'WH:Generate Summary Notification Message';
Line 1936: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1932: loop
1933: l_result := NULL;
1934: if (l_amount > l_chunksize) then
1935: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
1936: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1937: l_amount := l_amount - l_chunksize;
1938: l_offset := l_offset + l_chunksize;
1939: l_buffer := NULL;
1940: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 1943: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1939: l_buffer := NULL;
1940: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1941: else
1942: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
1943: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
1944: exit;
1945: end if;
1946: end loop;
1947:
Line 2363: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2359: end Get_Mailer_TOC;
2360:
2361: --
2362: -- Get_Ntf_Msg_From_Out
2363: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2364: --
2365: procedure Get_Ntf_Msg_From_Out(p_nid in varchar2,
2366: p_corr_id in varchar2,
2367: p_value in out nocopy clob)
Line 2377: from wf_notification_out nout
2373:
2374:
2375: cursor cout is
2376: select nout.user_data.text_lob lob
2377: from wf_notification_out nout
2378: where instr(nout.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
2379: and (p_corr_id is null or nout.corrid like p_corr_id)
2380: order by ENQ_TIME;
2381:
Line 2390: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT';
2386: l_buffer varchar2(32767);
2387:
2388: begin
2389:
2390: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT';
2391: Get_Table(l_cells, 1, 'H', l_result);
2392: l_cells.DELETE;
2393:
2394: l_result := l_result||'
';
Line 2397: ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_OUT';
2393:
2394: l_result := l_result||'
';
2395: l_result := l_result||'';
2396: l_result := l_result||'Notification Message from WF_NOTIFICATION_OUT ';
2398:
2399: for l_lob_rec in cout loop
2400: l_doc := l_lob_rec.lob;
2401:
Line 2416: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2412: loop
2413: l_result := NULL;
2414: if (l_amount > l_chunksize) then
2415: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2416: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2417: l_amount := l_amount - l_chunksize;
2418: l_offset := l_offset + l_chunksize;
2419: l_buffer := NULL;
2420: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2423: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2419: l_buffer := NULL;
2420: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2421: else
2422: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2423: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2424: exit;
2425: end if;
2426: end loop;
2427:
Line 2443: 'from WF_NOTIFICATION_OUT for nid '||p_nid;
2439: exception
2440: when others then
2441: l_cells(1) := '10%:Note';
2442: l_cells(2) := 'Error when fetching Notification Message ' ||
2443: 'from WF_NOTIFICATION_OUT for nid '||p_nid;
2444: l_cells(3) := '10%:Error';
2445: l_cells(4) := sqlerrm;
2446: Get_Table(l_cells, 2, 'V', l_result);
2447: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2452: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2448: end Get_Ntf_Msg_From_Out;
2449:
2450: --
2451: -- Get_Ntf_Msg_From_Out
2452: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2453: --
2454: procedure Get_Ntf_Msg_From_Out(p_nid in number,
2455: p_value in out nocopy clob)
2456: is
Line 2469: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2465:
2466:
2467: --
2468: -- Get_Summary_Msg_From_Out
2469: -- Fetches the notification content from WF_NOTIFICATION_OUT queue
2470: --
2471: procedure Get_Summary_Msg_From_Out(p_role in varchar2,
2472: p_value in out nocopy clob)
2473: is
Line 2492: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT AQ';
2488: l_offset pls_integer;
2489: l_buffer varchar2(32767);
2490:
2491: begin
2492: l_cells(1) := 'WH:Content from WF_NOTIFICATION_OUT AQ';
2493: Get_Table(l_cells, 1, 'H', l_result);
2494: l_cells.DELETE;
2495:
2496: l_result := l_result||'
';
Line 2500: ' WF_NOTIFICATION_OUT AQ ';
2496: l_result := l_result||'
';
2497: l_result := l_result||'';
2498: l_result := l_result||'XML for Summary Message from ' ||
2500: ' WF_NOTIFICATION_OUT AQ ';
2501: l_result := l_result||'
';
2502:
2503: -- SQL string to get XML from wf_notification_out queue
2504: l_sql_str := ' select tab.user_data.text_lob '||
Line 2503: -- SQL string to get XML from wf_notification_out queue
2499: ' color='||th_fontcolor||'>XML for Summary Message from ' ||
2500: ' WF_NOTIFICATION_OUT AQ ';
2501: l_result := l_result||'
';
2502:
2503: -- SQL string to get XML from wf_notification_out queue
2504: l_sql_str := ' select tab.user_data.text_lob '||
2505: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2506: ' where instr(tab.user_data.get_string_property(''ROLE_NAME''), :p1) > 0 ' ||
2507: ' order by tab.enq_time desc';
Line 2505: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2501: l_result := l_result||'
';
2502:
2503: -- SQL string to get XML from wf_notification_out queue
2504: l_sql_str := ' select tab.user_data.text_lob '||
2505: ' from '||g_qowner||'.aq$wf_notification_out tab' ||
2506: ' where instr(tab.user_data.get_string_property(''ROLE_NAME''), :p1) > 0 ' ||
2507: ' order by tab.enq_time desc';
2508:
2509: open c_ntf_out for l_sql_str using p_role;
Line 2530: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2526: l_result := NULL;
2527:
2528: if (l_amount > l_chunksize) then
2529: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2530: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2531: l_amount := l_amount - l_chunksize;
2532: l_offset := l_offset + l_chunksize;
2533: l_buffer := NULL;
2534: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2537: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2533: l_buffer := NULL;
2534: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2535: else
2536: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2537: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2538: exit;
2539: end if;
2540: end loop;
2541:
Line 2559: ' WF_NOTIFICATION_OUT for role '||p_role;
2555: exception
2556: when others then
2557: l_cells(1) := '10%:Note';
2558: l_cells(2) := 'Error when fetching Summary Ntf. Message from ' ||
2559: ' WF_NOTIFICATION_OUT for role '||p_role;
2560: l_cells(3) := '10%:Error';
2561: l_cells(4) := sqlerrm;
2562: Get_Table(l_cells, 2, 'V', l_result);
2563: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2568: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2564: end Get_Summary_Msg_From_Out;
2565:
2566: --
2567: -- Get_Ntf_Msg_From_In
2568: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2569: --
2570: procedure Get_Ntf_Msg_From_In(p_nid in varchar2,
2571: p_corr_id in varchar2,
2572: p_value in out nocopy clob)
Line 2581: from wf_notification_in nin
2577: i pls_integer := 1;
2578:
2579: cursor cin is
2580: select nin.user_data.text_lob lob
2581: from wf_notification_in nin
2582: where instr(nin.user_data.get_string_property('BES_EVENT_KEY'), p_nid) > 0
2583: and (p_corr_id is null or upper(nin.corrid) like upper(p_corr_id))
2584: order by ENQ_TIME;
2585:
Line 2593: l_cells(1) := 'WH:Content from WF_NOTIFICATION_IN';
2589: l_offset pls_integer;
2590: l_buffer varchar2(32767);
2591:
2592: begin
2593: l_cells(1) := 'WH:Content from WF_NOTIFICATION_IN';
2594: Get_Table(l_cells, 1, 'H', l_result);
2595: l_cells.DELETE;
2596:
2597: l_result := l_result||'
';
Line 2600: ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_IN';
2596:
2597: l_result := l_result||'
';
2598: l_result := l_result||'';
2599: l_result := l_result||'Notification Message from WF_NOTIFICATION_IN ';
2601:
2602: for l_lob_rec in cin loop
2603: l_doc := l_lob_rec.lob;
2604: if (i=1) then
Line 2616: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2612: loop
2613: l_result := NULL;
2614: if (l_amount > l_chunksize) then
2615: dbms_lob.read(l_doc, l_chunksize, l_offset, l_buffer);
2616: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2617: l_amount := l_amount - l_chunksize;
2618: l_offset := l_offset + l_chunksize;
2619: l_buffer := NULL;
2620: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2623: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2619: l_buffer := NULL;
2620: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2621: else
2622: dbms_lob.read(l_doc, l_amount, l_offset, l_buffer);
2623: l_result := Wf_Notification.SubstituteSpecialChars(l_buffer);
2624: exit;
2625: end if;
2626: end loop;
2627:
Line 2642: l_cells(2) := 'Error when fetching Notification Message from WF_NOTIFICATION_IN for nid '||p_nid;
2638:
2639: exception
2640: when others then
2641: l_cells(1) := '10%:Note';
2642: l_cells(2) := 'Error when fetching Notification Message from WF_NOTIFICATION_IN for nid '||p_nid;
2643: l_cells(3) := '10%:Error';
2644: l_cells(4) := sqlerrm;
2645: Get_Table(l_cells, 2, 'V', l_result);
2646: dbms_lob.WriteAppend(p_value, length(l_result), l_result);
Line 2651: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2647: end Get_Ntf_Msg_From_In;
2648:
2649: --
2650: -- Get_Ntf_Msg_From_In
2651: -- Fetches the notification content from WF_NOTIFICATION_IN queue
2652: --
2653: procedure Get_Ntf_Msg_From_In(p_nid in number,
2654: p_value in out nocopy clob)
2655: is
Line 2677: from wf_notifications wfn, wf_notification_attributes wfa, wf_notification_attributes wfna
2673: i pls_integer := 1;
2674:
2675: cursor c_ntf is
2676: select wfn.notification_id nid, to_char(begin_date, 'DD-MON-YYYY HH:MI:SS') dt, subject ntf_sub
2677: from wf_notifications wfn, wf_notification_attributes wfa, wf_notification_attributes wfna
2678: where wfn.notification_id=wfa.notification_id and wfn.notification_id=wfna.notification_id
2679: and wfn.message_type = 'WFERROR'
2680: and wfn.message_name = 'DEFAULT_EVENT_ERROR'
2681: and wfa.name = 'EVENT_NAME'
Line 2690: from wf_notification_attributes
2686:
2687: cursor c_ntf_attr(p_nid in number) is
2688: select decode(name, 'ERROR_NAME', 'Error', 'ERROR_MESSAGE', 'Error Message',
2689: 'ERROR_STACK', 'Error Stack', name) name, text_value value
2690: from wf_notification_attributes
2691: where notification_id = p_nid
2692: and name in ('ERROR_NAME', 'ERROR_MESSAGE', 'ERROR_STACK');
2693:
2694: c_ntf_rec c_ntf%rowtype;
Line 2776: from wf_notifications
2772:
2773: begin
2774: select null
2775: into l_dummy
2776: from wf_notifications
2777: where notification_id = p_nid;
2778: exception
2779: when NO_DATA_FOUND then
2780: l_cells(1) := 'WH:Notification Id '||p_nid||' has been purged and is not available in the Database.';
Line 2871: p_event_name => 'oracle.apps.wf.notification%',
2867: -- Queue Statuses
2868: -- Deferred queue status
2869: l_temp_result := '
'
2870: ||Get_Event_Queue_Status(p_queue_name => WFD_DEFERRED,
2871: p_event_name => 'oracle.apps.wf.notification%',
2872: p_event_key => to_char(p_nid))
2873: ||wf_core.newline;
2874: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 2881: p_event_name => 'oracle.apps.wf.notification%',
2877:
2878: -- Error Queue Status
2879: l_temp_result := '
'
2880: ||Get_Event_Queue_Status(p_queue_name => WFD_ERROR,
2881: p_event_name => 'oracle.apps.wf.notification%',
2882: p_event_key => to_char(p_nid))
2883: ||wf_core.newline;
2884: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 2890: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
2886: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
2887:
2888: -- Error Notification Details
2889: l_temp_result := '
'
2890: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
2891: p_event_key => p_nid)
2892: ||wf_core.newline;
2893: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 2900: p_event_name => 'oracle.apps.wf.notification%',
2896:
2897: -- Notification OUT Queue Status
2898: l_temp_result := '
'
2899: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
2900: p_event_name => 'oracle.apps.wf.notification%',
2901: p_event_key => to_char(p_nid))
2902: ||wf_core.newline;
2903: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 2910: p_event_name => 'oracle.apps.wf.notification%',
2906:
2907: -- Notification IN Queue Status
2908: l_temp_result := '
'
2909: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_IN,
2910: p_event_name => 'oracle.apps.wf.notification%',
2911: p_event_key => to_char(p_nid))
2912: ||wf_core.newline;
2913: l_temp_result := l_temp_result||'Go to top
'||wf_core.newline;
Line 2933: -- XML Message for the notification from WF_NOTIFICATION_OUT
2929: l_temp_result := 'Go to top
'||wf_core.newline;
2931: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
2932:
2933: -- XML Message for the notification from WF_NOTIFICATION_OUT
2934: l_temp_result := '
'||wf_core.newline;
2935: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
2936: Get_Ntf_Msg_From_Out(p_nid, l_value);
2937: l_temp_result := 'Go to top
'||wf_core.newline;
2939: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
2940:
2941: -- XML Message for the notification from WF_NOTIFICATION_IN
2942: l_temp_result := '
'||wf_core.newline;
2943: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
2944: Get_Ntf_Msg_From_In(p_nid, l_value);
2945: l_temp_result := '
3113: '
' || s.NAME || ' ' sys_name,
3114: ' ' || a.status ||' ' status
3115: FROM WF_AGENTS a, WF_SYSTEMS s
3116: WHERE a.system_guid = s.guid
3117: AND a.name IN ('WF_CONTROL', 'WF_NOTIFICATION_IN', 'WF_NOTIFICATION_OUT',
3118: 'WF_DEFERRED', 'WF_ERROR');
3119:
3120: cursor c_sub is
3121: SELECT e.name EVENT_NAME,
Line 3130: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3126: DECODE(sub.out_agent_guid, NULL, 'Not Defined',
3127: oa.name || '@' || oas.name)) OUT_AGENT,
3128: sub.status STATUS
3129: FROM WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
3130: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3131: 'oracle.apps.fnd.cp.gsc.bes.control.group',
3132: 'oracle.apps.wf.notification.summary.send')
3133: AND e.guid = sub.event_filter_guid(+)
3134: AND sub.licensed_flag(+) = 'Y'
Line 3132: 'oracle.apps.wf.notification.summary.send')
3128: sub.status STATUS
3129: FROM WF_EVENTS e, WF_SYSTEMS s, WF_EVENT_SUBSCRIPTIONS sub, WF_AGENTS oa, WF_SYSTEMS oas
3130: WHERE e.NAME IN ('oracle.apps.wf.notification.send.group',
3131: 'oracle.apps.fnd.cp.gsc.bes.control.group',
3132: 'oracle.apps.wf.notification.summary.send')
3133: AND e.guid = sub.event_filter_guid(+)
3134: AND sub.licensed_flag(+) = 'Y'
3135: AND e.licensed_flag = 'Y'
3136: AND sub.system_guid = s.guid(+)
Line 4224: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4220: l_temp_result := '
' ||Get_Summary_Ntf_Role_Info(p_role)||wf_core.newline;
4221: l_temp_result := l_temp_result || '
'||wf_core.newline;
4222: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4223:
4224: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4225: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4226: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4227:
4228: -- Notification OUT Queue Status
Line 4226: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4222: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4223:
4224: -- Queue Statuses : Since summary ntf always enqued in wf_notification_out.
4225: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4226: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4227:
4228: -- Notification OUT Queue Status
4229: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4230: -- then need to show all of them OR XML part of each one.
Line 4229: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4225: -- If SUMHTML or SUMMARY then contents are fetched from Fwk API :
4226: -- Detail can be seen at WF_NOTIFICATION.getSummaryURL
4227:
4228: -- Notification OUT Queue Status
4229: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4230: -- then need to show all of them OR XML part of each one.
4231:
4232: -- show number of rows in wf_notification_out AQ
4233: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
Line 4232: -- show number of rows in wf_notification_out AQ
4228: -- Notification OUT Queue Status
4229: -- If a Role has more than one notifications in WF_NOTIFICATION_OUT table
4230: -- then need to show all of them OR XML part of each one.
4231:
4232: -- show number of rows in wf_notification_out AQ
4233: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4234: p_event_name => 'oracle.apps.wf.notification%',
4235: p_event_key => p_role || '%')||wf_core.newline;
4236:
Line 4234: p_event_name => 'oracle.apps.wf.notification%',
4230: -- then need to show all of them OR XML part of each one.
4231:
4232: -- show number of rows in wf_notification_out AQ
4233: l_temp_result := '
' ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4234: p_event_name => 'oracle.apps.wf.notification%',
4235: p_event_key => p_role || '%')||wf_core.newline;
4236:
4237: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4238:
Line 4252: -- XML Message for a role from WF_NOTIFICATION_OUT
4248: l_temp_result := '
'||wf_core.newline;
4249: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4250: Get_Summary_Ntf_Message(p_role, l_value );
4251:
4252: -- XML Message for a role from WF_NOTIFICATION_OUT
4253: l_temp_result := '
'||wf_core.newline;
4254: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4255: Get_Summary_Msg_From_Out(p_role, l_value);
4256:
Line 4330: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
4326: -- Error Notification Details
4327: -- Check whether wf_error aq will have ALERT in case it failes or not.
4328: --
4329: l_temp_result := '
'
4330: ||Get_Error_Ntf_Details(p_event_name => 'oracle.apps.wf.notification.%',
4331: p_event_key => p_idstring)
4332: ||wf_core.newline;
4333: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4334:
Line 4338: p_event_name => 'oracle.apps.wf.notification%',
4334:
4335: -- Notification OUT Queue Status
4336: l_temp_result := '
'
4337: ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_OUT,
4338: p_event_name => 'oracle.apps.wf.notification%',
4339: p_event_key => to_char(p_idstring) || '%',
4340: p_corr_id => l_user || ':' || p_module || '%')
4341: ||wf_core.newline;
4342:
Line 4358: -- XML Message for the alert from WF_NOTIFICATION_OUT
4354:
4355: l_temp_result := '
'||wf_core.newline;
4356: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4357:
4358: -- XML Message for the alert from WF_NOTIFICATION_OUT
4359: Get_Ntf_Msg_From_Out(p_idstring, l_user || ':' || p_module || '%', l_value);
4360:
4361: l_temp_result := '
'||wf_core.newline;
4362: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
Line 4364: -- XML Message for the notification from WF_NOTIFICATION_IN
4360:
4361: l_temp_result := '
'||wf_core.newline;
4362: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4363:
4364: -- XML Message for the notification from WF_NOTIFICATION_IN
4365: Get_Ntf_Msg_From_In(p_idstring, l_user || ':' || p_module || '%', l_value);
4366:
4367: l_temp_result := '
'||wf_core.newline;
4368: dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);