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