[Home] [Help]
PACKAGE BODY: APPS.PQH_EMPLOYEE_SALARY
Source
1 Package Body pqh_employee_salary as
2 /* $Header: pqempsal.pkb 120.2 2005/06/15 04:38 ggnanagu noship $ */
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 -- The following procedure returns the salary change caused by a grade or step change on the assignment
433 --
434 -- p_status = 1 : Success. Proposed salary value will be returned only for this status.
435 -- p_status = 2 : No life event
436 -- p_status = 3 : No default enrt
437 -- p_status = 4 : GL not using salary update
438 -- p_status = 5 : Assignment not on GL
439 --
440 Procedure get_emp_proposed_salary
441 (P_Assignment_id In Per_All_Assignments_F.ASSIGNMENT_ID%TYPE,
442 P_Effective_Date In Date,
443 p_proposed_salary OUT nocopy number,
444 p_sal_chg_dt OUT nocopy date,
445 p_frequency OUT nocopy varchar2,
446 p_annual_salary OUT nocopy number,
447 p_pay_basis OUT nocopy varchar2,
448 p_reason_cd OUT nocopy varchar2,
449 p_currency OUT nocopy varchar2,
450 p_status OUT nocopy number
451 ) IS
452 --
453 L_Cur_Sal Ben_Enrt_Rt.val%type;
454 l_update_sal_cd ben_pgm_f.update_salary_cd%type;
455 --
456 l_pay_basis_id Per_All_Assignments_f.pay_basis_id%type;
457 l_pgm_id Per_All_Assignments_f.GRADE_LADDER_PGM_ID%type;
458 l_person_id Per_All_Assignments_f.person_id%type;
459 l_payroll_id Per_All_Assignments_f.payroll_id%type;
460 --
461 l_pgm_frequency Ben_Pgm_f.ACTY_REF_PERD_CD%type;
462 l_per_in_ler_id Ben_Per_in_Ler.per_in_ler_id%type;
463 --
464 l_continue boolean;
465 l_proc varchar2(72);
466 --
467 Cursor csr_asg_details is
468 Select paf.pay_basis_id, paf.GRADE_LADDER_PGM_ID, paf.person_id
469 From Per_All_Assignments_f paf
470 Where paf.Assignment_Id = p_Assignment_Id
471 and p_Effective_Date
472 Between Paf.Effective_Start_Date and Paf.Effective_End_Date;
473 --
474 Cursor csr_GrdLdr is
475 Select ACTY_REF_PERD_CD,pgm_uom,Update_Salary_Cd
476 from Ben_Pgm_f pgm
477 Where pgm.pgm_id = l_pgm_id
478 and p_Effective_Date
479 Between pgm.Effective_Start_date and pgm.Effective_End_Date;
480 --
481 Cursor csr_le is
482 Select max(pil.Per_in_Ler_Id)
483 From Ben_Per_in_ler PIL,
484 Ben_Ler_F LER
485 Where Pil.Ler_Id = LER.Ler_Id
486 And Pil.LF_EVT_OCRD_DT = P_Effective_Date
487 And ler.typ_Cd = 'GSP'
488 And Pil.person_Id = l_person_id
489 And Pil.Per_In_Ler_Stat_Cd = 'PROCD';
490 --
491 Cursor csr_sal is
492 Select Rate.Val Proposed_Sal,
493 Rate.Rt_Strt_Dt Sal_Chg_Dt
494 From Ben_Elig_Per_Elctbl_Chc Elct,
495 Ben_Enrt_Rt Rate
496 Where Elct.DFLT_FLAG = 'Y'
497 and Elct.Elctbl_Flag = 'Y'
498 and Elct.Per_in_ler_id = l_per_in_ler_id
499 and Elct.Enrt_Cvg_Strt_Dt is Not NULL
500 And Rate.ELIG_PER_ELCTBL_CHC_ID(+) = Elct.ELIG_PER_ELCTBL_CHC_ID;
501 --
502 Cursor csr_payroll_freq (p_payroll_id in number) is
503 Select pr.period_type
504 From pay_all_payrolls_f pr
505 Where pr.payroll_id = p_payroll_id
506 and p_Effective_Date
507 Between pr.Effective_Start_date and pr.Effective_End_Date;
508 --
509 Cursor csr_proposal_rsn (p_change_dt in date) is
510 select proposal_reason
511 from per_pay_proposals
512 where assignment_id = p_assignment_id
513 and change_date = p_change_dt;
514 --
515 CURSOR C_Pay_Basis IS
516 select name, pay_basis
517 From Per_Pay_Bases ppb
518 where Ppb.Pay_Basis_Id = l_pay_basis_id;
519
520 Begin
521 --
522 l_continue := true;
523 l_proc := 'get_emp_proposed_salary';
524 hr_utility.set_location('Entering:'||l_proc, 5);
525 --
526 p_proposed_salary := null;
527 p_sal_chg_dt := null;
528 p_frequency := null;
529 p_annual_salary := null;
530 p_pay_basis := null;
531 p_reason_cd := null;
532 p_currency := null;
533 --
534 -- Step 1. Fetch Assignment Details
535 --
536 Open csr_asg_details;
537 Fetch csr_asg_details into l_pay_basis_id, l_pgm_id, l_person_id;
538 If csr_asg_details%found then
539 --
540 If l_pgm_id is NOT NULL then
541 --
542 -- Get grade ladder currency
543 --
544 hr_utility.set_location('Get grade ladder details', 20);
545 --
546 Open csr_GrdLdr;
547 Fetch csr_GrdLdr into l_pgm_frequency, p_currency, l_update_sal_cd;
548 Close csr_GrdLdr;
549 --
550 -- If the grade ladder does not allow salary update, then return
551 --
552 If l_update_sal_cd in ('SALARY_BASIS','SALARY_ELEMENT') then
553 --
554 Open csr_le;
555 Fetch csr_le into l_per_in_ler_id;
556 If csr_le%notfound then
557 l_continue := false;
558 p_status := 2;
559 hr_utility.set_location('No life event on the assignment change date. Cannot compute proposed salary.', 30);
560 End if;
561 Close csr_le;
562 --
563 --
564 If l_continue then
565 Open csr_sal;
566 Fetch csr_sal into L_Cur_Sal,p_sal_chg_dt;
567 If csr_sal%notfound then
568 l_continue := false;
569 p_status := 3;
570 hr_utility.set_location('No default enrolment found. Cannot compute proposed salary.', 40);
571 End if;
572 Close csr_sal;
573 End if;
574 --
575 If l_continue then
576 If l_update_sal_cd = 'SALARY_BASIS' then
577 --
578 If l_pay_basis_id IS NOT NULL then
579 --
580 hr_utility.set_location('Assignment has Salary Basis', 50);
581 --
582 -- Get pay basis frequency
583 --
584 Open C_Pay_Basis;
585 Fetch C_Pay_Basis into p_pay_basis,p_frequency;
586 Close C_Pay_Basis;
587 --
588 -- Get pay proposal reason
589 --
590 Open csr_proposal_rsn(p_sal_chg_dt);
591 Fetch csr_proposal_rsn into p_reason_cd;
592 Close csr_proposal_rsn;
593 --
594 --
595 Else
596 --
597 -- This scenario should never occur.
598 --
599 l_continue := false;
600 hr_utility.set_location('Assignment has no Salary Basis', 60);
601 --
602 End if;
603 --
604 Else /** Using salary element **/
605 --
606 hr_utility.set_location('Using salary element', 70);
607 If l_payroll_id is not null then
608 Open csr_payroll_freq(l_payroll_id);
609 Fetch csr_payroll_freq into p_frequency;
610 Close csr_payroll_freq;
611 Else
612 p_frequency := l_pgm_frequency;
613 p_proposed_salary := L_Cur_Sal;
614 End if;
615 --
616 End if; /** l_update_salary_cd = 'SALARY BASIS' **/
617 End if; /** l_continue **/
618 --
619 -- Convert rate in program frequency to Salary basis/Salary element frequency
620 --
621 If l_continue then
622 If L_Cur_Sal is not null then
623 --
624 hr_utility.set_location('Calling pqh_gsp_utility.PGM_TO_BASIS_CONVERSION', 80);
625 --
626 p_proposed_salary := pqh_gsp_utility.PGM_TO_BASIS_CONVERSION
627 (P_Pgm_ID => l_pgm_id
628 ,P_EFFECTIVE_DATE => P_Effective_Date
629 ,P_AMOUNT => L_Cur_Sal
630 ,P_ASSIGNMENT_ID => p_assignment_id);
631 --
632 --
633 hr_utility.set_location('Computing annual salary from program frequency.', 90);
634 --
635 p_annual_salary := pgm_to_annual(p_ref_perd_cd => l_pgm_frequency,
636 p_pgm_currency => p_currency,
637 p_amount => L_Cur_Sal);
638 --
639 p_status := 1;
640 --
641 End If;
642 End if; /** l_continue **/
643 --
644 Else
645 --
646 hr_utility.set_location('Grade Ladder setup to not update salary', 120);
647 p_status := 4;
648 --
649 End if;
650 Else
651 --
652 -- Assignment is not on a GL. Cannot compute proposed salary..
653 --
654 hr_utility.set_location(' Assignment is not on a Grade Ladder. Cannot compute proposed salary', 130);
655 p_status := 5;
656 --
657 --
658 End if;
659 Close csr_asg_details;
660 End if;
661 --
662 --
663 hr_utility.set_location('Leaving:'||l_proc, 10);
664 --
665 Exception When others then
666 --
667 hr_utility.set_location('Exception:'||l_proc, 200);
668 raise;
669 --
670 End get_emp_proposed_salary;
671 --
672 End;