[Home] [Help]
PACKAGE BODY: APPS.BEN_EFC_ADJUSTMENTS
Source
1 package body ben_efc_adjustments as
2 /* $Header: beefcadj.pkb 115.27 2003/02/14 01:58:10 kmahendr noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 31-Jan-01 mhoyes Created.
15 115.1 01-Feb-01 mhoyes Added PRV and EEV adjustment covers.
16 115.2 06-Apr-01 mhoyes Total revamp for patchset D.
17 115.6 30-May-01 mhoyes Leapfrogged 115.4 and re-applied
18 changes in 115.5.
19 - Enhanced for Patchset E.
20 115.7 26-Jul-01 mhoyes Enhanced for Patchset E+ patch.
21 115.8 13-Aug-01 mhoyes Enhanced for Patchset E+ patch.
22 115.9 14-Aug-01 mhoyes ECR tuning.
23 115.10 17-Aug-01 mhoyes Enhanced for BEN July patch.
24 115.11 27-Aug-01 mhoyes Enhanced for BEN July patch.
25 115.12 31-Aug-01 mhoyes Enhanced for BEN July patch.
26 115.13 13-Sep-01 mhoyes Enhanced for BEN July patch.
27 115.14 19-Sep-01 mhoyes - Backed out nocopy autonomous transaction
28 because of deadlock. Could be
29 8.1.7.1 bug.
30 115.15 02-Oct-01 mhoyes Enhanced for BEN F patchset.
31 115.19 04-Jan-02 mhoyes Enhanced for BEN G patchset.
32 115.21 23-May-02 kmahendr Added a parameter to ben_determine_acty_base_rt
33 115.22 03-Jun-02 pabodla Bug 2367556 : Changed STANDARD.bitand to just bitand
34 115.23 03-Jun-02 pabodla Added SET VERIFY OFF
35 115.24 08-Jun-02 pabodla Do not select the contingent worker
36 assignment when assignment data is
37 fetched.
38 115.25 11-Oct-02 vsethi Rates Sequence no enhancements. Modified to cater
39 to new column ord_num on ben_acty_base_rt_f
40 115.26 30-Dec-2002 mmudigon NOCOPY
41 115.27 13-feb-2003 kmahendr Added a parameter to call -acty_base_rt.main
42 -----------------------------------------------------------------------------
43 */
44 --
45 -- Globals.
46 --
47 g_package varchar2(50) := 'ben_efc_adjustments.';
48 --
49 procedure DetectAppError
50 (p_sqlerrm in varchar2
51 ,p_abr_rt_mlt_cd in varchar2 default null
52 ,p_abr_val in number default null
53 ,p_abr_entr_val_at_enrt_flag in varchar2 default null
54 ,p_abr_id in number default null
55 ,p_eff_date in date default null
56 ,p_penepe_id in number default null
57 --
58 ,p_faterr_code out nocopy varchar2
59 ,p_faterr_type out nocopy varchar2
60 )
61 is
62 --
63 cursor c_parntabr
64 (c_abr_id number
65 ,c_eff_date date
66 )
67 is
68 select abr2.acty_base_rt_id,
69 abr2.entr_val_at_enrt_flag,
70 abr2.val
71 from ben_acty_base_rt_f abr,
72 ben_acty_base_rt_f abr2
73 where abr.acty_base_rt_id = c_abr_id
74 and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
75 and abr2.parnt_chld_cd = 'PARNT'
76 and c_eff_date
77 between abr.effective_start_date
78 and abr.effective_end_date
79 and c_eff_date
80 between abr2.effective_start_date
81 and abr2.effective_end_date;
82 --
83 l_parntabr c_parntabr%rowtype;
84 --
85 cursor c_epedets
86 (c_epe_id number
87 )
88 is
89 select epe.pgm_id,
90 epe.pl_id
91 from ben_elig_per_elctbl_chc epe
92 where epe.elig_per_elctbl_chc_id = c_epe_id;
93 --
94 l_epedets c_epedets%rowtype;
95 --
96 cursor c_plndets
97 (c_pln_id number
98 ,c_eff_date date
99 )
100 is
101 select pln.pl_id
102 from ben_pl_f pln
103 where pln.pl_id = c_pln_id
104 and c_eff_date
105 between pln.effective_start_date
106 and pln.effective_end_date;
107 --
108 l_plndets c_plndets%rowtype;
109 --
110 begin
111 --
112 if instr(p_sqlerrm,'91834') > 0
113 then
114 --
115 if p_abr_rt_mlt_cd is null
116 then
117 --
118 p_faterr_code := 'ABRMCNULL';
119 p_faterr_type := 'MISSINGSETUP';
120 --
121 elsif (p_abr_val is null and p_abr_entr_val_at_enrt_flag = 'N')
122 then
123 --
124 p_faterr_code := 'ABRVALNULLEVAEFN';
125 p_faterr_type := 'MISSINGSETUP';
126 --
127 else
128 --
129 -- Check for a parent activity base rate
130 --
131 open c_parntabr
132 (c_abr_id => p_abr_id
133 ,c_eff_date => p_eff_date
134 );
135 fetch c_parntabr into l_parntabr;
136 if c_parntabr%found then
137 --
138 if l_parntabr.val is null and l_parntabr.entr_val_at_enrt_flag = 'N'
139 then
140 --
141 p_faterr_code := 'CHILDABRVALNULLEVAEFN';
142 p_faterr_type := 'MISSINGSETUP';
143 --
144 end if;
145 --
146 end if;
147 close c_parntabr;
148 --
149 end if;
150 --
151 elsif instr(p_sqlerrm,'92411') > 0
152 then
153 --
154 -- Get EPE dets
155 --
156 open c_epedets
157 (c_epe_id => p_penepe_id
158 );
159 fetch c_epedets into l_epedets;
160 if c_epedets%found then
161 --
162 -- Check that the EPE PGMID is null and the PLNID is not null
163 --
164 if l_epedets.pgm_id is null
165 and l_epedets.pl_id is not null
166 then
167 --
168 open c_plndets
169 (c_pln_id => l_epedets.pl_id
170 ,c_eff_date => p_eff_date
171 );
172 fetch c_plndets into l_plndets;
173 if c_plndets%notfound then
174 --
175 p_faterr_code := 'EPEPLNNIPNOEXIST';
176 p_faterr_type := 'DELETEDINFO';
177 --
178 end if;
179 close c_plndets;
180 --
181 end if;
182 --
183 else
184 --
185 p_faterr_code := 'PENEPENOEXIST';
186 p_faterr_type := 'DELETEDINFO';
187 --
188 end if;
189 close c_epedets;
190 --
191 else
192 --
193 p_faterr_code := null;
194 p_faterr_type := null;
195 --
196 end if;
197 --
198 end DetectAppError;
199 --
200 procedure DetectWhoInfo
201 (p_creation_date in date
202 ,p_last_update_date in date
203 ,p_object_version_number in number
204 --
205 ,p_who_counts in out nocopy g_who_counts
206 ,p_faterr_code out nocopy varchar2
207 ,p_faterr_type out nocopy varchar2
208 )
209 is
210 --
211 l_gap number;
212 l_faterr_type varchar2(100);
213 --
214 begin
215 --
216 p_who_counts.olddata := FALSE;
217 p_who_counts.olddata12mths := FALSE;
218 p_who_counts.multtransmod := FALSE;
219 --
220 -- Check for old data
221 --
222 if p_who_counts.olddata_count is null then
223 --
224 p_who_counts.olddata_count := 0;
225 p_who_counts.mod_count := 0;
226 p_who_counts.modovn1_count := 0;
227 p_who_counts.modovn2_count := 0;
228 p_who_counts.modovn3_count := 0;
229 p_who_counts.modovn4_count := 0;
230 p_who_counts.modovn5_count := 0;
231 p_who_counts.modovn6_count := 0;
232 p_who_counts.modovnov6_count := 0;
233 p_who_counts.multtransmod_count := 0;
234 --
235 end if;
236 --
237 if nvl(p_creation_date,hr_api.g_sot)
238 <> nvl(p_last_update_date,hr_api.g_sot)
239 then
240 --
241 p_who_counts.mod_count := p_who_counts.mod_count+1;
242 --
243 -- Check for multiple transactions gap more that 1 min
244 --
245 l_gap := (p_last_update_date-p_creation_date)*(24*60);
246 --
247 if l_gap > 1 then
248 --
249 p_who_counts.multtransmod_count := p_who_counts.multtransmod_count+1;
250 p_who_counts.multtransmod := TRUE;
251 --
252 end if;
253 --
254 -- Check ENB OVN
255 --
256 if p_object_version_number = 1 then
257 --
258 p_who_counts.modovn1_count := p_who_counts.modovn1_count+1;
259 --
260 elsif p_object_version_number = 2 then
261 --
262 p_who_counts.modovn2_count := p_who_counts.modovn2_count+1;
263 --
264 elsif p_object_version_number = 3 then
265 --
266 p_who_counts.modovn3_count := p_who_counts.modovn3_count+1;
267 --
268 elsif p_object_version_number = 4 then
269 --
270 p_who_counts.modovn4_count := p_who_counts.modovn4_count+1;
271 --
272 elsif p_object_version_number = 5 then
273 --
274 p_who_counts.modovn5_count := p_who_counts.modovn5_count+1;
275 --
276 elsif p_object_version_number = 6 then
277 --
278 p_who_counts.modovn6_count := p_who_counts.modovn6_count+1;
279 --
280 elsif p_object_version_number > 6 then
281 --
282 p_who_counts.modovnov6_count := p_who_counts.modovnov6_count+1;
283 --
284 end if;
285 --
286 end if;
287 --
288 l_faterr_type := 'OBSOLETEDATA';
289 --
290 if p_creation_date is null then
291 --
292 p_who_counts.olddata_count := p_who_counts.olddata_count+1;
293 p_who_counts.olddata := TRUE;
294 p_faterr_code := 'NULLCREDT';
295 p_faterr_type := l_faterr_type;
296 --
297 /*
298 elsif p_creation_date
299 < sysdate-365
300 then
301 --
302 p_who_counts.olddata_count := p_who_counts.olddata_count+1;
303 p_who_counts.olddata := TRUE;
304 p_faterr_code := 'OLDDATA12MTHS';
305 p_faterr_type := l_faterr_type;
306 --
307 */
308 else
309 --
310 p_who_counts.olddata := FALSE;
311 p_faterr_code := null;
312 p_faterr_type := null;
313 --
314 end if;
315 --
316 end DetectWhoInfo;
317 --
318 procedure DetectPILInfo
319 (p_person_id in number
320 ,p_per_in_ler_id in number
321 --
322 ,p_faterr_code out nocopy varchar2
323 )
324 is
325 --
326 cursor c_maxpildets
327 (c_person_id in number
328 )
329 is
330 select pil.per_in_ler_id
331 from ben_per_in_ler pil
332 where pil.person_id = c_person_id
333 order by pil.per_in_ler_id desc;
334 --
335 l_maxpildets c_maxpildets%rowtype;
336 --
337 l_pil_count pls_integer;
338 --
339 begin
340 --
341 l_pil_count := 0;
342 --
343 for row in c_maxpildets
344 (c_person_id => p_person_id
345 )
346 loop
347 --
348 if row.per_in_ler_id = p_per_in_ler_id then
349 --
350 if l_pil_count = 0 then
351 --
352 p_faterr_code := null;
353 --
354 elsif l_pil_count = 1 then
355 --
356 p_faterr_code := 'HISTPIL1';
357 --
358 elsif l_pil_count = 2 then
359 --
360 p_faterr_code := 'HISTPIL2';
361 --
362 elsif l_pil_count = 3 then
363 --
364 p_faterr_code := 'HISTPIL3';
365 --
366 elsif l_pil_count = 4 then
367 --
368 p_faterr_code := 'HISTPIL4';
369 --
370 elsif l_pil_count = 5 then
371 --
372 p_faterr_code := 'HISTPIL5';
373 --
374 elsif l_pil_count = 6 then
375 --
376 p_faterr_code := 'HISTPIL6';
377 --
378 elsif l_pil_count = 7 then
379 --
380 p_faterr_code := 'HISTPIL7';
381 --
382 elsif l_pil_count = 8 then
383 --
384 p_faterr_code := 'HISTPIL8';
385 --
386 elsif l_pil_count = 9 then
387 --
388 p_faterr_code := 'HISTPIL9';
389 --
390 else
391 --
392 p_faterr_code := 'HISTPIL';
393 --
394 end if;
395 --
396 return;
397 --
398 end if;
399 --
400 l_pil_count := l_pil_count+1;
401 --
402 end loop;
403 --
404 if l_pil_count = 0 then
405 --
406 p_faterr_code := 'NOPERPILS';
407 return;
408 --
409 end if;
410 --
411 end DetectPILInfo;
412 --
413 procedure DetectBCOLRowInfo
414 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
415 ,p_effective_date in date
416 ,p_business_group_id in number
417 --
418 ,p_faterr_code out nocopy varchar2
419 )
420 is
421 --
422 l_elig_rec ben_derive_part_and_rate_cache.g_cache_clf_rec_obj;
423 l_rate_rec ben_derive_part_and_rate_cache.g_cache_clf_rec_obj;
424 --
425 begin
426 --
427 -- Check that the comp object details were found
428 --
429 if p_comp_obj_tree_row.trk_inelig_per_flag is null then
430 --
431 p_faterr_code := 'NOMATCO';
432 return;
433 --
434 else
435 --
436 p_faterr_code := null;
437 --
438 end if;
439 --
440 -- Check that attached DFs exist
441 --
442 if p_comp_obj_tree_row.flag_bit_val = 0
443 and p_comp_obj_tree_row.oiplip_flag_bit_val = 0
444 then
445 --
446 p_faterr_code := 'NOATTDF';
447 return;
448 --
449 else
450 --
451 p_faterr_code := null;
452 --
453 end if;
454 --
455 -- Check for a comp DF
456 --
457 IF (bitand(p_comp_obj_tree_row.flag_bit_val
458 ,ben_manage_life_events.g_cmp_flag) <> 0)
459 THEN
460 --
461 ben_derive_part_and_rate_cache.get_comp_elig
462 (p_pgm_id => p_comp_obj_tree_row.pgm_id
463 ,p_pl_id => p_comp_obj_tree_row.pl_id
464 ,p_oipl_id => p_comp_obj_tree_row.oipl_id
465 ,p_plip_id => p_comp_obj_tree_row.plip_id
466 ,p_ptip_id => p_comp_obj_tree_row.ptip_id
467 ,p_business_group_id => p_business_group_id
468 ,p_effective_date => p_effective_date
469 ,p_rec => l_elig_rec
470 );
471 --
472 p_faterr_code := null;
473 --
474 elsif (bitand(p_comp_obj_tree_row.flag_bit_val
475 ,ben_manage_life_events.g_cmp_rt_flag) <> 0)
476 OR
477 (p_comp_obj_tree_row.oiplip_id IS NOT NULL
478 AND bitand(p_comp_obj_tree_row.oiplip_flag_bit_val
479 ,ben_manage_life_events.g_cmp_rt_flag) <> 0)
480 THEN
481 --
482 ben_derive_part_and_rate_cache.get_comp_rate
483 (p_pgm_id => p_comp_obj_tree_row.pgm_id
484 ,p_pl_id => p_comp_obj_tree_row.pl_id
485 ,p_oipl_id => p_comp_obj_tree_row.oipl_id
486 ,p_plip_id => p_comp_obj_tree_row.plip_id
487 ,p_ptip_id => p_comp_obj_tree_row.ptip_id
488 ,p_oiplip_id => p_comp_obj_tree_row.oiplip_id
489 ,p_business_group_id => p_business_group_id
490 ,p_effective_date => p_effective_date
491 ,p_rec => l_rate_rec
492 );
493 --
494 p_faterr_code := null;
495 --
496 else
497 --
498 p_faterr_code := 'NOCMPATTDF';
499 return;
500 --
501 end if;
502 --
503 end DetectBCOLRowInfo;
504 --
505 procedure DetectEPEENBInfo
506 (p_elig_per_elctbl_chc_id in number
507 ,p_enrt_bnft_id in number
508 --
509 ,p_detect_mode in varchar2 default null
510 --
511 ,p_currpil_row out nocopy g_pil_rowtype
512 ,p_currepe_row out nocopy ben_determine_rates.g_curr_epe_rec
513 ,p_faterr_code out nocopy varchar2
514 ,p_faterr_type out nocopy varchar2
515 )
516 is
517 --
518 l_tmpepe_row ben_epe_cache.g_pilepe_inst_row;
519 l_currepe_row ben_determine_rates.g_curr_epe_rec;
520 --
521 cursor c_epedets
522 (c_epe_id in number
523 )
524 is
525 select pil.lf_evt_ocrd_dt,
526 pil.person_id,
527 pil.per_in_ler_id,
528 pil.business_group_id,
529 pil.ler_id,
530 pil.per_in_ler_stat_cd
531 from BEN_ELIG_PER_ELCTBL_CHC epe,
532 ben_per_in_ler pil,
533 per_all_people_f per
534 where pil.per_in_ler_id = epe.per_in_ler_id
535 and epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
536 and per.person_id = pil.person_id
537 and pil.lf_evt_ocrd_dt
538 between per.effective_start_date and per.effective_end_date;
539 --
540 l_epedets c_epedets%rowtype;
541 --
542 cursor c_enbdets
543 (c_enb_id in number
544 )
545 is
546 select pil.lf_evt_ocrd_dt,
547 pil.person_id,
548 pil.per_in_ler_id,
549 pil.business_group_id,
550 pil.ler_id,
551 pil.per_in_ler_stat_cd,
552 enb.val
553 from ben_enrt_bnft enb,
554 BEN_ELIG_PER_ELCTBL_CHC epe,
555 ben_per_in_ler pil,
556 per_all_people_f per
557 where enb.ELIG_PER_ELCTBL_CHC_id = epe.ELIG_PER_ELCTBL_CHC_id
558 and pil.per_in_ler_id = epe.per_in_ler_id
559 and enb.enrt_bnft_id = c_enb_id
560 and per.person_id = pil.person_id
561 and pil.lf_evt_ocrd_dt
562 between per.effective_start_date and per.effective_end_date;
563 --
564 l_enbdets c_enbdets%rowtype;
565 --
566 cursor c_enbnoperdets
567 (c_enb_id in number
568 )
569 is
570 select pil.lf_evt_ocrd_dt,
571 pil.person_id,
572 pil.per_in_ler_id,
573 pil.business_group_id,
574 pil.ler_id,
575 pil.per_in_ler_stat_cd,
576 enb.val
577 from ben_enrt_bnft enb,
578 BEN_ELIG_PER_ELCTBL_CHC epe,
579 ben_per_in_ler pil
580 where enb.ELIG_PER_ELCTBL_CHC_id = epe.ELIG_PER_ELCTBL_CHC_id
581 and pil.per_in_ler_id = epe.per_in_ler_id
582 and enb.enrt_bnft_id = c_enb_id;
583 --
584 l_enbnoperdets c_enbnoperdets%rowtype;
585 --
586 begin
587 /*
588 --
589 -- Check if the ENB id and EPE id are both set exists
590 --
591 if p_elig_per_elctbl_chc_id is not null
592 and p_enrt_bnft_id is not null
593 then
594 --
595 p_faterr_code := 'EPEANDENBSET';
596 p_faterr_type := 'POTENTIALCODEBUG';
597 return;
598 --
599 end if;
600 */
601 --
602 -- Get EPE details
603 --
604 if p_elig_per_elctbl_chc_id is not null
605 then
606 --
607 open c_epedets
608 (c_epe_id => p_elig_per_elctbl_chc_id
609 );
610 fetch c_epedets into l_epedets;
611 if c_epedets%notfound then
612 --
613 p_faterr_code := 'NOEPEDETS';
614 p_faterr_type := 'DELETEDINFO';
615 close c_epedets;
616 return;
617 --
618 end if;
619 close c_epedets;
620 --
621 p_currpil_row.per_in_ler_id := l_epedets.per_in_ler_id;
622 p_currpil_row.person_id := l_epedets.person_id;
623 p_currpil_row.lf_evt_ocrd_dt := l_epedets.lf_evt_ocrd_dt;
624 --
625 if l_epedets.per_in_ler_stat_cd in ('VOIDD','BCKDT')
626 then
627 --
628 p_faterr_code := 'VOIDBACKPIL';
629 p_faterr_type := 'VALIDEXCLUSION';
630 return;
631 --
632 end if;
633 --
634 if nvl(p_detect_mode,'ZZZ') = 'EPEINFO' then
635 --
636 ben_epe_cache.EPE_GetEPEDets
637 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
638 ,p_per_in_ler_id => l_epedets.per_in_ler_id
639 ,p_inst_row => l_tmpepe_row
640 );
641 --
642 end if;
643 --
644 elsif p_enrt_bnft_id is not null then
645 --
646 open c_enbdets
647 (c_enb_id => p_enrt_bnft_id
648 );
649 fetch c_enbdets into l_enbdets;
650 if c_enbdets%notfound then
651 --
652 p_faterr_code := 'NOENBEPEDETS';
653 p_faterr_type := 'POTENTIALCODEBUG';
654 --
655 close c_enbdets;
656 --
657 -- Check if the person exists for the life event
658 --
659 open c_enbnoperdets
660 (c_enb_id => p_enrt_bnft_id
661 );
662 fetch c_enbnoperdets into l_enbnoperdets;
663 if c_enbnoperdets%found then
664 --
665 p_faterr_code := 'NOPILPERSON';
666 p_faterr_type := 'DELETEDINFO';
667 --
668 end if;
669 close c_enbnoperdets;
670 --
671 return;
672 --
673 end if;
674 close c_enbdets;
675 --
676 p_currpil_row.per_in_ler_id := l_enbdets.per_in_ler_id;
677 p_currpil_row.person_id := l_enbdets.person_id;
678 p_currpil_row.lf_evt_ocrd_dt := l_enbdets.lf_evt_ocrd_dt;
679 --
680 if l_epedets.per_in_ler_stat_cd in ('VOIDD','BCKDT')
681 then
682 --
683 p_faterr_code := 'VOIDBACKPIL';
684 p_faterr_type := 'VALIDEXCLUSION';
685 return;
686 --
687 end if;
688 --
689 if nvl(p_detect_mode,'ZZZ') = 'EPEINFO' then
690 --
691 ben_epe_cache.ENBEPE_GetEPEDets
692 (p_enrt_bnft_id => p_enrt_bnft_id
693 ,p_per_in_ler_id => l_enbdets.per_in_ler_id
694 ,p_inst_row => l_tmpepe_row
695 );
696 --
697 end if;
698 --
699 end if;
700 --
701 -- Check if EPE details were found
702 --
703 if l_tmpepe_row.elig_per_elctbl_chc_id is null
704 and nvl(p_detect_mode,'ZZZ') = 'EPEINFO'
705 then
706 --
707 p_faterr_code := 'EPEENBNOEPE';
708 p_faterr_type := 'POTENTIALCODEBUG';
709 return;
710 --
711 end if;
712 --
713 l_currepe_row.elig_per_elctbl_chc_id := l_tmpepe_row.elig_per_elctbl_chc_id;
714 l_currepe_row.business_group_id := l_tmpepe_row.business_group_id;
715 l_currepe_row.person_id := l_tmpepe_row.person_id;
716 l_currepe_row.ler_id := l_tmpepe_row.ler_id;
717 l_currepe_row.lf_evt_ocrd_dt := l_tmpepe_row.lf_evt_ocrd_dt;
718 l_currepe_row.per_in_ler_id := l_tmpepe_row.per_in_ler_id;
719 l_currepe_row.enrt_bnft_id := l_tmpepe_row.enrt_bnft_id;
720 l_currepe_row.pgm_id := l_tmpepe_row.pgm_id;
721 l_currepe_row.pl_typ_id := l_tmpepe_row.pl_typ_id;
722 l_currepe_row.ptip_id := l_tmpepe_row.ptip_id;
723 l_currepe_row.plip_id := l_tmpepe_row.plip_id;
724 l_currepe_row.pl_id := l_tmpepe_row.pl_id;
725 l_currepe_row.oipl_id := l_tmpepe_row.oipl_id;
726 l_currepe_row.oiplip_id := l_tmpepe_row.oiplip_id;
727 l_currepe_row.opt_id := l_tmpepe_row.opt_id;
728 l_currepe_row.enrt_perd_id := l_tmpepe_row.enrt_perd_id;
729 l_currepe_row.lee_rsn_id := l_tmpepe_row.lee_rsn_id;
730 l_currepe_row.enrt_perd_strt_dt := l_tmpepe_row.enrt_perd_strt_dt;
731 l_currepe_row.prtt_enrt_rslt_id := l_tmpepe_row.prtt_enrt_rslt_id;
732 l_currepe_row.prtn_strt_dt := l_tmpepe_row.prtn_strt_dt;
733 l_currepe_row.enrt_cvg_strt_dt := l_tmpepe_row.enrt_cvg_strt_dt;
734 l_currepe_row.enrt_cvg_strt_dt_cd := l_tmpepe_row.enrt_cvg_strt_dt_cd;
735 l_currepe_row.enrt_cvg_strt_dt_rl := l_tmpepe_row.enrt_cvg_strt_dt_rl;
736 l_currepe_row.yr_perd_id := l_tmpepe_row.yr_perd_id;
737 l_currepe_row.prtn_ovridn_flag := l_tmpepe_row.prtn_ovridn_flag;
738 l_currepe_row.prtn_ovridn_thru_dt := l_tmpepe_row.prtn_ovridn_thru_dt;
739 l_currepe_row.rt_age_val := l_tmpepe_row.rt_age_val;
740 l_currepe_row.rt_los_val := l_tmpepe_row.rt_los_val;
741 l_currepe_row.rt_hrs_wkd_val := l_tmpepe_row.rt_hrs_wkd_val;
742 l_currepe_row.rt_cmbn_age_n_los_val := l_tmpepe_row.rt_cmbn_age_n_los_val;
743 /*
744 l_currepe_row.elctbl_flag := l_tmpepe_row.elctbl_flag;
745 l_currepe_row.object_version_number := l_tmpepe_row.object_version_number;
746 l_currepe_row.alws_dpnt_dsgn_flag := l_tmpepe_row.alws_dpnt_dsgn_flag;
747 l_currepe_row.dpnt_dsgn_cd := l_tmpepe_row.dpnt_dsgn_cd;
748 l_currepe_row.ler_chg_dpnt_cvg_cd := l_tmpepe_row.ler_chg_dpnt_cvg_cd;
749 l_currepe_row.dpnt_cvg_strt_dt_cd := l_tmpepe_row.dpnt_cvg_strt_dt_cd;
750 l_currepe_row.dpnt_cvg_strt_dt_rl := l_tmpepe_row.dpnt_cvg_strt_dt_rl;
751 l_currepe_row.in_pndg_wkflow_flag := l_tmpepe_row.in_pndg_wkflow_flag;
752 */
753 l_currepe_row.bnft_prvdr_pool_id := l_tmpepe_row.bnft_prvdr_pool_id;
754 --
755 -- Set OUT parameters
756 --
757 p_currepe_row := l_currepe_row;
758 --
759 end DetectEPEENBInfo;
760 --
761 procedure DetectVAPROInfo
762 (p_currepe_row in ben_determine_rates.g_curr_epe_rec
763 --
764 ,p_lf_evt_ocrd_dt in date
765 ,p_last_update_date in date
766 --
767 ,p_actl_prem_id in number default null
768 ,p_acty_base_rt_id in number default null
769 ,p_cvg_amt_calc_mthd_id in number default null
770 --
771 ,p_vpfdets out nocopy gc_vpfdets%rowtype
772 ,p_vpf_id out nocopy number
773 ,p_faterr_code out nocopy varchar2
774 ,p_faterr_type out nocopy varchar2
775 )
776 is
777 --
778 l_vpfdets gc_vpfdets%rowtype;
779 --
780 cursor c_avrdets
781 (c_vpf_id in number
782 ,c_abr_id in number
783 ,c_eff_date in date
784 )
785 is
786 select avr.creation_date,
787 avr.last_update_date
788 from ben_acty_vrbl_rt_f avr
789 where avr.vrbl_rt_prfl_id = c_vpf_id
790 and avr.ACTY_BASE_RT_ID = c_abr_id
791 and c_eff_date
792 between avr.effective_start_date
793 and avr.effective_end_date;
794 --
795 l_avrdets c_avrdets%rowtype;
796 --
797 l_vpf_id number;
798 --
799 begin
800 --
801 ben_evaluate_rate_profiles.main
802 (p_currepe_row => p_currepe_row
803 --
804 ,p_person_id => p_currepe_row.person_id
805 ,p_acty_base_rt_id => p_acty_base_rt_id
806 ,p_actl_prem_id => p_actl_prem_id
807 ,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
808 ,p_elig_per_elctbl_chc_id => p_currepe_row.elig_per_elctbl_chc_id
809 ,p_effective_date => p_lf_evt_ocrd_dt
810 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
811 ,p_calc_only_rt_val_flag => FALSE
812 ,p_pgm_id => p_currepe_row.pgm_id
813 ,p_pl_id => p_currepe_row.pl_id
814 ,p_pl_typ_id => p_currepe_row.pl_typ_id
815 ,p_oipl_id => p_currepe_row.oipl_id
816 ,p_per_in_ler_id => p_currepe_row.per_in_ler_id
817 ,p_ler_id => p_currepe_row.ler_id
818 ,p_business_group_id => p_currepe_row.business_group_id
819 ,p_vrbl_rt_prfl_id => l_vpf_id
820 );
821 --
822 -- Check for VAPRO
823 --
824 if l_vpf_id is null then
825 --
826 p_faterr_code := null;
827 return;
828 --
829 end if;
830 --
831 -- Set OUT parameters
832 --
833 p_vpf_id := l_vpf_id;
834 --
835 -- Get vapro details
836 --
837 open gc_vpfdets
838 (c_vpf_id => l_vpf_id
839 ,c_eff_date => p_lf_evt_ocrd_dt
840 );
841 fetch gc_vpfdets into l_vpfdets;
842 if gc_vpfdets%notfound then
843 --
844 p_faterr_code := 'NODTVPF';
845 return;
846 --
847 end if;
848 close gc_vpfdets;
849 --
850 p_vpfdets := l_vpfdets;
851 --
852 -- Check for modified VAPRO since LUD
853 --
854 if l_vpfdets.last_update_date > nvl(p_last_update_date,hr_api.g_eot)
855 then
856 --
857 p_faterr_code := 'VPFCORR';
858 p_faterr_type := 'CORRECTEDINFO';
859 return;
860 --
861 end if;
862 --
863 -- Check for AVRs
864 --
865 if p_acty_base_rt_id is not null then
866 --
867 open c_avrdets
868 (c_vpf_id => l_vpf_id
869 ,c_abr_id => p_acty_base_rt_id
870 ,c_eff_date => p_lf_evt_ocrd_dt
871 );
872 fetch c_avrdets into l_avrdets;
873 if c_avrdets%notfound then
874 --
875 p_faterr_code := 'NODTAVRVPF';
876 return;
877 --
878 end if;
879 close c_avrdets;
880 --
881 -- Check for modified AVR since LUD
882 --
883 if l_avrdets.last_update_date > p_last_update_date
884 then
885 --
886 p_faterr_code := 'VPFAVRCORR';
887 return;
888 --
889 end if;
890 --
891 end if;
892 /*
893 --
894 -- Check for flat amount vapros
895 --
896 if l_vpfdets.mlt_cd = 'FLFX' then
897 --
898 p_faterr_code := 'VPFFLFX';
899 return;
900 --
901 end if;
902 */
903 --
904 end DetectVAPROInfo;
905 --
906 procedure DetectRoundInfo
907 (p_rndg_cd in varchar2
908 ,p_rndg_rl in number
909 ,p_old_val in number
910 ,p_new_val in number
911 ,p_eff_date in date
912 --
913 ,p_faterr_code out nocopy varchar2
914 ,p_faterr_type out nocopy varchar2
915 )
916 is
917 --
918
919 --
920 begin
921 --
922 if (p_rndg_cd is not null
923 or p_rndg_rl is not null
924 )
925 then
926 --
927 if benutils.do_rounding
928 (p_rounding_cd => p_rndg_cd
929 ,p_rounding_rl => p_rndg_rl
930 ,p_value => p_new_val
931 ,p_effective_date => p_eff_date
932 ) <> p_new_val
933 and round(p_new_val) = round(p_old_val)
934 then
935 --
936 p_faterr_code := 'NOROUND';
937 p_faterr_type := 'CONVEXCLUSION';
938 return;
939 --
940 end if;
941 --
942 if benutils.do_rounding
943 (p_rounding_cd => p_rndg_cd
944 ,p_rounding_rl => p_rndg_rl
945 ,p_value => p_old_val
946 ,p_effective_date => p_eff_date
947 ) = p_new_val
948 then
949 --
950 p_faterr_code := 'NEWLYROUNDED';
951 p_faterr_type := 'CONVEXCLUSION';
952 return;
953 --
954 end if;
955 --
956 if benutils.do_rounding
957 (p_rounding_cd => p_rndg_cd
958 ,p_rounding_rl => p_rndg_rl
959 ,p_value => p_new_val
960 ,p_effective_date => p_eff_date
961 ) = p_old_val
962 then
963 --
964 p_faterr_code := 'WASROUNDED';
965 p_faterr_type := 'CONVEXCLUSION';
966 return;
967 --
968 end if;
969 --
970 end if;
971 --
972 -- Hard coded rounded removal
973 --
974 if round(p_new_val,2) = p_old_val then
975 --
976 p_faterr_code := 'WASHCROUNDED';
977 p_faterr_type := 'CONVEXCLUSION';
978 return;
979 --
980 end if;
981 --
982 -- Check for the hard code rounded change in bendisrt
983 --
984 if (p_rndg_cd is null
985 and p_rndg_rl is null
986 )
987 and p_old_val = round(p_new_val,-1)
988 and p_old_val > 0
989 then
990 --
991 p_faterr_code := 'RNGDERRTO10';
992 p_faterr_type := 'CONVEXCLUSION';
993 return;
994 --
995 elsif (p_rndg_cd is null
996 and p_rndg_rl is null
997 )
998 and p_old_val = round(p_new_val,-2)
999 and p_old_val > 0
1000 then
1001 --
1002 p_faterr_code := 'RNGDERRTO100';
1003 p_faterr_type := 'CONVEXCLUSION';
1004 return;
1005 --
1006 elsif (p_rndg_cd is null
1007 and p_rndg_rl is null
1008 )
1009 and p_old_val = round(p_new_val,-3)
1010 and p_old_val > 0
1011 then
1012 --
1013 p_faterr_code := 'RNGDERRTO1000';
1014 p_faterr_type := 'CONVEXCLUSION';
1015 return;
1016 --
1017 elsif (p_rndg_cd is null
1018 and p_rndg_rl is null
1019 )
1020 and p_old_val = round(p_new_val,-4)
1021 and p_old_val > 0
1022 then
1023 --
1024 p_faterr_code := 'RNGDERRTO10000';
1025 p_faterr_type := 'CONVEXCLUSION';
1026 return;
1027 --
1028 elsif (p_rndg_cd is null
1029 and p_rndg_rl is null
1030 )
1031 and p_old_val = round(p_new_val,-5)
1032 and p_old_val > 0
1033 then
1034 --
1035 p_faterr_code := 'RNGDERRTO100000';
1036 p_faterr_type := 'CONVEXCLUSION';
1037 return;
1038 --
1039 elsif (p_rndg_cd is null
1040 and p_rndg_rl is null
1041 )
1042 and p_old_val = round(p_new_val,-6)
1043 and p_old_val > 0
1044 then
1045 --
1046 p_faterr_code := 'RNGDERRTO1000000';
1047 p_faterr_type := 'CONVEXCLUSION';
1048 return;
1049 --
1050 elsif (p_rndg_cd is null
1051 and p_rndg_rl is null
1052 )
1053 and p_old_val = round(p_new_val,-7)
1054 and p_old_val > 0
1055 then
1056 --
1057 p_faterr_code := 'RNGDERRTO10000000';
1058 p_faterr_type := 'CONVEXCLUSION';
1059 return;
1060 --
1061 elsif (p_rndg_cd is null
1062 and p_rndg_rl is null
1063 )
1064 and (p_old_val
1065 between (p_new_val*.99) and (p_new_val*1.01))
1066 and p_old_val > 0
1067 then
1068 --
1069 p_faterr_code := 'RNGDERR1%';
1070 p_faterr_type := 'CONVEXCLUSION';
1071 return;
1072 --
1073 elsif (p_rndg_cd is null
1074 and p_rndg_rl is null
1075 )
1076 and (p_old_val
1077 between (p_new_val*.98) and (p_new_val*1.02))
1078 and p_old_val > 0
1079 then
1080 --
1081 p_faterr_code := 'RNGDERR2%';
1082 p_faterr_type := 'CONVEXCLUSION';
1083 return;
1084 --
1085 elsif (p_rndg_cd is null
1086 and p_rndg_rl is null
1087 )
1088 and (p_old_val
1089 between (p_new_val*.97) and (p_new_val*1.03))
1090 and p_old_val > 0
1091 then
1092 --
1093 p_faterr_code := 'RNGDERR3%';
1094 p_faterr_type := 'CONVEXCLUSION';
1095 return;
1096 --
1097 elsif (p_rndg_cd is null
1098 and p_rndg_rl is null
1099 )
1100 and (p_old_val
1101 between (p_new_val*.96) and (p_new_val*1.04))
1102 and p_old_val > 0
1103 then
1104 --
1105 p_faterr_code := 'RNGDERR4%';
1106 p_faterr_type := 'CONVEXCLUSION';
1107 return;
1108 --
1109 elsif (p_rndg_cd is null
1110 and p_rndg_rl is null
1111 )
1112 and (p_old_val
1113 between (p_new_val*.9) and (p_new_val*1.1))
1114 and p_old_val > 0
1115 then
1116 --
1117 p_faterr_code := 'RNGDERR10%';
1118 p_faterr_type := 'CONVEXCLUSION';
1119 return;
1120 --
1121 end if;
1122 --
1123 end DetectRoundInfo;
1124 --
1125 procedure DetectConvInfo
1126 (p_ncucurr_code in varchar2
1127 ,p_new_val in number
1128 ,p_preconv_val in number
1129 --
1130 ,p_faterr_code out nocopy varchar2
1131 ,p_faterr_type out nocopy varchar2
1132 ,p_postconv_val out nocopy number
1133 )
1134 is
1135 --
1136 cursor c_ccfactdets
1137 (c_curr_code varchar2
1138 )
1139 is
1140 select fcu.derive_factor
1141 from fnd_currencies fcu
1142 where fcu.currency_code = c_curr_code;
1143 --
1144 l_ccfactdets c_ccfactdets%rowtype;
1145 --
1146 l_faterr_code varchar2(30);
1147 l_faterr_type varchar2(30);
1148 l_rndfaterr_code varchar2(30);
1149 l_rndfaterr_type varchar2(30);
1150 l_postconv_val number;
1151 --
1152 begin
1153 --
1154 open c_ccfactdets
1155 (c_curr_code => p_ncucurr_code
1156 );
1157 fetch c_ccfactdets into l_ccfactdets;
1158 if c_ccfactdets%notfound then
1159 --
1160 l_faterr_code := 'NOCURRCONVFACT';
1161 l_faterr_type := 'CORRUPTDATA';
1162 --
1163 end if;
1164 close c_ccfactdets;
1165 --
1166 -- Check for a converted value problems
1167 --
1168 if l_faterr_code is null
1169 and p_new_val=p_preconv_val
1170 and l_ccfactdets.derive_factor <> 1
1171 then
1172 --
1173 l_postconv_val := p_new_val;
1174 l_faterr_code := 'NOCONVADJVAL';
1175 l_faterr_type := 'CONVEXCLUSION';
1176 --
1177 elsif l_faterr_code is null
1178 and (p_new_val*l_ccfactdets.derive_factor)
1179 between (p_preconv_val*0.95)
1180 and (p_preconv_val*1.05)
1181 and l_ccfactdets.derive_factor <> 1
1182 then
1183 --
1184 l_postconv_val := p_new_val*l_ccfactdets.derive_factor;
1185 l_faterr_code := 'CONVADJVAL5%';
1186 l_faterr_type := 'CONVEXCLUSION';
1187 --
1188 elsif l_faterr_code is null
1189 and (p_new_val*l_ccfactdets.derive_factor)
1190 between (p_preconv_val*0.9)
1191 and (p_preconv_val*1.1)
1192 and l_ccfactdets.derive_factor <> 1
1193 then
1194 --
1195 l_postconv_val := p_new_val*l_ccfactdets.derive_factor;
1196 l_faterr_code := 'CONVADJVAL10%';
1197 l_faterr_type := 'CONVEXCLUSION';
1198 --
1199 elsif l_faterr_code is null
1200 and (p_new_val*l_ccfactdets.derive_factor)
1201 between (p_preconv_val*0.75)
1202 and (p_preconv_val*1.25)
1203 and l_ccfactdets.derive_factor <> 1
1204 then
1205 --
1206 l_postconv_val := p_new_val*l_ccfactdets.derive_factor;
1207 l_faterr_code := 'CONVADJVAL25%';
1208 l_faterr_type := 'CONVEXCLUSION';
1209 --
1210 elsif l_faterr_code is null
1211 and (p_new_val*l_ccfactdets.derive_factor)
1212 between (p_preconv_val*0.5)
1213 and (p_preconv_val*1.5)
1214 and l_ccfactdets.derive_factor <> 1
1215 then
1216 --
1217 l_postconv_val := p_new_val*l_ccfactdets.derive_factor;
1218 l_faterr_code := 'CONVADJVAL50%';
1219 l_faterr_type := 'CONVEXCLUSION';
1220 --
1221 elsif l_faterr_code is null
1222 and l_ccfactdets.derive_factor <> 1
1223 then
1224 --
1225 l_postconv_val := p_new_val*l_ccfactdets.derive_factor;
1226 l_faterr_code := 'CONVADJVAL>50%';
1227 l_faterr_type := 'CONVEXCLUSION';
1228 --
1229 -- Check for negative values
1230 --
1231 if p_preconv_val < 0 then
1232 --
1233 l_faterr_code := 'MINUSCONVADJVAL';
1234 l_faterr_type := 'CONVEXCLUSION';
1235 --
1236 end if;
1237 --
1238 end if;
1239 --
1240 -- Check for hard coded rounding problems
1241 --
1242 if l_faterr_code in ('CONVADJVAL5%'
1243 ,'CONVADJVAL10%'
1244 ,'CONVADJVAL25%'
1245 ,'CONVADJVAL50%'
1246 ,'CONVADJVAL>50%'
1247 )
1248 then
1249 --
1250 ben_efc_adjustments.DetectRoundInfo
1251 (p_rndg_cd => null
1252 ,p_rndg_rl => null
1253 ,p_old_val => p_preconv_val
1254 ,p_new_val => l_postconv_val
1255 ,p_eff_date => null
1256 --
1257 ,p_faterr_code => l_rndfaterr_code
1258 ,p_faterr_type => l_rndfaterr_type
1259 );
1260 --
1261 if l_rndfaterr_code is not null then
1262 --
1263 l_faterr_code := l_rndfaterr_code;
1264 l_faterr_type := 'CONVEXCLUSION';
1265 --
1266 end if;
1267 --
1268 end if;
1269 --
1270 p_faterr_code := l_faterr_code;
1271 p_faterr_type := l_faterr_type;
1272 p_postconv_val := l_postconv_val;
1273 --
1274 end DetectConvInfo;
1275 --
1276 procedure DetectInvAsg
1277 (p_person_id in number
1278 ,p_eff_date in date
1279 --
1280 ,p_perasg out nocopy gc_perasg%rowtype
1281 ,p_noasgpay out nocopy boolean
1282 )
1283 is
1284 --
1285 l_perasg gc_perasg%rowtype;
1286 --
1287 begin
1288 --
1289 open gc_perasg
1290 (c_person_id => p_person_id
1291 ,c_effective_date => p_eff_date
1292 );
1293 fetch gc_perasg into l_perasg;
1294 close gc_perasg;
1295 --
1296 if l_perasg.payroll_id is null then
1297 --
1298 p_noasgpay := TRUE;
1299 --
1300 else
1301 --
1302 p_noasgpay := FALSE;
1303 --
1304 end if;
1305 --
1306 p_perasg := l_perasg;
1307 --
1308 end DetectInvAsg;
1309 --
1310 procedure Insert_fndsession_row
1311 (p_ses_date in date
1312 )
1313 is
1314 --
1315 /*
1316 PRAGMA AUTONOMOUS_TRANSACTION;
1317 --
1318 l_commit number;
1319 */
1320 --
1321 begin
1322 --
1323 null;
1324 /*
1325 dt_fndate.change_ses_date
1326 (p_ses_date => p_ses_date
1327 ,p_commit => l_commit
1328 );
1329 --
1330 COMMIT;
1331 */
1332 --
1333 end Insert_fndsession_row;
1334 --
1335 procedure insert_validation_exceptions
1336 (p_val_set in g_failed_adj_values_tbl
1337 ,p_efc_action_id in number
1338 ,p_ent_scode in varchar2
1339 ,p_exception_type in varchar2
1340 )
1341 is
1342 --
1343 PRAGMA AUTONOMOUS_TRANSACTION;
1344 --
1345 l_esd date;
1346 l_eed date;
1347 --
1348 l_exception_type varchar2(100);
1349 --
1350 begin
1351 --
1352 if p_val_set.count > 0 then
1353 --
1354 l_exception_type := p_exception_type;
1355 --
1356 for ele_num in p_val_set.first..p_val_set.last
1357 loop
1358 --
1359 if p_exception_type is null then
1360 --
1361 l_exception_type := p_val_set(ele_num).faterr_type;
1362 --
1363 end if;
1364 --
1365 l_esd := p_val_set(ele_num).esd;
1366 l_eed := p_val_set(ele_num).eed;
1367 --
1368 if p_val_set(ele_num).esd is null then
1369 --
1370 l_esd := hr_api.g_sot;
1371 --
1372 end if;
1373 --
1374 if p_val_set(ele_num).eed is null then
1375 --
1376 l_eed := hr_api.g_eot;
1377 --
1378 end if;
1379 --
1380 insert into ben_efc_exclusions
1381 (efc_action_id
1382 ,ent_scode
1383 ,exclusion_type
1384 ,pk_id
1385 ,effective_start_date
1386 ,effective_end_date
1387 ,exclusion_code
1388 ,old_val
1389 ,new_val
1390 ,object_version_number
1391 ,business_group_id
1392 ,creation_date
1393 ,last_update_date
1394 ,created_by
1395 ,last_updated_by
1396 )
1397 values
1398 (p_efc_action_id
1399 ,p_ent_scode
1400 ,l_exception_type
1401 ,p_val_set(ele_num).id
1402 ,l_esd
1403 ,l_eed
1404 ,p_val_set(ele_num).faterr_code
1405 ,p_val_set(ele_num).old_val1
1406 ,p_val_set(ele_num).new_val1
1407 ,p_val_set(ele_num).ovn
1408 ,p_val_set(ele_num).bgp_id
1409 ,p_val_set(ele_num).credt
1410 ,p_val_set(ele_num).lud
1411 ,p_val_set(ele_num).cre_by
1412 ,p_val_set(ele_num).lu_by
1413 );
1414 --
1415 end loop;
1416 --
1417 COMMIT;
1418 --
1419 end if;
1420 --
1421 end insert_validation_exceptions;
1422 --
1423 procedure pep_adjustments
1424 (p_validate in boolean default false
1425 ,p_worker_id in number default null
1426 ,p_action_id in number default null
1427 ,p_total_workers in number default null
1428 ,p_pk1 in number default null
1429 ,p_chunk in number default null
1430 ,p_efc_worker_id in number default null
1431 --
1432 ,p_valworker_id in number default null
1433 ,p_valtotal_workers in number default null
1434 --
1435 ,p_business_group_id in number default null
1436 --
1437 ,p_adjustment_counts out nocopy g_adjustment_counts
1438 )
1439 is
1440 --
1441 TYPE cur_type IS REF CURSOR;
1442 --
1443 type g_efc_row is record
1444 (elig_per_id ben_elig_per_f.elig_per_id%type
1445 ,effective_start_date ben_elig_per_f.effective_start_date%type
1446 ,effective_end_date ben_elig_per_f.effective_end_date%type
1447 ,comp_ref_amt ben_elig_per_f.comp_ref_amt%type
1448 ,rt_comp_ref_amt ben_elig_per_f.rt_comp_ref_amt%type
1449 ,comp_ref_uom ben_elig_per_f.comp_ref_uom%type
1450 ,rt_comp_ref_uom ben_elig_per_f.rt_comp_ref_uom%type
1451 ,person_id ben_elig_per_f.person_id%type
1452 ,pgm_id ben_elig_per_f.pgm_id%type
1453 ,ptip_id ben_elig_per_f.ptip_id%type
1454 ,plip_id ben_elig_per_f.plip_id%type
1455 ,pl_id ben_elig_per_f.pl_id%type
1456 ,business_group_id ben_elig_per_f.business_group_id%type
1457 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
1458 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
1459 ,creation_date ben_elig_per_f.creation_date%type
1460 ,last_update_date ben_elig_per_f.last_update_date%type
1461 ,object_version_number ben_elig_per_f.object_version_number%type
1462 );
1463 --
1464 c_efc_rows cur_type;
1465 --
1466 l_proc varchar2(1000) := 'pep_adjustments';
1467 --
1468 l_efc_row g_efc_row;
1469 --
1470 l_who_counts g_who_counts;
1471 --
1472 l_row_count pls_integer;
1473 l_calfail_count pls_integer;
1474 l_calsucc_count pls_integer;
1475 l_conv_count pls_integer;
1476 l_unconv_count pls_integer;
1477 l_actconv_count pls_integer;
1478 l_dupconv_count pls_integer;
1479 --
1480 l_rcoerr_count pls_integer;
1481 l_faterrs_count pls_integer;
1482 --
1483 l_olddata boolean;
1484 l_tabrow_count pls_integer;
1485 --
1486 l_fatal_error boolean;
1487 l_sql_str long;
1488 l_from_str long;
1489 l_where_str long;
1490 --
1491 l_efc_batch boolean;
1492 l_mode varchar2(100);
1493 l_nomatco boolean;
1494 l_noattdf boolean;
1495 l_nocmpattdf boolean;
1496 --
1497 l_comp_obj_tree ben_manage_life_events.g_cache_proc_object_table;
1498 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
1499 l_init_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
1500 l_df_counts ben_efc_functions.g_attach_df_counts;
1501 l_per_row per_all_people_f%ROWTYPE;
1502 l_empasg_row per_all_assignments_f%ROWTYPE;
1503 l_benasg_row per_all_assignments_f%ROWTYPE;
1504 l_pil_row ben_per_in_ler%rowtype;
1505 l_comp_rec ben_derive_part_and_rate_facts.g_cache_structure;
1506 l_d_comp_rec ben_derive_part_and_rate_facts.g_cache_structure;
1507 l_oiplip_rec ben_derive_part_and_rate_facts.g_cache_structure;
1508 l_d_oiplip_rec ben_derive_part_and_rate_facts.g_cache_structure;
1509 --
1510 l_coent_scode varchar2(100);
1511 l_compobj_id number;
1512 l_pk1 number;
1513 --
1514 l_faterr_code varchar2(100);
1515 l_faterr_type varchar2(100);
1516 l_adjfailed boolean;
1517 --
1518 l_prevbgp_id number;
1519 --
1520 begin
1521 --
1522 l_efc_batch := FALSE;
1523 --
1524 l_row_count := 0;
1525 l_calfail_count := 0;
1526 l_calsucc_count := 0;
1527 l_dupconv_count := 0;
1528 l_conv_count := 0;
1529 l_actconv_count := 0;
1530 l_unconv_count := 0;
1531 --
1532 l_rcoerr_count := 0;
1533 l_faterrs_count := 0;
1534 --
1535 g_pep_success_adj_val_set.delete;
1536 g_pep_rcoerr_val_set.delete;
1537 g_pep_failed_adj_val_set.delete;
1538 g_pep_fatal_error_val_set.delete;
1539 --
1540 l_mode := 'L';
1541 --
1542 -- Check if EFC process parameters are set
1543 --
1544 if p_action_id is not null
1545 and p_pk1 is not null
1546 and p_chunk is not null
1547 and p_efc_worker_id is not null
1548 then
1549 --
1550 l_efc_batch := TRUE;
1551 --
1552 end if;
1553 --
1554 l_from_str := ' FROM ben_elig_per_f pep, '
1555 ||' ben_per_in_ler pil, '
1556 ||' per_all_people_f per ';
1557 --
1558 l_where_str := ' where pep.per_in_ler_id = pil.per_in_ler_id '
1559 ||' and (pep.comp_ref_amt is not null '
1560 ||' or pep.rt_comp_ref_amt is not null) '
1561 ||' and pil.person_id = per.person_id '
1562 ||' and pil.LF_EVT_OCRD_DT '
1563 ||' between per.effective_start_date and per.effective_end_date ';
1564 --
1565 -- Check if we are restricting by business group
1566 --
1567 if p_business_group_id is not null then
1568 --
1569 l_where_str := l_where_str||' and pep.business_group_id = '||p_business_group_id;
1570 --
1571 end if;
1572 --
1573 -- Build in batch specific restrictions
1574 --
1575 if l_efc_batch then
1576 --
1577 l_from_str := l_from_str||', ben_elig_per_f_efc efc ';
1578 l_where_str := l_where_str||' and efc.elig_per_id = pep.elig_per_id '
1579 ||' and efc.effective_start_date = pep.effective_start_date '
1580 ||' and efc.effective_end_date = pep.effective_end_date '
1581 ||' and efc.efc_action_id = :action_id '
1582 ||' and pep.elig_per_id > :pk1 '
1583 ||' and mod(pep.elig_per_id, :total_workers) = :worker_id ';
1584 --
1585 elsif p_valworker_id is not null
1586 and p_valtotal_workers is not null
1587 then
1588 --
1589 l_where_str := l_where_str||' and mod(pep.elig_per_id, :valtotal_workers) = :valworker_id ';
1590 --
1591 end if;
1592 --
1593 l_sql_str := ' select pep.elig_per_id, '
1594 ||' pep.effective_start_date, '
1595 ||' pep.effective_end_date, '
1596 ||' pep.comp_ref_amt, '
1597 ||' pep.rt_comp_ref_amt, '
1598 ||' pep.comp_ref_uom, '
1599 ||' pep.rt_comp_ref_uom, '
1600 ||' pep.person_id, '
1601 ||' pep.pgm_id, '
1602 ||' pep.ptip_id, '
1603 ||' pep.plip_id, '
1604 ||' pep.pl_id, '
1605 ||' pep.business_group_id, '
1606 ||' pil.lf_evt_ocrd_dt, '
1607 ||' pil.per_in_ler_id, '
1608 ||' pep.creation_date, '
1609 ||' pep.last_update_date, '
1610 ||' pep.object_version_number '
1611 ||l_from_str
1612 ||l_where_str
1613 ||' order by pep.elig_per_id ';
1614 --
1615 if l_efc_batch then
1616 --
1617 hr_efc_info.insert_line('-- ');
1618 hr_efc_info.insert_line('-- Adjusting elig pers ');
1619 hr_efc_info.insert_line('-- ');
1620 --
1621 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
1622 --
1623 elsif p_valworker_id is not null
1624 and p_valtotal_workers is not null
1625 then
1626 --
1627 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
1628 --
1629 else
1630 --
1631 open c_efc_rows FOR l_sql_str;
1632 --
1633 end if;
1634 --
1635 loop
1636 FETCH c_efc_rows INTO l_efc_row;
1637 EXIT WHEN c_efc_rows%NOTFOUND;
1638 --
1639 l_fatal_error := FALSE;
1640 l_faterr_type := null;
1641 l_faterr_code := null;
1642 l_adjfailed := FALSE;
1643 --
1644 -- Success and failure checks
1645 --
1646 if (l_efc_row.comp_ref_amt is not null
1647 and l_efc_row.comp_ref_uom is null)
1648 or
1649 (l_efc_row.rt_comp_ref_amt is not null
1650 and l_efc_row.rt_comp_ref_uom is null)
1651 and l_faterr_code is null
1652 then
1653 --
1654 l_adjfailed := TRUE;
1655 l_faterr_code := 'NULLUOM';
1656 l_faterr_type := 'MISSINGSETUP';
1657 --
1658 end if;
1659 --
1660 if nvl(l_efc_row.comp_ref_uom,'ZZZZ') = 'EUR'
1661 or nvl(l_efc_row.rt_comp_ref_uom,'ZZZZ') = 'EUR'
1662 and l_faterr_code is null
1663 then
1664 --
1665 l_adjfailed := TRUE;
1666 l_faterr_code := 'EUROUOM';
1667 l_faterr_type := 'VALIDEXCLUSION';
1668 --
1669 end if;
1670 --
1671 if l_faterr_code is null then
1672 --
1673 ben_derive_part_and_rate_cache.clear_down_cache;
1674 ben_person_object.clear_down_cache;
1675 ben_pil_object.clear_down_cache;
1676 --
1677 if nvl(l_prevbgp_id,-9999) <> l_efc_row.business_group_id then
1678 --
1679 ben_manage_life_events.g_cache_proc_object.delete;
1680 --
1681 end if;
1682 --
1683 begin
1684 --
1685 -- Set up comp object list
1686 --
1687 ben_comp_object_list.build_comp_object_list
1688 (p_effective_date => l_efc_row.lf_evt_ocrd_dt
1689 ,p_business_group_id => l_efc_row.business_group_id
1690 ,p_mode => l_mode
1691 );
1692 --
1693 l_prevbgp_id := l_efc_row.business_group_id;
1694 l_comp_obj_tree := ben_manage_life_events.g_cache_proc_object;
1695 --
1696 -- Set comp object context values
1697 --
1698 l_comp_obj_tree_row := l_init_comp_obj_tree_row;
1699 --
1700 exception
1701 when others then
1702 --
1703 g_pep_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.elig_per_id;
1704 g_pep_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
1705 g_pep_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
1706 g_pep_rcoerr_val_set(l_rcoerr_count).rco_name := 'BCOL';
1707 g_pep_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
1708 g_pep_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
1709 g_pep_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
1710 --
1711 l_rcoerr_count := l_rcoerr_count+1;
1712 l_fatal_error := TRUE;
1713 --
1714 end;
1715 --
1716 end if;
1717 --
1718 if l_comp_obj_tree.count > 0
1719 and not l_fatal_error
1720 then
1721 --
1722 for bcolele_num in l_comp_obj_tree.first..l_comp_obj_tree.last
1723 loop
1724 --
1725 if nvl(l_comp_obj_tree(bcolele_num).par_pgm_id,9999) = nvl(l_efc_row.pgm_id,9999)
1726 and nvl(l_comp_obj_tree(bcolele_num).ptip_id,9999) = nvl(l_efc_row.ptip_id,9999)
1727 and nvl(l_comp_obj_tree(bcolele_num).pl_id,9999) = nvl(l_efc_row.pl_id,9999)
1728 and nvl(l_comp_obj_tree(bcolele_num).plip_id,9999) = nvl(l_efc_row.plip_id,9999)
1729 then
1730 --
1731 l_comp_obj_tree_row := l_comp_obj_tree(bcolele_num);
1732 --
1733 exit;
1734 --
1735 end if;
1736 --
1737 end loop;
1738 --
1739 -- Detect comp object list information
1740 --
1741 ben_efc_adjustments.DetectBCOLRowInfo
1742 (p_comp_obj_tree_row => l_comp_obj_tree_row
1743 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
1744 ,p_business_group_id => l_efc_row.business_group_id
1745 --
1746 ,p_faterr_code => l_faterr_code
1747 );
1748 --
1749 end if;
1750 --
1751 if l_faterr_code is null then
1752 --
1753 -- Check the comp object type
1754 --
1755 if l_comp_obj_tree_row.plip_id is not null then
1756 --
1757 l_coent_scode := 'CPP';
1758 l_compobj_id := l_comp_obj_tree_row.plip_id;
1759 --
1760 elsif l_comp_obj_tree_row.pl_id is not null then
1761 --
1762 l_coent_scode := 'PLN';
1763 l_compobj_id := l_comp_obj_tree_row.pl_id;
1764 --
1765 elsif l_comp_obj_tree_row.ptip_id is not null then
1766 --
1767 l_coent_scode := 'CTP';
1768 l_compobj_id := l_comp_obj_tree_row.ptip_id;
1769 --
1770 elsif l_comp_obj_tree_row.pgm_id is not null then
1771 --
1772 l_coent_scode := 'PGM';
1773 l_compobj_id := l_comp_obj_tree_row.pgm_id;
1774 --
1775 end if;
1776 --
1777 ben_efc_functions.CompObject_ChkAttachDF
1778 (p_coent_scode => l_coent_scode
1779 ,p_compobj_id => l_compobj_id
1780 --
1781 ,p_counts => l_df_counts
1782 );
1783 --
1784 if l_df_counts.noattdf_count = 0 then
1785 --
1786 l_faterr_code := 'NOATTDF';
1787 --
1788 end if;
1789 --
1790 end if;
1791 --
1792 if l_faterr_code is null then
1793 --
1794 -- Set up benefits environment
1795 --
1796 ben_env_object.init
1797 (p_business_group_id => l_efc_row.business_group_id
1798 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
1799 ,p_thread_id => 1
1800 ,p_chunk_size => 10
1801 ,p_threads => 1
1802 ,p_max_errors => 100
1803 ,p_benefit_action_id => 99999
1804 ,p_audit_log_flag => 'N'
1805 );
1806 --
1807 -- Get person info
1808 --
1809 ben_person_object.get_object
1810 (p_person_id => l_efc_row.person_id
1811 ,p_rec => l_per_row
1812 );
1813 --
1814 ben_person_object.get_object
1815 (p_person_id => l_efc_row.person_id
1816 ,p_rec => l_empasg_row
1817 );
1818 --
1819 ben_person_object.get_benass_object
1820 (p_person_id => l_efc_row.person_id
1821 ,p_rec => l_benasg_row
1822 );
1823 --
1824 ben_person_object.get_object
1825 (p_person_id => l_efc_row.person_id
1826 ,p_rec => l_pil_row
1827 );
1828 --
1829 l_comp_rec:=l_d_comp_rec;
1830 l_oiplip_rec:=l_d_oiplip_rec;
1831 --
1832 begin
1833 --
1834 ben_derive_part_and_rate_facts.derive_rates_and_factors
1835 (p_calculate_only_mode => TRUE
1836 ,p_comp_obj_tree_row => l_comp_obj_tree_row
1837 --
1838 -- Context info
1839 --
1840 ,p_per_row => l_per_row
1841 ,p_empasg_row => l_empasg_row
1842 ,p_benasg_row => l_benasg_row
1843 ,p_pil_row => l_pil_row
1844 --
1845 ,p_mode => l_mode
1846 --
1847 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
1848 ,p_lf_evt_ocrd_dt => l_efc_row.lf_evt_ocrd_dt
1849 ,p_person_id => l_efc_row.person_id
1850 ,p_business_group_id => l_efc_row.business_group_id
1851 ,p_pgm_id => l_comp_obj_tree_row.pgm_id
1852 ,p_pl_id => l_comp_obj_tree_row.pl_id
1853 ,p_oipl_id => l_comp_obj_tree_row.oipl_id
1854 ,p_plip_id => l_comp_obj_tree_row.plip_id
1855 ,p_ptip_id => l_comp_obj_tree_row.ptip_id
1856 --
1857 ,p_comp_rec => l_comp_rec
1858 ,p_oiplip_rec => l_oiplip_rec
1859 );
1860 --
1861 if nvl(l_comp_rec.comp_ref_amt,9999) <> nvl(l_efc_row.comp_ref_amt,9999)
1862 then
1863 --
1864 g_pep_failed_adj_val_set(l_calfail_count).id := l_efc_row.elig_per_id;
1865 g_pep_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
1866 g_pep_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
1867 g_pep_failed_adj_val_set(l_calfail_count).old_val1 := l_efc_row.comp_ref_amt;
1868 g_pep_failed_adj_val_set(l_calfail_count).new_val1 := l_comp_rec.comp_ref_amt;
1869 g_pep_failed_adj_val_set(l_calfail_count).val_type := 'PEP_CRAMT';
1870 g_pep_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
1871 g_pep_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
1872 --
1873 l_adjfailed := TRUE;
1874 l_calfail_count := l_calfail_count+1;
1875 --
1876 elsif nvl(l_comp_rec.rt_comp_ref_amt,9999) <> nvl(l_efc_row.rt_comp_ref_amt,9999)
1877 then
1878 --
1879 g_pep_failed_adj_val_set(l_calfail_count).id := l_efc_row.elig_per_id;
1880 g_pep_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
1881 g_pep_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
1882 g_pep_failed_adj_val_set(l_calfail_count).old_val1 := l_efc_row.rt_comp_ref_amt;
1883 g_pep_failed_adj_val_set(l_calfail_count).new_val1 := l_comp_rec.rt_comp_ref_amt;
1884 g_pep_failed_adj_val_set(l_calfail_count).val_type := 'PEP_RTCRAMT';
1885 g_pep_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
1886 g_pep_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
1887 --
1888 l_adjfailed := TRUE;
1889 l_calfail_count := l_calfail_count+1;
1890 --
1891 else
1892 --
1893 l_adjfailed := FALSE;
1894 --
1895 end if;
1896 --
1897 if l_adjfailed
1898 and l_faterr_code is null
1899 then
1900 --
1901 -- WHO checks
1902 --
1903 if l_faterr_code is null then
1904 --
1905 ben_efc_adjustments.DetectWhoInfo
1906 (p_creation_date => l_efc_row.creation_date
1907 ,p_last_update_date => l_efc_row.last_update_date
1908 ,p_object_version_number => l_efc_row.object_version_number
1909 --
1910 ,p_who_counts => l_who_counts
1911 ,p_faterr_code => l_faterr_code
1912 ,p_faterr_type => l_faterr_type
1913 );
1914 --
1915 end if;
1916 --
1917 end if;
1918 --
1919 if l_efc_batch
1920 and l_faterr_code is null
1921 then
1922 --
1923 update ben_elig_per_f pep
1924 set pep.comp_ref_amt = l_comp_rec.comp_ref_amt,
1925 pep.rt_comp_ref_amt = l_comp_rec.rt_comp_ref_amt
1926 where pep.elig_per_id = l_efc_row.elig_per_id
1927 and pep.effective_start_date = l_efc_row.effective_start_date
1928 and pep.effective_end_date = l_efc_row.effective_end_date;
1929 --
1930 if p_validate then
1931 --
1932 rollback;
1933 --
1934 end if;
1935 --
1936 -- Check for end of chunk and commit if necessary
1937 --
1938 l_pk1 := l_efc_row.elig_per_id;
1939 --
1940 ben_efc_functions.maintain_chunks
1941 (p_row_count => l_row_count
1942 ,p_pk1 => l_pk1
1943 ,p_chunk_size => p_chunk
1944 ,p_efc_worker_id => p_efc_worker_id
1945 );
1946 --
1947 end if;
1948 --
1949 exception
1950 when others then
1951 --
1952 g_pep_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.elig_per_id;
1953 g_pep_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
1954 g_pep_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
1955 g_pep_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENDRPAR';
1956 g_pep_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
1957 g_pep_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
1958 g_pep_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
1959 --
1960 l_rcoerr_count := l_rcoerr_count+1;
1961 --
1962 end;
1963 --
1964 end if;
1965 --
1966 -- Check for fatal errors
1967 --
1968 if l_faterr_code is not null
1969 then
1970 --
1971 g_pep_fatal_error_val_set(l_faterrs_count).id := l_efc_row.elig_per_id;
1972 g_pep_fatal_error_val_set(l_faterrs_count).esd := l_efc_row.effective_start_date;
1973 g_pep_fatal_error_val_set(l_faterrs_count).eed := l_efc_row.effective_end_date;
1974 g_pep_fatal_error_val_set(l_faterrs_count).old_val1 := l_efc_row.comp_ref_amt;
1975 g_pep_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
1976 g_pep_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
1977 g_pep_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
1978 g_pep_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
1979 --
1980 l_faterrs_count := l_faterrs_count+1;
1981 --
1982 elsif l_faterr_code is null
1983 and not l_adjfailed
1984 then
1985 --
1986 g_pep_success_adj_val_set(l_calsucc_count).id := l_efc_row.elig_per_id;
1987 g_pep_success_adj_val_set(l_calsucc_count).esd := l_efc_row.effective_start_date;
1988 g_pep_success_adj_val_set(l_calsucc_count).eed := l_efc_row.effective_end_date;
1989 g_pep_success_adj_val_set(l_calsucc_count).old_val1 := l_efc_row.comp_ref_amt;
1990 g_pep_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
1991 g_pep_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
1992 --
1993 l_calsucc_count := l_calsucc_count+1;
1994 --
1995 end if;
1996 --
1997 l_row_count := l_row_count+1;
1998 --
1999 end loop;
2000 CLOSE c_efc_rows;
2001 --
2002 ben_efc_functions.conv_check
2003 (p_table_name => 'ben_elig_per_f'
2004 ,p_efctable_name => 'ben_elig_per_f_efc'
2005 --
2006 ,p_tabwhere_clause => ' (comp_ref_amt is not null '
2007 ||' or rt_comp_ref_amt is not null) '
2008 ,p_bgp_id => p_business_group_id
2009 ,p_action_id => p_action_id
2010 --
2011 ,p_conv_count => l_conv_count
2012 ,p_unconv_count => l_unconv_count
2013 ,p_tabrow_count => l_tabrow_count
2014 );
2015 --
2016 -- Set counts
2017 --
2018 if p_action_id is null then
2019 --
2020 l_actconv_count := 0;
2021 --
2022 else
2023 --
2024 l_actconv_count := l_conv_count;
2025 --
2026 end if;
2027 --
2028 p_adjustment_counts.efcrow_count := l_row_count;
2029 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
2030 p_adjustment_counts.tabrow_count := l_tabrow_count;
2031 --
2032 p_adjustment_counts.calfail_count := l_calfail_count;
2033 p_adjustment_counts.calsucc_count := l_calsucc_count;
2034 p_adjustment_counts.dupconv_count := l_dupconv_count;
2035 p_adjustment_counts.conv_count := l_conv_count;
2036 p_adjustment_counts.actconv_count := l_actconv_count;
2037 p_adjustment_counts.unconv_count := l_unconv_count;
2038 --
2039 end pep_adjustments;
2040 --
2041 procedure epo_adjustments
2042 (p_validate in boolean default false
2043 ,p_worker_id in number default null
2044 ,p_action_id in number default null
2045 ,p_total_workers in number default null
2046 ,p_pk1 in number default null
2047 ,p_chunk in number default null
2048 ,p_efc_worker_id in number default null
2049 --
2050 ,p_valworker_id in number default null
2051 ,p_valtotal_workers in number default null
2052 --
2053 ,p_business_group_id in number default null
2054 --
2055 ,p_adjustment_counts out nocopy g_adjustment_counts
2056 )
2057 is
2058 --
2059 TYPE cur_type IS REF CURSOR;
2060 --
2061 type g_efc_row is record
2062 (elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type
2063 ,effective_start_date ben_elig_per_opt_f.effective_start_date%type
2064 ,effective_end_date ben_elig_per_opt_f.effective_end_date%type
2065 ,comp_ref_amt ben_elig_per_opt_f.comp_ref_amt%type
2066 ,rt_comp_ref_amt ben_elig_per_opt_f.rt_comp_ref_amt%type
2067 ,person_id ben_elig_per_f.person_id%type
2068 ,pgm_id ben_elig_per_f.pgm_id%type
2069 ,pl_id ben_elig_per_f.pl_id%type
2070 ,opt_id ben_elig_per_opt_f.opt_id%type
2071 ,business_group_id ben_elig_per_opt_f.business_group_id%type
2072 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
2073 ,creation_date ben_elig_per_opt_f.creation_date%type
2074 ,last_update_date ben_elig_per_opt_f.last_update_date%type
2075 ,object_version_number ben_elig_per_opt_f.object_version_number%type
2076 ,per_in_ler_id ben_elig_per_opt_f.per_in_ler_id%type
2077 );
2078 --
2079 c_efc_rows cur_type;
2080 --
2081 l_proc varchar2(1000) := 'epo_adjustments';
2082 --
2083 l_efc_row g_efc_row;
2084 --
2085 l_who_counts g_who_counts;
2086 --
2087 l_row_count pls_integer;
2088 l_calfail_count pls_integer;
2089 l_calsucc_count pls_integer;
2090 l_nomatco_count pls_integer;
2091 l_noattdf_count pls_integer;
2092 l_nocmpattdf_count pls_integer;
2093 l_conv_count pls_integer;
2094 l_unconv_count pls_integer;
2095 l_actconv_count pls_integer;
2096 l_dupconv_count pls_integer;
2097 l_oipl_count pls_integer;
2098 l_oiplip_count pls_integer;
2099 --
2100 l_rcoerr_count pls_integer;
2101 l_bcolrcoerr_count pls_integer;
2102 l_pbbepocorr_count pls_integer;
2103 l_pbbenddate_count pls_integer;
2104 --
2105 l_olddata boolean;
2106 --
2107 l_tabrow_count pls_integer;
2108 --
2109 l_succepocra_count pls_integer;
2110 l_succeporcra_count pls_integer;
2111 --
2112 l_epopildtupd_count pls_integer;
2113 l_faterrs_count pls_integer;
2114 --
2115 l_fatal_error boolean;
2116 l_sql_str long;
2117 l_from_str long;
2118 l_where_str long;
2119 --
2120 l_efc_batch boolean;
2121 l_mode varchar2(100);
2122 --
2123 l_nomatco boolean;
2124 l_noattdf boolean;
2125 l_nocmpattdf boolean;
2126 --
2127 l_comp_obj_tree ben_manage_life_events.g_cache_proc_object_table;
2128 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
2129 l_init_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
2130 l_df_counts ben_efc_functions.g_attach_df_counts;
2131 l_per_row per_all_people_f%ROWTYPE;
2132 l_empasg_row per_all_assignments_f%ROWTYPE;
2133 l_benasg_row per_all_assignments_f%ROWTYPE;
2134 l_pil_row ben_per_in_ler%rowtype;
2135 l_comp_rec ben_derive_part_and_rate_facts.g_cache_structure;
2136 l_d_comp_rec ben_derive_part_and_rate_facts.g_cache_structure;
2137 l_oiplip_rec ben_derive_part_and_rate_facts.g_cache_structure;
2138 l_d_oiplip_rec ben_derive_part_and_rate_facts.g_cache_structure;
2139 --
2140 l_coent_scode varchar2(100);
2141 l_compobj_id number;
2142 l_pk1 number;
2143 --
2144 l_oipl_id number;
2145 l_oiplip_id number;
2146 l_prevbgp_id number;
2147 --
2148 CURSOR c_oipl
2149 (c_opt_id in number
2150 ,c_pl_id in number
2151 ,c_eff_date in date
2152 )
2153 IS
2154 select cop.oipl_id
2155 FROM ben_oipl_f cop
2156 where cop.opt_id = c_opt_id
2157 and cop.pl_id = c_pl_id
2158 and c_eff_date
2159 between cop.effective_start_date and cop.effective_end_date;
2160 --
2161 CURSOR c_oiplip
2162 (c_oipl_id in number
2163 ,c_plip_id in number
2164 ,c_eff_date in date
2165 )
2166 IS
2167 select opp.oiplip_id
2168 FROM ben_oiplip_f opp
2169 where opp.oipl_id = c_oipl_id
2170 and opp.plip_id = c_plip_id
2171 and c_eff_date
2172 between opp.effective_start_date and opp.effective_end_date;
2173 --
2174 CURSOR c_pbbdets
2175 (c_person_id in number
2176 ,c_eff_date in date
2177 )
2178 IS
2179 select pbb.per_bnfts_bal_id,
2180 pbb.last_update_date,
2181 pbb.object_version_number,
2182 pbb.effective_end_date
2183 FROM ben_per_bnfts_bal_f pbb
2184 where pbb.person_id = c_person_id
2185 order by pbb.per_bnfts_bal_id;
2186 --
2187 l_pbbdets c_pbbdets%rowtype;
2188 --
2189 CURSOR c_epopildtupd
2190 (c_per_in_ler_id in number
2191 ,c_elig_per_opt_id in number
2192 )
2193 IS
2194 select epo.elig_per_opt_id,
2195 count(*)
2196 FROM ben_elig_per_opt_f epo
2197 where epo.per_in_ler_id = c_per_in_ler_id
2198 and epo.elig_per_opt_id = c_elig_per_opt_id
2199 group by epo.elig_per_opt_id
2200 having count(*) > 1;
2201 --
2202 l_epopildtupd c_epopildtupd%rowtype;
2203 --
2204 l_faterr_code varchar2(100);
2205 l_faterr_type varchar2(100);
2206 l_adjfailed boolean;
2207 --
2208 begin
2209 --
2210 l_efc_batch := FALSE;
2211 --
2212 l_row_count := 0;
2213 l_calfail_count := 0;
2214 l_calsucc_count := 0;
2215 l_dupconv_count := 0;
2216 l_conv_count := 0;
2217 l_actconv_count := 0;
2218 l_unconv_count := 0;
2219 --
2220 l_rcoerr_count := 0;
2221 l_faterrs_count := 0;
2222 --
2223 g_epo_success_adj_val_set.delete;
2224 g_epo_failed_adj_val_set.delete;
2225 g_epo_rcoerr_val_set.delete;
2226 g_epo_fatal_error_val_set.delete;
2227 --
2228 l_mode := 'L';
2229 --
2230 -- Check if EFC process parameters are set
2231 --
2232 if p_action_id is not null
2233 and p_pk1 is not null
2234 and p_chunk is not null
2235 and p_efc_worker_id is not null
2236 then
2237 --
2238 l_efc_batch := TRUE;
2239 --
2240 end if;
2241 --
2242 l_from_str := ' FROM ben_elig_per_opt_f epo, '
2243 ||' ben_elig_per_f pep, '
2244 ||' ben_per_in_ler pil, '
2245 ||' per_all_people_f per ';
2246 --
2247 l_where_str := ' where epo.elig_per_id = pep.elig_per_id '
2248 ||' and epo.effective_start_date '
2249 ||' between pep.effective_start_date and pep.effective_end_date '
2250 ||' and epo.per_in_ler_id = pil.per_in_ler_id '
2251 ||' and epo.opt_id is not null '
2252 ||' and (epo.comp_ref_amt is not null '
2253 ||' or epo.rt_comp_ref_amt is not null) '
2254 ||' and pil.person_id = per.person_id '
2255 ||' and pil.LF_EVT_OCRD_DT '
2256 ||' between per.effective_start_date and per.effective_end_date ';
2257 --
2258 -- Check if we are restricting by business group
2259 --
2260 if p_business_group_id is not null then
2261 --
2262 l_where_str := l_where_str||' and epo.business_group_id = '||p_business_group_id;
2263 --
2264 end if;
2265 --
2266 -- Build in batch specific restrictions
2267 --
2268 if l_efc_batch then
2269 --
2270 l_from_str := l_from_str||', ben_elig_per_opt_f_efc efc ';
2271 l_where_str := l_where_str||' and efc.elig_per_opt_id = epo.elig_per_opt_id '
2272 ||' and efc.effective_start_date = epo.effective_start_date '
2273 ||' and efc.effective_end_date = epo.effective_end_date '
2274 ||' and efc.efc_action_id = :action_id '
2275 ||' and epo.elig_per_opt_id > :pk1 '
2276 ||' and mod(epo.elig_per_opt_id, :total_workers) = :worker_id ';
2277 --
2278 elsif p_valworker_id is not null
2279 and p_valtotal_workers is not null
2280 then
2281 --
2282 l_where_str := l_where_str||' and mod(epo.elig_per_opt_id, :valtotal_workers) = :valworker_id ';
2283 --
2284 end if;
2285 --
2286 l_sql_str := ' select epo.elig_per_opt_id, '
2287 ||' epo.effective_start_date, '
2288 ||' epo.effective_end_date, '
2289 ||' epo.comp_ref_amt, '
2290 ||' epo.rt_comp_ref_amt, '
2291 ||' pep.person_id, '
2292 ||' pep.pgm_id, '
2293 ||' pep.pl_id, '
2294 ||' epo.opt_id, '
2295 ||' pep.business_group_id, '
2296 ||' pil.lf_evt_ocrd_dt, '
2297 ||' epo.creation_date, '
2298 ||' epo.last_update_date, '
2299 ||' epo.object_version_number, '
2300 ||' epo.per_in_ler_id '
2301 ||l_from_str
2302 ||l_where_str
2303 ||' order by epo.elig_per_opt_id ';
2304 --
2305 if l_efc_batch then
2306 --
2307 hr_efc_info.insert_line('-- ');
2308 hr_efc_info.insert_line('-- Adjusting elig per options ');
2309 hr_efc_info.insert_line('-- ');
2310 --
2311 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
2312 --
2313 elsif p_valworker_id is not null
2314 and p_valtotal_workers is not null
2315 then
2316 --
2317 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
2318 --
2319 else
2320 --
2321 open c_efc_rows FOR l_sql_str;
2322 --
2323 end if;
2324 --
2325 loop
2326 FETCH c_efc_rows INTO l_efc_row;
2327 EXIT WHEN c_efc_rows%NOTFOUND;
2328 --
2329 l_fatal_error := FALSE;
2330 l_faterr_code := null;
2331 --
2332 -- WHO checks
2333 --
2334 ben_efc_adjustments.DetectWhoInfo
2335 (p_creation_date => l_efc_row.creation_date
2336 ,p_last_update_date => l_efc_row.last_update_date
2337 ,p_object_version_number => l_efc_row.object_version_number
2338 ,p_who_counts => l_who_counts
2339 ,p_faterr_code => l_faterr_code
2340 ,p_faterr_type => l_faterr_type
2341 );
2342 --
2343 -- Check for old data
2344 --
2345 if l_faterr_code is not null then
2346 --
2347 l_fatal_error := TRUE;
2348 --
2349 end if;
2350 --
2351 if not l_fatal_error then
2352 --
2353 -- Check for multiple DT instances for an EPO within a PIL
2354 --
2355 open c_epopildtupd
2356 (c_per_in_ler_id => l_efc_row.per_in_ler_id
2357 ,c_elig_per_opt_id => l_efc_row.elig_per_opt_id
2358 );
2359 fetch c_epopildtupd into l_epopildtupd;
2360 if c_epopildtupd%found then
2361 --
2362 l_faterr_code := 'EPOPILDTUPD';
2363 l_fatal_error := TRUE;
2364 --
2365 end if;
2366 close c_epopildtupd;
2367 --
2368 end if;
2369 --
2370 if not l_fatal_error then
2371 --
2372 ben_derive_part_and_rate_cache.clear_down_cache;
2373 ben_person_object.clear_down_cache;
2374 ben_pil_object.clear_down_cache;
2375 --
2376 if nvl(l_prevbgp_id,-9999) <> l_efc_row.business_group_id then
2377 --
2378 ben_manage_life_events.g_cache_proc_object.delete;
2379 --
2380 end if;
2381 --
2382 begin
2383 --
2384 -- Set up comp object list
2385 --
2386 ben_comp_object_list.build_comp_object_list
2387 (p_effective_date => l_efc_row.lf_evt_ocrd_dt
2388 ,p_business_group_id => l_efc_row.business_group_id
2389 ,p_mode => l_mode
2390 );
2391 --
2392 l_prevbgp_id := l_efc_row.business_group_id;
2393 l_comp_obj_tree := ben_manage_life_events.g_cache_proc_object;
2394 --
2395 -- Set comp object context values
2396 --
2397 l_comp_obj_tree_row := l_init_comp_obj_tree_row;
2398 --
2399 exception
2400 when others then
2401 --
2402 g_epo_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.elig_per_opt_id;
2403 g_epo_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
2404 g_epo_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
2405 g_epo_rcoerr_val_set(l_rcoerr_count).rco_name := 'BCOL';
2406 g_epo_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
2407 --
2408 l_rcoerr_count := l_rcoerr_count+1;
2409 l_fatal_error := TRUE;
2410 --
2411 end;
2412 --
2413 end if;
2414 --
2415 if l_comp_obj_tree.count > 0
2416 and not l_fatal_error
2417 then
2418 --
2419 for bcolele_num in l_comp_obj_tree.first..l_comp_obj_tree.last
2420 loop
2421 --
2422 if nvl(l_comp_obj_tree(bcolele_num).par_pgm_id,9999) = nvl(l_efc_row.pgm_id,9999)
2423 and nvl(l_comp_obj_tree(bcolele_num).par_pl_id,9999) = nvl(l_efc_row.pl_id,9999)
2424 and nvl(l_comp_obj_tree(bcolele_num).par_opt_id,9999) = nvl(l_efc_row.opt_id,9999)
2425 then
2426 --
2427 l_comp_obj_tree_row := l_comp_obj_tree(bcolele_num);
2428 --
2429 exit;
2430 --
2431 end if;
2432 --
2433 end loop;
2434 --
2435 -- Detect comp object list information
2436 --
2437 ben_efc_adjustments.DetectBCOLRowInfo
2438 (p_comp_obj_tree_row => l_comp_obj_tree_row
2439 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
2440 ,p_business_group_id => l_efc_row.business_group_id
2441 --
2442 ,p_faterr_code => l_faterr_code
2443 );
2444 --
2445 -- Check fatal BCOL errors
2446 --
2447 if l_faterr_code is not null
2448 then
2449 --
2450 l_fatal_error := TRUE;
2451 --
2452 end if;
2453 --
2454 end if;
2455 --
2456 if not l_fatal_error then
2457 --
2458 open c_oipl
2459 (c_opt_id => l_efc_row.opt_id
2460 ,c_pl_id => l_efc_row.pl_id
2461 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
2462 );
2463 fetch c_oipl into l_oipl_id;
2464 if c_oipl%found then
2465 --
2466 open c_oiplip
2467 (c_oipl_id => l_oipl_id
2468 ,c_plip_id => l_comp_obj_tree_row.par_plip_id
2469 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
2470 );
2471 fetch c_oiplip into l_oiplip_id;
2472 if c_oiplip%found then
2473 --
2474 l_oiplip_count := l_oiplip_count+1;
2475 --
2476 end if;
2477 close c_oiplip;
2478 --
2479 l_oipl_count := l_oipl_count+1;
2480 --
2481 -- Check for attached derived factors
2482 --
2483 ben_efc_functions.CompObject_ChkAttachDF
2484 (p_coent_scode => 'COP'
2485 ,p_compobj_id => l_oipl_id
2486 --
2487 ,p_counts => l_df_counts
2488 );
2489 --
2490 if l_df_counts.noattdf_count = 0 then
2491 --
2492 l_faterr_code := 'NOATTDF';
2493 l_fatal_error := TRUE;
2494 --
2495 end if;
2496 --
2497 end if;
2498 close c_oipl;
2499 --
2500 end if;
2501 --
2502 if not l_fatal_error then
2503 --
2504 -- Set up benefits environment
2505 --
2506 ben_env_object.init
2507 (p_business_group_id => l_efc_row.business_group_id
2508 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
2509 ,p_thread_id => 1
2510 ,p_chunk_size => 10
2511 ,p_threads => 1
2512 ,p_max_errors => 100
2513 ,p_benefit_action_id => 99999
2514 ,p_audit_log_flag => 'N'
2515 );
2516 --
2517 -- Get person info
2518 --
2519 ben_person_object.get_object
2520 (p_person_id => l_efc_row.person_id
2521 ,p_rec => l_per_row
2522 );
2523 --
2524 ben_person_object.get_object
2525 (p_person_id => l_efc_row.person_id
2526 ,p_rec => l_empasg_row
2527 );
2528 --
2529 ben_person_object.get_benass_object
2530 (p_person_id => l_efc_row.person_id
2531 ,p_rec => l_benasg_row
2532 );
2533 --
2534 ben_person_object.get_object
2535 (p_person_id => l_efc_row.person_id
2536 ,p_rec => l_pil_row
2537 );
2538 --
2539 -- Check for modified person benefit balances
2540 --
2541 for row in c_pbbdets
2542 (c_person_id => l_efc_row.person_id
2543 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
2544 )
2545 loop
2546 --
2547 -- Check for a modified PBB since the EPO was last updated
2548 --
2549 if row.last_update_date > l_efc_row.last_update_date then
2550 --
2551 l_fatal_error := TRUE;
2552 l_faterr_code := 'PBBEPOCORR';
2553 exit;
2554 --
2555 end if;
2556 --
2557 -- Check for a end dated PBB
2558 --
2559 if row.effective_end_date <> hr_api.g_eot
2560 then
2561 --
2562 l_fatal_error := TRUE;
2563 l_faterr_code := 'PBBENDDATE';
2564 exit;
2565 --
2566 end if;
2567 --
2568 end loop;
2569 --
2570 if not l_fatal_error then
2571 --
2572 l_comp_rec:=l_d_comp_rec;
2573 l_oiplip_rec:=l_d_oiplip_rec;
2574 --
2575 begin
2576 --
2577 ben_derive_part_and_rate_facts.derive_rates_and_factors
2578 (p_comp_obj_tree_row => l_comp_obj_tree_row
2579 --
2580 -- Context info
2581 --
2582 ,p_per_row => l_per_row
2583 ,p_empasg_row => l_empasg_row
2584 ,p_benasg_row => l_benasg_row
2585 ,p_pil_row => l_pil_row
2586 --
2587 ,p_mode => l_mode
2588 --
2589 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
2590 ,p_lf_evt_ocrd_dt => l_efc_row.lf_evt_ocrd_dt
2591 ,p_person_id => l_efc_row.person_id
2592 ,p_business_group_id => l_efc_row.business_group_id
2593 ,p_pgm_id => l_comp_obj_tree_row.pgm_id
2594 ,p_pl_id => l_comp_obj_tree_row.pl_id
2595 ,p_oipl_id => l_comp_obj_tree_row.oipl_id
2596 ,p_plip_id => l_comp_obj_tree_row.plip_id
2597 ,p_ptip_id => l_comp_obj_tree_row.ptip_id
2598 --
2599 ,p_comp_rec => l_comp_rec
2600 ,p_oiplip_rec => l_oiplip_rec
2601 );
2602 --
2603 if nvl(l_comp_rec.comp_ref_amt,9999) <> nvl(l_efc_row.comp_ref_amt,9999)
2604 then
2605 --
2606 g_epo_failed_adj_val_set(l_calfail_count).id := l_efc_row.elig_per_opt_id;
2607 g_epo_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
2608 g_epo_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
2609 g_epo_failed_adj_val_set(l_calfail_count).old_val1 := l_efc_row.comp_ref_amt;
2610 g_epo_failed_adj_val_set(l_calfail_count).new_val1 := l_comp_rec.comp_ref_amt;
2611 g_epo_failed_adj_val_set(l_calfail_count).val_type := 'EPO_CRAMT';
2612 g_epo_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
2613 g_epo_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
2614 --
2615 l_adjfailed := TRUE;
2616 l_calfail_count := l_calfail_count+1;
2617 --
2618 elsif nvl(l_comp_rec.rt_comp_ref_amt,9999) <> nvl(l_efc_row.rt_comp_ref_amt,9999)
2619 then
2620 --
2621 g_epo_failed_adj_val_set(l_calfail_count).id := l_efc_row.elig_per_opt_id;
2622 g_epo_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
2623 g_epo_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
2624 g_epo_failed_adj_val_set(l_calfail_count).old_val1 := l_efc_row.rt_comp_ref_amt;
2625 g_epo_failed_adj_val_set(l_calfail_count).new_val1 := l_comp_rec.rt_comp_ref_amt;
2626 g_epo_failed_adj_val_set(l_calfail_count).val_type := 'EPO_RTCRAMT';
2627 g_epo_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
2628 g_epo_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
2629 --
2630 l_adjfailed := TRUE;
2631 l_calfail_count := l_calfail_count+1;
2632 --
2633 else
2634 --
2635 l_adjfailed := FALSE;
2636 --
2637 end if;
2638 --
2639 if l_efc_batch
2640 and l_faterr_code is null
2641 then
2642 --
2643 update ben_elig_per_opt_f epo
2644 set epo.comp_ref_amt = l_comp_rec.comp_ref_amt,
2645 epo.rt_comp_ref_amt = l_comp_rec.rt_comp_ref_amt
2646 where epo.elig_per_opt_id = l_efc_row.elig_per_opt_id
2647 and epo.effective_start_date = l_efc_row.effective_start_date
2648 and epo.effective_end_date = l_efc_row.effective_end_date;
2649 --
2650 if p_validate then
2651 --
2652 rollback;
2653 --
2654 end if;
2655 --
2656 -- Check for end of chunk and commit if necessary
2657 --
2658 l_pk1 := l_efc_row.elig_per_opt_id;
2659 --
2660 ben_efc_functions.maintain_chunks
2661 (p_row_count => l_row_count
2662 ,p_pk1 => l_pk1
2663 ,p_chunk_size => p_chunk
2664 ,p_efc_worker_id => p_efc_worker_id
2665 );
2666 --
2667 end if;
2668 --
2669 exception
2670 when others then
2671 --
2672 g_epo_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.elig_per_opt_id;
2673 g_epo_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
2674 g_epo_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
2675 g_epo_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENDRPAR';
2676 g_epo_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
2677 g_epo_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
2678 g_epo_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
2679 --
2680 l_rcoerr_count := l_rcoerr_count+1;
2681 --
2682 end;
2683 --
2684 end if;
2685 --
2686 end if;
2687 --
2688 -- Check for fatal errors
2689 --
2690 if l_faterr_code is not null
2691 then
2692 --
2693 g_epo_fatal_error_val_set(l_faterrs_count).id := l_efc_row.elig_per_opt_id;
2694 g_epo_fatal_error_val_set(l_faterrs_count).esd := l_efc_row.effective_start_date;
2695 g_epo_fatal_error_val_set(l_faterrs_count).eed := l_efc_row.effective_end_date;
2696 g_epo_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
2697 g_epo_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
2698 g_epo_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
2699 --
2700 l_faterrs_count := l_faterrs_count+1;
2701 --
2702 elsif l_faterr_code is null
2703 and not l_adjfailed
2704 then
2705 --
2706 g_epo_success_adj_val_set(l_calsucc_count).id := l_efc_row.elig_per_opt_id;
2707 g_epo_success_adj_val_set(l_calsucc_count).esd := l_efc_row.effective_start_date;
2708 g_epo_success_adj_val_set(l_calsucc_count).eed := l_efc_row.effective_end_date;
2709 g_epo_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
2710 g_epo_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
2711 --
2712 l_calsucc_count := l_calsucc_count+1;
2713 --
2714 end if;
2715 --
2716 l_row_count := l_row_count+1;
2717 --
2718 end loop;
2719 CLOSE c_efc_rows;
2720 --
2721 -- Check that all rows have been converted or excluded
2722 --
2723 ben_efc_functions.conv_check
2724 (p_table_name => 'ben_elig_per_opt_f'
2725 ,p_efctable_name => 'ben_elig_per_opt_f_efc'
2726 ,p_tabwhere_clause => ' (comp_ref_amt is not null '
2727 ||' or rt_comp_ref_amt is not null) '
2728 --
2729 ,p_bgp_id => p_business_group_id
2730 ,p_action_id => p_action_id
2731 --
2732 ,p_conv_count => l_conv_count
2733 ,p_unconv_count => l_unconv_count
2734 ,p_tabrow_count => l_tabrow_count
2735 );
2736 --
2737 -- Set counts
2738 --
2739 if p_action_id is null then
2740 --
2741 l_actconv_count := 0;
2742 --
2743 else
2744 --
2745 l_actconv_count := l_conv_count;
2746 --
2747 end if;
2748 --
2749 p_adjustment_counts.efcrow_count := l_row_count;
2750 p_adjustment_counts.tabrow_count := l_tabrow_count;
2751 p_adjustment_counts.calfail_count := l_calfail_count;
2752 p_adjustment_counts.calsucc_count := l_calsucc_count;
2753 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
2754 p_adjustment_counts.dupconv_count := l_dupconv_count;
2755 p_adjustment_counts.conv_count := l_conv_count;
2756 p_adjustment_counts.actconv_count := l_actconv_count;
2757 p_adjustment_counts.unconv_count := l_unconv_count;
2758 --
2759 end epo_adjustments;
2760 --
2761 procedure enb_adjustments
2762 (p_validate in boolean default false
2763 ,p_worker_id in number default null
2764 ,p_action_id in number default null
2765 ,p_total_workers in number default null
2766 ,p_pk1 in number default null
2767 ,p_chunk in number default null
2768 ,p_efc_worker_id in number default null
2769 --
2770 ,p_valworker_id in number default null
2771 ,p_valtotal_workers in number default null
2772 --
2773 ,p_business_group_id in number default null
2774 --
2775 ,p_adjustment_counts out nocopy g_adjustment_counts
2776 )
2777 is
2778 --
2779 TYPE cur_type IS REF CURSOR;
2780 --
2781 type g_efc_row is record
2782 (enrt_bnft_id ben_enrt_bnft.enrt_bnft_id%type
2783 ,elig_per_elctbl_chc_id ben_enrt_bnft.elig_per_elctbl_chc_id%type
2784 ,business_group_id ben_enrt_bnft.business_group_id%type
2785 ,mx_wout_ctfn_val ben_enrt_bnft.mx_wout_ctfn_val%type
2786 ,mn_val ben_enrt_bnft.mn_val%type
2787 ,mx_val ben_enrt_bnft.mx_val%type
2788 ,incrmt_val ben_enrt_bnft.incrmt_val%type
2789 ,dflt_val ben_enrt_bnft.dflt_val%type
2790 ,val ben_enrt_bnft.val%type
2791 ,pgm_id ben_elig_per_elctbl_chc.pgm_id%type
2792 ,pl_id ben_elig_per_elctbl_chc.pl_id%type
2793 ,pl_typ_id ben_elig_per_elctbl_chc.pl_typ_id%type
2794 ,ptip_id ben_elig_per_elctbl_chc.ptip_id%type
2795 ,plip_id ben_elig_per_elctbl_chc.plip_id%type
2796 ,oipl_id ben_elig_per_elctbl_chc.oipl_id%type
2797 ,prtt_enrt_rslt_id ben_elig_per_elctbl_chc.prtt_enrt_rslt_id%type
2798 ,epe_ovn ben_elig_per_elctbl_chc.object_version_number%type
2799 ,elctbl_flag ben_elig_per_elctbl_chc.elctbl_flag%type
2800 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
2801 ,person_id ben_per_in_ler.person_id%type
2802 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
2803 ,ler_id ben_per_in_ler.ler_id%type
2804 ,last_update_date ben_enrt_bnft.last_update_date%type
2805 ,creation_date ben_enrt_bnft.creation_date%type
2806 ,object_version_number ben_enrt_bnft.object_version_number%type
2807 ,cvg_mlt_cd ben_enrt_bnft.cvg_mlt_cd%type
2808 );
2809 --
2810 c_efc_rows cur_type;
2811 --
2812 l_proc varchar2(1000) := 'enb_adjustments';
2813 --
2814 l_efc_row g_efc_row;
2815 --
2816 l_who_counts g_who_counts;
2817 --
2818 l_cvg_set ben_cvg_cache.g_epeplncvg_cache;
2819 --
2820 l_enb_valrow ben_determine_coverage.ENBValType;
2821 l_perasg gc_perasg%rowtype;
2822 l_vpfdets gc_vpfdets%rowtype;
2823 --
2824 l_currepe_row ben_determine_rates.g_curr_epe_rec;
2825 --
2826 l_row_count pls_integer;
2827 l_calfail_count pls_integer;
2828 l_calsucc_count pls_integer;
2829 l_conv_count pls_integer;
2830 l_unconv_count pls_integer;
2831 l_actconv_count pls_integer;
2832 l_dupconv_count pls_integer;
2833 --
2834 l_rcoerr_count pls_integer;
2835 l_faterrs_count pls_integer;
2836 --
2837 l_tabrow_count pls_integer;
2838 --
2839 l_sql_str long;
2840 l_from_str long;
2841 l_where_str long;
2842 --
2843 l_efc_batch boolean;
2844 --
2845 l_pk1 number;
2846 --
2847 l_detected boolean;
2848 l_olddata boolean;
2849 l_comp_value number;
2850 l_faterr_code varchar2(100);
2851 l_faterr_type varchar2(100);
2852 --
2853 l_adjfailed boolean;
2854 --
2855 l_val_type varchar2(100);
2856 l_old_val1 number;
2857 l_new_val1 number;
2858 l_vpf_id number;
2859 --
2860 l_enb_uom varchar2(100);
2861 --
2862 cursor c_pbb_dets
2863 (c_person_id in number
2864 ,c_bnfts_bal_id in number
2865 ,c_eff_date in date
2866 )
2867 is
2868 select pbb.val,
2869 bnb.name,
2870 bnb.uom
2871 from ben_per_bnfts_bal_f pbb,
2872 ben_bnfts_bal_f bnb
2873 where pbb.person_id = c_person_id
2874 and pbb.bnfts_bal_id = bnb.bnfts_bal_id
2875 and c_eff_date
2876 between bnb.effective_start_date
2877 and bnb.effective_end_date
2878 and c_eff_date
2879 between pbb.effective_start_date
2880 and pbb.effective_end_date
2881 and pbb.bnfts_bal_id = c_bnfts_bal_id;
2882 --
2883 l_pbb_dets c_pbb_dets%rowtype;
2884 --
2885 cursor c_clf_dets
2886 (c_clf_id in number
2887 )
2888 is
2889 select clf.comp_src_cd,
2890 clf.bnfts_bal_id
2891 from ben_comp_lvl_fctr clf
2892 where clf.comp_lvl_fctr_id = c_clf_id;
2893 --
2894 l_clf_dets c_clf_dets%rowtype;
2895 --
2896 cursor c_ccm_dets
2897 (c_ccm_id in number
2898 ,c_eff_date in date
2899 )
2900 is
2901 select ccm.creation_date,
2902 ccm.last_update_date,
2903 ccm.object_version_number
2904 from ben_cvg_amt_calc_mthd_f ccm
2905 where ccm.cvg_amt_calc_mthd_id = c_ccm_id
2906 and c_eff_date
2907 between ccm.effective_start_date and ccm.effective_end_date;
2908 --
2909 l_ccm_dets c_ccm_dets%rowtype;
2910 --
2911 cursor c_cop_dets
2912 (c_cop_id in number
2913 ,c_eff_date in date
2914 )
2915 is
2916 select cop.creation_date,
2917 cop.last_update_date,
2918 cop.object_version_number
2919 from ben_oipl_f cop
2920 where cop.oipl_id = c_cop_id
2921 and c_eff_date
2922 between cop.effective_start_date and cop.effective_end_date;
2923 --
2924 l_cop_dets c_cop_dets%rowtype;
2925 --
2926 cursor c_pln_dets
2927 (c_pln_id in number
2928 ,c_eff_date in date
2929 )
2930 is
2931 select pln.creation_date,
2932 pln.last_update_date,
2933 pln.object_version_number
2934 from ben_pl_f pln
2935 where pln.pl_id = c_pln_id
2936 and c_eff_date
2937 between pln.effective_start_date and pln.effective_end_date;
2938 --
2939 l_pln_dets c_pln_dets%rowtype;
2940 --
2941 cursor c_asgpppdets
2942 (c_per_id in number
2943 ,c_eff_date in date
2944 )
2945 is
2946 select ppp.PAY_PROPOSAL_ID
2947 from per_pay_proposals ppp,
2948 per_assignments_f asg
2949 where ppp.assignment_id = asg.assignment_id
2950 and c_eff_date
2951 between asg.effective_start_date and asg.effective_end_date
2952 and asg.person_id = c_per_id;
2953 --
2954 l_asgpppdets c_asgpppdets%rowtype;
2955 --
2956 cursor c_pppdets
2957 (c_person_id in number
2958 ,c_eff_date in date
2959 )
2960 is
2961 select ppp.proposed_salary_n proposed_salary,
2962 ppb.pay_basis,
2963 ppb.pay_annualization_factor,
2964 paf.period_type payroll,
2965 asg.normal_hours,
2966 asg.frequency
2967 from per_pay_proposals ppp,
2968 per_assignments_f asg,
2969 per_pay_bases ppb,
2970 pay_all_payrolls_f paf,
2971 per_all_people_f per
2972 where per.person_id = c_person_id
2973 and asg.assignment_type <> 'C'
2974 and asg.person_id = per.person_id
2975 and asg.primary_flag = 'Y'
2976 and ppb.pay_basis_id = asg.pay_basis_id
2977 and asg.payroll_id = paf.payroll_id
2978 and c_eff_date
2979 between asg.effective_start_date
2980 and asg.effective_end_date
2981 and c_eff_date
2982 between per.effective_start_date
2983 and per.effective_end_date
2984 and asg.assignment_id = ppp.assignment_id
2985 and ppp.change_date <= c_eff_date
2986 order by ppp.change_date desc;
2987 --
2988 l_pppdets c_pppdets%rowtype;
2989 --
2990 begin
2991 --
2992 l_efc_batch := FALSE;
2993 --
2994 l_row_count := 0;
2995 l_calfail_count := 0;
2996 l_calsucc_count := 0;
2997 l_dupconv_count := 0;
2998 l_conv_count := 0;
2999 l_actconv_count := 0;
3000 l_unconv_count := 0;
3001 l_rcoerr_count := 0;
3002 l_faterrs_count := 0;
3003 --
3004 g_enb_success_adj_val_set.delete;
3005 g_enb_failed_adj_val_set.delete;
3006 g_enb_rcoerr_val_set.delete;
3007 g_enb_fatal_error_val_set.delete;
3008 --
3009 -- Check if EFC process parameters are set
3010 --
3011 if p_action_id is not null
3012 and p_pk1 is not null
3013 and p_chunk is not null
3014 and p_efc_worker_id is not null
3015 then
3016 --
3017 l_efc_batch := TRUE;
3018 --
3019 end if;
3020 --
3021 l_from_str := ' FROM ben_enrt_bnft enb, '
3022 ||' BEN_ELIG_PER_ELCTBL_CHC epe, '
3023 ||' ben_per_in_ler pil, '
3024 ||' per_all_people_f per ';
3025 --
3026 l_where_str := ' where enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id '
3027 ||' and epe.per_in_ler_id = pil.per_in_ler_id '
3028 ||' and pil.person_id = per.person_id '
3029 ||' and pil.LF_EVT_OCRD_DT '
3030 ||' between per.effective_start_date and per.effective_end_date '
3031 /* Exclude flat amounts */
3032 ||' and enb.cvg_mlt_cd not in ('||''''||'FLFX'||''''||') '
3033 ||' and (enb.mx_wout_ctfn_val is not null '
3034 ||' or enb.mn_val is not null '
3035 ||' or enb.mx_val is not null '
3036 ||' or enb.incrmt_val is not null '
3037 ||' or enb.dflt_val is not null '
3038 ||' or enb.val is not null '
3039 ||' ) ';
3040 --
3041 -- Check if we are restricting by business group
3042 --
3043 if p_business_group_id is not null then
3044 --
3045 l_where_str := l_where_str||' and enb.business_group_id = '||p_business_group_id
3046 ||' and pil.business_group_id = '||p_business_group_id;
3047 --
3048 end if;
3049 --
3050 -- Build in batch specific restrictions
3051 --
3052 if l_efc_batch then
3053 --
3054 l_from_str := l_from_str||', ben_enrt_bnft_efc efc ';
3055 l_where_str := l_where_str||' and efc.enrt_bnft_id = enb.enrt_bnft_id '
3056 ||' and efc.efc_action_id = :action_id '
3057 ||' and enb.enrt_bnft_id > :pk1 '
3058 ||' and mod(enb.enrt_bnft_id, :total_workers) = :worker_id ';
3059 --
3060 elsif p_valworker_id is not null
3061 and p_valtotal_workers is not null
3062 then
3063 --
3064 l_where_str := l_where_str||' and mod(enb.enrt_bnft_id, :valtotal_workers) = :valworker_id ';
3065 --
3066 end if;
3067 --
3068 l_sql_str := ' select enb.enrt_bnft_id, '
3069 ||' enb.elig_per_elctbl_chc_id, '
3070 ||' enb.business_group_id, '
3071 ||' enb.mx_wout_ctfn_val, '
3072 ||' enb.mn_val, '
3073 ||' enb.mx_val, '
3074 ||' enb.incrmt_val, '
3075 ||' enb.dflt_val, '
3076 ||' enb.val, '
3077 ||' epe.pgm_id, '
3078 ||' epe.pl_id, '
3079 ||' epe.pl_typ_id, '
3080 ||' epe.ptip_id, '
3081 ||' epe.plip_id, '
3082 ||' epe.oipl_id, '
3083 ||' epe.prtt_enrt_rslt_id, '
3084 ||' epe.object_version_number epe_ovn, '
3085 ||' epe.elctbl_flag, '
3086 ||' pil.per_in_ler_id, '
3087 ||' pil.person_id, '
3088 ||' pil.lf_evt_ocrd_dt, '
3089 ||' pil.ler_id, '
3090 ||' enb.last_update_date, '
3091 ||' enb.creation_date, '
3092 ||' enb.object_version_number, '
3093 ||' enb.cvg_mlt_cd '
3094 ||l_from_str
3095 ||l_where_str
3096 ||' order by enb.enrt_bnft_id ';
3097 --
3098 if l_efc_batch then
3099 --
3100 hr_efc_info.insert_line('-- ');
3101 hr_efc_info.insert_line('-- Adjusting coverages ');
3102 hr_efc_info.insert_line('-- ');
3103 --
3104 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
3105 --
3106 elsif p_valworker_id is not null
3107 and p_valtotal_workers is not null
3108 then
3109 --
3110 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
3111 --
3112 else
3113 --
3114 open c_efc_rows FOR l_sql_str;
3115 --
3116 end if;
3117 --
3118 loop
3119 FETCH c_efc_rows INTO l_efc_row;
3120 EXIT WHEN c_efc_rows%NOTFOUND;
3121 --
3122 l_faterr_code := null;
3123 l_adjfailed := FALSE;
3124 --
3125 -- Check for modifications
3126 --
3127 if l_faterr_code is null then
3128 --
3129 -- Check that the assignment is valid
3130 --
3131 ben_efc_adjustments.DetectInvAsg
3132 (p_person_id => l_efc_row.person_id
3133 ,p_eff_date => l_efc_row.lf_evt_ocrd_dt
3134 --
3135 ,p_perasg => l_perasg
3136 ,p_noasgpay => l_detected
3137 );
3138 --
3139 if l_detected then
3140 --
3141 l_faterr_code := 'NOASGPAY';
3142 l_faterr_type := 'MISSINGSETUP';
3143 --
3144 end if;
3145 --
3146 end if;
3147 --
3148 -- Check if the plan has been modified
3149 --
3150 if l_faterr_code is null
3151 then
3152 --
3153 open c_pln_dets
3154 (c_pln_id => l_efc_row.pl_id
3155 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3156 );
3157 fetch c_pln_dets into l_pln_dets;
3158 if c_pln_dets%notfound then
3159 --
3160 l_faterr_code := 'NODTPLN';
3161 --
3162 end if;
3163 close c_pln_dets;
3164 --
3165 end if;
3166 --
3167 -- Check if the oipl has been modified
3168 --
3169 if l_efc_row.oipl_id is not null
3170 and l_faterr_code is null
3171 then
3172 --
3173 open c_cop_dets
3174 (c_cop_id => l_efc_row.oipl_id
3175 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3176 );
3177 fetch c_cop_dets into l_cop_dets;
3178 if c_cop_dets%notfound then
3179 --
3180 l_faterr_code := 'NODTCOP';
3181 l_faterr_type := 'DELETEDINFO';
3182 --
3183 end if;
3184 close c_cop_dets;
3185 --
3186 end if;
3187 --
3188 -- Check if the compensation is 0
3189 --
3190 if l_faterr_code is null then
3191 --
3192 -- Set up benefits environment
3193 --
3194 ben_env_object.init
3195 (p_business_group_id => l_efc_row.business_group_id
3196 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3197 ,p_thread_id => 1
3198 ,p_chunk_size => 10
3199 ,p_threads => 1
3200 ,p_max_errors => 100
3201 ,p_benefit_action_id => 99999
3202 ,p_audit_log_flag => 'N'
3203 );
3204 --
3205 -- Get the comp level details
3206 --
3207 ben_cvg_cache.epecobjtree_getcvgdets
3208 (p_epe_id => l_efc_row.elig_per_elctbl_chc_id
3209 ,p_epe_pl_id => l_efc_row.pl_id
3210 ,p_epe_plip_id => l_efc_row.plip_id
3211 ,p_epe_oipl_id => l_efc_row.oipl_id
3212 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3213 --
3214 ,p_cvg_set => l_cvg_set
3215 );
3216 --
3217 -- Check if coverage details found
3218 --
3219 if l_cvg_set.count = 0 then
3220 --
3221 l_faterr_code := 'NOEPECCM';
3222 --
3223 else
3224 --
3225 -- Get CCM details
3226 --
3227 open c_ccm_dets
3228 (c_ccm_id => l_cvg_set(0).cvg_amt_calc_mthd_id
3229 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3230 );
3231 fetch c_ccm_dets into l_ccm_dets;
3232 close c_ccm_dets;
3233 --
3234 -- Get comp level factor details
3235 --
3236 if l_cvg_set(0).comp_lvl_fctr_id is not null
3237 and l_faterr_code is null
3238 then
3239 --
3240 open c_clf_dets
3241 (c_clf_id => l_cvg_set(0).comp_lvl_fctr_id
3242 );
3243 fetch c_clf_dets into l_clf_dets;
3244 if c_clf_dets%notfound then
3245 --
3246 l_faterr_code := 'NOCLF';
3247 --
3248 end if;
3249 close c_clf_dets;
3250 --
3251 end if;
3252 --
3253 -- Check multiple codes
3254 --
3255 if l_cvg_set(0).cvg_mlt_cd in ('CL','CLRNG','FLFXPCL','FLPCLRNG','CLPFLRNG')
3256 and l_faterr_code is null
3257 then
3258 --
3259 -- Check person salary information for STTDCOMP CLF
3260 --
3261 if l_faterr_code is null
3262 and l_clf_dets.comp_src_cd = 'STTDCOMP'
3263 then
3264 --
3265 open c_pppdets
3266 (c_person_id => l_efc_row.person_id
3267 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3268 );
3269 fetch c_pppdets into l_pppdets;
3270 if c_pppdets%notfound then
3271 --
3272 l_faterr_code := 'NODTSALDETS';
3273 --
3274 end if;
3275 close c_pppdets;
3276 --
3277 end if;
3278 --
3279 if l_faterr_code is null then
3280 --
3281 ben_derive_factors.determine_compensation
3282 (p_comp_lvl_fctr_id => l_cvg_set(0).comp_lvl_fctr_id
3283 ,p_person_id => l_efc_row.person_id
3284 ,p_pgm_id => null
3285 ,p_pl_id => l_efc_row.pl_id
3286 ,p_oipl_id => l_efc_row.oipl_id
3287 ,p_per_in_ler_id => l_efc_row.per_in_ler_id
3288 ,p_business_group_id => l_efc_row.business_group_id
3289 ,p_perform_rounding_flg => true
3290 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3291 ,p_lf_evt_ocrd_dt => l_efc_row.lf_evt_ocrd_dt
3292 ,p_calc_bal_to_date => null
3293 ,p_value => l_comp_value
3294 );
3295 --
3296 if l_comp_value = 0 then
3297 --
3298 l_faterr_code := 'ZEROCOMPVAL';
3299 --
3300 end if;
3301 --
3302 end if;
3303 --
3304 -- Check that a person benefits balance exists when the CLF
3305 -- source code is BNFTBALTYP
3306 --
3307 if l_clf_dets.comp_src_cd = 'BNFTBALTYP' then
3308 --
3309 -- Get the PBB
3310 --
3311 open c_pbb_dets
3312 (c_person_id => l_efc_row.person_id
3313 ,c_bnfts_bal_id => l_clf_dets.bnfts_bal_id
3314 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3315 );
3316 fetch c_pbb_dets into l_pbb_dets;
3317 if c_pbb_dets%notfound then
3318 --
3319 l_faterr_code := 'NODTPBB';
3320 l_faterr_type := 'MISSINGSETUP';
3321 --
3322 end if;
3323 close c_pbb_dets;
3324 --
3325 if l_pbb_dets.uom is null
3326 and l_faterr_code is null
3327 then
3328 --
3329 l_faterr_code := 'NULLUOM';
3330 l_faterr_type := 'MISSINGSETUP';
3331 --
3332 end if;
3333 --
3334 if nvl(l_pbb_dets.uom,'ZZZZ') = 'EUR'
3335 and l_faterr_code is null
3336 then
3337 --
3338 l_faterr_code := 'EUROUOM';
3339 l_faterr_type := 'VALIDEXCLUSION';
3340 --
3341 end if;
3342 --
3343 end if;
3344 --
3345 elsif l_cvg_set(0).cvg_mlt_cd in ('FLFX')
3346 then
3347 --
3348 l_faterr_code := 'CCMFLFX';
3349 l_faterr_type := 'VALIDEXCLUSION';
3350 --
3351 end if;
3352 --
3353 end if;
3354 --
3355 end if;
3356 --
3357 if l_faterr_code is null then
3358 --
3359 -- Set up benefits environment
3360 --
3361 ben_env_object.init
3362 (p_business_group_id => l_efc_row.business_group_id
3363 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3364 ,p_thread_id => 1
3365 ,p_chunk_size => 10
3366 ,p_threads => 1
3367 ,p_max_errors => 100
3368 ,p_benefit_action_id => 99999
3369 ,p_audit_log_flag => 'N'
3370 );
3371 --
3372 -- Set up electable choice context
3373 --
3374 ben_epe_cache.g_currcobjepe_row.pl_id := l_efc_row.pl_id;
3375 ben_epe_cache.g_currcobjepe_row.plip_id := l_efc_row.plip_id;
3376 ben_epe_cache.g_currcobjepe_row.oipl_id := l_efc_row.oipl_id;
3377 --
3378 -- Add a row to fnd sessions
3379 --
3380 Insert_fndsession_row
3381 (p_ses_date => l_efc_row.lf_evt_ocrd_dt
3382 );
3383 --
3384 begin
3385 --
3386 begin
3387 --
3388 ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id := l_efc_row.elig_per_elctbl_chc_id;
3389 ben_epe_cache.g_currcobjepe_row.business_group_id := l_efc_row.business_group_id;
3390 ben_epe_cache.g_currcobjepe_row.pgm_id := l_efc_row.pgm_id;
3391 ben_epe_cache.g_currcobjepe_row.pl_id := l_efc_row.pl_id;
3392 ben_epe_cache.g_currcobjepe_row.pl_typ_id := l_efc_row.pl_typ_id;
3393 ben_epe_cache.g_currcobjepe_row.ptip_id := l_efc_row.ptip_id;
3394 ben_epe_cache.g_currcobjepe_row.plip_id := l_efc_row.plip_id;
3395 ben_epe_cache.g_currcobjepe_row.oipl_id := l_efc_row.oipl_id;
3396 ben_epe_cache.g_currcobjepe_row.prtt_enrt_rslt_id := l_efc_row.prtt_enrt_rslt_id;
3397 ben_epe_cache.g_currcobjepe_row.ler_id := l_efc_row.ler_id;
3398 ben_epe_cache.g_currcobjepe_row.object_version_number := l_efc_row.epe_ovn;
3399 ben_epe_cache.g_currcobjepe_row.elctbl_flag := l_efc_row.elctbl_flag;
3400 ben_epe_cache.g_currcobjepe_row.per_in_ler_id := l_efc_row.per_in_ler_id;
3401 --
3402 ben_determine_coverage.main
3403 (p_calculate_only_mode => TRUE
3404 ,p_elig_per_elctbl_chc_id => l_efc_row.elig_per_elctbl_chc_id
3405 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3406 ,p_lf_evt_ocrd_dt => l_efc_row.lf_evt_ocrd_dt
3407 ,p_perform_rounding_flg => TRUE
3408 --
3409 ,p_enb_valrow => l_enb_valrow
3410 );
3411 --
3412 exception
3413 when ben_manage_life_events.g_record_error then
3414 --
3415 fnd_message.raise_error;
3416 --
3417 end;
3418 --
3419 if nvl(l_efc_row.val,9999) <> nvl(l_enb_valrow.val,9999)
3420 then
3421 --
3422 l_val_type := 'ENB_VAL';
3423 l_old_val1 := l_efc_row.val;
3424 --
3425 if l_efc_row.val is null then
3426 --
3427 l_enb_valrow.val := null;
3428 l_new_val1 := null;
3429 l_adjfailed := FALSE;
3430 --
3431 else
3432 --
3433 l_new_val1 := l_enb_valrow.val;
3434 l_adjfailed := TRUE;
3435 --
3436 end if;
3437 --
3438 end if;
3439 --
3440 if nvl(l_efc_row.mn_val,9999) <> nvl(l_enb_valrow.mn_val,9999)
3441 and not l_adjfailed
3442 then
3443 --
3444 l_val_type := 'ENB_MNVAL';
3445 l_old_val1 := l_efc_row.mn_val;
3446 --
3447 if l_efc_row.mn_val is null then
3448 --
3449 l_enb_valrow.mn_val := null;
3450 l_new_val1 := null;
3451 l_adjfailed := FALSE;
3452 --
3453 else
3454 --
3455 l_new_val1 := l_enb_valrow.mn_val;
3456 l_adjfailed := TRUE;
3457 --
3458 end if;
3459 --
3460 end if;
3461 --
3462 if nvl(l_efc_row.mx_val,9999) <> nvl(l_enb_valrow.mx_val,9999)
3463 and not l_adjfailed
3464 then
3465 --
3466 l_val_type := 'ENB_MXVAL';
3467 l_old_val1 := l_efc_row.mx_val;
3468 --
3469 if l_efc_row.mx_val is null then
3470 --
3471 l_enb_valrow.mx_val := null;
3472 l_new_val1 := null;
3473 l_adjfailed := FALSE;
3474 --
3475 else
3476 --
3477 l_new_val1 := l_enb_valrow.mx_val;
3478 l_adjfailed := TRUE;
3479 --
3480 end if;
3481 --
3482 end if;
3483 --
3484 if nvl(l_efc_row.incrmt_val,9999) <> nvl(l_enb_valrow.incrmt_val,9999)
3485 and not l_adjfailed
3486 then
3487 --
3488 l_val_type := 'ENB_INCRMTVAL';
3489 l_old_val1 := l_efc_row.incrmt_val;
3490 --
3491 if l_efc_row.incrmt_val is null then
3492 --
3493 l_enb_valrow.incrmt_val := null;
3494 l_new_val1 := null;
3495 l_adjfailed := FALSE;
3496 --
3497 else
3498 --
3499 l_new_val1 := l_enb_valrow.incrmt_val;
3500 l_adjfailed := TRUE;
3501 --
3502 end if;
3503 --
3504 end if;
3505 --
3506 if nvl(l_efc_row.dflt_val,9999) <> nvl(l_enb_valrow.dflt_val,9999)
3507 and not l_adjfailed
3508 then
3509 --
3510 l_val_type := 'ENB_DFLTVAL';
3511 l_old_val1 := l_efc_row.dflt_val;
3512 --
3513 if l_efc_row.dflt_val is null then
3514 --
3515 l_enb_valrow.dflt_val := null;
3516 l_new_val1 := null;
3517 l_adjfailed := FALSE;
3518 --
3519 else
3520 --
3521 l_new_val1 := l_enb_valrow.dflt_val;
3522 l_adjfailed := TRUE;
3523 --
3524 end if;
3525 --
3526 end if;
3527 --
3528 if nvl(l_efc_row.mx_wout_ctfn_val,9999) <> nvl(l_enb_valrow.mx_wout_ctfn_val,9999)
3529 and not l_adjfailed
3530 then
3531 --
3532 l_val_type := 'ENB_MXWOCTFNVAL';
3533 l_old_val1 := l_efc_row.mx_wout_ctfn_val;
3534 --
3535 if l_efc_row.mx_wout_ctfn_val is null then
3536 --
3537 l_enb_valrow.mx_wout_ctfn_val := null;
3538 l_new_val1 := null;
3539 l_adjfailed := FALSE;
3540 --
3541 else
3542 --
3543 l_new_val1 := l_enb_valrow.mx_wout_ctfn_val;
3544 l_adjfailed := TRUE;
3545 --
3546 end if;
3547 --
3548 end if;
3549 --
3550 if not l_adjfailed
3551 then
3552 --
3553 l_val_type := 'ENB_VAL';
3554 l_old_val1 := l_efc_row.val;
3555 l_new_val1 := l_enb_valrow.val;
3556 l_adjfailed := FALSE;
3557 --
3558 end if;
3559 --
3560 -- Success and failure fatal error checks
3561 --
3562 --
3563 -- Get the parent UOM
3564 --
3565 ben_efc_functions.CompObject_GetParUom
3566 (p_pgm_id => l_efc_row.pgm_id
3567 ,p_ptip_id => l_efc_row.ptip_id
3568 ,p_pl_id => l_efc_row.pl_id
3569 ,p_plip_id => l_efc_row.plip_id
3570 ,p_oipl_id => l_efc_row.oipl_id
3571 ,p_oiplip_id => null
3572 ,p_eff_date => l_efc_row.lf_evt_ocrd_dt
3573 --
3574 ,p_paruom => l_enb_uom
3575 ,p_faterr_code => l_faterr_code
3576 ,p_faterr_type => l_faterr_type
3577 );
3578 --
3579 -- Check for points uom
3580 --
3581 if l_enb_uom = 'POINTS'
3582 and l_faterr_code is null
3583 then
3584 --
3585 l_faterr_code := 'ENBPOINTSUOM';
3586 l_faterr_type := 'VALIDEXCLUSION';
3587 --
3588 end if;
3589 --
3590 -- Post failure fatal error checks
3591 --
3592 if l_adjfailed
3593 then
3594 --
3595 if l_faterr_code is null then
3596 --
3597 -- Check for a FLRNG mlt cd
3598 --
3599 if l_efc_row.cvg_mlt_cd = 'FLRNG'
3600 then
3601 --
3602 l_faterr_code := 'FLRNGCVGMC';
3603 l_faterr_type := 'UNSUPPORTTRANS';
3604 --
3605 end if;
3606 --
3607 end if;
3608 --
3609 -- Check for a VAPRO treatment code of RPLC
3610 --
3611 if l_faterr_code is null then
3612 --
3613 -- Validate vapro
3614 --
3615 l_currepe_row.person_id := l_efc_row.person_id;
3616 l_currepe_row.elig_per_elctbl_chc_id := l_efc_row.elig_per_elctbl_chc_id;
3617 l_currepe_row.pgm_id := l_efc_row.pgm_id;
3618 l_currepe_row.pl_id := l_efc_row.pl_id;
3619 l_currepe_row.pl_typ_id := l_efc_row.pl_typ_id;
3620 l_currepe_row.oipl_id := l_efc_row.oipl_id;
3621 l_currepe_row.per_in_ler_id := l_efc_row.per_in_ler_id;
3622 l_currepe_row.ler_id := l_efc_row.ler_id;
3623 l_currepe_row.business_group_id := l_efc_row.business_group_id;
3624 --
3625 ben_efc_adjustments.DetectVAPROInfo
3626 (p_currepe_row => l_currepe_row
3627 --
3628 ,p_lf_evt_ocrd_dt => l_efc_row.lf_evt_ocrd_dt
3629 ,p_last_update_date => l_efc_row.last_update_date
3630 --
3631 ,p_cvg_amt_calc_mthd_id => l_cvg_set(0).cvg_amt_calc_mthd_id
3632 --
3633 ,p_vpfdets => l_vpfdets
3634 ,p_vpf_id => l_vpf_id
3635 ,p_faterr_code => l_faterr_code
3636 ,p_faterr_type => l_faterr_type
3637 );
3638 --
3639 -- Check for a replace rate type. A code fix has been made since the
3640 -- original info was created.
3641 --
3642 if l_vpfdets.vrbl_rt_trtmt_cd = 'RPLC'
3643 and l_faterr_code is null
3644 then
3645 --
3646 l_faterr_code := 'INVADJRPLCVPF';
3647 l_faterr_type := 'CODECHANGE';
3648 --
3649 end if;
3650 --
3651 end if;
3652 --
3653 if l_faterr_code is null
3654 then
3655 --
3656 -- Check rounding
3657 --
3658 ben_efc_adjustments.DetectRoundInfo
3659 (p_rndg_cd => null
3660 ,p_rndg_rl => null
3661 ,p_old_val => l_old_val1
3662 ,p_new_val => l_new_val1
3663 ,p_eff_date => l_efc_row.lf_evt_ocrd_dt
3664 --
3665 ,p_faterr_code => l_faterr_code
3666 ,p_faterr_type => l_faterr_type
3667 );
3668 --
3669 end if;
3670 --
3671 if l_faterr_code is null
3672 then
3673 --
3674 -- Check if the CCM has been modified since the ENB was created
3675 --
3676 if nvl(l_ccm_dets.last_update_date,hr_api.g_sot)
3677 > nvl(l_efc_row.creation_date,hr_api.g_sot)
3678 then
3679 --
3680 l_faterr_code := 'CCMDTCORR';
3681 l_faterr_type := 'CORRECTEDINFO';
3682 --
3683 elsif l_who_counts.multtransmod_count > 0
3684 and l_efc_row.object_version_number > 1
3685 then
3686 --
3687 l_faterr_code := 'ENBMODS';
3688 l_faterr_type := 'UNSUPPORTTRANS';
3689 --
3690 elsif l_who_counts.multtransmod_count > 0
3691 and l_efc_row.object_version_number = 1
3692 then
3693 --
3694 l_faterr_code := 'INVOVNTRIG';
3695 l_faterr_type := 'DATACORRUPT';
3696 --
3697 end if;
3698 --
3699 end if;
3700 --
3701 -- Check multiple codes
3702 --
3703 if l_cvg_set(0).cvg_mlt_cd in ('CL','CLRNG','FLFXPCL','FLPCLRNG','CLPFLRNG')
3704 and l_faterr_code is null
3705 then
3706 --
3707 -- Check that a pay proposal exists for the assignment
3708 --
3709 open c_asgpppdets
3710 (c_per_id => l_efc_row.person_id
3711 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3712 );
3713 fetch c_asgpppdets into l_asgpppdets;
3714 if c_asgpppdets%notfound then
3715 --
3716 l_faterr_code := 'NOASGPPP';
3717 --
3718 end if;
3719 close c_asgpppdets;
3720 --
3721 end if;
3722 --
3723 if l_faterr_code is null then
3724 --
3725 -- WHO checks
3726 --
3727 ben_efc_adjustments.DetectWhoInfo
3728 (p_creation_date => l_efc_row.creation_date
3729 ,p_last_update_date => l_efc_row.last_update_date
3730 ,p_object_version_number => l_efc_row.object_version_number
3731 ,p_who_counts => l_who_counts
3732 ,p_faterr_code => l_faterr_code
3733 ,p_faterr_type => l_faterr_type
3734 );
3735 --
3736 end if;
3737 --
3738 if l_faterr_code is null then
3739 --
3740 g_enb_failed_adj_val_set(l_calfail_count).id := l_efc_row.enrt_bnft_id;
3741 g_enb_failed_adj_val_set(l_calfail_count).old_val1 := l_old_val1;
3742 g_enb_failed_adj_val_set(l_calfail_count).new_val1 := l_new_val1;
3743 g_enb_failed_adj_val_set(l_calfail_count).val_type := l_val_type;
3744 g_enb_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
3745 g_enb_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
3746 --
3747 if l_cvg_set.count > 0 then
3748 --
3749 g_enb_failed_adj_val_set(l_calfail_count).code1 := l_cvg_set(0).cvg_mlt_cd;
3750 --
3751 end if;
3752 --
3753 g_enb_failed_adj_val_set(l_calfail_count).code2 := l_clf_dets.comp_src_cd;
3754 g_enb_failed_adj_val_set(l_calfail_count).code3 := l_vpfdets.mlt_cd;
3755 g_enb_failed_adj_val_set(l_calfail_count).code4 := l_enb_uom;
3756 --
3757 l_calfail_count := l_calfail_count+1;
3758 --
3759 end if;
3760 --
3761 end if;
3762 --
3763 if l_efc_batch
3764 and l_faterr_code is null
3765 then
3766 --
3767 update ben_enrt_bnft enb
3768 set enb.val = l_enb_valrow.val,
3769 enb.mn_val = l_enb_valrow.mn_val,
3770 enb.mx_val = l_enb_valrow.mx_val,
3771 enb.mx_wout_ctfn_val = l_enb_valrow.mx_wout_ctfn_val,
3772 enb.incrmt_val = l_enb_valrow.incrmt_val,
3773 enb.dflt_val = l_enb_valrow.dflt_val
3774 where enb.enrt_bnft_id = l_efc_row.enrt_bnft_id;
3775 --
3776 if p_validate then
3777 --
3778 rollback;
3779 --
3780 end if;
3781 --
3782 -- Check for end of chunk and commit if necessary
3783 --
3784 l_pk1 := l_efc_row.enrt_bnft_id;
3785 --
3786 ben_efc_functions.maintain_chunks
3787 (p_row_count => l_row_count
3788 ,p_pk1 => l_pk1
3789 ,p_chunk_size => p_chunk
3790 ,p_efc_worker_id => p_efc_worker_id
3791 );
3792 --
3793 end if;
3794 --
3795 exception
3796 when others then
3797 --
3798 g_enb_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.enrt_bnft_id;
3799 g_enb_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENCVRGE';
3800 g_enb_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
3801 g_enb_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
3802 g_enb_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
3803 --
3804 l_rcoerr_count := l_rcoerr_count+1;
3805 --
3806 end;
3807 --
3808 end if;
3809 --
3810 -- Check for fatal errors
3811 --
3812 if l_faterr_code is not null
3813 then
3814 --
3815 g_enb_fatal_error_val_set(l_faterrs_count).id := l_efc_row.enrt_bnft_id;
3816 g_enb_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
3817 g_enb_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
3818 g_enb_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
3819 g_enb_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
3820 g_enb_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
3821 g_enb_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
3822 --
3823 if l_cvg_set.count > 0 then
3824 --
3825 g_enb_fatal_error_val_set(l_faterrs_count).code1 := l_cvg_set(0).cvg_mlt_cd;
3826 --
3827 end if;
3828 --
3829 g_enb_fatal_error_val_set(l_faterrs_count).code2 := l_clf_dets.comp_src_cd;
3830 g_enb_fatal_error_val_set(l_faterrs_count).code3 := l_vpfdets.mlt_cd;
3831 g_enb_fatal_error_val_set(l_faterrs_count).code4 := l_enb_uom;
3832 --
3833 l_faterrs_count := l_faterrs_count+1;
3834 --
3835 elsif l_faterr_code is null
3836 and not l_adjfailed
3837 then
3838 --
3839 g_enb_success_adj_val_set(l_calsucc_count).id := l_efc_row.enrt_bnft_id;
3840 g_enb_success_adj_val_set(l_calsucc_count).faterr_code := l_faterr_code;
3841 g_enb_success_adj_val_set(l_calsucc_count).faterr_type := l_faterr_type;
3842 g_enb_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
3843 g_enb_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
3844 g_enb_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
3845 g_enb_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
3846 g_enb_success_adj_val_set(l_calsucc_count).code1 := l_cvg_set(0).cvg_mlt_cd;
3847 g_enb_success_adj_val_set(l_calsucc_count).code2 := l_clf_dets.comp_src_cd;
3848 g_enb_success_adj_val_set(l_calsucc_count).code3 := l_vpfdets.mlt_cd;
3849 --
3850 l_calsucc_count := l_calsucc_count+1;
3851 --
3852 end if;
3853 --
3854 l_row_count := l_row_count+1;
3855 --
3856 end loop;
3857 CLOSE c_efc_rows;
3858 --
3859 -- Check that all rows have been converted or excluded
3860 --
3861 ben_efc_functions.conv_check
3862 (p_table_name => 'ben_enrt_bnft'
3863 ,p_efctable_name => 'ben_enrt_bnft_efc'
3864 ,p_tabwhere_clause => ' (mx_wout_ctfn_val is not null '
3865 ||' or mn_val is not null '
3866 ||' or mx_val is not null '
3867 ||' or incrmt_val is not null '
3868 ||' or dflt_val is not null '
3869 ||' or val is not null '
3870 ||' ) '
3871 --
3872 ,p_bgp_id => p_business_group_id
3873 ,p_action_id => p_action_id
3874 --
3875 ,p_conv_count => l_conv_count
3876 ,p_unconv_count => l_unconv_count
3877 ,p_tabrow_count => l_tabrow_count
3878 );
3879 --
3880 -- Set counts
3881 --
3882 if p_action_id is null then
3883 --
3884 l_actconv_count := 0;
3885 --
3886 else
3887 --
3888 l_actconv_count := l_conv_count;
3889 --
3890 end if;
3891 --
3892 p_adjustment_counts.efcrow_count := l_row_count;
3893 p_adjustment_counts.tabrow_count := l_tabrow_count;
3894 p_adjustment_counts.calfail_count := l_calfail_count;
3895 p_adjustment_counts.calsucc_count := l_calsucc_count;
3896 p_adjustment_counts.dupconv_count := l_dupconv_count;
3897 p_adjustment_counts.conv_count := l_conv_count;
3898 p_adjustment_counts.actconv_count := l_actconv_count;
3899 p_adjustment_counts.unconv_count := l_unconv_count;
3900 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
3901 --
3902 end enb_adjustments;
3903 --
3904 procedure epr_adjustments
3905 (p_validate in boolean default false
3906 ,p_worker_id in number default null
3907 ,p_action_id in number default null
3908 ,p_total_workers in number default null
3909 ,p_pk1 in number default null
3910 ,p_chunk in number default null
3911 ,p_efc_worker_id in number default null
3912 --
3913 ,p_valworker_id in number default null
3914 ,p_valtotal_workers in number default null
3915 --
3916 ,p_business_group_id in number default null
3917 --
3918 ,p_adjustment_counts out nocopy g_adjustment_counts
3919 )
3920 is
3921 --
3922 TYPE cur_type IS REF CURSOR;
3923 --
3924 type g_efc_row is record
3925 (enrt_prem_id ben_enrt_prem.enrt_prem_id%type
3926 ,elig_per_elctbl_chc_id ben_enrt_prem.elig_per_elctbl_chc_id%type
3927 ,enrt_bnft_id ben_enrt_prem.enrt_bnft_id%type
3928 ,actl_prem_id ben_enrt_prem.actl_prem_id%type
3929 ,business_group_id ben_enrt_prem.business_group_id%type
3930 ,val ben_enrt_prem.val%type
3931 ,last_update_date ben_enrt_prem.last_update_date%type
3932 ,creation_date ben_enrt_prem.creation_date%type
3933 ,object_version_number ben_enrt_prem.object_version_number%type
3934 );
3935 --
3936 c_efc_rows cur_type;
3937 --
3938 l_proc varchar2(1000) := 'epr_adjustments';
3939 --
3940 l_efc_row g_efc_row;
3941 --
3942 l_who_counts g_who_counts;
3943 --
3944 l_currepe_row ben_determine_rates.g_curr_epe_rec;
3945 --
3946 l_vpfdets gc_vpfdets%rowtype;
3947 --
3948 l_currpil_row g_pil_rowtype;
3949 --
3950 l_row_count pls_integer;
3951 l_calfail_count pls_integer;
3952 l_calsucc_count pls_integer;
3953 l_conv_count pls_integer;
3954 l_unconv_count pls_integer;
3955 l_actconv_count pls_integer;
3956 l_dupconv_count pls_integer;
3957 --
3958 l_rcoerr_count pls_integer;
3959 --
3960 l_faterrs_count pls_integer;
3961 l_tabrow_count pls_integer;
3962 --
3963 l_olddata boolean;
3964 --
3965 l_sql_str long;
3966 l_from_str long;
3967 l_where_str long;
3968 --
3969 l_efc_batch boolean;
3970 --
3971 l_pk1 number;
3972 --
3973 l_bnft_amt number;
3974 l_val number;
3975 l_vpf_id number;
3976 l_prev_person_id number;
3977 --
3978 l_faterr_code varchar2(100);
3979 l_faterr_type varchar2(100);
3980 l_adj_failed boolean;
3981 --
3982 cursor c_apr
3983 (c_apr_id in number
3984 ,c_lf_evt_ocrd_dt in date
3985 )
3986 is
3987 select apr.actl_prem_id,
3988 apr.mlt_cd,
3989 apr.val,
3990 apr.rndg_cd,
3991 apr.rndg_rl,
3992 apr.rt_typ_cd,
3993 apr.bnft_rt_typ_cd,
3994 apr.comp_lvl_fctr_id,
3995 apr.prem_asnmt_cd,
3996 apr.val_calc_rl,
3997 apr.upr_lmt_val,
3998 apr.upr_lmt_calc_rl,
3999 apr.lwr_lmt_val,
4000 apr.lwr_lmt_calc_rl,
4001 apr.uom,
4002 apr.last_update_date
4003 from ben_actl_prem_f apr
4004 where apr.actl_prem_id = c_apr_id
4005 and apr.prem_asnmt_cd = 'ENRT'
4006 and c_lf_evt_ocrd_dt
4007 between apr.effective_start_date
4008 and apr.effective_end_date;
4009 --
4010 l_apr c_apr%rowtype;
4011 --
4012 cursor c_epeenb_dets
4013 (c_epe_id in number
4014 )
4015 is
4016 select enb.val
4017 from BEN_ELIG_PER_ELCTBL_CHC epe,
4018 ben_enrt_bnft enb
4019 where epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
4020 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id;
4021 --
4022 cursor c_enb_dets
4023 (c_enb_id in number
4024 )
4025 is
4026 select enb.val
4027 from ben_enrt_bnft enb
4028 where enb.enrt_bnft_id = c_enb_id;
4029 --
4030 cursor c_mxaprvprlud
4031 (c_apr_id in number
4032 ,c_eff_date in date
4033 )
4034 is
4035 select apr.actl_prem_id,
4036 max(apr.last_update_date) mxapr_lud,
4037 max(vrp.last_update_date) mxvrp_lud
4038 from ben_actl_prem_vrbl_rt_f apr,
4039 ben_vrbl_rt_prfl_f vrp
4040 where vrp.vrbl_rt_prfl_stat_cd = 'A'
4041 and c_eff_date
4042 between vrp.effective_start_date and vrp.effective_end_date
4043 and apr.actl_prem_id = c_apr_id
4044 and vrp.vrbl_rt_prfl_id = apr.vrbl_rt_prfl_id
4045 and c_eff_date
4046 between apr.effective_start_date
4047 and apr.effective_end_date
4048 group by apr.actl_prem_id;
4049 --
4050 l_mxaprvprlud c_mxaprvprlud%rowtype;
4051 --
4052 begin
4053 --
4054 l_efc_batch := FALSE;
4055 --
4056 l_row_count := 0;
4057 l_calfail_count := 0;
4058 l_calsucc_count := 0;
4059 l_dupconv_count := 0;
4060 l_conv_count := 0;
4061 l_actconv_count := 0;
4062 l_unconv_count := 0;
4063 l_rcoerr_count := 0;
4064 l_faterrs_count := 0;
4065 --
4066 l_prev_person_id := -1;
4067 --
4068 g_epr_success_adj_val_set.delete;
4069 g_epr_failed_adj_val_set.delete;
4070 g_epr_rcoerr_val_set.delete;
4071 g_epr_fatal_error_val_set.delete;
4072 --
4073 -- Check if EFC process parameters are set
4074 --
4075 if p_action_id is not null
4076 and p_pk1 is not null
4077 and p_chunk is not null
4078 and p_efc_worker_id is not null
4079 then
4080 --
4081 l_efc_batch := TRUE;
4082 --
4083 end if;
4084 --
4085 l_from_str := ' FROM ben_enrt_prem epr ';
4086 --
4087 l_where_str := ' where epr.val is not null ';
4088 --
4089 -- Check if we are restricting by business group
4090 --
4091 if p_business_group_id is not null then
4092 --
4093 l_where_str := l_where_str||' and epr.business_group_id = '||p_business_group_id;
4094 --
4095 end if;
4096 --
4097 -- Build in batch specific restrictions
4098 --
4099 if l_efc_batch then
4100 --
4101 l_from_str := l_from_str||', ben_enrt_prem_efc efc ';
4102 l_where_str := l_where_str||' and efc.enrt_prem_id = epr.enrt_prem_id '
4103 ||' and efc.efc_action_id = :action_id '
4104 ||' and epr.enrt_prem_id > :pk1 '
4105 ||' and mod(epr.enrt_prem_id, :total_workers) = :worker_id ';
4106 --
4107 elsif p_valworker_id is not null
4108 and p_valtotal_workers is not null
4109 then
4110 --
4111 l_where_str := l_where_str||' and mod(epr.enrt_prem_id, :valtotal_workers) = :valworker_id ';
4112 --
4113 end if;
4114 --
4115 l_sql_str := ' select epr.enrt_prem_id, '
4116 ||' epr.elig_per_elctbl_chc_id, '
4117 ||' epr.enrt_bnft_id, '
4118 ||' epr.actl_prem_id, '
4119 ||' epr.business_group_id, '
4120 ||' epr.val, '
4121 ||' epr.last_update_date, '
4122 ||' epr.creation_date, '
4123 ||' epr.object_version_number '
4124 ||l_from_str
4125 ||l_where_str
4126 ||' order by epr.enrt_prem_id ';
4127 --
4128 if l_efc_batch then
4129 --
4130 hr_efc_info.insert_line('-- ');
4131 hr_efc_info.insert_line('-- Adjusting enrolment premiums ');
4132 hr_efc_info.insert_line('-- ');
4133 --
4134 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
4135 --
4136 elsif p_valworker_id is not null
4137 and p_valtotal_workers is not null
4138 then
4139 --
4140 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
4141 --
4142 else
4143 --
4144 open c_efc_rows FOR l_sql_str;
4145 --
4146 end if;
4147 --
4148 ben_epe_cache.clear_down_cache;
4149 --
4150 loop
4151 FETCH c_efc_rows INTO l_efc_row;
4152 EXIT WHEN c_efc_rows%NOTFOUND;
4153 --
4154 l_faterr_code := null;
4155 l_adj_failed := FALSE;
4156 --
4157 if l_faterr_code is null then
4158 --
4159 -- Detect EPE or ENB Info
4160 --
4161 ben_efc_adjustments.DetectEPEENBInfo
4162 (p_elig_per_elctbl_chc_id => l_efc_row.elig_per_elctbl_chc_id
4163 ,p_enrt_bnft_id => l_efc_row.enrt_bnft_id
4164 --
4165 ,p_detect_mode => 'EPEINFO'
4166 --
4167 ,p_currepe_row => l_currepe_row
4168 ,p_currpil_row => l_currpil_row
4169 ,p_faterr_code => l_faterr_code
4170 ,p_faterr_type => l_faterr_type
4171 );
4172 --
4173 -- Set up benefits environment
4174 --
4175 ben_env_object.init
4176 (p_business_group_id => l_efc_row.business_group_id
4177 ,p_effective_date => l_currepe_row.lf_evt_ocrd_dt
4178 ,p_thread_id => 1
4179 ,p_chunk_size => 10
4180 ,p_threads => 1
4181 ,p_max_errors => 100
4182 ,p_benefit_action_id => 99999
4183 ,p_audit_log_flag => 'N'
4184 );
4185 --
4186 end if;
4187 --
4188 -- Check actual premium info
4189 --
4190 if l_faterr_code is null then
4191 --
4192 open c_apr
4193 (c_apr_id => l_efc_row.actl_prem_id
4194 ,c_lf_evt_ocrd_dt => l_currepe_row.lf_evt_ocrd_dt
4195 );
4196 fetch c_apr into l_apr;
4197 if c_apr%notfound then
4198 --
4199 l_faterr_code := 'NODTAPR';
4200 l_faterr_type := 'DELETEDINFO';
4201 --
4202 end if;
4203 close c_apr;
4204 --
4205 if l_apr.uom = 'EUR'
4206 and l_faterr_code is null
4207 then
4208 --
4209 l_faterr_code := 'EUROUOM';
4210 l_faterr_type := 'VALIDEXCLUSION';
4211 --
4212 end if;
4213 --
4214 end if;
4215 --
4216 if l_faterr_code is null then
4217 --
4218 -- Check if the person has changed if so flush person level caches
4219 --
4220 if l_currepe_row.person_id <> l_prev_person_id
4221 then
4222 --
4223 ben_person_object.clear_down_cache;
4224 ben_comp_object.clear_down_cache;
4225 ben_rt_prfl_cache.clear_down_cache;
4226 --
4227 l_prev_person_id := l_currepe_row.person_id;
4228 --
4229 end if;
4230 --
4231 -- Get the benefit amount value from the coverage
4232 --
4233 if l_efc_row.elig_per_elctbl_chc_id is not null then
4234 --
4235 open c_epeenb_dets
4236 (c_epe_id => l_efc_row.elig_per_elctbl_chc_id
4237 );
4238 fetch c_epeenb_dets into l_bnft_amt;
4239 close c_epeenb_dets;
4240 --
4241 else
4242 --
4243 open c_enb_dets
4244 (c_enb_id => l_efc_row.enrt_bnft_id
4245 );
4246 fetch c_enb_dets into l_bnft_amt;
4247 close c_enb_dets;
4248 --
4249 end if;
4250 --
4251 -- Check if the bnft amt is set when using the coverage mlt code
4252 --
4253 if l_apr.mlt_cd = 'CVG'
4254 and l_bnft_amt is null
4255 then
4256 --
4257 l_faterr_code := 'APRMLTCDBAMTNULL';
4258 --
4259 end if;
4260 --
4261 if l_faterr_code is null then
4262 --
4263 begin
4264 --
4265 BEN_DETERMINE_ACTUAL_PREMIUM.compute_premium
4266 (p_person_id => l_currepe_row.person_id
4267 ,p_lf_evt_ocrd_dt => l_currepe_row.lf_evt_ocrd_dt
4268 ,p_effective_date => l_currepe_row.lf_evt_ocrd_dt
4269 ,p_business_group_id => l_currepe_row.business_group_id
4270 ,p_per_in_ler_id => l_currepe_row.per_in_ler_id
4271 ,p_ler_id => l_currepe_row.ler_id
4272 ,p_actl_prem_id => l_efc_row.actl_prem_id
4273 ,p_perform_rounding_flg => TRUE
4274 ,p_calc_only_rt_val_flag => FALSE
4275 ,p_pgm_id => l_currepe_row.pgm_id
4276 ,p_pl_typ_id => l_currepe_row.pl_typ_id
4277 ,p_pl_id => l_currepe_row.pl_id
4278 ,p_oipl_id => l_currepe_row.oipl_id
4279 ,p_opt_id => l_currepe_row.opt_id
4280 ,p_elig_per_elctbl_chc_id => l_currepe_row.elig_per_elctbl_chc_id
4281 ,p_enrt_bnft_id => l_currepe_row.enrt_bnft_id
4282 ,p_bnft_amt => l_bnft_amt
4283 ,p_prem_val => l_apr.val
4284 ,p_mlt_cd => l_apr.mlt_cd
4285 ,p_bnft_rt_typ_cd => l_apr.bnft_rt_typ_cd
4286 ,p_val_calc_rl => l_apr.val_calc_rl
4287 ,p_rndg_cd => l_apr.rndg_cd
4288 ,p_rndg_rl => l_apr.rndg_rl
4289 ,p_upr_lmt_val => l_apr.upr_lmt_val
4290 ,p_lwr_lmt_val => l_apr.lwr_lmt_val
4291 ,p_upr_lmt_calc_rl => l_apr.upr_lmt_calc_rl
4292 ,p_lwr_lmt_calc_rl => l_apr.lwr_lmt_calc_rl
4293 ,p_computed_val => l_val
4294 );
4295 --
4296 if nvl(l_efc_row.val,9999) <> nvl(l_val,9999)
4297 then
4298 --
4299 -- Check for a null value
4300 --
4301 if l_val is null then
4302 --
4303 l_faterr_code := 'EPRVALNULL';
4304 l_faterr_type := 'POTENTIALCODEBUG';
4305 --
4306 end if;
4307 --
4308 if l_faterr_code is null then
4309 --
4310 -- Validate vapro information
4311 --
4312 ben_efc_adjustments.DetectVAPROInfo
4313 (p_currepe_row => l_currepe_row
4314 --
4315 ,p_lf_evt_ocrd_dt => l_currepe_row.lf_evt_ocrd_dt
4316 ,p_last_update_date => l_efc_row.last_update_date
4317 --
4318 ,p_actl_prem_id => l_efc_row.actl_prem_id
4319 --
4320 ,p_vpfdets => l_vpfdets
4321 ,p_vpf_id => l_vpf_id
4322 ,p_faterr_code => l_faterr_code
4323 ,p_faterr_type => l_faterr_type
4324 );
4325 --
4326 -- Check if the vapro fails
4327 --
4328 if l_vpf_id is null
4329 and l_faterr_code is null
4330 then
4331 --
4332 -- Check for a zero value
4333 --
4334 if l_efc_row.val = 0 then
4335 --
4336 l_faterr_code := 'EPRVRPFAIL0VAL';
4337 --
4338 end if;
4339 --
4340 end if;
4341 --
4342 end if;
4343 --
4344 -- Check for apr mods since the actual premium was created
4345 --
4346 if nvl(l_apr.last_update_date,hr_api.g_sot)
4347 > nvl(l_efc_row.last_update_date,hr_api.g_eot)
4348 and l_faterr_code is null
4349 then
4350 --
4351 l_faterr_code := 'APREPRCORR';
4352 --
4353 end if;
4354 --
4355 if l_faterr_code is null then
4356 --
4357 ben_efc_adjustments.DetectWhoInfo
4358 (p_creation_date => l_efc_row.creation_date
4359 ,p_last_update_date => l_efc_row.last_update_date
4360 ,p_object_version_number => l_efc_row.object_version_number
4361 --
4362 ,p_who_counts => l_who_counts
4363 ,p_faterr_code => l_faterr_code
4364 ,p_faterr_type => l_faterr_type
4365 );
4366 --
4367 end if;
4368 --
4369 if l_faterr_code is null then
4370 --
4371 g_epr_failed_adj_val_set(l_calfail_count).id := l_efc_row.enrt_prem_id;
4372 g_epr_failed_adj_val_set(l_calfail_count).old_val1 := l_efc_row.val;
4373 g_epr_failed_adj_val_set(l_calfail_count).new_val1 := l_val;
4374 g_epr_failed_adj_val_set(l_calfail_count).val_type := 'EPR_VAL';
4375 g_epr_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
4376 g_epr_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
4377 --
4378 l_adj_failed := TRUE;
4379 l_calfail_count := l_calfail_count+1;
4380 --
4381 end if;
4382 --
4383 end if;
4384 --
4385 exception
4386 when others then
4387 --
4388 g_epr_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.enrt_prem_id;
4389 g_epr_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENACPRM';
4390 g_epr_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
4391 g_epr_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
4392 g_epr_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
4393 --
4394 l_rcoerr_count := l_rcoerr_count+1;
4395 --
4396 end;
4397 --
4398 end if;
4399 --
4400 if l_efc_batch
4401 and l_faterr_code is null
4402 then
4403 --
4404 update ben_enrt_prem epr
4405 set epr.val = l_val
4406 where epr.enrt_prem_id = l_efc_row.enrt_prem_id;
4407 --
4408 if p_validate then
4409 --
4410 rollback;
4411 --
4412 end if;
4413 --
4414 -- Check for end of chunk and commit if necessary
4415 --
4416 l_pk1 := l_efc_row.enrt_prem_id;
4417 --
4418 ben_efc_functions.maintain_chunks
4419 (p_row_count => l_row_count
4420 ,p_pk1 => l_pk1
4421 ,p_chunk_size => p_chunk
4422 ,p_efc_worker_id => p_efc_worker_id
4423 );
4424 --
4425 end if;
4426 --
4427 end if;
4428 --
4429 -- Check for fatal errors
4430 --
4431 if l_faterr_code is not null
4432 then
4433 --
4434 g_epr_fatal_error_val_set(l_faterrs_count).id := l_efc_row.enrt_prem_id;
4435 g_epr_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
4436 g_epr_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
4437 g_epr_fatal_error_val_set(l_faterrs_count).old_val1 := l_efc_row.val;
4438 g_epr_fatal_error_val_set(l_faterrs_count).new_val1 := l_val;
4439 g_epr_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
4440 g_epr_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
4441 g_epr_fatal_error_val_set(l_faterrs_count).code1 := l_apr.uom;
4442 --
4443 l_faterrs_count := l_faterrs_count+1;
4444 --
4445 elsif l_faterr_code is null
4446 and not l_adj_failed
4447 then
4448 --
4449 g_epr_success_adj_val_set(l_calsucc_count).id := l_efc_row.enrt_prem_id;
4450 g_epr_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
4451 g_epr_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
4452 g_epr_success_adj_val_set(l_calsucc_count).code1 := l_apr.uom;
4453 --
4454 l_calsucc_count := l_calsucc_count+1;
4455 --
4456 end if;
4457 --
4458 l_row_count := l_row_count+1;
4459 --
4460 end loop;
4461 CLOSE c_efc_rows;
4462 /*
4463 --
4464 -- Write exceptions down to the table
4465 --
4466 if l_efc_batch then
4467 --
4468 ben_efc_adjustments.insert_validation_exceptions
4469 (p_val_set => g_epr_fatal_error_val_set
4470 ,p_efc_action_id => p_action_id
4471 ,p_ent_scode => 'EPR'
4472 ,p_exception_type => null
4473 );
4474 --
4475 end if;
4476 */
4477 --
4478 -- Check that all rows have been converted or excluded
4479 --
4480 ben_efc_functions.conv_check
4481 (p_table_name => 'ben_enrt_prem'
4482 ,p_efctable_name => 'ben_enrt_prem_efc'
4483 ,p_tabwhere_clause => ' val is not null '
4484 --
4485 ,p_bgp_id => p_business_group_id
4486 ,p_action_id => p_action_id
4487 --
4488 ,p_conv_count => l_conv_count
4489 ,p_unconv_count => l_unconv_count
4490 ,p_tabrow_count => l_tabrow_count
4491 );
4492 --
4493 -- Set counts
4494 --
4495 if p_action_id is null then
4496 --
4497 l_actconv_count := 0;
4498 --
4499 else
4500 --
4501 l_actconv_count := l_conv_count;
4502 --
4503 end if;
4504 --
4505 p_adjustment_counts.efcrow_count := l_row_count;
4506 p_adjustment_counts.tabrow_count := l_tabrow_count;
4507 p_adjustment_counts.calfail_count := l_calfail_count;
4508 p_adjustment_counts.calsucc_count := l_calsucc_count;
4509 p_adjustment_counts.dupconv_count := l_dupconv_count;
4510 p_adjustment_counts.conv_count := l_conv_count;
4511 p_adjustment_counts.actconv_count := l_actconv_count;
4512 p_adjustment_counts.unconv_count := l_unconv_count;
4513 p_adjustment_counts.faterrs_count := l_faterrs_count;
4514 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
4515 --
4516 end epr_adjustments;
4517 --
4518 procedure ecr_adjustments
4519 (p_validate in boolean default false
4520 ,p_worker_id in number default null
4521 ,p_action_id in number default null
4522 ,p_total_workers in number default null
4523 ,p_pk1 in number default null
4524 ,p_chunk in number default null
4525 ,p_efc_worker_id in number default null
4526 --
4527 ,p_valworker_id in number default null
4528 ,p_valtotal_workers in number default null
4529 --
4530 ,p_business_group_id in number default null
4531 --
4532 ,p_adjustment_counts out nocopy g_adjustment_counts
4533 )
4534 is
4535 --
4536 TYPE cur_type IS REF CURSOR;
4537 --
4538 type g_efc_row is record
4539 (enrt_rt_id ben_enrt_rt.enrt_rt_id%type
4540 ,elig_per_elctbl_chc_id ben_enrt_rt.elig_per_elctbl_chc_id%type
4541 ,enrt_bnft_id ben_enrt_rt.enrt_bnft_id%type
4542 ,acty_base_rt_id ben_enrt_rt.acty_base_rt_id%type
4543 ,business_group_id ben_enrt_rt.business_group_id%type
4544 ,cmcd_mn_elcn_val ben_enrt_rt.cmcd_mn_elcn_val%type
4545 ,cmcd_mx_elcn_val ben_enrt_rt.cmcd_mx_elcn_val%type
4546 ,cmcd_val ben_enrt_rt.cmcd_val%type
4547 ,cmcd_dflt_val ben_enrt_rt.cmcd_dflt_val%type
4548 ,ann_dflt_val ben_enrt_rt.ann_dflt_val%type
4549 ,dsply_mn_elcn_val ben_enrt_rt.dsply_mn_elcn_val%type
4550 ,dsply_mx_elcn_val ben_enrt_rt.dsply_mx_elcn_val%type
4551 ,dflt_val ben_enrt_rt.dflt_val%type
4552 ,ann_val ben_enrt_rt.ann_val%type
4553 ,ann_mn_elcn_val ben_enrt_rt.ann_mn_elcn_val%type
4554 ,ann_mx_elcn_val ben_enrt_rt.ann_mx_elcn_val%type
4555 ,mx_elcn_val ben_enrt_rt.mx_elcn_val%type
4556 ,mn_elcn_val ben_enrt_rt.mn_elcn_val%type
4557 ,incrmt_elcn_val ben_enrt_rt.incrmt_elcn_val%type
4558 ,val ben_enrt_rt.val%type
4559 ,last_update_date ben_enrt_rt.last_update_date%type
4560 ,creation_date ben_enrt_rt.creation_date%type
4561 ,object_version_number ben_enrt_rt.object_version_number%type
4562 );
4563 --
4564 c_efc_rows cur_type;
4565 --
4566 l_proc varchar2(1000) := 'ecr_adjustments';
4567 --
4568 l_efc_row g_efc_row;
4569 --
4570 l_who_counts g_who_counts;
4571 --
4572 l_perasg gc_perasg%rowtype;
4573 l_vpfdets gc_vpfdets%rowtype;
4574 --
4575 l_currpil_row g_pil_rowtype;
4576 --
4577 l_row_count pls_integer;
4578 l_calfail_count pls_integer;
4579 l_calsucc_count pls_integer;
4580 l_conv_count pls_integer;
4581 l_unconv_count pls_integer;
4582 l_actconv_count pls_integer;
4583 l_dupconv_count pls_integer;
4584 --
4585 l_faterrs_count pls_integer;
4586 l_rcoerr_count pls_integer;
4587 --
4588 l_tabrow_count pls_integer;
4589 l_chunkrow_count pls_integer;
4590 --
4591 l_sql_str long;
4592 l_from_str long;
4593 l_where_str long;
4594 --
4595 l_efc_batch boolean;
4596 l_detected boolean;
4597 l_pk1 number;
4598 --
4599 l_currepe_row ben_determine_rates.g_curr_epe_rec;
4600 l_per_row per_all_people_F%rowtype;
4601 l_asg_row per_all_assignments_f%rowtype;
4602 l_ast_row per_assignment_status_types%rowtype;
4603 l_adr_row per_addresses%rowtype;
4604 --
4605 l_val number;
4606 l_mn_elcn_val number;
4607 l_mx_elcn_val number;
4608 l_ann_val number;
4609 l_ann_mn_elcn_val number;
4610 l_ann_mx_elcn_val number;
4611 l_cmcd_val number;
4612 l_cmcd_mn_elcn_val number;
4613 l_cmcd_mx_elcn_val number;
4614 l_incrmt_elcn_val number;
4615 l_dflt_val number;
4616 l_ann_dflt_val number;
4617 l_dsply_mn_elcn_val number;
4618 l_dsply_mx_elcn_val number;
4619 --
4620 l_dummy_varchar2 varchar2(100);
4621 l_dummy_number number;
4622 l_dummy_date date;
4623 --
4624 l_faterr_code varchar2(100);
4625 l_faterr_type varchar2(100);
4626 --
4627 l_olddata boolean;
4628 --
4629 l_vpf_id number;
4630 l_adjfailed boolean;
4631 --
4632 l_val_type varchar2(100);
4633 l_old_val1 number;
4634 l_new_val1 number;
4635 l_preconv_val number;
4636 l_postconv_val number;
4637 --
4638 l_lf_evt_ocrd_dt date;
4639 l_person_id number;
4640 l_per_in_ler_id number;
4641 l_prev_bgp_id number;
4642 --
4643 cursor c_apr
4644 (c_apr_id in number
4645 ,c_lf_evt_ocrd_dt in date
4646 )
4647 is
4648 select apr.actl_prem_id,
4649 apr.mlt_cd,
4650 apr.val,
4651 apr.rndg_cd,
4652 apr.rndg_rl,
4653 apr.rt_typ_cd,
4654 apr.bnft_rt_typ_cd,
4655 apr.comp_lvl_fctr_id,
4656 apr.prem_asnmt_cd,
4657 apr.val_calc_rl,
4658 apr.upr_lmt_val,
4659 apr.upr_lmt_calc_rl,
4660 apr.lwr_lmt_val,
4661 apr.lwr_lmt_calc_rl,
4662 apr.uom
4663 from ben_actl_prem_f apr
4664 where apr.actl_prem_id = c_apr_id
4665 and apr.prem_asnmt_cd = 'ENRT'
4666 and c_lf_evt_ocrd_dt
4667 between apr.effective_start_date
4668 and apr.effective_end_date;
4669 --
4670 l_apr c_apr%rowtype;
4671 --
4672 cursor c_epeenb_dets
4673 (c_epe_id in number
4674 )
4675 is
4676 select enb.val
4677 from BEN_ELIG_PER_ELCTBL_CHC epe,
4678 ben_enrt_bnft enb
4679 where epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
4680 and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id;
4681 --
4682 cursor c_enb_dets
4683 (c_enb_id in number
4684 )
4685 is
4686 select enb.val,
4687 enb.last_update_date
4688 from ben_enrt_bnft enb
4689 where enb.enrt_bnft_id = c_enb_id;
4690 --
4691 l_enb_dets c_enb_dets%rowtype;
4692 --
4693 cursor c_pgmdets
4694 (c_pgm_id in number
4695 ,c_eff_date in date
4696 )
4697 is
4698 select pgm.acty_ref_perd_cd,
4699 pgm.object_version_number,
4700 pgm.last_update_date
4701 from ben_pgm_f pgm
4702 where pgm.pgm_id = c_pgm_id
4703 and c_eff_date
4704 between pgm.effective_start_date
4705 and pgm.effective_end_date;
4706 --
4707 l_pgmdets c_pgmdets%rowtype;
4708 --
4709 cursor c_abrdets
4710 (c_abr_id in number
4711 ,c_eff_date in date
4712 )
4713 is
4714 select abr.rt_mlt_cd,
4715 abr.actl_prem_id,
4716 abr.last_update_date,
4717 abr.rndg_cd,
4718 abr.rndg_rl,
4719 abr.use_calc_acty_bs_rt_flag,
4720 abr.nnmntry_uom,
4721 abr.val,
4722 abr.entr_val_at_enrt_flag,
4723 abr.entr_ann_val_flag,
4724 abr.mn_elcn_val,
4725 abr.mx_elcn_val,
4726 abr.incrmt_elcn_val
4727 from ben_acty_base_rt_f abr
4728 where abr.acty_base_rt_id = c_abr_id
4729 and c_eff_date
4730 between abr.effective_start_date
4731 and abr.effective_end_date;
4732 --
4733 l_abrdets c_abrdets%rowtype;
4734 --
4735 cursor c_abrvpfdets
4736 (c_abr_id in number
4737 ,c_eff_date in date
4738 )
4739 is
4740 select vrp.vrbl_rt_prfl_id,
4741 vrp.mlt_cd
4742 from ben_acty_vrbl_rt_f avr,
4743 ben_vrbl_rt_prfl_f vrp
4744 where vrp.vrbl_rt_prfl_stat_cd = 'A'
4745 and c_eff_date
4746 between vrp.effective_start_date
4747 and vrp.effective_end_date
4748 and avr.acty_base_rt_id = c_abr_id
4749 and vrp.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
4750 and c_eff_date
4751 between avr.effective_start_date
4752 and avr.effective_end_date;
4753 --
4754 l_abrvpfdets c_abrvpfdets%rowtype;
4755 --
4756 cursor c_epeaprepr
4757 (c_actl_prem_id in number
4758 ,c_elig_per_elctbl_chc_id in number
4759 )
4760 is
4761 select epr.val,
4762 pil.per_in_ler_stat_cd
4763 from ben_enrt_prem epr,
4764 ben_per_in_ler pil,
4765 ben_elig_per_elctbl_chc epe
4766 where epr.actl_prem_id = c_actl_prem_id
4767 and epr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
4768 and epe.elig_per_elctbl_chc_id = epr.elig_per_elctbl_chc_id
4769 and pil.per_in_ler_id = epe.per_in_ler_id;
4770 --
4771 l_epeaprepr c_epeaprepr%rowtype;
4772 --
4773 cursor c_asgppp
4774 (c_asg_id in number
4775 )
4776 is
4777 select ppp.change_date
4778 from per_pay_proposals ppp
4779 where ppp.assignment_id = c_asg_id
4780 order by ppp.change_date desc;
4781 --
4782 l_asgppp c_asgppp%rowtype;
4783 --
4784 cursor c_preconvdets
4785 (c_efc_action_id number
4786 ,c_ecr_id number
4787 )
4788 is
4789 select efc.CMCD_MN_ELCN_VAL,
4790 efc.CMCD_MX_ELCN_VAL,
4791 efc.CMCD_VAL,
4792 efc.CMCD_DFLT_VAL,
4793 efc.ANN_DFLT_VAL,
4794 efc.DSPLY_MN_ELCN_VAL,
4795 efc.DSPLY_MX_ELCN_VAL,
4796 efc.DFLT_VAL,
4797 efc.ANN_VAL,
4798 efc.ANN_MN_ELCN_VAL,
4799 efc.ANN_MX_ELCN_VAL,
4800 efc.MX_ELCN_VAL,
4801 efc.MN_ELCN_VAL,
4802 efc.INCRMT_ELCN_VAL,
4803 efc.VAL,
4804 nvl(efc.pgm_uom,efc.nip_pl_uom) uom
4805 from ben_enrt_rt_efc efc
4806 where efc.efc_action_id = c_efc_action_id
4807 and efc.enrt_rt_id = c_ecr_id;
4808 --
4809 l_preconvdets c_preconvdets%rowtype;
4810 --
4811 begin
4812 --
4813 l_efc_batch := FALSE;
4814 --
4815 l_row_count := 0;
4816 l_calfail_count := 0;
4817 l_calsucc_count := 0;
4818 l_dupconv_count := 0;
4819 l_conv_count := 0;
4820 l_actconv_count := 0;
4821 l_unconv_count := 0;
4822 --
4823 l_faterrs_count := 0;
4824 l_rcoerr_count := 0;
4825 --
4826 l_chunkrow_count := 0;
4827 --
4828 g_ecr_success_adj_val_set.delete;
4829 g_ecr_failed_adj_val_set.delete;
4830 g_ecr_rcoerr_val_set.delete;
4831 g_ecr_fatal_error_val_set.delete;
4832 --
4833 -- Check if EFC process parameters are set
4834 --
4835 if p_action_id is not null
4836 and p_pk1 is not null
4837 and p_chunk is not null
4838 and p_efc_worker_id is not null
4839 then
4840 --
4841 l_efc_batch := TRUE;
4842 --
4843 end if;
4844 --
4845 l_from_str := ' FROM ben_enrt_rt ecr ';
4846 --
4847 l_where_str := ' where (ecr.val is not null '
4848 ||' or ecr.ann_val is not null '
4849 ||' or ecr.dflt_val is not null '
4850 ||' or ecr.ann_dflt_val is not null '
4851 ||' or ecr.cmcd_val is not null '
4852 ||' ) '
4853 ;
4854 --
4855 -- Check if we are restricting by business group
4856 --
4857 if p_business_group_id is not null then
4858 --
4859 l_where_str := l_where_str||' and ecr.business_group_id = '||p_business_group_id;
4860 --
4861 end if;
4862 --
4863 -- Build in batch specific restrictions
4864 --
4865 if l_efc_batch then
4866 --
4867 l_from_str := l_from_str||', ben_enrt_rt_efc efc ';
4868 l_where_str := l_where_str||' and efc.enrt_rt_id = ecr.enrt_rt_id '
4869 ||' and efc.efc_action_id = :action_id '
4870 ||' and ecr.enrt_rt_id > :pk1 '
4871 ||' and mod(ecr.enrt_rt_id, :total_workers) = :worker_id ';
4872 --
4873 elsif p_valworker_id is not null
4874 and p_valtotal_workers is not null
4875 then
4876 --
4877 l_where_str := l_where_str||' and mod(ecr.enrt_rt_id, :valtotal_workers) = :valworker_id ';
4878 --
4879 end if;
4880 --
4881 l_sql_str := ' select ecr.enrt_rt_id, '
4882 ||' ecr.elig_per_elctbl_chc_id, '
4883 ||' ecr.enrt_bnft_id, '
4884 ||' ecr.acty_base_rt_id, '
4885 ||' ecr.business_group_id, '
4886 ||' ecr.cmcd_mn_elcn_val, '
4887 ||' ecr.cmcd_mx_elcn_val, '
4888 ||' ecr.cmcd_val, '
4889 ||' ecr.cmcd_dflt_val, '
4890 ||' ecr.ann_dflt_val, '
4891 ||' ecr.dsply_mn_elcn_val, '
4892 ||' ecr.dsply_mx_elcn_val, '
4893 ||' ecr.dflt_val, '
4894 ||' ecr.ann_val, '
4895 ||' ecr.ann_mn_elcn_val, '
4896 ||' ecr.ann_mx_elcn_val, '
4897 ||' ecr.mx_elcn_val, '
4898 ||' ecr.mn_elcn_val, '
4899 ||' ecr.incrmt_elcn_val, '
4900 ||' ecr.val, '
4901 ||' ecr.last_update_date, '
4902 ||' ecr.creation_date, '
4903 ||' ecr.object_version_number '
4904 ||l_from_str
4905 ||l_where_str
4906 ||' order by ecr.enrt_rt_id ';
4907 --
4908 if l_efc_batch then
4909 --
4910 /*
4911 l_sql_str := l_sql_str||' FOR UPDATE OF ecr.enrt_rt_id ';
4912 --
4913 */
4914 hr_efc_info.insert_line('-- ');
4915 hr_efc_info.insert_line('-- Adjusting enrolment rates. Worker: '||p_worker_id
4916 ||' of '||p_total_workers
4917 );
4918 hr_efc_info.insert_line('-- ');
4919 --
4920 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
4921 --
4922 elsif p_valworker_id is not null
4923 and p_valtotal_workers is not null
4924 then
4925 --
4926 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
4927 --
4928 else
4929 --
4930 hr_efc_info.insert_line('-- ');
4931 hr_efc_info.insert_line('-- Validating enrolment rate adjustments');
4932 hr_efc_info.insert_line('-- ');
4933 --
4934 open c_efc_rows FOR l_sql_str;
4935 --
4936 end if;
4937 --
4938 ben_epe_cache.clear_down_cache;
4939 --
4940 loop
4941 --
4942 FETCH c_efc_rows INTO l_efc_row;
4943 EXIT WHEN c_efc_rows%NOTFOUND;
4944 --
4945 l_faterr_code := null;
4946 l_adjfailed := FALSE;
4947 --
4948 if l_faterr_code is null
4949 then
4950 --
4951 -- Detect EPE or ENB Info
4952 --
4953 ben_efc_adjustments.DetectEPEENBInfo
4954 (p_elig_per_elctbl_chc_id => l_efc_row.elig_per_elctbl_chc_id
4955 ,p_enrt_bnft_id => l_efc_row.enrt_bnft_id
4956 --
4957 ,p_currpil_row => l_currpil_row
4958 ,p_currepe_row => l_currepe_row
4959 ,p_faterr_code => l_faterr_code
4960 ,p_faterr_type => l_faterr_type
4961 );
4962 --
4963 l_lf_evt_ocrd_dt := l_currpil_row.lf_evt_ocrd_dt;
4964 l_person_id := l_currpil_row.person_id;
4965 l_per_in_ler_id := l_currpil_row.per_in_ler_id;
4966 --
4967 -- Set up benefits environment
4968 --
4969 ben_env_object.init
4970 (p_business_group_id => l_efc_row.business_group_id
4971 ,p_effective_date => l_lf_evt_ocrd_dt
4972 ,p_thread_id => 1
4973 ,p_chunk_size => 10
4974 ,p_threads => 1
4975 ,p_max_errors => 100
4976 ,p_benefit_action_id => 99999
4977 ,p_audit_log_flag => 'N'
4978 );
4979 --
4980 end if;
4981 --
4982 -- Check ABR info
4983 --
4984 if l_faterr_code is null then
4985 --
4986 open c_abrdets
4987 (c_abr_id => l_efc_row.acty_base_rt_id
4988 ,c_eff_date => l_lf_evt_ocrd_dt
4989 );
4990 fetch c_abrdets into l_abrdets;
4991 if c_abrdets%notfound then
4992 --
4993 l_faterr_code := 'NODTABR';
4994 l_faterr_type := 'DELETEDINFO';
4995 --
4996 end if;
4997 close c_abrdets;
4998 --
4999 end if;
5000 --
5001 -- Check for flat amount
5002 --
5003 if l_abrdets.rt_mlt_cd = 'FLFX'
5004 and l_faterr_code is null
5005 then
5006 --
5007 l_faterr_code := 'ABRMCFLFX';
5008 l_faterr_type := 'VALIDEXCLUSION';
5009 --
5010 end if;
5011 --
5012 -- Validate PGM info
5013 --
5014 if l_currepe_row.pgm_id is not null
5015 and l_faterr_code is null
5016 then
5017 --
5018 open c_pgmdets
5019 (c_pgm_id => l_currepe_row.pgm_id
5020 ,c_eff_date => l_lf_evt_ocrd_dt
5021 );
5022 fetch c_pgmdets into l_pgmdets;
5023 if c_pgmdets%notfound then
5024 --
5025 l_faterr_code := 'NODTPGM';
5026 --
5027 end if;
5028 close c_pgmdets;
5029 --
5030 end if;
5031 --
5032 if l_faterr_code is null then
5033 --
5034 -- Check actual premium info
5035 --
5036 if l_abrdets.rt_mlt_cd = 'AP'
5037 and l_abrdets.actl_prem_id is not null
5038 and l_faterr_code is null
5039 then
5040 --
5041 open c_apr
5042 (c_apr_id => l_abrdets.actl_prem_id
5043 ,c_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
5044 );
5045 fetch c_apr into l_apr;
5046 if c_apr%notfound then
5047 --
5048 l_faterr_code := 'NODTAPR';
5049 l_faterr_type := 'DELETEDINFO';
5050 --
5051 end if;
5052 close c_apr;
5053 --
5054 if l_apr.uom = 'EUR'
5055 and l_faterr_code is null
5056 then
5057 --
5058 l_faterr_code := 'EUROUOM';
5059 l_faterr_type := 'VALIDEXCLUSION';
5060 --
5061 end if;
5062 --
5063 end if;
5064 --
5065 end if;
5066 --
5067 -- Check the coverage has not been modified
5068 --
5069 if l_faterr_code is null
5070 and l_efc_row.enrt_bnft_id is not null
5071 then
5072 --
5073 open c_enb_dets
5074 (c_enb_id => l_efc_row.enrt_bnft_id
5075 );
5076 fetch c_enb_dets into l_enb_dets;
5077 close c_enb_dets;
5078 --
5079 end if;
5080 --
5081 -- Check primary assignment information for comp related calcs
5082 --
5083 if l_faterr_code is null then
5084 --
5085 ben_efc_adjustments.DetectInvAsg
5086 (p_person_id => l_person_id
5087 ,p_eff_date => l_lf_evt_ocrd_dt
5088 --
5089 ,p_perasg => l_perasg
5090 ,p_noasgpay => l_detected
5091 );
5092 --
5093 if l_detected then
5094 --
5095 l_faterr_code := 'NOASGPAY';
5096 l_faterr_type := 'MISSINGSETUP';
5097 --
5098 end if;
5099 --
5100 end if;
5101 --
5102 if l_abrdets.rt_mlt_cd in ('CL')
5103 and l_faterr_code is null
5104 then
5105 --
5106 if l_perasg.pay_basis_id is null then
5107 --
5108 l_faterr_code := 'NOASGPBB';
5109 l_faterr_type := 'MISSINGSETUP';
5110 --
5111 end if;
5112 --
5113 -- Check if the pay proposal exists for the assignment
5114 --
5115 if l_faterr_code is null then
5116 --
5117 open c_asgppp
5118 (c_asg_id => l_perasg.assignment_id
5119 );
5120 fetch c_asgppp into l_asgppp;
5121 if c_asgppp%notfound then
5122 --
5123 l_faterr_code := 'NOASGPPP';
5124 l_faterr_type := 'MISSINGSETUP';
5125 --
5126 end if;
5127 close c_asgppp;
5128 --
5129 end if;
5130 --
5131 end if;
5132 --
5133 -- Validate premimum info
5134 --
5135 if l_abrdets.rt_mlt_cd in ('APANDCVG','AP')
5136 and l_faterr_code is null
5137 and l_currepe_row.ELIG_PER_ELCTBL_CHC_id is not null
5138 then
5139 --
5140 open c_epeaprepr
5141 (c_actl_prem_id => l_abrdets.actl_prem_id
5142 ,c_elig_per_elctbl_chc_id => l_currepe_row.ELIG_PER_ELCTBL_CHC_id
5143 );
5144 fetch c_epeaprepr into l_epeaprepr;
5145 if c_epeaprepr%notfound then
5146 --
5147 l_faterr_code := 'NOEPEAPREPR';
5148 --
5149 end if;
5150 close c_epeaprepr;
5151 --
5152 -- Check for a voided or backed out life event
5153 --
5154 if l_epeaprepr.per_in_ler_stat_cd in ('VOIDD','BCKDT') then
5155 --
5156 l_faterr_code := 'VOIDBACKPIL';
5157 l_faterr_type := 'VALIDEXCLUSION';
5158 --
5159 end if;
5160 --
5161 end if;
5162 --
5163 if l_faterr_code is null then
5164 --
5165 -- Clear the ATP/PTA cache
5166 --
5167 ben_distribute_rates.clear_down_cache;
5168 --
5169 -- Detect EPE or ENB Info
5170 --
5171 ben_efc_adjustments.DetectEPEENBInfo
5172 (p_elig_per_elctbl_chc_id => l_efc_row.elig_per_elctbl_chc_id
5173 ,p_enrt_bnft_id => l_efc_row.enrt_bnft_id
5174 --
5175 ,p_detect_mode => 'EPEINFO'
5176 --
5177 ,p_currpil_row => l_currpil_row
5178 ,p_currepe_row => l_currepe_row
5179 ,p_faterr_code => l_faterr_code
5180 ,p_faterr_type => l_faterr_type
5181 );
5182 --
5183 begin
5184 --
5185 -- Check for a business group change and refresh vapro caches
5186 --
5187 if l_efc_row.business_group_id <> nvl(l_prev_bgp_id,-9999)
5188 then
5189 --
5190 ben_rt_prfl_cache.clear_down_cache;
5191 l_prev_bgp_id := l_efc_row.business_group_id;
5192 --
5193 end if;
5194 ben_determine_activity_base_rt.main
5195 (p_currepe_row => l_currepe_row
5196 ,p_per_row => l_per_row
5197 ,p_asg_row => l_asg_row
5198 ,p_ast_row => l_ast_row
5199 ,p_adr_row => l_adr_row
5200 ,p_person_id => l_person_id
5201 ,p_elig_per_elctbl_chc_id => l_currepe_row.ELIG_PER_ELCTBL_CHC_id
5202 ,p_enrt_bnft_id => l_efc_row.enrt_bnft_id
5203 ,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
5204 ,p_effective_date => l_lf_evt_ocrd_dt
5205 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
5206 ,p_perform_rounding_flg => TRUE
5207 ,p_val => l_val
5208 ,p_mn_elcn_val => l_mn_elcn_val
5209 ,p_mx_elcn_val => l_mx_elcn_val
5210 ,p_ann_val => l_ann_val
5211 ,p_ann_mn_elcn_val => l_ann_mn_elcn_val
5212 ,p_ann_mx_elcn_val => l_ann_mx_elcn_val
5213 ,p_cmcd_val => l_cmcd_val
5214 ,p_cmcd_mn_elcn_val => l_cmcd_mn_elcn_val
5215 ,p_cmcd_mx_elcn_val => l_cmcd_mx_elcn_val
5216 ,p_incrmt_elcn_val => l_incrmt_elcn_val
5217 ,p_dflt_val => l_dflt_val
5218 ,p_ann_dflt_val => l_ann_dflt_val
5219 ,p_dsply_mn_elcn_val => l_dsply_mn_elcn_val
5220 ,p_dsply_mx_elcn_val => l_dsply_mx_elcn_val
5221 --
5222 ,p_cmcd_acty_ref_perd_cd => l_dummy_varchar2
5223 ,p_tx_typ_cd => l_dummy_varchar2
5224 ,p_acty_typ_cd => l_dummy_varchar2
5225 ,p_nnmntry_uom => l_dummy_varchar2
5226 ,p_entr_val_at_enrt_flag => l_dummy_varchar2
5227 ,p_dsply_on_enrt_flag => l_dummy_varchar2
5228 ,p_use_to_calc_net_flx_cr_flag => l_dummy_varchar2
5229 ,p_rt_usg_cd => l_dummy_varchar2
5230 ,p_bnft_prvdr_pool_id => l_dummy_number
5231 ,p_actl_prem_id => l_dummy_number
5232 ,p_cvg_calc_amt_mthd_id => l_dummy_number
5233 ,p_bnft_rt_typ_cd => l_dummy_varchar2
5234 ,p_rt_typ_cd => l_dummy_varchar2
5235 ,p_rt_mlt_cd => l_dummy_varchar2
5236 ,p_comp_lvl_fctr_id => l_dummy_number
5237 ,p_entr_ann_val_flag => l_dummy_varchar2
5238 ,p_ptd_comp_lvl_fctr_id => l_dummy_number
5239 ,p_clm_comp_lvl_fctr_id => l_dummy_number
5240 ,p_rt_strt_dt => l_dummy_date
5241 ,p_rt_strt_dt_cd => l_dummy_varchar2
5242 ,p_rt_strt_dt_rl => l_dummy_number
5243 ,p_prtt_rt_val_id => l_dummy_number
5244 ,p_pp_in_yr_used_num => l_dummy_number
5245 ,p_ordr_num => l_dummy_number
5246 ,p_iss_val => l_dummy_number
5247 );
5248 --
5249 if l_efc_row.val <> nvl(l_val,-999999)
5250 then
5251 --
5252 l_adjfailed := TRUE;
5253 l_val_type := 'ECR_VAL';
5254 l_old_val1 := l_efc_row.val;
5255 l_new_val1 := l_val;
5256 --
5257 elsif l_efc_row.ann_val <> nvl(l_ann_val,-999999)
5258 then
5259 --
5260 l_adjfailed := TRUE;
5261 l_val_type := 'ECR_ANNVAL';
5262 l_old_val1 := l_efc_row.ann_val;
5263 l_new_val1 := l_ann_val;
5264 --
5265 elsif l_efc_row.dflt_val <> nvl(l_dflt_val,-999999)
5266 then
5267 --
5268 l_adjfailed := TRUE;
5269 l_val_type := 'ECR_DFLTVAL';
5270 l_old_val1 := l_efc_row.dflt_val;
5271 l_new_val1 := l_dflt_val;
5272 --
5273 elsif l_efc_row.ann_dflt_val <> nvl(l_ann_dflt_val,-999999)
5274 then
5275 --
5276 l_adjfailed := TRUE;
5277 l_val_type := 'ECR_ANNDFLTVAL';
5278 l_old_val1 := l_efc_row.ann_dflt_val;
5279 l_new_val1 := l_ann_dflt_val;
5280 --
5281 elsif l_efc_row.cmcd_val <> nvl(l_cmcd_val,-999999)
5282 then
5283 --
5284 l_adjfailed := TRUE;
5285 l_val_type := 'ECR_CMCDVAL';
5286 l_old_val1 := l_efc_row.cmcd_val;
5287 l_new_val1 := l_cmcd_val;
5288 --
5289 -- Copied straight from a Vapro or ABR. No adjustment required.
5290 --
5291 /*
5292 elsif l_efc_row.incrmt_elcn_val <> nvl(l_incrmt_elcn_val,-999999)
5293 then
5294 --
5295 l_adjfailed := TRUE;
5296 l_val_type := 'ECR_INCELVAL';
5297 l_old_val1 := l_efc_row.incrmt_elcn_val;
5298 l_new_val1 := l_incrmt_elcn_val;
5299 --
5300 elsif l_efc_row.mx_elcn_val <> nvl(l_mx_elcn_val,-999999)
5301 then
5302 --
5303 l_adjfailed := TRUE;
5304 l_val_type := 'ECR_MXELVAL';
5305 l_old_val1 := l_efc_row.mx_elcn_val;
5306 l_new_val1 := l_mx_elcn_val;
5307 --
5308 elsif l_efc_row.ann_mx_elcn_val <> nvl(l_ann_mx_elcn_val,-999999)
5309 then
5310 --
5311 l_adjfailed := TRUE;
5312 l_val_type := 'ECR_ANNMXELVAL';
5313 l_old_val1 := l_efc_row.ann_mx_elcn_val;
5314 l_new_val1 := l_ann_mx_elcn_val;
5315 --
5316 elsif l_efc_row.dsply_mx_elcn_val <> nvl(l_dsply_mx_elcn_val,-999999)
5317 then
5318 --
5319 l_adjfailed := TRUE;
5320 l_val_type := 'ECR_DSMXELVAL';
5321 l_old_val1 := l_efc_row.dsply_mx_elcn_val;
5322 l_new_val1 := l_dsply_mx_elcn_val;
5323 --
5324 elsif l_efc_row.cmcd_mx_elcn_val <> nvl(l_cmcd_mx_elcn_val,-999999)
5325 then
5326 --
5327 l_adjfailed := TRUE;
5328 l_val_type := 'ECR_CMCDMXELVAL';
5329 l_old_val1 := l_efc_row.cmcd_mx_elcn_val;
5330 l_new_val1 := l_cmcd_mx_elcn_val;
5331 --
5332 elsif l_efc_row.mn_elcn_val <> nvl(l_mn_elcn_val,-999999)
5333 then
5334 --
5335 l_adjfailed := TRUE;
5336 l_val_type := 'ECR_MNELVAL';
5337 l_old_val1 := l_efc_row.mn_elcn_val;
5338 l_new_val1 := l_mn_elcn_val;
5339 --
5340 elsif l_efc_row.ann_mn_elcn_val <> nvl(l_ann_mn_elcn_val,-999999)
5341 then
5342 --
5343 l_adjfailed := TRUE;
5344 l_val_type := 'ECR_ANNMNELVAL';
5345 l_old_val1 := l_efc_row.ann_mn_elcn_val;
5346 l_new_val1 := l_ann_mn_elcn_val;
5347 --
5348 elsif l_efc_row.dsply_mn_elcn_val <> nvl(l_dsply_mn_elcn_val,-999999)
5349 then
5350 --
5351 l_adjfailed := TRUE;
5352 l_val_type := 'ECR_DSMNELVAL';
5353 l_old_val1 := l_efc_row.dsply_mn_elcn_val;
5354 l_new_val1 := l_dsply_mn_elcn_val;
5355 --
5356 elsif l_efc_row.cmcd_mn_elcn_val <> nvl(l_cmcd_mn_elcn_val,-999999)
5357 then
5358 --
5359 l_adjfailed := TRUE;
5360 l_val_type := 'ECR_CMCDMNELVAL';
5361 l_old_val1 := l_efc_row.cmcd_mn_elcn_val;
5362 l_new_val1 := l_cmcd_mn_elcn_val;
5363 --
5364 */
5365 else
5366 --
5367 l_faterr_code := null;
5368 l_adjfailed := FALSE;
5369 l_val_type := 'ECR_VAL';
5370 --
5371 -- Added so that converted vals can be checked
5372 --
5373 l_old_val1 := l_efc_row.val;
5374 l_new_val1 := l_val;
5375 --
5376 end if;
5377 --
5378 -- Success and failure checks
5379 --
5380 -- Check for a non monetary UOM
5381 --
5382 if l_abrdets.NNMNTRY_UOM is not null
5383 and l_faterr_code is null
5384 then
5385 --
5386 l_faterr_code := 'ABRNONMONUOM';
5387 l_faterr_type := 'VALIDEXCLUSION';
5388 --
5389 -- Check for enter value at enrolment
5390 --
5391 elsif l_abrdets.entr_val_at_enrt_flag = 'Y'
5392 and l_faterr_code is null
5393 then
5394 --
5395 l_faterr_code := 'ABREVAEFLGY';
5396 l_faterr_type := 'VALIDEXCLUSION';
5397 --
5398 end if;
5399 --
5400 -- Check if the adjustment failed
5401 --
5402 if l_adjfailed then
5403 --
5404 -- Get the currency conversion factor details
5405 --
5406 if l_faterr_code is null
5407 and l_efc_batch
5408 then
5409 --
5410 if l_faterr_code is null then
5411 --
5412 -- get pre conversion details
5413 --
5414 open c_preconvdets
5415 (c_efc_action_id => p_action_id
5416 ,c_ecr_id => l_efc_row.enrt_rt_id
5417 );
5418 fetch c_preconvdets into l_preconvdets;
5419 if c_preconvdets%notfound then
5420 --
5421 l_faterr_code := 'NOEFCACTEEV';
5422 l_faterr_type := 'CORRUPTDATA';
5423 --
5424 end if;
5425 close c_preconvdets;
5426 --
5427 end if;
5428 --
5429 if l_faterr_code is null then
5430 --
5431 if l_val_type = 'ECR_VAL' then
5432 --
5433 l_preconv_val := l_preconvdets.val;
5434 --
5435 elsif l_val_type = 'ECR_ANNVAL' then
5436 --
5437 l_preconv_val := l_preconvdets.ANN_VAL;
5438 --
5439 elsif l_val_type = 'ECR_DFLTVAL' then
5440 --
5441 l_preconv_val := l_preconvdets.DFLT_VAL;
5442 --
5443 elsif l_val_type = 'ECR_ANNDFLTVAL' then
5444 --
5445 l_preconv_val := l_preconvdets.ANN_DFLT_VAL;
5446 --
5447 elsif l_val_type = 'ECR_CMCDVAL' then
5448 --
5449 l_preconv_val := l_preconvdets.CMCD_VAL;
5450 --
5451 /*
5452 --
5453 -- Copied straight from a Vapro or ABR. No adjustment required.
5454 --
5455 elsif l_val_type = 'ECR_INCELVAL' then
5456 --
5457 l_preconv_val := l_preconvdets.INCRMT_ELCN_VAL;
5458 --
5459 elsif l_val_type = 'ECR_MXELVAL' then
5460 --
5461 l_preconv_val := l_preconvdets.MX_ELCN_VAL;
5462 --
5463 elsif l_val_type = 'ECR_MNELVAL' then
5464 --
5465 l_preconv_val := l_preconvdets.MN_ELCN_VAL;
5466 --
5467 elsif l_val_type = 'ECR_ANNMXELVAL' then
5468 --
5469 l_preconv_val := l_preconvdets.ANN_MX_ELCN_VAL;
5470 --
5471 elsif l_val_type = 'ECR_ANNMNELVAL' then
5472 --
5473 l_preconv_val := l_preconvdets.ANN_MN_ELCN_VAL;
5474 --
5475 elsif l_val_type = 'ECR_DSMNELVAL' then
5476 --
5477 l_preconv_val := l_preconvdets.DSPLY_MN_ELCN_VAL;
5478 --
5479 elsif l_val_type = 'ECR_DSMXELVAL' then
5480 --
5481 l_preconv_val := l_preconvdets.DSPLY_MX_ELCN_VAL;
5482 --
5483 elsif l_val_type = 'ECR_CMCDMNELVAL' then
5484 --
5485 l_preconv_val := l_preconvdets.CMCD_MN_ELCN_VAL;
5486 --
5487 elsif l_val_type = 'ECR_CMCDMXELVAL' then
5488 --
5489 l_preconv_val := l_preconvdets.CMCD_MX_ELCN_VAL;
5490 --
5491 */
5492 end if;
5493 --
5494 /*
5495 ben_efc_adjustments.DetectConvInfo
5496 (p_ncucurr_code => l_preconvdets.uom
5497 ,p_new_val => l_new_val1
5498 ,p_preconv_val => l_preconv_val
5499 --
5500 ,p_faterr_code => l_faterr_code
5501 ,p_faterr_type => l_faterr_type
5502 ,p_postconv_val => l_postconv_val
5503 );
5504 --
5505 if l_faterr_code is not null then
5506 --
5507 l_old_val1 := l_preconv_val;
5508 l_new_val1 := l_postconv_val;
5509 --
5510 end if;
5511 --
5512 */
5513 end if;
5514 --
5515 end if;
5516 --
5517 -- Check for hard coded rounding code
5518 --
5519 if l_faterr_code is null then
5520 --
5521 ben_efc_adjustments.DetectRoundInfo
5522 (p_rndg_cd => l_abrdets.rndg_cd
5523 ,p_rndg_rl => l_abrdets.rndg_rl
5524 ,p_old_val => l_old_val1
5525 ,p_new_val => l_new_val1
5526 ,p_eff_date => l_lf_evt_ocrd_dt
5527 --
5528 ,p_faterr_code => l_faterr_code
5529 ,p_faterr_type => l_faterr_type
5530 );
5531 --
5532 if (nvl(l_abrdets.last_update_date,hr_api.g_sot)
5533 > nvl(l_efc_row.last_update_date,hr_api.g_eot))
5534 and l_faterr_code is null
5535 then
5536 --
5537 l_faterr_code := 'ABRCORR';
5538 l_faterr_type := 'CORRECTEDINFO';
5539 --
5540 end if;
5541 --
5542 end if;
5543 --
5544 -- Check VAPRO info
5545 --
5546 if l_faterr_code is null
5547 and l_abrdets.use_calc_acty_bs_rt_flag = 'Y'
5548 then
5549 --
5550 -- Validate vapro
5551 --
5552 ben_efc_adjustments.DetectVAPROInfo
5553 (p_currepe_row => l_currepe_row
5554 --
5555 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
5556 ,p_last_update_date => l_efc_row.last_update_date
5557 --
5558 ,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
5559 --
5560 ,p_vpfdets => l_vpfdets
5561 ,p_vpf_id => l_vpf_id
5562 ,p_faterr_code => l_faterr_code
5563 ,p_faterr_type => l_faterr_type
5564 );
5565 --
5566 -- Check for a replace rate type. A code fix has been made since the
5567 -- original info was created.
5568 --
5569 if l_vpfdets.vrbl_rt_trtmt_cd = 'RPLC'
5570 and l_faterr_code is null
5571 then
5572 --
5573 l_faterr_code := 'INVADJRPLCVPF';
5574 l_faterr_type := 'CODECHANGE';
5575 --
5576 end if;
5577 --
5578 end if;
5579 --
5580 -- Check for a null value
5581 --
5582 if l_new_val1 is null
5583 and l_faterr_code is null
5584 then
5585 --
5586 l_faterr_code := 'NULLADJECRVAL';
5587 l_faterr_type := 'ADJUSTBUG';
5588 --
5589 end if;
5590 --
5591 if l_faterr_code is null then
5592 --
5593 ben_efc_adjustments.DetectWhoInfo
5594 (p_creation_date => l_efc_row.creation_date
5595 ,p_last_update_date => l_efc_row.last_update_date
5596 ,p_object_version_number => l_efc_row.object_version_number
5597 --
5598 ,p_who_counts => l_who_counts
5599 ,p_faterr_code => l_faterr_code
5600 ,p_faterr_type => l_faterr_type
5601 );
5602 --
5603 end if;
5604 --
5605 if l_faterr_code is null then
5606 --
5607 g_ecr_failed_adj_val_set(l_calfail_count).id := l_efc_row.enrt_rt_id;
5608 g_ecr_failed_adj_val_set(l_calfail_count).old_val1 := l_old_val1;
5609 g_ecr_failed_adj_val_set(l_calfail_count).new_val1 := l_new_val1;
5610 g_ecr_failed_adj_val_set(l_calfail_count).val_type := l_val_type;
5611 g_ecr_failed_adj_val_set(l_calfail_count).code1 := l_abrdets.rt_mlt_cd;
5612 g_ecr_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
5613 g_ecr_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
5614 --
5615 l_calfail_count := l_calfail_count+1;
5616 --
5617 end if;
5618 --
5619 end if;
5620 --
5621 if l_efc_batch
5622 and (l_faterr_code is null
5623 or nvl(l_faterr_type,'ZZZZ') = 'CONVEXCLUSION')
5624 then
5625 --
5626 update ben_enrt_rt ecr
5627 set ecr.val = l_val,
5628 ecr.ann_val = l_ann_val,
5629 ecr.dflt_val = l_dflt_val,
5630 ecr.ann_dflt_val = l_ann_dflt_val,
5631 ecr.cmcd_val = l_cmcd_val
5632 /*
5633 --
5634 -- Copied straight from a Vapro or ABR. No adjustment required.
5635 --
5636 ecr.cmcd_mn_elcn_val = l_cmcd_mn_elcn_val,
5637 ecr.cmcd_mx_elcn_val = l_cmcd_mx_elcn_val,
5638 ecr.dsply_mn_elcn_val = l_dsply_mn_elcn_val,
5639 ecr.dsply_mx_elcn_val = l_dsply_mx_elcn_val,
5640 ecr.ann_mn_elcn_val = l_ann_mn_elcn_val,
5641 ecr.ann_mx_elcn_val = l_ann_mx_elcn_val,
5642 ecr.mx_elcn_val = l_mx_elcn_val,
5643 ecr.mn_elcn_val = l_mn_elcn_val,
5644 ecr.incrmt_elcn_val = l_incrmt_elcn_val,
5645 */
5646 where ecr.enrt_rt_id = l_efc_row.enrt_rt_id;
5647 --
5648 -- Check for end of chunk and commit if necessary
5649 --
5650 l_pk1 := l_efc_row.enrt_rt_id;
5651 --
5652 ben_efc_functions.maintain_chunks
5653 (p_row_count => l_chunkrow_count
5654 ,p_pk1 => l_pk1
5655 ,p_chunk_size => p_chunk
5656 ,p_efc_worker_id => p_efc_worker_id
5657 );
5658 --
5659 end if;
5660 --
5661 exception
5662 when others then
5663 --
5664 ben_efc_adjustments.DetectAppError
5665 (p_sqlerrm => SQLERRM
5666 ,p_abr_rt_mlt_cd => l_abrdets.rt_mlt_cd
5667 ,p_abr_val => l_abrdets.val
5668 ,p_abr_entr_val_at_enrt_flag => l_abrdets.entr_val_at_enrt_flag
5669 ,p_abr_id => l_efc_row.acty_base_rt_id
5670 ,p_eff_date => l_lf_evt_ocrd_dt
5671 --
5672 ,p_faterr_code => l_faterr_code
5673 ,p_faterr_type => l_faterr_type
5674 );
5675 --
5676 if instr(SQLERRM,'92749') > 0
5677 then
5678 --
5679 l_faterr_code := 'NOSTSALSTCOMP';
5680 l_faterr_type := 'MISSINGSETUP';
5681 --
5682 elsif instr(SQLERRM,'92741') > 0
5683 then
5684 --
5685 l_faterr_code := 'NOPLNCCMCVGMC';
5686 l_faterr_type := 'DELETEDINFO';
5687 --
5688 elsif instr(SQLERRM,'92746') > 0
5689 then
5690 --
5691 l_faterr_code := 'NULLCOMP';
5692 l_faterr_type := 'POTENTIALCODEBUG';
5693 --
5694 elsif instr(SQLERRM,'92748') > 0
5695 then
5696 --
5697 l_faterr_code := 'NULLENBIDCVGMC';
5698 l_faterr_type := 'POTENTIALCODEBUG';
5699 --
5700 end if;
5701 --
5702 if l_faterr_code is null then
5703 --
5704 g_ecr_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.enrt_rt_id;
5705 g_ecr_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENACTBR';
5706 g_ecr_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
5707 g_ecr_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
5708 g_ecr_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
5709 --
5710 l_rcoerr_count := l_rcoerr_count+1;
5711 --
5712 end if;
5713 --
5714 end;
5715 --
5716 end if;
5717 --
5718 -- Check for fatal errors
5719 --
5720 if l_faterr_code is not null
5721 then
5722 --
5723 g_ecr_fatal_error_val_set(l_faterrs_count).id := l_efc_row.enrt_rt_id;
5724 g_ecr_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
5725 g_ecr_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
5726 g_ecr_fatal_error_val_set(l_faterrs_count).val_type := l_val_type;
5727 g_ecr_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
5728 g_ecr_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
5729 g_ecr_fatal_error_val_set(l_faterrs_count).code1 := l_abrdets.rt_mlt_cd;
5730 g_ecr_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
5731 g_ecr_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
5732 --
5733 l_faterrs_count := l_faterrs_count+1;
5734 --
5735 elsif l_faterr_code is null
5736 and not l_adjfailed
5737 then
5738 --
5739 g_ecr_success_adj_val_set(l_calsucc_count).id := l_efc_row.enrt_rt_id;
5740 g_ecr_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
5741 g_ecr_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
5742 g_ecr_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
5743 g_ecr_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
5744 --
5745 l_calsucc_count := l_calsucc_count+1;
5746 --
5747 end if;
5748 --
5749 l_row_count := l_row_count+1;
5750 --
5751 end loop;
5752 CLOSE c_efc_rows;
5753 /*
5754 --
5755 -- Write exceptions down to the table
5756 --
5757 if l_efc_batch then
5758 --
5759 ben_efc_adjustments.insert_validation_exceptions
5760 (p_val_set => g_ecr_failed_adj_val_set
5761 ,p_efc_action_id => p_action_id
5762 ,p_ent_scode => 'ECR'
5763 ,p_exception_type => 'AF'
5764 );
5765 --
5766 ben_efc_adjustments.insert_validation_exceptions
5767 (p_val_set => g_ecr_fatal_error_val_set
5768 ,p_efc_action_id => p_action_id
5769 ,p_ent_scode => 'ECR'
5770 ,p_exception_type => null
5771 );
5772 --
5773 end if;
5774 */
5775 --
5776 -- Check that all rows have been converted or excluded
5777 --
5778 ben_efc_functions.conv_check
5779 (p_table_name => 'ben_enrt_rt'
5780 ,p_efctable_name => 'ben_enrt_rt_efc'
5781 ,p_tabwhere_clause => ' (cmcd_mn_elcn_val is not null '
5782 ||' or cmcd_mn_elcn_val is not null '
5783 ||' or cmcd_mx_elcn_val is not null '
5784 ||' or cmcd_val is not null '
5785 ||' or cmcd_dflt_val is not null '
5786 ||' or ann_dflt_val is not null '
5787 ||' or dsply_mn_elcn_val is not null '
5788 ||' or dsply_mx_elcn_val is not null '
5789 ||' or dflt_val is not null '
5790 ||' or ann_val is not null '
5791 ||' or ann_mn_elcn_val is not null '
5792 ||' or ann_mx_elcn_val is not null '
5793 ||' or mx_elcn_val is not null '
5794 ||' or mn_elcn_val is not null '
5795 ||' or incrmt_elcn_val is not null '
5796 ||' or val is not null) '
5797 --
5798 ,p_bgp_id => p_business_group_id
5799 ,p_action_id => p_action_id
5800 --
5801 ,p_conv_count => l_conv_count
5802 ,p_unconv_count => l_unconv_count
5803 ,p_tabrow_count => l_tabrow_count
5804 );
5805 --
5806 -- Set counts
5807 --
5808 if p_action_id is null then
5809 --
5810 l_actconv_count := 0;
5811 --
5812 else
5813 --
5814 l_actconv_count := l_conv_count;
5815 --
5816 end if;
5817 --
5818 p_adjustment_counts.efcrow_count := l_row_count;
5819 p_adjustment_counts.tabrow_count := l_tabrow_count;
5820 p_adjustment_counts.actconv_count := l_actconv_count;
5821 p_adjustment_counts.calfail_count := l_calfail_count;
5822 p_adjustment_counts.calsucc_count := l_calsucc_count;
5823 p_adjustment_counts.dupconv_count := l_dupconv_count;
5824 p_adjustment_counts.conv_count := l_conv_count;
5825 p_adjustment_counts.unconv_count := l_unconv_count;
5826 p_adjustment_counts.faterrs_count := l_faterrs_count;
5827 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
5828 --
5829 end ecr_adjustments;
5830 --
5831 end ben_efc_adjustments;