1 package body FND_OAM as
2 /* $Header: AFCPOAMB.pls 120.3 2005/11/16 13:17:40 ravmohan ship $ */
3
4 --
5 -- Package
6 -- FND_OAM
7 -- Purpose
8 -- Utilities for the Oracle Applications Manager
9 -- History
10
11 --
12 -- GENERIC_ERROR (Internal)
13 --
14 -- Set error message and raise exception for unexpected sql errors.
15 --
16 procedure GENERIC_ERROR(routine in varchar2,
17 errcode in number,
18 errmsg in varchar2) is
19 begin
20 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
21 fnd_message.set_token('ROUTINE', routine);
22 fnd_message.set_token('ERRNO', errcode);
23 fnd_message.set_token('REASON', errmsg);
24 end;
25
26
27
28 --
29 -- PUBLIC VARIABLES
30 --
31
32 -- Exceptions
33
34 -- Exception Pragmas
35
36 --
37 -- PUBLIC FUNCTIONS
38 --
39
40 --
41 -- Name
42 -- APPS_SESSIONS
43 --
44 -- Purpose
45 -- Returns the number of Apps logins, and the number
46 -- of open forms.
47 --
48 -- Output Arguments
49 -- logins - Current number of Apps logins.
50 -- forms - Current number of open forms.
51 --
52 -- Notes:
53 -- Login Auditing must be set to the FORM level.
54 --
55 procedure APPS_SESSIONS(logins out nocopy number, forms out nocopy number) is
56 begin
57 select count(distinct(r.login_id))
58 into logins
59 from fnd_login_responsibilities r, v$session s
60 where r.audsid = s.audsid;
61
62 select count(*)
63 into forms
64 from fnd_login_resp_forms f, v$session s
65 where f.audsid = s.audsid;
66 end;
67
68
69
70
71 --
72 -- Name
73 -- COMPLETED_REQS
74 --
75 -- Purpose
76 -- Returns the numbers of requests that completed with
77 -- the statuses Normal, Warning, Error, and Terminated.
78 --
79 -- Output Arguments
80 -- Normal - Number of Completed/Normal requests.
81 -- Warning - Number of Completed/Warning requests.
82 -- Error - Number of Completed/Error requests.
83 -- Terminated - Number of Completed/Terminated requests.
84 --
85 procedure COMPLETED_REQS (normal out nocopy number, warning out nocopy number,
86 error out nocopy number, terminated out nocopy number) is
87 begin
88 select count(*)
89 into normal
90 from fnd_concurrent_requests
91 where status_code = 'C';
92
93 select count(*)
94 into warning
95 from fnd_concurrent_requests
96 where status_code = 'G';
97
98 select count(*)
99 into error
100 from fnd_concurrent_requests
101 where status_code = 'E';
102
103 select count(*)
104 into terminated
105 from fnd_concurrent_requests
106 where status_code = 'X';
107 end;
108
109
110 --
114 -- Purpose
111 -- Name
112 -- PENDING_REQS
113 --
115 -- Returns the numbers of requests that are pending with
116 -- the statuses Normal, Scheduled, and Standby.
117 --
118 -- Output Arguments
119 -- Normal - Number of Pending/Normal requests.
120 -- Scheduled - Number of Pending/Scheduled requests.
121 -- Standby - Number of Pending/Standby Requests.
122 --
123 procedure PENDING_REQS (normal out nocopy number, scheduled out nocopy number,
124 standby out nocopy number) is
125 begin
126 select count(*)
127 into normal
128 from fnd_concurrent_requests
129 where status_code = 'I'
130 and requested_start_date <= sysdate
131 and hold_flag = 'N';
132
133 select count(*)
134 into standby
135 from fnd_concurrent_requests
136 where status_code = 'Q'
137 and requested_start_date <= sysdate
138 and hold_flag = 'N';
139
140 select count(*)
141 into scheduled
142 from fnd_concurrent_requests
143 where (status_code = 'P' or
144 (status_code in ('I', 'Q') and requested_start_date > sysdate))
145 and hold_flag = 'N';
146 end;
147
148
149 --
150 -- Name
151 -- CONC_MGR_PROCS
152 --
153 -- Purpose
154 -- Returns the number of running requests and total number
155 -- of running concurrent manager processes.
156 --
157 -- Output Arguments
158 -- running_reqs - Number of running requests.
159 -- mgr_procs - Number of manager processes.
160 --
161 procedure CONC_MGR_PROCS (running_reqs out nocopy number, mgr_procs out nocopy number) is
162 begin
163 select count(*)
164 into running_reqs
165 from fnd_concurrent_requests
166 where status_code = 'R'
167 or status_code = 'T';
168
169 select sum(running_processes)
170 into mgr_procs
171 from fnd_concurrent_queues
172 where manager_type = '1';
173 end;
174
175 --
176 -- Name
177 -- VALIDATE_USER
178 --
179 -- Purpose
180 -- To check if user has access to 'System Administrator' responsibility
181 -- and if access to Oracle Applications using the current username/password
182 -- combination has expired.
183 -- Parameters/Arguments:
184 -- Input - Application username
185 -- Output - Error message indicating the reason for validation failure
186 -- (upto 1800 bytes long)
187 -- Returns:
188 -- 0 - When it fails to validate the user.
189 -- Reason for failure will be in message variable.
190 -- 1 - When the specified User has access to System Administrator responsibility.
191 --
192 -- Notes:
193 --
194 function VALIDATE_USER(username in varchar2, message in out nocopy varchar2) return number is
195 dummy number;
196 begin
197
198 select 1
199 into dummy
200 from fnd_responsibility r,
201 fnd_user_resp_groups u,
202 fnd_user fu
203 where fu.user_name=upper(username)
204 and u.user_id = fu.user_id
205 and u.responsibility_id = r.responsibility_id
206 and u.responsibility_application_id = r.application_id
207 and r.responsibility_key='SYSTEM_ADMINISTRATOR'
208 and r.version = '4'
209 and r.start_date <= sysdate
210 and (r.end_date is null or r.end_date > sysdate);
211
212
213 return 1;
214
215 exception
216 when no_data_found then
217 return 0;
218 when others then
219 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
220 fnd_message.set_token('ROUTINE', 'FND_OAM.VALIDATE_USER');
221 fnd_message.set_token('ERRNO', SQLCODE);
222 fnd_message.set_token('REASON', SQLERRM);
223 message := fnd_message.get;
224 return 0;
225
226 end;
227
228 --
229 -- Name
230 -- Set_Debug
231 --
232 -- Purpose
233 -- To dynamically change/alter the diagnostics level of
234 -- individual manager or service
235 -- Parameters/Arguments:
236 -- Input - Application ID, Concurrent Queue ID, Manager Type,
237 -- Diagnostic Level
238 -- Returns:
239 -- 0 - When it fails
240 -- Reason for failure will be in message variable.
241 -- 1 - When the operation of requesting diagnostic level change succeeds
242 --
243 -- Notes:
244 --
245 --
246 function Set_Debug(Application in number,
247 QueueID in number,
248 ManagerType in number,
249 DiagLevel in varchar2,
250 Message in out nocopy varchar2) return number is
251 begin
252
253 -- Following is just a place holder .. the logic should change ..
254 --
255 if (ManagerType is not null) then
256 Update Fnd_Concurrent_Queues
257 Set Diagnostic_Level = DiagLevel
258 Where Manager_Type = ManagerType;
259 else
260 Update Fnd_Concurrent_Queues
261 Set Diagnostic_Level = DiagLevel
262 Where Application_ID = Application
263 and Concurrent_queue_ID = QueueID;
264 end if;
265
266 commit;
267 return 1;
268
269 exception
270 when others then
271 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
272 fnd_message.set_token('ROUTINE', 'FND_OAM.Set_Debug');
273 fnd_message.set_token('ERRNO', SQLCODE);
277 end;
274 fnd_message.set_token('REASON', SQLERRM);
275 message := fnd_message.get;
276 return 0;
278
279
280 -- Service Status Procedure
281 -- Input Arguments:
282 -- Service_id - ID of the service instance.
283 -- Output Arguments:
284 -- target - Total number of processes that should be
285 -- alive for this service.
286 -- actual - Total number of processes that are actually
287 -- alive for this service instance.
288 -- status - Status of the service:
289 -- 0 = Normal, 1 = Warning, 2 = Error
290 -- 3 = All instanaces are inactive (Deactivated,
291 -- terminated, etc.)
292 -- Description - Describes the status. All warnings and
293 -- errors must have a description. The
294 -- description must not exceed 2000 characters.
295 -- error_code - Indicates if there was a runtime error in
296 -- the function. 0 = Normal, > 0 = Error. All
297 -- exceptions must be caught by the procedure.
298 -- The "when others" clause is mandatory for
299 -- these procedures.
300 -- error_message - Describes any runtime errors within the
301 -- procedure. The error message must not
302 -- exceed 2000 characters.
303 --
304 procedure get_svc_status(service_id in number,
305 target out nocopy number,
306 actual out nocopy number,
307 status out nocopy number,
308 description out nocopy varchar2,
309 error_code out nocopy number,
310 error_message out nocopy varchar2) is
311 n number;
312 Begin
313 /* Due to NLS issues and UI concerns, we will not be
314 * passing back concatenated error and warning messages
315 * in the description parameter. We will pass back
316 * a single, somewhat generic message that describes the
317 * worst error or warning found. e.g. "One or more service
318 * instances is down."
319 *
320 */
321
322 n := 0;
323 error_code := 0;
324 error_message := null;
325
326
327 --- if (service_id < 1000) then //this is no longer needed
328 /*
329 * Need to call get_manager_status
330 * for and Accurate count.
331 */
332 declare
333 appl_id number;
334 conc_queue_id number;
335 tmp_target number;
336 tmp_actual number;
337 pmon varchar2(10);
338 callstat number;
339
340 cursor svc_cursor (svc_id number) is
341 select application_id, concurrent_queue_id
342 from fnd_concurrent_queues
343 where (max_processes > 0 or running_processes > 0)
344 and manager_type = svc_id;
345
346 begin
347 target := 0;
348 actual := 0;
349
350 for svc_rec in svc_cursor(service_id) loop
351 appl_id := svc_rec.application_id;
352 conc_queue_id := svc_rec.concurrent_queue_id;
353 fnd_concurrent.get_manager_status( appl_id,
354 conc_queue_id,
355 tmp_target,
356 tmp_actual,
357 pmon,
358 callstat);
359 if (callstat > 0) then
360 error_message := fnd_message.get;
361 error_code := 1;
362 return;
363 end if;
364
365 actual := actual + tmp_actual;
366 target := target + tmp_target;
367
368 end loop;
369 end;
370
371
372 /* Were any processes down? */
373 if ( actual < target) then
374
375 /* Are all of the service processes for an instance down? */
376 select count(*) into n
377 from fnd_concurrent_queues
378 where running_processes = 0
379 and max_processes > 0;
380
381 if ( n > 0) then /* All processes for an instance are down. */
382
383 fnd_message.set_name('FND', 'CONC-SM SOME INST DOWN');
384 status := 2; /* Error*/
385
386 else /* No one instance is completely down. */
387
388 fnd_message.set_name('FND', 'CONC-SM SOME PROCS DOWN');
389 /* The message name is slightly misleading. This is an imbalance. */
390
391 status := 1; /* Warning */
392
393 end if;
394
395 description := fnd_message.get;
396 return;
397
398 elsif (actual = 0 and target = 0) then /* All are inactive */
399
400 description := null;
401
402 -- Change for Bug 2640311
403 if service_id = 0 then
404 status := 2; /* Error because ICM should always be up */
405 else
406 status := 3; /* Deactivated */
407 end if;
408 -- End change for Bug 2640311
409
410 return;
411
412 end if;
413
414 description := null;
415 status := 0; /* Normal */
416 return;
417
418 exception
419 when others then
420 generic_error('fnd_oam.get_service_status', SQLCODE, SQLERRM);
421 error_message := fnd_message.get;
422 error_code := 1;
423 end;
424
425
426
427
428 -- Service Instance Status Procedure
429 -- Input Arguments:
433 -- target - Number of processes that should be alive for
430 -- application_id - Application ID of the service instance
431 -- concurrent_queue_id - ID of the service instance
432 -- Output Arguments:
434 -- this service instance.
435 -- actual - Number of processes that are actually alive
436 -- for this service instance.
437 -- status - Status of the service instance:
438 -- 0 = Normal, 1 = Warning, 2 = Error,
439 -- 3 = Inactive (Deactivated, Terminated, etc.)
440 -- Description - Describes the status. All warnings and
441 -- errors must have a description. The
442 -- description must not exceed 2000 characters.
443 -- error_code - Indicates if there was a runtime error in
444 -- the function. 0 = Normal, > 0 = Error. All
445 -- exceptions must be caught by the procedure.
446 -- The "when others" clause is mandatory for
447 -- these procedures.
448 -- error_message - Describes any runtime errors within theif;
449
450 -- procedure. The error message must not
451 -- exceed 2000 characters.
452 --
453 procedure get_svc_inst_status(appl_id in number,
454 conc_queue_id in number,
455 target out nocopy number,
456 actual out nocopy number,
457 status out nocopy number,
458 description out nocopy varchar2,
459 error_code out nocopy number,
460 error_message out nocopy varchar2) is
461 mgr_type number;
462 pmon varchar2(10);
463 callstat number;
464 begin
465
466 error_code := 0;
467
468 fnd_concurrent.get_manager_status(appl_id,
469 conc_queue_id,
470 target,
471 actual,
472 pmon,
473 callstat);
474 if (callstat > 0) then
475 error_message := fnd_message.get;
476 error_code := 1;
477 return;
478 end if;
479
480 if (actual = 0 and target > 0) then /* All processes down */
481
482 fnd_message.set_name('FND', 'CONC-SM SVC INST DOWN');
483 description := fnd_message.get;
484 status := 2; /* Error */
485
486 elsif (actual > 0 and actual < target) then /* Some procs down */
487
488 fnd_message.set_name('FND', 'CONC-SM INST PROCS DOWN');
489 /* Again, the message name is slightly misleading. */
490
491 description := fnd_message.get;
492 status := 1; /* Warning */
493
494 elsif (actual = 0 and target = 0) then /* Inactive */
495
496 description := null;
497
498 -- Change for Bug 2640311
499 if appl_id = 0 and conc_queue_id = 1 then
500 status := 2; /* Error because ICM should always be up and running */
501 else
502 status := 3; /* Deactivated */
503 end if;
504 -- end Change for Bug 2640311
505
506 else
507
508 description := null;
509 status := 0; /* Warning */
510
511 end if;
512
513
514 exception
515 when others then
516 generic_error('fnd_oam.get_svc_inst_status', SQLCODE, SQLERRM);
517 error_message := fnd_message.get;
518 error_code := 1;
519 end;
520
521
522
523
524 -- Node Status Procedure
525 -- Input Arguments:
526 -- node_name - Name of the node
527 -- Output Arguments:
528 -- status - Status of the node:
529 -- 0 = Normal, 1 = Warning, 2 = Error
530 -- 3 = All instanaces are inactive (Deactivated,
531 -- terminated, etc.)
532 -- Description - Describes the status. All warnings and
533 -- errors must have a description. The
534 -- description must not exceed 2000 characters.
535 -- error_code - Indicates if there was a runtime error in
536 -- the function. 0 = Normal, > 0 = Error. All
537 -- exceptions must be caught by the procedure.
538 -- The "when others" clause is mandatory for
539 -- these procedures.
540 -- error_message - Describes any runtime errors within the
541 -- procedure. The error message must not
542 -- exceed 2000 characters.
543 --
544 procedure get_node_status(node_name in varchar2,
545 status out nocopy number,
546 description out nocopy varchar2,
547 error_code out nocopy number,
548 error_message out nocopy varchar2) is
549 icm_node varchar2(30);
550 target number;
551 actual number;
552 all_procs_down_for_service boolean;
553 total_target number;
554 total_actual number;
555 appl_id number;
556 conc_queue_id number;
557 pmon varchar2(10);
558 callstat number;
559
560 cursor svc_cursor (tnode varchar2, inode varchar2) is
561 select application_id, concurrent_queue_id,
565 or (target_node is null and tnode = inode);
562 manager_type, max_processes, running_processes
563 from fnd_concurrent_queues
564 where target_node = tnode
566
567 begin
568 /* Get ICM_NODE */
569 select target_node into icm_node
570 from fnd_concurrent_queues
571 where concurrent_queue_id = 1
572 and application_id = 0;
573
574 total_target := 0;
575 total_actual := 0;
576 all_procs_down_for_service := false;
577
578 for svc_rec in svc_cursor(node_name, icm_node) loop
579
580 if (to_number(svc_rec.manager_type) < 1000) then
581 /*
582 * Internal service - Need to call get_manager_status
583 * for and Accurate count.
584 */
585 appl_id := svc_rec.application_id;
586 conc_queue_id := svc_rec.concurrent_queue_id;
587
588 fnd_concurrent.get_manager_status(appl_id,
589 conc_queue_id,
590 target,
591 actual,
592 pmon,
593 callstat);
594 if (callstat > 0) then
595 error_message := fnd_message.get;
596 error_code := 1;
597 return;
598 end if;
599 else
600 target := svc_rec.max_processes;
601 actual := svc_rec.running_processes;
602 end if;
603
604
605 total_target := total_target + target;
606 total_actual := total_actual + actual;
607
608 if (target > 0 and actual = 0) then
609 all_procs_down_for_service := true;
610 end if;
611
612 end loop;
613
614
615 if (total_actual = 0 and total_target > 0) then /* All processes down */
616
617 fnd_message.set_name('FND', 'CONC-NODE ALL PROCS DOWN');
618 description := fnd_message.get;
619 status := 2; /* Error */
620
621 elsif (all_procs_down_for_service) then /* Some service down */
622
623 fnd_message.set_name('FND', 'CONC-NODE SVC DOWN');
624 description := fnd_message.get;
625 status := 2; /* Error */
626
627 elsif (total_target > total_actual) then /* Some procs down */
628
629 fnd_message.set_name('FND', 'CONC-NODE PROCS DOWN');
630 description := fnd_message.get;
631 status := 1; /* WARNING */
632
633 else
634
635 description := null;
636 status := 0; /* Normal */
637
638 end if;
639
640 exception
641 when others then
642 generic_error('fnd_oam.get_node_status', SQLCODE, SQLERRM);
643 error_message := fnd_message.get;
644 error_code := 1;
645 end;
646
647
648 procedure get_req_status_phase_schDesc(
649 pcode in char,
650 scode in char,
651 hold in char,
652 enbld in char,
653 stdate in date,
654 rid in number,
655 status out nocopy varchar2,
656 phase out nocopy varchar2,
657 schDesc out nocopy varchar2) is
658 begin
659 status := fnd_amp_private.get_phase(pcode, scode, hold, enbld, stdate, rid);
660 phase := fnd_amp_private.get_status(pcode, scode, hold, enbld, stdate, rid);
661 schDesc := fnd_conc_sswa.get_sch_desc(rid);
662 end;
663
664
665
666 end FND_OAM;
[Home] [Help]