[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIG_PER_ELC_CHC_API
Source
1 Package Body ben_elig_per_elc_chc_api as
2 /* $Header: beepeapi.pkb 120.1.12020000.2 2012/07/03 12:10:57 amnaraya ship $ */
3 --
4 -- Package Variables
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 g_package varchar2(33) := ' ben_elig_per_elc_chc_api.';
8 --
9 -- ----------------------------------------------------------------------------
10 -- |----------------------< CreOrSel_pil_elctbl_chc_popl >--------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 -- Create or select ben_pil_elctbl_chc_popl
14 --
15 procedure CreOrSel_pil_elctbl_chc_popl
16 (p_per_in_ler_id in number
17 ,p_effective_date in date
18 ,p_business_group_id in number
19 ,p_pgm_id in number
20 ,p_plip_id in number
21 ,p_pl_id in number
22 ,p_oipl_id in number
23 ,p_yr_perd_id in number
24 ,p_uom in varchar2
25 ,p_acty_ref_perd_cd in varchar2
26 ,p_dflt_enrt_dt in date
27 ,p_cls_enrt_dt_to_use_cd in varchar2
28 ,p_enrt_typ_cycl_cd in varchar2
29 ,p_enrt_perd_end_dt in date
30 ,p_enrt_perd_strt_dt in date
31 ,p_procg_end_dt in date
35 ,p_program_application_id in number
32 ,p_lee_rsn_id in number
33 ,p_enrt_perd_id in number
34 ,p_request_id in number
36 ,p_program_id in number
37 ,p_program_update_date in date
38 ,p_ws_mgr_id in number
39 ,p_assignment_id in number
40 --
41 ,p_pil_elctbl_chc_popl_id out nocopy number
42 ,p_oiplip_id out nocopy number
43 )
44 is
45 --
46 -- Declare cursors and local variables
47 --
48 l_proc varchar2(72) ;
49 --
50 l_elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%TYPE;
51 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
52 l_cbr_elig_perd_strt_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_strt_dt%TYPE;
53 l_cbr_elig_perd_end_dt ben_pil_elctbl_chc_popl.cbr_elig_perd_end_dt%TYPE;
54 l_pel_pl_id number;
55 l_pel_pgm_id number;
56 l_pil_elctbl_chc_popl_id number;
57 l_pel_ovn number;
58 l_pel_dflt_enrt_dt date;
59 l_pel_cls_enrt_dt_to_use_cd varchar2(30);
60 l_pel_enrt_typ_cycl_cd varchar2(30);
61 l_pel_enrt_perd_strt_dt date;
62 l_pel_enrt_perd_end_dt date;
63 l_pel_lee_rsn_id number;
64 l_pel_enrt_perd_id number;
65 l_pel_procg_end_dt date;
66 l_pel_uom varchar2(30):=p_uom;
67 l_pel_acty_ref_perd_cd varchar2(30):=p_acty_ref_perd_cd;
68 l_pel_create boolean;
69 l_update_pel boolean;
70 l_ws_iss_dt date;
71 l_bdgt_iss_dt date;
72 --
73 -- Cursors
74 --
75 cursor c_pel_at_pgm_level is
76 select
77 pil_elctbl_chc_popl_id,
78 object_version_number,
79 dflt_enrt_dt,
80 cls_enrt_dt_to_use_cd,
81 enrt_typ_cycl_cd,
82 enrt_perd_strt_dt,
83 enrt_perd_end_dt,
84 lee_rsn_id,
85 enrt_perd_id,
86 procg_end_dt,
87 uom,
88 acty_ref_perd_cd
89 from ben_pil_elctbl_chc_popl
90 where
91 per_in_ler_id=p_per_in_ler_id and
92 pgm_id=l_pel_pgm_id
93 ;
94 --
95 cursor c_pel_at_pl_level is
96 select
97 pil_elctbl_chc_popl_id,
98 object_version_number,
99 dflt_enrt_dt,
100 cls_enrt_dt_to_use_cd,
101 enrt_typ_cycl_cd,
102 enrt_perd_strt_dt,
103 enrt_perd_end_dt,
104 lee_rsn_id,
105 enrt_perd_id,
106 procg_end_dt,
107 uom,
108 acty_ref_perd_cd
109 from ben_pil_elctbl_chc_popl
110 where
111 per_in_ler_id=p_per_in_ler_id and
112 pl_id=l_pel_pl_id
113 ;
114
115 cursor get_oiplip is
116 select oiplip_id
117 from ben_oiplip_f oi
118 where oi.oipl_id = p_oipl_id
119 and oi.plip_id = p_plip_id
120 and p_effective_date between
121 oi.effective_start_date and oi.effective_end_date;
122 l_oiplip_id number;
123 --
124 cursor c_yrp is
125 select yrp.end_date
126 from ben_yr_perd yrp
127 where yrp.yr_perd_id = p_yr_perd_id
128 and yrp.business_group_id = p_business_group_id;
129 l_yr_perd_end_dt date := null;
130 --
131 --
132 -- Bug 2174005
133 --
134 cursor c_enp is
135 select enp.DFLT_WS_ACC_CD ,enp.ler_id,
136 enp.WS_UPD_END_DT, enp.BDGT_UPD_END_DT,
137 enp.auto_distr_flag,
138 enp.reinstate_cd,
139 enp.reinstate_ovrdn_cd,
140 enp.reopen_le_on_reprocess
141 from ben_enrt_perd enp
142 where enrt_perd_id = p_enrt_perd_id;
143 --
144 cursor c_len(c_effective_date date,
145 c_lee_rsn_id number) is
146 select len.reinstate_cd,
147 len.reinstate_ovrdn_cd,
148 len.reopen_le_on_reprocess
149 from ben_lee_rsn_f len
150 where len.lee_rsn_id = c_lee_rsn_id
151 and c_effective_date between len.effective_start_date
152 and len.effective_end_date ;
153 CURSOR c_abr_pl(
154 c_pl_id number,
155 c_effective_date DATE) IS
156 SELECT 'Y'
157 FROM ben_acty_base_rt_f abr
158 WHERE abr.acty_base_rt_stat_cd = 'A'
159 AND abr.pl_id = c_pl_id
160 AND abr.rt_usg_cd <> 'FLXCR'
161 and abr.acty_typ_cd in ('CWBWB','CWBDB')
162 AND c_effective_date BETWEEN abr.effective_start_date
163 AND abr.effective_end_date;
164 --
165 CURSOR c_abr_oipl(
166 c_oipl_id number,
167 c_effective_date DATE) IS
168 SELECT 'Y'
169 FROM ben_acty_base_rt_f abr
170 WHERE abr.acty_base_rt_stat_cd = 'A'
171 AND abr.oipl_id = c_oipl_id
172 AND abr.rt_usg_cd <> 'FLXCR'
173 and abr.acty_typ_cd in ('CWBWB','CWBDB')
174 AND c_effective_date BETWEEN abr.effective_start_date
175 AND abr.effective_end_date;
176 cursor c_oipl (p_pl_id number) is
177 select opt.oipl_id
178 from ben_oipl_f opt
179 where pl_id = p_pl_id
180 and p_effective_date between opt.effective_start_date
181 and opt.effective_end_date;
182 --
183
184 l_enp_rec c_enp%rowtype;
185 l_bdgt_acc_cd varchar2(30);
186 l_ws_acc_cd varchar2(30);
187 l_bdgt_stat_cd varchar2(30);
188 l_ws_stat_cd varchar2(30);
189 l_pop_cd varchar2(30);
193 l_oipl_id number;
190 l_ws_upd_end_dt date;
191 l_bdgt_upd_end_dt date;
192 l_auto_distr_flag varchar2(1) := 'N';
194 --Bug 4068639
195 l_enrt_perd_id number;
196 l_pgm_id number;
197 l_pl_id number;
198 l_lee_rsn_id number;
199 l_cls_enrt_dt_to_use_cd varchar2(30);
200 l_acty_ref_perd_cd varchar2(30);
201 l_uom varchar2(30);
202 l_dflt_enrt_dt date;
203 l_enrt_perd_end_dt date;
204 l_enrt_perd_strt_dt date;
205 l_procg_end_dt date;
206 l_reinstate_cd varchar2(30);
207 l_reinstate_ovrdn_cd varchar2(30);
208 l_reopen_le_on_reprocess varchar2(30);
209 --
210 begin
211 hr_utility.set_location('Entering:'|| l_proc, 10);
212 --
213 if g_debug then
214 l_proc := g_package||'CreOrSel_pil_elctbl_chc_popl';
215 --
216 hr_utility.set_location('Entering:'|| l_proc, 10);
217 hr_utility.set_location(' p_pgm_id '||p_pgm_id,120);
218 hr_utility.set_location(' p_pl_id '||p_pl_id,120);
219 hr_utility.set_location(' p_lee_rsn_id '||p_lee_rsn_id,120);
220 hr_utility.set_location(' p_enrt_perd_id '||p_enrt_perd_id,120);
221 --
222 end if;
223 --
224 -- Create or find ben_pil_elctbl_chc_popl
225 --
226 -- Determine if plan or program level record is needed
227 --
228 if p_pgm_id is not null then
229 l_pel_pgm_id:=p_pgm_id;
230 l_pel_pl_id:=null;
231 open c_pel_at_pgm_level;
232 fetch c_pel_at_pgm_level into
233 l_pil_elctbl_chc_popl_id,
234 l_pel_ovn,
235 l_pel_dflt_enrt_dt,
236 l_pel_cls_enrt_dt_to_use_cd,
237 l_pel_enrt_typ_cycl_cd,
238 l_pel_enrt_perd_strt_dt,
239 l_pel_enrt_perd_end_dt,
240 l_pel_lee_rsn_id,
241 l_pel_enrt_perd_id,
242 l_pel_procg_end_dt,
243 l_pel_uom,
244 l_pel_acty_ref_perd_cd;
245 l_pel_create:=c_pel_at_pgm_level%notfound;
246 close c_pel_at_pgm_level;
247 elsif p_pl_id is not null then -- and pgm_id is null
248 l_pel_pgm_id:=null;
249 l_pel_pl_id:=p_pl_id;
250 open c_pel_at_pl_level;
251 fetch c_pel_at_pl_level into
252 l_pil_elctbl_chc_popl_id,
253 l_pel_ovn,
254 l_pel_dflt_enrt_dt,
255 l_pel_cls_enrt_dt_to_use_cd,
256 l_pel_enrt_typ_cycl_cd,
257 l_pel_enrt_perd_strt_dt,
258 l_pel_enrt_perd_end_dt,
259 l_pel_lee_rsn_id,
260 l_pel_enrt_perd_id,
261 l_pel_procg_end_dt,
262 l_pel_uom,
263 l_pel_acty_ref_perd_cd;
264 l_pel_create:=c_pel_at_pl_level%notfound;
265 close c_pel_at_pl_level;
266 else -- big trouble, neither is filled in error.
267 if g_debug then
268 hr_utility.set_location('BEN_?????_EPE_PL_OR_PGM', 22);
269 end if;
270 fnd_message.set_name('BEN','BEN_?????_EPE_PL_OR_PGM');
271 fnd_message.raise_error;
272 end if;
273 --
274 if l_pel_create then
275 --
276 if g_debug then
277 hr_utility.set_location('BPECPAPI_CRE: '|| l_proc, 10);
278 end if;
279 --
280 -- Bug 2174005
281 --
282 open c_enp;
283 fetch c_enp into l_enp_rec;
284 --
285 if c_enp%found then
286 --
287 if g_debug then
288 hr_utility.set_location('Enp found',11);
289 end if;
290 if l_enp_rec.ler_id is not null then
291 -- It is compensation Work Bench type of plan.
292 --
293 l_bdgt_acc_cd := 'NA'; -- Not Available
294 l_ws_acc_cd := l_enp_rec.DFLT_WS_ACC_CD;
295 l_bdgt_stat_cd := 'NS'; -- Not Started
296 l_ws_stat_cd := 'NS'; -- Not Started
297 l_pop_cd := 'D'; -- Direct Managers
298 l_ws_upd_end_dt := l_enp_rec.ws_upd_end_dt;
299 l_bdgt_upd_end_dt := l_enp_rec.bdgt_upd_end_dt;
300 --
301 if l_enp_rec.auto_distr_flag = 'Y' then
302 open c_oipl (p_pl_id);
303 fetch c_oipl into l_oipl_id;
304 close c_oipl;
305 --
306 if l_oipl_id is not null then
307 --
308 open c_abr_oipl (l_oipl_id ,p_effective_date);
309 fetch c_abr_oipl into l_auto_distr_flag;
310 close c_abr_oipl;
311 --
312 else
313 --
314 open c_abr_pl (p_pl_id, p_effective_date);
315 fetch c_abr_pl into l_auto_distr_flag;
316 close c_abr_pl;
317 end if;
318 --hr_utility.set_location ('Auto distribute'||l_auto_distr_flag,12);
319 if l_auto_distr_flag = 'Y' then
320 l_bdgt_stat_cd := 'IS';
321 --l_ws_stat_cd := 'IS';
322 l_ws_iss_dt := p_effective_date;
323 l_bdgt_iss_dt := p_effective_date;
324 end if;
325 --
326 end if;
327
328 end if;
329 l_reinstate_cd := l_enp_rec.reinstate_cd;
330 l_reinstate_ovrdn_cd:= l_enp_rec.reinstate_ovrdn_cd;
331 l_reopen_le_on_reprocess := l_enp_rec.reopen_le_on_reprocess;
332 --
333 end if;
334 --
335 -- Bug 2174005
336 --
337 close c_enp;
338 --
339 if p_lee_rsn_id is not null then
340 --
341 open c_len(p_effective_date,p_lee_rsn_id) ;
345 end if;
342 fetch c_len into l_reinstate_cd,l_reinstate_ovrdn_cd,l_reopen_le_on_reprocess;
343 close c_len ;
344 --
346 --
347 --Bug 4068639 : When Creating PIL record, if some of the parameters
348 --are erroneously set to default(values from HR_API package),
349 --then pass their default value in create_Pil_Elctbl_chc_Popl procedure.
350
351 if p_enrt_perd_id = hr_api.g_number then --p_enrt_perd_id
352 l_enrt_perd_id := null;
353 else
354 l_enrt_perd_id := p_enrt_perd_id;
355 end if;
356
357 if l_pel_pgm_id = hr_api.g_number then --p_pgm_id
358 l_pgm_id := null;
359 else
360 l_pgm_id := l_pel_pgm_id;
361 end if;
362
363 if l_pel_pl_id = hr_api.g_number then --p_pl_id
364 l_pl_id := null;
365 else
366 l_pl_id := l_pel_pl_id;
367 end if;
368
369 if p_lee_rsn_id = hr_api.g_number then --p_lee_rsn_id
370 l_lee_rsn_id := null;
371 else
375 if l_pel_uom = hr_api.g_varchar2 then --p_uom
372 l_lee_rsn_id := p_lee_rsn_id;
373 end if;
374
376 l_uom := null;
377 else
378 l_uom := l_pel_uom;
379 end if;
380
381 if p_cls_enrt_dt_to_use_cd = hr_api.g_varchar2 then --p_cls_enrt_dt_to_use_cd
382 l_cls_enrt_dt_to_use_cd := null;
383 else
384 l_cls_enrt_dt_to_use_cd := p_cls_enrt_dt_to_use_cd;
385 end if;
386
387 if l_pel_acty_ref_perd_cd = hr_api.g_varchar2 then --p_acty_ref_perd_cd
388 l_acty_ref_perd_cd := null;
389 else
390 l_acty_ref_perd_cd := l_pel_acty_ref_perd_cd;
391 end if;
392
393 if p_dflt_enrt_dt = hr_api.g_date then --p_dflt_enrt_dt
394 l_dflt_enrt_dt := null;
395 else
396 l_dflt_enrt_dt := p_dflt_enrt_dt;
397 end if;
398
399 if p_enrt_perd_end_dt = hr_api.g_date then --p_enrt_perd_end_dt
400 l_enrt_perd_end_dt := null;
401 else
402 l_enrt_perd_end_dt := p_enrt_perd_end_dt;
403 end if;
404
405 if p_enrt_perd_strt_dt = hr_api.g_date then --p_enrt_perd_strt_dt
406 l_enrt_perd_strt_dt := null;
407 else
408 l_enrt_perd_strt_dt := p_enrt_perd_strt_dt;
409 end if;
410
411 if p_procg_end_dt = hr_api.g_date then --p_procg_end_dt
412 l_procg_end_dt := null;
413 else
414 l_procg_end_dt := p_procg_end_dt;
415 end if;
416 --
417 hr_utility.set_location(' l_reinstate_cd '||l_reinstate_cd,100);
418 hr_utility.set_location(' l_reinstate_ovrdn_cd '||l_reinstate_ovrdn_cd,100);
419 --
420 ben_Pil_Elctbl_chc_Popl_api.create_Pil_Elctbl_chc_Popl
421 (p_validate => FALSE
422 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
423 ,p_dflt_enrt_dt => l_dflt_enrt_dt
424 ,p_cls_enrt_dt_to_use_cd => l_cls_enrt_dt_to_use_cd
425 ,p_enrt_typ_cycl_cd => p_enrt_typ_cycl_cd
426 ,p_enrt_perd_end_dt => l_enrt_perd_end_dt
427 ,p_enrt_perd_strt_dt => l_enrt_perd_strt_dt
428 ,p_procg_end_dt => l_procg_end_dt
429 ,p_pil_elctbl_popl_stat_cd => 'STRTD'
430 ,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
431 ,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
432 ,p_lee_rsn_id => l_lee_rsn_id
433 ,p_enrt_perd_id => l_enrt_perd_id
434 ,p_per_in_ler_id => p_per_in_ler_id
435 ,p_business_group_id => p_business_group_id
436 ,p_request_id => p_request_id
437 ,p_program_application_id => p_program_application_id
438 ,p_program_id => p_program_id
439 ,p_program_update_date => p_program_update_date
440 ,p_object_version_number => l_pel_ovn
441 ,p_effective_date => p_effective_date
442 ,p_pl_id => l_pl_id
443 ,p_pgm_id => l_pgm_id
444 ,p_uom => l_uom
445 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
446 -- Bug 2174005
447 ,p_ws_due_dt => l_ws_upd_end_dt
448 ,p_bdgt_due_dt => l_bdgt_upd_end_dt
449 ,p_bdgt_acc_cd => l_bdgt_acc_cd
450 ,p_ws_acc_cd => l_ws_acc_cd
451 ,p_bdgt_stat_cd => l_bdgt_stat_cd
452 ,p_ws_stat_cd => l_ws_stat_cd
453 ,p_pop_cd => l_pop_cd
454 ,p_bdgt_iss_dt => l_bdgt_iss_dt
455 ,p_ws_iss_dt => l_ws_iss_dt
456 ,p_reinstate_cd => l_reinstate_cd
460 if g_debug then
457 ,p_reopen_le_on_reprocess => l_reopen_le_on_reprocess
458 ,p_reinstate_ovrdn_cd => l_reinstate_ovrdn_cd
459 );
461 hr_utility.set_location('l_pil_elctbl_chc_popl_id '|| l_pil_elctbl_chc_popl_id, 10);
462 end if;
463 if g_debug then
464 hr_utility.set_location('p_ws_mgr_id '|| p_ws_mgr_id, 10);
465 end if;
466 if g_debug then
467 hr_utility.set_location('p_assignment_id '|| p_assignment_id, 10);
468 end if;
469 update ben_Pil_Elctbl_chc_Popl
470 set ws_mgr_id = p_ws_mgr_id,
471 assignment_id = p_assignment_id
472 where Pil_Elctbl_chc_Popl_id = l_pil_elctbl_chc_popl_id;
473 if g_debug then
474 hr_utility.set_location('Dn BPECPAPI_CRE: '|| l_proc, 10);
475 end if;
476 --
477 -- If the enrollment period spans beyond the year period, issue a message.
478 --
479 hr_utility.set_location('fnd_global.conc_request_id:'||fnd_global.conc_request_id,10);
480 if fnd_global.conc_request_id <> -1 then
481 --
482 open c_yrp;
483 fetch c_yrp into l_yr_perd_end_dt;
484 close c_yrp;
485 --
486 if p_enrt_perd_end_dt > l_yr_perd_end_dt or
487 p_dflt_enrt_dt > l_yr_perd_end_dt or
488 p_procg_end_dt > l_yr_perd_end_dt then
489 --
490 fnd_message.set_name('BEN','BEN_92551_ENRT_PRD_BYND_YR_PRD');
491 benutils.write(p_text => substr(fnd_message.get,1,128));
492 --
493 end if;
494 --
495 end if;
496 --
497 else
498 --
499 -- Check to see which attributes need to be updated
500 --
501 l_update_pel:=false;
502 if l_pel_dflt_enrt_dt is null and p_dflt_enrt_dt is not null then
503 l_update_pel:=true;
504 l_pel_dflt_enrt_dt:=p_dflt_enrt_dt;
505 end if;
506 if l_pel_cls_enrt_dt_to_use_cd is null and p_cls_enrt_dt_to_use_cd is not null then
507 l_update_pel:=true;
508 l_pel_cls_enrt_dt_to_use_cd:=p_cls_enrt_dt_to_use_cd;
509 end if;
510 if l_pel_enrt_typ_cycl_cd is null and p_enrt_typ_cycl_cd is not null then
511 l_update_pel:=true;
512 l_pel_enrt_typ_cycl_cd:=p_enrt_typ_cycl_cd;
513 end if;
514 if l_pel_enrt_perd_strt_dt is null and p_enrt_perd_strt_dt is not null then
515 l_update_pel:=true;
516 l_pel_enrt_perd_strt_dt:=p_enrt_perd_strt_dt;
517 end if;
518 if l_pel_enrt_perd_end_dt is null and p_enrt_perd_end_dt is not null then
519 l_update_pel:=true;
520 l_pel_enrt_perd_end_dt:=p_enrt_perd_end_dt;
521 end if;
522 if l_pel_lee_rsn_id is null and p_lee_rsn_id is not null then
523 l_update_pel:=true;
524 l_pel_lee_rsn_id:=p_lee_rsn_id;
525 end if;
526 if l_pel_enrt_perd_id is null and p_enrt_perd_id is not null then
527 l_update_pel:=true;
528 l_pel_enrt_perd_id:=p_enrt_perd_id;
529 end if;
533 end if;
530 if l_pel_procg_end_dt is null and p_procg_end_dt is not null then
531 l_update_pel:=true;
532 l_pel_procg_end_dt:=p_procg_end_dt;
534 if l_pel_uom is null and p_uom is not null then
535 l_update_pel:=true;
536 l_pel_uom:=p_uom;
537 end if;
538 if l_pel_acty_ref_perd_cd is null and p_acty_ref_perd_cd is not null then
539 l_update_pel:=true;
540 l_pel_acty_ref_perd_cd:=p_acty_ref_perd_cd;
541 end if;
542 --
543 -- If any attributes need to be updated, then do it.
544 --
545 if l_update_pel then
546 if g_debug then
547 hr_utility.set_location('BPECPAPI_UPD: '|| l_proc, 10);
548 end if;
549 ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
550 (p_validate => FALSE
551 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
552 ,p_object_version_number => l_pel_ovn
553 ,p_dflt_enrt_dt => l_pel_dflt_enrt_dt
554 ,p_cls_enrt_dt_to_use_cd => l_pel_cls_enrt_dt_to_use_cd
555 ,p_enrt_typ_cycl_cd => l_pel_enrt_typ_cycl_cd
556 ,p_enrt_perd_end_dt => l_pel_enrt_perd_end_dt
557 ,p_enrt_perd_strt_dt => l_pel_enrt_perd_strt_dt
558 ,p_procg_end_dt => l_pel_procg_end_dt
559 ,p_lee_rsn_id => l_pel_lee_rsn_id
560 ,p_enrt_perd_id => l_pel_enrt_perd_id
561 ,p_effective_date => p_effective_date
562 ,p_uom => l_pel_uom
563 ,p_acty_ref_perd_cd => l_pel_acty_ref_perd_cd
564 );
568 end if;
565 if g_debug then
566 hr_utility.set_location('Dn BPECPAPI_UPD: '|| l_proc, 10);
567 end if;
569 end if;
570 --
571 -- Finished with pil_elctbl_chc_popl stuff
572 --
573 -- Get oiplip_id for options in plans in programs.
574 --
575 if p_oiplip_id is null
576 and p_oipl_id is not null
577 and p_pgm_id is not null
578 then
579 if g_debug then
580 hr_utility.set_location('get_oiplip', 25);
581 end if;
582 open get_oiplip;
583 fetch get_oiplip into l_oiplip_id;
584 close get_oiplip;
585 else
586 l_oiplip_id := p_oiplip_id;
587 end if;
588 --
589 -- Set out parameters
590 --
591 p_pil_elctbl_chc_popl_id := l_pil_elctbl_chc_popl_id;
592 p_oiplip_id := l_oiplip_id;
593 --
594 end CreOrSel_pil_elctbl_chc_popl;
595 --
596 -- ----------------------------------------------------------------------------
597 -- |------------------------< create_ELIG_PER_ELC_CHC >----------------------|
598 -- ----------------------------------------------------------------------------
599 --
600 procedure create_ELIG_PER_ELC_CHC
601 (p_validate in boolean default false
602 ,p_elig_per_elctbl_chc_id out nocopy number
603 ,p_enrt_typ_cycl_cd in varchar2 default null
604 ,p_enrt_cvg_strt_dt_cd in varchar2 default null
605 ,p_enrt_perd_end_dt in date default null
606 ,p_enrt_perd_strt_dt in date default null
607 ,p_enrt_cvg_strt_dt_rl in varchar2 default null
608 ,p_ctfn_rqd_flag in varchar2 default 'N'
609 ,p_pil_elctbl_chc_popl_id in number default null
610 ,p_roll_crs_flag in varchar2 default 'N'
611 ,p_crntly_enrd_flag in varchar2 default 'N'
612 ,p_dflt_flag in varchar2 default 'N'
613 ,p_elctbl_flag in varchar2 default 'N'
614 ,p_mndtry_flag in varchar2 default 'N'
615 ,p_in_pndg_wkflow_flag in varchar2 default 'N'
616 ,p_dflt_enrt_dt in date default null
617 ,p_dpnt_cvg_strt_dt_cd in varchar2 default null
618 ,p_dpnt_cvg_strt_dt_rl in varchar2 default null
619 ,p_enrt_cvg_strt_dt in date default null
620 ,p_alws_dpnt_dsgn_flag in varchar2 default 'N'
621 ,p_dpnt_dsgn_cd in varchar2 default null
622 ,p_ler_chg_dpnt_cvg_cd in varchar2 default null
623 ,p_erlst_deenrt_dt in date default null
624 ,p_procg_end_dt in date default null
625 ,p_comp_lvl_cd in varchar2 default null
626 ,p_pl_id in number default null
627 ,p_oipl_id in number default null
628 ,p_pgm_id in number default null
629 ,p_pgm_typ_cd in varchar2 default null
630 ,p_plip_id in number default null
631 ,p_ptip_id in number default null
632 ,p_pl_typ_id in number default null
633 ,p_oiplip_id in number default null
634 ,p_cmbn_plip_id in number default null
635 ,p_cmbn_ptip_id in number default null
636 ,p_cmbn_ptip_opt_id in number default null
637 ,p_assignment_id in number default null
638 ,p_spcl_rt_pl_id in number default null
639 ,p_spcl_rt_oipl_id in number default null
640 ,p_must_enrl_anthr_pl_id in number default null
641 ,p_int_elig_per_elctbl_chc_id in number default null
642 ,p_prtt_enrt_rslt_id in number default null
643 ,p_bnft_prvdr_pool_id in number default null
644 ,p_per_in_ler_id in number default null
645 ,p_yr_perd_id in number default null
646 ,p_auto_enrt_flag in varchar2 default 'N'
647 ,p_business_group_id in number default null
648 ,p_pl_ordr_num in number default null
649 ,p_plip_ordr_num in number default null
650 ,p_ptip_ordr_num in number default null
651 ,p_oipl_ordr_num in number default null
652 -- cwb
653 ,p_comments in varchar2 default null
654 ,p_elig_flag in varchar2 default 'Y'
655 ,p_elig_ovrid_dt in date default null
656 ,p_elig_ovrid_person_id in number default null
657 ,p_inelig_rsn_cd in varchar2 default null
658 ,p_mgr_ovrid_dt in date default null
659 ,p_mgr_ovrid_person_id in number default null
660 ,p_ws_mgr_id in number default null
661 -- cwb
662 ,p_epe_attribute_category in varchar2 default null
663 ,p_epe_attribute1 in varchar2 default null
667 ,p_epe_attribute5 in varchar2 default null
664 ,p_epe_attribute2 in varchar2 default null
665 ,p_epe_attribute3 in varchar2 default null
666 ,p_epe_attribute4 in varchar2 default null
668 ,p_epe_attribute6 in varchar2 default null
669 ,p_epe_attribute7 in varchar2 default null
670 ,p_epe_attribute8 in varchar2 default null
671 ,p_epe_attribute9 in varchar2 default null
672 ,p_epe_attribute10 in varchar2 default null
673 ,p_epe_attribute11 in varchar2 default null
674 ,p_epe_attribute12 in varchar2 default null
675 ,p_epe_attribute13 in varchar2 default null
676 ,p_epe_attribute14 in varchar2 default null
677 ,p_epe_attribute15 in varchar2 default null
678 ,p_epe_attribute16 in varchar2 default null
679 ,p_epe_attribute17 in varchar2 default null
680 ,p_epe_attribute18 in varchar2 default null
681 ,p_epe_attribute19 in varchar2 default null
682 ,p_epe_attribute20 in varchar2 default null
683 ,p_epe_attribute21 in varchar2 default null
684 ,p_epe_attribute22 in varchar2 default null
685 ,p_epe_attribute23 in varchar2 default null
686 ,p_epe_attribute24 in varchar2 default null
687 ,p_epe_attribute25 in varchar2 default null
688 ,p_epe_attribute26 in varchar2 default null
689 ,p_epe_attribute27 in varchar2 default null
690 ,p_epe_attribute28 in varchar2 default null
691 ,p_epe_attribute29 in varchar2 default null
692 ,p_epe_attribute30 in varchar2 default null
693 ,p_approval_status_cd in varchar2 default null
694 ,p_fonm_cvg_strt_dt in date default null
695 ,p_cryfwd_elig_dpnt_cd in varchar2 default null
696 ,p_request_id in number default null
697 ,p_program_application_id in number default null
698 ,p_program_id in number default null
699 ,p_program_update_date in date default null
700 ,p_object_version_number out nocopy number
701 ,p_effective_date in date
702 ,p_enrt_perd_id in number default null
703 ,p_lee_rsn_id in number default null
704 ,p_cls_enrt_dt_to_use_cd in varchar2 default null
705 ,p_uom in varchar2 default null
706 ,p_acty_ref_perd_cd in varchar2 default null
707 )
708 is
709 --
710 l_proc varchar2(72) ;
711 --
712 l_elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%TYPE;
713 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
714 l_pil_elctbl_chc_popl_id number;
715 l_oiplip_id number;
716 --
717 begin
718 --
719 g_debug := hr_utility.debug_enabled;
720 if g_debug then
721 l_proc := g_package||'create_ELIG_PER_ELC_CHC';
722 hr_utility.set_location('Entering:'|| l_proc, 10);
723 end if;
724 --
725 -- Issue a savepoint if operating in validation only mode
726 --
727 savepoint create_ELIG_PER_ELC_CHC;
728 --
729 --
730 -- Create or find ben_pil_elctbl_chc_popl
731 --
732 CreOrSel_pil_elctbl_chc_popl
733 (p_per_in_ler_id => p_per_in_ler_id
734 ,p_effective_date => p_effective_date
735 ,p_business_group_id => p_business_group_id
736 ,p_pgm_id => p_pgm_id
737 ,p_plip_id => p_plip_id
738 ,p_pl_id => p_pl_id
739 ,p_oipl_id => p_oipl_id
740 ,p_yr_perd_id => p_yr_perd_id
741 ,p_uom => p_uom
742 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
743 ,p_dflt_enrt_dt => p_dflt_enrt_dt
744 ,p_cls_enrt_dt_to_use_cd => p_cls_enrt_dt_to_use_cd
745 ,p_enrt_typ_cycl_cd => p_enrt_typ_cycl_cd
746 ,p_enrt_perd_end_dt => p_enrt_perd_end_dt
747 ,p_enrt_perd_strt_dt => p_enrt_perd_strt_dt
748 ,p_procg_end_dt => p_procg_end_dt
749 ,p_lee_rsn_id => p_lee_rsn_id
750 ,p_enrt_perd_id => p_enrt_perd_id
751 ,p_request_id => p_request_id
752 ,p_program_application_id => p_program_application_id
753 ,p_program_id => p_program_id
754 ,p_program_update_date => p_program_update_date
755 ,p_ws_mgr_id => p_ws_mgr_id
756 ,p_assignment_id => p_assignment_id
757 --
758 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
759 ,p_oiplip_id => l_oiplip_id
760 );
761 --
762 -- Process Logic
763 --
764 begin
765 --
766 -- Start of API User Hook for the before hook of create_ELIG_PER_ELC_CHC
767 --
768 ben_ELIG_PER_ELC_CHC_bk1.create_ELIG_PER_ELC_CHC_b
769 (
770 -- p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
774 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
771 p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
772 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
773 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
775 ,p_roll_crs_flag => p_roll_crs_flag
776 ,p_crntly_enrd_flag => p_crntly_enrd_flag
777 ,p_dflt_flag => p_dflt_flag
778 ,p_elctbl_flag => p_elctbl_flag
779 ,p_mndtry_flag => p_mndtry_flag
780 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
781 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
782 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
783 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
784 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
785 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
786 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
787 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
788 ,p_procg_end_dt => p_procg_end_dt
789 ,p_comp_lvl_cd => p_comp_lvl_cd
790 ,p_pl_id => p_pl_id
791 ,p_oipl_id => p_oipl_id
792 ,p_pgm_id => p_pgm_id
793 ,p_plip_id => p_plip_id
794 ,p_ptip_id => p_ptip_id
795 ,p_pl_typ_id => p_pl_typ_id
796 ,p_oiplip_id => l_oiplip_id
797 ,p_cmbn_plip_id => p_cmbn_ptip_id
798 ,p_cmbn_ptip_id => p_cmbn_ptip_id
799 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
800 ,p_assignment_id => p_assignment_id
801 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
802 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
803 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
804 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
805 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
806 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
807 ,p_per_in_ler_id => p_per_in_ler_id
808 ,p_yr_perd_id => p_yr_perd_id
809 ,p_auto_enrt_flag => p_auto_enrt_flag
810 ,p_business_group_id => p_business_group_id
811 ,p_pl_ordr_num => p_pl_ordr_num
812 ,p_plip_ordr_num => p_plip_ordr_num
813 ,p_ptip_ordr_num => p_ptip_ordr_num
814 ,p_oipl_ordr_num => p_oipl_ordr_num
815 --cwb
816 ,p_comments => p_comments
817 ,p_elig_flag => p_elig_flag
818 ,p_elig_ovrid_dt => p_elig_ovrid_dt
819 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
820 ,p_inelig_rsn_cd => p_inelig_rsn_cd
821 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
822 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
823 ,p_ws_mgr_id => p_ws_mgr_id
824 --cwb
825 ,p_epe_attribute_category => p_epe_attribute_category
826 ,p_epe_attribute1 => p_epe_attribute1
827 ,p_epe_attribute2 => p_epe_attribute2
828 ,p_epe_attribute3 => p_epe_attribute3
829 ,p_epe_attribute4 => p_epe_attribute4
830 ,p_epe_attribute5 => p_epe_attribute5
831 ,p_epe_attribute6 => p_epe_attribute6
832 ,p_epe_attribute7 => p_epe_attribute7
833 ,p_epe_attribute8 => p_epe_attribute8
834 ,p_epe_attribute9 => p_epe_attribute9
835 ,p_epe_attribute10 => p_epe_attribute10
836 ,p_epe_attribute11 => p_epe_attribute11
837 ,p_epe_attribute12 => p_epe_attribute12
838 ,p_epe_attribute13 => p_epe_attribute13
839 ,p_epe_attribute14 => p_epe_attribute14
840 ,p_epe_attribute15 => p_epe_attribute15
841 ,p_epe_attribute16 => p_epe_attribute16
842 ,p_epe_attribute17 => p_epe_attribute17
843 ,p_epe_attribute18 => p_epe_attribute18
844 ,p_epe_attribute19 => p_epe_attribute19
845 ,p_epe_attribute20 => p_epe_attribute20
846 ,p_epe_attribute21 => p_epe_attribute21
847 ,p_epe_attribute22 => p_epe_attribute22
848 ,p_epe_attribute23 => p_epe_attribute23
849 ,p_epe_attribute24 => p_epe_attribute24
850 ,p_epe_attribute25 => p_epe_attribute25
851 ,p_epe_attribute26 => p_epe_attribute26
852 ,p_epe_attribute27 => p_epe_attribute27
853 ,p_epe_attribute28 => p_epe_attribute28
854 ,p_epe_attribute29 => p_epe_attribute29
855 ,p_epe_attribute30 => p_epe_attribute30
856 ,p_approval_status_cd => p_approval_status_cd
857 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
858 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
859 ,p_request_id => p_request_id
860 ,p_program_application_id => p_program_application_id
861 ,p_program_id => p_program_id
862 ,p_program_update_date => p_program_update_date
863 ,p_effective_date => trunc(p_effective_date)
864 );
865 exception
866 when hr_api.cannot_find_prog_unit then
867 hr_api.cannot_find_prog_unit_error
868 (
872 --
869 p_module_name => 'CREATE_ELIG_PER_ELC_CHC'
870 ,p_hook_type => 'BP'
871 );
873 -- End of API User Hook for the before hook of create_ELIG_PER_ELC_CHC
874 --
875 end;
876 --
877 -- Use the resulting l_pil_elctbl_chc_popl_id as the FK
878 --
879 if g_debug then
880 hr_utility.set_location('EPE Ins: '|| l_proc, 10);
881 end if;
882 ben_epe_ins.ins
883 (
884 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
885 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
886 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
887 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
888 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
889 ,p_roll_crs_flag => p_roll_crs_flag
890 ,p_crntly_enrd_flag => p_crntly_enrd_flag
891 ,p_dflt_flag => p_dflt_flag
892 ,p_elctbl_flag => p_elctbl_flag
893 ,p_mndtry_flag => p_mndtry_flag
894 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
895 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
896 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
897 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
898 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
899 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
900 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
901 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
902 ,p_procg_end_dt => p_procg_end_dt
903 ,p_comp_lvl_cd => p_comp_lvl_cd
904 ,p_pl_id => p_pl_id
905 ,p_oipl_id => p_oipl_id
906 ,p_pgm_id => p_pgm_id
907 ,p_plip_id => p_plip_id
908 ,p_ptip_id => p_ptip_id
909 ,p_pl_typ_id => p_pl_typ_id
910 ,p_oiplip_id => l_oiplip_id
911 ,p_cmbn_plip_id => p_cmbn_plip_id
912 ,p_cmbn_ptip_id => p_cmbn_ptip_id
913 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
914 ,p_assignment_id => p_assignment_id
915 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
916 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
917 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
918 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
919 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
920 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
921 ,p_per_in_ler_id => p_per_in_ler_id
922 ,p_yr_perd_id => p_yr_perd_id
923 ,p_auto_enrt_flag => p_auto_enrt_flag
924 ,p_business_group_id => p_business_group_id
925 ,p_pl_ordr_num => p_pl_ordr_num
926 ,p_plip_ordr_num => p_plip_ordr_num
927 ,p_ptip_ordr_num => p_ptip_ordr_num
928 ,p_oipl_ordr_num => p_oipl_ordr_num
929 --cwb
930 ,p_comments => p_comments
931 ,p_elig_flag => p_elig_flag
932 ,p_elig_ovrid_dt => p_elig_ovrid_dt
933 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
934 ,p_inelig_rsn_cd => p_inelig_rsn_cd
935 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
936 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
937 ,p_ws_mgr_id => p_ws_mgr_id
938 --cwb
939 ,p_epe_attribute_category => p_epe_attribute_category
940 ,p_epe_attribute1 => p_epe_attribute1
941 ,p_epe_attribute2 => p_epe_attribute2
942 ,p_epe_attribute3 => p_epe_attribute3
943 ,p_epe_attribute4 => p_epe_attribute4
944 ,p_epe_attribute5 => p_epe_attribute5
945 ,p_epe_attribute6 => p_epe_attribute6
946 ,p_epe_attribute7 => p_epe_attribute7
947 ,p_epe_attribute8 => p_epe_attribute8
948 ,p_epe_attribute9 => p_epe_attribute9
949 ,p_epe_attribute10 => p_epe_attribute10
950 ,p_epe_attribute11 => p_epe_attribute11
951 ,p_epe_attribute12 => p_epe_attribute12
952 ,p_epe_attribute13 => p_epe_attribute13
953 ,p_epe_attribute14 => p_epe_attribute14
954 ,p_epe_attribute15 => p_epe_attribute15
955 ,p_epe_attribute16 => p_epe_attribute16
956 ,p_epe_attribute17 => p_epe_attribute17
957 ,p_epe_attribute18 => p_epe_attribute18
958 ,p_epe_attribute19 => p_epe_attribute19
959 ,p_epe_attribute20 => p_epe_attribute20
960 ,p_epe_attribute21 => p_epe_attribute21
961 ,p_epe_attribute22 => p_epe_attribute22
962 ,p_epe_attribute23 => p_epe_attribute23
963 ,p_epe_attribute24 => p_epe_attribute24
964 ,p_epe_attribute25 => p_epe_attribute25
965 ,p_epe_attribute26 => p_epe_attribute26
966 ,p_epe_attribute27 => p_epe_attribute27
967 ,p_epe_attribute28 => p_epe_attribute28
968 ,p_epe_attribute29 => p_epe_attribute29
969 ,p_epe_attribute30 => p_epe_attribute30
970 ,p_approval_status_cd => p_approval_status_cd
971 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
972 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
973 ,p_request_id => p_request_id
974 ,p_program_application_id => p_program_application_id
975 ,p_program_id => p_program_id
979 );
976 ,p_program_update_date => p_program_update_date
977 ,p_object_version_number => l_object_version_number
978 ,p_effective_date => trunc(p_effective_date)
980 if g_debug then
981 hr_utility.set_location('Dn EPE Ins: '|| l_proc, 10);
982 end if;
983 --
984 begin
985 --
986 -- Start of API User Hook for the after hook of create_ELIG_PER_ELC_CHC
987 --
988 ben_ELIG_PER_ELC_CHC_bk1.create_ELIG_PER_ELC_CHC_a
989 (
990 p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
991 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
992 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
993 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
994 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
995 ,p_roll_crs_flag => p_roll_crs_flag
996 ,p_crntly_enrd_flag => p_crntly_enrd_flag
997 ,p_dflt_flag => p_dflt_flag
998 ,p_elctbl_flag => p_elctbl_flag
999 ,p_mndtry_flag => p_mndtry_flag
1000 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
1001 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
1002 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
1003 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
1004 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
1005 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
1006 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
1007 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
1008 ,p_procg_end_dt => p_procg_end_dt
1009 ,p_comp_lvl_cd => p_comp_lvl_cd
1010 ,p_pl_id => p_pl_id
1011 ,p_oipl_id => p_oipl_id
1012 ,p_pgm_id => p_pgm_id
1013 ,p_plip_id => p_plip_id
1014 ,p_ptip_id => p_ptip_id
1015 ,p_pl_typ_id => p_pl_typ_id
1016 ,p_oiplip_id => l_oiplip_id
1017 ,p_cmbn_plip_id => p_cmbn_plip_id
1018 ,p_cmbn_ptip_id => p_cmbn_ptip_id
1019 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
1020 ,p_assignment_id => p_assignment_id
1021 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
1022 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
1023 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
1024 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
1025 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1026 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1027 ,p_per_in_ler_id => p_per_in_ler_id
1028 ,p_yr_perd_id => p_yr_perd_id
1029 ,p_auto_enrt_flag => p_auto_enrt_flag
1030 ,p_business_group_id => p_business_group_id
1031 ,p_pl_ordr_num => p_pl_ordr_num
1032 ,p_plip_ordr_num => p_plip_ordr_num
1033 ,p_ptip_ordr_num => p_ptip_ordr_num
1034 ,p_oipl_ordr_num => p_oipl_ordr_num
1035 --cwb
1036 ,p_comments => p_comments
1037 ,p_elig_flag => p_elig_flag
1038 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1042 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
1039 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1040 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1041 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
1043 ,p_ws_mgr_id => p_ws_mgr_id
1044 --cwb
1045 ,p_epe_attribute_category => p_epe_attribute_category
1046 ,p_epe_attribute1 => p_epe_attribute1
1047 ,p_epe_attribute2 => p_epe_attribute2
1048 ,p_epe_attribute3 => p_epe_attribute3
1049 ,p_epe_attribute4 => p_epe_attribute4
1050 ,p_epe_attribute5 => p_epe_attribute5
1051 ,p_epe_attribute6 => p_epe_attribute6
1052 ,p_epe_attribute7 => p_epe_attribute7
1053 ,p_epe_attribute8 => p_epe_attribute8
1054 ,p_epe_attribute9 => p_epe_attribute9
1055 ,p_epe_attribute10 => p_epe_attribute10
1056 ,p_epe_attribute11 => p_epe_attribute11
1057 ,p_epe_attribute12 => p_epe_attribute12
1061 ,p_epe_attribute16 => p_epe_attribute16
1058 ,p_epe_attribute13 => p_epe_attribute13
1059 ,p_epe_attribute14 => p_epe_attribute14
1060 ,p_epe_attribute15 => p_epe_attribute15
1062 ,p_epe_attribute17 => p_epe_attribute17
1063 ,p_epe_attribute18 => p_epe_attribute18
1064 ,p_epe_attribute19 => p_epe_attribute19
1065 ,p_epe_attribute20 => p_epe_attribute20
1066 ,p_epe_attribute21 => p_epe_attribute21
1067 ,p_epe_attribute22 => p_epe_attribute22
1068 ,p_epe_attribute23 => p_epe_attribute23
1069 ,p_epe_attribute24 => p_epe_attribute24
1070 ,p_epe_attribute25 => p_epe_attribute25
1071 ,p_epe_attribute26 => p_epe_attribute26
1072 ,p_epe_attribute27 => p_epe_attribute27
1073 ,p_epe_attribute28 => p_epe_attribute28
1074 ,p_epe_attribute29 => p_epe_attribute29
1075 ,p_epe_attribute30 => p_epe_attribute30
1076 ,p_approval_status_cd => p_approval_status_cd
1077 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
1078 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
1079 ,p_request_id => p_request_id
1080 ,p_program_application_id => p_program_application_id
1081 ,p_program_id => p_program_id
1082 ,p_program_update_date => p_program_update_date
1083 ,p_object_version_number => l_object_version_number
1084 ,p_effective_date => trunc(p_effective_date)
1085 );
1086 exception
1087 when hr_api.cannot_find_prog_unit then
1088 hr_api.cannot_find_prog_unit_error
1089 (p_module_name => 'CREATE_ELIG_PER_ELC_CHC'
1090 ,p_hook_type => 'AP'
1091 );
1092 --
1093 -- End of API User Hook for the after hook of create_ELIG_PER_ELC_CHC
1094 --
1095 end;
1096 --
1097 if g_debug then
1098 hr_utility.set_location(l_proc, 60);
1099 end if;
1100 --
1101 -- When in validation only mode raise the Validate_Enabled exception
1102 --
1103 if p_validate then
1104 raise hr_api.validate_enabled;
1105 end if;
1106 --
1107 -- Set all output arguments
1108 --
1109 p_elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id;
1110 p_object_version_number := l_object_version_number;
1111 --
1112 if g_debug then
1113 hr_utility.set_location('p_elig_per_elctbl_chc_id:'|| to_char(l_elig_per_elctbl_chc_id),99);
1114 end if;
1115
1116 if g_debug then
1117 hr_utility.set_location(' Leaving:'||l_proc, 99);
1118 end if;
1119 --
1120 exception
1121 --
1122 when hr_api.validate_enabled then
1123 --
1124 -- As the Validate_Enabled exception has been raised
1125 -- we must rollback to the savepoint
1126 --
1127 ROLLBACK TO create_ELIG_PER_ELC_CHC;
1128 --
1129 -- Only set output warning arguments
1130 -- (Any key or derived arguments must be set to null
1131 -- when validation only mode is being used.)
1132 --
1133 p_elig_per_elctbl_chc_id := null;
1134 p_object_version_number := null;
1135 if g_debug then
1136 hr_utility.set_location(' Leaving:'||l_proc, 80);
1137 end if;
1138 --
1139 when others then
1140 --
1141 -- A validation or unexpected error has occured
1142 --
1143 p_elig_per_elctbl_chc_id := null;
1144 p_object_version_number := null;
1145 if g_debug then
1146 hr_utility.set_location(' Leaving:'||l_proc, 80);
1147 end if;
1148 --
1149 ROLLBACK TO create_ELIG_PER_ELC_CHC;
1150 raise;
1151 --
1152 end create_ELIG_PER_ELC_CHC;
1153 --
1154 -- ----------------------------------------------------------------------------
1155 -- |-----------------< create_perf_ELIG_PER_ELC_CHC >-------------------------|
1156 -- ----------------------------------------------------------------------------
1157 --
1158 procedure create_perf_ELIG_PER_ELC_CHC
1159 (p_validate in boolean default false
1160 ,p_elig_per_elctbl_chc_id out nocopy number
1161 ,p_enrt_typ_cycl_cd in varchar2 default null
1162 ,p_enrt_cvg_strt_dt_cd in varchar2 default null
1163 ,p_enrt_perd_end_dt in date default null
1164 ,p_enrt_perd_strt_dt in date default null
1165 ,p_enrt_cvg_strt_dt_rl in varchar2 default null
1166 ,p_ctfn_rqd_flag in varchar2 default 'N'
1167 ,p_pil_elctbl_chc_popl_id in number default null
1168 ,p_roll_crs_flag in varchar2 default 'N'
1169 ,p_crntly_enrd_flag in varchar2 default 'N'
1170 ,p_dflt_flag in varchar2 default 'N'
1171 ,p_elctbl_flag in varchar2 default 'N'
1172 ,p_mndtry_flag in varchar2 default 'N'
1173 ,p_in_pndg_wkflow_flag in varchar2 default 'N'
1174 ,p_dflt_enrt_dt in date default null
1175 ,p_dpnt_cvg_strt_dt_cd in varchar2 default null
1176 ,p_dpnt_cvg_strt_dt_rl in varchar2 default null
1177 ,p_enrt_cvg_strt_dt in date default null
1178 ,p_alws_dpnt_dsgn_flag in varchar2 default 'N'
1179 ,p_dpnt_dsgn_cd in varchar2 default null
1180 ,p_ler_chg_dpnt_cvg_cd in varchar2 default null
1181 ,p_erlst_deenrt_dt in date default null
1185 ,p_oipl_id in number default null
1182 ,p_procg_end_dt in date default null
1183 ,p_comp_lvl_cd in varchar2 default null
1184 ,p_pl_id in number default null
1186 ,p_pgm_id in number default null
1187 ,p_pgm_typ_cd in varchar2 default null
1188 ,p_plip_id in number default null
1189 ,p_ptip_id in number default null
1190 ,p_pl_typ_id in number default null
1191 ,p_oiplip_id in number default null
1192 ,p_cmbn_plip_id in number default null
1193 ,p_cmbn_ptip_id in number default null
1194 ,p_cmbn_ptip_opt_id in number default null
1195 ,p_assignment_id in number default null
1196 ,p_spcl_rt_pl_id in number default null
1197 ,p_spcl_rt_oipl_id in number default null
1198 ,p_must_enrl_anthr_pl_id in number default null
1199 ,p_int_elig_per_elctbl_chc_id in number default null
1200 ,p_prtt_enrt_rslt_id in number default null
1201 ,p_bnft_prvdr_pool_id in number default null
1202 ,p_per_in_ler_id in number default null
1203 ,p_yr_perd_id in number default null
1204 ,p_auto_enrt_flag in varchar2 default 'N'
1205 ,p_business_group_id in number default null
1206 ,p_pl_ordr_num in number default null
1207 ,p_plip_ordr_num in number default null
1208 ,p_ptip_ordr_num in number default null
1209 ,p_oipl_ordr_num in number default null
1210 -- cwb
1211 ,p_comments in varchar2 default null
1212 ,p_elig_flag in varchar2 default 'Y'
1213 ,p_elig_ovrid_dt in date default null
1214 ,p_elig_ovrid_person_id in number default null
1215 ,p_inelig_rsn_cd in varchar2 default null
1216 ,p_mgr_ovrid_dt in date default null
1217 ,p_mgr_ovrid_person_id in number default null
1218 ,p_ws_mgr_id in number default null
1219 -- cwb
1220 ,p_epe_attribute_category in varchar2 default null
1221 ,p_epe_attribute1 in varchar2 default null
1222 ,p_epe_attribute2 in varchar2 default null
1223 ,p_epe_attribute3 in varchar2 default null
1224 ,p_epe_attribute4 in varchar2 default null
1225 ,p_epe_attribute5 in varchar2 default null
1226 ,p_epe_attribute6 in varchar2 default null
1227 ,p_epe_attribute7 in varchar2 default null
1228 ,p_epe_attribute8 in varchar2 default null
1229 ,p_epe_attribute9 in varchar2 default null
1233 ,p_epe_attribute13 in varchar2 default null
1230 ,p_epe_attribute10 in varchar2 default null
1231 ,p_epe_attribute11 in varchar2 default null
1232 ,p_epe_attribute12 in varchar2 default null
1234 ,p_epe_attribute14 in varchar2 default null
1235 ,p_epe_attribute15 in varchar2 default null
1236 ,p_epe_attribute16 in varchar2 default null
1237 ,p_epe_attribute17 in varchar2 default null
1238 ,p_epe_attribute18 in varchar2 default null
1239 ,p_epe_attribute19 in varchar2 default null
1240 ,p_epe_attribute20 in varchar2 default null
1241 ,p_epe_attribute21 in varchar2 default null
1242 ,p_epe_attribute22 in varchar2 default null
1243 ,p_epe_attribute23 in varchar2 default null
1244 ,p_epe_attribute24 in varchar2 default null
1245 ,p_epe_attribute25 in varchar2 default null
1246 ,p_epe_attribute26 in varchar2 default null
1247 ,p_epe_attribute27 in varchar2 default null
1248 ,p_epe_attribute28 in varchar2 default null
1249 ,p_epe_attribute29 in varchar2 default null
1250 ,p_epe_attribute30 in varchar2 default null
1251 ,p_approval_status_cd in varchar2 default null
1252 ,p_fonm_cvg_strt_dt in date default null
1253 ,p_cryfwd_elig_dpnt_cd in varchar2 default null
1254 ,p_request_id in number default null
1255 ,p_program_application_id in number default null
1256 ,p_program_id in number default null
1257 ,p_program_update_date in date default null
1258 ,p_object_version_number out nocopy number
1259 ,p_effective_date in date
1260 ,p_enrt_perd_id in number default null
1261 ,p_lee_rsn_id in number default null
1262 ,p_cls_enrt_dt_to_use_cd in varchar2 default null
1263 ,p_uom in varchar2 default null
1264 ,p_acty_ref_perd_cd in varchar2 default null
1265 -- CWB changes.
1266 ,p_mode in varchar2 default null
1267 )
1268 is
1269 --
1270 l_proc varchar2(72) ;
1271 --
1272 -- Declare cursors and local variables
1273 --
1274 l_elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%TYPE;
1275 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
1276 l_pil_elctbl_chc_popl_id number;
1277 l_oiplip_id number;
1278 --
1279 l_created_by ben_elig_per_elctbl_chc.created_by%TYPE;
1280 l_creation_date ben_elig_per_elctbl_chc.creation_date%TYPE;
1281 l_last_update_date ben_elig_per_elctbl_chc.last_update_date%TYPE;
1282 l_last_updated_by ben_elig_per_elctbl_chc.last_updated_by%TYPE;
1283 l_last_update_login ben_elig_per_elctbl_chc.last_update_login%TYPE;
1284 l_elig_flag varchar2(30);
1285 l_inelig_rsn_cd varchar2(30);
1286 --
1287 l_sysdate date;
1288 --
1289 begin
1290 --
1291 g_debug := hr_utility.debug_enabled;
1292 if g_debug then
1293 l_proc := g_package||'create_perf_ELIG_PER_ELC_CHC';
1294 hr_utility.set_location('Entering:'|| l_proc, 10);
1295 end if;
1296 --
1297 -- Issue a savepoint if operating in validation only mode
1298 --
1299 savepoint create_perf_ELIG_PER_ELC_CHC;
1300 --
1301 -- Create or find ben_pil_elctbl_chc_popl
1302 --
1303 CreOrSel_pil_elctbl_chc_popl
1304 (p_per_in_ler_id => p_per_in_ler_id
1305 ,p_effective_date => p_effective_date
1306 ,p_business_group_id => p_business_group_id
1307 ,p_pgm_id => p_pgm_id
1308 ,p_plip_id => p_plip_id
1309 ,p_pl_id => p_pl_id
1310 ,p_oipl_id => p_oipl_id
1311 ,p_yr_perd_id => p_yr_perd_id
1312 ,p_uom => p_uom
1313 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
1314 ,p_dflt_enrt_dt => p_dflt_enrt_dt
1315 ,p_cls_enrt_dt_to_use_cd => p_cls_enrt_dt_to_use_cd
1316 ,p_enrt_typ_cycl_cd => p_enrt_typ_cycl_cd
1317 ,p_enrt_perd_end_dt => p_enrt_perd_end_dt
1318 ,p_enrt_perd_strt_dt => p_enrt_perd_strt_dt
1319 ,p_procg_end_dt => p_procg_end_dt
1320 ,p_lee_rsn_id => p_lee_rsn_id
1321 ,p_enrt_perd_id => p_enrt_perd_id
1322 ,p_request_id => p_request_id
1323 ,p_program_application_id => p_program_application_id
1324 ,p_program_id => p_program_id
1325 ,p_program_update_date => p_program_update_date
1326 ,p_ws_mgr_id => p_ws_mgr_id
1327 ,p_assignment_id => p_assignment_id
1328 --
1329 ,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
1330 ,p_oiplip_id => l_oiplip_id
1331 );
1332 --
1333 -- Insert the row
1334 --
1335 -- Set the object version number for the insert
1336 --
1337 l_object_version_number := 1;
1338 --
1339 ben_epe_shd.g_api_dml := true; -- Set the api dml status
1340 --
1341 -- CWB Changes Start
1342 --
1343 l_elig_flag := p_elig_flag;
1344 if p_elctbl_flag = 'N' then
1345 --
1346 l_elig_flag := 'N';
1347 --
1348 end if;
1349 --
1350 l_inelig_rsn_cd := p_inelig_rsn_cd;
1351 if l_elig_flag = 'N' and p_inelig_rsn_cd is null then
1352 --
1353 l_inelig_rsn_cd := 'OTH'; -- Other
1354 --
1355 end if;
1356 --
1357 -- CWB : End
1358 --
1362 insert into ben_elig_per_elctbl_chc
1359 if g_debug then
1360 hr_utility.set_location('Insert EPE: '||l_proc, 5);
1361 end if;
1363 ( elig_per_elctbl_chc_id,
1364 -- enrt_typ_cycl_cd,
1365 enrt_cvg_strt_dt_cd,
1366 -- enrt_perd_end_dt,
1367 -- enrt_perd_strt_dt,
1368 enrt_cvg_strt_dt_rl,
1369 -- rt_strt_dt,
1370 -- rt_strt_dt_rl,
1371 -- rt_strt_dt_cd,
1372 ctfn_rqd_flag,
1373 pil_elctbl_chc_popl_id,
1374 roll_crs_flag,
1375 crntly_enrd_flag,
1376 dflt_flag,
1377 elctbl_flag,
1378 mndtry_flag,
1379 in_pndg_wkflow_flag,
1380 -- dflt_enrt_dt,
1381 dpnt_cvg_strt_dt_cd,
1382 dpnt_cvg_strt_dt_rl,
1383 enrt_cvg_strt_dt,
1384 alws_dpnt_dsgn_flag,
1385 dpnt_dsgn_cd,
1386 ler_chg_dpnt_cvg_cd,
1387 erlst_deenrt_dt,
1388 procg_end_dt,
1389 comp_lvl_cd,
1390 pl_id,
1391 oipl_id,
1392 pgm_id,
1393 plip_id,
1394 ptip_id,
1395 pl_typ_id,
1396 oiplip_id,
1397 cmbn_plip_id,
1398 cmbn_ptip_id,
1399 cmbn_ptip_opt_id,
1400 assignment_id,
1401 spcl_rt_pl_id,
1402 spcl_rt_oipl_id,
1403 must_enrl_anthr_pl_id,
1404 interim_elig_per_elctbl_chc_id,
1405 prtt_enrt_rslt_id,
1406 bnft_prvdr_pool_id,
1407 per_in_ler_id,
1408 yr_perd_id,
1409 auto_enrt_flag,
1410 business_group_id,
1411 pl_ordr_num,
1412 plip_ordr_num,
1413 ptip_ordr_num,
1414 oipl_ordr_num,
1415 -- cwb
1416 comments,
1417 elig_flag,
1418 elig_ovrid_dt,
1419 elig_ovrid_person_id,
1420 inelig_rsn_cd,
1421 mgr_ovrid_dt,
1422 mgr_ovrid_person_id,
1423 ws_mgr_id,
1424 -- cwb
1425 epe_attribute_category,
1426 epe_attribute1,
1427 epe_attribute2,
1428 epe_attribute3,
1429 epe_attribute4,
1430 epe_attribute5,
1431 epe_attribute6,
1432 epe_attribute7,
1433 epe_attribute8,
1434 epe_attribute9,
1435 epe_attribute10,
1436 epe_attribute11,
1437 epe_attribute12,
1438 epe_attribute13,
1439 epe_attribute14,
1440 epe_attribute15,
1441 epe_attribute16,
1442 epe_attribute17,
1443 epe_attribute18,
1444 epe_attribute19,
1445 epe_attribute20,
1446 epe_attribute21,
1447 epe_attribute22,
1448 epe_attribute23,
1449 epe_attribute24,
1450 epe_attribute25,
1451 epe_attribute26,
1452 epe_attribute27,
1453 epe_attribute28,
1454 epe_attribute29,
1455 epe_attribute30,
1456 approval_status_cd,
1457 fonm_cvg_strt_dt,
1458 cryfwd_elig_dpnt_cd,
1459 request_id,
1460 program_application_id,
1461 program_id,
1462 program_update_date,
1463 object_version_number
1464 )
1465 Values
1466 (ben_elig_per_elctbl_chc_s.nextval,
1467 -- p_enrt_typ_cycl_cd,
1468 p_enrt_cvg_strt_dt_cd,
1469 -- p_enrt_perd_end_dt,
1470 -- p_enrt_perd_strt_dt,
1471 p_enrt_cvg_strt_dt_rl,
1472 -- p_rt_strt_dt,
1473 -- p_rt_strt_dt_rl,
1474 -- p_rt_strt_dt_cd,
1475 p_ctfn_rqd_flag,
1476 l_pil_elctbl_chc_popl_id,
1477 p_roll_crs_flag,
1478 p_crntly_enrd_flag,
1479 p_dflt_flag,
1480 p_elctbl_flag,
1481 p_mndtry_flag,
1482 p_in_pndg_wkflow_flag,
1483 -- p_dflt_enrt_dt,
1484 p_dpnt_cvg_strt_dt_cd,
1485 p_dpnt_cvg_strt_dt_rl,
1486 p_enrt_cvg_strt_dt,
1487 p_alws_dpnt_dsgn_flag,
1488 p_dpnt_dsgn_cd,
1489 p_ler_chg_dpnt_cvg_cd,
1490 p_erlst_deenrt_dt,
1491 p_procg_end_dt,
1492 p_comp_lvl_cd,
1493 p_pl_id,
1494 p_oipl_id,
1495 p_pgm_id,
1496 p_plip_id,
1497 p_ptip_id,
1498 p_pl_typ_id,
1499 l_oiplip_id,
1500 p_cmbn_plip_id,
1501 p_cmbn_ptip_id,
1502 p_cmbn_ptip_opt_id,
1503 p_assignment_id,
1504 p_spcl_rt_pl_id,
1505 p_spcl_rt_oipl_id,
1506 p_must_enrl_anthr_pl_id,
1507 p_int_elig_per_elctbl_chc_id,
1508 p_prtt_enrt_rslt_id,
1509 p_bnft_prvdr_pool_id,
1510 p_per_in_ler_id,
1511 p_yr_perd_id,
1512 p_auto_enrt_flag,
1513 p_business_group_id,
1514 p_pl_ordr_num,
1515 p_plip_ordr_num,
1516 p_ptip_ordr_num,
1517 p_oipl_ordr_num,
1518 -- cwb
1519 p_comments,
1520 l_elig_flag,
1521 p_elig_ovrid_dt,
1522 p_elig_ovrid_person_id,
1523 l_inelig_rsn_cd,
1524 p_mgr_ovrid_dt,
1525 p_mgr_ovrid_person_id,
1526 p_ws_mgr_id,
1527 -- cwb
1528 p_epe_attribute_category,
1529 p_epe_attribute1,
1530 p_epe_attribute2,
1531 p_epe_attribute3,
1532 p_epe_attribute4,
1533 p_epe_attribute5,
1534 p_epe_attribute6,
1535 p_epe_attribute7,
1536 p_epe_attribute8,
1537 p_epe_attribute9,
1538 p_epe_attribute10,
1539 p_epe_attribute11,
1540 p_epe_attribute12,
1541 p_epe_attribute13,
1542 p_epe_attribute14,
1543 p_epe_attribute15,
1544 p_epe_attribute16,
1545 p_epe_attribute17,
1546 p_epe_attribute18,
1547 p_epe_attribute19,
1548 p_epe_attribute20,
1549 p_epe_attribute21,
1550 p_epe_attribute22,
1551 p_epe_attribute23,
1552 p_epe_attribute24,
1553 p_epe_attribute25,
1554 p_epe_attribute26,
1555 p_epe_attribute27,
1556 p_epe_attribute28,
1557 p_epe_attribute29,
1558 p_epe_attribute30,
1559 p_approval_status_cd,
1560 p_fonm_cvg_strt_dt,
1561 p_cryfwd_elig_dpnt_cd,
1565 p_program_update_date,
1562 p_request_id,
1563 p_program_application_id,
1564 p_program_id,
1566 l_object_version_number
1567 ) RETURNING elig_per_elctbl_chc_id into l_elig_per_elctbl_chc_id;
1568 if g_debug then
1569 hr_utility.set_location('Dn Insert: '||l_proc, 5);
1570 end if;
1571 --
1572 ben_epe_shd.g_api_dml := false; -- Unset the api dml status
1573 --
1574 -- When in validation only mode raise the Validate_Enabled exception
1575 --
1576 if p_validate then
1577 raise hr_api.validate_enabled;
1578 end if;
1579 --
1580 -- Set electable choice current row globals
1581 --
1582 -- Check if bendenrr is being called recursively to create the parent EPE
1583 -- for the oipl. We do not want to modify the current EPE values if creating
1584 -- the parent plan EPE for an oipl EPE.
1585 --
1586 -- CWB Chnages :
1587 -- Do not call the caching code if called from CWB benmngle mode
1588 --
1589 if nvl(p_mode, 'OAB') <> 'W' then
1590 --
1591 if nvl(ben_epe_cache.g_currcobjepe_row.pl_id,hr_api.g_number) = nvl(p_pl_id,hr_api.g_number)
1592 and nvl(ben_epe_cache.g_currcobjepe_row.plip_id,hr_api.g_number) = nvl(p_plip_id,hr_api.g_number)
1593 and ben_epe_cache.g_currcobjepe_row.oipl_id is not null
1594 and p_oipl_id is null
1595 then
1596 --
1597 null;
1598 --
1599 else
1600 --
1601 -- BENCVRGE
1602 --
1603 ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id;
1604 ben_epe_cache.g_currcobjepe_row.pl_id := p_pl_id;
1605 ben_epe_cache.g_currcobjepe_row.plip_id := p_plip_id;
1606 ben_epe_cache.g_currcobjepe_row.oipl_id := p_oipl_id;
1607 --
1608 -- BENDEPEN
1609 --
1610 ben_epe_cache.g_currcobjepe_row.elctbl_flag := p_elctbl_flag;
1611 ben_epe_cache.g_currcobjepe_row.per_in_ler_id := p_per_in_ler_id;
1612 ben_epe_cache.g_currcobjepe_row.business_group_id := p_business_group_id;
1613 ben_epe_cache.g_currcobjepe_row.object_version_number := l_object_version_number;
1614 --
1615 -- BENCHCTF
1616 --
1617 ben_epe_cache.g_currcobjepe_row.comp_lvl_cd := p_comp_lvl_cd;
1618 ben_epe_cache.g_currcobjepe_row.pgm_id := p_pgm_id;
1619 ben_epe_cache.g_currcobjepe_row.pl_typ_id := p_pl_typ_id;
1620 ben_epe_cache.g_currcobjepe_row.ctfn_rqd_flag := p_ctfn_rqd_flag;
1621 --
1622 end if;
1623 --
1624 end if;
1625 --
1626 -- Set all output arguments
1627 --
1628 p_elig_per_elctbl_chc_id := l_elig_per_elctbl_chc_id;
1629 p_object_version_number := l_object_version_number;
1630 --
1631 if g_debug then
1632 hr_utility.set_location(' Leaving:'||l_proc, 99);
1633 end if;
1634 --
1635 exception
1636 --
1637 when hr_api.validate_enabled then
1638 --
1639 -- As the Validate_Enabled exception has been raised
1640 -- we must rollback to the savepoint
1641 --
1642 ROLLBACK TO create_perf_ELIG_PER_ELC_CHC;
1643 --
1644 -- Only set output warning arguments
1645 -- (Any key or derived arguments must be set to null
1646 -- when validation only mode is being used.)
1647 --
1648 p_elig_per_elctbl_chc_id := null;
1649 p_object_version_number := null;
1650 if g_debug then
1651 hr_utility.set_location(' Leaving:'||l_proc, 80);
1652 end if;
1653 --
1654 when others then
1655 --
1656 -- A validation or unexpected error has occured
1657 --
1658 p_elig_per_elctbl_chc_id := null;
1659 p_object_version_number := null;
1660 if g_debug then
1661 hr_utility.set_location(' Leaving:'||l_proc, 80);
1662 end if;
1663 --
1664 ROLLBACK TO create_perf_ELIG_PER_ELC_CHC;
1665 raise;
1666 --
1667 end create_perf_ELIG_PER_ELC_CHC;
1668 -- ----------------------------------------------------------------------------
1669 -- |------------------------< update_ELIG_PER_ELC_CHC >--- ------------------|
1670 -- ----------------------------------------------------------------------------
1671 --
1672 procedure update_ELIG_PER_ELC_CHC
1673 (p_validate in boolean default false
1674 ,p_elig_per_elctbl_chc_id in number
1675 ,p_enrt_typ_cycl_cd in varchar2 default hr_api.g_varchar2
1676 ,p_enrt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
1677 ,p_enrt_perd_end_dt in date default hr_api.g_date
1678 ,p_enrt_perd_strt_dt in date default hr_api.g_date
1679 ,p_enrt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
1680 ,p_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
1681 ,p_pil_elctbl_chc_popl_id in number default hr_api.g_number
1682 ,p_roll_crs_flag in varchar2 default hr_api.g_varchar2
1683 ,p_crntly_enrd_flag in varchar2 default hr_api.g_varchar2
1684 ,p_dflt_flag in varchar2 default hr_api.g_varchar2
1685 ,p_elctbl_flag in varchar2 default hr_api.g_varchar2
1686 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
1687 ,p_in_pndg_wkflow_flag in varchar2 default hr_api.g_varchar2
1688 ,p_dflt_enrt_dt in date default hr_api.g_date
1689 ,p_dpnt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
1690 ,p_dpnt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
1691 ,p_enrt_cvg_strt_dt in date default hr_api.g_date
1695 ,p_erlst_deenrt_dt in date default hr_api.g_date
1692 ,p_alws_dpnt_dsgn_flag in varchar2 default hr_api.g_varchar2
1693 ,p_dpnt_dsgn_cd in varchar2 default hr_api.g_varchar2
1694 ,p_ler_chg_dpnt_cvg_cd in varchar2 default hr_api.g_varchar2
1699 ,p_oipl_id in number default hr_api.g_number
1696 ,p_procg_end_dt in date default hr_api.g_date
1697 ,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
1698 ,p_pl_id in number default hr_api.g_number
1700 ,p_pgm_id in number default hr_api.g_number
1701 ,p_plip_id in number default hr_api.g_number
1702 ,p_ptip_id in number default hr_api.g_number
1703 ,p_pl_typ_id in number default hr_api.g_number
1704 ,p_oiplip_id in number default hr_api.g_number
1705 ,p_cmbn_plip_id in number default hr_api.g_number
1706 ,p_cmbn_ptip_id in number default hr_api.g_number
1707 ,p_cmbn_ptip_opt_id in number default hr_api.g_number
1708 ,p_assignment_id in number default hr_api.g_number
1709 ,p_spcl_rt_pl_id in number default hr_api.g_number
1710 ,p_spcl_rt_oipl_id in number default hr_api.g_number
1711 ,p_must_enrl_anthr_pl_id in number default hr_api.g_number
1712 ,p_int_elig_per_elctbl_chc_id in number default hr_api.g_number
1713 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
1714 ,p_bnft_prvdr_pool_id in number default hr_api.g_number
1715 ,p_per_in_ler_id in number default hr_api.g_number
1716 ,p_yr_perd_id in number default hr_api.g_number
1717 ,p_auto_enrt_flag in varchar2 default hr_api.g_varchar2
1718 ,p_business_group_id in number default hr_api.g_number
1719 ,p_pl_ordr_num in number default hr_api.g_number
1720 ,p_plip_ordr_num in number default hr_api.g_number
1721 ,p_ptip_ordr_num in number default hr_api.g_number
1722 ,p_oipl_ordr_num in number default hr_api.g_number
1723 -- cwb
1724 ,p_comments in varchar2 default hr_api.g_varchar2
1725 ,p_elig_flag in varchar2 default hr_api.g_varchar2
1726 ,p_elig_ovrid_dt in date default hr_api.g_date
1727 ,p_elig_ovrid_person_id in number default hr_api.g_number
1728 ,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
1729 ,p_mgr_ovrid_dt in date default hr_api.g_date
1730 ,p_mgr_ovrid_person_id in number default hr_api.g_number
1731 ,p_ws_mgr_id in number default hr_api.g_number
1732 -- cwb
1733 ,p_epe_attribute_category in varchar2 default hr_api.g_varchar2
1734 ,p_epe_attribute1 in varchar2 default hr_api.g_varchar2
1735 ,p_epe_attribute2 in varchar2 default hr_api.g_varchar2
1736 ,p_epe_attribute3 in varchar2 default hr_api.g_varchar2
1737 ,p_epe_attribute4 in varchar2 default hr_api.g_varchar2
1738 ,p_epe_attribute5 in varchar2 default hr_api.g_varchar2
1739 ,p_epe_attribute6 in varchar2 default hr_api.g_varchar2
1740 ,p_epe_attribute7 in varchar2 default hr_api.g_varchar2
1741 ,p_epe_attribute8 in varchar2 default hr_api.g_varchar2
1742 ,p_epe_attribute9 in varchar2 default hr_api.g_varchar2
1743 ,p_epe_attribute10 in varchar2 default hr_api.g_varchar2
1744 ,p_epe_attribute11 in varchar2 default hr_api.g_varchar2
1745 ,p_epe_attribute12 in varchar2 default hr_api.g_varchar2
1746 ,p_epe_attribute13 in varchar2 default hr_api.g_varchar2
1747 ,p_epe_attribute14 in varchar2 default hr_api.g_varchar2
1748 ,p_epe_attribute15 in varchar2 default hr_api.g_varchar2
1749 ,p_epe_attribute16 in varchar2 default hr_api.g_varchar2
1750 ,p_epe_attribute17 in varchar2 default hr_api.g_varchar2
1751 ,p_epe_attribute18 in varchar2 default hr_api.g_varchar2
1752 ,p_epe_attribute19 in varchar2 default hr_api.g_varchar2
1753 ,p_epe_attribute20 in varchar2 default hr_api.g_varchar2
1754 ,p_epe_attribute21 in varchar2 default hr_api.g_varchar2
1755 ,p_epe_attribute22 in varchar2 default hr_api.g_varchar2
1756 ,p_epe_attribute23 in varchar2 default hr_api.g_varchar2
1757 ,p_epe_attribute24 in varchar2 default hr_api.g_varchar2
1761 ,p_epe_attribute28 in varchar2 default hr_api.g_varchar2
1758 ,p_epe_attribute25 in varchar2 default hr_api.g_varchar2
1759 ,p_epe_attribute26 in varchar2 default hr_api.g_varchar2
1760 ,p_epe_attribute27 in varchar2 default hr_api.g_varchar2
1762 ,p_epe_attribute29 in varchar2 default hr_api.g_varchar2
1763 ,p_epe_attribute30 in varchar2 default hr_api.g_varchar2
1764 ,p_approval_status_cd in varchar2 default hr_api.g_varchar2
1765 ,p_fonm_cvg_strt_dt in date default hr_api.g_date
1766 ,p_cryfwd_elig_dpnt_cd in varchar2 default hr_api.g_varchar2
1767 ,p_request_id in number default hr_api.g_number
1768 ,p_program_application_id in number default hr_api.g_number
1769 ,p_program_id in number default hr_api.g_number
1770 ,p_program_update_date in date default hr_api.g_date
1771 ,p_object_version_number in out nocopy number
1772 ,p_effective_date in date
1773 ) is
1774 --
1775 -- Declare cursors and local variables
1776 --
1777 l_proc varchar2(72) ;
1778 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
1779 --
1780 begin
1781 --
1782 g_debug := hr_utility.debug_enabled;
1783 if g_debug then
1784 l_proc := g_package||'update_ELIG_PER_ELC_CHC';
1785 hr_utility.set_location('Entering:'|| l_proc, 10);
1786 end if;
1787 --
1788 -- Issue a savepoint if operating in validation only mode
1789 --
1790 savepoint update_ELIG_PER_ELC_CHC;
1791 --
1792 -- Process Logic
1793 --
1794 l_object_version_number := p_object_version_number;
1795 --
1796 begin
1797 --
1798 -- Start of API User Hook for the before hook of update_ELIG_PER_ELC_CHC
1799 --
1800 ben_ELIG_PER_ELC_CHC_bk2.update_ELIG_PER_ELC_CHC_b
1801 (
1802 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1803 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
1804 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
1805 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
1806 ,p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id
1810 ,p_elctbl_flag => p_elctbl_flag
1807 ,p_roll_crs_flag => p_roll_crs_flag
1808 ,p_crntly_enrd_flag => p_crntly_enrd_flag
1809 ,p_dflt_flag => p_dflt_flag
1811 ,p_mndtry_flag => p_mndtry_flag
1812 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
1813 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
1814 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
1815 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
1816 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
1817 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
1818 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
1819 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
1820 ,p_procg_end_dt => p_procg_end_dt
1821 ,p_comp_lvl_cd => p_comp_lvl_cd
1822 ,p_pl_id => p_pl_id
1823 ,p_oipl_id => p_oipl_id
1824 ,p_pgm_id => p_pgm_id
1825 ,p_plip_id => p_plip_id
1826 ,p_ptip_id => p_ptip_id
1827 ,p_pl_typ_id => p_pl_typ_id
1828 ,p_oiplip_id => p_oiplip_id
1829 ,p_cmbn_plip_id => p_cmbn_plip_id
1830 ,p_cmbn_ptip_id => p_cmbn_ptip_id
1831 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
1832 ,p_assignment_id => p_assignment_id
1833 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
1834 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
1835 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
1836 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
1837 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1838 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1839 ,p_per_in_ler_id => p_per_in_ler_id
1840 ,p_yr_perd_id => p_yr_perd_id
1841 ,p_auto_enrt_flag => p_auto_enrt_flag
1842 ,p_business_group_id => p_business_group_id
1843 ,p_pl_ordr_num => p_pl_ordr_num
1844 ,p_plip_ordr_num => p_plip_ordr_num
1845 ,p_ptip_ordr_num => p_ptip_ordr_num
1846 ,p_oipl_ordr_num => p_oipl_ordr_num
1847 --cwb
1848 ,p_comments => p_comments
1849 ,p_elig_flag => p_elig_flag
1850 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1851 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1852 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1853 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
1854 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
1855 ,p_ws_mgr_id => p_ws_mgr_id
1856 --cwb
1860 ,p_epe_attribute3 => p_epe_attribute3
1857 ,p_epe_attribute_category => p_epe_attribute_category
1858 ,p_epe_attribute1 => p_epe_attribute1
1859 ,p_epe_attribute2 => p_epe_attribute2
1861 ,p_epe_attribute4 => p_epe_attribute4
1862 ,p_epe_attribute5 => p_epe_attribute5
1863 ,p_epe_attribute6 => p_epe_attribute6
1864 ,p_epe_attribute7 => p_epe_attribute7
1865 ,p_epe_attribute8 => p_epe_attribute8
1866 ,p_epe_attribute9 => p_epe_attribute9
1867 ,p_epe_attribute10 => p_epe_attribute10
1868 ,p_epe_attribute11 => p_epe_attribute11
1869 ,p_epe_attribute12 => p_epe_attribute12
1870 ,p_epe_attribute13 => p_epe_attribute13
1871 ,p_epe_attribute14 => p_epe_attribute14
1872 ,p_epe_attribute15 => p_epe_attribute15
1873 ,p_epe_attribute16 => p_epe_attribute16
1874 ,p_epe_attribute17 => p_epe_attribute17
1875 ,p_epe_attribute18 => p_epe_attribute18
1876 ,p_epe_attribute19 => p_epe_attribute19
1877 ,p_epe_attribute20 => p_epe_attribute20
1878 ,p_epe_attribute21 => p_epe_attribute21
1879 ,p_epe_attribute22 => p_epe_attribute22
1880 ,p_epe_attribute23 => p_epe_attribute23
1881 ,p_epe_attribute24 => p_epe_attribute24
1882 ,p_epe_attribute25 => p_epe_attribute25
1883 ,p_epe_attribute26 => p_epe_attribute26
1884 ,p_epe_attribute27 => p_epe_attribute27
1885 ,p_epe_attribute28 => p_epe_attribute28
1886 ,p_epe_attribute29 => p_epe_attribute29
1887 ,p_epe_attribute30 => p_epe_attribute30
1888 ,p_approval_status_cd => p_approval_status_cd
1889 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
1890 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
1891 ,p_request_id => p_request_id
1892 ,p_program_application_id => p_program_application_id
1893 ,p_program_id => p_program_id
1894 ,p_program_update_date => p_program_update_date
1895 ,p_object_version_number => p_object_version_number
1896 ,p_effective_date => trunc(p_effective_date)
1897 );
1898 exception
1899 when hr_api.cannot_find_prog_unit then
1900 hr_api.cannot_find_prog_unit_error
1901 (p_module_name => 'UPDATE_ELIG_PER_ELC_CHC'
1902 ,p_hook_type => 'BP'
1903 );
1904 --
1905 -- End of API User Hook for the before hook of update_ELIG_PER_ELC_CHC
1906 --
1907 end;
1908 --
1909 if g_debug then
1910 hr_utility.set_location(' EPE Upd'|| l_proc, 10);
1911 end if;
1912 ben_epe_upd.upd
1913 (
1914 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1915 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
1916 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
1917 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
1918 ,p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id
1919 ,p_roll_crs_flag => p_roll_crs_flag
1920 ,p_crntly_enrd_flag => p_crntly_enrd_flag
1921 ,p_dflt_flag => p_dflt_flag
1922 ,p_elctbl_flag => p_elctbl_flag
1923 ,p_mndtry_flag => p_mndtry_flag
1924 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
1925 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
1926 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
1927 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
1928 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
1929 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
1930 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
1931 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
1932 ,p_procg_end_dt => p_procg_end_dt
1933 ,p_comp_lvl_cd => p_comp_lvl_cd
1934 ,p_pl_id => p_pl_id
1935 ,p_oipl_id => p_oipl_id
1936 ,p_pgm_id => p_pgm_id
1937 ,p_plip_id => p_plip_id
1938 ,p_ptip_id => p_ptip_id
1939 ,p_pl_typ_id => p_pl_typ_id
1940 ,p_oiplip_id => p_oiplip_id
1941 ,p_cmbn_plip_id => p_cmbn_plip_id
1942 ,p_cmbn_ptip_id => p_cmbn_ptip_id
1943 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
1944 ,p_assignment_id => p_assignment_id
1945 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
1946 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
1947 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
1948 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
1949 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1950 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
1951 ,p_per_in_ler_id => p_per_in_ler_id
1952 ,p_yr_perd_id => p_yr_perd_id
1953 ,p_auto_enrt_flag => p_auto_enrt_flag
1954 ,p_business_group_id => p_business_group_id
1955 ,p_pl_ordr_num => p_pl_ordr_num
1956 ,p_plip_ordr_num => p_plip_ordr_num
1957 ,p_ptip_ordr_num => p_ptip_ordr_num
1958 ,p_oipl_ordr_num => p_oipl_ordr_num
1959 --cwb
1960 ,p_comments => p_comments
1964 ,p_inelig_rsn_cd => p_inelig_rsn_cd
1961 ,p_elig_flag => p_elig_flag
1962 ,p_elig_ovrid_dt => p_elig_ovrid_dt
1963 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
1965 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
1966 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
1967 ,p_ws_mgr_id => p_ws_mgr_id
1968 --cwb
1969 ,p_epe_attribute_category => p_epe_attribute_category
1970 ,p_epe_attribute1 => p_epe_attribute1
1971 ,p_epe_attribute2 => p_epe_attribute2
1972 ,p_epe_attribute3 => p_epe_attribute3
1973 ,p_epe_attribute4 => p_epe_attribute4
1974 ,p_epe_attribute5 => p_epe_attribute5
1975 ,p_epe_attribute6 => p_epe_attribute6
1976 ,p_epe_attribute7 => p_epe_attribute7
1977 ,p_epe_attribute8 => p_epe_attribute8
1978 ,p_epe_attribute9 => p_epe_attribute9
1979 ,p_epe_attribute10 => p_epe_attribute10
1980 ,p_epe_attribute11 => p_epe_attribute11
1981 ,p_epe_attribute12 => p_epe_attribute12
1982 ,p_epe_attribute13 => p_epe_attribute13
1983 ,p_epe_attribute14 => p_epe_attribute14
1984 ,p_epe_attribute15 => p_epe_attribute15
1985 ,p_epe_attribute16 => p_epe_attribute16
1986 ,p_epe_attribute17 => p_epe_attribute17
1987 ,p_epe_attribute18 => p_epe_attribute18
1988 ,p_epe_attribute19 => p_epe_attribute19
1989 ,p_epe_attribute20 => p_epe_attribute20
1990 ,p_epe_attribute21 => p_epe_attribute21
1991 ,p_epe_attribute22 => p_epe_attribute22
1992 ,p_epe_attribute23 => p_epe_attribute23
1993 ,p_epe_attribute24 => p_epe_attribute24
1994 ,p_epe_attribute25 => p_epe_attribute25
1995 ,p_epe_attribute26 => p_epe_attribute26
1996 ,p_epe_attribute27 => p_epe_attribute27
1997 ,p_epe_attribute28 => p_epe_attribute28
1998 ,p_epe_attribute29 => p_epe_attribute29
1999 ,p_epe_attribute30 => p_epe_attribute30
2000 ,p_approval_status_cd => p_approval_status_cd
2001 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
2002 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
2003 ,p_request_id => p_request_id
2004 ,p_program_application_id => p_program_application_id
2005 ,p_program_id => p_program_id
2006 ,p_program_update_date => p_program_update_date
2007 ,p_object_version_number => l_object_version_number
2008 ,p_effective_date => trunc(p_effective_date)
2009 );
2010 if g_debug then
2011 hr_utility.set_location(' Dn EPE Upd'|| l_proc, 10);
2012 end if;
2013 --
2014 begin
2015 --
2016 -- Start of API User Hook for the after hook of update_ELIG_PER_ELC_CHC
2017 --
2018 ben_ELIG_PER_ELC_CHC_bk2.update_ELIG_PER_ELC_CHC_a
2019 (
2020 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2021 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
2022 ,p_enrt_cvg_strt_dt_rl => p_enrt_cvg_strt_dt_rl
2023 ,p_ctfn_rqd_flag => p_ctfn_rqd_flag
2024 ,p_pil_elctbl_chc_popl_id => p_pil_elctbl_chc_popl_id
2025 ,p_roll_crs_flag => p_roll_crs_flag
2026 ,p_crntly_enrd_flag => p_crntly_enrd_flag
2027 ,p_dflt_flag => p_dflt_flag
2028 ,p_elctbl_flag => p_elctbl_flag
2029 ,p_mndtry_flag => p_mndtry_flag
2030 ,p_in_pndg_wkflow_flag => p_in_pndg_wkflow_flag
2031 ,p_dpnt_cvg_strt_dt_cd => p_dpnt_cvg_strt_dt_cd
2032 ,p_dpnt_cvg_strt_dt_rl => p_dpnt_cvg_strt_dt_rl
2033 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
2034 ,p_alws_dpnt_dsgn_flag => p_alws_dpnt_dsgn_flag
2035 ,p_dpnt_dsgn_cd => p_dpnt_dsgn_cd
2036 ,p_ler_chg_dpnt_cvg_cd => p_ler_chg_dpnt_cvg_cd
2037 ,p_erlst_deenrt_dt => p_erlst_deenrt_dt
2038 ,p_procg_end_dt => p_procg_end_dt
2039 ,p_comp_lvl_cd => p_comp_lvl_cd
2040 ,p_pl_id => p_pl_id
2041 ,p_oipl_id => p_oipl_id
2042 ,p_pgm_id => p_pgm_id
2043 ,p_plip_id => p_plip_id
2044 ,p_ptip_id => p_ptip_id
2045 ,p_pl_typ_id => p_pl_typ_id
2046 ,p_oiplip_id => p_oiplip_id
2047 ,p_cmbn_plip_id => p_cmbn_plip_id
2048 ,p_cmbn_ptip_id => p_cmbn_ptip_id
2049 ,p_cmbn_ptip_opt_id => p_cmbn_ptip_opt_id
2050 ,p_assignment_id => p_assignment_id
2051 ,p_spcl_rt_pl_id => p_spcl_rt_pl_id
2052 ,p_spcl_rt_oipl_id => p_spcl_rt_oipl_id
2053 ,p_must_enrl_anthr_pl_id => p_must_enrl_anthr_pl_id
2054 ,p_int_elig_per_elctbl_chc_id => p_int_elig_per_elctbl_chc_id
2055 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
2056 ,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
2057 ,p_per_in_ler_id => p_per_in_ler_id
2058 ,p_yr_perd_id => p_yr_perd_id
2059 ,p_auto_enrt_flag => p_auto_enrt_flag
2060 ,p_business_group_id => p_business_group_id
2061 ,p_pl_ordr_num => p_pl_ordr_num
2065 --cwb
2062 ,p_plip_ordr_num => p_plip_ordr_num
2063 ,p_ptip_ordr_num => p_ptip_ordr_num
2064 ,p_oipl_ordr_num => p_oipl_ordr_num
2066 ,p_comments => p_comments
2067 ,p_elig_flag => p_elig_flag
2068 ,p_elig_ovrid_dt => p_elig_ovrid_dt
2069 ,p_elig_ovrid_person_id => p_elig_ovrid_person_id
2070 ,p_inelig_rsn_cd => p_inelig_rsn_cd
2071 ,p_mgr_ovrid_dt => p_mgr_ovrid_dt
2072 ,p_mgr_ovrid_person_id => p_mgr_ovrid_person_id
2073 ,p_ws_mgr_id => p_ws_mgr_id
2074 --cwb
2075 ,p_epe_attribute_category => p_epe_attribute_category
2076 ,p_epe_attribute1 => p_epe_attribute1
2077 ,p_epe_attribute2 => p_epe_attribute2
2078 ,p_epe_attribute3 => p_epe_attribute3
2079 ,p_epe_attribute4 => p_epe_attribute4
2080 ,p_epe_attribute5 => p_epe_attribute5
2081 ,p_epe_attribute6 => p_epe_attribute6
2082 ,p_epe_attribute7 => p_epe_attribute7
2083 ,p_epe_attribute8 => p_epe_attribute8
2084 ,p_epe_attribute9 => p_epe_attribute9
2085 ,p_epe_attribute10 => p_epe_attribute10
2086 ,p_epe_attribute11 => p_epe_attribute11
2087 ,p_epe_attribute12 => p_epe_attribute12
2088 ,p_epe_attribute13 => p_epe_attribute13
2089 ,p_epe_attribute14 => p_epe_attribute14
2090 ,p_epe_attribute15 => p_epe_attribute15
2091 ,p_epe_attribute16 => p_epe_attribute16
2092 ,p_epe_attribute17 => p_epe_attribute17
2093 ,p_epe_attribute18 => p_epe_attribute18
2094 ,p_epe_attribute19 => p_epe_attribute19
2095 ,p_epe_attribute20 => p_epe_attribute20
2096 ,p_epe_attribute21 => p_epe_attribute21
2097 ,p_epe_attribute22 => p_epe_attribute22
2098 ,p_epe_attribute23 => p_epe_attribute23
2099 ,p_epe_attribute24 => p_epe_attribute24
2100 ,p_epe_attribute25 => p_epe_attribute25
2101 ,p_epe_attribute26 => p_epe_attribute26
2102 ,p_epe_attribute27 => p_epe_attribute27
2103 ,p_epe_attribute28 => p_epe_attribute28
2104 ,p_epe_attribute29 => p_epe_attribute29
2105 ,p_epe_attribute30 => p_epe_attribute30
2106 ,p_approval_status_cd => p_approval_status_cd
2107 ,p_fonm_cvg_strt_dt => p_fonm_cvg_strt_dt
2108 ,p_cryfwd_elig_dpnt_cd => p_cryfwd_elig_dpnt_cd
2109 ,p_request_id => p_request_id
2110 ,p_program_application_id => p_program_application_id
2111 ,p_program_id => p_program_id
2112 ,p_program_update_date => p_program_update_date
2113 ,p_object_version_number => l_object_version_number
2114 ,p_effective_date => trunc(p_effective_date)
2115 );
2116 exception
2117 when hr_api.cannot_find_prog_unit then
2118 hr_api.cannot_find_prog_unit_error
2119 (p_module_name => 'UPDATE_ELIG_PER_ELC_CHC'
2120 ,p_hook_type => 'AP'
2121 );
2122 --
2123 -- End of API User Hook for the after hook of update_ELIG_PER_ELC_CHC
2124 --
2125 end;
2126 --
2127 if g_debug then
2128 hr_utility.set_location(l_proc, 60);
2129 end if;
2130 --
2131 -- When in validation only mode raise the Validate_Enabled exception
2132 --
2133 if p_validate then
2134 raise hr_api.validate_enabled;
2135 end if;
2136 --
2137 -- Set all output arguments
2138 --
2139 p_object_version_number := l_object_version_number;
2140 --
2141 if g_debug then
2142 hr_utility.set_location(' Leaving:'||l_proc, 70);
2143 end if;
2144 --
2145 exception
2146 --
2147 when hr_api.validate_enabled then
2148 --
2149 -- As the Validate_Enabled exception has been raised
2150 -- we must rollback to the savepoint
2151 --
2152 ROLLBACK TO update_ELIG_PER_ELC_CHC;
2153 --
2154 -- Only set output warning arguments
2155 -- (Any key or derived arguments must be set to null
2156 -- when validation only mode is being used.)
2157 --
2158 if g_debug then
2159 hr_utility.set_location(' Leaving:'||l_proc, 80);
2160 end if;
2161 --
2162 when others then
2163 --
2164 -- A validation or unexpected error has occured
2165 --
2166 p_object_version_number := l_object_version_number;
2167 if g_debug then
2168 hr_utility.set_location(' Leaving:'||l_proc, 80);
2169 end if;
2170 --
2171 ROLLBACK TO update_ELIG_PER_ELC_CHC;
2172 raise;
2173 --
2174 end update_ELIG_PER_ELC_CHC;
2175 -- ----------------------------------------------------------------------------
2176 -- |------------------------------< chk_valid_mgr >---------------------------|
2177 -- ----------------------------------------------------------------------------
2178 --
2179 procedure chk_valid_mgr(
2180 p_emp_epe_id number,
2181 p_new_mgr_id number,
2182 p_effective_date in date) is
2183
2184 cursor c1 is
2185 select per1.full_name person1,
2186 per2.full_name person2
2187 from ben_cwb_mgr_hrchy cwb1,
2188 ben_elig_per_elctbl_chc epe1,
2192 where cwb1.mgr_elig_per_elctbl_chc_id = p_emp_epe_id
2189 ben_per_in_ler pil1,
2190 per_all_people_f per1,
2191 per_all_people_f per2
2193 and cwb1.lvl_num > 0
2194 and epe1.elig_per_elctbl_chc_id = cwb1.mgr_elig_per_elctbl_chc_id
2195 and pil1.per_in_ler_id = epe1.per_in_ler_id
2196 and per1.person_id = pil1.person_id
2197 and trunc(sysdate) between per1.effective_start_date
2198 and per1.effective_end_date
2199 and per2.person_id = p_new_mgr_id
2200 and trunc(sysdate) between per2.effective_start_date
2201 and per2.effective_end_date
2202 and exists
2203 ( select 'x'
2204 from ben_elig_per_elctbl_chc epe2,
2205 ben_per_in_ler pil2
2206 where pil2.person_id = p_new_mgr_id
2207 and pil2.lf_evt_ocrd_dt = pil1.lf_evt_ocrd_dt
2208 and pil2.ler_id = pil1.ler_id
2209 and epe2.per_in_ler_id = pil2.per_in_ler_id
2210 and epe2.pl_id = epe1.pl_id
2211 and epe2.elig_per_elctbl_chc_id = cwb1.emp_elig_per_elctbl_chc_id);
2212
2213 l_person1 per_all_people_f.full_name%type;
2214 l_person2 per_all_people_f.full_name%type;
2215 l_proc varchar2(72) ;
2216
2217 begin
2218
2219 if g_debug then
2220 l_proc := g_package||'chk_valid_mgr';
2221 hr_utility.set_location(' Entering:'||l_proc, 10);
2222 end if;
2223
2224 open c1;
2225 fetch c1 into l_person1,l_person2;
2226 if c1%found then
2227 close c1;
2228 fnd_message.set_name('BEN','BEN_93251_CWB_CANNOT_REASSIGN');
2229 fnd_message.set_token('PERSON1', l_person1);
2230 fnd_message.set_token('PERSON2', l_person2);
2231 fnd_message.raise_error;
2232 end if;
2233 close c1;
2234
2235 if g_debug then
2236 hr_utility.set_location(' Leaving:'||l_proc, 20);
2237 end if;
2238 end;
2239 -- ----------------------------------------------------------------------------
2240 -- |--------------------< update_perf_ELIG_PER_ELC_CHC >--- ------------------|
2241 -- ----------------------------------------------------------------------------
2242 --
2243 procedure update_perf_ELIG_PER_ELC_CHC
2244 (p_validate in boolean default false
2245 ,p_elig_per_elctbl_chc_id in number
2246 -- ,p_enrt_typ_cycl_cd in varchar2 default hr_api.g_varchar2
2247 ,p_enrt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
2248 --,p_enrt_perd_end_dt in date default hr_api.g_date
2249 --,p_enrt_perd_strt_dt in date default hr_api.g_date
2250 ,p_enrt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
2251 ,p_ctfn_rqd_flag in varchar2 default hr_api.g_varchar2
2252 ,p_pil_elctbl_chc_popl_id in number default hr_api.g_number
2253 ,p_roll_crs_flag in varchar2 default hr_api.g_varchar2
2254 ,p_crntly_enrd_flag in varchar2 default hr_api.g_varchar2
2255 ,p_dflt_flag in varchar2 default hr_api.g_varchar2
2256 ,p_elctbl_flag in varchar2 default hr_api.g_varchar2
2257 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
2258 ,p_in_pndg_wkflow_flag in varchar2 default hr_api.g_varchar2
2259 -- ,p_dflt_enrt_dt in date default hr_api.g_date
2260 ,p_dpnt_cvg_strt_dt_cd in varchar2 default hr_api.g_varchar2
2261 ,p_dpnt_cvg_strt_dt_rl in varchar2 default hr_api.g_varchar2
2262 ,p_enrt_cvg_strt_dt in date default hr_api.g_date
2263 ,p_alws_dpnt_dsgn_flag in varchar2 default hr_api.g_varchar2
2264 ,p_dpnt_dsgn_cd in varchar2 default hr_api.g_varchar2
2265 ,p_ler_chg_dpnt_cvg_cd in varchar2 default hr_api.g_varchar2
2266 ,p_erlst_deenrt_dt in date default hr_api.g_date
2267 ,p_procg_end_dt in date default hr_api.g_date
2268 ,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
2269 ,p_pl_id in number default hr_api.g_number
2270 ,p_oipl_id in number default hr_api.g_number
2271 ,p_pgm_id in number default hr_api.g_number
2272 ,p_plip_id in number default hr_api.g_number
2273 ,p_ptip_id in number default hr_api.g_number
2274 ,p_pl_typ_id in number default hr_api.g_number
2275 ,p_oiplip_id in number default hr_api.g_number
2276 ,p_cmbn_plip_id in number default hr_api.g_number
2277 ,p_cmbn_ptip_id in number default hr_api.g_number
2278 ,p_cmbn_ptip_opt_id in number default hr_api.g_number
2279 ,p_assignment_id in number default hr_api.g_number
2280 ,p_spcl_rt_pl_id in number default hr_api.g_number
2281 ,p_spcl_rt_oipl_id in number default hr_api.g_number
2282 ,p_must_enrl_anthr_pl_id in number default hr_api.g_number
2283 ,p_int_elig_per_elctbl_chc_id in number default hr_api.g_number
2284 ,p_prtt_enrt_rslt_id in number default hr_api.g_number
2285 ,p_bnft_prvdr_pool_id in number default hr_api.g_number
2286 ,p_per_in_ler_id in number default hr_api.g_number
2287 ,p_yr_perd_id in number default hr_api.g_number
2288 ,p_auto_enrt_flag in varchar2 default hr_api.g_varchar2
2289 ,p_business_group_id in number default hr_api.g_number
2290 ,p_pl_ordr_num in number default hr_api.g_number
2291 ,p_plip_ordr_num in number default hr_api.g_number
2292 ,p_ptip_ordr_num in number default hr_api.g_number
2293 ,p_oipl_ordr_num in number default hr_api.g_number
2297 ,p_elig_ovrid_dt in date default hr_api.g_date
2294 -- cwb
2295 ,p_comments in varchar2 default hr_api.g_varchar2
2296 ,p_elig_flag in varchar2 default hr_api.g_varchar2
2298 ,p_elig_ovrid_person_id in number default hr_api.g_number
2299 ,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
2300 ,p_mgr_ovrid_dt in date default hr_api.g_date
2301 ,p_mgr_ovrid_person_id in number default hr_api.g_number
2302 ,p_ws_mgr_id in number default hr_api.g_number
2303 -- cwb
2304 ,p_epe_attribute_category in varchar2 default hr_api.g_varchar2
2305 ,p_epe_attribute1 in varchar2 default hr_api.g_varchar2
2306 ,p_epe_attribute2 in varchar2 default hr_api.g_varchar2
2307 ,p_epe_attribute3 in varchar2 default hr_api.g_varchar2
2308 ,p_epe_attribute4 in varchar2 default hr_api.g_varchar2
2309 ,p_epe_attribute5 in varchar2 default hr_api.g_varchar2
2310 ,p_epe_attribute6 in varchar2 default hr_api.g_varchar2
2311 ,p_epe_attribute7 in varchar2 default hr_api.g_varchar2
2312 ,p_epe_attribute8 in varchar2 default hr_api.g_varchar2
2313 ,p_epe_attribute9 in varchar2 default hr_api.g_varchar2
2314 ,p_epe_attribute10 in varchar2 default hr_api.g_varchar2
2315 ,p_epe_attribute11 in varchar2 default hr_api.g_varchar2
2316 ,p_epe_attribute12 in varchar2 default hr_api.g_varchar2
2317 ,p_epe_attribute13 in varchar2 default hr_api.g_varchar2
2318 ,p_epe_attribute14 in varchar2 default hr_api.g_varchar2
2319 ,p_epe_attribute15 in varchar2 default hr_api.g_varchar2
2320 ,p_epe_attribute16 in varchar2 default hr_api.g_varchar2
2321 ,p_epe_attribute17 in varchar2 default hr_api.g_varchar2
2322 ,p_epe_attribute18 in varchar2 default hr_api.g_varchar2
2323 ,p_epe_attribute19 in varchar2 default hr_api.g_varchar2
2324 ,p_epe_attribute20 in varchar2 default hr_api.g_varchar2
2325 ,p_epe_attribute21 in varchar2 default hr_api.g_varchar2
2326 ,p_epe_attribute22 in varchar2 default hr_api.g_varchar2
2327 ,p_epe_attribute23 in varchar2 default hr_api.g_varchar2
2328 ,p_epe_attribute24 in varchar2 default hr_api.g_varchar2
2329 ,p_epe_attribute25 in varchar2 default hr_api.g_varchar2
2330 ,p_epe_attribute26 in varchar2 default hr_api.g_varchar2
2331 ,p_epe_attribute27 in varchar2 default hr_api.g_varchar2
2332 ,p_epe_attribute28 in varchar2 default hr_api.g_varchar2
2333 ,p_epe_attribute29 in varchar2 default hr_api.g_varchar2
2334 ,p_epe_attribute30 in varchar2 default hr_api.g_varchar2
2335 ,p_approval_status_cd in varchar2 default hr_api.g_varchar2
2336 ,p_fonm_cvg_strt_dt in date default hr_api.g_date
2337 ,p_cryfwd_elig_dpnt_cd in varchar2 default hr_api.g_varchar2
2338 ,p_request_id in number default hr_api.g_number
2339 ,p_program_application_id in number default hr_api.g_number
2340 ,p_program_id in number default hr_api.g_number
2341 ,p_program_update_date in date default hr_api.g_date
2342 ,p_object_version_number in out nocopy number
2343 ,p_effective_date in date
2344 )
2345 is
2346 --
2347 -- Declare cursors and local variables
2348 --
2349 l_proc varchar2(72) ;
2350 l_rec ben_epe_shd.g_rec_type;
2351 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
2352 l_elig_flag varchar2(30);
2353 l_inelig_rsn_cd varchar2(30);
2354 --
2355 begin
2356 --
2357 g_debug := hr_utility.debug_enabled;
2358 if g_debug then
2359 l_proc := g_package||'update_perf_ELIG_PER_ELC_CHC';
2360 hr_utility.set_location('Entering:'|| l_proc, 10);
2361 end if;
2362 --
2363 -- Issue a savepoint if operating in validation only mode
2364 --
2365 savepoint update_perf_ELIG_PER_ELC_CHC;
2366 --
2367 --
2368 -- CWB Changes : Start
2369 --
2370 l_elig_flag := p_elig_flag;
2371 if p_elctbl_flag = 'N' then
2372 --
2373 l_elig_flag := 'N';
2374 --
2375 end if;
2376 --
2377 l_inelig_rsn_cd := p_inelig_rsn_cd;
2378 if l_elig_flag = 'N' and p_inelig_rsn_cd is null then
2379 --
2380 l_inelig_rsn_cd := 'OTH'; -- Other
2381 --
2382 end if;
2383
2384 --
2385 -- CWB Changes : End
2386 --
2387 l_rec :=
2388 ben_epe_shd.convert_args
2389 (
2390 p_elig_per_elctbl_chc_id,
2391 -- p_enrt_typ_cycl_cd,
2392 p_enrt_cvg_strt_dt_cd,
2393 -- p_enrt_perd_end_dt,
2394 -- p_enrt_perd_strt_dt,
2395 p_enrt_cvg_strt_dt_rl,
2396 -- p_rt_strt_dt,
2397 -- p_rt_strt_dt_rl,
2398 -- p_rt_strt_dt_cd,
2399 p_ctfn_rqd_flag,
2400 p_pil_elctbl_chc_popl_id,
2401 p_roll_crs_flag,
2402 p_crntly_enrd_flag,
2403 p_dflt_flag,
2404 p_elctbl_flag,
2405 p_mndtry_flag,
2406 p_in_pndg_wkflow_flag,
2407 -- p_dflt_enrt_dt,
2408 p_dpnt_cvg_strt_dt_cd,
2409 p_dpnt_cvg_strt_dt_rl,
2410 p_enrt_cvg_strt_dt,
2411 p_alws_dpnt_dsgn_flag,
2412 p_dpnt_dsgn_cd,
2413 p_ler_chg_dpnt_cvg_cd,
2414 p_erlst_deenrt_dt,
2415 p_procg_end_dt,
2416 p_comp_lvl_cd,
2417 p_pl_id,
2418 p_oipl_id,
2419 p_pgm_id,
2420 p_plip_id,
2421 p_ptip_id,
2422 p_pl_typ_id,
2423 p_oiplip_id,
2424 p_cmbn_plip_id,
2428 p_spcl_rt_pl_id,
2425 p_cmbn_ptip_id,
2426 p_cmbn_ptip_opt_id,
2427 p_assignment_id,
2429 p_spcl_rt_oipl_id,
2430 p_must_enrl_anthr_pl_id,
2431 p_int_elig_per_elctbl_chc_id,
2432 p_prtt_enrt_rslt_id,
2433 p_bnft_prvdr_pool_id,
2434 p_per_in_ler_id,
2435 p_yr_perd_id,
2436 p_auto_enrt_flag,
2437 p_business_group_id,
2438 p_pl_ordr_num,
2439 p_plip_ordr_num,
2440 p_ptip_ordr_num,
2441 p_oipl_ordr_num,
2442 -- cwb
2443 p_comments,
2444 l_elig_flag,
2445 p_elig_ovrid_dt,
2446 p_elig_ovrid_person_id,
2447 l_inelig_rsn_cd,
2448 p_mgr_ovrid_dt,
2449 p_mgr_ovrid_person_id,
2450 p_ws_mgr_id,
2451 -- cwb
2452 p_epe_attribute_category,
2453 p_epe_attribute1,
2454 p_epe_attribute2,
2455 p_epe_attribute3,
2456 p_epe_attribute4,
2457 p_epe_attribute5,
2458 p_epe_attribute6,
2459 p_epe_attribute7,
2460 p_epe_attribute8,
2461 p_epe_attribute9,
2462 p_epe_attribute10,
2463 p_epe_attribute11,
2464 p_epe_attribute12,
2465 p_epe_attribute13,
2466 p_epe_attribute14,
2467 p_epe_attribute15,
2468 p_epe_attribute16,
2469 p_epe_attribute17,
2470 p_epe_attribute18,
2471 p_epe_attribute19,
2472 p_epe_attribute20,
2473 p_epe_attribute21,
2474 p_epe_attribute22,
2475 p_epe_attribute23,
2476 p_epe_attribute24,
2477 p_epe_attribute25,
2478 p_epe_attribute26,
2479 p_epe_attribute27,
2480 p_epe_attribute28,
2481 p_epe_attribute29,
2482 p_epe_attribute30,
2483 p_approval_status_cd,
2484 p_fonm_cvg_strt_dt,
2485 p_cryfwd_elig_dpnt_cd,
2486 p_request_id,
2487 p_program_application_id,
2488 p_program_id,
2489 p_program_update_date,
2490 null
2491 );
2492
2493 --
2494 -- We must lock the row which we need to update.
2495 --
2496 ben_epe_shd.lck
2497 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2501 ben_epe_upd.convert_defs(p_rec => l_rec);
2498 ,p_object_version_number => p_object_version_number
2499 );
2500 --
2502 --
2503 --CWB
2504 -- Check only if the new manager is not null or defaulted.
2505 --
2506 if (nvl(p_ws_mgr_id, hr_api.g_number) <> hr_api.g_number) and
2507 (nvl(ben_epe_shd.g_old_rec.ws_mgr_id,-1) <> nvl(p_ws_mgr_id,-1))
2508 then
2509 chk_valid_mgr(p_elig_per_elctbl_chc_id,p_ws_mgr_id,p_effective_date);
2510 end if;
2511 --CWB
2512 --
2513 -- Increment object version number
2514 --
2515 l_object_version_number := p_object_version_number+1;
2516 --
2517 if g_debug then
2518 hr_utility.set_location(' EPE Upd'|| l_proc, 10);
2519 end if;
2520 ben_epe_shd.g_api_dml := true; -- Set the api dml status
2521 --
2522 -- Update the ben_elig_per_elctbl_chc Row
2523 --
2524
2525 update ben_elig_per_elctbl_chc
2526 set
2527 elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id,
2528 -- enrt_typ_cycl_cd = l_rec.enrt_typ_cycl_cd,
2529 enrt_cvg_strt_dt_cd = l_rec.enrt_cvg_strt_dt_cd,
2530 -- enrt_perd_end_dt = l_rec.enrt_perd_end_dt,
2531 -- enrt_perd_strt_dt = l_rec.enrt_perd_strt_dt,
2532 enrt_cvg_strt_dt_rl = l_rec.enrt_cvg_strt_dt_rl,
2533 -- rt_strt_dt = l_rec.rt_strt_dt,
2534 -- rt_strt_dt_rl = l_rec.rt_strt_dt_rl,
2535 -- rt_strt_dt_cd = l_rec.rt_strt_dt_cd,
2536 ctfn_rqd_flag = l_rec.ctfn_rqd_flag,
2537 pil_elctbl_chc_popl_id = l_rec.pil_elctbl_chc_popl_id,
2541 elctbl_flag = l_rec.elctbl_flag,
2538 roll_crs_flag = l_rec.roll_crs_flag,
2539 crntly_enrd_flag = l_rec.crntly_enrd_flag,
2540 dflt_flag = l_rec.dflt_flag,
2542 mndtry_flag = l_rec.mndtry_flag,
2543 in_pndg_wkflow_flag = l_rec.in_pndg_wkflow_flag,
2544 -- dflt_enrt_dt = l_rec.dflt_enrt_dt,
2545 dpnt_cvg_strt_dt_cd = l_rec.dpnt_cvg_strt_dt_cd,
2546 dpnt_cvg_strt_dt_rl = l_rec.dpnt_cvg_strt_dt_rl,
2547 enrt_cvg_strt_dt = l_rec.enrt_cvg_strt_dt,
2548 alws_dpnt_dsgn_flag = l_rec.alws_dpnt_dsgn_flag,
2549 dpnt_dsgn_cd = l_rec.dpnt_dsgn_cd,
2550 ler_chg_dpnt_cvg_cd = l_rec.ler_chg_dpnt_cvg_cd,
2551 erlst_deenrt_dt = l_rec.erlst_deenrt_dt,
2552 procg_end_dt = l_rec.procg_end_dt,
2553 comp_lvl_cd = l_rec.comp_lvl_cd,
2554 pl_id = l_rec.pl_id,
2555 oipl_id = l_rec.oipl_id,
2556 pgm_id = l_rec.pgm_id,
2557 plip_id = l_rec.plip_id,
2558 ptip_id = l_rec.ptip_id,
2559 pl_typ_id = l_rec.pl_typ_id,
2560 oiplip_id = l_rec.oiplip_id,
2561 cmbn_plip_id = l_rec.cmbn_plip_id,
2562 cmbn_ptip_id = l_rec.cmbn_ptip_id,
2563 cmbn_ptip_opt_id = l_rec.cmbn_ptip_opt_id,
2564 spcl_rt_pl_id = l_rec.spcl_rt_pl_id,
2565 spcl_rt_oipl_id = l_rec.spcl_rt_oipl_id,
2566 must_enrl_anthr_pl_id = l_rec.must_enrl_anthr_pl_id,
2567 interim_elig_per_elctbl_chc_id = l_rec.int_elig_per_elctbl_chc_id,
2568 prtt_enrt_rslt_id = l_rec.prtt_enrt_rslt_id,
2569 bnft_prvdr_pool_id = l_rec.bnft_prvdr_pool_id,
2570 per_in_ler_id = l_rec.per_in_ler_id,
2571 yr_perd_id = l_rec.yr_perd_id,
2572 auto_enrt_flag = l_rec.auto_enrt_flag,
2573 business_group_id = l_rec.business_group_id,
2574 pl_ordr_num = l_rec.pl_ordr_num,
2575 plip_ordr_num = l_rec.plip_ordr_num,
2576 ptip_ordr_num = l_rec.ptip_ordr_num,
2577 oipl_ordr_num = l_rec.oipl_ordr_num,
2578 -- cwb
2579 comments = l_rec.comments,
2580 elig_flag = l_rec.elig_flag,
2581 elig_ovrid_dt = l_rec.elig_ovrid_dt,
2582 elig_ovrid_person_id = l_rec.elig_ovrid_person_id,
2583 inelig_rsn_cd = l_rec.inelig_rsn_cd,
2584 mgr_ovrid_dt = l_rec.mgr_ovrid_dt,
2585 mgr_ovrid_person_id = l_rec.mgr_ovrid_person_id,
2586 ws_mgr_id = l_rec.ws_mgr_id,
2587 -- cwb
2588 epe_attribute_category = l_rec.epe_attribute_category,
2589 epe_attribute1 = l_rec.epe_attribute1,
2590 epe_attribute2 = l_rec.epe_attribute2,
2591 epe_attribute3 = l_rec.epe_attribute3,
2592 epe_attribute4 = l_rec.epe_attribute4,
2593 epe_attribute5 = l_rec.epe_attribute5,
2594 epe_attribute6 = l_rec.epe_attribute6,
2595 epe_attribute7 = l_rec.epe_attribute7,
2596 epe_attribute8 = l_rec.epe_attribute8,
2597 epe_attribute9 = l_rec.epe_attribute9,
2598 epe_attribute10 = l_rec.epe_attribute10,
2599 epe_attribute11 = l_rec.epe_attribute11,
2600 epe_attribute12 = l_rec.epe_attribute12,
2601 epe_attribute13 = l_rec.epe_attribute13,
2602 epe_attribute14 = l_rec.epe_attribute14,
2603 epe_attribute15 = l_rec.epe_attribute15,
2604 epe_attribute16 = l_rec.epe_attribute16,
2605 epe_attribute17 = l_rec.epe_attribute17,
2606 epe_attribute18 = l_rec.epe_attribute18,
2607 epe_attribute19 = l_rec.epe_attribute19,
2608 epe_attribute20 = l_rec.epe_attribute20,
2609 epe_attribute21 = l_rec.epe_attribute21,
2610 epe_attribute22 = l_rec.epe_attribute22,
2611 epe_attribute23 = l_rec.epe_attribute23,
2612 epe_attribute24 = l_rec.epe_attribute24,
2613 epe_attribute25 = l_rec.epe_attribute25,
2614 epe_attribute26 = l_rec.epe_attribute26,
2615 epe_attribute27 = l_rec.epe_attribute27,
2616 epe_attribute28 = l_rec.epe_attribute28,
2617 epe_attribute29 = l_rec.epe_attribute29,
2618 epe_attribute30 = l_rec.epe_attribute30,
2619 approval_status_cd = l_rec.approval_status_cd,
2620 fonm_cvg_strt_dt = l_rec.fonm_cvg_strt_dt,
2621 cryfwd_elig_dpnt_cd = l_rec.cryfwd_elig_dpnt_cd,
2622 request_id = l_rec.request_id,
2626 object_version_number = l_object_version_number
2623 program_application_id = l_rec.program_application_id,
2624 program_id = l_rec.program_id,
2625 program_update_date = l_rec.program_update_date,
2627 where elig_per_elctbl_chc_id = l_rec.elig_per_elctbl_chc_id;
2628 --
2629 ben_epe_shd.g_api_dml := false; -- Unset the api dml status
2630 --
2631 if g_debug then
2632 hr_utility.set_location(' Dn EPE Upd'|| l_proc, 10);
2633 end if;
2634 --
2635 -- When in validation only mode raise the Validate_Enabled exception
2636 --
2637 if p_validate then
2638 raise hr_api.validate_enabled;
2639 end if;
2640 --
2641 -- Set EPE comp object context values
2642 --
2643 if ben_epe_cache.g_currcobjepe_row.elig_per_elctbl_chc_id is not null then
2644 --
2645 -- BENDEPEN
2646 --
2647 ben_epe_cache.g_currcobjepe_row.alws_dpnt_dsgn_flag := l_rec.alws_dpnt_dsgn_flag;
2648 ben_epe_cache.g_currcobjepe_row.object_version_number := l_object_version_number;
2649 ben_epe_cache.g_currcobjepe_row.dpnt_dsgn_cd := l_rec.dpnt_dsgn_cd;
2650 ben_epe_cache.g_currcobjepe_row.ler_chg_dpnt_cvg_cd := l_rec.ler_chg_dpnt_cvg_cd;
2651 ben_epe_cache.g_currcobjepe_row.dpnt_cvg_strt_dt_cd := l_rec.dpnt_cvg_strt_dt_cd;
2652 ben_epe_cache.g_currcobjepe_row.dpnt_cvg_strt_dt_rl := l_rec.dpnt_cvg_strt_dt_rl;
2653 --
2654 -- BENCHCTF
2655 --
2656 ben_epe_cache.g_currcobjepe_row.ctfn_rqd_flag := l_rec.ctfn_rqd_flag;
2657 --
2658 -- BENCVRGE
2659 --
2660 ben_epe_cache.g_currcobjepe_row.elctbl_flag := l_rec.elctbl_flag;
2661 ben_epe_cache.g_currcobjepe_row.dflt_flag := l_rec.dflt_flag;
2662 --
2663 end if;
2664 --
2665 -- Set all output arguments
2666 --
2667 p_object_version_number := l_object_version_number;
2668 --
2669 if g_debug then
2670 hr_utility.set_location(' Leaving:'||l_proc, 70);
2671 end if;
2672 --
2673 exception
2674 --
2675 when hr_api.validate_enabled then
2676 --
2677 -- As the Validate_Enabled exception has been raised
2678 -- we must rollback to the savepoint
2679 --
2680 ROLLBACK TO update_perf_ELIG_PER_ELC_CHC;
2681 --
2682 -- Only set output warning arguments
2683 -- (Any key or derived arguments must be set to null
2684 -- when validation only mode is being used.)
2685 --
2686 if g_debug then
2687 hr_utility.set_location(' Leaving:'||l_proc, 80);
2688 end if;
2689 --
2690 when others then
2691 --
2692 -- A validation or unexpected error has occured
2693 --
2694 p_object_version_number := l_object_version_number;
2695 if g_debug then
2696 hr_utility.set_location(' Leaving:'||l_proc, 80);
2697 end if;
2698 --
2699 ROLLBACK TO update_perf_ELIG_PER_ELC_CHC;
2700 raise;
2701 --
2702 end update_perf_ELIG_PER_ELC_CHC;
2703 -- ----------------------------------------------------------------------------
2704 -- |------------------------< delete_ELIG_PER_ELC_CHC >----------------------|
2705 -- ----------------------------------------------------------------------------
2706 --
2707 procedure delete_ELIG_PER_ELC_CHC
2708 (p_validate in boolean default false
2709 ,p_elig_per_elctbl_chc_id in number
2710 ,p_object_version_number in out nocopy number
2711 ,p_effective_date in date
2712 ) is
2713 --
2717 l_object_version_number ben_elig_per_elctbl_chc.object_version_number%TYPE;
2714 -- Declare cursors and local variables
2715 --
2716 l_proc varchar2(72) ;
2718
2719 cursor c_ctfn is
2720 /* select ecc.elctbl_chc_ctfn_id,
2721 ecc.object_version_number
2722 from ben_elctbl_chc_ctfn ecc
2723 where elig_per_elctbl_chc_id in
2724 (select elig_per_elctbl_chc_id
2725 from ben_elig_per_elctbl_chc
2726 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
2727 or enrt_bnft_id in
2728 (select enrt_bnft_id
2729 from ben_enrt_bnft
2730 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);*/
2731
2732 select ecc.elctbl_chc_ctfn_id,
2733 ecc.object_version_number
2734 from ben_elctbl_chc_ctfn ecc
2735 where elig_per_elctbl_chc_id in
2736 (select elig_per_elctbl_chc_id
2737 from ben_elig_per_elctbl_chc
2738 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
2739 UNION
2740 select ecc.elctbl_chc_ctfn_id,
2741 ecc.object_version_number
2742 from ben_elctbl_chc_ctfn ecc
2743 where enrt_bnft_id in
2744 (select enrt_bnft_id
2745 from ben_enrt_bnft
2746 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
2747
2748
2749 cursor c_rate is
2750 /* select ecr.enrt_rt_id,
2751 ecr.object_version_number
2752 from ben_enrt_rt ecr
2753 where elig_per_elctbl_chc_id in
2754 (select elig_per_elctbl_chc_id
2755 from ben_elig_per_elctbl_chc
2756 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
2757 or enrt_bnft_id in
2758 (select enrt_bnft_id
2759 from ben_enrt_bnft
2760 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
2761 */
2762
2763 select ecr.enrt_rt_id,
2764 ecr.object_version_number
2765 from ben_enrt_rt ecr
2766 where elig_per_elctbl_chc_id in
2767 (select elig_per_elctbl_chc_id
2768 from ben_elig_per_elctbl_chc
2769 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id)
2770 UNION all
2771 select ecr.enrt_rt_id,
2772 ecr.object_version_number
2773 from ben_enrt_rt ecr
2774 where enrt_bnft_id in
2775 (select enrt_bnft_id
2776 from ben_enrt_bnft
2777 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
2778
2779
2780 cursor c_bnft is
2781 select enb.enrt_bnft_id,
2782 enb.object_version_number
2783 from ben_enrt_bnft enb
2784 where elig_per_elctbl_chc_id in
2785 (select elig_per_elctbl_chc_id
2786 from ben_elig_per_elctbl_chc
2787 where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id);
2788
2789 --
2790 begin
2791 --
2792 g_debug := hr_utility.debug_enabled;
2793 if g_debug then
2794 l_proc := g_package||'update_ELIG_PER_ELC_CHC';
2795 hr_utility.set_location('Entering:'|| l_proc, 10);
2796 end if;
2797 --
2798 -- Issue a savepoint if operating in validation only mode
2799 --
2800 savepoint delete_ELIG_PER_ELC_CHC;
2801 --
2802 if g_debug then
2803 hr_utility.set_location(l_proc, 20);
2804 end if;
2805 --
2806 -- Process Logic
2807 --
2808 l_object_version_number := p_object_version_number;
2809 --
2810 --
2811 begin
2812 --
2813 -- Start of API User Hook for the before hook of delete_ELIG_PER_ELC_CHC
2814 --
2815 ben_ELIG_PER_ELC_CHC_bk3.delete_ELIG_PER_ELC_CHC_b
2816 (
2817 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2818 ,p_object_version_number => p_object_version_number
2819 ,p_effective_date => trunc(p_effective_date)
2820 );
2821 exception
2822 when hr_api.cannot_find_prog_unit then
2823 hr_api.cannot_find_prog_unit_error
2824 (p_module_name => 'DELETE_ELIG_PER_ELC_CHC'
2825 ,p_hook_type => 'BP'
2826 );
2827 --
2828 -- End of API User Hook for the before hook of delete_ELIG_PER_ELC_CHC
2829 --
2830 end;
2831 --
2832 -- Delete all other related choice info:
2833
2834 -- Choice Certification:
2835 for l_ctfn in c_ctfn loop
2836 null; -- api does not exist yet!
2837 -- ben_elctbl_chc_ctfn_api.delete_elctbl_chc_ctfn
2838 -- (p_validate => false,
2839 -- p_enrt_cvg_n_rt_ctfn_id => l_ctfn.enrt_cvg_n_rt_ctfn_id,
2840 -- p_object_version_number => l_ctfn.object_version_number,
2841 -- p_effective_date => p_effective_date);
2842 end loop;
2843
2844 -- Choice Rates:
2845 for l_rate in c_rate loop
2846 ben_enrollment_rate_api.delete_enrollment_rate
2847 (p_validate => false,
2848 p_enrt_rt_id => l_rate.enrt_rt_id,
2849 p_object_version_number => l_rate.object_version_number,
2850 p_effective_date => p_effective_date);
2851 end loop;
2852
2853 -- Choice Benefits:
2854 for l_bnft in c_bnft loop
2855 ben_enrt_bnft_api.delete_enrt_bnft
2856 (p_validate => false,
2857 p_enrt_bnft_id => l_bnft.enrt_bnft_id,
2858 p_object_version_number => l_bnft.object_version_number,
2859 p_effective_date => p_effective_date);
2860 end loop;
2861
2862 -- Delete the choice:
2863 --
2864 ben_epe_del.del
2865 (
2866 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2870 --
2867 ,p_object_version_number => l_object_version_number
2868 ,p_effective_date => p_effective_date
2869 );
2871 begin
2872 --
2873 -- Start of API User Hook for the after hook of delete_ELIG_PER_ELC_CHC
2874 --
2875 ben_ELIG_PER_ELC_CHC_bk3.delete_ELIG_PER_ELC_CHC_a
2876 (
2877 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2878 ,p_object_version_number => l_object_version_number
2879 ,p_effective_date => trunc(p_effective_date)
2880 );
2881 exception
2882 when hr_api.cannot_find_prog_unit then
2883 hr_api.cannot_find_prog_unit_error
2884 (p_module_name => 'DELETE_ELIG_PER_ELC_CHC'
2885 ,p_hook_type => 'AP'
2886 );
2887 --
2888 -- End of API User Hook for the after hook of delete_ELIG_PER_ELC_CHC
2889 --
2890 end;
2891 --
2892 if g_debug then
2893 hr_utility.set_location(l_proc, 60);
2894 end if;
2895 --
2896 -- When in validation only mode raise the Validate_Enabled exception
2897 --
2898 if p_validate then
2899 raise hr_api.validate_enabled;
2900 end if;
2901 --
2902 if g_debug then
2903 hr_utility.set_location(' Leaving:'||l_proc, 70);
2904 end if;
2905 --
2906 exception
2907 --
2908 when hr_api.validate_enabled then
2909 --
2910 -- As the Validate_Enabled exception has been raised
2911 -- we must rollback to the savepoint
2912 --
2913 ROLLBACK TO delete_ELIG_PER_ELC_CHC;
2914 --
2915 -- Only set output warning arguments
2916 -- (Any key or derived arguments must be set to null
2917 -- when validation only mode is being used.)
2918 --
2919 --
2920 when others then
2921 --
2922 -- A validation or unexpected error has occured
2923 --
2924 p_object_version_number := l_object_version_number;
2925 if g_debug then
2926 hr_utility.set_location(' Leaving:'||l_proc, 80);
2927 end if;
2928 --
2929 ROLLBACK TO delete_ELIG_PER_ELC_CHC;
2930 raise;
2931 --
2932 end delete_ELIG_PER_ELC_CHC;
2933 --
2934 -- ----------------------------------------------------------------------------
2935 -- |-------------------------------< lck >------------------------------------|
2936 -- ----------------------------------------------------------------------------
2937 --
2938 procedure lck
2939 (
2940 p_elig_per_elctbl_chc_id in number
2941 ,p_object_version_number in number
2942 ) is
2943 --
2944 --
2945 -- Declare cursors and local variables
2946 --
2947 l_proc varchar2(72) ;
2948 --
2949 begin
2950 --
2951 g_debug := hr_utility.debug_enabled;
2952 if g_debug then
2953 l_proc := g_package||'lck';
2954 hr_utility.set_location('Entering:'|| l_proc, 10);
2955 end if;
2956 --
2957 ben_epe_shd.lck
2958 (
2959 p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
2960 ,p_object_version_number => p_object_version_number
2961 );
2962 --
2963 if g_debug then
2964 hr_utility.set_location(' Leaving:'||l_proc, 70);
2965 end if;
2966 --
2967 end lck;
2968 --
2969 end ben_elig_per_elc_chc_api;