1 PACKAGE BODY hri_oltp_disc_salary AS
2 /* $Header: hriodsal.pkb 115.3 2003/04/02 15:07:48 jtitmas noship $ */
3
4
5 /******************************************************************************/
6 /* This function will return the previous salary proposal of a given */
7 /* pay_proposal_id, it is called from the Oracle Internal workbooks that */
8 /* display previous salary. */
9 /* */
10 /* The function was found to be the most performant way of returning an */
11 /* employees previous salary proposal amount for a given employees */
12 /* pay_proposal_id. */
13 /******************************************************************************/
14 FUNCTION get_prev_salary_pro_amount(p_pay_proposal_id NUMBER)
15 RETURN NUMBER IS
16
17 CURSOR cur_get_prev_sal(cp_pay_proposal_id number) is
18 SELECT
19 asg.assignment_number assignment_number
20 , pro.proposed_salary_n salary_amount
21 , ppb.pay_annualization_factor annualization_factor
22 , ppb.pay_annualization_factor * pro.proposed_salary_n annual_salary
23 FROM per_assignments_f asg /* Secure */
24 , per_pay_bases ppb
25 , per_pay_proposals pro
26 , per_pay_proposals pro_next
27 , pay_input_values_f piv
28 , pay_element_types_f pet
29 , pay_payrolls_f pay /* Secure */
30 WHERE
31 /* Joins inc. date joins */
32 pro.assignment_id = asg.assignment_id
33 AND pro.change_date BETWEEN asg.effective_start_date
34 AND asg.effective_end_date
35 /* only show people who have a salary */
36 AND asg.pay_basis_id = ppb.pay_basis_id
37 /* Next Pay proposal to get date */
38 AND pro.assignment_id = pro_next.assignment_id
39 AND pro.change_date = pro_next.last_change_date
40 /* Element entry types and currencies */
41 AND ppb.input_value_id = piv.input_value_id
42 AND piv.element_type_id = pet.element_type_id
43 AND pro.change_date BETWEEN pet.effective_start_date
44 AND pet.effective_end_date
45 /* Payrolls and periods */
46 AND asg.payroll_id = pay.payroll_id
47 AND pro.change_date BETWEEN pay.effective_start_date
48 AND pay.effective_end_date
49 AND pro_next.pay_proposal_id = cp_pay_proposal_id;
50
51 l_cur_rec cur_get_prev_sal%ROWTYPE;
52
53 l_annual_salary number := 0;
54
55 BEGIN
56
57 OPEN cur_get_prev_sal(p_pay_proposal_id);
58 FETCH cur_get_prev_sal INTO l_cur_rec;
59
60 l_annual_salary := l_cur_rec.annual_salary;
61
62 CLOSE cur_get_prev_sal;
63
64 RETURN(l_annual_salary);
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 IF cur_get_prev_sal%ISOPEN THEN
69 CLOSE cur_get_prev_sal;
70 END IF;
71
72 RETURN(l_annual_salary);
73
74 END get_prev_salary_pro_amount;
75
76
77 /******************************************************************************/
78 /* Gets the annual salary for an assignment on a given date */
79 /******************************************************************************/
80 FUNCTION get_annual_salary_as_of_date(p_effective_date DATE
81 ,p_assignment_id NUMBER)
82 RETURN NUMBER IS
83
84 cursor cur_get_ann_sal(cp_effective_date date,cp_assignment_id number) IS
85 select annual_salary
86 from HRIFV_SAL_PRO
87 where assignment_id = cp_assignment_id
88 and cp_effective_date between effective_from_date and effective_to_date_nn;
89
90 l_annual_salary number :=0;
91
92 BEGIN
93
94 open cur_get_ann_sal(p_effective_date, p_assignment_id);
95 fetch cur_get_ann_sal into l_annual_salary;
96 close cur_get_ann_sal;
97
98 return(l_annual_salary);
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 IF cur_get_ann_sal%ISOPEN THEN
103 close cur_get_ann_sal;
104 END IF;
105
106 return(l_annual_salary);
107
108 END get_annual_salary_as_of_date;
109
110 /******************************************************************************/
111 /* Converts currency amount using a specified precision and default rate type */
112 /******************************************************************************/
113 FUNCTION convert_currency_amount(p_from_currency IN VARCHAR2
114 ,p_to_currency IN VARCHAR2
115 ,p_conversion_date IN DATE
116 ,p_amount IN NUMBER
117 ,p_precision IN NUMBER)
118 RETURN NUMBER IS
119
120 l_converted_amount NUMBER;
121
122 BEGIN
123
124 l_converted_amount := hri_bpl_currency.convert_currency_amount
125 (p_from_currency => p_from_currency
126 ,p_to_currency => p_to_currency
127 ,p_conversion_date => p_conversion_date
128 ,p_amount => p_amount
129 ,p_precision => p_precision);
130
131 RETURN l_converted_amount;
132
133 END convert_currency_amount;
134
135 /******************************************************************************/
136 /* Converts a currency amount given a rate type */
137 /******************************************************************************/
138 FUNCTION convert_currency_amount(p_from_currency IN VARCHAR2
139 ,p_to_currency IN VARCHAR2
140 ,p_conversion_date IN DATE
141 ,p_amount IN NUMBER
142 ,p_rate_type IN VARCHAR2)
143 RETURN NUMBER IS
144
145 l_converted_amount NUMBER;
146
147 BEGIN
148
149 l_converted_amount := hri_bpl_currency.convert_currency_amount
150 (p_from_currency => p_from_currency
151 ,p_to_currency => p_to_currency
152 ,p_conversion_date => p_conversion_date
153 ,p_amount => p_amount
154 ,p_rate_type => p_rate_type);
155
156 RETURN l_converted_amount;
157
158 END convert_currency_amount;
159
160 /******************************************************************************/
161 /* Converts a currency amount using a default rate type */
162 /******************************************************************************/
163 FUNCTION convert_currency_amount(p_from_currency IN VARCHAR2,
164 p_to_currency IN VARCHAR2,
165 p_conversion_date IN DATE,
166 p_amount IN NUMBER)
167 RETURN NUMBER IS l_converted_amount NUMBER;
168
169 BEGIN
170
171 l_converted_amount := hri_bpl_currency.convert_currency_amount
172 (p_from_currency => p_from_currency
173 ,p_to_currency => p_to_currency
174 ,p_conversion_date => p_conversion_date
175 ,p_amount => p_amount);
176
177 RETURN l_converted_amount;
178
179 END convert_currency_amount;
180
181 END hri_oltp_disc_salary;
[Home] [Help]