1 package body ben_comp_obj_filter as
2 /* $Header: bebmfilt.pkb 120.13 2011/12/14 12:05:37 usaraswa ship $ */
3 --
4 type g_procptip is record
5 (ptip_id number
6 ,eligible boolean
7 );
8 --
9 type g_procptip_set is table of g_procptip index by binary_integer;
10 --
11 -- Processed PTIP list
12 --
13 g_procptip_list g_procptip_set;
14 --
15 g_package varchar2(50) := 'ben_comp_obj_filter.';
16 g_hash_key number := ben_hash_utility.get_hash_key;
17 g_hash_jump number := ben_hash_utility.get_hash_jump;
18 --
19 function check_dupproc_ptip
20 (p_ptip_id in number
24 --
21 )
22 return boolean
23 is
25 l_package varchar2(80) := g_package||'.check_dupproc_ptip';
26 --
27 l_hv pls_integer;
28 --
29 begin
30 --
31 -- hr_utility.set_location ('Entering ' || l_package,10);
32 --
33 -- Get the hashed value for the ptip
34 --
35 l_hv := mod(p_ptip_id,g_hash_key);
36 --
37 -- Check for list entry. if none exists the NO_DATA_FOUND exception
38 -- will fire.
39 --
40 if g_procptip_list(l_hv).ptip_id = p_ptip_id then
41 --
42 return true;
43 --
44 --
45 -- A clash has been found
46 --
47 else
48 --
49 -- Loop through the hash using the jump routine to check further
50 -- indexes if none exists at current index the NO_DATA_FOUND expection
51 -- will fire
52 --
53 while g_procptip_list(l_hv).ptip_id <> p_ptip_id loop
54 --
55 l_hv := l_hv+g_hash_jump;
56 --
57 end loop;
58 --
59 end if;
60 --
61 -- hr_utility.set_location ('Leaving '||l_package,10);
62 --
63 exception when NO_DATA_FOUND then
64 --
65 -- No value stored for the hashed value due to the error being raised.
66 -- We are dealing with a unique PTIP. Add the PTIP to the list
67 --
68 g_procptip_list(l_hv).ptip_id := p_ptip_id;
69 --
70 -- Initialise the ptip eligible flag to false this will be set at
71 -- the end of eligibility to the determined eligibility result.
72 --
73 g_procptip_list(l_hv).eligible := false;
74 --
75 return false;
76 --
77 end check_dupproc_ptip;
78 --
79 procedure flush_dupproc_ptip_list
80
81 is
82 --
83 l_package varchar2(80) := g_package||'.flush_dupproc_ptip_list';
84 --
85 l_dupptip boolean;
86 --
87 begin
88 --
89 g_procptip_list.delete;
90 --
91 end flush_dupproc_ptip_list;
92 --
93 procedure set_dupproc_ptip_elig
94 (p_ptip_id in number
95 ,p_eligible in boolean
96 )
97 is
98 --
99 l_package varchar2(80) := g_package||'.set_dupproc_ptip_elig';
100 --
101 l_hv pls_integer;
102 --
103 begin
104 --
105 hr_utility.set_location ('Entering ' || l_package,10);
106 --
107 -- Get the hashed value for the ptip
108 --
109 l_hv := ben_hash_utility.get_hashed_index(p_id => p_ptip_id);
110 --
111 -- Check for list entry. if none exists the NO_DATA_FOUND exception
112 -- will fire.
113 --
114 if g_procptip_list(l_hv).ptip_id = p_ptip_id then
115 --
116 g_procptip_list(l_hv).eligible := p_eligible;
117 --
118 --
119 -- A clash has been found
120 --
121 else
122 --
123 -- Loop through the hash using the jump routine to check further
124 -- indexes if none exists at current index the NO_DATA_FOUND expection
125 -- will fire
126 --
127 while g_procptip_list(l_hv).ptip_id <> p_ptip_id loop
128 --
129 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
130 --
131 end loop;
132 --
133 g_procptip_list(l_hv).eligible := p_eligible;
134 --
135 end if;
136 --
137 hr_utility.set_location ('Leaving '||l_package,10);
138 --
139 exception when NO_DATA_FOUND then
140 --
141 -- No value stored for the hashed value due to the error being raised.
142 -- We are dealing with a unique PTIP. Add the PTIP to the list
143 --
144 hr_Utility.set_location('hit no_data_found condition',10);
145 g_procptip_list(l_hv).ptip_id := p_ptip_id;
146 --
147 -- Initialise the ptip eligible flag to false this will be set at
148 -- the end of eligibility to the determined eligibility result.
149 --
150 g_procptip_list(l_hv).eligible := p_eligible;
151 --
152 return;
153 --
154 end set_dupproc_ptip_elig;
155 --
156 function get_dupproc_ptip_elig
157 (p_ptip_id in number
158 )
159 return boolean
160 is
161 --
162 l_package varchar2(80) := g_package||'.get_dupproc_ptip_elig';
163 --
164 l_hv pls_integer;
165 --
166 begin
167 --
168 hr_utility.set_location ('Entering ' || l_package,10);
169 --
170 -- Get the hashed value for the ptip
171 --
172 l_hv := ben_hash_utility.get_hashed_index(p_id => p_ptip_id);
173 --
174 -- Check for list entry. if none exists the NO_DATA_FOUND exception
175 -- will fire.
176 --
177 if g_procptip_list(l_hv).ptip_id = p_ptip_id then
178 --
179 return g_procptip_list(l_hv).eligible;
180 --
181 --
182 -- A clash has been found
183 --
184 else
185 --
186 -- Loop through the hash using the jump routine to check further
187 -- indexes if none exists at current index the NO_DATA_FOUND expection
188 -- will fire
189 --
190 while g_procptip_list(l_hv).ptip_id <> p_ptip_id loop
191 --
192 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
193 --
194 end loop;
195 --
196 return g_procptip_list(l_hv).eligible;
197 --
198 end if;
199 --
200 hr_utility.set_location ('Leaving '||l_package,10);
201 --
202 exception when NO_DATA_FOUND then
203 --
204 -- No value stored for the hashed value due to the error being raised.
205 -- We are dealing with a unique PTIP. Add the PTIP to the list
206 --
207 hr_Utility.set_location('hit no_data_found condition',20);
208 g_procptip_list(l_hv).ptip_id := p_ptip_id;
209 --
210 -- Initialise the ptip eligible flag to false this will be set at
211 -- the end of eligibility to the determined eligibility result.
212 --
213 g_procptip_list(l_hv).eligible := false;
214 --
215 return false;
216 --
217 end get_dupproc_ptip_elig;
218 --
219 procedure set_parent_elig_flags
220 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
221 ,p_eligible in boolean
222 ,p_treeele_num in pls_integer
223 --
224 ,p_par_elig_state in out nocopy ben_comp_obj_filter.g_par_elig_state_rec
225 )
226 is
227 --
228 l_package varchar2(80) := g_package||'.set_parent_elig_flags';
229 --
230 l_hv pls_integer;
231 --
232 begin
233 --
234 -- hr_utility.set_location ('Entering ' || l_package,10);
235 --
236 if p_eligible then
237 --
238 if p_comp_obj_tree_row.pgm_id is not null then
239 --
240 p_par_elig_state.elig_for_pgm_flag := 'Y';
241 --
242 elsif p_comp_obj_tree_row.ptip_id is not null then
243 --
244 -- Set the PTIP eligibility in the duplicate PTIP list
245 --
246 set_dupproc_ptip_elig
247 (p_ptip_id => p_comp_obj_tree_row.ptip_id
248 ,p_eligible => TRUE
249 );
250 --
251 p_par_elig_state.elig_for_ptip_flag := 'Y';
252 --
253 elsif p_comp_obj_tree_row.plip_id is not null then
254 --
255 p_par_elig_state.elig_for_plip_flag := 'Y';
256 --
257 elsif p_comp_obj_tree_row.pl_id is not null then
258 --
259 p_par_elig_state.elig_for_pl_flag := 'Y';
260 --
261 end if;
262 --
263 elsif not p_eligible then
264 --
265 if p_comp_obj_tree_row.pgm_id is not null then
266 --
267 -- When in-eligible for the program cannot be eligible for
268 -- sub comp objects
269 --
270 p_par_elig_state.elig_for_pgm_flag := 'N';
271 p_par_elig_state.elig_for_ptip_flag := 'N';
272 p_par_elig_state.elig_for_plip_flag := 'N';
273 p_par_elig_state.elig_for_pl_flag := 'N';
274 --
275 elsif p_comp_obj_tree_row.ptip_id is not null then
276 --
277 -- Set the PTIP eligibility in the duplicate PTIP list
278 --
279 set_dupproc_ptip_elig
280 (p_ptip_id => p_comp_obj_tree_row.ptip_id
281 ,p_eligible => FALSE
282 );
283 --
284 -- When in-eligible for the ptip cannot be eligible for
285 -- sub comp objects
286 --
287 p_par_elig_state.elig_for_ptip_flag := 'N';
288 p_par_elig_state.elig_for_plip_flag := 'N';
289 p_par_elig_state.elig_for_pl_flag := 'N';
290 --
291 elsif p_comp_obj_tree_row.plip_id is not null then
292 --
293 -- When in-eligible for the ptip cannot be eligible for
294 -- sub comp objects
295 --
296 p_par_elig_state.elig_for_plip_flag := 'N';
297 p_par_elig_state.elig_for_pl_flag := 'N';
298 --
299 elsif p_comp_obj_tree_row.pl_id is not null then
300 --
301 p_par_elig_state.elig_for_pl_flag := 'N';
302 --
303 end if;
304 --
305 end if;
306 --
307 -- hr_utility.set_location ('Leaving '||l_package,10);
308 --
312 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
309 end set_parent_elig_flags;
310 --
311 procedure set_bound_parent_elig_flags
313 --
314 ,p_par_elig_state in out nocopy ben_comp_obj_filter.g_par_elig_state_rec
315 )
316 is
317 --
318 l_package varchar2(80) := g_package||'.set_bound_parent_elig_flags';
319 --
320 begin
321 --
322 -- hr_utility.set_location ('Entering ' || l_package,10);
323 --
324 if p_comp_obj_tree_row.pl_id is not null
325 and p_comp_obj_tree_row.pl_nip = 'N'
326 then
327 --
328 p_par_elig_state.elig_for_pl_flag := 'Y';
329 --
330 elsif p_comp_obj_tree_row.pl_id is not null
331 and p_comp_obj_tree_row.pl_nip = 'Y'
332 then
333 --
334 p_par_elig_state.elig_for_pgm_flag := 'Y';
335 p_par_elig_state.elig_for_ptip_flag := 'Y';
336 p_par_elig_state.elig_for_plip_flag := 'Y';
337 p_par_elig_state.elig_for_pl_flag := 'Y';
338 --
339 elsif p_comp_obj_tree_row.plip_id is not null then
340 --
341 p_par_elig_state.elig_for_plip_flag := 'Y';
342 p_par_elig_state.elig_for_pl_flag := 'Y';
343 --
344 elsif p_comp_obj_tree_row.ptip_id is not null then
345 --
346 -- hr_utility.set_location ('BENMGLEINPEFPTIP: '||p_comp_obj_tree_row.ptip_id,20);
347 --
348 p_par_elig_state.elig_for_ptip_flag := 'Y';
349 p_par_elig_state.elig_for_plip_flag := 'Y';
350 p_par_elig_state.elig_for_pl_flag := 'Y';
351 --
352 elsif p_comp_obj_tree_row.pgm_id is not null then
353 --
354 hr_utility.set_location ('BENMGLEINPEFPGM: '||p_comp_obj_tree_row.pgm_id,20);
355 --
356 p_par_elig_state.elig_for_pgm_flag := 'Y';
357 p_par_elig_state.elig_for_ptip_flag := 'Y';
358 p_par_elig_state.elig_for_plip_flag := 'Y';
359 p_par_elig_state.elig_for_pl_flag := 'Y';
360 --
361 end if;
362 --
363 -- hr_utility.set_location ('Leaving '||l_package,10);
364 --
365 end set_bound_parent_elig_flags;
366 --
367 function check_prevelig_compobj
368 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
369 ,p_business_group_id in number
370 ,p_person_id in number
371 ,p_effective_date in date
372 )
373 return boolean
374 is
375 --
376 l_package varchar2(80) := g_package||'.check_prevelig_compobj';
377 --
378 l_inst_row ben_derive_part_and_rate_facts.g_cache_structure;
379 --
380 l_prevelig boolean;
381 l_dummy_number number;
382 --
383 begin
384 --
385 l_prevelig := false;
386 --
387 if p_comp_obj_tree_row.pgm_id is not null then
388 --
389 l_prevelig := false;
390 --
391 elsif p_comp_obj_tree_row.ptip_id is not null then
392 --
393 -- hr_utility.set_location ('PTIP PILPEP ' || l_package,10);
394 --
395 ben_pep_cache.get_pilpep_dets
396 (p_person_id => p_person_id
397 ,p_business_group_id => p_business_group_id
398 ,p_effective_date => p_effective_date
399 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
400 ,p_ptip_id => p_comp_obj_tree_row.ptip_id
401 ,p_inst_row => l_inst_row
402 ,p_date_sync => true
403 );
404 --
405 if nvl(l_inst_row.elig_flag,'N') = 'Y'
406 then
407 --
408 l_prevelig := TRUE;
409 --
410 end if;
411 --
412 -- hr_utility.set_location ('Dn PTIP PILPEP ' || l_package,10);
416 -- hr_utility.set_location ('PLIP PILPEP ' || l_package,10);
413 --
414 elsif p_comp_obj_tree_row.plip_id is not null then
415 --
417 --
418 ben_pep_cache.get_pilpep_dets
419 (p_person_id => p_person_id
420 ,p_business_group_id => p_business_group_id
421 ,p_effective_date => p_effective_date
422 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
423 ,p_plip_id => p_comp_obj_tree_row.plip_id
424 ,p_inst_row => l_inst_row
425 ,p_date_sync => true
426 );
427 --
428 if nvl(l_inst_row.elig_flag,'N') = 'Y'
429 then
430 --
431 l_prevelig := TRUE;
432 --
433 end if;
434 --
435 -- hr_utility.set_location ('Dn PLIP PILPEP ' || l_package,10);
436 --
437 elsif p_comp_obj_tree_row.pl_id is not null
438 then
439 --
443 (p_person_id => p_person_id
440 -- hr_utility.set_location ('PL PILPEP ' || l_package,10);
441 --
442 ben_pep_cache.get_pilpep_dets
444 ,p_business_group_id => p_business_group_id
445 ,p_effective_date => p_effective_date
446 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
447 ,p_pl_id => p_comp_obj_tree_row.pl_id
448 ,p_inst_row => l_inst_row
449 ,p_date_sync => true
450 );
451 --
452 if nvl(l_inst_row.elig_flag,'N') = 'Y'
453 then
454 --
455 l_prevelig := TRUE;
456 --
457 end if;
458 --
459 -- hr_utility.set_location ('Dn PL PILPEP ' || l_package,10);
460 --
461 elsif p_comp_obj_tree_row.oipl_id is not null then
462 --
463 -- hr_utility.set_location ('OIPL PILPEP ' || l_package,10);
464 -- Bug 8351660: passed the fonm date in the call to ben_pep_cache.get_pilepo_dets
465 ben_pep_cache.get_pilepo_dets
466 (p_person_id => p_person_id
467 ,p_business_group_id => p_business_group_id
468 ,p_effective_date => nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,p_effective_date)
469 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
470 ,p_pl_id => p_comp_obj_tree_row.par_pl_id
471 ,p_opt_id => p_comp_obj_tree_row.par_opt_id
472 ,p_inst_row => l_inst_row
473 ,p_date_sync => true --Bug 13257261
474 );
475 --
476 if nvl(l_inst_row.elig_flag,'N') = 'Y'
477 then
478 --
479 l_prevelig := TRUE;
480 --
481 end if;
482 --
483 -- hr_utility.set_location ('Dn OIPL PILPEP ' || l_package,10);
484 --
485 end if;
486 --
487 return l_prevelig;
488 --
489 end check_prevelig_compobj;
490 --
491 function check_selection_rule
492 (p_person_selection_rule_id in number,
493 p_person_id in number,
494 p_business_group_id in number,
495 p_effective_date in date) return boolean is
496 --
497 l_outputs ff_exec.outputs_t;
498 l_assignment_id number;
499 l_package varchar2(80) := g_package||'.check_selection_rule';
500 --
501 begin
502 --
503 if p_person_selection_rule_id is null then
504 --
505 return true;
506 --
507 else
508 --
509 l_assignment_id := benutils.get_assignment_id
510 (p_person_id => p_person_id,
511 p_business_group_id => p_business_group_id,
512 p_effective_date => p_effective_date);
513 --
514 l_outputs := benutils.formula
515 (p_formula_id => p_person_selection_rule_id,
516 p_effective_date => p_effective_date,
517 p_business_group_id => p_business_group_id,
518 p_assignment_id => l_assignment_id,
519 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
520 p_param1_value => to_char(p_person_id));
521 --
522 if l_outputs(l_outputs.first).value = 'Y' then
523 --
524 return true;
525 --
526 elsif l_outputs(l_outputs.first).value = 'N' then
527 --
528 return false;
529 --
530 elsif l_outputs(l_outputs.first).value <> 'N' then
531 --
532 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
533 fnd_message.set_token('RL','person_selection_rule_id');
534 fnd_message.set_token('PROC',l_package);
535 raise ben_manage_life_events.g_record_error;
536 --
537 end if;
538 --
539 end if;
540 end check_selection_rule;
541 --
542
543 --Bug 13257261 changes start
544 --Removed the code to determine date from filter_comp_objects and written in a
545 --separate procedure as the the date should be determined in multiple places.
546 ---------------------------------------------------------------------------------
547 procedure get_new_date
548 (p_comp_obj_tree in ben_manage_life_events.g_cache_proc_object_table
549 ,p_mode in varchar
550 ,p_person_id in number
551 ,p_effective_date in date
552 ,p_treeele_num in pls_integer
553 ,p_ler_id in number default null
554 ,p_lf_evt_ocrd_dt in date default null
555 ,p_business_group_id in number default null
556 ,p_new_effective_date out nocopy date
557 ,p_effective_date_1 out nocopy date
558 ) is
559
560 l_new_effective_date date;
561 l_fonm_cd varchar2(1);
562 l_rec_enrt_cvg_strt_dt_1 date;
563 l_mode varchar2(1);
564 l_rec benutils.g_active_life_event;
565 l_lf_rec benutils.g_active_life_event;
566 l_dummy_rt_strt_dt_1 DATE;
567 l_dummy_rt_strt_dt_cd_1 VARCHAR2(30);
568 l_dummy_rt_strt_dt_rl_1 NUMBER;
569 l_dummy_enrt_cvg_end_dt_1 DATE;
570 l_dummy_enrt_cvg_end_dt_cd_1 VARCHAR2(30);
571 l_dummy_enrt_cvg_end_dt_rl_1 NUMBER;
572 l_dummy_rt_end_dt_1 DATE;
573 l_dummy_rt_end_dt_cd_1 VARCHAR2(30);
574 l_dummy_rt_end_dt_rl_1 NUMBER;
575 l_dummy_enrt_cvg_strt_dt_cd_1 VARCHAR2(30);
576 l_dummy_enrt_cvg_strt_dt_rl_1 NUMBER;
577 l_effective_date date;
578 l_rec_lee_rsn_id NUMBER;
579 l_rec_enrt_perd_id NUMBER;
585 cursor c_lee_rsn(c_pl_id number ,
580 l_treeele_num NUMBER;
581 l_dummy VARCHAR2(30);
582 l_defer_deenrol_flag varchar2(1);
583 l_proc varchar2(80);
584
586 c_pgm_id number,
587 c_effective_date date)
588 is
589 select 1 ordr_num ,
590 leer.lee_rsn_id,
591 NVL(leer.defer_deenrol_flag,'N')
592 FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
593 WHERE leer.ler_id = p_ler_id
594 AND leer.business_group_id = p_business_group_id
595 AND c_effective_date BETWEEN leer.effective_start_date
596 AND leer.effective_end_date
597 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
598 AND petc.pl_id = c_pl_id
599 AND petc.enrt_typ_cycl_cd = 'L' -- life event
600 AND petc.business_group_id = p_business_group_id
601 AND c_effective_date BETWEEN petc.effective_start_date
602 AND petc.effective_end_date
603 UNION
604 select 2 ordr_num ,
605 leer.lee_rsn_id,
606 NVL(leer.defer_deenrol_flag,'N')
607 FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
608 WHERE leer.ler_id = p_ler_id
609 AND leer.business_group_id = p_business_group_id
610 AND c_effective_date BETWEEN leer.effective_start_date
611 AND leer.effective_end_date
612 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
613 AND petc.pgm_id = c_pgm_id
614 AND petc.enrt_typ_cycl_cd = 'L' -- life event
615 AND petc.business_group_id = p_business_group_id
616 AND c_effective_date BETWEEN petc.effective_start_date
617 AND petc.effective_end_date
618 order by 1 ;
619 --
620 begin
621 l_proc := g_package||'get_new_date';
622 hr_utility.set_location('Entering '||l_proc,10);
623 l_mode := p_mode;
624 l_treeele_num := p_treeele_num;
625 if l_mode not in ('A','P','S','T') then --a
626 -- p_mode changed to l_mode in following line by Gopal Venkataraman 3/27/01 bug 1636071
627
628 hr_utility.set_location('mode'||l_mode,10);
629 --
630 -- p_mode changed to l_mode in following line by Gopal Venkataraman 3/27/01 bug 1636071
631 if (l_mode = 'U'or l_mode = 'R' or l_mode = 'D') then --b
632
633 if l_mode <> 'D' then --c
634 benutils.get_active_life_event
635 (p_person_id => p_person_id,
636 p_business_group_id => p_business_group_id,
637 p_effective_date => p_effective_date,
638 p_lf_event_mode => 'U',
639 p_rec => l_rec);
640
641 else
642 benutils.get_active_life_event
643 (p_person_id => p_person_id,
644 p_business_group_id => p_business_group_id,
645 p_effective_date => p_effective_date,
646 p_lf_event_mode => 'D',
647 p_rec => l_rec);
648
649 end if; --c
650
651 benutils.get_active_life_event
652 (p_person_id => p_person_id,
653 p_business_group_id => p_business_group_id,
654 p_effective_date => p_effective_date,
655 p_rec => l_lf_rec);
656
657 else --b
658 --
659 -- CWB Changes .
660 --
661 if l_mode = 'W' then --d
662 --
663 benutils.get_active_life_event
664 (p_person_id => p_person_id,
665 p_business_group_id => p_business_group_id,
666 p_effective_date => p_effective_date,
667 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
668 p_ler_id => p_ler_id,
669 p_rec => l_rec);
670 --
671 elsif l_mode = 'G' then
672 --
673 benutils.get_active_life_event
674 (p_person_id => p_person_id,
675 p_business_group_id => p_business_group_id,
676 p_effective_date => p_effective_date,
677 p_lf_event_mode => 'G',
678 p_rec => l_rec);
679 --
680 elsif l_mode = 'M' then
681 --
682 benutils.get_active_life_event
683 (p_person_id => p_person_id,
684 p_business_group_id => p_business_group_id,
685 p_effective_date => p_effective_date,
686 p_lf_event_mode => 'M',
687 p_rec => l_rec);
688 --
689 elsif l_mode = 'I' then
690 --
691 -- iRec
692 benutils.get_active_life_event
693 (p_person_id => p_person_id,
694 p_business_group_id => p_business_group_id,
695 p_effective_date => p_effective_date,
696 p_lf_event_mode => 'I',
697 p_rec => l_rec);
698 --
699 else
700 --
701 benutils.get_active_life_event
702 (p_person_id => p_person_id,
703 p_business_group_id => p_business_group_id,
704 p_effective_date => p_effective_date,
705 p_rec => l_rec);
706 --
707 end if;--d
708 end if;--b
709 end if;--a
710
711
712 l_effective_date :=nvl(l_rec.lf_evt_ocrd_dt,p_effective_date);
713 l_new_effective_date := nvl(l_rec.lf_evt_ocrd_dt,p_effective_date);
714
715 hr_utility.set_location('l_effective_date '||l_effective_date,10);
716 hr_utility.set_location('l_new_effective_date '||l_new_effective_date,10);
717
718 if l_mode in ('L') then
719
720 open c_lee_rsn(c_pl_id => p_comp_obj_tree(l_treeele_num).par_pl_id,
721 c_pgm_id=> p_comp_obj_tree(l_treeele_num).par_pgm_id,
722 c_effective_date => nvl(l_rec.lf_evt_ocrd_dt, p_effective_date)
723 ) ;
724 fetch c_lee_rsn into l_dummy , l_rec_lee_rsn_id ,l_defer_deenrol_flag ;
725 close c_lee_rsn ;
726
727 end if;
728
729 hr_utility.set_location('l_effective_date '||l_effective_date,20);
730
731 ben_use_cvg_rt_date.get_csd_rsd_Status
732 (p_pgm_id => p_comp_obj_tree(l_treeele_num).par_pgm_id
733 ,p_ptip_id => p_comp_obj_tree(l_treeele_num).par_ptip_id
734 ,p_plip_id => p_comp_obj_tree(l_treeele_num).par_plip_id
738 ) ;
735 ,p_pl_id => p_comp_obj_tree(l_treeele_num).par_pl_id
736 ,p_effective_date => p_effective_date
737 ,p_status => l_fonm_cd
739
740 hr_utility.set_location('l_fonm_cd '||l_fonm_cd,10);
741
742 if l_fonm_cd = 'Y' then
743 ben_determine_date.rate_and_coverage_dates
744 (p_cache_mode => TRUE
745 ,p_per_in_ler_id => l_rec.per_in_ler_id
746 ,p_person_id => p_person_id
747 ,p_pgm_id => p_comp_obj_tree(l_treeele_num).par_pgm_id
748 ,p_pl_id => p_comp_obj_tree(l_treeele_num).par_pl_id
749 ,p_oipl_id => p_comp_obj_tree(l_treeele_num).oipl_id
750 ,p_par_ptip_id => p_comp_obj_tree(l_treeele_num).par_ptip_id
751 ,p_par_plip_id => p_comp_obj_tree(l_treeele_num).par_plip_id
752 ,p_lee_rsn_id => l_rec_lee_rsn_id
753 ,p_enrt_perd_id => l_rec_enrt_perd_id
754 ,p_business_group_id => p_business_group_id
755 ,p_which_dates_cd => 'C'
756 ,p_date_mandatory_flag => 'N'
757 ,p_compute_dates_flag => 'Y'
758 ,p_enrt_cvg_strt_dt => l_rec_enrt_cvg_strt_dt_1
759 ,p_enrt_cvg_strt_dt_cd => l_dummy_enrt_cvg_strt_dt_cd_1
760 ,p_enrt_cvg_strt_dt_rl => l_dummy_enrt_cvg_strt_dt_rl_1
761 ,p_rt_strt_dt => l_dummy_rt_strt_dt_1
762 ,p_rt_strt_dt_cd => l_dummy_rt_strt_dt_cd_1
763 ,p_rt_strt_dt_rl => l_dummy_rt_strt_dt_rl_1
764 ,p_enrt_cvg_end_dt => l_dummy_enrt_cvg_end_dt_1
765 ,p_enrt_cvg_end_dt_cd => l_dummy_enrt_cvg_end_dt_cd_1
766 ,p_enrt_cvg_end_dt_rl => l_dummy_enrt_cvg_end_dt_rl_1
767 ,p_rt_end_dt => l_dummy_rt_end_dt_1
768 ,p_rt_end_dt_cd => l_dummy_rt_end_dt_cd_1
769 ,p_rt_end_dt_rl => l_dummy_rt_end_dt_rl_1
773
770 ,p_effective_date => p_effective_date
771 ,p_lf_evt_ocrd_dt => nvl(l_rec.lf_evt_ocrd_dt, p_effective_date)
772 );
774 l_new_effective_date := nvl(l_rec_enrt_cvg_strt_dt_1,l_new_effective_date);
775
776 end if;
777
778 p_new_effective_date := l_new_effective_date;
779 p_effective_date_1 := l_effective_date;
780
781 hr_utility.set_location('p_new_effective_date '||p_new_effective_date,10);
782 hr_utility.set_location('p_effective_date_1 '||p_effective_date_1,10);
783 hr_utility.set_location('Leaving '||l_proc,10);
784
785 /*exception
786 when others then
787 hr_utility.set_location('SQLERRM '||sqlerrm,10);*/
788
789 end get_new_date;
790
791 --Bug 13257261 changes end
792 --
793 procedure filter_comp_objects
794 (p_comp_obj_tree in ben_manage_life_events.g_cache_proc_object_table
795 ,p_mode in varchar
796 ,p_person_id in number
797 ,p_effective_date in date
798 ,p_maxtreeele_num in pls_integer
799 --
800 ,p_par_elig_state in out nocopy ben_comp_obj_filter.g_par_elig_state_rec
801 ,p_treeele_num in out nocopy pls_integer
802 --
803 ,p_treeloop out nocopy boolean
804 ,p_ler_id in number default null
805 ,p_lf_evt_ocrd_dt in date default null
806 -- ,p_popl_enrt_typ_cycl_id in number default null
807 ,p_business_group_id in number default null
808 )
809 is
810 --
811 l_package varchar2(80) := g_package||'filter_comp_objects';
812 --
813 l_comp_obj_tree ben_manage_life_events.g_cache_proc_object_table;
814 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
815 l_par_elig_state ben_comp_obj_filter.g_par_elig_state_rec;
816 --
817 l_boundary boolean;
818 l_treeloop boolean;
819 --
820 l_pgm_id number;
821 l_ptip_id number;
822 l_plip_id number;
823 l_pl_id number;
824 l_oipl_id number;
825 l_pl_nip varchar2(30);
826 --
827 l_treeele_num pls_integer;
828 --
829 l_failed_rule boolean;
830 --
831 --
832 --------------------------------------------------------------------------------
833 --Bug 13257261 changes Start
834 -- added code to get the date on which the Comp Object to be proceesed.
835 ----------------------------------------
836 l_new_effective_date date;
837 l_effective_date date;
838 -------------------------------------------
839 ----Bug 13257261 changes end
840
841 -- 12715758
842 -- Changed p_effective_date to l_effective_date in the below cursors
843 cursor c_lee_rsn_rl_pgm is
844 select formula_id
845 FROM ben_lee_rsn_f leer,
846 ben_popl_enrt_typ_cycl_f petc,
847 ben_lee_rsn_rl_f lrr
848 WHERE leer.ler_id = p_ler_id
849 AND l_effective_date BETWEEN leer.effective_start_date -- 12715758
850 AND leer.effective_end_date
851 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
852 AND petc.pgm_id = l_pgm_id
853 AND petc.enrt_typ_cycl_cd = 'L'
854 AND l_effective_date BETWEEN petc.effective_start_date -- 12715758
855 AND petc.effective_end_date
856 and l_effective_date between -- 12715758
857 lrr.effective_start_date and lrr.effective_end_date
858 and lrr.lee_rsn_id=leer.lee_rsn_id
859 and lrr.business_group_id=leer.business_group_id
860 order by ordr_to_aply_num;
861 --
862 -- bug - 4644355 - changed pgm_id to pl_id below
863 --
864 cursor c_lee_rsn_rl_plnip is
865 select formula_id
866 FROM ben_lee_rsn_f leer,
867 ben_popl_enrt_typ_cycl_f petc,
868 ben_lee_rsn_rl_f lrr
869 WHERE leer.ler_id = p_ler_id
870 AND l_effective_date BETWEEN leer.effective_start_date -- 12715758
871 AND leer.effective_end_date
872 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
873 AND petc.pl_id = l_pl_id
874 AND petc.enrt_typ_cycl_cd = 'L'
875 AND l_effective_date BETWEEN petc.effective_start_date -- 12715758
876 AND petc.effective_end_date
877 and l_effective_date between -- 12715758
878 lrr.effective_start_date and lrr.effective_end_date
879 and lrr.lee_rsn_id=leer.lee_rsn_id
880 and lrr.business_group_id=leer.business_group_id
881 order by ordr_to_aply_num;
885 from ben_schedd_enrt_rl_f lrr,
882 --
883 cursor c_schedd_enrt_rl_pgm is
884 select formula_id
886 ben_popl_enrt_typ_cycl_f pet,
887 ben_enrt_perd enp
888 where pet.pgm_id = l_pgm_id
889 and l_effective_date -- 12715758
890 between pet.effective_start_date
891 and pet.effective_end_date
892 and pet.popl_enrt_typ_cycl_id =
893 enp.popl_enrt_typ_cycl_id
894 -- PB : 5422 :
895 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
896 /* 5422 : PB :and enp.strt_dt = (select enp1.strt_dt
897 from ben_enrt_perd enp1
898 where enp1.enrt_perd_id
899 = p_popl_enrt_typ_cycl_id) */
900 and enp.business_group_id =
901 pet.business_group_id
902 and l_effective_date between -- 12715758
903 lrr.effective_start_date and lrr.effective_end_date
904 and lrr.enrt_perd_id=enp.enrt_perd_id
905 and lrr.business_group_id=enp.business_group_id
906 order by ordr_to_aply_num;
907 --
908 cursor c_schedd_enrt_rl_plnip is
909 select formula_id
910 from ben_schedd_enrt_rl_f lrr,
911 ben_popl_enrt_typ_cycl_f pet,
912 ben_enrt_perd enp
913 -- CWB changes
914 where pet.pl_id = l_pl_id
915 and l_effective_date -- 12715758
916 between pet.effective_start_date
917 and pet.effective_end_date
918 and pet.popl_enrt_typ_cycl_id =
919 enp.popl_enrt_typ_cycl_id
920 -- PB : 5422 :
921 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
922 /* and enp.strt_dt = (select enp1.strt_dt
923 from ben_enrt_perd enp1
924 where enp1.enrt_perd_id
925 = p_popl_enrt_typ_cycl_id) */
926 and enp.business_group_id =
927 pet.business_group_id
928 and l_effective_date between -- 12715758
929 lrr.effective_start_date and lrr.effective_end_date
930 and lrr.enrt_perd_id=enp.enrt_perd_id
931 and lrr.business_group_id=enp.business_group_id
932 order by ordr_to_aply_num;
933 --
934 begin
935 --
936 hr_utility.set_location ('Entering '||l_package,10);
937 --
938 l_par_elig_state := p_par_elig_state;
939 l_treeele_num := p_treeele_num;
940 --
941 l_boundary := FALSE;
942 --
943 -- Comp object navigation loop. Only loop if a boundary is hit
944 --
945 loop
946 hr_utility.set_location ('St CO Tree Loop '||l_package,10);
947 --
948 --Bug 13257261 changes Start
949 get_new_date
950 (p_comp_obj_tree => p_comp_obj_tree
951 ,p_mode => p_mode
952 ,p_person_id => p_person_id
953 ,p_effective_date => p_effective_date
954 ,p_treeele_num => l_treeele_num
955 ,p_ler_id => p_ler_id
956 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
957 ,p_business_group_id => p_business_group_id
958 ,p_new_effective_date => l_new_effective_date
959 ,p_effective_date_1 => l_effective_date
960 );
961 --Bug 13257261 changes End
962
963 -- Set comp object tree row record
964 --
965 l_comp_obj_tree_row := p_comp_obj_tree(l_treeele_num);
966 --
967 l_pgm_id := p_comp_obj_tree(l_treeele_num).pgm_id;
968 l_ptip_id := p_comp_obj_tree(l_treeele_num).ptip_id;
969 l_plip_id := p_comp_obj_tree(l_treeele_num).plip_id;
970 l_pl_id := p_comp_obj_tree(l_treeele_num).pl_id;
971 l_oipl_id := p_comp_obj_tree(l_treeele_num).oipl_id;
972 l_pl_nip := p_comp_obj_tree(l_treeele_num).pl_nip;
973 --
974 -- Initialise parent eligibility flags
975 --
976 if l_pgm_id is not null then
977 hr_utility.set_location ('PGM Chk '||l_package,10);
978 --
979 l_failed_rule:=false;
980 -- PB : 5422 : if p_popl_enrt_typ_cycl_id is not null then
981 if p_lf_evt_ocrd_dt is not null then
982 for l_person_selection in c_schedd_enrt_rl_pgm loop
983 if l_person_selection.formula_id is not null then
984 if check_selection_rule
985 (p_person_selection_rule_id =>l_person_selection.formula_id,
986 p_person_id =>p_person_id,
987 p_business_group_id =>p_business_group_id,
988 p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
989 hr_utility.set_location('Rule passed for pgm',15);
990 else
991 l_failed_rule:=true;
992 exit;
993 end if;
994 end if;
995 end loop;
996 elsif p_ler_id is not null then
997 for l_person_selection in c_lee_rsn_rl_pgm loop
998 if l_person_selection.formula_id is not null then
999 if check_selection_rule
1000 (p_person_selection_rule_id =>l_person_selection.formula_id,
1001 p_person_id =>p_person_id,
1002 p_business_group_id =>p_business_group_id,
1003 p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
1004 hr_utility.set_location('Rule passed for pgm',15);
1005 else
1006 hr_utility.set_location('Rule failed for pgm',15);
1007 l_failed_rule:=true;
1008 exit;
1009 end if;
1013 if l_failed_rule then
1010 end if;
1011 end loop;
1012 end if;
1014 loop
1015 l_treeele_num:=l_treeele_num+1;
1016 if l_treeele_num>p_maxtreeele_num then
1017 l_treeloop := FALSE;
1018 exit;
1019 end if;
1020 --
1021 -- Check for a program or plan not in program boundary
1022 --
1023 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1024 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y' then
1025 --
1026 -- At next program or plan not in program
1027 -- return to outer loop to see if this one is OK
1028 --
1029 l_boundary := TRUE;
1030 exit;
1031 --
1032 end if;
1033 end loop;
1034 else
1035 hr_utility.set_location ('BENMGLEINPEFPGM: '||l_pgm_id,20);
1036 --
1037 -- Set parent eligibility flag info
1038 --
1039 l_par_elig_state.elig_for_pgm_flag := 'Y';
1040 l_par_elig_state.elig_for_ptip_flag := 'Y';
1044 l_boundary := FALSE;
1041 l_par_elig_state.elig_for_plip_flag := 'Y';
1042 l_par_elig_state.elig_for_pl_flag := 'Y';
1043 --
1045 end if;
1046 --
1047 elsif l_ptip_id is not null then
1048 -- hr_utility.set_location ('PTIP Chk '||l_package,10);
1049 --
1050 l_boundary := FALSE;
1051 --
1052 -- hr_utility.set_location ('BENMGLEINPEFPTIP: '||l_ptip_id,20);
1053 --
1054 -- Check that the PTIP has not been processed. When the PTIP
1055 -- has already been processed navigate to the next comp object.
1056 -- PTIPs can be duplicated in the comp object list when there
1057 -- are two PLIPs within an individual program with the same plan
1058 -- type.
1059 --
1060 if check_dupproc_ptip
1061 (p_ptip_id => l_ptip_id
1062 )
1063 then
1064 --
1065 -- Set parent eligibility flags based on the processed PTIP
1066 -- eligible state.
1067 --
1068 set_parent_elig_flags
1069 (p_comp_obj_tree_row => l_comp_obj_tree_row
1070 ,p_eligible => get_dupproc_ptip_elig
1071 (p_ptip_id => l_ptip_id
1072 )
1073 ,p_treeele_num => l_treeele_num
1074 --
1075 ,p_par_elig_state => l_par_elig_state
1076 );
1077 --
1078 l_treeele_num := l_treeele_num+1;
1079 --
1080 --
1081 -- Check parent comp object eligibility
1082 -- When the program is in-eligible then do not process any sub comp objects
1083 -- navigate to the next ptip.
1084 --
1085 elsif l_par_elig_state.elig_for_pgm_flag = 'N'
1086 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1087 and p_mode <> 'T'
1088 then
1089 --
1090 loop
1091 --
1092 -- Check for a program or plan not in program boundary
1093 --
1094 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1095 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1096 then
1097 --
1098 -- Reset parent eligibility flags based on boundary crossed
1099 --
1100 set_bound_parent_elig_flags
1101 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1102 --
1103 ,p_par_elig_state => l_par_elig_state
1104 );
1105 --
1106 l_boundary := TRUE;
1107 exit;
1108 --
1109 --
1110 -- Check for a PTIP in the same program
1111 -- with track in-eligibility set
1112 --
1113 elsif p_comp_obj_tree(l_treeele_num).ptip_id is not null
1114 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1115 then
1116 --
1117 -- Check for a duplicate ptip being processed
1118 --
1119 if check_dupproc_ptip
1120 (p_ptip_id => p_comp_obj_tree(l_treeele_num).ptip_id
1121 )
1122 then
1123 --
1124 -- Set parent eligibility flags based on the processed PTIP
1125 -- eligible state.
1126 --
1127 set_parent_elig_flags
1128 (p_comp_obj_tree_row => l_comp_obj_tree_row
1129 ,p_eligible => get_dupproc_ptip_elig
1133 --
1130 (p_ptip_id => p_comp_obj_tree(l_treeele_num).ptip_id
1131 )
1132 ,p_treeele_num => l_treeele_num
1134 ,p_par_elig_state => l_par_elig_state
1135 );
1136 --
1137 l_treeele_num := l_treeele_num+1;
1138 --
1139 else
1140 --
1141 exit;
1142 --
1143 end if;
1144 --
1145 -- Check if the last row of the comp object list has been reached
1146 -- otherwise navigate to the next row
1147 --
1148 elsif l_treeele_num = p_maxtreeele_num then
1149 --
1150 l_treeloop := FALSE;
1151 exit;
1152 --
1153 elsif check_prevelig_compobj
1154 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1155 ,p_business_group_id => p_business_group_id
1156 ,p_person_id => p_person_id
1157 ,p_effective_date => l_new_effective_date -- 12715758 changed p_effective_date to l_new_effective_date
1158 )
1159 then
1160 --
1161 -- Check if the person was previously eligible for the comp object
1162 --
1163 -- BUG 5062.
1164 --
1165 if p_comp_obj_tree(l_treeele_num).ptip_id is not null then
1166 --
1167 -- We have filter from a PTIP to a PTIP so we need to
1168 -- make sure the PTIP has been cached
1169 --
1170 if check_dupproc_ptip
1171 (p_ptip_id => l_ptip_id) then
1172 --
1173 null;
1174 --
1175 end if;
1176 --
1177 end if;
1178 -- bug # 5929587
1179 -- At this point, the parent ptip is not eligible. So, the elig_for_ptip_flag should be set to N,
1180 -- if the current comp object is a child of ptip, before exiting
1181
1182 if p_comp_obj_tree(l_treeele_num).plip_id is not null or
1183 p_comp_obj_tree(l_treeele_num).pl_id is not null or
1184 p_comp_obj_tree(l_treeele_num).oipl_id is not null then
1185
1186 -- hr_utility.set_location ('Setting elig_for_ptip_flag to N before exiting ',70);
1187 -- l_par_elig_state.elig_for_ptip_flag := 'N';
1188
1189 --Bug 13257261 setting parent compensation object's elig flags to 'N'
1190 if p_comp_obj_tree(l_treeele_num).plip_id is not null
1191 then
1192 hr_utility.set_location ('Setting elig_for_ptip_flag to N before exiting ',70);
1193 l_par_elig_state.elig_for_ptip_flag := 'N';
1194 end if;
1195
1196 if p_comp_obj_tree(l_treeele_num).pl_id is not null then
1197 hr_utility.set_location ('Setting ptip and plip to N before exiting ',70);
1198 l_par_elig_state.elig_for_ptip_flag := 'N';
1199 l_par_elig_state.elig_for_plip_flag := 'N';
1200 end if;
1201
1202 if p_comp_obj_tree(l_treeele_num).oipl_id is not null then
1203 hr_utility.set_location ('Setting ptip and plip and pl to N before exiting ',70);
1204 l_par_elig_state.elig_for_ptip_flag := 'N';
1205 l_par_elig_state.elig_for_plip_flag := 'N';
1206 l_par_elig_state.elig_for_pl_flag := 'N';
1207 end if;
1208
1209 --Bug 13257261
1210 end if;
1211 -- bug # 5929587
1212 --
1213 exit;
1214 --
1215 else
1216 --
1217 --------------------------------------------------------------------------------
1218 --Bug 12732878 ud changes start
1219 --If the Compensation object is first ineligible or still ineligible then inserting the
1220 --comp object into globle variables to check whether any future records are there in
1221 -- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
1222 if p_comp_obj_tree(l_treeele_num).oipl_id is null then
1223 hr_utility.set_location('p_comp_obj_tree(l_treeele_num).ptip_id'||p_comp_obj_tree(l_treeele_num).ptip_id,10);
1224 hr_utility.set_location('p_comp_obj_tree(l_treeele_num).plip_id'||p_comp_obj_tree(l_treeele_num).plip_id,10);
1225 hr_utility.set_location('p_comp_obj_tree(l_treeele_num).pl_id'||p_comp_obj_tree(l_treeele_num).plip_id,10);
1226 hr_utility.set_location('p_comp_obj_tree(l_treeele_num).oipl_id'||p_comp_obj_tree(l_treeele_num).oipl_id,10);
1227
1228 ben_determine_eligibility2.g_cache_first_inelig_object.extend;
1229 ben_determine_eligibility2.g_cache_first_inelig_object
1230 (ben_determine_eligibility2.g_cache_first_inelig_object.last).rowno :=
1231 ben_determine_eligibility2.g_cache_first_inelig_object.last;
1232
1233 if p_comp_obj_tree(l_treeele_num).ptip_id is not null then
1234 ben_determine_eligibility2.g_cache_first_inelig_object
1238 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1235 (ben_determine_eligibility2.g_cache_first_inelig_object.last).ptip_id :=
1236 p_comp_obj_tree(l_treeele_num).ptip_id;
1237 ben_determine_eligibility2.g_cache_first_inelig_object
1239 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1240
1241 elsif p_comp_obj_tree(l_treeele_num).plip_id is not null then
1242 ben_determine_eligibility2.g_cache_first_inelig_object
1243 (ben_determine_eligibility2.g_cache_first_inelig_object.last).plip_id :=
1244 p_comp_obj_tree(l_treeele_num).plip_id;
1245 ben_determine_eligibility2.g_cache_first_inelig_object
1246 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1247 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1248
1249 elsif p_comp_obj_tree(l_treeele_num).pl_id is not null then
1250 ben_determine_eligibility2.g_cache_first_inelig_object
1251 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pl_id :=
1252 p_comp_obj_tree(l_treeele_num).pl_id;
1253 ben_determine_eligibility2.g_cache_first_inelig_object
1254 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1255 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1256
1257 end if;
1258 end if;
1259 --Bug 12732878 ud changes end
1260 ----------------------------------------------------------------------------------
1261 l_treeele_num := l_treeele_num+1;
1262
1263 --Bug 13257261
1264
1265 get_new_date
1266 (p_comp_obj_tree => p_comp_obj_tree
1267 ,p_mode => p_mode
1268 ,p_person_id => p_person_id
1269 ,p_effective_date => p_effective_date
1270 ,p_treeele_num => l_treeele_num
1271 ,p_ler_id => p_ler_id
1272 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1273 ,p_business_group_id => p_business_group_id
1274 ,p_new_effective_date => l_new_effective_date
1275 ,p_effective_date_1 => l_effective_date
1276 );
1277
1278 --Bug 13257261
1279 --
1280 end if;
1281 --
1282 end loop;
1283 --
1284 elsif l_par_elig_state.elig_for_pgm_flag = 'Y' then
1285 --
1286 -- Reset parent eligibility flags
1287 --
1288 l_par_elig_state.elig_for_ptip_flag := 'Y';
1289 l_par_elig_state.elig_for_plip_flag := 'Y';
1290 l_par_elig_state.elig_for_pl_flag := 'Y';
1291 --
1292 end if;
1293 --
1294 elsif l_plip_id is not null then
1295 -- hr_utility.set_location ('PLIP Chk '||l_package,10);
1296 --
1297 l_boundary := FALSE;
1298 --
1299 -- Check parent comp object eligibility
1300 -- When the ptip is in-eligible then do not process any sub comp objects
1301 -- navigate to the next plip.
1302 --
1303 if l_par_elig_state.elig_for_ptip_flag = 'N'
1304 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1305 and p_mode <> 'T'
1306 then
1307 --
1308 loop
1309 --
1310 -- Check for a program or plan not in program boundary
1311 --
1312 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1313 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1314 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
1315 then
1316 --
1317 -- Reset parent eligibility flags based on boundary crossed
1318 --
1319 set_bound_parent_elig_flags
1320 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1321 --
1322 ,p_par_elig_state => l_par_elig_state
1323 );
1324 --
1325 l_boundary := TRUE;
1326 exit;
1327 --
1328 --
1329 -- Check for a plip with track in-eligibility set
1330 --
1331 elsif p_comp_obj_tree(l_treeele_num).plip_id is not null
1332 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1333 then
1334 --
1335 exit;
1336 --
1337 -- Check if the last row of the comp object list has been reached
1338 -- otherwise navigate to the next row
1339 --
1340 elsif l_treeele_num = p_maxtreeele_num then
1341 --
1342 l_treeloop := FALSE;
1343 exit;
1344 --
1345 elsif check_prevelig_compobj
1346 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1347 ,p_business_group_id => p_business_group_id
1348 ,p_person_id => p_person_id
1349 ,p_effective_date => l_new_effective_date -- 12715758 changed p_effective_date to l_new_effective_date
1350 )
1351 then
1352 --
1353 -- Check if the person was previously eligible for the comp object
1354 --
1355
1356 -- bug # 5929587
1357 -- At this point, the parent plip is not eligible. So, the elig_for_plip_flag should be set to N,
1358 -- if at point of exit, the comp object is a child of plip
1359 if p_comp_obj_tree(l_treeele_num).pl_id is not null or
1363 -- l_par_elig_state.elig_for_plip_flag := 'N';
1360 p_comp_obj_tree(l_treeele_num).oipl_id is not null then
1361
1362 -- hr_utility.set_location ('Setting elig_for_plip_flag to N before exiting ',70);
1364 --
1365
1366 --Bug 13257261 setting parent compensation object's elig flags to 'N'
1367 if p_comp_obj_tree(l_treeele_num).pl_id is not null then
1368 hr_utility.set_location ('Setting elig_for_plip_flag to N before exiting ',70);
1369 l_par_elig_state.elig_for_plip_flag := 'N';
1370 end if;
1371
1372 if p_comp_obj_tree(l_treeele_num).oipl_id is not null then
1373 hr_utility.set_location ('Setting plip and pl to N before exiting ',70);
1374 l_par_elig_state.elig_for_plip_flag := 'N';
1375 l_par_elig_state.elig_for_pl_flag := 'N';
1376 end if;
1377
1378 --Bug 13257261
1379
1380 end if;
1381 -- bug # 5929587
1382
1383 exit;
1384 --
1385 else
1386 --
1387 --------------------------------------------------------------------------------
1388 --Bug 12732878 ud changes start
1389 --If the Compensation object is first ineligible or still ineligible then inserting the
1390 --comp object into globle variables to check whether any future records are there in
1391 -- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
1392 if p_comp_obj_tree(l_treeele_num).oipl_id is null then
1393 ben_determine_eligibility2.g_cache_first_inelig_object.extend;
1394 ben_determine_eligibility2.g_cache_first_inelig_object
1395 (ben_determine_eligibility2.g_cache_first_inelig_object.last).rowno :=
1396 ben_determine_eligibility2.g_cache_first_inelig_object.last;
1397
1398 if p_comp_obj_tree(l_treeele_num).plip_id is not null then
1399 ben_determine_eligibility2.g_cache_first_inelig_object
1400 (ben_determine_eligibility2.g_cache_first_inelig_object.last).plip_id :=
1401 p_comp_obj_tree(l_treeele_num).plip_id;
1402 ben_determine_eligibility2.g_cache_first_inelig_object
1403 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1404 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1405
1406 elsif p_comp_obj_tree(l_treeele_num).pl_id is not null then
1407 ben_determine_eligibility2.g_cache_first_inelig_object
1408 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pl_id :=
1409 p_comp_obj_tree(l_treeele_num).pl_id;
1410 ben_determine_eligibility2.g_cache_first_inelig_object
1411 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1412 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1413
1414 end if;
1415 end if;
1416 --Bug 12732878 ud changes end
1417 ----------------------------------------------------------------------------------
1418 l_treeele_num := l_treeele_num+1;
1419 --
1420 end if;
1421 --
1422 end loop;
1423 --
1424 elsif l_par_elig_state.elig_for_ptip_flag = 'Y' then
1425 --
1426 -- Reset parent eligibility flags
1427 --
1428 l_par_elig_state.elig_for_plip_flag := 'Y';
1429 l_par_elig_state.elig_for_pl_flag := 'Y';
1430 --
1431 end if;
1432 --
1433 elsif l_pl_id is not null then
1434 -- hr_utility.set_location ('PLN Chk '||l_package,10);
1435 --
1436 l_boundary := FALSE;
1437 --
1438 if l_pl_nip = 'Y' then
1439 --
1440 l_failed_rule:=false;
1441 --PB :5422 : if p_popl_enrt_typ_cycl_id is not null then
1442 if p_lf_evt_ocrd_dt is not null then
1443 for l_person_selection in c_schedd_enrt_rl_plnip loop
1444 if l_person_selection.formula_id is not null then
1445 if check_selection_rule
1446 (p_person_selection_rule_id =>l_person_selection.formula_id,
1447 p_person_id =>p_person_id,
1448 p_business_group_id =>p_business_group_id,
1449 p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
1450 hr_utility.set_location('Rule passed for pgm',15);
1451 else
1452 l_failed_rule:=true;
1453 exit;
1454 end if;
1455 end if;
1456 end loop;
1457 elsif p_ler_id is not null then
1458 for l_person_selection in c_lee_rsn_rl_plnip loop
1459 if l_person_selection.formula_id is not null then
1460 if check_selection_rule
1461 (p_person_selection_rule_id =>l_person_selection.formula_id,
1462 p_person_id =>p_person_id,
1463 p_business_group_id =>p_business_group_id,
1464 p_effective_date =>l_effective_date) then -- 12715758 changed p_effective_date to l_effective_date
1465 hr_utility.set_location('Rule passed for pgm',15);
1466 else
1467 hr_utility.set_location('Rule failed for pgm',15);
1468 l_failed_rule:=true;
1469 exit;
1470 end if;
1471 end if;
1472 end loop;
1473 end if;
1474 if l_failed_rule then
1475 loop
1476 l_treeele_num:=l_treeele_num+1;
1477 if l_treeele_num>p_maxtreeele_num then
1481 --
1478 l_treeloop := FALSE;
1479 exit;
1480 end if;
1482 -- Check for a program or plan not in program boundary
1483 --
1484 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1485 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y' then
1486 --
1487 -- At next program or plan not in program
1488 -- return to outer loop to see if this one is OK
1489 --
1490 l_boundary := TRUE;
1491 exit;
1492 --
1493 end if;
1494 end loop;
1495 else
1496 l_par_elig_state.elig_for_pgm_flag := 'Y';
1497 l_par_elig_state.elig_for_ptip_flag := 'Y';
1498 l_par_elig_state.elig_for_plip_flag := 'Y';
1499 end if;
1500 --
1501 else
1502 --
1503 -- Check parent comp object eligibility
1504 -- When the plip is in-eligible then do not process any sub comp objects
1505 -- navigate to the next pln.
1506 --
1507 if nvl(l_par_elig_state.elig_for_plip_flag,'N') = 'N' --Bug 13257261
1508 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1509 and p_mode <> 'T'
1510 then
1511 --
1512 loop
1513 --
1514 -- Check for a boundary
1515 --
1516 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1517 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1518 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
1519 or p_comp_obj_tree(l_treeele_num).plip_id is not null
1520 then
1521 --
1522 -- Reset parent eligibility flags based on boundary crossed
1523 --
1524 set_bound_parent_elig_flags
1525 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1526 --
1527 ,p_par_elig_state => l_par_elig_state
1528 );
1529 --
1530 l_boundary := TRUE;
1531 exit;
1532 --
1533 --
1534 -- Check for a pln with track in-eligibility set
1535 --
1536 elsif p_comp_obj_tree(l_treeele_num).pl_id is not null
1537 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1538 then
1539 --
1540 exit;
1541 --
1542 -- Check if the last row of the comp object list has been reached
1543 -- otherwise navigate to the next row
1544 --
1545 elsif l_treeele_num = p_maxtreeele_num then
1546 --
1547 --l_treeloop := FALSE;--4522811
1548
1549 l_boundary := FALSE; --4522811
1550 exit;
1551 --
1552 elsif check_prevelig_compobj
1553 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1554 ,p_business_group_id => p_business_group_id
1555 ,p_person_id => p_person_id
1556 ,p_effective_date => l_new_effective_date -- 12715758 changed p_effective_date to l_new_effective_date
1557 )
1558 then
1559 --
1560 -- Check if the person was previously eligible for the comp object
1561 --
1562
1563 -- bug # 5929587
1564 -- At this point, the parent pl is not eligible. So, the elig_for_pl_flag should be set to N,
1565 -- if at point of exit, the comp object is a child of pl
1566
1567 if p_comp_obj_tree(l_treeele_num).oipl_id is not null then
1568 hr_utility.set_location ('Setting elig_for_pl_flag to N before exiting ',70);
1569 l_par_elig_state.elig_for_pl_flag := 'N';
1570 end if;
1571 -- bug # 5929587
1572
1573 exit;
1574 --
1575 else
1576 --------------------------------------------------------------------------------
1577 --Bug 12732878 ud changes start
1578 --If the Compensation object is first ineligible or still ineligible then inserting the
1579 --comp object into globle variables to check whether any future records are there in
1580 -- ben_elig_per_f and ben_elig_opt_f tables of the same compensation object.
1581 if p_comp_obj_tree(l_treeele_num).oipl_id is null then
1582 ben_determine_eligibility2.g_cache_first_inelig_object.extend;
1583 ben_determine_eligibility2.g_cache_first_inelig_object
1584 (ben_determine_eligibility2.g_cache_first_inelig_object.last).rowno :=
1585 ben_determine_eligibility2.g_cache_first_inelig_object.last;
1586
1587 if p_comp_obj_tree(l_treeele_num).pl_id is not null then
1588 ben_determine_eligibility2.g_cache_first_inelig_object
1589 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pl_id :=
1590 p_comp_obj_tree(l_treeele_num).pl_id;
1591 ben_determine_eligibility2.g_cache_first_inelig_object
1592 (ben_determine_eligibility2.g_cache_first_inelig_object.last).pgm_id :=
1593 p_comp_obj_tree(l_treeele_num).par_pgm_id;
1594
1595 end if;
1596 end if;
1597 --Bug 12732878 ud changes end
1598 ----------------------------------------------------------------------------------
1599 --
1600 l_treeele_num := l_treeele_num+1;
1601 --
1602 end if;
1603 --
1604 end loop;
1605 --
1606 elsif l_par_elig_state.elig_for_plip_flag = 'Y' then
1607 --
1608 -- Reset parent eligibility flags
1612 end if;
1609 --
1610 l_par_elig_state.elig_for_pl_flag := 'Y';
1611 --
1613 --
1614 end if;
1615 --
1616 elsif l_oipl_id is not null then
1617 -- hr_utility.set_location ('OIPL Chk '||l_package,10);
1618 --
1619 l_boundary := FALSE;
1620 --
1621 -- Check parent comp object eligibility
1622 -- When the pln is in-eligible then do not process any oipls
1623 -- navigate to the next pln.
1624 --
1625 if l_par_elig_state.elig_for_pl_flag = 'N'
1626 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1627 and p_mode <> 'T'
1628 then
1629 --
1630 loop
1631 --
1632 -- Check for a program or plan not in program boundary
1633 --
1634 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1635 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1636 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
1637 or p_comp_obj_tree(l_treeele_num).plip_id is not null
1638 or p_comp_obj_tree(l_treeele_num).pl_id is not null
1639 then
1640 --
1641 -- Reset parent eligibility flags based on boundary crossed
1642 --
1643 set_bound_parent_elig_flags
1644 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1645 --
1646 ,p_par_elig_state => l_par_elig_state
1647 );
1648 --
1649 l_boundary := TRUE;
1650 exit;
1651 --
1652 --
1653 -- Check for a oipl with track in-eligibility set
1654 --
1655 elsif p_comp_obj_tree(l_treeele_num).oipl_id is not null
1656 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1657 then
1658 --
1659 exit;
1660 --
1661 -- Check if the last row of the comp object list has been reached
1662 -- otherwise navigate to the next row
1663 --
1664 elsif l_treeele_num = p_maxtreeele_num then
1665 --
1666 --l_treeloop := FALSE;
1667 l_boundary := FALSE; --4522811
1668
1669 exit;
1670 --
1671 elsif check_prevelig_compobj
1672 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1673 ,p_business_group_id => p_business_group_id
1674 ,p_person_id => p_person_id
1675 ,p_effective_date => l_new_effective_date -- 12715758 changed p_effective_date to l_new_effective_date
1676 )
1677 then
1678 --
1679 -- Check if the person was previously eligible for the comp object
1680 --
1681 exit;
1682 --
1683 else
1684 --
1685 l_treeele_num := l_treeele_num+1;
1686 --
1687 end if;
1688 --
1689 end loop;
1690 --
1691 end if;
1692 --
1693 end if;
1694 --
1695 -- Exit the boundary loop if the boundary was not crossed
1696 --
1697 if not l_boundary then
1698 --
1699 exit;
1700 --
1701 end if;
1702 --
1703 end loop; -- boundary loop
1704 --
1705 hr_utility.set_location ('Leaving '||l_package,10);
1706 --
1707 p_par_elig_state := l_par_elig_state;
1708 p_treeele_num := l_treeele_num;
1709 p_treeloop := l_treeloop;
1710 --
1711 end filter_comp_objects;
1712 --
1713 end ben_comp_obj_filter;
[Home] [Help]