[Home] [Help]
PACKAGE BODY: APPS.XNP_TIMER_CORE
Source
1 PACKAGE BODY xnp_timer_core AS
2 /* $Header: XNPTBLPB.pls 120.1 2005/06/17 03:55:24 appldev $ */
3
4 -----------------------------------------------------------------------------
5 ---- Name : Remove_Timer_From_AQ
6 ---- Purpose : Remove timer from AQ
7 -----------------------------------------------------------------------------
8 PROCEDURE remove_timer_from_aq(
9 p_timer_id IN NUMBER,
10 x_error_code OUT NOCOPY NUMBER,
11 x_error_message OUT NOCOPY VARCHAR2
12 ) ;
13
14 -----------------------------------------------------------------------------
15 -----------------------------------------------------------------------------
16 ---- Name : Recalculate
17 ---- Purpose : Recalculate the delay and interval for the timer
18 -----------------------------------------------------------------------------
19 -----------------------------------------------------------------------------
20 PROCEDURE recalculate(
21 p_reference_id IN VARCHAR2
22 ,p_timer_message_code IN VARCHAR2
23 ,x_error_code OUT NOCOPY NUMBER
24 ,x_error_message OUT NOCOPY VARCHAR2
25 )
26 IS
27 CURSOR c_timer_data IS
28 SELECT timer_id, timer_message_code,
29 next_timer,start_time
30 FROM xnp_timer_registry
31 WHERE reference_id = p_reference_id
32 AND (timer_message_code = p_timer_message_code
33 OR next_timer = p_timer_message_code
34 )
35 AND status = 'ACTIVE';
36
37 l_timer_id NUMBER DEFAULT NULL;
38 l_next_timer_code VARCHAR2(80) DEFAULT NULL;
39 l_timer_code VARCHAR2(80) DEFAULT NULL;
40 l_status VARCHAR2(80);
41 l_start_time DATE;
42 l_elapsed_time NUMBER;
43 l_current_time DATE := sysdate;
44 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
45 l_old_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
46 l_msg_text VARCHAR2(32767);
47 l_new_interval NUMBER;
48 l_new_delay NUMBER;
49 l_interval_text VARCHAR2(40);
50 l_delay_text VARCHAR2(40);
51 l_interval NUMBER;
52 l_delay NUMBER;
53
54 l_EXCEPTION EXCEPTION; -- adabholk 06142000 for error trapping
55
56 BEGIN
57 -- Retrieve the timer_id
58 OPEN c_timer_data;
59 FETCH c_timer_data INTO
60 l_timer_id,
61 l_timer_code,
62 l_next_timer_code,
63 l_start_time;
64 CLOSE c_timer_data;
65
66 IF (l_timer_code <> 'T_DUMMY')
67 THEN
68 -- Call REMOVE_TIMER
69
70 xnp_timer_core.remove_timer(
71 p_timer_id => l_timer_id
72 ,x_error_code => x_error_code
73 ,x_error_message => x_error_message
74 );
75
76 IF x_error_code <> 0 THEN
77 raise l_EXCEPTION;
78 END IF;
79
80
81 l_elapsed_time := round((l_current_time - l_start_time)*24*60*60);
82
83 -- Construct timer with new_delay and new_interval
84
85 -- adabholk 03/2001
86 -- performance fix
87 -- new get() replaces two get calls
88
89 xnp_message.get(
90 p_msg_id => l_timer_id
91 ,x_msg_header => l_old_msg_header
92 ,x_msg_text => l_msg_text);
93 /*
94 -- xnp_message.get_header(
95 -- p_msg_id => l_timer_id
96 -- ,x_msg_header => l_old_msg_header);
97 --
98 -- xnp_message.get(
99 -- p_msg_id => l_timer_id
100 -- ,x_msg_text => l_msg_text);
101 */
102 -- Recalculate delay and interval
103
104 xnp_xml_utils.decode(
105 p_msg_text => l_msg_text
106 ,p_tag => 'DELAY'
107 ,x_value => l_delay_text
108 );
109
110 IF ( l_delay <> 0 )
111 THEN
112 l_delay := TO_NUMBER(l_delay_text);
113 l_new_delay := l_delay - l_elapsed_time;
114 ELSE
115 l_new_delay := 0;
116 END IF;
117
118
119 xnp_xml_utils.decode(
120 p_msg_text => l_msg_text
121 ,p_tag => 'INTERVAL'
122 ,x_value => l_interval_text
123 );
124
125 l_interval := TO_NUMBER(l_interval_text);
126 l_new_interval := l_interval - l_elapsed_time;
127
128 -- Reconstruct the message
129 xnp_timer_mgr.construct_dynamic_message(
130 p_msg_to_create => l_old_msg_header.message_code
131 ,p_old_msg_header => l_old_msg_header
132 ,p_delay => l_new_delay
133 ,p_interval => l_new_interval
134 ,x_new_msg_header => l_msg_header
135 ,x_new_msg_text => l_msg_text
136 ,x_error_code => x_error_code
137 ,x_error_message => x_error_message
138 );
139
140
141 IF x_error_code <> 0 THEN
142 raise l_EXCEPTION;
143 END IF;
144
145
146 xnp_timer.start_timer(
147 p_msg_header => l_msg_header
148 ,p_msg_text => l_msg_text
149 ,x_error_code => x_error_code
150 ,x_error_message => x_error_message
151 );
152
153 IF x_error_code <> 0 THEN
154 raise l_EXCEPTION;
155 END IF;
156
157 -- Search for next_timer in registry
158
159 ELSIF l_timer_code = 'T_DUMMY'
160
161 THEN
162 xnp_timer_core.remove_timer(
163 p_timer_id => l_timer_id
164 ,x_error_code => x_error_code
165 ,x_error_message => x_error_message
166 );
167
168 IF x_error_code <> 0 THEN
169 raise l_EXCEPTION;
170 END IF;
171
172 -- Recalculate elapsed time
173
174 l_elapsed_time := round((l_current_time - l_start_time)*24*60*60);
175
176 -- Recalculate delay and interval
177
178 xnp_message.get(
179 p_msg_id => l_timer_id
180 ,x_msg_text => l_msg_text);
181
182 xnp_xml_utils.decode(
183 p_msg_text => l_msg_text
184 ,p_tag => 'DELAY'
185 ,x_value => l_delay_text
186 );
187
188 l_delay := TO_NUMBER(l_delay_text);
189
190 l_new_delay := l_delay - l_elapsed_time;
191
192 -- Construct timer with new_delay and interval
193
194 xnp_message.get_header(
195 p_msg_id => l_timer_id
196 ,x_msg_header => l_old_msg_header);
197
198 xnp_timer_mgr.construct_dynamic_message(
199 p_msg_to_create => l_next_timer_code
200 ,p_old_msg_header => l_old_msg_header
201 ,p_delay => l_new_delay
202 ,x_new_msg_header => l_msg_header
203 ,x_new_msg_text => l_msg_text
204 ,x_error_code => x_error_code
205 ,x_error_message => x_error_message
206 );
207
208 IF x_error_code <> 0 THEN
209 raise l_EXCEPTION;
210 END IF;
211
212
213 xnp_timer.start_timer(
214 p_msg_header => l_msg_header
215 ,p_msg_text => l_msg_text
216 ,x_error_code => x_error_code
217 ,x_error_message => x_error_message
218 );
219
220 IF x_error_code <> 0 THEN
221 raise l_EXCEPTION;
222 END IF;
223
224 ELSE
225 x_error_code := XNP_ERRORS.G_TIMER_NOT_FOUND;
226
227 END IF;
228
229 EXCEPTION
230 WHEN others THEN
231 null; -- Just pass the x_error_code and x_error_message
232 -- to the calling procedure
233 END recalculate;
234
235 -----------------------------------------------------------------------------
236 -----------------------------------------------------------------------------
237 ---- Name : Recalculate_All
238 ---- Purpose : Recalculate the delay and interval given the ref. id
239 -----------------------------------------------------------------------------
240 -----------------------------------------------------------------------------
241
242 PROCEDURE recalculate_all
243 (
244 p_reference_id IN VARCHAR2
245 ,x_error_code OUT NOCOPY NUMBER
246 ,x_error_message OUT NOCOPY VARCHAR2
247 )
248 IS
249
250 CURSOR c_recalculate_all_timers IS
251 SELECT timer_message_code
252 FROM xnp_timer_registry
253 WHERE reference_id = p_reference_id
254 AND status = 'ACTIVE' ;
255
256
257 l_timer_message_code VARCHAR2(20);
258 l_error_code NUMBER;
259 l_error_message VARCHAR2(80);
260
261 BEGIN
262
263 FOR rec IN c_recalculate_all_timers LOOP
264
265 xnp_timer_core.recalculate(
266 p_reference_id => p_reference_id
267 ,p_timer_message_code => rec.timer_message_code
268 ,x_error_code => l_error_code
269 ,x_error_message => l_error_message);
270 END LOOP;
271
272 END recalculate_all;
273
274 -----------------------------------------------------------------------------
275 -----------------------------------------------------------------------------
276 ---- Name : Get_Timer_Status
277 ---- Purpose : Get the status given the timerId
278 -----------------------------------------------------------------------------
279 -----------------------------------------------------------------------------
280 PROCEDURE get_timer_status(
281 p_timer_id IN NUMBER
282 ,x_status OUT NOCOPY VARCHAR2
283 ,x_error_code OUT NOCOPY NUMBER
284 ,x_error_message OUT NOCOPY VARCHAR2
285 )
286
287 IS
288 CURSOR c_timer_status IS
289 SELECT status
290 FROM xnp_timer_registry
291 WHERE timer_id = p_timer_id;
292
293 BEGIN
294
295 OPEN c_timer_status;
296 FETCH c_timer_status into x_status;
297 IF c_timer_status%NOTFOUND
298 THEN
299 x_error_code := XNP_ERRORS.G_TIMER_NOT_FOUND;
300 END IF;
301 CLOSE c_timer_status;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 x_error_code := SQLCODE;
306 x_error_message := SQLERRM;
307
308 IF c_timer_status%ISOPEN
309 THEN
310 CLOSE c_timer_status;
311 END IF;
312
313 END get_timer_status;
314
315 -----------------------------------------------------------------------------
316 -----------------------------------------------------------------------------
317 ---- Name : Get_Timer_Status
318 ---- Purpose : Get the status given the reference id and timer name
319 -----------------------------------------------------------------------------
320 -----------------------------------------------------------------------------
321
322 PROCEDURE get_timer_status
323 (
324 p_reference_id IN VARCHAR2
325 ,p_timer_message_code IN VARCHAR2
326 ,x_timer_id OUT NOCOPY NUMBER
327 ,x_status OUT NOCOPY VARCHAR2
328 ,x_error_code OUT NOCOPY NUMBER
329 ,x_error_message OUT NOCOPY VARCHAR2
330 )
331 IS
332
333 -- Fixed as part of Bug 1351421
334 -- Logic changed for performance.
335 -- The timer status will be returned ordered by
336 -- status asc (ACTIVE, EXPIRED, REMOVED)
337 -- Only the first record will be processed.
338
339 CURSOR c_timer_status IS
340 SELECT timer_id, status FROM xnp_timer_registry
341 WHERE reference_id = p_reference_id
342 AND timer_message_code = p_timer_message_code
343 ORDER BY status ASC;
344
345 BEGIN
346 x_error_code := 0;
347 x_error_message := NULL;
348
349 OPEN c_timer_status;
350 FETCH c_timer_status into x_timer_id, x_status;
351 IF c_timer_status%NOTFOUND
352 THEN
353 X_ERROR_CODE := xnp_errors.g_timer_not_found;
354 fnd_message.set_name('XNP', 'TIMER_NOT_FOUND');
355 fnd_message.set_token('REFERENCE_ID',p_reference_id);
356 fnd_message.set_token('MESSAGE_CODE',p_timer_message_code);
357 x_error_message := fnd_message.get;
358 END IF;
359 CLOSE c_timer_status;
360 EXCEPTION
361 WHEN OTHERS THEN
362 x_error_code := SQLCODE;
363 x_error_message := SQLERRM;
364
365 IF c_timer_status%ISOPEN
366 THEN
367 CLOSE c_timer_status;
368 END IF;
369
370 END get_timer_status;
371
372 -----------------------------------------------------------------------------
373 -----------------------------------------------------------------------------
374 ---- Name : Update_Timer_Status
375 ---- Purpose : Update the status given the reference id and timer name
376 -----------------------------------------------------------------------------
377 -----------------------------------------------------------------------------
378 PROCEDURE update_timer_status
379 (
380 p_reference_id IN VARCHAR2
381 ,p_timer_message_code IN VARCHAR2
382 ,p_status IN VARCHAR2
383 ,x_error_code OUT NOCOPY NUMBER
384 ,x_error_message OUT NOCOPY VARCHAR2
385 )
386 IS
387
388 -- Fixed as part of Bug 1351421
389 -- Logic changed for performance.
390 -- The timer status will be returned ordered by
391 -- status asc (ACTIVE, EXPIRED, REMOVED)
392 -- Only the first record will be processed.
393
394 CURSOR c_update_timer_status IS
395 SELECT *
396 FROM xnp_timer_registry
397 WHERE reference_id = p_reference_id
398 AND timer_message_code = p_timer_message_code
399 ORDER BY status ASC
400 FOR UPDATE OF status;
401
402 v_Timer_Registry xnp_timer_registry%ROWTYPE;
403 BEGIN
404 x_error_code := 0;
405 x_error_message := NULL ;
406
407 OPEN c_update_timer_status;
408 FETCH c_update_timer_status INTO v_Timer_Registry;
409 IF c_update_timer_status%NOTFOUND THEN
410 x_error_code := xnp_errors.g_timer_not_found;
411 ELSE
412 UPDATE xnp_timer_registry
413 SET status = p_status
414 WHERE CURRENT OF c_update_timer_status;
415 END IF;
416
417 EXCEPTION
418 WHEN NO_DATA_FOUND THEN
419 x_error_code := XNP_ERRORS.G_TIMER_NOT_FOUND;
420 CLOSE c_update_timer_status;
421
422 END update_timer_status;
423
424 -----------------------------------------------------------------------------
425 -----------------------------------------------------------------------------
426 ---- Name : Update_Timer_Status
427 ---- Purpose : Update the status given the timer_id
428 -----------------------------------------------------------------------------
429 -----------------------------------------------------------------------------
430 PROCEDURE update_timer_status
431 (
432 p_timer_id IN NUMBER
433 ,p_status IN VARCHAR2
434 ,x_error_code OUT NOCOPY NUMBER
435 ,x_error_message OUT NOCOPY VARCHAR2
436 )
437 IS
438 CURSOR c_update_timer_status IS
439 SELECT *
440 FROM xnp_timer_registry
441 WHERE timer_id = p_timer_id
442 FOR UPDATE OF status;
443
444 v_Timer_Registry xnp_timer_registry%ROWTYPE;
445
446 BEGIN
447 x_error_code := 0;
448 x_error_message := NULL ;
449
450 OPEN c_update_timer_status;
451 FETCH c_update_timer_status INTO v_Timer_Registry;
452
453 IF c_update_timer_status%NOTFOUND THEN
454 x_error_code := xnp_errors.g_timer_not_found;
455 fnd_message.set_name ('XNP', 'TIMER_ID_NOT_FOUND') ;
456 fnd_message.set_token ('TIMER_ID',p_timer_id) ;
457 x_error_message:= FND_MESSAGE.get ;
458 ELSE
459 UPDATE xnp_timer_registry
460 SET status = p_status
461 WHERE CURRENT OF c_update_timer_status;
462 END IF;
463 CLOSE c_update_timer_status;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 x_error_code := SQLCODE;
468 x_error_message := SQLERRM;
469 IF c_update_timer_status%ISOPEN
470 THEN
471 close c_update_timer_status;
472 END IF;
473
474 END update_timer_status;
475
476 -----------------------------------------------------------------------------
477 -----------------------------------------------------------------------------
478 ---- Name : Remove_Timer
479 ---- Purpose : Remove the timer given the timer_id
480 -----------------------------------------------------------------------------
481 -----------------------------------------------------------------------------
482 PROCEDURE remove_timer
483 (
484 p_timer_id IN NUMBER
485 ,x_error_code OUT NOCOPY NUMBER
486 ,x_error_message OUT NOCOPY VARCHAR2
487 )
488 IS
489 remove_timer_failed EXCEPTION ;
490
491 BEGIN
492
493
494 xnp_timer_core.update_timer_status(
495 p_timer_id => p_timer_id
496 ,p_status => 'REMOVED'
497 ,x_error_code => x_error_code
498 ,x_error_message => x_error_message
499 );
500 IF (x_error_code = 0) THEN
501 remove_timer_from_aq(p_timer_id => p_timer_id,
502 x_error_code => x_error_code,
503 x_error_message => x_error_message);
504 IF (x_error_code <> 0) THEN
505 RAISE remove_timer_failed;
506 END IF;
507 END IF;
508
509
510 EXCEPTION
511
512 WHEN remove_timer_failed THEN
513 fnd_message.set_name ('XNP', 'REMOVE_TIMER_FAILED') ;
514 fnd_message.set_token ('TIMER_ID',p_timer_id) ;
515 x_error_message := FND_MESSAGE.get ;
516
517 END remove_timer;
518
519 -----------------------------------------------------------------------------
520 -----------------------------------------------------------------------------
521 ---- Name : Remove_Timer_From_AQ
522 ---- Purpose : Remove the timer from the AQ
523 -----------------------------------------------------------------------------
524 -----------------------------------------------------------------------------
525 PROCEDURE remove_timer_from_aq(
526 p_timer_id IN NUMBER,
527 x_error_code OUT NOCOPY NUMBER,
528 x_error_message OUT NOCOPY VARCHAR2
529 )
530 IS
531 l_message system.xnp_message_type ;
532 l_message_id RAW(16) ;
533 l_msg_header xnp_message.msg_header_rec_type;
534 l_msg_text varchar2(32767) ;
535
536 BEGIN
537
538 l_message := system.xnp_message_type(p_timer_id) ;
539
540 SELECT msg_id INTO l_message_id
541 FROM aq$xnp_in_tmr_qtab
542 WHERE user_data = l_message ;
543
544 IF (l_message_id IS NOT NULL) THEN
545
546 xnp_message.pop(p_queue_name => xnp_event.c_timer_q,
547 x_msg_header => l_msg_header,
548 x_body_text => l_msg_text,
549 x_error_code => x_error_code,
550 x_error_message => x_error_message,
551 p_msg_id => l_message_id );
552
553 IF x_error_code = 0
554 THEN
555 xnp_message.update_status(p_msg_id=>p_timer_id
556 ,p_status=>'PROCESSED'
557 ,p_error_desc=>'Timer Removed') ;
558 END IF;
559
560 END IF ;
561
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 x_error_code := SQLCODE;
566 x_error_message := SQLERRM ;
567
568 END remove_timer_from_aq;
569
570 -----------------------------------------------------------------------------
571 -----------------------------------------------------------------------------
572 ---- Name : Remove_Timer
573 ---- Purpose : Remove the timer given the ref. id and the timer name
574 -----------------------------------------------------------------------------
575 -----------------------------------------------------------------------------
576 PROCEDURE remove_timer
577 (
578 p_reference_id IN VARCHAR2
579 ,p_timer_message_code IN VARCHAR2
580 ,x_error_code OUT NOCOPY NUMBER
581 ,x_error_message OUT NOCOPY VARCHAR2
582 )
583 IS
584
585 -- Fix for bug 1608343 - rraheja
586 -- When removing expired timers, the cursor returns nothing and hence crashes
587 -- Handling case for no data found and gracefully exiting.
588 -- In case timer is not active, it will remain in its original state and
589 -- will not be removed as it is the business logic that timer has expired
590
591 CURSOR c_timer_status_id IS
592 SELECT timer_id FROM xnp_timer_registry
593 WHERE reference_id = p_reference_id
594 AND (timer_message_code = p_timer_message_code
595 OR next_timer = p_timer_message_code)
596 AND status = 'ACTIVE'
597 FOR UPDATE OF status ;
598
599 l_status VARCHAR2(20);
600 l_timer_id NUMBER;
601 l_error_code VARCHAR2(80);
602 l_error_message VARCHAR2(80);
603
604 ex_timer_not_found EXCEPTION;
605
606 BEGIN
607 -- Obtain the latest timer_id and status for the given
608 -- Reference_Id and Timer_Message_code
609 OPEN c_timer_status_id;
610 FETCH c_timer_status_id INTO l_timer_id;
611 IF c_timer_status_id%NOTFOUND
612 THEN
613 RAISE ex_timer_not_found;
614 END IF;
615 CLOSE c_timer_status_id;
616
617 xnp_timer_core.remove_timer(
618 p_timer_id => l_timer_id
619 ,x_error_code => x_error_code
620 ,x_error_message => x_error_message
621 );
622 IF (x_error_code <> 0) THEN
623 x_error_code := xnp_errors.g_timer_not_found;
624 fnd_message.set_name ('XNP', 'TIMER_ID_NOT_FOUND') ;
625 fnd_message.set_token ('TIMER_ID',l_timer_id) ;
626 x_error_message:= FND_MESSAGE.get ;
627 END IF;
628
629 EXCEPTION
630 WHEN ex_timer_not_found THEN
631
632 x_error_code := 0;
633 x_error_message := NULL;
634 IF c_timer_status_id%ISOPEN
635 THEN
636 CLOSE c_timer_status_id;
637 END IF;
638
639
640 WHEN NO_DATA_FOUND THEN
641 x_error_code := xnp_errors.g_timer_not_found;
642 fnd_message.set_name ('XNP', 'TIMER_ID_NOT_FOUND') ;
643 fnd_message.set_token ('TIMER_ID',l_timer_id) ;
644 x_error_message:= FND_MESSAGE.get ;
645
646 IF c_timer_status_id%ISOPEN
647 THEN
648 CLOSE c_timer_status_id;
649 END IF;
650
651 WHEN OTHERS THEN
652 x_error_code := sqlcode;
653 x_error_message := sqlerrm;
654 IF c_timer_status_id%ISOPEN
655 THEN
656 CLOSE c_timer_status_id;
657 END IF;
658
659 END remove_timer;
660
661 -----------------------------------------------------------------------------
662 -----------------------------------------------------------------------------
663 ---- Name : Deregister
664 ---- Purpose : Remove all timers for the give order_id
665 -----------------------------------------------------------------------------
666 -----------------------------------------------------------------------------
667 PROCEDURE deregister
668 (
669 p_order_id IN NUMBER
670 ,x_error_code OUT NOCOPY NUMBER
671 ,x_error_message OUT NOCOPY VARCHAR2
672 )
673 IS
674 CURSOR c_deregister_timers IS
675 SELECT timer_id FROM xnp_timer_registry
676 WHERE order_id = p_order_id
677 AND status = 'ACTIVE';
678
679 l_error_code NUMBER;
680 l_error_message VARCHAR2(80);
681
682 BEGIN
683 l_error_code := 0;
684 l_error_message := NULL;
685
686 FOR rec IN c_deregister_timers LOOP
687 xnp_timer_core.remove_timer (
688 p_timer_id => rec.timer_id
689 ,x_error_code => l_error_code
690 ,x_error_message => l_error_message
691 );
692 IF (x_error_code <> 0) THEN
693 x_error_code := xnp_errors.g_timer_not_found;
694 x_error_message := 'Timer Id : ' || rec.timer_id || '-' || l_error_message;
695 END IF;
696
697 END LOOP;
698
699 EXCEPTION
700
701 WHEN NO_DATA_FOUND THEN
702 x_error_code := xnp_errors.g_timer_not_found;
703
704 WHEN OTHERS THEN
705 x_error_code := sqlcode;
706 x_error_message := sqlerrm;
707
708 END deregister;
709
710 -----------------------------------------------------------------------------
711 ---- Name : Deregister_for_workitem
712 ---- Purpose : Remove all timers for the give Workitem Instance ID
713 -----------------------------------------------------------------------------
714 -----------------------------------------------------------------------------
715 PROCEDURE deregister_for_workitem
716 (
717 p_workitem_instance_id IN NUMBER
718 ,x_error_code OUT NOCOPY NUMBER
719 ,x_error_message OUT NOCOPY VARCHAR2
720 )
721 IS
722 CURSOR c_deregister_timers IS
723 SELECT timer_id FROM xnp_timer_registry
724 WHERE wi_instance_id = p_workitem_instance_id
725 AND status = 'ACTIVE';
726
727 l_error_code NUMBER;
728 l_error_message VARCHAR2(80);
729
730 BEGIN
731 l_error_code := 0;
732 l_error_message := NULL;
733
734 FOR rec IN c_deregister_timers LOOP
735 xnp_timer_core.remove_timer (
736 p_timer_id => rec.timer_id
737 ,x_error_code => l_error_code
738 ,x_error_message => l_error_message
739 );
740 IF (x_error_code <> 0) THEN
741 x_error_code := xnp_errors.g_timer_not_found;
742 x_error_message := 'Timer Id : ' || rec.timer_id || '-' || l_error_message;
743 END IF;
744
745 END LOOP;
746
747 EXCEPTION
748
749 WHEN NO_DATA_FOUND THEN
750 x_error_code := xnp_errors.g_timer_not_found;
751
752 WHEN OTHERS THEN
753 x_error_code := sqlcode;
754 x_error_message := sqlerrm;
755
756 END deregister_for_workitem;
757
758
759 -----------------------------------------------------------------------------
760 ---- Name : get_next_timer
761 ---- Purpose : Get the value for the next timer from the registry
762 -----------------------------------------------------------------------------
763 -----------------------------------------------------------------------------
764 FUNCTION get_next_timer(p_timer_id NUMBER)
765 RETURN VARCHAR2
766 IS
767 l_next_timer VARCHAR2(80) ;
768
769 CURSOR c_next_timer IS
770 SELECT next_timer
771 FROM xnp_timer_registry
772 WHERE timer_id = p_timer_id ;
773
774 BEGIN
775
776 OPEN c_next_timer;
777 FETCH c_next_timer INTO l_next_timer;
778 CLOSE c_next_timer;
779
780 RETURN l_next_timer ;
781 END ;
782
783 -----------------------------------------------------------------------------
784 -----------------------------------------------------------------------------
785 ---- Name : Restart
786 ---- Purpose : Restart the timer given the timer name and ref. id
787 -----------------------------------------------------------------------------
788 -----------------------------------------------------------------------------
789 PROCEDURE restart
790 (
791 p_reference_id IN VARCHAR2
792 ,p_timer_message_code IN VARCHAR2
793 ,x_error_code OUT NOCOPY NUMBER
794 ,x_error_message OUT NOCOPY VARCHAR2
795 )
796 IS
797 CURSOR c_timer_data IS
798 SELECT timer_id, timer_message_code,
799 next_timer
800 FROM xnp_timer_registry
801 WHERE reference_id = p_reference_id
802 AND (timer_message_code = p_timer_message_code
803 OR next_timer = p_timer_message_code
804 )
805 AND status = 'ACTIVE';
806
807 l_timer_id NUMBER DEFAULT NULL;
808 l_next_timer_code VARCHAR2(80) DEFAULT NULL;
809 l_timer_code VARCHAR2(80) DEFAULT NULL;
810 l_status VARCHAR2(80);
811 l_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
812 l_old_msg_header XNP_MESSAGE.MSG_HEADER_REC_TYPE;
813 l_msg_text VARCHAR2(32767);
814 l_msg_to_create VARCHAR2(80);
815
816 l_EXCEPTION EXCEPTION; -- adabholk 06142000 for error trapping
817 BEGIN
818 /* Retrieve the timer_id */
819 OPEN c_timer_data;
820 FETCH c_timer_data into l_timer_id,l_timer_code,
821 l_next_timer_code;
822 CLOSE c_timer_data;
823 IF l_timer_code <> 'T_DUMMY'
824 THEN
825 l_msg_to_create := l_timer_code;
826 ELSE
827 l_msg_to_create := l_next_timer_code;
828 END IF;
829
830 xnp_timer_core.remove_timer(
831 p_timer_id => l_timer_id
832 ,x_error_code => x_error_code
833 ,x_error_message => x_error_message
834 );
835
836 IF x_error_code <> 0 THEN
837 raise l_EXCEPTION;
838 END IF;
839
840 xnp_message.get_header(
841 p_msg_id => l_timer_id
842 ,x_msg_header => l_old_msg_header);
843
844 -- Reconstruct the message
845
846 xnp_timer_mgr.construct_dynamic_message(
847 p_msg_to_create => l_msg_to_create
848 ,p_old_msg_header => l_old_msg_header
849 ,x_new_msg_header => l_msg_header
850 ,x_new_msg_text => l_msg_text
851 ,x_error_code => x_error_code
852 ,x_error_message => x_error_message
853 );
854
855 IF x_error_code <> 0 THEN
856 raise l_EXCEPTION;
857 END IF;
858
859 xnp_timer.start_timer(
860 p_msg_header => l_msg_header
861 ,p_msg_text => l_msg_text
862 ,x_error_code => x_error_code
863 ,x_error_message => x_error_message
864 );
865 IF x_error_code <> 0 THEN
866 raise l_EXCEPTION;
867 END IF;
868
869 EXCEPTION
870 WHEN others THEN
871 null; -- Just pass the x_error_code and x_error_message
872 -- to the calling procedure
873 END restart;
874
875
876 -----------------------------------------------------------------------------
877 -----------------------------------------------------------------------------
878 ---- Name : Restart All
879 ---- Purpose : Restart all timers for a given Reference Id
880 -----------------------------------------------------------------------------
881 -----------------------------------------------------------------------------
882 PROCEDURE restart_all
883 (
884 p_reference_id IN VARCHAR2
885 ,x_error_code OUT NOCOPY NUMBER
886 ,x_error_message OUT NOCOPY VARCHAR2
887 )
888 IS
889 CURSOR c_restart_all_timers IS
890 SELECT timer_message_code
891 FROM xnp_timer_registry
892 WHERE reference_id = p_reference_id
893 AND status = 'ACTIVE' ;
894
895 l_timer_message_code VARCHAR2(20);
896 l_error_code NUMBER;
897 l_error_message VARCHAR2(80);
898
899 BEGIN
900
901
902 FOR rec IN c_restart_all_timers LOOP
903 xnp_timer_core.restart(
904 p_reference_id => p_reference_id
905 ,p_timer_message_code => rec.timer_message_code
906 ,x_error_code => l_error_code
907 ,x_error_message => l_error_message
908 );
909 END LOOP;
910
911 END restart_all;
912
913 -----------------------------------------------------------------------------
914 -----------------------------------------------------------------------------
915 ---- Name : Start_Related_Timers
916 ---- Purpose : For the given message code, start the related timers
917 -----------------------------------------------------------------------------
918 -----------------------------------------------------------------------------
919 PROCEDURE start_related_timers
920 (
921 p_message_code IN VARCHAR2
922 ,p_reference_id IN VARCHAR2
923 ,x_error_code OUT NOCOPY NUMBER
924 ,x_error_message OUT NOCOPY VARCHAR2
925 ,p_opp_reference_id IN VARCHAR2 DEFAULT NULL
926 ,p_sender_name IN VARCHAR2 DEFAULT NULL
927 ,p_recipient_name IN VARCHAR2 DEFAULT NULL
928 ,p_order_id IN NUMBER DEFAULT NULL
929 ,p_wi_instance_id IN NUMBER DEFAULT NULL
930 ,p_fa_instance_id IN NUMBER DEFAULT NULL
931
932 )
933 IS
934 CURSOR c_get_all_timers IS
935 SELECT timer_message_code
936 FROM xnp_timer_publishers
937 WHERE source_message_code = p_message_code ;
938
939 l_old_msg_header xnp_message.msg_header_rec_type ;
940 l_new_msg_header xnp_message.msg_header_rec_type ;
941
942 l_msg_text VARCHAR2(32767) ;
943
944 BEGIN
945
946 x_error_code := 0;
947 x_error_message := NULL ;
948
949 l_old_msg_header.reference_id := p_reference_id ;
950 l_old_msg_header.opp_reference_id := p_opp_reference_id ;
951 l_old_msg_header.sender_name := p_sender_name ;
952 l_old_msg_header.recipient_name := p_recipient_name ;
953 l_old_msg_header.order_id := p_order_id ;
954 l_old_msg_header.wi_instance_id := p_wi_instance_id ;
955 l_old_msg_header.fa_instance_id := p_fa_instance_id ;
956
957 FOR rec IN c_get_all_timers LOOP
958
959 xnp_timer_mgr.construct_dynamic_message(
960 p_msg_to_create => rec.timer_message_code,
961 p_old_msg_header => l_old_msg_header,
962 x_new_msg_header => l_new_msg_header,
963 x_new_msg_text => l_msg_text,
964 x_error_code => x_error_code,
965 x_error_message => x_error_message) ;
966
967 IF (x_error_code = 0) THEN
968
969 xnp_timer.start_timer(l_new_msg_header,
970 l_msg_text ,
971 x_error_code,
972 x_error_message);
973
974 IF (x_error_code <> 0) THEN
975 EXIT ;
976 END IF ;
977
978 ELSE
979 EXIT;
980 END IF ;
981
982 END LOOP;
983
984 EXCEPTION
985 WHEN OTHERS THEN
986 x_error_code := SQLCODE;
987 x_error_message := SQLERRM ;
988
989 END start_related_timers;
990
991 --------------------------------------------------------------------------------
992 ----- API Name : Get Jeopardy Flag
993 ----- Type : Private
994 ----- Purpose : Get jeopardy flag for the given order id
995 ----- Parameters : p_order_id
996 ----- x_flag
997 ----- x_error_code
998 ----- x_error_message
999 ----- Changes : Changed to refer to xdp_order_headers
1000 ----- Earlier it was incorrectly refering to xdp_oe_order_headers
1001 ----- adabholk 03/2001
1002 -----------------------------------------------------------------------------------
1003 PROCEDURE get_jeopardy_flag
1004 (
1005 p_order_id IN NUMBER
1006 ,x_flag OUT NOCOPY VARCHAR2
1007 ,x_error_code OUT NOCOPY NUMBER
1008 ,x_error_message OUT NOCOPY VARCHAR2
1009 )
1010 IS
1011 CURSOR c_get_jeopardy_flag
1012 IS
1013 SELECT jeopardy_enabled_flag
1014 FROM xdp_order_headers
1015 WHERE order_id = p_order_id;
1016
1017 l_jeopardy_flag VARCHAR2(1);
1018
1019 BEGIN
1020
1021 OPEN c_get_jeopardy_flag;
1022 FETCH c_get_jeopardy_flag
1023 INTO l_jeopardy_flag;
1024 IF c_get_jeopardy_flag%NOTFOUND
1025 THEN
1026 x_flag := NULL;
1027 END IF;
1028 CLOSE c_get_jeopardy_flag;
1029
1030 x_flag := l_jeopardy_flag;
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 x_error_code := sqlcode;
1035 x_error_message := sqlerrm;
1036
1037 END get_jeopardy_flag;
1038
1039
1040 --------------------------------------------------------------------------------
1041 ----- API Name : FIRE
1042 ----- Type : Public
1043 ----- Purpose : A Wrapper to the XNP_<Timer Code>_U.FIRE procedure
1044 -- VBhatia Created on 05/07/2002
1045 -----------------------------------------------------------------------------------
1046 PROCEDURE FIRE ( p_timer_code IN VARCHAR2,
1047 x_timer_id OUT NOCOPY NUMBER,
1048 x_timer_contents OUT NOCOPY VARCHAR2,
1049 x_error_code OUT NOCOPY NUMBER,
1050 x_error_message OUT NOCOPY VARCHAR2,
1051 p_sender_name IN VARCHAR2 DEFAULT NULL,
1052 p_recipient_list IN VARCHAR2 DEFAULT NULL,
1053 p_version IN NUMBER DEFAULT 1,
1054 p_reference_id IN VARCHAR2 DEFAULT NULL,
1055 p_opp_reference_id IN VARCHAR2 DEFAULT NULL,
1056 p_order_id IN NUMBER DEFAULT NULL,
1057 p_wi_instance_id IN NUMBER DEFAULT NULL,
1058 p_fa_instance_id IN NUMBER DEFAULT NULL )
1059 IS
1060
1061 l_pkg_name VARCHAR2(200) := NULL;
1062 l_proc_call VARCHAR2(32767) := NULL;
1063
1064 BEGIN
1065
1066 l_pkg_name := XNP_MESSAGE.g_pkg_prefix || p_timer_code || XNP_MESSAGE.g_pkg_suffix;
1067 l_PROC_CALL :=
1068 'BEGIN
1069 '||l_pkg_name||'.fire(' ||
1070 ' :x_timer_id' ||
1071 ',:x_timer_contents' ||
1072 ',:x_error_code' ||
1073 ',:x_error_message' ||
1074 ',:p_sender_name' ||
1075 ',:p_recipient_list' ||
1076 ',:p_version' ||
1077 ',:p_reference_id' ||
1078 ',:p_opp_reference_id' ||
1079 ',:p_order_id' ||
1080 ',:p_wi_instance_id' ||
1081 ',:p_fa_instance_id' ||
1082 ');
1083 END;';
1084
1085 BEGIN
1086
1087 EXECUTE IMMEDIATE l_proc_call USING
1088 OUT x_timer_id
1089 ,OUT x_timer_contents
1090 ,OUT x_error_code
1091 ,OUT x_error_message
1092 ,p_sender_name
1093 ,p_recipient_list
1094 ,p_version
1095 ,p_reference_id
1096 ,p_opp_reference_id
1097 ,p_order_id
1098 ,p_wi_instance_id
1099 ,p_fa_instance_id;
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 -- Grab the error message and error no.
1104 x_error_code := SQLCODE;
1105 fnd_message.set_name('XNP','STD_ERROR');
1106 fnd_message.set_token('ERROR_LOCN'
1107 ,'XNP_TIMER_CORE.FIRE');
1108 fnd_message.set_token('ERROR_TEXT',SQLERRM);
1109 x_error_message := fnd_message.get;
1110 END;
1111
1112 END FIRE;
1113
1114
1115 END xnp_timer_core;