[Home] [Help]
PACKAGE BODY: APPS.HXC_NOTIFICATION_HELPER
Source
1 Package Body hxc_notification_helper as
2 /* $Header: hxcnothel.pkb 120.1 2006/07/28 13:18:39 gsirigin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |---------------------------------< run_extensions >-----------------------|
6 -- ----------------------------------------------------------------------------
7
8 FUNCTION run_extensions (p_approval_comp_id IN NUMBER)
9 RETURN BOOLEAN
10 IS
11 l_extension BOOLEAN;
12 l_run_recipient_extensions hxc_approval_styles.run_recipient_extensions%TYPE;
13
14 CURSOR csr_run_extension
15 IS
16 SELECT has.run_recipient_extensions
17 FROM hxc_approval_styles has, hxc_approval_comps hac
18 WHERE hac.approval_comp_id = p_approval_comp_id
19 AND has.approval_style_id = hac.approval_style_id;
20 BEGIN
21 OPEN csr_run_extension;
22 FETCH csr_run_extension INTO l_run_recipient_extensions;
23
24 IF l_run_recipient_extensions = 'Y' OR l_run_recipient_extensions IS NULL
25 THEN
26 l_extension := TRUE ;
27 ELSE
28 l_extension := FALSE ;
29 END IF;
30
31 CLOSE csr_run_extension;
32 RETURN (l_extension);
33 END run_extensions;
34
35 --
36 -- ----------------------------------------------------------------------------
37 -- |-------------------------< has_timeout_notifications >--------------------|
38 -- ----------------------------------------------------------------------------
39
40 FUNCTION has_timeout_notifications (p_approval_style_id IN NUMBER)
41 RETURN VARCHAR2
42 IS
43 l_timeout_notifs number (9) := 0;
44 l_notifs VARCHAR2 (9);
45
46 CURSOR csr_timeout_notifs
47 IS select count(1) FROM hxc_approval_styles has,
48 hxc_approval_comps hac,
49 hxc_app_comp_notifications hacn,
50 hxc_app_comp_notif_usages hacnu
51 WHERE has.approval_style_id = p_approval_style_id
52 AND has.approval_style_id = hac.approval_style_id
53 AND hac.approval_comp_id = hacnu.approval_comp_id
54 AND hac.object_version_number = hacnu.approval_comp_ovn
55 AND hacnu.comp_notification_id = hacn.comp_notification_id
56 AND hacn.notification_action_code =
57 hxc_app_comp_notifications_api.
58 c_action_request_appr_resend
59 AND hacnu.enabled_flag = 'Y';
60 BEGIN
61 OPEN csr_timeout_notifs;
62 FETCH csr_timeout_notifs INTO l_timeout_notifs;
63
64 IF l_timeout_notifs<>0
65 THEN
66 l_notifs := 'Y';
67 ELSE
68 l_notifs := 'N';
69 END IF;
70
71 CLOSE csr_timeout_notifs;
72 RETURN (l_notifs);
73 END has_timeout_notifications;
74 --
75 -- ----------------------------------------------------------------------------
76 -- |-----------------------< number_timeout_retries_any >---------------------|
77 -- ----------------------------------------------------------------------------
78
79 FUNCTION number_timeout_retries_any (p_approval_style_id IN NUMBER)
80 RETURN NUMBER
81 IS
82 l_retries hxc_app_comp_notifications.notification_number_retries%TYPE;
83
84 CURSOR csr_timeout_retries
85 IS
86 SELECT notification_number_retries
87 FROM hxc_approval_styles has,
88 hxc_approval_comps hac,
89 hxc_app_comp_notifications hacn,
90 hxc_app_comp_notif_usages hacnu
91 WHERE has.approval_style_id = p_approval_style_id
92 AND has.approval_style_id = hac.approval_style_id
93 AND hac.approval_comp_id = hacnu.approval_comp_id
94 AND hac.object_version_number = hacnu.approval_comp_ovn
95 AND hacnu.comp_notification_id = hacn.comp_notification_id
96 AND hacnu.comp_notification_ovn = hacn.object_version_number
97 AND hacn.notification_action_code =
98 hxc_app_comp_notifications_api.c_action_request_appr_resend
99 AND hacnu.enabled_flag = 'Y';
100 BEGIN
101 OPEN csr_timeout_retries;
102 FETCH csr_timeout_retries INTO l_retries;
103 RETURN (l_retries);
104 CLOSE csr_timeout_retries;
105 END number_timeout_retries_any;
106
107 --
108 -- ----------------------------------------------------------------------------
109 -- |------------------------< approver_timeout_value >-----------------------|
110 -- ----------------------------------------------------------------------------
111
112 FUNCTION approver_timeout_value (p_approval_style_id IN NUMBER)
113 RETURN NUMBER
114 IS
115 l_appr_timeout_value hxc_app_comp_notifications.notification_timeout_value%TYPE;
116
117 CURSOR csr_timeout_value
118 IS
119 SELECT notification_timeout_value
120 FROM hxc_approval_styles has,
121 hxc_approval_comps hac,
122 hxc_app_comp_notifications hacn,
123 hxc_app_comp_notif_usages hacnu
124 WHERE has.approval_style_id = p_approval_style_id
125 AND has.approval_style_id = hac.approval_style_id
126 AND hac.approval_comp_id = hacnu.approval_comp_id
127 AND hac.object_version_number = hacnu.approval_comp_ovn
128 AND hacnu.comp_notification_id = hacn.comp_notification_id
129 AND hacnu.comp_notification_ovn = hacn.object_version_number
130 AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
131 AND hacn.notification_recipient_code = 'APPROVER'
132 AND hacnu.enabled_flag = 'Y';
133 BEGIN
134 OPEN csr_timeout_value;
135 FETCH csr_timeout_value INTO l_appr_timeout_value;
136 RETURN (l_appr_timeout_value);
137 CLOSE csr_timeout_value;
138 END approver_timeout_value;
139
140
141 --
142 -- ----------------------------------------------------------------------------
143 -- |-------------------------< preparer_timeout_value >-----------------------|
144 -- ----------------------------------------------------------------------------
145
146 FUNCTION preparer_timeout_value (p_approval_style_id IN NUMBER)
147 RETURN NUMBER
148 IS
149 l_preparer_timeout_value hxc_app_comp_notifications.notification_timeout_value%TYPE;
150
151 CURSOR csr_timeout_value
152 IS
153 SELECT hacn.notification_timeout_value
154 FROM hxc_approval_styles has,
155 hxc_approval_comps hac,
156 hxc_app_comp_notifications hacn,
157 hxc_app_comp_notif_usages hacnu
158 WHERE has.approval_style_id = p_approval_style_id
159 AND has.approval_style_id = hac.approval_style_id
160 AND hac.approval_comp_id = hacnu.approval_comp_id
161 AND hac.object_version_number = hacnu.approval_comp_ovn
162 AND hacnu.comp_notification_id = hacn.comp_notification_id
163 AND hacnu.comp_notification_ovn = hacn.object_version_number
164 AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
165 AND hacn.notification_recipient_code = 'PREPARER'
166 AND hacnu.enabled_flag = 'Y';
167 BEGIN
168 OPEN csr_timeout_value;
169 FETCH csr_timeout_value INTO l_preparer_timeout_value;
170 RETURN (l_preparer_timeout_value);
171 CLOSE csr_timeout_value;
172 END preparer_timeout_value;
173
174
175 --
176 -- ----------------------------------------------------------------------------
177 -- |----------------------------< admin_timeout_value >-----------------------|
178 -- ----------------------------------------------------------------------------
179
180 FUNCTION admin_timeout_value (p_approval_style_id IN NUMBER)
181 RETURN NUMBER
182 IS
183 l_admin_timeout_value hxc_app_comp_notifications.notification_timeout_value%TYPE;
184
185 CURSOR csr_timeout_value
186 IS
187 SELECT hacn.notification_timeout_value
188 FROM hxc_approval_styles has,
189 hxc_approval_comps hac,
190 hxc_app_comp_notifications hacn,
191 hxc_app_comp_notif_usages hacnu
192 WHERE has.approval_style_id = p_approval_style_id
193 AND has.approval_style_id = hac.approval_style_id
194 AND hac.approval_comp_id = hacnu.approval_comp_id
195 AND hac.object_version_number = hacnu.approval_comp_ovn
196 AND hacnu.comp_notification_id = hacn.comp_notification_id
197 AND hacnu.comp_notification_ovn = hacn.object_version_number
198 AND hacn.notification_action_code = 'REQUEST-APPROVAL-RESEND'
199 AND hacn.notification_recipient_code = 'ADMIN'
200 AND hacnu.enabled_flag = 'Y';
201 BEGIN
202 OPEN csr_timeout_value;
203 FETCH csr_timeout_value INTO l_admin_timeout_value;
204 RETURN (l_admin_timeout_value);
205 CLOSE csr_timeout_value;
206 END admin_timeout_value;
207
208 --
209 -- ----------------------------------------------------------------------------
210 -- |------------------------< notify_sup_for_approval >-----------------------|
211 -- ----------------------------------------------------------------------------
212
213 FUNCTION notify_sup_for_approval (p_approval_style_id IN NUMBER)
214 RETURN VARCHAR2
215 IS
216 l_approval NUMBER (3) := 0;
217 l_notifs VARCHAR2 (1);
218
219 CURSOR csr_sup_for_approval
220 IS
221 SELECT COUNT (1)
222 FROM hxc_approval_styles has,
223 hxc_approval_comps hac,
224 hxc_app_comp_notifications hacn,
225 hxc_app_comp_notif_usages hacnu
226 WHERE has.approval_style_id = p_approval_style_id
227 AND has.approval_style_id = hac.approval_style_id
228 AND hac.approval_comp_id = hacnu.approval_comp_id
229 AND hac.object_version_number = hacnu.approval_comp_ovn
230 AND hacnu.comp_notification_id = hacn.comp_notification_id
231 AND hacnu.comp_notification_ovn = hacn.object_version_number
232 AND hacn.notification_action_code = 'REQUEST-APPROVAL'
233 AND hacn.notification_recipient_code = 'SUPERVISOR'
234 AND hacnu.enabled_flag = 'Y';
235 BEGIN
236 OPEN csr_sup_for_approval;
237 FETCH csr_sup_for_approval INTO l_approval;
238
239 IF l_approval <> 0
240 THEN
241 l_notifs := 'Y';
242 ELSE
243 l_notifs := 'N';
244 END IF;
245
246 CLOSE csr_sup_for_approval;
247 RETURN (l_notifs);
248 END notify_sup_for_approval;
249 --
250 -- ----------------------------------------------------------------------------
251 -- |------------------------<notify_worker_submission >-----------------------|
252 -- ----------------------------------------------------------------------------
253
254 FUNCTION notify_worker_submission (p_approval_style_id IN NUMBER)
255 RETURN VARCHAR2
256 IS
257 l_approval NUMBER (3) := 0;
258 l_notifs VARCHAR2 (1);
259
260 CURSOR csr_worker_sumission
261 IS
262 SELECT COUNT (1)
263 FROM hxc_approval_styles has,
264 hxc_approval_comps hac,
265 hxc_app_comp_notifications hacn,
266 hxc_app_comp_notif_usages hacnu
267 WHERE has.approval_style_id = p_approval_style_id
268 AND has.approval_style_id = hac.approval_style_id
269 AND hac.approval_comp_id = hacnu.approval_comp_id
270 AND hac.object_version_number = hacnu.approval_comp_ovn
271 AND hacnu.comp_notification_id = hacn.comp_notification_id
272 AND hacnu.comp_notification_ovn = hacn.object_version_number
273 AND hacn.notification_action_code = 'SUBMISSION'
274 AND hacn.notification_recipient_code = 'WORKER'
275 AND hacnu.enabled_flag = 'Y';
276 BEGIN
277 OPEN csr_worker_sumission;
278 FETCH csr_worker_sumission INTO l_approval;
279
280 IF l_approval <> 0
281 THEN
282 l_notifs := 'Y';
283 ELSE
284 l_notifs := 'N';
285 END IF;
286
287 CLOSE csr_worker_sumission;
288 RETURN (l_notifs);
289 END notify_worker_submission;
290
291
292 --
293 -- ----------------------------------------------------------------------------
294 -- |---------------------< notify_worker_auto_approve >-----------------------|
295 -- ----------------------------------------------------------------------------
296
297 FUNCTION notify_worker_auto_approve (p_approval_style_id IN NUMBER)
298 RETURN VARCHAR2
299 IS
300 l_approval NUMBER (3) := 0;
301 l_notifs VARCHAR2 (1);
302
303 CURSOR csr_worker_auto_approve
304 IS
305 SELECT COUNT (1)
306 FROM hxc_approval_styles has,
307 hxc_approval_comps hac,
308 hxc_app_comp_notifications hacn,
309 hxc_app_comp_notif_usages hacnu
310 WHERE has.approval_style_id = p_approval_style_id
311 AND has.approval_style_id = hac.approval_style_id
312 AND hac.approval_comp_id = hacnu.approval_comp_id
313 AND hac.object_version_number = hacnu.approval_comp_ovn
314 AND hacnu.comp_notification_id = hacn.comp_notification_id
315 AND hacn.notification_action_code = 'AUTO-APPROVE'
316 AND hacn.notification_recipient_code = 'WORKER'
317 AND hacnu.enabled_flag = 'Y';
318 BEGIN
319 OPEN csr_worker_auto_approve;
320 FETCH csr_worker_auto_approve INTO l_approval;
321
322 IF l_approval <> 0
323 THEN
324 l_notifs := 'Y';
325 ELSE
326 l_notifs := 'N';
327 END IF;
328
329 CLOSE csr_worker_auto_approve;
330 RETURN (l_notifs);
331 END notify_worker_auto_approve;
332
333
334
335 --
336 -- ----------------------------------------------------------------------------
337 -- |------------------------< notify_preparer_approve >-----------------------|
338 -- ----------------------------------------------------------------------------
339
340 FUNCTION notify_preparer_approve (p_approval_style_id IN NUMBER)
341 RETURN VARCHAR2
342 IS
343 l_approval NUMBER (3) := 0;
344 l_notifs VARCHAR2 (1);
345
346 CURSOR csr_preparer_approve
347 IS
348 SELECT COUNT (1)
349 FROM hxc_approval_styles has,
350 hxc_approval_comps hac,
351 hxc_app_comp_notifications hacn,
352 hxc_app_comp_notif_usages hacnu
353 WHERE has.approval_style_id = p_approval_style_id
354 AND has.approval_style_id = hac.approval_style_id
355 AND hac.approval_comp_id = hacnu.approval_comp_id
356 AND hac.object_version_number = hacnu.approval_comp_ovn
357 AND hacnu.comp_notification_id = hacn.comp_notification_id
358 AND hacn.notification_action_code = 'APPROVED'
359 AND hacn.notification_recipient_code = 'PREPARER'
360 AND hacnu.enabled_flag = 'Y';
361 BEGIN
362 OPEN csr_preparer_approve;
363 FETCH csr_preparer_approve INTO l_approval;
364
365 IF l_approval <> 0
366 THEN
367 l_notifs := 'Y';
368 ELSE
369 l_notifs := 'N';
370 END IF;
371
372 CLOSE csr_preparer_approve;
373 RETURN (l_notifs);
374 END notify_preparer_approve;
375
376
377
378 --
379 -- ----------------------------------------------------------------------------
380 -- |-------------------------< notify_preparer_reject >-----------------------|
381 -- ----------------------------------------------------------------------------
382
383 FUNCTION notify_preparer_reject (p_approval_style_id IN NUMBER)
384 RETURN VARCHAR2
385 IS
386 l_approval NUMBER (1) := 0;
387 l_notifs VARCHAR2 (1);
388
389 CURSOR csr_preparer_reject
390 IS
391 SELECT COUNT (1)
392 FROM hxc_approval_styles has,
393 hxc_approval_comps hac,
394 hxc_app_comp_notifications hacn,
395 hxc_app_comp_notif_usages hacnu
396 WHERE has.approval_style_id = p_approval_style_id
397 AND has.approval_style_id = hac.approval_style_id
398 AND hac.approval_comp_id = hacnu.approval_comp_id
399 AND hac.object_version_number = hacnu.approval_comp_ovn
400 AND hacnu.comp_notification_id = hacn.comp_notification_id
401 AND hacn.notification_action_code = 'REJECTED'
402 AND hacn.notification_recipient_code = 'PREPARER'
403 AND hacnu.enabled_flag = 'Y';
404 BEGIN
405 OPEN csr_preparer_reject;
406 FETCH csr_preparer_reject INTO l_approval;
407
408 IF l_approval <> 0
409 THEN
410 l_notifs := 'Y';
411 ELSE
412 l_notifs := 'N';
413 END IF;
414
415 CLOSE csr_preparer_reject;
416 RETURN (l_notifs);
417 END notify_preparer_reject;
418
419 --
420 -- ----------------------------------------------------------------------------
421 -- |-----------------------< notify_preparer_transfer >-----------------------|
422 -- ----------------------------------------------------------------------------
423
424
425 FUNCTION notify_preparer_transfer (p_approval_style_id IN NUMBER)
426 RETURN VARCHAR2
427 IS
428 l_approval NUMBER (13) := 0;
429 l_notifs VARCHAR2 (1);
430
431 CURSOR csr_preparer_transfer
432 IS
433 SELECT COUNT (1)
434 FROM hxc_approval_styles has,
435 hxc_approval_comps hac,
436 hxc_app_comp_notifications hacn,
437 hxc_app_comp_notif_usages hacnu
438 WHERE has.approval_style_id = p_approval_style_id
439 AND has.approval_style_id = hac.approval_style_id
440 AND hac.approval_comp_id = hacnu.approval_comp_id
441 AND hac.object_version_number = hacnu.approval_comp_ovn
442 AND hacnu.comp_notification_id = hacn.comp_notification_id
443 AND hacn.notification_action_code = 'TRANSFER'
444 AND hacn.notification_recipient_code = 'PREPARER'
445 AND hacnu.enabled_flag = 'Y';
446 BEGIN
447 OPEN csr_preparer_transfer;
448 FETCH csr_preparer_transfer INTO l_approval;
449
450 IF l_approval <> 0
451 THEN
452 l_notifs := 'Y';
453 ELSE
454 l_notifs := 'N';
455 END IF;
456
457 CLOSE csr_preparer_transfer;
458 RETURN (l_notifs);
459 END notify_preparer_transfer;
460 --
461 -- ----------------------------------------------------------------------------
462 -- |--------------------------< create_notification_records>------------------|
463 -- ----------------------------------------------------------------------------
464 --
465
466
467 Procedure create_notification_records
468 ( p_approval_style_id in number
469 ,p_approval_style_name in varchar2
470 ,p_timeouts_enabled in varchar2
471 ,p_number_retries in number
472 ,p_approver_timeout in number
473 ,p_preparer_timeout in number
474 ,p_admin_timeout in number
475 ,p_notify_supervisor in varchar2
476 ,p_notify_worker_on_submit in varchar2
477 ,p_notify_worker_on_aa in varchar2
478 ,p_notify_preparer_approved in varchar2
479 ,p_notify_preparer_rejected in varchar2
480 ,p_notify_preparer_transfer in varchar2
481 ) is
482 l_comp_notification_id hxc_app_comp_notifications_api.id_type;
483 l_object_version_number hxc_app_comp_notifications_api.ovn_type;
484 begin
485
486
487
488 If p_timeouts_enabled ='Y'
489 then
490 hxc_app_comp_notifications_api.create_app_comp_notification
491 (
492 p_notification_number_retries => p_number_retries
493 ,p_notification_timeout_value => p_approver_timeout
494 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
495 ,p_notification_recipient_code => 'APPROVER'
496 ,p_approval_style_name => p_approval_style_name
497 ,p_time_recipient_name => null
498 ,p_approval_component_id => null
499 ,p_comp_notification_id => l_comp_notification_id
500 ,p_object_version_number => l_object_version_number
501 );
502
503 hxc_app_comp_notifications_api.create_app_comp_notification
504 (
505 p_notification_number_retries => p_number_retries
506 ,p_notification_timeout_value => p_preparer_timeout
507 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
508 ,p_notification_recipient_code => 'PREPARER'
509 ,p_approval_style_name => p_approval_style_name
510 ,p_time_recipient_name => null
511 ,p_approval_component_id => null
512 ,p_comp_notification_id => l_comp_notification_id
513 ,p_object_version_number => l_object_version_number
514 ) ;
515
516
517 hxc_app_comp_notifications_api.create_app_comp_notification
518 (
519 p_notification_number_retries => p_number_retries
520 ,p_notification_timeout_value => p_admin_timeout
521 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
522 ,p_notification_recipient_code => 'ADMIN'
523 ,p_approval_style_name => p_approval_style_name
524 ,p_time_recipient_name => null
525 ,p_approval_component_id => null
526 ,p_comp_notification_id => l_comp_notification_id
527 ,p_object_version_number => l_object_version_number
528 ) ;
529
530 end if;
531
532 If p_notify_supervisor = 'Y'
533 then
534 hxc_app_comp_notifications_api.create_app_comp_notification
535 (
536 p_notification_number_retries => 0
537 ,p_notification_timeout_value => 0
538 ,p_notification_action_code => 'REQUEST-APPROVAL'
539 ,p_notification_recipient_code => 'SUPERVISOR'
540 ,p_approval_style_name => p_approval_style_name
541 ,p_time_recipient_name => null
542 ,p_approval_component_id => null
543 ,p_comp_notification_id => l_comp_notification_id
544 ,p_object_version_number => l_object_version_number
545 );
546 end if;
547
548 If p_notify_worker_on_submit = 'Y'
549 then
550 hxc_app_comp_notifications_api.create_app_comp_notification
551 (
552 p_notification_number_retries => 0
553 ,p_notification_timeout_value => 0
554 ,p_notification_action_code => 'SUBMISSION'
555 ,p_notification_recipient_code => 'WORKER'
556 ,p_approval_style_name => p_approval_style_name
557 ,p_time_recipient_name => null
558 ,p_approval_component_id => null
559 ,p_comp_notification_id => l_comp_notification_id
560 ,p_object_version_number => l_object_version_number
561 );
562 end if;
563
564 If p_notify_worker_on_aa = 'Y'
565 then
566 hxc_app_comp_notifications_api.create_app_comp_notification
567 (
568 p_notification_number_retries => 0
569 ,p_notification_timeout_value => 0
570 ,p_notification_action_code => 'AUTO-APPROVE'
571 ,p_notification_recipient_code => 'WORKER'
572 ,p_approval_style_name => p_approval_style_name
573 ,p_time_recipient_name => null
574 ,p_approval_component_id => null
575 ,p_comp_notification_id => l_comp_notification_id
576 ,p_object_version_number => l_object_version_number
577 ) ;
578 end if;
579
580 If p_notify_preparer_approved = 'Y'
581 then
582 hxc_app_comp_notifications_api.create_app_comp_notification
583 (
584 p_notification_number_retries => 0
585 ,p_notification_timeout_value => 0
586 ,p_notification_action_code => 'APPROVED'
587 ,p_notification_recipient_code => 'PREPARER'
588 ,p_approval_style_name => p_approval_style_name
589 ,p_time_recipient_name => null
590 ,p_approval_component_id => null
591 ,p_comp_notification_id => l_comp_notification_id
592 ,p_object_version_number => l_object_version_number
593 ) ;
594 end if;
595
596 If p_notify_preparer_rejected = 'Y'
597 then
598 hxc_app_comp_notifications_api.create_app_comp_notification
599 (
600 p_notification_number_retries => 0
601 ,p_notification_timeout_value => 0
602 ,p_notification_action_code => 'REJECTED'
603 ,p_notification_recipient_code => 'PREPARER'
604 ,p_approval_style_name => p_approval_style_name
605 ,p_time_recipient_name => null
606 ,p_approval_component_id => null
607 ,p_comp_notification_id => l_comp_notification_id
608 ,p_object_version_number => l_object_version_number
609 ) ;
610 end if;
611
612 If p_notify_preparer_transfer = 'Y' then
613
614 hxc_app_comp_notifications_api.create_app_comp_notification
615 (
616 p_notification_number_retries => 0
617 ,p_notification_timeout_value => 0
618 ,p_notification_action_code => 'TRANSFER'
619 ,p_notification_recipient_code => 'PREPARER'
620 ,p_approval_style_name => p_approval_style_name
621 ,p_time_recipient_name => null
622 ,p_approval_component_id => null
623 ,p_comp_notification_id => l_comp_notification_id
624 ,p_object_version_number => l_object_version_number
625 ) ;
626 end if;
627
628
629 end create_notification_records;
630
631 --
632 -- ----------------------------------------------------------------------------
633 -- |--------------------------< update_notification_records>------------------|
634 -- ----------------------------------------------------------------------------
635 --
636
637 Procedure update_notification_records
638 ( p_approval_style_id in number
639 ,p_approval_style_name in varchar2
640 ,p_timeouts_enabled in varchar2
641 ,p_number_retries in number
642 ,p_approver_timeout in number
643 ,p_preparer_timeout in number
644 ,p_admin_timeout in number
645 ,p_notify_supervisor in varchar2
646 ,p_notify_worker_on_submit in varchar2
647 ,p_notify_worker_on_aa in varchar2
648 ,p_notify_preparer_approved in varchar2
649 ,p_notify_preparer_rejected in varchar2
650 ,p_notify_preparer_transfer in varchar2
651 ) is
652 l_comp_notification_id hxc_app_comp_notifications.comp_notification_id%type;
653 l_object_version_number hxc_app_comp_notifications.object_version_number%type;
654 l_comp_notification_id_table hxc_app_comp_notifications_api.id_type;
655 l_object_version_number_table hxc_app_comp_notifications_api.ovn_type;
656 l_count number(9) :=0;
657 l_comp_notif_id_exist hxc_app_comp_notifications.comp_notification_id%type;
658 l_ovn_exist hxc_app_comp_notifications.object_version_number%type;
659 l_approval_comp_id hxc_approval_comps.approval_comp_id%type;
660 l_approval_comp_ovn hxc_approval_comps.object_version_number%type;
661 l_old_notif_rec hxc_notification_helper.g_rec_notif;
662 l_comp_notification_ovn number(9);
663
664 --
665 --Cursor to get the current status of the notifications
666 --
667
668 cursor csr_notif_record(p_approval_style_id number) is
669 select
670 approval_style_id,
671 timeouts_enabled,
672 number_retries,
673 approver_timeout,
674 preparer_timeout,
675 admin_timeout,
676 notify_supervisor,
677 notify_worker_on_submit,
678 notify_worker_on_aa,
679 notify_preparer_approved,
680 notify_preparer_rejected,
681 notify_preparer_transfer
682 from hxc_approval_styles_v
683 where
684 approval_style_id=p_approval_style_id;
685
686
687 --Cursor to find the entry in the notifications table linked to the given approval
688 --style having the specified action code and recipient code.
689
690 cursor csr_notif_id
691 (p_approval_style_id in number
692 ,p_notification_action_code in varchar2
693 ,p_notification_recipient_code in varchar2) is
694 select hacn.comp_notification_id,hacn.object_version_number
695 from
696 hxc_approval_styles has,
697 hxc_approval_comps hac,
698 hxc_app_comp_notifications hacn,
699 hxc_app_comp_notif_usages hacnu
700 where
701 has.approval_style_id = p_approval_style_id and
702 has.approval_style_id = hac.approval_style_id and
703 hac.approval_comp_id=hacnu.approval_comp_id and
704 hac.object_version_number=hacnu.approval_comp_ovn and
705 hacnu.comp_notification_id=hacn.comp_notification_id and
706 hacnu.comp_notification_ovn=hacn.object_version_number and
707 hacn.notification_action_code = p_notification_action_code and
708 hacn.notification_recipient_code=p_notification_recipient_code;
709
710
711
712 --
713 -- Cursor to check if the notification record with the new values already exists
714 --
715
716 cursor csr_notif_already_exists
717 (p_notification_number_retries in number, p_notification_timeout_value in number, p_notification_recipient_code in varchar2) is
718 select hacn.comp_notification_id, hacn.object_version_number
719 from hxc_app_comp_notifications hacn where
720 notification_number_retries=p_notification_number_retries and
721 notification_timeout_value=p_notification_timeout_value and
722 notification_action_code='REQUEST-APPROVAL-RESEND' and
723 notification_recipient_code=p_notification_recipient_code and
724 object_version_number=(select max(object_version_number)
725 from hxc_app_comp_notifications
726 where comp_notification_id=hacn.comp_notification_id);
727
728 --
729 -- Cursor to check if the notification record being updated is being used by other
730 -- aproval styles as well.
731 --
732
733
734
735
736 cursor csr_chk_notif_used_byothers
737 (p_approval_style_id in number
738 ,p_comp_notification_id in number
739 ,p_object_version_number in number)
740 is
741 select count(1) from
742 hxc_approval_comps hac,
743 hxc_app_comp_notifications hacn,
744 hxc_app_comp_notif_usages hacnu
745 where
746 hac.approval_comp_id not in (select hac.approval_comp_id
747 from hxc_approval_comps hac
748 where
749 approval_style_id = p_approval_style_id) and
750 hac.approval_comp_id=hacnu.approval_comp_id and
751 hacn.comp_notification_id=hacnu.comp_notification_id and
752 hacn.object_version_number=hacnu.comp_notification_ovn and
753 hacnu.comp_notification_id=p_comp_notification_id and
754 hacnu.comp_notification_ovn=p_object_version_number;
755
756
757 cursor csr_approval_comp_id(p_approval_style in varchar2)
758 is select hac.approval_comp_id,hac.object_version_number
759 from
760 hxc_approval_styles has,
761 hxc_approval_comps hac
762 where
763 has.name=p_approval_style and
764 has.approval_style_id = hac.approval_style_id and
765 hac.object_version_number = (SELECT MAX (object_version_number)
766 FROM hxc_approval_comps
767 WHERE approval_comp_id =hac.approval_comp_id);
768
769 cursor csr_del_disabled_usages(p_approval_style_id in number)
770 is select hacnu.approval_comp_id,hacnu.approval_comp_ovn,
771 hacnu.comp_notification_id,hacnu.comp_notification_ovn from
772 hxc_app_comp_notif_usages hacnu,
773 hxc_approval_styles has,
774 hxc_approval_comps hac,
775 hxc_app_comp_notifications hacn
776 where
777 has.approval_style_id=p_approval_style_id and
778 has.approval_style_id=hac.approval_style_id and
779 hac.approval_comp_id=hacnu.approval_comp_id and
780 hac.object_version_number=hacnu.approval_comp_ovn and
781 hacnu.comp_notification_id=hacn.comp_notification_id and
782 hacn.notification_action_code='REQUEST-APPROVAL-RESEND' and
783 hacnu.enabled_flag='N';
784
785 begin
786
787 Open csr_notif_record(p_approval_style_id);
788 Fetch csr_notif_record Into l_old_notif_rec;
789
790
791
792 if p_timeouts_enabled ='N' and l_old_notif_rec.timeouts_enabled='Y'
793 then
794 hxc_app_comp_notifications_api.disable_timeout_notifications
795 (
796 p_approval_style_name => p_approval_style_name
797 ) ;
798
799 elsif p_timeouts_enabled ='Y' and l_old_notif_rec.timeouts_enabled='N'
800 then
801 ---if the notification usages already exists we have to delete those
802 open csr_del_disabled_usages(p_approval_style_id);
803 loop
804 fetch csr_del_disabled_usages into
805 l_approval_comp_id,l_approval_comp_ovn,
806 l_comp_notification_id,l_comp_notification_ovn;
807 exit when csr_del_disabled_usages%notfound;
808 delete
809 from hxc_app_comp_notif_usages where
810 approval_comp_id=l_approval_comp_id and
811 approval_comp_ovn=l_approval_comp_ovn and
812 comp_notification_id=l_comp_notification_id and
813 comp_notification_ovn=l_comp_notification_ovn;
814 end loop;
815 close csr_del_disabled_usages;
816
817 hxc_app_comp_notifications_api.create_app_comp_notification
818 (
819 p_notification_number_retries => p_number_retries
820 ,p_notification_timeout_value => p_approver_timeout
821 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
822 ,p_notification_recipient_code => 'APPROVER'
823 ,p_approval_style_name => p_approval_style_name
824 ,p_time_recipient_name => null
825 ,p_approval_component_id => null
826 ,p_comp_notification_id => l_comp_notification_id_table
827 ,p_object_version_number => l_object_version_number_table
828 );
829 hxc_app_comp_notifications_api.create_app_comp_notification
830 (
831 p_notification_number_retries => p_number_retries
832 ,p_notification_timeout_value => p_preparer_timeout
833 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
834 ,p_notification_recipient_code => 'PREPARER'
835 ,p_approval_style_name => p_approval_style_name
836 ,p_time_recipient_name => null
837 ,p_approval_component_id => null
838 ,p_comp_notification_id => l_comp_notification_id_table
839 ,p_object_version_number => l_object_version_number_table
840 ) ;
841
842
843 hxc_app_comp_notifications_api.create_app_comp_notification
844 (
845 p_notification_number_retries => p_number_retries
846 ,p_notification_timeout_value => p_admin_timeout
847 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
848 ,p_notification_recipient_code => 'ADMIN'
849 ,p_approval_style_name => p_approval_style_name
850 ,p_time_recipient_name => null
851 ,p_approval_component_id => null
852 ,p_comp_notification_id => l_comp_notification_id_table
853 ,p_object_version_number => l_object_version_number_table
854 ) ;
855
856 elsif p_timeouts_enabled ='Y' and l_old_notif_rec.timeouts_enabled='Y'
857 then
858 if p_number_retries <> l_old_notif_rec.number_retries
859 then
860 ---
861 --- For APPROVER
862 ---
863
864 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','APPROVER');
865 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
866 close csr_notif_id;
867
868 open csr_chk_notif_used_byothers(p_approval_style_id,
869 l_comp_notification_id,
870 l_object_version_number);
871 fetch csr_chk_notif_used_byothers into l_count;
872 close csr_chk_notif_used_byothers;
873
874 open csr_notif_already_exists(p_number_retries,p_approver_timeout,'APPROVER');
875 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
876
877 if csr_notif_already_exists%found
878 then
879 open csr_approval_comp_id(p_approval_style_name);
880 loop
881 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
882 exit when csr_approval_comp_id%notfound;
883
884 update hxc_app_comp_notif_usages
885 set
886 comp_notification_id = l_comp_notif_id_exist,
887 comp_notification_ovn = l_ovn_exist
888 where
889 approval_comp_id = l_approval_comp_id and
890 approval_comp_ovn= l_approval_comp_ovn and
891 comp_notification_id=l_comp_notification_id and
892 comp_notification_ovn=l_object_version_number;
893 end loop;
894 close csr_approval_comp_id;
895
896 else
897 if(l_count=0) --- enters if not used
898 then
899 hxc_app_comp_notifications_api.update_app_comp_notification
900 (
901 p_comp_notification_id => l_comp_notification_id
902 ,p_object_version_number => l_object_version_number
903 ,p_notification_number_retries => p_number_retries
904 ,p_notification_timeout_value => p_approver_timeout
905 );
906
907 else
908 hxc_app_comp_notifications_api.create_app_comp_notification
909 (
910 p_notification_number_retries => p_number_retries
911 ,p_notification_timeout_value => p_approver_timeout
912 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
913 ,p_notification_recipient_code => 'APPROVER'
914 ,p_approval_style_name => p_approval_style_name
915 ,p_time_recipient_name => null
916 ,p_approval_component_id => null
917 ,p_comp_notification_id => l_comp_notification_id_table
918 ,p_object_version_number => l_object_version_number_table
919 );
920 open csr_approval_comp_id(p_approval_style_name);
921 loop
922 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
923 exit when csr_approval_comp_id%notfound;
924 delete from hxc_app_comp_notif_usages
925 where
926 approval_comp_id =l_approval_comp_id and
927 approval_comp_ovn =l_approval_comp_ovn and
928 comp_notification_id=l_comp_notification_id and
929 comp_notification_ovn = l_object_version_number;
930 end loop;
931 close csr_approval_comp_id;
932 end if;
933 end if;
934 close csr_notif_already_exists;
935 --
936 -- For Preparer
937 --
938
939 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','PREPARER');
940 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
941 close csr_notif_id;
942 open csr_chk_notif_used_byothers(p_approval_style_id,
943 l_comp_notification_id,
944 l_object_version_number);
945 fetch csr_chk_notif_used_byothers into l_count;
946 close csr_chk_notif_used_byothers;
947
948 open csr_notif_already_exists(p_number_retries,p_preparer_timeout,'PREPARER');
949 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
950
951 if csr_notif_already_exists%found
952 then
953
954 open csr_approval_comp_id(p_approval_style_name);
955 loop
956 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
957 exit when csr_approval_comp_id%notfound;
958
959 update hxc_app_comp_notif_usages
960 set
961 comp_notification_id = l_comp_notif_id_exist,
962 comp_notification_ovn = l_ovn_exist
963 where
964 approval_comp_id = l_approval_comp_id and
965 approval_comp_ovn= l_approval_comp_ovn and
966 comp_notification_id=l_comp_notification_id and
967 comp_notification_ovn=l_object_version_number;
968 end loop;
969 close csr_approval_comp_id;
970
971 else
972 if(l_count=0) --- enters if not used
973 then
974
975
976 hxc_app_comp_notifications_api.update_app_comp_notification
977 (
978 p_comp_notification_id => l_comp_notification_id
979 ,p_object_version_number => l_object_version_number
980 ,p_notification_number_retries => p_number_retries
981 ,p_notification_timeout_value => p_preparer_timeout
982 );
983
984
985 else
986
987 hxc_app_comp_notifications_api.create_app_comp_notification
988 (
989 p_notification_number_retries => p_number_retries
990 ,p_notification_timeout_value => p_preparer_timeout
991 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
992 ,p_notification_recipient_code => 'PREPARER'
993 ,p_approval_style_name => p_approval_style_name
994 ,p_time_recipient_name => null
995 ,p_approval_component_id => null
996 ,p_comp_notification_id => l_comp_notification_id_table
997 ,p_object_version_number => l_object_version_number_table
998 );
999 open csr_approval_comp_id(p_approval_style_name);
1000 loop
1001 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1002 exit when csr_approval_comp_id%notfound;
1003 delete from hxc_app_comp_notif_usages
1004 where
1005 approval_comp_id =l_approval_comp_id and
1006 approval_comp_ovn =l_approval_comp_ovn and
1007 comp_notification_id=l_comp_notification_id and
1008 comp_notification_ovn = l_object_version_number;
1009 end loop;
1010 close csr_approval_comp_id;
1011 end if;
1012 end if;
1013 close csr_notif_already_exists;
1014 ---
1015 --- For ADMIN
1016 ---
1017
1018 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','ADMIN');
1019 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
1020 close csr_notif_id;
1021 open csr_chk_notif_used_byothers(p_approval_style_id,
1022 l_comp_notification_id,
1023 l_object_version_number);
1024 fetch csr_chk_notif_used_byothers into l_count;
1025 close csr_chk_notif_used_byothers;
1026
1027 open csr_notif_already_exists(p_number_retries,p_admin_timeout,'ADMIN');
1028 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
1029
1030 if csr_notif_already_exists%found
1031 then
1032
1033 open csr_approval_comp_id(p_approval_style_name);
1034 loop
1035 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1036 exit when csr_approval_comp_id%notfound;
1037
1038 update hxc_app_comp_notif_usages
1039 set
1040 comp_notification_id = l_comp_notif_id_exist,
1041 comp_notification_ovn = l_ovn_exist
1042 where
1043 approval_comp_id = l_approval_comp_id and
1044 approval_comp_ovn= l_approval_comp_ovn and
1045 comp_notification_id=l_comp_notification_id and
1046 comp_notification_ovn=l_object_version_number;
1047 end loop;
1048 close csr_approval_comp_id;
1049
1050 else
1051 if(l_count=0) --- enters if not used
1052 then
1053
1054
1055 hxc_app_comp_notifications_api.update_app_comp_notification
1056 (
1057 p_comp_notification_id => l_comp_notification_id
1058 ,p_object_version_number => l_object_version_number
1059 ,p_notification_number_retries => p_number_retries
1060 ,p_notification_timeout_value => p_admin_timeout
1061 );
1062
1063
1064 else
1065
1066 hxc_app_comp_notifications_api.create_app_comp_notification
1067 (
1068 p_notification_number_retries => p_number_retries
1069 ,p_notification_timeout_value => p_admin_timeout
1070 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
1071 ,p_notification_recipient_code => 'ADMIN'
1072 ,p_approval_style_name => p_approval_style_name
1073 ,p_time_recipient_name => null
1074 ,p_approval_component_id => null
1075 ,p_comp_notification_id => l_comp_notification_id_table
1076 ,p_object_version_number => l_object_version_number_table
1077 );
1078 open csr_approval_comp_id(p_approval_style_name);
1079 loop
1080 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1081 exit when csr_approval_comp_id%notfound;
1082 delete from hxc_app_comp_notif_usages
1083 where
1084 approval_comp_id =l_approval_comp_id and
1085 approval_comp_ovn =l_approval_comp_ovn and
1086 comp_notification_id=l_comp_notification_id and
1087 comp_notification_ovn = l_object_version_number;
1088 end loop;
1089 close csr_approval_comp_id;
1090 end if;
1091 end if;
1092 close csr_notif_already_exists;
1093 else
1094 --- If there is no change in number of retries
1095 ---
1096 --- For Approver
1097 ---
1098 if (p_approver_timeout <> l_old_notif_rec.approver_timeout)
1099 then
1100 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','APPROVER');
1101 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
1102 close csr_notif_id;
1103 open csr_chk_notif_used_byothers(p_approval_style_id,
1104 l_comp_notification_id,
1105 l_object_version_number);
1106 fetch csr_chk_notif_used_byothers into l_count;
1107 close csr_chk_notif_used_byothers;
1108
1109 open csr_notif_already_exists(p_number_retries,p_approver_timeout,'APPROVER');
1110 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
1111
1112 if csr_notif_already_exists%found
1113 then
1114 open csr_approval_comp_id(p_approval_style_name);
1115 loop
1116 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1117 exit when csr_approval_comp_id%notfound;
1118
1119 update hxc_app_comp_notif_usages
1120 set
1121 comp_notification_id = l_comp_notif_id_exist,
1122 comp_notification_ovn = l_ovn_exist
1123 where
1124 approval_comp_id = l_approval_comp_id and
1125 approval_comp_ovn= l_approval_comp_ovn and
1126 comp_notification_id=l_comp_notification_id and
1127 comp_notification_ovn=l_object_version_number;
1128 end loop;
1129 close csr_approval_comp_id;
1130
1131 else
1132 if(l_count=0) --- enters if not used
1133 then
1134
1135
1136 hxc_app_comp_notifications_api.update_app_comp_notification
1137 (
1138 p_comp_notification_id => l_comp_notification_id
1139 ,p_object_version_number => l_object_version_number
1140 ,p_notification_timeout_value => p_approver_timeout
1141 );
1142
1143
1144 else
1145
1146 hxc_app_comp_notifications_api.create_app_comp_notification
1147 (
1148 p_notification_number_retries => p_number_retries
1149 ,p_notification_timeout_value => p_approver_timeout
1150 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
1151 ,p_notification_recipient_code => 'APPROVER'
1152 ,p_approval_style_name => p_approval_style_name
1153 ,p_time_recipient_name => null
1154 ,p_approval_component_id => null
1155 ,p_comp_notification_id => l_comp_notification_id_table
1156 ,p_object_version_number => l_object_version_number_table
1157 );
1158 open csr_approval_comp_id(p_approval_style_name);
1159 loop
1160 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1161 exit when csr_approval_comp_id%notfound;
1162 delete from hxc_app_comp_notif_usages
1163 where
1164 approval_comp_id =l_approval_comp_id and
1165 approval_comp_ovn =l_approval_comp_ovn and
1166 comp_notification_id=l_comp_notification_id and
1167 comp_notification_ovn = l_object_version_number;
1168 end loop;
1169 close csr_approval_comp_id;
1170 end if;
1171 end if;
1172 close csr_notif_already_exists;
1173
1174 end if;
1175 ---
1176 --- For Preparer
1177 ---
1178 if (p_preparer_timeout <> l_old_notif_rec.preparer_timeout)
1179 then
1180 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','PREPARER');
1181 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
1182 close csr_notif_id;
1183 open csr_chk_notif_used_byothers(p_approval_style_id,
1184 l_comp_notification_id,
1185 l_object_version_number);
1186 fetch csr_chk_notif_used_byothers into l_count;
1187 close csr_chk_notif_used_byothers;
1188
1189 open csr_notif_already_exists(p_number_retries,p_preparer_timeout,'PREPARER');
1190 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
1191
1192 if csr_notif_already_exists%found
1193 then
1194 open csr_approval_comp_id(p_approval_style_name);
1195 loop
1196 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1197 exit when csr_approval_comp_id%notfound;
1198
1199 update hxc_app_comp_notif_usages
1200 set
1201 comp_notification_id = l_comp_notif_id_exist,
1202 comp_notification_ovn = l_ovn_exist
1203 where
1204 approval_comp_id = l_approval_comp_id and
1205 approval_comp_ovn= l_approval_comp_ovn and
1206 comp_notification_id=l_comp_notification_id and
1207 comp_notification_ovn=l_object_version_number;
1208 end loop;
1209 close csr_approval_comp_id;
1210
1211 else
1212 if(l_count=0) --- enters if not used
1213 then
1214
1215
1216 hxc_app_comp_notifications_api.update_app_comp_notification
1217 (
1218 p_comp_notification_id => l_comp_notification_id
1219 ,p_object_version_number => l_object_version_number
1220 ,p_notification_timeout_value => p_preparer_timeout
1221 );
1222
1223
1224 else
1225
1226 hxc_app_comp_notifications_api.create_app_comp_notification
1227 (
1228 p_notification_number_retries => p_number_retries
1229 ,p_notification_timeout_value => p_preparer_timeout
1230 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
1231 ,p_notification_recipient_code => 'PREPARER'
1232 ,p_approval_style_name => p_approval_style_name
1233 ,p_time_recipient_name => null
1234 ,p_approval_component_id => null
1235 ,p_comp_notification_id => l_comp_notification_id_table
1236 ,p_object_version_number => l_object_version_number_table
1237 );
1238 open csr_approval_comp_id(p_approval_style_name);
1239 loop
1240 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1241 exit when csr_approval_comp_id%notfound;
1242 delete from hxc_app_comp_notif_usages
1243 where
1244 approval_comp_id =l_approval_comp_id and
1245 approval_comp_ovn =l_approval_comp_ovn and
1246 comp_notification_id=l_comp_notification_id and
1247 comp_notification_ovn = l_object_version_number;
1248 end loop;
1249 close csr_approval_comp_id;
1250 end if;
1251 end if;
1252 close csr_notif_already_exists;
1253
1254 end if;
1255 ---
1256 --- For ADMIN
1257 ---
1258
1259 if (p_admin_timeout <> l_old_notif_rec.admin_timeout)
1260 then
1261 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL-RESEND','ADMIN');
1262 fetch csr_notif_id into l_comp_notification_id, l_object_version_number;
1263 close csr_notif_id;
1264 open csr_chk_notif_used_byothers(p_approval_style_id,
1265 l_comp_notification_id,
1266 l_object_version_number);
1267 fetch csr_chk_notif_used_byothers into l_count;
1268 close csr_chk_notif_used_byothers;
1269
1270 open csr_notif_already_exists(p_number_retries,p_admin_timeout,'ADMIN');
1271 fetch csr_notif_already_exists into l_comp_notif_id_exist,l_ovn_exist;
1272
1273 if csr_notif_already_exists%found
1274 then
1275 open csr_approval_comp_id(p_approval_style_name);
1276 loop
1277 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1278 exit when csr_approval_comp_id%notfound;
1279
1280 update hxc_app_comp_notif_usages
1281 set
1282 comp_notification_id = l_comp_notif_id_exist,
1283 comp_notification_ovn = l_ovn_exist
1284 where
1285 approval_comp_id = l_approval_comp_id and
1286 approval_comp_ovn= l_approval_comp_ovn and
1287 comp_notification_id=l_comp_notification_id and
1288 comp_notification_ovn=l_object_version_number;
1289 end loop;
1290 close csr_approval_comp_id;
1291 else
1292 if(l_count=0) --- enters if not used
1293 then
1294
1295
1296 hxc_app_comp_notifications_api.update_app_comp_notification
1297 (
1298 p_comp_notification_id => l_comp_notification_id
1299 ,p_object_version_number => l_object_version_number
1300 ,p_notification_timeout_value => p_admin_timeout
1301 );
1302
1303
1304 else
1305
1306 hxc_app_comp_notifications_api.create_app_comp_notification
1307 (
1308 p_notification_number_retries => p_number_retries
1309 ,p_notification_timeout_value => p_admin_timeout
1310 ,p_notification_action_code => 'REQUEST-APPROVAL-RESEND'
1311 ,p_notification_recipient_code => 'ADMIN'
1312 ,p_approval_style_name => p_approval_style_name
1313 ,p_time_recipient_name => null
1314 ,p_approval_component_id => null
1315 ,p_comp_notification_id => l_comp_notification_id_table
1316 ,p_object_version_number => l_object_version_number_table
1317 );
1318 open csr_approval_comp_id(p_approval_style_name);
1319 loop
1320 fetch csr_approval_comp_id into l_approval_comp_id,l_approval_comp_ovn;
1321 exit when csr_approval_comp_id%notfound;
1322 delete from hxc_app_comp_notif_usages
1323 where
1324 approval_comp_id =l_approval_comp_id and
1325 approval_comp_ovn =l_approval_comp_ovn and
1326 comp_notification_id=l_comp_notification_id and
1327 comp_notification_ovn = l_object_version_number;
1328 end loop;
1329 close csr_approval_comp_id;
1330 end if;
1331 end if;
1332 close csr_notif_already_exists;
1333 end if;
1334 end if;
1335 end if;
1336
1337 -- Non-timeout notifications
1338
1339 -- Checking REQUEST-APPROVAL/SUPERVISOR notification
1340
1341 If (p_notify_supervisor = 'Y' and l_old_notif_rec.notify_supervisor = 'N')
1342 then
1343
1344 hxc_app_comp_notifications_api.create_app_comp_notification
1345 (
1346 p_notification_number_retries => 0
1347 ,p_notification_timeout_value => 0
1348 ,p_notification_action_code => 'REQUEST-APPROVAL'
1349 ,p_notification_recipient_code => 'SUPERVISOR'
1350 ,p_approval_style_name => p_approval_style_name
1351 ,p_time_recipient_name => null
1352 ,p_approval_component_id => null
1353 ,p_comp_notification_id => l_comp_notification_id_table
1354 ,p_object_version_number => l_object_version_number_table
1355 );
1356 elsif(p_notify_supervisor = 'N' and l_old_notif_rec.notify_supervisor = 'Y')
1357 then
1358
1359 open csr_notif_id(p_approval_style_id,'REQUEST-APPROVAL','SUPERVISOR');
1360
1361 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1362 update hxc_app_comp_notif_usages
1363 set
1364 enabled_flag='N' where
1365 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1366 where approval_style_id=p_approval_style_id) and
1367 comp_notification_id=l_comp_notification_id and
1368 comp_notification_ovn=l_object_version_number;
1369 close csr_notif_id;
1370
1371
1372 end if;
1373
1374 -- Checking SUBMISSION/WORKER notification
1375 If (p_notify_worker_on_submit = 'Y' and l_old_notif_rec.notify_worker_on_submit = 'N')
1376 then
1377 hxc_app_comp_notifications_api.create_app_comp_notification
1378 (
1379 p_notification_number_retries => 0
1380 ,p_notification_timeout_value => 0
1381 ,p_notification_action_code => 'SUBMISSION'
1382 ,p_notification_recipient_code => 'WORKER'
1383 ,p_approval_style_name => p_approval_style_name
1384 ,p_time_recipient_name => null
1385 ,p_approval_component_id => null
1386 ,p_comp_notification_id => l_comp_notification_id_table
1387 ,p_object_version_number => l_object_version_number_table
1388 );
1389 elsif(p_notify_worker_on_submit = 'N' and l_old_notif_rec.notify_worker_on_submit = 'Y')
1390 then
1391 open csr_notif_id(p_approval_style_id,'SUBMISSION','WORKER');
1392 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1393 update hxc_app_comp_notif_usages
1394 set
1395 enabled_flag='N' where
1396 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1397 where approval_style_id=p_approval_style_id) and
1398 comp_notification_id=l_comp_notification_id and
1399 comp_notification_ovn=l_object_version_number;
1400 close csr_notif_id;
1401 end if;
1402
1403 -- Checking AUTO-APPROVE/WORKER notification
1404 If (p_notify_worker_on_aa = 'Y' and l_old_notif_rec.notify_worker_on_aa = 'N')
1405 then
1406 hxc_app_comp_notifications_api.create_app_comp_notification
1407 (
1408 p_notification_number_retries => 0
1409 ,p_notification_timeout_value => 0
1410 ,p_notification_action_code => 'AUTO-APPROVE'
1411 ,p_notification_recipient_code => 'WORKER'
1412 ,p_approval_style_name => p_approval_style_name
1413 ,p_time_recipient_name => null
1414 ,p_approval_component_id => null
1415 ,p_comp_notification_id => l_comp_notification_id_table
1416 ,p_object_version_number => l_object_version_number_table
1417 );
1418 elsif(p_notify_worker_on_aa = 'N' and l_old_notif_rec.notify_worker_on_aa = 'Y')
1419 then
1420 open csr_notif_id(p_approval_style_id,'AUTO-APPROVE','WORKER');
1421
1422 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1423 update hxc_app_comp_notif_usages
1424 set
1425 enabled_flag='N' where
1426 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1427 where approval_style_id=p_approval_style_id) and
1428 comp_notification_id=l_comp_notification_id and
1429 comp_notification_ovn=l_object_version_number;
1430
1431 close csr_notif_id;
1432 end if;
1433
1434 -- Checking APPROVED/PREPARER notification
1435 If (p_notify_preparer_approved = 'Y' and l_old_notif_rec.notify_preparer_approved = 'N')
1436 then
1437 hxc_app_comp_notifications_api.create_app_comp_notification
1438 (
1439 p_notification_number_retries => 0
1440 ,p_notification_timeout_value => 0
1441 ,p_notification_action_code => 'APPROVED'
1442 ,p_notification_recipient_code => 'PREPARER'
1443 ,p_approval_style_name => p_approval_style_name
1444 ,p_time_recipient_name => null
1445 ,p_approval_component_id => null
1446 ,p_comp_notification_id => l_comp_notification_id_table
1447 ,p_object_version_number => l_object_version_number_table
1448 );
1449 elsif(p_notify_preparer_approved = 'N' and l_old_notif_rec.notify_preparer_approved = 'Y')
1450 then
1451 open csr_notif_id(p_approval_style_id,'APPROVED','PREPARER');
1452
1453 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1454 update hxc_app_comp_notif_usages
1455 set
1456 enabled_flag='N' where
1457 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1458 where approval_style_id=p_approval_style_id) and
1459 comp_notification_id=l_comp_notification_id and
1460 comp_notification_ovn=l_object_version_number;
1461
1462 close csr_notif_id;
1463 end if;
1464
1465 -- Checking REJECTED/PREPARER notification
1466 If (p_notify_preparer_rejected = 'Y' and l_old_notif_rec.notify_preparer_rejected = 'N')
1467 then
1468 hxc_app_comp_notifications_api.create_app_comp_notification
1469 (
1470 p_notification_number_retries => 0
1471 ,p_notification_timeout_value => 0
1472 ,p_notification_action_code => 'REJECTED'
1473 ,p_notification_recipient_code => 'PREPARER'
1474 ,p_approval_style_name => p_approval_style_name
1475 ,p_time_recipient_name => null
1476 ,p_approval_component_id => null
1477 ,p_comp_notification_id => l_comp_notification_id_table
1478 ,p_object_version_number => l_object_version_number_table
1479 );
1480 elsif(p_notify_preparer_rejected = 'N' and l_old_notif_rec.notify_preparer_rejected = 'Y')
1481 then
1482 open csr_notif_id(p_approval_style_id,'REJECTED','PREPARER');
1483 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1484 update hxc_app_comp_notif_usages
1485 set
1486 enabled_flag='N' where
1487 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1488 where approval_style_id=p_approval_style_id) and
1489 comp_notification_id=l_comp_notification_id and
1490 comp_notification_ovn=l_object_version_number;
1491 close csr_notif_id;
1492 end if;
1493
1494
1495 -- Checking TRANSFER/PREPARER notification
1496 If (p_notify_preparer_transfer = 'Y' and l_old_notif_rec.notify_preparer_transfer = 'N')
1497 then
1498 hxc_app_comp_notifications_api.create_app_comp_notification
1499 (
1500 p_notification_number_retries => 0
1501 ,p_notification_timeout_value => 0
1502 ,p_notification_action_code => 'TRANSFER'
1503 ,p_notification_recipient_code => 'PREPARER'
1504 ,p_approval_style_name => p_approval_style_name
1505 ,p_time_recipient_name => null
1506 ,p_approval_component_id => null
1507 ,p_comp_notification_id => l_comp_notification_id_table
1508 ,p_object_version_number => l_object_version_number_table
1509 );
1510 elsif(p_notify_preparer_transfer = 'N' and l_old_notif_rec.notify_preparer_transfer = 'Y')
1511 then
1512 open csr_notif_id(p_approval_style_id,'TRANSFER','PREPARER');
1513 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1514 update hxc_app_comp_notif_usages
1515 set
1516 enabled_flag='N' where
1517 approval_comp_id in (select approval_comp_id from hxc_approval_comps
1518 where approval_style_id=p_approval_style_id) and
1519 comp_notification_id=l_comp_notification_id and
1520 comp_notification_ovn=l_object_version_number;
1521 close csr_notif_id;
1522 end if;
1523
1524
1525 end update_notification_records;
1526
1527
1528 --
1529 -- ----------------------------------------------------------------------------
1530 -- |--------------------------< delete_notification_records>------------------|
1531 -- ----------------------------------------------------------------------------
1532
1533 Procedure delete_notification_records
1534 (
1535 p_approval_style_id in hxc_approval_styles.approval_style_id%type
1536 ) is
1537
1538
1539 --
1540 -- Cursor to fetch all the notifications associated with a approval_style
1541 --
1542 cursor csr_notif_id
1543 (p_approval_style_id in number) is
1544 select distinct hacn.comp_notification_id,hacn.object_version_number
1545 from
1546 hxc_approval_styles has,
1547 hxc_approval_comps hac,
1548 hxc_app_comp_notifications hacn,
1549 hxc_app_comp_notif_usages hacnu
1550 where
1551 has.approval_style_id = p_approval_style_id and
1552 has.approval_style_id = hac.approval_style_id and
1553 hac.approval_comp_id=hacnu.approval_comp_id and
1554 hac.object_version_number=hacnu.approval_comp_ovn and
1555 hacnu.comp_notification_id=hacn.comp_notification_id and
1556 hacnu.comp_notification_ovn=hacn.object_version_number;
1557
1558 cursor csr_chk_notif_used_byothers
1559 (p_approval_style_id in number
1560 ,p_comp_notification_id in number
1561 ,p_object_version_number in number)
1562 is
1563 select count(1) from
1564 hxc_approval_comps hac,
1565 hxc_app_comp_notifications hacn,
1566 hxc_app_comp_notif_usages hacnu
1567 where
1568 hac.approval_comp_id not in (select hac.approval_comp_id from hxc_approval_comps hac
1569 where approval_style_id = p_approval_style_id) and
1570 hac.approval_comp_id=hacnu.approval_comp_id and
1571 hacn.comp_notification_id=hacnu.comp_notification_id and
1572 hacn.object_version_number=hacnu.comp_notification_ovn and
1573 hacnu.comp_notification_id=p_comp_notification_id and
1574 hacnu.comp_notification_ovn=p_object_version_number;
1575
1576 l_count number(9) :=0;
1577 l_comp_notification_id hxc_app_comp_notifications.comp_notification_id%type;
1578 l_object_version_number hxc_app_comp_notifications.object_version_number%type;
1579
1580 begin
1581
1582
1583 open csr_notif_id(p_approval_style_id);
1584 loop
1585
1586 fetch csr_notif_id into l_comp_notification_id,l_object_version_number;
1587 exit when csr_notif_id%notfound;
1588
1589 open csr_chk_notif_used_byothers(p_approval_style_id,l_comp_notification_id,l_object_version_number);
1590 fetch csr_chk_notif_used_byothers into l_count;
1591 close csr_chk_notif_used_byothers;
1592
1593 if(l_count =0)
1594 then
1595
1596 hxc_app_comp_notifications_api.delete_app_comp_notification
1597 (
1598 p_comp_notification_id => l_comp_notification_id
1599 ,p_object_version_number => l_object_version_number
1600 );
1601
1602 hxc_app_comp_notifications_api.purge_comp_notification
1603 (
1604 p_comp_notification_id => l_comp_notification_id
1605 ,p_object_version_number => l_object_version_number
1606 );
1607 else
1608
1609 delete from hxc_app_comp_notif_usages
1610 where
1611 approval_comp_id in
1612 (select approval_comp_id
1613 from hxc_approval_comps where
1614 approval_style_id=p_approval_style_id) and
1615 comp_notification_id = l_comp_notification_id and
1616 comp_notification_ovn = l_object_version_number;
1617
1618
1619 end if;
1620 end loop;
1621 close csr_notif_id;
1622
1623
1624
1625 end delete_notification_records;
1626
1627 --
1628 -- ----------------------------------------------------------------------------
1629 -- |--------------------------< approval_comps_usages>------------------------|
1630 -- ----------------------------------------------------------------------------
1631 Procedure approval_comps_usages
1632 ( p_approval_style_id in number
1633 ,p_approval_comp_id in number
1634 ,p_approval_comp_ovn in number
1635 ,p_dml_action in varchar2
1636 )
1637 is
1638
1639 l_count number(9):=0;
1640 l_comp_notification_id number(9);
1641 l_comp_notification_ovn number(9);
1642 l_enabled_flag varchar2(1);
1643 l_existing_approval_comp_id number(9);
1644
1645 cursor csr_chk_comps_usages
1646 (
1647 p_approval_comp_id in number
1648 )
1649 is
1650 select count(1) from
1651 hxc_app_comp_notif_usages hacnu,
1652 hxc_approval_comps hac,
1653 hxc_approval_comps hac1
1654 where
1655 hac.approval_comp_id=p_approval_comp_id and
1656 hac.approval_comp_id<>hac1.approval_comp_id and
1657 hac.approval_style_id=hac1.approval_style_id and
1658 hac1.approval_comp_id=hacnu.approval_comp_id;
1659
1660 cursor csr_approval_comp (p_approval_style_id in number)
1661 is
1662 select hac.approval_comp_id from
1663 hxc_approval_styles has,
1664 hxc_approval_comps hac
1665 where
1666 has.approval_style_id=p_approval_style_id and
1667 has.approval_style_id=hac.approval_style_id and
1668 hac.approval_comp_id in (select approval_comp_id from hxc_app_comp_notif_usages);
1669
1670 cursor csr_comp_notification (p_approval_comp_id in number) is
1671 select hacnu.comp_notification_id,hacnu.comp_notification_ovn,hacnu.enabled_flag
1672 from
1673 hxc_approval_comps hac,
1674 hxc_app_comp_notif_usages hacnu
1675 where
1676 hac.approval_comp_id=p_approval_comp_id and
1677 hacnu.approval_comp_id=hac.approval_comp_id;
1678
1679 begin
1680
1681 if (p_dml_action = 'INSERT')
1682 then
1683 open csr_chk_comps_usages(p_approval_comp_id);
1684 fetch csr_chk_comps_usages into l_count;
1685 close csr_chk_comps_usages;
1686 if (l_count>0)
1687 then
1688 open csr_approval_comp(p_approval_style_id);
1689 fetch csr_approval_comp into l_existing_approval_comp_id;
1690 close csr_approval_comp;
1691 open csr_comp_notification(l_existing_approval_comp_id);
1692 loop
1693 fetch csr_comp_notification into l_comp_notification_id,l_comp_notification_ovn,l_enabled_flag;
1694 exit when csr_comp_notification%notfound;
1695 insert into
1696 hxc_app_comp_notif_usages
1697 (
1698 approval_comp_id
1699 ,approval_comp_ovn
1700 ,comp_notification_id
1701 ,comp_notification_ovn
1702 ,enabled_flag
1703 )
1704 values
1705 (
1706 p_approval_comp_id
1707 ,p_approval_comp_ovn
1708 ,l_comp_notification_id
1709 ,l_comp_notification_ovn
1710 ,l_enabled_flag
1711 );
1712 end loop;
1713 close csr_comp_notification;
1714 end if;
1715
1716 elsif (p_dml_action = 'DELETE')
1717 then
1718
1719 --For deleting rows corresponding to ELA
1720 delete from
1721 hxc_app_comp_notif_usages
1722 where
1723 approval_comp_id in (select approval_comp_id from
1724 hxc_approval_comps where
1725 PARENT_COMP_ID=p_approval_comp_id and
1726 PARENT_COMP_OVN=p_approval_comp_ovn);
1727
1728 delete from
1729 hxc_app_comp_notif_usages
1730 where
1731 approval_comp_id = p_approval_comp_id and
1732 approval_comp_ovn = p_approval_comp_ovn;
1733 end if;
1734 end approval_comps_usages;
1735
1736 end hxc_notification_helper;