[Home] [Help]
PACKAGE BODY: APPS.AMV_WFAPPROVAL_PVT
Source
1 PACKAGE BODY amv_wfapproval_pvt as
2 /* $Header: amvvwfab.pls 120.1 2005/06/21 16:54:58 appldev ship $ */
3
4 --
5 -- Procedure
6 -- StartProcess
7 --
8 -- Description
9 -- Initiate workflow for a requisition
10 -- IN
11 -- RequestorId - PK of requestor, Item owner or Subscriber
12 -- ItemId - PK of Item published
13 -- ChannelId - PK of Channek
14 -- ProcessOwner - Requisition Process Owner Username from calling appl
15 -- Workflowprocess - Workflow process to run.
16 --
17 PROCEDURE StartProcess( RequestorId in number,
18 ItemId in number default null,
19 ChannelId in number,
20 Timeout in number default null,
21 ProcessOwner in varchar2,
22 WorkflowProcess in varchar2,
23 Item_Type in varchar2 default null) IS
24 --
25 --
26 ItemType varchar2(30) := nvl(Item_Type, 'AMV_APPR');
27 ItemKey varchar2(30);
28 ItemUserKey varchar2(80);
29
30 l_item_key number;
31 l_channel_name varchar2(80);
32 l_requestor varchar2(80);
33 l_item_name varchar2(240);
34 --
35 CURSOR Channel_Name IS
36 select channel_name
37 from amv_c_channels_vl
38 where channel_id = ChannelId;
39
40 CURSOR Item_Name IS
41 select item_name
42 from jtf_amv_items_vl
43 where item_id = ItemId;
44
45 CURSOR Requestor_Name IS
46 select FND.USER_NAME
47 from JTF_RS_RESOURCE_EXTNS RD
48 , FND_USER FND
49 where RD.USER_ID = FND.USER_ID
50 and RD.RESOURCE_ID = RequestorId;
51
52 CURSOR ItemKey_csr IS
53 select amv_wf_requests_s.nextval
54 from dual;
55
56 BEGIN
57 OPEN Channel_Name;
58 FETCH Channel_Name INTO l_channel_name;
59 CLOSE Channel_Name;
60
61 OPEN Requestor_Name;
62 FETCH Requestor_Name INTO l_requestor;
63 CLOSE Requestor_Name;
64
65 --
66 -- keyid for Item key
67 OPEN ItemKey_csr;
68 FETCH ItemKey_csr INTO l_item_key;
69 CLOSE ItemKey_csr;
70
71 IF WorkflowProcess = AMV_UTILITY_PVT.G_PUB_APPROVAL THEN
72 --
73 OPEN Item_Name;
74 FETCH Item_Name INTO l_item_name;
75 CLOSE Item_Name;
76
77 ItemKey := 'PUB'||l_item_key;
78 ItemUserKey := substr(l_channel_name,1,19)||'_'||substr(l_item_name,1,60);
79 --
80 ELSE
81 --
82 ItemKey := 'SUB'||l_item_key;
83 ItemUserKey:=substr(l_channel_name,1,59)||'_'||RequestorId;
84 --
85 END IF;
86
87 --
88 -- Start Process :
89 --
90 wf_engine.CreateProcess( ItemType => ItemType,
91 ItemKey => ItemKey,
92 process => WorkflowProcess );
93
94 wf_engine.SetItemUserKey ( ItemType => ItemType,
95 ItemKey => ItemKey,
96 UserKey => ItemUserKey);
97 --
98 -- Initialize workflow item attributes
99 --
100 wf_engine.SetItemAttrNumber (itemtype => itemtype,
101 itemkey => itemkey,
102 aname => 'AMV_CHANNEL_ID',
103 avalue => ChannelId);
104 --
105 wf_engine.SetItemAttrText ( itemtype => itemtype,
106 itemkey => itemkey,
107 aname => 'AMV_CHANNEL_NAME',
108 avalue => l_channel_name);
109 --
110 wf_engine.SetItemAttrNumber (itemtype => itemtype,
111 itemkey => itemkey,
112 aname => 'AMV_REQUESTOR_ID',
113 avalue => RequestorId);
114 --
115 wf_engine.SetItemAttrText ( itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'AMV_REQUESTOR',
118 avalue => l_requestor);
119 --
120 wf_engine.SetItemAttrNumber (itemtype => itemtype,
121 itemkey => itemkey,
122 aname => 'AMV_ITEM_ID',
123 avalue => ItemId);
124 --
125 wf_engine.SetItemAttrText ( itemtype => itemtype,
126 itemkey => itemkey,
127 aname => 'AMV_ITEM_NAME',
128 avalue => l_item_name);
129 --
130 wf_engine.SetItemAttrNumber (itemtype => itemtype,
131 itemkey => itemkey,
132 aname => 'AMV_TIMEOUT',
133 avalue => Timeout);
134 --
135 wf_engine.SetItemAttrText ( itemtype => itemtype,
136 itemkey => itemkey,
137 aname => 'AMV_WORKFLOW_PROCESS',
138 avalue => WorkflowProcess);
139
140 --
141 wf_engine.SetItemOwner ( itemtype => itemtype,
142 itemkey => itemkey,
143 owner => ProcessOwner );
144
145 --
146 wf_engine.StartProcess( itemtype => itemtype,
147 itemkey => itemkey );
148 --
149 EXCEPTION
150 WHEN OTHERS THEN
151 --
152 wf_core.context( 'AMV_WFAPPROVAL_PVT',
153 'StartProcess',
154 itemtype, itemkey,to_char(ChannelId),Workflowprocess);
155 RAISE;
156 --
157 END StartProcess;
158 --------------------------------------------------------------------------------
159 --------------------------------------------------------------------------------
160 --
161 -- Procedure
162 -- Is_ChannelApprover
163 --
164 -- Description
165 -- Check if the User is an approver for the channel
166 -- IN
167 -- channel_id
168 -- user_id
169 --
170 -- Returns
171 -- TRUE - If User has privilege to approve channel
172 -- FALSE - If User does not have the privilege to approve channel
173 --
174 FUNCTION Is_ChannelApprover ( channel_id in number,
175 user_id in number ) return boolean IS
176 --
177 l_api_version number := 1.0;
178 l_return_status varchar2(1);
179 l_msg_count number;
180 l_msg_data varchar2(80);
181 l_setup_result varchar2(1) := FND_API.G_FALSE;
182 --
183 l_default_approver_id number;
184 l_owner_id number;
185 l_create_flag varchar2(1);
186 l_approver_flag varchar2(1);
187 --
188 CURSOR Chan_Approvers IS
189 select default_approver_user_id
190 , owner_user_id
191 from amv_c_channels_b
192 where channel_id = channel_id;
193
194 CURSOR Sec_Approvers IS
195 select chl_approver_flag
196 from amv_u_access
197 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
198 and access_to_table_record_id = channel_id
199 and user_or_group_type = AMV_UTILITY_PVT.G_USER
200 and user_or_group_id = user_id;
201
202 BEGIN
203 --
204 AMV_USER_PVT.Can_ApproveContent(
205 p_api_version => l_api_version,
206 x_return_status => l_return_status,
207 x_msg_count => l_msg_count,
208 x_msg_data => l_msg_data,
209 p_check_login_user => FND_API.G_FALSE,
210 p_resource_id => user_id,
211 p_include_group_flag => FND_API.G_TRUE,
212 x_result_flag => l_setup_result
213 );
214
215 IF l_setup_result = FND_API.G_TRUE THEN
216 return(TRUE);
217 ELSE
218 OPEN Chan_Approvers;
219 FETCH Chan_Approvers INTO l_default_approver_id, l_owner_id;
220 CLOSE Chan_Approvers;
221
222 IF l_default_approver_id = user_id THEN
223 return(TRUE);
224 ELSIF l_owner_id = user_id THEN
225 return(TRUE);
226 ELSE
227 OPEN Sec_Approvers;
228 FETCH Sec_Approvers INTO l_approver_flag;
229 CLOSE Sec_Approvers;
230
231 IF l_approver_flag = FND_API.G_TRUE THEN
232 return(TRUE);
233 ELSE
234 return(FALSE);
235 END IF;
236 END IF;
237 END IF;
238 --
239 EXCEPTION
240 WHEN OTHERS THEN
241 wf_core.context('AMV_WFAPPROVAL_PVT',
242 'Is_ChannelApprover',
243 channel_id,user_id);
244 RAISE;
245 END Is_ChannelApprover;
246 --------------------------------------------------------------------------------
247 --------------------------------------------------------------------------------
248 --
249 -- Procedure
250 -- Can_Publish
251 --
252 -- Workflow cover: Check if the user can publish a document in the channel
253 -- IN
254 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
255 -- itemkey - A string generated from the application object's primary key.
256 -- actid - The function activity(instance id).
257 -- funcmode - Run/Cancel/Timeout
258 -- OUT
259 -- Resultout - 'COMPLETE:Y' IF user can subscribe without approval
260 -- - 'COMPLETE:N' IF user cannot subscribe without approval
261 --
262 -- USED BY ACTIVITIES
263 -- <ITEM_TYPE> <ACTIVITY>
264 -- AMV_APPROVAL_PVT AMV_CAN_PUBLISH
265 --
266 --
267 PROCEDURE Can_Publish ( itemtype in varchar2,
268 itemkey in varchar2,
269 actid in number,
270 funcmode in varchar2,
271 resultout OUT NOCOPY varchar2 ) IS
272 --
273 l_api_version number := 1.0;
274 l_return_status varchar2(1);
275 l_msg_count number;
276 l_msg_data varchar2(80);
277 l_setup_result varchar2(1);
278 --
279 l_channel_id number;
280 l_requestor_id number;
281 l_create_flag varchar2(1);
282 l_publish_flag varchar2(1);
283 --
284 CURSOR Chn_Access IS
285 select can_create_flag
286 from amv_u_access
287 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
288 and access_to_table_record_id = l_channel_id
289 and user_or_group_type = AMV_UTILITY_PVT.G_USER
290 and user_or_group_id = l_requestor_id;
291
292 CURSOR Chn_Publish IS
293 select pub_need_approval_flag
294 from amv_c_channels_b
295 where channel_id = l_channel_id;
296
297 BEGIN
298 --
299 -- RUN mode - normal process execution
300 --
301 IF (funcmode = 'RUN') THEN
302 l_channel_id := wf_engine.GetItemAttrNumber(
303 itemtype => itemtype,
304 itemkey => itemkey,
305 aname => 'AMV_CHANNEL_ID' );
306
307 l_requestor_id := wf_engine.GetItemAttrNumber(
308 itemtype => itemtype,
309 itemkey => itemkey,
310 aname => 'AMV_REQUESTOR_ID' );
311
312 --
313 AMV_USER_PVT.Can_PublishContent(
314 p_api_version => l_api_version,
315 x_return_status => l_return_status,
316 x_msg_count => l_msg_count,
317 x_msg_data => l_msg_data,
318 p_check_login_user => FND_API.G_FALSE,
319 p_resource_id => l_requestor_id,
320 p_include_group_flag => FND_API.G_TRUE,
321 x_result_flag => l_setup_result
322 );
323
324 IF l_setup_result = FND_API.G_TRUE THEN
325 resultout := wf_engine.eng_completed||':Y';
326 ELSE
327 OPEN Chn_Publish;
328 FETCH Chn_Publish INTO l_publish_flag;
329 CLOSE Chn_Publish;
330
331 IF l_publish_flag = FND_API.G_TRUE THEN
332 IF Is_ChannelApprover(l_channel_id, l_requestor_id) THEN
333 resultout := wf_engine.eng_completed||':Y';
334 ELSE
335 OPEN Chn_Access;
336 FETCH Chn_Access INTO l_create_flag;
337 CLOSE Chn_Access;
338
339 IF l_create_flag = FND_API.G_TRUE THEN
340 resultout := wf_engine.eng_completed||':Y';
341 ELSE
342 resultout := wf_engine.eng_completed||':N';
343 END IF;
344 END IF;
345 ELSE
346 resultout := wf_engine.eng_completed||':Y';
347 END IF;
348 END IF;
349 --
350
351 --
352 return;
353 --
354 END IF;
355 --
356
357 --
358 -- CANCEL mode
359 --
360 IF (funcmode = 'CANCEL') THEN
361 --
362 -- Return process to run
363 --
364 resultout := 'COMPLETE:';
365 return;
366 END IF;
367 --
368 -- TIMEOUT mode
369 --
370 IF (funcmode = 'TIMEOUT') THEN
371 resultout := 'COMPLETE:';
372 return;
373 END IF;
374 --
375 EXCEPTION
376 WHEN OTHERS THEN
377 wf_core.context('AMV_WFAPPROVAL_PVT',
378 'Can_Publish',
379 itemtype, itemkey,to_char(actid),funcmode);
380 RAISE;
381 END Can_Publish;
382 --------------------------------------------------------------------------------
383 --------------------------------------------------------------------------------
384 --
385 -- Procedure
386 -- Can_Subscribe
387 --
388 -- Workflow cover: Check if user can subscribe to a channel without approval
389 -- IN
390 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
391 -- itemkey - A string generated from the application object's primary key.
392 -- actid - The function activity(instance id).
393 -- funcmode - Run/Cancel/Timeout
394 -- OUT
395 -- Resultout - 'COMPLETE:Y' IF user can subscribe without approval
396 -- - 'COMPLETE:N' IF user cannot subscribe without approval
397 --
398 -- USED BY ACTIVITIES
399 -- <ITEM_TYPE> <ACTIVITY>
400 -- AMV_APPROVAL_PVT AMV_CAN_SUBSCRIBE
401 --
402 --
403 PROCEDURE Can_Subscribe (itemtype in varchar2,
404 itemkey in varchar2,
405 actid in number,
406 funcmode in varchar2,
407 resultout OUT NOCOPY varchar2 ) IS
408 --
409 l_channel_id number;
410 l_requestor_id number;
411 l_view_flag varchar2(1);
412 l_subscribe_flag varchar2(1);
413 --
414 CURSOR Chn_Access IS
415 select can_view_flag
416 from amv_u_access
417 where access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
418 and access_to_table_record_id = l_channel_id
419 and user_or_group_type = AMV_UTILITY_PVT.G_USER
420 and user_or_group_id = l_requestor_id;
421
422 CURSOR Chn_Subscribe IS
423 select sub_need_approval_flag
424 from amv_c_channels_b
425 where channel_id = l_channel_id;
426
427 BEGIN
428 --
429 -- RUN mode - normal process execution
430 --
431 IF (funcmode = 'RUN') THEN
432 l_channel_id := wf_engine.GetItemAttrNumber(
433 itemtype => itemtype,
434 itemkey => itemkey,
435 aname => 'AMV_CHANNEL_ID' );
436
437 l_requestor_id := wf_engine.GetItemAttrNumber(
438 itemtype => itemtype,
439 itemkey => itemkey,
440 aname => 'AMV_REQUESTOR_ID' );
441
442 OPEN Chn_Subscribe;
443 FETCH Chn_Subscribe INTO l_subscribe_flag;
444 CLOSE Chn_Subscribe;
445
446 IF l_subscribe_flag = FND_API.G_TRUE THEN
447 IF Is_ChannelApprover(l_channel_id, l_requestor_id) THEN
448 resultout := wf_engine.eng_completed||':Y';
449 ELSE
450 OPEN Chn_Access;
451 FETCH Chn_Access INTO l_view_flag;
452 CLOSE Chn_Access;
453
454 IF l_view_flag = FND_API.G_TRUE THEN
455 resultout := wf_engine.eng_completed||':Y';
456 ELSE
457 resultout := wf_engine.eng_completed||':N';
458 END IF;
459 END IF;
460 ELSE
461 resultout := wf_engine.eng_completed||':Y';
462 END IF;
463 --
464
465 --
466 return;
467 --
468 END IF;
469 --
470
471 --
472 -- CANCEL mode
473 --
474 IF (funcmode = 'CANCEL') THEN
475 --
476 -- Return process to run
477 --
478 resultout := 'COMPLETE:';
479 return;
480 END IF;
481 --
482 -- TIMEOUT mode
483 --
484 IF (funcmode = 'TIMEOUT') THEN
485 resultout := 'COMPLETE:';
486 return;
487 END IF;
488 --
489 EXCEPTION
490 WHEN OTHERS THEN
491 wf_core.context('AMV_WFAPPROVAL_PVT',
492 'Can_Subscribe',
493 itemtype, itemkey,to_char(actid),funcmode);
494 RAISE;
495 END Can_Subscribe;
496 --------------------------------------------------------------------------------
497 --------------------------------------------------------------------------------
498 --
499 -- Procedure
500 -- GetApprover
501 --
502 -- Description
503 -- Select an Approver
504 -- IN
505 -- channel_id
506 -- approver_type
507 -- approver_out
508 -- Out
509 -- approver
510 --
511 PROCEDURE GetApprover ( channel_id in number,
512 approver_in_type in varchar2,
513 approver_out_type OUT NOCOPY varchar2,
514 approvers OUT NOCOPY varchar2 ) IS
515 --
516 l_default_approver varchar2(100);
517 l_channel_owner varchar2(100);
518 l_secn_approver varchar2(100);
519 l_record_counter number;
520 --
521 -- NOTE Remove PPX relation in the cursor
522 CURSOR Chan_Approvers(chn_id NUMBER) IS
523 select FND1.USER_NAME
524 from JTF_RS_RESOURCE_EXTNS RD
525 , FND_USER FND1
526 , AMV_C_CHANNELS_B CHN
527 where RD.USER_ID = FND1.USER_ID
528 and RD.RESOURCE_ID = CHN.DEFAULT_APPROVER_USER_ID
529 and CHN.CHANNEL_ID = chn_id;
530
531 CURSOR Secn_Approvers IS
532 select fu.user_name
533 from amv_u_access acc
534 , fnd_user fu
535 where acc.access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
536 and acc.access_to_table_record_id = GetApprover.channel_id
537 and acc.user_or_group_type = AMV_UTILITY_PVT.G_USER
538 and acc.user_or_group_id = fu.user_id;
539 --
540 BEGIN
541 OPEN Chan_Approvers(channel_id);
542 FETCH Chan_Approvers INTO l_default_approver;
543 CLOSE Chan_Approvers;
544
545 IF approver_in_type = AMV_UTILITY_PVT.G_DEFAULT THEN
546 approvers := l_default_approver;
547 approver_out_type := AMV_UTILITY_PVT.G_DEFAULT;
548 ELSIF approver_in_type = AMV_UTILITY_PVT.G_OWNER THEN
549 approvers := l_channel_owner;
550 approver_out_type := AMV_UTILITY_PVT.G_OWNER;
551 ELSIF approver_in_type = AMV_UTILITY_PVT.G_SECONDARY THEN
552 OPEN Secn_Approvers;
553 LOOP
554 FETCH Secn_Approvers INTO l_secn_approver;
555 EXIT WHEN Secn_Approvers%NOTFOUND;
556 l_record_counter := l_record_counter + 1;
557 approvers := approvers||' '||l_secn_approver;
558 approver_out_type := AMV_UTILITY_PVT.G_SECONDARY;
559 END LOOP;
560 CLOSE Secn_Approvers;
561
562 IF (l_record_counter is null) THEN
563 approvers := l_channel_owner;
564 approver_out_type := AMV_UTILITY_PVT.G_OWNER;
565 END IF;
566 ELSE
567 approver_out_type := AMV_UTILITY_PVT.G_DONE;
568 END IF;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 wf_core.context('AMV_WFAPPROVAL_PVT','GetApprover',channel_id);
573 RAISE;
574 END GetApprover;
575 --------------------------------------------------------------------------------
576 --------------------------------------------------------------------------------
577 --
578 -- Select_Approver
579 -- Workflow cover: Select a channel approver and set Workflow item attributes
580 -- IN
581 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
582 -- itemkey - A string generated from the application object's primary key.
583 -- actid - The function activity(instance id).
584 -- funcmode - Run/Cancel/Timeout
585 -- OUT
586 -- Resultout - 'COMPLETE:T' IF channel has approver
587 -- - 'COMPLETE:F' IF channel does not any more approver
588 --
589 -- USED BY ACTIVITIES
590 -- <ITEM_TYPE> <ACTIVITY>
591 -- AMV_APPROVAL_PVT AMV_SELECT_APPROVER
592 --
593 PROCEDURE Select_Approver ( itemtype in varchar2,
594 itemkey in varchar2,
595 actid in number,
596 funcmode in varchar2,
597 resultout OUT NOCOPY varchar2 ) IS
598 --
599 l_channel_id number;
600 l_requestor_id number;
601 l_item_id number;
602 l_channel_name varchar2(80);
603 l_item_name varchar2(240);
604 l_comments varchar2(4000);
605 l_forward_to_username varchar2(4000);
606 l_forward_to_usertype varchar2(30);
607 l_workflow_process varchar2(30);
608 l_timeout number := 3;
609 l_out_usertype varchar2(30);
610 l_req_subject varchar2(240);
611 l_req_body varchar2(2000);
612 l_role_name varchar2(100);
613 l_role_display_name varchar2(240);
614 l_role_description varchar2(240);
615 l_application_id number := 520;
616 --
617 BEGIN
618 --
619 -- RUN mode - normal process execution
620 --
621 IF (funcmode = 'RUN') THEN
622 l_channel_id := wf_engine.GetItemAttrNumber(
623 itemtype => itemtype,
624 itemkey => itemkey,
625 aname => 'AMV_CHANNEL_ID' );
626
627 l_requestor_id := wf_engine.GetItemAttrNumber(
628 itemtype => itemtype,
629 itemkey => itemkey,
630 aname => 'AMV_REQUESTOR_ID' );
631
632 l_item_id := wf_engine.GetItemAttrNumber(
633 itemtype => itemtype,
634 itemkey => itemkey,
635 aname => 'AMV_ITEM_ID' );
636
637 l_channel_name := wf_engine.GetItemAttrText(
638 itemtype => itemtype,
639 itemkey => itemkey,
640 aname => 'AMV_CHANNEL_NAME' );
641
642 l_item_name := wf_engine.GetItemAttrText(
643 itemtype => itemtype,
644 itemkey => itemkey,
645 aname => 'AMV_ITEM_NAME' );
646
647 l_comments := wf_engine.GetItemAttrText(
648 itemtype => itemtype,
649 itemkey => itemkey,
650 aname => 'AMV_COMMENTS' );
651
652 l_forward_to_usertype := wf_engine.GetItemAttrText(
653 itemtype => itemtype,
654 itemkey => itemkey,
655 aname => 'AMV_FORWARD_TO_USERTYPE' );
656
657 l_timeout := wf_engine.GetItemAttrNumber(
658 itemtype => itemtype,
659 itemkey => itemkey,
660 aname => 'AMV_TIMEOUT' );
661
662 l_workflow_process := wf_engine.GetItemAttrText(
663 itemtype => itemtype,
664 itemkey => itemkey,
665 aname => 'AMV_WORKFLOW_PROCESS' );
666
667 --
668 IF ( l_forward_to_usertype is null ) THEN
669 l_forward_to_usertype := AMV_UTILITY_PVT.G_DEFAULT;
670 END IF;
671 --
672
673 -- Call Application API to retrieve an approver
674 GetApprover ( l_channel_id,
675 l_forward_to_usertype,
676 l_out_usertype,
677 l_forward_to_username);
678
679 -- NOTE role name and display name are unique
680 -- NOTE set the notification to type
681 --
682 IF l_out_usertype = AMV_UTILITY_PVT.G_SECONDARY THEN
683 l_role_name := 'AMV_CHN:'||l_channel_id;
684 ELSIF l_out_usertype = AMV_UTILITY_PVT.G_OWNER THEN
685 l_role_name := 'AMV_APPR';
686 ELSE
687 l_role_name := l_forward_to_username;
688 END IF;
689
690 -- NOTE perform a check to validate role creation
691 wf_engine.SetItemAttrText (
692 itemtype=> itemtype,
693 itemkey => itemkey,
694 aname => 'AMV_CHANNEL_APPROVER',
695 avalue => l_role_name);
696 --
697 wf_engine.SetItemAttrText (
698 itemtype=> itemtype,
699 itemkey => itemkey,
700 aname => 'AMV_FORWARD_TO_USERNAME',
701 avalue => l_forward_to_username);
702 --
703
704 IF l_out_usertype = AMV_UTILITY_PVT.G_DEFAULT THEN
705 --
706 l_forward_to_usertype := AMV_UTILITY_PVT.G_SECONDARY;
707 resultout := 'COMPLETE:DEFAULT';
708 --
709 ELSIF l_out_usertype = AMV_UTILITY_PVT.G_SECONDARY THEN
710 --
711 l_forward_to_usertype := AMV_UTILITY_PVT.G_OWNER;
712 resultout := 'COMPLETE:SECONDARY';
713 --
714 ELSIF l_out_usertype = AMV_UTILITY_PVT.G_OWNER THEN
715 --
716 l_forward_to_usertype := AMV_UTILITY_PVT.G_DONE;
717 resultout := 'COMPLETE:OWNER';
718 --
719 ELSIF l_out_usertype = AMV_UTILITY_PVT.G_DONE THEN
720 --
721 l_forward_to_usertype := null;
722 resultout := 'COMPLETE:NONE';
723 --
724 END IF;
725 --
726 --
727 wf_engine.SetItemAttrText (
728 itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'AMV_FORWARD_TO_USERTYPE',
731 avalue => l_forward_to_usertype);
732 --
733 return;
734 --
735 END IF;
736 --
737
738 --
739 -- CANCEL mode - activity 'compensation'
740 --
741 IF (funcmode = 'CANCEL') THEN
742 --
743 -- Return process to run
744 --
745 resultout := 'COMPLETE:';
746 return;
747 END IF;
748 --
749 -- TIMEOUT mode
750 --
751 IF (funcmode = 'TIMEOUT') THEN
752 resultout := 'COMPLETE:';
753 return;
754 END IF;
755 --
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 wf_core.context('AMV_WFAPPROVAL_PVT',
760 'Selector_Approver',
761 itemtype, itemkey,to_char(actid),funcmode);
762 RAISE;
763 END Select_Approver;
764 --------------------------------------------------------------------------------
765 --------------------------------------------------------------------------------
766 --
767 -- Notification_Results
768 --
769 --
770 -- Notification_Results
771 -- Workflow cover: End the notification process based on the result
772 -- IN
773 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
774 -- itemkey - A string generated from the application object's primary key.
775 -- actid - The function activity(instance id).
776 -- funcmode - Run/Cancel/Timeout
777 -- OUT
778 --
779 -- USED BY ACTIVITIES
780 -- <ITEM_TYPE> <ACTIVITY>
781 -- AMV_APPROVAL_PVT AMV_NTF_APPROVAL
782 --
783 PROCEDURE Notification_Results (itemtype in varchar2,
784 itemkey in varchar2,
785 actid in number,
786 funcmode in varchar2,
787 resultout OUT NOCOPY varchar2 ) IS
788 --
789 l_result_code varchar2(30);
790 l_status varchar2(30);
791 l_group_id pls_integer;
792 l_user varchar2(30);
793
794 -- Select all lookup codes for an activities result type
795 cursor result_codes is
796 select wfl.lookup_code result_code
797 from wf_lookups wfl,
798 wf_activities wfa,
799 wf_process_activities wfpa,
800 wf_items wfi
801 where wfl.lookup_type = wfa.result_type
802 and wfa.name = wfpa.activity_name
803 and wfi.begin_date >= wfa.begin_date
804 and wfi.begin_date < nvl(wfa.end_date,wfi.begin_date+1)
805 and wfpa.activity_item_type = wfa.item_type
806 and wfpa.instance_id = actid
807 and wfi.item_key = itemkey
808 and wfi.item_type = itemtype;
809
810 BEGIN
811
812 -- Do nothing in cancel mode
813 if (funcmode <> wf_engine.eng_run) then
814 resultout := wf_engine.eng_null;
815 return;
816 end if;
817
818 --
819 -- RUN mode - normal process execution
820 --
821 IF (funcmode = wf_engine.eng_run) THEN
822 -- Get Notifications group_id for activity
823 Wf_Item_Activity_Status.Notification_Status(itemtype,itemkey,actid,
824 l_group_id,l_user);
825 -- check for notification status and set AMV_PROCESS_ERROR to 'COMPLETE' if processing is done
826 for result_rec in result_codes loop
827 --if (process_completed(l_group_id)) then
828 --resultout := wf_engine.eng_null;
829 --return;
830 --else
831 resultout := wf_engine.eng_completed||':'||l_result_code;
832 return;
833 --end if;
834 end loop;
835 END IF;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 wf_core.context('AMV_WFAPPROVAL_PVT',
840 'Notification_Results',
841 itemtype, itemkey,to_char(actid),funcmode);
842 RAISE;
843 END Notification_Results;
844 --------------------------------------------------------------------------------
845 --------------------------------------------------------------------------------
846 --
847 -- Procedure
848 -- Approve_Item
849 --
850 -- Description - Publish an Item - Set Channel Item Match to approved
851 --
852 -- IN
853 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
854 -- itemkey - A string generated from the application object's primary key.
855 -- itemuserkey - A string generated from the application object user-friendly
856 -- primary key.
857 -- actid - The function activity(instance id).
858 -- processowner - The username owner for this item instance.
859 -- funcmode - Run/Cancel/Timeout
860 -- OUT
861 -- resultout - Name of workflow process to run
862 --
863 -- USED BY ACTIVITIES
864 -- <ITEM_TYPE> <ACTIVITY>
865 -- AMV_APPROVAL_PVT AMV_APPROVE_ITEM
866 --
867 PROCEDURE Approve_Item ( itemtype in varchar2,
868 itemkey in varchar2,
869 actid in number,
870 funcmode in varchar2,
871 resultout OUT NOCOPY varchar2 ) IS
872 l_api_version CONSTANT NUMBER := 1.0;
873 l_return_status varchar2(1);
874 l_msg_count number;
875 l_msg_data varchar2(4000);
876 --
877 l_item_id number;
878 l_channel_id number;
879 l_approval_status varchar2(30);
880 --
881 BEGIN
882 --
883 -- RUN mode - normal process execution
884 --
885 IF (funcmode = 'RUN') THEN
886 --
887 -- Return process to run
888 --
889 l_approval_status := wf_engine.GetItemAttrText(
890 itemtype => itemtype,
891 itemkey => itemkey,
892 aname => 'AMV_APPROVAL_STATUS' );
893
894 l_channel_id := wf_engine.GetItemAttrNumber(
895 itemtype => itemtype,
896 itemkey => itemkey,
897 aname => 'AMV_CHANNEL_ID' );
898
899 l_item_id := wf_engine.GetItemAttrNumber(
900 itemtype => itemtype,
901 itemkey => itemkey,
902 aname => 'AMV_ITEM_ID' );
903
904
905 IF l_approval_status is null THEN
906 AMV_CHANNEL_PVT.Set_ChannelApprovalStatus(
907 p_api_version => l_api_version,
908 p_init_msg_list => FND_API.G_FALSE,
909 p_commit => FND_API.G_FALSE,
910 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
911 x_return_status => l_return_status,
912 x_msg_count => l_msg_count,
913 x_msg_data => l_msg_data,
914 p_check_login_user => FND_API.G_FALSE,
915 p_channel_id => l_channel_id,
916 p_channel_name => FND_API.G_MISS_CHAR,
917 p_category_id => FND_API.G_MISS_NUM,
918 p_item_id => l_item_id,
919 p_approval_status => AMV_UTILITY_PVT.G_APPROVED);
920
921 -- Check if api completes sucessfully
922 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
923 wf_engine.SetItemAttrText ( itemtype => itemtype,
924 itemkey => itemkey,
925 aname => 'AMV_APPROVAL_STATUS',
926 avalue => 'NOTIFIED');
927 resultout := 'COMPLETE:Y';
928 return;
929 ELSE
930 --
931 wf_engine.SetItemAttrText ( itemtype => itemtype,
932 itemkey => itemkey,
933 aname => 'AMV_PROCESS_ERROR',
934 avalue => l_msg_data);
935 wf_engine.SetItemAttrText ( itemtype => itemtype,
936 itemkey => itemkey,
937 aname => 'AMV_APPROVAL_STATUS',
938 avalue => 'NOTIFIED');
939 resultout := 'COMPLETE:N';
940 return;
941 END IF;
942 END IF;
943 END IF;
944
945 --
946 -- CANCEL mode - activity 'compensation'
947 --
948 IF (funcmode = 'CANCEL') THEN
949 --
950 -- Return process to run
951 --
952 resultout := 'COMPLETE:';
953 return;
954 END IF;
955 --
956 -- TIMEOUT mode
957 --
958 IF (funcmode = 'TIMEOUT') THEN
959 resultout := 'COMPLETE:';
960 return;
961 END IF;
962 --
963 EXCEPTION
964 WHEN OTHERS THEN
965 wf_core.context('AMV_WFAPPROVAL_PVT',
966 'Approve_Item',
967 itemtype,itemkey,to_char(actid),funcmode);
968 RAISE;
969 END Approve_Item;
970 --------------------------------------------------------------------------------
971 --------------------------------------------------------------------------------
972 --
973 -- Procedure
974 -- Approve_Subscription
975 --
976 -- Description - Approves a subscription - creates a channel in my channel
977 --
978 -- IN
979 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
980 -- itemkey - A string generated from the application object's primary key.
981 -- itemuserkey - A string generated from the application object user-friendly
982 -- primary key.
983 -- actid - The function activity(instance id).
984 -- processowner - The username owner for this item instance.
985 -- funcmode - Run/Cancel
986 -- OUT
987 -- resultout - Name of workflow process to run
988 --
989 -- USED BY ACTIVITIES
990 -- <ITEM_TYPE> <ACTIVITY>
991 -- AMV_APPROVAL_PVT AMV_APPROVE_SUBSCRIPTON
992 --
993 PROCEDURE Approve_Subscription ( itemtype in varchar2,
994 itemkey in varchar2,
995 actid in number,
996 funcmode in varchar2,
997 resultout OUT NOCOPY varchar2 ) IS
998 --
999 l_api_version CONSTANT NUMBER := 1.0;
1000 l_return_status varchar2(1);
1001 l_msg_count number;
1002 l_msg_data varchar2(4000);
1003 --
1004 l_channel_id number;
1005 l_requestor_id number;
1006 l_mychannel_obj amv_mychannel_pvt.amv_my_channel_obj_type;
1007 l_mychannel_id number;
1008 l_approval_status varchar2(30);
1009 BEGIN
1010 --
1011 -- RUN mode - normal process execution
1012 --
1013 IF (funcmode = 'RUN') THEN
1014 --
1015 -- Return process to run
1016 --
1017 l_approval_status := wf_engine.GetItemAttrText(
1018 itemtype => itemtype,
1019 itemkey => itemkey,
1020 aname => 'AMV_APPROVAL_STATUS' );
1021
1022 l_channel_id := wf_engine.GetItemAttrNumber(
1023 itemtype => itemtype,
1024 itemkey => itemkey,
1025 aname => 'AMV_CHANNEL_ID' );
1026
1027 l_requestor_id := wf_engine.GetItemAttrNumber(
1028 itemtype => itemtype,
1029 itemkey => itemkey,
1030 aname => 'AMV_REQUESTOR_ID' );
1031
1032 IF l_approval_status is null THEN
1033 l_mychannel_obj.my_channel_id := FND_API.G_MISS_NUM;
1034 l_mychannel_obj.channel_type := FND_API.G_MISS_CHAR;
1035 l_mychannel_obj.access_level_type := FND_API.G_MISS_CHAR;
1036 l_mychannel_obj.user_or_group_id := l_requestor_id;
1037 l_mychannel_obj.user_or_group_type :=AMV_UTILITY_PVT.G_USER;
1038 l_mychannel_obj.subscribing_to_id := l_channel_id;
1039 l_mychannel_obj.subscribing_to_type := AMV_UTILITY_PVT.G_CHANNEL;
1040 l_mychannel_obj.subscription_reason_type:=AMV_UTILITY_PVT.G_SUBSCRIBED;
1041 l_mychannel_obj.order_number := FND_API.G_MISS_NUM;
1042 l_mychannel_obj.status := AMV_UTILITY_PVT.G_ACTIVE;
1043 l_mychannel_obj.notify_flag := FND_API.G_FALSE;
1044 l_mychannel_obj.notification_interval_type := FND_API.G_MISS_CHAR;
1045 /*
1046 l_mychannel_obj := amv_my_channel_obj_type(
1047 FND_API.G_MISS_NUM,
1048 FND_API.G_MISS_CHAR,
1049 FND_API.G_MISS_CHAR,
1050 l_requestor_id,
1051 AMV_UTILITY_PVT.G_USER,
1052 l_channel_id,
1053 AMV_UTILITY_PVT.G_CHANNEL,
1054 AMV_UTILITY_PVT.G_SUBSCRIBED,
1055 FND_API.G_MISS_NUM,
1056 AMV_UTILITY_PVT.G_ACTIVE,
1057 FND_API.G_FALSE,
1058 FND_API.G_MISS_CHAR);
1059 */
1060
1061 AMV_MYCHANNEL_PVT.Add_Subscription(
1062 p_api_version => l_api_version,
1063 p_init_msg_list => FND_API.G_FALSE,
1064 p_commit => FND_API.G_TRUE,
1065 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1066 x_return_status => l_return_status,
1067 x_msg_count => l_msg_count,
1068 x_msg_data => l_msg_data,
1069 p_check_login_user => FND_API.G_FALSE,
1070 p_mychannel_obj => l_mychannel_obj,
1071 x_mychannel_id => l_mychannel_id);
1072
1073 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1074 wf_engine.SetItemAttrText ( itemtype => itemtype,
1075 itemkey => itemkey,
1076 aname => 'AMV_APPROVAL_STATUS',
1077 avalue => 'NOTIFIED');
1078 resultout := 'COMPLETE:Y';
1079 return;
1080 ELSE
1081 --
1082 wf_engine.SetItemAttrText ( itemtype => itemtype,
1083 itemkey => itemkey,
1084 aname => 'AMV_PROCESS_ERROR',
1085 avalue => l_msg_data);
1086 wf_engine.SetItemAttrText ( itemtype => itemtype,
1087 itemkey => itemkey,
1088 aname => 'AMV_APPROVAL_STATUS',
1089 avalue => 'NOTIFIED');
1090 resultout := 'COMPLETE:N';
1091 return;
1092 END IF;
1093 END IF;
1094 END IF;
1095
1096 --
1097 -- CANCEL mode - activity 'compensation'
1098 --
1099 IF (funcmode = 'CANCEL') THEN
1100 resultout := 'COMPLETE:';
1101 return;
1102 END IF;
1103 --
1104 -- TIMEOUT mode
1105 --
1106 IF (funcmode = 'TIMEOUT') THEN
1107 resultout := 'COMPLETE:';
1108 return;
1109 END IF;
1110 --
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 wf_core.context('AMV_WFAPPROVAL_PVT',
1114 'Approve_Subscription',
1115 itemtype, itemkey,to_char(actid),funcmode);
1116 RAISE;
1117 END Approve_Subscription;
1118 --------------------------------------------------------------------------------
1119 --------------------------------------------------------------------------------
1120 --
1121 -- Procedure
1122 -- Reject_Item
1123 --
1124 -- Description - Does nothing currently
1125 --
1126 -- IN
1127 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1128 -- itemkey - A string generated from the application object's primary key.
1129 -- itemuserkey - A string generated from the application object user-friendly
1130 -- primary key.
1131 -- actid - The function activity(instance id).
1132 -- processowner - The username owner for this item instance.
1133 -- funcmode - Run/Cancel
1134 -- OUT
1135 -- resultout - Name of workflow process to run
1136 --
1137 -- USED BY ACTIVITIES
1138 -- <ITEM_TYPE> <ACTIVITY>
1139 -- AMV_APPROVAL_PVT AMV_REJECT_ITEM
1140 --
1141 PROCEDURE Reject_Item ( itemtype in varchar2,
1142 itemkey in varchar2,
1143 actid in number,
1144 funcmode in varchar2,
1145 resultout OUT NOCOPY varchar2 ) IS
1146 --
1147 l_api_version CONSTANT NUMBER := 1.0;
1148 l_return_status varchar2(1);
1149 l_msg_count number;
1150 l_msg_data varchar2(4000);
1151 --
1152 l_item_id number;
1153 l_channel_id number;
1154 l_approval_status varchar2(30);
1155 BEGIN
1156 --
1157 -- RUN mode - normal process execution
1158 --
1159 IF (funcmode = 'RUN') THEN
1160 --
1161 -- Return process to run
1162 --
1163 l_approval_status := wf_engine.GetItemAttrText(
1164 itemtype => itemtype,
1165 itemkey => itemkey,
1166 aname => 'AMV_APPROVAL_STATUS' );
1167
1168 l_channel_id := wf_engine.GetItemAttrNumber(
1169 itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'AMV_CHANNEL_ID' );
1172
1173 l_item_id := wf_engine.GetItemAttrNumber(
1174 itemtype => itemtype,
1175 itemkey => itemkey,
1176 aname => 'AMV_ITEM_ID' );
1177
1178 IF l_approval_status is null THEN
1179 AMV_CHANNEL_PVT.Set_ChannelApprovalStatus(
1180 p_api_version => l_api_version,
1181 p_init_msg_list => FND_API.G_FALSE,
1182 p_commit => FND_API.G_FALSE,
1183 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1184 x_return_status => l_return_status,
1185 x_msg_count => l_msg_count,
1186 x_msg_data => l_msg_data,
1187 p_check_login_user => FND_API.G_FALSE,
1188 p_channel_id => l_channel_id,
1189 p_channel_name => FND_API.G_MISS_CHAR,
1190 p_category_id => FND_API.G_MISS_NUM,
1191 p_item_id => l_item_id,
1192 p_approval_status => AMV_UTILITY_PVT.G_REJECTED);
1193
1194 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1195 wf_engine.SetItemAttrText ( itemtype => itemtype,
1196 itemkey => itemkey,
1197 aname => 'AMV_APPROVAL_STATUS',
1198 avalue => 'NOTIFIED');
1199 resultout := 'COMPLETE:Y';
1200 return;
1201 ELSE
1202 --
1203 wf_engine.SetItemAttrText ( itemtype => itemtype,
1204 itemkey => itemkey,
1205 aname => 'AMV_PROCESS_ERROR',
1206 avalue => l_msg_data);
1207 wf_engine.SetItemAttrText ( itemtype => itemtype,
1208 itemkey => itemkey,
1209 aname => 'AMV_APPROVAL_STATUS',
1210 avalue => 'NOTIFIED');
1211 resultout := 'COMPLETE:N';
1212 return;
1213 END IF;
1214 END IF;
1215 END IF;
1216
1217 --
1218 -- CANCEL mode - activity 'compensation'
1219 --
1220 IF (funcmode = 'CANCEL') THEN
1221 resultout := 'COMPLETE:';
1222 return;
1223 END IF;
1224 --
1225 -- TIMEOUT mode
1226 --
1227 IF (funcmode = 'TIMEOUT') THEN
1228 resultout := 'COMPLETE:';
1229 return;
1230 END IF;
1231 --
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 wf_core.context('AMV_WFAPPROVAL_PVT',
1235 'Reject_Item',
1236 itemtype,itemkey,to_char(actid),funcmode);
1237 RAISE;
1238 END Reject_Item;
1239 --------------------------------------------------------------------------------
1240 --------------------------------------------------------------------------------
1241 END amv_wfapproval_pvt;