[Home] [Help]
PACKAGE BODY: APPS.PSB_SUBMIT_WORKSHEET_PVT
Source
1 PACKAGE BODY PSB_Submit_Worksheet_PVT AS
2 /* $Header: PSBWSSPB.pls 120.8 2005/08/25 10:47:26 matthoma ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PSB_Submit_Worksheet_PVT';
5
6 /*--------------------------- Global variables -----------------------------*/
7
8 --
9 -- CALLBACK procedure related global information.
10 --
11 g_worksheet_id psb_worksheets.worksheet_id%TYPE ;
12 g_worksheet_name psb_worksheets.name%TYPE ;
13 g_budget_group_name psb_budget_groups.name%TYPE ;
14 g_itemtype VARCHAR2(2000) ;
15 g_itemkey VARCHAR2(2000) ;
16
17 -- WHO columns variables
18 g_current_date DATE := sysdate ;
19 g_current_user_id NUMBER := NVL( Fnd_Global.User_Id , 0) ;
20 g_current_login_id NUMBER := NVL( Fnd_Global.Login_Id , 0) ;
21 g_user_name VARCHAR2(100);
22
23 /*----------------------- End Global variables -----------------------------*/
24
25
26 /*===========================================================================+
27 | PROCEDURE Start_Process |
28 +===========================================================================*/
29 --
30 -- The API creates an instance of the item type 'PSBWS' and starts the workflow
31 -- process 'Submit Worksheet'.
32 --
33 PROCEDURE Start_Process
34 (
35 p_api_version IN NUMBER ,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
39 p_return_status OUT NOCOPY VARCHAR2 ,
40 p_msg_count OUT NOCOPY NUMBER ,
41 p_msg_data OUT NOCOPY VARCHAR2 ,
42 --
43 p_item_key IN VARCHAR2 ,
44 p_submitter_id IN NUMBER ,
45 p_submitter_name IN VARCHAR2 ,
46 p_operation_type IN VARCHAR2 ,
47 p_review_group_flag IN VARCHAR2 := 'N',
48 p_orig_system IN VARCHAR2 ,
49 p_merge_to_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
50 p_comments IN VARCHAR2 ,
51 p_operation_id IN NUMBER ,
52 p_constraint_set_id IN NUMBER
53 )
54 IS
55 --
56 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process' ;
57 l_api_version CONSTANT NUMBER := 1.0 ;
58 --
59 l_return_status VARCHAR2(1) ;
60 l_msg_count NUMBER ;
61 l_msg_data VARCHAR2(2000) ;
62 --
63 l_ItemType VARCHAR2(100) := 'PSBWS';
64 l_ItemKey VARCHAR2(240) := p_item_key;
65 --
66 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
67 l_comments VARCHAR2(2000) ;
68 --
69 BEGIN
70 --
71 /* Bug 2576222 Start */
72 g_user_name := fnd_global.user_name;
73 /* Bug 2576222 End */
74
75 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
76 FND_MSG_PUB.initialize ;
77 END IF;
78 --
79 p_return_status := FND_API.G_RET_STS_SUCCESS ;
80
81 --
82 -- Get p_item_key related information.
83 --
84 SELECT worksheet_id INTO l_worksheet_id
85 FROM psb_workflow_processes
86 WHERE item_key = p_item_key ;
87
88
89 wf_engine.CreateProcess ( ItemType => l_ItemType,
90 ItemKey => l_ItemKey,
91 Process => 'SUBMIT_WORKSHEET' );
92
93 --
94 -- Set worksheet_id as the Item User Key for the process.
95 --
96 WF_Engine.SetItemUserKey
97 (
98 ItemType => l_ItemType ,
99 ItemKey => l_ItemKey ,
100 UserKey => l_worksheet_id
101 );
102
103 --
104 -- Populate item type.
105 --
106 wf_engine.SetItemAttrNumber( ItemType => l_ItemType,
107 ItemKey => l_itemkey,
108 aname => 'WORKSHEET_ID',
109 avalue => l_worksheet_id );
110 --
111 wf_engine.SetItemAttrNumber( ItemType => l_ItemType,
112 ItemKey => l_itemkey,
113 aname => 'LOOP_VISITED_COUNTER',
114 avalue => 0 );
115 --
116 wf_engine.SetItemAttrText( ItemType => l_itemtype,
117 ItemKey => l_itemkey,
118 aname => 'SUBMITTER_ID',
119 avalue => p_submitter_id );
120
121 /* Bug 2576222 Start */
122 wf_engine.SetItemAttrtext( ItemType => l_ItemType,
123 ItemKey => l_itemkey,
124 aname => 'FROM_ROLE',
125 avalue => g_user_name );
126 /* Bug 2576222 End */
127
128 --
129 wf_engine.SetItemAttrText( ItemType => l_itemtype,
130 ItemKey => l_itemkey,
131 aname => 'SUBMITTER_NAME',
132 avalue => p_submitter_name );
133 --
134 wf_engine.SetItemAttrText( ItemType => l_itemtype,
135 ItemKey => l_itemkey,
136 aname => 'OPERATION_TYPE',
137 avalue => p_operation_type );
138 --
139 wf_engine.SetItemAttrText( ItemType => l_itemtype,
140 ItemKey => l_itemkey,
141 aname => 'REVIEW_GROUP_FLAG',
142 avalue => p_review_group_flag );
143 --
144 wf_engine.SetItemAttrText( ItemType => l_itemtype,
145 ItemKey => l_itemkey,
146 aname => 'ORIG_SYSTEM',
147 avalue => p_orig_system );
148 --
149 wf_engine.SetItemAttrNumber( ItemType => l_itemtype,
150 ItemKey => l_itemkey,
151 aname => 'MERGE_TO_WORKSHEET_ID',
152 avalue => p_merge_to_worksheet_id );
153 --
154 wf_engine.SetItemAttrNumber( ItemType => l_itemtype,
155 ItemKey => l_itemkey,
156 aname => 'OPERATION_ID',
157 avalue => p_operation_id );
158 --
159 wf_engine.SetItemAttrNumber( ItemType => l_itemtype,
160 ItemKey => l_itemkey,
161 aname => 'CONSTRAINT_SET_ID',
162 avalue => p_constraint_set_id );
163 --
164
165 --
166 -- Populate comments.
167 --
168 IF p_comments = 'Y' THEN
169
170 -- Retrieve the comments.
171 SELECT comments INTO l_comments
172 FROM psb_ws_submit_comments
173 WHERE operation_id = p_operation_id;
174 --
175 wf_engine.SetItemAttrText( ItemType => l_itemtype,
176 ItemKey => l_itemkey,
177 aname => 'COMMENTS',
178 avalue => l_comments );
179 END IF;
180
181
182 --
183 -- Start the process
184 --
185 wf_engine.StartProcess ( ItemType => l_ItemType,
186 ItemKey => l_ItemKey );
187
188 --
189 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
190 p_data => p_msg_data );
191 --
192 EXCEPTION
193 --
194 WHEN OTHERS THEN
195 --
196 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 --
198 RAISE ;
199
200 END Start_Process ;
201 /*---------------------------------------------------------------------------*/
202
203
204 /*===========================================================================+
205 | PROCEDURE Populate_Worksheet |
206 +===========================================================================*/
207 --
208 -- The API populates the item attribues of the item type 'PSBWS'.
209 -- ( The SUBMITTER_NAME is populated by the interface API 'PSBWKFLB.pls'.)
210 --
211 PROCEDURE Populate_Worksheet
212 (
213 itemtype IN VARCHAR2,
214 itemkey IN VARCHAR2,
215 actid IN NUMBER,
216 funcmode IN VARCHAR2,
217 result OUT NOCOPY VARCHAR2
218 )
219 IS
220 --
221 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
222 l_worksheet_name psb_worksheets.name%TYPE ;
223 l_budget_group_name psb_budget_groups.name%TYPE ;
224 l_submitter_name VARCHAR2(80);
225 --
226 l_orig_system VARCHAR2(8) ;
227 l_submitter_id NUMBER ;
228 l_tmp_char VARCHAR2(200) ;
229 --
230 BEGIN
231 --
232 IF ( funcmode = 'RUN' ) THEN
233 --
234 -- Get worksheet_id item_attribute.
235 --
236 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
237 itemkey => itemkey,
238 aname => 'WORKSHEET_ID');
239
240 --
241 -- Finding worksheet information.
242 --
243 SELECT name ,
244 budget_group_name
245 INTO
246 l_worksheet_name ,
247 l_budget_group_name
248 FROM psb_worksheets_v
249 WHERE worksheet_id = l_worksheet_id;
250
251 --
252 wf_engine.SetItemAttrText ( itemtype => itemtype,
253 itemkey => itemkey,
254 aname => 'WORKSHEET_NAME',
255 avalue => l_worksheet_name );
256 --
257 wf_engine.SetItemAttrText ( itemtype => itemtype,
258 itemkey => itemkey,
259 aname => 'BUDGET_GROUP_NAME',
260 avalue => l_budget_group_name );
261 --
262 result := 'COMPLETE' ;
263 END IF ;
264
265 IF ( funcmode = 'CANCEL' ) THEN
266 result := 'COMPLETE' ;
267 END IF;
268 --
269
270 --
271 -- In future implementations, appropriate code is to be inserted here.
272 --
273 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
274 result := '' ;
275 END IF;
276
277 EXCEPTION
278 --
279 WHEN OTHERS THEN
280 wf_core.context('PSBWS', 'Populate_Worksheet',
281 itemtype, itemkey, to_char(actid), funcmode);
282 RAISE ;
283
284 END Populate_Worksheet ;
285 /*---------------------------------------------------------------------------*/
286
287
288 /*===========================================================================+
289 | PROCEDURE Enforce_Concurrency_Check |
290 +===========================================================================*/
291 --
292 -- The activity implements Enforce_Concurrency_Check workflow activity.
293 --
294 PROCEDURE Enforce_Concurrency_Check
295 (
296 itemtype IN VARCHAR2,
297 itemkey IN VARCHAR2,
298 actid IN NUMBER,
299 funcmode IN VARCHAR2,
300 result OUT NOCOPY VARCHAR2
301 )
302 IS
303 --
304 l_return_status VARCHAR2(1) ;
305 l_msg_count NUMBER ;
306 l_msg_data VARCHAR2(2000) ;
307 --
308 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
309 l_operation_type VARCHAR2(20) ;
310 --
311 BEGIN
312
313 l_return_status := FND_API.G_RET_STS_SUCCESS ;
314
315 IF ( funcmode = 'RUN' ) THEN
316 --
317 -- Get operation_type item_attribute.
318 --
319 l_worksheet_id := wf_engine.GetItemAttrNumber
320 ( itemtype => itemtype,
321 itemkey => itemkey,
322 aname => 'WORKSHEET_ID' );
323
324 l_operation_type := wf_engine.GetItemAttrText
325 ( itemtype => itemtype,
326 itemkey => itemkey,
327 aname => 'OPERATION_TYPE' );
328 --
329 -- API to perform worksheet related concurrency control.
330 --
331 PSB_WS_Ops_Pvt.Check_WS_Ops_Concurrency
332 (
333 p_api_version => 1.0,
334 p_init_msg_list => FND_API.G_FALSE ,
335 p_commit => FND_API.G_FALSE ,
336 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
337 p_return_status => l_return_status,
338 p_msg_count => l_msg_count,
339 p_msg_data => l_msg_data,
340 --
341 p_worksheet_id => l_worksheet_id ,
342 p_operation_type => l_operation_type
343 );
344 --
345 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
346 -- (TBD) Need to specify why it failed (?).
347 result := 'COMPLETE:NO' ;
348 ELSE
349 result := 'COMPLETE:YES' ;
350 END IF ;
351 --
352 END IF ;
353
354 IF ( funcmode = 'CANCEL' ) THEN
355 result := 'COMPLETE' ;
356 END IF;
357
358 --
359 -- In future implementations, appropriate code is to be inserted here.
360 --
361 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
362 --
363 result := '' ;
364 --
365 END IF;
366
367 EXCEPTION
368 --
369 WHEN OTHERS THEN
370 wf_core.context('PSBWS', 'Enforce_Concurrency_Check',
371 PSB_Message_S.Get_Error_Stack(l_msg_count) );
372 RAISE ;
373
374 END Enforce_Concurrency_Check ;
375 /*---------------------------------------------------------------------------*/
376
377
378 /*===========================================================================+
379 | PROCEDURE Perform_Validation |
380 +===========================================================================*/
381 --
382 -- The API checks whether worksheet validation is required or not.
383 --
384 PROCEDURE Perform_Validation
385 (
386 itemtype IN VARCHAR2,
387 itemkey IN VARCHAR2,
388 actid IN NUMBER,
389 funcmode IN VARCHAR2,
390 result OUT NOCOPY VARCHAR2
391 )
392 IS
393 --
394 l_operation_type VARCHAR2(20) ;
395 --
396 BEGIN
397 --
398 IF ( funcmode = 'RUN' ) THEN
399 --
400 -- Get operation_type item_attribute.
401 --
402 l_operation_type := wf_engine.GetItemAttrText
403 ( itemtype => itemtype,
404 itemkey => itemkey,
405 aname => 'OPERATION_TYPE' );
406
407 IF l_operation_type IN ('COPY', 'MERGE') THEN
408 result := 'COMPLETE:NO' ;
409 ELSE
410 result := 'COMPLETE:YES' ;
411 END IF ;
412 --
413 END IF ;
414
415 IF ( funcmode = 'CANCEL' ) THEN
416 result := 'COMPLETE' ;
417 END IF;
418
419 --
420 -- In future implementations, appropriate code is to be inserted here.
421 --
422 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
423 --
424 result := '' ;
425 --
426 END IF;
427
428 EXCEPTION
429 --
430 WHEN OTHERS THEN
431 wf_core.context('PSBWS', 'Perform_Validation',
432 itemtype, itemkey, to_char(actid), funcmode);
433 RAISE ;
434
435 END Perform_Validation ;
436 /*---------------------------------------------------------------------------*/
437
438
439 /*===========================================================================+
440 | PROCEDURE Validate_Constraints |
441 +===========================================================================*/
442 --
443 -- The API calls worksheet validation API. If the validation fails, the
444 -- workflow process terminates and a notification is sent to the submitter.
445 --
446 PROCEDURE Validate_Constraints
447 (
448 itemtype IN VARCHAR2,
449 itemkey IN VARCHAR2,
450 actid IN NUMBER,
451 funcmode IN VARCHAR2,
452 result OUT NOCOPY VARCHAR2
453 )
454 IS
455 --
456 l_return_status VARCHAR2(1) ;
457 l_msg_count NUMBER ;
458 l_msg_data VARCHAR2(2000) ;
459 --
460 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
461 l_validation_status VARCHAR2(1) ;
462 l_operation_type VARCHAR2(20) ;
463 l_constraint_set_id NUMBER ;
464 --
465 BEGIN
466
467 IF ( funcmode = 'RUN' ) THEN
468 --
469 -- Get worksheet_id item_attribute.
470 --
471 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
472 itemkey => itemkey,
473 aname => 'WORKSHEET_ID');
474
475 l_operation_type := wf_engine.GetItemAttrText
476 ( itemtype => itemtype,
477 itemkey => itemkey,
478 aname => 'OPERATION_TYPE' );
479
480 --
481 -- Find constraint_set_id optionally needed for validation.
482 --
483 l_constraint_set_id := wf_engine.GetItemAttrNumber
484 ( itemtype => itemtype,
485 itemkey => itemkey,
486 aname => 'CONSTRAINT_SET_ID' );
487
488 IF l_operation_type IN ('COPY', 'MERGE') THEN
489 --
490 result := 'COMPLETE:SUCCESS' ;
491 RETURN ;
492 --
493 END IF ;
494
495 -- /* **** For testing.
496
497 --
498 -- Call the API to validate the worksheet.
499 --
500 PSB_Worksheet_Pvt.Apply_Constraints
501 (
502 p_api_version => 1.0 ,
503 p_init_msg_list => FND_API.G_FALSE ,
504 p_commit => FND_API.G_FALSE ,
505 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
506 p_return_status => l_return_status ,
507 p_validation_status => l_validation_status ,
508 p_msg_count => l_msg_count ,
509 p_msg_data => l_msg_data ,
510 --
511 p_worksheet_id => l_worksheet_id ,
512 p_constraint_set_id => l_constraint_set_id
513 ) ;
514 --
515 IF l_validation_status = 'F' THEN
516 result := 'COMPLETE:FAIL' ;
517 ELSE
518 result := 'COMPLETE:SUCCESS' ;
519 END IF ;
520 -- */
521
522 -- /* For testing. */
523 -- result := 'COMPLETE:SUCCESS' ;
524
525 END IF ;
526
527 IF ( funcmode = 'CANCEL' ) THEN
528 --
529 result := 'COMPLETE' ;
530 --
531 END IF;
532
533 --
534 -- In future implementations, appropriate code is to be inserted here.
535 --
536 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
537 --
538 result := '' ;
539 --
540 END IF;
541
542 EXCEPTION
543 --
544 WHEN OTHERS THEN
545 wf_core.context('PSBWS', 'Validate_Constraints',
546 PSB_Message_S.Get_Error_Stack(l_msg_count) );
547 RAISE ;
548
549 END Validate_Constraints ;
550 /*---------------------------------------------------------------------------*/
551
552
553 /*===========================================================================+
554 | PROCEDURE Select_Operation |
555 +===========================================================================*/
556 --
557 -- The API selects the operation to be performed on the worksheet. The
558 -- appropriate branch on the process is selected accordingly.
559 --
560 PROCEDURE Select_Operation
561 (
562 itemtype IN VARCHAR2,
563 itemkey IN VARCHAR2,
564 actid IN NUMBER,
565 funcmode IN VARCHAR2,
566 result OUT NOCOPY VARCHAR2
567 )
568 IS
569 --
570 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
571 l_operation_type VARCHAR2(20) ;
572 --
573 BEGIN
574 --
575 IF ( funcmode = 'RUN' ) THEN
576 --
577 -- Get worksheet_id item_attribute.
578 --
579 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
580 itemkey => itemkey,
581 aname => 'WORKSHEET_ID');
582 --
583 l_operation_type := wf_engine.GetItemAttrText( itemtype => itemtype,
584 itemkey => itemkey,
585 aname => 'OPERATION_TYPE');
586 --
587 -- Operation_type item attribute determines what operation is to
588 -- be performed on the worksheet.
589 --
590 IF l_operation_type = 'COPY' THEN
591 --
592 result := 'COMPLETE:COPY' ;
593 --
594 ELSIF l_operation_type = 'MERGE' THEN
595 --
596 result := 'COMPLETE:MERGE' ;
597 --
598 ELSIF l_operation_type = 'VALIDATE' THEN
599 --
600 result := 'COMPLETE:VALIDATE' ;
601 --
602 ELSIF l_operation_type = 'FREEZE' THEN
603 --
604 result := 'COMPLETE:FREEZE' ;
605 --
606 ELSIF l_operation_type = 'UNFREEZE' THEN
607 --
608 result := 'COMPLETE:UNFREEZE' ;
609 --
610 ELSIF l_operation_type = 'MOVE' THEN
611 --
612 result := 'COMPLETE:MOVE' ;
613 --
614 ELSIF l_operation_type = 'SUBMIT' THEN
615 --
616 result := 'COMPLETE:SUBMIT' ;
617 --
618 END IF ;
619
620 END IF ;
621
622 IF ( funcmode = 'CANCEL' ) THEN
623 --
624 result := 'COMPLETE' ;
625 --
626 END IF;
627
628 --
629 -- In future implementations, appropriate code is to be inserted here.
630 --
631 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
632 --
633 result := '' ;
634 --
635 END IF;
636
637 EXCEPTION
638 --
639 WHEN OTHERS THEN
640 wf_core.context('PSBWS', 'Select_Operation',
641 itemtype, itemkey, to_char(actid), funcmode);
642 RAISE ;
643
644 END Select_Operation ;
645 /*---------------------------------------------------------------------------*/
646
647
648 /*===========================================================================+
649 | PROCEDURE Copy_Worksheet |
650 +===========================================================================*/
651 --
652 -- The API calls a program to make a copy of the submitted worksheet.
653 --
654 PROCEDURE Copy_Worksheet
655 (
656 itemtype IN VARCHAR2,
657 itemkey IN VARCHAR2,
658 actid IN NUMBER,
659 funcmode IN VARCHAR2,
660 result OUT NOCOPY VARCHAR2
661 )
662 IS
663 --
664 l_return_status VARCHAR2(1) ;
665 l_msg_count NUMBER ;
666 l_msg_data VARCHAR2(2000) ;
667 --
668 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
669 l_new_worksheet_id psb_worksheets.worksheet_id%TYPE ;
670 l_worksheet_name psb_worksheets.name%TYPE ;
671 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
672 l_current_freeze_flag psb_worksheets.freeze_flag%TYPE ;
673 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
674
675 --
676 l_notification_id NUMBER ;
677 l_submitter_name VARCHAR2(80);
678 l_operation_type VARCHAR2(20) ;
679 --
680 BEGIN
681 --
682 IF ( funcmode = 'RUN' ) THEN
683 --
684 -- Get worksheet_id item attribute.
685 --
686 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
687 itemkey => itemkey,
688 aname => 'WORKSHEET_ID');
689
690 --
691 -- Call the API.
692 --
693 PSB_WS_Ops_Pvt.Copy_Worksheet
694 (
695 p_api_version => 1.0 ,
696 p_init_msg_list => FND_API.G_TRUE,
697 p_commit => FND_API.G_FALSE,
698 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
699 p_return_status => l_return_status,
700 p_msg_count => l_msg_count,
701 p_msg_data => l_msg_data,
702 --
703 p_worksheet_id => l_worksheet_id,
704 p_worksheet_id_OUT => l_new_worksheet_id
705 );
706 --
707 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
709 END IF ;
710 --
711
712 --
713 -- Set COPY_WORKSHEET_ID item attribute.
714 --
715 wf_engine.SetItemAttrNumber( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'COPY_WORKSHEET_ID',
718 avalue => l_new_worksheet_id );
719 --
720
721 result := 'COMPLETE' ;
722
723 END IF ;
724
725 IF ( funcmode = 'CANCEL' ) THEN
726 --
727 result := 'COMPLETE' ;
728 --
729 END IF;
730
731 -- In future implementations, appropriate code is to be inserted here.
732 --
733 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
734 --
735 result := '' ;
736 --
737 END IF;
738
739 EXCEPTION
740 --
741 WHEN OTHERS THEN
742 wf_core.context('PSBWS', 'Copy_Worksheet',
743 PSB_Message_S.Get_Error_Stack(l_msg_count) );
744 RAISE ;
745
746 END Copy_Worksheet ;
747 /*---------------------------------------------------------------------------*/
748
749
750 /*===========================================================================+
751 | PROCEDURE Merge_Worksheets |
752 +===========================================================================*/
753 --
754 -- The API calls a program to merge the given worksheets.
755 --
756 PROCEDURE Merge_Worksheets
757 (
758 itemtype IN VARCHAR2,
759 itemkey IN VARCHAR2,
760 actid IN NUMBER,
761 funcmode IN VARCHAR2,
762 result OUT NOCOPY VARCHAR2
763 )
764 IS
765 --
766 l_return_status VARCHAR2(1) ;
767 l_msg_count NUMBER ;
768 l_msg_data VARCHAR2(2000) ;
769 --
770 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
771 l_merge_to_worksheet_id psb_worksheets.worksheet_id%TYPE ;
772 l_worksheet_name psb_worksheets.name%TYPE ;
773 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
774 l_current_freeze_flag psb_worksheets.freeze_flag%TYPE ;
775 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
776
777 --
778 l_notification_id NUMBER ;
779 l_submitter_name VARCHAR2(80);
780 l_operation_type VARCHAR2(20) ;
781 --
782 BEGIN
783 --
784 IF ( funcmode = 'RUN' ) THEN
785 --
786 -- Get worksheet_id item attribute.
787 --
788 l_worksheet_id := wf_engine.GetItemAttrNumber
789 (
790 itemtype => itemtype,
791 itemkey => itemkey,
792 aname => 'WORKSHEET_ID'
793 );
794 --
795 l_merge_to_worksheet_id := wf_engine.GetItemAttrNumber
796 (
797 itemtype => itemtype,
798 itemkey => itemkey,
799 aname => 'MERGE_TO_WORKSHEET_ID'
800 );
801 --
802
803 --
804 -- Call the API.
805 --
806 PSB_WS_Ops_Pvt.Merge_Worksheets
807 (
808 p_api_version => 1.0 ,
809 p_init_msg_list => FND_API.G_TRUE,
810 p_commit => FND_API.G_FALSE,
811 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
812 p_return_status => l_return_status,
813 p_msg_count => l_msg_count,
814 p_msg_data => l_msg_data,
815 --
816 p_source_worksheet_id => l_worksheet_id,
817 p_target_worksheet_id => l_merge_to_worksheet_id
818 );
819 --
820 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
822 END IF ;
823 --
824
825 result := 'COMPLETE' ;
826
827
828 END IF ;
829
830 IF ( funcmode = 'CANCEL' ) THEN
831 --
832 result := 'COMPLETE' ;
833 --
834 END IF;
835
836 -- In future implementations, appropriate code is to be inserted here.
837 --
838 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
839 --
840 result := '' ;
841 --
842 END IF;
843
844 EXCEPTION
845 --
846 WHEN OTHERS THEN
847 wf_core.context('PSBWS', 'Merge_Worksheets',
848 PSB_Message_S.Get_Error_Stack(l_msg_count) );
849 RAISE ;
850
851 END Merge_Worksheets ;
852 /*---------------------------------------------------------------------------*/
853
854
855 /*===========================================================================+
856 | PROCEDURE Freeze_Worksheets |
857 +===========================================================================*/
858 --
859 -- The API freezes the submitted worksheet and all its lower worksheets.
860 --
861 PROCEDURE Freeze_Worksheets
862 (
863 itemtype IN VARCHAR2,
864 itemkey IN VARCHAR2,
865 actid IN NUMBER,
866 funcmode IN VARCHAR2,
867 result OUT NOCOPY VARCHAR2
868 )
869 IS
870 --
871 l_return_status VARCHAR2(1) ;
872 l_msg_count NUMBER ;
873 l_msg_data VARCHAR2(2000) ;
874 --
875 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
876 l_worksheet_name psb_worksheets.name%TYPE ;
877 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
878 l_current_freeze_flag psb_worksheets.freeze_flag%TYPE ;
879 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
880
881 --
882 l_notification_id NUMBER ;
883 l_submitter_name VARCHAR2(80);
884 l_operation_type VARCHAR2(20) ;
885 --
886 BEGIN
887 --
888 IF ( funcmode = 'RUN' ) THEN
889 --
890 -- Get worksheet_id item attribute.
891 --
892 l_worksheet_id := wf_engine.GetItemAttrNumber
893 ( itemtype => itemtype,
894 itemkey => itemkey,
895 aname => 'WORKSHEET_ID');
896 --
897 g_worksheet_name := wf_engine.GetItemAttrText
898 ( itemtype => itemtype,
899 itemkey => itemkey,
900 aname => 'WORKSHEET_NAME');
901 --
902 l_submitter_name := wf_engine.GetItemAttrText
903 ( itemtype => itemtype,
904 itemkey => itemkey,
905 aname => 'SUBMITTER_NAME' );
906 --
907 l_operation_type := wf_engine.GetItemAttrText
908 ( itemtype => itemtype,
909 itemkey => itemkey,
910 aname => 'OPERATION_TYPE' );
911
912 --
913 -- Setting context for the CALLBACK procedure.
914 --
915 SELECT budget_group_name INTO g_worksheet_name
916 FROM psb_worksheets_v
917 WHERE worksheet_id = l_worksheet_id ;
918
919 g_worksheet_id := l_worksheet_id ;
920 g_itemtype := itemtype ;
921 g_itemkey := itemkey ;
922
923 --
924 -- Freeze the top level worksheet.
925 --
926 PSB_WS_Ops_Pvt.Freeze_Worksheet
927 (
928 p_api_version => 1.0 ,
929 p_init_msg_list => FND_API.G_FALSE,
930 p_commit => FND_API.G_FALSE,
931 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
932 p_return_status => l_return_status,
933 p_msg_count => l_msg_count,
934 p_msg_data => l_msg_data,
935 --
936 p_worksheet_id => l_worksheet_id ,
937 p_freeze_flag => 'Y'
938 );
939 --
940 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
942 END IF ;
943
944 --
945 -- Send 'NOTIFY_OF_FREEZE_COMPLETION' notification to the top level budget
946 -- group users. To be done for operation_type 'MOVE' and 'SUBMIT'.
947 --
948 IF l_operation_type IN ('MOVE', 'SUBMIT') THEN
949 --
950 l_notification_id :=
951 WF_Notification.SendGroup
952 ( role => l_submitter_name ,
953 msg_type => 'PSBWS' ,
954 msg_name => 'NOTIFY_OF_FREEZE_COMPLETION' ,
955 context => itemtype ||':'|| itemkey ||':'|| actid ,
956 callback => 'PSB_Submit_Worksheet_PVT.Callback'
957 ) ;
958 END IF ;
959
960 --
961 -- Call API to find all lower level worksheets.
962 --
963 PSB_WS_Ops_Pvt.Find_Child_Worksheets
964 (
965 p_api_version => 1.0 ,
966 p_init_msg_list => FND_API.G_FALSE,
967 p_commit => FND_API.G_FALSE,
968 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
969 p_return_status => l_return_status,
970 p_msg_count => l_msg_count,
971 p_msg_data => l_msg_data,
972 --
973 p_worksheet_id => l_worksheet_id,
974 p_worksheet_tbl => l_worksheets_tab
975 );
976 --
977 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
979 END IF ;
980 --
981
982 --
983 -- Freeze all lower level worksheets
984 --
985 FOR i IN 1..l_worksheets_tab.COUNT
986 LOOP
987 --
988 -- Check whether the current worksheet is already frozen or not.
989 -- If already frozen, do not have to do anything.
990 --
991 SELECT NVL(freeze_flag, 'N') INTO l_current_freeze_flag
992 FROM psb_worksheets
993 WHERE worksheet_id = l_worksheets_tab(i) ;
994
995 IF l_current_freeze_flag = 'N' THEN
996 --
997 PSB_WS_Ops_Pvt.Freeze_Worksheet
998 (
999 p_api_version => 1.0 ,
1000 p_init_msg_list => FND_API.G_FALSE,
1001 p_commit => FND_API.G_FALSE,
1002 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1003 p_return_status => l_return_status,
1004 p_msg_count => l_msg_count,
1005 p_msg_data => l_msg_data,
1006 --
1007 p_worksheet_id => l_worksheets_tab(i) ,
1008 p_freeze_flag => 'Y'
1009 );
1010 --
1011 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1013 END IF ;
1014 --
1015
1016 --
1017 -- Find budget_group_id and worksheet_name for the worksheet to find
1018 -- workflow roles and to set up context info for the CALLBACK procedure.
1019 --
1020 SELECT budget_group_id ,
1021 budget_group_name ,
1022 name
1023 INTO
1024 l_budget_group_id ,
1025 g_budget_group_name ,
1026 g_worksheet_name
1027 FROM psb_worksheets_v
1028 WHERE worksheet_id = l_worksheets_tab(i) ;
1029
1030 g_worksheet_id := l_worksheets_tab(i) ;
1031
1032 --
1033 -- Send notifications to the budget group roles.
1034 --
1035 FOR l_role_rec IN
1036 (
1037 SELECT wf_role_name
1038 FROM psb_budget_groups bg ,
1039 psb_budget_group_resp resp
1040 WHERE resp.responsibility_type = 'N'
1041 AND bg.budget_group_id = l_budget_group_id
1042 AND bg.budget_group_id = resp.budget_group_id
1043 )
1044 LOOP
1045 --
1046 l_notification_id :=
1047 WF_Notification.SendGroup
1048 ( role => l_role_rec.wf_role_name ,
1049 msg_type => 'PSBWS' ,
1050 msg_name => 'NOTIFY_OF_FREEZE_COMPLETION' ,
1051 context => itemtype ||':'|| itemkey ||':'|| actid ,
1052 callback => 'PSB_Submit_Worksheet_PVT.Callback'
1053 ) ;
1054 END LOOP ;
1055 --
1056 END IF ;
1057 --
1058 END LOOP ;
1059
1060 result := 'COMPLETE' ;
1061
1062 END IF ;
1063
1064 IF ( funcmode = 'CANCEL' ) THEN
1065 --
1066 result := 'COMPLETE' ;
1067 --
1068 END IF;
1069
1070 --
1071 -- In future implementations, appropriate code is to be inserted here.
1072 --
1073 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1074 --
1075 result := '' ;
1076 --
1077 END IF;
1078
1079 EXCEPTION
1080 --
1081 WHEN OTHERS THEN
1082 wf_core.context('PSBWS', 'Freeze_Worksheets',
1083 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1084 RAISE ;
1085
1086 END Freeze_Worksheets ;
1087 /*---------------------------------------------------------------------------*/
1088
1089
1090 /*===========================================================================+
1091 | PROCEDURE Update_View_Line_Flag |
1092 +===========================================================================*/
1093 --
1094 -- API updates view_line flag for all parent worksheets of the submittted
1095 -- worksheet as per the service package selection.
1096 --
1097 PROCEDURE Update_View_Line_Flag
1098 (
1099 itemtype IN VARCHAR2,
1100 itemkey IN VARCHAR2,
1101 actid IN NUMBER,
1102 funcmode IN VARCHAR2,
1103 result OUT NOCOPY VARCHAR2
1104 )
1105 IS
1106 --
1107 l_return_status VARCHAR2(1) ;
1108 l_msg_count NUMBER ;
1109 l_msg_data VARCHAR2(2000) ;
1110 --
1111 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1112 l_budget_by_position psb_worksheets.budget_by_position%TYPE ;
1113 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
1114 l_operation_id NUMBER ;
1115 l_service_package_count NUMBER ;
1116 --
1117 BEGIN
1118 --
1119 IF ( funcmode = 'RUN' ) THEN
1120 --
1121 -- Get worksheet_id item_attribute.
1122 --
1123 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1124 itemkey => itemkey,
1125 aname => 'WORKSHEET_ID');
1126
1127 l_operation_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'OPERATION_ID');
1130
1131 -- Check if this is a position worksheet.
1132 SELECT NVL( budget_by_position, 'N') INTO l_budget_by_position
1133 FROM psb_worksheets
1134 WHERE worksheet_id = l_worksheet_id ;
1135
1136 -- Note when a user starts submission process without clickin on service
1137 -- package button, this means all service packages are being selected
1138 -- even though psb_ws_submit_service_packages will have no records in it.
1139 SELECT COUNT(*) INTO l_service_package_count
1140 FROM dual
1141 WHERE EXISTS
1142 ( SELECT 1
1143 FROM psb_ws_submit_service_packages
1144 WHERE worksheet_id = l_worksheet_id
1145 AND operation_id = l_operation_id ) ;
1146
1147 -- Get all parent worksheets for the selected worksheet.
1148 PSB_WS_Ops_Pvt.Find_Parent_Worksheets
1149 (
1150 p_api_version => 1.0 ,
1151 p_init_msg_list => FND_API.G_FALSE,
1152 p_commit => FND_API.G_FALSE,
1153 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1154 p_return_status => l_return_status,
1155 p_msg_count => l_msg_count,
1156 p_msg_data => l_msg_data,
1157 --
1158 p_worksheet_id => l_worksheet_id,
1159 p_worksheet_tbl => l_worksheets_tab
1160 );
1161 --
1162 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1164 END IF ;
1165
1166 --
1167 -- Update view_line_flag for all parent worksheets.
1168 --
1169 FOR i IN 1..l_worksheets_tab.COUNT
1170 LOOP
1171
1172 -- Bug#3124025: Update view_line_flag for the current parent worksheet in
1173 -- line matrix table as per service package selection. Note using COUNT
1174 -- is fine as it will always return either 0 or 1.
1175 UPDATE psb_ws_lines lines
1176 SET lines.view_line_flag =
1177 ( SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
1178 FROM psb_ws_account_lines accts
1179 WHERE accts.account_line_id = lines.account_line_id
1180 AND ( l_service_package_count = 0
1181 OR
1182 accts.service_package_id IN
1183 (
1184 SELECT ssp.service_package_id
1185 FROM psb_ws_submit_service_packages ssp
1186 WHERE ssp.worksheet_id = l_worksheet_id
1187 AND ssp.operation_id = l_operation_id
1188 )
1189 )
1190 )
1191 WHERE lines.worksheet_id = l_worksheets_tab(i)
1192 AND EXISTS
1193 ( SELECT 1
1194 FROM psb_ws_lines pwl
1195 WHERE pwl.account_line_id = lines.account_line_id
1196 AND pwl.worksheet_id = l_worksheet_id
1197 ) ;
1198
1199 /*
1200 -- Bug#3124025: Commenting and now using prior query.
1201 UPDATE psb_ws_lines lines
1202 SET lines.view_line_flag = 'N'
1203 WHERE lines.worksheet_id = l_worksheets_tab(i)
1204 AND EXISTS
1205 (
1206 SELECT accts.account_line_id
1207 FROM psb_ws_account_lines accts
1208 WHERE accts.account_line_id = lines.account_line_id
1209 AND accts.service_package_id NOT IN
1210 (
1211 SELECT service_package_id
1212 FROM psb_ws_submit_service_packages
1213 WHERE worksheet_id = l_worksheet_id
1214 AND operation_id = l_operation_id
1215 )
1216 ) ;
1217 */
1218
1219 -- Bug#3124025: Update view_line_flag for the current parent worksheet in
1220 -- position matrix table as per service package selection.
1221 IF l_budget_by_position = 'Y' THEN
1222
1223 --
1224 -- Bug#3124025: The positions are always associated with BASE service
1225 -- package. Now when a worksheet is submitted, the BASE is always
1226 -- selected. This means psb_ws_lines_positions.view_line_flag has got
1227 -- to be always "Y". Using this defensive (fixing) query. We may comment
1228 -- out this query later on.
1229 --
1230 UPDATE psb_ws_lines_positions lines
1231 SET lines.view_line_flag = 'Y'
1232 WHERE lines.worksheet_id = l_worksheets_tab(i)
1233 AND ( lines.view_line_flag IS NULL OR lines.view_line_flag = 'N' )
1234 AND EXISTS
1235 ( SELECT 1
1236 FROM psb_ws_lines_positions pwl
1237 WHERE pwl.position_line_id = lines.position_line_id
1238 AND pwl.worksheet_id = l_worksheet_id
1239 ) ;
1240
1241 /*
1242 -- Bug#3124025: Commenting the potential new query and using prior query.
1243 UPDATE psb_ws_lines_positions lines
1244 SET lines.view_line_flag =
1245 ( DECODE ( ( SELECT COUNT(*)
1246 FROM psb_ws_account_lines accts
1247 WHERE accts.position_line_id = lines.position_line_id
1248 AND ( l_service_package_count = 0
1249 OR
1250 accts.service_package_id IN
1251 (
1252 SELECT ssp.service_package_id
1253 FROM psb_ws_submit_service_packages ssp
1254 WHERE ssp.worksheet_id = l_worksheet_id
1255 AND ssp.operation_id = l_operation_id
1256 )
1257 )
1258 ),
1259 0, 'N', 'Y'
1260 )
1261 )
1262 WHERE lines.worksheet_id = l_worksheets_tab(i)
1263 AND EXISTS
1264 ( SELECT 1
1265 FROM psb_ws_lines_positions pwl
1266 WHERE pwl.position_line_id = lines.position_line_id
1267 AND pwl.worksheet_id = l_worksheet_id
1268 ) ;
1269
1270 -- Bug#3124025: Commenting the original query and now using prior query.
1271 UPDATE psb_ws_lines_positions lines
1272 SET view_line_flag = 'N'
1273 WHERE lines.worksheet_id = l_worksheets_tab(i)
1274 AND lines.position_line_id IN
1275 (
1276 SELECT accts.position_line_id
1277 FROM psb_ws_lines lines ,
1278 psb_ws_account_lines accts
1279 WHERE lines.worksheet_id = l_worksheets_tab(i)
1280 AND lines.account_line_id = accts.account_line_id
1281 AND accts.service_package_id NOT IN
1282 (
1283 SELECT ssp.service_package_id
1284 FROM psb_ws_submit_service_packages ssp
1285 WHERE ssp.worksheet_id = l_worksheet_id
1286 AND ssp.operation_id = l_operation_id
1287 )
1288 ) ;
1289 */
1290 --
1291 END IF ;
1292 -- End updating view_line_flag for the current parent worksheet in
1293 -- position matrix table as per service package selection.
1294
1295 END LOOP ;
1296 -- End updating view_line_flag for all parent worksheets.
1297
1298 result := 'COMPLETE:YES' ;
1299
1300 END IF ;
1301
1302 IF ( funcmode = 'CANCEL' ) THEN
1303 result := 'COMPLETE' ;
1304 END IF;
1305
1306 --
1307 -- In future implementations, appropriate code is to be inserted here.
1308 --
1309 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1310 result := '' ;
1311 END IF;
1312
1313 EXCEPTION
1314 --
1315 WHEN OTHERS THEN
1316 wf_core.context('PSBWS', 'Update_View_Line_Flag',
1317 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1318 RAISE ;
1319 --
1320 END Update_View_Line_Flag;
1321 /*---------------------------------------------------------------------------*/
1322
1323
1324 /*===========================================================================+
1325 | PROCEDURE Change_Worksheet_Stage |
1326 +===========================================================================*/
1327 --
1328 -- The API changes the stage of a worksheet and all its lower worksheets.
1329 --
1330 PROCEDURE Change_Worksheet_Stage
1331 (
1332 itemtype IN VARCHAR2,
1333 itemkey IN VARCHAR2,
1334 actid IN NUMBER,
1335 funcmode IN VARCHAR2,
1336 result OUT NOCOPY VARCHAR2
1337 )
1338 IS
1339 --
1340 l_return_status VARCHAR2(1) ;
1341 l_msg_count NUMBER ;
1342 l_msg_data VARCHAR2(2000) ;
1343 --
1344 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1345 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
1346 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
1347 --
1348 l_notification_id NUMBER ;
1349 l_stage_set_id psb_worksheets.stage_set_id%TYPE ;
1350 l_target_stage_seq psb_worksheets.current_stage_seq%TYPE ;
1351 l_current_stage_seq psb_worksheets.current_stage_seq%TYPE ;
1352 l_operation_id NUMBER ;
1353 --
1354 BEGIN
1355 --
1356 IF ( funcmode = 'RUN' ) THEN
1357 --
1358 -- Get worksheet_id item_attribute.
1359 --
1360 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1361 itemkey => itemkey,
1362 aname => 'WORKSHEET_ID');
1363
1364 l_operation_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1365 itemkey => itemkey,
1366 aname => 'OPERATION_ID');
1367 --
1368 -- Setting context for the CALLBACK procedure.
1369 --
1370 g_itemtype := itemtype ;
1371 g_itemkey := itemkey ;
1372
1373 --
1374 -- Find next stage_id for the given worksheet l_target_stage_seq.
1375 --
1376 SELECT stage_set_id ,
1377 current_stage_seq
1378 INTO
1379 l_stage_set_id ,
1380 l_current_stage_seq
1381 FROM psb_worksheets
1382 WHERE worksheet_id = l_worksheet_id ;
1383
1384 SELECT MIN (sequence_number) INTO l_target_stage_seq
1385 FROM psb_budget_stages
1386 WHERE budget_stage_set_id = l_stage_set_id
1387 AND sequence_number > l_current_stage_seq
1388 ORDER BY sequence_number ;
1389
1390 --
1391 -- If l_target_stage_seq is NULL means the worksheet is already at
1392 -- its highest stage. Simply return with status 'COMPLETE'.
1393 --
1394 IF l_target_stage_seq IS NULL THEN
1395 result := 'COMPLETE' ;
1396 RETURN ;
1397 END IF ;
1398
1399 --
1400 -- Change the stage of the current worksheet.
1401 --
1402 PSB_WS_Ops_Pvt.Change_Worksheet_Stage
1403 (
1404 p_api_version => 1.0 ,
1405 p_init_msg_list => FND_API.G_FALSE,
1406 p_commit => FND_API.G_FALSE,
1407 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1408 p_return_status => l_return_status,
1409 p_msg_count => l_msg_count,
1410 p_msg_data => l_msg_data,
1411 --
1412 p_worksheet_id => l_worksheet_id ,
1413 p_stage_seq => l_target_stage_seq ,
1414 p_operation_id => l_operation_id
1415 );
1416 --
1417 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1419 END IF ;
1420 --
1421
1422 --
1423 -- Find all the child worksheets as they may also be advanced to
1424 -- next stages depening on their current stage.
1425 --
1426 PSB_WS_Ops_Pvt.Find_Child_Worksheets
1427 (
1428 p_api_version => 1.0 ,
1429 p_init_msg_list => FND_API.G_FALSE,
1430 p_commit => FND_API.G_FALSE,
1431 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1432 p_return_status => l_return_status,
1433 p_msg_count => l_msg_count,
1434 p_msg_data => l_msg_data,
1435 --
1436 p_worksheet_id => l_worksheet_id,
1437 p_worksheet_tbl => l_worksheets_tab
1438 );
1439 --
1440 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1442 END IF ;
1443 --
1444
1445 --
1446 -- Processing all lower level worksheets
1447 --
1448 FOR i IN 1..l_worksheets_tab.COUNT
1449 LOOP
1450 --
1451 -- Find current_stage_seq for the current worksheet.
1452 --
1453 SELECT current_stage_seq INTO l_current_stage_seq
1454 FROM psb_worksheets
1455 WHERE worksheet_id = l_worksheets_tab(i) ;
1456
1457 --
1458 -- Advance the current worksheet to its next stage only when it is
1459 -- at the lower stage.
1460 --
1461 IF l_target_stage_seq > l_current_stage_seq THEN
1462 --
1463 PSB_Worksheet_Pvt.Update_Worksheet
1464 (
1465 p_api_version => 1.0 ,
1466 p_init_msg_list => FND_API.G_FALSE,
1467 p_commit => FND_API.G_FALSE,
1468 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1469 p_return_status => l_return_status,
1470 p_msg_count => l_msg_count,
1471 p_msg_data => l_msg_data,
1472 --
1473 p_worksheet_id => l_worksheets_tab(i) ,
1474 p_current_stage_seq => l_target_stage_seq
1475 ) ;
1476 --
1477 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1479 END IF ;
1480 --
1481
1482 --
1483 -- Find budget_group_id and worksheet_name for the worksheet to find
1484 -- workflow roles and to set up context info for the CALLBACK procedure.
1485 --
1486 SELECT budget_group_id ,
1487 name
1488 INTO
1489 l_budget_group_id ,
1490 g_worksheet_name
1491 FROM psb_worksheets
1492 WHERE worksheet_id = l_worksheets_tab(i) ;
1493
1494 --
1495 -- Send 'Worksheet Stage Moved' related notifications to the budget
1496 -- group users.
1497 --
1498 FOR l_role_rec IN
1499 (
1500 SELECT wf_role_name
1501 FROM psb_budget_groups bg ,
1502 psb_budget_group_resp resp
1503 WHERE resp.responsibility_type = 'N'
1504 AND bg.budget_group_id = l_budget_group_id
1505 AND bg.budget_group_id = resp.budget_group_id
1506 )
1507 LOOP
1508 --
1509 l_notification_id :=
1510 WF_Notification.SendGroup
1511 ( role => l_role_rec.wf_role_name ,
1512 msg_type => 'PSBWS' ,
1513 msg_name => 'NOTIFY_OF_WS_MOVE_COMPLETION' ,
1514 context => itemtype ||':'|| itemkey ||':'|| actid ,
1515 callback => 'PSB_Submit_Worksheet_PVT.Callback'
1516 ) ;
1517 END LOOP ;
1518 --
1519 END IF ;
1520
1521 END LOOP ;
1522
1523 result := 'COMPLETE' ;
1524
1525 END IF ;
1526
1527 --
1528 -- In future implementations, appropriate code is to be inserted here.
1529 --
1530 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1531 --
1532 result := '' ;
1533 --
1534 END IF;
1535
1536 EXCEPTION
1537 --
1538 WHEN OTHERS THEN
1539 wf_core.context('PSBWS', 'Change_Worksheet_Stage',
1540 PSB_Message_S.Get_Error_Stack(l_msg_count) );
1541 RAISE ;
1542
1543 END Change_Worksheet_Stage ;
1544 /*---------------------------------------------------------------------------*/
1545
1546
1547 /*===========================================================================+
1548 | PROCEDURE Perform_Review_Group_Approval |
1549 +===========================================================================*/
1550 --
1551 -- The API checks whether review group approval is needed or not.
1552 --
1553 PROCEDURE Perform_Review_Group_Approval
1554 (
1555 itemtype IN VARCHAR2,
1556 itemkey IN VARCHAR2,
1557 actid IN NUMBER,
1558 funcmode IN VARCHAR2,
1559 result OUT NOCOPY VARCHAR2
1560 )
1561 IS
1562 --
1563 l_review_group_flag VARCHAR2(1) ;
1564 --
1565 BEGIN
1566 --
1567 IF ( funcmode = 'RUN' ) THEN
1568 --
1569 -- Get value of l_review_group_flag attribute to find whether Review Group
1570 -- approval is needed or not.
1571 --
1572 l_review_group_flag := wf_engine.GetItemAttrText
1573 ( itemtype => itemtype,
1574 itemkey => itemkey,
1575 aname => 'REVIEW_GROUP_FLAG') ;
1576 --
1577 IF NVL( l_review_group_flag, 'N') = 'N' THEN
1578 result := 'COMPLETE:NO' ;
1579 ELSE
1580 result := 'COMPLETE:YES' ;
1581 END IF ;
1582 --
1583
1584 /* For testing */
1585 -- result := 'COMPLETE:NO' ;
1586 -- result := 'COMPLETE:YES' ;
1587
1588 END IF ;
1589
1590 IF ( funcmode = 'CANCEL' ) THEN
1591 --
1592 result := 'COMPLETE' ;
1593 --
1594 END IF;
1595
1596 --
1597 -- In future implementations, appropriate code is to be inserted here.
1598 --
1599 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1600 --
1601 result := '' ;
1602 --
1603 END IF;
1604
1605 EXCEPTION
1606 --
1607 WHEN OTHERS THEN
1608 wf_core.context('PSBWS', 'Perform_Review_Group_Approval',
1609 itemtype, itemkey, to_char(actid), funcmode);
1610 RAISE ;
1611
1612 END Perform_Review_Group_Approval ;
1613 /*---------------------------------------------------------------------------*/
1614
1615
1616 /*===========================================================================+
1617 | PROCEDURE Set_Loop_Limit |
1618 +===========================================================================*/
1619 --
1620 -- The API sets 'Loop Limit' attribute for the special 'Loop Counter' activity.
1621 --
1622 PROCEDURE Set_Loop_Limit
1623 (
1624 itemtype IN VARCHAR2,
1625 itemkey IN VARCHAR2,
1626 actid IN NUMBER,
1627 funcmode IN VARCHAR2,
1628 result OUT NOCOPY VARCHAR2
1629 )
1630 IS
1631 --
1632 l_return_status VARCHAR2(1) ;
1633 --
1634 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1635 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
1636 l_budget_calendar_id psb_worksheets.budget_calendar_id%TYPE ;
1637 l_root_budget_group_id psb_budget_groups.root_budget_group_id%TYPE ;
1638 l_count NUMBER ;
1639 --
1640 BEGIN
1641 --
1642 IF ( funcmode = 'RUN' ) THEN
1643 --
1644 -- Get worksheet_id item_attribute.
1645 --
1646 l_worksheet_id := wf_engine.GetItemAttrNumber
1647 ( itemtype => itemtype,
1648 itemkey => itemkey,
1649 aname => 'WORKSHEET_ID'
1650 );
1651
1652 --
1653 -- Get budget_group, root budget group and calendar indo for the worksheet.
1654 --
1655 SELECT ws.budget_group_id ,
1656 ws.budget_calendar_id ,
1657 bg.root_budget_group_id
1658 INTO
1659 l_budget_group_id ,
1660 l_budget_calendar_id ,
1661 l_root_budget_group_id
1662 FROM psb_worksheets ws ,
1663 psb_budget_groups bg
1664 WHERE worksheet_id = l_worksheet_id
1665 AND ws.budget_group_id = bg.budget_group_id ;
1666
1667
1668 --
1669 -- Get budget calendar related info to find whether the review groups is
1670 -- active in the current budget group hierarchy or not.
1671 --
1672 IF NVL(PSB_WS_Acct1.g_budget_calendar_id, -99) <> l_budget_calendar_id
1673 THEN
1674 --
1675 PSB_WS_Acct1.Cache_Budget_Calendar
1676 (
1677 p_return_status => l_return_status ,
1678 p_budget_calendar_id => l_budget_calendar_id
1679 );
1680 --
1681 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1683 END IF ;
1684 --
1685 END IF ;
1686
1687 --
1688 -- Find review groups for the worksheet and populate psb_wf_review_groups
1689 -- table with this information. Consider whether the review_group is
1690 -- active in the current budget group hierarchy. If not, do not select
1691 -- such a budget workflow rule.
1692 --
1693 INSERT INTO psb_wf_review_groups
1694 ( item_key, budget_workflow_rule_id, sequence )
1695 SELECT itemkey, rules.budget_workflow_rule_id, ROWNUM
1696 FROM psb_budget_group_categories cats ,
1697 psb_budget_workflow_rules rules ,
1698 psb_budget_groups bg
1699 WHERE cats.budget_group_id = l_budget_group_id
1700 AND rules.budget_group_id = l_root_budget_group_id
1701 AND rules.stage_id = cats.stage_id
1702 AND bg.budget_group_id = rules.review_budget_group_id
1703 AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
1704 AND ( ( bg.effective_end_date IS NULL)
1705 OR
1706 ( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy )
1707 ) ;
1708
1709 --
1710 -- Count total number of review groups to set the Loop Counter Activity.
1711 --
1712 l_count := SQL%ROWCOUNT ;
1713
1714 --
1715 -- Committing as the users on_exit settings may be ROLLBACK.
1716 --
1717 COMMIT ;
1718
1719 /* For testing */
1720 -- l_count := 2 ;
1721
1722 wf_engine.SetItemAttrNumber( ItemType => itemtype,
1723 ItemKey => itemkey,
1724 aname => 'LOOP_SET_COUNTER',
1725 avalue => l_count );
1726 --
1727 result := 'COMPLETE' ;
1728
1729 END IF ;
1730
1731 IF ( funcmode = 'CANCEL' ) THEN
1732 --
1733 result := 'COMPLETE' ;
1734 --
1735 END IF;
1736
1737 --
1738 -- In future implementations, appropriate code is to be inserted here.
1739 --
1740 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
1741 --
1742 result := '' ;
1743 --
1744 END IF;
1745
1746 EXCEPTION
1747 --
1748 WHEN OTHERS THEN
1749 wf_core.context('PSBWS', 'Set_Loop_Limit',
1750 itemtype, itemkey, to_char(actid), funcmode);
1751 RAISE ;
1752
1753 END Set_Loop_Limit ;
1754 /*---------------------------------------------------------------------------*/
1755
1756
1757 /*===========================================================================+
1758 | PROCEDURE Create_Review_Group_Worksheet |
1759 +===========================================================================*/
1760 --
1761 -- The API creates a worksheet for a Review Group.
1762 --
1763 PROCEDURE Create_Review_Group_Worksheet
1764 (
1765 itemtype IN VARCHAR2,
1766 itemkey IN VARCHAR2,
1767 actid IN NUMBER,
1768 funcmode IN VARCHAR2,
1769 result OUT NOCOPY VARCHAR2
1770 )
1771 IS
1772 --
1773 l_return_status VARCHAR2(1) ;
1774 l_msg_count NUMBER ;
1775 l_msg_data VARCHAR2(2000) ;
1776 --
1777 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1778 l_new_worksheet_id psb_worksheets.worksheet_id%TYPE ;
1779 l_new_worksheet_name psb_worksheets.name%TYPE ;
1780 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
1781 l_budget_workflow_rule_id NUMBER ;
1782 l_account_or_position_type VARCHAR2(1) ;
1783 l_approval_option VARCHAR2(1) ;
1784 l_review_budget_group_id NUMBER ;
1785 l_review_budget_group_name psb_budget_workflow_rules.name%TYPE ;
1786 l_set_tbl PSB_WS_Ops_Pvt.account_position_set_tbl_type ;
1787 l_wf_role_name psb_budget_group_resp.wf_role_name%TYPE ;
1788 --For Bug#4475288: Modifying width of l_review_group_approver_name from VARCHAR2(80)
1789 l_review_group_approver_name psb_budget_group_resp.wf_role_name%TYPE ;
1790 l_operation_id NUMBER ;
1791 --
1792 l_count NUMBER ;
1793 l_loop_visited_counter NUMBER ;
1794 --
1795 BEGIN
1796 --
1797
1798 IF ( funcmode = 'RUN' ) THEN
1799
1800
1801 /* ******
1802 -- Start testing.
1803 l_review_group_approver_name := 'PSBTEST' ;
1804
1805 l_loop_visited_counter := wf_engine.GetItemAttrNumber
1806 ( itemtype => itemtype,
1807 itemkey => itemkey,
1808 aname => 'LOOP_VISITED_COUNTER'
1809 );
1810
1811 l_loop_visited_counter := l_loop_visited_counter + 1 ;
1812
1813 wf_engine.SetItemAttrNumber( ItemType => ItemType,
1814 ItemKey => ItemKey,
1815 aname => 'LOOP_VISITED_COUNTER',
1816 avalue => l_loop_visited_counter );
1817
1818 IF l_loop_visited_counter = 1 THEN
1819
1820 wf_engine.SetItemAttrText( ItemType => ItemType,
1821 ItemKey => ItemKey,
1822 aname => 'APPROVAL_OPTION',
1823 avalue => 'N' );
1824 ELSE
1825
1826 wf_engine.SetItemAttrText( ItemType => ItemType,
1827 ItemKey => ItemKey,
1828 aname => 'APPROVAL_OPTION',
1829 avalue => 'Y' );
1830 END IF ;
1831
1832 -- Set approver_name item attribute.
1833 wf_engine.SetItemAttrText( ItemType => itemtype,
1834 ItemKey => itemkey,
1835 aname => 'REVIEW_GROUP_APPROVER_NAME',
1836 avalue => l_review_group_approver_name );
1837
1838 --
1839 result := 'COMPLETE' ;
1840 return ;
1841
1842 -- End testing.
1843 ***** */
1844
1845 --
1846 -- Get item related attribute values.
1847 --
1848
1849 l_worksheet_id := wf_engine.GetItemAttrNumber
1850 ( itemtype => itemtype,
1851 itemkey => itemkey,
1852 aname => 'WORKSHEET_ID'
1853 );
1854 --
1855 l_operation_id := wf_engine.GetItemAttrNumber
1856 ( itemtype => itemtype,
1857 itemkey => itemkey,
1858 aname => 'OPERATION_ID'
1859 );
1860 --
1861 l_loop_visited_counter := wf_engine.GetItemAttrNumber
1862 ( itemtype => itemtype,
1863 itemkey => itemkey,
1864 aname => 'LOOP_VISITED_COUNTER'
1865 );
1866
1867 --
1868 -- Get budget_group_id for the current worksheet.
1869 --
1870 SELECT budget_group_id INTO l_budget_group_id
1871 FROM psb_worksheets
1872 WHERE worksheet_id = l_worksheet_id ;
1873
1874 l_loop_visited_counter := l_loop_visited_counter + 1 ;
1875
1876 --
1877 -- Get the current review group to be processed.
1878 --
1879 SELECT wrg.budget_workflow_rule_id ,
1880 rules.approval_option ,
1881 rules.review_budget_group_id
1882 INTO
1883 l_budget_workflow_rule_id ,
1884 l_approval_option ,
1885 l_review_budget_group_id
1886 FROM psb_wf_review_groups wrg ,
1887 psb_budget_workflow_rules rules
1888 WHERE item_key = itemkey
1889 AND sequence = l_loop_visited_counter
1890 AND rules.budget_workflow_rule_id = wrg.budget_workflow_rule_id ;
1891
1892 --
1893 -- Get the current review group name to update 'REVIEW_GROUP_NAME' attribute.
1894 --
1895 SELECT name INTO l_review_budget_group_name
1896 FROM psb_budget_groups
1897 WHERE budget_group_id = l_review_budget_group_id ;
1898
1899 --
1900 -- Update 'LOOP_VISITED_COUNTER', 'APPROVAL_OPTION' and
1901 -- 'REVIEW_GROUP_NAME' item attributes.
1902 --
1903
1904 wf_engine.SetItemAttrNumber( ItemType => ItemType,
1905 ItemKey => ItemKey,
1906 aname => 'LOOP_VISITED_COUNTER',
1907 avalue => l_loop_visited_counter );
1908
1909 wf_engine.SetItemAttrText( ItemType => ItemType,
1910 ItemKey => ItemKey,
1911 aname => 'APPROVAL_OPTION',
1912 avalue => l_approval_option );
1913
1914 wf_engine.SetItemAttrText( ItemType => ItemType,
1915 ItemKey => ItemKey,
1916 aname => 'REVIEW_GROUP_NAME',
1917 avalue => l_review_budget_group_name );
1918
1919 --
1920 -- For the current review group, we have to create a new worksheet for each
1921 -- review group rules. For rule type 'A' (Account) or 'P' (Position), we need
1922 -- to find the sets associated with the rule and call Create_Worksheet API.
1923 -- IF the review group rule is for 'New Positions' and call the appropriate
1924 -- Create_New_Position_Worksheet API.
1925 --
1926
1927 -- Check whether the budget workflow rule is for 'New Positions'.
1928 SELECT account_or_position_type INTO l_account_or_position_type
1929 FROM psb_budget_workflow_rules
1930 WHERE budget_workflow_rule_id = l_budget_workflow_rule_id ;
1931
1932 IF l_account_or_position_type IN ( 'A', 'P') THEN
1933
1934 l_count := 0 ;
1935 l_set_tbl.DELETE ;
1936
1937 FOR l_set_rec IN
1938 (
1939 SELECT account_position_set_id,
1940 account_or_position_type
1941 FROM psb_budget_workflow_rules rules ,
1942 psb_set_relations relations
1943 WHERE rules.budget_workflow_rule_id = l_budget_workflow_rule_id
1944 AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
1945 )
1946 LOOP
1947 l_count := l_count + 1;
1948 l_set_tbl(l_count).account_position_set_id
1949 := l_set_rec.account_position_set_id ;
1950 l_set_tbl(l_count).account_or_position_type
1951 := l_set_rec.account_or_position_type ;
1952 END LOOP;
1953
1954 --
1955 -- Create a new worksheet for the current review group.
1956 -- ( Rule type 'Account' or 'Positions' )
1957 --
1958 PSB_WS_Ops_Pvt.Create_Worksheet
1959 (
1960 p_api_version => 1.0 ,
1961 p_init_msg_list => FND_API.G_TRUE ,
1962 p_commit => FND_API.G_FALSE ,
1963 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1964 p_return_status => l_return_status ,
1965 p_msg_count => l_msg_count ,
1966 p_msg_data => l_msg_data ,
1967 --
1968 p_worksheet_id => l_worksheet_id ,
1969 p_budget_group_id => l_review_budget_group_id ,
1970 p_account_position_set_tbl => l_set_tbl ,
1971 p_service_package_operation_id => l_operation_id ,
1972 p_worksheet_id_OUT => l_new_worksheet_id
1973 );
1974
1975 ELSIF l_account_or_position_type = 'N' THEN
1976
1977 --
1978 -- Create a new worksheet for the current review group.
1979 -- ( Rule type 'New Positions' )
1980 --
1981 PSB_WS_Ops_Pvt.Create_New_Position_Worksheet
1982 (
1983 p_api_version => 1.0 ,
1984 p_init_msg_list => FND_API.G_TRUE ,
1985 p_commit => FND_API.G_FALSE ,
1986 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1987 p_return_status => l_return_status ,
1988 p_msg_count => l_msg_count ,
1989 p_msg_data => l_msg_data ,
1990 --
1991 p_worksheet_id => l_worksheet_id ,
1992 p_budget_group_id => l_review_budget_group_id ,
1993 p_service_package_operation_id => l_operation_id ,
1994 p_worksheet_id_OUT => l_new_worksheet_id
1995 );
1996 --
1997
1998 END IF;
1999
2000 --
2001 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2003 END IF ;
2004 --
2005
2006 --
2007 -- Get new worksheet related information.
2008 --
2009 IF l_new_worksheet_id <> 0 THEN
2010
2011 -- Find new worksheet name.
2012 SELECT name INTO l_new_worksheet_name
2013 FROM psb_worksheets
2014 WHERE worksheet_id = l_new_worksheet_id ;
2015
2016 -- Find approvers for the current review group.
2017 SELECT wf_role_name INTO l_review_group_approver_name
2018 FROM psb_budget_groups bg ,
2019 psb_budget_group_resp resp
2020 WHERE resp.responsibility_type = 'N'
2021 AND bg.budget_group_id = l_review_budget_group_id
2022 AND bg.budget_group_id = resp.budget_group_id
2023 AND ROWNUM < 2 ;
2024
2025 ELSE
2026
2027 l_new_worksheet_name := NULL ;
2028 l_review_group_approver_name := NULL ;
2029
2030 END IF ;
2031
2032 --
2033 -- Set item attributes related to the new worksheet.
2034 --
2035
2036 wf_engine.SetItemAttrNumber( ItemType => itemtype,
2037 ItemKey => itemkey,
2038 aname => 'NEW_WORKSHEET_ID' ,
2039 avalue => l_new_worksheet_id );
2040
2041 wf_engine.SetItemAttrText( ItemType => itemtype,
2042 ItemKey => itemkey,
2043 aname => 'NEW_WORKSHEET_NAME' ,
2044 avalue => l_new_worksheet_name );
2045
2046 wf_engine.SetItemAttrText( ItemType => itemtype,
2047 ItemKey => itemkey,
2048 aname => 'REVIEW_GROUP_APPROVER_NAME',
2049 avalue => l_review_group_approver_name );
2050 --
2051 result := 'COMPLETE' ;
2052
2053 END IF ;
2054
2055 IF ( funcmode = 'CANCEL' ) THEN
2056 --
2057 result := 'COMPLETE' ;
2058 --
2059 END IF;
2060
2061 --
2062 -- In future implementations, appropriate code is to be inserted here.
2063 --
2064 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2065 --
2066 result := '' ;
2067 --
2068 END IF;
2069
2070 EXCEPTION
2071 --
2072 WHEN OTHERS THEN
2073 wf_core.context('PSBWS', 'Create_Review_Group_Worksheet',
2074 PSB_Message_S.Get_Error_Stack(l_msg_count) );
2075 RAISE ;
2076
2077 END Create_Review_Group_Worksheet ;
2078 /*---------------------------------------------------------------------------*/
2079
2080
2081 /*===========================================================================+
2082 | PROCEDURE New_Worksheet_Created |
2083 +===========================================================================*/
2084 --
2085 -- The API checks whether the new worksheet for the review group was created
2086 -- or not. A worksheet may have not been created if it did not have relevant
2087 -- account/position sets (from which the new worksheet gets created).
2088 --
2089 PROCEDURE New_Worksheet_Created
2090 (
2091 itemtype IN VARCHAR2,
2092 itemkey IN VARCHAR2,
2093 actid IN NUMBER,
2094 funcmode IN VARCHAR2,
2095 result OUT NOCOPY VARCHAR2
2096 )
2097 IS
2098 --
2099 l_new_worksheet_id psb_worksheets.worksheet_id%TYPE ;
2100 --
2101 BEGIN
2102 --
2103 IF ( funcmode = 'RUN' ) THEN
2104 --
2105 -- Get value of 'NEW_WORKSHEET_ID' attribute to find whether a new
2106 -- worksheet was created or not.
2107 --
2108 l_new_worksheet_id := wf_engine.GetItemAttrNumber
2109 ( itemtype => itemtype,
2110 itemkey => itemkey,
2111 aname => 'NEW_WORKSHEET_ID'
2112 ) ;
2113 --
2114 IF NVL( l_new_worksheet_id, 0 ) = 0 THEN
2115 result := 'COMPLETE:NO' ;
2116 ELSE
2117 result := 'COMPLETE:YES' ;
2118 END IF ;
2119 --
2120
2121 END IF ;
2122
2123 IF ( funcmode = 'CANCEL' ) THEN
2124 --
2125 result := 'COMPLETE' ;
2126 --
2127 END IF;
2128
2129 --
2130 -- In future implementations, appropriate code is to be inserted here.
2131 --
2132 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2133 --
2134 result := '' ;
2135 --
2136 END IF;
2137
2138 EXCEPTION
2139 --
2140 WHEN OTHERS THEN
2141 wf_core.context('PSBWS', 'New_Worksheet_Created',
2142 itemtype, itemkey, to_char(actid), funcmode);
2143 RAISE ;
2144
2145 END New_Worksheet_Created;
2146 /*---------------------------------------------------------------------------*/
2147
2148
2149 /*===========================================================================+
2150 | PROCEDURE Find_Approval_Option |
2151 +===========================================================================*/
2152 --
2153 -- The API finds the approval option for a worksheet. The approval option
2154 -- tells you whether the approval is required for a worksheet or it is just
2155 -- for review group approvers' information.
2156 --
2157 PROCEDURE Find_Approval_Option
2158 (
2159 itemtype IN VARCHAR2,
2160 itemkey IN VARCHAR2,
2161 actid IN NUMBER,
2162 funcmode IN VARCHAR2,
2163 result OUT NOCOPY VARCHAR2
2164 )
2165 IS
2166 --
2167 l_approval_option psb_budget_workflow_rules.approval_option%TYPE ;
2168 --
2169 BEGIN
2170 --
2171 IF ( funcmode = 'RUN' ) THEN
2172 --
2173 -- Get value of 'APPROVAL_OPTION' attribute to find whether Review Group
2174 -- approval is needed or not.
2175 --
2176 l_approval_option := wf_engine.GetItemAttrText
2177 ( itemtype => itemtype,
2178 itemkey => itemkey,
2179 aname => 'APPROVAL_OPTION') ;
2180 --
2181 IF NVL( l_approval_option, 'N') = 'N' THEN
2182 result := 'COMPLETE:NO' ;
2183 ELSE
2184 result := 'COMPLETE:YES' ;
2185 END IF ;
2186 --
2187
2188 /* For testing */
2189 --result := 'COMPLETE:NO' ;
2190 --result := 'COMPLETE:YES' ;
2191
2192 END IF ;
2193
2194 IF ( funcmode = 'CANCEL' ) THEN
2195 --
2196 result := 'COMPLETE' ;
2197
2198 --
2199 END IF;
2200
2201 --
2202 -- In future implementations, appropriate code is to be inserted here.
2203 --
2204 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2205 --
2206 result := '' ;
2207 --
2208 END IF;
2209
2210 EXCEPTION
2211 --
2212 WHEN OTHERS THEN
2213 wf_core.context('PSBWS', 'Find_Approval_Option',
2214 itemtype, itemkey, to_char(actid), funcmode);
2215 RAISE ;
2216
2217 END Find_Approval_Option ;
2218 /*---------------------------------------------------------------------------*/
2219
2220
2221 /*===========================================================================+
2222 | PROCEDURE Set_Reviewed_Flag |
2223 +===========================================================================*/
2224 --
2225 -- The API sets the attribute 'REVIWED_FLAG' to 'Y' as the review group has
2226 -- reviewed the worksheet by now.
2227 --
2228 PROCEDURE Set_Reviewed_Flag
2229 (
2230 itemtype IN VARCHAR2,
2231 itemkey IN VARCHAR2,
2232 actid IN NUMBER,
2233 funcmode IN VARCHAR2,
2234 result OUT NOCOPY VARCHAR2
2235 )
2236 IS
2237 BEGIN
2238
2239 IF ( funcmode = 'RUN' ) THEN
2240
2241 wf_engine.SetItemAttrText( ItemType => itemtype,
2242 ItemKey => itemkey,
2243 aname => 'REVIEWED_FLAG',
2244 avalue => 'Y' );
2245
2246 result := 'COMPLETE' ;
2247
2248 END IF ;
2249
2250 IF ( funcmode = 'CANCEL' ) THEN
2251 --
2252 result := 'COMPLETE' ;
2253 --
2254 END IF;
2255
2256 --
2257 -- In future implementations, appropriate code is to be inserted here.
2258 --
2259 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2260 --
2261 result := '' ;
2262 --
2263 END IF;
2264
2265 EXCEPTION
2266 --
2267 WHEN OTHERS THEN
2268 wf_core.context('PSBWS', 'Set_Reviewed_Flag',
2269 itemtype, itemkey, to_char(actid), funcmode);
2270 RAISE ;
2271
2272 END Set_Reviewed_Flag ;
2273 /*---------------------------------------------------------------------------*/
2274
2275
2276 /*===========================================================================+
2277 | PROCEDURE Send_Approval_Notification |
2278 +===========================================================================*/
2279 --
2280 -- The activity finds out whether 'Worksheet Approved' related notification
2281 -- will be sent to the submitter or not.
2282 --
2283 PROCEDURE Send_Approval_Notification
2284 (
2285 itemtype IN VARCHAR2,
2286 itemkey IN VARCHAR2,
2287 actid IN NUMBER,
2288 funcmode IN VARCHAR2,
2289 result OUT NOCOPY VARCHAR2
2290 )
2291 IS
2292 --
2293 l_reviewed_flag VARCHAR2(1) ;
2294 --
2295 BEGIN
2296
2297 IF ( funcmode = 'RUN' ) THEN
2298 --
2299 -- Get reviewed_flag item_attribute.
2300 --
2301 l_reviewed_flag := wf_engine.GetItemAttrText
2302 ( itemtype => itemtype,
2303 itemkey => itemkey,
2304 aname => 'REVIEWED_FLAG' );
2305
2306 --
2307 IF NVL( l_reviewed_flag, 'N') = 'N' THEN
2308 result := 'COMPLETE:NO' ;
2309 ELSE
2310 result := 'COMPLETE:YES' ;
2311 END IF ;
2312 --
2313
2314 END IF ;
2315
2316 IF ( funcmode = 'CANCEL' ) THEN
2317 result := 'COMPLETE' ;
2318 END IF;
2319
2320 --
2321 -- In future implementations, appropriate code is to be inserted here.
2322 --
2323 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2324 --
2325 result := '' ;
2326 --
2327 END IF;
2328
2329 EXCEPTION
2330 --
2331 WHEN OTHERS THEN
2332 wf_core.context('PSBWS', 'Send_Approval_Notification',
2333 itemtype, itemkey, to_char(actid), funcmode);
2334 RAISE ;
2335
2336 END Send_Approval_Notification ;
2337 /*---------------------------------------------------------------------------*/
2338
2339
2340 /*===========================================================================+
2341 | PROCEDURE Update_Worksheets_Status |
2342 +===========================================================================*/
2343 --
2344 -- The API updates submission related information in the submitted worksheet
2345 -- and all its lower worksheets.
2346 --
2347 PROCEDURE Update_Worksheets_Status
2348 (
2349 itemtype IN VARCHAR2,
2350 itemkey IN VARCHAR2,
2351 actid IN NUMBER,
2352 funcmode IN VARCHAR2,
2353 result OUT NOCOPY VARCHAR2
2354 )
2355 IS
2356 --
2357 l_return_status VARCHAR2(1) ;
2358 l_msg_count NUMBER ;
2359 l_msg_data VARCHAR2(2000) ;
2360 --
2361 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
2362 l_worksheet_name psb_worksheets.name%TYPE ;
2363 l_submitter_id NUMBER ;
2364 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
2365 l_worksheets_tab PSB_WS_Ops_Pvt.Worksheet_Tbl_Type ;
2366 --
2367 BEGIN
2368 --
2369 IF ( funcmode = 'RUN' ) THEN
2370 --
2371 -- Get worksheet_id item_attribute.
2372 --
2373 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2374 itemkey => itemkey,
2375 aname => 'WORKSHEET_ID');
2376 --
2377 l_submitter_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2378 itemkey => itemkey,
2379 aname => 'SUBMITTER_ID');
2380
2381 --
2382 -- Call API to find all lower level worksheets.
2383 --
2384 PSB_WS_Ops_Pvt.Find_Child_Worksheets
2385 (
2386 p_api_version => 1.0 ,
2387 p_init_msg_list => FND_API.G_FALSE,
2388 p_commit => FND_API.G_FALSE,
2389 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2390 p_return_status => l_return_status,
2391 p_msg_count => l_msg_count,
2392 p_msg_data => l_msg_data,
2393 --
2394 p_worksheet_id => l_worksheet_id,
2395 p_worksheet_tbl => l_worksheets_tab
2396 );
2397 --
2398 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2400 END IF ;
2401 --
2402
2403 l_worksheets_tab(0) := l_worksheet_id ;
2404
2405 --
2406 -- Processing all lower level worksheets for updation
2407 --
2408 FOR i IN 0..l_worksheets_tab.COUNT-1
2409 LOOP
2410 --
2411 -- Update Distribution related information in psb_worksheets.
2412 --
2413 PSB_Worksheet_Pvt.Update_Worksheet
2414 (
2415 p_api_version => 1.0 ,
2416 p_init_msg_list => FND_API.G_FALSE,
2417 p_commit => FND_API.G_FALSE,
2418 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
2419 p_return_status => l_return_status,
2420 p_msg_count => l_msg_count,
2421 p_msg_data => l_msg_data ,
2422 --
2423 p_worksheet_id => l_worksheet_id ,
2424 p_date_submitted => SYSDATE ,
2425 p_submitted_by => l_submitter_id
2426 );
2427
2428 --
2429 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2431 END IF ;
2432 --
2433 END LOOP ;
2434
2435 result := 'COMPLETE' ;
2436
2437 END IF ;
2438
2439 IF ( funcmode = 'CANCEL' ) THEN
2440 --
2441 result := 'COMPLETE' ;
2442 --
2443 END IF;
2444
2445 --
2446 -- In future implementations, appropriate code is to be inserted here.
2447 --
2448 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2449 --
2450 result := '' ;
2451 --
2452 END IF;
2453
2454 EXCEPTION
2455 --
2456 WHEN OTHERS THEN
2457 wf_core.context('PSBWS', 'Update_Worksheets_Status',
2458 PSB_Message_S.Get_Error_Stack(l_msg_count) );
2459 RAISE ;
2460
2461 END Update_Worksheets_Status ;
2462 /*---------------------------------------------------------------------------*/
2463
2464
2465 /*===========================================================================+
2466 | PROCEDURE Select_Approvers |
2467 +===========================================================================*/
2468 --
2469 -- The API finds Approvers for the worksheet and then sends notifications
2470 -- to them.
2471 --
2472 PROCEDURE Select_Approvers
2473 (
2474 itemtype IN VARCHAR2,
2475 itemkey IN VARCHAR2,
2476 actid IN NUMBER,
2477 funcmode IN VARCHAR2,
2478 result OUT NOCOPY VARCHAR2
2479 )
2480 IS
2481 --
2482 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
2483 l_approver_name VARCHAR2(80) ;
2484 l_parent_budget_group_id psb_worksheets.budget_group_id%TYPE ;
2485 l_notification_group_id NUMBER ;
2486 BEGIN
2487 --
2488 IF ( funcmode = 'RUN' ) THEN
2489 --
2490 -- Get worksheet_id item_attribute.
2491 --
2492 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2493 itemkey => itemkey,
2494 aname => 'WORKSHEET_ID');
2495 --
2496 -- Setting context for the CALLBACK procedure.
2497 --
2498
2499 g_itemtype := itemtype ;
2500 g_itemkey := itemkey ;
2501 g_worksheet_id := l_worksheet_id ;
2502
2503 g_budget_group_name := wf_engine.GetItemAttrText
2504 ( itemtype => itemtype,
2505 itemkey => itemkey,
2506 aname => 'BUDGET_GROUP_NAME'
2507 );
2508
2509 g_worksheet_name := wf_engine.GetItemAttrText
2510 ( itemtype => itemtype,
2511 itemkey => itemkey,
2512 aname => 'WORKSHEET_NAME'
2513 );
2514
2515 --
2516 -- Find the approver role.
2517 --
2518 SELECT bg.parent_budget_group_id
2519 INTO
2520 l_parent_budget_group_id
2521 FROM psb_worksheets ws,
2522 psb_budget_groups bg
2523 WHERE ws.worksheet_id = l_worksheet_id
2524 AND ws.budget_group_id = bg.budget_group_id ;
2525
2526
2527 FOR l_role_rec IN
2528 (
2529 SELECT wf_role_name
2530 FROM psb_budget_groups bg ,
2531 psb_budget_group_resp resp
2532 WHERE bg.budget_group_id = l_parent_budget_group_id
2533 AND resp.responsibility_type = 'N'
2534 AND bg.budget_group_id = resp.budget_group_id
2535 )
2536 LOOP
2537 --
2538 l_notification_group_id :=
2539 WF_Notification.SendGroup
2540 (
2541 role => l_role_rec.wf_role_name ,
2542 msg_type => 'PSBWS' ,
2543 msg_name => 'NOTIFY_APPROVERS_OF_SUBMISSION' ,
2544 context => itemtype ||':'|| itemkey || ':'|| actid ,
2545 callback => 'PSB_Submit_Worksheet_PVT.Callback'
2546 ) ;
2547 END LOOP ;
2548
2549 --
2550 END IF ;
2551
2552 IF ( funcmode = 'CANCEL' ) THEN
2553 --
2554 result := 'COMPLETE' ;
2555 --
2556 END IF;
2557
2558 --
2559 -- In future implementations, appropriate code is to be inserted here.
2560 --
2561 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2562 --
2563 result := '' ;
2564 --
2565 END IF;
2566
2567 EXCEPTION
2568 --
2569 WHEN OTHERS THEN
2570 wf_core.context('PSBWS', 'Select_Approvers',
2571 itemtype, itemkey, to_char(actid), funcmode);
2572 RAISE ;
2573
2574 END Select_Approvers ;
2575 /*---------------------------------------------------------------------------*/
2576
2577
2578 /*===========================================================================+
2579 | PROCEDURE Unfreeze_Worksheets |
2580 +===========================================================================*/
2581 --
2582 -- This API unfreezes the submitted worksheet. Note that the lower worksheets
2583 -- are not unfrozen, even though they were frozen during Freeze operation.
2584 --
2585 PROCEDURE Unfreeze_Worksheets
2586 (
2587 itemtype IN VARCHAR2,
2588 itemkey IN VARCHAR2,
2589 actid IN NUMBER,
2590 funcmode IN VARCHAR2,
2591 result OUT NOCOPY VARCHAR2
2592 )
2593 IS
2594 --
2595 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
2596 l_worksheet_name psb_worksheets.name%TYPE ;
2597 --
2598 l_return_status VARCHAR2(1) ;
2599 l_msg_count NUMBER ;
2600 l_msg_data VARCHAR2(2000) ;
2601 --
2602 l_notification_id NUMBER ;
2603 --
2604 BEGIN
2605 --
2606 IF ( funcmode = 'RUN' ) THEN
2607 --
2608 -- Get worksheet_id item_attribute.
2609 --
2610 l_worksheet_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2611 itemkey => itemkey,
2612 aname => 'WORKSHEET_ID');
2613 --
2614 -- Unfreeze only the current worksheet.
2615 --
2616 PSB_WS_Ops_Pvt.Freeze_Worksheet
2617 (
2618 p_api_version => 1.0 ,
2619 p_init_msg_list => FND_API.G_FALSE,
2620 p_commit => FND_API.G_FALSE,
2621 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2622 p_return_status => l_return_status,
2623 p_msg_count => l_msg_count,
2624 p_msg_data => l_msg_data,
2625 --
2626 p_worksheet_id => l_worksheet_id,
2627 p_freeze_flag => 'N'
2628 );
2629 --
2630 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2632 END IF ;
2633 --
2634
2635 result := 'COMPLETE' ;
2636
2637 END IF ;
2638
2639
2640 IF ( funcmode = 'CANCEL' ) THEN
2641 --
2642 result := 'COMPLETE' ;
2643 --
2644 END IF;
2645
2646 --
2647 -- In future implementations, appropriate code is to be inserted here.
2648 --
2649 IF ( funcmode NOT IN ( 'RUN', 'CANCEL' ) ) THEN
2650 --
2651 result := '' ;
2652 --
2653 END IF;
2654
2655 EXCEPTION
2656 --
2657 WHEN OTHERS THEN
2658 wf_core.context('PSBWS', 'Unfreeze_Worksheets',
2659 PSB_Message_S.Get_Error_Stack(l_msg_count) );
2660 RAISE ;
2661
2662 END Unfreeze_Worksheets ;
2663 /*---------------------------------------------------------------------------*/
2664
2665
2666 /*===========================================================================+
2667 | PROCEDURE Callback |
2668 +===========================================================================*/
2669 --
2670 -- The callback API.
2671 --
2672 PROCEDURE Callback
2673 (
2674 command IN VARCHAR2,
2675 context IN VARCHAR2,
2676 attr_name IN VARCHAR2,
2677 attr_type IN VARCHAR2,
2678 text_value IN OUT NOCOPY VARCHAR2,
2679 number_value IN OUT NOCOPY NUMBER,
2680 date_value IN OUT NOCOPY DATE
2681 )
2682 IS
2683 --
2684 l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
2685 l_worksheet_name psb_worksheets.name%TYPE ;
2686 --
2687 l_notification_id NUMBER ;
2688
2689 BEGIN
2690 --
2691 IF ( command = 'GET' ) THEN
2692
2693 g_user_name := fnd_global.user_name; -- bug 2576222
2694
2695 IF attr_name = 'WORKSHEET_ID' THEN
2696
2697 number_value := g_worksheet_id ;
2698
2699 ELSIF attr_name = 'WORKSHEET_NAME' THEN
2700
2701 text_value := g_worksheet_name ;
2702
2703 ELSIF attr_name = 'BUDGET_GROUP_NAME' THEN
2704
2705 text_value := g_budget_group_name ;
2706
2707 ELSIF attr_name = 'FROM_ROLE' THEN -- bug 2576222
2708
2709 text_value := g_user_name ;
2710
2711 END IF ;
2712 --
2713 END IF ;
2714
2715 /*
2716 IF ( command = 'SET' ) THEN
2717 --
2718 IF attr_name = 'REPLY' THEN
2719 wf_engine.SetItemAttrText( ItemType => g_itemtype,
2720 ItemKey => g_itemkey,
2721 aname => 'OPERATION_TYPE',
2722 avalue => 'XXX' );
2723
2724 text_value := 'REPLY' ;
2725 END IF ;
2726 --
2727 END IF ;
2728
2729 IF ( command = 'COMPLETE' ) THEN
2730 NULL ;
2731 END IF ;
2732 */
2733
2734 EXCEPTION
2735 --
2736 WHEN OTHERS THEN
2737 RAISE ;
2738
2739 END Callback ;
2740 /*---------------------------------------------------------------------------*/
2741
2742
2743 /*===========================================================================+
2744 | PROCEDURE Check_Review_Groups |
2745 +===========================================================================*/
2746 --
2747 -- Checks whether review groups exist for a worksheet. This is a normal API
2748 -- and it does not follow Workflow API structure.
2749 --
2750 PROCEDURE Check_Review_Groups
2751 (
2752 p_api_version IN NUMBER ,
2753 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2754 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2755 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
2756 p_return_status OUT NOCOPY VARCHAR2 ,
2757 p_msg_count OUT NOCOPY NUMBER ,
2758 p_msg_data OUT NOCOPY VARCHAR2 ,
2759 --
2760 p_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
2761 p_review_group_exists OUT NOCOPY VARCHAR2
2762 )
2763 IS
2764 --
2765 l_api_name CONSTANT VARCHAR2(30) := 'Check_Review_Groups' ;
2766 l_api_version CONSTANT NUMBER := 1.0 ;
2767 --
2768 l_return_status VARCHAR2(1) ;
2769 l_msg_count NUMBER ;
2770 l_msg_data VARCHAR2(2000) ;
2771 --
2772 l_budget_group_id psb_worksheets.budget_group_id%TYPE ;
2773 l_budget_calendar_id psb_worksheets.budget_calendar_id%TYPE ;
2774 l_root_budget_group_id psb_budget_groups.root_budget_group_id%TYPE ;
2775 l_count NUMBER ;
2776 --
2777 l_exists VARCHAR2(10);
2778
2779 BEGIN
2780 --
2781 SAVEPOINT Check_Review_Groups_Pvt ;
2782 --
2783 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2784 p_api_version,
2785 l_api_name,
2786 G_PKG_NAME )
2787 THEN
2788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2789 END IF;
2790 --
2791
2792 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
2793 FND_MSG_PUB.initialize ;
2794 END IF;
2795 --
2796
2797 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2798 p_review_group_exists := FND_API.G_FALSE ;
2799 --
2800
2801 --
2802 -- Get budget_group_id for the worksheet
2803 --
2804 SELECT ws.budget_group_id ,
2805 ws.budget_calendar_id ,
2806 bg.root_budget_group_id
2807 INTO
2808 l_budget_group_id ,
2809 l_budget_calendar_id ,
2810 l_root_budget_group_id
2811 FROM psb_worksheets ws ,
2812 psb_budget_groups bg
2813 WHERE worksheet_id = p_worksheet_id
2814 AND ws.budget_group_id = bg.budget_group_id ;
2815
2816 l_count := 0 ;
2817
2818 --
2819 -- Get budget calendar related info to find whether the review groups is
2820 -- active in the current budget group hierarchy or not.
2821 --
2822 IF NVL(PSB_WS_Acct1.g_budget_calendar_id, -99) <> l_budget_calendar_id
2823 THEN
2824 --
2825 PSB_WS_Acct1.Cache_Budget_Calendar
2826 (
2827 p_return_status => l_return_status ,
2828 p_budget_calendar_id => l_budget_calendar_id
2829 );
2830 --
2831 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2833 END IF ;
2834 --
2835 END IF ;
2836
2837 l_exists := null;
2838 --
2839 -- Checking for review group existence for account sets.
2840 --
2841 BEGIN
2842
2843 /*For Bug No 2115869 Start */
2844 --Fine tuned the following sql by adding the rownum < 2 restriction in sub query
2845 --Also moved the psb_budget_accounts from subquery to main query
2846 SELECT 'Exists' INTO l_exists FROM dual
2847 WHERE EXISTS
2848 (SELECT 1
2849 FROM psb_budget_group_categories cats,
2850 psb_budget_workflow_rules rules,
2851 psb_budget_groups bg,
2852 psb_set_relations relations,
2853 psb_budget_accounts ba
2854 WHERE cats.budget_group_id = l_budget_group_id
2855 AND rules.budget_group_id = l_root_budget_group_id
2856 AND bg.budget_group_id = rules.review_budget_group_id
2857 AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
2858 AND (( bg.effective_end_date IS NULL)
2859 OR( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
2860 AND rules.stage_id = cats.stage_id
2861 AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
2862 AND relations.account_position_set_id = ba.account_position_set_id
2863 AND EXISTS
2864 (
2865 SELECT 1
2866 FROM psb_ws_lines lines ,
2867 psb_ws_account_lines accts
2868 WHERE lines.worksheet_id = p_worksheet_id
2869 AND lines.account_line_id = accts.account_line_id
2870 AND accts.code_combination_id = ba.code_combination_id
2871 AND ROWNUM < 2
2872 ));
2873 /*For Bug No 2115869 End */
2874 EXCEPTION
2875 when NO_DATA_FOUND then
2876 l_exists := null;
2877 END;
2878
2879 IF l_exists = 'Exists' THEN
2880
2881 p_review_group_exists := FND_API.G_TRUE ;
2882
2883 ELSE
2884
2885 l_exists := null;
2886 --
2887 -- Checking for review group existence for position sets.
2888 --
2889 BEGIN
2890
2891 /*For Bug No 2115869 Start */
2892 --Fine tuned the following sql by adding the rownum < 2 restriction in sub query
2893 --Also moved the psb_budget_positions from subquery to main query
2894
2895 SELECT 'Exists' INTO l_exists FROM dual
2896 WHERE EXISTS
2897 (SELECT 1
2898 FROM psb_budget_group_categories cats,
2899 psb_budget_workflow_rules rules,
2900 psb_budget_groups bg,
2901 psb_set_relations relations,
2902 psb_budget_positions bp
2903 WHERE cats.budget_group_id = l_budget_group_id
2904 AND rules.budget_group_id = l_root_budget_group_id
2905 AND bg.budget_group_id = rules.review_budget_group_id
2906 AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
2907 AND (( bg.effective_end_date IS NULL)
2908 OR( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
2909 AND rules.stage_id = cats.stage_id
2910 AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
2911 AND relations.account_position_set_id = bp.account_position_set_id
2912 AND EXISTS
2913 (
2914 SELECT 1
2915 FROM psb_ws_lines_positions lines ,
2916 psb_ws_position_lines pos
2917 WHERE lines.worksheet_id = p_worksheet_id
2918 AND lines.position_line_id = pos.position_line_id
2919 AND pos.position_id = bp.position_id
2920 AND ROWNUM < 2
2921 ));
2922 /*For Bug No 2115869 End */
2923 EXCEPTION
2924 when NO_DATA_FOUND then
2925 l_exists := null;
2926 END;
2927 --
2928 IF l_exists = 'Exists' THEN
2929 p_review_group_exists := FND_API.G_TRUE ;
2930 ELSE
2931
2932 /* Bug 3641566 Start */
2933 --p_review_group_exists := FND_API.G_FALSE ;
2934 l_exists := NULL;
2935 BEGIN
2936 SELECT 'Exists' INTO l_exists
2937 FROM DUAL
2938 WHERE EXISTS
2939 (SELECT 1
2940 FROM PSB_BUDGET_GROUP_CATEGORIES cats,
2941 PSB_BUDGET_WORKFLOW_RULES rules,
2942 PSB_BUDGET_GROUPS bgrp
2943 WHERE cats.budget_group_id = l_budget_group_id
2944 AND rules.stage_id = cats.stage_id
2945 AND rules.budget_group_id = l_root_budget_group_id
2946 AND bgrp.budget_group_id = rules.review_budget_group_id
2947 AND bgrp.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
2948 AND (( bgrp.effective_end_date IS NULL )
2949 OR( bgrp.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
2950 AND EXISTS
2951 (SELECT 1
2952 FROM PSB_POSITIONS ppos,
2953 PSB_WS_LINES_POSITIONS lines,
2954 PSB_WS_POSITION_LINES wspos
2955 WHERE ppos.position_id = wspos.position_id
2956 AND ppos.new_position_flag = 'Y'
2957 AND lines.worksheet_id = p_worksheet_id
2958 AND wspos.position_line_id = lines.position_line_id
2959 )
2960 );
2961 EXCEPTION
2962 WHEN NO_DATA_FOUND THEN
2963 l_exists := NULL;
2964 END;
2965 IF l_exists = 'Exists' THEN
2966 p_review_group_exists := FND_API.G_TRUE;
2967 ELSE
2968 p_review_group_exists := FND_API.G_FALSE ;
2969 END IF ;
2970 /* Bug 3641566 End */
2971 END IF ;
2972 --
2973 END IF ;
2974
2975 --
2976 IF FND_API.To_Boolean ( p_commit ) THEN
2977 COMMIT WORK;
2978 END IF;
2979 --
2980 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2981 p_data => p_msg_data );
2982 --
2983 EXCEPTION
2984 --
2985 WHEN FND_API.G_EXC_ERROR THEN
2986 --
2987 ROLLBACK TO Check_Review_Groups_Pvt ;
2988 p_return_status := FND_API.G_RET_STS_ERROR;
2989 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2990 p_data => p_msg_data );
2991 --
2992 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2993 --
2994 ROLLBACK TO Check_Review_Groups_Pvt ;
2995 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2996 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
2997 p_data => p_msg_data );
2998 --
2999 WHEN OTHERS THEN
3000 --
3001 ROLLBACK TO Check_Review_Groups_Pvt ;
3002 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3003 --
3004 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3005 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
3006 l_api_name );
3007 END if;
3008 --
3009 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
3010 p_data => p_msg_data );
3011 --
3012 END Check_Review_Groups;
3013 /*---------------------------------------------------------------------------*/
3014
3015
3016 END PSB_Submit_Worksheet_PVT ;