[Home] [Help]
PACKAGE BODY: APPS.BEN_CWB_PERSON_INFO_PKG
Source
1 package body BEN_CWB_PERSON_INFO_PKG as
2 /* $Header: bencwbpi.pkb 120.25 2011/10/14 07:22:05 naramasa ship $ */
3 --
4 -- --------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- --------------------------------------------------------------------------
7 --
8 g_package varchar2(33):=' ben_cwb_person_info_pkg.'; --Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 g_fte_factor VARCHAR2(240) := fnd_profile.VALUE('BEN_CWB_FTE_FACTOR');
11 g_salary_survey_id number := null; --Change to right value when implementing.
12 --
13 cursor c_sal_info(v_assignment_id number,
14 v_effective_date date) is
15 select ppp.assignment_id assignment_id
16 ,ppp.proposed_salary_n salary
17 ,ppb.pay_basis salary_frequency
18 ,get_salary_currency(ppb.input_value_id
19 ,ppp.change_date) salary_currency
20 ,nvl(ppb.pay_annualization_factor,1) salary_ann_fctr
21 ,get_fte_factor(v_assignment_id
22 ,v_effective_date) fte_factor
23 from per_all_assignments_f paf
24 ,per_pay_proposals ppp
25 ,per_pay_bases ppb
26 where ppp.assignment_id = v_assignment_id
27 and ppp.approved = 'Y'
28 and ppp.change_date =
29 (select max(ppp1.change_date)
30 from per_pay_proposals ppp1
31 where ppp1.assignment_id = v_assignment_id
32 and ppp1.change_date <= v_effective_date
33 and ppp1.approved = 'Y')
34 and paf.assignment_id = ppp.assignment_id
35 and ppp.change_date between
36 paf.effective_start_date and paf.effective_end_date
37 and paf.pay_basis_id = ppb.pay_basis_id;
38
39 cursor c_survey_info(v_job_id number,
40 v_effective_date date) is
41 SELECT pss_mappings.parent_id parent_id
42 ,pss.salary_survey_id survey_id
43 ,pss_lines.currency_code currency
44 ----- vkodedal 8869036 - survey type
45 ,pss.survey_type_code frequency
46 ,decode(pss.survey_type_code,
47 'MONTHLY', 12,
48 'HOURLY', 2080, 1) annualization_factor
49 ,pss_lines.minimum_pay min_salary
50 ,NVL(pss_lines.twenty_fifth_percentile
51 ,pss_lines.minimum_pay+
52 (pss_lines.maximum_pay-pss_lines.minimum_pay)*0.25)
53 pct25_salary
54 ,NVL(pss_lines.mean_pay
55 ,(pss_lines.minimum_pay+pss_lines.maximum_pay)/2)
56 mid_salary
57 ,NVL(pss_lines.seventy_fifth_percentile
58 ,pss_lines.minimum_pay+
59 (pss_lines.maximum_pay-pss_lines.minimum_pay)*0.75)
60 pct75_salary
61 ,pss_lines.maximum_pay max_salary
62 FROM per_salary_surveys pss
63 ,per_salary_survey_lines pss_lines
64 ,per_salary_survey_mappings pss_mappings
65 WHERE pss.salary_survey_id = g_salary_survey_id
66 AND pss_lines.salary_survey_id = pss.salary_survey_id
67 AND v_effective_date BETWEEN pss_lines.start_date
68 AND NVL(pss_lines.end_date, to_date('4712/12/31', 'yyyy/mm/dd'))
69 AND pss_lines.salary_survey_line_id = pss_mappings.salary_survey_line_id
70 AND pss_mappings.parent_table_name = 'PER_JOBS'
71 and pss_mappings.parent_id = v_job_id;
72 -- --------------------------------------------------------------------------
73 -- |----------------------------< get_salary_info >--------------------------|
74 -- --------------------------------------------------------------------------
75 -- This function derives the salary information.
76 function get_salary_info(p_assignment_id in number
77 ,p_effective_date in date)
78
79 return c_sal_info%rowtype is
80 --
81 l_sal_info c_sal_info%rowtype;
82 --
83 l_proc varchar2(72) := g_package||'get_salary_info';
84 --
85
86 begin
87 --
88 if g_debug then
89 hr_utility.set_location('Entering:'|| l_proc, 10);
90 end if;
91 --
92 open c_sal_info(p_assignment_id, p_effective_date);
93 fetch c_sal_info into l_sal_info;
94 close c_sal_info;
95 --
96 if g_debug then
97 hr_utility.set_location(l_proc, 99);
98 end if;
99 --
100 return l_sal_info;
101 end get_salary_info;
102 --
103 -- --------------------------------------------------------------------------
104 -- |----------------------------< get_survey_info >--------------------------|
105 -- --------------------------------------------------------------------------
106 -- This function derives the survey information.
107 function get_survey_info(p_job_id in number
108 ,p_effective_date in date)
109 return c_survey_info%rowtype is
110 --
111 l_survey_info c_survey_info%rowtype;
112 --
113 l_proc varchar2(72) := g_package||'get_survey_info';
114 --
115 begin
116 --
117 if g_debug then
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 end if;
120 --
121 open c_survey_info(p_job_id, p_effective_date);
122 fetch c_survey_info into l_survey_info;
123 close c_survey_info;
124 --
125 if g_debug then
126 hr_utility.set_location(l_proc, 99);
127 end if;
128 --
129 return l_survey_info;
130 end get_survey_info;
131 --
132 -- --------------------------------------------------------------------------
133 -- |---------------------------< get_years_in_job >--------------------------|
134 -- --------------------------------------------------------------------------
135 --
136 function get_years_in_job(p_assignment_id in number
137 ,p_job_id in number
138 ,p_effective_date in date
139 ,p_asg_effective_start_date in date)
140 return number is
141 --
142 l_years_in_job number;
143 --
144 l_proc varchar2(72) := g_package||'get_years_in_job';
145 --
146 begin
147 --
148 if g_debug then
149 hr_utility.set_location('Entering:'|| l_proc, 10);
150 end if;
151 --
152 select trunc(sum(months_between(
153 decode(asgjob.effective_end_date,
154 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
155 least(asgjob.effective_end_date+1,p_effective_date)),
156 asgjob.effective_start_date))/12,1)
157 into l_years_in_job
158 from per_all_assignments_f asgjob
159 where asgjob.assignment_id=p_assignment_id
160 and asgjob.job_id = p_job_id
161 and asgjob.effective_start_date <= p_asg_effective_start_date
162 and asgjob.assignment_type in ('C','E'); -- bug 13061653
163 --
164 if g_debug then
165 hr_utility.set_location(l_proc, 99);
166 end if;
167 --
168 return l_years_in_job;
169 end;
170 --
171 -- --------------------------------------------------------------------------
172 -- |-------------------------< get_years_in_position >-----------------------|
173 -- --------------------------------------------------------------------------
174 --
175 function get_years_in_position(p_assignment_id in number
176 ,p_position_id in number
177 ,p_effective_date in date
178 ,p_asg_effective_start_date in date)
179 return number is
180 --
181 l_years_in_position number;
182 --
183 l_proc varchar2(72) := g_package||'get_years_in_position';
184 --
185 begin
186 --
187 if g_debug then
188 hr_utility.set_location('Entering:'|| l_proc, 10);
189 end if;
190 --
191 select trunc(sum(months_between(
192 decode(asgpos.effective_end_date,
193 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
194 least(asgpos.effective_end_date+1,p_effective_date)),
195 asgpos.effective_start_date))/12,1)
196 into l_years_in_position
197 from per_all_assignments_f asgpos
198 where asgpos.assignment_id=p_assignment_id
199 and asgpos.position_id = p_position_id
200 and asgpos.effective_start_date <= p_asg_effective_start_date;
201 --
202 if g_debug then
203 hr_utility.set_location(l_proc, 99);
204 end if;
205 --
206 return l_years_in_position;
207 end;
208 --
209 -- --------------------------------------------------------------------------
210 -- |--------------------------< get_years_in_grade >-------------------------|
211 -- --------------------------------------------------------------------------
212 --
213 function get_years_in_grade(p_assignment_id in number
214 ,p_grade_id in number
215 ,p_effective_date in date
216 ,p_asg_effective_start_date in date)
217 return number is
218 --
219 l_years_in_grade number;
220 --
221 l_proc varchar2(72) := g_package||'get_years_in_grade';
222 --
223 begin
224 --
225 if g_debug then
226 hr_utility.set_location('Entering:'|| l_proc, 10);
227 end if;
228 --
229 select trunc(sum(months_between(
230 decode(asggrd.effective_end_date,
231 to_date('4712/12/31', 'yyyy/mm/dd'),p_effective_date,
232 least(asggrd.effective_end_date+1,p_effective_date)),
233 asggrd.effective_start_date))/12,1)
234 into l_years_in_grade
235 from per_all_assignments_f asggrd
236 where asggrd.assignment_id=p_assignment_id
237 and asggrd.grade_id = p_grade_id
238 and asggrd.effective_start_date <= p_asg_effective_start_date
239 and asggrd.assignment_type <> 'A'; --9060804
240 --
241 if g_debug then
242 hr_utility.set_location(l_proc, 99);
243 end if;
244 --
245 return l_years_in_grade;
246 end; -- get_years_in_grade
247 -- --------------------------------------------------------------------------
248 -- |----------------------------< get_grd_min_val >--------------------------|
249 -- --------------------------------------------------------------------------
250 -- This function computes the years in grade
251 function get_grd_min_val(p_grade_id in number
252 ,p_rate_id in number
253 ,p_effective_date in date)
254 return number is
255 --
256 l_grd_min_val number;
257 --
258 l_proc varchar2(72) := g_package||'get_grd_min_val';
259 --
260 begin
261 --
262 if g_debug then
263 hr_utility.set_location('Entering:'|| l_proc, 10);
264 end if;
265 --
266 select fnd_number.canonical_to_number(minimum) into l_grd_min_val
267 from pay_grade_rules_f grdrule
268 where grdrule.rate_id = p_rate_id
269 and grdrule.grade_or_spinal_point_id = p_grade_id
270 and p_effective_date between grdrule.effective_start_date
271 and grdrule.effective_end_date;
272 --
273 if g_debug then
274 hr_utility.set_location(l_proc, 99);
275 end if;
276 --
277 return l_grd_min_val;
278 end; -- get_grd_min_val
279 --
280 -- --------------------------------------------------------------------------
281 -- |----------------------------< get_grd_max_val >--------------------------|
282 -- --------------------------------------------------------------------------
283 -- This function computes the years in grade
284 function get_grd_max_val(p_grade_id in number
285 ,p_rate_id in number
286 ,p_effective_date in date)
287 return number is
288 --
289 l_grd_max_val number;
290 --
291 l_proc varchar2(72) := g_package||'get_grd_max_val';
292 --
293 begin
294 --
295 if g_debug then
296 hr_utility.set_location('Entering:'|| l_proc, 10);
297 end if;
298 --
299 select fnd_number.canonical_to_number(maximum) into l_grd_max_val
300 from pay_grade_rules_f grdrule
301 where grdrule.rate_id = p_rate_id
302 and grdrule.grade_or_spinal_point_id = p_grade_id
303 and p_effective_date between grdrule.effective_start_date
304 and grdrule.effective_end_date;
305 --
306 if g_debug then
307 hr_utility.set_location(l_proc, 99);
308 end if;
309 --
310 return l_grd_max_val;
311 end; -- get_grd_max_val
312 --
313 -- --------------------------------------------------------------------------
314 -- |---------------------------< get_grd_mid_point >-------------------------|
315 -- --------------------------------------------------------------------------
316 -- This function computes the years in grade
317 function get_grd_mid_point(p_grade_id in number
318 ,p_rate_id in number
319 ,p_effective_date in date)
320 return number is
321 --
322 l_grd_mid_point number;
323 --
324 l_proc varchar2(72) := g_package||'get_grd_mid_point';
325 --
326 begin
327 --
328 if g_debug then
329 hr_utility.set_location('Entering:'|| l_proc, 10);
330 end if;
331 --
332 select fnd_number.canonical_to_number(mid_value) into l_grd_mid_point
333 from pay_grade_rules_f grdrule
334 where grdrule.rate_id = p_rate_id
335 and grdrule.grade_or_spinal_point_id = p_grade_id
336 and p_effective_date between grdrule.effective_start_date
337 and grdrule.effective_end_date;
338 --
339 if g_debug then
340 hr_utility.set_location(l_proc, 99);
341 end if;
342 --
343 return l_grd_mid_point;
344 end; -- get_grd_mid_point
345 --
346 -- --------------------------------------------------------------------------
347 -- |-------------------------< refresh_person_info >-------------------------|
348 -- --------------------------------------------------------------------------
349 --
350 procedure refresh_person_info(p_group_per_in_ler_id in number
351 ,p_effective_date in date
352 ,p_called_from_benmngle in boolean default false)
353 is
354
355 l_performance_rating_type ben_cwb_person_info.performance_rating_type%type;
356
357 --vkodedal cursor to fetch survey_id from eit setup.
358 cursor c_survey_id(l_group_pl_id in number) is
359 select PLI_INFORMATION2 from ben_pl_extra_info
360 where INFORMATION_TYPE='CWB_CUSTOM_DOWNLOAD'
361 and pl_id=l_group_pl_id;
362 --
363 -- cursor to fetch the person information
364 cursor csr_person_info(p_group_per_in_ler_id number
365 ,p_effective_date date
366 ,p_from_benmngle varchar2
367 ,p_appraisal_n_days number) is
368 --
369 select p_group_per_in_ler_id group_per_in_ler_id
370 ,pil.assignment_id assignment_id
371 ,decode(p_from_benmngle,'Y',-1,ppf.person_id)
372 person_id
373 ,paf.supervisor_id supervisor_id
374 ,p_effective_date effective_date
375 ,ppf.full_name full_name
376 ,trim(ppf.first_name ||' '||ppf.last_name||' '||ppf.suffix) brief_name
377 ,nvl(ben_cwb_custom_person_pkg.get_custom_name(ppf.person_id
378 ,pil.assignment_id
379 ,bg.org_information9
380 ,pil.group_pl_id
381 ,pil.lf_evt_ocrd_dt
382 ,p_effective_date),
383 ppf.full_name) custom_name
384 ,supv.full_name supervisor_full_name
385 ,trim(supv.first_name||' '||supv.last_name||' '||supv.suffix)
386 supervisor_brief_name
387 ,nvl(ben_cwb_custom_person_pkg.get_custom_name(supv.person_id
388 ,supv_asg.assignment_id
389 ,supv_bg.org_information9
390 ,pil.group_pl_id
391 ,pil.lf_evt_ocrd_dt
392 ,p_effective_date),
393 supv.full_name) supervisor_custom_name
394 ,bg.org_information9 legislation_code
395 ,trunc(months_between(p_effective_date,
396 nvl(service_period.adjusted_svc_date,
397 nvl(service_period.date_start,
398 ppf.start_date)))/12,1) years_employed
399 ,get_years_in_job(paf.assignment_id
400 ,paf.job_id
401 ,p_effective_date
402 ,paf.effective_start_date) years_in_job
403 ,get_years_in_position(paf.assignment_id
404 ,paf.position_id
405 ,p_effective_date
406 ,paf.effective_start_date) years_in_position
407 ,get_years_in_grade(paf.assignment_id
408 ,paf.grade_id
409 ,p_effective_date
410 ,paf.effective_start_date) years_in_grade
411 ,ppf.employee_number employee_number
412 ,nvl(service_period.date_start,ppf.start_date) start_date
413 ,ppf.original_date_of_hire original_start_date
414 ,service_period.adjusted_svc_date adjusted_svc_date
415 ,ppp.proposed_salary_n base_salary
416 ,ppp.change_date base_salary_change_date
417 ,pay.payroll_name payroll_name
418 ,perf.performance_rating performance_rating
419 ,perf.review_date performance_rating_date
420 ,paf.business_group_id business_group_id
421 ,paf.organization_id organization_id
422 ,paf.job_id job_id
423 ,paf.grade_id grade_id
424 ,paf.position_id position_id
425 ,paf.people_group_id people_group_id
426 ,paf.soft_coding_keyflex_id soft_coding_keyflex_id
427 ,paf.location_id location_id
428 ,ppb.rate_id pay_rate_id
429 ,paf.assignment_status_type_id assignment_status_type_id
430 ,paf.frequency frequency
431 ,nvl(ppb.grade_annualization_factor,1) grade_annualization_factor
432 ,nvl(ppb.pay_annualization_factor,1) pay_annualization_factor
433 ,get_grd_min_val(paf.grade_id
434 ,ppb.rate_id
435 ,p_effective_date) grd_min_val
436 ,get_grd_max_val(paf.grade_id
437 ,ppb.rate_id
438 ,p_effective_date) grd_max_val
439 ,get_grd_mid_point(paf.grade_id
440 ,ppb.rate_id
441 ,p_effective_date) grd_mid_point
442 ,paf.employment_category emp_category
443 ,paf.change_reason change_reason
444 ,paf.normal_hours normal_hours
445 ,ppf.email_address email_address
446 ,ppb.pay_basis base_salary_frequency
447 ,ppb.rate_basis grade_rate_frequency
448 ,ben_cwb_custom_person_pkg.get_custom_segment1(ppf.person_id
449 ,pil.assignment_id
450 ,bg.org_information9
451 ,pil.group_pl_id
452 ,pil.lf_evt_ocrd_dt
453 ,p_effective_date)
454 custom_segment1
455 ,ben_cwb_custom_person_pkg.get_custom_segment2(ppf.person_id
456 ,pil.assignment_id
457 ,bg.org_information9
458 ,pil.group_pl_id
459 ,pil.lf_evt_ocrd_dt
460 ,p_effective_date)
461 custom_segment2
462 ,ben_cwb_custom_person_pkg.get_custom_segment3(ppf.person_id
463 ,pil.assignment_id
464 ,bg.org_information9
465 ,pil.group_pl_id
466 ,pil.lf_evt_ocrd_dt
467 ,p_effective_date)
468 custom_segment3
469 ,ben_cwb_custom_person_pkg.get_custom_segment4(ppf.person_id
470 ,pil.assignment_id
471 ,bg.org_information9
472 ,pil.group_pl_id
473 ,pil.lf_evt_ocrd_dt
474 ,p_effective_date)
475 custom_segment4
476 ,ben_cwb_custom_person_pkg.get_custom_segment5(ppf.person_id
477 ,pil.assignment_id
478 ,bg.org_information9
479 ,pil.group_pl_id
480 ,pil.lf_evt_ocrd_dt
481 ,p_effective_date)
482 custom_segment5
483 ,ben_cwb_custom_person_pkg.get_custom_segment6(ppf.person_id
484 ,pil.assignment_id
485 ,bg.org_information9
486 ,pil.group_pl_id
487 ,pil.lf_evt_ocrd_dt
488 ,p_effective_date)
489 custom_segment6
490 ,ben_cwb_custom_person_pkg.get_custom_segment7(ppf.person_id
491 ,pil.assignment_id
492 ,bg.org_information9
493 ,pil.group_pl_id
494 ,pil.lf_evt_ocrd_dt
495 ,p_effective_date)
496 custom_segment7
497 ,ben_cwb_custom_person_pkg.get_custom_segment8(ppf.person_id
498 ,pil.assignment_id
499 ,bg.org_information9
500 ,pil.group_pl_id
501 ,pil.lf_evt_ocrd_dt
502 ,p_effective_date)
503 custom_segment8
504 ,ben_cwb_custom_person_pkg.get_custom_segment9(ppf.person_id
505 ,pil.assignment_id
506 ,bg.org_information9
507 ,pil.group_pl_id
508 ,pil.lf_evt_ocrd_dt
509 ,p_effective_date)
510 custom_segment9
511 ,ben_cwb_custom_person_pkg.get_custom_segment10(ppf.person_id
512 ,pil.assignment_id
513 ,bg.org_information9
514 ,pil.group_pl_id
515 ,pil.lf_evt_ocrd_dt
516 ,p_effective_date)
517 custom_segment10
518 ,ben_cwb_custom_person_pkg.get_custom_segment11(ppf.person_id
519 ,pil.assignment_id
520 ,bg.org_information9
521 ,pil.group_pl_id
522 ,pil.lf_evt_ocrd_dt
523 ,p_effective_date)
524 custom_segment11
525 ,ben_cwb_custom_person_pkg.get_custom_segment12(ppf.person_id
526 ,pil.assignment_id
527 ,bg.org_information9
528 ,pil.group_pl_id
529 ,pil.lf_evt_ocrd_dt
530 ,p_effective_date)
531 custom_segment12
532 ,ben_cwb_custom_person_pkg.get_custom_segment13(ppf.person_id
533 ,pil.assignment_id
534 ,bg.org_information9
535 ,pil.group_pl_id
536 ,pil.lf_evt_ocrd_dt
537 ,p_effective_date)
538 custom_segment13
539 ,ben_cwb_custom_person_pkg.get_custom_segment14(ppf.person_id
540 ,pil.assignment_id
541 ,bg.org_information9
542 ,pil.group_pl_id
543 ,pil.lf_evt_ocrd_dt
544 ,p_effective_date)
545 custom_segment14
546 ,ben_cwb_custom_person_pkg.get_custom_segment15(ppf.person_id
547 ,pil.assignment_id
548 ,bg.org_information9
549 ,pil.group_pl_id
550 ,pil.lf_evt_ocrd_dt
551 ,p_effective_date)
552 custom_segment15
553 ,ben_cwb_custom_person_pkg.get_custom_segment16(ppf.person_id
554 ,pil.assignment_id
555 ,bg.org_information9
556 ,pil.group_pl_id
557 ,pil.lf_evt_ocrd_dt
558 ,p_effective_date)
559 custom_segment16
560 ,ben_cwb_custom_person_pkg.get_custom_segment17(ppf.person_id
561 ,pil.assignment_id
562 ,bg.org_information9
563 ,pil.group_pl_id
564 ,pil.lf_evt_ocrd_dt
565 ,p_effective_date)
566 custom_segment17
567 ,ben_cwb_custom_person_pkg.get_custom_segment18(ppf.person_id
568 ,pil.assignment_id
569 ,bg.org_information9
570 ,pil.group_pl_id
571 ,pil.lf_evt_ocrd_dt
572 ,p_effective_date)
573 custom_segment18
574 ,ben_cwb_custom_person_pkg.get_custom_segment19(ppf.person_id
575 ,pil.assignment_id
576 ,bg.org_information9
577 ,pil.group_pl_id
578 ,pil.lf_evt_ocrd_dt
579 ,p_effective_date)
580 custom_segment19
581 ,ben_cwb_custom_person_pkg.get_custom_segment20(ppf.person_id
582 ,pil.assignment_id
583 ,bg.org_information9
584 ,pil.group_pl_id
585 ,pil.lf_evt_ocrd_dt
586 ,p_effective_date)
587 custom_segment20
588 ,paf.ass_attribute_category ass_attribute_category
589 ,paf.ass_attribute1 ass_attribute1
590 ,paf.ass_attribute2 ass_attribute2
591 ,paf.ass_attribute3 ass_attribute3
592 ,paf.ass_attribute4 ass_attribute4
593 ,paf.ass_attribute5 ass_attribute5
594 ,paf.ass_attribute6 ass_attribute6
595 ,paf.ass_attribute7 ass_attribute7
596 ,paf.ass_attribute8 ass_attribute8
597 ,paf.ass_attribute9 ass_attribute9
598 ,paf.ass_attribute10 ass_attribute10
599 ,paf.ass_attribute11 ass_attribute11
600 ,paf.ass_attribute12 ass_attribute12
601 ,paf.ass_attribute13 ass_attribute13
602 ,paf.ass_attribute14 ass_attribute14
603 ,paf.ass_attribute15 ass_attribute15
604 ,paf.ass_attribute16 ass_attribute16
605 ,paf.ass_attribute17 ass_attribute17
606 ,paf.ass_attribute18 ass_attribute18
607 ,paf.ass_attribute19 ass_attribute19
608 ,paf.ass_attribute20 ass_attribute20
609 ,paf.ass_attribute21 ass_attribute21
610 ,paf.ass_attribute22 ass_attribute22
611 ,paf.ass_attribute23 ass_attribute23
612 ,paf.ass_attribute24 ass_attribute24
613 ,paf.ass_attribute25 ass_attribute25
614 ,paf.ass_attribute26 ass_attribute26
615 ,paf.ass_attribute27 ass_attribute27
616 ,paf.ass_attribute28 ass_attribute28
617 ,paf.ass_attribute29 ass_attribute29
618 ,paf.ass_attribute30 ass_attribute30
619 ,grp.group_name people_group_name
620 ,grp.segment1 people_group_segment1
621 ,grp.segment2 people_group_segment2
622 ,grp.segment3 people_group_segment3
623 ,grp.segment4 people_group_segment4
624 ,grp.segment5 people_group_segment5
625 ,grp.segment6 people_group_segment6
626 ,grp.segment7 people_group_segment7
627 ,grp.segment8 people_group_segment8
628 ,grp.segment9 people_group_segment9
629 ,grp.segment10 people_group_segment10
630 ,grp.segment11 people_group_segment11
631 ,get_salary_currency(ppb.input_value_id
632 ,p_effective_date) base_salary_currency
633 ,pil.group_pl_id group_pl_id
634 ,pil.lf_evt_ocrd_dt lf_evt_ocrd_dt
635 ,ppp_prev.proposed_salary_n prev_sal
636 ,ppb_prev.pay_basis prev_sal_frequency
637 ,get_salary_currency(ppb_prev.input_value_id
638 ,ppp_prev.change_date) prev_sal_currency
639 ,nvl(ppb_prev.pay_annualization_factor,1) prev_sal_ann_fctr
640 ,ppp.change_date prev_sal_chg_date
641 ,ppp.proposal_reason prev_sal_chg_rsn
642 ,get_fte_factor(paf.assignment_id
643 ,p_effective_date) fte_factor
644 ,get_fte_factor(paf.assignment_id
645 ,ppp_prev.change_date) prev_fte_factor
646 ,ppp.pay_proposal_id current_pay_proposal_id
647 , (CASE
648 WHEN perf.event_id IS NULL THEN
649 NULL
650 WHEN perf.event_id IS NOT NULL THEN
651 (SELECT appraisal_id
652 FROM per_appraisals
653 WHERE appraisee_person_id = ppf.person_id
654 AND event_id = perf.event_id)
655 END ) appraisal_id
656 from per_all_people_f ppf
657 ,per_all_assignments_f paf
658 ,ben_per_in_ler pil
659 ,per_all_people_f supv
660 ,per_all_assignments_f supv_asg
661 ,hr_organization_information bg
662 ,hr_organization_information supv_bg
663 ,per_periods_of_service service_period
664 ,per_pay_proposals ppp
665 ,pay_all_payrolls_f pay
666 ,(select rtg1.review_date review_date
667 ,rtg1.performance_rating performance_rating
668 ,rtg1.person_id person_id
669 ,evt1.event_id
670 from per_performance_reviews rtg1
671 ,per_events evt1
672 where rtg1.event_id = evt1.event_id (+)
673 --and rtg1.review_date <= p_effective_date
674 --ER:8369634
675 and ((p_appraisal_n_days is not null and rtg1.review_date between (p_effective_date - p_appraisal_n_days) and p_effective_date)
676 or (p_appraisal_n_days is null and rtg1.review_date <= p_effective_date))
677 and nvl(evt1.type, '-X-X-X-') = nvl(l_performance_rating_type, '-X-X-X-')
678 ) perf
679 ,per_pay_bases ppb
680 ,pay_people_groups grp
681 ,per_all_assignments_f paf_prev
682 ,per_pay_proposals ppp_prev
683 ,per_pay_bases ppb_prev
684 where pil.per_in_ler_id = p_group_per_in_ler_id
685 and paf.assignment_id = pil.assignment_id
686 and p_effective_date between paf.effective_start_date and
687 paf.effective_end_date
688 and paf.person_id = ppf.person_id
689 and p_effective_date between ppf.effective_start_date and
690 ppf.effective_end_date
691 and paf.supervisor_id = supv.person_id (+)
692 and p_effective_date between supv.effective_start_date (+) and
693 supv.effective_end_date (+)
694 and supv.person_id = supv_asg.person_id (+)
695 and p_effective_date between supv_asg.effective_start_date (+) and
696 supv_asg.effective_end_date (+)
697 and supv_asg.primary_flag (+) = 'Y'
698 and bg.organization_id = paf.business_group_id
699 and bg.org_information_context = 'Business Group Information'
700 and supv_bg.organization_id (+) = supv_asg.business_group_id
701 and supv_bg.org_information_context (+) = 'Business Group Information'
702 and paf.period_of_service_id = service_period.period_of_service_id (+)
703 and paf.assignment_id = ppp.assignment_id (+)
704 and ppp.approved (+) = 'Y'
705 and ppp.change_date (+) <= p_effective_date
706 and nvl(ppp.change_date,to_date('4712/12/31', 'yyyy/mm/dd')) =
707 (select nvl(max(ppp1.change_date), to_date('4712/12/31',
708 'yyyy/mm/dd'))
709 from per_pay_proposals ppp1
710 where ppp1.assignment_id = ppp.assignment_id
711 and ppp1.change_date <= p_effective_date
712 and ppp1.approved = 'Y')
713 and paf.payroll_id = pay.payroll_id (+)
714 and p_effective_date between pay.effective_start_date (+) and
715 pay.effective_end_date (+)
716 and ppf.person_id = perf.person_id (+)
717 and nvl(perf.review_date, to_date('4712/12/31', 'yyyy/mm/dd')) =
718 (select nvl(max(rtg2.review_date),to_date('4712/12/31',
719 'yyyy/mm/dd'))
720 from per_performance_reviews rtg2
721 ,per_events evt2
722 where rtg2.person_id = ppf.person_id
723 -- and rtg2.review_date <= p_effective_date
724 --ER:8369634
725 and ((p_appraisal_n_days is not null and rtg2.review_date between (p_effective_date - p_appraisal_n_days) and p_effective_date)
726 or (p_appraisal_n_days is null and rtg2.review_date <= p_effective_date))
727 and rtg2.event_id = evt2.event_id (+)
728 and nvl(evt2.type, '-X-X-X-') = nvl(l_performance_rating_type, '-X-X-X-') )
729 and paf.pay_basis_id = ppb.pay_basis_id (+)
730 and grp.people_group_id (+) = paf.people_group_id
731 and ppp_prev.assignment_id (+) = ppp.assignment_id
732 and ppp_prev.approved (+) = 'Y'
733 and ppp_prev.change_date (+) < ppp.change_date
734 and nvl(ppp_prev.change_date, to_date('4712/12/31', 'yyyy/mm/dd')) =
735 (select nvl(max(ppp1.change_date), to_date('4712/12/31',
736 'yyyy/mm/dd'))
737 from per_pay_proposals ppp1
738 where ppp1.assignment_id = ppp.assignment_id
739 and ppp1.change_date < ppp.change_date
740 and ppp1.approved = 'Y')
741 and paf_prev.assignment_id (+) = ppp_prev.assignment_id
742 and ppp_prev.change_date between paf_prev.effective_start_date (+) and
743 paf_prev.effective_end_date (+)
744 and paf_prev.pay_basis_id = ppb_prev.pay_basis_id (+);
745 --
746 cursor c_person_info is
747 select cpi.effective_date
748 from ben_cwb_person_info cpi
749 where cpi.group_per_in_ler_id = p_group_per_in_ler_id;
750
751 --Bug fix 9760967
752 cursor csr_grp_plan_extra_info(p_group_pl_id number, p_lf_evt_ocrd_dt date)
753 is
754 select nvl(pl_dsgn.show_appraisals_n_days,pl_info.pli_information4) show_appraisals_n_days
755 from ben_pl_extra_info pl_info,
756 ben_cwb_pl_dsgn pl_dsgn
757 where pl_dsgn.pl_id = p_group_pl_id
758 and pl_dsgn.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
759 and pl_info.information_type(+) = 'CWB_CUSTOM_DOWNLOAD'
760 and pl_info.pl_id(+) = pl_dsgn.pl_id;
761
762 --
763
764 l_data_freeze_date date;
765 l_effective_date date;
766 l_lf_evt_ocrd_dt date;
767 l_from_benmngle varchar2(1);
768 l_cpi_effective_date date;
769 l_assignment_id number;
770 l_group_pl_id number;
771 l_rec_modified boolean := false;
772 l_pay_annualization_factor number;
773 l_fte_annual_salary number;
774 l_annual_grd_min_val number;
775 l_annual_grd_mid_point number;
776 l_annual_grd_max_val number;
777 l_salary_1_year_ago c_sal_info%rowtype;
778 l_salary_2_year_ago c_sal_info%rowtype;
779 l_salary_3_year_ago c_sal_info%rowtype;
780 l_salary_4_year_ago c_sal_info%rowtype;
781 l_salary_5_year_ago c_sal_info%rowtype;
782 l_survey_info c_survey_info%rowtype;
783 l_appraisal_n_days number := null;
784 --
785 l_proc varchar2(72) := g_package||'refresh_person_info';
786 --
787 begin
788 --
789 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
790 'refresh_person_info';
791 ben_manage_cwb_life_events.g_error_log_rec.step_number := 211;
792 --
793 if g_debug then
794 hr_utility.set_location('Entering:'|| l_proc, 10);
795 end if;
796 --
797 -- get the performance_rating_type from pl_dsgn. This value is same
798 -- for all plans and oipls in a group plan.
799
800 select pldsgn.emp_interview_typ_cd, pldsgn.data_freeze_date,
801 pil.lf_evt_ocrd_dt, pil.group_pl_id, pil.assignment_id
802 into l_performance_rating_type, l_data_freeze_date,
803 l_lf_evt_ocrd_dt, l_group_pl_id, l_assignment_id
804 from ben_cwb_pl_dsgn pldsgn
805 ,ben_per_in_ler pil
806 where pil.per_in_ler_id = p_group_per_in_ler_id
807 and pil.group_pl_id = pldsgn.pl_id
808 and pil.lf_evt_ocrd_dt = pldsgn.lf_evt_ocrd_dt
809 and pldsgn.oipl_id = -1 ;
810 --
811 open c_person_info;
812 fetch c_person_info into l_cpi_effective_date;
813 close c_person_info;
814 --
815 -- the effective_date for fetching date track tables is
816 -- 1. p_effective_date,
817 -- 2. effective_date on person_info rec,
818 -- 3. data_freeze_date from pl_dsgn table,
819 -- 4. lf_evt_ocrd_dt
820 if p_effective_date is not null then
821 l_effective_date := p_effective_date;
822 elsif l_cpi_effective_date is not null then
823 l_effective_date := l_cpi_effective_date;
824 elsif l_data_freeze_date is not null then
825 l_effective_date := l_data_freeze_date;
826 else
827 l_effective_date := l_lf_evt_ocrd_dt;
828 end if;
829 --
830 if (p_called_from_benmngle) then
831 l_from_benmngle := 'Y';
832 else
833 l_from_benmngle := 'N';
834 end if;
835 --
836 if g_debug then
837 hr_utility.set_location(l_proc, 20);
838 end if;
839 --vkodedal get the salary suvey id from EIT setup
840
841 open c_survey_id(l_group_pl_id);
842 fetch c_survey_id into g_salary_survey_id;
843 close c_survey_id;
844 --
845 if g_debug then
846 hr_utility.set_location('c_survey_id:'|| g_salary_survey_id, 15);
847 end if;
848 --
849 --ER:8369634
850 --Bug fix 9760967
851 for l_grp_plan_extra_info in csr_grp_plan_extra_info(l_group_pl_id,l_lf_evt_ocrd_dt) loop
852 l_appraisal_n_days := to_number(l_grp_plan_extra_info.show_appraisals_n_days);
853 end loop;
854 if g_debug then
855 hr_utility.set_location('show_appraisals_n_days:'|| l_appraisal_n_days, 15);
856 end if;
857
858 -- Open the cursor and update the details in ben_cwb_person_info
859 -- It always should return only one row, but using the for to avoid
860 -- the declaration of too many local variables.
861
862 for perinfo_rec in csr_person_info(
863 p_group_per_in_ler_id => p_group_per_in_ler_id
864 ,p_effective_date => l_effective_date
865 ,p_from_benmngle => l_from_benmngle
866 ,p_appraisal_n_days => l_appraisal_n_days) loop
867 --
868 ben_manage_cwb_life_events.g_error_log_rec.calling_proc :=
869 'refresh_person_info';
870 ben_manage_cwb_life_events.g_error_log_rec.step_number := 212;
871 --
872 l_salary_1_year_ago := get_salary_info(perinfo_rec.assignment_id,
873 add_months(l_effective_date,-12));
874 if l_salary_1_year_ago.assignment_id is not null then
875 l_salary_2_year_ago := get_salary_info(perinfo_rec.assignment_id,
876 add_months(l_effective_date,-24));
877 end if;
878 if l_salary_2_year_ago.assignment_id is not null then
879 l_salary_3_year_ago := get_salary_info(perinfo_rec.assignment_id,
880 add_months(l_effective_date,-36));
881 end if;
882 if l_salary_3_year_ago.assignment_id is not null then
883 l_salary_4_year_ago := get_salary_info(perinfo_rec.assignment_id,
884 add_months(l_effective_date,-48));
885 end if;
886 if l_salary_4_year_ago.assignment_id is not null then
887 l_salary_5_year_ago := get_salary_info(perinfo_rec.assignment_id,
888 add_months(l_effective_date,-60));
889 end if;
890 --
891 if g_salary_survey_id is not null and
892 perinfo_rec.job_id is not null then
893 l_survey_info := get_survey_info(perinfo_rec.job_id, l_effective_date);
894 end if;
895 --
896 l_rec_modified := true;
897 --
898 -- If a person has hourly salary, then adjust the pay annualization factor
899 -- to reflect his part time pay, for calculations.
900 -- Calculate fte annual salary, grd vals as per the annualization factor.
901 --
902 if('HOURLY' = perinfo_rec.base_salary_frequency) then
903 l_pay_annualization_factor := perinfo_rec.pay_annualization_factor * perinfo_rec.fte_factor;
904 else
905 l_pay_annualization_factor := perinfo_rec.pay_annualization_factor;
906 end if;
907 --
908 l_fte_annual_salary := perinfo_rec.base_salary * l_pay_annualization_factor / perinfo_rec.fte_factor;
909 l_annual_grd_min_val := perinfo_rec.grd_min_val * perinfo_rec.grade_annualization_factor;
910 l_annual_grd_mid_point := perinfo_rec.grd_mid_point * perinfo_rec.grade_annualization_factor;
911 l_annual_grd_max_val := perinfo_rec.grd_max_val * perinfo_rec.grade_annualization_factor;
912 --
913 if l_cpi_effective_date is not null then
914 update ben_cwb_person_info
915 set person_id = perinfo_rec.person_id
916 ,supervisor_id = perinfo_rec.supervisor_id
917 ,assignment_id = perinfo_rec.assignment_id
918 ,effective_date = perinfo_rec.effective_date
919 ,full_name = perinfo_rec.full_name
920 ,brief_name = perinfo_rec.brief_name
921 ,custom_name = perinfo_rec.custom_name
922 ,supervisor_full_name = perinfo_rec.supervisor_full_name
923 ,supervisor_brief_name = perinfo_rec.supervisor_brief_name
924 ,supervisor_custom_name = perinfo_rec.supervisor_custom_name
925 ,legislation_code = perinfo_rec.legislation_code
926 ,years_employed = perinfo_rec.years_employed
927 ,years_in_job = perinfo_rec.years_in_job
928 ,years_in_position = perinfo_rec.years_in_position
929 ,years_in_grade = perinfo_rec.years_in_grade
930 ,employee_number = perinfo_rec.employee_number
931 ,start_date = perinfo_rec.start_date
932 ,original_start_date = perinfo_rec.original_start_date
933 ,adjusted_svc_date = perinfo_rec.adjusted_svc_date
934 ,base_salary = perinfo_rec.base_salary
935 ,base_salary_change_date = perinfo_rec.base_salary_change_date
936 ,payroll_name = perinfo_rec.payroll_name
937 ,performance_rating = perinfo_rec.performance_rating
938 ,performance_rating_type = l_performance_rating_type
939 ,performance_rating_date = perinfo_rec.performance_rating_date
940 ,business_group_id = perinfo_rec.business_group_id
941 ,organization_id = perinfo_rec.organization_id
942 ,job_id = perinfo_rec.job_id
943 ,grade_id = perinfo_rec.grade_id
944 ,position_id = perinfo_rec.position_id
945 ,people_group_id = perinfo_rec.people_group_id
946 ,soft_coding_keyflex_id = perinfo_rec.soft_coding_keyflex_id
947 ,location_id = perinfo_rec.location_id
948 ,pay_rate_id = perinfo_rec.pay_rate_id
949 ,grade_annulization_factor = perinfo_rec.grade_annualization_factor
950 ,pay_annulization_factor = perinfo_rec.pay_annualization_factor
951 ,grd_min_val = perinfo_rec.grd_min_val
952 ,grd_max_val = perinfo_rec.grd_max_val
953 ,grd_mid_point = perinfo_rec.grd_mid_point
954 ,emp_category = perinfo_rec.emp_category
955 ,change_reason = perinfo_rec.change_reason
956 ,normal_hours = perinfo_rec.normal_hours
957 ,email_address = perinfo_rec.email_address
958 ,base_salary_frequency = perinfo_rec.base_salary_frequency
959 ,assignment_status_type_id = perinfo_rec.assignment_status_type_id
960 ,frequency = perinfo_rec.frequency
961 ,grd_quartile = get_grd_quartile
962 (l_fte_annual_salary
963 ,l_annual_grd_min_val
964 ,l_annual_grd_max_val
965 ,l_annual_grd_mid_point)
966 ,grd_comparatio = get_grd_comparatio
967 (l_fte_annual_salary
968 ,l_annual_grd_mid_point)
969 ,custom_segment1 = perinfo_rec.custom_segment1
970 ,custom_segment2 = perinfo_rec.custom_segment2
971 ,custom_segment3 = perinfo_rec.custom_segment3
972 ,custom_segment4 = perinfo_rec.custom_segment4
973 ,custom_segment5 = perinfo_rec.custom_segment5
974 ,custom_segment6 = perinfo_rec.custom_segment6
975 ,custom_segment7 = perinfo_rec.custom_segment7
976 ,custom_segment8 = perinfo_rec.custom_segment8
977 ,custom_segment9 = perinfo_rec.custom_segment9
978 ,custom_segment10 = perinfo_rec.custom_segment10
979 ,custom_segment11 = perinfo_rec.custom_segment11
980 ,custom_segment12 = perinfo_rec.custom_segment12
981 ,custom_segment13 = perinfo_rec.custom_segment13
982 ,custom_segment14 = perinfo_rec.custom_segment14
983 ,custom_segment15 = perinfo_rec.custom_segment15
984 ,custom_segment16 = perinfo_rec.custom_segment16
985 ,custom_segment17 = perinfo_rec.custom_segment17
986 ,custom_segment18 = perinfo_rec.custom_segment18
987 ,custom_segment19 = perinfo_rec.custom_segment19
988 ,custom_segment20 = perinfo_rec.custom_segment20
989 ,ass_attribute_category = perinfo_rec.ass_attribute_category
990 ,ass_attribute1 = perinfo_rec.ass_attribute1
991 ,ass_attribute2 = perinfo_rec.ass_attribute2
992 ,ass_attribute3 = perinfo_rec.ass_attribute3
993 ,ass_attribute4 = perinfo_rec.ass_attribute4
994 ,ass_attribute5 = perinfo_rec.ass_attribute5
995 ,ass_attribute6 = perinfo_rec.ass_attribute6
996 ,ass_attribute7 = perinfo_rec.ass_attribute7
997 ,ass_attribute8 = perinfo_rec.ass_attribute8
998 ,ass_attribute9 = perinfo_rec.ass_attribute9
999 ,ass_attribute10 = perinfo_rec.ass_attribute10
1000 ,ass_attribute11 = perinfo_rec.ass_attribute11
1001 ,ass_attribute12 = perinfo_rec.ass_attribute12
1002 ,ass_attribute13 = perinfo_rec.ass_attribute13
1003 ,ass_attribute14 = perinfo_rec.ass_attribute14
1004 ,ass_attribute15 = perinfo_rec.ass_attribute15
1005 ,ass_attribute16 = perinfo_rec.ass_attribute16
1006 ,ass_attribute17 = perinfo_rec.ass_attribute17
1007 ,ass_attribute18 = perinfo_rec.ass_attribute18
1008 ,ass_attribute19 = perinfo_rec.ass_attribute19
1009 ,ass_attribute20 = perinfo_rec.ass_attribute20
1010 ,ass_attribute21 = perinfo_rec.ass_attribute21
1011 ,ass_attribute22 = perinfo_rec.ass_attribute22
1012 ,ass_attribute23 = perinfo_rec.ass_attribute23
1013 ,ass_attribute24 = perinfo_rec.ass_attribute24
1014 ,ass_attribute25 = perinfo_rec.ass_attribute25
1015 ,ass_attribute26 = perinfo_rec.ass_attribute26
1016 ,ass_attribute27 = perinfo_rec.ass_attribute27
1017 ,ass_attribute28 = perinfo_rec.ass_attribute28
1018 ,ass_attribute29 = perinfo_rec.ass_attribute29
1019 ,ass_attribute30 = perinfo_rec.ass_attribute30
1020 ,people_group_name = perinfo_rec.people_group_name
1021 ,people_group_segment1 = perinfo_rec.people_group_segment1
1022 ,people_group_segment2 = perinfo_rec.people_group_segment2
1023 ,people_group_segment3 = perinfo_rec.people_group_segment3
1024 ,people_group_segment4 = perinfo_rec.people_group_segment4
1025 ,people_group_segment5 = perinfo_rec.people_group_segment5
1026 ,people_group_segment6 = perinfo_rec.people_group_segment6
1027 ,people_group_segment7 = perinfo_rec.people_group_segment7
1028 ,people_group_segment8 = perinfo_rec.people_group_segment8
1029 ,people_group_segment9 = perinfo_rec.people_group_segment9
1030 ,people_group_segment10 = perinfo_rec.people_group_segment10
1031 ,people_group_segment11 = perinfo_rec.people_group_segment11
1032 ,group_pl_id = perinfo_rec.group_pl_id
1033 ,lf_evt_ocrd_dt = perinfo_rec.lf_evt_ocrd_dt
1034 ,fte_factor = perinfo_rec.fte_factor
1035 ,grd_quintile = get_grd_quintile
1036 (l_fte_annual_salary
1037 ,l_annual_grd_min_val
1038 ,l_annual_grd_max_val)
1039 ,grd_decile = get_grd_decile
1040 (l_fte_annual_salary
1041 ,l_annual_grd_min_val
1042 ,l_annual_grd_max_val)
1043 ,grd_pct_in_range = get_grd_pct_in_range
1044 (l_fte_annual_salary
1045 ,l_annual_grd_min_val
1046 ,l_annual_grd_max_val)
1047 ,grade_rate_frequency = perinfo_rec.grade_rate_frequency
1048 ,base_salary_currency = perinfo_rec.base_salary_currency
1049 ,salary_1_year_ago = l_salary_1_year_ago.salary
1050 ,salary_1_year_ago_frequency = l_salary_1_year_ago.salary_frequency
1051 ,salary_1_year_ago_currency = l_salary_1_year_ago.salary_currency
1052 ,salary_1_year_ago_ann_fctr = decode(l_salary_1_year_ago.salary_frequency
1053 ,'HOURLY',l_salary_1_year_ago.salary_ann_fctr * l_salary_1_year_ago.fte_factor
1054 ,l_salary_1_year_ago.salary_ann_fctr)
1055 ,salary_2_year_ago = l_salary_2_year_ago.salary
1056 ,salary_2_year_ago_frequency = l_salary_2_year_ago.salary_frequency
1057 ,salary_2_year_ago_currency = l_salary_2_year_ago.salary_currency
1058 ,salary_2_year_ago_ann_fctr = decode(l_salary_2_year_ago.salary_frequency
1059 ,'HOURLY',l_salary_2_year_ago.salary_ann_fctr * l_salary_2_year_ago.fte_factor
1060 ,l_salary_2_year_ago.salary_ann_fctr)
1061 ,salary_3_year_ago = l_salary_3_year_ago.salary
1062 ,salary_3_year_ago_frequency = l_salary_3_year_ago.salary_frequency
1063 ,salary_3_year_ago_currency = l_salary_3_year_ago.salary_currency
1064 ,salary_3_year_ago_ann_fctr = decode(l_salary_3_year_ago.salary_frequency
1065 ,'HOURLY',l_salary_3_year_ago.salary_ann_fctr * l_salary_3_year_ago.fte_factor
1066 ,l_salary_3_year_ago.salary_ann_fctr)
1067 ,salary_4_year_ago = l_salary_4_year_ago.salary
1068 ,salary_4_year_ago_frequency = l_salary_4_year_ago.salary_frequency
1069 ,salary_4_year_ago_currency = l_salary_4_year_ago.salary_currency
1070 ,salary_4_year_ago_ann_fctr = decode(l_salary_4_year_ago.salary_frequency
1071 ,'HOURLY',l_salary_4_year_ago.salary_ann_fctr * l_salary_4_year_ago.fte_factor
1072 ,l_salary_4_year_ago.salary_ann_fctr)
1073 ,salary_5_year_ago = l_salary_5_year_ago.salary
1074 ,salary_5_year_ago_frequency = l_salary_5_year_ago.salary_frequency
1075 ,salary_5_year_ago_currency = l_salary_5_year_ago.salary_currency
1076 ,salary_5_year_ago_ann_fctr = decode(l_salary_5_year_ago.salary_frequency
1077 ,'HOURLY',l_salary_5_year_ago.salary_ann_fctr * l_salary_5_year_ago.fte_factor
1078 ,l_salary_5_year_ago.salary_ann_fctr)
1079 ,prev_sal = perinfo_rec.prev_sal
1080 ,prev_sal_frequency = perinfo_rec.prev_sal_frequency
1081 ,prev_sal_currency = perinfo_rec.prev_sal_currency
1082 ,prev_sal_ann_fctr = decode(perinfo_rec.prev_sal_frequency
1083 ,'HOURLY',perinfo_rec.prev_sal_ann_fctr * perinfo_rec.prev_fte_factor
1084 ,perinfo_rec.prev_sal_ann_fctr)
1085 ,prev_sal_chg_date = perinfo_rec.prev_sal_chg_date
1086 ,prev_sal_chg_rsn = perinfo_rec.prev_sal_chg_rsn
1087 ,mkt_currency = l_survey_info.currency
1088 ,mkt_frequency = l_survey_info.frequency
1089 ,mkt_annualization_factor = l_survey_info.annualization_factor
1090 ,mkt_min_salary = l_survey_info.min_salary
1091 ,mkt_25pct_salary = l_survey_info.pct25_salary
1092 ,mkt_mid_salary = l_survey_info.mid_salary
1093 ,mkt_75pct_salary = l_survey_info.pct75_salary
1094 ,mkt_max_salary = l_survey_info.max_salary
1095 ,mkt_emp_quartile = null
1096 ,mkt_emp_pct_in_range = null
1097 ,mkt_emp_comparatio = null
1098 ,mkt_survey_id = l_survey_info.survey_id
1099 ,current_pay_proposal_id = perinfo_rec.current_pay_proposal_id
1100 ,appraisal_id = perinfo_rec.appraisal_id
1101 where group_per_in_ler_id = perinfo_rec.group_per_in_ler_id;
1102 --
1103 if g_debug then
1104 hr_utility.set_location(l_proc, 30);
1105 end if;
1106 --
1107 else
1108 -- The record does not exist. So insert the new record
1109 --
1110 insert into ben_cwb_person_info
1111 (group_per_in_ler_id
1112 ,person_id
1113 ,supervisor_id
1114 ,assignment_id
1115 ,effective_date
1116 ,full_name
1117 ,brief_name
1118 ,custom_name
1119 ,supervisor_full_name
1120 ,supervisor_brief_name
1121 ,supervisor_custom_name
1122 ,legislation_code
1123 ,years_employed
1124 ,years_in_job
1125 ,years_in_position
1126 ,years_in_grade
1127 ,employee_number
1128 ,start_date
1129 ,original_start_date
1130 ,adjusted_svc_date
1131 ,base_salary
1132 ,base_salary_change_date
1133 ,payroll_name
1134 ,performance_rating
1135 ,performance_rating_type
1136 ,performance_rating_date
1137 ,business_group_id
1138 ,organization_id
1139 ,job_id
1140 ,grade_id
1141 ,position_id
1142 ,people_group_id
1143 ,soft_coding_keyflex_id
1144 ,location_id
1145 ,pay_rate_id
1146 ,assignment_status_type_id
1147 ,frequency
1148 ,grade_annulization_factor
1149 ,pay_annulization_factor
1150 ,grd_min_val
1151 ,grd_max_val
1152 ,grd_mid_point
1153 ,grd_quartile
1154 ,grd_comparatio
1155 ,emp_category
1156 ,change_reason
1157 ,normal_hours
1158 ,email_address
1159 ,base_salary_frequency
1160 ,custom_segment1
1161 ,custom_segment2
1162 ,custom_segment3
1163 ,custom_segment4
1164 ,custom_segment5
1165 ,custom_segment6
1166 ,custom_segment7
1167 ,custom_segment8
1168 ,custom_segment9
1169 ,custom_segment10
1170 ,custom_segment11
1171 ,custom_segment12
1172 ,custom_segment13
1173 ,custom_segment14
1174 ,custom_segment15
1175 ,custom_segment16
1176 ,custom_segment17
1177 ,custom_segment18
1178 ,custom_segment19
1179 ,custom_segment20
1180 ,ass_attribute_category
1181 ,ass_attribute1
1182 ,ass_attribute2
1183 ,ass_attribute3
1184 ,ass_attribute4
1185 ,ass_attribute5
1186 ,ass_attribute6
1187 ,ass_attribute7
1188 ,ass_attribute8
1189 ,ass_attribute9
1190 ,ass_attribute10
1191 ,ass_attribute11
1192 ,ass_attribute12
1193 ,ass_attribute13
1194 ,ass_attribute14
1195 ,ass_attribute15
1196 ,ass_attribute16
1197 ,ass_attribute17
1198 ,ass_attribute18
1199 ,ass_attribute19
1200 ,ass_attribute20
1201 ,ass_attribute21
1202 ,ass_attribute22
1203 ,ass_attribute23
1204 ,ass_attribute24
1205 ,ass_attribute25
1206 ,ass_attribute26
1207 ,ass_attribute27
1208 ,ass_attribute28
1209 ,ass_attribute29
1210 ,ass_attribute30
1211 ,people_group_name
1212 ,people_group_segment1
1213 ,people_group_segment2
1214 ,people_group_segment3
1215 ,people_group_segment4
1216 ,people_group_segment5
1217 ,people_group_segment6
1218 ,people_group_segment7
1219 ,people_group_segment8
1220 ,people_group_segment9
1221 ,people_group_segment10
1222 ,people_group_segment11
1223 ,object_version_number
1224 ,group_pl_id
1225 ,lf_evt_ocrd_dt
1226 ,fte_factor
1227 ,grd_quintile
1228 ,grd_decile
1229 ,grd_pct_in_range
1230 ,grade_rate_frequency
1231 ,base_salary_currency
1232 ,salary_1_year_ago
1233 ,salary_1_year_ago_frequency
1234 ,salary_1_year_ago_currency
1235 ,salary_1_year_ago_ann_fctr
1236 ,salary_2_year_ago
1237 ,salary_2_year_ago_frequency
1238 ,salary_2_year_ago_currency
1239 ,salary_2_year_ago_ann_fctr
1240 ,salary_3_year_ago
1241 ,salary_3_year_ago_frequency
1242 ,salary_3_year_ago_currency
1243 ,salary_3_year_ago_ann_fctr
1244 ,salary_4_year_ago
1245 ,salary_4_year_ago_frequency
1246 ,salary_4_year_ago_currency
1247 ,salary_4_year_ago_ann_fctr
1248 ,salary_5_year_ago
1249 ,salary_5_year_ago_frequency
1250 ,salary_5_year_ago_currency
1251 ,salary_5_year_ago_ann_fctr
1252 ,prev_sal
1253 ,prev_sal_frequency
1254 ,prev_sal_currency
1255 ,prev_sal_ann_fctr
1256 ,prev_sal_chg_date
1257 ,prev_sal_chg_rsn
1258 ,mkt_currency
1259 ,mkt_frequency
1260 ,mkt_annualization_factor
1261 ,mkt_min_salary
1262 ,mkt_25pct_salary
1263 ,mkt_mid_salary
1264 ,mkt_75pct_salary
1265 ,mkt_max_salary
1266 ,mkt_emp_quartile
1267 ,mkt_emp_pct_in_range
1268 ,mkt_emp_comparatio
1269 ,mkt_survey_id
1270 ,current_pay_proposal_id
1271 ,appraisal_id)
1272 values (p_group_per_in_ler_id
1273 ,perinfo_rec.person_id
1274 ,perinfo_rec.supervisor_id
1275 ,perinfo_rec.assignment_id
1276 ,perinfo_rec.effective_date
1277 ,perinfo_rec.full_name
1278 ,perinfo_rec.brief_name
1279 ,perinfo_rec.custom_name
1280 ,perinfo_rec.supervisor_full_name
1281 ,perinfo_rec.supervisor_brief_name
1282 ,perinfo_rec.supervisor_custom_name
1283 ,perinfo_rec.legislation_code
1284 ,perinfo_rec.years_employed
1285 ,perinfo_rec.years_in_job
1286 ,perinfo_rec.years_in_position
1287 ,perinfo_rec.years_in_grade
1288 ,perinfo_rec.employee_number
1289 ,perinfo_rec.start_date
1290 ,perinfo_rec.original_start_date
1291 ,perinfo_rec.adjusted_svc_date
1292 ,perinfo_rec.base_salary
1293 ,perinfo_rec.base_salary_change_date
1294 ,perinfo_rec.payroll_name
1295 ,perinfo_rec.performance_rating
1296 ,l_performance_rating_type
1297 ,perinfo_rec.performance_rating_date
1298 ,perinfo_rec.business_group_id
1299 ,perinfo_rec.organization_id
1300 ,perinfo_rec.job_id
1301 ,perinfo_rec.grade_id
1302 ,perinfo_rec.position_id
1303 ,perinfo_rec.people_group_id
1304 ,perinfo_rec.soft_coding_keyflex_id
1305 ,perinfo_rec.location_id
1306 ,perinfo_rec.pay_rate_id
1307 ,perinfo_rec.assignment_status_type_id
1308 ,perinfo_rec.frequency
1309 ,perinfo_rec.grade_annualization_factor
1310 ,perinfo_rec.pay_annualization_factor
1311 ,perinfo_rec.grd_min_val
1312 ,perinfo_rec.grd_max_val
1313 ,perinfo_rec.grd_mid_point
1314 ,get_grd_quartile
1315 (l_fte_annual_salary
1316 ,l_annual_grd_min_val
1317 ,l_annual_grd_max_val
1318 ,l_annual_grd_mid_point)
1319 ,get_grd_comparatio
1320 (l_fte_annual_salary
1321 ,l_annual_grd_mid_point)
1322 ,perinfo_rec.emp_category
1323 ,perinfo_rec.change_reason
1324 ,perinfo_rec.normal_hours
1325 ,perinfo_rec.email_address
1326 ,perinfo_rec.base_salary_frequency
1327 ,perinfo_rec.custom_segment1
1328 ,perinfo_rec.custom_segment2
1329 ,perinfo_rec.custom_segment3
1330 ,perinfo_rec.custom_segment4
1331 ,perinfo_rec.custom_segment5
1332 ,perinfo_rec.custom_segment6
1333 ,perinfo_rec.custom_segment7
1334 ,perinfo_rec.custom_segment8
1335 ,perinfo_rec.custom_segment9
1336 ,perinfo_rec.custom_segment10
1337 ,perinfo_rec.custom_segment11
1338 ,perinfo_rec.custom_segment12
1339 ,perinfo_rec.custom_segment13
1340 ,perinfo_rec.custom_segment14
1341 ,perinfo_rec.custom_segment15
1342 ,perinfo_rec.custom_segment16
1343 ,perinfo_rec.custom_segment17
1344 ,perinfo_rec.custom_segment18
1345 ,perinfo_rec.custom_segment19
1346 ,perinfo_rec.custom_segment20
1347 ,perinfo_rec.ass_attribute_category
1348 ,perinfo_rec.ass_attribute1
1349 ,perinfo_rec.ass_attribute2
1350 ,perinfo_rec.ass_attribute3
1351 ,perinfo_rec.ass_attribute4
1352 ,perinfo_rec.ass_attribute5
1353 ,perinfo_rec.ass_attribute6
1354 ,perinfo_rec.ass_attribute7
1355 ,perinfo_rec.ass_attribute8
1356 ,perinfo_rec.ass_attribute9
1357 ,perinfo_rec.ass_attribute10
1358 ,perinfo_rec.ass_attribute11
1359 ,perinfo_rec.ass_attribute12
1360 ,perinfo_rec.ass_attribute13
1361 ,perinfo_rec.ass_attribute14
1362 ,perinfo_rec.ass_attribute15
1363 ,perinfo_rec.ass_attribute16
1364 ,perinfo_rec.ass_attribute17
1365 ,perinfo_rec.ass_attribute18
1366 ,perinfo_rec.ass_attribute19
1367 ,perinfo_rec.ass_attribute20
1368 ,perinfo_rec.ass_attribute21
1369 ,perinfo_rec.ass_attribute22
1370 ,perinfo_rec.ass_attribute23
1371 ,perinfo_rec.ass_attribute24
1372 ,perinfo_rec.ass_attribute25
1373 ,perinfo_rec.ass_attribute26
1374 ,perinfo_rec.ass_attribute27
1375 ,perinfo_rec.ass_attribute28
1376 ,perinfo_rec.ass_attribute29
1377 ,perinfo_rec.ass_attribute30
1378 ,perinfo_rec.people_group_name
1379 ,perinfo_rec.people_group_segment1
1380 ,perinfo_rec.people_group_segment2
1381 ,perinfo_rec.people_group_segment3
1382 ,perinfo_rec.people_group_segment4
1383 ,perinfo_rec.people_group_segment5
1384 ,perinfo_rec.people_group_segment6
1385 ,perinfo_rec.people_group_segment7
1386 ,perinfo_rec.people_group_segment8
1387 ,perinfo_rec.people_group_segment9
1388 ,perinfo_rec.people_group_segment10
1389 ,perinfo_rec.people_group_segment11
1390 ,1 -- insert 1 as the ovn
1391 ,perinfo_rec.group_pl_id
1392 ,perinfo_rec.lf_evt_ocrd_dt
1393 ,perinfo_rec.fte_factor
1394 ,get_grd_quintile
1395 (l_fte_annual_salary
1396 ,l_annual_grd_min_val
1397 ,l_annual_grd_max_val)
1398 ,get_grd_decile
1399 (l_fte_annual_salary
1400 ,l_annual_grd_min_val
1401 ,l_annual_grd_max_val)
1402 ,get_grd_pct_in_range
1403 (l_fte_annual_salary
1404 ,l_annual_grd_min_val
1405 ,l_annual_grd_max_val)
1406 ,perinfo_rec.grade_rate_frequency
1407 ,perinfo_rec.base_salary_currency
1408 ,l_salary_1_year_ago.salary
1409 ,l_salary_1_year_ago.salary_frequency
1410 ,l_salary_1_year_ago.salary_currency
1411 ,decode(l_salary_1_year_ago.salary_frequency
1412 ,'HOURLY',l_salary_1_year_ago.salary_ann_fctr * l_salary_1_year_ago.fte_factor
1413 ,l_salary_1_year_ago.salary_ann_fctr)
1414 ,l_salary_2_year_ago.salary
1415 ,l_salary_2_year_ago.salary_frequency
1416 ,l_salary_2_year_ago.salary_currency
1417 ,decode(l_salary_2_year_ago.salary_frequency
1418 ,'HOURLY',l_salary_2_year_ago.salary_ann_fctr * l_salary_2_year_ago.fte_factor
1419 ,l_salary_2_year_ago.salary_ann_fctr)
1420 ,l_salary_3_year_ago.salary
1421 ,l_salary_3_year_ago.salary_frequency
1422 ,l_salary_3_year_ago.salary_currency
1423 ,decode(l_salary_3_year_ago.salary_frequency
1424 ,'HOURLY',l_salary_3_year_ago.salary_ann_fctr * l_salary_3_year_ago.fte_factor
1425 ,l_salary_3_year_ago.salary_ann_fctr)
1426 ,l_salary_4_year_ago.salary
1427 ,l_salary_4_year_ago.salary_frequency
1428 ,l_salary_4_year_ago.salary_currency
1429 ,decode(l_salary_4_year_ago.salary_frequency
1430 ,'HOURLY',l_salary_4_year_ago.salary_ann_fctr * l_salary_4_year_ago.fte_factor
1431 ,l_salary_4_year_ago.salary_ann_fctr)
1432 ,l_salary_5_year_ago.salary
1433 ,l_salary_5_year_ago.salary_frequency
1434 ,l_salary_5_year_ago.salary_currency
1435 ,decode(l_salary_5_year_ago.salary_frequency
1436 ,'HOURLY',l_salary_5_year_ago.salary_ann_fctr * l_salary_5_year_ago.fte_factor
1437 ,l_salary_5_year_ago.salary_ann_fctr)
1438 ,perinfo_rec.prev_sal
1439 ,perinfo_rec.prev_sal_frequency
1440 ,perinfo_rec.prev_sal_currency
1441 ,decode(perinfo_rec.prev_sal_frequency
1442 ,'HOURLY',perinfo_rec.prev_sal_ann_fctr * perinfo_rec.prev_fte_factor
1443 ,perinfo_rec.prev_sal_ann_fctr)
1444 ,perinfo_rec.prev_sal_chg_date
1445 ,perinfo_rec.prev_sal_chg_rsn
1446 ,l_survey_info.currency
1447 ,l_survey_info.frequency
1448 ,l_survey_info.annualization_factor
1449 ,l_survey_info.min_salary
1450 ,l_survey_info.pct25_salary
1451 ,l_survey_info.mid_salary
1452 ,l_survey_info.pct75_salary
1453 ,l_survey_info.max_salary
1454 , null
1455 , null
1456 , null
1457 ,l_survey_info.survey_id
1458 ,perinfo_rec.current_pay_proposal_id
1459 ,perinfo_rec.appraisal_id
1460 );
1461 --
1462 if g_debug then
1463 hr_utility.set_location(l_proc, 40);
1464 end if;
1465 --
1466 -- ************ audit changes ****************** --
1467 ben_cwb_audit_api.create_per_record
1468 (p_per_in_ler_id => p_group_per_in_ler_id);
1469 -- ********************************************* --
1470
1471 end if;
1472 --
1473 -- If not called from benmngle, run dynamic calculations.
1474 --
1475 if perinfo_rec.person_id <> -1 then
1476 ben_cwb_dyn_calc_pkg.run_dynamic_calculations(
1477 p_group_per_in_ler_id => p_group_per_in_ler_id
1478 ,p_group_pl_id => perinfo_rec.group_pl_id
1479 ,p_lf_evt_ocrd_dt => perinfo_rec.lf_evt_ocrd_dt);
1480 end if;
1481 -- Only one record, so exit.
1482 exit;
1483 --
1484 end loop; -- of for
1485 --
1486 -- Create a dummy record if called from benmngle and the person does
1487 -- not exist as of the freeze date (effective_date).
1488 --
1489 if p_called_from_benmngle and not(l_rec_modified) and
1490 l_cpi_effective_date is null then
1491 --
1492 if g_debug then
1493 hr_utility.set_location(l_proc, 50);
1494 end if;
1495 --
1496 if l_assignment_id is not null then
1497 insert into ben_cwb_person_info
1498 (group_per_in_ler_id
1499 ,person_id
1500 ,assignment_id
1501 ,effective_date
1502 ,full_name
1503 ,object_version_number
1504 ,group_pl_id
1505 ,lf_evt_ocrd_dt)
1506 values
1507 (p_group_per_in_ler_id
1508 ,-1
1509 ,l_assignment_id
1510 ,l_effective_date
1511 ,'Person Data does not exist as of freeze date for assignment: '||
1512 l_assignment_id ||'. Request your administrator to refresh person.'
1513 ,1
1514 ,l_group_pl_id
1515 ,l_lf_evt_ocrd_dt);
1516 -- ************ audit changes ****************** --
1517 ben_cwb_audit_api.create_per_record
1518 (p_per_in_ler_id => p_group_per_in_ler_id);
1519 -- ********************************************* --
1520 if g_debug then
1521 hr_utility.set_location(l_proc, 60);
1522 end if;
1523 --
1524 end if;
1525 --
1526 end if;
1527 --
1528 if g_debug then
1529 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1530 end if;
1531 --
1532 end; -- of refresh_person_info
1533 --
1534 -- --------------------------------------------------------------------------
1535 -- |--------------------< refresh_person_info_group_pl >---------------------|
1536 -- --------------------------------------------------------------------------
1537 procedure refresh_person_info_group_pl(p_group_pl_id in number
1538 ,p_lf_evt_ocrd_dt in date
1539 ,p_effective_date in date) is
1540
1541 cursor csr_pil is
1542 select pil.per_in_ler_id
1543 from ben_per_in_ler pil
1544 where pil.group_pl_id = p_group_pl_id
1545 and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
1546 and pil.assignment_id is not null
1547 and pil.per_in_ler_stat_cd in ('STRTD','PROCD');
1548 --
1549 l_count number := 0;
1550 l_proc varchar2(72) := g_package||'refresh_person_info_group_pl';
1551 --
1552 begin
1553 --
1554 if g_debug then
1555 hr_utility.set_location('Entering:'|| l_proc, 10);
1556 end if;
1557 --
1558 -- for each record in csr_pil
1559 for pil in csr_pil loop
1560 --
1561 if g_debug then
1562 hr_utility.set_location(l_proc, 20);
1563 end if;
1564 --
1565 refresh_person_info(p_group_per_in_ler_id => pil.per_in_ler_id
1566 ,p_effective_date => p_effective_date);
1567 --
1568 l_count := l_count + 1;
1569 if l_count = 10 then
1570 commit;
1571 l_count := 0;
1572 end if;
1573 --
1574 end loop;
1575 --
1576 if g_debug then
1577 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1578 end if;
1579 --
1580 end;
1581 --
1582 -- --------------------------------------------------------------------------
1583 -- |---------------------------< get_grd_quartile >--------------------------|
1584 -- --------------------------------------------------------------------------
1585 -- Description
1586 -- This function is referred by refresh_person_info
1587 function get_grd_quartile(p_salary in number
1588 ,p_min in number
1589 ,p_max in number
1590 ,p_mid in number)
1591 return varchar2 is
1592 --
1593 l_return_value varchar2(30) := null;
1594 --
1595 l_proc varchar2(72) := g_package||'get_grd_quartile';
1596 --
1597 begin
1598 --
1599 if g_debug then
1600 hr_utility.set_location('Entering:'|| l_proc, 10);
1601 end if;
1602 --
1603 if p_salary is not null then
1604 if p_salary < p_min then
1605 l_return_value := 'BLW';
1606 elsif p_salary > p_max then
1607 l_return_value := 'ABV';
1608 elsif p_salary < (p_mid + p_min)/2 then
1609 l_return_value := '1';
1610 elsif p_salary < p_mid then
1611 l_return_value := '2';
1612 elsif p_salary >= (p_mid+p_max)/2 then
1613 l_return_value := '4';
1614 elsif p_salary >= p_mid then
1615 l_return_value := '3';
1616 end if;
1617 end if;
1618 --
1619 if g_debug then
1620 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1621 end if;
1622 --
1623 return l_return_value;
1624 --
1625 end; -- end of get_grd_quartile
1626 --
1627 --
1628 -- --------------------------------------------------------------------------
1629 -- |---------------------------< get_grd_quintile >--------------------------|
1630 -- --------------------------------------------------------------------------
1631 -- Description
1632 -- This function is referred by refresh_person_info
1633 function get_grd_quintile(p_salary in number
1634 ,p_min in number
1635 ,p_max in number)
1636 return varchar2 is
1637 --
1638 l_return_value varchar2(30) := null;
1639 --
1640 l_proc varchar2(72) := g_package||'get_grd_quintile';
1641 l_step NUMBER := (p_max-p_min)/5;
1642 --
1643 begin
1644 --
1645 if g_debug then
1646 hr_utility.set_location('Entering:'|| l_proc, 10);
1647 end if;
1648 --
1649 if p_salary is not null and l_step is not null then
1650 if p_salary < p_min then
1651 l_return_value := 'BLW';
1652 elsif p_salary > p_max then
1653 l_return_value := 'ABV';
1654 elsif p_salary < (p_min + l_step) then
1655 l_return_value := '1';
1656 elsif p_salary < (p_min + l_step*2) then
1657 l_return_value := '2';
1658 elsif p_salary < (p_min + l_step*3) then
1659 l_return_value := '3';
1660 elsif p_salary < (p_min + l_step*4) then
1661 l_return_value := '4';
1662 elsif p_salary <= p_max THEN
1663 l_return_value := '5';
1664 end if;
1665 end if;
1666 --
1667 if g_debug then
1668 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1669 end if;
1670 --
1671 return l_return_value;
1672 --
1673 end; -- end of get_grd_quintile
1674 --
1675 --
1676 -- --------------------------------------------------------------------------
1677 -- |----------------------------< get_grd_decile >---------------------------|
1678 -- --------------------------------------------------------------------------
1679 -- Description
1680 -- This function is referred by refresh_person_info
1681 function get_grd_decile(p_salary in number
1682 ,p_min in number
1683 ,p_max in number)
1684 return varchar2 is
1685 --
1686 l_return_value varchar2(30) := null;
1687 --
1688 l_proc varchar2(72) := g_package||'get_grd_decile';
1689 l_step NUMBER := (p_max-p_min)/10;
1690 --
1691 begin
1692 --
1693 if g_debug then
1694 hr_utility.set_location('Entering:'|| l_proc, 10);
1695 end if;
1696 --
1697 if p_salary is not null and l_step is not null then
1698 if p_salary < p_min then
1699 l_return_value := 'BLW';
1700 elsif p_salary > p_max then
1701 l_return_value := 'ABV';
1702 elsif p_salary < (p_min + l_step) then
1703 l_return_value := '1';
1704 elsif p_salary < (p_min + l_step*2) then
1705 l_return_value := '2';
1706 elsif p_salary < (p_min + l_step*3) then
1707 l_return_value := '3';
1708 elsif p_salary < (p_min + l_step*4) then
1709 l_return_value := '4';
1710 elsif p_salary < (p_min + l_step*5) then
1711 l_return_value := '5';
1712 elsif p_salary < (p_min + l_step*6) then
1713 l_return_value := '6';
1714 elsif p_salary < (p_min + l_step*7) then
1715 l_return_value := '7';
1716 elsif p_salary < (p_min + l_step*8) then
1717 l_return_value := '8';
1718 elsif p_salary < (p_min + l_step*9) then
1719 l_return_value := '9';
1720 elsif p_salary <= p_max THEN
1721 l_return_value := '10';
1722 end if;
1723 end if;
1724 --
1725 if g_debug then
1726 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1727 end if;
1728 --
1729 return l_return_value;
1730 --
1731 end; -- end of get_grd_decile
1732 --
1733 --
1734 --
1735 -- --------------------------------------------------------------------------
1736 -- |------------------------< get_grd_pct_in_range >------------------------|
1737 -- --------------------------------------------------------------------------
1738 -- Description
1739 -- This function is referred by refresh_person_info
1740 function get_grd_pct_in_range(p_salary in number
1741 ,p_min in number
1742 ,p_max in number)
1743 return number is
1744 --
1745 l_return_value varchar2(30) := null;
1746 --
1747 l_proc varchar2(72) := g_package||'get_grd_pct_in_range';
1748 l_range NUMBER := (p_max-p_min);
1749 l_min_to_sal NUMBER := (p_salary-p_min);
1750 l_return_val NUMBER := NULL;
1751 --
1752 begin
1753 --
1754 if g_debug then
1755 hr_utility.set_location('Entering:'|| l_proc, 10);
1756 end if;
1757 --
1758 if l_range = 0 then
1759 return l_return_val;
1760 end if;
1761 --
1762 l_return_val := round((l_min_to_sal / l_range * 100),1);
1763 --
1764 if g_debug then
1765 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1766 end if;
1767 --
1768 return l_return_val;
1769 --
1770 end; -- end of get_grd_pct_in_range
1771 --
1772 -- --------------------------------------------------------------------------
1773 -- |-------------------------< get_grd_comparatio >-------------------------|
1774 -- --------------------------------------------------------------------------
1775 -- Description
1776 -- This function is referred by refresh_person_info
1777 function get_grd_comparatio(p_salary in number
1778 ,p_mid in number)
1779 return number is
1780 --
1781 l_proc varchar2(72) := g_package||'get_grd_comparatio';
1782 --
1783 begin
1784 --
1785 if g_debug then
1786 hr_utility.set_location('Entering:'|| l_proc, 10);
1787 end if;
1788 --
1789 if p_salary is null or p_mid is null or p_mid <= 0 then
1790 return null;
1791 else
1792 return round(p_salary/p_mid*100,3);
1793 end if;
1794 end; -- end of get_grd_comparatio
1795 --
1796 -- --------------------------------------------------------------------------
1797 -- |--------------------------< refresh_from_master >-----------------------|
1798 -- --------------------------------------------------------------------------
1799 -- Description
1800 -- This procedure is used only by the admin page to refresh the person
1801 -- info. This procedure checks if th effective_date column in ben_cwb_pl_dsgn
1802 -- is null. If it is, the p_effective_date will be passed to
1803 -- refresh_person_info. Otherwise null will be passed to refresh_person_info
1804 -- so that it will use effective_date column in pl_dsgn.
1805 procedure refresh_from_master(p_group_per_in_ler_id in number
1806 ,p_effective_date in date) is
1807 --
1808 l_effective_date date;
1809 --
1810 l_proc varchar2(72) := g_package||'refresh_from_master';
1811 --
1812 begin
1813 --
1814 if g_debug then
1815 hr_utility.set_location('Entering:'|| l_proc, 10);
1816 end if;
1817 --
1818 select pl.effective_date into l_effective_date
1819 from ben_cwb_pl_dsgn pl
1820 ,ben_per_in_ler pil
1821 where pil.per_in_ler_id = p_group_per_in_ler_id
1822 and pl.pl_id = pil.group_pl_id
1823 and pl.lf_evt_ocrd_dt = pil.lf_evt_ocrd_dt
1824 and pl.group_oipl_id = -1;
1825
1826 if l_effective_date is null then
1827 -- data_freeze_date is null. So refresh shoule be based on
1828 -- user provided effective_date
1829 l_effective_date := p_effective_date;
1830 else
1831 -- data_freeze_date is not null. So refresh should not use
1832 -- user provided effective_date. Pass null.
1833 l_effective_date := null;
1834 end if;
1835 --
1836 if g_debug then
1837 hr_utility.set_location(l_proc, 30);
1838 end if;
1839 --
1840 -- call refresh_person_info
1841 refresh_person_info(p_group_per_in_ler_id => p_group_per_in_ler_id
1842 ,p_effective_date => l_effective_date);
1843 --
1844 if g_debug then
1845 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1846 end if;
1847 --
1848 end;
1849 --
1850 --
1851 -- --------------------------------------------------------------------------
1852 -- |-------------------------< get_salary_currency >------------------------|
1853 -- --------------------------------------------------------------------------
1854 -- Description
1855 -- This function is referred by refresh_person_info
1856 function get_salary_currency(p_input_value_id in number
1857 ,p_effective_date in date)
1858 return varchar2 is
1859 --
1860 l_proc varchar2(72) := g_package||'get_salary_currency';
1861 l_currency varchar2(15);
1862 --
1863 cursor csr_currency
1864 is
1865 select pet.input_currency_code
1866 from pay_input_values_f piv,
1867 pay_element_types_f pet
1868 where piv.input_value_id = p_input_value_id
1869 and p_effective_date between piv.effective_start_date and piv.effective_end_date
1870 and piv.element_type_id = pet.element_type_id
1871 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
1872 --
1873 begin
1874 --
1875 if g_debug then
1876 hr_utility.set_location('Entering:'|| l_proc, 10);
1877 end if;
1878 --
1879 open csr_currency;
1880 fetch csr_currency into l_currency;
1881 close csr_currency;
1882 --
1883 if g_debug then
1884 hr_utility.set_location(' Leaving:'|| l_proc, 99);
1885 end if;
1886 --
1887 return l_currency;
1888 end;
1889 --
1890 --
1891 -- --------------------------------------------------------------------------
1892 -- |---------------------------< get_fte_factor >---------------------------|
1893 -- --------------------------------------------------------------------------
1894 FUNCTION get_fte_factor(p_assignment_id IN NUMBER
1895 ,p_effective_date IN DATE)
1896 return NUMBER IS
1897
1898 /*
1899 CURSOR csr_fte_fctr
1900 IS
1901 SELECT
1902 decode(g_fte_factor,
1903 'NHBGWH',(select normal_hours /decode(fnd_number.canonical_to_number(working_hours), 0, to_number(NULL)
1904 ,fnd_number.canonical_to_number(working_hours))
1905 from per_all_assignments_f asg,
1906 per_business_groups bg
1907 where asg.assignment_id = p_assignment_id
1908 and asg.business_group_id = bg.business_group_id
1909 and p_effective_date BETWEEN effective_start_date AND effective_end_date),
1910 'BFTE', (select value
1911 from per_assignment_budget_values_f
1912 where assignment_id = p_assignment_id
1913 and unit = 'FTE'
1914 and p_effective_date BETWEEN effective_start_date AND effective_end_date),
1915 'BPFT', (select value
1916 from per_assignment_budget_values_f
1917 where assignment_id = p_assignment_id
1918 and unit = 'PFT'
1919 and p_effective_date BETWEEN effective_start_date AND effective_end_date),
1920 'NC', 1, 1)
1921 FROM
1922 DUAL;
1923
1924 l_fte_factor NUMBER := null;
1925 */
1926
1927 BEGIN
1928 --
1929 /*
1930 OPEN csr_fte_fctr;
1931 FETCH csr_fte_fctr INTO l_fte_factor;
1932 CLOSE csr_fte_fctr;
1933 --
1934 -- fte_factor should always be between 0 and 1.
1935 -- if null, >1 or <0(improbable, still), return 1.
1936 --
1937 if l_fte_factor is null OR l_fte_factor > 1 OR l_fte_factor < 0 then
1938 l_fte_factor := 1;
1939 end if;
1940 --
1941 RETURN l_fte_factor;
1942 */
1943
1944 RETURN per_saladmin_utility.get_fte_factor(p_assignment_id
1945 ,p_effective_date);
1946 --
1947 END;
1948 --
1949 end BEN_CWB_PERSON_INFO_PKG;
1950 --