1 package body ben_comp_obj_filter as
2 /* $Header: bebmfilt.pkb 120.6 2006/07/04 07:13:02 swjain noship $ */
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
21 )
22 return boolean
23 is
24 --
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 --
309 end set_parent_elig_flags;
310 --
311 procedure set_bound_parent_elig_flags
312 (p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
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 );
403 --
404 if nvl(l_inst_row.elig_flag,'N') = 'Y'
405 then
406 --
407 l_prevelig := TRUE;
408 --
409 end if;
410 --
411 -- hr_utility.set_location ('Dn PTIP PILPEP ' || l_package,10);
412 --
413 elsif p_comp_obj_tree_row.plip_id is not null then
414 --
415 -- hr_utility.set_location ('PLIP PILPEP ' || l_package,10);
416 --
417 ben_pep_cache.get_pilpep_dets
418 (p_person_id => p_person_id
419 ,p_business_group_id => p_business_group_id
420 ,p_effective_date => p_effective_date
421 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
422 ,p_plip_id => p_comp_obj_tree_row.plip_id
423 ,p_inst_row => l_inst_row
424 );
425 --
426 if nvl(l_inst_row.elig_flag,'N') = 'Y'
427 then
428 --
429 l_prevelig := TRUE;
430 --
431 end if;
432 --
433 -- hr_utility.set_location ('Dn PLIP PILPEP ' || l_package,10);
434 --
435 elsif p_comp_obj_tree_row.pl_id is not null
436 then
437 --
438 -- hr_utility.set_location ('PL PILPEP ' || l_package,10);
439 --
440 ben_pep_cache.get_pilpep_dets
441 (p_person_id => p_person_id
442 ,p_business_group_id => p_business_group_id
443 ,p_effective_date => p_effective_date
444 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
445 ,p_pl_id => p_comp_obj_tree_row.pl_id
446 ,p_inst_row => l_inst_row
447 );
448 --
449 if nvl(l_inst_row.elig_flag,'N') = 'Y'
450 then
451 --
452 l_prevelig := TRUE;
453 --
454 end if;
455 --
456 -- hr_utility.set_location ('Dn PL PILPEP ' || l_package,10);
457 --
458 elsif p_comp_obj_tree_row.oipl_id is not null then
459 --
460 -- hr_utility.set_location ('OIPL PILPEP ' || l_package,10);
461 --
462 ben_pep_cache.get_pilepo_dets
463 (p_person_id => p_person_id
464 ,p_business_group_id => p_business_group_id
465 ,p_effective_date => p_effective_date
466 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
467 ,p_pl_id => p_comp_obj_tree_row.par_pl_id
468 ,p_opt_id => p_comp_obj_tree_row.par_opt_id
469 ,p_inst_row => l_inst_row
470 );
471 --
472 if nvl(l_inst_row.elig_flag,'N') = 'Y'
473 then
474 --
475 l_prevelig := TRUE;
476 --
477 end if;
478 --
482 --
479 -- hr_utility.set_location ('Dn OIPL PILPEP ' || l_package,10);
480 --
481 end if;
483 return l_prevelig;
484 --
485 end check_prevelig_compobj;
486 --
487 function check_selection_rule
488 (p_person_selection_rule_id in number,
489 p_person_id in number,
490 p_business_group_id in number,
491 p_effective_date in date) return boolean is
492 --
493 l_outputs ff_exec.outputs_t;
494 l_assignment_id number;
495 l_package varchar2(80) := g_package||'.check_selection_rule';
496 --
497 begin
498 --
499 if p_person_selection_rule_id is null then
500 --
501 return true;
502 --
503 else
504 --
505 l_assignment_id := benutils.get_assignment_id
506 (p_person_id => p_person_id,
507 p_business_group_id => p_business_group_id,
508 p_effective_date => p_effective_date);
509 --
510 l_outputs := benutils.formula
511 (p_formula_id => p_person_selection_rule_id,
512 p_effective_date => p_effective_date,
513 p_business_group_id => p_business_group_id,
514 p_assignment_id => l_assignment_id,
515 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
516 p_param1_value => to_char(p_person_id));
517 --
518 if l_outputs(l_outputs.first).value = 'Y' then
519 --
520 return true;
521 --
522 elsif l_outputs(l_outputs.first).value = 'N' then
523 --
524 return false;
525 --
526 elsif l_outputs(l_outputs.first).value <> 'N' then
527 --
528 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
529 fnd_message.set_token('RL','person_selection_rule_id');
530 fnd_message.set_token('PROC',l_package);
531 raise ben_manage_life_events.g_record_error;
532 --
533 end if;
534 --
535 end if;
536 end check_selection_rule;
537 --
538 procedure filter_comp_objects
539 (p_comp_obj_tree in ben_manage_life_events.g_cache_proc_object_table
540 ,p_mode in varchar
541 ,p_person_id in number
542 ,p_effective_date in date
543 ,p_maxtreeele_num in pls_integer
544 --
545 ,p_par_elig_state in out nocopy ben_comp_obj_filter.g_par_elig_state_rec
546 ,p_treeele_num in out nocopy pls_integer
547 --
548 ,p_treeloop out nocopy boolean
549 ,p_ler_id in number default null
550 ,p_lf_evt_ocrd_dt in date default null
551 -- ,p_popl_enrt_typ_cycl_id in number default null
552 ,p_business_group_id in number default null
553 )
554 is
555 --
556 l_package varchar2(80) := g_package||'filter_comp_objects';
557 --
558 l_comp_obj_tree ben_manage_life_events.g_cache_proc_object_table;
559 l_comp_obj_tree_row ben_manage_life_events.g_cache_proc_objects_rec;
560 l_par_elig_state ben_comp_obj_filter.g_par_elig_state_rec;
561 --
562 l_boundary boolean;
563 l_treeloop boolean;
564 --
565 l_pgm_id number;
566 l_ptip_id number;
567 l_plip_id number;
568 l_pl_id number;
569 l_oipl_id number;
570 l_pl_nip varchar2(30);
571 --
572 l_treeele_num pls_integer;
573 --
574 l_failed_rule boolean;
575 --
576 cursor c_lee_rsn_rl_pgm is
577 select formula_id
578 FROM ben_lee_rsn_f leer,
579 ben_popl_enrt_typ_cycl_f petc,
580 ben_lee_rsn_rl_f lrr
581 WHERE leer.ler_id = p_ler_id
582 AND p_effective_date BETWEEN leer.effective_start_date
583 AND leer.effective_end_date
584 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
585 AND petc.pgm_id = l_pgm_id
586 AND petc.enrt_typ_cycl_cd = 'L'
587 AND p_effective_date BETWEEN petc.effective_start_date
588 AND petc.effective_end_date
589 and p_effective_date between
590 lrr.effective_start_date and lrr.effective_end_date
591 and lrr.lee_rsn_id=leer.lee_rsn_id
592 and lrr.business_group_id=leer.business_group_id
593 order by ordr_to_aply_num;
594 --
595 -- bug - 4644355 - changed pgm_id to pl_id below
596 --
597 cursor c_lee_rsn_rl_plnip is
598 select formula_id
599 FROM ben_lee_rsn_f leer,
600 ben_popl_enrt_typ_cycl_f petc,
601 ben_lee_rsn_rl_f lrr
602 WHERE leer.ler_id = p_ler_id
603 AND p_effective_date BETWEEN leer.effective_start_date
604 AND leer.effective_end_date
605 AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
606 AND petc.pl_id = l_pl_id
607 AND petc.enrt_typ_cycl_cd = 'L'
608 AND p_effective_date BETWEEN petc.effective_start_date
609 AND petc.effective_end_date
610 and p_effective_date between
611 lrr.effective_start_date and lrr.effective_end_date
612 and lrr.lee_rsn_id=leer.lee_rsn_id
613 and lrr.business_group_id=leer.business_group_id
617 select formula_id
614 order by ordr_to_aply_num;
615 --
616 cursor c_schedd_enrt_rl_pgm is
618 from ben_schedd_enrt_rl_f lrr,
619 ben_popl_enrt_typ_cycl_f pet,
620 ben_enrt_perd enp
621 where pet.pgm_id = l_pgm_id
622 and p_effective_date
623 between pet.effective_start_date
624 and pet.effective_end_date
625 and pet.popl_enrt_typ_cycl_id =
626 enp.popl_enrt_typ_cycl_id
627 -- PB : 5422 :
628 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
629 /* 5422 : PB :and enp.strt_dt = (select enp1.strt_dt
630 from ben_enrt_perd enp1
631 where enp1.enrt_perd_id
632 = p_popl_enrt_typ_cycl_id) */
633 and enp.business_group_id =
634 pet.business_group_id
635 and p_effective_date between
636 lrr.effective_start_date and lrr.effective_end_date
637 and lrr.enrt_perd_id=enp.enrt_perd_id
638 and lrr.business_group_id=enp.business_group_id
639 order by ordr_to_aply_num;
640 --
641 cursor c_schedd_enrt_rl_plnip is
642 select formula_id
643 from ben_schedd_enrt_rl_f lrr,
644 ben_popl_enrt_typ_cycl_f pet,
645 ben_enrt_perd enp
646 -- CWB changes
647 where pet.pl_id = l_pl_id
648 and p_effective_date
649 between pet.effective_start_date
650 and pet.effective_end_date
651 and pet.popl_enrt_typ_cycl_id =
652 enp.popl_enrt_typ_cycl_id
653 -- PB : 5422 :
654 and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
655 /* and enp.strt_dt = (select enp1.strt_dt
656 from ben_enrt_perd enp1
657 where enp1.enrt_perd_id
658 = p_popl_enrt_typ_cycl_id) */
659 and enp.business_group_id =
660 pet.business_group_id
661 and p_effective_date between
662 lrr.effective_start_date and lrr.effective_end_date
663 and lrr.enrt_perd_id=enp.enrt_perd_id
664 and lrr.business_group_id=enp.business_group_id
665 order by ordr_to_aply_num;
666 --
667 begin
668 --
669 hr_utility.set_location ('Entering '||l_package,10);
670 --
671 l_par_elig_state := p_par_elig_state;
672 l_treeele_num := p_treeele_num;
673 --
674 l_boundary := FALSE;
675 --
676 -- Comp object navigation loop. Only loop if a boundary is hit
677 --
678 loop
679 hr_utility.set_location ('St CO Tree Loop '||l_package,10);
680 --
681 -- Set comp object tree row record
682 --
683 l_comp_obj_tree_row := p_comp_obj_tree(l_treeele_num);
684 --
685 l_pgm_id := p_comp_obj_tree(l_treeele_num).pgm_id;
686 l_ptip_id := p_comp_obj_tree(l_treeele_num).ptip_id;
687 l_plip_id := p_comp_obj_tree(l_treeele_num).plip_id;
688 l_pl_id := p_comp_obj_tree(l_treeele_num).pl_id;
689 l_oipl_id := p_comp_obj_tree(l_treeele_num).oipl_id;
690 l_pl_nip := p_comp_obj_tree(l_treeele_num).pl_nip;
691 --
692 -- Initialise parent eligibility flags
693 --
694 if l_pgm_id is not null then
695 hr_utility.set_location ('PGM Chk '||l_package,10);
696 --
697 l_failed_rule:=false;
698 -- PB : 5422 : if p_popl_enrt_typ_cycl_id is not null then
699 if p_lf_evt_ocrd_dt is not null then
700 for l_person_selection in c_schedd_enrt_rl_pgm loop
701 if l_person_selection.formula_id is not null then
702 if check_selection_rule
703 (p_person_selection_rule_id =>l_person_selection.formula_id,
704 p_person_id =>p_person_id,
705 p_business_group_id =>p_business_group_id,
706 p_effective_date =>p_effective_date) then
707 hr_utility.set_location('Rule passed for pgm',15);
708 else
709 l_failed_rule:=true;
710 exit;
711 end if;
712 end if;
713 end loop;
714 elsif p_ler_id is not null then
715 for l_person_selection in c_lee_rsn_rl_pgm loop
716 if l_person_selection.formula_id is not null then
717 if check_selection_rule
718 (p_person_selection_rule_id =>l_person_selection.formula_id,
719 p_person_id =>p_person_id,
720 p_business_group_id =>p_business_group_id,
721 p_effective_date =>p_effective_date) then
722 hr_utility.set_location('Rule passed for pgm',15);
723 else
724 hr_utility.set_location('Rule failed for pgm',15);
725 l_failed_rule:=true;
726 exit;
727 end if;
728 end if;
729 end loop;
730 end if;
731 if l_failed_rule then
732 loop
733 l_treeele_num:=l_treeele_num+1;
734 if l_treeele_num>p_maxtreeele_num then
735 l_treeloop := FALSE;
736 exit;
737 end if;
738 --
739 -- Check for a program or plan not in program boundary
740 --
741 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
742 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y' then
743 --
747 l_boundary := TRUE;
744 -- At next program or plan not in program
745 -- return to outer loop to see if this one is OK
746 --
748 exit;
749 --
750 end if;
751 end loop;
752 else
753 hr_utility.set_location ('BENMGLEINPEFPGM: '||l_pgm_id,20);
754 --
755 -- Set parent eligibility flag info
756 --
757 l_par_elig_state.elig_for_pgm_flag := 'Y';
758 l_par_elig_state.elig_for_ptip_flag := 'Y';
759 l_par_elig_state.elig_for_plip_flag := 'Y';
760 l_par_elig_state.elig_for_pl_flag := 'Y';
761 --
762 l_boundary := FALSE;
763 end if;
764 --
765 elsif l_ptip_id is not null then
766 -- hr_utility.set_location ('PTIP Chk '||l_package,10);
767 --
768 l_boundary := FALSE;
769 --
770 -- hr_utility.set_location ('BENMGLEINPEFPTIP: '||l_ptip_id,20);
771 --
772 -- Check that the PTIP has not been processed. When the PTIP
773 -- has already been processed navigate to the next comp object.
774 -- PTIPs can be duplicated in the comp object list when there
775 -- are two PLIPs within an individual program with the same plan
776 -- type.
777 --
778 if check_dupproc_ptip
779 (p_ptip_id => l_ptip_id
780 )
781 then
782 --
783 -- Set parent eligibility flags based on the processed PTIP
784 -- eligible state.
785 --
786 set_parent_elig_flags
787 (p_comp_obj_tree_row => l_comp_obj_tree_row
788 ,p_eligible => get_dupproc_ptip_elig
789 (p_ptip_id => l_ptip_id
790 )
791 ,p_treeele_num => l_treeele_num
792 --
793 ,p_par_elig_state => l_par_elig_state
794 );
795 --
796 l_treeele_num := l_treeele_num+1;
797 --
798 --
799 -- Check parent comp object eligibility
800 -- When the program is in-eligible then do not process any sub comp objects
801 -- navigate to the next ptip.
802 --
803 elsif l_par_elig_state.elig_for_pgm_flag = 'N'
804 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
805 and p_mode <> 'T'
806 then
807 --
808 loop
809 --
810 -- Check for a program or plan not in program boundary
811 --
812 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
813 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
814 then
815 --
816 -- Reset parent eligibility flags based on boundary crossed
817 --
818 set_bound_parent_elig_flags
819 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
820 --
821 ,p_par_elig_state => l_par_elig_state
822 );
823 --
824 l_boundary := TRUE;
825 exit;
826 --
827 --
828 -- Check for a PTIP in the same program
829 -- with track in-eligibility set
830 --
831 elsif p_comp_obj_tree(l_treeele_num).ptip_id is not null
832 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
833 then
834 --
835 -- Check for a duplicate ptip being processed
836 --
837 if check_dupproc_ptip
838 (p_ptip_id => p_comp_obj_tree(l_treeele_num).ptip_id
839 )
840 then
841 --
842 -- Set parent eligibility flags based on the processed PTIP
843 -- eligible state.
844 --
845 set_parent_elig_flags
846 (p_comp_obj_tree_row => l_comp_obj_tree_row
847 ,p_eligible => get_dupproc_ptip_elig
848 (p_ptip_id => p_comp_obj_tree(l_treeele_num).ptip_id
849 )
850 ,p_treeele_num => l_treeele_num
851 --
852 ,p_par_elig_state => l_par_elig_state
853 );
854 --
855 l_treeele_num := l_treeele_num+1;
856 --
857 else
858 --
859 exit;
860 --
861 end if;
862 --
863 -- Check if the last row of the comp object list has been reached
864 -- otherwise navigate to the next row
865 --
866 elsif l_treeele_num = p_maxtreeele_num then
867 --
868 l_treeloop := FALSE;
869 exit;
870 --
871 elsif check_prevelig_compobj
872 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
873 ,p_business_group_id => p_business_group_id
874 ,p_person_id => p_person_id
875 ,p_effective_date => p_effective_date
876 )
877 then
878 --
882 --
879 -- Check if the person was previously eligible for the comp object
880 --
881 -- BUG 5062.
883 if p_comp_obj_tree(l_treeele_num).ptip_id is not null then
884 --
885 -- We have filter from a PTIP to a PTIP so we need to
886 -- make sure the PTIP has been cached
887 --
888 if check_dupproc_ptip
889 (p_ptip_id => l_ptip_id) then
890 --
891 null;
892 --
893 end if;
894 --
895 end if;
896 --
897 exit;
898 --
899 else
900 --
901 l_treeele_num := l_treeele_num+1;
902 --
903 end if;
904 --
905 end loop;
906 --
907 elsif l_par_elig_state.elig_for_pgm_flag = 'Y' then
908 --
909 -- Reset parent eligibility flags
910 --
911 l_par_elig_state.elig_for_ptip_flag := 'Y';
912 l_par_elig_state.elig_for_plip_flag := 'Y';
913 l_par_elig_state.elig_for_pl_flag := 'Y';
914 --
915 end if;
916 --
917 elsif l_plip_id is not null then
918 -- hr_utility.set_location ('PLIP Chk '||l_package,10);
919 --
920 l_boundary := FALSE;
921 --
922 -- Check parent comp object eligibility
923 -- When the ptip is in-eligible then do not process any sub comp objects
924 -- navigate to the next plip.
925 --
926 if l_par_elig_state.elig_for_ptip_flag = 'N'
927 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
928 and p_mode <> 'T'
929 then
930 --
931 loop
932 --
933 -- Check for a program or plan not in program boundary
934 --
935 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
936 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
937 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
938 then
939 --
940 -- Reset parent eligibility flags based on boundary crossed
941 --
942 set_bound_parent_elig_flags
943 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
944 --
945 ,p_par_elig_state => l_par_elig_state
946 );
947 --
948 l_boundary := TRUE;
949 exit;
950 --
951 --
952 -- Check for a plip with track in-eligibility set
953 --
954 elsif p_comp_obj_tree(l_treeele_num).plip_id is not null
955 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
956 then
957 --
958 exit;
959 --
960 -- Check if the last row of the comp object list has been reached
961 -- otherwise navigate to the next row
962 --
963 elsif l_treeele_num = p_maxtreeele_num then
964 --
965 l_treeloop := FALSE;
966 exit;
967 --
968 elsif check_prevelig_compobj
969 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
970 ,p_business_group_id => p_business_group_id
971 ,p_person_id => p_person_id
972 ,p_effective_date => p_effective_date
973 )
974 then
975 --
976 -- Check if the person was previously eligible for the comp object
977 --
978 exit;
979 --
980 else
981 --
982 l_treeele_num := l_treeele_num+1;
983 --
984 end if;
985 --
986 end loop;
987 --
988 elsif l_par_elig_state.elig_for_ptip_flag = 'Y' then
989 --
990 -- Reset parent eligibility flags
991 --
992 l_par_elig_state.elig_for_plip_flag := 'Y';
993 l_par_elig_state.elig_for_pl_flag := 'Y';
994 --
995 end if;
996 --
997 elsif l_pl_id is not null then
998 -- hr_utility.set_location ('PLN Chk '||l_package,10);
999 --
1000 l_boundary := FALSE;
1001 --
1002 if l_pl_nip = 'Y' then
1003 --
1004 l_failed_rule:=false;
1005 --PB :5422 : if p_popl_enrt_typ_cycl_id is not null then
1006 if p_lf_evt_ocrd_dt is not null then
1007 for l_person_selection in c_schedd_enrt_rl_plnip loop
1008 if l_person_selection.formula_id is not null then
1009 if check_selection_rule
1010 (p_person_selection_rule_id =>l_person_selection.formula_id,
1011 p_person_id =>p_person_id,
1012 p_business_group_id =>p_business_group_id,
1013 p_effective_date =>p_effective_date) then
1014 hr_utility.set_location('Rule passed for pgm',15);
1015 else
1016 l_failed_rule:=true;
1017 exit;
1021 elsif p_ler_id is not null then
1018 end if;
1019 end if;
1020 end loop;
1022 for l_person_selection in c_lee_rsn_rl_plnip loop
1023 if l_person_selection.formula_id is not null then
1024 if check_selection_rule
1025 (p_person_selection_rule_id =>l_person_selection.formula_id,
1026 p_person_id =>p_person_id,
1027 p_business_group_id =>p_business_group_id,
1028 p_effective_date =>p_effective_date) then
1029 hr_utility.set_location('Rule passed for pgm',15);
1030 else
1031 hr_utility.set_location('Rule failed for pgm',15);
1032 l_failed_rule:=true;
1033 exit;
1034 end if;
1035 end if;
1036 end loop;
1037 end if;
1038 if l_failed_rule then
1039 loop
1040 l_treeele_num:=l_treeele_num+1;
1041 if l_treeele_num>p_maxtreeele_num then
1042 l_treeloop := FALSE;
1043 exit;
1044 end if;
1045 --
1046 -- Check for a program or plan not in program boundary
1047 --
1048 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1049 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y' then
1050 --
1051 -- At next program or plan not in program
1052 -- return to outer loop to see if this one is OK
1053 --
1054 l_boundary := TRUE;
1055 exit;
1056 --
1057 end if;
1058 end loop;
1059 else
1060 l_par_elig_state.elig_for_pgm_flag := 'Y';
1061 l_par_elig_state.elig_for_ptip_flag := 'Y';
1062 l_par_elig_state.elig_for_plip_flag := 'Y';
1063 end if;
1064 --
1065 else
1066 --
1067 -- Check parent comp object eligibility
1068 -- When the plip is in-eligible then do not process any sub comp objects
1069 -- navigate to the next pln.
1070 --
1071 if l_par_elig_state.elig_for_plip_flag = 'N'
1072 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1073 and p_mode <> 'T'
1074 then
1075 --
1076 loop
1077 --
1078 -- Check for a boundary
1079 --
1080 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1081 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1082 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
1083 or p_comp_obj_tree(l_treeele_num).plip_id is not null
1084 then
1085 --
1086 -- Reset parent eligibility flags based on boundary crossed
1087 --
1088 set_bound_parent_elig_flags
1089 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1090 --
1091 ,p_par_elig_state => l_par_elig_state
1092 );
1093 --
1094 l_boundary := TRUE;
1095 exit;
1096 --
1097 --
1098 -- Check for a pln with track in-eligibility set
1099 --
1100 elsif p_comp_obj_tree(l_treeele_num).pl_id is not null
1101 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1102 then
1103 --
1104 exit;
1105 --
1106 -- Check if the last row of the comp object list has been reached
1107 -- otherwise navigate to the next row
1108 --
1109 elsif l_treeele_num = p_maxtreeele_num then
1110 --
1111 --l_treeloop := FALSE;--4522811
1112
1113 l_boundary := FALSE; --4522811
1114 exit;
1115 --
1116 elsif check_prevelig_compobj
1117 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1118 ,p_business_group_id => p_business_group_id
1119 ,p_person_id => p_person_id
1120 ,p_effective_date => p_effective_date
1121 )
1122 then
1123 --
1124 -- Check if the person was previously eligible for the comp object
1125 --
1126 exit;
1127 --
1128 else
1129 --
1130 l_treeele_num := l_treeele_num+1;
1131 --
1132 end if;
1133 --
1134 end loop;
1135 --
1136 elsif l_par_elig_state.elig_for_plip_flag = 'Y' then
1137 --
1138 -- Reset parent eligibility flags
1139 --
1140 l_par_elig_state.elig_for_pl_flag := 'Y';
1141 --
1142 end if;
1143 --
1144 end if;
1145 --
1146 elsif l_oipl_id is not null then
1147 -- hr_utility.set_location ('OIPL Chk '||l_package,10);
1148 --
1149 l_boundary := FALSE;
1150 --
1151 -- Check parent comp object eligibility
1155 if l_par_elig_state.elig_for_pl_flag = 'N'
1152 -- When the pln is in-eligible then do not process any oipls
1153 -- navigate to the next pln.
1154 --
1156 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'N'
1157 and p_mode <> 'T'
1158 then
1159 --
1160 loop
1161 --
1162 -- Check for a program or plan not in program boundary
1163 --
1164 if p_comp_obj_tree(l_treeele_num).pgm_id is not null
1165 or p_comp_obj_tree(l_treeele_num).pl_nip = 'Y'
1166 or p_comp_obj_tree(l_treeele_num).ptip_id is not null
1167 or p_comp_obj_tree(l_treeele_num).plip_id is not null
1168 or p_comp_obj_tree(l_treeele_num).pl_id is not null
1169 then
1170 --
1171 -- Reset parent eligibility flags based on boundary crossed
1172 --
1173 set_bound_parent_elig_flags
1174 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1175 --
1176 ,p_par_elig_state => l_par_elig_state
1177 );
1178 --
1179 l_boundary := TRUE;
1180 exit;
1181 --
1182 --
1183 -- Check for a oipl with track in-eligibility set
1184 --
1185 elsif p_comp_obj_tree(l_treeele_num).oipl_id is not null
1186 and p_comp_obj_tree(l_treeele_num).trk_inelig_per_flag = 'Y'
1187 then
1188 --
1189 exit;
1190 --
1191 -- Check if the last row of the comp object list has been reached
1192 -- otherwise navigate to the next row
1193 --
1194 elsif l_treeele_num = p_maxtreeele_num then
1195 --
1196 --l_treeloop := FALSE;
1197 l_boundary := FALSE; --4522811
1198
1199 exit;
1200 --
1201 elsif check_prevelig_compobj
1202 (p_comp_obj_tree_row => p_comp_obj_tree(l_treeele_num)
1203 ,p_business_group_id => p_business_group_id
1204 ,p_person_id => p_person_id
1205 ,p_effective_date => p_effective_date
1206 )
1207 then
1208 --
1209 -- Check if the person was previously eligible for the comp object
1210 --
1211 exit;
1212 --
1213 else
1214 --
1215 l_treeele_num := l_treeele_num+1;
1216 --
1217 end if;
1218 --
1219 end loop;
1220 --
1221 end if;
1222 --
1223 end if;
1224 --
1225 -- Exit the boundary loop if the boundary was not crossed
1226 --
1227 if not l_boundary then
1228 --
1229 exit;
1230 --
1231 end if;
1232 --
1233 end loop; -- boundary loop
1234 --
1235 hr_utility.set_location ('Leaving '||l_package,10);
1236 --
1237 p_par_elig_state := l_par_elig_state;
1238 p_treeele_num := l_treeele_num;
1239 p_treeloop := l_treeloop;
1240 --
1241 end filter_comp_objects;
1242 --
1243 end ben_comp_obj_filter;
[Home] [Help]