[Home] [Help]
PACKAGE BODY: APPS.WF_DIAGNOSTICS
Source
1 package body WF_DIAGNOSTICS as
2 /* $Header: WFDIAGPB.pls 120.17.12020000.5 2013/04/09 17:34:50 skandepu ship $ */
3
4 -- Table attributes in line with BLAF standards
5 table_width varchar2(8) := '100%';
6 table_border varchar2(2) := '0';
7 table_bgcolor varchar2(7) := 'white';
8
9 -- Header attributes
10 th_bgcolor varchar2(7) := wf_mail.g_th_bgcolor;
11 th_fontcolor varchar2(7) := wf_mail.g_th_fontcolor;
12 th_fontface varchar2(80) := wf_mail.g_th_fontface;
13 th_fontsize varchar2(2) := wf_mail.g_th_fontsize;
14
15 -- Cell attributes
16 td_bgcolor varchar2(7) := wf_mail.g_td_bgcolor;
17 td_fontcolor varchar2(7) := wf_mail.g_td_fontcolor;
18 td_fontface varchar2(80) := wf_mail.g_td_fontface;
19 td_fontsize varchar2(2) := wf_mail.g_td_fontsize;
20
21 -- Header
22 g_head varchar2(50) := '<html><body>';
23 g_end varchar2(50) := '</body></html>';
24
25 -- Temp CLOB
26 l_temp CLOB;
27
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';
37 WFD_PROV_IN varchar2(30) := 'WF_PROV_IN';
38 WFD_JAVA_DEFERRED varchar2(30) := 'WF_JAVA_DEFERRED';
39 WFD_JAVA_ERROR varchar2(30) := 'WF_JAVA_ERROR';
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,
49 p_dir in varchar2,
50 p_table in out nocopy varchar2)
51 is
52 l_table_width varchar2(8);
53 l_table_border varchar2(1);
54 l_table_bgcolor varchar2(7);
55 l_th_bgcolor varchar2(7);
56 l_th_fontcolor varchar2(7);
57 l_th_fontface varchar2(80);
58 l_th_fontsize varchar2(2);
59 l_td_bgcolor varchar2(7);
60 l_td_fontcolor varchar2(7);
61 l_td_fontface varchar2(80);
62 l_td_fontsize varchar2(2);
63 i pls_integer;
64 l_mod pls_integer;
65 l_colon pls_integer;
66 l_width varchar2(10);
67 l_text varchar2(4000);
68 l_table varchar2(32000);
69 l_rowcolor varchar2(10);
70 begin
71
72 l_table_width := table_width;
73 l_table_border := table_border;
74 l_table_bgcolor:= table_bgcolor;
75 l_th_bgcolor := th_bgcolor;
76 l_th_fontcolor := th_fontcolor;
77 l_th_fontface := th_fontface;
78 l_th_fontsize := th_fontsize;
79 l_td_bgcolor := td_bgcolor;
80 l_td_fontcolor := td_fontcolor;
81 l_td_fontface := td_fontface;
82 l_td_fontsize := td_fontsize;
83
84 if (p_cells.COUNT = 0) then
85 p_table := null;
86 return;
87 end if;
88
89 l_table := '<table width='||l_table_width||
90 ' border='||l_table_border||
91 ' bgcolor='||l_table_bgcolor||'>';
92
93 if (p_cells.COUNT = 1) then
94 l_rowcolor := substr(p_cells(1), 1, 3);
95 l_text := substr(p_cells(1), 4);
96 if (l_rowcolor = 'TD:') then
97 l_table := l_table||'<tr><td bgcolor='||l_td_bgcolor||'><font color='||l_td_fontcolor||
98 ' face="'||l_td_fontface||'"'||' size='||l_td_fontsize||'>'||
99 l_text||'</font></td></tr>';
100 else
101 l_table := l_table||'<tr><td><font color='||l_td_fontcolor||' face="'||l_td_fontface||'"'||
102 ' size='||l_td_fontsize||'>'||l_text||'</font></td></tr>';
103 end if;
104 else
105 for i in 1..p_cells.LAST loop
106 l_mod := mod(i, p_cols);
107 if (l_mod = 1) then
108 l_table := l_table || '<tr>';
109 end if;
110
111 l_text := p_cells(i);
112 if ((p_dir = 'V' and l_mod = 1) or (p_dir = 'H' and i <= p_cols)) then
113 l_colon := instr(l_text,':');
114 l_width := substr(l_text, 1, l_colon-1);
115 l_text := substr(l_text, l_colon+1);
116 l_table := l_table||wf_core.newline||'<th align=left';
117
118 if (l_width is not null) then
119 l_table := l_table||' width='||l_width;
120 end if;
121 l_table := l_table||' bgcolor='||l_th_bgcolor||'>';
122 l_table := l_table||'<font color='||l_th_fontcolor||' face="'||l_th_fontface||'"'
123 ||' size='||l_th_fontsize||'>';
124 l_table := l_table|| l_text||'</font>';
125 l_table := l_table||'</th>';
126 else
127 l_table := l_table||wf_core.newline||'<td';
128 l_table := l_table||' bgcolor='||l_td_bgcolor||'>';
129 l_table := l_table||'<font color='||l_td_fontcolor||' face="'||l_td_fontface||'"'
130 ||' size='||l_td_fontsize||'>';
131 l_table := l_table||l_text||'</font></td>';
132 end if;
133
134 if (l_mod = 0) then
135 l_table := l_table||'</tr>';
136 end if;
137 end loop;
138 end if;
139 l_table := l_table || '</table>';
140 p_table := l_table;
141 return;
142 exception
143 when others then
144 wf_core.context('WF_DIAGNOSTICS', 'Get_Table', 'Varchar2 Table');
145 raise;
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,
155 p_dir in varchar2,
156 p_table in out nocopy CLOB)
157 is
158 l_table_width varchar2(8);
159 l_table_border varchar2(1);
160 l_table_bgcolor varchar2(7);
161 l_th_bgcolor varchar2(7);
162 l_th_fontcolor varchar2(7);
163 l_th_fontface varchar2(80);
164 l_th_fontsize varchar2(2);
165 l_td_bgcolor varchar2(7);
166 l_td_fontcolor varchar2(7);
167 l_td_fontface varchar2(80);
168 l_td_fontsize varchar2(2);
169
170 i pls_integer;
171 l_mod pls_integer;
172 l_colon pls_integer;
173 l_width varchar2(10);
174 l_text varchar2(4000);
175 l_table varchar2(32000);
176 l_rowcolor varchar2(10);
177 begin
178
179 l_table_width := table_width;
180 l_table_border := table_border;
181 l_table_bgcolor := table_bgcolor;
182 l_th_bgcolor := th_bgcolor;
183 l_th_fontcolor := th_fontcolor;
184 l_th_fontface := th_fontface;
185 l_th_fontsize := th_fontsize;
186 l_td_bgcolor := td_bgcolor;
187 l_td_fontcolor := td_fontcolor;
188 l_td_fontface := td_fontface;
189 l_td_fontsize := td_fontsize;
190
191 if (p_cells.COUNT = 0) then
192 p_table := null;
193 return;
194 end if;
195
196 l_table := '<table width='||l_table_width||
197 ' border='||l_table_border||
198 ' bgcolor='||l_table_bgcolor||'>';
199
200 if (p_cells.COUNT = 1) then
201 l_rowcolor := substr(p_cells(1), 1, 3);
202 l_text := substr(p_cells(1), 4);
203 if (l_rowcolor = 'TD:') then
204 l_table := l_table||'<tr><td bgcolor='||l_td_bgcolor||'><font color='||l_td_fontcolor||
205 ' face="'||l_td_fontface||'"'||' size='||l_td_fontsize||'>'||
206 l_text||'</font></td></tr>';
207 else
208 l_table := l_table||'<tr><td><font color='||l_td_fontcolor||' face="'||l_td_fontface||'"'||
209 ' size='||l_td_fontsize||'>'||l_text||'</font></td></tr>';
210 end if;
211 else
212 for i in 1..p_cells.LAST loop
213 l_mod := mod(i, p_cols);
214 if (l_mod = 1) then
215 l_table := l_table || '<tr>';
216 end if;
217
218 l_text := p_cells(i);
219 if ((p_dir = 'V' and l_mod = 1) or (p_dir = 'H' and i <= p_cols)) then
220 l_colon := instr(l_text,':');
221 l_width := substr(l_text, 1, l_colon-1);
222 l_text := substr(l_text, l_colon+1);
223 l_table := l_table||wf_core.newline||'<th align=left';
224
225 if (l_width is not null) then
226 l_table := l_table||' width='||l_width;
227 end if;
228 l_table := l_table||' bgcolor='||l_th_bgcolor||'>';
229 l_table := l_table||'<font color='||l_th_fontcolor||' face="'||l_th_fontface||'"'
230 ||' size='||l_th_fontsize||'>';
231 l_table := l_table|| l_text||'</font>';
232 l_table := l_table||'</th>';
233 else
234 l_table := l_table||wf_core.newline||'<td';
235 l_table := l_table||' bgcolor='||l_td_bgcolor||'>';
236 l_table := l_table||'<font color='||l_td_fontcolor||' face="'||l_td_fontface||'"'
237 ||' size='||l_td_fontsize||'>';
238 l_table := l_table||l_text||'</font></td>';
239 end if;
240
241 if (l_mod = 0) then
242 l_table := l_table||'</tr>';
243 end if;
244 dbms_lob.WriteAppend(p_table, length(l_table), l_table);
245 l_table := '';
246 end loop;
247 end if;
248 l_table := '</table>';
249 dbms_lob.WriteAppend(p_table, length(l_table), l_table);
250 return;
251 exception
252 when others then
253 wf_core.context('WF_DIAGNOSTICS', 'Get_Table', 'CLOB Table');
254 raise;
255 end Get_Table;
256
257 --
258 -- Get_Ntf_Item_Info - <Explained in WFDIAGPS.pls>
259 --
260 function Get_Ntf_Item_Info(p_nid in number)
261 return varchar2
262 is
263 l_result varchar2(32000);
264 l_temp varchar2(32000);
265 cursor c_ntf is
266 select notification_id nid,
267 message_type msg_type,
268 message_name msg_name,
269 begin_date,
270 end_date,
271 recipient_role rec_role,
272 more_info_role more_role,
273 status stat,
274 mail_status m_stat,
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;
284 l_role varchar2(320);
285 l_dname varchar2(360);
286 l_email varchar2(320);
287 l_npref varchar2(8);
288 l_lang varchar2(30);
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:<b>Notification Item Information</b>';
297 Get_table(l_cells, 1, 'H', l_result);
298 l_cells.DELETE;
299
300 l_cells(1) := 'Notification Id';
301 l_cells(2) := 'Message Type';
302 l_cells(3) := 'Message Name';
303 l_cells(4) := 'Fwk Content';
304 l_cells(5) := 'Begin Date';
305 l_cells(6) := 'End Date';
306 l_cells(7) := 'Recipient Role';
307 l_cells(8) := 'More Info Role';
308 l_cells(9) := 'Status';
309 l_cells(10) := 'Mail Status';
310 l_cells(11) := 'Call back';
311 l_cells(12) := 'Context';
312 l_cells(13) := 'Responder';
313 l_cells(14) := 'Subject';
314
315
316 open c_ntf;
317 fetch c_ntf into l_ntf_rec;
318 l_cells(15) := l_ntf_rec.nid;
319 l_cells(16) := l_ntf_rec.msg_type;
320 l_cells(17) := l_ntf_rec.msg_name;
321 -- Begin bug#5529150
322 -- Get role from current cursure
323 l_role := l_ntf_rec.rec_role;
324 -- get Role's preferences
325 Wf_Directory.GetRoleInfoMail(l_role, l_dname, l_email, l_npref, l_lang, l_terr,
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
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');
341 l_cells(21) := l_ntf_rec.rec_role;
342 l_cells(22) := l_ntf_rec.more_role;
343 l_cells(23) := l_ntf_rec.stat;
344 l_cells(24) := l_ntf_rec.m_stat;
345 l_cells(25) := l_ntf_rec.cb;
346 l_cells(26) := l_ntf_rec.ctx;
347 l_cells(27) := l_ntf_rec.resp;
348 l_cells(28) := l_ntf_rec.subj;
349
350 close c_ntf;
351
352 Get_Table(l_cells, 14, 'H', l_temp);
353 l_result := l_result || l_temp;
354 return l_result;
355 exception
356 when others then
357 l_cells.DELETE;
358 l_cells(1) := '10%:Note';
359 l_cells(2) := 'Error when generating Notification Item Information for nid '||p_nid;
360 l_cells(3) := '10%:Error';
361 l_cells(4) := sqlerrm;
362 Get_Table(l_cells, 2, 'V', l_result);
363 return l_result;
364 end Get_Ntf_Item_Info;
365
366 --
367 -- Get_Group_Ntfs - <Explained in WFDIAGPS.pls>
368 --
369 function Get_Group_Ntfs(p_nid in number)
370 return varchar2
371 is
372 l_cells tdType;
373 l_result varchar2(32000);
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
383 begin
384 l_cells(1) := 'WH:<b>Group Associated Notifications</b>';
385 Get_table(l_cells, 1, 'H', l_result);
386 l_cells.DELETE;
387
388 l_cells(1) := 'Notification Id';
389 l_cells(2) := 'Recipent Role';
390 l_cells(3) := 'Status';
391 l_cells(4) := 'Mail Status';
392 l_cells(5) := 'Begin Date';
393 l_cells(6) := 'End Date';
394
395 if g_group_ntf = TRUE then
396 for l_ntf_rec in c_grp_ntf loop
397 if l_cnt >= 20 then
398 l_cells(i+7) := '...';
399 l_cells(i+8) := '...';
400 l_cells(i+9) := '...';
401 l_cells(i+10) := '...';
402 l_cells(i+11) := '...';
403 l_cells(i+12) := '...';
404 exit;
405 else
406 l_cells(i+7) := l_ntf_rec.NOTIFICATION_ID;
407 l_cells(i+8) := l_ntf_rec.RECIPIENT_ROLE;
408 l_cells(i+9) := l_ntf_rec.STATUS;
409 l_cells(i+10) := l_ntf_rec.MAIL_STATUS;
410 l_cells(i+11) := l_ntf_rec.BEGIN_DATE;
411 l_cells(i+12) := l_ntf_rec.END_DATE;
412 end if;
413 i := i+6;
414 l_cnt := l_cnt+1;
415 end loop;
416 end if;
417 Get_Table(l_cells, 6, 'H', l_temp);
418 l_result := l_result || l_temp;
419 return l_result;
420 exception
421 when others then
422 l_cells.DELETE;
423 l_cells(1) := '10%:Note';
424 l_cells(2) := 'Error when collecting notifications associated to this group '||p_nid;
425 l_cells(3) := '10%:Error';
426 l_cells(4) := sqlerrm;
427 Get_Table(l_cells, 2, 'V', l_result);
428 return l_result;
429 end Get_Group_Ntfs;
430
431 --
432 -- Get_Ntf_Role_Users - <Explained in WFDIAGPS.pls>
433 --
434 function Get_Ntf_Role_Users(p_nid in number)
435 return varchar2
436 is
437 l_result varchar2(32000);
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;
447 i pls_integer;
448
449 l_role varchar2(320);
450 l_dname varchar2(360);
451 l_email varchar2(320);
452 l_npref varchar2(8);
453 l_lang varchar2(30);
454 l_terr varchar2(30);
455 l_orig_sys varchar2(30);
456 l_orig_id number;
457 l_install varchar2(1);
458 begin
459 l_cells(1) := 'WH:<b>Notification Recipient Role Members</b>';
460 Get_table(l_cells, 1, 'H', l_result);
461 l_cells.DELETE;
462
463 l_cells(1) := '15%:User Name';
464 l_cells(2) := '20%:Display Name';
465 l_cells(3) := '25%:Email Address';
466 l_cells(4) := '10%:Notification Pref';
467 l_cells(5) := '5%:Language';
468 l_cells(6) := '10%:Territory';
469 l_cells(7) := '10%:Orig Sys';
470 l_cells(8) := '5%:Orig Sys Id';
471 l_cells(9) := '5%:Installed';
472 i := 9;
473
474 for l_rec in user_curs loop
475 Wf_Directory.GetRoleInfoMail(l_rec.user_name, l_dname, l_email, l_npref,
476 l_lang, l_terr, l_orig_sys, l_orig_id, l_install);
477 l_cells(i+1) := l_rec.user_name;
478 l_cells(i+2) := l_dname;
479 l_cells(i+3) := l_email;
480 l_cells(i+4) := l_npref;
481 l_cells(i+5) := l_lang;
482 l_cells(i+6) := l_terr;
483 l_cells(i+7) := l_orig_sys;
484 l_cells(i+8) := l_orig_id;
485 l_cells(i+9) := l_install;
486 i := i+9;
487 end loop;
488 Get_Table(l_cells, 9, 'H', l_temp);
489 l_result := l_result || l_temp;
490 return l_result;
491 exception
492 when others then
493 l_cells.DELETE;
494 l_cells(1) := '10%:Note';
495 l_cells(2) := 'Error when generating Notification Recipient Role Members Information for nid '||p_nid;
496 l_cells(3) := '10%:Error';
497 if (wf_core.error_name is null) then
498 l_cells(4) := sqlerrm;
499 else
500 l_cells(4) := wf_core.error_name;
501 end if;
502 Get_Table(l_cells, 2, 'V', l_result);
503 return l_result;
504 end Get_Ntf_Role_Users;
505
506 --
507 -- Get_Ntf_Role_Info - <Explained in WFDIAGPS.pls>
508 --
509 function Get_Ntf_Role_Info(p_nid in number)
510 return varchar2
511 is
512 l_result varchar2(32000);
513 l_temp varchar2(32000);
514 l_role varchar2(320);
515 l_dname varchar2(360);
516 l_email varchar2(320);
517 l_npref varchar2(8);
518 l_lang varchar2(30);
519 l_terr varchar2(30);
520 l_orig_sys varchar2(30);
521 l_orig_id number;
522 l_install varchar2(1);
523 l_cells tdType;
524
525 begin
526 l_cells(1) := 'WH:<b>Notification Recipient Role Information</b>';
527 Get_Table(l_cells, 1, 'H', l_result);
528 l_cells.DELETE;
529
530 l_cells(1) := '15%:Role Name';
531 l_cells(2) := '20%:Display Name';
532 l_cells(3) := '25%:Email Address';
533 l_cells(4) := '10%:Notification Pref';
534 l_cells(5) := '5%:Launguage';
535 l_cells(6) := '5%:Territory';
536 l_cells(7) := '10%:Orig Sys';
537 l_cells(8) := '5%:Orig Sys Id';
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);
547
548 l_cells(10) := l_role;
549 l_cells(11) := l_dname;
550 l_cells(12) := l_email;
551 l_cells(13) := l_npref;
552 l_cells(14) := l_lang;
553 l_cells(15) := l_terr;
554 l_cells(16) := l_orig_sys;
555 l_cells(17) := l_orig_id;
556 l_cells(18) := l_install;
557
558 Get_Table(l_cells, 9, 'H', l_temp);
559 l_result := l_result || l_temp;
560 return l_result;
561 exception
562 when others then
563 l_cells.DELETE;
564 l_cells(1) := '10%:Note';
565 l_cells(2) := 'Error when generating Notification Recipient Role Information for nid '||p_nid;
566 l_cells(3) := '10%:Error';
567 if (wf_core.error_name is null) then
568 l_cells(4) := sqlerrm;
569 else
570 l_cells(4) := wf_core.error_name;
571 end if;
572 Get_Table(l_cells, 2, 'V', l_result);
573 return l_result;
574 end Get_Ntf_Role_Info;
575
576 -- Get_Summary_Ntf_Role_Users - <Explained in WFDIAGPS.pls>
577 --
578 function Get_Summary_Ntf_Role_Users(p_role in varchar2)
579 return varchar2
580 is
581 l_result varchar2(32000);
582 l_temp varchar2(32000);
583
584 CURSOR user_curs IS
585 SELECT wur.user_name
586 FROM wf_user_roles wur
587 WHERE wur.role_name = p_role;
588
589 l_cells tdType;
590 i pls_integer;
591
592 l_role varchar2(320);
593 l_dname varchar2(360);
594 l_email varchar2(320);
595 l_npref varchar2(8);
596 l_lang varchar2(30);
597 l_terr varchar2(30);
598 l_orig_sys varchar2(30);
599 l_orig_id number;
600 l_install varchar2(1);
601 begin
602 l_cells(1) := 'WH:<b>Summary Notification Recipient Role Members</b>';
603 Get_table(l_cells, 1, 'H', l_result);
604 l_cells.DELETE;
605
606 l_cells(1) := '15%:User Name';
607 l_cells(2) := '20%:Display Name';
608 l_cells(3) := '25%:Email Address';
609 l_cells(4) := '10%:Notification Pref';
610 l_cells(5) := '5%:Language';
611 l_cells(6) := '10%:Territory';
612 l_cells(7) := '10%:Orig Sys';
613 l_cells(8) := '5%:Orig Sys Id';
614 l_cells(9) := '5%:Installed';
615 i := 9;
616
617 for l_rec in user_curs loop
618 Wf_Directory.GetRoleInfoMail(l_rec.user_name, l_dname, l_email, l_npref,
619 l_lang, l_terr, l_orig_sys, l_orig_id, l_install);
620 l_cells(i+1) := l_rec.user_name;
621 l_cells(i+2) := l_dname;
622 l_cells(i+3) := l_email;
623 l_cells(i+4) := l_npref;
624 l_cells(i+5) := l_lang;
625 l_cells(i+6) := l_terr;
626 l_cells(i+7) := l_orig_sys;
627 l_cells(i+8) := l_orig_id;
628 l_cells(i+9) := l_install;
629 i := i+9;
630 end loop;
631 Get_Table(l_cells, 9, 'H', l_temp);
632 l_result := l_result || l_temp;
633 return l_result;
634 exception
635 when others then
636 l_cells.DELETE;
637 l_cells(1) := '10%:Note';
638 l_cells(2) := 'Error when generating Summary Notification Recipient ' ||
639 'Role Members Info. for role '||p_role;
640 l_cells(3) := '10%:Error';
641 if (wf_core.error_name is null) then
642 l_cells(4) := sqlerrm;
643 else
644 l_cells(4) := wf_core.error_name;
645 end if;
646 Get_Table(l_cells, 2, 'V', l_result);
647 return l_result;
648 end Get_Summary_Ntf_Role_Users;
649
650 --
651 -- Get_Summary_Ntf_Role_Info - <Explained in WFDIAGPS.pls>
652 -- Returns the info about a specified Role
653 function Get_Summary_Ntf_Role_Info(p_role in varchar2)
654 return varchar2
655 is
656 l_result varchar2(32000);
657 l_temp varchar2(32000);
658
659 l_dname varchar2(360);
660 l_email varchar2(320);
661 l_npref varchar2(8);
662 l_lang varchar2(30);
663 l_terr varchar2(30);
664 l_orig_sys varchar2(30);
665 l_orig_id number;
666 l_install varchar2(1);
667 l_cells tdType;
668
669 begin
670 l_cells(1) := 'WH:<b>Summary Notification Recipient Role Information</b>';
671 Get_Table(l_cells, 1, 'H', l_result);
672 l_cells.DELETE;
673
674 l_cells(1) := '15%:Role Name';
675 l_cells(2) := '20%:Display Name';
676 l_cells(3) := '25%:Email Address';
677 l_cells(4) := '10%:Notification Pref';
678 l_cells(5) := '5%:Launguage';
679 l_cells(6) := '5%:Territory';
680 l_cells(7) := '10%:Orig Sys';
681 l_cells(8) := '5%:Orig Sys Id';
682 l_cells(9) := '5%:Installed';
683
684 -- If user passes Role's display name then findout
685 --SELECT recipient_role
686 --INTO l_role
687 --FROM wf_roles
688 --WHERE name = p_nid;
689
690 Wf_Directory.GetRoleInfoMail(p_role, l_dname, l_email, l_npref, l_lang, l_terr,
691 l_orig_sys, l_orig_id, l_install);
692
693 l_cells(10) := p_role;
694 l_cells(11) := l_dname;
695 l_cells(12) := l_email;
696 l_cells(13) := l_npref;
697 l_cells(14) := l_lang;
698 l_cells(15) := l_terr;
699 l_cells(16) := l_orig_sys;
700 l_cells(17) := l_orig_id;
701 l_cells(18) := l_install;
702
703 Get_Table(l_cells, 9, 'H', l_temp);
704 l_result := l_result || l_temp;
705 return l_result;
706 exception
707 when others then
708 l_cells.DELETE;
709 l_cells(1) := '10%:Note';
710 l_cells(2) := 'Error when generating Notification Recipient Role Information for nid '||p_role;
711 l_cells(3) := '10%:Error';
712 if (wf_core.error_name is null) then
713 l_cells(4) := sqlerrm;
714 else
715 l_cells(4) := wf_core.error_name;
716 end if;
717 Get_Table(l_cells, 2, 'V', l_result);
718
719 return l_result;
720 end Get_Summary_Ntf_Role_Info;
721
722 --
723 -- Get_Ntf_More_Info - <Explained in WFDIAGPS.pls>
724 --
725 function Get_Ntf_More_Info(p_nid in number)
726 return varchar2
727 is
728 l_result varchar2(32000);
729 l_temp varchar2(32000);
730 l_role varchar2(320);
731 l_dname varchar2(360);
732 l_email varchar2(320);
733 l_npref varchar2(8);
734 l_lang varchar2(30);
735 l_terr varchar2(30);
736 l_orig_sys varchar2(30);
737 l_orig_id number;
738 l_install varchar2(1);
739 l_cells tdType;
740
741 begin
742 l_cells(1) := 'WH:<b>Notification More Info Role Information</b>';
743 Get_Table(l_cells, 1, 'H', l_result);
744 l_cells.DELETE;
745
746 l_cells(1) := '15%:Role Name';
747 l_cells(2) := '20%:Display Name';
748 l_cells(3) := '25%:Email Address';
749 l_cells(4) := '10%:Notification Pref';
750 l_cells(5) := '5%:Launguage';
751 l_cells(6) := '5%:Territory';
752 l_cells(7) := '10%:Orig Sys';
753 l_cells(8) := '5%:Orig Sys Id';
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
763 Wf_Directory.GetRoleInfoMail(l_role, l_dname, l_email, l_npref, l_lang, l_terr,
764 l_orig_sys, l_orig_id, l_install);
765
766 l_cells(10) := l_role;
767 l_cells(11) := l_dname;
768 l_cells(12) := l_email;
769 l_cells(13) := l_npref;
770 l_cells(14) := l_lang;
771 l_cells(15) := l_terr;
772 l_cells(16) := l_orig_sys;
773 l_cells(17) := l_orig_id;
774 l_cells(18) := l_install;
775
776 Get_Table(l_cells, 9, 'H', l_temp);
777 l_result := l_result || l_temp;
778 end if;
779 return l_result;
780 exception
781 when others then
782 l_cells.DELETE;
783 l_cells(1) := '10%:Note';
784 l_cells(2) := 'Error when generating Notification More Info Role Information for nid '||p_nid;
785 l_cells(3) := '10%:Error';
786 if (wf_core.error_message is null) then
787 l_cells(4) := sqlerrm;
788 else
789 l_cells(4) := wf_core.error_message;
790 end if;
791 Get_Table(l_cells, 2, 'V', l_result);
792 return l_result;
793 end Get_Ntf_More_Info;
794
795 --
796 -- Get_Routing_Rules - <Explained in WFDIAGPS.pls>
797 --
798 function Get_Routing_Rules(p_nid in number)
799 return varchar2
800 is
801 l_result varchar2(32000);
802 l_temp varchar2(32000);
803 l_cells tdType;
804 i pls_integer;
805
806 CURSOR c_rules IS
807 SELECT wr.action action,
808 wr.begin_date begin_date,
809 wr.end_date end_date,
810 wr.message_type msg_type,
811 wr.message_name msg_name,
812 wr.action_argument act_arg,
813 wra.name,
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
823 begin
824 l_cells(1) := 'WH:<b>Notification Recipient Routing Rules</b>';
825 Get_Table(l_cells, 1, 'H', l_result);
826 l_cells.DELETE;
827
828 l_cells(1) := '10%:Action';
829 l_cells(2) := '5%:Begin Date';
830 l_cells(3) := '5%:End Date';
831 l_cells(4) := '15%:Message Type';
832 l_cells(5) := '15%:Message Name';
833 l_cells(6) := '15%:Action Argument';
834 l_cells(7) := '10%:Name';
835 l_cells(8) := '15%:Type';
836 l_cells(9) := '10%:Value';
837 i := 9;
838
839 for l_rec in c_rules loop
840 l_cells(i+1) := l_rec.action;
841 l_cells(i+2) := l_rec.begin_date;
842 l_cells(i+3) := l_rec.end_Date;
843 l_cells(i+4) := l_rec.msg_type;
844 l_cells(i+5) := l_rec.msg_name;
845 l_cells(i+6) := l_rec.act_arg;
846 l_cells(i+7) := l_rec.name;
847 l_cells(i+8) := l_rec.type;
848 l_cells(i+9) := l_rec.value;
849 i := i+9;
850 end loop;
851 Get_Table(l_cells, 9, 'H', l_temp);
852 l_result := l_result || l_temp;
853 return l_result;
854 exception
855 when others then
856 l_cells.DELETE;
857 l_cells(1) := '10%:Note';
858 l_cells(2) := 'Error when generating Notification Recipient Role Routing Rules Information for nid '||p_nid;
859 l_cells(3) := '10%:Error';
860 l_cells(4) := sqlerrm;
861 Get_Table(l_cells, 2, 'V', l_result);
862 return l_result;
863 end Get_Routing_Rules;
864
865 --
866 -- Get_Ntf_Msg_Attrs - <Explained in WFDIAGPS.pls>
867 --
868 procedure Get_Ntf_Msg_Attrs(p_nid in number,
869 p_value in out nocopy clob)
870 is
871 l_result varchar2(32000);
872 l_cells tdType;
873 i pls_integer;
874
875 cursor c_msg_attr is
876 select wma.name name,
877 wmat.display_name d_name,
878 wma.sequence seq,
879 wma.type type,
880 wma.subtype s_type,
881 wma.value_type v_type,
882 decode (wma.type,
883 'DATE', to_char(wma.date_default),
884 'NUMBER', to_char(wma.number_default),
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')
894 and wma.message_type = wn.message_type
895 and wma.message_name = wn.message_name
896 and wn.notification_id = p_nid;
897
898 begin
899 dbms_lob.trim(l_temp, 0);
900
901 l_cells(1) := 'WH:<b>Notification Message Attribute Values</b>';
902 Get_Table(l_cells, 1, 'H', l_result);
903 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
904 l_cells.DELETE;
905
906 l_cells(1) := '10%:Name';
907 l_cells(2) := '15%:Display Name';
908 l_cells(3) := '5%:Sequence';
909 l_cells(4) := '10%:Type';
910 l_cells(5) := '10%:Sub Type';
911 l_cells(6) := '10%:Value Type';
912 l_cells(7) := '30%:Value';
913 l_cells(8) := '10%:Format';
914 i := 8;
915
916 for l_msg_rec in c_msg_attr loop
917 l_cells(i+1) := l_msg_rec.name;
918 l_cells(i+2) := l_msg_rec.d_name;
919 l_cells(i+3) := l_msg_rec.seq;
920 l_cells(i+4) := l_msg_rec.type;
921 l_cells(i+5) := l_msg_rec.s_type;
922 l_cells(i+6) := l_msg_rec.v_type;
923 l_cells(i+7) := l_msg_rec.value;
924 l_cells(i+8) := l_msg_rec.format;
925 i := i+8;
926 end loop;
927
928 Get_Table(l_cells, 8, 'H', l_temp);
929 dbms_lob.Append(p_value, l_temp);
930 exception
931 when others then
932 l_cells.DELETE;
933 l_cells(1) := '10%:Note';
934 l_cells(2) := 'Error when generating Notification Message Attribute Values Information for nid '||p_nid;
935 l_cells(3) := '10%:Error';
936 l_cells(4) := sqlerrm;
937 Get_Table(l_cells, 2, 'V', l_result);
938 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
939 end Get_Ntf_Msg_Attrs;
940
941 --
942 -- Get_Ntf_Attrs - <Explained in WFDIAGPS.pls>
943 --
944 procedure Get_Ntf_Attrs(p_nid in number,
945 p_value in out nocopy clob)
946 is
947 l_result varchar2(32000);
948 l_cells tdType;
949 i pls_integer;
950
951 cursor c_ntf_attr is
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);
961
962 l_cells(1) := 'WH:<b>Notification Attribute Values</b>';
963 Get_Table(l_cells, 1, 'H', l_result);
964 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
965 l_cells.DELETE;
966
967 l_cells(1) := '15%:Name';
968 l_cells(2) := '15%:Number Value';
969 l_cells(3) := '15%:Date Value';
970 l_cells(4) := '55%:Text Value';
971 i := 4;
972
973 for l_attr_rec in c_ntf_attr loop
974 l_cells(i+1) := l_attr_rec.name;
975 l_cells(i+2) := l_attr_rec.num_val;
976 l_cells(i+3) := l_attr_rec.date_val;
977 l_cells(i+4) := l_attr_rec.txt_val;
978 i := i+4;
979 end loop;
980 Get_Table(l_cells, 4, 'H', l_temp);
981 dbms_lob.Append(p_value, l_temp);
982 exception
983 when others then
984 l_cells.DELETE;
985 l_cells(1) := '10%:Note';
986 l_cells(2) := 'Error when generating Notification Attributes Values Information for nid '||p_nid;
987 l_cells(3) := '10%:Error';
988 l_cells(4) := sqlerrm;
989 Get_Table(l_cells, 2, 'V', l_result);
990 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
991 end Get_Ntf_Attrs;
992
993
994 --
995 -- Bug 6677333
996 -- Get_Ntf_Msg_Attrs
997 -- Returns a HTML table of all the Message Result Attribute values associated with the
998 -- Notification message
999 --
1000
1001 procedure Get_Ntf_Msg_Result_Attrs(p_nid in number,
1002 p_value in out nocopy clob)
1003 is
1004 l_result varchar2(32000);
1005 l_cells tdType;
1006 i pls_integer;
1007 l_format varchar2(100);
1008 l_msg_type varchar2(100);
1009 l_msg_name varchar2(100);
1010 l_lookup_code varchar2(100);
1011 l_display_value varchar2(100);
1012 l_lang varchar2(100) ;
1013 l_lang_code varchar2(10) ;
1014
1015 cursor c_msg_attr(type VARCHAR2, lang varchar2) is
1016 select lookup_type lookup_type,
1017 lookup_code lookup_code,
1018 meaning display_Value,
1019 language lang_code
1020 from WF_LOOKUPS_TL
1021 where language = lang
1022 AND lookup_type = type;
1023
1024
1025 begin
1026 dbms_lob.Trim(l_temp, 0);
1027
1028 l_cells(1) := 'WH:<b>Notification Message Result Attribute Values</b>';
1029 Get_Table(l_cells, 1, 'H', l_result);
1030 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1031 l_cells.DELETE;
1032
1033 l_cells(1) := '15%:Lookup Type';
1034 l_cells(2) := '15%:Lookup Code';
1035 l_cells(3) := '15%:Display Value';
1036 l_cells(4) := '55%:Language Code';
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
1046 select format
1047 into l_format
1048 from wf_message_attributes
1049 where message_type = l_msg_type
1050 and message_name = l_msg_name
1051 and name = 'RESULT' ;
1052
1053 exception
1054 when no_data_found then
1055 Get_Table(l_cells, 4, 'H', l_temp);
1056 dbms_lob.Append(p_value, l_temp);
1057
1058 l_cells.DELETE;
1059 l_cells(1) := '20%(not defined)'||wf_core.newline;
1060 Get_Table(l_cells, 1, 'V', l_result);
1061 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1062
1063 return;
1064 end;
1065
1066 for l_attr_rec in c_msg_attr(l_format,'US') loop
1067 l_cells(i+1) := l_attr_rec.lookup_type;
1068 l_cells(i+2) := l_attr_rec.lookup_code;
1069 l_cells(i+3) := l_attr_rec.display_Value;
1070 l_cells(i+4) := l_attr_rec.lang_code;
1071 i := i+4;
1072 end loop;
1073
1074 if(not l_lang_code = 'US') then
1075 for l_attr_rec in c_msg_attr(l_format,l_lang_code) loop
1076 l_cells(i+1) := l_attr_rec.lookup_type;
1077 l_cells(i+2) := l_attr_rec.lookup_code;
1078 l_cells(i+3) := l_attr_rec.display_Value;
1079 l_cells(i+4) := l_attr_rec.lang_code;
1080 i := i+4;
1081 end loop;
1082 end if;
1083
1084 Get_Table(l_cells, 4, 'H', l_temp);
1085 dbms_lob.Append(p_value, l_temp);
1086 exception
1087 when others then
1088 l_cells.DELETE;
1089 l_cells(1) := '10%:Note';
1090 l_cells(2) := 'Error when generating Notification Result Attributes Values Information for nid '||p_nid;
1091 l_cells(3) := '10%:Error';
1092 l_cells(4) := sqlerrm;
1093 Get_Table(l_cells, 2, 'V', l_result);
1094 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1095 end Get_Ntf_Msg_Result_Attrs;
1096
1097 --
1098 -- Get_User_Comments - <Explained in WFDIAGPS.pls>
1099 --
1100 procedure Get_User_Comments(p_nid in number,
1101 p_value in out nocopy clob)
1102 is
1103 l_result varchar2(32000);
1104 l_cells tdType;
1105 i pls_integer;
1106
1107 cursor c_comm is
1108 select wc.from_role,
1109 wc.from_user,
1110 to_char(wc.comment_date, 'DD-MON-RRRR HH24:MI:SS') comm_date,
1111 wc.action,
1112 wc.user_comment
1113 from wf_comments wc
1114 where wc.notification_id = p_nid
1115 order by comment_date;
1116
1117 begin
1118 dbms_lob.Trim(l_temp, 0);
1119
1120 l_cells(1) := 'WH:<b>Notification User Comments</b>';
1121 Get_Table(l_cells, 1, 'H', l_result);
1122 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1123 l_cells.DELETE;
1124
1125 l_cells(1) := '10%:From Role';
1126 l_cells(2) := '10%:From User';
1127 l_cells(3) := '5%:Comment Date';
1128 l_cells(4) := '15%:Action';
1129 l_cells(5) := '60%:User Comment';
1130 i := 5;
1131
1132 for l_comm_rec in c_comm loop
1133 l_cells(i+1) := l_comm_rec.from_role;
1134 l_cells(i+2) := l_comm_rec.from_user;
1135 l_cells(i+3) := l_comm_rec.comm_date;
1136 l_cells(i+4) := l_comm_rec.action;
1137 l_cells(i+5) := l_comm_rec.user_comment;
1138 i := i+5;
1139 end loop;
1140 Get_Table(l_cells, 5, 'H', l_temp);
1141 dbms_lob.Append(p_value, l_temp);
1142 exception
1143 when others then
1144 l_cells.DELETE;
1145 l_cells(1) := '10%:Note';
1146 l_cells(2) := 'Error when generating Notification User Comments Information for nid '||p_nid;
1147 l_cells(3) := '10%:Error';
1148 l_cells(4) := sqlerrm;
1149 Get_Table(l_cells, 2, 'V', l_result);
1150 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1151 end Get_User_Comments;
1152
1153 --
1154 -- Get_Event_Queue_Status - <Explained in WFDIAGPS.pls>
1155 --
1156 function Get_Event_Queue_Status(p_queue_name in varchar2,
1157 p_event_name in varchar2,
1158 p_event_key in varchar2)
1159 return varchar2
1160 is
1161 l_result varchar2(32767);
1162 l_temp varchar2(32767);
1163 l_cells tdType;
1164 i pls_integer;
1165 l_sql_str varchar2(4000);
1166
1167 type t_eventq is ref cursor;
1168 c_eventq t_eventq;
1169
1170 type t_eventq_rec is record
1171 (
1172 msgid raw(16),
1173 state varchar2(13),
1174 con_name varchar2(30),
1175 queue_name varchar2(30),
1176 exception_queue varchar2(30),
1177 retry_count number,
1178 ev_name varchar2(240),
1179 ev_key varchar2(240),
1180 enq_time date,
1181 deq_time date ,
1182 err_msg varchar2(4000),
1183 err_stk varchar2(4000)
1184 );
1185 l_eventq_rec t_eventq_rec;
1186
1187 begin
1188 l_cells(1) := 'WH:<b>'||p_queue_name||' Queue Status</b>';
1189 Get_table(l_cells, 1, 'H', l_result);
1190 l_cells.DELETE;
1191
1192 l_cells(1) := 'Message Id';
1193 l_cells(2) := 'Message State';
1194 l_cells(3) := 'Consumer Name';
1195 l_cells(4) := 'Queue';
1196 l_cells(5) := 'Exception Queue';
1197 l_cells(6) := 'Retry Count';
1198 l_cells(7) := 'Event Name';
1199 l_cells(8) := 'Event Key';
1200 if (p_queue_name = 'WF_ERROR') then
1201 l_cells(9) := 'Error Message';
1202 l_cells(10) := 'Error Stack';
1203 else
1204 l_cells(9) := 'Enqueue Time';
1205 l_cells(10) := 'Dequeue Time';
1206 end if;
1207 i := 10;
1208
1209 l_sql_str := 'select tab.msg_id msgid, '||
1210 'tab.msg_state state, '||
1211 'tab.consumer_name con_name, '||
1212 'tab.queue queue_name, '||
1213 'tab.exception_queue ex_queue_name, '||
1214 'tab.retry_count, '||
1215 'tab.user_data.event_name ev_name, '||
1216 'tab.user_data.event_key ev_key, '||
1217 'tab.enq_time, '||
1218 'tab.deq_time, '||
1219 'tab.user_data.error_message err_msg, '||
1220 'tab.user_data.error_stack err_stack '||
1221 'from '||g_qowner||'.aq$'||p_queue_name||' tab '||
1222 'where tab.user_data.event_name like :p1 '||
1223 'and tab.user_data.event_key like :p2';
1224
1225 open c_eventq for l_sql_str using p_event_name, p_event_key;
1226 loop
1227 fetch c_eventq into l_eventq_rec;
1228 exit when c_eventq%NOTFOUND;
1229
1230 l_cells(i+1) := l_eventq_rec.msgid;
1231 l_cells(i+2) := l_eventq_rec.state;
1232 l_cells(i+3) := l_eventq_rec.con_name;
1233 l_cells(i+4) := l_eventq_rec.queue_name;
1234 l_cells(i+5) := l_eventq_rec.exception_queue;
1235 l_cells(i+6) := l_eventq_rec.retry_count;
1236 l_cells(i+7) := l_eventq_rec.ev_name;
1237 l_cells(i+8) := l_eventq_rec.ev_key;
1238 if (p_queue_name = 'WF_ERROR') then
1239 l_cells(i+9) := l_eventq_rec.err_msg;
1240 l_cells(i+10) := l_eventq_rec.err_stk;
1241 else
1242 l_cells(i+9) := to_char(l_eventq_rec.enq_time, 'DD-MON-RRRR HH24:MI:SS');
1243 l_cells(i+10) := to_char(l_eventq_rec.deq_time, 'DD-MON-RRRR HH24:MI:SS');
1244 end if;
1245 i := i+10;
1246 end loop;
1247 close c_eventq;
1248
1249 Get_Table(l_cells, 10, 'H', l_temp);
1250 l_result := l_result || l_temp;
1251 return l_result;
1252 exception
1253 when others then
1254 l_cells.DELETE;
1255 l_cells(1) := 'Note';
1256 l_cells(2) := 'Error when generating '||p_queue_name||' Queue Status Information for Event Name '
1257 ||p_event_name||' and Event Key '||p_event_key;
1258 l_cells(3) := 'Error';
1259 l_cells(4) := sqlerrm;
1260 Get_Table(l_cells, 2, 'V', l_result);
1261 return l_result;
1262 end Get_Event_Queue_Status;
1263
1264
1265
1266 --
1267 -- Get_JMS_Queue_Status - <Explained in WFDIAGPS.pls>
1268 --
1269 function Get_JMS_Queue_Status(p_queue_name in varchar2,
1270 p_event_name in varchar2,
1271 p_event_key in varchar2,
1272 p_corr_id in varchar2 )
1273 return varchar2
1274 is
1275 l_result varchar2(32767);
1276 l_temp varchar2(32767);
1277 l_cells tdType;
1278 i pls_integer;
1279 l_retention varchar2(40);
1280 l_event_t wf_event_t;
1281
1282 type t_jmsq is ref cursor;
1283 c_jmsq t_jmsq;
1284
1285 type t_jmsq_rec is record
1286 (
1287 msg_id raw(16),
1288 corr_id varchar2(128),
1289 msg_state varchar2(13),
1290 consumer_name varchar2(30),
1291 queue_name varchar2(30),
1292 exception_queue varchar2(30),
1293 retry_count number,
1294 enq_time date,
1295 deq_time date,
1296 user_data SYS.AQ$_JMS_TEXT_MESSAGE
1297 );
1298 l_jmsq_rec t_jmsq_rec;
1299
1300 begin
1301 l_cells(1) := 'WH:<b>'||p_queue_name||' Queue Status </b>' || p_corr_id ;
1302 Get_Table(l_cells, 1, 'H', l_result);
1303 l_cells.DELETE;
1304
1305 l_cells(1) := 'Message Id';
1306 l_cells(2) := 'Message State';
1307 l_cells(3) := 'Consumer Name';
1308 l_cells(4) := 'Queue';
1309 l_cells(5) := 'Exception Queue';
1310 l_cells(6) := 'Retry Count';
1311 l_cells(7) := 'Enqueue Time';
1312 l_cells(8) := 'Dequeue Time';
1313 i := 8;
1314
1315
1316 wf_event_t.Initialize(l_event_t);
1317
1318 -- org
1319 open c_jmsq for 'select msg_id, corr_id, msg_state, consumer_name, queue, exception_queue, '||
1320 ' retry_count, enq_time, deq_time, user_data'||
1321 ' from '||g_qowner||'.aq$'||p_queue_name ||
1322 ' order by enq_time desc';
1323
1324
1325 loop
1326 fetch c_jmsq into l_jmsq_rec;
1327 exit when c_jmsq%NOTFOUND;
1328
1329 -- deserialize DOES NOT updates l_event_t.correlation_id field, so we have
1330 -- to use l_jmsq_rec.corr_id to compare
1331
1332 wf_event_ojmstext_qh.deserialize(l_jmsq_rec.user_data, l_event_t);
1333
1334
1335
1336 if ( (l_event_t.event_key like p_event_key) AND
1337 (l_event_t.event_name like p_event_name) AND
1338 (p_corr_id is null or upper(l_jmsq_rec.corr_id)
1339 like upper(p_corr_id ) )) then
1340
1341 l_cells(i+1) := l_jmsq_rec.msg_id;
1342 l_cells(i+2) := l_jmsq_rec.msg_state ;
1343 l_cells(i+3) := l_jmsq_rec.consumer_name;
1344 l_cells(i+4) := l_jmsq_rec.queue_name;
1345 l_cells(i+5) := l_jmsq_rec.exception_queue;
1346 l_cells(i+6) := l_jmsq_rec.retry_count;
1347 l_cells(i+7) := to_char(l_jmsq_rec.enq_time, 'DD-MON-YYYY HH24:MI:SS');
1348 l_cells(i+8) := to_char(l_jmsq_rec.deq_time, 'DD-MON-YYYY HH24:MI:SS');
1349 i := i+8;
1350
1351 end if;
1352
1353 end loop;
1354
1355 close c_jmsq;
1356
1357 Get_Table(l_cells, 8, 'H', l_temp);
1358 l_result := l_result || l_temp;
1359 return l_result;
1360 exception
1361 when others then
1362 l_cells.DELETE;
1363 l_cells(1) := 'Note';
1364 l_cells(2) := 'Error when generating '||p_queue_name||' Queue Status Information for Event Name'
1365 ||p_event_name||' and Event Key '||p_event_key;
1366 l_cells(3) := 'Error';
1367 l_cells(4) := sqlerrm;
1368 Get_Table(l_cells, 2, 'V', l_result);
1369 return l_result;
1370 end Get_JMS_Queue_Status;
1371 --
1372 -- Get_JMS_Queue_Status - <Explained in WFDIAGPS.pls>
1373 --
1374 function Get_JMS_Queue_Status(p_queue_name in varchar2,
1375 p_event_name in varchar2,
1376 p_event_key in varchar2 )
1377 return varchar2
1378 is
1379 begin
1380
1381 return Get_JMS_Queue_Status(p_queue_name => p_queue_name,
1382 p_event_name => p_event_name,
1383 p_event_key => p_event_key,
1384 p_corr_id =>null );
1385
1386 end Get_JMS_Queue_Status;
1387
1388 --
1389 -- SetNLS - Sets the NLS parameters Langauge and Territory for the current session
1390 --
1391 procedure SetNLS(p_language in varchar2,
1392 p_territory in varchar2)
1393 is
1394 l_lang varchar2(30);
1395 l_terr varchar2(30);
1396 l_install varchar2(10);
1397 begin
1398 if (p_language is null) then
1399 l_lang := 'AMERICAN';
1400 end if;
1401 if (p_territory is null) then
1402 l_terr := 'AMERICA';
1403 end if;
1404 begin
1405 SELECT installed_flag
1406 INTO l_install
1407 FROM wf_languages
1408 WHERE nls_language = p_language
1409 AND installed_flag = 'Y';
1410
1411 l_lang := ''''||p_language||'''';
1412 l_terr := ''''||p_territory||'''';
1413 exception
1414 when others then
1415 l_lang := 'AMERICAN';
1416 l_terr := 'AMERICA';
1417 end;
1418 dbms_session.set_nls('NLS_LANGUAGE', l_lang);
1419 dbms_session.set_nls('NLS_TERRITORY', l_terr);
1420
1421 end SetNLS;
1422
1423 --
1424 -- Get_Ntf_Templates - <Explained in WFDIAGPS.pls>
1425 --
1426 procedure Get_Ntf_Templates(p_nid in number,
1427 p_value in out nocopy clob)
1428 is
1429 l_result varchar2(32767);
1430 l_temp varchar2(32767);
1431 l_cells tdType;
1432 i pls_integer;
1433 l_txt_body varchar2(4000);
1434 l_htm_body varchar2(4000);
1435 l_subj varchar2(240);
1436 l_tname varchar2(100);
1437 l_ttype varchar2(100);
1438 l_status varchar2(8);
1439 l_mstatus varchar2(8);
1440 l_msg_type varchar2(8);
1441 l_msg_name varchar2(30);
1442 l_nid number;
1443 l_recip_role varchar2(320);
1444 l_dname varchar2(360);
1445 l_email varchar2(320);
1446 l_npref varchar2(8);
1447 l_user_lang varchar2(30);
1448 l_user_terr varchar2(30);
1449 l_osys varchar2(30);
1450 l_osysid number;
1451 l_installed varchar2(10);
1452
1453 l_ses_lang varchar2(30);
1454 l_ses_terr varchar2(30);
1455 l_ses_codeset varchar2(30);
1456 l_ntf_lang varchar2(30);
1457 l_ntf_terr varchar2(30);
1458 l_ntf_codeset varchar2(30);
1459 l_nls_lang varchar2(30);
1460 l_nls_terr varchar2(30);
1461
1462 type t_lang_terr is record
1463 (
1464 language varchar2(30),
1465 territory varchar2(30)
1466 );
1467
1468 type t_lang_list is table of t_lang_terr index by binary_integer;
1469 l_lang_list t_lang_list;
1470
1471 begin
1472 l_nid := p_nid;
1473
1474 l_cells(1) := 'WH:<b>Notification Message Template Definition</b>';
1475 Get_Table(l_cells, 1, 'H', l_result);
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;
1485 l_cells(1) := 'WH:<b>Setting MAIL_STATUS to MAIL since the original status was ' || l_mstatus || '.</b>';
1486 Get_Table(l_cells, 1, 'H', l_temp);
1487 l_cells.DELETE;
1488 l_result := l_result || l_temp;
1489 l_mstatus := 'MAIL';
1490 if (l_status not in ('OPEN','CANCELED','CLOSED')) then
1491 l_status := 'OPEN';
1492 end if;
1493 end if;
1494
1495 begin
1496 wf_mail.getTemplateName(l_nid, l_status, l_mstatus, l_ttype, l_tname);
1497 exception
1498 when others then
1499 l_ttype := null;
1500 l_tname := null;
1501 end;
1502
1503 -- We want to generate in all possible language/territory associated to
1504 -- the notification
1505 -- 1. Recipient's preference
1506 -- 2. Setting at the language level using #WFM_NLS_XXXXXX attribute
1507 -- 3. Current session language
1508 Wf_Directory.GetRoleInfoMail(l_recip_role, l_dname, l_email, l_npref, l_user_lang,
1509 l_user_terr, l_osys, l_osysid, l_installed);
1510
1511 l_ntf_lang := l_user_lang;
1512 l_ntf_terr := l_user_terr;
1513 Wf_Mail.Get_Ntf_Language(l_nid, l_ntf_lang, l_ntf_terr, l_ntf_codeset);
1514
1515 Wf_Mail.GetSessionLanguage(l_ses_lang, l_ses_terr, l_ses_codeset);
1516
1517 -- Storing all the possible language/territory combinations for the given
1518 -- notification id in a parameter list
1519 l_lang_list(1).language := l_user_lang;
1520 l_lang_list(1).territory := l_user_terr;
1521 if (l_ntf_lang||l_ntf_terr <> l_user_lang||l_user_terr) then
1522 l_lang_list(2).language := l_ntf_lang;
1523 l_lang_list(2).territory := l_ntf_terr;
1524 if (l_ses_lang||l_ses_terr not in (l_ntf_lang||l_ntf_terr, l_user_lang||l_user_terr)) then
1525 l_lang_list(3).language := l_ses_lang;
1526 l_lang_list(3).territory := l_ses_terr;
1527 end if;
1528 elsif (l_ses_lang||l_ses_terr <> l_user_lang||l_user_terr) then
1529 l_lang_list(2).language := l_ses_lang;
1530 l_lang_list(2).territory := l_ses_terr;
1531 end if;
1532
1533 for i in 1..l_lang_list.COUNT loop
1534 l_nls_lang := l_lang_list(i).language;
1535 l_nls_terr := l_lang_list(i).territory;
1536
1537 -- l_user_lang should be valid within wf_langauges
1538 select installed_flag
1539 into l_installed
1540 from wf_languages
1541 where nls_language = l_nls_lang;
1542
1543 if (l_installed = 'Y') then
1544 -- Generate based on User's preference
1545 SetNLS(l_nls_lang, l_nls_terr);
1546
1547 if (l_ttype is not null and l_tname is not null) then
1548 begin
1549 select subject, body, html_body
1550 into l_subj, l_txt_body, l_htm_body
1551 from wf_messages_vl
1552 where name = l_tname
1553 and type = l_ttype;
1554 exception
1555 when no_data_found then
1556 wf_core.token('NAME', l_tname);
1557 wf_core.token('TYPE', l_ttype);
1558 wf_core.raise('WFNTF_MESSAGE');
1559 end;
1560
1561 l_result := NULL;
1562 l_cells(1) := '25%:Message Type';
1563 l_cells(2) := l_ttype;
1564 l_cells(3) := '25%:Message Name';
1565 l_cells(4) := l_tname;
1566 Get_Table(l_cells, 2, 'V', l_temp);
1567 l_result := l_result||l_temp||wf_core.newline;
1568 l_cells.DELETE;
1569 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1570 l_result := NULL;
1571
1572 l_result := '<table width='||table_width||'><tr>';
1573 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
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||')</th></tr>';
1576 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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||'</pre></td></tr>'||wf_core.newline;
1583
1584 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
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||')</th></tr>';
1587 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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||'</pre></td></tr></table>';
1594 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1595 else
1596 l_result := NULL;
1597 l_cells(1) := 'Warning';
1598 -- Bug 14365670. Consider the case when this is the NID of a group of notifications
1599 -- which mail status is null and no
1600 l_cells(2) := 'Unable to generate E-mail full template information. This can occur when the '||
1601 'mail status of the notification is NULL';
1602 if g_group_ntf=TRUE then
1603 l_cells(2) := 'E-mail full template information not generated since notification '||l_nid||' represents '||
1604 'a notification group. View details of associated notifications by runnig wfmlrdbg.sql for them.';
1605 end if;
1606 Get_Table(l_cells, 2, 'V', l_result);
1607 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1608 end if;
1609
1610 begin
1611 select subject, body, html_body
1612 into l_subj, l_txt_body, l_htm_body
1613 from wf_messages_vl
1614 where name = l_msg_name
1615 and type = l_msg_type;
1616 exception
1617 when no_data_found then
1618 wf_core.token('NAME', l_msg_name );
1619 wf_core.token('TYPE', l_msg_type);
1620 wf_core.raise('WFNTF_MESSAGE');
1621 end;
1622
1623 l_result := NULL;
1624 l_cells(1) := '25%:Message Type';
1625 l_cells(2) := l_msg_type;
1626 l_cells(3) := '25%:Message Name';
1627 l_cells(4) := l_msg_name;
1628 l_cells(5) := '25%:Subject';
1629 l_cells(6) := l_subj;
1630
1631 Get_Table(l_cells, 2, 'V', l_temp);
1632 l_result := l_result||l_temp||wf_core.newline;
1633 l_cells.DELETE;
1634 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1635 l_result := NULL;
1636
1637 l_result := '<table width='||table_width||'><tr>';
1638 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
1639 ' size='||th_fontsize||' color='||th_fontcolor||'>';
1640 l_result := l_result||'Notification Message Definition in TEXT format. ('||l_nls_lang||'_'||l_nls_terr||')</th></tr>';
1641 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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||'</pre></td></tr>'||wf_core.newline;
1648
1649 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
1650 ' size='||th_fontsize||' color='||th_fontcolor||'>';
1651 l_result := l_result||'Notification Message Definition in HTML format. ('||l_nls_lang||'_'||l_nls_terr||')</th></tr>';
1652 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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||'</pre></td></tr></table>';
1659 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1660 end if; -- installed flag check
1661 end loop;
1662 -- Reset to the base NLS settings
1663 SetNLS(l_ses_lang, l_ses_terr);
1664 exception
1665 when others then
1666 l_cells.DELETE;
1667 l_cells(1) := '10%:Note';
1668 l_cells(2) := 'Error when generating Notification Template Information for nid '||p_nid;
1669 l_cells(3) := '10%:Error';
1670 l_cells(4) := sqlerrm;
1671 Get_Table(l_cells, 2, 'V', l_result);
1672 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1673 end Get_Ntf_Templates;
1674
1675
1676
1677 -- get_Summary_Template -
1678 --
1679 -- IN
1680 -- Role
1681 -- Role pref. (SUMHTML or SUMMARY)
1682 -- OUT
1683 -- Item type and Message name for template
1684 procedure get_Summary_Templates(p_role in varchar2,
1685 p_ntf_pref in varchar2,
1686 p_value in out nocopy clob)
1687 is
1688
1689 l_result varchar2(32767);
1690 l_temp varchar2(32767);
1691 l_cells tdType;
1692 i pls_integer;
1693 l_txt_body varchar2(4000);
1694 l_htm_body varchar2(4000);
1695 l_subj varchar2(240);
1696 l_tname varchar2(100);
1697 l_ttype varchar2(100);
1698 -- l_status varchar2(8);
1699 -- l_mstatus varchar2(8);
1700 -- l_msg_type varchar2(8);
1701 -- l_msg_name varchar2(30);
1702
1703 l_dname varchar2(360);
1704 l_email varchar2(320);
1705 l_npref varchar2(8);
1706 l_user_lang varchar2(30);
1707 l_user_terr varchar2(30);
1708 l_osys varchar2(30);
1709 l_osysid number;
1710 l_installed varchar2(10);
1711
1712 l_ses_lang varchar2(30);
1713 l_ses_terr varchar2(30);
1714 l_ses_codeset varchar2(30);
1715 l_ntf_lang varchar2(30);
1716 l_ntf_terr varchar2(30);
1717 l_ntf_codeset varchar2(30);
1718 l_nls_lang varchar2(30);
1719 l_nls_terr varchar2(30);
1720
1721 l_component_id number;
1722 l_summary_param varchar(50);
1723 l_summary varchar(50);
1724 --
1725 CURSOR c_get_components_id is
1726 SELECT component_id
1727 FROM FND_SVC_COMPONENTS
1728 WHERE component_type = 'WF_MAILER'
1729 order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
1730
1731 begin
1732
1733 l_ttype := 'WFMAIL'; -- Set the default type;
1734 wf_mail.Set_FYI_Flag(FALSE);
1735
1736 -- GET tamplte name of a Mailer component
1737 for rec_component in c_get_components_id loop
1738
1739 l_component_id := rec_component.component_id;
1740
1741 if (p_ntf_pref = 'SUMHTML') then
1742
1743 SELECT a.parameter_value into l_summary_param
1744 FROM fnd_svc_comp_param_vals a,
1745 fnd_svc_components b,
1746 fnd_svc_comp_params_vl c
1747 WHERE b.component_id = a.component_id
1748 AND b.component_type = c.component_type
1749 AND c.parameter_id = a.parameter_id
1750 AND c.encrypted_flag = 'N'
1751 AND b.component_id = l_component_id
1752 AND c.parameter_name in ('SUMHTML' );
1753
1754 else
1755
1756 SELECT a.parameter_value into l_summary_param
1757 FROM fnd_svc_comp_param_vals a,
1758 fnd_svc_components b,
1759 fnd_svc_comp_params_vl c
1760 WHERE b.component_id = a.component_id
1761 AND b.component_type = c.component_type
1762 AND c.parameter_id = a.parameter_id
1763 AND c.encrypted_flag = 'N'
1764 AND b.component_id = l_component_id
1765 AND c.parameter_name in ('SUMMARY' );
1766
1767 end if;
1768
1769 -- get values only for one and running Mailer components
1770 exit;
1771
1772 end loop; -- end for loop
1773
1774 -- parse for template type and name for summary html
1775 l_ttype := substr(l_summary_param, 1, instr(l_summary_param, ':')-1) ;
1776 l_tname := substr(l_summary_param, instr(l_summary_param, ':')+1) ;
1777
1778 -- Get Roles' language pref.
1779 Wf_Directory.GetRoleInfoMail(p_role, l_dname, l_email,
1780 l_npref, l_user_lang, l_user_terr,
1781 l_osys, l_osysid, l_installed);
1782 -- get Session lang
1783 Wf_Mail.GetSessionLanguage(l_ses_lang, l_ses_terr, l_ses_codeset);
1784
1785 -- l_user_lang should be valid within wf_langauges
1786 select installed_flag
1787 into l_installed
1788 from wf_languages
1789 where nls_language = l_user_lang;
1790
1791 if (l_installed = 'Y') then
1792 -- Generate based on User's preference
1793 SetNLS(l_user_lang, l_user_terr);
1794
1795 select subject, body, html_body
1796 into l_subj, l_txt_body, l_htm_body
1797 from wf_messages_vl
1798 where name = l_tname
1799 and type = l_ttype;
1800
1801 l_result := NULL;
1802
1803 l_cells(1) := '25%:Message Type';
1804 l_cells(2) := l_ttype;
1805 l_cells(3) := '25%:Message Name';
1806 l_cells(4) := l_tname;
1807
1808 Get_Table(l_cells, 2, 'V', l_temp);
1809 l_result := l_result||l_temp||wf_core.newline;
1810 l_cells.DELETE;
1811
1812 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1813
1814 l_result := NULL;
1815
1816 l_result := '<table width='||table_width||'><tr>';
1817 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='
1818 || th_fontface|| ' size='||th_fontsize||' color='||th_fontcolor||'>';
1819
1820 l_result := l_result||'Summary Message Template in TEXT format. ('||
1821 l_user_lang||'_'||l_user_terr||')</th></tr>';
1822 --l_result := l_result||wf_core.newline;
1823 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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
1831 l_result := l_result||'</pre></td></tr>'||wf_core.newline;
1832
1833 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
1834 ' size='||th_fontsize||' color='||th_fontcolor||'>';
1835 l_result := l_result||'Summary Message Template in HTML format. ('||
1836 l_user_lang||'_'||l_user_terr||')</th></tr>';
1837 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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
1845 l_result := l_result||'</pre></td></tr></table>';
1846
1847 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1848
1849 end if;
1850
1851 -- Generate template based on session lang
1852 SetNLS(l_ses_lang, l_ses_terr);
1853
1854 -- We don't want to show same template two times.
1855 if (l_user_lang <> l_ses_lang) THEN
1856
1857 -- Generate template based on session lang
1858 SetNLS(l_ses_lang, l_ses_terr);
1859
1860 select subject, body, html_body
1861 into l_subj, l_txt_body, l_htm_body
1862 from wf_messages_vl
1863 where name = l_tname
1864 and type = l_ttype;
1865
1866 l_result := NULL;
1867
1868 l_cells(1) := '25%:Message Type';
1869 l_cells(2) := l_ttype;
1870 l_cells(3) := '25%:Message Name';
1871 l_cells(4) := l_tname;
1872
1873 Get_Table(l_cells, 2, 'V', l_temp);
1874 l_result := l_result||l_temp||wf_core.newline;
1875 l_cells.DELETE;
1876
1877 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1878
1879 l_result := NULL;
1880
1881 l_result := '<table width='||table_width||'><tr>';
1882 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='
1883 || th_fontface|| ' size='||th_fontsize||' color='||th_fontcolor||'>';
1884
1885 l_result := l_result||'Summary Message Template in TEXT format. ('||
1886 l_ses_lang||'_'||l_ses_terr||')</th></tr>';
1887 --l_result := l_result||wf_core.newline;
1888 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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
1896 l_result := l_result||'</pre></td></tr>'||wf_core.newline;
1897
1898 l_result := l_result||'<tr><th bgcolor='||th_bgcolor||' align=left><font face='||th_fontface||
1899 ' size='||th_fontsize||' color='||th_fontcolor||'>';
1900 l_result := l_result||'Summary Message Template in HTML format. ('||
1901 l_ses_lang||'_'||l_ses_terr||')</th></tr>';
1902 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'><pre>';
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
1910 l_result := l_result||'</pre></td></tr></table>';
1911
1912 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1913
1914 end if; -- If language installed
1915
1916 -- Reset to the base NLS settings
1917 SetNLS(l_ses_lang, l_ses_terr);
1918
1919 exception
1920 when no_data_found then
1921 wf_core.token('NAME', l_tname);
1922 wf_core.token('TYPE', l_ttype);
1923 wf_core.raise('WFNTF_MESSAGE');
1924
1925 when others then
1926 l_cells.DELETE;
1927 l_cells(1) := '10%:Note';
1928 l_cells(2) := 'Error when generating Summary Notification Template for Role '||p_role;
1929 l_cells(3) := '10%:Error';
1930 l_cells(4) := sqlerrm;
1931 Get_Table(l_cells, 2, 'V', l_result);
1932 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
1933
1934 end get_Summary_Templates;
1935
1936
1937 --
1938 -- Get_Ntf_Message - <Explained in WFDIAGPS.pls>
1939 --
1940 procedure Get_Ntf_Message(p_nid in number,
1941 p_value in out nocopy clob)
1942 is
1943 l_result varchar2(32000);
1944 l_temp varchar2(32000);
1945 l_cells tdType;
1946 i pls_integer;
1947
1948 p_event_name varchar2(100);
1949 p_event_key varchar2(100);
1950 p_parameter_list wf_parameter_list_t;
1951 l_doc clob;
1952 l_evt wf_event_t;
1953 l_parameters wf_parameter_list_t;
1954 l_erragt wf_agent_t;
1955 l_role varchar2(320);
1956 l_msg_type varchar2(8);
1957 l_amount number;
1958 l_chunksize pls_integer;
1959 l_offset pls_integer;
1960 l_buffer varchar2(32767);
1961 l_buffer_size pls_integer;
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:<b>Generate Notification Message</b>';
1971 Get_Table(l_cells, 1, 'H', l_result);
1972 l_cells.DELETE;
1973
1974 l_result := l_result||'<table width='||table_width||'>';
1975 l_result := l_result||'<tr bgcolor='||th_bgcolor||'>';
1976 l_result := l_result||'<th align=left><font face='||th_fontface||' size='||th_fontsize||
1977 ' color='||th_fontcolor||'>Notification Message in XML format</font></th>';
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));
1987 raise;
1988 end;
1989 wf_event.AddParameterToList('NOTIFICATION_ID', to_char(p_nid), l_parameters);
1990 wf_event.AddParameterToList('ROLE', l_role, l_parameters);
1991 wf_event.AddParameterToList('GROUP_ID', to_char(p_nid), l_parameters);
1992 wf_event.addParameterToList('Q_CORRELATION_ID', l_msg_type, l_parameters);
1993
1994 dbms_lob.CreateTemporary(l_doc, false, dbms_lob.Call);
1995
1996 wf_mail.test_flag := TRUE;
1997 l_before := dbms_utility.get_time();
1998 begin
1999 l_doc := wf_xml.generate(p_event_name, p_event_key, l_parameters);
2000 exception
2001 when others then
2002 wf_core.context('WF_DIAGNOSTICS', 'Generate', p_event_name, p_event_key);
2003 raise;
2004 end;
2005 l_time_taken := to_char((dbms_utility.get_time()-l_before)/100);
2006
2007 l_result := l_result||'<th align=right><font face='||th_fontface||' size='||th_fontsize||
2008 ' color='||th_fontcolor||'>Time Taken to complete Generate: '||l_time_taken||' Seconds</font></th></tr></table>';
2009 l_result := l_result||'<table width='||table_width||'><tr><td bgcolor='||td_bgcolor||'><pre>';
2010 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2011
2012 l_amount := dbms_lob.getlength(l_doc);
2013 l_chunksize := 10000;
2014 l_offset := 1;
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);
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
2031 l_result := l_result||'</pre></td></tr></table>';
2032 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2033 exception
2034 when others then
2035 l_cells.DELETE;
2036 l_cells(1) := '10%:Note';
2037 l_cells(2) := 'Error when generating Notification Message Information for nid '||p_nid;
2038 l_cells(3) := '10%:Error';
2039 l_cells(4) := sqlerrm;
2040 Get_Table(l_cells, 2, 'V', l_result);
2041 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
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)
2051 is
2052 l_result varchar2(32000);
2053 l_temp varchar2(32000);
2054 l_cells tdType;
2055 i pls_integer;
2056
2057 p_event_name varchar2(100);
2058 p_event_key varchar2(100);
2059 p_parameter_list wf_parameter_list_t;
2060 l_doc clob;
2061 l_evt wf_event_t;
2062 l_parameters wf_parameter_list_t;
2063 l_erragt wf_agent_t;
2064 l_role varchar2(320);
2065 l_msg_type varchar2(8);
2066 l_amount number;
2067 l_chunksize pls_integer;
2068 l_offset pls_integer;
2069 l_buffer varchar2(32767);
2070 l_buffer_size pls_integer;
2071
2072 l_before number;
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:<b>Generate Summary Notification Message</b>';
2082 Get_Table(l_cells, 1, 'H', l_result);
2083 l_cells.DELETE;
2084
2085 l_result := l_result||'<table width='||table_width||'>';
2086 l_result := l_result||'<tr bgcolor='||th_bgcolor||'>';
2087 l_result := l_result||'<th align=left><font face='||th_fontface||' size='||th_fontsize||
2088 ' color='||th_fontcolor||'>Notification Message in XML format</font></th>';
2089
2090 wf_event.AddParameterToList('ROLE_NAME', p_role, l_parameters);
2091 -- Set AQs correlation id to item type i.e. 'WFMAIL'
2092 wf_event.addParameterToList('Q_CORRELATION_ID', 'WFMAIL', l_parameters);
2093
2094 -- wf_event.AddParameterToList('GROUP_ID', to_char(0), l_parameters);
2095 dbms_lob.CreateTemporary(l_doc, false, dbms_lob.Call);
2096
2097 wf_mail.test_flag := TRUE;
2098
2099 l_before := dbms_utility.get_time();
2100
2101 begin
2102 l_doc := wf_xml.generate(p_event_name, p_event_key, l_parameters);
2103 exception
2104 when others then
2105 wf_core.context('WF_DIAGNOSTICS', 'Generate', p_event_name, p_event_key);
2106 raise;
2107 end;
2108
2109 l_time_taken := to_char((dbms_utility.get_time()-l_before)/100);
2110
2111 l_result := l_result||'<th align=right><font face='||th_fontface||' size='||th_fontsize||
2112 ' color='||th_fontcolor||'>Time Taken to complete Generate: '||l_time_taken||' Seconds</font></th></tr></table>';
2113 l_result := l_result||'<table width='||table_width||'><tr><td bgcolor='||td_bgcolor||'><pre>';
2114 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2115
2116 l_amount := dbms_lob.getlength(l_doc);
2117 l_chunksize := 10000;
2118 l_offset := 1;
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);
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
2135 l_result := l_result||'</pre></td></tr></table>';
2136 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2137 exception
2138 when others then
2139 l_cells.DELETE;
2140 l_cells(1) := '10%:Note';
2141 l_cells(2) := 'Error when generating Summary Notification Message Information for Role '||p_role;
2142 l_cells(3) := '10%:Error';
2143 l_cells(4) := sqlerrm;
2144 Get_Table(l_cells, 2, 'V', l_result);
2145 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2146 end Get_Summary_Ntf_Message;
2147
2148
2149 --
2150 -- Get_GSC_Comp_Parameters - <Explained in WFDIAGPS.pls>
2151 --
2152 procedure Get_GSC_Comp_Parameters(p_comp_type in varchar2,
2153 p_comp_name in varchar2 default null,
2154 p_value in out nocopy clob)
2155 is
2156 l_result varchar2(32767);
2157 l_temp CLOB;
2158 l_cells tdType;
2159 i pls_integer;
2160
2161 cursor c_comps is
2162 select component_id,
2163 component_name,
2164 component_status,
2165 startup_mode,
2166 container_type,
2167 inbound_agent_name,
2168 outbound_agent_name,
2169 correlation_id
2170 from fnd_svc_components
2171 where component_type = p_comp_type
2172 and component_name like nvl(p_comp_name, '%');
2173
2174 cursor c_params (p_comp_id in number) is
2175 select p.parameter_name,
2176 v.parameter_value,
2177 v.parameter_description,
2178 v.default_parameter_value
2179 from fnd_svc_comp_param_vals_v v,
2180 fnd_svc_comp_params_b p
2181 where p.encrypted_flag = 'N'
2182 and v.component_id = p_comp_id
2183 and v.parameter_id = p.parameter_id
2184 order by p.parameter_name;
2185
2186 begin
2187 l_cells(1) := 'WH:<b>GSC '||p_comp_type||' Component Parameters</b>';
2188 Get_Table(l_cells, 1, 'H', l_result);
2189 l_cells.DELETE;
2190 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2191
2192 for l_crec in c_comps loop
2193 l_cells(1) := '20%:Component Id';
2194 l_cells(2) := l_crec.component_id;
2195 l_cells(3) := '20%:Component Name';
2196 l_cells(4) := l_crec.component_name;
2197 l_cells(5) := '20%:Component Status';
2198 l_cells(6) := l_crec.component_status;
2199 l_cells(7) := '20%:Startup Mode';
2200 l_cells(8) := l_crec.startup_mode;
2201 l_cells(9) := '20%:Inbound Agent Name';
2202 l_cells(10) := l_crec.inbound_agent_name;
2203 l_cells(11) := '20%:Outbound Agent Name';
2204 l_cells(12) := l_crec.outbound_agent_name;
2205 l_cells(13) := '20%:Correlation Id';
2206 l_cells(14) := l_crec.correlation_id;
2207
2208 Get_Table(l_cells, 2, 'V', l_result);
2209 l_result := l_result||wf_core.newline;
2210 l_cells.DELETE;
2211 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2212 l_result := NULL;
2213
2214 l_cells(1) := '25%:Parameter Name';
2215 l_cells(2) := '25%:Parameter Value';
2216 l_cells(3) := '25%:Parameter Description';
2217 l_cells(4) := '25%:Default Value';
2218 i := 4;
2219
2220 for l_prec in c_params(l_crec.component_id) loop
2221 l_cells(i+1) := l_prec.parameter_name;
2222 l_cells(i+2) := l_prec.parameter_value;
2223 l_cells(i+3) := l_prec.parameter_description;
2224 l_cells(i+4) := l_prec.default_parameter_value;
2225 i := i+4;
2226 end loop;
2227
2228 dbms_lob.createTemporary(l_temp, TRUE, DBMS_LOB.CALL);
2229 Get_Table(l_cells, 4, 'H', l_temp);
2230 l_cells.DELETE;
2231 dbms_lob.append(dest_lob => p_value, src_lob => l_temp);
2232 l_result := NULL;
2233 dbms_lob.freeTemporary(l_temp);
2234 end loop;
2235 exception
2236 when others then
2237 l_cells.DELETE;
2238 l_cells(1) := '10%:Note';
2239 l_cells(2) := 'Error when generating Parameters Information for GSC Component Type '||p_comp_type;
2240 l_cells(3) := '10%:Error';
2241 l_cells(4) := sqlerrm;
2242 Get_Table(l_cells, 2, 'V', l_result);
2243 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2244 end Get_GSC_Comp_Parameters;
2245
2246
2247 --
2248 -- Get_GSC_Comp_ScheduledEvents - <Explained in WFDIAGPS.pls>
2249 --
2250 -- Returns scheduled events for a given component
2251 -- Out
2252 -- p_value
2253 procedure Get_GSC_Comp_ScheduledEvents(p_comp_type in varchar2,
2254 p_comp_name in varchar2 default null,
2255 p_value in out nocopy clob)
2256 is
2257 l_result varchar2(32767);
2258 l_temp CLOB;
2259 l_cells tdType;
2260 i pls_integer;
2261
2262 -- Cusrsor for components
2263 cursor c_comps is
2264 select component_id,
2265 component_name,
2266 component_status,
2267 startup_mode,
2268 container_type,
2269 inbound_agent_name,
2270 outbound_agent_name,
2271 correlation_id
2272 from fnd_svc_components
2273 where component_type = p_comp_type
2274 and component_name like nvl(p_comp_name, '%')
2275 order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
2276
2277 -- cursor for scheduled events for a component
2278 cursor c_params (p_comp_id in number) is
2279 SELECT component_request_id,
2280 job_id,
2281 event_name,
2282 event_params,
2283 event_date,
2284 event_frequency,
2285 requested_by_user,
2286 b.what,
2287 b.last_Date,
2288 b.last_sec
2289 from fnd_svc_comp_requests a,
2290 user_jobs b
2291 WHERE a.component_id = p_comp_id
2292 AND a.job_id = b.job;
2293
2294 begin
2295 l_cells(1) := 'WH:<b>GSC '||p_comp_type||' Component Scheduled Events </b>';
2296 Get_Table(l_cells, 1, 'H', l_result);
2297 l_cells.DELETE;
2298 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2299
2300 for l_crec in c_comps loop
2301 l_cells(1) := '20%:Component Id';
2302 l_cells(2) := l_crec.component_id;
2303 l_cells(3) := '20%:Component Name';
2304 l_cells(4) := l_crec.component_name;
2305 l_cells(5) := '20%:Component Status';
2306 l_cells(6) := l_crec.component_status;
2307
2308
2309 Get_Table(l_cells, 2, 'V', l_result);
2310 l_result := l_result||wf_core.newline;
2311 l_cells.DELETE;
2312 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2313 l_result := NULL;
2314
2315 l_cells(1) := '12%:Request Id';
2316 l_cells(2) := '12%:Job Id';
2317 l_cells(3) := '12%:Event Name';
2318 l_cells(4) := '12%:Event Params';
2319 l_cells(5) := '12%:Event Frequency';
2320 l_cells(6) := '16%:What';
2321 l_cells(7) := '12%:Last Date';
2322 l_cells(8) := '12%:Last Scheduled';
2323
2324 i := 8;
2325
2326 for l_prec in c_params(l_crec.component_id) loop
2327 l_cells(i+1) := l_prec.component_request_id;
2328 l_cells(i+2) := l_prec.job_id;
2329 l_cells(i+3) := l_prec.event_name;
2330 l_cells(i+4) := l_prec.event_params;
2331 l_cells(i+5) := l_prec.event_frequency;
2332 l_cells(i+6) := l_prec.what;
2333 l_cells(i+7) := l_prec.last_Date;
2334 l_cells(i+8) := l_prec.last_sec;
2335
2336 i := i+8;
2337 end loop;
2338
2339 -- Just add a blank row
2340 l_cells(i+1) := '';
2341 l_cells(i+2) := '';
2342 l_cells(i+3) := '';
2343 l_cells(i+4) := '';
2344 l_cells(i+5) := '';
2345 l_cells(i+6) := '';
2346 l_cells(i+7) := '';
2347 l_cells(i+8) := '';
2348
2349 dbms_lob.createTemporary(l_temp, TRUE, DBMS_LOB.CALL);
2350 Get_Table(l_cells, 8, 'H', l_temp);
2351 l_cells.DELETE;
2352
2353 dbms_lob.append(dest_lob => p_value, src_lob => l_temp);
2354 l_result := NULL;
2355 dbms_lob.freeTemporary(l_temp);
2356 end loop;
2357 exception
2358 when others then
2359 l_cells.DELETE;
2360 l_cells(1) := '10%:Note';
2361 l_cells(2) := 'Error when generating Scheduled Events Information for GSC Component Type '||p_comp_type;
2362 l_cells(3) := '10%:Error';
2363 l_cells(4) := sqlerrm;
2364 Get_Table(l_cells, 2, 'V', l_result);
2365 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2366 end Get_GSC_Comp_ScheduledEvents;
2367
2368 --
2369 -- Get_Profile_Option_Values
2370 -- Fetch the Profile Option Values that are relevant to the Mailer
2371 --
2372 procedure Get_Profile_Option_Values(p_value in out nocopy clob)
2373 is
2374 l_result varchar2(32767);
2375 l_temp CLOB;
2376 l_cells tdType;
2377 i pls_integer;
2378
2379 cursor c_opts is
2380 select profile_option_name, profile_option_value
2381 from fnd_profile_options a, fnd_profile_option_values b
2382 where a.application_id = b.application_id and
2383 a.profile_option_id = b.profile_option_id and
2384 a.profile_option_name in ('APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT',
2385 'AMPOOL_ENABLED', 'ICX_LIMIT_TIME',
2386 'ICX_LIMIT_CONNECT', 'ICX_SESSION_TIMEOUT',
2387 'FRAMEWORK_URL_TIMEOUT')
2388 and b.level_value = 0;
2389
2390 begin
2391 l_cells(1) := 'WH:<b>Profile Option Values</b>';
2392 Get_Table(l_cells, 1, 'H', l_result);
2393 l_cells.DELETE;
2394 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2395
2396 l_cells(1) := '25%:Parameter Name';
2397 l_cells(2) := '25%:Parameter Value';
2398
2399 i := 2;
2400 for l_rec in c_opts loop
2401 l_cells(i+1) := l_rec.profile_option_name;
2402 l_cells(i+2) := l_rec.profile_option_value;
2403 i := i+2;
2404 end loop;
2405
2406 Get_Table(l_cells, 2, 'H', l_result);
2407 l_cells.DELETE;
2408 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2409 exception
2410 when others then
2411 l_cells.DELETE;
2412 l_cells(1) := '10%:Note';
2413 l_cells(2) := 'Error when fetching Profile Option Values';
2414 l_cells(3) := '10%:Error';
2415 l_cells(4) := sqlerrm;
2416 Get_Table(l_cells, 2, 'V', l_result);
2417 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2418 end Get_Profile_Option_Values;
2419
2420
2421 --
2422 -- Get_Override_Charset
2423 -- Returns the override charsets that are defined for
2424 -- the installed languages and db charset
2425 --
2426 procedure Get_Override_Charset(p_value in out nocopy clob)
2427 is
2428
2429 l_result varchar2(32767);
2430 l_temp CLOB;
2431 l_cells tdType;
2432 i pls_integer;
2433
2434 cursor c_override_charset is
2435 select code,
2436 override_email_charset
2437 from wf_override_charsets;
2438
2439 begin
2440 l_cells(1) := 'WH:<b>Override Email Character Set Values</b>';
2441 Get_Table(l_cells, 1, 'H', l_result);
2442 l_cells.DELETE;
2443 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2444
2445 l_cells(1) := '25%:Language Code / DB Charset';
2446 l_cells(2) := '25%:Override IANA Charset';
2447
2448 i := 2;
2449 for l_rec in c_override_charset loop
2450 l_cells(i+1) := l_rec.code;
2451 l_cells(i+2) := l_rec.override_email_charset;
2452 i := i+2;
2453 end loop;
2454
2455 Get_Table(l_cells, 2, 'H', l_result);
2456 l_cells.DELETE;
2457 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2458 exception
2459 when others then
2460 l_cells.DELETE;
2461 l_cells(1) := '10%:Note';
2462 l_cells(2) := 'Error while fetching Override Email Charset Values';
2463 l_cells(3) := '10%:Error';
2464 l_cells(4) := sqlerrm;
2465 Get_Table(l_cells, 2, 'V', l_result);
2466 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2467
2468 end Get_Override_Charset;
2469
2470 --
2471 -- Get_Mailer_Tags
2472 -- Returns a HTML table of all the Mailer Tag information
2473 --
2474 function Get_Mailer_Tags
2475 return varchar2
2476 is
2477 l_result varchar2(32000);
2478 l_temp varchar2(32000);
2479 l_cells tdType;
2480 i pls_integer;
2481 cursor c_tags is
2482 select name,
2483 tag_id,
2484 action,
2485 pattern,
2486 allow_reload
2487 from wf_mailer_tags
2488 order by name;
2489 begin
2490 l_cells(1) := 'WH:<b>Workflow Notification Tags Value</b>';
2491 Get_Table(l_cells, 1, 'H', l_result);
2492 l_cells.DELETE;
2493
2494 l_cells(1) := '20%:Name';
2495 l_cells(2) := '10%:Tag ID';
2496 l_cells(3) := '20%:Action';
2497 l_cells(4) := '40%:Pattern';
2498 l_cells(5) := '10%:Reload';
2499 i := 5;
2500
2501 for l_tag_rec in c_tags loop
2502 l_cells(i+1) := l_tag_rec.name;
2503 l_cells(i+2) := l_tag_rec.tag_id;
2504 l_cells(i+3) := l_tag_rec.action;
2505 l_cells(i+4) := l_tag_rec.pattern;
2506 l_cells(i+5) := l_tag_rec.allow_reload;
2507 i := i+5;
2508 end loop;
2509 Get_Table(l_cells, 5, 'H', l_temp);
2510 return l_result||l_temp;
2511 exception
2512 when others then
2513 l_cells.DELETE;
2514 l_cells(1) := '10%:Note';
2515 l_cells(2) := 'Error when generating Mailer Tags Information';
2516 l_cells(3) := '10%:Error';
2517 l_cells(4) := sqlerrm;
2518 Get_Table(l_cells, 2, 'V', l_result);
2519 return l_result;
2520 end Get_Mailer_Tags;
2521
2522 --
2523 -- Get_Mailer_TOC <Explained in WFDIAGPS.pls>
2524 --
2525 function Get_Mailer_TOC(p_nid in number)
2526 return varchar2
2527 is
2528 l_result varchar2(32000);
2529 l_temp varchar2(32000);
2530 l_cells tdType;
2531
2532 begin
2533 l_cells(1) := 'WH:<b>TABLE OF CONTENTS</b>';
2534 Get_Table(l_cells, 1, 'H', l_result);
2535 l_result := l_result||wf_core.newline;
2536 l_cells.DELETE;
2537
2538 l_cells(1) := '20%:Serial No.';
2539 l_cells(2) := '80%:Contents';
2540 l_cells(3) := '1';
2541 l_cells(4) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_item">Notification Item Information</a>';
2542 l_cells(5) := '2';
2543 l_cells(6) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_group_ntfs">Group Associated Notifications</a>';
2544 l_cells(7) := '3';
2545 l_cells(8) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_rec_role">Notification Recipient Role Members</a>';
2546 l_cells(9) := '4';
2547 l_cells(10) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_rec">Notification Recipient Role Information</a>';
2548 l_cells(11) := '5';
2549 l_cells(12) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_routing_rules">Notification Recipient Routing Rules</a>';
2550 l_cells(13) := '6';
2551 l_cells(14) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_more_info">Notification More Info Role Information</a>';
2552 l_cells(15) := '7';
2553 l_cells(16) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_msg_attr_vals">Notification Message Attribute Values</a>';
2554 l_cells(17) := '8';
2555 l_cells(18) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_attr_vals">Notification Attribute Values</a>';
2556 l_cells(19) := '9';
2557 l_cells(20) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_msg_result_attr_vals">Notification Message Result Attribute Values</a>';
2558 l_cells(21) := '10';
2559 l_cells(22) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_comments">Notification User Comments</a>';
2560 l_cells(23) := '11';
2561 l_cells(24) := '<a href="wfmlrdbg'||p_nid||'.html#def_q">Deferred Queue Status</a>';
2562 l_cells(25) := '12';
2563 l_cells(26) := '<a href="wfmlrdbg'||p_nid||'.html#error_q">Error Queue Status</a>';
2564 l_cells(27) := '13';
2565 l_cells(28) := '<a href="wfmlrdbg'||p_nid||'.html#err_ntf">Error Notification(s)</a>';
2566 l_cells(29) := '14';
2567 l_cells(30) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_out_q">Notification OUT Queue Status</a>';
2568 l_cells(31) := '15';
2569 l_cells(32) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_in_q">Notification IN Queue Status</a>';
2570 l_cells(33) := '16';
2571 l_cells(34) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_templ">Message Templates</a>';
2572 l_cells(35) := '17';
2573 l_cells(36) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_body">Generate Notification Message</a>';
2574 l_cells(37) := '18';
2575 l_cells(38) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_body_out">Message Content from Notification OUT Queue</a>';
2576 l_cells(39) := '19';
2577 l_cells(40) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_body_in">Message Content from Notification IN Queue</a>';
2578 l_cells(41) := '20';
2579 l_cells(42) := '<a href="wfmlrdbg'||p_nid||'.html#profile_opts">Profile Option Values</a>';
2580 l_cells(43) := '21';
2581 l_cells(44) := '<a href="wfmlrdbg'||p_nid||'.html#overide_charset_vals">Override Email Character Set Values</a>';
2582 l_cells(45) := '22';
2583 l_cells(46) := '<a href="wfmlrdbg'||p_nid||'.html#gsc_params">GSC Mailer Component Parameters</a>';
2584 l_cells(47) := '23';
2585 l_cells(48) := '<a href="wfmlrdbg'||p_nid||'.html#gsc_scheduled_evt">GSC Mailer Scheduled Events</a>';
2586 l_cells(49) := '24';
2587 l_cells(50) := '<a href="wfmlrdbg'||p_nid||'.html#ntf_tags">Mailer Tags</a>';
2588 Get_Table(l_cells, 2, 'H', l_temp);
2589 return l_result||l_temp;
2590 exception
2591 when others then
2592 l_cells.DELETE;
2593 l_cells(1) := '10%:Note';
2594 l_cells(2) := 'Error when generating Table of Contents for nid '||p_nid;
2595 l_cells(3) := '10%:Error';
2596 l_cells(4) := sqlerrm;
2597 Get_Table(l_cells, 2, 'V', l_result);
2598 return l_result;
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)
2608 is
2609 l_result varchar2(32000);
2610 l_temp varchar2(32000);
2611 l_cells tdType;
2612 i pls_integer := 1;
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
2622 l_doc clob;
2623 l_amount number;
2624 l_chunksize pls_integer;
2625 l_offset pls_integer;
2626 l_buffer varchar2(32767);
2627
2628 begin
2629
2630 l_cells(1) := 'WH:<b>Content from WF_NOTIFICATION_OUT</b>';
2631 Get_Table(l_cells, 1, 'H', l_result);
2632 l_cells.DELETE;
2633
2634 l_result := l_result||'<table width='||table_width||'>';
2635 l_result := l_result||'<tr bgcolor='||th_bgcolor||'>';
2636 l_result := l_result||'<th align=left><font face='||th_fontface||' size='||th_fontsize||
2637 ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_OUT</font></th></tr>';
2638
2639 for l_lob_rec in cout loop
2640 l_doc := l_lob_rec.lob;
2641
2642 if (i=1) then
2643 l_result := l_result||'</table> ';
2644 end if;
2645
2646 l_result := l_result||'<table width='||table_width||'><tr><td bgcolor='||td_bgcolor||'> <pre> ';
2647 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2648 l_amount := dbms_lob.getlength(l_doc);
2649 l_chunksize := 10000;
2650 l_offset := 1;
2651
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);
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
2668 l_result := l_result||'</pre></td></tr></table><br>';
2669 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2670 l_result := NULL;
2671 i := i+1;
2672 end loop;
2673
2674 if (i=1) then
2675 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'>(not available)</td></tr></table>';
2676 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2677 end if;
2678
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);
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
2697 begin
2698 -- Call with Corr_Id as Null
2699 Get_Ntf_Msg_From_Out(p_nid => p_nid, p_corr_id => null, p_value => p_value);
2700 exception
2701 when others then
2702 DBMS_OUTPUT.PUT_LINE('ERROR =' || sqlerrm); -- DEBUG
2703 RAISE; -- DEBUG
2704 end Get_Ntf_Msg_From_Out;
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
2714 l_result varchar2(32000);
2715 l_temp varchar2(32000);
2716 l_cells tdType;
2717 i pls_integer := 1;
2718
2719 l_sql_str varchar2(4000);
2720
2721 type t_ref_out is ref cursor;
2722 c_ntf_out t_ref_out;
2723
2724
2725 l_doc clob;
2726 l_amount number;
2727 l_chunksize pls_integer;
2728 l_offset pls_integer;
2729 l_buffer varchar2(32767);
2730
2731 begin
2732 l_cells(1) := 'WH:<b>Content from WF_NOTIFICATION_OUT AQ</b>';
2733 Get_Table(l_cells, 1, 'H', l_result);
2734 l_cells.DELETE;
2735
2736 l_result := l_result||'<table width='||table_width||'>';
2737 l_result := l_result||'<tr bgcolor='||th_bgcolor||'>';
2738 l_result := l_result||'<th align=left><font face='||th_fontface||' size='||th_fontsize||
2739 ' color='||th_fontcolor||'>XML for Summary Message from ' ||
2740 ' WF_NOTIFICATION_OUT AQ </font></th></tr>';
2741 l_result := l_result||'</table>';
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;
2750
2751 loop
2752
2753 fetch c_ntf_out into l_doc;
2754 exit when c_ntf_out%NOTFOUND;
2755
2756 l_result := l_result||'<table width='||table_width||'><tr><td bgcolor='||td_bgcolor||'><pre>';
2757
2758 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2759
2760 l_amount := dbms_lob.getlength(l_doc);
2761
2762 l_chunksize := 10000;
2763 l_offset := 1;
2764
2765 loop
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);
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
2782 l_result := l_result||'</pre></td></tr></table><br>';
2783 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2784 l_result := NULL;
2785
2786 i := i+1;
2787
2788 end loop; -- cursor loop
2789
2790 if (i=1) then
2791 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'>(not available)</td></tr></table>';
2792 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2793 end if;
2794
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);
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)
2813 is
2814 l_result varchar2(32000);
2815 l_temp varchar2(32000);
2816 l_cells tdType;
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
2826 l_doc clob;
2827 l_amount number;
2828 l_chunksize pls_integer;
2829 l_offset pls_integer;
2830 l_buffer varchar2(32767);
2831
2832 begin
2833 l_cells(1) := 'WH:<b>Content from WF_NOTIFICATION_IN</b>';
2834 Get_Table(l_cells, 1, 'H', l_result);
2835 l_cells.DELETE;
2836
2837 l_result := l_result||'<table width='||table_width||'>';
2838 l_result := l_result||'<tr bgcolor='||th_bgcolor||'>';
2839 l_result := l_result||'<th align=left><font face='||th_fontface||' size='||th_fontsize||
2840 ' color='||th_fontcolor||'>Notification Message from WF_NOTIFICATION_IN</font></th></tr>';
2841
2842 for l_lob_rec in cin loop
2843 l_doc := l_lob_rec.lob;
2844 if (i=1) then
2845 l_result := l_result||'</table>';
2846 end if;
2847 l_result := l_result||'<table width='||table_width||'><tr><td bgcolor='||td_bgcolor||'><pre>';
2848 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2849 l_amount := dbms_lob.getlength(l_doc);
2850 l_chunksize := 10000;
2851 l_offset := 1;
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);
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
2868 l_result := l_result||'</pre></td></tr></table><br>';
2869 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2870 l_result := NULL;
2871 i := i+1;
2872 end loop;
2873
2874 if (i=1) then
2875 l_result := l_result||'<tr><td bgcolor='||td_bgcolor||'>(not available)</td></tr></table>';
2876 dbms_lob.WriteAppend(p_value, length(l_result), l_result);
2877 end if;
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);
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
2896 begin
2897
2898 Get_Ntf_Msg_From_In(p_nid => p_nid, p_corr_id=> null, p_value=>p_value);
2899
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,
2909 p_event_key in varchar2)
2910 return varchar2
2911 is
2912 l_result varchar2(2000);
2913 l_temp varchar2(32000);
2914 l_cells tdType;
2915 i pls_integer := 1;
2916 l_nid number;
2917 l_date varchar2(20);
2918 l_subject varchar2(2000);
2919 l_err_msg varchar2(2000);
2920 l_err_name varchar2(30);
2921 l_err_stack varchar2(4000);
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'
2931 and wfa.text_value like p_event_name
2932 and wfna.name = 'EVENT_KEY'
2933 and wfna.text_value = p_event_key
2934 order by 1;
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;
2944 c_ntf_attr_rec c_ntf_attr%rowtype;
2945
2946 begin
2947
2948 l_ntf_id := to_number(p_event_key);
2949
2950 l_cells(1) := 'WH:<b>Error Notification(s)</b>';
2951 Get_table(l_cells, 1, 'H', l_temp);
2952 l_cells.DELETE;
2953
2954
2955 begin
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
2965 AND wn.item_key = wias.item_key;
2966
2967
2968 if ((l_err_msg is not null) or (l_err_name is not null) or (l_err_stack is not null)) then
2969
2970 l_cells(1) := '20%:<br>Notification ID';
2971 l_cells(2) := '<b><br>'||l_nid||'</b>';
2972 Get_Table(l_cells, 2, 'V', l_result);
2973 l_temp := l_temp || l_result;
2974
2975 l_cells(1) := '20%:Date';
2976 l_cells(2) := l_date;
2977 Get_Table(l_cells, 2, 'V', l_result);
2978 l_temp := l_temp || l_result;
2979
2980 l_cells(1) := '20%:Notification Subject';
2981 l_cells(2) := l_subject;
2982 Get_Table(l_cells, 2, 'V', l_result);
2983 l_temp := l_temp || l_result;
2984
2985 l_cells(1) := '20%:Error Message';
2986 l_cells(2) := l_err_msg;
2987 Get_Table(l_cells, 2, 'V', l_result);
2988 l_temp := l_temp || l_result;
2989
2990 l_cells(1) := '20%:Error';
2991 l_cells(2) := l_err_name;
2992 Get_Table(l_cells, 2, 'V', l_result);
2993 l_temp := l_temp || l_result;
2994
2995 l_cells(1) := '20%:Error Stack';
2996 l_cells(2) := l_err_stack;
2997 Get_Table(l_cells, 2, 'V', l_result);
2998 l_temp := l_temp || l_result;
2999
3000 i := i+1;
3001
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
3011
3012 for c_ntf_rec in c_ntf loop
3013 l_cells(1) := '20%:<br>Notification ID';
3014 l_cells(2) := '<b><br>'||c_ntf_rec.nid||'</b>';
3015 Get_Table(l_cells, 2, 'V', l_result);
3016 l_temp := l_temp || l_result;
3017
3018 l_cells(1) := '20%:Date';
3019 l_cells(2) := c_ntf_rec.dt;
3020 Get_Table(l_cells, 2, 'V', l_result);
3021 l_temp := l_temp || l_result;
3022
3023 l_cells(1) := '20%:Notification Subject';
3024 l_cells(2) := c_ntf_rec.ntf_sub;
3025 Get_Table(l_cells, 2, 'V', l_result);
3026 l_temp := l_temp || l_result;
3027
3028 for c_ntf_attr_rec in c_ntf_attr(c_ntf_rec.nid) loop
3029 l_cells(1) := '20%:'||c_ntf_attr_rec.name;
3030
3031 if(c_ntf_attr_rec.value is not null) then
3032 l_cells(2) := c_ntf_attr_rec.value;
3033 else
3034 l_cells(2) := ' ';
3035 end if;
3036 Get_Table(l_cells, 2, 'V', l_result);
3037 l_temp := l_temp || l_result;
3038 end loop;
3039 i := i+1;
3040 end loop;
3041
3042 if (i=1) then
3043 l_cells.DELETE;
3044 l_cells(1) := '100%:No error notifications were found';
3045 l_cells(2) := '';
3046 Get_Table(l_cells, 1, 'H', l_result);
3047 l_temp := l_temp || l_result;
3048 end if;
3049
3050 return l_temp;
3051
3052 exception
3053 when others then
3054 l_cells.DELETE;
3055 l_cells(1) := '10%:Note';
3056 l_cells(2) := 'Exception encountered when fetching Error Notification Details';
3057 l_cells(3) := '10%:Error';
3058 l_cells(4) := sqlerrm;
3059 Get_Table(l_cells, 2, 'V', l_result);
3060 return l_result;
3061
3062 end Get_Error_Ntf_Details;
3063
3064 --
3065 -- Get_Mailer_Debug - <Explained in WFDIAGPS.pls>
3066 --
3067 procedure Get_Mailer_Debug(p_nid in number,
3068 p_value out nocopy clob)
3069 is
3070 l_value clob;
3071 l_temp_result varchar2(32000);
3072 l_amount number;
3073 l_dummy varchar2(1);
3074 l_cells tdType;
3075 l_anchor varchar2(100);
3076 l_head varchar2(100);
3077 l_group_cnt number;
3078
3079 begin
3080 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.session);
3081 dbms_lob.CreateTemporary(l_temp, TRUE, dbms_lob.session);
3082 l_head := '<html><head><title>'||to_char(p_nid)||' - wfmlrdbg output</title></head><body>';
3083 dbms_lob.WriteAppend(l_value, length(l_head), l_head);
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:<b>Notification Id '||p_nid||' has been purged and is not available in the Database.</b>';
3093 Get_Table(l_cells, 1, 'H', l_temp_result);
3094 l_cells.DELETE;
3095 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3096
3097 -- Profile Option Values
3098 l_temp_result := '<br>'||wf_core.newline;
3099 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3100 Get_Profile_Option_Values(l_value);
3101
3102 -- Get IANA Override charset values
3103 l_temp_result := '<br>'||wf_core.newline;
3104 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3105 Get_Override_Charset(l_value);
3106
3107 -- GSC Mailer component parameters
3108 l_temp_result := '<br>'||wf_core.newline;
3109 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3110 Get_GSC_Comp_Parameters('WF_MAILER', null, l_value);
3111
3112 -- GSC Mailer component scheduled events
3113 l_temp_result := '<br>'||wf_core.newline;
3114 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3115 Get_GSC_Comp_ScheduledEvents('WF_MAILER', null, l_value);
3116
3117 -- Mailer Tags
3118 l_temp_result := '<br>'||Get_Mailer_Tags()||wf_core.newline;
3119 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3120
3121 -- Send the final HTML Output to the caller
3122 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3123
3124 p_value := l_value;
3125 return;
3126 end;
3127
3128 -- Get Table of Contents
3129 l_temp_result := '<a name=top>'||Get_Mailer_TOC(p_nid)||wf_core.newline;
3130 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3131
3132 -- Get Notfication Item Info
3133 l_temp_result := '<br><a name=ntf_item>'||Get_Ntf_Item_Info(p_nid)||wf_core.newline;
3134 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3135 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3136 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3137
3138 -- Bug 14365670. Consider the case when this is the NID of a group of 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;
3148 end if;
3149
3150 -- Get Group associated notifications
3151 l_temp_result := '<br><a name=ntf_group_ntfs>'||Get_Group_Ntfs(p_nid)||wf_core.newline;
3152 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3153 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3154 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3155
3156 -- Get Users belonging to the recipient Role
3157 l_temp_result := '<br><a name=ntf_rec_role>'||Get_Ntf_Role_Users(p_nid)||wf_core.newline;
3158 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3159 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3160 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3161
3162 -- Get the recipient Role Information
3163 l_temp_result := '<br><a name=ntf_rec>'||Get_Ntf_Role_Info(p_nid)||wf_core.newline;
3164 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3165 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3166 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3167
3168 -- Get the Recipient Role routing rules information
3169 l_temp_result := '<br><a name=ntf_routing_rules>'||Get_Routing_Rules(p_nid)||wf_core.newline;
3170 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3171 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3172 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3173
3174 -- Get the Nore Info Role Information
3175 l_temp_result := '<br><a name=ntf_more_info>'||Get_Ntf_More_Info(p_nid)||wf_core.newline;
3176 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3177 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3178 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3179
3180 l_temp_result := '<br><a name=ntf_msg_attr_vals>'||wf_core.newline;
3181 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3182 Get_Ntf_Msg_Attrs(p_nid, l_value);
3183 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3184 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3185 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3186
3187 l_temp_result := '<br><a name=ntf_attr_vals>'||wf_core.newline;
3188 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3189 Get_Ntf_Attrs(p_nid, l_value);
3190 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3191 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3192 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3193
3194 -- Get Notification result attribute values
3195 l_temp_result := '<br><a name=ntf_msg_result_attr_vals>'||wf_core.newline;
3196 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3197 Get_Ntf_Msg_Result_Attrs(p_nid, l_value);
3198 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3199 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3200 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3201
3202 -- Get the Notification User comments
3203 l_temp_result := '<br><a name=ntf_comments>'||wf_core.newline;
3204 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3205 Get_User_Comments(p_nid, l_value);
3206 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3207 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3208 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3209
3210 -- Queue Statuses
3211 -- Deferred queue status
3212 l_temp_result := '<br><a name=def_q>'
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||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3218 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3219 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3220
3221 -- Error Queue Status
3222 l_temp_result := '<br><a name=error_q>'
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||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3228 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3229 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3230
3231 -- Error Notification Details
3232 l_temp_result := '<br><a name=err_ntf>'
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||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3237 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3238 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3239
3240 -- Notification OUT Queue Status
3241 l_temp_result := '<br><a name=ntf_out_q>'
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||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3247 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3248 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3249
3250 -- Notification IN Queue Status
3251 l_temp_result := '<br><a name=ntf_in_q>'
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||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3257 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3258 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3259
3260 -- Email and Notification message definition
3261 l_temp_result := '<br><a name=ntf_templ>'||wf_core.newline;
3262 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3263 Get_Ntf_Templates(p_nid, l_value);
3264 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3265 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3266 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3267
3268 -- XML Message for the notification
3269 l_temp_result := '<br><a name=ntf_body>'||wf_core.newline;
3270 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3271 Get_Ntf_Message(p_nid, l_value);
3272 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3273 ' size='||td_fontsize||'>Go to top</font></a><br>'||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 := '<br><a name=ntf_body_out>'||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 := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3281 ' size='||td_fontsize||'>Go to top</font></a><br>'||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 := '<br><a name=ntf_body_in>'||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 := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3289 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3290 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3291
3292 -- Profile Option Values
3293 l_temp_result := '<br><a name=profile_opts>'||wf_core.newline;
3294 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3295 Get_Profile_Option_Values(l_value);
3296 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3297 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3298 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3299
3300 -- IANA Override Character Set Values
3301 l_temp_result := '<br><a name=overide_charset_vals>'||wf_core.newline;
3302 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3303 Get_Override_Charset(l_value);
3304 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3305 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3306 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3307
3308 -- GSC Mailer component parameters
3309 l_temp_result := '<br><a name=gsc_params>'||wf_core.newline;
3310 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3311 Get_GSC_Comp_Parameters('WF_MAILER', null, l_value);
3312 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3313 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3314 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3315
3316 -- GSC Mailer component scheduled events
3317 l_temp_result := '<br><a name=gsc_scheduled_evt>'||wf_core.newline;
3318 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3319 Get_GSC_Comp_ScheduledEvents('WF_MAILER', null, l_value);
3320 l_temp_result := '<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3321 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3322 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3323
3324 -- Mailer Tags
3325 l_temp_result := '<br><a name=ntf_tags>'||Get_Mailer_Tags()||wf_core.newline;
3326 l_temp_result := l_temp_result||'<a href=wfmlrdbg'||p_nid||'.html#top><font face='||td_fontface||
3327 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
3328 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3329
3330
3331 -- Send the final HTML Output to the caller
3332 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3333 p_value := l_value;
3334 exception
3335 when others then
3336 l_temp_result := 'Error encountered while Generating Mailer Debug Information for nid '||p_nid||wf_core.newline;
3337 l_temp_result := l_temp_result||'Error Name : '||wf_core.newline||wf_core.error_name;
3338 l_temp_result := l_temp_result||'Error Stack: '||wf_core.newline||wf_core.error_stack;
3339 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3340 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3341 p_value := l_value;
3342 end Get_Mailer_Debug;
3343
3344 --
3345 -- Get_Control_Queue_Status - <Explained in WFDIAGPS.pls>
3346 --
3347 procedure Get_Control_Queue_Status(p_value out nocopy clob)
3348 is
3349 l_subscriber_number NUMBER := 0;
3350
3351 queue_corruption EXCEPTION;
3352 PRAGMA EXCEPTION_INIT(queue_corruption, -24026);
3353
3354 job_count NUMBER ;
3355 l_temp_result varchar2(32000);
3356 l_value clob;
3357 l_dead_subscriber NUMBER;
3358 l_subscriber varchar2(300);
3359
3360 type t_controlq is ref cursor;
3361 l_unresponded_subs_c t_controlq;
3362 begin
3363 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.Session);
3364 -- Set up Header
3365 l_temp_result := g_head ;
3366 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3367
3368 l_temp_result := '<table width="100%">';
3369
3370 -- Removed check for concurrent request in order to make this work in Standalone too
3371 -- Originally the control queue cleanup is set up as DBMS job by wfctqcln.sql.
3372 -- Check if the dbms_job is running
3373 select count(1) into job_count
3374 from user_jobs
3375 where upper(what) like '%WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS%'
3376 and broken = 'N';
3377
3378 if (job_count = 0) then
3379 l_temp_result := l_temp_result || '<tr><td>DBMS JOB for Control Queue cleanup is not Running</td></tr> ';
3380 end if;
3381 l_temp_result := l_temp_result || ' </table>';
3382
3383 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3384
3385 l_temp_result := '<table width="100%">';
3386
3387 execute immediate 'SELECT COUNT(1) FROM '||g_qowner||'.AQ$WF_CONTROL_S' INTO l_subscriber_number;
3388
3389 -- If the number is less than 1024, return
3390 -- the message.
3391 IF (l_subscriber_number < 1024) THEN
3392 -- return message saying that the number of subscriber is OK.
3393 l_temp_result := l_temp_result || '<tr><td>WF Control Queue has less than 1024 active subscribers.</td></tr> </table> ';
3394 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3395 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3396 p_value := l_value;
3397 return;
3398 END IF;
3399
3400
3401 l_temp_result := l_temp_result || '<tr><td>1024 Subscribers have subscribed to WF_CONTROL queue.</td></tr> ';
3402
3403 -- Now try to remove an unused subscriber.
3404 execute immediate 'SELECT COUNT(1) '||
3405 'FROM WF_BES_SUBSCRIBER_PINGS wbsp, '||g_qowner||'.AQ$WF_CONTROL_S sub '||
3406 'WHERE sub.name = wbsp.subscriber_name '||
3407 'AND sub.queue = wbsp.queue_name '||
3408 'AND wbsp.queue_name = ''WF_CONTROL'' '||
3409 'AND wbsp.status IN (''REMOVE_FAILED'', ''PINGED'') '||
3410 'AND wbsp.ping_time < SYSDATE - 1/48'
3411 INTO l_dead_subscriber;
3412
3413 IF (l_dead_subscriber > 0) THEN
3414 l_temp_result := l_temp_result || '<tr><td>' || l_dead_subscriber || ' dead subscribers subscribe to WF_CONTROL queue. </td></tr> ';
3415
3416 -- Ref cursor in order to make the schema name based on WF_SCHEMA.
3417 OPEN l_unresponded_subs_c FOR
3418 'SELECT wbsp.subscriber_name ' ||
3419 'FROM WF_BES_SUBSCRIBER_PINGS wbsp, '||g_qowner||'.AQ$WF_CONTROL_S sub ' ||
3420 'WHERE sub.name = wbsp.subscriber_name '||
3421 'AND sub.queue = wbsp.queue_name '||
3422 'AND wbsp.queue_name = ''WF_CONTROL'' '||
3423 'AND wbsp.status IN (''REMOVE_FAILED'', ''PINGED'') '||
3424 'AND wbsp.ping_time < SYSDATE - 1/48';
3425
3426 FETCH l_unresponded_subs_c INTO l_subscriber;
3427 CLOSE l_unresponded_subs_c;
3428
3429 -- manually remove the AQ subscriber.
3430 BEGIN
3431 dbms_aqadm.remove_subscriber(
3432 g_qowner||'.WF_CONTROL',
3433 sys.aq$_agent(l_subscriber, null,null));
3434 l_temp_result := l_temp_result || '<tr><td> Removing one dead subscriber succeeded </td></tr> ';
3435
3436 EXCEPTION
3437 WHEN queue_corruption THEN
3438 l_temp_result := l_temp_result || '<tr><td> ORA-24026 happened, WF_CONTROL queue corrupted. Please re-create WF_CONTROL queue.</td></tr>';
3439
3440 WHEN OTHERS THEN
3441 l_temp_result := l_temp_result || '<tr><td>Remove Subscriber operation failed with Error ' || SQLCODE || ' message ' || SQLERRM || ' </td></tr>';
3442 END;
3443 END IF;
3444
3445 l_temp_result := l_temp_result || '</table>';
3446 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3447
3448 -- Send the final HTML Output to the caller
3449 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3450
3451 p_value := l_value;
3452 end Get_Control_Queue_Status;
3453
3454 --
3455 -- Get_BES_Clone_Status - <Explained in WFDIAGPS.pls>
3456 --
3457 procedure Get_BES_Clone_Status(p_value out nocopy clob)
3458 is
3459 l_sys_name VARCHAR2(240);
3460 l_temp_result VARCHAR2(32000);
3461 l_value clob;
3462 cursor c_agent is
3463 SELECT '<tr><td> ' || a.NAME || '</td>' agent_name ,
3464 '<td> ' || s.NAME || '</td>' sys_name,
3465 '<td> ' || a.status ||'</td></tr>' 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,
3473 DECODE(sub.guid, NULL, 'Subscription Not Defined',
3474 DECODE(sub.rule_function, NULL, 'Not Defined',
3475 sub.rule_function || '@' || s.name)) RULE_FUNCTION,
3476 DECODE(sub.guid, NULL, 'Subscription Not Defined',
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'
3486 AND e.licensed_flag = 'Y'
3487 AND sub.system_guid = s.guid(+)
3488 AND oa.guid(+) = sub.out_agent_guid
3489 AND oa.system_guid = oas.guid(+)
3490 ORDER BY e.name;
3491 begin
3492 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.Session);
3493 -- Set up Header
3494 l_temp_result := g_head ;
3495 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3496
3497 l_temp_result := '<table width="100%">';
3498
3499 SELECT WF_EVENT.LOCAL_SYSTEM_NAME INTO l_sys_name FROM dual;
3500
3501 l_temp_result := l_temp_result || '<tr><td>The local System Name is ' || l_sys_name || '</td></tr>';
3502
3503 l_temp_result := l_temp_result || '</table>';
3504 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3505
3506 l_temp_result := '<table width="100%">';
3507 l_temp_result := l_temp_result || '<tr><td class=h>Agent Name </td><td class=h>System Name</td><td class=h>Status</td></tr>';
3508
3509 for r_agent in c_agent loop
3510 l_temp_result := l_temp_result || r_agent.agent_name || r_agent.sys_name || r_agent.status;
3511 end loop;
3512
3513 l_temp_result := l_temp_result || '</table>';
3514 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3515
3516 l_temp_result := '<table width="100%">';
3517 l_temp_result := l_temp_result || '<tr><td>Event Name </td><td class=h>Subscription Rule Function</td><td class=h>Subscription Out Agent</td><td class=h>Status</td></tr>';
3518
3519 for r_sub in c_sub loop
3520 l_temp_result := l_temp_result || '<tr><td> '|| r_sub.event_name ||'</td>';
3521 l_temp_result := l_temp_result || '<td> '|| r_sub.RULE_FUNCTION ||'</td>';
3522 l_temp_result := l_temp_result || '<td> '|| r_sub.OUT_AGENT ||'</td>';
3523 l_temp_result := l_temp_result || '<td> '|| r_sub.STATUS ||'</td></tr>';
3524
3525 end loop;
3526 l_temp_result := l_temp_result || '</table>';
3527 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
3528
3529
3530 -- Send the final HTML Output to the caller
3531 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
3532
3533 p_value := l_value;
3534 end Get_BES_Clone_Status;
3535
3536 --
3537 -- Show <Explained in WFDIAGPS.pls
3538 --
3539 procedure Show(p_value in varchar2)
3540 is
3541 l_amount number;
3542 l_chunksize pls_integer;
3543 l_offset pls_integer;
3544 l_buffer varchar2(4000);
3545 l_buffer_size pls_integer;
3546 l_last_cr pls_integer;
3547 l_last_gt pls_integer;
3548 l_last_sc pls_integer;
3549 l_value varchar2(32000);
3550 begin
3551 l_amount := length(p_value);
3552 l_chunksize := 100;
3553 l_offset := 1;
3554 l_buffer_size := 0;
3555 if (l_amount > 0) then
3556 loop
3557 l_last_cr := 0;
3558 if (l_amount > l_chunksize) then
3559 l_buffer := substr(p_value, l_offset, l_chunksize);
3560 l_last_cr := instr(l_buffer, wf_core.newline, -1);
3561 l_last_gt := instr(l_buffer, '>', -1);
3562 l_last_sc := instr(l_buffer, ';', -1);
3563 if (l_last_cr > 1) then
3564 l_buffer_size := l_last_cr;
3565 elsif (l_last_gt > 1) then
3566 l_buffer_size := l_last_gt;
3567 elsif (l_last_sc > 1) then
3568 l_buffer_size := l_last_sc;
3569 else
3570 l_buffer_size := l_chunksize;
3571 end if;
3572 dbms_output.put_line(substr(l_buffer, 1, l_buffer_size));
3573 l_amount := l_amount - l_buffer_size + 1;
3574 l_offset := l_offset + l_buffer_size;
3575 l_buffer := '';
3576 else
3577 l_buffer := substr(p_value, l_offset, l_amount);
3578 dbms_output.put_line(l_buffer);
3579 exit;
3580 end if;
3581 end loop;
3582 end if;
3583 end Show;
3584
3585 --
3586 -- Show <Explained in WFDIAGPS.pls
3587 --
3588 procedure Show(p_value in CLOB)
3589 is
3590 l_amount number;
3591 l_chunksize pls_integer;
3592 l_offset pls_integer;
3593 l_buffer varchar2(4000);
3594 l_tmpbuff varchar2(32000);
3595 l_buffer_size pls_integer;
3596 l_last_cr pls_integer;
3597 l_last_gt pls_integer;
3598 l_last_sc pls_integer;
3599 l_value varchar2(32000);
3600 begin
3601 l_amount := dbms_lob.GetLength(p_value);
3602 l_chunksize := 100;
3603 l_offset := 1;
3604 l_buffer_size := 0;
3605 if (l_amount > 0) then
3606 loop
3607 l_last_cr := 0;
3608 if (l_amount > l_chunksize) then
3609 dbms_lob.read(p_value, l_chunksize, l_offset, l_buffer);
3610 l_last_cr := instr(l_buffer, wf_core.newline, -1);
3611 l_last_gt := instr(l_buffer, '>', -1);
3612 l_last_sc := instr(l_buffer, ';', -1);
3613 if (l_last_cr > 1) then
3614 l_buffer_size := l_last_cr;
3615 elsif (l_last_gt > 1) then
3616 l_buffer_size := l_last_gt;
3617 elsif (l_last_sc > 1) then
3618 l_buffer_size := l_last_sc;
3619 else
3620 l_buffer_size := l_chunksize;
3621 end if;
3622 dbms_output.put_line(substr(l_buffer, 1, l_buffer_size));
3623 l_amount := l_amount - l_buffer_size;
3624 l_offset := l_offset + l_buffer_size;
3625 l_buffer := '';
3626 else
3627 dbms_lob.read(p_value, l_amount, l_offset, l_buffer);
3628 dbms_output.put_line(l_buffer);
3629 exit;
3630 end if;
3631 end loop;
3632 end if;
3633 end Show;
3634
3635 --
3636 -- CheckObjectsValidity - <Explained in WFDIAGS.pls>
3637 --
3638 procedure CheckObjectsValidity(p_status out nocopy varchar2,
3639 p_report out nocopy varchar2)
3640 is
3641 CURSOR c_objs IS
3642 SELECT uo.object_name name,
3643 uo.object_type type,
3644 uo.status status
3645 FROM user_objects uo
3646 WHERE (uo.object_name LIKE 'WF%'
3647 OR uo.object_name LIKE 'ECX%'
3648 OR uo.object_name LIKE 'FND_SVC_%')
3649 AND uo.object_type IN ('PACKAGE', 'PACKAGE BODY')
3650 AND uo.status <> 'VALID'
3651 ORDER BY 1, 2;
3652
3653 l_invalids varchar2(32000);
3654 obj_rec c_objs%ROWTYPE;
3655 l_temp varchar2(32000);
3656 l_cells tdType;
3657 i pls_integer;
3658 begin
3659 -- Get the title
3660 l_cells(1) := 'WH:<b>Workflow/XML Gateway Database invalid objects report.</b>';
3661 Get_Table(l_cells, 1, 'H', l_temp);
3662 l_cells.DELETE;
3663 p_report := l_temp;
3664
3665 open c_objs;
3666 fetch c_objs into obj_rec;
3667 if (c_objs%NOTFOUND) then
3668 p_status := 'SUCCESS';
3669 l_cells(1) := 'TD:All the Workflow and XML Gateway Database objects are valid.';
3670 Get_Table(l_cells, 1, 'H', l_temp);
3671 l_cells.DELETE;
3672 p_report := p_report || l_temp;
3673 else
3674 p_status := 'FAIL';
3675 l_cells(1) := '50%:Object Name';
3676 l_cells(2) := '50%:Object Type';
3677 i := 2;
3678 loop
3679 l_cells(i+1) := obj_rec.name;
3680 l_cells(i+2) := obj_rec.type;
3681 i := i+2;
3682 fetch c_objs into obj_rec;
3683 exit when c_objs%NOTFOUND;
3684 end loop;
3685 Get_table(l_cells, 2, 'H', l_temp);
3686 l_cells.DELETE;
3687 p_report := p_report || l_temp;
3688 end if;
3689 close c_objs;
3690 exception
3691 when others then
3692 -- No SQLException is sent to Java
3693 p_status := 'FAIL';
3694 l_cells(1) := 'TD:<b>Error: '||sqlerrm||'</b>';
3695 Get_table(l_cells, 1, 'H', l_temp);
3696 p_report := p_report || l_temp;
3697 l_cells.DELETE;
3698 close c_objs;
3699 end CheckObjectsValidity;
3700
3701 --
3702 -- CheckXMLParserStatus
3703 -- Checks the installation status of XML Parser.
3704 procedure CheckXMLParserStatus(p_status out nocopy varchar2,
3705 p_report out nocopy varchar2)
3706 is
3707 CURSOR c_dom IS
3708 SELECT status, owner
3709 FROM all_objects
3710 WHERE object_name = 'XMLDOM'
3711 AND object_type = 'PACKAGE'
3712 AND (owner in ('SYS', 'SYSTEM') OR owner = g_qowner)
3713 UNION
3714 SELECT status, user
3715 FROM user_objects
3716 WHERE object_name = 'XMLDOM'
3717 AND object_type = 'PACKAGE';
3718
3719 CURSOR c_java IS
3720 SELECT object_name, status, owner
3721 FROM all_objects
3722 WHERE object_type = 'JAVA RESOURCE'
3723 AND object_name like '%xmlparser%'
3724 AND (owner in ('SYS', 'SYSTEM') OR owner = g_qowner)
3725 UNION
3726 SELECT object_name, status, user
3727 FROM user_objects
3728 WHERE object_type = 'JAVA RESOURCE'
3729 AND object_name like '%xmlparser%';
3730
3731 dom_rec c_dom%ROWTYPE;
3732 java_rec c_java%ROWTYPE;
3733
3734 l_temp varchar2(32000);
3735 l_cells tdType;
3736 i pls_integer;
3737 l_xml_ver varchar2(1000);
3738 begin
3739
3740 -- Get the XML Parser version
3741 begin
3742 l_xml_ver := Wf_Diagnostics.GetXMLParserVersion();
3743 exception
3744 when others then
3745 l_xml_ver := 'Unable to retrieve XML Parser Version due to error ['||sqlerrm||']';
3746 end;
3747
3748 l_cells(1) := 'WH:<b>XML Parser Version:</b> '||l_xml_ver;
3749 Get_Table(l_cells, 1, 'H', l_temp);
3750 p_report := l_temp||'<br>';
3751 l_cells.DELETE;
3752
3753 l_cells(1) := 'WH:<b>XML Parser Installation Status.</b>';
3754 Get_Table(l_cells, 1, 'H', l_temp);
3755 p_report := p_report||l_temp;
3756 l_cells.DELETE;
3757
3758 l_cells(1) := '40%:Object Name';
3759 l_cells(2) := '20%:Object Type';
3760 l_cells(3) := '20%:Owner';
3761 l_cells(4) := '20%:Status';
3762 i := 4;
3763
3764 p_status := 'SUCCESS';
3765
3766 -- XML Parser PL/SQL package validation
3767 open c_dom;
3768 loop
3769 fetch c_dom into dom_rec;
3770 exit when c_dom%NOTFOUND;
3771 if (dom_rec.status <> 'VALID') then
3772 p_status := 'FAIL';
3773 end if;
3774 l_cells(i+1) := 'XMLDOM';
3775 l_cells(i+2) := 'PACKAGE';
3776 l_cells(i+3) := dom_rec.owner;
3777 l_cells(i+4) := dom_rec.status;
3778 i := i+4;
3779 end loop;
3780 -- XML Parser Java Class
3781 open c_java;
3782 loop
3783 fetch c_java into java_rec;
3784 exit when c_java%NOTFOUND;
3785 if (java_rec.status <> 'VALID') then
3786 p_status := 'FAIL';
3787 td_fontcolor := 'red';
3788 end if;
3789 l_cells(i+1) := java_rec.object_name;
3790 l_cells(i+2) := 'JAVA SOURCE';
3791 l_cells(i+3) := java_rec.owner;
3792 l_cells(i+4) := java_rec.status;
3793 i := i+4;
3794 td_fontcolor := 'black';
3795 end loop;
3796 Get_Table(l_cells, 4, 'H', l_temp);
3797 p_report := p_report||l_temp;
3798 l_cells.DELETE;
3799
3800 -- Close cursors
3801 close c_dom;
3802 close c_java;
3803 exception
3804 when others then
3805 -- The SQLException is not sent to Java
3806 p_status := 'FAIL';
3807 l_cells(1) := 'TD:<b>Error: '||sqlerrm||'</b>';
3808 Get_table(l_cells, 1, 'H', l_temp);
3809 p_report := p_report || l_temp;
3810 l_cells.DELETE;
3811 close c_dom;
3812 close c_java;
3813 end CheckXMLParserStatus;
3814
3815 --
3816 -- CheckAgentsAQStatus
3817 -- Checks the validity of the Agents, AQs associated with the Agents, rules
3818 -- and AQ subscribers
3819 procedure CheckAgentsAQStatus(p_status out nocopy varchar2,
3820 p_report out nocopy CLOB)
3821 is
3822 l_queue_name varchar2(30);
3823 l_owner varchar2(30);
3824
3825 CURSOR c_agents IS
3826 SELECT name, queue_name, status
3827 FROM wf_agents
3828 WHERE (name like 'WF%'
3829 OR name like 'ECX%');
3830
3831 CURSOR c_queue IS
3832 SELECT aq.enqueue_enabled, aq.dequeue_enabled, db1.status queue_status, db2.status table_status
3833 FROM all_queues aq, dba_objects db1, dba_objects db2
3834 WHERE db1.object_name = l_queue_name
3835 AND db1.owner = l_owner
3836 AND db1.object_type = 'QUEUE'
3837 AND aq.name = l_queue_name
3838 AND aq.owner = l_owner
3839 AND db2.object_name = aq.queue_table
3840 AND db2.object_type = 'TABLE'
3841 AND db2.owner = l_owner;
3842
3843 type subs_t is ref cursor;
3844 c_subs subs_t;
3845
3846 type rules_t is ref cursor;
3847 c_rules rules_t;
3848
3849 agt_rec c_agents%ROWTYPE;
3850 que_rec c_queue%ROWTYPE;
3851
3852 type sub_rec_t is record
3853 (
3854 l_quname varchar2(30),
3855 l_subname varchar2(30),
3856 l_address varchar2(1024),
3857 l_protocol number
3858 );
3859 sub_rec sub_rec_t;
3860
3861 type rule_rec_t is record
3862 (
3863 l_rulename varchar2(30),
3864 l_condition varchar2(4000)
3865 );
3866 rule_rec rule_rec_t;
3867
3868 l_temp varchar2(32000);
3869 l_message CLOB;
3870 l_cells tdType;
3871 i pls_integer;
3872 l_dbver varchar2(17);
3873 l_rule_count pls_integer := 0;
3874 l_main_version varchar2(100);
3875 begin
3876 -- Get the version of Database
3877 SELECT version
3878 INTO l_dbver
3879 FROM v$instance;
3880
3881 dbms_lob.CreateTemporary(l_message, TRUE, dbms_lob.Call);
3882
3883 l_cells(1) := 'WH:<b>Workflow Agents/Queues Status report.</b>';
3884 Get_table(l_cells, 1, 'H', l_temp);
3885 dbms_lob.WriteAppend(l_message, length(l_temp), l_temp);
3886 l_cells.DELETE;
3887
3888 p_status := 'SUCCESS';
3889
3890 open c_agents;
3891 loop
3892 fetch c_agents into agt_rec;
3893 exit when c_agents%NOTFOUND;
3894 l_owner := substr(agt_rec.queue_name, 1, instr(agt_rec.queue_name, '.', 1)-1);
3895 l_queue_name := substr(agt_rec.queue_name, instr(agt_rec.queue_name, '.', 1)+1);
3896
3897 open c_queue;
3898 fetch c_queue into que_rec;
3899
3900 if (c_queue%ROWCOUNT > 0) then
3901 -- If a queue is enqueue/dequeue disabled or invalid, this test case will fail
3902 if (que_rec.enqueue_enabled = 'NO' or que_rec.dequeue_enabled = 'NO' or
3903 que_rec.queue_status <> 'VALID' or que_rec.table_status <> 'VALID') then
3904 p_status := 'FAIL';
3905 td_fontcolor := 'red';
3906 end if;
3907 l_cells(1) := '20%:Agent Name';
3908 l_cells(2) := '20%:Agent Status';
3909 l_cells(3) := '20%:Queue Name';
3910 l_cells(4) := '10%:Enqueue Enabled';
3911 l_cells(5) := '10%:Dequeue Enabled';
3912 l_cells(6) := '10%:Queue Status';
3913 l_cells(7) := '10%:Queue Table Status';
3914 l_cells(8) := '<b>'||agt_rec.name||'</b>';
3915 l_cells(9) := agt_rec.status;
3916 l_cells(10) := agt_rec.queue_name;
3917 l_cells(11) := que_rec.enqueue_enabled;
3918 l_cells(12) := que_rec.dequeue_enabled;
3919 l_cells(13) := que_rec.queue_status;
3920 l_cells(14) := que_rec.table_status;
3921 td_fontcolor := 'black';
3922
3923 Get_table(l_cells, 7, 'H', l_temp);
3924 l_temp := '<br>'||l_temp;
3925 dbms_lob.WriteAppend(l_message, length(l_temp), l_temp);
3926 l_temp := '';
3927 l_cells.DELETE;
3928
3929 if (agt_rec.name <> 'WF_CONTROL') then
3930
3931 begin
3932 -- Get Table header for writing Subscribers
3933 l_cells(1) := '30%:Sub Que Name';
3934 l_cells(2) := '30%:Sub Name';
3935 l_cells(3) := '25%:Sub Address';
3936 l_cells(4) := '15%:Sub Protocol';
3937 i := 4;
3938
3939 -- Get the subscribers for queue
3940 open c_subs for 'SELECT s.queue_name, s.name, s.address, s.protocol '||
3941 'FROM '||l_owner||'.aq$_'||l_queue_name||'_s s';
3942 loop
3943 fetch c_subs into sub_rec;
3944 exit when c_subs%NOTFOUND;
3945
3946 l_cells(i+1) := sub_rec.l_quname;
3947 l_cells(i+2) := sub_rec.l_subname;
3948 l_cells(i+3) := sub_rec.l_address;
3949 l_cells(i+4) := sub_rec.l_protocol;
3950 i := i+4;
3951 end loop;
3952 close c_subs;
3953 exception
3954 when others then
3955 l_cells.DELETE;
3956 l_cells(1) := 'TD:No Subscribers for the Agent '||agt_rec.name;
3957 close c_subs;
3958 end;
3959 if (l_cells.COUNT = 1) then
3960 Get_Table(l_cells, 1, 'H', l_temp);
3961 else
3962 Get_Table(l_cells, 4, 'H', l_temp);
3963 end if;
3964 dbms_lob.WriteAppend(l_message, length(l_temp), l_temp);
3965 l_cells.DELETE;
3966
3967 -- Get Table header for writing Subscribers Rules
3968 l_cells(1) := '30%:Rule Name';
3969 l_cells(2) := '70%:Rule Condition';
3970 i := 2;
3971 l_rule_count := 0;
3972 begin
3973 -- Get the rules for the subscribers
3974 open c_rules for 'SELECT name, rule '||
3975 'FROM '||l_owner||'.aq$'||l_queue_name||'_r';
3976 loop
3977 fetch c_rules into rule_rec;
3978 exit when c_rules%NOTFOUND;
3979
3980 l_cells(i+1) := rule_rec.l_rulename;
3981 l_cells(i+2) := rule_rec.l_condition;
3982 i := i+2;
3983 l_rule_count := l_rule_count+1;
3984 end loop;
3985 close c_rules;
3986 exception
3987 when others then
3988 i := 2;
3989 l_rule_count := 0;
3990 close c_rules;
3991 end;
3992
3993 l_main_version := substr(l_dbver, 1, instr(l_dbver, '.', 1, 2)-1);
3994
3995 if (l_rule_count = 0 and to_number(l_main_version) >= 9.2) then
3996 -- for DBs 9.2 or higher, check for rules in DBA_RULES.
3997 begin
3998 open c_rules for 'SELECT s.name name, rule_condition rule '||
3999 'FROM '||l_owner||'.aq$_'||l_queue_name||'_s s, dba_rules r '||
4000 'WHERE (bitand(s.subscriber_type, 1) = 1) '||
4001 'AND s.rule_name = r.rule_name '||
4002 'AND r.rule_owner = :1' using l_owner;
4003
4004 loop
4005 fetch c_rules into rule_rec;
4006 exit when c_rules%NOTFOUND;
4007
4008 l_cells(i+1) := rule_rec.l_rulename;
4009 l_cells(i+2) := rule_rec.l_condition;
4010 i := i+2;
4011 l_rule_count := l_rule_count + 1;
4012 end loop;
4013 close c_rules;
4014 exception
4015 when others then
4016 l_rule_count := 0;
4017 close c_rules;
4018 end;
4019 end if;
4020
4021 if (l_rule_count = 0) then
4022 l_cells.DELETE;
4023 l_cells(1) := 'TD:No Rules for the subscribers of Agent/Queue '||agt_rec.name;
4024 Get_Table(l_cells, 1, 'H', l_temp);
4025 else
4026 Get_Table(l_cells, 2, 'H', l_temp);
4027 end if;
4028 dbms_lob.WriteAppend(l_message, length(l_temp), l_temp);
4029 l_temp := '';
4030 l_cells.DELETE;
4031 end if;
4032 end if;
4033 close c_queue;
4034 end loop;
4035 close c_agents;
4036
4037 -- dbms_lob.Copy(p_report, l_message, dbms_lob.GetLength(l_message), 1, 1);
4038 -- dbms_lob.FreeTemporary(l_message);
4039 p_report := l_message;
4040
4041 end CheckAgentsAQStatus;
4042
4043 --
4044 -- GetXMLParserVersion
4045 -- Gets the XML Parser version in the database. This function is modeled
4046 -- after ECX_UTILS.XMLVersion for use within Standalone Workflow
4047 function GetXMLParserVersion
4048 return varchar2
4049 is language java name 'oracle.xml.parser.v2.XMLParser.getReleaseVersion() returns java.lang.String';
4050
4051 --
4052 -- Get_Evt_Sys_Status
4053 -- Gets the Workflow System Name, Status, GUID
4054 function Get_Evt_Sys_Status(p_event_name in varchar2,
4055 p_event_key in varchar2)
4056 return varchar2
4057 is
4058 l_result varchar2(2000);
4059 l_temp varchar2(32000);
4060 l_cells tdType;
4061 begin
4062 l_cells(1) := 'WH:<b>Event and System Information</b>';
4063 Get_table(l_cells, 1, 'H', l_temp);
4064 l_cells.DELETE;
4065
4066 l_cells(1) := '35%:BUSINESS EVENT NAME';
4067 l_cells(2) := p_event_name;
4068 Get_Table(l_cells, 2, 'V', l_result);
4069 l_temp := l_temp || l_result;
4070
4071 l_cells(1) := '35%:EVENT KEY';
4072 l_cells(2) := p_event_key;
4073 Get_Table(l_cells, 2, 'V', l_result);
4074 l_temp := l_temp || l_result;
4075
4076 l_cells(1) := '35%:WORKFLOW SYSTEM STATUS';
4077 select text into l_result from wf_resources where name='WF_SYSTEM_STATUS' and language='US';
4078 l_cells(2) := l_result;
4079 Get_Table(l_cells, 2, 'V', l_result);
4080 l_temp := l_temp || l_result;
4081
4082 l_cells(1) := '35%:WORKFLOW SYSTEM GUID';
4083 select text into l_result from wf_resources where name='WF_SYSTEM_GUID' and language='US';
4084 l_cells(2) := l_result;
4085 Get_Table(l_cells, 2, 'V', l_result);
4086 l_temp := l_temp || l_result;
4087
4088 l_cells(1) := '35%:WORKFLOW SYSTEM NAME';
4089 select name into l_result from wf_systems where guid=l_cells(2);
4090 l_cells(2) := l_result;
4091 Get_Table(l_cells, 2, 'V', l_result);
4092 l_temp := l_temp || l_result;
4093
4094 return l_temp;
4095
4096 exception
4097 when others then
4098 l_cells.DELETE;
4099 l_cells(1) := '10%:Note';
4100 l_cells(2) := 'Error when fetching Workflow System Information';
4101 l_cells(3) := '10%:Error';
4102 l_cells(4) := sqlerrm;
4103 Get_Table(l_cells, 2, 'V', l_result);
4104 return l_result;
4105
4106 end Get_Evt_Sys_Status;
4107
4108 --
4109 -- Get_Bus_Evt_Info
4110 -- Gets the relevant information for the passed Business Event
4111 function Get_Bus_Evt_Info(p_event_name in varchar2)
4112 return varchar2
4113 is
4114 l_result varchar2(32000);
4115 l_temp varchar2(32000);
4116
4117 l_type varchar2(8);
4118 l_status varchar2(8);
4119 l_own_name varchar2(30);
4120 l_own_tag varchar2(30);
4121 l_gen_func varchar2(240);
4122 l_jgen_func varchar2(240);
4123 l_cust_level varchar2(1);
4124 l_lic_flag varchar2(1);
4125
4126 l_cells tdType;
4127
4128 begin
4129
4130 l_cells(1) := 'WH:<b>Business Event Information</b>';
4131 Get_table(l_cells, 1, 'H', l_result);
4132 l_cells.DELETE;
4133
4134 l_cells(1) := 'Event Type';
4135 l_cells(2) := 'Event Status';
4136 l_cells(3) := 'Owner Name';
4137 l_cells(4) := 'Owner Tag';
4138 l_cells(5) := 'Generate Function';
4139 l_cells(6) := 'Java Generate Function';
4140 l_cells(7) := 'Customization Level';
4141 l_cells(8) := 'License Flag';
4142
4143 select type, status, owner_name, owner_tag,
4144 generate_function gen_func,
4145 java_generate_func jgen_func,
4146 customization_level cust_level,
4147 licensed_flag lic_flag
4148 into l_type, l_status, l_own_name, l_own_tag,
4149 l_gen_func, l_jgen_func, l_cust_level, l_lic_flag
4150 from wf_events
4151 where name = p_event_name;
4152
4153 l_cells(9) := l_type;
4154 l_cells(10) := l_status;
4155 l_cells(11) := l_own_name;
4156 l_cells(12) := l_own_tag;
4157 l_cells(13) := l_gen_func;
4158 l_cells(14) := l_jgen_func;
4159 l_cells(15) := l_cust_level;
4160 l_cells(16) := l_lic_flag;
4161
4162 Get_Table(l_cells, 8, 'H', l_temp);
4163 l_result := l_result || l_temp;
4164 return l_result;
4165
4166 end Get_Bus_Evt_Info;
4167
4168 --
4169 -- Get_Evt_Subs_Cac_Info
4170 -- Gets the details of Subscriptions from the BES Cache
4171 -- Makes use of WF_BES_CACHE.GetSubscriptions.
4172 function Get_Evt_Subs_Cac_Info(p_event_name in varchar2)
4173 return varchar2
4174 is
4175 l_result varchar2(32000);
4176 l_temp varchar2(32000);
4177 l_subs_list wf_event_subs_tab;
4178
4179 l_cells tdType;
4180 j pls_integer;
4181 l_agent_tmp varchar2(80);
4182
4183 begin
4184 l_cells(1) := 'WH:<b>Event Subscriptions using WF_BES_CACHE API</b>';
4185 Get_table(l_cells, 1, 'H', l_result);
4186 l_cells.DELETE;
4187
4188 l_cells(1) := '5%:Source Type';
4189 l_cells(2) := '5%:Source Agent';
4190 l_cells(3) := '5%:Phase';
4191 l_cells(4) := '10%:Rule Data';
4192 l_cells(5) := '10%:Out Agent';
4193 l_cells(6) := '10%:To Agent';
4194 l_cells(7) := '15%:Rule Function';
4195 l_cells(8) := '10%:Workflow Process Type';
4196 l_cells(9) := '10%:Workflow Process Name';
4197 l_cells(10) := '10%:Parameters';
4198 l_cells(11) := '10%:On Error Code';
4199 l_cells(12) := '10%:Action Code';
4200 j := 12;
4201
4202 l_subs_list := WF_BES_CACHE.GetSubscriptions(p_event_name, 'LOCAL', null);
4203
4204 if (l_subs_list is not null) then
4205 for i in 1..l_subs_list.COUNT loop
4206 l_cells(j+1) := l_subs_list(i).SOURCE_TYPE;
4207 l_cells(j+2) := l_subs_list(i).SOURCE_AGENT_GUID;
4208
4209 if (l_subs_list(i).SOURCE_AGENT_GUID is not null) then
4210 select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).SOURCE_AGENT_GUID;
4211 l_cells(j+2) := l_agent_tmp;
4212 end if;
4213
4214 l_cells(j+3) := l_subs_list(i).PHASE;
4215 l_cells(j+4) := l_subs_list(i).RULE_DATA;
4216 l_cells(j+5) := l_subs_list(i).OUT_AGENT_GUID;
4217 if (l_subs_list(i).OUT_AGENT_GUID is not null) then
4218 select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).OUT_AGENT_GUID;
4219 l_cells(j+5) := l_agent_tmp;
4220 end if;
4221
4222 l_cells(j+6) := l_subs_list(i).TO_AGENT_GUID;
4223 if (l_subs_list(i).TO_AGENT_GUID is not null) then
4224 select display_name into l_agent_tmp from wf_agents where guid = l_subs_list(i).TO_AGENT_GUID;
4225 l_cells(j+6) := l_agent_tmp;
4226 end if;
4227
4228 l_cells(j+7) := l_subs_list(i).RULE_FUNCTION;
4229 l_cells(j+8) := l_subs_list(i).WF_PROCESS_TYPE;
4230 l_cells(j+9) := l_subs_list(i).WF_PROCESS_NAME;
4231 l_cells(j+10) := l_subs_list(i).PARAMETERS;
4232 l_cells(j+11) := l_subs_list(i).ON_ERROR_CODE;
4233 l_cells(j+12) := l_subs_list(i).ACTION_CODE;
4234 j := j+12;
4235 end loop;
4236
4237 Get_Table(l_cells, 12, 'H', l_temp);
4238 l_result := l_result || l_temp;
4239 end if;
4240
4241 return l_result;
4242 end Get_Evt_Subs_Cac_Info;
4243
4244 --
4245 -- Get_Evt_Subs_Info
4246 -- Gets the details of Subscriptions from the Database
4247 function Get_Evt_Subs_Info(p_event_name in varchar2)
4248 return varchar2
4249 is
4250 l_result varchar2(32000);
4251 l_temp varchar2(32000);
4252
4253 CURSOR c_subs IS
4254 SELECT subscription_source_type source_type,
4255 ac.display_name source_agent,
4256 subscription_phase phase,
4257 subscription_rule_data rule_data,
4258 aa.display_name out_agent,
4259 ab.display_name to_agent,
4260 subscription_rule_function rule_function,
4261 wf_process_type wf_process_type,
4262 wf_process_name wf_process_name,
4263 subscription_parameters parameters,
4264 subscription_on_error_type error_type
4265 FROM wf_active_subscriptions_v wfact, wf_agents aa,
4266 wf_agents ab, wf_agents ac
4267 WHERE event_name = p_event_name
4268 and wfact.subscription_out_agent_guid = aa.guid(+)
4269 and wfact.subscription_to_agent_guid = ab.guid(+)
4270 and wfact.subscription_source_agent_guid = ac.guid(+);
4271
4272 l_cells tdType;
4273 i pls_integer;
4274
4275 begin
4276 l_cells(1) := 'WH:<b>Event Subscriptions from Database using the view WF_ACTIVE_SUBSCRIPTIONS_V</b>';
4277 Get_table(l_cells, 1, 'H', l_result);
4278 l_cells.DELETE;
4279
4280 l_cells(1) := '5%:Source Type';
4281 l_cells(2) := '5%:Source Agent';
4282 l_cells(3) := '5%:Phase';
4283 l_cells(4) := '10%:Rule Data';
4284 l_cells(5) := '10%:Out Agent';
4285 l_cells(6) := '10%:To Agent';
4286 l_cells(7) := '15%:Rule Function';
4287 l_cells(8) := '10%:Workflow Process Type';
4288 l_cells(9) := '10%:Workflow Process Name';
4289 l_cells(10) := '10%:Parameters';
4290 l_cells(11) := '10%:On Error Code';
4291 i := 11;
4292
4293 for l_rec in c_subs loop
4294 l_cells(i+1) := l_rec.source_type;
4295 l_cells(i+2) := l_rec.source_agent;
4296 l_cells(i+3) := l_rec.phase;
4297 l_cells(i+4) := l_rec.rule_data;
4298 l_cells(i+5) := l_rec.out_agent;
4299 l_cells(i+6) := l_rec.to_agent;
4300 l_cells(i+7) := l_rec.rule_function;
4301 l_cells(i+8) := l_rec.wf_process_type;
4302 l_cells(i+9) := l_rec.wf_process_name;
4303 l_cells(i+10) := l_rec.parameters;
4304 l_cells(i+11) := l_rec.error_type;
4305 i := i+11;
4306 end loop;
4307
4308 Get_Table(l_cells, 11, 'H', l_temp);
4309 l_result := l_result || l_temp;
4310 return l_result;
4311
4312 end Get_Evt_Subs_Info;
4313
4314 --
4315 -- Get_Agent_Lsnrs_Status
4316 -- Gets the details of all the Agent Listeners for component types
4317 -- Workflow Agent Listener, Workflow Java Agent Listener
4318 function Get_Agent_Lsnrs_Status
4319 return varchar2
4320 is
4321 l_result varchar2(32000);
4322 l_temp varchar2(32000);
4323
4324 CURSOR c_comps IS
4325 select component_name comp_name, correlation_id corrid,
4326 inbound_agent_name inbound_agent,
4327 initcap(decode(FND_SVC_COMPONENT.Get_Component_Status(component_name),
4328 'NOT_CONFIGURED', 'Not Configured',
4329 'STOPPED_ERROR', 'Stopped with Error',
4330 'DEACTIVATED_USER', 'User Deactivated',
4331 FND_SVC_COMPONENT.Get_Component_Status(component_name))) status,
4332 component_status_info info
4333 FROM fnd_svc_components_v
4334 WHERE component_type in ('WF_AGENT_LISTENER', 'WF_JAVA_AGENT_LISTENER');
4335
4336 l_cells tdType;
4337 i pls_integer;
4338
4339 begin
4340
4341 l_cells(1) := 'WH:<b>Agent Listener Statuses</b>';
4342 Get_table(l_cells, 1, 'H', l_result);
4343 l_cells.DELETE;
4344
4345 l_cells(1) := '25%:Component Name';
4346 l_cells(2) := '15%:Correlation ID';
4347 l_cells(3) := '15%:Inbound Agent';
4348 l_cells(4) := '15%:Status';
4349 l_cells(5) := '30%:Status Information';
4350 i := 5;
4351
4352 for l_rec in c_comps loop
4353 l_cells(i+1) := l_rec.comp_name;
4354 l_cells(i+2) := l_rec.corrid;
4355 l_cells(i+3) := l_rec.inbound_agent;
4356 l_cells(i+4) := l_rec.status;
4357 l_cells(i+5) := l_rec.info;
4358 i := i+5;
4359 end loop;
4360
4361 Get_Table(l_cells, 5, 'H', l_temp);
4362 l_result := l_result || l_temp;
4363 return l_result;
4364
4365 end Get_Agent_Lsnrs_Status;
4366
4367 --
4368 -- Get_Bes_Debug - <Explained in WFDIAGPS.pls>
4369 --
4370 procedure Get_Bes_Debug(p_event_name in varchar2,
4371 p_event_key in varchar2,
4372 p_value out nocopy clob)
4373 is
4374 l_value clob;
4375 l_temp_result varchar2(32000);
4376 l_dummy varchar2(1);
4377 l_cells tdType;
4378 l_head varchar2(200);
4379 begin
4380
4381 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.session);
4382 l_head := '<html><head><title>'||'Event Info:'||p_event_name||' - wfbesdbg output</title></head><body>';
4383 dbms_lob.WriteAppend(l_value, length(l_head), l_head);
4384
4385 begin
4386 select null
4387 into l_dummy
4388 from wf_events
4389 where name = p_event_name;
4390 exception
4391 when NO_DATA_FOUND then
4392 l_cells(1) := 'WH:<b> Event '||p_event_name||' has been purged and is not available in the Database.</b>';
4393 Get_Table(l_cells, 1, 'H', l_temp_result);
4394 l_cells.DELETE;
4395 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4396 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4397 p_value := l_value;
4398 return;
4399 end;
4400
4401 l_temp_result := '<a name=top>'||wf_core.newline;
4402 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4403
4404 -- Get WF_SYSTEM_STATUS
4405 l_temp_result := '<br><a name=wf_status>'
4406 ||Get_Evt_Sys_Status(p_event_name => p_event_name,
4407 p_event_key => p_event_key)
4408 ||wf_core.newline;
4409 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4410 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4411 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4412
4413 -- Get Business Event Item Info
4414 l_temp_result := '<br><a name=bus_evt>'||Get_Bus_Evt_Info(p_event_name)||wf_core.newline;
4415 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4416 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4417 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4418
4419 -- Get Subscription Info using Cache API
4420 l_temp_result := '<br><a name=sub_info>'||Get_Evt_Subs_Cac_Info(p_event_name)||wf_core.newline;
4421 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4422 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4423 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4424
4425 -- Get Subscription Info from DB
4426 l_temp_result := '<br><a name=sub_info>'||Get_Evt_Subs_Info(p_event_name)||wf_core.newline;
4427 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4428 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4429 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4430
4431 -- Listener Statuses
4432 l_temp_result := '<br><a name=lsnr_info>'||Get_Agent_Lsnrs_Status||wf_core.newline;
4433 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4434 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4435 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4436
4437 -- Queue Statuses
4438 -- Deferred queue status
4439 l_temp_result := '<br><a name=def_q>'
4440 ||Get_Event_Queue_Status(p_queue_name => WFD_DEFERRED,
4441 p_event_name => p_event_name,
4442 p_event_key => p_event_key)
4443 ||wf_core.newline;
4444 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4445 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4446 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4447
4448 -- Java Deferred Queue Status
4449 l_temp_result := '<br><a name=java_def_q>'
4450 ||Get_JMS_Queue_Status(p_queue_name => WFD_JAVA_DEFERRED,
4451 p_event_name => p_event_name,
4452 p_event_key => p_event_key)
4453 ||wf_core.newline;
4454 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4455 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4456 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4457
4458 -- Error Queue Status
4459 l_temp_result := '<br><a name=error_q>'
4460 ||Get_Event_Queue_Status(p_queue_name => WFD_ERROR,
4461 p_event_name => p_event_name,
4462 p_event_key => p_event_key)
4463 ||wf_core.newline;
4464 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4465 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4466 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4467
4468 -- Java Error Queue Status
4469 l_temp_result := '<br><a name=java_err_q>'
4470 ||Get_JMS_Queue_Status(p_queue_name => WFD_JAVA_ERROR,
4471 p_event_name => p_event_name,
4472 p_event_key => p_event_key)
4473 ||wf_core.newline;
4474 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4475 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4476 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4477
4478 -- Error Notification Details
4479 l_temp_result := '<br><a name=err_ntf>'
4480 ||Get_Error_Ntf_Details(p_event_name => p_event_name,
4481 p_event_key => p_event_key)
4482 ||wf_core.newline;
4483 l_temp_result := l_temp_result||'<a href=wfevtdbg'||p_event_key||'.html#top><font face='||td_fontface||
4484 ' size='||td_fontsize||'>Go to top</font></a><br>'||wf_core.newline;
4485 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4486
4487 -- Send the final HTML Output to the caller
4488 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4489 p_value := l_value;
4490 exception
4491 when others then
4492 l_temp_result := 'Error encountered while Generating BES Debug Information for event '||p_event_name||wf_core.newline;
4493 l_temp_result := l_temp_result||'Error Name : '||wf_core.newline||wf_core.error_name;
4494 l_temp_result := l_temp_result||'Error Stack: '||wf_core.newline||wf_core.error_stack;
4495 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4496 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4497 p_value := l_value;
4498 end Get_Bes_Debug;
4499
4500
4501
4502 -- Get_Mailer_Summary_Ntf_Debug
4503 -- Returns information about summary notification for a role
4504 procedure Get_Summary_Mailer_Debug(p_role in varchar2,
4505 p_content out nocopy clob)
4506
4507 is
4508 l_value clob;
4509 l_temp_result varchar2(32000);
4510 l_dummy varchar2(1);
4511 l_cells tdType;
4512 l_head varchar2(200);
4513 l_exist varchar2(1);
4514
4515 l_role varchar2(320);
4516 l_dname varchar2(360);
4517 l_email varchar2(320);
4518 l_npref varchar2(8);
4519 l_lang varchar2(30);
4520 l_terr varchar2(30);
4521 l_orig_sys varchar2(30);
4522 l_orig_id number;
4523 l_install varchar2(1);
4524
4525 begin
4526
4527 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.session);
4528
4529 dbms_lob.CreateTemporary(l_temp, TRUE, dbms_lob.session);
4530
4531 l_head := '<html><head><title>'||'Role Info:'||p_role||' - wfsmrdbg output</title></head><body>';
4532
4533 l_head := l_head||wf_core.newline;
4534
4535 dbms_lob.WriteAppend(l_value, length(l_head), l_head);
4536
4537 -- check Role exist in row exist
4538 -- Get_Ntf_Role_Info / or some DIS Apis
4539 Wf_Directory.GetRoleInfoMail(p_role, l_dname, l_email,
4540 l_npref, l_lang, l_terr,
4541 l_orig_sys, l_orig_id, l_install);
4542
4543 -- check if associated fileds exists for a given role.
4544 -- If not, assume role does not exist and return
4545 if(l_dname is null and
4546 l_email is null and
4547 l_npref is null and
4548 l_lang is null and
4549 l_orig_sys is null ) then
4550
4551 l_cells(1) := 'WH:<b> The role : '||p_role||' does not exist in the Database.</b>';
4552 Get_Table(l_cells, 1, 'H', l_temp_result);
4553 l_cells.DELETE;
4554
4555 -- Add to LOB
4556 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4557 --
4558 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4559
4560 p_content := l_value;
4561 return;
4562 end if;
4563
4564 -- Get Users belonging to the recipient Role
4565 l_temp_result := '<br>' ||Get_Summary_Ntf_Role_Users(p_role)||wf_core.newline;
4566 l_temp_result := l_temp_result || '<br>'||wf_core.newline;
4567 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4568
4569
4570 -- Get the recipient Role Information
4571 l_temp_result := '<br>' ||Get_Summary_Ntf_Role_Info(p_role)||wf_core.newline;
4572 l_temp_result := l_temp_result || '<br>'||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
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 := '<br>' ||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
4590 -- Email and Notification message definition
4591 l_temp_result := '<br>'||wf_core.newline;
4592 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4593 -- Summary template : WFMAIL
4594 Get_Summary_Templates(p_role, l_npref, l_value);
4595
4596 -- WF_XML.generate FOR an event with
4597 -- event key for summary is generated as: role_name + sysdate
4598 --
4599 l_temp_result := '<br>'||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 := '<br>'||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
4608 -- Profile Option Values
4609 l_temp_result := '<br>'||wf_core.newline;
4610 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4611 Get_Profile_Option_Values(l_value);
4612
4613 -- GSC Mailer component parameters
4614 l_temp_result := '<br>'||wf_core.newline;
4615 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4616 Get_GSC_Comp_Parameters('WF_MAILER', null, l_value);
4617
4618 -- GSC Mailer component scheduled events
4619 l_temp_result := '<br>'||wf_core.newline;
4620 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4621 Get_GSC_Comp_ScheduledEvents('WF_MAILER', null, l_value);
4622
4623 -- Mailer Tags
4624 l_temp_result := '<br>'||wf_core.newline;
4625 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4626
4627 l_temp_result := Get_Mailer_Tags()||wf_core.newline;
4628 l_temp_result := l_temp_result || '<br>'||wf_core.newline;
4629 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4630
4631 -- Send the final HTML Output to the caller
4632 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4633
4634 p_content := l_value;
4635
4636 exception
4637 when others then
4638 l_temp_result := 'Error encountered while Generating Summary Mailer Debug '||
4639 'Information for role '||p_role||wf_core.newline;
4640 l_temp_result := l_temp_result||'Error Name : '||wf_core.newline||wf_core.error_name;
4641 l_temp_result := l_temp_result||'Error Stack: '||wf_core.newline||wf_core.error_stack;
4642 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4643 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4644 p_content := l_value;
4645
4646 end Get_Summary_Mailer_Debug; -- end of Summary Mailer dbg
4647
4648 --
4649 -- Get_Mailer_Alert_Debug
4650 -- Returns information about alert for a module
4651 --
4652 procedure Get_Mailer_Alert_Debug(p_module in varchar2,
4653 p_idstring in varchar2,
4654 p_content out nocopy clob)
4655 is
4656 l_value clob;
4657 l_temp_result varchar2(32000);
4658 l_dummy varchar2(1);
4659 l_cells tdType;
4660 l_head varchar2(200);
4661 l_cnt int;
4662
4663 -- session user
4664 l_user varchar(30) := 'APPS';
4665
4666 begin
4667
4668 -- get session user
4669 select user into l_user from dual;
4670
4671 dbms_lob.CreateTemporary(l_value, TRUE, dbms_lob.session);
4672 dbms_lob.CreateTemporary(l_temp, TRUE, dbms_lob.session);
4673
4674 l_head := '<html><head><title>'||'Alert Info:'||p_module||' - wfalrdbg output</title></head><body>';
4675 dbms_lob.WriteAppend(l_value, length(l_head), l_head);
4676
4677 -- Error Notification Details
4678 -- Check whether wf_error aq will have ALERT in case it failes or not.
4679 --
4680 l_temp_result := '<br>'
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
4686 -- Notification OUT Queue Status
4687 l_temp_result := '<br>'
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
4694 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4695
4696 -- Notification IN Queue Status
4697 l_temp_result := '<br>'
4698 ||Get_JMS_Queue_Status(p_queue_name => WFD_NTF_IN,
4699 p_event_name => 'oracle.apps.alr.response.receive%',
4700 p_event_key => to_char(p_idstring) || '%',
4701 p_corr_id => l_user || ':' || p_module || '%')
4702 ||wf_core.newline;
4703
4704 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4705
4706 l_temp_result := '<br>'||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 := '<br><br>'||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 := '<br><br>'||wf_core.newline;
4719 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4720
4721 -- Profile Option Values
4722 Get_Profile_Option_Values(l_value);
4723 l_temp_result := '<br><br>'||wf_core.newline;
4724 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4725
4726 -- GSC Mailer component parameters
4727 Get_GSC_Comp_Parameters('WF_MAILER', null, l_value);
4728 l_temp_result := '<br><br>'||wf_core.newline;
4729 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4730
4731 -- GSC Mailer component scheduled events
4732 Get_GSC_Comp_ScheduledEvents('WF_MAILER', null, l_value);
4733 l_temp_result := '<br>'||wf_core.newline;
4734 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4735
4736 -- Mailer Tags
4737 l_temp_result := '<br><br>'||Get_Mailer_Tags()||wf_core.newline;
4738 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4739
4740 -- Send the final HTML Output to the caller
4741 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4742
4743 p_content := l_value;
4744
4745 exception
4746 when others then
4747 l_temp_result := 'Error encountered while Generating Mailer Alert Debug '||
4748 ' Information for module '||p_module|| '<br>'|| wf_core.newline;
4749 l_temp_result := l_temp_result||'Error Name : '||wf_core.error_name || '<br>' || wf_core.newline;
4750 l_temp_result := l_temp_result||'Error Stack: '||wf_core.newline||wf_core.error_stack;
4751
4752 dbms_lob.WriteAppend(l_value, length(l_temp_result), l_temp_result);
4753 dbms_lob.WriteAppend(l_value, length(g_end), g_end);
4754
4755 p_content := l_value;
4756
4757 end Get_Mailer_Alert_Debug;
4758
4759
4760 end WF_DIAGNOSTICS;