[Home] [Help]
PACKAGE BODY: APPS.BEN_CON_LER
Source
1 package body ben_con_ler as
2 /* $Header: becontrg.pkb 120.3.12010000.3 2008/08/05 14:18:14 ubhat ship $*/
3
4 -- Start Fix 4911130
5 g_param_tab ff_exec.outputs_t;
6
7 procedure populate_param_tab
8 (p_name in varchar2,
9 p_value in varchar2) is
10 l_next_index number;
11 begin
12 l_next_index := nvl(g_param_tab.count,0) + 1;
13 g_param_tab(l_next_index).name := p_name;
14 g_param_tab(l_next_index).value := p_value;
15 end;
16 -- End Fix 4911130
17
18 procedure ler_chk(p_old IN g_con_ler_rec
19 ,p_new IN g_con_ler_rec
20 ,p_effective_date in date ) is
21 --
22 l_proc varchar2(100) := 'ben_con_ler.ler_chk';
23 l_session_date DATE;
24 l_system_date DATE;
25 --
26
27 cursor get_session_date IS
28 select effective_date
29 from fnd_sessions
30 where session_id = userenv('SESSIONID');
31 --
32 cursor get_system_date IS
33 select trunc(sysdate)
34 from dual;
35 --
36 cursor get_ler(l_status varchar2) is
37 select ler.ler_id
38 , ler.typ_cd
39 , ler.ocrd_dt_det_cd
40 , ler.effective_start_date -- Added for bug 3105696
41 from ben_ler_f ler
42 where ler.business_group_id = p_new.business_group_id
43 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
44 and l_session_date
45 between ler.effective_start_date
46 and ler.effective_end_date
47 and ((exists
48 (select 1
49 from ben_per_info_chg_cs_ler_f psl
50 , ben_ler_per_info_cs_ler_f lpl
51 where source_table = 'PER_CONTACT_RELATIONSHIPS'
52 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
53 and lpl.business_group_id = psl.business_group_id
54 and lpl.business_group_id = ler.business_group_id
55 and l_session_date between psl.effective_start_date
56 and psl.effective_end_date
57 and l_session_date between lpl.effective_start_date
58 and lpl.effective_end_date
59 and lpl.ler_id = ler.ler_id)
60 )
61 OR (exists
62 (select 1
63 from ben_rltd_per_chg_cs_ler_f rpc
64 , ben_ler_rltd_per_cs_ler_f lrp
65 where source_table = 'PER_CONTACT_RELATIONSHIPS'
66 and lrp.business_group_id = rpc.business_group_id
67 and lrp.business_group_id = ler.business_group_id
68 and l_session_date between rpc.effective_start_date
69 and rpc.effective_end_date
70 and l_session_date between lrp.effective_start_date
71 and lrp.effective_end_date
72 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
73 and lrp.ler_id = ler.ler_id)
74 ))
75 order by ler.ler_id;
76
77 cursor get_ler_col(p_ler_id IN NUMBER) is
78 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
79 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
80 where lpl.ler_id = p_ler_id and
81 lpl.business_group_id = p_new.business_group_id
82 and lpl.business_group_id = psl.business_group_id
83 and l_session_date between psl.effective_start_date
84 and psl.effective_end_date
85 and l_session_date between lpl.effective_start_date
86 and lpl.effective_end_date
87 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
88 and source_table = 'PER_CONTACT_RELATIONSHIPS'
89 UNION
90 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
91 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
92 where lrp.ler_id = p_ler_id and
93 lrp.business_group_id = p_new.business_group_id
94 and lrp.business_group_id = rpc.business_group_id
95 and l_session_date between rpc.effective_start_date
96 and rpc.effective_end_date
97 and l_session_date between lrp.effective_start_date
98 and lrp.effective_end_date
99 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
100 and source_table = 'PER_CONTACT_RELATIONSHIPS'
101 order by 1;
102 --
103 cursor le_exists(p_person_id in number
104 ,p_ler_id in number
105 ,p_lf_evt_ocrd_dt in date) is
106 select 'Y'
107 from ben_ptnl_ler_for_per
108 where person_id = p_person_id
109 and ler_id = p_ler_id
110 and ptnl_ler_for_per_stat_cd = 'DTCTD'
111 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
112
113 cursor get_person_date(p_person_id in number) is
114 select min(effective_start_date) -- 3518038
115 from per_all_people_f
116 where person_id = p_person_id
117 and business_group_id = p_new.business_group_id;
118 --Bug 2206932 (always using the system date as user can start relationship before
119 --before the person is started.
120 /*and l_system_date between effective_start_date
121 and effective_end_date; */ -- commented for 3518038
122
123 l_changed BOOLEAN;
124 l_ler_id NUMBER;
125 l_typ_cd ben_ler_f.typ_cd%type ;
126 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
127 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
128 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
129 l_ocrd_dt_cd VARCHAR2(30);
130 l_per_info_chg_cs_ler_rl number;
131 l_rule_output VARCHAR2(1);
132 l_ovn NUMBER;
133 l_ptnl_id NUMBER;
134 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
135 l_effective_start_date DATE ;
136 l_dtctd_dt DATE ;
137 l_person_esd DATE := null;
138 l_lf_evt_ocrd_date DATE ;
139 l_le_exists VARCHAR2(1);
140 l_mnl_dt date;
141 l_procd_dt date;
142 l_unprocd_dt date;
143 l_voidd_dt date;
144 l_type VARCHAR2(1);
145 l_date_start date;
146 l_date_end date;
147 --l_hld_person_id NUMBER;
148 --
149 l_bool BOOLEAN;
150 l_status VARCHAR2(1);
151 l_industry VARCHAR2(1);
152 l_col_new_val VARCHAR2(1000);
153 l_col_old_val varchar2(1000);
154 --
155 l_rule_overrides_flag VARCHAR2(1);
156 l_chg_mandatory_cd VARCHAR2(1);
157 l_trigger boolean := TRUE;
158 --
159 --
160 -- added these two variables as part of bug 3105696
161 l_flag boolean:=FALSE;
162 l_initial_date DATE;
163 l_ler_eff_strt_dt DATE;
164 --
165 cursor c_per_type (p_person_id in per_all_people_f.person_id%TYPE )is
166 select con.system_person_type
167 from per_all_people_f per , per_person_types con
168 where per.person_id = p_person_id
169 and per.person_type_id = con.person_type_id
170 and l_session_date between per.effective_start_date and per.effective_end_date ;
171 --
172 l_per_type per_person_types.system_person_type%TYPE ;
173 l_con_per_type per_person_types.system_person_type%TYPE ;
174 --
175
176 begin
177 --
178 -- Bug 2016857
179 benutils.set_data_migrator_mode;
180 -- Bug 2016857
181 if hr_general.g_data_migrator_mode in ( 'Y','P') then
182 --
183 return;
184 --
185 end if;
186 --
187 l_bool :=fnd_installation.get(appl_id => 805
188 ,dep_appl_id =>805
189 ,status => l_status
190 ,industry => l_industry);
191
192 hr_utility.set_location(' Entering: '||l_proc, 10);
193 l_changed := FALSE;
194
195 If p_effective_date is not null then
196 l_initial_date := p_effective_date ;
197 -- Modified for bug 3105696.
198 -- If end date is specified then, consider the end date
199 -- We need to consider the end date only when its entered fresh.
200 If p_new.date_end is not null then
201 l_session_date:= p_new.date_end;
202 else
203 l_session_date := p_effective_date ;
204 end if;
205 -- End of Bug 3105696
206 else
207 open get_session_date;
208 fetch get_session_date into l_session_date;
209 close get_session_date;
210 l_initial_date := l_session_date;
211 end if ;
212 open get_system_date;
213 fetch get_system_date into l_system_date;
214 close get_system_date;
215
216 -- Bug 3105696
217 -- l_session_date is used to fetch the valid LEs. storing the l_session_date temporarily
218 -- so that we can use it later to check whether the LE is valid
219
220 -- Bug 3105696
221
222 l_effective_start_date := l_session_date;
223
224 hr_utility.set_location(l_proc, 20);
225 hr_utility.set_location('CONTACT PERSON '||p_new.contact_person_id, 20);
226 hr_utility.set_location('PERSON '||p_new.person_id, 20);
227 hr_utility.set_location('BG '||p_new.business_group_id, 20);
228
229 -- in some situations the date we use for occured on date is null,
230 -- use session date instead.
231 if p_new.date_start is null then
232 l_date_start := l_session_date;
233 else
234 l_date_start := p_new.date_start;
235 end if;
236
237 if p_new.date_end is null then
238 l_date_end := l_session_date;
239 else
240 l_date_end := p_new.date_end;
241 end if;
242
243 -- the lf evt occurred date cannot be less than the date the person rec was created.
244 open get_person_date(p_person_id => p_new.person_id); -- modified for bug 3105696
245 fetch get_person_date into l_person_esd;
246 close get_person_date;
247
248 --
249 if l_date_start < nvl(l_person_esd, l_date_start) then
250 l_date_start := l_person_esd;
251 end if;
252 -- Bug 2206932
253 if l_effective_start_date < nvl(l_person_esd, l_date_start) then
254 --
255 l_effective_start_date := nvl(l_person_esd, l_date_start) ;
256 --
257 end if;
258 --
259 hr_utility.set_location(' l_person_esd:'||to_char(l_person_esd), 30);
260 hr_utility.set_location(' l_date_start:'||to_char(l_date_start), 30);
261 hr_utility.set_location(' l_system_date:'||to_char(l_system_date), 30);
262 hr_utility.set_location(' l_session_date:'||to_char(l_session_date), 30);
263 hr_utility.set_location('l_initial_date:'||to_char(l_initial_date), 30);
264 hr_utility.set_location('l_effective_start_date: '||to_char(l_effective_start_date),30);
265 --
266 open get_ler(l_status);
267 loop
268 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd, l_ler_eff_strt_dt;
269 exit when get_ler%notfound;
270 l_trigger := TRUE;
271
272 if l_ocrd_dt_cd is null then
273 l_lf_evt_ocrd_date := l_date_start;
274 else
275 --
276 -- Call the common date procedure.
277 --
278 ben_determine_date.main
279 (p_date_cd => l_ocrd_dt_cd
280 ,p_effective_date => l_date_start
281 ,p_lf_evt_ocrd_dt => p_new.date_start
282 ,p_returned_date => l_lf_evt_ocrd_date
283 );
284 end if;
285
286 hr_utility.set_location(' l_lf_evt_ocrd_date ' ||l_lf_evt_ocrd_date, 95);
287 hr_utility.set_location(' l_effective_start_date ' ||l_effective_start_date, 95);
288 hr_utility.set_location(' l_dtctd_dt ' ||l_dtctd_dt, 95);
289 hr_utility.set_location(' l_ler_eff_strt_dt ' ||l_ler_eff_strt_dt, 95);
290
291 open get_ler_col(l_ler_id);
292 loop
293 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;
294 exit when get_ler_col%NOTFOUND;
295 hr_utility.set_location('LER '||l_ler_id, 40);
296 hr_utility.set_location('COLUMN '||l_column, 40);
297 hr_utility.set_location('NEWVAL '||l_new_val, 40);
298 hr_utility.set_location('OLDVAL '||l_old_val, 40);
299 hr_utility.set_location('TYPE '||l_type, 40);
300 --l_changed := TRUE; -- Bug 6219676 -- No need of this
301 if get_ler_col%ROWCOUNT = 1 then
302 hr_utility.set_location('rowcount 1 ', 50);
303 l_changed := TRUE;
304 end if;
305
306 -- Due to mirroring this will avoid ptnl being written for both
307 -- prtt and contact
308
309 -- if p_new.contact_person_id < p_new.person_id then -- Bug 6219676
310 --
311 -- Bug - 1809612 added code to ensure that ptnl LE gets created for contact who is also an employee.
312 -- Also the ptnl LE should not get created for contacts who are not EMP , EMP_APL .
313 --
314 -- hr_utility.set_location('Mirror - disregard ', 60);
315 -- l_changed := FALSE;
316 --
317 --
318 open c_per_type (p_new.contact_person_id );
319 fetch c_per_type into l_con_per_type;
320 close c_per_type ;
321 --
322 hr_utility.set_location('person type '||l_con_per_type||'PER'||l_per_type, 52);
323 if l_con_per_type in ( 'EMP' , 'EMP_APL', 'EX_EMP') -- Added ex-employee for bug 7113467
324 then
325 --
326 open c_per_type (p_new.person_id );
327 fetch c_per_type into l_per_type;
328 close c_per_type ;
329 --
330 if l_per_type in ( 'EMP' , 'EMP_APL')
331 then
332 hr_utility.set_location('person type '||l_per_type , 55);
333 l_changed := TRUE;
334 else
335 hr_utility.set_location('Mirror - disregard ', 57);
336 l_changed := FALSE;
337 end if;
338 else
339 hr_utility.set_location('Mirror - disregard ', 60);
340 l_changed := l_changed AND TRUE; -- Bug 6219676
341 end if ;
342 -- bug - 1809612
343 -- end if;
344
345 hr_utility.set_location(l_proc, 70);
346
347 --
348 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
349 -- If it returns Y, then see the applicability of the data
350 -- changes based on new and old values.
351 --
352 l_rule_output := 'Y';
353 --
354 if l_per_info_chg_cs_ler_rl is not null then
355
356 if l_column = 'PERSON_ID' then
357 l_col_old_val := to_char(p_old.PERSON_ID);
358 l_col_new_val := to_char(p_new.PERSON_ID);
359 end if;
360 --
361 if l_column = 'CONTACT_PERSON_ID' then
362 l_col_old_val := to_char(p_old.CONTACT_PERSON_ID);
363 l_col_new_val := to_char(p_new.CONTACT_PERSON_ID);
364 end if;
365 --
366 if l_column = 'DATE_START' then
367 l_col_old_val := to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
368 l_col_new_val := to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS');
369 end if;
370 --
371 if l_column = 'DATE_END' then
372 l_col_old_val := to_char(p_old.DATE_END, 'YYYY/MM/DD HH24:MI:SS');
373 l_col_new_val := to_char(p_new.DATE_END, 'YYYY/MM/DD HH24:MI:SS');
374 end if;
375 --
376 if l_column = 'CONTACT_TYPE' then
377 l_col_old_val := p_old.CONTACT_TYPE;
378 l_col_new_val := p_new.CONTACT_TYPE;
379 end if;
380 --
381 if l_column = 'PERSONAL_FLAG' then
382 l_col_old_val := p_old.PERSONAL_FLAG;
383 l_col_new_val := p_new.PERSONAL_FLAG;
384 end if;
385 --
386 if l_column = 'START_LIFE_REASON_ID' then
387 l_col_old_val := to_char(p_old.START_LIFE_REASON_ID);
388 l_col_new_val := to_char(p_new.START_LIFE_REASON_ID);
389 end if;
390 --
391 if l_column = 'END_LIFE_REASON_ID' then
392 l_col_old_val := to_char(p_old.END_LIFE_REASON_ID);
393 l_col_new_val := to_char(p_new.END_LIFE_REASON_ID);
394 end if;
395 --
396 if l_column = 'RLTD_PER_RSDS_W_DSGNTR_FLAG' then
397 l_col_old_val := p_old.RLTD_PER_RSDS_W_DSGNTR_FLAG;
398 l_col_new_val := p_new.RLTD_PER_RSDS_W_DSGNTR_FLAG;
399 end if;
400 --
401 -- Bug 1772037 fix
402 --
403 if l_column = 'CONT_ATTRIBUTE1' then
404 l_col_old_val := p_old.CONT_ATTRIBUTE1;
405 l_col_new_val := p_new.CONT_ATTRIBUTE1;
406 end if;
407 --
408 if l_column = 'CONT_ATTRIBUTE2' then
409 l_col_old_val := p_old.CONT_ATTRIBUTE2;
410 l_col_new_val := p_new.CONT_ATTRIBUTE2;
411 end if;
412 --
413 if l_column = 'CONT_ATTRIBUTE3' then
414 l_col_old_val := p_old.CONT_ATTRIBUTE3;
415 l_col_new_val := p_new.CONT_ATTRIBUTE3;
416 end if;
417 --
418 if l_column = 'CONT_ATTRIBUTE4' then
419 l_col_old_val := p_old.CONT_ATTRIBUTE4;
420 l_col_new_val := p_new.CONT_ATTRIBUTE4;
421 end if;
422 --
423 if l_column = 'CONT_ATTRIBUTE5' then
424 l_col_old_val := p_old.CONT_ATTRIBUTE5;
425 l_col_new_val := p_new.CONT_ATTRIBUTE5;
426 end if;
427 --
428 if l_column = 'CONT_ATTRIBUTE6' then
429 l_col_old_val := p_old.CONT_ATTRIBUTE6;
430 l_col_new_val := p_new.CONT_ATTRIBUTE6;
431 end if;
432 --
433 if l_column = 'CONT_ATTRIBUTE7' then
434 l_col_old_val := p_old.CONT_ATTRIBUTE7;
435 l_col_new_val := p_new.CONT_ATTRIBUTE7;
436 end if;
437 --
438 if l_column = 'CONT_ATTRIBUTE8' then
439 l_col_old_val := p_old.CONT_ATTRIBUTE8;
440 l_col_new_val := p_new.CONT_ATTRIBUTE8;
441 end if;
442 --
443 if l_column = 'CONT_ATTRIBUTE9' then
444 l_col_old_val := p_old.CONT_ATTRIBUTE9;
445 l_col_new_val := p_new.CONT_ATTRIBUTE9;
446 end if;
447 --
448 if l_column = 'CONT_ATTRIBUTE10' then
449 l_col_old_val := p_old.CONT_ATTRIBUTE10;
450 l_col_new_val := p_new.CONT_ATTRIBUTE10;
451 end if;
452 --
453 if l_column = 'CONT_ATTRIBUTE11' then
454 l_col_old_val := p_old.CONT_ATTRIBUTE11;
455 l_col_new_val := p_new.CONT_ATTRIBUTE11;
456 end if;
457 --
458 if l_column = 'CONT_ATTRIBUTE12' then
459 l_col_old_val := p_old.CONT_ATTRIBUTE12;
460 l_col_new_val := p_new.CONT_ATTRIBUTE12;
461 end if;
462 --
463 if l_column = 'CONT_ATTRIBUTE13' then
464 l_col_old_val := p_old.CONT_ATTRIBUTE13;
465 l_col_new_val := p_new.CONT_ATTRIBUTE13;
466 end if;
467 --
468 if l_column = 'CONT_ATTRIBUTE14' then
469 l_col_old_val := p_old.CONT_ATTRIBUTE14;
470 l_col_new_val := p_new.CONT_ATTRIBUTE14;
471 end if;
472 --
473 if l_column = 'CONT_ATTRIBUTE15' then
474 l_col_old_val := p_old.CONT_ATTRIBUTE15;
475 l_col_new_val := p_new.CONT_ATTRIBUTE15;
476 end if;
477 --
478 if l_column = 'CONT_ATTRIBUTE16' then
479 l_col_old_val := p_old.CONT_ATTRIBUTE16;
480 l_col_new_val := p_new.CONT_ATTRIBUTE16;
481 end if;
482 --
483 if l_column = 'CONT_ATTRIBUTE17' then
484 l_col_old_val := p_old.CONT_ATTRIBUTE17;
485 l_col_new_val := p_new.CONT_ATTRIBUTE17;
486 end if;
487 --
488 if l_column = 'CONT_ATTRIBUTE18' then
489 l_col_old_val := p_old.CONT_ATTRIBUTE18;
490 l_col_new_val := p_new.CONT_ATTRIBUTE18;
491 end if;
492 --
493 if l_column = 'CONT_ATTRIBUTE19' then
494 l_col_old_val := p_old.CONT_ATTRIBUTE19;
495 l_col_new_val := p_new.CONT_ATTRIBUTE19;
499 l_col_old_val := p_old.CONT_ATTRIBUTE20;
496 end if;
497 --
498 if l_column = 'CONT_ATTRIBUTE20' then
500 l_col_new_val := p_new.CONT_ATTRIBUTE20;
501 end if;
502 --
503 -- End fix 1772037
504 --
505
506 -- Start Fix 4911130
507 hr_utility.set_location('Populating the g_param_tab', 66);
508 populate_param_tab('BEN_CON_IN_PERSON_ID', to_char(p_new.PERSON_ID));
509 populate_param_tab('BEN_CON_IN_CONTACT_PERSON_ID', to_char(p_new.CONTACT_PERSON_ID));
510 populate_param_tab('BEN_CON_IN_DATE_START', to_char(p_new.DATE_START, 'YYYY/MM/DD HH24:MI:SS'));
511 populate_param_tab('BEN_CON_IN_DATE_END', to_char(p_new.DATE_END, 'YYYY/MM/DD HH24:MI:SS'));
512 populate_param_tab('BEN_CON_IN_CONTACT_TYPE', p_new.CONTACT_TYPE);
513 populate_param_tab('BEN_CON_IN_PERSONAL_FLAG', p_new.PERSONAL_FLAG);
514 populate_param_tab('BEN_CON_IN_START_LIFE_REASON_ID', to_char(p_new.START_LIFE_REASON_ID));
515 populate_param_tab('BEN_CON_IN_END_LIFE_REASON_ID', to_char(p_new.END_LIFE_REASON_ID));
516 populate_param_tab('BEN_CON_IN_RLTD_PER_RSDS_W_DSGNTR_FLAG', p_new.RLTD_PER_RSDS_W_DSGNTR_FLAG);
517 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE1', p_new.cont_attribute1);
518 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE2', p_new.cont_attribute2);
519 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE3', p_new.cont_attribute3);
520 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE4', p_new.cont_attribute4);
521 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE5', p_new.cont_attribute5);
522 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE6', p_new.cont_attribute6);
523 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE7', p_new.cont_attribute7);
524 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE8', p_new.cont_attribute8);
525 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE9', p_new.cont_attribute9);
526 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE10', p_new.cont_attribute10);
527 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE11', p_new.cont_attribute11);
528 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE12', p_new.cont_attribute12);
529 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE13', p_new.cont_attribute13);
530 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE14', p_new.cont_attribute14);
531 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE15', p_new.cont_attribute15);
532 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE16', p_new.cont_attribute16);
533 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE17', p_new.cont_attribute17);
534 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE18', p_new.cont_attribute18);
535 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE19', p_new.cont_attribute19);
536 populate_param_tab('BEN_CON_IN_CONT_ATTRIBUTE20', p_new.cont_attribute20);
537 populate_param_tab('BEN_CON_IO_PERSON_ID', to_char(p_old.PERSON_ID));
538 populate_param_tab('BEN_CON_IO_CONTACT_PERSON_ID', to_char(p_old.CONTACT_PERSON_ID));
539 populate_param_tab('BEN_CON_IO_DATE_START', to_char(p_old.DATE_START, 'YYYY/MM/DD HH24:MI:SS'));
540 populate_param_tab('BEN_CON_IO_DATE_END', to_char(p_old.DATE_END, 'YYYY/MM/DD HH24:MI:SS'));
541 populate_param_tab('BEN_CON_IO_CONTACT_TYPE', p_old.CONTACT_TYPE);
542 populate_param_tab('BEN_CON_IO_PERSONAL_FLAG', p_old.PERSONAL_FLAG);
543 populate_param_tab('BEN_CON_IO_START_LIFE_REASON_ID', to_char(p_old.START_LIFE_REASON_ID));
544 populate_param_tab('BEN_CON_IO_END_LIFE_REASON_ID', to_char(p_old.END_LIFE_REASON_ID));
545 populate_param_tab('BEN_CON_IO_RLTD_PER_RSDS_W_DSGNTR_FLAG', p_old.RLTD_PER_RSDS_W_DSGNTR_FLAG);
546 populate_param_tab('BEN_CON_IO_START_LIFE_REASON_ID', to_char(p_old.START_LIFE_REASON_ID));
547 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE1', p_old.cont_attribute1);
548 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE2', p_old.cont_attribute2);
549 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE3', p_old.cont_attribute3);
550 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE4', p_old.cont_attribute4);
551 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE5', p_old.cont_attribute5);
552 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE6', p_old.cont_attribute6);
553 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE7', p_old.cont_attribute7);
554 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE8', p_old.cont_attribute8);
555 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE9', p_old.cont_attribute9);
556 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE10', p_old.cont_attribute10);
557 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE11', p_old.cont_attribute11);
558 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE12', p_old.cont_attribute12);
559 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE13', p_old.cont_attribute13);
560 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE14', p_old.cont_attribute14);
561 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE15', p_old.cont_attribute15);
562 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE16', p_old.cont_attribute16);
563 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE17', p_old.cont_attribute17);
564 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE18', p_old.cont_attribute18);
565 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE19', p_old.cont_attribute19);
566 populate_param_tab('BEN_CON_IO_CONT_ATTRIBUTE20', p_old.cont_attribute20);
567 populate_param_tab('BEN_IV_LER_ID', to_char(l_ler_id));
568 hr_utility.set_location('Done Populating the g_param_tab', 66);
569 -- End Fix 4911130
570
571 benutils.exec_rule(
575 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
572 p_formula_id => l_per_info_chg_cs_ler_rl,
573 p_effective_date => l_session_date,
574 p_lf_evt_ocrd_dt => null,
576 p_person_id => nvl(p_new.person_id, p_old.person_id),
577 p_new_value => l_col_new_val,
578 p_old_value => l_col_old_val,
579 p_column_name => l_column,
580 p_ret_val => l_rule_output,
581 p_param_tab => g_param_tab, /* Bug No 4911130 */
582 p_pk_id => to_char(p_new.contact_relationship_id));
583 --
584 g_param_tab.delete; /* Bug No 4911130 */
585 end if;
586 --
587
588 --
589 if l_column = 'DATE_START' then
590 hr_utility.set_location('Old Start Date '||p_old.date_start,10);
591 hr_utility.set_location('New Start Date '||p_new.date_start,10);
592 l_changed := (benutils.column_changed(p_old.date_start
593 ,p_new.date_start,l_new_val) AND
594 benutils.column_changed(p_new.date_start
595 ,p_old.date_start,l_old_val) AND
596 (l_changed));
597 l_dtctd_dt := l_date_start;
598 end if;
599
600 if l_column = 'DATE_END' then
601 l_changed := (benutils.column_changed(p_old.date_end
602 ,p_new.date_end,l_new_val) AND
603 benutils.column_changed(p_new.date_end
604 ,p_old.date_end,l_old_val) AND
605 (l_person_esd <= l_session_date) AND -- Added for 3105696
606 (l_changed));
607 hr_utility.set_location(' l_person_esd (DATE END) ' ||l_person_esd, 70);
608 hr_utility.set_location(' l_session_date (DATE END) ' || l_session_date, 70);
609 if l_ocrd_dt_cd is null then
610 l_lf_evt_ocrd_date := l_date_end;
611 else
612 --
613 -- Call the common date procedure.
614 --
615 ben_determine_date.main
616 (p_date_cd => l_ocrd_dt_cd
617 ,p_effective_date => l_date_end
618 ,p_lf_evt_ocrd_dt => p_new.date_end
619 ,p_returned_date => l_lf_evt_ocrd_date
620 );
621 end if;
622 l_dtctd_dt := l_date_end;
623 end if;
624
625 if l_column = 'CONTACT_TYPE' then
626 hr_utility.set_location('Old ConTYp '||p_old.contact_type, 80);
627 hr_utility.set_location('New ConTYp '||p_new.contact_type, 80);
628 l_changed := (benutils.column_changed(p_old.contact_type
629 ,p_new.contact_type,l_new_val) AND
630 benutils.column_changed(p_new.contact_type
631 ,p_old.contact_type,l_old_val) AND
632 (l_changed));
633 end if;
634
635 if l_column = 'PERSONAL_FLAG' then
636 hr_utility.set_location('Old Per_flag '||p_old.personal_flag, 90);
637 hr_utility.set_location('New Per_flag '||p_new.personal_flag, 90);
638 l_changed := (benutils.column_changed(p_old.personal_flag
639 ,p_new.personal_flag,l_new_val) AND
640 benutils.column_changed(p_new.personal_flag
641 ,p_old.personal_flag,l_old_val) AND
642 (l_changed));
643 end if;
644
645 if l_column = 'START_LIFE_REASON_ID' then
646
647 l_changed := (benutils.column_changed(p_old.start_life_reason_id
648 ,p_new.start_life_reason_id,l_new_val) AND
649 benutils.column_changed(p_new.start_life_reason_id
650 ,p_old.start_life_reason_id,l_old_val) AND
651 (l_changed));
652 end if;
653
654 if l_column = 'END_LIFE_REASON_ID' then
655 l_changed := (benutils.column_changed(p_old.end_life_reason_id
656 ,p_new.end_life_reason_id,l_new_val) AND
657 benutils.column_changed(p_new.end_life_reason_id
658 ,p_old.end_life_reason_id,l_old_val) AND
659 (l_person_esd <= l_session_date) AND -- Added for 3105696
660 (l_changed));
661 hr_utility.set_location(' l_person_esd (END_LIFE_REASON_ID) ' ||l_person_esd, 70);
662 hr_utility.set_location(' l_session_date (END_LIFE_REASON_ID) ' || l_session_date, 70);
663 if l_ocrd_dt_cd is null then
664 l_lf_evt_ocrd_date := l_date_end;
665 else
666 --
667 -- Call the common date procedure.
668 --
669 ben_determine_date.main
670 (p_date_cd => l_ocrd_dt_cd
671 ,p_effective_date => l_date_end
672 ,p_lf_evt_ocrd_dt => p_new.date_end
673 ,p_returned_date => l_lf_evt_ocrd_date
674 );
675 end if;
676 l_dtctd_dt := l_date_end;
677 end if;
678
679 if l_column = 'RLTD_PER_RSDS_W_DSGNTR_FLAG' then
683 ,p_old.rltd_per_rsds_w_dsgntr_flag,l_old_val) AND
680 l_changed := (benutils.column_changed(p_old.rltd_per_rsds_w_dsgntr_flag
681 ,p_new.rltd_per_rsds_w_dsgntr_flag,l_new_val) AND
682 benutils.column_changed(p_new.rltd_per_rsds_w_dsgntr_flag
684 (l_changed));
685 end if;
686 --
687
688 --
689 -- Bug 1772037 fix
690 --
691 if l_column = 'CONT_ATTRIBUTE1' then
692 l_changed := (benutils.column_changed(p_old.cont_attribute1
693 ,p_new.cont_attribute1,l_new_val) AND
694 benutils.column_changed(p_new.cont_attribute1
695 ,p_old.cont_attribute1,l_old_val) AND
696 (l_changed));
697 end if;
698 --
699 if l_column = 'CONT_ATTRIBUTE2' then
700 l_changed := (benutils.column_changed(p_old.cont_attribute2
701 ,p_new.cont_attribute2,l_new_val) AND
702 benutils.column_changed(p_new.cont_attribute2
703 ,p_old.cont_attribute2,l_old_val) AND
704 (l_changed));
705 end if;
706 --
707 if l_column = 'CONT_ATTRIBUTE3' then
708 l_changed := (benutils.column_changed(p_old.cont_attribute3
709 ,p_new.cont_attribute3,l_new_val) AND
710 benutils.column_changed(p_new.cont_attribute3
711 ,p_old.cont_attribute3,l_old_val) AND
712 (l_changed));
713 end if;
714 --
715 if l_column = 'CONT_ATTRIBUTE4' then
716 l_changed := (benutils.column_changed(p_old.cont_attribute4
717 ,p_new.cont_attribute4,l_new_val) AND
718 benutils.column_changed(p_new.cont_attribute4
719 ,p_old.cont_attribute4,l_old_val) AND
720 (l_changed));
721 end if;
722 --
723 if l_column = 'CONT_ATTRIBUTE5' then
724 l_changed := (benutils.column_changed(p_old.cont_attribute5
725 ,p_new.cont_attribute5,l_new_val) AND
726 benutils.column_changed(p_new.cont_attribute5
727 ,p_old.cont_attribute5,l_old_val) AND
728 (l_changed));
729 end if;
730 --
731 if l_column = 'CONT_ATTRIBUTE6' then
732 l_changed := (benutils.column_changed(p_old.cont_attribute6
733 ,p_new.cont_attribute6,l_new_val) AND
734 benutils.column_changed(p_new.cont_attribute6
735 ,p_old.cont_attribute6,l_old_val) AND
736 (l_changed));
737 end if;
738 --
739 if l_column = 'CONT_ATTRIBUTE7' then
740 l_changed := (benutils.column_changed(p_old.cont_attribute7
741 ,p_new.cont_attribute7,l_new_val) AND
742 benutils.column_changed(p_new.cont_attribute7
743 ,p_old.cont_attribute7,l_old_val) AND
744 (l_changed));
745 end if;
746 --
747 if l_column = 'CONT_ATTRIBUTE8' then
748 l_changed := (benutils.column_changed(p_old.cont_attribute8
749 ,p_new.cont_attribute8,l_new_val) AND
750 benutils.column_changed(p_new.cont_attribute8
751 ,p_old.cont_attribute8,l_old_val) AND
752 (l_changed));
753 end if;
754 --
755 if l_column = 'CONT_ATTRIBUTE9' then
756 l_changed := (benutils.column_changed(p_old.cont_attribute9
757 ,p_new.cont_attribute9,l_new_val) AND
758 benutils.column_changed(p_new.cont_attribute9
759 ,p_old.cont_attribute9,l_old_val) AND
760 (l_changed));
761 end if;
762 --
763 if l_column = 'CONT_ATTRIBUTE10' then
764 l_changed := (benutils.column_changed(p_old.cont_attribute10
765 ,p_new.cont_attribute10,l_new_val) AND
766 benutils.column_changed(p_new.cont_attribute10
767 ,p_old.cont_attribute10,l_old_val) AND
768 (l_changed));
769 end if;
770 --
771 if l_column = 'CONT_ATTRIBUTE11' then
772 l_changed := (benutils.column_changed(p_old.cont_attribute11
773 ,p_new.cont_attribute11,l_new_val) AND
774 benutils.column_changed(p_new.cont_attribute11
775 ,p_old.cont_attribute11,l_old_val) AND
776 (l_changed));
777 end if;
778 --
779 if l_column = 'CONT_ATTRIBUTE12' then
780 l_changed := (benutils.column_changed(p_old.cont_attribute12
781 ,p_new.cont_attribute12,l_new_val) AND
782 benutils.column_changed(p_new.cont_attribute12
783 ,p_old.cont_attribute12,l_old_val) AND
784 (l_changed));
785 end if;
786 --
790 benutils.column_changed(p_new.cont_attribute13
787 if l_column = 'CONT_ATTRIBUTE13' then
788 l_changed := (benutils.column_changed(p_old.cont_attribute13
789 ,p_new.cont_attribute13,l_new_val) AND
791 ,p_old.cont_attribute13,l_old_val) AND
792 (l_changed));
793 end if;
794 --
795 if l_column = 'CONT_ATTRIBUTE14' then
796 l_changed := (benutils.column_changed(p_old.cont_attribute14
797 ,p_new.cont_attribute14,l_new_val) AND
798 benutils.column_changed(p_new.cont_attribute14
799 ,p_old.cont_attribute14,l_old_val) AND
800 (l_changed));
801 end if;
802 --
803 if l_column = 'CONT_ATTRIBUTE15' then
804 l_changed := (benutils.column_changed(p_old.cont_attribute15
805 ,p_new.cont_attribute15,l_new_val) AND
806 benutils.column_changed(p_new.cont_attribute15
807 ,p_old.cont_attribute15,l_old_val) AND
808 (l_changed));
809 end if;
810 --
811 if l_column = 'CONT_ATTRIBUTE16' then
812 l_changed := (benutils.column_changed(p_old.cont_attribute16
813 ,p_new.cont_attribute16,l_new_val) AND
814 benutils.column_changed(p_new.cont_attribute16
815 ,p_old.cont_attribute16,l_old_val) AND
816 (l_changed));
817 end if;
818 --
819 if l_column = 'CONT_ATTRIBUTE17' then
820 l_changed := (benutils.column_changed(p_old.cont_attribute17
821 ,p_new.cont_attribute17,l_new_val) AND
822 benutils.column_changed(p_new.cont_attribute17
823 ,p_old.cont_attribute17,l_old_val) AND
824 (l_changed));
825 end if;
826 --
827 if l_column = 'CONT_ATTRIBUTE18' then
828 l_changed := (benutils.column_changed(p_old.cont_attribute18
829 ,p_new.cont_attribute18,l_new_val) AND
830 benutils.column_changed(p_new.cont_attribute18
831 ,p_old.cont_attribute18,l_old_val) AND
832 (l_changed));
833 end if;
834 --
835 if l_column = 'CONT_ATTRIBUTE19' then
836 l_changed := (benutils.column_changed(p_old.cont_attribute19
837 ,p_new.cont_attribute19,l_new_val) AND
838 benutils.column_changed(p_new.cont_attribute19
839 ,p_old.cont_attribute19,l_old_val) AND
840 (l_changed));
841 end if;
842 --
843 if l_column = 'CONT_ATTRIBUTE20' then
844 l_changed := (benutils.column_changed(p_old.cont_attribute20
845 ,p_new.cont_attribute20,l_new_val) AND
846 benutils.column_changed(p_new.cont_attribute20
847 ,p_old.cont_attribute20,l_old_val) AND
848 (l_changed));
849 end if;
850 --
851
852 -- Added for 3105696
853 l_flag:=FALSE;
854 if l_column in ('DATE_START','START_LIFE_REASON_ID') then
855 l_flag := TRUE;
856 hr_utility.set_location('Setting the flag true', 95);
857 end if;
858
859 -- End of 3105696
860
861
862 -- End fix 1772037
863 --
864 -- Checking the rule output and the rule override flag.
865 -- Whether the rule is mandatory or not, rule output should return 'Y'
866 -- Rule Mandatory flag is just to override the column data change.
867 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
868 l_changed := TRUE ;
869 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
870 l_changed := l_changed AND TRUE;
871 elsif l_rule_output = 'N' then
872 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
873 l_changed := FALSE;
874 end if;
875
876 hr_utility.set_location('After the rule Check ',20.05);
877 if l_changed then
878 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
879 else
880 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
881 end if;
882 -- Check for Column Mandatory Change
883 -- If column change is mandatory and data change has failed then dont trigger
884 -- If column change is non-mandatory and the data change has passed, then trigger.
885
886 if l_chg_mandatory_cd = 'Y' and not l_changed then
887 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
888 l_changed := FALSE;
889 l_trigger := FALSE;
890 exit;
891 elsif l_chg_mandatory_cd = 'Y' and l_changed then
892 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
893 l_changed := TRUE;
894 -- exit; */
895 elsif l_chg_mandatory_cd = 'N' and l_changed then
896 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
897 l_changed := TRUE;
898 l_trigger := TRUE;
899 exit;
900 end if;
901
902
903 hr_utility.set_location('After the Mandatory code check ',20.05);
904 if l_changed then
905 hr_utility.set_location(' l_change TRUE ', 20.1);
906 else
907 hr_utility.set_location(' l_change FALSE ', 20.1);
908 end if;
909 --
910 /* if not l_changed then
911 exit;
912 end if; */
913 --
914 end loop;
915
916
917 -- Added for 3105696
918 if l_flag then
919 --l_flag is true =>>DATE_START/START_LIFE_REASON_ID has been updated.
920 -- We should check whether the Life event getting triggered is
921 -- valid as of Effective start date of relationship
922
923 if l_ler_eff_strt_dt >= l_initial_date then
924 -- we should not trigger the LE
925 l_changed := FALSE;
926 hr_utility.set_location('Making flag False', 95);
927 end if;
928 end if;
929 -- End of 3105696
930
931
932 hr_utility.set_location( l_proc, 95);
933 hr_utility.set_location(' l_lf_evt_ocrd_date ' ||l_lf_evt_ocrd_date, 95);
934 hr_utility.set_location(' l_effective_start_date ' ||l_effective_start_date, 95);
935 hr_utility.set_location(' l_dtctd_dt ' ||l_dtctd_dt, 95);
936 l_ptnl_id := 0;
937 l_ovn :=null;
938 --
939 if l_trigger then
940 if l_type = 'P' then
941 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
942 fetch le_exists into l_le_exists;
943 if le_exists%notfound then
944 hr_utility.set_location(l_proc, 96);
945 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
946 ---ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
947 (p_validate => false
948 ,p_ptnl_ler_for_per_id => l_ptnl_id
949 ,p_ntfn_dt => l_system_date
950 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
951 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
952 ,p_ler_id => l_ler_id
953 ,p_ler_typ_cd => l_typ_cd
954 ,p_person_id => p_new.person_id
955 ,p_business_group_Id =>p_new.business_group_id
956 ,p_object_version_number => l_ovn
957 ,p_effective_date => l_effective_start_date
958 ,p_dtctd_dt => nvl(l_dtctd_dt, l_effective_start_date)
959 );
960 end if;
961 close le_exists;
962 elsif l_type = 'R' then
963 hr_utility.set_location(l_proc, 97);
964 open le_exists(p_new.contact_person_id,l_ler_id,l_lf_evt_ocrd_date);
965 fetch le_exists into l_le_exists;
966 if le_exists%notfound then
967 hr_utility.set_location(l_proc, 98);
968
969 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
970 ---ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
971 (p_validate => false
972 ,p_ptnl_ler_for_per_id => l_ptnl_id
973 ,p_ntfn_dt => l_system_date
974 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
975 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
976 ,p_ler_id => l_ler_id
977 ,p_ler_typ_cd => l_typ_cd
978 ,p_person_id => p_new.contact_person_id
979 ,p_business_group_Id =>p_new.business_group_id
980 ,p_object_version_number => l_ovn
981 ,p_effective_date => l_effective_start_date
982 ,p_dtctd_dt => nvl(l_dtctd_dt, l_effective_start_date)
983 );
984 end if;
985 l_ptnl_id := 0;
986 l_ovn :=null;
987 close le_exists;
988 end if;
989 --
990 -- reset the variables.
991 --
992 hr_utility.set_location(l_proc, 99);
993 l_changed := FALSE;
994 l_flag := FALSE;
995 l_trigger := TRUE;
996 l_ovn := NULL;
997 hr_utility.set_location( l_proc, 95);
998 --hr_utility.set_location(' l_lf_evt_ocrd_date ' ||l_lf_evt_ocrd_date, 95);
999 --hr_utility.set_location(' l_effective_start_date ' ||l_effective_start_date, 95);
1000 --hr_utility.set_location(' l_dtctd_dt ' ||l_dtctd_dt, 95);
1001 --hr_utility.set_location(' l_session_date ' ||l_session_date, 95);
1002
1003 -- l_effective_start_date := l_session_date; Commented part of 3105696 bug fix
1004 -- hr_utility.set_location(' l_effective_start_date ' ||l_effective_start_date, 567);
1005 l_dtctd_dt := null;
1006 end if;
1007 close get_ler_col;
1008 end loop;
1009 hr_utility.set_location(l_proc, 100);
1010 close get_ler;
1011 hr_utility.set_location('Leaving: '||l_proc, 999);
1012 --
1013
1014 end;
1015 end ben_con_ler;