1 package body ben_evl_dpnt_elig_criteria as
2 /* $Header: bendpcrt.pkb 120.1 2010/05/17 12:21:40 sgnanama noship $ */
3
4 --------------------------------------------------------------------------------
5 /*
6 +==============================================================================+
7 | Copyright (c) 1997 Oracle Corporation |
8 | Redwood Shores, California, USA |
9 | All rights reserved. |
10 +==============================================================================+
11
12 Name
13 Profile Evaluation Package
14 Purpose
15 This package is used to determine if a dependent satisfies general eligiblity
16 criteria or not.
17 History
18 Date Who Version What?
19 --------- ----------- ------- --------------------------------------------
20 07-Apr-10 krupani 120.0 Enh 9558250 - created package to evaluate
21 user defined criteria for dependent
22 12-May-10 krupani 120.0.12010000.3 Bug 9688328 - changed the inelig_rsn_cd from 'RBC' to 'OTH'.
23 */
24 --------------------------------------------------------------------------------
25
26
27 --
28 g_package varchar2(30) := 'ben_evl_dpnt_elig_criteria.';
29 g_debug boolean := hr_utility.debug_enabled;
30 --
31 --
32 -- -----------------------------------------------------
33 -- get the values from accesss table
34 -- -----------------------------------------------------
35 --
36 procedure get_values_access_table
37 (p_table_name varchar2,
38 p_column_name varchar2,
39 p_data_type_cd varchar2,
40 p_person_id number ,
44 p_value_date out nocopy date ,
41 p_business_group_id number ,
42 p_value_char out nocopy varchar2,
43 p_value_num out nocopy number ,
45 p_effective_date date
46 ) is
47
48 l_proc varchar2(100):= g_package||'get_values_access_table';
49 l_statement varchar2(3000) ;
50 l_dummy varchar2(3000) ;
51 l_effective_date date ;
52 --
53 TYPE valueCurType is REF CURSOR;
54 l_valcur valueCurType ;
55 --
56 l_current_loc NUMBER:=0;
57 Begin
58 if g_debug then
59 hr_utility.set_location('Entering: '||l_proc,10);
60 end if ;
61
62 -- build the dynamic statement
63 l_statement := 'Select ' || p_column_name ||
64 ' From ' || p_table_name || ' tbl ' ||
65 ' Where person_id = ' || p_person_id ||
66 ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR')|| ''',''DD-MM-RRRR'') ' ||
67 ' between tbl.effective_start_date and tbl.effective_end_date ' ;
68
69 if p_table_name = 'PER_ALL_PEOPLE_F' or p_table_name = 'PER_PEOPLE_F' then
70 l_statement := l_statement|| ' order by tbl.effective_start_date desc ' ;
71 end if ;
72 --
73 -- get the value from the dynamic statment
74 -- errors when the statment fails
75 begin
76 open l_valcur for l_statement ;
77 exception
78 --
79 when others then
80 --
81 fnd_file.put_line(fnd_file.log,'Error executing this dynamically build SQL Statement: ');
82 FOR i in 1..LENGTH(l_statement) LOOP
83 IF mod(i,80)=0 OR i=LENGTH(l_statement) THEN
84 fnd_file.put_line(fnd_file.log,' ' ||substr(l_statement,l_current_loc+1,i-l_current_loc));
85 l_current_loc:=i;
86 END IF;
87 END LOOP;
88 raise;
89 --
90 end;
91 --- eof dynamic sql
92
93 open l_valcur for l_statement ;
94 if p_data_type_cd ='C' then
95 fetch l_valcur into p_value_char ;
96 elsif p_data_type_cd ='N' then
97 fetch l_valcur into p_value_num ;
98 elsif p_data_type_cd ='D' then
99 fetch l_valcur into p_value_date ;
100 end if ;
101 close l_valcur ;
102
103 if g_debug then
104 hr_utility.set_location(' char return :' || p_value_char, 5);
105 hr_utility.set_location(' num return :' || p_value_num , 5);
106 hr_utility.set_location(' date return :'|| p_value_date, 5);
107 hr_utility.set_location(' Leaving:' || l_proc, 5);
108 end if ;
109 exception
110 when others then
111 hr_utility.set_location(' exception:' || substr(sqlerrm,1,110), 5);
112 raise ;
113 End get_values_access_table ;
114
115
116 function is_ok (p_value varchar2 ,
117 p_from_value varchar2 ,
118 p_to_value varchar2 default null,
119 p_range_check varchar2 default 'N' )
120 return boolean is
121
122 l_proc varchar2(100):= g_package||'is_ok C';
123 l_return boolean ;
124 begin
125 if g_debug then
126 hr_utility.set_location('Entering: '||l_proc,10);
127 hr_utility.set_location('range : '||p_range_check,10);
128 end if ;
129 l_return := false ;
130 if p_range_check = 'N' then
131 l_return := (p_value = p_from_value ) ;
132 else
133 l_return := ( p_value between p_from_value and p_to_value ) ;
134 end if ;
135
136 if l_return is null then
137 l_return := false ;
138 end if ;
139
140 if g_debug then
141 hr_utility.set_location(' Leaving:' || l_proc, 5);
142 end if ;
143 return l_return ;
144 end is_ok ;
145
146 -- override function for numeric
147 function is_ok (p_value number ,
148 p_from_value number ,
149 p_to_value number default null,
150 p_range_check varchar2 default 'N' )
151 return boolean is
152
153 l_proc varchar2(100):= g_package||'is_ok N';
154 l_return boolean ;
155 begin
156 if g_debug then
157 hr_utility.set_location('Entering: '||l_proc,10);
158 hr_utility.set_location('range : '||p_range_check,10);
159 end if ;
160 l_return := false ;
161
162 if p_range_check = 'N' then
163 l_return := (p_value = p_from_value ) ;
164
165 else
166 l_return := ( p_value between p_from_value and p_to_value ) ;
167
168 end if ;
169
170 if l_return is null then
171 l_return := false ;
172 end if ;
173
174 if g_debug then
175 hr_utility.set_location(' Leaving:' || l_proc, 5);
176 end if ;
177 return l_return ;
178 end is_ok ;
179
180 -- override function for numeric
181 function is_ok (p_value date ,
182 p_from_value date ,
183 p_to_value date default null,
184 p_range_check varchar2 default 'N' )
185 return boolean is
186
187 l_proc varchar2(100):= g_package||'is_ok D';
188 l_return boolean ;
189 begin
190 if g_debug then
191 hr_utility.set_location('Entering: '||l_proc,10);
192 hr_utility.set_location('range : '||p_range_check,10);
193 end if ;
194 l_return := false ;
195
196 if p_range_check = 'N' then
197 l_return := (p_value = p_from_value ) ;
198
202 end if ;
199 else
200 l_return := ( p_value between p_from_value and p_to_value ) ;
201
203
204 if l_return is null then
205 l_return := false ;
206 end if ;
207
208 if g_debug then
209 hr_utility.set_location(' Leaving:' || l_proc, 5);
210 end if ;
211 return l_return ;
212 end is_ok ;
213
214
215 -- --------- Function set_true_false -----------
216 -- validates the value defined by SET1/SET2
217 -- returns True if satisfies
218 -- else False.
219 ------------------------------------------------
220 Function set_true_false
221 (p_crit_col_datatype varchar2,
222 p_value_char varchar2,
223 p_value_num number,
224 p_value_date date,
225 p_char_from_value varchar2,
226 p_char_to_value varchar2 default null,
227 p_num_from_value number,
228 p_num_to_value number default null,
229 p_date_from_value date,
230 p_date_to_value date default null,
231 p_allow_range_validation_flag varchar2 default 'N'
232 )
233 return Boolean is
234
235 l_proc varchar2(100):= g_package||'set_true_false';
236 l_return boolean ;
237
238 begin
239 if g_debug then
240 hr_utility.set_location('Entering: '||l_proc,5);
241 end if ;
242
243 if p_crit_col_datatype = 'C' then
244 l_return := is_ok(p_value_char,p_char_from_value,p_char_to_value,p_allow_range_validation_flag) ;
245 elsif p_crit_col_datatype = 'N' then
246 l_return := is_ok(p_value_num,p_num_from_value,p_num_to_value,p_allow_range_validation_flag) ;
247 elsif p_crit_col_datatype = 'D' then
248 l_return := is_ok(p_value_date,p_date_from_value,p_date_to_value,p_allow_range_validation_flag) ;
249 end if ;
250
251 if g_debug then
252 hr_utility.set_location(' Leaving:' || l_proc, 10);
253 end if ;
254
255 return l_return;
256
257 end set_true_false;
258
259
260
261 -- -----------------------------------------------------
262 -- This procedure determines define criteria for rate by criteria RBC.
263 -- -----------------------------------------------------
264
265
266 procedure main(p_dpnt_cvg_eligy_prfl_id in number,
267 p_person_id in number,
268 p_business_group_id in number,
269 p_lf_evt_ocrd_dt in date,
270 p_effective_date in date,
271 p_eligible_flag out nocopy varchar2,
272 p_inelig_rsn_cd out nocopy varchar2) is
273
274 l_effective_date date ;
275
276
277 cursor c_dst_egc is
278 select distinct egc.eligy_criteria_dpnt_id
279 from ben_eligy_criteria_dpnt egc,
280 ben_dpnt_eligy_crit_values_f egv
281 where egv.dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id
282 and egv.eligy_criteria_dpnt_id = egc.eligy_criteria_dpnt_id
283 and egc.business_group_id = p_business_group_id
284 and egc.criteria_type <> 'SEED'
285 and l_effective_date between egv.effective_Start_date
286 and egv.effective_end_date ;
287
288
289 cursor c_info_egc (p_eligy_criteria_dpnt_id number ) is
290 select egc.criteria_type ,
291 egc.crit_col1_val_type_cd,
292 egc.crit_col1_datatype,
293 egc.access_table_name1,
294 egc.access_column_name1,
295 egc.crit_col2_datatype,
296 egc.access_table_name2,
297 egc.access_column_name2,
298 egc.allow_range_validation_flag,
299 egc.allow_range_validation_flag2,
300 egc.name,
301 egc.short_code
302 from ben_eligy_criteria_dpnt egc
303 where eligy_criteria_dpnt_id = p_eligy_criteria_dpnt_id ;
304
305 l_info_egc c_info_egc%rowtype ;
306
307
308 cursor c_egv (p_dpnt_cvg_eligy_prfl_id number ,
309 p_eligy_criteria_dpnt_id number ) is
310 select egv.number_value1 ,
311 egv.number_value2 ,
312 egv.char_value1 ,
313 egv.char_value2 ,
314 egv.date_value1 ,
315 egv.date_value2 ,
316 egv.number_value3 ,
317 egv.number_value4 ,
318 egv.char_value3 ,
319 egv.char_value4 ,
320 egv.date_value3 ,
321 egv.date_value4 ,
322 egv.EXCLD_FLAG
323 from ben_dpnt_eligy_crit_values_f egv
324 where egv.dpnt_cvg_eligy_prfl_id = p_dpnt_cvg_eligy_prfl_id
325 and egv.eligy_criteria_dpnt_id = p_eligy_criteria_dpnt_id
326 and egv.business_group_id = p_business_group_id
327 and p_effective_date between egv.effective_Start_date
328 and egv.effective_end_date
329 -- order by ordr_num
330 ;
331 l_egv c_egv%rowtype ;
332
333
334 l_proc varchar2(100):= g_package||'main';
335 l_crit_value_checked varchar2(1) ;
336 --
337 l_value_char1 varchar2(4000) ;
338 l_value_num1 number ;
339 l_value_date1 date ;
340 l_value_char2 varchar2(4000) ;
341 l_value_num2 number ;
342 l_value_date2 date ;
343 l_true_false1 boolean ;
344 l_true_false2 boolean ;
345 l_error_value1 varchar2(4000) ;
346 l_error_value2 varchar2(4000) ;
347 l_eror_crit_name ben_eligy_criteria_dpnt.name%type ;
348 l_overide_found varchar2(1) ;
352 Begin
349 l_crit_found varchar2(1) ;
350 l_fonm_cvg_strt_dt date;
351
353
354 g_debug := hr_utility.debug_enabled;
355 if g_debug then
356 hr_utility.set_location('Entering: '||l_proc,10);
357 end if ;
358
359 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
360
361 if ben_manage_life_events.fonm = 'Y'
362 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
363 --
364 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
365 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
366
367 --
368 end if;
369
370 l_crit_value_checked := 'Y' ;
371 if g_debug then
372 hr_utility.set_location('effective date : '||p_effective_date,11);
373 hr_utility.set_location('fonm effective date : '||l_effective_date,11);
374 end if ;
375
376
377 for i in c_dst_egc
378 loop
379 hr_utility.set_location('eligy_criteria_dpnt_id : '||i.eligy_criteria_dpnt_id,20);
380 open c_info_egc (i.eligy_criteria_dpnt_id) ;
381 fetch c_info_egc into l_info_egc ;
382 close c_info_egc ;
383
384 hr_utility.set_location('get the values from the table column SET1',99099);
385 get_values_access_table
386 (p_table_name => l_info_egc.access_table_name1 ,
387 p_column_name => l_info_egc.access_column_name1,
388 p_data_type_cd => l_info_egc.crit_col1_datatype ,
389 p_person_id => p_person_id ,
390 p_business_group_id => p_business_group_id,
391 p_value_char => l_value_char1 ,
392 p_value_num => l_value_num1 ,
393 p_value_date => l_value_date1 ,
394 p_effective_date => l_effective_date
395 ) ;
396
397
398 if l_value_char1 is null and l_value_num1 is null and l_value_date1 is null then
399 -- RAISE the ERROR a
400 null ;
401 end if ;
402
403 hr_utility.set_location('l_info_egc.access_table_name1 '||l_info_egc.access_table_name1,99011);
404 hr_utility.set_location('l_info_egc.access_column_name1 '||l_info_egc.access_column_name1,99011);
405 hr_utility.set_location('l_info_egc.crit_col1_datatype '||l_info_egc.crit_col1_datatype,99011);
406 hr_utility.set_location('l_info_egc.allow_range_validation_flag '||l_info_egc.allow_range_validation_flag,99011);
407 hr_utility.set_location('l_value_char1 '||l_value_char1,99011);
408 hr_utility.set_location('l_value_num1 '||l_value_num1,99011);
409 hr_utility.set_location('l_value_date1 '||l_value_date1,99011);
410
411
412 if l_info_egc.access_table_name2 is not null then
413 hr_utility.set_location('get the values from the table column SET2',99099);
414 get_values_access_table
415 (p_table_name => l_info_egc.access_table_name2 ,
416 p_column_name => l_info_egc.access_column_name2,
417 p_data_type_cd => l_info_egc.crit_col2_datatype ,
418 p_person_id => p_person_id ,
419 p_business_group_id => p_business_group_id,
420 p_value_char => l_value_char2 ,
421 p_value_num => l_value_num2 ,
422 p_value_date => l_value_date2 ,
423 p_effective_date => p_effective_date
424 ) ;
425 end if ; --if l_info_egc.ACCESS_CALC_RULE2
426 hr_utility.set_location('l_info_egc.access_table_name2 '||l_info_egc.access_table_name2,99011);
427 hr_utility.set_location('l_info_egc.access_column_name2 '||l_info_egc.access_column_name2,99011);
428 hr_utility.set_location('l_info_egc.crit_col2_datatype '||l_info_egc.crit_col2_datatype,99011);
429 hr_utility.set_location('l_info_egc.allow_range_validation_flag2 '||l_info_egc.allow_range_validation_flag2,99011);
430 hr_utility.set_location('l_value_char2 '||l_value_char2,99011);
431 hr_utility.set_location('l_value_num2 '||l_value_num2,99011);
432 hr_utility.set_location('l_value_date2 '||l_value_date2,99011);
433
434 --- intialise the falg value
435 l_crit_value_checked := 'Y' ;
436 l_true_false1 := true ;
437 l_true_false2 := true ;
438
439 --- here is automatic conversion from value to varchar
440 --- we dont have to change the date/number formats
441 --- this variable displyed in log in case of criteria failure
442 l_error_value1 := l_value_char1||l_value_num1||l_value_date1 ;
443 l_error_value2 := l_value_char2||l_value_num2||l_value_date2 ;
444 if l_error_value2 is not null then
445 l_error_value1 := l_error_value1 || ' , ' || l_error_value2 ;
446 end if ;
447 hr_utility.set_location(' person values : '|| l_error_value1 ,30);
448 l_eror_crit_name := l_info_egc.name ;
449 ---
450 for l in c_egv(p_dpnt_cvg_eligy_prfl_id ,
451 i.eligy_criteria_dpnt_id)
452 loop
453 if g_debug then
454 hr_utility.set_location('eligy_criteria_values C : '||l.char_value1 ||' / ' || l.char_value2||' and '||l.char_value3 ||'/ '||l.char_value4 ,30);
455 hr_utility.set_location('eligy_criteria_values N : '||l.number_value1 ||' / ' || l.number_value2||' and '||l.number_value3 ||'/ '||l.number_value4 ,30);
456 hr_utility.set_location('eligy_criteria_values D : '||l.date_value1 ||' / ' || l.date_value2 ||' and '||l.date_value3 ||'/ '||l.date_value4,30);
457 end if ;
458 -- intialize the variable for the calue row
459 l_crit_value_checked := 'Y' ;
460 l_true_false1 := true ;
461 l_true_false2 := true ;
462
463
467 p_value_num => l_value_num1,
464 l_true_false1 := set_true_false
465 ( p_crit_col_datatype => l_info_egc.crit_col1_datatype,
466 p_value_char => l_value_char1,
468 p_value_date => l_value_date1,
469 p_char_from_value => l.char_value1,
470 p_char_to_value => l.char_value2,
471 p_num_from_value => l.number_value1,
472 p_num_to_value => l.number_value2,
473 p_date_from_value => l.date_value1,
474 p_date_to_value => l.date_value2,
475 p_allow_range_validation_flag => nvl(l_info_egc.allow_range_validation_flag,'N')
476 );
477 if (l_info_egc.access_column_name2 is not null)then
478 l_true_false2 := set_true_false
479 ( p_crit_col_datatype => l_info_egc.crit_col2_datatype,
480 p_value_char => l_value_char2,
481 p_value_num => l_value_num2 ,
482 p_value_date => l_value_date2,
483 p_char_from_value => l.char_value3,
484 p_char_to_value => l.char_value4,
485 p_num_from_value => l.number_value3,
486 p_num_to_value => l.number_value4,
487 p_date_from_value => l.date_value3,
488 p_date_to_value => l.date_value4,
489 p_allow_range_validation_flag => nvl(l_info_egc.allow_range_validation_flag2,'N')
490 );
491 end if;
492
493 if not ( l_true_false1 and l_true_false2 ) then
494 l_crit_value_checked := 'N' ;
495 end if;
496
497 -- if one of the value satified exit the loop , value loop works in OR condition
498 -- also validate the exclude flag
499 --- Met the condition Exclude
500 --- Y N exit and validate further criteria
501 --- Y Y exit consider prfile failed
502 --- N N validate further values to see whether he pass any values
503 --- N Y treate like he meets the condition Y and N
504
505 hr_utility.set_location('end result : '||l_crit_value_checked,20);
506 hr_utility.set_location('exclude : '||l.EXCLD_FLAG,20);
507 if l_crit_value_checked = 'Y' and l.EXCLD_FLAG = 'N' then
508 --- when one of the value satisfied and exclde flag is false
509 --- exit, next criteria will be validated for failure
510 exit ;
511 elsif l_crit_value_checked = 'Y' and l.EXCLD_FLAG = 'Y' then
512 --- when a person met the condition and he is to be exclude
513 --- he is not eligible for ne need to validate next criteria
514 --- change the falg to 'N' and exit
515 l_crit_value_checked := 'N' ;
516 exit ;
517
518 elsif l_crit_value_checked = 'N' and l.EXCLD_FLAG = 'N' then
519 --- when there is failure in value and no exclude flag
520 --- dont do anything, evaluate the next values for the
521 --- same criteria -- no need for the if condition
522 --- this is added only for better understanding
523 null ;
524 elsif l_crit_value_checked = 'N' and l.EXCLD_FLAG = 'Y' then
525 --- consider the condition is met and lookup for any further validate
526 --- to validate, this is as good as Y and N
527 l_crit_value_checked := 'Y' ;
528 exit ;
529 end if ;
530 hr_utility.set_location('after exclude end result : '||l_crit_value_checked,20);
531
532 end Loop ;
533
534 -- if any of the criteria failed (non of the value matched) then exit
535 -- criteria works in AND condition
536 if l_crit_value_checked = 'N' then
537 exit ;
538 end if ;
539 end Loop ;
540
541
542 hr_utility.set_location('end result : '||l_crit_value_checked,20);
543
544 if l_crit_value_checked = 'N' then
545
546 p_inelig_rsn_cd := 'OTH';
547 p_eligible_flag := 'N';
548
549 else
550 p_inelig_rsn_cd := null;
551 p_eligible_flag := 'Y';
552 end if ;
553
554 if g_debug then
555 hr_utility.set_location(' Leaving:' || l_proc, 5);
556 end if ;
557 end main ;
558
559
560
561 end ben_evl_dpnt_elig_criteria;
[Home] [Help]