1 PACKAGE BODY PER_SALADMIN_UTILITY AS
2 /* $Header: pesalutl.pkb 120.41.12020000.3 2012/11/08 06:42:55 vsripera 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 --start of changes for bug # 12588482
189 l_derived_date :=least ( ADD_MONTHS (p_change_date, l_num_months),
190 TRUNC(ADD_MONTHS (p_change_date, l_num_months),'MONTH') +
191 (EXTRACT(DAY FROM TO_DATE(p_change_date)) - 1) );
192 --end of changes for bug # 12588482
193 elsif (l_num_days <> 0 ) then
194 l_derived_date := p_change_date + l_num_days;
195 end if;
196
197 return l_derived_date;
198 --
199 end derive_next_sal_perf_date;
200
201 FUNCTION get_next_sal_review_date(p_assignment_id IN NUMBER,p_change_date IN DATE,p_business_group_id IN NUMBER)
202 RETURN DATE
203 IS
204 --
205 l_sal_review_period number(15);
206 l_sal_review_period_frequency varchar2(30);
207 l_next_sal_review_date Date;
208 --
209 cursor csr_sal_review_details is
210 select sal_review_period,
211 sal_review_period_frequency
212 from per_all_assignments_f
213 where assignment_id = p_assignment_id
214 and business_group_id + 0 = p_business_group_id
215 and p_change_date between effective_start_date
216 and nvl(effective_end_date, hr_general.end_of_time);
217 --
218 BEGIN
219 OPEN csr_sal_review_details;
220 FETCH csr_sal_review_details into l_sal_review_period,l_sal_review_period_frequency;
221
222 If csr_sal_review_details%found then
223 If (l_sal_review_period is not null) then
224 l_next_sal_review_date :=
225 derive_next_sal_perf_date
226 (p_change_date => p_change_date
227 ,p_period => l_sal_review_period
228 ,p_frequency => l_sal_review_period_frequency
229 );
230 end If;
231 end If;
232 close csr_sal_review_details;
233
234 return l_next_sal_review_date ;
235 END get_next_sal_review_date;
236
237
238
239 FUNCTION get_uom(p_pay_proposal_id IN NUMBER)
240 RETURN VARCHAR2
241 IS
242 l_uom pay_input_values_f.uom%TYPE;
243 CURSOR get_uom_cur
244 IS
245 SELECT piv.uom
246 FROM pay_element_types_f pet,
247 per_all_assignments_f paaf,
248 pay_input_values_f piv,
249 per_pay_bases ppb,
250 per_pay_proposals ppp
251 WHERE ppp.pay_proposal_id = p_pay_proposal_id
252 and paaf.assignment_id = ppp.assignment_id
253 and ppp.change_date BETWEEN paaf.effective_start_date
254 and paaf.effective_end_date
255 and ppb.pay_basis_id = paaf.pay_basis_id
256 and ppb.input_value_id = piv.input_value_id
257 and ppp.change_date BETWEEN piv.effective_start_date
258 and piv.effective_end_date
259 and piv.element_type_id = pet.element_type_id
260 and ppp.change_date BETWEEN pet.effective_start_date
261 and pet.effective_end_date;
262 BEGIN
263 OPEN get_uom_cur;
264 FETCH get_uom_cur INTO l_uom;
265 CLOSE get_uom_cur;
266 return l_uom;
267 END get_uom;
268
269 Function get_previous_proposal_dt(p_assignment_id IN NUMBER,p_change_date IN DATE)
270 return date
271 is
272 l_prev_change_date date;
273 CURSOR c_prev_pay_proposals is
274 select max(change_date)
275 from per_pay_proposals pro
276 where pro.assignment_id = p_assignment_id
277 and pro.change_date < p_change_date ;
278 begin
279 OPEN c_prev_pay_proposals;
280 FETCH c_prev_pay_proposals into l_prev_change_date;
281 IF c_prev_pay_proposals%FOUND then
282 CLOSE c_prev_pay_proposals;
283 return(l_prev_change_date);
284 end if;
285 CLOSE c_prev_pay_proposals;
286 return (null);
287 end get_previous_proposal_dt;
288
289
290
291
292 function get_next_proposal_with_comp(p_assignment_id in number,
293 p_session_date in date)
294 return date
295 is
296 cursor c1(p_assignment_id in number,
297 p_session_date in date) is
298 select change_date
299 from per_pay_proposals
300 where assignment_id = p_assignment_id
301 and multiple_components = 'Y'
302 and change_date =
303 (select min(change_date)
304 from per_pay_proposals
305 where assignment_id = p_assignment_id
306 and change_date > p_session_date);
307 --
308 l_change_date per_pay_proposals.change_date%TYPE;
309 begin
310 --
311 open c1(p_assignment_id, p_session_date);
312 fetch c1 into l_change_date;
313 close c1;
314 --
315 return l_change_date;
316 --
317 end get_next_proposal_with_comp;
318
319
320
321 procedure adjust_pay_proposals
322 (
323 p_assignment_id number
324 ) is
325
326 l_next_change_date per_pay_proposals.change_date%TYPE;
327 l_approved per_pay_proposals.approved%TYPE;
328 l_element_entry_end_date pay_element_entries_f.effective_end_date%TYPE;
329
330 Cursor csr_pay_proposals
331 is
332 select pay_proposal_id, change_date,date_to
333 from per_pay_proposals
334 where assignment_id = p_assignment_id
335 and approved = 'Y';
336
337 Cursor csr_next_change_date(p_change_date in date)
338 IS
339 select min(change_date)
340 from per_pay_proposals
341 where change_date > p_change_date
342 and assignment_id = p_assignment_id;
343
344 Cursor csr_approved(p_change_date in date)
345 is
346 select approved
347 from per_pay_proposals
348 where change_date = p_change_date
349 and assignment_id = p_assignment_id;
350
351 Cursor element_entry_end_date(p_pay_proposal_id in number,p_change_date in date)
352 is
353 select effective_end_date
354 From pay_element_entries_f
355 where assignment_id = p_assignment_id
356 and creator_type = 'SP'
357 and creator_id = p_pay_proposal_id
358 and effective_start_date =p_change_date;
359
360 begin
361
362 if g_debug then
363 hr_utility.trace('In per_saladmin_utility.adjust_pay_proposals');
364 end if;
365 for i in csr_pay_proposals loop
366 ---vkodedal fix -5941519
367 l_element_entry_end_date:=null;
368 if g_debug then
369 hr_utility.set_location('Proposal Id'||i.pay_proposal_id, 66);
370 hr_utility.set_location('Change Date'||i.change_date, 66);
371 hr_utility.set_location('Date to'||i.date_to, 67);
372 end if;
373
374 OPEN element_entry_end_date(i.pay_proposal_id,i.change_date);
375 FETCH element_entry_end_date into l_element_entry_end_date;
376 CLOSE element_entry_end_date;
377
378 if g_debug then
379 hr_utility.set_location('End Date'||l_element_entry_end_date, 70);
380 end if;
381
382 if l_element_entry_end_date is not null and l_element_entry_end_date <> i.date_to then
383 if g_debug then
384 hr_utility.set_location('End Date and date_to are not matching', 70);
385 end if;
386
387 OPEN csr_next_change_date(i.change_date);
388 FETCH csr_next_change_date into l_next_change_date;
389 CLOSE csr_next_change_date;
390
391 if l_next_change_date is not null then
392
393 OPEN csr_approved(l_next_change_date);
394 FETCH csr_approved into l_approved;
395 CLOSE csr_approved;
396
397 if l_approved = 'N' THEN
398
399 if l_element_entry_end_date > l_next_change_date-1 THEN
400
401 l_element_entry_end_date :=l_next_change_date-1;
402
403 END IF;
404
405 END IF;
406
407 end if;
408
409 if g_debug then
410 hr_utility.set_location('About to update', 70);
411 end if;
412
413 update per_pay_proposals
414 set date_to =l_element_entry_end_date
415 where pay_proposal_id = i.pay_proposal_id;
416
417 if g_debug then
418 hr_utility.set_location('Updated successfully', 70);
419 end if;
420
421
422 END IF;
423
424
425 end loop;
426
427 if g_debug then
428 hr_utility.trace('OUT per_saladmin_utility.adjust_pay_proposals');
429 end if;
430
431 end adjust_pay_proposals;
432
433 function get_last_payroll_dt(p_assignment_id NUMBER) RETURN date IS
434 l_asg_start_date date;
435 l_last_payroll_dt date;
436
437 cursor c1(p_assignment_id number) is
438 select min(effective_start_date)
439 from per_all_assignments_f
440 where assignment_id = p_assignment_id;
441
442 cursor csr_last_dt is
443 ---removed nvl from query, now returns null when no payroll run bug#9612944
444 Select /*+ use_nl(paf paa) */ max(ppa.date_earned)
445 from pay_assignment_actions paa,
446 per_all_assignments_f paf,
447 pay_payroll_actions ppa
448 where paf.assignment_id = p_assignment_id
449 and (paf.effective_end_date >= l_asg_start_date
450 and paf.effective_start_date <= hr_general.end_of_time)
451 and paa.assignment_id = paf.assignment_id
452 and ppa.payroll_action_id = paa.payroll_action_id
453 and ppa.effective_date +0 between
454 greatest(l_asg_start_date,paf.effective_start_date)
455 and least(hr_general.end_of_time,paf.effective_end_date)
456 --start of code change for bug 14054849
457 /*and ((nvl(paa.run_type_id, ppa.run_type_id) is null and
458 paa.source_action_id is null)
459 or (nvl(paa.run_type_id, ppa.run_type_id) is not null and
460 paa.source_action_id is not null ))*/
461 --end of code change for bug 14054849
462 and ppa.action_type in ('R','Q')
463 --start of code change for bug 14054849
464 AND ppa.action_status = 'C'
465 AND paa.action_status = 'C';
466 --end of code change for bug 14054849
467
468 begin
469
470 l_last_payroll_dt :=null;
471
472 open c1(p_assignment_id);
473 fetch c1 into l_asg_start_date;
474 close c1;
475 --
476
477 OPEN csr_last_dt;
478 FETCH csr_last_dt INTO l_last_payroll_dt;
479 CLOSE csr_last_dt;
480 --
481 return l_last_payroll_dt;
482
483 end;
484
485 Function get_previous_salary(p_assignment_id IN NUMBER,p_proposal_id in number)
486 return number
487 is
488 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
489 CURSOR previous_pay is
490 select pro.proposed_salary_n
491 from per_pay_proposals pro
492 where pro.assignment_id = p_assignment_id
493 and pro.change_date =(select max(pro2.change_date)
494 from per_pay_proposals pro2
495 where pro2.assignment_id = p_assignment_id
496 and pro2.change_date < (select change_date from per_pay_proposals
497 where pay_proposal_id =p_proposal_id));
498 begin
499 OPEN previous_pay;
500 FETCH previous_pay into l_previous_salary;
501 IF previous_pay%NOTFOUND then
502 l_previous_salary := -1;
503 end if;
504 CLOSE previous_pay;
505 return l_previous_salary;
506 end get_previous_salary;
507
508
509
510
511 FUNCTION get_proposed_salary (p_assignment_id IN NUMBER,p_effective_date IN DATE )
512 RETURN NUMBER
513 is
514 l_salary per_pay_proposals.proposed_salary_n%TYPE;
515
516 cursor csr_sal is
517 select proposed_salary_n
518 from per_pay_proposals
519 where assignment_id = p_assignment_id
520 and p_effective_date between nvl(change_date,hr_general.start_of_time) and nvl(date_to,hr_general.end_of_time);
521 begin
522 open csr_sal;
523 fetch csr_sal into l_salary;
524 close csr_sal;
525 RETURN l_salary;
526 END get_proposed_salary;
527
528
529 FUNCTION GET_ANNUALIZATION_FACTOR(p_assignment_id NUMBER,p_effective_date DATE)
530 RETURN number
531 IS
532 l_dummy VARCHAR2(30);
533 l_pay_basis VARCHAR2(30);
534 l_pay_basis_id NUMBER;
535 l_pay_annualization_factor NUMBER;
536
537 CURSOR c_pay_basis is
538 SELECT PAF.PAY_BASIS_ID
539 FROM PER_ALL_ASSIGNMENTS_F PAF
540 WHERE PAF.ASSIGNMENT_ID=p_assignment_id
541 AND p_effective_date BETWEEN
542 PAF.EFFECTIVE_START_DATE AND
543 PAF.EFFECTIVE_END_DATE;
544
545 CURSOR Currency IS
546 SELECT HR_GENERAL.DECODE_LOOKUP('PAY_BASIS',PPB.PAY_BASIS)
547 ,PPB.PAY_ANNUALIZATION_FACTOR
548 FROM PAY_ELEMENT_TYPES_F PET
549 ,PAY_INPUT_VALUES_F PIV
550 ,PER_PAY_BASES PPB
551 WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
552 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
553 AND p_effective_date BETWEEN
554 PIV.EFFECTIVE_START_DATE AND
555 PIV.EFFECTIVE_END_DATE
556 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
557 AND p_effective_date BETWEEN
558 PET.EFFECTIVE_START_DATE AND
559 PET.EFFECTIVE_END_DATE;
560
561 CURSOR payroll is
562 select tpt.number_per_fiscal_year
563 from pay_all_payrolls_f prl
564 , per_all_assignments_f paf
565 , per_time_period_types tpt
566 where paf.assignment_id=p_assignment_id
567 and p_effective_date between paf.effective_start_date
568 and paf.effective_end_date
569 and paf.payroll_id=prl.payroll_id
570 and p_effective_date between prl.effective_start_date
571 and prl.effective_end_date
572 and prl.period_type = tpt.period_type(+);
573
574
575 BEGIN
576
577 open c_pay_basis;
578 fetch c_pay_basis into l_pay_basis_id;
579 close c_pay_basis;
580
581 open Currency;
582 fetch Currency
583 into l_pay_basis
584 ,l_pay_annualization_factor;
585 close Currency;
586
587 if(l_pay_basis ='Period Salary' and l_pay_annualization_factor is null) then
588 open payroll;
589 fetch payroll
590 into l_pay_annualization_factor;
591 close payroll;
592 end if;
593
594 return l_pay_annualization_factor;
595 END GET_ANNUALIZATION_FACTOR;
596
597
598
599
600 FUNCTION get_grade (p_assignment_id IN NUMBER,p_effective_date IN DATE )
601 RETURN VARCHAR2
602 is
603 l_grade per_grades_vl.name%type :=null ;
604
605 cursor csr_grade is
606 select HR_GENERAL.DECODE_GRADE(paa.grade_id) as grade
607 from per_all_assignments_f paa
608 where paa.assignment_id = p_assignment_id
609 and p_effective_date between paa.effective_start_date and paa.effective_end_date;
610 begin
611 open csr_grade;
612 fetch csr_grade into l_grade;
613 close csr_grade;
614 RETURN l_grade;
615 END get_grade;
616
617
618 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 )
619 RETURN VARCHAR2
620 is
621 l_grade_currency pay_grade_rules_f.currency_code%type :=null ;
622
623 cursor csr_grade_currency is
624 select grdrule.currency_code
625 from pay_grade_rules_f grdrule
626 where grdrule.rate_id = p_rate_id
627 and grdrule.grade_or_spinal_point_id = p_grade_id
628 and p_effective_date between grdrule.effective_start_date
629 and grdrule.effective_end_date;
630 begin
631 open csr_grade_currency;
632 fetch csr_grade_currency into l_grade_currency;
633 close csr_grade_currency;
634
635 if(l_grade_currency is null) then
636 l_grade_currency := hr_general.DEFAULT_CURRENCY_CODE(p_business_group_id);
637 end if;
638
639 RETURN l_grade_currency;
640 END get_grade_currency;
641
642
643
644
645 FUNCTION get_basis_currency_code (p_assignment_id IN NUMBER,p_effective_date IN DATE )
646 RETURN VARCHAR2
647 is
648 l_currency varchar2(100):=null ;
649 cursor csr_currency is
650 select pet.input_currency_code as currency_code
651 from per_pay_bases ppb
652 ,pay_input_values_f piv
653 ,pay_element_types_f pet
654 ,per_all_assignments_f paa
655 where paa.pay_basis_id = ppb.pay_basis_id
656 and ppb.input_value_id = piv.input_value_id
657 and piv.element_type_id = pet.element_type_id
658 and paa.assignment_id = p_assignment_id
659 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)
660 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)
661 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);
662 begin
663 open csr_currency;
664 fetch csr_currency into l_currency;
665 close csr_currency;
666 RETURN l_currency;
667 END get_basis_currency_code;
668
669
670
671 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 )
672 RETURN VARCHAR2
673 is
674 l_pay_basis varchar2(100):=null;
675 cursor csr_lookup is
676 select description
677 from hr_lookups
678 where lookup_type = p_lookup_type
679 and lookup_code = p_lookup_code;
680 cursor csr_table is
681 select description
682 from pay_all_payrolls_f pap
683 ,per_all_assignments_f paa
684 ,hr_lookups
685 where pap.payroll_id = paa.payroll_id
686 and paa.assignment_id = p_assignment_id
687 and p_effective_date between paa.effective_start_date and paa.effective_end_date
688 and meaning = pap.period_type
689 and lookup_type = 'PROC_PERIOD_TYPE';
690 cursor csr_period_table is
691 select nvl(DESCRIPTION,ptt.period_type)
692 from PER_TIME_PERIOD_TYPES ptt
693 ,pay_all_payrolls_f pap
694 ,per_all_assignments_f paa
695 where pap.payroll_id = paa.payroll_id
696 and paa.assignment_id = p_assignment_id
697 and p_effective_date between paa.effective_start_date and paa.effective_end_date
698 and ptt.period_type = pap.period_type;
699 begin
700 if p_lookup_type is not null and p_lookup_code is not null and p_lookup_code = 'PERIOD' then
701 open csr_table;
702 fetch csr_table into l_pay_basis;
703 close csr_table;
704 if l_pay_basis is null then
705 open csr_period_table;
706 fetch csr_period_table into l_pay_basis;
707 close csr_period_table;
708 end if;
709 else if p_lookup_type is not null and p_lookup_code is not null and p_lookup_code <> 'PERIOD' then
710 open csr_lookup;
711 fetch csr_lookup into l_pay_basis;
712 close csr_lookup;
713 end if;
714 end if;
715 RETURN l_pay_basis;
716 END get_pay_basis_frequency;
717
718 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)
719 return NUMBER
720 is
721 l_dummy pay_all_payrolls_f.payroll_name%type;
722 l_annualization_factor NUMBER(20);
723 Begin
724 l_annualization_factor := p_annualization_factor;
725 if(p_pay_basis ='PERIOD' and l_annualization_factor is null) then
726 PER_PAY_PROPOSALS_POPULATE.GET_PAYROLL(p_assignment_id
727 ,p_effective_date
728 ,l_dummy
729 ,l_annualization_factor);
730 end if;
731
732 if(l_annualization_factor = 0) THEN
733 l_annualization_factor := 1;
734 end if;
735
736 return l_annualization_factor;
737 END get_pay_annualization_factor;
738
739
740 FUNCTION get_lookup_desc ( p_lookup_type varchar2,p_lookup_code varchar2)
741 return varchar2
742 is
743 cursor csr_lookup is
744 select description
745 from hr_lookups
746 where lookup_type = p_lookup_type
747 and lookup_code = p_lookup_code;
748 v_description varchar2(100) := null;
749 begin
750 if p_lookup_type is not null and p_lookup_code is not null then
751 open csr_lookup;
752 fetch csr_lookup into v_description;
753 close csr_lookup;
754 end if;
755 return v_description;
756 end get_lookup_desc;
757
758 FUNCTION decode_grade_ladder (p_grade_ladder_id IN NUMBER, p_effective_date IN DATE)
759 return varchar2
760 is
761
762 cursor csr_lookup is
763 select name
764 from ben_pgm_f pgm
765 where pgm_id = p_grade_ladder_id
766 and p_effective_date between
767 pgm.effective_start_date and pgm.effective_end_date;
768
769 v_meaning varchar2(240) := null;
770
771 begin
772
773 if p_grade_ladder_id is not null then
774
775 open csr_lookup;
776 fetch csr_lookup into v_meaning;
777 close csr_lookup;
778
779 end if;
780 return v_meaning;
781 end decode_grade_ladder;
782
783
784 FUNCTION get_fte (p_assignment_id IN NUMBER, p_effective_date IN DATE)
785 RETURN NUMBER IS
786 l_fte number;
787 cursor c1(p_assignment_id number, p_effective_date date) is
788 select abv.value
789 from per_assignment_budget_values_f abv, per_all_assignments_f asg,
790 per_assignment_status_types ast
791 where asg.assignment_id = p_assignment_id
792 and abv.assignment_id = asg.assignment_id
793 and asg.assignment_type in ('E', 'C')
794 and abv.unit = 'FTE'
795 and p_effective_date between asg.effective_start_date and asg.effective_end_date
796 and p_effective_date between abv.effective_start_date and abv.effective_end_date
797 and asg.assignment_status_type_id = ast.assignment_status_type_id
798 and ast.per_system_status <> 'TERM_ASSIGN';
799 --
800 begin
801 open c1(p_assignment_id, p_effective_date);
802 fetch c1 into l_fte;
803 close c1;
804 return l_fte;
805 end;
806
807 FUNCTION get_annual_salary (
808 p_proposed_salary IN NUMBER,
809 p_assignment_id IN NUMBER,
810 p_change_date IN DATE
811 )
812 RETURN NUMBER
813 IS
814 l_annualization_factor per_pay_bases.pay_annualization_factor%TYPE;
815
816 CURSOR csr_annualization_factor
817 IS
818 SELECT ppb.pay_annualization_factor
819 FROM per_all_assignments_f paaf, per_pay_bases ppb
820 WHERE paaf.assignment_id = p_assignment_id
821 AND p_change_date BETWEEN paaf.effective_start_date
822 AND paaf.effective_end_date
823 AND ppb.pay_basis_id = paaf.pay_basis_id;
824 BEGIN
825 OPEN csr_annualization_factor;
826
827 FETCH csr_annualization_factor
828 INTO l_annualization_factor;
829
830 CLOSE csr_annualization_factor;
831
832 RETURN p_proposed_salary * l_annualization_factor;
833 END get_annual_salary;
834
835 FUNCTION get_currency (p_assignment_id IN NUMBER, p_change_date IN DATE)
836 RETURN VARCHAR2
837 IS
838 l_currency pay_element_types_f.input_currency_code%TYPE;
839
840 CURSOR currency
841 IS
842 SELECT pet.input_currency_code
843 FROM pay_element_types_f pet,
844 per_all_assignments_f paaf,
845 pay_input_values_f piv,
846 per_pay_bases ppb
847 WHERE paaf.assignment_id = p_assignment_id
848 AND p_change_date BETWEEN paaf.effective_start_date
849 AND paaf.effective_end_date
850 AND ppb.pay_basis_id = paaf.pay_basis_id
851 AND ppb.input_value_id = piv.input_value_id
852 AND p_change_date BETWEEN piv.effective_start_date
853 AND piv.effective_end_date
854 AND piv.element_type_id = pet.element_type_id
855 AND p_change_date BETWEEN pet.effective_start_date
856 AND pet.effective_end_date;
857
858 Cursor decode_Currency is
859 Select Name
860 from Fnd_Currencies_Vl
861 Where Currency_Code = l_currency;
862 -- and Enabled_Flag = 'Y'; -- 5579090
863
864 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
865
866
867 BEGIN
868 OPEN currency;
869
870 FETCH currency
871 INTO l_currency;
872
873 CLOSE currency;
874
875 If l_currency is Not Null then
876 open decode_Currency;
877 Fetch decode_Currency into P_Currency_name;
878 Close decode_Currency;
879 End If;
880
881 RETURN P_Currency_name;
882 END get_currency;
883
884 FUNCTION get_currency_format (p_assignment_id IN NUMBER, p_change_date IN DATE)
885 RETURN VARCHAR2
886 IS
887 l_currency pay_element_types_f.input_currency_code%TYPE;
888 l_uom varchar2(20);
889 l_five_curr pay_element_types_f.input_currency_code%TYPE;
890
891 CURSOR currency
892 IS
893 SELECT pet.input_currency_code,piv.uom
894 FROM pay_element_types_f pet,
895 per_all_assignments_f paaf,
896 pay_input_values_f piv,
897 per_pay_bases ppb
898 WHERE paaf.assignment_id = p_assignment_id
899 AND p_change_date BETWEEN paaf.effective_start_date
900 AND paaf.effective_end_date
901 AND ppb.pay_basis_id = paaf.pay_basis_id
902 AND ppb.input_value_id = piv.input_value_id
903 AND p_change_date BETWEEN piv.effective_start_date
904 AND piv.effective_end_date
905 AND piv.element_type_id = pet.element_type_id
906 AND p_change_date BETWEEN pet.effective_start_date
907 AND pet.effective_end_date;
908
909 Cursor five_curr is
910 Select CURRENCY_CODE
911 from Fnd_Currencies_Vl
912 Where PRECISION = 5
913 and rownum = 1;
914
915 P_Currency_name Fnd_Currencies_Vl.Name%TYPE := NULL;
916 BEGIN
917 OPEN currency;
918 FETCH currency INTO l_currency,l_uom;
919 CLOSE currency;
920
921 If l_uom <> 'M' then
922 OPEN five_curr;
923 FETCH five_curr INTO l_five_curr;
924 CLOSE five_curr;
925 l_currency := l_five_curr;
926 End If;
927
928 RETURN l_currency;
929 END get_currency_format;
930
931
932
933
934 FUNCTION get_pay_basis (p_assignment_id IN NUMBER, p_change_date IN DATE)
935 RETURN VARCHAR2
936 IS
937 l_pay_basis per_pay_bases.NAME%TYPE;
938
939 CURSOR csr_pay_basis
940 IS
941 SELECT ppb.NAME
942 FROM per_all_assignments_f paaf, per_pay_bases ppb
943 WHERE paaf.assignment_id = p_assignment_id
944 AND p_change_date BETWEEN paaf.effective_start_date
945 AND paaf.effective_end_date
946 AND ppb.pay_basis_id = paaf.pay_basis_id;
947 BEGIN
948 OPEN csr_pay_basis;
949
950 FETCH csr_pay_basis
951 INTO l_pay_basis;
952
953 CLOSE csr_pay_basis;
954
955 RETURN l_pay_basis;
956 END get_pay_basis;
957
958 FUNCTION get_change_amount (p_assignment_id IN NUMBER,
959 p_proposal_id IN NUMBER,
960 p_proposed_salary in number)
961 RETURN NUMBER
962 IS
963 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
964 BEGIN
965 l_previous_salary:= get_previous_salary(p_assignment_id,p_proposal_id);
966 if l_previous_salary = -1 then
967 return p_proposed_salary;
968 else
969 return p_proposed_salary - l_previous_salary;
970 end if;
971 END get_change_amount;
972
973 FUNCTION get_change_percent (p_assignment_id IN NUMBER,
974 p_proposal_id IN NUMBER,
975 p_proposed_salary in number)
976 RETURN NUMBER
977 is
978 l_change_amount number;
979 l_previous_salary per_pay_proposals.proposed_salary_n%TYPE;
980
981 BEGIN
982 l_change_amount:= get_change_amount (p_assignment_id ,p_proposal_id ,p_proposed_salary);
983 l_previous_salary :=get_previous_salary(p_assignment_id,p_proposal_id);
984 if l_previous_salary = -1 then
985 return null;
986 else
987 return round((l_change_amount*100/l_previous_salary),6);
988 end if;
989 END get_change_percent;
990
991 --
992 --
993 -- Procedure CHECK_LENGTH is used for rounding off the Salary according to currency format
994 -- This procedure calculate the new salary value for the changed pay basis.
995 --
996 --
997
998 PROCEDURE CHECK_LENGTH(p_amount IN OUT NOCOPY NUMBER
999 ,p_uom IN VARCHAR2
1000 ,p_currcode IN VARCHAR2) IS
1001
1002 L_PRECISION NUMBER;
1003 L_EXT_PRECISION NUMBER;
1004 L_MIN_ACCT_UNIT NUMBER;
1005
1006 BEGIN
1007 if(p_uom='M') then
1008 fnd_currency.get_info(currency_code => p_currcode
1009 ,precision => L_PRECISION
1010 ,EXT_PRECISION => L_EXT_PRECISION
1011 ,MIN_ACCT_UNIT => L_MIN_ACCT_UNIT);
1012 p_amount:=round(p_amount,l_precision);
1013 else
1014 p_amount:=round(p_amount,5);
1015 end if;
1016 END CHECK_LENGTH;
1017
1018 ------
1019 ----------called from core HR on Criteria change
1020 -------
1021 procedure handle_asg_crit_change
1022 (p_assignment_id in number,
1023 p_effective_date in date)
1024 is
1025
1026 l_creator_id number;
1027 l_proposal_rec per_pay_proposals%ROWTYPE;
1028 l_date_to date;
1029
1030 l_component_id per_pay_proposal_components.component_id%TYPE;
1031 l_object_version_number per_pay_proposal_components.object_version_number%TYPE;
1032 l_proposal_comp_rec per_pay_proposal_components%ROWTYPE;
1033
1034 l_pay_proposal_id NUMBER;
1035 l_ovn NUMBER;
1036 l_inv_next_sal_date_warning BOOLEAN;
1037 l_inv_next_perf_date_warning BOOLEAN;
1038 l_proposed_salary_warning BOOLEAN;
1039 l_approved_warning BOOLEAN;
1040 l_payroll_warning BOOLEAN;
1041 l_element_entry_id NUMBER;
1042
1043
1044 Cursor csr_creator_id is
1045 select CREATOR_ID,
1046 effective_end_date
1047 from pay_element_entries_f
1048 where ASSIGNMENT_ID=p_assignment_id
1049 and effective_start_date=p_effective_date
1050 and creator_type='SP';
1051
1052 cursor csr_pay_proposal(c_assignment_id in number,c_pay_proposal_id in number) is
1053 select * from per_pay_proposals
1054 where ASSIGNMENT_ID=c_assignment_id
1055 and pay_proposal_id=c_pay_proposal_id;
1056
1057 cursor csr_proposal_components(c_pay_proposal_id in number) is
1058 select * from per_pay_proposal_components
1059 where pay_proposal_id=c_pay_proposal_id;
1060
1061 begin
1062
1063 ---
1064 hr_utility.set_location('Entering PER_SALADMIN_UTILITY.handle_asg_crit_change ',1);
1065 --
1066 hr_utility.set_location('p_assignment_id ='||to_char(p_assignment_id),5);
1067 hr_utility.set_location('p_effective_date ='||p_effective_date,5);
1068 --get the element entry details that's created on p_effective_date
1069 --because of change in element link
1070 open csr_creator_id;
1071 fetch csr_creator_id into l_creator_id,l_date_to;
1072
1073 IF csr_creator_id%FOUND then
1074 hr_utility.set_location('l_creator_id ='||to_char(l_creator_id),10);
1075 ---get prev pay proposal details
1076 open csr_pay_proposal(p_assignment_id,l_creator_id);
1077 fetch csr_pay_proposal into l_proposal_rec;
1078
1079 ---if the pay proposal exists and has a different change date then
1080 ---create a new pay proposal
1081 if csr_pay_proposal%found and l_proposal_rec.change_date <> p_effective_date then
1082 --start of code change for bug 12591075
1083 if hr_api.not_exists_in_hr_lookups
1084 (p_effective_date => p_effective_date --Passing new proposal change date rather than prev proposal l_proposal_rec.change_date
1085 ,p_lookup_type => 'PROPOSAL_REASON'
1086 ,p_lookup_code => 'SALBASISCHG'
1087 ) then
1088 --proposal_reason doesn't exist - set the reason to null
1089 hr_utility.set_location('proposal_reason set to null', 25);
1090 l_proposal_rec.proposal_reason :=null;
1091 else
1092 hr_utility.set_location('proposal_reason set to SALBASISCHG', 27);
1093 l_proposal_rec.proposal_reason := 'SALBASISCHG';
1094 end if;
1095 --end of code change for bug 12591075
1096
1097 ----insert a new proposal and update the creator id
1098 hr_utility.set_location('Insert a new proposal:p_effective_date:'||p_effective_date,15);
1099
1100 hr_maintain_proposal_api.insert_salary_proposal
1101 (p_pay_proposal_id => l_pay_proposal_id
1102 ,p_assignment_id => p_assignment_id
1103 ,p_business_group_id => l_proposal_rec.business_group_id
1104 ,p_change_date => p_effective_date
1105 ,p_comments => l_proposal_rec.comments
1106 ,p_next_sal_review_date => l_proposal_rec.next_sal_review_date --changed as part of bug # 12591075
1107 ,p_proposal_reason => l_proposal_rec.proposal_reason
1108 ,p_proposed_salary_n => l_proposal_rec.proposed_salary_n
1109 ,p_forced_ranking => l_proposal_rec.forced_ranking
1110 ,p_performance_review_id => l_proposal_rec.performance_review_id
1111 ,p_attribute_category => l_proposal_rec.attribute_category
1112 ,p_attribute1 => l_proposal_rec.attribute1
1113 ,p_attribute2 => l_proposal_rec.attribute2
1114 ,p_attribute3 => l_proposal_rec.attribute3
1115 ,p_attribute4 => l_proposal_rec.attribute4
1116 ,p_attribute5 => l_proposal_rec.attribute5
1117 ,p_attribute6 => l_proposal_rec.attribute6
1118 ,p_attribute7 => l_proposal_rec.attribute7
1119 ,p_attribute8 => l_proposal_rec.attribute8
1120 ,p_attribute9 => l_proposal_rec.attribute9
1121 ,p_attribute10 => l_proposal_rec.attribute10
1122 ,p_attribute11 => l_proposal_rec.attribute11
1123 ,p_attribute12 => l_proposal_rec.attribute12
1124 ,p_attribute13 => l_proposal_rec.attribute13
1125 ,p_attribute14 => l_proposal_rec.attribute14
1126 ,p_attribute15 => l_proposal_rec.attribute15
1127 ,p_attribute16 => l_proposal_rec.attribute16
1128 ,p_attribute17 => l_proposal_rec.attribute17
1129 ,p_attribute18 => l_proposal_rec.attribute18
1130 ,p_attribute19 => l_proposal_rec.attribute19
1131 ,p_attribute20 => l_proposal_rec.attribute20
1132 ,p_object_version_number => l_ovn
1133 ,p_multiple_components => 'N' --changed as part of bug # 12591075
1134 ,p_approved => 'Y'
1135 ,p_validate => FALSE
1136 ,p_element_entry_id => l_element_entry_id
1137 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1138 ,p_proposed_salary_warning => l_proposed_salary_warning
1139 ,p_approved_warning => l_approved_warning
1140 ,p_payroll_warning => l_payroll_warning
1141 ,p_date_to => l_date_to
1142 );
1143 /* commented as part of bug # 12591075
1144 ---if the pay proposal is created then
1145 ---create components if they exit for prevous propsoal
1146 if l_pay_proposal_id is not null and l_proposal_rec.multiple_components='Y' then
1147
1148 open csr_proposal_components(l_proposal_rec.pay_proposal_id);
1149 loop
1150 fetch csr_proposal_components into l_proposal_comp_rec;
1151 exit when csr_proposal_components%notfound;
1152 ---------create components
1153 hr_maintain_proposal_api.insert_proposal_component(
1154 p_component_id => l_component_id
1155 ,p_pay_proposal_id => l_pay_proposal_id
1156 ,p_business_group_id => l_proposal_comp_rec.business_group_id
1157 ,p_approved => l_proposal_comp_rec.approved
1158 ,p_component_reason => l_proposal_comp_rec.component_reason
1159 ,p_change_amount_n => l_proposal_comp_rec.change_amount_n
1160 ,p_change_percentage => l_proposal_comp_rec.change_percentage
1161 ,p_comments => l_proposal_comp_rec.comments
1162 ,p_attribute_category => l_proposal_comp_rec.attribute_category
1163 ,p_attribute1 => l_proposal_comp_rec.attribute1
1164 ,p_attribute2 => l_proposal_comp_rec.attribute2
1165 ,p_attribute3 => l_proposal_comp_rec.attribute3
1166 ,p_attribute4 => l_proposal_comp_rec.attribute4
1167 ,p_attribute5 => l_proposal_comp_rec.attribute5
1168 ,p_attribute6 => l_proposal_comp_rec.attribute6
1169 ,p_attribute7 => l_proposal_comp_rec.attribute7
1170 ,p_attribute8 => l_proposal_comp_rec.attribute8
1171 ,p_attribute9 => l_proposal_comp_rec.attribute9
1172 ,p_attribute10 => l_proposal_comp_rec.attribute10
1173 ,p_attribute11 => l_proposal_comp_rec.attribute11
1174 ,p_attribute12 => l_proposal_comp_rec.attribute12
1175 ,p_attribute13 => l_proposal_comp_rec.attribute13
1176 ,p_attribute14 => l_proposal_comp_rec.attribute14
1177 ,p_attribute15 => l_proposal_comp_rec.attribute15
1178 ,p_attribute16 => l_proposal_comp_rec.attribute16
1179 ,p_attribute17 => l_proposal_comp_rec.attribute17
1180 ,p_attribute18 => l_proposal_comp_rec.attribute18
1181 ,p_attribute19 => l_proposal_comp_rec.attribute19
1182 ,p_attribute20 => l_proposal_comp_rec.attribute20
1183 ,p_validation_strength => null
1184 ,p_object_version_number => l_object_version_number
1185 ,p_validate => false
1186 );
1187 end loop;
1188 close csr_proposal_components;
1189
1190 end if; ---components exist
1191 */
1192 end if;----proposal to be created
1193
1194 close csr_pay_proposal; --bug#9222493
1195
1196 end if; ----element entry found
1197
1198
1199 close csr_creator_id;
1200
1201 --
1202 --
1203 hr_utility.set_location('Leaving PER_SALADMIN_UTILITY.handle_asg_crit_change ',100);
1204 --
1205 EXCEPTION
1206 WHEN others THEN
1207 --
1208 hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.handle_asg_crit_change',120);
1209 raise;
1210 --
1211 end;
1212
1213
1214 --
1215 --
1216 -- Procedure get_sal_on_basis_chg is called from the assignment form when the pay basis id is changed.
1217 -- This procedure calculate the new salary value for the changed pay basis.
1218 --
1219 --
1220 PROCEDURE get_sal_on_basis_chg
1221 (p_assignment_id in number,
1222 p_new_pay_basis_id in number,
1223 p_effective_date in date,
1224 p_old_pay_basis_id in number,
1225 p_curr_payroll_id in number)
1226 IS
1227
1228 l_change number;
1229 l_new number;
1230 l_percent number;
1231 l_comps VARCHAR2(1);
1232
1233 l_pay_basis VARCHAR2(30);
1234 l_old_currency VARCHAR2(15);
1235 l_salary_basis_name VARCHAR2(30);
1236 l_pay_basis_name VARCHAR2(80);
1237 l_old_pay_annualization_factor NUMBER;
1238 l_grade_annualization_factor NUMBER;
1239 l_grade VARCHAR2(240);
1240 l_prev_salary NUMBER;
1241 l_element_entry_id NUMBER;
1242 l_uom VARCHAR2(30);
1243
1244 l_prev_change_date DATE;
1245 l_prev_annual_salary number;
1246 --
1247 l_currency_rate NUMBER;
1248 l_cannual_amount NUMBER;
1249 l_new_pay_annual_factor NUMBER;
1250 l_new_currency VARCHAR2(15);
1251 l_new_annual_salary NUMBER;
1252 l_new_uom VARCHAR2(15);
1253 l_rec per_pay_proposals%ROWTYPE;
1254 --
1255 -- Cursor to get the pay proposal details
1256 --
1257 cursor get_prop_det (c_assignment_id in number,c_pay_basis_id in number) is
1258 select * from per_pay_proposals pro
1259 where pro.assignment_id = c_assignment_id
1260 and (p_effective_date - 1) between change_date and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy'));
1261 -- and pro.pay_proposal_id = c_pay_basis_id;
1262 --
1263 Cursor csr_new_pb_details(L_PAY_BASIS_ID in number) is
1264 SELECT PET.INPUT_CURRENCY_CODE
1265 , PPB.PAY_BASIS
1266 , PPB.PAY_ANNUALIZATION_FACTOR
1267 ,PIV.UOM
1268 FROM PAY_ELEMENT_TYPES_F PET
1269 , PAY_INPUT_VALUES_F PIV
1270 , PER_PAY_BASES PPB
1271 WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
1272 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
1273 AND p_effective_date BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
1274 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
1275 AND p_effective_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE;
1276 --
1277 Cursor csr_payroll_freq is
1278 select tpt.number_per_fiscal_year
1279 from pay_all_payrolls_f prl ,
1280 per_time_period_types tpt
1281 where prl.payroll_id = p_curr_payroll_id
1282 and p_effective_date between prl.effective_start_date and prl.effective_end_date
1283 and prl.period_type = tpt.period_type(+);
1284 --
1285 begin
1286 --
1287 hr_utility.set_location('Entering PER_SALADMIN_UTILITY.get_sal_on_basis_chg',10);
1288 --
1289 l_change:=null;
1290 l_new:=null;
1291 l_comps:='N'; --:REVIEW.MULTIPLE_COMPONENTS;
1292 l_percent:= 0;
1293 --
1294 -- Fetch the details of the old pay proposal
1295 --
1296 hr_utility.set_location('p_old_pay_basis_id ='||to_char(p_old_pay_basis_id),11);
1297 hr_utility.set_location('p_new_pay_basis_id ='||to_char(p_new_pay_basis_id),11);
1298 --
1299 open get_prop_det(p_assignment_id,p_old_pay_basis_id);
1300 fetch get_prop_det into l_rec;
1301 close get_prop_det;
1302 --
1303 -- Execute foll code only if a valid pay proposal exists of validation date.
1304 --
1305 If l_rec.pay_proposal_id is not null then
1306 --
1307 -- Get the old proposal date and salary
1308 --
1309 l_prev_change_date := l_rec.change_date +1;
1310 l_prev_salary := l_rec.proposed_salary_n;
1311 --
1312 hr_utility.set_location('Prev change date= '||to_char(l_prev_change_date,'dd/mm/yyyy'),12);
1313 hr_utility.set_location('l_prev_salary = '||to_char(l_prev_salary),13);
1314 --
1315 --Calculate the currency and pay_annualization_factor of the old salary basis
1316 --
1317 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS
1318 (p_effective_date => l_prev_change_date
1319 ,p_assignment_id => p_assignment_id
1320 ,p_currency => l_old_currency
1321 ,p_salary_basis_name => l_salary_basis_name
1322 ,p_pay_basis_name => l_pay_basis_name
1323 ,p_pay_basis => l_pay_basis
1324 ,p_pay_annualization_factor => l_old_pay_annualization_factor
1325 ,p_grade_basis => l_grade
1326 ,p_grade_annualization_factor => l_grade_annualization_factor
1327 ,p_element_type_id => l_element_entry_id
1328 ,p_uom => l_uom);
1329 hr_utility.set_location('l_old_currency = '||l_old_currency,13);
1330 hr_utility.set_location('l_salary_basis_name = '||l_salary_basis_name,13);
1331 hr_utility.set_location('l_pay_basis_name = '||l_pay_basis_name,13);
1332 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1333 hr_utility.set_location('l_old_pay_annualization_factor = '||to_char(l_old_pay_annualization_factor),13);
1334 hr_utility.set_location('l_grade = '||l_grade,13);
1335 hr_utility.set_location('l_grade_annualization_factor = '||to_char(l_grade_annualization_factor),13);
1336 hr_utility.set_location('l_element_entry_id = '||to_char(l_element_entry_id),13);
1337 hr_utility.set_location('l_uom = '||l_uom,13);
1338 --
1339 --
1340 --Calculate the currency and pay_annualization_factor of the new salary basis
1341 --
1342 Open csr_new_pb_details(p_new_pay_basis_id);
1343 Fetch csr_new_pb_details into l_new_currency,l_pay_basis,l_new_pay_annual_factor,l_new_uom;
1344 Close csr_new_pb_details;
1345 --
1346 hr_utility.set_location('l_new_currency = '||l_new_currency,13);
1347 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1348 hr_utility.set_location('l_new_pay_annual_factor = '||to_char(l_new_pay_annual_factor),13);
1349 --
1350 if(l_pay_basis ='PERIOD' and l_new_pay_annual_factor is null) then
1351 --
1352 hr_utility.set_location('Fetching Payroll frequency',13);
1353 --
1354 l_new_pay_annual_factor := 1;
1355 --
1356 open csr_payroll_freq;
1357 Fetch csr_payroll_freq into l_new_pay_annual_factor;
1358 Close csr_payroll_freq;
1359 end if;
1360 --
1361 hr_utility.set_location('l_new_pay_annual_factor = '||to_char(l_new_pay_annual_factor),13);
1362 /**
1363 PER_PAY_PROPOSALS_POPULATE.GET_BASIS_DETAILS
1364 (p_effective_date => p_effective_date
1365 ,p_assignment_id => p_assignment_id
1366 ,p_currency => l_new_currency
1367 ,p_salary_basis_name => l_salary_basis_name
1368 ,p_pay_basis_name => l_pay_basis_name
1369 ,p_pay_basis => l_pay_basis
1370 ,p_pay_annualization_factor => l_new_pay_annualization_factor
1371 ,p_grade_basis => l_grade
1372 ,p_grade_annualization_factor => l_grade_annualization_factor
1373 ,p_element_type_id => l_element_entry_id
1374 ,p_uom => l_uom);
1375 hr_utility.set_location('l_new_currency = '||l_new_currency,13);
1376 hr_utility.set_location('l_salary_basis_name = '||l_salary_basis_name,13);
1377 hr_utility.set_location('l_pay_basis_name = '||l_pay_basis_name,13);
1378 hr_utility.set_location('l_pay_basis = '||l_pay_basis,13);
1379 hr_utility.set_location('l_new_pay_annualization_factor = '||to_char(l_new_pay_annualization_factor),13);
1380 hr_utility.set_location('l_grade = '||l_grade,13);
1381 hr_utility.set_location('l_grade_annualization_factor = '||to_char(l_grade_annualization_factor),13);
1382 hr_utility.set_location('l_element_entry_id = '||to_char(l_element_entry_id),13);
1383 hr_utility.set_location('l_uom = '||l_uom,13);
1384 **/
1385 --
1386 --
1387 -- Calculate Old Annual amt and New Salary
1388 --
1389 if l_old_pay_annualization_factor = 0 then
1390 l_old_pay_annualization_factor := 1;
1391 end if;
1392
1393 if l_new_pay_annual_factor = 0 then
1394 l_new_pay_annual_factor := 1;
1395 end if;
1396
1397
1398
1399 l_prev_annual_salary := l_prev_salary * l_old_pay_annualization_factor;
1400 l_new := (l_prev_salary * l_old_pay_annualization_factor)/l_new_pay_annual_factor;
1401 --
1402 hr_utility.set_location('l_prev_annual_salary = '||to_char(l_prev_annual_salary),14);
1403 hr_utility.set_location('l_new = '||to_char(l_new),15);
1404 --
1405 -- If there is a new salary value and currencies of the old and new salary basis do not match, do a
1406 -- currency conversion of the new value.
1407 --
1408 l_currency_rate := 1;
1409 IF l_new IS NOT NULL THEN
1410 --
1411 if l_old_currency <> l_new_currency AND l_old_currency IS NOT NULL AND l_new_currency IS NOT NULL THEN
1412 --
1413 l_currency_rate := get_currency_rate(
1414 p_from_currency => l_old_currency,
1415 p_to_currency => l_new_currency,
1416 p_conversion_date => p_effective_date,
1417 p_business_group_id => l_rec.business_group_id);
1418 --
1419 End if;
1420 --
1421 End if;
1422 --
1423 hr_utility.set_location('Currency rate = '||to_char(l_currency_rate),16);
1424 l_new := l_currency_rate * l_new;
1425 hr_utility.set_location('Converted rate = '||to_char(l_new),20);
1426 --
1427 --
1428 if l_new_uom is null then
1429 l_new_uom := 'M';
1430 end if;
1431
1432 CHECK_LENGTH(l_new
1433 ,l_new_uom
1434 ,l_new_currency);
1435
1436 ----------------VKODEDAL 01-MAR-2007--------------------------------
1437 --FIX 5857948 -DEFAULT NEW PROPOSAL REASON TO SALARY BASIS CHANGE ADJUSTMENT
1438 --
1439 -- check that the p_proposal_reason exists in hr_lookups.
1440 --
1441 --Fix 6417656 - if SALBASISCHG doens't exist set the default reason to null
1442 if hr_api.not_exists_in_hr_lookups
1443 (p_effective_date => l_rec.change_date
1444 ,p_lookup_type => 'PROPOSAL_REASON'
1445 ,p_lookup_code => 'SALBASISCHG'
1446 ) then
1447 -- proposal_reason doesn't exist - set the reason to null
1448 hr_utility.set_location('proposal_reason set to null', 25);
1449 l_rec.proposal_reason :=null;
1450 else
1451 hr_utility.set_location('proposal_reason set to SALBASISCHG', 27);
1452 l_rec.proposal_reason := 'SALBASISCHG';
1453
1454 end if;
1455
1456
1457 ------------------------------------------------
1458 g_proposal_rec := l_rec;
1459 g_new_sal_value := l_new;
1460 --
1461 End if;
1462 --
1463 hr_utility.set_location('Leaving PER_SALADMIN_UTILITY.get_sal_on_basis_chg ',100);
1464 --
1465 EXCEPTION
1466 WHEN others THEN
1467 --
1468 hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.get_sal_on_basis_chg',100);
1469 g_proposal_rec := null;
1470 g_new_sal_value := null;
1471 raise;
1472 --
1473 end;
1474 --
1475 /* This procedure is called from the assignment form to insert the new salary value after a pay basis change */
1476 procedure insert_pay_proposal(p_assignment_id in number, p_validation_start_date in date) is
1477
1478 l_pay_proposal_id NUMBER;
1479 l_ovn NUMBER;
1480 l_inv_next_sal_date_warning BOOLEAN;
1481 l_inv_next_perf_date_warning BOOLEAN;
1482 l_proposed_salary_warning BOOLEAN;
1483 l_approved_warning BOOLEAN;
1484 l_payroll_warning BOOLEAN;
1485 l_approved VARCHAR2(1);
1486 l_element_entry_id NUMBER;
1487 l_review_date date;
1488
1489 begin
1490 if g_new_sal_value is not null then
1491
1492 hr_maintain_proposal_api.insert_salary_proposal
1493 (p_pay_proposal_id => l_pay_proposal_id
1494 ,p_assignment_id => p_assignment_id
1495 ,p_business_group_id => g_proposal_rec.business_group_id
1496 ,p_change_date => p_validation_start_date
1497 ,p_comments => g_proposal_rec.comments
1498 --,p_next_sal_review_date => l_review_date
1499 ,p_proposal_reason => g_proposal_rec.proposal_reason
1500 ,p_proposed_salary_n => g_new_sal_value
1501 ,p_forced_ranking => g_proposal_rec.forced_ranking
1502 ,p_performance_review_id => g_proposal_rec.performance_review_id
1503 ,p_attribute_category => g_proposal_rec.attribute_category
1504 ,p_attribute1 => g_proposal_rec.attribute1
1505 ,p_attribute2 => g_proposal_rec.attribute2
1506 ,p_attribute3 => g_proposal_rec.attribute3
1507 ,p_attribute4 => g_proposal_rec.attribute4
1508 ,p_attribute5 => g_proposal_rec.attribute5
1509 ,p_attribute6 => g_proposal_rec.attribute6
1510 ,p_attribute7 => g_proposal_rec.attribute7
1511 ,p_attribute8 => g_proposal_rec.attribute8
1512 ,p_attribute9 => g_proposal_rec.attribute9
1513 ,p_attribute10 => g_proposal_rec.attribute10
1514 ,p_attribute11 => g_proposal_rec.attribute11
1515 ,p_attribute12 => g_proposal_rec.attribute12
1516 ,p_attribute13 => g_proposal_rec.attribute13
1517 ,p_attribute14 => g_proposal_rec.attribute14
1518 ,p_attribute15 => g_proposal_rec.attribute15
1519 ,p_attribute16 => g_proposal_rec.attribute16
1520 ,p_attribute17 => g_proposal_rec.attribute17
1521 ,p_attribute18 => g_proposal_rec.attribute18
1522 ,p_attribute19 => g_proposal_rec.attribute19
1523 ,p_attribute20 => g_proposal_rec.attribute20
1524 ,p_object_version_number => l_ovn
1525 ,p_multiple_components => 'N' --g_proposal_rec.multiple_components bug: 8939934
1526 ,p_approved => 'Y'
1527 ,p_validate => FALSE
1528 ,p_element_entry_id => l_element_entry_id
1529 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1530 ,p_proposed_salary_warning => l_proposed_salary_warning
1531 ,p_approved_warning => l_approved_warning
1532 ,p_payroll_warning => l_payroll_warning
1533 ,p_date_to => g_proposal_rec.date_to -- bug: 9288586
1534 );
1535 end if;
1536
1537 g_proposal_rec := null;
1538 g_new_sal_value := null;
1539
1540 EXCEPTION
1541 WHEN others THEN
1542 hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.insert_pay_proposal',100);
1543 g_proposal_rec := null;
1544 g_new_sal_value := null;
1545 raise;
1546 end;
1547
1548 function get_currency_rate(
1549 p_from_currency VARCHAR2,
1550 p_to_currency VARCHAR2,
1551 p_conversion_date DATE,
1552 p_business_group_id number) return number is
1553 l_conversion_date date;
1554 l_conversion_type varchar2(80);
1555 l_currency_rate number := 1;
1556 begin
1557 if (p_conversion_date is not null) then
1558 l_conversion_date := p_conversion_date;
1559 else
1560 l_conversion_date := sysdate;
1561 end if;
1562
1563 l_conversion_type := hr_currency_pkg.get_rate_type
1564 (p_business_group_id,p_conversion_date,'P');
1565 if (l_conversion_type is not null) then
1566 l_currency_rate :=
1567 hr_currency_pkg.get_rate_sql
1568 (p_from_currency,
1569 p_to_currency ,
1570 l_conversion_date,
1571 l_conversion_type
1572 );
1573 If l_currency_rate < 0 then
1574 l_currency_rate := 1;
1575 End if;
1576
1577 end if;
1578 --
1579 return l_currency_rate;
1580 end;
1581 -- --------------------------------------------------------------------------
1582 -- |---------------------------< get_fte_factor >---------------------------|
1583 -- --------------------------------------------------------------------------
1584 FUNCTION get_fte_factor(p_assignment_id IN NUMBER
1585 ,p_effective_date IN DATE)
1586 return NUMBER IS
1587
1588 l_fte_profile_value VARCHAR2(240) := fnd_profile.VALUE('BEN_CWB_FTE_FACTOR');
1589 l_fte_precision_value NUMBER;
1590
1591 CURSOR csr_fte_BFTE
1592 IS
1593 select nvl(value, 1) val
1594 from per_assignment_budget_values_f
1595 where assignment_id = p_assignment_id
1596 and unit = 'FTE'
1597 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
1598 CURSOR csr_fte_BPFT
1599 IS
1600 select nvl(value, 1) val
1601 from per_assignment_budget_values_f
1602 where assignment_id = p_assignment_id
1603 and unit = 'PFT'
1604 and p_effective_date BETWEEN effective_start_date AND effective_end_date;
1605
1606 l_fte_factor number := null;
1607 l_norm_hours_per_year number;
1608 l_hours_per_year number;
1609 BEGIN
1610 --Bug 8226280 smadhuna FTE Factor Precision profile to be used for rounding
1611 BEGIN
1612
1613 l_fte_precision_value := fnd_profile.VALUE('HR_FTE_PRECISION');
1614
1615 IF l_fte_precision_value NOT IN ( 1,2,3,4,5,6,7,8,9 ) THEN
1616 l_fte_precision_value := NULL;
1617 END IF;
1618
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 l_fte_precision_value := NULL;
1622 END;
1623 --Bug 8226280
1624 if (l_fte_profile_value = 'NHBGWH') then
1625
1626 PER_PAY_PROPOSALS_POPULATE.get_asg_hours(p_assignment_id
1627 ,p_effective_date
1628 ,l_hours_per_year);
1629
1630 if(nvl(l_hours_per_year,0) <> 0) then
1631 PER_PAY_PROPOSALS_POPULATE.get_norm_hours(p_assignment_id
1632 ,p_effective_date
1633 ,l_norm_hours_per_year);
1634 if ( nvl(l_norm_hours_per_year,0) = 0) then
1635 l_fte_factor := 1;
1636 else
1637 l_fte_factor := l_hours_per_year/l_norm_hours_per_year;
1638 end if;
1639 else
1640 l_fte_factor := 1;
1641 end if;
1642 elsif (l_fte_profile_value = 'BFTE') then
1643 for r1 in csr_fte_BFTE loop
1644 l_fte_factor := r1.val;
1645 end loop;
1646 elsif (l_fte_profile_value = 'BPFT') then
1647 for r1 in csr_fte_BPFT loop
1648 l_fte_factor := r1.val;
1649 end loop;
1650 else
1651 l_fte_factor := 1;
1652 end if;
1653 -- fte can be greater than 1 Bug#7497075 schowdhu
1654 -- if (l_fte_factor is null or l_fte_factor > 1) then
1655 if (l_fte_factor is null) then
1656 l_fte_factor := 1;
1657 end if;
1658
1659 --Bug 8226280
1660 IF l_fte_precision_value IS NOT NULL THEN
1661 l_fte_factor := ROUND( l_fte_factor, l_fte_precision_value);
1662 END IF;
1663 --Bug 8226280
1664
1665 RETURN l_fte_factor;
1666
1667 END get_fte_factor;
1668
1669 Function asg_pay_proposal_starts_at(p_assignment_id IN NUMBER, p_date in date)
1670 return varchar2 is
1671 l_dummy varchar2(10);
1672 CURSOR c_pay_proposals is
1673 select null
1674 from per_pay_proposals pro
1675 where pro.assignment_id = p_assignment_id
1676 and pro.change_date = p_date;
1677 begin
1678 OPEN c_pay_proposals;
1679 FETCH c_pay_proposals into l_dummy;
1680 IF c_pay_proposals%FOUND then
1681 CLOSE c_pay_proposals;
1682 return('Y');
1683 end if;
1684 CLOSE c_pay_proposals;
1685 return ('N');
1686 end asg_pay_proposal_starts_at;
1687
1688 Function get_initial_proposal_start(p_assignment_id IN NUMBER)
1689 return date is
1690 l_min_change_date date;
1691 CURSOR c_pay_proposals is
1692 select min(change_date)
1693 from per_pay_proposals pro
1694 where pro.assignment_id = p_assignment_id;
1695 begin
1696 OPEN c_pay_proposals;
1697 FETCH c_pay_proposals into l_min_change_date;
1698 IF c_pay_proposals%FOUND then
1699 CLOSE c_pay_proposals;
1700 return(l_min_change_date);
1701 end if;
1702 CLOSE c_pay_proposals;
1703 return (null);
1704 end get_initial_proposal_start;
1705
1706 function get_assignment_fte(p_assignment_id number, p_effective_date date) return number is
1707 l_fte number := 0;
1708 cursor c1(p_assignment_id number, p_effective_date date) is
1709 select nvl(abv.value,0)
1710 from per_assignment_budget_values_f abv, per_all_assignments_f asg,
1711 per_assignment_status_types ast
1712 where asg.assignment_id = p_assignment_id
1713 and abv.assignment_id = asg.assignment_id
1714 and asg.assignment_type in ('E', 'C')
1715 and abv.unit = 'FTE'
1716 and p_effective_date between asg.effective_start_date and asg.effective_end_date
1717 and p_effective_date between abv.effective_start_date and abv.effective_end_date
1718 and asg.assignment_status_type_id = ast.assignment_status_type_id
1719 and ast.per_system_status <> 'TERM_ASSIGN';
1720 --
1721 begin
1722 open c1(p_assignment_id, p_effective_date);
1723 fetch c1 into l_fte;
1724 close c1;
1725 return l_fte;
1726 end;
1727
1728 FUNCTION get_basis_lookup (p_assignment_id IN NUMBER, p_change_date IN DATE)
1729 RETURN VARCHAR2
1730 IS
1731 l_pay_basis per_pay_bases.PAY_BASIS%TYPE;
1732
1733 CURSOR csr_pay_basis
1734 IS
1735 SELECT ppb.Pay_basis
1736 FROM per_all_assignments_f paaf, per_pay_bases ppb
1737 WHERE paaf.assignment_id = p_assignment_id
1738 AND p_change_date BETWEEN paaf.effective_start_date
1739 AND paaf.effective_end_date
1740 AND ppb.pay_basis_id = paaf.pay_basis_id;
1741 BEGIN
1742 OPEN csr_pay_basis;
1743
1744 FETCH csr_pay_basis
1745 INTO l_pay_basis;
1746
1747 CLOSE csr_pay_basis;
1748
1749 RETURN l_pay_basis;
1750 END get_basis_lookup;
1751
1752 Function get_next_sal_basis_chg_dt(p_assignment_id IN NUMBER, p_from_date IN DATE)
1753 return date is
1754 l_next_sb_date date;
1755 CURSOR c_next_sb_date(p_assignment_id IN NUMBER, p_from_date IN DATE) is
1756 select min(effective_start_date)
1757 from per_all_assignments_f
1758 where assignment_id = p_assignment_id
1759 and effective_start_date > p_from_date
1760 and pay_basis_id not in
1761 (select pay_basis_id
1762 from per_all_assignments_f
1763 where assignment_id = p_assignment_id
1764 and p_from_date
1765 between effective_start_date and effective_end_date );
1766 begin
1767 OPEN c_next_sb_date(p_assignment_id, p_from_date);
1768 FETCH c_next_sb_date into l_next_sb_date;
1769 IF c_next_sb_date%FOUND then
1770 CLOSE c_next_sb_date;
1771 return(l_next_sb_date);
1772 end if;
1773 CLOSE c_next_sb_date;
1774 return (null);
1775 end get_next_sal_basis_chg_dt;
1776
1777 FUNCTION get_pay_basis_id(p_assignment_id IN NUMBER, p_from_date IN DATE)
1778 RETURN NUMBER
1779 IS
1780 l_pay_basis_id number;
1781
1782 CURSOR c_pay_basis_id is
1783 select pay_basis_id
1784 from per_all_assignments_f
1785 where assignment_id = p_assignment_id
1786 and p_from_date
1787 between effective_start_date and effective_end_date;
1788 BEGIN
1789 OPEN c_pay_basis_id;
1790 FETCH c_pay_basis_id INTO l_pay_basis_id;
1791 CLOSE c_pay_basis_id;
1792
1793 RETURN l_pay_basis_id;
1794 END get_pay_basis_id;
1795
1796 Function get_asg_sal_basis_end_dt(p_assignment_id IN NUMBER, p_from_date IN DATE)
1797 return date is
1798 l_asg_sb_end_date date;
1799 l_pay_basis_id number;
1800
1801 CURSOR c_asg_sal_basis_end_dt(p_assignment_id IN NUMBER,
1802 p_pay_basis_id IN NUMBER,
1803 p_from_date IN DATE) is
1804 select min(effective_start_date) - 1
1805 from per_all_assignments_f
1806 where assignment_id = p_assignment_id
1807 and effective_start_date >= p_from_date
1808 and nvl(pay_basis_id,-1) <> p_pay_basis_id;
1809 begin
1810 l_pay_basis_id := get_pay_basis_id(p_assignment_id, p_from_date);
1811
1812 OPEN c_asg_sal_basis_end_dt(p_assignment_id, l_pay_basis_id, p_from_date);
1813 FETCH c_asg_sal_basis_end_dt into l_asg_sb_end_date;
1814 IF c_asg_sal_basis_end_dt%FOUND then
1815 CLOSE c_asg_sal_basis_end_dt;
1816 return(l_asg_sb_end_date);
1817 end if;
1818 CLOSE c_asg_sal_basis_end_dt;
1819 return (null);
1820 end get_asg_sal_basis_end_dt;
1821
1822 END PER_SALADMIN_UTILITY;
1823
[Home] [Help]