[Home] [Help]
PACKAGE BODY: APPS.XDP_Q_ADMIN_PKG
Source
1 PACKAGE BODY XDP_Q_ADMIN_PKG AS
2 /* $Header: XDPQADMB.pls 120.4 2006/04/10 23:21:23 dputhiye ship $ */
3
4 /********** Commented out - START - sacsharm - 11.5.6 **************
5
6 PROCEDURE VerifyDQProcesses (
7 p_q_name IN VARCHAR2,
8 p_dq_count OUT NUMBER,
9 p_sql_code OUT NUMBER,
10 p_sql_desc OUT VARCHAR2);
11
12 -- ************************** PROCEDURE UPDATE_Q_STATUS **************************************
13 -- * Author: Sachin Sharma
14 -- * Date Created: April 20, 1999
15 -- *
16 -- * INPUTS : Queue name
17 -- * Action code
18 -- * OUTPUTS: Standard error handling parameters
19 -- * RETURNS: None
20 -- *
21 -- * This procedure updates the state of an queue. State of an queue has impact on the
22 -- * DQ processes for e.g. if queue state is updated to SHUTDOWN, related DQ processes
23 -- * automatically shutdown.
24 -- *
25 -- * Called by: Console UI to suspend, resume, start, stop queue.
26 -- * Calls :
27 -- * XDP_AQ_UTILITIES.SHUTDOWN_SDP_AQ
28 -- * XDP_AQ_UTILITIES.ENABLE_SDP_AQ
29 -- * FND_MESSAGE.CLEAR
30 -- * FND_MESSAGE.SET_NAME
31 -- * FND_MESSAGE.SET_TOKEN
32 -- * FND_MESSAGE.GET
33 -- *
34 -- * Modification history:
35 -- * WHO WHEN WHY
36 -- * -----------------------------------------------------------------------------------------
37 -- ********************************************************************************************
38
39 PROCEDURE Update_Q_Status (
40 p_q_name IN VARCHAR2,
41 p_action_code IN VARCHAR2,
42 p_sql_code OUT NUMBER,
43 p_sql_desc OUT VARCHAR2)
44 IS
45
46 l_CurrentState VARCHAR2(10);
47 l_CurrentCount NUMBER;
48 l_ResourceName VARCHAR2 (80);
49 l_IncorrectQStateError BOOLEAN := TRUE;
50 l_Status VARCHAR2 (1024);
51
52 e_ResourceBusy EXCEPTION;
53 e_IncorrectQState EXCEPTION;
54 e_CalledProgErr EXCEPTION;
55 pragma exception_init (e_ResourceBusy, -00054);
56
57 begin
58 p_sql_code := 0;
59 p_sql_desc := '';
60
61 -- Lock the row related to the Q and get the display name
62
63 SELECT display_name, state, num_of_dqer INTO l_ResourceName, l_CurrentState, l_CurrentCount
64 FROM XDP_DQ_CONFIGURATION_VL
65 --skilaru 03/28/2001
66 --WHERE UPPERi(nternal_q_name) = UPPER(p_q_name)
67 WHERE internal_q_name = UPPER(p_q_name)
68 and DQ_PROC_NAME <> 'NODQPROC'
69 FOR UPDATE NOWAIT;
70
71 if (p_action_code = 'STARTUP') then
72 if (l_CurrentState = 'SHUTDOWN') then
73
74 XDP_Q_ADMIN_PKG.START_Q (
75 p_q_name,
76 l_ResourceName,
77 l_CurrentState,
78 l_CurrentCount,
79 1,
80 'NON_CONC_JOB',
81 p_sql_code,
82 p_sql_desc);
83
84 if (p_sql_code <> 0) then
85 RAISE e_CalledProgErr;
86 end if;
87
88 elsif (l_CurrentState = 'ENABLED') then
89
90 -- Warn only -- already started
91 l_IncorrectQStateError := FALSE;
92 RAISE e_IncorrectQState;
93 else
94 RAISE e_IncorrectQState;
95 END IF;
96
97 elsif (p_action_code = 'SHUTDOWN') then
98 if (l_CurrentState <> 'SHUTDOWN') then
99 XDP_AQ_UTILITIES.SHUTDOWN_SDP_AQ (p_q_name, p_sql_code, p_sql_desc);
100 if (p_sql_code <> 0) then
101 RAISE e_CalledProgErr;
102 end if;
103 else
104 -- Warn only already stopped ?
105 l_IncorrectQStateError := FALSE;
106 RAISE e_IncorrectQState;
107 END IF;
108
109 elsif (p_action_code = 'SUSPEND') then
110 if (l_CurrentState = 'ENABLED') then
111 update XDP_DQ_CONFIGURATION set
112 STATE = 'SUSPENDED',
113 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
114 LAST_UPDATE_DATE = SYSDATE,
115 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
116 --skilaru 03/28/2001
117 --where UPPER(INTERNAL_Q_NAME) = UPPER(p_q_name);
118 where INTERNAL_Q_NAME = UPPER(p_q_name);
119 elsif (l_CurrentState = 'SUSPENDED') then
120 -- Warn already suspended ?
121 l_IncorrectQStateError := FALSE;
122 RAISE e_IncorrectQState;
123 else
124 RAISE e_IncorrectQState;
125 END IF;
126
127 elsif (p_action_code = 'RESUME') then
128 if (l_CurrentState = 'SUSPENDED') then
129 XDP_AQ_UTILITIES.ENABLE_SDP_AQ (p_q_name, p_sql_code, p_sql_desc);
130 if (p_sql_code <> 0) then
131 RAISE e_CalledProgErr;
132 end if;
133 else
134 RAISE e_IncorrectQState;
135 END IF;
136 else
137 -- Development time error message, no translation required
138 p_sql_code := -20001;
139 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.UPDATE_Q_STATUS. Invalid action code :'
140 ||p_action_code||': passed';
141 ROLLBACK;
142 return;
143 END IF;
144
145 COMMIT;
146
147 EXCEPTION
148
149 WHEN e_ResourceBusy THEN
150 p_sql_code := -54;
151 FND_MESSAGE.CLEAR;
152 FND_MESSAGE.SET_NAME ('XDP', 'RESOURCE_BUSY');
153 FND_MESSAGE.SET_TOKEN ('RESOURCE_NAME', l_ResourceName);
154 p_sql_desc := FND_MESSAGE.GET;
155 ROLLBACK;
156 -- DBMS_OUTPUT.PUT_LINE (p_sql_desc);
157 -- APP_EXCEPTION.RAISE_EXCEPTION;
158
159
160 WHEN e_IncorrectQState THEN
161 p_sql_code := -20001;
162 FND_MESSAGE.CLEAR;
163 if (l_IncorrectQStateError = FALSE) then -- Warn case
164 FND_MESSAGE.SET_NAME ('XDP', 'OPERATION_ALREADY_PERFORMED');
165 else
166 FND_MESSAGE.SET_NAME ('XDP', 'INCORRECT_Q_STATE');
167 FND_MESSAGE.SET_TOKEN ('CURRENT_STATE', l_CurrentState);
168 END IF;
169 p_sql_desc := FND_MESSAGE.GET;
170 ROLLBACK;
171 -- DBMS_OUTPUT.PUT_LINE (p_sql_desc);
172 -- APP_EXCEPTION.RAISE_EXCEPTION;
173
174 WHEN e_CalledProgErr THEN
175 ROLLBACK;
176 -- APP_EXCEPTION.RAISE_EXCEPTION;
177
178 WHEN OTHERS THEN
179 if SQLCODE <> 0 then
180 p_sql_code := SQLCODE;
181 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.UPDATE_Q_STATUS, Error Desc.: '||
182 SQLERRM, 1, 2000);
183 else
184 p_sql_code := -20001;
185 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.UPDATE_Q_STATUS, Other non-SQL error';
186 END IF;
187
188 FND_MESSAGE.CLEAR;
189 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
190 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
191 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
192 p_sql_desc := FND_MESSAGE.GET;
193 ROLLBACK;
194 -- DBMS_OUTPUT.PUT_LINE (p_sql_desc);
195 -- APP_EXCEPTION.RAISE_EXCEPTION;
196
197 END UPDATE_Q_STATUS;
198
199 -- ****************************** PROCEDURE START_Q *******************************************
200 -- * Author: Sachin Sharma
201 -- * Date Created: April 20, 1999
202 -- *
203 -- * INPUTS : Queue name
204 -- * OUTPUTS: Standard error handling parameters
205 -- * RETURNS: None
206 -- *
207 -- * This procedure checks whether the DQers processes associated with a queue have
208 -- * shutdown, if yes, it enables the queue and starts the DQer processes associated with it.
209 -- *
210 -- * Called by: Administration utilities
211 -- * XDP_Q_ADMIN_PKG.UPDATE_Q_STATUS
212 -- * Calls :
213 -- * XDP_ADAPTER_ADMIN.LockVerifyController;
214 -- * XDP_AQ_UTILITIES.ENABLE_SDP_AQ
215 -- * XDP_RECOVERY.CHECKNSTARTDQPROCESSES
216 -- * FND_MESSAGE.CLEAR;
217 -- * FND_MESSAGE.SET_NAME
218 -- * FND_MESSAGE.SET_TOKEN
219 -- * FND_MESSAGE.GET
220 -- *
221 -- * Modification history:
222 -- * WHO WHEN WHY
223 -- * -----------------------------------------------------------------------------------------
224 -- ****************************************************************************************
225
226 PROCEDURE Start_Q (
227 p_q_name IN VARCHAR2,
228 p_q_display_name IN VARCHAR2,
229 p_q_state IN VARCHAR2,
230 p_q_count IN NUMBER,
231 p_max_tries IN NUMBER DEFAULT 1,
232 p_caller IN VARCHAR2 DEFAULT 'NON_CONC_JOB',
233 p_sql_code OUT NUMBER,
234 p_sql_desc OUT VARCHAR2)
235 IS
236
237 l_Status VARCHAR2 (1024);
238 l_dq_count NUMBER := 0;
239 l_ControllerFlag1 BOOLEAN := FALSE;
240 l_ControllerFlag2 BOOLEAN := FALSE;
241 l_RetNumber NUMBER := 0;
242
243 e_CalledProgErr EXCEPTION;
244 e_ShutDownInProgress EXCEPTION;
245 e_ResourceBusy EXCEPTION;
246
247 pragma exception_init (e_ResourceBusy, -00054);
248
249 begin
250 p_sql_code := 0;
251 p_sql_desc := '';
252
253 -- No need to lock the row related to the queue as already locked by the
254 -- caller Update_Q_Status or Start_All_Qs
255
256 XDP_ADAPTER_ADMIN.LockVerifyController
257 (
258 errbuf => p_sql_desc,
259 retcode => p_sql_code,
260 IsControllerLocked => l_ControllerFlag1,
261 IsControllerDown => l_ControllerFlag2,
262 MaxTries => p_max_tries,
263 MaxTriesLock => p_max_tries,
264 Caller => p_caller
265 );
266
267 if p_sql_code <> 0 then
268 if p_caller = 'CONC_JOB' then
269 FND_FILE.put_line(FND_FILE.log,
270 'LockVerifyController returned error');
271 FND_FILE.put_line(FND_FILE.log, p_sql_desc);
272 END IF;
273 RAISE e_CalledProgErr;
274 else
275 l_dq_count := 0;
276
277 if p_caller = 'CONC_JOB' then
278 FND_FILE.put_line(FND_FILE.output,
279 'Verifying the DQ processes for the queue');
280 END IF;
281
282 VerifyDQProcesses (
283 p_q_name,
284 l_dq_count,
285 p_sql_code,
286 p_sql_desc);
287
288 if p_sql_code <> 0 then
289 if p_caller = 'CONC_JOB' then
290 FND_FILE.put_line(FND_FILE.log,
291 'VerifyDQProcesses returned error');
292 FND_FILE.put_line(FND_FILE.log, p_sql_desc);
293 END IF;
294 RAISE e_CalledProgErr;
295
296 else
297
298 if p_caller = 'CONC_JOB' then
299 FND_FILE.put_line(FND_FILE.output,
300 'Found '||l_dq_count
301 ||' DQ processes running for the queue');
302 END IF;
303
304 if p_q_state = 'SHUTDOWN' then
305
306 if l_dq_count > 0 then
307 if p_caller = 'CONC_JOB' then
308 FND_FILE.put_line(FND_FILE.log,
309 'Cannot start the queue, shutdown is in progress');
310 END IF;
311 RAISE e_ShutdownInProgress;
312 else
313 -- Enable the queue
314
315 if p_caller = 'CONC_JOB' then
316 FND_FILE.put_line(FND_FILE.output,
317 'Enabling the queue');
318 END IF;
319
320 XDP_AQ_UTILITIES.ENABLE_SDP_AQ (p_q_name, p_sql_code, p_sql_desc);
321 if p_sql_code <> 0 then
322 if p_caller = 'CONC_JOB' then
323 FND_FILE.put_line(FND_FILE.log,
324 'Enable queue returned error');
325 FND_FILE.put_line(FND_FILE.log, p_sql_desc);
326 END IF;
327 RAISE e_CalledProgErr;
328 else
329
330 -- Reason: Fixed BUG 1085175 By: sacsharm On: 06-Dec-1999
331
332 -- Commit required else DQ processes might not see the
333 -- Q status to be enabled but this will release all locks
334 -- But for this COMMIT no commits, rollbacks required
335 -- as they are present in caller logic
336
337 COMMIT;
338 END IF;
339
340 END IF;
341
342 -- COMMIT done Lock the queue and Controller again
343
344 SELECT 1 INTO l_RetNumber
345 FROM XDP_DQ_CONFIGURATION
346 --skilaru 03/27/2001
347 --WHERE UPPER(internal_q_name) = UPPER(p_q_name)
348 WHERE internal_q_name = UPPER(p_q_name)
349 FOR UPDATE NOWAIT;
350
351 XDP_ADAPTER_ADMIN.LockVerifyController
352 (
353 errbuf => p_sql_desc,
354 retcode => p_sql_code,
355 IsControllerLocked => l_ControllerFlag1,
356 IsControllerDown => l_ControllerFlag2,
357 MaxTries => p_max_tries,
358 MaxTriesLock => p_max_tries,
359 Caller => p_caller
360 );
361
362 if p_sql_code <> 0 then
363 if p_caller = 'CONC_JOB' then
364 FND_FILE.put_line(FND_FILE.log,
365 'Could not lock the Controller again');
366 FND_FILE.put_line(FND_FILE.log, p_sql_desc);
367 END IF;
368
369 -- Revert back the status in case of error
370
371 update XDP_DQ_CONFIGURATION
372 set STATE = 'SHUTDOWN',
373 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
374 LAST_UPDATE_DATE = sysdate,
375 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
376 where internal_q_name = UPPER(p_q_name);
377 COMMIT;
378
379 RAISE e_CalledProgErr;
380 END IF;
381
382 elsif p_q_state = 'ENABLED' then
383
384 if p_q_count = l_dq_count then
385 if p_caller = 'CONC_JOB' then
386 FND_FILE.put_line(FND_FILE.output,
387 'Required number of DQ processes '||
388 p_q_count||' already running');
389 END IF;
390 return;
391 else
392 if p_caller = 'CONC_JOB' then
393 FND_FILE.put_line(FND_FILE.output,
394 'Some DQ processes are required to be restarted');
395 END IF;
396
397 END IF;
398 END IF;
399
400 END IF;
401
402 if p_caller = 'CONC_JOB' then
403 FND_FILE.put_line(FND_FILE.output,
404 'Attempting to start DQ processes');
405 END IF;
406
407 -- Start DQ processes as per configuration
408 XDP_RECOVERY.CheckNStartDQProcesses (p_q_name, TRUE, l_Status,
409 p_sql_code, p_sql_desc);
413 if p_caller = 'CONC_JOB' then
410
411 if p_sql_code = 0 then
412 -- Donot commit in success case, caller will
414 FND_FILE.put_line(FND_FILE.output,
415 'DQ processes successfully started');
416 END IF;
417 else
418 if p_caller = 'CONC_JOB' then
419 FND_FILE.put_line(FND_FILE.log,
420 'CheckNStartDQProcesses returned error');
421 FND_FILE.put_line(FND_FILE.log, p_sql_desc);
422 END IF;
423 -- Here we are commiting as some DQ processes at OS may
424 -- have been started
425 COMMIT;
426 END IF;
427
428 END IF;
429
430
431 EXCEPTION
432
433 WHEN e_ResourceBusy THEN
434 p_sql_code := -54;
435 FND_MESSAGE.CLEAR;
436 FND_MESSAGE.SET_NAME ('XDP', 'RESOURCE_BUSY');
437 FND_MESSAGE.SET_TOKEN ('RESOURCE_NAME', p_q_display_name);
438 p_sql_desc := FND_MESSAGE.GET;
439 -- Revert back the status in case of error
440 -- Logic will come here only if queue cannot be locked after
441 -- first COMMIT
442 if p_caller = 'CONC_JOB' then
443 FND_FILE.put_line(FND_FILE.log,
444 'Could not lock the queue again');
445 END IF;
446 update XDP_DQ_CONFIGURATION
447 set STATE = 'SHUTDOWN',
448 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
449 LAST_UPDATE_DATE = sysdate,
450 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
451 where internal_q_name = UPPER(p_q_name);
452 COMMIT;
453
454 WHEN e_ShutdownInProgress THEN
455 p_sql_code := -20001;
456 FND_MESSAGE.CLEAR;
457 FND_MESSAGE.SET_NAME ('XDP', 'Q_SHUTDOWN_IN_PROGRESS');
458 FND_MESSAGE.SET_TOKEN ('QUEUE_NAME', p_q_display_name);
459 p_sql_desc := FND_MESSAGE.GET;
460
461 WHEN e_CalledProgErr THEN
462 NULL;
463
464 WHEN OTHERS THEN
465 if SQLCODE <> 0 then
466 p_sql_code := SQLCODE;
467 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.START_Q, Error Desc.: '
468 || SQLERRM, 1, 2000);
469 else
470 p_sql_code := -20001;
471 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.START_Q, Other non-SQL error';
472 END IF;
473
474 FND_MESSAGE.CLEAR;
475 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
476 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
477 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
478 p_sql_desc := FND_MESSAGE.GET;
479
480 END START_Q;
481
482
483 -- *************************** PROCEDURE START_ALL_QS ******************************************
484 -- * Author: Sachin Sharma
485 -- * Date Created: Dec. 09 1999
486 -- *
487 -- * INPUTS : None
488 -- * OUTPUTS: Standard error handling parameters
489 -- * RETURNS: None
490 -- *
491 -- * This procedure checks whether the DQers processes associated with all queue have
492 -- * shutdown, if yes, it enables the queues and starts the DQer processes associated with them.
493 -- *
494 -- * Called by: Administration utilities
495 -- * XDP_ADAPTER_ADMIN.XDP_START
496 -- * Calls :
497 -- * XDP_Q_ADMIN.START_Q
498 -- *
499 -- * Modification history:
500 -- * WHO WHEN WHY
501 -- * -----------------------------------------------------------------------------------------
502 -- *******************************************************************************************
503
504 PROCEDURE Start_All_Qs (
505 p_caller IN VARCHAR2 DEFAULT 'CONC_JOB',
506 p_sql_code OUT NUMBER,
507 p_sql_desc OUT VARCHAR2)
508 IS
509
510 -- If the caller is CONC_JOB i.e. application start then start all queues
511 -- in state SHUTDOWN and ENABLED else i.e. it is called from Watchdog
512 -- start queues having state ENABLED only. In former resultant state list
513 -- will be ('ENABLED', 'SHUTDOWN') and in latter it will be ('ENABLED', 'ENABLED')
514
515 cursor c_GetQList (lc_caller VARCHAR2) is
516 select INTERNAL_Q_NAME, DISPLAY_NAME, STATE, NUM_OF_DQER
517 from XDP_DQ_CONFIGURATION_VL
518 where DQ_PROC_NAME <> 'NODQPROC'
519 and STATE in
520 ('ENABLED', decode (lc_caller, 'CONC_JOB', 'SHUTDOWN', 'ENABLED'))
521 order by STATE, INTERNAL_Q_NAME;
522
523 l_RetNumber NUMBER := 0;
524 l_SomeErrorFlag BOOLEAN := FALSE;
525
526 e_ResourceBusy EXCEPTION;
527 e_CalledProgErr EXCEPTION;
528
529 pragma exception_init (e_ResourceBusy, -00054);
530
531 begin
532 p_sql_code := 0;
533 p_sql_desc := '';
534
535 for v_QData in c_GetQList (p_caller) loop
536
537 if p_caller = 'CONC_JOB' then
538 if v_QData.STATE = 'SHUTDOWN' then
539 FND_FILE.put_line(FND_FILE.output,
540 'Attempting to start the queue: '||v_QData.INTERNAL_Q_NAME);
541 else
542 FND_FILE.put_line(FND_FILE.output,
543 'Verifying the queue: '||v_QData.INTERNAL_Q_NAME);
544 END IF;
545 END IF;
546
547 -- Lock the current row
548
549 begin
550
551 SELECT 1 INTO l_RetNumber
552 FROM XDP_DQ_CONFIGURATION
553 --skilaru 03/27/2001
554 --WHERE UPPER(internal_q_name) = UPPER(v_QData.INTERNAL_Q_NAME)
555 WHERE internal_q_name = UPPER(v_QData.INTERNAL_Q_NAME)
556 FOR UPDATE NOWAIT;
557
558 EXCEPTION
559
560 WHEN e_ResourceBusy THEN
564 'Could not lock the queue: '||v_QData.INTERNAL_Q_NAME);
561 l_SomeErrorFlag := TRUE;
562 if p_caller = 'CONC_JOB' then
563 FND_FILE.put_line(FND_FILE.log,
565 END IF;
566 GOTO l_EndOfLoop;
567
568 END;
569
570 XDP_Q_ADMIN_PKG.START_Q (
571 v_QData.INTERNAL_Q_NAME,
572 v_QData.DISPLAY_NAME,
573 v_QData.STATE,
574 v_QData.NUM_OF_DQER,
575 1,
576 'CONC_JOB',
577 p_sql_code,
578 p_sql_desc);
579
580 if p_sql_code <> 0 then
581 l_SomeErrorFlag := TRUE;
582 if p_caller = 'CONC_JOB' then
583 FND_FILE.put_line(FND_FILE.log,
584 'Error in starting/verifying the queue: '||v_QData.INTERNAL_Q_NAME);
585 FND_FILE.put_line(FND_FILE.log,p_sql_desc);
586 END IF;
587 ROLLBACK;
588 else
589 if p_caller = 'CONC_JOB' then
590 if v_QData.STATE = 'SHUTDOWN' then
591 FND_FILE.put_line(FND_FILE.output,
592 'Successfully started the queue: '
593 ||v_QData.INTERNAL_Q_NAME);
594 else
595 FND_FILE.put_line(FND_FILE.output,
596 'Successfully verified the queue: '
597 ||v_QData.INTERNAL_Q_NAME);
598 END IF;
599 END IF;
600 COMMIT;
601 END IF;
602
603 <<l_EndOfLoop>>
604 null;
605
606 END LOOP;
607
608 if l_SomeErrorFlag = TRUE then
609 p_sql_code := -20001;
610 p_sql_desc := 'Some application queues started with warnings';
611 END IF;
612
613 EXCEPTION
614
615 WHEN OTHERS THEN
616
617 if SQLCODE <> 0 then
618 p_sql_code := SQLCODE;
619 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.START_ALL_QS, Error Desc.: '||
620 SQLERRM, 1, 2000);
621 else
622 p_sql_code := -20001;
623 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.START_ALL_QS, Other non-SQL error';
624 END IF;
625
626 if c_GetQlist%ISOPEN then
627 CLOSE c_GetQList;
628 END IF;
629 ROLLBACK;
630 FND_MESSAGE.CLEAR;
631 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
632 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
633 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
634 p_sql_desc := FND_MESSAGE.GET;
635
636 END START_ALL_QS;
637
638
639 -- **************************** PROCEDURE CHECK_Q_STATUS **************************************
640 -- * Author: Sachin Sharma
641 -- * Date Created: April 20, 1999
642 -- *
643 -- * INPUTS : Queue name
644 -- * OUTPUTS: DQ process count
645 -- * Standard error handling parameters
646 -- * RETURNS: None
647 -- *
648 -- * This proceduer checks the status of an queue and returns the number of DQ processes running.
649 -- *
650 -- * Called by: Console UI
651 -- * Calls :
652 -- * XDP_ADAPTER_ADMIN.LockVerifyController;
653 -- * FND_MESSAGE.CLEAR;
654 -- * FND_MESSAGE.SET_NAME
655 -- * FND_MESSAGE.SET_TOKEN
656 -- * FND_MESSAGE.GET
657 -- *
658 -- * Modification history:
659 -- * WHO WHEN WHY
660 -- * -------------------------------------------------------------------------------------------
661 -- **********************************************************************************************
662 PROCEDURE Check_Q_Status (
663 p_q_name IN VARCHAR2,
664 p_dq_count OUT NUMBER,
665 p_sql_code OUT NUMBER,
666 p_sql_desc OUT VARCHAR2)
667 IS
668
669 l_ControllerFlag1 BOOLEAN := FALSE;
670 l_ControllerFlag2 BOOLEAN := FALSE;
671
672 e_CalledProgErr EXCEPTION;
673
674 begin
675 p_sql_code := 0;
676 p_sql_desc := '';
677 p_dq_count := 0;
678
679 XDP_ADAPTER_ADMIN.LockVerifyController
680 (
681 errbuf => p_sql_desc,
682 retcode => p_sql_code,
683 IsControllerLocked => l_ControllerFlag1,
684 IsControllerDown => l_ControllerFlag2,
685 MaxTries => 1,
686 MaxTriesLock => 1,
687 Caller => 'NON_CONC_JOB'
688 );
689
690 if p_sql_code <> 0 then
691 RAISE e_CalledProgErr;
692 else
693 VerifyDQProcesses (
694 p_q_name,
695 p_dq_count,
696 p_sql_code,
697 p_sql_desc);
698
699 if p_sql_code <> 0 then
700 RAISE e_CalledProgErr;
701 END IF;
702 END IF;
703
704 COMMIT;
705
706 EXCEPTION
707
708 WHEN e_CalledProgErr THEN
709 ROLLBACK;
710
711 WHEN OTHERS THEN
712 FND_MESSAGE.CLEAR;
713 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
714
715 if SQLCODE <> 0 then
716 p_sql_code := SQLCODE;
717 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.CHECK_Q_STATUS, Error Desc.: '
718 || SQLERRM, 1, 2000);
719 else
720 p_sql_code := -20001;
721 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.CHECK_Q_STATUS, Other non-SQL error';
722 END IF;
723
724 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
725 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
726 p_sql_desc := FND_MESSAGE.GET;
727 ROLLBACK;
728 -- DBMS_OUTPUT.PUT_LINE (p_sql_desc);
729 -- APP_EXCEPTION.RAISE_EXCEPTION;
730
731 END CHECK_Q_STATUS;
732
736 -- *
733 -- **************************** PROCEDURE GET_Q_ERRORS ****************************************
734 -- * Author: Sachin Sharma
735 -- * Date Created: April 20, 1999
737 -- * INPUTS : Queue name
738 -- * OUTPUTS: List of translated messages associated with the queue
739 -- * Standard error handling parameters
740 -- * RETURNS: None
741 -- *
742 -- * This procedure returns the exceptions associated with a queue.
743 -- *
744 -- * Called by: Console UI.
745 -- * Calls :
746 -- *
747 -- * Modification history:
748 -- * WHO WHEN WHY
749 -- * ------------------------------------------------------------------------------------------
750 -- ********************************************************************************************
751 PROCEDURE Get_Q_Errors (
752 p_q_name IN VARCHAR2,
753 p_message_list OUT XDP_TYPES.MESSAGE_LIST,
754 p_sql_code OUT NUMBER,
755 p_sql_desc OUT VARCHAR2)
756 IS
757 -- cursor c_GetMessagesForQ (q_name VARCHAR2) is
758 -- select excep.ERROR_REF_ID, excep.TIME_STAMP
759 -- from XDP_DQ_EXCEPTIONS excep
760 --skilaru 03/27/2001
761 --where UPPER(excep.Q_NAME) = UPPER(q_name) and excep.Q_NAME is not NULL
762 -- where excep.Q_NAME = UPPER(q_name) and excep.Q_NAME is not NULL
763 -- order by TIME_STAMP desc;
764
765 -- Changed - sacsharm - 11.5.6 ErrorHandling changes
766 cursor c_GetMessagesForQ (q_name VARCHAR2) is
767 select excep.MESSAGE, excep.ERROR_TIMESTAMP
768 from XDP_ERROR_LOG_V excep
769 where excep.OBJECT_KEY = UPPER(q_name) and excep.OBJECT_TYPE = 'QUEUE'
770 order by ERROR_TIMESTAMP desc;
771
772 l_count NUMBER;
773
774 begin
775 p_sql_code := 0;
776 p_sql_desc := '';
777 p_message_list.DELETE;
778 l_count := 1;
779
780 for v_QExceps in c_GetMessagesForQ (p_q_name) loop
781 p_message_list(l_count).MESSAGE_TIME := v_QExceps.ERROR_TIMESTAMP;
782 p_message_list(l_count).MESSAGE_TEXT := v_QExceps.MESSAGE;
783 l_count := l_count + 1;
784 END LOOP;
785
786 EXCEPTION
787
788 WHEN OTHERS THEN
789
790 FND_MESSAGE.CLEAR;
791 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
792 if SQLCODE <> 0 then
793 p_sql_code := SQLCODE;
794 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.GET_Q_ERRORS, Error Desc.: '|| SQLERRM, 1, 2000);
795 else
796 p_sql_code := -20001;
797 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.GET_Q_ERRORS, Other non-SQL error';
798 END IF;
799
800 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
801 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
802 p_sql_desc := FND_MESSAGE.GET;
803 if c_GetMessagesForQ%ISOPEN then
804 CLOSE c_GetMessagesForQ;
805 END IF;
806 p_message_list.DELETE;
807 ROLLBACK;
808
809 -- DBMS_OUTPUT.PUT_LINE (p_sql_desc);
810 -- APP_EXCEPTION.RAISE_EXCEPTION;
811
812 END GET_Q_ERRORS;
813
814
815
816
817
818 PROCEDURE VerifyDQProcesses (
819 p_q_name IN VARCHAR2,
820 p_dq_count OUT NUMBER,
821 p_sql_code OUT NUMBER,
822 p_sql_desc OUT VARCHAR2)
823 IS
824
825 cursor c_GetDQPIDsForQ (q_name VARCHAR2) is
826 select DQER_PROCESS_ID
827 from XDP_DQER_REGISTRATION a
828 --skilaru 03/28/2001
829 --where UPPER(a.INTERNAL_Q_NAME) = UPPER(q_name )
830 where a.INTERNAL_Q_NAME = UPPER(q_name )
831 FOR UPDATE OF DQER_PROCESS_ID NOWAIT
832 order by DQER_PROCESS_ID;
833
834 l_Status VARCHAR2 (1024);
835
836 e_CalledProgErr EXCEPTION;
837
838 begin
839 p_sql_code := 0;
840 p_sql_desc := '';
841 p_dq_count := 0;
842
843 for v_DQProcessData in c_GetDQPIDsForQ (p_q_name) loop
844
845 l_Status := 'FAILURE';
846
847 XDP_RECOVERY.ValidatePID (v_DQProcessData.DQER_PROCESS_ID,
848 l_Status, p_sql_code, p_sql_desc);
849 if p_sql_code <> 0 then
850 RAISE e_CalledProgErr;
851 else
852 if l_Status = 'SUCCESS' then
853 -- Process running
854 p_dq_count := p_dq_count + 1;
855 else
856 -- Redundant row, process not running
857 -- Delete the row from XDP_DQER_REGISTRATION
858 DELETE XDP_DQER_REGISTRATION
859 WHERE CURRENT OF c_GetDQPIDsForQ;
860 END IF;
861 END IF;
862
863 END LOOP;
864
865 EXCEPTION
866
867 WHEN e_CalledProgErr THEN
868 if c_GetDQPIDsForQ%ISOPEN then
869 CLOSE c_GetDQPIDsForQ;
870 END IF;
871
872 WHEN OTHERS THEN
873 if SQLCODE <> 0 then
874 p_sql_code := SQLCODE;
875 p_sql_desc := SUBSTR ('Location: XDP_Q_ADMIN_PKG.VerifyDQProcesses, Error Desc.: '||
876 SQLERRM, 1, 2000);
877 else
878 p_sql_code := -20001;
879 p_sql_desc := 'Location: XDP_Q_ADMIN_PKG.VerifyDQProcesses, Other non-SQL error';
880 END IF;
881
882 IF c_GetDQPIDsForQ%ISOPEN THEN
883 CLOSE c_GetDQPIDsForQ;
884 END IF;
885 FND_MESSAGE.CLEAR;
886 FND_MESSAGE.SET_NAME ('XDP', 'INTERNAL_ERROR');
887 FND_MESSAGE.SET_TOKEN ('ERROR_CODE', p_sql_code);
888 FND_MESSAGE.SET_TOKEN ('ERROR_DESC', p_sql_desc);
889 p_sql_desc := FND_MESSAGE.GET;
890
894
891 END VerifyDQProcesses;
892
893 *********** Commented out - END - sacsharm - 11.5.6 *************/
895 END XDP_Q_ADMIN_PKG;