[Home] [Help]
PACKAGE BODY: APPS.PQH_EMPLOYEE_SALARY
Source
1 Package Body pqh_employee_salary as
2 /* $Header: pqempsal.pkb 120.2.12020000.2 2012/08/22 10:13:27 lbodired ship $ */
3 --
4 /**** Please find below the meaning for the status returned
5 p_status = 1 : Success
6 p_status = -1 : Invalid Assignment
7 p_status = 2 : No Payroll for assignment.
8 p_status = 3 : No Salary basis and No Grade Ladder Element.
9 --
10 p_pay_basis = 'Y' : Salary determined from salary basis element
11 p_pay_basis = 'N' : Salary determined from Grade Ladder element
12 ****/
13
14 Procedure get_employee_salary
15 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
16 P_Effective_Date In Date,
17 p_salary OUT nocopy number,
18 p_frequency OUT nocopy varchar2,
19 p_annual_salary OUT nocopy number,
20 p_pay_basis OUT nocopy varchar2,
21 p_reason_cd OUT nocopy varchar2,
22 p_currency OUT nocopy varchar2,
23 p_status OUT nocopy number,
24 p_pay_basis_frequency OUT nocopy varchar2
25 ) IS
26 --
27 L_Cur_Sal Per_pay_Proposals.PROPOSED_SALARY_N%TYPE;
28 l_input_value_id pay_input_values_f.Input_Value_id%TYPE;
29 l_dummy_iv_id pay_input_values_f.Input_Value_id%TYPE;
30 l_business_group_id Per_All_Assignments_f.business_group_id%type;
31 --
32 l_pay_basis_id Per_All_Assignments_f.pay_basis_id%type;
33 l_pgm_id Per_All_Assignments_f.GRADE_LADDER_PGM_ID%type;
34 l_payroll_id Per_All_Assignments_f.payroll_id%type;
35 l_basis_Annl_fctr Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
36 l_precision Fnd_Currencies.Precision%type;
37 L_Salary pay_element_entry_values_f.screen_entry_value%TYPE;
38 --
39 --
40 l_proc varchar2(72);
41 --
42 Cursor csr_asg_details is
43 Select paf.pay_basis_id, paf.GRADE_LADDER_PGM_ID, paf.payroll_id,paf.business_group_id
44 From Per_All_Assignments_f paf
45 Where paf.Assignment_Id = p_Assignment_Id
46 and p_Effective_Date
47 Between Paf.Effective_Start_Date and Paf.Effective_End_Date;
48 --
49 Cursor Sal is
50 Select pev.screen_entry_value,pet.INPUT_CURRENCY_CODE
51 From pay_element_entries_f pee,
52 pay_input_values_f piv,
53 pay_element_entry_values_f pev,
54 pay_element_types_f pet
55 Where pee.Assignment_id = P_Assignment_id
56 and P_Effective_Date
57 between pee.Effective_Start_Date and pee.Effective_End_Date
58 and Piv.Input_Value_id = l_Input_Value_id
59 and P_Effective_Date
60 Between Piv.Effective_Start_Date and Piv.Effective_End_Date
61 and pev.ELEMENT_ENTRY_ID = Pee.ELEMENT_ENTRY_ID
62 and Piv.INPUT_VALUE_ID = Pev.INPUT_VALUE_ID
63 and P_Effective_Date
64 Between Pev.Effective_Start_Date and Pev.Effective_End_Date
65 and pet.element_type_id = piv.element_type_id
66 and p_effective_date
67 Between pet.effective_start_date and pet.effective_end_date;
68 --
69 Cursor Pay_Bases_Element is
70 Select input_value_id, Pay_Annualization_Factor,pay_basis
71 From Per_Pay_Bases paf
72 Where paf.pay_basis_id = l_pay_basis_id;
73 --
74 Cursor GrdLdr_Element is
75 Select DFLT_INPUT_VALUE_ID
76 from Ben_Pgm_f pgm
77 Where pgm.pgm_id = l_pgm_id
78 and p_Effective_Date
79 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
80 --
81 Cursor csr_ann_sal (p_sal in number, p_payroll_id in number) is
82 Select pr.period_type, (p_sal * pt.number_per_fiscal_year) annual_salary
83 From per_time_period_types pt, pay_all_payrolls_f pr
84 Where pr.payroll_id = p_payroll_id
85 and p_Effective_Date
86 Between pr.Effective_Start_date and pr.Effective_End_Date
87 and pr.period_type = pt.period_type;
88 --
89 Cursor csr_proposal_rsn is
90 select proposal_reason
91 from per_pay_proposals
92 where assignment_id = p_assignment_id
93 and change_date = (Select max(change_date)
94 From per_pay_proposals
95 where assignment_id = p_assignment_id
96 and change_date <= p_Effective_Date);
97 --
98 Cursor csr_cur is
99 Select Nvl(Cur.Precision,2)
100 From Fnd_Currencies Cur
101 Where Cur.Currency_Code = P_currency;
102
103 Begin
104 --
105 l_proc := 'get_employee_salary';
106 hr_utility.set_location('Entering:'||l_proc, 5);
107 --
108 --
109 -- Step 1. Fetch Assignment Details
110 --
111 Open csr_asg_details;
112 Fetch csr_asg_details into l_pay_basis_id, l_pgm_id, l_payroll_id,l_business_group_id;
113 If csr_asg_details%found then
114 --
115 -- Step 2: If the Assignment is attached to a Salary basis, then find the Salary Basis Element.
116 --
117 p_pay_basis := 'N';
118 p_pay_basis_frequency := null;
119 If l_pay_basis_id IS NOT NULL then
120 --
121 --
122 hr_utility.set_location('Assignment has Salary Basis', 110);
123 --
124 Open Pay_Bases_Element;
125 Fetch Pay_Bases_Element into l_input_Value_id, l_basis_Annl_fctr, p_pay_basis_frequency;
126 Close Pay_Bases_Element;
127 --
128 -- Get pay proposal reason
129 --
130 Open csr_proposal_rsn;
131 Fetch csr_proposal_rsn into p_reason_cd;
132 Close csr_proposal_rsn;
133
134 --
135 p_pay_basis := 'Y';
136 --
137 /*
138
139 --
140 -- Get currency
141 --
142 If l_pgm_id is NOT NULL then
143 --
144 -- Get grade ladder currency
145 --
146 --
147 hr_utility.set_location('Get currency from grade ladder', 120);
148 --
149 Open GrdLdr_Element;
150 Fetch GrdLdr_Element into l_dummy_iv_id;
151 Close GrdLdr_Element;
152 --
153 Else
154 --
155 -- Assignment is on salary basis but not on a GL.
156 -- Get currency from BG ?
157 --
158 --
159 hr_utility.set_location('Get currency from business group', 130);
160 --
161 p_currency := hr_general.DEFAULT_CURRENCY_CODE(p_business_group_id => l_business_group_id);
162 --
163 End if;
164 --
165 -- Set p_pay_basis OUT paremeter to indicate that the salary is from pay basis element.
166 --
167 p_pay_basis := 'Y';*/
168 --
169 Else
170 --
171 -- Step 3: If Assignment Has no salary basis, then get the grade ladder element,
172 -- provided the assignment is on a grade ladder.
173 --
174 If l_pgm_id is NOT NULL then
175 --
176 --
177 hr_utility.set_location('Fetch Grade Ladder Element', 140);
178 --
179 Open GrdLdr_Element;
180 Fetch GrdLdr_Element into l_input_Value_id;
181 Close GrdLdr_Element;
182 --
183 Else
184 --
185 -- p_status = 3 : No Salary basis and No Grade Ladder Element.
186 --
187 --
188 hr_utility.set_location('No Salary basis and No Grade Ladder', 150);
189 --
190 p_status := 3;
191 p_salary := 0;
192 p_annual_salary := 0;
193 --
194 End if;
195 --
196 End if;
197 --
198 -- Step 4: Find the rate for the salary element.
199 --
200 Open csr_cur;
201 Fetch Csr_Cur into l_precision;
202 Close Csr_Cur;
203
204 If l_precision is NULL then
205 l_precision := 2;
206 End If;
207
208 if l_Input_Value_id is Not NULL Then
209 Open Sal;
210 Fetch Sal into l_salary,p_currency;
211 Close Sal;
212 p_salary:=fnd_number.canonical_to_number(l_salary);
213 --
214 -- Step 5: Find the payroll frequency and the annual salary.
215 --
216 If l_payroll_id IS NOT NULL then
217 --
218 --
219 hr_utility.set_location('Computing annual salary for assignment.', 160);
220
221 --
222 Open csr_ann_sal(nvl(p_salary,0),l_payroll_id);
223 Fetch csr_ann_sal into p_frequency, p_annual_salary;
224 Close csr_ann_sal;
225
226 If p_pay_basis = 'Y' then
227 if (l_basis_Annl_fctr is null or l_basis_Annl_fctr = 0 ) then
228 l_basis_Annl_fctr := 1;
229 end if ;
230 p_annual_salary := nvl(p_salary,0) * l_basis_Annl_fctr;
231 End If;
232
233 --sqlplus
234 -- p_status = 1 : Success
235 --
236
237 p_status := 1;
238 --
239 Else
240 --
241 -- p_status = 2 : No Payroll for assignment.
242 --
243 --
244 hr_utility.set_location('Null Payroll for assignment.', 170);
245 --
246 p_status := 2;
247 p_annual_salary := p_salary;
248
249 --
250 End if;
251 --
252 Else
253 --
254 -- p_status = 3 : No Salary basis and No Grade Ladder Element.
255 --
256 --
257 hr_utility.set_location('Null Input Value Id', 180);
258 --
259 p_status := 3;
260 p_salary := 0;
261 p_annual_salary := 0;
262 End If;
263 --
264 Else
265 --
266 -- p_status = -1 : Invalid Assignment
267 --
268 p_status := -1;
269 --
270 --
271 hr_utility.set_location('Invalid Assignment id', 190);
272 --
273 End if;
274 p_annual_salary := trunc(p_annual_salary,l_precision);
275 Close csr_asg_details;
276 --
277 --
278 hr_utility.set_location('Leaving:'||l_proc, 10);
279 --
280 Exception When others then
281 --
282 hr_utility.set_location('Exception:'||l_proc, 200);
283 raise;
284 --
285 End get_employee_salary;
286 --
287 ---------------------------------------------------------------------------------------------------------------
288 --
289 -- Follwing procedure returns true if there is a grade ladder setup in the
290 -- business group.
291 --
292 Procedure check_grade_ladder_exists(p_business_group_id in number,
293 p_effective_date in date ,
294 p_grd_ldr_exists_flag out nocopy boolean)
295 is
296 --
297 Cursor csr_gsp is
298 Select 'x'
299 from ben_pgm_f
300 Where business_group_id = p_business_group_id
301 and pgm_typ_cd = 'GSP'
302 and p_effective_date between effective_start_date and effective_end_date;
303 --
304 l_dummy varchar2(10);
305 l_proc varchar2(72);
306 --
307 Begin
308 --
309 l_proc := 'check_grade_ladder_exists';
310 hr_utility.set_location('Entering:'||l_proc, 5);
311 --
312 Open csr_gsp;
313 Fetch csr_gsp into l_dummy;
314 If csr_gsp%notfound then
315 p_grd_ldr_exists_flag := false;
316 hr_utility.set_location('No grade ladder', 7);
317 End if;
318 close csr_gsp;
319 --
320 p_grd_ldr_exists_flag := true;
321 hr_utility.set_location('Leaving:'||l_proc, 10);
322 --
323 --
324 Exception When others then
325 --
326 hr_utility.set_location('Exception:'||l_proc, 200);
327 raise;
328 --
329 End check_grade_ladder_exists;
330 --
331 --
332 -- Follwing procedure returns 'Y' if there is a grade ladder setup in the
333 -- business group.
334 --
335 Procedure check_grade_ladder_exists(p_business_group_id in number,
336 p_effective_date in date,
337 p_grd_ldr_exists_flag out nocopy varchar2)
338 is
339 --
340 --
341 l_status boolean;
342 l_proc varchar2(72);
343 --
344 Begin
345 --
346 l_proc := 'check_grade_ladder_exists';
347 l_status := false;
348 hr_utility.set_location('Entering:'||l_proc, 5);
349 --
350 check_grade_ladder_exists
351 (p_business_group_id => p_business_group_id,
352 p_effective_date => p_effective_date,
353 p_grd_ldr_exists_flag => l_status);
354 If l_status then
355 p_grd_ldr_exists_flag:= 'Y';
356 hr_utility.set_location('Found Grade ladder', 7);
357 End if;
358 --
359 p_grd_ldr_exists_flag:= 'N';
360 hr_utility.set_location('Leaving:'||l_proc, 10);
361 --
362 --
363 Exception When others then
364 --
365 hr_utility.set_location('Exception:'||l_proc, 200);
366 raise;
367 --
368 End check_grade_ladder_exists;
369 --
370 -------------------------------------------------------------------------------------------------------------------
371 Function pgm_to_annual (p_ref_perd_cd in varchar2,
372 p_pgm_currency in varchar2,
373 p_amount in number)
374 RETURN NUMBER is
375 --
376 Cursor csr_cur is
377 Select Nvl(Cur.Precision,2)
378 From Fnd_Currencies Cur
379 Where Cur.Currency_Code = p_pgm_currency;
380 --
381 l_ret_amount number;
382 l_precision Fnd_Currencies.Precision%type;
383 l_pay_annualization_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
384 l_proc varchar2(72);
385 --
386 Begin
387 --
388 l_precision := 2;
389 l_proc := 'pgm_to_annual';
390 hr_utility.set_location('Entering:'||l_proc, 10);
391 --
392 Open csr_cur;
393 Fetch csr_cur into l_precision;
394 Close csr_cur;
395 --
396 IF p_ref_perd_cd = 'PWK' THEN
397 l_ret_amount := (p_amount*52);
398 ELSIF p_ref_perd_cd = 'BWK' THEN
399 l_ret_amount := (p_amount*26);
400 ELSIF p_ref_perd_cd = 'SMO' THEN
401 l_ret_amount := (p_amount*24);
402 ELSIF p_ref_perd_cd = 'PQU' THEN
403 l_ret_amount := (p_amount*4);
404 ELSIF p_ref_perd_cd = 'PYR' THEN
405 l_ret_amount := (p_amount*1);
406 ELSIF p_ref_perd_cd = 'SAN' THEN
407 l_ret_amount := (p_amount*2);
408 ELSIF p_ref_perd_cd = 'MO' THEN
409 l_ret_amount := (p_amount*12);
410 ELSIF p_ref_perd_cd = 'NOVAL' THEN
411 l_ret_amount := (p_amount*1);
412 ELSIF p_ref_perd_cd = 'PHR' then
413 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
414 If l_pay_annualization_factor is null then
415 l_pay_annualization_factor := 2080;
416 End if;
417 --
418 l_ret_amount := (p_amount * l_pay_annualization_factor);
419 --
420 ELSE
421 l_ret_amount := p_amount;
422 END IF;
423 ---
424 RETURN trunc(l_ret_amount,l_precision);
425 --
426 hr_utility.set_location('Leaving:'||l_proc, 10);
427 --
428 End pgm_to_annual;
429 --
430 -----------------------------------------------------------------------------------------------------------------
431
432 --Fix for the bug 12613915
433 ---------------------------------------------------------------------------------------------------------------
434
435 Function pgm_to_annual1 (p_ref_perd_cd in varchar2,
436 p_pgm_currency in varchar2,
437 p_amount in number,
438 P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
439 P_Effective_Date In Date)
440 RETURN NUMBER is
441 --
442 Cursor csr_cur is
443 Select Nvl(Cur.Precision,2)
444 From Fnd_Currencies Cur
445 Where Cur.Currency_Code = p_pgm_currency;
446 --
447 l_ret_amount number;
448 l_precision Fnd_Currencies.Precision%type;
449 l_pay_annualization_factor Per_Pay_Bases.Pay_Annualization_Factor%TYPE;
450 l_proc varchar2(72);
451
452 l_pay_basis_id number;
453
454 --
455 Begin
456 --
457 l_precision := 2;
458 l_proc := 'pgm_to_annual';
459 hr_utility.set_location('Entering:'||l_proc, 10);
460 --
461 Open csr_cur;
462 Fetch csr_cur into l_precision;
463 Close csr_cur;
464 --
465 IF p_ref_perd_cd = 'PWK' THEN
466 l_ret_amount := (p_amount*52);
467 ELSIF p_ref_perd_cd = 'BWK' THEN
468 l_ret_amount := (p_amount*26);
469 ELSIF p_ref_perd_cd = 'SMO' THEN
470 l_ret_amount := (p_amount*24);
471 ELSIF p_ref_perd_cd = 'PQU' THEN
472 l_ret_amount := (p_amount*4);
473 ELSIF p_ref_perd_cd = 'PYR' THEN
474 l_ret_amount := (p_amount*1);
475 ELSIF p_ref_perd_cd = 'SAN' THEN
476 l_ret_amount := (p_amount*2);
477 ELSIF p_ref_perd_cd = 'MO' THEN
478 l_ret_amount := (p_amount*12);
479 ELSIF p_ref_perd_cd = 'NOVAL' THEN
480 l_ret_amount := (p_amount*1);
481 ELSIF p_ref_perd_cd = 'PHR' then
482 l_pay_annualization_factor := to_number(fnd_profile.value('BEN_HRLY_ANAL_FCTR'));
483 If l_pay_annualization_factor is null then
484 -- l_pay_annualization_factor := 2080;
485 select PAY_BASIS_ID into l_pay_basis_id
486 from per_all_assignments_f paf
487 where ASSIGNMENT_ID = P_Assignment_id
488 and P_Effective_Date
489 Between Paf.Effective_Start_Date and Paf.Effective_End_Date;
490
491 select PAY_ANNUALIZATION_FACTOR into l_PAY_ANNUALIZATION_FACTOR
492 from per_pay_bases ppb
493 where PAY_BASIS_ID = l_pay_basis_id;
494 End if;
495 --
496 l_ret_amount := (p_amount * l_pay_annualization_factor);
497 --
498 ELSE
499 l_ret_amount := p_amount;
500 END IF;
501 ---
502 RETURN trunc(l_ret_amount,l_precision);
503 --
504 hr_utility.set_location('Leaving:'||l_proc, 10);
505 --
506 End pgm_to_annual1;
507
508 -----------------------------------------------------------------------------------------------------------------
509 --Fix for the bug 12613915
510 -- The following procedure returns the salary change caused by a grade or step change on the assignment
511 --
512 -- p_status = 1 : Success. Proposed salary value will be returned only for this status.
513 -- p_status = 2 : No life event
514 -- p_status = 3 : No default enrt
515 -- p_status = 4 : GL not using salary update
516 -- p_status = 5 : Assignment not on GL
517 --
518 Procedure get_emp_proposed_salary
519 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
520 P_Effective_Date In Date,
521 p_proposed_salary OUT nocopy number,
522 p_sal_chg_dt OUT nocopy date,
523 p_frequency OUT nocopy varchar2,
524 p_annual_salary OUT nocopy number,
525 p_pay_basis OUT nocopy varchar2,
526 p_reason_cd OUT nocopy varchar2,
527 p_currency OUT nocopy varchar2,
528 p_status OUT nocopy number
529 ) IS
530 --
531 L_Cur_Sal Ben_Enrt_Rt.val%type;
532 l_update_sal_cd ben_pgm_f.update_salary_cd%type;
533 --
534 l_pay_basis_id Per_All_Assignments_f.pay_basis_id%type;
535 l_pgm_id Per_All_Assignments_f.GRADE_LADDER_PGM_ID%type;
536 l_person_id Per_All_Assignments_f.person_id%type;
537 l_payroll_id Per_All_Assignments_f.payroll_id%type;
538 --
539 l_pgm_frequency Ben_Pgm_f.ACTY_REF_PERD_CD%type;
540 l_per_in_ler_id Ben_Per_in_Ler.per_in_ler_id%type;
541 --
542 l_continue boolean;
543 l_proc varchar2(72);
544 --
545 Cursor csr_asg_details is
546 Select paf.pay_basis_id, paf.GRADE_LADDER_PGM_ID, paf.person_id
547 From Per_All_Assignments_f paf
548 Where paf.Assignment_Id = p_Assignment_Id
549 and p_Effective_Date
550 Between Paf.Effective_Start_Date and Paf.Effective_End_Date;
551 --
552 Cursor csr_GrdLdr is
553 Select ACTY_REF_PERD_CD,pgm_uom,Update_Salary_Cd
554 from Ben_Pgm_f pgm
555 Where pgm.pgm_id = l_pgm_id
556 and p_Effective_Date
557 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
558 --
559 Cursor csr_le is
560 Select max(pil.Per_in_Ler_Id)
561 From Ben_Per_in_ler PIL,
562 Ben_Ler_F LER
563 Where Pil.Ler_Id = LER.Ler_Id
564 And Pil.LF_EVT_OCRD_DT = P_Effective_Date
565 And ler.typ_Cd = 'GSP'
566 And Pil.person_Id = l_person_id
567 And Pil.Per_In_Ler_Stat_Cd = 'PROCD';
568 --
569 Cursor csr_sal is
570 Select Rate.Val Proposed_Sal,
571 Rate.Rt_Strt_Dt Sal_Chg_Dt
572 From Ben_Elig_Per_Elctbl_Chc Elct,
573 Ben_Enrt_Rt Rate
574 Where Elct.DFLT_FLAG = 'Y'
575 and Elct.Elctbl_Flag = 'Y'
576 and Elct.Per_in_ler_id = l_per_in_ler_id
577 and Elct.Enrt_Cvg_Strt_Dt is Not NULL
578 And Rate.ELIG_PER_ELCTBL_CHC_ID(+) = Elct.ELIG_PER_ELCTBL_CHC_ID;
579 --
580 Cursor csr_payroll_freq (p_payroll_id in number) is
581 Select pr.period_type
582 From pay_all_payrolls_f pr
583 Where pr.payroll_id = p_payroll_id
584 and p_Effective_Date
585 Between pr.Effective_Start_date and pr.Effective_End_Date;
586 --
587 Cursor csr_proposal_rsn (p_change_dt in date) is
588 select proposal_reason
589 from per_pay_proposals
590 where assignment_id = p_assignment_id
591 and change_date = p_change_dt;
592 --
593 CURSOR C_Pay_Basis IS
594 select name, pay_basis
595 From Per_Pay_Bases ppb
596 where Ppb.Pay_Basis_Id = l_pay_basis_id;
597
598 Begin
599 --
600 l_continue := true;
601 l_proc := 'get_emp_proposed_salary';
602 hr_utility.set_location('Entering:'||l_proc, 5);
603 --
604 p_proposed_salary := null;
605 p_sal_chg_dt := null;
606 p_frequency := null;
607 p_annual_salary := null;
608 p_pay_basis := null;
609 p_reason_cd := null;
610 p_currency := null;
611 --
612 -- Step 1. Fetch Assignment Details
613 --
614 Open csr_asg_details;
615 Fetch csr_asg_details into l_pay_basis_id, l_pgm_id, l_person_id;
616 If csr_asg_details%found then
617 --
618 If l_pgm_id is NOT NULL then
619 --
620 -- Get grade ladder currency
621 --
622 hr_utility.set_location('Get grade ladder details', 20);
623 --
624 Open csr_GrdLdr;
625 Fetch csr_GrdLdr into l_pgm_frequency, p_currency, l_update_sal_cd;
626 Close csr_GrdLdr;
627 --
628 -- If the grade ladder does not allow salary update, then return
629 --
630 If l_update_sal_cd in ('SALARY_BASIS','SALARY_ELEMENT') then
631 --
632 Open csr_le;
633 Fetch csr_le into l_per_in_ler_id;
634 If csr_le%notfound then
635 l_continue := false;
636 p_status := 2;
637 hr_utility.set_location('No life event on the assignment change date. Cannot compute proposed salary.', 30);
638 End if;
639 Close csr_le;
640 --
641 --
642 If l_continue then
643 Open csr_sal;
644 Fetch csr_sal into L_Cur_Sal,p_sal_chg_dt;
645 If csr_sal%notfound then
646 l_continue := false;
647 p_status := 3;
648 hr_utility.set_location('No default enrolment found. Cannot compute proposed salary.', 40);
649 End if;
650 Close csr_sal;
651 End if;
652 --
653 If l_continue then
654 If l_update_sal_cd = 'SALARY_BASIS' then
655 --
656 If l_pay_basis_id IS NOT NULL then
657 --
658 hr_utility.set_location('Assignment has Salary Basis', 50);
659 --
660 -- Get pay basis frequency
661 --
662 Open C_Pay_Basis;
663 Fetch C_Pay_Basis into p_pay_basis,p_frequency;
664 Close C_Pay_Basis;
665 --
666 -- Get pay proposal reason
667 --
668 Open csr_proposal_rsn(p_sal_chg_dt);
669 Fetch csr_proposal_rsn into p_reason_cd;
670 Close csr_proposal_rsn;
671 --
672 --
673 Else
674 --
675 -- This scenario should never occur.
676 --
677 l_continue := false;
678 hr_utility.set_location('Assignment has no Salary Basis', 60);
679 --
680 End if;
681 --
682 Else /** Using salary element **/
683 --
684 hr_utility.set_location('Using salary element', 70);
685 If l_payroll_id is not null then
686 Open csr_payroll_freq(l_payroll_id);
687 Fetch csr_payroll_freq into p_frequency;
688 Close csr_payroll_freq;
689 Else
690 p_frequency := l_pgm_frequency;
691 p_proposed_salary := L_Cur_Sal;
692 End if;
693 --
694 End if; /** l_update_salary_cd = 'SALARY BASIS' **/
695 End if; /** l_continue **/
696 --
697 -- Convert rate in program frequency to Salary basis/Salary element frequency
698 --
699 If l_continue then
700 If L_Cur_Sal is not null then
701 --
702 hr_utility.set_location('Calling pqh_gsp_utility.PGM_TO_BASIS_CONVERSION', 80);
703 --
704 p_proposed_salary := pqh_gsp_utility.PGM_TO_BASIS_CONVERSION
705 (P_Pgm_ID => l_pgm_id
706 ,P_EFFECTIVE_DATE => P_Effective_Date
707 ,P_AMOUNT => L_Cur_Sal
708 ,P_ASSIGNMENT_ID => p_assignment_id);
709 --
710 --
711 hr_utility.set_location('Computing annual salary from program frequency.', 90);
712 --
713 p_annual_salary := pgm_to_annual(p_ref_perd_cd => l_pgm_frequency,
714 p_pgm_currency => p_currency,
715 p_amount => L_Cur_Sal);
716 --
717 p_status := 1;
718 --
719 End If;
720 End if; /** l_continue **/
721 --
722 Else
723 --
724 hr_utility.set_location('Grade Ladder setup to not update salary', 120);
725 p_status := 4;
726 --
727 End if;
728 Else
729 --
730 -- Assignment is not on a GL. Cannot compute proposed salary..
731 --
732 hr_utility.set_location(' Assignment is not on a Grade Ladder. Cannot compute proposed salary', 130);
733 p_status := 5;
734 --
735 --
736 End if;
737 Close csr_asg_details;
738 End if;
739 --
740 --
741 hr_utility.set_location('Leaving:'||l_proc, 10);
742 --
743 Exception When others then
744 --
745 hr_utility.set_location('Exception:'||l_proc, 200);
746 raise;
747 --
748 End get_emp_proposed_salary;
749 --
750 End;