1 package body ben_evaluate_elig_criteria as
2 /* $Header: benelgcr.pkb 120.2 2005/10/26 01:40:56 ssarkar noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Profile Evaluation Package
13 Purpose
14 This package is used to determine if a person satisfies general eligiblity
15 criteria,rate by criteria or not.
16 History
17 Date Who Version What?
18 --------- ----------- ------- --------------------------------------------
19 13 Jan 04 tjesumic 115.0 Original version
20 14 Feb 05 abparekh 115.1 Changed ECV.EXCLUDE_FLAG to ECV.EXCLD_FLAG
21 14 Feb 05 tjesumic 115.2 hierarchy position validation fixed for least entry
22 24 Feb 05 tjesumic 115.3 if one of the value in is_ok is null the boolean return null
23 15 Mar 05 abparekh 115.4 Bug 4234033 : Process non-primary assignments also
24 18 oct 05 ssarkar 115.5 Bug 4586880 :eligibility evaluation for set1 and set2.
25 26-oct-05 ssarkar 115.6 Bug 4695890 : if set2 returns null , then consider eligibilty not satisfied.
26 */
27 --------------------------------------------------------------------------------
28 --
29 g_package varchar2(30) := 'ben_evaluate_elig_criteria.';
30 --
31 l_fonm_cvg_strt_dt date ;
32 g_debug boolean := hr_utility.debug_enabled;
33 --
34 --
35 -- -----------------------------------------------------
36 -- find whther any overide value definded for the criteria
37 -- -----------------------------------------------------
38
39 procedure get_override_value ( p_crit_ovrrd_val_tbl in pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl
40 ,p_short_code in varchar2
41 ,p_data_type_cd1 in out nocopy varchar2
42 ,p_value_char1 out nocopy varchar2
43 ,p_value_num1 out nocopy number
44 ,p_value_date1 out nocopy date
45 ,p_data_type_cd2 in out nocopy varchar2
46 ,p_value_char2 out nocopy varchar2
47 ,p_value_num2 out nocopy number
48 ,p_value_date2 out nocopy date
49 ,p_overide_found out nocopy varchar2
50 ) is
51
52 l_proc varchar2(100):= g_package||'get_override_value';
53 l_dummy_char varchar2(1) ;
54 l_num_val1 number ;
55 l_char_val1 varchar2(15) ;
56 l_date_val1 date ;
57 l_num_val2 number ;
58 l_char_val2 varchar2(15) ;
59 l_date_val2 date ;
60 l_short_code_found varchar2(15) ;
61 begin
62 if g_debug then
63 hr_utility.set_location('Entering: '||l_proc,10);
64 hr_utility.set_location('first: '||p_crit_ovrrd_val_tbl.first ,10);
65 end if ;
66
67 p_overide_found := 'N' ;
68 l_short_code_found := 'N' ;
69 if p_crit_ovrrd_val_tbl.first is not null then
70 for i in p_crit_ovrrd_val_tbl.first .. p_crit_ovrrd_val_tbl.last
71 loop
72 if p_short_code = p_crit_ovrrd_val_tbl(i).criteria_short_code then
73 l_num_val1 := p_crit_ovrrd_val_tbl(i).number_value1 ;
74 l_num_val2 := p_crit_ovrrd_val_tbl(i).number_value2 ;
75 l_char_val1 := p_crit_ovrrd_val_tbl(i).char_value1 ;
76 l_char_val2 := p_crit_ovrrd_val_tbl(i).char_value2 ;
77 l_date_val1 := p_crit_ovrrd_val_tbl(i).date_value1 ;
78 l_date_val2 := p_crit_ovrrd_val_tbl(i).date_value2 ;
79 l_short_code_found := 'Y' ;
80 exit ;
81 end if ;
82 end loop ;
83 end if ;
84 -- when the override table has values
85 if l_short_code_found = 'Y' then
86 if l_num_val1 is not null then
87 p_value_num1 := l_num_val1 ;
88 p_data_type_cd1 := 'N' ;
89 p_overide_found := 'Y' ;
90 elsif l_char_val1 is not null then
91 p_value_char1 := l_char_val1 ;
92 p_data_type_cd1 := 'C' ;
93 p_overide_found := 'Y' ;
94 elsif l_date_val1 is not null then
95 p_value_Date1 := l_date_val1 ;
96 p_data_type_cd1 := 'D' ;
97 p_overide_found := 'Y' ;
98 end if ;
99 -- seond value , if the first not nul then then dont evaluate
100 if p_overide_found = 'Y' then
101 if l_num_val2 is not null then
102 p_value_num2 := l_num_val2 ;
103 p_data_type_cd2 := 'N' ;
104 elsif l_char_val2 is not null then
105 p_value_char2 := l_char_val2 ;
106 p_data_type_cd2 := 'C' ;
107 elsif l_date_val2 is not null then
108 p_value_Date2 := l_date_val2 ;
109 p_data_type_cd2 := 'D' ;
110 end if ;
111 end if ;
112 end if ;
113
114 if g_debug then
115 hr_utility.set_location(' overide data :'|| p_short_code || ' : ' || l_short_code_found, 5);
116 hr_utility.set_location(' overide :' || p_overide_found, 5);
117 hr_utility.set_location(' Leaving:' || l_proc, 5);
118 end if ;
119
120 end get_override_value ;
121 --
122 -- -----------------------------------------------------
123 -- find whther the organization id in the hierarch
124 -- -----------------------------------------------------
125 --
126 Function find_part_of_org_hierarchy( p_organization_id in number,
127 p_org_structure_version_id in number,
128 p_start_organization_id in number,
129 p_business_group_id in number,
130 p_effective_date in date )
131 return Boolean is
132
133 l_proc varchar2(100):= g_package||'find_part_of_org_hierarchy';
134 l_ret_val boolean ;
135 l_dummy_char varchar2(1) ;
136
137 -- this cursor validation can not be joined with
138 -- other cursor. cieenct by sql statment has issue
139 -- when the second table joing with the sql db 9.2.04
140
141 cursor c_osv is
142 select 'x'
143 from per_org_structure_versions a
144 where a.org_structure_version_id = p_org_structure_version_id
145 and p_effective_date between nvl(a.date_from,p_effective_date)
146 and nvl(a.date_to,p_effective_date) ;
147
148
149 cursor c_org_hier is
150 select 'x'
151 from per_org_structure_elements a
152 where a.ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
153 and a.business_group_id = p_business_group_id
154 and (a.organization_id_parent = p_organization_id
155 or a.organization_id_child = p_organization_id)
156 start with a.organization_id_parent = p_start_organization_id
157 connect by prior a.organization_id_child = a.organization_id_parent ;
158 -- if the least organization defined as start postion
159 -- this cursor make sure the person belongs to the org
160 cursor c_org is
161 select 'x'
162 from per_org_structure_elements a
163 where a.organization_id_child = p_organization_id
164 and a.organization_id_child = p_start_organization_id
165 and a.business_group_id = p_business_group_id
166 and a.ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
167 ;
168
169 Begin
170 if g_debug then
171 hr_utility.set_location('Entering: '||l_proc,10);
172 end if ;
173
174 l_ret_val := false ;
175
176 open c_osv ;
177 fetch c_osv into l_dummy_char ;
178 if c_osv%notfound then
179 close c_osv ;
180 hr_utility.set_location('Leaving date track : '||l_proc,10);
181 Return l_ret_val ;
182 end if ;
183 close c_osv ;
184
185
186 open c_org_hier ;
187 fetch c_org_hier into l_dummy_char ;
188 if c_org_hier%Found then
189 l_ret_val := true ;
190 hr_utility.set_location(' Found the organization :' || p_organization_id , 15);
191 else
192 open c_org ;
193 fetch c_org into l_dummy_char ;
194 if c_org%found then
195 l_ret_val := true ;
196 hr_utility.set_location(' Found the organization :' || p_organization_id , 20);
197 end if ;
198 close c_org ;
199 end if ;
200 close c_org_hier ;
201
202
203 if g_debug then
204 hr_utility.set_location(' Leaving:' || l_proc, 5);
205 end if ;
206 Return l_ret_val ;
207
208 End find_part_of_org_hierarchy ;
209
210 -- -----------------------------------------------------
211 -- find whther the position id in the hierarchy
212 -- -----------------------------------------------------
213 --
214
215 Function find_part_of_pos_hierarchy(p_position_id in number,
216 p_pos_structure_version_id in number,
217 p_start_position_id in number,
218 p_business_group_id in number,
219 p_effective_date in date)
220 return Boolean is
221
222 l_proc varchar2(100):= g_package||'find_part_of_pos_hierarchy';
223 l_ret_val boolean ;
224 l_dummy_char varchar2(1) ;
225
226 -- this cursor validation can not be joined with
227 -- other cursor. cieenct by sql statment has issue
228 -- when the second table joing with the sql db 9.2.04
229
230 cursor c_psv is
231 select 'x'
232 from per_pos_structure_versions a
233 where a.pos_structure_version_id = p_pos_structure_version_id
234 and p_effective_date between nvl(a.date_from,p_effective_date)
235 and nvl(a.date_to,p_effective_date) ;
236
237
238 cursor c_pos_hier is
239 select 'x'
240 from per_pos_structure_elements a
241 where a.POS_STRUCTURE_VERSION_ID = p_pos_structure_version_id
242 and a.business_group_id = p_business_group_id
243 and (a.parent_position_id = p_position_id
244 or a.subordinate_position_id = p_position_id)
245 start with a.parent_position_id = p_start_position_id
246 connect by prior a.subordinate_position_id = a.parent_position_id ;
247
248
249 -- if the least position defined as start postion
250 -- this cursor make sure the validation
251 -- since it is user defined values
252 cursor c_pos is
253 select 'x'
254 from per_pos_structure_elements a
255 where a.subordinate_position_id = p_position_id
256 and a.subordinate_position_id = p_start_position_id
257 and a.business_group_id = p_business_group_id
258 and a.POS_STRUCTURE_VERSION_ID = p_pos_structure_version_id
259 ;
260 Begin
261 if g_debug then
262 hr_utility.set_location('Entering: '||l_proc,10);
263 end if ;
264 l_ret_val := false ;
265
266 open c_psv ;
267 fetch c_psv into l_dummy_char ;
268 if c_psv%notfound then
269 close c_psv ;
270 hr_utility.set_location('Leaving date track : '||l_proc,10);
271 Return l_ret_val ;
272 end if ;
273 close c_psv ;
274
275
276 open c_pos_hier ;
277 fetch c_pos_hier into l_dummy_char ;
278 if c_pos_hier%Found then
279 l_ret_val := true ;
280 hr_utility.set_location(' Found the position :' || p_position_id , 15);
281 else
282 open c_pos ;
283 fetch c_pos into l_dummy_char ;
284 if c_pos%found then
285 l_ret_val := true ;
286 hr_utility.set_location(' Found the position :' || p_position_id , 20);
287 end if ;
288 close c_pos ;
289 end if ;
290 close c_pos_hier ;
291
292
293 if g_debug then
294 hr_utility.set_location(' Leaving:' || l_proc, 5);
295 end if ;
296 Return l_ret_val ;
297
298 End find_part_of_pos_hierarchy ;
299
300
301 --
302 -- -----------------------------------------------------
303 -- get the values from formula
304 -- -----------------------------------------------------
305 --4586880
306 -- Its modified so that it would have only one set of char/num/date output
307 -- Input =ACCESS_CALC_RULE / ACCESS_CALC_RULE2
308 procedure get_formula_value
309 ( p_person_id number ,
310 p_assignment_id number,
311 p_formula_id number default null,
312 p_business_group_id number ,
313 p_data_type_cd varchar2,
314 p_value_char out nocopy varchar2,
315 p_value_num out nocopy number ,
316 p_value_date out nocopy date ,
317 p_pgm_id in number default null,
318 p_pl_id in number default null,
319 p_opt_id in number default null,
320 p_oipl_id in number default null,
321 p_ler_id in number default null,
322 p_pl_typ_id in number default null,
323 p_effective_date date ,
324 p_fonm_cvg_strt_date date default null ,
325 p_fonm_rt_strt_date date default null
326 ) is
327
328 l_proc varchar2(100):= g_package||'get_formula_value';
329 l_statement varchar2(3000) ;
330 l_output ff_exec.outputs_t;
331 l_dummy varchar2(3000) ;
332 l_effective_date date ;
333 --
334 Begin
335
336 if g_debug then
337 hr_utility.set_location('Entering: '||l_proc,10);
338 end if ;
339
340 if p_formula_id is not null then
341 l_output := benutils.formula
342 (p_formula_id => p_formula_id
343 ,p_effective_date => p_effective_date
344 ,p_business_group_id => p_business_group_id
345 ,p_assignment_id => p_assignment_id
346 ,p_pgm_id => p_pgm_id
347 ,p_pl_id => p_pl_id
348 ,p_ler_id => p_ler_id
349 ,p_opt_id => p_opt_id
350 ,p_pl_typ_id => p_pl_typ_id
351 ,p_param1 => 'BEN_IV_RT_STRT_DT'
352 ,p_param1_value => fnd_date.date_to_canonical(p_fonm_rt_strt_date)
353 ,p_param2 => 'BEN_IV_CVG_STRT_DT'
354 ,p_param2_value => fnd_date.date_to_canonical(p_fonm_cvg_strt_date)
355 );
356 for l_count in l_output.first..l_output.last loop
357 l_dummy := l_output(l_count).value;
358 hr_utility.set_location(l_count ||' : ' || l_dummy, 5);
359 if l_dummy is not null then
360
361 if p_data_type_cd = 'C' then
362 p_value_char := l_dummy ;
363 elsif p_data_type_cd = 'N' then
364 p_value_num := to_number(l_dummy) ;
365 elsif p_data_type_cd = 'D' then
366 p_value_date := to_date(l_dummy,'YYYY/MM/DD HH24:MI:SS') ;
367 end if ;
368
369 end if ;-- if dummy
370 end loop ;
371
372 end if ;
373
374 if g_debug then
375 hr_utility.set_location(' char return :' || p_value_char, 5);
376 hr_utility.set_location(' num return :' || p_value_num , 5);
377 hr_utility.set_location(' date return :'|| p_value_date, 5);
378
379 hr_utility.set_location(' Leaving:' || l_proc, 5);
380 end if ;
381
385 -- get the values from accesss table
382 end get_formula_value ;
383 --
384 -- -----------------------------------------------------
386 -- -----------------------------------------------------
387 --
388 procedure get_values_access_table
389 (p_table_name varchar2,
390 p_column_name varchar2,
391 p_data_type_cd varchar2,
392 p_person_id number ,
393 p_assignment_id number,
394 p_business_group_id number ,
395 p_value_char out nocopy varchar2,
396 p_value_num out nocopy number ,
397 p_value_date out nocopy date ,
398 p_effective_date date ,
399 p_fonm_cvg_strt_date date default null ,
400 p_fonm_rt_strt_date date default null
401 ) is
402
403 l_proc varchar2(100):= g_package||'get_values_access_table';
404 l_statement varchar2(3000) ;
405 l_output ff_exec.outputs_t;
406 l_dummy varchar2(3000) ;
407 l_effective_date date ;
408 --
409 TYPE valueCurType is REF CURSOR;
410 l_valcur valueCurType ;
411 --
412 l_current_loc NUMBER:=0;
413 Begin
414 if g_debug then
415 hr_utility.set_location('Entering: '||l_proc,10);
416 end if ;
417 l_effective_date := nvl(p_fonm_rt_strt_date,nvl(p_fonm_cvg_strt_date,p_effective_date)) ;
418
419 -- build the dynamic statement
420 l_statement := 'Select ' || p_column_name ||
421 ' From ' || p_table_name || ' tbl ' ||
422 ' Where person_id = ' || p_person_id ||
423 ' and to_date(''' || to_char(l_effective_date,'DD-MM-RRRR')|| ''',''DD-MM-RRRR'') ' ||
424 ' between tbl.effective_start_date and tbl.effective_end_date ' ;
425
426 if p_table_name = 'PER_ALL_PEOPLE_F' or p_table_name = 'PER_PEOPLE_F' then
427 l_statement := l_statement|| ' order by tbl.effective_start_date desc ' ; -- for the timing nothing
428 elsif p_table_name = 'PER_ALL_ASSIGNMENTS_F' or p_table_name = 'PER_ASSIGNMENTS_F' then
429 l_statement := l_statement|| ' and Assignment_id = ' || p_assignment_id ||
430 -- ' and primary_flag = ''Y'' order by tbl.effective_start_date desc ' ; /* Bug 4234033 */
431 ' order by tbl.effective_start_date desc ' ;
432 end if ;
433 --l_statement := l_statement || ' ; ' ;
434 --
435 -- get the value from the dynamic statment
436 -- errors when the statment fails
437 begin
438 open l_valcur for l_statement ;
439 exception
440 --
441 when others then
442 --
443 fnd_file.put_line(fnd_file.log,'Error executing this dynamically build SQL Statement: ');
444 FOR i in 1..LENGTH(l_statement) LOOP
445 IF mod(i,80)=0 OR i=LENGTH(l_statement) THEN
446 fnd_file.put_line(fnd_file.log,' ' ||substr(l_statement,l_current_loc+1,i-l_current_loc));
447 l_current_loc:=i;
448 END IF;
449 END LOOP;
450 raise;
451 --
452 end;
453 --- eof dynamic sql
454
455 open l_valcur for l_statement ;
456 if p_data_type_cd ='C' then
457 fetch l_valcur into p_value_char ;
458 elsif p_data_type_cd ='N' then
459 fetch l_valcur into p_value_num ;
460 elsif p_data_type_cd ='D' then
461 fetch l_valcur into p_value_date ;
462 end if ;
463 close l_valcur ;
464
465 if g_debug then
466 hr_utility.set_location(' char return :' || p_value_char, 5);
467 hr_utility.set_location(' num return :' || p_value_num , 5);
468 hr_utility.set_location(' date return :'|| p_value_date, 5);
469 hr_utility.set_location(' Leaving:' || l_proc, 5);
470 end if ;
471 exception
472 when others then
473 hr_utility.set_location(' exception:' || substr(sqlerrm,1,110), 5);
474 raise ;
475 End get_values_access_table ;
476
477
478
479 function is_ok (p_value varchar2 ,
480 p_from_value varchar2 ,
481 p_to_value varchar2 default null,
482 p_range_check varchar2 default 'N' )
483 return boolean is
484
485 l_proc varchar2(100):= g_package||'is_ok C';
486 l_return boolean ;
487 begin
488 if g_debug then
489 hr_utility.set_location('Entering: '||l_proc,10);
490 hr_utility.set_location('range : '||p_range_check,10);
491 end if ;
492 l_return := false ;
493 if p_range_check = 'N' then
494 l_return := (p_value = p_from_value ) ;
495
496 else
497 l_return := ( p_value between p_from_value and p_to_value ) ;
498
499 end if ;
500 -- if one of the value is null then
501 -- l_return is null # 4205818
502 if l_return is null then
503 l_return := false ;
504 end if ;
505 if g_debug then
506 hr_utility.set_location(' Leaving:' || l_proc, 5);
507 end if ;
508 return l_return ;
509 end is_ok ;
510
511
512 -- override function for numeric
513 function is_ok (p_value number ,
514 p_from_value number ,
515 p_to_value number default null,
516 p_range_check varchar2 default 'N' )
517 return boolean is
518
519 l_proc varchar2(100):= g_package||'is_ok N';
520 l_return boolean ;
521 begin
522 if g_debug then
523 hr_utility.set_location('Entering: '||l_proc,10);
524 hr_utility.set_location('range : '||p_range_check,10);
525 end if ;
526 l_return := false ;
527 if p_range_check = 'N' then
528 l_return := (p_value = p_from_value ) ;
529
530 else
534
531 l_return := ( p_value between p_from_value and p_to_value ) ;
532
533 end if ;
535 -- if one of the value is null then
536 -- l_return is null # 4205818
537 if l_return is null then
538 l_return := false ;
539 end if ;
540
541 if g_debug then
542 hr_utility.set_location(' Leaving:' || l_proc, 5);
543 end if ;
544 return l_return ;
545 end is_ok ;
546
547 -- override function for numeric
548 function is_ok (p_value date ,
549 p_from_value date ,
550 p_to_value date default null,
551 p_range_check varchar2 default 'N' )
552 return boolean is
553
554 l_proc varchar2(100):= g_package||'is_ok D';
555 l_return boolean ;
556 begin
557 if g_debug then
558 hr_utility.set_location('Entering: '||l_proc,10);
559 hr_utility.set_location('range : '||p_range_check,10);
560 end if ;
561 l_return := false ;
562 if p_range_check = 'N' then
563 l_return := (p_value = p_from_value ) ;
564
565 else
566 l_return := ( p_value between p_from_value and p_to_value ) ;
567
568 end if ;
569 -- if one of the value is null then
570 -- l_return is null # 4205818
571 if l_return is null then
572 l_return := false ;
573 end if ;
574
575 if g_debug then
576 hr_utility.set_location(' Leaving:' || l_proc, 5);
577 end if ;
578 return l_return ;
579 end is_ok ;
580
581 -- 4586880
582 -- --------- Function set_true_false -----------
583 -- validates the value defined by SET1/SET2
584 -- returns True if satisfies
585 -- else False.
586 ------------------------------------------------
587 Function set_true_false(p_crit_col_datatype varchar2,
588 p_value_char varchar2,
589 p_value_num number,
590 p_value_date date,
591 p_char_from_value varchar2,
592 p_char_to_value varchar2 default null,
593 p_num_from_value number,
594 p_num_to_value number default null,
595 p_date_from_value date,
596 p_date_to_value date default null,
597 p_allow_range_validation_flag varchar2 default 'N'
598 )
599 return Boolean is
600
601 l_proc varchar2(100):= g_package||'set_true_false';
602 l_return boolean ;
603
604 begin
605 if g_debug then
606 hr_utility.set_location('Entering: '||l_proc,5);
607 end if ;
608
609 if p_crit_col_datatype = 'C' then
610 l_return := is_ok(p_value_char,p_char_from_value,p_char_to_value,p_allow_range_validation_flag) ;
611 elsif p_crit_col_datatype = 'N' then
612 l_return := is_ok(p_value_num,p_num_from_value,p_num_to_value,p_allow_range_validation_flag) ;
613 elsif p_crit_col_datatype = 'D' then
614 l_return := is_ok(p_value_date,p_date_from_value,p_date_to_value,p_allow_range_validation_flag) ;
615 end if ;
616
617 if g_debug then
618 hr_utility.set_location(' Leaving:' || l_proc, 10);
619 end if ;
620
621 return l_return;
622
623 end set_true_false;
624 --
625 -- -----------------------------------------------------
626 -- This procedure determines define criteria for rate by criteria RBC.
627 -- -----------------------------------------------------
628 -- 4586880
629
630 procedure main(p_eligy_prfl_id in number,
631 p_person_id in number,
632 p_assignment_id in number,
633 p_business_group_id in number,
634 p_pgm_id in number default null,
635 p_pl_id in number default null,
636 p_opt_id in number default null,
637 p_oipl_id in number default null,
638 p_ler_id in number default null,
639 p_pl_typ_id in number default null,
640 p_effective_date in date,
641 p_fonm_cvg_strt_date in date default null,
642 p_fonm_rt_strt_date in date default null,
643 p_crit_ovrrd_val_tbl in pqh_popl_criteria_ovrrd.g_crit_ovrrd_val_tbl
644 ) is
645
646
647
648 l_effective_date date ;
649
650 cursor c_dst_egc is
651 select distinct egc.eligy_criteria_id
652 from ben_eligy_criteria egc,
653 ben_eligy_crit_values_f egv
654 where egv.eligy_prfl_id = p_eligy_prfl_id
655 and egv.eligy_criteria_id = egc.eligy_criteria_id
656 and egc.business_group_id = p_business_group_id
657 and egc.criteria_type <> 'SEED'
658 and l_effective_date between egv.effective_Start_date
659 and egv.effective_end_date ;
660
661 cursor c_info_egc (p_eligy_criteria_id number ) is
662 select egc.criteria_type ,
663 egc.crit_col1_val_type_cd,
664 egc.crit_col1_datatype,
665 egc.access_table_name1,
666 egc.access_column_name1,
667 egc.crit_col2_datatype,
668 egc.access_table_name2,
669 egc.access_column_name2,
670 egc.access_calc_rule,
671 egc.access_calc_rule2,
672 egc.allow_range_validation_flag,
673 egc.allow_range_validation_flag2,
674 egc.name,
675 egc.short_code
676 from ben_eligy_criteria egc
677 where eligy_criteria_id = p_eligy_criteria_id ;
678
679 l_info_egc c_info_egc%rowtype ;
680
681 cursor c_egv (p_eligy_prfl_id number ,
682 p_eligy_criteria_id number ) is
686 egv.char_value2 ,
683 select egv.number_value1 ,
684 egv.number_value2 ,
685 egv.char_value1 ,
687 egv.date_value1 ,
688 egv.date_value2 ,
689 egv.number_value3 ,
690 egv.number_value4 ,
691 egv.char_value3 ,
692 egv.char_value4 ,
693 egv.date_value3 ,
694 egv.date_value4 ,
695 egv.EXCLD_FLAG
696 from ben_eligy_crit_values_f egv
697 where egv.eligy_prfl_id = p_eligy_prfl_id
698 and egv.eligy_criteria_id = p_eligy_criteria_id
699 and egv.business_group_id = p_business_group_id
700 and p_effective_date between egv.effective_Start_date
701 and egv.effective_end_date
702 -- order by ordr_num
703 ;
704 l_egv c_egv%rowtype ;
705
706
707 l_proc varchar2(100):= g_package||'main';
708 l_crit_value_checked varchar2(1) ;
709 --
710 l_value_char1 varchar2(4000) ;
711 l_value_num1 number ;
712 l_value_date1 date ;
713 l_value_char2 varchar2(4000) ;
714 l_value_num2 number ;
715 l_value_date2 date ;
716 l_true_false1 boolean ;
717 l_true_false2 boolean ;
718 l_error_value1 varchar2(4000) ;
719 l_error_value2 varchar2(4000) ;
720 l_eror_crit_name ben_eligy_criteria.name%type ;
721 l_overide_found varchar2(1) ;
722 l_crit_found varchar2(1) ;
723
724 Begin
725 /*if p_person_id = 282401 then
726 hr_utility.trace_on(null,'rbc');
727 end if;*/
728 g_debug := hr_utility.debug_enabled;
729 if g_debug then
730 hr_utility.set_location('Entering: '||l_proc,10);
731 end if ;
732 l_effective_date := nvl(p_fonm_cvg_strt_date,nvl(p_fonm_rt_strt_date,p_effective_date)) ;
733 l_crit_value_checked := 'Y' ;
734 if g_debug then
735 hr_utility.set_location('effective date : '||p_effective_date,11);
736 hr_utility.set_location('fonm effective date : '||l_effective_date,11);
737 end if ;
738
739 for i in c_dst_egc
740 loop
741 hr_utility.set_location('eligy_criteria_id : '||i.eligy_criteria_id,20);
742 open c_info_egc (i.eligy_criteria_id) ;
743 fetch c_info_egc into l_info_egc ;
744 close c_info_egc ;
745 l_overide_found := 'N' ;
746
747 get_override_value ( p_crit_ovrrd_val_tbl => p_crit_ovrrd_val_tbl,
748 p_short_code => l_info_egc.short_code,
749 p_data_type_cd1 => l_info_egc.crit_col1_datatype,
750 p_value_char1 => l_value_char1 ,
751 p_value_num1 => l_value_num1 ,
752 p_value_date1 => l_value_date1 ,
753 p_data_type_cd2 => l_info_egc.crit_col2_datatype,
754 p_value_char2 => l_value_char2,
755 p_value_num2 => l_value_num2 ,
756 p_value_date2 => l_value_date2,
757 p_overide_found => l_overide_found
758 ) ;
759
760 if l_overide_found = 'N' then
761 /*** 4586880
762 1. If ACCESS_CALC_RULE defined
763 get_formula_value
764 o/p = l_value_num1 ,l_value_char1, l_value_date1
765 2. else get_values_access_table using access_table_name1 ,access_column_name1
766 o/p = l_value_num1 ,l_value_char1, l_value_date1
767
768 3. if ACCESS_CALC_RULE2 defined
769 get_formula_value
770 o/p = l_value_num2 ,l_value_char2, l_value_date2
771 4. else get_values_access_table using using access_table_name2 ,access_column_name2
772 o/p = l_value_num2 ,l_value_char2, l_value_date2
773
774 ***/
775 if l_info_egc.ACCESS_CALC_RULE is not null then
776 get_formula_value
777 ( p_person_id => p_person_id ,
778 p_assignment_id => p_assignment_id,
779 p_formula_id => l_info_egc.access_calc_rule,
780 p_business_group_id => p_business_group_id,
781 p_data_type_cd => l_info_egc.crit_col1_datatype,
782 p_value_char => l_value_char1 ,
783 p_value_num => l_value_num1 ,
784 p_value_date => l_value_date1 ,
785 p_pgm_id => p_pgm_id ,
786 p_pl_id => p_pl_id ,
787 p_opt_id => p_opt_id ,
788 p_oipl_id => p_oipl_id ,
789 p_ler_id => p_ler_id ,
790 p_pl_typ_id => p_pl_typ_id ,
791 p_effective_date => p_effective_date,
792 p_fonm_cvg_strt_date=> p_fonm_cvg_strt_date ,
793 p_fonm_rt_strt_date => p_fonm_rt_strt_date
794 ) ;
795
796 else
797 -- get the values from the table column
798 hr_utility.set_location('get the values from the table column SET1',99099);
799 get_values_access_table
800 (p_table_name => l_info_egc.access_table_name1 ,
801 p_column_name => l_info_egc.access_column_name1,
802 p_data_type_cd => l_info_egc.crit_col1_datatype ,
803 p_person_id => p_person_id ,
804 p_assignment_id => p_assignment_id,
805 p_business_group_id => p_business_group_id,
806 p_value_char => l_value_char1 ,
807 p_value_num => l_value_num1 ,
808 p_value_date => l_value_date1 ,
809 p_effective_date => p_effective_date,
810 p_fonm_cvg_strt_date => p_fonm_cvg_strt_date,
814
811 p_fonm_rt_strt_date => p_fonm_rt_strt_date
812 ) ;
813
815
816 if l_value_char1 is null and l_value_num1 is null and l_value_date1 is null then
817 -- RAISE the ERROR a
818 null ;
819 end if ;
820
821 end if; --l_info_egc.ACCESS_CALC_RULE
822 hr_utility.set_location('l_info_egc.access_table_name1 '||l_info_egc.access_table_name1,99011);
823 hr_utility.set_location('l_info_egc.access_column_name1 '||l_info_egc.access_column_name1,99011);
824 hr_utility.set_location('l_info_egc.access_calc_rule '||l_info_egc.access_calc_rule,99011);
825 hr_utility.set_location('l_info_egc.crit_col1_datatype '||l_info_egc.crit_col1_datatype,99011);
826 hr_utility.set_location('l_info_egc.allow_range_validation_flag '||l_info_egc.allow_range_validation_flag,99011);
827 hr_utility.set_location('l_value_char1 '||l_value_char1,99011);
828 hr_utility.set_location('l_value_num1 '||l_value_num1,99011);
829 hr_utility.set_location('l_value_date1 '||l_value_date1,99011);
830
831 if l_info_egc.ACCESS_CALC_RULE2 is not null then
832 get_formula_value
833 ( p_person_id => p_person_id ,
834 p_assignment_id => p_assignment_id,
835 p_formula_id => l_info_egc.access_calc_rule2,
836 p_business_group_id => p_business_group_id,
837 p_data_type_cd => l_info_egc.crit_col2_datatype,
838 p_value_char => l_value_char2 ,
839 p_value_num => l_value_num2 ,
840 p_value_date => l_value_date2 ,
841 p_pgm_id => p_pgm_id ,
842 p_pl_id => p_pl_id ,
843 p_opt_id => p_opt_id ,
844 p_oipl_id => p_oipl_id ,
845 p_ler_id => p_ler_id ,
846 p_pl_typ_id => p_pl_typ_id ,
847 p_effective_date => p_effective_date,
848 p_fonm_cvg_strt_date=> p_fonm_cvg_strt_date ,
849 p_fonm_rt_strt_date => p_fonm_rt_strt_date
850 ) ;
851
852 elsif l_info_egc.access_table_name2 is not null then
853 hr_utility.set_location('get the values from the table column SET2',99099);
854 get_values_access_table
855 (p_table_name => l_info_egc.access_table_name2 ,
856 p_column_name => l_info_egc.access_column_name2,
857 p_data_type_cd => l_info_egc.crit_col2_datatype ,
858 p_person_id => p_person_id ,
859 p_assignment_id => p_assignment_id,
860 p_business_group_id => p_business_group_id,
861 p_value_char => l_value_char2 ,
862 p_value_num => l_value_num2 ,
863 p_value_date => l_value_date2 ,
864 p_effective_date => p_effective_date,
865 p_fonm_cvg_strt_date => p_fonm_cvg_strt_date,
866 p_fonm_rt_strt_date => p_fonm_rt_strt_date
867 ) ;
868
869 end if ; --if l_info_egc.ACCESS_CALC_RULE2
870 hr_utility.set_location('l_info_egc.access_table_name2 '||l_info_egc.access_table_name2,99011);
871 hr_utility.set_location('l_info_egc.access_column_name2 '||l_info_egc.access_column_name2,99011);
872 hr_utility.set_location('l_info_egc.access_calc_rule2 '||l_info_egc.access_calc_rule2,99011);
873 hr_utility.set_location('l_info_egc.crit_col2_datatype '||l_info_egc.crit_col2_datatype,99011);
874 hr_utility.set_location('l_info_egc.allow_range_validation_flag2 '||l_info_egc.allow_range_validation_flag2,99011);
875 hr_utility.set_location('l_value_char2 '||l_value_char2,99011);
876 hr_utility.set_location('l_value_num2 '||l_value_num2,99011);
877 hr_utility.set_location('l_value_date2 '||l_value_date2,99011);
878
879 end if ; --l_overide_found = 'N'
880 --- intialise the falg value
881 l_crit_value_checked := 'Y' ;
882 l_true_false1 := true ;
883 l_true_false2 := true ;
884
885 --- here is automatic conversion from value to varchar
886 --- we dont have to change the date/number formats
887 --- this variable displyed in log in case of criteria failure
888 l_error_value1 := l_value_char1||l_value_num1||l_value_date1 ;
889 l_error_value2 := l_value_char2||l_value_num2||l_value_date2 ;
890 if l_error_value2 is not null then
891 l_error_value1 := l_error_value1 || ' , ' || l_error_value2 ;
892 end if ;
893 hr_utility.set_location(' person values : '|| l_error_value1 ,30);
894 l_eror_crit_name := l_info_egc.name ;
895 ---
896
897 for l in c_egv(p_eligy_prfl_id ,
898 i.eligy_criteria_id)
899 loop
900 if g_debug then
901 hr_utility.set_location('eligy_criteria_values C : '||l.char_value1 ||' / ' || l.char_value2||' and '||l.char_value3 ||'/ '||l.char_value4 ,30);
902 hr_utility.set_location('eligy_criteria_values N : '||l.number_value1 ||' / ' || l.number_value2||' and '||l.number_value3 ||'/ '||l.number_value4 ,30);
903 hr_utility.set_location('eligy_criteria_values D : '||l.date_value1 ||' / ' || l.date_value2 ||' and '||l.date_value3 ||'/ '||l.date_value4,30);
904 end if ;
905 -- intialize the variable for the calue row
906 l_crit_value_checked := 'Y' ;
907 l_true_false1 := true ;
908 l_true_false2 := true ;
909 /***4586880
910 1. check if hierarchy is defined on Organization/Position .
911 Validate the hierarchy .
912 2. Else Validate the output of EGL with those defined at ECV
913 I. Validate l_value_num1 against 1.number_value1 / 1.number_value2
917 ***/
914 similarly goes for l_value_char1, l_value_date1
915 II. Validate l_value_num2 against 1.number_value3 / 1.number_value4
916 similarly goes for l_value_char2, l_value_date2
918 --1. check if hierarchy is defined on Organization/Position .
919 if l_info_egc.crit_col1_val_type_cd in ('ORG_HIER','POS_HIER') then
920 if l_info_egc.crit_col1_val_type_cd = 'ORG_HIER' then
921
922 if not (find_part_of_org_hierarchy(p_organization_id => l_value_num1,
923 p_org_structure_version_id => l.number_value1,
924 p_start_organization_id => l.number_value2,
925 p_business_group_id => p_business_group_id,
926 p_effective_date => p_effective_date)
927 ) then
928 l_crit_value_checked := 'N' ;
929 end if ;
930
931 else
932
933 if not (find_part_of_pos_hierarchy(p_position_id => l_value_num1,
934 p_pos_structure_version_id => l.number_value1,
935 p_start_position_id => l.number_value2,
936 p_business_group_id => p_business_group_id,
937 p_effective_date => p_effective_date)
938 ) then
939 l_crit_value_checked := 'N' ;
940 end if ;
941 end if ;
942
943 else --2. Else Validate the output of EGL with those defined at ECV
944 -- I. Validate l_value_num1 against 1.number_value1 / 1.number_value2
945 -- similarly goes for l_value_char1, l_value_date1
946 l_true_false1 := set_true_false( p_crit_col_datatype => l_info_egc.crit_col1_datatype,
947 p_value_char => l_value_char1,
948 p_value_num => l_value_num1,
949 p_value_date => l_value_date1,
950 p_char_from_value => l.char_value1,
951 p_char_to_value => l.char_value2,
952 p_num_from_value => l.number_value1,
953 p_num_to_value => l.number_value2,
954 p_date_from_value => l.date_value1,
955 p_date_to_value => l.date_value2,
956 p_allow_range_validation_flag => nvl(l_info_egc.allow_range_validation_flag,'N')
957 );
958 -- II. Validate l_value_num2 against 1.number_value3 / 1.number_value4
959 -- similarly goes for l_value_char2, l_value_date2
960 -- Bug 4695890 -- if condition.
961 if l_info_egc.access_calc_rule2 is not null or (l_info_egc.access_column_name2 is not null and l_info_egc.access_column_name2 is not null)then
962 l_true_false2 := set_true_false( p_crit_col_datatype => l_info_egc.crit_col2_datatype,
963 p_value_char => l_value_char2,
964 p_value_num => l_value_num2 ,
965 p_value_date => l_value_date2,
966 p_char_from_value => l.char_value3,
967 p_char_to_value => l.char_value4,
968 p_num_from_value => l.number_value3,
969 p_num_to_value => l.number_value4,
970 p_date_from_value => l.date_value3,
971 p_date_to_value => l.date_value4,
972 p_allow_range_validation_flag => nvl(l_info_egc.allow_range_validation_flag2,'N')
973 );
974 end if;
975 /*** 4586880
976 1.Validate the result of set 1 and set 2
977 I.SET1 defined and SET2 not defined
978 set1 set2
979 true true = true
980 false true = false
981 II.SET1 not defined and SET2 defined
982 set1 set2
983 true true = true
984 true false = false
985 III.BOTH SET1 and SET2 defined
986 set1 set2
987 true true = true
988 true false = false
989 false true = false
990 false false = false
991 2. IF result is false means criteria is not satisfied.So, set l_crit_value_checked to 'N'
992 ***/
993 if not ( l_true_false1 and l_true_false2 ) then
994 l_crit_value_checked := 'N' ;
995 end if;
996
997 end if; -- ('ORG_HIER','POS_HIER')
998
999
1000 -- if one of the value satified exit the loop , value loop works in OR condition
1001 -- also validate the exclude flag
1002 --- Met the condition Exclude
1003 --- Y N exit and validate further criteria
1004 --- Y Y exit consider prfile failed
1005 --- N N validate further values to see whether he pass any values
1006 --- N Y treate like he meets the condition Y and N
1007
1008 hr_utility.set_location('end result : '||l_crit_value_checked,20);
1009 hr_utility.set_location('exclude : '||l.EXCLD_FLAG,20);
1010 if l_crit_value_checked = 'Y' and l.EXCLD_FLAG = 'N' then
1011 --- when one of the value satisfied and exclde flag is false
1012 --- exit, next criteria will be validated for failure
1013 exit ;
1014 elsif l_crit_value_checked = 'Y' and l.EXCLD_FLAG = 'Y' then
1015 --- when a person met the condition and he is to be exclude
1016 --- he is not eligible for ne need to validate next criteria
1017 --- change the falg to 'N' and exit
1018 l_crit_value_checked := 'N' ;
1019 exit ;
1020
1021 elsif l_crit_value_checked = 'N' and l.EXCLD_FLAG = 'N' then
1022 --- when there is failure in value and no exclude flag
1023 --- dont do anything, evaluate the next values for the
1024 --- same criteria -- no need for the if condition
1025 --- this is added only for better understanding
1026 null ;
1027 elsif l_crit_value_checked = 'N' and l.EXCLD_FLAG = 'Y' then
1028 --- consider the condition is met and lookup for any further validate
1029 --- to validate, this is as good as Y and N
1030 l_crit_value_checked := 'Y' ;
1031 exit ;
1032 end if ;
1033 hr_utility.set_location('after exclude end result : '||l_crit_value_checked,20);
1034
1035 end Loop ;
1036
1037 -- if any of the criteria failed (non of the value matched) then exit
1038 -- criteria works in AND condition
1039 if l_crit_value_checked = 'N' then
1040 exit ;
1041 end if ;
1042 end Loop ;
1043
1044
1045 hr_utility.set_location('end result : '||l_crit_value_checked,20);
1046 if l_crit_value_checked = 'N' then
1047 ben_evaluate_elig_profiles.g_inelg_rsn_cd := 'RBC';
1048 fnd_message.set_name('BEN','BEN_94124_RBC_PRFL_FAIL');
1049 hr_utility.set_location('Criteria Failed: '||l_proc,20);
1050 benutils.write(p_text => 'Generic Criteria : '|| l_eror_crit_name
1051 );
1052 benutils.write(p_text => 'Criteria Values : '||l_error_value1
1053 );
1054 --
1055 raise ben_evaluate_elig_profiles.g_criteria_failed;
1056 --
1057 end if ;
1058 if g_debug then
1059 hr_utility.set_location(' Leaving:' || l_proc, 5);
1060 end if ;
1061 end main ;
1062
1063
1064
1065 end ben_evaluate_elig_criteria;
[Home] [Help]