[Home] [Help]
PACKAGE BODY: APPS.BEN_ABS_LER
Source
1 package body ben_abs_ler as
2 /* $Header: beabatrg.pkb 120.2.12010000.2 2008/08/05 13:52:57 ubhat ship $*/
3
4 g_param_tab ff_exec.outputs_t;
5
6 function decode_reason(p_abs_attendance_reason_id in number) return varchar2 is
7 --
8 cursor c1 is
9 select name
10 from per_abs_attendance_reasons
11 where abs_attendance_reason_id = p_abs_attendance_reason_id;
12 --
13 l_reason varchar2(80);
14 --
15 begin
16 --
17 open c1;
18 fetch c1 into l_reason;
19 close c1;
20 --
21 return l_reason;
22 --
23 end decode_reason;
24
25 procedure populate_param_tab
26 (p_name in varchar2,
27 p_value in varchar2) is
28 l_next_index number;
29 begin
30
31 l_next_index := nvl(g_param_tab.count,0) + 1;
32 g_param_tab(l_next_index).name := p_name;
33 g_param_tab(l_next_index).value := p_value;
34
35 end;
36 --
37 procedure gb_abs_life_event_rule
38 (p_absence_event_type in varchar2
39 ,p_new_date_start in varchar2
40 ,p_old_date_start in varchar2
41 ,p_new_date_end in varchar2
42 ,p_old_date_end in varchar2
43 ,p_new_abs_attendance_type_id in varchar2
44 ,p_old_abs_attendance_type_id in varchar2
45 ,p_new_abs_information1 in varchar2
46 ,p_old_abs_information1 in varchar2
47 ,p_new_abs_information2 in varchar2
48 ,p_old_abs_information2 in varchar2
49 ,p_new_abs_information4 in varchar2
50 ,p_old_abs_information4 in varchar2
51 ,p_new_abs_information5 in varchar2
52 ,p_old_abs_information5 in varchar2
53 ,p_new_abs_information6 in varchar2
54 ,p_old_abs_information6 in varchar2
55 ,p_ret_val out nocopy varchar2) is
56 --Declare local variables
57 l_default VARCHAR2(25) := '_DEFAULT_';
58 l_look_up_check VARCHAR2(80) := ' ';
59 l_ret_val VARCHAR2(1) :='N';
60 --
61 l_proc_name Constant Varchar2(150) :='gb_abs_life_event_rule';
62 --
63 begin
64 --
65 hr_utility.set_location('Entering: ' || l_proc_name, 5);
66 hr_utility.set_location('Absence event type: ' || p_absence_event_type, 5);
67 --
68 if p_absence_event_type = 'START' then
69 --
70 --Check that there is a difference between the old and new values of
71 --start date.
72
73 --
74 hr_utility.set_location('Absence Event Type = START', 10);
75 l_look_up_check := HR_GENERAL.DECODE_LOOKUP('PQP_GAP_ABSENCE_TYPES_LIST',p_new_abs_attendance_type_id);
76 --
77 if l_look_up_check is not null then
78 --
79 if p_old_date_start <> p_new_date_start and p_new_abs_attendance_type_id <> l_default then
80 --
81 --Set the return flag to "Y"es to allow this person change to cause an
82 --"absence start" life event.
83 --NOTE the default for the flag is "N"o.
84 --
85 l_ret_val := 'Y';
86 --
87 end if;
88
89 --End Absence Start Date Section
90 hr_utility.set_location('End Absence Start Date Section:' || l_ret_val, 20);
91 --
92 --Begin Absence Information 1 - Start Date Fraction Section
93 --Check that there is a difference between the old and new values of
94 --ABS_INFORMATION1.
95
96 --
97 if l_ret_val = 'N' then
98 --
99 if p_old_abs_information1 <> p_new_abs_information1 and p_new_abs_attendance_type_id <> l_default then
100 --
101 l_ret_val := 'Y';
102 --
103 end if;
104 --
105 end if;
106
107 --End Absence Information 1
108 hr_utility.set_location('End of Absence Information 1 Section:' || l_ret_val, 30);
109 --
110 --Begin Absence Information 2 - End Date Fraction Section
111 --Check that there is a difference between the old and new values of
112 --ABS_INFORMATION2.
113
114 --
115 if l_ret_val = 'N' then
116 --
117 if p_old_abs_information2 <> p_new_abs_information2 and p_new_abs_attendance_type_id <> l_default then
118 --
119 l_ret_val := 'Y';
120 --
121 end if;
122 --
123 end if;
124
125 --End Absence Information 2
126 hr_utility.set_location('End of Absence Information 2 Section:' || l_ret_val, 40);
127
128
129 --
130 --Check that there is a difference between the old and new values of
131 --INFORMATION4(Pension rate start date) .
132
133 --
134 if l_ret_val = 'N' then
135 --
136 if p_new_abs_information4 <> p_old_abs_information4 and p_new_abs_attendance_type_id <> l_default then
137 --
138 l_ret_val := 'Y';
139 --
140 end if;
141 --
142 end if;
143
144 --End Absence Information 4 i.e. Pension start date
145 hr_utility.set_location('End of Pension Rate start date section:' || l_ret_val, 50);
146
147 --Check that there is a difference between the old and new values of
148 --INFORMATION5(Pension rate end date) .
149
150 --
151 if l_ret_val = 'N' then
152 --
153 if p_new_abs_information5 <> p_old_abs_information5 and p_new_abs_attendance_type_id <> l_default then
154 --
155 l_ret_val := 'Y';
156 --
157 end if;
158 --
159 end if;
160
161 --End Absence Information 5 i.e. Pension end date
162 hr_utility.set_location('End of Pension Rate end date section:' || l_ret_val, 60);
163 --
164 --Check that there is a difference between the old and new values of
165 --INFORMATION6(Pension rate) .
166
167 --
168 if l_ret_val = 'N' then
169 --
170 if p_new_abs_information6 <> p_old_abs_information6 and p_new_abs_attendance_type_id <> l_default then
171 --
172 l_ret_val := 'Y';
173 --
174 end if;
175 --
176 end if;
177
178 --End Absence Information 2 - End Date Fraction Section
179 hr_utility.set_location('End of Pension Rate section:' || l_ret_val, 70);
180 --
181 end if; --End for checking lookup exist
182 --
183 elsif p_absence_event_type = 'END' then
184 --
185 --Check that there is a difference between the old and new values of
186 --End Date.
187 --NOTE When an absence is deleted, the absence_type_id new value will be
188 --defaulted. To record an absence delete a seperate life event reason has
189 --to be setup. To prevent a absence start change from being logged on a
190 --delete, an additional check is introduced to ensure that new value is not
191 --equal to the default.
192 --
193 hr_utility.set_location('Absence Event Type = END', 80);
194 l_look_up_check := HR_GENERAL.DECODE_LOOKUP('PQP_GAP_ABSENCE_TYPES_LIST',p_new_abs_attendance_type_id);
195 --
196 if l_look_up_check is not null then
197 --(
198 if p_old_date_end <> p_new_date_end and p_new_abs_attendance_type_id <> l_default then
199 --
200 --Set the return flag to "Y"es to allow this person change to cause an
201 --"absence end" life event.
202 --NOTE the default for the flag is "N"o.
203 --
204 l_ret_val := 'Y';
205 --
206 end if;
207 --
208 end if;
209 --
210 elsif p_absence_event_type = 'DELETE' then
211 --
212 --Check that the new value of absence type id has been defaulted.
213 --NOTE When an absence is deleted, the absence_type_id new value will be
214 --defaulted. To record an absence delete a seperate life event reason has
215 --to be setup. To prevent a absence start change from being logged on a
216 --delete, an additional check is introduced to ensure that new value is not
217 --equal to the default.
218 --
219 hr_utility.set_location('Absence Event Type = DELETE', 90);
220 l_look_up_check := HR_GENERAL.DECODE_LOOKUP('PQP_GAP_ABSENCE_TYPES_LIST',p_old_abs_attendance_type_id);
221
222 if l_look_up_check is not null then
223 --
224 if p_old_abs_attendance_type_id <> l_default and p_new_abs_attendance_type_id = l_default then
225 --
226 --Set the return flag to "Y"es to allow this person change to cause an
227 --"absence delete" life event.
228 --NOTE the default for the flag is "N"o.
229 --
230 l_ret_val := 'Y';
231 --
232 end if;
233 --
234 end if;
235 --
236 end if;
237 --
238 p_ret_val := l_ret_val;
239 hr_utility.set_location('Leaving: ' || l_proc_name, 100);
240 --
241 end gb_abs_life_event_rule;
242 --
243 procedure ler_chk(p_old IN g_abs_ler_rec
244 ,p_new IN g_abs_ler_rec
245 ,p_effective_date in date default null ) is
246 --
247 l_session_date DATE;
248 l_system_date DATE;
249 l_status VARCHAR2(1) := 'N';
250 --
251 cursor get_session_date IS
252 select effective_date
253 from fnd_sessions
254 where session_id = userenv('SESSIONID');
255 --
256 cursor get_system_date IS
257 select trunc(sysdate)
258 from dual;
259 --
260 cursor get_ler is
261 select ler.ler_id,
262 ler.ocrd_dt_det_cd,
263 -- ABSENCES
264 ler.typ_cd,
265 ler.lf_evt_oper_cd
266 from ben_ler_f ler
267 where ler.business_group_id = nvl(p_new.business_group_id,p_old.business_group_id)
268 and l_session_date
269 between ler.effective_start_date
270 and ler.effective_end_date
271 --
272 -- ABSENCES : for OSB customers trigger the absence
273 -- life events.
274 --
275 and (l_status = 'I' or ler.typ_cd in ( 'ABS','CHECKLIST' ))
276 and ((exists
277 (select 1
278 from ben_per_info_chg_cs_ler_f psl,
279 ben_ler_per_info_cs_ler_f lpl
280 where source_table = 'PER_ABSENCE_ATTENDANCES'
281 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
282 and lpl.business_group_id = psl.business_group_id
283 and lpl.business_group_id = ler.business_group_id
284 and l_session_date between psl.effective_start_date
285 and psl.effective_end_date
286 and l_session_date
287 between lpl.effective_start_date
288 and lpl.effective_end_date
289 and lpl.ler_id = ler.ler_id)
290 )
291 or (exists
292 (select 1
293 from ben_rltd_per_chg_cs_ler_f rpc,
294 ben_ler_rltd_per_cs_ler_f lrp
295 where source_table = 'PER_ABSENCE_ATTENDANCES'
296 and lrp.business_group_id = rpc.business_group_id
297 and lrp.business_group_id = ler.business_group_id
298 and l_session_date
299 between rpc.effective_start_date
300 and rpc.effective_end_date
301 and l_session_date
302 between lrp.effective_start_date
303 and lrp.effective_end_date
304 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
305 and lrp.ler_id = ler.ler_id)
306 ))
307 order by ler.ler_id;
308 --
309 cursor get_ler_col(p_ler_id IN NUMBER) is
310 select psl.source_column,
311 psl.new_val,
312 psl.old_val,
313 'P',
314 psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, nvl(lpl.chg_mandatory_cd,'Y')
315 from ben_ler_per_info_cs_ler_f lpl,
316 ben_per_info_chg_cs_ler_f psl
317 where lpl.ler_id = p_ler_id
318 and lpl.business_group_id = nvl(p_new.business_group_id,p_old.business_group_id)
319 and lpl.business_group_id = psl.business_group_id
320 and l_session_date
321 between psl.effective_start_date
322 and psl.effective_end_date
323 and l_session_date
324 between lpl.effective_start_date
325 and lpl.effective_end_date
326 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
327 and source_table = 'PER_ABSENCE_ATTENDANCES'
328 union
329 select rpc.source_column,
330 rpc.new_val,
331 rpc.old_val,
332 'R',
333 rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, nvl(lrp.chg_mandatory_cd,'Y')
334 from ben_ler_rltd_per_cs_ler_f lrp,
335 ben_rltd_per_chg_cs_ler_f rpc
336 where lrp.ler_id = p_ler_id
337 and lrp.business_group_id = nvl(p_new.business_group_id,p_old.business_group_id)
338 and lrp.business_group_id = rpc.business_group_id
339 and l_session_date
340 between rpc.effective_start_date
341 and rpc.effective_end_date
342 and l_session_date
343 between lrp.effective_start_date
344 and lrp.effective_end_date
345 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
346 and source_table = 'PER_ABSENCE_ATTENDANCES'
347 order by 1;
348 --
349 cursor le_exists(p_person_id in number
350 ,p_ler_id in number
351 ,p_lf_evt_ocrd_dt in date
352 ,p_trgr_table_pk_id in number) is
353 select 'Y'
354 from ben_ptnl_ler_for_per
355 where person_id = p_person_id
356 and ler_id = p_ler_id
357 and ptnl_ler_for_per_stat_cd = 'DTCTD'
358 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
359 and nvl(trgr_table_pk_id,-1) = nvl(p_trgr_table_pk_id,-1);
360 --
361 cursor get_contacts(p_person_id in number) is
362 select person_id
363 from per_contact_relationships
364 where contact_person_id = p_person_id
365 and business_group_id = nvl(p_new.business_group_id,p_old.business_group_id)
366 and l_session_date
367 between nvl(date_start,l_session_date)
368 and nvl(date_end,l_session_date)
369 and personal_flag = 'Y'
370 order by person_id;
371 --
372 l_changed BOOLEAN;
373 l_ler_id NUMBER;
374 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
375 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
376 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
377 l_ocrd_dt_cd VARCHAR2(30);
378 l_bool boolean;
379 -- ABSENCES
380 l_typ_cd VARCHAR2(30);
381 l_per_info_chg_cs_ler_rl number;
382 l_rule_output VARCHAR2(1);
383 --
384 /* l_absences_rule_output VARCHAR2(1) := 'N'; */
385 -- Commented during Multiple Table LE Triggering enhancement
386
387 --
391 l_effective_start_date DATE ;
388 l_ovn NUMBER;
389 l_ptnl_id NUMBER;
390 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
392 l_lf_evt_ocrd_date DATE ;
393 l_le_exists VARCHAR2(1);
394 l_mnl_dt date;
395 l_dtctd_dt date;
396 l_procd_dt date;
397 l_unprocd_dt date;
398 l_voidd_dt date;
399 l_type VARCHAR2(1);
400 l_hld_person_id NUMBER;
401 l_date_start date;
402 l_date_end date;
403 l_industry VARCHAR2(1);
404 l_col_new_val VARCHAR2(1000);
405 l_col_old_val VARCHAR2(1000);
406 l_lf_evt_oper_cd VARCHAR2(30);
407 l_trgr_table_pk_id number;
408 l_effective_date date;
409 --
410 --
411 l_rule_overrides_flag VARCHAR2(1);
412 l_chg_mandatory_cd VARCHAR2(1);
413 l_trigger boolean := TRUE;
414 --
415 l_leg_code VARCHAR2(10);
416 --
417 begin
418 --
419 --hr_utility.trace_on(null, 'dax');
420 benutils.set_data_migrator_mode; -- Bug 3320133
421 --
422 if hr_general.g_data_migrator_mode in ( 'Y','P') then
423 --
424 return;
425 --
426 end if;
427 --
428 l_bool :=fnd_installation.get(appl_id => 805
429 ,dep_appl_id =>805
430 ,status => l_status
431 ,industry => l_industry);
432
433 hr_utility.set_location(' Entering: ben_abs_trigger', 10);
434 l_changed := FALSE;
435 --
436 If p_effective_date is not null then
437 --
438 l_session_date := p_effective_date ;
439 --
440 else
441 --
442 open get_session_date;
443 --
444 fetch get_session_date into l_session_date;
445 --
446 close get_session_date;
447 --
448 end if ;
449 --
450 -- 3096367
451 -- get the effective date to make it the lf evt ocrd dt, if actual and projected are null
452 --
453 open get_session_date;
454 --
455 fetch get_session_date into l_effective_date;
456 --
457 close get_session_date;
458
459 hr_utility.set_location('l_effective_date:'||l_effective_date, 20);
460
461 --
462 open get_system_date;
463 --
464 fetch get_system_date into l_system_date;
465 --
466 close get_system_date;
467 --
468 l_effective_start_date := l_session_date;
469 hr_utility.set_location(' ben_abs_trigger', 20);
470 --
471 --
472 hr_utility.set_location(' l_system_date:'||to_char(l_system_date), 20);
473 hr_utility.set_location(' l_session_date:'||to_char(l_session_date,'dd-mon-rr hh24:mi:ss'), 20);
474 hr_utility.set_location(' new abs id:'||to_char(p_new.absence_attendance_id), 20);
475 hr_utility.set_location(' old abs id:'||to_char(p_old.absence_attendance_id), 20);
476 --
477 open get_ler;
478 --
479 loop
480 --
481 fetch get_ler into l_ler_id, l_ocrd_dt_cd, l_typ_cd, l_lf_evt_oper_cd;
482 hr_utility.set_location(' count : '||to_char(get_ler%rowcount), 20);
483 exit when get_ler%notfound;
484 --
485 hr_utility.set_location(' lf evt oper cd: '||l_lf_evt_oper_cd, 20);
486 hr_utility.set_location('old start_date: '||to_char(p_old.date_start), 20);
487 hr_utility.set_location('new start_date: '||to_char(p_new.date_start), 20);
488 hr_utility.set_location('old end_date: '||to_char(p_old.date_end), 20);
489 hr_utility.set_location('new end_date: '||to_char(p_new.date_end), 20);
490 if l_typ_cd <> 'ABS' or
491 (l_typ_cd = 'ABS' and
492 -- This is to stop triggering START/END LEs in case of Delete
493 ((p_new.absence_attendance_id is null and
494 p_old.absence_attendance_id is not null and
495 l_lf_evt_oper_cd = 'DELETE') or
496 (p_new.absence_attendance_id is not null and
497 l_lf_evt_oper_cd in ('START','END'))
498 )
499 ) then
500 --
501 --l_absences_rule_output := 'N';
502 --
503 -- in some situations the date we use for occured on date is null,
504 -- use session date instead.
505 --
506 -- 3096367
507 -- nvl(actual start date, nvl(projected date, session date))
508
509 if l_typ_cd = 'ABS' then
510 l_date_start := nvl( nvl(nvl(p_new.date_start,p_old.date_start), p_new.date_projected_start)
511 ,l_effective_date);
512 l_date_end := nvl( nvl(nvl(p_new.date_end,p_old.date_end), p_new.date_projected_end)
513 ,l_effective_date);
514
515 else
516 l_date_start := nvl(p_new.date_start,l_session_date);
517 l_date_end := nvl(p_new.date_end,l_session_date);
518 end if;
519 --
520 hr_utility.set_location('l_date_start'||l_date_start, 30);
521 hr_utility.set_location('l_date_end'||l_date_end, 30);
522 --
523 if l_ocrd_dt_cd is null then
524 --
525 l_lf_evt_ocrd_date := l_date_start;
526 --
527 else
528 --
529 -- Call the common date procedure.
530 --
531 ben_determine_date.main
532 (p_date_cd => l_ocrd_dt_cd
533 ,p_effective_date => l_date_start
534 ,p_lf_evt_ocrd_dt => l_date_start
535 ,p_returned_date => l_lf_evt_ocrd_date);
536 --
537 end if;
541 open get_ler_col(l_ler_id);
538 --
539 l_changed := FALSE;
540 --
542 --
543 loop
544 --
545 fetch get_ler_col into l_column,
546 l_new_val,
547 l_old_val,
548 l_type,
549 l_per_info_chg_cs_ler_rl,l_rule_overrides_flag, l_chg_mandatory_cd;
550 exit when get_ler_col%NOTFOUND;
551 --
552 hr_utility.set_location(' l_column'||l_column, 50);
553 hr_utility.set_location(' l_new_val'||l_new_val, 50);
554 hr_utility.set_location(' l_old_val'||l_old_val, 50);
555 hr_utility.set_location(' l_type'||l_type, 50);
556 hr_utility.set_location(' l_ler_id'||l_ler_id, 50);
557 --
558 if get_ler_col%ROWCOUNT = 1 then
559 --
560 l_changed := TRUE;
561 --
562 end if;
563 --
564 hr_utility.set_location(' ben_abs_trigger', 51);
565 --
566 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
567 -- If it returns Y, then see the applicability of the data
568 -- changes based on new and old values.
569 --
570 l_rule_output := 'Y';
571 --
572 --If there is a rule attached, execute the rule
573 if l_per_info_chg_cs_ler_rl is not null then
574 --
575 if l_column = 'PERSON_ID' then
576 --
577 l_col_old_val := to_char(p_old.PERSON_ID);
578 l_col_new_val := to_char(p_new.PERSON_ID);
579 --
580 elsif l_column = 'DATE_START' then
581 --
582 l_col_old_val := to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
583 l_col_new_val := to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
584 --
585 elsif l_column = 'DATE_END' then
586 --
587 l_col_old_val := to_char(p_old.DATE_END, 'YYYY/MM/DD HH24:MI:SS');
588 l_col_new_val := to_char(p_new.DATE_END, 'YYYY/MM/DD HH24:MI:SS');
589 --
590 elsif l_column = 'ABSENCE_ATTENDANCE_TYPE_ID' then
591 --
592 l_col_old_val := to_char(p_old.ABSENCE_ATTENDANCE_TYPE_ID);
593 l_col_new_val := to_char(p_new.ABSENCE_ATTENDANCE_TYPE_ID);
594 --
595 elsif l_column = 'ABS_ATTENDANCE_REASON_ID' then
596 --
597 l_col_old_val := to_char(p_old.ABS_ATTENDANCE_REASON_ID);
598 l_col_new_val := to_char(p_new.ABS_ATTENDANCE_REASON_ID);
599 --
600 -- Added extra columns for ABSENCES processing.
601 --
602 elsif l_column = 'SICKNESS_START_DATE' then
603 --
604 l_col_old_val := to_char(p_old.SICKNESS_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
605 l_col_new_val := to_char(p_new.SICKNESS_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
606 --
607 elsif l_column = 'SICKNESS_END_DATE' then
608 --
609 l_col_old_val := to_char(p_old.SICKNESS_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
610 l_col_new_val := to_char(p_new.SICKNESS_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
611 --
612 elsif l_column = 'ABSENCE_DAYS' then
613 --
614 l_col_old_val := to_char(p_old.ABSENCE_DAYS);
615 l_col_new_val := to_char(p_new.ABSENCE_DAYS);
616 --
617 elsif l_column = 'ABSENCE_HOURS' then
618 --
619 l_col_old_val := to_char(p_old.ABSENCE_HOURS);
620 l_col_new_val := to_char(p_new.ABSENCE_HOURS);
621 --
622 elsif l_column = 'DATE_NOTIFICATION' then
623 --
624 l_col_old_val := to_char(p_old.DATE_NOTIFICATION, 'YYYY/MM/DD HH24:MI:SS');
625 l_col_new_val := to_char(p_new.DATE_NOTIFICATION, 'YYYY/MM/DD HH24:MI:SS');
626 --
627 elsif l_column = 'DATE_PROJECTED_END' then
628 --
629 l_col_old_val := to_char(p_old.DATE_PROJECTED_END, 'YYYY/MM/DD HH24:MI:SS');
630 l_col_new_val := to_char(p_new.DATE_PROJECTED_END, 'YYYY/MM/DD HH24:MI:SS');
631 --
632 elsif l_column = 'DATE_PROJECTED_START' then
633 --
634 l_col_old_val := to_char(p_old.DATE_PROJECTED_START, 'YYYY/MM/DD HH24:MI:SS');
635 l_col_new_val := to_char(p_new.DATE_PROJECTED_START, 'YYYY/MM/DD HH24:MI:SS');
636 --
637 elsif l_column = 'TIME_END' then
638 --
639 l_col_old_val := p_old.TIME_END;
640 l_col_new_val := p_new.TIME_END;
641 --
642 elsif l_column = 'TIME_PROJECTED_END' then
643 --
644 l_col_old_val := p_old.TIME_PROJECTED_END;
645 l_col_new_val := p_new.TIME_PROJECTED_END;
646 --
647 elsif l_column = 'TIME_PROJECTED_START' then
648 --
649 l_col_old_val := p_old.TIME_PROJECTED_START;
650 l_col_new_val := p_new.TIME_PROJECTED_START;
651 --
652 elsif l_column = 'TIME_START' then
656 --
653 --
654 l_col_old_val := p_old.TIME_START;
655 l_col_new_val := p_new.TIME_START;
657 elsif l_column = 'PREGNANCY_RELATED_ILLNESS' then
658 --
659 l_col_old_val := p_old.PREGNANCY_RELATED_ILLNESS;
660 l_col_new_val := p_new.PREGNANCY_RELATED_ILLNESS;
661 --
662 elsif l_column = 'REASON_FOR_NOTIFICATION_DELAY' then
663 --
664 l_col_old_val := p_old.REASON_FOR_NOTIFICATION_DELAY;
665 l_col_new_val := p_new.REASON_FOR_NOTIFICATION_DELAY;
666 --
667 elsif l_column = 'SSP1_ISSUED' then
668 --
669 l_col_old_val := p_old.SSP1_ISSUED;
670 l_col_new_val := p_new.SSP1_ISSUED;
671 --
672 elsif l_column = 'PERIOD_OF_INCAPACITY_ID' then
673 --
674 l_col_old_val := to_char(p_old.PERIOD_OF_INCAPACITY_ID);
675 l_col_new_val := to_char(p_new.PERIOD_OF_INCAPACITY_ID);
676 --
677 elsif l_column = 'ACCEPT_LATE_NOTIFICATION_FLAG' then
678 --
679 l_col_old_val := p_old.ACCEPT_LATE_NOTIFICATION_FLAG;
680 l_col_new_val := p_new.ACCEPT_LATE_NOTIFICATION_FLAG;
681 --
682 elsif l_column = 'LINKED_ABSENCE_ID' then
683 --
684 l_col_old_val := to_char(p_old.LINKED_ABSENCE_ID);
685 l_col_new_val := to_char(p_new.LINKED_ABSENCE_ID);
686 --
687 elsif l_column = 'MATERNITY_ID' then
688 --
689 l_col_old_val := to_char(p_old.MATERNITY_ID);
690 l_col_new_val := to_char(p_new.MATERNITY_ID);
691 --
692 elsif l_column = 'OCCURRENCE' then
693 --
694 l_col_old_val := to_char(p_old.OCCURRENCE);
695 l_col_new_val := to_char(p_new.OCCURRENCE);
696 --
697 end if;
698 --
699 populate_param_tab( 'BEN_ABA_IN_PERSON_ID',to_char(p_new.PERSON_ID));
700 populate_param_tab('BEN_ABA_IN_PERSON_ID',to_char(p_new.PERSON_ID));
701 populate_param_tab('BEN_ABA_IO_PERSON_ID',to_char(p_old.PERSON_ID));
702 populate_param_tab('BEN_ABA_IN_DATE_START', to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS'));
703 populate_param_tab('BEN_ABA_IO_DATE_START', to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS'));
704 populate_param_tab('BEN_ABA_IN_DATE_END', to_char(p_new.DATE_END, 'YYYY/MM/DD HH24:MI:SS'));
705 populate_param_tab('BEN_ABA_IO_DATE_END', to_char(p_old.DATE_END, 'YYYY/MM/DD HH24:MI:SS'));
706 populate_param_tab('BEN_ABA_IN_ABSENCE_ATTENDANCE_TYPE_ID',
707 to_char(p_new.ABSENCE_ATTENDANCE_TYPE_ID));
708 populate_param_tab('BEN_ABA_IO_ABSENCE_ATTENDANCE_TYPE_ID',
709 to_char(p_old.ABSENCE_ATTENDANCE_TYPE_ID));
710 populate_param_tab('BEN_ABA_IN_ABS_ATTENDANCE_REASON_ID',
711 to_char(p_new.ABS_ATTENDANCE_REASON_ID));
712 populate_param_tab('BEN_ABA_IO_ABS_ATTENDANCE_REASON_ID',
713 to_char(p_old.ABS_ATTENDANCE_REASON_ID));
714 populate_param_tab('BEN_ABA_IN_SICKNESS_START_DATE',
715 to_char(p_new.SICKNESS_START_DATE, 'YYYY/MM/DD HH24:MI:SS'));
716 populate_param_tab('BEN_ABA_IO_SICKNESS_START_DATE',
717 to_char(p_old.SICKNESS_START_DATE, 'YYYY/MM/DD HH24:MI:SS'));
718 populate_param_tab('BEN_ABA_IN_SICKNESS_END_DATE',
719 to_char(p_new.SICKNESS_END_DATE, 'YYYY/MM/DD HH24:MI:SS'));
720 populate_param_tab('BEN_ABA_IO_SICKNESS_END_DATE',
721 to_char(p_old.SICKNESS_END_DATE, 'YYYY/MM/DD HH24:MI:SS'));
722 populate_param_tab('BEN_ABA_IN_ABSENCE_DAYS', to_char(p_new.ABSENCE_DAYS));
723 populate_param_tab('BEN_ABA_IO_ABSENCE_DAYS', to_char(p_old.ABSENCE_DAYS));
724 populate_param_tab('BEN_ABA_IN_ABSENCE_HOURS', to_char(p_new.ABSENCE_HOURS));
725 populate_param_tab('BEN_ABA_IO_ABSENCE_HOURS', to_char(p_old.ABSENCE_HOURS));
726 populate_param_tab('BEN_ABA_IN_DATE_NOTIFICATION',
727 to_char(p_new.DATE_NOTIFICATION, 'YYYY/MM/DD HH24:MI:SS'));
728 populate_param_tab('BEN_ABA_IO_DATE_NOTIFICATION',
729 to_char(p_old.DATE_NOTIFICATION, 'YYYY/MM/DD HH24:MI:SS'));
730 populate_param_tab('BEN_ABA_IN_DATE_PROJECTED_END',
731 to_char(p_new.DATE_PROJECTED_END, 'YYYY/MM/DD HH24:MI:SS'));
732 populate_param_tab('BEN_ABA_IO_DATE_PROJECTED_END',
733 to_char(p_old.DATE_PROJECTED_END, 'YYYY/MM/DD HH24:MI:SS'));
734 populate_param_tab('BEN_ABA_IN_DATE_PROJECTED_START',
735 to_char(p_new.DATE_PROJECTED_START, 'YYYY/MM/DD HH24:MI:SS'));
736 populate_param_tab('BEN_ABA_IO_DATE_PROJECTED_START',
737 to_char(p_old.DATE_PROJECTED_START, 'YYYY/MM/DD HH24:MI:SS'));
738 populate_param_tab('BEN_ABA_IN_TIME_END', p_new.TIME_END);
739 populate_param_tab('BEN_ABA_IO_TIME_END', p_old.TIME_END);
740 populate_param_tab('BEN_ABA_IN_TIME_PROJECTED_END', p_new.TIME_PROJECTED_END);
741 populate_param_tab('BEN_ABA_IO_TIME_PROJECTED_END', p_old.TIME_PROJECTED_END);
742 populate_param_tab('BEN_ABA_IN_TIME_PROJECTED_START', p_new.TIME_PROJECTED_START);
746 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION1', p_new.abs_information1);
743 populate_param_tab('BEN_ABA_IO_TIME_PROJECTED_START', p_old.TIME_PROJECTED_START);
744 populate_param_tab('BEN_LER_ID', to_char(l_ler_id));
745 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION_CATEGORY', p_new.abs_information_category);
747 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION2', p_new.abs_information2);
748 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION3', p_new.abs_information3);
749 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION4', p_new.abs_information4);
750 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION5', p_new.abs_information5);
751 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION6', p_new.abs_information6);
752 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION7', p_new.abs_information7);
753 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION8', p_new.abs_information8);
754 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION9', p_new.abs_information9);
755 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION10', p_new.abs_information10);
756 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION11', p_new.abs_information11);
757 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION12', p_new.abs_information12);
758 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION13', p_new.abs_information13);
759 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION14', p_new.abs_information14);
760 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION15', p_new.abs_information15);
761 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION16', p_new.abs_information16);
762 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION17', p_new.abs_information17);
763 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION18', p_new.abs_information18);
764 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION19', p_new.abs_information19);
765 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION20', p_new.abs_information20);
766 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION21', p_new.abs_information21);
767 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION22', p_new.abs_information22);
768 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION23', p_new.abs_information23);
769 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION24', p_new.abs_information24);
770 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION25', p_new.abs_information25);
771 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION26', p_new.abs_information26);
772 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION27', p_new.abs_information27);
773 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION28', p_new.abs_information28);
774 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION29', p_new.abs_information29);
775 populate_param_tab('BEN_ABA_IN_ABS_INFORMATION30', p_new.abs_information30);
776 populate_param_tab('BEN_ABA_IN_ATTRIBUTE_CATEGORY', p_new.attribute_category);
777 populate_param_tab('BEN_ABA_IN_ATTRIBUTE1', p_new.attribute1);
778 populate_param_tab('BEN_ABA_IN_ATTRIBUTE2', p_new.attribute2);
779 populate_param_tab('BEN_ABA_IN_ATTRIBUTE3', p_new.attribute3);
780 populate_param_tab('BEN_ABA_IN_ATTRIBUTE4', p_new.attribute4);
781 populate_param_tab('BEN_ABA_IN_ATTRIBUTE5', p_new.attribute5);
782 populate_param_tab('BEN_ABA_IN_ATTRIBUTE6', p_new.attribute6);
783 populate_param_tab('BEN_ABA_IN_ATTRIBUTE7', p_new.attribute7);
784 populate_param_tab('BEN_ABA_IN_ATTRIBUTE8', p_new.attribute8);
785 populate_param_tab('BEN_ABA_IN_ATTRIBUTE9', p_new.attribute9);
786 populate_param_tab('BEN_ABA_IN_ATTRIBUTE10', p_new.attribute10);
787 populate_param_tab('BEN_ABA_IN_ATTRIBUTE11', p_new.attribute11);
788 populate_param_tab('BEN_ABA_IN_ATTRIBUTE12', p_new.attribute12);
789 populate_param_tab('BEN_ABA_IN_ATTRIBUTE13', p_new.attribute13);
790 populate_param_tab('BEN_ABA_IN_ATTRIBUTE14', p_new.attribute14);
791 populate_param_tab('BEN_ABA_IN_ATTRIBUTE15', p_new.attribute15);
792 populate_param_tab('BEN_ABA_IN_ATTRIBUTE16', p_new.attribute16);
793 populate_param_tab('BEN_ABA_IN_ATTRIBUTE17', p_new.attribute17);
794 populate_param_tab('BEN_ABA_IN_ATTRIBUTE18', p_new.attribute18);
795 populate_param_tab('BEN_ABA_IN_ATTRIBUTE19', p_new.attribute19);
796 populate_param_tab('BEN_ABA_IN_ATTRIBUTE20', p_new.attribute20);
797 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION_CATEGORY', p_old.abs_information_category);
798 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION1', p_old.abs_information1);
799 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION2', p_old.abs_information2);
800 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION3', p_old.abs_information3);
801 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION4', p_old.abs_information4);
802 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION5', p_old.abs_information5);
803 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION6', p_old.abs_information6);
804 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION7', p_old.abs_information7);
805 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION8', p_old.abs_information8);
806 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION9', p_old.abs_information9);
807 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION10', p_old.abs_information10);
808 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION11', p_old.abs_information11);
812 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION15', p_old.abs_information15);
809 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION12', p_old.abs_information12);
810 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION13', p_old.abs_information13);
811 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION14', p_old.abs_information14);
813 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION16', p_old.abs_information16);
814 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION17', p_old.abs_information17);
815 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION18', p_old.abs_information18);
816 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION19', p_old.abs_information19);
817 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION20', p_old.abs_information20);
818 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION21', p_old.abs_information21);
819 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION22', p_old.abs_information22);
820 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION23', p_old.abs_information23);
821 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION24', p_old.abs_information24);
822 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION25', p_old.abs_information25);
823 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION26', p_old.abs_information26);
824 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION27', p_old.abs_information27);
825 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION28', p_old.abs_information28);
826 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION29', p_old.abs_information29);
827 populate_param_tab('BEN_ABA_IO_ABS_INFORMATION30', p_old.abs_information30);
828 populate_param_tab('BEN_ABA_IO_ATTRIBUTE_CATEGORY', p_old.attribute_category);
829 populate_param_tab('BEN_ABA_IO_ATTRIBUTE1', p_old.attribute1);
830 populate_param_tab('BEN_ABA_IO_ATTRIBUTE2', p_old.attribute2);
831 populate_param_tab('BEN_ABA_IO_ATTRIBUTE3', p_old.attribute3);
832 populate_param_tab('BEN_ABA_IO_ATTRIBUTE4', p_old.attribute4);
833 populate_param_tab('BEN_ABA_IO_ATTRIBUTE5', p_old.attribute5);
834 populate_param_tab('BEN_ABA_IO_ATTRIBUTE6', p_old.attribute6);
835 populate_param_tab('BEN_ABA_IO_ATTRIBUTE7', p_old.attribute7);
836 populate_param_tab('BEN_ABA_IO_ATTRIBUTE8', p_old.attribute8);
837 populate_param_tab('BEN_ABA_IO_ATTRIBUTE9', p_old.attribute9);
838 populate_param_tab('BEN_ABA_IO_ATTRIBUTE10', p_old.attribute10);
839 populate_param_tab('BEN_ABA_IO_ATTRIBUTE11', p_old.attribute11);
840 populate_param_tab('BEN_ABA_IO_ATTRIBUTE12', p_old.attribute12);
841 populate_param_tab('BEN_ABA_IO_ATTRIBUTE13', p_old.attribute13);
842 populate_param_tab('BEN_ABA_IO_ATTRIBUTE14', p_old.attribute14);
843 populate_param_tab('BEN_ABA_IO_ATTRIBUTE15', p_old.attribute15);
844 populate_param_tab('BEN_ABA_IO_ATTRIBUTE16', p_old.attribute16);
845 populate_param_tab('BEN_ABA_IO_ATTRIBUTE17', p_old.attribute17);
846 populate_param_tab('BEN_ABA_IO_ATTRIBUTE18', p_old.attribute18);
847 populate_param_tab('BEN_ABA_IO_ATTRIBUTE19', p_old.attribute19);
848 populate_param_tab('BEN_ABA_IO_ATTRIBUTE20', p_old.attribute20);
849
850 benutils.exec_rule
851 (p_formula_id => l_per_info_chg_cs_ler_rl,
852 p_effective_date => l_session_date,
853 p_lf_evt_ocrd_dt => null,
854 p_business_group_id => nvl(p_new.business_group_id,p_old.business_group_id),
855 p_person_id => nvl(p_new.person_id, p_old.person_id),
856 p_new_value => l_col_new_val,
857 p_old_value => l_col_old_val,
858 p_column_name => l_column,
859 p_ret_val => l_rule_output,
860 p_param_tab => g_param_tab,
861 p_pk_id => to_char(nvl(p_new.absence_attendance_id,p_old.absence_attendance_id)));
862 g_param_tab.delete;
863 --
864 --If there is no rule attached, proceed with normal processing
865 else
866 --
867 l_leg_code := hr_api.return_legislation_code(nvl(p_new.business_group_id,p_old.business_group_id));
868 if l_leg_code = 'GB' then
869 --
870 gb_abs_life_event_rule
871 (p_absence_event_type => l_lf_evt_oper_cd,
872 p_new_date_start => nvl(to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS'), '12/31/4712'),
873 p_old_date_start => nvl(to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS'), '12/31/4712'),
874 p_new_date_end => nvl(to_char(p_new.DATE_END, 'YYYY/MM/DD HH24:MI:SS'), '12/31/4712'),
875 p_old_date_end => nvl(to_char(p_old.DATE_END, 'YYYY/MM/DD HH24:MI:SS'), '12/31/4712'),
876 p_new_abs_attendance_type_id => nvl(to_char(p_new.ABSENCE_ATTENDANCE_TYPE_ID), '_DEFAULT_'),
877 p_old_abs_attendance_type_id => nvl(to_char(p_old.ABSENCE_ATTENDANCE_TYPE_ID), '_DEFAULT_'),
878 p_new_abs_information1 => nvl(p_new.abs_information1, '_DEFAULT_'),
879 p_old_abs_information1 => nvl(p_old.abs_information1, '_DEFAULT_'),
880 p_new_abs_information2 => nvl(p_new.abs_information2, '_DEFAULT_'),
884 p_new_abs_information5 => nvl(p_new.abs_information5, '12/31/4712'),
881 p_old_abs_information2 => nvl(p_old.abs_information2, '_DEFAULT_'),
882 p_new_abs_information4 => nvl(p_new.abs_information4, '12/31/4712'),
883 p_old_abs_information4 => nvl(p_old.abs_information4, '12/31/4712'),
885 p_old_abs_information5 => nvl(p_old.abs_information5, '12/31/4712'),
886 p_new_abs_information6 => nvl(p_new.abs_information6, '_DEFAULT_'),
887 p_old_abs_information6 => nvl(p_old.abs_information6, '_DEFAULT_'),
888 p_ret_val => l_rule_output
889 );
890 --
891 end if;
892 --
893 end if;
894 --
895 -- ABSENCES
896 -- If the rule returns Y then override the columns data changed or not
897 -- for absences. Also attch the profile value check.
898 --
899 -- if l_absences_rule_output = 'N' and l_rule_output = 'Y' and l_typ_cd = 'ABS'
900 -- then
901 -- l_absences_rule_output := l_rule_output;
902 --
903 -- end if;
904 -- Commented during Multiple Table LE Triggering enhancement
905 --
906 if l_rule_output = 'Y' then
907 --
908 if l_column = 'ABSENCE_ATTENDANCE_TYPE_ID' then
909 --
910 l_changed :=
911 (benutils.column_changed
912 (p_old.absence_attendance_type_id,
913 p_new.absence_attendance_type_id,
914 l_new_val) and
915 benutils.column_changed
916 (p_new.absence_attendance_type_id,
917 p_old.absence_attendance_type_id,
918 l_old_val) and
919 (l_changed));
920 --
921 end if;
922 --
923 if l_column = 'ABS_ATTENDANCE_REASON_ID' then
924 --
925 -- We need to decode the absence_attendance_reasons back
926 -- to lookup values.
927 --
928 l_changed :=
929 (benutils.column_changed
930 (decode_reason(p_old.abs_attendance_reason_id),
931 decode_reason(p_new.abs_attendance_reason_id),
932 l_new_val) and
933 benutils.column_changed
934 (decode_reason(p_new.abs_attendance_reason_id),
935 decode_reason(p_old.abs_attendance_reason_id),
936 l_old_val) and (l_changed));
937 --
938 end if;
939 --
940 if l_column = 'DATE_START' then
941 --
942 hr_utility.set_location(' Inside Date start ben_abs_trigger', 52);
943 l_changed :=
944 (benutils.column_changed
945 (p_old.date_start,
946 p_new.date_start,
947 l_new_val) and
948 benutils.column_changed
949 (p_new.date_start,
950 p_old.date_start,
951 l_old_val) and (l_changed))or -- added here for bug 6895974
952 (benutils.column_changed
953 (p_old.abs_information1,
954 p_new.abs_information1,
955 l_new_val) and
956 benutils.column_changed
957 (p_new.abs_information1,
958 p_old.abs_information1,
959 l_old_val) and (l_changed))or
960 (benutils.column_changed
961 (p_old.abs_information2,
962 p_new.abs_information2,
963 l_new_val) and
964 benutils.column_changed
965 (p_new.abs_information2,
966 p_old.abs_information2,
967 l_old_val) and (l_changed))or
968 (benutils.column_changed
969 (p_old.abs_information4,
970 p_new.abs_information4,
971 l_new_val) and
972 benutils.column_changed
973 (p_new.abs_information4,
974 p_old.abs_information4,
975 l_old_val) and (l_changed))or
976 (benutils.column_changed
977 (p_old.abs_information5,
978 p_new.abs_information5,
979 l_new_val) and
980 benutils.column_changed
981 (p_new.abs_information5,
982 p_old.abs_information5,
983 l_old_val) and (l_changed))or
984 (benutils.column_changed
985 (p_old.abs_information6,
986 p_new.abs_information6,
987 l_new_val) and
988 benutils.column_changed
989 (p_new.abs_information6,
990 p_old.abs_information6,
991 l_old_val) and (l_changed)); -- added till here for bug 6895974
992 --
993 end if;
994 --
998 (benutils.column_changed
995 if l_column = 'DATE_END' then
996 --
997 l_changed :=
999 (p_old.date_end,
1000 p_new.date_end,
1001 l_new_val) and
1002 benutils.column_changed
1003 (p_new.date_end,
1004 p_old.date_end,
1005 l_old_val) and (l_changed));
1006 --
1007 if l_ocrd_dt_cd is null then
1008 --
1009 l_lf_evt_ocrd_date := l_date_end;
1010 --
1011 else
1012 --
1013 -- Call the common date procedure.
1014 --
1015 ben_determine_date.main
1016 (p_date_cd => l_ocrd_dt_cd,
1017 p_effective_date => l_date_end,
1018 p_lf_evt_ocrd_dt => l_date_end,
1019 p_returned_date => l_lf_evt_ocrd_date);
1020 --
1021 end if;
1022 --
1023 end if;
1024 --
1025 /* elsif l_rule_output = 'N' then
1026 --
1027 l_changed := FALSE;
1028
1029 -- Commented during Multiple Table LE Triggering enhancement
1030 */
1031 --
1032 end if;
1033 --
1034 hr_utility.set_location(' ben_abs_trigger', 30);
1035 --
1036 l_ptnl_id := 0;
1037 l_ovn :=null;
1038
1039 --
1040 -- Checking the rule output and the rule override flag.
1041 -- Whether the rule is mandatory or not, rule output should return 'Y'
1042 -- Rule Mandatory flag is just to override the column data change.
1043
1044
1045 hr_utility.set_location('l_rule_output = '|| l_rule_output, 20.01);
1046
1047
1048 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
1049 l_changed := TRUE ;
1050 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
1051 l_changed := l_changed AND TRUE;
1052 elsif l_rule_output = 'N' then
1053 l_changed := FALSE;
1054 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
1055 end if;
1056
1057 hr_utility.set_location('After the rule Check ',20.05);
1058 if l_changed then
1059 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
1060 else
1061 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
1062 end if;
1063 -- Check for Column Mandatory Change
1064 -- If column change is mandatory and data change has failed then dont trigger
1065 -- If column change is non-mandatory and the data change has passed, then trigger.
1066
1067 if l_chg_mandatory_cd = 'Y' and not l_changed then
1068 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
1069 l_changed := FALSE;
1070 l_trigger := FALSE;
1071 exit;
1072 elsif l_chg_mandatory_cd = 'Y' and l_changed then
1073 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
1074 l_changed := TRUE;
1075 l_trigger := TRUE;
1076 -- exit; */
1077 elsif l_chg_mandatory_cd = 'N' and l_changed then
1078 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
1079 l_changed := TRUE;
1080 l_trigger := TRUE;
1081 exit;
1082 end if;
1083
1084 hr_utility.set_location('After the Mandatory code check ',20.05);
1085 if l_changed then
1086 hr_utility.set_location(' l_change TRUE ', 20.1);
1087 else
1088 hr_utility.set_location(' l_change FALSE ', 20.1);
1089 end if;
1090 --
1091
1092
1093
1094 --
1095 -- ABSENCES
1096 -- For absence life events even if a formula is returns Y then create
1097 -- life events also add profile
1098 --
1099 if l_trigger -- or (l_typ_cd = 'ABS' and l_absences_rule_output = 'Y')
1100 then
1101 --
1102 if l_type = 'P' then
1103 --
1104 l_trgr_table_pk_id := nvl(p_new.absence_attendance_id,p_old.absence_attendance_id);
1105 open le_exists(nvl(p_new.person_id,p_old.person_id),l_ler_id,l_lf_evt_ocrd_date,l_trgr_table_pk_id);
1106 --
1107 fetch le_exists into l_le_exists;
1108 --
1109 if le_exists%notfound then
1110 --
1111 hr_utility.set_location(' Entering: ben_abs_trigger5', 60);
1112 --
1113 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1114 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1115 (p_validate => false
1116 ,p_ptnl_ler_for_per_id => l_ptnl_id
1117 ,p_ntfn_dt => l_system_date
1118 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1119 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1120 ,p_ler_id => l_ler_id
1121 ,p_ler_typ_cd => l_typ_cd
1122 ,p_person_id => nvl(p_new.person_id,p_old.person_id)
1123 ,p_business_group_Id => nvl(p_new.business_group_id,p_old.business_group_id)
1124 -- ABSENCES
1125 ,p_trgr_table_pk_id => l_trgr_table_pk_id
1126 ,p_object_version_number => l_ovn
1127 ,p_effective_date => l_effective_start_date
1128 ,p_dtctd_dt => l_effective_start_date);
1129 --
1130 end if;
1131 --
1132 close le_exists;
1133 --
1134 elsif l_type = 'R' then
1135 --
1136 hr_utility.set_location(' Entering: ben_abs_trigger5-', 65);
1137 --
1138 open get_contacts(nvl(p_new.person_id,p_old.person_id));
1139 --
1140 loop
1141 --
1142 fetch get_contacts into l_hld_person_id;
1143 exit when get_contacts%notfound;
1144 --
1145 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date,null);
1146 --
1147 fetch le_exists into l_le_exists;
1148 --
1149 if le_exists%notfound then
1150 --
1151 hr_utility.set_location('Entering: ben_abs_trigger5', 60);
1152 --
1153 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1154 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1155 (p_validate => false
1156 ,p_ptnl_ler_for_per_id => l_ptnl_id
1157 ,p_ntfn_dt => l_system_date
1158 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1159 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1160 ,p_ler_id => l_ler_id
1161 ,p_ler_typ_cd => l_typ_cd
1162 ,p_person_id => l_hld_person_id
1163 ,p_business_group_Id => nvl(p_new.business_group_id,p_old.business_group_id)
1164 ,p_object_version_number => l_ovn
1165 ,p_effective_date => l_effective_start_date
1166 ,p_dtctd_dt => l_effective_start_date);
1167 --
1168 end if;
1169 --
1170 l_ptnl_id := 0;
1171 l_ovn :=null;
1172 --
1173 close le_exists;
1174 --
1175 end loop;
1176 --
1177 close get_contacts;
1178 --
1179 end if;
1180 --
1181 -- reset the variables.
1182 --
1183 hr_utility.set_location(' ben_abs_trigger', 40);
1184 --
1185 l_changed := FALSE;
1186 l_ovn := NULL;
1187 l_effective_start_date := l_session_date;
1188 -- l_lf_evt_ocrd_date := l_session_date;
1189 end if;
1190 --
1191 end loop;
1192 --
1193 close get_ler_col;
1194 --
1195 end if;
1196 --
1197 end loop;
1198 --
1199 hr_utility.set_location(' ben_abs_trigger', 50);
1200 --
1201 close get_ler;
1202 --
1203 hr_utility.set_location(' leaving ben_abs_trigger', 70);
1204 -- hr_utility.trace_off;
1205 end;
1206 --
1207 end ben_abs_ler;
1208