1 Package body pqh_salary_class_intervals_pkg as
2 /* $Header: pqhsalco.pkb 120.2 2006/04/18 12:44:17 nsanghal noship $ */
3 --
4
5 function get_salary_interval (
6 p_emp_category in varchar2
7 ,p_annual_salary in number default 0)
8 return varchar2 IS
9 l_annual_salary Number;
10 begin
11 if (p_emp_category in ('FT','FR')) then
12 -- Bug 5018881: Added rounding of salary
13 l_annual_salary := round(nvl(p_annual_salary,0),2);
14
15 if l_annual_salary between 0 and 29999.99 then
16 return '01';
17 elsif l_annual_salary between 30000 and 39999.99 then
18 return '02';
19 elsif l_annual_salary between 40000 and 49999.99 then
20 return '03';
21 elsif l_annual_salary between 50000 and 64999.99 then
22 return '04';
23 elsif l_annual_salary between 65000 and 79999.99 then
24 return '05';
25 elsif l_annual_salary between 80000 and 99999.99 then
26 return '06';
27 elsif l_annual_salary >= 100000 then
28 return '07';
29 else
30 return to_char(l_annual_salary);
31 end if;
32 else
33 return ' ';
34 end if;
35 end;
36
37 function get_job_sal_interval (
38 p_emp_category in varchar2
39 ,p_job_code in varchar2
40 ,p_annual_salary in number default 0)
41 return varchar2 IS
42 l_annual_salary Number;
43 begin
44
45 -- Bug 5018881: Added rounding of salary
46 l_annual_salary := round(nvl(p_annual_salary,0),2);
47 --
48 if ((p_emp_category in ('FT','FR')) and (p_job_code in ('5','6','7'))) then
49 if nvl(l_annual_salary,0) = 0 or l_annual_salary between 0 and 29999.99 then
50 return '01';
51 elsif l_annual_salary between 30000 and 39999.99 then
52 return '02';
53 elsif l_annual_salary between 40000 and 49999.99 then
54 return '03';
55 elsif l_annual_salary between 50000 and 64999.99 then
56 return '04';
57 elsif l_annual_salary between 65000 and 79999.99 then
58 return '05';
59 elsif l_annual_salary between 80000 and 99999.99 then
60 return '06';
61 elsif l_annual_salary >= 100000 then
62 return '07';
63 else
64 return to_char(l_annual_salary);
65 end if;
66 elsif ((p_emp_category in ('FT','FR')) and (p_job_code in ('8','9','10','11'))) then
67 if nvl(l_annual_salary,0) = 0 or l_annual_salary between 0 and 19999.99 then
68 return '01';
69 elsif l_annual_salary between 20000 and 29999.99 then
70 return '02';
71 elsif l_annual_salary between 30000 and 39999.99 then
72 return '03';
73 elsif l_annual_salary between 40000 and 49999.99 then
74 return '04';
75 elsif l_annual_salary >= 50000 then
76 return '05';
77 else
78 return to_char(l_annual_salary);
79 end if;
80 else
81 return ' ';
82 end if;
83 end;
84
85 end;
[Home] [Help]