[Home] [Help]
PACKAGE BODY: APPS.BEN_COLLAPSE_LIFE_EVENT
Source
1 package body ben_collapse_life_event as
2 /* $Header: benclpse.pkb 120.16 2011/06/01 06:58:51 amnaraya ship $ */
3 --
4 g_package varchar2(30) := 'ben_collapse_life_event.';
5 --
6 type g_events_rec is record
7 (ptnl_ler_for_per_id number(15),
8 ler_id number(15),
9 ptnl_ler_for_per_stat_cd varchar2(30),
10 lf_evt_ocrd_dt date,
11 ntfn_dt date,
12 object_version_number number(15));
13 --
14 type g_events_table is table of g_events_rec index by binary_integer;
15 --
16 g_events g_events_table;
17 g_included_events g_events_table;
18 all_expressions_parsed exception;
19 --
20 g_rec benutils.g_batch_ler_rec;
21 --
22 procedure build_life_event_set(p_person_id in number,
23 p_business_group_id in number,
24 p_mode in varchar2,
25 p_effective_date in date) is
26 --
27 -- CWB Changes : Cursor joined to ben_ler_f
28 --
29 cursor c_events is
30 select ptn.ptnl_ler_for_per_id,
31 ptn.ler_id,
32 ptn.ptnl_ler_for_per_stat_cd,
33 ptn.lf_evt_ocrd_dt,
34 ptn.ntfn_dt,
35 ptn.object_version_number
36 from ben_ptnl_ler_for_per ptn,
37 ben_ler_f ler
38 where ptn.business_group_id = p_business_group_id
39 and ptn.person_id = p_person_id
40 and ptn.ler_id = ler.ler_id
41 and p_effective_date
42 between ler.effective_start_date
43 and ler.effective_end_date
44 -- ABSENCES : Do not consider absence life events
45 and ler.typ_cd not in ('COMP', 'ABS','GSP', 'IREC')
46 and ptn.ptnl_ler_for_per_stat_cd not in ('VOIDD','MNL')
47 and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
48 'SCHEDDO',
49 ptn.lf_evt_ocrd_dt,
50 'SCHEDDA',
51 ptn.lf_evt_ocrd_dt,
52 p_effective_date)
53 order by ptn.lf_evt_ocrd_dt asc;
54 --
55 -- CWB Changes End
56 --
57 l_events c_events%rowtype;
58 l_proc varchar2(80) := g_package||'build_life_event_set';
59 l_counter integer;
60 -- Added for bug 1975925
61 l_ptnl_ler_for_per_id number(15);
62 l_ler_id number(15);
63 l_ptnl_ler_for_per_stat_cd varchar2(30);
64 l_lf_evt_ocrd_dt date;
65 l_ntfn_dt date;
66 l_object_version_number number(15);
67 --
68 begin
69 --
70 hr_utility.set_location('Entering '||l_proc,10);
71 --
72 -- Clear g_events table cache
73 --
74 g_events.delete;
75 --
76 open c_events;
77 --
78 l_counter := nvl(g_events.last, 0);
79 hr_utility.set_location('Entering after last',11);
80 loop
81 --
82 fetch c_events into l_events;
83 exit when c_events%notfound;
84 --
85 -- Added for bug 1975925
86 --
87 l_ptnl_ler_for_per_id := l_events.ptnl_ler_for_per_id;
88 l_ler_id := l_events.ler_id;
89 l_ptnl_ler_for_per_stat_cd := l_events.ptnl_ler_for_per_stat_cd;
90 l_lf_evt_ocrd_dt := l_events.lf_evt_ocrd_dt;
91 l_ntfn_dt := l_events.ntfn_dt;
92 l_object_version_number := l_events.object_version_number;
93 l_counter := l_counter + 1;
94 hr_utility.set_location('Entering before assign',11);
95
96 /*
97 g_events(l_counter) := l_events;
98 */
99 g_events(l_counter).ptnl_ler_for_per_id := l_ptnl_ler_for_per_id;
100 g_events(l_counter).ler_id := l_ler_id;
101 g_events(l_counter).ptnl_ler_for_per_stat_cd := l_ptnl_ler_for_per_stat_cd;
102 g_events(l_counter).lf_evt_ocrd_dt := l_lf_evt_ocrd_dt;
103 g_events(l_counter).ntfn_dt := l_ntfn_dt;
104 g_events(l_counter).object_version_number := l_object_version_number;
105 hr_utility.set_location('Entering after assign',11);
106 /*
107 Bug 1975925
108 if g_events.exists(1) then
109 --
110 g_events(g_events.count+1) := l_events;
111 --
112 else
113 --
114 g_events(1) := l_events;
115 --
116 end if;
117 */
118 --
119 end loop;
120 --
121 close c_events;
122 --
123 hr_utility.set_location('Leaving '||l_proc,10);
124 --
125 end build_life_event_set;
126 --
127 function included_in_events(p_ler_id in number) return date is
128 --
129 l_proc varchar2(80) := g_package||'included_in_events';
130 l_date date;
131 --
132 begin
133 --
134 hr_utility.set_location('Entering '||l_proc,10);
135 --
136 -- If the life event is in the set of included life events then return the
137 -- earliest life event occurred date within the set.
138 --
139 for l_count in g_included_events.first..g_included_events.last loop
140 --
141 if g_included_events(l_count).ler_id = p_ler_id then
142 --
143 return g_included_events(l_count).lf_evt_ocrd_dt;
144 --
145 end if;
146 --
147 end loop;
148 --
149 hr_utility.set_location('Leaving '||l_proc,10);
150 --
151 return null;
152 --
153 end included_in_events;
154 -- ----------------------------------------------------------------
155 -- get_first_date
156 -- ----------------------------------------------------------------
157 function get_first_date(p_events in ben_clpse_lf_evt_f%rowtype) return date is
158 --
159 l_proc varchar2(80) := g_package||'get_first_date';
160 --
161 begin
162 --
163 hr_utility.set_location('Entering '||l_proc,10);
164 --
165 for l_count in g_included_events.first..g_included_events.last loop
166 --
167 if g_included_events(l_count).ler_id in
168 (p_events.ler1_id,p_events.ler2_id,
169 p_events.ler3_id,p_events.ler4_id,
170 p_events.ler5_id,p_events.ler6_id,
171 p_events.ler7_id,p_events.ler8_id,
172 p_events.ler9_id,p_events.ler10_id) then
173 --
174 hr_utility.set_location('Leaving '||l_proc,10);
175 --
176 return g_included_events(l_count).lf_evt_ocrd_dt;
177 --
178 exit;
179 --
180 end if;
181 --
182 end loop;
183 --
184 return null;
185 --
186 end get_first_date;
187 -- ----------------------------------------------------------------
188 -- get_last_date
189 -- ----------------------------------------------------------------
190 function get_last_date(p_events in ben_clpse_lf_evt_f%rowtype) return date is
191 --
192 l_proc varchar2(80) := g_package||'get_last_date';
193 --
194 begin
195 --
196 hr_utility.set_location('Entering '||l_proc,10);
197 --
198 for l_count in reverse g_included_events.first..g_included_events.last loop
199 --
200 if g_included_events(l_count).ler_id in
201 (p_events.ler1_id,p_events.ler2_id,
202 p_events.ler3_id,p_events.ler4_id,
203 p_events.ler5_id,p_events.ler6_id,
204 p_events.ler7_id,p_events.ler8_id,
205 p_events.ler9_id,p_events.ler10_id) then
206 --
207 hr_utility.set_location('Leaving '||l_proc,10);
208 --
209 return g_included_events(l_count).lf_evt_ocrd_dt;
210 exit;
211 --
212 end if;
213 --
214 end loop;
215 --
216 return null;
217 --
218 end get_last_date;
219 -- ----------------------------------------------------------------
220 -- perform_collapse
221 -- ----------------------------------------------------------------
222 procedure perform_collapse
223 (p_events in ben_clpse_lf_evt_f%rowtype,
224 p_business_group_id in number,
225 p_person_id in number,
226 p_effective_date in date,
227 p_operation in out nocopy varchar2) is
228 --
229 l_proc varchar2(80) := g_package||'perform_collapse';
230 l_outputs ff_exec.outputs_t;
231 l_effective_start_date date;
232 l_effective_end_date date;
233 l_object_version_number number(15);
234 l_ptnl_ler_for_per_id number(15);
235 l_per_in_ler_id number(15);
236 l_ass_rec per_all_assignments_f%rowtype;
237 l_mnl_dt date;
238 l_dtctd_dt date;
239 l_procd_dt date;
240 l_unprocd_dt date;
241 l_voidd_dt date;
242 l_lf_evt_ocrd_dt date;
243 l_operation varchar2(30);
244 l_ler_rec ben_ler_f%rowtype;
245 l_c1_lf_evt_ocrd_dt date;
246 l_flag boolean;
247 l_not_present boolean;
248 --
249 cursor c1(p_ptnl_ler_for_per_id number) is
250 select pil.per_in_ler_id, pil.lf_evt_ocrd_dt
251 from ben_per_in_ler pil
252 where pil.ptnl_ler_for_per_id = p_ptnl_ler_for_per_id
253 and pil.business_group_id = p_business_group_id
254 and pil.per_in_ler_stat_cd in ('STRTD','PROCD');
255 --
256 -- 5677090 Added this cursor
257 cursor get_all_fut_pils(p_lf_evt_ocrd_dt date, p_curr_per_in_ler_id number) is
258 select pil.per_in_ler_id, pil.lf_evt_ocrd_dt, pil.ntfn_dt, ler.name
259 from ben_per_in_ler pil,
260 ben_ler_f ler
261 where pil.person_id = p_person_id
262 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
263 and pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt
264 and pil.per_in_ler_id <> p_curr_per_in_ler_id
265 and pil.ler_id = ler.ler_id
266 and p_effective_date between ler.effective_start_date and ler.effective_end_date
267 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC', 'SCHEDDU')
268 order by lf_evt_ocrd_dt desc;
269 --
270 l_pil_rec get_all_fut_pils%ROWTYPE;
271 --
272 --Start 6086392
273 l_bckdt_pil_count BINARY_INTEGER;
274 --End 6086392
275
276
277 --Added for Bug 7583015
278 cursor c_chk_evt_procd is
279 select ptnl_ler_for_per_id from ben_ptnl_ler_for_per
280 where person_id=p_person_id
281 and business_group_id=p_events.business_group_id
282 and ler_id=p_events.eval_ler_id
283 and ptnl_ler_for_per_stat_cd in ('PROCD','STRTD')
284 and lf_evt_ocrd_dt=l_lf_evt_ocrd_dt;
285 l_win_ptnl_ler_for_per_id number;
286
287 cursor c_get_pil_id is
288 select per_in_ler_id from ben_per_in_ler
289 where ptnl_ler_for_per_id=l_win_ptnl_ler_for_per_id
290 and person_id=p_person_id
291 and business_group_id=p_events.business_group_id
292 and ler_id=p_events.eval_ler_id;
293
294 l_win_per_in_ler_id number;
295 --End 7583015
296
297 /*Bug 9372154*/
298 cursor c_ler_status(c_ptnl_id number) is
299 select 'Y' from ben_ptnl_ler_for_per
300 where ptnl_ler_for_per_id=c_ptnl_id
301 and ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD');
302 l_flag1 varchar2(1);
303
304 --Start bug 12538701
305 cursor c_ptnl_ler_for_per_ovn(c_ptnl_ler_for_per_id number) is
306 SELECT object_version_number
307 FROM ben_ptnl_ler_for_per
308 WHERE ptnl_ler_for_per_id = c_ptnl_ler_for_per_id;
309 l_new_object_version_number number(15);
310 --End bug 12538701
311
312 begin
313 --
314 l_flag := false;
315 l_not_present := true;
316 hr_utility.set_location('Entering '||l_proc,10);
317 --
318 -- First set operation to event collision method
319 --
320 if p_events.eval_cd = 'RL' then
321 --
322 -- Call the rule and get the evaluation cd
323 --
324 ben_person_object.get_object(p_person_id => p_person_id,
325 p_rec => l_ass_rec);
326 --
327 if l_ass_rec.assignment_id is null then
328 --
329 -- Get benefits assignment
330 --
331 ben_person_object.get_benass_object(p_person_id => p_person_id,
332 p_rec => l_ass_rec);
333 --
334 end if;
335 --
336 l_outputs := benutils.formula
337 (p_formula_id => p_events.eval_rl,
338 p_effective_date => p_effective_date,
339 p_business_group_id => p_business_group_id,
340 p_ler_id => p_events.eval_ler_id,
341 p_assignment_id => l_ass_rec.assignment_id);
342 --
343 if l_outputs(l_outputs.first).value not in ('V','D') then
344 --
345 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
346 fnd_message.set_token('RL','p_events.eval_rl');
347 fnd_message.set_token('PROC',l_proc);
348 raise ben_manage_life_events.g_record_error;
349 --
350 end if;
351 --
352 l_operation := l_outputs(l_outputs.first).value;
353 --
354 else
355 --
356 l_operation := p_events.eval_cd;
357 --
358 end if;
359 --
360 p_operation := l_operation;
361 --
362 -- Derive the life event occured date for the new potential life event
363 --
364 if p_events.eval_ler_det_cd = 'RL' then
365 --
366 -- Call the rule and get the evaluation cd
367 --
368 ben_person_object.get_object(p_person_id => p_person_id,
369 p_rec => l_ass_rec);
370 --
371 if l_ass_rec.assignment_id is null then
372 --
373 -- Get benefits assignment
374 --
375 ben_person_object.get_benass_object(p_person_id => p_person_id,
376 p_rec => l_ass_rec);
377 --
378 end if;
379 --
380 l_outputs := benutils.formula
381 (p_formula_id => p_events.eval_ler_det_rl,
382 p_effective_date => p_effective_date,
383 p_business_group_id => p_business_group_id,
384 p_ler_id => p_events.eval_ler_id,
385 p_assignment_id => l_ass_rec.assignment_id);
386 --
387 begin
388 --
389 hr_utility.set_location('loutput value1 '|| l_outputs(l_outputs.first).name,10);
390 hr_utility.set_location('loutput value2 '|| l_outputs(l_outputs.first).value,10);
391 if l_outputs(l_outputs.first).name = 'LIFE_EVENT_OCCURRED_DATE' then
392 --
393 l_lf_evt_ocrd_dt :=
394 fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
395 --
396 else
397 --
398 -- Account for cases where formula returns an unknown
399 -- variable name
400 --
401 fnd_message.set_name('BEN','BEN_92310_FORMULA_RET_PARAM');
402 fnd_message.set_token('PROC',l_proc);
403 fnd_message.set_token('FORMULA',p_events.eval_ler_det_rl);
404 fnd_message.set_token('PARAMETER',l_outputs(l_outputs.first).name);
405 fnd_message.raise_error;
406 --
407 end if;
408 --
409 -- Code for type casting errors from formula return variables
410 --
411 exception
412 --
413 when others then
414 --
415 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
416 fnd_message.set_token('PROC',l_proc);
417 fnd_message.set_token('FORMULA',p_events.eval_ler_det_rl);
418 fnd_message.set_token('PARAMETER',l_outputs(l_outputs.first).name);
419 fnd_message.raise_error;
420 --
421 end;
422 --
423 elsif p_events.eval_ler_det_cd = 'ELED' then
424 -- Earliest life event occured date of those that match
425 l_lf_evt_ocrd_dt := get_first_date(p_events => p_events);
426
427 elsif p_events.eval_ler_det_cd = 'LLED' then
428 -- Latest life event occurred date of those that match
429 l_lf_evt_ocrd_dt := get_last_date(p_events => p_events);
430
431 elsif p_events.eval_ler_det_cd = 'ELEDR' then
432 --
433 -- Earliest life event occured date or the date of the resulting life
434 -- event if it was in the set of matched life events.
435 --
436 l_lf_evt_ocrd_dt :=
437 nvl(included_in_events(p_ler_id => p_events.eval_ler_id),
438 get_first_date(p_events => p_events));
439 --
440 elsif p_events.eval_ler_det_cd = 'LLEDR' then
441 --
442 -- Latest life event occured date or the date of the resulting life
443 -- event if it was in the set of matched life events.
444 --
445 l_lf_evt_ocrd_dt :=
446 nvl(included_in_events(p_ler_id => p_events.eval_ler_id),
447 get_last_date(p_events => p_events));
448 --
449 else
450 --
451 l_lf_evt_ocrd_dt := p_effective_date;
452 --
453 end if;
454 --
455 -- Now we have the evaluation we have to loop through the included table
456 -- and either delete or replace the included life events
457 --
458 for l_count in g_included_events.first..g_included_events.last loop
459 --
460 -- Only delete or void an event if it matches the set of life events
461 --
462
463 if g_included_events(l_count).ler_id in
464 (p_events.ler1_id,p_events.ler2_id,
465 p_events.ler3_id,p_events.ler4_id,
466 p_events.ler5_id,p_events.ler6_id,
467 p_events.ler7_id,p_events.ler8_id,
468 p_events.ler9_id,p_events.ler10_id) then
469 --
470 -- Get the life event details
471 --
472 ben_life_object.get_object(p_ler_id => g_included_events(l_count).ler_id,
473 p_rec => l_ler_rec);
474 --
475 -- If the event is a real per in ler then we need to backout the
476 -- active per in ler. We always do a void even if the option is set
477 -- to be a delete as otherwise constraints between per_in_ler and
478 -- ptnl_per_for_ler fail.
479 --
480 if g_included_events(l_count).ptnl_ler_for_per_stat_cd = 'PROCD' then
481 --
482 -- Backout active per in ler
483 --
484 -- First get correct per in ler id
485 --
486 open c1(g_included_events(l_count).ptnl_ler_for_per_id);
487 fetch c1 into l_per_in_ler_id, l_c1_lf_evt_ocrd_dt;
488 close c1;
489 --
490 hr_utility.set_location('Backout All Future PILs ', 50);
491 --
492 -- 5677090 Backout LE shud be called in reverse order of occurance.
493 -- Before backing out the LEs with same ocrd-dt, we shud backout all future
494 -- LEs. Lets make them 'Unprocessed'.
495 -- These LEs will not show up in backed-out list in the benauthe (a small bug).
496 --
497 open get_all_fut_pils(l_c1_lf_evt_ocrd_dt, l_per_in_ler_id);
498 loop
499 --
500 hr_utility.set_location('per_in_ler_id ' || l_pil_rec.per_in_ler_id, 50);
501 hr_utility.set_location('lf_evt_ocrd_dt ' || l_pil_rec.lf_evt_ocrd_dt, 50);
502 --
503 fetch get_all_fut_pils into l_pil_rec;
504 exit when get_all_fut_pils%notfound;
505 --
506 hr_utility.set_location('Backing Out UNPROCD Future '|| l_pil_rec.per_in_ler_id, 50);
507 ben_back_out_life_event.back_out_life_events
508 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
509 p_bckt_per_in_ler_id => null,
510 p_bckt_stat_cd => 'UNPROCD',
511 p_business_group_id => p_business_group_id,
512 p_effective_date => p_effective_date);
513 --
514 --Start 6086392
515 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
516 l_bckdt_pil_count := l_bckdt_pil_count +1;
517 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_pil_rec.per_in_ler_id;
518 --End 6086392
519
520 fnd_message.set_name('BEN','BEN_92433_ACTIVE_LER_BCKDT');
521 fnd_message.set_token('NAME',l_pil_rec.name);
522 fnd_message.set_token('OCCURRED_DATE',l_pil_rec.lf_evt_ocrd_dt);
523 fnd_message.set_token('NOTIFICATION_DATE',l_pil_rec.ntfn_dt);
524 benutils.write(fnd_message.get);
525 --
526 end loop;
527 close get_all_fut_pils;
528 --
529 hr_utility.set_location('PROCD eval ler_id '|| p_events.eval_ler_id, 50);
530 hr_utility.set_location('PROCD g_included_events '|| g_included_events(l_count).ler_id, 50);
531 /* Bug 7583015: Added if else condition.VOIDD if not winning LE, else backout the winning LE to UNPROCD*/
532 if(g_included_events(l_count).ler_id <> p_events.eval_ler_id) then
533 hr_utility.set_location('Backing Out VOID '|| l_per_in_ler_id, 50);
534 ben_back_out_life_event.back_out_life_events
535 (p_per_in_ler_id => l_per_in_ler_id,
536 p_bckt_per_in_ler_id => null,
537 p_bckt_stat_cd => 'VOIDD',
538 p_business_group_id => p_business_group_id,
539 p_effective_date => p_effective_date);
540 l_flag := true;
541 else
542 hr_utility.set_location('Backing Out UNPROCD '|| l_per_in_ler_id, 50);
543 ben_back_out_life_event.back_out_life_events
544 (p_per_in_ler_id => l_per_in_ler_id,
545 p_bckt_per_in_ler_id => null,
546 p_bckt_stat_cd => 'UNPROCD',
547 p_business_group_id => p_business_group_id,
548 p_effective_date => p_effective_date);
549 l_flag := true;
550 l_not_present := false;
551
552 --Start bug 12538701
553 if l_lf_evt_ocrd_dt <> g_included_events(l_count).lf_evt_ocrd_dt then
554 open c_ptnl_ler_for_per_ovn(g_included_events(l_count).ptnl_ler_for_per_id);
555 fetch c_ptnl_ler_for_per_ovn into l_new_object_version_number;
556 close c_ptnl_ler_for_per_ovn;
557 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
558 (p_ptnl_ler_for_per_id => g_included_events(l_count).
559 ptnl_ler_for_per_id,
560 p_effective_date => p_effective_date,
561 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
562 p_object_version_number => l_new_object_version_number);
563 end if;
564 --End bug 12538701
565
566 end if;
567 --
568 fnd_message.set_name('BEN','BEN_92433_ACTIVE_LER_BCKDT');
569 fnd_message.set_token('NAME',l_ler_rec.name);
570 fnd_message.set_token('OCCURRED_DATE',g_included_events(l_count).lf_evt_ocrd_dt);
571 fnd_message.set_token('NOTIFICATION_DATE',g_included_events(l_count).ntfn_dt);
572 benutils.write(fnd_message.get);
573 --
574 g_rec.person_id := p_person_id;
575 g_rec.ler_id := g_included_events(l_count).ler_id;
576 g_rec.lf_evt_ocrd_dt := g_included_events(l_count).lf_evt_ocrd_dt;
577 g_rec.replcd_flag := 'N';
578 g_rec.crtd_flag := 'N';
579 g_rec.tmprl_flag := 'N';
580 g_rec.dltd_flag := 'N';
581 g_rec.open_and_clsd_flag := 'N';
582 g_rec.not_crtd_flag := 'N';
583 g_rec.clsd_flag := 'N';
584 g_rec.stl_actv_flag := 'N';
585 g_rec.clpsd_flag := 'Y';
586 g_rec.clsn_flag := 'N';
587 g_rec.no_effect_flag := 'N';
588 g_rec.cvrge_rt_prem_flag := 'N';
589 g_rec.business_group_id := p_business_group_id;
590 g_rec.effective_date := p_effective_date;
591 --
592 benutils.write(p_rec => g_rec);
593 --
594 elsif l_operation = 'V' then
595 --
596 -- Update the life event and set its status to voided
597 --
598 /* Bug 7583015: Added if else condition.VOIDD if not winning LE, else backout the winning LE to UNPROCD*/
599 if(g_included_events(l_count).ler_id <> p_events.eval_ler_id) then
600 hr_utility.set_location('In V VOIDD ', 50);
601 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
602 (p_ptnl_ler_for_per_id => g_included_events(l_count).
603 ptnl_ler_for_per_id,
604 p_ptnl_ler_for_per_stat_cd => 'VOIDD',
605 p_voidd_dt => p_effective_date,
606 p_effective_date => p_effective_date,
607 p_object_version_number => g_included_events(l_count).
608 object_version_number);
609 l_flag := true;
610 --
611 fnd_message.set_name('BEN','BEN_92434_PTNL_LER_VOIDED');
612 fnd_message.set_token('NAME',l_ler_rec.name);
613 fnd_message.set_token('OCCURRED_DATE',g_included_events(l_count).
614 lf_evt_ocrd_dt);
615 fnd_message.set_token('NOTIFICATION_DATE',g_included_events(l_count).
616 ntfn_dt);
617 benutils.write(fnd_message.get);
618 else
619 if (g_included_events(l_count).ler_id = p_events.eval_ler_id) then
620 hr_utility.set_location('Event not voided '||g_included_events(l_count).ler_id, 50);
621 hr_utility.set_location('p_events.eval_ler_id '||p_events.eval_ler_id, 50);
622 hr_utility.set_location('state '||g_included_events(l_count).ptnl_ler_for_per_stat_cd, 50);
623 l_flag := true;
624 l_not_present := false;
625
626 /*Bug 9372154: If Winner LE is not processed then update the LE occured with the
627 date determined from the LE occured date determination code */
628 open c_ler_status(g_included_events(l_count).ptnl_ler_for_per_id);
629 fetch c_ler_status into l_flag1;
630 if(c_ler_status%found) then
631 hr_utility.set_location('Winner LE is not processed ', 50);
632 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
633 (p_ptnl_ler_for_per_id => g_included_events(l_count).
634 ptnl_ler_for_per_id,
635 p_effective_date => p_effective_date,
636 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
637 p_object_version_number => g_included_events(l_count).
638 object_version_number);
639 end if;
640 close c_ler_status;
641 end if;
642 end if;
643 --
644 elsif l_operation = 'D' then
645 --
646 ben_ptnl_ler_for_per_api.delete_ptnl_ler_for_per
647 (p_ptnl_ler_for_per_id => g_included_events(l_count).
648 ptnl_ler_for_per_id,
649 p_effective_date => p_effective_date,
650 p_object_version_number => g_included_events(l_count).
651 object_version_number);
652 --
653 fnd_message.set_name('BEN','BEN_92435_PTNL_LER_DELETED');
654 fnd_message.set_token('NAME',l_ler_rec.name);
655 fnd_message.set_token('OCCURRED_DATE',g_included_events(l_count).
656 lf_evt_ocrd_dt);
657 fnd_message.set_token('NOTIFICATION_DATE',g_included_events(l_count).
658 ntfn_dt);
659 benutils.write(fnd_message.get);
660 --
661 end if;
662 --
663 end if;
664 --
665 end loop;
666 --
667 -- Now lets create the new collapsed life event
668 --
669 /* Bug 7583015: Added if else condition for creating the potential*/
670 if(not l_flag) then
671 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
672 (p_validate => false,
673 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
674 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
675 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
676 p_ler_id => p_events.eval_ler_id,
677 p_dtctd_dt => p_effective_date,
678 p_ntfn_dt => trunc(sysdate),
679 p_person_id => p_person_id,
680 p_business_group_id => p_events.business_group_id,
681 p_object_version_number => l_object_version_number,
682 p_effective_date => p_effective_date,
683 p_program_application_id => fnd_global.prog_appl_id,
684 p_program_id => fnd_global.conc_program_id,
685 p_request_id => fnd_global.conc_request_id,
686 p_program_update_date => sysdate);
687 else
688 if(l_not_present) then
689 open c_chk_evt_procd;
690 fetch c_chk_evt_procd into l_win_ptnl_ler_for_per_id;
691 if(c_chk_evt_procd%found) then
692 close c_chk_evt_procd;
693 open c_get_pil_id;
694 fetch c_get_pil_id into l_win_per_in_ler_id;
695 if(c_get_pil_id%found) then
696 ben_back_out_life_event.back_out_life_events
697 (p_per_in_ler_id => l_win_per_in_ler_id,
698 p_bckt_per_in_ler_id => null,
699 p_bckt_stat_cd => 'UNPROCD',
700 p_business_group_id => p_business_group_id,
701 p_effective_date => p_effective_date);
702 end if;
703 close c_get_pil_id;
704 else
705 close c_chk_evt_procd;
706 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
707 (p_validate => false,
708 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
709 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
710 p_ptnl_ler_for_per_stat_cd => 'DTCTD',
711 p_ler_id => p_events.eval_ler_id,
712 p_dtctd_dt => p_effective_date,
713 p_ntfn_dt => trunc(sysdate),
714 p_person_id => p_person_id,
715 p_business_group_id => p_events.business_group_id,
716 p_object_version_number => l_object_version_number,
717 p_effective_date => p_effective_date,
718 p_program_application_id => fnd_global.prog_appl_id,
719 p_program_id => fnd_global.conc_program_id,
720 p_request_id => fnd_global.conc_request_id,
721 p_program_update_date => sysdate);
722 end if;
723 end if;
724 end if;
725 --
726
727
728 hr_utility.set_location('Leaving '||l_proc,10);
729 --
730 end perform_collapse;
731 --
732 procedure add_to_included(p_rec in g_events_rec) is
733 --
734 l_proc varchar2(80) := g_package||'add_to_include';
735 --
736 begin
737 --
738 hr_utility.set_location('Entering '||l_proc,10);
739 --
740 -- Add record into included list. This is the list from which we will be
741 -- voiding or deleteing record from.
742 --
743 if g_included_events.exists(1) then
744 --
745 g_included_events(g_included_events.count+1) := p_rec;
746 --
747 else
748 --
749 g_included_events(1) := p_rec;
750 --
751 end if;
752 --
753 hr_utility.set_location('Leaving '||l_proc,10);
754 --
755 end add_to_included;
756 --
757 procedure add_expression(p_value in varchar2,
758 p_expression in out nocopy varchar2) is
759 --
760 l_proc varchar2(80) := g_package||'add_expression';
761 --
762 begin
763 --
764 if p_value is not null then
765 --
766 -- Handle boolean conditions
767 --
768 if p_value = 'AND' then
769 --
770 p_expression := p_expression||' and ';
771 --
772 elsif p_value = 'OR' then
773 --
774 p_expression := p_expression||' or ';
775 --
776 else
777 --
778 p_expression := p_expression||p_value;
779 --
780 end if;
781 --
782 else
783 --
784 -- No more expressions need to be parsed
785 --
786 raise all_expressions_parsed;
787 --
788 end if;
789 --
790 end add_expression;
791 --
792 function build_boolean_expression(p_events in ben_clpse_lf_evt_f%rowtype,
793 p_in_string in varchar2)
794 return varchar2 is
795 --
796 l_proc varchar2(80) := g_package||'build_boolean_expression';
797 l_expression varchar2(32000);
798 --
799 begin
800 --
801 hr_utility.set_location('Entering '||l_proc,10);
802 --
803 add_expression(p_value => p_events.ler1_id,
804 p_expression => l_expression);
805 --
806 add_expression(p_value => p_in_string,
807 p_expression => l_expression);
808 --
809 add_expression(p_value => p_events.bool1_cd,
810 p_expression => l_expression);
811 --
812 add_expression(p_value => p_events.ler2_id,
813 p_expression => l_expression);
814 --
815 add_expression(p_value => p_in_string,
816 p_expression => l_expression);
817 --
818 add_expression(p_value => p_events.bool2_cd,
819 p_expression => l_expression);
820 --
821 add_expression(p_value => p_events.ler3_id,
822 p_expression => l_expression);
823 --
824 add_expression(p_value => p_in_string,
825 p_expression => l_expression);
826 --
827 add_expression(p_value => p_events.bool3_cd,
828 p_expression => l_expression);
829 --
830 add_expression(p_value => p_events.ler4_id,
831 p_expression => l_expression);
832 --
833 add_expression(p_value => p_in_string,
834 p_expression => l_expression);
835 --
836 add_expression(p_value => p_events.bool4_cd,
837 p_expression => l_expression);
838 --
839 add_expression(p_value => p_events.ler5_id,
840 p_expression => l_expression);
841 --
842 add_expression(p_value => p_in_string,
843 p_expression => l_expression);
844 --
845 add_expression(p_value => p_events.bool5_cd,
846 p_expression => l_expression);
847 --
848 add_expression(p_value => p_events.ler6_id,
849 p_expression => l_expression);
850 --
851 add_expression(p_value => p_in_string,
852 p_expression => l_expression);
853 --
854 add_expression(p_value => p_events.bool6_cd,
855 p_expression => l_expression);
856 --
857 add_expression(p_value => p_events.ler7_id,
858 p_expression => l_expression);
859 --
860 add_expression(p_value => p_in_string,
861 p_expression => l_expression);
862 --
863 add_expression(p_value => p_events.bool7_cd,
864 p_expression => l_expression);
865 --
866 add_expression(p_value => p_events.ler8_id,
867 p_expression => l_expression);
868 --
869 add_expression(p_value => p_in_string,
870 p_expression => l_expression);
871 --
872 add_expression(p_value => p_events.bool8_cd,
873 p_expression => l_expression);
874 --
875 add_expression(p_value => p_events.ler9_id,
876 p_expression => l_expression);
877 --
878 add_expression(p_value => p_in_string,
879 p_expression => l_expression);
880 --
881 add_expression(p_value => p_events.bool9_cd,
882 p_expression => l_expression);
883 --
884 add_expression(p_value => p_events.ler10_id,
885 p_expression => l_expression);
886 --
887 add_expression(p_value => p_in_string,
888 p_expression => l_expression);
889 --
890 return l_expression;
891 --
892 hr_utility.set_location('Leaving '||l_proc,10);
893 --
894 exception
895 --
896 when all_expressions_parsed then
897 --
898 hr_utility.set_location('Leaving '||l_proc,10);
899 return l_expression;
900 --
901 end build_boolean_expression;
902 --
903 function parse_expression(p_expression in varchar2) return boolean is
904 --
905 l_proc varchar2(80) := g_package||'parse_expression';
906 l_dynamic_sql varchar2(32000);
907 l_rows integer;
908 --
909 begin
910 --
911 hr_utility.set_location('Entering '||l_proc,10);
912 --
913 l_dynamic_sql := 'select 1 from sys.dual where '||p_expression;
914 --
915 execute immediate l_dynamic_sql into l_rows;
916 --
917 if l_rows = 1 then
918 --
919 return true;
920 --
921 end if;
922 --
923 hr_utility.set_location('Entering '||l_proc,10);
924 --
925 exception
926 --
927 when no_data_found then
928 --
929 return false;
930 --
931 when others then
932 --
933 raise;
934 --
935 end parse_expression;
936 --
937 function parse_and_match(p_events in ben_clpse_lf_evt_f%rowtype,
938 p_min_lf_evt_ocrd_dt in date)
939 return boolean is
940 --
941 l_proc varchar2(80) := g_package||'parse_and_match';
942 l_in_string varchar2(1000) := ' in (';
943 l_first_entry boolean := false;
944 l_success boolean := true;
945 l_expression varchar2(32000);
946 --
947 begin
948 --
949 hr_utility.set_location('Entering '||l_proc,10);
950 --
951 g_included_events.delete;
952 --
953 -- Steps to build in string
954 --
955 for l_count in g_events.first..g_events.last loop
956 -- according to bill, tolerance should be used to swing around the
957 -- min date. Don't consider ones older than that.
958 if p_min_lf_evt_ocrd_dt + nvl(p_events.tlrnc_dys_num,0) >=
959 g_events(l_count).lf_evt_ocrd_dt and
960 p_min_lf_evt_ocrd_dt - nvl(p_events.tlrnc_dys_num,0) <=
961 g_events(l_count).lf_evt_ocrd_dt then
962 --
963 -- Account for first entry, this doesn't need to have a leading comma
964 --
965 if not l_first_entry then
966 --
967 l_first_entry := true;
968 l_in_string := l_in_string||g_events(l_count).ler_id;
969 --
970 else
971 --
972 -- We need a leading comma to be inserted into the string
973 --
974 l_in_string := l_in_string||','||g_events(l_count).ler_id;
975 --
976 end if;
977 --
978 add_to_included(p_rec => g_events(l_count));
979 --
980 end if;
981 --
982 end loop;
983 --
984 l_in_string := l_in_string||') ';
985 --
986 -- Only attempt to build the expression if we have some included events
987 --
988 if l_first_entry then
989 --
990 l_expression := build_boolean_expression(p_events => p_events,
991 p_in_string => l_in_string);
992 --
993 -- Now we must parse the boolean expression and return whether we have a
994 -- match.
995 --
996 l_success := parse_expression(p_expression => l_expression);
997 --
998 else
999 --
1000 l_success := false;
1001 --
1002 end if;
1003 --
1004 hr_utility.set_location(' l_exp '||l_expression,10);
1005 hr_utility.set_location('Leaving '||l_proc,10);
1006 --
1007 return l_success;
1008 --
1009 end parse_and_match;
1010 --
1011 procedure collapse_event
1012 (p_person_id in number,
1013 p_business_group_id in number,
1014 p_mode in varchar2,
1015 p_min_lf_evt_ocrd_dt in date,
1016 p_effective_date in date) is
1017 --
1018 cursor c_events is
1019 select *
1020 from ben_clpse_lf_evt_f clp
1021 where clp.business_group_id = p_business_group_id
1022 and p_effective_date
1023 between clp.effective_start_date
1024 and clp.effective_end_date
1025 order by seq;
1026 --
1027 l_events c_events%rowtype;
1028 l_proc varchar2(80) := g_package||'collapse_event';
1029 l_match boolean := false;
1030 l_operation varchar2(30) := 'NO_MATCH';
1031 --
1032 begin
1033 --
1034 hr_utility.set_location('Entering '||l_proc,10);
1035 --
1036 -- Test if at least one life event was included in the set
1037 --
1038 --
1039 open c_events;
1040 --
1041 loop
1042 --
1043 fetch c_events into l_events;
1044 exit when c_events%notfound;
1045 --
1046 --
1047 -- Build potential set of life events structure, this will be used as
1048 -- part of the parse routine
1049 --
1050 build_life_event_set(p_person_id => p_person_id,
1051 p_business_group_id => p_business_group_id,
1052 p_mode => p_mode,
1053 p_effective_date => p_effective_date);
1054 --
1055 if g_events.count = 0 then
1056 --
1057 -- There are no events to collapse
1058 --
1059 exit;
1060 --
1061 end if;
1062 --
1063 -- Now we have a record lets try and parse it and get a match
1064 --
1065 l_match := parse_and_match
1066 (p_events => l_events,
1067 p_min_lf_evt_ocrd_dt => p_min_lf_evt_ocrd_dt);
1068 --
1069 if l_match then
1070 --
1071 perform_collapse(p_events => l_events,
1072 p_business_group_id => p_business_group_id,
1073 p_person_id => p_person_id,
1074 p_effective_date => p_effective_date,
1075 p_operation => l_operation);
1076 --
1077 end if;
1078 --
1079 end loop;
1080 --
1081 close c_events;
1082 --
1083 hr_utility.set_location('Leaving '||l_proc,10);
1084 --
1085 end collapse_event;
1086 --
1087 -- Main routine. This performs the collapse and returns to the calling
1088 -- program.
1089 --
1090 procedure main(p_person_id in number,
1091 p_business_group_id in number,
1092 p_mode in varchar2,
1093 p_min_lf_evt_ocrd_dt in date,
1094 p_effective_date in date) is
1095 --
1096 l_proc varchar2(80) := g_package||'main';
1097 --
1098 begin
1099 --
1100 hr_utility.set_location('Entering '||l_proc,10);
1101 --
1102 -- Steps to perform process
1103 --
1104 -- 1) Check if we have rows in the collapse life event table
1105 -- 2) If so populate the potential life events cache with all potential
1106 -- life events
1107 -- 3) Retrieve rows and parse dynamic sql to test for solution
1108 --
1109 collapse_event(p_person_id => p_person_id,
1110 p_business_group_id => p_business_group_id,
1111 p_mode => p_mode,
1112 p_min_lf_evt_ocrd_dt => p_min_lf_evt_ocrd_dt,
1113 p_effective_date => p_effective_date);
1114 --
1115 hr_utility.set_location('Leaving '||l_proc,10);
1116 --
1117 end main;
1118 --
1119 end ben_collapse_life_event;