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