[Home] [Help]
PACKAGE BODY: APPS.BEN_PRO_LER
Source
1 package body ben_pro_ler as
2 /* $Header: beprotrg.pkb 120.3 2007/03/01 13:28:13 nhunur noship $*/
3
4 --
5 cursor get_session_date IS
6 select effective_date
7 from fnd_sessions
8 where session_id = userenv('SESSIONID');
9 --
10 cursor get_system_date IS
11 select trunc(sysdate)
12 from dual;
13 --
14 cursor le_exists(p_person_id in number
15 ,p_ler_id in number
16 ,p_lf_evt_ocrd_dt in date) is
17 select 'Y'
18 from ben_ptnl_ler_for_per
19 where person_id = p_person_id
20 and ler_id = p_ler_id
21 and ptnl_ler_for_per_stat_cd = 'DTCTD'
22 and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
23 --
24 cursor c_get_person(p_assignment_id in number
25 ,p_effective_date in date)
26 is
27 select a.person_id
28 from per_all_people_f a,
29 per_all_assignments_f asg
30 where a.person_id = asg.person_id
31 and asg.assignment_id = p_assignment_id
32 and asg.business_group_id = a.business_group_id
33 and p_effective_date
34 between a.effective_start_date
35 and a.effective_end_date
36 and p_effective_date
37 between asg.effective_start_date
38 and asg.effective_end_date;
39 --
40
41 procedure ler_chk(p_old IN g_pro_ler_rec
42 ,p_new IN g_pro_ler_rec
43 ,p_effective_date in date ) is
44 --
45 l_session_date DATE;
46 l_system_date DATE;
47
48 --
49 -- Bug 5203589
50 cursor c_old_ppp
51 is
52 select *
53 from per_pay_proposals
54 where pay_proposal_id = p_old.pay_proposal_id;
55 --
56 l_old_ppp_rec c_old_ppp%rowtype;
57 --
58 cursor get_ler(l_status varchar2) is
59 select ler.ler_id
60 , ler.typ_cd
61 , ler.ocrd_dt_det_cd
62 from ben_ler_f ler
63 where ler.business_group_id = p_new.business_group_id
64 and l_session_date
65 between ler.effective_start_date
66 and ler.effective_end_date
67 and ((exists
68 (select 1
69 from ben_per_info_chg_cs_ler_f psl
70 , ben_ler_per_info_cs_ler_f lpl
71 where source_table = 'PER_PAY_PROPOSALS'
72 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
73 and lpl.business_group_id = psl.business_group_id
74 and lpl.business_group_id = ler.business_group_id
75 and l_session_date between psl.effective_start_date
76 and psl.effective_end_date
77 and l_session_date between lpl.effective_start_date
78 and lpl.effective_end_date
79 and lpl.ler_id = ler.ler_id)
80 )
81 OR (exists
82 (select 1
83 from ben_rltd_per_chg_cs_ler_f rpc
84 , ben_ler_rltd_per_cs_ler_f lrp
85 where source_table = 'PER_PAY_PROPOSALS'
86 and lrp.business_group_id = rpc.business_group_id
87 and lrp.business_group_id = ler.business_group_id
88 and l_session_date between rpc.effective_start_date
89 and rpc.effective_end_date
90 and l_session_date between lrp.effective_start_date
91 and lrp.effective_end_date
92 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
93 and lrp.ler_id = ler.ler_id)
94 ))
95 order by ler.ler_id;
96 --
97 cursor get_ler_col(p_ler_id IN NUMBER) is
98 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
99 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
100 where lpl.ler_id = p_ler_id and
101 lpl.business_group_id = p_new.business_group_id
102 and lpl.business_group_id = psl.business_group_id
103 and l_session_date between psl.effective_start_date
104 and psl.effective_end_date
105 and l_session_date between lpl.effective_start_date
106 and lpl.effective_end_date
107 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
108 and source_table = 'PER_PAY_PROPOSALS'
109 UNION
110 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
111 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
112 where lrp.ler_id = p_ler_id and
113 lrp.business_group_id = p_new.business_group_id
114 and lrp.business_group_id = rpc.business_group_id
115 and l_session_date between rpc.effective_start_date
116 and rpc.effective_end_date
117 and l_session_date between lrp.effective_start_date
118 and lrp.effective_end_date
119 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
120 and source_table = 'PER_PAY_PROPOSALS'
121 order by 1;
122 --
123 cursor get_contacts(p_person_id in number) is
124 select person_id
125 from per_contact_relationships
126 where contact_person_id = p_person_id
127 and business_group_id = p_new.business_group_id
128 and l_session_date between nvl(date_start,l_session_date)
129 and nvl(date_end,l_session_date)
130 and personal_flag = 'Y'
131 order by person_id;
132 --
133 l_old g_pro_ler_rec; /* Bug 5203589 */
134 l_person_id number;
135 l_changed BOOLEAN;
136 l_ler_id NUMBER;
137 l_typ_cd ben_ler_f.typ_cd%type ;
138 l_ocrd_dt_cd VARCHAR2(30);
139 l_column ben_rltd_per_chg_cs_ler_f.source_column%type; -- VARCHAR2(30);
140 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type; -- VARCHAR2(30);
141 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type; -- VARCHAR2(30);
142 l_per_info_chg_cs_ler_rl number;
143 l_ovn NUMBER;
144 l_ptnl_id NUMBER;
145 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
146 l_effective_start_date DATE ;
147 --l_session_date DATE ;
148 l_lf_evt_ocrd_date DATE ;
149 l_le_exists VARCHAR2(1);
150 l_mnl_dt date;
151 l_dtctd_dt date;
152 l_procd_dt date;
153 l_unprocd_dt date;
154 l_voidd_dt date;
155 l_type VARCHAR2(1);
156 l_hld_person_id NUMBER;
157 l_rule_output VARCHAR2(1);
158 --
159 l_bool BOOLEAN;
160 l_status VARCHAR2(1);
161 l_industry VARCHAR2(1);
162 l_col_new_val VARCHAR2(1000); --UTF8
163 l_col_old_val varchar2(1000); --UTF8
164 --
165 l_rule_overrides_flag VARCHAR2(1);
166 l_chg_mandatory_cd VARCHAR2(1);
167 l_trigger boolean := TRUE;
168 --
169 --
170 begin
171 --
172
173 -- Bug 2016857
174 benutils.set_data_migrator_mode;
175 -- Bug 2016857
176
177 if hr_general.g_data_migrator_mode in ( 'Y','P') then
178 --
179 return;
180 --
181 end if;
182 --
183 -- Bug 5203589
184 -- When a new pay proposal is created then PER_PYP_SHD.G_OLD_REC in the calling package peppyrhi.pkb
185 -- do not hold correct values. Somehow PER_PYP_SHD.G_OLD_REC.PAY_PROPOSAL_ID holds correct PK value.
186 --
187 if p_old.PAY_PROPOSAL_ID <> p_new.PAY_PROPOSAL_ID
188 then
189 --
190 open c_old_ppp;
191 --
192 fetch c_old_ppp into l_old_ppp_rec;
193 --
194 close c_old_ppp;
195 --
196 l_old.person_id := p_old.person_id;
197 l_old.BUSINESS_GROUP_ID := l_old_ppp_rec.BUSINESS_GROUP_ID;
198 l_old.PAY_PROPOSAL_ID := l_old_ppp_rec.PAY_PROPOSAL_ID;
199 l_old.OBJECT_VERSION_NUMBER := l_old_ppp_rec.OBJECT_VERSION_NUMBER;
200 l_old.ASSIGNMENT_ID := l_old_ppp_rec.ASSIGNMENT_ID;
201 l_old.EVENT_ID := l_old_ppp_rec.EVENT_ID;
202 l_old.CHANGE_DATE := l_old_ppp_rec.CHANGE_DATE;
203 l_old.LAST_CHANGE_DATE := l_old_ppp_rec.LAST_CHANGE_DATE;
204 l_old.NEXT_PERF_REVIEW_DATE := l_old_ppp_rec.NEXT_PERF_REVIEW_DATE;
205 l_old.NEXT_SAL_REVIEW_DATE := l_old_ppp_rec.NEXT_SAL_REVIEW_DATE;
206 l_old.PERFORMANCE_RATING := l_old_ppp_rec.PERFORMANCE_RATING;
207 l_old.PROPOSAL_REASON := l_old_ppp_rec.PROPOSAL_REASON;
208 l_old.PROPOSED_SALARY_N := l_old_ppp_rec.PROPOSED_SALARY_N;
209 l_old.REVIEW_DATE := l_old_ppp_rec.REVIEW_DATE;
210 l_old.APPROVED := 'N' ; -- l_old_ppp_rec.APPROVED;
211 l_old.MULTIPLE_COMPONENTS := l_old_ppp_rec.MULTIPLE_COMPONENTS;
212 l_old.FORCED_RANKING := l_old_ppp_rec.FORCED_RANKING;
213 l_old.PERFORMANCE_REVIEW_ID := l_old_ppp_rec.PERFORMANCE_REVIEW_ID;
214 l_old.ATTRIBUTE1 := l_old_ppp_rec.ATTRIBUTE1;
215 l_old.ATTRIBUTE2 := l_old_ppp_rec.ATTRIBUTE2;
216 l_old.ATTRIBUTE3 := l_old_ppp_rec.ATTRIBUTE3;
217 l_old.ATTRIBUTE4 := l_old_ppp_rec.ATTRIBUTE4;
218 l_old.ATTRIBUTE5 := l_old_ppp_rec.ATTRIBUTE5;
219 l_old.ATTRIBUTE6 := l_old_ppp_rec.ATTRIBUTE6;
220 l_old.ATTRIBUTE7 := l_old_ppp_rec.ATTRIBUTE7;
221 l_old.ATTRIBUTE8 := l_old_ppp_rec.ATTRIBUTE8;
222 l_old.ATTRIBUTE9 := l_old_ppp_rec.ATTRIBUTE9;
223 l_old.ATTRIBUTE10 := l_old_ppp_rec.ATTRIBUTE10;
224 l_old.ATTRIBUTE11 := l_old_ppp_rec.ATTRIBUTE11;
225 l_old.ATTRIBUTE12 := l_old_ppp_rec.ATTRIBUTE12;
226 l_old.ATTRIBUTE13 := l_old_ppp_rec.ATTRIBUTE13;
227 l_old.ATTRIBUTE14 := l_old_ppp_rec.ATTRIBUTE14;
228 l_old.ATTRIBUTE15 := l_old_ppp_rec.ATTRIBUTE15;
229 l_old.ATTRIBUTE16 := l_old_ppp_rec.ATTRIBUTE16;
230 l_old.ATTRIBUTE17 := l_old_ppp_rec.ATTRIBUTE17;
231 l_old.ATTRIBUTE18 := l_old_ppp_rec.ATTRIBUTE18;
232 l_old.ATTRIBUTE19 := l_old_ppp_rec.ATTRIBUTE19;
233 l_old.ATTRIBUTE20 := l_old_ppp_rec.ATTRIBUTE20;
234 l_old.PROPOSED_SALARY := l_old_ppp_rec.PROPOSED_SALARY;
235 --
236 else
237 --
238 -- Case when pay proposal is updated, then PER_PYP_SHD.G_OLD_REC in the calling package peppyrhi.pkb
239 -- hold correct old values. Hence it is safe to use values from P_OLD
240 --
241 l_old := p_old;
242 --
243 end if;
244 --
245 -- Bug 5203589
246 --
247 /*
248 l_bool :=fnd_installation.get(appl_id => 805
249 ,dep_appl_id =>805
250 ,status => l_status
251 ,industry => l_industry);
252 if l_status = 'I' then
253 */
254 hr_utility.set_location(' Entering: ben_pro_trigger', 10);
255 --
256 if p_new.person_id is null then
257 --
258 open c_get_person(p_new.assignment_id, p_new.change_date);
259 fetch c_get_person into l_person_id;
260 -- p_new.person_id := l_person_id;
261 -- p_old.person_id := l_person_id;
262 close c_get_person;
263 --
264 end if;
265 --
266 l_changed := FALSE;
267 if p_effective_date is not null then
268 l_session_date := p_effective_Date ;
269 Else
270 open get_session_date;
271 fetch get_session_date into l_session_date;
272 close get_session_date;
273 End if ;
274 open get_system_date;
275 fetch get_system_date into l_system_date;
276 close get_system_date;
277 l_effective_start_date := l_session_date;
278 -- l_lf_evt_ocrd_date := l_session_date;
279 hr_utility.set_location(' ben_pro_trigger', 20);
280
281 /* 8888 delete lines.
282 if p_new.date_to is null then
283 l_date_to := l_session_date;
284 else
285 l_date_to := p_new.date_to;
286 end if;
287 8888
288 */
289
290 hr_utility.set_location(' l_system_date:'||to_char(l_system_date), 20);
291 hr_utility.set_location(' l_session_date:'||to_char(l_session_date), 20);
292
293 open get_ler(l_status);
294 loop
295 --
296 fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
297 exit when get_ler%notfound;
298 l_trigger := TRUE;
299
300 hr_utility.set_location('ler '||l_ler_id, 20);
301 hr_utility.set_location('det_cd '||l_ocrd_dt_cd, 20);
302 hr_utility.set_location('DR system date '||l_system_date, 20);
303 --
304 if l_ocrd_dt_cd is null then
305 l_lf_evt_ocrd_date := p_new.change_date;
306 else
307 --
308 -- Call the common date procedure.
309 --
310 ben_determine_date.main
311 (p_date_cd => l_ocrd_dt_cd
312 ,p_effective_date => p_new.change_date
313 ,p_lf_evt_ocrd_dt => p_new.change_date
314 ,p_returned_date => l_lf_evt_ocrd_date
315 );
316 end if;
317 --
318 open get_ler_col(l_ler_id);
319 loop
320 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;
321 exit when get_ler_col%NOTFOUND;
322
323 l_changed := TRUE;
324 if get_ler_col%ROWCOUNT = 1 then
325 l_changed := TRUE;
326 end if;
327 hr_utility.set_location(' ben_pro_trigger', 50);
328 --
329 -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
330 -- If it returns Y, then see the applicability of the data
331 -- changes based on new and old values.
332 --
333 l_rule_output := 'Y';
334 --
335 if l_per_info_chg_cs_ler_rl is not null then
336 --
337 if l_column = 'CHANGE_DATE' then
338 l_col_old_val := to_char(l_old.change_date, 'YYYY/MM/DD HH24:MI:SS');
339 l_col_new_val := to_char(p_new.change_date, 'YYYY/MM/DD HH24:MI:SS');
340 end if;
341 --
342 if l_column = 'LAST_CHANGE_DATE' then
343 l_col_old_val := to_char(l_old.last_change_date, 'YYYY/MM/DD HH24:MI:SS');
344 l_col_new_val := to_char(p_new.last_change_date, 'YYYY/MM/DD HH24:MI:SS');
345 end if;
346 --
347 if l_column = 'NEXT_PERF_REVIEW_DATE' then
348 l_col_old_val := to_char(l_old.next_perf_review_date, 'YYYY/MM/DD HH24:MI:SS');
349 l_col_new_val := to_char(p_new.next_perf_review_date, 'YYYY/MM/DD HH24:MI:SS');
350 end if;
351 --
352 if l_column = 'NEXT_SAL_REVIEW_DATE' then
353 l_col_old_val := to_char(l_old.next_sal_review_date, 'YYYY/MM/DD HH24:MI:SS');
354 l_col_new_val := to_char(p_new.next_sal_review_date, 'YYYY/MM/DD HH24:MI:SS');
355 end if;
356 --
357
358 if l_column = 'PERFORMANCE_RATING' then
359 l_col_old_val := l_old.PERFORMANCE_RATING;
360 l_col_new_val := p_new.PERFORMANCE_RATING;
361 end if;
362 --
363 --
364 if l_column = 'PROPOSAL_REASON' then
365 l_col_old_val := l_old.PROPOSAL_REASON;
366 l_col_new_val := p_new.PROPOSAL_REASON;
367 end if;
368 --
369 if l_column = 'PROPOSED_SALARY_N' then
370 l_col_old_val := to_char(l_old.PROPOSED_SALARY_N);
371 l_col_new_val := to_char(p_new.PROPOSED_SALARY_N);
372 end if;
373 --
374 if l_column = 'REVIEW_DATE' then
375 l_col_old_val := to_char(l_old.REVIEW_date, 'YYYY/MM/DD HH24:MI:SS');
376 l_col_new_val := to_char(p_new.REVIEW_date, 'YYYY/MM/DD HH24:MI:SS');
377 end if;
378 --
379 --
380 if l_column = 'APPROVED' then
381 l_col_old_val := l_old.APPROVED;
382 l_col_new_val := p_new.APPROVED;
383 end if;
384 --
385 --
386 if l_column = 'MULTIPLE_COMPONENTS' then
387 l_col_old_val := l_old.MULTIPLE_COMPONENTS;
388 l_col_new_val := p_new.MULTIPLE_COMPONENTS;
389 end if;
390 --
391 --
392 if l_column = 'FORCED_RANKING' then
393 l_col_old_val := to_char(l_old.FORCED_RANKING);
394 l_col_new_val := to_char(p_new.FORCED_RANKING);
395 end if;
396 --
397 if l_column = 'PERFORMANCE_REVIEW_ID' then
398 l_col_old_val := to_char(l_old.PERFORMANCE_REVIEW_ID);
399 l_col_new_val := to_char(p_new.PERFORMANCE_REVIEW_ID);
400 end if;
401 --
402 if l_column = 'EVENT_ID' then
403 l_col_old_val := to_char(l_old.EVENT_ID);
404 l_col_new_val := to_char(p_new.EVENT_ID);
405 end if;
406 --
407 if l_column = 'PROPOSED_SALARY' then
408 l_col_old_val := l_old.PROPOSED_SALARY;
409 l_col_new_val := p_new.PROPOSED_SALARY;
410 end if;
411 --
412 if l_column = 'ATTRIBUTE1' then
413 l_col_old_val := l_old.ATTRIBUTE1;
414 l_col_new_val := p_new.ATTRIBUTE1;
415 end if;
416 --
417 if l_column = 'ATTRIBUTE2' then
418 l_col_old_val := l_old.ATTRIBUTE2;
419 l_col_new_val := p_new.ATTRIBUTE2;
420 end if;
421 --
422 if l_column = 'ATTRIBUTE3' then
423 l_col_old_val := l_old.ATTRIBUTE3;
424 l_col_new_val := p_new.ATTRIBUTE3;
425 end if;
426 --
427 if l_column = 'ATTRIBUTE4' then
428 l_col_old_val := l_old.ATTRIBUTE4;
429 l_col_new_val := p_new.ATTRIBUTE4;
430 end if;
431 --
432 if l_column = 'ATTRIBUTE5' then
433 l_col_old_val := l_old.ATTRIBUTE5;
434 l_col_new_val := p_new.ATTRIBUTE5;
435 end if;
436 --
437 if l_column = 'ATTRIBUTE6' then
438 l_col_old_val := l_old.ATTRIBUTE6;
439 l_col_new_val := p_new.ATTRIBUTE6;
440 end if;
441 --
442 if l_column = 'ATTRIBUTE7' then
443 l_col_old_val := l_old.ATTRIBUTE7;
444 l_col_new_val := p_new.ATTRIBUTE7;
445 end if;
446 --
447 if l_column = 'ATTRIBUTE8' then
448 l_col_old_val := l_old.ATTRIBUTE8;
449 l_col_new_val := p_new.ATTRIBUTE8;
450 end if;
451 --
452 if l_column = 'ATTRIBUTE9' then
453 l_col_old_val := l_old.ATTRIBUTE9;
454 l_col_new_val := p_new.ATTRIBUTE9;
455 end if;
456 --
457 if l_column = 'ATTRIBUTE10' then
458 l_col_old_val := l_old.ATTRIBUTE10;
459 l_col_new_val := p_new.ATTRIBUTE10;
460 end if;
461 --
462 if l_column = 'ATTRIBUTE11' then
463 l_col_old_val := l_old.ATTRIBUTE11;
464 l_col_new_val := p_new.ATTRIBUTE11;
465 end if;
466 --
467 if l_column = 'ATTRIBUTE12' then
468 l_col_old_val := l_old.ATTRIBUTE12;
469 l_col_new_val := p_new.ATTRIBUTE12;
470 end if;
471 --
472 if l_column = 'ATTRIBUTE13' then
473 l_col_old_val := l_old.ATTRIBUTE13;
474 l_col_new_val := p_new.ATTRIBUTE13;
475 end if;
476 --
477 if l_column = 'ATTRIBUTE14' then
478 l_col_old_val := l_old.ATTRIBUTE14;
479 l_col_new_val := p_new.ATTRIBUTE14;
480 end if;
481 --
482 if l_column = 'ATTRIBUTE15' then
483 l_col_old_val := l_old.ATTRIBUTE15;
484 l_col_new_val := p_new.ATTRIBUTE15;
485 end if;
486 --
487 if l_column = 'ATTRIBUTE16' then
488 l_col_old_val := l_old.ATTRIBUTE16;
489 l_col_new_val := p_new.ATTRIBUTE16;
490 end if;
491 --
492 if l_column = 'ATTRIBUTE17' then
493 l_col_old_val := l_old.ATTRIBUTE17;
494 l_col_new_val := p_new.ATTRIBUTE17;
495 end if;
496 --
497 if l_column = 'ATTRIBUTE18' then
498 l_col_old_val := l_old.ATTRIBUTE18;
499 l_col_new_val := p_new.ATTRIBUTE18;
500 end if;
501 --
502 if l_column = 'ATTRIBUTE19' then
503 l_col_old_val := l_old.ATTRIBUTE19;
504 l_col_new_val := p_new.ATTRIBUTE19;
505 end if;
506 --
507 if l_column = 'ATTRIBUTE20' then
508 l_col_old_val := l_old.ATTRIBUTE20;
509 l_col_new_val := p_new.ATTRIBUTE20;
510 end if;
511 --
512 benutils.exec_rule(
513 p_formula_id => l_per_info_chg_cs_ler_rl,
514 p_effective_date => l_session_date,
515 p_lf_evt_ocrd_dt => null,
516 p_business_group_id => nvl(p_new.business_group_id, l_old.business_group_id),
517 p_person_id => nvl(p_new.person_id, nvl(l_old.person_id, l_person_id)),
518 p_new_value => l_col_new_val,
519 p_old_value => l_col_old_val,
520 p_column_name => l_column,
521 p_pk_id => to_char(p_new.pay_proposal_id),
522 p_param5 => 'BEN_PRO_IN_CHANGE_DATE',
523 p_param5_value => to_char(p_new.CHANGE_DATE,'YYYY/MM/DD HH24:MI:SS'),
524 p_param6 => 'BEN_PRO_IO_CHANGE_DATE',
525 p_param6_value => to_char(l_old.CHANGE_DATE,'YYYY/MM/DD HH24:MI:SS'),
526 p_param7 => 'BEN_PRO_IN_LAST_CHANGE_DATE',
527 p_param7_value => to_char(p_new.LAST_CHANGE_DATE,'YYYY/MM/DD HH24:MI:SS'),
528 p_param8 => 'BEN_PRO_IO_LAST_CHANGE_DATE',
529 p_param8_value => to_char(l_old.LAST_CHANGE_DATE,'YYYY/MM/DD HH24:MI:SS'),
530 p_param9 => 'BEN_PRO_IN_NEXT_PERF_REVIEW_DATE',
531 p_param9_value => to_char(p_new.NEXT_PERF_REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
532 p_param10 => 'BEN_PRO_IO_NEXT_PERF_REVIEW_DATE',
533 p_param10_value => to_char(l_old.NEXT_PERF_REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
534 p_param11 => 'BEN_PRO_IN_NEXT_SAL_REVIEW_DATE',
535 p_param11_value => to_char(p_new.NEXT_SAL_REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
536 p_param12 => 'BEN_PRO_IO_NEXT_SAL_REVIEW_DATE',
537 p_param12_value => to_char(l_old.NEXT_SAL_REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
538 p_param13 => 'BEN_PRO_IN_PERFORMANCE_RATING',
539 p_param13_value => p_new.PERFORMANCE_RATING,
540 p_param14 => 'BEN_PRO_IO_PERFORMANCE_RATING',
541 p_param14_value => l_old.PERFORMANCE_RATING,
542 p_param15 => 'BEN_PRO_IN_PROPOSAL_REASON',
543 p_param15_value => p_new.PROPOSAL_REASON,
544 p_param16 => 'BEN_PRO_IO_PROPOSAL_REASON',
545 p_param16_value => l_old.PROPOSAL_REASON,
546 p_param17 => 'BEN_PRO_IN_PROPOSED_SALARY_N',
547 p_param17_value => to_char(p_new.PROPOSED_SALARY_N),
548 p_param18 => 'BEN_PRO_IO_PROPOSED_SALARY_N',
549 p_param18_value => to_char(l_old.PROPOSED_SALARY_N),
550 p_param20 => 'BEN_PRO_IN_REVIEW_DATE',
551 p_param20_value => to_char(p_new.REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
552 p_param21 => 'BEN_PRO_IO_REVIEW_DATE',
553 p_param21_value => to_char(l_old.REVIEW_DATE,'YYYY/MM/DD HH24:MI:SS'),
554 p_param22 => 'BEN_PRO_IN_APPROVED',
555 p_param22_value => p_new.APPROVED,
556 p_param23 => 'BEN_PRO_IO_APPROVED',
557 p_param23_value => l_old.APPROVED,
558 p_param24 => 'BEN_PRO_IN_MULTIPLE_COMPONENTS',
559 p_param24_value => p_new.MULTIPLE_COMPONENTS,
560 p_param25 => 'BEN_PRO_IO_MULTIPLE_COMPONENTS',
561 p_param25_value => l_old.MULTIPLE_COMPONENTS,
562 p_param26 => 'BEN_PRO_IN_FORCED_RANKING',
563 p_param26_value => to_char(p_new.FORCED_RANKING),
564 p_param27 => 'BEN_PRO_IO_FORCED_RANKING',
565 p_param27_value => to_char(l_old.FORCED_RANKING),
566 p_param28 => 'BEN_PRO_IN_PERFORMANCE_REVIEW_ID',
567 p_param28_value => to_char(p_new.PERFORMANCE_REVIEW_ID),
568 p_param29 => 'BEN_PRO_IO_PERFORMANCE_REVIEW_ID',
569 p_param29_value => to_char(l_old.PERFORMANCE_REVIEW_ID),
570 p_param30 => 'BEN_PRO_IN_EVENT_ID',
571 p_param30_value => to_char(p_new.EVENT_ID),
572 p_param31 => 'BEN_PRO_IO_EVENT_ID',
573 p_param31_value => to_char(l_old.EVENT_ID),
574 p_param32 => 'BEN_PRO_IN_PROPOSED_SALARY',
575 p_param32_value => p_new.PROPOSED_SALARY,
576 p_param33 => 'BEN_PRO_IO_PROPOSED_SALARY',
577 p_param33_value => l_old.PROPOSED_SALARY,
578 p_param34 => 'BEN_PRO_IN_ATTRIBUTE1',
579 p_param34_value => p_new.ATTRIBUTE1,
580 p_param35 => 'BEN_PRO_IO_ATTRIBUTE1',
581 p_param35_value => l_old.ATTRIBUTE1,
582 p_ret_val => l_rule_output);
583 --
584 end if;
585 --
586
587 --
588 if l_column = 'CHANGE_DATE' then
589 l_changed := (benutils.column_changed(l_old.CHANGE_DATE
590 ,p_new.CHANGE_DATE,l_new_val) AND
591 benutils.column_changed(p_new.CHANGE_DATE
592 ,l_old.CHANGE_DATE,l_old_val) AND
593 (l_changed));
594 hr_utility.set_location(' l_changed:',40);
595 end if;
596 --
597 if l_column = 'LAST_CHANGE_DATE' then
598 l_changed := (benutils.column_changed(l_old.LAST_CHANGE_DATE
599 ,p_new.LAST_CHANGE_DATE,l_new_val) AND
600 benutils.column_changed(p_new.LAST_CHANGE_DATE
601 ,l_old.LAST_CHANGE_DATE,l_old_val) AND
602 (l_changed));
603 --
604 end if;
605 --
606 if l_column = 'NEXT_PERF_REVIEW_DATE' then
607 l_changed := (benutils.column_changed(l_old.NEXT_PERF_REVIEW_DATE
608 ,p_new.NEXT_PERF_REVIEW_DATE,l_new_val) AND
609 benutils.column_changed(p_new.NEXT_PERF_REVIEW_DATE
610 ,l_old.NEXT_PERF_REVIEW_DATE,l_old_val) AND
611 (l_changed));
612 end if;
613 --
614 if l_column = 'NEXT_SAL_REVIEW_DATE' then
615 l_changed := (benutils.column_changed(l_old.NEXT_SAL_REVIEW_DATE
616 ,p_new.NEXT_SAL_REVIEW_DATE,l_new_val) AND
617 benutils.column_changed(p_new.NEXT_SAL_REVIEW_DATE
618 ,l_old.NEXT_SAL_REVIEW_DATE,l_old_val) );
619 end if;
620 --
621 if l_column = 'PERFORMANCE_RATING' then
622 l_changed := (benutils.column_changed(l_old.PERFORMANCE_RATING
623 ,p_new.PERFORMANCE_RATING,l_new_val) AND
624 benutils.column_changed(p_new.PERFORMANCE_RATING
625 ,l_old.PERFORMANCE_RATING,l_old_val) );
626 end if;
627 --
628 if l_column = 'PROPOSED_SALARY_N' then
629 l_changed := (benutils.column_changed(l_old.PROPOSED_SALARY_N
630 ,p_new.PROPOSED_SALARY_N,l_new_val) AND
631 benutils.column_changed(p_new.PROPOSED_SALARY_N
632 ,l_old.PROPOSED_SALARY_N,l_old_val) );
633 end if;
634 --
635 if l_column = 'PROPOSAL_REASON' then
636 l_changed := (benutils.column_changed(l_old.PROPOSAL_REASON
637 ,p_new.PROPOSAL_REASON,l_new_val) AND
638 benutils.column_changed(p_new.PROPOSAL_REASON
639 ,l_old.PROPOSAL_REASON,l_old_val) );
640 end if;
641 --
642 if l_column = 'REVIEW_DATE' then
643 l_changed := (benutils.column_changed(l_old.REVIEW_DATE
644 ,p_new.REVIEW_DATE,l_new_val) AND
645 benutils.column_changed(p_new.REVIEW_DATE
646 ,l_old.REVIEW_DATE,l_old_val) );
647 end if;
648 --
649 if l_column = 'APPROVED' then
650 l_changed := (benutils.column_changed(l_old.APPROVED
651 ,p_new.APPROVED,l_new_val) AND
652 benutils.column_changed(p_new.APPROVED
653 ,l_old.APPROVED,l_old_val) );
654 end if;
655 --
656 if l_column = 'MULTIPLE_COMPONENTS' then
657 l_changed := (benutils.column_changed(l_old.MULTIPLE_COMPONENTS
658 ,p_new.MULTIPLE_COMPONENTS,l_new_val) AND
659 benutils.column_changed(p_new.MULTIPLE_COMPONENTS
660 ,l_old.MULTIPLE_COMPONENTS,l_old_val) );
661 end if;
662 --
663 if l_column = 'FORCED_RANKING' then
664 l_changed := (benutils.column_changed(l_old.FORCED_RANKING
665 ,p_new.FORCED_RANKING,l_new_val) AND
666 benutils.column_changed(p_new.FORCED_RANKING
667 ,l_old.FORCED_RANKING,l_old_val) );
668 end if;
669 --
670 if l_column = 'PERFORMANCE_REVIEW_ID' then
671 l_changed := (benutils.column_changed(l_old.PERFORMANCE_REVIEW_ID
672 ,p_new.PERFORMANCE_REVIEW_ID,l_new_val) AND
673 benutils.column_changed(p_new.PERFORMANCE_REVIEW_ID
674 ,l_old.PERFORMANCE_REVIEW_ID,l_old_val) );
675 end if;
676 --
677 if l_column = 'EVENT_ID' then
678 l_changed := (benutils.column_changed(l_old.EVENT_ID
679 ,p_new.EVENT_ID,l_new_val) AND
680 benutils.column_changed(p_new.EVENT_ID
681 ,l_old.EVENT_ID,l_old_val) );
682 end if;
683 --
684 if l_column = 'PROPOSED_SALARY' then
685 l_changed := (benutils.column_changed(l_old.PROPOSED_SALARY
686 ,p_new.PROPOSED_SALARY,l_new_val) AND
687 benutils.column_changed(p_new.PROPOSED_SALARY
688 ,l_old.PROPOSED_SALARY,l_old_val) );
689 end if;
690 --
691 if l_column = 'ATTRIBUTE1' then
692 l_changed := (benutils.column_changed(l_old.ATTRIBUTE1
693 ,p_new.ATTRIBUTE1,l_new_val) AND
694 benutils.column_changed(p_new.ATTRIBUTE1
695 ,l_old.ATTRIBUTE1,l_old_val) );
696 end if;
697 --
698 if l_column = 'ATTRIBUTE2' then
699 l_changed := (benutils.column_changed(l_old.ATTRIBUTE2
700 ,p_new.ATTRIBUTE2,l_new_val) AND
701 benutils.column_changed(p_new.ATTRIBUTE2
702 ,l_old.ATTRIBUTE2,l_old_val) );
703 end if;
704 --
705 if l_column = 'ATTRIBUTE3' then
706 l_changed := (benutils.column_changed(l_old.ATTRIBUTE3
707 ,p_new.ATTRIBUTE3,l_new_val) AND
708 benutils.column_changed(p_new.ATTRIBUTE3
709 ,l_old.ATTRIBUTE3,l_old_val) );
710 end if;
711 --
712 if l_column = 'ATTRIBUTE4' then
713 l_changed := (benutils.column_changed(l_old.ATTRIBUTE4
714 ,p_new.ATTRIBUTE4,l_new_val) AND
715 benutils.column_changed(p_new.ATTRIBUTE4
716 ,l_old.ATTRIBUTE4,l_old_val) );
717 end if;
718 --
719 if l_column = 'ATTRIBUTE5' then
720 l_changed := (benutils.column_changed(l_old.ATTRIBUTE5
721 ,p_new.ATTRIBUTE5,l_new_val) AND
722 benutils.column_changed(p_new.ATTRIBUTE5
723 ,l_old.ATTRIBUTE5,l_old_val) );
724 end if;
725 --
726 if l_column = 'ATTRIBUTE6' then
727 l_changed := (benutils.column_changed(l_old.ATTRIBUTE6
728 ,p_new.ATTRIBUTE6,l_new_val) AND
729 benutils.column_changed(p_new.ATTRIBUTE6
730 ,l_old.ATTRIBUTE6,l_old_val) );
731 end if;
732 --
733 if l_column = 'ATTRIBUTE7' then
734 l_changed := (benutils.column_changed(l_old.ATTRIBUTE7
735 ,p_new.ATTRIBUTE7,l_new_val) AND
736 benutils.column_changed(p_new.ATTRIBUTE7
737 ,l_old.ATTRIBUTE7,l_old_val) );
738 end if;
739 --
740 if l_column = 'ATTRIBUTE8' then
741 l_changed := (benutils.column_changed(l_old.ATTRIBUTE8
742 ,p_new.ATTRIBUTE8,l_new_val) AND
743 benutils.column_changed(p_new.ATTRIBUTE8
744 ,l_old.ATTRIBUTE8,l_old_val) );
745 end if;
746 --
747 if l_column = 'ATTRIBUTE9' then
748 l_changed := (benutils.column_changed(l_old.ATTRIBUTE9
749 ,p_new.ATTRIBUTE9,l_new_val) AND
750 benutils.column_changed(p_new.ATTRIBUTE9
751 ,l_old.ATTRIBUTE9,l_old_val) );
752 end if;
753 --
754 if l_column = 'ATTRIBUTE10' then
755 l_changed := (benutils.column_changed(l_old.ATTRIBUTE10
756 ,p_new.ATTRIBUTE10,l_new_val) AND
757 benutils.column_changed(p_new.ATTRIBUTE10
758 ,l_old.ATTRIBUTE10,l_old_val) );
759 end if;
760 --
761 if l_column = 'ATTRIBUTE11' then
762 l_changed := (benutils.column_changed(l_old.ATTRIBUTE11
763 ,p_new.ATTRIBUTE11,l_new_val) AND
764 benutils.column_changed(p_new.ATTRIBUTE11
765 ,l_old.ATTRIBUTE11,l_old_val) );
766 end if;
767 --
768 if l_column = 'ATTRIBUTE12' then
769 l_changed := (benutils.column_changed(l_old.ATTRIBUTE12
770 ,p_new.ATTRIBUTE12,l_new_val) AND
771 benutils.column_changed(p_new.ATTRIBUTE12
772 ,l_old.ATTRIBUTE12,l_old_val) );
773 end if;
774 --
775 if l_column = 'ATTRIBUTE13' then
776 l_changed := (benutils.column_changed(l_old.ATTRIBUTE13
777 ,p_new.ATTRIBUTE13,l_new_val) AND
778 benutils.column_changed(p_new.ATTRIBUTE13
779 ,l_old.ATTRIBUTE13,l_old_val) );
780 end if;
781 --
782 if l_column = 'ATTRIBUTE14' then
783 l_changed := (benutils.column_changed(l_old.ATTRIBUTE14
784 ,p_new.ATTRIBUTE14,l_new_val) AND
785 benutils.column_changed(p_new.ATTRIBUTE14
786 ,l_old.ATTRIBUTE14,l_old_val) );
787 end if;
788 --
789 if l_column = 'ATTRIBUTE15' then
790 l_changed := (benutils.column_changed(l_old.ATTRIBUTE15
791 ,p_new.ATTRIBUTE15,l_new_val) AND
792 benutils.column_changed(p_new.ATTRIBUTE15
793 ,l_old.ATTRIBUTE15,l_old_val) );
794 end if;
795 --
796 if l_column = 'ATTRIBUTE16' then
797 l_changed := (benutils.column_changed(l_old.ATTRIBUTE16
798 ,p_new.ATTRIBUTE16,l_new_val) AND
799 benutils.column_changed(p_new.ATTRIBUTE16
800 ,l_old.ATTRIBUTE16,l_old_val) );
801 end if;
802 --
803 if l_column = 'ATTRIBUTE17' then
804 l_changed := (benutils.column_changed(l_old.ATTRIBUTE17
805 ,p_new.ATTRIBUTE17,l_new_val) AND
806 benutils.column_changed(p_new.ATTRIBUTE17
807 ,l_old.ATTRIBUTE17,l_old_val) );
808 end if;
809 --
810 if l_column = 'ATTRIBUTE18' then
811 l_changed := (benutils.column_changed(l_old.ATTRIBUTE18
812 ,p_new.ATTRIBUTE18,l_new_val) AND
813 benutils.column_changed(p_new.ATTRIBUTE18
814 ,l_old.ATTRIBUTE18,l_old_val) );
815 end if;
816 --
817 if l_column = 'ATTRIBUTE20' then
818 l_changed := (benutils.column_changed(l_old.ATTRIBUTE20
819 ,p_new.ATTRIBUTE20,l_new_val) AND
820 benutils.column_changed(p_new.ATTRIBUTE20
821 ,l_old.ATTRIBUTE20,l_old_val) );
822 end if;
823 --
824 -- Checking the rule output and the rule override flag.
825 -- Whether the rule is mandatory or not, rule output should return 'Y'
826 -- Rule Mandatory flag is just to override the column data change.
827
828 if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
829 l_changed := TRUE ;
830 elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
831 l_changed := l_changed AND TRUE;
832 elsif l_rule_output = 'N' then
833 hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
834 l_changed := FALSE;
835 end if;
836
837 hr_utility.set_location('After the rule Check ',20.05);
838 if l_changed then
839 hr_utility.set_location(' l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
840 else
841 hr_utility.set_location(' l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
842 end if;
843 -- Check for Column Mandatory Change
844 -- If column change is mandatory and data change has failed then dont trigger
845 -- If column change is non-mandatory and the data change has passed, then trigger.
846
847 if l_chg_mandatory_cd = 'Y' and not l_changed then
848 hr_utility.set_location('Found Mandatory and its failed ', 20.1);
849 l_changed := FALSE;
850 l_trigger := FALSE;
851 exit;
852 elsif l_chg_mandatory_cd = 'Y' and l_changed then
853 hr_utility.set_location('Found Mandatory and its passed ', 20.1);
854 l_changed := TRUE;
855 -- exit; */
856 elsif l_chg_mandatory_cd = 'N' and l_changed then
857 hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
858 l_changed := TRUE;
859 l_trigger := TRUE;
860 exit;
861 end if;
862
863
864 hr_utility.set_location('After the Mandatory code check ',20.05);
865 if l_changed then
866 hr_utility.set_location(' l_change TRUE ', 20.1);
867 else
868 hr_utility.set_location(' l_change FALSE ', 20.1);
869 end if;
870 --
871 /* if not l_changed then
872 exit;
873 end if; */
874
875 end loop;
876 hr_utility.set_location(' ben_pro_trigger', 50);
877 l_ptnl_id := 0;
878 l_ovn :=null;
879 --
880 if l_changed then
881 hr_utility.set_location(' l_changed = TRUE' || l_type, 9999);
882 else
883 hr_utility.set_location(' l_changed = FALSE' || l_type, 9999);
884 end if;
885 if l_trigger then
886 if l_type = 'P' then
887 open le_exists(nvl(p_new.person_id, l_person_id),l_ler_id,l_lf_evt_ocrd_date);
888 fetch le_exists into l_le_exists;
889 if le_exists%notfound then
890 hr_utility.set_location(' Entering: ben_pro_trigger5', 60);
891
892 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
893 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
894 (p_validate => false
895 ,p_ptnl_ler_for_per_id => l_ptnl_id
896 ,p_ntfn_dt => l_system_date
897 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
898 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
899 ,p_ler_id => l_ler_id
900 ,p_ler_typ_cd => l_typ_cd
901 ,p_person_id => nvl(p_new.person_id, l_person_id)
902 ,p_business_group_Id =>p_new.business_group_id
903 ,p_object_version_number => l_ovn
904 ,p_effective_date => nvl(l_effective_start_date, p_new.change_date)
905 ,p_dtctd_dt => nvl(l_effective_start_date, p_new.change_date));
906 end if;
907 close le_exists;
908 elsif l_type = 'R' then
909 hr_utility.set_location(' Entering: ben_pro_trigger5-', 65);
910 open get_contacts(nvl(p_new.person_id, l_person_id));
911 loop
912 fetch get_contacts into l_hld_person_id;
913 exit when get_contacts%notfound;
914 open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
915 fetch le_exists into l_le_exists;
916 if le_exists%notfound then
917 hr_utility.set_location(' Entering: ben_pro_trigger5', 60);
918
919 ben_create_ptnl_ler_for_per.create_ptnl_ler_event
920 --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
921 (p_validate => false
922 ,p_ptnl_ler_for_per_id => l_ptnl_id
923 ,p_ntfn_dt => l_system_date
924 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
925 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
926 ,p_ler_id => l_ler_id
927 ,p_ler_typ_cd => l_typ_cd
928 ,p_person_id => l_hld_person_id
929 ,p_business_group_Id =>p_new.business_group_id
930 ,p_object_version_number => l_ovn
931 ,p_effective_date => nvl(l_effective_start_date, p_new.change_date)
932 ,p_dtctd_dt => nvl(l_effective_start_date, p_new.change_date));
933 end if;
934 l_ptnl_id := 0;
935 l_ovn :=null;
936 close le_exists;
937 end loop;
938 close get_contacts;
939 end if;
940 --
941 -- reset the variables.
942 --
943 hr_utility.set_location(' ben_pro_trigger', 40);
944 l_changed := FALSE;
945 l_trigger := TRUE;
946 l_ovn := NULL;
947 l_effective_start_date := nvl(l_session_date, p_new.change_date);
948 -- l_lf_evt_ocrd_date := l_session_date;
949 end if;
950 close get_ler_col;
951 end loop;
952 hr_utility.set_location(' ben_pro_trigger', 50);
953 close get_ler;
954 hr_utility.set_location(' leaving ben_pro_trigger', 60);
955 --end if;
956
957 -- If a Pay Proposal has been approved then check for Quartile in Grade life event
958 if hr_general.g_data_migrator_mode not in ( 'Y','P') then
959 if nvl(l_old.approved,'N') = 'N' and p_new.approved = 'Y' then
960 ben_pro_ler.qua_in_gr_ler_chk(null,null,l_old,p_new,p_effective_date,'P');
961 end if;
962 end if;
963 end;
964
965 procedure qua_in_gr_ler_chk (p_old_asg IN ben_asg_ler.g_asg_ler_rec
966 ,p_new_asg IN ben_asg_ler.g_asg_ler_rec
967 ,p_old_pro IN g_pro_ler_rec
968 ,p_new_pro IN g_pro_ler_rec
969 ,p_effective_date IN date default null
970 ,p_called_from IN varchar2) is
971
972 l_session_date date;
973 l_system_date date;
974 l_changed BOOLEAN;
975 l_ler_id NUMBER;
976 l_ocrd_dt_cd VARCHAR2(30);
977 l_ovn NUMBER;
978 l_ptnl_id NUMBER;
979 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
980 l_lf_evt_ocrd_date DATE ;
981 l_le_exists VARCHAR2(1);
982 l_dtctd_dt date;
983 l_procd_dt date;
984 l_unprocd_dt date;
985 l_type VARCHAR2(1);
986 --
987 l_bool BOOLEAN;
988 l_status VARCHAR2(1);
989 l_industry VARCHAR2(1);
990 l_date_from date;
991 l_date_to date;
992 l_old_qua_in_gr varchar2(30);
993 l_new_qua_in_gr varchar2(30);
994 l_person_id number;
995 l_new_start_date date;
996 --
997 l_old_max_val number;
998 l_old_min_val number;
999 l_new_max_val number;
1000 l_new_min_val number;
1001 l_assignment_id per_all_assignments_f.assignment_id%type;
1002 l_business_group_id per_all_assignments_f.business_group_id%type;
1003 l_old_grade_id number;
1004 l_new_grade_id number;
1005 l_old_pay_basis_id per_all_assignments_f.pay_basis_id%type;
1006 l_new_pay_basis_id per_all_assignments_f.pay_basis_id%type;
1007 l_quar_in_grade_cd varchar2(30);
1008 l_old_person_sal number;
1009 l_new_person_sal number;
1010 l_change_date date;
1011 --
1012 cursor get_ler_qig (p_business_group_id number) is
1013 select ler.ler_id
1014 ,ler.ocrd_dt_det_cd
1015 from ben_ler_f ler
1016 where ler.business_group_id = p_business_group_id
1017 and typ_cd = 'QUAINGR'
1018 and l_session_date between ler.effective_start_date and
1019 ler.effective_end_date
1020 order by ler.ler_id;
1021 --
1022 cursor c_min_max(p_grade_id number
1023 ,p_business_group_id number
1024 ,p_lf_evt_ocrd_dt date
1025 ,p_pay_basis_id number) is
1026 select (maximum * grade_annualization_factor) maximum ,
1027 (minimum * grade_annualization_factor) minimum
1028 from pay_grade_rules_f pgr,
1029 per_pay_bases ppb
1030 where ppb.pay_basis_id = p_pay_basis_id
1031 and ppb.business_group_id = p_business_group_id
1032 and pgr.rate_id = ppb.rate_id
1033 and pgr.business_group_id = p_business_group_id
1034 and pgr.grade_or_spinal_point_id = p_grade_id
1035 and p_lf_evt_ocrd_dt between nvl(pgr.effective_start_date, p_lf_evt_ocrd_dt)
1036 and nvl(pgr.effective_end_date, p_lf_evt_ocrd_dt);
1037 --
1038 cursor c_annual_sal(p_assignment_id number
1039 ,p_business_group_id number
1040 ,p_lf_evt_ocrd_dt date
1041 ,p_pay_basis_id number
1042 ,p_old_new varchar2) is
1043 select ppp.proposed_salary_n * ppb.pay_annualization_factor annual_salary,ppp.change_date
1044 from per_pay_bases ppb,
1045 per_pay_proposals ppp
1046 where ppb.pay_basis_id = p_pay_basis_id
1047 and ppb.business_group_id = p_business_group_id
1048 and ppp.assignment_id = p_assignment_id
1049 and ppp.approved = 'Y'
1050 and ppp.change_date <= p_lf_evt_ocrd_dt
1051 and (p_called_from = 'A' or (p_called_from = 'P' and p_old_new = 'new') )
1052 union
1053 select ppp.proposed_salary_n * ppb.pay_annualization_factor annual_salary,ppp.change_date
1054 from per_pay_bases ppb,
1055 per_pay_proposals ppp
1056 where ppb.pay_basis_id = p_pay_basis_id
1057 and ppb.business_group_id = p_business_group_id
1058 and ppp.assignment_id = p_assignment_id
1059 and ppp.approved = 'Y'
1060 and ppp.pay_proposal_id <> p_new_pro.pay_proposal_id
1061 and ppp.change_date <= p_lf_evt_ocrd_dt
1062 and p_called_from = 'P' and p_old_new = 'old'
1063 order by 2 desc ;
1064 --
1065 cursor c_asg(p_person_id number,p_business_group_id number,p_assignment_id number) is
1066 select grade_id,pay_basis_id
1067 from per_all_assignments_f
1068 where assignment_id = p_assignment_id
1069 and person_id = p_person_id
1070 and business_group_id = p_business_group_id
1071 and l_session_date between effective_start_date and effective_end_date;
1072 --
1073
1074 procedure get_quartile(p_min IN number default 0
1075 ,p_max IN number default 0
1076 ,p_salary IN number default 0
1077 ,p_code OUT NOCOPY varchar2
1078 )
1079 is
1080 l_min number;
1081 l_max number;
1082 l_count number;
1083 l_divisor number := 4;
1084 l_addition_factor number;
1085 l_multiplication_factor number;
1086 BEGIN
1087 hr_utility.set_location('Entering get_quartile',10);
1088 if p_salary > nvl(p_max,0) then
1089 --
1090 p_code := 'ABV' ;
1091 --
1092 elsif p_salary < nvl(p_min,0) then
1093 --
1094 p_code := 'BLW' ;
1095 --
1096 else
1097 --
1098 l_min := p_min;
1099 l_addition_factor := (p_max - p_min)/l_divisor;
1100 --
1101 for l_count in 1..4 loop
1102 l_max := l_min + (l_addition_factor );
1103 if l_count <> 1 then
1104 l_min := l_min + 1;
1105 end if;
1106 if p_salary between l_min and l_max then
1107 p_code := l_divisor - l_count + 1;
1108 --commit;
1109 --exit;
1110 end if;
1111 l_min := l_max;
1112 p_code := 'NA';
1113 end loop;
1114 --
1115 end if;
1116 hr_utility.set_location('Leaving get_quartile',15);
1117 END;
1118 --
1119
1120 begin
1121 hr_utility.set_location('Entering: qua_in_gr_ler_chk ', 510);
1122 hr_utility.set_location('Profile val '||fnd_profile.value('BEN_QUA_IN_GR_LER'), 510);
1123
1124 -- Check if the profile option for Life Event triggering is enabled
1125 --
1126 -- Changed to treat null as 'N' and not as 'Y'
1127 if nvl(fnd_profile.value('BEN_QUA_IN_GR_LER'),'N') = 'Y' then
1128
1129 l_bool :=fnd_installation.get(appl_id => 805
1130 ,dep_appl_id =>805
1131 ,status => l_status
1132 ,industry => l_industry);
1133 if l_status = 'I' then
1134 --
1135 l_changed := FALSE;
1136 --
1137 open get_session_date;
1138 fetch get_session_date into l_session_date;
1139 close get_session_date;
1140 --
1141 open get_system_date;
1142 fetch get_system_date into l_system_date;
1143 close get_system_date;
1144 --
1145 -- For Assignment use Session date, for PayProposal use Change Date
1146 --
1147 if p_called_from = 'A' then
1148 l_business_group_id := p_new_asg.business_group_id;
1149 l_new_start_date := l_session_date;
1150 else
1151 l_business_group_id := p_new_pro.business_group_id;
1152 l_new_start_date := p_new_pro.change_date;
1153 end if;
1154 --
1155 hr_utility.set_location('l_session_date:'||to_char(l_session_date), 30);
1156 hr_utility.set_location('l_business_group_id '||l_business_group_id, 199);
1157 hr_utility.set_location('l_new_start_date: '||to_char(l_new_start_date),30);
1158
1159 open get_ler_qig(l_business_group_id);
1160 fetch get_ler_qig into l_ler_id, l_ocrd_dt_cd;
1161
1162 if get_ler_qig%found then
1163 --
1164 hr_utility.set_location(' Found get_ler_qig ', 199);
1165
1166 if l_ocrd_dt_cd is null then
1167 --
1168 l_lf_evt_ocrd_date := l_new_start_date;
1169 --
1170 else
1171 --
1172 -- Call the common date procedure.
1173 --
1174 ben_determine_date.main
1175 (p_date_cd => l_ocrd_dt_cd
1176 ,p_effective_date => nvl(l_new_start_date,l_session_date)
1177 ,p_lf_evt_ocrd_dt => nvl(l_new_start_date,l_session_date)
1178 ,p_returned_date => l_lf_evt_ocrd_date
1179 );
1180 end if;
1181 hr_utility.set_location('LER ID is '||l_ler_id,30);
1182 hr_utility.set_location('Life Event Occured date is '||l_lf_evt_ocrd_date,30);
1183 --
1184 if p_called_from = 'A' then
1185 l_person_id := p_new_asg.person_id;
1186 l_assignment_id := p_new_asg.assignment_id;
1187 l_new_grade_id := p_new_asg.grade_id;
1188 l_new_pay_basis_id := p_new_asg.pay_basis_id;
1189 l_old_grade_id := p_old_asg.grade_id;
1190 l_old_pay_basis_id := p_old_asg.pay_basis_id;
1191
1192 --
1193 elsif p_called_from = 'P' then
1194
1195 if p_new_pro.person_id is null then
1196 open c_get_person(p_new_pro.assignment_id,p_effective_date);
1197 fetch c_get_person into l_person_id;
1198 close c_get_person;
1199 end if;
1200 l_person_id := nvl(p_new_pro.person_id,l_person_id);
1201 l_business_group_id := p_new_pro.business_group_id;
1202 l_assignment_id := p_new_pro.assignment_id;
1203
1204 open c_asg(l_person_id,l_business_group_id,l_assignment_id);
1205 fetch c_asg into l_old_grade_id,l_old_pay_basis_id;
1206 close c_asg;
1207
1208 l_new_grade_id := l_old_grade_id;
1209 l_new_pay_basis_id := l_old_pay_basis_id;
1210 --
1211 end if;
1212 --
1213 hr_utility.set_location('l_old_pay_basis_id is '||l_old_pay_basis_id, 199);
1214 hr_utility.set_location('l_new_pay_basis_id is '||l_new_pay_basis_id, 199);
1215 hr_utility.set_location('l_old_grade_id is '||l_old_grade_id, 199);
1216 hr_utility.set_location('l_new_grade_id is '||l_new_grade_id, 199);
1217 --
1218 -- For update, get the old proposed salary from the previous approved record.
1219 -- If the proposed salary is changed,get it from the pay proposal record being passed.
1220 --
1221
1222 open c_annual_sal(l_assignment_id
1223 ,l_business_group_id
1224 ,nvl(l_lf_evt_ocrd_date,l_new_start_date)
1225 ,l_old_pay_basis_id
1226 ,'old') ;
1227 fetch c_annual_sal into l_old_person_sal,l_change_date;
1228 close c_annual_sal;
1229
1230 open c_annual_sal(l_assignment_id
1231 ,l_business_group_id
1232 ,nvl(l_lf_evt_ocrd_date,l_new_start_date)
1233 ,l_new_pay_basis_id
1234 ,'new') ;
1235 fetch c_annual_sal into l_new_person_sal,l_change_date;
1236 close c_annual_sal;
1237 hr_utility.set_location('p_new_pro.pay_proposal_id is '||p_new_pro.pay_proposal_id, 199);
1238 hr_utility.set_location('l_old_person_sal is '||l_old_person_sal, 199);
1239 hr_utility.set_location('l_new_person_sal is '||l_new_person_sal, 199);
1240
1241 open c_min_max(l_old_grade_id
1242 ,l_business_group_id
1243 ,nvl(l_lf_evt_ocrd_date, p_effective_date)
1244 ,l_old_pay_basis_id);
1245 fetch c_min_max into l_old_max_val, l_old_min_val;
1246 close c_min_max;
1247 --
1248 open c_min_max(l_new_grade_id
1249 ,l_business_group_id
1250 ,nvl(l_lf_evt_ocrd_date, p_effective_date)
1251 ,l_new_pay_basis_id);
1252 fetch c_min_max into l_new_max_val, l_new_min_val;
1253 close c_min_max;
1254 --
1255 hr_utility.set_location('l_old_max_val is '||l_old_max_val, 199);
1256 hr_utility.set_location('l_old_min_val is '||l_old_min_val, 199);
1257 hr_utility.set_location('l_new_max_val is '||l_new_max_val, 199);
1258 hr_utility.set_location('l_new_min_val is '||l_new_min_val, 199);
1259 --
1260 -- Get the Quartile in grade in which the person's salary fall
1261 -- within the given min - max range
1262 --
1263 if l_old_person_sal is null or l_old_grade_id is null
1264 or (l_old_min_val is null and l_old_max_val is null) then
1265 l_old_qua_in_gr := 'NA';
1266 else
1267 /*
1268 -- commented for bug: 4558945
1269 get_quartile(p_min => nvl(l_old_min_val,0)
1270 ,p_max => nvl(l_old_max_val,0)
1271 ,p_salary => l_old_person_sal
1272 ,p_code => l_old_qua_in_gr);
1273 */
1274 -- added for bug: 4558945
1275 l_old_qua_in_gr :=
1276 ben_cwb_person_info_pkg.get_grd_quartile (p_salary => l_old_person_sal,
1277 p_min => nvl(l_old_min_val,0),
1278 p_max => nvl(l_old_max_val,0),
1279 p_mid => ( nvl(l_old_min_val,0)+ nvl(l_old_max_val,0))/ 2
1280 );
1281
1282 end if;
1283 if p_called_from = 'P' and l_old_grade_id is null
1284 or (l_new_min_val is null and l_new_max_val is null) then
1285 l_new_qua_in_gr := 'NA';
1286 else
1287 /*
1288 -- commented for bug: 4558945
1289 get_quartile(p_min => nvl(l_new_min_val,0)
1290 ,p_max => nvl(l_new_max_val,0)
1291 ,p_salary => l_new_person_sal
1292 ,p_code => l_new_qua_in_gr);
1293 */
1294 -- added for bug: 4558945
1295 l_new_qua_in_gr :=
1296 ben_cwb_person_info_pkg.get_grd_quartile (p_salary => l_new_person_sal,
1297 p_min => nvl(l_new_min_val,0),
1298 p_max => nvl(l_new_max_val,0),
1299 p_mid => ( nvl(l_new_min_val,0)+ nvl(l_new_max_val,0))/ 2
1300 );
1301 end if;
1302 --
1303 hr_utility.set_location('Old Quartile in Grade value is '||l_old_qua_in_gr,100);
1304 hr_utility.set_location('New Quartile in Grade value is '||l_new_qua_in_gr,110);
1305 --
1306 if l_old_qua_in_gr <> l_new_qua_in_gr then
1307 l_changed := TRUE;
1308 end if;
1309 --
1310 if l_changed then
1311 hr_utility.set_location('Change detected', 30);
1312 end if;
1313 --
1314 l_ptnl_id := 0;
1315 l_ovn :=null;
1316 --
1317 if l_changed then
1318 open le_exists(l_person_id,l_ler_id,l_lf_evt_ocrd_date);
1319 fetch le_exists into l_le_exists;
1320 if le_exists%notfound then
1321 hr_utility.set_location(' Calling create_ptnl_ler_for_per ', 60);
1322 ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
1323 (p_validate => false
1324 ,p_ptnl_ler_for_per_id => l_ptnl_id
1325 ,p_ntfn_dt => l_system_date
1326 ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
1327 ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
1328 ,p_ler_id => l_ler_id
1329 ,p_person_id => l_person_id
1330 ,p_business_group_Id =>l_business_group_id
1331 ,p_object_version_number => l_ovn
1332 ,p_effective_date => l_new_start_date
1333 ,p_dtctd_dt => l_new_start_date);
1334 end if;
1335 close le_exists;
1336 --
1337 -- reset the variables.
1338 --
1339 l_changed := FALSE;
1340 -- l_trigger := FALSE;
1341 l_ovn := NULL;
1342 end if;
1343 end if;
1344 close get_ler_qig;
1345 end if;
1346 end if;
1347 hr_utility.set_location('Leaving: qua_in_gr_ler_chk', 130);
1348 end qua_in_gr_ler_chk;
1349
1350 end ben_pro_ler;