[Home] [Help]
PACKAGE BODY: APPS.BEN_ELIGIBILITY_OVERRIDE
Source
1 Package Body ben_eligibility_override as
2 /* $Header: benovrel.pkb 120.1.12000000.2 2007/08/28 15:38:43 rtagarra noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_eligibility_override.'; -- Global package name
9
10 g_lvl_rec g_rec;
11 --
12 -- restart the counter variable
13 procedure reset_counter_rec is
14 begin
15 g_lvl_rec.delete;
16 end;
17 --
18 function format_mesg
19 return varchar2 is
20 i int;
21 l_mesg varchar2(2000);
22 l_blank varchar2(5) := rpad(' ',5,' ');
23 l_indent varchar2(1) := fnd_global.newline;
24 begin
25 for i in 1..g_lvl_rec.count
26 loop
27 l_mesg := l_mesg||rpad(substr(g_lvl_rec(i).meaning,1,20),20,' ')||l_indent;
28 end loop;
29 return l_mesg;
30 end;
31 --
32 procedure update_count(p_lvl_name in varchar2) is
33
34 i int;
35 l_meaning hr_lookups.meaning%type;
36
37 cursor hr_lookup is
38 select hl.meaning
39 from hr_lookups hl
40 where hl.lookup_type = 'BEN_COMP_LVL'
41 and hl.lookup_code = p_lvl_name;
42 begin
43 for i in 1..g_lvl_rec.count
44 loop
45 if g_lvl_rec(i).lvl_name = p_lvl_name then
46 g_lvl_rec(i).counter := nvl(g_lvl_rec(i).counter,0) + 1;
47 return;
48 end if;
49 end loop;
50
51 open hr_lookup;
52 fetch hr_lookup into l_meaning;
53 close hr_lookup;
54
55 g_lvl_rec(nvl(g_lvl_rec.count,0) +1).lvl_name := p_lvl_name;
56 g_lvl_rec(g_lvl_rec.count).meaning := nvl(l_meaning,p_lvl_name);
57 g_lvl_rec(g_lvl_rec.count).counter := 1;
58 end;
59 --
60 function get_level
61 (p_pgm_id in number,
62 p_ptip_id in number,
63 p_plip_id in number,
64 p_pl_id in number,
65 p_oipl_id in number) return varchar2 is
66
67 l_lvl varchar2(10) := 'UNKNOWN';
68 begin
69 if p_pgm_id is not null then
70 if p_ptip_id is not null then
71 l_lvl := 'PTIP';
72 elsif p_plip_id is not null then
73 l_lvl := 'PLIP';
74 elsif p_pl_id is not null then
75 l_lvl := 'PLAN';
76 elsif p_oipl_id is not null then
77 l_lvl := 'OIPL';
78 else
79 l_lvl := 'PGM';
80 end if;
81 elsif p_pl_id is not null then
82 -- l_lvl := 'PLNIP';
83 l_lvl := 'PNIP'; -- Bug 6370016
84 end if;
85 return l_lvl;
86 end;
87
88 function chk_if_enrolled
89 (p_pgm_id in number,
90 p_pl_id in number,
91 p_oipl_id in number,
92 p_business_group_id in number,
93 p_person_id in number,
94 p_effective_date in date)
95 return varchar2 is
96
97 l_dummy varchar2(1);
98
99 cursor c_elc is
100 select 'Y'
101 from ben_prtt_enrt_rslt_f prtt
102 where prtt.business_group_id = p_business_group_id
103 and prtt.person_id = p_person_id
104 and p_effective_date between prtt.effective_start_date
105 and prtt.effective_end_date
106 and nvl(prtt.pgm_id,-1) = nvl(p_pgm_id,-1)
107 and prtt.enrt_cvg_thru_dt = hr_api.g_eot
108 and prtt.prtt_enrt_rslt_stat_cd is null
109 and ( (p_oipl_id is not null and prtt.oipl_id = p_oipl_id)
110 or (p_pl_id is not null and prtt.pl_id = p_pl_id));
111 begin
112
113 open c_elc;
114 fetch c_elc into l_dummy;
115 close c_elc;
116
117 return(nvl(l_dummy,'N'));
118
119 end;
120
121 --
122 -- Updates the elig_flag on a single elig_per_opt record
123 procedure upd_elig_per_opt_flag
124 (p_elig_per_rec in ben_elig_per_f%rowtype,
125 p_business_group_id in number,
126 p_elig_flag in varchar2,
127 p_effective_date in date,
128 p_dt_mode in varchar2,
129 p_cobj_lvl in varchar2) is
130
131 cursor c_epo is
132 select oipl.oipl_id,epo.*
133 from ben_elig_per_opt_f epo,
134 ben_elig_per_f pep,
135 ben_oipl_f oipl
136 where epo.elig_per_id = p_elig_per_rec.elig_per_id
137 and pep.elig_per_id = epo.elig_per_id
138 and oipl.pl_id = pep.pl_id
139 and oipl.opt_id = epo.opt_id
140 and pep.business_group_id = p_business_group_id
141 and epo.business_group_id = p_business_group_id
142 and oipl.business_group_id = p_business_group_id
143 and p_effective_date between pep.effective_start_date
144 and pep.effective_end_date
145 and p_effective_date between epo.effective_start_date
146 and epo.effective_end_date
147 and p_effective_date between oipl.effective_start_date
148 and oipl.effective_end_date;
149
150 l_elig_per_opt_rec c_epo%rowtype;
151 l_ovn number;
152 l_effective_start_date date;
153 l_effective_end_date date;
154
155 begin
156 open c_epo;
157 loop
158 fetch c_epo into l_elig_per_opt_rec;
159 if c_epo%notfound then
160 exit;
161 end if;
162
163 if (chk_if_enrolled
164 (p_pgm_id => p_elig_per_rec.pgm_id,
165 p_pl_id => null,
166 p_oipl_id => l_elig_per_opt_rec.oipl_id,
167 p_business_group_id => p_business_group_id,
168 p_person_id => p_elig_per_rec.person_id,
169 p_effective_date => p_effective_date) = 'N') then
170
171 l_ovn := l_elig_per_opt_rec.object_version_number;
172
173 BEN_elig_person_option_API.update_elig_person_option
174 (p_validate => FALSE
175 ,p_elig_per_opt_id => l_elig_per_opt_rec.elig_per_opt_id
176 ,p_elig_per_id => l_elig_per_opt_rec.elig_per_id
177 ,p_business_group_id => p_business_group_id
178 ,p_elig_flag => p_elig_flag
179 ,p_prtn_ovridn_flag => 'Y'
180 ,p_effective_start_date => l_effective_start_date
181 ,p_effective_end_date => l_effective_end_date
182 ,p_object_version_number => l_ovn
183 ,p_effective_date => p_effective_date
184 ,p_datetrack_mode => p_dt_mode
185 );
186 update_count(p_cobj_lvl);
187
188 end if;
189 end loop;
190 close c_epo;
191
192 end upd_elig_per_opt_flag;
193
194 --
195 -- Updates the elig_flag on a single elig_per record
196 procedure upd_elig_per_flag
197 (p_elig_rec in ben_elig_per_f%rowtype,
198 p_business_group_id in number,
199 p_elig_flag in varchar2,
200 p_effective_date in date,
201 p_dt_mode in varchar2,
202 p_cobj_lvl in varchar2) is
203
204 l_ovn number := p_elig_rec.object_version_number;
205 l_effective_start_date date;
206 l_effective_end_date date;
207
208 begin
209 if (chk_if_enrolled
210 (p_pgm_id => p_elig_rec.pgm_id,
211 p_pl_id => p_elig_rec.pl_id,
212 p_oipl_id => null,
213 p_business_group_id => p_business_group_id,
214 p_person_id => p_elig_rec.person_id,
215 p_effective_date => p_effective_date) = 'N') then
216
217 BEN_eligible_person_API.update_eligible_person
218 (p_validate => FALSE
219 ,p_elig_per_id => p_elig_rec.elig_per_id
220 ,p_business_group_id => p_business_group_id
221 ,p_elig_flag => p_elig_flag
222 ,p_prtn_ovridn_flag => 'Y'
223 ,p_effective_start_date => l_effective_start_date
224 ,p_effective_end_date => l_effective_end_date
225 ,p_object_version_number => l_ovn
226 ,p_effective_date => p_effective_date
227 ,p_datetrack_mode => p_dt_mode
228 );
229
230 update_count(p_cobj_lvl);
231 end if;
232
233 end upd_elig_per_flag;
234
235 --
236 -- if p_elig_flag = Y then marks all the records UP in
237 -- the hierarchy as eligible
238 -- if p_elig_flag = N then marks all the records DOWN in
239 -- the hierarchy as ineligible
240 --
241 procedure update_elig_hierarchy
242 (p_person_id in number,
243 p_pgm_id in number,
244 p_ptip_id in number,
245 p_plip_id in number,
246 p_pl_id in number,
247 p_elig_per_id in number,
248 p_elig_flag in varchar2,
249 p_dt_mode in varchar2,
250 p_business_group_id in number,
251 p_per_in_ler_id in number,
252 p_effective_date in date,
253 p_out_mesg out nocopy varchar2) is
254
255
256 l_cobj_lvl varchar2(30) ;
257 l_up boolean := false;
258
259 cursor c_elig is
260 select pep.*
261 from ben_elig_per_f pep
262 where pep.person_id = p_person_id
263 and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
264 and p_effective_date between pep.effective_start_date and
265 pep.effective_end_date
266 and pep.business_group_id = p_business_group_id
267 and ( (p_elig_per_id is not null and
268 pep.elig_per_id = p_elig_per_id
269 ) or
270 (p_pl_id is not null and pep.plip_id is not null and exists
271 (select null
272 from ben_plip_f plip
273 where plip.plip_id = pep.plip_id
274 and plip.pl_id = p_pl_id
275 and plip.pgm_id = p_pgm_id
276 and p_effective_date between plip.effective_start_date and
277 plip.effective_end_date
278 and plip.business_group_id = p_business_group_id)
279 ) or
280 (p_plip_id is not null and pep.ptip_id is not null and exists
281 (select null
282 from ben_ptip_f ptip,
283 ben_plip_f plip,
284 ben_pl_f pln
285 where ptip.ptip_id = pep.ptip_id
286 and ptip.pgm_id = p_pgm_id
287 and plip.plip_id = p_plip_id
288 and pln.pl_id = plip.pl_id
289 and ptip.pl_typ_id = pln.pl_typ_id
290 and p_effective_date between ptip.effective_start_date and
291 ptip.effective_end_date
292 and ptip.business_group_id = p_business_group_id
293 and p_effective_date between plip.effective_start_date and
294 plip.effective_end_date
295 and plip.business_group_id = p_business_group_id
296 and p_effective_date between pln.effective_start_date and
297 pln.effective_end_date
298 and pln.business_group_id = p_business_group_id)
299 ) or
300 (p_ptip_id is not null and pep.ptip_id is null and
301 pep.plip_id is null and pep.pl_id is null)
302 );
303 l_elig_rec c_elig%rowtype;
304
305 cursor c_inelig is
306 select pep.*
307 from ben_elig_per_f pep
308 where pep.person_id = p_person_id
309 and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
310 and p_effective_date between pep.effective_start_date and
311 pep.effective_end_date
312 and pep.business_group_id = p_business_group_id
313 and ( (p_pgm_id is not null and p_ptip_id is null and
314 p_plip_id is null and p_pl_id is null and
315 pep.ptip_id is not null and exists
316 (select null
317 from ben_ptip_f ptip
318 where ptip.ptip_id = pep.ptip_id
319 and ptip.pgm_id = p_pgm_id
320 and p_effective_date between ptip.effective_start_date and
321 ptip.effective_end_date
322 and ptip.business_group_id = p_business_group_id)
323 ) or
324 (p_ptip_id is not null and pep.plip_id is not null and exists
325 (select null
326 from ben_ptip_f ptip,
327 ben_plip_f plip,
328 ben_pl_f pln
329 where ptip.ptip_id = p_ptip_id
330 and ptip.pgm_id = p_pgm_id
331 and ptip.pl_typ_id = pln.pl_typ_id
332 and plip.pl_id = pln.pl_id
333 and plip.plip_id = pep.plip_id
334 and plip.pgm_id = ptip.pgm_id
335 and p_effective_date between ptip.effective_start_date and
336 ptip.effective_end_date
337 and ptip.business_group_id = p_business_group_id
338 and p_effective_date between plip.effective_start_date and
339 plip.effective_end_date
340 and plip.business_group_id = p_business_group_id
341 and p_effective_date between pln.effective_start_date and
342 pln.effective_end_date
343 and pln.business_group_id = p_business_group_id)
344 ) or
345 (p_plip_id is not null and pep.pl_id is not null and exists
346 (select null
347 from ben_plip_f plip
348 where plip.plip_id = p_plip_id
349 and plip.pgm_id = p_pgm_id
350 and plip.pl_id = pep.pl_id
351 and p_effective_date between plip.effective_start_date and
352 plip.effective_end_date
353 and plip.business_group_id = p_business_group_id)
354 ) or
355 (p_pl_id is not null and pep.pl_id = p_pl_id and exists
356 (select null
357 from ben_elig_per_opt_f epo
358 where epo.elig_per_id = pep.elig_per_id
359 and p_effective_date between epo.effective_start_date and
360 epo.effective_end_date
361 and epo.business_group_id = p_business_group_id))
362 );
363
364 begin
365
366 if p_elig_flag = 'Y' then
367
368 open c_elig;
369 loop
370 fetch c_elig into l_elig_rec;
371 if c_elig%notfound then
372 exit;
373 end if;
374
375 l_cobj_lvl := get_level
376 (p_pgm_id => l_elig_rec.pgm_id,
377 p_ptip_id => l_elig_rec.ptip_id,
378 p_plip_id => l_elig_rec.plip_id,
379 p_pl_id => l_elig_rec.pl_id,
380 p_oipl_id => null);
381
382 if nvl(l_elig_rec.elig_flag,'N') <> p_elig_flag then
383 upd_elig_per_flag
384 (l_elig_rec,
385 p_business_group_id,
386 p_elig_flag,
387 p_effective_date,
388 p_dt_mode,
389 l_cobj_lvl);
390 end if;
391
392 if l_cobj_lvl <> 'PGM' then
393 ben_eligibility_override.update_elig_hierarchy
394 (p_person_id => p_person_id,
395 p_pgm_id => p_pgm_id,
396 p_ptip_id => l_elig_rec.ptip_id,
397 p_plip_id => l_elig_rec.plip_id,
398 p_pl_id => l_elig_rec.pl_id,
399 p_elig_per_id => null,
400 p_elig_flag => p_elig_flag,
401 p_dt_mode => p_dt_mode,
405 p_out_mesg => p_out_mesg);
402 p_business_group_id => p_business_group_id,
403 p_per_in_ler_id => p_per_in_ler_id,
404 p_effective_date => p_effective_date,
406 end if;
407 end loop;
408 close c_elig;
409
410 else
411
412 if p_elig_per_id is not null then
413 return;
414 end if;
415
416 open c_inelig;
417 loop
418 fetch c_inelig into l_elig_rec;
419 if c_inelig%notfound then
420 exit;
421 end if;
422
423 l_cobj_lvl := get_level
424 (p_pgm_id => l_elig_rec.pgm_id,
425 p_ptip_id => l_elig_rec.ptip_id,
426 p_plip_id => l_elig_rec.plip_id,
427 p_pl_id => l_elig_rec.pl_id,
428 p_oipl_id => null);
429
430 if nvl(l_elig_rec.elig_flag,'N') <> p_elig_flag then
431 upd_elig_per_flag
432 (l_elig_rec,
433 p_business_group_id,
434 p_elig_flag,
435 p_effective_date,
436 p_dt_mode,
437 l_cobj_lvl);
438 end if;
439
440 if l_cobj_lvl not in ('PLAN','PNIP') then -- Bug 6370016
441 ben_eligibility_override.update_elig_hierarchy
442 (p_person_id => p_person_id,
443 p_pgm_id => p_pgm_id,
444 p_ptip_id => l_elig_rec.ptip_id,
445 p_plip_id => l_elig_rec.plip_id,
446 p_pl_id => l_elig_rec.pl_id,
447 p_elig_per_id => null,
448 p_elig_flag => p_elig_flag,
449 p_dt_mode => p_dt_mode,
450 p_business_group_id => p_business_group_id,
451 p_per_in_ler_id => p_per_in_ler_id,
452 p_effective_date => p_effective_date,
453 p_out_mesg => p_out_mesg);
454 else
455 upd_elig_per_opt_flag
456 (l_elig_rec,
457 p_business_group_id,
458 p_elig_flag,
459 p_effective_date,
460 p_dt_mode,
461 'OIPL');
462 end if;
463
464 end loop;
465 close c_inelig;
466 end if;
467 p_out_mesg := format_mesg;
468 --- no copy
469 exception
470 when others then
471 p_out_mesg := null ;
472 raise ;
473 end update_elig_hierarchy;
474
475 --
476 -- Inserts elig_per records starting from the current level of
477 -- the comp object upto PGM level
478 --
479 procedure create_elig_hierarchy
480 (p_elig_per in ben_elig_per_f%rowtype,
481 p_cobj_level in varchar2,
482 p_dt_mode in varchar2,
483 p_business_group_id in number,
484 p_effective_date in date,
485 p_out_mesg out nocopy varchar2) is
486
487 l_elig_per ben_elig_per_f%rowtype := p_elig_per;
488 l_eff_dt date := p_effective_date;
489 l_bg_id number := p_business_group_id;
490 l_cobj_level varchar2(30) := p_cobj_level;
491 l_elig_per_id number;
492 l_effective_start_date date;
493 l_effective_end_date date;
494 l_object_version_number number;
495 l_dummy varchar2(1);
496
497 cursor c_plip is
498 select plip.plip_id,plip.ordr_num
499 from ben_plip_f plip
500 where plip.pl_id = l_elig_per.pl_id
501 and plip.pgm_id = l_elig_per.pgm_id
502 and l_eff_dt between plip.effective_start_date
503 and plip.effective_end_date;
504
505 cursor c_ptip is
506 select ptip.ptip_id,ptip.ordr_num
507 from ben_ptip_f ptip,
508 ben_plip_f plip,
509 ben_pl_f pl
510 where ptip.pgm_id = l_elig_per.pgm_id
511 and ptip.pl_typ_id = pl.pl_typ_id
512 and pl.pl_id = plip.pl_id
513 and plip.plip_id = l_elig_per.plip_id
514 and l_eff_dt between ptip.effective_start_date
515 and ptip.effective_end_date
516 and l_eff_dt between plip.effective_start_date
517 and plip.effective_end_date
518 and l_eff_dt between pl.effective_start_date
519 and pl.effective_end_date;
520
521 cursor c_elig_cobj is
522 select null
523 from ben_elig_per_f pep
524 where pep.pgm_id = l_elig_per.pgm_id
525 and nvl(pep.plip_id,-1) = nvl(l_elig_per.plip_id,-1)
526 and nvl(pep.ptip_id,-1) = nvl(l_elig_per.ptip_id,-1)
527 and nvl(pep.pl_id,-1) = nvl(l_elig_per.pl_id,-1)
528 and pep.person_id = l_elig_per.person_id
529 and (pep.per_in_ler_id is null or
530 exists
531 (select null
532 from ben_per_in_ler pil
533 where pep.per_in_ler_id = pil.per_in_ler_id
534 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')))
535 and l_eff_dt between pep.effective_start_date
536 and pep.effective_end_date;
537
538 begin
539
540 if l_elig_per.pgm_id is null then
541 return;
542 end if;
543
544 -- fetch the next higher level comp object
545 if l_cobj_level = 'PLAN' then
546 open c_plip;
550 l_cobj_level := 'PLIP';
547 fetch c_plip into l_elig_per.plip_id,l_elig_per.plip_ordr_num;
548 close c_plip;
549 l_elig_per.pl_id := null;
551 elsif l_cobj_level = 'PLIP' then
552 open c_ptip;
553 fetch c_ptip into l_elig_per.ptip_id, l_elig_per.ptip_ordr_num ;
554 close c_ptip;
555 l_elig_per.plip_id := null;
556 l_elig_per.plip_ordr_num := null;
557 l_cobj_level := 'PTIP';
558 elsif l_cobj_level = 'PTIP' then
559 l_elig_per.ptip_id := null;
560 l_elig_per.ptip_ordr_num := null;
561 l_cobj_level := 'PGM';
562 else
563 return;
564 end if;
565
566 -- check if the comp object already exists in elig_per
567 -- if not, create a new eli_per record for the comp object
568 open c_elig_cobj;
569 fetch c_elig_cobj into l_dummy;
570 if c_elig_cobj%notfound then
571
572 BEN_eligible_person_API.create_eligible_person
573 (p_validate => FALSE
574 ,p_ELIG_PER_ID => l_elig_per_id
575 ,p_EFFECTIVE_START_DATE => l_EFFECTIVE_START_DATE
576 ,p_EFFECTIVE_END_DATE => l_EFFECTIVE_END_DATE
577 ,p_BUSINESS_GROUP_ID =>l_bg_id
578 ,p_PL_ID => l_elig_per.pl_id
579 ,p_PGM_ID => l_elig_per.pgm_id
580 ,p_PLIP_ID => l_elig_per.plip_id
581 ,p_PTIP_ID => l_elig_per.ptip_id
582 ,p_LER_ID =>l_elig_per.LER_ID
583 ,p_PERSON_ID =>l_elig_per.PERSON_ID
584 ,p_PER_IN_LER_ID =>l_elig_per.PER_IN_LER_ID
585 ,p_DPNT_OTHR_PL_CVRD_RL_FLAG =>l_elig_per.DPNT_OTHR_PL_CVRD_RL_FLAG
586 ,p_PRTN_OVRIDN_THRU_DT =>l_elig_per.PRTN_OVRIDN_THRU_DT
587 ,p_PL_KEY_EE_FLAG =>l_elig_per.PL_KEY_EE_FLAG
588 ,p_PL_HGHLY_COMPD_FLAG =>l_elig_per.PL_HGHLY_COMPD_FLAG
589 ,p_ELIG_FLAG =>l_elig_per.ELIG_FLAG
590 ,p_COMP_REF_AMT =>l_elig_per.COMP_REF_AMT
591 ,p_CMBN_AGE_N_LOS_VAL =>l_elig_per.CMBN_AGE_N_LOS_VAL
592 ,p_COMP_REF_UOM =>l_elig_per.COMP_REF_UOM
593 ,p_AGE_VAL =>l_elig_per.AGE_VAL
594 ,p_LOS_VAL =>l_elig_per.LOS_VAL
595 ,p_PRTN_END_DT =>l_elig_per.PRTN_END_DT
596 ,p_PRTN_STRT_DT =>l_elig_per.PRTN_STRT_DT
597 ,p_wait_perd_cmpltn_dt =>l_elig_per.wait_perd_cmpltn_dt
598 ,p_wait_perd_strt_dt =>l_elig_per.wait_perd_strt_dt
599 ,p_WV_CTFN_TYP_CD =>l_elig_per.WV_CTFN_TYP_CD
600 ,p_HRS_WKD_VAL =>l_elig_per.HRS_WKD_VAL
601 ,p_HRS_WKD_BNDRY_PERD_CD =>l_elig_per.HRS_WKD_BNDRY_PERD_CD
602 ,p_PRTN_OVRIDN_FLAG =>l_elig_per.PRTN_OVRIDN_FLAG
603 ,p_NO_MX_PRTN_OVRID_THRU_FLAG =>l_elig_per.NO_MX_PRTN_OVRID_THRU_FLAG
604 ,p_PRTN_OVRIDN_RSN_CD =>l_elig_per.PRTN_OVRIDN_RSN_CD
605 ,p_AGE_UOM =>l_elig_per.AGE_UOM
606 ,p_LOS_UOM =>l_elig_per.LOS_UOM
607 ,p_OVRID_SVC_DT =>l_elig_per.OVRID_SVC_DT
608 ,p_inelg_rsn_cd =>l_elig_per.inelg_rsn_cd
609 ,p_FRZ_LOS_FLAG =>l_elig_per.FRZ_LOS_FLAG
610 ,p_FRZ_AGE_FLAG =>l_elig_per.FRZ_AGE_FLAG
611 ,p_FRZ_CMP_LVL_FLAG =>l_elig_per.FRZ_CMP_LVL_FLAG
612 ,p_FRZ_PCT_FL_TM_FLAG =>l_elig_per.FRZ_PCT_FL_TM_FLAG
613 ,p_FRZ_HRS_WKD_FLAG =>l_elig_per.FRZ_HRS_WKD_FLAG
614 ,p_FRZ_COMB_AGE_AND_LOS_FLAG =>l_elig_per.FRZ_COMB_AGE_AND_LOS_FLAG
615 ,p_DSTR_RSTCN_FLAG =>l_elig_per.DSTR_RSTCN_FLAG
616 ,p_PCT_FL_TM_VAL =>l_elig_per.PCT_FL_TM_VAL
617 ,p_WV_PRTN_RSN_CD =>l_elig_per.WV_PRTN_RSN_CD
618 ,p_rt_comp_ref_amt =>l_elig_per.rt_comp_ref_amt
619 ,p_rt_cmbn_age_n_los_val =>l_elig_per.rt_cmbn_age_n_los_val
620 ,p_rt_comp_ref_uom =>l_elig_per.rt_comp_ref_uom
621 ,p_rt_age_val =>l_elig_per.rt_age_val
622 ,p_rt_los_val =>l_elig_per.rt_los_val
623 ,p_rt_hrs_wkd_val =>l_elig_per.rt_hrs_wkd_val
624 ,p_rt_hrs_wkd_bndry_perd_cd =>l_elig_per.rt_hrs_wkd_bndry_perd_cd
625 ,p_rt_age_uom =>l_elig_per.rt_age_uom
626 ,p_rt_los_uom =>l_elig_per.rt_los_uom
627 ,p_rt_pct_fl_tm_val =>l_elig_per.rt_pct_fl_tm_val
628 ,p_rt_frz_los_flag =>l_elig_per.rt_frz_los_flag
629 ,p_rt_frz_age_flag =>l_elig_per.rt_frz_age_flag
630 ,p_rt_frz_cmp_lvl_flag =>l_elig_per.rt_frz_cmp_lvl_flag
631 ,p_rt_frz_pct_fl_tm_flag =>l_elig_per.rt_frz_pct_fl_tm_flag
632 ,p_rt_frz_hrs_wkd_flag =>l_elig_per.rt_frz_hrs_wkd_flag
633 ,p_rt_frz_comb_age_and_los_flag =>l_elig_per.rt_frz_comb_age_and_los_flag
634 ,p_once_r_cntug_cd =>l_elig_per.once_r_cntug_cd
635 ,p_pl_ordr_num =>l_elig_per.pl_ordr_num
636 ,p_plip_ordr_num =>l_elig_per.plip_ordr_num
637 ,p_ptip_ordr_num =>l_elig_per.ptip_ordr_num
638 ,p_PEP_ATTRIBUTE_CATEGORY =>l_elig_per.PEP_ATTRIBUTE_CATEGORY
639 ,p_PEP_ATTRIBUTE1 =>l_elig_per.PEP_ATTRIBUTE1
640 ,p_PEP_ATTRIBUTE2 =>l_elig_per.PEP_ATTRIBUTE2
641 ,p_PEP_ATTRIBUTE3 =>l_elig_per.PEP_ATTRIBUTE3
642 ,p_PEP_ATTRIBUTE4 =>l_elig_per.PEP_ATTRIBUTE4
643 ,p_PEP_ATTRIBUTE5 =>l_elig_per.PEP_ATTRIBUTE5
644 ,p_PEP_ATTRIBUTE6 =>l_elig_per.PEP_ATTRIBUTE6
645 ,p_PEP_ATTRIBUTE7 =>l_elig_per.PEP_ATTRIBUTE7
646 ,p_PEP_ATTRIBUTE8 =>l_elig_per.PEP_ATTRIBUTE8
647 ,p_PEP_ATTRIBUTE9 =>l_elig_per.PEP_ATTRIBUTE9
648 ,p_PEP_ATTRIBUTE10 =>l_elig_per.PEP_ATTRIBUTE10
649 ,p_PEP_ATTRIBUTE11 =>l_elig_per.PEP_ATTRIBUTE11
650 ,p_PEP_ATTRIBUTE12 =>l_elig_per.PEP_ATTRIBUTE12
651 ,p_PEP_ATTRIBUTE13 =>l_elig_per.PEP_ATTRIBUTE13
652 ,p_PEP_ATTRIBUTE14 =>l_elig_per.PEP_ATTRIBUTE14
656 ,p_PEP_ATTRIBUTE18 =>l_elig_per.PEP_ATTRIBUTE18
653 ,p_PEP_ATTRIBUTE15 =>l_elig_per.PEP_ATTRIBUTE15
654 ,p_PEP_ATTRIBUTE16 =>l_elig_per.PEP_ATTRIBUTE16
655 ,p_PEP_ATTRIBUTE17 =>l_elig_per.PEP_ATTRIBUTE17
657 ,p_PEP_ATTRIBUTE19 =>l_elig_per.PEP_ATTRIBUTE19
658 ,p_PEP_ATTRIBUTE20 =>l_elig_per.PEP_ATTRIBUTE20
659 ,p_PEP_ATTRIBUTE21 =>l_elig_per.PEP_ATTRIBUTE21
660 ,p_PEP_ATTRIBUTE22 =>l_elig_per.PEP_ATTRIBUTE22
661 ,p_PEP_ATTRIBUTE23 =>l_elig_per.PEP_ATTRIBUTE23
662 ,p_PEP_ATTRIBUTE24 =>l_elig_per.PEP_ATTRIBUTE24
663 ,p_PEP_ATTRIBUTE25 =>l_elig_per.PEP_ATTRIBUTE25
664 ,p_PEP_ATTRIBUTE26 =>l_elig_per.PEP_ATTRIBUTE26
665 ,p_PEP_ATTRIBUTE27 =>l_elig_per.PEP_ATTRIBUTE27
666 ,p_PEP_ATTRIBUTE28 =>l_elig_per.PEP_ATTRIBUTE28
667 ,p_PEP_ATTRIBUTE29 =>l_elig_per.PEP_ATTRIBUTE29
668 ,p_PEP_ATTRIBUTE30 =>l_elig_per.PEP_ATTRIBUTE30
669 ,p_request_id =>l_elig_per.request_id
670 ,p_program_application_id =>l_elig_per.program_application_id
671 ,p_program_id =>l_elig_per.program_id
672 ,p_program_update_date =>l_elig_per.program_update_date
673 ,p_OBJECT_VERSION_NUMBER =>l_OBJECT_VERSION_NUMBER
674 ,p_effective_date => p_effective_date
675 );
676 update_count(l_cobj_level);
677 end if;
678 close c_elig_cobj;
679
680 -- if we have not reached the top of hierarchy, recurse
681 if l_cobj_level <> 'PGM' then
682 create_elig_hierarchy
683 (p_elig_per =>l_elig_per,
684 p_cobj_level =>l_cobj_level,
685 p_dt_mode =>p_dt_mode,
686 p_business_group_id =>p_business_group_id,
687 p_effective_date =>p_effective_date,
688 p_out_mesg =>p_out_mesg);
689 else
690 p_out_mesg := format_mesg;
691 reset_counter_rec;
692 end if;
693 exception
694 when others then
695 p_out_mesg := null ;
696 raise ;
697
698 end create_elig_hierarchy;
699
700 End ben_eligibility_override;