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