[Home] [Help]
PACKAGE BODY: APPS.BEN_PPR_LER
Source
1 package body ben_ppr_ler as
2 /* $Header: bepprtrg.pkb 120.0 2005/05/28 11:00:38 appldev noship $*/
3 procedure ler_chk(p_old IN g_ppr_ler_rec
4 ,p_new IN g_ppr_ler_rec
5 ,p_effective_date in date
6 ) is
7 --
8 l_session_date DATE;
9
10 --
11 cursor get_session_date IS
12 select effective_date
13 from fnd_sessions
14 where session_id = userenv('SESSIONID');
15 --
16
17 cursor get_ler(l_status varchar2) is
18 select ler.ler_id
19 , ler.typ_cd
20 , ler.ocrd_dt_det_cd
21 from ben_ler_f ler
22 where
23 ler.business_group_id = p_new.business_group_id
24 and l_session_date between ler.effective_start_date and ler.effective_end_date
25 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
26 and
27 ((exists
28 (select 1
29 from ben_per_info_chg_cs_ler_f psl
30 , ben_ler_per_info_cs_ler_f lpl
31 where source_table = 'PER_PERFORMANCE_REVIEWS'
32 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
33 and lpl.business_group_id = psl.business_group_id
34 and lpl.business_group_id = ler.business_group_id
35 and l_session_date between psl.effective_start_date
36 and psl.effective_end_date
37 and l_session_date between lpl.effective_start_date
38 and lpl.effective_end_date -- For Bug 3299709
39 and lpl.ler_id = ler.ler_id)
40 )
41 OR (exists
42 (select 1
43 from ben_rltd_per_chg_cs_ler_f rpc
44 , ben_ler_rltd_per_cs_ler_f lrp
45 where source_table = 'PER_PERFORMANCE_REVIEWS'
46 and lrp.business_group_id = rpc.business_group_id
47 and lrp.business_group_id = ler.business_group_id
48 and l_session_date between rpc.effective_start_date
49 and rpc.effective_end_date
50 and l_session_date between lrp.effective_start_date
51 and lrp.effective_end_date -- For Bug 3299709
52 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
53 and lrp.ler_id = ler.ler_id)
54 ))
55 order by ler.ler_id;
56
57 --
58 cursor get_ler_col(p_ler_id IN NUMBER) is
59 select psl.source_column
60 , psl.new_val
61 , psl.old_val
62 , 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
63 from ben_ler_per_info_cs_ler_f lpl
64 , ben_per_info_chg_cs_ler_f psl
65 where lpl.ler_id = p_ler_id
66 and lpl.business_group_id = p_new.business_group_id
67 and lpl.business_group_id = psl.business_group_id
68 and l_session_date between
69 psl.effective_start_date and psl.effective_end_date
70 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
71 and source_table = 'PER_PERFORMANCE_REVIEWS'
72 UNION
73 select rpc.source_column
74 , rpc.new_val
75 , rpc.old_val
76 , 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
77 from ben_ler_rltd_per_cs_ler_f lrp
78 , ben_rltd_per_chg_cs_ler_f rpc
79 where lrp.ler_id = p_ler_id
80 and lrp.business_group_id = p_new.business_group_id
81 and lrp.business_group_id = rpc.business_group_id
82 and l_session_date between
83 rpc.effective_start_date and rpc.effective_end_date
84 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
85 and source_table = 'PER_PERFORMANCE_REVIEWS'
86 order by 1;
87 --
88 cursor le_exists(p_person_id in number
89 ,p_ler_id in number
90 ,p_lf_evt_ocrd_dt in date) is
91 select 'Y'
92 from ben_ptnl_ler_for_per
93 where person_id = p_person_id
94 and ler_id = p_ler_id
95 and ptnl_ler_for_per_stat_cd = 'DTCTD'
96 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
97 ;
98 --
99 cursor get_contacts(p_person_id in number) is
100 select person_id
101 from per_contact_relationships
102 where contact_person_id = p_person_id
103 and business_group_id = p_new.business_group_id
104 and l_session_date between nvl(date_start,l_session_date)
105 and nvl(date_end,l_session_date)
106 and personal_flag = 'Y'
107 order by person_id;
108 --
109 cursor c_get_review_type(p_event_id in number) is
110 select type
111 from per_events
112 where event_id = p_event_id;
113 --
114 l_changed BOOLEAN;
115 l_lf_evt_ocrd_date DATE;
116 l_effective_start_date DATE;
117 l_ler_id NUMBER;
118 l_typ_cd ben_ler_f.typ_cd%type ;
119 l_ovn NUMBER;
120 l_ptnl_id NUMBER;
121 l_hld_person_id NUMBER;
122 l_ocrd_dt_cd VARCHAR2(30);
123 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
124 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
125 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
126 l_per_info_chg_cs_ler_rl number;
127 l_rule_output VARCHAR2(1);
128 l_type VARCHAR2(1);
129 l_le_exists VARCHAR2(1);
130
131 --
132 l_bool BOOLEAN;
133 l_status VARCHAR2(1);
134 l_industry VARCHAR2(1);
135 --
136 l_col_new_val varchar2(1000);
137 l_col_old_val varchar2(1000);
138 --
139 l_rule_overrides_flag VARCHAR2(1);
140 l_chg_mandatory_cd VARCHAR2(1);
141 l_trigger boolean := TRUE;
142 --
143 l_old_review_type varchar2(30);
144 l_new_review_type varchar2(30);
145 --
146 --
147 begin
148
149 -- Bug 3320133
150 benutils.set_data_migrator_mode;
151 if hr_general.g_data_migrator_mode in ( 'Y','P') then
152 --
153 return;
154 --
155 end if;
156 --
157 -- End of Bug 3320133
158
159 l_bool :=fnd_installation.get(appl_id => 805
160 ,dep_appl_id =>805
161 ,status => l_status
162 ,industry => l_industry);
163
164 hr_utility.set_location(' Entering: ben_ppr_trigger', 10);
165 l_changed := FALSE;
166
167 --
168 If p_effective_date is not null then
169 l_session_date := p_effective_date ;
170 else
171 open get_session_date;
172 fetch get_session_date into l_session_date;
173 close get_session_date;
174 end if;
175
176
177 --
178 hr_utility.set_location('p_new.review_date is'||p_new.review_date, 999);
179 hr_utility.set_location('l_session_date is'||l_session_date, 999);
180 --
181 l_effective_start_date := l_session_date;
182 --
183 hr_utility.set_location(' ben_ppr_trigger', 20);
184 hr_utility.set_location('sess '||l_session_date, 20);
185 hr_utility.set_location('PERSON '||p_new.person_id, 20);
186 hr_utility.set_location('BG '||p_new.business_group_id, 20);
187
188 open get_ler(l_status);
189 loop
190 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
191 exit when get_ler%notfound;
192 l_trigger := TRUE;
193
194
195 if l_ocrd_dt_cd is null then
196 l_lf_evt_ocrd_date := p_new.review_date;
197 else
198 --
199 -- Call the common date procedure.
200 --
201 ben_determine_date.main
202 (p_date_cd => l_ocrd_dt_cd
203 ,p_effective_date => p_new.review_date
204 ,p_lf_evt_ocrd_dt => p_new.review_date
205 ,p_returned_date => l_lf_evt_ocrd_date
206 );
207 end if;
208
209
210 --
211 open get_ler_col(l_ler_id);
212 loop
213 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;
214 exit when get_ler_col%NOTFOUND;
215
216 hr_utility.set_location('LER '||l_ler_id, 20);
217 hr_utility.set_location('COLUMN '||l_column, 20);
218 hr_utility.set_location('NEWVAL '||l_new_val, 20);
219 hr_utility.set_location('OLDVAL '||l_old_val, 20);
220 hr_utility.set_location('TYPE '||l_type, 20);
221
222 l_changed := TRUE;
223 if get_ler_col%ROWCOUNT = 1 then
224 hr_utility.set_location('rowcount 1 ', 20);
225 l_changed := TRUE;
226 end if;
227 hr_utility.set_location(' ben_ppr_trigger', 50);
228 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
229 -- If it returns Y, then see the applicability of the data
230 -- changes based on new and old values.
231 --
232 l_rule_output := 'Y';
233 --
234 if l_per_info_chg_cs_ler_rl is not null then
235 --
236 if l_column = 'PERFORMANCE_RATING' then
237 l_col_old_val := p_old.PERFORMANCE_RATING;
238 l_col_new_val := p_new.PERFORMANCE_RATING;
239 end if;
240 --
241 if l_column = 'EVENT_ID' then
242 l_col_old_val := p_old.EVENT_ID;
243 l_col_new_val := p_new.EVENT_ID;
244 end if;
245 --
246
247 --
248 if l_column = 'REVIEW_DATE' then
249 l_col_old_val := p_old.REVIEW_DATE;
250 l_col_new_val := p_new.REVIEW_DATE;
251 end if;
252 --
253 if l_column = 'BUSINESS_GROUP_ID' then
254 l_col_old_val := to_char(p_old.BUSINESS_GROUP_ID);
255 l_col_new_val := to_char(p_new.BUSINESS_GROUP_ID);
256 end if;
257 --
258 if l_column = 'ATTRIBUTE1' then
259 l_col_old_val := p_old.ATTRIBUTE1;
260 l_col_new_val := p_new.ATTRIBUTE1;
261 end if;
262 --
263 if l_column = 'ATTRIBUTE2' then
264 l_col_old_val := p_old.ATTRIBUTE2;
265 l_col_new_val := p_new.ATTRIBUTE2;
266 end if;
267 --
268 if l_column = 'ATTRIBUTE3' then
269 l_col_old_val := p_old.ATTRIBUTE3;
270 l_col_new_val := p_new.ATTRIBUTE3;
271 end if;
272 --
273 if l_column = 'ATTRIBUTE4' then
274 l_col_old_val := p_old.ATTRIBUTE4;
275 l_col_new_val := p_new.ATTRIBUTE4;
276 end if;
277 --
278 if l_column = 'ATTRIBUTE5' then
279 l_col_old_val := p_old.ATTRIBUTE5;
280 l_col_new_val := p_new.ATTRIBUTE5;
281 end if;
282 --
283 if l_column = 'ATTRIBUTE6' then
284 l_col_old_val := p_old.ATTRIBUTE6;
285 l_col_new_val := p_new.ATTRIBUTE6;
286 end if;
287 --
288 if l_column = 'ATTRIBUTE7' then
289 l_col_old_val := p_old.ATTRIBUTE7;
290 l_col_new_val := p_new.ATTRIBUTE7;
291 end if;
292 --
293 if l_column = 'ATTRIBUTE8' then
294 l_col_old_val := p_old.ATTRIBUTE8;
295 l_col_new_val := p_new.ATTRIBUTE8;
296 end if;
297 --
298 if l_column = 'ATTRIBUTE9' then
299 l_col_old_val := p_old.ATTRIBUTE9;
300 l_col_new_val := p_new.ATTRIBUTE9;
301 end if;
302 --
303 if l_column = 'ATTRIBUTE10' then
304 l_col_old_val := p_old.ATTRIBUTE10;
305 l_col_new_val := p_new.ATTRIBUTE10;
306 end if;
307 --
308 if l_column = 'ATTRIBUTE11' then
309 l_col_old_val := p_old.ATTRIBUTE11;
310 l_col_new_val := p_new.ATTRIBUTE11;
311 end if;
312 --
313 if l_column = 'ATTRIBUTE12' then
314 l_col_old_val := p_old.ATTRIBUTE12;
315 l_col_new_val := p_new.ATTRIBUTE12;
316 end if;
317 --
318 if l_column = 'ATTRIBUTE13' then
319 l_col_old_val := p_old.ATTRIBUTE13;
320 l_col_new_val := p_new.ATTRIBUTE13;
321 end if;
322 --
323 if l_column = 'ATTRIBUTE14' then
324 l_col_old_val := p_old.ATTRIBUTE14;
325 l_col_new_val := p_new.ATTRIBUTE14;
326 end if;
327 --
328 if l_column = 'ATTRIBUTE15' then
329 l_col_old_val := p_old.ATTRIBUTE15;
330 l_col_new_val := p_new.ATTRIBUTE15;
331 end if;
332 --
333 if l_column = 'ATTRIBUTE16' then
334 l_col_old_val := p_old.ATTRIBUTE16;
335 l_col_new_val := p_new.ATTRIBUTE16;
336 end if;
337 --
338 if l_column = 'ATTRIBUTE17' then
339 l_col_old_val := p_old.ATTRIBUTE17;
340 l_col_new_val := p_new.ATTRIBUTE17;
341 end if;
342 --
343 if l_column = 'ATTRIBUTE18' then
344 l_col_old_val := p_old.ATTRIBUTE18;
345 l_col_new_val := p_new.ATTRIBUTE18;
346 end if;
347 --
351 end if;
348 if l_column = 'ATTRIBUTE19' then
349 l_col_old_val := p_old.ATTRIBUTE19;
350 l_col_new_val := p_new.ATTRIBUTE19;
352 --
353 if l_column = 'ATTRIBUTE20' then
354 l_col_old_val := p_old.ATTRIBUTE20;
355 l_col_new_val := p_new.ATTRIBUTE20;
356 end if;
357 --
358 if l_column = 'ATTRIBUTE21' then
359 l_col_old_val := p_old.ATTRIBUTE21;
360 l_col_new_val := p_new.ATTRIBUTE21;
361 end if;
362 --
363 if l_column = 'ATTRIBUTE22' then
364 l_col_old_val := p_old.ATTRIBUTE22;
365 l_col_new_val := p_new.ATTRIBUTE22;
366 end if;
367 --
368 if l_column = 'ATTRIBUTE23' then
369 l_col_old_val := p_old.ATTRIBUTE23;
370 l_col_new_val := p_new.ATTRIBUTE23;
371 end if;
372 --
373 if l_column = 'ATTRIBUTE24' then
374 l_col_old_val := p_old.ATTRIBUTE24;
375 l_col_new_val := p_new.ATTRIBUTE24;
376 end if;
377 --
378 if l_column = 'ATTRIBUTE25' then
379 l_col_old_val := p_old.ATTRIBUTE25;
380 l_col_new_val := p_new.ATTRIBUTE25;
381 end if;
382 --
383 if l_column = 'ATTRIBUTE26' then
384 l_col_old_val := p_old.ATTRIBUTE26;
385 l_col_new_val := p_new.ATTRIBUTE26;
386 end if;
387 --
388 if l_column = 'ATTRIBUTE27' then
389 l_col_old_val := p_old.ATTRIBUTE27;
390 l_col_new_val := p_new.ATTRIBUTE27;
391 end if;
392 --
393 if l_column = 'ATTRIBUTE28' then
394 l_col_old_val := p_old.ATTRIBUTE28;
395 l_col_new_val := p_new.ATTRIBUTE28;
396 end if;
397 --
398 if l_column = 'ATTRIBUTE29' then
399 l_col_old_val := p_old.ATTRIBUTE29;
400 l_col_new_val := p_new.ATTRIBUTE29;
401 end if;
402 --
403 if l_column = 'ATTRIBUTE30' then
404 l_col_old_val := p_old.ATTRIBUTE30;
405 l_col_new_val := p_new.ATTRIBUTE30;
406 end if;
407 --
408
409 --
410 benutils.exec_rule(
411 p_formula_id => l_per_info_chg_cs_ler_rl,
412 p_effective_date => l_session_date,
413 p_lf_evt_ocrd_dt => null,
414 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
415 p_person_id => nvl(p_new.person_id, p_old.person_id),
416 p_new_value => l_col_new_val,
417 p_old_value => l_col_old_val,
418 p_column_name => l_column,
419
420 p_param5 => 'BEN_PPR_IN_PERFORMANCE_RATING',
421 p_param5_value => p_new.PERFORMANCE_RATING,
422 p_param6 => 'BEN_PPR_IO_PERFORMANCE_RATING',
423 p_param6_value => p_old.PERFORMANCE_RATING,
424
425 p_param7 => 'BEN_PPR_IN_EVENT_ID',
426 p_param7_value => p_new.EVENT_ID,
427 p_param8 => 'BEN_PPR_IO_EVENT_ID',
428 p_param8_value => p_old.EVENT_ID,
429
430 p_param9 => 'BEN_PPR_IN_REVIEW_DATE',
431 p_param9_value => to_char(p_new.REVIEW_DATE, 'YYYY/MM/DD HH24:MI:SS'),
432 p_param10 => 'BEN_PPR_IO_REVIEW_DATE',
433 p_param10_value => to_char(p_old.REVIEW_DATE, 'YYYY/MM/DD HH24:MI:SS'),
434
435 p_param11 => 'BEN_PPR_IN_ATTRIBUTE1',
436 p_param11_value => p_new.ATTRIBUTE1,
437 p_param12 => 'BEN_PPR_IO_ATTRIBUTE1',
438 p_param12_value => p_old.ATTRIBUTE1,
439
440 p_param13 => 'BEN_PPR_IN_ATTRIBUTE2',
441 p_param13_value => p_new.ATTRIBUTE2,
442 p_param14 => 'BEN_PPR_IO_ATTRIBUTE2',
443 p_param14_value => p_old.ATTRIBUTE2,
444
445 p_param15 => 'BEN_PPR_IN_ATTRIBUTE3',
446 p_param15_value => p_new.ATTRIBUTE3,
447 p_param16 => 'BEN_PPR_IO_ATTRIBUTE3',
448 p_param16_value => p_old.ATTRIBUTE3,
449
450 p_param17 => 'BEN_PPR_IN_ATTRIBUTE4',
454
451 p_param17_value => p_new.ATTRIBUTE4,
452 p_param18 => 'BEN_PPR_IO_ATTRIBUTE4',
453 p_param18_value => p_old.ATTRIBUTE4,
455 p_param19 => 'BEN_PPR_IN_ATTRIBUTE5',
456 p_param19_value => p_new.ATTRIBUTE5,
457 p_param20 => 'BEN_PPR_IO_ATTRIBUTE5',
458 p_param20_value => p_old.ATTRIBUTE5,
459
460 p_param21 => 'BEN_PPR_IN_ATTRIBUTE6',
461 p_param21_value => p_new.ATTRIBUTE6,
462 p_param22 => 'BEN_PPR_IO_ATTRIBUTE6',
463 p_param22_value => p_old.ATTRIBUTE6,
464
465 p_param23 => 'BEN_PPR_IN_ATTRIBUTE7',
466 p_param23_value => p_new.ATTRIBUTE7,
467 p_param24 => 'BEN_PPR_IO_ATTRIBUTE7',
468 p_param24_value => p_old.ATTRIBUTE7,
469
470 p_param25 => 'BEN_PPR_IN_ATTRIBUTE8',
471 p_param25_value => p_new.ATTRIBUTE8,
472 p_param26 => 'BEN_PPR_IO_ATTRIBUTE8',
473 p_param26_value => p_old.ATTRIBUTE8,
474
475 p_param27 => 'BEN_PPR_IN_ATTRIBUTE9',
476 p_param27_value => p_new.ATTRIBUTE9,
477 p_param28 => 'BEN_PPR_IO_ATTRIBUTE9',
478 p_param28_value => p_old.ATTRIBUTE9,
479
480 p_param29 => 'BEN_PPR_IN_ATTRIBUTE10',
481 p_param29_value => p_new.ATTRIBUTE10,
482 p_param30 => 'BEN_PPR_IO_ATTRIBUTE10',
483 p_param30_value => p_old.ATTRIBUTE10,
484
485 p_param31 => 'BEN_PPR_IN_ATTRIBUTE11',
486 p_param31_value => p_new.ATTRIBUTE11,
487 p_param32 => 'BEN_PPR_IO_ATTRIBUTE11',
488 p_param32_value => p_old.ATTRIBUTE11,
489
490 p_param33 => 'BEN_PPR_IN_ATTRIBUTE12',
491 p_param33_value => p_new.ATTRIBUTE12,
492 p_param34 => 'BEN_PPR_IO_ATTRIBUTE12',
493 p_param34_value => p_old.ATTRIBUTE12,
494
495
496
497 p_pk_id => to_char(p_new.performance_review_id),
498 p_ret_val => l_rule_output);
499 --
500 end if;
501 --
502 hr_utility.set_location(' ben_ppr_trigger'|| ' l_rule_output = ' || l_rule_output, 9999);
503
504 --
505
506 if l_column = 'REVIEW_DATE' then
507 l_changed := (benutils.column_changed(p_old.review_date
508 ,p_new.review_date,l_new_val) AND
509 benutils.column_changed(p_new.review_date
510 ,p_old.review_date,l_old_val) AND
511 (l_changed));
512 end if;
513
514 --
515 if l_column = 'PERFORMANCE_RATING' then
516 l_changed := (benutils.column_changed(p_old.performance_rating
517 ,p_new.performance_rating,l_new_val) AND
518 benutils.column_changed(p_new.performance_rating
519 ,p_old.performance_rating,l_old_val) AND
520 (l_changed));
521 hr_utility.set_location('NEW DOB'||p_new.performance_rating, 50);
522 hr_utility.set_location('OLD DOB'||p_old.performance_rating, 50);
523 hr_utility.set_location(' ben_ppr_trigger', 60);
524 end if;
525 --
526 if l_column = 'EVENT_ID' then
527 --
528 -- Bug : 3692707
529 --
530 l_new_review_type := null;
531 l_old_review_type := null;
532 --
533 if l_new_val not in ('OABANY', 'NULL') then
534 --
535 open c_get_review_type(p_new.event_id);
536 fetch c_get_review_type into l_new_review_type;
537 close c_get_review_type;
538 --
539 end if;
540 --
541 if l_old_val not in ('OABANY', 'NULL') then
542 --
543 open c_get_review_type(p_old.event_id);
544 fetch c_get_review_type into l_old_review_type;
545 close c_get_review_type;
546 --
547 end if;
548 --
549 l_new_review_type := nvl(l_new_review_type, p_new.event_id);
550 l_old_review_type := nvl(l_old_review_type, p_old.event_id);
551 --
552 l_changed := (benutils.column_changed(l_old_review_type
553 ,l_new_review_type,l_new_val) AND
554 benutils.column_changed(l_new_review_type
555 ,l_old_review_type,l_old_val) AND
556 (l_changed));
557 --
558 -- Bug : 3692707
559 --
560 end if;
561
562 --
563 if l_column = 'ATTRIBUTE1' then
564 l_changed := (benutils.column_changed(p_old.attribute1
565 ,p_new.attribute1,l_new_val) AND
566 benutils.column_changed(p_new.attribute1
567 ,p_old.attribute1,l_old_val) AND
568 (l_changed));
569 hr_utility.set_location(' ben_ppr_trigger', 210);
570 hr_utility.set_location('NEW A1'||p_new.attribute1, 50);
571 hr_utility.set_location('OLD A1'||p_old.attribute1, 50);
572 end if;
576 ,p_new.attribute2,l_new_val) AND
573 --
574 if l_column = 'ATTRIBUTE2' then
575 l_changed := (benutils.column_changed(p_old.attribute2
577 benutils.column_changed(p_new.attribute2
578 ,p_old.attribute2,l_old_val) AND
579 (l_changed));
580 hr_utility.set_location(' ben_ppr_trigger', 220);
581 end if;
582 --
583 if l_column = 'ATTRIBUTE3' then
584 l_changed := (benutils.column_changed(p_old.attribute3
585 ,p_new.attribute3,l_new_val) AND
586 benutils.column_changed(p_new.attribute3
587 ,p_old.attribute3,l_old_val) AND
588 (l_changed));
589 hr_utility.set_location(' ben_ppr_trigger', 230);
590 end if;
591 --
592 if l_column = 'ATTRIBUTE4' then
593 l_changed := (benutils.column_changed(p_old.attribute4
594 ,p_new.attribute4,l_new_val) AND
595 benutils.column_changed(p_new.attribute4
596 ,p_old.attribute4,l_old_val) AND
597 (l_changed));
598 hr_utility.set_location(' ben_ppr_trigger', 240);
599 end if;
600 --
601 if l_column = 'ATTRIBUTE5' then
602 l_changed := (benutils.column_changed(p_old.attribute5
603 ,p_new.attribute5,l_new_val) AND
604 benutils.column_changed(p_new.attribute5
605 ,p_old.attribute5,l_old_val) AND
606 (l_changed));
607 hr_utility.set_location(' ben_ppr_trigger', 250);
608 end if;
609 --
610 if l_column = 'ATTRIBUTE6' then
611 l_changed := (benutils.column_changed(p_old.attribute6
612 ,p_new.attribute6,l_new_val) AND
613 benutils.column_changed(p_new.attribute6
614 ,p_old.attribute6,l_old_val) AND
615 (l_changed));
616 hr_utility.set_location(' ben_ppr_trigger', 260);
617 end if;
618 --
619 if l_column = 'ATTRIBUTE7' then
620 l_changed := (benutils.column_changed(p_old.attribute7
621 ,p_new.attribute7,l_new_val) AND
622 benutils.column_changed(p_new.attribute7
623 ,p_old.attribute7,l_old_val) AND
624 (l_changed));
625 hr_utility.set_location(' ben_ppr_trigger', 270);
626 end if;
627 --
628 if l_column = 'ATTRIBUTE8' then
629 l_changed := (benutils.column_changed(p_old.attribute8
630 ,p_new.attribute8,l_new_val) AND
631 benutils.column_changed(p_new.attribute8
632 ,p_old.attribute8,l_old_val) AND
633 (l_changed));
634 hr_utility.set_location(' ben_ppr_trigger', 280);
635 end if;
636 --
637 if l_column = 'ATTRIBUTE9' then
638 l_changed := (benutils.column_changed(p_old.attribute9
639 ,p_new.attribute9,l_new_val) AND
640 benutils.column_changed(p_new.attribute9
641 ,p_old.attribute9,l_old_val) AND
642 (l_changed));
643 hr_utility.set_location(' ben_ppr_trigger', 290);
644 end if;
645 --
646 if l_column = 'ATTRIBUTE10' then
647 l_changed := (benutils.column_changed(p_old.attribute10
648 ,p_new.attribute10,l_new_val) AND
649 benutils.column_changed(p_new.attribute10
650 ,p_old.attribute10,l_old_val) AND
651 (l_changed));
652 hr_utility.set_location(' ben_ppr_trigger', 300);
653 end if;
654 --
655 if l_column = 'ATTRIBUTE11' then
656 l_changed := (benutils.column_changed(p_old.attribute11
657 ,p_new.attribute11,l_new_val) AND
658 benutils.column_changed(p_new.attribute11
659 ,p_old.attribute11,l_old_val) AND
660 (l_changed));
661 hr_utility.set_location(' ben_ppr_trigger', 310);
662 end if;
663 --
664 if l_column = 'ATTRIBUTE12' then
665 l_changed := (benutils.column_changed(p_old.attribute12
666 ,p_new.attribute12,l_new_val) AND
667 benutils.column_changed(p_new.attribute12
668 ,p_old.attribute12,l_old_val) AND
669 (l_changed));
670 hr_utility.set_location(' ben_ppr_trigger', 320);
671 end if;
672 --
673 if l_column = 'ATTRIBUTE13' then
674 l_changed := (benutils.column_changed(p_old.attribute13
675 ,p_new.attribute13,l_new_val) AND
676 benutils.column_changed(p_new.attribute13
677 ,p_old.attribute13,l_old_val) AND
678 (l_changed));
679 hr_utility.set_location(' ben_ppr_trigger', 330);
683 l_changed := (benutils.column_changed(p_old.attribute14
680 end if;
681 --
682 if l_column = 'ATTRIBUTE14' then
684 ,p_new.attribute14,l_new_val) AND
685 benutils.column_changed(p_new.attribute14
686 ,p_old.attribute14,l_old_val) AND
687 (l_changed));
688 hr_utility.set_location(' ben_ppr_trigger', 340);
689 end if;
690 --
691 if l_column = 'ATTRIBUTE15' then
692 l_changed := (benutils.column_changed(p_old.attribute15
693 ,p_new.attribute15,l_new_val) AND
694 benutils.column_changed(p_new.attribute15
695 ,p_old.attribute15,l_old_val) AND
696 (l_changed));
697 hr_utility.set_location(' ben_ppr_trigger', 350);
698 end if;
699 --
700 if l_column = 'ATTRIBUTE16' then
701 l_changed := (benutils.column_changed(p_old.attribute16
702 ,p_new.attribute16,l_new_val) AND
703 benutils.column_changed(p_new.attribute16
704 ,p_old.attribute16,l_old_val) AND
705 (l_changed));
706 hr_utility.set_location(' ben_ppr_trigger', 360);
707 end if;
708 --
709 if l_column = 'ATTRIBUTE17' then
710 l_changed := (benutils.column_changed(p_old.attribute17
711 ,p_new.attribute17,l_new_val) AND
712 benutils.column_changed(p_new.attribute17
713 ,p_old.attribute17,l_old_val) AND
714 (l_changed));
715 hr_utility.set_location(' ben_ppr_trigger', 370);
716 end if;
717 --
718 if l_column = 'ATTRIBUTE18' then
719 l_changed := (benutils.column_changed(p_old.attribute18
720 ,p_new.attribute18,l_new_val) AND
721 benutils.column_changed(p_new.attribute18
722 ,p_old.attribute18,l_old_val) AND
723 (l_changed));
724 hr_utility.set_location(' ben_ppr_trigger', 380);
725 end if;
726 --
727 if l_column = 'ATTRIBUTE19' then
728 l_changed := (benutils.column_changed(p_old.attribute19
729 ,p_new.attribute19,l_new_val) AND
730 benutils.column_changed(p_new.attribute19
731 ,p_old.attribute19,l_old_val) AND
732 (l_changed));
733 hr_utility.set_location(' ben_ppr_trigger', 390);
734 end if;
735 --
736 if l_column = 'ATTRIBUTE20' then
737 l_changed := (benutils.column_changed(p_old.attribute20
738 ,p_new.attribute20,l_new_val) AND
739 benutils.column_changed(p_new.attribute20
740 ,p_old.attribute20,l_old_val) AND
741 (l_changed));
742 hr_utility.set_location(' ben_ppr_trigger', 400);
743 end if;
744 --
745
746 if l_column = 'ATTRIBUTE21' then
747 l_changed := (benutils.column_changed(p_old.attribute21
748 ,p_new.attribute21,l_new_val) AND
749 benutils.column_changed(p_new.attribute21
750 ,p_old.attribute21,l_old_val) AND
751 (l_changed));
752 end if;
753 --
754 if l_column = 'ATTRIBUTE22' then
755 l_changed := (benutils.column_changed(p_old.attribute22
756 ,p_new.attribute22,l_new_val) AND
757 benutils.column_changed(p_new.attribute22
758 ,p_old.attribute22,l_old_val) AND
759 (l_changed));
760 end if;
761 --
762 if l_column = 'ATTRIBUTE23' then
763 l_changed := (benutils.column_changed(p_old.attribute23
764 ,p_new.attribute23,l_new_val) AND
765 benutils.column_changed(p_new.attribute23
766 ,p_old.attribute23,l_old_val) AND
767 (l_changed));
768 end if;
769 --
770 if l_column = 'ATTRIBUTE24' then
771 l_changed := (benutils.column_changed(p_old.attribute24
772 ,p_new.attribute24,l_new_val) AND
773 benutils.column_changed(p_new.attribute24
774 ,p_old.attribute24,l_old_val) AND
775 (l_changed));
776 end if;
777 --
778 if l_column = 'ATTRIBUTE25' then
779 l_changed := (benutils.column_changed(p_old.attribute25
780 ,p_new.attribute25,l_new_val) AND
781 benutils.column_changed(p_new.attribute25
782 ,p_old.attribute25,l_old_val) AND
783 (l_changed));
784 end if;
785 --
789 benutils.column_changed(p_new.attribute26
786 if l_column = 'ATTRIBUTE26' then
787 l_changed := (benutils.column_changed(p_old.attribute26
788 ,p_new.attribute26,l_new_val) AND
790 ,p_old.attribute26,l_old_val) AND
791 (l_changed));
792 end if;
793 --
794 if l_column = 'ATTRIBUTE27' then
795 l_changed := (benutils.column_changed(p_old.attribute27
796 ,p_new.attribute27,l_new_val) AND
797 benutils.column_changed(p_new.attribute27
798 ,p_old.attribute27,l_old_val) AND
799 (l_changed));
800 end if;
801 --
802 if l_column = 'ATTRIBUTE28' then
803 l_changed := (benutils.column_changed(p_old.attribute28
804 ,p_new.attribute28,l_new_val) AND
805 benutils.column_changed(p_new.attribute28
806 ,p_old.attribute28,l_old_val) AND
807 (l_changed));
808 end if;
809 --
810 if l_column = 'ATTRIBUTE29' then
811 l_changed := (benutils.column_changed(p_old.attribute29
812 ,p_new.attribute29,l_new_val) AND
813 benutils.column_changed(p_new.attribute29
814 ,p_old.attribute29,l_old_val) AND
815 (l_changed));
816 end if;
817 --
818 if l_column = 'ATTRIBUTE30' then
819 l_changed := (benutils.column_changed(p_old.attribute30
820 ,p_new.attribute30,l_new_val) AND
821 benutils.column_changed(p_new.attribute30
822 ,p_old.attribute30,l_old_val) AND
823 (l_changed));
824 end if;
825 --
826
827 --
828 -- Checking the rule output and the rule override flag.
829 -- Whether the rule is mandatory or not, rule output should return 'Y'
830 -- Rule Mandatory flag is just to override the column data change.
831
832 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
833 l_changed := TRUE ;
834 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
835 l_changed := l_changed AND TRUE;
836 elsif l_rule_output = 'N' then
837 l_changed := FALSE;
838 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
839 end if;
840
841 hr_utility.set_location('After the rule Check ',20.05);
842 if l_changed then
843 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
844 else
845 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
846 end if;
847 -- Check for Column Mandatory Change
848 -- If column change is mandatory and data change has failed then dont trigger
849 -- If column change is non-mandatory and the data change has passed, then trigger.
850
851 if l_chg_mandatory_cd = 'Y' and not l_changed then
852 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
853 l_changed := FALSE;
854 l_trigger := FALSE;
855 exit;
856 elsif l_chg_mandatory_cd = 'Y' and l_changed then
857 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
858 l_changed := TRUE;
859 -- exit; */
860 elsif l_chg_mandatory_cd = 'N' and l_changed then
861 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
862 l_changed := TRUE;
863 l_trigger := TRUE;
864 exit;
865 end if;
866
867 hr_utility.set_location('After the Mandatory code check ',20.05);
868 if l_changed then
869 hr_utility.set_location(' l_change TRUE ', 20.1);
870 else
871 hr_utility.set_location(' l_change FALSE ', 20.1);
872 end if;
873 --
874 /* if not l_changed then
875 exit;
876 end if; */
877 end loop;
878 hr_utility.set_location(' ben_ppr_trigger', 30);
879 l_ptnl_id := 0;
880 l_ovn :=null;
881 if l_trigger then
882
883
884
885 if l_type = 'P' then
886 --
887 -- Life event has occured for Participant
888 --
889 hr_utility.set_location('l_lf_evt_ocrd_date is '||l_lf_evt_ocrd_date, 987);
890 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
891 fetch le_exists into l_le_exists;
892 --
893 -- If an already existing life event of this
894 -- type exists do nothing.
895 --
896
897 if le_exists%notfound then
898 hr_utility.set_location(' Entering: ben_ppr_trigger5', 60);
899 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
900 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
901 (p_validate => false
902 ,p_ptnl_ler_for_per_id => l_ptnl_id
903 ,p_ntfn_dt => trunc(sysdate)
904 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
905 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
906 ,p_ler_id => l_ler_id
907 ,p_ler_typ_cd => l_typ_cd
908 ,p_person_id => p_new.person_id
909 ,p_business_group_Id => p_new.business_group_id
910 ,p_object_version_number => l_ovn
911 ,p_effective_date => l_effective_start_date
912 ,p_dtctd_dt => l_lf_evt_ocrd_date);
913 end if;
914 close le_exists;
915 elsif l_type = 'R' then
916 hr_utility.set_location(' Entering: ben_ppr_trigger5-rp', 65);
917 --
918 -- Related Life event has occured for Participant contacts
919 --
920 open get_contacts(p_new.person_id);
921 loop
922 fetch get_contacts into l_hld_person_id;
923 exit when get_contacts%notfound;
924 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
925 fetch le_exists into l_le_exists;
926 --
927 -- If potential life event does not already exist
928 -- create it.
929 if le_exists%notfound then
930 hr_utility.set_location(' Entering: ben_ppr_trigger5', 60);
931 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
932 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
933 (p_validate => false
934 ,p_ptnl_ler_for_per_id => l_ptnl_id
935 ,p_ntfn_dt => trunc(sysdate)
936 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
937 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
938 ,p_ler_id => l_ler_id
939 ,p_ler_typ_cd => l_typ_cd
940 ,p_person_id => l_hld_person_id
941 ,p_business_group_Id => p_new.business_group_id
942 ,p_object_version_number => l_ovn
943 ,p_effective_date => l_effective_start_date
944 ,p_dtctd_dt => l_lf_evt_ocrd_date);
945 end if;
946 l_ptnl_id := 0;
947 l_ovn :=null;
948 close le_exists;
949 end loop;
950 close get_contacts;
951 end if;
952 --
953 -- reset the variables.
954 --
955 hr_utility.set_location(' ben_ppr_trigger', 40);
956 l_changed := FALSE;
957 l_trigger := TRUE;
958 l_ovn := NULL;
959
960 end if;
961 close get_ler_col;
962 end loop;
963 hr_utility.set_location(' ben_ppr_trigger', 180);
964 close get_ler;
965 hr_utility.set_location(' Leaving: ben_ppr_trigger', 200);
966
967 end;
968 end ben_ppr_ler;