1 Package Body pqh_bdgt_pool_realloctions_swi As
2 /* $Header: pqbreswi.pkb 115.0 2003/02/06 15:19:53 kgowripe noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_bdgt_pool_realloctions_swi.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_realloc_txn_dtl >------------------------|
10 -- ----------------------------------------------------------------------------
11 PROCEDURE create_realloc_txn_dtl
12 (p_validate in number default hr_api.g_false_num
13 ,p_effective_date in date
14 ,p_transaction_id in number
15 ,p_transaction_type in varchar2
16 ,p_entity_id in number default null
17 ,p_budget_detail_id in number default null
18 ,p_txn_detail_id out nocopy number
19 ,p_object_version_number out nocopy number
20 ,p_return_status out nocopy varchar2
21 ) is
22 --
23 -- Variables for API Boolean parameters
24 l_validate boolean;
25 --
26 -- Variables for IN/OUT parameters
27 --
28 -- Other variables
29 l_proc varchar2(72) := g_package ||'create_realloc_txn_dtl';
30 Begin
31 hr_utility.set_location(' Entering:' || l_proc,10);
32 --
33 -- Issue a savepoint
34 --
35 savepoint create_realloc_txn_dtl_swi;
36 --
37 -- Initialise Multiple Message Detection
38 --
39 hr_multi_message.enable_message_list;
40 --
41 -- Remember IN OUT parameter IN values
42 --
43 --
44 -- Convert constant values to their corresponding boolean value
45 --
46 l_validate :=
47 hr_api.constant_to_boolean
48 (p_constant_value => p_validate);
49 --
50 -- Register Surrogate ID or user key values
51 --
52 --
53 -- Call API
54 --
55 pqh_bdgt_pool_realloctions_api.create_realloc_txn_dtl
56 (p_validate => l_validate
57 ,p_effective_date => p_effective_date
58 ,p_transaction_id => p_transaction_id
59 ,p_transaction_type => p_transaction_type
60 ,p_entity_id => p_entity_id
61 ,p_budget_detail_id => p_budget_detail_id
62 ,p_txn_detail_id => p_txn_detail_id
63 ,p_object_version_number => p_object_version_number
64 );
65 --
66 -- Convert API warning boolean parameter values to specific
67 -- messages and add them to Multiple Message List
68 --
69 --
70 -- Convert API non-warning boolean parameter values
71 --
72 --
73 -- Derive the API return status value based on whether
74 -- messages of any type exist in the Multiple Message List.
75 -- Also disable Multiple Message Detection.
76 --
77 p_return_status := hr_multi_message.get_return_status_disable;
78 hr_utility.set_location(' Leaving:' || l_proc,20);
79 --
80 exception
81 when hr_multi_message.error_message_exist then
82 --
83 -- Catch the Multiple Message List exception which
84 -- indicates API processing has been aborted because
85 -- at least one message exists in the list.
86 --
87 rollback to create_realloc_txn_dtl_swi;
88 --
89 -- Reset IN OUT parameters and set OUT parameters
90 --
91 p_txn_detail_id := null;
92 p_object_version_number := null;
93 p_return_status := hr_multi_message.get_return_status_disable;
94 hr_utility.set_location(' Leaving:' || l_proc, 30);
95 when others then
96 --
97 -- When Multiple Message Detection is enabled catch
98 -- any Application specific or other unexpected
99 -- exceptions. Adding appropriate details to the
100 -- Multiple Message List. Otherwise re-raise the
101 -- error.
102 --
103 rollback to create_realloc_txn_dtl_swi;
104 if hr_multi_message.unexpected_error_add(l_proc) then
105 hr_utility.set_location(' Leaving:' || l_proc,40);
106 raise;
107 end if;
108 --
109 -- Reset IN OUT and set OUT parameters
110 --
111 p_txn_detail_id := null;
112 p_object_version_number := null;
113 p_return_status := hr_multi_message.get_return_status_disable;
114 hr_utility.set_location(' Leaving:' || l_proc,50);
115 end create_realloc_txn_dtl;
116 -- ----------------------------------------------------------------------------
117 -- |-----------------------< create_realloc_txn_period >----------------------|
118 -- ----------------------------------------------------------------------------
119 PROCEDURE create_realloc_txn_period
120 (p_validate in number default hr_api.g_false_num
121 ,p_effective_date in date
122 ,p_txn_detail_id in number
123 ,p_transaction_type in varchar2
124 ,p_entity_id in number default null
125 ,p_budget_period_id in number default null
126 ,p_start_date in date default null
127 ,p_end_date in date default null
128 ,p_reallocation_amt in number
129 ,p_reserved_amt in number default null
130 ,p_reallocation_period_id out nocopy number
131 ,p_object_version_number out nocopy number
132 ,p_return_status out nocopy varchar2
133 ) is
134 --
135 -- Variables for API Boolean parameters
136 l_validate boolean;
137 --
138 -- Variables for IN/OUT parameters
139 --
140 -- Other variables
141 l_proc varchar2(72) := g_package ||'create_realloc_txn_period';
142 Begin
143 hr_utility.set_location(' Entering:' || l_proc,10);
144 --
145 -- Issue a savepoint
146 --
147 savepoint create_realloc_txn_period_swi;
148 --
149 -- Initialise Multiple Message Detection
150 --
151 hr_multi_message.enable_message_list;
152 --
153 -- Remember IN OUT parameter IN values
154 --
155 --
156 -- Convert constant values to their corresponding boolean value
157 --
158 l_validate :=
159 hr_api.constant_to_boolean
160 (p_constant_value => p_validate);
161 --
162 -- Register Surrogate ID or user key values
163 --
164 --
165 -- Call API
166 --
167 pqh_bdgt_pool_realloctions_api.create_realloc_txn_period
168 (p_validate => l_validate
169 ,p_effective_date => p_effective_date
170 ,p_txn_detail_id => p_txn_detail_id
171 ,p_transaction_type => p_transaction_type
172 ,p_entity_id => p_entity_id
173 ,p_budget_period_id => p_budget_period_id
174 ,p_start_date => p_start_date
175 ,p_end_date => p_end_date
176 ,p_reallocation_amt => p_reallocation_amt
180 );
177 ,p_reserved_amt => p_reserved_amt
178 ,p_reallocation_period_id => p_reallocation_period_id
179 ,p_object_version_number => p_object_version_number
181 --
182 -- Convert API warning boolean parameter values to specific
183 -- messages and add them to Multiple Message List
184 --
185 --
186 -- Convert API non-warning boolean parameter values
187 --
188 --
189 -- Derive the API return status value based on whether
190 -- messages of any type exist in the Multiple Message List.
191 -- Also disable Multiple Message Detection.
192 --
193 p_return_status := hr_multi_message.get_return_status_disable;
194 hr_utility.set_location(' Leaving:' || l_proc,20);
195 --
196 exception
197 when hr_multi_message.error_message_exist then
198 --
199 -- Catch the Multiple Message List exception which
200 -- indicates API processing has been aborted because
201 -- at least one message exists in the list.
202 --
203 rollback to create_realloc_txn_period_swi;
204 --
205 -- Reset IN OUT parameters and set OUT parameters
206 --
207 p_reallocation_period_id := null;
208 p_object_version_number := null;
209 p_return_status := hr_multi_message.get_return_status_disable;
210 hr_utility.set_location(' Leaving:' || l_proc, 30);
211 when others then
212 --
213 -- When Multiple Message Detection is enabled catch
214 -- any Application specific or other unexpected
215 -- exceptions. Adding appropriate details to the
216 -- Multiple Message List. Otherwise re-raise the
217 -- error.
218 --
219 rollback to create_realloc_txn_period_swi;
220 if hr_multi_message.unexpected_error_add(l_proc) then
221 hr_utility.set_location(' Leaving:' || l_proc,40);
222 raise;
223 end if;
224 --
225 -- Reset IN OUT and set OUT parameters
226 --
227 p_reallocation_period_id := null;
228 p_object_version_number := null;
229 p_return_status := hr_multi_message.get_return_status_disable;
230 hr_utility.set_location(' Leaving:' || l_proc,50);
231 end create_realloc_txn_period;
232 -- ----------------------------------------------------------------------------
233 -- |------------------------< delete_realloc_txn_dtl >------------------------|
234 -- ----------------------------------------------------------------------------
235 PROCEDURE delete_realloc_txn_dtl
236 (p_validate in number default hr_api.g_false_num
237 ,p_txn_detail_id in number
238 ,p_object_version_number in number
239 ,p_return_status out nocopy varchar2
240 ) is
241 --
242 -- Variables for API Boolean parameters
243 l_validate boolean;
244 --
245 -- Variables for IN/OUT parameters
246 --
247 -- Other variables
248 l_proc varchar2(72) := g_package ||'delete_realloc_txn_dtl';
249 Begin
250 hr_utility.set_location(' Entering:' || l_proc,10);
251 --
252 -- Issue a savepoint
253 --
254 savepoint delete_realloc_txn_dtl_swi;
255 --
256 -- Initialise Multiple Message Detection
257 --
258 hr_multi_message.enable_message_list;
259 --
260 -- Remember IN OUT parameter IN values
261 --
262 --
263 -- Convert constant values to their corresponding boolean value
264 --
265 l_validate :=
266 hr_api.constant_to_boolean
267 (p_constant_value => p_validate);
268 --
269 -- Register Surrogate ID or user key values
270 --
271 --
272 -- Call API
273 --
274 pqh_bdgt_pool_realloctions_api.delete_realloc_txn_dtl
275 (p_validate => l_validate
276 ,p_txn_detail_id => p_txn_detail_id
277 ,p_object_version_number => p_object_version_number
278 );
279 --
280 -- Convert API warning boolean parameter values to specific
281 -- messages and add them to Multiple Message List
282 --
283 --
284 -- Convert API non-warning boolean parameter values
285 --
286 --
287 -- Derive the API return status value based on whether
288 -- messages of any type exist in the Multiple Message List.
289 -- Also disable Multiple Message Detection.
290 --
291 p_return_status := hr_multi_message.get_return_status_disable;
292 hr_utility.set_location(' Leaving:' || l_proc,20);
293 --
294 exception
295 when hr_multi_message.error_message_exist then
296 --
297 -- Catch the Multiple Message List exception which
298 -- indicates API processing has been aborted because
299 -- at least one message exists in the list.
300 --
301 rollback to delete_realloc_txn_dtl_swi;
302 --
303 -- Reset IN OUT parameters and set OUT parameters
304 --
305 p_return_status := hr_multi_message.get_return_status_disable;
306 hr_utility.set_location(' Leaving:' || l_proc, 30);
307 when others then
308 --
309 -- When Multiple Message Detection is enabled catch
310 -- any Application specific or other unexpected
311 -- exceptions. Adding appropriate details to the
312 -- Multiple Message List. Otherwise re-raise the
313 -- error.
314 --
315 rollback to delete_realloc_txn_dtl_swi;
319 end if;
316 if hr_multi_message.unexpected_error_add(l_proc) then
317 hr_utility.set_location(' Leaving:' || l_proc,40);
318 raise;
320 --
321 -- Reset IN OUT and set OUT parameters
322 --
323 p_return_status := hr_multi_message.get_return_status_disable;
324 hr_utility.set_location(' Leaving:' || l_proc,50);
325 end delete_realloc_txn_dtl;
326 -- ----------------------------------------------------------------------------
327 -- |-----------------------< delete_realloc_txn_period >----------------------|
328 -- ----------------------------------------------------------------------------
329 PROCEDURE delete_realloc_txn_period
330 (p_validate in number default hr_api.g_false_num
331 ,p_reallocation_period_id in number
332 ,p_object_version_number in number
333 ,p_return_status out nocopy varchar2
334 ) is
335 --
336 -- Variables for API Boolean parameters
337 l_validate boolean;
338 --
339 -- Variables for IN/OUT parameters
340 --
341 -- Other variables
342 l_proc varchar2(72) := g_package ||'delete_realloc_txn_period';
343 Begin
344 hr_utility.set_location(' Entering:' || l_proc,10);
345 --
346 -- Issue a savepoint
347 --
348 savepoint delete_realloc_txn_period_swi;
349 --
350 -- Initialise Multiple Message Detection
351 --
352 hr_multi_message.enable_message_list;
353 --
354 -- Remember IN OUT parameter IN values
355 --
356 --
357 -- Convert constant values to their corresponding boolean value
358 --
359 l_validate :=
360 hr_api.constant_to_boolean
361 (p_constant_value => p_validate);
362 --
363 -- Register Surrogate ID or user key values
364 --
365 --
366 -- Call API
367 --
368 pqh_bdgt_pool_realloctions_api.delete_realloc_txn_period
369 (p_validate => l_validate
370 ,p_reallocation_period_id => p_reallocation_period_id
371 ,p_object_version_number => p_object_version_number
372 );
373 --
374 -- Convert API warning boolean parameter values to specific
375 -- messages and add them to Multiple Message List
376 --
377 --
378 -- Convert API non-warning boolean parameter values
379 --
380 --
381 -- Derive the API return status value based on whether
382 -- messages of any type exist in the Multiple Message List.
383 -- Also disable Multiple Message Detection.
384 --
385 p_return_status := hr_multi_message.get_return_status_disable;
386 hr_utility.set_location(' Leaving:' || l_proc,20);
387 --
388 exception
389 when hr_multi_message.error_message_exist then
390 --
391 -- Catch the Multiple Message List exception which
392 -- indicates API processing has been aborted because
393 -- at least one message exists in the list.
394 --
395 rollback to delete_realloc_txn_period_swi;
396 --
397 -- Reset IN OUT parameters and set OUT parameters
398 --
399 p_return_status := hr_multi_message.get_return_status_disable;
400 hr_utility.set_location(' Leaving:' || l_proc, 30);
401 when others then
402 --
403 -- When Multiple Message Detection is enabled catch
404 -- any Application specific or other unexpected
405 -- exceptions. Adding appropriate details to the
406 -- Multiple Message List. Otherwise re-raise the
407 -- error.
408 --
409 rollback to delete_realloc_txn_period_swi;
410 if hr_multi_message.unexpected_error_add(l_proc) then
411 hr_utility.set_location(' Leaving:' || l_proc,40);
412 raise;
413 end if;
414 --
415 -- Reset IN OUT and set OUT parameters
416 --
417 p_return_status := hr_multi_message.get_return_status_disable;
418 hr_utility.set_location(' Leaving:' || l_proc,50);
419 end delete_realloc_txn_period;
420 -- ----------------------------------------------------------------------------
421 -- |------------------------< update_realloc_txn_dtl >------------------------|
422 -- ----------------------------------------------------------------------------
423 PROCEDURE update_realloc_txn_dtl
424 (p_validate in number default hr_api.g_false_num
425 ,p_effective_date in date
426 ,p_transaction_id in number default hr_api.g_number
427 ,p_transaction_type in varchar2 default hr_api.g_varchar2
428 ,p_entity_id in number default hr_api.g_number
429 ,p_budget_detail_id in number default hr_api.g_number
430 ,p_txn_detail_id in number
431 ,p_object_version_number in out nocopy number
432 ,p_return_status out nocopy varchar2
433 ) is
434 --
435 -- Variables for API Boolean parameters
436 l_validate boolean;
437 --
438 -- Variables for IN/OUT parameters
439 l_object_version_number number;
440 --
441 -- Other variables
442 l_proc varchar2(72) := g_package ||'update_realloc_txn_dtl';
443 Begin
444 hr_utility.set_location(' Entering:' || l_proc,10);
445 --
446 -- Issue a savepoint
447 --
448 savepoint update_realloc_txn_dtl_swi;
449 --
450 -- Initialise Multiple Message Detection
451 --
452 hr_multi_message.enable_message_list;
453 --
454 -- Remember IN OUT parameter IN values
455 --
456 l_object_version_number := p_object_version_number;
457 --
461 hr_api.constant_to_boolean
458 -- Convert constant values to their corresponding boolean value
459 --
460 l_validate :=
462 (p_constant_value => p_validate);
463 --
464 -- Register Surrogate ID or user key values
465 --
466 --
467 -- Call API
468 --
469 pqh_bdgt_pool_realloctions_api.update_realloc_txn_dtl
470 (p_validate => l_validate
471 ,p_effective_date => p_effective_date
472 ,p_transaction_id => p_transaction_id
473 ,p_transaction_type => p_transaction_type
474 ,p_entity_id => p_entity_id
475 ,p_budget_detail_id => p_budget_detail_id
476 ,p_txn_detail_id => p_txn_detail_id
477 ,p_object_version_number => p_object_version_number
478 );
479 --
480 -- Convert API warning boolean parameter values to specific
481 -- messages and add them to Multiple Message List
482 --
483 --
484 -- Convert API non-warning boolean parameter values
485 --
486 --
487 -- Derive the API return status value based on whether
488 -- messages of any type exist in the Multiple Message List.
489 -- Also disable Multiple Message Detection.
490 --
491 p_return_status := hr_multi_message.get_return_status_disable;
492 hr_utility.set_location(' Leaving:' || l_proc,20);
493 --
494 exception
495 when hr_multi_message.error_message_exist then
496 --
497 -- Catch the Multiple Message List exception which
498 -- indicates API processing has been aborted because
499 -- at least one message exists in the list.
500 --
501 rollback to update_realloc_txn_dtl_swi;
502 --
503 -- Reset IN OUT parameters and set OUT parameters
504 --
505 p_object_version_number := l_object_version_number;
506 p_return_status := hr_multi_message.get_return_status_disable;
507 hr_utility.set_location(' Leaving:' || l_proc, 30);
508 when others then
509 --
510 -- When Multiple Message Detection is enabled catch
511 -- any Application specific or other unexpected
512 -- exceptions. Adding appropriate details to the
513 -- Multiple Message List. Otherwise re-raise the
514 -- error.
515 --
516 rollback to update_realloc_txn_dtl_swi;
517 if hr_multi_message.unexpected_error_add(l_proc) then
518 hr_utility.set_location(' Leaving:' || l_proc,40);
519 raise;
520 end if;
521 --
522 -- Reset IN OUT and set OUT parameters
523 --
524 p_object_version_number := l_object_version_number;
525 p_return_status := hr_multi_message.get_return_status_disable;
526 hr_utility.set_location(' Leaving:' || l_proc,50);
527 end update_realloc_txn_dtl;
528 -- ----------------------------------------------------------------------------
529 -- |-----------------------< update_realloc_txn_period >----------------------|
530 -- ----------------------------------------------------------------------------
531 PROCEDURE update_realloc_txn_period
532 (p_validate in number default hr_api.g_false_num
533 ,p_effective_date in date
534 ,p_txn_detail_id in number default hr_api.g_number
535 ,p_transaction_type in varchar2 default hr_api.g_varchar2
536 ,p_entity_id in number default hr_api.g_number
537 ,p_budget_period_id in number default hr_api.g_number
538 ,p_start_date in date default hr_api.g_date
539 ,p_end_date in date default hr_api.g_date
540 ,p_reallocation_amt in number default hr_api.g_number
541 ,p_reserved_amt in number default hr_api.g_number
542 ,p_reallocation_period_id in number
543 ,p_object_version_number in out nocopy number
544 ,p_return_status out nocopy varchar2
545 ) is
546 --
547 -- Variables for API Boolean parameters
548 l_validate boolean;
549 --
550 -- Variables for IN/OUT parameters
551 l_object_version_number number;
552 --
553 -- Other variables
554 l_proc varchar2(72) := g_package ||'update_realloc_txn_period';
555 Begin
556 hr_utility.set_location(' Entering:' || l_proc,10);
557 --
558 -- Issue a savepoint
559 --
560 savepoint update_realloc_txn_period_swi;
561 --
562 -- Initialise Multiple Message Detection
563 --
564 hr_multi_message.enable_message_list;
565 --
566 -- Remember IN OUT parameter IN values
567 --
568 l_object_version_number := p_object_version_number;
569 --
570 -- Convert constant values to their corresponding boolean value
571 --
572 l_validate :=
573 hr_api.constant_to_boolean
574 (p_constant_value => p_validate);
575 --
576 -- Register Surrogate ID or user key values
577 --
578 --
579 -- Call API
580 --
581 pqh_bdgt_pool_realloctions_api.update_realloc_txn_period
582 (p_validate => l_validate
583 ,p_effective_date => p_effective_date
584 ,p_txn_detail_id => p_txn_detail_id
585 ,p_transaction_type => p_transaction_type
586 ,p_entity_id => p_entity_id
587 ,p_budget_period_id => p_budget_period_id
588 ,p_start_date => p_start_date
589 ,p_end_date => p_end_date
590 ,p_reallocation_amt => p_reallocation_amt
591 ,p_reserved_amt => p_reserved_amt
592 ,p_reallocation_period_id => p_reallocation_period_id
593 ,p_object_version_number => p_object_version_number
594 );
595 --
596 -- Convert API warning boolean parameter values to specific
597 -- messages and add them to Multiple Message List
598 --
599 --
600 -- Convert API non-warning boolean parameter values
601 --
602 --
603 -- Derive the API return status value based on whether
604 -- messages of any type exist in the Multiple Message List.
605 -- Also disable Multiple Message Detection.
606 --
607 p_return_status := hr_multi_message.get_return_status_disable;
608 hr_utility.set_location(' Leaving:' || l_proc,20);
609 --
610 exception
611 when hr_multi_message.error_message_exist then
612 --
613 -- Catch the Multiple Message List exception which
614 -- indicates API processing has been aborted because
615 -- at least one message exists in the list.
616 --
617 rollback to update_realloc_txn_period_swi;
618 --
619 -- Reset IN OUT parameters and set OUT parameters
620 --
621 p_object_version_number := l_object_version_number;
622 p_return_status := hr_multi_message.get_return_status_disable;
623 hr_utility.set_location(' Leaving:' || l_proc, 30);
624 when others then
625 --
626 -- When Multiple Message Detection is enabled catch
627 -- any Application specific or other unexpected
628 -- exceptions. Adding appropriate details to the
629 -- Multiple Message List. Otherwise re-raise the
630 -- error.
631 --
632 rollback to update_realloc_txn_period_swi;
633 if hr_multi_message.unexpected_error_add(l_proc) then
634 hr_utility.set_location(' Leaving:' || l_proc,40);
635 raise;
636 end if;
637 --
638 -- Reset IN OUT and set OUT parameters
639 --
640 p_object_version_number := l_object_version_number;
641 p_return_status := hr_multi_message.get_return_status_disable;
642 hr_utility.set_location(' Leaving:' || l_proc,50);
643 end update_realloc_txn_period;
644 end pqh_bdgt_pool_realloctions_swi;