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