1 PACKAGE BODY hri_bpl_sal AS
2 /* $Header: hribsal.pkb 120.2 2006/05/17 04:39:20 rkonduru noship $ */
3 --
4 -- Declare global variables.
5 --
6 g_currency VARCHAR2(3);
7 g_salary NUMBER;
8 g_assignment_id NUMBER;
9 g_date DATE;
10 TYPE g_varchar_tabtype IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11 g_rate_type_tab g_varchar_tabtype;
12 g_default_rate_type VARCHAR2(30) := fnd_profile.value('BIS_PRIMARY_RATE_TYPE');
13 --
14 -- -------------------------------------------------------------------------
15 --
16 PROCEDURE fetch_currency_and_salary(p_assignment_id IN NUMBER,
17 p_date IN DATE) IS
18 -- bug 3553301
19 l_salary NUMBER;
20 l_currency VARCHAR2(100);
21 --
22 CURSOR c_salary_value
23 ( p_assignment_id NUMBER
24 , p_effective_date DATE)
25 IS
26 select s.proposed_salary_n* ppb.pay_annualization_factor salary
27 -- nvl(ppb.pay_annualization_factor,tpt.number_per_fiscal_year) salary -- bug 3547581
28 , pet.input_currency_code salary_currency_code
29 from pay_element_types_f pet
30 , pay_input_values_f piv
31 , per_pay_bases ppb
32 --, per_time_period_types tpt -- bug 3547581
33 --, pay_all_payrolls_f prl -- bug 3547581
34 , per_assignments_f a
35 , per_pay_proposals_v2 s
36 where a.assignment_type = 'E'
37 and a.assignment_id = p_assignment_id
38 and p_effective_date between a.effective_start_date and a.effective_end_date
39 and s.change_date IN (select max(ppp2.change_date)
40 from per_pay_proposals_v2 ppp2
41 where ppp2.change_date <= p_effective_date
42 and ppp2.assignment_id = a.assignment_id)
43 and a.pay_basis_id = ppb.pay_basis_id
44 and ppb.input_value_id = piv.input_value_id
45 -- bug 3547581
46 /*and s.change_date between
47 prl.effective_start_date and prl.effective_end_date
48 and a.payroll_id=prl.payroll_id
49 and prl.period_type=tpt.period_type */
50 and p_effective_date between
51 piv.effective_start_date and piv.effective_end_date
52 and piv.element_type_id = pet.element_type_id
53 and p_effective_date between
54 pet.effective_start_date and pet.effective_end_date
55 and a.assignment_id = s.assignment_id
56 and s.approved = 'Y'
57 order by a.assignment_id;
58 --
59 BEGIN
60 --
61 OPEN c_salary_value(p_assignment_id,
62 p_date);
63 FETCH c_salary_value INTO l_salary, l_currency;
64 --
65 -- bug 3553301: get the cursor value in a local varibale first and therefore it does not
66 -- incorrectly cache data.
67 --
68 g_salary := NVL(l_salary,0);
69 --
70 g_currency := NVL(l_currency,'NA_EDW');
71 --
72 CLOSE c_salary_value;
73 --
74 g_assignment_id := p_assignment_id;
75 g_date := p_date;
76 --
77 END fetch_currency_and_salary;
78 -- -------------------------------------------------------------------------
79 FUNCTION get_assignment_sal(p_assignment_id IN NUMBER,
80 p_date IN DATE)
81 RETURN NUMBER IS
82 BEGIN
83 IF (p_assignment_id = g_assignment_id) AND (p_date = g_date) THEN
84 RETURN g_salary;
85 END IF;
86 fetch_currency_and_salary(p_assignment_id, p_date);
87 RETURN g_salary;
88 EXCEPTION
89 WHEN OTHERS THEN
90 RETURN NULL;
91 END get_assignment_sal;
92 -- -------------------------------------------------------------------------
93 FUNCTION get_assignment_currency(p_assignment_id IN NUMBER,
94 p_date IN DATE)
95 RETURN VARCHAR2 IS
96 BEGIN
97 IF (p_assignment_id = g_assignment_id) AND (p_date = g_date) THEN
98 RETURN g_currency;
99 END IF;
100 fetch_currency_and_salary(p_assignment_id, p_date);
101 RETURN g_currency;
102 EXCEPTION
103 WHEN OTHERS THEN
104 RETURN NULL;
105 END get_assignment_currency;
106 --
107 FUNCTION convert_amount(p_from_currency IN VARCHAR2,
108 p_to_currency IN VARCHAR2,
109 p_conversion_date IN DATE,
110 p_amount IN NUMBER,
111 p_business_group_id IN NUMBER DEFAULT NULL)
112 RETURN NUMBER IS
113
114 l_result NUMBER;
115 l_rate_type VARCHAR2(30);
116
117 BEGIN
118
119 /* If a business group id is given look for an overriding rate type */
120 IF (p_business_group_id IS NOT NULL) THEN
121
122 /* Trap exception in sql block for cache miss */
123 BEGIN
124 /* Check cache for rate type */
125 l_rate_type := g_rate_type_tab(p_business_group_id);
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 /* If there is no rate type stored in the cache, find the rate type */
129 /* for the given business group */
130 l_rate_type := hr_currency_pkg.get_rate_type
131 (p_business_group_id => p_business_group_id,
132 p_conversion_date => p_conversion_date,
133 p_processing_type => 'I');
134 /* If no rate type is found, use the default */
135 IF (l_rate_type IS NULL) THEN
136 l_rate_type := g_default_rate_type;
137 END IF;
138 /* Cache the rate type for next time */
139 g_rate_type_tab(p_business_group_id) := l_rate_type;
140 END;
141
142 ELSE
143 /* No business group given, so use the default rate type */
144 l_rate_type := g_default_rate_type;
145
146 END IF;
147
148 -- l_result := hr_currency_pkg.convert_amount
149 l_result := 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 => l_rate_type);
155
156 RETURN l_result;
157
158 END convert_amount;
159 --
160 -- Salary conversion function for DBI moved from HRI_DBI_SALARY
161 --
162 FUNCTION convert_amount(p_from_currency IN VARCHAR2,
163 p_to_currency IN VARCHAR2,
164 p_conversion_date IN DATE,
165 p_amount IN NUMBER,
166 p_rate_type IN VARCHAR2)
167 RETURN NUMBER IS
168
169 l_converted_amount NUMBER := 0;
170
171 BEGIN
172
173 IF (p_from_currency IS NOT NULL AND
174 p_to_currency IS NOT NULL AND
175 p_amount IS NOT NULL)
176 THEN
177 IF (p_from_currency = 'NA_EDW')
178 THEN
179 l_converted_amount := p_amount ;
180 ELSE
181 -- l_converted_amount := hr_currency_pkg.convert_amount
182 l_converted_amount := hri_bpl_currency.convert_currency_amount
183 (p_from_currency => p_from_currency
184 ,p_to_currency => p_to_currency
185 ,p_conversion_date => p_conversion_date
186 ,p_amount => p_amount
187 ,p_rate_type => p_rate_type);
188 END IF;
189 ELSE
190 -- no salary for this assignment.
191 l_converted_amount := 0;
192 END IF;
193
194 RETURN l_converted_amount ;
195
196 END convert_amount;
197 --
198 -- ----------------------------------------------------------------------------
199 -- FUNCTION GET_ANNUALIZATION_FACTOR
200 -- ----------------------------------------------------------------------------
201 -- When the pay basis type is PERIOD then the annualization factor can be null.
202 -- In such cases the annualization factor is same as the yearly frequency of
203 -- the payroll. This function returns the annualization factor is such cases.
204 -- ----------------------------------------------------------------------------
205 --
206 FUNCTION get_perd_annualization_factor( p_assignment_id IN NUMBER,
207 p_effective_date IN DATE)
208 RETURN NUMBER IS
209 --
210 l_dummy VARCHAR2(240);
211 l_pay_annualization_factor NUMBER;
212 --
213 BEGIN
214 --
215 PER_PAY_PROPOSALS_POPULATE.GET_PAYROLL(p_assignment_id
216 ,p_effective_date
217 ,l_dummy
218 ,l_pay_annualization_factor);
219 --
220 RETURN l_pay_annualization_factor;
221 --
222 --
223 END get_perd_annualization_factor;
224 --
225 END hri_bpl_sal;
[Home] [Help]