[Home] [Help]
PACKAGE BODY: APPS.AMS_WFCMPAPR_PVT
Source
1 PACKAGE BODY AMS_WFCmpApr_PVT AS
2 /* $Header: amsvwcab.pls 115.17 2002/12/02 20:30:54 dbiswas ship $*/
3
4
5 -- Start of Comments
6 --
7 -- NAME
8 -- AMS_WFCmpApr_PVT
9 --
10 -- PURPOSE
11 -- This package contains the workflow procedures for
12 -- Campaign Approval in Oracle Marketing
13 --
14 -- HISTORY
15 -- 09/13/1999 ptendulk CREATED
16 -- 01/28/2000 ptendulk Modified JTF Interaction
17 -- 03/10/2000 ptendulk Modified for Bug 1226905
18 -- 06/07/2000 ptendulk Modified , used the faster JTF view for resources
19 -- 06/08/2000 ptendulk 1. Modified the Update status process
20 -- 2. Modified the create Notif Doc Procedure
21 -- 02-dec-2002 dbiswas NOCOPY and debug-level changes for performance
22
23
24 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_WFCmpApr_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(15):='amsvwcab.pls';
26
27 --
28
29
30 /*************************** PRIVATE ROUTINES *******************************/
31
32 -- Start of Comments
33 --
34 -- NAME
35 -- Handle_Err
36 --
37 -- PURPOSE
38 -- This Procedure will Get all the Errors from the Message stack and
39 -- Set the Workflow item attribut with the Error Messages
40 --
41 -- Used By Activities
42 --
43 --
44 -- NOTES
45 --
46 -- HISTORY
47 -- 11/05/1999 ptendulk created
48 -- End of Comments
49 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
50 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
51 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
52
53 PROCEDURE Handle_Err
54 (p_itemtype IN VARCHAR2 ,
55 p_itemkey IN VARCHAR2 ,
56 p_msg_count IN NUMBER , -- Number of error Messages
57 p_msg_data IN VARCHAR2 ,
58 p_attr_name IN VARCHAR2
59 )
60 IS
61 l_msg_count NUMBER ;
62 l_msg_data VARCHAR2(2000);
63 l_final_data VARCHAR2(4000);
64 l_msg_index NUMBER ;
65 l_cnt NUMBER := 0 ;
66 BEGIN
67
68 WHILE l_cnt < p_msg_count
69 LOOP
70 FND_MSG_PUB.Get(p_msg_index => l_cnt + 1,
71 p_encoded => FND_API.G_FALSE,
72 p_data => l_msg_data,
73 p_msg_index_out => l_msg_index ) ;
74 l_final_data := l_final_data ||l_msg_index||': '||l_msg_data||fnd_global.local_chr(10) ;
75 l_cnt := l_cnt + 1 ;
76 END LOOP ;
77 WF_ENGINE.SetItemAttrText(itemtype => p_itemtype,
78 itemkey => p_itemkey ,
79 aname => p_attr_name,
80 avalue => l_final_data );
81
82 END Handle_Err;
83
84 -- Start of Comments
85 --
86 -- NAME
87 -- Create_WFreqest
88 --
89 -- PURPOSE
90 -- This Procedure inserts workflow process data into AMS_ACT_WF_REQUESTS
91 -- table and returns SUCCESS if the insertion is successful
92 -- else it will return FAILURE
93 --
94 -- CALLED BY
95 --
96
97
98 --
99 -- NOTES
100 --
101 --
102 -- HISTORY
103 -- 09/16/1999 ptendulk created
104 -- End of Comments
105
106
107 PROCEDURE Create_WFreqest(p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER ,
110 x_msg_data OUT NOCOPY VARCHAR2,
111 p_obj_id IN NUMBER,
112 p_object_type IN VARCHAR2,
113 p_approval_type IN VARCHAR2,
114 p_submitted_by IN NUMBER,
115 p_item_key IN VARCHAR2,
116 P_stat_code IN VARCHAR2,
117 p_stat_id IN NUMBER)
118 IS
119
120 l_api_name CONSTANT VARCHAR2(30) := 'Create_WFreqest';
121 l_api_version CONSTANT NUMBER := 1.0;
122 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
123
124
125 -- Status Local Variables
126 l_return_status VARCHAR2(1); -- Return value from procedures
127
128 x_rowid VARCHAR2(30);
129
130 l_wf_request_id NUMBER;
131
132 CURSOR C_wf_request_id_seq IS
133 SELECT ams_act_wf_requests_s.NEXTVAL
134 FROM DUAL;
135
136 BEGIN
137
138 -- Standard Start of API savepoint
139 SAVEPOINT Create_wfreq_PVT;
140
141 IF (AMS_DEBUG_HIGH_ON) THEN
142
143
144
145 AMS_Utility_PVT.debug_message(l_full_name||': start');
146
147 END IF;
148
149 IF FND_API.to_boolean(p_init_msg_list) THEN
150 FND_MSG_PUB.initialize;
151 END IF;
152
153
154 --
155 -- Initialize API return status to success
156 --
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159
160 --
161 -- API body
162 --
163
164 --
165 -- Insert the Record
166 --
167 IF (AMS_DEBUG_HIGH_ON) THEN
168
169 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
170 END IF;
171
172 --
173 -- open cursor AND fetch into local variable
174 --
175 OPEN C_wf_request_id_seq;
176 FETCH C_wf_request_id_seq INTO l_wf_request_id;
177 -- close cursor
178 CLOSE C_wf_request_id_seq;
179
180
181 INSERT INTO ams_act_wf_requests
182 (activity_wf_request_id
183
184 -- standard who columns
185 ,last_update_date
186 ,last_updated_by
187 ,creation_date
188 ,created_by
189 ,last_update_login
190
191 ,object_version_number
192 ,act_wf_req_submitted_for_id
193 ,arc_act_wf_req_submitted_for
194 ,submitted_by_user_id
195 ,request_type
196 ,approval_type
197 ,workflow_item_key
198 ,workflow_process_name
199 ,status_code
200 ,user_status_id
201 ,status_date
202 ,description
203 ,notes
204 )
205 VALUES
206 (
207 l_wf_request_id
208
209 -- standard who columns
210 ,sysdate
211 ,FND_GLOBAL.User_Id
212 ,sysdate
213 ,FND_GLOBAL.User_Id
214 ,FND_GLOBAL.Conc_Login_Id
215
216 ,1
217 ,p_obj_id
218 ,p_object_type
219 ,p_submitted_by
220 ,'APPROVAL_REQUEST'
221 ,p_approval_type
222 ,p_item_key
223 ,'AMS_APPROVAL'
224 ,p_STAT_CODE
225 ,p_stat_id
226 ,sysdate
227 ,null
228 ,null
229 );
230
231 --
232 -- END of API body.
233 --
234
235
236 --
237 -- Standard call to get message count AND IF count is 1, get message info.
238 --
239 FND_MSG_PUB.Count_AND_Get
240 ( p_count => x_msg_count,
241 p_data => x_msg_data,
242 p_encoded => FND_API.G_FALSE
243 );
244
245 IF (AMS_DEBUG_HIGH_ON) THEN
246
247
248
249 AMS_Utility_PVT.debug_message(l_full_name ||': end');
250
251 END IF;
252
253
254
255 EXCEPTION
256
257 WHEN FND_API.G_EXC_ERROR THEN
258
259 ROLLBACK TO Create_wfreq_PVT;
260 x_return_status := FND_API.G_RET_STS_ERROR ;
261
262 FND_MSG_PUB.Count_AND_Get
263 ( p_count => x_msg_count,
264 p_data => x_msg_data,
265 p_encoded => FND_API.G_FALSE
266 );
267
268
269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
270
271 ROLLBACK TO Create_wfreq_PVT;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
273
274 FND_MSG_PUB.Count_AND_Get
275 ( p_count => x_msg_count,
276 p_data => x_msg_data,
277 p_encoded => FND_API.G_FALSE
278 );
279
280
281 WHEN OTHERS THEN
282
283 ROLLBACK TO Create_wfreq_PVT;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
285
286 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
287 THEN
288 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
289 END IF;
290
291 FND_MSG_PUB.Count_AND_Get
292 ( p_count => x_msg_count,
293 p_data => x_msg_data,
294 p_encoded => FND_API.G_FALSE
295 );
296
297 END Create_WFreqest;
298
299
300 -- Start of Comments
301 --
302 -- NAME
303 -- Find_Owner
304 --
305 -- PURPOSE
306 -- This Procedure will be return the User role for
307 -- the userid sent
308 --
309 -- Called By
310 --
311 -- NOTES
312 --
313 -- HISTORY
314 -- 11/30/1999 ptendulk created
315 -- 01/28/2000 ptendulk Modified
316 -- End of Comments
317
318
319 PROCEDURE Get_User_Role
320 ( p_user_id IN NUMBER,
321 x_role_name OUT NOCOPY VARCHAR2,
322 x_role_display_name OUT NOCOPY VARCHAR2 )
323 IS
324
325 BEGIN
326 -- Pass the Employee ID to get the Role
327
328 -- IF (l_emp_id IS NOT NULL) THEN
329 WF_DIRECTORY.getrolename
330 ( p_orig_system => 'PER',
331 p_orig_system_id => p_user_id ,
332 p_name => x_role_name,
333 p_display_name => x_role_display_name );
334 -- ELSE
335
336 -- Has to be changed once the JTF is integrated with Workflow Directory
337 -- Following Code is Commented by ptendulk as for
338 -- Marketing Approvals will go to Only Employees so
339 -- Find out Roles only from Employees Dt : 28Jan2000
340
341 -- WF_DIRECTORY.getrolename
342 -- ( p_orig_system => 'FND_USR',
343 -- p_orig_system_id => p_user_id,
344 -- p_name => x_role_name,
345 -- p_display_name => x_role_display_name );
346 -- WF_DIRECTORY.getrolename
347 -- ( p_orig_system => 'JTF',
348 -- p_orig_system_id => p_user_id,
349 -- p_name => x_role_name,
350 -- p_display_name => x_role_display_name );
351 --
352 -- END IF;
353 END Get_User_Role;
354
355 -- Start of Comments
356 --
357 -- NAME
358 -- Find_Owner
359 --
360 -- PURPOSE
361 -- This Procedure will be called to find
362 -- username of the Owner of the Activity
363 --
364 -- Called By
365 --
366 -- NOTES
367 -- When the process is started , all the variables are extracted
368 -- from database using Activity id passed to the Start Process
369 --
370 -- HISTORY
371 -- 11/30/1999 ptendulk created
372 --
373 -- End of Comments
374
375 PROCEDURE Find_Owner
376 (p_activity_id IN NUMBER ,
377 p_activity_type IN VARCHAR2 ,
378 x_owner_role OUT NOCOPY VARCHAR2
379 )
380 IS
381
382
383 CURSOR c_camp_det(l_camp_id NUMBER) IS
384 SELECT owner_user_id
385 FROM ams_campaigns_vl
386 WHERE campaign_id = l_camp_id ;
387
388
389 -- Added By ptendulk on 28 Jan 2000, The Owner
390 -- of the campaign will be the Owner of the parent
391 -- Campaign Owner If it exist
392
393 l_parent_id NUMBER;
394 CURSOR c_parent_camp IS
395 SELECT parent_campaign_id
396 FROM ams_campaigns_vl
397 WHERE campaign_id = p_activity_id ;
398
399 CURSOR c_deli_det IS
400 SELECT owner_user_id
401 FROM ams_deliverables_vl deli
402 WHERE deliverable_id = p_activity_id ;
403
404 CURSOR c_eveh_det IS
405 SELECT owner_user_id
406 FROM ams_event_headers_vl
407 WHERE event_header_id = p_activity_id ;
408
409 -- Changed By ptendulk on 28 Jan 2000, The Owner
410 -- of the Event Offer will be the Owner of the Event
411 -- Header of that Campaign
412
413 CURSOR c_eveo_det IS
414 SELECT e.owner_user_id
415 FROM ams_event_offers_vl o,ams_event_headers_vl e
416 WHERE o.event_offer_id = p_activity_id
417 AND o.event_header_id = e.event_header_id ;
418
419 -- Following Code is Added by ptendulk on 28th Jan
420 -- Get the person ID from JTF
421 -- Following code is modified by ptendulk on 07-Jun-2000
422 -- the view is changed to faster jtf view
423 CURSOR c_get_person(l_res_id NUMBER) IS
424 SELECT employee_id source_id
425 FROM ams_jtf_rs_emp_v
426 WHERE resource_id = l_res_id ;
427
428 l_person_id NUMBER ;
429
430 l_resource_id NUMBER;
431 l_display_name VARCHAR2(240);
432
433 BEGIN
434 IF p_activity_type = 'CAMP' THEN
435 OPEN c_parent_camp ;
436 FETCH c_parent_camp INTO l_parent_id ;
437 IF l_parent_id IS NULL THEN
438 l_parent_id := p_activity_id ;
439 END IF ;
440 CLOSE c_parent_camp ;
441
442 OPEN c_camp_det(l_parent_id);
443 FETCH c_camp_det INTO l_resource_id;
444 CLOSE c_camp_det ;
445 ELSIF p_activity_type = 'DELV' THEN
446 OPEN c_deli_det;
447 FETCH c_deli_det INTO l_resource_id;
448 CLOSE c_deli_det ;
449 ELSIF p_activity_type = 'EVEH' THEN
450 OPEN c_eveh_det;
451 FETCH c_eveh_det INTO l_resource_id;
452 CLOSE c_eveh_det ;
453 ELSIF p_activity_type = 'EVEO' THEN
454 OPEN c_eveo_det;
455 FETCH c_eveo_det INTO l_resource_id;
456 CLOSE c_eveo_det ;
457 END IF ;
458
459 -- Get the Resource id from JTF
460 OPEN c_get_person(l_resource_id) ;
461 FETCH c_get_person INTO l_person_id ;
462 CLOSE c_get_person ;
463
464 -- x_owner_user_name := l_resource_name ;
465 /***************8888 Has to be removed *****************/
466 -- l_resource_id := 12037 ; -- Userid for BGEORGE
467 -- l_resource_id := 10446 ; -- Userid for NRENGASW
468
469 IF (AMS_DEBUG_HIGH_ON) THEN
470
471
472
473 ams_utility_pvt.debug_message('Owner Person : '||l_person_id);
474
475 END IF;
476
477 Get_User_Role
478 ( p_user_id => l_person_id,
479 x_role_name => x_owner_role,
480 x_role_display_name => l_display_name) ;
481
482 END Find_Owner ;
483
484 -- Start of Comments
485 --
486 -- NAME
487 -- Find_Manager
488 --
489 -- PURPOSE
490 -- This Procedure will be called to find
491 -- username of the Manager of the Requester
492 --
493 -- Called By
494 --
495 -- NOTES
496 -- When the process is started , all the variables are extracted
497 -- from database using Activity id passed to the Start Process
498 --
499 -- HISTORY
500 -- 11/30/1999 ptendulk created
501 -- End of Comments
502
503 PROCEDURE Find_Manager
504 (p_user_id IN NUMBER ,
505 x_manager_role OUT NOCOPY VARCHAR2
506 )
507 IS
508 CURSOR c_user_det IS
509 SELECT manager_id,employee_id
510 FROM ams_jtf_rs_emp_v
511 WHERE resource_id = p_user_id ;
512
513 -- Write cirsor to get Manager's Resource ID
514
515 -- CURSOR c_manager_usr(l_mgr_id IN NUMBER) IS
516 -- SELECT resource_id
517 -- FROM jtf_resource_extn
518 -- WHERE employee_person_id = l_mgr_id ;
519
520
521 l_man_person_id NUMBER ;
522 l_person_id NUMBER;
523 l_manager_id NUMBER;
524 l_display_name VARCHAR2(240);
525
526 BEGIN
527
528 OPEN c_user_det ;
529 FETCH c_user_det INTO l_man_person_id ,l_person_id ;
530 CLOSE c_user_det ;
531
532 -- Following code is modified by ptendulk on 8th Mar
533 -- If the manager not found then Manger's approval not required
534 IF l_man_person_id IS NULL THEN
535 l_man_person_id := l_person_id ;
536 END IF ;
537
538 -- Give call to find Resource ID of Manager
539
540 /*************** Has to be removed *****************/
541 -- l_mgr_person_id := 12038 ; -- Userid for NRENGASW
542 --l_mgr_person_id := 10446 ; -- Userid for PTENDULK
543 IF (AMS_DEBUG_HIGH_ON) THEN
544
545 ams_utility_pvt.debug_message('Manager: '||l_man_person_id );
546 END IF;
547
548 Get_User_Role
549 ( p_user_id => l_man_person_id,
550 x_role_name => x_manager_role,
551 x_role_display_name => l_display_name) ;
552
553
554 END Find_Manager ;
555
556 -- Start of Comments
557 --
558 -- NAME
559 -- Find_Source
560 --
561 -- PURPOSE
562 -- This Procedure will be called to find
563 -- username of the Fund Manager of the Activity
564 --
565 -- Called By
566 --
567 -- NOTES
568 -- When the process is started , all the variables are extracted
569 -- from database using Activity id passed to the Start Process
570 --
571 -- HISTORY
572 -- 01/28/2000 Created
573 -- End of Comments
574 PROCEDURE Find_Source
575 ( p_activity_type IN VARCHAR2,
576 p_activity_id IN VARCHAR2,
577 x_source_type OUT NOCOPY VARCHAR2,
578 x_source_id OUT NOCOPY NUMBER
579 )
580 IS
581
582
583 CURSOR c_camp_det IS
584 SELECT fund_source_type,
585 fund_source_id
586 FROM ams_campaigns_vl
587 WHERE campaign_id = p_activity_id ;
588
589 CURSOR c_deli_det IS
590 SELECT fund_source_type,
591 fund_source_id
592 FROM ams_deliverables_vl
593 WHERE deliverable_id = p_activity_id ;
594
595 CURSOR c_eveh_det IS
596 SELECT fund_source_type_code,
597 fund_source_id
598 FROM ams_event_headers_vl
599 WHERE event_header_id = p_activity_id ;
600
601 CURSOR c_eveo_det IS
602 SELECT fund_source_type_code,
603 fund_source_id
604 FROM ams_event_offers_vl
605 WHERE event_offer_id = p_activity_id ;
606
607
608 BEGIN
609 IF p_activity_type = 'CAMP' THEN
610 OPEN c_camp_det ;
611 FETCH c_camp_det INTO x_source_type,x_source_id ;
612 CLOSE c_camp_det ;
613 ELSIF p_activity_type = 'DELV' THEN
614 OPEN c_deli_det ;
615 FETCH c_deli_det INTO x_source_type,x_source_id ;
616 CLOSE c_deli_det ;
617 ELSIF p_activity_type = 'EVEH' THEN
618 OPEN c_eveh_det ;
619 FETCH c_eveh_det INTO x_source_type,x_source_id ;
620 CLOSE c_eveh_det ;
621 ELSIF p_activity_type = 'EVEO' THEN
622 OPEN c_eveo_det ;
623 FETCH c_eveo_det INTO x_source_type,x_source_id ;
624 CLOSE c_eveo_det ;
625 END IF ;
626 END Find_Source ;
627
628 -- Start of Comments
629 --
630 -- NAME
631 -- Find_Fund_Manager
632 --
633 -- PURPOSE
634 -- This Procedure will be called to find
635 -- username of the Fund Manager of the Activity
636 --
637 -- Called By
638 --
639 -- NOTES
640 -- When the process is started , all the variables are extracted
641 -- from database using Activity id passed to the Start Process
642 --
643 -- HISTORY
644 -- 11/30/1999 ptendulk created
645 -- 01/29/2000 ptendulk Modified
646 -- End of Comments
647 PROCEDURE Find_Fund_Manager
648 ( p_activity_type IN VARCHAR2,
649 p_activity_id IN VARCHAR2,
650 x_manager_role OUT NOCOPY VARCHAR2
651 )
652 IS
653
654 l_source_id NUMBER ;
655 l_source_type VARCHAR2(30);
656
657 CURSOR c_fund_det IS
658 SELECT owner
659 FROM ozf_funds_vl
660 WHERE fund_id = l_source_id ;
661
662 CURSOR c_camp_det IS
663 SELECT owner_user_id
664 FROM ams_campaigns_vl
665 WHERE campaign_id = l_source_id ;
666
667 CURSOR c_deli_det IS
668 SELECT owner_user_id
669 FROM ams_deliverables_vl deli
670 WHERE deliverable_id = l_source_id ;
671
672 CURSOR c_eveh_det IS
673 SELECT owner_user_id
674 FROM ams_event_headers_vl
675 WHERE event_header_id = l_source_id ;
676
677 CURSOR c_eveo_det IS
678 SELECT owner_user_id
679 FROM ams_event_offers_vl
680 WHERE event_offer_id = l_source_id ;
681
682 -- Following Code is Added by ptendulk on 28th Jan
683 -- Get the person ID from JTF
684 -- Following code is modified by ptendulk on 07-Jun-00
685 -- Replaced JTF view with the faster one
686 CURSOR c_get_person(l_res_id NUMBER) IS
687 SELECT employee_id source_id
688 FROM ams_jtf_rs_emp_v
689 WHERE resource_id = l_res_id ;
690
691 l_person_id NUMBER;
692 l_resource_id NUMBER;
693 l_display_name VARCHAR2(240);
694 BEGIN
695 -- Find the Source for Fund for the current Activity
696 Find_Source
697 ( p_activity_type => p_activity_type,
698 p_activity_id => p_activity_id,
699 x_source_type => l_source_type,
700 x_source_id => l_source_id ) ;
701
702 IF l_source_type = 'FUND' THEN
703 OPEN c_fund_det ;
704 FETCH c_fund_det INTO l_resource_id ;
705 CLOSE c_fund_det ;
706 ELSIF l_source_type = 'CAMP' THEN
707 OPEN c_camp_det ;
708 FETCH c_camp_det INTO l_resource_id ;
709 CLOSE c_camp_det ;
710 ELSIF l_source_type = 'EVEH' THEN
711 OPEN c_eveh_det ;
712 FETCH c_eveh_det INTO l_resource_id ;
713 CLOSE c_eveh_det ;
714 ELSIF l_source_type = 'EVEO' THEN
715 OPEN c_eveo_det ;
716 FETCH c_eveo_det INTO l_resource_id ;
717 CLOSE c_eveo_det ;
718 END IF ;
719
720 -- Get the Resource id from JTF
721 OPEN c_get_person(l_resource_id) ;
722 FETCH c_get_person INTO l_person_id ;
723 CLOSE c_get_person ;
724
725 -- x_owner_user_name := l_resource_name ;
726 /***************8888 Has to be removed *****************/
727 -- l_resource_id := 12037 ; -- Userid for BGEORGE
728 -- l_resource_id := 10446 ; -- Userid for NRENGASW
729
730 Get_User_Role
731 ( p_user_id => l_person_id,
732 x_role_name => x_manager_role,
733 x_role_display_name => l_display_name) ;
734
735 --x_manager_role := 'SSUNDARE' ;
736 -- x_manager_role := 'NRENGASW' ;
737
738 --Get_User_Role
739 -- ( p_user_id => l_mgr_person_id,
740 -- x_role_name => x_manager_role,
741 -- x_role_display_name => l_display_name) ;
742 --
743 END Find_Fund_Manager ;
744
745 -- Start of Comments
746 --
747 -- NAME
748 -- Get_Lookup_meaning
749 --
750 -- PURPOSE
751 -- This Function Will return the meaning of the Lookup used in Approvals
752 --
753 -- CALLED BY
754 -- Prepare_Doc
755 --
756 -- NOTES
757 --
758 --
759 -- HISTORY
760 -- 09/14/1999 ptendulk created
761 -- 11/30/1999 ptendulk Modified
762 -- End of Comments
763
764 FUNCTION Get_Lookup_meaning(p_lookup_type IN VARCHAR2,
765 p_lookup_code IN VARCHAR2)
766 RETURN VARCHAR2
767 IS
768 CURSOR c_meaning IS
769 SELECT meaning
770 FROM ams_lookups
771 WHERE lookup_type = p_lookup_type
772 AND lookup_code = p_lookup_code ;
773 l_meaning VARCHAR2(80);
774 BEGIN
775 OPEN c_meaning ;
776 FETCH c_meaning INTO l_meaning ;
777 CLOSE c_meaning ;
778 RETURN(l_meaning);
779 END;
780
781
782 -- Start of Comments
783 --
784 -- NAME
785 -- StartProcess
786 --
787 -- PURPOSE
788 -- This Procedure will Start the flow
789 --
790 --
791 -- IN
792 --
793 --
794 -- OUT
795 --
796 -- Used By Activities
797 --
798 -- NOTES
799 --
800 --
801 -- HISTORY
802 -- 09/13/1999 ptendulk created
803 -- 11/30/1999 ptendulk Modified
804 -- End of Comments
805
806 PROCEDURE StartProcess
807 (p_approval_for IN VARCHAR2
808 ,p_approval_for_id IN NUMBER
809 ,p_object_version_number IN NUMBER
810 ,p_orig_stat_id IN NUMBER
811 ,p_new_stat_id IN NUMBER
812 ,p_requester_userid IN NUMBER
813 ,p_notes_from_requester IN VARCHAR2 DEFAULT NULL
814 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
815 ,p_item_type IN VARCHAR2 DEFAULT NULL
816 )
817 IS
818 itemtype VARCHAR2(30) := nvl(p_item_type,'AMSAPPR');
819 itemkey VARCHAR2(30) := p_approval_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss');
820 itemuserkey VARCHAR2(80) := p_approval_for||'-'||p_approval_for_id ;
821
822 l_requester_role VARCHAR2(100) ;
823 l_manager_role VARCHAR2(100) ;
824 l_display_name VARCHAR2(240) ;
825 l_appr_for VARCHAR2(240) ;
826
827 -- Following code is modified by ptendulk on 07-Jun-00
828 -- Replaced JTF view with the faster one
829 CURSOR c_resource IS
830 SELECT resource_id ,employee_id source_id
831 FROM ams_jtf_rs_emp_v
832 WHERE user_id = p_requester_userid ;
833 l_requester_id NUMBER ;
834 l_person_id NUMBER ;
835
836 BEGIN
837 -- Start Process :
838 -- If workflowprocess is passed, it will be run.
839 -- If workflowprocess is NOT passed, the selector function
840 -- defined in the item type will determine which process to run.
841 IF (AMS_DEBUG_HIGH_ON) THEN
842
843 AMS_Utility_PVT.debug_message('Start :Item Type : '||itemtype||' Item key : '||itemkey);
844 END IF;
845 -- dbms_output.put_line('Start :Item Type : '||itemtype||' Item key : '||itemkey);
846
847 WF_ENGINE.CreateProcess (itemtype => itemtype, --itemtype,
848 itemkey => itemkey ,
849 process => p_workflowprocess);
850
851 WF_ENGINE.SetItemUserkey(itemtype => itemtype,
852 itemkey => itemkey ,
853 userkey => itemuserkey);
854
855 -- Initialize Workflow Item Attributes
856
857 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
858 itemkey => itemkey,
859 aname => 'AMS_APPROVAL_FOR_OBJECT',
860 avalue => p_approval_for );
861
862 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
863 itemkey => itemkey,
864 aname => 'AMS_ACT_ID',
865 avalue => p_approval_for_id );
866
867 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
868 itemkey => itemkey,
869 aname => 'AMS_ORIG_STAT_ID',
870 avalue => p_orig_stat_id );
871
872 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
873 itemkey => itemkey,
874 aname => 'AMS_NEW_STAT_ID',
875 avalue => p_new_stat_id );
876
877 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
878 itemkey => itemkey,
879 aname => 'AMS_OBJECT_VERSION_NUMBER',
880 avalue => p_object_version_number );
881
882 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
883 itemkey => itemkey,
884 aname => 'AMS_NOTES_FROM_REQUESTER',
885 avalue => nvl(p_notes_from_requester,'') );
886
887 OPEN c_resource ;
888 FETCH c_resource INTO l_requester_id ,l_person_id ;
889 CLOSE c_resource ;
890
891 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
892 itemkey => itemkey,
893 aname => 'AMS_REQUESTER_ID',
894 avalue => l_requester_id );
895
896 l_appr_for := Get_Lookup_Meaning('AMS_SYS_ARC_QUALIFIER',p_approval_for);
897
898 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
899 itemkey => itemkey,
900 aname => 'AMS_APPR_FOR',
901 avalue => l_appr_for );
902
903 Get_User_Role
904 (p_user_id => l_person_id,
905 x_role_name => l_requester_role,
906 x_role_display_name => l_display_name );
907
908
909 -- Following Code is Commented by ptendulk as Find Manager will be called in Set
910 -- Activity Details
911 -- Find_Manager
912 -- (p_user_id => p_requester_userid,
913 -- x_manager_role => l_manager_role );
914 -- WF_ENGINE.SetItemAttrText(itemtype => itemtype,
915 -- itemkey => itemkey,
916 -- aname => 'AMS_MANAGER',
917 -- avalue => l_manager_role );
918 --
919
920 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Manager : '||l_manager_role); END IF;
921 -- dbms_output.put_line('Manager : '||l_manager_role);
922 IF (AMS_DEBUG_HIGH_ON) THEN
923
924 AMS_Utility_PVT.debug_message('requester : '||l_requester_role);
925 END IF;
926 -- dbms_output.put_line('requester : '||l_requester_role);
927
928 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
929 itemkey => itemkey,
930 aname => 'AMS_REQUESTER',
931 avalue => l_requester_role );
932
933
934 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
935 itemkey => itemkey,
936 aname => 'MONITOR_URL',
937 avalue => wf_monitor.geturl
938 (wf_core.TRANSLATE('WF_WEB_AGENT'), itemtype, itemkey, 'NO')
939 );
940
941
942 WF_ENGINE.SetItemOwner (itemtype => itemtype,
943 itemkey => itemkey,
944 owner => l_requester_role);
945
946
947 WF_ENGINE.StartProcess (itemtype => itemtype,
948 itemkey => itemkey);
949
950
951 EXCEPTION
952 WHEN OTHERS
953 THEN
954 wf_core.context ('AMS_WfCmpApr_PVT', 'StartProcess',p_approval_for
955 ,p_approval_for_id ,p_workflowprocess);
956 RAISE;
957
958 END StartProcess;
959
960 -- Start of Comments
961 --
962 -- NAME
963 -- Selector
964 --
965 -- PURPOSE
966 -- This Procedure will determine which process to run
967 --
968 -- IN
969 -- itemtype - A Valid item type from (WF_ITEM_TYPES Table).
970 -- itemkey - A string generated from application object's primary key.
971 -- actid - The function Activity
972 -- funcmode - Run / Cancel
973 --
974 -- OUT
975 -- resultout - Name of workflow process to run
976 --
977 -- Used By Activities
978 --
979 -- NOTES
980 --
981 --
982 -- HISTORY
983 -- 08/13/1999 ptendulk created
984 -- 11/30/1999 ptendulk Modified
985 -- End of Comments
986
987
988 PROCEDURE Selector( itemtype IN VARCHAR2,
989 itemkey IN VARCHAR2,
990 actid IN NUMBER,
991 funcmode IN VARCHAR2,
992 resultout OUT NOCOPY VARCHAR2
993 )
994 IS
995 -- PL/SQL Block
996 BEGIN
997 IF (AMS_DEBUG_HIGH_ON) THEN
998
999 AMS_Utility_PVT.debug_message('In Selector Function');
1000 END IF;
1001 -- dbms_output.put_line('In Selector Function');
1002 --
1003 -- RUN mode - normal process execution
1004 --
1005 IF (funcmode = 'RUN')
1006 THEN
1007 --
1008 -- Return process to run
1009 --
1010 resultout := 'AMS_APPROVAL';
1011 RETURN;
1012 END IF;
1013 --
1014 -- CANCEL mode - activity 'compensation'
1015 --
1016 IF (funcmode = 'CANCEL')
1017 THEN
1018 --
1019 -- Return process to run
1020 --
1021 resultout := 'AMS_APPROVAL';
1022 RETURN;
1023 END IF;
1024 --
1025 -- TIMEOUT mode
1026 --
1027 IF (funcmode = 'TIMEOUT')
1028 THEN
1029 resultout := 'AMS_APPROVAL';
1030 RETURN;
1031 END IF;
1032 --
1033 EXCEPTION
1034 WHEN OTHERS
1035 THEN
1036 wf_core.context ('AMS_WFCMPAPR_PVT', 'Selector', itemtype, itemkey, actid, funcmode);
1037 RAISE;
1038 END Selector;
1039
1040 -- Start of Comments
1041 --
1042 -- NAME
1043 -- check_status_order_type
1044 --
1045 -- PURPOSE
1046 -- This Function will return the Status status type for the objects.
1047 -- i.e. CAMP/DELV/EVEO/EVEH
1048 --
1049 --
1050 -- IN
1051 -- Object_type -- CAMP/DELV/EVEO/EVEH
1052 --
1053 -- OUT
1054 -- SYSTEM_STATUS_TYPE
1055 --
1056 -- NOTES
1057 --
1058 --
1059 -- HISTORY
1060 -- 12/1/1999 ptendulk Modified
1061 -- End of Comments
1062
1063 FUNCTION Check_Status_Order_Type(p_approval_for_object IN VARCHAR2)
1064 RETURN VARCHAR2
1065 IS
1066 BEGIN
1067 IF p_approval_for_object = 'CAMP' THEN
1068 RETURN('AMS_CAMPAIGN_STATUS');
1069 ELSIF p_approval_for_object = 'DELV' THEN
1070 RETURN('AMS_DELIV_STATUS');
1071 ELSIF p_approval_for_object = 'EVEH' THEN
1072 RETURN('AMS_EVENT_STATUS');
1073 ELSIF p_approval_for_object = 'EVEO' THEN
1074 RETURN('AMS_EVENT_STATUS');
1075 END IF;
1076 END Check_Status_Order_Type;
1077
1078 -- Start of Comments
1079 --
1080 -- NAME
1081 -- Get_Valid_status
1082 --
1083 -- PURPOSE
1084 -- This Procedure will return the Valid System status for the objects.
1085 --
1086 --
1087 -- IN
1088 -- Object_type -- CAMP/DELV/EVEO/EVEH
1089 -- Stat_Code -- (Used in the Procedure)
1090 --
1091 -- OUT
1092 -- SYSTEM_STATUS_CODE (Defined in the Status_Order_Rules)
1093 --
1094 -- NOTES
1095 -- If the Status Order Rule table changes, Change this Procedure only
1096 -- No need to Change anywhere else. This Process uses internal codes
1097 -- to find out what system status codes are used for it.
1098 -- For e.g. 'SUBMIT_THEME_APPROVAL' for 'SUBMIT_TA'
1099 -- HISTORY
1100 -- 12/1/1999 ptendulk Modified
1101 -- 01/28/2000 ptendulk Modified Lookups
1102 -- End of Comments
1103 PROCEDURE Get_Valid_status(p_object_type IN VARCHAR2,
1104 p_stat_code IN VARCHAR2,
1105 x_sys_stat_code OUT NOCOPY VARCHAR2)
1106 IS
1107 BEGIN
1108 IF p_object_type = 'DELV' THEN
1109 IF p_stat_code = 'SUBMIT_TA' THEN
1110 x_sys_stat_code := 'SUBMITTED_TA' ;
1111 ELSIF p_stat_code = 'SUBMIT_BA' THEN
1112 x_sys_stat_code := 'SUBMITTED_BA' ;
1113 ELSIF p_stat_code = 'REJECT_TA' THEN
1114 x_sys_stat_code := 'DENIED_TA' ;
1115 ELSIF p_stat_code = 'REJECT_BA' THEN
1116 x_sys_stat_code := 'DENIED_BA' ;
1117 END IF;
1118 ELSIF (p_object_type = 'CAMP' OR
1119 p_object_type = 'EVEH' OR
1120 p_object_type = 'EVEO' )THEN
1121 IF p_stat_code = 'SUBMIT_TA' THEN
1122 x_sys_stat_code := 'SUBMITTED_TA' ;
1123 ELSIF p_stat_code = 'SUBMIT_BA' THEN
1124 x_sys_stat_code := 'SUBMITTED_BA' ;
1125 ELSIF p_stat_code = 'REJECT_TA' THEN
1126 x_sys_stat_code := 'DENIED_TA' ;
1127 ELSIF p_stat_code = 'REJECT_BA' THEN
1128 x_sys_stat_code := 'DENIED_BA' ;
1129 END IF;
1130 END IF;
1131
1132 END Get_Valid_status ;
1133
1134 -- Start of Comments
1135 --
1136 -- NAME
1137 -- Update_Status
1138 --
1139 -- PURPOSE
1140 -- This Procedure will Update the Statuses of the Activities
1141 --
1142 --
1143 -- IN
1144 -- Object_type -- CAMP/DELV/EVEO/EVEH
1145 -- Object_id -- Camp_id,..
1146 -- Object Version Number
1147 -- Next Status Code -- System Status Code
1148 -- Next_Stat_id -- User Status ID
1149 --
1150 -- OUT
1151 -- x_retuen_status -- Success Flag
1152 --
1153 -- NOTES
1154 -- If the next Status id (User Sta ID ) is passed then the Status of
1155 -- the Activity is updated using this status ID but If not passed then
1156 -- default status ID of the system status code passed will be used as
1157 -- the Status of the Activity
1158 --
1159 -- HISTORY
1160 -- 12/1/1999 ptendulk Modified
1161 -- 06/08/2000 ptendulk Changed the Update Campaign Statement
1162 -- End of Comments
1163 PROCEDURE Update_Status(p_obj_type IN VARCHAR2,
1164 p_obj_id IN NUMBER,
1165 p_object_version_number IN NUMBER,
1166 p_next_stat_code IN VARCHAR2, --System Status
1167 p_next_stat_id IN NUMBER DEFAULT NULL, --User Status
1168 p_appr_type IN VARCHAR2 DEFAULT NULL,
1169 p_submitted_by IN NUMBER ,
1170 p_item_key IN VARCHAR2 ,
1171 x_msg_count OUT NOCOPY NUMBER,
1172 x_msg_data OUT NOCOPY VARCHAR2,
1173 x_return_status OUT NOCOPY VARCHAR2)
1174
1175 IS
1176
1177 l_api_name CONSTANT VARCHAR2(30) := 'Approval_Req_Check';
1178 l_next_stat_id NUMBER;
1179 l_api_version CONSTANT NUMBER := 1.0;
1180
1181 CURSOR c_next_user_status(l_stat_type IN VARCHAR2,
1182 l_stat_code IN VARCHAR2)
1183 IS
1184 SELECT user_status_id
1185 FROM ams_user_statuses_vl
1186 WHERE system_status_code = l_stat_code
1187 AND system_status_type = l_stat_type
1188 AND default_flag = 'Y' ;
1189
1190 l_camp_rec AMS_Campaign_PVT.camp_rec_type;
1191 l_eveh_rec AMS_EVENTHEADER_PVT.evh_rec_type ;
1192 l_eveo_rec AMS_EVENTOFFER_PVT.evo_rec_type ;
1193 l_delv_rec AMS_DELIVERABLE_PVT.deliv_rec_type;
1194 l_stat_type VARCHAR2(30);
1195
1196 BEGIN
1197 --
1198 -- Initialize the Message List
1199 --
1200 FND_MSG_PUB.initialize;
1201
1202 --
1203 -- Get the System status type
1204 --
1205 l_stat_type := Check_Status_Order_Type(p_obj_type) ;
1206
1207 --
1208 -- Get Next User Status Code
1209 --
1210 IF p_next_stat_id IS NULL THEN
1211 OPEN c_next_user_status(l_stat_type,p_next_stat_code) ;
1212 FETCH c_next_user_status INTO l_next_stat_id ;
1213 CLOSE c_next_user_status ;
1214 ELSE
1215 l_next_stat_id := p_next_stat_id ;
1216 END IF;
1217
1218 --
1219 -- Call the APIs to Update the Activities
1220 --
1221 IF p_obj_type = 'CAMP' THEN
1222
1223 Update ams_campaigns_all_b set user_status_id = l_next_stat_id,
1224 status_code = p_next_stat_code,
1225 status_date = sysdate
1226 where campaign_id = p_obj_id ;
1227 -- AND object_version_number = p_object_version_number ;
1228
1229 -- Update Campaign
1230 -- AMS_Campaign_PVT.init_camp_rec(l_camp_rec);
1231 -- l_camp_rec.campaign_id := p_obj_id ;
1232 -- l_camp_rec.status_code := p_next_stat_code ;
1233 -- l_camp_rec.status_date := SYSDATE ;
1234 -- l_camp_rec.user_status_id := l_next_stat_id ;
1235 -- l_camp_rec.object_version_number := p_object_version_number ;
1236
1237
1238 -- dbms_output.put_line('Update Campaign Stat Code : '||p_next_stat_code||'Status ID : '||l_next_stat_id) ;
1239 -- AMS_CAMPAIGN_PVT.Update_Campaign(
1240 -- p_api_version => l_api_version,
1241 -- p_init_msg_list => FND_API.g_false,
1242 -- p_commit => FND_API.g_false,
1243 -- p_validation_level => FND_API.g_valid_level_full,
1244 --
1245 -- x_return_status => x_return_status,
1246 -- x_msg_count => x_msg_count,
1247 -- x_msg_data => x_msg_data,
1248 --
1249 -- p_camp_rec => l_camp_rec
1250 -- ) ;
1251
1252 ELSIF p_obj_type = 'DELV' THEN
1253
1254 -- Update Deliverables
1255 Update ams_deliverables_all_b
1256 set user_status_id = l_next_stat_id,
1257 status_code = p_next_stat_code,
1258 status_date = sysdate
1259 where deliverable_id = p_obj_id ;
1260 -- AND object_version_number = p_object_version_number ;
1261
1262 -- AMS_Deliverable_PVT.init_deliv_rec(l_delv_rec);
1263 -- - l_delv_rec.deliverable_id := p_obj_id ;
1264 -- l_delv_rec.status_code := p_next_stat_code ;
1265 -- l_delv_rec.status_date := SYSDATE ;
1266 -- l_delv_rec.user_status_id := l_next_stat_id ;
1267 -- l_delv_rec.object_version_number := p_object_version_number ;
1268 --
1269 -- AMS_DELIVERABLE_PVT.Update_Deliverable(
1270 -- p_api_version => l_api_version,
1271 -- p_init_msg_list => FND_API.g_false,
1272 -- p_commit => FND_API.g_false,
1273 -- p_validation_level => FND_API.g_valid_level_full,
1274 --
1275 -- x_return_status => x_return_status,
1276 -- x_msg_count => x_msg_count,
1277 -- x_msg_data => x_msg_data,
1278 --
1279 -- p_deliv_rec => l_delv_rec
1280 -- ) ;
1281 --
1282 ELSIF p_obj_type = 'EVEH' THEN
1283 -- Update Event Header
1284 Update ams_event_headers_all_b
1285 set user_status_id = l_next_stat_id,
1286 system_status_code = p_next_stat_code,
1287 last_status_date = sysdate
1288 where event_header_id = p_obj_id ;
1289 -- AND object_version_number = p_object_version_number ;
1290
1291 -- AMS_EVENTHEADER_PVT.init_evh_rec(l_eveh_rec);
1292 -- l_eveh_rec.event_header_id := p_obj_id ;
1293 -- l_eveh_rec.system_status_code := p_next_stat_code ;
1294 -- l_eveh_rec.last_status_date := SYSDATE ;
1295 -- l_eveh_rec.object_version_number := p_object_version_number ;
1296 -- l_eveh_rec.user_status_id := l_next_stat_id ;
1297 --
1298 -- AMS_EVENTHEADER_PVT.Update_Event_Header(
1299 -- p_api_version => l_api_version,
1300 -- p_init_msg_list => FND_API.g_false,
1301 -- p_commit => FND_API.g_false,
1302 -- p_validation_level => FND_API.g_valid_level_full,
1303 --
1304 -- x_return_status => x_return_status,
1305 -- x_msg_count => x_msg_count,
1306 -- x_msg_data => x_msg_data,
1307 --
1308 -- p_evh_rec => l_eveh_rec
1309 -- ) ;
1310 ELSIF p_obj_type = 'EVEO' THEN
1311 -- Update Event Offers
1312 Update ams_event_offers_all_b
1313 set user_status_id = l_next_stat_id,
1314 system_status_code = p_next_stat_code,
1315 last_status_date = sysdate
1316 where event_offer_id = p_obj_id ;
1317 -- AND object_version_number = p_object_version_number ;
1318
1319
1320 -- AMS_EVENTOFFER_PVT.init_evo_rec(l_eveo_rec);
1321 -- l_eveo_rec.event_offer_id := p_obj_id ;
1322 -- l_eveo_rec.system_status_code := p_next_stat_code ;
1323 -- l_eveo_rec.last_status_date := SYSDATE ;
1324 -- l_eveo_rec.object_version_number := p_object_version_number ;
1325 -- l_eveo_rec.user_status_id := l_next_stat_id ;
1326 --
1327 -- AMS_EVENTOFFER_PVT.Update_Event_Offer(
1328 -- p_api_version => l_api_version,
1329 -- p_init_msg_list => FND_API.g_false,
1330 -- p_commit => FND_API.g_false,
1331 -- p_validation_level => FND_API.g_valid_level_full,
1332 --
1333 -- x_return_status => x_return_status,
1334 -- x_msg_count => x_msg_count,
1335 -- x_msg_data => x_msg_data,
1336 --
1337 -- p_evo_rec => l_eveo_rec
1338 -- ) ;
1339
1340 END IF;
1341
1342 Create_WFreqest(p_init_msg_list => FND_API.G_FALSE,
1343 x_return_status => x_return_status,
1344 x_msg_count => x_msg_count,
1345 x_msg_data => x_msg_data,
1346 p_obj_id => p_obj_id ,
1347 p_object_type => p_obj_type,
1348 p_approval_type => p_appr_type,
1349 p_submitted_by => p_submitted_by,
1350 p_item_key => p_item_key,
1351 P_stat_code => p_next_stat_code,
1352 p_stat_id => l_next_stat_id) ;
1353
1354 END Update_Status;
1355
1356
1357
1358 -- Start of Comments
1359 --
1360 -- NAME
1361 -- Update_Attribute
1362 --
1363 -- PURPOSE
1364 -- This Procedure will Update the Attributes of the Activities
1365 -- upon Approvals
1366 --
1367 -- IN
1368 -- Object_type -- CAMP/DELV/EVEO/EVEH
1369 -- Object_id -- Camp_id,..
1370 -- Object Version Number
1371 -- Next Status Code -- System Status Code
1372 -- Next_Stat_id -- User Status ID
1373 --
1374 -- OUT
1375 -- x_return_status -- Success Flag
1376 --
1377 -- NOTES
1378 -- If the next Status id (User Sta ID ) is passed then the Status of
1379 -- the Activity is updated using this status ID but If not passed then
1380 -- default status ID of the system status code passed will be used as
1381 -- the Status of the Activity
1382 --
1383 -- HISTORY
1384 -- 12/1/1999 ptendulk Modified
1385 -- End of Comments
1386 PROCEDURE Update_Attribute(p_obj_type IN VARCHAR2,
1387 p_obj_id IN NUMBER,
1388 p_obj_attr IN VARCHAR2,
1389 x_msg_count OUT NOCOPY NUMBER,
1390 x_msg_data OUT NOCOPY VARCHAR2,
1391 x_return_status OUT NOCOPY VARCHAR2)
1392 IS
1393
1394
1395 BEGIN
1396 -- This Process Has to be modified once the API for Update Attribute
1397 -- is available
1398 -- UPDATE ams_object_attributes
1399 -- SET attribute_defined_flag = 'Y'
1400 -- WHERE object_type = p_obj_type
1401 -- AND object_id = p_obj_id
1402 -- AND object_attribute = p_obj_attr ;
1403
1404 AMS_ObjectAttribute_PVT.Modify_Object_Attribute
1405 (p_api_version => 1.0 ,
1406 p_init_msg_list => FND_API.g_false,
1407 p_commit => FND_API.g_false,
1408 p_validation_level => FND_API.g_valid_level_full,
1409
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_msg_data => x_msg_data,
1413
1414 p_object_type => p_obj_type,
1415 p_object_id => p_obj_id,
1416 p_attr => p_obj_attr,
1417 p_attr_defined_flag => 'Y' ) ;
1418
1419
1420 END Update_Attribute;
1421
1422
1423 -- Start of Comments
1424 --
1425 -- NAME
1426 -- Create Note
1427 --
1428 -- PURPOSE
1429 -- This Procedure will create the Approval Note in
1430 -- Notes Table
1431 --
1432 --
1433 -- HISTORY
1434 -- 01/29/2000 ptendulk Modified
1435 -- End of Comments
1436 PROCEDURE Update_Note(p_obj_type IN VARCHAR2,
1437 p_obj_id IN NUMBER,
1438 p_note IN VARCHAR2,
1439 p_user IN VARCHAR2,
1440 x_msg_count OUT NOCOPY NUMBER,
1441 x_msg_data OUT NOCOPY VARCHAR2,
1442 x_return_status OUT NOCOPY VARCHAR2)
1443 IS
1444 l_id NUMBER ;
1445 l_tab wf_directory.UserTable ;
1446 l_res_id NUMBER ;
1447
1448 -- *************************8Need to Change
1449 CURSOR c_emp_id(l_usr VARCHAR2) IS
1450 SELECT resource_id
1451 FROM ams_jtf_rs_emp_v
1452 WHERE resource_id = 100 ;
1453
1454 BEGIN
1455 -- Note API to Update Approval Notes
1456
1457 wf_directory.GetRoleUsers(p_user,l_tab);
1458
1459 OPEN c_emp_id(l_tab(1));
1460 FETCH c_emp_id INTO l_res_id ;
1461 CLOSE c_emp_id ;
1462
1463
1464 JTF_NOTES_PUB.Create_note
1465 ( p_api_version => 1.0 ,
1466
1467 x_return_status => x_return_status,
1468 x_msg_count => x_msg_count,
1469 x_msg_data => x_msg_data,
1470
1471 p_source_object_id => p_obj_id,
1472 p_source_object_code => p_obj_type,
1473 p_notes => p_note,
1474 p_note_status => NULL ,
1475 p_entered_by => l_res_id,
1476 p_entered_date => sysdate,
1477 x_jtf_note_id => l_id ,
1478 p_note_type => 'APPROVAL' ,
1479 p_last_update_date => SYSDATE ,
1480 p_last_updated_by => l_res_id ,
1481 p_creation_date => SYSDATE ) ;
1482
1483
1484
1485
1486 END Update_Note;
1487
1488
1489 -- Start of Comments
1490 --
1491 -- NAME
1492 -- Check_Approval_Required
1493 --
1494 -- PURPOSE
1495 -- This Procedure will check which type of Approvals are required for the
1496 -- Objects to change the status
1497 --
1498 -- IN
1499 -- p_orig_stat
1500 -- p_new_stat
1501 -- p_stat_type
1502 --
1503 -- OUT
1504 -- SYSTEM_STATUS_TYPE
1505 --
1506 -- NOTES
1507 --
1508 --
1509 -- HISTORY
1510 -- 12/1/1999 ptendulk Modified
1511 -- End of Comments
1512 PROCEDURE Check_Approval_Required(
1513 p_orig_stat IN NUMBER,
1514 p_new_stat IN NUMBER,
1515 p_stat_type IN VARCHAR2,
1516 p_obj_type IN VARCHAR2,
1517 p_obj_id IN NUMBER,
1518
1519 x_return_status OUT NOCOPY VARCHAR2,
1520 x_msg_data OUT NOCOPY VARCHAR2,
1521 x_appr_req_flag OUT NOCOPY VARCHAR2,
1522 x_appr_type OUT NOCOPY VARCHAR2 )
1523 IS
1524
1525 CURSOR c_sys_status(l_user_stat_id IN NUMBER)
1526 IS
1527 SELECT system_status_code
1528 FROM ams_user_statuses_vl
1529 WHERE user_status_id = l_user_stat_id ;
1530
1531 CURSOR c_stat_rule(l_sys_stat_type IN VARCHAR2,
1532 l_curr_stat IN VARCHAR2,
1533 l_next_stat IN VARCHAR2)
1534 IS
1535 SELECT theme_approval_flag,
1536 budget_approval_flag
1537 FROM ams_status_order_rules
1538 WHERE system_status_type = l_sys_stat_type
1539 AND current_status_code = l_curr_stat
1540 AND next_status_code = l_next_stat ;
1541
1542 l_stat_rec c_stat_rule%ROWTYPE;
1543
1544 CURSOR c_attr_det
1545 IS
1546 SELECT attribute_defined_flag,
1547 object_attribute
1548 FROM ams_object_attributes
1549 WHERE object_type = p_obj_type
1550 AND object_id = p_obj_id
1551 AND (object_attribute = 'TAPL' OR
1552 object_attribute = 'BAPL')
1553 AND attribute_defined_flag = 'N' ;
1554
1555 CURSOR c_appr_rule(l_appr_type VARCHAR2)
1556 IS
1557 SELECT mgr_approval_needed_flag ,
1558 parent_owner_approval_flag
1559 FROM ams_approval_rules
1560 WHERE arc_approval_for_object = p_obj_type
1561 AND approval_type = l_appr_type ;
1562
1563 l_attr_rec c_attr_det%ROWTYPE;
1564
1565 l_cnt NUMBER := 0 ;
1566 l_ta_flag VARCHAR2(1);
1567 l_ba_flag VARCHAR2(1);
1568
1569 l_orig_stat_code VARCHAR2(30);
1570 l_new_stat_code VARCHAR2(30);
1571
1572 l_own_appr_flag VARCHAR2(1) ;
1573 l_mgr_appr_flag VARCHAR2(1) ;
1574 BEGIN
1575 -- dbms_output.put_line('In/ Rec : Stat_type :'||p_stat_type||' Orig_stat: '||p_orig_stat||' New Stat: '||p_new_stat) ;
1576 --
1577 -- Initialize the API Return Status to Success
1578 --
1579 x_return_status := FND_API.G_RET_STS_SUCCESS;
1580
1581 --
1582 -- Take the System Statuses for the User Statuses
1583 --
1584 OPEN c_sys_status(p_orig_stat) ;
1585 FETCH c_sys_status INTO l_orig_stat_code ;
1586 CLOSE c_sys_status ;
1587
1588 OPEN c_sys_status(p_new_stat) ;
1589 FETCH c_sys_status INTO l_new_stat_code ;
1590 CLOSE c_sys_status ;
1591
1592 --
1593 -- Check in Status Order rule whether Any Approval is Required for the Status Change
1594 --
1595 OPEN c_stat_rule(p_stat_type,l_orig_stat_code,l_new_stat_code);
1596 FETCH c_stat_rule INTO l_stat_rec;
1597 IF c_stat_rule%NOTFOUND THEN
1598 -- Invalid Statuses
1599 --dbms_output.put_line('trigger_name is missing');
1600 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_NTF_INVALID_STAT_CODE');
1601 x_msg_data := FND_MESSAGE.Get;
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 -- If any errors happen abort API/Procedure.
1604 RETURN;
1605 END IF ;
1606 CLOSE c_stat_rule ;
1607
1608 -- dbms_output.put_line('Flags in Approval Rules Theme :'||l_stat_rec.theme_approval_flag||' Budget: '|| l_stat_rec.budget_approval_flag);
1609 IF l_stat_rec.theme_approval_flag = 'N'
1610 AND l_stat_rec.budget_approval_flag = 'N' THEN
1611 x_appr_req_flag := 'N' ;
1612 RETURN;
1613 END IF;
1614
1615 --
1616 -- Initialize the Flags
1617 --
1618 l_ta_flag := 'N';
1619 l_ba_flag := 'N';
1620
1621 --
1622 -- Now Chack Which Approval are required
1623 --
1624 OPEN c_attr_det;
1625 LOOP
1626 FETCH c_attr_det INTO l_attr_rec ;
1627 EXIT WHEN c_attr_det%NOTFOUND ;
1628 IF l_attr_rec.object_attribute = 'TAPL' THEN
1629 IF l_attr_rec.attribute_defined_flag = 'N'
1630 AND l_stat_rec.theme_approval_flag = 'Y'
1631 THEN
1632 l_ta_flag := 'Y' ;
1633 END IF;
1634 l_cnt := l_cnt + 1 ;
1635 ELSIF l_attr_rec.object_attribute = 'BAPL' THEN
1636 IF l_attr_rec.attribute_defined_flag = 'N'
1637 AND l_stat_rec.budget_approval_flag = 'Y'
1638 THEN
1639 l_ba_flag := 'Y' ;
1640 END IF;
1641 l_cnt := l_cnt + 1 ;
1642 END IF;
1643 END LOOP;
1644 CLOSE c_attr_det;
1645
1646 IF l_ba_flag = 'N' AND l_ta_flag = 'N' THEN
1647 x_appr_req_flag := 'N' ;
1648 ELSIF l_ba_flag = 'Y' AND l_ta_flag = 'N' THEN
1649 x_appr_type := 'BUDGET' ;
1650
1651 --Following Code is Added by PTENDULK on 28th Jan
1652 -- If the Flags in AMS_APPROVAL_RULES are N we
1653 -- Don't need Approvals
1654 OPEN c_appr_rule(x_appr_type) ;
1655 FETCH c_appr_rule INTO l_mgr_appr_flag,l_own_appr_flag ;
1656 CLOSE c_appr_rule ;
1657
1658 IF l_mgr_appr_flag = 'N' AND l_mgr_appr_flag = 'N' THEN
1659 x_appr_req_flag := 'N' ;
1660 ELSE
1661 x_appr_req_flag := 'Y' ;
1662 END IF ;
1663 ELSIF l_ba_flag = 'N' AND l_ta_flag = 'Y' THEN
1664 x_appr_type := 'THEME' ;
1665 --Following Code is Added by PTENDULK on 28th Jan
1666 -- If the Flags in AMS_APPROVAL_RULES are N we
1667 -- Don't need Approvals
1668 OPEN c_appr_rule(x_appr_type) ;
1669 FETCH c_appr_rule INTO l_mgr_appr_flag,l_own_appr_flag ;
1670 CLOSE c_appr_rule ;
1671
1672 IF l_mgr_appr_flag = 'N' AND l_mgr_appr_flag = 'N' THEN
1673 x_appr_req_flag := 'N' ;
1674 ELSE
1675 x_appr_req_flag := 'Y' ;
1676 END IF ;
1677 ELSIF l_ba_flag = 'Y' AND l_ta_flag = 'Y' THEN
1678 x_appr_type := 'BOTH' ;
1679 --Following Code is Added by PTENDULK on 28th Jan
1680 -- If the Flags in AMS_APPROVAL_RULES are N we
1681 -- Don't need Approvals
1682 OPEN c_appr_rule(x_appr_type) ;
1683 FETCH c_appr_rule INTO l_mgr_appr_flag,l_own_appr_flag ;
1684 CLOSE c_appr_rule ;
1685
1686 IF l_mgr_appr_flag = 'N' AND l_mgr_appr_flag = 'N' THEN
1687 x_appr_req_flag := 'N' ;
1688 ELSE
1689 x_appr_req_flag := 'Y' ;
1690 END IF ;
1691 END IF;
1692
1693 END Check_Approval_Required;
1694
1695 -- Start of Comments
1696 --
1697 -- NAME
1698 -- set_activity_details
1699 --
1700 -- PURPOSE
1701 -- This Procedure will set the workflow attributes for the details of the activity
1702 -- These Attributes will be used throughout the process espacially in Notifications
1703 -- It will also check if appropriate Approvers are availables for the approvals seeked
1704 --
1705 --
1706 -- It will Return - Success if the process is successful
1707 -- - Error If the process is errored out
1708 --
1709 -- IN
1710 -- Itemtype - AMSAPPR
1711 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
1712 -- Accid - Activity ID
1713 -- Funmode - Run/Cancel/Timeout
1714 --
1715 -- OUT
1716 -- Result - 'COMPLETE:AMS_SUCCESS' If the Process is successful
1717 -- - 'COMPLETE:AMS_ERROR' If the Process is errored out
1718 --
1719 -- Used By Activities
1720 -- Item Type - AMSAPPR
1721 -- Activity - AMS_SET_ACT_DETAILS
1722 --
1723 -- NOTES
1724 -- Various Combinations For Approvals (Requester/Owner/Manager/Fund Manager)
1725 -- BOTH APPROVAL
1726 -- 1. All are Same ((Req. = Own = Manager = Fm)
1727 -- 2. (Req. = Own = Fm) AND Manager is Different
1728 -- 3. (Req. = Own ) AND (Manager = Fm)
1729 -- 4. (Req. = Own ) AND (Manager <> Fm)
1730 -- 5. (Req = FM ) AND (Man = Own)
1731 -- 6. (Req = FM ) AND (Man <> Own)
1732 -- 7. (Own = Man = Fm) AND Req. is Diff.
1733 -- 8. (Own = Man <> FM) AND Req. is Diff.
1734 -- 9. (Own = FM <> Man ) AND Req. is Diff.
1735 -- 10.(Man = FM ) AND req. and Own Diff
1736 -- 11.(Man<>FM<>Own<>Req)
1737 -- BUDGET APPPROVAL
1738 -- 12.(Own = FM = REQ )
1739 -- 13.(Req = Own <> FM )
1740 -- 14.(Req = FM <> Own)
1741 -- 15.(Own = FM <> Req)
1742 -- 16.(Own <> FM <> Req)
1743 -- Theme Approvals
1744 -- 17.(Req = Own = Man)
1745 -- 18.(Req = Own <> Man)
1746 -- 19.(Req <> Own = Man)
1747 -- 20.(Req <> Own <> Man)
1748 -- HISTORY
1749 -- 08/20/1999 ptendulk created
1750 -- 11/30/1999 ptendulk Modified
1751 -- End of Comments
1752
1753 PROCEDURE Set_Activity_Details(itemtype IN VARCHAR2,
1754 itemkey IN VARCHAR2,
1755 actid IN NUMBER,
1756 funcmode IN VARCHAR2,
1757 result OUT NOCOPY VARCHAR2) IS
1758
1759 l_approval_for_object VARCHAR2(30);
1760 l_approval_for_objectid NUMBER;
1761 l_appr_req_flag VARCHAR2(1);
1762 l_requester VARCHAR2(100);
1763 l_requester_id NUMBER ;
1764 l_owner VARCHAR2(100);
1765 l_manager VARCHAR2(100);
1766 l_fund_manager VARCHAR2(100);
1767 l_stat_type VARCHAR2(30);
1768 l_orig_stat_id NUMBER;
1769 l_new_stat_id NUMBER;
1770 l_appr_type VARCHAR2(30);
1771 l_appr_flag VARCHAR2(1) ;
1772
1773 l_err_msg VARCHAR2(2000);
1774 l_return_status VARCHAR2(1);
1775
1776 tmp VARCHAR2(1);
1777
1778 CURSOR c_appr_check(l_appr_type VARCHAR) IS
1779 SELECT mgr_approval_needed_flag ,
1780 parent_owner_approval_flag
1781 FROM ams_approval_rules
1782 WHERE arc_approval_for_object = l_approval_for_object
1783 AND approval_type = l_appr_type ;
1784
1785 l_own_appr_flag VARCHAR2(1);
1786 l_man_appr_flag VARCHAR2(1);
1787
1788
1789 BEGIN
1790 IF (AMS_DEBUG_HIGH_ON) THEN
1791
1792 AMS_Utility_PVT.debug_message('Process Set_Activity_Details');
1793 END IF;
1794 -- dbms_output.put_line('Process Set_Activity_Details');
1795
1796 -- RUN mode - Normal Process Execution
1797 IF (funcmode = 'RUN')
1798 THEN
1799 l_approval_for_object := WF_ENGINE.GetItemAttrText(
1800 itemtype => itemtype,
1801 itemkey => itemkey ,
1802 aname => 'AMS_APPROVAL_FOR_OBJECT');
1803
1804 l_approval_for_objectid := WF_ENGINE.GetItemAttrText(
1805 itemtype => itemtype,
1806 itemkey => itemkey ,
1807 aname => 'AMS_ACT_ID');
1808
1809 l_orig_stat_id := WF_ENGINE.GetItemAttrText(
1810 itemtype => itemtype,
1811 itemkey => itemkey ,
1812 aname => 'AMS_ORIG_STAT_ID');
1813
1814 l_new_stat_id := WF_ENGINE.GetItemAttrText(
1815 itemtype => itemtype,
1816 itemkey => itemkey ,
1817 aname => 'AMS_NEW_STAT_ID');
1818
1819 --
1820 -- call the Function to get the Status order type for each Activity
1821 --
1822 l_stat_type := Check_Status_Order_Type(l_approval_for_object);
1823
1824
1825 Check_Approval_Required(
1826 p_orig_stat => l_orig_stat_id,
1827 p_new_stat => l_new_stat_id,
1828 p_stat_type => l_stat_type,
1829 p_obj_type => l_approval_for_object,
1830 p_obj_id => l_approval_for_objectid,
1831
1832 x_return_status => l_return_status,
1833 x_msg_data => l_err_msg,
1834 x_appr_req_flag => l_appr_req_flag ,
1835 x_appr_type => l_appr_type);
1836
1837 IF (AMS_DEBUG_HIGH_ON) THEN
1838
1839
1840
1841 AMS_Utility_PVT.debug_message('Approval Required : '||l_appr_type);
1842
1843 END IF;
1844 -- dbms_output.put_line('Approval Required Ret Stat : '||l_return_status);
1845 -- dbms_output.put_line('Approval Type : '||l_appr_type);
1846 -- dbms_output.put_line('Approval Required : '||l_appr_req_flag);
1847 IF l_return_status <> FND_API.G_ret_sts_success THEN
1848 -- Approval process is errored out
1849 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1850 itemkey => itemkey,
1851 aname => 'AMS_ERROR_MSG',
1852 avalue => l_err_msg);
1853
1854 result := 'COMPLETE:ERROR' ;
1855 ELSE
1856 IF l_appr_req_flag = 'N' THEN
1857 -- No Need of approval
1858 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1859 itemkey => itemkey,
1860 aname => 'AMS_APPR_REQ_CHECK',
1861 avalue => 'N' );
1862
1863 result := 'COMPLETE:SUCCESS' ;
1864 ELSE
1865
1866 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1867 itemkey => itemkey,
1868 aname => 'AMS_APPR_TYPE_LOOKUP',
1869 avalue => l_appr_type );
1870
1871 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1872 itemkey => itemkey,
1873 aname => 'AMS_APPR_REQ_CHECK',
1874 avalue => 'Y' );
1875
1876
1877 l_requester := WF_ENGINE.GetItemAttrText(
1878 itemtype => itemtype,
1879 itemkey => itemkey ,
1880 aname => 'AMS_REQUESTER');
1881
1882 l_requester_id := WF_ENGINE.GetItemAttrText(
1883 itemtype => itemtype,
1884 itemkey => itemkey ,
1885 aname => 'AMS_REQUESTER_ID');
1886
1887 -- Find Manager
1888 Find_Manager
1889 (p_user_id => l_requester_id,
1890 x_manager_role => l_manager );
1891
1892 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
1893 itemkey => itemkey,
1894 aname => 'AMS_MANAGER',
1895 avalue => l_manager );
1896 IF (AMS_DEBUG_HIGH_ON) THEN
1897
1898 AMS_Utility_PVT.debug_message('Manager : '||l_Manager);
1899 END IF;
1900
1901 --
1902 -- Find the Owner
1903 --
1904 Find_Owner
1905 (p_activity_id => l_approval_for_objectid,
1906 p_activity_type => l_approval_for_object,
1907 x_owner_role => l_owner ) ;
1908
1909 IF (AMS_DEBUG_HIGH_ON) THEN
1910
1911
1912
1913 AMS_Utility_PVT.debug_message('Owner : '||l_owner);
1914
1915 END IF;
1916 -- dbms_output.put_line('Owner : '||l_owner);
1917 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1918 itemkey => itemkey,
1919 aname => 'AMS_OWNER',
1920 avalue => l_owner );
1921 --
1922 -- Find the Fund Manager
1923 --
1924 Find_Fund_Manager
1925 ( p_activity_type => l_approval_for_object,
1926 p_activity_id => l_approval_for_objectid,
1927 x_manager_role => l_fund_manager
1928 ) ;
1929
1930
1931 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1932 itemkey => itemkey,
1933 aname => 'AMS_BUD_MANAGER',
1934 avalue => l_fund_manager );
1935 -- dbms_output.put_line('Fund Manager : '||l_owner);
1936
1937 IF l_appr_type = 'BOTH' THEN
1938 -- Check if all of the Approvers found
1939 IF (l_manager IS NULL OR
1940 l_owner IS NULL OR
1941 l_fund_manager IS NULL)
1942 THEN
1943 IF l_manager IS NULL THEN
1944 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_MANAGER');
1945 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
1946 l_err_msg := FND_MESSAGE.Get;
1947 ELSIF l_owner IS NULL THEN
1948 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_OWNER');
1949 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
1950 l_err_msg := FND_MESSAGE.Get;
1951 ELSIF l_fund_manager IS NULL THEN
1952 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_FUND_MANAGER');
1953 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
1954 l_err_msg := FND_MESSAGE.Get;
1955 END IF ;
1956
1957 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1958 itemkey => itemkey,
1959 aname => 'AMS_ERROR_MSG',
1960 avalue => l_err_msg);
1961
1962 result := 'COMPLETE:ERROR' ;
1963 ELSE
1964
1965 -- Now Check Which Aprovals are required .
1966 -- Check the Flags in AMS_APPROVAL_RULES
1967 -- Whether all Approvals are required.
1968 OPEN c_appr_check('BOTH') ;
1969 FETCH c_appr_check INTO l_man_appr_flag,l_own_appr_flag ;
1970 CLOSE c_appr_check ;
1971
1972 IF l_man_appr_flag = 'N' THEN
1973 l_manager := l_owner ;
1974 ELSIF l_own_appr_flag = 'N' THEN
1975 l_owner := l_manager ;
1976 END IF;
1977
1978 -- Check if all the Approvers are same (Condi: 1)
1979 IF l_requester = l_manager AND
1980 l_requester = l_owner AND
1981 l_requester = l_fund_manager
1982 THEN
1983 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1984 itemkey => itemkey,
1985 aname => 'AMS_APPR_REQ_CHECK',
1986 avalue => 'N' );
1987
1988 result := 'COMPLETE:SUCCESS' ;
1989 ELSE
1990 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
1991 itemkey => itemkey,
1992 aname => 'AMS_APPR_USERNAME',
1993 avalue => l_manager);
1994
1995 IF l_requester = l_owner OR
1996 l_requester = l_fund_manager
1997 THEN
1998 --(Condi: 2)
1999 IF (l_requester = l_owner AND
2000 l_requester = l_fund_manager)
2001 THEN --(req = Own = Fun_Man) <> Man
2002 -- Theme Approval Require Budget Approval Not Require
2003 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2004 itemkey => itemkey,
2005 aname => 'AMS_BUDGET_APPR_FLAG',
2006 avalue => 'N');
2007 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2008 itemkey => itemkey,
2009 aname => 'AMS_TAOWNER_APPR_FLAG',
2010 avalue => 'N');
2011 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2012 itemkey => itemkey,
2013 aname => 'AMS_BAOWNER_APPR_FLAG',
2014 avalue => 'N');
2015 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2016 itemkey => itemkey,
2017 aname => 'AMS_THEME_APPR_FLAG',
2018 avalue => 'Y');
2019 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2020 itemkey => itemkey,
2021 aname => 'AMS_APPR_DOC_TYPE',
2022 avalue => 'BOTH');
2023
2024 ELSIF (l_requester = l_owner AND
2025 l_requester <> l_fund_manager)
2026 THEN --(req = Own <> Fun_Man) <> Man
2027 IF l_fund_manager = l_manager THEN --(Condi: 3)
2028 -- Theme Approval Require Budget Approval Not Require
2029 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2030 itemkey => itemkey,
2031 aname => 'AMS_BUDGET_APPR_FLAG',
2032 avalue => 'N');
2033 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2034 itemkey => itemkey,
2035 aname => 'AMS_TAOWNER_APPR_FLAG',
2036 avalue => 'N');
2037 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2038 itemkey => itemkey,
2039 aname => 'AMS_BAOWNER_APPR_FLAG',
2040 avalue => 'N');
2041 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2042 itemkey => itemkey,
2043 aname => 'AMS_THEME_APPR_FLAG',
2044 avalue => 'Y');
2045 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2046 itemkey => itemkey,
2047 aname => 'AMS_APPR_DOC_TYPE',
2048 avalue => 'BOTH');
2049 ELSE --(Condi: 4)
2050 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2051 itemkey => itemkey,
2052 aname => 'AMS_BUDGET_APPR_FLAG',
2053 avalue => 'Y');
2054 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2055 itemkey => itemkey,
2056 aname => 'AMS_TAOWNER_APPR_FLAG',
2057 avalue => 'N');
2058 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2059 itemkey => itemkey,
2060 aname => 'AMS_BAOWNER_APPR_FLAG',
2061 avalue => 'N');
2062 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2063 itemkey => itemkey,
2064 aname => 'AMS_THEME_APPR_FLAG',
2065 avalue => 'Y');
2066 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2067 itemkey => itemkey,
2068 aname => 'AMS_APPR_DOC_TYPE',
2069 avalue => 'BOTH');-- Changed on
2070 -- 29Jan from THEME by PTENDULK as Manager will receive Notification for Both Approvals
2071 END IF;
2072 ELSE
2073 --(l_requester <> l_owner AND l_requester = l_fund_manager)
2074 IF l_owner = l_manager THEN --(Condi: 5)
2075 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2076 itemkey => itemkey,
2077 aname => 'AMS_BUDGET_APPR_FLAG',
2078 avalue => 'N');
2079 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2080 itemkey => itemkey,
2081 aname => 'AMS_TAOWNER_APPR_FLAG',
2082 avalue => 'N');
2083 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2084 itemkey => itemkey,
2085 aname => 'AMS_BAOWNER_APPR_FLAG',
2086 avalue => 'N');
2087 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2088 itemkey => itemkey,
2089 aname => 'AMS_THEME_APPR_FLAG',
2090 avalue => 'Y');
2091 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2092 itemkey => itemkey,
2093 aname => 'AMS_APPR_DOC_TYPE',
2094 avalue => 'BOTH');-- Changed on
2095 -- 29Jan from THEME by PTENDULK as Manager will receive Notification for Both Approvals
2096 ELSE --(Condi: 6)
2097 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2098 itemkey => itemkey,
2099 aname => 'AMS_BUDGET_APPR_FLAG',
2100 avalue => 'N');
2101 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2102 itemkey => itemkey,
2103 aname => 'AMS_TAOWNER_APPR_FLAG',
2104 avalue => 'Y');
2105 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2106 itemkey => itemkey,
2107 aname => 'AMS_BAOWNER_APPR_FLAG',
2108 avalue => 'N');
2109 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2110 itemkey => itemkey,
2111 aname => 'AMS_THEME_APPR_FLAG',
2112 avalue => 'Y');
2113 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2114 itemkey => itemkey,
2115 aname => 'AMS_APPR_DOC_TYPE',
2116 avalue => 'BOTH'); -- Changed on
2117 -- 29Jan from THEME by PTENDULK as Manager will receive Notification for Both Approvals
2118 END IF;
2119
2120 END IF;
2121 ELSE -- (Req in not any of the Approvers)
2122 IF (l_owner = l_manager OR
2123 l_owner = l_fund_manager)
2124 THEN
2125 IF (l_owner = l_manager AND
2126 l_manager = l_fund_manager)
2127 THEN --(Condi: 7)
2128 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2129 itemkey => itemkey,
2130 aname => 'AMS_BUDGET_APPR_FLAG',
2131 avalue => 'N');
2132 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2133 itemkey => itemkey,
2134 aname => 'AMS_TAOWNER_APPR_FLAG',
2135 avalue => 'N');
2136 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2137 itemkey => itemkey,
2138 aname => 'AMS_BAOWNER_APPR_FLAG',
2139 avalue => 'N');
2140 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2141 itemkey => itemkey,
2142 aname => 'AMS_THEME_APPR_FLAG',
2143 avalue => 'Y');
2144 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2145 itemkey => itemkey,
2146 aname => 'AMS_APPR_DOC_TYPE',
2147 avalue => 'BOTH');
2148
2149 ELSIF (l_owner = l_manager AND
2150 l_owner <> l_fund_manager)
2151 THEN --(Condi: 8)
2152 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2153 itemkey => itemkey,
2154 aname => 'AMS_BUDGET_APPR_FLAG',
2155 avalue => 'Y');
2156 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2157 itemkey => itemkey,
2158 aname => 'AMS_TAOWNER_APPR_FLAG',
2159 avalue => 'N');
2160 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2161 itemkey => itemkey,
2162 aname => 'AMS_BAOWNER_APPR_FLAG',
2163 avalue => 'N');
2164 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2165 itemkey => itemkey,
2166 aname => 'AMS_THEME_APPR_FLAG',
2167 avalue => 'Y');
2168 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2169 itemkey => itemkey,
2170 aname => 'AMS_APPR_DOC_TYPE',
2171 avalue => 'BOTH');
2172
2173 ELSE --(Condi: 9)
2174 -- (l_owner <> l_manager AND l_owner = l_fund_manager)
2175 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2176 itemkey => itemkey,
2177 aname => 'AMS_BUDGET_APPR_FLAG',
2178 avalue => 'N');
2179 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2180 itemkey => itemkey,
2181 aname => 'AMS_TAOWNER_APPR_FLAG',
2182 avalue => 'Y');
2183 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2184 itemkey => itemkey,
2185 aname => 'AMS_BAOWNER_APPR_FLAG',
2186 avalue => 'N');
2187 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2188 itemkey => itemkey,
2189 aname => 'AMS_THEME_APPR_FLAG',
2190 avalue => 'Y');
2191 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2192 itemkey => itemkey,
2193 aname => 'AMS_APPR_DOC_TYPE',
2194 avalue => 'BOTH');
2195 END IF;
2196 ELSE
2197 IF l_manager = l_fund_manager
2198 THEN --(Condi: 10)
2199 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2200 itemkey => itemkey,
2201 aname => 'AMS_BUDGET_APPR_FLAG',
2202 avalue => 'N');
2203 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2204 itemkey => itemkey,
2205 aname => 'AMS_TAOWNER_APPR_FLAG',
2206 avalue => 'Y');
2207 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2208 itemkey => itemkey,
2209 aname => 'AMS_BAOWNER_APPR_FLAG',
2210 avalue => 'N');
2211 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2212 itemkey => itemkey,
2213 aname => 'AMS_THEME_APPR_FLAG',
2214 avalue => 'Y');
2215 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2216 itemkey => itemkey,
2217 aname => 'AMS_APPR_DOC_TYPE',
2218 avalue => 'BOTH');
2219 ELSE -- (requester<> owner<> manager<> fund_manager) (Condi 11)
2220 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2221 itemkey => itemkey,
2222 aname => 'AMS_BUDGET_APPR_FLAG',
2223 avalue => 'Y');
2224 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2225 itemkey => itemkey,
2226 aname => 'AMS_TAOWNER_APPR_FLAG',
2227 avalue => 'Y');
2228 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2229 itemkey => itemkey,
2230 aname => 'AMS_BAOWNER_APPR_FLAG',
2231 avalue => 'N');
2232 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2233 itemkey => itemkey,
2234 aname => 'AMS_THEME_APPR_FLAG',
2235 avalue => 'Y');
2236 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2237 itemkey => itemkey,
2238 aname => 'AMS_APPR_DOC_TYPE',
2239 avalue => 'BOTH');
2240 END IF ;
2241 END IF;
2242 END IF;
2243 result := 'COMPLETE:SUCCESS' ;
2244 END IF;
2245 END IF ;
2246
2247
2248 ELSIF l_appr_type = 'BUDGET' THEN
2249 -- dbms_output.put_line('In Budget Fund Man : '||l_fund_manager);
2250 -- dbms_output.put_line('In Budget Owner : '||l_owner);
2251
2252 -- In Funds Approval Approval is required of Manager and
2253 -- Fund Manager so Replace Owner with Manager
2254 l_owner := l_manager ;
2255
2256 -- Check if all of the Approvers found
2257 IF (l_fund_manager IS NULL OR
2258 l_owner IS NULL )
2259 THEN
2260 IF l_fund_manager IS NULL THEN
2261 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_FUND_MANAGER');
2262 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
2263 ELSE
2264 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_OWNER');
2265 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
2266 END IF;
2267 l_err_msg := FND_MESSAGE.Get;
2268
2269 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2270 itemkey => itemkey,
2271 aname => 'AMS_ERROR_MSG',
2272 avalue => l_err_msg);
2273
2274 result := 'COMPLETE:ERROR' ;
2275 ELSE
2276 -- Check if all the Approvers are same
2277 IF l_requester = l_owner AND
2278 l_requester = l_fund_manager
2279 THEN -- Condi : 12
2280 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2281 itemkey => itemkey,
2282 aname => 'AMS_APPR_REQ_CHECK',
2283 avalue => 'N' );
2284 -- dbms_output.put_line('All Appr Same : '||l_fund_manager);
2285
2286 result := 'COMPLETE:SUCCESS' ;
2287 ELSE
2288 -- Now Check Which Aprovals are required .
2289 -- Check the Flags in AMS_APPROVAL_RULES
2290 -- Whether all Approvals are required.
2291 OPEN c_appr_check('BUDGET') ;
2292 FETCH c_appr_check INTO l_man_appr_flag,l_own_appr_flag ;
2293 CLOSE c_appr_check ;
2294
2295 IF l_man_appr_flag = 'N' THEN
2296 l_fund_manager := l_owner ;
2297 ELSIF l_own_appr_flag = 'N' THEN
2298 l_owner := l_fund_manager ;
2299 END IF;
2300
2301 IF (l_requester = l_owner ) AND (l_requester <> l_fund_manager )
2302 THEN -- Condi : 13
2303 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2304 itemkey => itemkey,
2305 aname => 'AMS_APPR_USERNAME',
2306 avalue => l_fund_manager);
2307
2308 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2309 itemkey => itemkey,
2310 aname => 'AMS_BUDGET_APPR_FLAG',
2311 avalue => 'Y');
2312 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2313 itemkey => itemkey,
2314 aname => 'AMS_TAOWNER_APPR_FLAG',
2315 avalue => 'N');
2316 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2317 itemkey => itemkey,
2318 aname => 'AMS_BAOWNER_APPR_FLAG',
2319 avalue => 'N');
2320 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2321 itemkey => itemkey,
2322 aname => 'AMS_THEME_APPR_FLAG',
2323 avalue => 'N');
2324 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2325 itemkey => itemkey,
2326 aname => 'AMS_APPR_DOC_TYPE',
2327 avalue => 'BUDGET');
2328 ELSIF (l_requester = l_fund_manager) AND (l_requester <> l_owner )
2329 THEN -- Condi : 14
2330 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2331 itemkey => itemkey,
2332 aname => 'AMS_APPR_USERNAME',
2333 avalue => l_owner);
2334
2335 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2336 itemkey => itemkey,
2337 aname => 'AMS_BUDGET_APPR_FLAG',
2338 avalue => 'Y');
2339 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2340 itemkey => itemkey,
2341 aname => 'AMS_TAOWNER_APPR_FLAG',
2342 avalue => 'N');
2343 -- Here Budget Approval is not required from Owner as it is already taken (Requester = Fund Manager)
2344 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2345 itemkey => itemkey,
2346 aname => 'AMS_BAOWNER_APPR_FLAG',
2347 avalue => 'N');
2348 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2349 itemkey => itemkey,
2350 aname => 'AMS_THEME_APPR_FLAG',
2351 avalue => 'N');
2352 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2353 itemkey => itemkey,
2354 aname => 'AMS_APPR_DOC_TYPE',
2355 avalue => 'BUDGET');
2356 ELSE
2357 -- dbms_output.put_line('All Appr No Same : '||l_fund_manager);
2358 --(l_requester <> l_fund_manager) AND (l_requester <> l_owner)
2359 IF l_owner = l_fund_manager THEN -- Condi : 15
2360 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2361 itemkey => itemkey,
2362 aname => 'AMS_APPR_USERNAME',
2363 avalue => l_owner);
2364
2365 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2366 itemkey => itemkey,
2367 aname => 'AMS_BUDGET_APPR_FLAG',
2368 avalue => 'Y');
2369 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2370 itemkey => itemkey,
2371 aname => 'AMS_TAOWNER_APPR_FLAG',
2372 avalue => 'N');
2373 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2374 itemkey => itemkey,
2375 aname => 'AMS_BAOWNER_APPR_FLAG',
2376 avalue => 'N');
2377 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2378 itemkey => itemkey,
2379 aname => 'AMS_THEME_APPR_FLAG',
2380 avalue => 'N');
2381 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2382 itemkey => itemkey,
2383 aname => 'AMS_APPR_DOC_TYPE',
2384 avalue => 'BUDGET');
2385 ELSE -- Condi : 16
2386 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2387 itemkey => itemkey,
2388 aname => 'AMS_APPR_USERNAME',
2389 avalue => l_fund_manager);
2390
2391 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2392 itemkey => itemkey,
2393 aname => 'AMS_BUDGET_APPR_FLAG',
2394 avalue => 'Y');
2395 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2396 itemkey => itemkey,
2397 aname => 'AMS_TAOWNER_APPR_FLAG',
2398 avalue => 'N');
2399 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2400 itemkey => itemkey,
2401 aname => 'AMS_BAOWNER_APPR_FLAG',
2402 avalue => 'Y');
2403 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2404 itemkey => itemkey,
2405 aname => 'AMS_THEME_APPR_FLAG',
2406 avalue => 'N');
2407 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2408 itemkey => itemkey,
2409 aname => 'AMS_APPR_DOC_TYPE',
2410 avalue => 'BUDGET');
2411 END IF;
2412 END IF;
2413 result := 'COMPLETE:SUCCESS' ;
2414 END IF;
2415 END IF;
2416
2417 ELSIF l_appr_type = 'THEME' THEN
2418 -- Check if all of the Approvers found
2419 IF (l_manager IS NULL OR
2420 l_owner IS NULL )
2421 THEN
2422 IF l_manager IS NULL THEN
2423 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_MANAGER');
2424 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
2425 ELSE
2426 FND_MESSAGE.Set_Name('AMS', 'AMS_WF_APPR_NO_OWNER');
2427 FND_MESSAGE.Set_Token('OBJECT_TYPE',l_approval_for_object, FALSE);
2428 END IF;
2429 l_err_msg := FND_MESSAGE.Get;
2430
2431 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2432 itemkey => itemkey,
2433 aname => 'AMS_ERROR_MSG',
2434 avalue => l_err_msg);
2435
2436 result := 'COMPLETE:ERROR' ;
2437 ELSE
2438 -- Now Check Which Aprovals are required .
2439 -- Check the Flags in AMS_APPROVAL_RULES
2440 -- Whether all Approvals are required.
2441 OPEN c_appr_check('THEME') ;
2442 FETCH c_appr_check INTO l_man_appr_flag,l_own_appr_flag ;
2443 CLOSE c_appr_check ;
2444
2445 IF l_man_appr_flag = 'N' THEN
2446 l_manager := l_owner ;
2447 ELSIF l_own_appr_flag = 'N' THEN
2448 l_owner := l_manager ;
2449 END IF;
2450
2451 -- Check if all the Approvers are same
2452 IF l_requester = l_owner AND
2453 l_requester = l_manager
2454 THEN -- Condi : 17
2455 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2456 itemkey => itemkey,
2457 aname => 'AMS_APPR_REQ_CHECK',
2458 avalue => 'N' );
2459
2460 result := 'COMPLETE:SUCCESS' ;
2461
2462 -- dbms_output.put_line('Theme All Approvals same');
2463 ELSE
2464 IF (l_requester = l_owner )
2465 THEN -- Condi : 18
2466 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2467 itemkey => itemkey,
2468 aname => 'AMS_APPR_USERNAME',
2469 avalue => l_manager);
2470
2471 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2472 itemkey => itemkey,
2473 aname => 'AMS_BUDGET_APPR_FLAG',
2474 avalue => 'N');
2475 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2476 itemkey => itemkey,
2477 aname => 'AMS_TAOWNER_APPR_FLAG',
2478 avalue => 'N');
2479 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2480 itemkey => itemkey,
2481 aname => 'AMS_BAOWNER_APPR_FLAG',
2482 avalue => 'N');
2483 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2484 itemkey => itemkey,
2485 aname => 'AMS_THEME_APPR_FLAG',
2486 avalue => 'Y');
2487 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2488 itemkey => itemkey,
2489 aname => 'AMS_APPR_DOC_TYPE',
2490 avalue => 'THEME');
2491 ELSE --(l_requester <> l_owner)
2492 IF l_owner = l_fund_manager THEN -- Condi : 19
2493 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2494 itemkey => itemkey,
2495 aname => 'AMS_APPR_USERNAME',
2496 avalue => l_owner);
2497
2498 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2499 itemkey => itemkey,
2500 aname => 'AMS_BUDGET_APPR_FLAG',
2501 avalue => 'N');
2502 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2503 itemkey => itemkey,
2504 aname => 'AMS_TAOWNER_APPR_FLAG',
2505 avalue => 'N');
2506 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2507 itemkey => itemkey,
2508 aname => 'AMS_BAOWNER_APPR_FLAG',
2509 avalue => 'N');
2510 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2511 itemkey => itemkey,
2512 aname => 'AMS_THEME_APPR_FLAG',
2513 avalue => 'Y');
2514 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2515 itemkey => itemkey,
2516 aname => 'AMS_APPR_DOC_TYPE',
2517 avalue => 'THEME');
2518 ELSE -- (Condi 20)
2519 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2520 itemkey => itemkey,
2521 aname => 'AMS_APPR_USERNAME',
2522 avalue => l_manager);
2523
2524 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2525 itemkey => itemkey,
2526 aname => 'AMS_BUDGET_APPR_FLAG',
2527 avalue => 'N');
2528 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2529 itemkey => itemkey,
2530 aname => 'AMS_TAOWNER_APPR_FLAG',
2531 avalue => 'Y');
2532 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2533 itemkey => itemkey,
2534 aname => 'AMS_BAOWNER_APPR_FLAG',
2535 avalue => 'N');
2536 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2537 itemkey => itemkey,
2538 aname => 'AMS_THEME_APPR_FLAG',
2539 avalue => 'Y');
2540 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
2541 itemkey => itemkey,
2542 aname => 'AMS_APPR_DOC_TYPE',
2543 avalue => 'THEME');
2544 END IF;
2545 END IF;
2546 result := 'COMPLETE:SUCCESS' ;
2547 END IF;
2548 END IF;
2549
2550 END IF; -- For ELSIF l_appr_type = 'THEME' THEN
2551 END IF;
2552 END IF;
2553
2554 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Process Set_Activity_Details End : '||result); END IF;
2555 -- dbms_output.put_line('Process Set_Activity_Details End : '||result);
2556 END IF;
2557
2558 -- CANCEL mode - Normal Process Execution
2559 IF (funcmode = 'CANCEL')
2560 THEN
2561 result := 'COMPLETE:' ;
2562 RETURN;
2563 END IF;
2564
2565 -- TIMEOUT mode - Normal Process Execution
2566 IF (funcmode = 'TIMEOUT')
2567 THEN
2568 result := 'COMPLETE:' ;
2569 RETURN;
2570 END IF;
2571
2572 EXCEPTION
2573 WHEN OTHERS THEN
2574 wf_core.context('AMS_WFCMPAPR_PVT','Set_Activity_Details',itemtype,itemkey,actid,funcmode);
2575 raise ;
2576 END Set_Activity_Details ;
2577
2578 -- Start of Comments
2579 --
2580 -- NAME
2581 -- Appr_Required_Check
2582 --
2583 -- PURPOSE
2584 -- This Procedure will check whether the Approval is required or not
2585 --
2586 -- Itemtype - AMSAPPR
2587 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
2588 -- Accid - Activity ID
2589 -- Funmode - Run/Cancel/Timeout
2590 --
2591 -- OUT
2592 -- Result - 'COMPLETE:Y' If the approval is required
2593 -- - 'COMPLETE:N' If the approval is not required
2594 --
2595 -- Used By Activities
2596 -- Item Type - AMSAPPR
2597 -- Activity - AMS_CHECK_APPR
2598 --
2599 -- NOTES
2600 --
2601 --
2602 -- HISTORY
2603 -- 08/20/1999 ptendulk created
2604 -- 11/30/1999 ptendulk Modified
2605 -- End of Comments
2606
2607 PROCEDURE Appr_Required_Check (itemtype IN VARCHAR2,
2608 itemkey IN VARCHAR2,
2609 actid IN NUMBER,
2610 funcmode IN VARCHAR2,
2611 result OUT NOCOPY VARCHAR2) IS
2612 l_appr_flag VARCHAR2(1);
2613 BEGIN
2614 IF (AMS_DEBUG_HIGH_ON) THEN
2615
2616 AMS_Utility_PVT.debug_message('Process Appr_Required_Check');
2617 END IF;
2618 -- dbms_output.put_line('Process Appr_Required_Check');
2619 -- RUN mode - Normal Process Execution
2620 IF (funcmode = 'RUN')
2621 THEN
2622 l_appr_flag := WF_ENGINE.GetItemAttrText(
2623 itemtype => itemtype,
2624 itemkey => itemkey ,
2625 aname => 'AMS_APPR_REQ_CHECK');
2626 IF l_appr_flag = 'Y' THEN
2627 result := 'COMPLETE:Y' ;
2628 ELSE
2629 result := 'COMPLETE:N' ;
2630 END IF;
2631
2632 END IF;
2633
2634 -- CANCEL mode - Normal Process Execution
2635 IF (funcmode = 'CANCEL')
2636 THEN
2637 result := 'COMPLETE:' ;
2638 RETURN;
2639 END IF;
2640
2641 -- TIMEOUT mode - Normal Process Execution
2642 IF (funcmode = 'TIMEOUT')
2643 THEN
2644 result := 'COMPLETE:' ;
2645 RETURN;
2646 END IF;
2647 -- dbms_output.put_line('appr_req_check end: '||result);
2648 EXCEPTION
2649 WHEN OTHERS THEN
2650 wf_core.context(G_PKG_NAME,'Appr_Required_Check',itemtype,itemkey,actid,funcmode);
2651 raise ;
2652 END Appr_Required_Check ;
2653
2654
2655 -- Start of Comments
2656 --
2657 -- NAME
2658 -- Update_Status_Na
2659 --
2660 -- PURPOSE
2661 -- This Procedure will Update the Status of the Activity as the
2662 -- Approval is not required
2663 --
2664 -- IN
2665 -- Itemtype - AMSAPPR
2666 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
2667 -- Accid - Activity ID
2668 -- Funmode - Run/Cancel/Timeout
2669 --
2670 -- OUT
2671 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
2672 -- COMPLETE:AMS_ERROR If the Process is errored out.
2673 --
2674 -- Used By Activities
2675 -- Item Type - AMSAPPR
2676 -- Activity - AMS_UPDATE_STATUS_NA
2677 --
2678 -- NOTES
2679 --
2680 --
2681 -- HISTORY
2682 -- 08/20/1999 ptendulk created
2683 -- 11/30/1999 ptendulk Modified
2684 -- End of Comments
2685
2686 PROCEDURE UPDATE_STATUS_NA (itemtype IN VARCHAR2,
2687 itemkey IN VARCHAR2,
2688 actid IN NUMBER,
2689 funcmode IN VARCHAR2,
2690 result OUT NOCOPY VARCHAR2) IS
2691
2692 l_obj_type VARCHAR2(30);
2693 l_next_stat_id NUMBER ;
2694
2695 l_msg_count NUMBER ;
2696 l_msg_data VARCHAR2(2000);
2697 l_obj_id NUMBER ;
2698 l_obj_version_number NUMBER ;
2699 l_requester_id NUMBER ;
2700 l_sys_stat_code VARCHAR2(30);
2701 l_return_status VARCHAR2(1);
2702
2703 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
2704 SELECT system_status_code
2705 FROM ams_user_statuses_vl
2706 WHERE user_status_id = l_user_stat_id ;
2707
2708 BEGIN
2709 IF (AMS_DEBUG_HIGH_ON) THEN
2710
2711 AMS_Utility_PVT.debug_message('Process Approval_Type');
2712 END IF;
2713 -- dbms_output.put_line('Process Approval_Type');
2714 -- RUN mode - Normal Process Execution
2715 IF (funcmode = 'RUN')
2716 THEN
2717
2718 l_obj_type := wf_engine.GetItemAttrText(
2719 itemtype => ItemType,
2720 itemkey => ItemKey,
2721 aname => 'AMS_APPROVAL_FOR_OBJECT');
2722
2723 l_obj_id := wf_engine.GetItemAttrText(
2724 itemtype => ItemType,
2725 itemkey => ItemKey,
2726 aname => 'AMS_ACT_ID');
2727 l_obj_version_number := wf_engine.GetItemAttrText(
2728 itemtype => ItemType,
2729 itemkey => ItemKey,
2730 aname => 'AMS_OBJECT_VERSION_NUMBER');
2731 l_next_stat_id := wf_engine.GetItemAttrText(
2732 itemtype => ItemType,
2733 itemkey => ItemKey,
2734 aname => 'AMS_NEW_STAT_ID');
2735
2736 l_requester_id := wf_engine.GetItemAttrText(
2737 itemtype => ItemType,
2738 itemkey => ItemKey,
2739 aname => 'AMS_REQUESTER_ID');
2740
2741 OPEN c_sys_stat(l_next_stat_id) ;
2742 FETCH c_sys_stat INTO l_sys_stat_code ;
2743 CLOSE c_sys_stat ;
2744
2745 --
2746 -- Update Activity
2747 --
2748 Update_Status(p_obj_type => l_obj_type,
2749 p_obj_id => l_obj_id,
2750 p_object_version_number => l_obj_version_number,
2751 p_next_stat_code => l_sys_stat_code, --System Status
2752 p_next_stat_id => l_next_stat_id,
2753 p_submitted_by => l_requester_id,
2754 p_item_key => itemkey,
2755 x_msg_count => l_msg_count,
2756 x_msg_data => l_msg_data,
2757 x_return_status => l_return_status ) ;
2758
2759 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2760 result := 'COMPLETE:SUCCESS' ;
2761 ELSE
2762 Handle_Err
2763 (p_itemtype => itemtype ,
2764 p_itemkey => itemkey ,
2765 p_msg_count => l_msg_count, -- Number of error Messages
2766 p_msg_data => l_msg_data ,
2767 p_attr_name => 'AMS_ERROR_MSG'
2768 ) ;
2769 result := 'COMPLETE:ERROR' ;
2770 END IF ;
2771 END IF ;
2772
2773 -- CANCEL mode - Normal Process Execution
2774 IF (funcmode = 'CANCEL')
2775 THEN
2776 result := 'COMPLETE:' ;
2777 RETURN;
2778 END IF;
2779
2780 -- TIMEOUT mode - Normal Process Execution
2781 IF (funcmode = 'TIMEOUT')
2782 THEN
2783 result := 'COMPLETE:' ;
2784 RETURN;
2785 END IF;
2786 EXCEPTION
2787 WHEN OTHERS THEN
2788 wf_core.context(G_PKG_NAME,'Update_Status_NA',itemtype,itemkey,actid,funcmode);
2789 raise ;
2790 END Update_Status_NA ;
2791
2792 -- Start of Comments
2793 --
2794 -- NAME
2795 -- Theme_Appr_Req_Check
2796 --
2797 -- PURPOSE
2798 -- This Procedure will check whether the Theme Approval is required or not
2799 --
2800 -- Itemtype - AMSAPPR
2801 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
2802 -- Accid - Activity ID
2803 -- Funmode - Run/Cancel/Timeout
2804 --
2805 -- OUT
2806 -- Result - 'COMPLETE:Y' If the approval is required
2807 -- - 'COMPLETE:N' If the approval is not required
2808 --
2809 -- Used By Activities
2810 -- Item Type - AMSAPPR
2811 -- Activity - AMS_CHECK_MAN_APPR
2812 --
2813 -- NOTES
2814 --
2815 --
2816 -- HISTORY
2817 -- 08/20/1999 ptendulk created
2818 -- 11/30/1999 ptendulk Modified
2819 -- End of Comments
2820
2821 PROCEDURE Theme_Appr_Req_Check (itemtype IN VARCHAR2,
2822 itemkey IN VARCHAR2,
2823 actid IN NUMBER,
2824 funcmode IN VARCHAR2,
2825 result OUT NOCOPY VARCHAR2) IS
2826 l_theme_appr_flag VARCHAR2(1);
2827 BEGIN
2828 IF (AMS_DEBUG_HIGH_ON) THEN
2829
2830 AMS_Utility_PVT.debug_message('Process Fund_Appr_Req_Check');
2831 END IF;
2832 -- dbms_output.put_line('Process Fund_Appr_Req_Check');
2833 -- RUN mode - Normal Process Execution
2834 IF (funcmode = 'RUN')
2835 THEN
2836 l_theme_appr_flag := WF_ENGINE.GetItemAttrText(
2837 itemtype => itemtype,
2838 itemkey => itemkey ,
2839 aname => 'AMS_THEME_APPR_FLAG');
2840 IF l_theme_appr_flag = 'Y' THEN
2841 result := 'COMPLETE:Y' ;
2842 ELSE
2843 result := 'COMPLETE:N' ;
2844 END IF;
2845
2846 END IF;
2847
2848 -- CANCEL mode - Normal Process Execution
2849 IF (funcmode = 'CANCEL')
2850 THEN
2851 result := 'COMPLETE:' ;
2852 RETURN;
2853 END IF;
2854
2855 -- TIMEOUT mode - Normal Process Execution
2856 IF (funcmode = 'TIMEOUT')
2857 THEN
2858 result := 'COMPLETE:' ;
2859 RETURN;
2860 END IF;
2861 -- dbms_output.put_line('End Theme Appr Req Check');
2862 EXCEPTION
2863 WHEN OTHERS THEN
2864 wf_core.context(G_PKG_NAME,'Theme_Appr_Req_Check',itemtype,itemkey,actid,funcmode);
2865 raise ;
2866 END Theme_Appr_Req_Check ;
2867
2868 -- Start of Comments
2869 --
2870 -- NAME
2871 -- Find_Priority
2872 --
2873 -- PURPOSE
2874 -- This Procedure Calculates Priority and Timeout Days for the given activity and the given
2875 -- approval type
2876 --
2877 -- CALLED BY
2878 -- Prepare_Doc
2879 --
2880 -- NOTES
2881 --
2882 --
2883 -- HISTORY
2884 -- 09/14/1999 ptendulk created
2885 -- 11/30/1999 ptendulk Modified
2886 -- End of Comments
2887
2888
2889 PROCEDURE Find_Priority(p_obj_type IN VARCHAR2,
2890 p_obj_id IN NUMBER,
2891 p_approval_type IN VARCHAR2,
2892 x_timeout_days OUT NOCOPY NUMBER,
2893 x_priority OUT NOCOPY NUMBER)
2894 IS
2895 CURSOR c_timeout is
2896 SELECT timeout_days_low_prio,
2897 timeout_days_std_prio,
2898 timeout_days_high_prio,
2899 timeout_days_medium_prio
2900 FROM ams_approval_rules
2901 WHERE arc_approval_for_object = p_obj_type
2902 AND DECODE(approval_type,'BOTH','BUDGET',approval_type) = p_approval_type ;
2903 -- If the Approval type is Both then Use Priority and Timeout of Budget
2904
2905 CURSOR c_camp_priority
2906 IS
2907 SELECT priority
2908 FROM ams_campaigns_vl
2909 WHERE campaign_id = p_obj_id ;
2910
2911 CURSOR c_eveh_priority
2912 IS
2913 SELECT priority_type_code priority
2914 FROM ams_event_headers_vl
2915 WHERE event_header_id = p_obj_id ;
2916
2917 CURSOR c_eveo_priority
2918 IS
2919 SELECT priority_type_code priority
2920 FROM ams_event_headers_vl
2921 WHERE event_header_id = p_obj_id ;
2922
2923 l_priority VARCHAR2(30);
2924 l_timeout_rec c_timeout%ROWTYPE;
2925 l_timeout NUMBER ;
2926 l_prio NUMBER ;
2927 BEGIN
2928 IF p_obj_type = 'CAMP' THEN
2929 OPEN c_camp_priority ;
2930 FETCH c_camp_priority INTO l_priority ;
2931 CLOSE c_camp_priority ;
2932 ELSIF p_obj_type = 'DELV' THEN
2933 l_priority := 'STANDARD' ;
2934 ELSIF p_obj_type = 'EVEH' THEN
2935 OPEN c_eveh_priority ;
2936 FETCH c_eveh_priority INTO l_priority ;
2937 CLOSE c_eveh_priority ;
2938 ELSIF p_obj_type = 'EVEO' THEN
2939 OPEN c_eveo_priority ;
2940 FETCH c_eveo_priority INTO l_priority ;
2941 CLOSE c_eveo_priority ;
2942 END IF;
2943
2944 OPEN c_timeout ;
2945 FETCH c_timeout INTO l_timeout_rec ;
2946 CLOSE c_timeout ;
2947
2948 IF l_priority = 'HIGH' THEN
2949 l_timeout := l_timeout_rec.timeout_days_high_prio ;
2950 l_prio := 1 ;
2951 ELSIF l_priority = 'LOW' THEN
2952 l_timeout := l_timeout_rec.timeout_days_low_prio ;
2953 l_prio := 99 ;
2954 ELSIF l_priority = 'MEDIUM' THEN
2955 l_timeout := l_timeout_rec.timeout_days_medium_prio ;
2956 l_prio := 50 ;
2957 ELSIF l_priority = 'STANDARD' THEN
2958 l_timeout := l_timeout_rec.timeout_days_std_prio ;
2959 l_prio := 50 ;
2960 ELSE
2961 l_timeout := l_timeout_rec.timeout_days_std_prio ;
2962 l_prio := 50 ;
2963 END IF;
2964
2965
2966 x_timeout_days := l_timeout ;
2967 -- x_priority := NVL(l_priority,'STANDARD') ;
2968 x_priority := l_prio ;
2969
2970 END Find_Priority ;
2971
2972
2973 --
2974 -- Create_Notif_Document
2975 -- Generate the Theme/Budget Document for display in messages (Notifications
2976 -- for the Activity Approvals)
2977 -- IN
2978 -- document_id - Item Key
2979 -- display_type - either 'text/plain' or 'text/html'
2980 -- document - document buffer
2981 -- document_type - type of document buffer created, either 'text/plain'
2982 -- or 'text/html'
2983 -- OUT
2984 -- USED BY
2985 --
2986 procedure Create_Notif_Document(document_id in varchar2,
2987 display_type in varchar2,
2988 document in OUT NOCOPY varchar2,
2989 document_type in OUT NOCOPY varchar2)
2990 is
2991 itemtype VARCHAR2(30);
2992 itemkey VARCHAR2(30);
2993
2994 l_approval_for VARCHAR2(30);
2995 l_approval_for_id NUMBER;
2996 l_approval_type VARCHAR2(80);
2997
2998 l_message VARCHAR2(4000);
2999
3000 l_requester VARCHAR2(30);
3001 l_requester_note VARCHAR2(2000);
3002 l_start_dt DATE;
3003 l_end_dt DATE;
3004 l_desc VARCHAR2(4000);
3005 l_budget_amount NUMBER ;
3006 l_currency_code VARCHAR2(30);
3007 l_currency VARCHAR2(80);
3008 l_doc_type VARCHAR2(30);
3009
3010 l_tmp_str VARCHAR2(2000);
3011
3012
3013 l_activity_name VARCHAR2(240);
3014 l_camp_theme VARCHAR2(4000);
3015 l_camp_obj VARCHAR2(4000);
3016 l_camp_mkt VARCHAR2(4000);
3017 l_camp_geo VARCHAR2(4000);
3018
3019
3020
3021
3022
3023 l_deli_language VARCHAR2(30);
3024 l_deli_mkt_msg VARCHAR2(4000);
3025
3026
3027 l_eve_mkt_msg VARCHAR2(4000);
3028
3029 l_owner VARCHAR2(240) ;
3030
3031 -- Following cursors to get the owners is added by ptendulk
3032 -- on 08-Jun-2000
3033 CURSOR c_camp_owner(l_id NUMBER)
3034 IS
3035 SELECT jtf.full_name
3036 FROM ams_jtf_rs_emp_v jtf,ams_campaigns_vl camp
3037 WHERE camp.campaign_id = l_id
3038 AND jtf.resource_id = camp.owner_user_id ;
3039
3040 CURSOR c_eveh_owner(l_id NUMBER)
3041 IS
3042 SELECT jtf.full_name
3043 FROM ams_jtf_rs_emp_v jtf,ams_event_headers_vl eveh
3044 WHERE eveh.event_header_id = l_id
3045 AND jtf.resource_id = eveh.owner_user_id ;
3046
3047 CURSOR c_eveo_owner(l_id NUMBER)
3048 IS
3049 SELECT jtf.full_name
3050 FROM ams_jtf_rs_emp_v jtf,ams_event_offers_vl eveo
3051 WHERE eveo.event_offer_id = l_id
3052 AND jtf.resource_id = eveo.owner_user_id ;
3053
3054
3055 begin
3056 IF (AMS_DEBUG_HIGH_ON) THEN
3057
3058 AMS_Utility_PVT.debug_message('Create Documents');
3059 END IF;
3060
3061 -- dbms_output.put_line('Create Documents');
3062 -- parse document_id for the ':' dividing item type name from item key value
3063 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
3064 -- release 2.5 version of this demo
3065 ItemType := nvl(substr(document_id, 1, instr(document_id,':')-1),'AMSAPPR');
3066 ItemKey := substr(document_id
3067 , instr(document_id,':')+1);
3068
3069 l_approval_for := wf_engine.GetItemAttrText(
3070 itemtype => ItemType,
3071 itemkey => ItemKey,
3072 aname => 'AMS_APPROVAL_FOR_OBJECT');
3073
3074 l_approval_for_id := wf_engine.GetItemAttrText(
3075 itemtype => ItemType,
3076 itemkey => ItemKey,
3077 aname => 'AMS_ACT_ID');
3078
3079 l_requester := wf_engine.GetItemAttrText(
3080 itemtype => ItemType,
3081 itemkey => ItemKey,
3082 aname => 'AMS_REQUESTER');
3083
3084 l_requester_note := wf_engine.GetItemAttrText(
3085 itemtype => ItemType,
3086 itemkey => ItemKey,
3087 aname => 'AMS_NOTES_FROM_REQUESTER');
3088
3089 -- dbms_output.Put_line('Requester Note : '||l_requester_note );
3090 l_doc_type := wf_engine.GetItemAttrText(
3091 itemtype => ItemType,
3092 itemkey => ItemKey,
3093 aname => 'AMS_APPR_DOC_TYPE');
3094
3095 l_approval_type := wf_engine.GetItemAttrText(
3096 itemtype => ItemType,
3097 itemkey => ItemKey,
3098 aname => 'AMS_APPROVAL_TYPE');
3099
3100 l_activity_name := WF_ENGINE.GetItemAttrText
3101 (itemtype => itemtype ,
3102 itemkey => itemkey,
3103 aname => 'AMS_ACT_NAME' );
3104
3105 l_start_dt := WF_ENGINE.GetItemAttrText
3106 (itemtype => itemtype,
3107 itemkey => itemkey ,
3108 aname => 'AMS_ACT_START_DATE');
3109
3110 l_end_dt := WF_ENGINE.GetItemAttrText
3111 (itemtype => itemtype,
3112 itemkey => itemkey ,
3113 aname => 'AMS_ACT_END_DATE' );
3114
3115 l_budget_amount := WF_ENGINE.GetItemAttrText
3116 (itemtype => itemtype ,
3117 itemkey => itemkey,
3118 aname => 'AMS_BUDGET_AMOUNT');
3119 l_desc := WF_ENGINE.GetItemAttrText
3120 (itemtype => itemtype,
3121 itemkey => itemkey ,
3122 aname => 'AMS_ACT_DESC');
3123 l_currency := WF_ENGINE.GetItemAttrText
3124 (itemtype => itemtype ,
3125 itemkey => itemkey,
3126 aname => 'AMS_CURRENCY');
3127
3128
3129 -- Create an html text buffer
3130 -- if (display_type = 'text/html') then
3131 -- null;
3132 -- return;
3133 -- end if;
3134
3135 -- Create a plain text buffer
3136 -- if (display_type = 'text/plain') then
3137 IF l_approval_for = 'CAMP' THEN
3138
3139 OPEN c_camp_owner(l_approval_for_id) ;
3140 FETCH c_camp_owner INTO l_owner ;
3141 CLOSE c_camp_owner ;
3142
3143
3144 l_camp_theme := WF_ENGINE.GetItemAttrText
3145 (itemtype => itemtype ,
3146 itemkey => itemkey,
3147 aname => 'AMS_CAMP_THEME');
3148 l_camp_mkt := WF_ENGINE.GetItemAttrText
3149 (itemtype => itemtype,
3150 itemkey => itemkey ,
3151 aname => 'AMS_CAMP_MARKET');
3152 l_camp_geo := WF_ENGINE.GetItemAttrText
3153 (itemtype => itemtype ,
3154 itemkey => itemkey,
3155 aname => 'AMS_CAMP_GEO');
3156
3157
3158 IF l_doc_type = 'THEME' THEN
3159
3160
3161 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_THEME_APPROVAL');
3162 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3163 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE', l_start_dt, FALSE);
3164 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_end_dt, FALSE);
3165 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3166 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3167 FND_MESSAGE.Set_Token('CAMP_MKT', l_camp_mkt, FALSE);
3168 FND_MESSAGE.Set_Token('CAMP_GEO', l_camp_geo, FALSE);
3169 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3170 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3171 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3172 l_message := FND_MESSAGE.Get;
3173
3174 ELSIF l_doc_type = 'BUDGET' THEN
3175
3176
3177
3178 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPR');
3179 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3180 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3181 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3182 FND_MESSAGE.Set_Token('CURR', l_currency, FALSE);
3183 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE', l_start_dt, FALSE);
3184 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_end_dt, FALSE);
3185 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3186 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3187 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3188 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3189 l_message := FND_MESSAGE.Get;
3190 ELSIF l_doc_type = 'BOTH' THEN
3191
3192
3193 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BOTH_APPR');
3194 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3195 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3196 FND_MESSAGE.Set_Token('CURR', l_currency, FALSE);
3197 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE', l_start_dt, FALSE);
3198 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_end_dt, FALSE);
3199 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3200 FND_MESSAGE.Set_Token('CAMP_MKT', l_camp_mkt, FALSE);
3201 FND_MESSAGE.Set_Token('CAMP_GEO', l_camp_geo, FALSE);
3202 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3203 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3204 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3205 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3206 l_message := FND_MESSAGE.Get;
3207
3208 END IF;
3209 ELSIF l_approval_for = 'DELV' THEN
3210 l_deli_mkt_msg := WF_ENGINE.GetItemAttrText
3211 (itemtype => itemtype,
3212 itemkey => itemkey ,
3213 aname => 'AMS_DELI_MKT_MESSAGE');
3214
3215 l_deli_language := WF_ENGINE.GetItemAttrText
3216 (itemtype => itemtype,
3217 itemkey => itemkey ,
3218 aname => 'AMS_DELI_LANGUAGE' );
3219
3220 IF l_approval_type = 'THEME' THEN
3221
3222
3223 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_THEME_APPROVAL');
3224 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3225 FND_MESSAGE.Set_Token('DELI_START_DATE', l_start_dt, FALSE);
3226 FND_MESSAGE.Set_Token('DELI_END_DATE', l_end_dt, FALSE);
3227 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3228 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3229 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3230 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3231 l_message := FND_MESSAGE.Get;
3232
3233 ELSIF l_approval_type = 'BUDGET' THEN
3234
3235 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_BUDGET_APPROVAL');
3236 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3237 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3238 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3239 FND_MESSAGE.Set_Token('DELI_START_DATE', l_start_dt, FALSE);
3240 FND_MESSAGE.Set_Token('DELI_END_DATE', l_end_dt, FALSE);
3241 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3242 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3243 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3244 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3245 l_message := FND_MESSAGE.Get;
3246 ELSIF l_approval_type = 'BOTH' THEN
3247 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_THEME_APPROVAL');
3248 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3249 FND_MESSAGE.Set_Token('DELI_START_DATE', l_start_dt, FALSE);
3250 FND_MESSAGE.Set_Token('DELI_END_DATE', l_end_dt, FALSE);
3251 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3252 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3253 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3254 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3255 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3256 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3257 l_message := FND_MESSAGE.Get;
3258
3259 END IF;
3260 ELSIF l_approval_for = 'EVEH' THEN
3261 l_eve_mkt_msg := WF_ENGINE.GetItemAttrText
3262 (itemtype => itemtype,
3263 itemkey => itemkey ,
3264 aname => 'AMS_EVE_MKT_MESSAGE');
3265 OPEN c_eveh_owner(l_approval_for_id) ;
3266 FETCH c_eveh_owner INTO l_owner ;
3267 CLOSE c_eveh_owner ;
3268
3269
3270 IF l_approval_type = 'THEME' THEN
3271 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEH_WF_NTF_THEME_APPROVAL');
3272 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3273 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_start_dt, FALSE);
3274 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_end_dt, FALSE);
3275 FND_MESSAGE.Set_Token('EVEH_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3276 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3277 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3278 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3279 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3280 l_message := FND_MESSAGE.Get;
3281
3282 ELSIF l_approval_type = 'BUDGET' THEN
3283 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPROVAL');
3284 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3285 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_start_dt, FALSE);
3286 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_end_dt, FALSE);
3287 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3288 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3289 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3290 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3291 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3292 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3293 l_message := FND_MESSAGE.Get;
3294
3295 ELSIF l_approval_type = 'BOTH' THEN
3296 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEH_WF_NTF_THEME_APPROVAL');
3297 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3298 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_start_dt, FALSE);
3299 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_end_dt, FALSE);
3300 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3301 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3302 FND_MESSAGE.Set_Token('EVEH_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3303 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3304 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3305 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3306 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3307 l_message := FND_MESSAGE.Get;
3308
3309 END IF;
3310 ELSIF l_approval_for = 'EVEO' THEN
3311
3312 l_eve_mkt_msg := WF_ENGINE.GetItemAttrText
3313 (itemtype => itemtype,
3314 itemkey => itemkey ,
3315 aname => 'AMS_EVE_MKT_MESSAGE');
3316 OPEN c_eveo_owner(l_approval_for_id) ;
3317 FETCH c_eveo_owner INTO l_owner ;
3318 CLOSE c_eveo_owner ;
3319
3320
3321 IF l_approval_type = 'THEME' THEN
3322 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEO_WF_NTF_THEME_APPROVAL');
3323 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3324 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_start_dt, FALSE);
3325 FND_MESSAGE.Set_Token('EVEO_END_DATE', l_end_dt, FALSE);
3326 FND_MESSAGE.Set_Token('EVEO_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3327 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3328 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3329 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3330 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3331 l_message := FND_MESSAGE.Get;
3332
3333 ELSIF l_approval_type = 'BUDGET' THEN
3334 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPROVAL');
3335 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3336 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3337 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3338 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_start_dt, FALSE);
3339 FND_MESSAGE.Set_Token('EVEO_END_DATE', l_end_dt, FALSE);
3340 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3341 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3342 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3343 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3344 l_message := FND_MESSAGE.Get;
3345
3346 ELSIF l_approval_type = 'BOTH' THEN
3347 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEO_WF_NTF_THEME_APPROVAL');
3348 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3349 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_start_dt, FALSE);
3350 FND_MESSAGE.Set_Token('EVEO_END_DATE', l_end_dt, FALSE);
3351 FND_MESSAGE.Set_Token('EVEO_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3352 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3353 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3354 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3355 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3356 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3357 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3358 l_message := FND_MESSAGE.Get;
3359
3360 END IF;
3361 END IF ;
3362
3363
3364 document := document|| l_message ;
3365
3366 document_type := 'text/plain';
3367
3368 RETURN;
3369 -- END IF;
3370
3371 EXCEPTION
3372 WHEN OTHERS THEN
3373 wf_core.context('AMS_WfCmpapr_PVT','Create_Notif_Document',itemtype,itemkey);
3374 RAISE;
3375 --actid,
3376 END Create_Notif_Document;
3377
3378 -- Start of Comments
3379 --
3380 -- NAME
3381 -- Prepare_Doc
3382 --
3383 -- PURPOSE
3384 -- This Procedure will create the Document to be sent for the Approvals
3385 -- it will also Update the Status As the Activity as Submitted for Approvals
3386 --
3387 -- IN
3388 -- Itemtype - AMSAPPR
3389 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
3390 -- Accid - Activity ID
3391 -- Funmode - Run/Cancel/Timeout
3392 --
3393 -- OUT
3394 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
3395 -- COMPLETE:AMS_ERROR If the Process is errored out.
3396 --
3397 -- Used By Activities
3398 -- Item Type - AMSAPPR
3399 -- Activity - AMS_PREPARE_DOC
3400 --
3401 -- NOTES
3402 --
3403 --
3404 -- HISTORY
3405 -- 08/20/1999 ptendulk created
3406 -- 11/30/1999 ptendulk Modified
3407 -- End of Comments
3408
3409 PROCEDURE Prepare_Doc (itemtype IN VARCHAR2,
3410 itemkey IN VARCHAR2,
3411 actid IN NUMBER,
3412 funcmode IN VARCHAR2,
3413 result OUT NOCOPY VARCHAR2) IS
3414
3415 l_obj_type VARCHAR2(30);
3416 l_tmp_stat_code VARCHAR2(30);
3417 l_sys_stat_code VARCHAR2(30);
3418
3419 l_msg_count NUMBER ;
3420 l_msg_data VARCHAR2(2000);
3421 l_obj_id NUMBER ;
3422 l_obj_version_number NUMBER ;
3423 l_requester_id NUMBER ;
3424 l_doc_type VARCHAR2(30) ;
3425 l_return_status VARCHAR2(1);
3426
3427 l_requester VARCHAR2(30);
3428 l_approval_for VARCHAR2(30);
3429 l_approval_for_id NUMBER;
3430 l_approval_type VARCHAR2(80);
3431 l_timeout NUMBER ;
3432 l_priority NUMBER;
3433
3434 l_activity_name VARCHAR2(240);
3435
3436 l_start_dt DATE;
3437 l_end_dt DATE;
3438 l_desc VARCHAR2(2000);
3439 l_budget_amount NUMBER;
3440 l_currency_code VARCHAR2(30);
3441 l_currency VARCHAR2(80);
3442
3443 -- Campaign Variables
3444 CURSOR C_campaign(l_my_campaign_id VARCHAR2
3445 ) IS
3446 SELECT camp.campaign_name,
3447 camp.actual_exec_start_date,
3448 camp.actual_exec_end_date,
3449 camp.description,
3450 camp.transaction_currency_code,
3451 camp.budget_amount_tc
3452 FROM ams_campaigns_vl camp
3453 WHERE camp.campaign_id = l_my_campaign_id ;
3454
3455 CURSOR C_camp_geo(l_my_campaign_id VARCHAR2
3456 ) IS
3457 SELECT geo_area_name
3458 FROM ams_act_geo_areas_v
3459 WHERE act_geo_area_user = 'CAMP'
3460 AND act_geo_area_user_id = l_my_campaign_id ;
3461
3462 CURSOR C_camp_mkt(l_my_campaign_id VARCHAR2
3463 ) IS
3464 SELECT mkt.cell_name
3465 FROM ams_act_market_segments act,ams_cells_vl mkt
3466 WHERE act.arc_act_market_segment_used_by = 'CAMP'
3467 AND act.act_market_segment_used_by_id = l_my_campaign_id
3468 AND mkt.cell_id = act.market_segment_id ;
3469
3470 CURSOR C_theme(l_my_campaign_id IN NUMBER,
3471 l_my_obj_type IN VARCHAR2)
3472 IS
3473 SELECT m.message_name
3474 FROM ams_messages_vl m,ams_act_messages a
3475 WHERE a.message_used_by_id = l_my_campaign_id
3476 AND a.message_used_by = l_my_obj_type
3477 AND a.message_id = m.message_id ;
3478
3479 CURSOR c_currency(l_cur_code IN VARCHAR2)
3480 IS
3481 SELECT name
3482 FROM fnd_currencies_vl
3483 WHERE currency_code = l_cur_code ;
3484
3485 l_tmp_str VARCHAR2(2000);
3486
3487
3488
3489 l_camp_theme VARCHAR2(4000);
3490 l_camp_obj VARCHAR2(4000);
3491 l_camp_mkt VARCHAR2(4000);
3492 l_camp_geo VARCHAR2(4000);
3493
3494
3495 -- Deliverables Variables
3496 CURSOR C_deliverable(l_my_deliverable_id VARCHAR2 )
3497 IS
3498 SELECT dl.deliverable_name deliverable_name,
3499 dl.actual_avail_from_date actual_avail_from_date,
3500 dl.actual_avail_to_date actual_avail_to_date,
3501 dl.description description,
3502 lg.nls_language nls_language,
3503 dl.transaction_currency_code transaction_currency_code,
3504 dl.budget_amount_tc budget_amount_tc
3505 FROM ams_deliverables_vl dl,fnd_languages lg
3506 WHERE dl.deliverable_id = l_my_deliverable_id
3507 AND dl.language_code = lg.language_code ;
3508
3509
3510 l_deli_language VARCHAR2(30);
3511 l_deli_mkt_msg VARCHAR2(4000);
3512
3513 -- events variables
3514
3515 CURSOR C_event_header(l_my_event_header_id VARCHAR2
3516 ) IS
3517 SELECT event_header_name,
3518 active_from_date,
3519 active_to_date,
3520 description,
3521 fund_amount_tc
3522 ,currency_code_tc
3523 FROM ams_event_headers_vl
3524 WHERE event_header_id = l_my_event_header_id ;
3525
3526 l_eve_mkt_msg VARCHAR2(4000);
3527
3528 CURSOR C_event_offer(l_my_event_offer_id VARCHAR2 )
3529 IS
3530 SELECT o.event_offer_name,
3531 o.event_start_date,
3532 o.event_end_date,
3533 o.description,
3534 o.fund_amount_tc
3535 ,o.currency_code_tc
3536 FROM ams_event_offers_vl o
3537 WHERE o.event_offer_id = l_my_event_offer_id ;
3538
3539
3540
3541 BEGIN
3542 IF (AMS_DEBUG_HIGH_ON) THEN
3543
3544 AMS_Utility_PVT.debug_message('Prepare Documents');
3545 END IF;
3546 -- RUN mode - Normal Process Execution
3547 IF (funcmode = 'RUN')
3548 THEN
3549 -- Create the Notification Document
3550
3551 l_approval_for := wf_engine.GetItemAttrText(
3552 itemtype => ItemType,
3553 itemkey => ItemKey,
3554 aname => 'AMS_APPROVAL_FOR_OBJECT');
3555
3556 l_approval_for_id := wf_engine.GetItemAttrText(
3557 itemtype => ItemType,
3558 itemkey => ItemKey,
3559 aname => 'AMS_ACT_ID');
3560
3561 l_requester := wf_engine.GetItemAttrText(
3562 itemtype => ItemType,
3563 itemkey => ItemKey,
3564 aname => 'AMS_REQUESTER');
3565
3566 l_doc_type := wf_engine.GetItemAttrText(
3567 itemtype => ItemType,
3568 itemkey => ItemKey,
3569 aname => 'AMS_APPR_DOC_TYPE');
3570
3571 l_approval_type := Get_Lookup_Meaning('AMS_APPROVAL_TYPE',l_doc_type);
3572
3573 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3574 itemkey => itemkey,
3575 aname => 'AMS_APPROVAL_TYPE',
3576 avalue => l_approval_type );
3577
3578 Find_Priority(p_obj_type => l_approval_for,
3579 p_obj_id => l_approval_for_id,
3580 p_approval_type => l_doc_type,
3581 x_timeout_days => l_timeout,
3582 x_priority => l_priority) ;
3583
3584
3585 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3586 itemkey => itemkey,
3587 aname => 'AMS_TIMEOUT',
3588 avalue => l_timeout );
3589
3590 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3591 itemkey => itemkey,
3592 aname => 'AMS_PRIORITY',
3593 avalue => l_priority );
3594
3595
3596 IF l_approval_for = 'CAMP' THEN
3597
3598 OPEN c_campaign(l_approval_for_id) ;
3599 FETCH c_campaign
3600 INTO l_activity_name,l_start_dt,l_end_dt,
3601 l_desc,l_currency_code,l_budget_amount ;
3602 CLOSE c_campaign;
3603
3604 OPEN C_theme(l_approval_for_id,'CAMP') ;
3605 FETCH C_theme INTO l_camp_theme ;
3606 LOOP
3607 FETCH C_theme INTO l_tmp_str ;
3608 EXIT WHEN C_theme%NOTFOUND ;
3609 l_camp_theme := l_camp_theme ||', '||l_tmp_str ;
3610 END LOOP;
3611 CLOSE C_theme ;
3612
3613 OPEN c_camp_geo(l_approval_for_id) ;
3614 FETCH c_camp_geo INTO l_camp_geo ;
3615 LOOP
3616 FETCH C_camp_geo INTO l_tmp_str ;
3617 EXIT WHEN C_camp_geo%NOTFOUND ;
3618 l_camp_geo := l_camp_geo ||', '||l_tmp_str ;
3619 END LOOP;
3620 CLOSE c_camp_geo ;
3621
3622 OPEN c_camp_mkt(l_approval_for_id) ;
3623 FETCH c_camp_mkt INTO l_camp_mkt ;
3624 LOOP
3625 FETCH c_camp_mkt INTO l_tmp_str ;
3626 EXIT WHEN c_camp_mkt%NOTFOUND ;
3627 l_camp_mkt := l_camp_mkt ||', '||l_tmp_str ;
3628 END LOOP;
3629 CLOSE c_camp_mkt ;
3630
3631 OPEN c_currency(l_currency_code);
3632 FETCH c_currency INTO l_currency ;
3633 CLOSE c_currency ;
3634
3635
3636 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3637 itemkey => itemkey,
3638 aname => 'AMS_ACT_NAME',
3639 avalue => l_activity_name );
3640 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3641 itemkey => itemkey ,
3642 aname => 'AMS_ACT_START_DATE',
3643 avalue => l_start_dt );
3644 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3645 itemkey => itemkey ,
3646 aname => 'AMS_ACT_END_DATE',
3647 avalue => l_end_dt );
3648 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3649 itemkey => itemkey,
3650 aname => 'AMS_CAMP_THEME',
3651 avalue => l_camp_theme );
3652
3653 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3654 itemkey => itemkey ,
3655 aname => 'AMS_CAMP_MARKET',
3656 avalue => l_camp_mkt );
3657 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3658 itemkey => itemkey,
3659 aname => 'AMS_CAMP_GEO',
3660 avalue => l_camp_geo );
3661 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3662 itemkey => itemkey,
3663 aname => 'AMS_BUDGET_AMOUNT',
3664 avalue => l_budget_amount );
3665 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3666 itemkey => itemkey ,
3667 aname => 'AMS_ACT_DESC',
3668 avalue => l_desc );
3669
3670 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3671 itemkey => itemkey,
3672 aname => 'AMS_CURRENCY',
3673 avalue => l_currency );
3674
3675
3676 ELSIF l_approval_for = 'DELV' THEN
3677 OPEN c_deliverable(l_approval_for_id) ;
3678 FETCH c_deliverable INTO l_activity_name,l_start_dt,
3679 l_end_dt,l_desc,l_deli_language,l_currency_code,l_budget_amount ;
3680 CLOSE c_deliverable;
3681
3682 OPEN c_currency(l_currency_code);
3683 FETCH c_currency INTO l_currency ;
3684 CLOSE c_currency ;
3685
3686 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3687 itemkey => itemkey,
3688 aname => 'AMS_ACT_NAME',
3689 avalue => l_activity_name );
3690 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3691 itemkey => itemkey ,
3692 aname => 'AMS_ACT_START_DATE',
3693 avalue => l_start_dt );
3694 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3695 itemkey => itemkey ,
3696 aname => 'AMS_ACT_END_DATE',
3697 avalue => l_end_dt );
3698 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3699 itemkey => itemkey ,
3700 aname => 'AMS_DELI_LANGUAGE',
3701 avalue => l_deli_language );
3702 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3703 itemkey => itemkey ,
3704 aname => 'AMS_ACT_DESC',
3705 avalue => l_desc );
3706 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3707 itemkey => itemkey,
3708 aname => 'AMS_BUDGET_AMOUNT',
3709 avalue => l_budget_amount );
3710
3711 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3712 itemkey => itemkey,
3713 aname => 'AMS_CURRENCY',
3714 avalue => l_currency );
3715 ELSIF l_approval_for = 'EVEH' THEN
3716
3717 OPEN c_event_header(l_approval_for_id);
3718 FETCH c_event_header
3719 INTO l_activity_name,l_start_dt,l_end_dt,l_desc,l_budget_amount,l_currency_code ;
3720 CLOSE c_event_header;
3721
3722 OPEN c_currency(l_currency_code);
3723 FETCH c_currency INTO l_currency ;
3724 CLOSE c_currency ;
3725
3726 OPEN C_theme(l_approval_for_id,'EVEH') ;
3727 FETCH C_theme INTO l_eve_mkt_msg ;
3728 LOOP
3729 FETCH C_theme INTO l_tmp_str ;
3730 EXIT WHEN C_theme%NOTFOUND ;
3731 l_eve_mkt_msg := l_eve_mkt_msg ||', '||l_tmp_str ;
3732 END LOOP;
3733 CLOSE C_theme ;
3734
3735 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3736 itemkey => itemkey,
3737 aname => 'AMS_ACT_NAME',
3738 avalue => l_activity_name );
3739 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3740 itemkey => itemkey ,
3741 aname => 'AMS_ACT_START_DATE',
3742 avalue => l_start_dt );
3743 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3744 itemkey => itemkey ,
3745 aname => 'AMS_ACT_END_DATE',
3746 avalue => l_end_dt );
3747 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3748 itemkey => itemkey ,
3749 aname => 'AMS_EVE_MKT_MESSAGE',
3750 avalue => l_eve_mkt_msg );
3751 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3752 itemkey => itemkey,
3753 aname => 'AMS_BUDGET_AMOUNT',
3754 avalue => l_budget_amount );
3755 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3756 itemkey => itemkey ,
3757 aname => 'AMS_ACT_DESC',
3758 avalue => l_desc );
3759 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3760 itemkey => itemkey,
3761 aname => 'AMS_CURRENCY',
3762 avalue => l_currency );
3763 ELSIF l_approval_for = 'EVEO' THEN
3764
3765 OPEN c_event_offer(l_approval_for_id) ;
3766 FETCH c_event_offer
3767 INTO l_activity_name,l_start_dt,l_end_dt,l_desc,l_budget_amount,l_currency_code;
3768 -- , l_currency ;
3769 CLOSE c_event_offer;
3770
3771 OPEN c_currency(l_currency_code);
3772 FETCH c_currency INTO l_currency ;
3773 CLOSE c_currency ;
3774
3775 OPEN C_theme(l_approval_for_id,'EVEO') ;
3776 FETCH C_theme INTO l_eve_mkt_msg ;
3777 LOOP
3778 FETCH C_theme INTO l_tmp_str ;
3779 EXIT WHEN C_theme%NOTFOUND ;
3780 l_eve_mkt_msg := l_eve_mkt_msg ||', '||l_tmp_str ;
3781 END LOOP;
3782 CLOSE C_theme ;
3783
3784 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3785 itemkey => itemkey,
3786 aname => 'AMS_ACT_NAME',
3787 avalue => l_activity_name );
3788 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3789 itemkey => itemkey ,
3790 aname => 'AMS_ACT_START_DATE',
3791 avalue => l_start_dt );
3792 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3793 itemkey => itemkey ,
3794 aname => 'AMS_ACT_END_DATE',
3795 avalue => l_end_dt );
3796 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3797 itemkey => itemkey ,
3798 aname => 'AMS_EVE_MKT_MESSAGE',
3799 avalue => l_eve_mkt_msg );
3800 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3801 itemkey => itemkey,
3802 aname => 'AMS_BUDGET_AMOUNT',
3803 avalue => l_budget_amount );
3804 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3805 itemkey => itemkey ,
3806 aname => 'AMS_ACT_DESC',
3807 avalue => l_desc );
3808 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3809 itemkey => itemkey,
3810 aname => 'AMS_CURRENCY',
3811 avalue => l_currency );
3812
3813
3814 END IF;
3815
3816
3817 wf_engine.SetItemAttrText( itemtype => itemtype,
3818 itemkey => itemkey,
3819 aname => 'AMS_NOTIF_DOCUMENT',
3820 avalue =>
3821 'PLSQL:AMS_WFCMPAPR_PVT.Create_Notif_Document/'||
3822 ItemType||':'||
3823 ItemKey);
3824
3825 l_doc_type := wf_engine.GetItemAttrText(
3826 itemtype => ItemType,
3827 itemkey => ItemKey,
3828 aname => 'AMS_APPR_DOC_TYPE');
3829
3830
3831 l_obj_type := wf_engine.GetItemAttrText(
3832 itemtype => ItemType,
3833 itemkey => ItemKey,
3834 aname => 'AMS_APPROVAL_FOR_OBJECT');
3835
3836 l_obj_id := wf_engine.GetItemAttrText(
3837 itemtype => ItemType,
3838 itemkey => ItemKey,
3839 aname => 'AMS_ACT_ID');
3840 l_obj_version_number := wf_engine.GetItemAttrText(
3841 itemtype => ItemType,
3842 itemkey => ItemKey,
3843 aname => 'AMS_OBJECT_VERSION_NUMBER');
3844 l_requester_id := wf_engine.GetItemAttrText(
3845 itemtype => ItemType,
3846 itemkey => ItemKey,
3847 aname => 'AMS_REQUESTER_ID');
3848
3849
3850 IF l_doc_type = 'BUDGET' THEN
3851 l_tmp_stat_code := 'SUBMIT_BA' ;
3852 ELSE
3853 l_tmp_stat_code := 'SUBMIT_TA' ;
3854 END IF ;
3855 --
3856 -- Get the Valid Code (Bubmit BA/TA) for Activity
3857 --
3858 Get_Valid_status(p_object_type => l_obj_type,
3859 p_stat_code => l_tmp_stat_code,
3860 x_sys_stat_code => l_sys_stat_code) ;
3861
3862 --
3863 -- Update Activity
3864 --
3865 Update_Status(p_obj_type => l_obj_type,
3866 p_obj_id => l_obj_id,
3867 p_object_version_number => l_obj_version_number,
3868 p_next_stat_code => l_sys_stat_code, --System Status
3869 p_appr_type => l_doc_type,
3870 p_submitted_by => l_requester_id,
3871 p_item_key => itemkey ,
3872 x_msg_count => l_msg_count,
3873 x_msg_data => l_msg_data,
3874 x_return_status => l_return_status) ;
3875
3876
3877 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3878 result := 'COMPLETE:SUCCESS' ;
3879 ELSE
3880 Handle_Err
3881 (p_itemtype => itemtype ,
3882 p_itemkey => itemkey ,
3883 p_msg_count => l_msg_count, -- Number of error Messages
3884 p_msg_data => l_msg_data ,
3885 p_attr_name => 'AMS_ERROR_MSG'
3886 ) ;
3887 result := 'COMPLETE:ERROR' ;
3888 END IF ;
3889 END IF ;
3890
3891 -- CANCEL mode - Normal Process Execution
3892 IF (funcmode = 'CANCEL')
3893 THEN
3894 result := 'COMPLETE:' ;
3895 RETURN;
3896 END IF;
3897
3898 -- TIMEOUT mode - Normal Process Execution
3899 IF (funcmode = 'TIMEOUT')
3900 THEN
3901 result := 'COMPLETE:' ;
3902 RETURN;
3903 END IF;
3904 EXCEPTION
3905 WHEN OTHERS THEN
3906 wf_core.context(G_PKG_NAME,'Prepare_Doc',itemtype,itemkey,actid,funcmode);
3907 raise ;
3908 END Prepare_Doc ;
3909
3910 -- Start of Comments
3911 --
3912 -- NAME
3913 -- Owner_Appr_Check
3914 --
3915 -- PURPOSE
3916 -- This Procedure will check whether the Owner's Approval is required for the Theme
3917 --
3918 -- IN
3919 -- Itemtype - AMSAPPR
3920 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
3921 -- Accid - Activity ID
3922 -- Funmode - Run/Cancel/Timeout
3923 --
3924 -- OUT
3925 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
3926 -- - 'COMPLETE:N' If the Owner's Approval is not required
3927 --
3928 --
3929 -- OUT
3930 --
3931 -- Used By Activities
3932 -- Item Type - AMSAPPR
3933 -- Activity - AMS_CHECK_OWN_APPR
3934 -- NOTES
3935 --
3936 --
3937 -- HISTORY
3938 -- 09/13/1999 ptendulk created
3939 -- 11/30/1999 ptendulk Modified
3940 -- End of Comments
3941
3942 PROCEDURE Owner_Appr_Check (itemtype IN VARCHAR2,
3943 itemkey IN VARCHAR2,
3944 actid IN NUMBER,
3945 funcmode IN VARCHAR2,
3946 result OUT NOCOPY VARCHAR2) IS
3947 l_ta_appr_flag VARCHAR2(1);
3948 l_owner VARCHAR2(100);
3949 l_manager VARCHAR2(100);
3950 l_fund_manager VARCHAR2(100);
3951 l_bud_appr_flag VARCHAR2(1);
3952 l_appr_type VARCHAR2(30);
3953 BEGIN
3954 IF (AMS_DEBUG_HIGH_ON) THEN
3955
3956 AMS_Utility_PVT.debug_message('Process Owner_appr_check');
3957 END IF;
3958 -- dbms_output.put_line('Process Owner_appr_check');
3959 -- RUN mode - Normal Process Execution
3960 IF (funcmode = 'RUN')
3961 THEN
3962 l_ta_appr_flag := WF_ENGINE.GetItemAttrText(
3963 itemtype => itemtype,
3964 itemkey => itemkey ,
3965 aname => 'AMS_TAOWNER_APPR_FLAG');
3966 l_owner := WF_ENGINE.GetItemAttrText(
3967 itemtype => itemtype,
3968 itemkey => itemkey ,
3969 aname => 'AMS_OWNER');
3970 l_bud_appr_flag := WF_ENGINE.GetItemAttrText(
3971 itemtype => itemtype,
3972 itemkey => itemkey ,
3973 aname => 'AMS_BUDGET_APPR_FLAG');
3974
3975 l_fund_manager := WF_ENGINE.GetItemAttrText(
3976 itemtype => itemtype,
3977 itemkey => itemkey ,
3978 aname => 'AMS_BUD_MANAGER');
3979
3980 l_manager := WF_ENGINE.GetItemAttrText(
3981 itemtype => itemtype,
3982 itemkey => itemkey ,
3983 aname => 'AMS_MANAGER');
3984
3985 l_appr_type := WF_ENGINE.GetItemAttrText(
3986 itemtype => itemtype,
3987 itemkey => itemkey ,
3988 aname => 'AMS_APPR_TYPE_LOOKUP');
3989
3990 IF l_ta_appr_flag = 'Y' THEN
3991 IF l_appr_type = 'THEME' THEN
3992 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3993 itemkey => itemkey,
3994 aname => 'AMS_APPR_USERNAME',
3995 avalue => l_owner);
3996 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3997 itemkey => itemkey,
3998 aname => 'AMS_TAOWNER_APPR_FLAG',
3999 avalue => 'N');
4000 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4001 itemkey => itemkey,
4002 aname => 'AMS_APPR_DOC_TYPE',
4003 avalue => 'THEME');
4004
4005 ELSE -- l_appr_type = 'BOTH' THEN
4006 IF l_fund_manager = l_manager THEN
4007 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4008 itemkey => itemkey,
4009 aname => 'AMS_APPR_USERNAME',
4010 avalue => l_owner);
4011 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4012 itemkey => itemkey,
4013 aname => 'AMS_TAOWNER_APPR_FLAG',
4014 avalue => 'N');
4015 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4016 itemkey => itemkey,
4017 aname => 'AMS_APPR_DOC_TYPE',
4018 avalue => 'THEME');
4019 ELSIF l_owner = l_fund_manager THEN
4020 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4021 itemkey => itemkey,
4022 aname => 'AMS_APPR_USERNAME',
4023 avalue => l_owner);
4024
4025 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4026 itemkey => itemkey,
4027 aname => 'AMS_TAOWNER_APPR_FLAG',
4028 avalue => 'N');
4029 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4030 itemkey => itemkey,
4031 aname => 'AMS_APPR_DOC_TYPE',
4032 avalue => 'BOTH');
4033 ELSE
4034 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4035 itemkey => itemkey,
4036 aname => 'AMS_APPR_USERNAME',
4037 avalue => l_owner);
4038
4039 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4040 itemkey => itemkey,
4041 aname => 'AMS_BUDGET_APPR_FLAG',
4042 avalue => 'Y');
4043 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4044 itemkey => itemkey,
4045 aname => 'AMS_TAOWNER_APPR_FLAG',
4046 avalue => 'N');
4047 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4048 itemkey => itemkey,
4049 aname => 'AMS_APPR_DOC_TYPE',
4050 avalue => 'THEME');
4051 END IF ;
4052 END IF;
4053 result := 'COMPLETE:Y' ;
4054 ELSE -- IF l_ba_appr_flag = 'N' THEN
4055 IF l_bud_appr_flag = 'Y' THEN
4056 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4057 itemkey => itemkey,
4058 aname => 'AMS_APPR_USERNAME',
4059 avalue => l_fund_manager);
4060 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4061 itemkey => itemkey,
4062 aname => 'AMS_APPR_DOC_TYPE',
4063 avalue => 'BUDGET');
4064 END IF;
4065 result := 'COMPLETE:N' ;
4066 END IF;
4067 END IF;
4068
4069 -- CANCEL mode - Normal Process Execution
4070 IF (funcmode = 'CANCEL')
4071 THEN
4072 result := 'COMPLETE:' ;
4073 RETURN;
4074 END IF;
4075
4076 -- TIMEOUT mode - Normal Process Execution
4077 IF (funcmode = 'TIMEOUT')
4078 THEN
4079 result := 'COMPLETE:' ;
4080 RETURN;
4081 END IF;
4082 -- dbms_output.put_line('End Update log');
4083 EXCEPTION
4084 WHEN OTHERS THEN
4085 wf_core.context(G_PKG_NAME,'Owner_Appr_Check',itemtype,itemkey,actid,funcmode);
4086 raise ;
4087 END Owner_Appr_Check;
4088
4089 -- Start of Comments
4090 --
4091 -- NAME
4092 -- Update_Stat_ApprTA
4093 --
4094 -- PURPOSE
4095 -- This Procedure will Update the Status of the Activity for Approval
4096 --
4097 -- IN
4098 -- Itemtype - AMSAPPR
4099 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4100 -- Accid - Activity ID
4101 -- Funmode - Run/Cancel/Timeout
4102 --
4103 -- OUT
4104 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4105 -- COMPLETE:AMS_ERROR If the Process is errored out.
4106 --
4107 -- Used By Activities
4108 -- Item Type - AMSAPPR
4109 -- Activity - AMS_UPDATE_STATUS_TA
4110 --
4111 -- NOTES
4112 --
4113 --
4114 -- HISTORY
4115 -- 08/20/1999 ptendulk created
4116 -- 11/30/1999 ptendulk Modified
4117 -- End of Comments
4118
4119 PROCEDURE Update_Stat_ApprTA (itemtype IN VARCHAR2,
4120 itemkey IN VARCHAR2,
4121 actid IN NUMBER,
4122 funcmode IN VARCHAR2,
4123 result OUT NOCOPY VARCHAR2) IS
4124
4125 l_bud_appr_flg VARCHAR2(1);
4126 l_obj_type VARCHAR2(30);
4127 l_next_stat_id NUMBER ;
4128
4129 l_msg_count NUMBER ;
4130 l_msg_data VARCHAR2(2000);
4131 l_obj_id NUMBER ;
4132 l_obj_version_number NUMBER ;
4133 l_doc_type VARCHAR2(30);
4134 l_requester_id NUMBER ;
4135 l_appr_type_lookup VARCHAR2(30);
4136 l_sys_stat_code VARCHAR2(30);
4137 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4138
4139 l_approver VARCHAR2(30);
4140 l_note VARCHAR2(2000);
4141
4142 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4143 SELECT system_status_code
4144 FROM ams_user_statuses_vl
4145 WHERE user_status_id = l_user_stat_id ;
4146
4147 BEGIN
4148 IF (AMS_DEBUG_HIGH_ON) THEN
4149
4150 AMS_Utility_PVT.debug_message('Process Updt_Stat_apprTA');
4151 END IF;
4152 -- dbms_output.put_line('Process Updt_Stat_apprTA');
4153 -- RUN mode - Normal Process Execution
4154 IF (funcmode = 'RUN')
4155 THEN
4156 l_bud_appr_flg := wf_engine.GetItemAttrText(
4157 itemtype => ItemType,
4158 itemkey => ItemKey,
4159 aname => 'AMS_BUDGET_APPR_FLAG');
4160
4161 l_obj_type := wf_engine.GetItemAttrText(
4162 itemtype => ItemType,
4163 itemkey => ItemKey,
4164 aname => 'AMS_APPROVAL_FOR_OBJECT');
4165
4166 l_obj_id := wf_engine.GetItemAttrText(
4167 itemtype => ItemType,
4168 itemkey => ItemKey,
4169 aname => 'AMS_ACT_ID');
4170 l_obj_version_number := wf_engine.GetItemAttrText(
4171 itemtype => ItemType,
4172 itemkey => ItemKey,
4173 aname => 'AMS_OBJECT_VERSION_NUMBER');
4174 l_next_stat_id := wf_engine.GetItemAttrText(
4175 itemtype => ItemType,
4176 itemkey => ItemKey,
4177 aname => 'AMS_NEW_STAT_ID');
4178 l_requester_id := wf_engine.GetItemAttrText(
4179 itemtype => ItemType,
4180 itemkey => ItemKey,
4181 aname => 'AMS_REQUESTER_ID');
4182 l_doc_type := wf_engine.GetItemAttrText(
4183 itemtype => ItemType,
4184 itemkey => ItemKey,
4185 aname => 'AMS_APPR_DOC_TYPE');
4186
4187 l_appr_type_lookup := wf_engine.GetItemAttrText(
4188 itemtype => ItemType,
4189 itemkey => ItemKey,
4190 aname => 'AMS_APPR_TYPE_LOOKUP');
4191
4192 l_approver := wf_engine.GetItemAttrText(
4193 itemtype => ItemType,
4194 itemkey => ItemKey,
4195 aname => 'AMS_APPR_USERNAME');
4196
4197 l_note := wf_engine.GetItemAttrText(
4198 itemtype => ItemType,
4199 itemkey => ItemKey,
4200 aname => 'AMS_NOTE');
4201
4202 OPEN c_sys_stat(l_next_stat_id) ;
4203 FETCH c_sys_stat INTO l_sys_stat_code ;
4204 CLOSE c_sys_stat ;
4205
4206 -- Update the Notes which Approver has Given with Approvals
4207 IF l_note IS NOT NULL THEN
4208 Update_Note(p_obj_type => l_obj_type,
4209 p_obj_id => l_obj_id,
4210 p_note => l_note,
4211 p_user => l_approver,
4212 x_msg_count => l_msg_count,
4213 x_msg_data => l_msg_data,
4214 x_return_status => l_return_status) ;
4215
4216 END IF ;
4217 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4218 Handle_Err
4219 (p_itemtype => itemtype ,
4220 p_itemkey => itemkey ,
4221 p_msg_count => l_msg_count, -- Number of error Messages
4222 p_msg_data => l_msg_data ,
4223 p_attr_name => 'AMS_ERROR_MSG'
4224 ) ;
4225 result := 'COMPLETE:ERROR' ;
4226 ELSE
4227 IF l_bud_appr_flg = 'Y' THEN
4228 -- No Need to Update the Status
4229
4230 -- Update the Attribute
4231 Update_Attribute(p_obj_type => l_obj_type,
4232 p_obj_id => l_obj_id,
4233 p_obj_attr => 'TAPL',
4234 x_msg_count => l_msg_count,
4235 x_msg_data => l_msg_data,
4236 x_return_status => l_return_status ) ;
4237
4238 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4239 result := 'COMPLETE:SUCCESS' ;
4240 ELSE
4241 Handle_Err
4242 (p_itemtype => itemtype ,
4243 p_itemkey => itemkey ,
4244 p_msg_count => l_msg_count, -- Number of error Messages
4245 p_msg_data => l_msg_data ,
4246 p_attr_name => 'AMS_ERROR_MSG'
4247 ) ;
4248 result := 'COMPLETE:ERROR' ;
4249 END IF;
4250
4251 ELSE
4252 --
4253 -- Update Activity
4254 --
4255 Update_Status(p_obj_type => l_obj_type,
4256 p_obj_id => l_obj_id,
4257 p_object_version_number => l_obj_version_number,
4258 p_next_stat_code => l_sys_stat_code, --System Status
4259 p_next_stat_id => l_next_stat_id,
4260 p_appr_type => l_doc_type,
4261 p_submitted_by => l_requester_id,
4262 p_item_key => itemkey ,
4263 x_msg_count => l_msg_count,
4264 x_msg_data => l_msg_data,
4265 x_return_status => l_return_status) ;
4266
4267 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4268 -- Update the Attribute to show that Theme Approval is Completed
4269 IF l_appr_type_lookup = 'THEME' THEN
4270 Update_Attribute(p_obj_type => l_obj_type,
4271 p_obj_id => l_obj_id,
4272 p_obj_attr => 'TAPL',
4273 x_msg_count => l_msg_count,
4274 x_msg_data => l_msg_data,
4275 x_return_status => l_return_status) ;
4276 ELSIF l_appr_type_lookup = 'BOTH' THEN
4277 Update_Attribute(p_obj_type => l_obj_type,
4278 p_obj_id => l_obj_id,
4279 p_obj_attr => 'TAPL',
4280 x_msg_count => l_msg_count,
4281 x_msg_data => l_msg_data,
4282 x_return_status => l_return_status) ;
4283 Update_Attribute(p_obj_type => l_obj_type,
4284 p_obj_id => l_obj_id,
4285 p_obj_attr => 'BAPL',
4286 x_msg_count => l_msg_count,
4287 x_msg_data => l_msg_data,
4288 x_return_status => l_return_status) ;
4289
4290 END IF;
4291
4292 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4293 result := 'COMPLETE:SUCCESS' ;
4294 ELSE
4295 Handle_Err
4296 (p_itemtype => itemtype ,
4297 p_itemkey => itemkey ,
4298 p_msg_count => l_msg_count, -- Number of error Messages
4299 p_msg_data => l_msg_data ,
4300 p_attr_name => 'AMS_ERROR_MSG'
4301 ) ;
4302 result := 'COMPLETE:ERROR' ;
4303 END IF;
4304 ELSE
4305 Handle_Err
4306 (p_itemtype => itemtype ,
4307 p_itemkey => itemkey ,
4308 p_msg_count => l_msg_count, -- Number of error Messages
4309 p_msg_data => l_msg_data ,
4310 p_attr_name => 'AMS_ERROR_MSG'
4311 ) ;
4312 result := 'COMPLETE:ERROR' ;
4313 END IF ;
4314 END IF ;
4315 END IF;
4316
4317
4318 END IF ;
4319
4320 -- CANCEL mode - Normal Process Execution
4321 IF (funcmode = 'CANCEL')
4322 THEN
4323 result := 'COMPLETE:' ;
4324 RETURN;
4325 END IF;
4326
4327 -- TIMEOUT mode - Normal Process Execution
4328 IF (funcmode = 'TIMEOUT')
4329 THEN
4330 result := 'COMPLETE:' ;
4331 RETURN;
4332 END IF;
4333 EXCEPTION
4334 WHEN OTHERS THEN
4335 wf_core.context(G_PKG_NAME,'Update_Stat_ApprTA',itemtype,itemkey,actid,funcmode);
4336 raise ;
4337 END Update_Stat_ApprTA ;
4338
4339
4340 -- Start of Comments
4341 --
4342 -- NAME
4343 -- Update_Status_Rej
4344 --
4345 -- PURPOSE
4346 -- This Procedure will Update the Status of the Activity for Rejection
4347 --
4348 -- IN
4349 -- Itemtype - AMSAPPR
4350 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4351 -- Accid - Activity ID
4352 -- Funmode - Run/Cancel/Timeout
4353 --
4354 -- OUT
4355 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4356 -- COMPLETE:AMS_ERROR If the Process is errored out.
4357 --
4358 -- Used By Activities
4359 -- Item Type - AMSAPPR
4360 -- Activity - AMS_UPDATE_STATUS_REJ
4361 --
4362 -- NOTES
4363 --
4364 --
4365 -- HISTORY
4366 -- 08/20/1999 ptendulk created
4367 -- 11/30/1999 ptendulk Modified
4368 -- End of Comments
4369
4370 PROCEDURE Update_Status_Rej (itemtype IN VARCHAR2,
4371 itemkey IN VARCHAR2,
4372 actid IN NUMBER,
4373 funcmode IN VARCHAR2,
4374 result OUT NOCOPY VARCHAR2) IS
4375 l_doc_type VARCHAR2(30);
4376 l_obj_type VARCHAR2(30);
4377 l_tmp_stat_code VARCHAR2(30);
4378 l_sys_stat_code VARCHAR2(30);
4379
4380 l_msg_count NUMBER ;
4381 l_msg_data VARCHAR2(2000);
4382 l_obj_id NUMBER ;
4383 l_obj_version_number NUMBER ;
4384 l_requester_id NUMBER ;
4385 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4386 l_approver VARCHAR2(30);
4387 l_note VARCHAR2(2000);
4388 BEGIN
4389 IF (AMS_DEBUG_HIGH_ON) THEN
4390
4391 AMS_Utility_PVT.debug_message('Process Update Status_rej');
4392 END IF;
4393 -- dbms_output.put_line('Process Update Status_rej');
4394 -- RUN mode - Normal Process Execution
4395 IF (funcmode = 'RUN')
4396 THEN
4397 l_doc_type := wf_engine.GetItemAttrText(
4398 itemtype => ItemType,
4399 itemkey => ItemKey,
4400 aname => 'AMS_APPR_DOC_TYPE');
4401
4402 l_obj_type := wf_engine.GetItemAttrText(
4403 itemtype => ItemType,
4404 itemkey => ItemKey,
4405 aname => 'AMS_APPROVAL_FOR_OBJECT');
4406
4407 l_obj_id := wf_engine.GetItemAttrText(
4408 itemtype => ItemType,
4409 itemkey => ItemKey,
4410 aname => 'AMS_ACT_ID');
4411 l_obj_version_number := wf_engine.GetItemAttrText(
4412 itemtype => ItemType,
4413 itemkey => ItemKey,
4414 aname => 'AMS_OBJECT_VERSION_NUMBER');
4415 l_requester_id := wf_engine.GetItemAttrText(
4416 itemtype => ItemType,
4417 itemkey => ItemKey,
4418 aname => 'AMS_REQUESTER_ID');
4419
4420 l_approver := wf_engine.GetItemAttrText(
4421 itemtype => ItemType,
4422 itemkey => ItemKey,
4423 aname => 'AMS_APPR_USERNAME');
4424
4425 l_note := wf_engine.GetItemAttrText(
4426 itemtype => ItemType,
4427 itemkey => ItemKey,
4428 aname => 'AMS_NOTE');
4429
4430 IF l_doc_type = 'BUDGET' THEN
4431 l_tmp_stat_code := 'REJECT_BA' ;
4432 ELSE
4433 l_tmp_stat_code := 'REJECT_TA' ;
4434 END IF ;
4435 --
4436 -- Get the Valid Code (Bubmit BA/TA) for Activity
4437 --
4438 Get_Valid_status(p_object_type => l_obj_type,
4439 p_stat_code => l_tmp_stat_code,
4440 x_sys_stat_code => l_sys_stat_code) ;
4441
4442 -- Update the Notes which Approver has Given with Rejection
4443 IF l_note IS NOT NULL THEN
4444 Update_Note(p_obj_type => l_obj_type,
4445 p_obj_id => l_obj_id,
4446 p_note => l_note,
4447 p_user => l_approver,
4448 x_msg_count => l_msg_count,
4449 x_msg_data => l_msg_data,
4450 x_return_status => l_return_status) ;
4451
4452 END IF ;
4453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4454 Handle_Err
4455 (p_itemtype => itemtype ,
4456 p_itemkey => itemkey ,
4457 p_msg_count => l_msg_count, -- Number of error Messages
4458 p_msg_data => l_msg_data ,
4459 p_attr_name => 'AMS_ERROR_MSG'
4460 ) ;
4461 result := 'COMPLETE:ERROR' ;
4462 ELSE
4463
4464 --
4465 -- Update Activity
4466 --
4467 Update_Status(p_obj_type => l_obj_type,
4468 p_obj_id => l_obj_id,
4469 p_object_version_number => l_obj_version_number,
4470 p_next_stat_code => l_sys_stat_code, --System Status
4471 p_appr_type => l_doc_type,
4472 p_submitted_by => l_requester_id,
4473 p_item_key => itemkey ,
4474 x_msg_count => l_msg_count,
4475 x_msg_data => l_msg_data,
4476 x_return_status => l_return_status) ;
4477
4478 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4479 result := 'COMPLETE:SUCCESS' ;
4480 ELSE
4481 Handle_Err
4482 (p_itemtype => itemtype ,
4483 p_itemkey => itemkey ,
4484 p_msg_count => l_msg_count, -- Number of error Messages
4485 p_msg_data => l_msg_data ,
4486 p_attr_name => 'AMS_ERROR_MSG'
4487 ) ;
4488 result := 'COMPLETE:ERROR' ;
4489 END IF ;
4490 END IF ;
4491 END IF ;
4492
4493 -- CANCEL mode - Normal Process Execution
4494 IF (funcmode = 'CANCEL')
4495 THEN
4496 result := 'COMPLETE:' ;
4497 RETURN;
4498 END IF;
4499
4500 -- TIMEOUT mode - Normal Process Execution
4501 IF (funcmode = 'TIMEOUT')
4502 THEN
4503 result := 'COMPLETE:' ;
4504 RETURN;
4505 END IF;
4506 EXCEPTION
4507 WHEN OTHERS THEN
4508 wf_core.context(G_PKG_NAME,'Update_Status_Rej',itemtype,itemkey,actid,funcmode);
4509 raise ;
4510 END Update_Status_Rej ;
4511
4512 -- Start of Comments
4513 --
4514 -- NAME
4515 -- Revert_Status
4516 --
4517 -- PURPOSE
4518 -- This Procedure will Revert the Status of the Activity Back to Original
4519 --
4520 -- IN
4521 -- Itemtype - AMSAPPR
4522 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4523 -- Accid - Activity ID
4524 -- Funmode - Run/Cancel/Timeout
4525 --
4526 -- OUT
4527 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4528 -- COMPLETE:AMS_ERROR If the Process is errored out.
4529 --
4530 -- Used By Activities
4531 -- Item Type - AMSAPPR
4532 -- Activity - AMS_REVERT_STATUS
4533 --
4534 -- NOTES
4535 --
4536 --
4537 -- HISTORY
4538 -- 08/20/1999 ptendulk created
4539 -- 11/30/1999 ptendulk Modified
4540 -- End of Comments
4541
4542 PROCEDURE Revert_Status (itemtype IN VARCHAR2,
4543 itemkey IN VARCHAR2,
4544 actid IN NUMBER,
4545 funcmode IN VARCHAR2,
4546 result OUT NOCOPY VARCHAR2) IS
4547 l_obj_type VARCHAR2(30);
4548 l_tmp_stat_code VARCHAR2(30);
4549 l_doc_type VARCHAR2(30);
4550 l_requester_id NUMBER ;
4551 l_orig_stat_id NUMBER ;
4552
4553 l_msg_count NUMBER ;
4554 l_msg_data VARCHAR2(2000);
4555 l_obj_id NUMBER ;
4556 l_obj_version_number NUMBER ;
4557 l_sys_stat_code VARCHAR2(30);
4558 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4559 l_approver VARCHAR2(30);
4560 l_note VARCHAR2(2000);
4561
4562 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4563 SELECT system_status_code
4564 FROM ams_user_statuses_vl
4565 WHERE user_status_id = l_user_stat_id ;
4566
4567 BEGIN
4568 IF (AMS_DEBUG_HIGH_ON) THEN
4569
4570 AMS_Utility_PVT.debug_message('Process Revert Status');
4571 END IF;
4572 -- dbms_output.put_line('Process Revert Status');
4573 -- RUN mode - Normal Process Execution
4574 IF (funcmode = 'RUN')
4575 THEN
4576
4577 l_obj_type := wf_engine.GetItemAttrText(
4578 itemtype => ItemType,
4579 itemkey => ItemKey,
4580 aname => 'AMS_APPROVAL_FOR_OBJECT');
4581
4582 l_obj_id := wf_engine.GetItemAttrText(
4583 itemtype => ItemType,
4584 itemkey => ItemKey,
4585 aname => 'AMS_ACT_ID');
4586 l_obj_version_number := wf_engine.GetItemAttrText(
4587 itemtype => ItemType,
4588 itemkey => ItemKey,
4589 aname => 'AMS_OBJECT_VERSION_NUMBER');
4590
4591 l_orig_stat_id := wf_engine.GetItemAttrText(
4592 itemtype => ItemType,
4593 itemkey => ItemKey,
4594 aname => 'AMS_ORIG_STAT_ID');
4595 l_requester_id := wf_engine.GetItemAttrText(
4596 itemtype => ItemType,
4597 itemkey => ItemKey,
4598 aname => 'AMS_REQUESTER_ID');
4599 l_doc_type := wf_engine.GetItemAttrText(
4600 itemtype => ItemType,
4601 itemkey => ItemKey,
4602 aname => 'AMS_APPR_DOC_TYPE');
4603
4604 l_approver := wf_engine.GetItemAttrText(
4605 itemtype => ItemType,
4606 itemkey => ItemKey,
4607 aname => 'AMS_APPR_USERNAME');
4608
4609 l_note := wf_engine.GetItemAttrText(
4610 itemtype => ItemType,
4611 itemkey => ItemKey,
4612 aname => 'AMS_NOTE');
4613
4614
4615
4616 OPEN c_sys_stat(l_orig_stat_id) ;
4617 FETCH c_sys_stat INTO l_sys_stat_code ;
4618 CLOSE c_sys_stat ;
4619
4620 -- Update the Notes which Approver has Given with Rejection
4621 IF l_note IS NOT NULL THEN
4622 Update_Note(p_obj_type => l_obj_type,
4623 p_obj_id => l_obj_id,
4624 p_note => l_note,
4625 p_user => l_approver,
4626 x_msg_count => l_msg_count,
4627 x_msg_data => l_msg_data,
4628 x_return_status => l_return_status) ;
4629
4630 END IF ;
4631 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4632 Handle_Err
4633 (p_itemtype => itemtype ,
4634 p_itemkey => itemkey ,
4635 p_msg_count => l_msg_count, -- Number of error Messages
4636 p_msg_data => l_msg_data ,
4637 p_attr_name => 'AMS_ERROR_MSG'
4638 ) ;
4639 result := 'COMPLETE:ERROR' ;
4640 ELSE
4641 --
4642 -- Update Activity
4643 --
4644 Update_Status(p_obj_type => l_obj_type,
4645 p_obj_id => l_obj_id,
4646 p_object_version_number => l_obj_version_number,
4647 p_next_stat_code => l_sys_stat_code, -- System Status
4648 p_next_stat_id => l_orig_stat_id,
4649 p_appr_type => l_doc_type,
4650 p_submitted_by => l_requester_id,
4651 p_item_key => itemkey,
4652 x_msg_count => l_msg_count,
4653 x_msg_data => l_msg_data,
4654 x_return_status => l_return_status) ;
4655
4656 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4657 result := 'COMPLETE:SUCCESS' ;
4658 ELSE
4659 Handle_Err
4660 (p_itemtype => itemtype ,
4661 p_itemkey => itemkey ,
4662 p_msg_count => l_msg_count, -- Number of error Messages
4663 p_msg_data => l_msg_data ,
4664 p_attr_name => 'AMS_ERROR_MSG'
4665 ) ;
4666 result := 'COMPLETE:ERROR' ;
4667 END IF ;
4668 END IF;
4669 END IF ;
4670
4671 -- CANCEL mode - Normal Process Execution
4672 IF (funcmode = 'CANCEL')
4673 THEN
4674 result := 'COMPLETE:' ;
4675 RETURN;
4676 END IF;
4677
4678 -- TIMEOUT mode - Normal Process Execution
4679 IF (funcmode = 'TIMEOUT')
4680 THEN
4681 result := 'COMPLETE:' ;
4682 RETURN;
4683 END IF;
4684 EXCEPTION
4685 WHEN OTHERS THEN
4686 wf_core.context(G_PKG_NAME,'Revert_Status',itemtype,itemkey,actid,funcmode);
4687 raise ;
4688 END Revert_Status ;
4689
4690 -- Start of Comments
4691 --
4692 -- NAME
4693 -- Fund_Appr_Req_Check
4694 --
4695 -- PURPOSE
4696 -- This Procedure will check whether the Budget Approval is required or not
4697 --
4698 -- Itemtype - AMSAPPR
4699 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4700 -- Accid - Activity ID
4701 -- Funmode - Run/Cancel/Timeout
4702 --
4703 -- OUT
4704 -- Result - 'COMPLETE:Y' If the approval is required
4705 -- - 'COMPLETE:N' If the approval is not required
4706 --
4707 -- Used By Activities
4708 -- Item Type - AMSAPPR
4709 -- Activity - AMS_CHECK_BUD_APPR
4710 --
4711 -- NOTES
4712 --
4713 --
4714 -- HISTORY
4715 -- 08/20/1999 ptendulk created
4716 -- 11/30/1999 ptendulk Modified
4717 -- End of Comments
4718
4719 PROCEDURE Fund_Appr_Req_Check (itemtype IN VARCHAR2,
4720 itemkey IN VARCHAR2,
4721 actid IN NUMBER,
4722 funcmode IN VARCHAR2,
4723 result OUT NOCOPY VARCHAR2) IS
4724 l_budget_appr_flag VARCHAR2(1);
4725 BEGIN
4726 IF (AMS_DEBUG_HIGH_ON) THEN
4727
4728 AMS_Utility_PVT.debug_message('Process Fund Appr Req_Check');
4729 END IF;
4730 -- dbms_output.put_line('Process Fund Appr Req_Check');
4731 -- RUN mode - Normal Process Execution
4732 IF (funcmode = 'RUN')
4733 THEN
4734 l_budget_appr_flag := WF_ENGINE.GetItemAttrText(
4735 itemtype => itemtype,
4736 itemkey => itemkey ,
4737 aname => 'AMS_BUDGET_APPR_FLAG');
4738 IF l_budget_appr_flag = 'Y' THEN
4739 result := 'COMPLETE:Y' ;
4740 ELSE
4741 result := 'COMPLETE:N' ;
4742 END IF;
4743
4744 END IF;
4745
4746 -- CANCEL mode - Normal Process Execution
4747 IF (funcmode = 'CANCEL')
4748 THEN
4749 result := 'COMPLETE:' ;
4750 RETURN;
4751 END IF;
4752
4753 -- TIMEOUT mode - Normal Process Execution
4754 IF (funcmode = 'TIMEOUT')
4755 THEN
4756 result := 'COMPLETE:' ;
4757 RETURN;
4758 END IF;
4759 -- dbms_output.put_line('End Update log');
4760 EXCEPTION
4761 WHEN OTHERS THEN
4762 wf_core.context(G_PKG_NAME,'Fund_Appr_Req_Check',itemtype,itemkey,actid,funcmode);
4763 raise ;
4764 END Fund_Appr_Req_Check ;
4765
4766
4767
4768 -- Start of Comments
4769 --
4770 -- NAME
4771 -- Ba_Owner_Appr_Check
4772 --
4773 -- PURPOSE
4774 -- This Procedure will check whether the Owner's Approval is required for the Budget
4775 --
4776 -- IN
4777 -- Itemtype - AMSAPPR
4778 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4779 -- Accid - Activity ID
4780 -- Funmode - Run/Cancel/Timeout
4781 --
4782 -- OUT
4783 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
4784 -- - 'COMPLETE:N' If the Owner's Approval is not required
4785 --
4786 --
4787 -- OUT
4788 --
4789 -- Used By Activities
4790 -- Item Type - AMSAPPR
4791 -- Activity - AMS_CHECK_BA_OWN_APPR
4792 -- NOTES
4793 --
4794 --
4795 -- HISTORY
4796 -- 09/13/1999 ptendulk created
4797 -- 11/30/1999 ptendulk Modified
4798 -- End of Comments
4799
4800 PROCEDURE Ba_Owner_Appr_Check (itemtype IN VARCHAR2,
4801 itemkey IN VARCHAR2,
4802 actid IN NUMBER,
4803 funcmode IN VARCHAR2,
4804 result OUT NOCOPY VARCHAR2) IS
4805 l_ba_appr_flag VARCHAR2(1);
4806 l_owner VARCHAR2(100);
4807 BEGIN
4808 IF (AMS_DEBUG_HIGH_ON) THEN
4809
4810 AMS_Utility_PVT.debug_message('Process BA_OWNER_APPR_CHECK');
4811 END IF;
4812 -- dbms_output.put_line('Process BA_OWNER_APPR_CHECK');
4813 -- RUN mode - Normal Process Execution
4814 IF (funcmode = 'RUN')
4815 THEN
4816 l_ba_appr_flag := WF_ENGINE.GetItemAttrText(
4817 itemtype => itemtype,
4818 itemkey => itemkey ,
4819 aname => 'AMS_TAOWNER_APPR_FLAG');
4820 l_owner := WF_ENGINE.GetItemAttrText(
4821 itemtype => itemtype,
4822 itemkey => itemkey ,
4823 aname => 'AMS_OWNER');
4824
4825 IF l_ba_appr_flag = 'Y' THEN
4826
4827 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4828 itemkey => itemkey,
4829 aname => 'AMS_APPR_USERNAME',
4830 avalue => l_owner);
4831
4832 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4833 itemkey => itemkey,
4834 aname => 'AMS_BUDGET_APPR_FLAG',
4835 avalue => 'N');
4836 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4837 itemkey => itemkey,
4838 aname => 'AMS_TAOWNER_APPR_FLAG',
4839 avalue => 'N');
4840 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4841 itemkey => itemkey,
4842 aname => 'AMS_BAOWNER_APPR_FLAG',
4843 avalue => 'N');
4844 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4845 itemkey => itemkey,
4846 aname => 'AMS_THEME_APPR_FLAG',
4847 avalue => 'Y');
4848 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4849 itemkey => itemkey,
4850 aname => 'AMS_APPR_DOC_TYPE',
4851 avalue => 'BUDGET');
4852
4853 result := 'COMPLETE:Y' ;
4854 ELSE -- IF l_ba_appr_flag = 'N' THEN
4855 result := 'COMPLETE:N' ;
4856 END IF;
4857 END IF;
4858
4859 -- CANCEL mode - Normal Process Execution
4860 IF (funcmode = 'CANCEL')
4861 THEN
4862 result := 'COMPLETE:' ;
4863 RETURN;
4864 END IF;
4865
4866 -- TIMEOUT mode - Normal Process Execution
4867 IF (funcmode = 'TIMEOUT')
4868 THEN
4869 result := 'COMPLETE:' ;
4870 RETURN;
4871 END IF;
4872 -- dbms_output.put_line('End Update log');
4873 EXCEPTION
4874 WHEN OTHERS THEN
4875 wf_core.context(G_PKG_NAME,'Ba_Owner_Appr_Check',itemtype,itemkey,actid,funcmode);
4876 raise ;
4877 END Ba_Owner_Appr_Check;
4878
4879
4880 -- Start of Comments
4881 --
4882 -- NAME
4883 -- Update_Stat_ApprBA
4884 --
4885 -- PURPOSE
4886 -- This Procedure will Update the Status of the Activity for Approval
4887 --
4888 -- IN
4889 -- Itemtype - AMSAPPR
4890 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4891 -- Accid - Activity ID
4892 -- Funmode - Run/Cancel/Timeout
4893 --
4894 -- OUT
4895 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4896 -- COMPLETE:AMS_ERROR If the Process is errored out.
4897 --
4898 -- Used By Activities
4899 -- Item Type - AMSAPPR
4900 -- Activity - AMS_UPDATE_STATUS_BA
4901 --
4902 -- NOTES
4903 --
4904 --
4905 -- HISTORY
4906 -- 08/20/1999 ptendulk created
4907 -- 11/30/1999 ptendulk Modified
4908 -- End of Comments
4909
4910 PROCEDURE Update_Stat_ApprBA (itemtype IN VARCHAR2,
4911 itemkey IN VARCHAR2,
4912 actid IN NUMBER,
4913 funcmode IN VARCHAR2,
4914 result OUT NOCOPY VARCHAR2) IS
4915
4916 l_obj_type VARCHAR2(30);
4917 l_next_stat_id NUMBER ;
4918
4919 l_msg_count NUMBER ;
4920 l_msg_data VARCHAR2(2000);
4921 l_obj_id NUMBER ;
4922 l_obj_version_number NUMBER ;
4923 l_requester_id NUMBER ;
4924 l_doc_type VARCHAR2(30);
4925 l_sys_stat_code VARCHAR2(30);
4926 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4927 l_approver VARCHAR2(30);
4928 l_note VARCHAR2(2000);
4929
4930 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4931 SELECT system_status_code
4932 FROM ams_user_statuses_vl
4933 WHERE user_status_id = l_user_stat_id ;
4934
4935 BEGIN
4936 IF (AMS_DEBUG_HIGH_ON) THEN
4937
4938 AMS_Utility_PVT.debug_message('Process Update Status APPRBA');
4939 END IF;
4940 -- dbms_output.put_line('Process Update Status APPRBA');
4941 -- RUN mode - Normal Process Execution
4942 IF (funcmode = 'RUN')
4943 THEN
4944
4945 l_obj_type := wf_engine.GetItemAttrText(
4946 itemtype => ItemType,
4947 itemkey => ItemKey,
4948 aname => 'AMS_APPROVAL_FOR_OBJECT');
4949
4950 l_obj_id := wf_engine.GetItemAttrText(
4951 itemtype => ItemType,
4952 itemkey => ItemKey,
4953 aname => 'AMS_ACT_ID');
4954 l_obj_version_number := wf_engine.GetItemAttrText(
4955 itemtype => ItemType,
4956 itemkey => ItemKey,
4957 aname => 'AMS_OBJECT_VERSION_NUMBER');
4958 l_next_stat_id := wf_engine.GetItemAttrText(
4959 itemtype => ItemType,
4960 itemkey => ItemKey,
4961 aname => 'AMS_NEW_STAT_ID');
4962 l_requester_id := wf_engine.GetItemAttrText(
4963 itemtype => ItemType,
4964 itemkey => ItemKey,
4965 aname => 'AMS_REQUESTER_ID');
4966 l_doc_type := wf_engine.GetItemAttrText(
4967 itemtype => ItemType,
4968 itemkey => ItemKey,
4969 aname => 'AMS_APPR_DOC_TYPE');
4970
4971 OPEN c_sys_stat(l_next_stat_id) ;
4972 FETCH c_sys_stat INTO l_sys_stat_code ;
4973 CLOSE c_sys_stat ;
4974
4975
4976 l_approver := wf_engine.GetItemAttrText(
4977 itemtype => ItemType,
4978 itemkey => ItemKey,
4979 aname => 'AMS_APPR_USERNAME');
4980
4981 l_note := wf_engine.GetItemAttrText(
4982 itemtype => ItemType,
4983 itemkey => ItemKey,
4984 aname => 'AMS_NOTE');
4985
4986
4987 -- Update the Notes which Approver has Given with Rejection
4988 IF l_note IS NOT NULL THEN
4989 Update_Note(p_obj_type => l_obj_type,
4990 p_obj_id => l_obj_id,
4991 p_note => l_note,
4992 p_user => l_approver,
4993 x_msg_count => l_msg_count,
4994 x_msg_data => l_msg_data,
4995 x_return_status => l_return_status) ;
4996
4997 END IF ;
4998 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4999 Handle_Err
5000 (p_itemtype => itemtype ,
5001 p_itemkey => itemkey ,
5002 p_msg_count => l_msg_count, -- Number of error Messages
5003 p_msg_data => l_msg_data ,
5004 p_attr_name => 'AMS_ERROR_MSG'
5005 ) ;
5006 result := 'COMPLETE:ERROR' ;
5007 ELSE
5008 --
5009 -- Update Activity
5010 --
5011 Update_Status(p_obj_type => l_obj_type,
5012 p_obj_id => l_obj_id,
5013 p_object_version_number => l_obj_version_number,
5014 p_next_stat_code => l_sys_stat_code, --System Status
5015 p_next_stat_id => l_next_stat_id,
5016 p_appr_type => l_doc_type,
5017 p_submitted_by => l_requester_id,
5018 p_item_key => itemkey ,
5019 x_msg_count => l_msg_count,
5020 x_msg_data => l_msg_data,
5021 x_return_status => l_return_status) ;
5022
5023 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5024 -- Update the Attribute to show that Theme Approval is Completed
5025 Update_Attribute(p_obj_type => l_obj_type,
5026 p_obj_id => l_obj_id,
5027 p_obj_attr => 'BAPL',
5028 x_msg_count => l_msg_count,
5029 x_msg_data => l_msg_data,
5030 x_return_status => l_return_status) ;
5031
5032 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5033 result := 'COMPLETE:SUCCESS' ;
5034 ELSE
5035 Handle_Err
5036 (p_itemtype => itemtype ,
5037 p_itemkey => itemkey ,
5038 p_msg_count => l_msg_count, -- Number of error Messages
5039 p_msg_data => l_msg_data ,
5040 p_attr_name => 'AMS_ERROR_MSG'
5041 ) ;
5042 result := 'COMPLETE:ERROR' ;
5043 END IF;
5044 result := 'COMPLETE:SUCCESS' ;
5045 ELSE
5046 Handle_Err
5047 (p_itemtype => itemtype ,
5048 p_itemkey => itemkey ,
5049 p_msg_count => l_msg_count, -- Number of error Messages
5050 p_msg_data => l_msg_data ,
5051 p_attr_name => 'AMS_ERROR_MSG'
5052 ) ;
5053 result := 'COMPLETE:ERROR' ;
5054 END IF ;
5055 END IF ;
5056 END IF ;
5057
5058 -- CANCEL mode - Normal Process Execution
5059 IF (funcmode = 'CANCEL')
5060 THEN
5061 result := 'COMPLETE:' ;
5062 RETURN;
5063 END IF;
5064
5065 -- TIMEOUT mode - Normal Process Execution
5066 IF (funcmode = 'TIMEOUT')
5067 THEN
5068 result := 'COMPLETE:' ;
5069 RETURN;
5070 END IF;
5071 EXCEPTION
5072 WHEN OTHERS THEN
5073 wf_core.context(G_PKG_NAME,'Update_Stat_ApprBA',itemtype,itemkey,actid,funcmode);
5074 raise ;
5075 END Update_Stat_ApprBA ;
5076
5077
5078 -- Start of Comments
5079 --
5080 -- NAME
5081 -- AbortProcess
5082 --
5083 -- PURPOSE
5084 -- This Procedure will abort the process of Approvals
5085 --
5086 -- Used By Activities
5087 --
5088 -- NOTES
5089 --
5090 --
5091 -- HISTORY
5092 -- 09/13/1999 ptendulk created
5093 -- 11/30/1999 ptendulk Modified
5094 -- End of Comments
5095
5096 PROCEDURE AbortProcess
5097 (p_itemkey IN VARCHAR2
5098 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
5099 ,p_itemtype IN VARCHAR2 DEFAULT NULL
5100 )
5101 IS
5102 itemkey VARCHAR2(30) := p_itemkey ;
5103 itemtype VARCHAR2(30) := nvl(p_itemtype,'AMSAPPR') ;
5104 BEGIN
5105 IF (AMS_DEBUG_HIGH_ON) THEN
5106
5107 AMS_Utility_PVT.debug_message('Process Abort Process');
5108 END IF;
5109 -- dbms_output.put_line('Process Abort Process');
5110
5111 WF_ENGINE.AbortProcess (itemtype => itemtype,
5112 itemkey => itemkey ,
5113 process => p_workflowprocess);
5114
5115 -- dbms_output.put_line('After Aborting Process ');
5116 EXCEPTION
5117 WHEN OTHERS
5118 THEN
5119 wf_core.context ('AMS_WfCmpApr_PVT', 'AbortProcess',itemtype,itemkey
5120 ,p_workflowprocess);
5121 RAISE;
5122
5123 END AbortProcess;
5124
5125
5126
5127 END AMS_WfCmpApr_PVT ;
5128