[Home] [Help]
PACKAGE BODY: APPS.ASO_PA_APR_WF_PVT
Source
1 PACKAGE BODY ASO_PA_APR_WF_PVT AS
2 /* $Header: asovwpab.pls 120.0.12020000.17 2013/05/07 06:53:06 rassharm noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_PA_APR_WF_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovwpab.pls';
6
7 g_user_id NUMBER:= FND_GLOBAL.USER_ID;
8
9 g_next_approvers ame_util.approversTable2;
10
11
12 PROCEDURE start_aso_approvals (
13 P_Object_approval_id IN NUMBER,
14 P_itemtype_name IN VARCHAR2,
15 P_sender_name IN VARCHAR2
16 ) AS
17 l_itemkey VARCHAR2 (30);
18 l_itemtype VARCHAR2 (30);
19 l_requestor_display_name VARCHAR2 (240);
20 l_object_approval_id NUMBER;
21 l_api_name varchar2(240):= 'start_aso_approvals';
22
23 CURSOR C_get_appr_id IS
24 SELECT min(object_approval_id)
25 FROM aso_apr_obj_approvals
26 WHERE object_id = (SELECT object_id
27 FROM aso_apr_obj_approvals
28 WHERE object_approval_id = P_Object_approval_id)
29 AND approval_status = 'PEND';
30
31 BEGIN
32 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
33 aso_debug_pub.ADD (
34 'Begin ASO_PA_APR_WF_PVT package ',
35 1,
36 'N'
37 );
38 aso_debug_pub.ADD (
39 'Starting Approval Process for approval id ' || P_Object_approval_id,
40 1,
41 'N'
42 );
43 aso_debug_pub.ADD (
44 'Starting Approval Process for sender_name ' || P_sender_name,
45 1,
46 'N'
47 );
48 END IF;
49
50 OPEN C_get_appr_id;
51 FETCH C_get_appr_id INTO l_object_approval_id;
52 CLOSE C_get_appr_id;
53
54 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
55 aso_debug_pub.ADD('l_object_approval_id: '|| l_object_approval_id,1,'N');
56 END IF;
57
58 IF (P_Object_approval_id = l_object_approval_id) THEN
59
60 l_itemtype := P_itemtype_name; --'ASOAPPRV';
61 l_itemkey := TO_CHAR (
62 P_Object_approval_id
63 ) || 'HED';
64 wf_engine.createprocess (
65 itemtype => l_itemtype,
66 itemkey => l_itemkey,
67 process => 'PARALLELAPR'
68 );
69 wf_engine.setitemowner (
70 l_itemtype,
71 l_itemkey,
72 P_sender_name
73 );
74 wf_engine.setitemuserkey (
75 itemtype => l_itemtype,
76 itemkey => l_itemkey,
77 userkey => l_itemkey
78 );
79 wf_engine.setitemattrtext (
80 itemtype => l_itemtype,
81 itemkey => l_itemkey,
82 aname => 'REQUESTOR_USERNAME',
83 avalue => P_sender_name
84 );
85 wf_engine.setitemattrnumber (
86 itemtype => l_itemtype,
87 itemkey => l_itemkey,
88 aname => 'APPROVALID',
89 avalue => P_Object_approval_id
90 );
91
92 wf_engine.setitemattrtext (
93 itemtype => l_itemtype,
94 itemkey => l_itemkey,
95 aname => 'NEWPROCESSFLAG',
96 avalue =>'Y'
97 );
98
99
100 l_requestor_display_name := wf_directory.getroledisplayname (
101 P_sender_name
102 );
103 wf_engine.setitemattrtext (
104 itemtype => l_itemtype,
105 itemkey => l_itemkey,
106 aname => 'REQUESTOR_DISPLAYNAME',
107 avalue => l_requestor_display_name
108 );
109 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
110 aso_debug_pub.ADD (
111 'Requester DisplayName is :' || l_requestor_display_name,
112 1,
113 'N'
114 );
115 END IF;
116
117 wf_engine.startprocess (
118 itemtype => l_itemtype,
119 itemkey => l_itemkey
120 );
121
122 ELSE -- approval_id and obj_approval_id are not the same
123
124 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
125 aso_debug_pub.ADD('Skipping the create approval workflow',1,'N');
126 aso_debug_pub.ADD('***** NOTE: APPROVAL WORKFLOW PROCESS HAS NOT BEEN STARTED',1,'N');
127 END IF;
128 END IF;
129
130
131
132 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
133 aso_debug_pub.ADD (
134 'End of Start_ASO_Approvals ',
135 1,
136 'N'
137 );
138 END IF;
139 EXCEPTION
140 WHEN OTHERS
141 THEN
142 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
143 aso_debug_pub.ADD (
144 'Exception in Start_ASO_Approvals Proc SqlCode :' || SQLERRM,
145 1,
146 'N'
147 );
148 END IF;
149 wf_core.CONTEXT (
150 'ASO_APPROVE',
151 l_api_name,
152 P_Object_approval_id,
153 P_sender_name
154 );
155 RAISE;
156 END start_aso_approvals;
157
158
159 PROCEDURE update_approval_status (
160 p_update_header_or_detail_flag IN VARCHAR2,
161 p_object_approval_id IN NUMBER,
162 p_approval_det_id IN NUMBER,
163 p_status IN VARCHAR2,
164 note IN VARCHAR2
165 ) is
166 pragma AUTONOMOUS_TRANSACTION;
167 l_object_version_number number;
168 begin
169 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
170 aso_debug_pub.ADD (
171 'Start update_approval_status procedure ',
172 1,
173 'N'
174 );
175
176 aso_debug_pub.ADD (
177 'Flag is :' || p_update_header_or_detail_flag,
178 1,
179 'N'
180 );
181 aso_debug_pub.ADD (
182 'Object Approval ID is :' || p_object_approval_id,
183 1,
184 'N'
185 );
186 aso_debug_pub.ADD (
187 'Approval Detail ID is :' || p_approval_det_id,
188 1,
189 'N'
190 );
191 aso_debug_pub.ADD (
192 'Status is :' || p_status,
193 1,
194 'N'
195 );
196
197 END IF;
198
199 IF p_update_header_or_detail_flag = 'HEADER' THEN
200 select nvl(object_version_number,0) into l_object_version_number
201 from aso_apr_obj_approvals
202 where object_approval_id=p_object_approval_id;
203
204 l_object_version_number:=l_object_version_number+1;
205 IF (p_status = 'PEND')
206 THEN
207 UPDATE aso_apr_obj_approvals
208 SET approval_status = p_status,
209 object_version_number = l_object_version_number,
210 last_update_date = SYSDATE,
211 last_updated_by = g_user_id,
212 last_update_login = g_user_id
213 WHERE object_approval_id = p_object_approval_id;
214 ELSE
215 UPDATE aso_apr_obj_approvals
216 SET approval_status = p_status,
217 object_version_number = l_object_version_number,
218 last_update_date = SYSDATE,
219 end_date = SYSDATE,
220 last_updated_by = g_user_id,
221 last_update_login = g_user_id
222 WHERE object_approval_id = p_object_approval_id;
223 END IF;
224
225 END IF;
226
227 IF p_update_header_or_detail_flag = 'DETAIL' THEN
228 UPDATE aso_apr_approval_details
229 SET approver_status = p_status,
230 date_received = SYSDATE,
231 last_update_date = SYSDATE,
232 approver_comments = note,
233 last_updated_by = fnd_global.user_id,
234 last_update_login = fnd_global.user_id
235 WHERE approval_det_id = p_approval_det_id;
236 END IF;
237
238 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
239 aso_debug_pub.ADD (
240 'End update_approval_status procedure ',
241 1,
242 'N'
243 );
244 END IF;
245 commit;
246
247 end update_approval_status;
248
249 ----------------------------------------------------------------------------------
250 --Start of Comments
251 --Name: get_next_approvers
252 --Function:
253 -- Workflow activity PL/SQL handler
254 -- Get the next approver name from the AME approval list
255 -- And update workflow attributes.
256 -- If no next approver is found, approval routing will terminate.
257 --Parameters:
258 --IN:
259 -- Standard workflow IN parameters
260 --OUT:
261 -- Standard workflow OUT parameters
262 --
263 --End of Comments
264 -------------------------------------------------------------------------------
265
266 procedure Get_Next_Approvers(itemtype in varchar2,
267 itemkey in varchar2,
268 actid in number,
269 funcmode in varchar2,
270 resultout out NOCOPY varchar2) IS
271
272 l_completeYNO VARCHAR2(1);
273 -- l_position_has_valid_approvers VARCHAR2(10);
274 l_need_to_get_next_approver BOOLEAN;
275 l_ame_exception ame_util.longestStringType:=null;
276 l_api_name VARCHAR2(500) := 'get_next_approvers';
277
278 l_approval_id number;
279 l_object_id NUMBER;
280 l_object_type VARCHAR2 (240);
281 l_application_id NUMBER;
282 x_rules_list_id ame_util.idList;
283
284 CURSOR get_application_id (
285 c_object_approval_id NUMBER
286 ) IS
287 SELECT DISTINCT object_id, object_type, application_id
288 FROM aso_apr_obj_approvals aoa
289 WHERE object_approval_id = c_object_approval_id;
290
291 Begin
292
293 --pp_debug('Entered Get next approvers');
294 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
295 aso_debug_pub.ADD (
296 'Start Get_Next_Approvers procedure '||funcmode,
297 1,
298 'N'
299 );
300
301 END IF;
302
303 IF (funcmode = 'RUN') THEN
304 /* l_ame_exception := wf_engine.GetItemAttrText(itemtype => itemtype,
305 itemkey => itemkey,
306 aname => 'AME_EXCEPTION',
307 ignore_notfound => TRUE);
308
309 IF l_ame_exception IS NOT NULL THEN
310 resultout:='COMPLETE:'||'INVALID_APPROVER';
311 RETURN;
312 END IF;
313 */
314 l_approval_id := wf_engine.getitemattrnumber (
315 itemtype,
316 itemkey,
317 'APPROVALID'
318 );
319 open get_application_id(l_approval_id);
320 fetch get_application_id into l_object_id,l_object_type,l_application_id;
321 close get_application_id;
322
323 --pp_debug('In Get next approvers l_approval_id'||l_approval_id);
324 --pp_debug('In Get next approvers l_object_id'||l_object_id);
325 --pp_debug('In Get next approvers l_object_type'||l_object_type);
326 --pp_debug('In Get next approvers l_application_id'||l_application_id);
327
328 BEGIN
329
330 -- inserting rule details
331 ame_api3.getApplicableRules1(applicationIdIn =>l_application_id,
332 transactionTypeIn =>l_object_type,
333 transactionIdIn => l_object_id,
334 ruleIdsOut => x_rules_list_id);
335
336 --pp_debug('after ame call'||x_rules_list_id.count);
337 insertruledetails(approvalid => l_approval_id,
338 p_rule_list_ids=> x_rules_list_id);
339 --pp_debug('after insert rules call');
340
341 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
342 ame_api2.getNextApprovers4( applicationIdIn=>l_application_id,
343 transactionIdIn=>l_object_id,
344 transactionTypeIn=>l_object_type,
345 flagapproversasnotifiedin => ame_util.booleantrue,
346 approvalProcessCompleteYNOut=>l_completeYNO,
347 nextApproversOut=>g_next_approvers
348 );
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 if SQLCODE= -20212 then
353 -- pp_debug('SQLCODE 20212');
354 resultout:=wf_engine.eng_completed || ':' ||'INVALID_APPROVER';
355 wf_core.context
356 (itemtype,
357 'Get_Next_Approvers: Unable to get the surrogate approver from AME.',
358 itemtype,
359 itemkey);
360
361 -- updating the ASO_APR_OBJ_APPROVALS
362 update_approval_status (
363 p_update_header_or_detail_flag => 'HEADER' ,
364 p_object_approval_id => l_approval_id,
365 p_approval_det_id =>null,
366 p_status => 'REJ',
367 note => null);
368
369 -- updating the quote entity
370 update_entity(
371 p_object_approval_id => l_approval_id,
372 p_status => 'REJECT');
373
374
375 else
376
377 resultout:=wf_engine.eng_completed || ':' ||'INVALID_APPROVER';
378 wf_core.context
379 (itemtype,
380 'Get_Next_Approvers: Unable to get the next approvers from AME.',
381 itemtype,
382 itemkey);
383 end if;
384 return;
385 END;
386
387
388 --pp_debug('In Get next approvers g_next_approvers'||g_next_approvers.count);
389 --pp_debug('In Get next approvers l_completeYNO::'||l_completeYNO);
390 -- Check the number of next approvers. If the count is zero, then verify the approval process is completed or not.
391 if ( g_next_approvers.count > 0 ) then
392 resultout:=wf_engine.eng_completed || ':' ||'VALID APPROVER';
393 --pp_debug('In Get next approvers resultout'||resultout);
394 return;
395
396 else
397
398 -- 'X' is the code when there is no rule needed and applied.
399
400 if (l_completeYNO in ('X','Y')) then
401 resultout:=wf_engine.eng_completed || ':' ||'NO_NEXT_APPROVER';
402 --pp_debug('In Get next approvers resultout'||resultout);
403 return;
404 else
405 resultout:=wf_engine.eng_completed || ':' ||'';
406 --pp_debug('In Get next approvers resultout'||resultout);
407 return;
408 end if;
409
410 end if;
411 end if; -- run mode
412 EXCEPTION
413 WHEN OTHERS THEN
414
415 wf_core.CONTEXT (
416 itemtype,
417 'GET_NEXT_APPROVERS Exception Block',
418 itemtype,
419 itemkey);
420 resultout:=wf_engine.eng_completed || ':' ||'INVALID_APPROVER';
421 return;
422
423
424 end Get_Next_Approvers;
425
426
427 ----------------------------------------------------------------------------------
428 --Start of Comments
429 --Name: set_attributes
430 --Function:
431 -- Workflow activity PL/SQL handler
432 -- This procedure is used to set attributes for transaction id i.e. parent
433 --Parameters:
434 --IN:
435 -- Standard workflow IN parameters
436 --OUT:
437 -- Standard workflow OUT parameters
438 --
439 --End of Comments
440 -------------------------------------------------------------------------------
441 PROCEDURE set_attributes (
442 itemtype IN VARCHAR2,
443 itemkey IN VARCHAR2,
444 actid IN NUMBER,
445 funcmode IN VARCHAR2,
446 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
447 ) IS
448
449 l_approval_object VARCHAR2 (4000);
450 l_approval_id NUMBER;
451 l_org_id NUMBER;
452 l_ampsign VARCHAR2(1) := fnd_global.local_chr(38);
453 l_url VARCHAR2(32000);
454
455 -- bug 7657061
456 l_resp_appl_id NUMBER := 880;
457 l_resp_id NUMBER;
458
459 CURSOR OBJECT (
460 c_approval_id NUMBER
461 ) IS
462 SELECT quote_name, quote_number,org_id,quote_header_id
463 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
464 WHERE qha.quote_header_id = aoa.object_id
465 AND aoa.object_approval_id = c_approval_id;
466
467 BEGIN
468
469 --pp_debug('Entered Get attributes');
470 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
471 aso_debug_pub.ADD (
472 'Begin set attribute Process',
473 1,
474 'N'
475 );
476 END IF;
477
478 IF funcmode = 'RUN'
479 THEN
480 l_approval_id := wf_engine.getitemattrnumber (
481 itemtype,
482 itemkey,
483 'APPROVALID'
484 );
485
486 --pp_debug('Inside set attributes');
487
488 FOR i IN OBJECT (
489 l_approval_id
490 )
491 LOOP
492 l_approval_object := i.quote_name;
493 l_org_id := i.org_id;
494
495 --pp_debug('Inside set attributes lloop');
496
497 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
498 aso_debug_pub.ADD (
499 'Quote name :' || l_approval_object,
500 1,
501 'N'
502 );
503 END IF;
504 wf_engine.setitemattrtext (
505 itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'APPROVAL_OBJECT',
508 avalue => l_approval_object
509 );
510
511 wf_engine.setitemattrnumber (
512 itemtype => itemtype,
513 itemkey => itemkey,
514 aname => 'ORGID',
515 avalue => l_org_id
516 );
517
518 wf_engine.setitemattrnumber (
519 itemtype => itemtype,
520 itemkey => itemkey,
521 aname => 'QTEHDRID',
522 avalue => i.quote_header_id
523 );
524
525 wf_engine.setitemattrnumber (
526 itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'QTENUMBER',
529 avalue => i.quote_number
530 );
531
532
533 select nvl(fnd_profile.value('ASO_QUOTE_APPROVER_RESP'),-1) into l_resp_id from dual; -- bug 7657061
534
535 -- bug 5350149
536 l_url := aso_apr_wf_pvt.GetRunFuncURL(
537 p_function_name => 'QOT_OAUI_QUOTE_DETAILS',
538 p_resp_appl_id => l_resp_appl_id, -- bug 7657061
539 p_resp_id => l_resp_id, -- bug 7657061
540 p_parameters => l_ampsign||l_ampsign||'QotIntgEvtSrc=ApvlNotif'||l_ampsign||'QotIntgEvt=Event.QuoteDet'||l_ampsign||'QotIntgEvtVal='
541 ||i.quote_header_id||l_ampsign||'QotIntgEvtVal1='||i.quote_number||l_ampsign||'addBreadCrumb=Y') ;
542
543
544 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
545 aso_debug_pub.ADD ('RESP_APPL_ID: ' || l_resp_appl_id,1,'N'); -- bug 7657061
546 aso_debug_pub.ADD ('RESP_ID: ' || l_resp_id,1,'N'); -- bug 7657061
547 aso_debug_pub.ADD ('l_url: '||substr(l_url,1,240),1,'N');
548 END IF;
549
550
551 wf_engine.setitemattrtext (
552 itemtype => itemtype,
553 itemkey => itemkey,
554 aname => 'OAQTEDETAILLNK',
555 avalue => l_url
556 );
557
558 END LOOP;
559 resultout := wf_engine.eng_completed;
560 --pp_debug('End set attributes');
561 END IF;
562 EXCEPTION
563 WHEN OTHERS
564 THEN
565 --pp_debug('Inside exception set attributes');
566 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
567 aso_debug_pub.ADD (
568 'Exception in Set Attributes SqlCode :' || SQLERRM,
569 1,
570 'N'
571 );
572 END IF;
573 wf_core.CONTEXT (
574 itemtype,
575 'set_attributes',
576 itemtype,
577 itemkey
578 );
579 RAISE;
580
581 END set_Attributes;
582
583
584 --------------------------------------------------------------------------------
585 --Start of Comments
586 --Name: launch_parallel_approval
587 --Pre-reqs:
588 -- None.
589 --Modifies:
590 -- None.
591 --Locks:
592 -- None.
593 --Function:
594 -- Workflow activity PL/SQL handler.
595 -- + This procedure is used to send the notification for the approvers.
596 -- + Iterate through the list of approvers got from the API call
597 -- ame_api2.getNextApprovers4
598 -- + Get the next approver name from the global variable g_next_approvers
599 -- and for each retrieved approver separate workflow process is kicked.
600 -- + They are marked as child of the current approval process (workflow
601 -- master detail co-ordination).
602 -- + For example, if there are 3 approvers, then 3 child process will be
603 -- created and each of them will be notified at the same time.
604 --
605 --Parameters:
606 --IN:
607 -- Standard workflow IN parameters
608 --OUT:
609 -- Standard workflow OUT parameters
610 --Testing:
611 --
612 --End of Comments
613 -------------------------------------------------------------------------------
614 PROCEDURE LAUNCH_PARALLEL_APPROVAL (itemtype in varchar2,
615 itemkey in varchar2,
616 actid in number,
617 funcmode in varchar2,
618 resultout out NOCOPY varchar2) IS
619
620
621 l_orgid number;
622 l_approval_Detail_id number;
623 l_next_approver_id number;
624 l_next_fnd_usr_id number;
625 l_next_approver_name per_employees_current_x.full_name%TYPE;
626 l_next_approver_user_name VARCHAR2(100);
627 l_next_approver_disp_name VARCHAR2(240);
628 l_item_key VARCHAR2 (30);
629 l_approval_id NUMBER;
630 l_requestor_name VARCHAR2 (240);
631 l_requestor_displayname VARCHAR2 (240);
632 l_forward_user_name VARCHAR2 (240);
633 l_forward_displayname VARCHAR2 (240);
634 l_start_block_activity varchar2(1);
635 l_has_fyi_app varchar2(1);
636 l_approver_index NUMBER;
637 l_fnd_user_id number;
638
639 begin
640 IF (funcmode='RUN') THEN
641
642 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
643 aso_debug_pub.ADD (
644 'Begin Lauch Approval Process',
645 1,
646 'N'
647 );
648 END IF;
649
650
651
652 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval');
653
654
655 l_approval_id := wf_engine.getitemattrnumber (
656 itemtype,
657 itemkey,
658 'APPROVALID'
659 );
660 l_requestor_name := wf_engine.getitemattrtext (
661 itemtype,
662 itemkey,
663 'REQUESTOR_USERNAME'
664 );
665 l_requestor_displayname :=
666 wf_engine.getitemattrtext (
667 itemtype,
668 itemkey,
669 'REQUESTOR_DISPLAYNAME'
670 );
671
672 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval l_approval_id'||l_approval_id);
673 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval l_requestor_name'||l_requestor_name);
674 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval l_requestor_displayname'||l_requestor_displayname);
675
676
677 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
678 aso_debug_pub.ADD (
679 'Object Approval ID is ' || l_approval_id,
680 1,
681 'N'
682 );
683 aso_debug_pub.ADD (
684 'Requester UserName is ' || l_requestor_name,
685 1,
686 'N'
687 );
688 aso_debug_pub.ADD (
689 'Requester DisplayName is :' || l_requestor_displayname,
690 1,
691 'N'
692 );
693 END IF;
694
695 l_orgid := wf_engine.getitemattrnumber (
696 itemtype,
697 itemkey,
698 'ORGID'
699 );
700
701 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
702 aso_debug_pub.ADD (
703 'Org Id is ' || l_orgid,
704 1,
705 'N'
706 );
707 END IF;
708
709 l_start_block_activity := 'N';
710 l_has_fyi_app := 'N';
711 -- Iterate through the list of next approvers.
712 l_approver_index := g_next_approvers.first();
713 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval l_approver_index'||l_approver_index);
714 while ( l_approver_index is not null ) loop
715
716 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval approver name'||g_next_approvers(l_approver_index).name);
717 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
718 aso_debug_pub.ADD (
719 'Begin Launch_Parallel_Approval: 002 -- Next Approver :' || g_next_approvers(l_approver_index).name,
720 1,
721 'N'
722 );
723 END IF;
724
725
726
727 SELECT aso_apr_obj_det_s.NEXTVAL into l_approval_detail_id FROM sys.dual;
728 l_item_key:=to_char(l_approval_detail_id) ||'DET';
729
730 /* Need to test the following how to get the username of approver */
731 /* l_forward_user_name, l_forward_displayname */
732
733 if (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) then
734 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
735 l_next_fnd_usr_id:=null;
736 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.fndUserOrigSystem) then
737 l_next_fnd_usr_id:= g_next_approvers(l_approver_index).orig_system_id;
738 l_next_approver_id:=null;
739 WF_DIRECTORY.GetUserName(ame_util.fndUserOrigSystem, l_next_fnd_usr_id, l_next_approver_user_name, l_next_approver_disp_name);
740 elsif (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
741 l_next_approver_id := g_next_approvers(l_approver_index).orig_system_id;
742 l_next_fnd_usr_id:=null;
743
744 end if;
745
746
747 IF (g_next_approvers(l_approver_index).orig_system = ame_util.perOrigSystem) or (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) then
748 l_forward_user_name := g_next_approvers(l_approver_index).name;
749 l_forward_displayname:= g_next_approvers(l_approver_index).display_name;
750
751 ELSE
752
753 l_forward_user_name := l_next_approver_user_name;
754 l_forward_displayname := l_next_approver_disp_name;
755 END IF;
756
757 -- Create a child process for the retrieved approver.
758 wf_engine.CreateProcess( itemtype => itemtype,
759 itemkey => l_item_key,
760 process => 'PARALLELSUBPROCESS');
761
762
763 /* Need to set the parent child relationship between processes */
764 wf_engine.SetItemParent( itemtype => itemtype,
765 itemkey => l_item_key,
766 parent_itemtype => itemtype,
767 parent_itemkey => itemkey,
768 parent_context => NULL);
769
770 wf_engine.setitemuserkey (
771 itemtype => itemtype,
772 itemkey => l_item_key,
773 userkey => l_item_key
774 );
775 wf_engine.setitemattrnumber (
776 itemtype => itemtype,
777 itemkey => l_item_key,
778 aname => 'APPROVAL_DET_ID',
779 avalue => l_approval_detail_id
780 );
781 wf_engine.setitemattrtext (
782 itemtype => itemtype, --'ASOAPPRV',
783 itemkey => l_item_key,
784 aname => 'REQUESTOR_DISPLAYNAME',
785 avalue => l_requestor_displayname
786 );
787 wf_engine.setitemattrtext (
788 itemtype => itemtype,
789 itemkey => l_item_key,
790 aname => 'RECEIVER_USERNAME',
791 avalue => l_forward_user_name
792 );
793
794 wf_engine.setitemattrtext (
795 itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'NEWPROCESSFLAG',
798 avalue =>'Y'
799 );
800
801 l_forward_displayname :=
802 wf_directory.getroledisplayname (
803 l_forward_user_name
804 );
805 wf_engine.setitemattrtext (
806 itemtype => itemtype,
807 itemkey => l_item_key,
808 aname => 'RECEIVER_DISPLAYNAME',
809 avalue => l_forward_displayname
810 );
811 wf_engine.setitemattrtext (
812 itemtype => itemtype, --'ASOAPPRV',
813 itemkey => l_item_key,
814 aname => 'REQUESTOR_USERNAME',
815 avalue => l_requestor_name
816 );
817 wf_engine.setitemattrnumber (
818 itemtype => itemtype, --'ASOAPPRV',
819 itemkey => l_item_key,
820 aname => 'APPROVALID',
821 avalue => l_approval_id
822 );
823
824
825
826 if (g_next_approvers(l_approver_index).approver_category = ame_util.fyiApproverCategory) then
827 l_has_fyi_app := 'Y';
828 l_start_block_activity := 'N';
829 wf_engine.setitemattrtext (
830 itemtype => itemtype,
831 itemkey => l_item_key,
832 aname => 'IS_FYI_APPROVER',
833 avalue => 'Y'
834 );
835 else
836 wf_engine.setitemattrtext (
837 itemtype => itemtype,
838 itemkey => l_item_key,
839 aname => 'IS_FYI_APPROVER',
840 avalue => 'N'
841 );
842 if (l_has_fyi_app = 'N') then
843 -- only start BLOCK if there are no FYI approvers
844 l_start_block_activity := 'Y';
845 end if;
846
847 end if;
848
849 /*-----------------------------------------------------------------------------------
850 /* insert to approver record here since all information is available here
851 --------------------------------------------------------------------------------------- */
852
853 insertapprovershistory
854 (l_approval_det_id => l_approval_detail_id,
855 l_approval_id => l_approval_id,
856 l_approver_person_id => l_next_approver_id,
857 l_approver_user_id => l_next_fnd_usr_id,
858 l_order_number => g_next_approvers(l_approver_index).approver_order_number,
859 l_NAME => g_next_approvers(l_approver_index).name,
860 l_ITEM_CLASS => g_next_approvers(l_approver_index).item_class,
861 l_ITEM_ID => g_next_approvers(l_approver_index).item_id,
862 l_ACTION_TYPE_ID => g_next_approvers(l_approver_index).action_type_id, --ACTION_TYPE_ID
863 l_GROUP_OR_CHAIN_ID => g_next_approvers(l_approver_index).group_or_chain_id,
864 L_OCCURRENCE => g_next_approvers(l_approver_index).occurrence,
865 L_AUTHORITY => g_next_approvers(l_approver_index).authority ,
866 l_APPROVER_CATEGORY => g_next_approvers(l_approver_index).approver_category);
867
868
869 wf_engine.StartProcess( itemtype => itemtype,
870 itemkey => l_item_key );
871
872 l_approver_index := g_next_approvers.next(l_approver_index);
873
874
875
876 end loop; -- end of for loop.
877
878 if l_start_block_activity = 'Y' then
879 resultout:=wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
880 else
881 resultout:=wf_engine.eng_completed || ':' || '';
882 end if;
883 -- After routing is done, delete approver list
884 g_next_approvers.delete;
885
886 --pp_debug ('Begin Lauch Approval Process Launch_Parallel_Approval resultout'||resultout);
887
888 RETURN;
889
890 END IF; --run mode
891
892 exception
893 WHEN OTHERS
894 THEN
895 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
896 aso_debug_pub.ADD (
897 'Exception in Launch Parallel Approval Proc SqlCode :' || SQLERRM,
898 1,
899 'N'
900 );
901 END IF;
902 wf_core.CONTEXT (
903 itemtype,
904 'LAUNCH Parallel_Approvals Excpetion Block',
905 itemtype,
906 itemkey
907 );
908 end Launch_Parallel_Approval;
909
910
911
912 PROCEDURE set_notification_attributes (
913 itemtype IN VARCHAR2,
914 itemkey IN VARCHAR2,
915 actid IN NUMBER,
916 funcmode IN VARCHAR2,
917 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
918 ) as
919 begin
920
921 -- can add code in future if required. Leaving blank for now as we are already setting the attributes
922 resultout := 'COMPLETE:NULL';
923
924
925 end set_notification_attributes;
926
927 --------------------------------------------------------------------------------
928 --Start of Comments
929 --Name: is_fyi_approver
930 --Pre-reqs:
931 -- None.
932 --Modifies:
933 -- None.
934 --Locks:
935 -- None.
936 --Function:
937 -- Workflow activity PL/SQL handler.
938 -- This is uesd to determine whether approver is FYI approver or not.
939 -- Values can be Y or N.
940 --Parameters:
941 --IN:
942 -- Standard workflow IN parameters
943 --OUT:
944 -- Standard workflow OUT parameters
945 --Testing:
946 -- None
947 --End of Comments
948 ---------------------------------------------------------------------------------
949 PROCEDURE is_fyi_approver(
950 itemtype IN VARCHAR2,
951 itemkey IN VARCHAR2,
952 actid IN NUMBER,
953 funcmode IN VARCHAR2,
954 resultout OUT NOCOPY VARCHAR2)
955 IS
956 l_is_fyi_approver VARCHAR2(1);
957 BEGIN
958
959 IF (funcmode <> wf_engine.eng_run) THEN
960 resultout := wf_engine.eng_null;
961 RETURN;
962 END IF;
963
964
965 --Logic is check first for workflow attribute 'IS_FYI_APPROVER', then attribute 'APPROVER_CATEGORY'
966 --Fetch 'IS_FYI_APPROVER' value which is set in launch_parralel_approval
967 l_is_fyi_approver := wf_engine.getitemattrtext (
968 itemtype,
969 itemkey,
970 'IS_FYI_APPROVER'
971 );
972
973 IF l_is_fyi_approver = 'Y' THEN
974 resultout := wf_engine.eng_completed || ':' || 'Y';
975 ELSE
976 resultout := wf_engine.eng_completed || ':' || 'N';
977 END IF;
978
979 END is_fyi_approver;
980
981 --------------------------------------------------------------------------------
982 --Start of Comments
983 --Name: Update_Action_History_Approve
984 --Pre-reqs:
985 -- None.
986 --Modifies:
987 -- None.
988 --Locks:
989 -- None.
990 --Function:
991 -- Workflow activity PL/SQL handler.
992 -- This procedure updates the aso_apr_approval_details table based on the approvers response.
993 --Parameters:
994 --IN:
995 -- Standard workflow IN parameters
996 --OUT:
997 -- Standard workflow OUT parameters
998 --Testing:
999 --
1000 --End of Comments
1001 -------------------------------------------------------------------------------
1002 PROCEDURE update_Action_history_approve (
1003 itemtype IN VARCHAR2,
1004 itemkey IN VARCHAR2,
1005 actid IN NUMBER,
1006 funcmode IN VARCHAR2,
1007 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1008 ) IS
1009 l_approval_det_id NUMBER;
1010 l_note VARCHAR2 (4000);
1011 BEGIN
1012 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1013 aso_debug_pub.ADD (
1014 'Begin update_Action_history_approve Procedure ',
1015 1,
1016 'N'
1017 );
1018 END IF;
1019
1020 IF (funcmode <> wf_engine.eng_run) THEN
1021 resultout := wf_engine.eng_null;
1022 RETURN;
1023 END IF;
1024
1025 IF funcmode = 'RUN'
1026 THEN
1027
1028 l_approval_det_id := wf_engine.getitemattrnumber (
1029 itemtype,
1030 itemkey,
1031 'APPROVAL_DET_ID'
1032 );
1033 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1034 aso_debug_pub.ADD (
1035 'Approval detail ID is : ' || l_approval_det_id,
1036 1,
1037 'N'
1038 );
1039 END IF;
1040
1041 --pp_debug('update_Action_history_approve l_approval_det_id'||l_approval_det_id);
1042 l_note := wf_engine.getitemattrtext (
1043 itemtype,
1044 itemkey,
1045 'NOTE'
1046 );
1047 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1048 aso_debug_pub.ADD (
1049 'Approver comments are: ' || SUBSTR (
1050 l_note,
1051 1,
1052 32
1053 ),
1054 1,
1055 'N'
1056 );
1057 aso_debug_pub.ADD (
1058 'Calling the update table procedure setting approver status to APPR',
1059 1,
1060 'N'
1061 );
1062 END IF;
1063
1064 --pp_debug('update_Action_history_approve l_note'||l_note);
1065 update_approval_status (
1066 p_update_header_or_detail_flag => 'DETAIL' ,
1067 p_object_approval_id => null,
1068 p_approval_det_id =>l_approval_det_id,
1069 p_status => 'APPR',
1070 note => l_note);
1071
1072 wf_engine.setitemattrtext (
1073 itemtype => itemtype,
1074 itemkey => itemkey,
1075 aname => 'APPROVER_RESPONSE',
1076 avalue => 'APPROVED'
1077 );
1078
1079 resultout := wf_engine.eng_completed|| ':' ||'ACTIVITY_PERFORMED';
1080 --pp_debug('update_Action_history_approve resultout'||resultout);
1081 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1082 aso_debug_pub.ADD (
1083 'End of APPROVED Procedure',
1084 1,
1085 'N'
1086 );
1087 END IF;
1088 RETURN;
1089 END IF;
1090
1091
1092 EXCEPTION
1093 WHEN OTHERS
1094 THEN
1095 --pp_debug('Exception in update_Action_history_approve ');
1096 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1097 aso_debug_pub.ADD (
1098 'Exception in update_Action_history_approve Procedure SqlCode :' || SQLERRM,
1099 1,
1100 'N'
1101 );
1102 END IF;
1103 wf_core.CONTEXT (
1104 itemtype,
1105 'APPROVED',
1106 itemtype,
1107 itemkey,
1108 TO_CHAR (
1109 actid
1110 ),
1111 funcmode
1112 );
1113 RAISE;
1114 END update_Action_history_approve;
1115
1116 PROCEDURE update_Action_history_reject (
1117 itemtype IN VARCHAR2,
1118 itemkey IN VARCHAR2,
1119 actid IN NUMBER,
1120 funcmode IN VARCHAR2,
1121 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1122 ) IS
1123 l_approval_det_id NUMBER;
1124 l_note VARCHAR2 (4000);
1125 BEGIN
1126 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1127 aso_debug_pub.ADD (
1128 'Start of update_Action_history_REJECTED Procedure',
1129 1,
1130 'N'
1131 );
1132 END IF;
1133
1134 IF (funcmode <> wf_engine.eng_run) THEN
1135 resultout := wf_engine.eng_null;
1136 RETURN;
1137 END IF;
1138
1139 IF funcmode = 'RUN'
1140 THEN
1141 /* IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1142 aso_debug_pub.ADD (
1143 'Seting message name to OA_REQUEST_REJECTED_FYI',
1144 1,
1145 'N'
1146 );
1147 END IF;
1148 wf_engine.setitemattrtext (
1149 itemtype => itemtype,
1150 itemkey => itemkey,
1151 aname => 'MESSAGE',
1152 avalue => 'OA_REQUEST_REJECTED_FYI'
1153 );
1154 */
1155 l_approval_det_id := wf_engine.getitemattrnumber (
1156 itemtype,
1157 itemkey,
1158 'APPROVAL_DET_ID'
1159 );
1160 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1161 aso_debug_pub.ADD (
1162 'Approval detail ID is : ' || l_approval_det_id,
1163 1,
1164 'N'
1165 );
1166 END IF;
1167 l_note := wf_engine.getitemattrtext (
1168 itemtype,
1169 itemkey,
1170 'NOTE'
1171 );
1172 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1173 aso_debug_pub.ADD (
1174 'Approver comments are: ' || SUBSTR (
1175 l_note,
1176 1,
1177 32
1178 ),
1179 1,
1180 'N'
1181 );
1182 aso_debug_pub.ADD (
1183 'Calling the update table procedure setting approver status to REJ',
1184 1,
1185 'N'
1186 );
1187 END IF;
1188 update_approval_status (
1189 p_update_header_or_detail_flag => 'DETAIL' ,
1190 p_object_approval_id => null,
1191 p_approval_det_id =>l_approval_det_id,
1192 p_status => 'REJ',
1193 note => l_note);
1194
1195
1196 wf_engine.setitemattrtext (
1197 itemtype => itemtype,
1198 itemkey => itemkey,
1199 aname => 'APPROVER_RESPONSE',
1200 avalue => 'REJECTED'
1201 );
1202
1203
1204
1205
1206 resultout := wf_engine.eng_completed|| ':' || 'ACTIVITY_PERFORMED';
1207 RETURN;
1208 END IF;
1209
1210
1211 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1212 aso_debug_pub.ADD (
1213 'End of update_Action_history_rejected Procedure',
1214 1,
1215 'N'
1216 );
1217 END IF;
1218 RETURN;
1219 EXCEPTION
1220 WHEN OTHERS
1221 THEN
1222 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1223 aso_debug_pub.ADD (
1224 'Exception in Rejected Procedure SqlCode :' || SQLERRM,
1225 1,
1226 'N'
1227 );
1228 END IF;
1229 wf_core.CONTEXT (
1230 itemtype,
1231 'REJECTED',
1232 itemtype,
1233 itemkey,
1234 TO_CHAR (
1235 actid
1236 ),
1237 funcmode
1238 );
1239 RAISE;
1240 END update_action_history_reject;
1241
1242 PROCEDURE update_action_history_timedout (
1243 itemtype IN VARCHAR2,
1244 itemkey IN VARCHAR2,
1245 actid IN NUMBER,
1246 funcmode IN VARCHAR2,
1247 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1248 ) IS
1249 l_approval_det_id NUMBER;
1250 l_note VARCHAR2 (4000);
1251 l_notification_id NUMBER;
1252 BEGIN
1253 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1254 aso_debug_pub.ADD (
1255 'begin update_action_history_timedout Procedure',
1256 1,
1257 'N'
1258 );
1259 END IF;
1260 IF (funcmode <> wf_engine.eng_run) THEN
1261 resultout := wf_engine.eng_null;
1262 RETURN;
1263 END IF;
1264
1265 IF funcmode = 'RUN'
1266 THEN
1267 /* l_notification_id := wf_engine.getitemattrnumber ( -- bug 16764591
1268 itemtype,
1269 itemkey,
1270 'NOTIFICATION_ID'
1271 );
1272 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1273 aso_debug_pub.ADD (
1274 'Cancelling notification for notification ID : ' || l_notification_id,
1275 1,
1276 'N'
1277 );
1278 END IF;
1279 wf_notification.CANCEL (
1280 nid => l_notification_id,
1281 cancel_comment => 'TIMEOUT'
1282 );
1283 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1284 aso_debug_pub.ADD (
1285 'Seting message name to OA_REQUEST_TIMEDOUT_FYI',
1286 1,
1287 'N'
1288 );
1289 END IF;
1290 wf_engine.setitemattrtext (
1291 itemtype => itemtype,
1292 itemkey => itemkey,
1293 aname => 'MESSAGE',
1294 avalue => 'OA_REQUEST_TIMEDOUT_FYI'
1295 );*/
1296 l_approval_det_id := wf_engine.getitemattrnumber (
1297 itemtype,
1298 itemkey,
1299 'APPROVAL_DET_ID'
1300 );
1301 l_note := wf_engine.getitemattrtext (
1302 itemtype,
1303 itemkey,
1304 'NOTE'
1305 );
1306 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1307 aso_debug_pub.ADD (
1308 'Approver comments are: ' || SUBSTR (
1309 l_note,
1310 1,
1311 32
1312 ),
1313 1,
1314 'N'
1315 );
1316 aso_debug_pub.ADD (
1317 'Calling the update table procedure setting approver status to TOUT',
1318 1,
1319 'N'
1320 );
1321 END IF;
1322 update_approval_status (
1323 p_update_header_or_detail_flag => 'DETAIL' ,
1324 p_object_approval_id => null,
1325 p_approval_det_id =>l_approval_det_id,
1326 p_status => 'TOUT',
1327 note => l_note);
1328
1329 resultout := wf_engine.eng_completed|| ':' ||'ACTIVITY_PERFORMED'; -- bug 16764591
1330 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1331 aso_debug_pub.ADD (
1332 'End of TIMEDOUT Procedure',
1333 1,
1334 'N'
1335 );
1336 END IF;
1337 RETURN;
1338 END IF;
1339
1340 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1341 aso_debug_pub.ADD (
1342 'End of update_action_history_timedout Procedure',
1343 1,
1344 'N'
1345 );
1346 END IF;
1347 RETURN;
1348 EXCEPTION
1349 WHEN OTHERS
1350 THEN
1351 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1352 aso_debug_pub.ADD (
1353 'Exception in Approved Procedure SqlCode :' || SQLERRM,
1354 1,
1355 'N'
1356 );
1357 END IF;
1358 wf_core.CONTEXT (
1359 itemtype,
1360 'TIMEDOUT',
1361 itemtype,
1362 itemkey,
1363 TO_CHAR (
1364 actid
1365 ),
1366 funcmode
1367 );
1368 RAISE;
1369 END update_action_history_timedout;
1370
1371 --------------------------------------------------------------------------------
1372 --Start of Comments
1373 --Name: Process_Response_AME
1374 --Pre-reqs:
1375 -- None.
1376 --Modifies:
1377 -- None.
1378 --Locks:
1379 -- None.
1380 --Function:
1381 -- Workflow activity PL/SQL handler. This procedure is used to inform AME about the approvers response.
1382 --Parameters:
1383 --IN:
1384 -- Standard workflow IN parameters
1385 --OUT:
1386 -- Standard workflow OUT parameters
1387 --Testing:
1388 --
1389 --End of Comments
1390 -------------------------------------------------------------------------------
1391
1392 PROCEDURE process_response_ame(itemtype in varchar2,
1393 itemkey in varchar2,
1394 p_response in varchar2) IS
1395
1396 l_current_approver ame_util.approverRecord2;
1397 l_approval_det_id number;
1398 l_obj_approval_id number;
1399 l_object_id NUMBER;
1400 l_object_type VARCHAR2 (240);
1401 l_application_id NUMBER;
1402
1403 cursor get_approver_details(l_approval_det_id number)
1404 is
1405 select name,item_class, item_id,action_type_id,group_or_chain_id,OCCURRENCE
1406 from aso_apr_approval_details
1407 where APPROVAL_DET_ID =l_approval_det_id;
1408
1409 CURSOR get_application_id (
1410 c_object_approval_id NUMBER
1411 ) IS
1412 SELECT DISTINCT object_id, object_type, application_id
1413 FROM aso_apr_obj_approvals aoa
1414 WHERE object_approval_id = c_object_approval_id;
1415
1416 begin
1417
1418 if( p_response = 'APPROVE') then
1419 l_current_approver.approval_status := ame_util.approvedStatus;
1420 elsif( p_response = 'REJECT') then
1421 l_current_approver.approval_status := ame_util.rejectStatus;
1422 elsif( p_response = 'TIMEOUT') then
1423 l_current_approver.approval_status := ame_util.noResponseStatus;
1424 end if;
1425
1426 l_approval_det_id := wf_engine.getitemattrnumber (
1427 itemtype,
1428 itemkey,
1429 'APPROVAL_DET_ID'
1430 );
1431
1432 l_obj_approval_id := wf_engine.getitemattrnumber (
1433 itemtype => itemtype,
1434 itemkey => itemkey,
1435 aname => 'APPROVALID'
1436 );
1437
1438 open get_approver_details(l_approval_det_id);
1439 fetch get_approver_details into l_current_approver.name,l_current_approver.item_class,
1440 l_current_approver.item_id, l_current_approver.action_type_id, l_current_approver.group_or_chain_id, l_current_approver.occurrence;
1441 close get_approver_details;
1442
1443 open get_application_id(l_obj_approval_id);
1444 fetch get_application_id into l_object_id, l_object_type, l_application_id;
1445 close get_application_id;
1446
1447
1448
1449 -- Update the Approval status with the response from the approver.
1450 ame_api2.updateApprovalStatus2(
1451 applicationIdIn=>l_application_id,
1452 transactionIdIn=>l_object_id,
1453 transactionTypeIn=>l_object_type,
1454 approvalStatusIn => l_current_approver.approval_status,
1455 approverNameIn=>l_current_approver.name,
1456 itemClassIn=> l_current_approver.item_class,
1457 itemIdIn=> l_current_approver.item_id,
1458 actionTypeIdIn=> l_current_approver.action_type_id,
1459 groupOrChainIdIn=> l_current_approver.group_or_chain_id,
1460 occurrenceIn=> l_current_approver.occurrence
1461 );
1462
1463 exception
1464 when others then
1465 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1466 aso_debug_pub.ADD (' Exception in AME when updating approval status'||itemtype||' ,key'||itemkey, 1, 'N' );
1467 end if;
1468 null;
1469 /* wf_engine.setitemattrtext (itemtype,
1470 itemkey,
1471 'AME_EXCEPTION',
1472 'Y'
1473 );*/
1474 --pp_debug('Exception in AME when updating approval status');
1475
1476
1477 END PROCESS_RESPONSE_AME;
1478
1479
1480 --------------------------------------------------------------------------------
1481 --Name: Process_Beat_By_First
1482 --Pre-reqs:
1483 -- None.
1484 --Modifies:
1485 -- None.
1486 --Locks:
1487 -- None.
1488 --Function:
1489 -- Workflow activity PL/SQL handler.
1490 -- This procedure handles the stopping of workflow and the updating of the
1491 -- action history table in the case of approvers being beat by first
1492 -- responder.
1493 --Parameters:
1494 --IN:
1495 -- Standard workflow IN parameters
1496 --OUT:
1497 -- Standard workflow OUT parameters
1498 --Testing:
1499 --
1500 --End of Comments
1501 -------------------------------------------------------------------------------
1502 PROCEDURE PROCESS_BEAT_BY_FIRST( itemtype in varchar2,
1503 itemkey in varchar2,
1504 actid in number,
1505 funcmode in varchar2,
1506 resultout out NOCOPY varchar2) AS
1507
1508
1509 l_parent_item_type wf_items.parent_item_type%TYPE;
1510 l_parent_item_key wf_items.parent_item_key%TYPE;
1511 l_child_approver_empid NUMBER;
1512 l_child_approver_groupid NUMBER;
1513 l_header_id NUMBER;
1514 l_process_out VARCHAR2 (10);
1515 approverlist ame_util.approverstable2;
1516 ametransactiontype po_document_types.ame_transaction_type%TYPE;
1517 l_response_action VARCHAR2 (20);
1518 l_note VARCHAR2 (4000);
1519 l_person_id NUMBER;
1520 l_orig_system VARCHAR2 (30);
1521 l_orig_system_id NUMBER;
1522 l_approver_response VARCHAR2 (20);
1523
1524 CURSOR l_child_wf (
1525 p_itemtype IN wf_items.parent_item_type%TYPE,
1526 p_itemkey IN wf_items.parent_item_key%TYPE
1527 )
1528 IS
1529 SELECT wfi.item_type, wfi.item_key
1530 FROM wf_items wfi, wf_item_activity_statuses wfias
1531 WHERE wfi.parent_item_key = p_itemkey
1532 AND wfi.item_type = p_itemtype
1533 AND wfi.parent_item_type = p_itemtype
1534 AND wfias.item_type = wfi.item_type
1535 AND wfias.item_key = wfi.item_key
1536 AND wfias.activity_status = 'NOTIFIED'
1537 AND wfias.notification_id IS NOT NULL
1538 AND wfi.item_key <> itemkey;
1539
1540 l_child_wf_cur l_child_wf%ROWTYPE;
1541 l_current_person_id NUMBER;
1542 l_current_appr_group_id NUMBER;
1543 l_abort NUMBER;
1544 l_current_username wf_users.name%TYPE;
1545 tempOrigSystem ame_util.stringType;
1546 l_approval_det_id number;
1547 l_child_username wf_users.name%TYPE;
1548 l_fnd_usr_id number;
1549
1550 BEGIN
1551
1552 IF (funcmode <> wf_engine.eng_run) THEN
1553 --pp_debug('Entered Beat not run');
1554 resultout := wf_engine.eng_null;
1555 RETURN;
1556 END IF;
1557
1558 IF funcmode = 'RUN' THEN
1559 --pp_debug('Entered Beat by First Responder');
1560 SELECT parent_item_type, parent_item_key
1561 INTO l_parent_item_type, l_parent_item_key
1562 FROM wf_items
1563 WHERE item_type = itemtype AND item_key = itemkey;
1564
1565 --pp_debug('In Beat by First Responder parent_item_type'||l_parent_item_type);
1566 --pp_debug('In Beat by First Responder l_parent_item_key'||l_parent_item_key);
1567
1568
1569 l_current_username:=wf_engine.getitemattrtext (
1570 itemtype,
1571 itemkey,
1572 'RECEIVER_USERNAME');
1573 --pp_debug('In beat by first resp l_current_username'||l_current_username);
1574 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => l_current_username,
1575 origSystemOut => tempOrigSystem,
1576 origSystemIdOut => l_current_person_id);
1577 --pp_debug('In beat by first resp tempOrigSystem'||tempOrigSystem);
1578 --pp_debug('In beat by first resp l_current_person_id'||l_current_person_id);
1579
1580
1581 l_header_id :=wf_engine.getitemattrnumber (itemtype => l_parent_item_type,
1582 itemkey => l_parent_item_key,
1583 aname => 'QTEHDRID'
1584 );
1585
1586 --pp_debug('In beat by first resp l_header_id'||l_header_id);
1587
1588
1589 l_approver_response :=
1590 wf_engine.getitemattrtext (itemtype => itemtype,
1591 itemkey => itemkey,
1592 aname => 'APPROVER_RESPONSE'
1593 );
1594 --pp_debug('In beat by first resp l_approver_response'||l_approver_response);
1595
1596 IF l_approver_response = 'APPROVED'
1597 THEN
1598 -- BEGIN
1599 ame_api2.getallapprovers7
1600 (applicationidin => 880,
1601 transactionidin => l_header_id,
1602 transactiontypein => 'Quote',
1603 approvalprocesscompleteynout => l_process_out,
1604 approversout => approverlist
1605 );
1606
1607 -- Once we get the approvers list from AME, we iterate through the approvers list,
1608 -- to find out the current first authority approver.
1609 FOR i IN 1 .. approverlist.COUNT
1610 LOOP
1611 IF approverlist (i).orig_system_id = l_current_person_id
1612 THEN
1613 l_current_appr_group_id := approverlist (i).group_or_chain_id;
1614 END IF;
1615 END LOOP;
1616
1617 --pp_debug('In beat by first resp l_current_appr_group_id'||l_current_appr_group_id);
1618 FOR i IN 1 .. approverlist.COUNT
1619 LOOP
1620
1621
1622 --pp_debug('In beat by first resp approval_status'||approverlist (i).approval_status);
1623 --pp_debug('In beat by first resp api_insertion'||approverlist (i).api_insertion);
1624 --pp_debug('In beat by first resp group_or_chain_id'||approverlist (i).group_or_chain_id);
1625
1626 IF ( approverlist (i).approval_status = ame_util.beatbyfirstresponderstatus
1627 AND approverlist (i).api_insertion = ame_util.oamgenerated
1628 AND approverlist (i).group_or_chain_id =l_current_appr_group_id)
1629 THEN
1630 -- pp_debug('In beat by first resp group_or_chain_id'||approverlist (i).group_or_chain_id);
1631 l_orig_system := approverlist (i).orig_system;
1632 l_orig_system_id := approverlist (i).orig_system_id;
1633
1634 IF (l_orig_system = ame_util.perorigsystem)
1635 THEN
1636 -- Employee Supervisor Record.
1637 l_person_id := l_orig_system_id;
1638
1639 ELSIF (l_orig_system = ame_util.posorigsystem)
1640 THEN
1641 l_person_id := l_orig_system_id;
1642
1643 ELSIF (l_orig_system = ame_util.fnduserorigsystem)
1644 THEN
1645 -- FND User Record.
1646 l_fnd_usr_id:=l_orig_system_id;
1647
1648 END IF;
1649
1650 --pp_debug ('Stop workflow child username'||nvl(l_person_id,l_fnd_usr_id));
1651 --pp_debug ('Stop workflow l_parent_item_type'||l_parent_item_type);
1652 --pp_debug ('Stop workflow child l_parent_item_key'||l_parent_item_key);
1653
1654 OPEN l_child_wf (l_parent_item_type, l_parent_item_key);
1655
1656 LOOP
1657 FETCH l_child_wf
1658 INTO l_child_wf_cur;
1659 EXIT WHEN l_child_wf%NOTFOUND;
1660
1661 --pp_debug ('Inside child workflows');
1662
1663
1664 BEGIN
1665
1666 -- pp_debug ('before aborting the flow for l_child_username:'|| l_child_wf_cur.item_type);
1667
1668 l_approval_det_id:=wf_engine.getitemattrnumber (
1669 itemtype=> l_child_wf_cur.item_type,
1670 itemkey=> l_child_wf_cur.item_key,
1671 aname=> 'APPROVAL_DET_ID');
1672
1673 select name into l_child_username
1674 from aso_apr_approval_details
1675 where approval_det_id=l_approval_det_id;
1676
1677 --pp_debug ('before aborting the flow for l_child_username:'|| l_child_username);
1678
1679 SELECT count(*)
1680 INTO l_abort
1681 FROM wf_item_attribute_values
1682 WHERE item_type = l_child_wf_cur.item_type
1683 AND NAME = 'RECEIVER_USERNAME'
1684 AND text_value = l_child_username
1685 AND item_key = l_child_wf_cur.item_key;
1686
1687
1688 --pp_debug ('before aborting the flow for11:'|| l_child_wf_cur.item_key||l_abort);
1689 l_note := null;--fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_APPROVED');
1690
1691 wf_engine.abortprocess (l_child_wf_cur.item_type,
1692 l_child_wf_cur.item_key
1693 );
1694 -- updating ASO action history
1695 aso_pa_apr_wf_pvt.update_approval_status (
1696 p_update_header_or_detail_flag => 'DETAIL' ,
1697 p_object_approval_id => null,
1698 p_approval_det_id =>l_approval_det_id,
1699 p_status => 'NR',
1700 note => l_note);
1701
1702 EXCEPTION
1703 WHEN NO_DATA_FOUND
1704 THEN
1705 null;
1706 --pp_debug('this might be a parallel flow from dual chain of authority.do no abort');
1707
1708 END;
1709 END LOOP;
1710
1711 CLOSE l_child_wf;
1712 END IF;
1713 END LOOP;
1714 ELSIF (l_approver_response = 'REJECTED')
1715 THEN
1716 OPEN l_child_wf (l_parent_item_type, l_parent_item_key);
1717
1718 LOOP
1719 FETCH l_child_wf
1720 INTO l_child_wf_cur;
1721
1722 EXIT WHEN l_child_wf%NOTFOUND;
1723 l_note := null; --fnd_message.get_string('ICX', 'ICX_POR_REQ_ALREADY_REJECTED');
1724 l_approval_det_id:=wf_engine.getitemattrnumber (
1725 itemtype=> l_child_wf_cur.item_type,
1726 itemkey=> l_child_wf_cur.item_key,
1727 aname=> 'APPROVAL_DET_ID');
1728
1729 wf_engine.abortprocess (l_child_wf_cur.item_type,
1730 l_child_wf_cur.item_key
1731 );
1732 -- updating ASO action history
1733 aso_pa_apr_wf_pvt.update_approval_status (
1734 p_update_header_or_detail_flag => 'DETAIL' ,
1735 p_object_approval_id => null,
1736 p_approval_det_id =>l_approval_det_id,
1737 p_status => 'NR',
1738 note => l_note);
1739
1740 END LOOP;
1741
1742 CLOSE l_child_wf;
1743 END IF;
1744
1745
1746
1747
1748 wf_engine.CompleteActivity( itemtype => l_parent_item_type,
1749 itemkey => l_parent_item_key,
1750 activity => 'BLOCK',
1751 result => null);
1752
1753 --pp_debug('END beat by first resultout');
1754 resultout := wf_engine.eng_completed|| ':' || 'ACTIVITY_PERFORMED';
1755
1756 RETURN;
1757 end if;
1758 EXCEPTION
1759 WHEN OTHERS THEN
1760
1761 --pp_debug('Inside beat by first exception others');
1762 wf_core.CONTEXT ('ASO_APR_PA_WF_PVT',
1763 'process_beat_by_first',
1764 itemtype,
1765 itemkey,
1766 TO_CHAR (actid),
1767 funcmode,
1768 SQLERRM);
1769 RAISE;
1770 end PROCESS_BEAT_BY_FIRST;
1771
1772 --------------------------------------------------------------------------------
1773 --Start of Comments
1774 --Name: process_response_approve
1775 --Pre-reqs:
1776 -- None.
1777 --Modifies:
1778 -- None.
1779 --Locks:
1780 -- None.
1781 --Function:
1782 -- Workflow activity PL/SQL handler.
1783 -- This procedure is the wrapper procedure of process_response_ame()
1784 --Parameters:
1785 --IN:
1786 -- Standard workflow IN parameters
1787 --OUT:
1788 -- Standard workflow OUT parameters
1789 --Testing:
1790 --
1791 --End of Comments
1792 -------------------------------------------------------------------------------
1793 PROCEDURE process_response_approve(
1794 itemtype IN VARCHAR2,
1795 itemkey IN VARCHAR2,
1796 actid IN NUMBER,
1797 funcmode IN VARCHAR2,
1798 resultout OUT NOCOPY VARCHAR2)
1799 IS
1800
1801 l_parent_item_type wf_items.parent_item_type%TYPE;
1802 l_parent_item_key wf_items.parent_item_key%TYPE;
1803 l_child_approver_empid NUMBER;
1804 l_child_approver_user_name wf_users.name%TYPE;
1805 l_child_approver_display_name wf_users.display_name%TYPE;
1806 l_api_name VARCHAR2(500) := 'process_response_approve';
1807
1808
1809 BEGIN
1810
1811 IF (funcmode <> wf_engine.eng_run) THEN
1812 resultout := wf_engine.eng_null;
1813 RETURN;
1814 END IF;
1815 --pp_debug('Process response approve AME itemkey'||itemkey);
1816 process_response_ame(itemtype, itemkey, 'APPROVE');
1817 -- Logic :
1818 -- + Get parent_itemp_type and parent_item_key.
1819 -- + Call process_response_internal with action 'APPROVE'. It will update ame with status and forwadee record.
1820 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to APPROVE' of parent.
1821 -- + Fetch required current approver related workflow attributes as child attributes.
1822 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
1823 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
1824
1825 SELECT parent_item_type, parent_item_key
1826 INTO l_parent_item_type, l_parent_item_key
1827 FROM wf_items
1828 WHERE item_type = itemtype
1829 AND item_key = itemkey;
1830
1831 --pp_debug('Process response approve AME itemkey'||l_parent_item_key);
1832
1833 l_child_approver_user_name := wf_engine.getitemattrtext (
1834 itemtype,
1835 itemkey,
1836 'RECEIVER_USERNAME');
1837
1838 l_child_approver_display_name := wf_engine.getitemattrtext (
1839 itemtype,
1840 itemkey,
1841 'RECEIVER_DISPLAYNAME');
1842
1843 wf_engine.setitemattrtext (
1844 itemtype => l_parent_item_type,
1845 itemkey => l_parent_item_key,
1846 aname => 'AME_SUB_APPROVAL_RESPONSE',
1847 avalue => 'APPROVE'
1848 );
1849
1850 wf_engine.setitemattrtext (
1851 itemtype => l_parent_item_type,
1852 itemkey => l_parent_item_key,
1853 aname => 'RECEIVER_USERNAME',
1854 avalue =>l_child_approver_user_name
1855 );
1856
1857 wf_engine.setitemattrtext (
1858 itemtype => l_parent_item_type,
1859 itemkey => l_parent_item_key,
1860 aname => 'RECEIVER_DISPLAYNAME',
1861 avalue => l_child_approver_display_name
1862 );
1863
1864
1865 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
1866 --pp_debug('process response Approve'||resultout);
1867 RETURN;
1868 exception
1869 WHEN OTHERS
1870 THEN
1871 --pp_debug('Exception in process response Approve');
1872 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1873 aso_debug_pub.ADD (
1874 'Exception in process_response_Approve Procedure SqlCode :' || SQLERRM,
1875 1,
1876 'N'
1877 );
1878 END IF;
1879 wf_core.CONTEXT (
1880 itemtype,
1881 'PROCESS_RESPONSE_APPROVE',
1882 itemtype,
1883 itemkey,
1884 TO_CHAR (
1885 actid
1886 ),
1887 funcmode
1888 );
1889 RAISE;
1890 END Process_Response_Approve;
1891
1892 --------------------------------------------------------------------------------
1893 --Start of Comments
1894 --Name: process_response_reject
1895 --Pre-reqs:
1896 -- None.
1897 --Modifies:
1898 -- None.
1899 --Locks:
1900 -- None.
1901 --Function:
1902 -- Workflow activity PL/SQL handler.
1903 -- This procedure is the wrapper procedure of process_response_ame()
1904 --Parameters:
1905 --IN:
1906 -- Standard workflow IN parameters
1907 --OUT:
1908 -- Standard workflow OUT parameters
1909 --Testing:
1910 --
1911 --End of Comments
1912 -------------------------------------------------------------------------------
1913 PROCEDURE process_response_reject(
1914 itemtype IN VARCHAR2,
1915 itemkey IN VARCHAR2,
1916 actid IN NUMBER,
1917 funcmode IN VARCHAR2,
1918 resultout OUT NOCOPY VARCHAR2)
1919 IS
1920
1921 l_parent_item_type wf_items.parent_item_type%TYPE;
1922 l_parent_item_key wf_items.parent_item_key%TYPE;
1923 l_child_approver_empid NUMBER;
1924 l_child_approver_user_name wf_users.name%TYPE;
1925 l_child_approver_display_name wf_users.display_name%TYPE;
1926 l_api_name VARCHAR2(500) := 'process_response_reject';
1927
1928
1929 BEGIN
1930
1931 IF (funcmode <> wf_engine.eng_run) THEN
1932 resultout := wf_engine.eng_null;
1933 RETURN;
1934 END IF;
1935
1936
1937 -- Logic :
1938 -- + Get parent_itemp_type and parent_item_key.
1939 -- + Call process_response_ame with action 'REJECT'. It will update ame with status and forwadee record.
1940 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to REJECT' of parent.
1941 -- + Fetch required current approver related workflow attributes as child attributes.
1942 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
1943 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
1944
1945 SELECT parent_item_type, parent_item_key
1946 INTO l_parent_item_type, l_parent_item_key
1947 FROM wf_items
1948 WHERE item_type = itemtype
1949 AND item_key = itemkey;
1950
1951 process_response_ame(itemtype, itemkey, 'REJECT');
1952
1953 l_child_approver_user_name := wf_engine.getitemattrtext (
1954 itemtype,
1955 itemkey,
1956 'RECEIVER_USERNAME');
1957
1958 l_child_approver_display_name := wf_engine.getitemattrtext (
1959 itemtype,
1960 itemkey,
1961 'RECEIVER_DISPLAYNAME');
1962
1963 wf_engine.setitemattrtext (
1964 itemtype => l_parent_item_type,
1965 itemkey => l_parent_item_key,
1966 aname => 'AME_SUB_APPROVAL_RESPONSE',
1967 avalue => 'REJECT'
1968 );
1969
1970 wf_engine.setitemattrtext (
1971 itemtype => l_parent_item_type,
1972 itemkey => l_parent_item_key,
1973 aname => 'RECEIVER_USERNAME',
1974 avalue =>l_child_approver_user_name
1975 );
1976
1977 wf_engine.setitemattrtext (
1978 itemtype => l_parent_item_type,
1979 itemkey => l_parent_item_key,
1980 aname => 'RECEIVER_DISPLAYNAME',
1981 avalue => l_child_approver_display_name
1982 );
1983
1984
1985 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
1986 RETURN;
1987 exception
1988 WHEN OTHERS
1989 THEN
1990 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1991 aso_debug_pub.ADD (
1992 'Exception in PROCESS_RESPONSE_REJECT Procedure SqlCode :' || SQLERRM,
1993 1,
1994 'N'
1995 );
1996 END IF;
1997 wf_core.CONTEXT (
1998 itemtype,
1999 'PROCESS_RESPONSE_REJECT',
2000 itemtype,
2001 itemkey,
2002 TO_CHAR (
2003 actid
2004 ),
2005 funcmode
2006 );
2007 RAISE;
2008 END Process_Response_Reject;
2009
2010 --------------------------------------------------------------------------------
2011 --Start of Comments
2012 --Name: Process_Response_timedout
2013 --Pre-reqs:
2014 -- None.
2015 --Modifies:
2016 -- None.
2017 --Locks:
2018 -- None.
2019 --Function:
2020 -- Workflow activity PL/SQL handler.
2021 -- This procedure is the wrapper procedure of process_response_ame()
2022 --Parameters:
2023 --IN:
2024 -- Standard workflow IN parameters
2025 --OUT:
2026 -- Standard workflow OUT parameters
2027 --Testing:
2028 --
2029 --End of Comments
2030 -------------------------------------------------------------------------------
2031 PROCEDURE Process_Response_timedout(
2032 itemtype IN VARCHAR2,
2033 itemkey IN VARCHAR2,
2034 actid IN NUMBER,
2035 funcmode IN VARCHAR2,
2036 resultout OUT NOCOPY VARCHAR2)
2037 IS
2038
2039 l_parent_item_type wf_items.parent_item_type%TYPE;
2040 l_parent_item_key wf_items.parent_item_key%TYPE;
2041 l_child_approver_empid NUMBER;
2042 l_child_approver_user_name wf_users.name%TYPE;
2043 l_child_approver_display_name wf_users.display_name%TYPE;
2044 l_api_name VARCHAR2(500) := 'Process_Response_timedout';
2045
2046
2047 BEGIN
2048
2049 IF (funcmode <> wf_engine.eng_run) THEN
2050 resultout := wf_engine.eng_null;
2051 RETURN;
2052 END IF;
2053
2054
2055 -- Logic :
2056 -- + Get parent_itemp_type and parent_item_key.
2057 -- + Call process_response_ame with action 'TIMEOUT'. It will update ame with status and forwadee record.
2058 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to TIMEOUT' of parent.
2059 -- + Fetch required current approver related workflow attributes as child attributes.
2060 -- + Populate parent workflow attributes APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2061
2062 SELECT parent_item_type, parent_item_key
2063 INTO l_parent_item_type, l_parent_item_key
2064 FROM wf_items
2065 WHERE item_type = itemtype
2066 AND item_key = itemkey;
2067
2068 process_response_ame(itemtype, itemkey, 'TIMEOUT');
2069
2070 l_child_approver_user_name := wf_engine.getitemattrtext (
2071 itemtype,
2072 itemkey,
2073 'RECEIVER_USERNAME');
2074
2075 l_child_approver_display_name := wf_engine.getitemattrtext (
2076 itemtype,
2077 itemkey,
2078 'RECEIVER_DISPLAYNAME');
2079
2080 wf_engine.setitemattrtext (
2081 itemtype => l_parent_item_type,
2082 itemkey => l_parent_item_key,
2083 aname => 'AME_SUB_APPROVAL_RESPONSE',
2084 avalue => 'TIMEOUT'
2085 );
2086
2087 wf_engine.setitemattrtext (
2088 itemtype => l_parent_item_type,
2089 itemkey => l_parent_item_key,
2090 aname => 'RECEIVER_USERNAME',
2091 avalue =>l_child_approver_user_name
2092 );
2093
2094 wf_engine.setitemattrtext (
2095 itemtype => l_parent_item_type,
2096 itemkey => l_parent_item_key,
2097 aname => 'RECEIVER_DISPLAYNAME',
2098 avalue => l_child_approver_display_name
2099 );
2100
2101 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
2102 itemkey => l_parent_item_key,
2103 activity => 'BLOCK',
2104 result => null);
2105
2106
2107 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2108 RETURN;
2109 exception
2110 WHEN OTHERS
2111 THEN
2112 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2113 aso_debug_pub.ADD (
2114 'Exception in Process_Response_timedout Procedure SqlCode :' || SQLERRM,
2115 1,
2116 'N'
2117 );
2118 END IF;
2119 wf_core.CONTEXT (
2120 itemtype,
2121 'Process_Response_timedout',
2122 itemtype,
2123 itemkey,
2124 TO_CHAR (
2125 actid
2126 ),
2127 funcmode
2128 );
2129 RAISE;
2130 END Process_Response_timedout;
2131
2132 --------------------------------------------------------------------------------
2133 --Start of Comments
2134 --Name: update_Action_history_pending
2135 --Pre-reqs:
2136 -- None.
2137 --Modifies:
2138 -- None.
2139 --Locks:
2140 -- None.
2141 --Function:
2142 -- Workflow activity PL/SQL handler.
2143 -- This procedure is used to set the status of current approver to Pending
2144 --Parameters:
2145 --IN:
2146 -- Standard workflow IN parameters
2147 --OUT:
2148 -- Standard workflow OUT parameters
2149 --Testing:
2150 --
2151 --End of Comments
2152 -------------------------------------------------------------------------------
2153 PROCEDURE update_Action_history_pending(
2154 itemtype IN VARCHAR2,
2155 itemkey IN VARCHAR2,
2156 actid IN NUMBER,
2157 funcmode IN VARCHAR2,
2158 resultout OUT NOCOPY VARCHAR2)
2159 IS
2160
2161 l_approval_det_id number;
2162 l_api_name VARCHAR2(500) := 'update_Action_history_pending';
2163 l_approver_category varchar2(1);
2164
2165
2166 BEGIN
2167
2168 IF (funcmode <> wf_engine.eng_run) THEN
2169 resultout := wf_engine.eng_null;
2170 RETURN;
2171 END IF;
2172
2173 --pp_debug('updating data for the l_approval_det_id'||l_approval_det_id);
2174
2175 l_approval_det_id := wf_engine.getitemattrnumber (
2176 itemtype,
2177 itemkey,
2178 'APPROVAL_DET_ID'
2179 );
2180
2181 -- pp_debug('updating data for the l_approval_det_id'||l_approval_det_id);
2182
2183 select nvl(APPROVER_CATEGORY,'A')
2184 into l_approver_category
2185 from ASO_APR_APPROVAL_DETAILS
2186 where APPROVAL_DET_ID=l_approval_det_id;
2187
2188 -- updating ASO action history
2189 if l_approver_category='A' then
2190 -- For Normal Approvers updating the status to approved
2191 update_approval_status (
2192 p_update_header_or_detail_flag => 'DETAIL' ,
2193 p_object_approval_id => null,
2194 p_approval_det_id =>l_approval_det_id,
2195 p_status => 'PEND',
2196 note => null);
2197 else
2198 -- FYI approvers updating the status to Not required , need to check if control cum here when there is no normal approver
2199 update_approval_status (
2200 p_update_header_or_detail_flag => 'DETAIL' ,
2201 p_object_approval_id => null,
2202 p_approval_det_id =>l_approval_det_id,
2203 p_status => 'NR',
2204 note => null);
2205 end if;
2206
2207 resultout := wf_engine.eng_completed;
2208 RETURN;
2209 exception
2210 WHEN OTHERS
2211 THEN
2212 -- pp_debug('Inside exception block update_Action_history_pending');
2213 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2214 aso_debug_pub.ADD (
2215 'Exception in update_Action_history_pending Procedure SqlCode :' || SQLERRM,
2216 1,
2217 'N'
2218 );
2219 END IF;
2220 wf_core.CONTEXT (
2221 itemtype,
2222 'update_Action_history_pending',
2223 itemtype,
2224 itemkey,
2225 TO_CHAR (
2226 actid
2227 ),
2228 funcmode
2229 );
2230 RAISE;
2231 END update_Action_history_pending;
2232
2233 --------------------------------------------------------------------------------
2234 --Start of Comments
2235 --Name: update_Action_history_fyi
2236 --Pre-reqs:
2237 -- None.
2238 --Modifies:
2239 -- None.
2240 --Locks:
2241 -- None.
2242 --Function:
2243 -- Workflow activity PL/SQL handler.
2244 -- This procedure is used to set the status of FYI approver to Not required
2245 --Parameters:
2246 --IN:
2247 -- Standard workflow IN parameters
2248 --OUT:
2249 -- Standard workflow OUT parameters
2250 --Testing:
2251 --
2252 --End of Comments
2253 -------------------------------------------------------------------------------
2254 PROCEDURE update_Action_history_fyi(
2255 itemtype IN VARCHAR2,
2256 itemkey IN VARCHAR2,
2257 actid IN NUMBER,
2258 funcmode IN VARCHAR2,
2259 resultout OUT NOCOPY VARCHAR2)
2260 IS
2261
2262 l_approval_det_id number;
2263 l_api_name VARCHAR2(500) := 'update_Action_history_fyi';
2264
2265
2266
2267 BEGIN
2268
2269 IF (funcmode <> wf_engine.eng_run) THEN
2270 resultout := wf_engine.eng_null;
2271 RETURN;
2272 END IF;
2273
2274 --pp_debug('updating data for the l_approval_det_id'||l_approval_det_id);
2275
2276 l_approval_det_id := wf_engine.getitemattrnumber (
2277 itemtype,
2278 itemkey,
2279 'APPROVAL_DET_ID'
2280 );
2281
2282 --pp_debug('updating data for the l_approval_det_id'||l_approval_det_id);
2283
2284
2285 -- FYI approvers updating the status to Not required
2286 update_approval_status (
2287 p_update_header_or_detail_flag => 'DETAIL' ,
2288 p_object_approval_id => null,
2289 p_approval_det_id =>l_approval_det_id,
2290 p_status => 'NR',
2291 note => null);
2292
2293
2294 resultout := wf_engine.eng_completed;
2295 RETURN;
2296 exception
2297 WHEN OTHERS
2298 THEN
2299 --pp_debug('Inside exception block update_Action_history_fyi');
2300 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2301 aso_debug_pub.ADD (
2302 'Exception in update_Action_history_fyi Procedure SqlCode :' || SQLERRM,
2303 1,
2304 'N'
2305 );
2306 END IF;
2307 wf_core.CONTEXT (
2308 itemtype,
2309 'update_Action_history_fyi',
2310 itemtype,
2311 itemkey,
2312 TO_CHAR (
2313 actid
2314 ),
2315 funcmode
2316 );
2317 RAISE;
2318 END update_Action_history_fyi;
2319
2320 PROCEDURE update_entity(
2321 p_object_approval_id IN NUMBER,
2322 --p_approval_det_id IN NUMBER,
2323 p_status IN VARCHAR2) AS
2324
2325
2326
2327
2328 l_contract_approval_level VARCHAR2 (240);
2329 l_quote_header_rec aso_quote_pub.qte_header_rec_type;
2330 l_control_rec aso_quote_pub.control_rec_type;
2331 l_user_id Number;
2332 l_person_id Number;
2333 l_status varchar2(20);
2334
2335 x_qte_header_rec aso_quote_pub.qte_header_rec_type;
2336 x_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
2337 x_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
2338 x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2339 x_hd_payment_tbl aso_quote_pub.payment_tbl_type;
2340 x_hd_shipment_tbl aso_quote_pub.shipment_tbl_type;
2341 x_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2342 x_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2343 x_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
2344 x_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
2345 x_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
2346 x_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
2347 x_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
2348 x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2349 x_ln_payment_tbl aso_quote_pub.payment_tbl_type;
2350 x_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
2351 x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2352 x_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2353 x_return_status VARCHAR2 (240);
2354 x_msg_count NUMBER;
2355 x_msg_data VARCHAR2 (240);
2356
2357 CURSOR get_quote_header_id (
2358 c_object_approval_id NUMBER
2359 ) IS
2360 SELECT object_id
2361 FROM aso_apr_obj_approvals
2362 WHERE object_approval_id = c_object_approval_id;
2363
2364 CURSOR check_contract_enabled (
2365 c_quote_header_id NUMBER
2366 ) IS
2367 SELECT contract_approval_level
2368 FROM aso_quote_headers_all
2369 WHERE quote_header_id = c_quote_header_id;
2370
2371 CURSOR get_latest_date (
2372 c_quote_header_id NUMBER
2373 ) IS
2374 SELECT last_update_date,org_id
2375 FROM aso_quote_headers_all
2376 WHERE quote_header_id = c_quote_header_id;
2377
2378 CURSOR get_quote_status_id (
2379 v_status VARCHAR2
2380 ) IS
2381 SELECT quote_status_id
2382 FROM aso_quote_statuses_b
2383 WHERE status_code = v_status;
2384
2385 CURSOR get_last_approver (
2386 c_object_approval_id NUMBER
2387 ) IS
2388 SELECT approver_person_id,approver_user_id
2389 FROM aso_apr_approval_details
2390 WHERE object_approval_id = c_object_approval_id
2391 AND approver_sequence = (select max(approver_sequence)
2392 FROM aso_apr_approval_details
2393 WHERE object_approval_id = c_object_approval_id)
2394 and rownum=1;
2395
2396 CURSOR get_user_id ( c_employee_id NUMBER)
2397 IS
2398 SELECT user_id
2399 FROM fnd_user
2400 WHERE employee_id = c_employee_id;
2401
2402
2403
2404 CURSOR get_rejected_approver (c_object_approval_id NUMBER) IS
2405 SELECT approver_person_id,approver_user_id
2406 FROM aso_apr_approval_details
2407 WHERE object_approval_id = c_object_approval_id
2408 AND approver_status = 'REJ';
2409
2410 BEGIN
2411 -- Initialize the quote header record
2412
2413 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2414 aso_debug_pub.ADD (
2415 'Begin Update Entity Procedure ',
2416 1,
2417 'N'
2418 );
2419 END IF;
2420
2421
2422 IF p_status = 'REJECT' then
2423 l_status := 'REJ';
2424 ELSIF p_status = 'APPROVE' THEN
2425 l_status := 'APPR';
2426 END IF;
2427
2428 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2429 aso_debug_pub.ADD (
2430 'Object approval ID :' || p_object_approval_id,
2431 1,
2432 'N'
2433 );
2434 aso_debug_pub.ADD (
2435 'Status :' || l_status,
2436 1,
2437 'N'
2438 );
2439 END IF;
2440 --g_user_id := fnd_global.user_id;
2441 -- fix for bug 3929409
2442 IF l_status = 'APPR' THEN
2443 OPEN get_last_approver (
2444 p_object_approval_id
2445 );
2446 FETCH get_last_approver INTO l_person_id,l_user_id;
2447 CLOSE get_last_approver;
2448
2449 if l_user_id is null then
2450 OPEN get_user_id(l_person_id);
2451 FETCH get_user_id INTO l_user_id;
2452 CLOSE get_user_id;
2453 end if;
2454
2455 g_user_id := l_user_id;
2456
2457 ELSIF l_status = 'REJ' THEN
2458 OPEN get_rejected_approver(p_object_approval_id);
2459 FETCH get_rejected_approver into l_person_id,l_user_id;
2460 CLOSE get_rejected_approver;
2461
2462 if l_user_id is null then
2463 OPEN get_user_id(l_person_id);
2464 FETCH get_user_id INTO l_user_id;
2465 CLOSE get_user_id;
2466 end if;
2467
2468 g_user_id := l_user_id;
2469
2470 ELSE
2471 g_user_id := fnd_global.user_id;
2472
2473 END IF;
2474
2475 FND_GLOBAL.APPS_INITIALIZE(g_user_id,0,0,0);
2476
2477
2478 l_quote_header_rec := aso_quote_pub.g_miss_qte_header_rec;
2479
2480 OPEN get_quote_header_id (
2481 p_object_approval_id
2482 );
2483 FETCH get_quote_header_id INTO l_quote_header_rec.quote_header_id;
2484 CLOSE get_quote_header_id;
2485 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2486 aso_debug_pub.ADD (
2487 'Quote Header ID :' || l_quote_header_rec.quote_header_id,
2488 1,
2489 'N'
2490 );
2491 END IF;
2492 -- Check if contract is enabled for the quote
2493 OPEN check_contract_enabled (
2494 l_quote_header_rec.quote_header_id
2495 );
2496 FETCH check_contract_enabled INTO l_contract_approval_level;
2497 CLOSE check_contract_enabled;
2498
2499 -- setting the quote status id
2500 IF l_status = 'APPR'
2501 THEN
2502 -- if contract is enabled, set status to contract enables
2503 IF l_contract_approval_level IS NOT NULL
2504 THEN
2505 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2506 aso_debug_pub.ADD (
2507 'Setting Quote Status to CONTRACT REQUIRED',
2508 1,
2509 'N'
2510 );
2511 END IF;
2512 OPEN get_quote_status_id (
2513 'CONTRACT REQUIRED'
2514 );
2515 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2516 CLOSE get_quote_status_id;
2517 ELSE
2518 -- otherwise set status to APPROVED
2519 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2520 aso_debug_pub.ADD (
2521 'Setting Quote Status to APPROVED',
2522 1,
2523 'N'
2524 );
2525 END IF;
2526 OPEN get_quote_status_id (
2527 'APPROVED'
2528 );
2529 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2530 CLOSE get_quote_status_id;
2531 END IF;
2532 ELSIF l_status = 'REJ'
2533 THEN
2534 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2535 aso_debug_pub.ADD (
2536 'Setting Quote Status to REJECTED',
2537 1,
2538 'N'
2539 );
2540 END IF;
2541 OPEN get_quote_status_id (
2542 'APPROVAL REJECTED'
2543 );
2544 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2545 CLOSE get_quote_status_id;
2546 ELSIF l_status = 'CAN'
2547 THEN
2548 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2549 aso_debug_pub.ADD (
2550 'Setting Quote Status to APPROVAL CANCELLED ',
2551 1,
2552 'N'
2553 );
2554 END IF;
2555 OPEN get_quote_status_id (
2556 'APPROVAL CANCELED'
2557 );
2558 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2559 CLOSE get_quote_status_id;
2560 ELSIF l_status = 'PEND'
2561 THEN
2562 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2563 aso_debug_pub.ADD (
2564 'Setting Quote Status to APPROVAL PENDING',
2565 1,
2566 'N'
2567 );
2568 END IF;
2569 OPEN get_quote_status_id (
2570 'APPROVAL PENDING'
2571 );
2572 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2573 CLOSE get_quote_status_id;
2574 END IF;
2575
2576 OPEN get_latest_date (
2577 l_quote_header_rec.quote_header_id
2578 );
2579 FETCH get_latest_date INTO l_quote_header_rec.last_update_date, l_quote_header_rec.org_id;
2580 CLOSE get_latest_date;
2581 -- Setting the auto version flag to true
2582 l_control_rec.auto_version_flag := fnd_api.g_true;
2583
2584 -- set the org context , see bug 4731684
2585 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2586 aso_debug_pub.ADD (
2587 'Before calling update quote: Setting the single org context to org_id: '|| l_quote_header_rec.org_id,
2588 1,
2589 'N'
2590 );
2591 END IF;
2592 mo_global.set_policy_context('S', l_quote_header_rec.org_id);
2593
2594
2595 -- Update the quote status by calling the update_quote API
2596
2597 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2598 aso_debug_pub.ADD (
2599 'Calling the update quote API in ASO_UPDATE_QUOTE_PUB package ',
2600 1,
2601 'N'
2602 );
2603 END IF;
2604 aso_quote_pub.update_quote (
2605 p_api_version_number => 1.0,
2606 p_init_msg_list => fnd_api.g_false,
2607 p_commit => fnd_api.g_false,
2608 p_control_rec => l_control_rec,
2609 p_qte_header_rec => l_quote_header_rec,
2610 p_hd_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2611 p_hd_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2612 p_hd_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2613 p_hd_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2614 p_hd_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2615 p_qte_line_tbl => aso_quote_pub.g_miss_qte_line_tbl,
2616 p_qte_line_dtl_tbl => aso_quote_pub.g_miss_qte_line_dtl_tbl,
2617 p_line_attr_ext_tbl => aso_quote_pub.g_miss_line_attribs_ext_tbl,
2618 p_line_rltship_tbl => aso_quote_pub.g_miss_line_rltship_tbl,
2619 p_price_adjustment_tbl => aso_quote_pub.g_miss_price_adj_tbl,
2620 p_price_adj_attr_tbl => aso_quote_pub.g_miss_price_adj_attr_tbl,
2621 p_price_adj_rltship_tbl => aso_quote_pub.g_miss_price_adj_rltship_tbl,
2622 p_ln_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2623 p_ln_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2624 p_ln_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2625 p_ln_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2626 p_ln_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2627 x_qte_header_rec => x_qte_header_rec,
2628 x_qte_line_tbl => x_qte_line_tbl,
2629 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
2630 x_hd_price_attributes_tbl => x_hd_price_attributes_tbl,
2631 x_hd_payment_tbl => x_hd_payment_tbl,
2632 x_hd_shipment_tbl => x_hd_shipment_tbl,
2633 x_hd_freight_charge_tbl => x_hd_freight_charge_tbl,
2634 x_hd_tax_detail_tbl => x_hd_tax_detail_tbl,
2635 x_line_attr_ext_tbl => x_line_attr_ext_tbl,
2636 x_line_rltship_tbl => x_line_rltship_tbl,
2637 x_price_adjustment_tbl => x_price_adjustment_tbl,
2638 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
2639 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl,
2640 x_ln_price_attributes_tbl => x_ln_price_attributes_tbl,
2641 x_ln_payment_tbl => x_ln_payment_tbl,
2642 x_ln_shipment_tbl => x_ln_shipment_tbl,
2643 x_ln_freight_charge_tbl => x_ln_freight_charge_tbl,
2644 x_ln_tax_detail_tbl => x_ln_tax_detail_tbl,
2645 x_return_status => x_return_status,
2646 x_msg_count => x_msg_count,
2647 x_msg_data => x_msg_data
2648 );
2649
2650 IF x_return_status <> fnd_api.g_ret_sts_success
2651 THEN
2652 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2653 aso_debug_pub.ADD (
2654 'Return Status from update quote API is :' || x_return_status,
2655 1,
2656 'N'
2657 );
2658 END IF;
2659
2660
2661 /*bug 3500380 */
2662 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2664 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF;
2667 --RAISE update_quote_exception;
2668
2669
2670 END IF;
2671
2672 -- set the org context , see bug 4731684
2673 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2674 aso_debug_pub.ADD (
2675 'After calling update quote Setting the org context to multi-org org_id:null',
2676 1,
2677 'N'
2678 );
2679 END IF;
2680
2681 mo_global.set_policy_context('M',null);
2682
2683
2684 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2685 aso_debug_pub.ADD (
2686 'End update entity procedure ',
2687 1,
2688 'N'
2689 );
2690 END IF;
2691
2692
2693 EXCEPTION
2694
2695 /*bug 3500380 */
2696 WHEN FND_API.G_EXC_ERROR THEN
2697 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2698 aso_debug_pub.ADD (
2699 'Exception in update entity ',
2700 1,
2701 'N'
2702 );
2703 END IF;
2704
2705 RAISE;
2706
2707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2708 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2709 aso_debug_pub.ADD (
2710 'Exception in update entity ',
2711 1,
2712 'N'
2713 );
2714 END IF;
2715
2716 RAISE;
2717
2718 WHEN OTHERS THEN
2719 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2720 aso_debug_pub.ADD (
2721 'Exception in update entity ',
2722 1,
2723 'N'
2724 );
2725 END IF;
2726
2727 RAISE;
2728
2729 END UPDATE_ENTITY;
2730
2731 PROCEDURE cancelapproval (
2732 approval_id IN NUMBER,
2733 p_itemtype IN VARCHAR2,
2734 p_user_id IN NUMBER
2735 ) IS
2736 l_approval_id NUMBER;
2737 l_itemkey VARCHAR2 (30);
2738 l_itemtype VARCHAR2 (30);
2739 l_requestor_name VARCHAR2 (240);
2740 l_requestor_displayname VARCHAR2 (240);
2741
2742 l_forward_user_name VARCHAR2 (240);
2743 l_forward_displayname VARCHAR2 (240);
2744 x_msg_data VARCHAR2 (10000);
2745 x_msg_count NUMBER;
2746 l_approval_object VARCHAR2 (4000);
2747 l_cancellor_displayname VARCHAR2 (240);
2748 l_cancellor_username VARCHAR2 (240);
2749 l_approval_det_id number;
2750 l_parent_item_type wf_items.parent_item_type%TYPE;
2751 l_parent_item_key wf_items.parent_item_key%TYPE;
2752
2753
2754 CURSOR l_child_wf (
2755 p_itemtype IN wf_items.parent_item_type%TYPE,
2756 p_itemkey IN wf_items.parent_item_key%TYPE
2757 )
2758 IS
2759 SELECT wfi.item_type, wfi.item_key
2760 FROM wf_items wfi, wf_item_activity_statuses wfias
2761 WHERE wfi.parent_item_key = p_itemkey
2762 AND wfi.item_type = p_itemtype
2763 AND wfi.parent_item_type = p_itemtype
2764 AND wfias.item_type = wfi.item_type
2765 AND wfias.item_key = wfi.item_key
2766 AND wfias.activity_status = 'NOTIFIED'
2767 AND wfias.notification_id IS NOT NULL;
2768 --AND wfi.item_key <> itemkey;
2769
2770 l_child_wf_cur l_child_wf%ROWTYPE;
2771
2772 CURSOR get_requestor (
2773 l_approval_id NUMBER
2774 ) IS
2775 SELECT aoa.requester_userid, fu.employee_id
2776 FROM aso_apr_obj_approvals aoa, fnd_user fu
2777 WHERE object_approval_id = l_approval_id
2778 AND aoa.requester_userid = fu.user_id
2779 AND SYSDATE BETWEEN fu.start_date AND NVL (
2780 fu.end_date,
2781 SYSDATE
2782 );
2783 CURSOR get_username(l_user_id NUMBER) IS
2784 SELECT user_name
2785 FROM fnd_user
2786 WHERE user_id = l_user_id;
2787
2788 l_object_id number;
2789 type approverrec is record
2790 ( username varchar(320));
2791 TYPE approvertable IS TABLE OF approverrec
2792 INDEX BY BINARY_INTEGER;
2793 appr_rec approverrec;
2794 empty_appr_rec approverrec;
2795 empty_appr_tbl approvertable;
2796 appr_tbl approvertable;
2797 l_count number:=0;
2798
2799 BEGIN
2800
2801 --pp_debug('Begin CancelApproval Procedure approval_id::'||approval_id);
2802 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2803 aso_debug_pub.ADD (
2804 'Begin CancelApproval Procedure',
2805 1,
2806 'N'
2807 );
2808
2809 END IF;
2810
2811 select object_id into l_object_id
2812 from aso_apr_obj_approvals
2813 where object_approval_id=approval_id
2814 and approval_Status='PEND';
2815
2816 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN -- bug 16699364
2817
2818 aso_debug_pub.ADD (
2819 'Aborting process for approval ID :' || approval_id,
2820 1,
2821 'N'
2822 );
2823 END IF;
2824 -- Updating the approval obj table status
2825 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2826 aso_debug_pub.ADD (
2827 'Updating table aso_apr_obj_approvals, status is CAN and approval id :'
2828 || approval_id,
2829 1,
2830 'N'
2831 );
2832 END IF;
2833
2834 --pp_debug('In CancelApproval Procedure updating aso_apr_obj_app approval_id::'||approval_id);
2835
2836 aso_pa_apr_wf_pvt.update_approval_status (
2837 p_update_header_or_detail_flag => 'HEADER' ,
2838 p_object_approval_id => approval_id,
2839 p_approval_det_id =>null,
2840 p_status => 'CAN',
2841 note => null);
2842
2843 l_parent_item_type:= p_itemtype;
2844 l_parent_item_key := TO_CHAR (approval_id) || 'HED';
2845
2846 --pp_debug('In CancelApproval Procedure updating l_parent_item_type::'||l_parent_item_type);
2847 --pp_debug('In CancelApproval Procedure updating l_parent_item_key::'||l_parent_item_key);
2848
2849 -- Aborting the parent process
2850 wf_engine.abortprocess (
2851 itemtype => l_parent_item_type, --'ASOAPPRV',
2852 itemkey => l_parent_item_key
2853
2854 );
2855
2856 l_approval_id := approval_id;
2857 l_itemtype := p_itemtype;
2858 l_itemkey := TO_CHAR (
2859 approval_id
2860 ) || 'CAN';
2861
2862 --pp_debug('In CancelApproval Procedure l_itemtype::'||l_itemtype);
2863 --pp_debug('In CancelApproval Procedure l_itemkey::'||l_itemkey);
2864
2865
2866
2867 FOR i IN get_requestor (
2868 l_approval_id
2869 )
2870 LOOP
2871
2872 IF i.employee_id IS NOT NULL
2873 THEN
2874 wf_directory.getrolename (
2875 'PER',
2876 i.employee_id,
2877 l_requestor_name,
2878 l_requestor_displayname
2879 );
2880 ELSE
2881 wf_directory.getrolename (
2882 'FND_USR',
2883 i.requester_userid,
2884 l_requestor_name,
2885 l_requestor_displayname
2886 );
2887 END IF;
2888
2889 l_requestor_displayname :=
2890 wf_directory.getroledisplayname (
2891 l_requestor_name
2892 );
2893 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2894 aso_debug_pub.ADD (
2895 'Requester Displayname is :' || l_requestor_displayname,
2896 1,
2897 'N'
2898 );
2899 END IF;
2900
2901 END LOOP;
2902 --pp_debug('In CancelApproval Procedure l_requestor_name::'||l_requestor_name);
2903 --pp_debug('In CancelApproval Procedure l_requestor_displayname::'||l_requestor_displayname);
2904 -- Get the display name of the request cancellor
2905
2906 OPEN get_username(p_user_id);
2907 FETCH get_username INTO l_cancellor_username;
2908 CLOSE get_username;
2909
2910 l_cancellor_displayname :=
2911 wf_directory.getroledisplayname (
2912 l_cancellor_username
2913 );
2914 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2915 aso_debug_pub.ADD (
2916 'Cancellor Displayname is :' || l_cancellor_displayname,
2917 1,
2918 'N'
2919 );
2920 END IF;
2921
2922 -- pp_debug('In CancelApproval Procedure l_cancellor_displayname::'||l_cancellor_displayname);
2923
2924
2925
2926 l_count:=0;
2927 appr_tbl:=empty_appr_tbl;
2928 OPEN l_child_wf (l_parent_item_type, l_parent_item_key);
2929
2930 LOOP
2931 FETCH l_child_wf
2932 INTO l_child_wf_cur;
2933 EXIT WHEN l_child_wf%NOTFOUND;
2934 BEGIN
2935 l_count:=l_count+1;
2936 --pp_debug('In CancelApproval Procedure l_child_wf_cur.item_type::'||l_child_wf_cur.item_type);
2937 --pp_debug('In CancelApproval Procedure l_child_wf_cur.item_key::'||l_child_wf_cur.item_key);
2938 l_approval_det_id:=wf_engine.getitemattrnumber (
2939 itemtype=> l_child_wf_cur.item_type,
2940 itemkey=> l_child_wf_cur.item_key,
2941 aname=> 'APPROVAL_DET_ID');
2942
2943 l_forward_user_name:= wf_engine.getitemattrtext (
2944 itemtype => l_child_wf_cur.item_type,
2945 itemkey => l_child_wf_cur.item_key,
2946 aname => 'RECEIVER_USERNAME'
2947 );
2948
2949
2950 --pp_debug('In CancelApproval Procedure l_approval_det_id::'||l_approval_det_id);
2951
2952 -- pp_debug('In CancelApproval Procedure l_forward_user_name::'||l_forward_user_name);
2953
2954
2955 appr_rec.username:=l_forward_user_name;
2956 appr_tbl(l_count):=appr_rec;
2957 appr_rec:=empty_appr_rec;
2958
2959
2960
2961
2962 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2963 aso_debug_pub.ADD (
2964 'Aborting process for approval detail ID :' || l_approval_det_id,
2965 1,
2966 'N'
2967 );
2968 END IF;
2969 wf_engine.abortprocess (
2970 itemtype => p_itemtype, --'ASOAPPRV',
2971 itemkey => to_char(l_approval_det_id)||'DET'
2972 -- process => '',
2973 -- result => 'CANCELLED'
2974 );
2975
2976 --pp_debug('Before updating the detail table');
2977
2978 aso_pa_apr_wf_pvt.update_approval_status (
2979 p_update_header_or_detail_flag => 'DETAIL' ,
2980 p_object_approval_id => null,
2981 p_approval_det_id =>l_approval_det_id,
2982 p_status => 'CAN',
2983 note => null);
2984
2985
2986
2987
2988 END ;
2989 END LOOP;
2990
2991 --pp_debug('In CancelApproval Procedure approver table count::'||appr_tbl.count);
2992 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2993 aso_debug_pub.ADD (
2994 'Starting process CANCEL_REQUISITION itemtype is '
2995 || l_itemtype
2996 || ' and itemkey is '
2997 || l_itemkey,
2998 1,
2999 'N'
3000 );
3001 END IF;
3002 wf_engine.createprocess (
3003 itemtype => l_itemtype,
3004 itemkey => l_itemkey,
3005 process => 'PA_CANCEL_REQUISTION'
3006 );
3007 wf_engine.setitemuserkey (
3008 itemtype => l_itemtype,
3009 itemkey => l_itemkey,
3010 userkey => l_itemkey
3011 );
3012 wf_engine.setitemattrnumber (
3013 itemtype => l_itemtype,
3014 itemkey => l_itemkey,
3015 aname => 'APPROVAL_DET_ID',
3016 avalue => l_approval_det_id
3017 );
3018 wf_engine.setitemattrtext (
3019 itemtype => l_itemtype, --'ASOAPPRV',
3020 itemkey => l_itemkey,
3021 aname => 'REQUESTOR_DISPLAYNAME',
3022 avalue => l_requestor_displayname
3023 );
3024 wf_engine.setitemattrtext (
3025 itemtype => l_itemtype, --'ASOAPPRV',
3026 itemkey => l_itemkey,
3027 aname => 'REQUESTOR_USERNAME',
3028 avalue => l_requestor_name
3029 );
3030 wf_engine.setitemattrtext (
3031 itemtype => l_itemtype,
3032 itemkey => l_itemkey,
3033 aname => 'RECEIVER_USERNAME',
3034 avalue => l_forward_user_name
3035 );
3036 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3037 aso_debug_pub.ADD (
3038 'Receiver username is ' || l_forward_user_name,
3039 1,
3040 'N'
3041 );
3042 END IF;
3043 l_forward_displayname :=
3044 wf_directory.getroledisplayname (
3045 l_forward_user_name
3046 );
3047 wf_engine.setitemattrtext (
3048 itemtype => l_itemtype,
3049 itemkey => l_itemkey,
3050 aname => 'RECEIVER_DISPLAYNAME',
3051 avalue => l_forward_displayname
3052 );
3053 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3054 aso_debug_pub.ADD (
3055 'Receiver displayname is ' || l_forward_displayname,
3056 1,
3057 'N'
3058 );
3059 END IF;
3060 wf_engine.setitemattrtext (
3061 itemtype => l_itemtype,
3062 itemkey => l_itemkey,
3063 aname => 'REQUEST_CANCELLOR_USERNAME',
3064 avalue => l_cancellor_username
3065 );
3066 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3067 aso_debug_pub.ADD (
3068 'Cancellor username is ' || l_cancellor_username,
3069 1,
3070 'N'
3071 );
3072 END IF;
3073 wf_engine.setitemattrtext (
3074 itemtype => l_itemtype,
3075 itemkey => l_itemkey,
3076 aname => 'REQUEST_CANCELLOR_DISPLAYNAME',
3077 avalue => l_cancellor_displayname
3078 );
3079 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3080 aso_debug_pub.ADD (
3081 'Cancellor displayname is ' || l_cancellor_displayname,
3082 1,
3083 'N'
3084 );
3085 END IF;
3086 wf_engine.setitemattrnumber (
3087 itemtype => l_itemtype, --'ASOAPPRV',
3088 itemkey => l_itemkey,
3089 aname => 'APPROVALID',
3090 avalue => l_approval_id
3091 );
3092
3093 --pp_debug('Before starting the process'||l_itemtype);
3094 --pp_debug('Before starting the process'||l_itemkey);
3095 wf_engine.startprocess (
3096 itemtype => l_itemtype,
3097 itemkey => l_itemkey
3098 );
3099
3100 --pp_debug('After the cancel WF process');
3101
3102 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3103 aso_debug_pub.ADD (
3104 'Updating detail table setting approver status to cancelled where det id :'
3105 || l_approval_det_id,
3106 1,
3107 'N'
3108 );
3109 END IF;
3110
3111 /* pp_debug('Before updating the detail table');
3112
3113 aso_pa_apr_wf_pvt.update_approval_status (
3114 p_update_header_or_detail_flag => 'DETAIL' ,
3115 p_object_approval_id => null,
3116 p_approval_det_id =>l_approval_det_id,
3117 p_status => 'CAN',
3118 note => null);
3119
3120
3121
3122
3123 END ;
3124 END LOOP; */
3125
3126 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3127 aso_debug_pub.ADD ('Sending notification to all approvers count'||appr_tbl.count,1,'N' );
3128 END IF;
3129 --pp_debug('Sending notification to cancelled approvers '||appr_tbl.count);
3130 for i in 1..appr_tbl.count loop
3131 -- Sending mail to approver for cancel
3132
3133 cancel_notify_approver
3134 (
3135 p_approval_id => l_approval_id,
3136 p_item_type => l_itemtype,
3137 p_message_name =>'OA_PA_CANCEL_REQ',
3138 p_from_user_name => l_requestor_name,
3139 p_from_displayname => l_requestor_displayname,
3140 p_cancellor_username => l_cancellor_username,
3141 p_cancellor_displayname => l_cancellor_displayname,
3142 p_role => appr_tbl(i).username
3143
3144 );
3145
3146
3147 end loop;
3148
3149
3150 -- clearing the data in AME
3151 ame_api2.clearallapprovals (
3152 applicationidin => 880,
3153 transactionidin => l_object_id,
3154 transactiontypein => 'Quote'
3155 );
3156
3157 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3158 aso_debug_pub.ADD (
3159 'End of CancelApproval Procedure',
3160 1,
3161 'N'
3162 );
3163 END IF;
3164 EXCEPTION
3165 WHEN OTHERS
3166 THEN
3167 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3168 aso_debug_pub.ADD (
3169 'Exception in CancelApproval SqlCode :' || SQLERRM,
3170 1,
3171 'N'
3172 );
3173 END IF;
3174 fnd_msg_pub.count_and_get (
3175 p_encoded => 'F',
3176 p_count => x_msg_count,
3177 p_data => x_msg_data
3178 );
3179 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3180 aso_debug_pub.ADD (
3181 'no. of FND messages :' || x_msg_count,
3182 1,
3183 'N'
3184 );
3185 END IF;
3186
3187 FOR k IN 1 .. x_msg_count
3188 LOOP
3189 x_msg_data := fnd_msg_pub.get (
3190 p_msg_index => k,
3191 p_encoded => 'F'
3192 );
3193 END LOOP;
3194
3195 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3196 aso_debug_pub.ADD (
3197 'Msg Data is' || x_msg_data,
3198 1,
3199 'N'
3200 );
3201 END IF;
3202 wf_core.CONTEXT (
3203 p_itemtype,
3204 'CancelApproval ' || x_msg_data,
3205 approval_id
3206 );
3207 RAISE;
3208
3209
3210 END cancelapproval;
3211
3212 PROCEDURE UPDATE_ENTITY_REJECT (
3213 itemtype IN VARCHAR2,
3214 itemkey IN VARCHAR2,
3215 actid IN NUMBER,
3216 funcmode IN VARCHAR2,
3217 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3218 ) IS
3219 l_approval_id NUMBER;
3220
3221 BEGIN
3222 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3223 aso_debug_pub.ADD (
3224 'Begin UPDATE_ENTITY_REJECT Procedure ',
3225 1,
3226 'N'
3227 );
3228 END IF;
3229
3230 IF (funcmode <> wf_engine.eng_run) THEN
3231 resultout := wf_engine.eng_null;
3232 RETURN;
3233 END IF;
3234
3235 IF funcmode = 'RUN'
3236 THEN
3237
3238 l_approval_id := wf_engine.getitemattrnumber (
3239 itemtype,
3240 itemkey,
3241 'APPROVALID'
3242 );
3243 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3244 aso_debug_pub.ADD ('Approval ID is : ' || l_approval_id,1,'N');
3245
3246 aso_debug_pub.ADD ('Calling the update entity procedure setting quote status to REJECTED',1,'N' );
3247 END IF;
3248 -- updating the ASO_APR_OBJ_APPROVALS
3249 update_approval_status (
3250 p_update_header_or_detail_flag => 'HEADER' ,
3251 p_object_approval_id => l_approval_id,
3252 p_approval_det_id =>null,
3253 p_status => 'REJ',
3254 note => null);
3255
3256 -- updating the quote entity
3257 update_entity(
3258 p_object_approval_id => l_approval_id,
3259 -- p_approval_det_id => NULL,
3260 p_status => 'REJECT');
3261
3262
3263 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3264 aso_debug_pub.ADD ('End of UPDATE_ENTITY_REJECT Procedure',1,'N');
3265 END IF;
3266
3267 resultout := wf_engine.eng_completed;
3268 RETURN;
3269 END IF;
3270
3271
3272 EXCEPTION
3273 WHEN OTHERS
3274 THEN
3275 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3276 aso_debug_pub.ADD (
3277 'Exception in UPDATE_ENTITY_REJECT Procedure SqlCode :' || SQLERRM,
3278 1,
3279 'N'
3280 );
3281 END IF;
3282 wf_core.CONTEXT (
3283 itemtype,
3284 'UPDATE_ENTITY_REJECT',
3285 itemtype,
3286 itemkey,
3287 TO_CHAR (
3288 actid
3289 ),
3290 funcmode
3291 );
3292 RAISE;
3293
3294
3295 END UPDATE_ENTITY_REJECT;
3296
3297 PROCEDURE UPDATE_ENTITY_approved (
3298 itemtype IN VARCHAR2,
3299 itemkey IN VARCHAR2,
3300 actid IN NUMBER,
3301 funcmode IN VARCHAR2,
3302 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3303 ) IS
3304 l_approval_id NUMBER;
3305 l_apr_count NUMBER;
3306
3307 BEGIN
3308 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3309 aso_debug_pub.ADD (
3310 'Begin update_Action_history_approved Procedure ',
3311 1,
3312 'N'
3313 );
3314 END IF;
3315
3316 IF (funcmode <> wf_engine.eng_run) THEN
3317 resultout := wf_engine.eng_null;
3318 RETURN;
3319 END IF;
3320
3321 IF funcmode = 'RUN'
3322 THEN
3323
3324 l_approval_id := wf_engine.getitemattrnumber (
3325 itemtype,
3326 itemkey,
3327 'APPROVALID'
3328 );
3329 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3330 aso_debug_pub.ADD ('Approval ID is : ' || l_approval_id,1,'N');
3331
3332 aso_debug_pub.ADD ('Calling the update entity procedure setting quote status to APPROVED',1,'N' );
3333 END IF;
3334
3335 -- updating the ASO_APR_OBJ_APPROVALS
3336 update_approval_status (
3337 p_update_header_or_detail_flag => 'HEADER' ,
3338 p_object_approval_id => l_approval_id,
3339 p_approval_det_id =>null,
3340 p_status => 'APPR',
3341 note => null);
3342
3343 select count(*) into l_apr_count
3344 from aso_apr_approval_details
3345 where approver_category='A'
3346 and object_approval_id=l_approval_id;
3347
3348 -- updating quote entity only if there are approvers in the quote
3349 if l_apr_count>0 then
3350 update_entity(
3351 p_object_approval_id => l_approval_id,
3352 -- p_approval_det_id => NULL,
3353 p_status => 'APPROVE');
3354 end if;
3355
3356
3357 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3358 aso_debug_pub.ADD ('End of UPDATE_ENTITY_approve Procedure',1,'N');
3359 END IF;
3360
3361 resultout := wf_engine.eng_completed;
3362 RETURN;
3363 END IF;
3364
3365
3366 EXCEPTION
3367 WHEN OTHERS
3368 THEN
3369 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3370 aso_debug_pub.ADD (
3371 'Exception in UPDATE_ENTITY_approve Procedure SqlCode :' || SQLERRM,
3372 1,
3373 'N'
3374 );
3375 END IF;
3376 wf_core.CONTEXT (
3377 itemtype,
3378 'UPDATE_ENTITY_approve',
3379 itemtype,
3380 itemkey,
3381 TO_CHAR (
3382 actid
3383 ),
3384 funcmode
3385 );
3386 RAISE;
3387
3388
3389 END UPDATE_ENTITY_approved;
3390
3391
3392 PROCEDURE is_ame_exception(
3393 itemtype IN VARCHAR2,
3394 itemkey IN VARCHAR2,
3395 actid IN NUMBER,
3396 funcmode IN VARCHAR2,
3397 resultout OUT NOCOPY VARCHAR2)
3398 IS
3399 l_ame_exception ame_util.longestStringType:=null;
3400 l_api_name VARCHAR2(500) := 'is_ame_exception';
3401
3402 BEGIN
3403
3404 IF (funcmode <> wf_engine.eng_run) THEN
3405 resultout := wf_engine.eng_null;
3406 RETURN;
3407 END IF;
3408
3409 IF l_ame_exception IS NOT NULL THEN
3410 resultout := wf_engine.eng_completed || ':' ||'Y';
3411 ELSE
3412 resultout := wf_engine.eng_completed || ':' ||'N';
3413 END IF;
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 wf_core.context(g_pkg_name, l_api_name, 'Unexpected Exception:', 'IS AME Exceptioon exception block', SQLERRM);
3417
3418 END is_ame_exception;
3419
3420 procedure insertapprovershistory
3421 (l_approval_det_id IN number,
3422 l_approval_id IN number,
3423 l_approver_person_id IN number,
3424 l_approver_user_id in number,
3425 l_order_number IN number,
3426 l_NAME IN VARCHAR2,
3427 l_ITEM_CLASS IN VARCHAR2,
3428 l_ITEM_ID IN VARCHAR2,
3429 l_ACTION_TYPE_ID IN NUMBER,
3430 l_GROUP_OR_CHAIN_ID IN NUMBER,
3431 L_OCCURRENCE IN NUMBER,
3432 L_AUTHORITY IN VARCHAR2,
3433 l_APPROVER_CATEGORY IN VARCHAR2)
3434 is
3435 pragma AUTONOMOUS_TRANSACTION;
3436 l_approval_detail number;
3437 begin
3438 l_approval_detail:=l_approval_det_id;
3439 --pp_debug('Inside insertapprovershistory approval detail id'||l_approval_detail);
3440 --pp_debug('Inside insertapprovershistory approver name'||l_NAME);
3441 aso_apr_approvals_pkg.detail_insert_row (
3442 l_approval_detail,
3443 l_approval_id,
3444 l_approver_person_id, --p_APPROVER_PERSON_ID
3445 l_approver_user_id, --p_APPROVER_USER_ID
3446 l_order_number, --p_approver_sequence
3447 'PEND', -- p_APPROVER_STATUS
3448
3449 NULL -- p_APPROVER_COMMENTS
3450 ,
3451 NULL -- p_DATE_SENT
3452 ,
3453 NULL -- p_DATE_RECEIVED
3454 ,
3455 SYSDATE -- p_CREATION_DATE
3456 ,
3457 SYSDATE -- p_LAST_UPDATE_DATE
3458 ,
3459 g_user_id -- P_CREATED_BY
3460 ,
3461 g_user_id -- P_UPDATED_BY
3462 ,
3463 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3464 ,
3465 NULL -- p_ATTRIBUTE1
3466 ,
3467 NULL -- p_ATTRIBUTE2
3468 ,
3469 NULL -- p_ATTRIBUTE3
3470 ,
3471 NULL -- p_ATTRIBUTE4
3472 ,
3473 NULL -- p_ATTRIBUTE5
3474 ,
3475 NULL -- p_ATTRIBUTE6
3476 ,
3477 NULL -- p_ATTRIBUTE7
3478 ,
3479 NULL -- p_ATTRIBUTE8
3480 ,
3481 NULL -- p_ATTRIBUTE9
3482 ,
3483 NULL -- p_ATTRIBUTE10
3484 ,
3485 NULL -- p_ATTRIBUTE11
3486 ,
3487 NULL -- p_ATTRIBUTE12
3488 ,
3489 NULL -- p_ATTRIBUTE13
3490 ,
3491 NULL -- p_ATTRIBUTE14
3492 ,
3493 NULL -- p_ATTRIBUTE15
3494 ,
3495 NULL -- p_Attribute16
3496 ,
3497 NULL -- p_Attribute17
3498 ,
3499 NULL -- p_Attribute18
3500 ,
3501 NULL -- p_Attribute19
3502 ,
3503 NULL -- p_Attribute20
3504 ,
3505 NULL -- p_CONTEXT
3506 ,
3507 NULL -- p_SECURITY_GROUP_ID
3508 ,
3509 NULL, -- p_OBJECT_VERSION_NUMBER,
3510 l_name, -- NAME
3511 l_item_id, --ITEM_ID
3512 l_item_class, --ITEM_CLASS
3513 l_action_type_id, --ACTION_TYPE_ID
3514 l_group_or_chain_id, --GROUP OR CHAIN ID
3515 l_occurrence, -- occurrence
3516 L_AUTHORITY, -- Authority
3517 l_approver_category -- approver category
3518 );
3519 commit;
3520 end insertapprovershistory;
3521
3522 procedure insertruledetails(approvalid number, p_rule_list_ids ame_util.idList) IS
3523 pragma AUTONOMOUS_TRANSACTION;
3524
3525 p_rule_id number;
3526
3527 begin
3528
3529
3530
3531 delete from aso_apr_rules
3532 where object_approval_id=approvalid;
3533
3534 /*if sql%found then
3535 commit;
3536 end if;
3537 */
3538 --pp_debug('In procedure insertruledetails, after deleting old details ');
3539
3540 for i in 1..p_rule_list_ids.count loop
3541 --pp_debug('In loop rule id'||p_rule_list_ids(i));
3542 aso_apr_approvals_pkg.rule_insert_row (
3543 p_rule_id,
3544 p_rule_list_ids (i),
3545 null, --rule_action_id
3546 SYSDATE -- P_CREATION_DATE
3547 ,
3548 g_user_id -- P_CREATED_BY
3549 ,
3550 SYSDATE -- p_LAST_UPDATE_DATE
3551 ,
3552 g_user_id -- P_UPDATED_BY
3553 ,
3554 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3555 ,
3556 approvalid,
3557 NULL -- p_ATTRIBUTE1
3558 ,
3559 NULL -- p_ATTRIBUTE2
3560 ,
3561 NULL -- p_ATTRIBUTE3
3562 ,
3563 NULL -- p_ATTRIBUTE4
3564 ,
3565 NULL -- p_ATTRIBUTE5
3566 ,
3567 NULL -- p_ATTRIBUTE6
3568 ,
3569 NULL -- p_ATTRIBUTE7
3570 ,
3571 NULL -- p_ATTRIBUTE8
3572 ,
3573 NULL -- p_ATTRIBUTE9
3574 ,
3575 NULL -- p_ATTRIBUTE10
3576 ,
3577 NULL -- p_ATTRIBUTE11
3578 ,
3579 NULL -- p_ATTRIBUTE12
3580 ,
3581 NULL -- p_ATTRIBUTE13
3582 ,
3583 NULL -- p_ATTRIBUTE14
3584 ,
3585 NULL -- p_ATTRIBUTE15
3586 ,
3587 NULL -- p_Attribute16
3588 ,
3589 NULL -- p_Attribute17
3590 ,
3591 NULL -- p_Attribute18
3592 ,
3593 NULL -- p_Attribute19
3594 ,
3595 NULL -- p_Attribute20
3596 ,
3597 NULL -- p_CONTEXT
3598 ,
3599 NULL -- p_SECURITY_GROUP_ID
3600 ,
3601 null -- p_OBJECT_VERSION_NUMBER
3602 );
3603 END LOOP;
3604 commit;
3605
3606 exception
3607 when others then
3608 null;
3609 --pp_debug('Error in fetching rule details');
3610 end insertruledetails;
3611
3612 procedure cancel_notify_approver
3613 (
3614 p_approval_id IN NUMBER,
3615 p_item_type IN VARCHAR2,
3616 p_message_name IN VARCHAR2,
3617 p_from_user_name IN VARCHAR2,
3618 p_from_displayname IN VARCHAR2,
3619 p_cancellor_username IN VARCHAR2,
3620 p_cancellor_displayname IN VARCHAR2,
3621 p_role IN VARCHAR2
3622
3623
3624 )
3625 as
3626 pragma AUTONOMOUS_TRANSACTION;
3627
3628 l_notification_id NUMBER;
3629 l_approval_id number;
3630 l_qte_header_id number;
3631 l_quote_name varchar2(240);
3632 l_qte_number number;
3633 l_url VARCHAR2(32000);
3634 l_resp_appl_id NUMBER := 880;
3635 l_resp_id NUMBER;
3636 l_ampsign VARCHAR2(1) := fnd_global.local_chr(38);
3637 BEGIN
3638
3639 l_approval_id:=p_approval_id;
3640
3641 select object_id into l_qte_header_id
3642 from aso_apr_obj_approvals
3643 where object_approval_id=l_approval_id;
3644
3645 select quote_name,quote_number into l_quote_name,l_qte_number
3646 from aso_quote_headers_all
3647 where quote_header_id=l_qte_header_id;
3648
3649 --pp_debug('quote number'||l_qte_number);
3650 --pp_debug('l_quote_name'||l_quote_name);
3651 l_notification_id := wf_notification.send(role =>p_role,
3652 msg_type => p_item_type,
3653 msg_name => p_message_name);
3654
3655 --pp_debug('Notification id'||l_notification_id);
3656
3657 wf_notification.SetAttrText(nid =>l_notification_id,
3658 aname=>'APPR_OBJ',
3659 avalue=>l_quote_name);
3660
3661 wf_notification.SetAttrText(nid =>l_notification_id,
3662 aname=> 'REQUEST_CANCELLOR_USERNAME',
3663 avalue =>p_cancellor_username);
3664 wf_notification.SetAttrText(nid =>l_notification_id,
3665 aname=> 'REQUEST_CANCELLOR_DISPLAYNAME',
3666 avalue =>p_cancellor_displayname);
3667
3668 wf_notification.SetAttrText (
3669 nid =>l_notification_id,
3670 aname => 'REQUESTOR_DISPLAYNAME',
3671 avalue => p_from_displayname
3672 );
3673 wf_notification.SetAttrText (
3674 nid =>l_notification_id,
3675 aname => 'REQUESTOR_USERNAME',
3676 avalue => p_from_user_name
3677 );
3678
3679 wf_notification.SetAttrText(nid =>l_notification_id,
3680 aname=> '#FROM_ROLE',
3681 avalue =>p_from_user_name);
3682
3683 wf_notification.SetAttrText(nid =>l_notification_id,
3684 aname=> 'OAPAAPPROVALRGN',
3685 avalue =>'JSP:/OA_HTML/OA.jsp?OAFunc=QOT_OAUI_PAPPVL_NOTIF&qotHdrId='||l_qte_header_id||'&qotReqName='||p_from_displayname||'&qotObjApvlId='||l_approval_id);
3686
3687 select nvl(fnd_profile.value('ASO_QUOTE_APPROVER_RESP'),-1) into l_resp_id from dual;
3688
3689 -- bug 5350149
3690 l_url := aso_apr_wf_pvt.GetRunFuncURL(
3691 p_function_name => 'QOT_OAUI_QUOTE_DETAILS',
3692 p_resp_appl_id => l_resp_appl_id, -- bug 7657061
3693 p_resp_id => l_resp_id, -- bug 7657061
3694 p_parameters => l_ampsign||l_ampsign||'QotIntgEvtSrc=ApvlNotif'||l_ampsign||'QotIntgEvt=Event.QuoteDet'||l_ampsign||'QotIntgEvtVal='
3695 ||l_qte_header_id||l_ampsign||'QotIntgEvtVal1='||l_qte_number||l_ampsign||'addBreadCrumb=Y') ;
3696
3697
3698 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3699 aso_debug_pub.ADD ('RESP_APPL_ID: ' || l_resp_appl_id,1,'N'); -- bug 7657061
3700 aso_debug_pub.ADD ('RESP_ID: ' || l_resp_id,1,'N'); -- bug 7657061
3701 aso_debug_pub.ADD ('l_url: '||substr(l_url,1,240),1,'N');
3702 END IF;
3703
3704 --pp_debug('l_url: '||l_url);
3705
3706 wf_notification.SetAttrText
3707 (nid =>l_notification_id,
3708 aname => 'OAQTEDETAILLNK',
3709 avalue => l_url
3710 );
3711
3712
3713 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3714 aso_debug_pub.ADD ('Sending notification ' || l_notification_id ||' to ' || p_role, 1,'N');
3715
3716 end if;
3717 --pp_debug('Notification has been sent. Notification ID:' || l_notification_id);
3718
3719
3720 COMMIT;
3721
3722 EXCEPTION
3723
3724 WHEN OTHERS THEN
3725 ROLLBACK;
3726 --pp_debug('Exception ' || SQLERRM || ' while sending notification to ' || p_role);
3727 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3728 aso_debug_pub.ADD ('Exception ' || SQLERRM || ' while sending notification to ' || p_role, 1,'N');
3729
3730 end if;
3731 end cancel_notify_approver;
3732
3733 END ASO_PA_APR_WF_PVT;
3734