[Home] [Help]
PACKAGE BODY: APPS.AMS_WFCMPAPR_PVT
Source
1 PACKAGE BODY AMS_WFCmpApr_PVT AS
2 /* $Header: amsvwcab.pls 120.3 2011/07/26 04:31:26 jmahendr 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 --Hbandi added for re-solving the BUG #7538786
3032 l_camp_start_date_txt VARCHAR2(4000);
3033 l_camp_end_date_txt VARCHAR2(4000);
3034 --End of hbandi code
3035
3036 -- Following cursors to get the owners is added by ptendulk
3037 -- on 08-Jun-2000
3038 CURSOR c_camp_owner(l_id NUMBER)
3039 IS
3040 SELECT jtf.full_name
3041 FROM ams_jtf_rs_emp_v jtf,ams_campaigns_vl camp
3042 WHERE camp.campaign_id = l_id
3043 AND jtf.resource_id = camp.owner_user_id ;
3044
3045 CURSOR c_eveh_owner(l_id NUMBER)
3046 IS
3047 SELECT jtf.full_name
3048 FROM ams_jtf_rs_emp_v jtf,ams_event_headers_vl eveh
3049 WHERE eveh.event_header_id = l_id
3050 AND jtf.resource_id = eveh.owner_user_id ;
3051
3052 CURSOR c_eveo_owner(l_id NUMBER)
3053 IS
3054 SELECT jtf.full_name
3055 FROM ams_jtf_rs_emp_v jtf,ams_event_offers_vl eveo
3056 WHERE eveo.event_offer_id = l_id
3057 AND jtf.resource_id = eveo.owner_user_id ;
3058
3059
3060 begin
3061 IF (AMS_DEBUG_HIGH_ON) THEN
3062
3063 AMS_Utility_PVT.debug_message('Create Documents');
3064 END IF;
3065
3066 -- dbms_output.put_line('Create Documents');
3067 -- parse document_id for the ':' dividing item type name from item key value
3068 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
3069 -- release 2.5 version of this demo
3070 ItemType := nvl(substr(document_id, 1, instr(document_id,':')-1),'AMSAPPR');
3071 ItemKey := substr(document_id
3072 , instr(document_id,':')+1);
3073
3074 l_approval_for := wf_engine.GetItemAttrText(
3075 itemtype => ItemType,
3076 itemkey => ItemKey,
3077 aname => 'AMS_APPROVAL_FOR_OBJECT');
3078
3079 l_approval_for_id := wf_engine.GetItemAttrText(
3080 itemtype => ItemType,
3081 itemkey => ItemKey,
3082 aname => 'AMS_ACT_ID');
3083
3084 l_requester := wf_engine.GetItemAttrText(
3085 itemtype => ItemType,
3086 itemkey => ItemKey,
3087 aname => 'AMS_REQUESTER');
3088
3089 l_requester_note := wf_engine.GetItemAttrText(
3090 itemtype => ItemType,
3091 itemkey => ItemKey,
3092 aname => 'AMS_NOTES_FROM_REQUESTER');
3093
3094 -- dbms_output.Put_line('Requester Note : '||l_requester_note );
3095 l_doc_type := wf_engine.GetItemAttrText(
3096 itemtype => ItemType,
3097 itemkey => ItemKey,
3098 aname => 'AMS_APPR_DOC_TYPE');
3099
3100 l_approval_type := wf_engine.GetItemAttrText(
3101 itemtype => ItemType,
3102 itemkey => ItemKey,
3103 aname => 'AMS_APPROVAL_TYPE');
3104
3105 l_activity_name := WF_ENGINE.GetItemAttrText
3106 (itemtype => itemtype ,
3107 itemkey => itemkey,
3108 aname => 'AMS_ACT_NAME' );
3109
3110 l_start_dt := WF_ENGINE.GetItemAttrText
3111 (itemtype => itemtype,
3112 itemkey => itemkey ,
3113 aname => 'AMS_ACT_START_DATE');
3114
3115 l_end_dt := WF_ENGINE.GetItemAttrText
3116 (itemtype => itemtype,
3117 itemkey => itemkey ,
3118 aname => 'AMS_ACT_END_DATE' );
3119
3120 l_budget_amount := WF_ENGINE.GetItemAttrText
3121 (itemtype => itemtype ,
3122 itemkey => itemkey,
3123 aname => 'AMS_BUDGET_AMOUNT');
3124 l_desc := WF_ENGINE.GetItemAttrText
3125 (itemtype => itemtype,
3126 itemkey => itemkey ,
3127 aname => 'AMS_ACT_DESC');
3128 l_currency := WF_ENGINE.GetItemAttrText
3129 (itemtype => itemtype ,
3130 itemkey => itemkey,
3131 aname => 'AMS_CURRENCY');
3132
3133 --Hbandi code for resoving the BUG #7538786 and (set this FND_FORMS_USER_CALENDAR default value to 'GREGORIAN' for the bug #8974486)
3134 --jmahendr bug fix 11824518
3135 if (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version = 1 and FND_RELEASE.POINT_VERSION >= 1 )
3136 or (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.MINOR_VERSION >= 2)
3137 or (FND_RELEASE.MAJOR_VERSION > 12)
3138 then
3139 l_camp_start_date_txt := to_char(l_start_dt,
3140 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', -1),
3141 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR',-1),'GREGORIAN') || '''');
3142 l_camp_end_date_txt := to_char(l_end_dt,
3143 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK', -1),
3144 'NLS_CALENDAR = ''' || NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR',-1),'GREGORIAN') || '''');
3145 else
3146 l_camp_start_date_txt := to_char(l_start_dt);
3147 l_camp_end_date_txt := to_char(l_end_dt);
3148 end if;
3149
3150
3151
3152 --End of hbandi Code
3153
3154
3155 -- Create an html text buffer
3156 -- if (display_type = 'text/html') then
3157 -- null;
3158 -- return;
3159 -- end if;
3160
3161 -- Create a plain text buffer
3162 -- if (display_type = 'text/plain') then
3163 IF l_approval_for = 'CAMP' THEN
3164
3165 OPEN c_camp_owner(l_approval_for_id) ;
3166 FETCH c_camp_owner INTO l_owner ;
3167 CLOSE c_camp_owner ;
3168
3169
3170 l_camp_theme := WF_ENGINE.GetItemAttrText
3171 (itemtype => itemtype ,
3172 itemkey => itemkey,
3173 aname => 'AMS_CAMP_THEME');
3174 l_camp_mkt := WF_ENGINE.GetItemAttrText
3175 (itemtype => itemtype,
3176 itemkey => itemkey ,
3177 aname => 'AMS_CAMP_MARKET');
3178 l_camp_geo := WF_ENGINE.GetItemAttrText
3179 (itemtype => itemtype ,
3180 itemkey => itemkey,
3181 aname => 'AMS_CAMP_GEO');
3182
3183
3184 IF l_doc_type = 'THEME' THEN
3185
3186
3187 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_THEME_APPROVAL');
3188 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3189
3190 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE', l_camp_start_date_txt, FALSE);
3191 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_camp_end_date_txt, FALSE);
3192
3193 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3194 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3195 FND_MESSAGE.Set_Token('CAMP_MKT', l_camp_mkt, FALSE);
3196 FND_MESSAGE.Set_Token('CAMP_GEO', l_camp_geo, FALSE);
3197 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3198 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3199 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3200 l_message := FND_MESSAGE.Get;
3201
3202 ELSIF l_doc_type = 'BUDGET' THEN
3203
3204
3205
3206 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPR');
3207 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3208 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3209 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3210 FND_MESSAGE.Set_Token('CURR', l_currency, FALSE);
3211 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE',l_camp_start_date_txt, FALSE);
3212 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_camp_end_date_txt, FALSE);
3213 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3214 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3215 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3216 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3217 l_message := FND_MESSAGE.Get;
3218 ELSIF l_doc_type = 'BOTH' THEN
3219
3220
3221 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BOTH_APPR');
3222 FND_MESSAGE.Set_Token('CAMPAIGN_NAME', l_activity_name, FALSE);
3223 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3224 FND_MESSAGE.Set_Token('CURR', l_currency, FALSE);
3225 FND_MESSAGE.Set_Token('CAMP_EXEC_START_DATE', l_camp_start_date_txt, FALSE);
3226 FND_MESSAGE.Set_Token('CAMP_EXEC_END_DATE', l_camp_end_date_txt, FALSE);
3227 FND_MESSAGE.Set_Token('PURPOSE', l_camp_theme, FALSE);
3228 FND_MESSAGE.Set_Token('CAMP_MKT', l_camp_mkt, FALSE);
3229 FND_MESSAGE.Set_Token('CAMP_GEO', l_camp_geo, FALSE);
3230 FND_MESSAGE.Set_Token('CAMP_DESC', l_desc, FALSE);
3231 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3232 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3233 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3234 l_message := FND_MESSAGE.Get;
3235
3236 END IF;
3237 ELSIF l_approval_for = 'DELV' THEN
3238 l_deli_mkt_msg := WF_ENGINE.GetItemAttrText
3239 (itemtype => itemtype,
3240 itemkey => itemkey ,
3241 aname => 'AMS_DELI_MKT_MESSAGE');
3242
3243 l_deli_language := WF_ENGINE.GetItemAttrText
3244 (itemtype => itemtype,
3245 itemkey => itemkey ,
3246 aname => 'AMS_DELI_LANGUAGE' );
3247
3248 IF l_approval_type = 'THEME' THEN
3249
3250
3251 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_THEME_APPROVAL');
3252 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3253 FND_MESSAGE.Set_Token('DELI_START_DATE',l_camp_start_date_txt, FALSE);
3254 FND_MESSAGE.Set_Token('DELI_END_DATE', l_camp_end_date_txt, FALSE);
3255 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3256 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3257 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3258 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3259 l_message := FND_MESSAGE.Get;
3260
3261 ELSIF l_approval_type = 'BUDGET' THEN
3262
3263 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_BUDGET_APPROVAL');
3264 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3265 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3266 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3267 FND_MESSAGE.Set_Token('DELI_START_DATE', l_camp_start_date_txt, FALSE);
3268 FND_MESSAGE.Set_Token('DELI_END_DATE', l_camp_end_date_txt, FALSE);
3269 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3270 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3271 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3272 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3273 l_message := FND_MESSAGE.Get;
3274 ELSIF l_approval_type = 'BOTH' THEN
3275 FND_MESSAGE.Set_Name('AMS', 'AMS_DELI_WF_NTF_THEME_APPROVAL');
3276 FND_MESSAGE.Set_Token('DELIVERABLE_NAME', l_activity_name, FALSE);
3277 FND_MESSAGE.Set_Token('DELI_START_DATE', l_camp_start_date_txt, FALSE);
3278 FND_MESSAGE.Set_Token('DELI_END_DATE', l_camp_end_date_txt, FALSE);
3279 FND_MESSAGE.Set_Token('LANGUAGE', l_deli_language, FALSE);
3280 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3281 FND_MESSAGE.Set_Token('DELI_DESC', l_desc, FALSE);
3282 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3283 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3284 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3285 l_message := FND_MESSAGE.Get;
3286
3287 END IF;
3288 ELSIF l_approval_for = 'EVEH' THEN
3289 l_eve_mkt_msg := WF_ENGINE.GetItemAttrText
3290 (itemtype => itemtype,
3291 itemkey => itemkey ,
3292 aname => 'AMS_EVE_MKT_MESSAGE');
3293 OPEN c_eveh_owner(l_approval_for_id) ;
3294 FETCH c_eveh_owner INTO l_owner ;
3295 CLOSE c_eveh_owner ;
3296
3297
3298 IF l_approval_type = 'THEME' THEN
3299 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEH_WF_NTF_THEME_APPROVAL');
3300 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3301 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_camp_start_date_txt, FALSE);
3302 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_camp_end_date_txt, FALSE);
3303 FND_MESSAGE.Set_Token('EVEH_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3304 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3305 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3306 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3307 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3308 l_message := FND_MESSAGE.Get;
3309
3310 ELSIF l_approval_type = 'BUDGET' THEN
3311 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPROVAL');
3312 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3313 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_camp_start_date_txt, FALSE);
3314 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_camp_end_date_txt, FALSE);
3315 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3316 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3317 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3318 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3319 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3320 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3321 l_message := FND_MESSAGE.Get;
3322
3323 ELSIF l_approval_type = 'BOTH' THEN
3324 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEH_WF_NTF_THEME_APPROVAL');
3325 FND_MESSAGE.Set_Token('EVENT_HEADER_NAME', l_activity_name, FALSE);
3326 FND_MESSAGE.Set_Token('EVEH_START_DATE', l_camp_start_date_txt, FALSE);
3327 FND_MESSAGE.Set_Token('EVEH_END_DATE', l_camp_end_date_txt, FALSE);
3328 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3329 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3330 FND_MESSAGE.Set_Token('EVEH_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3331 FND_MESSAGE.Set_Token('EVEH_DESC', l_desc, FALSE);
3332 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3333 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3334 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3335 l_message := FND_MESSAGE.Get;
3336
3337 END IF;
3338 ELSIF l_approval_for = 'EVEO' THEN
3339
3340 l_eve_mkt_msg := WF_ENGINE.GetItemAttrText
3341 (itemtype => itemtype,
3342 itemkey => itemkey ,
3343 aname => 'AMS_EVE_MKT_MESSAGE');
3344 OPEN c_eveo_owner(l_approval_for_id) ;
3345 FETCH c_eveo_owner INTO l_owner ;
3346 CLOSE c_eveo_owner ;
3347
3348
3349 IF l_approval_type = 'THEME' THEN
3350 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEO_WF_NTF_THEME_APPROVAL');
3351 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3352 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_camp_start_date_txt, FALSE);
3353 FND_MESSAGE.Set_Token('EVEO_END_DATE', l_camp_end_date_txt, FALSE);
3354 FND_MESSAGE.Set_Token('EVEO_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3355 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3356 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3357 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3358 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3359 l_message := FND_MESSAGE.Get;
3360
3361 ELSIF l_approval_type = 'BUDGET' THEN
3362 FND_MESSAGE.Set_Name('AMS', 'AMS_CAMP_WF_NTF_BUDGET_APPROVAL');
3363 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3364 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3365 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3366 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_camp_start_date_txt, FALSE);
3367 FND_MESSAGE.Set_Token('EVEO_END_DATE',l_camp_end_date_txt, FALSE);
3368 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3369 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3370 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3371 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3372 l_message := FND_MESSAGE.Get;
3373
3374 ELSIF l_approval_type = 'BOTH' THEN
3375 FND_MESSAGE.Set_Name('AMS', 'AMS_EVEO_WF_NTF_THEME_APPROVAL');
3376 FND_MESSAGE.Set_Token('EVENT_OFFER_NAME', l_activity_name, FALSE);
3377 FND_MESSAGE.Set_Token('EVEO_START_DATE', l_camp_start_date_txt, FALSE);
3378 FND_MESSAGE.Set_Token('EVEO_END_DATE', l_camp_end_date_txt, FALSE);
3379 FND_MESSAGE.Set_Token('EVEO_MARKETING_MSG', l_eve_mkt_msg, FALSE);
3380 FND_MESSAGE.Set_Token('BUDGET_AMOUNT', l_budget_amount, FALSE);
3381 FND_MESSAGE.Set_Token('CURRENCY', l_currency, FALSE);
3382 FND_MESSAGE.Set_Token('EVEO_DESC', l_desc, FALSE);
3383 FND_MESSAGE.Set_Token('REQUESTER', l_requester, FALSE);
3384 FND_MESSAGE.Set_Token('NOTE', l_requester_note, FALSE);
3385 FND_MESSAGE.Set_Token('OWNER', l_owner, FALSE);
3386 l_message := FND_MESSAGE.Get;
3387
3388 END IF;
3389 END IF ;
3390
3391
3392 document := document|| l_message ;
3393
3394 document_type := 'text/plain';
3395
3396 RETURN;
3397 -- END IF;
3398
3399 EXCEPTION
3400 WHEN OTHERS THEN
3401 wf_core.context('AMS_WfCmpapr_PVT','Create_Notif_Document',itemtype,itemkey);
3402 RAISE;
3403 --actid,
3404 END Create_Notif_Document;
3405
3406 -- Start of Comments
3407 --
3408 -- NAME
3409 -- Prepare_Doc
3410 --
3411 -- PURPOSE
3412 -- This Procedure will create the Document to be sent for the Approvals
3413 -- it will also Update the Status As the Activity as Submitted for Approvals
3414 --
3415 -- IN
3416 -- Itemtype - AMSAPPR
3417 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
3418 -- Accid - Activity ID
3419 -- Funmode - Run/Cancel/Timeout
3420 --
3421 -- OUT
3422 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
3423 -- COMPLETE:AMS_ERROR If the Process is errored out.
3424 --
3425 -- Used By Activities
3426 -- Item Type - AMSAPPR
3427 -- Activity - AMS_PREPARE_DOC
3428 --
3429 -- NOTES
3430 --
3431 --
3432 -- HISTORY
3433 -- 08/20/1999 ptendulk created
3434 -- 11/30/1999 ptendulk Modified
3435 -- End of Comments
3436
3437 PROCEDURE Prepare_Doc (itemtype IN VARCHAR2,
3438 itemkey IN VARCHAR2,
3439 actid IN NUMBER,
3440 funcmode IN VARCHAR2,
3441 result OUT NOCOPY VARCHAR2) IS
3442
3443 l_obj_type VARCHAR2(30);
3444 l_tmp_stat_code VARCHAR2(30);
3445 l_sys_stat_code VARCHAR2(30);
3446
3447 l_msg_count NUMBER ;
3448 l_msg_data VARCHAR2(2000);
3449 l_obj_id NUMBER ;
3450 l_obj_version_number NUMBER ;
3451 l_requester_id NUMBER ;
3452 l_doc_type VARCHAR2(30) ;
3453 l_return_status VARCHAR2(1);
3454
3455 l_requester VARCHAR2(30);
3456 l_approval_for VARCHAR2(30);
3457 l_approval_for_id NUMBER;
3458 l_approval_type VARCHAR2(80);
3459 l_timeout NUMBER ;
3460 l_priority NUMBER;
3461
3462 l_activity_name VARCHAR2(240);
3463
3464 l_start_dt DATE;
3465 l_end_dt DATE;
3466 l_desc VARCHAR2(2000);
3467 l_budget_amount NUMBER;
3468 l_currency_code VARCHAR2(30);
3469 l_currency VARCHAR2(80);
3470
3471 -- Campaign Variables
3472 CURSOR C_campaign(l_my_campaign_id VARCHAR2
3473 ) IS
3474 SELECT camp.campaign_name,
3475 camp.actual_exec_start_date,
3476 camp.actual_exec_end_date,
3477 camp.description,
3478 camp.transaction_currency_code,
3479 camp.budget_amount_tc
3480 FROM ams_campaigns_vl camp
3481 WHERE camp.campaign_id = l_my_campaign_id ;
3482
3483 CURSOR C_camp_geo(l_my_campaign_id VARCHAR2
3484 ) IS
3485 SELECT geo_area_name
3486 FROM ams_act_geo_areas_v
3487 WHERE act_geo_area_user = 'CAMP'
3488 AND act_geo_area_user_id = l_my_campaign_id ;
3489
3490 CURSOR C_camp_mkt(l_my_campaign_id VARCHAR2
3491 ) IS
3492 SELECT mkt.cell_name
3493 FROM ams_act_market_segments act,ams_cells_vl mkt
3494 WHERE act.arc_act_market_segment_used_by = 'CAMP'
3495 AND act.act_market_segment_used_by_id = l_my_campaign_id
3496 AND mkt.cell_id = act.market_segment_id ;
3497
3498 CURSOR C_theme(l_my_campaign_id IN NUMBER,
3499 l_my_obj_type IN VARCHAR2)
3500 IS
3501 SELECT m.message_name
3502 FROM ams_messages_vl m,ams_act_messages a
3503 WHERE a.message_used_by_id = l_my_campaign_id
3504 AND a.message_used_by = l_my_obj_type
3505 AND a.message_id = m.message_id ;
3506
3507 CURSOR c_currency(l_cur_code IN VARCHAR2)
3508 IS
3509 SELECT name
3510 FROM fnd_currencies_vl
3511 WHERE currency_code = l_cur_code ;
3512
3513 l_tmp_str VARCHAR2(2000);
3514
3515
3516
3517 l_camp_theme VARCHAR2(4000);
3518 l_camp_obj VARCHAR2(4000);
3519 l_camp_mkt VARCHAR2(4000);
3520 l_camp_geo VARCHAR2(4000);
3521
3522
3523 -- Deliverables Variables
3524 CURSOR C_deliverable(l_my_deliverable_id VARCHAR2 )
3525 IS
3526 SELECT dl.deliverable_name deliverable_name,
3527 dl.actual_avail_from_date actual_avail_from_date,
3528 dl.actual_avail_to_date actual_avail_to_date,
3529 dl.description description,
3530 lg.nls_language nls_language,
3531 dl.transaction_currency_code transaction_currency_code,
3532 dl.budget_amount_tc budget_amount_tc
3533 FROM ams_deliverables_vl dl,fnd_languages lg
3534 WHERE dl.deliverable_id = l_my_deliverable_id
3535 AND dl.language_code = lg.language_code ;
3536
3537
3538 l_deli_language VARCHAR2(30);
3539 l_deli_mkt_msg VARCHAR2(4000);
3540
3541 -- events variables
3542
3543 CURSOR C_event_header(l_my_event_header_id VARCHAR2
3544 ) IS
3545 SELECT event_header_name,
3546 active_from_date,
3547 active_to_date,
3548 description,
3549 fund_amount_tc
3550 ,currency_code_tc
3551 FROM ams_event_headers_vl
3552 WHERE event_header_id = l_my_event_header_id ;
3553
3554 l_eve_mkt_msg VARCHAR2(4000);
3555
3556 CURSOR C_event_offer(l_my_event_offer_id VARCHAR2 )
3557 IS
3558 SELECT o.event_offer_name,
3559 o.event_start_date,
3560 o.event_end_date,
3561 o.description,
3562 o.fund_amount_tc
3563 ,o.currency_code_tc
3564 FROM ams_event_offers_vl o
3565 WHERE o.event_offer_id = l_my_event_offer_id ;
3566
3567
3568
3569 BEGIN
3570 IF (AMS_DEBUG_HIGH_ON) THEN
3571
3572 AMS_Utility_PVT.debug_message('Prepare Documents');
3573 END IF;
3574 -- RUN mode - Normal Process Execution
3575 IF (funcmode = 'RUN')
3576 THEN
3577 -- Create the Notification Document
3578
3579 l_approval_for := wf_engine.GetItemAttrText(
3580 itemtype => ItemType,
3581 itemkey => ItemKey,
3582 aname => 'AMS_APPROVAL_FOR_OBJECT');
3583
3584 l_approval_for_id := wf_engine.GetItemAttrText(
3585 itemtype => ItemType,
3586 itemkey => ItemKey,
3587 aname => 'AMS_ACT_ID');
3588
3589 l_requester := wf_engine.GetItemAttrText(
3590 itemtype => ItemType,
3591 itemkey => ItemKey,
3592 aname => 'AMS_REQUESTER');
3593
3594 l_doc_type := wf_engine.GetItemAttrText(
3595 itemtype => ItemType,
3596 itemkey => ItemKey,
3597 aname => 'AMS_APPR_DOC_TYPE');
3598
3599 l_approval_type := Get_Lookup_Meaning('AMS_APPROVAL_TYPE',l_doc_type);
3600
3601 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3602 itemkey => itemkey,
3603 aname => 'AMS_APPROVAL_TYPE',
3604 avalue => l_approval_type );
3605
3606 Find_Priority(p_obj_type => l_approval_for,
3607 p_obj_id => l_approval_for_id,
3608 p_approval_type => l_doc_type,
3609 x_timeout_days => l_timeout,
3610 x_priority => l_priority) ;
3611
3612
3613 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3614 itemkey => itemkey,
3615 aname => 'AMS_TIMEOUT',
3616 avalue => l_timeout );
3617
3618 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3619 itemkey => itemkey,
3620 aname => 'AMS_PRIORITY',
3621 avalue => l_priority );
3622
3623
3624 IF l_approval_for = 'CAMP' THEN
3625
3626 OPEN c_campaign(l_approval_for_id) ;
3627 FETCH c_campaign
3628 INTO l_activity_name,l_start_dt,l_end_dt,
3629 l_desc,l_currency_code,l_budget_amount ;
3630 CLOSE c_campaign;
3631
3632 OPEN C_theme(l_approval_for_id,'CAMP') ;
3633 FETCH C_theme INTO l_camp_theme ;
3634 LOOP
3635 FETCH C_theme INTO l_tmp_str ;
3636 EXIT WHEN C_theme%NOTFOUND ;
3637 l_camp_theme := l_camp_theme ||', '||l_tmp_str ;
3638 END LOOP;
3639 CLOSE C_theme ;
3640
3641 OPEN c_camp_geo(l_approval_for_id) ;
3642 FETCH c_camp_geo INTO l_camp_geo ;
3643 LOOP
3644 FETCH C_camp_geo INTO l_tmp_str ;
3645 EXIT WHEN C_camp_geo%NOTFOUND ;
3646 l_camp_geo := l_camp_geo ||', '||l_tmp_str ;
3647 END LOOP;
3648 CLOSE c_camp_geo ;
3649
3650 OPEN c_camp_mkt(l_approval_for_id) ;
3651 FETCH c_camp_mkt INTO l_camp_mkt ;
3652 LOOP
3653 FETCH c_camp_mkt INTO l_tmp_str ;
3654 EXIT WHEN c_camp_mkt%NOTFOUND ;
3655 l_camp_mkt := l_camp_mkt ||', '||l_tmp_str ;
3656 END LOOP;
3657 CLOSE c_camp_mkt ;
3658
3659 OPEN c_currency(l_currency_code);
3660 FETCH c_currency INTO l_currency ;
3661 CLOSE c_currency ;
3662
3663
3664 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3665 itemkey => itemkey,
3666 aname => 'AMS_ACT_NAME',
3667 avalue => l_activity_name );
3668 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3669 itemkey => itemkey ,
3670 aname => 'AMS_ACT_START_DATE',
3671 avalue => l_start_dt );
3672 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3673 itemkey => itemkey ,
3674 aname => 'AMS_ACT_END_DATE',
3675 avalue => l_end_dt );
3676 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3677 itemkey => itemkey,
3678 aname => 'AMS_CAMP_THEME',
3679 avalue => l_camp_theme );
3680
3681 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3682 itemkey => itemkey ,
3683 aname => 'AMS_CAMP_MARKET',
3684 avalue => l_camp_mkt );
3685 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3686 itemkey => itemkey,
3687 aname => 'AMS_CAMP_GEO',
3688 avalue => l_camp_geo );
3689 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3690 itemkey => itemkey,
3691 aname => 'AMS_BUDGET_AMOUNT',
3692 avalue => l_budget_amount );
3693 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3694 itemkey => itemkey ,
3695 aname => 'AMS_ACT_DESC',
3696 avalue => l_desc );
3697
3698 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3699 itemkey => itemkey,
3700 aname => 'AMS_CURRENCY',
3701 avalue => l_currency );
3702
3703
3704 ELSIF l_approval_for = 'DELV' THEN
3705 OPEN c_deliverable(l_approval_for_id) ;
3706 FETCH c_deliverable INTO l_activity_name,l_start_dt,
3707 l_end_dt,l_desc,l_deli_language,l_currency_code,l_budget_amount ;
3708 CLOSE c_deliverable;
3709
3710 OPEN c_currency(l_currency_code);
3711 FETCH c_currency INTO l_currency ;
3712 CLOSE c_currency ;
3713
3714 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3715 itemkey => itemkey,
3716 aname => 'AMS_ACT_NAME',
3717 avalue => l_activity_name );
3718 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3719 itemkey => itemkey ,
3720 aname => 'AMS_ACT_START_DATE',
3721 avalue => l_start_dt );
3722 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3723 itemkey => itemkey ,
3724 aname => 'AMS_ACT_END_DATE',
3725 avalue => l_end_dt );
3726 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3727 itemkey => itemkey ,
3728 aname => 'AMS_DELI_LANGUAGE',
3729 avalue => l_deli_language );
3730 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3731 itemkey => itemkey ,
3732 aname => 'AMS_ACT_DESC',
3733 avalue => l_desc );
3734 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3735 itemkey => itemkey,
3736 aname => 'AMS_BUDGET_AMOUNT',
3737 avalue => l_budget_amount );
3738
3739 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3740 itemkey => itemkey,
3741 aname => 'AMS_CURRENCY',
3742 avalue => l_currency );
3743 ELSIF l_approval_for = 'EVEH' THEN
3744
3745 OPEN c_event_header(l_approval_for_id);
3746 FETCH c_event_header
3747 INTO l_activity_name,l_start_dt,l_end_dt,l_desc,l_budget_amount,l_currency_code ;
3748 CLOSE c_event_header;
3749
3750 OPEN c_currency(l_currency_code);
3751 FETCH c_currency INTO l_currency ;
3752 CLOSE c_currency ;
3753
3754 OPEN C_theme(l_approval_for_id,'EVEH') ;
3755 FETCH C_theme INTO l_eve_mkt_msg ;
3756 LOOP
3757 FETCH C_theme INTO l_tmp_str ;
3758 EXIT WHEN C_theme%NOTFOUND ;
3759 l_eve_mkt_msg := l_eve_mkt_msg ||', '||l_tmp_str ;
3760 END LOOP;
3761 CLOSE C_theme ;
3762
3763 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3764 itemkey => itemkey,
3765 aname => 'AMS_ACT_NAME',
3766 avalue => l_activity_name );
3767 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3768 itemkey => itemkey ,
3769 aname => 'AMS_ACT_START_DATE',
3770 avalue => l_start_dt );
3771 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3772 itemkey => itemkey ,
3773 aname => 'AMS_ACT_END_DATE',
3774 avalue => l_end_dt );
3775 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3776 itemkey => itemkey ,
3777 aname => 'AMS_EVE_MKT_MESSAGE',
3778 avalue => l_eve_mkt_msg );
3779 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3780 itemkey => itemkey,
3781 aname => 'AMS_BUDGET_AMOUNT',
3782 avalue => l_budget_amount );
3783 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3784 itemkey => itemkey ,
3785 aname => 'AMS_ACT_DESC',
3786 avalue => l_desc );
3787 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3788 itemkey => itemkey,
3789 aname => 'AMS_CURRENCY',
3790 avalue => l_currency );
3791 ELSIF l_approval_for = 'EVEO' THEN
3792
3793 OPEN c_event_offer(l_approval_for_id) ;
3794 FETCH c_event_offer
3795 INTO l_activity_name,l_start_dt,l_end_dt,l_desc,l_budget_amount,l_currency_code;
3796 -- , l_currency ;
3797 CLOSE c_event_offer;
3798
3799 OPEN c_currency(l_currency_code);
3800 FETCH c_currency INTO l_currency ;
3801 CLOSE c_currency ;
3802
3803 OPEN C_theme(l_approval_for_id,'EVEO') ;
3804 FETCH C_theme INTO l_eve_mkt_msg ;
3805 LOOP
3806 FETCH C_theme INTO l_tmp_str ;
3807 EXIT WHEN C_theme%NOTFOUND ;
3808 l_eve_mkt_msg := l_eve_mkt_msg ||', '||l_tmp_str ;
3809 END LOOP;
3810 CLOSE C_theme ;
3811
3812 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3813 itemkey => itemkey,
3814 aname => 'AMS_ACT_NAME',
3815 avalue => l_activity_name );
3816 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3817 itemkey => itemkey ,
3818 aname => 'AMS_ACT_START_DATE',
3819 avalue => l_start_dt );
3820 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3821 itemkey => itemkey ,
3822 aname => 'AMS_ACT_END_DATE',
3823 avalue => l_end_dt );
3824 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3825 itemkey => itemkey ,
3826 aname => 'AMS_EVE_MKT_MESSAGE',
3827 avalue => l_eve_mkt_msg );
3828 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3829 itemkey => itemkey,
3830 aname => 'AMS_BUDGET_AMOUNT',
3831 avalue => l_budget_amount );
3832 WF_ENGINE.SetItemAttrText(itemtype => itemtype,
3833 itemkey => itemkey ,
3834 aname => 'AMS_ACT_DESC',
3835 avalue => l_desc );
3836 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
3837 itemkey => itemkey,
3838 aname => 'AMS_CURRENCY',
3839 avalue => l_currency );
3840
3841
3842 END IF;
3843
3844
3845 wf_engine.SetItemAttrText( itemtype => itemtype,
3846 itemkey => itemkey,
3847 aname => 'AMS_NOTIF_DOCUMENT',
3848 avalue =>
3849 'PLSQL:AMS_WFCMPAPR_PVT.Create_Notif_Document/'||
3850 ItemType||':'||
3851 ItemKey);
3852
3853 l_doc_type := wf_engine.GetItemAttrText(
3854 itemtype => ItemType,
3855 itemkey => ItemKey,
3856 aname => 'AMS_APPR_DOC_TYPE');
3857
3858
3859 l_obj_type := wf_engine.GetItemAttrText(
3860 itemtype => ItemType,
3861 itemkey => ItemKey,
3862 aname => 'AMS_APPROVAL_FOR_OBJECT');
3863
3864 l_obj_id := wf_engine.GetItemAttrText(
3865 itemtype => ItemType,
3866 itemkey => ItemKey,
3867 aname => 'AMS_ACT_ID');
3868 l_obj_version_number := wf_engine.GetItemAttrText(
3869 itemtype => ItemType,
3870 itemkey => ItemKey,
3871 aname => 'AMS_OBJECT_VERSION_NUMBER');
3872 l_requester_id := wf_engine.GetItemAttrText(
3873 itemtype => ItemType,
3874 itemkey => ItemKey,
3875 aname => 'AMS_REQUESTER_ID');
3876
3877
3878 IF l_doc_type = 'BUDGET' THEN
3879 l_tmp_stat_code := 'SUBMIT_BA' ;
3880 ELSE
3881 l_tmp_stat_code := 'SUBMIT_TA' ;
3882 END IF ;
3883 --
3884 -- Get the Valid Code (Bubmit BA/TA) for Activity
3885 --
3886 Get_Valid_status(p_object_type => l_obj_type,
3887 p_stat_code => l_tmp_stat_code,
3888 x_sys_stat_code => l_sys_stat_code) ;
3889
3890 --
3891 -- Update Activity
3892 --
3893 Update_Status(p_obj_type => l_obj_type,
3894 p_obj_id => l_obj_id,
3895 p_object_version_number => l_obj_version_number,
3896 p_next_stat_code => l_sys_stat_code, --System Status
3897 p_appr_type => l_doc_type,
3898 p_submitted_by => l_requester_id,
3899 p_item_key => itemkey ,
3900 x_msg_count => l_msg_count,
3901 x_msg_data => l_msg_data,
3902 x_return_status => l_return_status) ;
3903
3904
3905 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3906 result := 'COMPLETE:SUCCESS' ;
3907 ELSE
3908 Handle_Err
3909 (p_itemtype => itemtype ,
3910 p_itemkey => itemkey ,
3911 p_msg_count => l_msg_count, -- Number of error Messages
3912 p_msg_data => l_msg_data ,
3913 p_attr_name => 'AMS_ERROR_MSG'
3914 ) ;
3915 result := 'COMPLETE:ERROR' ;
3916 END IF ;
3917 END IF ;
3918
3919 -- CANCEL mode - Normal Process Execution
3920 IF (funcmode = 'CANCEL')
3921 THEN
3922 result := 'COMPLETE:' ;
3923 RETURN;
3924 END IF;
3925
3926 -- TIMEOUT mode - Normal Process Execution
3927 IF (funcmode = 'TIMEOUT')
3928 THEN
3929 result := 'COMPLETE:' ;
3930 RETURN;
3931 END IF;
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934 wf_core.context(G_PKG_NAME,'Prepare_Doc',itemtype,itemkey,actid,funcmode);
3935 raise ;
3936 END Prepare_Doc ;
3937
3938 -- Start of Comments
3939 --
3940 -- NAME
3941 -- Owner_Appr_Check
3942 --
3943 -- PURPOSE
3944 -- This Procedure will check whether the Owner's Approval is required for the Theme
3945 --
3946 -- IN
3947 -- Itemtype - AMSAPPR
3948 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
3949 -- Accid - Activity ID
3950 -- Funmode - Run/Cancel/Timeout
3951 --
3952 -- OUT
3953 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
3954 -- - 'COMPLETE:N' If the Owner's Approval is not required
3955 --
3956 --
3957 -- OUT
3958 --
3959 -- Used By Activities
3960 -- Item Type - AMSAPPR
3961 -- Activity - AMS_CHECK_OWN_APPR
3962 -- NOTES
3963 --
3964 --
3965 -- HISTORY
3966 -- 09/13/1999 ptendulk created
3967 -- 11/30/1999 ptendulk Modified
3968 -- End of Comments
3969
3970 PROCEDURE Owner_Appr_Check (itemtype IN VARCHAR2,
3971 itemkey IN VARCHAR2,
3972 actid IN NUMBER,
3973 funcmode IN VARCHAR2,
3974 result OUT NOCOPY VARCHAR2) IS
3975 l_ta_appr_flag VARCHAR2(1);
3976 l_owner VARCHAR2(100);
3977 l_manager VARCHAR2(100);
3978 l_fund_manager VARCHAR2(100);
3979 l_bud_appr_flag VARCHAR2(1);
3980 l_appr_type VARCHAR2(30);
3981 BEGIN
3982 IF (AMS_DEBUG_HIGH_ON) THEN
3983
3984 AMS_Utility_PVT.debug_message('Process Owner_appr_check');
3985 END IF;
3986 -- dbms_output.put_line('Process Owner_appr_check');
3987 -- RUN mode - Normal Process Execution
3988 IF (funcmode = 'RUN')
3989 THEN
3990 l_ta_appr_flag := WF_ENGINE.GetItemAttrText(
3991 itemtype => itemtype,
3992 itemkey => itemkey ,
3993 aname => 'AMS_TAOWNER_APPR_FLAG');
3994 l_owner := WF_ENGINE.GetItemAttrText(
3995 itemtype => itemtype,
3996 itemkey => itemkey ,
3997 aname => 'AMS_OWNER');
3998 l_bud_appr_flag := WF_ENGINE.GetItemAttrText(
3999 itemtype => itemtype,
4000 itemkey => itemkey ,
4001 aname => 'AMS_BUDGET_APPR_FLAG');
4002
4003 l_fund_manager := WF_ENGINE.GetItemAttrText(
4004 itemtype => itemtype,
4005 itemkey => itemkey ,
4006 aname => 'AMS_BUD_MANAGER');
4007
4008 l_manager := WF_ENGINE.GetItemAttrText(
4009 itemtype => itemtype,
4010 itemkey => itemkey ,
4011 aname => 'AMS_MANAGER');
4012
4013 l_appr_type := WF_ENGINE.GetItemAttrText(
4014 itemtype => itemtype,
4015 itemkey => itemkey ,
4016 aname => 'AMS_APPR_TYPE_LOOKUP');
4017
4018 IF l_ta_appr_flag = 'Y' THEN
4019 IF l_appr_type = 'THEME' THEN
4020 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4021 itemkey => itemkey,
4022 aname => 'AMS_APPR_USERNAME',
4023 avalue => l_owner);
4024 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4025 itemkey => itemkey,
4026 aname => 'AMS_TAOWNER_APPR_FLAG',
4027 avalue => 'N');
4028 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4029 itemkey => itemkey,
4030 aname => 'AMS_APPR_DOC_TYPE',
4031 avalue => 'THEME');
4032
4033 ELSE -- l_appr_type = 'BOTH' THEN
4034 IF l_fund_manager = l_manager THEN
4035 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4036 itemkey => itemkey,
4037 aname => 'AMS_APPR_USERNAME',
4038 avalue => l_owner);
4039 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4040 itemkey => itemkey,
4041 aname => 'AMS_TAOWNER_APPR_FLAG',
4042 avalue => 'N');
4043 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4044 itemkey => itemkey,
4045 aname => 'AMS_APPR_DOC_TYPE',
4046 avalue => 'THEME');
4047 ELSIF l_owner = l_fund_manager THEN
4048 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4049 itemkey => itemkey,
4050 aname => 'AMS_APPR_USERNAME',
4051 avalue => l_owner);
4052
4053 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4054 itemkey => itemkey,
4055 aname => 'AMS_TAOWNER_APPR_FLAG',
4056 avalue => 'N');
4057 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4058 itemkey => itemkey,
4059 aname => 'AMS_APPR_DOC_TYPE',
4060 avalue => 'BOTH');
4061 ELSE
4062 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4063 itemkey => itemkey,
4064 aname => 'AMS_APPR_USERNAME',
4065 avalue => l_owner);
4066
4067 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4068 itemkey => itemkey,
4069 aname => 'AMS_BUDGET_APPR_FLAG',
4070 avalue => 'Y');
4071 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4072 itemkey => itemkey,
4073 aname => 'AMS_TAOWNER_APPR_FLAG',
4074 avalue => 'N');
4075 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4076 itemkey => itemkey,
4077 aname => 'AMS_APPR_DOC_TYPE',
4078 avalue => 'THEME');
4079 END IF ;
4080 END IF;
4081 result := 'COMPLETE:Y' ;
4082 ELSE -- IF l_ba_appr_flag = 'N' THEN
4083 IF l_bud_appr_flag = 'Y' THEN
4084 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4085 itemkey => itemkey,
4086 aname => 'AMS_APPR_USERNAME',
4087 avalue => l_fund_manager);
4088 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4089 itemkey => itemkey,
4090 aname => 'AMS_APPR_DOC_TYPE',
4091 avalue => 'BUDGET');
4092 END IF;
4093 result := 'COMPLETE:N' ;
4094 END IF;
4095 END IF;
4096
4097 -- CANCEL mode - Normal Process Execution
4098 IF (funcmode = 'CANCEL')
4099 THEN
4100 result := 'COMPLETE:' ;
4101 RETURN;
4102 END IF;
4103
4104 -- TIMEOUT mode - Normal Process Execution
4105 IF (funcmode = 'TIMEOUT')
4106 THEN
4107 result := 'COMPLETE:' ;
4108 RETURN;
4109 END IF;
4110 -- dbms_output.put_line('End Update log');
4111 EXCEPTION
4112 WHEN OTHERS THEN
4113 wf_core.context(G_PKG_NAME,'Owner_Appr_Check',itemtype,itemkey,actid,funcmode);
4114 raise ;
4115 END Owner_Appr_Check;
4116
4117 -- Start of Comments
4118 --
4119 -- NAME
4120 -- Update_Stat_ApprTA
4121 --
4122 -- PURPOSE
4123 -- This Procedure will Update the Status of the Activity for Approval
4124 --
4125 -- IN
4126 -- Itemtype - AMSAPPR
4127 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4128 -- Accid - Activity ID
4129 -- Funmode - Run/Cancel/Timeout
4130 --
4131 -- OUT
4132 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4133 -- COMPLETE:AMS_ERROR If the Process is errored out.
4134 --
4135 -- Used By Activities
4136 -- Item Type - AMSAPPR
4137 -- Activity - AMS_UPDATE_STATUS_TA
4138 --
4139 -- NOTES
4140 --
4141 --
4142 -- HISTORY
4143 -- 08/20/1999 ptendulk created
4144 -- 11/30/1999 ptendulk Modified
4145 -- End of Comments
4146
4147 PROCEDURE Update_Stat_ApprTA (itemtype IN VARCHAR2,
4148 itemkey IN VARCHAR2,
4149 actid IN NUMBER,
4150 funcmode IN VARCHAR2,
4151 result OUT NOCOPY VARCHAR2) IS
4152
4153 l_bud_appr_flg VARCHAR2(1);
4154 l_obj_type VARCHAR2(30);
4155 l_next_stat_id NUMBER ;
4156
4157 l_msg_count NUMBER ;
4158 l_msg_data VARCHAR2(2000);
4159 l_obj_id NUMBER ;
4160 l_obj_version_number NUMBER ;
4161 l_doc_type VARCHAR2(30);
4162 l_requester_id NUMBER ;
4163 l_appr_type_lookup VARCHAR2(30);
4164 l_sys_stat_code VARCHAR2(30);
4165 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4166
4167 l_approver VARCHAR2(30);
4168 l_note VARCHAR2(2000);
4169
4170 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4171 SELECT system_status_code
4172 FROM ams_user_statuses_vl
4173 WHERE user_status_id = l_user_stat_id ;
4174
4175 BEGIN
4176 IF (AMS_DEBUG_HIGH_ON) THEN
4177
4178 AMS_Utility_PVT.debug_message('Process Updt_Stat_apprTA');
4179 END IF;
4180 -- dbms_output.put_line('Process Updt_Stat_apprTA');
4181 -- RUN mode - Normal Process Execution
4182 IF (funcmode = 'RUN')
4183 THEN
4184 l_bud_appr_flg := wf_engine.GetItemAttrText(
4185 itemtype => ItemType,
4186 itemkey => ItemKey,
4187 aname => 'AMS_BUDGET_APPR_FLAG');
4188
4189 l_obj_type := wf_engine.GetItemAttrText(
4190 itemtype => ItemType,
4191 itemkey => ItemKey,
4192 aname => 'AMS_APPROVAL_FOR_OBJECT');
4193
4194 l_obj_id := wf_engine.GetItemAttrText(
4195 itemtype => ItemType,
4196 itemkey => ItemKey,
4197 aname => 'AMS_ACT_ID');
4198 l_obj_version_number := wf_engine.GetItemAttrText(
4199 itemtype => ItemType,
4200 itemkey => ItemKey,
4201 aname => 'AMS_OBJECT_VERSION_NUMBER');
4202 l_next_stat_id := wf_engine.GetItemAttrText(
4203 itemtype => ItemType,
4204 itemkey => ItemKey,
4205 aname => 'AMS_NEW_STAT_ID');
4206 l_requester_id := wf_engine.GetItemAttrText(
4207 itemtype => ItemType,
4208 itemkey => ItemKey,
4209 aname => 'AMS_REQUESTER_ID');
4210 l_doc_type := wf_engine.GetItemAttrText(
4211 itemtype => ItemType,
4212 itemkey => ItemKey,
4213 aname => 'AMS_APPR_DOC_TYPE');
4214
4215 l_appr_type_lookup := wf_engine.GetItemAttrText(
4216 itemtype => ItemType,
4217 itemkey => ItemKey,
4218 aname => 'AMS_APPR_TYPE_LOOKUP');
4219
4220 l_approver := wf_engine.GetItemAttrText(
4221 itemtype => ItemType,
4222 itemkey => ItemKey,
4223 aname => 'AMS_APPR_USERNAME');
4224
4225 l_note := wf_engine.GetItemAttrText(
4226 itemtype => ItemType,
4227 itemkey => ItemKey,
4228 aname => 'AMS_NOTE');
4229
4230 OPEN c_sys_stat(l_next_stat_id) ;
4231 FETCH c_sys_stat INTO l_sys_stat_code ;
4232 CLOSE c_sys_stat ;
4233
4234 -- Update the Notes which Approver has Given with Approvals
4235 IF l_note IS NOT NULL THEN
4236 Update_Note(p_obj_type => l_obj_type,
4237 p_obj_id => l_obj_id,
4238 p_note => l_note,
4239 p_user => l_approver,
4240 x_msg_count => l_msg_count,
4241 x_msg_data => l_msg_data,
4242 x_return_status => l_return_status) ;
4243
4244 END IF ;
4245 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4246 Handle_Err
4247 (p_itemtype => itemtype ,
4248 p_itemkey => itemkey ,
4249 p_msg_count => l_msg_count, -- Number of error Messages
4250 p_msg_data => l_msg_data ,
4251 p_attr_name => 'AMS_ERROR_MSG'
4252 ) ;
4253 result := 'COMPLETE:ERROR' ;
4254 ELSE
4255 IF l_bud_appr_flg = 'Y' THEN
4256 -- No Need to Update the Status
4257
4258 -- Update the Attribute
4259 Update_Attribute(p_obj_type => l_obj_type,
4260 p_obj_id => l_obj_id,
4261 p_obj_attr => 'TAPL',
4262 x_msg_count => l_msg_count,
4263 x_msg_data => l_msg_data,
4264 x_return_status => l_return_status ) ;
4265
4266 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4267 result := 'COMPLETE:SUCCESS' ;
4268 ELSE
4269 Handle_Err
4270 (p_itemtype => itemtype ,
4271 p_itemkey => itemkey ,
4272 p_msg_count => l_msg_count, -- Number of error Messages
4273 p_msg_data => l_msg_data ,
4274 p_attr_name => 'AMS_ERROR_MSG'
4275 ) ;
4276 result := 'COMPLETE:ERROR' ;
4277 END IF;
4278
4279 ELSE
4280 --
4281 -- Update Activity
4282 --
4283 Update_Status(p_obj_type => l_obj_type,
4284 p_obj_id => l_obj_id,
4285 p_object_version_number => l_obj_version_number,
4286 p_next_stat_code => l_sys_stat_code, --System Status
4287 p_next_stat_id => l_next_stat_id,
4288 p_appr_type => l_doc_type,
4289 p_submitted_by => l_requester_id,
4290 p_item_key => itemkey ,
4291 x_msg_count => l_msg_count,
4292 x_msg_data => l_msg_data,
4293 x_return_status => l_return_status) ;
4294
4295 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4296 -- Update the Attribute to show that Theme Approval is Completed
4297 IF l_appr_type_lookup = 'THEME' THEN
4298 Update_Attribute(p_obj_type => l_obj_type,
4299 p_obj_id => l_obj_id,
4300 p_obj_attr => 'TAPL',
4301 x_msg_count => l_msg_count,
4302 x_msg_data => l_msg_data,
4303 x_return_status => l_return_status) ;
4304 ELSIF l_appr_type_lookup = 'BOTH' THEN
4305 Update_Attribute(p_obj_type => l_obj_type,
4306 p_obj_id => l_obj_id,
4307 p_obj_attr => 'TAPL',
4308 x_msg_count => l_msg_count,
4309 x_msg_data => l_msg_data,
4310 x_return_status => l_return_status) ;
4311 Update_Attribute(p_obj_type => l_obj_type,
4312 p_obj_id => l_obj_id,
4313 p_obj_attr => 'BAPL',
4314 x_msg_count => l_msg_count,
4315 x_msg_data => l_msg_data,
4316 x_return_status => l_return_status) ;
4317
4318 END IF;
4319
4320 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4321 result := 'COMPLETE:SUCCESS' ;
4322 ELSE
4323 Handle_Err
4324 (p_itemtype => itemtype ,
4325 p_itemkey => itemkey ,
4326 p_msg_count => l_msg_count, -- Number of error Messages
4327 p_msg_data => l_msg_data ,
4328 p_attr_name => 'AMS_ERROR_MSG'
4329 ) ;
4330 result := 'COMPLETE:ERROR' ;
4331 END IF;
4332 ELSE
4333 Handle_Err
4334 (p_itemtype => itemtype ,
4335 p_itemkey => itemkey ,
4336 p_msg_count => l_msg_count, -- Number of error Messages
4337 p_msg_data => l_msg_data ,
4338 p_attr_name => 'AMS_ERROR_MSG'
4339 ) ;
4340 result := 'COMPLETE:ERROR' ;
4341 END IF ;
4342 END IF ;
4343 END IF;
4344
4345
4346 END IF ;
4347
4348 -- CANCEL mode - Normal Process Execution
4349 IF (funcmode = 'CANCEL')
4350 THEN
4351 result := 'COMPLETE:' ;
4352 RETURN;
4353 END IF;
4354
4355 -- TIMEOUT mode - Normal Process Execution
4356 IF (funcmode = 'TIMEOUT')
4357 THEN
4358 result := 'COMPLETE:' ;
4359 RETURN;
4360 END IF;
4361 EXCEPTION
4362 WHEN OTHERS THEN
4363 wf_core.context(G_PKG_NAME,'Update_Stat_ApprTA',itemtype,itemkey,actid,funcmode);
4364 raise ;
4365 END Update_Stat_ApprTA ;
4366
4367
4368 -- Start of Comments
4369 --
4370 -- NAME
4371 -- Update_Status_Rej
4372 --
4373 -- PURPOSE
4374 -- This Procedure will Update the Status of the Activity for Rejection
4375 --
4376 -- IN
4377 -- Itemtype - AMSAPPR
4378 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4379 -- Accid - Activity ID
4380 -- Funmode - Run/Cancel/Timeout
4381 --
4382 -- OUT
4383 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4384 -- COMPLETE:AMS_ERROR If the Process is errored out.
4385 --
4386 -- Used By Activities
4387 -- Item Type - AMSAPPR
4388 -- Activity - AMS_UPDATE_STATUS_REJ
4389 --
4390 -- NOTES
4391 --
4392 --
4393 -- HISTORY
4394 -- 08/20/1999 ptendulk created
4395 -- 11/30/1999 ptendulk Modified
4396 -- End of Comments
4397
4398 PROCEDURE Update_Status_Rej (itemtype IN VARCHAR2,
4399 itemkey IN VARCHAR2,
4400 actid IN NUMBER,
4401 funcmode IN VARCHAR2,
4402 result OUT NOCOPY VARCHAR2) IS
4403 l_doc_type VARCHAR2(30);
4404 l_obj_type VARCHAR2(30);
4405 l_tmp_stat_code VARCHAR2(30);
4406 l_sys_stat_code VARCHAR2(30);
4407
4408 l_msg_count NUMBER ;
4409 l_msg_data VARCHAR2(2000);
4410 l_obj_id NUMBER ;
4411 l_obj_version_number NUMBER ;
4412 l_requester_id NUMBER ;
4413 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4414 l_approver VARCHAR2(30);
4415 l_note VARCHAR2(2000);
4416 BEGIN
4417 IF (AMS_DEBUG_HIGH_ON) THEN
4418
4419 AMS_Utility_PVT.debug_message('Process Update Status_rej');
4420 END IF;
4421 -- dbms_output.put_line('Process Update Status_rej');
4422 -- RUN mode - Normal Process Execution
4423 IF (funcmode = 'RUN')
4424 THEN
4425 l_doc_type := wf_engine.GetItemAttrText(
4426 itemtype => ItemType,
4427 itemkey => ItemKey,
4428 aname => 'AMS_APPR_DOC_TYPE');
4429
4430 l_obj_type := wf_engine.GetItemAttrText(
4431 itemtype => ItemType,
4432 itemkey => ItemKey,
4433 aname => 'AMS_APPROVAL_FOR_OBJECT');
4434
4435 l_obj_id := wf_engine.GetItemAttrText(
4436 itemtype => ItemType,
4437 itemkey => ItemKey,
4438 aname => 'AMS_ACT_ID');
4439 l_obj_version_number := wf_engine.GetItemAttrText(
4440 itemtype => ItemType,
4441 itemkey => ItemKey,
4442 aname => 'AMS_OBJECT_VERSION_NUMBER');
4443 l_requester_id := wf_engine.GetItemAttrText(
4444 itemtype => ItemType,
4445 itemkey => ItemKey,
4446 aname => 'AMS_REQUESTER_ID');
4447
4448 l_approver := wf_engine.GetItemAttrText(
4449 itemtype => ItemType,
4450 itemkey => ItemKey,
4451 aname => 'AMS_APPR_USERNAME');
4452
4453 l_note := wf_engine.GetItemAttrText(
4454 itemtype => ItemType,
4455 itemkey => ItemKey,
4456 aname => 'AMS_NOTE');
4457
4458 IF l_doc_type = 'BUDGET' THEN
4459 l_tmp_stat_code := 'REJECT_BA' ;
4460 ELSE
4461 l_tmp_stat_code := 'REJECT_TA' ;
4462 END IF ;
4463 --
4464 -- Get the Valid Code (Bubmit BA/TA) for Activity
4465 --
4466 Get_Valid_status(p_object_type => l_obj_type,
4467 p_stat_code => l_tmp_stat_code,
4468 x_sys_stat_code => l_sys_stat_code) ;
4469
4470 -- Update the Notes which Approver has Given with Rejection
4471 IF l_note IS NOT NULL THEN
4472 Update_Note(p_obj_type => l_obj_type,
4473 p_obj_id => l_obj_id,
4474 p_note => l_note,
4475 p_user => l_approver,
4476 x_msg_count => l_msg_count,
4477 x_msg_data => l_msg_data,
4478 x_return_status => l_return_status) ;
4479
4480 END IF ;
4481 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4482 Handle_Err
4483 (p_itemtype => itemtype ,
4484 p_itemkey => itemkey ,
4485 p_msg_count => l_msg_count, -- Number of error Messages
4486 p_msg_data => l_msg_data ,
4487 p_attr_name => 'AMS_ERROR_MSG'
4488 ) ;
4489 result := 'COMPLETE:ERROR' ;
4490 ELSE
4491
4492 --
4493 -- Update Activity
4494 --
4495 Update_Status(p_obj_type => l_obj_type,
4496 p_obj_id => l_obj_id,
4497 p_object_version_number => l_obj_version_number,
4498 p_next_stat_code => l_sys_stat_code, --System Status
4499 p_appr_type => l_doc_type,
4500 p_submitted_by => l_requester_id,
4501 p_item_key => itemkey ,
4502 x_msg_count => l_msg_count,
4503 x_msg_data => l_msg_data,
4504 x_return_status => l_return_status) ;
4505
4506 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4507 result := 'COMPLETE:SUCCESS' ;
4508 ELSE
4509 Handle_Err
4510 (p_itemtype => itemtype ,
4511 p_itemkey => itemkey ,
4512 p_msg_count => l_msg_count, -- Number of error Messages
4513 p_msg_data => l_msg_data ,
4514 p_attr_name => 'AMS_ERROR_MSG'
4515 ) ;
4516 result := 'COMPLETE:ERROR' ;
4517 END IF ;
4518 END IF ;
4519 END IF ;
4520
4521 -- CANCEL mode - Normal Process Execution
4522 IF (funcmode = 'CANCEL')
4523 THEN
4524 result := 'COMPLETE:' ;
4525 RETURN;
4526 END IF;
4527
4528 -- TIMEOUT mode - Normal Process Execution
4529 IF (funcmode = 'TIMEOUT')
4530 THEN
4531 result := 'COMPLETE:' ;
4532 RETURN;
4533 END IF;
4534 EXCEPTION
4535 WHEN OTHERS THEN
4536 wf_core.context(G_PKG_NAME,'Update_Status_Rej',itemtype,itemkey,actid,funcmode);
4537 raise ;
4538 END Update_Status_Rej ;
4539
4540 -- Start of Comments
4541 --
4542 -- NAME
4543 -- Revert_Status
4544 --
4545 -- PURPOSE
4546 -- This Procedure will Revert the Status of the Activity Back to Original
4547 --
4548 -- IN
4549 -- Itemtype - AMSAPPR
4550 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4551 -- Accid - Activity ID
4552 -- Funmode - Run/Cancel/Timeout
4553 --
4554 -- OUT
4555 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4556 -- COMPLETE:AMS_ERROR If the Process is errored out.
4557 --
4558 -- Used By Activities
4559 -- Item Type - AMSAPPR
4560 -- Activity - AMS_REVERT_STATUS
4561 --
4562 -- NOTES
4563 --
4564 --
4565 -- HISTORY
4566 -- 08/20/1999 ptendulk created
4567 -- 11/30/1999 ptendulk Modified
4568 -- End of Comments
4569
4570 PROCEDURE Revert_Status (itemtype IN VARCHAR2,
4571 itemkey IN VARCHAR2,
4572 actid IN NUMBER,
4573 funcmode IN VARCHAR2,
4574 result OUT NOCOPY VARCHAR2) IS
4575 l_obj_type VARCHAR2(30);
4576 l_tmp_stat_code VARCHAR2(30);
4577 l_doc_type VARCHAR2(30);
4578 l_requester_id NUMBER ;
4579 l_orig_stat_id NUMBER ;
4580
4581 l_msg_count NUMBER ;
4582 l_msg_data VARCHAR2(2000);
4583 l_obj_id NUMBER ;
4584 l_obj_version_number NUMBER ;
4585 l_sys_stat_code VARCHAR2(30);
4586 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4587 l_approver VARCHAR2(30);
4588 l_note VARCHAR2(2000);
4589
4590 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4591 SELECT system_status_code
4592 FROM ams_user_statuses_vl
4593 WHERE user_status_id = l_user_stat_id ;
4594
4595 BEGIN
4596 IF (AMS_DEBUG_HIGH_ON) THEN
4597
4598 AMS_Utility_PVT.debug_message('Process Revert Status');
4599 END IF;
4600 -- dbms_output.put_line('Process Revert Status');
4601 -- RUN mode - Normal Process Execution
4602 IF (funcmode = 'RUN')
4603 THEN
4604
4605 l_obj_type := wf_engine.GetItemAttrText(
4606 itemtype => ItemType,
4607 itemkey => ItemKey,
4608 aname => 'AMS_APPROVAL_FOR_OBJECT');
4609
4610 l_obj_id := wf_engine.GetItemAttrText(
4611 itemtype => ItemType,
4612 itemkey => ItemKey,
4613 aname => 'AMS_ACT_ID');
4614 l_obj_version_number := wf_engine.GetItemAttrText(
4615 itemtype => ItemType,
4616 itemkey => ItemKey,
4617 aname => 'AMS_OBJECT_VERSION_NUMBER');
4618
4619 l_orig_stat_id := wf_engine.GetItemAttrText(
4620 itemtype => ItemType,
4621 itemkey => ItemKey,
4622 aname => 'AMS_ORIG_STAT_ID');
4623 l_requester_id := wf_engine.GetItemAttrText(
4624 itemtype => ItemType,
4625 itemkey => ItemKey,
4626 aname => 'AMS_REQUESTER_ID');
4627 l_doc_type := wf_engine.GetItemAttrText(
4628 itemtype => ItemType,
4629 itemkey => ItemKey,
4630 aname => 'AMS_APPR_DOC_TYPE');
4631
4632 l_approver := wf_engine.GetItemAttrText(
4633 itemtype => ItemType,
4634 itemkey => ItemKey,
4635 aname => 'AMS_APPR_USERNAME');
4636
4637 l_note := wf_engine.GetItemAttrText(
4638 itemtype => ItemType,
4639 itemkey => ItemKey,
4640 aname => 'AMS_NOTE');
4641
4642
4643
4644 OPEN c_sys_stat(l_orig_stat_id) ;
4645 FETCH c_sys_stat INTO l_sys_stat_code ;
4646 CLOSE c_sys_stat ;
4647
4648 -- Update the Notes which Approver has Given with Rejection
4649 IF l_note IS NOT NULL THEN
4650 Update_Note(p_obj_type => l_obj_type,
4651 p_obj_id => l_obj_id,
4652 p_note => l_note,
4653 p_user => l_approver,
4654 x_msg_count => l_msg_count,
4655 x_msg_data => l_msg_data,
4656 x_return_status => l_return_status) ;
4657
4658 END IF ;
4659 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4660 Handle_Err
4661 (p_itemtype => itemtype ,
4662 p_itemkey => itemkey ,
4663 p_msg_count => l_msg_count, -- Number of error Messages
4664 p_msg_data => l_msg_data ,
4665 p_attr_name => 'AMS_ERROR_MSG'
4666 ) ;
4667 result := 'COMPLETE:ERROR' ;
4668 ELSE
4669 --
4670 -- Update Activity
4671 --
4672 Update_Status(p_obj_type => l_obj_type,
4673 p_obj_id => l_obj_id,
4674 p_object_version_number => l_obj_version_number,
4675 p_next_stat_code => l_sys_stat_code, -- System Status
4676 p_next_stat_id => l_orig_stat_id,
4677 p_appr_type => l_doc_type,
4678 p_submitted_by => l_requester_id,
4679 p_item_key => itemkey,
4680 x_msg_count => l_msg_count,
4681 x_msg_data => l_msg_data,
4682 x_return_status => l_return_status) ;
4683
4684 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4685 result := 'COMPLETE:SUCCESS' ;
4686 ELSE
4687 Handle_Err
4688 (p_itemtype => itemtype ,
4689 p_itemkey => itemkey ,
4690 p_msg_count => l_msg_count, -- Number of error Messages
4691 p_msg_data => l_msg_data ,
4692 p_attr_name => 'AMS_ERROR_MSG'
4693 ) ;
4694 result := 'COMPLETE:ERROR' ;
4695 END IF ;
4696 END IF;
4697 END IF ;
4698
4699 -- CANCEL mode - Normal Process Execution
4700 IF (funcmode = 'CANCEL')
4701 THEN
4702 result := 'COMPLETE:' ;
4703 RETURN;
4704 END IF;
4705
4706 -- TIMEOUT mode - Normal Process Execution
4707 IF (funcmode = 'TIMEOUT')
4708 THEN
4709 result := 'COMPLETE:' ;
4710 RETURN;
4711 END IF;
4712 EXCEPTION
4713 WHEN OTHERS THEN
4714 wf_core.context(G_PKG_NAME,'Revert_Status',itemtype,itemkey,actid,funcmode);
4715 raise ;
4716 END Revert_Status ;
4717
4718 -- Start of Comments
4719 --
4720 -- NAME
4721 -- Fund_Appr_Req_Check
4722 --
4723 -- PURPOSE
4724 -- This Procedure will check whether the Budget Approval is required or not
4725 --
4726 -- Itemtype - AMSAPPR
4727 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4728 -- Accid - Activity ID
4729 -- Funmode - Run/Cancel/Timeout
4730 --
4731 -- OUT
4732 -- Result - 'COMPLETE:Y' If the approval is required
4733 -- - 'COMPLETE:N' If the approval is not required
4734 --
4735 -- Used By Activities
4736 -- Item Type - AMSAPPR
4737 -- Activity - AMS_CHECK_BUD_APPR
4738 --
4739 -- NOTES
4740 --
4741 --
4742 -- HISTORY
4743 -- 08/20/1999 ptendulk created
4744 -- 11/30/1999 ptendulk Modified
4745 -- End of Comments
4746
4747 PROCEDURE Fund_Appr_Req_Check (itemtype IN VARCHAR2,
4748 itemkey IN VARCHAR2,
4749 actid IN NUMBER,
4750 funcmode IN VARCHAR2,
4751 result OUT NOCOPY VARCHAR2) IS
4752 l_budget_appr_flag VARCHAR2(1);
4753 BEGIN
4754 IF (AMS_DEBUG_HIGH_ON) THEN
4755
4756 AMS_Utility_PVT.debug_message('Process Fund Appr Req_Check');
4757 END IF;
4758 -- dbms_output.put_line('Process Fund Appr Req_Check');
4759 -- RUN mode - Normal Process Execution
4760 IF (funcmode = 'RUN')
4761 THEN
4762 l_budget_appr_flag := WF_ENGINE.GetItemAttrText(
4763 itemtype => itemtype,
4764 itemkey => itemkey ,
4765 aname => 'AMS_BUDGET_APPR_FLAG');
4766 IF l_budget_appr_flag = 'Y' THEN
4767 result := 'COMPLETE:Y' ;
4768 ELSE
4769 result := 'COMPLETE:N' ;
4770 END IF;
4771
4772 END IF;
4773
4774 -- CANCEL mode - Normal Process Execution
4775 IF (funcmode = 'CANCEL')
4776 THEN
4777 result := 'COMPLETE:' ;
4778 RETURN;
4779 END IF;
4780
4781 -- TIMEOUT mode - Normal Process Execution
4782 IF (funcmode = 'TIMEOUT')
4783 THEN
4784 result := 'COMPLETE:' ;
4785 RETURN;
4786 END IF;
4787 -- dbms_output.put_line('End Update log');
4788 EXCEPTION
4789 WHEN OTHERS THEN
4790 wf_core.context(G_PKG_NAME,'Fund_Appr_Req_Check',itemtype,itemkey,actid,funcmode);
4791 raise ;
4792 END Fund_Appr_Req_Check ;
4793
4794
4795
4796 -- Start of Comments
4797 --
4798 -- NAME
4799 -- Ba_Owner_Appr_Check
4800 --
4801 -- PURPOSE
4802 -- This Procedure will check whether the Owner's Approval is required for the Budget
4803 --
4804 -- IN
4805 -- Itemtype - AMSAPPR
4806 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4807 -- Accid - Activity ID
4808 -- Funmode - Run/Cancel/Timeout
4809 --
4810 -- OUT
4811 -- Result - 'COMPLETE:Y' If the Owner's Approval is required
4812 -- - 'COMPLETE:N' If the Owner's Approval is not required
4813 --
4814 --
4815 -- OUT
4816 --
4817 -- Used By Activities
4818 -- Item Type - AMSAPPR
4819 -- Activity - AMS_CHECK_BA_OWN_APPR
4820 -- NOTES
4821 --
4822 --
4823 -- HISTORY
4824 -- 09/13/1999 ptendulk created
4825 -- 11/30/1999 ptendulk Modified
4826 -- End of Comments
4827
4828 PROCEDURE Ba_Owner_Appr_Check (itemtype IN VARCHAR2,
4829 itemkey IN VARCHAR2,
4830 actid IN NUMBER,
4831 funcmode IN VARCHAR2,
4832 result OUT NOCOPY VARCHAR2) IS
4833 l_ba_appr_flag VARCHAR2(1);
4834 l_owner VARCHAR2(100);
4835 BEGIN
4836 IF (AMS_DEBUG_HIGH_ON) THEN
4837
4838 AMS_Utility_PVT.debug_message('Process BA_OWNER_APPR_CHECK');
4839 END IF;
4840 -- dbms_output.put_line('Process BA_OWNER_APPR_CHECK');
4841 -- RUN mode - Normal Process Execution
4842 IF (funcmode = 'RUN')
4843 THEN
4844 l_ba_appr_flag := WF_ENGINE.GetItemAttrText(
4845 itemtype => itemtype,
4846 itemkey => itemkey ,
4847 aname => 'AMS_TAOWNER_APPR_FLAG');
4848 l_owner := WF_ENGINE.GetItemAttrText(
4849 itemtype => itemtype,
4850 itemkey => itemkey ,
4851 aname => 'AMS_OWNER');
4852
4853 IF l_ba_appr_flag = 'Y' THEN
4854
4855 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4856 itemkey => itemkey,
4857 aname => 'AMS_APPR_USERNAME',
4858 avalue => l_owner);
4859
4860 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4861 itemkey => itemkey,
4862 aname => 'AMS_BUDGET_APPR_FLAG',
4863 avalue => 'N');
4864 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4865 itemkey => itemkey,
4866 aname => 'AMS_TAOWNER_APPR_FLAG',
4867 avalue => 'N');
4868 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4869 itemkey => itemkey,
4870 aname => 'AMS_BAOWNER_APPR_FLAG',
4871 avalue => 'N');
4872 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4873 itemkey => itemkey,
4874 aname => 'AMS_THEME_APPR_FLAG',
4875 avalue => 'Y');
4876 WF_ENGINE.SetItemAttrText(itemtype => itemtype ,
4877 itemkey => itemkey,
4878 aname => 'AMS_APPR_DOC_TYPE',
4879 avalue => 'BUDGET');
4880
4881 result := 'COMPLETE:Y' ;
4882 ELSE -- IF l_ba_appr_flag = 'N' THEN
4883 result := 'COMPLETE:N' ;
4884 END IF;
4885 END IF;
4886
4887 -- CANCEL mode - Normal Process Execution
4888 IF (funcmode = 'CANCEL')
4889 THEN
4890 result := 'COMPLETE:' ;
4891 RETURN;
4892 END IF;
4893
4894 -- TIMEOUT mode - Normal Process Execution
4895 IF (funcmode = 'TIMEOUT')
4896 THEN
4897 result := 'COMPLETE:' ;
4898 RETURN;
4899 END IF;
4900 -- dbms_output.put_line('End Update log');
4901 EXCEPTION
4902 WHEN OTHERS THEN
4903 wf_core.context(G_PKG_NAME,'Ba_Owner_Appr_Check',itemtype,itemkey,actid,funcmode);
4904 raise ;
4905 END Ba_Owner_Appr_Check;
4906
4907
4908 -- Start of Comments
4909 --
4910 -- NAME
4911 -- Update_Stat_ApprBA
4912 --
4913 -- PURPOSE
4914 -- This Procedure will Update the Status of the Activity for Approval
4915 --
4916 -- IN
4917 -- Itemtype - AMSAPPR
4918 -- Itemkey - p_approver_for||p_approval_for_id||to_char(sysdate,'ddmmyyhhmiss')
4919 -- Accid - Activity ID
4920 -- Funmode - Run/Cancel/Timeout
4921 --
4922 -- OUT
4923 -- Result - COMPLETE:AMS_SUCCESS If the Process is Success.
4924 -- COMPLETE:AMS_ERROR If the Process is errored out.
4925 --
4926 -- Used By Activities
4927 -- Item Type - AMSAPPR
4928 -- Activity - AMS_UPDATE_STATUS_BA
4929 --
4930 -- NOTES
4931 --
4932 --
4933 -- HISTORY
4934 -- 08/20/1999 ptendulk created
4935 -- 11/30/1999 ptendulk Modified
4936 -- End of Comments
4937
4938 PROCEDURE Update_Stat_ApprBA (itemtype IN VARCHAR2,
4939 itemkey IN VARCHAR2,
4940 actid IN NUMBER,
4941 funcmode IN VARCHAR2,
4942 result OUT NOCOPY VARCHAR2) IS
4943
4944 l_obj_type VARCHAR2(30);
4945 l_next_stat_id NUMBER ;
4946
4947 l_msg_count NUMBER ;
4948 l_msg_data VARCHAR2(2000);
4949 l_obj_id NUMBER ;
4950 l_obj_version_number NUMBER ;
4951 l_requester_id NUMBER ;
4952 l_doc_type VARCHAR2(30);
4953 l_sys_stat_code VARCHAR2(30);
4954 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS ;
4955 l_approver VARCHAR2(30);
4956 l_note VARCHAR2(2000);
4957
4958 CURSOR c_sys_stat(l_user_stat_id NUMBER) IS
4959 SELECT system_status_code
4960 FROM ams_user_statuses_vl
4961 WHERE user_status_id = l_user_stat_id ;
4962
4963 BEGIN
4964 IF (AMS_DEBUG_HIGH_ON) THEN
4965
4966 AMS_Utility_PVT.debug_message('Process Update Status APPRBA');
4967 END IF;
4968 -- dbms_output.put_line('Process Update Status APPRBA');
4969 -- RUN mode - Normal Process Execution
4970 IF (funcmode = 'RUN')
4971 THEN
4972
4973 l_obj_type := wf_engine.GetItemAttrText(
4974 itemtype => ItemType,
4975 itemkey => ItemKey,
4976 aname => 'AMS_APPROVAL_FOR_OBJECT');
4977
4978 l_obj_id := wf_engine.GetItemAttrText(
4979 itemtype => ItemType,
4980 itemkey => ItemKey,
4981 aname => 'AMS_ACT_ID');
4982 l_obj_version_number := wf_engine.GetItemAttrText(
4983 itemtype => ItemType,
4984 itemkey => ItemKey,
4985 aname => 'AMS_OBJECT_VERSION_NUMBER');
4986 l_next_stat_id := wf_engine.GetItemAttrText(
4987 itemtype => ItemType,
4988 itemkey => ItemKey,
4989 aname => 'AMS_NEW_STAT_ID');
4990 l_requester_id := wf_engine.GetItemAttrText(
4991 itemtype => ItemType,
4992 itemkey => ItemKey,
4993 aname => 'AMS_REQUESTER_ID');
4994 l_doc_type := wf_engine.GetItemAttrText(
4995 itemtype => ItemType,
4996 itemkey => ItemKey,
4997 aname => 'AMS_APPR_DOC_TYPE');
4998
4999 OPEN c_sys_stat(l_next_stat_id) ;
5000 FETCH c_sys_stat INTO l_sys_stat_code ;
5001 CLOSE c_sys_stat ;
5002
5003
5004 l_approver := wf_engine.GetItemAttrText(
5005 itemtype => ItemType,
5006 itemkey => ItemKey,
5007 aname => 'AMS_APPR_USERNAME');
5008
5009 l_note := wf_engine.GetItemAttrText(
5010 itemtype => ItemType,
5011 itemkey => ItemKey,
5012 aname => 'AMS_NOTE');
5013
5014
5015 -- Update the Notes which Approver has Given with Rejection
5016 IF l_note IS NOT NULL THEN
5017 Update_Note(p_obj_type => l_obj_type,
5018 p_obj_id => l_obj_id,
5019 p_note => l_note,
5020 p_user => l_approver,
5021 x_msg_count => l_msg_count,
5022 x_msg_data => l_msg_data,
5023 x_return_status => l_return_status) ;
5024
5025 END IF ;
5026 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5027 Handle_Err
5028 (p_itemtype => itemtype ,
5029 p_itemkey => itemkey ,
5030 p_msg_count => l_msg_count, -- Number of error Messages
5031 p_msg_data => l_msg_data ,
5032 p_attr_name => 'AMS_ERROR_MSG'
5033 ) ;
5034 result := 'COMPLETE:ERROR' ;
5035 ELSE
5036 --
5037 -- Update Activity
5038 --
5039 Update_Status(p_obj_type => l_obj_type,
5040 p_obj_id => l_obj_id,
5041 p_object_version_number => l_obj_version_number,
5042 p_next_stat_code => l_sys_stat_code, --System Status
5043 p_next_stat_id => l_next_stat_id,
5044 p_appr_type => l_doc_type,
5045 p_submitted_by => l_requester_id,
5046 p_item_key => itemkey ,
5047 x_msg_count => l_msg_count,
5048 x_msg_data => l_msg_data,
5049 x_return_status => l_return_status) ;
5050
5051 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5052 -- Update the Attribute to show that Theme Approval is Completed
5053 Update_Attribute(p_obj_type => l_obj_type,
5054 p_obj_id => l_obj_id,
5055 p_obj_attr => 'BAPL',
5056 x_msg_count => l_msg_count,
5057 x_msg_data => l_msg_data,
5058 x_return_status => l_return_status) ;
5059
5060 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5061 result := 'COMPLETE:SUCCESS' ;
5062 ELSE
5063 Handle_Err
5064 (p_itemtype => itemtype ,
5065 p_itemkey => itemkey ,
5066 p_msg_count => l_msg_count, -- Number of error Messages
5067 p_msg_data => l_msg_data ,
5068 p_attr_name => 'AMS_ERROR_MSG'
5069 ) ;
5070 result := 'COMPLETE:ERROR' ;
5071 END IF;
5072 result := 'COMPLETE:SUCCESS' ;
5073 ELSE
5074 Handle_Err
5075 (p_itemtype => itemtype ,
5076 p_itemkey => itemkey ,
5077 p_msg_count => l_msg_count, -- Number of error Messages
5078 p_msg_data => l_msg_data ,
5079 p_attr_name => 'AMS_ERROR_MSG'
5080 ) ;
5081 result := 'COMPLETE:ERROR' ;
5082 END IF ;
5083 END IF ;
5084 END IF ;
5085
5086 -- CANCEL mode - Normal Process Execution
5087 IF (funcmode = 'CANCEL')
5088 THEN
5089 result := 'COMPLETE:' ;
5090 RETURN;
5091 END IF;
5092
5093 -- TIMEOUT mode - Normal Process Execution
5094 IF (funcmode = 'TIMEOUT')
5095 THEN
5096 result := 'COMPLETE:' ;
5097 RETURN;
5098 END IF;
5099 EXCEPTION
5100 WHEN OTHERS THEN
5101 wf_core.context(G_PKG_NAME,'Update_Stat_ApprBA',itemtype,itemkey,actid,funcmode);
5102 raise ;
5103 END Update_Stat_ApprBA ;
5104
5105
5106 -- Start of Comments
5107 --
5108 -- NAME
5109 -- AbortProcess
5110 --
5111 -- PURPOSE
5112 -- This Procedure will abort the process of Approvals
5113 --
5114 -- Used By Activities
5115 --
5116 -- NOTES
5117 --
5118 --
5119 -- HISTORY
5120 -- 09/13/1999 ptendulk created
5121 -- 11/30/1999 ptendulk Modified
5122 -- End of Comments
5123
5124 PROCEDURE AbortProcess
5125 (p_itemkey IN VARCHAR2
5126 ,p_workflowprocess IN VARCHAR2 DEFAULT NULL
5127 ,p_itemtype IN VARCHAR2 DEFAULT NULL
5128 )
5129 IS
5130 itemkey VARCHAR2(30) := p_itemkey ;
5131 itemtype VARCHAR2(30) := nvl(p_itemtype,'AMSAPPR') ;
5132 BEGIN
5133 IF (AMS_DEBUG_HIGH_ON) THEN
5134
5135 AMS_Utility_PVT.debug_message('Process Abort Process');
5136 END IF;
5137 -- dbms_output.put_line('Process Abort Process');
5138
5139 WF_ENGINE.AbortProcess (itemtype => itemtype,
5140 itemkey => itemkey ,
5141 process => p_workflowprocess);
5142
5143 -- dbms_output.put_line('After Aborting Process ');
5144 EXCEPTION
5145 WHEN OTHERS
5146 THEN
5147 wf_core.context ('AMS_WfCmpApr_PVT', 'AbortProcess',itemtype,itemkey
5148 ,p_workflowprocess);
5149 RAISE;
5150
5151 END AbortProcess;
5152
5153
5154
5155 END AMS_WfCmpApr_PVT ;
5156