[Home] [Help]
PACKAGE BODY: APPS.BEN_EFC_ADJUSTMENTS1
Source
1 package body ben_efc_adjustments1 as
2 /* $Header: beefcaj1.pkb 120.1 2006/11/15 15:19:48 rtagarra 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 12-Jul-01 mhoyes Created.
15 115.1 26-Jul-01 mhoyes Enhanced for Patchset E+ patch.
16 115.2 13-Aug-01 mhoyes Enhanced for Patchset E+ patch.
17 115.3 17-Aug-01 mhoyes Enhanced for BEN July patch.
18 115.4 27-Aug-01 mhoyes Enhanced for BEN July patch.
19 115.6 13-Sep-01 mhoyes Enhanced for BEN July patch.
20 115.10 04-Jan-02 mhoyes Enhanced for BEN G patchset.
21 115.12 30-Dec-2002 mmudigon NOCOPY
22 115.13 22-Jan-04 mmudigon new param to ben_element_entry call
23 115.14 15-Nov-06 rtagarra Bug 5049253:Commented the insert into exception when there is no
24 PRV corresponding to the PIL for prv_adjustments.
25 -----------------------------------------------------------------------------
26 */
27 --
28 -- Globals.
29 --
30 g_package varchar2(50) := 'ben_efc_adjustments1.';
31 --
32 procedure prv_adjustments
33 (p_validate in boolean default false
34 ,p_worker_id in number default null
35 ,p_action_id in number default null
36 ,p_total_workers in number default null
37 ,p_pk1 in number default null
38 ,p_chunk in number default null
39 ,p_efc_worker_id in number default null
40 --
41 ,p_valworker_id in number default null
42 ,p_valtotal_workers in number default null
43 --
44 ,p_business_group_id in number default null
45 --
46 ,p_adjustment_counts out nocopy ben_efc_adjustments.g_adjustment_counts
47 )
48 is
49 --
50 TYPE cur_type IS REF CURSOR;
51 --
52 type g_efc_row is record
53 (person_id ben_per_in_ler.person_id%type
54 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
55 ,business_group_id ben_per_in_ler.business_group_id%type
56 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
57 ,creation_date ben_per_in_ler.creation_date%type
58 ,last_update_date ben_per_in_ler.last_update_date%type
59 ,object_version_number ben_per_in_ler.object_version_number%type
60 /*
61 ,enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type
62 */
63 );
64 --
65 c_efc_rows cur_type;
66 --
67 l_proc varchar2(1000) := 'prv_adjustments';
68 --
69 l_efc_row g_efc_row;
70 --
71 l_global_asg_rec ben_global_enrt.g_global_asg_rec_type;
72 --
73 l_who_counts ben_efc_adjustments.g_who_counts;
74 l_olddata boolean;
75 --
76 l_row_count pls_integer;
77 l_calfail_count pls_integer;
78 l_calsucc_count pls_integer;
79 l_conv_count pls_integer;
80 l_unconv_count pls_integer;
81 l_actconv_count pls_integer;
82 l_dupconv_count pls_integer;
83 l_faterrs_count pls_integer;
84 l_rcoerr_count pls_integer;
85 l_tabrow_count pls_integer;
86 l_chunkrow_count pls_integer;
87 --
88 l_pil_count pls_integer;
89 l_pilprv_count pls_integer;
90 l_nopilprv_count pls_integer;
91 --
92 l_tmp_count pls_integer;
93 --
94 l_sql_str long;
95 l_from_str long;
96 l_where_str long;
97 l_groupby_str long;
98 --
99 l_efc_batch boolean;
100 l_pk1 number;
101 --
102 l_prv_rtval_set ben_det_enrt_rates.PRVRtVal_tab;
103 --
104 l_effective_date date;
105 --
106 l_faterr_code varchar2(100);
107 l_faterr_type varchar2(100);
108 --
109 l_adjfailed boolean;
110 l_val_type varchar2(100);
111 l_old_val1 number;
112 l_new_val1 number;
113 --
114 l_allpilprv_count pls_integer;
115 --
116 l_dtpen_count pls_integer;
117 l_prv_rt_val number;
118 l_prv_ann_rt_val number;
119 --
120 l_rco_name varchar2(100);
121 --
122 l_ecr_count pls_integer;
123 l_prv_id number;
124 l_prv_uom varchar2(100);
125 l_ecrnomatchprv_count pls_integer;
126 --
127 l_prev_bgp_id number;
128 --
129 cursor c_prvdets
130 (c_prv_id in number
131 )
132 is
133 select prv.prtt_rt_val_id,
134 prv.rt_val,
135 prv.ann_rt_val,
136 prv.cmcd_rt_val,
137 pen.enrt_mthd_cd,
138 prv.prtt_enrt_rslt_id,
139 prv.acty_base_rt_id,
140 prv.creation_date,
141 prv.last_update_date,
142 prv.object_version_number,
143 prv.MLT_CD,
144 pil.person_id,
145 pil.per_in_ler_id
146 from ben_prtt_rt_val prv,
147 ben_prtt_enrt_rslt_f pen,
148 ben_per_in_ler pil
149 where prv.prtt_rt_val_id = c_prv_id
150 and pil.lf_evt_ocrd_dt
151 between pen.effective_start_date and pen.effective_end_date
152 and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
153 and prv.per_in_ler_id = pil.per_in_ler_id;
154 --
155 l_prvdets c_prvdets%rowtype;
156 --
157 cursor c_enbdets
158 (c_enb_id in number
159 )
160 is
161 select enb.prtt_enrt_rslt_id
162 from ben_enrt_bnft enb
163 where enb.enrt_bnft_id = c_enb_id;
164 --
165 l_enbdets c_enbdets%rowtype;
166 --
167 cursor c_pilprv
168 (c_pil_id in number
169 )
170 is
171 select prv.acty_base_rt_id,
172 prv.prtt_enrt_rslt_id,
173 prv.creation_date,
174 prv.last_update_date,
175 prv.created_by,
176 prv.last_updated_by,
177 prv.last_update_login,
178 prv.object_version_number,
179 prv.business_group_id,
180 prv.prtt_rt_val_id,
181 prv.per_in_ler_id,
182 prv.rt_val,
183 prv.ann_rt_val,
184 prv.mlt_cd,
185 pil.person_id,
186 prv.RT_END_DT
187 from ben_prtt_rt_val prv,
188 ben_per_in_ler pil
189 where prv.per_in_ler_id = c_pil_id
190 and prv.per_in_ler_id = pil.per_in_ler_id
191 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
192 --
193 l_pilprv c_pilprv%rowtype;
194 --
195 cursor c_ecrdets
196 (c_prv_id in number
197 )
198 is
199 select ecr.enrt_rt_id,
200 ecr.rt_mlt_cd,
201 ecr.enrt_bnft_id,
202 ecr.elig_per_elctbl_chc_id,
203 ecr.asn_on_enrt_flag,
204 ecr.entr_val_at_enrt_flag,
205 ecr.rt_strt_dt_cd
206 from ben_enrt_rt ecr
207 where ecr.prtt_rt_val_id = c_prv_id;
208 --
209 l_ecrdets c_ecrdets%rowtype;
210 --
211 cursor c_abrdets
212 (c_abr_id in number
213 ,c_eff_date in date
214 )
215 is
216 select abr.last_update_date,
217 abr.rt_mlt_cd,
218 abr.nnmntry_uom,
219 abr.entr_val_at_enrt_flag,
220 abr.rt_typ_cd,
221 abr.val,
222 abr.pgm_id,
223 abr.ptip_id,
224 abr.pl_id,
225 abr.plip_id,
226 abr.oipl_id,
227 abr.oiplip_id,
228 abr.actl_prem_id
229 from ben_acty_base_rt_f abr
230 where abr.acty_base_rt_id = c_abr_id
231 and c_eff_date
232 between abr.effective_start_date and abr.effective_end_date;
233 --
234 l_abrdets c_abrdets%rowtype;
235 --
236 cursor c_pendets
237 (c_pen_id in number
238 ,c_eff_date in date
239 )
240 is
241 select pen.effective_end_date,
242 pen.enrt_cvg_strt_dt,
243 pen.ENRT_CVG_THRU_DT,
244 pen.pgm_id,
245 pen.pl_id,
246 pen.oipl_id,
247 pen.prtt_enrt_rslt_stat_cd,
248 pen.enrt_ovridn_flag,
249 pen.sspndd_flag,
250 pen.enrt_mthd_cd
251 from ben_prtt_enrt_rslt_f pen
252 where pen.prtt_enrt_rslt_id = c_pen_id
253 and c_eff_date
254 between pen.effective_start_date and pen.effective_end_date;
255 --
256 l_pendets c_pendets%rowtype;
257 --
258 cursor c_dtpendets
259 (c_pen_id in number
260 )
261 is
262 select pen.effective_end_date,
263 pen.prtt_enrt_rslt_stat_cd
264 from ben_prtt_enrt_rslt_f pen
265 where pen.prtt_enrt_rslt_id = c_pen_id;
266 --
267 l_dtpendets c_dtpendets%rowtype;
268 --
269 cursor c_enrt_rt
270 (c_elig_per_elctbl_chc_id in number
271 ,c_prtt_enrt_rslt_id in number
272 )
273 is
274 select ecr.prtt_rt_val_id,
275 ecr.enrt_rt_id,
276 ecr.val,
277 ecr.ann_val,
278 ecr.rt_mlt_cd,
279 ecr.SPCL_RT_ENRT_RT_ID,
280 ecr.business_group_id,
281 ecr.enrt_bnft_id,
282 ecr.elig_per_elctbl_chc_id,
283 ecr.acty_base_rt_id,
284 ecr.entr_val_at_enrt_flag
285 from ben_enrt_rt ecr
286 where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
287 and ecr.SPCL_RT_ENRT_RT_ID is null
288 /*
289 and ecr.entr_val_at_enrt_flag = 'N'
290 */
291 /*
292 and ecr.asn_on_enrt_flag = 'Y'
293 */
294 UNION
295 select ecr.prtt_rt_val_id,
296 ecr.enrt_rt_id,
297 ecr.val,
298 ecr.ann_val,
299 ecr.rt_mlt_cd,
300 ecr.SPCL_RT_ENRT_RT_ID,
301 ecr.business_group_id,
302 ecr.enrt_bnft_id,
303 ecr.elig_per_elctbl_chc_id,
304 ecr.acty_base_rt_id,
305 ecr.entr_val_at_enrt_flag
306 from ben_enrt_bnft enb,
307 ben_enrt_rt ecr
308 where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
309 and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
310 and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
311 and ecr.SPCL_RT_ENRT_RT_ID is null
312 /*
313 and ecr.entr_val_at_enrt_flag = 'N'
314 */
315 /*
316 and ecr.asn_on_enrt_flag = 'Y'
317 */
318 ;
319 --
320 l_enrt_rt c_enrt_rt%rowtype;
321 --
322 cursor c_elctbl_chc
323 (c_pen_id number
324 ,c_pil_id number
325 ,c_eff_date date
326 )
327 is
328 select epe.pgm_id,
329 epe.pl_id,
330 epe.oipl_id,
331 epe.elig_per_elctbl_chc_id,
332 epe.spcl_rt_pl_id,
333 epe.spcl_rt_oipl_id,
334 pel.acty_ref_perd_cd
335 from ben_elig_per_elctbl_chc epe,
336 ben_per_in_ler pil,
337 ben_pil_elctbl_chc_popl pel,
338 ben_prtt_enrt_rslt_f pen
339 where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
340 and pil.per_in_ler_id = epe.per_in_ler_id
341 and pil.per_in_ler_id = c_pil_id
342 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
343 and pen.prtt_enrt_rslt_id = c_pen_id
344 and nvl(pen.pgm_id,-1) = nvl(epe.pgm_id,-1)
345 and pen.pl_id = epe.pl_id
346 and nvl(pen.oipl_id,-1) = nvl(epe.oipl_id,-1)
347 and c_eff_date
348 between pen.effective_start_date and pen.effective_end_date;
349 --
350 l_elctbl_chc c_elctbl_chc%rowtype;
351 --
352 cursor c_ncuuomdets
353 (c_uom varchar2
354 )
355 is
356 select ncu.currency_code
357 from hr_ncu_currencies ncu
358 where ncu.currency_code = c_uom;
359 --
360 l_ncuuomdets c_ncuuomdets%rowtype;
361 --
362 cursor c_tmpprvefc
363 (c_efc_action_id number
364 ,c_pk1 number
365 ,c_total_workers number
366 ,c_worker_id number
367 )
368 is
369 select prvefc.prtt_rt_val_id
370 from ben_prtt_rt_val_efc prvefc
371 where prvefc.efc_action_id = c_efc_action_id
372 and prvefc.prtt_rt_val_id > c_pk1
373 and mod(prvefc.prtt_rt_val_id, c_total_workers) = c_worker_id;
374 --
375 l_tmpprvefc c_tmpprvefc%rowtype;
376 --
377 --
378 procedure check_adjusted_values
379 (p_prv_mlt_cd in varchar2
380 ,p_ecr_mlt_cd in varchar2
381 ,p_old_prv_rt_val in number
382 ,p_new_prv_rt_val in number
383 ,p_old_prv_ann_rt_val in number
384 ,p_new_prv_ann_rt_val in number
385 --
386 ,p_prv_id in number
387 ,p_prvabr_id in number
388 ,p_ecrabr_id in number
389 ,p_ecrepe_id in number
390 ,p_ecrenb_id in number
391 ,p_eff_date in date
392 ,p_efc_action_id in number
393 --
394 ,p_adjfailed out nocopy boolean
395 ,p_faterr_code out nocopy varchar2
396 ,p_faterr_type out nocopy varchar2
397 ,p_val_type out nocopy varchar2
398 ,p_old_val1 out nocopy number
399 ,p_new_val1 out nocopy number
400 ,p_prv_uom out nocopy varchar2
401 )
402 is
403 --
404 l_currepe_row ben_determine_rates.g_curr_epe_rec;
405 l_currpil_row ben_efc_adjustments.g_pil_rowtype;
406 --
407 l_vpfdets ben_efc_adjustments.gc_vpfdets%rowtype;
408 --
409 l_adjfailed boolean;
410 l_old_val1 number;
411 l_new_val1 number;
412 l_faterr_code varchar2(100);
413 l_faterr_type varchar2(100);
414 l_par_pgm_id number;
415 l_par_pl_id number;
416 l_uom varchar2(100);
417 l_val_type varchar2(100);
418 l_preconv_val number;
419 l_postconv_val number;
420 l_vpf_id number;
421 --
422 cursor c_preconvdets
423 (c_efc_action_id number
424 ,c_prv_id number
425 )
426 is
427 select efc.rt_val,
428 efc.ann_rt_val,
429 efc.cmcd_rt_val,
430 efc.pgm_uom,
431 efc.nip_pl_uom
432 from ben_prtt_rt_val_efc efc
433 where efc.efc_action_id = c_efc_action_id
434 and efc.prtt_rt_val_id = c_prv_id;
435 --
436 l_preconvdets c_preconvdets%rowtype;
437 --
438 cursor c_aprdets
439 (c_apr_id number
440 ,c_eff_date date
441 )
442 is
443 select apr.uom
444 from ben_actl_prem_f apr
445 where apr.actl_prem_id = c_apr_id
446 and c_eff_date
447 between apr.effective_start_date and apr.effective_end_date;
448 --
449 l_aprdets c_aprdets%rowtype;
450 --
451 cursor c_vpfabrdets
452 (c_vpf_id number
453 ,c_eff_date date
454 )
455 is
456 SELECT abr.pgm_id,
457 abr.ptip_id,
458 abr.pl_id,
459 abr.plip_id,
460 abr.oipl_id,
461 abr.oiplip_id,
462 abr.nnmntry_uom
463 FROM ben_vrbl_rt_prfl_f vpf
464 , ben_acty_vrbl_rt_f avr
465 , ben_acty_base_rt_f abr
466 WHERE avr.vrbl_rt_prfl_id = c_vpf_id
467 AND vpf.VRBL_USG_CD = 'RT'
468 AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
469 AND avr.acty_base_rt_id = abr.acty_base_rt_id
470 AND c_eff_date
471 BETWEEN avr.effective_start_date AND avr.effective_end_date
472 AND c_eff_date
473 BETWEEN abr.effective_start_date AND abr.effective_end_date
474 AND avr.acty_base_rt_id =
475 (select min(avr1.acty_base_rt_id)
476 from ben_acty_vrbl_rt_f avr1,
477 ben_acty_base_rt_f abr1
478 where avr1.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
479 AND vpf.effective_start_date
480 BETWEEN avr1.effective_start_date AND avr1.effective_end_date
481 AND avr1.acty_base_rt_id = abr1.acty_base_rt_id
482 AND vpf.effective_start_date
483 BETWEEN abr1.effective_start_date AND abr1.effective_end_date
484 and abr1.NNMNTRY_UOM is null
485 and avr1.ordr_num =
486 (select min(avr2.ordr_num)
487 from ben_acty_vrbl_rt_f avr2,
488 ben_acty_base_rt_f abr2
489 where avr2.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
490 AND vpf.effective_start_date
491 BETWEEN avr2.effective_start_date AND avr2.effective_end_date
492 AND avr2.acty_base_rt_id = abr1.acty_base_rt_id
493 AND vpf.effective_start_date
494 BETWEEN abr2.effective_start_date AND abr2.effective_end_date
495 and abr2.NNMNTRY_UOM is null
496 )
497 )
498 ORDER BY avr.ORDR_NUM;
499
500 l_vpfabrdets c_vpfabrdets%rowtype;
501
502 begin
503 --
504 l_adjfailed := FALSE;
505 l_old_val1 := null;
506 l_new_val1 := null;
507 l_val_type := null;
508 --
509 if nvl(p_old_prv_rt_val,999) <> nvl(p_new_prv_rt_val,999)
510 then
511 --
512 l_adjfailed := TRUE;
513 l_val_type := 'PRV_RTVAL';
514 l_old_val1 := p_old_prv_rt_val;
515 l_new_val1 := p_new_prv_rt_val;
516 --
517 end if;
518 --
519 if nvl(p_old_prv_ann_rt_val,999) <> nvl(p_new_prv_ann_rt_val,999)
520 and not l_adjfailed
521 then
522 --
523 l_adjfailed := TRUE;
524 l_val_type := 'PRV_ANNRTVAL';
525 l_old_val1 := p_old_prv_ann_rt_val;
526 l_new_val1 := p_new_prv_ann_rt_val;
527 --
528 end if;
529 --
530 -- Success and failure exclusions
531 --
532 if l_faterr_code is null then
533 --
534 open c_abrdets
535 (c_abr_id => p_prvabr_id
536 ,c_eff_date => p_eff_date
537 );
538 fetch c_abrdets into l_abrdets;
539 if c_abrdets%notfound
540 then
541 --
542 l_adjfailed := TRUE;
543 l_faterr_code := 'NODTABR';
544 l_faterr_type := 'DELETEDINFO';
545 --
546 end if;
547 close c_abrdets;
548 --
549 if l_faterr_code is null
550 and l_abrdets.nnmntry_uom is not null
551 then
552 --
553 l_adjfailed := TRUE;
554 l_faterr_code := 'ABRNONMONUOM';
555 l_faterr_type := 'VALIDEXCLUSION';
556 --
557 -- Check for enter value at enrolment
558 --
559 elsif l_abrdets.entr_val_at_enrt_flag = 'Y'
560 and l_faterr_code is null
561 then
562 --
563 l_adjfailed := TRUE;
564 l_faterr_code := 'ABREVAEFLGY';
565 l_faterr_type := 'VALIDEXCLUSION';
566 --
567 end if;
568 --
569 if l_abrdets.actl_prem_id is not null
570 and p_prv_mlt_cd = 'AP'
571 and l_faterr_code is null
572 then
573 --
574 open c_aprdets
575 (c_apr_id => l_abrdets.actl_prem_id
576 ,c_eff_date => p_eff_date
577 );
578 fetch c_aprdets into l_aprdets;
579 if c_aprdets%notfound then
580 --
581 l_adjfailed := TRUE;
582 l_faterr_code := 'NODTAPR';
583 l_faterr_type := 'DELETEDINFO';
584 --
585 end if;
586 close c_aprdets;
587 --
588 if l_aprdets.uom = 'EUR'
589 and l_faterr_code is null
590 then
591 --
592 l_adjfailed := TRUE;
593 l_faterr_code := 'ABRAPREUROUOM';
594 l_faterr_type := 'VALIDEXCLUSION';
595 --
596 end if;
597 --
598 end if;
599 --
600 end if;
601 --
602 if l_faterr_code is null then
603 --
604 l_uom := null;
605 --
606 ben_efc_functions.CompObject_GetParUom
607 (p_pgm_id => l_abrdets.pgm_id
608 ,p_ptip_id => l_abrdets.ptip_id
609 ,p_pl_id => l_abrdets.pl_id
610 ,p_plip_id => l_abrdets.plip_id
611 ,p_oipl_id => l_abrdets.oipl_id
612 ,p_oiplip_id => l_abrdets.oiplip_id
613 ,p_eff_date => p_eff_date
614 --
615 ,p_paruom => l_uom
616 ,p_faterr_code => l_faterr_code
617 ,p_faterr_type => l_faterr_type
618 );
619 --
620 if l_faterr_code is not null then
621 --
622 l_adjfailed := TRUE;
623 --
624 end if;
625 --
626 end if;
627 --
628 if p_prv_mlt_cd = 'FLFX'
629 and l_faterr_code is null
630 then
631 --
632 l_adjfailed := TRUE;
633 l_faterr_code := 'PRVFLFX';
634 l_faterr_type := 'VALIDEXCLUSION';
635 --
636 elsif nvl(p_ecr_mlt_cd,'ZZZ') = 'FLFX'
637 and l_faterr_code is null
638 then
639 --
640 l_adjfailed := TRUE;
641 l_faterr_code := 'ECRMCFLFX';
642 l_faterr_type := 'VALIDEXCLUSION';
643 --
644 elsif l_uom = 'POINTS'
645 and l_faterr_code is null
646 then
647 --
648 l_adjfailed := TRUE;
649 l_faterr_code := 'PGMPOINTSUOM';
650 l_faterr_type := 'VALIDEXCLUSION';
651 --
652 elsif nvl(p_ecr_mlt_cd,'ZZZ') = 'NSVU'
653 and l_faterr_code is null
654 and (p_ecrepe_id is not null or p_ecrenb_id is not null)
655 then
656 --
657 -- Detect EPE or ENB Info
658 --
659 ben_efc_adjustments.DetectEPEENBInfo
660 (p_elig_per_elctbl_chc_id => p_ecrepe_id
661 ,p_enrt_bnft_id => p_ecrenb_id
662 --
663 ,p_detect_mode => 'EPEINFO'
664 --
665 ,p_currepe_row => l_currepe_row
666 ,p_currpil_row => l_currpil_row
667 ,p_faterr_code => l_faterr_code
668 ,p_faterr_type => l_faterr_type
669 );
670 --
671 -- Validate vapro
672 --
673 if l_faterr_code is null then
674 --
675 ben_efc_adjustments.DetectVAPROInfo
676 (p_currepe_row => l_currepe_row
677 --
678 ,p_lf_evt_ocrd_dt => l_currepe_row.lf_evt_ocrd_dt
679 ,p_last_update_date => null
680 --
681 ,p_acty_base_rt_id => p_prvabr_id
682 --
683 ,p_vpfdets => l_vpfdets
684 ,p_vpf_id => l_vpf_id
685 ,p_faterr_code => l_faterr_code
686 ,p_faterr_type => l_faterr_type
687 );
688 --
689 if l_faterr_code is not null
690 then
691 --
692 l_adjfailed := TRUE;
693 --
694 else
695 --
696 open c_vpfabrdets
697 (c_vpf_id => l_vpf_id
698 ,c_eff_date => p_eff_date
699 );
700 fetch c_vpfabrdets into l_vpfabrdets;
701 if c_vpfabrdets%notfound then
702 --
703 l_adjfailed := TRUE;
704 l_faterr_code := 'NODTVPFABR';
705 l_faterr_type := 'DELETEDINFO';
706 --
707 else
708 --
709 ben_efc_functions.CompObject_GetParUom
710 (p_pgm_id => l_vpfabrdets.pgm_id
711 ,p_ptip_id => l_vpfabrdets.ptip_id
712 ,p_pl_id => l_vpfabrdets.pl_id
713 ,p_plip_id => l_vpfabrdets.plip_id
714 ,p_oipl_id => l_vpfabrdets.oipl_id
715 ,p_oiplip_id => l_vpfabrdets.oiplip_id
716 ,p_eff_date => p_eff_date
717 --
718 ,p_paruom => l_uom
719 ,p_faterr_code => l_faterr_code
720 ,p_faterr_type => l_faterr_type
721 );
722 --
723 if l_vpfabrdets.nnmntry_uom is not null
724 and l_faterr_code is null
725 then
726 --
727 l_adjfailed := TRUE;
728 l_faterr_code := 'VPFABRNONMONUOM';
729 l_faterr_type := 'VALIDEXCLUSION';
730 --
731 elsif l_uom = 'POINTS'
732 and l_faterr_code is null
733 then
734 --
735 l_adjfailed := TRUE;
736 l_faterr_code := 'VPFPOINTSUOM';
737 l_faterr_type := 'VALIDEXCLUSION';
738 --
739 end if;
740 --
741 end if;
742 close c_vpfabrdets;
743 --
744 end if;
745 --
746 end if;
747 --
748 end if;
749 /*
750 --
751 -- Check conversion info
752 --
753 if l_faterr_code is null
754 and p_efc_action_id is not null
755 then
756 --
757 -- Check for non NCU UOMs
758 --
759 if l_faterr_code is null
760 and l_uom is not null
761 then
762 --
763 open c_ncuuomdets
764 (c_uom => l_uom
765 );
766 fetch c_ncuuomdets into l_ncuuomdets;
767 if c_ncuuomdets%notfound then
768 --
769 l_adjfailed := TRUE;
770 l_faterr_code := 'NOTNCUUOM';
771 l_faterr_type := 'VALIDEXCLUSION';
772 --
773 end if;
774 close c_ncuuomdets;
775 --
776 end if;
777 --
778 -- get pre conversion details
779 --
780 open c_preconvdets
781 (c_efc_action_id => p_efc_action_id
782 ,c_prv_id => p_prv_id
783 );
784 fetch c_preconvdets into l_preconvdets;
785 if c_preconvdets%notfound then
786 --
787 l_faterr_code := 'NOEFCACTEEV';
788 l_faterr_type := 'CORRUPTDATA';
789 --
790 end if;
791 close c_preconvdets;
792 --
793 if l_faterr_code is null
794 and l_adjfailed
795 then
796 --
797 if l_val_type = 'PRV_RTVAL' then
798 --
799 l_preconv_val := l_preconvdets.rt_val;
800 --
801 elsif l_val_type = 'PRV_ANNRTVAL' then
802 --
803 l_preconv_val := l_preconvdets.ann_rt_val;
804 --
805 end if;
806 --
807 ben_efc_adjustments.DetectConvInfo
808 (p_ncucurr_code => l_uom
809 ,p_new_val => l_new_val1
810 ,p_preconv_val => l_preconv_val
811 --
812 ,p_faterr_code => l_faterr_code
813 ,p_faterr_type => l_faterr_type
814 ,p_postconv_val => l_postconv_val
815 );
816 --
817 end if;
818 --
819 end if;
820 */
821 --
822 -- Failure exclusions
823 --
824 -- Check for null values
825 --
826 if l_new_val1 is null
827 and l_adjfailed
828 and l_faterr_code is null
829 then
830 --
831 l_faterr_code := 'NULLADJPRVVAL';
832 l_faterr_type := 'ADJUSTBUG';
833 --
834 end if;
835 --
836 if not l_adjfailed
837 and l_faterr_code is null
838 then
839 --
840 l_val_type := 'PRV_RTVAL';
841 l_old_val1 := p_old_prv_rt_val;
842 l_new_val1 := p_new_prv_rt_val;
843 --
844 end if;
845 --
846 p_adjfailed := l_adjfailed;
847 p_old_val1 := l_old_val1;
848 p_new_val1 := l_new_val1;
849 p_faterr_code := l_faterr_code;
850 p_faterr_type := l_faterr_type;
851 p_val_type := l_val_type;
852 p_prv_uom := l_uom;
853 --
854 end;
855 --
856 procedure update_prv
857 (p_prtt_rt_val_id in number
858 ,p_rt_val in number
859 ,p_ann_rt_val in number
860 ,p_chunk in number
861 ,p_efc_worker_id in number
862 ,p_chunkrow_count in out nocopy number
863 )
864 is
865
866 begin
867 --
868 update ben_prtt_rt_val prv
869 set prv.rt_val = p_rt_val,
870 prv.ann_rt_val = p_ann_rt_val
871 where prv.prtt_rt_val_id = p_prtt_rt_val_id;
872 --
873 -- Check for end of chunk and commit if necessary
874 --
875 ben_efc_functions.maintain_chunks
876 (p_row_count => p_chunkrow_count
877 ,p_pk1 => p_prtt_rt_val_id
878 ,p_chunk_size => p_chunk
879 ,p_efc_worker_id => p_efc_worker_id
880 );
881 --
882 end;
883 --
884 begin
885 --
886 l_efc_batch := FALSE;
887 --
888 l_row_count := 0;
889 l_calfail_count := 0;
890 l_calsucc_count := 0;
891 l_dupconv_count := 0;
892 l_conv_count := 0;
893 l_actconv_count := 0;
894 l_unconv_count := 0;
895 l_faterrs_count := 0;
896 l_rcoerr_count := 0;
897 --
898 l_pil_count := 0;
899 --
900 l_chunkrow_count := 0;
901 l_allpilprv_count := 0;
902 --
903 ben_efc_adjustments.g_prv_success_adj_val_set.delete;
904 ben_efc_adjustments.g_prv_failed_adj_val_set.delete;
905 ben_efc_adjustments.g_prv_rcoerr_val_set.delete;
906 ben_efc_adjustments.g_prv_fatal_error_val_set.delete;
907 --
908 -- Check if EFC process parameters are set
909 --
910 if p_action_id is not null
911 and p_pk1 is not null
912 and p_chunk is not null
913 and p_efc_worker_id is not null
914 then
915 --
916 l_efc_batch := TRUE;
917 --
918 end if;
919 --
920 l_from_str := ' FROM ben_prtt_rt_val prv, '
921 ||' ben_per_in_ler pil, '
922 ||' per_all_people_f per ';
923 --
924 l_where_str := ' where prv.per_in_ler_id = pil.per_in_ler_id '
925 ||' and pil.person_id = per.person_id '
926 ||' and per.effective_start_date = '
927 ||' (select min(per1.effective_start_date) '
928 ||' from per_all_people_f per1 '
929 ||' where per.person_id = per1.person_id '
930 ||' ) '
931 ||' and pil.lf_evt_ocrd_dt '
932 ||' between per.effective_start_date and per.effective_end_date '
933 /* Exclude out nocopy voided and backed out nocopy life events */
934 ||' and pil.per_in_ler_stat_cd not in ('
935 ||''''||'VOIDD'||''''||','||''''||'BCKDT'||''''||') '
936 ||' and (prv.rt_val is not null '
937 ||' or prv.ann_rt_val is not null '
938 ||' or prv.cmcd_rt_val is not null) ';
939 --
940 -- Check if we are restricting by business group
941 --
942 if p_business_group_id is not null then
943 --
944 l_where_str := l_where_str||' and prv.business_group_id = '||p_business_group_id;
945 --
946 end if;
947 --
948 l_groupby_str := ' pil.person_id, '
949 ||' pil.per_in_ler_id, '
950 ||' pil.business_group_id, '
951 ||' pil.lf_evt_ocrd_dt, '
952 ||' pil.creation_date, '
953 ||' pil.last_update_date, '
954 ||' pil.object_version_number ';
955 --
956 -- Build in batch specific restrictions
957 --
958 if l_efc_batch then
959 --
960 l_from_str := l_from_str||', ben_prtt_rt_val_efc efc ';
961 l_where_str := l_where_str||' and efc.prtt_rt_val_id = prv.prtt_rt_val_id '
962 ||' and efc.efc_action_id = :action_id '
963 ||' and prv.prtt_rt_val_id > :pk1 '
964 ||' and mod(prv.prtt_rt_val_id, :total_workers) = :worker_id ';
965 --
966 elsif p_valworker_id is not null
967 and p_valtotal_workers is not null
968 then
969 --
970 l_where_str := l_where_str||' and mod(pil.per_in_ler_id, :valtotal_workers) = :valworker_id ';
971 --
972 end if;
973 --
974 l_sql_str := ' select '||l_groupby_str
975 ||l_from_str
976 ||l_where_str
977 ||' group by '||l_groupby_str
978 ||' order by pil.per_in_ler_id ';
979 --
980 if l_efc_batch then
981 --
982 hr_efc_info.insert_line('-- ');
983 hr_efc_info.insert_line('-- Adjusting participant rate values ');
984 hr_efc_info.insert_line('-- ');
985 --
986 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
987 --
988 elsif p_valworker_id is not null
989 and p_valtotal_workers is not null
990 then
991 --
992 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
993 --
994 else
995 --
996 open c_efc_rows FOR l_sql_str;
997 --
998 end if;
999 --
1000 loop
1001 FETCH c_efc_rows INTO l_efc_row;
1002 EXIT WHEN c_efc_rows%NOTFOUND;
1003 --
1004 l_faterr_code := null;
1005 l_faterr_type := null;
1006 --
1007 l_effective_date := l_efc_row.lf_evt_ocrd_dt;
1008 --
1009 -- Set up benefits environment
1010 --
1011 ben_env_object.init
1012 (p_business_group_id => l_efc_row.business_group_id
1013 ,p_effective_date => l_effective_date
1014 ,p_thread_id => 1
1015 ,p_chunk_size => 10
1016 ,p_threads => 1
1017 ,p_max_errors => 100
1018 ,p_benefit_action_id => 99999
1019 ,p_audit_log_flag => 'N'
1020 );
1021 --
1022 -- Check if the payroll id is set for the employee or benefit assignment
1023 --
1024 ben_global_enrt.get_asg -- assignment
1025 (p_person_id => l_efc_row.person_id
1026 ,p_effective_date => l_effective_date
1027 ,p_global_asg_rec => l_global_asg_rec
1028 );
1029 --
1030 if l_global_asg_rec.payroll_id is null
1031 and l_faterr_code is null
1032 then
1033 --
1034 l_faterr_code := 'NOASGPAY';
1035 l_faterr_type := 'MISSINGSETUP';
1036 --
1037 end if;
1038 --
1039 if l_faterr_code is null then
1040 --
1041 begin
1042 --
1043 -- Check for a business group change and refresh vapro caches
1044 --
1045 if l_efc_row.business_group_id <> nvl(l_prev_bgp_id,-9999)
1046 then
1047 --
1048 ben_rt_prfl_cache.clear_down_cache;
1049 l_prev_bgp_id := l_efc_row.business_group_id;
1050 --
1051 end if;
1052 --
1053 l_rco_name := 'BENRATEN';
1054 --
1055 ben_det_enrt_rates.p_det_enrt_rates
1056 (p_calculate_only_mode => TRUE
1057 ,p_person_id => l_efc_row.person_id
1058 ,p_per_in_ler_id => l_efc_row.per_in_ler_id
1059 ,p_enrt_mthd_cd => null
1060 ,p_business_group_id => l_efc_row.business_group_id
1061 ,p_effective_date => l_effective_date
1062 ,p_validate => FALSE
1063 --
1064 ,p_prv_rtval_set => l_prv_rtval_set
1065 );
1066 --
1067 if l_prv_rtval_set.count > 0 then
1068 --
1069 for prvele_num in l_prv_rtval_set.first .. l_prv_rtval_set.last
1070 loop
1071 --
1072 l_faterr_code := null;
1073 l_faterr_type := null;
1074 l_adjfailed := FALSE;
1075 l_prv_uom := null;
1076 --
1077 open c_prvdets
1078 (c_prv_id => l_prv_rtval_set(prvele_num).prtt_rt_val_id
1079 );
1080 fetch c_prvdets into l_prvdets;
1081 if c_prvdets%notfound then
1082 --
1083 l_faterr_code := 'CORRPRV';
1084 --
1085 end if;
1086 close c_prvdets;
1087 --
1088 if l_faterr_code is null then
1089 --
1090 check_adjusted_values
1091 (p_prv_mlt_cd => l_prvdets.MLT_CD
1092 ,p_ecr_mlt_cd => l_prv_rtval_set(prvele_num).ecr_rt_mlt_cd
1093 ,p_old_prv_rt_val => l_prvdets.rt_val
1094 ,p_new_prv_rt_val => l_prv_rtval_set(prvele_num).rt_val
1095 ,p_old_prv_ann_rt_val => l_prvdets.ann_rt_val
1096 ,p_new_prv_ann_rt_val => l_prv_rtval_set(prvele_num).ann_rt_val
1097 --
1098 ,p_prv_id => l_prv_rtval_set(prvele_num).prtt_rt_val_id
1099 ,p_prvabr_id => l_prvdets.acty_base_rt_id
1100 ,p_ecrabr_id => null
1101 ,p_ecrepe_id => null
1102 ,p_ecrenb_id => null
1103 ,p_eff_date => l_effective_date
1104 ,p_efc_action_id => p_action_id
1105 --
1106 ,p_adjfailed => l_adjfailed
1107 ,p_faterr_code => l_faterr_code
1108 ,p_faterr_type => l_faterr_type
1109 ,p_val_type => l_val_type
1110 ,p_old_val1 => l_old_val1
1111 ,p_new_val1 => l_new_val1
1112 ,p_prv_uom => l_prv_uom
1113 );
1114 --
1115 end if;
1116 --
1117 if l_adjfailed
1118 and l_faterr_code is null
1119 then
1120 --
1121 if l_faterr_code is null
1122 then
1123 --
1124 -- Check rounding
1125 --
1126 ben_efc_adjustments.DetectRoundInfo
1127 (p_rndg_cd => null
1128 ,p_rndg_rl => null
1129 ,p_old_val => l_old_val1
1130 ,p_new_val => l_new_val1
1131 ,p_eff_date => l_effective_date
1132 --
1133 ,p_faterr_code => l_faterr_code
1134 ,p_faterr_type => l_faterr_type
1135 );
1136 --
1137 end if;
1138 --
1139 if l_faterr_code is null
1140 then
1141 --
1142 -- Check if the coverage has been modified
1143 --
1144 ben_efc_adjustments.DetectWhoInfo
1145 (p_creation_date => l_prvdets.creation_date
1146 ,p_last_update_date => l_prvdets.last_update_date
1147 ,p_object_version_number => l_prvdets.object_version_number
1148 --
1149 ,p_who_counts => l_who_counts
1150 ,p_faterr_code => l_faterr_code
1151 ,p_faterr_type => l_faterr_type
1152 );
1153 --
1154 end if;
1155 --
1156 if l_faterr_code is null then
1157 --
1158 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).id := l_prv_rtval_set(prvele_num).prtt_rt_val_id;
1159 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).id1 := l_prvdets.prtt_enrt_rslt_id;
1160 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).id2 := l_efc_row.per_in_ler_id;
1161 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).old_val1 := l_old_val1;
1162 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).new_val1 := l_new_val1;
1163 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).val_type := l_val_type;
1164 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).credt := l_prvdets.creation_date;
1165 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).lud := l_prvdets.last_update_date;
1166 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).code1 := l_prvdets.mlt_cd;
1167 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).code2 := l_prv_rtval_set(prvele_num).ecr_rt_mlt_cd;
1168 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).code3 := l_prv_uom;
1169 --
1170 l_calfail_count := l_calfail_count+1;
1171 --
1172 end if;
1173 --
1174 end if;
1175 --
1176 -- Check for fatal errors
1177 --
1178 if l_faterr_code is not null
1179 then
1180 --
1181 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id := l_prv_rtval_set(prvele_num).prtt_rt_val_id;
1182 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
1183 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
1184 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
1185 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
1186 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).lud := l_prvdets.last_update_date;
1187 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).credt := l_prvdets.creation_date;
1188 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id1 := l_prvdets.prtt_enrt_rslt_id;
1189 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id2 := l_efc_row.per_in_ler_id;
1190 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code1 := l_prvdets.mlt_cd;
1191 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code2 := l_prv_rtval_set(prvele_num).ecr_rt_mlt_cd;
1192 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code3 := l_prv_uom;
1193 --
1194 l_faterrs_count := l_faterrs_count+1;
1195 --
1196 elsif l_faterr_code is null
1197 and not l_adjfailed
1198 then
1199 --
1200 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).id := l_prv_rtval_set(prvele_num).prtt_rt_val_id;
1201 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).id1 := l_prvdets.prtt_enrt_rslt_id;
1202 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).id2 := l_efc_row.per_in_ler_id;
1203 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
1204 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
1205 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).val_type := l_val_type;
1206 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).credt := l_prvdets.creation_date;
1207 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).lud := l_prvdets.last_update_date;
1208 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code1 := l_prvdets.mlt_cd;
1209 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code2 := l_prv_rtval_set(prvele_num).ecr_rt_mlt_cd;
1210 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code3 := l_prv_uom;
1211 --
1212 l_calsucc_count := l_calsucc_count+1;
1213 --
1214 end if;
1215 --
1216 l_row_count := l_row_count+1;
1217 --
1218 if l_efc_batch
1219 and (l_faterr_code is null
1220 or nvl(l_faterr_type,'ZZZZ') = 'CONVEXCLUSION')
1221 then
1222 --
1223 update_prv
1224 (p_prtt_rt_val_id => l_prv_rtval_set(prvele_num).prtt_rt_val_id
1225 ,p_rt_val => l_prv_rtval_set(prvele_num).rt_val
1226 ,p_ann_rt_val => l_prv_rtval_set(prvele_num).ann_rt_val
1227 ,p_chunk => p_chunk
1228 ,p_efc_worker_id => p_efc_worker_id
1229 ,p_chunkrow_count => l_chunkrow_count
1230 );
1231 --
1232 end if;
1233 --
1234 end loop;
1235 --
1236 else
1237 --
1238 -- Check for corrupt PRVs for a PIL
1239 --
1240 l_pilprv_count := 0;
1241 --
1242 for pilprv_row in c_pilprv
1243 (c_pil_id => l_efc_row.per_in_ler_id
1244 )
1245 loop
1246 --
1247 l_faterr_code := null;
1248 l_faterr_type := null;
1249 l_adjfailed := TRUE;
1250 l_prv_uom := null;
1251 --
1252 if l_faterr_code is null then
1253 --
1254 open c_abrdets
1255 (c_abr_id => pilprv_row.acty_base_rt_id
1256 ,c_eff_date => l_effective_date
1257 );
1258 fetch c_abrdets into l_abrdets;
1259 if c_abrdets%notfound then
1260 --
1261 l_faterr_code := 'NODTPRVABR';
1262 l_faterr_type := 'DELETEDINFO';
1263 --
1264 end if;
1265 close c_abrdets;
1266 --
1267 -- Check for a non monetary UOM
1268 --
1269 if l_abrdets.NNMNTRY_UOM is not null
1270 and l_faterr_code is null
1271 then
1272 --
1273 l_faterr_code := 'ABRNONMONUOM';
1274 l_faterr_type := 'VALIDEXCLUSION';
1275 --
1276 -- Check for enter value at enrolment
1277 --
1278 elsif l_abrdets.entr_val_at_enrt_flag = 'Y'
1279 and l_faterr_code is null
1280 then
1281 --
1282 l_faterr_code := 'ABREVAEFLGY';
1283 l_faterr_type := 'VALIDEXCLUSION';
1284 --
1285 end if;
1286 --
1287 end if;
1288 --
1289 -- Check PEN details
1290 --
1291 if l_faterr_code is null then
1292 --
1293 l_dtpen_count := 0;
1294 --
1295 for row in c_dtpendets
1296 (c_pen_id => pilprv_row.prtt_enrt_rslt_id
1297 )
1298 loop
1299 --
1300 if row.prtt_enrt_rslt_stat_cd in ('BCKDT','VOIDD') then
1301 --
1302 l_faterr_code := 'BACKVOIDPEN';
1303 l_faterr_type := 'VALIDEXCLUSION';
1304 --
1305 end if;
1306 --
1307 l_dtpen_count := l_dtpen_count+1;
1308 --
1309 end loop;
1310 --
1311 if l_dtpen_count = 0
1312 and l_faterr_code is null
1313 then
1314 --
1315 l_faterr_code := 'NOPEN';
1316 l_faterr_type := 'DELETEDINFO';
1317 --
1318 end if;
1319 --
1320 end if;
1321 --
1322 -- Check for a set ESD other than EOT
1323 --
1324 if l_faterr_code is null
1325 then
1326 --
1327 open c_elctbl_chc
1328 (c_pen_id => pilprv_row.prtt_enrt_rslt_id
1329 ,c_pil_id => l_efc_row.per_in_ler_id
1330 ,c_eff_date => l_effective_date
1331 );
1332 fetch c_elctbl_chc into l_elctbl_chc;
1333 if c_elctbl_chc%notfound then
1334 --
1335 open c_pendets
1336 (c_pen_id => pilprv_row.prtt_enrt_rslt_id
1337 ,c_eff_date => l_effective_date
1338 );
1339 fetch c_pendets into l_pendets;
1340 if c_pendets%notfound then
1341 --
1342 l_faterr_code := 'NOPILLEODPRVPEN';
1343 l_faterr_type := 'UNSUPPORTTRANS';
1344 --
1345 else
1346 --
1347 l_faterr_code := 'NOPENEPE';
1348 l_faterr_type := 'POTENTIALCODEBUG';
1349 --
1350 end if;
1351 close c_pendets;
1352 --
1353 end if;
1354 close c_elctbl_chc;
1355 --
1356 if l_elctbl_chc.elig_per_elctbl_chc_id is not null
1357 and l_faterr_code is null
1358 then
1359 --
1360 l_ecr_count := 0;
1361 l_ecrnomatchprv_count := 0;
1362 --
1363 for ecr_row in c_enrt_rt
1364 (c_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id
1365 ,c_prtt_enrt_rslt_id => pilprv_row.prtt_enrt_rslt_id
1366 )
1367 loop
1368 --
1369 if l_ecr_count = 0
1370 and pilprv_row.prtt_rt_val_id = ecr_row.prtt_rt_val_id
1371 then
1372 --
1373 l_enrt_rt := ecr_row;
1374 l_ecr_count := 1;
1375 --
1376 elsif pilprv_row.prtt_rt_val_id = ecr_row.prtt_rt_val_id
1377 then
1378 --
1379 l_ecr_count := l_ecr_count+1;
1380 --
1381 else
1382 --
1383 l_ecrnomatchprv_count := l_ecrnomatchprv_count+1;
1384 --
1385 end if;
1386 --
1387 end loop;
1388 --
1389 if l_ecr_count = 1
1390 then
1391 --
1392 begin
1393 --
1394 l_rco_name := 'BENELINF_ERI';
1395 l_prv_id := pilprv_row.prtt_rt_val_id;
1396 --
1397 ben_election_information.election_rate_information
1398 (p_calculate_only_mode => TRUE
1399 ,p_enrt_mthd_cd => l_pendets.enrt_mthd_cd
1400 ,p_effective_date => l_effective_date
1401 ,p_prtt_enrt_rslt_id => pilprv_row.prtt_enrt_rslt_id
1402 ,p_per_in_ler_id => l_efc_row.per_in_ler_id
1403 ,p_person_id => l_efc_row.person_id
1404 ,p_pgm_id => l_elctbl_chc.pgm_id
1405 ,p_pl_id => l_elctbl_chc.pl_id
1406 ,p_oipl_id => l_elctbl_chc.oipl_id
1407 ,p_enrt_rt_id => l_enrt_rt.enrt_rt_id
1408 ,p_prtt_rt_val_id => l_prv_id
1409 ,p_rt_val => l_enrt_rt.val
1410 ,p_ann_rt_val => l_enrt_rt.ann_val
1411 ,p_enrt_cvg_strt_dt => l_pendets.enrt_cvg_strt_dt
1412 ,p_acty_ref_perd_cd => l_elctbl_chc.acty_ref_perd_cd
1413 ,p_datetrack_mode => null
1414 ,p_business_group_id => pilprv_row.business_group_id
1415 --
1416 ,p_prv_rt_val => l_prv_rt_val
1417 ,p_prv_ann_rt_val => l_prv_ann_rt_val
1418 );
1419 --
1420 check_adjusted_values
1421 (p_prv_mlt_cd => pilprv_row.mlt_cd
1422 ,p_ecr_mlt_cd => l_enrt_rt.rt_mlt_cd
1423 ,p_old_prv_rt_val => pilprv_row.rt_val
1424 ,p_new_prv_rt_val => l_prv_rt_val
1425 ,p_old_prv_ann_rt_val => pilprv_row.ann_rt_val
1426 ,p_new_prv_ann_rt_val => l_prv_ann_rt_val
1427 --
1428 ,p_prv_id => pilprv_row.prtt_rt_val_id
1429 ,p_prvabr_id => pilprv_row.acty_base_rt_id
1430 ,p_ecrabr_id => l_enrt_rt.acty_base_rt_id
1431 ,p_ecrepe_id => l_enrt_rt.elig_per_elctbl_chc_id
1432 ,p_ecrenb_id => l_enrt_rt.enrt_bnft_id
1433 ,p_eff_date => l_effective_date
1434 ,p_efc_action_id => p_action_id
1435 --
1436 ,p_adjfailed => l_adjfailed
1437 ,p_faterr_code => l_faterr_code
1438 ,p_faterr_type => l_faterr_type
1439 ,p_val_type => l_val_type
1440 ,p_old_val1 => l_old_val1
1441 ,p_new_val1 => l_new_val1
1442 ,p_prv_uom => l_prv_uom
1443 );
1444 --
1445 exception
1446 when others then
1447 --
1448 ben_efc_adjustments.DetectAppError
1449 (p_sqlerrm => SQLERRM
1450 ,p_abr_rt_mlt_cd => l_abrdets.rt_mlt_cd
1451 ,p_abr_val => l_abrdets.val
1452 ,p_abr_entr_val_at_enrt_flag => l_abrdets.entr_val_at_enrt_flag
1453 ,p_abr_id => pilprv_row.acty_base_rt_id
1454 ,p_eff_date => l_effective_date
1455 ,p_penepe_id => l_elctbl_chc.elig_per_elctbl_chc_id
1456 --
1457 ,p_faterr_code => l_faterr_code
1458 ,p_faterr_type => l_faterr_type
1459 );
1460 --
1461 if l_faterr_code is null then
1462 --
1463 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.per_in_ler_id;
1464 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).rco_name := l_rco_name;
1465 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
1466 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
1467 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
1468 --
1469 l_rcoerr_count := l_rcoerr_count+1;
1470 --
1471 end if;
1472 --
1473 end;
1474 --
1475 elsif l_ecr_count > 1 then
1476 --
1477 l_faterr_code := 'MULTPENEPEECR';
1478 l_faterr_type := 'POTENTIALCODEBUG';
1479 --
1480 elsif l_ecr_count = 0
1481 and l_ecrnomatchprv_count > 0
1482 then
1483 --
1484 l_faterr_code := 'ECRMCFLFX';
1485 l_faterr_type := 'VALIDEXCLUSION';
1486 --
1487 else
1488 --
1489 l_faterr_code := 'NOPENEPEECR';
1490 l_faterr_type := 'POTENTIALCODEBUG';
1491 --
1492 end if;
1493 --
1494 end if;
1495 --
1496 end if;
1497 --
1498 if l_faterr_code is null
1499 and l_adjfailed
1500 then
1501 --
1502 -- Check rounding
1503 --
1504 ben_efc_adjustments.DetectRoundInfo
1505 (p_rndg_cd => null
1506 ,p_rndg_rl => null
1507 ,p_old_val => l_old_val1
1508 ,p_new_val => l_new_val1
1509 ,p_eff_date => l_effective_date
1510 --
1511 ,p_faterr_code => l_faterr_code
1512 ,p_faterr_type => l_faterr_type
1513 );
1514 --
1515 end if;
1516 --
1517 -- Check for a PRV mod
1518 --
1519 if pilprv_row.creation_date <> pilprv_row.last_update_date
1520 and l_faterr_code is null
1521 and l_adjfailed
1522 then
1523 --
1524 if pilprv_row.RT_END_DT <> hr_api.g_eot
1525 then
1526 --
1527 l_faterr_code := 'PRVENDDATED';
1528 l_faterr_type := 'UNSUPPORTTRANS';
1529 --
1530 else
1531 --
1532 l_faterr_code := 'PRVMODIFIED';
1533 l_faterr_type := 'UNSUPPORTTRANS';
1534 --
1535 end if;
1536 --
1537 -- Check for a set ECTD other than EOT
1538 --
1539 if l_pendets.ENRT_CVG_THRU_DT <> hr_api.g_eot
1540 and l_faterr_code is null
1541 and l_adjfailed
1542 then
1543 --
1544 l_faterr_code := 'PRVPENECTDSET';
1545 l_faterr_type := 'UNSUPPORTTRANS';
1546 --
1547 end if;
1548 --
1549 -- Check for a set ESD other than EOT
1550 --
1551 if l_pendets.effective_end_date <> hr_api.g_eot
1552 and l_faterr_code is null
1553 and l_adjfailed
1554 then
1555 --
1556 l_faterr_code := 'PRVPENEEDSET';
1557 l_faterr_type := 'UNSUPPORTTRANS';
1558 --
1559 end if;
1560 --
1561 -- Check for an overidden enrolment. Created from
1562 -- ben_lf_evt_clps_restore.reinstate_the_prev_enrt
1563 --
1564 if l_pendets.enrt_ovridn_flag = 'Y'
1565 and l_faterr_code is null
1566 and l_adjfailed
1567 then
1568 --
1569 l_faterr_code := 'OVERIDDENPEN';
1570 --
1571 end if;
1572 --
1573 end if;
1574 --
1575 if l_faterr_code is null
1576 and l_adjfailed
1577 then
1578 --
1579 -- Get the attached ECR ID for the PRV
1580 --
1581 open c_ecrdets
1582 (c_prv_id => pilprv_row.prtt_rt_val_id
1583 );
1584 fetch c_ecrdets into l_ecrdets;
1585 if c_ecrdets%notfound then
1586 --
1587 l_faterr_code := 'NOPRVECR';
1588 --
1589 end if;
1590 close c_ecrdets;
1591 --
1592 end if;
1593 --
1594 -- Check for a correction on the ABR
1595 --
1596 if l_abrdets.rt_mlt_cd <> l_ecrdets.rt_mlt_cd
1597 and l_faterr_code is null
1598 and l_adjfailed
1599 then
1600 --
1601 l_faterr_code := 'NMABRECRMLTCDS';
1602 l_faterr_type := 'POTENTIALCODEBUG';
1603 --
1604 end if;
1605 --
1606 -- Check for a ECR attached to coverage or electable choice
1607 --
1608 if l_ecrdets.enrt_bnft_id is not null
1609 and l_faterr_code is null
1610 and l_adjfailed
1611 then
1612 --
1613 -- Get the PEN ID from the enrolment benefit
1614 --
1615 open c_enbdets
1616 (c_enb_id => l_ecrdets.enrt_bnft_id
1617 );
1618 fetch c_enbdets into l_enbdets;
1619 close c_enbdets;
1620 --
1621 if nvl(pilprv_row.prtt_enrt_rslt_id,999) <> nvl(l_enbdets.prtt_enrt_rslt_id,999)
1622 and l_faterr_code is null
1623 then
1624 --
1625 l_faterr_code := 'NMPRVENBPEN';
1626 --
1627 end if;
1628 --
1629 end if;
1630 --
1631 -- Check if the ASN on enrt flag is Y
1632 --
1633 if l_ecrdets.asn_on_enrt_flag = 'N'
1634 and l_faterr_code is null
1635 and l_adjfailed
1636 then
1637 --
1638 l_faterr_code := 'ECRAOEFLGN';
1639 l_faterr_type := 'VALIDEXCLUSION';
1640 --
1641 end if;
1642 --
1643 -- Check if the enter val at enrt flag is N
1644 --
1645 if l_ecrdets.entr_val_at_enrt_flag = 'Y'
1646 and l_faterr_code is null
1647 and l_adjfailed
1648 then
1649 --
1650 l_faterr_code := 'EPEECREVAENFLGY';
1651 l_faterr_type := 'VALIDEXCLUSION';
1652 --
1653 end if;
1654 --
1655 -- Check if the coverage has been modified
1656 --
1657 if l_faterr_code is null
1658 and l_adjfailed
1659 then
1660 --
1661 ben_efc_adjustments.DetectWhoInfo
1662 (p_creation_date => pilprv_row.creation_date
1663 ,p_last_update_date => pilprv_row.last_update_date
1664 ,p_object_version_number => pilprv_row.object_version_number
1665 --
1666 ,p_who_counts => l_who_counts
1667 ,p_faterr_code => l_faterr_code
1668 ,p_faterr_type => l_faterr_type
1669 );
1670 --
1671 end if;
1672 --
1673 -- Check for fatal errors
1674 --
1675 if l_faterr_code is not null
1676 then
1677 --
1678 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id := pilprv_row.prtt_rt_val_id;
1679 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
1680 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
1681 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
1682 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
1683 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).ovn := pilprv_row.object_version_number;
1684 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).lud := pilprv_row.last_update_date;
1685 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).credt := pilprv_row.creation_date;
1686 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id1 := pilprv_row.prtt_enrt_rslt_id;
1687 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).id2 := l_efc_row.per_in_ler_id;
1688 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code1 := pilprv_row.mlt_cd;
1689 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code2 := l_enrt_rt.rt_mlt_cd;
1690 ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).code3 := l_prv_uom;
1691 --
1692 l_faterrs_count := l_faterrs_count+1;
1693 --
1694 elsif not l_adjfailed
1695 and l_faterr_code is null
1696 then
1697 --
1698 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).id := pilprv_row.prtt_rt_val_id;
1699 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).id2 := l_efc_row.per_in_ler_id;
1700 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
1701 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
1702 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).credt := pilprv_row.creation_date;
1703 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).lud := pilprv_row.last_update_date;
1704 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code1 := pilprv_row.mlt_cd;
1705 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code2 := l_enrt_rt.rt_mlt_cd;
1706 ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).code3 := l_prv_uom;
1707 --
1708 l_calsucc_count := l_calsucc_count+1;
1709 --
1710 end if;
1711 --
1712 l_pilprv_count := l_pilprv_count+1;
1713 --
1714 if l_efc_batch
1715 and (l_faterr_code is null
1716 or nvl(l_faterr_type,'ZZZZ') = 'CONVEXCLUSION')
1717 then
1718 --
1719 update_prv
1720 (p_prtt_rt_val_id => pilprv_row.prtt_rt_val_id
1721 ,p_rt_val => l_prv_rt_val
1722 ,p_ann_rt_val => l_prv_ann_rt_val
1723 ,p_chunk => p_chunk
1724 ,p_efc_worker_id => p_efc_worker_id
1725 ,p_chunkrow_count => l_chunkrow_count
1726 );
1727 --
1728 end if;
1729 --
1730 end loop;
1731 --
1732 if l_pilprv_count = 0
1733 and l_faterr_code is null
1734 then
1735 --
1736 l_faterr_code := 'NOPILPRVS';
1737 l_faterr_type := 'DATACORRUPT';
1738 --
1739 end if;
1740 --
1741 -- Check if no PRVs exist for the PIL
1742 --
1743 if l_pilprv_count = 0
1744 and l_faterr_code is null
1745 then
1746 --
1747 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).id := l_efc_row.per_in_ler_id;
1748 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).id1 := null;
1749 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).old_val1 := null;
1750 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).new_val1 := null;
1751 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).val_type := 'NORECALPRV';
1752 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
1753 ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
1754 --
1755 l_calfail_count := l_calfail_count+1;
1756 --
1757 end if;
1758 --
1759 end if;
1760 --
1761 exception
1762 when others then
1763 --
1764 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.per_in_ler_id;
1765 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).rco_name := l_rco_name;
1766 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
1767 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
1768 ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
1769 --
1770 l_rcoerr_count := l_rcoerr_count+1;
1771 --
1772 end;
1773 --
1774 end if;
1775 --
1776 -- Count PRVs
1777 --
1778 l_pilprv_count := 0;
1779 --
1780 for pilprv_row in c_pilprv
1781 (c_pil_id => l_efc_row.per_in_ler_id
1782 )
1783 loop
1784 --
1785 l_pilprv_count := l_pilprv_count+1;
1786 --
1787 end loop;
1788 --
1789 l_allpilprv_count := l_allpilprv_count+l_pilprv_count;
1790 l_pil_count := l_pil_count+1;
1791 --
1792 end loop;
1793 CLOSE c_efc_rows;
1794 --
1795 --Bug 5049253
1796 -- Write exceptions down to the table
1797 /*
1798 --
1799 if l_efc_batch
1800 and p_valworker_id is null
1801 and p_valtotal_workers is null
1802 then
1803 --
1804 ben_efc_adjustments.insert_validation_exceptions
1805 (p_val_set => ben_efc_adjustments.g_prv_failed_adj_val_set
1806 ,p_efc_action_id => p_action_id
1807 ,p_ent_scode => 'PRV'
1808 ,p_exception_type => 'AF'
1809 );
1810 --
1811 ben_efc_adjustments.insert_validation_exceptions
1812 (p_val_set => ben_efc_adjustments.g_prv_fatal_error_val_set
1813 ,p_efc_action_id => p_action_id
1814 ,p_ent_scode => 'PRV'
1815 ,p_exception_type => null
1816 );
1817 --
1818 end if;
1819 */
1820 --Bug 5049253
1821 --
1822 ben_efc_functions.conv_check
1823 (p_table_name => 'ben_prtt_rt_val'
1824 ,p_efctable_name => 'ben_prtt_rt_val_efc'
1825 ,p_tabwhere_clause => ' (rt_val is not null '
1826 ||' or ann_rt_val is not null '
1827 ||' or cmcd_rt_val is not null) '
1828 ,p_bgp_id => p_business_group_id
1829 ,p_action_id => p_action_id
1830 --
1831 ,p_conv_count => l_conv_count
1832 ,p_unconv_count => l_unconv_count
1833 ,p_tabrow_count => l_tabrow_count
1834 );
1835 --
1836 -- Set counts
1837 --
1838 if p_action_id is null then
1839 --
1840 l_actconv_count := 0;
1841 --
1842 else
1843 --
1844 l_actconv_count := l_conv_count;
1845 --
1846 end if;
1847 --
1848 -- Set counts
1849 --
1850 p_adjustment_counts.efcrow_count := l_allpilprv_count;
1851 p_adjustment_counts.tabrow_count := l_tabrow_count;
1852 p_adjustment_counts.actconv_count := l_actconv_count;
1853 p_adjustment_counts.calfail_count := l_calfail_count;
1854 p_adjustment_counts.calsucc_count := l_calsucc_count;
1855 p_adjustment_counts.conv_count := l_conv_count;
1856 p_adjustment_counts.unconv_count := l_unconv_count;
1857 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
1858 --
1859 end prv_adjustments;
1860 --
1861 procedure eev_adjustments
1862 (p_validate in boolean default false
1863 ,p_worker_id in number default null
1864 ,p_action_id in number default null
1865 ,p_total_workers in number default null
1866 ,p_pk1 in number default null
1867 ,p_chunk in number default null
1868 ,p_efc_worker_id in number default null
1869 --
1870 ,p_valworker_id in number default null
1871 ,p_valtotal_workers in number default null
1872 --
1873 ,p_business_group_id in number default null
1874 --
1875 ,p_adjustment_counts out nocopy ben_efc_adjustments.g_adjustment_counts
1876 )
1877 is
1878 --
1879 TYPE cur_type IS REF CURSOR;
1880 --
1881 type g_efc_row is record
1882 (element_entry_value_id pay_element_entry_values_f.element_entry_value_id%type
1883 ,effective_start_date pay_element_entry_values_f.effective_start_date%type
1884 ,effective_end_date pay_element_entry_values_f.effective_end_date%type
1885 ,prv_creation_date ben_prtt_rt_val.creation_date%type
1886 ,prv_last_update_date ben_prtt_rt_val.last_update_date%type
1887 ,prv_last_update_login ben_prtt_rt_val.last_update_login%type
1888 ,prv_created_by ben_prtt_rt_val.created_by%type
1889 ,screen_entry_value pay_element_entry_values_f.screen_entry_value%type
1890 ,enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type
1891 ,business_group_id ben_prtt_rt_val.business_group_id%type
1892 ,prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type
1893 ,acty_ref_perd_cd ben_prtt_rt_val.acty_ref_perd_cd%type
1894 ,acty_base_rt_id ben_prtt_rt_val.acty_base_rt_id%type
1895 ,prtt_enrt_rslt_id ben_prtt_rt_val.prtt_enrt_rslt_id%type
1896 ,rt_strt_dt ben_prtt_rt_val.rt_strt_dt%type
1897 ,rt_val ben_prtt_rt_val.rt_val%type
1898 ,prv_ovn ben_prtt_rt_val.object_version_number%type
1899 ,input_value_id ben_acty_base_rt_f.input_value_id%type
1900 ,element_type_id ben_acty_base_rt_f.element_type_id%type
1901 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
1902 ,person_id ben_per_in_ler.person_id%type
1903 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
1904 ,ELEMENT_ENTRY_ID pay_element_entry_values_f.ELEMENT_ENTRY_ID%type
1905 );
1906 --
1907 c_efc_rows cur_type;
1908 --
1909 l_proc varchar2(1000) := 'eev_adjustments';
1910 --
1911 l_efc_row g_efc_row;
1912 --
1913 l_who_counts ben_efc_adjustments.g_who_counts;
1914 l_prvwho_counts ben_efc_adjustments.g_who_counts;
1915 l_olddata boolean;
1916 --
1917 l_row_count pls_integer;
1918 l_calfail_count pls_integer;
1919 l_calsucc_count pls_integer;
1920 l_conv_count pls_integer;
1921 l_unconv_count pls_integer;
1922 l_actconv_count pls_integer;
1923 l_dupconv_count pls_integer;
1924 l_rcoerr_count pls_integer;
1925 l_faterrs_count pls_integer;
1926 l_preadjexc_count pls_integer;
1927 --
1928 l_sql_str long;
1929 l_from_str long;
1930 l_where_str long;
1931 --
1932 l_efc_batch boolean;
1933 l_pk1 number;
1934 --
1935 l_assign_exists boolean;
1936 l_dummy_id number;
1937 l_count pls_integer;
1938 --
1939 l_tabrow_count pls_integer;
1940 l_chunkrow_count pls_integer;
1941 --
1942 l_eev_screen_entry_value number;
1943 l_dummy_number number;
1944 --
1945 l_effective_date date;
1946 l_faterr_code varchar2(100);
1947 l_faterr_type varchar2(100);
1948 --
1949 l_prev_pkid number;
1950 l_prev_esd date;
1951 --
1952 l_old_val1 number;
1953 l_new_val1 number;
1954 l_postconv_val number;
1955 --
1956 l_adjfailed boolean;
1957 --
1958 cursor c_multprveevs
1959 (c_eev_id in number
1960 )
1961 is
1962 select count(*)
1963 from ben_prtt_rt_val
1964 where element_entry_value_id = c_eev_id;
1965 --
1966 cursor c_abrdets
1967 (c_abr_id in number
1968 ,c_eff_date in date
1969 )
1970 is
1971 select abr.last_update_date,
1972 abr.ele_rqd_flag
1973 from ben_acty_base_rt_f abr
1974 where abr.acty_base_rt_id = c_abr_id
1975 and c_eff_date
1976 between abr.effective_start_date and abr.effective_end_date;
1977 --
1978 l_abrdets c_abrdets%rowtype;
1979 --
1980 cursor c_eledets
1981 (c_ele_id in number
1982 ,c_eff_date in date
1983 )
1984 is
1985 select ele.creation_date,
1986 ele.last_update_date,
1987 ele.object_version_number,
1988 ele.created_by,
1989 ele.last_updated_by,
1990 ele.last_update_login
1991 from pay_element_entries_f ele
1992 where ele.ELEMENT_ENTRY_ID = c_ele_id
1993 and c_eff_date
1994 between ele.effective_start_date and ele.effective_end_date;
1995 --
1996 l_eledets c_eledets%rowtype;
1997 l_tmpdets c_eledets%rowtype;
1998 --
1999 cursor c_eledtinsts
2000 (c_ele_id in number
2001 )
2002 is
2003 select ele.effective_start_date,
2004 ele.effective_end_date
2005 from pay_element_entries_f ele
2006 where ele.ELEMENT_ENTRY_ID = c_ele_id;
2007 --
2008 l_eledtinsts c_eledtinsts%rowtype;
2009 --
2010 cursor c_preconvdets
2011 (c_efc_action_id number
2012 ,c_eev_id number
2013 ,c_eev_esd date
2014 )
2015 is
2016 select efc.SCREEN_ENTRY_VALUE,
2017 efc.input_currency_code
2018 from pay_element_entry_values_f_efc efc
2019 where efc.efc_action_id = c_efc_action_id
2020 and efc.element_entry_value_id = c_eev_id
2021 and efc.effective_start_date = c_eev_esd;
2022 --
2023 l_preconvdets c_preconvdets%rowtype;
2024 --
2025 cursor c_valexcexist
2026 (c_prv_id number
2027 ,c_efc_action_id number
2028 ,c_ent_scode varchar2
2029 )
2030 is
2031 select exc.efc_action_id
2032 from ben_efc_exclusions exc
2033 where exc.efc_action_id = c_efc_action_id
2034 and exc.ent_scode = c_ent_scode
2035 and exc.pk_id = c_prv_id
2036 and exc.exclusion_type = 'VALIDEXCLUSION';
2037 --
2038 l_valexcexist c_valexcexist%rowtype;
2039 --
2040 cursor c_convvalexist
2041 (c_prv_id number
2042 ,c_efc_action_id number
2043 )
2044 is
2045 select efc.efc_action_id
2046 from ben_prtt_rt_val_efc efc
2047 where efc.efc_action_id = c_efc_action_id
2048 and efc.prtt_rt_val_id = c_prv_id;
2049 --
2050 l_convvalexist c_convvalexist%rowtype;
2051 --
2052 begin
2053 --
2054 l_efc_batch := FALSE;
2055 --
2056 l_row_count := 0;
2057 l_calfail_count := 0;
2058 l_calsucc_count := 0;
2059 l_dupconv_count := 0;
2060 l_conv_count := 0;
2061 l_actconv_count := 0;
2062 l_unconv_count := 0;
2063 l_rcoerr_count := 0;
2064 l_faterrs_count := 0;
2065 l_preadjexc_count := 0;
2066 l_chunkrow_count := 0;
2067 --
2068 ben_efc_adjustments.g_eev_failed_adj_val_set.delete;
2069 ben_efc_adjustments.g_eev_rcoerr_val_set.delete;
2070 ben_efc_adjustments.g_eev_fatal_error_val_set.delete;
2071 ben_efc_adjustments.g_eev_success_adj_val_set.delete;
2072 --
2073 -- Check if EFC process parameters are set
2074 --
2075 if p_action_id is not null
2076 and p_pk1 is not null
2077 and p_chunk is not null
2078 and p_efc_worker_id is not null
2079 then
2080 --
2081 l_efc_batch := TRUE;
2082 --
2083 end if;
2084 --
2085 l_from_str := ' FROM pay_element_entry_values_f eev, '
2086 ||' ben_prtt_rt_val prv, '
2087 ||' ben_prtt_enrt_rslt_f pen, '
2088 ||' ben_acty_base_rt_f abr, '
2089 ||' ben_per_in_ler pil, '
2090 ||' per_all_people_f per ';
2091 --
2092 l_where_str := ' where prv.element_entry_value_id = eev.element_entry_value_id '
2093 ||' and prv.prtt_rt_val_id = '
2094 ||' (select min(prv1.prtt_rt_val_id) '
2095 ||' from ben_prtt_rt_val prv1 '
2096 ||' where prv.element_entry_value_id = prv1.element_entry_value_id '
2097 ||' ) '
2098 ||' and prv.rt_strt_dt '
2099 ||' between pen.effective_start_date and pen.effective_end_date '
2100 ||' and pen.effective_start_date = '
2101 ||' (select min(pen1.effective_start_date) '
2102 ||' from ben_prtt_enrt_rslt_f pen1 '
2103 ||' where pen.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id '
2104 ||' ) '
2105 ||' and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id '
2106 ||' and prv.acty_base_rt_id = abr.acty_base_rt_id '
2107 ||' and prv.rt_strt_dt '
2108 ||' between abr.effective_start_date and abr.effective_end_date '
2109 ||' and abr.effective_start_date = '
2110 ||' (select min(abr1.effective_start_date) '
2111 ||' from ben_acty_base_rt_f abr1 '
2112 ||' where abr.acty_base_rt_id = abr1.acty_base_rt_id '
2113 ||' ) '
2114 ||' and prv.per_in_ler_id = pil.per_in_ler_id '
2115 ||' and per.person_id = pil.person_id '
2116 ||' and prv.rt_strt_dt '
2117 ||' between per.effective_start_date and per.effective_end_date '
2118 ||' and per.effective_start_date = '
2119 ||' (select min(per1.effective_start_date) '
2120 ||' from per_all_people_f per1 '
2121 ||' where per.person_id = per1.person_id '
2122 ||' ) '
2123 ||' and eev.screen_entry_value is not null '
2124 /* Exclude out nocopy voided and backed out nocopy life events */
2125 ||' and pil.per_in_ler_stat_cd not in ('
2126 ||''''||'VOIDD'||''''||','||''''||'BCKDT'||''''||') '
2127 ;
2128 --
2129 -- Check if we are restricting by business group
2130 --
2131 if p_business_group_id is not null then
2132 --
2133 l_where_str := l_where_str||' and prv.business_group_id = '||p_business_group_id;
2134 --
2135 end if;
2136 --
2137 -- Build in batch specific restrictions
2138 --
2139 if l_efc_batch then
2140 --
2141 l_from_str := l_from_str||', pay_element_entry_values_f_efc efc ';
2142 l_where_str := l_where_str||' and efc.element_entry_value_id = eev.element_entry_value_id '
2143 ||' and efc.effective_start_date = eev.effective_start_date '
2144 ||' and efc.efc_action_id = :action_id '
2145 ||' and eev.element_entry_value_id > :pk1 '
2146 ||' and mod(eev.element_entry_value_id, :total_workers) = :worker_id ';
2147 --
2148 elsif p_valworker_id is not null
2149 and p_valtotal_workers is not null
2150 then
2151 --
2152 l_where_str := l_where_str||' and mod(eev.element_entry_value_id, :valtotal_workers) = :valworker_id ';
2153 --
2154 end if;
2155 --
2156 l_sql_str := ' select eev.element_entry_value_id, '
2157 ||' eev.effective_start_date, '
2158 ||' eev.effective_end_date, '
2159 ||' prv.creation_date, '
2160 ||' prv.last_update_date, '
2161 ||' prv.last_update_login, '
2162 ||' prv.created_by, '
2163 ||' eev.screen_entry_value, '
2164 ||' pen.enrt_mthd_cd, '
2165 ||' prv.business_group_id, '
2166 ||' prv.prtt_rt_val_id, '
2167 ||' prv.acty_ref_perd_cd, '
2168 ||' prv.acty_base_rt_id, '
2169 ||' prv.prtt_enrt_rslt_id, '
2170 ||' prv.rt_strt_dt, '
2171 ||' prv.rt_val, '
2172 ||' prv.object_version_number, '
2173 ||' abr.input_value_id, '
2174 ||' abr.element_type_id, '
2175 ||' pil.lf_evt_ocrd_dt, '
2176 ||' pil.person_id, '
2177 ||' pil.per_in_ler_id, '
2178 ||' eev.ELEMENT_ENTRY_ID '
2179 ||l_from_str
2180 ||l_where_str
2181 ||' order by eev.element_entry_value_id, '
2182 ||' eev.effective_start_date ';
2183 --
2184 if l_efc_batch then
2185 --
2186 /*
2187 l_sql_str := l_sql_str||' for update of eev.element_entry_value_id ';
2188 --
2189 */
2190 hr_efc_info.insert_line('-- ');
2191 hr_efc_info.insert_line('-- Adjusting element entry values. Worker: '||p_worker_id
2192 ||' of '||p_total_workers
2193 );
2194 hr_efc_info.insert_line('-- ');
2195 --
2196 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
2197 --
2198 elsif p_valworker_id is not null
2199 and p_valtotal_workers is not null
2200 then
2201 --
2202 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
2203 --
2204 else
2205 --
2206 hr_efc_info.insert_line('-- ');
2207 hr_efc_info.insert_line('-- Validating element entry value adjustments');
2208 hr_efc_info.insert_line('-- ');
2209 --
2210 open c_efc_rows FOR l_sql_str;
2211 --
2212 end if;
2213 --
2214 l_prev_pkid := -999;
2215 l_prev_esd := hr_api.g_sot;
2216 --
2217 loop
2218 FETCH c_efc_rows INTO l_efc_row;
2219 EXIT WHEN c_efc_rows%NOTFOUND;
2220 --
2221 l_faterr_code := null;
2222 l_faterr_type := null;
2223 l_adjfailed := FALSE;
2224 --
2225 if l_faterr_code is null then
2226 --
2227 -- Deduce the effective date
2228 --
2229 -- BENAUTEN - Takes the minimum of the effective date and the
2230 -- enrt perd strt dt from the PEL
2231 --
2232 l_effective_date := l_efc_row.rt_strt_dt;
2233 --
2234 -- Check for exclusions
2235 --
2236 -- Assignments with a null payroll as of EEV effective start date
2237 --
2238 l_dummy_id := null;
2239 --
2240 begin
2241 --
2242 l_assign_exists := ben_element_entry.chk_assign_exists
2243 (p_person_id => l_efc_row.person_id
2244 ,p_business_group_id => l_efc_row.business_group_id
2245 ,p_effective_date => l_effective_date
2246 ,p_rate_date => l_efc_row.rt_strt_dt
2247 ,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
2248 ,p_assignment_id => l_dummy_id
2249 ,p_organization_id => l_dummy_id
2250 ,p_payroll_id => l_dummy_id
2251 );
2252 --
2253 exception
2254 when others then
2255 --
2256 if instr(SQLERRM,'92458') > 0 then
2257 --
2258 l_faterr_code := 'NOASGPAY';
2259 l_faterr_type := 'MISSINGSETUP';
2260 --
2261 else
2262 --
2263 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2264 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
2265 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
2266 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).bgp_id := l_efc_row.business_group_id;
2267 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENELMEN_CHKASG';
2268 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
2269 --
2270 l_rcoerr_count := l_rcoerr_count+1;
2271 --
2272 end if;
2273 --
2274 end;
2275 --
2276 end if;
2277 --
2278 if l_faterr_code is null
2279 then
2280 --
2281 -- Check if the ABR was modified after the element entry was created
2282 --
2283 open c_abrdets
2284 (c_abr_id => l_efc_row.acty_base_rt_id
2285 ,c_eff_date => l_effective_date
2286 );
2287 fetch c_abrdets into l_abrdets;
2288 if c_abrdets%notfound then
2289 --
2290 l_faterr_code := 'NODTABR';
2291 --
2292 end if;
2293 close c_abrdets;
2294 --
2295 end if;
2296 --
2297 if l_faterr_code is null
2298 then
2299 --
2300 begin
2301 --
2302 -- Clear distribute rates function caches
2303 --
2304 ben_distribute_rates.clear_down_cache;
2305 --
2306 ben_element_entry.create_enrollment_element
2307 (p_calculate_only_mode => TRUE
2308 ,p_business_group_id => l_efc_row.business_group_id
2309 ,p_prtt_rt_val_id => l_efc_row.prtt_rt_val_id
2310 ,p_person_id => l_efc_row.person_id
2311 ,p_acty_ref_perd => l_efc_row.acty_ref_perd_cd
2312 ,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
2313 ,p_enrt_rslt_id => l_efc_row.prtt_enrt_rslt_id
2314 ,p_rt_start_date => l_efc_row.rt_strt_dt
2315 ,p_rt => l_efc_row.rt_val
2316 ,p_input_value_id => l_efc_row.input_value_id
2317 ,p_element_type_id => l_efc_row.element_type_id
2318 ,p_prv_object_version_number=> l_efc_row.prv_ovn
2319 ,p_effective_date => l_effective_date
2320 --
2321 ,p_eev_screen_entry_value => l_eev_screen_entry_value
2322 ,p_element_entry_value_id => l_dummy_number
2323 );
2324 --
2325 -- Check for a special values. Multiple datetracked EEVs
2326 --
2327 if ben_element_entry.g_creee_calc_vals.special_pp_date is not null
2328 then
2329 --
2330 if ben_element_entry.g_creee_calc_vals.special_pp_date = l_efc_row.effective_start_date
2331 then
2332 --
2333 l_eev_screen_entry_value := ben_element_entry.g_creee_calc_vals.special_amt;
2334 --
2335 elsif ben_element_entry.g_creee_calc_vals.normal_pp_date = l_efc_row.effective_start_date
2336 then
2337 --
2338 l_eev_screen_entry_value := ben_element_entry.g_creee_calc_vals.normal_amt;
2339 --
2340 end if;
2341 --
2342 else
2343 --
2344 l_eev_screen_entry_value := ben_element_entry.g_creee_calc_vals.normal_amt;
2345 --
2346 end if;
2347 --
2348 l_old_val1 := l_efc_row.screen_entry_value;
2349 l_new_val1 := l_eev_screen_entry_value;
2350 --
2351 if nvl(l_eev_screen_entry_value,-9999) <> nvl(l_efc_row.screen_entry_value,-9999)
2352 then
2353 --
2354 -- Post adjustment checks
2355 --
2356 if l_eev_screen_entry_value is null then
2357 --
2358 if l_abrdets.ele_rqd_flag = 'N' then
2359 --
2360 l_faterr_code := 'PRVABRERQDFLGN';
2361 l_faterr_type := 'CODECHANGE';
2362 --
2363 else
2364 --
2365 l_faterr_code := 'NULLADJEEVSEV';
2366 l_faterr_type := 'ADJUSTBUG';
2367 --
2368 end if;
2369 --
2370 end if;
2371 --
2372 -- Get element entry info
2373 --
2374 if l_faterr_code is null then
2375 --
2376 open c_eledets
2377 (c_ele_id => l_efc_row.ELEMENT_ENTRY_ID
2378 ,c_eff_date => l_efc_row.effective_start_date
2379 );
2380 fetch c_eledets into l_eledets;
2381 if c_eledets%notfound then
2382 --
2383 l_faterr_code := 'NODTELE';
2384 l_faterr_type := 'DATACORRUPT';
2385 --
2386 end if;
2387 close c_eledets;
2388 --
2389 end if;
2390 --
2391 if l_faterr_code is null then
2392 --
2393 -- Check if the EEV is attached to multiple PRVs (bug 1483757)
2394 --
2395 open c_multprveevs
2396 (c_eev_id => l_efc_row.ELEMENT_ENTRY_VALUE_id
2397 );
2398 fetch c_multprveevs into l_count;
2399 close c_multprveevs;
2400 --
2401 if l_count > 1 then
2402 --
2403 l_faterr_code := 'WWBUG1483757';
2404 l_faterr_type := 'POTENTIALCODEBUG';
2405 --
2406 end if;
2407 --
2408 end if;
2409 --
2410 -- Check for a duplicate DT row
2411 --
2412 if l_efc_row.element_entry_value_id = l_prev_pkid
2413 and l_efc_row.effective_start_date = l_prev_esd
2414 and l_faterr_code is null
2415 then
2416 --
2417 l_faterr_code := 'DUPDTROW';
2418 l_faterr_type := 'DATACORRUPT';
2419 --
2420 end if;
2421 /*
2422 --
2423 -- Check for a conversion factor
2424 --
2425 if l_efc_batch
2426 and l_faterr_code is null
2427 then
2428 --
2429 -- Check for un converted PRVs
2430 --
2431 open c_convvalexist
2432 (c_prv_id => l_efc_row.prtt_rt_val_id
2433 ,c_efc_action_id => p_action_id
2434 );
2435 fetch c_convvalexist into l_convvalexist;
2436 if c_convvalexist%notfound then
2437 --
2438 l_faterr_code := 'PRVVALNOCONVERT';
2439 l_faterr_type := 'CONVEXCLUSION';
2440 --
2441 end if;
2442 close c_convvalexist;
2443 --
2444 -- Check for PRV exclusions in batch mode
2445 --
2446 if l_faterr_code is null then
2447 --
2448 open c_valexcexist
2449 (c_prv_id => l_efc_row.prtt_rt_val_id
2450 ,c_efc_action_id => p_action_id
2451 ,c_ent_scode => 'PRV'
2452 );
2453 fetch c_valexcexist into l_valexcexist;
2454 if c_valexcexist%found then
2455 --
2456 l_faterr_code := 'PRVVALEXCL';
2457 l_faterr_type := 'CONVEXCLUSION';
2458 --
2459 end if;
2460 close c_valexcexist;
2461 --
2462 end if;
2463 --
2464 if l_faterr_code is null then
2465 --
2466 -- get pre conversion details
2467 --
2468 open c_preconvdets
2469 (c_efc_action_id => p_action_id
2470 ,c_eev_id => l_efc_row.ELEMENT_ENTRY_VALUE_id
2471 ,c_eev_esd => l_efc_row.effective_start_date
2472 );
2473 fetch c_preconvdets into l_preconvdets;
2474 if c_preconvdets%notfound then
2475 --
2476 l_faterr_code := 'NOEFCACTEEV';
2477 l_faterr_type := 'CORRUPTDATA';
2478 --
2479 end if;
2480 close c_preconvdets;
2481 --
2482 end if;
2483 --
2484 -- Get the currency conversion factor details
2485 --
2486 if l_faterr_code is null then
2487 --
2488 ben_efc_adjustments.DetectConvInfo
2489 (p_ncucurr_code => l_preconvdets.input_currency_code
2490 ,p_new_val => l_eev_screen_entry_value
2491 ,p_preconv_val => l_preconvdets.SCREEN_ENTRY_VALUE
2492 --
2493 ,p_faterr_code => l_faterr_code
2494 ,p_faterr_type => l_faterr_type
2495 ,p_postconv_val => l_postconv_val
2496 );
2497 --
2498 if l_faterr_code is not null then
2499 --
2500 l_old_val1 := l_preconvdets.SCREEN_ENTRY_VALUE;
2501 l_new_val1 := l_postconv_val;
2502 --
2503 end if;
2504 --
2505 end if;
2506 --
2507 end if;
2508 */
2509 --
2510 -- Check rounding
2511 --
2512 if l_faterr_code is null then
2513 --
2514 ben_efc_adjustments.DetectRoundInfo
2515 (p_rndg_cd => null
2516 ,p_rndg_rl => null
2517 ,p_old_val => l_efc_row.screen_entry_value
2518 ,p_new_val => l_eev_screen_entry_value
2519 ,p_eff_date => l_efc_row.rt_strt_dt
2520 --
2521 ,p_faterr_code => l_faterr_code
2522 ,p_faterr_type => l_faterr_type
2523 );
2524 --
2525 end if;
2526 --
2527 -- Check for multiple batch process modifications
2528 --
2529 if l_faterr_code is null then
2530 --
2531 for row in c_eledtinsts
2532 (c_ele_id => l_efc_row.ELEMENT_ENTRY_ID
2533 )
2534 loop
2535 --
2536 open c_eledets
2537 (c_ele_id => l_efc_row.ELEMENT_ENTRY_ID
2538 ,c_eff_date => row.effective_start_date
2539 );
2540 fetch c_eledets into l_tmpdets;
2541 if c_eledets%notfound then
2542 --
2543 l_faterr_code := 'NOELEDETS';
2544 l_faterr_type := 'DATACORRUPT';
2545 --
2546 end if;
2547 close c_eledets;
2548 --
2549 -- Check for a modified PRV
2550 --
2551 if l_tmpdets.last_update_login <> l_efc_row.prv_last_update_login
2552 and l_tmpdets.created_by = l_efc_row.prv_created_by
2553 and l_faterr_code is null
2554 then
2555 --
2556 if l_efc_row.prv_last_update_login = -1 then
2557 --
2558 l_faterr_code := 'SQLPLUSELEPRVCORR';
2559 l_faterr_type := 'DATACORRUPT';
2560 --
2561 else
2562 --
2563 l_faterr_code := 'ELEPRVCORR';
2564 l_faterr_type := 'UNSUPPORTTRANS';
2565 --
2566 end if;
2567 --
2568 end if;
2569 --
2570 end loop;
2571 --
2572 end if;
2573 --
2574 -- Check for PRV mods
2575 --
2576 if l_efc_row.prv_ovn > 1
2577 and l_faterr_code is null
2578 then
2579 --
2580 l_faterr_code := 'PRVMODS';
2581 l_faterr_type := 'UNSUPPORTTRANS';
2582 --
2583 end if;
2584 --
2585 if l_faterr_code is null then
2586 --
2587 -- Check if the ELE has been modified
2588 --
2589 ben_efc_adjustments.DetectWhoInfo
2590 (p_creation_date => l_eledets.creation_date
2591 ,p_last_update_date => l_eledets.last_update_date
2592 ,p_object_version_number => l_eledets.object_version_number
2593 --
2594 ,p_who_counts => l_who_counts
2595 ,p_faterr_code => l_faterr_code
2596 ,p_faterr_type => l_faterr_type
2597 );
2598 --
2599 end if;
2600 --
2601 if l_abrdets.last_update_date > l_eledets.creation_date
2602 and l_faterr_code is null
2603 then
2604 --
2605 l_faterr_code := 'ABRCORR';
2606 l_faterr_type := 'CORRECTEDINFO';
2607 --
2608 elsif l_faterr_code is null then
2609 --
2610 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).id := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2611 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
2612 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
2613 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).bgp_id := l_efc_row.business_group_id;
2614 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).credt := l_eledets.creation_date;
2615 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).lud := l_eledets.last_update_date;
2616 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).old_val1 := l_old_val1;
2617 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).new_val1 := l_new_val1;
2618 ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).val_type := 'EEV_SCRENTVAL';
2619 --
2620 l_adjfailed := TRUE;
2621 l_calfail_count := l_calfail_count+1;
2622 --
2623 end if;
2624 --
2625 else
2626 --
2627 l_adjfailed := FALSE;
2628 --
2629 -- Success exclusions
2630 --
2631 if l_efc_batch
2632 and l_faterr_code is null
2633 then
2634 --
2635 -- Check for un converted PRVs
2636 --
2637 open c_convvalexist
2638 (c_prv_id => l_efc_row.prtt_rt_val_id
2639 ,c_efc_action_id => p_action_id
2640 );
2641 fetch c_convvalexist into l_convvalexist;
2642 if c_convvalexist%notfound then
2643 --
2644 l_faterr_code := 'PRVVALNOCONVERT';
2645 l_faterr_type := 'CONVEXCLUSION';
2646 --
2647 end if;
2648 close c_convvalexist;
2649 --
2650 if l_faterr_code is null then
2651 --
2652 -- Check for PRV exclusions in batch mode
2653 --
2654 open c_valexcexist
2655 (c_prv_id => l_efc_row.prtt_rt_val_id
2656 ,c_efc_action_id => p_action_id
2657 ,c_ent_scode => 'PRV'
2658 );
2659 fetch c_valexcexist into l_valexcexist;
2660 if c_valexcexist%found then
2661 --
2662 l_faterr_code := 'PRVVALEXCL';
2663 l_faterr_type := 'CONVEXCLUSION';
2664 --
2665 end if;
2666 close c_valexcexist;
2667 --
2668 end if;
2669 --
2670 end if;
2671 --
2672 end if;
2673 --
2674 if l_efc_batch
2675 and (l_faterr_code is null
2676 or nvl(l_faterr_type,'ZZZZ') = 'CONVEXCLUSION')
2677 then
2678 --
2679 update PAY_ELEMENT_ENTRY_VALUES_F eev
2680 set eev.screen_entry_value = l_eev_screen_entry_value
2681 where eev.ELEMENT_ENTRY_VALUE_id = l_efc_row.ELEMENT_ENTRY_VALUE_id
2682 and eev.effective_start_date = l_efc_row.effective_start_date;
2683 --
2684 if p_validate then
2685 --
2686 rollback;
2687 --
2688 end if;
2689 --
2690 -- Check for end of chunk and commit if necessary
2691 --
2692 l_pk1 := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2693 --
2694 ben_efc_functions.maintain_chunks
2695 (p_row_count => l_chunkrow_count
2696 ,p_pk1 => l_pk1
2697 ,p_chunk_size => p_chunk
2698 ,p_efc_worker_id => p_efc_worker_id
2699 );
2700 --
2701 end if;
2702 --
2703 exception
2704 when others then
2705 --
2706 if instr(SQLERRM,'92690') > 0 then
2707 --
2708 l_faterr_code := 'WWBUG1691913';
2709 l_faterr_type := 'FIXEDCODEBUG';
2710 --
2711 elsif instr(SQLERRM,'92547') > 0 then
2712 --
2713 l_faterr_code := 'NOPRVABRDTIPV';
2714 l_faterr_type := 'MISSINGSETUP';
2715 --
2716 elsif instr(SQLERRM,'91884') > 0 then
2717 --
2718 l_faterr_code := 'PENPLNYPINFO';
2719 l_faterr_type := 'POTENTIALCODEBUG';
2720 --
2721 elsif instr(SQLERRM,'92346') > 0 then
2722 --
2723 l_faterr_code := 'NOPAYPTPNXMTH';
2724 l_faterr_type := 'MISSINGSETUP';
2725 --
2726 else
2727 --
2728 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2729 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
2730 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
2731 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).bgp_id := l_efc_row.business_group_id;
2732 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).lud := l_eledets.last_update_date;
2733 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENELMEN';
2734 ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
2735 --
2736 l_rcoerr_count := l_rcoerr_count+1;
2737 --
2738 end if;
2739 --
2740 end;
2741 --
2742 else
2743 --
2744 l_preadjexc_count := l_preadjexc_count+1;
2745 --
2746 end if;
2747 --
2748 -- Check for fatal errors
2749 --
2750 if l_faterr_code is not null
2751 then
2752 --
2753 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).id := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2754 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).esd := l_efc_row.effective_start_date;
2755 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).eed := l_efc_row.effective_end_date;
2756 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
2757 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
2758 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
2759 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
2760 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).bgp_id := l_efc_row.business_group_id;
2761 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).lud := l_eledets.last_update_date;
2762 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).credt := l_eledets.creation_date;
2763 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).ovn := l_eledets.object_version_number;
2764 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).cre_by := l_eledets.created_by;
2765 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).lu_by := l_eledets.last_updated_by;
2766 ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).id1 := l_efc_row.prtt_rt_val_id;
2767 --
2768 l_faterrs_count := l_faterrs_count+1;
2769 --
2770 elsif l_faterr_code is null
2771 and not l_adjfailed
2772 then
2773 --
2774 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).id := l_efc_row.ELEMENT_ENTRY_VALUE_id;
2775 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).esd := l_efc_row.effective_start_date;
2776 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).eed := l_efc_row.effective_end_date;
2777 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
2778 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
2779 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).val_type := 'EEV_SCRENTVAL';
2780 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).credt := l_eledets.creation_date;
2781 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).lud := l_eledets.last_update_date;
2782 ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).id1 := l_efc_row.prtt_rt_val_id;
2783 --
2784 l_calsucc_count := l_calsucc_count+1;
2785 -- --
2786 end if;
2787 --
2788 l_row_count := l_row_count+1;
2789 l_prev_pkid := l_efc_row.element_entry_value_id;
2790 l_prev_esd := l_efc_row.effective_start_date;
2791 --
2792 end loop;
2793 CLOSE c_efc_rows;
2794 /*
2795 --
2796 -- Write exceptions down to the table
2797 --
2798 if l_efc_batch
2799 and p_valworker_id is null
2800 and p_valtotal_workers is null
2801 then
2802 --
2803 ben_efc_adjustments.insert_validation_exceptions
2804 (p_val_set => ben_efc_adjustments.g_eev_failed_adj_val_set
2805 ,p_efc_action_id => p_action_id
2806 ,p_ent_scode => 'EEV'
2807 ,p_exception_type => 'AF'
2808 );
2809 --
2810 ben_efc_adjustments.insert_validation_exceptions
2811 (p_val_set => ben_efc_adjustments.g_eev_fatal_error_val_set
2812 ,p_efc_action_id => p_action_id
2813 ,p_ent_scode => 'EEV'
2814 ,p_exception_type => null
2815 );
2816 --
2817 end if;
2818 */
2819 --
2820 -- Check that all rows have been converted or excluded
2821 --
2822 l_sql_str := 'select count(*) '
2823 ||' from pay_element_entry_values_f eev, '
2824 ||' ben_prtt_rt_val prv '
2825 ||' where prv.element_entry_value_id = eev.element_entry_value_id '
2826 ||' and eev.screen_entry_value is not null ';
2827 --
2828 if p_business_group_id is not null then
2829 --
2830 l_sql_str := l_sql_str||' and prv.business_group_id = '||p_business_group_id;
2831 --
2832 end if;
2833 --
2834 ben_efc_functions.conv_check
2835 (p_table_name => 'pay_element_entry_values_f'
2836 ,p_efctable_name => 'pay_element_entry_values_f_efc'
2837 --
2838 ,p_table_sql => l_sql_str
2839 ,p_efctable_sql => 'select count(*) from pay_element_entry_values_f_efc '
2840 ||' where efc_action_id = '||p_action_id
2841 --
2842 ,p_bgp_id => p_business_group_id
2843 ,p_action_id => p_action_id
2844 --
2845 ,p_conv_count => l_conv_count
2846 ,p_unconv_count => l_unconv_count
2847 ,p_tabrow_count => l_tabrow_count
2848 );
2849 --
2850 -- Set counts
2851 --
2852 if p_action_id is null then
2853 --
2854 l_actconv_count := 0;
2855 --
2856 else
2857 --
2858 l_actconv_count := l_conv_count;
2859 --
2860 end if;
2861 --
2862 p_adjustment_counts.tabrow_count := l_tabrow_count;
2863 p_adjustment_counts.efcrow_count := l_row_count;
2864 p_adjustment_counts.actconv_count := l_actconv_count;
2865 p_adjustment_counts.calfail_count := l_calfail_count;
2866 p_adjustment_counts.calsucc_count := l_calsucc_count;
2867 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
2868 p_adjustment_counts.faterrs_count := l_faterrs_count;
2869 p_adjustment_counts.preadjexc_count := l_preadjexc_count;
2870 --
2871 end eev_adjustments;
2872 --
2873 procedure bpl_adjustments
2874 (p_validate in boolean default false
2875 ,p_worker_id in number default null
2876 ,p_action_id in number default null
2877 ,p_total_workers in number default null
2878 ,p_pk1 in number default null
2879 ,p_chunk in number default null
2880 ,p_efc_worker_id in number default null
2881 --
2882 ,p_valworker_id in number default null
2883 ,p_valtotal_workers in number default null
2884 --
2885 ,p_business_group_id in number default null
2886 --
2887 ,p_adjustment_counts out nocopy ben_efc_adjustments.g_adjustment_counts
2888 )
2889 is
2890 --
2891 TYPE cur_type IS REF CURSOR;
2892 --
2893 type g_efc_row is record
2894 (bnft_prvdd_ldgr_id ben_bnft_prvdd_ldgr_f.bnft_prvdd_ldgr_id%type
2895 ,effective_start_date ben_bnft_prvdd_ldgr_f.effective_start_date%type
2896 ,effective_end_date ben_bnft_prvdd_ldgr_f.effective_end_date%type
2897 ,creation_date ben_bnft_prvdd_ldgr_f.creation_date%type
2898 ,last_update_date ben_bnft_prvdd_ldgr_f.last_update_date%type
2899 ,object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%type
2900 ,created_by ben_bnft_prvdd_ldgr_f.created_by%type
2901 ,last_updated_by ben_bnft_prvdd_ldgr_f.last_updated_by%type
2902 ,used_val ben_bnft_prvdd_ldgr_f.used_val%type
2903 ,FRFTD_VAL ben_bnft_prvdd_ldgr_f.FRFTD_VAL%type
2904 ,PRVDD_VAL ben_bnft_prvdd_ldgr_f.PRVDD_VAL%type
2905 ,RLD_UP_VAL ben_bnft_prvdd_ldgr_f.RLD_UP_VAL%type
2906 ,CASH_RECD_VAL ben_bnft_prvdd_ldgr_f.CASH_RECD_VAL%type
2907 ,business_group_id ben_bnft_prvdd_ldgr_f.business_group_id%type
2908 ,bnft_prvdr_pool_id ben_bnft_prvdd_ldgr_f.bnft_prvdr_pool_id%type
2909 ,acty_base_rt_id ben_bnft_prvdd_ldgr_f.acty_base_rt_id%type
2910 ,prtt_enrt_rslt_id ben_bnft_prvdd_ldgr_f.prtt_enrt_rslt_id%type
2911 ,person_id ben_per_in_ler.person_id%type
2912 ,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
2913 ,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
2914 );
2915 --
2916 c_efc_rows cur_type;
2917 --
2918 l_proc varchar2(1000) := 'bpl_adjustments';
2919 --
2920 l_epe_rec ben_epe_shd.g_rec_type;
2921 --
2922 l_efc_batch boolean;
2923 --
2924 l_sql_str long;
2925 l_from_str long;
2926 l_where_str long;
2927 l_groupby_str long;
2928 --
2929 l_efc_row g_efc_row;
2930 --
2931 l_who_counts ben_efc_adjustments.g_who_counts;
2932 l_olddata boolean;
2933 --
2934 l_faterr_code varchar2(100);
2935 l_faterr_type varchar2(100);
2936 --
2937 l_row_count pls_integer;
2938 l_calfail_count pls_integer;
2939 l_calsucc_count pls_integer;
2940 l_conv_count pls_integer;
2941 l_unconv_count pls_integer;
2942 l_actconv_count pls_integer;
2943 l_dupconv_count pls_integer;
2944 --
2945 l_rcoerr_count pls_integer;
2946 l_faterrs_count pls_integer;
2947 --
2948 l_pk1 number;
2949 --
2950 l_tabrow_count pls_integer;
2951 l_chunkrow_count pls_integer;
2952 --
2953 l_effective_date date;
2954 --
2955 l_dummy_number number;
2956 l_dummy_varchar2 varchar2(30);
2957 l_dummy_date date;
2958 --
2959 l_bpl_id number;
2960 l_bpl_used_val number;
2961 l_bpl_frftd_val number;
2962 l_bpl_prvdd_val number;
2963 l_bpl_rld_up_val number;
2964 l_bpl_cash_recd_val number;
2965 --
2966 l_adjfailed boolean;
2967 l_val_type varchar2(100);
2968 l_old_val1 number;
2969 l_new_val1 number;
2970 --
2971 l_ecr_count pls_integer;
2972 --
2973 cursor c_penprvdets
2974 (c_pen_id in number
2975 ,c_abr_id in number
2976 ,c_eff_date in date
2977 )
2978 is
2979 select ecr.prtt_rt_val_id,
2980 ecr.ELIG_PER_ELCTBL_CHC_ID
2981 from ben_enrt_bnft enb,
2982 ben_enrt_rt ecr,
2983 ben_bnft_prvdd_ldgr_f bpl,
2984 ben_elig_per_elctbl_chc epe
2985 where ecr.DECR_BNFT_PRVDR_POOL_ID = bpl.BNFT_PRVDR_POOL_ID
2986 and ecr.acty_base_rt_id = bpl.acty_base_rt_id
2987 and ecr.enrt_bnft_id = enb.enrt_bnft_id
2988 and bpl.per_in_ler_id = epe.per_in_ler_id
2989 and enb.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
2990 and bpl.acty_base_rt_id = c_abr_id
2991 and bpl.prtt_enrt_rslt_id = c_pen_id
2992 union
2993 select ecr.prtt_rt_val_id,
2994 ecr.ELIG_PER_ELCTBL_CHC_ID
2995 from ben_enrt_rt ecr,
2996 ben_bnft_prvdd_ldgr_f bpl,
2997 ben_elig_per_elctbl_chc epe
2998 where ecr.DECR_BNFT_PRVDR_POOL_ID = bpl.BNFT_PRVDR_POOL_ID
2999 and ecr.acty_base_rt_id = bpl.acty_base_rt_id
3000 and ecr.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
3001 and bpl.per_in_ler_id = epe.per_in_ler_id
3002 and bpl.acty_base_rt_id = c_abr_id
3003 and bpl.prtt_enrt_rslt_id = c_pen_id
3004 ;
3005 --
3006 l_penprvdets c_penprvdets%rowtype;
3007 --
3008 cursor c_bpldets
3009 (c_bpl_id in number
3010 ,c_eff_date in date
3011 )
3012 is
3013 select bpl.bnft_prvdd_ldgr_id,
3014 bpl.effective_start_date,
3015 bpl.effective_end_date,
3016 bpl.used_val,
3017 bpl.FRFTD_VAL,
3018 bpl.PRVDD_VAL,
3019 bpl.RLD_UP_VAL,
3020 bpl.CASH_RECD_VAL,
3021 bpl.creation_date,
3022 bpl.last_update_date,
3023 bpl.created_by,
3024 bpl.last_updated_by,
3025 bpl.object_version_number
3026 from ben_bnft_prvdd_ldgr_f bpl
3027 where bpl.bnft_prvdd_ldgr_id = c_bpl_id
3028 and c_eff_date
3029 between bpl.effective_start_date and bpl.effective_end_date;
3030 --
3031 l_bpldets c_bpldets%rowtype;
3032 --
3033 cursor c_ecrdets
3034 (c_abr_id in number
3035 ,c_pil_id in number
3036 )
3037 is
3038 select ecr.enrt_rt_id,
3039 ecr.enrt_bnft_ID,
3040 ecr.ELIG_PER_ELCTBL_CHC_ID,
3041 ecr.decr_bnft_prvdr_pool_id,
3042 ecr.prtt_rt_val_id,
3043 ecr.val,
3044 epe.prtt_enrt_rslt_id
3045 from ben_enrt_rt ecr,
3046 ben_elig_per_elctbl_chc epe
3047 where ecr.acty_base_rt_id = c_abr_id
3048 /*
3049 and ecr.decr_bnft_prvdr_pool_id = c_bpp_id
3050 */
3051 and ecr.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
3052 and epe.per_in_ler_id = c_pil_id
3053 /*
3054 and epe.prtt_enrt_rslt_id = c_pen_id
3055 */
3056 union
3057 select ecr.enrt_rt_id,
3058 ecr.enrt_bnft_ID,
3059 ecr.ELIG_PER_ELCTBL_CHC_ID,
3060 ecr.decr_bnft_prvdr_pool_id,
3061 ecr.prtt_rt_val_id,
3062 ecr.val,
3063 epe.prtt_enrt_rslt_id
3064 from ben_enrt_rt ecr,
3065 ben_enrt_bnft enb,
3066 ben_elig_per_elctbl_chc epe
3067 where
3068 /*
3069 ecr.DECR_BNFT_PRVDR_POOL_ID = c_bpp_id
3070 and
3071 */
3072 ecr.acty_base_rt_id = c_abr_id
3073 and ecr.enrt_bnft_id = enb.enrt_bnft_id
3074 and enb.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
3075 and epe.per_in_ler_id = c_pil_id;
3076 /*
3077 and epe.prtt_enrt_rslt_id = c_pen_id;
3078 */
3079 --
3080 l_ecrdets c_ecrdets%rowtype;
3081 --
3082 cursor c_bppdets
3083 (c_bpp_id in number
3084 ,c_eff_date in date
3085 )
3086 is
3087 select bpp.dflt_excs_trtmt_cd,
3088 bpp.auto_alct_excs_flag
3089 from ben_bnft_prvdr_pool_f bpp
3090 where bpp.bnft_prvdr_pool_id = c_bpp_id
3091 and c_eff_date
3092 between bpp.effective_start_date and bpp.effective_end_date;
3093 --
3094 l_bppdets c_bppdets%rowtype;
3095 --
3096 begin
3097 --
3098 l_efc_batch := FALSE;
3099 --
3100 l_row_count := 0;
3101 l_calfail_count := 0;
3102 l_calsucc_count := 0;
3103 l_dupconv_count := 0;
3104 l_conv_count := 0;
3105 l_actconv_count := 0;
3106 l_unconv_count := 0;
3107 --
3108 l_rcoerr_count := 0;
3109 --
3110 l_faterrs_count := 0;
3111 --
3112 l_chunkrow_count := 0;
3113 --
3114 ben_efc_adjustments.g_bpl_success_adj_val_set.delete;
3115 ben_efc_adjustments.g_bpl_failed_adj_val_set.delete;
3116 ben_efc_adjustments.g_bpl_rcoerr_val_set.delete;
3117 ben_efc_adjustments.g_bpl_fatal_error_val_set.delete;
3118 --
3119 -- Check if EFC process parameters are set
3120 --
3121 if p_action_id is not null
3122 and p_pk1 is not null
3123 and p_chunk is not null
3124 and p_efc_worker_id is not null
3125 then
3126 --
3127 l_efc_batch := TRUE;
3128 --
3129 end if;
3130 --
3131 l_from_str := ' FROM ben_bnft_prvdd_ldgr_f bpl, '
3132 ||' ben_per_in_ler pil, '
3133 ||' per_all_people_f per ';
3134 --
3135 l_where_str := ' where bpl.per_in_ler_id = pil.per_in_ler_id '
3136 ||' and pil.person_id = per.person_id '
3137 ||' and pil.LF_EVT_OCRD_DT '
3138 ||' between per.effective_start_date and per.effective_end_date '
3139 ||' and pil.LF_EVT_OCRD_DT '
3140 ||' between bpl.effective_start_date and bpl.effective_end_date '
3141 ||' and (bpl.used_val is not null '
3142 ||' or bpl.FRFTD_VAL is not null '
3143 ||' or bpl.PRVDD_VAL is not null '
3144 ||' or bpl.RLD_UP_VAL is not null '
3145 ||' or bpl.CASH_RECD_VAL is not null '
3146 ||' ) '
3147 /* Exclude out nocopy voided and backed out nocopy life events */
3148 ||' and pil.per_in_ler_stat_cd not in ('
3149 ||''''||'VOIDD'||''''||','||''''||'BCKDT'||''''||') '
3150 ;
3151 --
3152 -- Check if we are restricting by business group
3153 --
3154 if p_business_group_id is not null then
3155 --
3156 l_where_str := l_where_str||' and bpl.business_group_id = '||p_business_group_id;
3157 --
3158 end if;
3159 --
3160 -- Build in batch specific restrictions
3161 --
3162 if l_efc_batch then
3163 --
3164 l_from_str := l_from_str||', ben_bnft_prvdd_ldgr_f_efc efc ';
3165 l_where_str := l_where_str||' and efc.bnft_prvdd_ldgr_id = bpl.bnft_prvdd_ldgr_id '
3166 ||' and efc.efc_action_id = :action_id '
3167 ||' and bpl.bnft_prvdd_ldgr_id > :pk1 '
3168 ||' and mod(bpl.bnft_prvdd_ldgr_id, :total_workers) = :worker_id ';
3169 --
3170 elsif p_valworker_id is not null
3171 and p_valtotal_workers is not null
3172 then
3173 --
3174 l_where_str := l_where_str||' and mod(bpl.bnft_prvdd_ldgr_id, :valtotal_workers) = :valworker_id ';
3175 --
3176 end if;
3177 --
3178 l_sql_str := ' select bpl.bnft_prvdd_ldgr_id, '
3179 ||' bpl.effective_start_date, '
3180 ||' bpl.effective_end_date, '
3181 ||' bpl.creation_date, '
3182 ||' bpl.last_update_date, '
3183 ||' bpl.object_version_number, '
3184 ||' bpl.created_by, '
3185 ||' bpl.last_updated_by, '
3186 ||' bpl.used_val, '
3187 ||' bpl.FRFTD_VAL, '
3188 ||' bpl.PRVDD_VAL, '
3189 ||' bpl.RLD_UP_VAL, '
3190 ||' bpl.CASH_RECD_VAL, '
3191 ||' bpl.business_group_id, '
3192 ||' bpl.bnft_prvdr_pool_id, '
3193 ||' bpl.acty_base_rt_id, '
3194 ||' bpl.prtt_enrt_rslt_id, '
3195 ||' pil.person_id, '
3196 ||' pil.per_in_ler_id, '
3197 ||' pil.lf_evt_ocrd_dt '
3198 ||l_from_str
3199 ||l_where_str
3200 ||' order by pil.per_in_ler_id, bpl.prtt_enrt_rslt_id ';
3201 --
3202 if l_efc_batch then
3203 --
3204 hr_efc_info.insert_line('-- ');
3205 hr_efc_info.insert_line('-- Adjusting benefit provider ledgers ');
3206 hr_efc_info.insert_line('-- ');
3207 --
3208 open c_efc_rows FOR l_sql_str using p_action_id, p_pk1, p_total_workers, p_worker_id;
3209 --
3210 elsif p_valworker_id is not null
3211 and p_valtotal_workers is not null
3212 then
3213 --
3214 open c_efc_rows FOR l_sql_str using p_valtotal_workers, p_valworker_id;
3215 --
3216 else
3217 --
3218 open c_efc_rows FOR l_sql_str;
3219 --
3220 end if;
3221 --
3222 loop
3223 FETCH c_efc_rows INTO l_efc_row;
3224 EXIT WHEN c_efc_rows%NOTFOUND;
3225 --
3226 l_faterr_code := null;
3227 l_faterr_type := null;
3228 --
3229 if l_faterr_code is null then
3230 --
3231 begin
3232 --
3233 l_dummy_number := null;
3234 l_dummy_varchar2 := null;
3235 l_dummy_date := null;
3236 l_adjfailed := FALSE;
3237 --
3238 l_bpl_used_val := null;
3239 l_bpl_frftd_val := null;
3240 l_bpl_prvdd_val := null;
3241 l_bpl_rld_up_val := null;
3242 l_bpl_cash_recd_val := null;
3243 --
3244 if l_faterr_code is null then
3245 --
3246 if l_efc_row.bnft_prvdr_pool_id is null then
3247 --
3248 l_faterr_code := 'NULLBPLBPP';
3249 l_faterr_type := 'MISC';
3250 --
3251 end if;
3252 --
3253 end if;
3254 --
3255 if l_faterr_code is null then
3256 --
3257 -- Get the ECRs for the ABR and PIL
3258 --
3259 l_ecr_count := 0;
3260 --
3261 for ecr_row in c_ecrdets
3262 (c_abr_id => l_efc_row.acty_base_rt_id
3263 ,c_pil_id => l_efc_row.per_in_ler_id
3264 )
3265 loop
3266 --
3267 l_ecrdets := ecr_row;
3268 l_ecr_count := l_ecr_count+1;
3269 --
3270 end loop;
3271 --
3272 -- Check for no and multiple ECRs
3273 --
3274 if l_ecr_count = 0 then
3275 --
3276 l_faterr_code := 'NOECR';
3277 l_faterr_type := 'POTENTIALCODEBUG';
3278 --
3279 elsif l_ecr_count = 2 then
3280 --
3281 l_faterr_code := 'MULTECRS';
3282 l_faterr_type := 'POTENTIALCODEBUG';
3283 --
3284 end if;
3285 --
3286 end if;
3287 --
3288 l_bpl_id := null;
3289 --
3290 if l_efc_row.used_val is not null
3291 and l_faterr_code is null
3292 then
3293 --
3294 ben_provider_pools.create_debit_ledger_entry
3295 (p_calculate_only_mode => TRUE
3296 ,p_person_id => l_efc_row.person_id
3297 ,p_per_in_ler_id => l_efc_row.per_in_ler_id
3298 ,p_elig_per_elctbl_chc_id => null
3299 ,p_prtt_enrt_rslt_id => l_efc_row.prtt_enrt_rslt_id
3300 ,p_decr_bnft_prvdr_pool_id => null
3301 ,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
3302 ,p_prtt_rt_val_id => l_ecrdets.prtt_rt_val_id
3303 ,p_enrt_mthd_cd => null
3304 ,p_val => null
3305 ,p_bnft_prvdd_ldgr_id => l_bpl_id
3306 ,p_business_group_id => l_efc_row.business_group_id
3307 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3308 --
3309 ,p_bpl_used_val => l_bpl_used_val
3310 );
3311 --
3312 end if;
3313 --
3314 if l_efc_row.prvdd_val is not null
3315 and l_faterr_code is null
3316 then
3317 --
3318 l_epe_rec.per_in_ler_id := l_efc_row.per_in_ler_id;
3319 l_epe_rec.elig_per_elctbl_chc_id := l_ecrdets.ELIG_PER_ELCTBL_CHC_ID;
3320 l_epe_rec.business_group_id := l_efc_row.business_group_id;
3321 l_epe_rec.bnft_prvdr_pool_id := l_efc_row.bnft_prvdr_pool_id;
3322 --
3323 ben_provider_pools.create_credit_ledger_entry
3324 (p_calculate_only_mode => TRUE
3325 ,p_person_id => l_efc_row.person_id
3326 ,p_epe_rec => l_epe_rec
3327 ,p_enrt_mthd_cd => null
3328 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3329 --
3330 ,p_bnft_prvdd_ldgr_id => l_bpl_id
3331 ,p_bpl_prvdd_val => l_bpl_prvdd_val
3332 );
3333 --
3334 end if;
3335 --
3336 if l_efc_row.cash_recd_val is not null
3337 and l_faterr_code is null
3338 then
3339 --
3340 -- Check for a BPP
3341 --
3342 if l_efc_row.bnft_prvdr_pool_id is null then
3343 --
3344 l_faterr_code := 'BPLBPPNULL';
3345 l_faterr_type := 'MISC';
3346 --
3347 end if;
3348 --
3349 /*
3350 --
3351 -- Check for a BPP
3352 --
3353 if nvl(l_ecrdets.decr_bnft_prvdr_pool_id,-999) <> l_efc_row.bnft_prvdr_pool_id
3354 and l_faterr_code is null
3355 then
3356 --
3357 l_faterr_code := 'NOECRBPP';
3358 l_faterr_type := 'MISC';
3359 --
3360 end if;
3361 */
3362 --
3363 if l_faterr_code is null then
3364 --
3365 open c_bppdets
3366 (c_bpp_id => l_efc_row.bnft_prvdr_pool_id
3367 ,c_eff_date => l_efc_row.lf_evt_ocrd_dt
3368 );
3369 fetch c_bppdets into l_bppdets;
3370 if c_bppdets%notfound then
3371 --
3372 l_faterr_code := 'NOBPLBPPDETS';
3373 l_faterr_type := 'MISC';
3374 --
3375 end if;
3376 close c_bppdets;
3377 --
3378 end if;
3379 --
3380 if l_faterr_code is null then
3381 --
3382 ben_provider_pools.compute_excess
3383 (p_calculate_only_mode => TRUE
3384 ,p_bnft_prvdr_pool_id => l_efc_row.bnft_prvdr_pool_id
3385 ,p_flex_rslt_id => l_efc_row.prtt_enrt_rslt_id
3386 ,p_person_id => l_efc_row.person_id
3387 ,p_per_in_ler_id => l_efc_row.per_in_ler_id
3388 ,p_enrt_mthd_cd => null
3389 ,p_effective_date => l_efc_row.lf_evt_ocrd_dt
3390 ,p_business_group_id => l_efc_row.business_group_id
3391 ,p_frftd_val => l_dummy_number
3392 ,p_def_exc_amount => l_dummy_number
3393 ,p_bpl_cash_recd_val => l_bpl_cash_recd_val
3394 );
3395 --
3396 end if;
3397 --
3398 end if;
3399 --
3400 if l_faterr_code is null then
3401 --
3402 if l_efc_row.used_val is not null
3403 and nvl(l_bpl_used_val,-999999999) <> l_efc_row.used_val
3404 then
3405 --
3406 l_adjfailed := TRUE;
3407 l_val_type := 'BPL_USEDVAL';
3408 l_old_val1 := l_efc_row.used_val;
3409 l_new_val1 := l_bpl_used_val;
3410 --
3411 elsif l_efc_row.prvdd_val is not null
3412 and nvl(l_bpl_prvdd_val,-999999999) <> l_efc_row.prvdd_val
3413 then
3414 --
3415 l_adjfailed := TRUE;
3416 l_val_type := 'BPL_PRVDDVAL';
3417 l_old_val1 := l_efc_row.prvdd_val;
3418 l_new_val1 := l_bpl_prvdd_val;
3419 --
3420 elsif l_efc_row.cash_recd_val is not null
3421 and nvl(l_bpl_cash_recd_val,-999999999) <> l_efc_row.cash_recd_val
3422 then
3423 --
3424 l_adjfailed := TRUE;
3425 l_val_type := 'BPL_CASHRECDVAL';
3426 l_old_val1 := l_efc_row.cash_recd_val;
3427 l_new_val1 := l_bpl_cash_recd_val;
3428 --
3429 elsif l_efc_row.frftd_val is not null
3430 and nvl(l_bpl_frftd_val,-999999999) <> l_efc_row.frftd_val
3431 then
3432 --
3433 l_adjfailed := TRUE;
3434 l_val_type := 'BPL_FRFTDVAL';
3435 l_old_val1 := l_efc_row.frftd_val;
3436 l_new_val1 := l_bpl_frftd_val;
3437 --
3438 elsif l_efc_row.rld_up_val is not null
3439 and nvl(l_bpl_rld_up_val,-999999999) <> l_efc_row.rld_up_val
3440 then
3441 --
3442 l_adjfailed := TRUE;
3443 l_val_type := 'BPL_RLDUPVAL';
3444 l_old_val1 := l_efc_row.rld_up_val;
3445 l_new_val1 := l_bpl_rld_up_val;
3446 --
3447 else
3448 --
3449 l_adjfailed := FALSE;
3450 --
3451 if l_efc_row.used_val is not null then
3452 --
3453 l_val_type := 'BPL_USEDVAL';
3454 l_old_val1 := l_efc_row.used_val;
3455 l_new_val1 := l_bpl_used_val;
3456 --
3457 elsif l_efc_row.prvdd_val is not null then
3458 --
3459 l_val_type := 'BPL_PRVDDVAL';
3460 l_old_val1 := l_efc_row.prvdd_val;
3461 l_new_val1 := l_bpl_prvdd_val;
3462 --
3463 elsif l_efc_row.cash_recd_val is not null then
3464 --
3465 l_val_type := 'BPL_CASHRECDVAL';
3466 l_old_val1 := l_efc_row.cash_recd_val;
3467 l_new_val1 := l_bpl_cash_recd_val;
3468 --
3469 elsif l_efc_row.frftd_val is not null then
3470 --
3471 l_val_type := 'BPL_FRFTDVAL';
3472 l_old_val1 := l_efc_row.frftd_val;
3473 l_new_val1 := l_bpl_frftd_val;
3474 --
3475 elsif l_efc_row.rld_up_val is not null then
3476 --
3477 l_val_type := 'BPL_RLDUPVAL';
3478 l_old_val1 := l_efc_row.rld_up_val;
3479 l_new_val1 := l_bpl_rld_up_val;
3480 --
3481 end if;
3482 --
3483 end if;
3484 --
3485 end if;
3486 --
3487 if l_faterr_code is null
3488 and l_adjfailed
3489 then
3490 --
3491 if l_efc_row.prtt_enrt_rslt_id is null
3492 and l_faterr_code is null
3493 then
3494 --
3495 l_faterr_code := 'NULLBPLPENID';
3496 l_faterr_type := 'POTENTIALCODEBUG';
3497 --
3498 end if;
3499 --
3500 if l_efc_row.bnft_prvdr_pool_id is null
3501 and l_faterr_code is null
3502 then
3503 --
3504 l_faterr_code := 'NULLBPLBPPID';
3505 l_faterr_type := 'POTENTIALCODEBUG';
3506 --
3507 end if;
3508 --
3509 if l_efc_row.effective_end_date <> hr_api.g_eot
3510 and l_faterr_code is null
3511 then
3512 --
3513 l_faterr_code := 'BPLDTUPD';
3514 l_faterr_type := 'UNSUPPORTTRANS';
3515 --
3516 end if;
3517 --
3518 -- Check rounding
3519 --
3520 if l_faterr_code is null then
3521 --
3522 ben_efc_adjustments.DetectRoundInfo
3523 (p_rndg_cd => null
3524 ,p_rndg_rl => null
3525 ,p_old_val => l_old_val1
3526 ,p_new_val => l_new_val1
3527 ,p_eff_date => l_efc_row.lf_evt_ocrd_dt
3528 --
3529 ,p_faterr_code => l_faterr_code
3530 ,p_faterr_type => l_faterr_type
3531 );
3532 --
3533 end if;
3534 --
3535 if l_faterr_code is null then
3536 --
3537 ben_efc_adjustments.DetectWhoInfo
3538 (p_creation_date => l_efc_row.creation_date
3539 ,p_last_update_date => l_efc_row.last_update_date
3540 ,p_object_version_number => l_efc_row.object_version_number
3541 --
3542 ,p_who_counts => l_who_counts
3543 ,p_faterr_code => l_faterr_code
3544 ,p_faterr_type => l_faterr_type
3545 );
3546 --
3547 end if;
3548 --
3549 if l_faterr_code is null then
3550 --
3551 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).id := l_efc_row.bnft_prvdd_ldgr_id;
3552 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).esd := l_efc_row.effective_start_date;
3553 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).eed := l_efc_row.effective_end_date;
3554 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).ovn := l_efc_row.object_version_number;
3555 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).credt := l_efc_row.creation_date;
3556 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
3557 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).old_val1 := l_old_val1;
3558 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).new_val1 := l_new_val1;
3559 ben_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).val_type := l_val_type;
3560 --
3561 l_calfail_count := l_calfail_count+1;
3562 --
3563 end if;
3564 --
3565 end if;
3566 --
3567 if l_efc_batch and l_faterr_code is null
3568 then
3569 --
3570 update ben_bnft_prvdd_ldgr_f bpl
3571 set bpl.used_val = l_bpl_used_val,
3572 bpl.prvdd_val = l_bpl_prvdd_val,
3573 bpl.cash_recd_val = l_bpl_cash_recd_val,
3574 bpl.frftd_val = l_bpl_frftd_val,
3575 bpl.rld_up_val = l_bpl_rld_up_val
3576 where bpl.bnft_prvdd_ldgr_id = l_efc_row.bnft_prvdd_ldgr_id
3577 and bpl.effective_start_date = l_efc_row.effective_start_date
3578 and bpl.effective_end_date = l_efc_row.effective_end_date;
3579 --
3580 if p_validate then
3581 --
3582 rollback;
3583 --
3584 end if;
3585 --
3586 -- Check for end of chunk and commit if necessary
3587 --
3588 l_pk1 := l_efc_row.bnft_prvdd_ldgr_id;
3589 --
3590 ben_efc_functions.maintain_chunks
3591 (p_row_count => l_chunkrow_count
3592 ,p_pk1 => l_pk1
3593 ,p_chunk_size => p_chunk
3594 ,p_efc_worker_id => p_efc_worker_id
3595 );
3596 --
3597 end if;
3598 --
3599 exception
3600 when others then
3601 --
3602 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).id := l_efc_row.bnft_prvdd_ldgr_id;
3603 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).esd := l_efc_row.effective_start_date;
3604 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).eed := l_efc_row.effective_end_date;
3605 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).credt := l_efc_row.creation_date;
3606 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
3607 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).rco_name := 'BENPSTCR';
3608 ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).sql_error := SQLERRM;
3609 --
3610 l_rcoerr_count := l_rcoerr_count+1;
3611 --
3612 end;
3613 --
3614 end if;
3615 --
3616 -- Check for fatal errors
3617 --
3618 if l_faterr_code is not null
3619 then
3620 --
3621 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).id := l_efc_row.bnft_prvdd_ldgr_id;
3622 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).esd := l_efc_row.effective_start_date;
3623 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).eed := l_efc_row.effective_end_date;
3624 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).val_type := l_val_type;
3625 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).old_val1 := l_old_val1;
3626 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).new_val1 := l_new_val1;
3627 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).faterr_code := l_faterr_code;
3628 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).faterr_type := l_faterr_type;
3629 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
3630 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).credt := l_efc_row.creation_date;
3631 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).ovn := l_efc_row.object_version_number;
3632 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).cre_by := l_efc_row.created_by;
3633 ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).lu_by := l_efc_row.last_updated_by;
3634 --
3635 l_faterrs_count := l_faterrs_count+1;
3636 --
3637 elsif l_faterr_code is null
3638 and not l_adjfailed
3639 then
3640 --
3641 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).id := l_efc_row.bnft_prvdd_ldgr_id;
3642 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).esd := l_efc_row.effective_start_date;
3643 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).eed := l_efc_row.effective_end_date;
3644 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).old_val1 := l_old_val1;
3645 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).new_val1 := l_new_val1;
3646 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).val_type := l_val_type;
3647 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).credt := l_efc_row.creation_date;
3648 ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
3649 --
3650 l_calsucc_count := l_calsucc_count+1;
3651 --
3652 end if;
3653 --
3654 l_row_count := l_row_count+1;
3655 --
3656 end loop;
3657 CLOSE c_efc_rows;
3658 --
3659 -- Check that all rows have been converted or excluded
3660 --
3661 ben_efc_functions.conv_check
3662 (p_table_name => 'ben_bnft_prvdd_ldgr_f'
3663 ,p_efctable_name => 'ben_bnft_prvdd_ldgr_f_efc'
3664 ,p_tabwhere_clause => ' (used_val is not null '
3665 ||' or FRFTD_VAL is not null '
3666 ||' or PRVDD_VAL is not null '
3667 ||' or RLD_UP_VAL is not null '
3668 ||' or CASH_RECD_VAL is not null '
3669 ||' ) '
3670 --
3671 ,p_action_id => p_action_id
3672 ,p_bgp_id => p_business_group_id
3673 --
3674 ,p_conv_count => l_conv_count
3675 ,p_unconv_count => l_unconv_count
3676 ,p_tabrow_count => l_tabrow_count
3677 );
3678 --
3679 -- Set counts
3680 --
3681 if p_action_id is null then
3682 --
3683 l_actconv_count := 0;
3684 --
3685 else
3686 --
3687 l_actconv_count := l_conv_count;
3688 --
3689 end if;
3690 --
3691 p_adjustment_counts.efcrow_count := l_row_count;
3692 p_adjustment_counts.tabrow_count := l_tabrow_count;
3693 p_adjustment_counts.calfail_count := l_calfail_count;
3694 p_adjustment_counts.calsucc_count := l_calsucc_count;
3695 p_adjustment_counts.rcoerr_count := l_rcoerr_count;
3696 --
3697 p_adjustment_counts.actconv_count := l_actconv_count;
3698 --
3699 end bpl_adjustments;
3700 --
3701 end ben_efc_adjustments1;