[Home] [Help]
PACKAGE BODY: APPS.BEN_ASG_LER
Source
1 package body ben_asg_ler as
2 /* $Header: beasgtrg.pkb 120.0 2005/05/28 00:29:52 appldev noship $*/
3 procedure ler_chk(p_old IN g_asg_ler_rec
4 ,p_new IN g_asg_ler_rec
5 ,p_effective_date in date default null ) is
6 --
7 l_session_date DATE;
8 l_system_date DATE;
9 --
10 cursor get_session_date IS
11 select effective_date
12 from fnd_sessions
13 where session_id = userenv('SESSIONID');
14 --
15 cursor get_system_date IS
16 select trunc(sysdate)
17 from dual;
18 --
19 cursor get_ler(l_status varchar2) is
20 select ler.ler_id
21 , ler.typ_cd
22 , ler.ocrd_dt_det_cd
23 from ben_ler_f ler
24 where ler.business_group_id = p_new.business_group_id
25 and l_session_date between ler.effective_start_date
26 and ler.effective_end_date -- For Bug 3299709
27 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
28 and ((exists
29 (select 1
30 from ben_per_info_chg_cs_ler_f psl
31 , ben_ler_per_info_cs_ler_f lpl
32 where source_table = 'PER_ALL_ASSIGNMENTS_F'
33 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
34 and lpl.business_group_id = psl.business_group_id
35 and lpl.business_group_id = ler.business_group_id
36 and l_session_date between psl.effective_start_date
37 and psl.effective_end_date
38 and l_session_date between lpl.effective_start_date
39 and lpl.effective_end_date -- For Bug 3299709
40 and lpl.ler_id = ler.ler_id)
41 )
42 OR (exists
43 (select 1
44 from ben_rltd_per_chg_cs_ler_f rpc
45 , ben_ler_rltd_per_cs_ler_f lrp
46 where source_table = 'PER_ALL_ASSIGNMENTS_F'
47 and lrp.business_group_id = rpc.business_group_id
48 and lrp.business_group_id = ler.business_group_id
49 and l_session_date between rpc.effective_start_date
50 and rpc.effective_end_date
51 and l_session_date between lrp.effective_start_date
52 and lrp.effective_end_date -- For Bug 3299709
53 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
54 and lrp.ler_id = ler.ler_id)
55 ))
56 order by ler.ler_id;
57
58 cursor get_ler_col(p_ler_id IN NUMBER) is
59 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
60 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
61 where lpl.ler_id = p_ler_id and
62 lpl.business_group_id = p_new.business_group_id
63 and lpl.business_group_id = psl.business_group_id
64 and l_session_date between psl.effective_start_date
65 and psl.effective_end_date
66 and l_session_date between lpl.effective_start_date
67 and lpl.effective_end_date -- For Bug 3299709
68 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
69 and source_table = 'PER_ALL_ASSIGNMENTS_F'
70 UNION
71 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
72 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
73 where lrp.ler_id = p_ler_id and
74 lrp.business_group_id = p_new.business_group_id
75 and lrp.business_group_id = rpc.business_group_id
76 and l_session_date between rpc.effective_start_date
77 and rpc.effective_end_date
78 and l_session_date between lrp.effective_start_date
79 and lrp.effective_end_date -- For Bug 3299709
80 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
81 and source_table = 'PER_ALL_ASSIGNMENTS_F'
82 order by 1;
83 --
84 cursor le_exists(p_person_id in number
85 ,p_ler_id in number
86 ,p_lf_evt_ocrd_dt in date) is
87 select 'Y'
88 from ben_ptnl_ler_for_per
89 where person_id = p_person_id
90 and ler_id = p_ler_id
91 and ptnl_ler_for_per_stat_cd = 'DTCTD'
92 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
93 --
94 cursor get_contacts(p_person_id in number) is
95 select person_id
96 from per_contact_relationships
97 where contact_person_id = p_person_id
98 and business_group_id = p_new.business_group_id
99 and l_session_date between nvl(date_start,l_session_date)
100 and nvl(date_end,l_session_date)
101 and personal_flag = 'Y'
102 order by person_id;
103 --
104 l_changed BOOLEAN;
105 l_ler_id NUMBER;
106 l_typ_cd ben_ler_f.typ_cd%type ;
107 l_ocrd_dt_cd VARCHAR2(30);
108 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
109 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
110 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
111 l_per_info_chg_cs_ler_rl number;
112 l_rule_output VARCHAR2(1);
113 l_ovn NUMBER;
114 l_ptnl_id NUMBER;
115 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
116 l_effective_start_date DATE ;
117 --l_session_date DATE ;
118 l_lf_evt_ocrd_date DATE ;
119 l_le_exists VARCHAR2(1);
120 l_mnl_dt date;
121 l_dtctd_dt date;
122 l_procd_dt date;
123 l_unprocd_dt date;
124 l_voidd_dt date;
125 l_type VARCHAR2(1);
126 l_hld_person_id NUMBER;
127 --
128 l_bool BOOLEAN;
129 l_status VARCHAR2(1);
130 l_industry VARCHAR2(1);
131 l_col_new_val VARCHAR2(1000);
132 l_col_old_val varchar2(1000);
133 --
134 l_rule_overrides_flag VARCHAR2(1);
135 l_chg_mandatory_cd VARCHAR2(1);
136 l_trigger boolean := TRUE;
137 --
138 --
139 begin
140 -- Bug 3320133
141 benutils.set_data_migrator_mode;
142 if hr_general.g_data_migrator_mode in ( 'Y','P') then
143 --
144 return;
145 --
146 end if;
147 --
148 -- End of Bug 3320133
149
150 l_bool :=fnd_installation.get(appl_id => 805
151 ,dep_appl_id =>805
152 ,status => l_status
153 ,industry => l_industry);
154
155 hr_utility.set_location(' Entering: ben_asg_trigger', 10);
156 l_changed := FALSE;
157 --
158 if p_effective_date is not null then
159 --
160 l_session_date := p_effective_date ;
161 --
162 else
163 --
164 open get_session_date;
165 fetch get_session_date into l_session_date;
166 close get_session_date;
167 --
168 end if;
169 --
170 /*
171 open get_session_date;
172 fetch get_session_date into l_session_date;
173 close get_session_date;
174 */
175 open get_system_date;
176 fetch get_system_date into l_system_date;
177 close get_system_date;
178 l_effective_start_date := l_session_date;
179 -- l_lf_evt_ocrd_date := l_session_date;
180 hr_utility.set_location(' ben_asg_trigger', 20);
181 open get_ler(l_status);
182 loop
183 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
184 exit when get_ler%notfound;
185 l_trigger := TRUE;
186 --
187 if l_ocrd_dt_cd is null then
188 l_lf_evt_ocrd_date := p_new.effective_start_date;
189 else
190 --
191 -- Call the common date procedure.
192 --
193 ben_determine_date.main
194 (p_date_cd => l_ocrd_dt_cd
195 ,p_effective_date => p_new.effective_start_date
196 ,p_lf_evt_ocrd_dt => p_new.effective_start_date
197 ,p_returned_date => l_lf_evt_ocrd_date
198 );
199 end if;
200 --
201 open get_ler_col(l_ler_id);
202 loop
203 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;
204 exit when get_ler_col%NOTFOUND;
205 if get_ler_col%ROWCOUNT =1 then
206 l_changed := TRUE;
207 end if;
208 l_changed := TRUE;
209 hr_utility.set_location(' ben_asg_trigger', 50);
210 hr_utility.set_location('LER '||l_ler_id, 20);
211 hr_utility.set_location('COLUMN '||l_column, 20);
212 hr_utility.set_location('NEWVAL '||l_new_val, 20);
213 hr_utility.set_location('OLDVAL '||l_old_val, 20);
214 hr_utility.set_location('TYPE '||l_type, 20);
215 hr_utility.set_location('leod '||l_lf_evt_ocrd_date, 20);
216 --
217 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
218 -- If it returns Y, then see the applicability of the data
219 -- changes based on new and old values.
220 --
221 l_rule_output := 'Y';
222 --
223 if l_per_info_chg_cs_ler_rl is not null then
224 --
225 if l_column = 'PERSON_ID' then
226 l_col_old_val := to_char(p_old.PERSON_ID);
227 l_col_new_val := to_char(p_new.PERSON_ID);
228 end if;
229 --
230 if l_column = 'ASSIGNMENT_ID' then
231 l_col_old_val := to_char(p_old.ASSIGNMENT_ID);
232 l_col_new_val := to_char(p_new.ASSIGNMENT_ID);
233 end if;
234 --
235 if l_column = 'BUSINESS_GROUP_ID' then
236 l_col_old_val := to_char(p_old.BUSINESS_GROUP_ID);
237 l_col_new_val := to_char(p_new.BUSINESS_GROUP_ID);
238 end if;
239 --
240 if l_column = 'POSITION_ID' then
241 l_col_old_val := to_char(p_old.POSITION_ID);
242 l_col_new_val := to_char(p_new.POSITION_ID);
243 end if;
244 --
245 if l_column = 'PAY_BASIS_ID' then
246 l_col_old_val := to_char(p_old.PAY_BASIS_ID);
247 l_col_new_val := to_char(p_new.PAY_BASIS_ID);
248 end if;
249 --
250 if l_column = 'CHANGE_REASON' then
251 l_col_old_val := p_old.CHANGE_REASON;
252 l_col_new_val := p_new.CHANGE_REASON;
253 end if;
254 --
255 if l_column = 'FREQUENCY' then
256 l_col_old_val := p_old.FREQUENCY;
257 l_col_new_val := p_new.FREQUENCY;
258 end if;
259 --
260 if l_column = 'LABOUR_UNION_MEMBER_FLAG' then
261 l_col_old_val := p_old.LABOUR_UNION_MEMBER_FLAG;
262 l_col_new_val := p_new.LABOUR_UNION_MEMBER_FLAG;
263 end if;
264 --
265 if l_column = 'PEOPLE_GROUP_ID' then
266 l_col_old_val := to_char(p_old.PEOPLE_GROUP_ID);
267 l_col_new_val := to_char(p_new.PEOPLE_GROUP_ID);
268 end if;
269 --
270 if l_column = 'ASS_ATTRIBUTE1' then
271 l_col_old_val := p_old.ASS_ATTRIBUTE1;
272 l_col_new_val := p_new.ASS_ATTRIBUTE1;
273 end if;
274 --
275 if l_column = 'ASS_ATTRIBUTE2' then
276 l_col_old_val := p_old.ASS_ATTRIBUTE2;
277 l_col_new_val := p_new.ASS_ATTRIBUTE2;
278 end if;
279 --
280 if l_column = 'ASS_ATTRIBUTE3' then
281 l_col_old_val := p_old.ASS_ATTRIBUTE3;
282 l_col_new_val := p_new.ASS_ATTRIBUTE3;
283 end if;
284 --
285 if l_column = 'ASS_ATTRIBUTE4' then
286 l_col_old_val := p_old.ASS_ATTRIBUTE4;
287 l_col_new_val := p_new.ASS_ATTRIBUTE4;
288 end if;
289 --
290 if l_column = 'ASS_ATTRIBUTE5' then
291 l_col_old_val := p_old.ASS_ATTRIBUTE5;
292 l_col_new_val := p_new.ASS_ATTRIBUTE5;
293 end if;
294 --
295 if l_column = 'ASS_ATTRIBUTE6' then
296 l_col_old_val := p_old.ASS_ATTRIBUTE6;
297 l_col_new_val := p_new.ASS_ATTRIBUTE6;
298 end if;
299 --
300 if l_column = 'ASS_ATTRIBUTE7' then
301 l_col_old_val := p_old.ASS_ATTRIBUTE7;
302 l_col_new_val := p_new.ASS_ATTRIBUTE7;
303 end if;
304 --
305 if l_column = 'ASS_ATTRIBUTE8' then
306 l_col_old_val := p_old.ASS_ATTRIBUTE8;
307 l_col_new_val := p_new.ASS_ATTRIBUTE8;
308 end if;
309 --
310 if l_column = 'ASS_ATTRIBUTE9' then
311 l_col_old_val := p_old.ASS_ATTRIBUTE9;
312 l_col_new_val := p_new.ASS_ATTRIBUTE9;
313 end if;
314 --
315 if l_column = 'ASS_ATTRIBUTE10' then
316 l_col_old_val := p_old.ASS_ATTRIBUTE10;
317 l_col_new_val := p_new.ASS_ATTRIBUTE10;
318 end if;
319 --
320 if l_column = 'ASS_ATTRIBUTE11' then
321 l_col_old_val := p_old.ASS_ATTRIBUTE11;
322 l_col_new_val := p_new.ASS_ATTRIBUTE11;
323 end if;
324 --
325 if l_column = 'ASS_ATTRIBUTE12' then
326 l_col_old_val := p_old.ASS_ATTRIBUTE12;
327 l_col_new_val := p_new.ASS_ATTRIBUTE12;
328 end if;
329 --
330 if l_column = 'ASS_ATTRIBUTE13' then
331 l_col_old_val := p_old.ASS_ATTRIBUTE13;
332 l_col_new_val := p_new.ASS_ATTRIBUTE13;
333 end if;
334 --
335 if l_column = 'ASS_ATTRIBUTE14' then
336 l_col_old_val := p_old.ASS_ATTRIBUTE14;
337 l_col_new_val := p_new.ASS_ATTRIBUTE14;
338 end if;
339 --
340 if l_column = 'ASS_ATTRIBUTE15' then
341 l_col_old_val := p_old.ASS_ATTRIBUTE15;
342 l_col_new_val := p_new.ASS_ATTRIBUTE15;
343 end if;
344 --
345 if l_column = 'ASS_ATTRIBUTE16' then
346 l_col_old_val := p_old.ASS_ATTRIBUTE16;
347 l_col_new_val := p_new.ASS_ATTRIBUTE16;
348 end if;
349 --
350 if l_column = 'ASS_ATTRIBUTE17' then
351 l_col_old_val := p_old.ASS_ATTRIBUTE17;
352 l_col_new_val := p_new.ASS_ATTRIBUTE17;
353 end if;
354 --
355 if l_column = 'ASS_ATTRIBUTE18' then
356 l_col_old_val := p_old.ASS_ATTRIBUTE18;
357 l_col_new_val := p_new.ASS_ATTRIBUTE18;
358 end if;
359 --
360 if l_column = 'ASS_ATTRIBUTE19' then
361 l_col_old_val := p_old.ASS_ATTRIBUTE19;
362 l_col_new_val := p_new.ASS_ATTRIBUTE19;
363 end if;
364 --
365 if l_column = 'ASS_ATTRIBUTE20' then
366 l_col_old_val := p_old.ASS_ATTRIBUTE20;
367 l_col_new_val := p_new.ASS_ATTRIBUTE20;
368 end if;
369 --
370 if l_column = 'ASS_ATTRIBUTE21' then
371 l_col_old_val := p_old.ASS_ATTRIBUTE21;
372 l_col_new_val := p_new.ASS_ATTRIBUTE21;
373 end if;
374 --
375 if l_column = 'ASS_ATTRIBUTE22' then
376 l_col_old_val := p_old.ASS_ATTRIBUTE22;
377 l_col_new_val := p_new.ASS_ATTRIBUTE22;
378 end if;
379 --
380 if l_column = 'ASS_ATTRIBUTE23' then
381 l_col_old_val := p_old.ASS_ATTRIBUTE23;
382 l_col_new_val := p_new.ASS_ATTRIBUTE23;
383 end if;
384 --
385 if l_column = 'ASS_ATTRIBUTE24' then
386 l_col_old_val := p_old.ASS_ATTRIBUTE24;
387 l_col_new_val := p_new.ASS_ATTRIBUTE24;
388 end if;
389 --
390 if l_column = 'ASS_ATTRIBUTE25' then
391 l_col_old_val := p_old.ASS_ATTRIBUTE25;
392 l_col_new_val := p_new.ASS_ATTRIBUTE25;
393 end if;
394 --
395 if l_column = 'ASS_ATTRIBUTE26' then
396 l_col_old_val := p_old.ASS_ATTRIBUTE26;
397 l_col_new_val := p_new.ASS_ATTRIBUTE26;
398 end if;
399 --
400 if l_column = 'ASS_ATTRIBUTE27' then
401 l_col_old_val := p_old.ASS_ATTRIBUTE27;
402 l_col_new_val := p_new.ASS_ATTRIBUTE27;
403 end if;
404 --
405 if l_column = 'ASS_ATTRIBUTE28' then
406 l_col_old_val := p_old.ASS_ATTRIBUTE28;
407 l_col_new_val := p_new.ASS_ATTRIBUTE28;
408 end if;
409 --
410 if l_column = 'ASS_ATTRIBUTE29' then
414 --
411 l_col_old_val := p_old.ASS_ATTRIBUTE29;
412 l_col_new_val := p_new.ASS_ATTRIBUTE29;
413 end if;
415 if l_column = 'ASS_ATTRIBUTE30' then
416 l_col_old_val := p_old.ASS_ATTRIBUTE30;
417 l_col_new_val := p_new.ASS_ATTRIBUTE30;
418 end if;
419 --
420 if l_column = 'EFFECTIVE_START_DATE' then
421 l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
422 l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS');
423 end if;
424 --
425 if l_column = 'EFFECTIVE_END_DATE' then
426 l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
427 l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS');
428 end if;
429 --
430 if l_column = 'ORGANIZATION_ID' then
431 l_col_old_val := to_char(p_old.ORGANIZATION_ID);
432 l_col_new_val := to_char(p_new.ORGANIZATION_ID);
433 end if;
434 --
435 if l_column = 'GRADE_ID' then
436 l_col_old_val := to_char(p_old.GRADE_ID);
437 l_col_new_val := to_char(p_new.GRADE_ID);
438 end if;
439 --
440 if l_column = 'JOB_ID' then
441 l_col_old_val := to_char(p_old.JOB_ID);
442 l_col_new_val := to_char(p_new.JOB_ID);
443 end if;
444 --
445 if l_column = 'PAYROLL_ID' then
446 l_col_old_val := to_char(p_old.PAYROLL_ID);
447 l_col_new_val := to_char(p_new.PAYROLL_ID);
448 end if;
449 --
450 if l_column = 'LOCATION_ID' then
451 l_col_old_val := to_char(p_old.LOCATION_ID);
452 l_col_new_val := to_char(p_new.LOCATION_ID);
453 end if;
454 --
455 if l_column = 'ASSIGNMENT_STATUS_TYPE_ID' then
456 l_col_old_val := to_char(p_old.ASSIGNMENT_STATUS_TYPE_ID);
457 l_col_new_val := to_char(p_new.ASSIGNMENT_STATUS_TYPE_ID);
458 end if;
459 --
460 if l_column = 'ASSIGNMENT_TYPE' then
461 l_col_old_val := p_old.ASSIGNMENT_TYPE;
462 l_col_new_val := p_new.ASSIGNMENT_TYPE;
463 end if;
464 --
465 if l_column = 'PRIMARY_FLAG' then
466 l_col_old_val := p_old.PRIMARY_FLAG;
467 l_col_new_val := p_new.PRIMARY_FLAG;
468 end if;
469 --
470 if l_column = 'EMPLOYMENT_CATEGORY' then
471 l_col_old_val := p_old.EMPLOYMENT_CATEGORY;
472 l_col_new_val := p_new.EMPLOYMENT_CATEGORY;
473 end if;
474 --
475 if l_column = 'BARGAINING_UNIT_CODE' then
476 l_col_old_val := p_old.BARGAINING_UNIT_CODE;
477 l_col_new_val := p_new.BARGAINING_UNIT_CODE;
478 end if;
479 --
480 if l_column = 'HOURLY_SALARIED_CODE' then
481 l_col_old_val := p_old.HOURLY_SALARIED_CODE;
482 l_col_new_val := p_new.HOURLY_SALARIED_CODE;
483 end if;
484 --
485 if l_column = 'NORMAL_HOURS' then
486 l_col_old_val := p_old.NORMAL_HOURS;
487 l_col_new_val := p_new.NORMAL_HOURS;
488 end if;
489 --
490 benutils.exec_rule(
491 p_formula_id => l_per_info_chg_cs_ler_rl,
492 p_effective_date => l_session_date,
493 p_lf_evt_ocrd_dt => null,
494 p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
495 p_person_id => nvl(p_new.person_id, p_old.person_id),
496 p_new_value => l_col_new_val,
497 p_old_value => l_col_old_val,
498 p_column_name => l_column,
499 p_param5 => 'BEN_ASG_IN_EFFECTIVE_START_DATE',
500 p_param5_value => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
501 p_param6 => 'BEN_ASG_IO_EFFECTIVE_START_DATE',
502 p_param6_value => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
503 p_param7 => 'BEN_ASG_IN_EFFECTIVE_END_DATE',
504 p_param7_value => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
505 p_param8 => 'BEN_ASG_IO_EFFECTIVE_END_DATE',
506 p_param8_value => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
507 p_param9 => 'BEN_ASG_IN_ORGANIZATION_ID',
508 p_param9_value => to_char(p_new.ORGANIZATION_ID),
509 p_param10 => 'BEN_ASG_IO_ORGANIZATION_ID',
510 p_param10_value => to_char(p_old.ORGANIZATION_ID),
511 p_param11 => 'BEN_ASG_IN_GRADE_ID',
512 p_param11_value => to_char(p_new.GRADE_ID),
513 p_param12 => 'BEN_ASG_IO_GRADE_ID',
514 p_param12_value => to_char(p_old.GRADE_ID),
515 p_param13 => 'BEN_ASG_IN_JOB_ID',
516 p_param13_value => to_char(p_new.JOB_ID),
517 p_param14 => 'BEN_ASG_IO_JOB_ID',
518 p_param14_value => to_char(p_old.JOB_ID),
519 p_param15 => 'BEN_ASG_IN_PAYROLL_ID',
520 p_param15_value => to_char(p_new.PAYROLL_ID),
521 p_param16 => 'BEN_ASG_IO_PAYROLL_ID',
525 p_param18 => 'BEN_ASG_IO_LOCATION_ID',
522 p_param16_value => to_char(p_old.PAYROLL_ID),
523 p_param17 => 'BEN_ASG_IN_LOCATION_ID',
524 p_param17_value => to_char(p_new.LOCATION_ID),
526 p_param18_value => to_char(p_old.LOCATION_ID),
527 p_param19 => 'BEN_ASG_IN_ASSIGNMENT_STATUS_TYPE_ID',
528 p_param19_value => to_char(p_new.ASSIGNMENT_STATUS_TYPE_ID),
529 p_param20 => 'BEN_ASG_IO_ASSIGNMENT_STATUS_TYPE_ID',
530 p_param20_value => to_char(p_old.ASSIGNMENT_STATUS_TYPE_ID),
531 p_param21 => 'BEN_ASG_IN_ASSIGNMENT_TYPE',
532 p_param21_value => p_new.ASSIGNMENT_TYPE,
533 p_param22 => 'BEN_ASG_IO_ASSIGNMENT_TYPE',
534 p_param22_value => p_old.ASSIGNMENT_TYPE,
535 p_param23 => 'BEN_ASG_IN_PRIMARY_FLAG',
536 p_param23_value => p_new.PRIMARY_FLAG,
537 p_param24 => 'BEN_ASG_IO_PRIMARY_FLAG',
538 p_param24_value => p_old.PRIMARY_FLAG,
539 p_param25 => 'BEN_ASG_IN_EMPLOYMENT_CATEGORY',
540 p_param25_value => p_new.EMPLOYMENT_CATEGORY,
541 p_param26 => 'BEN_ASG_IO_EMPLOYMENT_CATEGORY',
542 p_param26_value => p_old.EMPLOYMENT_CATEGORY,
543 p_param27 => 'BEN_ASG_IN_BARGAINING_UNIT_CODE',
544 p_param27_value => p_new.BARGAINING_UNIT_CODE,
545 p_param28 => 'BEN_ASG_IO_BARGAINING_UNIT_CODE',
546 p_param28_value => p_old.BARGAINING_UNIT_CODE,
547 p_param29 => 'BEN_ASG_IN_HOURLY_SALARIED_CODE',
548 p_param29_value => p_new.HOURLY_SALARIED_CODE,
549 p_param30 => 'BEN_ASG_IO_HOURLY_SALARIED_CODE',
550 p_param30_value => p_old.HOURLY_SALARIED_CODE,
551 p_param31 => 'BEN_ASG_IN_NORMAL_HOURS',
552 p_param31_value => p_new.NORMAL_HOURS,
553 p_param32 => 'BEN_ASG_IO_NORMAL_HOURS',
554 p_param32_value => p_old.NORMAL_HOURS,
555 p_param33 => 'BEN_IV_LER_ID', /* Bug 3891096 */
556 p_param33_value => to_char(l_ler_id),
557 p_pk_id => to_char(p_new.assignment_id),
558 p_ret_val => l_rule_output);
559 --
560 end if;
561 --
562
563 --
564 if l_column = 'ASSIGNMENT_STATUS_TYPE_ID' then
565 l_changed := (benutils.column_changed(p_old.assignment_status_type_id
566 ,p_new.assignment_status_type_id,l_new_val) AND
567 benutils.column_changed(p_new.assignment_status_type_id
568 ,p_old.assignment_status_type_id,l_old_val) AND
569 (l_changed));
570 end if;
571 --
572 if l_column = 'ORGANIZATION_ID' then
573 l_changed := (benutils.column_changed(p_old.organization_id
574 ,p_new.organization_id,l_new_val) AND
575 benutils.column_changed(p_new.organization_id
576 ,p_old.organization_id,l_old_val) AND
577 (l_changed));
578 end if;
579 --
580 if l_column = 'ASSIGNMENT_TYPE' then
581 l_changed := (benutils.column_changed(p_old.assignment_type
582 ,p_new.assignment_type,l_new_val) AND
583 benutils.column_changed(p_new.assignment_type
584 ,p_old.assignment_type,l_old_val) AND
585 (l_changed));
586 end if;
587 --
588 if l_column = 'PRIMARY_FLAG' then
589 l_changed := (benutils.column_changed(p_old.primary_flag
590 ,p_new.primary_flag,l_new_val) AND
591 benutils.column_changed(p_new.primary_flag
592 ,p_old.primary_flag,l_old_val) AND
593 (l_changed));
594 end if;
595 --
596 if l_column = 'CHANGE_REASON' then
597 l_changed := (benutils.column_changed(p_old.change_reason
598 ,p_new.change_reason,l_new_val) AND
599 benutils.column_changed(p_new.change_reason
600 ,p_old.change_reason,l_old_val) AND
601 (l_changed));
602 end if;
603 --
604 if l_column = 'EMPLOYMENT_CATEGORY' then
605 l_changed := (benutils.column_changed(p_old.employment_category
606 ,p_new.employment_category,l_new_val) AND
607 benutils.column_changed(p_new.employment_category
608 ,p_old.employment_category,l_old_val) AND
609 (l_changed));
610 hr_utility.set_location('NEW EC'||p_new.employment_category, 50);
611 hr_utility.set_location('OLD EC'||p_old.employment_category, 50);
612 end if;
613 if l_column = 'FREQUENCY' then
614 l_changed := (benutils.column_changed(p_old.frequency
615 ,p_new.frequency,l_new_val) AND
616 benutils.column_changed(p_new.frequency
620 --
617 ,p_old.frequency,l_old_val) AND
618 (l_changed));
619 end if;
621 if l_column = 'GRADE_ID' then
622 l_changed := (benutils.column_changed(p_old.grade_id
623 ,p_new.grade_id,l_new_val) AND
624 benutils.column_changed(p_new.grade_id
625 ,p_old.grade_id,l_old_val) AND
626 (l_changed));
627 end if;
628 --
629 if l_column = 'JOB_ID' then
630 l_changed := (benutils.column_changed(p_old.job_id
631 ,p_new.job_id,l_new_val) AND
632 benutils.column_changed(p_new.job_id
633 ,p_old.job_id,l_old_val) AND
634 (l_changed));
635 end if;
636 --
637 if l_column = 'POSITION_ID' then
638 l_changed := (benutils.column_changed(p_old.position_id
639 ,p_new.position_id,l_new_val) AND
640 benutils.column_changed(p_new.position_id
641 ,p_old.position_id,l_old_val) AND
642 (l_changed));
643 end if;
644 --
645 if l_column = 'LOCATION_ID' then
646 l_changed := (benutils.column_changed(p_old.location_id
647 ,p_new.location_id,l_new_val) AND
648 benutils.column_changed(p_new.location_id
649 ,p_old.location_id,l_old_val) AND
650 (l_changed));
651
652 -- When called from Termination, do not trigger Location LE
653 if l_changed and not ben_asg_ins.g_trgr_loc_chg then --Bug 2666342
654 l_changed := FALSE;
655 end if;
656
657 end if;
658 --
659 if l_column = 'NORMAL_HOURS' then
660 l_changed := (benutils.column_changed(p_old.normal_hours
661 ,p_new.normal_hours,l_new_val) AND
662 benutils.column_changed(p_new.normal_hours
663 ,p_old.normal_hours,l_old_val) AND
664 (l_changed));
665 end if;
666 --
667 if l_column = 'PAYROLL_ID' then
668 l_changed := (benutils.column_changed(p_old.payroll_id
669 ,p_new.payroll_id,l_new_val) AND
670 benutils.column_changed(p_new.payroll_id
671 ,p_old.payroll_id,l_old_val) AND
672 (l_changed));
673 end if;
674 --
675 if l_column = 'PAY_BASIS_ID' then
676 l_changed := (benutils.column_changed(p_old.pay_basis_id
677 ,p_new.pay_basis_id,l_new_val) AND
678 benutils.column_changed(p_new.pay_basis_id
679 ,p_old.pay_basis_id,l_old_val) AND
680 (l_changed));
681 end if;
682 --
683 if l_column = 'BARGAINING_UNIT_CODE' then
684 l_changed := (benutils.column_changed(p_old.bargaining_unit_code
685 ,p_new.bargaining_unit_code,l_new_val) AND
686 benutils.column_changed(p_new.bargaining_unit_code
687 ,p_old.bargaining_unit_code,l_old_val) AND
688 (l_changed));
689 end if;
690 --
691 if l_column = 'LABOUR_UNION_MEMBER_FLAG' then
692 l_changed := (benutils.column_changed(p_old.labour_union_member_flag
693 ,p_new.labour_union_member_flag,l_new_val) AND
694 benutils.column_changed(p_new.labour_union_member_flag
695 ,p_old.labour_union_member_flag,l_old_val) AND
696 (l_changed));
697 end if;
698 --
699 if l_column = 'PEOPLE_GROUP_ID' then
700 l_changed := (benutils.column_changed(p_old.people_group_id
701 ,p_new.people_group_id,l_new_val) AND
702 benutils.column_changed(p_new.people_group_id
703 ,p_old.people_group_id,l_old_val) AND
704 (l_changed));
705 end if;
706 --
707 if l_column = 'HOURLY_SALARIED_CODE' then
708 l_changed := (benutils.column_changed(p_old.hourly_salaried_code
709 ,p_new.hourly_salaried_code,l_new_val) AND
710 benutils.column_changed(p_new.hourly_salaried_code
711 ,p_old.hourly_salaried_code,l_old_val) AND
712 (l_changed));
713 end if;
714 --
715 if l_column = 'ASS_ATTRIBUTE1' then
716 l_changed := (benutils.column_changed(p_old.ass_attribute1
717 ,p_new.ass_attribute1,l_new_val) AND
718 benutils.column_changed(p_new.ass_attribute1
719 ,p_old.ass_attribute1,l_old_val) AND
723 if l_column = 'ASS_ATTRIBUTE2' then
720 (l_changed));
721 end if;
722 --
724 l_changed := (benutils.column_changed(p_old.ass_attribute2
725 ,p_new.ass_attribute2,l_new_val) AND
726 benutils.column_changed(p_new.ass_attribute2
727 ,p_old.ass_attribute2,l_old_val) AND
728 (l_changed));
729 end if;
730 --
731 if l_column = 'ASS_ATTRIBUTE3' then
732 l_changed := (benutils.column_changed(p_old.ass_attribute3
733 ,p_new.ass_attribute3,l_new_val) AND
734 benutils.column_changed(p_new.ass_attribute3
735 ,p_old.ass_attribute3,l_old_val) AND
736 (l_changed));
737 end if;
738 --
739 if l_column = 'ASS_ATTRIBUTE4' then
740 l_changed := (benutils.column_changed(p_old.ass_attribute4
741 ,p_new.ass_attribute4,l_new_val) AND
742 benutils.column_changed(p_new.ass_attribute4
743 ,p_old.ass_attribute4,l_old_val) AND
744 (l_changed));
745 end if;
746 --
747 if l_column = 'ASS_ATTRIBUTE5' then
748 l_changed := (benutils.column_changed(p_old.ass_attribute5
749 ,p_new.ass_attribute5,l_new_val) AND
750 benutils.column_changed(p_new.ass_attribute5
751 ,p_old.ass_attribute5,l_old_val) AND
752 (l_changed));
753 end if;
754 --
755 if l_column = 'ASS_ATTRIBUTE6' then
756 l_changed := (benutils.column_changed(p_old.ass_attribute6
757 ,p_new.ass_attribute6,l_new_val) AND
758 benutils.column_changed(p_new.ass_attribute6
759 ,p_old.ass_attribute6,l_old_val) AND
760 (l_changed));
761 end if;
762 --
763 if l_column = 'ASS_ATTRIBUTE7' then
764 l_changed := (benutils.column_changed(p_old.ass_attribute7
765 ,p_new.ass_attribute7,l_new_val) AND
766 benutils.column_changed(p_new.ass_attribute7
767 ,p_old.ass_attribute7,l_old_val) AND
768 (l_changed));
769 end if;
770 --
771 if l_column = 'ASS_ATTRIBUTE8' then
772 l_changed := (benutils.column_changed(p_old.ass_attribute8
773 ,p_new.ass_attribute8,l_new_val) AND
774 benutils.column_changed(p_new.ass_attribute8
775 ,p_old.ass_attribute8,l_old_val) AND
776 (l_changed));
777 end if;
778 --
779 if l_column = 'ASS_ATTRIBUTE9' then
780 l_changed := (benutils.column_changed(p_old.ass_attribute9
781 ,p_new.ass_attribute9,l_new_val) AND
782 benutils.column_changed(p_new.ass_attribute9
783 ,p_old.ass_attribute9,l_old_val) AND
784 (l_changed));
785 end if;
786 --
787 if l_column = 'ASS_ATTRIBUTE10' then
788 l_changed := (benutils.column_changed(p_old.ass_attribute10
789 ,p_new.ass_attribute10,l_new_val) AND
790 benutils.column_changed(p_new.ass_attribute10
791 ,p_old.ass_attribute10,l_old_val) AND
792 (l_changed));
793 end if;
794 --
795 if l_column = 'ASS_ATTRIBUTE11' then
796 l_changed := (benutils.column_changed(p_old.ass_attribute11
797 ,p_new.ass_attribute11,l_new_val) AND
798 benutils.column_changed(p_new.ass_attribute11
799 ,p_old.ass_attribute11,l_old_val) AND
800 (l_changed));
801 end if;
802 --
803 if l_column = 'ASS_ATTRIBUTE12' then
804 l_changed := (benutils.column_changed(p_old.ass_attribute12
805 ,p_new.ass_attribute12,l_new_val) AND
806 benutils.column_changed(p_new.ass_attribute12
807 ,p_old.ass_attribute12,l_old_val) AND
808 (l_changed));
809 end if;
810 --
811 if l_column = 'ASS_ATTRIBUTE13' then
812 l_changed := (benutils.column_changed(p_old.ass_attribute13
813 ,p_new.ass_attribute13,l_new_val) AND
814 benutils.column_changed(p_new.ass_attribute13
815 ,p_old.ass_attribute13,l_old_val) AND
816 (l_changed));
817 end if;
818 --
819 if l_column = 'ASS_ATTRIBUTE14' then
820 l_changed := (benutils.column_changed(p_old.ass_attribute14
824 (l_changed));
821 ,p_new.ass_attribute14,l_new_val) AND
822 benutils.column_changed(p_new.ass_attribute14
823 ,p_old.ass_attribute14,l_old_val) AND
825 end if;
826 --
827 if l_column = 'ASS_ATTRIBUTE15' then
828 l_changed := (benutils.column_changed(p_old.ass_attribute15
829 ,p_new.ass_attribute15,l_new_val) AND
830 benutils.column_changed(p_new.ass_attribute15
831 ,p_old.ass_attribute15,l_old_val) AND
832 (l_changed));
833 end if;
834 --
835 if l_column = 'ASS_ATTRIBUTE16' then
836 l_changed := (benutils.column_changed(p_old.ass_attribute16
837 ,p_new.ass_attribute16,l_new_val) AND
838 benutils.column_changed(p_new.ass_attribute16
839 ,p_old.ass_attribute16,l_old_val) AND
840 (l_changed));
841 end if;
842 --
843 if l_column = 'ASS_ATTRIBUTE17' then
844 l_changed := (benutils.column_changed(p_old.ass_attribute17
845 ,p_new.ass_attribute17,l_new_val) AND
846 benutils.column_changed(p_new.ass_attribute17
847 ,p_old.ass_attribute17,l_old_val) AND
848 (l_changed));
849 end if;
850 --
851 if l_column = 'ASS_ATTRIBUTE18' then
852 l_changed := (benutils.column_changed(p_old.ass_attribute18
853 ,p_new.ass_attribute18,l_new_val) AND
854 benutils.column_changed(p_new.ass_attribute18
855 ,p_old.ass_attribute18,l_old_val) AND
856 (l_changed));
857 end if;
858 --
859 if l_column = 'ASS_ATTRIBUTE19' then
860 l_changed := (benutils.column_changed(p_old.ass_attribute19
861 ,p_new.ass_attribute19,l_new_val) AND
862 benutils.column_changed(p_new.ass_attribute19
863 ,p_old.ass_attribute19,l_old_val) AND
864 (l_changed));
865 end if;
866 --
867 if l_column = 'ASS_ATTRIBUTE20' then
868 l_changed := (benutils.column_changed(p_old.ass_attribute20
869 ,p_new.ass_attribute20,l_new_val) AND
870 benutils.column_changed(p_new.ass_attribute20
871 ,p_old.ass_attribute20,l_old_val) AND
872 (l_changed));
873 end if;
874 --
875 if l_column = 'ASS_ATTRIBUTE21' then
876 l_changed := (benutils.column_changed(p_old.ass_attribute21
877 ,p_new.ass_attribute21,l_new_val) AND
878 benutils.column_changed(p_new.ass_attribute21
879 ,p_old.ass_attribute21,l_old_val) AND
880 (l_changed));
881 end if;
882 --
883 if l_column = 'ASS_ATTRIBUTE22' then
884 l_changed := (benutils.column_changed(p_old.ass_attribute22
885 ,p_new.ass_attribute22,l_new_val) AND
886 benutils.column_changed(p_new.ass_attribute22
887 ,p_old.ass_attribute22,l_old_val) AND
888 (l_changed));
889 end if;
890 --
891 if l_column = 'ASS_ATTRIBUTE23' then
892 l_changed := (benutils.column_changed(p_old.ass_attribute23
893 ,p_new.ass_attribute23,l_new_val) AND
894 benutils.column_changed(p_new.ass_attribute23
895 ,p_old.ass_attribute23,l_old_val) AND
896 (l_changed));
897 end if;
898 --
899 if l_column = 'ASS_ATTRIBUTE24' then
900 l_changed := (benutils.column_changed(p_old.ass_attribute24
901 ,p_new.ass_attribute24,l_new_val) AND
902 benutils.column_changed(p_new.ass_attribute24
903 ,p_old.ass_attribute24,l_old_val) AND
904 (l_changed));
905 end if;
906 --
907 if l_column = 'ASS_ATTRIBUTE25' then
908 l_changed := (benutils.column_changed(p_old.ass_attribute25
909 ,p_new.ass_attribute25,l_new_val) AND
910 benutils.column_changed(p_new.ass_attribute25
911 ,p_old.ass_attribute25,l_old_val) AND
912 (l_changed));
913 end if;
914 --
915 if l_column = 'ASS_ATTRIBUTE26' then
916 l_changed := (benutils.column_changed(p_old.ass_attribute26
917 ,p_new.ass_attribute26,l_new_val) AND
918 benutils.column_changed(p_new.ass_attribute26
922 --
919 ,p_old.ass_attribute26,l_old_val) AND
920 (l_changed));
921 end if;
923 if l_column = 'ASS_ATTRIBUTE27' then
924 l_changed := (benutils.column_changed(p_old.ass_attribute27
925 ,p_new.ass_attribute27,l_new_val) AND
926 benutils.column_changed(p_new.ass_attribute27
927 ,p_old.ass_attribute27,l_old_val) AND
928 (l_changed));
929 end if;
930 --
931 if l_column = 'ASS_ATTRIBUTE28' then
932 l_changed := (benutils.column_changed(p_old.ass_attribute28
933 ,p_new.ass_attribute28,l_new_val) AND
934 benutils.column_changed(p_new.ass_attribute28
935 ,p_old.ass_attribute28,l_old_val) AND
936 (l_changed));
937 end if;
938 --
939 if l_column = 'ASS_ATTRIBUTE29' then
940 l_changed := (benutils.column_changed(p_old.ass_attribute29
941 ,p_new.ass_attribute29,l_new_val) AND
942 benutils.column_changed(p_new.ass_attribute29
943 ,p_old.ass_attribute29,l_old_val) AND
944 (l_changed));
945 end if;
946 --
947 if l_column = 'ASS_ATTRIBUTE30' then
948 l_changed := (benutils.column_changed(p_old.ass_attribute30
949 ,p_new.ass_attribute30,l_new_val) AND
950 benutils.column_changed(p_new.ass_attribute30
951 ,p_old.ass_attribute30,l_old_val) AND
952 (l_changed));
953 end if;
954 --
955
956 --
957 -- Checking the rule output and the rule override flag.
958 -- Whether the rule is mandatory or not, rule output should return 'Y'
959 -- Rule Mandatory flag is just to override the column data change.
960 hr_utility.set_location('Rule Override Flag = '||l_rule_overrides_flag,20);
961 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
962 l_changed := TRUE ;
963 hr_utility.set_location('Rule output = Y, Trigger LE even column change not satisfied', 20.01);
964 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
965 l_changed := l_changed AND TRUE;
966 elsif l_rule_output = 'N' then
967 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
968 l_changed := FALSE;
969 end if;
970
971 hr_utility.set_location('After the rule Check ',20.05);
972 if l_changed then
973 hr_utility.set_location('l_changed TRUE ', 20.1);
974 else
975 hr_utility.set_location('l_changed FALSE ', 20.1);
976 end if;
977 -- Check for Column Mandatory Change
978 -- If column change is mandatory and data change has failed then dont trigger
979 -- If column change is non-mandatory and the data change has passed, then trigger.
980
981 if l_chg_mandatory_cd = 'Y' and not l_changed then
982 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
983 l_changed := FALSE;
984 l_trigger := FALSE;
985 exit;
986 elsif l_chg_mandatory_cd = 'Y' and l_changed then
987 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
988 l_changed := TRUE;
989 -- exit; */
990 elsif l_chg_mandatory_cd = 'N' and l_changed then
991 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
992 l_changed := TRUE;
993 l_trigger := TRUE;
994 exit;
995 end if;
996
997
998 hr_utility.set_location('After the Mandatory code check ',20.05);
999 if l_changed then
1000 hr_utility.set_location(' l_change TRUE ', 20.1);
1001 else
1002 hr_utility.set_location(' l_change FALSE ', 20.1);
1003 end if;
1004 --
1005 /* if not l_changed then
1006 exit;
1007 end if; */
1008 --
1009 end loop;
1010 hr_utility.set_location(' ben_asg_trigger', 30);
1011 if l_changed then
1012 hr_utility.set_location(' Change detected', 30);
1013 end if;
1014 l_ptnl_id := 0;
1015 l_ovn :=null;
1016 if l_trigger then
1017 if l_type = 'P' then
1018 open le_exists(p_new.person_id,l_ler_id,l_lf_evt_ocrd_date);
1019 fetch le_exists into l_le_exists;
1020 if le_exists%notfound then
1021 hr_utility.set_location(' Entering: ben_asg_trigger5', 60);
1022 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1023 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1024 (p_validate => false
1025 ,p_ptnl_ler_for_per_id => l_ptnl_id
1026 ,p_ntfn_dt => l_system_date
1027 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1028 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1029 ,p_ler_id => l_ler_id
1030 ,p_ler_typ_cd => l_typ_cd
1031 ,p_person_id => p_new.person_id
1032 ,p_business_group_Id =>p_new.business_group_id
1033 ,p_object_version_number => l_ovn
1034 ,p_assignment_id => nvl(p_new.ASSIGNMENT_ID, p_old.ASSIGNMENT_ID)
1035 ,p_effective_date => l_effective_start_date
1036 ,p_dtctd_dt => l_effective_start_date);
1037 end if;
1038 close le_exists;
1039 elsif l_type = 'R' then
1040 hr_utility.set_location(' Entering: ben_asg_trigger5-', 65);
1041 open get_contacts(p_new.person_id);
1042 loop
1043 fetch get_contacts into l_hld_person_id;
1044 exit when get_contacts%notfound;
1045 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
1046 fetch le_exists into l_le_exists;
1047 if le_exists%notfound then
1048 hr_utility.set_location(' Entering: ben_asg_trigger5', 60);
1049
1050 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
1051 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1052 (p_validate => false
1053 ,p_ptnl_ler_for_per_id => l_ptnl_id
1054 ,p_ntfn_dt => l_system_date
1055 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1056 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1057 ,p_ler_id => l_ler_id
1058 ,p_ler_typ_cd => l_typ_cd
1059 ,p_person_id => l_hld_person_id
1060 ,p_business_group_Id =>p_new.business_group_id
1061 ,p_object_version_number => l_ovn
1062 ,p_effective_date => l_effective_start_date
1063 ,p_assignment_id => nvl(p_new.ASSIGNMENT_ID, p_old.ASSIGNMENT_ID)
1064 ,p_dtctd_dt => l_effective_start_date);
1065 end if;
1066 l_ptnl_id := 0;
1067 l_ovn :=null;
1068 close le_exists;
1069 end loop;
1070 close get_contacts;
1071 end if;
1072 --
1073 -- reset the variables.
1074 --
1075 hr_utility.set_location(' ben_asg_trigger', 100);
1076 l_changed := FALSE;
1077 l_trigger := TRUE;
1078 l_ovn := NULL;
1079 -- l_effective_start_date := p_new.effective_start_date;
1080 -- l_lf_evt_ocrd_date := p_new.effective_start_date;
1081 end if;
1082 close get_ler_col;
1083 end loop;
1084 hr_utility.set_location(' ben_asg_trigger', 110);
1085 close get_ler;
1086 hr_utility.set_location(' Leaving: ben_asg_trigger', 120);
1087
1088 benutils.set_data_migrator_mode; -- Bug 3320133
1089 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
1090 -- If Pay Basis or Grade is changed then check for Quartile in Grade life event, Bug 2628274
1091 if ( nvl(p_old.grade_id,0) <> nvl(p_new.grade_id,0) ) or
1092 ( nvl(p_old.pay_basis_id,0) <> nvl(p_new.pay_basis_id,0) ) then
1093 ben_pro_ler.qua_in_gr_ler_chk(p_old,p_new,null,null,p_effective_date,'A');
1094 end if;
1095 end if;
1096 end;
1097 end ben_asg_ler;