1 PACKAGE BODY fnd_oam_cpcharts_col AS
2 /* $Header: AFOAMCCB.pls 120.0 2005/11/18 15:37:25 appldev noship $ */
3 --
4 -- Name
5 -- refresh_all
6 --
7 -- Purpose
8 -- Computes the values for all the chart metrics and updates the
9 -- fnd_oam_chart_metrics table.
10 --
11 -- Input Arguments
12 --
13 -- Output Arguments
14 -- errbuf - for any error message
15 -- retcode - 0 for success, 1 for success with warnings, 2 for error
16 --
17 -- Notes:
18 --
19 --
20 PROCEDURE refresh_all (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2)
21 IS
22
23 BEGIN
24 fnd_file.put_line(fnd_file.log, 'OAM Chart Collection, Refreshing All ...');
25
26 fnd_file.put_line(fnd_file.log, 'Refreshing concurrent requests status ...');
27 refresh_req_status;
28 fnd_file.new_line(fnd_file.log, 1);
29
30 fnd_file.put_line(fnd_file.log, 'Refreshing completed requests status ...');
31 refresh_completed_req_status;
32 fnd_file.new_line(fnd_file.log, 1);
33
34 fnd_file.put_line(fnd_file.log, 'Refreshing pending requests status ...');
35 refresh_pending_req_status;
36 fnd_file.new_line(fnd_file.log, 1);
37
38 fnd_file.put_line(fnd_file.log, 'Refreshing running requests duration ...');
39 refresh_running_req_duration;
40 fnd_file.new_line(fnd_file.log, 1);
41
42 fnd_file.put_line(fnd_file.log, 'Refreshing running request counts grouped by user ...');
43 refresh_running_req_user;
44 fnd_file.new_line(fnd_file.log, 1);
45
46 fnd_file.put_line(fnd_file.log, 'Refreshing running request counts grouped by application ...');
47 refresh_running_req_app;
48 fnd_file.new_line(fnd_file.log, 1);
49
50 fnd_file.put_line(fnd_file.log, 'Refreshing running request counts grouped by responsibility ...');
51 refresh_running_req_resp;
52 fnd_file.new_line(fnd_file.log, 1);
53
54 fnd_file.put_line(fnd_file.log, 'Refreshing pending request counts grouped by user ...');
55 refresh_pending_req_user;
56 fnd_file.new_line(fnd_file.log, 1);
57
58 fnd_file.put_line(fnd_file.log, 'Refreshing pending request counts grouped by application ...');
59 refresh_pending_req_app;
60 fnd_file.new_line(fnd_file.log, 1);
61
62 fnd_file.put_line(fnd_file.log, 'Refreshing pending request counts grouped by responsibility ...');
63 refresh_pending_req_resp;
64 fnd_file.new_line(fnd_file.log, 1);
65
66 fnd_file.put_line(fnd_file.log, 'Refreshing pending request counts grouped by manager ...');
67 refresh_pend_req_mgr;
68 fnd_file.new_line(fnd_file.log, 1);
69
70 fnd_file.put_line(fnd_file.log, 'Refreshing running request and process counts grouped by responsibility ...');
71 refresh_run_req_process_mgr;
72 fnd_file.new_line(fnd_file.log, 1);
73
74 fnd_file.put_line(fnd_file.log, 'Refreshing the Concurrent requests statististics by user ...');
75 refresh_req_stats_user;
76 fnd_file.new_line(fnd_file.log, 1);
77
78 fnd_file.put_line(fnd_file.log, 'Refreshing the Concurrent requests statististics by program ...');
79 refresh_req_stats_program;
80 fnd_file.new_line(fnd_file.log, 1);
81
82 fnd_file.put_line(fnd_file.log, 'Done refreshing All ...');
83
84 EXCEPTION
85 when others then
86 retcode := '2';
87 errbuf := SQLERRM;
88 END refresh_all;
89
90
91 --
92 --
93 -- Gets the current user id
94 --
95 FUNCTION get_user_id RETURN number
96 IS
97 v_userid number;
98
99 BEGIN
100 select fnd_global.user_id into v_userid from dual;
101 if (v_userid < 0 or v_userid is null) then
102 v_userid := 0; -- default
103 end if;
104
105 return v_userid;
106 EXCEPTION
107 when others then
108 v_userid := 0;
109 return v_userid;
110 END get_user_id;
111
112 -- Name
113 -- insert_metric_internal
114 -- Purpose
115 -- This procedure is for internal use only!
116 -- This procedure will insert a row in fnd_oam_chart_metrics for the given
117 -- metric name.
118 --
119 -- Input Arguments
120 -- p_metric_name varchar2
121 -- p_context varchar2
122 -- p_value number
123 --
124 -- Output Arguments
125 --
126 -- Input/Output Arguments
127 --
128 -- Notes:
129 -- This is an internal convenience method only for OAM chart data collection
130 --
131 PROCEDURE insert_metric_internal (
132 p_metric_name in varchar2,
133 p_context in varchar2,
134 p_value in number)
135 IS
136 v_userid number;
137 BEGIN
138 v_userid := get_user_id;
139
140 -- insert the data
141 insert into fnd_oam_chart_metrics (metric_short_name, metric_context,
142 value, last_updated_by, last_update_date,
143 last_update_login, created_by, creation_date)
144 values (p_metric_name, p_context, p_value,
145 v_userid, sysdate, v_userid, 0, sysdate);
146
147 END insert_metric_internal;
148
149 -- Name
150 -- update_metric_internal
151 -- Purpose
152 -- This procedure is for internal use only!
153 -- This procedure will update a row in fnd_oam_chart_metrics for the given
154 -- metric name. If it does not exist, then insert.
155 --
156 -- Input Arguments
157 -- p_metric_name varchar2
158 -- p_context varchar2
159 -- p_value number
160 --
161 -- Output Arguments
162 --
163 -- Input/Output Arguments
164 --
165 -- Notes:
166 -- This is an internal convenience method only for OAM chart data collection
167 --
168
169 PROCEDURE update_metric_internal (
170 p_metric_name in varchar2,
171 p_context in varchar2,
172 p_value in number)
173 IS
174 v_userid number;
175 name varchar2(30);
176 BEGIN
177 v_userid := get_user_id;
178
179 select metric_short_name into name
180 from fnd_oam_chart_metrics
181 where metric_short_name = p_metric_name
182 and metric_context = p_context;
183
184 if(name is not null) then
185 update fnd_oam_chart_metrics
186 set value = p_value,
187 last_updated_by = v_userid,
188 last_update_date = sysdate,
189 last_update_login = v_userid
190 where
191 metric_short_name = p_metric_name
192 and metric_context = p_context;
193 end if;
194
195 exception
196 when no_data_found then
197 -- insert the data
198 insert into fnd_oam_chart_metrics (metric_short_name, metric_context,
199 value, last_updated_by, last_update_date,
200 last_update_login, created_by, creation_date)
201 values (p_metric_name, p_context, p_value,
202 v_userid, sysdate, v_userid, 0, sysdate);
203
204 END update_metric_internal;
205
206 -- Name
207 -- delete_metric_internal
208 -- Purpose
209 -- This procedure is for internal use only!
210 -- This procedure will delete the metric entry if it exists in fnd_oam_chart_metrics for the given
211 -- metric name regardless of the context.
212 --
213 -- Input Arguments
214 -- p_metric_name varchar2
215 --
216 -- Output Arguments
217 --
218 -- Input/Output Arguments
219 --
220 -- Notes:
221 -- This is an internal convenience method only for OAM chart data collection
222 --
223 PROCEDURE delete_metric_internal (
224 p_metric_name in varchar2)
225 IS
226 BEGIN
227
228 -- delete the entry
229 delete from fnd_oam_chart_metrics
230 where metric_short_name = p_metric_name;
231
232 END delete_metric_internal;
233
234
235 --
236 -- Name
237 -- refresh_req_status
238 --
239 -- Purpose
240 -- Computes the metric values for the all request status
241 --
242 PROCEDURE refresh_req_status
243 IS
244
245 BEGIN
246
247 update_req_status_metric('REQ_RUNNING');
248 update_req_status_metric('REQ_PENDING');
249 update_req_status_metric('REQ_COMPLETED');
250 update_req_status_metric('REQ_WAITING_ON_LOCK');
251 update_req_status_metric('REQ_INACTIVE');
252
253 END refresh_req_status;
254 --
255 -- Name
256 -- update_req_status_metric
257 --
258 -- Purpose
259 -- compute the metric value for one request status
260 --
261 PROCEDURE update_req_status_metric(p_metric_name in varchar2)
262 IS
263 ct_running number;
264 ct_pending number;
265 ct_completed number;
266 ct_waiting_on_lock number;
267 ct_inactive number;
268 BEGIN
269
270 if(p_metric_name = 'REQ_RUNNING') then
271 select count(*) into ct_running
272 from fnd_concurrent_requests
273 where status_code = 'R';
274
275 -- Update the number of running requests, use 0 for the metric_context
276 update_metric_internal(p_metric_name, '0', ct_running);
277 end if;
278
279 if(p_metric_name = 'REQ_PENDING') then
280 select count(rv.Request_ID) into ct_pending
281 from Fnd_amp_requests_v rv,
282 Fnd_lookups l
283 Where rv.phase_code = 'P'
284 and l.meaning = rv.phase
285 and l.lookup_code = 'P'
286 and l.lookup_type = 'CP_PHASE_CODE';
287
288 /*query from old request java code:
289 select count(distinct(R.Request_ID)) into ct_pending
290 from Fnd_Concurrent_Programs_vl CP,
291 Fnd_User U,
292 Fnd_Concurrent_Requests R,
293 Fnd_Responsibility_Tl RES,
294 Fnd_Application A,
295 Fnd_amp_requests_v rv,
296 Fnd_lookups l
297 Where rv.phase_code = 'P'
298 and l.meaning = rv.phase
299 and l.lookup_code = 'P'
300 and l.lookup_type = 'CP_PHASE_CODE'
301 And CP.Application_ID = rv.Program_Application_ID
302 And CP.Concurrent_Program_ID = rv.Concurrent_Program_ID
303 and R.request_id = rv.request_id
304 and rv.Program_Application_ID = R.Program_Application_ID
305 And rv.Concurrent_Program_ID = R.Concurrent_Program_ID
306 And A.Application_ID = rv.Program_Application_ID
307 And U.User_ID = R.Requested_By
308 And RES.application_id = R.responsibility_application_id
309 AND RES.language(+)=USERENV('LANG')
310 And RES.responsibility_id = R.responsibility_id;
311 */
312 -- update the number of pending requests, use 0 for the metric_context
313 update_metric_internal(p_metric_name, '0', ct_pending);
314 end if;
315
316 if(p_metric_name = 'REQ_COMPLETED') then
317 select count(*) into ct_completed
318 from fnd_concurrent_requests
319 where phase_code = 'C' and (sysdate - actual_completion_date)*1440 <= 60;
320
321 -- update the number of COMPLETED requests in the last 1 hour, use 0 for the metric_context
322 update_metric_internal(p_metric_name, '0', ct_completed);
323 end if;
324
325 if(p_metric_name = 'REQ_WAITING_ON_LOCK') then
326 select count(*) into ct_waiting_on_lock
327 from fnd_concurrent_requests r, GV$SESSION WS
328 where r.phase_code = 'R'
329 and r.oracle_session_id = WS.AUDSID
330 and WS.LOCKWAIT IS NOT NULL;
331
332 -- update the number of requests that are waiting on locks, use 0 for the metric_context
333 update_metric_internal(p_metric_name, '0', ct_waiting_on_lock);
334 end if;
335
336 if(p_metric_name = 'REQ_INACTIVE') then
337 select count(rv.Request_ID) into ct_inactive
338 From Fnd_amp_requests_v rv,
339 Fnd_lookups l
340 Where rv.phase_code = 'P'
341 and l.meaning = rv.phase
342 and l.lookup_code = 'I'
343 and l.lookup_type = 'CP_PHASE_CODE';
344
345 /* query from old request java code:
346 select count(distinct(R.Request_ID)) into ct_inactive
347 From Fnd_Concurrent_Programs_vl CP,
348 Fnd_User U,
349 Fnd_Concurrent_Requests R,
350 Fnd_Responsibility_Tl RES,
351 Fnd_Application A,
352 Fnd_amp_requests_v rv,
353 Fnd_lookups l
354 Where rv.phase_code = 'P'
355 and l.meaning = rv.phase
356 and l.lookup_code = 'I'
357 and l.lookup_type = 'CP_PHASE_CODE'
358 And CP.Application_ID = rv.Program_Application_ID
359 And CP.Concurrent_Program_ID = rv.Concurrent_Program_ID
360 and R.request_id = rv.request_id
361 and rv.Program_Application_ID = R.Program_Application_ID
362 And rv.Concurrent_Program_ID = R.Concurrent_Program_ID
363 And A.Application_ID = rv.Program_Application_ID
364 And U.User_ID = R.Requested_By
365 And RES.application_id = R.responsibility_application_id
366 AND RES.language(+)=USERENV('LANG')
367 And RES.responsibility_id = R.responsibility_id;
368 */
369 -- update the number of inactive requests, use 0 for the metric_context
370 update_metric_internal(p_metric_name, '0', ct_inactive);
371 end if;
372
373 commit;
374 EXCEPTION
375 when others then
376 rollback;
377 raise;
378 END update_req_status_metric;
379
380 --
381 -- Name
382 -- refresh_completed_req_status
383 --
384 -- Purpose
385 -- Computes the metric values for the completed request status for the last hour
386 --
387 -- Input Arguments
388 --
389 -- Output Arguments
390 --
391 -- Notes:
392 --
393 --
394 PROCEDURE refresh_completed_req_status
395 IS
396 cursor req_c is
397 SELECT count(*) count, status_code
398 FROM fnd_concurrent_requests
399 WHERE status_code IN ('C', 'G', 'E', 'X' )
400 AND ((sysdate - actual_completion_date) * (1440)) <= 60
401 GROUP BY status_code;
402
403 ct_error number := 0;
404 ct_succ number := 0;
405 ct_term number := 0;
406 ct_warn number := 0;
407
408 BEGIN
409
410 for req in req_c loop
411 --error
412 if req.status_code = 'E' then
413 ct_error := req.count;
414 end if;
415
416 if req.status_code = 'C' then
417 ct_succ := req.count;
418 end if;
419
420 if req.status_code = 'X' then
421 ct_term := req.count;
422 end if;
423
424 if req.status_code = 'G' then
425 ct_warn := req.count;
426 end if;
427 end loop;
428
429 -- update the number of completed requests with error, use 0 for the metric_context
430 update_metric_internal('COMPLETED_REQ_ERROR', '0', ct_error);
431
432 -- update the number of completed requests with success, use 0 for the metric_context
433 update_metric_internal('COMPLETED_REQ_SUCCESSFUL', '0', ct_succ);
434
435 -- update the number of completed requests with termination, use 0 for the metric_context
436 update_metric_internal('COMPLETED_REQ_TERMINATED', '0', ct_term);
437
438 -- update the number of completed requests with warning, use 0 for the metric_context
439 update_metric_internal('COMPLETED_REQ_WARNING', '0', ct_warn);
440
441 commit;
442 EXCEPTION
443 when others then
444 rollback;
445 raise;
446 END refresh_completed_req_status;
447
448 -- Name
449 -- refresh_pending_req_status
450 --
451 -- Purpose
452 -- Computes the metric values for the pending request status
453 --
454 -- Input Arguments
455 --
456 -- Output Arguments
457 --
458 -- Notes:
459 --
460 --
461 PROCEDURE refresh_pending_req_status
462 IS
463 cursor req_c is
464 SELECT count(*) count, status_code
465 FROM fnd_concurrent_requests
466 WHERE (status_code IN ('I', 'Q')
467 AND requested_start_date <= sysdate
468 AND phase_code = 'P'
469 AND hold_flag = 'N')
470 GROUP BY status_code;
471
472
473 ct_normal number := 0;
474 ct_standby number := 0;
475 ct_scheduled number := 0;
476 BEGIN
477
478 for req in req_c loop
479 --normal
480 if req.status_code = 'I' then
481 ct_normal := req.count;
482 end if;
483
484 if req.status_code = 'Q' then
485 ct_standby := req.count;
486 end if;
487 end loop;
488
489 SELECT count(*) into ct_scheduled
490 FROM fnd_concurrent_requests
491 WHERE (phase_code = 'P' AND hold_flag = 'N')
492 AND ( (status_code = 'P' )
493 OR (status_code IN( 'I', 'Q')
494 AND requested_start_date > sysdate ));
495
496 -- update the number of pending requests with status normal, use 0 for the metric_context
497 update_metric_internal('PENDING_REQ_NORMAL', '0', ct_normal);
498
499 -- update the number of pending requests with status standby, use 0 for the metric_context
500 update_metric_internal('PENDING_REQ_STANDBY', '0', ct_standby);
501
502 -- update the number of pending requests with status scheduled, use 0 for the metric_context
503 update_metric_internal('PENDING_REQ_SCHEDULED', '0', ct_scheduled);
504
505 commit;
506 EXCEPTION
507 when others then
508 rollback;
509 raise;
510 END refresh_pending_req_status;
511
512 -- Name
513 -- refresh_running_req_duration
514 --
515 -- Purpose
516 -- Computes the metric values for the running request duration
517 --
518 -- Input Arguments
519 --
520 -- Output Arguments
521 --
522 -- Notes:
523 --
524 --
525 PROCEDURE refresh_running_req_duration
526 IS
527 ct_10min number := 0;
528 ct_60min number := 0;
529 ct_long number := 0;
530 BEGIN
531
532 select count(*) into ct_10min
533 from fnd_concurrent_requests
534 where ((sysdate - actual_start_date) * (1440)) <= 10
535 and status_code in ('R');
536
537 select count(*) into ct_60min
538 from fnd_concurrent_requests
539 where ((sysdate - actual_start_date) * (1440)) between 10 and 60
540 and status_code in ('R');
541
542 select count(*) into ct_long
543 from fnd_concurrent_requests
544 where ((sysdate - actual_start_date) * (1440)) >= 60
545 and status_code in ('R');
546
547 -- update the number of running requests with duration less than 10 minutes, use 0 for the metric_context
548 update_metric_internal('RUNNING_REQ_10MIN', '0', ct_10min);
549
550 -- update the number of running requests with duration between 10-60 minutes, use 0 for the metric_context
551 update_metric_internal('RUNNING_REQ_60MIN', '0', ct_60min);
552
553 -- update the number of running requests with duration longer than 60 minutes, use 0 for the metric_context
554 update_metric_internal('RUNNING_REQ_LONG', '0', ct_long);
555
556 commit;
557 EXCEPTION
558 when others then
559 rollback;
560 raise;
561 END refresh_running_req_duration;
562
563
564 --
565 -- Name
566 -- refresh_running_req_user
567 --
568 -- Purpose
569 -- Computes the metric values for the running request grouped by user
570 --
571 --
572
573 PROCEDURE refresh_running_req_user
574 IS
575 cursor req_c is
576 select count(*) count, user_name
577 from fnd_concurrent_requests, fnd_user
578 where requested_by = user_id and status_code = 'R'
579 group by user_name;
580
581 BEGIN
582
583 --Delete entries for running request count for all users.
584 delete_metric_internal('RUNNING_REQ_USER');
585 for req in req_c loop
586 -- Insert the number of running requests for each user, use user_name as the metric_context
587 insert_metric_internal('RUNNING_REQ_USER', req.user_name, req.count);
588 end loop;
589
590 commit;
591 EXCEPTION
592 when others then
593 rollback;
594 raise;
595 END refresh_running_req_user;
596
597
598 --
599 -- Name
600 -- refresh_pending_req_user
601 --
602 -- Purpose
603 -- Computes the metric values for the pending request grouped by user
604 --
605 --
606
607 PROCEDURE refresh_pending_req_user
608 IS
609 cursor req_c is
610 select count(rv.Request_ID) count, user_name
611 from Fnd_amp_requests_v rv,
612 Fnd_lookups l
613 Where rv.phase_code = 'P'
614 and l.meaning = rv.phase
615 and l.lookup_code = 'P'
616 and l.lookup_type = 'CP_PHASE_CODE'
617 group by user_name;
618 BEGIN
619
620 --Delete entries for pending request count for all users.
621 delete_metric_internal('PENDING_REQ_USER');
622 for req in req_c loop
623 -- Insert the number of pending requests for each user, use user_name as the metric_context
624 insert_metric_internal('PENDING_REQ_USER', req.user_name, req.count);
625 end loop;
626
627 commit;
628 EXCEPTION
629 when others then
630 rollback;
631 raise;
632 END refresh_pending_req_user;
633
634 --
635 -- Name
636 -- refresh_running_req_app
637 --
638 -- Purpose
639 -- Computes the metric values for the running request grouped by application
640 --
641 --
642
643 PROCEDURE refresh_running_req_app
644 IS
645 cursor req_c is
646 select count(*) count, application_name
647 from fnd_concurrent_requests r, fnd_application_vl v
648 where r.program_application_id = v.application_id and status_code = 'R'
649 group by application_name;
650
651 BEGIN
652
653 --Delete entries for running request count for all applications.
654 delete_metric_internal('RUNNING_REQ_APPLICATION');
655 for req in req_c loop
656 -- Insert the number of running requests for each APPLICATION, use application_name as the metric_context
657 insert_metric_internal('RUNNING_REQ_APPLICATION', req.application_name, req.count);
658 end loop;
659
660 commit;
661 EXCEPTION
662 when others then
663 rollback;
664 raise;
665 END refresh_running_req_app;
666
667 -- Name
668 -- refresh_pending_req_app
669 --
670 -- Purpose
671 -- Computes the metric values for the pending request grouped by application
672 --
673 --
674
675 PROCEDURE refresh_pending_req_app
676 IS
677 cursor req_c is
678 select count(rv.Request_ID) count, application_name
679 from Fnd_amp_requests_v rv,
680 Fnd_lookups l
681 Where rv.phase_code = 'P'
682 and l.meaning = rv.phase
683 and l.lookup_code = 'P'
684 and l.lookup_type = 'CP_PHASE_CODE'
685 group by application_name;
686
687 BEGIN
688
689 --Delete entries for pending request count for all applications.
690 delete_metric_internal('PENDING_REQ_APPLICATION');
691 for req in req_c loop
692 -- Insert the number of pending requests for each APPLICATION, use application_name as the metric_context
693 insert_metric_internal('PENDING_REQ_APPLICATION', req.application_name, req.count);
694 end loop;
695
696 commit;
697 EXCEPTION
698 when others then
699 rollback;
700 raise;
701 END refresh_pending_req_app;
702
703 --
704 -- Name
705 -- refresh_running_req_resp
706 --
707 -- Purpose
708 -- Computes the metric values for the running request grouped by responsibility
709 --
710 --
711
712 PROCEDURE refresh_running_req_resp
713 IS
714 cursor req_c is
715 select count(*) count, responsibility_name
716 from fnd_concurrent_requests r, fnd_responsibility_vl v
717 where r.responsibility_application_id = v.application_id
718 and r.responsibility_id = v.responsibility_id and status_code = 'R'
719 group by responsibility_name;
720
721 BEGIN
722
723 --Delete entries for running request count for all responsibility.
724 delete_metric_internal('RUNNING_REQ_RESP');
725 for req in req_c loop
726 -- Insert the number of running requests for each responsibility, use responsibility_name as the metric_context
727 insert_metric_internal('RUNNING_REQ_RESP', req.responsibility_name, req.count);
728 end loop;
729
730 commit;
731 EXCEPTION
732 when others then
733 rollback;
734 raise;
735 END refresh_running_req_resp;
736 --
737 -- Name
738 -- refresh_pending_req_resp
739 --
740 -- Purpose
741 -- Computes the metric values for the pending request grouped by responsibility
742 --
743 --
744
745 PROCEDURE refresh_pending_req_resp
746 IS
747 cursor req_c is
748 select count(rv.Request_ID) count, responsibility_name
749 from Fnd_amp_requests_v rv,
750 Fnd_lookups l
751 Where rv.phase_code = 'P'
752 and l.meaning = rv.phase
753 and l.lookup_code = 'P'
754 and l.lookup_type = 'CP_PHASE_CODE'
755 group by responsibility_name;
756
757 BEGIN
758
759 --Delete entries for pending request count for all responsibility.
760 delete_metric_internal('PENDING_REQ_RESP');
761 for req in req_c loop
762 -- Insert the number of pending requests for each responsibility, use responsibility_name as the metric_context
763 insert_metric_internal('PENDING_REQ_RESP', req.responsibility_name, req.count);
764 end loop;
765
766 commit;
767 EXCEPTION
768 when others then
769 rollback;
770 raise;
771 END refresh_pending_req_resp;
772
773 -- Name
774 -- update_run_req_mgr_metric
775 --
776 -- Purpose
777 -- compute the count of running requests for a specified manager
778 --
779 PROCEDURE update_run_req_mgr_metric(p_queue_application_id in number,
780 p_concurrent_queue_name in varchar2,
781 p_user_concurrent_queue_name in varchar2)
782 IS
783 ct_running number := 0;
784 BEGIN
785 select count(*) into ct_running
786 from fnd_concurrent_worker_requests r
787 where queue_application_id = p_queue_application_id
788 and concurrent_queue_name = p_concurrent_queue_name
789 and status_code = 'R';
790
791 update_metric_internal('RUNNING_REQ_MANAGER', p_user_concurrent_queue_name, ct_running);
792
793 commit;
794 EXCEPTION
795 when others then
796 rollback;
797 raise;
798 END update_run_req_mgr_metric;
799 -- Name
800 -- update_pend_req_mgr_metric
801 --
802 -- Purpose
803 -- compute the count of pending requests for a specified manager
804 --
805 PROCEDURE update_pend_req_mgr_metric(p_queue_application_id in number,
806 p_concurrent_queue_name in varchar2,
807 p_user_concurrent_queue_name in varchar2)
808 IS
809 ct_pending number := 0;
810 BEGIN
811 --sql suggested by mike.
812 select count(*) into ct_pending
813 from fnd_concurrent_worker_requests r
814 where queue_application_id = p_queue_application_id
815 and concurrent_queue_name = p_concurrent_queue_name
816 and status_code = 'I' and hold_flag <> 'Y'
817 and requested_start_date <= sysdate;
818
819 update_metric_internal('PENDING_REQ_MANAGER', p_user_concurrent_queue_name, ct_pending);
820
821 commit;
822 EXCEPTION
823 when others then
824 rollback;
825 raise;
826 END update_pend_req_mgr_metric;
827
828 -- Name
829 -- update_process_mgr_metric
830 --
831 -- Purpose
832 -- compute the count of process for a specified manager
833 --
834 PROCEDURE update_process_mgr_metric(p_queue_application_id in number,
835 p_concurrent_queue_name in varchar2,
836 p_user_concurrent_queue_name in varchar2)
837 IS
838 ct_process number :=0;
839 BEGIN
840 select running_processes into ct_process
841 from fnd_concurrent_queues_vl
842 where application_id = p_queue_application_id
843 and concurrent_queue_name = p_concurrent_queue_name;
844
845 update_metric_internal('RUNNING_PROCESS_MANAGER', p_user_concurrent_queue_name, ct_process);
846
847 commit;
848 EXCEPTION
849 when no_data_found then
850 -- the count for running process is 0
851 update_metric_internal('RUNNING_PROCESS_MANAGER', p_user_concurrent_queue_name, 0);
852 commit;
853
854 when others then
855 rollback;
856 raise;
857
858 END update_process_mgr_metric;
859
860
861 -- Name
862 -- refresh_run_req_process_mgr
863 --
864 -- Purpose
865 -- refresh the count of running requests and processes for all managers
866 --
867 PROCEDURE refresh_run_req_process_mgr
868 IS
869 cursor mgr_c is
870 select application_id, concurrent_queue_name, user_concurrent_queue_name
871 from fnd_concurrent_queues_vl
872 where manager_type = 1;
873 BEGIN
874
875 --Delete entries for running request count and running process count for all managers.
876 delete_metric_internal('RUNNING_REQ_MANAGER');
877 delete_metric_internal('RUNNING_PROCESS_MANAGER');
878 for mgr in mgr_c loop
879 -- update the number of running requests for each manager
880 update_run_req_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
881 -- update the number of running processes for each manager
882 update_process_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
883 end loop;
884
885 commit;
886 EXCEPTION
887 when others then
888 rollback;
889 raise;
890
891 END refresh_run_req_process_mgr;
892
893 -- Name
894 -- refresh_pend_req_mgr
895 --
896 -- Purpose
897 -- refresh the count of pending requests for all managers
898 --
899 PROCEDURE refresh_pend_req_mgr
900 IS
901 cursor mgr_c is
902 select application_id, concurrent_queue_name, user_concurrent_queue_name
903 from fnd_concurrent_queues_vl
904 where manager_type = 1;
905 BEGIN
906
907 --Delete entries for pending request count for all managers.
908 delete_metric_internal('PENDING_REQ_MANAGER');
909 for mgr in mgr_c loop
910 -- update the number of pending requests for each manager
911 update_pend_req_mgr_metric(mgr.application_id, mgr.concurrent_queue_name, mgr.user_concurrent_queue_name);
912 end loop;
913
914 commit;
915 EXCEPTION
916 when others then
917 rollback;
918 raise;
919
920 END refresh_pend_req_mgr;
921
922 -- Name
923 -- refresh_req_stats_user
924 --
925 -- Purpose
926 -- refresh the concurrent request statistics by user
927 --
928 PROCEDURE refresh_req_stats_user
929 IS
930 cursor s24h_c is
931 SELECT r.requested_by user_id,
932 count(r.actual_completion_date) count,
933 sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
934 FROM fnd_concurrent_requests r
935 WHERE r.phase_code = 'C'
936 and (sysdate - r.actual_completion_date) <= 1
937 group by r.requested_by;
938
939 cursor s7day_c is
940 SELECT r.requested_by user_id,
941 count(r.actual_completion_date) count,
942 sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
943 FROM fnd_concurrent_requests r
944 WHERE r.phase_code = 'C'
945 and (sysdate - r.actual_completion_date) <= 7
946 group by r.requested_by;
947
948 cursor s31day_c is
949 SELECT r.requested_by user_id,
950 count(r.actual_completion_date) count,
951 sum(r.actual_completion_date-nvl(r.actual_start_date,r.requested_start_date))*24*3600 runtime --in seconds
952 FROM fnd_concurrent_requests r
953 WHERE r.phase_code = 'C'
954 and (sysdate - r.actual_completion_date) <= 31
955 group by r.requested_by;
956
957 BEGIN
958
959 --Delete entries for statistics data for stats_interval=24hours.
960 delete from fnd_oam_cpstats_user where stats_interval = 24; --24 hours
961 for stat in s24h_c loop
962 -- insert the data
963 insert_stats_user(stat.user_id, 24, stat.count, stat.runtime);
964 end loop;
965
966 delete from fnd_oam_cpstats_user where stats_interval = 168; -- 7days = 168 hours
967 for stat in s7day_c loop
968 -- insert the data
969 insert_stats_user(stat.user_id, 168, stat.count, stat.runtime);
970 end loop;
971
972 delete from fnd_oam_cpstats_user where stats_interval = 744; --31 days = 744 hours
973 for stat in s31day_c loop
974 -- insert the data
975 insert_stats_user(stat.user_id, 744, stat.count, stat.runtime);
976 end loop;
977 commit;
978 EXCEPTION
979 when others then
980 rollback;
981 raise;
982 END refresh_req_stats_user;
983
984 -- Name
985 -- refresh_req_stats_program
986 --
987 -- Purpose
988 -- refresh the concurrent request statistics by program
989 --
990 PROCEDURE refresh_req_stats_program
991 IS
992 cursor s24h_c is
993 SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
994 round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total, -- in seconds
995 round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave, -- in seconds
996 round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum, -- in seconds
997 round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum, -- in seconds
998 count(*) count
999 FROM fnd_concurrent_requests r
1000 WHERE r.phase_code = 'C'
1001 and r.actual_completion_date is not null
1002 and r.actual_start_date is not null
1003 and (sysdate - r.actual_completion_date) <= 1
1004 GROUP BY r.program_application_id, r.concurrent_program_id;
1005
1006 cursor s7day_c is
1007 SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
1008 round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total,
1009 round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave,
1010 round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum,
1011 round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum,
1012 count(*) count
1013 FROM fnd_concurrent_requests r
1014 WHERE r.phase_code = 'C'
1015 and r.actual_completion_date is not null
1016 and r.actual_start_date is not null
1017 and (sysdate - r.actual_completion_date) <= 7
1018 GROUP BY r.program_application_id, r.concurrent_program_id;
1019
1020 cursor s31day_c is
1021 SELECT r.program_application_id app_id, r.concurrent_program_id prog_id,
1022 round(sum(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) total,
1023 round(avg(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) ave,
1024 round(min(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) minimum,
1025 round(max(greatest(actual_completion_date-actual_start_date,0))*3600*24, 0) maximum,
1026 count(*) count
1027 FROM fnd_concurrent_requests r
1028 WHERE r.phase_code = 'C'
1029 and r.actual_completion_date is not null
1030 and r.actual_start_date is not null
1031 and (sysdate - r.actual_completion_date) <= 31
1032 GROUP BY r.program_application_id, r.concurrent_program_id;
1033
1034 BEGIN
1035
1036 --Delete entries for statistics data for stats_interval=24hours.
1037 delete from fnd_oam_cpstats_program where stats_interval = 24;
1038 for stat in s24h_c loop
1039 -- insert the data
1040 insert_stats_program(stat.app_id, stat.prog_id, 24,
1041 stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
1042 end loop;
1043
1044 delete from fnd_oam_cpstats_program where stats_interval = 168; -- 7 days = 168 hours
1045 for stat in s7day_c loop
1046 -- insert the data
1047 insert_stats_program(stat.app_id, stat.prog_id, 168,
1048 stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
1049 end loop;
1050
1051 delete from fnd_oam_cpstats_program where stats_interval = 744; -- 31 days = 744 hours
1052 for stat in s31day_c loop
1053 -- insert the data
1054 insert_stats_program(stat.app_id, stat.prog_id, 744,
1055 stat.total, stat.ave, stat.minimum, stat.maximum, stat.count);
1056 end loop;
1057 commit;
1058 EXCEPTION
1059 when others then
1060 rollback;
1061 raise;
1062 END refresh_req_stats_program;
1063
1064
1065 -- Name
1066 -- insert_stats_user
1067 -- Purpose
1068 -- This procedure is for internal use only!
1069 -- This procedure will insert a row in fnd_oam_stats_user
1070 --
1071 -- Input Arguments
1072 --
1073 --
1074 PROCEDURE insert_stats_user (
1075 p_user_id in number,
1076 p_stats_interval in varchar2,
1077 p_comp_req_count in number,
1078 p_total_runtime in number)
1079 IS
1080 v_userid number;
1081 BEGIN
1082 v_userid := get_user_id;
1083
1084 -- insert the data
1085 insert into fnd_oam_cpstats_user (user_id, stats_interval,
1086 comp_req_count, total_runtime,
1087 last_updated_by, last_update_date,
1088 last_update_login, created_by, creation_date)
1089 values (p_user_id, p_stats_interval, p_comp_req_count,
1090 p_total_runtime,
1091 v_userid, sysdate, v_userid, 0, sysdate);
1092
1093 END insert_stats_user;
1094 -- Name
1095 -- insert_stats_program
1096 -- Purpose
1097 -- This procedure is for internal use only!
1098 -- This procedure will insert a row in fnd_oam_stats_user
1099 --
1100 -- Input Arguments
1101 --
1102 --
1103 PROCEDURE insert_stats_program (
1104 p_app_id in number,
1105 p_program_id in number,
1106 p_stats_interval in varchar2,
1107 p_total_runtime in number,
1108 p_ave_tuntime in number,
1109 p_min_tuntime in number,
1110 p_max_tuntime in number,
1111 p_times_run in number)
1112 IS
1113 v_userid number;
1114 BEGIN
1115 v_userid := get_user_id;
1116
1117 -- insert the data
1118 insert into fnd_oam_cpstats_program (application_id, program_id, stats_interval,
1119 total_runtime, ave_runtime, min_runtime, max_runtime, times_run,
1120 last_updated_by, last_update_date,
1121 last_update_login, created_by, creation_date)
1122 values (p_app_id, p_program_id, p_stats_interval, p_total_runtime,
1123 p_ave_tuntime, p_min_tuntime, p_max_tuntime,
1124 p_times_run,
1125 v_userid, sysdate, v_userid, 0, sysdate);
1126
1127 END insert_stats_program;
1128
1129 --
1130 -- Name
1131 -- submit_req_conditional
1132 --
1133 -- Purpose
1134 -- Submits a request for program 'OAMCHARTCOL' if and only if there are no
1135 -- other requests for this program in the pending or running phase.
1136 --
1137 -- Input Arguments
1138 --
1139 -- Output Arguments
1140 --
1141 --
1142 -- Notes:
1143 --
1144 --
1145
1146 PROCEDURE submit_req_conditional
1147 IS
1148 retcode number;
1149 retval boolean;
1150 msg varchar2(1000);
1151 active_count number;
1152
1153 appl_id number;
1154 resp_id number;
1155 user_id number;
1156 user_name varchar2(80);
1157 resp_name varchar2(80);
1158 resp_key varchar2(50);
1159
1160 p_request_id number := null;
1161 p_phase varchar2(100);
1162 p_status varchar2(100);
1163 p_dev_phase varchar2(100);
1164 p_dev_status varchar2(100);
1165 p_message varchar2(500);
1166 outcome boolean;
1167 BEGIN
1168 -- First query to see if there is a request already submitted for this
1169 -- program.
1170 outcome :=
1171 fnd_concurrent.get_request_status(
1172 request_id=>p_request_id,
1173 appl_shortname=>'FND',
1174 program=>'OAMCHARTCOL',
1175 phase=>p_phase,
1176 status=>p_status,
1177 dev_phase=>p_dev_phase,
1178 dev_status=>p_dev_status,
1179 message=>p_message);
1180
1181 if p_dev_phase is null then
1182 p_dev_phase := 'X';
1183 end if;
1184 if ((outcome = false and p_request_id is null) or
1185 (outcome = true and p_request_id is not null and
1186 p_dev_phase <> 'PENDING' and
1187 p_dev_phase <> 'RUNNING')) and
1188 fnd_program.program_exists('OAMCHARTCOL', 'FND') = true then
1189
1190 select application_id, responsibility_id, responsibility_key
1191 into appl_id, resp_id, resp_key
1192 from fnd_responsibility
1193 where responsibility_key = 'SYSTEM_ADMINISTRATOR';
1194
1195 select user_id, user_name
1196 into user_id, user_name
1197 from fnd_user
1198 where user_name = 'SYSADMIN';
1199
1200 -- Now initialize the environment for SYSADMIN
1201 fnd_global.apps_initialize(user_id, resp_id, appl_id);
1202
1203 -- Submit the request.
1204 retcode := fnd_request.submit_request(application=>'FND', program=>'OAMCHARTCOL');
1205
1206 end if;
1207 commit;
1208 EXCEPTION
1209 when others then
1210 rollback;
1211 null;
1212 END submit_req_conditional;
1213
1214
1215 END fnd_oam_cpcharts_col;
[Home] [Help]