[Home] [Help]
PACKAGE BODY: APPS.PSB_SUBMIT_REVISION_PVT
Source
1 PACKAGE BODY PSB_Submit_Revision_PVT AS
2 /* $Header: PSBVBRSB.pls 120.10 2006/01/17 18:39:57 matthoma ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PSB_Submit_Revision_PVT';
5
6
7 /*--------------------------- Global variables -----------------------------*/
8
9 --
10 -- CALLBACK procedure related global information.
11 --
12
13 g_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
14 g_budgetgroup_name psb_budget_groups.name%TYPE ;
15 g_requestor_name VARCHAR2(100);
16 g_itemtype VARCHAR2(2000) ;
17 g_itemkey VARCHAR2(2000) ;
18
19 -- For Bug 4475288: Changed approver_name, approver_display_name lenghts.
20 TYPE g_approver_rec_type IS RECORD
21 (approver_name wf_roles.name%TYPE,
22 approver_display_name wf_roles.display_name%TYPE,
23 item_key VARCHAR2(240),
24 sequence number);
25
26 TYPE g_approver_tbl_type IS TABLE OF g_approver_rec_type
27 INDEX BY BINARY_INTEGER;
28
29 g_approvers g_approver_tbl_type;
30 g_num_approvers NUMBER := 0;
31
32
33 --
34 -- WHO columns variables
35 --
36
37 g_current_date DATE := sysdate ;
38 g_current_user_id NUMBER := NVL( Fnd_Global.User_Id , 0) ;
39 g_current_login_id NUMBER := NVL( Fnd_Global.Login_Id , 0) ;
40 g_user_name VARCHAR2(100);
41
42 /*----------------------- End Global variables -----------------------------*/
43
44
45
46 /*===========================================================================+
47 | PROCEDURE Start_Process |
48 +===========================================================================*/
49 --
50 -- The API creates an instance of the item type 'PSBBR' and starts the workflow
51 -- process 'Submit Budget Revision'.
52 --
53 PROCEDURE Start_Process
54 (
55 p_api_version IN NUMBER ,
56 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
57 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
58 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
59 p_return_status OUT NOCOPY VARCHAR2 ,
60 p_msg_count OUT NOCOPY NUMBER ,
61 p_msg_data OUT NOCOPY VARCHAR2 ,
62 --
63 p_item_key IN VARCHAR2 ,
64 p_submitter_id IN NUMBER ,
65 p_submitter_name IN VARCHAR2 ,
66 p_operation_type IN VARCHAR2 ,
67 p_orig_system IN VARCHAR2 ,
68 p_comments IN VARCHAR2 ,
69 p_operation_id IN NUMBER ,
70 p_constraint_set_id IN NUMBER
71 )
72 IS
73 --
74 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process' ;
75 l_api_version CONSTANT NUMBER := 1.0 ;
76 --
77 l_return_status VARCHAR2(1) ;
78 l_msg_count NUMBER ;
79 l_msg_data VARCHAR2(2000) ;
80 --
81 l_ItemType VARCHAR2(100) := 'PSBBR';
82 l_ItemKey VARCHAR2(240) := p_item_key;
83 --
84 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
85 l_comments VARCHAR2(2000) ;
86 l_requestor_name VARCHAR2(100);
87 l_justification VARCHAR2(240);
88
89 Cursor C_Requestor is
90 Select requestor,user_name
91 from psb_budget_revisions pbr,
92 fnd_user fu
93 where budget_revision_id = l_budget_revision_id
94 and requestor = user_id;
95 --
96 Cursor C_Justification is
97 Select justification
98 from psb_budget_revisions
99 where budget_revision_id = l_budget_revision_id;
100 --
101 BEGIN
102 --
103 --
104
105 /* Bug 2576222 Start */
106 g_user_name := fnd_global.user_name;
107 /* Bug 2576222 End */
108
109 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
110 FND_MSG_PUB.initialize ;
111 END IF;
112 --
113 p_return_status := FND_API.G_RET_STS_SUCCESS ;
114
115 --
116 -- Get p_item_key related information.
117 --
118 SELECT worksheet_id INTO l_budget_revision_id
119 FROM psb_workflow_processes
120 WHERE item_key = p_item_key
121 AND document_type = 'BR';
122
123
124 wf_engine.CreateProcess ( ItemType => l_ItemType,
125 ItemKey => l_ItemKey,
126 Process => 'SUBMIT_REVISION' );
127
128 --
129 -- Set budget_revision_id as the Item User Key for the process.
130 --
131
132 WF_Engine.SetItemUserKey
133 (
134 ItemType => l_ItemType ,
135 ItemKey => l_ItemKey ,
136 UserKey => l_budget_revision_id
137 );
138
139 --
140 -- Populate item type.
141 --
142 wf_engine.SetItemAttrNumber( ItemType => l_ItemType,
143 ItemKey => l_itemkey,
144 aname => 'BUDGET_REVISION_ID',
145 avalue => l_budget_revision_id );
146 --
147 wf_engine.SetItemAttrText( ItemType => l_itemtype,
148 ItemKey => l_itemkey,
149 aname => 'SUBMITTER_ID',
150 avalue => p_submitter_id );
151 --
152 wf_engine.SetItemAttrText( ItemType => l_itemtype,
153 ItemKey => l_itemkey,
154 aname => 'SUBMITTER_NAME',
155 avalue => p_submitter_name );
156
157 /* Bug 2576222 Start */
158 wf_engine.SetItemAttrtext( ItemType => l_ItemType,
159 ItemKey => l_itemkey,
160 aname => 'FROM_ROLE',
161 avalue => g_user_name );
162 /* Bug 2576222 End */
163 --
164 wf_engine.SetItemAttrText( ItemType => l_itemtype,
165 ItemKey => l_itemkey,
166 aname => 'OPERATION_TYPE',
167 avalue => p_operation_type );
168
169 --
170 wf_engine.SetItemAttrNumber( ItemType => l_ItemType,
171 ItemKey => l_itemkey,
172 aname => 'LOOP_VISITED_COUNTER',
173 avalue => 0 );
174
175 --
176 wf_engine.SetItemAttrText( ItemType => l_itemtype,
177 ItemKey => l_itemkey,
178 aname => 'ORIG_SYSTEM',
179 avalue => p_orig_system );
180 --
181 wf_engine.SetItemAttrNumber( ItemType => l_itemtype,
182 ItemKey => l_itemkey,
183 aname => 'OPERATION_ID',
184 avalue => p_operation_id );
185 --
186 wf_engine.SetItemAttrNumber( ItemType => l_itemtype,
187 ItemKey => l_itemkey,
188 aname => 'CONSTRAINT_SET_ID',
189 avalue => p_constraint_set_id );
190 --
191
192 --
193 -- Populate comments.
194 --
195 IF p_comments = 'Y' THEN
196
197 -- Retrieve the comments.
198 SELECT comments INTO l_comments
199 FROM psb_ws_submit_comments
200 WHERE operation_id = p_operation_id;
201 --
202 wf_engine.SetItemAttrText( ItemType => l_itemtype,
203 ItemKey => l_itemkey,
204 aname => 'COMMENTS',
205 avalue => l_comments );
206 END IF;
207
208
209 For C_Justification_Rec in C_Justification
210 Loop
211 l_justification := C_Justification_Rec.justification;
212 End Loop;
213
214 wf_engine.SetItemAttrText( ItemType => l_itemtype,
215 ItemKey => l_itemkey,
216 aname => 'JUSTIFICATION',
217 avalue => l_justification );
218
219 For C_Requestor_Rec in C_Requestor
220 Loop
221 l_requestor_name := C_Requestor_Rec.user_name;
222 End Loop;
223
224
225 g_requestor_name := l_requestor_name;
226
227 wf_engine.SetItemAttrText( ItemType => l_itemtype,
228 ItemKey => l_itemkey,
229 aname => 'REQUESTOR_NAME',
230 avalue => l_requestor_name );
231 --
232 -- Start the process
233 --
234 wf_engine.StartProcess ( ItemType => l_ItemType,
235 ItemKey => l_ItemKey );
236
237 --
238 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
239 p_data => p_msg_data );
240 --
241 EXCEPTION
242 --
243 WHEN OTHERS THEN
244 --
245 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 --
247 RAISE ;
248
249 END Start_Process ;
250 /*---------------------------------------------------------------------------*/
251
252
253
254 /*===========================================================================+
255 | PROCEDURE Populate_Revision |
256 +===========================================================================*/
257 --
258 -- The API populates the item attribues of the item type 'PSBBR'.
259 -- ( The SUBMITTER_NAME is populated by the interface API 'PSBWKFLB.pls'.)
260 --
261 PROCEDURE Populate_Revision
262 (
263 itemtype IN VARCHAR2,
264 itemkey IN VARCHAR2,
265 actid IN NUMBER,
266 funcmode IN VARCHAR2,
267 result OUT NOCOPY VARCHAR2
268 )
269 IS
270 --
271 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
272 l_budget_group_name psb_budget_groups.name%TYPE ;
273 l_submitter_name VARCHAR2(80);
274 --
275 l_orig_system VARCHAR2(8) ;
276 l_submitter_id NUMBER ;
277 l_tmp_char VARCHAR2(200) ;
278 --
279 BEGIN
280 --
281 IF ( funcmode = 'RUN' ) THEN
282 --
283 -- Get budget_revision_id item_attribute.
284 --
285 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'BUDGET_REVISION_ID');
288
289 --
290 -- Finding Budget Revision information.
291 --
292 SELECT budget_group_name
293 INTO
294 l_budget_group_name
295 FROM psb_budget_revisions_v
296 WHERE budget_revision_id = l_budget_revision_id;
297
298 --
299 wf_engine.SetItemAttrText ( itemtype => itemtype,
300 itemkey => itemkey,
301 aname => 'BUDGET_GROUP_NAME',
302 avalue => l_budget_group_name );
303 --
304 result := 'COMPLETE' ;
305 END IF ;
306
307 IF ( funcmode = 'CANCEL' ) THEN
308 result := 'COMPLETE' ;
309 END IF;
310 --
311
312 --
313 -- In future implementations, appropriate code is to be inserted here.
314 --
315 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
316 result := '' ;
317 END IF;
318
319 EXCEPTION
320 --
321 WHEN OTHERS THEN
322 wf_core.context('PSBBR', 'Populate_Revision',
323 itemtype, itemkey, to_char(actid), funcmode);
324 RAISE ;
325
326 END Populate_Revision ;
327 /*---------------------------------------------------------------------------*/
328
329
330
331 /*===========================================================================+
332 | PROCEDURE Enforce_Concurrency_Check |
333 +===========================================================================*/
334 --
335 -- The activity implements Enforce_Concurrency_Check workflow activity.
336 --
337 PROCEDURE Enforce_Concurrency_Check
338 (
339 itemtype IN VARCHAR2,
340 itemkey IN VARCHAR2,
341 actid IN NUMBER,
342 funcmode IN VARCHAR2,
343 result OUT NOCOPY VARCHAR2
344 )
345 IS
346 --
347 l_return_status VARCHAR2(1) ;
348 l_msg_count NUMBER ;
349 l_msg_data VARCHAR2(2000) ;
350 --
351 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
352 l_operation_type VARCHAR2(20) ;
353 --
354 BEGIN
355
356 l_return_status := FND_API.G_RET_STS_SUCCESS ;
357
358 IF ( funcmode = 'RUN' ) THEN
359 --
360 -- Get operation_type item_attribute.
361 --
362 l_budget_revision_id := wf_engine.GetItemAttrNumber
363 ( itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'BUDGET_REVISION_ID' );
366
367 l_operation_type := wf_engine.GetItemAttrText
368 ( itemtype => itemtype,
369 itemkey => itemkey,
370 aname => 'OPERATION_TYPE' );
371 --
372 -- API to perform Revision related concurrency control.
373 --
374 PSB_Create_BR_Pvt.Check_BR_Ops_Concurrency
375 (
376 p_api_version => 1.0,
377 p_init_msg_list => FND_API.G_FALSE ,
378 p_commit => FND_API.G_FALSE ,
379 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
380 p_return_status => l_return_status,
381 p_msg_count => l_msg_count,
382 p_msg_data => l_msg_data,
383 --
384 p_budget_revision_id => l_budget_revision_id ,
385 p_operation_type => l_operation_type
386 );
387 --
388 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
389 -- (TBD) Need to specify why it failed (?).
390 result := 'COMPLETE:NO' ;
391 ELSE
392 result := 'COMPLETE:YES' ;
393 END IF ;
394 --
395 END IF ;
396
397 IF ( funcmode = 'CANCEL' ) THEN
398 result := 'COMPLETE' ;
399 END IF;
400
401 --
402 -- In future implementations, appropriate code is to be inserted here.
403 --
404 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
405 --
406 result := '' ;
407 --
408 END IF;
409
410 EXCEPTION
411 --
412 WHEN OTHERS THEN
413 wf_core.context('PSBBR', 'Enforce_Concurrency_Check',
414 PSB_Message_S.Get_Error_Stack(l_msg_count) );
415 RAISE ;
416
417 END Enforce_Concurrency_Check ;
418 /*---------------------------------------------------------------------------*/
419
420
421 /*===========================================================================+
422 | PROCEDURE Validate_Constraints |
423 +===========================================================================*/
424 --
425 -- The API calls Revision validation API. If the validation fails, the
426 -- workflow process terminates and a notification is sent to the submitter.
427 --
428 PROCEDURE Validate_Constraints
429 (
430 itemtype IN VARCHAR2,
431 itemkey IN VARCHAR2,
432 actid IN NUMBER,
433 funcmode IN VARCHAR2,
434 result OUT NOCOPY VARCHAR2
435 )
436 IS
437 --
438 l_return_status VARCHAR2(1) ;
439 l_msg_count NUMBER ;
440 l_msg_data VARCHAR2(2000) ;
441 --
442 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
443 l_validation_status VARCHAR2(1) ;
444 l_operation_type VARCHAR2(20) ;
445 l_constraint_set_id NUMBER ;
446 --
447 BEGIN
448
449 IF ( funcmode = 'RUN' ) THEN
450 --
451 -- Get budget_revision_id item_attribute.
452 --
453 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
454 itemkey => itemkey,
455 aname => 'BUDGET_REVISION_ID');
456
457 l_operation_type := wf_engine.GetItemAttrText
458 ( itemtype => itemtype,
459 itemkey => itemkey,
460 aname => 'OPERATION_TYPE' );
461
462 --
463 -- Find constraint_set_id optionally needed for validation.
464 --
465 l_constraint_set_id := wf_engine.GetItemAttrNumber
466 ( itemtype => itemtype,
467 itemkey => itemkey,
468 aname => 'CONSTRAINT_SET_ID' );
469
470 --
471 -- Call the API to validate the Revision.
472 --
473 PSB_Budget_Revisions_Pvt.Apply_Constraints
474 (
475 p_api_version => 1.0 ,
476 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
477 p_return_status => l_return_status ,
478 p_validation_status => l_validation_status ,
479 --
480 p_budget_revision_id => l_budget_revision_id ,
481 p_constraint_set_id => l_constraint_set_id
482 ) ;
483 --
484 IF l_validation_status = 'F' THEN
485 result := 'COMPLETE:FAIL' ;
486 ELSE
487 result := 'COMPLETE:SUCCESS' ;
488 END IF ;
489 -- */
490
491 -- /* For testing. */
492 -- result := 'COMPLETE:SUCCESS' ;
493
494 END IF ;
495
496 IF ( funcmode = 'CANCEL' ) THEN
497 --
498 result := 'COMPLETE' ;
499 --
500 END IF;
501
502 --
503 -- In future implementations, appropriate code is to be inserted here.
504 --
505 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
506 --
507 result := '' ;
508 --
509 END IF;
510
511 EXCEPTION
512 --
513 WHEN OTHERS THEN
514 wf_core.context('PSBBR', 'Validate_Constraints',
515 PSB_Message_S.Get_Error_Stack(l_msg_count) );
516 RAISE ;
517
518 END Validate_Constraints ;
519 /*---------------------------------------------------------------------------*/
520
521
522
523 /*===========================================================================+
524 | PROCEDURE Select_Operation |
525 +===========================================================================*/
526 --
527 -- The API selects the operation to be performed on the Revision. The
528 -- appropriate branch on the process is selected accordingly.
529 --
530 PROCEDURE Select_Operation
531 (
532 itemtype IN VARCHAR2,
533 itemkey IN VARCHAR2,
534 actid IN NUMBER,
535 funcmode IN VARCHAR2,
536 result OUT NOCOPY VARCHAR2
537 )
538 IS
539 --
540 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
541 l_operation_type VARCHAR2(20) ;
542 --
543 BEGIN
544 --
545 IF ( funcmode = 'RUN' ) THEN
546 --
547 -- Get budget_revision_id item_attribute.
548 --
549 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
550 itemkey => itemkey,
551 aname => 'BUDGET_REVISION_ID');
552 --
553 l_operation_type := wf_engine.GetItemAttrText( itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'OPERATION_TYPE');
556 --
557 -- Operation_type item attribute determines what operation is to
558 -- be performed on the Revision.
559 --
560 IF l_operation_type = 'VALIDATE_REVISION' THEN
561 --
562 result := 'COMPLETE:VALIDATE_REVISION' ;
563 --
564 ELSIF l_operation_type = 'FREEZE_REVISION' THEN
565 --
566 result := 'COMPLETE:FREEZE_REVISION' ;
567 --
568 ELSIF l_operation_type = 'UNFREEZE_REVISION' THEN
569 --
570 result := 'COMPLETE:UNFREEZE_REVISION' ;
571 --
572 ELSIF l_operation_type = 'SUBMIT_REVISION' THEN
573 --
574 result := 'COMPLETE:SUBMIT_REVISION' ;
575 --
576 END IF ;
577
578 END IF ;
579
580 IF ( funcmode = 'CANCEL' ) THEN
581 --
582 result := 'COMPLETE' ;
583 --
584 END IF;
585
586 --
587 -- In future implementations, appropriate code is to be inserted here.
588 --
589 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
590 --
591 result := '' ;
592 --
593 END IF;
594
595 EXCEPTION
596 --
597 WHEN OTHERS THEN
598 wf_core.context('PSBBR', 'Select_Operation',
599 itemtype, itemkey, to_char(actid), funcmode);
600 RAISE ;
601
602 END Select_Operation ;
603 /*---------------------------------------------------------------------------*/
604
605 /*===========================================================================+
606 | PROCEDURE Freeze_Revisions |
607 +===========================================================================*/
608 --
609 -- The API freezes the submitted Budget Revision and
610 -- all its lower Budget Revisions.
611 --
612 PROCEDURE Freeze_Revisions
613 (
614 itemtype IN VARCHAR2,
615 itemkey IN VARCHAR2,
616 actid IN NUMBER,
617 funcmode IN VARCHAR2,
618 result OUT NOCOPY VARCHAR2
619 )
620 IS
621 --
622 l_return_status VARCHAR2(1) ;
623 l_msg_count NUMBER ;
624 l_msg_data VARCHAR2(2000) ;
625 --
626 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
627 l_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
628 l_current_freeze_flag psb_budget_revisions.freeze_flag%TYPE ;
629 l_budget_revisions_tab PSB_Create_BR_Pvt.Budget_Revision_Tbl_Type ;
630
631 --
632 l_notification_id NUMBER ;
633 l_submitter_name VARCHAR2(80);
634 l_operation_type VARCHAR2(20) ;
635 --
636 BEGIN
637 --
638 IF ( funcmode = 'RUN' ) THEN
639 --
640 -- Get budget_revision_id item attribute.
641 --
642 l_budget_revision_id := wf_engine.GetItemAttrNumber
643 (itemtype => itemtype,
644 itemkey => itemkey,
645 aname => 'BUDGET_REVISION_ID');
646 --
647 l_submitter_name := wf_engine.GetItemAttrText
648 ( itemtype => itemtype,
649 itemkey => itemkey,
650 aname => 'SUBMITTER_NAME' );
651 --
652 l_operation_type := wf_engine.GetItemAttrText
653 ( itemtype => itemtype,
654 itemkey => itemkey,
655 aname => 'OPERATION_TYPE' );
656
657 --
658 -- Setting context for the CALLBACK procedure.
659 --
660 SELECT budget_group_name INTO g_budgetgroup_name
661 FROM psb_budget_revisions_v
662 WHERE budget_revision_id = l_budget_revision_id ;
663
664 g_budget_revision_id := l_budget_revision_id ;
665 g_itemtype := itemtype ;
666 g_itemkey := itemkey ;
667
668 --
669 -- Freeze the top level worksheet.
670 --
671 PSB_Create_BR_Pvt.Freeze_Budget_Revision
672 (
673 p_api_version => 1.0 ,
674 p_init_msg_list => FND_API.G_FALSE,
675 p_commit => FND_API.G_FALSE,
676 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
677 p_return_status => l_return_status,
678 p_msg_count => l_msg_count,
679 p_msg_data => l_msg_data,
680 --
681 p_budget_revision_id => l_budget_revision_id ,
682 p_freeze_flag => 'Y'
683 );
684 --
685 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
687 END IF ;
688
689 --
690 -- Send 'NOTIFY_OF_FREEZE_COMPLETION' notification to the top level budget
691 -- group users. To be done for operation_type 'SUBMIT'.
692 --
693 IF l_operation_type = 'SUBMIT_REVISION' THEN
694 --
695 l_notification_id :=
696 WF_Notification.SendGroup
697 ( role => l_submitter_name ,
698 msg_type => 'PSBBR' ,
699 msg_name => 'NOTIFY_OF_FREEZE_COMPLETION' ,
700 context => itemtype ||':'|| itemkey ||':'|| actid ,
701 callback => 'PSB_Submit_Revision_PVT.Callback'
702 ) ;
703 END IF ;
704
705 --
706 -- Call API to find all lower level worksheets.
707 --
708
709 PSB_Create_BR_Pvt.Find_Child_Budget_Revisions
710 (
711 p_api_version => 1.0 ,
712 p_init_msg_list => FND_API.G_FALSE,
713 p_commit => FND_API.G_FALSE,
714 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
715 p_return_status => l_return_status,
716 p_msg_count => l_msg_count,
717 p_msg_data => l_msg_data,
718 --
719 p_budget_revision_id => l_budget_revision_id,
720 p_budget_revision_tbl => l_budget_revisions_tab
721 );
722 --
723
724 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
726 END IF ;
727 --
728
729 --
730 -- Freeze all lower level revisions
731 --
732 FOR i IN 1..l_budget_revisions_tab.COUNT
733 LOOP
734 --
735 -- Check whether the current Revision is already frozen or not.
736 -- If already frozen, do not have to do anything.
737 --
738 SELECT NVL(freeze_flag, 'N') INTO l_current_freeze_flag
739 FROM psb_budget_revisions
740 WHERE budget_revision_id = l_budget_revisions_tab(i) ;
741
742 IF l_current_freeze_flag = 'N' THEN
743 --
744 PSB_Create_BR_Pvt.Freeze_Budget_Revision
745 (
746 p_api_version => 1.0 ,
747 p_init_msg_list => FND_API.G_FALSE,
748 p_commit => FND_API.G_FALSE,
749 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
750 p_return_status => l_return_status,
751 p_msg_count => l_msg_count,
752 p_msg_data => l_msg_data,
753 --
754 p_budget_revision_id => l_budget_revisions_tab(i) ,
755 p_freeze_flag => 'Y'
756 );
757 --
758 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
760 END IF ;
761 --
762
763 --
764 -- Find budget_group_id and budget_group_name for the Revision to find
765 -- workflow roles and to set up context info for the CALLBACK procedure.
766 --
767 SELECT budget_group_id ,
768 budget_group_name
769 INTO
770 l_budget_group_id ,
771 g_budgetgroup_name
772 FROM psb_budget_revisions_v
773 WHERE budget_revision_id = l_budget_revisions_tab(i) ;
774
775 g_budget_revision_id := l_budget_revisions_tab(i) ;
776
777 --
778 -- Send notifications to the budget group roles.
779 --
780 FOR l_role_rec IN
781 (
782 SELECT wf_role_name
783 FROM psb_budget_groups bg ,
784 psb_budget_group_resp resp
785 WHERE resp.responsibility_type = 'N'
786 AND bg.budget_group_id = l_budget_group_id
787 AND bg.budget_group_id = resp.budget_group_id
788 )
789 LOOP
790 --
791 l_notification_id :=
792 WF_Notification.SendGroup
793 ( role => l_role_rec.wf_role_name ,
794 msg_type => 'PSBBR' ,
795 msg_name => 'NOTIFY_OF_FREEZE_COMPLETION' ,
796 context => itemtype ||':'|| itemkey ||':'|| actid ,
797 callback => 'PSB_Submit_Revision_PVT.Callback'
798 ) ;
799 END LOOP ;
800 --
801 END IF ;
802 --
803 END LOOP ;
804
805 result := 'COMPLETE' ;
806
807 END IF ;
808
809 IF ( funcmode = 'CANCEL' ) THEN
810 --
811 result := 'COMPLETE' ;
812 --
813 END IF;
814
815 --
816 -- In future implementations, appropriate code is to be inserted here.
817 --
818 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
819 --
820 result := '' ;
821 --
822 END IF;
823
824 EXCEPTION
825 --
826 WHEN OTHERS THEN
827 wf_core.context('PSBBR', 'Freeze_Revisions',
828 PSB_Message_S.Get_Error_Stack(l_msg_count) );
829 RAISE ;
830
831 END Freeze_Revisions ;
832
833 /*---------------------------------------------------------------------------*/
834
835 /*===========================================================================+
836 | PROCEDURE Post_Revisons_To_GL |
837 +===========================================================================*/
838 --
839 -- The API Posts the transactions of the global budget revision to GL
840 --
841 PROCEDURE Post_Revisions_To_GL
842 (
843 itemtype IN VARCHAR2 ,
844 itemkey IN VARCHAR2 ,
845 actid IN NUMBER ,
846 funcmode IN VARCHAR2 ,
847 result OUT NOCOPY VARCHAR2
848 )
849 IS
850 --
851 l_return_status VARCHAR2(1) ;
852 l_msg_count NUMBER ;
853 l_msg_data VARCHAR2(2000) ;
854 --
855 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
856 l_error_code VARCHAR2(50); -- bug# 4341619
857 l_subject VARCHAR2(2000); -- bug# 4341619
858 l_body VARCHAR2(2000); -- bug# 4341619
859
860 -- commented for bug 4341619
861 /* Cursor C_global_revision is
862 Select global_budget_revision
863 from psb_budget_revisions
864 where budget_revision_id = l_budget_revision_id;*/
865
866 BEGIN
867
868 IF ( funcmode = 'RUN' ) THEN
869 --
870 -- Get budget_revision_id item_attribute.
871 --
872 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
873 itemkey => itemkey,
874 aname => 'BUDGET_REVISION_ID');
875 -- commented for bug 4341619
876 /*For C_global_revision_rec in C_global_revision
877 Loop
878 if ((C_global_revision_rec.global_budget_revision is not null) and
879 (C_global_revision_rec.global_budget_revision = 'Y')) then */
880
881 PSB_GL_Interface_Pvt.Create_Revision_Journal
882 ( p_api_version => 1.0,
883 p_return_status => l_return_status,
884 p_msg_count => l_msg_count,
885 p_msg_data => l_msg_data,
886 --
887 p_budget_revision_id => l_budget_revision_id,
888 p_order_by1 => null,
889 p_order_by2 => null,
890 p_order_by3 => null,
891 p_error_code => l_error_code -- bug 4341619
892 );
893
894 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
896 END IF ;
897
898 /* start bug # 4341619 */
899 if(l_error_code = 'NO_ERR') then
900 result := 'COMPLETE:YES';
901
902 elsif(l_error_code = 'ACCOUNT_OVERLAP_ERR') then
903
904 fnd_message.set_name('PSB','PSB_BR_ACCT_OVERLAP_SUBJ');
905 fnd_message.set_token('BUDGET_REVISION_ID',l_budget_revision_id);
906 l_subject := fnd_message.get;
907
908 fnd_message.set_name('PSB','PSB_BR_ACCT_OVERLAP_BODY');
909 l_body := fnd_message.get;
910
911 result := 'COMPLETE:NO';
912
913 elsif(l_error_code = 'GL_BUDGET_PERIOD_NOT_OPEN_ERR') then
914
915 fnd_message.set_name('PSB','PSB_BR_GL_PERIOD_NOT_OPEN_SUBJ');
916 fnd_message.set_token('BUDGET_REVISION_ID',l_budget_revision_id);
917 l_subject := fnd_message.get;
918
919 fnd_message.set_name('PSB','PSB_BR_GL_PERIOD_NOT_OPEN_BODY');
920 l_body := fnd_message.get;
921
922 result := 'COMPLETE:NO';
923
924 elsif(l_error_code = 'NO_FUNDING_BUDGET_ERR') then
925
926 fnd_message.set_name('PSB','PSB_BR_NO_FUNDING_BUDGET_SUBJ');
927 fnd_message.set_token('BUDGET_REVISION_ID',l_budget_revision_id);
928 l_subject := fnd_message.get;
929
930 fnd_message.set_name('PSB','PSB_BR_NO_FUNDING_BUDGET_BODY');
931 l_body := fnd_message.get;
932
933 result := 'COMPLETE:NO';
934
935 end if;
936
937 if(l_error_code <> 'NO_ERR') then
938 wf_engine.SetItemAttrText( ItemType => itemtype,
939 ItemKey => itemkey,
940 aname => 'NOTIFICATION_SUBJECT',
941 avalue => l_subject );
942
943 wf_engine.SetItemAttrText( ItemType => itemtype,
944 ItemKey => itemkey,
945 aname => 'NOTIFICATION_BODY',
946 avalue => l_body );
947 end if;
948 /* end bug # 4341619 */
949
950
951 -- commented for bug 4341619
952 /* result := 'COMPLETE:YES' ;
953 else
954 result := 'COMPLETE:NO' ;
955 end if;
956
957 End loop; */
958
959
960 END IF ;
961
962 IF ( funcmode = 'CANCEL' ) THEN
963 --
964 result := 'COMPLETE' ;
965 --
966 END IF;
967
968 --In future implementations, appropriate code is to be inserted here.
969 --
970 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
971 --
972 result := '' ;
973 --
974 END IF;
975
976 EXCEPTION
977 --
978 WHEN OTHERS THEN
979 wf_core.context('PSBBR', 'Post_Revisions_To_GL',
980 PSB_Message_S.Get_Error_Stack(l_msg_count) );
981 RAISE ;
982
983 End Post_Revisions_To_GL;
984
985 /*---------------------------------------------------------------------------*/
986
987 /*===========================================================================+
988 | PROCEDURE Update_View_Line_Flag |
989 +===========================================================================*/
990 --
991 -- The API updates view_line flag for the parent revisions of the submittted
992 -- revision.
993 --
994 PROCEDURE Update_View_Line_Flag
995 (
996 itemtype IN VARCHAR2,
997 itemkey IN VARCHAR2,
998 actid IN NUMBER,
999 funcmode IN VARCHAR2,
1000 result OUT NOCOPY VARCHAR2
1001 )
1002 IS
1003 --
1004 l_return_status VARCHAR2(1) ;
1005 l_msg_count NUMBER ;
1006 l_msg_data VARCHAR2(2000) ;
1007 --
1008 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1009 l_budget_revisions_tab PSB_Create_BR_Pvt.Budget_Revision_Tbl_Type ;
1010 l_operation_id NUMBER ;
1011 --
1012
1013 BEGIN
1014 --
1015 IF ( funcmode = 'RUN' ) THEN
1016 --
1017 -- Get budget_revision_id item_attribute.
1018 --
1019 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1020 itemkey => itemkey,
1021 aname => 'BUDGET_REVISION_ID');
1022
1023 l_operation_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1024 itemkey => itemkey,
1025 aname => 'OPERATION_ID');
1026
1027 --
1028 -- Find all the parent worksheets to update thier view_line_flag.
1029 --
1030 PSB_Create_BR_Pvt.Find_Parent_Budget_Revisions
1031 (
1032 p_api_version => 1.0 ,
1033 p_init_msg_list => FND_API.G_FALSE,
1034 p_commit => FND_API.G_FALSE,
1035 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1036 p_return_status => l_return_status,
1037 p_msg_count => l_msg_count,
1038 p_msg_data => l_msg_data,
1039 --
1040 p_budget_revision_id => l_budget_revision_id,
1041 p_budget_revision_tbl => l_budget_revisions_tab
1042 );
1043 --
1044 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1046 END IF ;
1047
1048 --
1049 -- Update view_line_flag for the parent revisions
1050 --
1051 FOR i IN 1..l_budget_revisions_tab.COUNT
1052 LOOP
1053
1054 --
1055 -- Update view_line_flags in matrixes for all the lines to 'Y'.
1056 -- Later we will set the flag to 'N' as per the selection.
1057 --
1058
1059 UPDATE psb_budget_revision_lines
1060 SET view_line_flag = 'Y'
1061 WHERE budget_revision_id = l_budget_revisions_tab(i) ;
1062
1063 UPDATE psb_budget_revision_pos_lines
1064 SET view_line_flag = 'Y'
1065 WHERE budget_revision_id = l_budget_revisions_tab(i) ;
1066
1067
1068 END LOOP ;
1069
1070 result := 'COMPLETE:YES' ;
1071
1072 END IF ;
1073
1074 IF ( funcmode = 'CANCEL' ) THEN
1075 --
1076 result := 'COMPLETE' ;
1077 --
1078 END IF;
1079
1080 --
1081 -- In future implementations, appropriate code is to be inserted here.
1082 --
1083 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1084 --
1085 result := '' ;
1086 --
1087 END IF;
1088
1089 EXCEPTION
1090 --
1091 WHEN OTHERS THEN
1092 wf_core.context('PSBBR', 'Update_View_Line_Flag',
1093 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1094 RAISE ;
1095
1096 END Update_View_Line_Flag;
1097 /*---------------------------------------------------------------------------*/
1098
1099
1100 /*===========================================================================+
1101 | PROCEDURE Find_Override_Approver |
1102 +===========================================================================*/
1103 --
1104 -- The API finds the Override_Approver for a Revision.
1105 --
1106 PROCEDURE Find_Override_Approver
1107 (
1108 itemtype IN VARCHAR2,
1109 itemkey IN VARCHAR2,
1110 actid IN NUMBER,
1111 funcmode IN VARCHAR2,
1112 result OUT NOCOPY VARCHAR2
1113 )
1114 IS
1115 --
1116 l_override_approver psb_budget_revisions.approval_override_by%TYPE;
1117 -- For Bug 4475288: Changed l_approver_name length.
1118 l_approver_name psb_budget_group_resp.wf_role_name%TYPE;
1119 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1120 l_notification_group_id NUMBER ;
1121 -- Added the following variable for bug 3394080
1122 -- For Bug 4475288: Changed l_approver_display_name length.
1123 l_approver_display_name wf_roles.display_name%TYPE;
1124 --
1125 BEGIN
1126 --
1127 IF ( funcmode = 'RUN' ) THEN
1128 --
1129 -- Get budget_revision_id item_attribute.
1130 --
1131 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1132 itemkey => itemkey,
1133 aname => 'BUDGET_REVISION_ID');
1134
1135 -- Bug#3259142: Use display_name instead of name from wf dir.
1136 SELECT br.approval_override_by, wr.name,wr.display_name
1137 INTO
1138 l_override_approver , l_approver_name,l_approver_display_name
1139 FROM psb_budget_revisions br,
1140 wf_roles wr
1141 WHERE br.budget_revision_id = l_budget_revision_id
1142 AND br.approval_orig_system = orig_system(+)
1143 AND br.approval_override_by = orig_system_id(+) ;
1144
1145 --
1146 if (l_override_approver is null) then
1147 result := 'COMPLETE:NO' ;
1148 ELSE
1149 result := 'COMPLETE:YES' ;
1150
1151 wf_engine.SetItemAttrText( ItemType => itemtype,
1152 ItemKey => itemkey,
1153 aname => 'OVERRIDE_APPROVER',
1154 avalue => l_override_approver );
1155
1156 wf_engine.SetItemAttrText( ItemType => itemtype,
1157 ItemKey => itemkey,
1158 aname => 'APPROVER_NAME',
1159 avalue => l_approver_name );
1160
1161 -- Added for bug 3394080
1162 wf_engine.SetItemAttrText( ItemType => itemtype,
1163 ItemKey => itemkey,
1164 aname => 'APPROVER_DISPLAY_NAME',
1165 avalue => l_approver_display_name );
1166 END IF ;
1167 --
1168
1169 END IF ;
1170 --
1171 IF ( funcmode = 'CANCEL' ) THEN
1172 --
1173 result := 'COMPLETE' ;
1174
1175 --
1176 END IF;
1177
1178 --
1179 -- In future implementations, appropriate code is to be inserted here.
1180 --
1181 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1182 --
1183 result := '' ;
1184 --
1185 END IF;
1186
1187 EXCEPTION
1188 --
1189 WHEN OTHERS THEN
1190 wf_core.context('PSBBR', 'Find_Override_Approver',
1191 itemtype, itemkey, to_char(actid), funcmode);
1192 RAISE ;
1193
1194 END Find_Override_Approver;
1195 /*---------------------------------------------------------------------------*/
1196
1197 /*===========================================================================+
1198 | PROCEDURE Send_Approval_Notification |
1199 +===========================================================================*/
1200 --
1201 -- The activity finds out whether 'Revision Approved' related notification
1202 -- will be sent to the submitter or not.
1203 --
1204 PROCEDURE Send_Approval_Notification
1205 (
1206 itemtype IN VARCHAR2,
1207 itemkey IN VARCHAR2,
1208 actid IN NUMBER,
1209 funcmode IN VARCHAR2,
1210 result OUT NOCOPY VARCHAR2
1211 )
1212 IS
1213 --
1214 l_reviewed_flag VARCHAR2(1) ;
1215 --
1216 BEGIN
1217
1218 IF ( funcmode = 'RUN' ) THEN
1219 --
1220 -- Get reviewed_flag item_attribute.
1221 --
1222 l_reviewed_flag := wf_engine.GetItemAttrText
1223 ( itemtype => itemtype,
1224 itemkey => itemkey,
1225 aname => 'REVIEWED_FLAG' );
1226
1227 --
1228 IF NVL( l_reviewed_flag, 'N') = 'N' THEN
1229 result := 'COMPLETE:NO' ;
1230 ELSE
1231 result := 'COMPLETE:YES' ;
1232 END IF ;
1233 --
1234
1235 END IF ;
1236
1237 IF ( funcmode = 'CANCEL' ) THEN
1238 result := 'COMPLETE' ;
1239 END IF;
1240
1241 --
1242 -- In future implementations, appropriate code is to be inserted here.
1243 --
1244 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1245 --
1246 result := '' ;
1247 --
1248 END IF;
1249
1250 EXCEPTION
1251 --
1252 WHEN OTHERS THEN
1253 wf_core.context('PSBBR', 'Send_Approval_Notification',
1254 itemtype, itemkey, to_char(actid), funcmode);
1255 RAISE ;
1256
1257 END Send_Approval_Notification ;
1258 /*---------------------------------------------------------------------------*/
1259
1260
1261
1262 /*===========================================================================+
1263 | PROCEDURE Update_Revisions_Status |
1264 +===========================================================================*/
1265 --
1266 -- The API updates submission related information in the submitted revision
1267 -- and all its lower revisions.
1268 --
1269 PROCEDURE Update_Revisions_Status
1270 (
1271 itemtype IN VARCHAR2,
1272 itemkey IN VARCHAR2,
1273 actid IN NUMBER,
1274 funcmode IN VARCHAR2,
1275 result OUT NOCOPY VARCHAR2
1276 )
1277 IS
1278 --
1279 l_return_status VARCHAR2(1) ;
1280 l_msg_count NUMBER ;
1281 l_msg_data VARCHAR2(2000) ;
1282 --
1283 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1284 l_submitter_id NUMBER ;
1285 l_submission_status VARCHAR2(1);
1286 l_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1287 l_budget_revisions_tab PSB_Create_BR_Pvt.Budget_Revision_Tbl_Type ;
1288 --
1289 BEGIN
1290 --
1291 IF ( funcmode = 'RUN' ) THEN
1292 --
1293 -- Get budget_revision_id item_attribute.
1294 --
1295 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1296 itemkey => itemkey,
1297 aname => 'BUDGET_REVISION_ID');
1298 --
1299 l_submitter_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1300 itemkey => itemkey,
1301 aname => 'SUBMITTER_ID');
1302
1303 l_submission_status := wf_engine.GetItemAttrText( itemtype => itemtype,
1304 itemkey => itemkey,
1305 aname => 'SUBMISSION_STATUS');
1306 --
1307 -- Call API to find all lower level worksheets.
1308 --
1309 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
1310 (
1311 p_api_version => 1.0 ,
1312 p_init_msg_list => FND_API.G_FALSE,
1313 p_commit => FND_API.G_FALSE,
1314 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1315 p_return_status => l_return_status,
1316 p_msg_count => l_msg_count,
1317 p_msg_data => l_msg_data ,
1318 --
1319 p_budget_revision_id => l_budget_revision_id,
1320 p_submission_date => SYSDATE ,
1321 p_submission_status => l_submission_status,
1322 p_requestor => l_submitter_id
1323 );
1324
1325 --
1326 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1328 END IF ;
1329
1330 /*PSB_Create_BR_Pvt.Find_Child_Budget_Revisions
1331 (
1332 p_api_version => 1.0 ,
1333 p_init_msg_list => FND_API.G_FALSE,
1334 p_commit => FND_API.G_FALSE,
1335 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1336 p_return_status => l_return_status,
1337 p_msg_count => l_msg_count,
1338 p_msg_data => l_msg_data,
1339 --
1340 p_budget_revision_id => l_budget_revision_id ,
1341 p_budget_revision_tbl => l_budget_revisions_tab
1342 );
1343 --
1344 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1346 END IF ;
1347 --
1348
1349 l_budget_revisions_tab(0) := l_budget_revision_id ;
1350
1351 --
1352 -- Processing all lower level Revisions for updation
1353 --
1354 FOR i IN 0..l_budget_revisions_tab.COUNT
1355 LOOP
1356 --
1357 -- Update Distribution related information in psb_budget_revisions.
1358 --
1359 PSB_Budget_Revisions_Pvt.Create_Budget_Revision
1360 (
1361 p_api_version => 1.0 ,
1362 p_init_msg_list => FND_API.G_FALSE,
1363 p_commit => FND_API.G_FALSE,
1364 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1365 p_return_status => l_return_status,
1366 p_msg_count => l_msg_count,
1367 p_msg_data => l_msg_data ,
1368 --
1369 p_budget_revision_id => l_budget_revisions_tab(i),
1370 p_submission_date => SYSDATE ,
1371 p_submission_status => l_submission_status,
1372 p_requestor => l_submitter_id
1373 );
1374
1375 --
1376 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1378 END IF ;
1379 --
1380 END LOOP ; */
1381
1382 result := 'COMPLETE' ;
1383
1384 END IF ;
1385
1386 IF ( funcmode = 'CANCEL' ) THEN
1387 --
1388 result := 'COMPLETE' ;
1389 --
1390 END IF;
1391
1392 --
1393 -- In future implementations, appropriate code is to be inserted here.
1394 --
1395 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1396 --
1397 result := '' ;
1398 --
1399 END IF;
1400
1401 EXCEPTION
1402 --
1403 WHEN OTHERS THEN
1404 wf_core.context('PSBBR', 'Update_Revisions_Status',
1405 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1406 RAISE ;
1407
1408 END Update_Revisions_Status ;
1409
1410 /*---------------------------------------------------------------------------*/
1411
1412 /*===========================================================================+
1413 | PROCEDURE Update_Baseline_Values |
1414 +===========================================================================*/
1415 --
1416 -- This API updates the base value of the position ftes, position costs and
1417 -- position account distributions after the revision is approved.
1418 --
1419
1420 PROCEDURE Update_Baseline_Values
1421 (
1422 itemtype IN VARCHAR2,
1423 itemkey IN VARCHAR2,
1424 actid IN NUMBER,
1425 funcmode IN VARCHAR2,
1426 result OUT NOCOPY VARCHAR2
1427 )
1428 IS
1429 --
1430 l_return_status VARCHAR2(1) ;
1431 l_msg_count NUMBER ;
1432 l_msg_data VARCHAR2(2000) ;
1433 --
1434 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1435 l_currency_code VARCHAR2(15);
1436 --
1437
1438 -- added cursor for bug 4341619
1439 Cursor C_global_revision is
1440 Select global_budget_revision,
1441 currency_code -- Bug 3029168
1442 from psb_budget_revisions
1443 where budget_revision_id = l_budget_revision_id;
1444
1445 BEGIN
1446 --
1447 IF ( funcmode = 'RUN' ) THEN
1448 --
1449 -- Get budget_revision_id item_attribute.
1450 --
1451 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1452 itemkey => itemkey,
1453 aname => 'BUDGET_REVISION_ID');
1454 --
1455 -- Update the baseline values in Position Control Tables
1456 -- with the revised value
1457 -- for the positions.
1458
1459 /* start bug 4341619 */
1460 For C_global_revision_rec in C_global_revision
1461 Loop
1462 l_currency_code := c_global_revision_rec.currency_code;
1463 if ((C_global_revision_rec.global_budget_revision is not null) and
1464 (C_global_revision_rec.global_budget_revision = 'Y')) then
1465 result := 'COMPLETE:YES' ;
1466 else
1467 result := 'COMPLETE:NO' ;
1468 end if;
1469 End loop;
1470 /* end bug 4341619 */
1471
1472 IF l_currency_code <> 'STAT' THEN -- Bug 3029168
1473 PSB_Budget_Revisions_Pvt.Update_Baseline_Values
1474 (
1475 p_api_version => 1.0 ,
1476 p_init_msg_list => FND_API.G_FALSE,
1477 p_commit => FND_API.G_FALSE,
1478 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1479 p_return_status => l_return_status,
1480 p_msg_count => l_msg_count,
1481 p_msg_data => l_msg_data ,
1482 --
1483 p_budget_revision_id => l_budget_revision_id
1484 );
1485
1486 --
1487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1489 END IF ;
1490 END IF;
1491
1492 END IF ;
1493
1494 IF ( funcmode = 'CANCEL' ) THEN
1495 --
1496 result := 'COMPLETE' ;
1497 --
1498 END IF;
1499
1500 --
1501 -- In future implementations, appropriate code is to be inserted here.
1502 --
1503 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1504 --
1505 result := '' ;
1506 --
1507 END IF;
1508
1509 EXCEPTION
1510 --
1511 WHEN OTHERS THEN
1512 wf_core.context('PSBBR', 'Update_Baseline_Values',
1513 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1514 RAISE ;
1515
1516 END Update_Baseline_Values ;
1517
1518 /*===========================================================================+
1519 | PROCEDURE Funds_Reservation_Update |
1520 +===========================================================================*/
1521 --
1522 -- This API does a funds reservation for each of the accounts affected
1523 -- by the current budget revision
1524 --
1525
1526 PROCEDURE Funds_Reservation_Update
1527 (
1528 itemtype IN VARCHAR2,
1529 itemkey IN VARCHAR2,
1530 actid IN NUMBER,
1531 funcmode IN VARCHAR2,
1532 result OUT NOCOPY VARCHAR2
1533 )
1534 IS
1535 --
1536 l_return_status VARCHAR2(1) ;
1537 l_msg_count NUMBER ;
1538 l_msg_data VARCHAR2(2000) ;
1539 --
1540 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1541 l_fund_check_failures NUMBER; -- bug#4341619
1542
1543 l_operation_type VARCHAR2(20);
1544 l_called_from VARCHAR2(8) := 'PSBBGRVS';
1545 l_currency_code VARCHAR2(15);
1546 --
1547 BEGIN
1548 --
1549 IF ( funcmode = 'RUN' ) THEN
1550 --
1551 -- Get budget_revision_id item_attribute.
1552 --
1553 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1554 itemkey => itemkey,
1555 aname => 'BUDGET_REVISION_ID');
1556
1557 SELECT currency_code INTO l_currency_code
1558 FROM psb_budget_revisions
1559 WHERE budget_revision_id = l_budget_revision_id; -- Bug 3029168
1560
1561
1562 --
1563 --
1564 -- Bug#4310411 Start
1565 l_operation_type := wf_engine.GetItemAttrText
1566 (itemtype => itemtype,
1567 itemkey => itemkey,
1568 aname => 'OPERATION_TYPE'
1569 );
1570
1571 IF l_operation_type = 'SUBMIT_REVISION' THEN
1572 -- Called for Revision Submission.
1573 l_called_from := 'PSBBR';
1574 ELSIF l_operation_type = 'VALIDATE_REVISION' THEN
1575 -- Called from Budget Revision Form to validate
1576 l_called_from := 'PSBBGRVS';
1577 ELSIF l_operation_type = 'FREEZE_REVISION' THEN
1578 -- Called for Budget Revision Form to freeze
1579 l_called_from := 'PSBBGRVS';
1580 ELSIF l_operation_type = 'UNFREEZE_REVISION' THEN
1581 -- Called for Budget Revision Form to unfreeze
1582 l_called_from := 'PSBBGRVS';
1583 END IF;
1584 -- Bug#4310411 End
1585
1586 -- Bug 3029168 added the following IF condition
1587 -- funds check should not be called for STAT
1588 IF l_currency_code <> 'STAT' THEN -- Bug 3029168
1589
1590 PSB_Budget_Revisions_Pvt.Budget_Revision_Funds_Check
1591 (
1592 p_api_version => 1.0 ,
1593 p_init_msg_list => FND_API.G_FALSE,
1594 p_commit => FND_API.G_FALSE,
1595 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1596 p_return_status => l_return_status,
1597 p_msg_count => l_msg_count,
1598 p_msg_data => l_msg_data ,
1599 --
1600 p_funds_reserve_flag => 'Y',
1601 p_budget_revision_id => l_budget_revision_id ,
1602 p_fund_check_failures => l_fund_check_failures, -- Bug4341619
1603 p_called_from => l_called_from -- Bug#4310411
1604 );
1605
1606 --
1607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1609 END IF ;
1610 --
1611
1612 /* start bug 4341619 */
1613 IF (l_fund_check_failures > 0) THEN
1614 result := 'COMPLETE:NO' ;
1615 ELSE
1616 result := 'COMPLETE:YES' ;
1617 END IF;
1618 /* end bug 4341619 */
1619
1620 ELSE
1621 result := 'COMPLETE:YES' ;
1622 END IF;
1623
1624 END IF ;
1625
1626 IF ( funcmode = 'CANCEL' ) THEN
1627 --
1628 result := 'COMPLETE' ;
1629 --
1630 END IF;
1631
1632 --
1633 -- In future implementations, appropriate code is to be inserted here.
1634 --
1635 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1636 --
1637 result := '' ;
1638 --
1639 END IF;
1640
1641 EXCEPTION
1642 --
1643 WHEN OTHERS THEN
1644 wf_core.context('PSBBR', 'Funds_Reservation_Update',
1645 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1646 RAISE ;
1647
1648 END Funds_Reservation_Update ;
1649
1650 /*===========================================================================+
1651 | PROCEDURE Select_Approvers |
1652 +===========================================================================*/
1653 --
1654 -- The API finds Approvers for the REvision and then sends notifications
1655 -- to them.
1656 --
1657 PROCEDURE Select_Approvers
1658 (
1659 itemtype IN VARCHAR2,
1660 itemkey IN VARCHAR2,
1661 actid IN NUMBER,
1662 funcmode IN VARCHAR2,
1663 result OUT NOCOPY VARCHAR2
1664 )
1665 IS
1666 --
1667 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1668 l_approver_name VARCHAR2(80) ;
1669 l_parent_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1670 l_notification_group_id NUMBER ;
1671 --
1672
1673 BEGIN
1674 --
1675 IF ( funcmode = 'RUN' ) THEN
1676 --
1677 -- Get budget_revision_id item_attribute.
1678 --
1679 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1680 itemkey => itemkey,
1681 aname => 'BUDGET_REVISION_ID');
1682 --
1683 -- Setting context for the CALLBACK procedure.
1684 --
1685
1686 g_itemtype := itemtype ;
1687 g_itemkey := itemkey ;
1688 g_budget_revision_id := l_budget_revision_id ;
1689
1690 g_budgetgroup_name := wf_engine.GetItemAttrText
1691 ( itemtype => itemtype,
1692 itemkey => itemkey,
1693 aname => 'BUDGET_GROUP_NAME'
1694 );
1695
1696 --
1697 -- Find the approver role.
1698 --
1699 -- Modified the query for bug 3394080
1700 SELECT nvl(bg.parent_budget_group_id,bg.budget_group_id)
1701 INTO
1702 l_parent_budget_group_id
1703 FROM psb_budget_revisions br,
1704 psb_budget_groups bg
1705 WHERE br.budget_revision_id = l_budget_revision_id
1706 AND br.budget_group_id = bg.budget_group_id ;
1707
1708
1709
1710 FOR l_role_rec IN
1711 (
1712 SELECT wf_role_name
1713 FROM psb_budget_groups bg ,
1714 psb_budget_group_resp resp
1715 WHERE bg.budget_group_id = l_parent_budget_group_id
1716 AND resp.responsibility_type = 'N'
1717 AND bg.budget_group_id = resp.budget_group_id
1718 )
1719 LOOP
1720 --
1721 l_notification_group_id :=
1722 WF_Notification.SendGroup
1723 (
1724 role => l_role_rec.wf_role_name ,
1725 msg_type => 'PSBBR' ,
1726 msg_name => 'NOTIFY_APPROVERS_OF_SUBMISSION' ,
1727 context => itemtype ||':'|| itemkey || ':'|| actid ,
1728 callback => 'PSB_Submit_Revision_PVT.Callback'
1729 ) ;
1730 END LOOP ;
1731
1732 --
1733 END IF ;
1734
1735 IF ( funcmode = 'CANCEL' ) THEN
1736 --
1737 result := 'COMPLETE' ;
1738 --
1739 END IF;
1740
1741 --
1742 -- In future implementations, appropriate code is to be inserted here.
1743 --
1744 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1745 --
1746 result := '' ;
1747 --
1748 END IF;
1749
1750 EXCEPTION
1751 --
1752 WHEN OTHERS THEN
1753 wf_core.context('PSBBR', 'Select_Approvers',
1754 itemtype, itemkey, to_char(actid), funcmode);
1755 RAISE ;
1756
1757 END Select_Approvers ;
1758 /*---------------------------------------------------------------------------*/
1759
1760
1761
1762 /*===========================================================================+
1763 | PROCEDURE Unfreeze_Revisions |
1764 +===========================================================================*/
1765 --
1766 -- This API unfreezes the submitted revision. Note that the lower revision
1767 -- are not unfrozen, even though they were frozen during Freeze operation.
1768 --
1769 PROCEDURE Unfreeze_Revisions
1770 (
1771 itemtype IN VARCHAR2,
1772 itemkey IN VARCHAR2,
1773 actid IN NUMBER,
1774 funcmode IN VARCHAR2,
1775 result OUT NOCOPY VARCHAR2
1776 )
1777 IS
1778 --
1779 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
1780 --
1781 l_return_status VARCHAR2(1) ;
1782 l_msg_count NUMBER ;
1783 l_msg_data VARCHAR2(2000) ;
1784 --
1785 l_notification_id NUMBER ;
1786 --
1787 BEGIN
1788 --
1789 IF ( funcmode = 'RUN' ) THEN
1790 --
1791 -- Get budget_revision_id item_attribute.
1792 --
1793 l_budget_revision_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1794 itemkey => itemkey,
1795 aname => 'BUDGET_REVISION_ID');
1796 --
1797 -- Unfreeze only the current worksheet.
1798 --
1799 PSB_Create_BR_Pvt.Freeze_Budget_Revision
1800 (
1801 p_api_version => 1.0 ,
1802 p_init_msg_list => FND_API.G_FALSE,
1803 p_commit => FND_API.G_FALSE,
1804 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1805 p_return_status => l_return_status,
1806 p_msg_count => l_msg_count,
1807 p_msg_data => l_msg_data,
1808 --
1809 p_budget_revision_id => l_budget_revision_id,
1810 p_freeze_flag => 'N'
1811 );
1812 --
1813 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1815 END IF ;
1816 --
1817
1818 result := 'COMPLETE' ;
1819
1820 END IF ;
1821
1822
1823 IF ( funcmode = 'CANCEL' ) THEN
1824 --
1825 result := 'COMPLETE' ;
1826 --
1827 END IF;
1828
1829 --
1830 -- In future implementations, appropriate code is to be inserted here.
1831 --
1832 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1833 --
1834 result := '' ;
1835 --
1836 END IF;
1837
1838 EXCEPTION
1839 --
1840 WHEN OTHERS THEN
1841 wf_core.context('PSBBR', 'Unfreeze_Revisions',
1842 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1843 RAISE ;
1844
1845 END Unfreeze_Revisions ;
1846 /*---------------------------------------------------------------------------*/
1847
1848 /*===========================================================================+
1849 | PROCEDURE Set_Loop_Limit |
1850 +===========================================================================*/
1851 --
1852 -- The API sets 'Loop Limit' attribute for the special 'Loop Counter' activity.
1853 --
1854 PROCEDURE Set_Loop_Limit
1855 (
1856 itemtype IN VARCHAR2,
1857 itemkey IN VARCHAR2,
1858 actid IN NUMBER,
1859 funcmode IN VARCHAR2,
1860 result OUT NOCOPY VARCHAR2
1861 )
1862 IS
1863 --
1864 l_return_status VARCHAR2(1);
1865 --
1866 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE;
1867 l_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1868 l_parent_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1869 l_count NUMBER;
1870 l_init_index NUMBER;
1871 --
1872 BEGIN
1873 --
1874 IF ( funcmode = 'RUN' ) THEN
1875 --
1876 -- Get budget_revision_id item_attribute.
1877 --
1878 l_budget_revision_id := wf_engine.GetItemAttrNumber
1879 ( itemtype => itemtype,
1880 itemkey => itemkey,
1881 aname => 'BUDGET_REVISION_ID'
1882 );
1883
1884 --
1885 -- Get budget_group, parent budget group info for the Revision.
1886 --
1887
1888 -- Modified the query for bug 3394080
1889 SELECT nvl(bg.parent_budget_group_id,bg.budget_group_id)
1890 INTO
1891 l_parent_budget_group_id
1892 FROM psb_budget_revisions br,
1893 psb_budget_groups bg
1894 WHERE br.budget_revision_id = l_budget_revision_id
1895 AND br.budget_group_id = bg.budget_group_id ;
1896
1897
1898
1899 g_num_approvers := 0;
1900
1901 SELECT count(*)
1902 INTO g_num_approvers
1903 FROM psb_budget_group_resp resp
1904 WHERE resp.budget_group_id in
1905 (select budget_group_id from psb_budget_groups bg
1906 start with bg.budget_group_id = l_parent_budget_group_id
1907 connect by prior bg.parent_budget_group_id = bg.budget_group_id)
1908 AND resp.responsibility_type = 'N';
1909
1910 --
1911 -- Committing as the users on_exit settings may be ROLLBACK.
1912 --
1913 COMMIT ;
1914
1915 wf_engine.SetItemAttrNumber( ItemType => itemtype,
1916 ItemKey => itemkey,
1917 aname => 'LOOP_SET_COUNTER',
1918 avalue => g_num_approvers );
1919 --
1920 result := 'COMPLETE' ;
1921
1922 END IF ;
1923
1924 IF ( funcmode = 'CANCEL' ) THEN
1925 --
1926 result := 'COMPLETE' ;
1927 --
1928 END IF;
1929
1930 --
1931 -- In future implementations, appropriate code is to be inserted here.
1932 --
1933 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1934 --
1935 result := '' ;
1936 --
1937 END IF;
1938
1939 EXCEPTION
1940 --
1941 WHEN OTHERS THEN
1942 wf_core.context('PSBBR', 'Set_Loop_Limit',
1943 itemtype, itemkey, to_char(actid), funcmode);
1944 RAISE ;
1945
1946 END Set_Loop_Limit;
1947
1948 PROCEDURE Find_Approver
1949 (
1950 itemtype IN VARCHAR2,
1951 itemkey IN VARCHAR2,
1952 actid IN NUMBER,
1953 funcmode IN VARCHAR2,
1954 result OUT NOCOPY VARCHAR2)
1955 IS
1956 l_return_status VARCHAR2(1) ;
1957 --
1958 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE;
1959 l_parent_budget_group_id psb_budget_revisions.budget_group_id%TYPE ;
1960 l_count NUMBER;
1961 l_loop_visited_counter NUMBER := 0;
1962 -- For Bug 4475288: Changed l_approver_name length.
1963 l_approver_name VARCHAR2(320);
1964 -- Added the following variable for bug 3394080
1965 -- For Bug 4475288: Changed l_approver_display_name length.
1966 l_approver_display_name wf_roles.display_name%TYPE;
1967 l_init_index NUMBER ;
1968
1969 BEGIN
1970
1971 IF ( funcmode = 'RUN' ) THEN
1972 --
1973 -- Get budget_revision_id item_attribute.
1974 --
1975 l_budget_revision_id := wf_engine.GetItemAttrNumber
1976 ( itemtype => itemtype,
1977 itemkey => itemkey,
1978 aname => 'BUDGET_REVISION_ID'
1979 );
1980
1981 l_count := wf_engine.GetItemAttrNumber
1982 ( itemtype => itemtype,
1983 itemkey => itemkey,
1984 aname => 'LOOP_SET_COUNTER'
1985 );
1986
1987
1988 l_loop_visited_counter := wf_engine.GetItemAttrNumber
1989 ( itemtype => itemtype,
1990 itemkey => itemkey,
1991 aname => 'LOOP_VISITED_COUNTER'
1992 );
1993
1994 for l_init_index in 1..g_approvers.Count loop
1995 g_approvers(l_init_index).item_key := null;
1996 g_approvers(l_init_index).approver_name := null;
1997 g_approvers(l_init_index).approver_display_name := null;
1998 g_approvers(l_init_index).sequence := null;
1999 end loop;
2000
2001 g_num_approvers := 0;
2002
2003 -- Modified the query for bug 3394080
2004 SELECT nvl(bg.parent_budget_group_id,bg.budget_group_id)
2005 INTO l_parent_budget_group_id
2006 FROM psb_budget_revisions br,
2007 psb_budget_groups bg
2008 WHERE br.budget_revision_id = l_budget_revision_id
2009 AND br.budget_group_id = bg.budget_group_id;
2010
2011
2012
2013 -- Bug#3259142: Use display_name instead of name from wf dir.
2014 For l_role_rec in
2015 (
2016 SELECT bg.bglevel ,
2017 resp.budget_group_resp_id ,
2018 resp.wf_role_name ,
2019 wfr.name name ,
2020 wfr.display_name display_name
2021 FROM psb_budget_group_resp resp,
2022 wf_roles wfr,
2023 ( SELECT level bglevel,
2024 budget_group_id
2025 FROM psb_budget_groups
2026 START WITH budget_group_id = l_parent_budget_group_id CONNECT BY PRIOR parent_budget_group_id = budget_group_id
2027 ) bg
2028 WHERE bg.budget_group_id = resp.budget_group_id
2029 AND resp.responsibility_type = 'N'
2030 AND wfr.orig_system = resp.wf_role_orig_system
2031 AND wfr.orig_system_id = resp.wf_role_orig_system_id
2032 ORDER BY 1,2
2033 )
2034 Loop
2035 g_num_approvers := g_num_approvers + 1;
2036 g_approvers(g_num_approvers).item_key := itemkey;
2037 g_approvers(g_num_approvers).approver_name := l_role_rec.name;
2038 g_approvers(g_num_approvers).approver_display_name := l_role_rec.display_name;
2039 g_approvers(g_num_approvers).sequence := g_num_approvers;
2040 End loop;
2041
2042 l_loop_visited_counter := l_loop_visited_counter + 1;
2043
2044 if ((g_approvers(l_loop_visited_counter).item_key = itemkey) and
2045 (g_approvers(l_loop_visited_counter).sequence = l_loop_visited_counter))then
2046 l_approver_name := g_approvers(l_loop_visited_counter).approver_name;
2047 l_approver_display_name := g_approvers(l_loop_visited_counter).approver_display_name;
2048
2049 IF l_approver_name IS NULL THEN
2050 RAISE NO_DATA_FOUND;
2051 End If;
2052
2053 wf_engine.SetItemAttrText( ItemType => itemtype,
2054 ItemKey => itemkey,
2055 aname => 'APPROVER_NAME',
2056 avalue => l_approver_name );
2057
2058 -- Added for bug 3394080
2059 wf_engine.SetItemAttrText( ItemType => itemtype,
2060 ItemKey => itemkey,
2061 aname => 'APPROVER_DISPLAY_NAME',
2062 avalue => l_approver_display_name );
2063 end if;
2064
2065
2066 wf_engine.SetItemAttrNumber( ItemType => ItemType,
2067 ItemKey => ItemKey,
2068 aname => 'LOOP_VISITED_COUNTER',
2069 avalue => l_loop_visited_counter );
2070 result := 'COMPLETE' ;
2071
2072 END IF ;
2073
2074 IF ( funcmode = 'CANCEL' ) THEN
2075 --
2076 result := 'COMPLETE' ;
2077 --
2078 END IF;
2079
2080 --
2081 -- In future implementations, appropriate code is to be inserted here.
2082 --
2083 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2084 --
2085 result := '' ;
2086 --
2087 END IF;
2088
2089
2090 EXCEPTION
2091 --
2092 WHEN NO_DATA_FOUND THEN
2093 wf_core.context('PSBBR', 'Find_Approver',
2094 itemtype, itemkey, to_char(actid), funcmode);
2095 RAISE ;
2096
2097 WHEN OTHERS THEN
2098 wf_core.context('PSBBR', 'Find_Approver',
2099 itemtype, itemkey, to_char(actid), funcmode);
2100 RAISE ;
2101
2102 END Find_Approver ;
2103
2104 /*===========================================================================+
2105 | PROCEDURE Set_Reviewed_Flag |
2106 +===========================================================================*/
2107 --
2108 -- The API sets the attribute 'REVIWED_FLAG' to 'Y' as the Approver has
2109 -- reviewed the Revision by now.
2110 --
2111 PROCEDURE Set_Reviewed_Flag
2112 (
2113 itemtype IN VARCHAR2,
2114 itemkey IN VARCHAR2,
2115 actid IN NUMBER,
2116 funcmode IN VARCHAR2,
2117 result OUT NOCOPY VARCHAR2
2118 )
2119 IS
2120 BEGIN
2121
2122 IF ( funcmode = 'RUN' ) THEN
2123
2124 wf_engine.SetItemAttrText( ItemType => itemtype,
2125 ItemKey => itemkey,
2126 aname => 'REVIEWED_FLAG',
2127 avalue => 'Y' );
2128
2129 result := 'COMPLETE' ;
2130
2131 END IF ;
2132
2133 IF ( funcmode = 'CANCEL' ) THEN
2134 --
2135 result := 'COMPLETE' ;
2136 --
2137 END IF;
2138
2139 --
2140 -- In future implementations, appropriate code is to be inserted here.
2141 --
2142 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2143 --
2144 result := '' ;
2145 --
2146 END IF;
2147
2148 EXCEPTION
2149 --
2150 WHEN OTHERS THEN
2151 wf_core.context('PSBBR', 'Set_Reviewed_Flag',
2152 itemtype, itemkey, to_char(actid), funcmode);
2153 RAISE ;
2154
2155 END Set_Reviewed_Flag ;
2156
2157 /*===========================================================================+
2158 | PROCEDURE Callback |
2159 +===========================================================================*/
2160 --
2161 -- The callback API.
2162 --
2163 PROCEDURE Callback
2164 (
2165 command IN VARCHAR2,
2166 context IN VARCHAR2,
2167 attr_name IN VARCHAR2,
2168 attr_type IN VARCHAR2,
2169 text_value IN OUT NOCOPY VARCHAR2,
2170 number_value IN OUT NOCOPY NUMBER,
2171 date_value IN OUT NOCOPY DATE
2172 )
2173 IS
2174 --
2175 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
2176 --
2177 l_notification_id NUMBER ;
2178 /*For Bug No : 2127425 Start*/
2179 Cursor C_Justification IS
2180 SELECT justification
2181 FROM psb_budget_revisions
2182 WHERE budget_revision_id = g_budget_revision_id;
2183 /*For Bug No : 2127425 End*/
2184
2185 BEGIN
2186 --
2187 IF ( command = 'GET' ) THEN
2188 --
2189 IF attr_name = 'BUDGET_REVISION_ID' THEN
2190
2191 number_value := g_budget_revision_id ;
2192
2193 ELSIF attr_name = 'BUDGET_GROUP_NAME' THEN
2194
2195 text_value := g_budgetgroup_name ;
2196
2197 ELSIF attr_name = 'REQUESTOR_NAME' THEN
2198
2199 text_value := g_requestor_name ;
2200 /*For Bug No : 2127425 Start*/
2201 ELSIF ((attr_name = 'JUSTIFICATION') AND (g_budget_revision_id IS NOT NULL)) THEN
2202
2203 For C_Justification_Rec in C_Justification Loop
2204 text_value := C_Justification_Rec.justification;
2205 End Loop;
2206 /*For Bug No : 2127425 End*/
2207
2208 ELSIF attr_name = 'FROM_ROLE' THEN -- bug 2576222
2209
2210 text_value := nvl(g_user_name,fnd_global.user_name) ;
2211
2212 END IF ;
2213 --
2214 END IF ;
2215
2216 /*
2217 IF ( command = 'SET' ) THEN
2218 --
2219 IF attr_name = 'REPLY' THEN
2220 wf_engine.SetItemAttrText( ItemType => g_itemtype,
2221 ItemKey => g_itemkey,
2222 aname => 'OPERATION_TYPE',
2223 avalue => 'XXX' );
2224
2225 text_value := 'REPLY' ;
2226 END IF ;
2227 --
2228 END IF ;
2229
2230 IF ( command = 'COMPLETE' ) THEN
2231 NULL ;
2232 END IF ;
2233 */
2234
2235 EXCEPTION
2236 --
2237 WHEN OTHERS THEN
2238 RAISE ;
2239
2240 END Callback ;
2241
2242
2243 /*===========================================================================+
2244 | PROCEDURE Start_Distribution_Process |
2245 +===========================================================================*/
2246 --
2247 -- The API creates an instance of the item type 'PSBBR' and start the workflow
2248 -- process 'Distribute Budget Revision'.
2249 --
2250 PROCEDURE Start_Distribution_Process
2251 (
2252 p_api_version IN NUMBER ,
2253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2254 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2256 p_return_status OUT NOCOPY VARCHAR2 ,
2257 p_msg_count OUT NOCOPY NUMBER ,
2258 p_msg_data OUT NOCOPY VARCHAR2 ,
2259 --
2260 p_item_key IN NUMBER ,
2261 p_distribution_instructions IN VARCHAR2 ,
2262 p_recipient_name IN VARCHAR2
2263 )
2264 IS
2265 --
2266 l_api_name CONSTANT VARCHAR2(30) := 'Start_Distribution_Process' ;
2267 l_api_version CONSTANT NUMBER := 1.0 ;
2268 --
2269 l_return_status VARCHAR2(1) ;
2270 l_msg_count NUMBER ;
2271 l_msg_data VARCHAR2(2000) ;
2272 --
2273 l_ItemType VARCHAR2(100) := 'PSBBR' ;
2274 l_ItemKey VARCHAR2(100) := p_item_key ;
2275 --
2276 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
2277
2278 BEGIN
2279 --
2280 SAVEPOINT Start_Distribution_Process_Pvt ;
2281 --
2282 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2283 p_api_version,
2284 l_api_name,
2285 G_PKG_NAME )
2286 THEN
2287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2288 END IF;
2289 --
2290
2291 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
2292 FND_MSG_PUB.initialize ;
2293 END IF;
2294 --
2295 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2296 --
2297
2298 WF_Engine.CreateProcess
2299 (
2300 ItemType => l_ItemType,
2301 ItemKey => l_ItemKey,
2302 Process => 'DISTRIBUTE_REVISION'
2303 );
2304
2305 --
2306 -- Get p_item_key related information.
2307 --
2308 SELECT worksheet_id INTO l_budget_revision_id
2309 FROM psb_workflow_processes
2310 WHERE item_key = p_item_key
2311 AND document_type = 'BR';
2312
2313 --
2314 -- Set budget_revision_id as the Item User Key for the process.
2315 --
2316 WF_Engine.SetItemUserKey
2317 (
2318 ItemType => l_ItemType ,
2319 ItemKey => l_ItemKey ,
2320 UserKey => l_budget_revision_id
2321 );
2322
2323 --
2324 WF_Engine.SetItemAttrNumber
2325 (
2326 ItemType => l_ItemType,
2327 ItemKey => l_itemkey,
2328 aname => 'BUDGET_REVISION_ID',
2329 avalue => l_budget_revision_id
2330 );
2331
2332 --
2333 WF_Engine.SetItemAttrText
2334 (
2335 ItemType => l_ItemType,
2336 ItemKey => l_itemkey,
2337 aname => 'DISTRIBUTION_INSTRUCTIONS',
2338 avalue => p_distribution_instructions
2339 );
2340
2341 --
2342 WF_Engine.SetItemAttrText
2343 (
2344 ItemType => l_ItemType,
2345 ItemKey => l_itemkey,
2346 aname => 'RECIPIENT_NAME',
2347 avalue => p_recipient_name
2348 );
2349
2350 --
2351 WF_Engine.StartProcess
2352 (
2353 ItemType => l_ItemType,
2354 ItemKey => l_ItemKey
2355 );
2356
2357 --
2358 IF FND_API.To_Boolean ( p_commit ) THEN
2359 COMMIT WORK;
2360 END IF;
2361 --
2362 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2363 p_data => p_msg_data );
2364 --
2365 EXCEPTION
2366 --
2367 WHEN FND_API.G_EXC_ERROR THEN
2368 --
2369 ROLLBACK TO Start_Distribution_Process_Pvt ;
2370 p_return_status := FND_API.G_RET_STS_ERROR;
2371 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2372 p_data => p_msg_data );
2373 --
2374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2375 --
2376 ROLLBACK TO Start_Distribution_Process_Pvt ;
2377 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2379 p_data => p_msg_data );
2380 --
2381 WHEN OTHERS THEN
2382 --
2383 ROLLBACK TO Start_Distribution_Process_Pvt ;
2384 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2385 --
2386 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2387 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2388 l_api_name);
2389 END if;
2390 --
2391 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2392 p_data => p_msg_data );
2393 --
2394 END Start_Distribution_Process ;
2395 /*---------------------------------------------------------------------------*/
2396
2397
2398
2399 /*===========================================================================+
2400 | PROCEDURE Find_Requestor |
2401 +===========================================================================*/
2402 --
2403 -- The API finds out if the requestor is different from submitter (submitter
2404 -- indicates the set of users assigned to the role associated to the
2405 -- budget group .
2406 --
2407 PROCEDURE Find_Requestor
2408 (
2409 itemtype IN VARCHAR2,
2410 itemkey IN VARCHAR2,
2411 actid IN NUMBER,
2412 funcmode IN VARCHAR2,
2413 result OUT NOCOPY VARCHAR2
2414 )
2415
2416 IS
2417
2418 l_requestor_name VARCHAR2(100);
2419 l_submitter_name VARCHAR2(80);
2420
2421 BEGIN
2422
2423 IF ( funcmode = 'RUN' ) THEN
2424 --
2425 -- Get Requestor Name item_attribute.
2426 --
2427 l_requestor_name := WF_Engine.GetItemAttrNumber
2428 (
2429 itemtype => itemtype,
2430 itemkey => itemkey,
2431 aname => 'REQUESTOR_NAME'
2432 );
2433
2434 l_submitter_name := WF_Engine.GetItemAttrNumber
2435 (
2436 itemtype => itemtype,
2437 itemkey => itemkey,
2438 aname => 'SUBMITTER_NAME'
2439 );
2440
2441 if (l_requestor_name = l_submitter_name) then
2442 result := 'COMPLETE:NO' ;
2443 else
2444 result := 'COMPLETE:YES' ;
2445 end if;
2446
2447 END IF ;
2448
2449 IF ( funcmode = 'CANCEL' ) THEN
2450 --
2451 result := 'COMPLETE' ;
2452 --
2453 END IF;
2454
2455 --In future implementations, appropriate code is to be inserted here.
2456 --
2457 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2458 --
2459 result := '' ;
2460 --
2461 END IF;
2462
2463 EXCEPTION
2464 --
2465 WHEN OTHERS THEN
2466 wf_core.context('PSBBR', 'Find Requestor',
2467 itemtype, itemkey, to_char(actid), funcmode);
2468 RAISE ;
2469
2470 End Find_Requestor;
2471
2472 /*===========================================================================+
2473 | PROCEDURE Populate_Distribute_Revision |
2474 +===========================================================================*/
2475 --
2476 -- The API populates the item attribues of the item type 'PSBBR'.
2477 --
2478 PROCEDURE Populate_Distribute_Revision
2479 (
2480 itemtype IN VARCHAR2,
2481 itemkey IN VARCHAR2,
2482 actid IN NUMBER,
2483 funcmode IN VARCHAR2,
2484 result OUT NOCOPY VARCHAR2
2485 )
2486 IS
2487 --
2488 l_budget_revision_id psb_budget_revisions.budget_revision_id%TYPE ;
2489 l_budget_group_name psb_budget_groups.name%TYPE ;
2490 l_recipient_name VARCHAR2(2000);
2491 l_user_name VARCHAR2(100);
2492 --
2493 BEGIN
2494 --
2495 IF ( funcmode = 'RUN' ) THEN
2496
2497 /* Bug 2576222 Start */
2498 l_user_name := fnd_global.user_name;
2499 /* Bug 2576222 End */
2500 --
2501 -- Get budget_revision_id item_attribute.
2502 --
2503 l_budget_revision_id := WF_Engine.GetItemAttrNumber
2504 (
2505 itemtype => itemtype,
2506 itemkey => itemkey,
2507 aname => 'BUDGET_REVISION_ID'
2508 );
2509
2510 --
2511 -- Finding Revision information.
2512 --
2513 SELECT budget_group_name
2514 INTO l_budget_group_name
2515 FROM psb_budget_revisions_v
2516 WHERE budget_revision_id = l_budget_revision_id;
2517
2518 --
2519 WF_Engine.SetItemAttrText
2520 (
2521 itemtype => itemtype,
2522 itemkey => itemkey,
2523 aname => 'BUDGET_GROUP_NAME',
2524 avalue => l_budget_group_name
2525 );
2526
2527 /* Bug 2576222 Start */
2528 wf_engine.SetItemAttrtext( ItemType => ItemType,
2529 ItemKey => Itemkey,
2530 aname => 'FROM_ROLE',
2531 avalue => l_user_name );
2532 /* Bug 2576222 End */
2533
2534 result := 'COMPLETE' ;
2535
2536 END IF ;
2537 --
2538
2539 IF ( funcmode = 'CANCEL' ) THEN
2540 result := 'COMPLETE' ;
2541 END IF;
2542
2543 --
2544 -- In future implementations, appropriate code is to be inserted here.
2545 --
2546 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2547 result := '' ;
2548 END IF;
2549
2550 EXCEPTION
2551 --
2552 WHEN OTHERS THEN
2553 wf_core.context('PSBBR', 'Populate_Distribute_Revision',
2554 itemtype, itemkey, to_char(actid), funcmode);
2555 RAISE ;
2556
2557 END Populate_Distribute_Revision ;
2558
2559
2560 PROCEDURE Set_Approval_Status
2561 (
2562 itemtype IN VARCHAR2 ,
2563 itemkey IN VARCHAR2 ,
2564 actid IN NUMBER ,
2565 funcmode IN VARCHAR2 ,
2566 result OUT NOCOPY VARCHAR2
2567 )
2568 IS
2569 BEGIN
2570
2571 IF ( funcmode = 'RUN' ) THEN
2572
2573 wf_engine.SetItemAttrText( ItemType => itemtype,
2574 ItemKey => itemkey,
2575 aname => 'SUBMISSION_STATUS',
2576 avalue => 'A' );
2577 result := 'COMPLETE' ;
2578
2579 END IF ;
2580
2581 IF ( funcmode = 'CANCEL' ) THEN
2582 --
2583 result := 'COMPLETE' ;
2584 --
2585 END IF;
2586
2587 --
2588 -- In future implementations, appropriate code is to be inserted here.
2589 --
2590 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2591 --
2592 result := '' ;
2593 --
2594 END IF;
2595
2596 EXCEPTION
2597 --
2598 WHEN OTHERS THEN
2599 wf_core.context('PSBBR', 'Set_Approval_status',
2600 itemtype, itemkey, to_char(actid), funcmode);
2601 RAISE ;
2602
2603 END Set_Approval_Status;
2604
2605 PROCEDURE Set_Rejection_Status
2606 (
2607 itemtype IN VARCHAR2 ,
2608 itemkey IN VARCHAR2 ,
2609 actid IN NUMBER ,
2610 funcmode IN VARCHAR2 ,
2611 result OUT NOCOPY VARCHAR2
2612 )
2613 IS
2614 BEGIN
2615
2616 IF ( funcmode = 'RUN' ) THEN
2617
2618 wf_engine.SetItemAttrText( ItemType => itemtype,
2619 ItemKey => itemkey,
2620 aname => 'SUBMISSION_STATUS',
2621 avalue => 'R' );
2622 result := 'COMPLETE' ;
2623
2624 END IF ;
2625
2626 IF ( funcmode = 'CANCEL' ) THEN
2627 --
2628 result := 'COMPLETE' ;
2629 --
2630 END IF;
2631
2632 --
2633 -- In future implementations, appropriate code is to be inserted here.
2634 --
2635 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2636 --
2637 result := '' ;
2638 --
2639 END IF;
2640
2641 EXCEPTION
2642 --
2643 WHEN OTHERS THEN
2644 wf_core.context('PSBBR', 'Set_Rejaection_Status',
2645 itemtype, itemkey, to_char(actid), funcmode);
2646 RAISE ;
2647
2648 END Set_Rejection_Status ;
2649
2650 /*===========================================================================+
2651 | PROCEDURE Validate_Revision_Rules |
2652 +===========================================================================*/
2653 -- Used for Validating budget revision rules
2654 --
2655 PROCEDURE Validate_Revision_Rules
2656 (
2657 itemtype IN VARCHAR2,
2658 itemkey IN VARCHAR2,
2659 actid IN NUMBER,
2660 funcmode IN VARCHAR2,
2661 result OUT NOCOPY VARCHAR2
2662 )
2663 IS
2664 --
2665 l_return_status VARCHAR2(1);
2666 l_msg_count NUMBER;
2667 l_msg_data VARCHAR2(2000);
2668 --
2669 l_budget_revision_id NUMBER(20);
2670 l_validation_status VARCHAR2(1);
2671 l_operation_type VARCHAR2(20);
2672 BEGIN
2673 --
2674 IF ( funcmode = 'RUN' ) THEN
2675 l_budget_revision_id := wf_engine.GetItemAttrNumber
2676 (
2677 itemtype => itemtype,
2678 itemkey => itemkey,
2679 aname => 'BUDGET_REVISION_ID'
2680 );
2681
2682 l_operation_type := wf_engine.GetItemAttrText
2683 (
2684 itemtype => itemtype,
2685 itemkey => itemkey,
2686 aname => 'OPERATION_TYPE'
2687 );
2688
2689
2690 PSB_Budget_Revisions_PVT.Apply_Revision_Rules
2691 (
2692 p_api_version => 1.0,
2693 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2694 p_return_status => l_return_status,
2695 p_validation_status => l_validation_status,
2696 p_budget_revision_id => l_budget_revision_id
2697 );
2698 --
2699
2700 IF l_validation_status = 'F' THEN
2701 --
2702 result := 'COMPLETE:FAIL' ;
2703 --
2704 ELSE
2705 --
2706 result := 'COMPLETE:SUCCESS' ;
2707 --
2708 END IF ;
2709
2710 ELSIF (funcmode = 'CANCEL' ) THEN
2711 --
2712 result := 'COMPLETE' ;
2713 --
2714 ELSIF (funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2715 --
2716 result := '' ;
2717 --
2718 END IF;
2719
2720 EXCEPTION
2721 --
2722 WHEN OTHERS THEN
2723 wf_core.context('PSBBR',
2724 'Validate_Revision_Rules',
2725 PSB_Message_S.Get_Error_Stack(l_msg_count) );
2726 RAISE;
2727
2728 END Validate_Revision_Rules;
2729
2730 /*---------------------------------------------------------------------------*/
2731
2732 END PSB_Submit_Revision_PVT;