[Home] [Help]
PACKAGE BODY: APPS.PAY_FORMULA_RESULT_RULE_API
Source
1 Package Body PAY_FORMULA_RESULT_RULE_API as
2 /* $Header: pyfrrapi.pkb 120.0 2005/05/29 05:06:01 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_FORMULA_RESULT_RULE_API.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |----------------------< CREATE_FORMULA_RESULT_RULE >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure CREATE_FORMULA_RESULT_RULE
13 (p_validate in boolean default false
14 ,p_effective_date in date
15 ,p_status_processing_rule_id in number
16 ,p_result_name in varchar2
17 ,p_result_rule_type in varchar2
18 ,p_business_group_id in number default null
19 ,p_legislation_code in varchar2 default null
20 ,p_element_type_id in number default null
21 ,p_legislation_subgroup in varchar2 default null
22 ,p_severity_level in varchar2 default null
23 ,p_input_value_id in number default null
24 ,p_formula_result_rule_id out nocopy number
25 ,p_effective_start_date out nocopy date
26 ,p_effective_end_date out nocopy date
27 ,p_object_version_number out nocopy number
28 ) is
29 --
30 -- Declare cursors and local variables
31 --
32 l_proc varchar2(72):=g_package||'CREATE_FORMULA_RESULT_RULE';
33 l_effective_date date;
34 l_effective_start_date date;
35 l_effective_end_date date;
36 l_formula_result_rule_id number;
37 l_object_version_number number;
38 l_element_type_id pay_status_processing_rules_f.element_type_id%type;
39 --
40 cursor c_spr_element is
41 select element_type_id
42 from pay_status_processing_rules_f spr
43 where spr.status_processing_rule_id = p_status_processing_rule_id
44 and p_effective_date between spr.effective_start_date
45 and spr.effective_end_date;
46 --
47 begin
48 hr_utility.set_location('Entering:'|| l_proc, 10);
49 --
50 -- Issue a savepoint
51 --
52 savepoint CREATE_FORMULA_RESULT_RULE;
53 --
54 -- Truncate the time portion from all IN date parameters
55 --
56 l_effective_date := trunc(p_effective_date);
57 --
58 -- Call Before Process User Hook
59 --
60 begin
61 PAY_FORMULA_RESULT_RULE_bk1.CREATE_FORMULA_RESULT_RULE_b
62 (p_effective_date => l_effective_date
63 ,p_status_processing_rule_id => p_status_processing_rule_id
64 ,p_result_name => p_result_name
65 ,p_result_rule_type => p_result_rule_type
66 ,p_business_group_id => p_business_group_id
67 ,p_legislation_code => p_legislation_code
68 ,p_element_type_id => p_element_type_id
69 ,p_legislation_subgroup => p_legislation_subgroup
70 ,p_severity_level => p_severity_level
71 ,p_input_value_id => p_input_value_id
72 );
73 exception
74 when hr_api.cannot_find_prog_unit then
75 hr_api.cannot_find_prog_unit_error
76 (p_module_name => 'CREATE_FORMULA_RESULT_RULE'
77 ,p_hook_type => 'BP'
78 );
79 end;
80 --
81 -- Validation in addition to Row Handlers
82 --
83 if p_result_rule_type = 'D' then
84 --
85 -- default the element type for direct result rule.
86 --
87 open c_spr_element;
88 fetch c_spr_element into l_element_type_id;
89 close c_spr_element;
90 --
91 else
92 l_element_type_id := p_element_type_id;
93 end if;
94 --
95 -- Process Logic
96 --
97 pay_frr_ins.ins
98 (p_effective_date => l_effective_date
99 ,p_status_processing_rule_id => p_status_processing_rule_id
100 ,p_result_name => p_result_name
101 ,p_result_rule_type => p_result_rule_type
102 ,p_business_group_id => p_business_group_id
103 ,p_legislation_code => p_legislation_code
104 ,p_element_type_id => l_element_type_id
105 ,p_legislation_subgroup => p_legislation_subgroup
106 ,p_severity_level => p_severity_level
107 ,p_input_value_id => p_input_value_id
108 ,p_formula_result_rule_id => l_formula_result_rule_id
109 ,p_object_version_number => l_object_version_number
110 ,p_effective_start_date => l_effective_start_date
111 ,p_effective_end_date => l_effective_end_date
112 );
113 --
114 -- Call After Process User Hook
115 --
116 begin
117 PAY_FORMULA_RESULT_RULE_bk1.CREATE_FORMULA_RESULT_RULE_a
118 (p_effective_date => l_effective_date
119 ,p_status_processing_rule_id => p_status_processing_rule_id
120 ,p_result_name => p_result_name
121 ,p_result_rule_type => p_result_rule_type
122 ,p_business_group_id => p_business_group_id
123 ,p_legislation_code => p_legislation_code
124 ,p_element_type_id => l_element_type_id
125 ,p_legislation_subgroup => p_legislation_subgroup
126 ,p_severity_level => p_severity_level
127 ,p_input_value_id => p_input_value_id
128 ,p_formula_result_rule_id => l_formula_result_rule_id
129 ,p_effective_start_date => l_effective_start_date
130 ,p_effective_end_date => l_effective_end_date
131 ,p_object_version_number => l_object_version_number
132 );
133 exception
134 when hr_api.cannot_find_prog_unit then
135 hr_api.cannot_find_prog_unit_error
136 (p_module_name => 'CREATE_FORMULA_RESULT_RULE'
137 ,p_hook_type => 'AP'
138 );
139 end;
140 --
141 -- When in validation only mode raise the Validate_Enabled exception
142 --
143 if p_validate then
144 raise hr_api.validate_enabled;
145 end if;
146 --
147 -- Set all output arguments
148 --
149 p_formula_result_rule_id := l_formula_result_rule_id;
150 p_effective_start_date := l_effective_start_date;
151 p_effective_end_date := l_effective_end_date;
152 p_object_version_number := l_object_version_number;
153 --
154 hr_utility.set_location(' Leaving:'||l_proc, 70);
155 exception
156 when hr_api.validate_enabled then
157 --
158 -- As the Validate_Enabled exception has been raised
159 -- we must rollback to the savepoint
160 --
161 rollback to CREATE_FORMULA_RESULT_RULE;
162 --
163 -- Only set output warning arguments
164 -- (Any key or derived arguments must be set to null
165 -- when validation only mode is being used.)
166 --
167 p_formula_result_rule_id := null;
168 p_object_version_number := null;
169 p_effective_start_date := null;
170 p_effective_end_date := null;
171 --
172 hr_utility.set_location(' Leaving:'||l_proc, 80);
173 when others then
174 --
175 -- A validation or unexpected error has occured
176 --
177 rollback to CREATE_FORMULA_RESULT_RULE;
178 --
179 -- Reset IN OUT parameters and set all
180 -- OUT parameters, including warnings, to null
181 --
182 p_formula_result_rule_id := null;
183 p_object_version_number := null;
184 p_effective_start_date := null;
185 p_effective_end_date := null;
186 hr_utility.set_location(' Leaving:'||l_proc, 90);
187 raise;
188 --
189 End CREATE_FORMULA_RESULT_RULE;
190 --
191 -- ----------------------------------------------------------------------------
192 -- |----------------------< UPDATE_FORMULA_RESULT_RULE >----------------------|
193 -- ----------------------------------------------------------------------------
194 --
195 procedure UPDATE_FORMULA_RESULT_RULE
196 (p_validate in boolean default false
197 ,p_effective_date in date
198 ,p_datetrack_update_mode in varchar2
199 ,p_formula_result_rule_id in number
200 ,p_object_version_number in out nocopy number
201 ,p_result_rule_type in varchar2 default hr_api.g_varchar2
202 ,p_element_type_id in number default hr_api.g_number
203 ,p_severity_level in varchar2 default hr_api.g_varchar2
204 ,p_input_value_id in number default hr_api.g_number
205 ,p_effective_start_date out nocopy date
206 ,p_effective_end_date out nocopy date
207 ) is
208 --
209 -- Declare cursors and local variables
210 --
211 l_proc varchar2(72):=g_package||'UPDATE_FORMULA_RESULT_RULE';
212 l_effective_date date;
213 l_effective_start_date date;
214 l_effective_end_date date;
215 l_object_version_number number;
216 l_element_type_id pay_status_processing_rules_f.element_type_id%type;
217 --
218 cursor c_spr_element is
219 select spr.element_type_id
220 from pay_formula_result_rules_f frr
221 ,pay_status_processing_rules_f spr
222 where frr.formula_result_rule_id = p_formula_result_rule_id
223 and spr.status_processing_rule_id = frr.status_processing_rule_id
224 and p_effective_date between frr.effective_start_date
225 and frr.effective_end_date;
226 --
227 begin
228 hr_utility.set_location('Entering:'|| l_proc, 10);
229 --
230 -- Issue a savepoint
231 --
232 savepoint UPDATE_FORMULA_RESULT_RULE;
233 --
234 -- Truncate the time portion from all IN date parameters
235 --
236 l_effective_date := trunc(p_effective_date);
237 --
238 -- Remember IN OUT parameter IN values
239 --
240 l_object_version_number := p_object_version_number;
241 --
242 -- Call Before Process User Hook
243 --
244 begin
245 PAY_FORMULA_RESULT_RULE_bk2.UPDATE_FORMULA_RESULT_RULE_b
246 (p_effective_date => l_effective_date
247 ,p_datetrack_update_mode => p_datetrack_update_mode
248 ,p_formula_result_rule_id => p_formula_result_rule_id
249 ,p_object_version_number => l_object_version_number
250 ,p_result_rule_type => p_result_rule_type
251 ,p_element_type_id => p_element_type_id
252 ,p_severity_level => p_severity_level
253 ,p_input_value_id => p_input_value_id
254 );
255 exception
256 when hr_api.cannot_find_prog_unit then
257 hr_api.cannot_find_prog_unit_error
258 (p_module_name => 'UPDATE_FORMULA_RESULT_RULE'
259 ,p_hook_type => 'BP'
260 );
261 end;
262 --
263 -- Validation in addition to Row Handlers
264 --
265 if p_result_rule_type = 'D' then
266 --
267 -- default the element type for direct result rule.
268 --
269 open c_spr_element;
270 fetch c_spr_element into l_element_type_id;
271 close c_spr_element;
272 --
273 else
274 l_element_type_id := p_element_type_id;
275 end if;
276 --
277 -- Process Logic
278 --
279 pay_frr_upd.upd
280 (p_effective_date => l_effective_date
281 ,p_datetrack_mode => p_datetrack_update_mode
282 ,p_formula_result_rule_id => p_formula_result_rule_id
283 ,p_object_version_number => l_object_version_number
284 ,p_result_rule_type => p_result_rule_type
285 ,p_element_type_id => l_element_type_id
286 ,p_severity_level => p_severity_level
287 ,p_input_value_id => p_input_value_id
288 ,p_effective_start_date => l_effective_start_date
289 ,p_effective_end_date => l_effective_end_date
290 );
291 --
292 -- Call After Process User Hook
293 --
294 begin
295 PAY_FORMULA_RESULT_RULE_bk2.UPDATE_FORMULA_RESULT_RULE_a
296 (p_effective_date => l_effective_date
297 ,p_datetrack_update_mode => p_datetrack_update_mode
298 ,p_result_rule_type => p_result_rule_type
299 ,p_element_type_id => l_element_type_id
300 ,p_severity_level => p_severity_level
301 ,p_input_value_id => p_input_value_id
302 ,p_formula_result_rule_id => p_formula_result_rule_id
303 ,p_effective_start_date => l_effective_start_date
304 ,p_effective_end_date => l_effective_end_date
305 ,p_object_version_number => l_object_version_number
306 );
307 exception
308 when hr_api.cannot_find_prog_unit then
309 hr_api.cannot_find_prog_unit_error
310 (p_module_name => 'UPDATE_FORMULA_RESULT_RULE'
311 ,p_hook_type => 'AP'
312 );
313 end;
314 --
315 -- When in validation only mode raise the Validate_Enabled exception
316 --
317 if p_validate then
318 raise hr_api.validate_enabled;
319 end if;
320 --
321 -- Set all output arguments
322 --
323 p_effective_start_date := l_effective_start_date;
324 p_effective_end_date := l_effective_end_date;
325 p_object_version_number := l_object_version_number;
326 --
327 hr_utility.set_location(' Leaving:'||l_proc, 70);
328 exception
329 when hr_api.validate_enabled then
330 --
331 -- As the Validate_Enabled exception has been raised
332 -- we must rollback to the savepoint
333 --
334 rollback to UPDATE_FORMULA_RESULT_RULE;
335 --
336 -- Only set output warning arguments
337 -- (Any key or derived arguments must be set to null
338 -- when validation only mode is being used.)
339 --
340 p_object_version_number := l_object_version_number;
341 p_effective_start_date := null;
342 p_effective_end_date := null;
343 --
344 hr_utility.set_location(' Leaving:'||l_proc, 80);
345 when others then
346 --
347 -- A validation or unexpected error has occured
348 --
349 rollback to UPDATE_FORMULA_RESULT_RULE;
350 --
351 -- Reset IN OUT parameters and set all
352 -- OUT parameters, including warnings, to null
353 --
354 p_object_version_number := l_object_version_number;
355 p_effective_start_date := null;
356 p_effective_end_date := null;
357 hr_utility.set_location(' Leaving:'||l_proc, 90);
358 raise;
359 --
360 End UPDATE_FORMULA_RESULT_RULE;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |----------------------< DELETE_FORMULA_RESULT_RULE >----------------------|
364 -- ----------------------------------------------------------------------------
365 --
366 procedure DELETE_FORMULA_RESULT_RULE
367 (p_validate in boolean default false
368 ,p_effective_date in date
369 ,p_datetrack_delete_mode in varchar2
370 ,p_formula_result_rule_id in number
371 ,p_object_version_number in out nocopy number
372 ,p_effective_start_date out nocopy date
373 ,p_effective_end_date out nocopy date
374 ) is
375 --
376 -- Declare cursors and local variables
377 --
378 l_proc varchar2(72):=g_package||'DELETE_FORMULA_RESULT_RULE';
379 l_effective_date date;
380 l_effective_start_date date;
381 l_effective_end_date date;
382 l_object_version_number number;
383 --
384 begin
385 hr_utility.set_location('Entering:'|| l_proc, 10);
386 --
387 -- Issue a savepoint
388 --
389 savepoint DELETE_FORMULA_RESULT_RULE;
390 --
391 -- Truncate the time portion from all IN date parameters
392 --
393 l_effective_date := trunc(p_effective_date);
394 --
395 -- Remember IN OUT parameter IN values
396 --
397 l_object_version_number := p_object_version_number;
398 --
399 -- Call Before Process User Hook
400 --
401 begin
402 PAY_FORMULA_RESULT_RULE_bk3.DELETE_FORMULA_RESULT_RULE_b
403 (p_effective_date => l_effective_date
404 ,p_datetrack_delete_mode => p_datetrack_delete_mode
405 ,p_formula_result_rule_id => p_formula_result_rule_id
406 ,p_object_version_number => l_object_version_number
407 );
408 exception
409 when hr_api.cannot_find_prog_unit then
410 hr_api.cannot_find_prog_unit_error
411 (p_module_name => 'DELETE_FORMULA_RESULT_RULE'
412 ,p_hook_type => 'BP'
413 );
414 end;
415 --
416 -- Validation in addition to Row Handlers
417 --
418
419 --
420 -- Process Logic
421 --
422 pay_frr_del.del
423 (p_effective_date => l_effective_date
424 ,p_datetrack_mode => p_datetrack_delete_mode
425 ,p_formula_result_rule_id => p_formula_result_rule_id
426 ,p_object_version_number => l_object_version_number
427 ,p_effective_start_date => l_effective_start_date
428 ,p_effective_end_date => l_effective_end_date
429 );
430 --
431 -- Call After Process User Hook
432 --
433 begin
434 PAY_FORMULA_RESULT_RULE_bk3.DELETE_FORMULA_RESULT_RULE_a
435 (p_effective_date => l_effective_date
436 ,p_datetrack_delete_mode => p_datetrack_delete_mode
437 ,p_formula_result_rule_id => p_formula_result_rule_id
438 ,p_effective_start_date => l_effective_start_date
439 ,p_effective_end_date => l_effective_end_date
440 ,p_object_version_number => l_object_version_number
441 );
442 exception
443 when hr_api.cannot_find_prog_unit then
444 hr_api.cannot_find_prog_unit_error
445 (p_module_name => 'DELETE_FORMULA_RESULT_RULE'
446 ,p_hook_type => 'AP'
447 );
448 end;
449 --
450 -- When in validation only mode raise the Validate_Enabled exception
451 --
452 if p_validate then
453 raise hr_api.validate_enabled;
454 end if;
455 --
456 -- Set all output arguments
457 --
458 p_effective_start_date := l_effective_start_date;
459 p_effective_end_date := l_effective_end_date;
460 p_object_version_number := l_object_version_number;
461 --
462 hr_utility.set_location(' Leaving:'||l_proc, 70);
463 exception
464 when hr_api.validate_enabled then
465 --
466 -- As the Validate_Enabled exception has been raised
467 -- we must rollback to the savepoint
468 --
469 rollback to DELETE_FORMULA_RESULT_RULE;
470 --
471 -- Only set output warning arguments
472 -- (Any key or derived arguments must be set to null
473 -- when validation only mode is being used.)
474 --
475 p_object_version_number := l_object_version_number;
476 p_effective_start_date := null;
477 p_effective_end_date := null;
478 --
479 hr_utility.set_location(' Leaving:'||l_proc, 80);
480 when others then
481 --
482 -- A validation or unexpected error has occured
483 --
484 rollback to DELETE_FORMULA_RESULT_RULE;
485 --
486 -- Reset IN OUT parameters and set all
487 -- OUT parameters, including warnings, to null
488 --
489 p_object_version_number := l_object_version_number;
490 p_effective_start_date := null;
491 p_effective_end_date := null;
492 hr_utility.set_location(' Leaving:'||l_proc, 90);
493 raise;
494 --
495 End DELETE_FORMULA_RESULT_RULE;
496 --
497
498 end PAY_FORMULA_RESULT_RULE_API;