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