1 PACKAGE BODY PER_SALADMIN_UTILITY AS
2 /* $Header: pesalutl.pkb 120.25.12010000.2 2008/11/07 07:00:46 schowdhu ship $ */
3
4 g_package Varchar2(30) := 'per_saladmin_utility.';
5 g_debug boolean := hr_utility.debug_enabled;
6
7 function Check_GSP_Manual_Override (p_assignment_id in NUMBER, p_effective_date in DATE) RETURN VARCHAR2 is
8 Cursor Grade_Ladder Is
9 Select Nvl(Gsp_Allow_Override_Flag,'Y')
10 From Ben_Pgm_f Pgm,
11 Per_all_assignments_F paa
12 Where paa.Assignment_Id = p_assignment_id
13 and p_effective_date between paa.Effective_Start_Date and paa.Effective_End_Date
14 and paa.GRADE_LADDER_PGM_ID is Not NULL
15 and pgm.pgm_id = paa.Grade_Ladder_Pgm_Id
16 and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
17 and Pgm_typ_Cd = 'GSP'
18 and Pgm_stat_Cd = 'A'
19 and Update_Salary_Cd = 'SALARY_BASIS';
20
21 l_status Varchar2(1) := 'Y';
22 Begin
23 Open Grade_Ladder;
24 Fetch Grade_Ladder into l_Status;
25 Close Grade_Ladder;
26
27 RETURN l_Status;
28 End;
29
30 FUNCTION get_query_only
31 return VARCHAR2 IS
32 l_query_only_profile_value VARCHAR2(240) := fnd_profile.VALUE('PER_QUERY_ONLY_MODE');
33 BEGIN
34
35 RETURN l_query_only_profile_value;
36
37 END get_query_only;
38
39
40
41 function get_grd_min_pay(p_assignment_id in NUMBER
42 ,p_business_group_id in NUMBER
43 ,p_effective_date in date)
44 return number is
45 l_grd_min_val number := null;
46 l_conversion_rate number;
47 l_grd_annualization_factor number;
48 l_pay_basis varchar2(20);
49 l_rate_basis varchar2(20);
50 l_element_curr_code varchar2(20);
51 l_grade_curr_code varchar2(20);
52
53 cursor grd_min_rate is
54 select ppb.grade_annualization_factor
55 ,ppb.pay_basis
56 ,ppb.rate_basis
57 ,pet.input_currency_code as element_currency_code
58 ,PER_SALADMIN_UTILITY.get_grade_currency(paa.grade_id,ppb.rate_id,p_effective_date,paa.business_group_id) as grade_rate_currency_code
59 ,ben_cwb_person_info_pkg.get_grd_min_val(paa.grade_id,ppb.rate_id,p_effective_date)
60 from per_all_assignments_f paa
61 ,per_pay_bases ppb
62 ,pay_input_values_f piv
63 ,pay_element_types_f pet
64 where paa.assignment_id = p_assignment_id
65 and paa.pay_basis_id = ppb.pay_basis_id
66 and ppb.input_value_id = piv.input_value_id
67 and piv.element_type_id = pet.element_type_id
68 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
69
70 BEGIN
71
72 open grd_min_rate;
73 fetch grd_min_rate into l_grd_annualization_factor,l_pay_basis,l_rate_basis,l_element_curr_code,l_grade_curr_code,l_grd_min_val;
74 close grd_min_rate;
75
76 l_conversion_rate := nvl(PER_SALADMIN_UTILITY.get_currency_rate(l_grade_curr_code,l_element_curr_code,p_effective_date,p_business_group_id),1);
77
78 if(l_element_curr_code = l_grade_curr_code) then
79 if l_grd_annualization_factor is null then
80 l_grd_min_val := null;
81 elsif (l_grd_annualization_factor = 0 OR (l_pay_basis = 'HOURLY' AND l_rate_basis = 'HOURLY' )) then
82 l_grd_min_val := l_grd_min_val;
83 elsif (l_grd_annualization_factor <> 0) then
84 l_grd_min_val := l_grd_min_val * l_grd_annualization_factor;
85 end if;
86 else
87 if l_grd_annualization_factor is null then
88 l_grd_min_val := null;
89 elsif (l_grd_annualization_factor = 0 OR (l_pay_basis = 'HOURLY' AND l_rate_basis = 'HOURLY' )) then
90 l_grd_min_val := l_grd_min_val * l_conversion_rate;
91 elsif (l_grd_annualization_factor <> 0) then
92 l_grd_min_val := l_grd_min_val * l_grd_annualization_factor * l_conversion_rate;
93 end if;
94 end if;
95 return l_grd_min_val;
96 END;
97
98
99 function get_grd_max_pay(p_assignment_id in NUMBER
100 ,p_business_group_id in NUMBER
101 ,p_effective_date in date)
102 return number is
103 l_grd_max_val number := null;
104 l_conversion_rate number;
105 l_grd_annualization_factor number;
106 l_pay_basis varchar2(20);
107 l_rate_basis varchar2(20);
108 l_element_curr_code varchar2(20);
109 l_grade_curr_code varchar2(20);
110
111 cursor grd_max_rate is
112 select ppb.grade_annualization_factor
113 ,ppb.pay_basis
114 ,ppb.rate_basis
115 ,pet.input_currency_code as element_currency_code
116 ,PER_SALADMIN_UTILITY.get_grade_currency(paa.grade_id,ppb.rate_id,p_effective_date,paa.business_group_id) as grade_rate_currency_code
117 ,ben_cwb_person_info_pkg.get_grd_max_val(paa.grade_id,ppb.rate_id,p_effective_date)
118 from per_all_assignments_f paa
119 ,per_pay_bases ppb
120 ,pay_input_values_f piv
121 ,pay_element_types_f pet
122 where paa.assignment_id = p_assignment_id
123 and paa.pay_basis_id = ppb.pay_basis_id
124 and ppb.input_value_id = piv.input_value_id
125 and piv.element_type_id = pet.element_type_id
126 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
127
128 BEGIN
129
130 open grd_max_rate;
131 fetch grd_max_rate into l_grd_annualization_factor,l_pay_basis,l_rate_basis,l_element_curr_code,l_grade_curr_code,l_grd_max_val;
132 close grd_max_rate;
133
134 l_conversion_rate := nvl(PER_SALADMIN_UTILITY.get_currency_rate(l_grade_curr_code,l_element_curr_code,p_effective_date,p_business_group_id),1);
135
136 if(l_element_curr_code = l_grade_curr_code) then
137 if l_grd_annualization_factor is null then
138 l_grd_max_val := null;
139 elsif (l_grd_annualization_factor = 0 OR (l_pay_basis = 'HOURLY' AND l_rate_basis = 'HOURLY' )) then
140 l_grd_max_val := l_grd_max_val;
141 elsif (l_grd_annualization_factor <> 0) then
142 l_grd_max_val := l_grd_max_val * l_grd_annualization_factor;
143 end if;
144 else
145 if l_grd_annualization_factor is null then
146 l_grd_max_val := null;
147 elsif (l_grd_annualization_factor = 0 OR (l_pay_basis = 'HOURLY' AND l_rate_basis = 'HOURLY' )) then
148 l_grd_max_val := l_grd_max_val * l_conversion_rate;
149 elsif (l_grd_annualization_factor <> 0) then
150 l_grd_max_val := l_grd_max_val * l_grd_annualization_factor * l_conversion_rate;
151 end if;
152 end if;
153
154 return l_grd_max_val;
155 END;
156
157
158
159 function derive_next_sal_perf_date
160 (p_change_date in per_pay_proposals.change_date%TYPE
161 ,p_period in per_all_assignments_f.sal_review_period%TYPE
162 ,p_frequency in per_all_assignments_f.sal_review_period_frequency%TYPE
163 )
164 Return Date is
165 l_derived_date date;
166 l_num_months number(15) := 0;
167 l_num_days number(15) := 0;
168 --
169 begin
170 if (p_frequency = 'Y')then
171 l_num_months := 12 * p_period;
172 elsif
173 (p_frequency = 'M') then
174 l_num_months := p_period;
175 elsif (p_frequency = 'W' ) then
176 l_num_days := 7 * p_period;
177 elsif
178 (p_frequency = 'D') then
179 l_num_days := p_period;
180 else
181 hr_utility.set_message(801,'HR_51258_PYP_INVAL_FREQ_PERIOD');
182 hr_utility.raise_error;
183 end if;
184 --
185 -- Now return the derived date
186 --
187 if (l_num_months <> 0) then
188 l_derived_date := add_months(p_change_date,l_num_months);
189 elsif (l_num_days <> 0 ) then
190 l_derived_date := p_change_date + l_num_days;
191 end if;
192
193 return l_derived_date;
194 --
195 end derive_next_sal_perf_date;
196
197 FUNCTION get_next_sal_review_date(p_assignment_id IN NUMBER,p_change_date IN DATE,p_business_group_id IN NUMBER)
198 RETURN DATE
199 IS
200 --
201 l_sal_review_period number(15);
202 l_sal_review_period_frequency varchar2(30);
203 l_next_sal_review_date Date;
204 --
205 cursor csr_sal_review_details is
206 select sal_review_period,
207 sal_review_period_frequency
208 from per_all_assignments_f
209 where assignment_id = p_assignment_id
210 and business_group_id + 0 = p_business_group_id
211 and p_change_date between effective_start_date
212 and nvl(effective_end_date, hr_general.end_of_time);
213 --
214 BEGIN
215 OPEN csr_sal_review_details;
216 FETCH csr_sal_review_details into l_sal_review_period,l_sal_review_period_frequency;
217
218 If csr_sal_review_details%found then
219 If (l_sal_review_period is not null) then
220 l_next_sal_review_date :=
221 derive_next_sal_perf_date
222 (p_change_date => p_change_date
223 ,p_period => l_sal_review_period
224 ,p_frequency => l_sal_review_period_frequency
225 );
226 end If;
227 end If;
228 close csr_sal_review_details;
229
230 return l_next_sal_review_date ;
231 END get_next_sal_review_date;
232
233
234
235 FUNCTION get_uom(p_pay_proposal_id IN NUMBER)
236 RETURN VARCHAR2
237 IS
238 l_uom pay_input_values_f.uom%TYPE;
239 CURSOR get_uom_cur
240 IS
241 SELECT piv.uom
242 FROM pay_element_types_f pet,
243 per_all_assignments_f paaf,
244 pay_input_values_f piv,
245 per_pay_bases ppb,
246 per_pay_proposals ppp
247 WHERE ppp.pay_proposal_id = p_pay_proposal_id
248 and paaf.assignment_id = ppp.assignment_id
249 and ppp.change_date BETWEEN paaf.effective_start_date
250 and paaf.effective_end_date
251 and ppb.pay_basis_id = paaf.pay_basis_id
252 and ppb.input_value_id = piv.input_value_id
253 and ppp.change_date BETWEEN piv.effective_start_date
254 and piv.effective_end_date
255 and piv.element_type_id = pet.element_type_id
256 and ppp.change_date BETWEEN pet.effective_start_date
257 and pet.effective_end_date;
258 BEGIN
259 OPEN get_uom_cur;
260 FETCH get_uom_cur INTO l_uom;
261 CLOSE get_uom_cur;
262 return l_uom;
263 END get_uom;
264
265 Function get_previous_proposal_dt(p_assignment_id IN NUMBER,p_change_date IN DATE)
266 return date
267 is
268 l_prev_change_date date;
269 CURSOR c_prev_pay_proposals is
270 select max(change_date)
271 from per_pay_proposals pro
272 where pro.assignment_id = p_assignment_id
273 and pro.change_date < p_change_date ;
274 begin
275 OPEN c_prev_pay_proposals;
276 FETCH c_prev_pay_proposals into l_prev_change_date;
277 IF c_prev_pay_proposals%FOUND then
278 CLOSE c_prev_pay_proposals;
279 return(l_prev_change_date);
280 end if;
281 CLOSE c_prev_pay_proposals;
282 return (null);
283 end get_previous_proposal_dt;
284
285
286
287
288 function get_next_proposal_with_comp(p_assignment_id in number,
289 p_session_date in date)
290 return date
291 is
292 cursor c1(p_assignment_id in number,
293 p_session_date in date) is
294 select change_date
295 from per_pay_proposals
296 where assignment_id = p_assignment_id
297 and multiple_components = 'Y'
298 and change_date =
299 (select min(change_date)
300 from per_pay_proposals
301 where assignment_id = p_assignment_id
302 and change_date > p_session_date);
303 --
304 l_change_date per_pay_proposals.change_date%TYPE;
305 begin
306 --
307 open c1(p_assignment_id, p_session_date);
308 fetch c1 into l_change_date;
309 close c1;
310 --
311 return l_change_date;
312 --
313 end get_next_proposal_with_comp;
314
315
316
317 procedure adjust_pay_proposals
318 (
319 p_assignment_id number
320 ) is
321
322 l_next_change_date per_pay_proposals.change_date%TYPE;
323 l_approved per_pay_proposals.approved%TYPE;
324 l_element_entry_end_date pay_element_entries_f.effective_end_date%TYPE;
325
326 Cursor csr_pay_proposals
327 is
328 select pay_proposal_id, change_date,date_to
329 from per_pay_proposals
330 where assignment_id = p_assignment_id
331 and approved = 'Y';
332
333 Cursor csr_next_change_date(p_change_date in date)
334 IS
335 select min(change_date)
336 from per_pay_proposals
337 where change_date > p_change_date
338 and assignment_id = p_assignment_id;
339
340 Cursor csr_approved(p_change_date in date)
341 is
342 select approved
343 from per_pay_proposals
344 where change_date = p_change_date
345 and assignment_id = p_assignment_id;
346
347 Cursor element_entry_end_date(p_pay_proposal_id in number,p_change_date in date)
348 is
349 select effective_end_date
350 From pay_element_entries_f
351 where assignment_id = p_assignment_id
352 and creator_type = 'SP'
353 and creator_id = p_pay_proposal_id
354 and effective_start_date =p_change_date;
355
356 begin
357
358 if g_debug then
359 hr_utility.trace('In per_saladmin_utility.adjust_pay_proposals');
360 end if;
361 for i in csr_pay_proposals loop
362 ---vkodedal fix -5941519
363 l_element_entry_end_date:=null;
364 if g_debug then
365 hr_utility.set_location('Proposal Id'||i.pay_proposal_id, 66);
366 hr_utility.set_location('Change Date'||i.change_date, 66);
367 hr_utility.set_location('Date to'||i.date_to, 67);
368 end if;
369
370 OPEN element_entry_end_date(i.pay_proposal_id,i.change_date);
371 FETCH element_entry_end_date into l_element_entry_end_date;
372 CLOSE element_entry_end_date;
373
374 if g_debug then
375 hr_utility.set_location('End Date'||l_element_entry_end_date, 70);
376 end if;
377
378 if l_element_entry_end_date is not null and l_element_entry_end_date <> i.date_to then
379 if g_debug then
380 hr_utility.set_location('End Date and date_to are not matching', 70);
381 end if;
382
383 OPEN csr_next_change_date(i.change_date);
384 FETCH csr_next_change_date into l_next_change_date;
385 CLOSE csr_next_change_date;
386
387 if l_next_change_date is not null then
388
389 OPEN csr_approved(l_next_change_date);
390 FETCH csr_approved into l_approved;
391 CLOSE csr_approved;
392
393 if l_approved = 'N' THEN
394
395 if l_element_entry_end_date > l_next_change_date-1 THEN
396
397 l_element_entry_end_date :=l_next_change_date-1;
398
399 END IF;
400
401 END IF;
402
403 end if;
404
405 if g_debug then
406 hr_utility.set_location('About to update', 70);
407 end if;
408
409 update per_pay_proposals
410 set date_to =l_element_entry_end_date
411 where pay_proposal_id = i.pay_proposal_id;
412
413 if g_debug then
414 hr_utility.set_location('Updated successfully', 70);
415 end if;
416
417
418 END IF;
419
420
421 end loop;
422
423 if g_debug then
424 hr_utility.trace('OUT per_saladmin_utility.adjust_pay_proposals');
425 end if;
426
427 end adjust_pay_proposals;
428
429 function get_last_payroll_dt(p_assignment_id NUMBER) RETURN date IS
430 l_asg_start_date date;
431 l_last_payroll_dt date;
432 cursor c1(p_assignment_id number) is
433 select min(effective_start_date)
434 from per_all_assignments_f
435 where assignment_id = p_assignment_id;
436 begin
437 open c1(p_assignment_id);
438 fetch c1 into l_asg_start_date;
439 close c1;
440 --
441 l_last_payroll_dt := pqh_bdgt_actual_cmmtmnt_pkg.get_last_payroll_dt (
442 p_assignment_id,
443 l_asg_start_date,
444 hr_general.end_of_time );
445
446 if l_last_payroll_dt is not null
447 then
448 return l_last_payroll_dt;
449 end if;
450
451 return null;
452 end;
453
454 Function get_previous_salary(p_assignment_id IN NUMBER,p_proposal_id in number)
455 return number
456 is
457 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
458 CURSOR previous_pay is
459 select pro.proposed_salary_n
460 from per_pay_proposals pro
461 where pro.assignment_id = p_assignment_id
462 and pro.change_date =(select max(pro2.change_date)
463 from per_pay_proposals pro2
464 where pro2.assignment_id = p_assignment_id
465 and pro2.change_date < (select change_date from per_pay_proposals
466 where pay_proposal_id =p_proposal_id));
467 begin
468 OPEN previous_pay;
469 FETCH previous_pay into l_previous_salary;
470 IF previous_pay%NOTFOUND then
471 l_previous_salary := -1;
472 end if;
473 CLOSE previous_pay;
474 return l_previous_salary;
475 end get_previous_salary;
476
477
478
479
480 FUNCTION get_proposed_salary (p_assignment_id IN NUMBER,p_effective_date IN DATE )
481 RETURN NUMBER
482 is
483 l_salary per_pay_proposals.proposed_salary_n%TYPE;
484
485 cursor csr_sal is
486 select proposed_salary_n
487 from per_pay_proposals
488 where assignment_id = p_assignment_id
489 and p_effective_date between nvl(change_date,hr_general.start_of_time) and nvl(date_to,hr_general.end_of_time);
490 begin
491 open csr_sal;
492 fetch csr_sal into l_salary;
493 close csr_sal;
494 RETURN l_salary;
495 END get_proposed_salary;
496
497
498 FUNCTION GET_ANNUALIZATION_FACTOR(p_assignment_id NUMBER,p_effective_date DATE)
499 RETURN number
500 IS
501 l_dummy VARCHAR2(30);
502 l_pay_basis VARCHAR2(30);
503 l_pay_basis_id NUMBER;
504 l_pay_annualization_factor NUMBER;
505
506 CURSOR c_pay_basis is
507 SELECT PAF.PAY_BASIS_ID
508 FROM PER_ALL_ASSIGNMENTS_F PAF
509 WHERE PAF.ASSIGNMENT_ID=p_assignment_id
510 AND p_effective_date BETWEEN
511 PAF.EFFECTIVE_START_DATE AND
512 PAF.EFFECTIVE_END_DATE;
513
514 CURSOR Currency IS
515 SELECT HR_GENERAL.DECODE_LOOKUP('PAY_BASIS',PPB.PAY_BASIS)
516 ,PPB.PAY_ANNUALIZATION_FACTOR
517 FROM PAY_ELEMENT_TYPES_F PET
518 ,PAY_INPUT_VALUES_F PIV
519 ,PER_PAY_BASES PPB
520 WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
521 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
522 AND p_effective_date BETWEEN
523 PIV.EFFECTIVE_START_DATE AND
524 PIV.EFFECTIVE_END_DATE
525 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
526 AND p_effective_date BETWEEN
527 PET.EFFECTIVE_START_DATE AND
528 PET.EFFECTIVE_END_DATE;
529
530 CURSOR payroll is
531 select tpt.number_per_fiscal_year
532 from pay_all_payrolls_f prl
533 , per_all_assignments_f paf
534 , per_time_period_types tpt
535 where paf.assignment_id=p_assignment_id
536 and p_effective_date between paf.effective_start_date
537 and paf.effective_end_date
538 and paf.payroll_id=prl.payroll_id
539 and p_effective_date between prl.effective_start_date
540 and prl.effective_end_date
541 and prl.period_type = tpt.period_type(+);
542
543
544 BEGIN
545
546 open c_pay_basis;
547 fetch c_pay_basis into l_pay_basis_id;
548 close c_pay_basis;
549
550 open Currency;
551 fetch Currency
552 into l_pay_basis
553 ,l_pay_annualization_factor;
554 close Currency;
555
556 if(l_pay_basis ='Period Salary' and l_pay_annualization_factor is null) then
557 open payroll;
558 fetch payroll
559 into l_pay_annualization_factor;
560 close payroll;
561 end if;
562
563 return l_pay_annualization_factor;
564 END GET_ANNUALIZATION_FACTOR;
565
566
567
568
569 FUNCTION get_grade (p_assignment_id IN NUMBER,p_effective_date IN DATE )
570 RETURN VARCHAR2
571 is
572 l_grade per_grades_vl.name%type :=null ;
573
574 cursor csr_grade is
575 select HR_GENERAL.DECODE_GRADE(paa.grade_id) as grade
576 from per_all_assignments_f paa
577 where paa.assignment_id = p_assignment_id
578 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
579 begin
580 open csr_grade;
581 fetch csr_grade into l_grade;
582 close csr_grade;
583 RETURN l_grade;
584 END get_grade;
585
586
587 FUNCTION get_grade_currency (p_grade_id in number,p_rate_id in number,p_effective_date in date,p_business_group_id in number )
588 RETURN VARCHAR2
589 is
590 l_grade_currency pay_grade_rules_f.currency_code%type :=null ;
591
592 cursor csr_grade_currency is
593 select grdrule.currency_code
594 from pay_grade_rules_f grdrule
595 where grdrule.rate_id = p_rate_id
596 and grdrule.grade_or_spinal_point_id = p_grade_id
597 and p_effective_date between grdrule.effective_start_date
598 and grdrule.effective_end_date;
599 begin
600 open csr_grade_currency;
601 fetch csr_grade_currency into l_grade_currency;
602 close csr_grade_currency;
603
604 if(l_grade_currency is null) then
605 l_grade_currency := hr_general.DEFAULT_CURRENCY_CODE(p_business_group_id);
606 end if;
607
608 RETURN l_grade_currency;
609 END get_grade_currency;
610
611
612
613
614 FUNCTION get_basis_currency_code (p_assignment_id IN NUMBER,p_effective_date IN DATE )
615 RETURN VARCHAR2
616 is
617 l_currency varchar2(100):=null ;
618 cursor csr_currency is
619 select pet.input_currency_code as currency_code
620 from per_pay_bases ppb
621 ,pay_input_values_f piv
622 ,pay_element_types_f pet
623 ,per_all_assignments_f paa
624 where paa.pay_basis_id = ppb.pay_basis_id
625 and ppb.input_value_id = piv.input_value_id
626 and piv.element_type_id = pet.element_type_id
627 and paa.assignment_id = p_assignment_id
628 and p_effective_date between nvl(paa.effective_start_date,hr_general.start_of_time) and nvl(paa.effective_end_date,hr_general.end_of_time)
629 and p_effective_date between nvl(pet.effective_start_date,hr_general.start_of_time) and nvl(pet.effective_end_date,hr_general.end_of_time)
630 and p_effective_date between nvl(piv.effective_start_date,hr_general.start_of_time) and nvl(piv.effective_end_date,hr_general.end_of_time);
631 begin
632 open csr_currency;
633 fetch csr_currency into l_currency;
634 close csr_currency;
635 RETURN l_currency;
636 END get_basis_currency_code;
637
638
639
640 FUNCTION get_pay_basis_frequency (p_assignment_id IN NUMBER,p_lookup_type IN varchar2,p_lookup_code IN varchar2,p_effective_date IN date )
641 RETURN VARCHAR2
642 is
643 l_pay_basis varchar2(100):=null;
644 cursor csr_lookup is
645 select description
646 from hr_lookups
647 where lookup_type = p_lookup_type
648 and lookup_code = p_lookup_code;
649 cursor csr_table is
650 select description
651 from pay_all_payrolls_f pap
652 ,per_all_assignments_f paa
653 ,hr_lookups
654 where pap.payroll_id = paa.payroll_id
655 and paa.assignment_id = p_assignment_id
656 and p_effective_date between paa.effective_start_date and paa.effective_end_date
657 and meaning = pap.period_type
658 and lookup_type = 'PROC_PERIOD_TYPE';
659 cursor csr_period_table is
660 select nvl(DESCRIPTION,ptt.period_type)
661 from PER_TIME_PERIOD_TYPES ptt
662 ,pay_all_payrolls_f pap
663 ,per_all_assignments_f paa
664 where pap.payroll_id = paa.payroll_id
665 and paa.assignment_id = p_assignment_id
666 and p_effective_date between paa.effective_start_date and paa.effective_end_date
667 and ptt.period_type = pap.period_type;
668 begin
669 if p_lookup_type is not null and p_lookup_code is not null and p_lookup_code = 'PERIOD' then
670 open csr_table;
671 fetch csr_table into l_pay_basis;
672 close csr_table;
673 if l_pay_basis is null then
674 open csr_period_table;
675 fetch csr_period_table into l_pay_basis;
676 close csr_period_table;
677 end if;
678 else if p_lookup_type is not null and p_lookup_code is not null and p_lookup_code <> 'PERIOD' then
679 open csr_lookup;
680 fetch csr_lookup into l_pay_basis;
681 close csr_lookup;
682 end if;
683 end if;
684 RETURN l_pay_basis;
685 END get_pay_basis_frequency;
686
687 FUNCTION get_pay_annualization_factor (p_assignment_id IN NUMBER, p_effective_date IN DATE, p_annualization_factor IN NUMBER, p_pay_basis IN VARCHAR2)
688 return NUMBER
689 is
690 l_dummy pay_all_payrolls_f.payroll_name%type;
691 l_annualization_factor NUMBER(20);
692 Begin
693 l_annualization_factor := p_annualization_factor;
694 if(p_pay_basis ='PERIOD' and l_annualization_factor is null) then
695 PER_PAY_PROPOSALS_POPULATE.GET_PAYROLL(p_assignment_id
696 ,p_effective_date
697 ,l_dummy
698 ,l_annualization_factor);
699 end if;
700
701 if(l_annualization_factor = 0) THEN
702 l_annualization_factor := 1;
703 end if;
704
705 return l_annualization_factor;
706 END get_pay_annualization_factor;
707
708
709 FUNCTION get_lookup_desc ( p_lookup_type varchar2,p_lookup_code varchar2)
710 return varchar2
711 is
712 cursor csr_lookup is
713 select description
714 from hr_lookups
715 where lookup_type = p_lookup_type
716 and lookup_code = p_lookup_code;
717 v_description varchar2(100) := null;
718 begin
719 if p_lookup_type is not null and p_lookup_code is not null then
720 open csr_lookup;
721 fetch csr_lookup into v_description;
722 close csr_lookup;
723 end if;
724 return v_description;
725 end get_lookup_desc;
726
727 FUNCTION decode_grade_ladder (p_grade_ladder_id IN NUMBER, p_effective_date IN DATE)
728 return varchar2
729 is
730
731 cursor csr_lookup is
732 select name
733 from ben_pgm_f pgm
734 where pgm_id = p_grade_ladder_id
735 and p_effective_date between
736 pgm.effective_start_date and pgm.effective_end_date;
737
738 v_meaning varchar2(240) := null;
739
740 begin
741
742 if p_grade_ladder_id is not null then
743
744 open csr_lookup;
745 fetch csr_lookup into v_meaning;
746 close csr_lookup;
747
748 end if;
749 return v_meaning;
750 end decode_grade_ladder;
751
752
753 FUNCTION get_fte (p_assignment_id IN NUMBER, p_effective_date IN DATE)
754 RETURN NUMBER IS
755 l_fte number;
756 cursor c1(p_assignment_id number, p_effective_date date) is
757 select abv.value
758 from per_assignment_budget_values_f abv, per_all_assignments_f asg,
759 per_assignment_status_types ast
760 where asg.assignment_id = p_assignment_id
761 and abv.assignment_id = asg.assignment_id
762 and asg.assignment_type in ('E', 'C')
763 and abv.unit = 'FTE'
764 and p_effective_date between asg.effective_start_date and asg.effective_end_date
765 and p_effective_date between abv.effective_start_date and abv.effective_end_date
766 and asg.assignment_status_type_id = ast.assignment_status_type_id
767 and ast.per_system_status <> 'TERM_ASSIGN';
768 --
769 begin
770 open c1(p_assignment_id, p_effective_date);
771 fetch c1 into l_fte;
772 close c1;
773 return l_fte;
774 end;
775
776 FUNCTION get_annual_salary (
777 p_proposed_salary IN NUMBER,
778 p_assignment_id IN NUMBER,
779 p_change_date IN DATE
780 )
781 RETURN NUMBER
782 IS
783 l_annualization_factor per_pay_bases.pay_annualization_factor%TYPE;
784
785 CURSOR csr_annualization_factor
786 IS
787 SELECT ppb.pay_annualization_factor
788 FROM per_all_assignments_f paaf, per_pay_bases ppb
789 WHERE paaf.assignment_id = p_assignment_id
790 AND p_change_date BETWEEN paaf.effective_start_date
791 AND paaf.effective_end_date
792 AND ppb.pay_basis_id = paaf.pay_basis_id;
793 BEGIN
794 OPEN csr_annualization_factor;
795
796 FETCH csr_annualization_factor
797 INTO l_annualization_factor;
798
799 CLOSE csr_annualization_factor;
800
801 RETURN p_proposed_salary * l_annualization_factor;
802 END get_annual_salary;
803
804 FUNCTION get_currency (p_assignment_id IN NUMBER, p_change_date IN DATE)
805 RETURN VARCHAR2
806 IS
807 l_currency pay_element_types_f.input_currency_code%TYPE;
808
809 CURSOR currency
810 IS
811 SELECT pet.input_currency_code
812 FROM pay_element_types_f pet,
813 per_all_assignments_f paaf,
814 pay_input_values_f piv,
815 per_pay_bases ppb
816 WHERE paaf.assignment_id = p_assignment_id
817 AND p_change_date BETWEEN paaf.effective_start_date
818 AND paaf.effective_end_date
819 AND ppb.pay_basis_id = paaf.pay_basis_id
820 AND ppb.input_value_id = piv.input_value_id
821 AND p_change_date BETWEEN piv.effective_start_date
822 AND piv.effective_end_date
823 AND piv.element_type_id = pet.element_type_id
824 AND p_change_date BETWEEN pet.effective_start_date
825 AND pet.effective_end_date;
826
827 Cursor decode_Currency is
828 Select Name
829 from Fnd_Currencies_Vl
830 Where Currency_Code = l_currency;
831 -- and Enabled_Flag = 'Y'; -- 5579090
832
833 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
834
835
836 BEGIN
837 OPEN currency;
838
839 FETCH currency
840 INTO l_currency;
841
842 CLOSE currency;
843
844 If l_currency is Not Null then
845 open decode_Currency;
846 Fetch decode_Currency into P_Currency_name;
847 Close decode_Currency;
848 End If;
849
850 RETURN P_Currency_name;
851 END get_currency;
852
853 FUNCTION get_currency_format (p_assignment_id IN NUMBER, p_change_date IN DATE)
854 RETURN VARCHAR2
855 IS
856 l_currency pay_element_types_f.input_currency_code%TYPE;
857 l_uom varchar2(20);
858 l_five_curr pay_element_types_f.input_currency_code%TYPE;
859
860 CURSOR currency
861 IS
862 SELECT pet.input_currency_code,piv.uom
863 FROM pay_element_types_f pet,
864 per_all_assignments_f paaf,
865 pay_input_values_f piv,
866 per_pay_bases ppb
867 WHERE paaf.assignment_id = p_assignment_id
868 AND p_change_date BETWEEN paaf.effective_start_date
869 AND paaf.effective_end_date
870 AND ppb.pay_basis_id = paaf.pay_basis_id
871 AND ppb.input_value_id = piv.input_value_id
872 AND p_change_date BETWEEN piv.effective_start_date
873 AND piv.effective_end_date
874 AND piv.element_type_id = pet.element_type_id
875 AND p_change_date BETWEEN pet.effective_start_date
876 AND pet.effective_end_date;
877
878 Cursor five_curr is
879 Select CURRENCY_CODE
880 from Fnd_Currencies_Vl
881 Where PRECISION = 5
882 and rownum = 1;
883
884 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
885 BEGIN
886 OPEN currency;
887 FETCH currency INTO l_currency,l_uom;
888 CLOSE currency;
889
890 If l_uom <> 'M' then
891 OPEN five_curr;
892 FETCH five_curr INTO l_five_curr;
893 CLOSE five_curr;
894 l_currency := l_five_curr;
895 End If;
896
897 RETURN l_currency;
898 END get_currency_format;
899
900
901
902
903 FUNCTION get_pay_basis (p_assignment_id IN NUMBER, p_change_date IN DATE)
904 RETURN VARCHAR2
905 IS
906 l_pay_basis per_pay_bases.NAME%TYPE;
907
908 CURSOR csr_pay_basis
909 IS
910 SELECT ppb.NAME
911 FROM per_all_assignments_f paaf, per_pay_bases ppb
912 WHERE paaf.assignment_id = p_assignment_id
913 AND p_change_date BETWEEN paaf.effective_start_date
914 AND paaf.effective_end_date
915 AND ppb.pay_basis_id = paaf.pay_basis_id;
916 BEGIN
917 OPEN csr_pay_basis;
918
919 FETCH csr_pay_basis
920 INTO l_pay_basis;
921
922 CLOSE csr_pay_basis;
923
924 RETURN l_pay_basis;
925 END get_pay_basis;
926
927 FUNCTION get_change_amount (p_assignment_id IN NUMBER,
928 p_proposal_id IN NUMBER,
929 p_proposed_salary in number)
930 RETURN NUMBER
931 IS
932 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
933 BEGIN
934 l_previous_salary:= get_previous_salary(p_assignment_id,p_proposal_id);
935 if l_previous_salary = -1 then
936 return p_proposed_salary;
937 else
938 return p_proposed_salary - l_previous_salary;
939 end if;
940 END get_change_amount;
941
942 FUNCTION get_change_percent (p_assignment_id IN NUMBER,
943 p_proposal_id IN NUMBER,
944 p_proposed_salary in number)
945 RETURN NUMBER
946 is
947 l_change_amount number;
948 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
949
950 BEGIN
951 l_change_amount:= get_change_amount (p_assignment_id ,p_proposal_id ,p_proposed_salary);
952 l_previous_salary :=get_previous_salary(p_assignment_id,p_proposal_id);
953 if l_previous_salary = -1 then
954 return null;
955 else
956 return round((l_change_amount*100/l_previous_salary),6);
957 end if;
958 END get_change_percent;
959
960 --
961 --
962 -- Procedure CHECK_LENGTH is used for rounding off the Salary according to currency format
963 -- This procedure calculate the new salary value for the changed pay basis.
964 --
965 --
966
967 PROCEDURE CHECK_LENGTH(p_amount IN OUT NOCOPY NUMBER
968 ,p_uom IN VARCHAR2
969 ,p_currcode IN VARCHAR2) IS
970
971 L_PRECISION NUMBER;
972 L_EXT_PRECISION NUMBER;
973 L_MIN_ACCT_UNIT NUMBER;
974
975 BEGIN
976 if(p_uom='M') then
977 fnd_currency.get_info(currency_code => p_currcode
978 ,precision => L_PRECISION
979 ,EXT_PRECISION => L_EXT_PRECISION
980 ,MIN_ACCT_UNIT => L_MIN_ACCT_UNIT);
981 p_amount:=round(p_amount,l_precision);
982 else
983 p_amount:=round(p_amount,5);
984 end if;
985 END CHECK_LENGTH;
986
987
988
989 --
990 --
991 -- Procedure get_sal_on_basis_chg is called from the assignment form when the pay basis id is changed.
992 -- This procedure calculate the new salary value for the changed pay basis.
993 --
994 --
995 PROCEDURE get_sal_on_basis_chg
996 (p_assignment_id in number,
997 p_new_pay_basis_id in number,
998 p_effective_date in date,
999 p_old_pay_basis_id in number,
1000 p_curr_payroll_id in number)
1001 IS
1002
1003 l_change number;
1004 l_new number;
1005 l_percent number;
1006 l_comps VARCHAR2(1);
1007
1008 l_pay_basis VARCHAR2(30);
1009 l_old_currency VARCHAR2(15);
1010 l_salary_basis_name VARCHAR2(30);
1011 l_pay_basis_name VARCHAR2(80);
1012 l_old_pay_annualization_factor NUMBER;
1013 l_grade_annualization_factor NUMBER;
1014 l_grade VARCHAR2(240);
1015 l_prev_salary NUMBER;
1016 l_element_entry_id NUMBER;
1017 l_uom VARCHAR2(30);
1018
1019 l_prev_change_date DATE;
1020 l_prev_annual_salary number;
1021 --
1022 l_currency_rate NUMBER;
1023 l_cannual_amount NUMBER;
1024 l_new_pay_annual_factor NUMBER;
1025 l_new_currency VARCHAR2(15);
1026 l_new_annual_salary NUMBER;
1027 l_new_uom VARCHAR2(15);
1028 l_rec per_pay_proposals%ROWTYPE;
1029 --
1030 -- Cursor to get the pay proposal details
1031 --
1032 cursor get_prop_det (c_assignment_id in number,c_pay_basis_id in number) is
1033 select * from per_pay_proposals pro
1034 where pro.assignment_id = c_assignment_id
1035 and (p_effective_date - 1) between change_date and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy'));
1036 -- and pro.pay_proposal_id = c_pay_basis_id;
1037 --
1038 Cursor csr_new_pb_details(L_PAY_BASIS_ID in number) is
1039 SELECT PET.INPUT_CURRENCY_CODE
1040 , PPB.PAY_BASIS
1041 , PPB.PAY_ANNUALIZATION_FACTOR
1042 ,PIV.UOM
1043 FROM PAY_ELEMENT_TYPES_F PET
1044 , PAY_INPUT_VALUES_F PIV
1045 , PER_PAY_BASES PPB
1046 WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
1047 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
1048 AND p_effective_date BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
1049 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
1050 AND p_effective_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE;
1051 --
1052 Cursor csr_payroll_freq is
1053 select tpt.number_per_fiscal_year
1054 from pay_all_payrolls_f prl ,
1055 per_time_period_types tpt
1056 where prl.payroll_id = p_curr_payroll_id
1057 and p_effective_date between prl.effective_start_date and prl.effective_end_date
1058 and prl.period_type = tpt.period_type(+);
1059 --
1060 begin
1061 --
1062 hr_utility.set_location('Entering PER_SALADMIN_UTILITY.get_sal_on_basis_chg',10);
1063 --
1064 l_change:=null;
1065 l_new:=null;
1066 l_comps:='N'; --:REVIEW.MULTIPLE_COMPONENTS;
1067 l_percent:= 0;
1068 --
1069 -- Fetch the details of the old pay proposal
1070 --
1071 hr_utility.set_location('p_old_pay_basis_id ='||to_char(p_old_pay_basis_id),11);
1072 hr_utility.set_location('p_new_pay_basis_id ='||to_char(p_new_pay_basis_id),11);
1073 --
1074 open get_prop_det(p_assignment_id,p_old_pay_basis_id);
1075 fetch get_prop_det into l_rec;
1076 close get_prop_det;
1077 --
1078 -- Execute foll code only if a valid pay proposal exists of validation date.
1079 --
1080 If l_rec.pay_proposal_id is not null then
1081 --
1082 -- Get the old proposal date and salary
1083 --
1084 l_prev_change_date := l_rec.change_date +1;
1085 l_prev_salary := l_rec.proposed_salary_n;
1086 --
1087 hr_utility.set_location('Prev change date= '||to_char(l_prev_change_date,'dd/mm/yyyy'),12);
1088 hr_utility.set_location('l_prev_salary = '||to_char(l_prev_salary),13);
1089 --
1090 --Calculate the currency and pay_annualization_factor of the old salary basis
1091 --
1092 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS
1093 (p_effective_date => l_prev_change_date
1094 ,p_assignment_id => p_assignment_id
1095 ,p_currency => l_old_currency
1096 ,p_salary_basis_name => l_salary_basis_name
1097 ,p_pay_basis_name => l_pay_basis_name
1098 ,p_pay_basis => l_pay_basis
1099 ,p_pay_annualization_factor => l_old_pay_annualization_factor
1100 ,p_grade_basis => l_grade
1101 ,p_grade_annualization_factor => l_grade_annualization_factor
1102 ,p_element_type_id => l_element_entry_id
1103 ,p_uom => l_uom);
1104 hr_utility.set_location('l_old_currency = '||l_old_currency,13);
1105 hr_utility.set_location('l_salary_basis_name = '||l_salary_basis_name,13);
1106 hr_utility.set_location('l_pay_basis_name = '||l_pay_basis_name,13);
1107 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1108 hr_utility.set_location('l_old_pay_annualization_factor = '||to_char(l_old_pay_annualization_factor),13);
1109 hr_utility.set_location('l_grade = '||l_grade,13);
1110 hr_utility.set_location('l_grade_annualization_factor = '||to_char(l_grade_annualization_factor),13);
1111 hr_utility.set_location('l_element_entry_id = '||to_char(l_element_entry_id),13);
1112 hr_utility.set_location('l_uom = '||l_uom,13);
1113 --
1114 --
1115 --Calculate the currency and pay_annualization_factor of the new salary basis
1116 --
1117 Open csr_new_pb_details(p_new_pay_basis_id);
1118 Fetch csr_new_pb_details into l_new_currency,l_pay_basis,l_new_pay_annual_factor,l_new_uom;
1119 Close csr_new_pb_details;
1120 --
1121 hr_utility.set_location('l_new_currency = '||l_new_currency,13);
1122 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1123 hr_utility.set_location('l_new_pay_annual_factor = '||to_char(l_new_pay_annual_factor),13);
1124 --
1125 if(l_pay_basis ='PERIOD' and l_new_pay_annual_factor is null) then
1126 --
1127 hr_utility.set_location('Fetching Payroll frequency',13);
1128 --
1129 l_new_pay_annual_factor := 1;
1130 --
1131 open csr_payroll_freq;
1132 Fetch csr_payroll_freq into l_new_pay_annual_factor;
1133 Close csr_payroll_freq;
1134 end if;
1135 --
1136 hr_utility.set_location('l_new_pay_annual_factor = '||to_char(l_new_pay_annual_factor),13);
1137 /**
1138 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS
1139 (p_effective_date => p_effective_date
1140 ,p_assignment_id => p_assignment_id
1141 ,p_currency => l_new_currency
1142 ,p_salary_basis_name => l_salary_basis_name
1143 ,p_pay_basis_name => l_pay_basis_name
1144 ,p_pay_basis => l_pay_basis
1145 ,p_pay_annualization_factor => l_new_pay_annualization_factor
1146 ,p_grade_basis => l_grade
1147 ,p_grade_annualization_factor => l_grade_annualization_factor
1148 ,p_element_type_id => l_element_entry_id
1149 ,p_uom => l_uom);
1150 hr_utility.set_location('l_new_currency = '||l_new_currency,13);
1151 hr_utility.set_location('l_salary_basis_name = '||l_salary_basis_name,13);
1152 hr_utility.set_location('l_pay_basis_name = '||l_pay_basis_name,13);
1153 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1154 hr_utility.set_location('l_new_pay_annualization_factor = '||to_char(l_new_pay_annualization_factor),13);
1155 hr_utility.set_location('l_grade = '||l_grade,13);
1156 hr_utility.set_location('l_grade_annualization_factor = '||to_char(l_grade_annualization_factor),13);
1157 hr_utility.set_location('l_element_entry_id = '||to_char(l_element_entry_id),13);
1158 hr_utility.set_location('l_uom = '||l_uom,13);
1159 **/
1160 --
1161 --
1162 -- Calculate Old Annual amt and New Salary
1163 --
1164 if l_old_pay_annualization_factor = 0 then
1165 l_old_pay_annualization_factor := 1;
1166 end if;
1167
1168 if l_new_pay_annual_factor = 0 then
1169 l_new_pay_annual_factor := 1;
1170 end if;
1171
1172
1173
1174 l_prev_annual_salary := l_prev_salary * l_old_pay_annualization_factor;
1175 l_new := (l_prev_salary * l_old_pay_annualization_factor)/l_new_pay_annual_factor;
1176 --
1177 hr_utility.set_location('l_prev_annual_salary = '||to_char(l_prev_annual_salary),14);
1178 hr_utility.set_location('l_new = '||to_char(l_new),15);
1179 --
1180 -- If there is a new salary value and currencies of the old and new salary basis do not match, do a
1181 -- currency conversion of the new value.
1182 --
1183 l_currency_rate := 1;
1184 IF l_new IS NOT NULL THEN
1185 --
1186 if l_old_currency <> l_new_currency AND l_old_currency IS NOT NULL AND l_new_currency IS NOT NULL THEN
1187 --
1188 l_currency_rate := get_currency_rate(
1189 p_from_currency => l_old_currency,
1190 p_to_currency => l_new_currency,
1191 p_conversion_date => p_effective_date,
1192 p_business_group_id => l_rec.business_group_id);
1193 --
1194 End if;
1195 --
1196 End if;
1197 --
1198 hr_utility.set_location('Currency rate = '||to_char(l_currency_rate),16);
1199 l_new := l_currency_rate * l_new;
1200 hr_utility.set_location('Converted rate = '||to_char(l_new),20);
1201 --
1202 --
1203 if l_new_uom is null then
1204 l_new_uom := 'M';
1205 end if;
1206
1207 CHECK_LENGTH(l_new
1208 ,l_new_uom
1209 ,l_new_currency);
1210
1211 ----------------VKODEDAL 01-MAR-2007--------------------------------
1212 --FIX 5857948 -DEFAULT NEW PROPOSAL REASON TO SALARY BASIS CHANGE ADJUSTMENT
1213 --
1214 -- check that the p_proposal_reason exists in hr_lookups.
1215 --
1216 --Fix 6417656 - if SALBASISCHG doens't exist set the default reason to null
1217 if hr_api.not_exists_in_hr_lookups
1218 (p_effective_date => l_rec.change_date
1219 ,p_lookup_type => 'PROPOSAL_REASON'
1220 ,p_lookup_code => 'SALBASISCHG'
1221 ) then
1222 -- proposal_reason doesn't exist - set the reason to null
1223 hr_utility.set_location('proposal_reason set to null', 25);
1224 l_rec.proposal_reason :=null;
1225 else
1226 hr_utility.set_location('proposal_reason set to SALBASISCHG', 27);
1227 l_rec.proposal_reason := 'SALBASISCHG';
1228
1229 end if;
1230
1231
1232 ------------------------------------------------
1233 g_proposal_rec := l_rec;
1234 g_new_sal_value := l_new;
1235 --
1236 End if;
1237 --
1238 hr_utility.set_location('Leaving PER_SALADMIN_UTILITY.get_sal_on_basis_chg ',100);
1239 --
1240 EXCEPTION
1241 WHEN others THEN
1242 --
1243 hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.get_sal_on_basis_chg',100);
1244 g_proposal_rec := null;
1245 g_new_sal_value := null;
1246 raise;
1247 --
1248 end;
1249 --
1250 /* This procedure is called from the assignment form to insert the new salary value after a pay basis change */
1251 procedure insert_pay_proposal(p_assignment_id in number, p_validation_start_date in date) is
1252
1253 l_pay_proposal_id NUMBER;
1254 l_ovn NUMBER;
1255 l_inv_next_sal_date_warning BOOLEAN;
1256 l_inv_next_perf_date_warning BOOLEAN;
1257 l_proposed_salary_warning BOOLEAN;
1258 l_approved_warning BOOLEAN;
1259 l_payroll_warning BOOLEAN;
1260 l_approved VARCHAR2(1);
1261 l_element_entry_id NUMBER;
1262 l_review_date date;
1263
1264 begin
1265 if g_new_sal_value is not null then
1266
1267 hr_maintain_proposal_api.insert_salary_proposal
1268 (p_pay_proposal_id => l_pay_proposal_id
1269 ,p_assignment_id => p_assignment_id
1270 ,p_business_group_id => g_proposal_rec.business_group_id
1271 ,p_change_date => p_validation_start_date
1272 ,p_comments => g_proposal_rec.comments
1273 --,p_next_sal_review_date => l_review_date
1274 ,p_proposal_reason => g_proposal_rec.proposal_reason
1275 ,p_proposed_salary_n => g_new_sal_value
1276 ,p_forced_ranking => g_proposal_rec.forced_ranking
1277 ,p_performance_review_id => g_proposal_rec.performance_review_id
1278 ,p_attribute_category => g_proposal_rec.attribute_category
1279 ,p_attribute1 => g_proposal_rec.attribute1
1280 ,p_attribute2 => g_proposal_rec.attribute2
1281 ,p_attribute3 => g_proposal_rec.attribute3
1282 ,p_attribute4 => g_proposal_rec.attribute4
1283 ,p_attribute5 => g_proposal_rec.attribute5
1284 ,p_attribute6 => g_proposal_rec.attribute6
1285 ,p_attribute7 => g_proposal_rec.attribute7
1286 ,p_attribute8 => g_proposal_rec.attribute8
1287 ,p_attribute9 => g_proposal_rec.attribute9
1288 ,p_attribute10 => g_proposal_rec.attribute10
1289 ,p_attribute11 => g_proposal_rec.attribute11
1290 ,p_attribute12 => g_proposal_rec.attribute12
1291 ,p_attribute13 => g_proposal_rec.attribute13
1292 ,p_attribute14 => g_proposal_rec.attribute14
1293 ,p_attribute15 => g_proposal_rec.attribute15
1294 ,p_attribute16 => g_proposal_rec.attribute16
1295 ,p_attribute17 => g_proposal_rec.attribute17
1296 ,p_attribute18 => g_proposal_rec.attribute18
1297 ,p_attribute19 => g_proposal_rec.attribute19
1298 ,p_attribute20 => g_proposal_rec.attribute20
1299 ,p_object_version_number => l_ovn
1300 ,p_multiple_components => g_proposal_rec.multiple_components
1301 ,p_approved => 'Y'
1302 ,p_validate => FALSE
1303 ,p_element_entry_id => l_element_entry_id
1304 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1305 ,p_proposed_salary_warning => l_proposed_salary_warning
1306 ,p_approved_warning => l_approved_warning
1307 ,p_payroll_warning => l_payroll_warning
1308 ,p_date_to => to_date('31/12/4712','dd/mm/yyyy')
1309 );
1310 end if;
1311
1312 g_proposal_rec := null;
1313 g_new_sal_value := null;
1314
1315 EXCEPTION
1316 WHEN others THEN
1317 hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.insert_pay_proposal',100);
1318 g_proposal_rec := null;
1319 g_new_sal_value := null;
1320 raise;
1321 end;
1322
1323 function get_currency_rate(
1324 p_from_currency VARCHAR2,
1325 p_to_currency VARCHAR2,
1326 p_conversion_date DATE,
1327 p_business_group_id number) return number is
1328 l_conversion_date date;
1329 l_conversion_type varchar2(80);
1330 l_currency_rate number := 1;
1331 begin
1332 if (p_conversion_date is not null) then
1333 l_conversion_date := p_conversion_date;
1334 else
1335 l_conversion_date := sysdate;
1336 end if;
1337
1338 l_conversion_type := hr_currency_pkg.get_rate_type
1339 (p_business_group_id,p_conversion_date,'P');
1340 if (l_conversion_type is not null) then
1341 l_currency_rate :=
1342 hr_currency_pkg.get_rate_sql
1343 (p_from_currency,
1344 p_to_currency ,
1345 l_conversion_date,
1346 l_conversion_type
1347 );
1348 If l_currency_rate < 0 then
1349 l_currency_rate := 1;
1350 End if;
1351
1352 end if;
1353 --
1354 return l_currency_rate;
1355 end;
1356 -- --------------------------------------------------------------------------
1357 -- |---------------------------< get_fte_factor >---------------------------|
1358 -- --------------------------------------------------------------------------
1359 FUNCTION get_fte_factor(p_assignment_id IN NUMBER
1360 ,p_effective_date IN DATE)
1361 return NUMBER IS
1362
1363 l_fte_profile_value VARCHAR2(240) := fnd_profile.VALUE('BEN_CWB_FTE_FACTOR');
1364
1365 CURSOR csr_fte_BFTE
1366 IS
1367 select nvl(value, 1) val
1368 from per_assignment_budget_values_f
1369 where assignment_id = p_assignment_id
1370 and unit = 'FTE'
1371 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
1372 CURSOR csr_fte_BPFT
1373 IS
1374 select nvl(value, 1) val
1375 from per_assignment_budget_values_f
1376 where assignment_id = p_assignment_id
1377 and unit = 'PFT'
1378 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
1379
1380 l_fte_factor number := null;
1381 l_norm_hours_per_year number;
1382 l_hours_per_year number;
1383 BEGIN
1384 --
1385 if (l_fte_profile_value = 'NHBGWH') then
1386
1387 PER_PAY_PROPOSALS_POPULATE.get_asg_hours(p_assignment_id
1388 ,p_effective_date
1389 ,l_hours_per_year);
1390
1391 if(nvl(l_hours_per_year,0) <> 0) then
1392 PER_PAY_PROPOSALS_POPULATE.get_norm_hours(p_assignment_id
1393 ,p_effective_date
1394 ,l_norm_hours_per_year);
1395 if ( nvl(l_norm_hours_per_year,0) = 0) then
1396 l_fte_factor := 1;
1397 else
1398 l_fte_factor := l_hours_per_year/l_norm_hours_per_year;
1399 end if;
1400 else
1401 l_fte_factor := 1;
1402 end if;
1403 elsif (l_fte_profile_value = 'BFTE') then
1404 for r1 in csr_fte_BFTE loop
1405 l_fte_factor := r1.val;
1406 end loop;
1407 elsif (l_fte_profile_value = 'BPFT') then
1408 for r1 in csr_fte_BPFT loop
1409 l_fte_factor := r1.val;
1410 end loop;
1411 else
1412 l_fte_factor := 1;
1413 end if;
1414 -- fte can be greater than 1 Bug#7497075 schowdhu
1415 -- if (l_fte_factor is null or l_fte_factor > 1) then
1416 if (l_fte_factor is null) then
1417 l_fte_factor := 1;
1418 end if;
1419 --
1420 RETURN l_fte_factor;
1421
1422 END get_fte_factor;
1423
1424 Function asg_pay_proposal_starts_at(p_assignment_id IN NUMBER, p_date in date)
1425 return varchar2 is
1426 l_dummy varchar2(10);
1427 CURSOR c_pay_proposals is
1428 select null
1429 from per_pay_proposals pro
1430 where pro.assignment_id = p_assignment_id
1431 and pro.change_date = p_date;
1432 begin
1433 OPEN c_pay_proposals;
1434 FETCH c_pay_proposals into l_dummy;
1435 IF c_pay_proposals%FOUND then
1436 CLOSE c_pay_proposals;
1437 return('Y');
1438 end if;
1439 CLOSE c_pay_proposals;
1440 return ('N');
1441 end asg_pay_proposal_starts_at;
1442
1443 Function get_initial_proposal_start(p_assignment_id IN NUMBER)
1444 return date is
1445 l_min_change_date date;
1446 CURSOR c_pay_proposals is
1447 select min(change_date)
1448 from per_pay_proposals pro
1449 where pro.assignment_id = p_assignment_id;
1450 begin
1451 OPEN c_pay_proposals;
1452 FETCH c_pay_proposals into l_min_change_date;
1453 IF c_pay_proposals%FOUND then
1454 CLOSE c_pay_proposals;
1455 return(l_min_change_date);
1456 end if;
1457 CLOSE c_pay_proposals;
1458 return (null);
1459 end get_initial_proposal_start;
1460
1461 function get_assignment_fte(p_assignment_id number, p_effective_date date) return number is
1462 l_fte number := 0;
1463 cursor c1(p_assignment_id number, p_effective_date date) is
1464 select nvl(abv.value,0)
1465 from per_assignment_budget_values_f abv, per_all_assignments_f asg,
1466 per_assignment_status_types ast
1467 where asg.assignment_id = p_assignment_id
1468 and abv.assignment_id = asg.assignment_id
1469 and asg.assignment_type in ('E', 'C')
1470 and abv.unit = 'FTE'
1471 and p_effective_date between asg.effective_start_date and asg.effective_end_date
1472 and p_effective_date between abv.effective_start_date and abv.effective_end_date
1473 and asg.assignment_status_type_id = ast.assignment_status_type_id
1474 and ast.per_system_status <> 'TERM_ASSIGN';
1475 --
1476 begin
1477 open c1(p_assignment_id, p_effective_date);
1478 fetch c1 into l_fte;
1479 close c1;
1480 return l_fte;
1481 end;
1482
1483 FUNCTION get_basis_lookup (p_assignment_id IN NUMBER, p_change_date IN DATE)
1484 RETURN VARCHAR2
1485 IS
1486 l_pay_basis per_pay_bases.PAY_BASIS%TYPE;
1487
1488 CURSOR csr_pay_basis
1489 IS
1490 SELECT ppb.Pay_basis
1491 FROM per_all_assignments_f paaf, per_pay_bases ppb
1492 WHERE paaf.assignment_id = p_assignment_id
1493 AND p_change_date BETWEEN paaf.effective_start_date
1494 AND paaf.effective_end_date
1495 AND ppb.pay_basis_id = paaf.pay_basis_id;
1496 BEGIN
1497 OPEN csr_pay_basis;
1498
1499 FETCH csr_pay_basis
1500 INTO l_pay_basis;
1501
1502 CLOSE csr_pay_basis;
1503
1504 RETURN l_pay_basis;
1505 END get_basis_lookup;
1506
1507 Function get_next_sal_basis_chg_dt(p_assignment_id IN NUMBER, p_from_date IN DATE)
1508 return date is
1509 l_next_sb_date date;
1510 CURSOR c_next_sb_date(p_assignment_id IN NUMBER, p_from_date IN DATE) is
1511 select min(effective_start_date)
1512 from per_all_assignments_f
1513 where assignment_id = p_assignment_id
1514 and effective_start_date > p_from_date
1515 and pay_basis_id not in
1516 (select pay_basis_id
1517 from per_all_assignments_f
1518 where assignment_id = p_assignment_id
1519 and p_from_date
1520 between effective_start_date and effective_end_date );
1521 begin
1522 OPEN c_next_sb_date(p_assignment_id, p_from_date);
1523 FETCH c_next_sb_date into l_next_sb_date;
1524 IF c_next_sb_date%FOUND then
1525 CLOSE c_next_sb_date;
1526 return(l_next_sb_date);
1527 end if;
1528 CLOSE c_next_sb_date;
1529 return (null);
1530 end get_next_sal_basis_chg_dt;
1531
1532 FUNCTION get_pay_basis_id(p_assignment_id IN NUMBER, p_from_date IN DATE)
1533 RETURN NUMBER
1534 IS
1535 l_pay_basis_id number;
1536
1537 CURSOR c_pay_basis_id is
1538 select pay_basis_id
1539 from per_all_assignments_f
1540 where assignment_id = p_assignment_id
1541 and p_from_date
1542 between effective_start_date and effective_end_date;
1543 BEGIN
1544 OPEN c_pay_basis_id;
1545 FETCH c_pay_basis_id INTO l_pay_basis_id;
1546 CLOSE c_pay_basis_id;
1547
1548 RETURN l_pay_basis_id;
1549 END get_pay_basis_id;
1550
1551 Function get_asg_sal_basis_end_dt(p_assignment_id IN NUMBER, p_from_date IN DATE)
1552 return date is
1553 l_asg_sb_end_date date;
1554 l_pay_basis_id number;
1555
1556 CURSOR c_asg_sal_basis_end_dt(p_assignment_id IN NUMBER,
1557 p_pay_basis_id IN NUMBER,
1558 p_from_date IN DATE) is
1559 select min(effective_start_date) - 1
1560 from per_all_assignments_f
1561 where assignment_id = p_assignment_id
1562 and effective_start_date >= p_from_date
1563 and nvl(pay_basis_id,-1) <> p_pay_basis_id;
1564 begin
1565 l_pay_basis_id := get_pay_basis_id(p_assignment_id, p_from_date);
1566
1567 OPEN c_asg_sal_basis_end_dt(p_assignment_id, l_pay_basis_id, p_from_date);
1568 FETCH c_asg_sal_basis_end_dt into l_asg_sb_end_date;
1569 IF c_asg_sal_basis_end_dt%FOUND then
1570 CLOSE c_asg_sal_basis_end_dt;
1571 return(l_asg_sb_end_date);
1572 end if;
1573 CLOSE c_asg_sal_basis_end_dt;
1574 return (null);
1575 end get_asg_sal_basis_end_dt;
1576
1577 END PER_SALADMIN_UTILITY;
1578
[Home] [Help]