[Home] [Help]
PACKAGE BODY: APPS.BEN_EVALUATE_PTNL_LF_EVT
Source
1 package body ben_evaluate_ptnl_lf_evt as
2 /* $Header: benptnle.pkb 120.21.12010000.3 2008/08/05 14:51:01 ubhat ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' ben_evaluate_ptnl_lf_evt.';
7 g_rec benutils.g_batch_ler_rec;
8
9 -- ----------------------------------------------------------------------------
10 -- |------------------------< update_ptnl_per_for_ler >-----------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure update_ptnl_per_for_ler
14 (p_ptnl_rec IN OUT NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
15 ,p_effective_date IN DATE) is
16 --
17 l_proc varchar2(72) := g_package||'update_ptnl_per_for_ler';
18 --
19 l_mnl_dt date;
20 l_dtctd_dt date;
21 l_procd_dt date;
22 l_unprocd_dt date;
23 l_voidd_dt date;
24 --
25 begin
26 --
27 l_procd_dt := trunc(sysdate);
28 --
29 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
30 (p_validate => false
31 ,p_ptnl_ler_for_per_id => p_ptnl_rec.ptnl_ler_for_per_id
32 ,p_lf_evt_ocrd_dt => p_ptnl_rec.lf_evt_ocrd_dt
33 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
34 ,p_procd_dt => p_effective_date
35 ,p_ler_id => p_ptnl_rec.ler_id
36 ,p_person_id => p_ptnl_rec.person_id
37 ,p_business_group_id => p_ptnl_rec.business_group_id
38 ,p_object_version_number => p_ptnl_rec.object_version_number
39 ,p_effective_date => p_effective_date
40 ,p_program_application_id => fnd_global.prog_appl_id
41 ,p_program_id => fnd_global.conc_program_id
42 ,p_request_id => fnd_global.conc_request_id
43 ,p_program_update_date => sysdate);
44 --
45 end update_ptnl_per_for_ler;
46 --
47 -- ----------------------------------------------------------------------------
48 -- |------------------< absences_eval_ptnl_per_for_ler >----------------------|
49 -- ----------------------------------------------------------------------------
50 --
51 procedure absences_eval_ptnl_per_for_ler(p_validate in boolean default false
52 ,p_person_id in number
53 ,p_business_group_id in number
54 ,p_ler_id in number default null
55 ,p_mode in varchar2
56 ,p_effective_date in date
57 ,p_created_ler_id out NOCOPY number) is
58 --
59 l_min_lf_evt_ocrd_dt date := null;
60 --
61 cursor get_all_potential is
62 select ler.ovridg_le_flag,
63 pfl.ler_id,
64 pfl.ptnl_ler_for_per_id,
65 pfl.lf_evt_ocrd_dt,
66 pfl.object_version_number,
67 ler.ler_eval_rl,
68 pfl.creation_date,
69 pfl.ptnl_ler_for_per_stat_cd,
70 pfl.ntfn_dt,
71 pfl.dtctd_dt,
72 pfl.voidd_dt,
73 ler.name,
74 pfl.trgr_table_pk_id, -- it is absence_attendance_id
75 ler.lf_evt_oper_cd, -- 9999 lf_evt_oper_cd,
76 ler.typ_cd
77 from ben_ptnl_ler_for_per pfl,
78 ben_ler_f ler
79 where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
80 and pfl.person_id = p_person_id
81 and pfl.ler_id = ler.ler_id
82 and p_effective_date
83 between ler.effective_start_date
84 and ler.effective_end_date
85 and pfl.lf_evt_ocrd_dt <= p_effective_date
86 --
87 -- 9999 Do we need to filter based on effective_date, as all potentials
88 -- need to be processed. Also first void all potentials which are not
89 -- processed and are corrections.
90 --
91 and pfl.ler_id <> ben_manage_life_events.g_ler_id
92 and ler.typ_cd = 'ABS'
93 order by pfl.lf_evt_ocrd_dt asc,
94 decode(ler.lf_evt_oper_cd,'DELETE',3,'START',2,1) desc;
95
96 --
97 l_potent get_all_potential%rowtype;
98 l_potent_temp get_all_potential%rowtype;
99 --
100 TYPE l_ppl_rec is TABLE OF get_all_potential%rowtype
101 INDEX BY BINARY_INTEGER;
102 --
103 l_ppl_table l_ppl_rec;
104 l_next_row binary_integer;
105 --
106 cursor c_get_min_ptnl is
107 select min(ptn.lf_evt_ocrd_dt)
108 from ben_ptnl_ler_for_per ptn,
109 ben_ler_f ler
110 where ptn.person_id = p_person_id
111 and ptn.ler_id = ler.ler_id
112 and p_effective_date
113 between ler.effective_start_date
114 and ler.effective_end_date
115 and ler.typ_cd = 'ABS'
116 and ptn.business_group_id = p_business_group_id
117 and ptn.lf_evt_ocrd_dt <= p_effective_date
118 and ptn.ler_id <> ben_manage_life_events.g_ler_id
119 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
120 --
121 cursor c_get_ptnl_for_aba is
122 select ptn.ptnl_ler_for_per_id
123 from ben_ptnl_ler_for_per ptn,
124 ben_ler_f ler
125 where ptn.person_id = p_person_id
126 and ptn.business_group_id = p_business_group_id
127 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
128 and p_effective_date
129 between ler.effective_start_date
130 and ler.effective_end_date
131 and ler.typ_cd = 'ABS'
132 and ptn.ler_id = ler.ler_id
133 and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id
134 and ler.lf_evt_oper_cd = 'DELETE';
135 --
136 cursor c_get_ptnl_for_del_aba is
137 select ptn.*
138 from ben_ptnl_ler_for_per ptn
139 where ptn.person_id = p_person_id
140 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
141 and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id;
142 --
143 --
144 cursor c_winning_ptnl_of_same_type is
145 select ler.lf_evt_oper_cd,ptn.*
146 from ben_ptnl_ler_for_per ptn,
147 ben_ler_f ler
148 where ptn.person_id = p_person_id
149 and ptn.ptnl_ler_for_per_stat_cd <> 'VOIDD' -- 9999(it should work) not in ('PROCD','VOIDD')
150 and ptn.trgr_table_pk_id = l_potent.trgr_table_pk_id
151 -- not needed 9999 delete it and ptn.ptnl_ler_for_per_id <> l_potent.ptnl_ler_for_per_id
152 and ptn.ler_id = l_potent.ler_id
153 and ler.ler_id = ptn.ler_id
154 and p_effective_date between ler.effective_start_date and
155 ler.effective_end_date
156 order by ptn.ptnl_ler_for_per_id desc;
157 --
158 l_winning_ptnl_rec c_winning_ptnl_of_same_type%rowtype;
159 l_next_ptnl_rec c_winning_ptnl_of_same_type%rowtype;
160
161 cursor c_reopened_abs is
162 select 'x'
163 from per_absence_attendances
164 where absence_attendance_id = l_winning_ptnl_rec.trgr_table_pk_id
165 and date_end is null;
166
167 cursor c_get_ptnl_for_win_aba is
168 select ptn.*
169 from ben_ptnl_ler_for_per ptn
170 where ptn.person_id = p_person_id
171 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD')
172 and ptn.ler_id = l_winning_ptnl_rec.ler_id
173 and ptn.trgr_table_pk_id = l_winning_ptnl_rec.trgr_table_pk_id
174 and ptn.ptnl_ler_for_per_id <> l_winning_ptnl_rec.ptnl_ler_for_per_id;
175 --
176 cursor c_get_procd_pils_for_aba is
177 select pil.lf_evt_ocrd_dt
178 from ben_per_in_ler pil
179 where pil.person_id = p_person_id
180 and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
181 and pil.business_group_id = p_business_group_id
182 and pil.trgr_table_pk_id = l_potent.trgr_table_pk_id
183 order by pil.lf_evt_ocrd_dt asc;
184 --
185 cursor c_get_procd_pils_for_win is
186 select pil.lf_evt_ocrd_dt
187 from ben_per_in_ler pil
188 where pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
189 and pil.business_group_id = p_business_group_id
190 and pil.trgr_table_pk_id = l_winning_ptnl_rec.trgr_table_pk_id
191 and pil.ler_id = l_winning_ptnl_rec.ler_id
192 order by pil.lf_evt_ocrd_dt asc;
193 --
194 l_procd_lf_evt_ocrd_dt date;
195 --
196 cursor c_pils_to_backout(p_min_lf_evt_ocrd_dt date, p_ler_id number ) is
197 select pil.*
198 from ben_per_in_ler pil,
199 ben_ler_f ler
200 where pil.business_group_id = p_business_group_id
201 and pil.person_id = p_person_id
202 and pil.lf_evt_ocrd_dt >= p_min_lf_evt_ocrd_dt
203 and pil.ler_id <> ben_manage_life_events.g_ler_id -- 9999 what is this?
204 and pil.ler_id = ler.ler_id
205 and (pil.ler_id = p_ler_id or p_ler_id = -1)
206 and p_effective_date
207 between ler.effective_start_date
208 and ler.effective_end_date
209 -- GRADE/STEP
210 -- bug 6147208
211 -- and ler.typ_cd not in ('COMP', 'GSP', 'IREC')-- iRec
212 and ler.typ_cd = 'ABS'
213 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
214 order by pil.lf_evt_ocrd_dt desc,ler.lf_evt_oper_cd asc; -- most recent is the first one to backout.
215 --
216 l_proc varchar2(72) := g_package||'absences_eval_ptnl_per_for_ler';
217 l_del_ptnl_ler_for_per_id NUMBER;
218 l_bckt_stat_cd varchar2(72);
219 l_curr_per_in_ler_id NUMBER;
220 l_object_version_number NUMBER;
221 l_procd_dt date;
222 l_strtd_dt date;
223 l_voidd_dt date;
224 l_del_ptnl_found boolean := false;
225 l_create_per_in_ler boolean := true;
226 l_dummy varchar2(1);
227
228 --
229 --Start 6086392
230 l_date date;
231 l_bckdt_pil_indx BINARY_INTEGER;
232 l_bckdt_pil_count BINARY_INTEGER;
233 --End 6086392
234
235 -- bug
236 CURSOR c_pil_ovn (cv_per_in_ler_id IN NUMBER)
237 IS
238 SELECT object_version_number
239 FROM ben_per_in_ler
240 WHERE per_in_ler_id = cv_per_in_ler_id;
241
242 pil_ovn_rec c_pil_ovn%ROWTYPE;
243 -- end bug
244
245 begin
246 --
247 hr_utility.set_location('Entering:'|| l_proc, 10);
248 --
249 -- Initialise all the variables in the loop.
250 --
251 loop
252 --
253 l_potent := l_potent_temp;
254 open get_all_potential;
255 fetch get_all_potential into l_potent;
256 close get_all_potential;
257 --
258 -- Check if there is a absence delete potential life event exists
259 -- for absence attendance id then void all the potentials.
260 --
261 l_del_ptnl_ler_for_per_id := null;
262 open c_get_ptnl_for_aba;
263 fetch c_get_ptnl_for_aba into l_del_ptnl_ler_for_per_id;
264 close c_get_ptnl_for_aba;
265 --
266 if l_del_ptnl_ler_for_per_id is not null then
267 --
268 -- Check any absences are processed which are attached to current absence_id.
269 --
270 -- Check if there is a Absence Deleted Potential Life Event attached to
271 -- this absence_id. If exists then make it processed. If there are
272 -- no processed life events attached to this absence_id then void the
273 -- absence potential life events attached to this absence_id. If
274 -- Absence Start Life Event attached to this absence_id is processed
275 -- then backout all the processed absence life events which are in
276 -- future with respect to current Absence Start Life Event in descending
277 -- order i.e., from row with max lf_evt_ocrd_dt to min lf_evt_ocrd_dt.
278 -- Now void all potential life events attached to the deleted absence_id.
279 --
280 --
281 l_del_ptnl_found := true;
282 --
283 open c_get_procd_pils_for_aba;
284 fetch c_get_procd_pils_for_aba into l_min_lf_evt_ocrd_dt;
285 close c_get_procd_pils_for_aba;
286 --
287 if l_min_lf_evt_ocrd_dt is not null then
288 --
289 -- Back out all the processed life events as this absence is deleted.
290 --
291 for l_pil_rec in c_pils_to_backout(l_min_lf_evt_ocrd_dt, -1) loop
292 --
293 l_bckt_stat_cd := 'UNPROCD';
294 if l_potent.trgr_table_pk_id = l_pil_rec.trgr_table_pk_id then
295 --
296 -- Void the potentials which are attched to the deleted absence
297 --
298 l_bckt_stat_cd := 'VOIDD';
299 --
300 end if;
301 --
302 ben_back_out_life_event.back_out_life_events
303 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
304 p_bckt_per_in_ler_id => null,
305 p_bckt_stat_cd => l_bckt_stat_cd,
306 p_business_group_id => p_business_group_id,
307 p_effective_date => p_effective_date);
308 --
309 --Start 6086392
310 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
311 l_bckdt_pil_count := l_bckdt_pil_count +1;
312 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_pil_rec.per_in_ler_id;
313 --End 6086392
314
315
316 end loop;
317 --
318 end if;
319 --
320 -- Now void all the potentials attached to the current absence.
321 --
322 for l_ppl_rec in c_get_ptnl_for_del_aba loop
323 --
324 if l_del_ptnl_ler_for_per_id = l_ppl_rec.ptnl_ler_for_per_id then
325 --
326 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
327 (p_validate => false
328 ,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
329 ,p_lf_evt_ocrd_dt => l_ppl_rec.lf_evt_ocrd_dt
330 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
331 ,p_procd_dt => p_effective_date
332 ,p_ler_id => l_ppl_rec.ler_id
333 ,p_person_id => l_ppl_rec.person_id
334 ,p_business_group_id => l_ppl_rec.business_group_id
335 ,p_object_version_number => l_ppl_rec.object_version_number
336 ,p_effective_date => p_effective_date
337 ,p_program_application_id => fnd_global.prog_appl_id
338 ,p_program_id => fnd_global.conc_program_id
339 ,p_request_id => fnd_global.conc_request_id
340 ,p_program_update_date => sysdate);
341 --
342 else
343 --
344 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
345 (p_validate => false
346 ,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
347 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
348 ,p_object_version_number => l_ppl_rec.object_version_number
349 ,p_effective_date => p_effective_date
350 ,p_program_application_id => fnd_global.prog_appl_id
351 ,p_program_id => fnd_global.conc_program_id
352 ,p_request_id => fnd_global.conc_request_id
353 ,p_program_update_date => sysdate
354 ,p_voidd_dt => p_effective_date);
355 --
356 end if;
357 --
358 end loop;
359 --
360 else
361 --
362 -- If the delete potential do not exist for the current absence
363 -- continue processing it.
364 --
365 exit;
366 --
367 end if; -- End of delete potential existence.
368 --
369 end loop;
370 --
371 -- For the absence_id attached to this potential find the max(potential_id)
372 -- of same life event type (same ler_id) (If in step 1 absence start life event
373 -- is picked up then all absence start life events attached to this absence id
374 -- will be picked up). If there is a End potential Life Event on same day then
375 -- process have to pick up Start potential life event first. This will be
376 -- the winning potential life event.
377 --
378 open c_winning_ptnl_of_same_type;
379 fetch c_winning_ptnl_of_same_type into l_winning_ptnl_rec;
380 --
381 if l_del_ptnl_found and l_winning_ptnl_rec.ptnl_ler_for_per_id is null then
382 --
383 close c_winning_ptnl_of_same_type;
384 --
385 -- All the absences are deleted, no winner is found then we need to just
386 -- commit the data and move on with next person.
387 -- 9999 change the message.
388 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
389 fnd_message.set_token('PERSON_ID',p_person_id);
390 fnd_message.set_token('PROC',l_proc);
391 benutils.write(fnd_message.get);
392 --
393 -- For BENAUTHE
394 --
395 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
396 fnd_message.set_token('PERSON_ID',p_person_id);
397 fnd_message.set_token('PROC',l_proc);
398 raise ben_manage_life_events.g_life_event_after;
399 --
400 end if;
401 --
402 -- Check any more potential exists for the winner type of absence.
403 --
404 fetch c_winning_ptnl_of_same_type into l_next_ptnl_rec;
405 --
406 close c_winning_ptnl_of_same_type;
407 --
408 --
409 --
410 if (l_winning_ptnl_rec.ptnl_ler_for_per_id <> l_potent.ptnl_ler_for_per_id or
411 l_next_ptnl_rec.ptnl_ler_for_per_id is not null)
412 then
413 --
414 -- There are corrections associated with the winner so void all the corrections.
415 -- also backout any processed life events in future if there is a processed
416 -- life event associated with this absence and ler type.
417 --
418 -- Find processed absence life event of same type(same ler_id, absence_id)
419 -- as winning absence life event (this could be for example absence start
420 -- life event). Backout all the processed absence life events which are
421 -- in future compared to this processed life event. This processed life event
422 -- should be backed out and voided and also potential associated with it
423 -- should be voided.
424 --
425 l_procd_lf_evt_ocrd_dt := null;
426 open c_get_procd_pils_for_win;
427 fetch c_get_procd_pils_for_win into l_procd_lf_evt_ocrd_dt;
428 close c_get_procd_pils_for_win;
429 --
430 if l_procd_lf_evt_ocrd_dt is not null then
431 --
432 -- Back out all the processed future life events
433 --
434 for l_pil_rec in c_pils_to_backout(l_procd_lf_evt_ocrd_dt, -1)
435 loop
436 --
437 l_bckt_stat_cd := 'UNPROCD';
438 --
439 if (l_winning_ptnl_rec.trgr_table_pk_id = l_pil_rec.trgr_table_pk_id and
440 l_winning_ptnl_rec.ler_id = l_pil_rec.ler_id)
441 then
442 --
443 -- Void the potentials which are attched to the deleted absence
444 --
445 l_bckt_stat_cd := 'VOIDD';
446 --
447 end if;
448 --
449 ben_back_out_life_event.back_out_life_events
450 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
451 p_bckt_per_in_ler_id => null,
452 p_bckt_stat_cd => l_bckt_stat_cd,
453 p_business_group_id => p_business_group_id,
454 p_effective_date => p_effective_date);
455 --
456
457 --Start 6086392
458 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
459 l_bckdt_pil_count := l_bckdt_pil_count +1;
460 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_pil_rec.per_in_ler_id;
461 --End 6086392
462
463
464 end loop;
465 --
466 end if;
467 --
468 -- Now void all the potentials attached to the current absence.
469 --
470 for l_ppl_rec in c_get_ptnl_for_win_aba loop
471 --
472 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
473 (p_validate => false
474 ,p_ptnl_ler_for_per_id => l_ppl_rec.ptnl_ler_for_per_id
475 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
476 ,p_object_version_number => l_ppl_rec.object_version_number
477 ,p_effective_date => p_effective_date
478 ,p_program_application_id => fnd_global.prog_appl_id
479 ,p_program_id => fnd_global.conc_program_id
480 ,p_request_id => fnd_global.conc_request_id
481 ,p_program_update_date => sysdate
482 ,p_voidd_dt => p_effective_date);
483 --
484 end loop;
485 end if;
486 --
487 -- Now backout all the life events in future compared with
488 -- current life event.
489 --
490 for l_future_pil_rec in c_pils_to_backout(l_winning_ptnl_rec.lf_evt_ocrd_dt + 1, -1) --iRec added +
491 loop
492 --
493 ben_back_out_life_event.back_out_life_events
494 (p_per_in_ler_id => l_future_pil_rec.per_in_ler_id,
495 p_bckt_per_in_ler_id => null,
496 p_bckt_stat_cd => 'UNPROCD',
497 p_business_group_id => p_business_group_id,
498 p_effective_date => p_effective_date);
499 --
500 --Start 6086392
501 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
502 l_bckdt_pil_count := l_bckdt_pil_count +1;
503 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_future_pil_rec.per_in_ler_id;
504 --End 6086392
505
506 end loop;
507 --
508 if l_winning_ptnl_rec.lf_evt_oper_cd ='END' then
509 -- in case absence is reopened, don't create a new per in ler
510 -- just mark the ptnl as PROCD
511 open c_reopened_abs;
512 fetch c_reopened_abs into l_dummy;
513 l_create_per_in_ler := c_reopened_abs%notfound;
514 close c_reopened_abs;
515 end if;
516
517 if l_create_per_in_ler then
518 --
519 -- Create the per in ler for the winner.
520 --
521 ben_Person_Life_Event_api.create_Person_Life_Event_perf
522 (p_validate => false
523 ,p_per_in_ler_id => l_curr_per_in_ler_id
524 ,p_ler_id => l_winning_ptnl_rec.ler_id
525 ,p_person_id => l_winning_ptnl_rec.person_id
526 ,p_per_in_ler_stat_cd => 'STRTD'
527 ,p_ptnl_ler_for_per_id => l_winning_ptnl_rec.ptnl_ler_for_per_id
528 ,p_lf_evt_ocrd_dt => l_winning_ptnl_rec.lf_evt_ocrd_dt
529 ,p_business_group_id => l_winning_ptnl_rec.business_group_id
530 ,p_ntfn_dt => l_winning_ptnl_rec.ntfn_dt
531 ,p_trgr_table_pk_id => l_winning_ptnl_rec.trgr_table_pk_id
532 ,p_object_version_number => l_object_version_number
533 ,p_effective_date => p_effective_date
534 ,p_program_application_id => fnd_global.prog_appl_id
535 ,p_program_id => fnd_global.conc_program_id
536 ,p_request_id => fnd_global.conc_request_id
537 ,p_program_update_date => sysdate
538 ,p_procd_dt => l_procd_dt
539 ,p_strtd_dt => l_strtd_dt
540 ,p_voidd_dt => l_voidd_dt);
541
542 end if;
543
544 g_rec.person_id := p_person_id;
545 g_rec.ler_id := l_winning_ptnl_rec.ler_id;
546 g_rec.lf_evt_ocrd_dt := l_winning_ptnl_rec.lf_evt_ocrd_dt;
547 g_rec.replcd_flag := 'N';
548 g_rec.crtd_flag := 'Y';
549 g_rec.tmprl_flag := 'N';
550 g_rec.dltd_flag := 'N';
551 g_rec.open_and_clsd_flag := 'N';
552 g_rec.not_crtd_flag := 'N';
553 g_rec.clsd_flag := 'N';
554 g_rec.stl_actv_flag := 'N';
555 g_rec.clpsd_flag := 'N';
556 g_rec.clsn_flag := 'N';
557 g_rec.no_effect_flag := 'N';
558 g_rec.cvrge_rt_prem_flag := 'N';
559 g_rec.business_group_id := p_business_group_id;
560 g_rec.effective_date := p_effective_date;
561 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
562 --
563 benutils.write(p_rec => g_rec);
564 --
565 -- update ptnl
566 --
567 --
568 l_procd_dt := trunc(sysdate);
569 --
570 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
571 (p_validate => false
572 ,p_ptnl_ler_for_per_id => l_winning_ptnl_rec.ptnl_ler_for_per_id
573 ,p_lf_evt_ocrd_dt => l_winning_ptnl_rec.lf_evt_ocrd_dt
574 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
575 ,p_procd_dt => p_effective_date
576 ,p_ler_id => l_winning_ptnl_rec.ler_id
577 ,p_person_id => l_winning_ptnl_rec.person_id
578 ,p_business_group_id => l_winning_ptnl_rec.business_group_id
579 ,p_object_version_number => l_winning_ptnl_rec.object_version_number
580 ,p_effective_date => p_effective_date
581 ,p_program_application_id => fnd_global.prog_appl_id
582 ,p_program_id => fnd_global.conc_program_id
583 ,p_request_id => fnd_global.conc_request_id
584 ,p_program_update_date => sysdate);
585
586 p_created_ler_id := l_winning_ptnl_rec.ler_id;
587
588 if not l_create_per_in_ler then
589 hr_utility.set_location('Leaving:'|| l_proc, 10);
590 raise ben_manage_life_events.g_life_event_after;
591 end if;
592
593
594 --Start 6086392
595 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.first;
596
597 if(l_bckdt_pil_indx is not null) then
598
599 loop
600 -- bug 5987235
601
602 OPEN c_pil_ovn(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx));
603 FETCH c_pil_ovn INTO pil_ovn_rec;
604 CLOSE c_pil_ovn;
605
606 ben_Person_Life_Event_api.update_person_life_event
607 (p_per_in_ler_id => ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx)
608 ,p_bckt_per_in_ler_id => l_curr_per_in_ler_id
609 -- ,p_object_version_number => l_object_version_number
610 ,p_object_version_number => pil_ovn_rec.object_version_number
611 ,p_effective_date => p_effective_date
612 ,P_PROCD_DT => l_date -- outputs
613 ,P_STRTD_DT => l_date
614 ,P_VOIDD_DT => l_date );
615
616 exit when l_bckdt_pil_indx = ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.last;
617
618 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.next(l_bckdt_pil_indx);
619
620 end loop;
621
622 end if;
623
624 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
625
626 --End 6086392
627
628 hr_utility.set_location('Leaving:'|| l_proc, 10);
629 --
630 -- bug 5987235
631 Exception
632 when ben_manage_life_events.g_life_event_after then
633 hr_utility.set_location('PTNLE Absence eval Exception g_life_event_after', 121);
634 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
635 raise ben_manage_life_events.g_life_event_after;
636 when others then
637 hr_utility.set_location('PTNLE Absence eval Exception ', 121);
638 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
639 fnd_message.raise_error;
640 -- end bug 5987235
641 end absences_eval_ptnl_per_for_ler;
642 --
643 -- ----------------------------------------------------------------------------
644 -- |------------------------< cwb_eval_ptnl_per_for_ler >----------------------|
645 -- ----------------------------------------------------------------------------
646 --
647 procedure cwb_eval_ptnl_per_for_ler(p_validate in boolean default false
648 ,p_person_id in number
649 ,p_business_group_id in number
650 ,p_ler_id in number default null
651 ,p_mode in varchar2
652 ,p_effective_date in date
653 ,p_lf_evt_ocrd_dt in date
654 ,p_ptnl_ler_for_per_id in number
655 ,p_created_ler_id out NOCOPY number) is
656 --
657 cursor c_ptnl(cv_ptnl_ler_for_per_id in number)
658 is
659 select ptnl.*
660 from ben_ptnl_ler_for_per ptnl
661 where ptnl.ptnl_ler_for_per_id = cv_ptnl_ler_for_per_id;
662 --
663 cursor get_per_in_ler(cv_lf_evt_ocrd_dt date)
664 is
665 select pil.per_in_ler_id,
666 pil.per_in_ler_stat_cd,
667 pil.lf_evt_ocrd_dt,
668 pil.ler_id,
669 pil.person_id,
670 pil.business_group_id,
671 pil.object_version_number,
672 pil.procd_dt,
673 pil.strtd_dt,
674 pil.voidd_dt,
675 pil.bckt_dt,
676 pil.clsd_dt,
677 pil.ntfn_dt
678 from ben_per_in_ler pil,
679 ben_ler_f ler
680 where pil.person_id = p_person_id
681 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
682 and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
683 and pil.ler_id = ler.ler_id
684 and pil.ler_id = p_ler_id
685 and p_effective_date
686 between ler.effective_start_date
687 and ler.effective_end_date
688 and ler.typ_cd = 'COMP'
689 union
690 select pil.per_in_ler_id,
691 pil.per_in_ler_stat_cd,
692 pil.lf_evt_ocrd_dt,
693 pil.ler_id,
694 pil.person_id,
695 pil.business_group_id,
696 pil.object_version_number,
697 pil.procd_dt,
698 pil.strtd_dt,
699 pil.voidd_dt,
700 pil.bckt_dt,
701 pil.clsd_dt,
702 pil.ntfn_dt
703 from ben_per_in_ler pil,
704 ben_ler_f ler
705 where pil.person_id = p_person_id
706 and pil.per_in_ler_stat_cd in ('STRTD')
707 and pil.lf_evt_ocrd_dt < cv_lf_evt_ocrd_dt
708 and pil.ler_id = ler.ler_id
709 and pil.ler_id = p_ler_id
710 and p_effective_date
711 between ler.effective_start_date
712 and ler.effective_end_date
713 and ler.typ_cd = 'COMP'
714 order by 3 asc;
715 --
716 l_pil_rec get_per_in_ler%rowtype;
717 l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
718 l_procd_dt date;
719 l_strtd_dt date;
720 l_voidd_dt date;
721 l_object_version_number NUMBER;
722 l_curr_per_in_ler_id number;
723 l_created_ler varchar2(2) := 'N';
724 l_ws_mgr_id number;
725 l_assignment_id number(15);
726 l_rec benutils.g_batch_param_rec;
727 --
728 begin
729 --
730 hr_utility.set_location('Entering cwb_eval_ptnl_per_for_ler',10);
731 hr_utility.set_location('ler_id = ' || p_ler_id,12345);
732 hr_utility.set_location('p_lf_evt_ocrd_dt = ' || p_lf_evt_ocrd_dt,12345);
733 --
734 -- Check whether a per in ler exists for a given ler_id,
735 -- life event occured date
736 --
737 open get_per_in_ler(p_lf_evt_ocrd_dt);
738 fetch get_per_in_ler into l_pil_rec;
739 close get_per_in_ler;
740 --
741 if l_pil_rec.lf_evt_ocrd_dt is null then
742 --
743 -- Case A : Create the per in ler.
744 --
745 hr_utility.set_location('A',10);
746 --
747 open c_ptnl(p_ptnl_ler_for_per_id);
748 fetch c_ptnl into l_ptnl_rec;
749 close c_ptnl;
750 --
751 l_created_ler := 'Y';
752 p_created_ler_id := p_ler_id;
753 --
754 -- GLOBALCWB : Populate data into ben_cwb_group_hrchy,
755 -- ben_cwb_person_tasks, ben_cwb_person_info if the per in ler
756 -- created is group per in ler.
757 --
758 ben_manage_cwb_life_events.g_cache_group_plan_rec.group_per_in_ler_id := null;
759 if benutils.g_benefit_action_id is not null then
760 --
761 benutils.get_batch_parameters
762 (p_benefit_action_id => benutils.g_benefit_action_id,
763 p_rec => l_rec);
764 --
765 ben_manage_cwb_life_events.get_group_plan_info(
766 p_pl_id => l_rec.pl_id,
767 p_lf_evt_ocrd_dt => l_rec.lf_evt_ocrd_dt,
768 p_business_group_id => l_rec.business_group_id);
769 --
770 hr_utility.set_location(ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id ,1234);
771 if l_rec.pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
772 --
773 ben_manage_cwb_life_events.get_cwb_manager_and_assignment
774 (p_person_id => p_person_id,
775 p_hrchy_to_use_cd => ben_manage_cwb_life_events.g_cache_group_plan_rec.hrchy_to_use_cd,
776 p_pos_structure_version_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.pos_structure_version_id,
777 p_effective_date => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
778 p_manager_id => l_ws_mgr_id,
779 p_assignment_id => l_assignment_id ) ;
780 --
781 hr_utility.set_location('l_ws_mgr_id = ' || l_ws_mgr_id, 1234);
782 hr_utility.set_location('l_assignment_id = ' || l_assignment_id, 1234);
783 end if;
784 end if;
785 --
786 hr_utility.set_location('group_pl_id = ' || ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id, 1234);
787 ben_Person_Life_Event_api.create_Person_Life_Event_perf
788 (p_validate => false
789 ,p_per_in_ler_id => l_curr_per_in_ler_id
790 ,p_ler_id => p_ler_id
791 ,p_person_id => p_person_id
792 ,p_per_in_ler_stat_cd => 'STRTD'
793 ,p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id
794 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
795 ,p_business_group_id => p_business_group_id
796 ,p_ntfn_dt => trunc(sysdate) -- p_ptnl_rec.ntfn_dt
797 ,p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id
798 ,p_ws_mgr_id => l_ws_mgr_id
799 ,p_assignment_id => l_assignment_id
800 ,p_object_version_number => l_object_version_number
801 ,p_effective_date => p_effective_date
802 ,p_program_application_id => fnd_global.prog_appl_id
803 ,p_program_id => fnd_global.conc_program_id
804 ,p_request_id => fnd_global.conc_request_id
805 ,p_program_update_date => sysdate
806 ,p_procd_dt => l_procd_dt
807 ,p_strtd_dt => l_strtd_dt
808 ,p_voidd_dt => l_voidd_dt);
809 --
810 if l_rec.pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
811 --
812 -- Per in ler created is a group per in ler so populate other
813 -- plan design tables.
814 --
815 hr_utility.set_location('Call ben_manage_cwb_life_events.popu_cwb_tables', 1234);
816 ben_manage_cwb_life_events.popu_cwb_tables(
817 p_group_per_in_ler_id => l_curr_per_in_ler_id,
818 p_group_pl_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id,
819 p_group_lf_evt_ocrd_dt => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_lf_evt_ocrd_dt,
820 p_group_business_group_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_business_group_id,
821 p_group_ler_id => ben_manage_cwb_life_events.g_cache_group_plan_rec.group_ler_id);
822 end if;
823 --
824 g_rec.person_id := p_person_id;
825 g_rec.ler_id := p_ler_id;
826 g_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
827 g_rec.replcd_flag := 'N';
828 g_rec.crtd_flag := 'Y';
829 g_rec.tmprl_flag := 'N';
830 g_rec.dltd_flag := 'N';
831 g_rec.open_and_clsd_flag := 'N';
832 g_rec.not_crtd_flag := 'N';
833 g_rec.clsd_flag := 'N';
834 g_rec.stl_actv_flag := 'N';
835 g_rec.clpsd_flag := 'N';
836 g_rec.clsn_flag := 'N';
837 g_rec.no_effect_flag := 'N';
838 g_rec.cvrge_rt_prem_flag := 'N';
839 g_rec.business_group_id := p_business_group_id;
840 g_rec.effective_date := p_effective_date;
841 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
842 --
843 benutils.write(p_rec => g_rec);
844 --
845 -- update ptnl
846 --
847 update_ptnl_per_for_ler
848 (p_ptnl_rec => l_ptnl_rec
849 ,p_effective_date => p_effective_date);
850 --
851 elsif p_lf_evt_ocrd_dt > l_pil_rec.lf_evt_ocrd_dt then
852 --
853 hr_utility.set_location('Case B ',10);
854 --
855 -- Case B : Potential is after active per in ler. First complete it.
856 --
857 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
858 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
859 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
860 l_pil_rec.lf_evt_ocrd_dt);
861 benutils.write(p_text => fnd_message.get);
862 --
863 g_rec.person_id := p_person_id;
864 g_rec.ler_id := l_pil_rec.ler_id;
865 g_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
866 g_rec.replcd_flag := 'N';
867 g_rec.crtd_flag := 'N';
868 g_rec.tmprl_flag := 'N';
869 g_rec.dltd_flag := 'N';
870 g_rec.open_and_clsd_flag := 'N';
871 g_rec.not_crtd_flag := 'N';
872 g_rec.clsd_flag := 'N';
873 g_rec.stl_actv_flag := 'Y';
874 g_rec.clpsd_flag := 'N';
875 g_rec.clsn_flag := 'N';
876 g_rec.no_effect_flag := 'N';
877 g_rec.cvrge_rt_prem_flag := 'N';
878 g_rec.business_group_id := p_business_group_id;
879 g_rec.effective_date := p_effective_date;
880 --
881 benutils.write(p_rec => g_rec);
882 --
883 -- For BENAUTHE retreival purpose.
884 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
885 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
886 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
887 l_pil_rec.lf_evt_ocrd_dt);
888 raise ben_manage_life_events.g_life_event_after;
889 --
890 else
891 --
892 hr_utility.set_location('Case C ',10);
893 --
894 -- Case C : Processed or active per in ler is in future for the given
895 -- ler so error out. You can't go back and run.
896 --
897 fnd_message.set_name('BEN','BEN_92864_CWB_PTNL_AFTR_ACTIVE');
898 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
899 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
900 l_pil_rec.lf_evt_ocrd_dt);
901 benutils.write(p_text => fnd_message.get);
902 --
903 g_rec.person_id := p_person_id;
904 g_rec.ler_id := l_pil_rec.ler_id;
905 g_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
906 g_rec.replcd_flag := 'N';
907 g_rec.crtd_flag := 'N';
908 g_rec.tmprl_flag := 'N';
909 g_rec.dltd_flag := 'N';
910 g_rec.open_and_clsd_flag := 'N';
911 g_rec.not_crtd_flag := 'N';
912 g_rec.clsd_flag := 'N';
913 g_rec.stl_actv_flag := 'Y';
914 g_rec.clpsd_flag := 'N';
915 g_rec.clsn_flag := 'N';
916 g_rec.no_effect_flag := 'N';
917 g_rec.cvrge_rt_prem_flag := 'N';
918 g_rec.business_group_id := p_business_group_id;
919 g_rec.effective_date := p_effective_date;
920 --
921 benutils.write(p_rec => g_rec);
922 --
923 -- For BENAUTHE retreival purpose.
924 fnd_message.set_name('BEN','BEN_92864_CWB_PTNL_AFTR_ACTIVE');
925 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
926 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
927 l_pil_rec.lf_evt_ocrd_dt);
928 raise ben_manage_life_events.g_life_event_after;
929 --
930 --
931 end if;
932 --
933 hr_utility.set_location('Leaving cwb_eval_ptnl_per_for_ler',10);
934 --
935 end cwb_eval_ptnl_per_for_ler;
936 --
937 -- GRADE/STEP : process the grade/step potential life events.
938 --
939 -- ----------------------------------------------------------------------------
940 -- |------------------------< grd_stp_eval_ptnl_per_for_ler >-----------------|
941 -- ----------------------------------------------------------------------------
942 --
943 procedure grd_stp_eval_ptnl_per_for_ler(p_validate in boolean default false
944 ,p_person_id in number
945 ,p_business_group_id in number
946 ,p_ler_id in number default null
947 ,p_mode in varchar2
948 ,p_effective_date in date
949 ,p_created_ler_id out NOCOPY number
950 ,p_lf_evt_oper_cd in varchar2 default null) is /* GSP Rate Sync*/
951 --
952 l_proc varchar2(72) := g_package||'grd_stp_eval_ptnl_per_for_ler';
953 --
954 cursor get_all_potential is
955 select ler.ovridg_le_flag,
956 pfl.ler_id,
957 pfl.ptnl_ler_for_per_id,
958 pfl.lf_evt_ocrd_dt,
959 pfl.object_version_number,
960 ler.ler_eval_rl,
961 pfl.creation_date,
962 pfl.ptnl_ler_for_per_stat_cd,
963 pfl.ntfn_dt,
964 pfl.dtctd_dt,
965 pfl.voidd_dt,
966 ler.name,
967 pfl.person_id,
968 pfl.business_group_id
969 from ben_ptnl_ler_for_per pfl,
970 ben_ler_f ler
971 where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
972 and pfl.person_id = p_person_id
973 and pfl.ler_id = ler.ler_id
974 and p_effective_date
975 between ler.effective_start_date
976 and ler.effective_end_date
977 and pfl.lf_evt_ocrd_dt <= p_effective_date
978 and pfl.ler_id <> ben_manage_life_events.g_ler_id
979 and ler.typ_cd = 'GSP'
980 order by pfl.lf_evt_ocrd_dt asc;
981 --
982 -- GSP Rate Sync
983 -- Before we process GSP Rate Sync, ensure that one GSP Progression has been processed
984 -- for the person in the past
985 cursor c_gsp_prog_procd_exists is
986 select null
987 from ben_per_in_ler pil, ben_ler_f ler
988 where pil.person_id = p_person_id
989 and pil.ler_id = ler.ler_id
990 and p_effective_date between ler.effective_start_date and ler.effective_end_date
991 and ler.typ_cd = 'GSP';
992 --
993 cursor get_gs_proc_strt_le(p_lf_evt_ocrd_dt date) is
994 select pil.*
995 from ben_per_in_ler pil,
996 ben_ler_f ler
997 where pil.person_id = p_person_id
998 and (pil.per_in_ler_stat_cd = 'STRTD'
999 or
1000 (pil.per_in_ler_stat_cd = 'PROCD'
1001 and pil.lf_evt_ocrd_dt >= p_lf_evt_ocrd_dt)
1002 )
1003 and pil.ler_id <> ben_manage_life_events.g_ler_id
1004 and pil.ler_id = ler.ler_id
1005 and p_effective_date
1006 between ler.effective_start_date
1007 and ler.effective_end_date
1008 and ler.typ_cd = 'GSP'
1009 order by per_in_ler_stat_cd desc, lf_evt_ocrd_dt desc;
1010 --
1011 l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
1012 l_potent get_all_potential%rowtype;
1013 l_min_ptnl get_all_potential%rowtype;
1014 l_recs_found boolean := false;
1015 l_pil_rec ben_per_in_ler%rowtype;
1016 l_curr_per_in_ler_id number;
1017 l_procd_dt date;
1018 l_strtd_dt date;
1019 l_voidd_dt date;
1020 l_object_version_number number;
1021 l_dummy varchar2(1);
1022 --
1023 begin
1024 --
1025 hr_utility.set_location('Entering:'|| l_proc, 10);
1026 --
1027 -- Step 1 : Find a potential life event with least lf_evt_ocrd_dt.
1028 -- Step 2 : Get a started or processed grade/step life event.
1029 -- If found log a message and skip processing of this person.
1030 -- Step 3 : If there is a processed grade step life event then log
1031 -- message : "Process this person manually; as grade step
1032 -- completed previously". Skip processing this person.
1033 -- Step 4 : Winner is the potential with least lf_evt_ocrd_dt
1034 --
1035 open get_all_potential;
1036 --
1037 loop
1038 --
1039 fetch get_all_potential into l_potent;
1040 exit when get_all_potential%notfound;
1041 --
1042 if l_min_ptnl.lf_evt_ocrd_dt is null then
1043 --
1044 l_min_ptnl := l_potent;
1045 --
1046 end if;
1047 --
1048 l_recs_found := true;
1049 --
1050 if l_potent.ptnl_ler_for_per_stat_cd = 'MNL' then
1051 --
1052 -- Need to comeup with new messages???? 99999
1053 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
1054 fnd_message.set_token('LE_NAME',l_potent.name);
1055 fnd_message.set_token('PROC',l_proc);
1056 benutils.write(p_text => fnd_message.get);
1057 -- For BENAUTHE retreival purpose.
1058 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
1059 fnd_message.set_token('LE_NAME',l_potent.name);
1060 fnd_message.set_token('PROC',l_proc);
1061 raise ben_manage_life_events.g_life_event_after;
1062 --
1063 end if;
1064 --
1065 end loop;
1066 --
1067 close get_all_potential;
1068 --
1069 -- Step 2
1070 --
1071 --
1072 -- Test for no potentials found error
1073 --
1074 if not l_recs_found then
1075 --
1076 -- We don't want to add to the error count so lets just process the next
1077 -- person. The life event could be strtd or procd we don't care.
1078 --
1079 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
1080 fnd_message.set_token('PERSON_ID',p_person_id);
1081 fnd_message.set_token('PROC',l_proc);
1082 benutils.write(fnd_message.get);
1083 --
1084 -- For BENAUTHE
1085 --
1086 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
1087 fnd_message.set_token('PERSON_ID',p_person_id);
1088 fnd_message.set_token('PROC',l_proc);
1089 raise ben_manage_life_events.g_life_event_after;
1090 --
1091 end if;
1092 --
1093 open get_gs_proc_strt_le(l_min_ptnl.lf_evt_ocrd_dt);
1094 fetch get_gs_proc_strt_le into l_pil_rec;
1095 close get_gs_proc_strt_le;
1096 --
1097 if l_pil_rec.per_in_ler_stat_cd = 'STRTD' then
1098 --
1099 -- Potential is after per in ler so leave ptnl as is
1100 --
1101 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
1102 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_min_ptnl.lf_evt_ocrd_dt);
1103 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
1104 l_pil_rec.lf_evt_ocrd_dt);
1105 benutils.write(p_text => fnd_message.get);
1106 --
1107 g_rec.person_id := p_person_id;
1108 g_rec.ler_id := l_min_ptnl.ler_id;
1109 g_rec.lf_evt_ocrd_dt := l_min_ptnl.lf_evt_ocrd_dt;
1110 g_rec.replcd_flag := 'N';
1111 g_rec.crtd_flag := 'N';
1112 g_rec.tmprl_flag := 'N';
1113 g_rec.dltd_flag := 'N';
1114 g_rec.open_and_clsd_flag := 'N';
1115 g_rec.not_crtd_flag := 'N';
1116 g_rec.clsd_flag := 'N';
1117 g_rec.stl_actv_flag := 'Y';
1118 g_rec.clpsd_flag := 'N';
1119 g_rec.clsn_flag := 'N';
1120 g_rec.no_effect_flag := 'N';
1121 g_rec.cvrge_rt_prem_flag := 'N';
1122 g_rec.business_group_id := p_business_group_id;
1123 g_rec.effective_date := p_effective_date;
1124 --
1125 benutils.write(p_rec => g_rec);
1126 --
1127 -- For BENAUTHE retreival purpose.
1128 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
1129 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_min_ptnl.lf_evt_ocrd_dt);
1130 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
1131 l_pil_rec.lf_evt_ocrd_dt);
1132 raise ben_manage_life_events.g_life_event_after;
1133 --
1134 elsif l_pil_rec.per_in_ler_stat_cd = 'PROCD' then
1135 --
1136 -- 99999 check the messages above and here.
1137 --
1138 -- Potential is after per in ler so leave ptnl as is
1139 --
1140 fnd_message.set_name('BEN','BEN_94092_PTNL_BEFORE_PROCD');
1141 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_min_ptnl.lf_evt_ocrd_dt);
1142 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
1143 l_pil_rec.lf_evt_ocrd_dt);
1144 benutils.write(p_text => fnd_message.get);
1145 --
1146 g_rec.person_id := p_person_id;
1147 g_rec.ler_id := l_min_ptnl.ler_id;
1148 g_rec.lf_evt_ocrd_dt := l_min_ptnl.lf_evt_ocrd_dt;
1149 g_rec.replcd_flag := 'N';
1150 g_rec.crtd_flag := 'N';
1151 g_rec.tmprl_flag := 'N';
1152 g_rec.dltd_flag := 'N';
1153 g_rec.open_and_clsd_flag := 'N';
1154 g_rec.not_crtd_flag := 'N';
1155 g_rec.clsd_flag := 'N';
1156 g_rec.stl_actv_flag := 'Y';
1157 g_rec.clpsd_flag := 'N';
1158 g_rec.clsn_flag := 'N';
1159 g_rec.no_effect_flag := 'N';
1160 g_rec.cvrge_rt_prem_flag := 'N';
1161 g_rec.business_group_id := p_business_group_id;
1162 g_rec.effective_date := p_effective_date;
1163 --
1164 benutils.write(p_rec => g_rec);
1165 --
1166 -- For BENAUTHE retreival purpose.
1167 fnd_message.set_name('BEN','BEN_94092_PTNL_BEFORE_PROCD');
1168 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_min_ptnl.lf_evt_ocrd_dt);
1169 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',l_pil_rec.lf_evt_ocrd_dt);
1170 raise ben_manage_life_events.g_life_event_after;
1171 --
1172 else
1173 --
1174 -- GSP Rate Sync
1175 if p_lf_evt_oper_cd = 'SYNC'
1176 then
1177 --
1178 open c_gsp_prog_procd_exists;
1179 --
1180 fetch c_gsp_prog_procd_exists into l_dummy;
1181 if c_gsp_prog_procd_exists%notfound
1182 then
1183 --
1184 -- GSP Rate Sync is being processed for a person who is never processed for GSP Prog in the past
1185 close c_gsp_prog_procd_exists;
1186 hr_utility.set_location('GSP Rate Sync processed withouth GSP Prog', 9);
1187 --
1188 fnd_message.set_name('BEN','BEN_94091_NO_GSP_PROG_PROCD');
1189 benutils.write(fnd_message.get);
1190 --
1191 fnd_message.set_name('BEN','BEN_94091_NO_GSP_PROG_PROCD');
1192 raise ben_manage_life_events.g_life_event_after;
1193 --
1194 end if;
1195 --
1196 close c_gsp_prog_procd_exists;
1197 --
1198 end if;
1199 -- GSP Rate Sync
1200
1201 -- insert ptnl
1202 --
1203 -- l_created_ler := 'Y';
1204 p_created_ler_id := l_min_ptnl.ler_id;
1205 --
1206 ben_Person_Life_Event_api.create_Person_Life_Event_perf
1207 (p_validate => false
1208 ,p_per_in_ler_id => l_curr_per_in_ler_id
1209 ,p_ler_id => l_min_ptnl.ler_id
1210 ,p_person_id => l_min_ptnl.person_id
1211 ,p_per_in_ler_stat_cd => 'STRTD'
1212 ,p_ptnl_ler_for_per_id => l_min_ptnl.ptnl_ler_for_per_id
1213 ,p_lf_evt_ocrd_dt => l_min_ptnl.lf_evt_ocrd_dt
1214 ,p_business_group_id => l_min_ptnl.business_group_id
1215 ,p_ntfn_dt => l_min_ptnl.ntfn_dt
1216 ,p_object_version_number => l_object_version_number
1217 ,p_effective_date => p_effective_date
1218 ,p_program_application_id => fnd_global.prog_appl_id
1219 ,p_program_id => fnd_global.conc_program_id
1220 ,p_request_id => fnd_global.conc_request_id
1221 ,p_program_update_date => sysdate
1222 ,p_procd_dt => l_procd_dt
1223 ,p_strtd_dt => l_strtd_dt
1224 ,p_voidd_dt => l_voidd_dt);
1225 --
1226 g_rec.person_id := p_person_id;
1227 g_rec.ler_id := l_min_ptnl.ler_id;
1228 g_rec.lf_evt_ocrd_dt := l_min_ptnl.lf_evt_ocrd_dt;
1229 g_rec.replcd_flag := 'N';
1230 g_rec.crtd_flag := 'Y';
1231 g_rec.tmprl_flag := 'N';
1232 g_rec.dltd_flag := 'N';
1233 g_rec.open_and_clsd_flag := 'N';
1234 g_rec.not_crtd_flag := 'N';
1235 g_rec.clsd_flag := 'N';
1236 g_rec.stl_actv_flag := 'N';
1237 g_rec.clpsd_flag := 'N';
1238 g_rec.clsn_flag := 'N';
1239 g_rec.no_effect_flag := 'N';
1240 g_rec.cvrge_rt_prem_flag := 'N';
1241 g_rec.business_group_id := p_business_group_id;
1242 g_rec.effective_date := p_effective_date;
1243 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
1244 --
1245 benutils.write(p_rec => g_rec);
1246 --
1247 -- update ptnl
1248 --
1249 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
1250 (p_validate => false
1251 ,p_ptnl_ler_for_per_id => l_min_ptnl.ptnl_ler_for_per_id
1252 ,p_lf_evt_ocrd_dt => l_min_ptnl.lf_evt_ocrd_dt
1253 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
1254 ,p_procd_dt => p_effective_date
1255 ,p_ler_id => l_min_ptnl.ler_id
1256 ,p_person_id => l_min_ptnl.person_id
1257 ,p_business_group_id => l_min_ptnl.business_group_id
1258 ,p_object_version_number => l_min_ptnl.object_version_number
1259 ,p_effective_date => p_effective_date
1260 ,p_program_application_id => fnd_global.prog_appl_id
1261 ,p_program_id => fnd_global.conc_program_id
1262 ,p_request_id => fnd_global.conc_request_id
1263 ,p_program_update_date => sysdate);
1264 --
1265 end if;
1266 hr_utility.set_location('Leaving:'|| l_proc, 10);
1267 --
1268 end grd_stp_eval_ptnl_per_for_ler;
1269 --
1270 -- ----------------------------------------------------------------------------
1271 -- |------------------------< eval_ptnl_per_for_ler >-------------------------|
1272 -- ----------------------------------------------------------------------------
1273 --
1274 procedure eval_ptnl_per_for_ler(p_validate in boolean default false
1275 ,p_person_id in number
1276 ,p_business_group_id in number
1277 ,p_ler_id in number default null
1278 ,p_mode in varchar2
1279 ,p_effective_date in date
1280 ,p_created_ler_id out NOCOPY number) is
1281
1282 --
1283 l_min_lf_evt_ocrd_dt date := null;
1284 --
1285 cursor get_all_potential is
1286 select ler.ovridg_le_flag,
1287 pfl.ler_id,
1288 pfl.ptnl_ler_for_per_id,
1289 pfl.lf_evt_ocrd_dt,
1290 pfl.object_version_number,
1291 ler.ler_eval_rl,
1292 pfl.creation_date,
1293 pfl.ptnl_ler_for_per_stat_cd,
1294 pfl.ntfn_dt,
1295 pfl.dtctd_dt,
1296 pfl.voidd_dt,
1297 ler.name
1298 from ben_ptnl_ler_for_per pfl,
1299 ben_ler_f ler
1300 where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
1301 and pfl.person_id = p_person_id
1302 and pfl.ler_id = ler.ler_id
1303 and p_effective_date
1304 between ler.effective_start_date
1305 and ler.effective_end_date
1306 and pfl.lf_evt_ocrd_dt <= decode(p_mode,
1307 'C',
1308 pfl.lf_evt_ocrd_dt,
1309 p_effective_date)
1310 and pfl.ler_id <> ben_manage_life_events.g_ler_id
1311 --
1312 -- CWB Changes
1313 --
1314 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') -- iRec
1315 order by pfl.lf_evt_ocrd_dt asc;
1316 --
1317 l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
1318 --
1319 l_pil_rec ben_per_in_ler%rowtype;
1320 --
1321 -- Bug 3179 : pil processed or started and a ptnl is created on the
1322 -- same day. Processed or started pil is not gatting backed out.
1323 --
1324 cursor get_all_per_in_ler(p_lf_evt_ocrd_dt date, p_curr_per_in_ler_id number) is
1325 select pil.*
1326 from ben_per_in_ler pil,
1327 ben_ler_f ler
1328 where pil.person_id = p_person_id
1329 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
1330 and pil.lf_evt_ocrd_dt >= p_lf_evt_ocrd_dt
1331 and pil.per_in_ler_id <> p_curr_per_in_ler_id
1332 and pil.ler_id <> ben_manage_life_events.g_ler_id
1333 --
1334 -- CWB Changes
1335 --
1336 and pil.ler_id = ler.ler_id
1337 and p_effective_date
1338 between ler.effective_start_date
1339 and ler.effective_end_date
1340 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
1341 --
1342 -- CWB Changes End
1343 --
1344 order by lf_evt_ocrd_dt desc;
1345 --
1346 -- Added as part of Bug : 3078
1347 -- Get earliest per in ler whether its processed or started
1348 --
1349 --
1350 -- CWB Changes : Cusrsor modified.
1351 --
1352 cursor get_current_per_in_ler(cv_lf_evt_ocrd_dt in date) is
1353 select pil.per_in_ler_id,
1354 pil.per_in_ler_stat_cd,
1355 pil.lf_evt_ocrd_dt,
1356 pil.ler_id,
1357 pil.person_id,
1358 pil.business_group_id,
1359 pil.object_version_number,
1360 pil.procd_dt,
1361 pil.strtd_dt,
1362 pil.voidd_dt,
1363 pil.bckt_dt,
1364 pil.clsd_dt,
1365 pil.ntfn_dt
1366 from ben_per_in_ler pil,
1367 ben_ler_f ler
1368 where pil.person_id = p_person_id
1369 and pil.per_in_ler_stat_cd = 'STRTD'
1370 and pil.ler_id <> ben_manage_life_events.g_ler_id
1371 and pil.ler_id = ler.ler_id
1372 and cv_lf_evt_ocrd_dt
1373 between ler.effective_start_date
1374 and ler.effective_end_date
1375 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
1376 union
1377 select pil.per_in_ler_id,
1378 pil.per_in_ler_stat_cd,
1379 pil.lf_evt_ocrd_dt,
1380 pil.ler_id,
1381 pil.person_id,
1382 pil.business_group_id,
1383 pil.object_version_number,
1384 pil.procd_dt,
1385 pil.strtd_dt,
1386 pil.voidd_dt,
1387 pil.bckt_dt,
1388 pil.clsd_dt,
1389 pil.ntfn_dt
1390 from ben_per_in_ler pil,
1391 ben_ler_f ler
1392 where pil.person_id = p_person_id
1393 and pil.per_in_ler_stat_cd = 'PROCD'
1394 and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
1395 and pil.ler_id <> ben_manage_life_events.g_ler_id
1396 and pil.ler_id = ler.ler_id
1397 -- GRADE/STEP
1398 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC')-- iRec
1399 and cv_lf_evt_ocrd_dt
1400 between ler.effective_start_date
1401 and ler.effective_end_date
1402 order by 3 asc;
1403 --
1404 -- CWB Changes End
1405 --
1406 --
1407 -- Bug 3179 : pbodla
1408 --
1409 --
1410 -- CWB Changes : Cursor joined to ben_ler_f
1411 --
1412 cursor c_check_deadlock_pil(cv_lf_evt_ocrd_dt in date) is
1413 select pil.per_in_ler_id,
1414 pil.per_in_ler_stat_cd,
1415 pil.lf_evt_ocrd_dt,
1416 pil.ler_id,
1417 pil.person_id,
1418 pil.business_group_id,
1419 pil.object_version_number,
1420 pil.procd_dt,
1421 pil.strtd_dt,
1422 pil.voidd_dt,
1423 pil.bckt_dt,
1424 pil.clsd_dt,
1425 pil.ntfn_dt
1426 from ben_per_in_ler pil,
1427 ben_ler_f ler
1428 where pil.person_id = p_person_id
1429 and pil.per_in_ler_stat_cd in ( 'PROCD', 'STRTD')
1430 and pil.ler_id <> ben_manage_life_events.g_ler_id
1431 and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1432 and pil.ler_id = ler.ler_id
1433 and cv_lf_evt_ocrd_dt
1434 between ler.effective_start_date
1435 and ler.effective_end_date
1436 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC');--iRec
1437 --
1438 -- CWB Changes End
1439 --
1440 --
1441 l_deadlock_per_in_ler get_current_per_in_ler%rowtype;
1442 l_current_per_in_ler get_current_per_in_ler%rowtype;
1443 l_curr_per_in_ler_id number;
1444 l_ovridg_le_flag BEN_LER_F.OVRIDG_LE_FLAG%TYPE;
1445 l_ptnl_ler_for_per_id BEN_PTNL_LER_FOR_PER.PTNL_LER_FOR_PER_ID%TYPE;
1446 l_lf_evt_ocrd_dt BEN_PTNL_LER_FOR_PER.LF_EVT_OCRD_DT%TYPE;
1447 l_creation_date BEN_PTNL_LER_FOR_PER.CREATION_DATE%TYPE;
1448 l_ntfn_dt BEN_PTNL_LER_FOR_PER.NTFN_DT%TYPE;
1449 l_win_ler_id BEN_LER_F.LER_ID%TYPE;
1450 l_norm_ler_id BEN_LER_F.LER_ID%TYPE;
1451 l_ler_id BEN_LER_F.LER_ID%TYPE;
1452 l_ler_typ_cd BEN_LER_F.TYP_CD%TYPE;
1453 l_tmlns_eval_cd BEN_LER_F.tmlns_eval_cd%type;
1454 l_tmlns_dys_num BEN_LER_F.tmlns_dys_num%type;
1455 l_tmlns_perd_cd BEN_LER_F.tmlns_perd_cd%type;
1456 l_tmlns_perd_rl BEN_LER_F.tmlns_perd_rl%type;
1457 l_min_tmlns_eval_cd BEN_LER_F.tmlns_eval_cd%type;
1458 l_min_tmlns_dys_num BEN_LER_F.tmlns_dys_num%type;
1459 l_min_tmlns_perd_cd BEN_LER_F.tmlns_perd_cd%type;
1460 l_min_tmlns_perd_rl BEN_LER_F.tmlns_perd_rl%type;
1461 l_min_creation_date BEN_PTNL_LER_FOR_PER.creation_date%type;
1462 l_object_version_number BEN_PTNL_LER_FOR_PER.OBJECT_VERSION_NUMBER%TYPE;
1463 l_proc varchar2(72) := g_package||'eval_ptnl_per_for_ler';
1464 l_created_ler varchar(2) := 'N';
1465 l_dummy varchar2(1);
1466 l_num_winners number := 0;
1467 l_num_recs number := 0;
1468 l_outputs ff_exec.outputs_t;
1469 l_return_ler_id number;
1470 l_min_ptnl_ler_for_per_id number;
1471 l_min_object_version_number number;
1472 l_rec benutils.g_ler;
1473 l_active_ler_rec benutils.g_ler;
1474 l_second_ler_rec benutils.g_ler;
1475 l_potent get_all_potential%rowtype;
1476 l_mnl_dt date;
1477 l_dtctd_dt date;
1478 l_procd_dt date;
1479 l_unprocd_dt date;
1480 l_voidd_dt date;
1481 l_recs_found boolean := false;
1482
1483 --Start 6086392
1484 l_bckdt_pil_indx BINARY_INTEGER;
1485 l_bckdt_pil_count BINARY_INTEGER;
1486 l_date date;
1487 l_pil_object_version_number BEN_PER_IN_LER.OBJECT_VERSION_NUMBER%TYPE;
1488 --End 6086392
1489
1490
1491 -- bug
1492 CURSOR c_pil_ovn (cv_per_in_ler_id IN NUMBER)
1493 IS
1494 SELECT object_version_number
1495 FROM ben_per_in_ler
1496 WHERE per_in_ler_id = cv_per_in_ler_id;
1497
1498 pil_ovn_rec c_pil_ovn%ROWTYPE;
1499 -- end bug
1500
1501
1502 --
1503 -- Bug 4872042
1504 --
1505 cursor c_winner_ler_typ_cd (cv_ler_id number)
1506 is
1507 select typ_cd, name
1508 from ben_ler_f ler
1509 where ler_id = cv_ler_id;
1510 --
1511 l_typ_cd VARCHAR2(30);
1512 l_use_mode VARCHAR2(30);
1513 l_ler_name VARCHAR2(240);
1514 --
1515 l_mnl_savepoint_established boolean := false;
1516 l_mnl_savepoint_preestablish boolean := false;
1517
1518 --
1519 /*
1520 -- ----------------------------------------------------------------------------
1521 -- |------------------------< update_ptnl_per_for_ler >-----------------------|
1522 -- ----------------------------------------------------------------------------
1523 --
1524 procedure update_ptnl_per_for_ler
1525 (p_ptnl_rec IN OUT NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
1526 ,p_effective_date IN DATE) is
1527 --
1528 l_proc varchar2(72) := g_package||'update_ptnl_per_for_ler';
1529 --
1530 l_mnl_dt date;
1531 l_dtctd_dt date;
1532 l_procd_dt date;
1533 l_unprocd_dt date;
1534 l_voidd_dt date;
1535 --
1536 begin
1537 --
1538 l_procd_dt := trunc(sysdate);
1539 --
1540 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
1541 (p_validate => false
1542 ,p_ptnl_ler_for_per_id => p_ptnl_rec.ptnl_ler_for_per_id
1543 ,p_lf_evt_ocrd_dt => p_ptnl_rec.lf_evt_ocrd_dt
1544 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
1545 ,p_procd_dt => p_effective_date
1546 ,p_ler_id => p_ptnl_rec.ler_id
1547 ,p_person_id => p_ptnl_rec.person_id
1548 ,p_business_group_id => p_ptnl_rec.business_group_id
1549 ,p_object_version_number => p_ptnl_rec.object_version_number
1550 ,p_effective_date => p_effective_date
1551 ,p_program_application_id => fnd_global.prog_appl_id
1552 ,p_program_id => fnd_global.conc_program_id
1553 ,p_request_id => fnd_global.conc_request_id
1554 ,p_program_update_date => sysdate);
1555 --
1556 end update_ptnl_per_for_ler;
1557 */
1558 -- ------------------------------------------------------------------------
1559 -- |------------------------< insert_per_in_ler >-------------------------|
1560 -- ------------------------------------------------------------------------
1561 procedure insert_per_in_ler
1562 (p_ptnl_rec IN out NOCOPY BEN_PTNL_LER_FOR_PER%ROWTYPE
1563 ,p_curr_per_in_ler_id out NOCOPY number
1564 ,p_effective_date IN DATE) is
1565 --
1566 l_per_in_ler_id NUMBER;
1567 l_object_version_number NUMBER;
1568 l_rslt_object_version_number NUMBER;
1569 l_proc varchar2(72) := g_package||'insert_per_in_ler';
1570 l_assignment_id number;
1571 l_perhasmultptus boolean;
1572 l_ler_rec ben_ler_f%rowtype;
1573 l_procd_dt date;
1574 l_strtd_dt date;
1575 l_voidd_dt date;
1576 l_effective_start_date date;
1577 l_effective_end_date date;
1578 l_effective_date date;
1579 --
1580 begin
1581 ben_Person_Life_Event_api.create_Person_Life_Event_perf
1582 (p_validate => false
1583 ,p_per_in_ler_id => p_curr_per_in_ler_id
1584 ,p_ler_id => p_ptnl_rec.ler_id
1585 ,p_person_id => p_ptnl_rec.person_id
1586 ,p_per_in_ler_stat_cd => 'STRTD'
1587 ,p_ptnl_ler_for_per_id => p_ptnl_rec.ptnl_ler_for_per_id
1588 ,p_lf_evt_ocrd_dt => p_ptnl_rec.lf_evt_ocrd_dt
1589 ,p_business_group_id => p_ptnl_rec.business_group_id
1590 ,p_ntfn_dt => p_ptnl_rec.ntfn_dt
1591 ,p_object_version_number => l_object_version_number
1592 ,p_effective_date => p_effective_date
1593 ,p_program_application_id => fnd_global.prog_appl_id
1594 ,p_program_id => fnd_global.conc_program_id
1595 ,p_request_id => fnd_global.conc_request_id
1596 ,p_program_update_date => sysdate
1597 ,p_procd_dt => l_procd_dt
1598 ,p_strtd_dt => l_strtd_dt
1599 ,p_voidd_dt => l_voidd_dt);
1600 --
1601 -- If life event is reduction in hours, create benefit
1602 -- assignment for all personal contacts.
1603 --
1604 ben_life_object.get_object(p_ler_id => p_ptnl_rec.ler_id,
1605 p_rec => l_ler_rec);
1606 --
1607 if l_ler_rec.typ_cd = 'REDUHRS' then
1608 --
1609 -- Create benefits assignment for dependent - COBRA requirement.
1610 --
1611 ben_assignment_internal.copy_empasg_to_benasg
1612 (p_person_id => p_ptnl_rec.person_id
1613 ,p_redu_hrs_flag => 'Y'
1614 ,p_effective_date => p_ptnl_rec.lf_evt_ocrd_dt
1615 ,p_assignment_id => l_assignment_id
1616 ,p_object_version_number => l_object_version_number
1617 ,p_perhasmultptus => l_perhasmultptus);
1618 --
1619 end if;
1620 --
1621 end insert_per_in_ler;
1622 --
1623 procedure check_for_timeliness
1624 (p_person_id in number,
1625 p_effective_date in date,
1626 p_mode in varchar2,
1627 p_business_group_id in number) is
1628 --
1629 l_proc varchar2(72) := g_package||'check_for_timeliness';
1630 l_effective_start_date date;
1631 l_effective_end_date date;
1632 l_outputs ff_exec.outputs_t;
1633 --
1634 l_ass_rec per_all_assignments_f%rowtype;
1635 l_loc_rec hr_locations_all%rowtype;
1636 l_jurisdiction_code varchar2(30);
1637 --
1638 --bug 1579642 added ptn.mnlo_dt is null in the where clause.
1639 --not to select the records which are having a date in
1640 --mnlo_dt column for timeliness information to handle the
1641 --case where evaluation rule is used along with timeliness.
1642 cursor c1 is
1643 select ptn.creation_date,
1644 ptn.ntfn_dt,
1645 ler.tmlns_eval_cd,
1646 ler.tmlns_perd_cd,
1647 ler.tmlns_dys_num,
1648 ler.tmlns_perd_rl,
1649 ler.name,
1650 ptn.lf_evt_ocrd_dt,
1651 ptn.ptnl_ler_for_per_id,
1652 ptn.ler_id,
1653 ptn.object_version_number
1654 from ben_ptnl_ler_for_per ptn,
1655 ben_ler_f ler
1656 where ler.ler_id = ptn.ler_id
1657 and ler.business_group_id = p_business_group_id
1658 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD', 'MNLO')
1659 and ptn.mnlo_dt is null -- to fix the bug 1579642
1660 and ptn.lf_evt_ocrd_dt --p_effective_date
1661 between ler.effective_start_date
1662 and ler.effective_end_date
1663 and ptn.business_group_id = ler.business_group_id
1664 and ptn.person_id = p_person_id
1665 and ptn.ler_id <> ben_manage_life_events.g_ler_id
1666 -- CWB Changes
1667 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP','IREC') --iRec
1668 and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
1669 'SCHEDDO',
1670 ptn.lf_evt_ocrd_dt,
1671 'SCHEDDA',
1672 ptn.lf_evt_ocrd_dt,
1673 p_effective_date);
1674 --
1675 l_potent c1%rowtype;
1676 l_mnl_dt date;
1677 l_dtctd_dt date;
1678 l_procd_dt date;
1679 l_unprocd_dt date;
1680 l_voidd_dt date;
1681 l_action_happened boolean := false;
1682 --
1683 -- Keep count of potentials which are not in ('PROCD','VOIDD')
1684 -- Keep count of potentials which are currently voided.
1685 -- These counts are used to determine whether all potentials
1686 -- are voided. This condition check is added to display more
1687 -- appropriate message when benmngle is called on line.
1688 --
1689 l_npv_ptnl_cnt number := 0;
1690 l_curr_voidd_ptnl_cnt number := 0;
1691 -- 6129827 Added these 2 variables
1692 l_mnl_ptnl_cnt number := 0;
1693 l_mnl_ler_name ben_ler_f.name%type;
1694 --
1695 begin
1696 --
1697 hr_utility.set_location('Entering:'|| l_proc, 10);
1698 --
1699 -- Rules
1700 --
1701 -- 1) if tmlns_eval_cd is null then
1702 -- do nothing
1703 -- 2) if tmlns_eval_cd = 'VOID' then
1704 -- if tmlns_dys_num is null and
1705 -- tmlns_perd_cd is null then
1706 -- do nothing
1707 -- elsif tmlns_dys_num is not null then
1708 -- if (p_ntfn_dt - lf_evt_ocrd_dt) > tmlns_dys_num then
1709 -- set event to voided
1710 -- else
1711 -- do nothing
1712 -- end if
1713 -- end if
1714 -- if no action has happened and
1715 -- tmlns_perd_cd is not null then
1716 -- if tmlns_perd_cd = 'PTCCY' then
1717 -- if lf_evt_ocrd_dt is before current year then
1718 -- set event to voided
1719 -- else
1720 -- do nothing
1721 -- end if
1722 -- elsif tmlns_perd_cd = 'RL' then
1723 -- if tmlns_perd_rl is not null then
1724 -- if rule evaluates to Y then
1725 -- set event to voided
1726 -- else
1727 -- do nothing
1728 -- end if
1729 -- else
1730 -- do nothing
1731 -- end if
1732 -- end if
1733 -- end if
1734 -- elsif tmlns_eval_cd = 'PRCM' then
1735 -- if tmlns_dys_num is null and
1736 -- tmlns_perd_cd is null then
1737 -- do nothing
1738 -- elsif tmlns_dys_num is not null then
1739 -- if (p_ntfn_dt - lf_evt_ocrd_dt) > tmlns_dys_num then
1740 -- leave event as is
1741 -- else
1742 -- do nothing
1743 -- end if
1744 -- end if
1745 -- if no action has happened and
1746 -- tmlns_perd_cd is not null then
1747 -- if tmlns_perd_cd = 'PTCCY' then
1748 -- if lf_evt_ocrd_dt is before current year then
1749 -- leave event as is
1750 -- else
1751 -- do nothing
1752 -- end if
1753 -- elsif tmlns_perd_cd = 'RL' then
1754 -- if tmlns_perd_rl is not null then
1755 -- if rule evaluates to Y then
1756 -- leave event as is
1757 -- else
1758 -- do nothing
1759 -- end if
1760 -- else
1761 -- do nothing
1762 -- end if
1763 -- end if
1764 -- end if
1765 -- end if
1766 --
1767 open c1;
1768 --
1769 loop
1770 --
1771 fetch c1 into l_potent;
1772 exit when c1%notfound;
1773 --
1774 hr_utility.set_location(' tmlns_eval_cd '|| l_potent.tmlns_eval_cd , 10);
1775 hr_utility.set_location(' tmlns_perd_cd '|| l_potent.tmlns_perd_cd , 10);
1776 --
1777 l_npv_ptnl_cnt := l_npv_ptnl_cnt + 1; --added during iRec
1778 l_action_happened := false;
1779 --
1780 if l_potent.tmlns_eval_cd is null then
1781 --
1782 -- No timeliness to consider
1783 --
1784 if l_mnl_savepoint_preestablish then
1785 l_mnl_savepoint_established := true;
1786 savepoint ptnl_set_to_manual_savepoint;
1787 end if;
1788 null;
1789 --
1790 elsif l_potent.tmlns_eval_cd = 'VOID' then
1791 --
1792 if l_potent.tmlns_dys_num is not null then
1793 --
1794 -- Note use of absolute so that future events work
1795 --
1796 if abs((l_potent.ntfn_dt - l_potent.lf_evt_ocrd_dt)) >
1797 l_potent.tmlns_dys_num then
1798 --
1799 -- We need to void the event and raise a message informing the user
1800 -- that the event has been voided
1801 --
1802 l_action_happened := true;
1803 --
1804 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
1805 (p_validate => false
1806 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
1807 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
1808 ,p_object_version_number => l_potent.object_version_number
1809 ,p_effective_date => p_effective_date
1810 ,p_program_application_id => fnd_global.prog_appl_id
1811 ,p_program_id => fnd_global.conc_program_id
1812 ,p_request_id => fnd_global.conc_request_id
1813 ,p_program_update_date => sysdate
1814 ,p_voidd_dt => p_effective_date);
1815 --
1816 l_curr_voidd_ptnl_cnt := l_curr_voidd_ptnl_cnt + 1;-- added during iRec
1817 fnd_message.set_name('BEN','BEN_92098_LIFE_EVENT_VOIDED');
1818 fnd_message.set_token('LF_EVT',l_potent.name);
1819 fnd_message.set_token('LF_EVT_OCRD_DT',l_potent.lf_evt_ocrd_dt);
1820 benutils.write(p_text => fnd_message.get);
1821 --
1822 g_rec.person_id := p_person_id;
1823 g_rec.ler_id := l_potent.ler_id;
1824 g_rec.lf_evt_ocrd_dt := l_potent.lf_evt_ocrd_dt;
1825 g_rec.replcd_flag := 'N';
1826 g_rec.crtd_flag := 'N';
1827 g_rec.tmprl_flag := 'N';
1828 g_rec.dltd_flag := 'N';
1829 g_rec.open_and_clsd_flag := 'N';
1830 g_rec.not_crtd_flag := 'Y';
1831 g_rec.clsd_flag := 'N';
1832 g_rec.stl_actv_flag := 'N';
1833 g_rec.clpsd_flag := 'N';
1834 g_rec.clsn_flag := 'N';
1835 g_rec.no_effect_flag := 'N';
1836 g_rec.cvrge_rt_prem_flag := 'N';
1837 g_rec.business_group_id := p_business_group_id;
1838 g_rec.effective_date := p_effective_date;
1839 --
1840 benutils.write(p_rec => g_rec);
1841 --
1842 end if;
1843 --
1844 end if;
1845 --
1846 if not l_action_happened and
1847 l_potent.tmlns_perd_cd is not null then
1848 --
1849 if l_potent.tmlns_perd_cd = 'PTCCY' then
1850 --
1851 -- Note use of absolute so that future events work
1852 --
1853 if abs(to_number(to_char(l_potent.ntfn_dt,'YYYY')) -
1854 to_number(to_char(l_potent.lf_evt_ocrd_dt,'YYYY'))) >= 1 then
1855 --
1856 -- We need to void the event and raise a message informing
1857 -- the user that the event has been voided
1858 --
1859 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
1860 (p_validate => false
1861 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
1862 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
1863 ,p_object_version_number => l_potent.object_version_number
1864 ,p_effective_date => p_effective_date
1865 ,p_program_application_id => fnd_global.prog_appl_id
1866 ,p_program_id => fnd_global.conc_program_id
1867 ,p_request_id => fnd_global.conc_request_id
1868 ,p_program_update_date => sysdate
1869 ,p_voidd_dt => p_effective_date);
1870 --
1871 l_curr_voidd_ptnl_cnt := l_curr_voidd_ptnl_cnt + 1;-- + added during iREC
1872 fnd_message.set_name('BEN','BEN_92098_LIFE_EVENT_VOIDED');
1873 fnd_message.set_token('LF_EVT',l_potent.name);
1874 fnd_message.set_token('LF_EVT_OCRD_DT',l_potent.lf_evt_ocrd_dt);
1875 benutils.write(p_text => fnd_message.get);
1876 --
1877 g_rec.person_id := p_person_id;
1878 g_rec.ler_id := l_potent.ler_id;
1879 g_rec.lf_evt_ocrd_dt := l_potent.lf_evt_ocrd_dt;
1880 g_rec.replcd_flag := 'N';
1881 g_rec.crtd_flag := 'N';
1882 g_rec.tmprl_flag := 'N';
1883 g_rec.dltd_flag := 'N';
1884 g_rec.open_and_clsd_flag := 'N';
1885 g_rec.not_crtd_flag := 'Y';
1886 g_rec.clsd_flag := 'N';
1887 g_rec.stl_actv_flag := 'N';
1888 g_rec.clpsd_flag := 'N';
1889 g_rec.clsn_flag := 'N';
1890 g_rec.no_effect_flag := 'N';
1891 g_rec.cvrge_rt_prem_flag := 'N';
1892 g_rec.business_group_id := p_business_group_id;
1893 g_rec.effective_date := p_effective_date;
1894 --
1895 benutils.write(p_rec => g_rec);
1896 --
1897 end if;
1898 --
1899 elsif l_potent.tmlns_perd_cd = 'RL' then
1900 --
1901 if l_potent.tmlns_perd_rl is not null then
1902 --
1903 ben_person_object.get_object(p_person_id => p_person_id,
1904 p_rec => l_ass_rec);
1905 --
1906 if l_ass_rec.assignment_id is null then
1907 --
1908 ben_person_object.get_benass_object(p_person_id => p_person_id,
1909 p_rec => l_ass_rec);
1910 --
1911 end if;
1912 --
1913 if l_ass_rec.location_id is not null then
1914 --
1915 ben_location_object.get_object
1916 (p_location_id => l_ass_rec.location_id,
1917 p_rec => l_loc_rec);
1918 --
1919 -- if l_loc_rec.region_2 is not null then
1920 --
1921 -- l_jurisdiction_code :=
1922 -- pay_mag_utils.lookup_jurisdiction_code
1923 -- (p_state => l_loc_rec.region_2);
1924 --
1925 -- end if;
1926 --
1927 end if;
1928 --
1929 l_outputs := benutils.formula
1930 (p_formula_id => l_potent.tmlns_perd_rl,
1931 p_effective_date => p_effective_date,
1932 p_ler_id => l_potent.ler_id,
1933 p_assignment_id => l_ass_rec.assignment_id,
1934 p_organization_id => l_ass_rec.organization_id,
1935 p_business_group_id=> p_business_group_id,
1936 p_jurisdiction_code=> l_jurisdiction_code);
1937 --
1938 if l_outputs(l_outputs.first).value = 'Y' then
1939 --
1940 -- We need to void the event and raise a message informing
1941 -- the user that the event has been voided
1942 --
1943 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
1944 (p_validate => false
1945 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
1946 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
1947 ,p_object_version_number => l_potent.object_version_number
1948 ,p_effective_date => p_effective_date
1949 ,p_program_application_id => fnd_global.prog_appl_id
1950 ,p_program_id => fnd_global.conc_program_id
1951 ,p_request_id => fnd_global.conc_request_id
1952 ,p_program_update_date => sysdate
1953 ,p_voidd_dt => p_effective_date);
1954 --
1955 l_curr_voidd_ptnl_cnt := l_curr_voidd_ptnl_cnt + 1;
1956 fnd_message.set_name('BEN','BEN_92098_LIFE_EVENT_VOIDED');
1957 fnd_message.set_token('LF_EVT',l_potent.name);
1958 fnd_message.set_token('LF_EVT_OCRD_DT',l_potent.lf_evt_ocrd_dt);
1959 benutils.write(p_text => fnd_message.get);
1960 --
1961 g_rec.person_id := p_person_id;
1962 g_rec.ler_id := l_potent.ler_id;
1963 g_rec.lf_evt_ocrd_dt := l_potent.lf_evt_ocrd_dt;
1964 g_rec.replcd_flag := 'N';
1965 g_rec.crtd_flag := 'N';
1966 g_rec.tmprl_flag := 'N';
1967 g_rec.dltd_flag := 'N';
1968 g_rec.open_and_clsd_flag := 'N';
1969 g_rec.not_crtd_flag := 'Y';
1970 g_rec.clsd_flag := 'N';
1971 g_rec.stl_actv_flag := 'N';
1972 g_rec.clpsd_flag := 'N';
1973 g_rec.clsn_flag := 'N';
1974 g_rec.no_effect_flag := 'N';
1975 g_rec.cvrge_rt_prem_flag := 'N';
1976 g_rec.business_group_id := p_business_group_id;
1977 g_rec.effective_date := p_effective_date;
1978 --
1979 benutils.write(p_rec => g_rec);
1980 --
1981 elsif l_outputs(l_outputs.first).value <> 'N' then
1982 --
1983 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
1984 fnd_message.set_token('RL',
1985 'tmlns_perd_rl :'||l_potent.tmlns_perd_rl);
1986 fnd_message.set_token('PROC',l_proc);
1987 raise ben_manage_life_events.g_record_error;
1988 --
1989 end if;
1990 --
1991 end if;
1992 --
1993 end if;
1994 --
1995 end if;
1996 --
1997 elsif l_potent.tmlns_eval_cd = 'PRCM' then
1998 --
1999 if l_potent.tmlns_dys_num is not null then
2000 --
2001 -- Note use of absolute to get value
2002 --
2003 if abs((l_potent.ntfn_dt - l_potent.lf_evt_ocrd_dt))
2004 > l_potent.tmlns_dys_num then
2005 --
2006 l_action_happened := true;
2007 --
2008 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2009 (p_validate => false
2010 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
2011 ,p_ptnl_ler_for_per_stat_cd => 'MNL'
2012 ,p_object_version_number => l_potent.object_version_number
2013 ,p_effective_date => p_effective_date
2014 ,p_program_application_id => fnd_global.prog_appl_id
2015 ,p_program_id => fnd_global.conc_program_id
2016 ,p_request_id => fnd_global.conc_request_id
2017 ,p_program_update_date => sysdate
2018 ,p_mnl_dt => p_effective_date);
2019 --
2020 l_mnl_ptnl_cnt := l_mnl_ptnl_cnt + 1;
2021 l_mnl_ler_name := l_potent.name;
2022 --
2023 end if;
2024 --
2025 end if;
2026 --
2027 if not l_action_happened and
2028 l_potent.tmlns_perd_cd is not null then
2029 --
2030 if l_potent.tmlns_perd_cd = 'PTCCY' then
2031 --
2032 -- Note use of absolute to get value
2033 --
2034 if abs(to_number(to_char(l_potent.ntfn_dt,'YYYY')) -
2035 to_number(to_char(l_potent.lf_evt_ocrd_dt,'YYYY'))) >= 1 then
2036 --
2037 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2038 (p_validate => false
2039 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
2040 ,p_ptnl_ler_for_per_stat_cd => 'MNL'
2041 ,p_object_version_number => l_potent.object_version_number
2042 ,p_effective_date => p_effective_date
2043 ,p_program_application_id => fnd_global.prog_appl_id
2044 ,p_program_id => fnd_global.conc_program_id
2045 ,p_request_id => fnd_global.conc_request_id
2046 ,p_program_update_date => sysdate
2047 ,p_mnl_dt => p_effective_date);
2048 --
2049 l_mnl_ptnl_cnt := l_mnl_ptnl_cnt + 1;
2050 l_mnl_ler_name := l_potent.name;
2051 --
2052 end if;
2053 --
2054 elsif l_potent.tmlns_perd_cd = 'RL' then
2055 --
2056 if l_potent.tmlns_perd_rl is not null then
2057 --
2058 ben_person_object.get_object(p_person_id => p_person_id,
2059 p_rec => l_ass_rec);
2060 --
2061 if l_ass_rec.assignment_id is null then
2062 --
2063 ben_person_object.get_benass_object(p_person_id => p_person_id,
2064 p_rec => l_ass_rec);
2065 --
2066 end if;
2067 --
2068 if l_ass_rec.location_id is not null then
2069 --
2070 ben_location_object.get_object
2071 (p_location_id => l_ass_rec.location_id,
2072 p_rec => l_loc_rec);
2073 --
2074 -- if l_loc_rec.region_2 is not null then
2075 --
2076 -- l_jurisdiction_code :=
2077 -- pay_mag_utils.lookup_jurisdiction_code
2078 -- (p_state => l_loc_rec.region_2);
2079 --
2080 -- end if;
2081 --
2082 end if;
2083 --
2084 l_outputs := benutils.formula
2085 (p_formula_id => l_potent.tmlns_perd_rl,
2086 p_effective_date => p_effective_date,
2087 p_ler_id => l_potent.ler_id,
2088 p_assignment_id => l_ass_rec.assignment_id,
2089 p_organization_id => l_ass_rec.organization_id,
2090 p_business_group_id=> p_business_group_id,
2091 p_jurisdiction_code=> l_jurisdiction_code);
2092 --
2093 if l_outputs(l_outputs.first).value = 'Y' then
2094 --
2095 -- We need to void the event and raise a message informing
2096 -- the user that the event has been voided
2097 -- Bug 4217795 set status to manual
2098 --
2099 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2100 (p_validate => false
2101 ,p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id
2102 ,p_ptnl_ler_for_per_stat_cd => 'MNL'
2103 ,p_object_version_number => l_potent.object_version_number
2104 ,p_effective_date => p_effective_date
2105 ,p_program_application_id => fnd_global.prog_appl_id
2106 ,p_program_id => fnd_global.conc_program_id
2107 ,p_request_id => fnd_global.conc_request_id
2108 ,p_program_update_date => sysdate
2109 ,p_mnl_dt => p_effective_date);
2110 --
2111 l_mnl_ptnl_cnt := l_mnl_ptnl_cnt + 1;
2112 l_mnl_ler_name := l_potent.name;
2113 --
2114 --
2115 /* Bug 4217795
2116 l_curr_voidd_ptnl_cnt := l_curr_voidd_ptnl_cnt + 1; -- + added during irec
2117 fnd_message.set_name('BEN','BEN_92098_LIFE_EVENT_VOIDED');
2118 fnd_message.set_token('LF_EVT',l_potent.name);
2119 fnd_message.set_token('LF_EVT_OCRD_DT',l_potent.lf_evt_ocrd_dt);
2120 benutils.write(p_text => fnd_message.get);
2121 --
2122 g_rec.person_id := p_person_id;
2123 g_rec.ler_id := p_ler_id;
2124 g_rec.lf_evt_ocrd_dt := l_potent.lf_evt_ocrd_dt;
2125 g_rec.replcd_flag := 'N';
2126 g_rec.crtd_flag := 'N';
2127 g_rec.tmprl_flag := 'N';
2128 g_rec.dltd_flag := 'N';
2129 g_rec.open_and_clsd_flag := 'N';
2130 g_rec.not_crtd_flag := 'Y';
2131 g_rec.clsd_flag := 'N';
2132 g_rec.stl_actv_flag := 'N';
2133 g_rec.clpsd_flag := 'N';
2134 g_rec.clsn_flag := 'N';
2135 g_rec.no_effect_flag := 'N';
2136 g_rec.cvrge_rt_prem_flag := 'N';
2137 g_rec.business_group_id := p_business_group_id;
2138 g_rec.effective_date := p_effective_date;
2139 --
2140 benutils.write(p_rec => g_rec);
2141 --
2142 */
2143 elsif l_outputs(l_outputs.first).value = 'N' then
2144 --
2145 null;
2146 --
2147 elsif l_outputs(l_outputs.first).value <> 'N' then
2148 --
2149 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
2150 fnd_message.set_token('RL',
2151 'tmlns_perd_rl :'||l_potent.tmlns_perd_rl);
2152 fnd_message.set_token('PROC',l_proc);
2153 raise ben_manage_life_events.g_record_error;
2154 --
2155 end if;
2156 --
2157 end if;
2158 --
2159 end if;
2160 --
2161 end if;
2162 --
2163 end if;
2164 --
2165 end loop;
2166 --
2167 close c1;
2168 --
2169 hr_utility.set_location(' l_npv_ptnl_cnt '|| l_npv_ptnl_cnt , 10);
2170 hr_utility.set_location(' l_curr_voidd_ptnl_cnt '|| l_curr_voidd_ptnl_cnt , 10);
2171 hr_utility.set_location(' l_mnl_ptnl_cnt '|| l_mnl_ptnl_cnt , 10);
2172 hr_utility.set_location(' l_mnl_ler_name '|| l_mnl_ler_name , 10);
2173 --
2174 -- 6245213 : Set savepoint. This is rolled back, if reqd, in check_and_get_winner.
2175 if (l_mnl_ptnl_cnt > 0) and not l_mnl_savepoint_established then
2176 l_mnl_savepoint_established := true;
2177 savepoint ptnl_set_to_manual_savepoint;
2178 end if;
2179 --
2180 -- Check if all the potetial's are made VOIDD
2181 --
2182 if l_npv_ptnl_cnt <> 0 and
2183 l_curr_voidd_ptnl_cnt = l_npv_ptnl_cnt then
2184 --
2185 fnd_message.set_name('BEN','BEN_92400_ALL_PTNL_VOIDD');
2186 benutils.write(p_text => fnd_message.get);
2187 --
2188 -- Mark a global indicating some of the potentials are
2189 -- made voidd. This flag is used by benptnle to display
2190 -- a message back to the user.
2191 --
2192 ben_on_line_lf_evt.g_ptnls_voidd_flag := TRUE;
2193 --
2194 raise ben_manage_life_events.g_life_event_after;
2195 --
2196 elsif l_npv_ptnl_cnt <> 0 and l_curr_voidd_ptnl_cnt <> 0 and
2197 l_curr_voidd_ptnl_cnt <> l_npv_ptnl_cnt then
2198 --
2199 -- Mark a global indicating some of the potentials are
2200 -- made voidd. This flag is used by benptnle to display
2201 -- a message back to the user.
2202 --
2203 ben_on_line_lf_evt.g_ptnls_voidd_flag := TRUE;
2204 --
2205 end if;
2206 --
2207 -- 6129827 : If timeliness sets all potentials to Manual/Voided
2208 -- then exit immediately.
2209 if (l_npv_ptnl_cnt <> 0) and (l_npv_ptnl_cnt = l_mnl_ptnl_cnt + l_curr_voidd_ptnl_cnt) then
2210 --
2211 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2212 fnd_message.set_token('LE_NAME',l_mnl_ler_name);
2213 fnd_message.set_token('PROC',l_proc);
2214 benutils.write(p_text => fnd_message.get);
2215 --
2216 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2217 fnd_message.set_token('LE_NAME',l_mnl_ler_name);
2218 fnd_message.set_token('PROC',l_proc);
2219 raise ben_manage_life_events.g_life_event_after;
2220 --
2221 end if;
2222 --
2223 hr_utility.set_location('Leaving:'|| l_proc, 10);
2224 --
2225 end check_for_timeliness;
2226 --
2227 function rule_evaluates
2228 (p_ler_id in number,
2229 p_person_id in number,
2230 p_business_group_id in number,
2231 p_ptnl_ler_for_per_id in number,
2232 p_ptnl_ler_for_per_stat_cd in varchar2,
2233 p_ntfn_dt in date,
2234 p_dtctd_dt in date,
2235 p_voidd_dt in date,
2236 p_object_version_number in number,
2237 p_rule_id in number,
2238 p_lf_evt_ocrd_dt in date,
2239 p_effective_date in date) return varchar2 is
2240 --
2241 l_proc varchar2(72) := g_package||'rule_evaluates';
2242 l_outputs ff_exec.outputs_t;
2243 l_happened varchar2(30) := 'UNSET';
2244 l_ler_id number := p_ler_id;
2245 l_object_version_number number;
2246 l_ptnl_ler_for_per_id number;
2247 l_lf_evt_ocrd_dt date := p_lf_evt_ocrd_dt;
2248 l_ntfn_dt date := p_ntfn_dt;
2249 l_dtctd_dt date := p_dtctd_dt;
2250 l_voidd_dt date := p_voidd_dt;
2251 l_ptnl_ler_for_per_stat_cd varchar2(30) := p_ptnl_ler_for_per_stat_cd;
2252 l_ptnl_ler_for_per_stat_cd_use varchar2(30);
2253 l_procd_dt date;
2254 l_unprocd_dt date;
2255 l_mnl_dt date;
2256 --
2257 l_ass_rec per_all_assignments_f%rowtype;
2258 l_loc_rec hr_locations_all%rowtype;
2259 l_jurisdiction_code varchar2(30);
2260 --
2261 begin
2262 --
2263 hr_utility.set_location('Entering:'|| l_proc, 10);
2264 --
2265 -- If no rule then return a Y else lets check the rule
2266 --
2267 if p_rule_id is null then
2268 --
2269 return 'Y';
2270 --
2271 else
2272 --
2273 ben_person_object.get_object(p_person_id => p_person_id,
2274 p_rec => l_ass_rec);
2275 --
2276 if l_ass_rec.assignment_id is null then
2277 --
2278 ben_person_object.get_benass_object(p_person_id => p_person_id,
2279 p_rec => l_ass_rec);
2280 --
2281 end if;
2282 --
2283 if l_ass_rec.location_id is not null then
2284 --
2285 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
2286 p_rec => l_loc_rec);
2287 --
2288 -- if l_loc_rec.region_2 is not null then
2289 --
2290 -- l_jurisdiction_code :=
2291 -- pay_mag_utils.lookup_jurisdiction_code
2292 -- (p_state => l_loc_rec.region_2);
2293 --
2294 -- end if;
2295 --
2296 end if;
2297 --
2298 l_outputs := benutils.formula
2299 (p_formula_id => p_rule_id,
2300 p_effective_date => p_effective_date,
2301 p_ler_id => p_ler_id,
2302 p_assignment_id => l_ass_rec.assignment_id,
2303 p_organization_id => l_ass_rec.organization_id,
2304 p_business_group_id=> p_business_group_id,
2305 --
2306 -- Bug 1844764
2307 -- Pass the primary key and lf event occured dt to access
2308 -- potential data.
2309 --
2310 p_param1 => 'BEN_PPL_IV_PTNL_LER_FOR_PER_ID',
2311 p_param1_value => to_char(p_ptnl_ler_for_per_id),
2312 p_param2 => 'BEN_PPL_IV_LF_EVT_OCRD_DT',
2313 p_param2_value => to_char(p_lf_evt_ocrd_dt, 'YYYY/MM/DD HH24:MI:SS'),
2314 p_param3 => 'BEN_PPL_IV_PTNL_LER_FOR_PER_STAT_CD',
2315 p_param3_value => p_ptnl_ler_for_per_stat_cd,
2316 p_param4 => 'BEN_PPL_IV_NTFN_DT',
2317 p_param4_value => to_char(p_ntfn_dt, 'YYYY/MM/DD HH24:MI:SS'),
2318 p_param5 => 'BEN_PPL_IV_DTCTD_DT',
2319 p_param5_value => to_char(p_dtctd_dt, 'YYYY/MM/DD HH24:MI:SS'),
2320 p_jurisdiction_code=> l_jurisdiction_code);
2321 --
2322 -- Loop through the returned table and make sure that the returned
2323 -- values have been found
2324 --
2325 for l_count in l_outputs.first..l_outputs.last loop
2326 --
2327 begin
2328 --
2329 if l_outputs(l_count).name = 'LIFE_EVENT_OCCURRED_DATE' then
2330 --
2331 l_lf_evt_ocrd_dt := fnd_date.canonical_to_date
2332 (l_outputs(l_count).value);
2333 --
2334 elsif l_outputs(l_count).name = 'LIFE_EVENT_HAPPENED' then
2335 --
2336 l_happened := l_outputs(l_count).value;
2337 --
2338 elsif l_outputs(l_count).name = 'LIFE_EVENT_REASON_ID' then
2339 --
2340 l_ler_id := l_outputs(l_count).value;
2341 --
2342 elsif l_outputs(l_count).name = 'LIFE_EVENT_NOTIFICATION_DATE' then
2343 --
2344 l_ntfn_dt := fnd_date.canonical_to_date
2345 (l_outputs(l_count).value);
2346 --
2347 elsif l_outputs(l_count).name = 'LIFE_EVENT_DETECTED_DATE' then
2348 --
2349 l_dtctd_dt := fnd_date.canonical_to_date
2350 (l_outputs(l_count).value);
2351 --
2352 elsif l_outputs(l_count).name = 'LIFE_EVENT_VOIDED_DATE' then
2353 --
2354 l_voidd_dt := fnd_date.canonical_to_date
2355 (l_outputs(l_count).value);
2356 elsif l_outputs(l_count).name = 'LIFE_EVENT_MANUAL_DATE' then
2357 --
2358 l_mnl_dt := fnd_date.canonical_to_date
2359 (l_outputs(l_count).value);
2360 --
2361 elsif l_outputs(l_count).name = 'LIFE_EVENT_STATUS_CODE' then
2362 --
2363 l_ptnl_ler_for_per_stat_cd := l_outputs(l_count).value;
2364 --
2365 else
2366 --
2367 -- Account for cases where formula returns an unknown
2368 -- variable name
2369 --
2370 fnd_message.set_name('BEN','BEN_92310_FORMULA_RET_PARAM');
2371 fnd_message.set_token('PROC',l_proc);
2372 fnd_message.set_token('FORMULA',p_rule_id);
2373 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
2374 fnd_message.raise_error;
2375 --
2376 end if;
2377 if (l_mnl_dt is not null or
2378 (l_ptnl_ler_for_per_stat_cd is not null and l_ptnl_ler_for_per_stat_cd = 'MNL')) then
2379 l_mnl_savepoint_preestablish := true;
2380 -- savepoint ptnl_set_to_manual_savepoint;
2381 end if;
2382
2383 --
2384 -- Code for type casting errors from formula return variables
2385 --
2386 exception
2387 --
2388 when others then
2389 --
2390 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
2391 fnd_message.set_token('PROC',l_proc);
2392 fnd_message.set_token('FORMULA',p_rule_id);
2393 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
2394 fnd_message.raise_error;
2395 --
2396 end;
2397 --
2398 end loop;
2399 --
2400 -- hr_utility.set_location('life_event_happened '||l_happened , 15);
2401 -- hr_utility.set_location('life_event_status_code '||l_ptnl_ler_for_per_stat_cd, 15);
2402 --
2403 if l_happened not in ('Y','N') then
2404 --
2405 fnd_message.set_name('BEN','BEN_92143_LIFE_EVENT_HAPPENED');
2406 fnd_message.set_token('PROC',l_proc);
2407 fnd_message.raise_error;
2408 --
2409 end if;
2410 --
2411 if l_happened = 'Y' then
2412 --
2413 -- hr_utility.set_location('Step 1 ' ,190);
2414 if p_ler_id <> l_ler_id or
2415 p_lf_evt_ocrd_dt <> l_lf_evt_ocrd_dt or
2416 nvl(p_ntfn_dt,hr_api.g_date) <> nvl(l_ntfn_dt,hr_api.g_date) or
2417 nvl(p_dtctd_dt,hr_api.g_date) <> nvl(l_dtctd_dt,hr_api.g_date) or
2418 nvl(p_voidd_dt,hr_api.g_date) <> nvl(l_voidd_dt,hr_api.g_date) or
2419 p_ptnl_ler_for_per_stat_cd <> l_ptnl_ler_for_per_stat_cd then
2420 --
2421 -- Sanity check, they may have updated the voidd_dt but forgot to
2422 -- set the ptnl_ler_for_per_stat_cd so we set it for them
2423 --
2424 -- hr_utility.set_location('Step 2 ' ,191);
2425 if l_voidd_dt is not null and
2426 l_ptnl_ler_for_per_stat_cd <> 'VOIDD' then
2427 --
2428 l_ptnl_ler_for_per_stat_cd := 'VOIDD';
2429 --
2430 -- In this case we have to force the rule to think that the rule
2431 -- did not happen as we want the event to be voided but we also
2432 -- want to update certain columns.
2433 --
2434 l_happened := 'N';
2435 --hr_utility.set_location(' In the didnot happened case ', 15);
2436 --
2437 end if;
2438 --
2439 -- We may have had a different life event reason returned in which
2440 -- case we need to void the old life event and create a new ptnl
2441 -- life event. In this case carry out the void with the old values
2442 -- and create the new with the new returned values from the
2443 -- formula. In this case the event happened.
2444 --
2445 -- hr_utility.set_location('Step 3 ' ,193);
2446 if l_ler_id <> p_ler_id then
2447
2448 -- Test for valid l_ptnl_ler_for_per_stat_cd
2449 if l_ptnl_ler_for_per_stat_cd not in ('DTCTD','MNL','UNPROCD') then
2450 -- hr_utility.set_location('Invalid stat code returned from rule.',10);
2451 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
2452 fnd_message.raise_error;
2453 end if;
2454
2455 --
2456 -- First lets create the new ptnl ler for per
2457 --
2458 -- If the fast formula rule returned anything other than 'DTCTD',
2459 -- create the new potential life event with a status of 'UNPROCD'.
2460
2461 if l_ptnl_ler_for_per_stat_cd = 'DTCTD' then
2462 l_ptnl_ler_for_per_stat_cd_use := 'DTCTD';
2463 else
2464 l_ptnl_ler_for_per_stat_cd_use := 'UNPROCD';
2465 -- hr_utility.set_location('Step 4 ' ,194);
2466 end if;
2467 --hr_utility.set_location(' r_per_stat_cd '||l_ptnl_ler_for_per_stat_cd, 17);
2468 if l_ptnl_ler_for_per_stat_cd = 'MNL' then
2469 l_unprocd_dt := l_mnl_dt;
2470 else
2471 l_unprocd_dt := sysdate;
2472 end if;
2473
2474 --
2475 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
2476 (p_validate => false,
2477 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
2478 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2479 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd_use,
2480 p_ler_id => l_ler_id,
2481 p_person_id => p_person_id,
2482 p_ntfn_dt => l_ntfn_dt,
2483 p_unprocd_dt => l_unprocd_dt,
2484 p_dtctd_dt => l_dtctd_dt,
2485 p_business_group_id => p_business_group_id,
2486 p_object_version_number => l_object_version_number,
2487 p_effective_date => p_effective_date,
2488 p_program_application_id => fnd_global.prog_appl_id,
2489 p_program_id => fnd_global.conc_program_id,
2490 p_request_id => fnd_global.conc_request_id,
2491 p_program_update_date => sysdate);
2492 --
2493 -- hr_utility.set_location('Step 5 ' ,195);
2494 if l_ptnl_ler_for_per_stat_cd = 'MNL' then
2495 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2496 (p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
2497 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2498 p_ler_id => l_ler_id,
2499 p_ntfn_dt => l_ntfn_dt,
2500 p_dtctd_dt => l_dtctd_dt,
2501 p_voidd_dt => l_voidd_dt,
2502 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
2503 p_object_version_number => l_object_version_number,
2504 p_effective_date => p_effective_date,
2505 p_mnl_dt => l_mnl_dt,
2506 p_procd_dt => l_procd_dt,
2507 p_unprocd_dt => l_unprocd_dt);
2508 end if;
2509 --hr_utility.set_location('Step 6 ' ,196);
2510 l_happened := 'Y';
2511 --
2512 -- Make sure that when we update the ptnl ler for per that the
2513 -- voided date and stat code are set correctly.
2514 --
2515 l_ptnl_ler_for_per_stat_cd := 'VOIDD';
2516 l_voidd_dt := nvl(l_voidd_dt,sysdate);
2517 l_unprocd_dt := NULL;
2518 l_dtctd_dt := NULL;
2519 l_mnl_dt := NULL;
2520 --
2521 end if;
2522 --
2523 -- The rule has reset some values on the ptnl per for ler.
2524 -- We need to update the ben_ptnl_ler_for_per record in order
2525 -- to reflect the evaluated rule.
2526 --
2527 l_object_version_number := p_object_version_number;
2528 --
2529 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2530 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
2531 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2532 p_ler_id => p_ler_id,
2533 p_ntfn_dt => l_ntfn_dt,
2534 p_dtctd_dt => l_dtctd_dt,
2535 p_voidd_dt => l_voidd_dt,
2536 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
2537 p_object_version_number => l_object_version_number,
2538 p_effective_date => p_effective_date,
2539 p_mnl_dt => l_mnl_dt,
2540 p_procd_dt => l_procd_dt,
2541 p_unprocd_dt => l_unprocd_dt);
2542 -- hr_utility.set_location('Step 7 ' ,197);
2543 --
2544 end if;
2545 --
2546 --hr_utility.set_location('Step 8 ' ,198);
2547 end if;
2548 --
2549 -- hr_utility.set_location('l_happened before return '||l_happened , 18 );
2550 return l_happened;
2551 --
2552 end if;
2553 --
2554 hr_utility.set_location('Leaving:'|| l_proc, 10);
2555 --
2556 end rule_evaluates;
2557 --
2558 procedure check_and_get_winner
2559 (p_lf_evt_ocrd_dt in date,
2560 p_person_id in number,
2561 p_business_group_id in number,
2562 p_effective_date in date,
2563 p_ptnl_rec out NOCOPY ben_ptnl_ler_for_per%rowtype) is
2564 --
2565 l_proc varchar2(72) := g_package||'check_and_get_winner';
2566 l_num_recs number := 0;
2567 l_num_winners number := 0;
2568 --
2569 -- Bugs : 3179/3249 : Now consider processed potentials as well to
2570 -- look for the winner.
2571 --
2572 cursor c1 is
2573 select ler.ovridg_le_flag,
2574 ppl.ptnl_ler_for_per_id,
2575 ler.name
2576 from ben_ptnl_ler_for_per ppl,
2577 ben_ler_f ler
2578 where ppl.person_id = p_person_id
2579 and ppl.ler_id = ler.ler_id
2580 and ppl.business_group_id = p_business_group_id
2581 and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2582 and ler.business_group_id = ppl.business_group_id
2583 and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD')
2584 and ppl.ler_id <> ben_manage_life_events.g_ler_id
2585 --
2586 -- CWB Changes
2587 --
2588 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2589 and p_effective_date
2590 between ler.effective_start_date
2591 and ler.effective_end_date;
2592 --
2593 l_ovridg_le_flag varchar2(30);
2594 l_win_ler_name ben_ler_f.name%TYPE; -- UTF8 varchar2(150);
2595 l_ler_name ben_ler_f.name%TYPE; -- UTF8 varchar2(150);
2596 l_ptnl_ler_for_per_id number(15);
2597 l_win_ptnl_ler_for_per_id number(15);
2598 l_search_ptnl_ler_for_per_id number(15);
2599 l_c1 c1%rowtype;
2600 --
2601 -- The following line is deleted from the where clause to
2602 --
2603 -- ****and ppl.ptnl_ler_for_per_id <> l_search_ptnl_ler_for_per_id ****
2604 --
2605 -- as the check is made in the loop.
2606 --
2607 -- Bugs : 3179/3249 : Now consider processed potentials as well to
2608 -- back out them and set the potentials to unprocessed.
2609 -- Then loop around the potentials and void the ones which are not
2610 -- processed.
2611 --
2612 --
2613 -- CWB Changes : Cursor joined to ben_ler_f
2614 --
2615 cursor c_ptnl is
2616 select ppl.object_version_number,
2617 ppl.ptnl_ler_for_per_id
2618 from ben_ptnl_ler_for_per ppl,
2619 ben_ler_f ler
2620 where ppl.person_id = p_person_id
2621 and ppl.business_group_id = p_business_group_id
2622 and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2623 and ppl.ler_id <> ben_manage_life_events.g_ler_id
2624 and ppl.ler_id = ler.ler_id
2625 and p_effective_date
2626 between ler.effective_start_date
2627 and ler.effective_end_date
2628 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2629 and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD');
2630 --
2631 -- CWB Changes End
2632 --
2633 cursor c2 is
2634 select *
2635 from ben_ptnl_ler_for_per ptn
2636 where ptn.ptnl_ler_for_per_id = l_search_ptnl_ler_for_per_id;
2637 --
2638 -- Bug 1146792 (4285) : Modified check_and_get_winner -
2639 -- Back out the per in ler's before determining
2640 -- the winner.
2641 --
2642 -- CWB Changes : Cursor joined to ben_ler_f
2643 --
2644 cursor c_pils_to_backout is
2645 select pil.*
2646 from ben_per_in_ler pil,
2647 ben_ler_f ler
2648 where pil.business_group_id = p_business_group_id
2649 and pil.person_id = p_person_id
2650 and (pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt -- 5727737/5677090: Need to backout all Future LEs
2651 or (pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2652 and pil.ler_id <> ben_manage_life_events.g_ler_id))
2653 and pil.ler_id = ler.ler_id
2654 and p_effective_date
2655 between ler.effective_start_date
2656 and ler.effective_end_date
2657 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC','SCHEDDU') --iRec
2658 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
2659 ORDER BY pil.lf_evt_ocrd_dt DESC;
2660 --
2661 -- CWB Changes End
2662 --
2663 l_per_in_ler_id number;
2664 --
2665 cursor c_ptnl_ovn(v_ptnl_ler_for_per_id number) is
2666 select ptnl.object_version_number
2667 from ben_ptnl_ler_for_per ptnl
2668 where ptnl.ptnl_ler_for_per_id = v_ptnl_ler_for_per_id
2669 and ptnl.business_group_id = p_business_group_id;
2670 --
2671 l_ptnl_ovn number;
2672 --
2673
2674 begin
2675 --
2676 hr_utility.set_location('Entering: '|| l_proc, 10);
2677 --
2678 -- First back out any ptnls.
2679 --
2680 --
2681 -- Bug 1146792 (4285) : Modified check_and_get_winner -
2682 -- Back out the per in ler's before determining
2683 -- the winner.
2684 --
2685 -- Any processed or started life events on the same day have
2686 -- to be backed out prior to processing the winner.
2687 --
2688 for l_pil_rec in c_pils_to_backout loop
2689 --
2690 ben_back_out_life_event.back_out_life_events
2691 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
2692 p_bckt_per_in_ler_id => null,
2693 p_bckt_stat_cd => 'UNPROCD',
2694 p_business_group_id => p_business_group_id,
2695 p_effective_date => p_effective_date);
2696 --
2697
2698 --Start 6086392
2699 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
2700 l_bckdt_pil_count := l_bckdt_pil_count +1;
2701 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_pil_rec.per_in_ler_id;
2702 --End 6086392
2703
2704 end loop;
2705 --
2706 -- Lets loop through all the jobs that occured on the p_lf_evt_ocrd_dt and
2707 -- count the winners and the number of rec with the same date
2708 --
2709 open c1;
2710 --
2711 loop
2712 --
2713 fetch c1 into l_ovridg_le_flag,
2714 l_ptnl_ler_for_per_id,
2715 l_ler_name;
2716 exit when c1%notfound;
2717 --
2718 l_num_recs := l_num_recs + 1; -- '+1' added during irec
2719 --
2720 if l_ovridg_le_flag = 'Y' then
2721 --
2722 l_num_winners := l_num_winners + 1;-- '+' added during irec
2723 l_win_ptnl_ler_for_per_id := l_ptnl_ler_for_per_id;
2724 l_win_ler_name := l_win_ler_name;
2725 --
2726 end if;
2727 --
2728 end loop;
2729 --
2730 close c1;
2731 --
2732 -- Now lets check if we break any rules
2733 --
2734 if l_num_winners > 1 then
2735 --
2736 -- More than one winner so return an error
2737 --
2738 fnd_message.set_name('BEN','BEN_91794_DUP_WINNERS');
2739 ben_manage_life_events.g_rec.rep_typ_cd := 'ERROR';
2740 ben_manage_life_events.g_rec.person_id := p_person_id;
2741 ben_manage_life_events.g_rec.ler_id := null;
2742 ben_manage_life_events.g_rec.error_message_code := 'BEN_91794_DUP_WINNERS';
2743 ben_manage_life_events.g_rec.text := fnd_message.get;
2744 fnd_message.set_name('BEN','BEN_91794_DUP_WINNERS');
2745 fnd_message.set_token('PROC',l_proc);
2746 fnd_message.set_token('PERSON_ID',p_person_id);
2747 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2748 raise ben_manage_life_events.g_record_error;
2749 --
2750 -- No winners, but more than one potential?
2751 --
2752 elsif l_num_winners = 0 and
2753 l_num_recs > 1 then
2754 --
2755 -- Potential life events clash, so error.
2756 --
2757 ben_manage_life_events.g_rec.rep_typ_cd := 'ERROR';
2758 ben_manage_life_events.g_rec.person_id := p_person_id;
2759 ben_manage_life_events.g_rec.ler_id := null;
2760 ben_manage_life_events.g_rec.error_message_code := 'BEN_92337_DUPLICATE_PTNL_LE';
2761 ben_manage_life_events.g_rec.text := fnd_message.get;
2762 fnd_message.set_name('BEN','BEN_92337_DUPLICATE_PTNL_LE');
2763 fnd_message.set_token('PROC',l_proc);
2764 fnd_message.set_token('PERSON_ID',p_person_id);
2765 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2766 raise ben_manage_life_events.g_record_error;
2767 --
2768 elsif l_num_winners = 1 then
2769 --
2770 l_search_ptnl_ler_for_per_id := l_win_ptnl_ler_for_per_id;
2771 --
2772 -- Wining potential found but there are multiple potentials, so
2773 -- void the other potentials
2774 --
2775 if l_num_recs > 1 then
2776 --
2777 for l_ptnl in c_ptnl loop
2778 --
2779 if l_ptnl.ptnl_ler_for_per_id <> l_search_ptnl_ler_for_per_id then
2780 --
2781 -- Get the object_version_number as the life event might have
2782 -- been bolfied resulting in new object version number.
2783 --
2784 /*
2785 open c_ptnl_ovn(l_ptnl.ptnl_ler_for_per_id);
2786 fetch c_ptnl_ovn into l_ptnl_ovn;
2787 close c_ptnl_ovn;
2788 */
2789 --
2790 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2791 (p_validate => false
2792 ,p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id
2793 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
2794 ,p_object_version_number => l_ptnl.object_version_number
2795 ,p_effective_date => p_effective_date
2796 ,p_program_application_id => fnd_global.prog_appl_id
2797 ,p_program_id => fnd_global.conc_program_id
2798 ,p_request_id => fnd_global.conc_request_id
2799 ,p_program_update_date => sysdate
2800 ,p_voidd_dt => p_effective_date);
2801 --
2802 end if;
2803 --
2804 end loop;
2805 --
2806 end if;
2807 --
2808 elsif l_num_recs = 1 then
2809 --
2810 l_search_ptnl_ler_for_per_id := l_ptnl_ler_for_per_id;
2811 l_win_ler_name := l_ler_name;
2812 --
2813 elsif l_num_recs = 0 then
2814 --
2815 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
2816 fnd_message.set_token('PROC',l_proc);
2817 fnd_message.set_token('PERSON_ID',p_person_id);
2818 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2819 raise ben_manage_life_events.g_record_error;
2820 --
2821 end if;
2822 --
2823 open c2;
2824 --
2825 fetch c2 into p_ptnl_rec;
2826 --
2827 if p_ptnl_rec.ptnl_ler_for_per_stat_cd = 'MNL' then
2828 --
2829 close c2;
2830 --
2831 -- 6245213 : Rollback all backouts, since the LE is going to MANUAL.
2832 --
2833 if (l_mnl_savepoint_established) then
2834 hr_utility.set_location ('Going to Manual. Rollback all Backouts' ,100);
2835 rollback to ptnl_set_to_manual_savepoint;
2836 l_mnl_savepoint_established := false;
2837 l_mnl_savepoint_preestablish:=false;
2838 end if;
2839 ---
2840 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2841 fnd_message.set_token('LE_NAME',l_win_ler_name);
2842 fnd_message.set_token('PROC',l_proc);
2843 benutils.write(p_text => fnd_message.get);
2844 -- For BENAUTHE retreival purpose.
2845 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2846 fnd_message.set_token('LE_NAME',l_win_ler_name);
2847 fnd_message.set_token('PROC',l_proc);
2848 raise ben_manage_life_events.g_life_event_after;
2849 --
2850 end if;
2851 --
2852 close c2;
2853 --
2854
2855 hr_utility.set_location('Lea l_win_ler_name '||l_win_ler_name , 9.9);
2856 hr_utility.set_location('Leaving:'|| l_proc, 10);
2857 --
2858 end check_and_get_winner;
2859 --
2860 function get_earliest_potential(p_person_id in number,
2861 p_business_group_id in number,
2862 p_mode in varchar2,
2863 p_effective_date in date) return date is
2864 --
2865 l_proc varchar2(72) := g_package||'get_earliest_potential';
2866 --
2867 --
2868 -- CWB Changes : Cursor joined to ben_ler_f
2869 --
2870 cursor c1 is
2871 select min(ptn.lf_evt_ocrd_dt)
2872 from ben_ptnl_ler_for_per ptn,
2873 ben_ler_f ler
2874 where ptn.person_id = p_person_id
2875 and ptn.ler_id = ler.ler_id
2876 and p_effective_date
2877 between ler.effective_start_date
2878 and ler.effective_end_date
2879 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2880 and ptn.business_group_id = p_business_group_id
2881 and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
2882 'SCHEDDO',
2883 ptn.lf_evt_ocrd_dt,
2884 'SCHEDDA',
2885 ptn.lf_evt_ocrd_dt,
2886 p_effective_date)
2887 and ptn.ler_id <> ben_manage_life_events.g_ler_id
2888 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
2889 --
2890 -- CWB Changes End
2891 --
2892 l_min_lf_evt_ocrd_dt date;
2893 --
2894 begin
2895 --
2896 hr_utility.set_location('Entering:'|| l_proc, 10);
2897 --
2898 open c1;
2899 --
2900 fetch c1 into l_min_lf_evt_ocrd_dt;
2901 --
2902 -- Remember the min function always returns a row so check if the result
2903 -- is null rather than c1%notfound.
2904 --
2905 if l_min_lf_evt_ocrd_dt is null then
2906 --
2907 close c1;
2908 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
2909 fnd_message.set_token('PROC',l_proc);
2910 fnd_message.set_token('PERSON_ID',p_person_id);
2911 fnd_message.set_token('LF_EVT_OCRD_DT',l_min_lf_evt_ocrd_dt);
2912 benutils.write(p_text => fnd_message.get);
2913 -- For BENAUTHE retreival purpose.
2914 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
2915 fnd_message.set_token('PROC',l_proc);
2916 fnd_message.set_token('PERSON_ID',p_person_id);
2917 fnd_message.set_token('LF_EVT_OCRD_DT',l_min_lf_evt_ocrd_dt);
2918 raise ben_manage_life_events.g_life_event_after;
2919 --
2920 end if;
2921 --
2922 close c1;
2923 --
2924 hr_utility.set_location('Leaving:'|| l_proc, 10);
2925 --
2926 return l_min_lf_evt_ocrd_dt;
2927 --
2928 end get_earliest_potential;
2929 --
2930 begin
2931 --
2932 hr_utility.set_location('Entering:'|| l_proc, 10);
2933
2934 --Start 6086392
2935 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
2936 --End 6086392
2937
2938 --
2939 -- Operation Steps
2940 -- ===============
2941 -- 1) Get potential life events and active life event
2942 -- 2) If a rule is attached evaluate rule
2943 -- 3) If rule evaluates to N then void life event
2944 -- 4) If rule evaluates to Y then fine. Apply life event occured date and
2945 -- new life event reason if required.
2946 -- 5) Keep account of minimum life event occurred date of valid life events
2947 -- 6) Check each potential life event for timeliness and void if neccessary
2948 -- 7) Perform collapsing logic
2949 -- 8) Check for winners
2950 --
2951 open get_all_potential;
2952 --
2953 loop
2954 --
2955 fetch get_all_potential into l_potent;
2956 exit when get_all_potential%notfound;
2957 --
2958 l_recs_found := true;
2959 --
2960 -- Bug 1177226 : Any potential with manual hit then just
2961 -- stop the process.
2962 --
2963 if l_potent.ptnl_ler_for_per_stat_cd = 'MNL' then
2964 --
2965 fnd_message.set_name('BEN','BEN_94209_MAN_LER_EXISTIS');
2966 -- fnd_message.set_token('LE_NAME',l_potent.name);
2967 -- fnd_message.set_token('PROC',l_proc);
2968 benutils.write(p_text => fnd_message.get);
2969 -- For BENAUTHE retreival purpose.
2970 fnd_message.set_name('BEN','BEN_94209_MAN_LER_EXISTIS');
2971 -- fnd_message.set_token('LE_NAME',l_potent.name);
2972 -- fnd_message.set_token('PROC',l_proc);
2973 raise ben_manage_life_events.g_life_event_after;
2974 --
2975 end if;
2976 --
2977 -- If there is a rule attached lets evaluate it and handle the returned
2978 -- values.
2979 --
2980 hr_utility.set_location(l_potent.ler_eval_rl,10);
2981 if rule_evaluates
2982 (p_ler_id => l_potent.ler_id,
2983 p_person_id => p_person_id,
2984 p_business_group_id => p_business_group_id,
2985 p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id,
2986 p_ptnl_ler_for_per_stat_cd => l_potent.ptnl_ler_for_per_stat_cd,
2987 p_ntfn_dt => l_potent.ntfn_dt,
2988 p_dtctd_dt => l_potent.dtctd_dt,
2989 p_voidd_dt => l_potent.voidd_dt,
2990 p_object_version_number => l_potent.object_version_number,
2991 p_lf_evt_ocrd_dt => l_potent.lf_evt_ocrd_dt,
2992 p_rule_id => l_potent.ler_eval_rl,
2993 p_effective_date => p_effective_date) <> 'Y' then
2994 --
2995 -- The life event didn't happen so void it
2996 --
2997 hr_utility.set_location('After call to rule_evaluates ',19);
2998 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2999 (p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id,
3000 p_object_version_number => l_potent.object_version_number,
3001 p_ptnl_ler_for_per_stat_cd => 'VOIDD',
3002 p_effective_date => p_effective_date,
3003 p_voidd_dt => p_effective_date);
3004 --
3005 end if;
3006 --
3007 end loop;
3008 --
3009 close get_all_potential;
3010 --
3011 -- Test for no potentials found error
3012 --
3013 if not l_recs_found then
3014 --
3015 -- Person already has a scheduled life event that has been run on this day
3016 -- We don't want to add to the error count so lets just process the next
3017 -- person. The life event could be strtd or procd we don't care.
3018 --
3019 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
3020 fnd_message.set_token('PERSON_ID',p_person_id);
3021 fnd_message.set_token('PROC',l_proc);
3022 benutils.write(fnd_message.get);
3023 --
3024 -- For BENAUTHE
3025 --
3026 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
3027 fnd_message.set_token('PERSON_ID',p_person_id);
3028 fnd_message.set_token('PROC',l_proc);
3029 raise ben_manage_life_events.g_life_event_after;
3030 --
3031 end if;
3032 --
3033 -- Now we have manipulated all potentials, check whether the remaining
3034 -- potentials fall within the timeliness
3035 --
3036 hr_utility.set_location('Before entering check_for_timeliness',90);
3037 --
3038 -- 6245213 : If Event goes to manual due to timeliness then
3039 -- set savepoint l_mnl_savepoint_established in the proc. check_for_timeliness
3040 -- and rollbck to savepoint in proc.check_and_get_winner
3041 --
3042 check_for_timeliness
3043 (p_person_id => p_person_id,
3044 p_effective_date => p_effective_date,
3045 p_mode => p_mode,
3046 p_business_group_id => p_business_group_id);
3047 --
3048 hr_utility.set_location('After leaving check_for_timeliness',90);
3049 --
3050 -- Check we actually have potential life events and get the minimum life
3051 -- event occurred of the set of potential life events
3052 --
3053 l_min_lf_evt_ocrd_dt := get_earliest_potential
3054 (p_person_id => p_person_id,
3055 p_business_group_id => p_business_group_id,
3056 p_mode => p_mode,
3057 p_effective_date => p_effective_date);
3058 --
3059 -- Do the collapse, Waaahoooo this is going to be good
3060 --
3061 ben_collapse_life_event.main
3062 (p_person_id => p_person_id,
3063 p_business_group_id => p_business_group_id,
3064 p_min_lf_evt_ocrd_dt => l_min_lf_evt_ocrd_dt,
3065 p_mode => p_mode,
3066 p_effective_date => p_effective_date);
3067 --
3068 -- We have to reget the min_lf_evt_ocrd_dt as a replace or void may have
3069 -- occurred and the minimum life event occurred date may be different to
3070 -- what it was prior to the calll.
3071 --
3072 l_min_lf_evt_ocrd_dt := get_earliest_potential
3073 (p_person_id => p_person_id,
3074 p_business_group_id => p_business_group_id,
3075 p_mode => p_mode,
3076 p_effective_date => p_effective_date);
3077 --
3078 -- Check for potentials that existed on the same day or multiple winners
3079 -- If there are multiple winners then error
3080 -- If there are multiple potentials but one winner then delete potentials
3081 -- If there are multiple potentials but no winners then error
3082 --
3083 check_and_get_winner(p_lf_evt_ocrd_dt => l_min_lf_evt_ocrd_dt,
3084 p_person_id => p_person_id,
3085 p_business_group_id => p_business_group_id,
3086 p_effective_date => p_effective_date,
3087 p_ptnl_rec => l_ptnl_rec);
3088 --
3089 --
3090 -- Bug 4872042
3091 --
3092 open c_winner_ler_typ_cd (cv_ler_id => l_ptnl_rec.ler_id);
3093 fetch c_winner_ler_typ_cd into l_typ_cd, l_ler_name;
3094 close c_winner_ler_typ_cd;
3095 --
3096 -- Here we need to check if winner life event type and BENMNGLE mode are different
3097 -- If different => then commit the COLLAPSING LOGIC and exit the process so that user has
3098 -- to explicitly process the winner life event in appropriate mode
3099 --
3100 -- Similar check also exists in benmngle.pkb
3101 --
3102 if /*(p_mode = 'C' and l_typ_cd not like 'SCHEDD%') or --commented against bug 6806014 */
3103 --Bug 4872042
3104 (p_mode = 'L' and l_typ_cd in ('SCHEDDU','UNRSTR')) -- remove 'SCHEDDO'
3105 then
3106 --
3107 benutils.write(p_text => 'Winner Life Event : ' || l_ler_name || benutils.id(l_ptnl_rec.ler_id) ||
3108 ', Supplied Mode : ' || p_mode);
3109 --
3110 if (l_typ_cd like 'SCHEDD%' or l_typ_cd = 'UNRSTR')
3111 then
3112 l_use_mode := 'Scheduled mode';
3113 else
3114 l_use_mode := 'Life event mode';
3115 end if;
3116 --
3117 if not p_validate
3118 then
3119 --
3120 commit;
3121 --
3122 savepoint process_life_event_savepoint;
3123 --
3124 end if;
3125 --
3126 fnd_message.set_name('BEN','BEN_92145_MODE_LE_DIFFER');
3127 fnd_message.set_token('MODE',l_use_mode);
3128 --
3129 raise ben_manage_life_events.g_record_error;
3130 --
3131 end if;
3132 --
3133 -- Bug 4872042
3134 --
3135 -- Lets do the backing out
3136 -- for the day of the minimum life event.
3137 --
3138 -- Get the current per in ler details so we decide whether to back out
3139 -- or whatever!
3140 --
3141 --
3142 -- Bug : 3078 (PBODLA)
3143 -- BENBOLFE currently only backs out closed person life events
3144 -- when there is a current started life event. BENBOLFE needs to
3145 -- backout all future life events.
3146 -- Due to above bug following two lines are commented and a local
3147 -- cursor is used to get pil's whose status is STRTD, PROCD
3148 --
3149 -- ben_person_object.get_object(p_person_id => p_person_id,
3150 -- p_rec => l_pil_rec);
3151 open get_current_per_in_ler(l_ptnl_rec.lf_evt_ocrd_dt);
3152 --
3153 fetch get_current_per_in_ler into l_current_per_in_ler;
3154 --
3155 close get_current_per_in_ler;
3156 --
3157 hr_utility.set_location('active LED '||l_current_per_in_ler.lf_evt_ocrd_dt,10);
3158 hr_utility.set_location('ptnl LED '||l_ptnl_rec.lf_evt_ocrd_dt,10);
3159 -- If no PER_IN_LER exists then we
3160 -- insert PTNL into PER_IN_LER
3161 -- update BEN_PTNL_LER_FOR_PER setting to processed
3162 --
3163 -- else
3164 -- We now we have the minimum PTNL PER_IN_LER
3165 -- and the date of the current PER in LER
3166 -- we should compare them and if the
3167 -- ptnl starts before the current
3168 -- we should delete the current and
3169 -- insert the ptnl into the PER_IN_LER table
3170 -- updating the current's old PTNL PER_FOR_LER
3171 -- record to be unprocessed.
3172 --
3173 --
3174 if l_current_per_in_ler.lf_evt_ocrd_dt is null then
3175 --
3176 hr_utility.set_location('A',10);
3177 -- insert ptnl
3178 --
3179 l_created_ler := 'Y';
3180 p_created_ler_id := l_ptnl_rec.ler_id;
3181 --
3182 insert_per_in_ler
3183 (p_ptnl_rec => l_ptnl_rec
3184 ,p_curr_per_in_ler_id => l_curr_per_in_ler_id
3185 ,p_effective_date => p_effective_date);
3186 --
3187 g_rec.person_id := p_person_id;
3188 g_rec.ler_id := l_ptnl_rec.ler_id;
3189 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3190 g_rec.replcd_flag := 'N';
3191 g_rec.crtd_flag := 'Y';
3192 g_rec.tmprl_flag := 'N';
3193 g_rec.dltd_flag := 'N';
3194 g_rec.open_and_clsd_flag := 'N';
3195 g_rec.not_crtd_flag := 'N';
3196 g_rec.clsd_flag := 'N';
3197 g_rec.stl_actv_flag := 'N';
3198 g_rec.clpsd_flag := 'N';
3199 g_rec.clsn_flag := 'N';
3200 g_rec.no_effect_flag := 'N';
3201 g_rec.cvrge_rt_prem_flag := 'N';
3202 g_rec.business_group_id := p_business_group_id;
3203 g_rec.effective_date := p_effective_date;
3204 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
3205 --
3206 benutils.write(p_rec => g_rec);
3207 --
3208 -- update ptnl
3209 --
3210 update_ptnl_per_for_ler
3211 (p_ptnl_rec => l_ptnl_rec
3212 ,p_effective_date => p_effective_date);
3213 --
3214 elsif l_ptnl_rec.lf_evt_ocrd_dt <= l_current_per_in_ler.lf_evt_ocrd_dt then
3215 --
3216 -- Bug : 3179 : Check any dead lock situation.
3217 -- Any pil's which occured on same day.
3218 --
3219 hr_utility.set_location('B',10);
3220 open c_check_deadlock_pil(l_ptnl_rec.lf_evt_ocrd_dt);
3221 --
3222 fetch c_check_deadlock_pil into l_deadlock_per_in_ler;
3223 --
3224 -- Bug : 3179 : pbodla : See case desicription below.
3225 -- 1. Created New hire say on 10/14/1999
3226 -- 2. Processed the new hire on 10/14/1999
3227 -- 3. Now a marriage reported.
3228 -- 4. Marriage PIL is created.
3229 -- 5. When marriage is processed new hire is backed out.
3230 -- and associated potential is made unprocessed.
3231 -- 6. Again when new hire potential is processed it causes
3232 -- marriage to back out as they happened on same day.
3233 -- 7. It is dead lock situation.
3234 -- 8. WDS, Phil, Pbodla : Decision is to set the ptnl to
3235 -- to manual if there is a processed or started potential
3236 -- on same day.
3237 --
3238 if l_deadlock_per_in_ler.per_in_ler_id is not null then
3239 --
3240 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
3241 (p_validate => false
3242 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3243 ,p_ptnl_ler_for_per_stat_cd => 'MNL'
3244 ,p_object_version_number => l_ptnl_rec.object_version_number
3245 ,p_effective_date => p_effective_date
3246 ,p_program_application_id => fnd_global.prog_appl_id
3247 ,p_program_id => fnd_global.conc_program_id
3248 ,p_request_id => fnd_global.conc_request_id
3249 ,p_program_update_date => sysdate
3250 ,p_mnl_dt => p_effective_date);
3251 --
3252 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
3253 fnd_message.set_token('LE_NAME',l_potent.name);
3254 fnd_message.set_token('PROC',l_proc);
3255 benutils.write(p_text => fnd_message.get);
3256 -- For BENAUTHE retreival purpose.
3257 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
3258 fnd_message.set_token('LE_NAME',l_potent.name);
3259 fnd_message.set_token('PROC',l_proc);
3260 g_rec.person_id := p_person_id;
3261 g_rec.ler_id := l_ptnl_rec.ler_id;
3262 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3263 g_rec.replcd_flag := 'N';
3264 g_rec.crtd_flag := 'N';
3265 g_rec.tmprl_flag := 'N';
3266 g_rec.dltd_flag := 'N';
3267 g_rec.open_and_clsd_flag := 'N';
3268 g_rec.not_crtd_flag := 'N';
3269 g_rec.clsd_flag := 'N';
3270 g_rec.stl_actv_flag := 'N';
3271 g_rec.clpsd_flag := 'N';
3272 g_rec.clsn_flag := 'Y';
3273 g_rec.no_effect_flag := 'N';
3274 g_rec.cvrge_rt_prem_flag := 'N';
3275 g_rec.business_group_id := p_business_group_id;
3276 g_rec.effective_date := p_effective_date;
3277 benutils.write(p_rec => g_rec);
3278 --
3279 raise ben_manage_life_events.g_life_event_after;
3280 --
3281 end if;
3282 --
3283 close c_check_deadlock_pil;
3284 --
3285 -- insert ptnl into per in ler
3286 --
3287 l_created_ler := 'Y';
3288 p_created_ler_id := l_ptnl_rec.ler_id;
3289 insert_per_in_ler
3290 (p_ptnl_rec => l_ptnl_rec
3291 ,p_curr_per_in_ler_id => l_curr_per_in_ler_id
3292 ,p_effective_date => p_effective_date);
3293 --
3294 g_rec.person_id := p_person_id;
3295 g_rec.ler_id := l_ptnl_rec.ler_id;
3296 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3297 g_rec.replcd_flag := 'Y';
3298 g_rec.crtd_flag := 'N';
3299 g_rec.tmprl_flag := 'N';
3300 g_rec.dltd_flag := 'N';
3301 g_rec.open_and_clsd_flag := 'N';
3302 g_rec.not_crtd_flag := 'N';
3303 g_rec.clsd_flag := 'N';
3304 g_rec.stl_actv_flag := 'N';
3305 g_rec.clpsd_flag := 'N';
3306 g_rec.clsn_flag := 'N';
3307 g_rec.no_effect_flag := 'N';
3308 g_rec.cvrge_rt_prem_flag := 'N';
3309 g_rec.business_group_id := p_business_group_id;
3310 g_rec.effective_date := p_effective_date;
3311 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
3312 --
3313 benutils.write(p_rec => g_rec);
3314 --
3315 -- update ptnl for per_in_ler
3316 --
3317 update_ptnl_per_for_ler
3318 (p_ptnl_rec => l_ptnl_rec
3319 ,p_effective_date => p_effective_date);
3320 --
3321 -- Fix for April release is to remove all per in lers that occured
3322 -- after the current potential.
3323 --
3324 open get_all_per_in_ler(l_ptnl_rec.lf_evt_ocrd_dt, l_curr_per_in_ler_id);
3325 --
3326 loop
3327 --
3328 hr_utility.set_location(l_proc||' Loop GAPIL ', 50);
3329 fetch get_all_per_in_ler into l_pil_rec;
3330 exit when get_all_per_in_ler%notfound;
3331 --
3332 -- First back out all the relevant stuff
3333 --
3334 -- Use effective start date of per in ler as this is the only
3335 -- real safe way of making sure that future stuff will back out
3336 -- correctly.
3337 --
3338 ben_back_out_life_event.back_out_life_events
3339 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
3340 p_bckt_per_in_ler_id => l_curr_per_in_ler_id,
3341 p_business_group_id => p_business_group_id,
3342 p_effective_date => p_effective_date);
3343 --
3344 end loop;
3345 --
3346 close get_all_per_in_ler;
3347 --
3348 else
3349 --
3350 -- Potential is after per in ler so leave ptnl as is
3351 --
3352 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
3353 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_ptnl_rec.lf_evt_ocrd_dt);
3354 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
3355 l_current_per_in_ler.lf_evt_ocrd_dt);
3356 benutils.write(p_text => fnd_message.get);
3357 --
3358 g_rec.person_id := p_person_id;
3359 g_rec.ler_id := l_ptnl_rec.ler_id;
3360 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3361 g_rec.replcd_flag := 'N';
3362 g_rec.crtd_flag := 'N';
3363 g_rec.tmprl_flag := 'N';
3364 g_rec.dltd_flag := 'N';
3365 g_rec.open_and_clsd_flag := 'N';
3366 g_rec.not_crtd_flag := 'N';
3367 g_rec.clsd_flag := 'N';
3368 g_rec.stl_actv_flag := 'Y';
3369 g_rec.clpsd_flag := 'N';
3370 g_rec.clsn_flag := 'N';
3371 g_rec.no_effect_flag := 'N';
3372 g_rec.cvrge_rt_prem_flag := 'N';
3373 g_rec.business_group_id := p_business_group_id;
3374 g_rec.effective_date := p_effective_date;
3375 --
3376 benutils.write(p_rec => g_rec);
3377 --
3378 -- For BENAUTHE retreival purpose.
3379 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
3380 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_ptnl_rec.lf_evt_ocrd_dt);
3381 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
3382 l_current_per_in_ler.lf_evt_ocrd_dt);
3383 raise ben_manage_life_events.g_life_event_after;
3384 --
3385 end if;
3386 --
3387
3388 --Start 6086392
3389
3390 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.first;
3391
3392
3393 if(l_bckdt_pil_indx is not null) then
3394
3395 loop
3396
3397 OPEN c_pil_ovn(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx));
3398 FETCH c_pil_ovn INTO pil_ovn_rec;
3399 CLOSE c_pil_ovn;
3400
3401 ben_Person_Life_Event_api.update_person_life_event
3402 (p_per_in_ler_id => ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx)
3403 ,p_bckt_per_in_ler_id => l_curr_per_in_ler_id
3404 ,p_object_version_number => pil_ovn_rec.object_version_number
3405 ,p_effective_date => p_effective_date
3406 ,P_PROCD_DT => l_date -- outputs
3407 ,P_STRTD_DT => l_date
3408 ,P_VOIDD_DT => l_date );
3409
3410 exit when l_bckdt_pil_indx = ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.last;
3411
3412 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.next(l_bckdt_pil_indx);
3413 end loop;
3414
3415 end if;
3416
3417 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
3418
3419
3420 --End 6086392
3421
3422 hr_utility.set_location('Leaving:'|| l_proc, 90);
3423 --
3424 end eval_ptnl_per_for_ler;
3425 --
3426 -- iRec
3427 procedure irec_eval_ptnl_per_for_ler(p_validate in boolean default false
3428 ,p_person_id in number
3429 ,p_business_group_id in number
3430 ,p_ler_id in number default null
3431 ,p_mode in varchar2
3432 ,p_effective_date in date
3433 ,p_lf_evt_ocrd_dt in date
3434 ,p_assignment_id in number
3435 ,p_ptnl_ler_for_per_id in number
3436 ,p_created_ler_id out NOCOPY number) is
3437 --
3438 cursor c_ptnl(cv_ptnl_ler_for_per_id in number)
3439 is
3440 select ptnl.*
3441 from ben_ptnl_ler_for_per ptnl
3442 where ptnl.ptnl_ler_for_per_id = cv_ptnl_ler_for_per_id;
3443 --
3444 cursor get_per_in_ler(cv_assignment_id number,
3445 cv_person_id number,
3446 cv_ler_id number,
3447 cv_effective_date date)
3448 is
3449 select pil.per_in_ler_id,
3450 pil.per_in_ler_stat_cd,
3451 pil.lf_evt_ocrd_dt,
3452 pil.ler_id,
3453 pil.person_id,
3454 pil.business_group_id,
3455 pil.object_version_number,
3456 pil.procd_dt,
3457 pil.strtd_dt,
3458 pil.voidd_dt,
3459 pil.bckt_dt,
3460 pil.clsd_dt,
3461 pil.ntfn_dt,
3462 pil.ptnl_ler_for_per_id
3463 from ben_per_in_ler pil,
3464 ben_ler_f ler
3465 where pil.person_id = cv_person_id
3466 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
3467 and pil.assignment_id = cv_assignment_id
3468 and pil.ler_id = ler.ler_id
3469 and pil.ler_id = cv_ler_id
3470 and cv_effective_date
3471 between ler.effective_start_date
3472 and ler.effective_end_date
3473 and ler.typ_cd = 'IREC';
3474 --
3475 cursor c_ben_pil_elctbl_chc_popl (p_per_in_ler_id number) is
3476 select pel.pil_elctbl_chc_popl_id,
3477 pel.object_version_number
3478 from ben_pil_elctbl_chc_popl pel,
3479 ben_per_in_ler pil
3480 where pil.per_in_ler_id = p_per_in_ler_id
3481 and pil.business_group_id = p_business_group_id
3482 and pel.per_in_ler_id = pil.per_in_ler_id
3483 and pel.business_group_id = pil.business_group_id;
3484 --
3485 l_pil_rec get_per_in_ler%rowtype;
3486 l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
3487 l_procd_dt date;
3488 l_strtd_dt date;
3489 l_voidd_dt date;
3490 l_ntfn_dt date;
3491 l_dtctd_dt date;
3492 l_unprocd_dt date;
3493 l_object_version_number NUMBER;
3494 l_pil_object_version_number NUMBER;
3495 l_curr_per_in_ler_id number;
3496 l_created_ler varchar2(2) := 'N';
3497 l_create_pil varchar2(2) := 'N';
3498 --irec2
3499 l_pel_object_version_number number;
3500 l_pel_pk_id number;
3501 l_pil_assignment_id number;
3502 --
3503 begin
3504
3505 -- Step 1.
3506 -- Check whether per in ler is in processed status for the associated
3507 -- assignment_id. If yes then raise a error as the offer is already
3508 -- processed, enrollments may have been already completed and HR
3509 -- data may have been committed.
3510 --
3511 -- Step 2.
3512 --
3513 -- If per in ler is started status then back out the event.
3514 --
3515 -- Step 3.
3516 --
3517 -- Create the pil in started status.
3518 --
3519 open get_per_in_ler(p_assignment_id,
3520 p_person_id,
3521 p_ler_id,
3522 p_effective_date);
3523 fetch get_per_in_ler into l_pil_rec;
3524 close get_per_in_ler;
3525 --
3526 if l_pil_rec.per_in_ler_id is not null and
3527 l_pil_rec.per_in_ler_stat_cd = 'PROCD'
3528 then
3529 --
3530 -- Offer is already processed, so you can't initiate the offer again.
3531 -- Error out.
3532 --
3533 -- hr_utility.set_location('** ERROR SUP',9909);
3534 fnd_message.set_name('BEN','BEN_94025_IREC_OFFER_PROCESSED');
3535 benutils.write(p_text => fnd_message.get);
3536 --
3537 g_rec.person_id := p_person_id;
3538 g_rec.ler_id := l_pil_rec.ler_id;
3539 g_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
3540 g_rec.replcd_flag := 'N';
3541 g_rec.crtd_flag := 'N';
3542 g_rec.tmprl_flag := 'N';
3543 g_rec.dltd_flag := 'N';
3544 g_rec.open_and_clsd_flag := 'N';
3545 g_rec.not_crtd_flag := 'N';
3546 g_rec.clsd_flag := 'N';
3547 g_rec.stl_actv_flag := 'Y';
3548 g_rec.clpsd_flag := 'N';
3549 g_rec.clsn_flag := 'N';
3550 g_rec.no_effect_flag := 'N';
3551 g_rec.cvrge_rt_prem_flag := 'N';
3552 g_rec.business_group_id := p_business_group_id;
3553 g_rec.effective_date := p_effective_date;
3554 --
3555 benutils.write(p_rec => g_rec);
3556 --
3557 -- For BENAUTHE retreival purpose.
3558 --
3559 fnd_message.set_name('BEN','BEN_94025_IREC_OFFER_PROCESSED');
3560 benutils.write(p_text => fnd_message.get);
3561 raise ben_manage_life_events.g_life_event_after;
3562 --
3563 elsif l_pil_rec.per_in_ler_id is not null and
3564 l_pil_rec.per_in_ler_stat_cd = 'STRTD' then
3565 --
3566 -- Backout the life event.
3567 -- Update the potential with the new life event occured date.
3568 --
3569 -- Start irec2 : dont call back_out_life_events
3570 -- instead update PIL.PER_IN_LER_STAT_CD to VOID ,
3571 -- PEL.PIL_ELCTBL_POPL_STAT_CD to BCKDT
3572
3573 /* ben_back_out_life_event.back_out_life_events
3574 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
3575 p_bckt_per_in_ler_id => null,
3576 p_bckt_stat_cd => 'UNPROCD',
3577 p_business_group_id => p_business_group_id,
3578 p_effective_date => p_effective_date); */
3579
3580 -- update PIL
3581 -- 5068367 as per requirement, we would Backout instead of VOID
3582 ben_Person_Life_Event_api.update_person_life_event
3583 (p_per_in_ler_id => l_pil_rec.per_in_ler_id
3584 ,p_bckt_per_in_ler_id => null
3585 ,p_per_in_ler_stat_cd => 'BCKDT'
3586 ,p_prvs_stat_cd => l_pil_rec.per_in_ler_stat_cd
3587 ,p_object_version_number => l_pil_rec.object_version_number
3588 ,p_effective_date => p_effective_date
3589 ,P_PROCD_DT => l_procd_dt -- outputs
3590 ,P_STRTD_DT => l_strtd_dt
3591 ,P_VOIDD_DT => l_voidd_dt );
3592
3593 -- update PEL
3594 open c_ben_pil_elctbl_chc_popl(l_pil_rec.per_in_ler_id) ;
3595
3596 loop
3597
3598 fetch c_ben_pil_elctbl_chc_popl into l_pel_pk_id,
3599 l_pel_object_version_number;
3600 exit when c_ben_pil_elctbl_chc_popl%notfound;
3601 --
3602 --
3603 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
3604 (p_validate => false,
3605 p_pil_elctbl_chc_popl_id => l_pel_pk_id,
3606 p_pil_elctbl_popl_stat_cd => 'BCKDT',
3607 p_object_version_number => l_pel_object_version_number,
3608 p_effective_date => p_effective_date);
3609
3610 end loop;
3611
3612 close c_ben_pil_elctbl_chc_popl;
3613
3614 -- update PPL
3615 --
3616 -- If lf event occured date is different then update the life event
3617 -- occured date.
3618 --
3619 open c_ptnl(l_pil_rec.ptnl_ler_for_per_id);
3620 fetch c_ptnl into l_ptnl_rec;
3621 close c_ptnl;
3622 --
3623 -- if l_ptnl_rec.lf_evt_ocrd_dt <> p_lf_evt_ocrd_dt then
3624 --
3625 -- update the potential record.
3626 --
3627 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
3628 (p_validate => false
3629 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3630 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
3631 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
3632 ,p_procd_dt => p_lf_evt_ocrd_dt
3633 ,p_person_id => l_ptnl_rec.person_id
3634 ,p_business_group_id => l_ptnl_rec.business_group_id
3635 ,p_object_version_number => l_ptnl_rec.object_version_number
3636 ,p_effective_date => p_lf_evt_ocrd_dt
3637 ,p_program_application_id => fnd_global.prog_appl_id
3638 ,p_program_id => fnd_global.conc_program_id
3639 ,p_request_id => fnd_global.conc_request_id
3640 ,p_program_update_date => sysdate);
3641 --
3642 -- Now create the per in ler.
3643 --
3644 l_create_pil := 'Y';
3645 -- end if;
3646 --
3647 else
3648 --
3649 -- Create potential.
3650 --
3651 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
3652 (p_validate => false,
3653 p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id,
3654 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3655 p_ptnl_ler_for_per_stat_cd => 'PROCD',
3656 p_ler_id => p_ler_id,
3657 p_person_id => p_person_id,
3658 p_ntfn_dt => sysdate, -- l_ntfn_dt
3659 p_unprocd_dt => p_lf_evt_ocrd_dt, -- l_unprocd_dt
3660 p_procd_dt => p_lf_evt_ocrd_dt,
3661 p_dtctd_dt => l_dtctd_dt,
3662 p_business_group_id => p_business_group_id,
3663 p_object_version_number => l_object_version_number,
3664 p_effective_date => p_lf_evt_ocrd_dt,
3665 p_program_application_id => fnd_global.prog_appl_id,
3666 p_program_id => fnd_global.conc_program_id,
3667 p_request_id => fnd_global.conc_request_id,
3668 p_program_update_date => sysdate);
3669 --
3670 l_create_pil := 'Y';
3671 --
3672 end if;
3673 --
3674 if l_create_pil = 'Y' then
3675 --
3676 ben_Person_Life_Event_api.create_Person_Life_Event_perf
3677 (p_validate => false
3678 ,p_per_in_ler_id => l_curr_per_in_ler_id
3679 ,p_ler_id => p_ler_id
3680 ,p_person_id => p_person_id
3681 ,p_per_in_ler_stat_cd => 'STRTD'
3682 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3683 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
3684 ,p_business_group_id => p_business_group_id
3685 ,p_ntfn_dt => trunc(sysdate) -- p_ptnl_rec.ntfn_dt
3686 ,p_assignment_id => p_assignment_id
3687 ,p_object_version_number => l_pil_object_version_number
3688 ,p_effective_date => p_lf_evt_ocrd_dt
3689 ,p_program_application_id => fnd_global.prog_appl_id
3690 ,p_program_id => fnd_global.conc_program_id
3691 ,p_request_id => fnd_global.conc_request_id
3692 ,p_program_update_date => sysdate
3693 ,p_procd_dt => l_procd_dt
3694 ,p_strtd_dt => l_strtd_dt
3695 ,p_voidd_dt => l_voidd_dt);
3696 --
3697 end if;
3698 --
3699 p_created_ler_id := p_ler_id;
3700 --
3701 -- irec2 call create_ben_pil_assignment_api
3702 ben_pil_assignment_api.create_pil_assignment
3703 (p_validate => false
3704 ,p_pil_assignment_id => l_pil_assignment_id
3705 ,p_per_in_ler_id => l_curr_per_in_ler_id
3706 ,p_applicant_assignment_id => p_assignment_id
3707 ,p_offer_assignment_id => null
3708 ,p_object_version_number => l_object_version_number
3709 ) ;
3710
3711 end irec_eval_ptnl_per_for_ler;
3712 -- end iRec
3713
3714 end ben_evaluate_ptnl_lf_evt;