[Home] [Help]
PACKAGE BODY: APPS.BE_CALL_FF_PKG
Source
4 --
1 PACKAGE BODY be_call_ff_pkg AS
2 -- $Header: pebeclff.pkb 115.6 2003/02/07 09:48:53 atrivedi noship $
3 --
5 -- Package constants
6 --
7 c_pkg_name CONSTANT VARCHAR2(30) := 'be_call_ff_pkg';
8 --
9 --
10 -- Service routine to return information on the formula.
11 --
12 PROCEDURE formula_details
13 (p_session_date DATE
14 ,p_formula_name VARCHAR2
15 ,o_formula_id OUT NOCOPY NUMBER
16 ,o_effective_start_date OUT NOCOPY DATE) IS
17 --
18 --
19 -- Cursor to find the formula.
20 --
21 CURSOR csr_formula_details
22 (p_session_date DATE
23 ,p_formula_name VARCHAR2) IS
24 SELECT formula_id, effective_start_date
25 FROM ff_formulas_f
26 WHERE formula_name = p_formula_name
27 AND legislation_code = 'BE'
28 AND p_session_date BETWEEN effective_start_date
29 AND effective_end_date;
30 --
31 --
32 -- Local variables
33 --
34 l_rec csr_formula_details%ROWTYPE;
35 formula_missing exception;
36 BEGIN
37 --
38 --
39 -- Get details on the formula.
40 --
41 OPEN csr_formula_details
42 (p_session_date => p_session_date
43 ,p_formula_name => p_formula_name);
44 FETCH csr_formula_details INTO l_rec;
45 IF csr_formula_details%NOTFOUND THEN
46 CLOSE csr_formula_details;
47 RAISE formula_missing;
48 END IF;
49 CLOSE csr_formula_details;
50 --
51 --
52 -- Return the details.
53 --
54 o_formula_id := l_rec.formula_id;
55 o_effective_start_date := l_rec.effective_start_date;
56 EXCEPTION
57 WHEN formula_missing THEN
58 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
59 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.FORMULA_DETAILS');
60 hr_utility.set_message_token('STEP', '10');
61 hr_utility.raise_error;
62 END formula_details;
63 --
64 --
65 -- Service routine to return information on the business group
66 --
67 PROCEDURE business_group_details
68 (p_business_group_id IN NUMBER
69 ,o_currency_code OUT NOCOPY VARCHAR2) IS
70 --
71 --
72 -- Curosr to find the business group.
73 --
74 CURSOR csr_bg_details
75 (p_business_group_id NUMBER) IS
76 SELECT currency_code
77 FROM per_business_groups
78 WHERE business_group_id = p_business_group_id;
79 --
80 --
81 -- Local variables
82 --
83 l_rec csr_bg_details%ROWTYPE;
84 bg_missing exception;
85 BEGIN
86 --
87 --
88 -- Get details on the business group.
89 --
90 OPEN csr_bg_details(p_business_group_id => p_business_group_id);
91 FETCH csr_bg_details INTO l_rec;
92 IF csr_bg_details%NOTFOUND THEN
93 CLOSE csr_bg_details;
94 RAISE bg_missing;
95 END IF;
96 CLOSE csr_bg_details;
97 --
98 --
99 -- Return the details.
100 --
101 o_currency_code := l_rec.currency_code;
102 EXCEPTION
103 WHEN bg_missing THEN
104 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
105 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.BUSINESS_GROUP_DETAILS');
106 hr_utility.set_message_token('STEP', '10');
107 hr_utility.raise_error;
108 END business_group_details;
109 --
110 --
111 -- Computes the employee's notice period
112 --
113 PROCEDURE calculate_notice_period
114 (p_service_years IN NUMBER
115 ,p_service_months IN NUMBER
116 ,p_age_years IN NUMBER
117 ,p_age_months IN NUMBER
118 ,p_salary IN NUMBER
119 ,p_notice_type IN VARCHAR2
120 ,p_derivation_method IN VARCHAR2
121 ,p_assignment_id IN NUMBER
122 ,p_business_group_id IN NUMBER
123 ,p_legislation_code IN VARCHAR2
124 ,p_session_date IN DATE
125 ,p_notice_period IN OUT NOCOPY NUMBER
126 ,p_counter_notice IN OUT NOCOPY NUMBER
127 ,p_leave_days IN OUT NOCOPY VARCHAR2) IS
128 --
129 --
130 -- Local constants
131 --
132 c_proc_name CONSTANT VARCHAR2(61) := c_pkg_name || '.calculate_notice_period';
133 --
134 --
135 -- Local variables.
136 --
137 l_formula_id ff_formulas_f.formula_id%TYPE;
138 l_effective_start_date ff_formulas_f.effective_start_date%TYPE;
139 l_bg_currency_code VARCHAR2(30);
140 l_inputs ff_exec.inputs_t;
141 l_outputs ff_exec.outputs_t;
142 wrong_input_params exception;
143 wrong_output_params exception;
144 BEGIN
145 --
146 --
147 -- Get the details for the formula.
148 --
152 ,p_formula_name => 'BE_DERIVE_NOTICE_PERIOD'
149 hr_utility.set_location(c_proc_name, 10);
150 formula_details
151 (p_session_date => p_session_date
153 ,o_formula_id => l_formula_id
154 ,o_effective_start_date => l_effective_start_date);
155 --
156 --
157 -- Get the details for the business group.
158 --
159 hr_utility.set_location(c_proc_name, 15);
160 business_group_details
161 (p_business_group_id => p_business_group_id
162 ,o_currency_code => l_bg_currency_code);
163 --
164 --
165 -- Prepare to run the formula.
166 --
167 hr_utility.set_location(c_proc_name, 20);
168 ff_exec.init_formula
169 (l_formula_id
170 ,l_effective_start_date
171 ,l_inputs
172 ,l_outputs);
173 --
174 --
175 -- Setup the inputs to the formula.
176 --
177 hr_utility.set_location(c_proc_name, 30);
178 FOR l_in_cnt IN l_inputs.first..l_inputs.last LOOP
179 IF l_inputs(l_in_cnt).name = 'BE_EMP_SERV_YEARS' THEN
180 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(p_service_years);
181 ELSIF l_inputs(l_in_cnt).name = 'BE_EMP_SERV_MONTHS' THEN
182 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(p_service_months);
183 ELSIF l_inputs(l_in_cnt).name = 'BE_EMP_AGE_YEARS' THEN
184 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(p_age_years);
185 ELSIF l_inputs(l_in_cnt).name = 'BE_EMP_AGE_MONTHS' THEN
186 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(p_age_months);
187 ELSIF l_inputs(l_in_cnt).name = 'BE_EMP_SALARY' THEN
188 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(p_salary);
189 ELSIF l_inputs(l_in_cnt).name = 'BE_NOTICE_TYPE' THEN
190 l_inputs(l_in_cnt).value := p_notice_type;
191 ELSIF l_inputs(l_in_cnt).name = 'BE_DERIVATION_METHOD' THEN
192 l_inputs(l_in_cnt).value := p_derivation_method;
193 ELSIF l_inputs(l_in_cnt).name = 'BE_CURRENCY_CODE' THEN
194 l_inputs(l_in_cnt).value := l_bg_currency_code;
195 ELSIF l_inputs(l_in_cnt).name = 'BUSINESS_GROUP_ID' THEN
196 l_inputs(l_in_cnt).value := p_business_group_id;
197 ELSIF l_inputs(l_in_cnt).name = 'EFFECTIVE_START_DATE' THEN
198 l_inputs(l_in_cnt).value := TO_CHAR(sysdate,'DD/MM/YYYY');
199 ELSIF l_inputs(l_in_cnt).name = 'ASSIGNMENT_ID' THEN
200 l_inputs(l_in_cnt).value := p_assignment_id;
201 ELSE
202 RAISE wrong_input_params;
203 END IF;
204 END LOOP;
205 --
206 --
207 -- Run the formula.
208 --
209 hr_utility.set_location(c_proc_name, 40);
210 ff_exec.run_formula
211 (l_inputs
212 ,l_outputs);
213 --
214 --
215 -- Extract all the outputs.
216 --
217 hr_utility.set_location(c_proc_name, 50);
218 FOR l_out_cnt IN l_outputs.first..l_outputs.last LOOP
219 IF l_outputs(l_out_cnt).name = 'BE_NOTICE_PERIOD' THEN
220 p_notice_period := fnd_number.canonical_to_number(l_outputs(l_out_cnt).value);
221 ELSIF l_outputs(l_out_cnt).name = 'BE_COUNTER_NOTICE' THEN
222 p_counter_notice := fnd_number.canonical_to_number(l_outputs(l_out_cnt).value);
223 ELSIF l_outputs(l_out_cnt).name = 'BE_LEAVE_DAYS' THEN
224 p_leave_days := l_outputs(l_out_cnt).value;
225 ELSE
226 RAISE wrong_output_params;
227 END IF;
228 END LOOP;
229 EXCEPTION
230 WHEN wrong_input_params THEN
231 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
232 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.CALCULATE_NOTICE_PERIOD');
233 hr_utility.set_message_token('STEP', '10');
234 hr_utility.raise_error;
235 WHEN wrong_output_params THEN
236 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
237 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.CALCULATE_NOTICE_PERIOD');
238 hr_utility.set_message_token('STEP', '20');
239 hr_utility.raise_error;
240 END calculate_notice_period;
241 --
242 --
243 -- Validates the NI Number.
244 --
245 FUNCTION check_ni
246 (p_national_identifier IN VARCHAR2
247 ,p_birth_date IN DATE
248 ,p_gender IN VARCHAR2
249 ,p_event IN VARCHAR2
250 ,p_person_id IN NUMBER
251 ,p_business_group_id IN NUMBER
252 ,p_legislation_code IN VARCHAR2 DEFAULT 'BE'
253 ,p_session_date IN DATE) RETURN VARCHAR2 IS
254 --
255 --
256 -- Local constants
257 --
258 c_proc_name CONSTANT VARCHAR2(61) := c_pkg_name || '.check_ni';
259 --
260 --
261 -- Local variables.
262 --
263 l_formula_id ff_formulas_f.formula_id%type;
264 l_effective_start_date ff_formulas_f.effective_start_date%type;
265 l_inputs ff_exec.inputs_t;
266 l_outputs ff_exec.outputs_t;
267 l_return_value VARCHAR(240) := p_national_identifier;
268 l_invalid_mesg VARCHAR(240);
269 wrong_input_params exception;
270 wrong_output_params exception;
271 BEGIN
272 --
273 --
274 -- Get the details for the formula.
275 --
276 formula_details
277 (p_session_date => p_session_date
278 ,p_formula_name => 'NI_VALIDATION'
279 ,o_formula_id => l_formula_id
280 ,o_effective_start_date => l_effective_start_date);
281 --
282 --
283 -- Prepare to run the formula.
284 --
285 ff_exec.init_formula
286 (l_formula_id
290 --
287 ,l_effective_start_date
288 ,l_inputs
289 ,l_outputs);
291 --
292 -- Setup the inputs to the formula.
293 --
294 FOR l_in_cnt IN l_inputs.first..l_inputs.last LOOP
295 IF l_inputs(l_in_cnt).name = 'NATIONAL_IDENTIFIER' THEN
296 l_inputs(l_in_cnt).value := p_national_identifier;
297 ELSIF l_inputs(l_in_cnt).name = 'BIRTH_DATE' THEN
298 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(p_birth_date);
299 ELSIF l_inputs(l_in_cnt).name = 'GENDER' THEN
300 l_inputs(l_in_cnt).value := p_gender;
301 ELSIF l_inputs(l_in_cnt).name = 'EVENT' THEN
302 l_inputs(l_in_cnt).value := p_event;
303 ELSE
304 RAISE wrong_input_params;
305 END IF;
306 END LOOP;
307 --
308 --
309 -- Run the formula.
310 --
311 ff_exec.run_formula
312 (l_inputs
313 ,l_outputs);
314 --
315 --
316 -- Extract all the outputs.
317 --
318 FOR l_out_cnt IN l_outputs.first..l_outputs.last LOOP
319 IF l_outputs(l_out_cnt).name = 'RETURN_VALUE' THEN
320 l_return_value := l_outputs(l_out_cnt).value;
321 ELSIF l_outputs(l_out_cnt).name = 'INVALID_MESG' THEN
322 l_invalid_mesg := l_outputs(l_out_cnt).value;
323 ELSE
324 RAISE wrong_output_params;
325 END IF;
326 END LOOP;
327 --
328 --
329 -- If the format was not correct then raise an error.
330 --
331 IF l_return_value = 'INVALID_ID' THEN
332 hr_utility.set_message(801, l_invalid_mesg);
333 hr_utility.raise_error;
334 END IF;
335 --
336 --
337 -- Pass back the result.
338 --
339 RETURN l_return_value;
340 EXCEPTION
341 WHEN wrong_input_params THEN
342 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
343 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.CHECK_NI');
344 hr_utility.set_message_token('STEP', '10');
345 hr_utility.raise_error;
346 WHEN wrong_output_params THEN
347 hr_utility.set_message(800,'HR_6153_ALL_PROCEDURE_FAIL');
348 hr_utility.set_message_token('PROCEDURE', 'BE_CALL_FF_PKG.CHECK_NI');
349 hr_utility.set_message_token('STEP', '20');
350 hr_utility.raise_error;
351 END check_ni;
352 --
353 end be_call_ff_pkg;