[Home] [Help]
PACKAGE BODY: APPS.PQH_BUDGET_POOLS_SWI
Source
1 Package Body pqh_budget_pools_swi As
2 /* $Header: pqbplswi.pkb 115.7 2003/04/28 11:41:20 kgowripe noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'pqh_budget_pools_swi.';
7 --
8 --
9 PROCEDURE delete_trxn_amts(p_transaction_type in varchar2 , p_bdgt_trnx_amount_id in number) is
10
11 /*
12
13 This is a private function which deletes Donor / Receiver Transaction Amount
14 for a given bdgt_trnx_amount_id
15
16 Transaction Type :
17 =================
18 DD - Donor Details
19 RD - Receiver Details
20
21 */
22 cursor csr_bdgt_trnx_amt is
23 select reallocation_id, object_version_number
24 from pqh_bdgt_pool_realloctions
25 where reallocation_id = p_bdgt_trnx_amount_id
26 and transaction_type = p_transaction_type;
27
28 cursor csr_receiver_prd_count is
29 select nvl(count(reallocation_id),0)
30 from pqh_bdgt_pool_realloctions
31 where txn_detail_id in (select txn_detail_id
32 from pqh_bdgt_pool_realloctions
33 where reallocation_id = p_bdgt_trnx_amount_id
34 and transaction_type = 'RD');
35 /* Modified by mvanakda
36 Bug No : 2924364
37 Fix : Modified the cursor csr_receiver_id to fetch the receiver id
38 and ovn based on receiver period id (p_bdgt_trnx_amount_id)
39
40 */
41 /* mvankada
42 cursor csr_receiver_id is
43 select txn_detail_id, object_version_number
44 from pqh_bdgt_pool_realloctions
45 where reallocation_id = p_bdgt_trnx_amount_id
46 and transaction_type = 'RD';
47 */
48
49 cursor csr_receiver_id is
50 select reallocation_id, object_version_number
51 from pqh_bdgt_pool_realloctions
52 where reallocation_id in ( select TXN_DETAIL_ID
53 from pqh_bdgt_pool_realloctions
54 where reallocation_id = p_bdgt_trnx_amount_id
55 and transaction_type = 'RD');
56
57 l_ovn number(15);
58 l_bdgt_trnx_amount_id number;
59 l_count number;
60 l_receiver_id number;
61 l_receiver_ovn number;
62 l_proc varchar2(72) := g_package ||'delete_trxn_amts';
63
64 BEGIN
65
66 /*
67 If the Receiver has only one period then delete receiver and receiver period.
68 If the reciver has more than one receiver periods then delete only the given receiver period.
69 */
70 hr_utility.set_location(' Entering:' || l_proc,10);
71 If p_transaction_type = 'RD' then
72 open csr_receiver_prd_count;
73 fetch csr_receiver_prd_count into l_count;
74 close csr_receiver_prd_count;
75 hr_utility.set_location(' Count :' || l_count,15);
76
77 open csr_receiver_id;
78 fetch csr_receiver_id into l_receiver_id, l_receiver_ovn;
79 close csr_receiver_id;
80
81 End If;
82
83 open csr_bdgt_trnx_amt;
84 fetch csr_bdgt_trnx_amt into l_bdgt_trnx_amount_id, l_ovn;
85 close csr_bdgt_trnx_amt;
86 hr_utility.set_location(' l_bdgt_trnx_amount_id :' || l_bdgt_trnx_amount_id,20);
87 --
88 -- Added IF condition to call delete API only when there is a row for the ID passed
89 --
90 IF l_bdgt_trnx_amount_id IS NOT NULL THEN
91 pqh_bdgt_pool_realloctions_api.delete_realloc_txn_period
92 (p_validate => false
93 ,p_reallocation_period_id => l_bdgt_trnx_amount_id
94 ,p_object_version_number => l_ovn
95 );
96 END IF;
97 If (p_transaction_type = 'RD') and (l_count = 1) then
98 pqh_bdgt_pool_realloctions_api.delete_realloc_txn_dtl
99 ( p_validate => false
100 ,p_txn_detail_id => l_receiver_id
101 ,p_object_version_number => l_receiver_ovn
102 );
103 End if;
104 hr_utility.set_location(' Leaving:' || l_proc,25);
105 END delete_trxn_amts;
106
107 PROCEDURE delete_trxn_dtls(p_transaction_type in varchar2 , p_bdgt_trnx_detail_id in number) is
108
109 /*
110 This is a private function which Deletes Donor / Receiver Transaction Details
111 for a given bdgt_trnx_detail_id
112 Transaction Type :
113 =================
114 D - Donor
115 R - Receiver
116 */
117
118 cursor csr_donor_receiver_details is
119 select reallocation_id,transaction_type
120 from pqh_bdgt_pool_realloctions
121 where txn_detail_id = p_bdgt_trnx_detail_id;
122
123 cursor csr_bdgt_trnx_dtl is
124 select reallocation_id,object_version_number
125 from pqh_bdgt_pool_realloctions
126 where reallocation_id = p_bdgt_trnx_detail_id
127 and transaction_type = p_transaction_type;
128
129 l_ovn number(15);
130 l_bdgt_trnx_detail_id number;
131 l_bdgt_trnx_amt_id number;
132 l_transaction_type varchar2(15);
133 l_proc varchar2(72) := g_package ||'delete_trxn_dtls';
134
135 BEGIN
136 hr_utility.set_location(' Entering:' || l_proc,10);
137 If p_transaction_type in ('D','R') then
138 open csr_donor_receiver_details;
139 loop
140 fetch csr_donor_receiver_details into l_bdgt_trnx_amt_id,l_transaction_type;
141 exit when csr_donor_receiver_details%notfound;
142
143
144 /* Delete all Donor/Receiver Amount Details */
145 hr_utility.set_location(' l_bdgt_trnx_amt_id:' || l_bdgt_trnx_amt_id,15);
146 hr_utility.set_location(' l_transaction_type:' || l_transaction_type,20);
147 delete_trxn_amts
148 (p_transaction_type => l_transaction_type,
149 p_bdgt_trnx_amount_id => l_bdgt_trnx_amt_id);
150
151 end loop;
152 close csr_donor_receiver_details;
153
154 /* Delete Donor/Receiver Details */
155 open csr_bdgt_trnx_dtl;
156 fetch csr_bdgt_trnx_dtl into l_bdgt_trnx_detail_id, l_ovn;
157 close csr_bdgt_trnx_dtl;
158 hr_utility.set_location(' l_bdgt_trnx_detail_id :' || l_bdgt_trnx_detail_id ,25);
159 if l_bdgt_trnx_detail_id is not null then
160
161 pqh_bdgt_pool_realloctions_api.delete_realloc_txn_dtl
162 ( p_validate => false
163 ,p_txn_detail_id => l_bdgt_trnx_detail_id
164 ,p_object_version_number => l_ovn
165 );
166 end if;
167 end if;
168 hr_utility.set_location(' Leaving:' || l_proc,30);
169 END delete_trxn_dtls;
170
171
172 PROCEDURE delete_bgt_transaction (p_transaction_id in number) is
173
174 /*
175 This is a private function which Deletes Budget Transaction for
176 a given Budget Transaction Id
177 */
178
179 cursor csr_bdgt_trnx_details is
180 select reallocation_id ,transaction_type
181 from pqh_bdgt_pool_realloctions
182 where pool_id = p_transaction_id;
183
184 cursor csr_bdgt_transaction is
185 select pool_id,object_version_number
186 from pqh_budget_pools
187 where pool_id = p_transaction_id;
188
189 l_ovn number;
190 l_transaction_id number;
191 l_transaction_type varchar2(15);
192 l_proc varchar2(72) := g_package ||'delete_bgt_transaction';
193
194 BEGIN
195 hr_utility.set_location(' Entering:' || l_proc,10);
196 /* fetch all budget transaction details */
197 open csr_bdgt_trnx_details;
198 loop
199 fetch csr_bdgt_trnx_details into l_transaction_id , l_transaction_type ;
200 exit when csr_bdgt_trnx_details%notfound;
201 hr_utility.set_location(' l_transaction_id :' || l_transaction_id ,15);
202 hr_utility.set_location(' l_transaction_type :' || l_transaction_type ,20);
203 delete_trxn_dtls
204 (p_transaction_type => l_transaction_type
205 ,p_bdgt_trnx_detail_id => l_transaction_id );
206 end loop;
207 close csr_bdgt_trnx_details;
208
209
210 open csr_bdgt_transaction;
211 fetch csr_bdgt_transaction into l_transaction_id, l_ovn;
212 close csr_bdgt_transaction;
213 hr_utility.set_location(' l_transaction_id :' || l_transaction_id ,25);
214 if l_transaction_id is not null then
215 pqh_budget_pools_api.delete_reallocation_txn
216 (p_validate => false
217 ,p_transaction_id => l_transaction_id
218 ,p_object_version_number => l_ovn
219 ,p_effective_date => sysdate
220 );
221 end if;
222 hr_utility.set_location(' Leaving:' || l_proc,30);
223 END delete_bgt_transaction;
224
225
226
227 PROCEDURE delete_bgt_folder(p_folder_id in number) is
228 /*
229 This is a private function which Deletes Budget Transaction for a
230 given Budget Folder Id
231 */
232
233 cursor csr_bdgt_transaction is
234 select pool_id
235 from pqh_budget_pools
236 where parent_pool_id = p_folder_id;
237
238
239 cursor csr_bdgt_folder is
240 select pool_id,object_version_number
241 from pqh_budget_pools
242 where pool_id = p_folder_id;
243
244 --added by kgowripe for bug#2875736
245 -- Modifed start with caluse for bug 2880128
246 Cursor csr_process_log_ids IS
247 SELECT process_log_id,object_version_number
248 FROM pqh_process_log
249 WHERE module_cd = 'BUDGET_REALLOCATION'
250 START WITH process_log_id = (SELECT process_log_id
251 FROM pqh_process_log
252 WHERE module_cd = 'BUDGET_REALLOCATION'
253 AND master_process_log_id IS NULL
254 AND txn_id = p_folder_id)
255 CONNECT BY master_process_log_id = PRIOR process_log_id
256 ORDER BY level DESC;
257
258 -- Added by mvankada
259 -- Bug : 2880151
260 Cursor csr_txn_cat_id IS
261 Select wf_transaction_category_id
262 From pqh_budget_pools
263 Where pool_id = p_folder_id;
264
265 l_ovn number;
266 l_folder_id number;
267 l_transaction_id number;
268 l_itemkey varchar2(2000);
269 l_txn_cat_id number;
270 l_proc varchar2(72) := g_package ||'delete_trxn_dtls';
271
272 BEGIN
273 hr_utility.set_location(' Entering:' || l_proc,10);
274 open csr_txn_cat_id;
275 fetch csr_txn_cat_id into l_txn_cat_id;
276 close csr_txn_cat_id;
277 hr_utility.set_location(' Txn Cat Id :' || l_txn_cat_id , 12);
278 if l_txn_cat_id is not null then
279 l_itemkey := l_txn_cat_id || '-' || p_folder_id;
280 end if;
281 hr_utility.set_location(' Item Key :' || l_itemkey , 12);
282 /* fetch all budget transactions*/
283 open csr_bdgt_transaction;
284 loop
285 fetch csr_bdgt_transaction into l_transaction_id;
286 exit when csr_bdgt_transaction%notfound;
287 delete_bgt_transaction
288 (p_transaction_id => l_transaction_id);
289 end loop;
290 close csr_bdgt_transaction;
291
292 open csr_bdgt_folder;
293 fetch csr_bdgt_folder into l_folder_id, l_ovn;
294 close csr_bdgt_folder;
295 hr_utility.set_location(' Folder Id :' || l_folder_id ,15);
296
297 if l_folder_id is not null then
298
299 pqh_budget_pools_api.delete_reallocation_folder
300 (p_validate => false
301 ,p_folder_id => l_folder_id
302 ,p_object_version_number => l_ovn
303 ,p_effective_date => sysdate
304 );
305 end if;
306
307 --Added by kgowripe for bug#2875736
308 --For deleting the process log entries for a folder
309 FOR plg IN csr_process_log_ids
310 LOOP
311 hr_utility.set_location(' Process Log Id :' || plg.process_log_id ,25);
312 hr_utility.set_location(' OVN :' || plg.object_version_number ,35);
313
314 pqh_process_log_api.delete_process_log(p_process_log_id => plg.process_log_id
315 ,p_object_version_number=> plg.object_version_number
316 ,p_effective_date => sysdate);
317 END LOOP;
318 -- Added by mvanakda
319 -- Bug : 2880151
320 hr_utility.set_location(' call to delete folder from inbox:' || l_proc,40);
321 wf_engine.AbortProcess(itemtype => 'PQHGEN',
322 itemkey => l_itemkey ,
323 process => 'PQH_ROUTING',
324 result => null);
325
326 hr_utility.set_location(' Leaving:' || l_proc,45);
327 Exception
328 When others then
329 Null;
330 END delete_bgt_folder;
331 --
332
333 --
334 -- ----------------------------------------------------------------------------
335 -- |----------------------< create_reallocation_folder >----------------------|
336 -- ----------------------------------------------------------------------------
337 PROCEDURE create_reallocation_folder
338 (p_validate in number default hr_api.g_false_num
339 ,p_effective_date in date
340 ,p_folder_id out nocopy number
341 ,p_name in varchar2
342 ,p_budget_version_id in number
343 ,p_budget_unit_id in number
344 ,p_entity_type in varchar2
345 ,p_approval_status in varchar2
346 ,p_object_version_number out nocopy number
347 ,p_business_group_id in number
348 ,p_wf_transaction_category_id in number
349 ,p_return_status out nocopy varchar2
350 ) is
351 --
352 -- Variables for API Boolean parameters
353 l_validate boolean;
354 --
355 -- Variables for IN/OUT parameters
356 --
357 -- Other variables
358 l_proc varchar2(72) := g_package ||'create_reallocation_folder';
359 Begin
360 hr_utility.set_location(' Entering:' || l_proc,10);
361 --
362 -- Issue a savepoint
363 --
364 savepoint create_reallocation_folder_swi;
365 --
366 -- Initialise Multiple Message Detection
367 --
368 hr_multi_message.enable_message_list;
369 --
370 -- Remember IN OUT parameter IN values
371 --
372 --
373 -- Convert constant values to their corresponding boolean value
374 --
375 l_validate :=
376 hr_api.constant_to_boolean
377 (p_constant_value => p_validate);
378 --
379 -- Register Surrogate ID or user key values
380 --
381 --
382 -- Call API
383 --
384 pqh_budget_pools_api.create_reallocation_folder
385 (p_validate => l_validate
386 ,p_effective_date => p_effective_date
387 ,p_folder_id => p_folder_id
388 ,p_name => p_name
389 ,p_budget_version_id => p_budget_version_id
390 ,p_budget_unit_id => p_budget_unit_id
391 ,p_entity_type => p_entity_type
392 ,p_approval_status => p_approval_status
393 ,p_object_version_number => p_object_version_number
394 ,p_business_group_id => p_business_group_id
395 ,p_wf_transaction_category_id => p_wf_transaction_category_id
396 );
397 --
398 -- Convert API warning boolean parameter values to specific
399 -- messages and add them to Multiple Message List
400 --
401 --
402 -- Convert API non-warning boolean parameter values
403 --
404 --
405 -- Derive the API return status value based on whether
406 -- messages of any type exist in the Multiple Message List.
407 -- Also disable Multiple Message Detection.
408 --
409 p_return_status := hr_multi_message.get_return_status_disable;
410 hr_utility.set_location(' Leaving:' || l_proc,20);
411 --
412 exception
413 when hr_multi_message.error_message_exist then
414 --
415 -- Catch the Multiple Message List exception which
416 -- indicates API processing has been aborted because
417 -- at least one message exists in the list.
418 --
419 rollback to create_reallocation_folder_swi;
420 --
421 -- Reset IN OUT parameters and set OUT parameters
422 --
423 p_folder_id := null;
424 p_object_version_number := null;
425 p_return_status := hr_multi_message.get_return_status_disable;
426 hr_utility.set_location(' Leaving:' || l_proc, 30);
427 when others then
428 --
429 -- When Multiple Message Detection is enabled catch
430 -- any Application specific or other unexpected
431 -- exceptions. Adding appropriate details to the
432 -- Multiple Message List. Otherwise re-raise the
433 -- error.
434 --
435 rollback to create_reallocation_folder_swi;
436 if hr_multi_message.unexpected_error_add(l_proc) then
437 hr_utility.set_location(' Leaving:' || l_proc,40);
438 raise;
439 end if;
440 --
441 -- Reset IN OUT and set OUT parameters
442 --
443 p_folder_id := null;
444 p_object_version_number := null;
445 p_return_status := hr_multi_message.get_return_status_disable;
446 hr_utility.set_location(' Leaving:' || l_proc,50);
447 end create_reallocation_folder;
448 -- ----------------------------------------------------------------------------
449 -- |------------------------< create_reallocation_txn >-----------------------|
450 -- ----------------------------------------------------------------------------
451 PROCEDURE create_reallocation_txn
452 (p_validate in number default hr_api.g_false_num
453 ,p_effective_date in date
454 ,p_transaction_id out nocopy number
455 ,p_name in varchar2
456 ,p_parent_folder_id in number
457 ,p_object_version_number out nocopy number
458 ,p_business_group_id in number
459 ,p_return_status out nocopy varchar2
460 ) is
461 --
462 -- Variables for API Boolean parameters
463 l_validate boolean;
464 --
465 -- Variables for IN/OUT parameters
466 --
467 -- Other variables
468 l_proc varchar2(72) := g_package ||'create_reallocation_txn';
469 Begin
470 hr_utility.set_location(' Entering:' || l_proc,10);
471 --
472 -- Issue a savepoint
473 --
474 savepoint create_reallocation_txn_swi;
475 --
476 -- Initialise Multiple Message Detection
477 --
478 hr_multi_message.enable_message_list;
479 --
480 -- Remember IN OUT parameter IN values
481 --
482 --
483 -- Convert constant values to their corresponding boolean value
484 --
485 l_validate :=
486 hr_api.constant_to_boolean
487 (p_constant_value => p_validate);
488 --
489 -- Register Surrogate ID or user key values
490 --
491 --
492 -- Call API
493 --
494 pqh_budget_pools_api.create_reallocation_txn
495 (p_validate => l_validate
496 ,p_effective_date => p_effective_date
497 ,p_transaction_id => p_transaction_id
498 ,p_name => p_name
499 ,p_parent_folder_id => p_parent_folder_id
500 ,p_object_version_number => p_object_version_number
501 ,p_business_group_id => p_business_group_id
502 );
503 --
504 -- Convert API warning boolean parameter values to specific
505 -- messages and add them to Multiple Message List
506 --
507 --
508 -- Convert API non-warning boolean parameter values
509 --
510 --
511 -- Derive the API return status value based on whether
512 -- messages of any type exist in the Multiple Message List.
513 -- Also disable Multiple Message Detection.
514 --
515 p_return_status := hr_multi_message.get_return_status_disable;
516 hr_utility.set_location(' Leaving:' || l_proc,20);
517 --
518 exception
519 when hr_multi_message.error_message_exist then
520 --
521 -- Catch the Multiple Message List exception which
522 -- indicates API processing has been aborted because
523 -- at least one message exists in the list.
524 --
525 rollback to create_reallocation_txn_swi;
526 --
527 -- Reset IN OUT parameters and set OUT parameters
528 --
529 p_transaction_id := null;
530 p_object_version_number := null;
531 p_return_status := hr_multi_message.get_return_status_disable;
532 hr_utility.set_location(' Leaving:' || l_proc, 30);
533 when others then
534 --
535 -- When Multiple Message Detection is enabled catch
536 -- any Application specific or other unexpected
537 -- exceptions. Adding appropriate details to the
538 -- Multiple Message List. Otherwise re-raise the
539 -- error.
540 --
541 rollback to create_reallocation_txn_swi;
542 if hr_multi_message.unexpected_error_add(l_proc) then
543 hr_utility.set_location(' Leaving:' || l_proc,40);
544 raise;
545 end if;
546 --
547 -- Reset IN OUT and set OUT parameters
548 --
549 p_transaction_id := null;
550 p_object_version_number := null;
551 p_return_status := hr_multi_message.get_return_status_disable;
552 hr_utility.set_location(' Leaving:' || l_proc,50);
553 end create_reallocation_txn;
554 -- ----------------------------------------------------------------------------
555 -- |----------------------< delete_reallocation_folder >----------------------|
556 -- ----------------------------------------------------------------------------
557 PROCEDURE delete_reallocation_folder
558 (p_validate in number default hr_api.g_false_num
559 ,p_folder_id in number
560 ,p_object_version_number in number
561 ,p_effective_date in date
562 ,p_return_status out nocopy varchar2
563 ) is
564 --
565 -- Variables for API Boolean parameters
566 l_validate boolean;
567 --
568 -- Variables for IN/OUT parameters
569 --
570 -- Other variables
571 l_proc varchar2(72) := g_package ||'delete_reallocation_folder';
572 Begin
573 hr_utility.set_location(' Entering:' || l_proc,10);
574 --
575 -- Issue a savepoint
576 --
577 savepoint delete_reallocation_folder_swi;
578 --
579 -- Initialise Multiple Message Detection
580 --
581 hr_multi_message.enable_message_list;
582 --
583 -- Remember IN OUT parameter IN values
584 --
585 --
586 -- Convert constant values to their corresponding boolean value
587 --
588 l_validate :=
589 hr_api.constant_to_boolean
590 (p_constant_value => p_validate);
591 --
592 -- Register Surrogate ID or user key values
593 --
594 --
595 -- Call API
596 --
597 pqh_budget_pools_api.delete_reallocation_folder
598 (p_validate => l_validate
602 );
599 ,p_folder_id => p_folder_id
600 ,p_object_version_number => p_object_version_number
601 ,p_effective_date => p_effective_date
603 --
604 -- Convert API warning boolean parameter values to specific
605 -- messages and add them to Multiple Message List
606 --
607 --
608 -- Convert API non-warning boolean parameter values
609 --
610 --
611 -- Derive the API return status value based on whether
612 -- messages of any type exist in the Multiple Message List.
613 -- Also disable Multiple Message Detection.
614 --
615 p_return_status := hr_multi_message.get_return_status_disable;
616 hr_utility.set_location(' Leaving:' || l_proc,20);
617 --
618 exception
619 when hr_multi_message.error_message_exist then
620 --
621 -- Catch the Multiple Message List exception which
622 -- indicates API processing has been aborted because
623 -- at least one message exists in the list.
624 --
625 rollback to delete_reallocation_folder_swi;
626 --
627 -- Reset IN OUT parameters and set OUT parameters
628 --
629 p_return_status := hr_multi_message.get_return_status_disable;
630 hr_utility.set_location(' Leaving:' || l_proc, 30);
631 when others then
632 --
633 -- When Multiple Message Detection is enabled catch
634 -- any Application specific or other unexpected
635 -- exceptions. Adding appropriate details to the
636 -- Multiple Message List. Otherwise re-raise the
637 -- error.
638 --
639 rollback to delete_reallocation_folder_swi;
640 if hr_multi_message.unexpected_error_add(l_proc) then
641 hr_utility.set_location(' Leaving:' || l_proc,40);
642 raise;
643 end if;
644 --
645 -- Reset IN OUT and set OUT parameters
646 --
647 p_return_status := hr_multi_message.get_return_status_disable;
648 hr_utility.set_location(' Leaving:' || l_proc,50);
649 end delete_reallocation_folder;
650 -- ----------------------------------------------------------------------------
651 -- |------------------------< delete_reallocation_txn >-----------------------|
652 -- ----------------------------------------------------------------------------
653 PROCEDURE delete_reallocation_txn
654 (p_validate in number default hr_api.g_false_num
655 ,p_transaction_id in number
656 ,p_object_version_number in number
657 ,p_effective_date in date
658 ,p_return_status out nocopy varchar2
659 ) is
660 --
661 -- Variables for API Boolean parameters
662 l_validate boolean;
663 --
664 -- Variables for IN/OUT parameters
665 --
666 -- Other variables
667 l_proc varchar2(72) := g_package ||'delete_reallocation_txn';
668 Begin
669 hr_utility.set_location(' Entering:' || l_proc,10);
670 --
671 -- Issue a savepoint
672 --
673 savepoint delete_reallocation_txn_swi;
674 --
675 -- Initialise Multiple Message Detection
676 --
677 hr_multi_message.enable_message_list;
678 --
679 -- Remember IN OUT parameter IN values
680 --
681 --
682 -- Convert constant values to their corresponding boolean value
683 --
684 l_validate :=
685 hr_api.constant_to_boolean
686 (p_constant_value => p_validate);
687 --
688 -- Register Surrogate ID or user key values
689 --
690 --
691 -- Call API
692 --
693 pqh_budget_pools_api.delete_reallocation_txn
694 (p_validate => l_validate
695 ,p_transaction_id => p_transaction_id
696 ,p_object_version_number => p_object_version_number
697 ,p_effective_date => p_effective_date
698 );
699 --
700 -- Convert API warning boolean parameter values to specific
701 -- messages and add them to Multiple Message List
702 --
703 --
704 -- Convert API non-warning boolean parameter values
705 --
706 --
707 -- Derive the API return status value based on whether
708 -- messages of any type exist in the Multiple Message List.
709 -- Also disable Multiple Message Detection.
710 --
711 p_return_status := hr_multi_message.get_return_status_disable;
712 hr_utility.set_location(' Leaving:' || l_proc,20);
713 --
714 exception
715 when hr_multi_message.error_message_exist then
716 --
717 -- Catch the Multiple Message List exception which
718 -- indicates API processing has been aborted because
719 -- at least one message exists in the list.
720 --
721 rollback to delete_reallocation_txn_swi;
722 --
723 -- Reset IN OUT parameters and set OUT parameters
724 --
725 p_return_status := hr_multi_message.get_return_status_disable;
726 hr_utility.set_location(' Leaving:' || l_proc, 30);
727 when others then
728 --
729 -- When Multiple Message Detection is enabled catch
730 -- any Application specific or other unexpected
731 -- exceptions. Adding appropriate details to the
732 -- Multiple Message List. Otherwise re-raise the
733 -- error.
734 --
735 rollback to delete_reallocation_txn_swi;
736 if hr_multi_message.unexpected_error_add(l_proc) then
737 hr_utility.set_location(' Leaving:' || l_proc,40);
738 raise;
739 end if;
740 --
741 -- Reset IN OUT and set OUT parameters
742 --
743 p_return_status := hr_multi_message.get_return_status_disable;
744 hr_utility.set_location(' Leaving:' || l_proc,50);
745 end delete_reallocation_txn;
749 PROCEDURE update_reallocation_folder
746 -- ----------------------------------------------------------------------------
747 -- |----------------------< update_reallocation_folder >----------------------|
748 -- ----------------------------------------------------------------------------
750 (p_validate in number default hr_api.g_false_num
751 ,p_effective_date in date
752 ,p_folder_id in number
753 ,p_name in varchar2 default hr_api.g_varchar2
754 ,p_budget_version_id in number default hr_api.g_number
755 ,p_budget_unit_id in number default hr_api.g_number
756 ,p_entity_type in varchar2 default hr_api.g_varchar2
757 ,p_approval_status in varchar2 default hr_api.g_varchar2
758 ,p_object_version_number in out nocopy number
759 ,p_business_group_id in number
760 ,p_wf_transaction_category_id in number
761 ,p_return_status out nocopy varchar2
762 ) is
763 --
764 -- Variables for API Boolean parameters
765 l_validate boolean;
766 --
767 -- Variables for IN/OUT parameters
768 l_object_version_number number;
769 --
770 -- Other variables
771 l_proc varchar2(72) := g_package ||'update_reallocation_folder';
772 Begin
773 hr_utility.set_location(' Entering:' || l_proc,10);
774 --
775 -- Issue a savepoint
776 --
777 savepoint update_reallocation_folder_swi;
778 --
779 -- Initialise Multiple Message Detection
780 --
781 hr_multi_message.enable_message_list;
782 --
783 -- Remember IN OUT parameter IN values
784 --
785 l_object_version_number := p_object_version_number;
786 --
787 -- Convert constant values to their corresponding boolean value
788 --
789 l_validate :=
790 hr_api.constant_to_boolean
791 (p_constant_value => p_validate);
792 --
793 -- Register Surrogate ID or user key values
794 --
795 --
796 -- Call API
797 --
798 pqh_budget_pools_api.update_reallocation_folder
799 (p_validate => l_validate
800 ,p_effective_date => p_effective_date
801 ,p_folder_id => p_folder_id
802 ,p_name => p_name
803 ,p_budget_version_id => p_budget_version_id
804 ,p_budget_unit_id => p_budget_unit_id
805 ,p_entity_type => p_entity_type
806 ,p_approval_status => p_approval_status
807 ,p_object_version_number => p_object_version_number
808 ,p_business_group_id => p_business_group_id
809 ,p_wf_transaction_category_id => p_wf_transaction_category_id
810 );
811 --
812 -- Convert API warning boolean parameter values to specific
813 -- messages and add them to Multiple Message List
814 --
815 --
816 -- Convert API non-warning boolean parameter values
817 --
818 --
819 -- Derive the API return status value based on whether
820 -- messages of any type exist in the Multiple Message List.
821 -- Also disable Multiple Message Detection.
822 --
823 p_return_status := hr_multi_message.get_return_status_disable;
824 hr_utility.set_location(' Leaving:' || l_proc,20);
825 --
826 exception
827 when hr_multi_message.error_message_exist then
828 --
829 -- Catch the Multiple Message List exception which
830 -- indicates API processing has been aborted because
831 -- at least one message exists in the list.
832 --
833 rollback to update_reallocation_folder_swi;
834 --
835 -- Reset IN OUT parameters and set OUT parameters
836 --
837 p_object_version_number := l_object_version_number;
838 p_return_status := hr_multi_message.get_return_status_disable;
839 hr_utility.set_location(' Leaving:' || l_proc, 30);
840 when others then
841 --
842 -- When Multiple Message Detection is enabled catch
843 -- any Application specific or other unexpected
844 -- exceptions. Adding appropriate details to the
845 -- Multiple Message List. Otherwise re-raise the
846 -- error.
847 --
848 rollback to update_reallocation_folder_swi;
849 if hr_multi_message.unexpected_error_add(l_proc) then
850 hr_utility.set_location(' Leaving:' || l_proc,40);
851 raise;
852 end if;
853 --
854 -- Reset IN OUT and set OUT parameters
855 --
856 p_object_version_number := l_object_version_number;
857 p_return_status := hr_multi_message.get_return_status_disable;
858 hr_utility.set_location(' Leaving:' || l_proc,50);
859 end update_reallocation_folder;
860 -- ----------------------------------------------------------------------------
861 -- |------------------------< update_reallocation_txn >-----------------------|
862 -- ----------------------------------------------------------------------------
863 PROCEDURE update_reallocation_txn
864 (p_validate in number default hr_api.g_false_num
865 ,p_effective_date in date
866 ,p_transaction_id in number
867 ,p_name in varchar2 default hr_api.g_varchar2
868 ,p_parent_folder_id in number default hr_api.g_number
869 ,p_object_version_number in out nocopy number
870 ,p_business_group_id in number default hr_api.g_number
871 ,p_return_status out nocopy varchar2
872 ) is
873 --
874 -- Variables for API Boolean parameters
875 l_validate boolean;
876 --
877 -- Variables for IN/OUT parameters
878 l_object_version_number number;
879 --
880 -- Other variables
881 l_proc varchar2(72) := g_package ||'update_reallocation_txn';
882 Begin
883 hr_utility.set_location(' Entering:' || l_proc,10);
884 --
885 -- Issue a savepoint
886 --
887 savepoint update_reallocation_txn_swi;
888 --
889 -- Initialise Multiple Message Detection
890 --
891 hr_multi_message.enable_message_list;
892 --
893 -- Remember IN OUT parameter IN values
894 --
895 l_object_version_number := p_object_version_number;
896 --
897 -- Convert constant values to their corresponding boolean value
898 --
899 l_validate :=
900 hr_api.constant_to_boolean
901 (p_constant_value => p_validate);
902 --
903 -- Register Surrogate ID or user key values
904 --
905 --
906 -- Call API
907 --
908 pqh_budget_pools_api.update_reallocation_txn
909 (p_validate => l_validate
910 ,p_effective_date => p_effective_date
911 ,p_transaction_id => p_transaction_id
912 ,p_name => p_name
913 ,p_parent_folder_id => p_parent_folder_id
914 ,p_object_version_number => p_object_version_number
915 ,p_business_group_id => p_business_group_id
916 );
917 --
918 -- Convert API warning boolean parameter values to specific
919 -- messages and add them to Multiple Message List
920 --
921 --
922 -- Convert API non-warning boolean parameter values
923 --
924 --
925 -- Derive the API return status value based on whether
926 -- messages of any type exist in the Multiple Message List.
927 -- Also disable Multiple Message Detection.
928 --
929 p_return_status := hr_multi_message.get_return_status_disable;
930 hr_utility.set_location(' Leaving:' || l_proc,20);
931 --
932 exception
933 when hr_multi_message.error_message_exist then
934 --
935 -- Catch the Multiple Message List exception which
936 -- indicates API processing has been aborted because
937 -- at least one message exists in the list.
938 --
939 rollback to update_reallocation_txn_swi;
940 --
941 -- Reset IN OUT parameters and set OUT parameters
942 --
943 p_object_version_number := l_object_version_number;
944 p_return_status := hr_multi_message.get_return_status_disable;
945 hr_utility.set_location(' Leaving:' || l_proc, 30);
946 when others then
947 --
948 -- When Multiple Message Detection is enabled catch
949 -- any Application specific or other unexpected
950 -- exceptions. Adding appropriate details to the
951 -- Multiple Message List. Otherwise re-raise the
952 -- error.
953 --
954 rollback to update_reallocation_txn_swi;
955 if hr_multi_message.unexpected_error_add(l_proc) then
956 hr_utility.set_location(' Leaving:' || l_proc,40);
957 raise;
958 end if;
959 --
960 -- Reset IN OUT and set OUT parameters
961 --
962 p_object_version_number := l_object_version_number;
963 p_return_status := hr_multi_message.get_return_status_disable;
964 hr_utility.set_location(' Leaving:' || l_proc,50);
965 end update_reallocation_txn;
966
967 PROCEDURE bgt_realloc_delete(p_node_type in varchar2, p_node_id in number)
968 is
969
970 /*
971 Node Type :
972 ==========
973 F- Folder
974 T - Transaction
975 D - Donor
976 DD - Donor Details
977 R - Receiver
978 RD - Receiver Details
979 */
980 l_proc varchar2(72) := g_package ||'bgt_realloc_delete';
981 BEGIN
982
983 hr_utility.set_location(' Entering:' || l_proc,10);
984 hr_utility.set_location(' Node Type :' || p_node_type ,20);
985 --added by kgowripe for bug#2875736
986 --
987 -- Initialise Multiple Message Detection
988 --
989 hr_multi_message.enable_message_list;
990
991 If p_node_type in ('DD','RD') then
992 delete_trxn_amts
993 ( p_transaction_type => p_node_type
994 ,p_bdgt_trnx_amount_id => p_node_id);
995
996 Elsif p_node_type in ('D','R') then
997 delete_trxn_dtls
998 (p_transaction_type => p_node_type
999 ,p_bdgt_trnx_detail_id => p_node_id);
1000
1001 Elsif p_node_type = 'T' then
1002 delete_bgt_transaction
1003 (p_transaction_id => p_node_id);
1004
1005 Else
1006 delete_bgt_folder
1007 (p_folder_id => p_node_id);
1008
1009 End if;
1010 hr_utility.set_location(' Leaving:' || l_proc,30);
1011
1012 END bgt_realloc_delete;
1013 end pqh_budget_pools_swi;