[Home] [Help]
PACKAGE BODY: APPS.BEN_CEL_LER
Source
1 package body ben_cel_ler as
2 /* $Header: beceltrg.pkb 120.1 2006/03/10 04:51:50 nhunur noship $*/
3 procedure ler_chk(p_old IN g_cel_ler_rec
4 ,p_new IN g_cel_ler_rec
5 ,p_effective_date in date ) is
6 --
7 l_session_date DATE;
8
9 --
10 cursor get_session_date IS
11 select effective_date
12 from fnd_sessions
13 where session_id = userenv('SESSIONID');
14 --
15 --
16 cursor get_ler(l_status varchar2) is
17 select ler.ler_id
18 , ler.typ_cd
19 , ler.ocrd_dt_det_cd
20 from ben_ler_f ler
21 where ler.business_group_id = p_new.business_group_id
22 and l_session_date between ler.effective_start_date
23 and ler.effective_end_date
24 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
25 and ((exists
26 (select 1
27 from ben_per_info_chg_cs_ler_f psl
28 , ben_ler_per_info_cs_ler_f lpl
29 where source_table = 'PER_COMPETENCE_ELEMENTS'
30 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
31 and lpl.business_group_id = psl.business_group_id
32 and lpl.business_group_id = ler.business_group_id
33 and l_session_date between psl.effective_start_date
34 and psl.effective_end_date
35 and l_session_date between lpl.effective_start_date
36 and lpl.effective_end_date -- For Bug 3299709
37 and lpl.ler_id = ler.ler_id)
38 )
39 OR (exists
40 (select 1
41 from ben_rltd_per_chg_cs_ler_f rpc
42 , ben_ler_rltd_per_cs_ler_f lrp
43 where source_table = 'PER_COMPETENCE_ELEMENTS'
44 and lrp.business_group_id = rpc.business_group_id
45 and lrp.business_group_id = ler.business_group_id
46 and l_session_date between rpc.effective_start_date
47 and rpc.effective_end_date
48 and l_session_date between lrp.effective_start_date
49 and lrp.effective_end_date -- For Bug 3299709
50 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
51 and lrp.ler_id = ler.ler_id)
52 ))
53 order by ler.ler_id;
54
55 cursor get_ler_col(p_ler_id IN NUMBER) is
56 select psl.source_column, psl.new_val, psl.old_val, 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
57 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
58 where lpl.ler_id = p_ler_id
59 and lpl.business_group_id = p_new.business_group_id
60 and lpl.business_group_id = psl.business_group_id
61 and l_session_date between psl.effective_start_date
62 and psl.effective_end_date
63 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
64 and source_table = 'PER_COMPETENCE_ELEMENTS'
65 UNION
66 select rpc.source_column, rpc.new_val, rpc.old_val, 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
67 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
68 where lrp.ler_id = p_ler_id and
69 lrp.business_group_id = p_new.business_group_id
70 and lrp.business_group_id = rpc.business_group_id
71 and l_session_date between rpc.effective_start_date
72 and rpc.effective_end_date
73 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
74 and source_table = 'PER_COMPETENCE_ELEMENTS'
75 order by 1;
76 --
77 cursor le_exists(p_person_id in number
78 ,p_ler_id in number
79 ,p_lf_evt_ocrd_dt in date) is
80 select 'Y'
81 from ben_ptnl_ler_for_per
82 where person_id = p_person_id
83 and ler_id = p_ler_id
84 and ptnl_ler_for_per_stat_cd = 'DTCTD'
85 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
86 --
87 cursor get_contacts(p_person_id in number) is
88 select person_id
89 from per_contact_relationships
90 where contact_person_id = p_person_id
91 and business_group_id = p_new.business_group_id
92 and l_session_date between nvl(date_start,l_session_date)
93 and nvl(date_end,l_session_date)
94 and personal_flag = 'Y'
95 order by person_id;
96
97 cursor get_person_date(p_person_id in number, p_eff_date in date) is
98 select effective_start_date
99 from per_all_people_f
100 where person_id = p_person_id
101 and business_group_id = p_new.business_group_id
102 and p_eff_date between effective_start_date and effective_end_date;
103
104 --
105 l_changed BOOLEAN;
106 l_ler_id NUMBER;
107 l_typ_cd ben_ler_f.typ_cd%type ;
108 l_ocrd_dt_cd VARCHAR2(30);
109 l_column ben_rltd_per_chg_cs_ler_f.source_column%type;
110 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type;
111 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type;
112 l_per_info_chg_cs_ler_rl number;
113 l_rule_output VARCHAR2(1);
114 l_ovn NUMBER;
115 l_ptnl_id NUMBER;
116 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
117 l_effective_start_date DATE ;
118 l_person_esd DATE := null;
119 l_lf_evt_ocrd_date DATE ;
120 l_le_exists VARCHAR2(1);
121 l_mnl_dt date;
122 l_dtctd_dt date;
123 l_procd_dt date;
124 l_unprocd_dt date;
125 l_voidd_dt date;
126 l_type VARCHAR2(1);
127 l_hld_person_id NUMBER;
128 --
129 l_bool BOOLEAN;
130 l_status VARCHAR2(1);
131 l_industry VARCHAR2(1);
132 l_col_new_val VARCHAR2(1000);
133 l_col_old_val varchar2(1000);
134 --
135 l_rule_overrides_flag VARCHAR2(1);
136 l_chg_mandatory_cd VARCHAR2(1);
137 l_trigger boolean := TRUE;
138 --
139 l_effective_date date;
140 --
141 -- bug 2862886
142
143 l_person_date date;
144 l_greatest_date date;
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_cel_ler', 10);
165 l_changed := FALSE;
166 --
167 if p_effective_date is not null then
168 --
169 l_session_date := p_effective_date ;
170 --
171 else
172 --
173 open get_session_date;
174 fetch get_session_date into l_session_date;
175 close get_session_date;
176 --
177 end if;
178 --
179 --
180 l_effective_start_date := l_session_date; --for Detected Date
181 hr_utility.set_location('l_effective_start_date' || to_char(l_effective_start_date,'dd-mon-yyyy') , 10);
182 --
183 --
184 -- bug 2862886
185 -- the lf evt occurred date cannot be less than the date the person rec was created.
186 open get_person_date(p_new.person_id, l_session_date);
187 fetch get_person_date into l_person_date;
188 close get_person_date;
189
190 hr_utility.set_location('l_person_date' || to_char(l_person_date,'dd-mon-yyyy') , 10);
191 hr_utility.set_location('p_new.effective_date_from' || to_char(p_new.effective_date_from,'dd-mon-yyyy') , 10);
192
193 l_greatest_date := greatest(l_person_date, nvl(p_new.effective_date_from, l_effective_start_date) );
194 l_effective_start_date := greatest ( l_effective_start_date, l_person_date);
195 hr_utility.set_location(' greatest dt '||l_greatest_date, 987);
196 hr_utility.set_location(' l_effective_start_date '|| l_effective_start_date, 987);
197 --
198
199
200 hr_utility.set_location('opening get_ler',30);
201 hr_utility.set_location('l sess date is '||l_session_date,9876);
202
203 open get_ler(l_status);
204 loop
205 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
206 exit when get_ler%notfound;
207 l_trigger := TRUE;
208 --
209 if l_ocrd_dt_cd is null then
210 --
211 --bug 2862886
212 -- life event occured date is the greater of the person's esd and the start date entered
213 --l_lf_evt_ocrd_date := p_new.effective_date_from;
214 l_lf_evt_ocrd_date := l_greatest_date;
215 --
216 else
217 --
218 -- Call the common date procedure.
219 --
220 ben_determine_date.main
221 (p_date_cd => l_ocrd_dt_cd
222 ,p_effective_date => l_greatest_date /*p_new.effective_date_from*/
223 ,p_lf_evt_ocrd_dt => p_new.effective_date_from
224 ,p_returned_date => l_lf_evt_ocrd_date
225 );
226 end if;
227
228 hr_utility.set_location('Life Event Occured date is '||l_lf_evt_ocrd_date,30);
229 hr_utility.set_location('l_session_date:'||to_char(l_session_date), 30);
230 hr_utility.set_location('l_effective_start_date: '||to_char(l_effective_start_date),30);
231
232 --
233 open get_ler_col(l_ler_id);
234 loop
235 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;
236 exit when get_ler_col%NOTFOUND;
237 if get_ler_col%ROWCOUNT =1 then
238 l_changed := TRUE;
239 end if;
240 l_changed := TRUE;
241 hr_utility.set_location(' ben_cel_ler', 50);
242 hr_utility.set_location('LER '||l_ler_id, 20);
243 hr_utility.set_location('COLUMN '||l_column, 20);
244 hr_utility.set_location('NEWVAL '||l_new_val, 20);
245 hr_utility.set_location('OLDVAL '||l_old_val, 20);
246 hr_utility.set_location('TYPE '||l_type, 20);
247 --
248 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
249 -- If it returns Y, then see the applicability of the data
250 -- changes based on new and old values.
251 --
252 l_rule_output := 'Y';
253 --
254 if l_per_info_chg_cs_ler_rl is not null then
255 --
256 if l_column = 'COMPETENCE_ID' then
257 l_col_old_val := to_char(p_old.COMPETENCE_ID);
258 l_col_new_val := to_char(p_new.COMPETENCE_ID);
259 end if;
260 --
261 if l_column = 'PROFICIENCY_LEVEL_ID' then
262 l_col_old_val := to_char(p_old.PROFICIENCY_LEVEL_ID);
263 l_col_new_val := to_char(p_new.PROFICIENCY_LEVEL_ID);
264 end if;
265 --
266 if l_column = 'EFFECTIVE_DATE_FROM' then
267 l_col_old_val := to_char(p_old.EFFECTIVE_DATE_FROM, 'YYYY/MM/DD HH24:MI:SS');
268 l_col_new_val := to_char(p_new.EFFECTIVE_DATE_FROM, 'YYYY/MM/DD HH24:MI:SS');
269 end if;
270 --
271 if l_column = 'EFFECTIVE_DATE_TO' then
272 l_col_old_val := to_char(p_old.EFFECTIVE_DATE_TO, 'YYYY/MM/DD HH24:MI:SS');
273 l_col_new_val := to_char(p_new.EFFECTIVE_DATE_TO, 'YYYY/MM/DD HH24:MI:SS');
274 end if;
275 --
276 if l_column = 'ATTRIBUTE1' then
277 l_col_old_val := p_old.ATTRIBUTE1;
278 l_col_new_val := p_new.ATTRIBUTE1;
279 end if;
280 --
281 if l_column = 'ATTRIBUTE2' then
282 l_col_old_val := p_old.ATTRIBUTE2;
283 l_col_new_val := p_new.ATTRIBUTE2;
284 end if;
285 --
286 if l_column = 'ATTRIBUTE3' then
287 l_col_old_val := p_old.ATTRIBUTE3;
288 l_col_new_val := p_new.ATTRIBUTE3;
289 end if;
290 --
291 if l_column = 'ATTRIBUTE4' then
292 l_col_old_val := p_old.ATTRIBUTE4;
293 l_col_new_val := p_new.ATTRIBUTE4;
294 end if;
295 --
296 if l_column = 'ATTRIBUTE5' then
297 l_col_old_val := p_old.ATTRIBUTE5;
298 l_col_new_val := p_new.ATTRIBUTE5;
299 end if;
300 --
301 if l_column = 'ATTRIBUTE6' then
302 l_col_old_val := p_old.ATTRIBUTE6;
303 l_col_new_val := p_new.ATTRIBUTE6;
304 end if;
305 --
306 if l_column = 'ATTRIBUTE7' then
307 l_col_old_val := p_old.ATTRIBUTE7;
308 l_col_new_val := p_new.ATTRIBUTE7;
309 end if;
310 --
311 if l_column = 'ATTRIBUTE8' then
312 l_col_old_val := p_old.ATTRIBUTE8;
313 l_col_new_val := p_new.ATTRIBUTE8;
314 end if;
315 --
316 if l_column = 'ATTRIBUTE9' then
317 l_col_old_val := p_old.ATTRIBUTE9;
318 l_col_new_val := p_new.ATTRIBUTE9;
319 end if;
320 --
321 if l_column = 'ATTRIBUTE10' then
322 l_col_old_val := p_old.ATTRIBUTE10;
323 l_col_new_val := p_new.ATTRIBUTE10;
324 end if;
325 --
326 if l_column = 'ATTRIBUTE11' then
327 l_col_old_val := p_old.ATTRIBUTE11;
328 l_col_new_val := p_new.ATTRIBUTE11;
329 end if;
330 --
331 if l_column = 'ATTRIBUTE12' then
332 l_col_old_val := p_old.ATTRIBUTE12;
333 l_col_new_val := p_new.ATTRIBUTE12;
334 end if;
335 --
336 if l_column = 'ATTRIBUTE13' then
337 l_col_old_val := p_old.ATTRIBUTE13;
338 l_col_new_val := p_new.ATTRIBUTE13;
339 end if;
340 --
341 if l_column = 'ATTRIBUTE14' then
342 l_col_old_val := p_old.ATTRIBUTE14;
343 l_col_new_val := p_new.ATTRIBUTE14;
344 end if;
345 --
346 if l_column = 'ATTRIBUTE15' then
347 l_col_old_val := p_old.ATTRIBUTE15;
348 l_col_new_val := p_new.ATTRIBUTE15;
349 end if;
350 --
351 if l_column = 'ATTRIBUTE16' then
352 l_col_old_val := p_old.ATTRIBUTE16;
353 l_col_new_val := p_new.ATTRIBUTE16;
354 end if;
355 --
356 if l_column = 'ATTRIBUTE17' then
357 l_col_old_val := p_old.ATTRIBUTE17;
358 l_col_new_val := p_new.ATTRIBUTE17;
359 end if;
360 --
361 if l_column = 'ATTRIBUTE18' then
362 l_col_old_val := p_old.ATTRIBUTE18;
363 l_col_new_val := p_new.ATTRIBUTE18;
364 end if;
365 --
366 if l_column = 'ATTRIBUTE19' then
367 l_col_old_val := p_old.ATTRIBUTE19;
368 l_col_new_val := p_new.ATTRIBUTE19;
369 end if;
370 --
371 if l_column = 'ATTRIBUTE20' then
372 l_col_old_val := p_old.ATTRIBUTE20;
373 l_col_new_val := p_new.ATTRIBUTE20;
374 end if;
375 --
376 benutils.exec_rule(
377 p_formula_id => l_per_info_chg_cs_ler_rl,
378 p_effective_date => l_session_date,
379 p_lf_evt_ocrd_dt => null,
380 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
381 p_person_id => nvl(p_new.person_id, p_old.person_id),
382 p_new_value => l_col_new_val,
383 p_old_value => l_col_old_val,
384 p_column_name => l_column,
385 p_param5 => 'BEN_CEL_IN_EFFECTIVE_DATE_FROM',
386 p_param5_value => to_char(p_new.EFFECTIVE_DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
387 p_param6 => 'BEN_CEL_IO_EFFECTIVE_DATE_FROM',
388 p_param6_value => to_char(p_old.EFFECTIVE_DATE_FROM, 'YYYY/MM/DD HH24:MI:SS'),
389 p_param7 => 'BEN_CEL_IN_EFFECTIVE_DATE_TO',
390 p_param7_value => to_char(p_new.EFFECTIVE_DATE_TO, 'YYYY/MM/DD HH24:MI:SS'),
391 p_param8 => 'BEN_CEL_IO_EFFECTIVE_DATE_TO',
392 p_param8_value => to_char(p_old.EFFECTIVE_DATE_TO, 'YYYY/MM/DD HH24:MI:SS'),
393 p_param9 => 'BEN_CEL_IN_COMPETENCE_ID',
394 p_param9_value => to_char(p_new.COMPETENCE_ID),
395 p_param10 => 'BEN_CEL_IO_COMPETENCE_ID',
396 p_param10_value => to_char(p_old.COMPETENCE_ID),
397 p_param11 => 'BEN_CEL_IN_PROFICIENCY_LEVEL_ID',
398 p_param11_value => to_char(p_new.PROFICIENCY_LEVEL_ID),
399 p_param12 => 'BEN_CEL_IO_PROFICIENCY_LEVEL_ID',
400 p_param12_value => to_char(p_old.PROFICIENCY_LEVEL_ID),
401
402 p_param13 => 'BEN_CEL_IN_ATTRIBUTE1',
403 p_param13_value => p_new.ATTRIBUTE1,
404 p_param14 => 'BEN_CEL_IO_ATTRIBUTE1',
405 p_param14_value => p_old.ATTRIBUTE1,
406
407 p_param15 => 'BEN_CEL_IN_ATTRIBUTE2',
408 p_param15_value => p_new.ATTRIBUTE2,
409 p_param16 => 'BEN_CEL_IO_ATTRIBUTE2',
410 p_param16_value => p_old.ATTRIBUTE2,
411
412 p_param17 => 'BEN_CEL_IN_ATTRIBUTE3',
413 p_param17_value => p_new.ATTRIBUTE3,
414 p_param18 => 'BEN_CEL_IO_ATTRIBUTE3',
415 p_param18_value => p_old.ATTRIBUTE3,
416
417 p_param19 => 'BEN_CEL_IN_ATTRIBUTE4',
418 p_param19_value => p_new.ATTRIBUTE4,
419 p_param20 => 'BEN_CEL_IO_ATTRIBUTE4',
420 p_param20_value => p_old.ATTRIBUTE4,
421
425 p_param22_value => p_old.ATTRIBUTE5,
422 p_param21 => 'BEN_CEL_IN_ATTRIBUTE5',
423 p_param21_value => p_new.ATTRIBUTE5,
424 p_param22 => 'BEN_CEL_IO_ATTRIBUTE5',
426
427 p_param23 => 'BEN_CEL_IN_ATTRIBUTE6',
428 p_param23_value => p_new.ATTRIBUTE6,
429 p_param24 => 'BEN_CEL_IO_ATTRIBUTE6',
430 p_param24_value => p_old.ATTRIBUTE6,
431
432 p_param25 => 'BEN_CEL_IN_ATTRIBUTE7',
433 p_param25_value => p_new.ATTRIBUTE7,
434 p_param26 => 'BEN_CEL_IO_ATTRIBUTE7',
435 p_param26_value => p_old.ATTRIBUTE7,
436
437 p_param27 => 'BEN_CEL_IN_ATTRIBUTE8',
438 p_param27_value => p_new.ATTRIBUTE8,
439 p_param28 => 'BEN_CEL_IO_ATTRIBUTE8',
440 p_param28_value => p_old.ATTRIBUTE8,
441
442 p_param29 => 'BEN_CEL_IN_ATTRIBUTE9',
443 p_param29_value => p_new.ATTRIBUTE9,
444 p_param30 => 'BEN_CEL_IO_ATTRIBUTE9',
445 p_param30_value => p_old.ATTRIBUTE9,
446
447 p_param31 => 'BEN_CEL_IN_ATTRIBUTE10',
448 p_param31_value => p_new.ATTRIBUTE10,
449 p_param32 => 'BEN_CEL_IO_ATTRIBUTE10',
450 p_param32_value => p_old.ATTRIBUTE10,
451
452 p_param33 => 'BEN_CEL_IN_ATTRIBUTE11',
453 p_param33_value => p_new.ATTRIBUTE11,
454 p_param34 => 'BEN_CEL_IO_ATTRIBUTE11',
455 p_param34_value => p_old.ATTRIBUTE11,
456
457
458 p_pk_id => to_char(p_new.COMPETENCE_ELEMENT_ID),
459 p_ret_val => l_rule_output);
460 --
461 end if;
462 --
463
464 --
465 if l_column = 'COMPETENCE_ID' then
466 hr_utility.set_location('p_old.COMPETENCE_ID '||p_old.COMPETENCE_ID, 111);
467 hr_utility.set_location('p_new.COMPETENCE_ID '||p_new.COMPETENCE_ID, 222);
468
469 l_changed := (benutils.column_changed(p_old.COMPETENCE_ID
470 ,p_new.COMPETENCE_ID,l_new_val) AND
471 benutils.column_changed(p_new.COMPETENCE_ID
472 ,p_old.COMPETENCE_ID,l_old_val) AND
473 (l_changed));
474 end if;
475 --
476 if l_column = 'PROFICIENCY_LEVEL_ID' then
477 hr_utility.set_location('p_old.PROFICIENCY_LEVEL_ID '||p_old.PROFICIENCY_LEVEL_ID, 111);
478 hr_utility.set_location('p_new.PROFICIENCY_LEVEL_ID '||p_new.PROFICIENCY_LEVEL_ID, 222);
479 l_changed := (benutils.column_changed(p_old.PROFICIENCY_LEVEL_ID
480 ,p_new.PROFICIENCY_LEVEL_ID,l_new_val) AND
481 benutils.column_changed(p_new.PROFICIENCY_LEVEL_ID
482 ,p_old.PROFICIENCY_LEVEL_ID,l_old_val) AND
483 (l_changed));
484 end if;
485 --
486
487 if l_column = 'EFFECTIVE_DATE_FROM' then
488 hr_utility.set_location('p_old.EFFECTIVE_DATE_FROM '||p_old.EFFECTIVE_DATE_FROM, 111);
489 hr_utility.set_location('p_new.EFFECTIVE_DATE_FROM '||p_new.EFFECTIVE_DATE_FROM, 222);
490
491 l_changed := (benutils.column_changed(p_old.EFFECTIVE_DATE_FROM
492 ,p_new.EFFECTIVE_DATE_FROM,l_new_val) AND
493 benutils.column_changed(p_new.EFFECTIVE_DATE_FROM
494 ,p_old.EFFECTIVE_DATE_FROM,l_old_val) AND
495 (l_changed));
496 end if;
497 --
498 if l_column = 'EFFECTIVE_DATE_TO' then
499 hr_utility.set_location('p_old.EFFECTIVE_DATE_TO '||p_old.EFFECTIVE_DATE_TO, 111);
500 hr_utility.set_location('p_new.EFFECTIVE_DATE_TO '||p_new.EFFECTIVE_DATE_TO, 222);
501
502
503 l_changed := (benutils.column_changed(p_old.EFFECTIVE_DATE_TO
504 ,p_new.EFFECTIVE_DATE_TO,l_new_val) AND
505 benutils.column_changed(p_new.EFFECTIVE_DATE_TO
506 ,p_old.EFFECTIVE_DATE_TO,l_old_val) AND
507 (l_changed));
508
509 if l_ocrd_dt_cd is null then
510 l_lf_evt_ocrd_date := nvl(p_new.EFFECTIVE_DATE_TO,p_new.EFFECTIVE_DATE_FROM);
511 else
512 --
513 -- Call the common date procedure.
514 --
515 ben_determine_date.main
516 (p_date_cd => l_ocrd_dt_cd
517 ,p_effective_date => nvl(p_new.EFFECTIVE_DATE_TO,p_new.EFFECTIVE_DATE_FROM)
518 ,p_lf_evt_ocrd_dt => nvl(p_new.EFFECTIVE_DATE_TO,p_new.EFFECTIVE_DATE_FROM)
519 ,p_returned_date => l_lf_evt_ocrd_date
520 );
521 end if;
522 end if;
523 --
524 if l_column = 'ATTRIBUTE1' then
525 l_changed := (benutils.column_changed(p_old.attribute1
526 ,p_new.attribute1,l_new_val) AND
527 benutils.column_changed(p_new.attribute1
528 ,p_old.attribute1,l_old_val) AND
532 if l_column = 'ATTRIBUTE2' then
529 (l_changed));
530 end if;
531 --
533 l_changed := (benutils.column_changed(p_old.attribute2
534 ,p_new.attribute2,l_new_val) AND
535 benutils.column_changed(p_new.attribute2
536 ,p_old.attribute2,l_old_val) AND
537 (l_changed));
538 end if;
539 --
540 if l_column = 'ATTRIBUTE3' then
541 l_changed := (benutils.column_changed(p_old.attribute3
542 ,p_new.attribute3,l_new_val) AND
543 benutils.column_changed(p_new.attribute3
544 ,p_old.attribute3,l_old_val) AND
545 (l_changed));
546 end if;
547 --
548 if l_column = 'ATTRIBUTE4' then
549 l_changed := (benutils.column_changed(p_old.attribute4
550 ,p_new.attribute4,l_new_val) AND
551 benutils.column_changed(p_new.attribute4
552 ,p_old.attribute4,l_old_val) AND
553 (l_changed));
554 end if;
555 --
556 if l_column = 'ATTRIBUTE5' then
557 l_changed := (benutils.column_changed(p_old.attribute5
558 ,p_new.attribute5,l_new_val) AND
559 benutils.column_changed(p_new.attribute5
560 ,p_old.attribute5,l_old_val) AND
561 (l_changed));
562 end if;
563 --
564 if l_column = 'ATTRIBUTE6' then
565 l_changed := (benutils.column_changed(p_old.attribute6
566 ,p_new.attribute6,l_new_val) AND
567 benutils.column_changed(p_new.attribute6
568 ,p_old.attribute6,l_old_val) AND
569 (l_changed));
570 end if;
571 --
572 if l_column = 'ATTRIBUTE7' then
573 l_changed := (benutils.column_changed(p_old.attribute7
574 ,p_new.attribute7,l_new_val) AND
575 benutils.column_changed(p_new.attribute7
576 ,p_old.attribute7,l_old_val) AND
577 (l_changed));
578 end if;
579 --
580 if l_column = 'ATTRIBUTE8' then
581 l_changed := (benutils.column_changed(p_old.attribute8
582 ,p_new.attribute8,l_new_val) AND
583 benutils.column_changed(p_new.attribute8
584 ,p_old.attribute8,l_old_val) AND
585 (l_changed));
586 end if;
587 --
588 if l_column = 'ATTRIBUTE9' then
589 l_changed := (benutils.column_changed(p_old.attribute9
590 ,p_new.attribute9,l_new_val) AND
591 benutils.column_changed(p_new.attribute9
592 ,p_old.attribute9,l_old_val) AND
593 (l_changed));
594 end if;
595 --
596 if l_column = 'ATTRIBUTE10' then
597 l_changed := (benutils.column_changed(p_old.attribute10
598 ,p_new.attribute10,l_new_val) AND
599 benutils.column_changed(p_new.attribute10
600 ,p_old.attribute10,l_old_val) AND
601 (l_changed));
602 end if;
603 --
604 if l_column = 'ATTRIBUTE11' then
605 l_changed := (benutils.column_changed(p_old.attribute11
606 ,p_new.attribute11,l_new_val) AND
607 benutils.column_changed(p_new.attribute11
608 ,p_old.attribute11,l_old_val) AND
609 (l_changed));
610 end if;
611 --
612 if l_column = 'ATTRIBUTE12' then
613 l_changed := (benutils.column_changed(p_old.attribute12
614 ,p_new.attribute12,l_new_val) AND
615 benutils.column_changed(p_new.attribute12
616 ,p_old.attribute12,l_old_val) AND
617 (l_changed));
618 end if;
619 --
620 if l_column = 'ATTRIBUTE13' then
621 l_changed := (benutils.column_changed(p_old.attribute13
622 ,p_new.attribute13,l_new_val) AND
623 benutils.column_changed(p_new.attribute13
624 ,p_old.attribute13,l_old_val) AND
625 (l_changed));
626 end if;
627 --
628 if l_column = 'ATTRIBUTE14' then
629 l_changed := (benutils.column_changed(p_old.attribute14
630 ,p_new.attribute14,l_new_val) AND
631 benutils.column_changed(p_new.attribute14
632 ,p_old.attribute14,l_old_val) AND
633 (l_changed));
637 l_changed := (benutils.column_changed(p_old.attribute15
634 end if;
635 --
636 if l_column = 'ATTRIBUTE15' then
638 ,p_new.attribute15,l_new_val) AND
639 benutils.column_changed(p_new.attribute15
640 ,p_old.attribute15,l_old_val) AND
641 (l_changed));
642 end if;
643 --
644 if l_column = 'ATTRIBUTE16' then
645 l_changed := (benutils.column_changed(p_old.attribute16
646 ,p_new.attribute16,l_new_val) AND
647 benutils.column_changed(p_new.attribute16
648 ,p_old.attribute16,l_old_val) AND
649 (l_changed));
650 end if;
651 --
652 if l_column = 'ATTRIBUTE17' then
653 l_changed := (benutils.column_changed(p_old.attribute17
654 ,p_new.attribute17,l_new_val) AND
655 benutils.column_changed(p_new.attribute17
656 ,p_old.attribute17,l_old_val) AND
657 (l_changed));
658 end if;
659 --
660 if l_column = 'ATTRIBUTE18' then
661 l_changed := (benutils.column_changed(p_old.attribute18
662 ,p_new.attribute18,l_new_val) AND
663 benutils.column_changed(p_new.attribute18
664 ,p_old.attribute18,l_old_val) AND
665 (l_changed));
666 end if;
667 --
668 if l_column = 'ATTRIBUTE19' then
669 l_changed := (benutils.column_changed(p_old.attribute19
670 ,p_new.attribute19,l_new_val) AND
671 benutils.column_changed(p_new.attribute19
672 ,p_old.attribute19,l_old_val) AND
673 (l_changed));
674 end if;
675 --
676 if l_column = 'ATTRIBUTE20' then
677 l_changed := (benutils.column_changed(p_old.attribute20
678 ,p_new.attribute20,l_new_val) AND
679 benutils.column_changed(p_new.attribute20
680 ,p_old.attribute20,l_old_val) AND
681 (l_changed));
682 end if;
683 --
684
685 --
686 -- Checking the rule output and the rule override flag.
687 -- Whether the rule is mandatory or not, rule output should return 'Y'
688 -- Rule Mandatory flag is just to override the column data change.
689
690 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
691 l_changed := TRUE ;
692 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
693 l_changed := l_changed AND TRUE;
694 elsif l_rule_output = 'N' then
695 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
696 l_changed := FALSE;
697 end if;
698
699 hr_utility.set_location('After the rule Check ',20.05);
700 if l_changed then
701 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
702 else
703 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
704 end if;
705 -- Check for Column Mandatory Change
706 -- If column change is mandatory and data change has failed then dont trigger
707 -- If column change is non-mandatory and the data change has passed, then trigger.
708
709 if l_chg_mandatory_cd = 'Y' and not l_changed then
710 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
711 l_changed := FALSE;
712 l_trigger := FALSE;
713 exit;
714 elsif l_chg_mandatory_cd = 'Y' and l_changed then
715 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
716 l_changed := TRUE;
717 -- exit; */
718 elsif l_chg_mandatory_cd = 'N' and l_changed then
719 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
720 l_changed := TRUE;
721 l_trigger := TRUE;
722 exit;
723 end if;
724
725 hr_utility.set_location('After the Mandatory code check ',20.05);
726 if l_changed then
727 hr_utility.set_location(' l_change TRUE ', 20.1);
728 else
729 hr_utility.set_location(' l_change FALSE ', 20.1);
730 end if;
731 --
732 /* if not l_changed then
733 exit;
734 end if; */
735 --
736 end loop;
737 hr_utility.set_location(' ben_cel_trigger', 30);
738 if l_changed then
739 hr_utility.set_location('Change detected', 30);
740 end if;
741 l_ptnl_id := 0;
742 l_ovn :=null;
743 if l_trigger then
744 if l_type = 'P' then
745 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
746 fetch le_exists into l_le_exists;
747 if le_exists%notfound then
748 hr_utility.set_location(' Entering: ben_cel_trigger5', 60);
749
750 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
751 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
755 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
752 (p_validate => false
753 ,p_ptnl_ler_for_per_id => l_ptnl_id
754 ,p_ntfn_dt => trunc(sysdate)
756 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
757 ,p_ler_id => l_ler_id
758 ,p_ler_typ_cd => l_typ_cd
759 ,p_person_id => p_new.person_id
760 ,p_business_group_Id =>p_new.business_group_id
761 ,p_object_version_number => l_ovn
762 ,p_effective_date => l_effective_start_date
763 ,p_dtctd_dt => l_lf_evt_ocrd_date);
764 end if;
765 close le_exists;
766 elsif l_type = 'R' then
767 hr_utility.set_location(' Entering: ben_cel_trigger5-', 65);
768 open get_contacts(p_new.person_id);
769 loop
770 fetch get_contacts into l_hld_person_id;
771 exit when get_contacts%notfound;
772 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
773 fetch le_exists into l_le_exists;
774 if le_exists%notfound then
775 hr_utility.set_location(' Entering: ben_cel_trigger5', 60);
776
777 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
778 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
779 (p_validate => false
780 ,p_ptnl_ler_for_per_id => l_ptnl_id
781 ,p_ntfn_dt => trunc(sysdate)
782 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
783 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
784 ,p_ler_id => l_ler_id
785 ,p_ler_typ_cd => l_typ_cd
786 ,p_person_id => l_hld_person_id
787 ,p_business_group_Id =>p_new.business_group_id
788 ,p_object_version_number => l_ovn
789 ,p_effective_date => l_effective_start_date
790 ,p_dtctd_dt => l_lf_evt_ocrd_date);
791 end if;
792 l_ptnl_id := 0;
793 l_ovn :=null;
794 close le_exists;
795 end loop;
796 close get_contacts;
797 end if;
798 --
799 -- reset the variables.
800 --
801 hr_utility.set_location(' ben_cel_ler', 100);
802 l_changed := FALSE;
803 l_trigger := TRUE;
804 l_ovn := NULL;
805 end if;
806 close get_ler_col;
807 end loop;
808 hr_utility.set_location(' ben_cel_ler', 110);
809 close get_ler;
810 hr_utility.set_location(' Leaving: ben_cel_ler', 120);
811
812 end;
813 end ben_cel_ler;