[Home] [Help]
PACKAGE BODY: APPS.XDP_APPLICATION
Source
1 PACKAGE BODY XDP_APPLICATION AS
2 /* $Header: XDPAADMB.pls 120.1 2005/06/15 22:07:04 appldev $ */
3
4 TYPE Service_Instance_reqs_rec IS RECORD
5 (Instance_Name varchar2(30),
6 Req_ID number);
7
8 TYPE Service_Instance_reqs_tab IS TABLE of Service_Instance_reqs_rec
9 index by binary_integer;
10
11 CURSOR c_getQServiceTypes IS
12 select SERVICE_ID, SERVICE_HANDLE, SERVICE_NAME
13 from fnd_cp_services_VL
14 where
15 ((service_handle like 'XDPA%') or (service_handle like 'XDPQ%')) and
16 ENABLED = 'Y' and
17 service_handle <> 'XDPCTRLS'
18 order by service_handle;
19
20 Function Is_ICM_Running (Caller in varchar2 default 'CONC') return boolean;
21
22 Procedure Get_Max_Tries(Interval OUT NOCOPY number, MaxTries OUT NOCOPY number) ;
23
24 Function Check_Request_Status (ReqID in number, ServiceInstanceId in number) return boolean;
25
26 Function Verify_Controller_Instances (p_ServiceInstanceList in SERVICE_INSTANCE_REQS_TAB)
27 return boolean;
28
29 Function Start_Service_Instances (p_ServiceHandle in varchar2) return SERVICE_INSTANCE_REQS_TAB;
30
31 Function Stop_Adapters (StopOptions IN VARCHAR2, FeOptions IN VARCHAR2, FeName IN VARCHAR2)
32 return boolean;
33
34 Procedure Stop_Service_Instances (p_ServiceHandle in varchar2, StopOptions in varchar2);
35
36 Function Fetch_ConcQ_Name (ConcQID in number) return varchar2;
37
38 Function Fetch_ConcQ_ID (ConcQName in varchar2) return number;
39
40
41 -- ************************** IS_ICM_RUNNING *****************************
42 -- * INPUTS : None
43 -- * OUTPUTS: None
44 -- * RETURNS: None
45 -- *
46 -- * Called by:
47 -- * Calls :
48 -- *
49 -- * Modification history:
50 -- * WHO WHEN WHY
51 -- * --------------------------------------------------------------------------------
52 Function Is_ICM_Running (Caller IN VARCHAR2 default 'CONC') return boolean
53 is
54
55 l_targetp number;
56 l_activep number;
57 l_pmon_method varchar2(80);
58 l_callstat number;
59
60 begin
61
62 if Caller = 'CONC' then
63 FND_FILE.put_line(FND_FILE.log, 'Checking if ICM is running');
64 END IF;
65
66 FND_CONCURRENT.GET_MANAGER_STATUS (
67 applid => 0,
68 managerid => 1,
69 targetp => l_targetp,
70 activep => l_activep,
71 pmon_method => l_pmon_method,
72 callstat => l_callstat);
73
74 if l_callstat <> 0 then
75 if Caller = 'CONC' then
76 FND_FILE.put_line(FND_FILE.log,
77 'FND_CONCURRENT.GET_MANAGER_STATUS failed while checking for ICM, callstat: '||l_callstat);
78 END IF;
79 return false;
80 else
81 if Caller = 'CONC' then
82 FND_FILE.put_line(FND_FILE.log,'ICM target processes: '||l_targetp||', active processes: '||l_activep);
83 END IF;
84 if l_activep > 0 then
85 if Caller = 'CONC' then
86 FND_FILE.put_line(FND_FILE.log,'ICM is running');
87 END IF;
88 return true;
89 else
90 if Caller = 'CONC' then
91 FND_FILE.put_line(FND_FILE.log,'ICM is not running, services cannot be managed');
92 END IF;
93 return false;
94 end if;
95 end if;
96
97 end Is_ICM_Running;
98
99 -- ************************** Stop_Adapters *****************************************
100 -- * INPUTS : None
101 -- * OUTPUTS: None
102 -- * RETURNS: FALSE if not entirely successful else TRUE
103 -- *
104 -- * This procedure stops specified adapters
105 -- *
106 -- * Called by:
107 -- * Calls :
108 -- *
109 -- * Modification history:
110 -- * WHO WHEN WHY
111 -- * --------------------------------------------------------------------------------
112 -- ***********************************************************************************
113
114 Function Stop_Adapters
115 (
116 StopOptions IN VARCHAR2,
117 FeOptions IN VARCHAR2,
118 FeName IN VARCHAR2
119 )
120 return boolean
121
122 IS
123
124 CURSOR c_getAdapterInfoAll IS
125 SELECT xar.channel_name, fe.fulfillment_element_name, xar.adapter_display_name,
126 xar.fe_id, xar.adapter_status, xar.process_id
127 FROM xdp_adapter_reg xar, xdp_fes fe
128 WHERE xar.adapter_status not in (XDP_ADAPTER.pv_statusStopped,
129 XDP_ADAPTER.pv_statusStoppedError,
130 XDP_ADAPTER.pv_statusTerminated,
131 XDP_ADAPTER.pv_statusNotAvailable,
132 XDP_ADAPTER.pv_statusDeactivated,
133 XDP_ADAPTER.pv_statusDeactivatedSystem)
134 -- and xar.startup_mode = XDP_ADAPTER.pv_startAutomatic
135 and xar.fe_id = fe.fe_id
136 order by fe.fulfillment_element_name;
137
138 l_temp_fe_name VARCHAR2(2000);
139 l_temp_fe_name2 VARCHAR2(2000);
140 l_offset NUMBER;
141 l_AdapterOfInterest BOOLEAN := FALSE;
142 l_SomeError BOOLEAN := FALSE;
143 l_retcode NUMBER := 0;
144 l_errbuf VARCHAR2(2000);
145
146 BEGIN
147
148 l_SomeError := FALSE;
149
150 -- ************************************************************
151 -- Stop adapters as per user request
152 -- ************************************************************
153
154 l_temp_fe_name := 'NONE';
155
156 if (((FeOptions = 'INCLUDE') or (FeOptions = 'EXCLUDE'))
157 and (FeName IS NOT NULL)) then
158
159 -- Remove leading, trailing blanks and upper case the FE names
160 l_temp_fe_name := UPPER (LTRIM (RTRIM (FeName)));
161
162 if FeOptions = 'INCLUDE' then
163 FND_FILE.put_line(FND_FILE.log,'Stopping adapters for :'||
164 l_temp_fe_name||': only');
165 else
166 FND_FILE.put_line(FND_FILE.log,'Stopping adapters for FE(s) other than :'||
167 l_temp_fe_name||':');
168 end if;
169
170 -- Replace embedded commas by blanks and finally append a blank to the FE name
171 -- list so that all words in the list have atleast a trailing blank for
172 -- correct matching
173
174 l_temp_fe_name := REPLACE (l_temp_fe_name, ',', ' ');
175 l_temp_fe_name := RPAD (l_temp_fe_name, LENGTH(l_temp_fe_name)+1);
176 else
177
178 -- if all adapters are required to be stoppped i.e. complete application stop
179 -- Controller stop will take care of its adapters
180
181 FND_FILE.put_line(FND_FILE.log,'None of the adapters stoppped');
182 return true;
183
184 END IF; -- End of if which checks the user request type
185
186
187 for v_AdapterData in c_getAdapterInfoAll loop
188
189 l_AdapterOfInterest := FALSE;
190
191 l_temp_fe_name2 := UPPER (LTRIM (RTRIM
192 (v_AdapterData.fulfillment_element_name)));
193 l_temp_fe_name2 := RPAD (l_temp_fe_name2, LENGTH(l_temp_fe_name2)+1);
194 l_offset := INSTR (l_temp_fe_name, l_temp_fe_name2);
195
196 if (((FeOptions = 'INCLUDE') and (l_offset <> 0)) or -- found
197 ((FeOptions = 'EXCLUDE') and (l_offset = 0))) then -- not found
198 l_AdapterOfInterest := TRUE;
199 end if;
200
201 -- If adapter is of interest
202 if (l_AdapterOfInterest = TRUE) then
203
204 IF StopOptions = 'NORMAL' then
205
206 -- Stop adapter instance
207 -- Following package with also update the adapter instance entry
208 -- in XDP_ADAPTER_REG table accordingly
209 -- and will submit a admin request if required i.e. if the
210 -- Adapter cannot be locked
211
212 FND_FILE.put_line(FND_FILE.log,'Attempting to stop adapter '||
213 v_AdapterData.adapter_display_name);
214
215 XDP_ADAPTER.Stop_Adapter (
216 v_AdapterData.channel_name,
217 l_retcode, l_errbuf);
218
219 IF ((l_retcode <> 0) and
220 (l_retcode <> XDP_ADAPTER.pv_retAdapterInvalidState) and
221 (l_retcode <> XDP_ADAPTER.pv_retAdapterCannotLockReqSub)) THEN
222 l_SomeError := TRUE;
223 FND_FILE.put_line(FND_FILE.log,
224 'Error in stopping adapter '||
225 v_AdapterData.adapter_display_name);
226 FND_FILE.put_line(FND_FILE.log,l_errbuf);
227 elsif (l_retcode = XDP_ADAPTER.pv_retAdapterCannotLockReqSub) then
228 FND_FILE.put_line(FND_FILE.log,
229 'Stop request for adapter '||v_AdapterData.adapter_display_name||
230 ' successfully submitted');
231 FND_FILE.put_line(FND_FILE.log,l_errbuf);
232 else
233 -- Success and InvalidState case
234 -- InvalidState means already stopped
235
236 FND_FILE.put_line(FND_FILE.log,
237 'Adapter '||v_AdapterData.adapter_display_name||
238 ' stopped successfully');
239 END IF;
240
241 -- elsif StopOptions = 'ABORT' then
242 else
243 -- Stop adapter instance in abbort mode
244 -- and also update the adapter instance entry
245 -- in XDP_ADAPTER_REG table accordingly
246
247 FND_FILE.put_line(FND_FILE.log,'Attempting to stop adapter '||
248 v_AdapterData.adapter_display_name||
249 ' in abort mode');
250
251 XDP_ADAPTER.Terminate_Adapter (
252 v_AdapterData.channel_name,
253 l_retcode, l_errbuf);
254
255 IF l_retcode <> 0 THEN
256 l_SomeError := TRUE;
257 FND_FILE.put_line(FND_FILE.log,
258 'Error in terminating adapter '||
259 v_AdapterData.adapter_display_name);
260 FND_FILE.put_line(FND_FILE.log,l_errbuf);
261 else
262 FND_FILE.put_line(FND_FILE.log,
263 'Adapter '||
264 v_AdapterData.adapter_display_name||
265 ' Aborted successfully');
266
267 END IF;
268
269 END IF;
270
271 commit;
272
273 END IF; -- End of if adapter is of interest
274
275 END LOOP;
276
277 if l_SomeError = TRUE then
278 return false;
279 else
280 return true;
281 END IF;
282
283 EXCEPTION
284
285 WHEN OTHERS THEN
286 IF c_getAdapterInfoAll%ISOPEN THEN
287 CLOSE c_getAdapterInfoAll;
288 END IF;
289 FND_FILE.put_line(FND_FILE.log,'SQL code: '||SQLCODE);
290 FND_FILE.put_line(FND_FILE.log,'SQL message string: '||SQLERRM);
291 return false;
292 END Stop_Adapters;
293
294 -- ************************** STOP_SERVICE_INSTANCES *******************************
295 -- * INPUTS : None
296 -- * OUTPUTS: None
297 -- * RETURNS: None
298 -- *
299 -- * Called by:
300 -- * Calls :
301 -- *
302 -- * Modification history:
303 -- * WHO WHEN WHY
304 -- * --------------------------------------------------------------------------------
305 Procedure Stop_Service_Instances (p_ServiceHandle in varchar2, StopOptions in varchar2)
306 is
307 l_ReqID number;
308 l_ServiceInstanceList FND_CONCURRENT.SERVICE_INSTANCE_TAB_TYPE;
309
310 l_targetp number;
311 l_activep number;
312 l_pmon_method varchar2(80);
313 l_callstat number;
314 l_ServiceInstanceId number := 0;
315 l_ServiceToBeStopped boolean := FALSE;
316
317 begin
318 l_ServiceInstanceList.delete;
319
320 FND_FILE.put_line(FND_FILE.log, 'Retrieving services instances for '||p_ServiceHandle);
321 l_ServiceInstanceList := FND_CONCURRENT.GET_SERVICE_INSTANCES
322 (svc_handle => p_ServiceHandle);
323
324 if (l_ServiceInstanceList.COUNT > 0) then
325
326 FND_FILE.put_line(FND_FILE.log, 'Service instances found: '||
327 l_ServiceInstanceList.COUNT);
328
329 for i in 1..l_ServiceInstanceList.COUNT loop
330 if (l_ServiceInstanceList.EXISTS(i)) then
331
332 -- TODO
333 -- Donot stop if DISABLED, INACTIVE
334 -- Stop if ACTIVE, SUSPENDED
335 -- But whatif TRANSIT
336 -- Possible TRANSIT statues are:
337 -- DEACTIVATING, MIGRATING, CONNECTING, TERMINATING, INITIALIZING
338 -- Apart from DEACTIVATING, TERMINATING in other cases
339 -- service should also be stopped
340
341 l_ServiceToBeStopped := FALSE;
342
343 --if UPPER(l_ServiceInstanceList(i).STATE)
344 -- not in ('INACTIVE','DISABLED','DEACTIVATING','TERMINATING') then
345 if UPPER(l_ServiceInstanceList(i).STATE)
346 in ('ACTIVE','SUSPENDED') then
347
348 l_ServiceToBeStopped := TRUE;
349
350 elsif UPPER(l_ServiceInstanceList(i).STATE) = 'TRANSIT' then
351
352 l_ServiceInstanceId := Fetch_ConcQ_ID
353 (ConcQName => l_ServiceInstanceList(i).INSTANCE_NAME);
354 if (l_ServiceInstanceId > 0) then
355
356 FND_CONCURRENT.GET_MANAGER_STATUS (
357 applid => 535,
358 managerid => l_ServiceInstanceId,
359 targetp => l_targetp,
360 activep => l_activep,
361 pmon_method => l_pmon_method,
362 callstat => l_callstat);
363
364 if l_callstat <> 0 then
365 FND_FILE.put_line(FND_FILE.log,
366 'FND_CONCURRENT.GET_MANAGER_STATUS failed, callstat: '||l_callstat);
367 else
368 FND_FILE.put_line(FND_FILE.log,'Target processes: '||l_targetp||', active processes: '||l_activep);
369 -- if l_targetp > 0 then
370 if l_activep > 0 then
371 l_ServiceToBeStopped := TRUE;
372 END IF;
373 END IF;
374 END IF;
375 END IF;
376
377 if l_ServiceToBeStopped = TRUE then
378
379 l_ReqID := 0;
380
381 if StopOptions = 'NORMAL' then
382 FND_FILE.put_line(FND_FILE.log,
383 'Submitting DEACTIVATE request for service instance: '||
384 l_ServiceInstanceList(i).INSTANCE_NAME);
385 l_ReqID := FND_REQUEST.SUBMIT_SVC_CTL_REQUEST (
386 command => 'DEACTIVATE',
387 service => l_ServiceInstanceList(i).INSTANCE_NAME,
388 service_app => 'XDP');
389 else
390 FND_FILE.put_line(FND_FILE.log,
391 'Submitting TERMINATE request for service instance: '||
392 l_ServiceInstanceList(i).INSTANCE_NAME);
393 l_ReqID := FND_REQUEST.SUBMIT_SVC_CTL_REQUEST (
394 command => 'ABORT',
395 service => l_ServiceInstanceList(i).INSTANCE_NAME,
396 service_app => 'XDP');
397 END IF;
398
399 if l_ReqID > 0 then
400 FND_FILE.put_line(FND_FILE.log,
401 'Request : '||
402 l_ReqID||
403 ' successfully submitted');
404 else
405 FND_FILE.put_line(FND_FILE.log,
406 'Error, request could not be successfully submitted');
407 END IF;
408 else
409 FND_FILE.put_line(FND_FILE.log,
410 'Service instance: '||
411 l_ServiceInstanceList(i).INSTANCE_NAME||
412 ' ignored, has state: '||
413 l_ServiceInstanceList(i).STATE);
414 END IF;
415 END IF;
416 END LOOP;
417 else
418 FND_FILE.put_line(FND_FILE.log, 'No service instances found');
419 END IF;
420
421 end Stop_Service_Instances;
422
423 -- *************************** XDP_STOP *********************************************
424 -- * INPUTS : None
425 -- * OUTPUTS: None
426 -- * RETURNS: FALSE if adapters and controller cannot be stoppped at this moment i.e. either
427 -- * atleast one adapter instance is BUSY or some process has acquired lock on the
428 -- * XDP_ADAPTER_REGISTRATION table else TRUE
429 -- *
430 -- * This function gracefully shuts down the adapter instances and the controller.
431 -- *
432 -- * Called by: Administration scripts used to shutdown application gracefully.
433 -- * Calls :
434 -- *
435 -- * Modification history:
436 -- * WHO WHEN WHY
437 -- * ---------------------------------------------------------------------------------
438 -- ***********************************************************************************
439
440 PROCEDURE XDP_STOP
441 (
442 errbuf OUT NOCOPY VARCHAR2,
443 retcode OUT NOCOPY NUMBER,
444 FeOptions IN VARCHAR2,
445 FeName IN VARCHAR2,
446 StopOptions IN VARCHAR2
447 )
448
449 IS
450
451 l_SomeError BOOLEAN := FALSE;
452
453 BEGIN
454 -- Verify / reset adapters silently
455 XDP_ADAPTER.Verify_All_Adapters ( p_retcode => retCode,
456 p_errbuf => errbuf);
457
458 l_SomeError := FALSE;
459 retcode := 0;
460 errbuf := '';
461
462 if FeName is NULL and FeOptions = 'ALL' then
463
464 FND_FILE.put_line(FND_FILE.log,'Application shutdown initiated');
465
466 -- ************************************************************
467 -- Check if ICM is running
468 -- ************************************************************
469
470 if (not Is_ICM_Running('CONC')) then
471 retCode := -1;
472 errbuf := 'Cannot stop application as ICM is not running';
473 FND_FILE.put_line(FND_FILE.log, errbuf);
474 COMMIT;
475 return;
476 end if;
477
478 -- ************************************************************
479 -- Stop all non DISABLED, non INACTIVE Controller services instances
480 -- Controller stop also stops all its Adapters
481 -- ************************************************************
482
483 FND_FILE.put_line(FND_FILE.log,'Stopping Controller services');
484 begin
485 Stop_Service_Instances (p_ServiceHandle => 'XDPCTRLS',
486 StopOptions => StopOptions);
487 FND_FILE.put_line(FND_FILE.log,'Requests to stop Controller services submitted successfully');
488 EXCEPTION
489 WHEN OTHERS THEN
490
491 l_SomeError := TRUE;
492 FND_FILE.put_line(FND_FILE.log,
493 'Encountered error while stopping Controller services');
494 FND_FILE.put_line(FND_FILE.log, 'Error: ' || SUBSTR(errbuf,1,200));
495 FND_FILE.put_line (FND_FILE.log, 'SQLCODE: '||SQLCODE);
496 FND_FILE.put_line (FND_FILE.log, 'SQLERRM: '||SQLERRM);
497 commit;
498 -- Continue, do as much as possible
499 end;
500
501 -- ************************************************************
502 -- Stop all non DISABLED, non INACTIVE application queue service instances
503 -- ************************************************************
504
505 FND_FILE.put_line(FND_FILE.log,'Stopping Queue services');
506
507 for v_ServiceTypeData in c_getQServiceTypes loop
508 FND_FILE.put_line(FND_FILE.log,'Stopping '||v_ServiceTypeData.SERVICE_NAME);
509
510 begin
511 Stop_Service_Instances (p_ServiceHandle =>
512 v_ServiceTypeData.SERVICE_HANDLE,
513 StopOptions => StopOptions);
514 FND_FILE.put_line(FND_FILE.log,
515 'Requests to stop '||v_ServiceTypeData.SERVICE_NAME||
516 ' submitted successfully');
517 EXCEPTION
518 WHEN OTHERS THEN
519 l_SomeError := TRUE;
520 FND_FILE.put_line(FND_FILE.log,
521 'Encountered error when stopping services');
522 FND_FILE.put_line (FND_FILE.log, 'SQLCODE: '||SQLCODE);
523 FND_FILE.put_line (FND_FILE.log, 'SQLERRM: '||SQLERRM);
524 commit;
525 -- Continue, do as much as possible
526 end;
527
528 END LOOP;
529
530 -- TODO Verify the adapters HERE
531 -- In normal shutdown, Controller will ensure that adapters are stopped
532 -- In abort shutdown, termination of Controller Service will terminate child adapter
533 -- processes, all we are trying to do is verify their statuses in case service
534 -- instances have been terminated. If ICM has not picked up the termination request
535 -- verify will still find adapters to be running, but there is nothing much we can do
536
537 if (l_SomeError = FALSE) then
538 FND_FILE.put_line(FND_FILE.log,
539 'Application stop request completed successfully');
540 else
541 retcode := -1;
542 errbuf := 'Application stop request completed with warnings or errors';
543 FND_FILE.put_line(FND_FILE.log, errbuf);
544 END IF;
545 else
546 l_SomeError := Stop_Adapters (
547 StopOptions => StopOptions,
548 FeOptions => FeOptions,
549 FeName => FeName);
550
551 if (l_SomeError = FALSE) then
552 retcode := -1;
553 errbuf := 'Adapters stopped with warnings or errors';
554 FND_FILE.put_line(FND_FILE.log, errbuf);
555 else
556 FND_FILE.put_line(FND_FILE.log,
557 'Adapters stopped successfully');
558 END IF;
559 end if;
560
561 COMMIT;
562
563 EXCEPTION
564
565 WHEN OTHERS THEN
566 IF c_getQServiceTypes%ISOPEN THEN
567 CLOSE c_getQServiceTypes;
568 end if;
569 FND_FILE.put_line(FND_FILE.log,'SQL code: '||SQLCODE);
570 FND_FILE.put_line(FND_FILE.log,'SQL message string: '||SQLERRM);
571 retcode := SQLCODE;
572 errbuf := SUBSTR(SQLERRM,1,200);
573 COMMIT;
574
575 END XDP_STOP;
576
577 -- ************************** GET_MAX_TRIES ****************************************
578 -- * INPUTS : None
579 -- * OUTPUTS: None
580 -- * RETURNS: None
581 -- *
582 -- * Called by:
583 -- * Calls :
584 -- *
585 -- * Modification history:
586 -- * WHO WHEN WHY
587 -- * --------------------------------------------------------------------------------
588 Procedure Get_Max_Tries(Interval OUT NOCOPY number, MaxTries OUT NOCOPY number)
589 is
590 l_interval varchar2(20);
591 l_loop varchar2(20);
592 begin
593 if fnd_profile.defined('XDP_CTRL_WAIT_LOOP_SLEEP_TIME') then
594 fnd_profile.get('XDP_CTRL_WAIT_LOOP_SLEEP_TIME', l_interval);
595 if to_number(l_interval) <= 0 then
596 l_interval := '10';
597 end if;
598 else
599 l_interval := '10';
600 end if;
601 Interval := to_number(l_interval);
602
603 if fnd_profile.defined('XDP_CTRL_WAIT_LOOP_COUNT') then
604 fnd_profile.get('XDP_CTRL_WAIT_LOOP_COUNT', l_loop);
605 if to_number(l_loop) <= 0 then
606 l_loop := '10';
607 end if;
608 else
609 l_loop := '10';
610 end if;
611 MaxTries := to_number(l_loop);
612
613 end Get_Max_Tries;
614
615 -- ************************** CHECK_REQUEST_STATUS ****************************************
616 -- * INPUTS : None
617 -- * OUTPUTS: None
618 -- * RETURNS: None
619 -- *
620 -- * Called by:
621 -- * Calls :
622 -- *
623 -- * Modification history:
624 -- * WHO WHEN WHY
625 -- * --------------------------------------------------------------------------------
626 Function Check_Request_Status (ReqID in number, ServiceInstanceId in number)
627 return boolean
628 is
629
630 l_RunningFlag BOOLEAN := FALSE;
631 l_CallStatus BOOLEAN := FALSE;
632 l_Interval number;
633 l_MaxTries number;
634 l_Tries number := 0;
635 phase varchar2(80);
636 status varchar2(80);
637 dev_phase varchar2(80);
638 dev_status varchar2(80);
639 message varchar2(1996);
640 l_ReqID number := ReqID;
641
642 l_CPID number := 0;
643
644 begin
645 FND_FILE.put_line(FND_FILE.log,
646 'Checking the status of request: ' || to_char(ReqID));
647
648 Get_Max_Tries(l_Interval, l_MaxTries);
649
650 FND_FILE.put_line(FND_FILE.log,
651 'Tries: '||l_MaxTries||', wait between retries: '||l_Interval);
652
653 l_Tries := 1;
654
655 while (l_Tries <= l_MaxTries) loop
656
657 if (l_Tries > 1) then
658 FND_FILE.put_line(FND_FILE.log, 'Try #: ' || to_char(l_Tries));
659 DBMS_LOCK.SLEEP(l_Interval);
660 end if;
661
662 -- Initialize out variables with NULL string
663 dev_phase := 'NULL';
664 dev_status := 'NULL';
665 l_CallStatus := FND_CONCURRENT.GET_REQUEST_STATUS
666 (l_ReqID, null, null, phase,
667 status, dev_phase, dev_status,
668 message);
669
670 if (not l_CallStatus) then
671 FND_FILE.put_line(FND_FILE.log,
672 'FND_CONCURRENT.GET_REQUEST_STATUS failed, message: '||message);
673 return false;
674 end if;
675
676 FND_FILE.put_line(FND_FILE.log,
677 'Request Dev. Phase: ' || dev_phase || 'Status: ' || dev_status);
678 FND_FILE.put_line(FND_FILE.log,
679 'Request User Phase: ' || phase || 'Status: ' || status);
680
681 if upper(dev_phase) in ('COMPLETE') then
682
683 if upper(dev_status) = 'NORMAL' then
684
685 FND_FILE.put_line(FND_FILE.log,'Request has completed');
686
687 exit;
688 else
689 FND_FILE.put_line(FND_FILE.log,
690 'Request didnot complete successfully');
691 return false;
692 end if;
693
694 elsif upper(dev_phase) in ('INACTIVE') then
695
696 -- Irrespective of dev_status request
697 -- should NOT enter this phase
698
699 FND_FILE.put_line(FND_FILE.log,
700 'Request not started. Check the Internal Concurrent Manager process and try again');
701 return false;
702
703 else
704 FND_FILE.put_line(FND_FILE.log,'Request not yet completed');
705 l_Tries := l_Tries + 1;
706 end if;
707 end loop;
708
709 while (l_Tries <= l_MaxTries) loop
710
711 l_CPID := Fetch_CPID(ConcQID => ServiceInstanceId, Caller => 'CONC');
712 if l_CPID > 0 then
713 FND_FILE.put_line(FND_FILE.log,'Service has started. CPID: '||l_CPID);
714 return true;
715 else
716 FND_FILE.put_line(FND_FILE.log,'No active process for Service found');
717 end if;
718
719 l_Tries := l_Tries + 1;
720
721 if (l_Tries < l_MaxTries) then
722 FND_FILE.put_line(FND_FILE.log, 'Try #: ' || to_char(l_Tries));
723 DBMS_LOCK.SLEEP(l_Interval);
724 end if;
725
726 end loop;
727
728 FND_FILE.put_line(FND_FILE.log,
729 'Request has not completed yet. Check the Internal Concurrent Manager process and try again');
730 return false;
731
732 end Check_Request_Status ;
733
734 -- ************************** VERIFY_CONTROLLER_INSTANCES *****************************
735 -- * INPUTS : None
736 -- * OUTPUTS: None
737 -- * RETURNS: None
738 -- *
739 -- * Called by:
740 -- * Calls :
741 -- *
742 -- * Modification history:
743 -- * WHO WHEN WHY
744 -- * --------------------------------------------------------------------------------
745 Function Verify_Controller_Instances (p_ServiceInstanceList in SERVICE_INSTANCE_REQS_TAB)
746 return boolean
747 is
748 l_Flag boolean := false;
749 l_ControllerReqSubmitted boolean := false;
750 begin
751
752 if (p_ServiceInstanceList.COUNT > 0) then
753
754 -- We are only interested in Controller instances that are used
755 for v_GetSvcID in XDP_ADAPTER_CORE_DB.G_Get_Controller_Instances loop
756
757 l_ControllerReqSubmitted := false;
758
759 for i in 1..p_ServiceInstanceList.COUNT loop
760 if (p_ServiceInstanceList.EXISTS(i)) then
761
762 if UPPER(v_GetSvcID.concurrent_queue_name) =
763 UPPER(p_ServiceInstanceList(i).INSTANCE_NAME) then
764
765 l_ControllerReqSubmitted := true;
766
767 FND_FILE.put_line(FND_FILE.log,
768 'Verifying Controller instance '||
769 p_ServiceInstanceList(i).INSTANCE_NAME||
770 ', conc. req.: '||p_ServiceInstanceList(i).Req_ID);
771
772 l_Flag := Check_Request_Status (
773 p_ServiceInstanceList(i).Req_ID,
774 v_GetSvcID.service_instance_id);
775
776 if l_Flag = false then
777 -- Request did not COMPLETE after
778 -- retrys
779
780 FND_FILE.put_line(FND_FILE.log,
781 'Could not verify Controller instance '||
782 p_ServiceInstanceList(i).INSTANCE_NAME);
783 return false;
784 else
785 FND_FILE.put_line(FND_FILE.log,
786 'Successfully verified Controller instance '||
787 p_ServiceInstanceList(i).INSTANCE_NAME);
788 end if;
789 end if;
790 end if;
791 end loop;
792
793 if l_ControllerReqSubmitted = false then
794 FND_FILE.put_line(FND_FILE.log, 'Did not verify Controller instance '||
795 v_GetSvcID.concurrent_queue_name||' as request to start not submitted');
796 end if;
797
798 end loop;
799 else
800 FND_FILE.put_line(FND_FILE.log, 'No Controller instances to verify');
801 end if;
802
803 return true;
804
805 end Verify_Controller_Instances;
806
807 -- ************************** START_SERVICE_INSTANCES *********************************
808 -- * INPUTS : None
809 -- * OUTPUTS: None
810 -- * RETURNS: None
811 -- *
812 -- * Called by:
813 -- * Calls :
814 -- *
815 -- * Modification history:
816 -- * WHO WHEN WHY
817 -- * --------------------------------------------------------------------------------
818 Function Start_Service_Instances (p_ServiceHandle in varchar2) return SERVICE_INSTANCE_REQS_TAB
819 is
820 l_ReqID number;
821 l_ServiceInstanceList FND_CONCURRENT.SERVICE_INSTANCE_TAB_TYPE;
822 l_ServiceInstanceStarted SERVICE_INSTANCE_REQS_TAB;
823 l_Count number := 0;
824
825 l_targetp number;
826 l_activep number;
827 l_pmon_method varchar2(80);
828 l_callstat number;
829 l_ServiceInstanceId number := 0;
830 l_ServiceToBeStarted boolean := FALSE;
831
832 begin
833 l_ServiceInstanceStarted.delete;
834 l_ServiceInstanceList.delete;
835
836 FND_FILE.put_line(FND_FILE.log, 'Retrieving services instances for '||p_ServiceHandle);
837 l_ServiceInstanceList := FND_CONCURRENT.GET_SERVICE_INSTANCES
838 (svc_handle => p_ServiceHandle);
839
840 if (l_ServiceInstanceList.COUNT > 0) then
841
842 FND_FILE.put_line(FND_FILE.log, 'Service instances found: '||
843 l_ServiceInstanceList.COUNT);
844
845 for i in 1..l_ServiceInstanceList.COUNT loop
846
847 if (l_ServiceInstanceList.EXISTS(i)) then
848
849 -- TODO
850 -- Donot start if DISABLED, SUSPENDED, ACTIVE
851 -- Start if INACTIVE
852 -- But whatif TRANSIT
853 -- Possible TRANSIT statues are:
854 -- DEACTIVATING, MIGRATING, CONNECTING, TERMINATING, INITIALIZING
855 -- DEACTIVATING, TERMINATING are the problem cases where
856 -- service should also be started
857
858 l_ServiceToBeStarted := FALSE;
859
860 -- if UPPER(l_ServiceInstanceList(i).STATE) in
861 -- ('INACTIVE', 'DEACTIVATING', 'TERMINATING') then
862 if UPPER(l_ServiceInstanceList(i).STATE) = 'INACTIVE' then
863
864 l_ServiceToBeStarted := TRUE;
865
866 elsif UPPER(l_ServiceInstanceList(i).STATE) = 'TRANSIT' then
867
868 l_ServiceInstanceId := Fetch_ConcQ_ID
869 (ConcQName => l_ServiceInstanceList(i).INSTANCE_NAME);
870 if (l_ServiceInstanceId > 0) then
871
872 FND_CONCURRENT.GET_MANAGER_STATUS (
873 applid => 535,
874 managerid => l_ServiceInstanceId,
875 targetp => l_targetp,
876 activep => l_activep,
877 pmon_method => l_pmon_method,
878 callstat => l_callstat);
879
880 if l_callstat <> 0 then
881 FND_FILE.put_line(FND_FILE.log,
882 'FND_CONCURRENT.GET_MANAGER_STATUS failed, callstat: '||l_callstat);
883 else
884 FND_FILE.put_line(FND_FILE.log,'Target processes: '||l_targetp||', active processes: '||l_activep);
885 -- if l_targetp = 0 then
886 if l_activep = 0 then
887 l_ServiceToBeStarted := TRUE;
888 END IF;
889 END IF;
890 END IF;
891 END IF;
892
893 if l_ServiceToBeStarted = TRUE then
894
895 FND_FILE.put_line(FND_FILE.log,
896 'Submitting ACTIVATE request for service instance: '||
897 l_ServiceInstanceList(i).INSTANCE_NAME);
898 l_ReqID := 0;
899 l_ReqID := FND_REQUEST.SUBMIT_SVC_CTL_REQUEST (
900 command => 'ACTIVATE',
901 service => l_ServiceInstanceList(i).INSTANCE_NAME,
902 service_app => 'XDP');
903
904 if l_ReqID > 0 then
905 l_Count := l_Count + 1;
906 l_ServiceInstanceStarted(l_Count).INSTANCE_NAME :=
907 l_ServiceInstanceList(i).INSTANCE_NAME;
908 l_ServiceInstanceStarted(l_Count).Req_ID := l_ReqID;
909 FND_FILE.put_line(FND_FILE.log,
910 'Request : '||
911 l_ReqID||
912 ' successfully submitted');
913 else
914 FND_FILE.put_line(FND_FILE.log,
915 'Error, request could not be successfully submitted');
916 END IF;
917 else
918 FND_FILE.put_line(FND_FILE.log,
919 'Service instance: '||
920 l_ServiceInstanceList(i).INSTANCE_NAME||
921 ' ignored, has state: '||
922 l_ServiceInstanceList(i).STATE);
923 END IF;
924 END IF;
925 END LOOP;
926 else
927 FND_FILE.put_line(FND_FILE.log, 'No service instances found');
928 END IF;
929
930 return l_ServiceInstanceStarted;
931
932 end Start_Service_Instances;
933
934 -- ************************** XDP_START ********************************************
935 -- * INPUTS : None
936 -- * OUTPUTS: None
937 -- * RETURNS: FALSE if not entirely successful else TRUE
938 -- *
939 -- * This function starts adapter and DQ instances as per configuration.
940 -- *
941 -- * Called by: Administration scripts used to start application.
942 -- * Calls :
943 -- *
944 -- * Modification history:
945 -- * WHO WHEN WHY
946 -- * --------------------------------------------------------------------------------
947 -- ***********************************************************************************
948
949 Procedure XDP_START
950 (
951 errbuf OUT NOCOPY VARCHAR2,
952 retcode OUT NOCOPY NUMBER,
953 FeOptions IN VARCHAR2,
954 FeName IN VARCHAR2,
955 DebugMode IN VARCHAR2
956 )
957
958 IS
959
960 CURSOR c_getAdapterInfoAll IS
961 SELECT xar.channel_name, xfe.fulfillment_element_name,
962 xar.adapter_display_name, xar.startup_mode, xar.fe_id, xar.adapter_status
963 FROM xdp_adapter_reg xar, xdp_fes xfe
964 WHERE xar.adapter_status in (XDP_ADAPTER.pv_statusStopped,
965 XDP_ADAPTER.pv_statusStoppedError,
966 XDP_ADAPTER.pv_statusTerminated,
967 XDP_ADAPTER.pv_statusDeactivated,
968 XDP_ADAPTER.pv_statusDeactivatedSystem)
969 and xar.startup_mode in (XDP_ADAPTER.pv_startAutomatic,
970 XDP_ADAPTER.pv_startGroup,
971 XDP_ADAPTER.pv_startOnDemand)
972 and xfe.fe_id = xar.fe_id
973 order by xfe.fulfillment_element_name;
974
975 l_temp_fe_name VARCHAR2(2000);
976 l_temp_fe_name2 VARCHAR2(2000);
977 l_offset NUMBER;
978 l_retCode Boolean;
979 l_AdaptersRequiredTobeStarted Boolean := FALSE;
980 l_StartThisAdapter Boolean := FALSE;
981 l_ServiceInstanceStartedC SERVICE_INSTANCE_REQS_TAB;
982 l_ServiceInstanceStartedQ SERVICE_INSTANCE_REQS_TAB;
983
984 BEGIN
985 -- Verify / reset running adapters silently
986 XDP_ADAPTER.Verify_All_Adapters ( p_retcode => retCode,
987 p_errbuf => errbuf);
988
989 l_retCode := TRUE;
990 retcode := 0;
991 errbuf := '';
992
993 FND_FILE.put_line(FND_FILE.log,'Application Start Initiated.');
994
995 -- ************************************************************
996 -- Check if ICM is running
997 -- ************************************************************
998
999 if (not Is_ICM_Running('CONC')) then
1000 retCode := -1;
1001 errbuf := 'Cannot start application as ICM is not running';
1002 FND_FILE.put_line(FND_FILE.log, errbuf);
1003 COMMIT;
1004 return;
1005 end if;
1006
1007 -- ************************************************************
1008 -- Start INACTIVE Controller services instances
1009 -- ************************************************************
1010
1011 FND_FILE.put_line(FND_FILE.log,'Starting Controller services');
1012 begin
1013 l_ServiceInstanceStartedC := Start_Service_Instances (p_ServiceHandle => 'XDPCTRLS');
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 retCode := SQLCODE;
1018 errbuf := SQLERRM;
1019 FND_FILE.put_line(FND_FILE.log,
1020 'Encountered error when starting Controller services. Aborting Start Request');
1021 FND_FILE.put_line(FND_FILE.log, 'Error: ' || SUBSTR(errbuf,1,200));
1022 COMMIT;
1023 return;
1024 end;
1025 FND_FILE.put_line(FND_FILE.log,'Requests to start Controller services submitted successfully');
1026
1027 -- ************************************************************
1028 -- Start INACTIVE application queue service instances
1029 -- ************************************************************
1030
1031 for v_ServiceTypeData in c_getQServiceTypes loop
1032 FND_FILE.put_line(FND_FILE.log,'Starting '||v_ServiceTypeData.SERVICE_NAME);
1033 -- Ignore the returned list
1034 begin
1035 l_ServiceInstanceStartedQ := Start_Service_Instances
1036 (p_ServiceHandle => v_ServiceTypeData.SERVICE_HANDLE);
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 retCode := SQLCODE;
1040 errbuf := SQLERRM;
1041 FND_FILE.put_line(FND_FILE.log,
1042 'Encountered error when starting services. Aborting Start Request');
1043 FND_FILE.put_line(FND_FILE.log, 'Error: ' || SUBSTR(errbuf,1,200));
1044 COMMIT;
1045 return;
1046 end;
1047 END LOOP;
1048 FND_FILE.put_line(FND_FILE.log,'Requests to start Queue services submitted successfully');
1049
1050 -- Check if Controller instances are really started
1051 FND_FILE.put_line(FND_FILE.log,'Verifying Controller processes');
1052 l_retCode := Verify_Controller_Instances (l_ServiceInstanceStartedC);
1053
1054 if l_retCode = false then
1055 retCode := -1;
1056 errbuf := 'Could not verify Controller processes';
1057 FND_FILE.put_line(FND_FILE.log, 'Error: ' || SUBSTR(errbuf,1,200));
1058 return;
1059 end if;
1060
1061 -- ************************************************************
1062 -- Start adapters as per user request
1063 -- ************************************************************
1064
1065 l_temp_fe_name := 'NONE';
1066
1067 if FeOptions = 'ALL' and FeName is NULL then
1068
1069 -- Start all adapters
1070 FND_FILE.put_line(FND_FILE.log,'Starting all adapters');
1071 l_AdaptersRequiredTobeStarted := TRUE;
1072
1073 elsif (((FeOptions = 'INCLUDE') or (FeOptions = 'EXCLUDE'))
1074 and (FeName IS NOT NULL)) then
1075
1076 l_AdaptersRequiredTobeStarted := TRUE;
1077
1078 -- Remove leading, trailing blanks and upper case the FE names
1079 l_temp_fe_name := UPPER (LTRIM (RTRIM (FeName)));
1080
1081 if FeOptions = 'INCLUDE' then
1082 FND_FILE.put_line(FND_FILE.log,
1083 'Starting adapters associated with FE(s) :'||l_temp_fe_name||': only');
1084 else
1085 FND_FILE.put_line(FND_FILE.log,
1086 'Starting adapters for FE(s) other than :'||l_temp_fe_name||':');
1087 end if;
1088
1089 -- Replace embedded commas by blanks and finally append a blank to the FE name
1090 -- list so that all words in the list have atleast a trailing blank for
1091 -- correct matching
1092
1093 l_temp_fe_name := REPLACE (l_temp_fe_name, ',', ' ');
1094 l_temp_fe_name := RPAD (l_temp_fe_name, LENGTH(l_temp_fe_name)+1);
1095
1096 else
1097 l_AdaptersRequiredTobeStarted := FALSE;
1098 -- Handles 'other' cases i.e. no adapters to be started
1099 FND_FILE.put_line(FND_FILE.log,'No adapters started');
1100 END IF;
1101
1102 if l_AdaptersRequiredTobeStarted = TRUE then
1103
1104 for v_AdapterData in c_getAdapterInfoAll loop
1105
1106 l_StartThisAdapter := TRUE;
1107
1108 if l_temp_fe_name <> 'NONE' then
1109 -- Remove leading, trailing blanks and upper case the string and
1110 -- finally append a trailing blank to the NE name so as to match
1111 -- correctly
1112
1113 l_temp_fe_name2 := UPPER (LTRIM (RTRIM
1114 (v_AdapterData.fulfillment_element_name)));
1115 l_temp_fe_name2 := RPAD (l_temp_fe_name2,
1116 LENGTH(l_temp_fe_name2)+1);
1117 l_offset := INSTR (l_temp_fe_name, l_temp_fe_name2);
1118
1119 if (((FeOptions = 'INCLUDE') and (l_offset = 0)) or -- not found
1120 ((FeOptions = 'EXCLUDE') and (l_offset <> 0))) then -- found
1121 l_StartThisAdapter := FALSE;
1122 end if;
1123 end if;
1124
1125 if l_StartThisAdapter = TRUE then
1126
1127 if v_AdapterData.startup_mode = XDP_ADAPTER.pv_startOnDemand then
1128
1129
1130 if (v_AdapterData.adapter_status = XDP_ADAPTER.pv_statusDeactivatedSystem) then
1131
1132 -- Reset the error count and adapter status of the DeactivatedSystem StartOnDemand Adapter
1133 XDP_ADAPTER.Reset_SysDeactivated_Adapter (
1134 p_ChannelName => v_AdapterData.channel_name);
1135 FND_FILE.put_line(FND_FILE.log,
1136 'Restart count and status of Start-on-demand Adapter '||
1137 v_AdapterData.adapter_display_name||
1138 ' successfully reinitialized');
1139
1140 else
1141 -- Reset the error count of the StartOnDemand Adapter
1142 XDP_ADAPTER.Reset_SysDeactivated_Adapter (
1143 p_ChannelName => v_AdapterData.channel_name,
1144 p_ResetStatusFlag => false);
1145
1146 FND_FILE.put_line(FND_FILE.log,
1147 'Restart count of Start-on-demand Adapter '||
1148 v_AdapterData.adapter_display_name||
1149 ' successfully reinitialized');
1150
1151 END IF;
1152 else
1153 -- For Automatic adapters START_ADAPTER will reset
1154 -- the restart count
1155
1156 FND_FILE.put_line(FND_FILE.log,
1157 'Attempting to start adapter :'||
1158 v_AdapterData.adapter_display_name||
1159 ': associated with FE :'||
1160 v_AdapterData.fulfillment_element_name||':');
1161
1162 XDP_ADAPTER.START_ADAPTER (
1163 p_ChannelName => v_AdapterData.channel_name,
1164 p_retcode => retcode,
1165 p_errbuf => errbuf);
1166
1167 IF retcode <> 0 THEN
1168 FND_FILE.put_line(FND_FILE.log,
1169 'Error in starting adapter '||
1170 v_AdapterData.adapter_display_name);
1171 FND_FILE.put_line(FND_FILE.log,errbuf);
1172 l_retCode := FALSE;
1173 else
1174 FND_FILE.put_line(FND_FILE.log,
1175 'Adapter '||v_AdapterData.adapter_display_name||
1176 ' started successfully');
1177 END IF;
1178
1179 END IF;
1180
1181 commit;
1182 end if;
1183
1184 END LOOP;
1185 end if;
1186
1187 if (l_retCode = TRUE) then
1188 FND_FILE.put_line(FND_FILE.log, 'Application started successfully');
1189 else
1190 retcode := -1;
1191 errbuf := 'Application started with warnings or errors';
1192 FND_FILE.put_line(FND_FILE.log, errbuf);
1193 END IF;
1194
1195 COMMIT;
1196
1197 EXCEPTION
1198
1199 WHEN OTHERS THEN
1200 IF c_getQServiceTypes%ISOPEN THEN
1201 CLOSE c_getQServiceTypes;
1202 end if;
1203 IF c_getAdapterInfoAll%ISOPEN THEN
1204 CLOSE c_getAdapterInfoAll;
1205 end if;
1206 FND_FILE.put_line(FND_FILE.log,'SQL code: '||SQLCODE);
1207 FND_FILE.put_line(FND_FILE.log,'SQL message string: '||SQLERRM);
1208 retcode := SQLCODE;
1209 errbuf := SUBSTR(SQLERRM,1,200);
1210 COMMIT;
1211
1212 END XDP_START;
1213
1214 -- ************************** XDP_CM_SHUTDOWN ****************************************
1215 -- * INPUTS : None
1216 -- * OUTPUTS: None
1217 -- * RETURNS: None
1218 -- *
1219 -- * This procedure stops the application's Concurrent Manager in abort mode
1220 -- *
1221 -- * Called by:
1222 -- * Calls :
1223 -- *
1224 -- * Modification history:
1225 -- * WHO WHEN WHY
1226 -- 02/23/2001 sacsharm Modified
1227 -- Bug fix 1653820. Reverted back to abort mode in case of CM shutdown callback.
1228 -- * --------------------------------------------------------------------------------
1229 -- ***********************************************************************************
1230 Procedure XDP_CM_SHUTDOWN
1231
1232 is
1233
1234 errbuf VARCHAR2 (2000);
1235 retcode NUMBER;
1236
1237 begin
1238 -- Bug 2396384 fix
1239 -- 11.5.6 onwards this Shutdown Callback is no longer needed
1240
1241 --XDP_STOP (
1242 -- errbuf => errbuf,
1243 -- retcode => retcode,
1244 -- FeOptions => 'ALL',
1245 -- FeName => NULL,
1246 -- StopOptions => 'NORMAL');
1247
1248 null;
1249
1250 END XDP_CM_SHUTDOWN;
1251
1252 -- ************************** FETCH_CPID ****************************************
1253 -- * INPUTS : None
1254 -- * OUTPUTS: None
1255 -- * RETURNS: None
1256 -- *
1257 -- * Called by:
1258 -- * Calls :
1259 -- *
1260 -- * Modification history:
1261 -- * WHO WHEN WHY
1262 -- * --------------------------------------------------------------------------------
1263 Function Fetch_CPID(ConcQID in number, Caller in varchar2 default 'SERV') return number
1264 is
1265
1266 l_CPID number := -1;
1267 l_targetp number := 0;
1268 l_activep number := 0;
1269 l_pmon_method varchar2(80);
1270 l_callstat number;
1271 l_SIProcessList FND_CONCURRENT.SERVICE_PROCESS_TAB_TYPE;
1272 l_ConcQName varchar2(30);
1273
1274 begin
1275 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1276 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'XDP_APPLICATION.FETCH_CPID', 'BEGIN:');
1277 end if;
1278 if Caller = 'CONC' then
1279 FND_FILE.put_line(FND_FILE.log, 'Checking if ServiceInstanceId '||ConcQID||' is running');
1280 END IF;
1281
1282 l_SIProcessList.delete;
1283
1284 FND_CONCURRENT.GET_MANAGER_STATUS (
1285 applid => 535,
1286 managerid => ConcQID,
1287 targetp => l_targetp,
1288 activep => l_activep,
1289 pmon_method => l_pmon_method,
1290 callstat => l_callstat);
1291
1292 if l_callstat = 0 then
1293 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1294 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_CPID',
1295 'Target processes: '||l_targetp||', active processes: '||l_activep);
1296 end if;
1297
1298 if Caller = 'CONC' then
1299 FND_FILE.put_line(FND_FILE.log, 'Target processes: '||l_targetp||', active processes: '||l_activep);
1300 END IF;
1301
1302 if l_activep > 0 and l_targetp > 0 then
1303
1304 l_ConcQName := Fetch_ConcQ_Name (ConcQID => ConcQID);
1305 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1306 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_CPID',
1307 'Retrieving ACTIVE services processes for '||l_ConcQName);
1308 end if;
1309
1310 if Caller = 'CONC' then
1311 FND_FILE.put_line(FND_FILE.log, 'Retrieving ACTIVE services processes for '||l_ConcQName);
1312 END IF;
1313
1314 l_SIProcessList := FND_CONCURRENT.GET_SERVICE_PROCESSES
1315 (appl_short_name => 'XDP',
1316 svc_instance_name => l_ConcQName,
1317 proc_state => 'ACTIVE');
1318
1319 if (l_SIProcessList.COUNT > 0) then
1320 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1321 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_CPID',
1322 'ACTIVE processes for the instance found: '||l_SIProcessList.COUNT);
1323 end if;
1324
1325 if Caller = 'CONC' then
1326 FND_FILE.put_line(FND_FILE.log, l_SIProcessList.COUNT||' ACTIVE processes for the instance found');
1327 END IF;
1328
1329 for i in 1..l_SIProcessList.COUNT loop
1330
1331 if (l_SIProcessList.EXISTS(i)) then
1332
1333 l_CPID := l_SIProcessList(i).CPID;
1334 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1335 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
1336 'XDP_APPLICATION.FETCH_CPID',
1337 'ACTIVE process CPID: '||l_CPID);
1338 end if;
1339
1340 exit;
1341
1342 END IF;
1343 END LOOP;
1344 else
1345 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1346 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_CPID',
1347 'No ACTIVE processes for Service found');
1348 end if;
1349 if Caller = 'CONC' then
1350 FND_FILE.put_line(FND_FILE.log, 'No ACTIVE processes for Service found');
1351 END IF;
1352 END IF;
1353
1354 else
1355 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1356 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_CPID',
1357 'ACTIVE and TARGET processes not greater than 0');
1358 end if;
1359 if Caller = 'CONC' then
1360 FND_FILE.put_line(FND_FILE.log, 'ACTIVE and TARGET processes not greater than 0, so service instance is not running');
1361 END IF;
1362 end if;
1363 else
1364 if( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1365 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, 'XDP_APPLICATION.FETCH_CPID',
1366 'FND_CONCURRENT.GET_MANAGER_STATUS failed, callstat: '||l_callstat);
1367 end if;
1368 if Caller = 'CONC' then
1369 FND_FILE.put_line(FND_FILE.log, 'FND_CONCURRENT.GET_MANAGER_STATUS failed, callstat: '||l_callstat);
1370 END IF;
1371 end if;
1372
1373 -- dbms_output.put_line('CPID: ' || l_CPID);
1374
1375 return (l_CPID);
1376
1377 end Fetch_CPID;
1378
1379 -- ************************** FETCH_CONCQ_DETAILS ***********************************
1380 -- * INPUTS : None
1381 -- * OUTPUTS: None
1382 -- * RETURNS: None
1383 -- *
1384 -- * Called by:
1385 -- * Calls :
1386 -- *
1387 -- * Modification history:
1388 -- * WHO WHEN WHY
1389 -- * --------------------------------------------------------------------------------
1390 Procedure Fetch_ConcQ_Details (
1391 CPID in number,
1392 ConcQID OUT NOCOPY number,
1393 ConcQName OUT NOCOPY varchar2)
1394 is
1395
1396 cursor c_GetConcQID is
1397 select a.concurrent_queue_id, b.concurrent_queue_name
1398 from fnd_concurrent_processes a, fnd_concurrent_queues b
1399 where a.concurrent_process_id = CPID and
1400 a.concurrent_queue_id = b.concurrent_queue_id and
1401 a.queue_application_id = b.application_id;
1402
1403 begin
1404 ConcQID := -1;
1405
1406 for v_GetConcQID in c_GetConcQID loop
1407 ConcQID := v_GetConcQID.concurrent_queue_id;
1408 ConcQName := v_GetConcQID.concurrent_queue_name;
1409 exit;
1410 end loop;
1411
1412 -- dbms_output.put_line('ConcQID: ' || ConcQID);
1413 -- dbms_output.put_line('ConcQName: ' || ConcQName);
1414
1415 end Fetch_ConcQ_Details;
1416
1417
1418 -- ************************** FETCH_THREAD_CNT *********************************
1419 -- * INPUTS : svc_handle - Service Handle of a Service
1420 -- * OUTPUTS: num_of_threads - Number of threads running actively for that service
1421 -- * RETURNS: none
1422 -- *
1423 -- * This procedure returns the Thread Count for a given service through the Service Parameters
1424 -- *
1425 -- * Called by: OAM Console for SFM
1426 -- * Calls :
1427 -- *
1428 -- * Modification history:
1429 -- * WHO WHEN WHY
1430 -- * ---------------------------------------------------------------------------
1431 -----
1432 -- *****************************************************************************
1433
1434 PROCEDURE FETCH_THREAD_CNT (svc_handle IN VARCHAR2,
1435 num_of_threads OUT NOCOPY NUMBER)
1436 IS
1437 l_ServiceInstanceList FND_CONCURRENT.SERVICE_INSTANCE_TAB_TYPE;
1438 l_SIProcessList FND_CONCURRENT.SERVICE_PROCESS_TAB_TYPE;
1439 l_ServiceInstanceId number := 0;
1440 p_num_of_threads number;
1441 l_InstanceName varchar2(30);
1442 l_Parameters varchar2(2000);
1443
1444 BEGIN
1445
1446 num_of_threads := 0;
1447
1448 BEGIN
1449
1450 l_ServiceInstanceList := FND_CONCURRENT.GET_SERVICE_INSTANCES(svc_handle);
1451
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1455 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'FND_CONCURRENT.GET_SERVICE_INSTANCES returned exception: ' ||to_char(SQLCODE) ||SQLERRM);
1456 end if;
1457 num_of_threads := -1;
1458 END;
1459
1460
1461 IF (l_ServiceInstanceList.COUNT > 0) THEN
1462 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1463 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'No. of service_instances found: '||l_ServiceInstanceList.COUNT);
1464 end if;
1465
1466 FOR i in 1..l_ServiceInstanceList.COUNT LOOP
1467
1468 IF (l_ServiceInstanceList.EXISTS(i)) THEN
1469
1470 l_InstanceName := l_ServiceInstanceList(i).INSTANCE_NAME;
1471
1472 BEGIN
1473
1474 l_SIProcessList := FND_CONCURRENT.GET_SERVICE_PROCESSES
1475 (appl_short_name => 'XDP',
1476 svc_instance_name => l_InstanceName,
1477 proc_state => 'ACTIVE');
1478
1479 IF (l_SIProcessList.COUNT > 0) THEN
1480 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1481 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'ACTIVE processes for the instance found: '||l_SIProcessList.COUNT);
1482 end if;
1483
1484 FOR i IN 1..l_SIProcessList.COUNT LOOP
1485
1486 IF (l_SIProcessList.EXISTS(i)) THEN
1487 l_Parameters := l_SIProcessList(i).PARAMETERS;
1488 p_num_of_threads := GET_COMPONENT_THREADS(l_Parameters, 'XDP_DQ_INIT_NUM_THREADS=');
1489 EXIT;
1490 END IF;
1491
1492 END LOOP;
1493 num_of_threads := num_of_threads + (l_SIProcessList.COUNT * p_num_of_threads);
1494 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1495 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'Number or Threads running for a service: '||num_of_threads);
1496 end if;
1497
1498 ELSE
1499 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1500 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'No ACTIVE processes for Service found');
1501 end if;
1502
1503 END IF;
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1508 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'FND_CONCURRENT.GET_SERVICE_PROCESS returned exception: ' ||to_char(SQLCODE) ||SQLERRM);
1509 end if;
1510 END;
1511 END IF;
1512 END LOOP;
1513
1514 ELSE
1515 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1516 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_APPLICATION.FETCH_THREAD_CNT', 'No Service Instances found for service_handle: ' ||svc_handle );
1517 end if;
1518
1519 END IF;
1520
1521 END FETCH_THREAD_CNT;
1522
1523
1524 -- ************************** GET_COMPONENT_THREADS ****************************
1525 -- * INPUTS : p_service_params_str - Service Parameter string
1526 -- * p_tag - The tag to be parsed
1527 -- * OUTPUTS: none
1528 -- * RETURNS: Value for the tag viz. Thread Count
1529 -- *
1530 -- * This function parses the given string, and returns its value
1531 -- *
1532 -- * Called by: Procedure FETCH_THREAD_CNT
1533 -- * Calls :
1534 -- *
1535 -- * Modification history:
1536 -- * WHO WHEN WHY
1537 -- * ---------------------------------------------------------------------------
1538 -----
1539 -- *****************************************************************************
1540
1541 FUNCTION GET_COMPONENT_THREADS (p_service_params_str IN VARCHAR2,
1542 p_tag IN VARCHAR2)
1543 RETURN NUMBER
1544
1545 IS
1546 l_index number;
1547 l_num_threads varchar2(3);
1548
1549 BEGIN
1550
1551 l_index := instr(p_service_params_str, p_tag) + length(p_tag);
1552 l_num_threads := substr(p_service_params_str, l_index);
1553 return TO_NUMBER(l_num_threads);
1554
1555 END GET_COMPONENT_THREADS;
1556
1557
1558 -- ************************** SUBMIT_SVC_CTL_REQUEST *********************************
1559 -- * INPUTS : None
1560 -- * OUTPUTS: None
1561 -- * RETURNS: None
1562 -- *
1563 -- * Called by:
1564 -- * Calls :
1565 -- *
1566 -- * Modification history:
1567 -- * WHO WHEN WHY
1568 -- * --------------------------------------------------------------------------------
1569 Function Submit_Svc_Ctl_Request (
1570 CPID in number, CtlCmd in varchar2)
1571 return number
1572 is
1573
1574 l_ReqID number := 0;
1575 l_ConcQID number := -1;
1576 l_ConcQName varchar2(30);
1577
1578 begin
1579 if (not Is_ICM_Running ('SERV')) then
1580 return l_ReqID;
1581 END IF;
1582
1583 Fetch_ConcQ_Details (
1584 CPID => CPID,
1585 ConcQID => l_ConcQID,
1586 ConcQName => l_ConcQName);
1587
1588 if l_ConcQID > 0 then
1589 l_ReqID := FND_REQUEST.SUBMIT_SVC_CTL_REQUEST (
1590 command => CtlCmd,
1591 service => l_ConcQName,
1592 service_app => 'XDP');
1593 end if;
1594
1595 return l_ReqID;
1596
1597 end Submit_Svc_Ctl_Request;
1598
1599 -- ************************** FETCH_CONCQ_NAME **************************************
1600 -- * INPUTS : None
1601 -- * OUTPUTS: None
1602 -- * RETURNS: None
1603 -- *
1604 -- * Called by:
1605 -- * Calls :
1606 -- *
1607 -- * Modification history:
1608 -- * WHO WHEN WHY
1609 -- * --------------------------------------------------------------------------------
1610 Function Fetch_ConcQ_Name (ConcQID in number) return varchar2
1611 is
1612
1613 cursor c_GetConcQName is
1614 select a.concurrent_queue_name
1615 from fnd_concurrent_queues a
1616 where a.concurrent_queue_id = ConcQID and
1617 a.application_id = 535;
1618
1619 l_ConcQName varchar2(30) := '';
1620 begin
1621
1622 for v_GetConcQName in c_GetConcQName loop
1623 l_ConcQName := v_GetConcQName.concurrent_queue_name;
1624 exit;
1625 end loop;
1626
1627 -- dbms_output.put_line('ConcQName: ' || l_ConcQName);
1628 return l_ConcQName;
1629
1630 end Fetch_ConcQ_Name;
1631
1632 -- ************************** FETCH_CONCQ_ID ****************************************
1633 -- * INPUTS : None
1634 -- * OUTPUTS: None
1635 -- * RETURNS: None
1636 -- *
1637 -- * Called by:
1638 -- * Calls :
1639 -- *
1640 -- * Modification history:
1641 -- * WHO WHEN WHY
1642 -- * --------------------------------------------------------------------------------
1643 Function Fetch_ConcQ_ID (ConcQName in varchar2) return number
1644 is
1645
1646 cursor c_GetConcQID is
1647 select a.concurrent_queue_id
1648 from fnd_concurrent_queues a
1649 where upper(a.concurrent_queue_name) = upper(ConcQName);
1650
1651 l_ConcQID number := -1;
1652 begin
1653
1654 for v_GetConcQID in c_GetConcQID loop
1655 l_ConcQID := v_GetConcQID.concurrent_queue_id;
1656 exit;
1657 end loop;
1658
1659 -- dbms_output.put_line('ConcQID: ' || l_ConcQID);
1660 return l_ConcQID;
1661
1662 end Fetch_ConcQ_ID;
1663
1664 --begin
1665 -- pv_AckTimeout := 30;
1666
1667
1668 -- ************************** XDP_CONSOLE_COUNTS_FENGINE *********************************
1669 -- * This procedure returns the Counts for a given Queue Service
1670 -- *
1671 -- * Called by: OAM Console for SFM
1672 -- * Calls :
1673 -- * Created by: Maya
1674 -- *
1675 -- * Modification history:
1676 -- * WHO WHEN WHY
1677 -- * ---------------------------------------------------------------------------
1678 --
1679 -- *****************************************************************************
1680
1681
1682 PROCEDURE XDP_CONSOLE_COUNTS_FENGINE(
1683 p_order_threads OUT NOCOPY NUMBER
1684 ,p_order_current OUT NOCOPY NUMBER
1685 ,p_order_future OUT NOCOPY NUMBER
1686 ,p_order_exception OUT NOCOPY NUMBER
1687 ,p_order_inprogress OUT NOCOPY NUMBER
1688 ,p_order_inerror OUT NOCOPY NUMBER
1689 ,p_order_completed OUT NOCOPY NUMBER
1690 ,p_wi_threads OUT NOCOPY NUMBER
1691 ,p_wi_current OUT NOCOPY NUMBER
1692 ,p_wi_future OUT NOCOPY NUMBER
1693 ,p_wi_exception OUT NOCOPY NUMBER
1694 ,p_wi_inprogress OUT NOCOPY NUMBER
1695 ,p_wi_inerror OUT NOCOPY NUMBER
1696 ,p_wi_completed OUT NOCOPY NUMBER
1697 ,p_fa_threads OUT NOCOPY NUMBER
1698 ,p_fa_current OUT NOCOPY NUMBER
1699 ,p_fa_future OUT NOCOPY NUMBER
1700 ,p_fa_exception OUT NOCOPY NUMBER
1701 ,p_fa_inprogress OUT NOCOPY NUMBER
1702 ,p_fa_inerror OUT NOCOPY NUMBER
1703 ,p_fa_completed OUT NOCOPY NUMBER
1704 ,p_fa_ready_current OUT NOCOPY NUMBER
1705 ,p_fa_ready_future OUT NOCOPY NUMBER
1706 ,p_fa_ready_exception OUT NOCOPY NUMBER
1707 ,p_timer_threads OUT NOCOPY NUMBER
1708 ,p_timer_current OUT NOCOPY NUMBER
1709 ,p_timer_future OUT NOCOPY NUMBER
1710 ,p_timer_exception OUT NOCOPY NUMBER
1711 ,p_timer_inprogress OUT NOCOPY NUMBER
1712 ,p_timer_completed OUT NOCOPY NUMBER
1713 ,p_event_threads OUT NOCOPY NUMBER
1714 ,p_event_current OUT NOCOPY NUMBER
1715 ,p_event_future OUT NOCOPY NUMBER
1716 ,p_event_exception OUT NOCOPY NUMBER
1717 ,p_event_inprogress OUT NOCOPY NUMBER
1718 ,p_event_inerror OUT NOCOPY NUMBER
1719 ,p_event_completed OUT NOCOPY NUMBER
1720 ,p_in_threads OUT NOCOPY NUMBER
1721 ,p_in_current OUT NOCOPY NUMBER
1722 ,p_in_future OUT NOCOPY NUMBER
1723 ,p_in_exception OUT NOCOPY NUMBER
1724 ,p_in_inprogress OUT NOCOPY NUMBER
1725 ,p_in_inerror OUT NOCOPY NUMBER
1726 ,p_in_completed OUT NOCOPY NUMBER
1727 ,p_out_current OUT NOCOPY NUMBER
1728 ,p_out_exception OUT NOCOPY NUMBER
1729 ,p_out_inprogress OUT NOCOPY NUMBER
1730 ,p_out_inerror OUT NOCOPY NUMBER
1731 ,p_out_completed OUT NOCOPY NUMBER
1732 ) IS
1733
1734 l_order_current number := 0;
1735 l_order_future1 number := 0;
1736 l_order_future2 number := 0;
1737 l_order_future number := 0;
1738 l_order_exception number := 0;
1739 l_order_inprogress number := 0;
1740 l_order_inerror number := 0;
1741 l_order_completed number := 0;
1742 l_order_threads number := 0;
1743 l_wi_current number := 0;
1744 l_wi_future1 number := 0;
1745 l_wi_future2 number := 0;
1746 l_wi_future number := 0;
1747 l_wi_exception number := 0;
1748 l_wi_inprogress number := 0;
1749 l_wi_inerror number := 0;
1750 l_wi_completed number := 0;
1751 l_wi_threads number := 0;
1752 l_fa_current number := 0;
1753 l_fa_future number := 0;
1754 l_fa_exception number := 0;
1755 l_fa_inprogress number := 0;
1756 l_fa_inerror number := 0;
1757 l_fa_completed number := 0;
1758 l_fa_threads number := 0;
1759 l_fa_ready_current number := 0;
1760 l_fa_ready_future number := 0;
1761 l_fa_ready_exception number := 0;
1762 l_timer_current number := 0;
1763 l_timer_future number := 0;
1764 l_timer_exception number := 0;
1765 l_timer_inprogress number := 0;
1766 l_timer_completed number := 0;
1767 l_timer_threads number := 0;
1768 l_event_current number := 0;
1769 l_event_future number := 0;
1770 l_event_exception number := 0;
1771 l_event_inprogress number := 0;
1772 l_event_inerror number := 0;
1773 l_event_completed number := 0;
1774 l_event_threads number := 0;
1775 l_in_current number := 0;
1776 l_in_future number := 0;
1777 l_in_exception number := 0;
1778 l_in_inprogress number := 0;
1779 l_in_inerror number := 0;
1780 l_in_completed number := 0;
1781 l_in_threads number := 0;
1782 l_out_current number := 0;
1783 l_out_exception number := 0;
1784 l_out_inprogress number := 0;
1785 l_out_inerror number := 0;
1786 l_out_completed number := 0;
1787
1788
1789 /*****Order Load*****/
1790 cursor c_order_load is
1791 select count(*)count, msg_state
1792 from AQ$XDP_ORDER_PROCESSOR_QTAB
1793 group by msg_state;
1794
1795 /*****Order Volume*****/
1796 cursor c_order_volume is
1797 select count(*)count, status_code
1798 from xdp_order_headers
1799 group by status_code;
1800
1801 /*****WI Load*******/
1802 cursor c_wi_load is
1803 select count(*)count, msg_state
1804 from AQ$XDP_WORKITEM_QTAB
1805 group by msg_state;
1806
1807 /*****WI Volume*****/
1808 cursor c_wi_volume is
1809 select count(*)count, status_code
1810 from XDP_FULFILL_WORKLIST
1811 group by status_code;
1812
1813 /*****FA Load*****/
1814 cursor c_fa_load is
1815 select count(*)count, msg_state
1816 from AQ$XDP_FA_QTAB
1817 group by msg_state;
1818
1819 /****FA Volume****/
1820 cursor c_fa_volume is
1821 select count(*)count, status_code
1822 from XDP_FA_RUNTIME_LIST
1823 group by status_code;
1824
1825 /*****FA Ready******/
1826 cursor c_fa_ready_load is
1827 select count(*)count,msg_state
1828 from AQ$XDP_WF_CHANNEL_QTAB
1829 group by msg_state;
1830
1831 /*****Timers Load*******/
1832 cursor c_timer_load is
1833 select count(*)count, msg_state
1834 from AQ$XNP_IN_TMR_QTAB
1835 group by msg_state;
1836
1837 /*****Timers Volume******/
1838 cursor c_timer_volume is
1839 select count(*)count, status
1840 FROM XNP_TIMER_REGISTRY
1841 group by status;
1842
1843 /*****Events Load******/
1844 cursor c_event_load is
1845 select count(*)count, msg_state
1846 from AQ$XNP_IN_EVT_QTAB
1847 group by msg_state;
1848
1849 /*****Inbound Load****/
1850 cursor c_in_load is
1851 select count(*)count,msg_state
1852 from AQ$XNP_IN_MSG_QTAB
1853 group by msg_state;
1854
1855 /*****Outbound Load****/
1856 cursor c_out_load is
1857 select count(*)count, msg_state
1858 from AQ$XNP_OUT_MSG_QTAB
1859 group by msg_state;
1860
1861 /*****Events, Invound, Outbound Volume*****/
1862 cursor c_comb_volume is
1863 select count(*)count, msg_status, direction_indicator
1864 from XNP_MSGS
1865 group by msg_status, direction_indicator;
1866
1867 BEGIN
1868
1869 /*********Order Load and Volume*********/
1870
1871 FOR v_order in c_order_load LOOP
1872 IF v_order.msg_state = 'READY' THEN
1873 l_order_current := v_order.count;
1874 ELSIF v_order.msg_state = 'WAIT' THEN
1875 l_order_future1 := v_order.count;
1876 ELSIF v_order.msg_state = 'EXPIRED' THEN
1877 l_order_exception := v_order.count;
1878 ELSE
1879 null;
1880 END IF;
1881 END LOOP;
1882
1883
1884 FOR v_order in c_order_volume LOOP
1885 IF v_order.status_code IN ('READY', 'IN PROGRESS') THEN
1886 l_order_inprogress := l_order_inprogress + v_order.count;
1887 ELSIF v_order.status_code IN ('ERROR') THEN
1888 l_order_inerror := l_order_inerror + v_order.count;
1889 ELSIF v_order.status_code IN ('CANCELLED', 'ABORTED', 'SUCCESS', 'SUCCESS_WITH_OVERRIDE') THEN
1890 l_order_completed := l_order_completed + v_order.count;
1891 ELSIF v_order.status_code = 'STANDBY' THEN
1892 l_order_future2 := l_order_future2 + v_order.count;
1893 ELSE
1894 null;
1895 END IF;
1896 END LOOP;
1897
1898 l_order_future := l_order_future1 + l_order_future2;
1899
1900 FETCH_THREAD_CNT(svc_handle => 'XDPQORDS'
1901 ,num_of_threads => l_order_threads);
1902
1903 p_order_threads := l_order_threads;
1904 p_order_current := l_order_current;
1905 p_order_future := l_order_future;
1906 p_order_exception := l_order_exception;
1907 p_order_inprogress := l_order_inprogress;
1908 p_order_inerror := l_order_inerror;
1909 p_order_completed := l_order_completed;
1910
1911
1912 /********Workitem Load and Volume********/
1913
1914 FOR v_wi in c_wi_load LOOP
1915 IF v_wi.msg_state = 'READY' THEN
1916 l_wi_current := v_wi.count;
1917 ELSIF v_wi.msg_state = 'WAIT' THEN
1918 l_wi_future1 := v_wi.count;
1919 ELSIF v_wi.msg_state = 'EXPIRED' THEN
1920 l_wi_exception := v_wi.count;
1921 ELSE
1922 null;
1923 END IF;
1924 END LOOP;
1925
1926
1927 FOR v_wi in c_wi_volume LOOP
1928 IF v_wi.status_code IN ('READY', 'IN PROGRESS') THEN
1929 l_wi_inprogress := l_wi_inprogress + v_wi.count;
1930 ELSIF v_wi.status_code IN ('ERROR') THEN
1931 l_wi_inerror := l_wi_inerror + v_wi.count;
1932 ELSIF v_wi.status_code IN ('CANCELLED', 'ABORTED', 'SUCCESS', 'SUCCESS_WITH_OVERRIDE') THEN
1933 l_wi_completed := l_wi_completed + v_wi.count;
1934 ELSIF v_wi.status_code = 'STANDBY' THEN
1935 l_wi_future2 := l_wi_future2 + v_wi.count;
1936 ELSE
1937 null;
1938 END IF;
1939 END LOOP;
1940 l_wi_future := l_wi_future1 + l_wi_future2;
1941
1942 FETCH_THREAD_CNT(svc_handle => 'XDPQWIS'
1943 ,num_of_threads => l_wi_threads);
1944
1945 p_wi_threads := l_wi_threads;
1946 p_wi_current := l_wi_current;
1947 p_wi_future := l_wi_future;
1948 p_wi_exception := l_wi_exception;
1949 p_wi_inprogress := l_wi_inprogress;
1950 p_wi_inerror := l_wi_inerror;
1951 p_wi_completed := l_wi_completed;
1952
1953
1954 /*********Fulfillment Action Load and Volume*******/
1955
1956
1957 FOR v_fa in c_fa_load LOOP
1958 IF v_fa.msg_state = 'READY' THEN
1959 l_fa_current := v_fa.count;
1960 ELSIF v_fa.msg_state = 'EXPIRED' THEN
1961 l_fa_exception := v_fa.count;
1962 ELSE
1963 null;
1964 END IF;
1965 END LOOP;
1966
1967 FOR v_fa in c_fa_volume LOOP
1968 IF v_fa.status_code IN ('READY', 'IN PROGRESS') THEN
1969 l_fa_inprogress := l_fa_inprogress + v_fa.count;
1970 ELSIF v_fa.status_code IN ('ERROR') THEN
1971 l_fa_inerror := l_fa_inerror + v_fa.count;
1972 ELSIF v_fa.status_code IN ('CANCELLED', 'ABORTED', 'SUCCESS', 'SUCCESS_WITH_OVERRIDE') THEN
1973 l_fa_completed := l_fa_completed + v_fa.count;
1974 ELSIF v_fa.status_code = 'STANDBY' THEN
1975 l_fa_future := l_fa_future + v_fa.count;
1976 ELSE
1977 null;
1978 END IF;
1979 END LOOP;
1980
1981 FETCH_THREAD_CNT(svc_handle => 'XDPQFAS'
1982 ,num_of_threads => l_fa_threads);
1983
1984
1985
1986 p_fa_threads := l_fa_threads;
1987 p_fa_current := l_fa_current;
1988 p_fa_future := l_fa_future;
1989 p_fa_exception := l_fa_exception;
1990 p_fa_inprogress := l_fa_inprogress;
1991 p_fa_inerror := l_fa_inerror;
1992 p_fa_completed := l_fa_completed;
1993
1994
1995 /******Fulfillment Action Ready*******/
1996
1997 FOR v_fa_ready in c_fa_ready_load LOOP
1998 IF v_fa_ready.msg_state = 'READY' THEN
1999 l_fa_ready_current := v_fa_ready.count;
2000 ELSIF v_fa_ready.msg_state = 'EXPIRED' THEN
2001 l_fa_ready_exception := v_fa_ready.count;
2002 ELSE
2003 null;
2004 END IF;
2005 END LOOP;
2006
2007 select count(*)
2008 into l_fa_ready_future
2009 from XDP_FA_RUNTIME_LIST
2010 where status_code = 'WAITING_FOR_RESOURCE';
2011
2012
2013 p_fa_ready_current := l_fa_ready_current;
2014 p_fa_ready_future := l_fa_ready_future;
2015 p_fa_ready_exception := l_fa_ready_exception;
2016
2017
2018
2019
2020 /******Timers*********/
2021
2022 FOR v_timer in c_timer_load LOOP
2023 IF v_timer.msg_state = 'READY' THEN
2024 l_timer_current := v_timer.count;
2025 ELSIF v_timer.msg_state = 'WAIT' THEN
2026 l_timer_future := v_timer.count;
2027 ELSIF v_timer.msg_state = 'EXPIRED' THEN
2028 l_timer_exception := v_timer.count;
2029 ELSE
2030 null;
2031 END IF;
2032 END LOOP;
2033
2034 select count(*)
2035 into l_timer_future
2036 from XNP_IN_TMR_QTAB
2037 where nvl(time_manager_info,SYSDATE) > SYSDATE;
2038
2039 FOR v_timer in c_timer_volume LOOP
2040 IF v_timer.status = 'ACTIVE' THEN
2041 l_timer_inprogress := l_timer_inprogress + v_timer.count;
2042 ELSIF v_timer.status IN ('EXPIRED', 'REMOVED') THEN
2043 l_timer_completed := l_timer_completed + v_timer.count;
2044 ELSE
2045 null;
2046 END IF;
2047 END LOOP;
2048
2049 FETCH_THREAD_CNT(svc_handle => 'XDPQTMRS'
2050 ,num_of_threads => l_timer_threads);
2051
2052
2053 p_timer_threads := l_timer_threads;
2054 p_timer_current := l_timer_current;
2055 p_timer_future := l_timer_future;
2056 p_timer_exception := l_timer_exception;
2057 p_timer_inprogress := l_timer_inprogress;
2058 p_timer_completed := l_timer_completed;
2059
2060
2061 /******Events**********/
2062
2063 FOR v_event in c_event_load LOOP
2064 IF v_event.msg_state = 'READY' THEN
2065 l_event_current := v_event.count;
2066 ELSIF v_event.msg_state = 'EXPIRED' THEN
2067 l_event_exception := v_event.count;
2068 ELSE
2069 null;
2070 END IF;
2071 END LOOP;
2072
2073 select count(*)
2074 into l_event_future
2075 from xnp_callback_events xce,
2076 xnp_msg_types_b xmt
2077 where xce.msg_code = xmt.msg_code and
2078 xce.status = 'WAITING' and
2079 xmt.msg_type IN ('EVENT', 'EVT_NOHEAD');
2080
2081 FETCH_THREAD_CNT(svc_handle => 'XDPQEVTS'
2082 ,num_of_threads => l_event_threads);
2083
2084
2085 p_event_threads := l_event_threads;
2086 p_event_current := l_event_current;
2087 p_event_exception := l_event_exception;
2088 p_event_future := l_event_future;
2089
2090
2091 /*****Inbound Messages*****/
2092
2093 FOR v_in in c_in_load LOOP
2094 IF v_in.msg_state = 'READY' THEN
2095 l_in_current := v_in.count;
2096 ELSIF v_in.msg_state = 'EXPIRED' THEN
2097 l_in_exception := v_in.count;
2098 ELSE
2099 null;
2100 END IF;
2101 END LOOP;
2102
2103 select count(*)
2104 into l_in_future
2105 from xnp_callback_events xce,
2106 xnp_msg_types_b xmt
2107 where xce.msg_code = xmt.msg_code and
2108 xce.status = 'WAITING' and
2109 xmt.msg_type = 'MSG' and
2110 xmt.queue_name = 'XNP_IN_MSG_Q';
2111
2112 FETCH_THREAD_CNT(svc_handle => 'XDPQMSGS'
2113 ,num_of_threads => l_in_threads);
2114
2115 p_in_threads := l_in_threads;
2116 p_in_current := l_in_current;
2117 p_in_exception := l_in_exception;
2118 p_in_future := l_in_future;
2119
2120
2121 /*****Outbound Message*****/
2122
2123 FOR v_out in c_out_load LOOP
2124 IF v_out.msg_state = 'READY' THEN
2125 l_out_current := v_out.count;
2126 ELSIF v_out.msg_state = 'EXPIRED' THEN
2127 l_out_exception := v_out.count;
2128 ELSE
2129 null;
2130 END IF;
2131 END LOOP;
2132
2133 p_out_current := l_out_current;
2134 p_out_exception := l_out_exception;
2135
2136
2137
2138 /******Event, Inbound, Outbound Volume******/
2139
2140 FOR v_comb in c_comb_volume LOOP
2141 IF v_comb.msg_status = 'READY' AND
2142 v_comb.direction_indicator = 'E' THEN
2143 l_event_inprogress := l_event_inprogress + v_comb.count;
2144 ELSIF v_comb.msg_status = 'FAILED' AND
2145 v_comb.direction_indicator = 'E' THEN
2146 l_event_inerror := l_event_inerror + v_comb.count;
2147 ELSIF v_comb.msg_status = 'PROCESSED' AND
2148 v_comb.direction_indicator = 'E' THEN
2149 l_event_completed := l_event_completed + v_comb.count;
2150 ELSIF v_comb.msg_status = 'READY' AND
2151 v_comb.direction_indicator = 'I' THEN
2152 l_in_inprogress := l_in_inprogress + v_comb.count;
2153 ELSIF v_comb.msg_status = 'FAILED' AND
2154 v_comb.direction_indicator = 'I' THEN
2155 l_in_inerror := l_in_inerror + v_comb.count;
2156 ELSIF v_comb.msg_status = 'PROCESSED' AND
2157 v_comb.direction_indicator = 'I' THEN
2158 l_in_completed := l_in_completed + v_comb.count;
2159 ELSIF v_comb.msg_status = 'READY' AND
2160 v_comb.direction_indicator = 'O' THEN
2161 l_out_inprogress := l_out_inprogress + v_comb.count;
2162 ELSIF v_comb.msg_status = 'FAILED' AND
2163 v_comb.direction_indicator = 'O' THEN
2164 l_out_inerror := l_out_inerror + v_comb.count;
2165 ELSIF v_comb.msg_status = 'PROCESSED' AND
2166 v_comb.direction_indicator = 'O' THEN
2167 l_out_completed := l_out_completed + v_comb.count;
2168 ELSE
2169 null;
2170 END IF;
2171 END LOOP;
2172
2173 p_event_inprogress := l_event_inprogress;
2174 p_event_inerror := l_event_inerror;
2175 p_event_completed := l_event_completed;
2176
2177 p_in_inprogress := l_in_inprogress;
2178 p_in_inerror := l_in_inerror;
2179 p_in_completed := l_in_completed;
2180
2181 p_out_inprogress := l_out_inprogress;
2182 p_out_inerror := l_out_inerror;
2183 p_out_completed := l_out_completed;
2184
2185
2186 END XDP_CONSOLE_COUNTS_FENGINE;
2187
2188 end XDP_APPLICATION;