[Home] [Help]
PACKAGE BODY: APPS.BEN_PER_ASG_ELIG
Source
1 Package Body ben_per_asg_elig as
2 /* $Header: benperel.pkb 120.1 2005/12/23 01:29:53 mmudigon noship $ */
3 --
4 g_proc varchar2(30) := 'ben_per_asg_elig';
5
6 procedure clear_down_cache(p_per_asg_cache_only boolean default false) is
7 --
8 --
9 begin
10 --
11 ben_person_object.clear_down_cache;
12 ben_manage_life_events.g_output_string := null;
13 if not p_per_asg_cache_only then
14 ben_elp_cache.clear_down_cache;
15 ben_elig_rl_cache.clear_down_cache;
16 end if;
17 --
18 end;
19 --
20 --Internal procedure to validate parameters and
21 --evaluate eligibility
22 --
23 procedure internal_eligible
24 (p_person_id in out nocopy number
25 ,p_assignment_id in number
26 ,p_assignment_type in varchar2
27 ,p_elig_obj_id in number
28 ,p_effective_date in date
29 ,p_business_group_id in number
30 ,p_save_results in boolean
31 ,p_elig_flag out nocopy varchar2
32 ) is
33
34 l_proc varchar2(61) := g_proc||'.internal_eligible';
35
36 cursor c_chk_ebo is
37 select null
38 from ben_elig_obj_f
39 where elig_obj_id = p_elig_obj_id
40 and p_effective_date between effective_start_date
41 and effective_end_date;
42
43 cursor c_asg(c_assignment_id number) is
44 select person_id
45 from per_all_assignments_f asg
46 where assignment_id = c_assignment_id
47 and p_effective_date between effective_start_date
48 and effective_end_date;
49
50 cursor c_elig_prof is
51 select elig_prfl_id,
52 nvl(mndtry_flag,'N'),
53 'Y',
54 'N'
55 from ben_elig_obj_elig_profl_f
56 where elig_obj_id = p_elig_obj_id
57 and p_effective_date between effective_start_date
58 and effective_end_date
59 order by decode(mndtry_flag,'Y',1,2);
60
61 cursor c_asg_by_type is
62 select paf.assignment_id
63 from per_all_assignments_f paf
64 where paf.person_id = p_person_id
65 and decode(substr(p_assignment_type,3,1),'P','Y',paf.primary_flag) = paf.primary_flag
66 and decode(substr(p_assignment_type,1,1),'L',paf.assignment_type,substr(p_assignment_type,1,1)) = paf.assignment_type
67 and paf.business_group_id = p_business_group_id
68 and p_effective_date between paf.effective_start_date
69 and paf.effective_end_date
70 order by paf.effective_start_date;
71
72 cursor c_elig_rslt(c_person_id number,
73 c_assignment_id number,
74 c_effective_date date) is
75 select elig_rslt_id,
76 elig_flag,
77 object_version_number,
78 effective_start_date,
79 effective_end_date
80 from ben_elig_rslt_f
81 where elig_obj_id = p_elig_obj_id
82 and person_id = c_person_id
83 and nvl(assignment_id,-1) = nvl(c_assignment_id,-1)
84 and c_effective_date between effective_start_date
85 and effective_end_date;
86 l_elig_rslt_rec c_elig_rslt%rowtype;
87
88 cursor c_chk_future_rslt(c_person_id number,
89 c_assignment_id number,
90 c_effective_date date) is
91 select min(effective_start_date)
92 from ben_elig_rslt_f
93 where elig_obj_id = p_elig_obj_id
94 and person_id = c_person_id
95 and nvl(assignment_id,-1) = nvl(c_assignment_id,-1)
96 and effective_start_date > c_effective_date;
97
98 l_elig_rslt_id number;
99 l_effective_start_date date;
100 l_effective_end_date date;
101 l_object_version_number number;
102 l_correction boolean;
103 l_update boolean;
104 l_update_override boolean;
105 l_update_change_insert boolean;
106 l_upd_mode varchar2(30);
107 l_person_id number;
108 l_dummy varchar2(1);
109 l_eligible boolean;
110 l_elig_flag varchar2(1);
111 l_loop_count number;
112 l_min_strt_dt date;
113 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
114 l_score_tab ben_evaluate_elig_profiles.scoreTab;
115
116 type l_asg_tab_type is table of number
117 index by binary_integer;
118
119 l_asg_tab l_asg_tab_type;
120
121 begin
122
123 if p_person_id is null and
124 p_assignment_id is null then
125
126 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
127 hr_utility.set_message_token('API_NAME', l_proc);
128 hr_utility.set_message_token('ARGUMENT', 'person_id,assignment_id');
129 hr_utility.raise_error;
130
131 end if;
132
133 if p_person_id is null and
134 p_assignment_type is not null then
135
136 hr_utility.set_message(801, 'HR_7207_API_MANDATORY_ARG');
137 hr_utility.set_message_token('API_NAME', l_proc);
138 hr_utility.set_message_token('ARGUMENT', 'person_id');
139 hr_utility.raise_error;
140
141 end if;
142
143 hr_api.mandatory_arg_error(p_api_name => l_proc,
144 p_argument => 'effective_date',
145 p_argument_value => p_effective_date);
146
147 hr_api.mandatory_arg_error(p_api_name => l_proc,
148 p_argument => 'elig_obj_id',
149 p_argument_value => p_elig_obj_id);
150
151 if fnd_global.conc_request_id in (0,-1) then
152 ben_env_object.init
153 (p_business_group_id => p_business_group_id,
154 p_thread_id => null,
155 p_chunk_size => null,
156 p_threads => null,
157 p_max_errors => null,
158 p_benefit_action_id => null,
159 p_effective_date=> p_effective_date);
160 end if;
161
162 open c_chk_ebo;
163 fetch c_chk_ebo into l_dummy;
164 if c_chk_ebo%notfound then
165 close c_chk_ebo;
166 hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
167 hr_utility.set_message_token('TABLE_NAME', 'ben_elig_obj_f');
168 hr_utility.set_message_token('SESSION_DATE', to_char(p_effective_date));
169 hr_utility.raise_error;
170 end if;
171 close c_chk_ebo;
172
173 open c_elig_prof;
174 loop
175 fetch c_elig_prof into
176 l_eligprof_tab(l_eligprof_tab.count+1).eligy_prfl_id,
177 l_eligprof_tab(l_eligprof_tab.count+1).mndtry_flag,
178 l_eligprof_tab(l_eligprof_tab.count+1).compute_score_flag,
179 l_eligprof_tab(l_eligprof_tab.count+1).trk_scr_for_inelg_flag;
180
181 if c_elig_prof%notfound then
182 exit;
183 end if;
184
185 end loop;
186 close c_elig_prof;
187
188 if p_assignment_type <> 'NAA' then
189 --
190 -- fetch all assignments of the given type
191 --
192 open c_asg_by_type;
193 fetch c_asg_by_type bulk collect into l_asg_tab;
194 close c_asg_by_type;
195 elsif p_assignment_id is not null then
196 l_asg_tab(1) := p_assignment_id;
197 end if;
198
199 if p_person_id is null then
200
201 open c_asg(l_asg_tab(1));
202 fetch c_asg into l_person_id;
203 close c_asg;
204
205 if l_person_id is null then
206 hr_utility.set_location('person_id is null and no assignment found',10);
207 hr_utility.set_message(801, 'HR_7180_DT_NO_ROW_EXIST');
208 hr_utility.set_message_token('TABLE_NAME', 'per_all_assignments_f');
209 hr_utility.set_message_token('SESSION_DATE', to_char(p_effective_date));
210 hr_utility.raise_error;
211 end if;
212 else
213 l_person_id := p_person_id;
214 end if;
215
216 l_loop_count := 0;
217 if l_asg_tab.count = 0 then
218 --
219 -- if only p_person_id is passed in, loop once
220 --
221 if p_person_id is not null and
222 (p_assignment_type is null or
223 p_assignment_type = 'NAA') then
224 l_loop_count := 1;
225 l_asg_tab(l_loop_count) := null;
226 end if;
227 else
228 l_loop_count := l_asg_tab.count;
229 end if;
230
231 for i in 1..l_loop_count
232 loop
233
234 hr_utility.set_location('Assignment id '||l_asg_tab(i),10);
235 if l_eligprof_tab.count > 0 then
236
237 ben_manage_life_events.g_output_string := null;
238
239 l_eligible :=
240 ben_evaluate_elig_profiles.eligible
241 (p_person_id => l_person_id,
242 p_assignment_id => l_asg_tab(i),
243 p_business_group_id => p_business_group_id,
244 p_eval_typ => 'E',
245 p_comp_obj_mode => false,
246 p_eligprof_tab => l_eligprof_tab,
247 p_score_tab => l_score_tab,
248 p_effective_date => p_effective_date);
249 else
250 l_eligible := true;
251 end if;
252
253 hr_utility.set_location('Score Tab '||l_score_tab.count,10);
254 if l_eligible then
255 l_elig_flag := 'Y';
256 else
257 l_elig_flag := 'N';
258 end if;
259
260 if p_save_results then
261
262 open c_elig_rslt(l_person_id,
263 l_asg_tab(i),
264 p_effective_date);
265 fetch c_elig_rslt into l_elig_rslt_rec;
266 close c_elig_rslt;
267
268 if l_elig_rslt_rec.elig_rslt_id is null then
269
270 ben_elig_rslt_api.create_elig_rslt
271 (p_elig_obj_id => p_elig_obj_id,
272 p_effective_date => p_effective_date,
273 p_business_group_id => p_business_group_id,
274 p_person_id => l_person_id,
275 p_assignment_id => l_asg_tab(i),
276 p_elig_flag => l_elig_flag,
277 p_elig_rslt_id => l_elig_rslt_id,
278 p_effective_start_date => l_effective_start_date,
279 p_effective_end_date => l_effective_end_date,
280 p_object_version_number => l_object_version_number);
281
282 --
283 --if there is a future result, date track delete this result
284 --
285 l_min_strt_dt := null;
286 open c_chk_future_rslt(l_person_id,
287 l_asg_tab(i),
288 p_effective_date);
289 fetch c_chk_future_rslt into l_min_strt_dt;
290 close c_chk_future_rslt;
291
292 if l_min_strt_dt is not null then
293
294 ben_elig_rslt_api.delete_elig_rslt
295 (p_effective_date => l_min_strt_dt-1,
296 p_elig_rslt_id => l_elig_rslt_id,
297 p_datetrack_mode => hr_api.g_delete,
298 p_effective_start_date => l_effective_start_date,
299 p_effective_end_date => l_effective_end_date,
300 p_object_version_number => l_object_version_number);
301
302 end if;
303
304 elsif l_elig_flag <> l_elig_rslt_rec.elig_flag then
305
306 dt_api.find_dt_upd_modes
307 (p_effective_date => p_effective_date,
308 p_base_table_name => 'BEN_ELIG_RSLT_F',
309 p_base_key_column => 'elig_rslt_id',
310 p_base_key_value => l_elig_rslt_rec.elig_rslt_id,
311 p_correction => l_correction,
312 p_update => l_update,
313 p_update_override => l_update_override,
314 p_update_change_insert => l_update_change_insert);
315
316 if l_update then
317 l_upd_mode := hr_api.g_update;
318 elsif l_update_change_insert then
319 l_upd_mode := hr_api.g_update_change_insert;
320 else
321 l_upd_mode := hr_api.g_correction;
322 end if;
323
324 ben_elig_rslt_api.update_elig_rslt
325 (p_datetrack_mode => l_upd_mode,
326 p_elig_obj_id => p_elig_obj_id,
327 p_effective_date => p_effective_date,
328 p_business_group_id => p_business_group_id,
329 p_person_id => l_person_id,
330 p_assignment_id => l_asg_tab(i),
331 p_elig_flag => l_elig_flag,
332 p_elig_rslt_id => l_elig_rslt_rec.elig_rslt_id,
333 p_effective_start_date => l_effective_start_date,
334 p_effective_end_date => l_effective_end_date,
335 p_object_version_number => l_elig_rslt_rec.object_version_number);
336 end if;
337
338 end if;
339
340 end loop;
341
342 if p_assignment_type is null then
343 p_elig_flag := l_elig_flag;
344 p_person_id := l_person_id;
345 end if;
346
347 end internal_eligible;
348 --
349 -- This procedure is called in the batch mode
350 --
351 procedure eligible
352 (p_person_id in number
353 ,p_assignment_type in varchar2
354 ,p_elig_obj_id in number
355 ,p_effective_date in date
356 ,p_business_group_id in number
357 ,p_save_results in boolean
358 ) is
359
360 l_proc varchar2(61) := g_proc||'.eligible';
361
362 l_person_id number := p_person_id;
363 l_elig_flag varchar2(1);
364
365 begin
366
367 internal_eligible
368 (p_person_id => l_person_id,
369 p_assignment_id => null,
370 p_assignment_type => p_assignment_type,
371 p_elig_obj_id => p_elig_obj_id,
372 p_elig_flag => l_elig_flag,
373 p_business_group_id => p_business_group_id,
374 p_save_results => p_save_results,
375 p_effective_date => p_effective_date);
376
377 end;
378 --
379 -- This function evaluates eligibility and stores the results
380 --
381 function eligible
382 (p_person_id in number default null
383 ,p_assignment_id in number default null
384 ,p_elig_obj_id in number
385 ,p_effective_date in date
386 ,p_business_group_id in number
387 ,p_save_results in boolean
388 ) return boolean is
389
390 l_proc varchar2(61) := g_proc||'.eligible';
391
392 l_person_id number := p_person_id;
393 l_eligible boolean;
394 l_elig_flag varchar2(1);
395
396 begin
397
398 internal_eligible
399 (p_person_id => l_person_id,
400 p_assignment_id => p_assignment_id,
401 p_assignment_type => null,
402 p_elig_obj_id => p_elig_obj_id,
403 p_elig_flag => l_elig_flag,
404 p_business_group_id => p_business_group_id,
405 p_save_results => p_save_results,
406 p_effective_date => p_effective_date);
407
408 l_eligible := (l_elig_flag = 'Y');
409
410
411 return l_eligible;
412
413 end eligible;
414 --
415 -- This function is for called from sql statements
416 --
417 function eligible
418 (p_person_id in number default null
419 ,p_assignment_id in number default null
420 ,p_elig_obj_id in number default null
421 ,p_effective_date in date
422 ,p_business_group_id in number
423 ) return varchar2 is
424 pragma autonomous_transaction;
425
426 l_proc varchar2(61) := g_proc||'.eligible';
427
428 l_person_id number := p_person_id;
429 l_elig_flag varchar2(1);
430
431 begin
432
433 internal_eligible
434 (p_person_id => l_person_id,
435 p_assignment_id => p_assignment_id,
436 p_assignment_type => null,
437 p_save_results => false,
438 p_elig_obj_id => p_elig_obj_id,
439 p_elig_flag => l_elig_flag,
440 p_business_group_id => p_business_group_id,
441 p_effective_date => p_effective_date);
442
443 return l_elig_flag;
444
445 end eligible;
446
447 --
448 -- This function is for called from sql statements
449 --
450 function elp_eligible
451 (p_person_id in number default null
452 ,p_assignment_id in number default null
453 ,p_eligy_prfl_id in number
454 ,p_effective_date in date
455 ,p_pl_id in number
456 ,p_node_pl_id in number
457 ,p_business_group_id in number
458 ) return varchar2 is
459 pragma autonomous_transaction;
460
461 cursor c_asg(c_assignment_id number) is
462 select person_id
463 from per_all_assignments_f asg
464 where assignment_id = c_assignment_id
465 and p_effective_date between effective_start_date
466 and effective_end_date;
467
468
469 cursor c_chk_elp is
470 select null
471 from ben_eligy_prfl_f
472 where eligy_prfl_id = p_eligy_prfl_id
473 and business_group_id = p_business_group_id
474 and stat_cd = 'A'
475 and p_effective_date between effective_start_date
476 and effective_end_date;
477
478 l_proc varchar2(61) := g_proc||'.eligible';
479 l_person_id number;
480 l_dummy varchar2(1);
481 l_eligible boolean;
482 l_elig_flag varchar2(1);
483 l_eligprof_tab ben_evaluate_elig_profiles.proftab;
484 l_score_tab ben_evaluate_elig_profiles.scoreTab;
485
486 begin
487
488 hr_api.mandatory_arg_error(p_api_name => l_proc,
489 p_argument => 'effective_date',
490 p_argument_value => p_effective_date);
491
492 hr_utility.set_location('entering elp_eligible',10);
493 hr_utility.set_location('p_pl_id '||p_pl_id,10);
494 hr_utility.set_location('p_node_pl_id '||p_node_pl_id,10);
495 hr_utility.set_location('p_business_group_id '||p_business_group_id,10);
496 hr_utility.set_location('p_eligy_prfl_id '||p_eligy_prfl_id,10);
497
498 if p_pl_id <> p_node_pl_id then
499 return 'N';
500 end if;
501
502 if p_eligy_prfl_id is null then
503 return 'Y';
504 end if;
505
506 --
507 -- check if elp is valid on p_effective_date. It is not possible to check
508 -- this in the calling proc because of the connect by behavior
509 --
510 open c_chk_elp;
511 fetch c_chk_elp into l_dummy;
512 if c_chk_elp%notfound then
513 close c_chk_elp;
514 return 'N';
515 end if;
516 close c_chk_elp;
517
518 if fnd_global.conc_request_id in (0,-1) then
519 ben_env_object.init
520 (p_business_group_id => p_business_group_id,
521 p_thread_id => null,
522 p_chunk_size => null,
523 p_threads => null,
524 p_max_errors => null,
525 p_benefit_action_id => null,
526 p_effective_date=> p_effective_date);
527 end if;
528
529 l_eligprof_tab(1).eligy_prfl_id := p_eligy_prfl_id;
530 l_eligprof_tab(1).mndtry_flag := 'Y';
531
532 if p_person_id is null then
533
534 open c_asg(p_assignment_id);
535 fetch c_asg into l_person_id;
536 close c_asg;
537
538 else
539 l_person_id := p_person_id;
540 end if;
541
542 ben_manage_life_events.g_output_string := null;
543
544 l_eligible :=
545 ben_evaluate_elig_profiles.eligible
546 (p_person_id => l_person_id,
547 p_assignment_id => p_assignment_id,
548 p_business_group_id => p_business_group_id,
549 p_eval_typ => 'E',
550 p_comp_obj_mode => false,
551 p_eligprof_tab => l_eligprof_tab,
552 p_score_tab => l_score_tab,
553 p_effective_date => p_effective_date);
554
555 if l_eligible then
556 l_elig_flag := 'Y';
557 else
558 l_elig_flag := 'N';
559 end if;
560
561 return l_elig_flag;
562
563 end elp_eligible;
564
565 end ben_per_asg_elig;