[Home] [Help]
PACKAGE BODY: APPS.BEN_EVAL_COMM_LER_ELIG_PROFL
Source
1 Package Body ben_eval_comm_ler_elig_profl as
2 /* $Header: beeligcl.pkb 120.3.12020000.2 2013/02/04 16:22:34 usaraswa noship $ */
3 --
4 g_package varchar2(30) := 'ben_eval_comm_ler_elig_profl.';
5
6 cursor get_elig_obj_elig_prf_id(p_elig_obj_id in number,
7 p_eligibility_profile_id in number,
8 p_effective_date date) is
9 select
10 elig_obj_elig_prfl_id,
11 elig_prfl_id,
12 object_version_number
13 from
14 ben_elig_obj_elig_profl_f
15 where
16 p_effective_date between effective_start_date and effective_end_date
17 and elig_obj_id = p_elig_obj_id
18 and elig_prfl_id = p_eligibility_profile_id;
19
20 Procedure chk_uniq_elig_prfl(p_eligy_prfl_id in number,
21 p_col_value in number,
22 p_table_name in varchar2,
23 p_column_name in varchar2,
24 p_effective_date in date,
25 p_business_group_id in number,
26 p_ELIG_OBJ_ELIG_PRFL_ID in number default null) is
27 --
28 l_proc varchar2(72) := g_package||'chk_uniq_elig_prfl';
29 l_dummy number ;
30 --
31 cursor c_uniq_cm_typ_elig_prfl is
32 select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
33 ben_elig_obj_elig_profl_f obe
34 where ((obe.effective_start_date > p_effective_date)
35 OR (p_effective_date BETWEEN obe.effective_start_date AND obe.effective_END_date))
36 and obj.table_name = p_table_name
37 and obj.column_name = p_column_name
38 and obj.column_value = p_col_value
39 and obj.business_group_id = p_business_group_id
40 and obe.business_group_id = p_business_group_id
41 and obj.ELIG_OBJ_ID = obe.ELIG_OBJ_ID
42 and obe.elig_prfl_id = p_eligy_prfl_id
43 and obe.ELIG_OBJ_ELIG_PRFL_ID <> nvl(p_ELIG_OBJ_ELIG_PRFL_ID,ELIG_OBJ_ELIG_PRFL_ID);
44 Begin
45 hr_utility.set_location('Entering:'||l_proc, 5);
46 --
47 -- raise error as the record already exists
48 open c_uniq_cm_typ_elig_prfl ;
49 fetch c_uniq_cm_typ_elig_prfl into l_dummy ;
50 --
51 if c_uniq_cm_typ_elig_prfl%found then
52 --
53 close c_uniq_cm_typ_elig_prfl ;
54 hr_utility.set_location('Future record exists.Cannot insert ', 8 ) ;
55 fnd_message.set_name('PER','HR_7211_DT_UPD_ROWS_IN_FUTURE');
56 fnd_message.raise_error;
57 --
58 else
59 close c_uniq_cm_typ_elig_prfl ;
60 end if;
61 --
62 hr_utility.set_location('Leaving:'||l_proc, 10);
63 --
64 End chk_uniq_elig_prfl;
65
66 procedure create_eligibility_object
67 (p_eligibility_profile_id in number,
68 p_mndtry_flag in varchar2,
69 p_TABLE_NAME in varchar2,
70 p_COLUMN_NAME in varchar2,
71 p_col_value in number,
72 p_effective_start_date out nocopy date,
73 p_effective_end_date out nocopy date,
74 p_business_group_id in number,
75 p_effective_date in date,
76 p_object_version_number out nocopy number ,
77 p_elig_obj_elig_prfl_id out nocopy number,
78 p_elig_obj_id out nocopy number,
79 p_result out nocopy varchar2) is
80
81 cursor get_elig_object_id (p_user_group_id in number) is
82 select
83 elig_obj_id
84 from
85 ben_elig_obj_f EO
86 where
87 p_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
88 and TABLE_NAME = p_TABLE_NAME
89 and COLUMN_NAME = p_COLUMN_NAME
90 and COLUMN_VALUE = p_col_value;
91
92
93 l_elig_obj_id ben_elig_obj_f.elig_obj_id%type;
94 l_start_date date;
95 l_end_date date;
96 l_object_version_number1 number;
97 l_effective_start_date date;
98 l_effective_end_date date;
99 l_object_version_number2 number;
100 l_business_group_id number:= p_business_group_id;
101 l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
102 l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
103 l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
104
105 begin
106 savepoint create_eligibility_object_s;
107
108 open get_elig_object_id(p_col_value);
109 fetch get_elig_object_id into l_elig_obj_id;
110 close get_elig_object_id;
111
112 chk_uniq_elig_prfl(p_eligy_prfl_id => p_eligibility_profile_id,
113 p_col_value => p_col_value,
114 p_table_name => p_TABLE_NAME,
115 p_column_name => p_COLUMN_NAME,
116 p_effective_date => p_effective_date,
117 p_business_group_id => p_business_group_id,
118 p_ELIG_OBJ_ELIG_PRFL_ID => -1);
119
120
121 if l_elig_obj_id is null then
122 ben_elig_obj_api.create_ELIG_OBJ
123 (
124 p_validate => false
125 ,p_elig_obj_id => l_elig_obj_id
126 ,p_effective_start_date => l_start_date
127 ,p_effective_end_date => l_end_date
128 ,p_business_group_id => l_business_group_id
129 ,p_table_name => p_TABLE_NAME
130 ,p_column_name => p_COLUMN_NAME
131 ,p_column_value => p_col_value
132 ,p_object_version_number => l_object_version_number1
133 ,p_effective_date => p_effective_date
134 );
135 end if;
136
137 if(l_elig_obj_id is null) then
138 p_result := 'F';
139 ROLLBACK TO create_eligibility_object_s;
140 return;
141 -- raise;
142 end if;
143
144 open get_elig_obj_elig_prf_id(l_elig_obj_id,p_eligibility_profile_id,p_effective_date);
145 fetch get_elig_obj_elig_prf_id into l_elig_obj_elig_prf_id,l_elig_prof_id,l_elig_obj_obj_version;
146 close get_elig_obj_elig_prf_id;
147
148 if l_elig_obj_elig_prf_id is null then
149
150 ben_ELIG_OBJ_ELIG_PROFL_api.create_elig_obj_elig_profl
151 (
152 p_validate => false
153 ,p_elig_obj_id => l_elig_obj_id
154 ,p_elig_prfl_id => p_eligibility_profile_id
155 ,p_mndtry_flag => p_mndtry_flag
156 ,p_business_group_id => l_business_group_id
157 ,p_effective_start_date => l_effective_start_date
158 ,p_effective_end_date => l_effective_end_date
159 ,p_elig_obj_elig_prfl_id => l_elig_obj_elig_prf_id
160 ,p_object_version_number => l_object_version_number2
161 ,p_effective_date => p_effective_date
162 );
163 end if;
164
165 if(l_elig_obj_elig_prf_id is not null) then
166 p_elig_obj_elig_prfl_id := l_elig_obj_elig_prf_id;
167 p_elig_obj_id := l_elig_obj_id;
168 p_object_version_number := l_object_version_number2;
169 p_effective_start_date := l_effective_start_date;
170 p_effective_end_date := l_effective_end_date;
171 p_result := 'S';
172 else
173 p_result := 'F';
174 end if;
175
176 Exception
177 when others then
178 hr_utility.set_location('In exce:', 10);
179 ROLLBACK TO create_eligibility_object_s;
180 fnd_message.raise_error;
181 p_result := 'F';
182 end create_eligibility_object;
183
184 procedure delete_eligibility_object(
185 p_validate in boolean default false,
186 p_col_value in varchar2,
187 p_TABLE_NAME varchar2,
188 p_COLUMN_NAME varchar2,
189 p_effective_start_date out nocopy date,
190 p_effective_end_date out nocopy date,
191 p_eligibility_profile_id in varchar2,
192 p_effective_date in date,
193 p_datetrack_mode in varchar2,
194 p_elig_obj_elig_prf_id in number,
195 p_elig_obj_id in number,
196 p_result out nocopy varchar) is
197
198 cursor get_elig_object_id (p_col_value in number) is
199 select
200 EO.elig_obj_id ,
201 EO.object_version_number
202 from
203 ben_elig_obj_f EO,
204 ben_elig_obj_elig_profl_f eop
205 where
206 p_effective_date between eo.EFFECTIVE_START_DATE and eo.EFFECTIVE_END_DATE
207 and TABLE_NAME = p_TABLE_NAME
208 and COLUMN_NAME = p_COLUMN_NAME
209 and COLUMN_VALUE = p_col_value
210 and eop.ELIG_OBJ_ELIG_PRFL_ID = p_elig_obj_elig_prf_id
211 and eop.elig_obj_id = eo.elig_obj_id;
212
213
214
215 cursor check_elig_obj_asso_id_exist(p_elig_obj_id in number) is
216 select
217 elig_obj_elig_prfl_id
218 from
219 ben_elig_obj_elig_profl_f
220 where
221 p_effective_date between effective_start_date and effective_end_date
222 and elig_obj_id = p_elig_obj_id
223 and rownum < 2;
224
225 l_elig_object_id ben_elig_obj_f.elig_obj_id%type;
226 l_obj_version ben_elig_obj_f.object_version_number%type;
227 l_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
228 l_elig_obj_obj_version ben_elig_obj_elig_profl_f.object_version_number%type;
229 l_elig_prof_id ben_elig_obj_elig_profl_f.elig_prfl_id%type;
230 l_effective_start_date date;
231 l_effective_end_date date;
232 l_check_elig_obj_elig_prf_id ben_elig_obj_elig_profl_f.elig_obj_elig_prfl_id%type;
233 begin
234
235 savepoint delete_eligibility_object;
236
237 open get_elig_object_id(p_col_value);
238 fetch get_elig_object_id into l_elig_object_id,l_obj_version;
239 close get_elig_object_id;
240
241 if l_elig_object_id is null then--add logic for cursor not found
242 p_result := 'F';
243 ROLLBACK TO delete_eligibility_object;
244 return;
245 end if;
246
247 open get_elig_obj_elig_prf_id(l_elig_object_id,p_eligibility_profile_id,p_effective_date);
248 fetch get_elig_obj_elig_prf_id into l_elig_obj_elig_prf_id,l_elig_prof_id,l_elig_obj_obj_version;
249 close get_elig_obj_elig_prf_id;
250
251 ben_elig_obj_elig_profl_api.delete_elig_obj_elig_profl
252 (p_validate => p_validate
253 ,p_elig_obj_elig_prfl_id => l_elig_obj_elig_prf_id
254 ,p_effective_start_date => l_effective_start_date
255 ,p_effective_end_date => l_effective_end_date
256 ,p_object_version_number => l_elig_obj_obj_version
257 ,p_effective_date => p_effective_date
258 ,p_datetrack_mode => p_datetrack_mode
259 );
260
261 p_effective_start_date := l_effective_start_date;
262 p_effective_end_date := l_effective_end_date;
263
264 open check_elig_obj_asso_id_exist(l_elig_object_id);
265 fetch check_elig_obj_asso_id_exist into l_check_elig_obj_elig_prf_id;
266 close check_elig_obj_asso_id_exist;
267
268 if l_check_elig_obj_elig_prf_id is null then
269 ben_elig_obj_api.delete_ELIG_OBJ
270 (p_validate => p_validate
271 ,p_elig_obj_id => l_elig_object_id
272 ,p_effective_start_date => l_effective_start_date
273 ,p_effective_end_date => l_effective_end_date
274 ,p_object_version_number => l_obj_version
275 ,p_effective_date => p_effective_date
276 ,p_datetrack_mode => hr_api.g_zap
277 );
278 else
279 l_obj_version := 1;
280 end if;
281 if l_elig_obj_obj_version is not null and l_obj_version is not null then
282 p_result := 'S';
283 else
284 p_result := 'F';
285 end if;
286
287 exception
288 when others then
289 ROLLBACK TO delete_eligibility_object;
290 fnd_message.raise_error;
291 p_result := 'F';
292 end delete_eligibility_object;
293
294 procedure update_eligibility_object
295 (p_validate in boolean default false
296 ,p_elig_obj_elig_prfl_id in number
297 ,p_effective_start_date out nocopy date
298 ,p_effective_end_date out nocopy date
299 ,p_elig_obj_id in number default hr_api.g_number
300 ,p_elig_prfl_id in number default hr_api.g_number
301 ,p_mndtry_flag in varchar2 default hr_api.g_varchar2
302 ,p_object_version_number in out nocopy number
303 ,p_effective_date in date
304 ,p_datetrack_mode in varchar2
305 ) is
306
307 l_effective_start_date ben_elig_obj_elig_profl_f.effective_start_date%TYPE;
308 l_effective_end_date ben_elig_obj_elig_profl_f.effective_end_date%TYPE;
309 l_object_version_number ben_elig_obj_elig_profl_f.object_version_number%TYPE;
310
311 cursor c_obj_det is
312 select TABLE_NAME,
313 COLUMN_NAME,
314 column_value ,
315 business_group_id
316 from ben_elig_obj_f
317 where elig_obj_id = p_elig_obj_id
318 and p_effective_date between effective_start_date and effective_end_date;
319
320 l_obj_det c_obj_det%rowtype;
321
322 begin
323 savepoint update_eligibility_object;
324 l_object_version_number := p_object_version_number;
325
326 open c_obj_det;
327 fetch c_obj_det into l_obj_det;
328 close c_obj_det;
329
330 chk_uniq_elig_prfl(p_eligy_prfl_id => p_elig_prfl_id,
331 p_col_value => l_obj_det.column_value,
332 p_table_name => l_obj_det.TABLE_NAME,
333 p_column_name => l_obj_det.COLUMN_NAME,
334 p_effective_date => p_effective_date,
335 p_business_group_id => l_obj_det.business_group_id,
336 p_ELIG_OBJ_ELIG_PRFL_ID => p_elig_obj_elig_prfl_id);
337
338 ben_ELIG_OBJ_ELIG_PROFL_api.update_ELIG_OBJ_ELIG_PROFL
339 (p_validate => p_validate
340 ,p_elig_obj_elig_prfl_id => p_elig_obj_elig_prfl_id
341 ,p_effective_start_date => l_effective_start_date
342 ,p_effective_end_date => l_effective_end_date
343 ,p_elig_obj_id => p_elig_obj_id
344 ,p_elig_prfl_id => p_elig_prfl_id
345 ,p_mndtry_flag => p_mndtry_flag
346 ,p_object_version_number => l_object_version_number
347 ,p_effective_date => p_effective_date
348 ,p_datetrack_mode => p_datetrack_mode
349 );
350
351 p_effective_start_date := l_effective_start_date;
352 p_effective_end_date := l_effective_end_date;
353 p_object_version_number := l_object_version_number;
354
355 exception
356 when others then
357 ROLLBACK TO update_eligibility_object;
358 fnd_message.raise_error;
359
360 end update_eligibility_object;
361
362 procedure check_eligible
363 (p_person_id in number
364 ,p_effective_date in date
365 ,p_lf_evt_ocrd_dt in date default null
366 ,p_business_group_id in number
367 ,p_per_in_ler_id in number default null
368 ,p_ler_id in number default null
369 ,p_source in varchar2
370 ,p_col_value in number
371 ,p_elig_flag out nocopy varchar2
372 ) is
373
374 l_proc varchar2(61) := g_package||'.check_eligible';
375
376 TYPE ebo_chk is REF CURSOR;
377 c_chk_ebo ebo_chk;
378
379 l_chk_obj_id long;
380
381 cursor c_elig_prof(p_elig_obj_id number) is
382 select elig_prfl_id,
383 nvl(mndtry_flag,'N'),
384 'Y',
385 'N'
386 from ben_elig_obj_elig_profl_f
387 where elig_obj_id = p_elig_obj_id
388 and p_effective_date between effective_start_date
389 and effective_end_date
390 order by decode(mndtry_flag,'Y',1,2);
391
392 l_effective_start_date date;
393 l_effective_end_date date;
394 l_object_version_number number;
395 l_person_id number;
396 l_dummy varchar2(1);
397 l_eligible boolean;
398 l_elig_flag varchar2(1);
399
400 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
401 l_score_tab ben_evaluate_elig_profiles.scoreTab;
402 l_elig_obj_id number;
403
404
405 type l_asg_tab_type is table of number
406 index by binary_integer;
407
408 l_asg_tab l_asg_tab_type;
409
410 begin
411
412 hr_utility.set_location('Entering '||l_proc,10);
413 if p_person_id is null then
414 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
415 hr_utility.set_message_token('API_NAME', l_proc);
416 hr_utility.set_message_token('ARGUMENT', 'person_id');
417 hr_utility.raise_error;
418
419 end if;
420
421 hr_api.mandatory_arg_error(p_api_name => l_proc,
422 p_argument => 'effective_date',
423 p_argument_value => p_effective_date);
424
425 if(p_source = 'C') then
426 l_chk_obj_id := 'select elig_obj_id from ben_elig_obj_f '||
427 ' where table_name = ''BEN_CM_TYP_F'' '||
428 ' and column_name = ''CM_TYP_ID'' '||
429 ' and business_group_id = '||p_business_group_id||
430 ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between effective_start_date and effective_end_date ' ||
431 ' and column_value = '|| p_col_value;
432 else
433 l_chk_obj_id := 'select elig_obj_id from ben_elig_obj_f '||
434 ' where table_name = ''BEN_LER_F'' '||
435 ' and column_name = ''LER_ID'' '||
436 ' and business_group_id = '||p_business_group_id||
437 ' and to_date(''' || to_char(p_effective_date,'DD-MM-RRRR') || ''',''DD-MM-RRRR'') between effective_start_date and effective_end_date ' ||
438 ' and column_value = '|| p_col_value ;
439 end if;
440
441 --Bug 16218328 commented the below if condition
442 -- if fnd_global.conc_request_id in (0,-1) then
443 ben_env_object.init
444 (p_business_group_id => p_business_group_id,
445 p_thread_id => null,
446 p_chunk_size => null,
447 p_threads => null,
448 p_max_errors => null,
449 p_benefit_action_id => null,
450 p_effective_date=> p_effective_date);
451 -- end if;
452 hr_utility.set_location('Entering '||l_proc,11);
453 open c_chk_ebo for l_chk_obj_id;
454 hr_utility.set_location('Entering '||l_proc,12);
455 fetch c_chk_ebo into l_elig_obj_id;
456 if c_chk_ebo%notfound then
457 close c_chk_ebo;
458 hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
459 hr_utility.set_message_token('TABLE_NAME', 'ben_elig_obj_f');
460 hr_utility.set_message_token('SESSION_DATE', to_char(p_effective_date));
461 hr_utility.raise_error;
462 end if;
463 close c_chk_ebo;
464
465 open c_chk_ebo for l_chk_obj_id;
466 loop
467 fetch c_chk_ebo into l_elig_obj_id;
468 hr_utility.set_location('l_elig_obj_id '||l_elig_obj_id,10);
469 if c_chk_ebo%notfound then
470 exit;
471 end if;
472 open c_elig_prof(l_elig_obj_id);
473 loop
474 fetch c_elig_prof into
475 l_eligprof_tab(l_eligprof_tab.count+1).eligy_prfl_id,
476 l_eligprof_tab(l_eligprof_tab.count+1).mndtry_flag,
477 l_eligprof_tab(l_eligprof_tab.count+1).compute_score_flag,
478 l_eligprof_tab(l_eligprof_tab.count+1).trk_scr_for_inelg_flag;
479
480 if c_elig_prof%notfound then
481 exit;
482 end if;
483
484 end loop;
485 close c_elig_prof;
486 end loop;
487 close c_chk_ebo;
488
489 l_person_id := p_person_id;
490
491 if l_eligprof_tab.count > 0 then
492
493 ben_manage_life_events.g_output_string := null;
494 if ben_manage_life_events.g_cache_person_prtn.count is null or
495 ben_manage_life_events.g_cache_person_prtn.count = 0 then
496 ben_manage_life_events.cache_person_information
497 ( p_person_id => l_person_id,
498 p_business_group_id => p_business_group_id,
499 p_effective_date => p_effective_date);
500 end if;
501 l_eligible :=
502 ben_evaluate_elig_profiles.eligible
503 (p_person_id => l_person_id,
504 p_business_group_id => p_business_group_id,
505 p_eval_typ => 'E',
506 p_comp_obj_mode => false,
507 p_eligprof_tab => l_eligprof_tab,
508 p_score_tab => l_score_tab,
509 p_effective_date => p_effective_date,
510 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
511 p_ler_id => p_ler_id,
512 p_source => p_source);
513 else
514 l_eligible := true;
515 end if;
516
517 hr_utility.set_location('Score Tab '||l_score_tab.count,10);
518 if l_eligible then
519 l_elig_flag := 'Y';
520 else
521 l_elig_flag := 'N';
522 end if;
523
524 p_elig_flag := l_elig_flag;
525
526 end check_eligible;
527
528 end ben_eval_comm_ler_elig_profl;