[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_EM
Source
1 PACKAGE BODY fnd_oam_em AS
2 /* $Header: AFOAMEMB.pls 120.1.12000000.4 2007/04/25 22:04:43 ssuprasa ship $ */
3
4
5 /* DONE FOR LEVEL 1 */
6 FUNCTION get_native_svcs return oam_em_srvcs_table_type IS
7 results oam_em_srvcs_table_type := oam_em_srvcs_table_type();
8 tmp_sts_code NUMBER :=0;
9 tmp_svc_message VARCHAR2(1024);
10 i NUMBER;
11
12 tmp_status INTEGER:=0;
13 tmp_target_procs INTEGER:=0;
14 tmp_actual_procs INTEGER:=0;
15
16 CURSOR l_srvc_arr_pair_csr IS
17 select q.user_concurrent_queue_name srvc_name,
18 q.concurrent_queue_id conc_queue_id,
19 a.application_id appl_id,
20 a.application_name srvc_app_name,
21 a.application_short_name srvc_app_short_name,
22 q.concurrent_queue_name srvc_short_name,
23 s.service_handle srvc_handle
24 from fnd_concurrent_queues_vl q, fnd_application_vl a,
25 fnd_cp_services s
26 where q.application_id = a.application_id
27 and s.service_id = q.manager_type;
28
29 BEGIN
30
31 i:=1;
32
33 FOR service_arr_pair IN l_srvc_arr_pair_csr LOOP
34 results.extend(1);
35
36 results(i) := oam_em_srvcs_type('','','','',0,0,0,'','');
37
38 results(i).name := service_arr_pair.srvc_name;
39 results(i).srvc_short_name := service_arr_pair.srvc_short_name;
40 results(i).srvc_handle := service_arr_pair.srvc_handle;
41
42 results(i).srvc_app_name := service_arr_pair.srvc_app_name;
43 results(i).srvc_app_short_name := service_arr_pair.srvc_app_short_name;
44 tmp_svc_message :='';
45 FND_OAM.get_svc_inst_status
46 (service_arr_pair.appl_id,
47 service_arr_pair.conc_queue_id,
48 tmp_target_procs,
49 tmp_actual_procs,
50 tmp_status,
51 results(i).message,
52 tmp_sts_code,
53 tmp_svc_message);
54
55 results(i).tgt_procs:=tmp_target_procs;
56 results(i).act_procs:=tmp_actual_procs;
57
58
59 IF tmp_status = 2 THEN
60 results(i).status:= 'DOWN';
61 ELSIF tmp_status = 0 THEN
62 results(i).status:= 'UP';
63 ELSIF tmp_status = 1 THEN
64 results(i).status := 'WARNING';
65 ELSIF tmp_status = 3 THEN
66 results(i).status := 'NOT_STARTED';
67 ELSE
68 results(i).status := 'NA';
69 END IF;
70 i := i+1;
71
72 END LOOP;
73 i:=i-1;
74 return results;
75 END get_native_svcs;
76
77
78
79
80 /* DONE FOR LEVEL 1*/
81 FUNCTION get_wf_agent_activity return oam_cursor_type IS
82 ret oam_cursor_type;
83
84 ready INTEGER:=0;
85 waiting INTEGER:=0;
86 processed INTEGER:=0;
87 expired INTEGER:=0;
88 undeliverable INTEGER:=0;
89 errored INTEGER:=0;
90
91 BEGIN
92
93 wf_queue.getcntmsgst('%',ready,waiting,processed,expired,undeliverable,errored);
94
95 OPEN ret FOR
96 SELECT to_char(ready),
97 to_char(waiting),
98 to_char(processed),
99 to_char(expired),
100 to_char(undeliverable),
101 to_char(errored)
102 FROM dual;
103 return ret;
104
105 END get_wf_agent_activity;
106
107
108
109
110 /* DONE FOR LEVEL 1*/
111 FUNCTION get_apps_sys_status return oam_cursor_type IS
112 ret oam_cursor_type;
113 BEGIN
114 fnd_oam_collection.refresh_app_sys_status;
115
116 OPEN ret FOR
117 SELECT foa.metric_short_name,
118 decode(fcq.user_concurrent_queue_name,NULL,'N/A',fcq.user_concurrent_queue_name),
119 decode(fcq.description,NULL,'N/A',fcq.description),
120 decode(foa.name,NULL,'N/A',foa.name),
121 to_char(foa.status_code),
122 decode(foa.node_name,NULL,'N/A',foa.node_name),
123 decode(fl.meaning,NULL,'N/A',fl.meaning) platform,
124 to_char(foa.last_update_date,'yyyy/MM/dd HH:mm:ss') last_update_date,
125 decode(foa.type,NULL,'N/A',foa.type),
126 decode(fcq.manager_type,NULL,'N/A',fcq.manager_type)
127
128 FROM fnd_oam_app_sys_status foa,
129 fnd_concurrent_queues_vl fcq,
130 FND_OAM_FNDNODES_VL fn,
131 fnd_lookups fl
132 WHERE foa.application_id = fcq.application_id (+)
133 AND foa.concurrent_queue_name = fcq.concurrent_queue_name (+)
134 AND upper(foa.node_name) = upper(fn.node_name (+))
135 AND nvl(fn.platform_code, '-x') = fl.lookup_code(+)
136 AND fl.lookup_type(+) = 'PLATFORM';
137
138 return ret;
139 END get_apps_sys_status;
140
141
142
143
144 /* DONE FOR LEVEL 1*/
145 FUNCTION get_conf_changed return oam_cursor_type IS
146 ret oam_cursor_type;
147 PATCHES VARCHAR2(256);
148 PROFILE_OPT VARCHAR2(256);
149 CONTEXT_FILES_EDITED VARCHAR2(256);
150 INVALID_OBJECTS VARCHAR2(256);
151 LAST_UPDATE_DATE VARCHAR2(256);
152 BEGIN
153
154 FOR pairs IN
155 (
156 SELECT fom.METRIC_SHORT_NAME metric_name,
157 fom.METRIC_VALUE value,
158 to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
159 FROM fnd_oam_metval fom
160 WHERE fom.METRIC_SHORT_NAME in
161 ('PATCHES','PROFILE_OPT','CONTEXT_FILES_EDITED','INVALID_OBJECTS')
162 )
163 LOOP
164 EXIT WHEN SQL%NOTFOUND;
165 LAST_UPDATE_DATE:=pairs.last_update;
166 IF pairs.metric_name='PATCHES' THEN
167 PATCHES:=pairs.value;
168 ELSIF pairs.metric_name='PROFILE_OPT' THEN
169 PROFILE_OPT:=pairs.value;
170 ELSIF pairs.metric_name='CONTEXT_FILES_EDITED' THEN
171 CONTEXT_FILES_EDITED:=pairs.value;
172 ELSIF pairs.metric_name='INVALID_OBJECTS' THEN
173 INVALID_OBJECTS:=pairs.value;
174 END IF;
175 END LOOP;
176
177 OPEN ret FOR
178 SELECT PATCHES,
179 PROFILE_OPT,
180 CONTEXT_FILES_EDITED,
181 INVALID_OBJECTS,
182 LAST_UPDATE_DATE
183 FROM dual;
184
185 return ret;
186 END get_conf_changed;
187
188
189 /* DONE FOR LEVEL 1*/
190 FUNCTION get_web_components_status return oam_cursor_type IS
191 ret oam_cursor_type;
192
193 PL_SQL_AGNT_GEN VARCHAR2(256);
194 SERVLET_AGNT_GEN VARCHAR2(256);
195 JSP_AGNT_GEN VARCHAR2(256);
196 JTF_GEN VARCHAR2(256);
197 DISCOVERER_GEN VARCHAR2(256);
198 PHP_GEN VARCHAR2(256);
199 TCF_GEN VARCHAR2(256);
200 LAST_UPDATE_DATE VARCHAR2(256);
201
202 BEGIN
203 FOR pairs IN
204 (
205 SELECT fom.METRIC_SHORT_NAME metric_name,
206 decode(to_char(fom.status_code),NULL,'NA','2','DOWN','0','UP','1','WARNING','3','NOT_STARTED','NA') value,
207 to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
208 FROM fnd_oam_metval fom
209 WHERE fom.GROUP_ID = 8
210 )
211 LOOP
212 EXIT WHEN SQL%NOTFOUND;
213 LAST_UPDATE_DATE:=pairs.last_update;
214 IF pairs.metric_name='PL_SQL_AGNT_GEN' THEN
215 PL_SQL_AGNT_GEN:=pairs.value;
216 ELSIF pairs.metric_name='SERVLET_AGNT_GEN' THEN
217 SERVLET_AGNT_GEN:=pairs.value;
218 ELSIF pairs.metric_name='JSP_AGNT_GEN' THEN
219 JSP_AGNT_GEN:=pairs.value;
220 ELSIF pairs.metric_name='JTF_GEN' THEN
221 JTF_GEN:=pairs.value;
222 ELSIF pairs.metric_name='DISCOVERER_GEN' THEN
223 DISCOVERER_GEN:=pairs.value;
224 ELSIF pairs.metric_name='PHP_GEN' THEN
225 PHP_GEN:=pairs.value;
226 ELSIF pairs.metric_name='TCF_GEN' THEN
227 TCF_GEN:=pairs.value;
228 END IF;
229 END LOOP;
230
231
232
233 OPEN ret FOR
234 SELECT PL_SQL_AGNT_GEN,
235 SERVLET_AGNT_GEN,
236 JSP_AGNT_GEN,
237 JTF_GEN,
238 DISCOVERER_GEN,
239 PHP_GEN,
240 TCF_GEN,
241 LAST_UPDATE_DATE
242 FROM dual;
243
244
245 return ret;
246 END get_web_components_status;
247
248
249 /* DONE FOR LEVEL 1*/
250 FUNCTION get_ebiz_int_sys_alerts return oam_cursor_type IS
251 ret oam_cursor_type;
252 CRIT_UNPR_EXCEP VARCHAR2(256);
253 CRIT_PR_EXCEP VARCHAR2(256);
254 CRIT_TOTAL_UNPR_EXCEP VARCHAR2(256);
255 OPEN_OCC VARCHAR2(256);
256 LAST_UPDATE_DATE VARCHAR2(256);
257 BEGIN
258
259 FOR pairs IN
260 (
261 SELECT fom.METRIC_SHORT_NAME metric_name,
262 fom.METRIC_VALUE value,
263 to_char(fom.LAST_UPDATE_DATE,'yyyy/MM/dd HH:mm:ss') last_update
264 FROM fnd_oam_metval fom
265 WHERE fom.METRIC_SHORT_NAME in
266 ('CRIT_UNPR_EXCEP','CRIT_PR_EXCEP','CRIT_TOTAL_UNPR_EXCEP','OPEN_OCC')
267 )
268 LOOP
269 EXIT WHEN SQL%NOTFOUND;
270 LAST_UPDATE_DATE:=pairs.last_update;
271 IF (pairs.metric_name='CRIT_UNPR_EXCEP') THEN
272 CRIT_UNPR_EXCEP:=pairs.value;
273 ELSIF (pairs.metric_name='CRIT_PR_EXCEP') THEN
274 CRIT_PR_EXCEP:=pairs.value;
275 ELSIF (pairs.metric_name='CRIT_TOTAL_UNPR_EXCEP') THEN
276 CRIT_TOTAL_UNPR_EXCEP:=pairs.value;
277 ELSIF (pairs.metric_name='OPEN_OCC') THEN
278 OPEN_OCC:=pairs.value;
279 END IF;
280 END LOOP;
281
282 OPEN ret FOR
283 SELECT CRIT_UNPR_EXCEP,
284 CRIT_PR_EXCEP,
285 CRIT_TOTAL_UNPR_EXCEP,
286 OPEN_OCC,
287 LAST_UPDATE_DATE
288 FROM dual;
289
290 return ret;
291
292 END get_ebiz_int_sys_alerts;
293 /*
294 FUNCTION get_apps_general_info return oam_cursor_type IS
295 ret oam_cursor_type;
296 oam_root_url VARCHAR2(1024);
297 apps_name VARCHAR2(1024);
298 apps_version VARCHAR2(1024);
299 multi_org VARCHAR2(1024);
300 multi_lingual VARCHAR2(1024);
301 multi_currency VARCHAR2(1024);
302 products_installed VARCHAR2(1024);
303 BEGIN
304
305 FOR p_r_pair IN
306 ( SELECT ( decode(substr(profile_option_value,length(profile_option_value)), '/', substr(profile_option_value,1,length(profile_option_value)-1), profile_option_value)
307 ||'/servlets/weboam/') p_v
308 FROM fnd_profile_options_vl ovl,
309 fnd_profile_option_values v
310 WHERE (v.level_id(+) = 10001)
311 AND (ovl.application_id = 0 or ovl.application_id = 178)
312 AND ovl.profile_option_name='APPS_FRAMEWORK_AGENT'
313 AND ovl.application_id = v.application_id(+)
314 AND ovl.profile_option_id=v.profile_option_id(+)
315 )
316 LOOP
317 oam_root_url := p_r_pair.p_v;
318 END LOOP;
319
320 FOR p_r_pair IN
321 ( select APPLICATIONS_SYSTEM_NAME apps_name,
322 RELEASE_NAME version,
323 MULTI_ORG_FLAG m_org,
324 decode(MULTI_LINGUAL_FLAG,null,' ',MULTI_LINGUAL_FLAG) m_li,
325 MULTI_CURRENCY_FLAG m_cur
326 from fnd_product_groups
327 )
328 LOOP
329 apps_name:=p_r_pair.apps_name;
330 apps_version := p_r_pair.version;
331 multi_org:=p_r_pair.m_org;
332 multi_lingual := p_r_pair.m_li;
333 multi_currency := p_r_pair.m_cur;
334 END LOOP;
335
336
337 FOR p_r_pair IN
338 ( select to_char(count(*)) ac
339 from fnd_oracle_userid o,
340 fnd_application a,
341 fnd_product_installations fpi
342 where fpi.application_id = a.application_id
343 and fpi.oracle_id = o.oracle_id and status <>'N'
344 )
345 LOOP
346 products_installed := p_r_pair.ac;
347 END LOOP;
348
349
350
351
352 -- open the cursor to return
353 OPEN ret FOR
354 SELECT fnd_oam_em.get_apps_level apps_level,
355 oam_root_url,
356 apps_name,
357 apps_version,
358 multi_org,
359 multi_lingual,
360 multi_currency,
361 products_installed
362 FROM dual;
363
364 return ret;
365 END get_apps_general_info;
366
367
368 FUNCTION get_apps_level return CHAR IS
369 ret CHAR:='1';
370 BEGIN
371 return ret;
372 END get_apps_level;
373
374
375 */
376
377
378 /*
379 FUNCTION get_ebiz_activity return oam_cursor_type IS
380 ret oam_cursor_type;
381 ACTIVE_USERS VARCHAR2(256);
382 SERVICE_PROCS VARCHAR2(256);
383 RUNNING_REQ VARCHAR2(256);
384
385 BEGIN
386 SELECT to_char(count(F.login_id))
387 INTO ACTIVE_USERS
388 FROM fnd_login_resp_forms F,
389 gv$session S
390 WHERE F.AUDSID = S.AUDSID;
391
392
393 SELECT to_char(count(*))
394 INTO SERVICE_PROCS
395 FROM fnd_concurrent_processes
396 WHERE process_status_code in ('R','A','P','C','M','D','T');
397
398 SELECT to_char(count(*))
399 INTO RUNNING_REQ
400 FROM fnd_concurrent_requests
401 WHERE phase_code = 'R';
402
403 OPEN ret FOR
404 SELECT ACTIVE_USERS,
405 SERVICE_PROCS,
406 RUNNING_REQ
407 FROM dual;
408
409 return ret;
410
411 END get_ebiz_activity;
412
413 */
414
415
416
417
418
419 /*
420 FUNCTION get_wf_notification return oam_cursor_type IS
421 ret oam_cursor_type;
422 errored_ct INTEGER:=0;
423 unsent_ct INTEGER:=0;
424 BEGIN
425
426 FOR pair_1 IN
427 ( SELECT count(*) p_v
428 FROM wf_notifications
429 WHERE mail_status in ('ERRORED','FAILED')
430 )
431 LOOP
432 errored_ct := pair_1.p_v;
433 END LOOP;
434
435 FOR pair_2 IN
436 ( SELECT count(*) p_v
437 FROM wf_notifications
438 WHERE mail_status in ('UNSENT')
439 )
440 LOOP
441 unsent_ct := pair_2.p_v;
442 END LOOP;
443
444 -- open the cursor to return
445 OPEN ret FOR
446 SELECT to_char(errored_ct),to_char(unsent_ct)
447 FROM dual;
448
449 return ret;
450 END get_wf_notification;
451
452 */
453 /*
454 FUNCTION get_ebiz_status return oam_cursor_type IS
455 ret oam_cursor_type;
456 status INTEGER:=1;
457 BEGIN
458
459 FOR pair_1 IN
460 ( SELECT foa.status_code sta
461 FROM fnd_oam_app_sys_status foa,
462 fnd_concurrent_queues_vl fcq,
463 FND_OAM_FNDNODES_VL fn,
464 fnd_lookups fl
465 WHERE foa.application_id = fcq.application_id (+)
466 AND foa.concurrent_queue_name = fcq.concurrent_queue_name (+)
467 AND upper(foa.node_name) = upper(fn.node_name (+))
468 AND nvl(fn.platform_code, '-x') = fl.lookup_code(+)
469 AND fl.lookup_type(+) = 'PLATFORM'
470 AND ( foa.metric_short_name='WEB_SERVER_OVERALL'
471 OR foa.metric_short_name='ADMIN_SERVER_OVERALL'
472 OR foa.metric_short_name='DATA_SERVER_OVERALL'
473 OR foa.metric_short_name='FORMS_SERVER_OVERALL'
474 OR foa.metric_short_name='CP_SERVER_OVERALL'
475 )
476 )
477 LOOP
478 EXIT WHEN SQL%NOTFOUND;
479 IF (pair_1.sta>0) THEN
480 status:=0;
481 END IF;
482 END LOOP;
483
484 -- open the cursor to return
485 OPEN ret FOR
486 SELECT to_char(status)
487 FROM dual;
488
489 return ret;
490 END get_ebiz_status;
491 */
492
493 /*
494 FUNCTION get_web_user_last_hour return oam_cursor_type IS
495 ret oam_cursor_type;
496 BEGIN
497 OPEN ret FOR
498 SELECT to_char(count(*))
499 FROM ( select distinct user_id from icx_sessions
500 where last_connect >= sysdate - (1/24)
501 -- and (function_type is null or function_type != 'FORM')
502 );
503 return ret;
504 END get_web_user_last_hour;
505 */
506
507 /*
508 FUNCTION get_active_requests_by_app return oam_cursor_type IS
509 ret oam_cursor_type;
510 BEGIN
511 OPEN ret FOR
512 select asn,an,to_char(sum(nac)),to_char(sum(ac)) from
513 (
514 SELECT a.application_short_name asn, a.application_name an, COUNT(*) nac, 0 ac
515 FROM fnd_concurrent_requests r, fnd_application_vl a
516 WHERE r.program_application_id = a.application_id
517 AND phase_code in ('P', 'R')
518 AND r.resubmit_time is null
519 GROUP BY a.application_short_name,a.application_name
520 UNION ALL
521 SELECT a.application_short_name asn, a.application_name an, 0 nac, COUNT(*) ac
522 FROM fnd_concurrent_requests r, fnd_application_vl a
523 WHERE r.program_application_id = a.application_id
524 AND phase_code in ('P', 'R')
525 AND r.resubmit_time is not null
526 GROUP BY a.application_short_name,a.application_name
527 )
528 group by asn,an;
529
530 return ret;
531 END get_active_requests_by_app;
532 */
533 /*
534 FUNCTION get_hourly_completed_requests return oam_cursor_type IS
535 ret oam_cursor_type;
536 BEGIN
537 OPEN ret FOR
538 SELECT to_char(a.a), to_char(b.b), to_char(c.c),
539 a.a/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100,
540 b.b/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100,
541 c.c/decode(a.a+b.b+c.c, 0, 1, a.a+b.b+c.c)*100
542 FROM
543 (
544 SELECT COUNT(*) a
545 FROM fnd_concurrent_requests
546 WHERE phase_code = 'C' AND status_code = 'C'
547 AND actual_completion_date between (sysdate - 1/24) and sysdate
548 ) a,
549 (
550 SELECT COUNT(*) b
551 FROM fnd_concurrent_requests
552 WHERE phase_code = 'C' AND status_code = 'G'
553 AND actual_completion_date between (sysdate - 1/24) and sysdate
554 ) b,
555 (
556 SELECT COUNT(*) c
557 FROM fnd_concurrent_requests
558 WHERE phase_code = 'C' AND status_code = 'E'
559 AND actual_completion_date between (sysdate - 1/24) and sysdate
560 ) c ;
561
562 return ret;
563 END get_hourly_completed_requests;
564
565 */
566 /*
567 FUNCTION get_apps_framework_agent return oam_cursor_type IS
568 ret oam_cursor_type;
569 frame_agent VARCHAR2(1024);
570
571 BEGIN
572
573 FOR p_r_pair IN
574 ( SELECT (profile_option_value||'/servlets/weboam/') p_v
575 FROM fnd_profile_options_vl ovl,
576 fnd_profile_option_values v
577 WHERE (v.level_id(+) = 10001)
578 AND (ovl.application_id = 0 or ovl.application_id = 178)
579 AND ovl.profile_option_name='APPS_FRAMEWORK_AGENT'
580 AND ovl.application_id = v.application_id(+)
581 AND ovl.profile_option_id=v.profile_option_id(+)
582 )
583 LOOP
584 frame_agent := p_r_pair.p_v;
585 END LOOP;
586
587
588 -- open the cursor to return
589 OPEN ret FOR
590 SELECT frame_agent
591 FROM dual;
592
593 return ret;
594 END get_apps_framework_agent;
595
596 */
597 /* FUNCTION get_rqsts_stats return oam_cursor_type IS
598
599 result oam_cursor_type;
600 pending_scheduled_ct INTEGER:=0;
601 pending_normal_ct INTEGER:=0;
602 pending_standby_ct INTEGER:=0;
603 no_manager_ct INTEGER:=0;
604 on_hold_ct INTEGER:=0;
605 running_ct INTEGER:=0;
606
607 BEGIN
608 FOR p_r_pair_1 IN
609 ( SELECT count(*) prc, status_code
610 FROM fnd_concurrent_requests
611 WHERE status_code IN ('I', 'Q' )
612 AND requested_start_date <= sysdate
613 AND phase_code = 'P'
614 AND hold_flag = 'N'
615 GROUP BY status_code
616 )
617 LOOP
618 IF p_r_pair_1.status_code = 'I' THEN
619 pending_normal_ct := p_r_pair_1.prc;
620 ELSIF p_r_pair_1.status_code = 'Q' THEN
621 pending_standby_ct := p_r_pair_1.prc;
622 END IF;
623 END LOOP;
624
625
626 FOR p_r_pair_2 IN
627 ( SELECT count(*) prc
628 FROM fnd_concurrent_requests
629 WHERE phase_code = 'P'
630 AND hold_flag = 'N'
631 AND ( (status_code = 'P' )
632 OR ( status_code IN( 'I', 'Q')
633 AND requested_start_date > sysdate
634 )
635 )
636 )
637 LOOP
638 pending_scheduled_ct := p_r_pair_2.prc;
639 END LOOP;
640
641 FOR p_r_pair_3 IN
642 ( SELECT count(*) prc
643 FROM fnd_concurrent_requests
644 WHERE status_code IN( 'R', 'T')
645 )
646 LOOP
647 running_ct := p_r_pair_3.prc;
648 END LOOP;
649
650 FOR p_r_pair_4 IN
651 ( SELECT count(*) prc
652 FROM fnd_concurrent_requests
653 WHERE phase_code = 'P' AND status_code = 'M'
654 )
655 LOOP
656 no_manager_ct := p_r_pair_4.prc;
657 END LOOP;
658
659 FOR p_r_pair_5 IN
660 ( SELECT count(*) prc
661 FROM fnd_concurrent_requests
662 WHERE phase_code = 'P' AND hold_flag = 'Y'
663 )
664 LOOP
665 on_hold_ct := p_r_pair_5.prc;
666 END LOOP;
667
668 -- open the cursor to return
669 OPEN result FOR
670 SELECT to_char(pending_normal_ct),
671 to_char(pending_standby_ct),
672 to_char(pending_scheduled_ct),
673 to_char(no_manager_ct),
674 to_char(on_hold_ct),
675 to_char(running_ct)
676 FROM dual;
677
678 return result;
679
680 END get_rqsts_stats;
681
682 */
683
684 /*
685 FUNCTION get_procs_rqsts_per_conc return oam_em_prpc_table_type IS
686 results oam_em_prpc_table_type := oam_em_prpc_table_type();
687 i NUMBER:=0;
688 BEGIN
689 i:=1;
690 FOR record_pair IN
691 ( SELECT user_concurrent_queue_name r_n,
692 fnd_conc_request_pkg.running_requests(concurrent_queue_name,
693 application_id) r_c,
694 (select count(*)
695 from fnd_concurrent_processes cp
696 where cp.queue_application_id = cq.application_id
697 and cp.concurrent_queue_id = cq.concurrent_queue_id
698 and (cp.process_status_code in ('C','M')
699 or (cp.process_status_code in ('A', 'D', 'T')
700 and exists (select 1 from gv$session
701 where cp.session_id = audsid)
702 )
703 )
704 ) a_p,
705 fnd_oam_em.get_pend_rqsts('I',application_id,concurrent_queue_id) n_c,
706 fnd_oam_em.get_pend_rqsts('Q',application_id,concurrent_queue_id) s_c
707
708 FROM fnd_concurrent_queues_vl CQ,
709 WHERE manager_type = 1
710 ORDER BY decode(CQ.control_code, 'X',2,'E',2,1), user_concurrent_queue_name
711 )
712 LOOP
713 EXIT WHEN SQL%NOTFOUND;
714 results.extend(1);
715
716 results(i) := oam_em_prpc_type(record_pair.r_n,
717 record_pair.a_p,
718 record_pair.r_c,
719 record_pair.n_c,
720 record_pair.s_c
721 );
722
723 i := i+1;
724 END LOOP;
725
726 i:=i-1;
727
728
729 return results;
730
731 END get_procs_rqsts_per_conc;
732 */
733
734
735 /*
736 FUNCTION get_pend_rqsts(status_code CHAR, app_id NUMBER, mgr_id NUMBER)
737 return NUMBER IS
738 ret NUMBER:=0;
739
740 TYPE cursor_type IS REF CURSOR;
741 data_cursor cursor_type;
742 sql_stmt VARCHAR2(1024);
743
744 BEGIN
745 sql_stmt := 'SELECT count(R.Request_ID) rc '||
746 ' FROM Fnd_concurrent_worker_requests '||
747 ' WHERE phase_code = ''P'''||
748 ' AND status_code = :status_code'||
749 ' AND queue_application_id = :app_id'||
750 ' AND concurrent_queue_id = :mgr_id'||
751 ' AND hold_flag != ''Y'''||
752 ' AND CWR.requested_start_date <= sysdate';
753
754
755 OPEN data_cursor FOR sql_stmt
756 USING status_code, app_id, mgr_id;
757 LOOP
758 FETCH data_cursor INTO ret;
759 EXIT WHEN data_cursor%NOTFOUND;
760 END LOOP;
761 CLOSE data_cursor;
762
763
764 return ret;
765 END get_pend_rqsts;
766 */
767 /*
768 FUNCTION get_workitem_metrics return oam_cursor_type IS
769
770 ret oam_cursor_type;
771 active_count INTEGER:=0;
772 deferred_count INTEGER:=0;
773 suspended_count INTEGER:=0;
774 errored_count INTEGER:=0;
775 BEGIN
776
777 FOR pairs IN(
778 SELECT count(distinct(item_key)) ac
779 FROM wf_items
780 WHERE end_date is null)
781 LOOP
782 EXIT WHEN SQL%NOTFOUND;
783 active_count := pairs.ac;
784 END LOOP;
785
786
787 FOR pairs IN(
788 SELECT count(*) ac
789 FROM wf_item_activity_statuses
790 WHERE activity_status = 'DEFERRED')
791 LOOP
792 EXIT WHEN SQL%NOTFOUND;
793 deferred_count := pairs.ac;
794 END LOOP;
795
796
797 FOR pairs IN(
798 SELECT count(*) ac
799 FROM wf_item_activity_statuses
800 WHERE activity_status = 'SUSPEND')
801 LOOP
802 EXIT WHEN SQL%NOTFOUND;
803 suspended_count := pairs.ac;
804 END LOOP;
805
806
807
808 FOR pairs IN(
809 SELECT count(distinct(item_key)) ac
810 FROM wf_item_activity_statuses
811 WHERE activity_status = 'ERROR')
812 LOOP
813 EXIT WHEN SQL%NOTFOUND;
814 errored_count := pairs.ac;
815 END LOOP;
816
817 OPEN ret FOR
818 SELECT to_char(active_count),
819 to_char(deferred_count),
820 to_char(suspended_count),
821 to_char(errored_count)
822 FROM dual;
823 return ret;
824
825 END get_workitem_metrics;
826 */
827 /* FUNCTION get_block_icm_crm return oam_cursor_type IS
828 ret oam_cursor_type;
829
830 BEGIN
831
832 OPEN ret FOR
833 SELECT hs.audsid, hs.program, hs.osuser, hs.process,
834 hs.machine, hs.terminal,
835 hl.ctime,
836 hl.type, to_char(hl.id1), to_char(hl.id2),
837 DECODE(hl.lmode, 1, 'NULL', 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',
838 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', '?')
839 FROM fnd_concurrent_processes p, v$session ws, v$lock wl, v$lock hl ,
840 v$session hs
841 WHERE p.queue_application_id = 0 AND p.concurrent_queue_id IN (1,4)
842 AND p.session_id = ws.audsid AND ws.lockwait IS NOT NULL
843 AND ws.lockwait = wl.kaddr AND wl.id1 = hl.id1 AND wl.id2 = hl.id2
844 AND hl.sid = hs.sid AND hl.request = 0;
845 return ret;
846
847 END get_block_icm_crm;
848 */
849
850 /*
851 FUNCTION get_apps_sys_metrics return oam_cursor_type IS
852 ret oam_cursor_type;
853 BEGIN
854
855 OPEN ret FOR
856 SELECT
857
858 fsm.metric_short_name,
859 fsm.metric_display_name,
860 fsm.metric_value,
861 decode(fsm.description,NULL,'N/A',fsm.description),
862 to_char(decode(fsm.status_code,NULL,-1,fsm.status_code)),
863 to_char(fsm.last_update_date,'yyyy/MM/dd HH:mm:ss') last_update_date,
864 to_char(fsm.sequence),
865 to_char(fsm.group_id),
866 to_char(fsmg.sequence),
867 fsmg.metric_group_display_name
868
869
870 FROM fnd_oam_metval_vl fsm,
871 fnd_oam_met_grps_vl fsmg,
872 FND_OAM_FNDNODES_VL fn
873 WHERE fsm.group_id = fsmg.metric_group_id
874 AND upper(fsm.node_name) = upper(fn.node_name (+))
875 ORDER BY fsmg.sequence, fsm.sequence;
876
877
878 return ret;
879 END get_apps_sys_metrics;
880 */
881
882
883 FUNCTION get_icm_status return NUMBER IS
884 PRAGMA AUTONOMOUS_TRANSACTION;
885 appId number;
886 mgrId number;
887 target number;
888 active number;
889 pmon varchar2(30);
890 stat number;
891 retu number(1);
892 BEGIN
893 appId :=0;
894 mgrId :=1;
895
896 fnd_concurrent.get_manager_status(APPLID=>appId, MANAGERID=>mgrId, targetp =>target, activep=>active, pmon_method =>pmon, callstat =>stat);
897 --dbms_output.put_line('stat=' || stat);
898 --dbms_output.put_line('active=' || active);
899 --dbms_output.put_line('target=' || target);
900 --dbms_output.put_line('pmon=' || pmon);
901
902 if((stat=0) AND (active > 0)) THEN
903 retu := 1;
904 else
905 retu := 0;
906 end if;
907
908 --dbms_output.put_line('retu=' || retu);
909
910 return retu;
911 END get_icm_status;
912
913 /*
914 FUNCTION get_icm_statusV return varchar2 IS
915 appId number(10);
916 mgrId number(10);
917 target number(10);
918 active number(10);
919 pmon varchar2(30);
920 stat number(10);
921 retu varchar2(10);
922 BEGIN
923 appId :=0;
924 mgrId :=0;
925
926 fnd_concurrent.get_manager_status(APPLID=>appId, MANAGERID=>mgrId, targetp =>target, activep=>active, pmon_method =>pmon, callstat =>stat);
927 --dbms_output.put_line('stat=' || stat);
928 --dbms_output.put_line('active=' || active);
929 --dbms_output.put_line('target=' || target);
930 --dbms_output.put_line('pmon=' || pmon);
931
932 if((stat=0) AND (active > 0)) THEN
933 retu := '1';
934 else
935 retu := '0';
936 end if;
937
938 --dbms_output.put_line('retu=' || retu);
939
940 return 'active=' || active || ' stat=' || stat || 'pMon=' || pmon || ' target=' || target ||'retu=' || retu;
941 END get_icm_statusV;
942 */
943
944
945 END fnd_oam_em;