[Home] [Help]
PACKAGE BODY: APPS.BEN_MANAGE_UNRES_LIFE_EVENTS
Source
1 package body ben_manage_unres_life_events as
2 /* $Header: bebmures.pkb 120.9 2006/11/29 05:49:26 gsehgal noship $ */
3 --
4 /*
5 +========================================================================+
6 | Copyright (c) 2002 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +========================================================================+
10 */
11 /*
12 Date Who Version What?
13 ---- --- ------- -----
14 08 Feb 02 mhoyes 115.0 Created.
15 04 Apr 02 kmahendr 115.1 Bug#2090319 - added a cursor in update_in_pend_flag
16 to update in_pending_flag to N before update to Y
17 08 Apr 02 kmahendr 115.2 Bug#2187697 - update in_pndg_wkflow_flag to
18 S so that rates are not calculated again if
19 there is any comp. object in pending or suspend
20 status.
21 16 Dec 02 hnarayan 115.3 Added NOCOPY hint
22 02-Jul-03 maagrawa 115.4 Modified c_pending cursor for performance.
23 Bug 3026837.
24 23-Sep-03 pbodla
25 kmahendr 115.5 3152322 : Modified
26 c_elig_per_elctbl_chc_3 to avoid
27 selecting pending in wf rows.
28 29 hnarayan 115.3 Added NOCOPY hint
29 30 Sep 03 mmudigon 115.6 bug 3170345 - Performance changes. Bulk
30 binding in procedure
31 delete_elctbl_choice.
32 09 Oct 03 kmahendr 115.7 Added cursor c_pl_nip to bypass
33 update_pending flag for performance
34 Added table count before deletes.
35 14 Oct 03 kmahendr 115.8 Removed c_enrt_rt cursor as the
36 data is not used at all.
37 30 Oct 03 mmudigon 115.9 Close c_elig_per_elctbl_chc_3
38 25 May 04 bmanyam 115.10 Bug 3638279: Added OVN to column list in the
39 cursor c_sspndd_enrts
40 27 Jul 04 nhunur 115.11 delete elig_dpnt for unrestricted as epe is deleted.
41 15 Nov 04 kmahendr 115.12 Unrest. enh changes
42 01-dec-04 kmahendr 115.13 Unrest. enh changes
43 09-dec-04 kmahendr 115.14 Bug#4056365 - enrt_rslt_id on epe updated
44 23-Sep-05 maagrawa 115.15 Bug 4483353. Remove hardcoding of
45 item type for ICD transactions.
46 06-Oct-05 rbingi 115.16 Bug#4640014. added proc clear_epe_cache to
47 delete EPEs that marked delete
48 05-dec-05 ssarkar 115.17 4761065 :Modified proc clear_epe_cache - Need to delete epe regardless
49 whether its elctbl_flag is Y/N
50 18-Jan-05 rbingi 115.18 4717052 : Not updating epe when already marked delete 'Y'
51 returning from the proc.
52 04-APR-06 ssarkar 115.19 Bug 5055119 - added end_date_elig_per_rows
53 10-apr-06 ssarkar 115.20 5055119 - redesigned the code of end_date_elig_per_rows
54 29-Nov-06 gsehgal 115.21 Bug 5658405 - modified procedure end_date_elig_per_rows
55 */
56
57 --------------------------------------------------------------------------------
58 --
59 g_package varchar2(80) := 'ben_manage_unres_life_events';
60 --
61 procedure update_in_pend_flag
62 (p_person_id in number
63 ,p_per_in_ler_id in number
64 ,p_business_group_id in number
65 ,p_effective_date in date
66 )
67 is
68 --
69 l_pending number;
70 l_package varchar2(100) := 'update_in_pend_flag';
71 --
72 cursor c_pending is
73 SELECT vlv_choice.number_value elig_per_elctbl_chc_id
74 from wf_item_activity_statuses process ,
75 wf_process_activities activity ,
76 hr_api_transactions txn,
77 hr_api_transaction_steps step ,
78 hr_api_transaction_values vlv_choice
79 WHERE activity.process_name = 'ROOT'
80 and activity.process_item_type = activity.activity_item_type
81 and activity.instance_id = process.process_activity
82 and process.activity_status = 'ACTIVE'
83 and txn.item_type = process.item_type
84 and txn.item_key = process.item_key
85 and txn.selected_person_id = p_person_id
86 and txn.transaction_id = step.transaction_id
87 and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API'
88 and step.transaction_step_id = vlv_choice.transaction_step_id
89 and vlv_choice.name = 'P_ELIG_PER_ELCTBL_CHC_ID';
90 --
91 /*
92 -- Bug : 1894718
93 Also treat the electable choices associated with suspended
94 enrollments as in pending work flow. In other words do not
95 delete the electable choises data and other data if the
96 enrollment is in suspended state.
97 Temporarily mark the in_pndg_wkflow_flag of electable choices
98 with 'Z' value and set the flag back to 'N' after deleting
99 the electable choice data and other data.
100 Conditions which may cause problems. :
101 1. Make enrollments on 10-oct-01 and come back and run
102 unrestricted on 10-may-01. How to deal with future
103 suspended rows.
104 2.
105 --
106 */
107 cursor c_sspndd_enrts is
108 select epe.elig_per_elctbl_chc_id, epe.object_version_number -- 3638279: Added ovn;
109 from ben_elig_per_elctbl_chc epe,
110 ben_prtt_enrt_rslt_f pen
111 where pen.person_id = p_person_id
112 and pen.per_in_ler_id = p_per_in_ler_id
113 and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
114 and pen.business_group_id = p_business_group_id
115 and pen.business_group_id = epe.business_group_id
116 and pen.sspndd_flag = 'Y'
117 and nvl(epe.in_pndg_wkflow_flag,'N') = 'N'
118 and pen.prtt_enrt_rslt_stat_cd IS NULL
119 and pen.effective_end_date = hr_api.g_eot
120 and pen.enrt_cvg_thru_dt = hr_api.g_eot;
121 /* need to worry about the following conditions
122 and p_effective_date
123 between enrt_cvg_strt_dt
124 and enrt_cvg_thru_dt
125 and enrt_cvg_thru_dt <= effective_end_date
126 and p_effective_date between effective_start_date and effective_end_date
127 */
128 --
129 cursor c_elig_per_elctbl_chc is
130 select object_version_number
131 from ben_elig_per_elctbl_chc
132 where elig_per_elctbl_chc_id = l_pending;
133 --
134 cursor c_elig_per_elctbl_chc_2 is
135 select elig_per_elctbl_chc_id,
136 object_version_number
137 from ben_elig_per_elctbl_chc
138 where per_in_ler_id = p_per_in_ler_id
139 and in_pndg_wkflow_flag = 'Y';
140 --
141 cursor c_elig_per_elctbl_chc_3 is
142 select elig_per_elctbl_chc_id,
143 object_version_number
144 from ben_elig_per_elctbl_chc
145 where per_in_ler_id = p_per_in_ler_id
146 and in_pndg_wkflow_flag <> 'Y'
147 and pil_elctbl_chc_popl_id in
148 (select pil_elctbl_chc_popl_id
149 from ben_pil_elctbl_chc_popl pel
150 where pel.per_in_ler_id = p_per_in_ler_id and
151 exists
152 ( select null
153 from ben_elig_per_elctbl_chc epe
154 where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
155 and nvl(epe.in_pndg_wkflow_flag,'N') in ('Y', 'S') ));
156 --
157 l_object_version_number number;
158 l_elig_per_elctbl_chc_id number;
159 --
160 begin
161 --
162 hr_utility.set_location ('Entering '||l_package,10);
163 -- need to update the pending flag to 'N' as the flag is not being reset by process
164 --after unpending
165 open c_elig_per_elctbl_chc_2;
166 loop
167 fetch c_elig_per_elctbl_chc_2 into
168 l_elig_per_elctbl_chc_id, l_object_version_number;
169 if c_elig_per_elctbl_chc_2%notfound then
170 exit;
171 end if;
172 ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
173 (p_validate => FALSE
174 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
175 ,p_object_version_number => l_object_version_number
176 ,p_in_pndg_wkflow_flag => 'N'
177 ,p_effective_date => p_effective_date
178 );
179 end loop;
180 --
181 close c_elig_per_elctbl_chc_2;
182 --
183 open c_pending;
184 loop
185 --
186 fetch c_pending into l_pending;
187 if c_pending%notfound then
188 exit;
189 end if;
190 --
191 if l_pending is not null then
192 --
193 open c_elig_per_elctbl_chc;
194 fetch c_elig_per_elctbl_chc into l_object_version_number;
195 --
196 if c_elig_per_elctbl_chc%found then
197 ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
198 (p_validate => FALSE
199 ,p_elig_per_elctbl_chc_id => l_pending
200 ,p_object_version_number => l_object_version_number
201 ,p_in_pndg_wkflow_flag => 'Y'
202 ,p_effective_date => p_effective_date);
203 end if;
204 close c_elig_per_elctbl_chc;
205 end if;
206 --
207 end loop;
208 --
209 close c_pending;
210 --
211 for l_epe_rec in c_sspndd_enrts loop
212 --
213 --
214 ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
215 (p_validate => FALSE
216 ,p_elig_per_elctbl_chc_id => l_epe_rec.elig_per_elctbl_chc_id
217 ,p_object_version_number => l_epe_rec.object_version_number
218 ,p_in_pndg_wkflow_flag => 'S'
219 ,p_effective_date => p_effective_date
220 );
221 --
222 /*
223 hr_utility.set_location ('sspndd epe = '||l_epe_rec.elig_per_elctbl_chc_id,20);
224 update ben_elig_per_elctbl_chc
225 set in_pndg_wkflow_flag = 'S'
226 where elig_per_elctbl_chc_id = l_epe_rec.elig_per_elctbl_chc_id;
227 */
228 --
229 end loop;
230 --
231 --Bug#2187697 - update all the comp. objects in a program/not in program if any of one
232 --comp. object is in pending work-flow or suspended status as choices are not deleted
233 open c_elig_per_elctbl_chc_3;
234 loop
235 fetch c_elig_per_elctbl_chc_3 into
236 l_elig_per_elctbl_chc_id, l_object_version_number;
237 if c_elig_per_elctbl_chc_3%notfound then
238 exit;
239 end if;
240 --
241 ben_elig_per_elc_chc_api.update_perf_ELIG_PER_ELC_CHC
242 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
243 ,p_object_version_number => l_object_version_number
244 ,p_in_pndg_wkflow_flag => 'S'
245 ,p_effective_date => p_effective_date
246 );
247 --
248 end loop ;
249 close c_elig_per_elctbl_chc_3;
250
251 --
252 hr_utility.set_location ('Leaving '||l_package,30);
253
254 end update_in_pend_flag;
255 --
256 procedure delete_elctbl_choice
257 (p_person_id in number
258 ,p_effective_date in date
259 ,p_business_group_id in number
260 ,p_rec out nocopy benutils.g_active_life_event
261 )
262 is
263 --
264 l_rec benutils.g_active_life_event;
265
266 type epetab is table of ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%type;
267 t_epe_tbl epetab;
268
269 l_object_version_number number ;
270 l_package varchar2(100):= 'delete_elctbl_choice';
271 l_cnt number;
272 --
273 /*
274 cursor c_enrt_rt is
275 select *
276 from ben_enrt_rt
277 where elig_per_elctbl_chc_id in
278 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
279 where per_in_ler_id = p_rec.per_in_ler_id)
280 union
281 select *
282 from ben_enrt_rt
283 where enrt_bnft_id in
284 (select enrt_bnft_id from ben_enrt_bnft
285 where elig_per_elctbl_chc_id in
286 (select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
287 where per_in_ler_id = p_rec.per_in_ler_id));
288 */
289 --
290 cursor c_pil_popl is
291 select *
292 from ben_pil_elctbl_chc_popl
293 where per_in_ler_id = p_rec.per_in_ler_id;
294 --
295 cursor c_pl_nip is
296 select null
297 from ben_pil_elctbl_chc_popl
298 where per_in_ler_id = p_rec.per_in_ler_id
299 and pl_id is not null;
300 --
301 cursor c_epe(p_per_in_ler_id number) is
302 select epe.elig_per_elctbl_chc_id
303 from ben_elig_per_elctbl_chc epe
304 where epe.per_in_ler_id = p_per_in_ler_id
305 and epe.pil_elctbl_chc_popl_id in
306 (select pel.pil_elctbl_chc_popl_id
307 from ben_pil_elctbl_chc_popl pel
308 where pel.per_in_ler_id = p_per_in_ler_id
309 and not exists
310 (select null
311 from ben_elig_per_elctbl_chc epe2
312 where pel.pil_elctbl_chc_popl_id=epe2.pil_elctbl_chc_popl_id
313 and nvl(epe2.in_pndg_wkflow_flag,'N') in ('Y', 'S')));
314 --
315 cursor c_epe_ch (p_elig_per_elctbl_chc_id number) is
316 select FONM_CVG_STRT_DT
317 ,PGM_ID
318 ,PL_ID
319 ,PL_TYP_ID
320 ,PLIP_ID
321 ,PTIP_ID
322 ,ENRT_CVG_STRT_DT_CD
323 ,ENRT_CVG_STRT_DT_RL
324 ,PRTT_ENRT_RSLT_ID
325 ,DPNT_CVG_STRT_DT_CD
326 ,DPNT_CVG_STRT_DT_RL
327 ,ENRT_CVG_STRT_DT
328 ,DPNT_DSGN_CD
329 ,LER_CHG_DPNT_CVG_CD
330 ,ERLST_DEENRT_DT
331 ,PROCG_END_DT
332 ,CRYFWD_ELIG_DPNT_CD
333 ,ELIG_FLAG
334 ,ELIG_OVRID_DT
335 ,ELIG_OVRID_PERSON_ID
336 ,INELIG_RSN_CD
337 ,MGR_OVRID_DT
338 ,MGR_OVRID_PERSON_ID
339 ,WS_MGR_ID
340 ,ASSIGNMENT_ID
341 ,ROLL_CRS_FLAG
342 ,CRNTLY_ENRD_FLAG
343 ,DFLT_FLAG
344 ,ELCTBL_FLAG
345 ,MNDTRY_FLAG
346 ,ALWS_DPNT_DSGN_FLAG
347 ,COMP_LVL_CD
348 ,AUTO_ENRT_FLAG
349 ,CTFN_RQD_FLAG
350 ,PER_IN_LER_ID
351 ,YR_PERD_ID
352 ,OIPLIP_ID
353 ,PL_ORDR_NUM
354 ,PLIP_ORDR_NUM
355 ,PTIP_ORDR_NUM
356 ,OIPL_ORDR_NUM
357 ,MUST_ENRL_ANTHR_PL_ID
358 ,SPCL_RT_PL_ID
359 ,SPCL_RT_OIPL_ID
360 ,BNFT_PRVDR_POOL_ID
361 ,CMBN_PTIP_ID
362 ,CMBN_PTIP_OPT_ID
363 ,CMBN_PLIP_ID
364 ,OIPL_ID
365 ,APPROVAL_STATUS_CD
366 ,elig_per_elctbl_chc_id
367 ,object_version_number
368 ,null mark_delete
369 from ben_elig_per_elctbl_chc
370 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
371 --
372 cursor c_enb_ch (p_elig_per_elctbl_chc_id number) is
373 select enrt_bnft_id
374 ,ELIG_PER_ELCTBL_CHC_ID
375 ,ORDR_NUM
376 ,OBJECT_VERSION_NUMBER
377 ,null mark_delete
378 from ben_enrt_bnft
379 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
380 --
381 cursor c_ecr_ch (p_elig_per_elctbl_chc_id number) is
382 select enrt_rt_id
383 ,ELIG_PER_ELCTBL_CHC_ID
384 ,ENRT_BNFT_ID
385 ,OBJECT_VERSION_NUMBER
386 ,ACTY_BASE_RT_ID
387 ,null mark_delete
388 from ben_enrt_rt
389 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
390 union
391 select enrt_rt_id
392 ,ELIG_PER_ELCTBL_CHC_ID
393 ,ENRT_BNFT_ID
394 ,OBJECT_VERSION_NUMBER
395 ,ACTY_BASE_RT_ID
396 ,null mark_delete
397 from ben_enrt_rt
398 where enrt_bnft_id in (select enrt_bnft_id
399 from ben_enrt_bnft
400 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
401 );
402 --
403 cursor c_epr_ch (p_elig_per_elctbl_chc_id number) is
404 select enrt_prem_id
405 ,actl_prem_id
406 ,ELIG_PER_ELCTBL_CHC_ID
407 ,ENRT_BNFT_ID
408 ,OBJECT_VERSION_NUMBER
409 ,null mark_delete
410 from ben_enrt_prem
411 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
412 --
413 cursor c_egd_ch (p_elig_per_elctbl_chc_id number) is
414 select elig_dpnt_id
415 ,ELIG_PER_ELCTBL_CHC_ID
416 ,PER_IN_LER_ID
417 ,ELIG_PER_ID
418 ,ELIG_PER_OPT_ID
419 ,ELIG_CVRD_DPNT_ID
420 ,DPNT_INELIG_FLAG
421 ,OVRDN_FLAG
422 ,DPNT_PERSON_ID
423 ,OBJECT_VERSION_NUMBER
424 ,null mark_delete
425 from ben_elig_dpnt
426 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
427 --
428 cursor c_ecc_ch (p_elig_per_elctbl_chc_id number) is
429 select elctbl_chc_ctfn_id
430 ,enrt_ctfn_typ_cd
431 ,ELIG_PER_ELCTBL_CHC_ID
432 ,ENRT_BNFT_ID
433 ,OBJECT_VERSION_NUMBER
434 ,null mark_delete
435 from ben_elctbl_chc_ctfn
436 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
437 --
438 --
439 l_dummy varchar2(10);
440 l_count number := 1;
441 begin
442 --
443 hr_utility.set_location ('Entering '||l_package,10);
444 g_unrest_epe_instance.delete;
445 g_unrest_enb_instance.delete;
446 g_unrest_ecr_instance.delete;
447 g_unrest_egd_instance.delete;
448 g_unrest_ecc_instance.delete;
449 g_unrest_epr_instance.delete;
450 --
451 benutils.get_active_life_event
452 (p_person_id => p_person_id,
453 p_business_group_id => p_business_group_id,
454 p_effective_date => p_effective_date,
455 p_lf_event_mode => 'U',
456 p_rec => p_rec);
457 --
458 --
459
460 if p_rec.per_in_ler_id is not null then
461 -- call update routine only if there are plnip/icd plans
462 open c_pl_nip;
463 fetch c_pl_nip into l_dummy;
464 if c_pl_nip%found then
465 --
466 update_in_pend_flag(p_person_id => p_person_id
467 ,p_per_in_ler_id => p_rec.per_in_ler_id
468 ,p_business_group_id => p_business_group_id
469 ,p_effective_date => p_effective_date
470 );
471 end if;
472 close c_pl_nip;
473 -- call default assignment process before deleting electable choices
474 --
475 -- only if effective_date is => previous lf evt occrd
476 /*
477 -- Commented as per Bug1874263 and
478 After discussing with Denise it is decided to remove the default enrollment
479 process from the benmngle call due to following reasons :
480 1. As unrestricted is called only from the professional UI and SS interface
481 user will always see the defaults on the enrollment forms.
482 2. Once the defaults are seen user takes a explicit action whether to enroll
483 or not, so it is not necessary to call the default enrollment process as part
484 of subsequent run.
485 3. Another problem is if we run the second unrestricted event after say
486 6 months, we should't create the enrollments for the first unrestricted run.
487 that too user explicitly not selected the default one as part of first enrollment.
488
489 if p_effective_date >= p_rec.lf_evt_ocrd_dt then
490 ben_manage_default_enrt.Process_default_enrt
491 (p_person_id => p_person_id
492 ,p_object_version_number => l_object_version_number
493 ,p_business_group_id => p_business_group_id
494 ,p_effective_date => p_effective_date
495 );
496 end if;
497 */
498 -- before deleting enrt_rt - capture the data into pl/sql table
499 /*
500 for i in c_enrt_rt loop
501 --
502 ben_manage_life_events.g_enrt_rt_tbl(l_count).enrt_rt_id := i.enrt_rt_id;
503 ben_manage_life_events.g_enrt_rt_tbl(l_count).acty_base_rt_id := i.acty_base_rt_id;
504 ben_manage_life_events.g_enrt_rt_tbl(l_count).prtt_rt_val_id := i.prtt_rt_val_id;
505 l_count := l_count + 1;
506 --
507 end loop;
508 --
509 */
510 l_count := 1;
511 for i in c_pil_popl loop
512 --
513 ben_manage_life_events.g_pil_popl_tbl(l_count).pgm_id := i.pgm_id;
514 ben_manage_life_events.g_pil_popl_tbl(l_count).pl_id := i.pl_id;
515 ben_manage_life_events.g_pil_popl_tbl(l_count).elcns_made_dt := i.elcns_made_dt;
516 l_count := l_count + 1;
517 --
518 end loop;
519 --
520 open c_epe(p_rec.per_in_ler_id);
521 fetch c_epe bulk collect into t_epe_tbl;
522 close c_epe;
523 --
524 --
525 hr_utility.set_location('epe count'|| t_epe_tbl.count,10);
526 if t_epe_tbl.count > 0 then
527 /*
528 forall i in 1..t_epe_tbl.last
529 delete from ben_enrt_rt
530 where elig_per_elctbl_chc_id = t_epe_tbl(i);
531
532 forall i in 1..t_epe_tbl.last
533 delete from ben_enrt_rt
534 where enrt_bnft_id in
535 (select enrt_bnft_id
536 from ben_enrt_bnft
537 where elig_per_elctbl_chc_id = t_epe_tbl(i));
538
539 forall i in 1..t_epe_tbl.last
540 delete from ben_elctbl_chc_ctfn
541 where elig_per_elctbl_chc_id = t_epe_tbl(i);
542
543 forall i in 1..t_epe_tbl.last
544 delete from ben_elctbl_chc_ctfn
545 where enrt_bnft_id in
546 (select enrt_bnft_id
547 from ben_enrt_bnft
548 where elig_per_elctbl_chc_id = t_epe_tbl(i));
549
550 forall i in 1..t_epe_tbl.last
551 delete from ben_enrt_bnft
552 where elig_per_elctbl_chc_id = t_epe_tbl(i);
553
554 forall i in 1..t_epe_tbl.last
555 delete from ben_elig_dpnt
556 where elig_per_elctbl_chc_id = t_epe_tbl(i);
557
558 forall i in 1..t_epe_tbl.last
559 delete from ben_elig_per_elctbl_chc
560 where elig_per_elctbl_chc_id = t_epe_tbl(i);
561 */
562 for i in 1..t_epe_tbl.last loop
563 hr_utility.set_location (' within loop'||t_epe_tbl(i),10);
564 open c_epe_ch (t_epe_tbl(i));
565 fetch c_epe_ch into g_unrest_epe_instance(i);
566 --
567 update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null
568 where elig_per_elctbl_chc_id = t_epe_tbl(i);
569 --
570 close c_epe_ch;
571 end loop;
572 --
573 l_cnt := 1;
574 for i in 1..t_epe_tbl.last loop
575 hr_utility.set_location (' within enb',10);
576 open c_enb_ch (t_epe_tbl(i));
577 loop
578 fetch c_enb_ch into g_unrest_enb_instance_row;
579 if c_enb_ch%found then
580 g_unrest_enb_instance(l_cnt) := g_unrest_enb_instance_row;
581 l_cnt := l_cnt + 1;
582 else
583 exit;
584 end if;
585 end loop;
586 close c_enb_ch;
587 end loop;
588 --
589 l_cnt := 1;
590 for i in 1..t_epe_tbl.last loop
591 hr_utility.set_location (' within ecr',10);
592 open c_ecr_ch (t_epe_tbl(i));
593 loop
594 fetch c_ecr_ch into g_unrest_ecr_instance_row;
595 if c_ecr_ch%found then
596 hr_utility.set_location ('enrt id'||g_unrest_ecr_instance_row.enrt_rt_id,11);
597 g_unrest_ecr_instance(l_cnt) := g_unrest_ecr_instance_row;
598 l_cnt := l_cnt + 1;
599 else
600 exit;
601 end if;
602 end loop;
603 close c_ecr_ch;
604 end loop;
605 --
606 l_cnt := 1;
607 for i in 1..t_epe_tbl.last loop
608 hr_utility.set_location (' within ecr',10);
609 open c_epr_ch (t_epe_tbl(i));
610 loop
611 fetch c_epr_ch into g_unrest_epr_instance_row;
612 if c_epr_ch%found then
613 hr_utility.set_location ('prem id'||g_unrest_epr_instance_row.enrt_prem_id,11);
614 g_unrest_epr_instance(l_cnt) := g_unrest_epr_instance_row;
615 l_cnt := l_cnt + 1;
616 else
617 exit;
618 end if;
619 end loop;
620 close c_epr_ch;
621 end loop;
622 --
623 l_cnt := 1;
624 for i in 1..t_epe_tbl.last loop
625 hr_utility.set_location (' within ecc',10);
626 open c_ecc_ch (t_epe_tbl(i));
627 loop
628 fetch c_ecc_ch into g_unrest_ecc_instance_row;
629 if c_ecc_ch%found then
630 hr_utility.set_location ('Chc ctfn id'||g_unrest_ecc_instance_row.elctbl_chc_ctfn_id,11);
631 g_unrest_ecc_instance(l_cnt) := g_unrest_ecc_instance_row;
632 l_cnt := l_cnt + 1;
633 else
634 exit;
635 end if;
636 end loop;
637 close c_ecc_ch;
638 end loop;
639 --
640 l_cnt := 1;
641 for i in 1..t_epe_tbl.last loop
642 hr_utility.set_location (' within egd ',10);
643 open c_egd_ch (t_epe_tbl(i));
644 loop
645 fetch c_egd_ch into g_unrest_egd_instance_row;
646 if c_egd_ch%found then
647 g_unrest_egd_instance(l_cnt) := g_unrest_egd_instance_row;
648 l_cnt := l_cnt + 1;
649 else
650 exit;
651 end if;
652 end loop;
653 close c_egd_ch;
654
655 end loop;
656
657 end if;
658 --
659 delete from ben_pil_elctbl_chc_popl pel
660 where per_in_ler_id = p_rec.per_in_ler_id
661 and not exists
662 (select null
663 from ben_elig_per_elctbl_chc epe
664 where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
665 and nvl(epe.in_pndg_wkflow_flag,'N') in ('Y', 'S') ) ;
666 --
667 -- Reset back 'Z' value of the in_pndg_wkflow_flag to 'N'.
668 -- As it was just used to suppress the deletion of electable choice
669 -- data associated with the suspended enrollments.
670 --
671 /*
672 update ben_elig_per_elctbl_chc
673 set in_pndg_wkflow_flag = 'N'
674 where in_pndg_wkflow_flag = 'Z'
675 and per_in_ler_id = p_rec.per_in_ler_id;
676 */
677 -- this call is moved to process_comp_objects
678 -- will be called just before calling the ben_automatic_enrollments.main
679 -- reset_elctbl_chc_inpng_flag(p_rec.per_in_ler_id);
680 --
681 end if;
682 --
683 hr_utility.set_location ('Leaving '||l_package,11);
684 --
685 -- Reset p_rec to null if exception
686 --
687 exception
688 --
689 when others then
690 --
691 p_rec := null;
692 raise;
693 --
694 --
695 End;
696
697 --
698 procedure update_elig_per_elctbl_choice
699 (
700 p_elig_per_elctbl_chc_id in number
701 ,p_enrt_cvg_strt_dt_cd in varchar2
702 ,p_enrt_cvg_strt_dt_rl in varchar2
703 ,p_ctfn_rqd_flag in varchar2
704 ,p_pil_elctbl_chc_popl_id in number
705 ,p_roll_crs_flag in varchar2
706 ,p_crntly_enrd_flag in varchar2
707 ,p_dflt_flag in varchar2
708 ,p_elctbl_flag in varchar2
709 ,p_mndtry_flag in varchar2
710 ,p_in_pndg_wkflow_flag in varchar2
711 ,p_dpnt_cvg_strt_dt_cd in varchar2
712 ,p_dpnt_cvg_strt_dt_rl in varchar2
713 ,p_enrt_cvg_strt_dt in date
714 ,p_alws_dpnt_dsgn_flag in varchar2
715 ,p_dpnt_dsgn_cd in varchar2
716 ,p_ler_chg_dpnt_cvg_cd in varchar2
717 ,p_erlst_deenrt_dt in date
718 ,p_procg_end_dt in date
719 ,p_comp_lvl_cd in varchar2
720 ,p_pl_id in number
721 ,p_oipl_id in number
722 ,p_pgm_id in number
723 ,p_plip_id in number
724 ,p_ptip_id in number
725 ,p_pl_typ_id in number
726 ,p_oiplip_id in number
727 ,p_cmbn_plip_id in number
728 ,p_cmbn_ptip_id in number
729 ,p_cmbn_ptip_opt_id in number
730 ,p_assignment_id in number
731 ,p_spcl_rt_pl_id in number
732 ,p_spcl_rt_oipl_id in number
733 ,p_must_enrl_anthr_pl_id in number
734 ,p_int_elig_per_elctbl_chc_id in number
735 ,p_prtt_enrt_rslt_id in number
736 ,p_bnft_prvdr_pool_id in number
737 ,p_per_in_ler_id in number
738 ,p_yr_perd_id in number
739 ,p_auto_enrt_flag in varchar2
740 ,p_business_group_id in number
741 ,p_pl_ordr_num in number
742 ,p_plip_ordr_num in number
743 ,p_ptip_ordr_num in number
744 ,p_oipl_ordr_num in number
745 ,p_comments in varchar2
746 ,p_elig_flag in varchar2
747 ,p_elig_ovrid_dt in date
748 ,p_elig_ovrid_person_id in number
749 ,p_inelig_rsn_cd in varchar2
750 ,p_mgr_ovrid_dt in date
751 ,p_mgr_ovrid_person_id in number
752 ,p_ws_mgr_id in number
753 ,p_approval_status_cd in varchar2
754 ,p_fonm_cvg_strt_dt in date
755 ,p_cryfwd_elig_dpnt_cd in varchar2
756 ,p_effective_date in date
757 ,p_pgm_typ_cd in varchar2
758 ,p_enrt_perd_end_dt in date
759 ,p_enrt_perd_strt_dt in date
760 ,p_dflt_enrt_dt in varchar2
761 ,p_uom in varchar2
762 ,p_acty_ref_perd_cd in varchar2
763 ,p_lee_rsn_id in number
764 ,p_enrt_perd_id in number
765 ,p_cls_enrt_dt_to_use_cd in varchar2
766 ) is
767 --
768 l_package varchar2(30) := '.Update_elig_per_elecatble';
769 l_object_version_number number;
770 l_pil_elctbl_chc_popl_id number;
771 l_oiplip_id number;
772 --
773 begin
774 --
775 hr_utility.set_location ('Entering '||l_package,10);
776 --
777 ben_elig_per_elc_chc_api.CreOrSel_pil_elctbl_chc_popl
778 (p_per_in_ler_id => p_per_in_ler_id
779 ,p_effective_date => p_effective_date
780 ,p_business_group_id => p_business_group_id
781 ,p_pgm_id => p_pgm_id
782 ,p_plip_id => p_plip_id
783 ,p_pl_id => p_pl_id
784 ,p_oipl_id => p_oipl_id
785 ,p_yr_perd_id => p_yr_perd_id
786 ,p_uom => p_uom
787 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
788 ,p_dflt_enrt_dt => p_dflt_enrt_dt
789 ,p_cls_enrt_dt_to_use_cd => p_cls_enrt_dt_to_use_cd
790 ,p_enrt_typ_cycl_cd => 'U'
791 ,p_enrt_perd_end_dt => p_enrt_perd_end_dt
792 ,p_enrt_perd_strt_dt => p_enrt_perd_strt_dt
793 ,p_procg_end_dt => p_procg_end_dt
794 ,p_lee_rsn_id => p_lee_rsn_id
795 ,p_enrt_perd_id => p_enrt_perd_id
796 ,p_ws_mgr_id => p_ws_mgr_id
797 ,p_assignment_id => p_assignment_id
798 ,p_program_application_id => fnd_global.prog_appl_id
799 ,p_program_id => fnd_global.conc_program_id
800 ,p_request_id => fnd_global.conc_request_id
801 ,p_program_update_date => sysdate
802 --
803 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
804 ,p_oiplip_id => l_oiplip_id
805 );
806 --
807 if g_unrest_epe_instance.count > 0 then
808 --
809 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
810 if g_unrest_epe_instance(i).elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id then
811 if g_unrest_epe_instance(i).mark_delete = 'Y' then -- Bug 4717052, Added this IF
812 return;
813 else
814 l_object_version_number := g_unrest_epe_instance(i).object_version_number;
815 g_unrest_epe_instance(i).mark_delete := 'Y';
816 end if;
817 end if;
818 --
819 end loop;
820 --
821 end if;
822 --
823 ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id := p_elig_per_elctbl_chc_id;
824 ben_epe_cache.g_currcobjepe_row.pl_id := p_pl_id;
825 ben_epe_cache.g_currcobjepe_row.plip_id := p_plip_id;
826 ben_epe_cache.g_currcobjepe_row.oipl_id := p_oipl_id;
827 --
828 ben_epe_cache.g_currcobjepe_row.elctbl_flag := p_elctbl_flag;
829 ben_epe_cache.g_currcobjepe_row.per_in_ler_id := p_per_in_ler_id;
830 ben_epe_cache.g_currcobjepe_row.business_group_id := p_business_group_id;
831 --ben_epe_cache.g_currcobjepe_row.object_version_number := l_object_version_number;
832 ben_epe_cache.g_currcobjepe_row.comp_lvl_cd := p_comp_lvl_cd;
833 ben_epe_cache.g_currcobjepe_row.pgm_id := p_pgm_id;
834 ben_epe_cache.g_currcobjepe_row.pl_typ_id := p_pl_typ_id;
835 ben_epe_cache.g_currcobjepe_row.ctfn_rqd_flag := p_ctfn_rqd_flag;
836 --
837 ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
838 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
839 p_dflt_flag => p_dflt_flag,
840 p_elctbl_flag => p_elctbl_flag,
841 p_object_version_number => l_object_version_number,
842 p_effective_date => p_effective_date,
843 p_program_application_id => fnd_global.prog_appl_id,
844 p_program_id => fnd_global.conc_program_id,
845 p_request_id => fnd_global.conc_request_id,
846 p_program_update_date => sysdate
847 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
848 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
849 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
850 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
851 ,p_roll_crs_flag => p_roll_crs_flag
852 ,p_crntly_enrd_flag => p_crntly_enrd_flag
853 ,p_mndtry_flag => p_mndtry_flag
854 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
855 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
856 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
857 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
858 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
859 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
860 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
861 ,p_procg_end_dt => p_procg_end_dt
862 ,p_comp_lvl_cd => p_comp_lvl_cd
863 ,p_pl_id => p_pl_id
864 ,p_oipl_id => p_oipl_id
865 ,p_pgm_id => p_pgm_id
866 ,p_plip_id => p_plip_id
867 ,p_ptip_id => p_ptip_id
868 ,p_pl_typ_id => p_pl_typ_id
869 ,p_oiplip_id => p_oiplip_id
870 ,p_cmbn_plip_id => p_cmbn_plip_id
871 ,p_cmbn_ptip_id => p_cmbn_ptip_id
872 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
873 ,p_assignment_id => p_assignment_id
874 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
875 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
876 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
877 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
878 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
879 ,p_per_in_ler_id => p_per_in_ler_id
880 ,p_yr_perd_id => p_yr_perd_id
881 ,p_auto_enrt_flag => p_auto_enrt_flag
882 ,p_business_group_id => p_business_group_id
883 ,p_pl_ordr_num => p_pl_ordr_num
884 ,p_plip_ordr_num => p_plip_ordr_num
885 ,p_ptip_ordr_num => p_ptip_ordr_num
886 ,p_oipl_ordr_num => p_oipl_ordr_num
887 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
888 );
889
890
891 end ;
892 --
893
894 procedure update_enrt_bnft
895 ( p_enrt_bnft_id in number
896 ,p_dflt_flag in varchar2
897 ,p_val_has_bn_prortd_flag in varchar2
898 ,p_bndry_perd_cd in varchar2
899 ,p_val in number
900 ,p_nnmntry_uom in varchar2
901 ,p_bnft_typ_cd in varchar2
902 ,p_entr_val_at_enrt_flag in varchar2
903 ,p_mn_val in number
904 ,p_mx_val in number
905 ,p_incrmt_val in number
906 ,p_dflt_val in number
907 ,p_rt_typ_cd in varchar2
908 ,p_cvg_mlt_cd in varchar2
909 ,p_ctfn_rqd_flag in varchar2
910 ,p_ordr_num in number
911 ,p_crntly_enrld_flag in varchar2
912 ,p_elig_per_elctbl_chc_id in number
913 ,p_prtt_enrt_rslt_id in number
914 ,p_comp_lvl_fctr_id in number
915 ,p_business_group_id in number
916 ,p_enb_attribute_category in varchar2
917 ,p_enb_attribute1 in varchar2
918 ,p_enb_attribute2 in varchar2
919 ,p_enb_attribute3 in varchar2
920 ,p_enb_attribute4 in varchar2
921 ,p_enb_attribute5 in varchar2
922 ,p_enb_attribute6 in varchar2
923 ,p_enb_attribute7 in varchar2
924 ,p_enb_attribute8 in varchar2
925 ,p_enb_attribute9 in varchar2
926 ,p_enb_attribute10 in varchar2
927 ,p_enb_attribute11 in varchar2
928 ,p_enb_attribute12 in varchar2
929 ,p_enb_attribute13 in varchar2
930 ,p_enb_attribute14 in varchar2
931 ,p_enb_attribute15 in varchar2
932 ,p_enb_attribute16 in varchar2
933 ,p_enb_attribute17 in varchar2
934 ,p_enb_attribute18 in varchar2
935 ,p_enb_attribute19 in varchar2
936 ,p_enb_attribute20 in varchar2
937 ,p_enb_attribute21 in varchar2
938 ,p_enb_attribute22 in varchar2
939 ,p_enb_attribute23 in varchar2
940 ,p_enb_attribute24 in varchar2
941 ,p_enb_attribute25 in varchar2
942 ,p_enb_attribute26 in varchar2
943 ,p_enb_attribute27 in varchar2
944 ,p_enb_attribute28 in varchar2
945 ,p_enb_attribute29 in varchar2
946 ,p_enb_attribute30 in varchar2
947 ,p_request_id in number
948 ,p_program_application_id in number
949 ,p_program_id in number
950 ,p_program_update_date in date
951 ,p_mx_wout_ctfn_val in number
952 ,p_mx_wo_ctfn_flag in varchar2
953 ,p_effective_date in date
954 ) is
955 --
956 l_object_version_number number;
957
958 begin
959 --
960 if g_unrest_enb_instance.count > 0 then
961 --
962 for i in g_unrest_enb_instance.first..g_unrest_enb_instance.last loop
963 if g_unrest_enb_instance(i).enrt_bnft_id = p_enrt_bnft_id then
964 l_object_version_number := g_unrest_enb_instance(i).object_version_number;
965 g_unrest_enb_instance(i).mark_delete := 'Y';
966 end if;
967 --
968 end loop;
969 --
970 end if;
971 --
972 ben_enrt_bnft_api.update_enrt_bnft
973 (p_enrt_bnft_id => p_enrt_bnft_id
974 ,p_dflt_flag => p_dflt_flag
975 ,p_val_has_bn_prortd_flag => p_val_has_bn_prortd_flag
976 ,p_bndry_perd_cd => p_bndry_perd_cd
977 ,p_val => p_val
978 ,p_nnmntry_uom => p_nnmntry_uom
979 ,p_bnft_typ_cd => p_bnft_typ_cd
980 ,p_entr_val_at_enrt_flag => p_entr_val_at_enrt_flag
981 ,p_mn_val => p_mn_val
982 ,p_mx_val => p_mx_val
983 ,p_incrmt_val => p_incrmt_val
984 ,p_dflt_val => p_dflt_val
985 ,p_rt_typ_cd => p_rt_typ_cd
986 ,p_cvg_mlt_cd => p_cvg_mlt_cd
987 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
988 ,p_ordr_num => p_ordr_num
989 ,p_crntly_enrld_flag => p_crntly_enrld_flag
990 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
991 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
992 ,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
993 ,p_business_group_id => p_business_group_id
994 ,p_enb_attribute_category => p_enb_attribute_category
995 ,p_enb_attribute1 => p_enb_attribute1
996 ,p_enb_attribute2 => p_enb_attribute2
997 ,p_enb_attribute3 => p_enb_attribute3
998 ,p_enb_attribute4 => p_enb_attribute4
999 ,p_enb_attribute5 => p_enb_attribute5
1000 ,p_enb_attribute6 => p_enb_attribute6
1001 ,p_enb_attribute7 => p_enb_attribute7
1002 ,p_enb_attribute8 => p_enb_attribute8
1003 ,p_enb_attribute9 => p_enb_attribute9
1004 ,p_enb_attribute10 => p_enb_attribute10
1005 ,p_enb_attribute11 => p_enb_attribute11
1006 ,p_enb_attribute12 => p_enb_attribute12
1007 ,p_enb_attribute13 => p_enb_attribute13
1008 ,p_enb_attribute14 => p_enb_attribute14
1009 ,p_enb_attribute15 => p_enb_attribute15
1010 ,p_enb_attribute16 => p_enb_attribute16
1011 ,p_enb_attribute17 => p_enb_attribute17
1012 ,p_enb_attribute18 => p_enb_attribute18
1013 ,p_enb_attribute19 => p_enb_attribute19
1014 ,p_enb_attribute20 => p_enb_attribute20
1015 ,p_enb_attribute21 => p_enb_attribute21
1016 ,p_enb_attribute22 => p_enb_attribute22
1017 ,p_enb_attribute23 => p_enb_attribute23
1018 ,p_enb_attribute24 => p_enb_attribute24
1019 ,p_enb_attribute25 => p_enb_attribute25
1020 ,p_enb_attribute26 => p_enb_attribute26
1021 ,p_enb_attribute27 => p_enb_attribute27
1022 ,p_enb_attribute28 => p_enb_attribute28
1023 ,p_enb_attribute29 => p_enb_attribute29
1024 ,p_enb_attribute30 => p_enb_attribute30
1025 ,p_request_id => p_request_id
1026 ,p_program_application_id => p_program_application_id
1027 ,p_program_id => p_program_id
1028 ,p_program_update_date => p_program_update_date
1029 ,p_mx_wout_ctfn_val => p_mx_wout_ctfn_val
1030 ,p_mx_wo_ctfn_flag => p_mx_wo_ctfn_flag
1031 ,p_object_version_number => l_object_version_number
1032 ,p_effective_date => trunc(p_effective_date));
1033 end;
1034 --
1035 procedure update_enrt_rt
1036 ( p_enrt_rt_id in NUMBER,
1037 p_ordr_num in number ,
1038 p_acty_typ_cd in VARCHAR2 ,
1039 p_tx_typ_cd in VARCHAR2 ,
1040 p_ctfn_rqd_flag in VARCHAR2 ,
1041 p_dflt_flag in VARCHAR2 ,
1042 p_dflt_pndg_ctfn_flag in VARCHAR2 ,
1043 p_dsply_on_enrt_flag in VARCHAR2 ,
1044 p_use_to_calc_net_flx_cr_flag in VARCHAR2 ,
1045 p_entr_val_at_enrt_flag in VARCHAR2 ,
1046 p_asn_on_enrt_flag in VARCHAR2 ,
1047 p_rl_crs_only_flag in VARCHAR2 ,
1048 p_dflt_val in NUMBER ,
1049 p_ann_val in NUMBER ,
1050 p_ann_mn_elcn_val in NUMBER ,
1051 p_ann_mx_elcn_val in NUMBER ,
1052 p_val in NUMBER ,
1053 p_nnmntry_uom in VARCHAR2 ,
1054 p_mx_elcn_val in NUMBER ,
1055 p_mn_elcn_val in NUMBER ,
1056 p_incrmt_elcn_val in NUMBER ,
1057 p_cmcd_acty_ref_perd_cd in VARCHAR2 ,
1058 p_cmcd_mn_elcn_val in NUMBER ,
1059 p_cmcd_mx_elcn_val in NUMBER ,
1060 p_cmcd_val in NUMBER ,
1061 p_cmcd_dflt_val in NUMBER ,
1062 p_rt_usg_cd in VARCHAR2 ,
1063 p_ann_dflt_val in NUMBER ,
1064 p_bnft_rt_typ_cd in VARCHAR2 ,
1065 p_rt_mlt_cd in VARCHAR2 ,
1066 p_dsply_mn_elcn_val in NUMBER ,
1067 p_dsply_mx_elcn_val in NUMBER ,
1068 p_entr_ann_val_flag in VARCHAR2,
1069 p_rt_strt_dt in DATE ,
1070 p_rt_strt_dt_cd in VARCHAR2 ,
1071 p_rt_strt_dt_rl in NUMBER ,
1072 p_rt_typ_cd in VARCHAR2 ,
1073 p_elig_per_elctbl_chc_id in NUMBER ,
1074 p_acty_base_rt_id in NUMBER ,
1075 p_spcl_rt_enrt_rt_id in NUMBER ,
1076 p_enrt_bnft_id in NUMBER ,
1077 p_prtt_rt_val_id in NUMBER ,
1078 p_decr_bnft_prvdr_pool_id in NUMBER ,
1079 p_cvg_amt_calc_mthd_id in NUMBER ,
1080 p_actl_prem_id in NUMBER ,
1081 p_comp_lvl_fctr_id in NUMBER ,
1082 p_ptd_comp_lvl_fctr_id in NUMBER ,
1083 p_clm_comp_lvl_fctr_id in NUMBER ,
1084 p_business_group_id in NUMBER,
1085 p_perf_min_max_edit in VARCHAR2 ,
1086 p_iss_val in number ,
1087 p_val_last_upd_date in date ,
1088 p_val_last_upd_person_id in number ,
1089 p_pp_in_yr_used_num in number ,
1090 p_ecr_attribute_category in VARCHAR2 ,
1091 p_ecr_attribute1 in VARCHAR2 ,
1092 p_ecr_attribute2 in VARCHAR2 ,
1093 p_ecr_attribute3 in VARCHAR2 ,
1094 p_ecr_attribute4 in VARCHAR2 ,
1095 p_ecr_attribute5 in VARCHAR2 ,
1096 p_ecr_attribute6 in VARCHAR2 ,
1097 p_ecr_attribute7 in VARCHAR2 ,
1098 p_ecr_attribute8 in VARCHAR2 ,
1099 p_ecr_attribute9 in VARCHAR2 ,
1100 p_ecr_attribute10 in VARCHAR2 ,
1101 p_ecr_attribute11 in VARCHAR2 ,
1102 p_ecr_attribute12 in VARCHAR2 ,
1103 p_ecr_attribute13 in VARCHAR2 ,
1104 p_ecr_attribute14 in VARCHAR2 ,
1105 p_ecr_attribute15 in VARCHAR2 ,
1106 p_ecr_attribute16 in VARCHAR2 ,
1107 p_ecr_attribute17 in VARCHAR2 ,
1108 p_ecr_attribute18 in VARCHAR2 ,
1109 p_ecr_attribute19 in VARCHAR2 ,
1110 p_ecr_attribute20 in VARCHAR2 ,
1111 p_ecr_attribute21 in VARCHAR2 ,
1112 p_ecr_attribute22 in VARCHAR2 ,
1113 p_ecr_attribute23 in VARCHAR2 ,
1114 p_ecr_attribute24 in VARCHAR2 ,
1115 p_ecr_attribute25 in VARCHAR2 ,
1116 p_ecr_attribute26 in VARCHAR2 ,
1117 p_ecr_attribute27 in VARCHAR2 ,
1118 p_ecr_attribute28 in VARCHAR2 ,
1119 p_ecr_attribute29 in VARCHAR2 ,
1120 p_ecr_attribute30 in VARCHAR2 ,
1121 p_request_id in NUMBER ,
1122 p_program_application_id in NUMBER ,
1123 p_program_id in NUMBER ,
1124 p_program_update_date in DATE ,
1125 p_effective_date in date
1126 ) is
1127 --
1128 l_object_version_number number;
1129 --
1130 begin
1131 --
1132 if g_unrest_ecr_instance.count > 0 then
1133 --
1134 for i in g_unrest_ecr_instance.first..g_unrest_ecr_instance.last loop
1135 if g_unrest_ecr_instance(i).enrt_rt_id = p_enrt_rt_id then
1136 l_object_version_number := g_unrest_ecr_instance(i).object_version_number;
1137 g_unrest_ecr_instance(i).mark_delete := 'Y';
1138 end if;
1139 --
1140 end loop;
1141 --
1142 end if;
1143 --
1144 ben_Enrollment_Rate_api.update_Enrollment_Rate
1145 ( p_enrt_rt_id => p_enrt_rt_id
1146 , p_acty_typ_cd =>p_acty_typ_cd
1147 , p_tx_typ_cd =>p_tx_typ_cd
1148 , p_ctfn_rqd_flag =>p_ctfn_rqd_flag
1149 , p_dflt_flag =>p_dflt_flag
1150 , p_dflt_pndg_ctfn_flag =>p_dflt_pndg_ctfn_flag
1151 , p_dsply_on_enrt_flag =>p_dsply_on_enrt_flag
1152 , p_use_to_calc_net_flx_cr_flag =>p_use_to_calc_net_flx_cr_flag
1153 , p_entr_val_at_enrt_flag =>p_entr_val_at_enrt_flag
1154 , p_asn_on_enrt_flag =>p_asn_on_enrt_flag
1155 , p_rl_crs_only_flag =>p_rl_crs_only_flag
1156 , p_dflt_val =>p_dflt_val
1157 , p_ann_val =>p_ann_val
1158 , p_ann_mn_elcn_val =>p_ann_mn_elcn_val
1159 , p_ann_mx_elcn_val =>p_ann_mx_elcn_val
1160 , p_val =>p_val
1161 , p_nnmntry_uom =>p_nnmntry_uom
1162 , p_mx_elcn_val =>p_mx_elcn_val
1163 , p_mn_elcn_val =>p_mn_elcn_val
1164 , p_incrmt_elcn_val =>p_incrmt_elcn_val
1165 , p_cmcd_acty_ref_perd_cd =>p_cmcd_acty_ref_perd_cd
1166 , p_cmcd_mn_elcn_val =>p_cmcd_mn_elcn_val
1167 , p_cmcd_mx_elcn_val =>p_cmcd_mx_elcn_val
1168 , p_cmcd_val =>p_cmcd_val
1169 , p_cmcd_dflt_val =>p_cmcd_dflt_val
1170 , p_rt_usg_cd =>p_rt_usg_cd
1171 , p_ann_dflt_val =>p_ann_dflt_val
1172 , p_bnft_rt_typ_cd =>p_bnft_rt_typ_cd
1173 , p_rt_mlt_cd =>p_rt_mlt_cd
1174 , p_dsply_mn_elcn_val =>p_dsply_mn_elcn_val
1175 , p_dsply_mx_elcn_val =>p_dsply_mx_elcn_val
1176 , p_entr_ann_val_flag =>p_entr_ann_val_flag
1177 , p_rt_strt_dt =>p_rt_strt_dt
1178 , p_rt_strt_dt_cd =>p_rt_strt_dt_cd
1179 , p_rt_strt_dt_rl =>p_rt_strt_dt_rl
1180 , p_rt_typ_cd =>p_rt_typ_cd
1181 , p_elig_per_elctbl_chc_id =>p_elig_per_elctbl_chc_id
1182 , p_acty_base_rt_id =>p_acty_base_rt_id
1183 , p_spcl_rt_enrt_rt_id =>p_spcl_rt_enrt_rt_id
1184 , p_enrt_bnft_id =>p_enrt_bnft_id
1185 , p_prtt_rt_val_id =>p_prtt_rt_val_id
1186 , p_decr_bnft_prvdr_pool_id =>p_decr_bnft_prvdr_pool_id
1187 , p_cvg_amt_calc_mthd_id =>p_cvg_amt_calc_mthd_id
1188 , p_actl_prem_id =>p_actl_prem_id
1189 , p_comp_lvl_fctr_id =>p_comp_lvl_fctr_id
1190 , p_ptd_comp_lvl_fctr_id =>p_ptd_comp_lvl_fctr_id
1191 , p_clm_comp_lvl_fctr_id =>p_clm_comp_lvl_fctr_id
1192 , p_business_group_id =>p_business_group_id
1193 , p_request_id =>p_request_id
1194 , p_program_application_id =>p_program_application_id
1195 , p_program_id =>p_program_id
1196 , p_program_update_date =>p_program_update_date
1197 , p_effective_date =>p_effective_date
1198 , p_pp_in_yr_used_num =>p_pp_in_yr_used_num
1199 , p_ordr_num =>p_ordr_num
1200 , p_iss_val =>p_iss_val
1201 , p_object_version_number => l_object_version_number
1202 );
1203
1204 null;
1205 end;
1206 --
1207 procedure update_elig_dpnt
1208 (p_elig_dpnt_id in number
1209 ,p_create_dt in date
1210 ,p_elig_strt_dt in date
1211 ,p_elig_thru_dt in date
1212 ,p_ovrdn_flag in varchar2
1213 ,p_ovrdn_thru_dt in date
1214 ,p_inelg_rsn_cd in varchar2
1215 ,p_dpnt_inelig_flag in varchar2
1216 ,p_elig_per_elctbl_chc_id in number
1217 ,p_per_in_ler_id in number
1218 ,p_elig_per_id in number
1219 ,p_elig_per_opt_id in number
1220 ,p_elig_cvrd_dpnt_id in number
1221 ,p_dpnt_person_id in number
1222 ,p_business_group_id in number
1223 ,p_egd_attribute_category in varchar2
1224 ,p_egd_attribute1 in varchar2
1225 ,p_egd_attribute2 in varchar2
1226 ,p_egd_attribute3 in varchar2
1227 ,p_egd_attribute4 in varchar2
1228 ,p_egd_attribute5 in varchar2
1229 ,p_egd_attribute6 in varchar2
1230 ,p_egd_attribute7 in varchar2
1231 ,p_egd_attribute8 in varchar2
1232 ,p_egd_attribute9 in varchar2
1233 ,p_egd_attribute10 in varchar2
1234 ,p_egd_attribute11 in varchar2
1235 ,p_egd_attribute12 in varchar2
1236 ,p_egd_attribute13 in varchar2
1237 ,p_egd_attribute14 in varchar2
1238 ,p_egd_attribute15 in varchar2
1239 ,p_egd_attribute16 in varchar2
1240 ,p_egd_attribute17 in varchar2
1241 ,p_egd_attribute18 in varchar2
1242 ,p_egd_attribute19 in varchar2
1243 ,p_egd_attribute20 in varchar2
1244 ,p_egd_attribute21 in varchar2
1245 ,p_egd_attribute22 in varchar2
1246 ,p_egd_attribute23 in varchar2
1247 ,p_egd_attribute24 in varchar2
1248 ,p_egd_attribute25 in varchar2
1249 ,p_egd_attribute26 in varchar2
1250 ,p_egd_attribute27 in varchar2
1251 ,p_egd_attribute28 in varchar2
1252 ,p_egd_attribute29 in varchar2
1253 ,p_egd_attribute30 in varchar2
1254 ,p_request_id in number
1255 ,p_program_application_id in number
1256 ,p_program_id in number
1257 ,p_program_update_date in date
1258 ,p_object_version_number in out nocopy number
1259 ,p_effective_date in date
1260 ) is
1261 --
1262 l_object_version_number number;
1263 --
1264 begin
1265 --
1266 if g_unrest_egd_instance.count > 0 then
1267 --
1268 for i in g_unrest_egd_instance.first..g_unrest_egd_instance.last loop
1269 if g_unrest_egd_instance(i).elig_dpnt_id = p_elig_dpnt_id then
1270 l_object_version_number := g_unrest_egd_instance(i).object_version_number;
1271 g_unrest_egd_instance(i).mark_delete := 'Y';
1272 end if;
1273 --
1274 end loop;
1275 --
1276 end if;
1277 --
1278 ben_ELIG_DPNT_api.update_perf_ELIG_DPNT
1279 (p_elig_dpnt_id => p_elig_dpnt_id
1280 ,p_create_dt => p_create_dt
1281 ,p_business_group_id => p_business_group_id
1282 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1283 ,p_dpnt_person_id => p_dpnt_person_id
1284 ,p_per_in_ler_id => p_per_in_ler_id
1285 ,p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id
1286 ,p_elig_strt_dt => p_elig_strt_dt
1287 ,p_elig_thru_dt => p_elig_thru_dt
1288 ,p_elig_per_id => p_elig_per_id
1289 ,p_elig_per_opt_id => p_elig_per_opt_id
1290 ,p_ovrdn_flag => p_ovrdn_flag
1291 ,p_ovrdn_thru_dt => p_ovrdn_thru_dt
1292 ,p_object_version_number => l_object_version_number
1293 ,p_effective_date => p_effective_date
1294 ,p_program_application_id => p_program_application_id
1295 ,p_program_id => p_program_id
1296 ,p_request_id => p_request_id
1297 ,p_program_update_date => p_program_update_date
1298 );
1299 --
1300 p_object_version_number := l_object_version_number;
1301 end;
1302 --
1303 --
1304 procedure update_enrt_prem
1305 ( p_enrt_prem_id in number
1306 ,p_val in number
1307 ,p_uom in varchar2
1308 ,p_elig_per_elctbl_chc_id in number
1309 ,p_enrt_bnft_id in number
1310 ,p_actl_prem_id in number
1311 ,p_business_group_id in number
1312 ,p_epr_attribute_category in varchar2
1313 ,p_epr_attribute1 in varchar2
1314 ,p_epr_attribute2 in varchar2
1315 ,p_epr_attribute3 in varchar2
1316 ,p_epr_attribute4 in varchar2
1317 ,p_epr_attribute5 in varchar2
1318 ,p_epr_attribute6 in varchar2
1319 ,p_epr_attribute7 in varchar2
1320 ,p_epr_attribute8 in varchar2
1321 ,p_epr_attribute9 in varchar2
1322 ,p_epr_attribute10 in varchar2
1323 ,p_epr_attribute11 in varchar2
1324 ,p_epr_attribute12 in varchar2
1325 ,p_epr_attribute13 in varchar2
1326 ,p_epr_attribute14 in varchar2
1327 ,p_epr_attribute15 in varchar2
1328 ,p_epr_attribute16 in varchar2
1329 ,p_epr_attribute17 in varchar2
1330 ,p_epr_attribute18 in varchar2
1331 ,p_epr_attribute19 in varchar2
1332 ,p_epr_attribute20 in varchar2
1333 ,p_epr_attribute21 in varchar2
1334 ,p_epr_attribute22 in varchar2
1335 ,p_epr_attribute23 in varchar2
1336 ,p_epr_attribute24 in varchar2
1337 ,p_epr_attribute25 in varchar2
1338 ,p_epr_attribute26 in varchar2
1339 ,p_epr_attribute27 in varchar2
1340 ,p_epr_attribute28 in varchar2
1341 ,p_epr_attribute29 in varchar2
1342 ,p_epr_attribute30 in varchar2
1343 ,p_object_version_number in out nocopy number
1344 ,p_request_id in number
1345 ,p_program_application_id in number
1346 ,p_program_id in number
1347 ,p_program_update_date in date
1348 ) is
1349 --
1350 l_object_version_number number;
1351 --
1352 begin
1353 --
1354 if g_unrest_epr_instance.count > 0 then
1355 --
1356 for i in g_unrest_epr_instance.first..g_unrest_epr_instance.last loop
1357 if g_unrest_epr_instance(i).enrt_prem_id = p_enrt_prem_id then
1358 l_object_version_number := g_unrest_epr_instance(i).object_version_number;
1359 g_unrest_epr_instance(i).mark_delete := 'Y';
1360 end if;
1361 --
1362 end loop;
1363 --
1364 end if;
1365 --
1366 hr_utility.set_location('Obj version'||l_object_version_number,10);
1367 --
1368 update ben_enrt_prem set val = p_val, uom=p_uom
1369 where enrt_prem_id = p_enrt_prem_id;
1370 /*
1371 ben_enrt_prem_api.update_enrt_prem
1372 (p_enrt_prem_id => p_enrt_prem_id
1373 ,p_val => p_val
1374 ,p_uom => p_uom
1375 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1376 ,p_enrt_bnft_id => p_enrt_bnft_id
1377 ,p_actl_prem_id => p_actl_prem_id
1378 ,p_business_group_id => p_business_group_id
1379 ,p_epr_attribute_category => p_epr_attribute_category
1380 ,p_epr_attribute1 => p_epr_attribute1
1381 ,p_epr_attribute2 => p_epr_attribute2
1382 ,p_epr_attribute3 => p_epr_attribute3
1383 ,p_epr_attribute4 => p_epr_attribute4
1384 ,p_epr_attribute5 => p_epr_attribute5
1385 ,p_epr_attribute6 => p_epr_attribute6
1386 ,p_epr_attribute7 => p_epr_attribute7
1387 ,p_epr_attribute8 => p_epr_attribute8
1388 ,p_epr_attribute9 => p_epr_attribute9
1389 ,p_epr_attribute10 => p_epr_attribute10
1390 ,p_epr_attribute11 => p_epr_attribute11
1391 ,p_epr_attribute12 => p_epr_attribute12
1392 ,p_epr_attribute13 => p_epr_attribute13
1393 ,p_epr_attribute14 => p_epr_attribute14
1394 ,p_epr_attribute15 => p_epr_attribute15
1395 ,p_epr_attribute16 => p_epr_attribute16
1396 ,p_epr_attribute17 => p_epr_attribute17
1397 ,p_epr_attribute18 => p_epr_attribute18
1398 ,p_epr_attribute19 => p_epr_attribute19
1399 ,p_epr_attribute20 => p_epr_attribute20
1400 ,p_epr_attribute21 => p_epr_attribute21
1401 ,p_epr_attribute22 => p_epr_attribute22
1402 ,p_epr_attribute23 => p_epr_attribute23
1403 ,p_epr_attribute24 => p_epr_attribute24
1404 ,p_epr_attribute25 => p_epr_attribute25
1405 ,p_epr_attribute26 => p_epr_attribute26
1406 ,p_epr_attribute27 => p_epr_attribute27
1407 ,p_epr_attribute28 => p_epr_attribute28
1408 ,p_epr_attribute29 => p_epr_attribute29
1409 ,p_epr_attribute30 => p_epr_attribute30
1410 ,p_object_version_number => l_object_version_number
1411 ,p_request_id => p_request_id
1412 ,p_program_application_id => p_program_application_id
1413 ,p_program_id => p_program_id
1414 ,p_program_update_date => p_program_update_date
1415 );
1416 --
1417 p_object_version_number := l_object_version_number;
1418 */
1419 --
1420 end;
1421 --
1422 function epe_exists
1423 ( p_per_in_ler_id number
1424 ,p_pgm_id number
1425 ,p_pl_id number
1426 ,p_oipl_id number
1427 ,p_plip_id number
1428 ,p_oiplip_id number
1429 ,p_ptip_id number
1430 ,p_bnft_prvdr_pool_id number
1431 ,p_CMBN_PTIP_ID number
1432 ,p_CMBN_PTIP_OPT_ID number
1433 ,p_CMBN_PLIP_ID number
1434 ,p_comp_lvl_cd varchar2
1435 ) return number is
1436 --
1437 l_elig_per_elctbl_chc_id number;
1438 begin
1439 --
1440 if g_unrest_epe_instance.count > 0 and p_bnft_prvdr_pool_id is null then
1441 --
1442 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1443 --
1444 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1445 nvl(g_unrest_epe_instance(i).pgm_id,-1) = nvl(p_pgm_id,-1) and
1446 nvl(g_unrest_epe_instance(i).pl_id, -1) = nvl(p_pl_id, -1) and
1447 nvl(g_unrest_epe_instance(i).oipl_id, -1) = nvl(p_oipl_id, -1)
1448 then
1449 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1450 exit;
1451 end if;
1452 end loop;
1453 end if;
1454 --
1455 if g_unrest_epe_instance.count > 0 and p_bnft_prvdr_pool_id is not null then
1456 --
1457 if p_comp_lvl_cd = 'PGM' then
1458 --
1459 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1460 --
1461 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1462 g_unrest_epe_instance(i).pgm_id = p_pgm_id and
1463 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1464 then
1465 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1466 exit;
1467 end if;
1468 end loop;
1469 --
1470 elsif p_comp_lvl_cd = 'PLIP' then
1471 --
1472 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1473 --
1474 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1475 g_unrest_epe_instance(i).plip_id = p_plip_id and
1476 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1477 then
1478 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1479 exit;
1480 end if;
1481 end loop;
1482 --
1483 elsif p_comp_lvl_cd = 'PTIP' then
1484 --
1485 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1486 --
1487 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1488 g_unrest_epe_instance(i).ptip_id = p_ptip_id and
1489 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1490 then
1491 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1492 exit;
1493 end if;
1494 end loop;
1495 --
1496 elsif p_comp_lvl_cd = 'CPLIP' then
1497 --
1498 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1499 --
1500 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1501 g_unrest_epe_instance(i).cmbn_plip_id = p_cmbn_plip_id and
1502 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1503 then
1504 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1505 exit;
1506 end if;
1507 end loop;
1508 --
1509 elsif p_comp_lvl_cd = 'CPTIP' then
1510 --
1511 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1512 --
1513 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1514 g_unrest_epe_instance(i).cmbn_ptip_id = p_cmbn_ptip_id and
1515 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1516 then
1517 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1518 exit;
1519 end if;
1520 end loop;
1521 --
1522 elsif p_comp_lvl_cd = 'OIPLIP' then
1523 --
1524 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1525 --
1526 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1527 g_unrest_epe_instance(i).oiplip_id = p_oiplip_id and
1528 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1529 then
1530 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1531 exit;
1532 end if;
1533 end loop;
1534 --
1535 elsif p_comp_lvl_cd = 'CPTIPOPT' then
1536 --
1537 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1538 --
1539 if g_unrest_epe_instance(i).per_in_ler_id = p_per_in_ler_id and
1540 g_unrest_epe_instance(i).cmbn_ptip_opt_id = p_cmbn_ptip_opt_id and
1541 g_unrest_epe_instance(i).comp_lvl_cd = p_comp_lvl_cd
1542 then
1543 l_elig_per_elctbl_chc_id := g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1544 exit;
1545 end if;
1546 end loop;
1547 --
1548 end if;
1549 --
1550 end if;
1551 hr_utility.set_location('Elig per chc id'||l_elig_per_elctbl_chc_id,10);
1552 return l_elig_per_elctbl_chc_id;
1553
1554 end;
1555 --
1556 function enb_exists
1557 (p_ELIG_PER_ELCTBL_CHC_ID number
1558 ,p_ORDR_NUM number
1559 ) return number is
1560 --
1561 l_enrt_bnft_id number;
1562 --
1563 begin
1564 --
1565 if g_unrest_enb_instance.count > 0 then
1566 --
1567 for i in g_unrest_enb_instance.first..g_unrest_enb_instance.last loop
1568 --
1569 if g_unrest_enb_instance(i).ELIG_PER_ELCTBL_CHC_ID = p_ELIG_PER_ELCTBL_CHC_ID and
1570 nvl(g_unrest_enb_instance(i).ORDR_NUM,-100) = nvl(p_ORDR_NUM,-100)
1571 then
1572 l_enrt_bnft_id := g_unrest_enb_instance(i).enrt_bnft_id;
1573 exit;
1574 end if;
1575 end loop;
1576 end if;
1577 hr_utility.set_location('ENrt Bnft id'||l_enrt_bnft_id,10);
1578 return l_enrt_bnft_id;
1579
1580 end;
1581 --
1582 function ecr_exists
1583 (p_ELIG_PER_ELCTBL_CHC_ID number
1584 ,p_enrt_bnft_id number
1585 ,p_acty_base_rt_id number
1586 ) return number is
1587 --
1588 l_enrt_rt_id number;
1589 --
1590 begin
1591 --
1592 hr_utility.set_location('Count '||g_unrest_ecr_instance.count,1);
1593 hr_utility.set_location('Electable choice Id'||p_ELIG_PER_ELCTBL_CHC_ID,2);
1594 hr_utility.set_location('Act base rt id'||p_acty_base_rt_id,3);
1595 hr_utility.set_location('Benefit Id'||p_enrt_bnft_id,4);
1596
1597
1598 if g_unrest_ecr_instance.count > 0 then
1599 --
1600 for i in g_unrest_ecr_instance.first..g_unrest_ecr_instance.last loop
1601 --
1602 if nvl(g_unrest_ecr_instance(i).ELIG_PER_ELCTBL_CHC_ID, -1) =
1603 nvl(p_ELIG_PER_ELCTBL_CHC_ID, -1) and
1604 nvl(g_unrest_ecr_instance(i).enrt_bnft_id,-100) = nvl(p_enrt_bnft_id,-100) and
1605 nvl(g_unrest_ecr_instance(i).acty_base_rt_id,-100) = nvl(p_acty_base_rt_id,-100)
1606 then
1607 l_enrt_rt_id := g_unrest_ecr_instance(i).enrt_rt_id;
1608 exit;
1609 end if;
1610 end loop;
1611 end if;
1612 hr_utility.set_location('ENrt Rate id'||l_enrt_rt_id,10);
1613 return l_enrt_rt_id;
1614
1615 end;
1616 --
1617 function epr_exists
1618 (p_ELIG_PER_ELCTBL_CHC_ID number
1619 ,p_enrt_bnft_id number
1620 ,p_ACTL_PREM_ID number
1621 ) return number is
1622 --
1623 l_enrt_prem_id number;
1624 --
1625 begin
1626 --
1627 if g_unrest_epr_instance.count > 0 then
1628 --
1629 for i in g_unrest_epr_instance.first..g_unrest_epr_instance.last loop
1630 --
1631 if nvl(g_unrest_epr_instance(i).ELIG_PER_ELCTBL_CHC_ID, -1) =
1632 nvl(p_ELIG_PER_ELCTBL_CHC_ID, -1) and
1633 nvl(g_unrest_epr_instance(i).enrt_bnft_id,-100) = nvl(p_enrt_bnft_id,-100) and
1634 nvl(g_unrest_epr_instance(i).ACTL_PREM_ID,-100) = nvl(p_ACTL_PREM_ID,-100)
1635 then
1636 l_enrt_prem_id := g_unrest_epr_instance(i).enrt_prem_id;
1637 exit;
1638 end if;
1639 end loop;
1640 end if;
1641 hr_utility.set_location('ENrt Premium id'||l_enrt_prem_id,10);
1642 return l_enrt_prem_id;
1643
1644 end;
1645
1646 function egd_exists
1647 (p_PER_IN_LER_ID number
1648 ,p_ELIG_PER_ID number
1649 ,p_ELIG_PER_OPT_ID number
1650 ,p_DPNT_PERSON_ID number
1651 ) return number is
1652 --
1653 l_elig_dpnt_id number;
1654 --
1655 begin
1656 --
1657 hr_utility.set_location('Egd Exists',10);
1658 hr_utility.set_location('Egd count'||g_unrest_egd_instance.count,11);
1659 if g_unrest_egd_instance.count > 0 then
1660 --
1661 for i in g_unrest_egd_instance.first..g_unrest_egd_instance.last loop
1662 hr_utility.set_location('per_in ler id'||g_unrest_egd_instance(i).PER_IN_LER_ID,12);
1663 --
1664 if g_unrest_egd_instance(i).PER_IN_LER_ID = p_PER_IN_LER_ID and
1665 nvl(g_unrest_egd_instance(i).ELIG_PER_ID,-100) = nvl(p_ELIG_PER_ID,-100) and
1666 nvl(g_unrest_egd_instance(i).ELIG_PER_OPT_ID,-1) = nvl(p_ELIG_PER_OPT_ID,-1) and
1667 g_unrest_egd_instance(i).DPNT_PERSON_ID = p_DPNT_PERSON_ID
1668 then
1669 l_elig_dpnt_id := g_unrest_egd_instance(i).elig_dpnt_id;
1670 exit;
1671 end if;
1672 end loop;
1673 end if;
1674 hr_utility.set_location('Elig dpnt id'||l_elig_dpnt_id,10);
1675 return l_elig_dpnt_id;
1676
1677 end;
1678 --
1679 function ecc_exists
1680 ( p_ELIG_PER_ELCTBL_CHC_ID number default null
1681 ,p_enrt_bnft_id number default null
1682 ,p_ENRT_CTFN_TYP_CD varchar2
1683 ) return number is
1684 --
1685 l_enrt_ctfn_id number;
1686 --
1687 begin
1688 --
1689 if g_unrest_ecc_instance.count > 0 then
1690 --
1691 for i in g_unrest_ecc_instance.first..g_unrest_ecc_instance.last loop
1692 --
1693 if nvl(g_unrest_ecc_instance(i).ELIG_PER_ELCTBL_CHC_ID, -1) =
1694 nvl(p_ELIG_PER_ELCTBL_CHC_ID, -1) and
1695 nvl(g_unrest_ecc_instance(i).enrt_bnft_id,-100) = nvl(p_enrt_bnft_id,-100) and
1696 nvl(g_unrest_ecc_instance(i).ENRT_CTFN_TYP_CD,-100) = nvl(p_ENRT_CTFN_TYP_CD,-100)
1697 then
1698 l_enrt_ctfn_id := g_unrest_ecc_instance(i).elctbl_chc_ctfn_id;
1699 exit;
1700 end if;
1701 end loop;
1702 end if;
1703 hr_utility.set_location('Chc Cert id'||l_enrt_ctfn_id,10);
1704 return l_enrt_ctfn_id;
1705 --
1706 end;
1707 --
1708 procedure update_enrt_ctfn
1709 ( p_elctbl_chc_ctfn_id in number
1710 ,p_enrt_ctfn_typ_cd in varchar2
1711 ,p_rqd_flag in varchar2
1712 ,p_elig_per_elctbl_chc_id in number
1713 ,p_enrt_bnft_id in number
1714 ,p_business_group_id in number
1715 ,p_ecc_attribute_category in varchar2
1716 ,p_ecc_attribute1 in varchar2
1717 ,p_ecc_attribute2 in varchar2
1718 ,p_ecc_attribute3 in varchar2
1719 ,p_ecc_attribute4 in varchar2
1720 ,p_ecc_attribute5 in varchar2
1721 ,p_ecc_attribute6 in varchar2
1722 ,p_ecc_attribute7 in varchar2
1723 ,p_ecc_attribute8 in varchar2
1724 ,p_ecc_attribute9 in varchar2
1725 ,p_ecc_attribute10 in varchar2
1726 ,p_ecc_attribute11 in varchar2
1727 ,p_ecc_attribute12 in varchar2
1728 ,p_ecc_attribute13 in varchar2
1729 ,p_ecc_attribute14 in varchar2
1730 ,p_ecc_attribute15 in varchar2
1731 ,p_ecc_attribute16 in varchar2
1732 ,p_ecc_attribute17 in varchar2
1733 ,p_ecc_attribute18 in varchar2
1734 ,p_ecc_attribute19 in varchar2
1735 ,p_ecc_attribute20 in varchar2
1736 ,p_ecc_attribute21 in varchar2
1737 ,p_ecc_attribute22 in varchar2
1738 ,p_ecc_attribute23 in varchar2
1739 ,p_ecc_attribute24 in varchar2
1740 ,p_ecc_attribute25 in varchar2
1741 ,p_ecc_attribute26 in varchar2
1742 ,p_ecc_attribute27 in varchar2
1743 ,p_ecc_attribute28 in varchar2
1744 ,p_ecc_attribute29 in varchar2
1745 ,p_ecc_attribute30 in varchar2
1746 ,p_susp_if_ctfn_not_prvd_flag in varchar2
1747 ,p_ctfn_determine_cd in varchar2
1748 ,p_request_id in number
1749 ,p_program_application_id in number
1750 ,p_program_id in number
1751 ,p_program_update_date in date
1752 ,p_object_version_number in out nocopy number
1753 ,p_effective_date in date
1754 ) is
1755 --
1756 l_object_version_number number;
1757 --
1758 begin
1759 --
1760 if g_unrest_ecc_instance.count > 0 then
1761 --
1762 for i in g_unrest_ecc_instance.first..g_unrest_ecc_instance.last loop
1763 if g_unrest_ecc_instance(i).elctbl_chc_ctfn_id = p_elctbl_chc_ctfn_id then
1764 l_object_version_number := g_unrest_ecc_instance(i).object_version_number;
1765 g_unrest_ecc_instance(i).mark_delete := 'Y';
1766 end if;
1767 --
1768 end loop;
1769 --
1770 end if;
1771 --
1772 ben_ELTBL_CHC_CTFN_api.update_ELTBL_CHC_CTFN
1773 (p_elctbl_chc_ctfn_id => p_elctbl_chc_ctfn_id
1774 ,p_enrt_ctfn_typ_cd => p_enrt_ctfn_typ_cd
1775 ,p_rqd_flag => p_rqd_flag
1776 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1777 ,p_enrt_bnft_id => p_enrt_bnft_id
1778 ,p_business_group_id => p_business_group_id
1779 ,p_ecc_attribute_category => p_ecc_attribute_category
1780 ,p_ecc_attribute1 => p_ecc_attribute1
1781 ,p_ecc_attribute2 => p_ecc_attribute2
1782 ,p_ecc_attribute3 => p_ecc_attribute3
1783 ,p_ecc_attribute4 => p_ecc_attribute4
1784 ,p_ecc_attribute5 => p_ecc_attribute5
1785 ,p_ecc_attribute6 => p_ecc_attribute6
1786 ,p_ecc_attribute7 => p_ecc_attribute7
1787 ,p_ecc_attribute8 => p_ecc_attribute8
1788 ,p_ecc_attribute9 => p_ecc_attribute9
1789 ,p_ecc_attribute10 => p_ecc_attribute10
1790 ,p_ecc_attribute11 => p_ecc_attribute11
1791 ,p_ecc_attribute12 => p_ecc_attribute12
1792 ,p_ecc_attribute13 => p_ecc_attribute13
1793 ,p_ecc_attribute14 => p_ecc_attribute14
1794 ,p_ecc_attribute15 => p_ecc_attribute15
1795 ,p_ecc_attribute16 => p_ecc_attribute16
1796 ,p_ecc_attribute17 => p_ecc_attribute17
1797 ,p_ecc_attribute18 => p_ecc_attribute18
1798 ,p_ecc_attribute19 => p_ecc_attribute19
1799 ,p_ecc_attribute20 => p_ecc_attribute20
1800 ,p_ecc_attribute21 => p_ecc_attribute21
1801 ,p_ecc_attribute22 => p_ecc_attribute22
1802 ,p_ecc_attribute23 => p_ecc_attribute23
1803 ,p_ecc_attribute24 => p_ecc_attribute24
1804 ,p_ecc_attribute25 => p_ecc_attribute25
1805 ,p_ecc_attribute26 => p_ecc_attribute26
1806 ,p_ecc_attribute27 => p_ecc_attribute27
1807 ,p_ecc_attribute28 => p_ecc_attribute28
1808 ,p_ecc_attribute29 => p_ecc_attribute29
1809 ,p_ecc_attribute30 => p_ecc_attribute30
1810 ,p_susp_if_ctfn_not_prvd_flag => p_susp_if_ctfn_not_prvd_flag
1811 ,p_ctfn_determine_cd => p_ctfn_determine_cd
1812 ,p_request_id => p_request_id
1813 ,p_program_application_id => p_program_application_id
1814 ,p_program_id => p_program_id
1815 ,p_program_update_date => p_program_update_date
1816 ,p_object_version_number => l_object_version_number
1817 ,p_effective_date => trunc(p_effective_date)
1818 );
1819 --
1820 p_object_version_number := l_object_version_number;
1821 end;
1822 --
1823 procedure clear_cache is
1824 --
1825 begin
1826 --
1827 if g_unrest_ecr_instance.count > 0 then
1828 --
1829 for i in g_unrest_ecr_instance.first..g_unrest_ecr_instance.last loop
1830 --
1831 if g_unrest_ecr_instance(i).mark_delete is null then
1832 --
1833 delete from ben_enrt_rt where enrt_rt_id = g_unrest_ecr_instance(i).enrt_rt_id;
1834 --
1835 end if;
1836 --
1837 end loop;
1838 --
1839 end if;
1840 --
1841 if g_unrest_epr_instance.count > 0 then
1842 --
1843 for i in g_unrest_epr_instance.first..g_unrest_epr_instance.last loop
1844 --
1845 if g_unrest_epr_instance(i).mark_delete is null then
1846 --
1847 delete from ben_enrt_prem where enrt_prem_id = g_unrest_epr_instance(i).enrt_prem_id;
1848 --
1849 end if;
1850 --
1851 end loop;
1852 --
1853 end if;
1854 --
1855 if g_unrest_ecc_instance.count > 0 then
1856 --
1857 for i in g_unrest_ecc_instance.first..g_unrest_ecc_instance.last loop
1858 --
1859 if g_unrest_ecc_instance(i).mark_delete is null then
1860 --
1861 delete from ben_elctbl_chc_ctfn where elctbl_chc_ctfn_id =
1862 g_unrest_ecc_instance(i).elctbl_chc_ctfn_id;
1863 --
1864 end if;
1865 --
1866 end loop;
1867 --
1868 end if;
1869 --
1870 if g_unrest_enb_instance.count > 0 then
1871 --
1872 for i in g_unrest_enb_instance.first..g_unrest_enb_instance.last loop
1873 --
1874 if g_unrest_enb_instance(i).mark_delete is null then
1875 --
1876 delete from ben_enrt_bnft where enrt_bnft_id = g_unrest_enb_instance(i).enrt_bnft_id;
1877 --
1878 end if;
1879 --
1880 end loop;
1881 --
1882 end if;
1883 --
1884 if g_unrest_egd_instance.count > 0 then
1885 --
1886 for i in g_unrest_egd_instance.first..g_unrest_egd_instance.last loop
1887 --
1888 if g_unrest_egd_instance(i).mark_delete is null then
1889 --
1890 delete from ben_elig_dpnt where elig_dpnt_id = g_unrest_egd_instance(i).elig_dpnt_id;
1891 --
1892 end if;
1893 --
1894 end loop;
1895 --
1896 end if;
1897 --
1898 if g_unrest_epe_instance.count > 0 then
1899 --
1900 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1901 --
1902 if g_unrest_epe_instance(i).mark_delete is null then
1903 --
1904 hr_utility.set_location ('Delete Elig Id '||g_unrest_epe_instance(i).elig_per_elctbl_chc_id,11);
1905 delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =
1906 g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1907 --
1908 end if;
1909 --
1910 end loop;
1911 --
1912 end if;
1913 --
1914 g_unrest_epe_instance.delete;
1915 g_unrest_enb_instance.delete;
1916 g_unrest_ecr_instance.delete;
1917 g_unrest_egd_instance.delete;
1918 g_unrest_ecc_instance.delete;
1919 g_unrest_epr_instance.delete;
1920 --
1921 end;
1922 --
1923 procedure clear_epe_cache is
1924 -- Added during bug fix 4640014
1925 l_unrest_epe_instance g_unrest_epe_inst_tbl;
1926 l_idx number := 1 ;
1927 --
1928 begin
1929 --
1930 if g_unrest_epe_instance.count > 0 then
1931 --
1932 for i in g_unrest_epe_instance.first..g_unrest_epe_instance.last loop
1933 --
1934 if g_unrest_epe_instance(i).mark_delete is null
1935 and g_unrest_epe_instance(i).comp_lvl_cd in ('OIPL','PLAN') then
1936 -- and g_unrest_epe_instance(i).elctbl_flag = 'Y' -- bug 4761065 : Need to delete epe regardless whether its elctbl_flag is Y/N .
1937 --
1938 hr_utility.set_location ('Delete Elig Id in clear_epe '||g_unrest_epe_instance(i).elig_per_elctbl_chc_id,11);
1939 delete from ben_elig_per_elctbl_chc where elig_per_elctbl_chc_id =
1940 g_unrest_epe_instance(i).elig_per_elctbl_chc_id;
1941 --
1942 else
1943 --
1944 l_unrest_epe_instance(l_idx) := g_unrest_epe_instance(i) ;
1945 l_idx := l_idx + 1;
1946 --
1947 end if;
1948 --
1949 end loop;
1950 --
1951 g_unrest_epe_instance := l_unrest_epe_instance ;
1952 --
1953 end if;
1954 --
1955 end clear_epe_cache;
1956 --
1957 -- Bug 5055119
1958
1959 PROCEDURE end_date_elig_per_rows (
1960 p_person_id IN NUMBER,
1961 p_per_in_ler_id IN NUMBER,
1962 p_effective_date IN DATE
1963 )
1964 IS
1965 /**
1966 Get the new peps which has corresponding future pep
1967 .If found then end date new pep to one day before effective start date of
1968 future counterpart pep
1969 **/
1970 CURSOR get_pep
1971 IS
1972 SELECT new_pep.elig_per_id, new_pep.object_version_number,
1973 MIN (old_pep.effective_start_date) - 1 end_date
1974 FROM ben_elig_per_f new_pep, ben_elig_per_f old_pep
1975 WHERE new_pep.person_id = p_person_id
1976 AND old_pep.person_id = new_pep.person_id
1977 AND new_pep.per_in_ler_id = p_per_in_ler_id
1978 AND old_pep.per_in_ler_id = new_pep.per_in_ler_id
1979 AND ( ( NVL (new_pep.pgm_id, -1) = NVL (old_pep.pgm_id, -2)
1980 AND new_pep.pl_id IS NULL
1981 AND new_pep.plip_id IS NULL
1982 AND new_pep.ptip_id IS NULL
1983 AND old_pep.pl_id IS NULL
1984 AND old_pep.plip_id IS NULL
1985 AND old_pep.ptip_id IS NULL
1986 )
1987 OR ( NVL (old_pep.pl_id, -1) = NVL (new_pep.pl_id, -2)
1988 -- added bug 5658405
1989 AND NVL (new_pep.pgm_id, -1) = NVL (old_pep.pgm_id, -1)
1990 )
1991 OR NVL (old_pep.plip_id, -1) = NVL (new_pep.plip_id, -2)
1992 OR NVL (old_pep.ptip_id, -1) = NVL (new_pep.ptip_id, -2)
1993 )
1994 AND old_pep.effective_start_date > p_effective_date
1995 AND old_pep.effective_start_date > new_pep.effective_start_date
1996 AND new_pep.effective_start_date = p_effective_date
1997 AND new_pep.effective_end_date = hr_api.g_eot
1998 GROUP BY new_pep.elig_per_id, new_pep.object_version_number;
1999
2000 /**
2001 Get the new child epos of new peps which has corresponding future pep
2002 .If found then end date new epos to one day before effective start date of
2003 future parent pep
2004 **/
2005 CURSOR get_epo (v_elig_per_id IN NUMBER
2006 -- added 5658405
2007 , v_end_date IN DATE)
2008 IS
2009 SELECT new_epo.elig_per_opt_id, new_epo.object_version_number,
2010 effective_start_date, effective_end_date
2011 FROM ben_elig_per_opt_f new_epo
2012 WHERE new_epo.elig_per_id = v_elig_per_id
2013 -- bug 5658405
2014 -- AND new_epo.per_in_ler_id = p_per_in_ler_id
2015 -- AND new_epo.effective_start_date = p_effective_date
2016 -- AND new_epo.effective_end_date = hr_api.g_eot;
2017 AND v_end_date BETWEEN new_epo.effective_start_date
2018 AND new_epo.effective_end_date;
2019
2020 -- added bug 5658405
2021 CURSOR get_future_epo (v_elig_per_id IN NUMBER, v_end_date IN DATE)
2022 IS
2023 SELECT future_epo.elig_per_opt_id, future_epo.object_version_number
2024 FROM ben_elig_per_opt_f future_epo
2025 WHERE elig_per_id = v_elig_per_id AND effective_end_date > v_end_date;
2026
2027 l_effective_start_date DATE;
2028 l_effective_end_date DATE;
2029 l_epo get_epo%ROWTYPE;
2030 l_pep get_pep%ROWTYPE;
2031
2032 future_epo_rec get_future_epo%ROWTYPE;
2033 ---- end bug 5658405
2034 /***/
2035 BEGIN
2036 hr_utility.set_location
2037 ('Entering ben_manage_unres_life_events.end_date_elig_per_rows',
2038 10
2039 );
2040
2041 OPEN get_pep;
2042
2043 LOOP
2044 FETCH get_pep
2045 INTO l_pep;
2046
2047 EXIT WHEN get_pep%NOTFOUND;
2048
2049 /**get its child **/
2050 -- OPEN get_epo (l_pep.elig_per_id);
2051 hr_utility.set_location
2052 ( '******************* l_pep.elig_per_id '
2053 || l_pep.elig_per_id,
2054 12.12
2055 );
2056
2057 OPEN get_epo (l_pep.elig_per_id, l_pep.end_date);
2058
2059 LOOP
2060 FETCH get_epo
2061 INTO l_epo;
2062
2063 EXIT WHEN get_epo%NOTFOUND;
2064 hr_utility.set_location ( 'End-dating elig_per_opt_id '
2065 || l_epo.elig_per_opt_id
2066 || ' to '
2067 || l_pep.end_date,
2068 9909
2069 );
2070 hr_utility.set_location ( l_epo.elig_per_opt_id
2071 || ' : '
2072 || TO_CHAR (l_pep.end_date)
2073 || ' : '
2074 || l_epo.object_version_number
2075 || ' : '
2076 || TO_CHAR (l_epo.effective_start_date)
2077 || ' : '
2078 || TO_CHAR (l_epo.effective_end_date),
2079 12.12
2080 );
2081 ben_elig_person_option_api.delete_elig_person_option
2082 (p_elig_per_opt_id => l_epo.elig_per_opt_id,
2083 p_object_version_number => l_epo.object_version_number,
2084 p_effective_start_date => l_effective_start_date,
2085 p_effective_end_date => l_effective_end_date,
2086 p_effective_date => l_pep.end_date,
2087 p_datetrack_mode => 'DELETE'
2088 );
2089 END LOOP;
2090
2091 CLOSE get_epo;
2092
2093 -- bug 5658405
2094 OPEN get_future_epo (l_pep.elig_per_id, l_pep.end_date);
2095 -- end dating the future epos .. otherwise these will have no parent pep
2096 -- and these will not end date the pep records.
2097
2098 LOOP
2099 FETCH get_future_epo
2100 INTO future_epo_rec;
2101
2102 EXIT WHEN get_future_epo%NOTFOUND;
2103 hr_utility.set_location ( 'End-dating elig_per_opt_id update'
2104 || future_epo_rec.elig_per_opt_id
2105 || ' to '
2106 || l_pep.end_date,
2107 9909
2108 );
2109
2110 UPDATE ben_elig_per_opt_f
2111 -- updating the elig_per_opt_f
2112 -- as these cant be end dated using api.
2113 SET effective_end_date = l_pep.end_date
2114 WHERE elig_per_opt_id = future_epo_rec.elig_per_opt_id
2115 AND object_version_number = future_epo_rec.object_version_number;
2116 END LOOP;
2117
2118 CLOSE get_future_epo;
2119
2120 hr_utility.set_location ( 'End-dating elig_per_id '
2121 || l_pep.elig_per_id
2122 || ' to '
2123 || l_pep.end_date,
2124 9909
2125 );
2126 hr_utility.set_location ( 'per_in_ler_id'
2127 || ' : '
2128 || 'elig_per_opt_id'
2129 || ' : '
2130 || 'effective_start_date'
2131 || ' : '
2132 || 'effective_end_date',
2133 12.12
2134 );
2135
2136 hr_utility.set_location ( '=>l_pep.elig_per_id '
2137 || l_pep.elig_per_id
2138 || ' '
2139 || l_pep.object_version_number,
2140 12.12
2141 );
2142
2143 ben_eligible_person_api.delete_eligible_person
2144 (p_elig_per_id => l_pep.elig_per_id,
2145 p_object_version_number => l_pep.object_version_number,
2146 p_effective_date => l_pep.end_date,
2147 p_effective_start_date => l_effective_start_date,
2148 p_effective_end_date => l_effective_end_date,
2149 p_datetrack_mode => 'DELETE'
2150 );
2151 hr_utility.set_location ( 'After ending pep '
2152 || l_pep.elig_per_id
2153 || ' '
2154 || TO_CHAR (l_effective_start_date)
2155 || ' '
2156 || TO_CHAR (l_effective_end_date),
2157 12.12
2158 );
2159 END LOOP;
2160
2161 CLOSE get_pep;
2162
2163 hr_utility.set_location
2164 ('Leaving ben_manage_unres_life_events.end_date_elig_per_rows',
2165 20
2166 );
2167 END end_date_elig_per_rows;
2168
2169 end ben_manage_unres_life_events;