[Home] [Help]
PACKAGE BODY: APPS.BEN_PPS_LER
Source
4 ,p_new in g_pps_ler_rec
1 package body ben_pps_ler as
2 /* $Header: bepsptrg.pkb 120.2 2006/11/29 15:18:52 abparekh noship $*/
3 procedure ler_chk(p_old in g_pps_ler_rec
5 ,p_event in varchar2
6 ,p_effective_date in date) is
7 --
8 l_session_date DATE;
9 l_system_date DATE;
10 --
11 cursor get_system_date IS
12 select trunc(sysdate)
13 from dual;
14 --
15 cursor get_ler(l_status varchar2) is
16 select ler.ler_id
17 , ler.typ_cd
18 , ler.ocrd_dt_det_cd
19 from ben_ler_f ler
20 where ler.business_group_id = p_new.business_group_id
21 and l_session_date
22 between ler.effective_start_date
23 and ler.effective_end_date
24 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
25 and ((exists
26 (select 1
27 from ben_per_info_chg_cs_ler_f psl
28 , ben_ler_per_info_cs_ler_f lpl
29 where source_table = 'PER_PERIODS_OF_SERVICE'
30 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
31 and lpl.business_group_id = psl.business_group_id
32 and lpl.business_group_id = ler.business_group_id
33 and l_session_date between psl.effective_start_date
34 and psl.effective_end_date
35 and l_session_date between lpl.effective_start_date
36 and lpl.effective_end_date
37 and lpl.ler_id = ler.ler_id)
38 )
39 OR (exists
40 (select 1
41 from ben_rltd_per_chg_cs_ler_f rpc
42 , ben_ler_rltd_per_cs_ler_f lrp
43 where source_table = 'PER_PERIODS_OF_SERVICE'
44 and lrp.business_group_id = rpc.business_group_id
45 and lrp.business_group_id = ler.business_group_id
46 and l_session_date between rpc.effective_start_date
47 and rpc.effective_end_date
48 and l_session_date between lrp.effective_start_date
49 and lrp.effective_end_date
50 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
51 and lrp.ler_id = ler.ler_id)
52 ))
53 order by ler.ler_id;
54
55 cursor get_ler_col(p_ler_id IN NUMBER) is
56 select psl.source_column, psl.new_val, psl.old_val, 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
57 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
58 where lpl.ler_id = p_ler_id and
59 lpl.business_group_id = p_new.business_group_id
60 and lpl.business_group_id = psl.business_group_id
61 and l_session_date between psl.effective_start_date
62 and psl.effective_end_date
63 and l_session_date between lpl.effective_start_date
64 and lpl.effective_end_date
65 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
66 and source_table = 'PER_PERIODS_OF_SERVICE'
67 UNION
68 select rpc.source_column, rpc.new_val, rpc.old_val, 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
69 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
70 where lrp.ler_id = p_ler_id and
71 lrp.business_group_id = p_new.business_group_id
72 and lrp.business_group_id = rpc.business_group_id
73 and l_session_date between rpc.effective_start_date
74 and rpc.effective_end_date
75 and l_session_date between lrp.effective_start_date
76 and lrp.effective_end_date
77 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
78 and source_table = 'PER_PERIODS_OF_SERVICE'
79 order by 1;
80 --
81 cursor le_exists(p_person_id in number
82 ,p_ler_id in number
83 ,p_lf_evt_ocrd_dt in date) is
84 select 'Y'
85 from ben_ptnl_ler_for_per
86 where person_id = p_person_id
87 and ler_id = p_ler_id
88 and ptnl_ler_for_per_stat_cd = 'DTCTD'
89 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
90 --
91 cursor get_contacts(p_person_id in number) is
92 select person_id
93 from per_contact_relationships
94 where contact_person_id = p_person_id
95 and business_group_id = p_new.business_group_id
96 and l_session_date between nvl(date_start,l_session_date)
97 and nvl(date_end,l_session_date)
98 and personal_flag = 'Y'
99 order by person_id;
100 --
101 l_changed BOOLEAN;
102 l_ler_id NUMBER;
103 l_typ_cd ben_ler_f.typ_cd%type ;
104 l_ocrd_dt_cd VARCHAR2(30);
105 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
106 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
107 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
108 l_per_info_chg_cs_ler_rl number;
109 l_rule_output VARCHAR2(1);
110 l_ovn NUMBER;
111 l_ptnl_id NUMBER;
112 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
113 l_effective_start_date DATE ;
114 --l_session_date DATE ;
115 l_lf_evt_ocrd_date DATE ;
116 l_le_exists VARCHAR2(1);
117 l_mnl_dt date;
118 l_dtctd_dt date;
119 l_procd_dt date;
120 l_unprocd_dt date;
121 l_voidd_dt date;
122 l_type VARCHAR2(1);
123 l_hld_person_id NUMBER;
124 l_actual_termination_date date;
125 --
126 l_bool BOOLEAN;
130 l_col_new_val VARCHAR2(1000); -- UTF8
127 l_status VARCHAR2(1);
128 l_industry VARCHAR2(1);
129 l_continue boolean := true;
131 l_col_old_val varchar2(1000); -- UTF8
132 --
133 l_rule_overrides_flag VARCHAR2(1);
134 l_chg_mandatory_cd VARCHAR2(1);
135 l_trigger boolean := TRUE;
136 --
137 --
138 begin
139 --
140 -- Bug 3320133
141 benutils.set_data_migrator_mode;
142 -- End of Bug 3320133
143 if hr_general.g_data_migrator_mode in ( 'Y','P') then
144 --
145 -- We don't want triggers to fire
146 --
147 return;
148 --
149 end if;
150 --
151 if ben_pps_ler.ben_pps_evt_chk >= 1 and p_event = 'UPDATING' then
152 --
153 l_continue := false;
154 --
155 end if ;
156 --
157 if l_continue then
158 l_bool :=fnd_installation.get(appl_id => 805
159 ,dep_appl_id =>805
160 ,status => l_status
161 ,industry => l_industry);
162
163 hr_utility.set_location(' Entering: ben_pps_trigger', 10);
164 l_changed := FALSE;
165 --open get_session_date;
166 --fetch get_session_date into l_session_date;
167 --close get_session_date;
168 --
169 -- Bug 1928098 : Here the per_periodsa_of_service.date_start
170 -- is passed as effective_date, if the person whose hire date is 1960 and
171 -- and terminated in 2001 will not get the termination life event
172 -- if that ler is defined after 1960.
173 -- So try use the actual termination date first and then use the
174 -- effective_date passed in
175 --
176 l_session_date := nvl(p_new.actual_termination_date, p_effective_date);
177 open get_system_date;
178 fetch get_system_date into l_system_date;
179 close get_system_date;
180 l_effective_start_date := l_session_date;
181 -- l_lf_evt_ocrd_date := l_session_date;
182 hr_utility.set_location(' ben_pps_trigger', 20);
183
184 -- in some situations the date we use for occured on date is null,
185 -- use session date instead.
186 if p_new.actual_termination_date is null then
187 l_actual_termination_date := l_session_date;
188 else
189 l_actual_termination_date := p_new.actual_termination_date;
190 end if;
191
192 open get_ler(l_status);
193 loop
194 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
195 exit when get_ler%notfound;
196 l_trigger := TRUE;
197 --
198 hr_utility.set_location(' ler type '|| l_typ_cd, 20);
199 if l_ocrd_dt_cd is null then
200 l_lf_evt_ocrd_date := p_new.date_start;
201 else
202 --
203 -- Call the common date procedure.
204 --
205 -- Bug 1928098 : pass l_session_date
206 --
207 ben_determine_date.main
208 (p_date_cd => l_ocrd_dt_cd
209 ,p_effective_date => nvl(l_session_date,p_new.date_start)
210 ,p_lf_evt_ocrd_dt => nvl(l_session_date,p_new.date_start)
211 ,p_returned_date => l_lf_evt_ocrd_date
212 );
213 end if;
214 --
215 open get_ler_col(l_ler_id);
216 loop
217 fetch get_ler_col into l_column,l_new_val, l_old_val, l_type, l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
218 exit when get_ler_col%NOTFOUND;
219 l_changed := TRUE;
220 if get_ler_col%ROWCOUNT = 1 then
221 l_changed := TRUE;
222 end if;
223 hr_utility.set_location(' ben_pps_trigger', 50);
224 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
225 -- If it returns Y, then see the applicability of the data
226 -- changes based on new and old values.
227 --
228 l_rule_output := 'Y';
229 --
230 if l_per_info_chg_cs_ler_rl is not null then
231 --
232 --
233 if l_column = 'DATE_START' then
234 l_col_old_val := to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
235 l_col_new_val := to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
236 end if;
237 --
238 if l_column = 'ACTUAL_TERMINATION_DATE' then
239 l_col_old_val := to_char(p_old.ACTUAL_TERMINATION_DATE, 'YYYY/MM/DD HH24:MI:SS');
240 l_col_new_val := to_char(p_new.ACTUAL_TERMINATION_DATE, 'YYYY/MM/DD HH24:MI:SS');
241 end if;
242 --
243 if l_column = 'ADJUSTED_SVC_DATE' then
244 l_col_old_val := to_char(p_old.ADJUSTED_SVC_DATE, 'YYYY/MM/DD HH24:MI:SS');
245 l_col_new_val := to_char(p_new.ADJUSTED_SVC_DATE, 'YYYY/MM/DD HH24:MI:SS');
246 end if;
247 --
248 if l_column = 'FINAL_PROCESS_DATE' then
249 l_col_old_val := to_char(p_old.FINAL_PROCESS_DATE, 'YYYY/MM/DD HH24:MI:SS');
250 l_col_new_val := to_char(p_new.FINAL_PROCESS_DATE, 'YYYY/MM/DD HH24:MI:SS');
251 end if;
252 --
253 if l_column = 'ATTRIBUTE1' then
254 l_col_old_val := p_old.ATTRIBUTE1;
255 l_col_new_val := p_new.ATTRIBUTE1;
256 end if;
257 --
258 if l_column = 'ATTRIBUTE2' then
259 l_col_old_val := p_old.ATTRIBUTE2;
260 l_col_new_val := p_new.ATTRIBUTE2;
261 end if;
262 --
263 if l_column = 'ATTRIBUTE3' then
264 l_col_old_val := p_old.ATTRIBUTE3;
265 l_col_new_val := p_new.ATTRIBUTE3;
266 end if;
267 --
268 if l_column = 'ATTRIBUTE4' then
269 l_col_old_val := p_old.ATTRIBUTE4;
270 l_col_new_val := p_new.ATTRIBUTE4;
271 end if;
272 --
273 if l_column = 'ATTRIBUTE5' then
277 --
274 l_col_old_val := p_old.ATTRIBUTE5;
275 l_col_new_val := p_new.ATTRIBUTE5;
276 end if;
278 if l_column = 'LEAVING_REASON' then
279 l_col_old_val := p_old.LEAVING_REASON;
280 l_col_new_val := p_new.LEAVING_REASON;
281 end if;
282 --
283 benutils.exec_rule(
284 p_formula_id => l_per_info_chg_cs_ler_rl,
285 p_effective_date => l_session_date,
286 p_lf_evt_ocrd_dt => null,
287 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
288 p_person_id => nvl(p_new.person_id, p_old.person_id),
289 p_new_value => l_col_new_val,
290 p_old_value => l_col_old_val,
291 p_column_name => l_column,
292 p_pk_id => to_char(p_new.PERIOD_OF_SERVICE_ID), -- 9999 is it passed.
293 p_param5 => 'BEN_PPS_IN_DATE_START',
294 p_param5_value => to_char(p_new.DATE_START,'YYYY/MM/DD HH24:MI:SS'),
295 p_param6 => 'BEN_PPS_IO_DATE_START',
296 p_param6_value => to_char(p_old.DATE_START,'YYYY/MM/DD HH24:MI:SS'),
297 p_param7 => 'BEN_PPS_IN_ACTUAL_TERMINATION_DATE',
298 p_param7_value => to_char(p_new.ACTUAL_TERMINATION_DATE,'YYYY/MM/DD HH24:MI:SS'),
299 p_param8 => 'BEN_PPS_IO_ACTUAL_TERMINATION_DATE',
300 p_param8_value => to_char(p_old.ACTUAL_TERMINATION_DATE,'YYYY/MM/DD HH24:MI:SS'),
301 p_param9 => 'BEN_PPS_IN_ADJUSTED_SVC_DATE',
302 p_param9_value => to_char(p_new.ADJUSTED_SVC_DATE,'YYYY/MM/DD HH24:MI:SS'),
303 p_param10 => 'BEN_PPS_IO_ADJUSTED_SVC_DATE',
304 p_param10_value => to_char(p_old.ADJUSTED_SVC_DATE,'YYYY/MM/DD HH24:MI:SS'),
305 p_param11 => 'BEN_PPS_IN_FINAL_PROCESS_DATE',
306 p_param11_value => to_char(p_new.FINAL_PROCESS_DATE,'YYYY/MM/DD HH24:MI:SS'),
307 p_param12 => 'BEN_PPS_IO_FINAL_PROCESS_DATE',
308 p_param12_value => to_char(p_old.FINAL_PROCESS_DATE,'YYYY/MM/DD HH24:MI:SS'),
309 p_param13 => 'BEN_PPS_IN_LEAVING_REASON',
310 p_param13_value => p_new.LEAVING_REASON,
311 p_param14 => 'BEN_PPS_IO_LEAVING_REASON',
312 p_param14_value => p_old.LEAVING_REASON,
313 p_param15 => 'BEN_PPS_IN_ATTRIBUTE1',
314 p_param15_value => p_new.ATTRIBUTE1,
315 p_param16 => 'BEN_PPS_IO_ATTRIBUTE1',
316 p_param16_value => p_old.ATTRIBUTE1,
317 p_param17 => 'BEN_PPS_IN_ATTRIBUTE2',
318 p_param17_value => p_new.ATTRIBUTE2,
319 p_param18 => 'BEN_PPS_IO_ATTRIBUTE2',
320 p_param18_value => p_old.ATTRIBUTE2,
321 p_param20 => 'BEN_PPS_IN_ATTRIBUTE3',
322 p_param20_value => p_new.ATTRIBUTE3,
323 p_param21 => 'BEN_PPS_IO_ATTRIBUTE3',
324 p_param21_value => p_old.ATTRIBUTE3,
325 p_param22 => 'BEN_PPS_IN_ATTRIBUTE4',
326 p_param22_value => p_new.ATTRIBUTE4,
327 p_param23 => 'BEN_PPS_IO_ATTRIBUTE4',
328 p_param23_value => p_old.ATTRIBUTE4,
329 p_param24 => 'BEN_PPS_IN_ATTRIBUTE5',
330 p_param24_value => p_new.ATTRIBUTE5,
331 p_param25 => 'BEN_PPS_IO_ATTRIBUTE5',
332 p_param25_value => p_old.ATTRIBUTE5,
333 p_param26 => 'BEN_IV_LER_ID', /* Bug 3891096 */
334 p_param26_value => to_char(l_ler_id),
335 p_ret_val => l_rule_output);
336 --
337 end if;
338 --
339
340 --
341 -- Bug 1877018
342 if l_column = 'FINAL_PROCESS_DATE' then
343 l_changed := (benutils.column_changed(p_old.FINAL_PROCESS_DATE
344 ,p_new.FINAL_PROCESS_DATE,l_new_val) AND
345 benutils.column_changed(p_new.FINAL_PROCESS_DATE
346 ,p_old.FINAL_PROCESS_DATE,l_old_val) AND
347 (l_changed));
348 --
349 if l_ocrd_dt_cd is null then
350 l_lf_evt_ocrd_date := nvl(p_new.FINAL_PROCESS_DATE,
351 l_session_date);
352 else
353 --
354 -- Call the common date procedure.
355 --
356 ben_determine_date.main
357 (p_date_cd => l_ocrd_dt_cd
358 ,p_effective_date => nvl(p_new.FINAL_PROCESS_DATE,
359 l_session_date)
360 ,p_lf_evt_ocrd_dt => nvl(p_new.FINAL_PROCESS_DATE,
361 l_session_date)
362 ,p_returned_date => l_lf_evt_ocrd_date
363 );
364 end if;
365 hr_utility.set_location(' l_changed:',39);
366 end if;
367 --
368 if l_column = 'DATE_START' then
369 l_changed := (benutils.column_changed(p_old.date_start
370 ,p_new.date_start,l_new_val) AND
371 benutils.column_changed(p_new.date_start
372 ,p_old.date_start,l_old_val) AND
373 (l_changed));
374 hr_utility.set_location(' l_changed:',40);
375 --
376 -- Bug 5672925
380 if l_ocrd_dt_cd is null then
377 -- If Person Changes are based on DATE_START then LF_EVT_OCRD_DT should be
378 -- new DATE_START and not the effective date
379 --
381 l_lf_evt_ocrd_date := p_new.date_start;
382 else
383 --
384 -- Call the common date procedure.
385 --
386 ben_determine_date.main
387 (p_date_cd => l_ocrd_dt_cd
388 ,p_effective_date => p_new.date_start
389 ,p_lf_evt_ocrd_dt => p_new.date_start
390 ,p_returned_date => l_lf_evt_ocrd_date
391 );
392 end if;
393 hr_utility.set_location('ACE l_lf_evt_ocrd_date = ' || l_lf_evt_ocrd_date, 9999);
394 -- Bug 5672925
395 --
396 end if;
397 --
398 if l_column = 'ACTUAL_TERMINATION_DATE' then
399 l_changed := (benutils.column_changed(p_old.actual_termination_date
400 ,p_new.actual_termination_date,l_new_val) AND
401 benutils.column_changed(p_new.actual_termination_date
402 ,p_old.actual_termination_date,l_old_val) AND
403 (l_changed));
404 --
405 if l_ocrd_dt_cd is null then
406 l_lf_evt_ocrd_date := l_actual_termination_date;
407 else
408 --
409 -- Call the common date procedure.
410 --
411 ben_determine_date.main
412 (p_date_cd => l_ocrd_dt_cd
413 ,p_effective_date => l_actual_termination_date
414 ,p_lf_evt_ocrd_dt => p_new.actual_termination_date
415 ,p_returned_date => l_lf_evt_ocrd_date
416 );
417 end if;
418 end if;
419 --
420 if l_column = 'LEAVING_REASON' then
421 l_changed := (benutils.column_changed(p_old.leaving_reason
422 ,p_new.leaving_reason,l_new_val) AND
423 benutils.column_changed(p_new.leaving_reason
424 ,p_old.leaving_reason,l_old_val) AND
425 (l_changed));
426 --
427 if l_ocrd_dt_cd is null then
428 l_lf_evt_ocrd_date := l_actual_termination_date;
429 else
430 --
431 -- Call the common date procedure.
432 --
433 ben_determine_date.main
434 (p_date_cd => l_ocrd_dt_cd
435 ,p_effective_date => l_actual_termination_date
436 ,p_lf_evt_ocrd_dt => p_new.actual_termination_date
437 ,p_returned_date => l_lf_evt_ocrd_date
438 );
439 end if;
440
441 end if;
442
443 --
444 if l_column = 'ADJUSTED_SVC_DATE' then
445 l_changed := (benutils.column_changed(p_old.adjusted_svc_date
446 ,p_new.adjusted_svc_date,l_new_val) AND
447 benutils.column_changed(p_new.adjusted_svc_date
448 ,p_old.adjusted_svc_date,l_old_val) AND
449 (l_changed));
450 end if;
451 --
452 if l_column = 'ATTRIBUTE1' then
453 l_changed := (benutils.column_changed(p_old.attribute1
454 ,p_new.attribute1,l_new_val) AND
455 benutils.column_changed(p_new.attribute1
456 ,p_old.attribute1,l_old_val) AND
457 (l_changed));
458 end if;
459 --
460 if l_column = 'ATTRIBUTE2' then
461 l_changed := (benutils.column_changed(p_old.attribute2
462 ,p_new.attribute2,l_new_val) AND
463 benutils.column_changed(p_new.attribute2
464 ,p_old.attribute2,l_old_val) AND
465 (l_changed));
466 end if;
467 --
468 if l_column = 'ATTRIBUTE3' then
469 l_changed := (benutils.column_changed(p_old.attribute3
470 ,p_new.attribute3,l_new_val) AND
471 benutils.column_changed(p_new.attribute3
472 ,p_old.attribute3,l_old_val) AND
473 (l_changed));
474 end if;
475 --
476 if l_column = 'ATTRIBUTE4' then
477 l_changed := (benutils.column_changed(p_old.attribute4
478 ,p_new.attribute4,l_new_val) AND
479 benutils.column_changed(p_new.attribute4
480 ,p_old.attribute4,l_old_val) AND
481 (l_changed));
482 end if;
483 --
484 if l_column = 'ATTRIBUTE5' then
485 l_changed := (benutils.column_changed(p_old.attribute5
486 ,p_new.attribute5,l_new_val) AND
487 benutils.column_changed(p_new.attribute5
488 ,p_old.attribute5,l_old_val) AND
489 (l_changed));
490 end if;
491 --
492
493 --
494 -- Checking the rule output and the rule override flag.
495 -- Whether the rule is mandatory or not, rule output should return 'Y'
496 -- Rule Mandatory flag is just to override the column data change.
497
501 l_changed := l_changed AND TRUE;
498 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
499 l_changed := TRUE ;
500 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
502 elsif l_rule_output = 'N' then
503 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
504 l_changed := FALSE;
505 end if;
506
507 hr_utility.set_location('After the rule Check ',20.05);
508 if l_changed then
509 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
510 else
511 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
512 end if;
513 -- Check for Column Mandatory Change
514 -- If column change is mandatory and data change has failed then dont trigger
515 -- If column change is non-mandatory and the data change has passed, then trigger.
516
517 if l_chg_mandatory_cd = 'Y' and not l_changed then
518 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
519 l_changed := FALSE;
520 l_trigger := FALSE;
521 exit;
522 elsif l_chg_mandatory_cd = 'Y' and l_changed then
523 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
524 l_changed := TRUE;
525 -- exit; */
526 elsif l_chg_mandatory_cd = 'N' and l_changed then
527 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
528 l_changed := TRUE;
529 l_trigger := TRUE;
530 exit;
531 end if;
532
533 hr_utility.set_location('After the Mandatory code check ',20.05);
534 if l_changed then
535 hr_utility.set_location(' l_change TRUE ', 20.1);
536 else
537 hr_utility.set_location(' l_change FALSE ', 20.1);
538 end if;
539 --
540 /* if not l_changed then
541 exit;
542 end if; */
543 end loop;
544 hr_utility.set_location(' ben_pps_trigger', 50);
545 l_ptnl_id := 0;
546 l_ovn :=null;
547 if l_trigger then
548 if l_type = 'P' then
549 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
550 fetch le_exists into l_le_exists;
551 if le_exists%notfound then
552 hr_utility.set_location(' Entering: ben_pps_trigger5', 60);
553
554 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
555 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
556 (p_validate => false
557 ,p_ptnl_ler_for_per_id => l_ptnl_id
558 ,p_ntfn_dt => l_system_date
559 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
560 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
561 ,p_ler_id => l_ler_id
562 ,p_ler_typ_cd => l_typ_cd
563 ,p_person_id => p_new.person_id
564 ,p_business_group_Id =>p_new.business_group_id
565 ,p_object_version_number => l_ovn
566 ,p_effective_date => l_effective_start_date
567 ,p_dtctd_dt => l_effective_start_date);
568 end if;
569 close le_exists;
570 elsif l_type = 'R' then
571 hr_utility.set_location(' Entering: ben_pps_trigger5-', 65);
572 open get_contacts(p_new.person_id);
573 loop
574 fetch get_contacts into l_hld_person_id;
575 exit when get_contacts%notfound;
576 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
577 fetch le_exists into l_le_exists;
578 if le_exists%notfound then
579 hr_utility.set_location(' Entering: ben_pps_trigger5', 60);
580
581 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
582 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
583 (p_validate => false
584 ,p_ptnl_ler_for_per_id => l_ptnl_id
585 ,p_ntfn_dt => l_system_date
586 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
587 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
588 ,p_ler_id => l_ler_id
589 ,p_ler_typ_cd => l_typ_cd
590 ,p_person_id => l_hld_person_id
591 ,p_business_group_Id =>p_new.business_group_id
592 ,p_object_version_number => l_ovn
593 ,p_effective_date => l_effective_start_date
594 ,p_dtctd_dt => l_effective_start_date);
595 end if;
596 l_ptnl_id := 0;
597 l_ovn :=null;
598 close le_exists;
599 end loop;
600 close get_contacts;
601 end if;
602 --
603 -- reset the variables.
604 --
605 hr_utility.set_location(' ben_pps_trigger', 60);
606 l_changed := FALSE;
607 l_trigger := TRUE;
608 l_ovn := NULL;
609 l_effective_start_date := l_session_date;
610 -- l_lf_evt_ocrd_date := l_session_date;
611 end if;
612 close get_ler_col;
613 end loop;
614 hr_utility.set_location(' ben_pps_trigger', 70);
615 close get_ler;
616 hr_utility.set_location(' leaving ben_pps_trigger', 80);
617 end if;
618 --
619 -- following are to be removed whne hr fix the bug
620 --
621 if p_event = 'UPDATING' and
622 p_new.actual_termination_date is null and
623 p_old.actual_termination_date is not null then
624 --
625 ben_pps_ler.ben_pps_evt_chk := ben_pps_ler.ben_pps_evt_chk + 1 ;
626 --
627 end if ;
628 --
629 if ben_pps_ler.ben_pps_evt_chk >= 1 then --5095450: resetting it to 0 /* NOTE logic using ben_pps_evt_chk can be removed !! */
630 --
631 ben_pps_ler.ben_pps_evt_chk := 0 ;
632 --
633 end if ;
634 --
635 end;
636 end ben_pps_ler;