[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ELIGIBILITY3
Source
1 package body ben_determine_eligibility3 as
2 /* $Header: bendete3.pkb 120.4.12000000.1 2007/01/19 15:44:49 appldev noship $ */
3 --
4 -- -----------------------------------------------------
5 -- This procedure checks the designation requirements
6 -- for dependent eligibility and verifying family members
7 -- for participant eligibility.
8 -- -----------------------------------------------------
9 --
10 procedure check_dsgn_rqmts(p_oipl_id in number,
11 p_pl_id in number,
12 p_opt_id in number,
13 p_person_id in number,
14 p_business_group_id in number,
15 p_lf_evt_ocrd_dt in date,
16 p_effective_date in date,
17 p_vrfy_fmm in boolean,
18 p_dpnt_elig_flag out nocopy varchar2) is
19 --
20 l_proc varchar2(100):= 'ben_determine_eligibility3.check_dsgn_rqmts';
21 l_exists varchar2(30);
22 l_dpnt_elig_flag varchar2(1) := 'Y';
23 l_rlshp_count number := 0;
24 l_found_rows boolean := FALSE;
25 --FONM
26 l_fonm_cvg_strt_dt DATE ;
27 --END FONM
28 --
29 cursor c_dsgn is
30 select *
31 from ben_dsgn_rqmt_f ddr
32 where (ddr.oipl_id = p_oipl_id
33 or ddr.pl_id = p_pl_id
34 or ddr.opt_id = p_opt_id)
35 and ddr.dsgn_typ_cd = 'DPNT'
36 and ddr.business_group_id = p_business_group_id
37 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
38 between ddr.effective_start_date
39 and ddr.effective_end_date;
40 --
41 cursor c_get_rlshp_typ(p_dsgn_rqmt_id in number) is
42 select *
43 from ben_dsgn_rqmt_rlshp_typ drr
44 where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
45 and drr.business_group_id = p_business_group_id;
46 --
47 cursor c_dsgn_rl_typ(p_mn_dpnts_rqd_num in number
48 ,p_dsgn_rqmt_id in number) is
49 select null
50 from per_contact_relationships ctr,
51 per_all_people_f ppf
52 where ctr.person_id = p_person_id
53 and ctr.personal_flag = 'Y'
54 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt, p_effective_date))
55 between nvl(ctr.date_start, hr_api.g_sot)
56 and nvl(ctr.date_end, hr_api.g_eot)
57 and ctr.business_group_id = p_business_group_id
58 and ctr.contact_person_id = ppf.person_id
59 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt, p_effective_date))
60 between ppf.effective_start_date
61 and ppf.effective_end_date
62 and (ctr.contact_type in
63 (select rlshp_typ_cd drr
64 from ben_dsgn_rqmt_rlshp_typ drr
65 where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
66 and drr.business_group_id = p_business_group_id)
67 or not exists (select null
68 from ben_dsgn_rqmt_rlshp_typ drr
69 where drr.dsgn_rqmt_id = p_dsgn_rqmt_id
70 and drr.business_group_id = p_business_group_id))
71 having count(*) >= nvl(p_mn_dpnts_rqd_num, 0);
72 --
73 begin
74 --
75 hr_utility.set_location('Entering: '||l_proc,10);
76
77 hr_utility.set_location('p_oipl_id ' || p_oipl_id ,99 ) ;
78 hr_utility.set_location('p_pl_id ' || p_pl_id ,99 );
79 hr_utility.set_location('p_opt_id ' || p_opt_id , 99 ) ;
80 -- hr_utility.set_location('Entering ' || l_package,10);
81 --FONM
82 if ben_manage_life_events.fonm = 'Y'
83 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
84 --
85 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
86 --
87 end if;
88 --END FONM
89 --
90 -- Check if designation requirements are met.
91 -- The person must have the contact type(s)
92 -- and minimum number of contacts defined.
93 --
94 for l_ddr_rec in c_dsgn loop
95 --
96 if p_vrfy_fmm then
97 --
98 open c_dsgn_rl_typ(l_ddr_rec.mn_dpnts_rqd_num,
99 l_ddr_rec.dsgn_rqmt_id);
100 fetch c_dsgn_rl_typ into l_exists;
101 if c_dsgn_rl_typ%notfound then
102 close c_dsgn_rl_typ;
103 l_dpnt_elig_flag := 'N';
104 exit;
105 end if;
106 --
107 close c_dsgn_rl_typ;
108 --
109 else
110 --
111 -- Get all relationship types and check if the eligible
112 -- dependents meets the designation requirements.
113 --
114 l_rlshp_count := 0;
115 l_found_rows := FALSE;
116 --
117 for l_drr_rec in c_get_rlshp_typ(l_ddr_rec.dsgn_rqmt_id) loop
118 l_found_rows := TRUE;
119 --
120 -- The number of eligible dependents must have the right
121 -- relationship type;
122 --
123 if ben_determine_eligibility.g_elig_dpnt_rec.first is not null then
124 for l_counter in ben_determine_eligibility.g_elig_dpnt_rec.first..ben_determine_eligibility.g_elig_dpnt_rec.last loop
125 if ben_determine_eligibility.g_elig_dpnt_rec(l_counter).contact_type = l_drr_rec.rlshp_typ_cd then
126 l_rlshp_count := l_rlshp_count + 1;
127 end if;
128 --
129 end loop;
130 end if;
131 end loop;
132 --
133 hr_utility.set_location( ' count ' || l_rlshp_count , 99 );
134 -- If there are no relationship rows, then the number of dependents
135 -- must meet the minimum number required for the comp object.
136 --
137 if l_found_rows = false then
138 if ben_determine_eligibility.g_elig_dpnt_rec.count < nvl(l_ddr_rec.mn_dpnts_rqd_num,0) then
139 l_dpnt_elig_flag := 'N';
140 exit;
141 end if;
142 else
143 if l_rlshp_count < nvl(l_ddr_rec.mn_dpnts_rqd_num,0) then
144 l_dpnt_elig_flag := 'N';
145 exit;
146 end if;
147 --
148 end if;
149 --
150 end if;
151 --
152 end loop;
153 --
154 p_dpnt_elig_flag := l_dpnt_elig_flag;
155 --
156 hr_utility.set_location('Leaving: '||l_proc,20);
157 --
158 end check_dsgn_rqmts;
159 --
160 -- -----------------------------------------------------------------------------
161 -- |--------------------< get_prtn_st_dt_aftr_wtg >----------------------------|
162 -- -----------------------------------------------------------------------------
163 --
164 -- This function applies the prtn_eff_strt_dt_cd to the waiting period end date
165 -- and returns the computed date.
166 --
167 function get_prtn_st_dt_aftr_wtg
168 (p_person_id in number
169 ,p_effective_date in date
170 ,p_business_group_id in number
171 ,p_prtn_eff_strt_dt_cd in varchar2
172 ,p_prtn_eff_strt_dt_rl in number
173 ,p_wtg_perd_cmpltn_dt in date
174 ,p_pl_id in number
175 ,p_pl_typ_id in number
176 ,p_pgm_id in number
177 ,p_oipl_id in number
178 ,p_plip_id in number
179 ,p_ptip_id in number
180 ,p_opt_id in number
181 )
182 return date
183 is
184 --
185 l_return_date date;
186 l_outputs ff_exec.outputs_t;
187 --
188 l_proc varchar2(80) := 'ben_determine_eligibility3.get_prtn_st_dt_aftr_wtg';
189 l_ptip_rec ben_ptip_f%rowtype;
190 l_plip_rec ben_plip_f%rowtype;
191 l_ass_rec per_all_assignments_f%rowtype;
192 l_loc_rec hr_locations_all%rowtype;
193 l_pil_rec ben_per_in_ler%rowtype;
194 l_jurisdiction_code varchar2(30);
195 --
196 --FONM
197 l_fonm_cvg_strt_dt DATE ;
198 --END FONM
199
200 begin
201 --
202 g_debug := hr_utility.debug_enabled;
203 --
204 if g_debug then
205 hr_utility.set_location('Entering : ' || l_proc, 10);
206 end if;
207 --
208 if ben_manage_life_events.fonm = 'Y'
209 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
210 --
211 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
212 --
213 end if;
214 --
215 if p_plip_id is not null then
216 --
217 ben_comp_object.get_object(p_plip_id => p_plip_id,
218 p_rec => l_plip_rec);
219 --
220 elsif p_ptip_id is not null then
221 --
222 ben_comp_object.get_object(p_ptip_id => p_ptip_id,
223 p_rec => l_ptip_rec);
224 --
225 end if;
226 --
227 if p_prtn_eff_strt_dt_cd <> 'RL' then
228 --
229 -- Pass the wtg_perd_cmpltn_dt as the lf_evt_ocrd_dt to ben_determine_date
230 -- This will cause the calculation to be based on p_wtg_perd_cmpltn_dt.
231 --
232 ben_determine_date.main
233 (p_date_cd => p_prtn_eff_strt_dt_cd,
234 p_person_id => p_person_id,
235 p_pgm_id => nvl(p_pgm_id,l_ptip_rec.pgm_id),
236 p_pl_id => nvl(p_pl_id,l_plip_rec.pl_id),
237 p_oipl_id => p_oipl_id,
238 p_business_group_id => p_business_group_id,
239 p_lf_evt_ocrd_dt => p_wtg_perd_cmpltn_dt,
240 p_start_date => null,
241 p_effective_date => p_effective_date,
242 p_returned_date => l_return_date);
243 --
244 elsif p_prtn_eff_strt_dt_cd = 'RL' then
245 --
246 ben_person_object.get_object(p_person_id => p_person_id,
247 p_rec => l_ass_rec);
248 --
249 if l_ass_rec.assignment_id is null then
250 --
251 ben_person_object.get_benass_object(p_person_id => p_person_id,
252 p_rec => l_ass_rec);
253 --
254 end if;
255 --
256 ben_person_object.get_object(p_person_id => p_person_id,
257 p_rec => l_pil_rec);
258 --
259 if l_ass_rec.location_id is not null then
260 --
261 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
262 p_rec => l_loc_rec);
263 --
264 -- if l_loc_rec.region_2 is not null then
265 --
266 -- l_jurisdiction_code :=
267 -- pay_mag_utils.lookup_jurisdiction_code
268 -- (p_state => l_loc_rec.region_2);
269 --
270 -- end if;
271 --
272 end if;
273 --
274 -- Get the date from calling a fast formula rule
275 --
276 -- Rule = Participation Eligibility Start Date (ID = -82)
277 --
278 -- Call formula initialise routine
279 --
280 l_outputs := benutils.formula
281 (p_formula_id => p_prtn_eff_strt_dt_rl,
282 p_effective_date => p_effective_date,
283 p_business_group_id => p_business_group_id,
284 p_assignment_id => l_ass_rec.assignment_id,
285 p_organization_id => l_ass_rec.organization_id,
286 p_pl_id => nvl(p_pl_id,l_plip_rec.pl_id),
287 p_pl_typ_id => p_pl_typ_id,
288 p_pgm_id => nvl(p_pgm_id,l_ptip_rec.pgm_id),
289 p_opt_id => p_opt_id,
290 p_ler_id => l_pil_rec.ler_id,
291 p_param1 => 'BEN_IV_RT_STRT_DT',
292 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
293 p_param2 => 'BEN_IV_CVG_STRT_DT',
294 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt),
295 p_param3 => 'BEN_IV_PERSON_ID', -- Bug 5331889
296 p_param3_value => to_char(p_person_id),
297 p_jurisdiction_code => l_jurisdiction_code);
298 --
299 -- Formula will return a date but code defensively in case the
300 -- date can not be typecast.
301 --
302 begin
303 --
304 l_return_date := fnd_date.canonical_to_date
305 (l_outputs(l_outputs.first).value);
306 --
307 exception
308 --
309 when others then
310 --
311 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
312 fnd_message.set_token('RL','prtn_eff_strt_dt_rl');
313 fnd_message.set_token('PROC',l_proc);
314 raise ben_manage_life_events.g_record_error;
315 --
316 end;
317 --
318 end if;
319 --
320 if g_debug then
321 --
322 hr_utility.set_location('Leaving : ' || l_proc, 10);
323 --
324 end if;
325 --
326 return l_return_date;
327 --
328 end get_prtn_st_dt_aftr_wtg;
329 --
330 --
331 -- -----------------------------------------------------------------------------
332 -- |------------------------< save_to_restore >--------------------------------|
333 -- -----------------------------------------------------------------------------
334 --
335 procedure save_to_restore
336 (p_current_per_in_ler_id NUMBER,
337 p_per_in_ler_id NUMBER,
338 p_elig_per_id NUMBER,
339 p_elig_per_opt_id NUMBER,
340 p_effective_date DATE
341 )
342 IS
343 --
344 l_proc varchar2(100):='ben_determine_eligibility3.save_to_restore';
345 --
346 cursor c_le (v_per_in_ler_id number,
347 v_effective_date date) is
348 select 'W'
349 from ben_per_in_ler pil,
350 ben_ler_f ler
351 where pil.per_in_ler_id = v_per_in_ler_id
352 and ler.ler_id = pil.ler_id
353 and ler.typ_cd not in ('COMP', 'ABS', 'SCHEDDU', 'GSP', 'IREC') -- iRec
354 and v_effective_date
355 between ler.effective_start_date
356 and ler.effective_end_date ;
357 --
358 cursor c_upd_pep(v_elig_per_id number,
359 v_effective_date date,
360 v_per_in_ler_id number ) is
361 select *
362 from ben_elig_per_f
363 where elig_per_id = v_elig_per_id
364 and effective_start_date = v_effective_date
365 and nvl(per_in_ler_id , -1) = v_per_in_ler_id ;
366 --
367 cursor c_upd_epo(v_elig_per_opt_id number,
368 v_effective_date date,
369 v_per_in_ler_id number ) is
370 select *
371 from ben_elig_per_opt_f
372 where elig_per_opt_id = v_elig_per_opt_id
373 and nvl(per_in_ler_id , -1) = v_per_in_ler_id
374 and effective_start_date = v_effective_date;
375 --
376 l_dummy varchar2(30) := NULL ;
377 l_dummy2 varchar2(30) := NULL;
378 BEGIN
379 --
380 hr_utility.set_location('Entering '||l_proc,10);
381 hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,22);
382 hr_utility.set_location('p_current_per_in_ler_id'||p_current_per_in_ler_id,22);
383 hr_utility.set_location('p_elig_per_id'||p_elig_per_id,22);
384 hr_utility.set_location('p_elig_per_opt_id'||p_elig_per_opt_id,22);
385 hr_utility.set_location('p_effective_date '||p_effective_date,22);
386 --
387 open c_le(p_per_in_ler_id,p_effective_date);
388 fetch c_le into l_dummy;
389 close c_le;
390 --
391 IF NVL(l_dummy,'X') <> 'W' THEN
392 hr_utility.set_location('Not Required '||l_proc,15);
393 return ;
394 ELSE
395 open c_le(p_current_per_in_ler_id,p_effective_date);
396 fetch c_le into l_dummy2;
397 close c_le;
398 --
399 IF NVL(l_dummy2,'X') <> 'W' THEN
400 hr_utility.set_location('Not Required '||l_proc,15);
401 return ;
402 END IF;
403 --
404 END IF;
405 --
406 --BEN_ELIG_PER_F
407 IF p_elig_per_id IS NOT NULL THEN
408 --
409 FOR l_upd_pep_rec IN c_upd_pep(p_elig_per_id,
410 p_effective_date,
411 p_per_in_ler_id )
412 LOOP
413 --
414 hr_utility.set_location('Inserting into BEN_LE_CLSN_N_RSTR PEP: '||p_elig_per_id,20);
415 --
416 insert into BEN_LE_CLSN_N_RSTR (
417 PER_IN_LER_ENDED_ID,
418 BKUP_TBL_TYP_CD,
419 PLIP_ID,
420 PTIP_ID,
421 WAIT_PERD_CMPLTN_DT,
422 PER_IN_LER_ID,
423 RT_FRZ_PCT_FL_TM_FLAG,
424 RT_FRZ_HRS_WKD_FLAG,
425 RT_FRZ_COMB_AGE_AND_LOS_FLAG,
426 ONCE_R_CNTUG_CD,
427 BKUP_TBL_ID, -- ELIG_PER_ID,
428 EFFECTIVE_START_DATE,
429 EFFECTIVE_END_DATE,
430 BUSINESS_GROUP_ID,
431 PL_ID,
432 PGM_ID,
433 LER_ID,
434 PERSON_ID,
435 DPNT_OTHR_PL_CVRD_RL_FLAG,
436 PRTN_OVRIDN_THRU_DT,
437 PL_KEY_EE_FLAG,
438 PL_HGHLY_COMPD_FLAG,
439 ELIG_FLAG,
440 COMP_REF_AMT,
441 CMBN_AGE_N_LOS_VAL,
442 COMP_REF_UOM,
443 AGE_VAL,
444 LOS_VAL,
445 PRTN_END_DT,
446 PRTN_STRT_DT,
447 WV_CTFN_TYP_CD,
448 HRS_WKD_VAL,
449 HRS_WKD_BNDRY_PERD_CD,
450 PRTN_OVRIDN_FLAG,
451 NO_MX_PRTN_OVRID_THRU_FLAG,
452 PRTN_OVRIDN_RSN_CD,
453 AGE_UOM,
454 LOS_UOM,
455 OVRID_SVC_DT,
456 FRZ_LOS_FLAG,
457 FRZ_AGE_FLAG,
458 FRZ_CMP_LVL_FLAG,
459 FRZ_PCT_FL_TM_FLAG,
460 FRZ_HRS_WKD_FLAG,
461 FRZ_COMB_AGE_AND_LOS_FLAG,
462 DSTR_RSTCN_FLAG,
463 PCT_FL_TM_VAL,
464 WV_PRTN_RSN_CD,
465 PL_WVD_FLAG,
466 LCR_ATTRIBUTE_CATEGORY,
467 LCR_ATTRIBUTE1,
468 LCR_ATTRIBUTE2,
469 LCR_ATTRIBUTE3,
470 LCR_ATTRIBUTE4,
471 LCR_ATTRIBUTE5,
472 LCR_ATTRIBUTE6,
473 LCR_ATTRIBUTE7,
474 LCR_ATTRIBUTE8,
475 LCR_ATTRIBUTE9,
476 LCR_ATTRIBUTE10,
477 LCR_ATTRIBUTE11,
478 LCR_ATTRIBUTE12,
479 LCR_ATTRIBUTE13,
480 LCR_ATTRIBUTE14,
481 LCR_ATTRIBUTE15,
482 LCR_ATTRIBUTE16,
483 LCR_ATTRIBUTE17,
484 LCR_ATTRIBUTE18,
485 LCR_ATTRIBUTE19,
486 LCR_ATTRIBUTE20,
487 LCR_ATTRIBUTE21,
488 LCR_ATTRIBUTE22,
489 LCR_ATTRIBUTE23,
490 LCR_ATTRIBUTE24,
491 LCR_ATTRIBUTE25,
492 LCR_ATTRIBUTE26,
493 LCR_ATTRIBUTE27,
494 LCR_ATTRIBUTE28,
495 LCR_ATTRIBUTE29,
496 LCR_ATTRIBUTE30,
497 LAST_UPDATE_DATE,
498 LAST_UPDATED_BY,
499 LAST_UPDATE_LOGIN,
500 CREATED_BY,
501 CREATION_DATE,
502 REQUEST_ID,
503 PROGRAM_APPLICATION_ID,
504 PROGRAM_ID,
505 PROGRAM_UPDATE_DATE,
506 OBJECT_VERSION_NUMBER,
507 MUST_ENRL_ANTHR_PL_ID,
508 RT_COMP_REF_AMT,
509 RT_CMBN_AGE_N_LOS_VAL,
510 RT_COMP_REF_UOM,
511 RT_AGE_VAL,
512 RT_LOS_VAL,
513 RT_HRS_WKD_VAL,
514 RT_HRS_WKD_BNDRY_PERD_CD,
515 RT_AGE_UOM,
516 RT_LOS_UOM,
517 RT_PCT_FL_TM_VAL,
518 RT_FRZ_LOS_FLAG,
519 RT_FRZ_AGE_FLAG,
520 RT_FRZ_CMP_LVL_FLAG,
521 INELG_RSN_CD,
522 PL_ORDR_NUM,
523 PLIP_ORDR_NUM,
524 PTIP_ORDR_NUM )
525 values (
526 p_current_per_in_ler_id,
527 'BEN_ELIG_PER_F_CORRECT',
528 l_upd_pep_rec.PLIP_ID,
529 l_upd_pep_rec.PTIP_ID,
530 l_upd_pep_rec.WAIT_PERD_CMPLTN_DT,
531 l_upd_pep_rec.PER_IN_LER_ID,
532 l_upd_pep_rec.RT_FRZ_PCT_FL_TM_FLAG,
533 l_upd_pep_rec.RT_FRZ_HRS_WKD_FLAG,
534 l_upd_pep_rec.RT_FRZ_COMB_AGE_AND_LOS_FLAG,
535 l_upd_pep_rec.ONCE_R_CNTUG_CD,
536 l_upd_pep_rec.ELIG_PER_ID,
537 l_upd_pep_rec.EFFECTIVE_START_DATE,
538 l_upd_pep_rec.EFFECTIVE_END_DATE,
539 l_upd_pep_rec.BUSINESS_GROUP_ID,
540 l_upd_pep_rec.PL_ID,
541 l_upd_pep_rec.PGM_ID,
542 l_upd_pep_rec.LER_ID,
543 l_upd_pep_rec.PERSON_ID,
544 l_upd_pep_rec.DPNT_OTHR_PL_CVRD_RL_FLAG,
545 l_upd_pep_rec.PRTN_OVRIDN_THRU_DT,
546 l_upd_pep_rec.PL_KEY_EE_FLAG,
547 l_upd_pep_rec.PL_HGHLY_COMPD_FLAG,
548 l_upd_pep_rec.ELIG_FLAG,
549 l_upd_pep_rec.COMP_REF_AMT,
550 l_upd_pep_rec.CMBN_AGE_N_LOS_VAL,
551 l_upd_pep_rec.COMP_REF_UOM,
552 l_upd_pep_rec.AGE_VAL,
553 l_upd_pep_rec.LOS_VAL,
554 l_upd_pep_rec.PRTN_END_DT,
555 l_upd_pep_rec.PRTN_STRT_DT,
556 l_upd_pep_rec.WV_CTFN_TYP_CD,
557 l_upd_pep_rec.HRS_WKD_VAL,
558 l_upd_pep_rec.HRS_WKD_BNDRY_PERD_CD,
559 l_upd_pep_rec.PRTN_OVRIDN_FLAG,
560 l_upd_pep_rec.NO_MX_PRTN_OVRID_THRU_FLAG,
561 l_upd_pep_rec.PRTN_OVRIDN_RSN_CD,
562 l_upd_pep_rec.AGE_UOM,
563 l_upd_pep_rec.LOS_UOM,
564 l_upd_pep_rec.OVRID_SVC_DT,
565 l_upd_pep_rec.FRZ_LOS_FLAG,
566 l_upd_pep_rec.FRZ_AGE_FLAG,
567 l_upd_pep_rec.FRZ_CMP_LVL_FLAG,
568 l_upd_pep_rec.FRZ_PCT_FL_TM_FLAG,
569 l_upd_pep_rec.FRZ_HRS_WKD_FLAG,
570 l_upd_pep_rec.FRZ_COMB_AGE_AND_LOS_FLAG,
571 l_upd_pep_rec.DSTR_RSTCN_FLAG,
572 l_upd_pep_rec.PCT_FL_TM_VAL,
573 l_upd_pep_rec.WV_PRTN_RSN_CD,
574 l_upd_pep_rec.PL_WVD_FLAG,
575 l_upd_pep_rec.PEP_ATTRIBUTE_CATEGORY,
576 l_upd_pep_rec.PEP_ATTRIBUTE1,
577 l_upd_pep_rec.PEP_ATTRIBUTE2,
578 l_upd_pep_rec.PEP_ATTRIBUTE3,
579 l_upd_pep_rec.PEP_ATTRIBUTE4,
580 l_upd_pep_rec.PEP_ATTRIBUTE5,
581 l_upd_pep_rec.PEP_ATTRIBUTE6,
582 l_upd_pep_rec.PEP_ATTRIBUTE7,
583 l_upd_pep_rec.PEP_ATTRIBUTE8,
584 l_upd_pep_rec.PEP_ATTRIBUTE9,
585 l_upd_pep_rec.PEP_ATTRIBUTE10,
586 l_upd_pep_rec.PEP_ATTRIBUTE11,
587 l_upd_pep_rec.PEP_ATTRIBUTE12,
588 l_upd_pep_rec.PEP_ATTRIBUTE13,
589 l_upd_pep_rec.PEP_ATTRIBUTE14,
590 l_upd_pep_rec.PEP_ATTRIBUTE15,
591 l_upd_pep_rec.PEP_ATTRIBUTE16,
592 l_upd_pep_rec.PEP_ATTRIBUTE17,
593 l_upd_pep_rec.PEP_ATTRIBUTE18,
594 l_upd_pep_rec.PEP_ATTRIBUTE19,
595 l_upd_pep_rec.PEP_ATTRIBUTE20,
596 l_upd_pep_rec.PEP_ATTRIBUTE21,
597 l_upd_pep_rec.PEP_ATTRIBUTE22,
598 l_upd_pep_rec.PEP_ATTRIBUTE23,
599 l_upd_pep_rec.PEP_ATTRIBUTE24,
600 l_upd_pep_rec.PEP_ATTRIBUTE25,
601 l_upd_pep_rec.PEP_ATTRIBUTE26,
602 l_upd_pep_rec.PEP_ATTRIBUTE27,
603 l_upd_pep_rec.PEP_ATTRIBUTE28,
604 l_upd_pep_rec.PEP_ATTRIBUTE29,
605 l_upd_pep_rec.PEP_ATTRIBUTE30,
606 l_upd_pep_rec.LAST_UPDATE_DATE,
607 l_upd_pep_rec.LAST_UPDATED_BY,
608 l_upd_pep_rec.LAST_UPDATE_LOGIN,
609 l_upd_pep_rec.CREATED_BY,
610 l_upd_pep_rec.CREATION_DATE,
611 l_upd_pep_rec.REQUEST_ID,
612 l_upd_pep_rec.PROGRAM_APPLICATION_ID,
613 l_upd_pep_rec.PROGRAM_ID,
614 l_upd_pep_rec.PROGRAM_UPDATE_DATE,
615 l_upd_pep_rec.OBJECT_VERSION_NUMBER,
616 l_upd_pep_rec.MUST_ENRL_ANTHR_PL_ID,
617 l_upd_pep_rec.RT_COMP_REF_AMT,
618 l_upd_pep_rec.RT_CMBN_AGE_N_LOS_VAL,
619 l_upd_pep_rec.RT_COMP_REF_UOM,
620 l_upd_pep_rec.RT_AGE_VAL,
621 l_upd_pep_rec.RT_LOS_VAL,
622 l_upd_pep_rec.RT_HRS_WKD_VAL,
623 l_upd_pep_rec.RT_HRS_WKD_BNDRY_PERD_CD,
624 l_upd_pep_rec.RT_AGE_UOM,
625 l_upd_pep_rec.RT_LOS_UOM,
626 l_upd_pep_rec.RT_PCT_FL_TM_VAL,
627 l_upd_pep_rec.RT_FRZ_LOS_FLAG,
628 l_upd_pep_rec.RT_FRZ_AGE_FLAG,
629 l_upd_pep_rec.RT_FRZ_CMP_LVL_FLAG,
630 l_upd_pep_rec.INELG_RSN_CD,
631 l_upd_pep_rec.PL_ORDR_NUM,
632 l_upd_pep_rec.PLIP_ORDR_NUM,
633 l_upd_pep_rec.PTIP_ORDR_NUM
634 );
635 --
636 END LOOP;
637 END IF;
638 --
639 --BEN_ELIG_PER_F
640 IF p_elig_per_opt_id IS NOT NULL THEN
641 --
642 FOR l_upd_epo_rec IN c_upd_epo(p_elig_per_opt_id,
643 p_effective_date,
644 p_per_in_ler_id )
645 LOOP
646 --
647 hr_utility.set_location('Inserting into BEN_LE_CLSN_N_RSTR EPO: '||p_elig_per_opt_id,20);
648 --
649 insert into BEN_LE_CLSN_N_RSTR (
650 PER_IN_LER_ENDED_ID,
651 BKUP_TBL_TYP_CD,
652 INELG_RSN_CD,
653 PER_IN_LER_ID,
654 AGE_UOM,
655 LOS_UOM,
656 FRZ_LOS_FLAG,
657 FRZ_AGE_FLAG,
658 FRZ_CMP_LVL_FLAG,
659 FRZ_PCT_FL_TM_FLAG,
660 FRZ_HRS_WKD_FLAG,
661 FRZ_COMB_AGE_AND_LOS_FLAG,
662 OVRID_SVC_DT,
663 WAIT_PERD_CMPLTN_DT,
664 COMP_REF_AMT,
665 CMBN_AGE_N_LOS_VAL,
666 COMP_REF_UOM,
667 AGE_VAL,
668 LOS_VAL,
669 HRS_WKD_VAL,
670 HRS_WKD_BNDRY_PERD_CD,
671 RT_COMP_REF_AMT,
672 RT_CMBN_AGE_N_LOS_VAL,
673 RT_COMP_REF_UOM,
674 RT_AGE_VAL,
675 RT_LOS_VAL,
676 RT_HRS_WKD_VAL,
677 RT_HRS_WKD_BNDRY_PERD_CD,
678 RT_AGE_UOM,
679 RT_LOS_UOM,
680 RT_PCT_FL_TM_VAL,
681 RT_FRZ_LOS_FLAG,
682 RT_FRZ_AGE_FLAG,
683 RT_FRZ_CMP_LVL_FLAG,
684 RT_FRZ_PCT_FL_TM_FLAG,
685 RT_FRZ_HRS_WKD_FLAG,
686 RT_FRZ_COMB_AGE_AND_LOS_FLAG,
687 BKUP_TBL_ID, -- ELIG_PER_OPT_ID,
688 ELIG_PER_ID,
689 EFFECTIVE_START_DATE,
690 EFFECTIVE_END_DATE,
691 PRTN_OVRIDN_FLAG,
692 PRTN_OVRIDN_THRU_DT,
693 NO_MX_PRTN_OVRID_THRU_FLAG,
694 ELIG_FLAG,
695 PRTN_STRT_DT,
696 PRTN_OVRIDN_RSN_CD,
697 PCT_FL_TM_VAL,
698 OPT_ID,
699 BUSINESS_GROUP_ID,
700 LCR_ATTRIBUTE_CATEGORY,
701 LCR_ATTRIBUTE1,
702 LCR_ATTRIBUTE2,
703 LCR_ATTRIBUTE3,
704 LCR_ATTRIBUTE4,
705 LCR_ATTRIBUTE5,
706 LCR_ATTRIBUTE6,
707 LCR_ATTRIBUTE7,
708 LCR_ATTRIBUTE8,
709 LCR_ATTRIBUTE9,
710 LCR_ATTRIBUTE10,
711 LCR_ATTRIBUTE11,
712 LCR_ATTRIBUTE12,
713 LCR_ATTRIBUTE13,
714 LCR_ATTRIBUTE14,
715 LCR_ATTRIBUTE15,
716 LCR_ATTRIBUTE16,
717 LCR_ATTRIBUTE17,
718 LCR_ATTRIBUTE18,
719 LCR_ATTRIBUTE19,
720 LCR_ATTRIBUTE20,
721 LCR_ATTRIBUTE21,
722 LCR_ATTRIBUTE22,
723 LCR_ATTRIBUTE23,
724 LCR_ATTRIBUTE24,
725 LCR_ATTRIBUTE25,
726 LCR_ATTRIBUTE26,
727 LCR_ATTRIBUTE27,
728 LCR_ATTRIBUTE28,
729 LCR_ATTRIBUTE29,
730 LCR_ATTRIBUTE30,
731 LAST_UPDATE_DATE,
732 LAST_UPDATED_BY,
733 LAST_UPDATE_LOGIN,
734 CREATED_BY,
735 CREATION_DATE,
736 REQUEST_ID,
737 PROGRAM_APPLICATION_ID,
738 PROGRAM_ID,
739 PROGRAM_UPDATE_DATE,
740 OBJECT_VERSION_NUMBER,
741 ONCE_R_CNTUG_CD,
742 OIPL_ORDR_NUM,
743 PRTN_END_DT )
744 values (
745 p_current_per_in_ler_id,
746 'BEN_ELIG_PER_OPT_F_CORRECT',
747 l_upd_epo_rec.INELG_RSN_CD,
748 l_upd_epo_rec.PER_IN_LER_ID,
749 l_upd_epo_rec.AGE_UOM,
750 l_upd_epo_rec.LOS_UOM,
751 l_upd_epo_rec.FRZ_LOS_FLAG,
752 l_upd_epo_rec.FRZ_AGE_FLAG,
753 l_upd_epo_rec.FRZ_CMP_LVL_FLAG,
754 l_upd_epo_rec.FRZ_PCT_FL_TM_FLAG,
755 l_upd_epo_rec.FRZ_HRS_WKD_FLAG,
756 l_upd_epo_rec.FRZ_COMB_AGE_AND_LOS_FLAG,
757 l_upd_epo_rec.OVRID_SVC_DT,
758 -- l_upd_epo_rec.WAIT_PERD_CMPLTN_DT,
759 l_upd_epo_rec.WAIT_PERD_CMPLTN_DATE,
760 l_upd_epo_rec.COMP_REF_AMT,
761 l_upd_epo_rec.CMBN_AGE_N_LOS_VAL,
762 l_upd_epo_rec.COMP_REF_UOM,
763 l_upd_epo_rec.AGE_VAL,
764 l_upd_epo_rec.LOS_VAL,
765 l_upd_epo_rec.HRS_WKD_VAL,
766 l_upd_epo_rec.HRS_WKD_BNDRY_PERD_CD,
767 l_upd_epo_rec.RT_COMP_REF_AMT,
768 l_upd_epo_rec.RT_CMBN_AGE_N_LOS_VAL,
769 l_upd_epo_rec.RT_COMP_REF_UOM,
770 l_upd_epo_rec.RT_AGE_VAL,
771 l_upd_epo_rec.RT_LOS_VAL,
772 l_upd_epo_rec.RT_HRS_WKD_VAL,
773 l_upd_epo_rec.RT_HRS_WKD_BNDRY_PERD_CD,
774 l_upd_epo_rec.RT_AGE_UOM,
775 l_upd_epo_rec.RT_LOS_UOM,
776 l_upd_epo_rec.RT_PCT_FL_TM_VAL,
777 l_upd_epo_rec.RT_FRZ_LOS_FLAG,
778 l_upd_epo_rec.RT_FRZ_AGE_FLAG,
779 l_upd_epo_rec.RT_FRZ_CMP_LVL_FLAG,
780 l_upd_epo_rec.RT_FRZ_PCT_FL_TM_FLAG,
781 l_upd_epo_rec.RT_FRZ_HRS_WKD_FLAG,
782 l_upd_epo_rec.RT_FRZ_COMB_AGE_AND_LOS_FLAG,
783 l_upd_epo_rec.ELIG_PER_OPT_ID,
784 l_upd_epo_rec.ELIG_PER_ID,
785 l_upd_epo_rec.EFFECTIVE_START_DATE,
786 l_upd_epo_rec.EFFECTIVE_END_DATE,
787 l_upd_epo_rec.PRTN_OVRIDN_FLAG,
788 l_upd_epo_rec.PRTN_OVRIDN_THRU_DT,
789 l_upd_epo_rec.NO_MX_PRTN_OVRID_THRU_FLAG,
790 l_upd_epo_rec.ELIG_FLAG,
791 l_upd_epo_rec.PRTN_STRT_DT,
792 l_upd_epo_rec.PRTN_OVRIDN_RSN_CD,
793 l_upd_epo_rec.PCT_FL_TM_VAL,
794 l_upd_epo_rec.OPT_ID,
795 l_upd_epo_rec.BUSINESS_GROUP_ID,
796 l_upd_epo_rec.EPO_ATTRIBUTE_CATEGORY,
797 l_upd_epo_rec.EPO_ATTRIBUTE1,
798 l_upd_epo_rec.EPO_ATTRIBUTE2,
799 l_upd_epo_rec.EPO_ATTRIBUTE3,
800 l_upd_epo_rec.EPO_ATTRIBUTE4,
801 l_upd_epo_rec.EPO_ATTRIBUTE5,
802 l_upd_epo_rec.EPO_ATTRIBUTE6,
803 l_upd_epo_rec.EPO_ATTRIBUTE7,
804 l_upd_epo_rec.EPO_ATTRIBUTE8,
805 l_upd_epo_rec.EPO_ATTRIBUTE9,
806 l_upd_epo_rec.EPO_ATTRIBUTE10,
807 l_upd_epo_rec.EPO_ATTRIBUTE11,
808 l_upd_epo_rec.EPO_ATTRIBUTE12,
809 l_upd_epo_rec.EPO_ATTRIBUTE13,
810 l_upd_epo_rec.EPO_ATTRIBUTE14,
811 l_upd_epo_rec.EPO_ATTRIBUTE15,
812 l_upd_epo_rec.EPO_ATTRIBUTE16,
813 l_upd_epo_rec.EPO_ATTRIBUTE17,
814 l_upd_epo_rec.EPO_ATTRIBUTE18,
815 l_upd_epo_rec.EPO_ATTRIBUTE19,
816 l_upd_epo_rec.EPO_ATTRIBUTE20,
817 l_upd_epo_rec.EPO_ATTRIBUTE21,
818 l_upd_epo_rec.EPO_ATTRIBUTE22,
819 l_upd_epo_rec.EPO_ATTRIBUTE23,
820 l_upd_epo_rec.EPO_ATTRIBUTE24,
821 l_upd_epo_rec.EPO_ATTRIBUTE25,
822 l_upd_epo_rec.EPO_ATTRIBUTE26,
823 l_upd_epo_rec.EPO_ATTRIBUTE27,
824 l_upd_epo_rec.EPO_ATTRIBUTE28,
825 l_upd_epo_rec.EPO_ATTRIBUTE29,
826 l_upd_epo_rec.EPO_ATTRIBUTE30,
827 l_upd_epo_rec.LAST_UPDATE_DATE,
828 l_upd_epo_rec.LAST_UPDATED_BY,
829 l_upd_epo_rec.LAST_UPDATE_LOGIN,
830 l_upd_epo_rec.CREATED_BY,
831 l_upd_epo_rec.CREATION_DATE,
832 l_upd_epo_rec.REQUEST_ID,
833 l_upd_epo_rec.PROGRAM_APPLICATION_ID,
834 l_upd_epo_rec.PROGRAM_ID,
835 l_upd_epo_rec.PROGRAM_UPDATE_DATE,
836 l_upd_epo_rec.OBJECT_VERSION_NUMBER,
837 l_upd_epo_rec.ONCE_R_CNTUG_CD,
838 l_upd_epo_rec.OIPL_ORDR_NUM,
839 l_upd_epo_rec.PRTN_END_DT
840 );
841 --
842 END LOOP;
843 END IF;
844 --
845 hr_utility.set_location('Leaving '||l_proc,10);
846 END save_to_restore ;
847 end ben_determine_eligibility3;