1 Package Body hxc_app_comp_notifications_api as
2 /* $Header: hxchanapi.pkb 120.0 2006/06/19 06:58:02 gsirigin noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hxc_app_comp_notifications_api.';
7 g_debug boolean :=hr_utility.debug_enabled;
8 --
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< <create_app_comp_notification> >--------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure create_app_comp_notification
14 (
15 p_notification_number_retries in number,
16 p_notification_timeout_value in number,
17 p_notification_action_code in varchar2,
18 p_notification_recipient_code in varchar2,
19 p_approval_style_name in varchar2,
20 p_time_recipient_name in varchar2,
21 p_approval_component_id in number,
22 p_comp_notification_id in out nocopy id_type,
23 p_object_version_number in out nocopy ovn_type
24 ) is
25
26 l_proc varchar2(72) := g_package||'create_app_comp_notifications';
27 l_comp_notification_id hxc_app_comp_notifications.comp_notification_id%TYPE;
28 l_object_version_number hxc_app_comp_notifications.object_version_number%TYPE;
29 l_approval_comp_id hxc_approval_comps.approval_comp_id%TYPE;
30 l_approval_comp_ovn hxc_approval_comps.object_version_number%TYPE;
31 l_enabled_flag hxc_app_comp_notif_usages.enabled_flag%TYPE DEFAULT 'Y';
32 l_comp_notification_id_exist hxc_app_comp_notifications.comp_notification_id%TYPE;
33 l_object_version_number_exist hxc_app_comp_notifications.object_version_number%TYPE;
34 l_count number(1) :=0;
35
36
37
38 cursor csr_approval_comp_id
39 (p_approval_style_name in varchar2)
40 is
41 SELECT hac.approval_comp_id, hac.object_version_number
42 FROM hxc_approval_styles has, hxc_approval_comps hac
43 WHERE has.NAME = p_approval_style_name
44 AND has.approval_style_id = hac.approval_style_id
45 AND hac.object_version_number = (SELECT MAX (object_version_number)
46 FROM hxc_approval_comps
47 WHERE approval_comp_id =hac.approval_comp_id);
48
49 cursor csr_chk_app_comp_notifications
50 ( p_notification_number_retries in hxc_app_comp_notifications.notification_number_retries%type
51 ,p_notification_timeout_value in hxc_app_comp_notifications.notification_timeout_value%type
52 ,p_notification_action_code in hxc_app_comp_notifications.notification_action_code%type
53 ,p_notification_recipient_code in hxc_app_comp_notifications.notification_recipient_code%type
54 ) is
55 SELECT hacn.comp_notification_id, hacn.object_version_number
56 FROM hxc_app_comp_notifications hacn
57 WHERE notification_number_retries = p_notification_number_retries
58 AND notification_timeout_value = p_notification_timeout_value
59 AND notification_action_code = p_notification_action_code
60 AND notification_recipient_code = p_notification_recipient_code
61 AND object_version_number = (SELECT MAX (object_version_number)
62 FROM hxc_app_comp_notifications
63 WHERE comp_notification_id =
64 hacn.comp_notification_id);
65
66 cursor csr_comp_from_recipient
67 ( p_approval_style_name in varchar2
68 ,p_time_recipient_name in varchar2) is
69 SELECT hac.approval_comp_id, hac.object_version_number
70 FROM hxc_approval_styles has, hxc_approval_comps hac, hxc_time_recipients htr
71 WHERE has.NAME = p_approval_style_name
72 AND htr.NAME = p_time_recipient_name
73 AND has.approval_style_id = hac.approval_style_id
74 AND htr.time_recipient_id = hac.time_recipient_id
75 AND hac.object_version_number = (SELECT MAX (object_version_number)
76 FROM hxc_approval_comps
77 WHERE approval_comp_id =
78 hac.approval_comp_id);
79
80 cursor csr_chk_diabled
81 ( l_approval_comp_id in number
82 ,l_approval_comp_ovn in number
83 ,l_comp_notification_id in number
84 ,l_comp_notification_ovn in number)
85 is
86 SELECT COUNT (1)
87 FROM hxc_app_comp_notif_usages
88 WHERE approval_comp_id = l_approval_comp_id
89 AND approval_comp_ovn = l_approval_comp_ovn
90 AND comp_notification_id = l_comp_notification_id
91 AND comp_notification_ovn = l_comp_notification_ovn
92 AND enabled_flag = 'N';
93
94 begin
95 g_debug:=hr_utility.debug_enabled;
96 if g_debug then
97 l_proc := g_package||'create_app_comp_notifications';
98 hr_utility.set_location('Entering:'|| l_proc, 10);
99 end if;
100 --
101 -- Issue a savepoint
102 --
103 savepoint create_app_comp_notification;
104 if g_debug then
105 hr_utility.set_location(l_proc, 20);
106 end if;
107 --
108 -- Remember IN OUT parameter IN values
109 --
110
111
112 --
113 -- Truncate the time portion from all IN date parameters
114 --
115
116
117 --
118 -- Call Before Process User Hook
119 --
120
121 begin
122
123
124
125
126 hxc_app_comp_notifications_bk1.create_app_comp_notification_b
127 (
128 p_notification_number_retries => p_notification_number_retries
129 ,p_notification_timeout_value => p_notification_timeout_value
130 ,p_notification_action_code => p_notification_action_code
131 ,p_notification_recipient_code => p_notification_recipient_code
132 );
133
134 exception
135 when hr_api.cannot_find_prog_unit then
136 hr_api.cannot_find_prog_unit_error
137 (p_module_name => 'create_app_comp_notification'
138 ,p_hook_type => 'BP'
139 );
140 end;
141
142 if g_debug then
143 hr_utility.set_location(l_proc, 30);
144 end if;
145 --
146 -- Validation in addition to Row Handlers
147 --
148
149
150
151 --
152 -- Process Logic
153 --
154 if g_debug then
155 hr_utility.set_location(l_proc, 40);
156 end if;
157
158 --
159 -- Call row hadler
160 --
161 begin
162
163 if(p_time_recipient_name is null and p_approval_component_id is null)
164 then
165 open csr_chk_app_comp_notifications(p_notification_number_retries, p_notification_timeout_value,
166 p_notification_action_code, p_notification_recipient_code);
170 then
167 fetch csr_chk_app_comp_notifications into l_comp_notification_id_exist,l_object_version_number_exist;
168
169 if csr_chk_app_comp_notifications%notfound
171
172 hxc_han_ins.ins
173 (p_notification_number_retries => p_notification_number_retries
174 ,p_notification_timeout_value => p_notification_timeout_value
175 ,p_notification_action_code => p_notification_action_code
176 ,p_notification_recipient_code => p_notification_recipient_code
177 ,p_comp_notification_id => l_comp_notification_id
178 ,p_object_version_number => l_object_version_number
179 );
180
181
182
183 open csr_approval_comp_id(p_approval_style_name);
184 LOOP
185 fetch csr_approval_comp_id into l_approval_comp_id, l_approval_comp_ovn;
186 exit when csr_approval_comp_id%notfound;
187 -- Insert into hxc_app_comp_notif_usages
188
189 insert into hxc_app_comp_notif_usages
190 (approval_comp_id
191 ,approval_comp_ovn
192 ,comp_notification_id
193 ,comp_notification_ovn
194 ,enabled_flag
195 ) values
196 (l_approval_comp_id
197 ,l_approval_comp_ovn
198 ,l_comp_notification_id
199 ,l_object_version_number
200 ,l_enabled_flag
201 );
202 end loop;
203 close csr_approval_comp_id;
204 else
205
206 open csr_approval_comp_id(p_approval_style_name);
207 LOOP
208 fetch csr_approval_comp_id into l_approval_comp_id, l_approval_comp_ovn;
209 exit when csr_approval_comp_id%notfound;
210 -- Insert into hxc_app_comp_notif_usages
211
212 open csr_chk_diabled( l_approval_comp_id
213 ,l_approval_comp_ovn
214 ,l_comp_notification_id_exist
215 ,l_object_version_number_exist
216 );
217 fetch csr_chk_diabled into l_count;
218 close csr_chk_diabled;
219 if (l_count>0)
220 then
221 update hxc_app_comp_notif_usages
222 set
223 enabled_flag='Y'
224 where
225 approval_comp_id=l_approval_comp_id and
226 approval_comp_ovn=l_approval_comp_ovn and
227 comp_notification_id=l_comp_notification_id_exist and
228 comp_notification_ovn=l_object_version_number_exist;
229
230 else
231 insert into hxc_app_comp_notif_usages
232 (approval_comp_id
233 ,approval_comp_ovn
234 ,comp_notification_id
238 (l_approval_comp_id
235 ,comp_notification_ovn
236 ,enabled_flag
237 ) values
239 ,l_approval_comp_ovn
240 ,l_comp_notification_id_exist
241 ,l_object_version_number_exist
242 ,l_enabled_flag
243 );
244
245 end if;
246 end loop;
247 close csr_approval_comp_id;
248 end if;
249 close csr_chk_app_comp_notifications;
250
251
252
253 else ---if p_approval_component_id is used to create notification record
254 if(p_approval_component_id is not null)
255 then
256 select approval_comp_id,object_version_number
257 into l_approval_comp_id,l_approval_comp_ovn
258 from hxc_approval_comps where
259 approval_comp_id=p_approval_component_id;
260 else
261 open csr_comp_from_recipient(p_approval_style_name,p_time_recipient_name);
262 fetch csr_comp_from_recipient into l_approval_comp_id,l_approval_comp_ovn;
263
264 end if;
265 open csr_chk_app_comp_notifications(p_notification_number_retries, p_notification_timeout_value,
266 p_notification_action_code, p_notification_recipient_code);
267 fetch csr_chk_app_comp_notifications into l_comp_notification_id_exist,l_object_version_number_exist;
268
269 if csr_chk_app_comp_notifications%notfound
270 then
271
272 hxc_han_ins.ins
273 (p_notification_number_retries => p_notification_number_retries
274 ,p_notification_timeout_value => p_notification_timeout_value
275 ,p_notification_action_code => p_notification_action_code
276 ,p_notification_recipient_code => p_notification_recipient_code
277 ,p_comp_notification_id => l_comp_notification_id
278 ,p_object_version_number => l_object_version_number
279 );
280
281 -- Insert into hxc_app_comp_notif_usages. We need not loop as we are dealing with only one
282 -- approval component.
283
284 insert into hxc_app_comp_notif_usages
285 (approval_comp_id
286 ,approval_comp_ovn
287 ,comp_notification_id
288 ,comp_notification_ovn
289 ,enabled_flag
290 ) values
291 (l_approval_comp_id
292 ,l_approval_comp_ovn
293 ,l_comp_notification_id
294 ,l_object_version_number
295 ,l_enabled_flag
296 );
297
298 else
299 -- Insert into hxc_app_comp_notif_usages
300
301 insert into hxc_app_comp_notif_usages
302 (approval_comp_id
303 ,approval_comp_ovn
304 ,comp_notification_id
305 ,comp_notification_ovn
306 ,enabled_flag
307 ) values
308 (l_approval_comp_id
309 ,l_approval_comp_ovn
310 ,l_comp_notification_id_exist
311 ,l_object_version_number_exist
312 ,l_enabled_flag
313 );
314
315 end if;
316 close csr_chk_app_comp_notifications; ---Cursor closed
317
318 end if;
319 end; --new end
320
321
322
323
324 if g_debug then
325 hr_utility.set_location(l_proc, 50);
326 end if;
327 --
328 -- Call After Process User Hook
329 --
330
331 begin
332 hxc_app_comp_notifications_bk1.create_app_comp_notification_a
333 (p_comp_notification_id => l_comp_notification_id
334 ,p_object_version_number => l_object_version_number
335 ,p_notification_number_retries => p_notification_number_retries
336 ,p_notification_timeout_value => p_notification_timeout_value
337 ,p_notification_action_code => p_notification_action_code
338 ,p_notification_recipient_code => p_notification_recipient_code
339 );
340 exception
341 when hr_api.cannot_find_prog_unit then
342 hr_api.cannot_find_prog_unit_error
343 (p_module_name => 'create_app_comp_notification'
344 ,p_hook_type => 'AP'
345 );
346 end;
347
348 if g_debug then
349 hr_utility.set_location(l_proc, 60);
350 end if;
351 --
352 -- When in validation only mode raise the Validate_Enabled exception
353 --
354
355 --
356 -- Set all IN OUT and OUT parameters with out values
357 --
358 p_comp_notification_id := id_type();
359 p_comp_notification_id.EXTEND(1);
360
361 p_object_version_number := ovn_type();
362 p_object_version_number.EXTEND(1);
363
364 p_comp_notification_id(1) := l_comp_notification_id;
365 p_object_version_number(1) := l_object_version_number;
366 --
367 if g_debug then
368 hr_utility.set_location(l_proc, 70);
369 end if;
370 --commit; --Have to be removed*****
371 exception
372 when hr_api.validate_enabled then
373 --
374 -- As the Validate_Enabled exception has been raised
375 -- we must rollback to the savepoint
376 --
377 rollback to create_app_comp_notification;
378 --
379 -- Reset IN OUT parameters and set OUT parameters
380 -- (Any key or derived arguments must be set to null
381 -- when validation only mode is being used.)
382 --
383 p_comp_notification_id := null;
384 p_object_version_number := null;
385
386 if g_debug then
387 hr_utility.set_location(l_proc, 80);
388 end if;
389 when others then
390 --
391 -- A validation or unexpected error has occured
392 --
393 rollback to create_app_comp_notification;
394 --
395 -- Reset IN OUT parameters and set all
399 p_object_version_number := null;
396 -- OUT parameters, including warnings, to null
397 --
398 p_comp_notification_id := null;
400 hr_utility.set_location(' Leaving:'||l_proc, 90);
401 raise;
402 end create_app_comp_notification;
403 --
404 -- ----------------------------------------------------------------------------
405 -- |--------------------------< <update_app_comp_notification> >--------------|
406 -- ----------------------------------------------------------------------------
407 --
408 procedure update_app_comp_notification
409 (
410 p_comp_notification_id in number,
411 p_object_version_number in out nocopy number,
412 p_notification_number_retries in number default hr_api.g_number,
413 p_notification_timeout_value in number default hr_api.g_number
414 ) is
415 --
416 -- Declare cursors and local variables
417 --
418 l_object_version_number hxc_app_comp_notifications.object_version_number%type :=p_object_version_number;
419 l_effective_date date;
420 l_proc varchar2(72) := g_package||'update_app_comp_notification';
421 begin
422 if g_debug then
423 hr_utility.set_location('Entering:'|| l_proc, 10);
424 end if;
425 --
426 -- Issue a savepoint
427 --
428 savepoint update_app_comp_notification;
429
430
431 --
432 -- Call Before Process User Hook
433 --
434 begin
435 hxc_app_comp_notifications_bk2.update_app_comp_notification_b
436 (p_comp_notification_id => p_comp_notification_id
437 ,p_object_version_number => p_object_version_number
438 ,p_notification_number_retries => p_notification_number_retries
439 ,p_notification_timeout_value => p_notification_timeout_value
440 );
441 exception
442 when hr_api.cannot_find_prog_unit then
443 hr_api.cannot_find_prog_unit_error
444 (p_module_name => 'update_app_comp_notification'
445 ,p_hook_type => 'BP'
446 );
447 end;
448 --
449 -- Validation in addition to Row Handlers
450 --
451
452
453
454 --
455 -- Process Logic
456 --
457
458
459 --
460 -- Call row hadler(parameters should be changed)
461 --
462
463 hxc_han_upd.upd
464 (p_comp_notification_id => p_comp_notification_id
465 ,p_object_version_number => l_object_version_number
466 ,p_notification_number_retries => p_notification_number_retries
467 ,p_notification_timeout_value => p_notification_timeout_value
468 );
469
470
471 --Update the hxc_app_comp_notif_usages table
472
473 update hxc_app_comp_notif_usages
474 set
475 comp_notification_ovn = l_object_version_number
476 where comp_notification_id = p_comp_notification_id;
477
478 --
479 -- Call After Process User Hook
480 --
481 begin
482 hxc_app_comp_notifications_bk2.update_app_comp_notification_a
483 (p_comp_notification_id => p_comp_notification_id
484 ,p_object_version_number => p_object_version_number
485 ,p_notification_number_retries => p_notification_number_retries
486 ,p_notification_timeout_value => p_notification_timeout_value
487 );
488 exception
489 when hr_api.cannot_find_prog_unit then
490 hr_api.cannot_find_prog_unit_error
491 (p_module_name => 'update_app_comp_notification'
492 ,p_hook_type => 'AP'
493 );
494 end;
495 --
496 -- When in validation only mode raise the Validate_Enabled exception
497 --
498
499 --
500 -- Set all IN OUT and OUT parameters with out values
501 --
502
503 p_object_version_number := l_object_version_number;
504 --
505 if g_debug then
506 hr_utility.set_location(' Leaving:'||l_proc, 70);
507 end if;
508 exception
509 when hr_api.validate_enabled then
510 --
511 -- As the Validate_Enabled exception has been raised
512 -- we must rollback to the savepoint
513 --
514 rollback to update_app_comp_notification;
515 --
516 -- Reset IN OUT parameters and set OUT parameters
517 -- (Any key or derived arguments must be set to null
518 -- when validation only mode is being used.)
519 --
520
521 p_object_version_number := null;
522
523 if g_debug then
524 hr_utility.set_location(' Leaving:'||l_proc, 80);
525 end if;
526 when others then
527 --
528 -- A validation or unexpected error has occured
529 --
530 rollback to update_app_comp_notification;
531 --
532 -- Reset IN OUT parameters and set all
533 -- OUT parameters, including warnings, to null
534 --
535
536 p_object_version_number := null;
537 if g_debug then
538 hr_utility.set_location(' Leaving:'||l_proc, 90);
539 end if;
540 raise;
541 end update_app_comp_notification;
542 --
543 --
544 -- ----------------------------------------------------------------------------
545 -- |--------------------------< <delete_app_comp_notification> >--------------|
546 -- ----------------------------------------------------------------------------
547 --
548 procedure delete_app_comp_notification
549 (
550 p_comp_notification_id in number
551 ,p_object_version_number in number
552 ) is
553 --
554 -- Declare cursors and local variables
555 --
556 l_in_out_parameter number;
557 l_effective_date date;
561 hr_utility.set_location('Entering:'|| l_proc, 10);
558 l_proc varchar2(72) := g_package||'delete_app_comp_notification';
559 begin
560 if g_debug then
562 end if;
563 --
564 -- Issue a savepoint
565 --
566 savepoint delete_app_comp_notification;
567
568
569 --
570 -- Call Before Process User Hook
571 --
572 begin
573 hxc_app_comp_notifications_bk3.delete_app_comp_notification_b
574 (p_comp_notification_id => p_comp_notification_id
575 ,p_object_version_number => p_object_version_number
576 );
577 exception
578 when hr_api.cannot_find_prog_unit then
579 hr_api.cannot_find_prog_unit_error
580 (p_module_name => 'delete_app_comp_notification'
581 ,p_hook_type => 'BP'
582 );
583 end;
584 --
585 -- update hxc_app_comp_notif_usages table
586 --
587 update hxc_app_comp_notif_usages
588 set
589 enabled_flag = 'N' where
590 comp_notification_id = p_comp_notification_id and
591 comp_notification_ovn = p_object_version_number;
592
593
594 --
595 -- Call After Process User Hook
596 --
597 begin
598 hxc_app_comp_notifications_bk3.delete_app_comp_notification_a
599 (p_comp_notification_id => p_comp_notification_id
600 ,p_object_version_number => p_object_version_number
601 ); exception
602 when hr_api.cannot_find_prog_unit then
603 hr_api.cannot_find_prog_unit_error
604 (p_module_name => 'delete_app_comp_notification'
605 ,p_hook_type => 'AP'
606 );
607 end;
608 --
609 -- When in validation only mode raise the Validate_Enabled exception
610 --
611
612
613 --
614 if g_debug then
615 hr_utility.set_location(' Leaving:'||l_proc, 70);
616 end if;
617 exception
618 when hr_api.validate_enabled then
619 --
620 -- As the Validate_Enabled exception has been raised
621 -- we must rollback to the savepoint
622 --
623 rollback to delete_app_comp_notification;
624 --
625 -- Reset IN OUT parameters and set OUT parameters
626 -- (Any key or derived arguments must be set to null
627 -- when validation only mode is being used.)
628 --
629 if g_debug then
630 hr_utility.set_location(' Leaving:'||l_proc, 80);
631 end if;
632 when others then
633 --
634 -- A validation or unexpected error has occured
635 --
636 rollback to delete_app_comp_notification;
637 --
638 -- Reset IN OUT parameters and set all
639 -- OUT parameters, including warnings, to null
640 --
641 if g_debug then
642 hr_utility.set_location(' Leaving:'||l_proc, 90);
643 end if;
644 raise;
645 end delete_app_comp_notification;
646
647 --
648 --
649 -- ----------------------------------------------------------------------------
650 -- |--------------------------< <purge_comp_notification> >-------------------|
651 -- ----------------------------------------------------------------------------
652 --
653 procedure purge_comp_notification
654 (
655 p_comp_notification_id in number
656 ,p_object_version_number in number
657 ) is
658 --
659 -- Declare cursors and local variables
660 --
661 l_in_out_parameter number;
662 l_effective_date date;
663 l_proc varchar2(72) := g_package||'purge_comp_notification';
664 begin
665 if g_debug then
666 hr_utility.set_location('Entering:'|| l_proc, 10);
667 end if;
668 --
669 -- Issue a savepoint
670 --
671 savepoint purge_comp_notification;
672
673
674
675
676 --
677 -- Deleting records from hxc_app_comp_notif_usages
678 --
679 delete from hxc_app_comp_notif_usages where
680 comp_notification_id = p_comp_notification_id and
681 comp_notification_ovn= p_object_version_number and
682 enabled_flag = 'N';
683
684 --
685 -- Delete from hxc_app_comp_notifications
686 --
687
688 hxc_han_del.del
689 (p_comp_notification_id => p_comp_notification_id
690 ,p_object_version_number => p_object_version_number
691 );
692
693
694
695 --
696 -- When in validation only mode raise the Validate_Enabled exception
697 --
698
699
700 --
701 if g_debug then
702 hr_utility.set_location(' Leaving:'||l_proc, 70);
703 end if;
704 exception
705 when hr_api.validate_enabled then
706 --
707 -- As the Validate_Enabled exception has been raised
708 -- we must rollback to the savepoint
709 --
710 rollback to purge_comp_notification;
711 --
712 -- Reset IN OUT parameters and set OUT parameters
713 -- (Any key or derived arguments must be set to null
714 -- when validation only mode is being used.)
715 --
716 if g_debug then
717 hr_utility.set_location(' Leaving:'||l_proc, 80);
718 end if;
719 when others then
720 --
721 -- A validation or unexpected error has occured
722 --
723 rollback to purge_comp_notification;
724 --
725 -- Reset IN OUT parameters and set all
726 -- OUT parameters, including warnings, to null
727 --
728 if g_debug then
729 hr_utility.set_location(' Leaving:'||l_proc, 90);
730 end if;
731 raise;
732 end purge_comp_notification;
733
734 --
738 -- ----------------------------------------------------------------------------
735 --
736 -- ----------------------------------------------------------------------------
737 -- |--------------------------< <disable_timeout_notifications> >-------------|
739 --
740 procedure disable_timeout_notifications
741 (
742 p_approval_style_name in varchar2
743 ) is
744 --
745 -- Declare cursors and local variables
746 --
747 l_in_out_parameter number;
748 l_effective_date date;
749 l_proc varchar2(72) := g_package||'disable_timeout_notifications';
750 l_approval_comp_id number;
751 l_comp_notification_id number;
752
753 CURSOR csr_resend_notifications
754 IS
755 SELECT hacnu.approval_comp_id, hacnu.comp_notification_id
756 FROM hxc_app_comp_notifications hacn,
757 hxc_approval_styles has,
758 hxc_approval_comps hac,
759 hxc_app_comp_notif_usages hacnu
760 WHERE has.NAME = p_approval_style_name
761 AND has.approval_style_id = hac.approval_style_id
762 AND hac.approval_comp_id = hacnu.approval_comp_id
763 AND hac.object_version_number = hacnu.approval_comp_ovn
764 AND hacnu.comp_notification_id = hacn.comp_notification_id
765 AND hacnu.comp_notification_ovn = hacn.object_version_number
766 AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND';
767
768 begin
769 if g_debug then
770 hr_utility.set_location('Entering:'|| l_proc, 10);
771 end if;
772 --
773 -- Issue a savepoint
774 --
775 savepoint disable_timeout_notifications;
776
777
778 --
779 -- Call Before Process User Hook
780 --
781
782 --
783 -- Deleting records from hxc_app_comp_notif_usages
784 --
785 open csr_resend_notifications;
786 LOOP
787 fetch csr_resend_notifications into l_approval_comp_id,l_comp_notification_id;
788 exit when csr_resend_notifications%notfound;
789 update hxc_app_comp_notif_usages
790 set
791 enabled_flag = 'N'
792 where approval_comp_id = l_approval_comp_id and
793 comp_notification_id = l_comp_notification_id;
794 end loop;
795 close csr_resend_notifications;
796
797 --
798 -- Delete from hxc_app_comp_notifications
799 --
800
801 /* hxc_han_del.del
802 (p_comp_notification_id => l_comp_notification_id
803 ,p_object_version_number => p_object_version_number
804 );*/
805
806
807 --
808 -- Call After Process User Hook
809 --
810
811 --
812 -- When in validation only mode raise the Validate_Enabled exception
813 --
814
815
816 --
817 if g_debug then
818 hr_utility.set_location(' Leaving:'||l_proc, 70);
819 end if;
820 exception
821 when hr_api.validate_enabled then
822 --
823 -- As the Validate_Enabled exception has been raised
824 -- we must rollback to the savepoint
825 --
826 rollback to delete_timeout_notifications;
827 --
828 -- Reset IN OUT parameters and set OUT parameters
829 -- (Any key or derived arguments must be set to null
830 -- when validation only mode is being used.)
831 --
832 if g_debug then
833 hr_utility.set_location(' Leaving:'||l_proc, 80);
834 end if;
835 when others then
836 --
837 -- A validation or unexpected error has occured
838 --
839 rollback to disable_timeout_notifications;
840 --
841 -- Reset IN OUT parameters and set all
842 -- OUT parameters, including warnings, to null
843 --
844 if g_debug then
845 hr_utility.set_location(' Leaving:'||l_proc, 90);
846 end if;
847 raise;
848 end disable_timeout_notifications;
849 end hxc_app_comp_notifications_api;