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