[Home] [Help]
PACKAGE BODY: APPS.IEU_DIAGNOSTICS_PVT
Source
1 PACKAGE BODY IEU_Diagnostics_PVT AS
2 /* $Header: IEUVDFB.pls 120.2 2007/12/04 06:41:12 majha ship $ */
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- IEU_Diagnostics_PVT
9 -- Purpose
10 -- To provide easy to use apis for UQW Diagnostic Framework.
11 -- History
12 -- 14-Mar-2002 gpagadal Created.
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18 --===================================================================
19 -- NAME
20 -- Is_ResourceId_Exist
21 --
22 -- PURPOSE
23 -- Private api to determine if resource id exist for the user
24 --
25 -- NOTES
26 -- 1. UWQ Login Diagnostics will use this procedure.
27 --
28 --
29 -- HISTORY
30 -- 14-Mar-2002 GPAGADAL Created
31
32 --===================================================================
33
34 PROCEDURE Is_ResourceId_Exist (x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2,
37 p_user_name IN VARCHAR2
38 )
39 As
40
41 l_user_name FND_USER.USER_NAME%TYPE;
42
43 l_user_id FND_USER.USER_ID%TYPE;
44
45 l_language VARCHAR2(4);
46
47 l_msg_count NUMBER(2);
48
49 l_msg_data VARCHAR2(2000);
50
51 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
52 l_sql VARCHAR2(4000);
53
54
55
56 BEGIN
57
58 fnd_msg_pub.delete_msg();
59 x_return_status := fnd_api.g_ret_sts_success;
60 FND_MSG_PUB.initialize;
61 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
62 x_msg_data := '';
63
64
65 if (p_user_name is null) then
66 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_USER');
67 FND_MSG_PUB.Add;
68 x_return_status := FND_API.G_RET_STS_ERROR;
69
70 else
71
72 begin
73 l_sql := ' select user_id from fnd_user
74 where upper(user_name) like upper( :p_user_name)';
75
76 EXECUTE IMMEDIATE l_sql
77 into l_user_id
78 USING p_user_name;
79 -- DBMS_OUTPUT.PUT_LINE('sql : '||l_sql);
80 --DBMS_OUTPUT.PUT_LINE('l_user_id : '||l_user_id);
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_USER_INVALID');
85 FND_MSG_PUB.Add;
86 x_return_status := FND_API.G_RET_STS_ERROR;
87
88 end;
89
90 end if;
91
92 if NOT(l_user_id is null) then
93 begin
94 l_sql := 'select resource_id
95 from jtf_rs_resource_extns where user_id = :l_user_id';
96
97 EXECUTE IMMEDIATE l_sql
98 into l_resource_id
99 USING l_user_id;
100
101 ----DBMS_OUTPUT.PUT_LINE('sql : '||l_sql);
102 --DBMS_OUTPUT.PUT_LINE('l_resource_id : '||l_resource_id);
103
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 --DBMS_OUTPUT.PUT_LINE('resource id does not exists ');
107 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESOURCEID');
108 FND_MSG_PUB.Add;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 --DBMS_OUTPUT.PUT_LINE('No data found for resource id : ');
111
112 end;
113 end if;
114
115 -- Standard call to get message count and if count is 1, get message info.
116 /*FND_MSG_PUB.Count_And_Get(
117 p_count => x_msg_count,
118 p_data => l_msg_data
119 );*/
120
121
122 x_msg_count := fnd_msg_pub.COUNT_MSG();
123
124 FOR i in 1..x_msg_count LOOP
125 l_msg_data := '';
126 l_msg_count := 0;
127 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
128 x_msg_data := x_msg_data || ',' || l_msg_data;
129 END LOOP;
130
131
132 EXCEPTION
133
134 WHEN FND_API.G_EXC_ERROR THEN
135
136 --DBMS_OUTPUT.PUT_LINE('TError : '||sqlerrm);
137
138 x_return_status := FND_API.G_RET_STS_ERROR;
139
140 x_msg_count := fnd_msg_pub.COUNT_MSG();
141
142 FOR i in 1..x_msg_count LOOP
143 l_msg_data := '';
144 l_msg_count := 0;
145 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
146 x_msg_data := x_msg_data || ',' || l_msg_data;
147 END LOOP;
148
149
150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
151
152 --DBMS_OUTPUT.PUT_LINE('TError : '||sqlerrm);
153
154
155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
156 x_msg_count := fnd_msg_pub.COUNT_MSG();
157
158 FOR i in 1..x_msg_count LOOP
159 l_msg_data := '';
160 l_msg_count := 0;
161 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
162 x_msg_data := x_msg_data || ',' || l_msg_data;
163 END LOOP;
164
165 WHEN OTHERS THEN
166 --DBMS_OUTPUT.PUT_LINE('TError : '||sqlerrm);
167
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 x_msg_count := fnd_msg_pub.COUNT_MSG();
170
171 FOR i in 1..x_msg_count LOOP
172 l_msg_data := '';
173 l_msg_count := 0;
174 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
175 x_msg_data := x_msg_data || ',' || l_msg_data;
176 END LOOP;
177
178
179
180 END Is_ResourceId_Exist;
181
182
183
184
185 --===================================================================
186 -- NAME
187 -- Check_User_Resp
188 --
189 -- PURPOSE
190 -- Private api to check the user id and responsibility
191 --
192 -- NOTES
193 -- 1. UWQ Login Diagnostics will use this procedure.
194 --
195 --
196 -- HISTORY
197 -- 01-Apr-2002 GPAGADAL Created
198
199 --===================================================================
200
201 PROCEDURE Check_User_Resp (x_return_status OUT NOCOPY VARCHAR2,
202 x_msg_count OUT NOCOPY NUMBER,
203 x_msg_data OUT NOCOPY VARCHAR2,
204 p_user_name IN VARCHAR2,
205 p_responsibility IN VARCHAR2,
206 x_user_id OUT NOCOPY NUMBER,
207 x_resp_id OUT NOCOPY NUMBER,
208 x_appl_id OUT NOCOPY NUMBER
209 )
210 AS
211 l_user_name FND_USER.USER_NAME%TYPE;
212
213 l_user_id FND_USER.USER_ID%TYPE;
214
215 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
216
217 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
218
219 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
220
221 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
222
223 l_language VARCHAR2(4);
224
225 l_valid boolean;
226 l_msg_count NUMBER(2);
227
228 l_msg_data VARCHAR2(2000);
229
230 l_media_count INTEGER;
231
232 l_sql VARCHAR2(4000);
233
234
235 BEGIN
236
237 fnd_msg_pub.delete_msg();
238 x_return_status := fnd_api.g_ret_sts_success;
239 FND_MSG_PUB.initialize;
240 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
241 x_msg_data := '';
242
243
244 if (p_user_name is null) then
245
246 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_USER');
247 FND_MSG_PUB.Add;
248 x_return_status := FND_API.G_RET_STS_ERROR;
249
250
251 else
252 begin
253 l_sql := ' select user_id from fnd_user
254 where upper(user_name) like upper(:p_user_name)';
255 EXECUTE IMMEDIATE l_sql
256 INTO l_user_id
257 USING in p_user_name;
258
259 x_user_id := l_user_id;
260
261 --DBMS_OUTPUT.PUT_LINE('l_user_id:='||l_user_id);
262
263 EXCEPTION
264 WHEN NO_DATA_FOUND THEN
265
266
267
268 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_USER_INVALID');
269 FND_MSG_PUB.Add;
270 x_return_status := FND_API.G_RET_STS_ERROR;
271
272
273 end;
274
275
276 end if;
277
278 if (p_responsibility is null) then
279
280 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESP');
281 FND_MSG_PUB.Add;
282 x_return_status := FND_API.G_RET_STS_ERROR;
283
284 else
285 begin
286
287 l_sql := ' select responsibility_id, application_id from fnd_responsibility_tl where language = :l_language and responsibility_id = :p_responsibility';
288 /* l_sql := ' select responsibility_id, application_id //bug 6414726
289 from fnd_responsibility_tl where language = :l_language
290 and responsibility_name like :p_responsibility'; */
291
292 EXECUTE IMMEDIATE l_sql
293 INTO l_responsibility_id, l_application_id
294 USING l_language, p_responsibility;
295
296 x_resp_id := l_responsibility_id;
297 x_appl_id := l_application_id;
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 begin
302 l_sql := ' select responsibility_id, application_id from fnd_responsibility where responsibility_id = :p_responsibility';
303 /* l_sql := ' select responsibility_id, application_id //bug6414726
304 from fnd_responsibility where responsibility_key like :p_responsibility'; */
305 EXECUTE IMMEDIATE l_sql
306 INTO l_responsibility_id, l_application_id
307 USING p_responsibility;
308
309 x_resp_id := l_responsibility_id;
310 x_appl_id := l_application_id;
311
312 EXCEPTION
313
314 WHEN NO_DATA_FOUND THEN
315
316
317 begin
318
319 -- l_responsibility_id := p_responsibility;
320
321 l_sql := ' select application_id
322 from fnd_responsibility where responsibility_id = :p_responsibility';
323
324 EXECUTE IMMEDIATE l_sql
325 INTO l_application_id
326 USING p_responsibility;
327
328 x_resp_id := p_responsibility;
329 x_appl_id := l_application_id;
330
331
332 EXCEPTION
333 WHEN NO_DATA_FOUND THEN
334
335 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESP_USER');
336 FND_MSG_PUB.Add;
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 WHEN others then
339 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_RESP_INVALID');
340 FND_MSG_PUB.Add;
341 x_return_status := FND_API.G_RET_STS_ERROR;
342
343 end;
344
345 end;
346 end;
347
348 end if;
349
350 fnd_global.APPS_INITIALIZE(x_user_id, x_resp_id, x_appl_id, null);
351
352 x_user_id := FND_GLOBAL.USER_ID;
353 x_resp_id := FND_GLOBAL.RESP_ID;
354 x_appl_id := FND_GLOBAL.RESP_APPL_ID;
355
356
357 -- Standard call to get message count and if count is 1, get message info.
358 /* FND_MSG_PUB.Count_And_Get(
359 p_count => x_msg_count,
360 p_data => l_msg_data
361 );*/
362
363
364 x_msg_count := fnd_msg_pub.COUNT_MSG();
365
366 FOR i in 1..x_msg_count LOOP
367 l_msg_data := '';
368 l_msg_count := 0;
369 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
370 x_msg_data := x_msg_data || ',' || l_msg_data;
371 END LOOP;
372
373
374
375 EXCEPTION
376
377 WHEN FND_API.G_EXC_ERROR THEN
378
379
380 --DBMS_OUTPUT.PUT_LINE('TError : '||sqlerrm);
381
382
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 x_msg_count := fnd_msg_pub.COUNT_MSG();
385
386 FOR i in 1..x_msg_count LOOP
387 l_msg_data := '';
388 l_msg_count := 0;
389 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
390 x_msg_data := x_msg_data || ',' || l_msg_data;
391 END LOOP;
392
393
394
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396
397 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
398
399
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 x_msg_count := fnd_msg_pub.COUNT_MSG();
402
403 FOR i in 1..x_msg_count LOOP
404 l_msg_data := '';
405 l_msg_count := 0;
406 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
407 x_msg_data := x_msg_data || ',' || l_msg_data;
408 END LOOP;
409
410
411 WHEN OTHERS THEN
412 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
413 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
414
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 x_msg_count := fnd_msg_pub.COUNT_MSG();
417
418 FOR i in 1..x_msg_count LOOP
419 l_msg_data := '';
420 l_msg_count := 0;
421 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
422 x_msg_data := x_msg_data || ',' || l_msg_data;
423 END LOOP;
424
425
426
427 END Check_User_Resp;
428
429 --===================================================================
430 -- NAME
431 -- Check_Object_Resp
432 --
433 -- PURPOSE
434 -- Private api to check the user id and responsibility
435 --
436 -- NOTES
437 -- 1. UWQ Login Diagnostics will use this procedure.
438 --
439 --
440 -- HISTORY
441 -- 20-Apr-2004 dolee Created
442
443 --===================================================================
444
445 PROCEDURE Check_Object_Resp (x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2,
448 p_object_code IN VARCHAR2,
449 p_responsibility IN VARCHAR2,
450 x_resp_id OUT NOCOPY NUMBER
451 )
452 AS
453 l_object_code jtf_objects_vl.object_code%TYPE;
454
455
456 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
457
458 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
459
460 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
461
462 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
463 x_appl_id number;
464 x_user_id number;
465 l_language VARCHAR2(4);
466 l_count number;
467 l_valid boolean;
468 l_msg_count NUMBER(2);
469
470 l_msg_data VARCHAR2(2000);
471
472 l_media_count INTEGER;
473
474 l_sql VARCHAR2(4000);
475
476
477 BEGIN
478
479 fnd_msg_pub.delete_msg();
480 x_return_status := fnd_api.g_ret_sts_success;
481 FND_MSG_PUB.initialize;
482 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
483 x_msg_data := '';
484
485 begin
486 l_sql := ' select count(object_code) from jtf_objects_b
487 where upper(object_code) like upper(:p_object_code)';
488 EXECUTE IMMEDIATE l_sql
489 INTO l_count
490 USING in p_object_code;
491
492 IF l_count = 0 THEN
493 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_NO_OBJ_INVALID');
494 FND_MSG_PUB.Add;
495 x_return_status := FND_API.G_RET_STS_ERROR;
496
497 END if;
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_NO_OBJ_INVALID');
501 FND_MSG_PUB.Add;
502 x_return_status := FND_API.G_RET_STS_ERROR;
503
504
505 end;
506
507
508
509 if (p_responsibility is null) then
510
511 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESP');
512 FND_MSG_PUB.Add;
513 x_return_status := FND_API.G_RET_STS_ERROR;
514
515 else
516 if (p_responsibility = '-1') then
517 x_resp_id := p_responsibility;
518 else
519 begin
520 l_sql := ' select responsibility_id, application_id
521 from fnd_responsibility_tl where language = :l_language
522 and responsibility_name like :p_responsibility';
523
524 EXECUTE IMMEDIATE l_sql
525 INTO l_responsibility_id, l_application_id
526 USING l_language, p_responsibility;
527
528 x_resp_id := l_responsibility_id;
529 x_appl_id := l_application_id;
530
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 begin
534
535 l_sql := ' select responsibility_id, application_id
536 from fnd_responsibility where responsibility_key like :p_responsibility';
537 EXECUTE IMMEDIATE l_sql
538 INTO l_responsibility_id, l_application_id
539 USING p_responsibility;
540
541 x_resp_id := l_responsibility_id;
542 x_appl_id := l_application_id;
543
544 EXCEPTION
545
546 WHEN NO_DATA_FOUND THEN
547 begin
548
549 -- l_responsibility_id := p_responsibility;
550 l_sql := ' select application_id
551 from fnd_responsibility where responsibility_id = :p_responsibility';
552
553 EXECUTE IMMEDIATE l_sql
554 INTO l_application_id
555 USING p_responsibility;
556
557 x_resp_id := p_responsibility;
558
559
560 EXCEPTION
561 WHEN NO_DATA_FOUND THEN
562
563 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESP_USER');
564 FND_MSG_PUB.Add;
565 x_return_status := FND_API.G_RET_STS_ERROR;
566 WHEN others then
567 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_RESP_INVALID');
568 FND_MSG_PUB.Add;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570
571 end;
572
573 end;
574 end;
575 end if;
576 end if;
577
578 fnd_global.APPS_INITIALIZE(x_user_id, x_resp_id, x_appl_id, null);
579
580 x_user_id := FND_GLOBAL.USER_ID;
581 x_resp_id := FND_GLOBAL.RESP_ID;
582
583
584
585 -- Standard call to get message count and if count is 1, get message info.
586 /* FND_MSG_PUB.Count_And_Get(
587 p_count => x_msg_count,
588 p_data => l_msg_data
589 );*/
590
591
592 x_msg_count := fnd_msg_pub.COUNT_MSG();
593
594 FOR i in 1..x_msg_count LOOP
595 l_msg_data := '';
596 l_msg_count := 0;
597 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
598 x_msg_data := x_msg_data || ',' || l_msg_data;
599 END LOOP;
600
601
602
603 EXCEPTION
604
605 WHEN FND_API.G_EXC_ERROR THEN
606
607
608 --DBMS_OUTPUT.PUT_LINE('TError : '||sqlerrm);
609
610
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 x_msg_count := fnd_msg_pub.COUNT_MSG();
613
614 FOR i in 1..x_msg_count LOOP
615 l_msg_data := '';
616 l_msg_count := 0;
617 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
618 x_msg_data := x_msg_data || ',' || l_msg_data;
619 END LOOP;
620
621
622
623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
624
625 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
626
627
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 x_msg_count := fnd_msg_pub.COUNT_MSG();
630
631 FOR i in 1..x_msg_count LOOP
632 l_msg_data := '';
633 l_msg_count := 0;
634 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
635 x_msg_data := x_msg_data || ',' || l_msg_data;
636 END LOOP;
637
638
639 WHEN OTHERS THEN
640 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
641 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
642
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 x_msg_count := fnd_msg_pub.COUNT_MSG();
645
646 FOR i in 1..x_msg_count LOOP
647 l_msg_data := '';
648 l_msg_count := 0;
649 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
650 x_msg_data := x_msg_data || ',' || l_msg_data;
651 END LOOP;
652
653
654
655 END Check_Object_Resp;
656
657
658 --===================================================================
659 -- NAME
660 -- Determine_Media_Enabled
661 --
662 -- PURPOSE
663 -- Private api to determine if any media queues are enabled.
664 --
665 -- NOTES
666 -- 1. UWQ Login Diagnostics will use this procedure.
667 --
668 --
669 -- HISTORY
670 -- 14-Mar-2002 GPAGADAL Created
671
672 --===================================================================
673
674
675
676 PROCEDURE Determine_Media_Enabled (x_return_status OUT NOCOPY VARCHAR2,
677 x_msg_count OUT NOCOPY NUMBER,
678 x_msg_data OUT NOCOPY VARCHAR2,
679 p_user_name IN VARCHAR2,
680 p_responsibility IN VARCHAR2,
681 x_media_types OUT NOCOPY IEU_DIAG_STRING_NST
682 )
683 AS
684
685
686 l_user_name FND_USER.USER_NAME%TYPE;
687
688 l_user_id FND_USER.USER_ID%TYPE;
689
690 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
691
692 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
693
694 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
695
696 l_work_q_enable_profile_option IEU_UWQ_SEL_ENUMERATORS.WORK_Q_ENABLE_PROFILE_OPTION%TYPE;
697
698 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
699
700 l_language VARCHAR2(4);
701
702 l_media_type_id IEU_UWQ_SEL_ENUMERATORS.MEDIA_TYPE_ID%TYPE;
703
704 l_valid boolean;
705 l_media_type_name IEU_UWQ_MEDIA_TYPES_TL.MEDIA_TYPE_NAME%TYPE;
706
707 l_msg_count NUMBER(2);
708
709 l_msg_data VARCHAR2(2000);
710
711 l_media_count INTEGER;
712
713 v_count BINARY_INTEGER ;
714
715 l_sql VARCHAR2(4000);
716
717
718 CURSOR c_types IS
719 select media_type_id
720 from ieu_uwq_sel_enumerators
721 where ((not_valid_flag is null) or (not_valid_flag = 'N')) and
722 work_q_register_type = 'M' order by media_type_id;
723
724 i integer ;
725
726
727
728 BEGIN
729
730 fnd_msg_pub.delete_msg();
731 x_return_status := fnd_api.g_ret_sts_success;
732 FND_MSG_PUB.initialize;
733 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
734 l_media_type_name := null;
735 x_media_types := IEU_DIAG_STRING_NST();
736 l_media_count := 0;
737 x_msg_data := '';
738
739 --dbms_output.put_line('calling check_user...');
740
741 Check_User_Resp (x_return_status, x_msg_count,
742 x_msg_data, p_user_name,
743 p_responsibility, l_user_id,
744 l_responsibility_id, l_application_id);
745
746
747 if (x_return_status = 'S') then
748 i:= 0;
749 FOR c_rec IN c_types LOOP
750 l_valid := TRUE;
751
752 BEGIN
753 select work_q_enable_profile_option
754 into l_work_q_enable_profile_option
755 from ieu_uwq_sel_enumerators
756 where media_type_id = c_rec.media_type_id ;
757
758
759 EXCEPTION
760 WHEN NO_DATA_FOUND THEN
761 null;
762 END;
763
764 if (fnd_profile.value(l_work_q_enable_profile_option) = 'N')then
765 l_valid := FALSE;
766 end if;
767
768 if (l_valid = true)then
769 i := i+1;
770 begin
771 x_media_types.EXTEND;
772
773 select media_type_name INTO x_media_types(i)
774 from ieu_uwq_media_types_tl
775 where media_type_id = c_rec.MEDIA_TYPE_ID
776 and language = l_language order by media_type_name;
777
778
779 exception
780 when no_data_found then
781 i:= i-1;
782 x_media_types.trim(1);
783 --exit;
784 end;
785 ----DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
786 end if;
787 END LOOP;
788
789 -- user does not have any media queues enabled
790 if x_media_types.COUNT = 0 then
791 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_MEDIAS_ENABLED');
792 FND_MSG_PUB.Add;
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 end if;
795
796
797 v_count := 1;
798 loop
799 if x_media_types.EXISTS(v_count) then
800 --DBMS_OUTPUT.put_line('media names count: -->'|| v_count);
801 begin
802 select count(s.SEL_ENUM_ID) into l_media_count
803 from
804 ieu_uwq_media_types_b b,
805 ieu_uwq_media_types_tl tl,
806 ieu_uwq_sel_enumerators s
807 where
808 b.media_type_id = tl.media_type_id and
809 tl.language = l_language and
810 s.media_type_id = b.media_type_id and
811 tl.media_type_name like x_media_types(v_count);
812
813 --DBMS_OUTPUT.put_line('mediatype name '|| x_media_types(v_count));
814 --DBMS_OUTPUT.put_line('media count in enumerators: -->'|| l_media_count);
815
816 if (l_media_count = 1) then
817 null;
818 elsif (l_media_count = 0) then
819 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_MEDIA');
820 FND_MSG_PUB.Add;
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 else
823 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_MORE_ENTRIES');
824 FND_MSG_PUB.Add;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 end if;
827
828
829 EXCEPTION
830 WHEN NO_DATA_FOUND then
831 null;
832 end;
833 --DBMS_OUTPUT.put_line('media_name(' || v_count || '): '|| x_media_types(v_count));
834 v_count := v_count+1;
835 else
836 exit;
837 end if;
838 end loop;
839
840 end if;
841
842
843 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
844
845 -- Standard call to get message count and if count is 1, get message info.
846 /*FND_MSG_PUB.Count_And_Get(
847 p_count => x_msg_count,
848 p_data => x_msg_data --x_msg_data --l_msg_data
849 );*/
850 x_msg_data := '';
851
852 x_msg_count := fnd_msg_pub.COUNT_MSG();
853
854 FOR i in 1..x_msg_count LOOP
855 l_msg_data := '';
856 l_msg_count := 0;
857 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
858 x_msg_data := x_msg_data || ',' || l_msg_data;
859 END LOOP;
860
861
862 EXCEPTION
863
864 WHEN FND_API.G_EXC_ERROR THEN
865
866 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
867
868
869 x_return_status := FND_API.G_RET_STS_ERROR;
870
871 x_msg_count := fnd_msg_pub.COUNT_MSG();
872
873 FOR i in 1..x_msg_count LOOP
874 l_msg_data := '';
875 l_msg_count := 0;
876 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
877 x_msg_data := x_msg_data || ',' || l_msg_data;
878 END LOOP;
879
880
881 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
882
883 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
884
885
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887
888 x_msg_count := fnd_msg_pub.COUNT_MSG();
889
890 FOR i in 1..x_msg_count LOOP
891 l_msg_data := '';
892 l_msg_count := 0;
893 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
894 x_msg_data := x_msg_data || ',' || l_msg_data;
895 END LOOP;
896
897
898
899 WHEN OTHERS THEN
900 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
901 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
902
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
904
905 x_msg_count := fnd_msg_pub.COUNT_MSG();
906
907 FOR i in 1..x_msg_count LOOP
908 l_msg_data := '';
909 l_msg_count := 0;
910 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
911 x_msg_data := x_msg_data || ',' || l_msg_data;
912 END LOOP;
913
914
915
916 END Determine_Media_Enabled;
917
918
919 --===================================================================
920 -- NAME
921 -- Determine_Valid_Server
922 --
923 -- PURPOSE
924 -- Private api to determine if the user is in a valid server group and the user's
925 -- server group contains servers, which can handle congigured media types.
926 --
927 -- NOTES
928 -- 1. UWQ Login Diagnostics will use this procedure.
929 --
930 --
931 -- HISTORY
932 -- 14-Mar-2002 GPAGADAL Created
933
934 --===================================================================
935
936
937 PROCEDURE Determine_Valid_Server ( x_return_status OUT NOCOPY VARCHAR2,
938 x_msg_count OUT NOCOPY NUMBER,
939 x_msg_data OUT NOCOPY VARCHAR2,
940 p_user_name IN VARCHAR2,
941 p_responsibility IN VARCHAR2,
942 x_server_group OUT NOCOPY VARCHAR2,
943 x_medias OUT NOCOPY IEU_DIAG_STRING_NST
944 )
945 AS
946
947
948 l_user_name FND_USER.USER_NAME%TYPE;
949
950 l_user_id FND_USER.USER_ID%TYPE;
951
952 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
953
954 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
955
956 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
957
958 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
959
960 l_language VARCHAR2(4);
961
962 l_media_type_id IEU_UWQ_SEL_ENUMERATORS.MEDIA_TYPE_ID%TYPE;
963
964 l_valid boolean;
965 l_media_type_name IEU_UWQ_MEDIA_TYPES_TL.MEDIA_TYPE_NAME%TYPE;
966
967 l_msg_count NUMBER(2);
968
969 l_msg_data VARCHAR2(2000);
970
971 l_server_group_id JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE;
972
973 l_media_types IEU_DIAG_STRING_NST;
974
975 l_svr_group_name IEO_SVR_GROUPS.GROUP_NAME%TYPE;
976
977 l_server_type_id IEU_UWQ_SVR_MPS_MMAPS.SVR_TYPE_ID%TYPE;
978 j integer ;
979
980
981 l_sql VARCHAR2(4000);
982
983
984 BEGIN
985
986 fnd_msg_pub.delete_msg();
987 x_return_status := fnd_api.g_ret_sts_success;
988 FND_MSG_PUB.initialize;
989 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
990 j := 0;
991 x_medias := IEU_DIAG_STRING_NST();
992
993 ----DBMS_OUTPUT.put_line('calling check_user...');
994 Check_User_Resp (x_return_status, x_msg_count,
995 x_msg_data, p_user_name,
996 p_responsibility, l_user_id,
997 l_responsibility_id, l_application_id);
998
999 if x_return_status = 'S' then
1000
1001 IEU_Diagnostics_PVT.Determine_Media_Enabled (x_return_status,
1002 x_msg_count,
1003 x_msg_data,
1004 p_user_name,
1005 p_responsibility,
1006 l_media_types);
1007
1008
1009 if x_return_status = 'S' then
1010
1011 begin
1012 l_sql := 'select server_group_id from jtf_rs_resource_extns where user_id = :l_user_id';
1013
1014 EXECUTE IMMEDIATE l_sql
1015 INTO l_server_group_id
1016 USING l_user_id;
1017
1018 ----DBMS_OUTPUT.PUT_LINE('server group id :' || l_server_group_id);
1019 FOR i IN 1..l_media_types.COUNT
1020 LOOP
1021
1022 --DBMS_OUTPUT.put_line('media names: -->'||i ||':' || l_media_types(i));
1023 begin
1024 select mmptab.SVR_TYPE_ID into l_server_type_id from
1025 IEO_SVR_SERVERS svrtab,
1026 IEU_UWQ_SVR_MPS_MMAPS mmptab,
1027 IEU_UWQ_MEDIA_TYPES_B mttab,
1028 IEU_UWQ_MEDIA_TYPES_TL mtltab
1029 where (mtltab.MEDIA_TYPE_NAME = l_media_types(i)) and
1030 (mtltab.LANGUAGE = l_language) and
1031 (mtltab.MEDIA_TYPE_ID = mttab.MEDIA_TYPE_ID) and
1032 (mmptab.MEDIA_TYPE_ID = mttab.MEDIA_TYPE_ID) and
1033 (svrtab.TYPE_ID = mmptab.SVR_TYPE_ID) and
1034 (svrtab.MEMBER_SVR_GROUP_ID = l_server_group_id);
1035
1036
1037
1038
1039 EXCEPTION
1040 WHEN NO_DATA_FOUND then
1041 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_SVRS');
1042 FND_MSG_PUB.Add;
1043 j:=j+1;
1044 x_medias.EXTEND;
1045 x_medias(j) := l_media_types(i);
1046 --DBMS_OUTPUT.put_line('failed medias '||x_medias(j)||':)');
1047
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 -- exit;
1050
1051 end;
1052 end LOOP;
1053
1054 begin
1055 select GROUP_NAME into l_svr_group_name from IEO_SVR_GROUPS where SERVER_GROUP_ID = l_server_group_id;
1056
1057 --DBMS_OUTPUT.put_line('server group name :--->'||l_svr_group_name||':)');
1058 x_server_group := l_svr_group_name;
1059 --DBMS_OUTPUT.put_line('server group name :--->'|| x_server_group||':)');
1060 EXCEPTION
1061 WHEN NO_DATA_FOUND THEN
1062 null;
1063 end;
1064
1065 EXCEPTION
1066 WHEN NO_DATA_FOUND THEN
1067 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_SVR_GROUP');--Server group does not exist
1068 FND_MSG_PUB.Add;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 end;
1071
1072
1073
1074
1075 end if;
1076 end if;
1077 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
1078
1079
1080
1081 -- Standard call to get message count and if count is 1, get message info.
1082 /*FND_MSG_PUB.Count_And_Get(
1083 p_count => x_msg_count,
1084 p_data => l_msg_data
1085 );*/
1086
1087 x_msg_data := '';
1088
1089 x_msg_count := fnd_msg_pub.COUNT_MSG();
1090
1091 FOR i in 1..x_msg_count LOOP
1092 l_msg_data := '';
1093 l_msg_count := 0;
1094 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1095 x_msg_data := x_msg_data || ',' || l_msg_data;
1096 END LOOP;
1097
1098
1099
1100
1101 EXCEPTION
1102
1103 WHEN FND_API.G_EXC_ERROR THEN
1104
1105
1106
1107 x_return_status := FND_API.G_RET_STS_ERROR;
1108
1109 x_msg_data := '';
1110
1111 x_msg_count := fnd_msg_pub.COUNT_MSG();
1112
1113 FOR i in 1..x_msg_count LOOP
1114 l_msg_data := '';
1115 l_msg_count := 0;
1116 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1117 x_msg_data := x_msg_data || ',' || l_msg_data;
1118 END LOOP;
1119
1120
1121
1122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1123
1124
1125
1126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 x_msg_data := '';
1128
1129 x_msg_count := fnd_msg_pub.COUNT_MSG();
1130
1131 FOR i in 1..x_msg_count LOOP
1132 l_msg_data := '';
1133 l_msg_count := 0;
1134 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1135 x_msg_data := x_msg_data || ',' || l_msg_data;
1136 END LOOP;
1137
1138
1139
1140
1141 WHEN OTHERS THEN
1142 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1143
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1145
1146 x_msg_data := '';
1147
1148 x_msg_count := fnd_msg_pub.COUNT_MSG();
1149
1150 FOR i in 1..x_msg_count LOOP
1151 l_msg_data := '';
1152 l_msg_count := 0;
1153 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1154 x_msg_data := x_msg_data || ',' || l_msg_data;
1155 END LOOP;
1156
1157
1158
1159
1160 END Determine_Valid_Server;
1161
1162
1163 --===================================================================
1164 -- NAME
1165 -- Get_Valid_Nodes
1166 --
1167 -- PURPOSE
1168 -- Private api to get the list of all valid nodes
1169 --
1170 -- NOTES
1171 -- 1. UWQ Login Diagnostics will use this procedure.
1172 --
1173 --
1174 -- HISTORY
1175 -- 01-Apr-2002 GPAGADAL Created
1176
1177 --===================================================================
1178 PROCEDURE Get_Valid_Nodes (x_return_status OUT NOCOPY VARCHAR2,
1179 x_msg_count OUT NOCOPY NUMBER,
1180 x_msg_data OUT NOCOPY VARCHAR2,
1181 p_user_name IN VARCHAR2,
1182 p_responsibility IN VARCHAR2,
1183 x_valid_nodes OUT NOCOPY IEU_DIAG_VNODE_NST)
1184 AS
1185
1186 l_user_name FND_USER.USER_NAME%TYPE;
1187
1188 l_user_id FND_USER.USER_ID%TYPE;
1189
1190 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
1191
1192 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
1193
1194 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
1195
1196 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
1197
1198 l_msg_count NUMBER(2);
1199
1200 l_msg_data VARCHAR2(2000);
1201
1202 l_language VARCHAR2(4);
1203
1204 l_valid_nodes IEU_DIAG_VNODE_NST;
1205
1206 l_sql VARCHAR2(4000);
1207
1208
1209
1210
1211 CURSOR c_enum IS
1212 SELECT
1213 distinct l.meaning node_name,
1214 e.SEL_ENUM_ID enum_id
1215 FROM
1216 IEU_UWQ_SEL_ENUMERATORS e,
1217 fnd_lookup_values l
1218 WHERE EXISTS (select 'x' from FND_PROFILE_OPTIONS b
1219 where upper(b.PROFILE_OPTION_NAME) = upper(e.work_q_enable_profile_option))
1220 AND ((e.NOT_VALID_FLAG is NULL) OR (e.NOT_VALID_FLAG = 'N')) AND
1221 (nvl(fnd_profile.value(e.work_q_enable_profile_option),'Y') = 'Y')
1222 AND l.language = l_language
1223 AND l.lookup_code = e.work_q_label_lu_code
1224 AND l.lookup_type = e.work_q_label_lu_type order by l.meaning;
1225
1226 i integer ;
1227
1228 BEGIN
1229
1230 fnd_msg_pub.delete_msg();
1231 x_return_status := fnd_api.g_ret_sts_success;
1232 FND_MSG_PUB.initialize;
1233 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1234 x_valid_nodes := IEU_DIAG_VNODE_NST();
1235 i := 0;
1236 Check_User_Resp (x_return_status, x_msg_count, x_msg_data,
1237 p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
1238
1239 if (x_return_status = 'S') then
1240 FOR cur_rec IN c_enum
1241 LOOP
1242 --dbms_output.put_line('in the loop of enum');
1243 i := i+1;
1244 x_valid_nodes.EXTEND(1);
1245
1246 -- dbms_output.put_line('extended');
1247
1248 x_valid_nodes(x_valid_nodes.last) := IEU_DIAG_VNODE_OBJ(cur_rec.enum_id, cur_rec.node_name);
1249
1250 -- dbms_output.put_line('id-->'||i||'....)-'||cur_rec.enum_id);
1251 --dbms_output.put_line('name-->'||cur_rec.node_name);
1252
1253 end LOOP;
1254 end if;
1255
1256 --dbms_output.PUT_LINE('initialized');
1257 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
1258
1259
1260 -- Standard call to get message count and if count is 1, get message info.
1261 /* FND_MSG_PUB.Count_And_Get(
1262 p_count => x_msg_count,
1263 p_data => l_msg_data
1264 );*/
1265
1266 x_msg_data := '';
1267
1268 x_msg_count := fnd_msg_pub.COUNT_MSG();
1269
1270 FOR i in 1..x_msg_count LOOP
1271 l_msg_data := '';
1272 l_msg_count := 0;
1273 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1274 x_msg_data := x_msg_data || ',' || l_msg_data;
1275 END LOOP;
1276
1277
1278
1279
1280 EXCEPTION
1281
1282 WHEN FND_API.G_EXC_ERROR THEN
1283
1284 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1285
1286
1287 x_return_status := FND_API.G_RET_STS_ERROR;
1288 x_msg_data := '';
1289
1290 x_msg_count := fnd_msg_pub.COUNT_MSG();
1291
1292 FOR i in 1..x_msg_count LOOP
1293 l_msg_data := '';
1294 l_msg_count := 0;
1295 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1296 x_msg_data := x_msg_data || ',' || l_msg_data;
1297 END LOOP;
1298
1299
1300 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1301 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1302 x_msg_data := x_msg_data || ',' || l_msg_data;
1303 END LOOP;*/
1304
1305
1306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1307
1308 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1309
1310
1311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1312 x_msg_data := '';
1313
1314 x_msg_count := fnd_msg_pub.COUNT_MSG();
1315
1316 FOR i in 1..x_msg_count LOOP
1317 l_msg_data := '';
1318 l_msg_count := 0;
1319 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1320 x_msg_data := x_msg_data || ',' || l_msg_data;
1321 END LOOP;
1322
1323 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1324 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1325 x_msg_data := x_msg_data || ',' || l_msg_data;
1326 END LOOP;*/
1327
1328 WHEN OTHERS THEN
1329 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1330 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1331
1332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1333
1334 x_msg_data := '';
1335
1336 x_msg_count := fnd_msg_pub.COUNT_MSG();
1337
1338 FOR i in 1..x_msg_count LOOP
1339 l_msg_data := '';
1340 l_msg_count := 0;
1341 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1342 x_msg_data := x_msg_data || ',' || l_msg_data;
1343 END LOOP;
1344
1345
1346 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1347 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1348 x_msg_data := x_msg_data || ',' || l_msg_data;
1349 END LOOP;*/
1350
1351 END Get_Valid_Nodes;
1352
1353 --===================================================================
1354 -- NAME
1355 -- Check_Profile_Options
1356 --
1357 -- PURPOSE
1358 -- Private api to check profile options
1359 --
1360 -- NOTES
1361 -- 1. UWQ Login Diagnostics will use this procedure.
1362 --
1363 --
1364 -- HISTORY
1365 -- 04-Apr-2002 GPAGADAL Created
1366 --===================================================================
1367 PROCEDURE Check_Profile_Options( x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2,
1370 p_user_name IN VARCHAR2,
1371 p_responsibility IN VARCHAR2,
1372 x_invalid_profile_options OUT NOCOPY IEU_DIAG_VNODE_NST
1373 )
1374 AS
1375
1376 l_user_name FND_USER.USER_NAME%TYPE;
1377
1378 l_user_id FND_USER.USER_ID%TYPE;
1379
1380 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
1381
1382 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
1383
1384 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
1385
1386 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
1387
1388 l_msg_count NUMBER(2);
1389
1390 l_msg_data VARCHAR2(2000);
1391
1392 l_language VARCHAR2(4);
1393
1394 l_valid_nodes IEU_DIAG_VNODE_NST;
1395
1396 l_profle_name IEU_UWQ_SEL_ENUMERATORS.WORK_Q_ENABLE_PROFILE_OPTION%TYPE;
1397
1398 v_count BINARY_INTEGER ;
1399
1400 --x_valid_nodes IEU_DIAG_VNODE_NST;
1401
1402 i integer ;
1403
1404 l_sql VARCHAR2(4000);
1405
1406
1407
1408 BEGIN
1409
1410 fnd_msg_pub.delete_msg();
1411 x_return_status := fnd_api.g_ret_sts_success;
1412 FND_MSG_PUB.initialize;
1413 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1414 --x_valid_nodes := IEU_DIAG_VNODE_NST();
1415 x_invalid_profile_options := IEU_DIAG_VNODE_NST();
1416 l_profle_name := null;
1417 i := 0;
1418
1419 ----dbms_output.put_line('check user resp...');
1420 Check_User_Resp(x_return_status, x_msg_count, x_msg_data,
1421 p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
1422
1423 if (x_return_status = 'S') then
1424 --dbms_output.put_line('check user resp...success');
1425
1426 Get_Valid_Nodes ( x_return_status,
1427 x_msg_count,
1428 x_msg_data,
1429 p_user_name,
1430 p_responsibility,
1431 l_valid_nodes);
1432 v_count := 1;
1433 loop
1434 if l_valid_nodes.EXISTS(v_count) then
1435 --dbms_output.put_line('count: -->'|| v_count);
1436 --dbms_output.put_line('id-->'||l_valid_nodes(v_count).enum_id);
1437 BEGIN
1438 select a.WORK_Q_ENABLE_PROFILE_OPTION into l_profle_name
1439 from ieu_uwq_sel_enumerators a,
1440 ieu_uwq_sel_enumerators b
1441 where
1442 a.sel_enum_id = b.sel_enum_id and
1443 a.sel_enum_id = l_valid_nodes(v_count).enum_id and
1444 (upper(a.WORK_Q_ENABLE_PROFILE_OPTION) <> b.WORK_Q_ENABLE_PROFILE_OPTION
1445 or (a.WORK_Q_ENABLE_PROFILE_OPTION) not like ('IEU_QEN_%'));
1446
1447
1448
1449 if not(l_profle_name is null) then
1450 x_return_status := FND_API.G_RET_STS_ERROR;
1451 i:= i+1;
1452 x_invalid_profile_options.EXTEND(1);
1453 --dbms_output.put_line('invalid profile extended');
1454 x_invalid_profile_options(x_invalid_profile_options.LAST) :=
1455 IEU_DIAG_VNODE_OBJ(l_valid_nodes(v_count).enum_id, l_profle_name);
1456 --dbms_output.put_line('invalid profile added');
1457
1458
1459 else
1460 null;
1461 end if;
1462
1463 v_count := v_count+1;
1464
1465 EXCEPTION
1466
1467 WHEN NO_DATA_FOUND then
1468 v_count := v_count+1;
1469 END;
1470 else
1471 exit;
1472 end if;
1473 end loop;
1474
1475 end if;
1476
1477 if (x_invalid_profile_options.COUNT > 0) then
1478 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_PROFILE_INVALID');
1479 FND_MSG_PUB.Add;
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481
1482 end if;
1483
1484
1485 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
1486
1487
1488 -- Standard call to get message count and if count is 1, get message info.
1489 /*FND_MSG_PUB.Count_And_Get(
1490 p_count => x_msg_count,
1491 p_data => l_msg_data
1492 );
1493
1494 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1495 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1496 x_msg_data := x_msg_data || ',' || l_msg_data;
1497 END LOOP;*/
1498 x_msg_data := '';
1499
1500 x_msg_count := fnd_msg_pub.COUNT_MSG();
1501
1502 FOR i in 1..x_msg_count LOOP
1503 l_msg_data := '';
1504 l_msg_count := 0;
1505 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1506 x_msg_data := x_msg_data || ',' || l_msg_data;
1507 END LOOP;
1508
1509
1510
1511 EXCEPTION
1512
1513 WHEN FND_API.G_EXC_ERROR THEN
1514
1515 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1516
1517
1518 x_return_status := FND_API.G_RET_STS_ERROR;
1519 x_msg_data := '';
1520
1521 x_msg_count := fnd_msg_pub.COUNT_MSG();
1522
1523 FOR i in 1..x_msg_count LOOP
1524 l_msg_data := '';
1525 l_msg_count := 0;
1526 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1527 x_msg_data := x_msg_data || ',' || l_msg_data;
1528 END LOOP;
1529
1530 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1531 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1532 x_msg_data := x_msg_data || ',' || l_msg_data;
1533 END LOOP;*/
1534
1535
1536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1537
1538 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1539
1540
1541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1542 x_msg_data := '';
1543
1544 x_msg_count := fnd_msg_pub.COUNT_MSG();
1545
1546 FOR i in 1..x_msg_count LOOP
1547 l_msg_data := '';
1548 l_msg_count := 0;
1549 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1550 x_msg_data := x_msg_data || ',' || l_msg_data;
1551 END LOOP;
1552
1553 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1554 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1555 x_msg_data := x_msg_data || ',' || l_msg_data;
1556 END LOOP;*/
1557
1558 WHEN OTHERS THEN
1559 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
1560 --dbms_output.PUT_LINE('Error : '||sqlerrm);
1561
1562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1563
1564 x_msg_data := '';
1565
1566 x_msg_count := fnd_msg_pub.COUNT_MSG();
1567
1568 FOR i in 1..x_msg_count LOOP
1569 l_msg_data := '';
1570 l_msg_count := 0;
1571 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1572 x_msg_data := x_msg_data || ',' || l_msg_data;
1573 END LOOP;
1574
1575
1576 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
1577 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
1578 x_msg_data := x_msg_data || ',' || l_msg_data;
1579 END LOOP;*/
1580
1581
1582 END Check_Profile_Options;
1583
1584 --===================================================================
1585 -- NAME
1586 -- Check_Node_Enumeration
1587 --
1588 -- PURPOSE
1589 -- Private api for node enumeration
1590 --
1591 -- NOTES
1592 -- 1. UWQ Login Diagnostics will use this procedure.
1593 --
1594 --
1595 -- HISTORY
1596 -- 28-Mar-2002 GPAGADAL Created
1597 -- 9-Jan-2003 GPAGADAL Updated--Display status/time taken to enumerate
1598 -- each node
1599 -- 30-Apr-2003 GPAGADAl updated- display total time.
1600
1601
1602 --===================================================================
1603 PROCEDURE Check_Node_Enumeration ( x_return_status OUT NOCOPY VARCHAR2,
1604 x_msg_count OUT NOCOPY NUMBER,
1605 x_msg_data OUT NOCOPY VARCHAR2,
1606 p_user_name IN VARCHAR2,
1607 p_responsibility IN VARCHAR2,
1608 x_dupli_proc OUT NOCOPY IEU_DIAG_ENUM_NST,
1609 x_invalid_pkg OUT NOCOPY IEU_DIAG_ENUM_NST,
1610 x_invalid_proc OUT NOCOPY IEU_DIAG_ENUM_ERR_NST,
1611 x_enum_time OUT NOCOPY IEU_DIAG_ENUM_TIME_NST,
1612 x_user_ver_time OUT NOCOPY NUMBER,
1613 x_etime_grand_total OUT NOCOPY NUMBER
1614
1615 )
1616 AS
1617
1618 l_user_name FND_USER.USER_NAME%TYPE;
1619
1620 l_user_id FND_USER.USER_ID%TYPE;
1621
1622 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
1623
1624 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
1625
1626 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
1627
1628 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
1629
1630 l_language VARCHAR2(4);
1631
1632 l_msg_count NUMBER(2);
1633
1634 l_msg_data VARCHAR2(2000);
1635
1636 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
1637
1638 l_wb_style VARCHAR2(2);
1639
1640 l_media_eligible VARCHAR2(5) ;
1641
1642 l_source_lang varchar2(4);
1643
1644 l_savepoint_valid NUMBER(1);
1645
1646 l_media_count PLS_INTEGER;
1647
1648 l_node_label VARCHAR2(80);
1649
1650 l_count NUMBER(10) ;
1651
1652 l_temp_eproc IEU_UWQ_SEL_ENUMERATORS.ENUM_PROC%TYPE;
1653
1654 l_temp_pkg_name IEU_UWQ_SEL_ENUMERATORS.ENUM_PROC%TYPE;
1655
1656 l_media_type_name IEU_UWQ_MEDIA_TYPES_TL.MEDIA_TYPE_NAME%TYPE;
1657
1658 l_temp_count NUMBER(10) ;
1659
1660 i integer ;
1661
1662 j integer ;
1663
1664
1665
1666 l_svr_group_name IEO_SVR_GROUPS.GROUP_NAME%TYPE;
1667
1668 l_medias IEU_DIAG_STRING_NST;
1669
1670 l_temp_msg_data VARCHAR2(5000);
1671
1672 l_temp_msg_count NUMBER(2);
1673
1674 l_temp_return_status VARCHAR2(1);
1675
1676 v_count BINARY_INTEGER ;
1677 temp_not_eligible_flag boolean;
1678
1679
1680 l_node_id number(10);
1681 l_node_pid number(10);
1682 l_node_weight ieu_uwq_sel_rt_nodes.node_weight%type;
1683 l_vnode_label varchar2(512);
1684
1685 t1 NUMBER; -- start time
1686 t2 NUMBER; -- end time
1687 l_time_spent NUMBER; -- time elapsed
1688 enum_status VARCHAR2(1); -- Succeeded or Failed
1689
1690 l_total_time NUMBER; -- total time
1691 l_user_ver_time NUMBER; -- time taken to verify user name and responsibility
1692
1693 temp1 NUMBER; -- start time
1694 temp2 NUMBER; -- end time
1695
1696 l_sql VARCHAR2(4000);
1697
1698
1699 cursor c_eproc is
1700 SELECT
1701 e.sel_enum_id,
1702 e.enum_proc,
1703 e.work_q_register_type,
1704 e.media_type_id,
1705 e.application_id,
1706 e.work_q_label_lu_type,
1707 e.work_q_label_lu_code
1708 FROM
1709 IEU_UWQ_SEL_ENUMERATORS e
1710 WHERE EXISTS (select 'x' from FND_PROFILE_OPTIONS b
1711 where upper(b.PROFILE_OPTION_NAME) = upper(e.work_q_enable_profile_option))
1712 AND ((e.NOT_VALID_FLAG is NULL) OR (e.NOT_VALID_FLAG = 'N')) AND
1713 (nvl(fnd_profile.value(e.work_q_enable_profile_option),'Y') = 'Y')
1714 AND (e.work_q_register_type <> 'W' or e.work_q_register_type is null)
1715 order by e.sel_enum_id;
1716
1717
1718 cursor c_dproc (enum_id NUMBER, app_id NUMBER, l_type VARCHAR2, l_code VARCHAR2)is
1719 select distinct v.meaning, s.sel_enum_id, s.enum_proc, tl.application_name
1720 from ieu_uwq_sel_enumerators s,
1721 fnd_application_tl tl,
1722 fnd_lookup_values v
1723 where s.sel_enum_id = enum_id
1724 and tl.application_id = app_id
1725 and v.lookup_type = l_type
1726 and v.lookup_code = l_code
1727 and tl.language = l_language
1728 and v.language = l_language;
1729
1730
1731 BEGIN
1732
1733 fnd_msg_pub.delete_msg();
1734 x_return_status := fnd_api.g_ret_sts_success;
1735 FND_MSG_PUB.initialize;
1736 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
1737 l_source_lang :=FND_GLOBAL.BASE_LANGUAGE;
1738 x_dupli_proc := IEU_DIAG_ENUM_NST();
1739 x_invalid_pkg := IEU_DIAG_ENUM_NST();
1740 x_invalid_proc := IEU_DIAG_ENUM_ERR_NST();
1741 x_enum_time := IEU_DIAG_ENUM_TIME_NST();
1742 x_user_ver_time := 0;
1743 x_etime_grand_total := 0;
1744 x_msg_count := 0;
1745 x_msg_data := '';
1746
1747 l_medias := IEU_DIAG_STRING_NST();
1748 l_total_time := 0;
1749
1750 t1 := DBMS_UTILITY.GET_TIME;
1751
1752 Check_User_Resp(x_return_status, x_msg_count, x_msg_data,
1753 p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
1754
1755 t2 := DBMS_UTILITY.GET_TIME;
1756
1757 x_user_ver_time := (t2 -t1)*10;
1758 --DBMS_OUTPUT.put_line('verified userid and resp values, time taken - '|| x_user_ver_time);
1759
1760
1761 if (x_return_status = 'S') then
1762 /* Determine_Valid_Server ( l_temp_return_status, l_temp_msg_count,
1763 l_temp_msg_data, p_user_name,
1764 p_responsibility, l_svr_group_name,
1765 l_medias);*/
1766
1767 l_sql := 'select resource_id from jtf_rs_resource_extns where user_id = :l_user_id';
1768
1769 EXECUTE IMMEDIATE l_sql
1770 INTO l_resource_id
1771 USING l_user_id;
1772
1773
1774 UPDATE IEU_UWQ_SEL_RT_NODES
1775 SET not_valid = 'Y'
1776 WHERE resource_id = l_resource_id;
1777
1778 UPDATE IEU_UWQ_RTNODE_BIND_VALS
1779 SET not_valid_flag = 'Y'
1780 WHERE resource_id = l_resource_id;
1781
1782
1783 l_wb_style := ieu_pvt.determine_wb_style( l_resource_id );
1784
1785
1786 FOR c_rec in c_eproc LOOP
1787
1788 BEGIN
1789
1790 t1 := DBMS_UTILITY.GET_TIME;
1791
1792 --get the details of a node
1793 /* SELECT lu.MEANING into l_vnode_label
1794 from IEU_UWQ_SEL_ENUMERATORS u, FND_LOOKUP_VALUES lu
1795 where
1796 u.WORK_Q_LABEL_LU_TYPE = lu.LOOKUP_TYPE
1797 and u.WORK_Q_LABEL_LU_CODE = lu.LOOKUP_CODE
1798 and u.SEL_ENUM_ID =c_rec.SEL_ENUM_ID;*/
1799
1800
1801 select distinct v.meaning into l_vnode_label
1802 from ieu_uwq_sel_enumerators s,
1803 fnd_application_tl tl,
1804 fnd_lookup_values v
1805 where s.sel_enum_id = c_rec.SEL_ENUM_ID
1806 and tl.application_id = s.APPLICATION_ID
1807 and v.lookup_type = s.WORK_Q_LABEL_LU_TYPE
1808 and v.lookup_code = s.WORK_Q_LABEL_LU_CODE
1809 and tl.language = v.language
1810 and v.language= l_language;
1811
1812
1813
1814
1815 --starting time
1816 enum_status := 'S';
1817
1818
1819 t1 := DBMS_UTILITY.GET_TIME;
1820 --DBMS_OUTPUT.put_line('after select enum id==='||c_rec.SEL_ENUM_ID);
1821 --DBMS_OUTPUT.put_line('start time== '||t1);
1822
1823
1824
1825 -- first check whether the enum_proc is unique or not.
1826 /*if(temp_not_eligible_flag = false) then
1827 null;
1828 end if;*/
1829
1830 begin
1831
1832 select count(sel_enum_id) into l_count from ieu_uwq_sel_enumerators
1833 where enum_proc = c_rec.enum_proc;
1834
1835
1836 --DBMS_OUTPUT.put_line('count of duplicate enum_proc-->'||l_count);
1837
1838 if not(l_count is null) and (l_count > 1) then
1839 x_return_status := FND_API.G_RET_STS_ERROR;
1840 --dbms_output.put_line('there are duplicate records with the same enum_proc');
1841
1842 FOR c_temp in c_dproc(c_rec.sel_enum_id, c_rec.application_id, c_rec.work_q_label_lu_type, c_rec.work_q_label_lu_code) LOOP
1843 --dbms_output.put_line('in the for loop of duplicate row cursor');
1844 i := i+1;
1845 x_dupli_proc.EXTEND();
1846 --dbms_output.put_line('extended');
1847 x_dupli_proc(x_dupli_proc.LAST) := IEU_DIAG_ENUM_OBJ(c_temp.application_name,
1848 c_temp.meaning, c_temp.enum_proc);
1849 --dbms_output.put_line('added');
1850 END LOOP;
1851
1852 end if;
1853
1854 EXCEPTION
1855 WHEN NO_DATA_FOUND THEN
1856 null;
1857
1858
1859 end;
1860 -- next check the procedure
1861
1862 begin
1863
1864 l_temp_eproc := c_rec.enum_proc;
1865
1866 if not(l_temp_eproc is null) then
1867
1868 l_temp_pkg_name := substr(l_temp_eproc,1, ( instr(l_temp_eproc,'.',1,1)-1));
1869 begin
1870
1871 select count(*) into l_temp_count from all_objects where owner = 'APPS' and object_type in('PACKAGE', 'PACKAGE BODY') and status='VALID'and object_name = l_temp_pkg_name;
1872
1873
1874
1875 if not(l_temp_count is null) and (l_temp_count <= 0) then
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877
1878 FOR c_temp2 in c_dproc(c_rec.sel_enum_id, c_rec.application_id, c_rec.work_q_label_lu_type, c_rec.work_q_label_lu_code)
1879 LOOP
1880 --dbms_output.put_line('in the for loop');
1881 j := j+1;
1882 x_invalid_pkg.EXTEND();
1883 --dbms_output.put_line('extended');
1884 enum_status := 'F';
1885 x_invalid_pkg(x_invalid_pkg.LAST) := IEU_DIAG_ENUM_OBJ(c_temp2.application_name,
1886 c_temp2.meaning, c_temp2.enum_proc);
1887 --dbms_output.put_line('added');
1888 END LOOP;
1889
1890 end if;
1891
1892 EXCEPTION
1893
1894 WHEN NO_DATA_FOUND THEN
1895 null;
1896
1897
1898 end;
1899
1900
1901 end if;
1902 end;
1903
1904 l_media_eligible := null;
1905
1906
1907 if ( ( (l_wb_style = 'F') or (l_wb_style = 'SF') )
1908 and (c_rec.work_q_register_type = 'M') ) -- Full/Simple Forced Blending
1909 then
1910
1911 IEU_DEFAULT_MEDIA_ENUMS_PVT.create_blended_node( l_resource_id, l_language, l_source_lang );
1912
1913 else
1914
1915 if ( ( (l_wb_style = 'O') or (l_wb_style = 'SO') )
1916 and (c_rec.work_q_register_type = 'M') ) -- Full/Simple Optional Blending
1917 then
1918
1919 IEU_DEFAULT_MEDIA_ENUMS_PVT.create_blended_node( l_resource_id, l_language, l_source_lang );
1920
1921 end if;
1922
1923 -- Here we are excluding Inbound and Acquired email as these will not have servers now.
1924 IF ( not( (c_rec.media_type_id = 10001) or (c_rec.media_type_id = 10008) ))
1925 THEN
1926
1927 IF ((c_rec.work_q_register_type = 'M') and (c_rec.media_type_id is not NULL))
1928 THEN
1929
1930 IF (IEU_PVT.IS_MEDIA_TYPE_ELIGIBLE
1931 (l_resource_id ,c_rec.media_type_id) = FALSE)
1932 THEN
1933 l_media_eligible := 'FALSE';
1934 ELSE
1935 l_media_eligible := 'TRUE';
1936 END IF;
1937
1938 END IF;
1939 END IF;
1940
1941 --dbms_output.put_line('l_media_eligible : '||l_media_eligible||' enum proc : '||c_rec.enum_proc);
1942 IF ( (l_media_eligible is null) or (l_media_eligible = 'TRUE') )
1943 THEN
1944
1945 BEGIN
1946
1947 EXECUTE IMMEDIATE
1948 'begin ' || c_rec.ENUM_PROC ||
1949 '( ' ||
1950 'p_resource_id => :1, ' ||
1951 'p_language => :2, ' ||
1952 'p_source_lang => :3, ' ||
1953 'p_sel_enum_id => :4 ' ||
1954 '); end;'
1955 USING
1956 IN l_resource_id,
1957 IN l_language,
1958 IN l_source_lang,
1959 IN c_rec.SEL_ENUM_ID;
1960
1961 --dbms_output.put_line('l_media_eligible : '||l_media_eligible||' enum proc : '||c_rec.enum_proc);
1962
1963 SAVEPOINT last_enum_success;
1964
1965 -- if we got here, the savepoint has been executed
1966 l_savepoint_valid := 1;
1967
1968 EXCEPTION
1969 WHEN others then
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971
1972 --dbms_output.put_line('sqlerr : ' ||sqlerrm || ' end err msg');
1973 FOR c_temp3 in c_dproc(c_rec.sel_enum_id, c_rec.application_id, c_rec.work_q_label_lu_type, c_rec.work_q_label_lu_code)
1974 LOOP
1975 --dbms_output.put_line('in the for loop');
1976 enum_status := 'F';
1977
1978 x_invalid_proc.EXTEND();
1979 --dbms_output.put_line('extended');
1980 x_invalid_proc(x_invalid_proc.LAST) := IEU_DIAG_ENUM_ERR_OBJ(c_temp3.application_name,
1981 c_temp3.meaning, c_temp3.enum_proc, SQLERRM);
1982 --dbms_output.put_line('added in the x_invalid_proc');
1983 END LOOP;
1984
1985
1986 END;
1987
1988
1989 END IF;
1990
1991 end if;
1992
1993 EXCEPTION
1994 WHEN OTHERS THEN
1995 -- Adding this condition will prevent an error if the exception
1996 -- was caused by the first record
1997
1998 if (l_savepoint_valid = 1) then
1999 ROLLBACK TO last_enum_success;
2000 end if;
2001 END;
2002
2003 --end time
2004
2005 t2 := DBMS_UTILITY.GET_TIME;
2006 l_time_spent := (t2 - t1)*10;
2007
2008 --DBMS_OUTPUT.put_line('node - '||l_time_spent);
2009
2010 x_enum_time.EXTEND();
2011
2012 /*(
2013 vnode_label VARCHAR2(512),
2014 status VARCHAR2(512),
2015 time_taken NUMBER(22)
2016
2017 */
2018 x_enum_time(x_enum_time.LAST) := IEU_DIAG_ENUM_TIME_OBJ(l_vnode_label,
2019 enum_status, l_time_spent);
2020
2021 l_total_time := l_total_time + l_time_spent;
2022 --DBMS_OUTPUT.PUT_LINE('Enumeration grand total time - ' || l_total_time);
2023
2024 END LOOP;
2025
2026 --
2027 temp1 := DBMS_UTILITY.GET_TIME;
2028
2029 begin
2030 select
2031 rownum
2032 into
2033 l_media_count
2034 from
2035 IEU_UWQ_SEL_RT_NODES
2036 where
2037 (resource_id = l_resource_id) and
2038 (not_valid = 'N') and
2039 (media_type_id IS NOT NULL) and
2040 (rownum = 1);
2041 exception
2042 when others then
2043 l_media_count := 0;
2044 end;
2045
2046
2047 if (l_media_count >= 1)
2048 then
2049
2050 Select
2051 meaning
2052 into
2053 l_node_label
2054 from
2055 fnd_lookup_values_vl
2056 where
2057 (lookup_type = 'IEU_NODE_LABELS') and
2058 (view_application_id = 696) and
2059 (lookup_code = 'IEU_MEDIA_LBL');
2060
2061 IEU_UWQ_SEL_RT_NODES_PKG.LOAD_ROW (
2062 X_RESOURCE_ID => l_resource_id,
2063 X_SEL_ENUM_ID => 0,
2064 X_NODE_ID => IEU_CONSTS_PUB.G_SNID_MEDIA,
2065 X_NODE_TYPE => 0,
2066 X_NODE_PID => 0,
2067 X_NODE_WEIGHT => nvl(IEU_UWQ_UTIL_PUB.to_number_noerr(fnd_profile.value('IEU_QOR_MEDIA')) , IEU_CONSTS_PUB.G_SNID_MEDIA),
2068 X_NODE_DEPTH => 1,
2069 X_SEL_ENUM_PID => 0,
2070 X_MEDIA_TYPE_ID => NULL,
2071 X_COUNT => 0,
2072 X_DATA_SOURCE => 'IEU_UWQ_MEDIA_DS',
2073 X_VIEW_NAME => 'IEU_UWQ_MEDIA_V',
2074 X_WHERE_CLAUSE => '',
2075 X_HIDE_IF_EMPTY => NULL,
2076 X_NOT_VALID => 'N',
2077 X_NODE_LABEL => l_node_label,
2078 X_REFRESH_VIEW_NAME => 'IEU_UWQ_MEDIA_V',
2079 X_RES_CAT_ENUM_FLAG => NULL,
2080 X_REFRESH_VIEW_SUM_COL => 'QUEUE_COUNT'
2081 );
2082
2083 end if;
2084
2085
2086 COMMIT;
2087
2088
2089 end if;
2090
2091 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
2092
2093
2094 -- Standard call to get message count and if count is 1, get message info.
2095 /*FND_MSG_PUB.Count_And_Get(
2096 p_count => x_msg_count,
2097 p_data => l_msg_data
2098 );
2099
2100 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2101 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2102 x_msg_data := x_msg_data || ',' || l_msg_data;
2103 END LOOP;*/
2104 -- x_msg_data := '';
2105
2106 x_msg_count := fnd_msg_pub.COUNT_MSG();
2107
2108 FOR i in 1..x_msg_count LOOP
2109 l_msg_data := '';
2110 l_msg_count := 0;
2111 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2112 x_msg_data := x_msg_data || ',' || l_msg_data;
2113 END LOOP;
2114
2115 temp2 := DBMS_UTILITY.GET_TIME;
2116
2117 x_etime_grand_total := l_total_time ;
2118
2119 --DBMS_OUTPUT.put_line('final total time ='|| x_etime_grand_total);
2120
2121
2122
2123 EXCEPTION
2124
2125 WHEN FND_API.G_EXC_ERROR THEN
2126
2127 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2128
2129
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131 -- x_msg_data := '';
2132
2133 x_msg_count := fnd_msg_pub.COUNT_MSG();
2134
2135 FOR i in 1..x_msg_count LOOP
2136 l_msg_data := '';
2137 l_msg_count := 0;
2138 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2139 x_msg_data := x_msg_data || ',' || l_msg_data;
2140 END LOOP;
2141
2142
2143 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2144 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2145 x_msg_data := x_msg_data || ',' || l_msg_data;
2146 END LOOP;*/
2147
2148
2149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2150
2151 -- dbms_output.PUT_LINE('Error : '||sqlerrm);
2152
2153
2154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2155 -- x_msg_data := '';
2156
2157 x_msg_count := fnd_msg_pub.COUNT_MSG();
2158
2159 FOR i in 1..x_msg_count LOOP
2160 l_msg_data := '';
2161 l_msg_count := 0;
2162 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2163 x_msg_data := x_msg_data || ',' || l_msg_data;
2164 END LOOP;
2165
2166
2167 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2168 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2169 x_msg_data := x_msg_data || ',' || l_msg_data;
2170 END LOOP;*/
2171
2172 WHEN OTHERS THEN
2173 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
2174 -- dbms_output.PUT_LINE('Error : '||sqlerrm);
2175
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2177
2178 -- x_msg_data := '';
2179
2180 x_msg_count := fnd_msg_pub.COUNT_MSG();
2181
2182 FOR i in 1..x_msg_count LOOP
2183 l_msg_data := '';
2184 l_msg_count := 0;
2185 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2186 x_msg_data := x_msg_data || ',' || l_msg_data;
2187 END LOOP;
2188
2189
2190 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2191 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2192 x_msg_data := x_msg_data || ',' || l_msg_data;
2193 END LOOP;*/
2194
2195
2196
2197 END Check_Node_Enumeration;
2198
2199
2200
2201 --===================================================================
2202 -- NAME
2203 -- Get_Valid_RT_Nodes
2204 --
2205 -- PURPOSE
2206 -- Private api to get the list of all valid rt nodes
2207 --
2208 -- NOTES
2209 -- 1. UWQ Login Diagnostics will use this procedure.
2210 --
2211 --
2212 -- HISTORY
2213 -- 01-Apr-2002 GPAGADAL Created
2214
2215 --===================================================================
2216 PROCEDURE Get_Valid_RT_Nodes( x_return_status OUT NOCOPY VARCHAR2,
2217 x_msg_count OUT NOCOPY NUMBER,
2218 x_msg_data OUT NOCOPY VARCHAR2,
2219 p_user_name IN VARCHAR2,
2220 p_responsibility IN VARCHAR2,
2221 x_valid_nodes OUT NOCOPY IEU_DIAG_NODE_NST
2222 )
2223 AS
2224 l_user_name FND_USER.USER_NAME%TYPE;
2225
2226 l_user_id FND_USER.USER_ID%TYPE;
2227
2228 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
2229
2230 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
2231
2232 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
2233
2234 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
2235
2236 l_msg_count NUMBER(2);
2237
2238 l_msg_data VARCHAR2(2000);
2239
2240 l_language VARCHAR2(4);
2241
2242 l_valid_nodes IEU_DIAG_NODE_NST;
2243
2244 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
2245
2246 CURSOR c_node IS
2247 SELECT
2248 node_id, node_label, node_pid, node_weight
2249 FROM ieu_uwq_sel_rt_nodes
2250 WHERE
2251 (resource_id = l_resource_id) and
2252 ((not_valid is null) or (not_valid <> 'Y'))order by node_pid, node_weight;
2253
2254
2255 i integer ;
2256 l_sql VARCHAR2(4000);
2257
2258
2259
2260 BEGIN
2261 fnd_msg_pub.delete_msg();
2262 x_return_status := fnd_api.g_ret_sts_success;
2263 FND_MSG_PUB.initialize;
2264 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2265 x_valid_nodes := IEU_DIAG_NODE_NST();
2266
2267 Check_User_Resp (x_return_status, x_msg_count, x_msg_data,
2268 p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
2269 if (x_return_status = 'S') then
2270 BEGIN
2271 l_sql := ' select resource_id
2272 from jtf_rs_resource_extns
2273 where user_id = :l_user_id';
2274
2275 EXECUTE IMMEDIATE l_sql
2276 INTO l_resource_id
2277 USING l_user_id;
2278
2279 --dbms_output.put_line('resource_id--> '||l_resource_id);
2280 FOR cur_rec IN c_node
2281 LOOP
2282 --dbms_output.put_line('in the loop of enum');
2283 i := i+1;
2284 x_valid_nodes.EXTEND(1);
2285
2286 --dbms_output.put_line('extended');
2287
2288 x_valid_nodes(x_valid_nodes.last) := IEU_DIAG_NODE_OBJ(cur_rec.node_id, cur_rec.node_label,
2289 cur_rec.node_pid, cur_rec.node_weight);
2290
2291 --dbms_output.put_line('node_id--> '||i||'-->'||cur_rec.node_id);
2292 --dbms_output.put_line('node_name-->'||cur_rec.node_label);
2293 --dbms_output.put_line('node_pid-->'||cur_rec.node_pid);
2294 --dbms_output.put_line('node_weight-->'||cur_rec.node_weight);
2295 end LOOP;
2296
2297 EXCEPTION
2298 WHEN NO_DATA_FOUND THEN
2299 --dbms_output.PUT_LINE('resource id does not exists ');
2300 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESOURCEID');-- Resource ID does not exist
2301 FND_MSG_PUB.Add;
2302 x_return_status := FND_API.G_RET_STS_ERROR;
2303 --dbms_output.PUT_LINE('No data found for resource id : ');
2304 END;
2305
2306 end if;
2307
2308 --dbms_output.PUT_LINE('initialized');
2309 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
2310
2311
2312 -- Standard call to get message count and if count is 1, get message info.
2313 /*FND_MSG_PUB.Count_And_Get(
2314 p_count => x_msg_count,
2315 p_data => l_msg_data
2316 );
2317
2318 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2319 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2320 x_msg_data := x_msg_data || ',' || l_msg_data;
2321 END LOOP;*/
2322 x_msg_data := '';
2323
2324 x_msg_count := fnd_msg_pub.COUNT_MSG();
2325
2326 FOR i in 1..x_msg_count LOOP
2327 l_msg_data := '';
2328 l_msg_count := 0;
2329 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2330 x_msg_data := x_msg_data || ',' || l_msg_data;
2331 END LOOP;
2332
2333
2334 EXCEPTION
2335
2336 WHEN FND_API.G_EXC_ERROR THEN
2337
2338 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2339
2340
2341 x_return_status := FND_API.G_RET_STS_ERROR;
2342 x_msg_data := '';
2343
2344 x_msg_count := fnd_msg_pub.COUNT_MSG();
2345
2346 FOR i in 1..x_msg_count LOOP
2347 l_msg_data := '';
2348 l_msg_count := 0;
2349 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2350 x_msg_data := x_msg_data || ',' || l_msg_data;
2351 END LOOP;
2352
2353
2354 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2355 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2356 x_msg_data := x_msg_data || ',' || l_msg_data;
2357 END LOOP;*/
2358
2359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2360
2361 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2362
2363
2364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2365 x_msg_data := '';
2366
2367 x_msg_count := fnd_msg_pub.COUNT_MSG();
2368
2369 FOR i in 1..x_msg_count LOOP
2370 l_msg_data := '';
2371 l_msg_count := 0;
2372 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2373 x_msg_data := x_msg_data || ',' || l_msg_data;
2374 END LOOP;
2375
2376 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2377 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2378 x_msg_data := x_msg_data || ',' || l_msg_data;
2379 END LOOP;*/
2380
2381
2382 WHEN OTHERS THEN
2383 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
2384 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2385
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2387
2388 x_msg_data := '';
2389
2390 x_msg_count := fnd_msg_pub.COUNT_MSG();
2391
2392 FOR i in 1..x_msg_count LOOP
2393 l_msg_data := '';
2394 l_msg_count := 0;
2395 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2396 x_msg_data := x_msg_data || ',' || l_msg_data;
2397 END LOOP;
2398
2399
2400 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2401 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2402 x_msg_data := x_msg_data || ',' || l_msg_data;
2403 END LOOP;*/
2404
2405 END Get_Valid_RT_Nodes;
2406
2407
2408 --===================================================================
2409 -- NAME
2410 -- Check_Refresh_Node_Counts
2411 --
2412 -- PURPOSE
2413 -- Private api used to refresh nodes table
2414 --
2415 -- NOTES
2416 -- 1. UWQ Login Diagnostics will use this procedure.
2417 --
2418 --
2419 -- HISTORY
2420 -- 18-Apr-2002 GPAGADAL Created
2421 -- 8-Jan-2003 GPAGADAL updated-- display status/time taken to
2422 -- refresh node
2423 -- 1-May-2003 GPAGADAL updated -- display total time
2424 --===================================================================
2425
2426 PROCEDURE Check_Refresh_Node_Counts ( x_return_status OUT NOCOPY VARCHAR2,
2427 x_msg_count OUT NOCOPY NUMBER,
2428 x_msg_data OUT NOCOPY VARCHAR2,
2429 p_user_name IN VARCHAR2,
2430 p_responsibility IN VARCHAR2,
2431 x_invalid_pkg OUT NOCOPY IEU_DIAG_REFRESH_NST,
2432 x_invalid_rproc OUT NOCOPY IEU_DIAG_REFRESH_ERR_NST,
2433 x_refresh_time OUT NOCOPY IEU_DIAG_REFRENUM_TIME_NST,
2434 x_user_ver_time OUT NOCOPY NUMBER,
2435 x_etime_total OUT NOCOPY NUMBER,
2436 x_rtime_total OUT NOCOPY NUMBER
2437 )
2438 AS
2439
2440 l_user_name FND_USER.USER_NAME%TYPE;
2441
2442 l_user_id FND_USER.USER_ID%TYPE;
2443
2444 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
2445
2446 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
2447
2448 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
2449
2450 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
2451
2452 l_msg_count NUMBER(2);
2453
2454 l_msg_data VARCHAR2(2000);
2455
2456 l_language VARCHAR2(4);
2457
2458 l_valid_nodes IEU_DIAG_NODE_NST;
2459
2460 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
2461
2462 l_temp_rproc IEU_UWQ_SEL_ENUMERATORS.REFRESH_PROC%TYPE;
2463
2464 l_temp_pkg_name IEU_UWQ_SEL_ENUMERATORS.REFRESH_PROC%TYPE;
2465
2466 l_temp_count NUMBER(10) ;
2467
2468 i integer ;
2469
2470 j integer ;
2471
2472 l_invalid_rproc IEU_DIAG_REFRESH_ERR_NST;
2473
2474 l_return_status VARCHAR2(1) ;
2475
2476
2477 l_count NUMBER;
2478 l_where_clause VARCHAR2(30000);
2479 l_refresh_view_name varchar2(200);
2480 l_refresh_view_sum_col varchar2(200);
2481 l_sel_rt_node_id number;
2482 l_node_id number(10);
2483 l_node_pid number(10);
2484 l_sel_enum_id number(15);
2485 l_res_cat_enum_flag varchar2(1);
2486 l_view_name varchar2(512);
2487 l_node_label varchar2(512);
2488 l_node_weight ieu_uwq_sel_rt_nodes.node_weight%type;
2489
2490 l_temp_view_name varchar2(512);
2491 l_temp_view_count number(2);
2492
2493 l_appli_name varchar2(240) ;
2494
2495 t1 NUMBER; -- start time
2496 t2 NUMBER; -- end time
2497 l_time_spent NUMBER; -- time elapsed
2498 refresh_status VARCHAR2(1); -- Succeeded or Failed
2499
2500
2501 l_rtotal_time NUMBER; -- refresh total time
2502 l_etotal_time NUMBER; -- enum total time
2503 l_user_ver_time NUMBER; -- time taken to verify user name and responsibility
2504
2505 l_sql VARCHAR2(4000);
2506
2507
2508
2509 CURSOR c_nodes IS
2510 SELECT
2511 rt_nodes.sel_rt_node_id,
2512 rt_nodes.node_id,
2513 rt_nodes.node_pid,
2514 rt_nodes.view_name,
2515 rt_nodes.where_clause,
2516 rt_nodes.media_type_id,
2517 rt_nodes.sel_enum_id,
2518 rt_nodes.refresh_view_name,
2519 rt_nodes.refresh_view_sum_col,
2520 rt_nodes.res_cat_enum_flag,
2521 rt_nodes.node_label,
2522 rt_nodes.node_weight
2523 FROM
2524
2525 ieu_uwq_sel_rt_nodes rt_nodes
2526 WHERE
2527 (rt_nodes.resource_id = l_resource_id) AND
2528 (rt_nodes.node_id <> 0) AND
2529 (rt_nodes.node_id <> IEU_CONSTS_PUB.G_SNID_MEDIA) and
2530 (rt_nodes.not_valid = 'N')
2531 order by rt_nodes.node_pid, rt_nodes.node_weight;
2532
2533 CURSOR c_media_nodes IS
2534 SELECT
2535 rt_nodes.sel_rt_node_id,
2536 rt_nodes.node_id,
2537 rt_nodes.node_pid,
2538 rt_nodes.where_clause,
2539 rt_nodes.sel_enum_id,
2540 rt_nodes.refresh_view_name,
2541 rt_nodes.refresh_view_sum_col,
2542 rt_nodes.res_cat_enum_flag,
2543 rt_nodes.view_name,
2544 rt_nodes.node_label,
2545 rt_nodes.node_weight
2546 FROM
2547 ieu_uwq_sel_rt_nodes rt_nodes
2548 WHERE
2549 (rt_nodes.resource_id = l_resource_id) AND
2550 (rt_nodes.node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) and
2551 (rt_nodes.not_valid = 'N')
2552 order by rt_nodes.node_pid, rt_nodes.node_weight;
2553
2554 cursor c_rproc is
2555 SELECT
2556 e.sel_enum_id,
2557 e.refresh_proc,
2558 e.work_q_register_type,
2559 e.media_type_id,
2560 e.application_id,
2561 e.work_q_label_lu_type,
2562 e.work_q_label_lu_code
2563 FROM
2564 IEU_UWQ_SEL_ENUMERATORS e
2565 WHERE EXISTS (select 'x' from FND_PROFILE_OPTIONS b
2566 where upper(b.PROFILE_OPTION_NAME) = upper(e.work_q_enable_profile_option))
2567 AND ((e.NOT_VALID_FLAG is NULL) OR (e.NOT_VALID_FLAG = 'N')) AND
2568 (nvl(fnd_profile.value(e.work_q_enable_profile_option),'Y') = 'Y')
2569 order by e.sel_enum_id;
2570
2571
2572 cursor c_dproc (enum_id NUMBER, app_id NUMBER, l_type VARCHAR2, l_code VARCHAR2)is
2573 select distinct v.meaning, s.sel_enum_id, s.refresh_proc, tl.application_name
2574 from ieu_uwq_sel_enumerators s,
2575 fnd_application_tl tl,
2576 fnd_lookup_values v
2577 where s.sel_enum_id = enum_id
2578 and tl.application_id = app_id
2579 and v.lookup_type = l_type
2580 and v.lookup_code = l_code
2581 and tl.language = l_language
2582 and v.language = l_language;
2583
2584 cursor c_temp (enum_id NUMBER)is
2585 select tl.application_name
2586 from ieu_uwq_sel_enumerators s,
2587 fnd_application_tl tl
2588 where s.sel_enum_id = enum_id
2589 and tl.application_id = s.application_id
2590 and tl.language = l_language;
2591
2592
2593
2594 l_dupli_proc IEU_DIAG_ENUM_NST;
2595 l_invalid_pkg IEU_DIAG_ENUM_NST;
2596 l_invalid_proc IEU_DIAG_ENUM_ERR_NST;
2597 l_enum_time IEU_DIAG_ENUM_TIME_NST;
2598
2599 BEGIN
2600 fnd_msg_pub.delete_msg();
2601 x_return_status := fnd_api.g_ret_sts_success;
2602 FND_MSG_PUB.initialize;
2603 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
2604 x_invalid_pkg := IEU_DIAG_REFRESH_NST();
2605 x_invalid_rproc := IEU_DIAG_REFRESH_ERR_NST();
2606 l_invalid_rproc := IEU_DIAG_REFRESH_ERR_NST();
2607 x_refresh_time := IEU_DIAG_REFRENUM_TIME_NST();
2608
2609 l_dupli_proc := IEU_DIAG_ENUM_NST();
2610 l_invalid_pkg := IEU_DIAG_ENUM_NST();
2611 l_invalid_proc := IEU_DIAG_ENUM_ERR_NST();
2612 l_enum_time := IEU_DIAG_ENUM_TIME_NST();
2613 l_user_ver_time := 0;
2614 l_etotal_time := 0;
2615 l_rtotal_time := 0;
2616 x_msg_count := 0;
2617 x_msg_data := '';
2618
2619
2620 --Check_User_Resp(x_return_status, x_msg_count, x_msg_data,
2621 -- p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
2622
2623 Check_Node_Enumeration ( x_return_status,
2624 x_msg_count,
2625 x_msg_data,
2626 p_user_name,
2627 p_responsibility,
2628 l_dupli_proc,
2629 l_invalid_pkg ,
2630 l_invalid_proc ,
2631 l_enum_time ,
2632 l_user_ver_time ,
2633 l_etotal_time
2634
2635 );
2636
2637 x_user_ver_time := l_user_ver_time;
2638 x_etime_total := l_etotal_time;
2639
2640 if (x_return_status = 'S') then
2641 -- DBMS_OUTPUT.put_line('Check_Node_Enumeration...success');
2642
2643 -- get the user id , responsibility id and application id
2644
2645 l_sql := ' select user_id from fnd_user
2646 where upper(user_name) like upper(:p_user_name)';
2647 EXECUTE IMMEDIATE l_sql
2648 INTO l_user_id
2649 USING in p_user_name;
2650 l_sql := ' select responsibility_id, application_id from fnd_responsibility_tl where language = :l_language and responsibility_id = :p_responsibility';
2651 /* l_sql := ' select responsibility_id, application_id //bug6414726
2652 from fnd_responsibility_tl where language = :l_language
2653 and responsibility_name like :p_responsibility'; */
2654
2655 EXECUTE IMMEDIATE l_sql
2656 INTO l_responsibility_id, l_application_id
2657 USING l_language, p_responsibility;
2658
2659
2660
2661
2662 l_sql := 'select resource_id
2663 from jtf_rs_resource_extns
2664 where user_id = :l_user_id';
2665
2666 --DBMS_OUTPUT.put_line('query---'||l_sql);
2667 EXECUTE IMMEDIATE l_sql
2668 INTO l_resource_id
2669 USING l_user_id;
2670
2671 --DBMS_OUTPUT.put_line('resource_id--> '||l_resource_id);
2672
2673 FOR c_rec in c_rproc LOOP
2674
2675 begin
2676
2677 l_temp_rproc := c_rec.refresh_proc;
2678
2679 if not(l_temp_rproc is null) then
2680
2681 l_temp_pkg_name := substr(l_temp_rproc,1, ( instr(l_temp_rproc,'.',1,1)-1));
2682 begin
2683
2684 select count(*) into l_temp_count
2685 from all_objects
2686 where owner = 'APPS' and object_type in('PACKAGE', 'PACKAGE BODY')
2687 and status='VALID'and object_name = l_temp_pkg_name;
2688
2689
2690 if not(l_temp_count is null) and (l_temp_count <= 0) then
2691
2692 FOR c_temp2 in c_dproc(c_rec.sel_enum_id, c_rec.application_id, c_rec.work_q_label_lu_type, c_rec.work_q_label_lu_code)
2693 LOOP
2694 --dbms_output.put_line('in the for loop');
2695 j := j+1;
2696 x_invalid_pkg.EXTEND();
2697 refresh_status := 'F';
2698 ----DBMS_OUTPUT.put_line('invalid pkg extended');
2699 x_invalid_pkg(x_invalid_pkg.LAST) := IEU_DIAG_REFRESH_OBJ(c_temp2.application_name,
2700 c_temp2.meaning, c_temp2.refresh_proc, 'PACKAGE');
2701 --DBMS_OUTPUT.put_line('invalid pkg added');
2702 END LOOP;
2703
2704 end if;
2705
2706 EXCEPTION
2707 WHEN NO_DATA_FOUND THEN
2708 null;
2709 end;
2710 end if;
2711 end;
2712 END LOOP;
2713
2714 if (x_invalid_pkg IS NOT NULL and x_invalid_pkg.count > 0 ) then
2715 x_return_status := FND_API.G_RET_STS_ERROR;
2716 refresh_status := 'F';--Failed
2717 end if;
2718
2719
2720
2721 begin
2722 FOR node in c_nodes
2723 LOOP
2724
2725 refresh_status := 'S';--Succeeded
2726 t1 := DBMS_UTILITY.GET_TIME;
2727 --DBMS_OUTPUT.put_line('start time== '||t1);
2728
2729 begin
2730 null;
2731 select count(object_name) into l_temp_view_count from all_objects
2732 where object_name = node.view_name and object_type = 'VIEW'
2733 and status = 'VALID' and owner = 'APPS';
2734
2735 select tl.application_name into l_appli_name
2736 from ieu_uwq_sel_enumerators s,
2737 fnd_application_tl tl
2738 where s.sel_enum_id = node.sel_enum_id
2739 and tl.application_id = s.application_id
2740 and tl.language = l_language;
2741
2742
2743 if (l_temp_view_count <> 1) then
2744 x_return_status := FND_API.G_RET_STS_ERROR;
2745
2746 i:= i+1;
2747 x_invalid_pkg.EXTEND();
2748 --DBMS_OUTPUT.put_line('invalid pkg extended for view');
2749 refresh_status := 'F';--Failed
2750
2751 x_invalid_pkg(x_invalid_pkg.LAST) := IEU_DIAG_REFRESH_OBJ(l_appli_name, node.node_label, node.view_name,'VIEW');
2752 --DBMS_OUTPUT.put_line('invalid pkg added');
2753
2754
2755 end if;
2756 EXCEPTION
2757 WHEN NO_DATA_FOUND THEN
2758 null;
2759 end;
2760
2761
2762 l_count := 0;
2763
2764
2765
2766 Refresh_Node(node.node_id, node.node_pid, node.sel_enum_id, node.where_clause,
2767 node.res_cat_enum_flag, node.refresh_view_name, node.refresh_view_sum_col,
2768 node.sel_rt_node_id, l_count, l_resource_id, node.view_name, node.node_label, l_invalid_rproc);
2769
2770 t2 := DBMS_UTILITY.GET_TIME;
2771 --DBMS_OUTPUT.put_line('end time=== '||t2);
2772 l_time_spent := (t2 - t1)*10;
2773 --DBMS_OUTPUT.put_line('Difference***-- '||l_time_spent);
2774 l_rtotal_time := l_rtotal_time + l_time_spent;
2775 --DBMS_OUTPUT.put_line('total***-- '||l_rtotal_time);
2776
2777 IF l_invalid_rproc.count > 0 THEN
2778 refresh_status := 'F';--Failed
2779 --DBMS_OUTPUT.put_line('failed');
2780 END IF;
2781 --DBMS_OUTPUT.put_line('put refresh time in nst');
2782
2783 x_refresh_time.EXTEND();
2784
2785 /*(vnode_id NUMBER(22),
2786 vnode_label VARCHAR2(512),
2787 vnode_pid NUMBER(22),
2788 vnode_weight NUMBER(22),
2789 status VARCHAR2(512),
2790 time_taken NUMBER(22)
2791
2792 */
2793 x_refresh_time(x_refresh_time.LAST) := IEU_DIAG_REFRENUM_TIME_OBJ(node.node_id, node.node_label,
2794 node.node_pid, node.node_weight, refresh_status, l_time_spent);
2795
2796
2797 /* insert into g_temp (G) values (l_time_spent);
2798 commit;*/
2799
2800 IF l_invalid_rproc.count > 0 THEN
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 FOR i IN l_invalid_rproc.first..l_invalid_rproc.last LOOP
2803 IF l_invalid_rproc.exists(i) THEN
2804 x_invalid_rproc.EXTEND();
2805 x_invalid_rproc(x_invalid_rproc.last) := l_invalid_rproc(i);
2806
2807 END IF;
2808 END LOOP;
2809 END IF;
2810
2811 /* if (x_invalid_rproc IS NOT NULL) then
2812 x_return_status := FND_API.G_RET_STS_ERROR;
2813 loop
2814
2815 if x_invalid_rproc.EXISTS(i) then
2816 --dbms_output.put_line('count: -->'|| i);
2817
2818 --dbms_output.put_line('application name id (' || i|| '): '||x_invalid_rproc(i).app_name);
2819 --dbms_output.put_line('node name (' || i|| '): '|| x_invalid_rproc(i).vnode_label);
2820 --dbms_output.put_line('obj name (' || i || '): '|| x_invalid_rproc(i).obj_name);
2821 i := i+1;
2822 else
2823 exit;
2824 end if;
2825
2826
2827 end loop;
2828
2829 end if;*/
2830
2831 END LOOP;
2832 end;
2833
2834 begin
2835 open c_media_nodes;
2836
2837 fetch c_media_nodes
2838 into l_sel_rt_node_id,l_node_id, l_node_pid, l_where_clause, l_sel_enum_id,
2839 l_refresh_view_name,l_refresh_view_sum_col, l_res_cat_enum_flag, l_view_name, l_node_label, l_node_weight;
2840
2841 t1 := DBMS_UTILITY.GET_TIME;
2842 --DBMS_OUTPUT.put_line('start time== '||t1);
2843
2844 if c_media_nodes%NOTFOUND then
2845 null;
2846 else
2847 begin
2848
2849 select count(object_name) into l_temp_view_count from all_objects
2850 where object_name = l_view_name and object_type = 'VIEW'
2851 and status = 'VALID' and owner = 'APPS';
2852
2853 select tl.application_name into l_appli_name
2854 from ieu_uwq_sel_enumerators s,
2855 fnd_application_tl tl
2856 where s.sel_enum_id = l_sel_enum_id
2857 and tl.application_id = s.application_id
2858 and tl.language = l_language;
2859
2860 if (l_temp_view_count <> 1) then
2861 x_return_status := FND_API.G_RET_STS_ERROR;
2862 i:= i+1;
2863 x_invalid_pkg.EXTEND();
2864 --DBMS_OUTPUT.put_line('invalid pkg extended');
2865 refresh_status := 'F';--Failed
2866
2867 x_invalid_pkg(x_invalid_pkg.LAST) := IEU_DIAG_REFRESH_OBJ(l_appli_name, l_node_label, l_view_name,'VIEW');
2868 --DBMS_OUTPUT.put_line('invalid pkg added for view');
2869
2870 end if;
2871 EXCEPTION
2872 WHEN NO_DATA_FOUND THEN
2873 null;
2874 end;
2875
2876 l_count := 0;
2877 refresh_status := 'S';--Succeeded
2878
2879
2880 Refresh_Node(l_node_id, l_node_pid, l_sel_enum_id, l_where_clause,
2881 l_res_cat_enum_flag, l_refresh_view_name, l_refresh_view_sum_col,
2882 l_sel_rt_node_id, l_count, l_resource_id,l_view_name, l_node_label, l_invalid_rproc);
2883
2884 t2 := DBMS_UTILITY.GET_TIME;
2885 --DBMS_OUTPUT.put_line('end time=== '||t2);
2886 l_time_spent := (t2 - t1)*10;
2887 --DBMS_OUTPUT.put_line('Difference***-- '||l_time_spent);
2888 l_rtotal_time := l_rtotal_time + l_time_spent;
2889 --DBMS_OUTPUT.put_line('total***-- '||l_rtotal_time);
2890
2891 IF l_invalid_rproc.count > 0 THEN
2892 x_return_status := FND_API.G_RET_STS_ERROR;--
2893 refresh_status := 'F';--Failed
2894 --DBMS_OUTPUT.put_line('failed');
2895 END IF;
2896 --DBMS_OUTPUT.put_line('put refresh time in nst');
2897
2898 x_refresh_time.EXTEND();
2899
2900 /*(vnode_id NUMBER(22),
2901 vnode_label VARCHAR2(512),
2902 vnode_pid NUMBER(22),
2903 vnode_weight NUMBER(22),
2904 status VARCHAR2(512),
2905 time_taken NUMBER(22)
2906
2907 */
2908 x_refresh_time(x_refresh_time.LAST) := IEU_DIAG_REFRENUM_TIME_OBJ(l_node_id, l_node_label,
2909 l_node_pid, l_node_weight, refresh_status, l_time_spent);
2910
2911 /* insert into g_temp (G) values (l_time_spent);
2912 commit;*/
2913
2914 IF l_invalid_rproc.count > 0 THEN
2915 x_return_status := FND_API.G_RET_STS_ERROR;--
2916 FOR i IN l_invalid_rproc.first..l_invalid_rproc.last LOOP
2917 IF l_invalid_rproc.exists(i) THEN
2918 x_invalid_rproc.EXTEND();
2919 refresh_status := 'F';--Failed
2920 x_invalid_rproc(x_invalid_rproc.last) := l_invalid_rproc(i);
2921 --dbms_output.put_line('application name '|| l_invalid_rproc(i).app_name);null; -- type of data not known
2922 END IF;
2923 END LOOP;
2924 END IF;
2925
2926
2927 /* --x_invalid_rproc.EXTEND();
2928 x_invalid_rproc := l_invalid_rproc;
2929 --x_return_status := l_return_status;*/
2930 if (x_invalid_rproc IS NOT NULL and x_invalid_rproc.count > 0) then
2931 x_return_status := FND_API.G_RET_STS_ERROR;
2932 refresh_status := 'F';--Failed
2933 end if;
2934
2935 end if;
2936 END;
2937 else
2938 -- DBMS_OUTPUT.put_line('Check_Node_Enumeration...failed');
2939 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_ENUM_FAIL_FIXIT');
2940 FND_MSG_PUB.Add;
2941
2942 end if;
2943
2944 commit;
2945
2946 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
2947 -- Standard call to get message count and if count is 1, get message info.
2948 /*FND_MSG_PUB.Count_And_Get(
2949 p_count => x_msg_count,
2950 p_data => l_msg_data
2951 );
2952
2953 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2954 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2955 x_msg_data := x_msg_data || ',' || l_msg_data;
2956 END LOOP;*/
2957 -- x_msg_data := '';
2958
2959 x_msg_count := fnd_msg_pub.COUNT_MSG();
2960
2961 FOR i in 1..x_msg_count LOOP
2962 l_msg_data := '';
2963 l_msg_count := 0;
2964 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2965 x_msg_data := x_msg_data || ',' || l_msg_data;
2966 END LOOP;
2967
2968 x_rtime_total := l_rtotal_time;
2969 --DBMS_OUTPUT.put_line('refresh time'||x_rtime_total);
2970 EXCEPTION
2971
2972 WHEN FND_API.G_EXC_ERROR THEN
2973
2974 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2975 x_return_status := FND_API.G_RET_STS_ERROR;
2976 -- x_msg_data := '';
2977 x_msg_count := fnd_msg_pub.COUNT_MSG();
2978
2979 FOR i in 1..x_msg_count LOOP
2980 l_msg_data := '';
2981 l_msg_count := 0;
2982 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2983 x_msg_data := x_msg_data || ',' || l_msg_data;
2984 END LOOP;
2985
2986
2987 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
2988 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
2989 x_msg_data := x_msg_data || ',' || l_msg_data;
2990 END LOOP;*/
2991
2992
2993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2994
2995 --dbms_output.PUT_LINE('Error : '||sqlerrm);
2996
2997
2998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2999 -- x_msg_data := '';
3000
3001 x_msg_count := fnd_msg_pub.COUNT_MSG();
3002
3003 FOR i in 1..x_msg_count LOOP
3004 l_msg_data := '';
3005 l_msg_count := 0;
3006 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3007 x_msg_data := x_msg_data || ',' || l_msg_data;
3008 END LOOP;
3009
3010 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3011 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3012 x_msg_data := x_msg_data || ',' || l_msg_data;
3013 END LOOP;*/
3014
3015
3016 WHEN OTHERS THEN
3017 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
3018 --dbms_output.PUT_LINE('Error : '||sqlerrm);
3019
3020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3021
3022 -- x_msg_data := '';
3023
3024 x_msg_count := fnd_msg_pub.COUNT_MSG();
3025
3026 FOR i in 1..x_msg_count LOOP
3027 l_msg_data := '';
3028 l_msg_count := 0;
3029 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3030 x_msg_data := x_msg_data || ',' || l_msg_data;
3031 END LOOP;
3032
3033 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3034 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3035 x_msg_data := x_msg_data || ',' || l_msg_data;
3036 END LOOP;*/
3037
3038 END Check_Refresh_Node_Counts;
3039
3040 --===================================================================
3041 -- NAME
3042 -- Refresh_Node
3043 --
3044 -- PURPOSE
3045 -- Private api to refresh nodes
3046 --
3047 -- NOTES
3048 -- 1. UWQ Login Diagnostics will use this procedure.
3049 --
3050 --
3051 -- HISTORY
3052 -- 19-Apr-2002 GPAGADAL Created
3053
3054 --===================================================================
3055
3056 PROCEDURE Refresh_Node(
3057 p_node_id in number,
3058 p_node_pid in number,
3059 p_sel_enum_id in number,
3060 p_where_clause in varchar2,
3061 p_res_cat_enum_flag in varchar2,
3062 p_refresh_view_name in varchar2,
3063 p_refresh_view_sum_col in varchar2,
3064 p_sel_rt_node_id in number,
3065 p_count in number,
3066 p_resource_id in number,
3067 p_view_name in varchar2,
3068 p_node_label in varchar2,
3069 x_invalid_rproc OUT NOCOPY IEU_DIAG_REFRESH_ERR_NST
3070 )
3071 AS
3072
3073 l_count NUMBER;
3074 l_refresh_proc VARCHAR2(100);
3075 l_where_clause VARCHAR2(30000);
3076 l_res_cat_where_clause VARCHAR2(30000);
3077 l_sql_stmt VARCHAR2(30000);
3078 l_cursor_name INTEGER;
3079 l_rows_processed INTEGER;
3080 l_rtnode_bind_var_flag Varchar2(50) ;
3081 l_enum_bind_var_flag Varchar2(50) ;
3082 l_resource_id_flag Varchar2(10) ;
3083 l_node_count number;
3084 l_param_pk_value varchar2(500);
3085 l_media_sql_stmt varchar2(10000);
3086
3087
3088 l_language VARCHAR2(4);
3089 l_appli_name varchar2(240) ;
3090 --valid_rproc boolean;
3091
3092 i integer ;
3093 l_sql VARCHAR2(4000);
3094
3095
3096
3097 CURSOR c_bindVal IS
3098 SELECT
3099 rt_nodes_bind_val.SEL_RT_NODE_ID,
3100 rt_nodes_bind_val.node_id,
3101 rt_nodes_bind_val.BIND_VAR_NAME,
3102 rt_nodes_bind_val.bind_var_value
3103 FROM
3104
3105 ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
3106 WHERE
3107 (rt_nodes_bind_val.resource_id = p_resource_id) AND
3108 (rt_nodes_bind_val.node_id <> 0) AND
3109 (rt_nodes_bind_val.not_valid_flag = 'N');
3110
3111 BEGIN
3112
3113 l_rtnode_bind_var_flag :='T';
3114 l_enum_bind_var_flag := '';
3115 l_resource_id_flag := '';
3116
3117 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
3118 x_invalid_rproc := IEU_DIAG_REFRESH_ERR_NST();
3119 -- valid_rproc := true;
3120 --x_return_status := null;
3121 if ( (p_node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) or
3122 (p_node_id = IEU_CONSTS_PUB.G_SNID_BLENDED) )
3123 then
3124 begin
3125 select
3126 where_clause
3127 into
3128 l_res_cat_where_clause
3129 from
3130 ieu_uwq_res_cats_b
3131 where
3132 res_cat_id = 10001;
3133
3134 exception
3135 when no_data_found then
3136 null;
3137 end;
3138 else
3139 l_res_cat_where_clause := ieu_pub.get_enum_res_cat(p_sel_enum_id);
3140 end if;
3141
3142 if (p_where_clause is NULL)
3143 then
3144 l_where_clause := l_res_cat_where_clause;
3145 l_rtnode_bind_var_flag := 'F';
3146 else
3147
3148 if (p_res_cat_enum_flag = 'Y') OR (p_res_cat_enum_flag is NULL)
3149 then
3150 if (l_res_cat_where_clause) is not null
3151 then
3152 l_where_clause :=
3153 l_res_cat_where_clause || ' and ' || p_where_clause;
3154 --l_rtnode_bind_var_flag := 'F';
3155 l_rtnode_bind_var_flag := 'T';
3156 end if;
3157 else
3158 l_where_clause := p_where_clause;
3159 l_rtnode_bind_var_flag := 'T';
3160 end if;
3161 end if;
3162
3163
3164
3165 if (l_res_cat_where_clause is not null)
3166 then
3167 select
3168 decode(
3169 (instr(l_res_cat_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
3170 into
3171 l_enum_bind_var_flag
3172 from
3173 dual;
3174 else
3175 l_enum_bind_var_flag := 'F';
3176 end if;
3177
3178
3179 BEGIN
3180
3181
3182 -- Use sel_enum_id to find which proc to call
3183 BEGIN
3184 select
3185 refresh_proc
3186 into
3187 l_refresh_proc
3188 from
3189 ieu_uwq_sel_enumerators
3190 where
3191 sel_enum_id = p_sel_enum_id;
3192 EXCEPTION
3193 WHEN NO_DATA_FOUND THEN
3194 NULL;
3195
3196 END;
3197
3198 --
3199 -- If Refresh Proc is present then get the count from refresh proc
3200 -- otherwise, get count from the refresh view, or default query.
3201 --
3202 IF (l_refresh_proc IS not NULL)
3203 THEN
3204
3205 BEGIN
3206 -- If any refresh proc produces an error
3207
3208 select tl.application_name into l_appli_name
3209 from ieu_uwq_sel_enumerators s,
3210 fnd_application_tl tl
3211 where s.sel_enum_id = p_sel_enum_id
3212 and tl.application_id = s.application_id
3213 and tl.language = l_language;
3214
3215 --dbms_output.put_line('call refresh procedure');
3216 execute immediate
3217 'begin '|| l_refresh_proc || '(' || ':P_RESOURCE_ID' ||
3218 ', '|| ':p_node_id ' || ',:l_count);end;'
3219 using
3220 in p_resource_id, in p_node_id, out l_count;
3221 EXCEPTION
3222
3223 when no_data_found then
3224 null;
3225 when others then
3226 --valid_rproc := false;
3227
3228 --x_return_status := FND_API.G_RET_STS_ERROR;
3229 --dbms_output.put_line('exception raised while calling refresh');
3230 x_invalid_rproc.EXTEND();
3231 --dbms_output.put_line('invalid rproc extended');
3232
3233 x_invalid_rproc(x_invalid_rproc.LAST) := IEU_DIAG_REFRESH_ERR_OBJ(l_appli_name,
3234 p_node_label, l_refresh_proc, 'PROCEDURE', SQLERRM);
3235 --dbms_output.put_line('invalid rproc added');
3236
3237
3238 END;
3239
3240 ELSE
3241
3242 --
3243
3244 -- if we have a refresh view then get the count from the refresh
3245 -- view else from the base view .
3246 --
3247 IF (p_refresh_view_name IS NOT NULL)
3248 THEN
3249
3250 --
3251 -- If there is a SUM column specified for the node, then use that
3252 -- to perform a sum on the specified column in refresh view.
3253 --
3254 IF (p_REFRESH_VIEW_SUM_COL IS NOT NULL)
3255 THEN
3256 --
3257 -- special processing for SUM count logic (i.e., Media nodes)
3258 --
3259 l_sql_stmt :=
3260 'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
3261 p_REFRESH_view_name || ' where ' || l_where_clause ||
3262 ' and ieu_param_pk_value is not null';
3263
3264 /* this following codes added because in passive mode there is no classification but 'ANY'
3265 so, the above select would not work for 'ANY' */
3266
3267 if p_node_pid = 4000 then
3268 l_media_sql_stmt :=
3269 'begin select count(*) into :l_node_count from '||
3270 p_refresh_view_name||' where resource_id = '||p_resource_id||'; end;';
3271
3272 EXECUTE IMMEDIATE l_media_sql_stmt
3273 USING out l_node_count;
3274
3275 if l_node_count = 1 then
3276 l_media_sql_stmt :=
3277 'begin select ieu_param_pk_value into :l_param_pk_value from '||
3278 p_refresh_view_name||' where resource_id = '||p_resource_id||'; end;';
3279
3280 EXECUTE IMMEDIATE l_media_sql_stmt
3281 USING out l_param_pk_value;
3282
3283 if l_param_pk_value is null then
3284 l_sql_stmt :=
3285 'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
3286 p_REFRESH_view_name || ' where ' || l_where_clause;
3287 end if;
3288 end if;
3289
3290 end if;
3291
3292 ELSE
3293
3294 l_sql_stmt :=
3295 'select count(resource_id) from ' || p_refresh_view_name ||
3296 ' where ' || l_where_clause;
3297
3298
3299 END IF;
3300
3301 ELSE
3302
3303 --
3304 -- we'll have to collect the count
3305 --
3306 l_sql_stmt :=
3307 'select count(resource_id) from ' || p_view_name ||
3308 ' where ' || l_where_clause;
3309
3310 END IF;
3311
3312
3313
3314 --
3315 -- Execute the sql_stmt to get the count
3316 --
3317
3318 BEGIN
3319 l_cursor_name := dbms_sql.open_cursor;
3320 DBMS_SQL.PARSE(l_cursor_name,l_sql_stmt , dbms_sql.native);
3321
3322 If (l_rtnode_bind_var_flag = 'T')
3323 then
3324
3325 -- Check if resource_id is present.
3326 if (l_where_clause is not null)
3327 then
3328 select
3329 decode((instr(l_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
3330 into
3331 l_resource_id_flag
3332 from
3333 dual;
3334 else
3335 l_resource_id_flag := 'F';
3336 end if;
3337
3338 if (l_resource_id_flag = 'T')
3339 then
3340 DBMS_SQL.BIND_VARIABLE (
3341 l_cursor_name,
3342 ':resource_id',
3343 p_resource_id );
3344 end if;
3345
3346
3347 for b in c_bindVal
3348 loop
3349
3350 if ( (b.sel_rt_node_id = p_sel_rt_node_id) and
3351 (b.node_id = p_node_id) )
3352 then
3353 -- Ignore bind Var :resource_id here.
3354 If (b.bind_var_name <> ':resource_id')
3355 then
3356
3357 DBMS_SQL.BIND_VARIABLE (
3358 l_cursor_name,
3359 b.bind_var_name,
3360 b.bind_var_value );
3361 end if;
3362 end if;
3363
3364 end loop;
3365
3366
3367 else
3368
3369 if (l_enum_bind_var_flag = 'T')
3370 then
3371 DBMS_SQL.BIND_VARIABLE (
3372 l_cursor_name,
3373 ':resource_id',
3374 p_resource_id );
3375 end if;
3376
3377 end if;
3378
3379 --DBMS_SQL.BIND_VARIABLE(l_cursor_name, ':resource_id', 3807);
3380 DBMS_SQL.DEFINE_COLUMN(l_cursor_name, 1, l_count);
3381 l_rows_processed := dbms_sql.execute(l_cursor_name);
3382
3383 IF (DBMS_SQL.FETCH_ROWS(l_cursor_name) > 0)
3384 THEN
3385 -- get column values of the row
3386 DBMS_SQL.COLUMN_VALUE(l_cursor_name, 1, l_count);
3387 END IF;
3388
3389 DBMS_SQL.close_cursor(l_cursor_name);
3390
3391 EXCEPTION
3392
3393 WHEN OTHERS THEN
3394 DBMS_SQL.CLOSE_CURSOR(l_cursor_name);
3395
3396 END;
3397
3398 END IF;
3399
3400 exception
3401 WHEN OTHERS THEN
3402 l_count := 0;
3403 --dbms_output.put_line(SQLCODE);
3404 --dbms_output.put_line(SQLERRM);
3405
3406
3407 end;
3408
3409 IF (l_count IS NULL)
3410 THEN
3411 l_count := 0;
3412 END IF;
3413
3414 --
3415 -- now update the count for the row
3416 --
3417 UPDATE
3418 IEU_UWQ_SEL_RT_NODES nodes
3419 SET
3420
3421 nodes.count = l_count
3422 WHERE
3423 (nodes.sel_rt_node_id = p_sel_rt_node_id) AND
3424 (nodes.resource_id = p_resource_id);
3425
3426 -- if NOT(valid_rproc)then
3427 -- x_return_status := FND_API.G_RET_STS_ERROR;
3428 -- end if;
3429
3430 --dbms_output.put_line('return status in refresh_node' || x_return_status);
3431
3432 EXCEPTION
3433 WHEN OTHERS THEN
3434 -- nothing we can really do if this fails...
3435 NULL;
3436 --DBMS_OUTPUT.put_line('exception : '||substr(sqlerrm, 1, 50));
3437
3438 END Refresh_Node;
3439
3440
3441 --===================================================================
3442 -- NAME
3443 -- Check_View
3444 --
3445 -- PURPOSE
3446 -- Private api to check each view of the node
3447 --
3448 -- NOTES
3449 -- 1. UWQ Login Diagnostics will use this procedure.
3450 --
3451 --
3452 -- HISTORY
3453 -- 17-Apr-2002 GPAGADAL Created
3454
3455 --===================================================================
3456
3457
3458 PROCEDURE Check_View ( x_return_status OUT NOCOPY VARCHAR2,
3459 x_msg_count OUT NOCOPY NUMBER,
3460 x_msg_data OUT NOCOPY VARCHAR2,
3461 p_user_name IN VARCHAR2,
3462 p_responsibility IN VARCHAR2,
3463 x_invalid_views OUT NOCOPY IEU_DIAG_STRING_NST)
3464 AS
3465 l_user_name FND_USER.USER_NAME%TYPE;
3466
3467 l_user_id FND_USER.USER_ID%TYPE;
3468
3469 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
3470
3471 l_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
3472
3473 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
3474
3475 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
3476
3477 l_msg_count NUMBER(2);
3478
3479 l_msg_data VARCHAR2(2000);
3480
3481 l_language VARCHAR2(4);
3482
3483 l_valid_nodes IEU_DIAG_NODE_NST;
3484
3485 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
3486
3487 l_count NUMBER(3) ;
3488
3489 l_sql VARCHAR2(4000);
3490
3491
3492
3493 CURSOR c_node IS
3494 SELECT
3495 node_id, view_name
3496 FROM ieu_uwq_sel_rt_nodes
3497 WHERE
3498 (resource_id = l_resource_id) and
3499 ((not_valid is null) or (not_valid <> 'Y'))order by node_pid, node_weight;
3500
3501
3502 i integer ;
3503
3504 l_temp_view VARCHAR2(512) ;
3505
3506 BEGIN
3507 fnd_msg_pub.delete_msg();
3508 x_return_status := fnd_api.g_ret_sts_success;
3509 FND_MSG_PUB.initialize;
3510 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
3511 x_invalid_views := IEU_DIAG_STRING_NST();
3512
3513 Check_User_Resp (x_return_status, x_msg_count, x_msg_data,
3514 p_user_name, p_responsibility, l_user_id, l_responsibility_id, l_application_id);
3515
3516 if (x_return_status = 'S') then
3517 BEGIN
3518 l_sql := ' select resource_id from jtf_rs_resource_extns where user_id = :l_user_id';
3519
3520 EXECUTE IMMEDIATE l_sql
3521 INTO l_resource_id
3522 USING l_user_id;
3523
3524 --dbms_output.put_line('resource_id--> '||l_resource_id);
3525
3526
3527
3528
3529 FOR cur_rec IN c_node
3530 LOOP
3531 --dbms_output.put_line('in the loop of enum');
3532 begin
3533
3534 select count(object_name) into l_count from all_objects
3535 where object_name = cur_rec.view_name and object_type = 'VIEW'
3536 and status = 'VALID' and owner = 'APPS';
3537
3538 if l_count <> 1 then
3539
3540 x_return_status := FND_API.G_RET_STS_ERROR;
3541 i := i+1;
3542 x_invalid_views.EXTEND;
3543 x_invalid_views(i) := cur_rec.view_name;
3544 end if;
3545
3546
3547 exception
3548 when no_data_found then
3549 null;
3550 end;
3551
3552
3553 end LOOP;
3554
3555 EXCEPTION
3556 WHEN NO_DATA_FOUND THEN
3557 --dbms_output.PUT_LINE('resource id does not exists ');
3558 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_NO_RESOURCEID');-- Resource ID does not exist
3559 FND_MSG_PUB.Add;
3560 x_return_status := FND_API.G_RET_STS_ERROR;
3561 --dbms_output.PUT_LINE('No data found for resource id : ');
3562 END;
3563
3564 end if;
3565
3566 fnd_global.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id, null);
3567 -- Standard call to get message count and if count is 1, get message info.
3568 /*FND_MSG_PUB.Count_And_Get(
3569 p_count => x_msg_count,
3570 p_data => l_msg_data
3571 );
3572
3573 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3574 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3575 x_msg_data := x_msg_data || ',' || l_msg_data;
3576 END LOOP;*/
3577
3578 x_msg_data := '';
3579
3580 x_msg_count := fnd_msg_pub.COUNT_MSG();
3581
3582 FOR i in 1..x_msg_count LOOP
3583 l_msg_data := '';
3584 l_msg_count := 0;
3585 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3586 x_msg_data := x_msg_data || ',' || l_msg_data;
3587 END LOOP;
3588
3589
3590 EXCEPTION
3591
3592 WHEN FND_API.G_EXC_ERROR THEN
3593
3594 --dbms_output.PUT_LINE('Error : '||sqlerrm);
3595
3596
3597 x_return_status := FND_API.G_RET_STS_ERROR;
3598 FND_MSG_PUB.Count_And_Get(
3599 p_count => x_msg_count,
3600 p_data => l_msg_data
3601 );
3602
3603
3604 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3605 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3606 x_msg_data := x_msg_data || ',' || l_msg_data;
3607 END LOOP;*/
3608
3609
3610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3611
3612 --dbms_output.PUT_LINE('Error : '||sqlerrm);
3613
3614
3615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3616 x_msg_data := '';
3617
3618 x_msg_count := fnd_msg_pub.COUNT_MSG();
3619
3620 FOR i in 1..x_msg_count LOOP
3621 l_msg_data := '';
3622 l_msg_count := 0;
3623 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3624 x_msg_data := x_msg_data || ',' || l_msg_data;
3625 END LOOP;
3626
3627
3628 /*FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3629 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3630 x_msg_data := x_msg_data || ',' || l_msg_data;
3631 END LOOP;*/
3632
3633 WHEN OTHERS THEN
3634 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
3635 --dbms_output.PUT_LINE('Error : '||sqlerrm);
3636
3637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3638
3639 x_msg_data := '';
3640
3641 x_msg_count := fnd_msg_pub.COUNT_MSG();
3642
3643 FOR i in 1..x_msg_count LOOP
3644 l_msg_data := '';
3645 l_msg_count := 0;
3646 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3647 x_msg_data := x_msg_data || ',' || l_msg_data;
3648 END LOOP;
3649
3650
3651 /* FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3652 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3653 x_msg_data := x_msg_data || ',' || l_msg_data;
3654 END LOOP;*/
3655
3656
3657 END Check_View;
3658
3659 PROCEDURE CHECK_OBJECT_FUNCTION(x_return_status OUT NOCOPY VARCHAR2,
3660 x_msg_count OUT NOCOPY NUMBER,
3661 x_msg_data OUT NOCOPY VARCHAR2,
3662 p_object_code IN VARCHAR2,
3663 p_task_source IN VARCHAR2,
3664 x_problem_tasks IN OUT NOCOPY IEU_DIAG_STRING_NST,
3665 x_log IN OUT NOCOPY IEU_DIAG_STRING_NST
3666 )AS
3667 v_create_string2 VARCHAR2(4000);
3668 v_cursor NUMBER;
3669 v_numrows NUMBER;
3670 v_cursor2 NUMBER;
3671 v_numrows2 NUMBER;
3672 v_numrows1 NUMBER;
3673 l_source_object_type_code varchar2(60);
3674 l_object_function varchar2(60);
3675 l_object_code varchar2(30);
3676 l_name varchar2(30);
3677 l_index integer ;
3678 l_msg_count NUMBER(2);
3679
3680 l_msg_data VARCHAR2(2000);
3681 TYPE c_cursor IS REF CURSOR;
3682 c_view_name c_cursor;
3683 sql_stmt varchar2(2000);
3684 sql_stmt1 varchar2(2000);
3685
3686 l_sql VARCHAR2(4000);
3687 l_count NUMBER;
3688 l_application_name VARCHAR2(2000);
3689
3690 begin
3691 x_return_status := fnd_api.g_ret_sts_success;
3692 -- 1. get object_function from jtf_objects_vl and call FND_FUNCTION.TEST
3693 -- 2. if FND_FUNCTION.TEST run successfully, show successful message with function name, application name
3694 -- 3. if FND_FUNCITON.TEST run failed,
3695 -- 3.1 if object_code is 'TASK', test 'Launch TASK Manager',
3696 -- i.e. get object_function from jtf_objects_vl
3697 -- show log message IEU_UWQ_DEFTASKMAN_LAUNCH
3698 -- 3.1.1 successfully, show success message
3699 -- 3.1.2 failed, show error message IEU_UWQ_FUNCTION_NOT_ALLOWED
3700 -- 3.2 not 'TASK' object_code, show error message IEU_UWQ_ALL_NO_SOURCE_DOC
3701 v_cursor := DBMS_SQL.OPEN_CURSOR;
3702 DBMS_SQL.parse(v_cursor,
3703 'SELECT unique object_function, name , APPLICATION_NAME
3704 FROM jtf_objects_vl
3705 WHERE lower(object_code) = lower(:action_code)',
3706 DBMS_SQL.V7);
3707
3708 DBMS_SQL.BIND_VARIABLE(v_cursor, 'action_code', p_object_code);
3709 DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, l_object_function,60);
3710 DBMS_SQL.DEFINE_COLUMN(v_cursor, 2, l_name,30);
3711 DBMS_SQL.DEFINE_COLUMN(v_cursor, 3, l_application_name,30);
3712
3713 v_numrows := DBMS_SQL.EXECUTE(v_cursor);
3714 --v_numrows := DBMS_SQL.FETCH_ROWS(v_cursor);
3715
3716 LOOP
3717 if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
3718 --DBMS_OUTPUT.Put_Line('end of rows');
3719 exit;
3720 end if;
3721
3722 DBMS_SQL.COLUMN_VALUE(v_cursor, 1, l_object_function);
3723 DBMS_SQL.COLUMN_VALUE(v_cursor, 2, l_name);
3724 DBMS_SQL.COLUMN_VALUE(v_cursor, 3, l_application_name);
3725
3726 IF (l_object_function IS null) THEN
3727 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_NULL_OBJ_F');
3728 x_log.extend;
3729 x_log(x_log.last) := FND_MESSAGE.GET();
3730 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_NULL_OBJ_FUNC');
3731 FND_MSG_PUB.Add;
3732 x_return_status := FND_API.G_RET_STS_ERROR;
3733
3734
3735 ELSe
3736 -- call FND_FUNCTION.TEST with this l_object_function
3737 IF (FND_FUNCTION.TEST(l_object_function)) then
3738 -- show success message
3739 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_OBJ_SU');
3740 FND_MESSAGE.SET_TOKEN ('APPLICATION_NAME', l_application_name);
3741 x_log.extend;
3742 x_log(x_log.last) := FND_MESSAGE.GET();
3743 else
3744 -- failed.
3745 IF (p_task_source = 'Y' or p_task_source = 'y') THEN
3746 EXECUTE immediate ' select object_function '||
3747 ' from jtf_objects_vl '||
3748 ' where lower(OBJECT_CODE) = lower(:1) '
3749 INTO l_object_function
3750 USING 'TASK';
3751 IF (FND_FUNCTION.TEST(l_object_function)) then
3752 -- show success message
3753 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_OBJ_SU');
3754 FND_MESSAGE.SET_TOKEN ('APPLICATION_NAME', l_application_name);
3755 x_log.extend;
3756 x_log(x_log.last) := FND_MESSAGE.GET();
3757 ELSE
3758 -- show failed message, IEU_UWQ_FUNCTION_NOT_ALLOWED
3759 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_L_F');
3760 FND_MESSAGE.SET_TOKEN ('APPLICATION_NAME', l_application_name);
3761 x_log.extend;
3762 x_log(x_log.last) := FND_MESSAGE.GET();
3763 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_OBJ_FUN_Y_FAIL');
3764 FND_MSG_PUB.Add;
3765 x_return_status := FND_API.G_RET_STS_ERROR;
3766
3767 END if; -- test for launch Task Manager
3768 ELSE -- not a TASK object code
3769 -- show error message, IEU_UWQ_ALL_NO_SOURCE_DOC
3770 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_OBJ_FUN_N_FAIL');
3771 FND_MSG_PUB.Add;
3772 x_return_status := FND_API.G_RET_STS_ERROR;
3773
3774 END if; -- object_code = 'TASK'
3775 END IF; -- if FND_FUNCTION.TEST
3776 END if; -- if object function is null
3777 end LOOP; -- select object function from view
3778 DBMS_SQL.CLOSE_CURSOR(v_cursor);
3779
3780 --DBMS_OUTPUT.Put_Line('v_numrows is '|| v_numrows);
3781 --DBMS_OUTPUT.Put_Line('l_action_code is '|| l_action_code);
3782
3783 FND_MSG_PUB.Count_And_Get(
3784 p_count => x_msg_count,
3785 p_data => l_msg_data
3786 );
3787
3788 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3789 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3790 x_msg_data := x_msg_data || ',' || l_msg_data;
3791 END LOOP;
3792
3793 EXCEPTION
3794
3795 WHEN FND_API.G_EXC_ERROR THEN
3796
3797 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
3798
3799 x_return_status := FND_API.G_RET_STS_ERROR;
3800 FND_MSG_PUB.Count_And_Get(
3801 p_count => x_msg_count,
3802 p_data => l_msg_data
3803 );
3804
3805
3806 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3807 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3808 x_msg_data := x_msg_data || ',' || l_msg_data;
3809 END LOOP;
3810
3811
3812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3813
3814 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
3815
3816
3817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3818 FND_MSG_PUB.Count_And_Get
3819 (
3820 p_count => x_msg_count,
3821 p_data => l_msg_data
3822 );
3823
3824 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3825 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3826 x_msg_data := x_msg_data || ',' || l_msg_data;
3827 END LOOP;
3828
3829
3830 WHEN OTHERS THEN
3831 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
3832 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
3833
3834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3835
3836 FND_MSG_PUB.Count_And_Get (
3837 p_count => x_msg_count,
3838 p_data => l_msg_data
3839 );
3840
3841 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
3842 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
3843 x_msg_data := x_msg_data || ',' || l_msg_data;
3844 END LOOP;
3845
3846 end CHECK_OBJECT_FUNCTION;
3847
3848 --===================================================================
3849 -- NAME
3850 -- CHECK_TASKS_LAUNCHING
3851 --
3852 -- PURPOSE
3853 -- Private api
3854 --
3855 -- NOTES
3856 -- 1. UWQ Login Diagnostics will use this procedure.
3857 --
3858 --
3859 -- HISTORY
3860 -- 17-Apr-2002 GPAGADAL Created
3861 -- 14-Apr-2004 dolee modified for workitem launch
3862 --===================================================================
3863 PROCEDURE CHECK_TASK_LAUNCHING(x_return_status OUT NOCOPY VARCHAR2,
3864 x_msg_count OUT NOCOPY NUMBER,
3865 x_msg_data OUT NOCOPY VARCHAR2,
3866 p_object_code IN VARCHAR2,
3867 p_responsibility IN VARCHAR2,
3868 p_task_source IN VARCHAR2,
3869 x_problem_tasks OUT NOCOPY IEU_DIAG_STRING_NST,
3870 x_log OUT NOCOPY IEU_DIAG_STRING_NST
3871 )
3872 AS
3873
3874 l_user_name FND_USER.USER_NAME%TYPE;
3875 l_responsibility_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
3876 l_application_id FND_RESPONSIBILITY.APPLICATION_ID%TYPE;
3877 l_user_id FND_USER.USER_ID%TYPE;
3878 l_language VARCHAR2(4);
3879 l_action_code VARCHAR2(60);
3880 l_view_name VARCHAR2(512);
3881 v_cursor NUMBER;
3882 v_cursor1 NUMBER;
3883 v_create_string varchar2(1000);
3884 v_numrows NUMBER;
3885 v_cursor2 NUMBER;
3886 v_cursor3 NUMBER;
3887 v_create_string2 varchar2(1000);
3888 v_numrows2 NUMBER;
3889 v_numrows1 NUMBER;
3890 l_count NUMBER;
3891 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
3892 l_string1 varchar2(60);
3893 l_string2 varchar2(60) ;
3894 l_string3 varchar2(60) ;
3895 l_source_object_type_code varchar2(60);
3896 l_object_function varchar2(60);
3897 l_object_code varchar2(30);
3898 l_name varchar2(30);
3899 l_index integer ;
3900 l_decrease number;
3901 l_decrease1 number;
3902
3903 l_msg_count NUMBER(2);
3904
3905 l_msg_data VARCHAR2(2000);
3906 v_create_string1 varchar2(1000);
3907 v_create_string3 varchar2(1000);
3908 v_create_string4 varchar2(1000);
3909 v_create_string5 varchar2(1000);
3910 TYPE c_cursor IS REF CURSOR;
3911 c_view_name c_cursor;
3912 sql_stmt varchar2(2000);
3913 sql_stmt1 varchar2(2000);
3914
3915 l_sql VARCHAR2(4000);
3916
3917 x_dupli_proc IEU_DIAG_ENUM_NST;
3918 x_invalid_pkg IEU_DIAG_ENUM_NST;
3919 x_invalid_proc IEU_DIAG_ENUM_ERR_NST;
3920 x_enum_time IEU_DIAG_ENUM_TIME_NST;
3921 x_user_ver_time NUMBER;
3922 l_application_name VARCHAR2(2000);
3923 x_etime_grand_total NUMBER;
3924
3925 BEGIN
3926
3927 l_string1 := 'RS_INDIVIDUAL';
3928 l_string2 := 'RS_EMPLOYEE';
3929 l_string3 := 'RS_GROUP';
3930 fnd_msg_pub.delete_msg();
3931 x_return_status := fnd_api.g_ret_sts_success;
3932 FND_MSG_PUB.initialize;
3933 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
3934 x_problem_tasks := IEU_DIAG_STRING_NST();
3935 x_log := IEU_DIAG_STRING_NST();
3936
3937 Check_Object_Resp(x_return_status, x_msg_count, x_msg_data,
3938 p_object_code, p_responsibility, l_responsibility_id);
3939
3940 if (x_return_status = 'S') then -- object code and resp are valid
3941 -- check if the given inputs is
3942 -- registered in ieu_uwq_nonmedia_action
3943 v_cursor3 := DBMS_SQL.OPEN_CURSOR;
3944 DBMS_SQL.parse(v_cursor3,
3945 'SELECT count(action_object_code)
3946 FROM ieu_uwq_nonmedia_actions
3947 WHERE lower(action_object_code) = lower(:action_code)
3948 AND lower(source_for_task_flag) = lower(:flag)
3949 AND nvl(responsibility_id, -1) IN (-1, :resp) ',
3950 DBMS_SQL.V7);
3951
3952 DBMS_SQL.BIND_VARIABLE(v_cursor3, 'action_code', p_object_code);
3953 DBMS_SQL.BIND_VARIABLE(v_cursor3, 'flag', p_task_source);
3954 DBMS_SQL.BIND_VARIABLE(v_cursor3, 'resp', l_responsibility_id);
3955 DBMS_SQL.DEFINE_COLUMN(v_cursor3, 1, l_count);
3956 v_numrows := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor3);
3957 DBMS_SQL.COLUMN_VALUE(v_cursor3, 1, l_count);
3958 DBMS_SQL.CLOSE_CURSOR(v_cursor3);
3959 -- DBMS_OUTPUT.Put_Line('count(action_object_code) in ieu_uwq_nonmedia_actions is ' || l_count);
3960 EXECUTE immediate ' select NAME , application_name'||
3961 ' from jtf_objects_vl '||
3962 ' where lower(OBJECT_CODE) = lower(:1) '
3963 INTO l_name,l_application_name
3964 USING p_object_code;
3965 if ( l_count = 0 ) then
3966 -- no data registered in ieu_uwq_nonmedia_actions
3967 -- a.1. get object_function from base view and call FND_FUNCTION.TEST
3968 -- a.2. if FND_FUNCTION.TEST run successfully, show successful message with function name, application name
3969 -- a.3. if FND_FUNCITON.TEST run failed,
3970 -- a.3.1 if object_code is 'TASK', test 'Launch TASK Manager',
3971 -- i.e. get object_function from jtf_objects_vl
3972 -- show log message IEU_UWQ_DEFTASKMAN_LAUNCH
3973 -- a.3.1.1 successfully, show success message
3974 -- a.3.1.2 failed, show error message IEU_UWQ_FUNCTION_NOT_ALLOWED
3975 -- a.3.2 not 'TASK' object_code, show error message IEU_UWQ_ALL_NO_SOURCE_DOC
3976
3977 -- log object_code name in base view is not registered in ieu_UWQ_NONMEDIA_ACTIONS
3978
3979 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_OBJ_FA');
3980 x_log.extend;
3981 x_log(x_log.last) := FND_MESSAGE.GET();
3982 CHECK_OBJECT_FUNCTION(x_return_status ,
3983 x_msg_count,
3984 x_msg_data ,
3985 p_object_code,
3986 p_task_source,
3987 x_problem_tasks ,
3988 x_log );
3989 ELSE -- ieu_action_object_code is registered in ieu_uwq_nonmedia_actions
3990 -- b.1. get the non_media function defined in ieu_uwq_maction_defs_b
3991 -- b.2. show success message with the non_media_function and application name
3992 EXECUTE immediate ' select action_proc'||
3993 ' from ieu_uwq_maction_defs_b a, ieu_uwq_nonmedia_actions b'||
3994 ' where a.maction_def_id = b.maction_def_id ' ||
3995 ' and lower(action_object_code) = lower(:1) ' ||
3996 ' and nvl(responsibility_id, -1) in (-1, :2) ' ||
3997 ' and lower(source_for_task_flag) = lower(:3) '
3998 INTO l_object_function
3999 USING p_object_code, l_responsibility_id, p_task_source;
4000 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_OBJ_SUC');
4001 x_log.extend;
4002 x_log(x_log.last) := FND_MESSAGE.GET();
4003 FND_MESSAGE.set_name('IEU', 'IEU_DIAG_LAU_LOG_OBJ_SUCCESS');
4004 FND_MESSAGE.set_token('FUNCTION', l_object_function);
4005 FND_MESSAGE.set_token('APPLICATION_NAME', l_application_name);
4006 x_log.extend;
4007 x_log(x_log.last) := FND_MESSAGE.GET();
4008 end if ; -- ieu_action_object_code is not registered in ieu_uwq_nonmedia_actions
4009
4010 end if;
4011 -- Standard call to get message count and if count is 1, get message info.
4012 FND_MSG_PUB.Count_And_Get(
4013 p_count => x_msg_count,
4014 p_data => l_msg_data
4015 );
4016
4017 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
4018 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
4019 x_msg_data := x_msg_data || ',' || l_msg_data;
4020 END LOOP;
4021
4022 EXCEPTION
4023
4024 WHEN FND_API.G_EXC_ERROR THEN
4025
4026 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
4027
4028 x_return_status := FND_API.G_RET_STS_ERROR;
4029 FND_MSG_PUB.Count_And_Get(
4030 p_count => x_msg_count,
4031 p_data => l_msg_data
4032 );
4033
4034
4035 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
4036 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
4037 x_msg_data := x_msg_data || ',' || l_msg_data;
4038 END LOOP;
4039
4040
4041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4042
4043 --DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
4044
4045
4046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4047 FND_MSG_PUB.Count_And_Get
4048 (
4049 p_count => x_msg_count,
4050 p_data => l_msg_data
4051 );
4052
4053 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
4054 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
4055 x_msg_data := x_msg_data || ',' || l_msg_data;
4056 END LOOP;
4057
4058
4059 WHEN OTHERS THEN
4060 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
4061 -- DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
4062
4063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4064
4065 FND_MSG_PUB.Count_And_Get (
4066 p_count => x_msg_count,
4067 p_data => l_msg_data
4068 );
4069
4070 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
4071 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
4072 x_msg_data := x_msg_data || ',' || l_msg_data;
4073 END LOOP;
4074
4075 end CHECK_TASK_LAUNCHING;
4076
4077
4078 END IEU_Diagnostics_PVT;