[Home] [Help]
PACKAGE BODY: APPS.PAY_ES_BENEFIT_UPLIFT_CALC
Source
1 PACKAGE BODY pay_es_benefit_uplift_calc AS
2 /* $Header: pyesssbu.pkb 120.0 2005/05/29 04:39:34 appldev noship $ */
3 --
4 -- Global Variables
5 hr_formula_error EXCEPTION;
6 g_gross_per_day_formula_exists BOOLEAN := TRUE;
7 g_gross_per_day_formula_cached BOOLEAN := FALSE;
8 g_gross_per_day_formula_id ff_formulas_f.formula_id%TYPE;
9 g_gross_per_day_formula_name ff_formulas_f.formula_name%TYPE;
10 --
11 g_duration_formula_exists BOOLEAN := TRUE;
12 g_duration_formula_cached BOOLEAN := FALSE;
13 g_duration_formula_id ff_formulas_f.formula_id%TYPE;
14 g_duration_formula_name ff_formulas_f.formula_name%TYPE;
15 -------------------------------------------------------------------------------
16 -- FUNCTION get_gross_per_day
17 -------------------------------------------------------------------------------
18 FUNCTION get_gross_per_day(p_assignment_id IN NUMBER
19 ,p_business_group_id IN NUMBER
20 ,p_date_earned IN DATE
21 ,p_formula_name IN VARCHAR2
22 ) RETURN NUMBER IS
23 --
24 l_inputs ff_exec.inputs_t;
25 l_outputs ff_exec.outputs_t;
26 l_formula_exists BOOLEAN := TRUE;
27 l_formula_cached BOOLEAN := FALSE;
28 l_formula_id ff_formulas_f.formula_id%TYPE;
29 l_gross_per_day VARCHAR2(500);
30 --
31 BEGIN
32 -- hr_utility.trace_on(null,'EFT');
33 hr_utility.set_location('--In Gross per Day ',10);
34 --
35 g_gross_per_day_formula_name := p_formula_name;
36 --
37 IF g_gross_per_day_formula_exists THEN
38 --
39 IF g_gross_per_day_formula_exists THEN
40 --
41 IF g_gross_per_day_formula_cached = FALSE THEN
42 cache_formula(p_formula_name,p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
43 g_gross_per_day_formula_exists:=l_formula_exists;
44 g_gross_per_day_formula_cached:=l_formula_cached;
45 g_gross_per_day_formula_id:=l_formula_id;
46 END IF;
47 --
48 IF g_gross_per_day_formula_exists THEN
49 --
50 l_inputs(1).name := 'ASSIGNMENT_ID';
51 l_inputs(1).value := p_assignment_id;
52 l_inputs(2).name := 'DATE_EARNED';
53 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
54 l_inputs(3).name := 'BUSINESS_GROUP_ID';
55 l_inputs(3).value := p_business_group_id;
56 --
57 l_outputs(1).name := 'GROSS_PAY_PER_DAY';
58 --
59 run_formula(p_formula_id => g_gross_per_day_formula_id,
60 p_effective_date => p_date_earned,
61 p_formula_name => g_gross_per_day_formula_name,
62 p_inputs => l_inputs,
63 p_outputs => l_outputs);
64 --
65 l_gross_per_day := substr(l_outputs(1).value,1,32);
66 --
67 END IF;
68
69 END IF;
70
71 --
72 END IF;
73 hr_utility.set_location('--In Formula Return ',11);
74 --
75 RETURN l_gross_per_day;
76 --
77 END get_gross_per_day;
78 --------------------------------------------------------------------------------
79 -- FUNCTION get_duration
80 --------------------------------------------------------------------------------
81 FUNCTION get_duration(p_assignment_id IN NUMBER
82 ,p_business_group_id IN NUMBER
83 ,p_date_earned IN DATE
84 ,p_formula_name IN VARCHAR2
85 ,p_rate1 OUT NOCOPY NUMBER
86 ,p_value1 OUT NOCOPY NUMBER
87 ,p_rate2 OUT NOCOPY NUMBER
88 ,p_value2 OUT NOCOPY NUMBER
89 ,p_rate3 OUT NOCOPY NUMBER
90 ,p_value3 OUT NOCOPY NUMBER
91 ,p_rate4 OUT NOCOPY NUMBER
92 ,p_value4 OUT NOCOPY NUMBER
93 ,p_rate5 OUT NOCOPY NUMBER
94 ,p_value5 OUT NOCOPY NUMBER
95 ,p_rate6 OUT NOCOPY NUMBER
96 ,p_value6 OUT NOCOPY NUMBER
97 ,p_rate7 OUT NOCOPY NUMBER
98 ,p_value7 OUT NOCOPY NUMBER
99 ,p_rate8 OUT NOCOPY NUMBER
100 ,p_value8 OUT NOCOPY NUMBER
101 ,p_rate9 OUT NOCOPY NUMBER
102 ,p_value9 OUT NOCOPY NUMBER
103 ,p_rate10 OUT NOCOPY NUMBER
104 ,p_value10 OUT NOCOPY NUMBER
105 ) RETURN VARCHAR2 IS
106 --
107 l_inputs ff_exec.inputs_t;
108 l_outputs ff_exec.outputs_t;
109 l_formula_exists BOOLEAN := TRUE;
110 l_formula_cached BOOLEAN := FALSE;
111 l_formula_id ff_formulas_f.formula_id%TYPE;
112 l_return_indicator VARCHAR2(1);
113 --
114 BEGIN
115 -- hr_utility.trace_on(null,'EFT');
116 hr_utility.set_location('--In Get Duration ',10);
117 --
118 l_return_indicator := 'N';
119 g_duration_formula_name := p_formula_name;
120 --
121 IF g_duration_formula_exists = TRUE THEN
122 IF g_duration_formula_cached = FALSE THEN
123 cache_formula(p_formula_name,p_business_group_id,p_date_earned,l_formula_id,l_formula_exists,l_formula_cached);
124 g_duration_formula_exists:=l_formula_exists;
125 g_duration_formula_cached:=l_formula_cached;
126 g_duration_formula_id:=l_formula_id;
127 END IF;
128 --
129 IF g_duration_formula_exists THEN
130 --
131 l_inputs(1).name := 'ASSIGNMENT_ID';
132 l_inputs(1).value := p_assignment_id;
133 l_inputs(2).name := 'DATE_EARNED';
134 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
135 l_inputs(3).name := 'BUSINESS_GROUP_ID';
136 l_inputs(3).value := p_business_group_id;
137 --
138 l_outputs(1).name := 'RATE1';
139 l_outputs(2).name := 'VALUE1';
140 l_outputs(3).name := 'RATE2';
141 l_outputs(4).name := 'VALUE2';
142 l_outputs(5).name := 'RATE3';
143 l_outputs(6).name := 'VALUE3';
144 l_outputs(7).name := 'RATE4';
145 l_outputs(8).name := 'VALUE4';
146 l_outputs(9).name := 'RATE5';
147 l_outputs(10).name := 'VALUE5';
148 l_outputs(11).name := 'RATE6';
149 l_outputs(12).name := 'VALUE6';
150 l_outputs(13).name := 'RATE7';
151 l_outputs(14).name := 'VALUE7';
152 l_outputs(15).name := 'RATE8';
153 l_outputs(16).name := 'VALUE8';
154 l_outputs(17).name := 'RATE9';
155 l_outputs(18).name := 'VALUE9';
156 l_outputs(19).name := 'RATE10';
157 l_outputs(20).name := 'VALUE10';
158 --
159 run_formula(p_formula_id => g_duration_formula_id
160 ,p_effective_date => p_date_earned
161 ,p_formula_name => g_duration_formula_name
162 ,p_inputs => l_inputs
163 ,p_outputs => l_outputs);
164 --
165 p_rate1 := substr(l_outputs(1).value,1,32);
166 p_value1 := substr(l_outputs(2).value,1,32);
167 p_rate2 := substr(l_outputs(3).value,1,32);
168 p_value2 := substr(l_outputs(4).value,1,32);
169 p_rate3 := substr(l_outputs(5).value,1,32);
170 p_value3 := substr(l_outputs(6).value,1,32);
171 p_rate4 := substr(l_outputs(7).value,1,32);
172 p_value4 := substr(l_outputs(8).value,1,32);
173 p_rate5 := substr(l_outputs(9).value,1,32);
174 p_value5 := substr(l_outputs(10).value,1,32);
175 p_rate6 := substr(l_outputs(11).value,1,32);
176 p_value6 := substr(l_outputs(12).value,1,32);
177 p_rate7 := substr(l_outputs(13).value,1,32);
178 p_value7 := substr(l_outputs(14).value,1,32);
179 p_rate8 := substr(l_outputs(15).value,1,32);
180 p_value8 := substr(l_outputs(16).value,1,32);
181 p_rate9 := substr(l_outputs(17).value,1,32);
182 p_value9 := substr(l_outputs(18).value,1,32);
183 p_rate10 := substr(l_outputs(19).value,1,32);
184 p_value10 := substr(l_outputs(20).value,1,32);
185 --
186 IF p_rate1 IS NULL OR p_rate1 < 0 THEN
187 p_rate1 := 0;
188 END IF;
189 IF p_rate2 IS NULL OR p_rate2 < 0 THEN
190 p_rate2 := 0;
191 END IF;
192 IF p_rate3 IS NULL OR p_rate3 < 0 THEN
193 p_rate3 := 0;
194 END IF;
195 IF p_rate4 IS NULL OR p_rate4 < 0 THEN
196 p_rate4 := 0;
197 END IF;
198 IF p_rate5 IS NULL OR p_rate5 < 0 THEN
199 p_rate5 := 0;
200 END IF;
201 IF p_rate6 IS NULL OR p_rate6 < 0 THEN
202 p_rate6 := 0;
203 END IF;
204 IF p_rate7 IS NULL OR p_rate7 < 0 THEN
205 p_rate7 := 0;
206 END IF;
207 IF p_rate8 IS NULL OR p_rate8 < 0 THEN
208 p_rate8 := 0;
209 END IF;
210 IF p_rate9 IS NULL OR p_rate9 < 0 THEN
211 p_rate9 := 0;
212 END IF;
213 IF p_rate10 IS NULL OR p_rate10 < 0 THEN
214 p_rate10 := 0;
215 END IF;
216 --
217 IF p_value1 IS NULL OR p_value1 < 0 THEN
218 p_value1 := 0;
219 END IF;
220 IF p_value2 IS NULL OR p_value2 < 0 THEN
221 p_value2 := 0;
222 END IF;
223 IF p_value3 IS NULL OR p_value3 < 0 THEN
224 p_value3 := 0;
225 END IF;
226 IF p_value4 IS NULL OR p_value4 < 0 THEN
227 p_value4 := 0;
228 END IF;
229 IF p_value5 IS NULL OR p_value5 < 0 THEN
230 p_value5 := 0;
231 END IF;
232 IF p_value6 IS NULL OR p_value6 < 0 THEN
233 p_value6 := 0;
234 END IF;
235 IF p_value7 IS NULL OR p_value7 < 0 THEN
236 p_value7 := 0;
237 END IF;
238 IF p_value8 IS NULL OR p_value8 < 0 THEN
239 p_value8 := 0;
240 END IF;
241 IF p_value9 IS NULL OR p_value9 < 0 THEN
242 p_value9 := 0;
243 END IF;
244 IF p_value10 IS NULL OR p_value10 < 0 THEN
245 p_value10 := 0;
246 END IF;
247 --
248 l_return_indicator := 'Y';
249 --
250 END IF;
251 --
252 END IF;
253 hr_utility.set_location('--In Formula Return ',11);
254 --
255 RETURN l_return_indicator;
256 --
257 END get_duration;
258 -------------------------------------------------------------------------------
259 -- PROCEDURE cache_formula
260 -------------------------------------------------------------------------------
261 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
262 ,p_business_group_id IN NUMBER
263 ,p_effective_date IN DATE
264 ,p_formula_id IN OUT NOCOPY NUMBER
265 ,p_formula_exists IN OUT NOCOPY BOOLEAN
266 ,p_formula_cached IN OUT NOCOPY BOOLEAN
267 ) IS
268
269 --
270 CURSOR c_compiled_formula_exist IS
271 SELECT 'Y'
272 FROM ff_formulas_f ff
273 ,ff_compiled_info_f ffci
274 WHERE ff.formula_id = ffci.formula_id
275 AND ff.effective_start_date = ffci.effective_start_date
276 AND ff.effective_end_date = ffci.effective_end_date
277 AND ff.formula_id = p_formula_id
278 AND ff.business_group_id = p_business_group_id
279 AND p_effective_date BETWEEN ff.effective_start_date
280 AND ff.effective_end_date;
281 --
282 CURSOR c_get_formula(p_formula_name ff_formulas_f.formula_name%TYPE
283 ,p_effective_date DATE) IS
284 SELECT ff.formula_id
285 FROM ff_formulas_f ff
286 WHERE ff.formula_name = p_formula_name
287 AND ff.business_group_id = p_business_group_id
288 AND p_effective_date BETWEEN ff.effective_start_date
289 AND ff.effective_end_date;
290 --
291 l_test VARCHAR2(1);
292 --
293 BEGIN
294 --
295 IF p_formula_cached = FALSE THEN
296 --
297 OPEN c_get_formula(p_formula_name,p_effective_date);
298 FETCH c_get_formula INTO p_formula_id;
299 IF c_get_formula%FOUND THEN
300 OPEN c_compiled_formula_exist;
301 FETCH c_compiled_formula_exist INTO l_test;
302 IF c_compiled_formula_exist%NOTFOUND THEN
303 p_formula_cached := FALSE;
304 p_formula_exists := FALSE;
305 --
306 fnd_message.set_name('PAY','FFX03A_FORMULA_NOT_FOUND');
307 fnd_message.set_token('1', p_formula_name);
308 fnd_message.raise_error;
309 ELSE
310 p_formula_cached := FALSE;
311 p_formula_exists := TRUE;
312 END IF;
313 ELSE
314 p_formula_cached := FALSE;
315 p_formula_exists := FALSE;
316 END IF;
317 CLOSE c_get_formula;
318 END IF;
319 --
320 END cache_formula;
321 -------------------------------------------------------------------------------
322 -- PROCEDURE run_formula
323 -------------------------------------------------------------------------------
324 PROCEDURE run_formula(p_formula_id IN NUMBER
325 ,p_effective_date IN DATE
326 ,p_formula_name IN VARCHAR2
327 ,p_inputs IN ff_exec.inputs_t
331 l_outputs ff_exec.outputs_t;
328 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
329 --
330 l_inputs ff_exec.inputs_t;
332 --
333 BEGIN
334 hr_utility.set_location('--In Formula ',20);
335 --
336 -- Initialize the formula
337 --
338 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
339 --
340 -- Set up the input values
341 --
342 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
343 FOR i IN l_inputs.first..l_inputs.last LOOP
344 FOR j IN p_inputs.first..p_inputs.last LOOP
345 IF l_inputs(i).name = p_inputs(j).name THEN
346 l_inputs(i).value := p_inputs(j).value;
347 exit;
348 END IF;
349 END LOOP;
350 END LOOP;
351 END IF;
352 --
353 -- Run the formula
354 --
355 ff_exec.run_formula(l_inputs,l_outputs);
356 --
357 -- Populate the output table
358 --
359 IF l_outputs.count > 0 and p_inputs.count > 0 then
360 FOR i IN l_outputs.first..l_outputs.last LOOP
361 FOR j IN p_outputs.first..p_outputs.last LOOP
362 IF l_outputs(i).name = p_outputs(j).name THEN
363 p_outputs(j).value := l_outputs(i).value;
364 exit;
365 END IF;
366 END LOOP;
367 END LOOP;
368 END IF;
369 hr_utility.set_location('--Leaving Formula ',21);
370 EXCEPTION
371 WHEN hr_formula_error THEN
372 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
373 fnd_message.set_token('1', p_formula_name);
374 fnd_message.raise_error;
375 WHEN OTHERS THEN
376 raise;
377 --
378 END run_formula;
379 --
380 END pay_es_benefit_uplift_calc;