[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.25.12020000.3 2012/10/17 13:52:04 amnaraya 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 l_env ben_env_object.g_global_env_rec_type;
2261
2262 --Start Bug 14137075
2263 --This proc is copied from get_earliest_potential
2264 procedure check_earliest_potential(p_person_id in number,
2265 p_business_group_id in number,
2266 p_mode in varchar2,
2267 p_effective_date in date,
2268 p_current_ler_id in number,
2269 p_new_ler_id in number,
2270 p_lf_evt_ocrd_dt in date
2271 ) is
2272 --
2273 l_proc varchar2(72) := g_package||'check_earliest_potential';
2274 --
2275 --
2276 -- CWB Changes : Cursor joined to ben_ler_f
2277 --
2278 cursor c1 is
2279 select min(ptn.lf_evt_ocrd_dt)
2280 from ben_ptnl_ler_for_per ptn,
2281 ben_ler_f ler
2282 where ptn.person_id = p_person_id
2283 and ptn.ler_id = ler.ler_id
2284 and p_effective_date
2285 between ler.effective_start_date
2286 and ler.effective_end_date
2287 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2288 and ptn.business_group_id = p_business_group_id
2289 and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
2290 'SCHEDDO',
2291 ptn.lf_evt_ocrd_dt,
2292 'SCHEDDA',
2293 ptn.lf_evt_ocrd_dt,
2294 p_effective_date)
2295 and ptn.ler_id <> ben_manage_life_events.g_ler_id
2296 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
2297
2298 cursor c_ler(p_ler_id number) is
2299 SELECT name
2300 FROM ben_ler_f
2301 WHERE business_group_id = p_business_group_id
2302 AND ler_id = p_ler_id
2303 AND p_effective_date
2304 BETWEEN effective_start_date
2305 AND effective_end_date;
2306 --
2307 -- CWB Changes End
2308 --
2309 l_min_lf_evt_ocrd_dt date;
2310 l_new_ler varchar2(240);
2311 l_current_ler varchar2(240);
2312 --
2313 begin
2314 --
2315 hr_utility.set_location('Entering:'|| l_proc, 116);
2316 --
2317 open c1;
2318 --
2319 fetch c1 into l_min_lf_evt_ocrd_dt;
2320 hr_utility.set_location('l_min_lf_evt_ocrd_dt:'|| l_min_lf_evt_ocrd_dt, 10);
2321 --
2322 -- Remember the min function always returns a row so check if the result
2323 -- is null rather than c1%notfound.
2324 --
2325 if l_min_lf_evt_ocrd_dt is null then
2326 --
2327 close c1;
2328 open c_ler(p_current_ler_id);
2329 fetch c_ler into l_current_ler;
2330 close c_ler;
2331 open c_ler(p_new_ler_id);
2332 fetch c_ler into l_new_ler;
2333 close c_ler;
2334
2335 --fnd_message.set_name('BEN','BEN_94750_NO_LIFE_EVENTS');--AMN Bug 14680386
2336 fnd_message.set_name('BEN','BEN_94752_NO_LIFE_EVENTS');--AMN Bug 14680386
2337 fnd_message.set_token('CURRENT_LE',l_current_ler);
2338 fnd_message.set_token('NEW_LE',l_new_ler);
2339 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2340 --fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);AMN Bug 14680386
2341 benutils.write(p_text => fnd_message.get);
2342 -- For BENAUTHE retreival purpose.
2343 fnd_message.set_name('BEN','BEN_94750_NO_LIFE_EVENTS');
2344 fnd_message.set_token('CURRENT_LE',l_current_ler);
2345 fnd_message.set_token('NEW_LE',l_new_ler);
2346 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2347 fnd_message.set_token('EFFECTIVE_DATE',p_effective_date);
2348 raise ben_manage_life_events.g_life_event_after;
2349 --
2350 end if;
2351 --
2352 close c1;
2353 --
2354 hr_utility.set_location('Leaving:'|| l_proc, 10);
2355 --
2356 end check_earliest_potential;
2357 --End Bug 14137075
2358 --
2359 begin
2360 --
2361 hr_utility.set_location('Entering:'|| l_proc, 10);
2362 --
2363 -- If no rule then return a Y else lets check the rule
2364 --
2365 if p_rule_id is null then
2366 --
2367 return 'Y';
2368 --
2369 else
2370 --
2371 /*Bug 9466400:Get the existing value from the cache. Set p_lf_evt_ocrd_dt in the cache
2372 to life event occured date*/
2373 ben_env_object.get(p_rec => l_env);
2374 ben_env_object.setenv(p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
2375 /*End of Bug 9466400*/
2376
2377 ben_person_object.get_object(p_person_id => p_person_id,
2378 p_rec => l_ass_rec);
2379 --
2380 if l_ass_rec.assignment_id is null then
2381 --
2382 ben_person_object.get_benass_object(p_person_id => p_person_id,
2383 p_rec => l_ass_rec);
2384 --
2385 end if;
2386 --
2387 if l_ass_rec.location_id is not null then
2388 --
2389 ben_location_object.get_object(p_location_id => l_ass_rec.location_id,
2390 p_rec => l_loc_rec);
2391 --
2392 -- if l_loc_rec.region_2 is not null then
2393 --
2394 -- l_jurisdiction_code :=
2395 -- pay_mag_utils.lookup_jurisdiction_code
2396 -- (p_state => l_loc_rec.region_2);
2397 --
2398 -- end if;
2399 --
2400 end if;
2401 --
2402 /*Bug 9466400:Reset the cache value to the old value*/
2403 ben_env_object.setenv(p_lf_evt_ocrd_dt => l_env.lf_evt_ocrd_dt);
2404
2405 l_outputs := benutils.formula
2406 (p_formula_id => p_rule_id,
2407 p_effective_date => p_effective_date,
2408 p_ler_id => p_ler_id,
2409 p_assignment_id => l_ass_rec.assignment_id,
2410 p_organization_id => l_ass_rec.organization_id,
2411 p_business_group_id=> p_business_group_id,
2412 --
2413 -- Bug 1844764
2414 -- Pass the primary key and lf event occured dt to access
2415 -- potential data.
2416 --
2417 p_param1 => 'BEN_PPL_IV_PTNL_LER_FOR_PER_ID',
2418 p_param1_value => to_char(p_ptnl_ler_for_per_id),
2419 p_param2 => 'BEN_PPL_IV_LF_EVT_OCRD_DT',
2420 p_param2_value => to_char(p_lf_evt_ocrd_dt, 'YYYY/MM/DD HH24:MI:SS'),
2421 p_param3 => 'BEN_PPL_IV_PTNL_LER_FOR_PER_STAT_CD',
2422 p_param3_value => p_ptnl_ler_for_per_stat_cd,
2423 p_param4 => 'BEN_PPL_IV_NTFN_DT',
2424 p_param4_value => to_char(p_ntfn_dt, 'YYYY/MM/DD HH24:MI:SS'),
2425 p_param5 => 'BEN_PPL_IV_DTCTD_DT',
2426 p_param5_value => to_char(p_dtctd_dt, 'YYYY/MM/DD HH24:MI:SS'),
2427 p_jurisdiction_code=> l_jurisdiction_code);
2428 --
2429 -- Loop through the returned table and make sure that the returned
2430 -- values have been found
2431 --
2432 for l_count in l_outputs.first..l_outputs.last loop
2433 --
2434 begin
2435 --
2436 if l_outputs(l_count).name = 'LIFE_EVENT_OCCURRED_DATE' then
2437 --
2438 l_lf_evt_ocrd_dt := fnd_date.canonical_to_date
2439 (l_outputs(l_count).value);
2440 --
2441 elsif l_outputs(l_count).name = 'LIFE_EVENT_HAPPENED' then
2442 --
2443 l_happened := l_outputs(l_count).value;
2444 --
2445 elsif l_outputs(l_count).name = 'LIFE_EVENT_REASON_ID' then
2446 --
2447 l_ler_id := l_outputs(l_count).value;
2448 --
2449 elsif l_outputs(l_count).name = 'LIFE_EVENT_NOTIFICATION_DATE' then
2450 --
2451 l_ntfn_dt := fnd_date.canonical_to_date
2452 (l_outputs(l_count).value);
2453 --
2454 elsif l_outputs(l_count).name = 'LIFE_EVENT_DETECTED_DATE' then
2455 --
2456 l_dtctd_dt := fnd_date.canonical_to_date
2457 (l_outputs(l_count).value);
2458 --
2459 elsif l_outputs(l_count).name = 'LIFE_EVENT_VOIDED_DATE' then
2460 --
2461 l_voidd_dt := fnd_date.canonical_to_date
2462 (l_outputs(l_count).value);
2463 elsif l_outputs(l_count).name = 'LIFE_EVENT_MANUAL_DATE' then
2464 --
2465 l_mnl_dt := fnd_date.canonical_to_date
2466 (l_outputs(l_count).value);
2467 --
2468 elsif l_outputs(l_count).name = 'LIFE_EVENT_STATUS_CODE' then
2469 --
2470 l_ptnl_ler_for_per_stat_cd := l_outputs(l_count).value;
2471 --
2472 else
2473 --
2474 -- Account for cases where formula returns an unknown
2475 -- variable name
2476 --
2477 fnd_message.set_name('BEN','BEN_92310_FORMULA_RET_PARAM');
2478 fnd_message.set_token('PROC',l_proc);
2479 fnd_message.set_token('FORMULA',p_rule_id);
2480 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
2481 fnd_message.raise_error;
2482 --
2483 end if;
2484 if (l_mnl_dt is not null or
2485 (l_ptnl_ler_for_per_stat_cd is not null and l_ptnl_ler_for_per_stat_cd = 'MNL')) then
2486 l_mnl_savepoint_preestablish := true;
2487 -- savepoint ptnl_set_to_manual_savepoint;
2488 end if;
2489
2490 --
2491 -- Code for type casting errors from formula return variables
2492 --
2493 exception
2494 --
2495 when others then
2496 --
2497 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
2498 fnd_message.set_token('PROC',l_proc);
2499 fnd_message.set_token('FORMULA',p_rule_id);
2500 fnd_message.set_token('PARAMETER',l_outputs(l_count).name);
2501 fnd_message.raise_error;
2502 --
2503 end;
2504 --
2505 end loop;
2506 --
2507 -- hr_utility.set_location('life_event_happened '||l_happened , 15);
2508 -- hr_utility.set_location('life_event_status_code '||l_ptnl_ler_for_per_stat_cd, 15);
2509 --
2510 if l_happened not in ('Y','N') then
2511 --
2512 fnd_message.set_name('BEN','BEN_92143_LIFE_EVENT_HAPPENED');
2513 fnd_message.set_token('PROC',l_proc);
2514 fnd_message.raise_error;
2515 --
2516 end if;
2517 --
2518 if l_happened = 'Y' then
2519 --
2520 -- hr_utility.set_location('Step 1 ' ,190);
2521 if p_ler_id <> l_ler_id or
2522 p_lf_evt_ocrd_dt <> l_lf_evt_ocrd_dt or
2523 nvl(p_ntfn_dt,hr_api.g_date) <> nvl(l_ntfn_dt,hr_api.g_date) or
2524 nvl(p_dtctd_dt,hr_api.g_date) <> nvl(l_dtctd_dt,hr_api.g_date) or
2525 nvl(p_voidd_dt,hr_api.g_date) <> nvl(l_voidd_dt,hr_api.g_date) or
2526 p_ptnl_ler_for_per_stat_cd <> l_ptnl_ler_for_per_stat_cd then
2527 --
2528 -- Sanity check, they may have updated the voidd_dt but forgot to
2529 -- set the ptnl_ler_for_per_stat_cd so we set it for them
2530 --
2531 -- hr_utility.set_location('Step 2 ' ,191);
2532 if l_voidd_dt is not null and
2533 l_ptnl_ler_for_per_stat_cd <> 'VOIDD' then
2534 --
2535 l_ptnl_ler_for_per_stat_cd := 'VOIDD';
2536 --
2537 -- In this case we have to force the rule to think that the rule
2538 -- did not happen as we want the event to be voided but we also
2539 -- want to update certain columns.
2540 --
2541 l_happened := 'N';
2542 --hr_utility.set_location(' In the didnot happened case ', 15);
2543 --
2544 end if;
2545 --
2546 -- We may have had a different life event reason returned in which
2547 -- case we need to void the old life event and create a new ptnl
2548 -- life event. In this case carry out the void with the old values
2549 -- and create the new with the new returned values from the
2550 -- formula. In this case the event happened.
2551 --
2552 -- hr_utility.set_location('Step 3 ' ,193);
2553 if l_ler_id <> p_ler_id then--l_ler_id <> p_ler_id
2554
2555 -- Test for valid l_ptnl_ler_for_per_stat_cd
2556 if l_ptnl_ler_for_per_stat_cd not in ('DTCTD','MNL','UNPROCD') then
2557 -- hr_utility.set_location('Invalid stat code returned from rule.',10);
2558 fnd_message.set_name('BEN','BEN_92162_INV_PPL_STCD_TRANS');
2559 fnd_message.raise_error;
2560 end if;
2561
2562 --
2563 -- First lets create the new ptnl ler for per
2564 --
2565 -- If the fast formula rule returned anything other than 'DTCTD',
2566 -- create the new potential life event with a status of 'UNPROCD'.
2567
2568 if l_ptnl_ler_for_per_stat_cd = 'DTCTD' then
2569 l_ptnl_ler_for_per_stat_cd_use := 'DTCTD';
2570 else
2571 l_ptnl_ler_for_per_stat_cd_use := 'UNPROCD';
2572 -- hr_utility.set_location('Step 4 ' ,194);
2573 end if;
2574 --hr_utility.set_location(' r_per_stat_cd '||l_ptnl_ler_for_per_stat_cd, 17);
2575 if l_ptnl_ler_for_per_stat_cd = 'MNL' then
2576 l_unprocd_dt := l_mnl_dt;
2577 else
2578 l_unprocd_dt := sysdate;
2579 end if;
2580
2581 --
2582 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
2583 (p_validate => false,
2584 p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
2585 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2586 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd_use,
2587 p_ler_id => l_ler_id,
2588 p_person_id => p_person_id,
2589 p_ntfn_dt => l_ntfn_dt,
2590 p_unprocd_dt => l_unprocd_dt,
2591 p_dtctd_dt => l_dtctd_dt,
2592 p_business_group_id => p_business_group_id,
2593 p_object_version_number => l_object_version_number,
2594 p_effective_date => p_effective_date,
2595 p_program_application_id => fnd_global.prog_appl_id,
2596 p_program_id => fnd_global.conc_program_id,
2597 p_request_id => fnd_global.conc_request_id,
2598 p_program_update_date => sysdate);
2599 --
2600 -- hr_utility.set_location('Step 5 ' ,195);
2601 if l_ptnl_ler_for_per_stat_cd = 'MNL' then
2602 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2603 (p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id,
2604 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2605 p_ler_id => l_ler_id,
2606 p_ntfn_dt => l_ntfn_dt,
2607 p_dtctd_dt => l_dtctd_dt,
2608 p_voidd_dt => l_voidd_dt,
2609 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
2610 p_object_version_number => l_object_version_number,
2611 p_effective_date => p_effective_date,
2612 p_mnl_dt => l_mnl_dt,
2613 p_procd_dt => l_procd_dt,
2614 p_unprocd_dt => l_unprocd_dt);
2615 end if;
2616 --hr_utility.set_location('Step 6 ' ,196);
2617 l_happened := 'Y';
2618 --
2619 -- Make sure that when we update the ptnl ler for per that the
2620 -- voided date and stat code are set correctly.
2621 --
2622 l_ptnl_ler_for_per_stat_cd := 'VOIDD';
2623 l_voidd_dt := nvl(l_voidd_dt,sysdate);
2624 l_unprocd_dt := sysdate; --Bug 14137075 l_unprocd_dt := NULL;
2625 l_dtctd_dt := NULL;
2626 l_mnl_dt := NULL;
2627 --
2628 end if;
2629 --Start Bug 14137075: Commented below lines fixed for bug 12596491
2630 /* Start bug 12596491 added below else condition and assigned the the
2631 --l_unprocd_dt to sysdate
2632 else
2633
2634 l_unprocd_dt := sysdate;
2635 --End bug 12596491
2636 */
2637 --End Bug 14137075:
2638 --
2639 -- The rule has reset some values on the ptnl per for ler.
2640 -- We need to update the ben_ptnl_ler_for_per record in order
2641 -- to reflect the evaluated rule.
2642 --
2643 l_object_version_number := p_object_version_number;
2644 --
2645 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2646 (p_ptnl_ler_for_per_id => p_ptnl_ler_for_per_id,
2647 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
2648 p_ler_id => p_ler_id,
2649 p_ntfn_dt => l_ntfn_dt,
2650 p_dtctd_dt => l_dtctd_dt,
2651 p_voidd_dt => l_voidd_dt,
2652 p_ptnl_ler_for_per_stat_cd => l_ptnl_ler_for_per_stat_cd,
2653 p_object_version_number => l_object_version_number,
2654 p_effective_date => p_effective_date,
2655 p_mnl_dt => l_mnl_dt,
2656 p_procd_dt => l_procd_dt,
2657 p_unprocd_dt => l_unprocd_dt);
2658 -- hr_utility.set_location('Step 7 ' ,197);
2659 --
2660 -- end if;--l_ler_id <> p_ler_id --bug 12596491
2661 --Start Start Bug 14137075
2662 if l_ler_id <> p_ler_id then
2663 check_earliest_potential
2664 (p_person_id => p_person_id,
2665 p_business_group_id => p_business_group_id,
2666 p_mode => p_mode,
2667 p_effective_date => p_effective_date,
2668 p_current_ler_id => p_ler_id,
2669 p_new_ler_id => l_ler_id,
2670 p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
2671 );
2672 end if;
2673 --End Start Bug 14137075
2674 end if;
2675 --
2676 --hr_utility.set_location('Step 8 ' ,198);
2677 end if;
2678 --
2679 -- hr_utility.set_location('l_happened before return '||l_happened , 18 );
2680 return l_happened;
2681 --
2682 end if;
2683 --
2684 hr_utility.set_location('Leaving:'|| l_proc, 10);
2685 --
2686 end rule_evaluates;
2687 --
2688 procedure check_and_get_winner
2689 (p_lf_evt_ocrd_dt in date,
2690 p_person_id in number,
2691 p_business_group_id in number,
2692 p_effective_date in date,
2693 p_ptnl_rec out NOCOPY ben_ptnl_ler_for_per%rowtype) is
2694 --
2695 l_proc varchar2(72) := g_package||'check_and_get_winner';
2696 l_num_recs number := 0;
2697 l_num_winners number := 0;
2698 --
2699 -- Bugs : 3179/3249 : Now consider processed potentials as well to
2700 -- look for the winner.
2701 --
2702 cursor c1 is
2703 select ler.ovridg_le_flag,
2704 ppl.ptnl_ler_for_per_id,
2705 ler.name
2706 from ben_ptnl_ler_for_per ppl,
2707 ben_ler_f ler
2708 where ppl.person_id = p_person_id
2709 and ppl.ler_id = ler.ler_id
2710 and ppl.business_group_id = p_business_group_id
2711 and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2712 and ler.business_group_id = ppl.business_group_id
2713 and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD')
2714 and ppl.ler_id <> ben_manage_life_events.g_ler_id
2715 --
2716 -- CWB Changes
2717 --
2718 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2719 and p_effective_date
2720 between ler.effective_start_date
2721 and ler.effective_end_date;
2722 --
2723 l_ovridg_le_flag varchar2(30);
2724 l_win_ler_name ben_ler_f.name%TYPE; -- UTF8 varchar2(150);
2725 l_ler_name ben_ler_f.name%TYPE; -- UTF8 varchar2(150);
2726 l_ptnl_ler_for_per_id number(15);
2727 l_win_ptnl_ler_for_per_id number(15);
2728 l_search_ptnl_ler_for_per_id number(15);
2729 l_c1 c1%rowtype;
2730 --
2731 -- The following line is deleted from the where clause to
2732 --
2733 -- ****and ppl.ptnl_ler_for_per_id <> l_search_ptnl_ler_for_per_id ****
2734 --
2735 -- as the check is made in the loop.
2736 --
2737 -- Bugs : 3179/3249 : Now consider processed potentials as well to
2738 -- back out them and set the potentials to unprocessed.
2739 -- Then loop around the potentials and void the ones which are not
2740 -- processed.
2741 --
2742 --
2743 -- CWB Changes : Cursor joined to ben_ler_f
2744 --
2745 cursor c_ptnl is
2746 select ppl.object_version_number,
2747 ppl.ptnl_ler_for_per_id
2748 from ben_ptnl_ler_for_per ppl,
2749 ben_ler_f ler
2750 where ppl.person_id = p_person_id
2751 and ppl.business_group_id = p_business_group_id
2752 and ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2753 and ppl.ler_id <> ben_manage_life_events.g_ler_id
2754 and ppl.ler_id = ler.ler_id
2755 and p_effective_date
2756 between ler.effective_start_date
2757 and ler.effective_end_date
2758 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
2759 and ppl.ptnl_ler_for_per_stat_cd not in ('VOIDD');
2760 --
2761 -- CWB Changes End
2762 --
2763 cursor c2 is
2764 select *
2765 from ben_ptnl_ler_for_per ptn
2766 where ptn.ptnl_ler_for_per_id = l_search_ptnl_ler_for_per_id;
2767 --
2768 -- Bug 1146792 (4285) : Modified check_and_get_winner -
2769 -- Back out the per in ler's before determining
2770 -- the winner.
2771 --
2772 -- CWB Changes : Cursor joined to ben_ler_f
2773 --
2774 cursor c_pils_to_backout is
2775 select pil.*
2776 from ben_per_in_ler pil,
2777 ben_ler_f ler
2778 where pil.business_group_id = p_business_group_id
2779 and pil.person_id = p_person_id
2780 and (pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt -- 5727737/5677090: Need to backout all Future LEs
2781 or (pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
2782 and pil.ler_id <> ben_manage_life_events.g_ler_id))
2783 and pil.ler_id = ler.ler_id
2784 and p_effective_date
2785 between ler.effective_start_date
2786 and ler.effective_end_date
2787 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC','SCHEDDU') --iRec
2788 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
2789 ORDER BY pil.lf_evt_ocrd_dt DESC;
2790 --
2791 -- CWB Changes End
2792 --
2793 l_per_in_ler_id number;
2794 --
2795 cursor c_ptnl_ovn(v_ptnl_ler_for_per_id number) is
2796 select ptnl.object_version_number
2797 from ben_ptnl_ler_for_per ptnl
2798 where ptnl.ptnl_ler_for_per_id = v_ptnl_ler_for_per_id
2799 and ptnl.business_group_id = p_business_group_id;
2800 --
2801 l_ptnl_ovn number;
2802 --
2803
2804 begin
2805 --
2806 hr_utility.set_location('Entering: '|| l_proc, 10);
2807 --
2808 -- First back out any ptnls.
2809 --
2810 --
2811 -- Bug 1146792 (4285) : Modified check_and_get_winner -
2812 -- Back out the per in ler's before determining
2813 -- the winner.
2814 --
2815 -- Any processed or started life events on the same day have
2816 -- to be backed out prior to processing the winner.
2817 --
2818 for l_pil_rec in c_pils_to_backout loop
2819 --
2820 ben_back_out_life_event.back_out_life_events
2821 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
2822 p_bckt_per_in_ler_id => null,
2823 p_bckt_stat_cd => 'UNPROCD',
2824 p_business_group_id => p_business_group_id,
2825 p_effective_date => p_effective_date);
2826 --
2827
2828 --Start 6086392
2829 l_bckdt_pil_count := nvl(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.count(),0);
2830 l_bckdt_pil_count := l_bckdt_pil_count +1;
2831 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_count) := l_pil_rec.per_in_ler_id;
2832 --End 6086392
2833
2834 end loop;
2835 --
2836 -- Lets loop through all the jobs that occured on the p_lf_evt_ocrd_dt and
2837 -- count the winners and the number of rec with the same date
2838 --
2839 open c1;
2840 --
2841 loop
2842 --
2843 fetch c1 into l_ovridg_le_flag,
2844 l_ptnl_ler_for_per_id,
2845 l_ler_name;
2846 exit when c1%notfound;
2847 --
2848 l_num_recs := l_num_recs + 1; -- '+1' added during irec
2849 --
2850 if l_ovridg_le_flag = 'Y' then
2851 --
2852 l_num_winners := l_num_winners + 1;-- '+' added during irec
2853 l_win_ptnl_ler_for_per_id := l_ptnl_ler_for_per_id;
2854 l_win_ler_name := l_win_ler_name;
2855 --
2856 end if;
2857 --
2858 end loop;
2859 --
2860 close c1;
2861 --
2862 -- Now lets check if we break any rules
2863 --
2864 if l_num_winners > 1 then
2865 --
2866 -- More than one winner so return an error
2867 --
2868 fnd_message.set_name('BEN','BEN_91794_DUP_WINNERS');
2869 ben_manage_life_events.g_rec.rep_typ_cd := 'ERROR';
2870 ben_manage_life_events.g_rec.person_id := p_person_id;
2871 ben_manage_life_events.g_rec.ler_id := null;
2872 ben_manage_life_events.g_rec.error_message_code := 'BEN_91794_DUP_WINNERS';
2873 ben_manage_life_events.g_rec.text := fnd_message.get;
2874 fnd_message.set_name('BEN','BEN_91794_DUP_WINNERS');
2875 fnd_message.set_token('PROC',l_proc);
2876 fnd_message.set_token('PERSON_ID',p_person_id);
2877 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2878 raise ben_manage_life_events.g_record_error;
2879 --
2880 -- No winners, but more than one potential?
2881 --
2882 elsif l_num_winners = 0 and
2883 l_num_recs > 1 then
2884 --
2885 -- Potential life events clash, so error.
2886 --
2887 ben_manage_life_events.g_rec.rep_typ_cd := 'ERROR';
2888 ben_manage_life_events.g_rec.person_id := p_person_id;
2889 ben_manage_life_events.g_rec.ler_id := null;
2890 ben_manage_life_events.g_rec.error_message_code := 'BEN_92337_DUPLICATE_PTNL_LE';
2891 ben_manage_life_events.g_rec.text := fnd_message.get;
2892 fnd_message.set_name('BEN','BEN_92337_DUPLICATE_PTNL_LE');
2893 fnd_message.set_token('PROC',l_proc);
2894 fnd_message.set_token('PERSON_ID',p_person_id);
2895 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2896 raise ben_manage_life_events.g_record_error;
2897 --
2898 elsif l_num_winners = 1 then
2899 --
2900 l_search_ptnl_ler_for_per_id := l_win_ptnl_ler_for_per_id;
2901 --
2902 -- Wining potential found but there are multiple potentials, so
2903 -- void the other potentials
2904 --
2905 if l_num_recs > 1 then
2906 --
2907 for l_ptnl in c_ptnl loop
2908 --
2909 if l_ptnl.ptnl_ler_for_per_id <> l_search_ptnl_ler_for_per_id then
2910 --
2911 -- Get the object_version_number as the life event might have
2912 -- been bolfied resulting in new object version number.
2913 --
2914 /*
2915 open c_ptnl_ovn(l_ptnl.ptnl_ler_for_per_id);
2916 fetch c_ptnl_ovn into l_ptnl_ovn;
2917 close c_ptnl_ovn;
2918 */
2919 --
2920 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
2921 (p_validate => false
2922 ,p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id
2923 ,p_ptnl_ler_for_per_stat_cd => 'VOIDD'
2924 ,p_object_version_number => l_ptnl.object_version_number
2925 ,p_effective_date => p_effective_date
2926 ,p_program_application_id => fnd_global.prog_appl_id
2927 ,p_program_id => fnd_global.conc_program_id
2928 ,p_request_id => fnd_global.conc_request_id
2929 ,p_program_update_date => sysdate
2930 ,p_voidd_dt => p_effective_date);
2931 --
2932 end if;
2933 --
2934 end loop;
2935 --
2936 end if;
2937 --
2938 elsif l_num_recs = 1 then
2939 --
2940 l_search_ptnl_ler_for_per_id := l_ptnl_ler_for_per_id;
2941 l_win_ler_name := l_ler_name;
2942 --
2943 elsif l_num_recs = 0 then
2944 --
2945 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
2946 fnd_message.set_token('PROC',l_proc);
2947 fnd_message.set_token('PERSON_ID',p_person_id);
2948 fnd_message.set_token('LF_EVT_OCRD_DT',p_lf_evt_ocrd_dt);
2949 raise ben_manage_life_events.g_record_error;
2950 --
2951 end if;
2952 --
2953 open c2;
2954 --
2955 fetch c2 into p_ptnl_rec;
2956 --
2957 if p_ptnl_rec.ptnl_ler_for_per_stat_cd = 'MNL' then
2958 --
2959 close c2;
2960 --
2961 -- 6245213 : Rollback all backouts, since the LE is going to MANUAL.
2962 --
2963 if (l_mnl_savepoint_established) then
2964 hr_utility.set_location ('Going to Manual. Rollback all Backouts' ,100);
2965 rollback to ptnl_set_to_manual_savepoint;
2966 l_mnl_savepoint_established := false;
2967 l_mnl_savepoint_preestablish:=false;
2968 end if;
2969 ---
2970 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2971 fnd_message.set_token('LE_NAME',l_win_ler_name);
2972 fnd_message.set_token('PROC',l_proc);
2973 benutils.write(p_text => fnd_message.get);
2974 -- For BENAUTHE retreival purpose.
2975 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
2976 fnd_message.set_token('LE_NAME',l_win_ler_name);
2977 fnd_message.set_token('PROC',l_proc);
2978 raise ben_manage_life_events.g_life_event_after;
2979 --
2980 end if;
2981 --
2982 close c2;
2983 --
2984
2985 hr_utility.set_location('Lea l_win_ler_name '||l_win_ler_name , 9.9);
2986 hr_utility.set_location('Leaving:'|| l_proc, 10);
2987 --
2988 end check_and_get_winner;
2989 --
2990 function get_earliest_potential(p_person_id in number,
2991 p_business_group_id in number,
2992 p_mode in varchar2,
2993 p_effective_date in date) return date is
2994 --
2995 l_proc varchar2(72) := g_package||'get_earliest_potential';
2996 --
2997 --
2998 -- CWB Changes : Cursor joined to ben_ler_f
2999 --
3000 cursor c1 is
3001 select min(ptn.lf_evt_ocrd_dt)
3002 from ben_ptnl_ler_for_per ptn,
3003 ben_ler_f ler
3004 where ptn.person_id = p_person_id
3005 and ptn.ler_id = ler.ler_id
3006 and p_effective_date
3007 between ler.effective_start_date
3008 and ler.effective_end_date
3009 and ler.typ_cd not in ( 'COMP', 'ABS', 'GSP', 'IREC') --iRec
3010 and ptn.business_group_id = p_business_group_id
3011 and ptn.lf_evt_ocrd_dt <= decode(ler.typ_cd, --Bug 5703825
3012 'SCHEDDO',
3013 ptn.lf_evt_ocrd_dt,
3014 'SCHEDDA',
3015 ptn.lf_evt_ocrd_dt,
3016 p_effective_date)
3017 and ptn.ler_id <> ben_manage_life_events.g_ler_id
3018 and ptn.ptnl_ler_for_per_stat_cd not in ('PROCD','VOIDD');
3019 --
3020 -- CWB Changes End
3021 --
3022 l_min_lf_evt_ocrd_dt date;
3023 --
3024 begin
3025 --
3026 hr_utility.set_location('Entering:'|| l_proc, 10);
3027 --
3028 open c1;
3029 --
3030 fetch c1 into l_min_lf_evt_ocrd_dt;
3031 --
3032 -- Remember the min function always returns a row so check if the result
3033 -- is null rather than c1%notfound.
3034 --
3035 if l_min_lf_evt_ocrd_dt is null then
3036 --
3037 close c1;
3038 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
3039 fnd_message.set_token('PROC',l_proc);
3040 fnd_message.set_token('PERSON_ID',p_person_id);
3041 fnd_message.set_token('LF_EVT_OCRD_DT',l_min_lf_evt_ocrd_dt);
3042 benutils.write(p_text => fnd_message.get);
3043 -- For BENAUTHE retreival purpose.
3044 fnd_message.set_name('BEN','BEN_92144_NO_LIFE_EVENTS');
3045 fnd_message.set_token('PROC',l_proc);
3046 fnd_message.set_token('PERSON_ID',p_person_id);
3047 fnd_message.set_token('LF_EVT_OCRD_DT',l_min_lf_evt_ocrd_dt);
3048 raise ben_manage_life_events.g_life_event_after;
3049 --
3050 end if;
3051 --
3052 close c1;
3053 --
3054 hr_utility.set_location('Leaving:'|| l_proc, 10);
3055 --
3056 return l_min_lf_evt_ocrd_dt;
3057 --
3058 end get_earliest_potential;
3059 --
3060 begin
3061 --
3062 hr_utility.set_location('Entering:'|| l_proc, 10);
3063
3064 --Start 6086392
3065 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
3066 --End 6086392
3067
3068 --
3069 -- Operation Steps
3070 -- ===============
3071 -- 1) Get potential life events and active life event
3072 -- 2) If a rule is attached evaluate rule
3073 -- 3) If rule evaluates to N then void life event
3074 -- 4) If rule evaluates to Y then fine. Apply life event occured date and
3075 -- new life event reason if required.
3076 -- 5) Keep account of minimum life event occurred date of valid life events
3077 -- 6) Check each potential life event for timeliness and void if neccessary
3078 -- 7) Perform collapsing logic
3079 -- 8) Check for winners
3080 --
3081 open get_all_potential;
3082 --
3083 loop
3084 --
3085 fetch get_all_potential into l_potent;
3086 exit when get_all_potential%notfound;
3087 --
3088 l_recs_found := true;
3089 --
3090 -- Bug 1177226 : Any potential with manual hit then just
3091 -- stop the process.
3092 --
3093 if l_potent.ptnl_ler_for_per_stat_cd = 'MNL' then
3094 --
3095 fnd_message.set_name('BEN','BEN_94209_MAN_LER_EXISTIS');
3096 -- fnd_message.set_token('LE_NAME',l_potent.name);
3097 -- fnd_message.set_token('PROC',l_proc);
3098 benutils.write(p_text => fnd_message.get);
3099 -- For BENAUTHE retreival purpose.
3100 fnd_message.set_name('BEN','BEN_94209_MAN_LER_EXISTIS');
3101 -- fnd_message.set_token('LE_NAME',l_potent.name);
3102 -- fnd_message.set_token('PROC',l_proc);
3103 raise ben_manage_life_events.g_life_event_after;
3104 --
3105 end if;
3106 --
3107 -- If there is a rule attached lets evaluate it and handle the returned
3108 -- values.
3109 --
3110 hr_utility.set_location(l_potent.ler_eval_rl,10);
3111 if rule_evaluates
3112 (p_ler_id => l_potent.ler_id,
3113 p_person_id => p_person_id,
3114 p_business_group_id => p_business_group_id,
3115 p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id,
3116 p_ptnl_ler_for_per_stat_cd => l_potent.ptnl_ler_for_per_stat_cd,
3117 p_ntfn_dt => l_potent.ntfn_dt,
3118 p_dtctd_dt => l_potent.dtctd_dt,
3119 p_voidd_dt => l_potent.voidd_dt,
3120 p_object_version_number => l_potent.object_version_number,
3121 p_lf_evt_ocrd_dt => l_potent.lf_evt_ocrd_dt,
3122 p_rule_id => l_potent.ler_eval_rl,
3123 p_effective_date => p_effective_date) <> 'Y' then
3124 --
3125 -- The life event didn't happen so void it
3126 --
3127 hr_utility.set_location('After call to rule_evaluates ',19);
3128 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
3129 (p_ptnl_ler_for_per_id => l_potent.ptnl_ler_for_per_id,
3130 p_object_version_number => l_potent.object_version_number,
3131 p_ptnl_ler_for_per_stat_cd => 'VOIDD',
3132 p_effective_date => p_effective_date,
3133 p_voidd_dt => p_effective_date);
3134 --
3135 end if;
3136 --
3137 end loop;
3138 --
3139 close get_all_potential;
3140 --
3141 -- Test for no potentials found error
3142 --
3143 if not l_recs_found then
3144 --
3145 -- Person already has a scheduled life event that has been run on this day
3146 -- We don't want to add to the error count so lets just process the next
3147 -- person. The life event could be strtd or procd we don't care.
3148 --
3149 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
3150 fnd_message.set_token('PERSON_ID',p_person_id);
3151 fnd_message.set_token('PROC',l_proc);
3152 benutils.write(fnd_message.get);
3153 --
3154 -- For BENAUTHE
3155 --
3156 fnd_message.set_name('BEN','BEN_92536_PERSON_HAS_NO_PPL');
3157 fnd_message.set_token('PERSON_ID',p_person_id);
3158 fnd_message.set_token('PROC',l_proc);
3159 raise ben_manage_life_events.g_life_event_after;
3160 --
3161 end if;
3162 --
3163 -- Now we have manipulated all potentials, check whether the remaining
3164 -- potentials fall within the timeliness
3165 --
3166 hr_utility.set_location('Before entering check_for_timeliness',90);
3167 --
3168 -- 6245213 : If Event goes to manual due to timeliness then
3169 -- set savepoint l_mnl_savepoint_established in the proc. check_for_timeliness
3170 -- and rollbck to savepoint in proc.check_and_get_winner
3171 --
3172 check_for_timeliness
3173 (p_person_id => p_person_id,
3174 p_effective_date => p_effective_date,
3175 p_mode => p_mode,
3176 p_business_group_id => p_business_group_id);
3177 --
3178 hr_utility.set_location('After leaving check_for_timeliness',90);
3179 --
3180 -- Check we actually have potential life events and get the minimum life
3181 -- event occurred of the set of potential life events
3182 --
3183 l_min_lf_evt_ocrd_dt := get_earliest_potential
3184 (p_person_id => p_person_id,
3185 p_business_group_id => p_business_group_id,
3186 p_mode => p_mode,
3187 p_effective_date => p_effective_date);
3188 --
3189 -- Do the collapse, Waaahoooo this is going to be good
3190 --
3191 ben_collapse_life_event.main
3192 (p_person_id => p_person_id,
3193 p_business_group_id => p_business_group_id,
3194 p_min_lf_evt_ocrd_dt => l_min_lf_evt_ocrd_dt,
3195 p_mode => p_mode,
3196 p_effective_date => p_effective_date);
3197 --
3198 -- We have to reget the min_lf_evt_ocrd_dt as a replace or void may have
3199 -- occurred and the minimum life event occurred date may be different to
3200 -- what it was prior to the calll.
3201 --
3202 l_min_lf_evt_ocrd_dt := get_earliest_potential
3203 (p_person_id => p_person_id,
3204 p_business_group_id => p_business_group_id,
3205 p_mode => p_mode,
3206 p_effective_date => p_effective_date);
3207 --
3208 -- Check for potentials that existed on the same day or multiple winners
3209 -- If there are multiple winners then error
3210 -- If there are multiple potentials but one winner then delete potentials
3211 -- If there are multiple potentials but no winners then error
3212 --
3213 check_and_get_winner(p_lf_evt_ocrd_dt => l_min_lf_evt_ocrd_dt,
3214 p_person_id => p_person_id,
3215 p_business_group_id => p_business_group_id,
3216 p_effective_date => p_effective_date,
3217 p_ptnl_rec => l_ptnl_rec);
3218 --
3219 --
3220 -- Bug 4872042
3221 --
3222 open c_winner_ler_typ_cd (cv_ler_id => l_ptnl_rec.ler_id);
3223 fetch c_winner_ler_typ_cd into l_typ_cd, l_ler_name;
3224 close c_winner_ler_typ_cd;
3225 --
3226 -- Here we need to check if winner life event type and BENMNGLE mode are different
3227 -- If different => then commit the COLLAPSING LOGIC and exit the process so that user has
3228 -- to explicitly process the winner life event in appropriate mode
3229 --
3230 -- Similar check also exists in benmngle.pkb
3231 --
3232 if /*(p_mode = 'C' and l_typ_cd not like 'SCHEDD%') or --commented against bug 6806014 */
3233 --Bug 4872042
3234 (p_mode = 'L' and l_typ_cd in ('SCHEDDU','UNRSTR')) -- remove 'SCHEDDO'
3235 then
3236 --
3237 benutils.write(p_text => 'Winner Life Event : ' || l_ler_name || benutils.id(l_ptnl_rec.ler_id) ||
3238 ', Supplied Mode : ' || p_mode);
3239 --
3240 if (l_typ_cd like 'SCHEDD%' or l_typ_cd = 'UNRSTR')
3241 then
3242 l_use_mode := 'Scheduled mode';
3243 else
3244 l_use_mode := 'Life event mode';
3245 end if;
3246 --
3247 if not p_validate
3248 then
3249 --
3250 commit;
3251 --
3252 savepoint process_life_event_savepoint;
3253 --
3254 end if;
3255 --
3256 fnd_message.set_name('BEN','BEN_92145_MODE_LE_DIFFER');
3257 fnd_message.set_token('MODE',l_use_mode);
3258 --
3259 raise ben_manage_life_events.g_record_error;
3260 --
3261 end if;
3262 --
3263 -- Bug 4872042
3264 --
3265 -- Lets do the backing out
3266 -- for the day of the minimum life event.
3267 --
3268 -- Get the current per in ler details so we decide whether to back out
3269 -- or whatever!
3270 --
3271 --
3272 -- Bug : 3078 (PBODLA)
3273 -- BENBOLFE currently only backs out closed person life events
3274 -- when there is a current started life event. BENBOLFE needs to
3275 -- backout all future life events.
3276 -- Due to above bug following two lines are commented and a local
3277 -- cursor is used to get pil's whose status is STRTD, PROCD
3278 --
3279 -- ben_person_object.get_object(p_person_id => p_person_id,
3280 -- p_rec => l_pil_rec);
3281 open get_current_per_in_ler(l_ptnl_rec.lf_evt_ocrd_dt);
3282 --
3283 fetch get_current_per_in_ler into l_current_per_in_ler;
3284 --
3285 close get_current_per_in_ler;
3286 --
3287 hr_utility.set_location('active LED '||l_current_per_in_ler.lf_evt_ocrd_dt,10);
3288 hr_utility.set_location('ptnl LED '||l_ptnl_rec.lf_evt_ocrd_dt,10);
3289 -- If no PER_IN_LER exists then we
3290 -- insert PTNL into PER_IN_LER
3291 -- update BEN_PTNL_LER_FOR_PER setting to processed
3292 --
3293 -- else
3294 -- We now we have the minimum PTNL PER_IN_LER
3295 -- and the date of the current PER in LER
3296 -- we should compare them and if the
3297 -- ptnl starts before the current
3298 -- we should delete the current and
3299 -- insert the ptnl into the PER_IN_LER table
3300 -- updating the current's old PTNL PER_FOR_LER
3301 -- record to be unprocessed.
3302 --
3303 --
3304 if l_current_per_in_ler.lf_evt_ocrd_dt is null then
3305 --
3306 hr_utility.set_location('A',10);
3307 -- insert ptnl
3308 --
3309 l_created_ler := 'Y';
3310 p_created_ler_id := l_ptnl_rec.ler_id;
3311 --
3312 insert_per_in_ler
3313 (p_ptnl_rec => l_ptnl_rec
3314 ,p_curr_per_in_ler_id => l_curr_per_in_ler_id
3315 ,p_effective_date => p_effective_date);
3316 --
3317 g_rec.person_id := p_person_id;
3318 g_rec.ler_id := l_ptnl_rec.ler_id;
3319 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3320 g_rec.replcd_flag := 'N';
3321 g_rec.crtd_flag := 'Y';
3322 g_rec.tmprl_flag := 'N';
3323 g_rec.dltd_flag := 'N';
3324 g_rec.open_and_clsd_flag := 'N';
3325 g_rec.not_crtd_flag := 'N';
3326 g_rec.clsd_flag := 'N';
3327 g_rec.stl_actv_flag := 'N';
3328 g_rec.clpsd_flag := 'N';
3329 g_rec.clsn_flag := 'N';
3330 g_rec.no_effect_flag := 'N';
3331 g_rec.cvrge_rt_prem_flag := 'N';
3332 g_rec.business_group_id := p_business_group_id;
3333 g_rec.effective_date := p_effective_date;
3334 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
3335 --
3336 benutils.write(p_rec => g_rec);
3337 --
3338 -- update ptnl
3339 --
3340 update_ptnl_per_for_ler
3341 (p_ptnl_rec => l_ptnl_rec
3342 ,p_effective_date => p_effective_date);
3343 --
3344 elsif l_ptnl_rec.lf_evt_ocrd_dt <= l_current_per_in_ler.lf_evt_ocrd_dt then
3345 --
3346 -- Bug : 3179 : Check any dead lock situation.
3347 -- Any pil's which occured on same day.
3348 --
3349 hr_utility.set_location('B',10);
3350 open c_check_deadlock_pil(l_ptnl_rec.lf_evt_ocrd_dt);
3351 --
3352 fetch c_check_deadlock_pil into l_deadlock_per_in_ler;
3353 --
3354 -- Bug : 3179 : pbodla : See case desicription below.
3355 -- 1. Created New hire say on 10/14/1999
3356 -- 2. Processed the new hire on 10/14/1999
3357 -- 3. Now a marriage reported.
3358 -- 4. Marriage PIL is created.
3359 -- 5. When marriage is processed new hire is backed out.
3360 -- and associated potential is made unprocessed.
3361 -- 6. Again when new hire potential is processed it causes
3362 -- marriage to back out as they happened on same day.
3363 -- 7. It is dead lock situation.
3364 -- 8. WDS, Phil, Pbodla : Decision is to set the ptnl to
3365 -- to manual if there is a processed or started potential
3366 -- on same day.
3367 --
3368 if l_deadlock_per_in_ler.per_in_ler_id is not null then
3369 --
3370 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
3371 (p_validate => false
3372 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3373 ,p_ptnl_ler_for_per_stat_cd => 'MNL'
3374 ,p_object_version_number => l_ptnl_rec.object_version_number
3375 ,p_effective_date => p_effective_date
3376 ,p_program_application_id => fnd_global.prog_appl_id
3377 ,p_program_id => fnd_global.conc_program_id
3378 ,p_request_id => fnd_global.conc_request_id
3379 ,p_program_update_date => sysdate
3380 ,p_mnl_dt => p_effective_date);
3381 --
3382 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
3383 fnd_message.set_token('LE_NAME',l_potent.name);
3384 fnd_message.set_token('PROC',l_proc);
3385 benutils.write(p_text => fnd_message.get);
3386 -- For BENAUTHE retreival purpose.
3387 fnd_message.set_name('BEN','BEN_92396_LIFE_EVENT_MANUAL');
3388 fnd_message.set_token('LE_NAME',l_potent.name);
3389 fnd_message.set_token('PROC',l_proc);
3390 g_rec.person_id := p_person_id;
3391 g_rec.ler_id := l_ptnl_rec.ler_id;
3392 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3393 g_rec.replcd_flag := 'N';
3394 g_rec.crtd_flag := 'N';
3395 g_rec.tmprl_flag := 'N';
3396 g_rec.dltd_flag := 'N';
3397 g_rec.open_and_clsd_flag := 'N';
3398 g_rec.not_crtd_flag := 'N';
3399 g_rec.clsd_flag := 'N';
3400 g_rec.stl_actv_flag := 'N';
3401 g_rec.clpsd_flag := 'N';
3402 g_rec.clsn_flag := 'Y';
3403 g_rec.no_effect_flag := 'N';
3404 g_rec.cvrge_rt_prem_flag := 'N';
3405 g_rec.business_group_id := p_business_group_id;
3406 g_rec.effective_date := p_effective_date;
3407 benutils.write(p_rec => g_rec);
3408 --
3409 raise ben_manage_life_events.g_life_event_after;
3410 --
3411 end if;
3412 --
3413 close c_check_deadlock_pil;
3414 --
3415 -- insert ptnl into per in ler
3416 --
3417 l_created_ler := 'Y';
3418 p_created_ler_id := l_ptnl_rec.ler_id;
3419 insert_per_in_ler
3420 (p_ptnl_rec => l_ptnl_rec
3421 ,p_curr_per_in_ler_id => l_curr_per_in_ler_id
3422 ,p_effective_date => p_effective_date);
3423 --
3424 g_rec.person_id := p_person_id;
3425 g_rec.ler_id := l_ptnl_rec.ler_id;
3426 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3427 g_rec.replcd_flag := 'Y';
3428 g_rec.crtd_flag := 'N';
3429 g_rec.tmprl_flag := 'N';
3430 g_rec.dltd_flag := 'N';
3431 g_rec.open_and_clsd_flag := 'N';
3432 g_rec.not_crtd_flag := 'N';
3433 g_rec.clsd_flag := 'N';
3434 g_rec.stl_actv_flag := 'N';
3435 g_rec.clpsd_flag := 'N';
3436 g_rec.clsn_flag := 'N';
3437 g_rec.no_effect_flag := 'N';
3438 g_rec.cvrge_rt_prem_flag := 'N';
3439 g_rec.business_group_id := p_business_group_id;
3440 g_rec.effective_date := p_effective_date;
3441 g_rec.per_in_ler_id := l_curr_per_in_ler_id;
3442 --
3443 benutils.write(p_rec => g_rec);
3444 --
3445 -- update ptnl for per_in_ler
3446 --
3447 update_ptnl_per_for_ler
3448 (p_ptnl_rec => l_ptnl_rec
3449 ,p_effective_date => p_effective_date);
3450 --
3451 -- Fix for April release is to remove all per in lers that occured
3452 -- after the current potential.
3453 --
3454 open get_all_per_in_ler(l_ptnl_rec.lf_evt_ocrd_dt, l_curr_per_in_ler_id);
3455 --
3456 loop
3457 --
3458 hr_utility.set_location(l_proc||' Loop GAPIL ', 50);
3459 fetch get_all_per_in_ler into l_pil_rec;
3460 exit when get_all_per_in_ler%notfound;
3461 --
3462 -- First back out all the relevant stuff
3463 --
3464 -- Use effective start date of per in ler as this is the only
3465 -- real safe way of making sure that future stuff will back out
3466 -- correctly.
3467 --
3468 ben_back_out_life_event.back_out_life_events
3469 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
3470 p_bckt_per_in_ler_id => l_curr_per_in_ler_id,
3471 p_business_group_id => p_business_group_id,
3472 p_effective_date => p_effective_date);
3473 --
3474 end loop;
3475 --
3476 close get_all_per_in_ler;
3477 --
3478 else
3479 --
3480 -- Potential is after per in ler so leave ptnl as is
3481 --
3482 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
3483 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_ptnl_rec.lf_evt_ocrd_dt);
3484 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
3485 l_current_per_in_ler.lf_evt_ocrd_dt);
3486 benutils.write(p_text => fnd_message.get);
3487 --
3488 g_rec.person_id := p_person_id;
3489 g_rec.ler_id := l_ptnl_rec.ler_id;
3490 g_rec.lf_evt_ocrd_dt := l_ptnl_rec.lf_evt_ocrd_dt;
3491 g_rec.replcd_flag := 'N';
3492 g_rec.crtd_flag := 'N';
3493 g_rec.tmprl_flag := 'N';
3494 g_rec.dltd_flag := 'N';
3495 g_rec.open_and_clsd_flag := 'N';
3496 g_rec.not_crtd_flag := 'N';
3497 g_rec.clsd_flag := 'N';
3498 g_rec.stl_actv_flag := 'Y';
3499 g_rec.clpsd_flag := 'N';
3500 g_rec.clsn_flag := 'N';
3501 g_rec.no_effect_flag := 'N';
3502 g_rec.cvrge_rt_prem_flag := 'N';
3503 g_rec.business_group_id := p_business_group_id;
3504 g_rec.effective_date := p_effective_date;
3505 --
3506 benutils.write(p_rec => g_rec);
3507 --
3508 -- For BENAUTHE retreival purpose.
3509 fnd_message.set_name('BEN','BEN_91797_PTNL_AFTER_ACTIVE');
3510 fnd_message.set_token('PTNL_LF_EVT_OCRD_DT',l_ptnl_rec.lf_evt_ocrd_dt);
3511 fnd_message.set_token('ACTIVE_LF_EVT_OCRD_DT',
3512 l_current_per_in_ler.lf_evt_ocrd_dt);
3513 raise ben_manage_life_events.g_life_event_after;
3514 --
3515 end if;
3516 --
3517
3518 --Start 6086392
3519
3520 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.first;
3521
3522
3523 if(l_bckdt_pil_indx is not null) then
3524
3525 loop
3526
3527 OPEN c_pil_ovn(ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx));
3528 FETCH c_pil_ovn INTO pil_ovn_rec;
3529 CLOSE c_pil_ovn;
3530
3531 ben_Person_Life_Event_api.update_person_life_event
3532 (p_per_in_ler_id => ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl(l_bckdt_pil_indx)
3533 ,p_bckt_per_in_ler_id => l_curr_per_in_ler_id
3534 ,p_object_version_number => pil_ovn_rec.object_version_number
3535 ,p_effective_date => p_effective_date
3536 ,P_PROCD_DT => l_date -- outputs
3537 ,P_STRTD_DT => l_date
3538 ,P_VOIDD_DT => l_date );
3539
3540 exit when l_bckdt_pil_indx = ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.last;
3541
3542 l_bckdt_pil_indx := ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.next(l_bckdt_pil_indx);
3543 end loop;
3544
3545 end if;
3546
3547 ben_evaluate_ptnl_lf_evt.g_bckdt_pil_tbl.delete;
3548
3549
3550 --End 6086392
3551
3552 hr_utility.set_location('Leaving:'|| l_proc, 90);
3553 --
3554 end eval_ptnl_per_for_ler;
3555 --
3556 -- iRec
3557 procedure irec_eval_ptnl_per_for_ler(p_validate in boolean default false
3558 ,p_person_id in number
3559 ,p_business_group_id in number
3560 ,p_ler_id in number default null
3561 ,p_mode in varchar2
3562 ,p_effective_date in date
3563 ,p_lf_evt_ocrd_dt in date
3564 ,p_assignment_id in number
3565 ,p_ptnl_ler_for_per_id in number
3566 ,p_created_ler_id out NOCOPY number) is
3567 --
3568 cursor c_ptnl(cv_ptnl_ler_for_per_id in number)
3569 is
3570 select ptnl.*
3571 from ben_ptnl_ler_for_per ptnl
3572 where ptnl.ptnl_ler_for_per_id = cv_ptnl_ler_for_per_id;
3573 --
3574 cursor get_per_in_ler(cv_assignment_id number,
3575 cv_person_id number,
3576 cv_ler_id number,
3577 cv_effective_date date)
3578 is
3579 select pil.per_in_ler_id,
3580 pil.per_in_ler_stat_cd,
3581 pil.lf_evt_ocrd_dt,
3582 pil.ler_id,
3583 pil.person_id,
3584 pil.business_group_id,
3585 pil.object_version_number,
3586 pil.procd_dt,
3587 pil.strtd_dt,
3588 pil.voidd_dt,
3589 pil.bckt_dt,
3590 pil.clsd_dt,
3591 pil.ntfn_dt,
3592 pil.ptnl_ler_for_per_id
3593 from ben_per_in_ler pil,
3594 ben_ler_f ler
3595 where pil.person_id = cv_person_id
3596 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
3597 and pil.assignment_id = cv_assignment_id
3598 and pil.ler_id = ler.ler_id
3599 and pil.ler_id = cv_ler_id
3600 and cv_effective_date
3601 between ler.effective_start_date
3602 and ler.effective_end_date
3603 and ler.typ_cd = 'IREC';
3604 --
3605 cursor c_ben_pil_elctbl_chc_popl (p_per_in_ler_id number) is
3606 select pel.pil_elctbl_chc_popl_id,
3607 pel.object_version_number
3608 from ben_pil_elctbl_chc_popl pel,
3609 ben_per_in_ler pil
3610 where pil.per_in_ler_id = p_per_in_ler_id
3611 and pil.business_group_id = p_business_group_id
3612 and pel.per_in_ler_id = pil.per_in_ler_id
3613 and pel.business_group_id = pil.business_group_id;
3614 --
3615 l_pil_rec get_per_in_ler%rowtype;
3616 l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
3617 l_procd_dt date;
3618 l_strtd_dt date;
3619 l_voidd_dt date;
3620 l_ntfn_dt date;
3621 l_dtctd_dt date;
3622 l_unprocd_dt date;
3623 l_object_version_number NUMBER;
3624 l_pil_object_version_number NUMBER;
3625 l_curr_per_in_ler_id number;
3626 l_created_ler varchar2(2) := 'N';
3627 l_create_pil varchar2(2) := 'N';
3628 --irec2
3629 l_pel_object_version_number number;
3630 l_pel_pk_id number;
3631 l_pil_assignment_id number;
3632 --
3633 begin
3634
3635 -- Step 1.
3636 -- Check whether per in ler is in processed status for the associated
3637 -- assignment_id. If yes then raise a error as the offer is already
3638 -- processed, enrollments may have been already completed and HR
3639 -- data may have been committed.
3640 --
3641 -- Step 2.
3642 --
3643 -- If per in ler is started status then back out the event.
3644 --
3645 -- Step 3.
3646 --
3647 -- Create the pil in started status.
3648 --
3649 open get_per_in_ler(p_assignment_id,
3650 p_person_id,
3651 p_ler_id,
3652 p_effective_date);
3653 fetch get_per_in_ler into l_pil_rec;
3654 close get_per_in_ler;
3655 --
3656 if l_pil_rec.per_in_ler_id is not null and
3657 l_pil_rec.per_in_ler_stat_cd = 'PROCD'
3658 then
3659 --
3660 -- Offer is already processed, so you can't initiate the offer again.
3661 -- Error out.
3662 --
3663 -- hr_utility.set_location('** ERROR SUP',9909);
3664 fnd_message.set_name('BEN','BEN_94025_IREC_OFFER_PROCESSED');
3665 benutils.write(p_text => fnd_message.get);
3666 --
3667 g_rec.person_id := p_person_id;
3668 g_rec.ler_id := l_pil_rec.ler_id;
3669 g_rec.lf_evt_ocrd_dt := p_lf_evt_ocrd_dt;
3670 g_rec.replcd_flag := 'N';
3671 g_rec.crtd_flag := 'N';
3672 g_rec.tmprl_flag := 'N';
3673 g_rec.dltd_flag := 'N';
3674 g_rec.open_and_clsd_flag := 'N';
3675 g_rec.not_crtd_flag := 'N';
3676 g_rec.clsd_flag := 'N';
3677 g_rec.stl_actv_flag := 'Y';
3678 g_rec.clpsd_flag := 'N';
3679 g_rec.clsn_flag := 'N';
3680 g_rec.no_effect_flag := 'N';
3681 g_rec.cvrge_rt_prem_flag := 'N';
3682 g_rec.business_group_id := p_business_group_id;
3683 g_rec.effective_date := p_effective_date;
3684 --
3685 benutils.write(p_rec => g_rec);
3686 --
3687 -- For BENAUTHE retreival purpose.
3688 --
3689 fnd_message.set_name('BEN','BEN_94025_IREC_OFFER_PROCESSED');
3690 benutils.write(p_text => fnd_message.get);
3691 raise ben_manage_life_events.g_life_event_after;
3692 --
3693 elsif l_pil_rec.per_in_ler_id is not null and
3694 l_pil_rec.per_in_ler_stat_cd = 'STRTD' then
3695 --
3696 -- Backout the life event.
3697 -- Update the potential with the new life event occured date.
3698 --
3699 -- Start irec2 : dont call back_out_life_events
3700 -- instead update PIL.PER_IN_LER_STAT_CD to VOID ,
3701 -- PEL.PIL_ELCTBL_POPL_STAT_CD to BCKDT
3702
3703 /* ben_back_out_life_event.back_out_life_events
3704 (p_per_in_ler_id => l_pil_rec.per_in_ler_id,
3705 p_bckt_per_in_ler_id => null,
3706 p_bckt_stat_cd => 'UNPROCD',
3707 p_business_group_id => p_business_group_id,
3708 p_effective_date => p_effective_date); */
3709
3710 -- update PIL
3711 -- 5068367 as per requirement, we would Backout instead of VOID
3712 ben_Person_Life_Event_api.update_person_life_event
3713 (p_per_in_ler_id => l_pil_rec.per_in_ler_id
3714 ,p_bckt_per_in_ler_id => null
3715 ,p_per_in_ler_stat_cd => 'BCKDT'
3716 ,p_prvs_stat_cd => l_pil_rec.per_in_ler_stat_cd
3717 ,p_object_version_number => l_pil_rec.object_version_number
3718 ,p_effective_date => p_effective_date
3719 ,P_PROCD_DT => l_procd_dt -- outputs
3720 ,P_STRTD_DT => l_strtd_dt
3721 ,P_VOIDD_DT => l_voidd_dt );
3722
3723 -- update PEL
3724 open c_ben_pil_elctbl_chc_popl(l_pil_rec.per_in_ler_id) ;
3725
3726 loop
3727
3728 fetch c_ben_pil_elctbl_chc_popl into l_pel_pk_id,
3729 l_pel_object_version_number;
3730 exit when c_ben_pil_elctbl_chc_popl%notfound;
3731 --
3732 --
3733 ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
3734 (p_validate => false,
3735 p_pil_elctbl_chc_popl_id => l_pel_pk_id,
3736 p_pil_elctbl_popl_stat_cd => 'BCKDT',
3737 p_object_version_number => l_pel_object_version_number,
3738 p_effective_date => p_effective_date);
3739
3740 end loop;
3741
3742 close c_ben_pil_elctbl_chc_popl;
3743
3744 -- update PPL
3745 --
3746 -- If lf event occured date is different then update the life event
3747 -- occured date.
3748 --
3749 open c_ptnl(l_pil_rec.ptnl_ler_for_per_id);
3750 fetch c_ptnl into l_ptnl_rec;
3751 close c_ptnl;
3752 --
3753 -- if l_ptnl_rec.lf_evt_ocrd_dt <> p_lf_evt_ocrd_dt then
3754 --
3755 -- update the potential record.
3756 --
3757 ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
3758 (p_validate => false
3759 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3760 ,p_ptnl_ler_for_per_stat_cd => 'PROCD'
3761 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
3762 ,p_procd_dt => p_lf_evt_ocrd_dt
3763 ,p_person_id => l_ptnl_rec.person_id
3764 ,p_business_group_id => l_ptnl_rec.business_group_id
3765 ,p_object_version_number => l_ptnl_rec.object_version_number
3766 ,p_effective_date => p_lf_evt_ocrd_dt
3767 ,p_program_application_id => fnd_global.prog_appl_id
3768 ,p_program_id => fnd_global.conc_program_id
3769 ,p_request_id => fnd_global.conc_request_id
3770 ,p_program_update_date => sysdate);
3771 --
3772 -- Now create the per in ler.
3773 --
3774 l_create_pil := 'Y';
3775 -- end if;
3776 --
3777 else
3778 --
3779 -- Create potential.
3780 --
3781 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per_perf
3782 (p_validate => false,
3783 p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id,
3784 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
3785 p_ptnl_ler_for_per_stat_cd => 'PROCD',
3786 p_ler_id => p_ler_id,
3787 p_person_id => p_person_id,
3788 p_ntfn_dt => sysdate, -- l_ntfn_dt
3789 p_unprocd_dt => p_lf_evt_ocrd_dt, -- l_unprocd_dt
3790 p_procd_dt => p_lf_evt_ocrd_dt,
3791 p_dtctd_dt => l_dtctd_dt,
3792 p_business_group_id => p_business_group_id,
3793 p_object_version_number => l_object_version_number,
3794 p_effective_date => p_lf_evt_ocrd_dt,
3795 p_program_application_id => fnd_global.prog_appl_id,
3796 p_program_id => fnd_global.conc_program_id,
3797 p_request_id => fnd_global.conc_request_id,
3798 p_program_update_date => sysdate);
3799 --
3800 l_create_pil := 'Y';
3801 --
3802 end if;
3803 --
3804 if l_create_pil = 'Y' then
3805 --
3806 ben_Person_Life_Event_api.create_Person_Life_Event_perf
3807 (p_validate => false
3808 ,p_per_in_ler_id => l_curr_per_in_ler_id
3809 ,p_ler_id => p_ler_id
3810 ,p_person_id => p_person_id
3811 ,p_per_in_ler_stat_cd => 'STRTD'
3812 ,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
3813 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
3814 ,p_business_group_id => p_business_group_id
3815 ,p_ntfn_dt => trunc(sysdate) -- p_ptnl_rec.ntfn_dt
3816 ,p_assignment_id => p_assignment_id
3817 ,p_object_version_number => l_pil_object_version_number
3818 ,p_effective_date => p_lf_evt_ocrd_dt
3819 ,p_program_application_id => fnd_global.prog_appl_id
3820 ,p_program_id => fnd_global.conc_program_id
3821 ,p_request_id => fnd_global.conc_request_id
3822 ,p_program_update_date => sysdate
3823 ,p_procd_dt => l_procd_dt
3824 ,p_strtd_dt => l_strtd_dt
3825 ,p_voidd_dt => l_voidd_dt);
3826 --
3827 end if;
3828 --
3829 p_created_ler_id := p_ler_id;
3830 --
3831 -- irec2 call create_ben_pil_assignment_api
3832 ben_pil_assignment_api.create_pil_assignment
3833 (p_validate => false
3834 ,p_pil_assignment_id => l_pil_assignment_id
3835 ,p_per_in_ler_id => l_curr_per_in_ler_id
3836 ,p_applicant_assignment_id => p_assignment_id
3837 ,p_offer_assignment_id => null
3838 ,p_object_version_number => l_object_version_number
3839 ) ;
3840
3841 end irec_eval_ptnl_per_for_ler;
3842 -- end iRec
3843
3844 end ben_evaluate_ptnl_lf_evt;