1 PACKAGE BODY ben_person_delete AS
2 /* $Header: bepedchk.pkb 120.1.12010000.3 2008/08/29 11:04:03 pvelvano ship $ */
3 g_package varchar2(50) := 'ben_person_delete.';
4 PROCEDURE perform_ri_check (
5 p_person_id IN NUMBER
6 ) IS
7 CURSOR c1 (
8 p_person_id NUMBER
9 ) IS
10 SELECT 1
11 FROM ben_batch_actn_item_info
12 WHERE person_id = p_person_id;
13 CURSOR c2 (
14 p_person_id NUMBER
15 ) IS
16 SELECT 1
17 FROM ben_batch_bnft_cert_info
18 WHERE person_id = p_person_id;
19 CURSOR c20 (
20 p_person_id NUMBER
21 ) IS
22 SELECT 1
23 FROM ben_batch_commu_info
24 WHERE person_id = p_person_id;
25 CURSOR c21 (
26 p_person_id NUMBER
27 ) IS
28 SELECT 1
29 FROM ben_batch_dpnt_info
30 WHERE person_id = p_person_id;
31 CURSOR c22 (
32 p_person_id NUMBER
33 ) IS
34 SELECT 1
35 FROM ben_batch_elctbl_chc_info
36 WHERE person_id = p_person_id;
37 CURSOR c23 (
38 p_person_id NUMBER
39 ) IS
40 SELECT 1
41 FROM ben_batch_elig_info
42 WHERE person_id = p_person_id;
43 CURSOR c24 (
44 p_person_id NUMBER
45 ) IS
46 SELECT 1
47 FROM ben_batch_ler_info
48 WHERE person_id = p_person_id;
49 CURSOR c25 (
50 p_person_id NUMBER
51 ) IS
52 SELECT 1
53 FROM ben_batch_rate_info
54 WHERE person_id = p_person_id;
55 CURSOR c26 (
56 p_person_id NUMBER
57 ) IS
58 SELECT 1
59 FROM ben_benefit_actions
60 WHERE person_id = p_person_id;
61 CURSOR c15 (
62 p_person_id NUMBER
63 ) IS
64 SELECT 1
65 FROM ben_crt_ordr
66 WHERE person_id = p_person_id;
67 CURSOR c16 (
68 p_person_id NUMBER
69 ) IS
70 SELECT 1
71 FROM ben_crt_ordr_cvrd_per
72 WHERE person_id = p_person_id;
73 CURSOR c17 (
74 p_person_id NUMBER
75 ) IS
76 SELECT 1
77 FROM ben_elig_per_f
78 WHERE person_id = p_person_id;
79 CURSOR c3 (
80 p_person_id NUMBER
81 ) IS
82 SELECT 1
83 FROM ben_ext_chg_evt_log
84 WHERE person_id = p_person_id;
85 CURSOR c6 (
86 p_person_id NUMBER
87 ) IS
88 SELECT 1
89 FROM ben_ext_rslt_dtl
90 WHERE person_id = p_person_id;
91 CURSOR c7 (
92 p_person_id NUMBER
93 ) IS
94 SELECT 1
95 FROM ben_ext_rslt_err
96 WHERE person_id = p_person_id;
97 CURSOR c11 (
98 p_person_id NUMBER
99 ) IS
100 SELECT 1
101 FROM ben_le_clsn_n_rstr lct,
102 ben_per_in_ler pil
103 WHERE pil.person_id = p_person_id
104 AND pil.per_in_ler_id = lct.per_in_ler_id; /* Bug 4882374 : Perf */
105 CURSOR c27 (
106 p_person_id NUMBER
107 ) IS
108 SELECT 1
109 FROM ben_person_actions
110 WHERE person_id = p_person_id;
111 CURSOR c4 (
112 p_person_id NUMBER
113 ) IS
114 SELECT 1
115 FROM ben_per_bnfts_bal_f
116 WHERE person_id = p_person_id;
117 CURSOR c19 (
118 p_person_id NUMBER
119 ) IS
120 SELECT 1
121 FROM ben_per_cm_f
122 WHERE person_id = p_person_id;
123 CURSOR c8 (
124 p_person_id NUMBER
125 ) IS
126 SELECT 1
127 FROM ben_per_dlvry_mthd_f
128 WHERE person_id = p_person_id;
129 CURSOR c12 (
130 p_person_id NUMBER
131 ) IS
132 SELECT 1
133 FROM ben_per_in_ler
134 WHERE person_id = p_person_id;
135 CURSOR c9 (
136 p_person_id NUMBER
137 ) IS
138 SELECT 1
139 FROM ben_per_in_lgl_enty_f
140 WHERE person_id = p_person_id;
141 CURSOR c10 (
142 p_person_id NUMBER
143 ) IS
144 SELECT 1
145 FROM ben_per_in_org_unit_f
146 WHERE person_id = p_person_id;
147 CURSOR c5 (
148 p_person_id NUMBER
149 ) IS
150 SELECT 1
151 FROM ben_per_pin_f
152 WHERE person_id = p_person_id;
153 CURSOR c14 (
154 p_person_id NUMBER
155 ) IS
156 SELECT 1
157 FROM ben_popl_org_f
158 WHERE person_id = p_person_id;
159 CURSOR c18 (
160 p_person_id NUMBER
161 ) IS
162 SELECT 1
163 FROM ben_prtt_enrt_rslt_f
164 WHERE person_id = p_person_id;
165 CURSOR c13 (
166 p_person_id NUMBER
167 ) IS
168 SELECT 1
169 FROM ben_ptnl_ler_for_per
170 WHERE person_id = p_person_id;
171 CURSOR c28 (
172 p_person_id NUMBER
173 ) IS
174 SELECT 1
175 FROM ben_reporting rep,
176 ben_person_actions pat
177 WHERE pat.person_id = p_person_id
178 AND rep.benefit_action_id = pat.benefit_action_id; /* Bug 4882374 : Perf */
179 CURSOR c29 (
180 p_person_id NUMBER
181 ) IS
182 SELECT 1
183 FROM ben_elig_cvrd_dpnt_f
184 WHERE dpnt_person_id = p_person_id;
185 CURSOR c30 (
186 p_person_id NUMBER
187 ) IS
188 SELECT 1
189 FROM ben_prtt_reimbmt_rqst_f
190 WHERE submitter_person_id = p_person_id;
191 CURSOR c31 (
192 p_person_id NUMBER
193 ) IS
194 SELECT 1
195 FROM ben_prtt_reimbmt_rqst_f
196 WHERE recipient_person_id = p_person_id;
197 CURSOR c32 (
198 p_person_id NUMBER
199 ) IS
200 SELECT 1
201 FROM ben_prtt_reimbmt_rqst_f
202 WHERE provider_person_id = p_person_id;
203 CURSOR c33 (
204 p_person_id NUMBER
205 ) IS
206 SELECT 1
207 FROM ben_prtt_reimbmt_rqst_f
208 WHERE provider_ssn_person_id = p_person_id;
209 CURSOR c34 (
210 p_person_id NUMBER
211 ) IS
212 SELECT 1
213 FROM ben_cbr_quald_bnf
214 WHERE cvrd_emp_person_id = p_person_id;
215 -- 3511450
216 CURSOR c35 (
217 p_person_id NUMBER
218 ) IS
219 SELECT 1
220 FROM ben_pl_bnf_f
221 WHERE bnf_person_id = p_person_id;
222
223 l_temp VARCHAR2 (2);
224 BEGIN
225
226 --
227 -- Testing for values in BEN_BATCH_ACTN_ITEM_INFO
228 --
229 OPEN c1 (
230 p_person_id
231 );
232
233 --
234 FETCH c1 INTO l_temp;
235 IF c1%FOUND THEN
236 CLOSE c1;
237 fnd_message.set_name (
238 'BEN',
239 'BEN_94121_DT_CHILD_EXISTS'
240 );
241 fnd_message.set_token (
242 'TABLE_NAME',
243 'BEN_BATCH_ACTN_ITEM_INFO'
244 );
245 fnd_message.raise_error;
246 END IF;
247
248 --
249 CLOSE c1;
250
251 --
252 -- Testing for values in BEN_BATCH_BNFT_CERT_INFO
253 --
254 OPEN c2 (
255 p_person_id
256 );
257
258 --
259 FETCH c2 INTO l_temp;
260 IF c2%FOUND THEN
261 CLOSE c2;
262 fnd_message.set_name (
263 'BEN',
264 'BEN_94121_DT_CHILD_EXISTS'
265 );
266 fnd_message.set_token (
267 'TABLE_NAME',
268 'BEN_BATCH_BNFT_CERT_INFO'
269 );
270 fnd_message.raise_error;
271 END IF;
272
273 --
274 CLOSE c2;
275
276 --
277 -- Testing for values in BEN_BATCH_COMMU_INFO
278 --
279 OPEN c20 (
280 p_person_id
281 );
282
283 --
284 FETCH c20 INTO l_temp;
285 IF c20%FOUND THEN
286 CLOSE c20;
287 fnd_message.set_name (
288 'BEN',
289 'BEN_94121_DT_CHILD_EXISTS'
290 );
291 fnd_message.set_token (
292 'TABLE_NAME',
293 'BEN_BATCH_COMMU_INFO'
294 );
295 fnd_message.raise_error;
296 END IF;
297
298 --
299 CLOSE c20;
300
301 --
302 -- Testing for values in BEN_BATCH_DPNT_INFO
303 --
304 OPEN c21 (
305 p_person_id
306 );
307
308 --
309 FETCH c21 INTO l_temp;
310 IF c21%FOUND THEN
311 CLOSE c21;
312 fnd_message.set_name (
313 'BEN',
314 'BEN_94121_DT_CHILD_EXISTS'
315 );
316 fnd_message.set_token (
317 'TABLE_NAME',
318 'BEN_BATCH_DPNT_INFO'
319 );
320 fnd_message.raise_error;
321 END IF;
322
323 --
324 CLOSE c21;
325
326 --
327 -- Testing for values in BEN_BATCH_ELCTBL_CHC_INFO
328 --
329 OPEN c22 (
330 p_person_id
331 );
332
333 --
334 FETCH c22 INTO l_temp;
335 IF c22%FOUND THEN
336 CLOSE c22;
337 fnd_message.set_name (
338 'BEN',
339 'BEN_94121_DT_CHILD_EXISTS'
340 );
341 fnd_message.set_token (
342 'TABLE_NAME',
343 'BEN_BATCH_ELCTBL_CHC_INFO'
344 );
345 fnd_message.raise_error;
346 END IF;
347
348 --
349 CLOSE c22;
350
351 --
352 -- Testing for values in BEN_BATCH_ELIG_INFO
353 --
354 OPEN c23 (
355 p_person_id
356 );
357
358 --
359 FETCH c23 INTO l_temp;
360 IF c23%FOUND THEN
361 CLOSE c23;
362 fnd_message.set_name (
363 'BEN',
364 'BEN_94121_DT_CHILD_EXISTS'
365 );
366 fnd_message.set_token (
367 'TABLE_NAME',
368 'BEN_BATCH_ELIG_INFO'
369 );
370 fnd_message.raise_error;
371 END IF;
372
373 --
374 CLOSE c23;
375
376 --
377 -- Testing for values in BEN_BATCH_LER_INFO
378 --
379 OPEN c24 (
380 p_person_id
381 );
382
383 --
384 FETCH c24 INTO l_temp;
385 IF c24%FOUND THEN
386 CLOSE c24;
387 fnd_message.set_name (
388 'BEN',
389 'BEN_94121_DT_CHILD_EXISTS'
390 );
391 fnd_message.set_token (
392 'TABLE_NAME',
393 'BEN_BATCH_LER_INFO'
394 );
395 fnd_message.raise_error;
396 END IF;
397
398 --
399 CLOSE c24;
400
401 --
402 -- Testing for values in BEN_BATCH_RATE_INFO
403 --
404 OPEN c25 (
405 p_person_id
406 );
407
408 --
409 FETCH c25 INTO l_temp;
410 IF c25%FOUND THEN
411 CLOSE c25;
412 fnd_message.set_name (
413 'BEN',
414 'BEN_94121_DT_CHILD_EXISTS'
415 );
416 fnd_message.set_token (
417 'TABLE_NAME',
418 'BEN_BATCH_RATE_INFO'
419 );
420 fnd_message.raise_error;
421 END IF;
422
423 --
424 CLOSE c25;
425
426 --
427 -- Testing for values in BEN_BENEFIT_ACTIONS
428 --
429 OPEN c26 (
430 p_person_id
431 );
432
433 --
434 FETCH c26 INTO l_temp;
435 IF c26%FOUND THEN
436 CLOSE c26;
437 fnd_message.set_name (
438 'BEN',
439 'BEN_94121_DT_CHILD_EXISTS'
440 );
441 fnd_message.set_token (
442 'TABLE_NAME',
443 'BEN_BENEFIT_ACTIONS'
444 );
445 fnd_message.raise_error;
446 END IF;
447
448 --
449 CLOSE c26;
450
451 --
452 -- Testing for values in BEN_CRT_ORDR
453 --
454 OPEN c15 (
455 p_person_id
456 );
457
458 --
459 FETCH c15 INTO l_temp;
460 IF c15%FOUND THEN
461 CLOSE c15;
462 fnd_message.set_name (
463 'BEN',
464 'BEN_94121_DT_CHILD_EXISTS'
465 );
466 fnd_message.set_token (
467 'TABLE_NAME',
468 'BEN_CRT_ORDR'
469 );
470 fnd_message.raise_error;
471 END IF;
472
473 --
474 CLOSE c15;
475
476 --
477 -- Testing for values in BEN_CRT_ORDR_CVRD_PER
478 --
479 OPEN c16 (
480 p_person_id
481 );
482
483 --
484 FETCH c16 INTO l_temp;
485 IF c16%FOUND THEN
486 CLOSE c16;
490 );
487 fnd_message.set_name (
488 'BEN',
489 'BEN_94121_DT_CHILD_EXISTS'
491 fnd_message.set_token (
492 'TABLE_NAME',
493 'BEN_CRT_ORDR_CVRD_PER'
494 );
495 fnd_message.raise_error;
496 END IF;
497
498 --
499 CLOSE c16;
500
501 --
502 -- Testing for values in BEN_ELIG_PER_F
503 --
504 OPEN c17 (
505 p_person_id
506 );
507
508 --
509 FETCH c17 INTO l_temp;
510 IF c17%FOUND THEN
511 CLOSE c17;
512 fnd_message.set_name (
513 'BEN',
514 'BEN_94121_DT_CHILD_EXISTS'
515 );
516 fnd_message.set_token (
517 'TABLE_NAME',
518 'BEN_ELIG_PER_F'
519 );
520 fnd_message.raise_error;
521 END IF;
522
523 --
524 CLOSE c17;
525
526 --
527 -- Testing for values in BEN_EXT_CHG_EVT_LOG
528 --
529 OPEN c3 (
530 p_person_id
531 );
532
533 --
534 FETCH c3 INTO l_temp;
535 IF c3%FOUND THEN
536 CLOSE c3;
537 fnd_message.set_name (
538 'BEN',
539 'BEN_94121_DT_CHILD_EXISTS'
540 );
541 fnd_message.set_token (
542 'TABLE_NAME',
543 'BEN_EXT_CHG_EVT_LOG'
544 );
545 fnd_message.raise_error;
546 END IF;
547
548 --
549 CLOSE c3;
550
551 --
552 -- Testing for values in BEN_EXT_RSLT_DTL
553 --
554 OPEN c6 (
555 p_person_id
556 );
557
558 --
559 FETCH c6 INTO l_temp;
560 IF c6%FOUND THEN
561 CLOSE c6;
562 fnd_message.set_name (
563 'BEN',
564 'BEN_94121_DT_CHILD_EXISTS'
565 );
566 fnd_message.set_token (
567 'TABLE_NAME',
568 'BEN_EXT_RSLT_DTL'
569 );
570 fnd_message.raise_error;
571 END IF;
572
573 --
574 CLOSE c6;
575
576 --
577 -- Testing for values in BEN_EXT_RSLT_ERR
578 --
579 OPEN c7 (
580 p_person_id
581 );
582
583 --
584 FETCH c7 INTO l_temp;
585 IF c7%FOUND THEN
586 CLOSE c7;
587 fnd_message.set_name (
588 'BEN',
589 'BEN_94121_DT_CHILD_EXISTS'
590 );
591 fnd_message.set_token (
592 'TABLE_NAME',
593 'BEN_EXT_RSLT_ERR'
594 );
595 fnd_message.raise_error;
596 END IF;
597
598 --
599 CLOSE c7;
600
601 --
602 -- Testing for values in BEN_LE_CLSN_N_RSTR
603 --
604 OPEN c11 (
605 p_person_id
606 );
607
608 --
609 FETCH c11 INTO l_temp;
610 IF c11%FOUND THEN
611 CLOSE c11;
612 fnd_message.set_name (
613 'BEN',
614 'BEN_94121_DT_CHILD_EXISTS'
615 );
616 fnd_message.set_token (
617 'TABLE_NAME',
618 'BEN_LE_CLSN_N_RSTR'
619 );
620 fnd_message.raise_error;
621 END IF;
622
623 --
624 CLOSE c11;
625
626 --
627 -- Testing for values in BEN_PERSON_ACTIONS
628 --
629 OPEN c27 (
630 p_person_id
631 );
632
633 --
634 FETCH c27 INTO l_temp;
635 IF c27%FOUND THEN
636 CLOSE c27;
637 fnd_message.set_name (
638 'BEN',
639 'BEN_94121_DT_CHILD_EXISTS'
640 );
641 fnd_message.set_token (
642 'TABLE_NAME',
643 'BEN_PERSON_ACTIONS'
644 );
645 fnd_message.raise_error;
646 END IF;
647
648 --
649 CLOSE c27;
650
651 --
652 -- Testing for values in BEN_PER_BNFTS_BAL_F
653 --
654 OPEN c4 (
655 p_person_id
656 );
657
658 --
659 FETCH c4 INTO l_temp;
660 IF c4%FOUND THEN
661 CLOSE c4;
662 fnd_message.set_name (
663 'BEN',
664 'BEN_94121_DT_CHILD_EXISTS'
665 );
666 fnd_message.set_token (
667 'TABLE_NAME',
668 'BEN_PER_BNFTS_BAL_F'
669 );
670 fnd_message.raise_error;
671 END IF;
672
673 --
674 CLOSE c4;
675
676 --
677 -- Testing for values in BEN_PER_CM_F
678 --
679 OPEN c19 (
680 p_person_id
681 );
682
683 --
684 FETCH c19 INTO l_temp;
685 IF c19%FOUND THEN
686 CLOSE c19;
687 fnd_message.set_name (
691 fnd_message.set_token (
688 'BEN',
689 'BEN_94121_DT_CHILD_EXISTS'
690 );
692 'TABLE_NAME',
693 'BEN_PER_CM_F'
694 );
695 fnd_message.raise_error;
696 END IF;
697
698 --
699 CLOSE c19;
700
701 --
702 -- Testing for values in BEN_PER_DLVRY_MTHD_F
703 --
704 OPEN c8 (
705 p_person_id
706 );
707
708 --
709 FETCH c8 INTO l_temp;
710 IF c8%FOUND THEN
711 CLOSE c8;
712 fnd_message.set_name (
713 'BEN',
714 'BEN_94121_DT_CHILD_EXISTS'
715 );
716 fnd_message.set_token (
717 'TABLE_NAME',
718 'BEN_PER_DLVRY_MTHD_F'
719 );
720 fnd_message.raise_error;
721 END IF;
722
723 --
724 CLOSE c8;
725
726 --
727 -- Testing for values in BEN_PER_IN_LER
728 --
729 OPEN c12 (
730 p_person_id
731 );
732
733 --
734 FETCH c12 INTO l_temp;
735 IF c12%FOUND THEN
736 CLOSE c12;
737 fnd_message.set_name (
738 'BEN',
739 'BEN_94121_DT_CHILD_EXISTS'
740 );
741 fnd_message.set_token (
742 'TABLE_NAME',
743 'BEN_PER_IN_LER'
744 );
745 fnd_message.raise_error;
746 END IF;
747
748 --
749 CLOSE c12;
750
751 --
752 -- Testing for values in BEN_PER_IN_LGL_ENTY_F
753 --
754 OPEN c9 (
755 p_person_id
756 );
757
758 --
759 FETCH c9 INTO l_temp;
760 IF c9%FOUND THEN
761 CLOSE c9;
762 fnd_message.set_name (
763 'BEN',
764 'BEN_94121_DT_CHILD_EXISTS'
765 );
766 fnd_message.set_token (
767 'TABLE_NAME',
768 'BEN_PER_IN_LGL_ENTY_F'
769 );
770 fnd_message.raise_error;
771 END IF;
772
773 --
774 CLOSE c9;
775
776 --
777 -- Testing for values in BEN_PER_IN_ORG_UNIT_F
778 --
779 OPEN c10 (
780 p_person_id
781 );
782
783 --
784 FETCH c10 INTO l_temp;
785 IF c10%FOUND THEN
786 CLOSE c10;
787 fnd_message.set_name (
788 'BEN',
789 'BEN_94121_DT_CHILD_EXISTS'
790 );
791 fnd_message.set_token (
792 'TABLE_NAME',
793 'BEN_PER_IN_ORG_UNIT_F'
794 );
795 fnd_message.raise_error;
796 END IF;
797
798 --
799 CLOSE c10;
800
801 --
802 -- Testing for values in BEN_PER_PIN_F
803 --
804 OPEN c5 (
805 p_person_id
806 );
807
808 --
809 FETCH c5 INTO l_temp;
810 IF c5%FOUND THEN
811 CLOSE c5;
812 fnd_message.set_name (
813 'BEN',
814 'BEN_94121_DT_CHILD_EXISTS'
815 );
816 fnd_message.set_token (
817 'TABLE_NAME',
818 'BEN_PER_PIN_F'
819 );
820 fnd_message.raise_error;
821 END IF;
822
823 --
824 CLOSE c5;
825
826 --
827 -- Testing for values in BEN_POPL_ORG_F
828 --
829 OPEN c14 (
830 p_person_id
831 );
832
833 --
834 FETCH c14 INTO l_temp;
835 IF c14%FOUND THEN
836 CLOSE c14;
837 fnd_message.set_name (
838 'BEN',
839 'BEN_94121_DT_CHILD_EXISTS'
840 );
841 fnd_message.set_token (
842 'TABLE_NAME',
843 'BEN_POPL_ORG_F'
844 );
845 fnd_message.raise_error;
846 END IF;
847
848 --
849 CLOSE c14;
850
851 --
852 -- Testing for values in BEN_PRTT_ENRT_RSLT_F
853 --
854 OPEN c18 (
855 p_person_id
856 );
857
858 --
859 FETCH c18 INTO l_temp;
860 IF c18%FOUND THEN
861 CLOSE c18;
862 fnd_message.set_name (
863 'BEN',
864 'BEN_94121_DT_CHILD_EXISTS'
865 );
866 fnd_message.set_token (
867 'TABLE_NAME',
868 'BEN_PRTT_ENRT_RSLT_F'
869 );
870 fnd_message.raise_error;
871 END IF;
872
873 --
874 CLOSE c18;
875
876 --
877 -- Testing for values in BEN_PTNL_LER_FOR_PER
878 --
879 OPEN c13 (
880 p_person_id
881 );
882
883 --
884 FETCH c13 INTO l_temp;
885 IF c13%FOUND THEN
886 CLOSE c13;
887 fnd_message.set_name (
888 'BEN',
889 'BEN_94121_DT_CHILD_EXISTS'
890 );
894 );
891 fnd_message.set_token (
892 'TABLE_NAME',
893 'BEN_PTNL_LER_FOR_PER'
895 fnd_message.raise_error;
896 END IF;
897
898 --
899 CLOSE c13;
900
901 --
902 -- Testing for values in BEN_REPORTING
903 --
904 OPEN c28 (
905 p_person_id
906 );
907
908 --
909 FETCH c28 INTO l_temp;
910 IF c28%FOUND THEN
911 CLOSE c28;
912 fnd_message.set_name (
913 'BEN',
914 'BEN_94121_DT_CHILD_EXISTS'
915 );
916 fnd_message.set_token (
917 'TABLE_NAME',
918 'BEN_REPORTING'
919 );
920 fnd_message.raise_error;
921 END IF;
922
923 --
924 CLOSE c28;
925 --
926 -- Testing for values in BEN_ELIG_CVRD_DPNT_F
927 --
928 OPEN c29 (
929 p_person_id
930 );
931
932 --
933 FETCH c29 INTO l_temp;
934 IF c29%FOUND THEN
935 CLOSE c29;
936 fnd_message.set_name (
937 'BEN',
938 'BEN_94121_DT_CHILD_EXISTS'
939 );
940 fnd_message.set_token (
941 'TABLE_NAME',
942 'BEN_ELIG_CVRD_DPNT_F'
943 );
944 fnd_message.raise_error;
945 END IF;
946
947 --
948 CLOSE c29;
949 --
950 -- Testing for values in BEN_PRTT_REIBMT_RQST_F
951 --
952 OPEN c30 (
953 p_person_id
954 );
955
956 --
957 FETCH c30 INTO l_temp;
958 IF c30%FOUND THEN
959 CLOSE c30;
960 fnd_message.set_name (
961 'BEN',
962 'BEN_94121_DT_CHILD_EXISTS'
963 );
964 fnd_message.set_token (
965 'TABLE_NAME',
966 'BEN_PRTT_REIBMT_RQST_F'
967 );
968 fnd_message.raise_error;
969 END IF;
970
971 --
972 CLOSE c30;
973 --
974 -- Testing for values in BEN_PRTT_REIBMT_RQST_F
975 --
976 OPEN c31 (
977 p_person_id
978 );
979
980 --
981 FETCH c31 INTO l_temp;
982 IF c31%FOUND THEN
983 CLOSE c31;
984 fnd_message.set_name (
985 'BEN',
986 'BEN_94121_DT_CHILD_EXISTS'
987 );
988 fnd_message.set_token (
989 'TABLE_NAME',
990 'BEN_PRTT_REIBMT_RQST_F'
991 );
992 fnd_message.raise_error;
993 END IF;
994
995 --
996 CLOSE c31;
997 --
998 -- Testing for values in BEN_PRTT_REIBMT_RQST_F
999 --
1000 OPEN c32 (
1001 p_person_id
1002 );
1003
1004 --
1005 FETCH c32 INTO l_temp;
1006 IF c32%FOUND THEN
1007 CLOSE c32;
1008 fnd_message.set_name (
1009 'BEN',
1010 'BEN_94121_DT_CHILD_EXISTS'
1011 );
1012 fnd_message.set_token (
1013 'TABLE_NAME',
1014 'BEN_PRTT_REIBMT_RQST_F'
1015 );
1016 fnd_message.raise_error;
1017 END IF;
1018
1019 --
1020 CLOSE c32;
1021 --
1022 -- Testing for values in BEN_PRTT_REIBMT_RQST_F
1023 --
1024 OPEN c33 (
1025 p_person_id
1026 );
1027
1028 --
1029 FETCH c33 INTO l_temp;
1030 IF c33%FOUND THEN
1031 CLOSE c33;
1032 fnd_message.set_name (
1033 'BEN',
1034 'BEN_94121_DT_CHILD_EXISTS'
1035 );
1036 fnd_message.set_token (
1037 'TABLE_NAME',
1038 'BEN_PRTT_REIBMT_RQST_F'
1039 );
1040 fnd_message.raise_error;
1041 END IF;
1042
1043 --
1044 CLOSE c33;
1045 --
1046 -- Testing for values in BEN_CBR_QUALD_BNF
1047 --
1048 OPEN c34 (
1049 p_person_id
1050 );
1051
1052 --
1053 FETCH c34 INTO l_temp;
1054 IF c34%FOUND THEN
1055 CLOSE c34;
1056 fnd_message.set_name (
1057 'BEN',
1058 'BEN_94121_DT_CHILD_EXISTS'
1059 );
1060 fnd_message.set_token (
1061 'TABLE_NAME',
1062 'BEN_CBR_QUALD_BNF'
1063 );
1064 fnd_message.raise_error;
1065 END IF;
1066
1067 --
1068 CLOSE c34;
1069 --
1070 --3511450 start
1071
1072 OPEN c35 (
1073 p_person_id
1074 );
1075 FETCH c35 INTO l_temp;
1076 IF c35%FOUND THEN
1077 CLOSE c35;
1078 fnd_message.set_name (
1079 'BEN',
1080 'BEN_93911_DPT_DESIG_BNF'
1081 );
1082 fnd_message.raise_error;
1083 END IF;
1084 --
1085 CLOSE c35;
1086 -- 3511450 end
1087
1091 PROCEDURE delete_dependent_information (
1088 END perform_ri_check;
1089
1090 --
1092 p_person_id IN NUMBER
1093 ) IS
1094
1095 l_proc varchar2(100):= g_package||'delete_dependent_information';
1096 --
1097 CURSOR c_ecd IS
1098 SELECT elig_cvrd_dpnt_id, dpnt_person_id
1099 FROM ben_elig_cvrd_dpnt_f
1100 WHERE dpnt_person_id = p_person_id
1101 FOR UPDATE OF elig_cvrd_dpnt_id,dpnt_person_id;
1102
1103 --
1104 CURSOR c_crt_ordr_cvrd (
1105 p_dpnt_person_id IN NUMBER
1106 ) IS
1107 SELECT crt_ordr_cvrd_per_id
1108 FROM ben_crt_ordr_cvrd_per
1109 WHERE crt_ordr_id IN (SELECT crt_ordr_id
1110 FROM ben_crt_ordr
1111 WHERE person_id = p_dpnt_person_id)
1112 FOR UPDATE OF crt_ordr_id;
1113
1114 --
1115 CURSOR c_cvrd_dpnt (
1116 p_elig_cvrd_dpnt_id IN NUMBER
1117 ) IS
1118 SELECT cvrd_dpnt_ctfn_prvdd_id
1119 FROM ben_cvrd_dpnt_ctfn_prvdd_f
1120 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1121 FOR UPDATE OF elig_cvrd_dpnt_id;
1122 CURSOR c1 (
1123 p_elig_cvrd_dpnt_id NUMBER
1124 ) IS
1125 SELECT elig_cvrd_dpnt_id
1126 FROM ben_prtt_enrt_actn_f
1127 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1128 FOR UPDATE OF elig_cvrd_dpnt_id;
1129
1130 --
1131 CURSOR c2 (
1132 p_elig_cvrd_dpnt_id NUMBER
1133 ) IS
1134 SELECT elig_cvrd_dpnt_id
1135 FROM ben_prmry_care_prvdr_f
1136 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1137 FOR UPDATE OF elig_cvrd_dpnt_id;
1138
1139 --
1140 CURSOR c3 (
1141 p_elig_cvrd_dpnt_id NUMBER
1142 ) IS
1143 SELECT elig_cvrd_dpnt_id
1144 FROM ben_cvrd_dpnt_ctfn_prvdd_f
1145 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1146 FOR UPDATE OF elig_cvrd_dpnt_id;
1147
1148 --
1149 CURSOR c4 (
1150 p_elig_cvrd_dpnt_id NUMBER
1151 ) IS
1152 SELECT elig_cvrd_dpnt_id
1153 FROM ben_elig_cvrd_dpnt_f
1154 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1155 FOR UPDATE OF elig_cvrd_dpnt_id;
1156
1157 --
1158 CURSOR c5 (
1159 p_elig_cvrd_dpnt_id NUMBER
1160 ) IS
1161 SELECT elig_cvrd_dpnt_id
1162 FROM ben_elig_dpnt
1163 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1164 FOR UPDATE OF elig_cvrd_dpnt_id;
1165
1166 --
1167 CURSOR c6 (
1168 p_elig_cvrd_dpnt_id NUMBER
1169 ) IS
1170 SELECT ext_crit_val_id
1171 FROM ben_ext_crit_val
1172 WHERE ext_crit_val_id IN
1173 (SELECT DISTINCT ext_crit_val_id
1174 FROM ben_ext_crit_val val,
1175 ben_ext_crit_typ typ
1176 WHERE typ.crit_typ_cd = 'PID'
1177 AND val.ext_crit_typ_id =
1178 typ.ext_crit_typ_id
1179 AND val.val_1 =
1180 TO_CHAR (
1181 p_elig_cvrd_dpnt_id
1182 ))
1183 FOR UPDATE OF ext_crit_val_id;
1184 CURSOR c7 (
1185 p_elig_cvrd_dpnt_id NUMBER
1186 ) IS
1187 SELECT elig_cvrd_dpnt_id
1188 FROM ben_elig_dpnt
1189 WHERE dpnt_person_id = p_elig_cvrd_dpnt_id
1190 FOR UPDATE OF elig_cvrd_dpnt_id;
1191
1192 --
1193 l_id NUMBER;
1194 BEGIN
1195 hr_utility.set_location('Entering: '||l_proc,10);
1196 FOR l_ecd IN c_ecd LOOP
1197 FOR l_crt_ordr_cvrd IN c_crt_ordr_cvrd (
1198 l_ecd.dpnt_person_id
1199 ) LOOP
1200 DELETE FROM ben_crt_ordr_cvrd_per
1201 WHERE CURRENT OF c_crt_ordr_cvrd;
1202 END LOOP;
1203 hr_utility.set_location(l_proc, 15);
1204 FOR l_cvrd_dpnt IN c_cvrd_dpnt (
1205 l_ecd.elig_cvrd_dpnt_id
1206 ) LOOP
1207 DELETE ben_cvrd_dpnt_ctfn_prvdd_f
1208 WHERE CURRENT OF c_cvrd_dpnt;
1209 END LOOP;
1210 hr_utility.set_location(l_proc, 20);
1211 OPEN c1 (
1212 l_ecd.elig_cvrd_dpnt_id
1213 );
1214 <<ben_prtt_enrt_actn_f>>
1215 LOOP
1216 FETCH c1 INTO l_id;
1217 EXIT WHEN c1%NOTFOUND;
1218 DELETE ben_prtt_enrt_actn_f
1219 WHERE CURRENT OF c1;
1220 END LOOP ben_prtt_enrt_actn_f;
1221 CLOSE c1;
1225 );
1222 hr_utility.set_location(l_proc, 25);
1223 OPEN c2 (
1224 l_ecd.elig_cvrd_dpnt_id
1226 <<ben_prmry_care_prvdr_f>>
1227 LOOP
1228 FETCH c2 INTO l_id;
1229 EXIT WHEN c2%NOTFOUND;
1230 DELETE ben_prmry_care_prvdr_f
1231 WHERE CURRENT OF c2;
1232 END LOOP ben_prmry_care_prvdr_f;
1233 CLOSE c2;
1234 hr_utility.set_location(l_proc, 30);
1235 OPEN c3 (
1236 l_ecd.elig_cvrd_dpnt_id
1237 );
1238 <<ben_cvrd_dpnt_ctfn_prvdd_f>>
1239 LOOP
1240 FETCH c3 INTO l_id;
1241 EXIT WHEN c3%NOTFOUND;
1242 DELETE ben_cvrd_dpnt_ctfn_prvdd_f
1243 WHERE CURRENT OF c3;
1244 END LOOP ben_cvrd_dpnt_ctfn_prvdd_f;
1245 CLOSE c3;
1246 hr_utility.set_location(l_proc, 35);
1247 /* OPEN c4 (
1248 l_ecd.elig_cvrd_dpnt_id
1249 );
1250 <<ben_elig_cvrd_dpnt_f>>
1251 LOOP
1252 FETCH c4 INTO l_id;
1253 EXIT WHEN c4%NOTFOUND;
1254 DELETE ben_elig_cvrd_dpnt_f
1255 WHERE CURRENT OF c4;
1256 END LOOP ben_elig_cvrd_dpnt_f;
1257 CLOSE c4; */
1258 hr_utility.set_location(l_proc, 40);
1259 OPEN c5 (
1260 l_ecd.elig_cvrd_dpnt_id
1261 );
1262 <<ben_elig_dpnt>>
1263 LOOP
1264 FETCH c5 INTO l_id;
1265 EXIT WHEN c5%NOTFOUND;
1266 DELETE ben_elig_dpnt
1267 WHERE CURRENT OF c5;
1268 END LOOP ben_elig_dpnt;
1269 CLOSE c5;
1270 hr_utility.set_location(l_proc, 45);
1271 OPEN c6 (
1272 l_ecd.elig_cvrd_dpnt_id
1273 );
1274 <<ben_ext_crit_val>>
1275 LOOP
1276 FETCH c6 INTO l_id;
1277 EXIT WHEN c6%NOTFOUND;
1278 DELETE FROM ben_ext_crit_val
1279 WHERE CURRENT OF c6;
1280 END LOOP ben_ext_crit_val;
1281 CLOSE c6;
1282 hr_utility.set_location(l_proc, 50);
1283 OPEN c7 (
1284 l_ecd.elig_cvrd_dpnt_id
1285 );
1286 <<ben_elig_dpnt>>
1287 LOOP
1288 FETCH c7 INTO l_id;
1289 EXIT WHEN c7%NOTFOUND;
1290 DELETE ben_elig_dpnt
1291 WHERE CURRENT OF c7;
1292 END LOOP ben_elig_dpnt;
1293 CLOSE c7;
1294 DELETE ben_elig_cvrd_dpnt_f
1295 WHERE CURRENT OF c_ecd;
1296 END LOOP;
1297 hr_utility.set_location('Leaving: '||l_proc,999);
1298 END delete_dependent_information;
1299
1300 --
1301 PROCEDURE delete_communications (
1302 p_person_id IN NUMBER
1303 ) IS
1304 l_proc varchar2(100):= g_package||'delete_communications';
1305
1306 --** C|c1
1307 --** CN|c4|Fetch the per_cm_id from ben_per_cm_f associated with a person_id.
1308 CURSOR c1 IS
1309 SELECT DISTINCT per_cm_id
1310 FROM ben_per_cm_f
1311 WHERE person_id = p_person_id;
1312
1313 --
1314 CURSOR c2 (
1315 p_per_cm_id NUMBER
1316 ) IS
1317 SELECT per_cm_prvdd_id
1318 FROM ben_per_cm_prvdd_f
1319 WHERE per_cm_id = p_per_cm_id
1320 FOR UPDATE OF per_cm_prvdd_id;
1321
1322 --
1323 CURSOR c3 (
1324 p_per_cm_id NUMBER
1325 ) IS
1326 SELECT per_cm_trgr_id
1327 FROM ben_per_cm_trgr_f
1328 WHERE per_cm_id = p_per_cm_id
1329 FOR UPDATE OF per_cm_trgr_id;
1330
1331 --
1332 CURSOR c4 (
1333 p_per_cm_id NUMBER
1334 ) IS
1335 SELECT per_cm_usg_id
1336 FROM ben_per_cm_usg_f
1337 WHERE per_cm_id = p_per_cm_id
1338 FOR UPDATE OF per_cm_usg_id;
1339 l_id NUMBER;
1340 BEGIN
1341 hr_utility.set_location('Entering: '||l_proc,20);
1342 FOR r1 IN c1 LOOP
1343 OPEN c2 (
1344 r1.per_cm_id
1345 );
1346 <<ben_per_cm_prvdd_f>>
1347 LOOP
1348 FETCH c2 INTO l_id;
1349 EXIT WHEN c2%NOTFOUND;
1350 DELETE FROM ben_per_cm_prvdd_f
1351 WHERE CURRENT OF c2;
1352 END LOOP ben_per_cm_prvdd_f;
1353 CLOSE c2;
1354 OPEN c3 (
1355 r1.per_cm_id
1356 );
1357 <<ben_per_cm_trgr_f>>
1358 LOOP
1359 FETCH c3 INTO l_id;
1360 EXIT WHEN c3%NOTFOUND;
1361 DELETE FROM ben_per_cm_trgr_f
1362 WHERE CURRENT OF c3;
1363 END LOOP ben_per_cm_trgr_f;
1364 CLOSE c3;
1365 OPEN c4 (
1366 r1.per_cm_id
1367 );
1368 <<ben_per_cm_usg_f>>
1369 LOOP
1370 FETCH c4 INTO l_id;
1371 EXIT WHEN c4%NOTFOUND;
1372 DELETE FROM ben_per_cm_usg_f
1373 WHERE CURRENT OF c4;
1377 hr_utility.set_location('Leaving: '||l_proc,999);
1374 END LOOP ben_per_cm_usg_f;
1375 CLOSE c4;
1376 END LOOP;
1378 END delete_communications;
1379
1380 --
1381 PROCEDURE delete_life_events (
1382 p_person_id IN NUMBER
1383 ) IS
1384 l_proc varchar2(100):= g_package||'delete_life_events';
1385 --** C|c1
1386 --** CN|c1|Fetch the per_in_ler_id from ben_per_in_ler associated with a person_id.
1387 CURSOR c1 IS
1388 SELECT DISTINCT per_in_ler_id
1389 FROM ben_per_in_ler
1390 WHERE person_id = p_person_id;
1391 --** C|c2|p_per_in_ler_id in number
1392 --** CN|c2|Fetch the prtt_prem_id from ben_prtt_prem_f associated with a per_in_ler_id.
1393 CURSOR c2 (
1394 p_per_in_ler_id IN NUMBER
1395 ) IS
1396 SELECT DISTINCT prtt_prem_id
1397 FROM ben_prtt_prem_f
1398 WHERE per_in_ler_id = p_per_in_ler_id;
1399 CURSOR c3 (
1400 p_prtt_prem_id NUMBER
1401 ) IS
1402 SELECT prtt_prem_by_mo_id
1403 FROM ben_prtt_prem_by_mo_f
1404 WHERE prtt_prem_id = p_prtt_prem_id
1405 FOR UPDATE OF prtt_prem_by_mo_id;
1406 --** C|c4|p_person_id in number
1407 --** CN|c4|Fetch the prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f associated with a per_in_ler_id.
1408 CURSOR c4 (
1409 p_per_in_ler_id IN NUMBER
1410 ) IS
1411 SELECT DISTINCT prtt_enrt_rslt_id
1412 FROM ben_prtt_enrt_rslt_f
1413 WHERE per_in_ler_id = p_per_in_ler_id;
1414
1415 --
1416 CURSOR c5 (
1417 p_prtt_enrt_rslt_id NUMBER
1418 ) IS
1419 SELECT prmry_care_prvdr_id
1420 FROM ben_prmry_care_prvdr_f
1421 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1422 FOR UPDATE OF prmry_care_prvdr_id;
1423
1424 --
1425 CURSOR c6 (
1426 p_prtt_enrt_rslt_id NUMBER
1427 ) IS
1428 SELECT bnft_prvdd_ldgr_id
1429 FROM ben_bnft_prvdd_ldgr_f
1430 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1431 FOR UPDATE OF bnft_prvdd_ldgr_id;
1432
1433 --
1434 CURSOR c7 (
1435 p_prtt_enrt_rslt_id NUMBER
1436 ) IS
1437 SELECT elig_cvrd_dpnt_id
1438 FROM ben_elig_cvrd_dpnt_f
1439 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1440 FOR UPDATE OF elig_cvrd_dpnt_id;
1441
1442 --
1443 CURSOR c8 (
1444 p_prtt_enrt_rslt_id NUMBER
1445 ) IS
1446 SELECT prtt_enrt_ctfn_prvdd_id
1447 FROM ben_prtt_enrt_ctfn_prvdd_f
1448 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1449 FOR UPDATE OF prtt_enrt_ctfn_prvdd_id;
1450
1451 --
1452 CURSOR c9 (
1453 p_prtt_enrt_rslt_id NUMBER
1454 ) IS
1455 SELECT prtt_prem_id
1456 FROM ben_prtt_prem_f
1457 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1458 FOR UPDATE OF prtt_prem_id;
1459
1460 --
1461 CURSOR c10 (
1462 p_prtt_enrt_rslt_id NUMBER
1463 ) IS
1464 SELECT prtt_rt_val_id
1465 FROM ben_prtt_rt_val
1466 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1467 FOR UPDATE OF prtt_rt_val_id;
1468
1469 --
1470 CURSOR c11 (
1471 p_prtt_enrt_rslt_id NUMBER
1472 ) IS
1473 SELECT prtt_enrt_actn_id
1474 FROM ben_prtt_enrt_actn_f
1475 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1476 FOR UPDATE OF prtt_enrt_actn_id;
1477 --** C|c12|p_per_in_ler_id in number
1478 --** CN|c12|Fetch the prtt_enrt_actn_id from ben_prtt_enrt_actn_f associated with a per_in_ler_id.
1479 CURSOR c12 (
1480 p_per_in_ler_id IN NUMBER
1481 ) IS
1482 SELECT DISTINCT prtt_enrt_actn_id
1483 FROM ben_prtt_enrt_actn_f
1484 WHERE per_in_ler_id = p_per_in_ler_id;
1485 CURSOR c13 (
1486 p_prtt_enrt_actn_id NUMBER
1487 ) IS
1488 SELECT pl_bnf_ctfn_prvdd_id
1489 FROM ben_pl_bnf_ctfn_prvdd_f
1490 WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id
1491 FOR UPDATE OF pl_bnf_ctfn_prvdd_id;
1492
1493 --
1494 CURSOR c14 (
1495 p_prtt_enrt_actn_id NUMBER
1496 ) IS
1497 SELECT cvrd_dpnt_ctfn_prvdd_id
1498 FROM ben_cvrd_dpnt_ctfn_prvdd_f
1499 WHERE prtt_enrt_actn_id = p_prtt_enrt_actn_id
1500 FOR UPDATE OF cvrd_dpnt_ctfn_prvdd_id;
1501 --** C|c15|p_per_in_ler_id in number
1502 --** CN|c15|Fetch the enrt_bnft_id from ben_enrt_bnft associated with a per_in_ler_id and elig_per_elctbl_chc_id.
1503 CURSOR c15 (
1504 p_per_in_ler_id IN NUMBER
1505 ) IS
1506 SELECT DISTINCT enrt_bnft_id
1507 FROM ben_elig_per_elctbl_chc b1, ben_enrt_bnft b2
1511
1508 WHERE b1.per_in_ler_id = p_per_in_ler_id
1509 AND b2.elig_per_elctbl_chc_id =
1510 b1.elig_per_elctbl_chc_id;
1512 --
1513 CURSOR c16 (
1514 p_enrt_bnft_id NUMBER
1515 ) IS
1516 SELECT enrt_rt_id
1517 FROM ben_enrt_rt
1518 WHERE enrt_bnft_id = p_enrt_bnft_id
1519 FOR UPDATE OF enrt_rt_id;
1520
1521 --
1522 --** C|c17|p_per_in_ler_id in number
1523 --** CN|c17|Fetch the elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc associated with a per_in_ler_id.
1524 CURSOR c17 (
1525 p_per_in_ler_id IN NUMBER
1526 ) IS
1527 SELECT DISTINCT elig_per_elctbl_chc_id
1528 FROM ben_elig_per_elctbl_chc
1529 WHERE per_in_ler_id = p_per_in_ler_id;
1530 CURSOR c18 (
1531 p_elig_per_elctbl_chc_id NUMBER
1532 ) IS
1533 SELECT enrt_bnft_id
1534 FROM ben_enrt_bnft
1535 WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1536 FOR UPDATE OF enrt_bnft_id;
1537
1538 --
1539 CURSOR c19 (
1540 p_elig_per_elctbl_chc_id NUMBER
1541 ) IS
1542 SELECT enrt_prem_id
1543 FROM ben_enrt_prem
1544 WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1545 FOR UPDATE OF enrt_prem_id;
1546
1547 --
1548 CURSOR c20 (
1549 p_elig_per_elctbl_chc_id NUMBER
1550 ) IS
1551 SELECT enrt_rt_id
1552 FROM ben_enrt_rt
1553 WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1554 FOR UPDATE OF enrt_rt_id;
1555
1556 --
1557 CURSOR c21 (
1558 p_elig_per_elctbl_chc_id NUMBER
1559 ) IS
1560 SELECT elctbl_chc_ctfn_id
1561 FROM ben_elctbl_chc_ctfn
1562 WHERE elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1563 FOR UPDATE OF elctbl_chc_ctfn_id;
1564
1565 --
1566 --** C|c22|p_per_in_ler_id in number
1567 --** CN|c22|Fetch the elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt associated with a per_in_ler_id and prtt_enrt_rslt_id.
1568 CURSOR c22 (
1569 p_per_in_ler_id IN NUMBER
1570 ) IS
1571 SELECT DISTINCT elig_cvrd_dpnt_id
1572 FROM ben_prtt_enrt_rslt_f b1, ben_elig_cvrd_dpnt b2
1573 WHERE b1.per_in_ler_id = p_per_in_ler_id
1574 AND b2.prtt_enrt_rslt_id = b1.prtt_enrt_rslt_id;
1575
1576 --
1577 CURSOR c23 (
1578 p_elig_cvrd_dpnt_id NUMBER
1579 ) IS
1580 SELECT prmry_care_prvdr_id
1581 FROM ben_prmry_care_prvdr_f
1582 WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1583 FOR UPDATE OF prmry_care_prvdr_id;
1584
1585 --
1586 CURSOR c24 (
1587 p_per_in_ler_id IN NUMBER
1588 ) IS
1589 SELECT cbr_per_in_ler_id
1590 FROM ben_cbr_per_in_ler
1591 WHERE per_in_ler_id = p_per_in_ler_id
1592 FOR UPDATE OF cbr_per_in_ler_id;
1593
1594 --
1595 CURSOR c25 (
1596 p_per_in_ler_id IN NUMBER
1597 ) IS
1598 SELECT elig_dpnt_id
1599 FROM ben_elig_dpnt
1600 WHERE per_in_ler_id = p_per_in_ler_id
1601 FOR UPDATE OF elig_dpnt_id;
1602
1603 --
1604 CURSOR c26 (
1605 p_per_in_ler_id IN NUMBER
1606 ) IS
1607 SELECT elig_per_elctbl_chc_id
1608 FROM ben_elig_per_elctbl_chc
1609 WHERE per_in_ler_id = p_per_in_ler_id
1610 FOR UPDATE OF elig_per_elctbl_chc_id;
1611
1612 --
1613 CURSOR c27 (
1614 p_per_in_ler_id IN NUMBER
1615 ) IS
1616 SELECT elig_per_opt_id
1617 FROM ben_elig_per_opt_f
1618 WHERE per_in_ler_id = p_per_in_ler_id
1619 FOR UPDATE OF elig_per_opt_id;
1620
1621 --
1622 CURSOR c28 (
1623 p_per_in_ler_id IN NUMBER
1624 ) IS
1625 SELECT bkup_tbl_id
1626 FROM ben_le_clsn_n_rstr
1627 WHERE per_in_ler_id = p_per_in_ler_id
1628 FOR UPDATE OF bkup_tbl_id;
1629
1630 --
1631 CURSOR c29 (
1632 p_per_in_ler_id IN NUMBER
1633 ) IS
1634 SELECT pil_elctbl_chc_popl_id
1635 FROM ben_pil_elctbl_chc_popl
1636 WHERE per_in_ler_id = p_per_in_ler_id
1637 FOR UPDATE OF pil_elctbl_chc_popl_id;
1638
1639 --
1640 CURSOR c30 (
1641 p_per_in_ler_id IN NUMBER
1642 ) IS
1643 SELECT pl_bnf_id
1644 FROM ben_pl_bnf_f
1645 WHERE per_in_ler_id = p_per_in_ler_id
1646 FOR UPDATE OF pl_bnf_id;
1647
1648 --
1649 CURSOR c31 (
1650 p_per_in_ler_id IN NUMBER
1651 ) IS
1652 SELECT prtt_enrt_rslt_id
1653 FROM ben_prtt_enrt_rslt_f
1657 --
1654 WHERE per_in_ler_id = p_per_in_ler_id
1655 FOR UPDATE OF prtt_enrt_rslt_id;
1656
1658 l_id NUMBER;
1659 BEGIN
1660 hr_utility.set_location('Entering: '||l_proc,10);
1661 FOR r1 IN c1 LOOP
1662 FOR r2 IN c2 (
1663 r1.per_in_ler_id
1664 ) LOOP
1665 OPEN c3 (
1666 r2.prtt_prem_id
1667 );
1668 <<ben_prtt_prem_by_mo_f>>
1669 LOOP
1670 FETCH c3 INTO l_id;
1671 EXIT WHEN c3%NOTFOUND;
1672 DELETE FROM ben_prtt_prem_by_mo_f
1673 WHERE CURRENT OF c3;
1674 END LOOP ben_prtt_prem_by_mo_f;
1675 CLOSE c3;
1676 END LOOP;
1677 FOR r4 IN c4 (
1678 r1.per_in_ler_id
1679 ) LOOP
1680 OPEN c5 (
1681 r4.prtt_enrt_rslt_id
1682 );
1683 <<ben_prmry_care_prvdr_f>>
1684 LOOP
1685 FETCH c5 INTO l_id;
1686 EXIT WHEN c5%NOTFOUND;
1687 DELETE FROM ben_prmry_care_prvdr_f
1688 WHERE CURRENT OF c5;
1689 END LOOP ben_prmry_care_prvdr_f;
1690 CLOSE c5;
1691 OPEN c6 (
1692 r4.prtt_enrt_rslt_id
1693 );
1694 <<ben_bnft_prvdd_ldgr_f>>
1695 LOOP
1696 FETCH c6 INTO l_id;
1697 EXIT WHEN c6%NOTFOUND;
1698 DELETE FROM ben_bnft_prvdd_ldgr_f
1699 WHERE CURRENT OF c6;
1700 END LOOP ben_bnft_prvdd_ldgr_f;
1701 CLOSE c6;
1702 OPEN c7 (
1703 r4.prtt_enrt_rslt_id
1704 );
1705 <<ben_elig_cvrd_dpnt_f>>
1706 LOOP
1707 FETCH c7 INTO l_id;
1708 EXIT WHEN c7%NOTFOUND;
1709 DELETE FROM ben_elig_cvrd_dpnt_f
1710 WHERE CURRENT OF c7;
1711 END LOOP ben_elig_cvrd_dpnt_f;
1712 CLOSE c7;
1713 OPEN c8 (
1714 r4.prtt_enrt_rslt_id
1715 );
1716 <<ben_prtt_enrt_ctfn_prvdd_f>>
1717 LOOP
1718 FETCH c8 INTO l_id;
1719 EXIT WHEN c8%NOTFOUND;
1720 DELETE FROM ben_prtt_enrt_ctfn_prvdd_f
1721 WHERE CURRENT OF c8;
1722 END LOOP ben_prtt_enrt_ctfn_prvdd_f;
1723 CLOSE c8;
1724 OPEN c9 (
1725 r4.prtt_enrt_rslt_id
1726 );
1727 <<ben_prtt_prem_f>>
1728 LOOP
1729 FETCH c9 INTO l_id;
1730 EXIT WHEN c9%NOTFOUND;
1731 DELETE FROM ben_prtt_prem_f
1732 WHERE CURRENT OF c9;
1733 END LOOP ben_prtt_prem_f;
1734 CLOSE c9;
1735 OPEN c10 (
1736 r4.prtt_enrt_rslt_id
1737 );
1738 <<ben_prtt_rt_val>>
1739 LOOP
1740 FETCH c10 INTO l_id;
1741 EXIT WHEN c10%NOTFOUND;
1742 DELETE FROM ben_prtt_rt_val
1743 WHERE CURRENT OF c10;
1744 END LOOP ben_prtt_rt_val;
1745 CLOSE c10;
1746 OPEN c11 (
1747 r4.prtt_enrt_rslt_id
1748 );
1749 <<ben_prtt_enrt_actn_f>>
1750 LOOP
1751 FETCH c11 INTO l_id;
1752 EXIT WHEN c11%NOTFOUND;
1753 DELETE FROM ben_prtt_enrt_actn_f
1754 WHERE CURRENT OF c11;
1755 END LOOP ben_prtt_enrt_actn_f;
1756 CLOSE c11;
1757 END LOOP;
1758 FOR r12 IN c12 (
1759 r1.per_in_ler_id
1760 ) LOOP
1761 OPEN c13 (
1762 r12.prtt_enrt_actn_id
1763 );
1764 <<ben_pl_bnf_ctfn_prvdd_f>>
1765 LOOP
1766 FETCH c13 INTO l_id;
1767 EXIT WHEN c13%NOTFOUND;
1768 DELETE FROM ben_pl_bnf_ctfn_prvdd_f
1769 WHERE CURRENT OF c13;
1770 END LOOP ben_pl_bnf_ctfn_prvdd_f;
1771 CLOSE c13;
1772 OPEN c14 (
1773 r12.prtt_enrt_actn_id
1774 );
1775 <<ben_cvrd_dpnt_ctfn_prvdd_f>>
1776 LOOP
1777 FETCH c14 INTO l_id;
1778 EXIT WHEN c14%NOTFOUND;
1779 DELETE FROM ben_cvrd_dpnt_ctfn_prvdd_f
1780 WHERE CURRENT OF c14;
1781 END LOOP ben_cvrd_dpnt_ctfn_prvdd_f;
1782 CLOSE c14;
1783 END LOOP;
1784 hr_utility.set_location(l_proc,50);
1785 FOR r15 IN c15 (
1786 r1.per_in_ler_id
1787 ) LOOP
1788 OPEN c16 (
1789 r15.enrt_bnft_id
1790 );
1791 <<ben_enrt_rt>>
1792 LOOP
1793 FETCH c16 INTO l_id;
1794 EXIT WHEN c16%NOTFOUND;
1795 DELETE FROM ben_enrt_rt
1796 WHERE CURRENT OF c16;
1797 END LOOP;
1801 FOR r17 IN c17 (
1798 CLOSE c16;
1799 END LOOP;
1800 hr_utility.set_location(l_proc,55);
1802 r1.per_in_ler_id
1803 ) LOOP
1804 OPEN c18 (
1805 r17.elig_per_elctbl_chc_id
1806 );
1807 <<ben_enrt_bnft>>
1808 LOOP
1809 FETCH c18 INTO l_id;
1810 EXIT WHEN c18%NOTFOUND;
1811 DELETE FROM ben_enrt_bnft
1812 WHERE CURRENT OF c18;
1813 END LOOP ben_enrt_bnft;
1814 CLOSE c18;
1815 --
1816 hr_utility.set_location(l_proc,60);
1817 OPEN c19 (
1818 r17.elig_per_elctbl_chc_id
1819 );
1820 <<ben_enrt_prem>>
1821 LOOP
1822 FETCH c19 INTO l_id;
1823 EXIT WHEN c19%NOTFOUND;
1824 DELETE FROM ben_enrt_prem
1825 WHERE CURRENT OF c19;
1826 END LOOP;
1827 CLOSE c19;
1828 --
1829 hr_utility.set_location(l_proc,65);
1830 OPEN c20 (
1831 r17.elig_per_elctbl_chc_id
1832 );
1833 <<ben_enrt_rt>>
1834 LOOP
1835 FETCH c20 INTO l_id;
1836 EXIT WHEN c20%NOTFOUND;
1837 DELETE FROM ben_enrt_rt
1838 WHERE CURRENT OF c20;
1839 END LOOP ben_enrt_rt;
1840 CLOSE c20;
1841 --
1842 hr_utility.set_location(l_proc,70);
1843 OPEN c21 (
1844 r17.elig_per_elctbl_chc_id
1845 );
1846 <<ben_elctbl_chc_ctfn>>
1847 LOOP
1848 FETCH c21 INTO l_id;
1849 EXIT WHEN c21%NOTFOUND;
1850 DELETE FROM ben_elctbl_chc_ctfn
1851 WHERE CURRENT OF c21;
1852 END LOOP ben_elctbl_chc_ctfn;
1853 CLOSE c21;
1854 END LOOP;
1855 hr_utility.set_location(l_proc,70);
1856 FOR r22 IN c22 (
1857 r1.per_in_ler_id
1858 ) LOOP
1859 OPEN c23 (
1860 r22.elig_cvrd_dpnt_id
1861 );
1862 <<ben_prmry_care_prvdr_f>>
1863 LOOP
1864 FETCH c23 INTO l_id;
1865 EXIT WHEN c23%NOTFOUND;
1866 DELETE FROM ben_prmry_care_prvdr_f
1867 WHERE CURRENT OF c23;
1868 END LOOP ben_prmry_care_prvdr_f;
1869 CLOSE c23;
1870 END LOOP;
1871 OPEN c24 (
1872 r1.per_in_ler_id
1873 );
1874 <<ben_cbr_per_in_ler>>
1875 LOOP
1876 FETCH c24 INTO l_id;
1877 EXIT WHEN c24%NOTFOUND;
1878 DELETE FROM ben_cbr_per_in_ler
1879 WHERE CURRENT OF c24;
1880 END LOOP ben_cbr_per_in_ler;
1881 CLOSE c24;
1882 OPEN c25 (
1883 r1.per_in_ler_id
1884 );
1885 <<ben_elig_dpnt>>
1886 LOOP
1887 FETCH c25 INTO l_id;
1888 EXIT WHEN c25%NOTFOUND;
1889 DELETE FROM ben_elig_dpnt
1890 WHERE CURRENT OF c25;
1891 END LOOP ben_elig_dpnt;
1892 CLOSE c25;
1893 OPEN c26 (
1894 r1.per_in_ler_id
1895 );
1896 hr_utility.set_location(l_proc,90);
1897 <<ben_elig_per_elctbl_chc>>
1898 LOOP
1899 FETCH c26 INTO l_id;
1900 EXIT WHEN c26%NOTFOUND;
1901 DELETE FROM ben_elig_per_elctbl_chc
1902 WHERE CURRENT OF c26;
1903 END LOOP ben_elig_per_elctbl_chc;
1904 CLOSE c26;
1905 OPEN c27 (
1906 r1.per_in_ler_id
1907 );
1908 <<ben_elig_per_opt_f>>
1909 LOOP
1910 FETCH c27 INTO l_id;
1911 EXIT WHEN c27%NOTFOUND;
1912 DELETE FROM ben_elig_per_opt_f
1913 WHERE CURRENT OF c27;
1914 END LOOP ben_elig_per_opt_f;
1915 CLOSE c27;
1916 OPEN c28 (
1917 r1.per_in_ler_id
1918 );
1919 <<ben_le_clsn_n_rstr>>
1920 LOOP
1921 FETCH c28 INTO l_id;
1922 EXIT WHEN c28%NOTFOUND;
1923 DELETE FROM ben_le_clsn_n_rstr
1924 WHERE CURRENT OF c28;
1925 END LOOP ben_le_clsn_n_rstr;
1926 CLOSE c28;
1927 OPEN c29 (
1928 r1.per_in_ler_id
1929 );
1930 <<ben_pil_elctbl_chc_popl>>
1931 LOOP
1932 FETCH c29 INTO l_id;
1933 EXIT WHEN c29%NOTFOUND;
1934 DELETE FROM ben_pil_elctbl_chc_popl
1935 WHERE CURRENT OF c29;
1936 END LOOP ben_pil_elctbl_chc_popl;
1937 CLOSE c29;
1938 OPEN c30 (
1939 r1.per_in_ler_id
1940 );
1941 <<ben_pl_bnf_f>>
1942 LOOP
1943 FETCH c30 INTO l_id;
1944 EXIT WHEN c30%NOTFOUND;
1945 DELETE FROM ben_pl_bnf_f
1946 WHERE CURRENT OF c30;
1947 END LOOP ben_pl_bnf_f;
1951 );
1948 CLOSE c30;
1949 OPEN c31 (
1950 r1.per_in_ler_id
1952 <<ben_prtt_enrt_rslt_f>>
1953 LOOP
1954 FETCH c31 INTO l_id;
1955 EXIT WHEN c31%NOTFOUND;
1956 DELETE FROM ben_prtt_enrt_rslt_f
1957 WHERE CURRENT OF c31;
1958 END LOOP ben_prtt_enrt_rslt_f;
1959 CLOSE c31;
1960 END LOOP;
1961 hr_utility.set_location('Leaving: '||l_proc,999);
1962 END delete_life_events;
1963
1964 --
1965 PROCEDURE delete_participant_information (
1966 p_person_id IN NUMBER
1967 ) IS
1968 l_proc varchar2(100):= g_package||'delete_participant_information';
1969
1970 --** C|c2|p_person_id in number
1971 --** CN|c2|Fetch the prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f associated with person_id.
1972 CURSOR c1 (
1973 p_person_id IN NUMBER
1974 ) IS
1975 SELECT DISTINCT prtt_enrt_rslt_id
1976 FROM ben_prtt_enrt_rslt_f
1977 WHERE person_id = p_person_id;
1978
1979 --
1980 CURSOR c2 (
1981 p_prtt_enrt_rslt_id NUMBER
1982 ) IS
1983 SELECT prmry_care_prvdr_id
1984 FROM ben_prmry_care_prvdr_f
1985 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1986 FOR UPDATE OF prmry_care_prvdr_id;
1987
1988 --
1989
1990 CURSOR c3 (
1991 p_prtt_enrt_rslt_id NUMBER
1992 ) IS
1993 SELECT bnft_prvdd_ldgr_id
1994 FROM ben_bnft_prvdd_ldgr_f
1995 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1996 FOR UPDATE OF bnft_prvdd_ldgr_id;
1997
1998 --
1999 CURSOR c4 (
2000 p_prtt_enrt_rslt_id NUMBER
2001 ) IS
2002 SELECT elig_cvrd_dpnt_id
2003 FROM ben_elig_cvrd_dpnt_f
2004 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2005 FOR UPDATE OF elig_cvrd_dpnt_id;
2006
2007 --
2008 CURSOR c5 (
2009 p_prtt_enrt_rslt_id NUMBER
2010 ) IS
2011 SELECT prtt_enrt_ctfn_prvdd_id
2012 FROM ben_prtt_enrt_ctfn_prvdd_f
2013 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2014 FOR UPDATE OF prtt_enrt_ctfn_prvdd_id;
2015
2016 --
2017
2018 CURSOR c6 (
2019 p_prtt_enrt_rslt_id NUMBER
2020 ) IS
2021 SELECT prtt_prem_id
2022 FROM ben_prtt_prem_f
2023 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2024 FOR UPDATE OF prtt_prem_id;
2025
2026 --
2027 CURSOR c7 (
2028 p_prtt_enrt_rslt_id NUMBER
2029 ) IS
2030 SELECT prtt_rt_val_id
2031 FROM ben_prtt_rt_val
2032 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2033 FOR UPDATE OF prtt_rt_val_id;
2034 CURSOR c8 (
2035 p_prtt_enrt_rslt_id NUMBER
2036 ) IS
2037 SELECT prtt_enrt_actn_id
2038 FROM ben_prtt_enrt_actn_f
2039 WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2040 FOR UPDATE OF prtt_enrt_actn_id;
2041 l_id NUMBER;
2042 BEGIN
2043 hr_utility.set_location('Entering: '||l_proc,10);
2044 FOR r1 IN c1 (
2045 p_person_id
2046 ) LOOP
2047 OPEN c2 (
2048 r1.prtt_enrt_rslt_id
2049 );
2050 <<ben_prmry_care_prvdr_f>>
2051 LOOP
2052 FETCH c2 INTO l_id;
2053 EXIT WHEN c2%NOTFOUND;
2054 DELETE FROM ben_prmry_care_prvdr_f
2055 WHERE CURRENT OF c2;
2056 END LOOP ben_prmry_care_prvdr_f;
2057 CLOSE c2;
2058 OPEN c3 (
2059 r1.prtt_enrt_rslt_id
2060 );
2061 <<ben_bnft_prvdd_ldgr_f>>
2062 LOOP
2063 FETCH c3 INTO l_id;
2064 EXIT WHEN c3%NOTFOUND;
2065 DELETE FROM ben_bnft_prvdd_ldgr_f
2066 WHERE CURRENT OF c3;
2067 END LOOP ben_bnft_prvdd_ldgr_f;
2068 CLOSE c3;
2069 OPEN c4 (
2070 r1.prtt_enrt_rslt_id
2071 );
2072 <<ben_elig_cvrd_dpnt_f>>
2073 LOOP
2074 FETCH c4 INTO l_id;
2075 EXIT WHEN c4%NOTFOUND;
2076 DELETE FROM ben_elig_cvrd_dpnt_f
2077 WHERE CURRENT OF c4;
2078 END LOOP ben_elig_cvrd_dpnt_f;
2079 CLOSE c4;
2080 OPEN c5 (
2081 r1.prtt_enrt_rslt_id
2082 );
2083 <<ben_prtt_enrt_ctfn_prvdd_f>>
2084 LOOP
2085 FETCH c5 INTO l_id;
2086 EXIT WHEN c5%NOTFOUND;
2087 DELETE FROM ben_prtt_enrt_ctfn_prvdd_f
2088 WHERE CURRENT OF c5;
2089 END LOOP ben_prtt_enrt_ctfn_prvdd_f;
2090 CLOSE c5;
2091 OPEN c6 (
2092 r1.prtt_enrt_rslt_id
2093 );
2094 <<ben_prtt_prem_f>>
2095 LOOP
2096 FETCH c6 INTO l_id;
2097 EXIT WHEN c6%NOTFOUND;
2101 CLOSE c6;
2098 DELETE FROM ben_prtt_prem_f
2099 WHERE CURRENT OF c6;
2100 END LOOP ben_prtt_prem_f;
2102 OPEN c7 (
2103 r1.prtt_enrt_rslt_id
2104 );
2105 <<ben_prtt_rt_val>>
2106 LOOP
2107 FETCH c7 INTO l_id;
2108 EXIT WHEN c7%NOTFOUND;
2109 DELETE FROM ben_prtt_rt_val
2110 WHERE CURRENT OF c7;
2111 END LOOP ben_prtt_rt_val;
2112 CLOSE c7;
2113 OPEN c8 (
2114 r1.prtt_enrt_rslt_id
2115 );
2116 <<ben_prtt_enrt_actn_f>>
2117 LOOP
2118 FETCH c8 INTO l_id;
2119 EXIT WHEN c8%NOTFOUND;
2120 DELETE FROM ben_prtt_enrt_actn_f
2121 WHERE CURRENT OF c8;
2122 END LOOP ben_prtt_enrt_actn_f;
2123 CLOSE c8;
2124 END LOOP;
2125 hr_utility.set_location('Leaving: '||l_proc,999);
2126 END delete_participant_information;
2127
2128 --
2129 PROCEDURE delete_benefit_action_children (
2130 p_person_id IN NUMBER
2131 ) IS
2132
2133 l_proc varchar2(100):= g_package||'delete_benefit_action_children';
2134 --** C|c1
2135 --** CN|c1|Fetch the benefit_action_id from ben_benefit_actions associated with a person_id for a particular business group.
2136 CURSOR c1 IS
2137 SELECT DISTINCT benefit_action_id
2138 FROM ben_benefit_actions
2139 WHERE person_id = p_person_id;
2140
2141 --
2142 CURSOR c2 (
2143 p_benefit_action_id NUMBER
2144 ) IS
2145 SELECT reporting_id
2146 FROM ben_reporting
2147 WHERE benefit_action_id = p_benefit_action_id
2148 FOR UPDATE OF reporting_id;
2149 CURSOR c3 (
2150 p_benefit_action_id NUMBER
2151 ) IS
2152 SELECT person_action_id
2153 FROM ben_person_actions
2154 WHERE benefit_action_id = p_benefit_action_id
2155 FOR UPDATE OF person_action_id;
2156 CURSOR c4 (
2157 p_benefit_action_id NUMBER
2158 ) IS
2159 SELECT range_id
2160 FROM ben_batch_ranges
2161 WHERE benefit_action_id = p_benefit_action_id
2162 FOR UPDATE OF range_id;
2163
2164 --
2165 l_id NUMBER;
2166 BEGIN
2167 hr_utility.set_location('Entering: '||l_proc,10);
2168 FOR r1 IN c1 LOOP
2169 OPEN c2 (
2170 r1.benefit_action_id
2171 );
2172 <<ben_reporting>>
2173 LOOP
2174 FETCH c2 INTO l_id;
2175 EXIT WHEN c2%NOTFOUND;
2176 DELETE FROM ben_reporting
2177 WHERE CURRENT OF c2;
2178 END LOOP ben_reporting;
2179 CLOSE c2;
2180 OPEN c3 (
2181 r1.benefit_action_id
2182 );
2183 <<ben_person_actions>>
2184 LOOP
2185 FETCH c3 INTO l_id;
2186 EXIT WHEN c3%NOTFOUND;
2187 DELETE FROM ben_person_actions
2188 WHERE CURRENT OF c3;
2189 END LOOP ben_person_actions;
2190 CLOSE c3;
2191 OPEN c4 (
2192 r1.benefit_action_id
2193 );
2194 <<ben_batch_ranges>>
2195 LOOP
2196 FETCH c4 INTO l_id;
2197 EXIT WHEN c4%NOTFOUND;
2198 DELETE FROM ben_batch_ranges
2199 WHERE CURRENT OF c4;
2200 END LOOP ben_batch_ranges;
2201 CLOSE c4;
2202 END LOOP;
2203 hr_utility.set_location('Leaving: '||l_proc,999);
2204 END delete_benefit_action_children;
2205
2206 --
2207 PROCEDURE delete_reimbmt_rqst (
2208 p_person_id NUMBER
2209 ) IS
2210
2211 --** C|c1
2212 --** CN|c1|Fetch the prtt_reimbmt_rqst_id from ben_prtt_reimbmt_rqst_f associated with a person_id.
2213 CURSOR c1 IS
2214 SELECT prtt_reimbmt_rqst_id
2215 FROM ben_prtt_reimbmt_rqst_f
2216 WHERE submitter_person_id = p_person_id
2217 OR recipient_person_id = p_person_id
2218 OR provider_person_id = p_person_id
2219 OR provider_ssn_person_id = p_person_id
2220 OR contact_relationship_id IN
2221 (SELECT contact_relationship_id
2222 FROM per_contact_relationships
2223 WHERE person_id = p_person_id)
2224 FOR UPDATE OF prtt_reimbmt_rqst_id;
2225
2226 --
2227 CURSOR c2 (
2228 p_prtt_reimbmt_rqst_id NUMBER
2229 ) IS
2230 SELECT prtt_reimbmt_recon_id
2231 FROM ben_prtt_reimbmt_recon
2232 WHERE prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
2233 FOR UPDATE OF prtt_reimbmt_recon_id;
2234 l_id NUMBER;
2235 BEGIN
2236 FOR r1 IN c1 LOOP
2237 OPEN c2 (
2238 r1.prtt_reimbmt_rqst_id
2239 );
2240 <<ben_prtt_reimbmt_recon>>
2241 LOOP
2242 FETCH c2 INTO l_id;
2243 EXIT WHEN c2%NOTFOUND;
2247 CLOSE c2;
2244 DELETE FROM ben_prtt_reimbmt_recon
2245 WHERE CURRENT OF c2;
2246 END LOOP ben_prtt_reimbmt_recon;
2248 DELETE FROM ben_prtt_reimbmt_rqst_f
2249 WHERE CURRENT OF c1;
2250 END LOOP;
2251 END delete_reimbmt_rqst;
2252
2253 --
2254 PROCEDURE delete_ben_rows (
2255 p_person_id NUMBER
2256 ) IS
2257 --** C|c01
2258 CURSOR c01 (
2259 p_benefit_action_id IN NUMBER
2260 ) IS
2261 SELECT benefit_action_id
2262 FROM ben_reporting
2263 WHERE benefit_action_id = p_benefit_action_id
2264 FOR UPDATE OF benefit_action_id;
2265 --** C|c7
2266 --** CN|c7|Fetch the elig_per_id from ben_elig_per_f associated with a person_id.
2267 CURSOR c7 IS
2268 SELECT DISTINCT elig_per_id
2269 FROM ben_elig_per_f
2270 WHERE person_id = p_person_id;
2271 --** C|c9
2272 --** CN|c9|Fetch the element_entry_id from pay_element_entries_f associated with a person_id.
2273 CURSOR c9 IS
2274 SELECT DISTINCT element_entry_id
2275 FROM per_all_assignments_f paf, pay_element_entries_f pee
2276 WHERE paf.person_id = p_person_id
2277 AND pee.assignment_id = paf.assignment_id;
2278 CURSOR c20 (
2279 p_person_id NUMBER
2280 ) IS
2281 SELECT batch_actn_item_id
2282 FROM ben_batch_actn_item_info
2283 WHERE person_id = p_person_id
2284 FOR UPDATE OF batch_actn_item_id;
2285
2286 --
2287 CURSOR c21 (
2288 p_person_id NUMBER
2289 ) IS
2290 SELECT batch_benft_cert_id
2291 FROM ben_batch_bnft_cert_info
2292 WHERE person_id = p_person_id
2293 FOR UPDATE OF batch_benft_cert_id;
2294
2295 --
2296 CURSOR c22 (
2297 p_person_id NUMBER
2298 ) IS
2299 SELECT batch_commu_id
2300 FROM ben_batch_commu_info
2301 WHERE person_id = p_person_id
2302 FOR UPDATE OF batch_commu_id;
2303
2304 --
2305 CURSOR c23 (
2306 p_person_id NUMBER
2307 ) IS
2308 SELECT batch_dpnt_id
2309 FROM ben_batch_dpnt_info
2310 WHERE person_id = p_person_id
2311 FOR UPDATE OF batch_dpnt_id;
2312
2313 --
2314 CURSOR c24 (
2315 p_person_id NUMBER
2316 ) IS
2317 SELECT batch_elctbl_id
2318 FROM ben_batch_elctbl_chc_info
2319 WHERE person_id = p_person_id
2320 FOR UPDATE OF batch_elctbl_id;
2321
2322 --
2323 CURSOR c25 (
2324 p_person_id NUMBER
2325 ) IS
2326 SELECT batch_elig_id
2327 FROM ben_batch_elig_info
2328 WHERE person_id = p_person_id
2329 FOR UPDATE OF batch_elig_id;
2330
2331 --
2332 CURSOR c26 (
2333 p_person_id NUMBER
2334 ) IS
2335 SELECT batch_ler_id
2336 FROM ben_batch_ler_info
2337 WHERE person_id = p_person_id
2338 FOR UPDATE OF batch_ler_id;
2339
2340 --
2341 CURSOR c27 (
2342 p_person_id NUMBER
2343 ) IS
2344 SELECT batch_rt_id
2345 FROM ben_batch_rate_info
2346 WHERE person_id = p_person_id
2347 FOR UPDATE OF batch_rt_id;
2348
2349 --
2350 CURSOR c28 (
2351 p_person_id NUMBER
2352 ) IS
2353 SELECT reporting_id
2354 FROM ben_reporting rep,
2355 ben_person_actions pat
2356 WHERE pat.person_id = p_person_id
2357 AND rep.benefit_action_id = pat.benefit_action_id /* Bug 4882374 : Perf */
2358 FOR UPDATE OF rep.reporting_id;
2359
2360 --
2361 CURSOR c29 (
2362 p_person_id NUMBER
2363 ) IS
2364 SELECT person_action_id
2365 FROM ben_person_actions
2366 WHERE person_id = p_person_id
2367 FOR UPDATE OF person_action_id;
2368
2369 --
2370 CURSOR c30 (
2371 p_person_id NUMBER
2372 ) IS
2373 SELECT benefit_action_id
2374 FROM ben_benefit_actions
2375 WHERE person_id = p_person_id
2376 FOR UPDATE OF benefit_action_id;
2377
2378 --
2379 CURSOR c31 (
2380 p_person_id NUMBER
2381 ) IS
2382 SELECT cbr_quald_bnf_id
2383 FROM ben_cbr_quald_bnf
2384 WHERE cvrd_emp_person_id = p_person_id
2385 FOR UPDATE OF cbr_quald_bnf_id;
2386
2387 --
2388 CURSOR c32 (
2389 p_person_id NUMBER
2390 ) IS
2391 SELECT crt_ordr_cvrd_per_id
2392 FROM ben_crt_ordr_cvrd_per
2396 FOR UPDATE OF crt_ordr_cvrd_per_id;
2393 WHERE crt_ordr_id IN (SELECT crt_ordr_id
2394 FROM ben_crt_ordr
2395 WHERE person_id = p_person_id)
2397
2398 --
2399 CURSOR c33 (
2400 p_person_id NUMBER
2401 ) IS
2402 SELECT crt_ordr_id
2403 FROM ben_crt_ordr
2404 WHERE person_id = p_person_id
2405 FOR UPDATE OF crt_ordr_id;
2406
2407 --
2408 CURSOR c34 (
2409 p_person_id NUMBER
2410 ) IS
2411 SELECT elig_per_id
2412 FROM ben_elig_per_f
2413 WHERE person_id = p_person_id
2414 FOR UPDATE OF elig_per_id;
2415
2416 --
2417 CURSOR c35 (
2418 p_person_id NUMBER
2419 ) IS
2420 SELECT ext_chg_evt_log_id
2421 FROM ben_ext_chg_evt_log
2422 WHERE person_id = p_person_id
2423 FOR UPDATE OF ext_chg_evt_log_id;
2424
2425 --
2426 CURSOR c36 (
2427 p_person_id NUMBER
2428 ) IS
2429 SELECT ext_rslt_dtl_id
2430 FROM ben_ext_rslt_dtl
2431 WHERE person_id = p_person_id
2432 FOR UPDATE OF ext_rslt_dtl_id;
2433
2434 --
2435 CURSOR c37 (
2436 p_person_id NUMBER
2437 ) IS
2438 SELECT ext_rslt_err_id
2439 FROM ben_ext_rslt_err
2440 WHERE person_id = p_person_id
2441 FOR UPDATE OF ext_rslt_err_id;
2442
2443 --
2444 CURSOR c38 (
2445 p_person_id NUMBER
2446 ) IS
2447 SELECT per_bnfts_bal_id
2448 FROM ben_per_bnfts_bal_f
2449 WHERE person_id = p_person_id
2450 FOR UPDATE OF per_bnfts_bal_id;
2451
2452 --
2453 CURSOR c39 (
2454 p_person_id NUMBER
2455 ) IS
2456 SELECT per_cm_id
2457 FROM ben_per_cm_f
2458 WHERE person_id = p_person_id
2459 FOR UPDATE OF per_cm_id;
2460
2461 --
2462 CURSOR c40 (
2463 p_person_id NUMBER
2464 ) IS
2465 SELECT per_dlvry_mthd_id
2466 FROM ben_per_dlvry_mthd_f
2467 WHERE person_id = p_person_id
2468 FOR UPDATE OF per_dlvry_mthd_id;
2469
2470 --
2471 CURSOR c41 (
2472 p_person_id NUMBER
2473 ) IS
2474 SELECT per_in_ler_id
2475 FROM ben_per_in_ler
2476 WHERE person_id = p_person_id
2477 FOR UPDATE OF per_in_ler_id;
2478
2479 --
2480 CURSOR c42 (
2481 p_person_id NUMBER
2482 ) IS
2483 SELECT per_in_lgl_enty_id
2484 FROM ben_per_in_lgl_enty_f
2485 WHERE person_id = p_person_id
2486 FOR UPDATE OF per_in_lgl_enty_id;
2487
2488 --
2489 CURSOR c43 (
2490 p_person_id NUMBER
2491 ) IS
2492 SELECT per_in_org_unit_id
2493 FROM ben_per_in_org_unit_f
2494 WHERE person_id = p_person_id
2495 FOR UPDATE OF per_in_org_unit_id;
2496
2497 --
2498 CURSOR c44 (
2499 p_person_id NUMBER
2500 ) IS
2501 SELECT per_pin_id
2502 FROM ben_per_pin_f
2503 WHERE person_id = p_person_id
2504 FOR UPDATE OF per_pin_id;
2505
2506 --
2507 CURSOR c45 (
2508 p_person_id NUMBER
2509 ) IS
2510 SELECT ptnl_ler_for_per_id
2511 FROM ben_ptnl_ler_for_per
2512 WHERE person_id = p_person_id
2513 FOR UPDATE OF ptnl_ler_for_per_id;
2514
2515 --
2516 CURSOR c46 (
2517 p_person_id NUMBER
2518 ) IS
2519 SELECT popl_org_id
2520 FROM ben_popl_org_f
2521 WHERE person_id = p_person_id
2522 FOR UPDATE OF popl_org_id;
2523
2524 --
2525 CURSOR c47 (
2526 p_person_id NUMBER
2527 ) IS
2528 SELECT ext_crit_val_id
2529 FROM ben_ext_crit_val
2530 WHERE ext_crit_val_id IN (SELECT DISTINCT ext_crit_val_id
2531 FROM ben_ext_crit_val val,
2532 ben_ext_crit_typ typ
2533 WHERE typ.crit_typ_cd =
2534 'PID'
2535 AND val.ext_crit_typ_id =
2536 typ.ext_crit_typ_id
2537 AND val.val_1 =
2538 TO_CHAR (
2542 --
2539 p_person_id
2540 ))
2541 FOR UPDATE OF ext_crit_val_id;
2543 CURSOR c48 (
2544 p_person_id NUMBER
2545 ) IS
2546 SELECT pl_bnf_id
2547 FROM ben_pl_bnf_f
2548 WHERE bnf_person_id = p_person_id
2549 FOR UPDATE OF pl_bnf_id;
2550
2551 --
2552 --Bug 4653271 For Deletion of BEn - CWB information
2553 --
2554 CURSOR c49 (
2555 p_per_in_ler_id NUMBER
2556 ) IS
2557 SELECT PIL_ELCTBL_CHC_POPL_ID
2558 FROM ben_pil_elctbl_chc_popl
2559 WHERE per_in_ler_id = p_per_in_ler_id
2560 FOR UPDATE OF PIL_ELCTBL_CHC_POPL_ID;
2561
2562
2563
2564 --
2565
2566 --
2567 CURSOR c50 (
2568 p_per_in_ler_id NUMBER
2569 ) IS
2570 SELECT ELIG_PER_ELCTBL_CHC_ID
2571 FROM BEN_ELIG_PER_ELCTBL_CHC
2572 WHERE per_in_ler_id = p_per_in_ler_id
2573 FOR UPDATE OF ELIG_PER_ELCTBL_CHC_ID;
2574
2575 --
2576 l_PIL_ELCTBL_CHC_POPL_ID Number;
2577 l_ELIG_PER_ELCTBL_CHC_ID Number;
2578 --End Bug 4653271
2579
2580 l_id NUMBER;
2581
2582 BEGIN
2583 delete_dependent_information (
2584 p_person_id
2585 );
2586 delete_communications (
2587 p_person_id
2588 );
2589 delete_life_events (
2590 p_person_id
2591 );
2592 delete_participant_information (
2593 p_person_id
2594 );
2595 delete_benefit_action_children (
2596 p_person_id
2597 );
2598 FOR r7 IN c7 LOOP
2599 DELETE FROM ben_elig_per_wv_pl_typ_f
2600 WHERE elig_per_id = r7.elig_per_id;
2601 END LOOP;
2602 FOR r9 IN c9 LOOP
2603 DELETE FROM ben_prtt_vstg_f
2604 WHERE element_entry_id = r9.element_entry_id;
2605 END LOOP;
2606 delete_reimbmt_rqst (
2607 p_person_id
2608 );
2609 --
2610 OPEN c20 (
2611 p_person_id
2612 );
2613 <<ben_batch_actn_item_info>>
2614 LOOP
2615 FETCH c20 INTO l_id;
2616 EXIT WHEN c20%NOTFOUND;
2617 DELETE FROM ben_batch_actn_item_info
2618 WHERE CURRENT OF c20;
2619 END LOOP ben_batch_actn_item_info;
2620 CLOSE c20;
2621 --
2622 OPEN c21 (
2623 p_person_id
2624 );
2625 <<ben_batch_bnft_cert_info>>
2626 LOOP
2627 FETCH c21 INTO l_id;
2628 EXIT WHEN c21%NOTFOUND;
2629 DELETE FROM ben_batch_bnft_cert_info
2630 WHERE CURRENT OF c21;
2631 END LOOP ben_batch_bnft_cert_info;
2632 CLOSE c21;
2633 --
2634 OPEN c22 (
2635 p_person_id
2636 );
2637 <<ben_batch_commu_info>>
2638 LOOP
2639 FETCH c22 INTO l_id;
2640 EXIT WHEN c22%NOTFOUND;
2641 DELETE FROM ben_batch_commu_info
2642 WHERE CURRENT OF c22;
2643 END LOOP ben_batch_commu_info;
2644 CLOSE c22;
2645 --
2646 OPEN c23 (
2647 p_person_id
2648 );
2649 <<ben_batch_dpnt_info>>
2650 LOOP
2651 FETCH c23 INTO l_id;
2652 EXIT WHEN c23%NOTFOUND;
2653 DELETE FROM ben_batch_dpnt_info
2654 WHERE CURRENT OF c23;
2655 END LOOP ben_batch_dpnt_info;
2656 CLOSE c23;
2657 --
2658 OPEN c24 (
2659 p_person_id
2660 );
2661 <<ben_batch_elctbl_chc_info>>
2662 LOOP
2663 FETCH c24 INTO l_id;
2664 EXIT WHEN c24%NOTFOUND;
2665 DELETE FROM ben_batch_elctbl_chc_info
2666 WHERE CURRENT OF c24;
2667 END LOOP ben_batch_elctbl_chc_info;
2668 CLOSE c24;
2669 OPEN c25 (
2670 p_person_id
2671 );
2672 <<ben_batch_elig_info>>
2673 LOOP
2674 FETCH c25 INTO l_id;
2675 EXIT WHEN c25%NOTFOUND;
2676 DELETE FROM ben_batch_elig_info
2677 WHERE CURRENT OF c25;
2678 END LOOP ben_batch_elig_info;
2679 CLOSE c25;
2680 OPEN c26 (
2681 p_person_id
2682 );
2683 <<ben_batch_ler_info>>
2684 LOOP
2685 FETCH c26 INTO l_id;
2686 EXIT WHEN c26%NOTFOUND;
2687 DELETE FROM ben_batch_ler_info
2688 WHERE CURRENT OF c26;
2689 END LOOP ben_batch_ler_info;
2690 CLOSE c26;
2691 OPEN c27 (
2692 p_person_id
2693 );
2694 <<ben_batch_rate_info>>
2695 LOOP
2696 FETCH c27 INTO l_id;
2697 EXIT WHEN c27%NOTFOUND;
2698 DELETE FROM ben_batch_rate_info
2699 WHERE CURRENT OF c27;
2700 END LOOP ben_batch_rate_info;
2704 );
2701 CLOSE c27;
2702 OPEN c28 (
2703 p_person_id
2705 <<ben_reporting>>
2706 LOOP
2707 FETCH c28 INTO l_id;
2708 EXIT WHEN c28%NOTFOUND;
2709 DELETE FROM ben_reporting
2710 WHERE CURRENT OF c28;
2711 END LOOP ben_reporting;
2712 CLOSE c28;
2713 OPEN c29 (
2714 p_person_id
2715 );
2716 <<ben_person_actions>>
2717 LOOP
2718 FETCH c29 INTO l_id;
2719 EXIT WHEN c29%NOTFOUND;
2720 DELETE FROM ben_person_actions
2721 WHERE CURRENT OF c29;
2722 END LOOP ben_person_actions;
2723 CLOSE c29;
2724 OPEN c30 (
2725 p_person_id
2726 );
2727 <<ben_benefit_actions>>
2728 LOOP
2729 FETCH c30 INTO l_id;
2730 EXIT WHEN c30%NOTFOUND;
2731 DELETE FROM ben_benefit_actions
2732 WHERE CURRENT OF c30;
2733 END LOOP ben_benefit_actions;
2734 CLOSE c30;
2735 OPEN c31 (
2736 p_person_id
2737 );
2738 <<ben_cbr_quald_bnf>>
2739 LOOP
2740 FETCH c31 INTO l_id;
2741 EXIT WHEN c31%NOTFOUND;
2742 DELETE FROM ben_cbr_quald_bnf
2743 WHERE CURRENT OF c31;
2744 END LOOP ben_cbr_quald_bnf;
2745 CLOSE c31;
2746 OPEN c32 (
2747 p_person_id
2748 );
2749 <<ben_crt_ordr_cvrd_per>>
2750 LOOP
2751 FETCH c32 INTO l_id;
2752 EXIT WHEN c32%NOTFOUND;
2753 DELETE FROM ben_crt_ordr_cvrd_per
2754 WHERE CURRENT OF c32;
2755 END LOOP ben_crt_ordr_cvrd_per;
2756 CLOSE c32;
2757 OPEN c33 (
2758 p_person_id
2759 );
2760 <<ben_crt_ordr>>
2761 LOOP
2762 FETCH c33 INTO l_id;
2763 EXIT WHEN c33%NOTFOUND;
2764 DELETE FROM ben_crt_ordr
2765 WHERE CURRENT OF c33;
2766 END LOOP ben_crt_ordr;
2767 CLOSE c33;
2768 OPEN c34 (
2769 p_person_id
2770 );
2771 <<ben_elig_per_f>>
2772 LOOP
2773 FETCH c34 INTO l_id;
2774 EXIT WHEN c34%NOTFOUND;
2775 DELETE FROM ben_elig_per_f
2776 WHERE CURRENT OF c34;
2777 END LOOP ben_elig_per_f;
2778 CLOSE c34;
2779 OPEN c35 (
2780 p_person_id
2781 );
2782 <<ben_ext_chg_evt_log>>
2783 LOOP
2784 FETCH c35 INTO l_id;
2785 EXIT WHEN c35%NOTFOUND;
2786 DELETE FROM ben_ext_chg_evt_log
2787 WHERE CURRENT OF c35;
2788 END LOOP ben_ext_chg_evt_log;
2789 CLOSE c35;
2790 OPEN c36 (
2791 p_person_id
2792 );
2793 <<ben_ext_rslt_dtl>>
2794 LOOP
2795 FETCH c36 INTO l_id;
2796 EXIT WHEN c36%NOTFOUND;
2797 DELETE FROM ben_ext_rslt_dtl
2798 WHERE CURRENT OF c36;
2799 END LOOP ben_ext_rslt_dtl;
2800 CLOSE c36;
2801 OPEN c37 (
2802 p_person_id
2803 );
2804 <<ben_ext_rslt_err>>
2805 LOOP
2806 FETCH c37 INTO l_id;
2807 EXIT WHEN c37%NOTFOUND;
2808 DELETE FROM ben_ext_rslt_err
2809 WHERE CURRENT OF c37;
2810 END LOOP ben_ext_rslt_err;
2811 CLOSE c37;
2812 OPEN c38 (
2813 p_person_id
2814 );
2815 <<ben_per_bnfts_bal_f>>
2816 LOOP
2817 FETCH c38 INTO l_id;
2818 EXIT WHEN c38%NOTFOUND;
2819 DELETE FROM ben_per_bnfts_bal_f
2820 WHERE CURRENT OF c38;
2821 END LOOP ben_per_bnfts_bal_f;
2822 CLOSE c38;
2823 OPEN c39 (
2824 p_person_id
2825 );
2826 <<ben_per_cm_f>>
2827 LOOP
2828 FETCH c39 INTO l_id;
2829 EXIT WHEN c39%NOTFOUND;
2830 DELETE FROM ben_per_cm_f
2831 WHERE CURRENT OF c39;
2832 END LOOP ben_per_cm_f;
2833 CLOSE c39;
2834 OPEN c40 (
2835 p_person_id
2836 );
2837 <<ben_per_dlvry_mthd_f>>
2838 LOOP
2839 FETCH c40 INTO l_id;
2840 EXIT WHEN c40%NOTFOUND;
2841 DELETE FROM ben_per_dlvry_mthd_f
2842 WHERE CURRENT OF c40;
2843 END LOOP ben_per_dlvry_mthd_f;
2844 CLOSE c40;
2845 OPEN c41 (
2846 p_person_id
2847 );
2848 <<ben_per_in_ler>>
2849 LOOP
2850 FETCH c41 INTO l_id;
2851 EXIT WHEN c41%NOTFOUND;
2852 --Bug 4653271 Deletion of BEn - CWB information
2853 DELETE FROM BEN_CWB_AUDIT WHERE group_per_in_ler_id=l_id;
2854
2855 DELETE FROM BEN_CWB_GROUP_HRCHY WHERE emp_per_in_ler_id=l_id;
2856
2857 DELETE FROM BEN_CWB_PERSON_GROUPS WHERE group_per_in_ler_id=l_id;
2858
2859 DELETE FROM BEN_CWB_PERSON_TASKS WHERE group_per_in_ler_id=l_id;
2860
2861 OPEN c49(l_id);
2862 <<ben_pil_elctbl_chc_popl>>
2863 LOOP
2867 END LOOP ben_pil_elctbl_chc_popl;
2864 FETCH c49 INTO l_pil_elctbl_chc_popl_id;
2865 EXIT WHEN c49%NOTFOUND;
2866 DELETE FROM BEN_CWB_HRCHY WHERE EMP_PIL_ELCTBL_CHC_POPL_ID = l_pil_elctbl_chc_popl_id;
2868 CLOSE c49;
2869
2870 OPEN c50(l_id);
2871 <<ben_elig_per_elctbl_chc>>
2872 LOOP
2873 FETCH c50 INTO l_elig_per_elctbl_chc_id;
2874 EXIT WHEN c50%NOTFOUND;
2875 DELETE FROM BEN_CWB_MGR_HRCHY WHERE EMP_ELIG_PER_ELCTBL_CHC_ID = l_elig_per_elctbl_chc_id;
2876 DELETE FROM BEN_CWB_MGR_HRCHY_RBV WHERE EMP_ELIG_PER_ELCTBL_CHC_ID = l_elig_per_elctbl_chc_id;
2877 END LOOP ben_elig_per_elctbl_chc;
2878 CLOSE c50;
2879 -- End Bug 4653271
2880 DELETE FROM ben_per_in_ler
2881 WHERE CURRENT OF c41;
2882 END LOOP ben_per_in_ler;
2883 CLOSE c41;
2884
2885 --Bug 4653271 Deletion of BEn - CWB information
2886 DELETE from BEN_CWB_PERSON_INFO where person_id = p_person_id;
2887
2888 DELETE from BEN_CWB_PERSON_RATES where person_id = p_person_id;
2889
2890 DELETE from BEN_CWB_SUMMARY where person_id = p_person_id;
2891 -- End Bug 4653271
2892
2893 OPEN c42 (
2894 p_person_id
2895 );
2896 <<ben_per_in_lgl_enty_f>>
2897 LOOP
2898 FETCH c42 INTO l_id;
2899 EXIT WHEN c42%NOTFOUND;
2900 DELETE FROM ben_per_in_lgl_enty_f
2901 WHERE CURRENT OF c42;
2902 END LOOP ben_per_in_lgl_enty_f;
2903 CLOSE c42;
2904 OPEN c43 (
2905 p_person_id
2906 );
2907 <<ben_per_in_org_unit_f>>
2908 LOOP
2909 FETCH c43 INTO l_id;
2910 EXIT WHEN c43%NOTFOUND;
2911 DELETE FROM ben_per_in_org_unit_f
2912 WHERE CURRENT OF c43;
2913 END LOOP ben_per_in_org_unit_f;
2914 CLOSE c43;
2915
2916 OPEN c44 (
2917 p_person_id
2918 );
2919 <<ben_per_pin_f>>
2920 LOOP
2921 FETCH c44 INTO l_id;
2922 EXIT WHEN c44%NOTFOUND;
2923 DELETE FROM ben_per_pin_f
2924 WHERE CURRENT OF c44;
2925 END LOOP ben_per_pin_f;
2926 CLOSE c44;
2927
2928 OPEN c45 (
2929 p_person_id
2930 );
2931 <<ben_ptnl_ler_for_per>>
2932 LOOP
2933 FETCH c45 INTO l_id;
2934 EXIT WHEN c45%NOTFOUND;
2935 DELETE FROM ben_ptnl_ler_for_per
2936 WHERE CURRENT OF c45;
2937 END LOOP ben_ptnl_ler_for_per;
2938 CLOSE c45;
2939 OPEN c46 (
2940 p_person_id
2941 );
2942 <<ben_popl_org_f>>
2943 LOOP
2944 FETCH c46 INTO l_id;
2945 EXIT WHEN c46%NOTFOUND;
2946 DELETE FROM ben_popl_org_f
2947 WHERE CURRENT OF c46;
2948 END LOOP ben_popl_org_f;
2949 CLOSE c46;
2950 OPEN c47 (
2951 p_person_id
2952 );
2953 <<ben_ext_crit_val>>
2954 LOOP
2955 FETCH c47 INTO l_id;
2956 EXIT WHEN c47%NOTFOUND;
2957 DELETE FROM ben_ext_crit_val
2958 WHERE CURRENT OF c47;
2959 END LOOP ben_ext_crit_val;
2960 CLOSE c47;
2961
2962 OPEN c48 (
2963 p_person_id
2964 );
2965 <<ben_pl_bnf_f>>
2966 LOOP
2967 FETCH c48 INTO l_id;
2968 EXIT WHEN c48%NOTFOUND;
2969 DELETE FROM ben_pl_bnf_f
2970 WHERE CURRENT OF c48;
2971 END LOOP ben_pl_bnf_f;
2972 CLOSE c48;
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 RAISE;
2977 END delete_ben_rows;
2978
2979 PROCEDURE check_ben_rows_before_delete(
2980 p_person_id number ,
2981 p_effective_date date
2982 ) is
2983 --
2984 l_proc varchar2(200) := 'ben_person_delete.check_ben_rows_before_delete' ;
2985 /* Bug 7339987 : Modified c_pil,c_pen,c_prv,c_ecd,c_ecdpn,c_plbnf cursors to
2986 Allow Delete when UnRestricted LE is in STRTD state
2987 Do Not Allow Delete when Other types of LE is in STRTD state
2988 Do Not Allow Delete when Other types of LE is in PROCD state
2989 Allow Delete when Other types of LE is in BCKDT/VOIDD state
2990 */
2991
2992 --cursor for the pils with STRTD status
2993
2994 CURSOR c_pil( p_person_id number ,
2995 p_effective_date date
2996 ) is
2997 SELECT 'Y'
2998 FROM ben_per_in_ler pil,
2999 ben_ler_f ler
3000 WHERE pil.person_id = p_person_id
3001 AND pil.per_in_ler_stat_cd = 'STRTD'
3002 AND pil.ler_id = ler.ler_id
3003 AND pil.business_group_id = ler.business_group_id
3004 AND p_effective_date between ler.effective_start_date and
3005 ler.effective_end_date
3006 AND ler.typ_cd <> 'SCHEDDU' ;
3007 --
3008 -- cursor to get active pen records
3009 --
3010 CURSOR c_pen( p_person_id number ,
3011 p_effective_date date
3012 ) is
3016 WHERE pen.person_id=p_person_id and
3013 SELECT 'Y'
3014 FROM ben_prtt_enrt_rslt_f pen,
3015 ben_ler_f ler
3017 pen.prtt_enrt_rslt_stat_cd is null and
3018 -- pen.sspndd_flag='N' and Needs to resolve suspended record also
3019 pen.effective_end_date = hr_api.g_eot and
3020 p_effective_date between pen.enrt_cvg_strt_dt and
3021 pen.enrt_cvg_thru_dt
3022 and ler.ler_id=pen.ler_id
3023 AND pen.business_group_id = ler.business_group_id
3024 AND p_effective_date between ler.effective_start_date and
3025 ler.effective_end_date
3026 AND ler.typ_cd <> 'SCHEDDU';
3027 --
3028 -- cursor to get active rate records
3029 --
3030 CURSOR c_prv( p_person_id number ,
3031 p_effective_date date
3032 ) is
3033 SELECT 'Y'
3034 FROM ben_prtt_rt_val prv,
3035 ben_prtt_enrt_rslt_f pen,
3036 ben_ler_f ler
3037 WHERE
3038 pen.person_id=p_person_id and
3039 prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
3040 pen.prtt_enrt_rslt_stat_cd is null and
3041 p_effective_date between prv.rt_strt_dt and prv.rt_end_dt and
3042 prv.business_group_id = pen.business_group_id and
3043 prv.prtt_rt_val_stat_cd is null
3044 and ler.ler_id=pen.ler_id
3045 AND pen.business_group_id = ler.business_group_id
3046 AND p_effective_date between ler.effective_start_date and
3047 ler.effective_end_date
3048 AND ler.typ_cd <> 'SCHEDDU';
3049 --
3050 -- cursor to get active dependents
3051 --
3052 CURSOR c_ecd( p_person_id number ,
3053 p_effective_date date
3054 ) is
3055 SELECT 'Y'
3056 FROM ben_elig_cvrd_dpnt_f ecd,
3057 ben_per_in_ler pil,
3058 ben_ler_f ler,
3059 ben_prtt_enrt_rslt_f pen
3060 WHERE
3061 pil.person_id=p_person_id and
3062 pil.per_in_ler_id = ecd.per_in_ler_id and
3063 pil.business_group_id = ecd.business_group_id and
3064 p_effective_date between ecd.cvg_strt_dt and cvg_thru_dt and
3065 ecd.effective_end_date = hr_api.g_eot
3066 and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
3067 pen.prtt_enrt_rslt_stat_cd is null
3068 AND pil.ler_id = ler.ler_id
3069 AND pil.business_group_id = ler.business_group_id
3070 AND p_effective_date between ler.effective_start_date and
3071 ler.effective_end_date
3072 AND ler.typ_cd <> 'SCHEDDU';
3073 --
3074 -- if the person is dpnt then validate the cvrd dpnt
3075 --
3076 CURSOR c_ecdpn( p_person_id number ,
3077 p_effective_date date
3078 ) is
3079 SELECT 'Y'
3080 FROM ben_elig_cvrd_dpnt_f ecd,
3081 ben_per_in_ler pil,
3082 ben_ler_f ler,
3083 ben_prtt_enrt_rslt_f pen
3084 WHERE
3085 ecd.dpnt_person_id = p_person_id and
3086 p_effective_date between ecd.cvg_strt_dt and cvg_thru_dt and
3087 ecd.effective_end_date = hr_api.g_eot
3088 and pil.per_in_ler_id = ecd.per_in_ler_id
3089 and pil.business_group_id = ecd.business_group_id
3090 and ecd.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
3091 pen.prtt_enrt_rslt_stat_cd is null
3092 AND pil.ler_id = ler.ler_id
3093 AND pil.business_group_id = ler.business_group_id
3094 AND p_effective_date between ler.effective_start_date and
3095 ler.effective_end_date
3096 AND ler.typ_cd <> 'SCHEDDU';
3097
3098 -- if the person is dpnt and only beneficiary
3099 CURSOR c_plbnf( p_person_id number ,
3100 p_effective_date date
3101 ) is
3102 SELECT 'Y'
3103 FROM ben_pl_bnf_f pbn,
3104 ben_per_in_ler pil,
3105 ben_ler_f ler,
3106 ben_prtt_enrt_rslt_f pen
3107 WHERE
3108 pbn.bnf_person_id = p_person_id and
3109 p_effective_date between pbn.dsgn_strt_dt and pbn.dsgn_thru_dt and
3110 pbn.effective_end_date = hr_api.g_eot
3111 and pbn.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
3112 pen.prtt_enrt_rslt_stat_cd is null
3113 and pil.per_in_ler_id=pbn.per_in_ler_id
3114 AND pil.business_group_id = pbn.business_group_id
3115 AND pil.ler_id = ler.ler_id
3116 AND pil.business_group_id = ler.business_group_id
3117 AND p_effective_date between ler.effective_start_date and
3118 ler.effective_end_date
3119 AND ler.typ_cd <> 'SCHEDDU';
3120
3121
3122
3123 l_dummy varchar2(1) := 'N' ;
3124 BEGIN
3125 hr_utility.set_location('Entering '||l_proc , 10);
3126 --check for per_in_ler in started status. Exclude the unrestricted cases
3127 open c_pil(p_person_id,p_effective_date) ;
3128 --
3129 fetch c_pil into l_dummy ;
3130 if c_pil%found then
3131 --
3132 hr_utility.set_location('Started Pil exist for person '||p_person_id , 20);
3133 close c_pil ;
3134 fnd_message.set_name('BEN', 'BEN_92720_BEN_DATA_EXISTS');
3135 fnd_message.set_token('BEN_ITEM','Life Event');
3136 fnd_message.raise_error;
3137 --
3138 end if;
3139 close c_pil ;
3140 --check for active enrollment result records
3141 open c_pen(p_person_id,p_effective_date);
3142 --
3143 fetch c_pen into l_dummy ;
3144 if c_pen%found then
3145 --
3146 hr_utility.set_location('Started pen exist for person '||p_person_id , 30);
3147 close c_pen ;
3148 fnd_message.set_name('BEN', 'BEN_92720_BEN_DATA_EXISTS');
3149 fnd_message.set_token('BEN_ITEM','Enrollment');
3150 fnd_message.raise_error;
3151 --
3152 end if;
3153 close c_pen ;
3154 --check for active rate records
3155 open c_prv(p_person_id,p_effective_date) ;
3156 --
3157 fetch c_prv into l_dummy ;
3158 if c_prv%found then
3159 --
3160 hr_utility.set_location('Started prv exist for person '||p_person_id , 40);
3161 close c_prv ;
3162 fnd_message.set_name('BEN', 'BEN_92720_BEN_DATA_EXISTS');
3163 fnd_message.set_token('BEN_ITEM','Rate');
3164 fnd_message.raise_error;
3165 --
3166 end if;
3167 close c_prv ;
3168 --check for active dependents
3169 open c_ecd(p_person_id,p_effective_date) ;
3170 --
3171 fetch c_ecd into l_dummy ;
3172 if c_ecd%found then
3173 --
3174 hr_utility.set_location('Started ecd exist for person '||p_person_id , 50);
3175 close c_ecd ;
3176 fnd_message.set_name('BEN', 'BEN_92720_BEN_DATA_EXISTS');
3177 fnd_message.set_token('BEN_ITEM','Dependents');
3178 fnd_message.raise_error;
3179 --
3180 end if;
3181 close c_ecd ;
3182 --
3183 --check whether the operson coverd as dpnt
3184 open c_ecdpn(p_person_id,p_effective_date) ;
3185 --
3186 fetch c_ecdpn into l_dummy ;
3187 if c_ecdpn%found then
3188 --
3189 hr_utility.set_location('Started ecdpn exist for person '||p_person_id , 50);
3190 close c_ecdpn ;
3191 fnd_message.set_name('BEN', 'BEN_93910_DPTN_CVRD');
3192 fnd_message.raise_error;
3193 --
3194 end if;
3195 close c_ecdpn ;
3196
3197 --check whether the operson coverd as beneficiary
3198 open c_plbnf(p_person_id,p_effective_date) ;
3199 --
3200 fetch c_plbnf into l_dummy ;
3201 if c_plbnf%found then
3202 --
3203 hr_utility.set_location('Started ecdpn exist for person '||p_person_id , 50);
3204 close c_plbnf ;
3205 fnd_message.set_name('BEN', 'BEN_93911_DPT_DESIG_BNF');
3206 fnd_message.raise_error;
3207 --
3208 end if;
3209 close c_plbnf ;
3210
3211
3212 --
3213 hr_utility.set_location('Leaving '||l_proc , 100);
3214 --
3215 EXCEPTION
3216 WHEN OTHERS THEN
3217 RAISE;
3218 END check_ben_rows_before_delete ;
3219 --
3220 END ben_person_delete;
[Home] [Help]