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