[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_WF_PVT
Source
1 PACKAGE BODY aso_apr_wf_pvt AS
2 /* $Header: asovwapb.pls 120.9 2007/01/31 19:12:16 skulkarn ship $ */
3 g_user_id NUMBER:= FND_GLOBAL.USER_ID;
4
5 PROCEDURE start_aso_approvals (
6 P_Object_approval_id IN NUMBER,
7 P_itemtype_name IN VARCHAR2,
8 P_sender_name IN VARCHAR2
9 ) IS
10 l_itemkey VARCHAR2 (30);
11 l_itemtype VARCHAR2 (30);
12 l_requestor_display_name VARCHAR2 (240);
13 l_object_approval_id NUMBER;
14
15
16 CURSOR C_get_appr_id IS
17 SELECT min(object_approval_id)
18 FROM aso_apr_obj_approvals
19 WHERE object_id = (SELECT object_id
20 FROM aso_apr_obj_approvals
21 WHERE object_approval_id = P_Object_approval_id)
22 AND approval_status = 'PEND';
23
24 BEGIN
25 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
26 aso_debug_pub.ADD (
27 'Begin ASO_APR_WF_PVT package ',
28 1,
29 'N'
30 );
31 aso_debug_pub.ADD (
32 'Starting Approval Process for approval id ' || P_Object_approval_id,
33 1,
34 'N'
35 );
36 aso_debug_pub.ADD (
37 'Starting Approval Process for sender_name ' || P_sender_name,
38 1,
39 'N'
40 );
41 END IF;
42
43 OPEN C_get_appr_id;
44 FETCH C_get_appr_id INTO l_object_approval_id;
45 CLOSE C_get_appr_id;
46
47 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
48 aso_debug_pub.ADD('l_object_approval_id: '|| l_object_approval_id,1,'N');
49 END IF;
50
51 IF (P_Object_approval_id = l_object_approval_id) THEN
52
53 l_itemtype := P_itemtype_name; --'ASOAPPRV';
54 l_itemkey := TO_CHAR (
55 P_Object_approval_id
56 ) || 'HED';
57 wf_engine.createprocess (
58 itemtype => l_itemtype,
59 itemkey => l_itemkey,
60 process => 'STARTAPPROVALS'
61 );
62 wf_engine.setitemowner (
63 l_itemtype, --'ASOAPPRV',
64 l_itemkey,
65 P_sender_name
66 );
67 wf_engine.setitemuserkey (
68 itemtype => l_itemtype,
69 itemkey => l_itemkey,
70 userkey => l_itemkey
71 );
72 wf_engine.setitemattrtext (
73 itemtype => l_itemtype,
74 itemkey => l_itemkey,
75 aname => 'REQUESTOR_USERNAME',
76 avalue => P_sender_name
77 );
78 wf_engine.setitemattrnumber (
79 itemtype => l_itemtype,
80 itemkey => l_itemkey,
81 aname => 'APPROVALID',
82 avalue => P_Object_approval_id
83 );
84
85 wf_engine.setitemattrtext (
86 itemtype => l_itemtype,
87 itemkey => l_itemkey,
88 aname => 'NEWPROCESSFLAG',
89 avalue =>'Y'
90 );
91
92
93 l_requestor_display_name := wf_directory.getroledisplayname (
94 P_sender_name
95 );
96 wf_engine.setitemattrtext (
97 itemtype => l_itemtype,
98 itemkey => l_itemkey,
99 aname => 'REQUESTOR_DISPLAYNAME',
100 avalue => l_requestor_display_name
101 );
102 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
103 aso_debug_pub.ADD (
104 'Requester DisplayName is :' || l_requestor_display_name,
105 1,
106 'N'
107 );
108 END IF;
109
110 wf_engine.startprocess (
111 itemtype => l_itemtype,
112 itemkey => l_itemkey
113 );
114
115 ELSE -- approval_id and obj_approval_id are not the same
116
117 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
118 aso_debug_pub.ADD('Skipping the create approval workflow',1,'N');
119 aso_debug_pub.ADD('***** NOTE: APPROVAL WORKFLOW PROCESS HAS NOT BEEN STARTED',1,'N');
120 END IF;
121 END IF;
122
123
124
125 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
126 aso_debug_pub.ADD (
127 'End of Start_ASO_Approvals ',
128 1,
129 'N'
130 );
131 END IF;
132 EXCEPTION
133 WHEN OTHERS
134 THEN
135 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
136 aso_debug_pub.ADD (
137 'Exception in Start_ASO_Approvals Proc SqlCode :' || SQLERRM,
138 1,
139 'N'
140 );
141 END IF;
142 wf_core.CONTEXT (
143 'ASO_APPROVE',
144 'Start_ASO_Approvals',
145 P_Object_approval_id,
146 P_sender_name
147 );
148 RAISE;
149 END start_aso_approvals;
150
151 PROCEDURE submit_approval (
152 itemtype IN VARCHAR2,
153 itemkey IN VARCHAR2,
154 actid IN NUMBER,
155 funcmode IN VARCHAR2,
156 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
157 ) IS
158 next_seq NUMBER := -99;
159 l_itemkey VARCHAR2 (30);
160 l_approval_id NUMBER;
161 l_requestor_name VARCHAR2 (240);
162 l_requestor_displayname VARCHAR2 (240);
163 l_forward_user_name VARCHAR2 (240);
164 l_forward_displayname VARCHAR2 (240);
165
166 CURSOR LIST (
167 c_approval_id NUMBER
168 ) IS
169 SELECT approval_det_id, approver_sequence, approver_person_id,
170 approver_user_id
171 FROM aso_apr_approval_details
172 WHERE object_approval_id = c_approval_id
173 AND approver_status = 'NOSUBMIT'
174 ORDER BY approver_sequence;
175 BEGIN
176 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
177 aso_debug_pub.ADD (
178 'Begin Sumbit_Approval Process',
179 1,
180 'N'
181 );
182 END IF;
183
184 IF funcmode = 'RUN'
185 THEN
186 l_approval_id := wf_engine.getitemattrnumber (
187 itemtype,
188 itemkey,
189 'APPROVALID'
190 );
191 l_requestor_name := wf_engine.getitemattrtext (
192 itemtype,
193 itemkey,
194 'REQUESTOR_USERNAME'
195 );
196 l_requestor_displayname :=
197 wf_engine.getitemattrtext (
198 itemtype,
199 itemkey,
200 'REQUESTOR_DISPLAYNAME'
201 );
202
203
204 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
205 aso_debug_pub.ADD (
206 'Object Approval ID is ' || l_approval_id,
207 1,
208 'N'
209 );
210 aso_debug_pub.ADD (
211 'Requester UserName is ' || l_requestor_name,
212 1,
213 'N'
214 );
215 aso_debug_pub.ADD (
216 'Requester DisplayName is :' || l_requestor_displayname,
217 1,
218 'N'
219 );
220 END IF;
221
222 FOR i IN LIST (
223 l_approval_id
224 )
225 LOOP
226 IF (next_seq = -99)
227 THEN
228 next_seq := i.approver_sequence;
229 ELSIF next_seq = i.approver_sequence
230 THEN
231 NULL;
232 ELSE
233 EXIT;
234 END IF;
235
236 l_itemkey := TO_CHAR (
237 i.approval_det_id
238 ) || 'DET';
239
240 IF (i.approver_person_id IS NULL)
241 THEN
242 wf_directory.getrolename (
243 'FND_USR',
244 i.approver_user_id,
245 l_forward_user_name,
246 l_forward_displayname
247 );
248 ELSE
249 wf_directory.getrolename (
250 'PER',
251 i.approver_person_id,
252 l_forward_user_name,
253 l_forward_displayname
254 );
255 END IF;
256
257 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
258 aso_debug_pub.ADD (
259 'Sumbit_Approval: RECEIVER USERNAME : ' || l_forward_user_name,
260 1,
261 'N'
262 );
263 aso_debug_pub.ADD (
264 'Sumbit_Approval: RECEIVER_DISPLAYNAME : ' || l_forward_displayname,
265 1,
266 'N'
267 );
268 aso_debug_pub.ADD (
269 'Sumbit_Approval: Creating Individual_approval Process for Itemkey'
270 || l_itemkey,
271 1,
272 'N'
273 );
274 END IF;
275 wf_engine.createprocess (
276 itemtype => itemtype,
277 itemkey => l_itemkey,
278 process => 'INDIVIDUAL_APPROVAL'
279 );
280 wf_engine.setitemuserkey (
281 itemtype => itemtype,
282 itemkey => l_itemkey,
283 userkey => l_itemkey
284 );
285 wf_engine.setitemattrnumber (
286 itemtype => itemtype,
287 itemkey => l_itemkey,
288 aname => 'APPROVAL_DET_ID',
289 avalue => i.approval_det_id
290 );
291 wf_engine.setitemattrtext (
292 itemtype => itemtype, --'ASOAPPRV',
293 itemkey => l_itemkey,
294 aname => 'REQUESTOR_DISPLAYNAME',
295 avalue => l_requestor_displayname
296 );
297 wf_engine.setitemattrtext (
298 itemtype => itemtype,
299 itemkey => l_itemkey,
300 aname => 'RECEIVER_USERNAME',
301 avalue => l_forward_user_name
302 );
303
304 wf_engine.setitemattrtext (
305 itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'NEWPROCESSFLAG',
308 avalue =>'Y'
309 );
310
311 l_forward_displayname :=
312 wf_directory.getroledisplayname (
313 l_forward_user_name
314 );
315 wf_engine.setitemattrtext (
316 itemtype => itemtype,
317 itemkey => l_itemkey,
318 aname => 'RECEIVER_DISPLAYNAME',
319 avalue => l_forward_displayname
320 );
321 wf_engine.setitemattrtext (
322 itemtype => itemtype, --'ASOAPPRV',
323 itemkey => l_itemkey,
324 aname => 'REQUESTOR_USERNAME',
325 avalue => l_requestor_name
326 );
327 wf_engine.setitemattrnumber (
328 itemtype => itemtype, --'ASOAPPRV',
329 itemkey => l_itemkey,
330 aname => 'APPROVALID',
331 avalue => l_approval_id
332 );
333 -- Set the mesage name to copy submitter (FYI) message
334
335 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
336 aso_debug_pub.ADD (
337 'Setting the message name to OA_FYI_TO_REQUESTER',
338 1,
339 'N'
340 );
341 END IF;
342 wf_engine.setitemattrtext (
343 itemtype => itemtype, --'ASOAPPRV',
344 itemkey => l_itemkey,
345 aname => 'MESSAGE',
346 avalue => 'OA_FYI_TO_REQUESTER'
347 );
348 -- define the parent child relationship
349
350 wf_engine.setitemparent (
351 itemtype => itemtype,
352 itemkey => l_itemkey,
353 parent_itemtype => itemtype,
354 parent_itemkey => itemkey,
355 parent_context => NULL
356 );
357 wf_engine.startprocess (
358 itemtype => itemtype,
359 itemkey => l_itemkey
360 );
361 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
362 aso_debug_pub.ADD (
363 'Updating the approver status to PEND for object approval id :'
364 || l_approval_id,
365 1,
366 'N'
367 );
368 END IF;
369
370 UPDATE aso_apr_approval_details
371 SET approver_status = 'PEND',
372 date_sent = SYSDATE,
373 last_update_date = SYSDATE,
374 last_updated_by = fnd_global.user_id,
375 last_update_login = fnd_global.user_id
376 WHERE object_approval_id = l_approval_id
377 AND approver_sequence = next_seq;
378 END LOOP;
379
380 resultout := 'COMPLETE' || ':' || wf_engine.eng_null;
381 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
382 aso_debug_pub.ADD (
383 'End Sumbit_Approval Process',
384 1,
385 'N'
386 );
387 END IF;
388 RETURN;
389 END IF;
390
391 IF (funcmode = 'CANCEL')
392 THEN
393 resultout := 'COMPLETE';
394 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
395 aso_debug_pub.ADD (
396 'End of Sumbit_Approval Process',
397 1,
398 'N'
399 );
400 END IF;
401 RETURN;
402 END IF;
403
404 resultout := '';
405 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
406 aso_debug_pub.ADD (
407 'End of Sumbit_Approval Process',
408 1,
409 'N'
410 );
411 END IF;
412 RETURN;
413 EXCEPTION
414 WHEN OTHERS
415 THEN
416 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
417 aso_debug_pub.ADD (
418 'Exception in Submit_Approvals Proc SqlCode :' || SQLERRM,
419 1,
420 'N'
421 );
422 END IF;
423 wf_core.CONTEXT (
424 'ASOAPPRV',
425 'Submit_Approvals',
426 itemtype,
427 itemkey,
428 TO_CHAR (
429 actid
430 ),
431 funcmode
432 );
433 RAISE;
434 END submit_approval;
435
436 PROCEDURE submit_next_batch (
437 itemtype IN VARCHAR2,
438 itemkey IN VARCHAR2,
439 actid IN NUMBER,
440 funcmode IN VARCHAR2,
441 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
442 ) IS
443 l_count NUMBER;
444 l_approval_id NUMBER;
445 l_approval_det_id NUMBER;
446 l_max_seq NUMBER;
447 l_status VARCHAR2 (30);
448 x_msg_count NUMBER;
449 x_msg_data VARCHAR2 (10000);
450 l_new_process_flag VARCHAR2(1);
451
452
453 CURSOR get_pend_sbmt_approvers (
454 c_approval_id NUMBER
455 ) IS
456 SELECT COUNT (
457 *
458 )
459 FROM aso_apr_approval_details
460 WHERE object_approval_id = c_approval_id
461 AND approver_status = 'NOSUBMIT';
462
463 CURSOR get_max_approver_seq (
464 c_approval_id NUMBER
465 ) IS
466 SELECT MAX (
467 approver_sequence
468 )
469 FROM aso_apr_approval_details
470 WHERE object_approval_id = c_approval_id;
471
472 CURSOR get_tout_approvers (
473 c_approval_id NUMBER,
474 c_max_seq NUMBER
475 ) IS
476 SELECT COUNT (
477 *
478 )
479 FROM aso_apr_approval_details
480 WHERE object_approval_id = c_approval_id
481 AND approver_status = 'TOUT'
482 AND approver_sequence = c_max_seq;
483 BEGIN
484 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
485 aso_debug_pub.ADD (
486 'Begin Sumbit_Next_Batch Process',
487 1,
488 'N'
489 );
490 END IF;
491
492 IF funcmode = 'RUN'
493 THEN
494 l_approval_id := wf_engine.getitemattrnumber (
495 itemtype,
496 itemkey,
497 'APPROVALID'
498 );
499 l_approval_det_id := wf_engine.getitemattrnumber (
500 itemtype,
501 itemkey,
502 'APPROVAL_DET_ID'
503 );
504 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
505 aso_debug_pub.ADD (
506 'Approval ID is ' || l_approval_id,
507 1,
508 'N'
509 );
510 aso_debug_pub.ADD (
511 'Approval Detail ID is ' || l_approval_det_id,
512 1,
513 'N'
514 );
515 aso_debug_pub.ADD (
516 'Calling the update approver list procedure ',
517 1,
518 'N'
519 );
520 END IF;
521 -- Update the approver list if any rules have changed
522 aso_apr_wf_pvt.update_approver_list (
523 l_approval_id
524 );
525 OPEN get_pend_sbmt_approvers (
526 l_approval_id
527 );
528 FETCH get_pend_sbmt_approvers INTO l_count;
529 CLOSE get_pend_sbmt_approvers;
530
531 IF l_count > 0
532 THEN
533 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
534 aso_debug_pub.ADD (
535 'Number of approvers who are in pending status are : ' || l_count,
536 1,
537 'N'
538 );
539 END IF;
540 resultout := 'COMPLETE:T';
541 ELSE
542 -- Checking whether Last Approver timed OUT NOCOPY /* file.sql.39 change */ if so treated as rejected
543 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
544 aso_debug_pub.ADD (
545 'NO approvers in pending status',
546 1,
547 'N'
548 );
549 END IF;
550 OPEN get_max_approver_seq (
551 l_approval_id
552 );
553 FETCH get_max_approver_seq INTO l_max_seq;
554 CLOSE get_max_approver_seq;
555 OPEN get_tout_approvers (
556 l_approval_id,
557 l_max_seq
558 );
559 FETCH get_tout_approvers INTO l_count;
560 CLOSE get_tout_approvers;
561 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
562 aso_debug_pub.ADD (
563 'Number of approvers who have timed OUT NOCOPY /* file.sql.39 change */ are : ' || l_count,
564 1,
565 'N'
566 );
567 END IF;
568
569 IF l_count > 0
570 THEN
571 -- Generate new list
572 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
573 aso_debug_pub.ADD (
574 'Calling procedure last approver timeout check ',
575 1,
576 'N'
577 );
578 END IF;
579 aso_apr_wf_pvt.last_approver_timeout_check (
580 l_approval_id
581 );
582 OPEN get_pend_sbmt_approvers (
583 l_approval_id
584 );
585 FETCH get_pend_sbmt_approvers INTO l_count;
586 CLOSE get_pend_sbmt_approvers;
587 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
588 aso_debug_pub.ADD (
589 'Number of approvers who are in pending status are : ' || l_count,
590 1,
591 'N'
592 );
593 END IF;
594
595 IF l_count > 0
596 THEN
597 resultout := 'COMPLETE:T';
598 RETURN;
599 ELSE
600 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
601 aso_debug_pub.ADD (
602 'Seting the status to rejected',
603 1,
604 'N'
605 );
606 END IF;
607 l_status := 'REJ';
608 -- set the message name to that of final rejection message
609
610 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
611 aso_debug_pub.ADD (
612 'Setting message name to OA_REQUEST_REJECTED',
613 1,
614 'N'
615 );
616 END IF;
617 wf_engine.setitemattrtext (
618 itemtype => itemtype,
619 itemkey => itemkey,
620 aname => 'MESSAGE',
621 avalue => 'OA_REQUEST_REJECTED'
622 );
623 END IF;
624 ELSE
625 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
626 aso_debug_pub.ADD (
627 'Seting the status to approved',
628 1,
629 'N'
630 );
631 END IF;
632 l_status := 'APPR';
633 -- set the message name to that of final approval message
634 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
635 aso_debug_pub.ADD (
636 'Setting message name to OA_REQ_APPR_BY_ALL_APPR',
637 1,
638 'N'
639 );
640 END IF;
641 wf_engine.setitemattrtext (
642 itemtype => itemtype,
643 itemkey => itemkey,
644 aname => 'MESSAGE',
645 avalue => 'OA_REQ_APPR_BY_ALL_APPR'
646 );
647 END IF;
648
649 -- Update the approval table to to proper status
650 aso_apr_wf_pvt.update_approval_status (
651 p_update_header_or_detail_flag => 'HEADER',
652 p_object_approval_id => l_approval_id,
653 p_approval_det_id => null,
654 p_status => l_status,
655 note => null);
656
657
658 /* For backward compatibility */
659 BEGIN
660 l_new_process_flag := wf_engine.getitemattrnumber (
661 itemtype,
662 itemkey,
663 'NEWPROCESSFLAG'
664 );
665
666 exception
667 when others then
668 l_new_process_flag := 'N';
669 END;
670
671 IF l_new_process_flag = 'N' THEN
672 -- Calling the procedure to update the quote status
673 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
674 aso_debug_pub.ADD (
675 'Calling update entity procedure ',
676 1,
677 'N'
678 );
679 END IF;
680 aso_apr_wf_pvt.update_entity (
681 itemtype ,
682 itemkey ,
683 actid ,
684 funcmode,
685 resultout
686 );
687 END IF;
688
689 resultout := 'COMPLETE:F';
690 END IF;
691
692 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
693 aso_debug_pub.ADD (
694 'End of Sumbit_Next_Batch Process',
695 1,
696 'N'
697 );
698 END IF;
699 RETURN;
700 END IF;
701
702 IF (funcmode = 'CANCEL')
703 THEN
704 resultout := 'COMPLETE';
705 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
706 aso_debug_pub.ADD (
707 'End of Sumbit_Next_Batch Process',
708 1,
709 'N'
710 );
711 END IF;
712 RETURN;
713 END IF;
714
715 resultout := '';
716 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
717 aso_debug_pub.ADD (
718 'End of Sumbit_Next_Batch Process',
719 1,
720 'N'
721 );
722 END IF;
723 RETURN;
724 EXCEPTION
725 WHEN OTHERS
726 THEN
727 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
728 aso_debug_pub.ADD (
729 'Exception in Submit_next_batch Proc SqlCode :' || SQLERRM,
730 1,
731 'N'
732 );
733 END IF;
734 fnd_msg_pub.count_and_get (
735 p_encoded => 'F',
736 p_count => x_msg_count,
737 p_data => x_msg_data
738 );
739 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
740 aso_debug_pub.ADD (
741 'no. of FND messages :' || x_msg_count,
742 1,
743 'N'
744 );
745 END IF;
746
747 FOR k IN 1 .. x_msg_count
748 LOOP
749 x_msg_data := fnd_msg_pub.get (
750 p_msg_index => k,
751 p_encoded => 'F'
752 );
753 END LOOP;
754
755 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
756 aso_debug_pub.ADD (
757 'Msg Data is' || x_msg_data,
758 1,
759 'N'
760 );
761 END IF;
762 wf_core.CONTEXT (
763 'ASOAPPRV',
764 'SUBMIT_NEXT_BATCH ' || x_msg_data,
765 itemtype,
766 itemkey,
767 TO_CHAR (
768 actid
769 ),
770 funcmode
771 );
772 RAISE;
773 END submit_next_batch;
774
775 PROCEDURE check_rejected (
776 itemtype IN VARCHAR2,
777 itemkey IN VARCHAR2,
778 actid IN NUMBER,
779 funcmode IN VARCHAR2,
780 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
781 ) IS
782 l_count NUMBER;
783 l_approval_id NUMBER;
784 x_msg_count NUMBER;
785 x_msg_data VARCHAR2 (10000);
786 l_new_process_flag VARCHAR2(1);
787
788
789 CURSOR get_rej_approver_count (
790 c_approval_id NUMBER
791 ) IS
792 SELECT COUNT (
793 *
794 )
795 FROM aso_apr_approval_details
796 WHERE object_approval_id = c_approval_id
797 AND approver_status = 'REJ';
798 BEGIN
799 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
800 aso_debug_pub.ADD (
801 'Begin CHECK_REJECTED Procedure ',
802 1,
803 'N'
804 );
805 END IF;
806
807 IF funcmode = 'RUN'
808 THEN
809 l_approval_id := wf_engine.getitemattrnumber (
810 itemtype,
811 itemkey,
812 'APPROVALID'
813 );
814 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
815 aso_debug_pub.ADD (
816 'Approval ID is ' || l_approval_id,
817 1,
818 'N'
819 );
820 END IF;
821 OPEN get_rej_approver_count (
822 l_approval_id
823 );
824 FETCH get_rej_approver_count INTO l_count;
825 CLOSE get_rej_approver_count;
826 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
827 aso_debug_pub.ADD (
828 'Number of approvers who are in rejected status are : ' || l_count,
829 1,
830 'N'
831 );
832 END IF;
833
834 IF l_count > 0
835 THEN
836 -- Calling the procedure to update the quote status
837 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
838 aso_debug_pub.ADD (
839 'Calling update quote procedure with status = REJ and approval id = '
840 || l_approval_id,
841 1,
842 'N'
843 );
844 END IF;
845 -- Set the message name to Final Rejection
846 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
847 aso_debug_pub.ADD (
848 'Setting message name to OA_REQUEST_REJECTED',
849 1,
850 'N'
851 );
852 END IF;
853 wf_engine.setitemattrtext (
854 itemtype => itemtype,
855 itemkey => itemkey,
856 aname => 'MESSAGE',
857 avalue => 'OA_REQUEST_REJECTED'
858 );
859
860
861 aso_apr_wf_pvt.update_approval_status (
862 p_update_header_or_detail_flag => 'HEADER',
863 p_object_approval_id => l_approval_id,
864 p_approval_det_id => null,
865 p_status => 'REJ',
866 note => null);
867
868
869 /* For backward compatibility */
870 BEGIN
871 l_new_process_flag := wf_engine.getitemattrnumber (
872 itemtype,
873 itemkey,
874 'NEWPROCESSFLAG'
875 );
876
877 exception
878 when others then
879 l_new_process_flag := 'N';
880 END;
881
882 IF l_new_process_flag = 'N' THEN
883 -- Calling the procedure to update the quote status
884 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
885 aso_debug_pub.ADD (
886 'Calling update entity procedure ',
887 1,
888 'N'
889 );
890 END IF;
891 aso_apr_wf_pvt.update_entity (
892 itemtype ,
893 itemkey ,
894 actid ,
895 funcmode,
896 resultout
897 );
898 END IF;
899
900 resultout := 'COMPLETE:T';
901 ELSE
902 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
903 aso_debug_pub.ADD (
904 'Setting message name to OA_REQ_APPR_BY_ALL_APPR',
905 1,
906 'N'
907 );
908 END IF;
909 wf_engine.setitemattrtext (
910 itemtype => itemtype,
911 itemkey => itemkey,
912 aname => 'MESSAGE',
913 avalue => 'OA_REQ_APPR_BY_ALL_APPR'
914 );
915 resultout := 'COMPLETE:F';
916 END IF;
917
918 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
919 aso_debug_pub.ADD (
920 'End of CHECK_REJECTED Procedure',
921 1,
922 'N'
923 );
924 END IF;
925 RETURN;
926 END IF;
927
928 IF (funcmode = 'CANCEL')
929 THEN
930 resultout := 'COMPLETE';
931 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
932 aso_debug_pub.ADD (
933 'End of CHECK_REJECTED Procedure',
934 1,
935 'N'
936 );
937 END IF;
938 RETURN;
939 END IF;
940
941 resultout := '';
942 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
943 aso_debug_pub.ADD (
944 'End of CHECK_REJECTED Procedure',
945 1,
946 'N'
947 );
948 END IF;
949 RETURN;
950 EXCEPTION
951 WHEN OTHERS
952 THEN
953 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
954 aso_debug_pub.ADD (
955 'Exception in Check_rejected Procedure SqlCode :' || SQLERRM,
956 1,
957 'N'
958 );
959 END IF;
960 fnd_msg_pub.count_and_get (
961 p_encoded => 'F',
962 p_count => x_msg_count,
963 p_data => x_msg_data
964 );
965 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
966 aso_debug_pub.ADD (
967 'no. of FND messages :' || x_msg_count,
968 1,
969 'N'
970 );
971 END IF;
972
973 FOR k IN 1 .. x_msg_count
974 LOOP
975 x_msg_data := fnd_msg_pub.get (
976 p_msg_index => k,
977 p_encoded => 'F'
978 );
979 END LOOP;
980
981 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
982 aso_debug_pub.ADD (
983 'Msg Data is' || x_msg_data,
984 1,
985 'N'
986 );
987 END IF;
988 wf_core.CONTEXT (
989 'ASOAPPRV',
990 'CHECK_REJECTED ' || x_msg_data,
991 itemtype,
992 itemkey,
993 TO_CHAR (
994 actid
995 ),
996 funcmode
997 );
998 RAISE;
999 END check_rejected;
1000
1001 PROCEDURE approved (
1002 itemtype IN VARCHAR2,
1003 itemkey IN VARCHAR2,
1004 actid IN NUMBER,
1005 funcmode IN VARCHAR2,
1006 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1007 ) IS
1008 l_approval_det_id NUMBER;
1009 l_note VARCHAR2 (4000);
1010 BEGIN
1011 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1012 aso_debug_pub.ADD (
1013 'Begin APPROVED Procedure ',
1014 1,
1015 'N'
1016 );
1017 END IF;
1018
1019 IF funcmode = 'RUN'
1020 THEN
1021 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1022 aso_debug_pub.ADD (
1023 'Seting message name to OA_REQUEST_APPROVED_FYI',
1024 1,
1025 'N'
1026 );
1027 END IF;
1028 wf_engine.setitemattrtext (
1029 itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => 'MESSAGE',
1032 avalue => 'OA_REQUEST_APPROVED_FYI'
1033 );
1034 l_approval_det_id := wf_engine.getitemattrnumber (
1035 itemtype,
1036 itemkey,
1037 'APPROVAL_DET_ID'
1038 );
1039 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1040 aso_debug_pub.ADD (
1041 'Approval detail ID is : ' || l_approval_det_id,
1042 1,
1043 'N'
1044 );
1045 END IF;
1046 l_note := wf_engine.getitemattrtext (
1047 itemtype,
1048 itemkey,
1049 'NOTE'
1050 );
1051 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1052 aso_debug_pub.ADD (
1053 'Approver comments are: ' || SUBSTR (
1054 l_note,
1055 1,
1056 32
1057 ),
1058 1,
1059 'N'
1060 );
1061 aso_debug_pub.ADD (
1062 'Calling the update table procedure setting approver status to APPR',
1063 1,
1064 'N'
1065 );
1066 END IF;
1067 aso_apr_wf_pvt.update_approval_status (
1068 p_update_header_or_detail_flag => 'DETAIL' ,
1069 p_object_approval_id => null,
1070 p_approval_det_id =>l_approval_det_id,
1071 p_status => 'APPR',
1072 note => l_note);
1073
1074
1075 resultout := 'COMPLETE';
1076 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1077 aso_debug_pub.ADD (
1078 'End of APPROVED Procedure',
1079 1,
1080 'N'
1081 );
1082 END IF;
1083 RETURN;
1084 END IF;
1085
1086 IF (funcmode = 'CANCEL')
1087 THEN
1088 resultout := 'COMPLETE';
1089 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1090 aso_debug_pub.ADD (
1091 'End of APPROVED Procedure',
1092 1,
1093 'N'
1094 );
1095 END IF;
1096 RETURN;
1097 END IF;
1098
1099 resultout := '';
1100 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1101 aso_debug_pub.ADD (
1102 'End of APPROVED Procedure',
1103 1,
1104 'N'
1105 );
1106 END IF;
1107 RETURN;
1108 EXCEPTION
1109 WHEN OTHERS
1110 THEN
1111 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1112 aso_debug_pub.ADD (
1113 'Exception in Approved Procedure SqlCode :' || SQLERRM,
1114 1,
1115 'N'
1116 );
1117 END IF;
1118 wf_core.CONTEXT (
1119 'ASOAPPRV',
1120 'APPROVED',
1121 itemtype,
1122 itemkey,
1123 TO_CHAR (
1124 actid
1125 ),
1126 funcmode
1127 );
1128 RAISE;
1129 END approved;
1130
1131 PROCEDURE rejected (
1132 itemtype IN VARCHAR2,
1133 itemkey IN VARCHAR2,
1134 actid IN NUMBER,
1135 funcmode IN VARCHAR2,
1136 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1137 ) IS
1138 l_approval_det_id NUMBER;
1139 l_note VARCHAR2 (4000);
1140 BEGIN
1141 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1142 aso_debug_pub.ADD (
1143 'Start of REJECTED Procedure',
1144 1,
1145 'N'
1146 );
1147 END IF;
1148
1149 IF funcmode = 'RUN'
1150 THEN
1151 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1152 aso_debug_pub.ADD (
1153 'Seting message name to OA_REQUEST_REJECTED_FYI',
1154 1,
1155 'N'
1156 );
1157 END IF;
1158 wf_engine.setitemattrtext (
1159 itemtype => itemtype,
1160 itemkey => itemkey,
1161 aname => 'MESSAGE',
1162 avalue => 'OA_REQUEST_REJECTED_FYI'
1163 );
1164 l_approval_det_id := wf_engine.getitemattrnumber (
1165 itemtype,
1166 itemkey,
1167 'APPROVAL_DET_ID'
1168 );
1169 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1170 aso_debug_pub.ADD (
1171 'Approval detail ID is : ' || l_approval_det_id,
1172 1,
1173 'N'
1174 );
1175 END IF;
1176 l_note := wf_engine.getitemattrtext (
1177 itemtype,
1178 itemkey,
1179 'NOTE'
1180 );
1181 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1182 aso_debug_pub.ADD (
1183 'Approver comments are: ' || SUBSTR (
1184 l_note,
1185 1,
1186 32
1187 ),
1188 1,
1189 'N'
1190 );
1191 aso_debug_pub.ADD (
1192 'Calling the update table procedure setting approver status to REJ',
1193 1,
1194 'N'
1195 );
1196 END IF;
1197 aso_apr_wf_pvt.update_approval_status (
1198 p_update_header_or_detail_flag => 'DETAIL' ,
1199 p_object_approval_id => null,
1200 p_approval_det_id =>l_approval_det_id,
1201 p_status => 'REJ',
1202 note => l_note);
1203
1204
1205
1206 resultout := 'COMPLETE';
1207 RETURN;
1208 END IF;
1209
1210 IF (funcmode = 'CANCEL')
1211 THEN
1212 resultout := 'COMPLETE';
1213 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1214 aso_debug_pub.ADD (
1215 'End of REJECTED Procedure',
1216 1,
1217 'N'
1218 );
1219 END IF;
1220 RETURN;
1221 END IF;
1222
1223 resultout := '';
1224 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1225 aso_debug_pub.ADD (
1226 'End of REJECTED Procedure',
1227 1,
1228 'N'
1229 );
1230 END IF;
1231 RETURN;
1232 EXCEPTION
1233 WHEN OTHERS
1234 THEN
1235 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1236 aso_debug_pub.ADD (
1237 'Exception in Rejected Procedure SqlCode :' || SQLERRM,
1238 1,
1239 'N'
1240 );
1241 END IF;
1242 wf_core.CONTEXT (
1243 'ASOAPPRV',
1244 'REJECTED',
1245 itemtype,
1246 itemkey,
1247 TO_CHAR (
1248 actid
1249 ),
1250 funcmode
1251 );
1252 RAISE;
1253 END rejected;
1254
1255 PROCEDURE timedout (
1256 itemtype IN VARCHAR2,
1257 itemkey IN VARCHAR2,
1258 actid IN NUMBER,
1259 funcmode IN VARCHAR2,
1260 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1261 ) IS
1262 l_approval_det_id NUMBER;
1263 l_note VARCHAR2 (4000);
1264 l_notification_id NUMBER;
1265 BEGIN
1266 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1267 aso_debug_pub.ADD (
1268 'begin TIMEDOUT Procedure',
1269 1,
1270 'N'
1271 );
1272 END IF;
1273
1274 IF funcmode = 'RUN'
1275 THEN
1276 l_notification_id := wf_engine.getitemattrnumber (
1277 itemtype,
1278 itemkey,
1279 'NOTIFICATION_ID'
1280 );
1281 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1282 aso_debug_pub.ADD (
1283 'Cancelling notification for notification ID : ' || l_notification_id,
1284 1,
1285 'N'
1286 );
1287 END IF;
1288 wf_notification.CANCEL (
1289 nid => l_notification_id,
1290 cancel_comment => 'TIMEOUT'
1291 );
1292 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1293 aso_debug_pub.ADD (
1294 'Seting message name to OA_REQUEST_TIMEDOUT_FYI',
1295 1,
1296 'N'
1297 );
1298 END IF;
1299 wf_engine.setitemattrtext (
1300 itemtype => itemtype,
1301 itemkey => itemkey,
1302 aname => 'MESSAGE',
1303 avalue => 'OA_REQUEST_TIMEDOUT_FYI'
1304 );
1305 l_approval_det_id := wf_engine.getitemattrnumber (
1306 itemtype,
1307 itemkey,
1308 'APPROVAL_DET_ID'
1309 );
1310 l_note := wf_engine.getitemattrtext (
1311 itemtype,
1312 itemkey,
1313 'NOTE'
1314 );
1315 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1316 aso_debug_pub.ADD (
1317 'Approver comments are: ' || SUBSTR (
1318 l_note,
1319 1,
1320 32
1321 ),
1322 1,
1323 'N'
1324 );
1325 aso_debug_pub.ADD (
1326 'Calling the update table procedure setting approver status to TOUT',
1327 1,
1328 'N'
1329 );
1330 END IF;
1331 aso_apr_wf_pvt.update_approval_status (
1332 p_update_header_or_detail_flag => 'DETAIL' ,
1333 p_object_approval_id => null,
1334 p_approval_det_id =>l_approval_det_id,
1335 p_status => 'TOUT',
1336 note => l_note);
1337
1338 resultout := 'COMPLETE';
1339 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1340 aso_debug_pub.ADD (
1341 'End of TIMEDOUT Procedure',
1342 1,
1343 'N'
1344 );
1345 END IF;
1346 RETURN;
1347 END IF;
1348
1349 IF (funcmode = 'CANCEL')
1350 THEN
1351 resultout := 'COMPLETE';
1352 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1353 aso_debug_pub.ADD (
1354 'End of TIMEDOUT Procedure',
1355 1,
1356 'N'
1357 );
1358 END IF;
1359 RETURN;
1360 END IF;
1361
1362 resultout := '';
1363 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1364 aso_debug_pub.ADD (
1365 'End of TIMEDOUT Procedure',
1366 1,
1367 'N'
1368 );
1369 END IF;
1370 RETURN;
1371 EXCEPTION
1372 WHEN OTHERS
1373 THEN
1374 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1375 aso_debug_pub.ADD (
1376 'Exception in Approved Procedure SqlCode :' || SQLERRM,
1377 1,
1378 'N'
1379 );
1380 END IF;
1381 wf_core.CONTEXT (
1382 'ASOAPPRV',
1383 'TIMEDOUT',
1384 itemtype,
1385 itemkey,
1386 TO_CHAR (
1387 actid
1388 ),
1389 funcmode
1390 );
1391 RAISE;
1392 END timedout;
1393
1394 PROCEDURE cancelapproval (
1395 approval_id IN NUMBER,
1396 p_itemtype IN VARCHAR2,
1397 p_user_id IN NUMBER
1398 ) IS
1399 l_approval_id NUMBER;
1400 l_itemkey VARCHAR2 (30);
1401 l_itemtype VARCHAR2 (30);
1402 l_requestor_name VARCHAR2 (240);
1403 l_requestor_displayname VARCHAR2 (240);
1404 l_timeout NUMBER;
1405 l_forward_user_name VARCHAR2 (240);
1406 l_forward_displayname VARCHAR2 (240);
1407 x_msg_data VARCHAR2 (10000);
1408 x_msg_count NUMBER;
1409 l_approval_object VARCHAR2 (4000);
1410 l_cancellor_displayname VARCHAR2 (240);
1411 l_cancellor_username VARCHAR2 (240);
1412
1413 CURSOR LIST (
1414 l_approval_id NUMBER
1415 ) IS
1416 SELECT approval_det_id, approver_sequence, approver_person_id,
1417 approver_user_id
1418 FROM aso_apr_approval_details
1419 WHERE object_approval_id = l_approval_id
1420 AND approver_status = 'PEND'
1421 ORDER BY approver_sequence;
1422
1423 CURSOR get_requestor (
1424 l_approval_id NUMBER
1425 ) IS
1426 SELECT aoa.requester_userid, fu.employee_id
1427 FROM aso_apr_obj_approvals aoa, fnd_user fu
1428 WHERE object_approval_id = l_approval_id
1429 AND aoa.requester_userid = fu.user_id
1430 AND SYSDATE BETWEEN fu.start_date AND NVL (
1431 fu.end_date,
1432 SYSDATE
1433 );
1434 CURSOR get_username(l_user_id NUMBER) IS
1435 SELECT user_name
1436 FROM fnd_user
1437 WHERE user_id = l_user_id;
1438
1439 BEGIN
1440 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1441 aso_debug_pub.ADD (
1442 'Begin CancelApproval Procedure',
1443 1,
1444 'N'
1445 );
1446 aso_debug_pub.ADD (
1447 'Aborting process for approval ID :' || approval_id,
1448 1,
1449 'N'
1450 );
1451 END IF;
1452 -- Updating the approval obj table status
1453 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1454 aso_debug_pub.ADD (
1455 'Updating table aso_apr_obj_approvals, status is CAN and approval id :'
1456 || approval_id,
1457 1,
1458 'N'
1459 );
1460 END IF;
1461
1462 aso_apr_wf_pvt.update_approval_status (
1463 p_update_header_or_detail_flag => 'HEADER' ,
1464 p_object_approval_id => approval_id,
1465 p_approval_det_id =>null,
1466 p_status => 'CAN',
1467 note => null);
1468
1469 wf_engine.abortprocess (
1470 itemtype => p_itemtype, --'ASOAPPRV',
1471 itemkey => TO_CHAR (
1472 approval_id
1473 ) || 'HED',
1474 process => '',
1475 result => 'CANCELLED'
1476 );
1477 l_approval_id := approval_id;
1478 l_itemtype := p_itemtype; --'ASOAPPRV';
1479 l_itemkey := TO_CHAR (
1480 approval_id
1481 ) || 'CAN';
1482
1483 FOR i IN get_requestor (
1484 l_approval_id
1485 )
1486 LOOP
1487 IF i.employee_id IS NOT NULL
1488 THEN
1489 wf_directory.getrolename (
1490 'PER',
1491 i.employee_id,
1492 l_requestor_name,
1493 l_requestor_displayname
1494 );
1495 ELSE
1496 wf_directory.getrolename (
1497 'FND_USR',
1498 i.requester_userid,
1499 l_requestor_name,
1500 l_requestor_displayname
1501 );
1502 END IF;
1503
1504 l_requestor_displayname :=
1505 wf_directory.getroledisplayname (
1506 l_requestor_name
1507 );
1508 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1509 aso_debug_pub.ADD (
1510 'Requester Displayname is :' || l_requestor_displayname,
1511 1,
1512 'N'
1513 );
1514 END IF;
1515 END LOOP;
1516
1517 -- Get the display name of the request cancellor
1518
1519 OPEN get_username(p_user_id);
1520 FETCH get_username INTO l_cancellor_username;
1521 CLOSE get_username;
1522
1523 l_cancellor_displayname :=
1524 wf_directory.getroledisplayname (
1525 l_cancellor_username
1526 );
1527 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1528 aso_debug_pub.ADD (
1529 'Cancellor Displayname is :' || l_cancellor_displayname,
1530 1,
1531 'N'
1532 );
1533 END IF;
1534
1535 FOR i IN LIST (
1536 l_approval_id
1537 )
1538 LOOP
1539 IF (i.approver_person_id IS NULL)
1540 THEN
1541 wf_directory.getrolename (
1542 'FND_USR',
1543 i.approver_user_id,
1544 l_forward_user_name,
1545 l_forward_displayname
1546 );
1547 ELSE
1548 wf_directory.getrolename (
1549 'PER',
1550 i.approver_person_id,
1551 l_forward_user_name,
1552 l_forward_displayname
1553 );
1554 END IF;
1555
1556 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1557 aso_debug_pub.ADD (
1558 'Completing Activity Notify Approve Requisition for approval detail ID :'
1559 || i.approval_det_id,
1560 1,
1561 'N'
1562 );
1563 END IF;
1564
1565 -- fix for bug 3130487
1566 BEGIN
1567 wf_engine.completeactivityinternalname (
1568 p_itemtype, --'ASOAPPRV',
1569 TO_CHAR (
1570 i.approval_det_id
1571 ) || 'DET',
1572 'NOTIFY_APPROVE_REQUISITION',
1573 'CANCELLED'
1574 );
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 aso_debug_pub.ADD (
1579 SQLERRM,
1580 1,
1581 'N'
1582 );
1583 END;
1584
1585 -- end fix for bug 3130487
1586
1587 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1588 aso_debug_pub.ADD (
1589 'Aborting process for approval detail ID :' || i.approval_det_id,
1590 1,
1591 'N'
1592 );
1593 END IF;
1594 wf_engine.abortprocess (
1595 itemtype => p_itemtype, --'ASOAPPRV',
1596 itemkey => TO_CHAR (
1597 i.approval_det_id
1598 ) || 'DET',
1599 process => '',
1600 result => 'CANCELLED'
1601 );
1602 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1603 aso_debug_pub.ADD (
1604 'Starting process CANCEL_REQUISITION itemtype is '
1605 || l_itemtype
1606 || ' and itemkey is '
1607 || l_itemkey,
1608 1,
1609 'N'
1610 );
1611 END IF;
1612 wf_engine.createprocess (
1613 itemtype => l_itemtype,
1614 itemkey => l_itemkey,
1615 process => 'CANCEL_REQUISITION'
1616 );
1617 wf_engine.setitemuserkey (
1618 itemtype => l_itemtype,
1619 itemkey => l_itemkey,
1620 userkey => l_itemkey
1621 );
1622 wf_engine.setitemattrnumber (
1623 itemtype => l_itemtype,
1624 itemkey => l_itemkey,
1625 aname => 'APPROVAL_DET_ID',
1626 avalue => i.approval_det_id
1627 );
1628 wf_engine.setitemattrtext (
1629 itemtype => l_itemtype, --'ASOAPPRV',
1630 itemkey => l_itemkey,
1631 aname => 'REQUESTOR_DISPLAYNAME',
1632 avalue => l_requestor_displayname
1633 );
1634 wf_engine.setitemattrtext (
1635 itemtype => l_itemtype, --'ASOAPPRV',
1636 itemkey => l_itemkey,
1637 aname => 'REQUESTOR_USERNAME',
1638 avalue => l_requestor_name
1639 );
1640 wf_engine.setitemattrtext (
1641 itemtype => l_itemtype,
1642 itemkey => l_itemkey,
1643 aname => 'RECEIVER_USERNAME',
1644 avalue => l_forward_user_name
1645 );
1646 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1647 aso_debug_pub.ADD (
1648 'Receiver username is ' || l_forward_user_name,
1649 1,
1650 'N'
1651 );
1652 END IF;
1653 l_forward_displayname :=
1654 wf_directory.getroledisplayname (
1655 l_forward_user_name
1656 );
1657 wf_engine.setitemattrtext (
1658 itemtype => l_itemtype,
1659 itemkey => l_itemkey,
1660 aname => 'RECEIVER_DISPLAYNAME',
1661 avalue => l_forward_displayname
1662 );
1663 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1664 aso_debug_pub.ADD (
1665 'Receiver displayname is ' || l_forward_displayname,
1666 1,
1667 'N'
1668 );
1669 END IF;
1670 wf_engine.setitemattrtext (
1671 itemtype => l_itemtype,
1672 itemkey => l_itemkey,
1673 aname => 'REQUEST_CANCELLOR_USERNAME',
1674 avalue => l_cancellor_username
1675 );
1676 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1677 aso_debug_pub.ADD (
1678 'Cancellor username is ' || l_cancellor_username,
1679 1,
1680 'N'
1681 );
1682 END IF;
1683 wf_engine.setitemattrtext (
1684 itemtype => l_itemtype,
1685 itemkey => l_itemkey,
1686 aname => 'REQUEST_CANCELLOR_DISPLAYNAME',
1687 avalue => l_cancellor_displayname
1688 );
1689 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1690 aso_debug_pub.ADD (
1691 'Cancellor displayname is ' || l_cancellor_displayname,
1692 1,
1693 'N'
1694 );
1695 END IF;
1696 wf_engine.setitemattrnumber (
1697 itemtype => l_itemtype, --'ASOAPPRV',
1698 itemkey => l_itemkey,
1699 aname => 'APPROVALID',
1700 avalue => l_approval_id
1701 );
1702
1703 wf_engine.startprocess (
1704 itemtype => l_itemtype,
1705 itemkey => l_itemkey
1706 );
1707 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1708 aso_debug_pub.ADD (
1709 'Updating detail table setting approver status to cancelled where det id :'
1710 || i.approval_det_id,
1711 1,
1712 'N'
1713 );
1714 END IF;
1715
1716 UPDATE aso_apr_approval_details
1717 SET approver_status = 'CAN',
1718 date_sent = SYSDATE,
1719 last_update_date = SYSDATE,
1720 last_updated_by = fnd_global.user_id,
1721 last_update_login = fnd_global.user_id
1722 WHERE approval_det_id = i.approval_det_id;
1723 END LOOP;
1724
1725 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1726 aso_debug_pub.ADD (
1727 'End of CancelApproval Procedure',
1728 1,
1729 'N'
1730 );
1731 END IF;
1732 EXCEPTION
1733 WHEN OTHERS
1734 THEN
1735 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1736 aso_debug_pub.ADD (
1737 'Exception in CancelApproval SqlCode :' || SQLERRM,
1738 1,
1739 'N'
1740 );
1741 END IF;
1742 fnd_msg_pub.count_and_get (
1743 p_encoded => 'F',
1744 p_count => x_msg_count,
1745 p_data => x_msg_data
1746 );
1747 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1748 aso_debug_pub.ADD (
1749 'no. of FND messages :' || x_msg_count,
1750 1,
1751 'N'
1752 );
1753 END IF;
1754
1755 FOR k IN 1 .. x_msg_count
1756 LOOP
1757 x_msg_data := fnd_msg_pub.get (
1758 p_msg_index => k,
1759 p_encoded => 'F'
1760 );
1761 END LOOP;
1762
1763 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1764 aso_debug_pub.ADD (
1765 'Msg Data is' || x_msg_data,
1766 1,
1767 'N'
1768 );
1769 END IF;
1770 wf_core.CONTEXT (
1771 'ASOAPPRV',
1772 'CancelApproval ' || x_msg_data,
1773 approval_id
1774 );
1775 RAISE;
1776 END cancelapproval;
1777
1778 PROCEDURE send_notification (
1779 itemtype IN VARCHAR2,
1780 itemkey IN VARCHAR2,
1781 actid IN NUMBER,
1782 funcmode IN VARCHAR2,
1783 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1784 ) IS
1785 l_requestor_display_name VARCHAR2 (240);
1786 l_message VARCHAR2 (240);
1787 l_notification_id NUMBER := 0;
1788 l_first_colon_pos NUMBER := 0;
1789 l_second_colon_pos NUMBER := 0;
1790 v_new_resultout VARCHAR2 (100);
1791 l_notifenabled VARCHAR2 (3) := 'Y';
1792 l_orgid NUMBER := NULL;
1793 l_approval_id NUMBER;
1794 x_return_status VARCHAR2 (240);
1795 x_msg_count NUMBER;
1796 x_msg_data VARCHAR2 (240);
1797 l_msgenabled VARCHAR2 (3) := 'Y';
1798 l_notifname VARCHAR2 (240);
1799 get_message_error EXCEPTION;
1800 notif_not_enabled_error EXCEPTION;
1801
1802 BEGIN
1803 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1804 aso_debug_pub.ADD (
1805 'Start of SEND NOTIFICATION Procedure',
1806 1,
1807 'N'
1808 );
1809 aso_debug_pub.ADD (
1810 'actid is ' || TO_CHAR (
1811 actid
1812 ),
1813 1,
1814 'N'
1815 );
1816 END IF;
1817
1818 IF funcmode = 'RUN'
1819 THEN
1820 -- Please note that the notification event name is same as message name
1821 l_notifname := wf_engine.getitemattrtext (
1822 itemtype,
1823 itemkey,
1824 'MESSAGE'
1825 );
1826 l_message := l_notifname;
1827 l_notifname := 'ASO_' || l_notifname;
1828 -- Check if the notification is enabled for that event
1829
1830 l_notifenabled := ibe_wf_notif_setup_pvt.check_notif_enabled (
1831 l_notifname
1832 );
1833 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1834 aso_debug_pub.ADD (
1835 'Notification Name: ' || l_notifname || ' Enabled: ' || l_notifenabled,
1836 1,
1837 'N'
1838 );
1839 END IF;
1840
1841 IF l_notifenabled = 'Y'
1842 THEN
1843 -- Get the approval id
1844 l_approval_id := wf_engine.getitemattrnumber (
1845 itemtype,
1846 itemkey,
1847 'APPROVALID'
1848 );
1849 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1850 aso_debug_pub.ADD (
1851 'Approval ID :' || l_approval_id,
1852 1,
1853 'N'
1854 );
1855 END IF;
1856 -- get the org id
1857 /*OPEN get_org_id (
1858 l_approval_id
1859 );
1860 FETCH get_org_id INTO l_orgid;
1861 CLOSE get_org_id; */
1862
1863 l_orgid := wf_engine.getitemattrnumber (
1864 itemtype,
1865 itemkey,
1866 'ORGID'
1867 );
1868
1869 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1870 aso_debug_pub.ADD (
1871 'Org Id is ' || l_orgid,
1872 1,
1873 'N'
1874 );
1875 END IF;
1876 -- Retreive the message name for that event
1877
1878 x_return_status := fnd_api.g_ret_sts_success;
1879
1880 ibe_wf_msg_mapping_pvt.retrieve_msg_mapping (
1881 p_org_id => l_orgid,
1882 p_msite_id => NULL,
1883 p_user_type => NULL,
1884 p_notif_name => l_notifname,
1885 x_enabled_flag => l_msgenabled,
1886 x_wf_message_name => l_message,
1887 x_return_status => x_return_status,
1888 x_msg_data => x_msg_data,
1889 x_msg_count => x_msg_count
1890 );
1891 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1892 aso_debug_pub.ADD (
1893 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled,
1894 1,
1895 'N'
1896 );
1897 END IF;
1898
1899 -- bug 3295179
1900 IF ( (x_return_status <> fnd_api.g_ret_sts_success) OR (l_message IS NULL) )
1901 --IF ((x_msg_count > 0)
1902 -- OR (l_message IS NULL)
1903 -- )
1904 THEN
1905 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1906 aso_debug_pub.ADD (
1907 'Message count is :' || x_msg_count,
1908 1,
1909 'N'
1910 );
1911 END IF;
1912 RAISE get_message_error;
1913 END IF;
1914
1915 IF l_msgenabled = 'Y'
1916 THEN
1917 -- Set the message name
1918 wf_engine.setitemattrtext (
1919 itemtype => itemtype,
1920 itemkey => itemkey,
1921 aname => 'MESSAGE',
1922 avalue => l_message
1923 );
1924 END IF;
1925 END IF;
1926
1927 IF (l_msgenabled <> 'Y')
1928 OR (l_notifenabled <> 'Y')
1929 THEN
1930 -- if the event is not enabled or message is not enabled
1931 -- if the notification requires a response
1932 IF ((l_message LIKE 'REQUEST_APPROVAL%') OR (l_message LIKE 'OA_REQUEST_APPROVAL%'))
1933 THEN
1934 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1935 aso_debug_pub.ADD (
1936 'Notification not enabled for REQUEST_APPROVAL',
1937 1,
1938 'N'
1939 );
1940 END IF;
1941 RAISE notif_not_enabled_error;
1942 ELSE
1943 -- if the notification is a FYI
1944 -- Check for FYI Message and if so make the attribute as Approval Message
1945 IF ((l_message LIKE 'FYI_TO_REQUESTER%') OR (l_message LIKE 'OA_FYI_TO_REQUESTER%'))
1946 THEN
1947 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1948 aso_debug_pub.ADD (
1949 'Setting the message name to OA_REQUEST_APPROVAL',
1950 1,
1951 'N'
1952 );
1953 END IF;
1954 wf_engine.setitemattrtext (
1955 itemtype => itemtype,
1956 itemkey => itemkey,
1957 aname => 'MESSAGE',
1958 avalue => 'OA_REQUEST_APPROVAL'
1959 );
1960 END IF;
1961
1962 resultout := 'COMPLETE';
1963 RETURN;
1964 END IF;
1965 END IF;
1966
1967 l_message := wf_engine.getitemattrtext (
1968 itemtype,
1969 itemkey,
1970 'MESSAGE'
1971 );
1972 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1973 aso_debug_pub.ADD (
1974 'Message is ' || l_message,
1975 1,
1976 'N'
1977 );
1978 END IF;
1979
1980 IF ((l_message LIKE 'REQUEST_APPROVAL_REMINDER%') OR (l_message LIKE 'OA_REQUEST_APPROVAL_REM%'))
1981 THEN
1982 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1983 aso_debug_pub.ADD (
1984 'As Message is REQUEST_APPROVAL_REMINDER will cancel original approval notif',
1985 1,
1986 'N'
1987 );
1988 END IF;
1989 l_notification_id := wf_engine.getitemattrnumber (
1990 itemtype,
1991 itemkey,
1992 'NOTIFICATION_ID'
1993 );
1994 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1995 aso_debug_pub.ADD (
1996 'Cancelling notification for notification ID :' || l_notification_id,
1997 1,
1998 'N'
1999 );
2000 END IF;
2001 wf_notification.CANCEL (
2002 nid => l_notification_id,
2003 cancel_comment => 'TIMEOUT'
2004 );
2005 END IF;
2006
2007 wf_standard.notify (
2008 itemtype,
2009 itemkey,
2010 actid,
2011 funcmode,
2012 resultout
2013 );
2014 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2015 aso_debug_pub.ADD (
2016 'Calling the standard notify process for Itemtype :'
2017 || itemtype
2018 || ' and itemkey :'
2019 || itemkey,
2020 1,
2021 'N'
2022 );
2023 END IF;
2024 -- Store the Notification id for timeout
2025
2026 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2027 aso_debug_pub.ADD (
2028 'Storing the notification id so as to be used in timeout case ',
2029 1,
2030 'N'
2031 );
2032 END IF;
2033
2034 IF (resultout IS NOT NULL)
2035 THEN
2036 l_first_colon_pos := INSTR (
2037 resultout,
2038 ':',
2039 1,
2040 1
2041 );
2042 l_second_colon_pos := INSTR (
2043 resultout,
2044 ':',
2045 1,
2046 2
2047 );
2048
2049 IF ((l_first_colon_pos <> 0)
2050 AND (l_second_colon_pos <> 0)
2051 )
2052 THEN
2053 l_notification_id := TO_NUMBER (
2054 SUBSTR (
2055 resultout,
2056 l_first_colon_pos + 1,
2057 l_second_colon_pos - l_first_colon_pos - 1
2058 )
2059 );
2060 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2061 aso_debug_pub.ADD (
2062 'Notification id is ' || l_notification_id,
2063 1,
2064 'N'
2065 );
2066 END IF;
2067 wf_engine.setitemattrnumber (
2068 itemtype => itemtype,
2069 itemkey => itemkey,
2070 aname => 'NOTIFICATION_ID',
2071 avalue => l_notification_id
2072 );
2073 END IF;
2074 END IF;
2075
2076 -- Check for FYI Message and if so make the attribute as Approval Message
2077 IF ((l_message LIKE 'FYI_TO_REQUESTER%') or (l_message LIKE 'OA_FYI_TO_REQUESTER%'))
2078 THEN
2079 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2080 aso_debug_pub.ADD (
2081 'Setting the message name to OA_REQUEST_APPROVAL',
2082 1,
2083 'N'
2084 );
2085 END IF;
2086 wf_engine.setitemattrtext (
2087 itemtype => itemtype,
2088 itemkey => itemkey,
2089 aname => 'MESSAGE',
2090 avalue => 'OA_REQUEST_APPROVAL'
2091 );
2092 ELSIF ((l_message LIKE 'REQUEST_APPROVAL%') or (l_message LIKE 'OA_REQUEST_APPROVAL%'))
2093 THEN
2094 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2095 aso_debug_pub.ADD (
2096 'Setting the message name to OA_REQUEST_APPROVAL_REM',
2097 1,
2098 'N'
2099 );
2100 END IF;
2101 wf_engine.setitemattrtext (
2102 itemtype => itemtype,
2103 itemkey => itemkey,
2104 aname => 'MESSAGE',
2105 avalue => 'OA_REQUEST_APPROVAL_REM'
2106 );
2107 END IF;
2108
2109 -- resultout := 'COMPLETE';
2110 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2111 aso_debug_pub.ADD (
2112 'End of SEND NOTIFICATION Procedure ',
2113 1,
2114 'N'
2115 );
2116 END IF;
2117 RETURN;
2118 END IF;
2119
2120 IF (funcmode = 'CANCEL')
2121 THEN
2122 resultout := 'COMPLETE';
2123 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2124 aso_debug_pub.ADD (
2125 'End of SEND NOTIFICATION Procedure ',
2126 1,
2127 'N'
2128 );
2129 END IF;
2130 RETURN;
2131 END IF;
2132
2133 resultout := '';
2134 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2135 aso_debug_pub.ADD (
2136 'End of SEND NOTIFICATION Procedure ',
2137 1,
2138 'N'
2139 );
2140 END IF;
2141 RETURN;
2142 EXCEPTION
2143 WHEN notif_not_enabled_error
2144 THEN
2145 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2146 aso_debug_pub.ADD (
2147 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2148 1,
2149 'N'
2150 );
2151 END IF;
2152 wf_core.CONTEXT (
2153 'ASOAPPRV',
2154 'SEND_NOTIFICATION',
2155 'Mandatory Notification Name: ' || l_notifname || ' is not Enabled '
2156 );
2157 RAISE;
2158 WHEN get_message_error
2159 THEN
2160 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2161 aso_debug_pub.ADD (
2162 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2163 1,
2164 'N'
2165 );
2166 END IF;
2167 wf_core.CONTEXT (
2168 'ASOAPPRV',
2169 'SEND_NOTIFICATION',
2170 'Error in retreiving Notification Message',
2171 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled
2172 );
2173 RAISE;
2174 WHEN OTHERS
2175 THEN
2176 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2177 aso_debug_pub.ADD (
2178 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2179 1,
2180 'N'
2181 );
2182 END IF;
2183 wf_core.CONTEXT (
2184 'ASOAPPRV',
2185 'SEND_NOTIFICATION',
2186 itemtype,
2187 itemkey,
2188 TO_CHAR (
2189 actid
2190 ),
2191 funcmode
2192 );
2193 RAISE;
2194 END send_notification;
2195
2196 PROCEDURE update_entity (
2197 itemtype IN VARCHAR2,
2198 itemkey IN VARCHAR2,
2199 actid IN NUMBER,
2200 funcmode IN VARCHAR2,
2201 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2202 ) IS
2203 l_contract_approval_level VARCHAR2 (240);
2204 l_quote_header_rec aso_quote_pub.qte_header_rec_type;
2205 l_control_rec aso_quote_pub.control_rec_type;
2206 x_qte_header_rec aso_quote_pub.qte_header_rec_type;
2207 x_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
2208 x_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
2209 x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2210 x_hd_payment_tbl aso_quote_pub.payment_tbl_type;
2211 x_hd_shipment_tbl aso_quote_pub.shipment_tbl_type;
2212 x_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2213 x_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2214 x_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
2215 x_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
2216 x_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
2217 x_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
2218 x_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
2219 x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2220 x_ln_payment_tbl aso_quote_pub.payment_tbl_type;
2221 x_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
2222 x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2223 x_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2224 x_return_status VARCHAR2 (240);
2225 x_msg_count NUMBER;
2226 x_msg_data VARCHAR2 (240);
2227 l_user_id Number;
2228 l_person_id Number;
2229 l_object_approval_id NUMBER;
2230 l_status varchar2(20);
2231 l_notifname varchar2(240);
2232 CURSOR get_quote_header_id (
2233 c_object_approval_id NUMBER
2234 ) IS
2235 SELECT object_id
2236 FROM aso_apr_obj_approvals
2237 WHERE object_approval_id = c_object_approval_id;
2238
2239 CURSOR check_contract_enabled (
2240 c_quote_header_id NUMBER
2241 ) IS
2242 SELECT contract_approval_level
2243 FROM aso_quote_headers_all
2244 WHERE quote_header_id = c_quote_header_id;
2245
2246 CURSOR get_latest_date (
2247 c_quote_header_id NUMBER
2248 ) IS
2249 SELECT last_update_date,org_id
2250 FROM aso_quote_headers_all
2251 WHERE quote_header_id = c_quote_header_id;
2252
2253 CURSOR get_quote_status_id (
2254 v_status VARCHAR2
2255 ) IS
2256 SELECT quote_status_id
2257 FROM aso_quote_statuses_b
2258 WHERE status_code = v_status;
2259
2260 CURSOR get_last_approver (
2261 c_object_approval_id NUMBER
2262 ) IS
2263 SELECT approver_person_id
2264 FROM aso_apr_approval_details
2265 WHERE object_approval_id = c_object_approval_id
2266 AND approver_sequence = (select max(approver_sequence)
2267 FROM aso_apr_approval_details
2268 WHERE object_approval_id = c_object_approval_id);
2269
2270 CURSOR get_user_id ( c_employee_id NUMBER)
2271 IS
2272 SELECT user_id
2273 FROM fnd_user
2274 WHERE employee_id = c_employee_id;
2275
2276
2277
2278 CURSOR get_rejected_approver (c_object_approval_id NUMBER) IS
2279 SELECT approver_person_id
2280 FROM aso_apr_approval_details
2281 WHERE object_approval_id = c_object_approval_id
2282 AND approver_status = 'REJ';
2283
2284 BEGIN
2285 -- Initialize the quote header record
2286
2287 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2288 aso_debug_pub.ADD (
2289 'Begin Update Entity Procedure ',
2290 1,
2291 'N'
2292 );
2293 END IF;
2294
2295 IF funcmode = 'RUN' THEN
2296
2297 l_object_approval_id := wf_engine.getitemattrnumber (
2298 itemtype,
2299 itemkey,
2300 'APPROVALID'
2301 );
2302
2303 l_notifname := wf_engine.getitemattrtext (
2304 itemtype,
2305 itemkey,
2306 'MESSAGE'
2307 );
2308 IF l_notifname = 'OA_REQUEST_REJECTED' then
2309 l_status := 'REJ';
2310 ELSIF l_notifname = 'OA_REQ_APPR_BY_ALL_APPR' THEN
2311 l_status := 'APPR';
2312 END IF;
2313
2314 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2315 aso_debug_pub.ADD (
2316 'Object approval ID :' || l_object_approval_id,
2317 1,
2318 'N'
2319 );
2320 aso_debug_pub.ADD (
2321 'Status :' || l_status,
2322 1,
2323 'N'
2324 );
2325 END IF;
2326 --g_user_id := fnd_global.user_id;
2327 -- fix for bug 3929409
2328 IF l_status = 'APPR' THEN
2329 OPEN get_last_approver (
2330 l_object_approval_id
2331 );
2332 FETCH get_last_approver INTO l_person_id;
2333 CLOSE get_last_approver;
2334
2335 OPEN get_user_id(l_person_id);
2336 FETCH get_user_id INTO l_user_id;
2337 CLOSE get_user_id;
2338
2339 g_user_id := l_user_id;
2340 ELSIF l_status = 'REJ' THEN
2341 OPEN get_rejected_approver(l_object_approval_id);
2342 FETCH get_rejected_approver into l_person_id;
2343 CLOSE get_rejected_approver;
2344
2345 OPEN get_user_id(l_person_id);
2346 FETCH get_user_id INTO l_user_id;
2347 CLOSE get_user_id;
2348 g_user_id := l_user_id;
2349
2350 ELSE
2351 g_user_id := fnd_global.user_id;
2352
2353 END IF;
2354
2355 FND_GLOBAL.APPS_INITIALIZE(g_user_id,0,0,0);
2356
2357
2358 l_quote_header_rec := aso_quote_pub.g_miss_qte_header_rec;
2359
2360 OPEN get_quote_header_id (
2361 l_object_approval_id
2362 );
2363 FETCH get_quote_header_id INTO l_quote_header_rec.quote_header_id;
2364 CLOSE get_quote_header_id;
2365 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2366 aso_debug_pub.ADD (
2367 'Quote Header ID :' || l_quote_header_rec.quote_header_id,
2368 1,
2369 'N'
2370 );
2371 END IF;
2372 -- Check if contract is enabled for the quote
2373 OPEN check_contract_enabled (
2374 l_quote_header_rec.quote_header_id
2375 );
2376 FETCH check_contract_enabled INTO l_contract_approval_level;
2377 CLOSE check_contract_enabled;
2378
2379 -- setting the quote status id
2380 IF l_status = 'APPR'
2381 THEN
2382 -- if contract is enabled, set status to contract enables
2383 IF l_contract_approval_level IS NOT NULL
2384 THEN
2385 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2386 aso_debug_pub.ADD (
2387 'Setting Quote Status to CONTRACT REQUIRED',
2388 1,
2389 'N'
2390 );
2391 END IF;
2392 OPEN get_quote_status_id (
2393 'CONTRACT REQUIRED'
2394 );
2395 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2396 CLOSE get_quote_status_id;
2397 ELSE
2398 -- otherwise set status to APPROVED
2399 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2400 aso_debug_pub.ADD (
2401 'Setting Quote Status to APPROVED',
2402 1,
2403 'N'
2404 );
2405 END IF;
2406 OPEN get_quote_status_id (
2407 'APPROVED'
2408 );
2409 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2410 CLOSE get_quote_status_id;
2411 END IF;
2412 ELSIF l_status = 'REJ'
2413 THEN
2414 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2415 aso_debug_pub.ADD (
2416 'Setting Quote Status to REJECTED',
2417 1,
2418 'N'
2419 );
2420 END IF;
2421 OPEN get_quote_status_id (
2422 'APPROVAL REJECTED'
2423 );
2424 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2425 CLOSE get_quote_status_id;
2426 ELSIF l_status = 'CAN'
2427 THEN
2428 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2429 aso_debug_pub.ADD (
2430 'Setting Quote Status to APPROVAL CANCELLED ',
2431 1,
2432 'N'
2433 );
2434 END IF;
2435 OPEN get_quote_status_id (
2436 'APPROVAL CANCELED'
2437 );
2438 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2439 CLOSE get_quote_status_id;
2440 ELSIF l_status = 'PEND'
2441 THEN
2442 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2443 aso_debug_pub.ADD (
2444 'Setting Quote Status to APPROVAL PENDING',
2445 1,
2446 'N'
2447 );
2448 END IF;
2449 OPEN get_quote_status_id (
2450 'APPROVAL PENDING'
2451 );
2452 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2453 CLOSE get_quote_status_id;
2454 END IF;
2455
2456 OPEN get_latest_date (
2457 l_quote_header_rec.quote_header_id
2458 );
2459 FETCH get_latest_date INTO l_quote_header_rec.last_update_date, l_quote_header_rec.org_id;
2460 CLOSE get_latest_date;
2461 -- Setting the auto version flag to true
2462 l_control_rec.auto_version_flag := fnd_api.g_true;
2463
2464 -- set the org context , see bug 4731684
2465 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2466 aso_debug_pub.ADD (
2467 'Before calling update quote: Setting the single org context to org_id: '|| l_quote_header_rec.org_id,
2468 1,
2469 'N'
2470 );
2471 END IF;
2472 mo_global.set_policy_context('S', l_quote_header_rec.org_id);
2473
2474
2475 -- Update the quote status by calling the update_quote API
2476
2477 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2478 aso_debug_pub.ADD (
2479 'Calling the update quote API in ASO_UPDATE_QUOTE_PUB package ',
2480 1,
2481 'N'
2482 );
2483 END IF;
2484 aso_quote_pub.update_quote (
2485 p_api_version_number => 1.0,
2486 p_init_msg_list => fnd_api.g_false,
2487 p_commit => fnd_api.g_false,
2488 p_control_rec => l_control_rec,
2489 p_qte_header_rec => l_quote_header_rec,
2490 p_hd_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2491 p_hd_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2492 p_hd_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2493 p_hd_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2494 p_hd_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2495 p_qte_line_tbl => aso_quote_pub.g_miss_qte_line_tbl,
2496 p_qte_line_dtl_tbl => aso_quote_pub.g_miss_qte_line_dtl_tbl,
2497 p_line_attr_ext_tbl => aso_quote_pub.g_miss_line_attribs_ext_tbl,
2498 p_line_rltship_tbl => aso_quote_pub.g_miss_line_rltship_tbl,
2499 p_price_adjustment_tbl => aso_quote_pub.g_miss_price_adj_tbl,
2500 p_price_adj_attr_tbl => aso_quote_pub.g_miss_price_adj_attr_tbl,
2501 p_price_adj_rltship_tbl => aso_quote_pub.g_miss_price_adj_rltship_tbl,
2502 p_ln_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2503 p_ln_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2504 p_ln_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2505 p_ln_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2506 p_ln_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2507 x_qte_header_rec => x_qte_header_rec,
2508 x_qte_line_tbl => x_qte_line_tbl,
2509 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
2510 x_hd_price_attributes_tbl => x_hd_price_attributes_tbl,
2511 x_hd_payment_tbl => x_hd_payment_tbl,
2512 x_hd_shipment_tbl => x_hd_shipment_tbl,
2513 x_hd_freight_charge_tbl => x_hd_freight_charge_tbl,
2514 x_hd_tax_detail_tbl => x_hd_tax_detail_tbl,
2515 x_line_attr_ext_tbl => x_line_attr_ext_tbl,
2516 x_line_rltship_tbl => x_line_rltship_tbl,
2517 x_price_adjustment_tbl => x_price_adjustment_tbl,
2518 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
2519 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl,
2520 x_ln_price_attributes_tbl => x_ln_price_attributes_tbl,
2521 x_ln_payment_tbl => x_ln_payment_tbl,
2522 x_ln_shipment_tbl => x_ln_shipment_tbl,
2523 x_ln_freight_charge_tbl => x_ln_freight_charge_tbl,
2524 x_ln_tax_detail_tbl => x_ln_tax_detail_tbl,
2525 x_return_status => x_return_status,
2526 x_msg_count => x_msg_count,
2527 x_msg_data => x_msg_data
2528 );
2529
2530 IF x_return_status <> fnd_api.g_ret_sts_success
2531 THEN
2532 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2533 aso_debug_pub.ADD (
2534 'Return Status from update quote API is :' || x_return_status,
2535 1,
2536 'N'
2537 );
2538 END IF;
2539
2540
2541 /*bug 3500380 */
2542 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2544 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2545 RAISE FND_API.G_EXC_ERROR;
2546 END IF;
2547 --RAISE update_quote_exception;
2548
2549
2550 END IF;
2551
2552 -- set the org context , see bug 4731684
2553 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2554 aso_debug_pub.ADD (
2555 'After calling update quote Setting the org context to multi-org org_id:null',
2556 1,
2557 'N'
2558 );
2559 END IF;
2560
2561 mo_global.set_policy_context('M',null);
2562
2563
2564 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2565 aso_debug_pub.ADD (
2566 'End update entity procedure ',
2567 1,
2568 'N'
2569 );
2570 END IF;
2571
2572 resultout := 'COMPLETE:T';
2573 END IF;
2574 EXCEPTION
2575
2576 /*bug 3500380 */
2577 WHEN FND_API.G_EXC_ERROR THEN
2578 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2579 aso_debug_pub.ADD (
2580 'Exception in update entity ',
2581 1,
2582 'N'
2583 );
2584 END IF;
2585 wf_core.CONTEXT (
2586 'ASOAPPRV',
2587 'update_entity',
2588 itemtype,
2589 itemkey,
2590 TO_CHAR(actid),
2591 funcmode);
2592 RAISE;
2593
2594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2595 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2596 aso_debug_pub.ADD (
2597 'Exception in update entity ',
2598 1,
2599 'N'
2600 );
2601 END IF;
2602 wf_core.CONTEXT (
2603 'ASOAPPRV',
2604 'update_entity',
2605 itemtype,
2606 itemkey,
2607 TO_CHAR(actid),
2608 funcmode);
2609 RAISE;
2610
2611 WHEN OTHERS THEN
2612 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2613 aso_debug_pub.ADD (
2614 'Exception in update entity ',
2615 1,
2616 'N'
2617 );
2618 END IF;
2619 wf_core.CONTEXT (
2620 'ASOAPPRV',
2621 'update_entity',
2622 itemtype,
2623 itemkey,
2624 TO_CHAR(actid),
2625 funcmode);
2626 RAISE;
2627
2628 END update_entity;
2629
2630 PROCEDURE update_approver_list (
2631 p_object_approval_id IN NUMBER
2632 ) IS
2633 l_approvers_changed_flag VARCHAR2 (1) := 'N';
2634 l_last_approved_approver NUMBER := 0;
2635 x_approvers_list aso_apr_pub.approvers_list_tbl_type;
2636 x_rules_list aso_apr_pub.rules_list_tbl_type;
2637 l_return_status VARCHAR2 (20);
2638 l_msg_count NUMBER;
2639 l_msg_data VARCHAR2 (2000);
2640 l_object_id NUMBER;
2641 l_object_type VARCHAR2 (240);
2642 l_application_id NUMBER;
2643 l_approver_status VARCHAR2 (30);
2644 l_approval_det_id NUMBER;
2645 l_approver_person_id NUMBER;
2646 l_approver_user_id NUMBER;
2647 l_approver_count NUMBER;
2648 j INTEGER;
2649 p_rule_id NUMBER;
2650 get_all_approvers_failed EXCEPTION;
2651 l_oam_rule_id NUMBER;
2652 l_rule_count NUMBER;
2653 l_rules_changed_flag VARCHAR2 (1) := 'N';
2654
2655 TYPE existing_approvers_tbl_type IS TABLE OF aso_apr_approval_details%ROWTYPE
2656 INDEX BY BINARY_INTEGER;
2657
2658 l_new_approvers_tbl existing_approvers_tbl_type;
2659 l_employee_id NUMBER;
2660
2661 CURSOR get_object_id (
2662 c_object_approval_id NUMBER
2663 ) IS
2664 SELECT DISTINCT object_id, object_type, application_id
2665 FROM aso_apr_obj_approvals
2666 WHERE object_approval_id = c_object_approval_id;
2667
2668 CURSOR get_existing_approvers (
2669 c_object_approval_id NUMBER
2670 ) IS
2671 SELECT approval_det_id, approver_person_id, approver_user_id,
2672 approver_status
2673 FROM aso_apr_approval_details
2674 WHERE object_approval_id = c_object_approval_id
2675 ORDER BY approver_sequence;
2676
2677 CURSOR get_approver_count (
2678 c_object_approval_id NUMBER
2679 ) IS
2680 SELECT COUNT (
2681 *
2682 )
2683 FROM aso_apr_approval_details
2684 WHERE object_approval_id = c_object_approval_id;
2685
2686 CURSOR get_old_approvers (
2687 c_object_approval_id NUMBER,
2688 c_approval_det_id NUMBER
2689 ) IS
2690 SELECT *
2691 FROM aso_apr_approval_details
2692 WHERE object_approval_id = c_object_approval_id
2693 AND approval_det_id = c_approval_det_id;
2694
2695 CURSOR get_existing_rules (
2696 c_object_approval_id NUMBER
2697 ) IS
2698 SELECT oam_rule_id
2699 FROM aso_apr_rules
2700 WHERE object_approval_id = c_object_approval_id
2701 ORDER BY rule_id;
2702
2703 CURSOR get_rule_count (
2704 c_object_approval_id NUMBER
2705 ) IS
2706 SELECT COUNT (
2707 *
2708 )
2709 FROM aso_apr_rules
2710 WHERE object_approval_id = c_object_approval_id;
2711
2712 cursor get_employee_id(l_user_id NUMBER) IS
2713 select employee_id
2714 from fnd_user
2715 where user_id = l_user_id;
2716
2717
2718 BEGIN
2719 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2720 aso_debug_pub.ADD (
2721 'Begin update approver list procedure ',
2722 1,
2723 'N'
2724 );
2725 END IF;
2726 g_user_id := fnd_global.user_id;
2727 -- get the latest list of approvers
2728 OPEN get_object_id (
2729 p_object_approval_id
2730 );
2731 FETCH get_object_id INTO l_object_id, l_object_type, l_application_id;
2732 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2733 aso_debug_pub.ADD (
2734 'Object ID :' || l_object_id,
2735 1,
2736 'N'
2737 );
2738 aso_debug_pub.ADD (
2739 'Object Type :' || l_object_type,
2740 1,
2741 'N'
2742 );
2743 aso_debug_pub.ADD (
2744 'application id :' || l_application_id,
2745 1,
2746 'N'
2747 );
2748 END IF;
2749 -- calling the get all approvers to get the latest list of approvers
2750 -- please note that we are passing the clear transaction flag as false
2751 -- this is to ensure that get all approvers does not clear transactions
2752
2753 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2754 aso_debug_pub.ADD (
2755 'Calling get_all_approvers',
2756 1,
2757 'N'
2758 );
2759 END IF;
2760 aso_apr_int.get_all_approvers (
2761 1.0,
2762 fnd_api.g_false,
2763 fnd_api.g_false,
2764 l_object_id,
2765 l_object_type,
2766 l_application_id, ---p_application_id,
2767 fnd_api.g_false, --- p_clear_transaction_flag
2768 l_return_status,
2769 l_msg_count,
2770 l_msg_data,
2771 x_approvers_list,
2772 x_rules_list
2773 );
2774 CLOSE get_object_id;
2775
2776 -- Checking to find OUT NOCOPY /* file.sql.39 change */ if call to get_all_approvers was successfull
2777 IF l_return_status <> fnd_api.g_ret_sts_success
2778 THEN
2779 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2780 aso_debug_pub.ADD (
2781 'Status returned from get_all_approvers procedure :' || l_return_status,
2782 1,
2783 'N'
2784 );
2785 END IF;
2786 -- RAISE get_all_approvers_failed;
2787 RETURN;
2788 END IF;
2789
2790 -- fix for bug 4590633
2791
2792 for i in 1..x_approvers_list.count loop
2793
2794 IF ((x_approvers_list(i).approver_person_id is null) or (x_approvers_list(i).approver_person_id = fnd_api.g_miss_num) and
2795 (x_approvers_list(i).approver_user_id is not null) and (x_approvers_list(i).approver_user_id <> fnd_api.g_miss_num)) then
2796
2797 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2798 aso_debug_pub.ADD ('Person_id is null from AME Hence deriving it from user_id',1,'N');
2799 END IF;
2800
2801 open get_employee_id(x_approvers_list(i).approver_user_id);
2802 fetch get_employee_id into x_approvers_list(i).approver_person_id;
2803 close get_employee_id;
2804
2805 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2806 aso_debug_pub.ADD ('Derived person_id is: '||to_char(x_approvers_list(i).approver_person_id),1,'N');
2807 END IF;
2808
2809 END IF;
2810 end loop;
2811
2812 -- end of fix for bug 4590633
2813
2814 --- comparing the count between old and new list
2815 OPEN get_approver_count (
2816 p_object_approval_id
2817 );
2818 FETCH get_approver_count INTO l_approver_count;
2819 CLOSE get_approver_count;
2820
2821 IF l_approver_count <> x_approvers_list.COUNT
2822 THEN
2823 l_approvers_changed_flag := 'Y';
2824 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2825 aso_debug_pub.ADD (
2826 'The approvers list has changed ',
2827 1,
2828 'N'
2829 );
2830 END IF;
2831 ELSE
2832 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2833 aso_debug_pub.ADD (
2834 'Comparing the old list from table and the latest list ',
2835 1,
2836 'N'
2837 );
2838 END IF;
2839 -- first loop to find OUT NOCOPY /* file.sql.39 change */ if the data has changed
2840 OPEN get_existing_approvers (
2841 p_object_approval_id
2842 );
2843
2844 FOR i IN 1 .. x_approvers_list.COUNT
2845 LOOP
2846 FETCH get_existing_approvers INTO l_approval_det_id,
2847 l_approver_person_id,
2848 l_approver_user_id,
2849 l_approver_status;
2850 EXIT WHEN get_existing_approvers%NOTFOUND;
2851
2852 -- Make sure that if miss num is passed back from ame api, then it is
2853 -- converted into a null
2854
2855 IF x_approvers_list (
2856 i
2857 ).approver_person_id = fnd_api.g_miss_num
2858 THEN
2859 x_approvers_list (
2860 i
2861 ).approver_person_id := NULL;
2862 END IF;
2863
2864 IF x_approvers_list (
2865 i
2866 ).approver_user_id = fnd_api.g_miss_num
2867 THEN
2868 x_approvers_list (
2869 i
2870 ).approver_user_id := NULL;
2871 END IF;
2872
2873 IF ((l_approver_person_id <> x_approvers_list (
2874 i
2875 ).approver_person_id
2876 )
2877 OR (l_approver_user_id <> x_approvers_list (
2878 i
2879 ).approver_user_id
2880 )
2881 )
2882 THEN
2883 l_approvers_changed_flag := 'Y';
2884 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2885 aso_debug_pub.ADD (
2886 'The approvers list has changed ',
2887 1,
2888 'N'
2889 );
2890 END IF;
2891 EXIT;
2892 ELSE
2893 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2894 aso_debug_pub.ADD (
2895 'The approvers list has not changed ',
2896 1,
2897 'N'
2898 );
2899 END IF;
2900 l_approvers_changed_flag := 'N';
2901 END IF;
2902 END LOOP;
2903
2904 CLOSE get_existing_approvers;
2905 END IF;
2906
2907 -- if the data has changed
2908
2909 IF l_approvers_changed_flag = 'Y'
2910 THEN
2911 -- copying latest list into the new data structure ( x to y)
2912
2913 FOR i IN 1 .. x_approvers_list.COUNT
2914 LOOP
2915 l_new_approvers_tbl (
2916 i
2917 ).approver_user_id :=
2918 x_approvers_list (
2919 i
2920 ).approver_user_id;
2921 l_new_approvers_tbl (
2922 i
2923 ).approver_person_id :=
2924 x_approvers_list (
2925 i
2926 ).approver_person_id;
2927 l_new_approvers_tbl (
2928 i
2929 ).approver_sequence := i;
2930 -- Initialize the manadatory columns
2931 l_new_approvers_tbl (
2932 i
2933 ).creation_date := SYSDATE;
2934 l_new_approvers_tbl (
2935 i
2936 ).last_update_date := SYSDATE;
2937 l_new_approvers_tbl (
2938 i
2939 ).object_approval_id := p_object_approval_id;
2940 END LOOP;
2941
2942 -- comparing new data structure and existing approvers in database and if person or user id matches, copying
2943 -- existing approvers into the new data structure ( comparing Y and E and copying E to Y )
2944
2945 FOR i IN get_existing_approvers (
2946 p_object_approval_id
2947 )
2948 LOOP
2949 FOR k IN 1 .. l_new_approvers_tbl.COUNT
2950 LOOP
2951 IF ((i.approver_person_id = l_new_approvers_tbl (
2952 k
2953 ).approver_person_id
2954 )
2955 OR (i.approver_user_id = l_new_approvers_tbl (
2956 k
2957 ).approver_user_id
2958 )
2959 )
2960 THEN
2961 OPEN get_old_approvers (
2962 p_object_approval_id,
2963 i.approval_det_id
2964 );
2965 FETCH get_old_approvers INTO l_new_approvers_tbl (
2966 k
2967 );
2968 CLOSE get_old_approvers;
2969 END IF;
2970 END LOOP;
2971 END LOOP;
2972
2973 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2974 aso_debug_pub.ADD (
2975 'Deleting approvers from detail table where object_approval_id :'
2976 || p_object_approval_id,
2977 1,
2978 'N'
2979 );
2980 END IF;
2981
2982 DELETE FROM aso_apr_approval_details
2983 WHERE object_approval_id = p_object_approval_id;
2984
2985 l_approver_status := NULL;
2986 -- third loop to insert the data
2987
2988 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2989 aso_debug_pub.ADD (
2990 'New Approver count is ' || TO_CHAR (
2991 l_new_approvers_tbl.COUNT
2992 ),
2993 1,
2994 'N'
2995 );
2996 END IF;
2997 -- traversing the data structure from bottom up
2998 j := l_new_approvers_tbl.COUNT;
2999
3000 WHILE j <> 0
3001 LOOP
3002 -- if new list has more records than the old list , update the new approvers with pend status
3003 IF l_new_approvers_tbl (
3004 j
3005 ).approver_status IS NULL
3006 THEN
3007 l_new_approvers_tbl (
3008 j
3009 ).approver_status := 'NOSUBMIT';
3010 END IF;
3011
3012 -- because of the new list if the last approver is skipped, make him pending
3013 IF ((l_new_approvers_tbl (
3014 j
3015 ).approver_status = 'SKIP'
3016 )
3017 AND (j = l_new_approvers_tbl.COUNT)
3018 )
3019 THEN
3020 l_new_approvers_tbl (
3021 j
3022 ).approver_status := 'NOSUBMIT';
3023 END IF;
3024
3025 -- Mark the highest authority who approved in the old list
3026
3027 IF ((l_new_approvers_tbl (
3028 j
3029 ).approver_status IN ('APPR', 'SKIP')
3030 )
3031 AND (l_last_approved_approver = 0)
3032 )
3033 THEN
3034 l_last_approved_approver := j;
3035 END IF;
3036
3037 -- Make the approvers who are below the old highest authority as skipped
3038 IF ((j < l_last_approved_approver)
3039 AND (l_new_approvers_tbl (
3040 j
3041 ).approver_status = 'NOSUBMIT'
3042 )
3043 )
3044 THEN
3045 l_new_approvers_tbl (
3046 j
3047 ).approver_status := 'SKIP';
3048 END IF;
3049
3050 aso_apr_approvals_pkg.detail_insert_row (
3051 l_new_approvers_tbl (
3052 j
3053 ).approval_det_id,
3054 l_new_approvers_tbl (
3055 j
3056 ).object_approval_id,
3057 l_new_approvers_tbl (
3058 j
3059 ).approver_person_id ---p_APPROVER_PERSON_ID
3060 ,
3061 l_new_approvers_tbl (
3062 j
3063 ).approver_user_id ---p_APPROVER_USER_ID
3064 ,
3065 j -- P_APPROVER_SEQUENCE
3066 ,
3067 l_new_approvers_tbl (
3068 j
3069 ).approver_status --p_APPROVER_STATUS
3070 ,
3071 l_new_approvers_tbl (
3072 j
3073 ).approver_comments -- p_APPROVER_COMMENTS
3074 ,
3075 l_new_approvers_tbl (
3076 j
3077 ).date_sent --p_DATE_SENT
3078 ,
3079 l_new_approvers_tbl (
3080 j
3081 ).date_received -- p_DATE_RECEIVED
3082 ,
3083 l_new_approvers_tbl (
3084 j
3085 ).creation_date -- p_CREATION_DATE
3086 ,
3087 SYSDATE -- p_LAST_UPDATE_DATE
3088 ,
3089 l_new_approvers_tbl (
3090 j
3091 ).created_by -- P_CREATED_BY
3092 ,
3093 g_user_id -- P_UPDATED_BY
3094 ,
3095 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3096 ,
3097 l_new_approvers_tbl (
3098 j
3099 ).attribute1 -- p_ATTRIBUTE1
3100 ,
3101 l_new_approvers_tbl (
3102 j
3103 ).attribute2 -- p_ATTRIBUTE2
3104 ,
3105 l_new_approvers_tbl (
3106 j
3107 ).attribute3 -- p_ATTRIBUTE3
3108 ,
3109 l_new_approvers_tbl (
3110 j
3111 ).attribute4 -- p_ATTRIBUTE4
3112 ,
3113 l_new_approvers_tbl (
3114 j
3115 ).attribute5 -- p_ATTRIBUTE5
3116 ,
3117 l_new_approvers_tbl (
3118 j
3119 ).attribute6 -- p_ATTRIBUTE6
3120 ,
3121 l_new_approvers_tbl (
3122 j
3123 ).attribute7 -- p_ATTRIBUTE7
3124 ,
3125 l_new_approvers_tbl (
3126 j
3127 ).attribute8 -- p_ATTRIBUTE8
3128 ,
3129 l_new_approvers_tbl (
3130 j
3131 ).attribute9 -- p_ATTRIBUTE9
3132 ,
3133 l_new_approvers_tbl (
3134 j
3135 ).attribute10 -- p_ATTRIBUTE10
3136 ,
3137 l_new_approvers_tbl (
3138 j
3139 ).attribute11 -- p_ATTRIBUTE11
3140 ,
3141 l_new_approvers_tbl (
3142 j
3143 ).attribute12 -- p_ATTRIBUTE12
3144 ,
3145 l_new_approvers_tbl (
3146 j
3147 ).attribute13 -- p_ATTRIBUTE13
3148 ,
3149 l_new_approvers_tbl (
3150 j
3151 ).attribute14 -- p_ATTRIBUTE14
3152 ,
3153 l_new_approvers_tbl (
3154 j
3155 ).attribute15 -- p_ATTRIBUTE15
3156 ,
3157 l_new_approvers_tbl (
3158 j
3159 ).attribute16 -- p_ATTRIBUTE16
3160 ,
3161 l_new_approvers_tbl (
3162 j
3163 ).attribute17 -- p_ATTRIBUTE17
3164 ,
3165 l_new_approvers_tbl (
3166 j
3167 ).attribute18 -- p_ATTRIBUTE18
3168 ,
3169 l_new_approvers_tbl (
3170 j
3171 ).attribute19 -- p_ATTRIBUTE19
3172 ,
3173 l_new_approvers_tbl (
3174 j
3175 ).attribute20 -- p_ATTRIBUTE20
3176 ,
3177 l_new_approvers_tbl (
3178 j
3179 ).CONTEXT -- p_CONTEXT
3180 ,
3181 l_new_approvers_tbl (
3182 j
3183 ).security_group_id -- p_SECURITY_GROUP_ID
3184 ,
3185 l_new_approvers_tbl (
3186 j
3187 ).object_version_number -- p_OBJECT_VERSION_NUMBER
3188 );
3189 j := j - 1;
3190 END LOOP;
3191 END IF;
3192
3193 -- Loop to find OUT NOCOPY /* file.sql.39 change */ if the rules have changed
3194
3195 OPEN get_rule_count (
3196 p_object_approval_id
3197 );
3198 FETCH get_rule_count INTO l_rule_count;
3199 CLOSE get_rule_count;
3200
3201 IF l_rule_count <> x_rules_list.COUNT
3202 THEN
3203 l_rules_changed_flag := 'Y';
3204 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3205 aso_debug_pub.ADD (
3206 'The rules have changed ',
3207 1,
3208 'N'
3209 );
3210 END IF;
3211 ELSE
3212 OPEN get_existing_rules (
3213 p_object_approval_id
3214 );
3215
3216 FOR i IN 1 .. x_rules_list.COUNT
3217 LOOP
3218 FETCH get_existing_rules INTO l_oam_rule_id;
3219 EXIT WHEN get_existing_rules%NOTFOUND;
3220
3221 -- Make sure that if miss num is passed back from ame api, then it is
3222 -- converted into a null
3223
3224 IF x_rules_list (
3225 i
3226 ).rule_id = fnd_api.g_miss_num
3227 THEN
3228 x_rules_list (
3229 i
3230 ).rule_id := NULL;
3231 END IF;
3232
3233 IF (l_oam_rule_id <> x_rules_list (
3234 i
3235 ).rule_id
3236 )
3237 THEN
3238 l_rules_changed_flag := 'Y';
3239 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3240 aso_debug_pub.ADD (
3241 'The rules have changed ',
3242 1,
3243 'N'
3244 );
3245 END IF;
3246 EXIT;
3247 ELSE
3248 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3249 aso_debug_pub.ADD (
3250 'The rules have not changed ',
3251 1,
3252 'N'
3253 );
3254 END IF;
3255 l_rules_changed_flag := 'N';
3256 END IF;
3257 END LOOP;
3258
3259 CLOSE get_existing_rules;
3260 END IF;
3261
3262 -- refresh the rules if they are changed
3263
3264 IF l_rules_changed_flag = 'Y'
3265 THEN
3266 -- delete the existing rules
3267 DELETE FROM aso_apr_rules
3268 WHERE object_approval_id = p_object_approval_id;
3269
3270 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3271 aso_debug_pub.ADD (
3272 'Inserting rows into the rule table ',
3273 1,
3274 'N'
3275 );
3276 END IF;
3277
3278 FOR i IN 1 .. x_rules_list.COUNT
3279 LOOP
3280 aso_apr_approvals_pkg.rule_insert_row (
3281 p_rule_id,
3282 x_rules_list (
3283 i
3284 ).rule_id,
3285 x_rules_list (
3286 i
3287 ).rule_action_id,
3288 SYSDATE --p_CREATION_DATE
3289 ,
3290 g_user_id -- P_CREATED_BY
3291 ,
3292 SYSDATE -- p_LAST_UPDATE_DATE
3293 ,
3294 g_user_id -- P_UPDATED_BY
3295 ,
3296 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3297 ,
3298 p_object_approval_id,
3299 NULL -- p_ATTRIBUTE1
3300 ,
3301 NULL -- p_ATTRIBUTE2
3302 ,
3303 NULL -- p_ATTRIBUTE3
3304 ,
3305 NULL -- p_ATTRIBUTE4
3306 ,
3307 NULL -- p_ATTRIBUTE5
3308 ,
3309 NULL -- p_ATTRIBUTE6
3310 ,
3311 NULL -- p_ATTRIBUTE7
3312 ,
3313 NULL -- p_ATTRIBUTE8
3314 ,
3315 NULL -- p_ATTRIBUTE9
3316 ,
3317 NULL -- p_ATTRIBUTE10
3318 ,
3319 NULL -- p_ATTRIBUTE11
3320 ,
3321 NULL -- p_ATTRIBUTE12
3322 ,
3323 NULL -- p_ATTRIBUTE13
3324 ,
3325 NULL -- p_ATTRIBUTE14
3326 ,
3327 NULL -- p_ATTRIBUTE15
3328 ,
3329 NULL -- p_Attribute16
3330 ,
3331 NULL -- p_Attribute17
3332 ,
3333 NULL -- p_Attribute18
3334 ,
3335 NULL -- p_Attribute19
3336 ,
3337 NULL -- p_Attribute20
3338 ,
3339 NULL -- p_CONTEXT
3340 ,
3341 NULL -- p_SECURITY_GROUP_ID
3342 ,
3343 NULL -- p_OBJECT_VERSION_NUMBER
3344 );
3345 END LOOP;
3346 END IF;
3347 -- commit the work
3348 COMMIT WORK;
3349 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3350 aso_debug_pub.ADD (
3351 'End update_approver_list procedure ',
3352 1,
3353 'N'
3354 );
3355 END IF;
3356 EXCEPTION
3357 WHEN get_all_approvers_failed
3358 THEN
3359 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3360 aso_debug_pub.ADD (
3361 'Call to get_all_approvers failed in update_approver_list ',
3362 1,
3363 'N'
3364 );
3365 END IF;
3366 fnd_msg_pub.count_and_get (
3367 p_encoded => 'F',
3368 p_count => l_msg_count,
3369 p_data => l_msg_data
3370 );
3371 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3372 aso_debug_pub.ADD (
3373 'no. of FND messages :' || l_msg_count,
3374 1,
3375 'N'
3376 );
3377 END IF;
3378
3379 FOR k IN 1 .. l_msg_count
3380 LOOP
3381 l_msg_data := fnd_msg_pub.get (
3382 p_msg_index => k,
3383 p_encoded => 'F'
3384 );
3385 END LOOP;
3386
3387 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3388 aso_debug_pub.ADD (
3389 'Msg Data is' || l_msg_data,
3390 1,
3391 'N'
3392 );
3393 END IF;
3394 wf_core.CONTEXT (
3395 'ASOAPPRV',
3396 'Update_approver_list',
3397 'msg data ' || l_msg_data
3398 );
3399 RAISE;
3400 WHEN OTHERS
3401 THEN
3402 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3403 aso_debug_pub.ADD (
3404 'When others exception in update approver list procedure ',
3405 1,
3406 'N'
3407 );
3408 END IF;
3409 wf_core.CONTEXT (
3410 'ASOAPPRV',
3411 'Update_approver_list',
3412 SUBSTR (
3413 SQLERRM,
3414 1,
3415 250
3416 )
3417 );
3418 RAISE;
3419 END update_approver_list;
3420
3421 PROCEDURE last_approver_timeout_check (
3422 p_object_approval_id IN NUMBER
3423 ) IS
3424 new_approver_record ame_util.approverrecord;
3425 l_approver_sequence NUMBER;
3426 l_approval_det_id NUMBER;
3427 l_object_id NUMBER;
3428 l_object_type VARCHAR2 (240);
3429 l_application_id NUMBER;
3430
3431 CURSOR get_approvers (
3432 c_object_approval_id NUMBER
3433 ) IS
3434 SELECT approval_det_id
3435 FROM aso_apr_approval_details
3436 WHERE object_approval_id = c_object_approval_id;
3437
3438 CURSOR get_approver_sequence (
3439 c_object_approval_id NUMBER
3440 ) IS
3441 SELECT MAX (
3442 approver_sequence
3443 ) + 1
3444 FROM aso_apr_approval_details
3445 WHERE object_approval_id = c_object_approval_id;
3446
3447 CURSOR get_application_id (
3448 c_object_approval_id NUMBER
3449 ) IS
3450 SELECT DISTINCT object_id, object_type, application_id
3451 FROM aso_apr_obj_approvals aoa
3452 WHERE object_approval_id = c_object_approval_id;
3453 BEGIN
3454 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3455 aso_debug_pub.ADD (
3456 'Begin the last_approver_timeout_check prcoedure ',
3457 1,
3458 'N'
3459 );
3460 END IF;
3461 g_user_id := fnd_global.user_id;
3462 OPEN get_application_id (
3463 p_object_approval_id
3464 );
3465 FETCH get_application_id INTO l_object_id, l_object_type, l_application_id;
3466 CLOSE get_application_id;
3467 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3468 aso_debug_pub.ADD (
3469 'Object ID :' || l_object_id,
3470 1,
3471 'N'
3472 );
3473 aso_debug_pub.ADD (
3474 'Object Type :' || l_object_type,
3475 1,
3476 'N'
3477 );
3478 aso_debug_pub.ADD (
3479 'application id :' || l_application_id,
3480 1,
3481 'N'
3482 );
3483 aso_debug_pub.ADD (
3484 'Calling the AME clearall approvals API',
3485 1,
3486 'N'
3487 );
3488 END IF;
3489 ame_api.clearallapprovals (
3490 applicationidin => l_application_id,
3491 transactionidin => l_object_id,
3492 transactiontypein => l_object_type
3493 );
3494
3495 FOR i IN get_approvers (
3496 p_object_approval_id
3497 )
3498 LOOP
3499 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3500 aso_debug_pub.ADD (
3501 'calling the AME get next approver API ',
3502 1,
3503 'N'
3504 );
3505 END IF;
3506 ame_api.getnextapprover (
3507 applicationidin => l_application_id,
3508 transactionidin => l_object_id,
3509 transactiontypein => l_object_type,
3510 nextapproverout => new_approver_record
3511 );
3512 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3513 aso_debug_pub.ADD (
3514 'New Approver Person ID :' || new_approver_record.person_id,
3515 1,
3516 'N'
3517 );
3518 aso_debug_pub.ADD (
3519 'Setting approval status to no response for previous approvers ',
3520 1,
3521 'N'
3522 );
3523 END IF;
3524 new_approver_record.approval_status := ame_util.noresponsestatus;
3525 ame_api.updateapprovalstatus (
3526 applicationidin => l_application_id,
3527 transactionidin => l_object_id,
3528 transactiontypein => l_object_type,
3529 approverin => new_approver_record
3530 );
3531 END LOOP;
3532
3533 -- after getting all the approvers, try to get the next approver
3534
3535 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3536 aso_debug_pub.ADD (
3537 'calling the AME get next approver API ',
3538 1,
3539 'N'
3540 );
3541 END IF;
3542 ame_api.getnextapprover (
3543 applicationidin => l_application_id,
3544 transactionidin => l_object_id,
3545 transactiontypein => l_object_type,
3546 nextapproverout => new_approver_record
3547 );
3548 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3549 aso_debug_pub.ADD (
3550 'New Approver Person ID :' || new_approver_record.person_id,
3551 1,
3552 'N'
3553 );
3554 END IF;
3555
3556 IF (((new_approver_record.person_id IS NOT NULL)
3557 AND (new_approver_record.person_id <> fnd_api.g_miss_num)
3558 )
3559 OR ((new_approver_record.user_id IS NOT NULL)
3560 AND (new_approver_record.user_id <> fnd_api.g_miss_num)
3561 )
3562 )
3563 THEN
3564 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3565 aso_debug_pub.ADD (
3566 'Additional Approver found ',
3567 1,
3568 'N'
3569 );
3570 END IF;
3571 OPEN get_approver_sequence (
3572 p_object_approval_id
3573 );
3574 FETCH get_approver_sequence INTO l_approver_sequence;
3575 CLOSE get_approver_sequence;
3576 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3577 aso_debug_pub.ADD (
3578 'Inserting the new approver into the detail table ',
3579 1,
3580 'N'
3581 );
3582 END IF;
3583 aso_apr_approvals_pkg.detail_insert_row (
3584 l_approval_det_id,
3585 p_object_approval_id,
3586 new_approver_record.person_id --p_APPROVER_PERSON_ID
3587 ,
3588 new_approver_record.user_id --p_APPROVER_USER_ID
3589 ,
3590 l_approver_sequence -- P_APPROVER_SEQUENCE
3591 ,
3592 'NOSUBMIT' --p_APPROVER_STATUS
3593 ,
3594 NULL -- p_APPROVER_COMMENTS
3595 ,
3596 NULL --p_DATE_SENT
3597 ,
3598 NULL -- p_DATE_RECEIVED
3599 ,
3600 SYSDATE -- p_CREATION_DATE
3601 ,
3602 SYSDATE -- p_LAST_UPDATE_DATE
3603 ,
3604 g_user_id -- P_CREATED_BY
3605 ,
3606 g_user_id -- P_UPDATED_BY
3607 ,
3608 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3609 ,
3610 NULL -- p_ATTRIBUTE1
3611 ,
3612 NULL -- p_ATTRIBUTE2
3613 ,
3614 NULL -- p_ATTRIBUTE3
3615 ,
3616 NULL -- p_ATTRIBUTE4
3617 ,
3618 NULL -- p_ATTRIBUTE5
3619 ,
3620 NULL -- p_ATTRIBUTE6
3621 ,
3622 NULL -- p_ATTRIBUTE7
3623 ,
3624 NULL -- p_ATTRIBUTE8
3625 ,
3626 NULL -- p_ATTRIBUTE9
3627 ,
3628 NULL -- p_ATTRIBUTE10
3629 ,
3630 NULL -- p_ATTRIBUTE11
3631 ,
3632 NULL -- p_ATTRIBUTE12
3633 ,
3634 NULL -- p_ATTRIBUTE13
3635 ,
3636 NULL -- p_ATTRIBUTE14
3637 ,
3638 NULL -- p_ATTRIBUTE15
3639 ,
3640 NULL -- p_Attribute16
3641 ,
3642 NULL -- p_Attribute17
3643 ,
3644 NULL -- p_Attribute18
3645 ,
3646 NULL -- p_Attribute19
3647 ,
3648 NULL -- p_Attribute20
3649 ,
3650 NULL -- p_CONTEXT
3651 ,
3652 NULL -- p_SECURITY_GROUP_ID
3653 ,
3654 NULL -- p_OBJECT_VERSION_NUMBER
3655 );
3656 ELSE
3657 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3658 aso_debug_pub.ADD (
3659 'No Additional Approvers found ',
3660 1,
3661 'N'
3662 );
3663 END IF;
3664 RETURN;
3665 END IF;
3666
3667 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3668 aso_debug_pub.ADD (
3669 'End of last_approver_timeout_check procedure ',
3670 1,
3671 'N'
3672 );
3673 END IF;
3674 EXCEPTION
3675 WHEN OTHERS
3676 THEN
3677 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3678 aso_debug_pub.ADD (
3679 'Exception in Last_Approver_Timeout_Check ',
3680 1,
3681 'N'
3682 );
3683 END IF;
3684 RETURN;
3685 END last_approver_timeout_check;
3686
3687 PROCEDURE send_cancel_notification (
3688 itemtype IN VARCHAR2,
3689 itemkey IN VARCHAR2,
3690 actid IN NUMBER,
3691 funcmode IN VARCHAR2,
3692 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3693 ) IS
3694 l_message VARCHAR2 (240);
3695 l_notifenabled VARCHAR2 (3) := 'Y';
3696 l_orgid NUMBER := NULL;
3697 l_approval_id NUMBER;
3698 x_return_status VARCHAR2 (240);
3699 x_msg_count NUMBER;
3700 x_msg_data VARCHAR2 (240);
3701 l_msgenabled VARCHAR2 (3) := 'Y';
3702 l_notifname VARCHAR2 (240);
3703 get_message_error EXCEPTION;
3704
3705 BEGIN
3706 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3707 aso_debug_pub.ADD (
3708 'Begin SEND_CANCEL_NOTIFICATION Procedure',
3709 1,
3710 'N'
3711 );
3712 aso_debug_pub.ADD (
3713 'actid is ' || TO_CHAR (
3714 actid
3715 ),
3716 1,
3717 'N'
3718 );
3719 END IF;
3720
3721 IF funcmode = 'RUN'
3722 THEN
3723 l_message := wf_engine.getitemattrtext (
3724 itemtype,
3725 itemkey,
3726 'MESSAGE'
3727 );
3728 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3729 aso_debug_pub.ADD (
3730 'Message is ' || l_message,
3731 1,
3732 'N'
3733 );
3734 END IF;
3735
3736 -- Check for FYI Cancel Message and if so make the attribute as Cancel Message to approver
3737 IF ((l_message LIKE 'REQUEST_CANCELLED_FYI%') or (l_message LIKE 'OA_REQUEST_CANCELLED_FYI%'))
3738 THEN
3739 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3740 aso_debug_pub.ADD (
3741 'Setting message to OA_REQUEST_CANCEL_FYI_TO_APPR',
3742 1,
3743 'N'
3744 );
3745 END IF;
3746 l_message := 'OA_REQ_CANCEL_FYI_TO_APPR';
3747 ELSE
3748 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3749 aso_debug_pub.ADD (
3750 'Setting message to OA_REQUEST_CANCELLED_FYI',
3751 1,
3752 'N'
3753 );
3754 END IF;
3755 l_message := 'OA_REQUEST_CANCELLED_FYI';
3756 END IF;
3757
3758 wf_engine.setitemattrtext (
3759 itemtype => itemtype,
3760 itemkey => itemkey,
3761 aname => 'MESSAGE',
3762 avalue => l_message
3763 );
3764 -- Please note that the notification event name is same as message name
3765
3766 l_notifname := wf_engine.getitemattrtext (
3767 itemtype,
3768 itemkey,
3769 'MESSAGE'
3770 );
3771 l_notifname := 'ASO_' || l_notifname;
3772 l_notifenabled := ibe_wf_notif_setup_pvt.check_notif_enabled (
3773 l_notifname
3774 );
3775 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3776 aso_debug_pub.ADD (
3777 'Notification Name: ' || l_notifname || ' Enabled: ' || l_notifenabled,
3778 1,
3779 'N'
3780 );
3781 END IF;
3782
3783 IF l_notifenabled = 'Y'
3784 THEN
3785 -- Get the approval id
3786 l_approval_id := wf_engine.getitemattrnumber (
3787 itemtype,
3788 itemkey,
3789 'APPROVALID'
3790 );
3791 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3792 aso_debug_pub.ADD (
3793 'Approval Id is ' || l_approval_id,
3794 1,
3795 'N'
3796 );
3797 END IF;
3798 -- get the org id
3799 /*OPEN get_org_id (
3800 l_approval_id
3801 );
3802 FETCH get_org_id INTO l_orgid;
3803 CLOSE get_org_id; */
3804 l_orgid := wf_engine.getitemattrnumber (
3805 itemtype,
3806 itemkey,
3807 'ORGID'
3808 );
3809
3810 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3811 aso_debug_pub.ADD (
3812 'Org Id is ' || l_orgid,
3813 1,
3814 'N'
3815 );
3816 END IF;
3817 -- Retreive the message name for that event
3818 x_return_status := fnd_api.g_ret_sts_success;
3819
3820 ibe_wf_msg_mapping_pvt.retrieve_msg_mapping (
3821 p_org_id => l_orgid,
3822 p_msite_id => NULL,
3823 p_user_type => 'ALL',
3824 p_notif_name => l_notifname,
3825 x_enabled_flag => l_msgenabled,
3826 x_wf_message_name => l_message,
3827 x_return_status => x_return_status,
3828 x_msg_data => x_msg_data,
3829 x_msg_count => x_msg_count
3830 );
3831
3832 -- Check if the call to MSG mapping API was succssfull
3833 -- bug 3295179
3834 IF x_return_status <> fnd_api.g_ret_sts_success
3835 --IF x_msg_count > 0
3836 THEN
3837 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3838 aso_debug_pub.ADD (
3839 'Message count from MSG mapping API :' || x_msg_count,
3840 1,
3841 'N'
3842 );
3843 END IF;
3844 RAISE get_message_error;
3845 END IF;
3846 ELSE
3847 -- If the notification is not enabled
3848 resultout := 'COMPLETE';
3849 END IF;
3850
3851 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3852 aso_debug_pub.ADD (
3853 'Calling the standard notify process ',
3854 1,
3855 'N'
3856 );
3857 END IF;
3858 wf_standard.notify (
3859 itemtype,
3860 itemkey,
3861 actid,
3862 funcmode,
3863 resultout
3864 );
3865 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3866 aso_debug_pub.ADD (
3867 'End SEND_CANCEL_NOTIFICATION Procedure',
3868 1,
3869 'N'
3870 );
3871 END IF;
3872 RETURN;
3873 END IF;
3874
3875 IF (funcmode = 'CANCEL')
3876 THEN
3877 resultout := 'COMPLETE';
3878 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3879 aso_debug_pub.ADD (
3880 'End of SEND_CANCEL_NOTIFICATION Procedure',
3881 1,
3882 'N'
3883 );
3884 END IF;
3885 RETURN;
3886 END IF;
3887
3888 resultout := '';
3889 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3890 aso_debug_pub.ADD (
3891 'End of SEND_CANCEL_NOTIFICATION Procedure',
3892 1,
3893 'N'
3894 );
3895 END IF;
3896 RETURN;
3897 EXCEPTION
3898 WHEN get_message_error
3899 THEN
3900 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3901 aso_debug_pub.ADD (
3902 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
3903 1,
3904 'N'
3905 );
3906 END IF;
3907 wf_core.CONTEXT (
3908 'ASOAPPRV',
3909 'SEND_CANCEL_NOTIFICATION',
3910 'Error in retreiving Notification Message',
3911 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled
3912 );
3913 RAISE;
3914 WHEN OTHERS
3915 THEN
3916 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3917 aso_debug_pub.ADD (
3918 'Exception in SEND_CANCEL_NOTIFICATION Process SqlCode :' || SQLERRM,
3919 1,
3920 'N'
3921 );
3922 END IF;
3923 wf_core.CONTEXT (
3924 'ASOAPPRV',
3925 'SEND_CANCEL_NOTIFICATION',
3926 itemtype,
3927 itemkey,
3928 TO_CHAR (
3929 actid
3930 ),
3931 funcmode
3932 );
3933 RAISE;
3934 END send_cancel_notification;
3935
3936 PROCEDURE approver_details_doc (
3937 document_id IN VARCHAR2,
3938 display_type IN VARCHAR2,
3939 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3940 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3941 ) IS
3942 itemtype VARCHAR2 (30);
3943 itemkey VARCHAR2 (30);
3944 l_approval_id NUMBER;
3945 l_approver_user_name VARCHAR2 (240);
3946 l_approver_display_name VARCHAR2 (240);
3947 l_approver_status VARCHAR2 (10);
3948 l_approver_comments VARCHAR2 (4000);
3949 l_attribute_tbl aso_attribute_label_tbl_type;
3950
3951 CURSOR approver_details (
3952 c_object_approval_id NUMBER
3953 ) IS
3954 SELECT approver_user_id, approver_person_id, fl.meaning, approver_comments
3955 FROM aso_apr_approval_details apd, aso_lookups fl
3956 WHERE apd.approver_status = fl.lookup_code
3957 AND object_approval_id = c_object_approval_id
3958 AND fl.lookup_type = 'ASO_APPROVER_STATUS'
3959 ORDER BY approver_sequence;
3960 BEGIN
3961 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3962 aso_debug_pub.ADD (
3963 'Begin APPROVER_DETAILS_DOC procedure ',
3964 1,
3965 'N'
3966 );
3967 END IF;
3968 itemtype := NVL (
3969 SUBSTR (
3970 document_id,
3971 1,
3972 INSTR (
3973 document_id,
3974 ':'
3975 ) - 1
3976 ),
3977 'ASOAPPRV'
3978 );
3979 itemkey := SUBSTR (
3980 document_id,
3981 INSTR (
3982 document_id,
3983 ':'
3984 ) + 1
3985 ) || 'HED';
3986 l_approval_id := wf_engine.getitemattrnumber (
3987 itemtype => itemtype,
3988 itemkey => itemkey,
3989 aname => 'APPROVALID'
3990 );
3991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3992 aso_debug_pub.ADD (
3993 'ItemType is:' || itemtype,
3994 1,
3995 'N'
3996 );
3997 aso_debug_pub.ADD (
3998 'ItemKey is :' || itemkey,
3999 1,
4000 'N'
4001 );
4002 aso_debug_pub.ADD (
4003 'Approval ID :' || l_approval_id,
4004 1,
4005 'N'
4006 );
4007 END IF;
4008 -- get the attribute label
4009 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4010 aso_debug_pub.ADD (
4011 'Calling the get_attribute_label procedure',
4012 1,
4013 'N'
4014 );
4015 END IF;
4016 get_attribute_label (
4017 l_approval_id,
4018 l_attribute_tbl
4019 );
4020
4021 -- Create an html text buffer
4022 IF (display_type = 'text/html')
4023 THEN
4024 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4025 aso_debug_pub.ADD (
4026 'Display type is text/html',
4027 1,
4028 'N'
4029 );
4030 END IF;
4031 document := document
4032 || '<span class=sectionHeader1>'
4033 || l_attribute_tbl (
4034 11
4035 )
4036 || '</span>';
4037 document :=
4038 document
4039 || '<table class=OraBGAccentDark width="75%" cellpadding="1", cellspacing="1" border="0">';
4040 document :=
4041 document
4042 || '<tr> <td class="tableSmallHeaderCell" align="center">'
4043 || l_attribute_tbl (
4044 12
4045 )
4046 || '</td>';
4047 document := document
4048 || '<td class="tableSmallHeaderCell" align="center">'
4049 || l_attribute_tbl (
4050 13
4051 )
4052 || '</td>';
4053 document := document || '</tr>';
4054
4055 FOR i IN approver_details (
4056 l_approval_id
4057 )
4058 LOOP
4059 l_approver_display_name :=
4060 aso_apr_int.get_approver_name (
4061 i.approver_user_id,
4062 i.approver_person_id
4063 );
4064 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4065 aso_debug_pub.ADD (
4066 'Approver Name is ' || l_approver_display_name,
4067 1,
4068 'N'
4069 );
4070 END IF;
4071 document := document || '<tr>';
4072 document := document
4073 || '<td class="tableDataCell">'
4074 || l_approver_display_name
4075 || '</td>';
4076 document := document
4077 || '<td class="tableDataCell">'
4078 || i.meaning
4079 || '</td>';
4080 document := document || '</tr>';
4081 END LOOP;
4082
4083 document := document || '</table>';
4084 document_type := 'text/html';
4085 END IF;
4086
4087 -- Create a plain text buffer
4088
4089 IF (display_type = 'text/plain')
4090 THEN
4091 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4092 aso_debug_pub.ADD (
4093 'Display type is text/plain',
4094 1,
4095 'N'
4096 );
4097 END IF;
4098 document := document || fnd_global.local_chr (
4099 10
4100 );
4101 document := document || l_attribute_tbl (
4102 11
4103 );
4104 document := document || fnd_global.local_chr (
4105 10
4106 );
4107
4108 FOR i IN approver_details (
4109 l_approval_id
4110 )
4111 LOOP
4112 -- get the approver name
4113 l_approver_display_name :=
4114 aso_apr_int.get_approver_name (
4115 i.approver_user_id,
4116 i.approver_person_id
4117 );
4118 document := document || fnd_global.local_chr (
4119 10
4120 );
4121 document := document || l_attribute_tbl (
4122 12
4123 );
4124 document := document || ': ';
4125 document := document || l_approver_display_name;
4126 document := document || fnd_global.local_chr (
4127 10
4128 );
4129 document := document || l_attribute_tbl (
4130 13
4131 );
4132 document := document || ': ';
4133 document := document || i.meaning;
4134 END LOOP;
4135
4136 document_type := 'text/plain';
4137 END IF;
4138
4139 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4140 aso_debug_pub.ADD (
4141 'End APPROVER_DETAILS_DOC procedure ',
4142 1,
4143 'N'
4144 );
4145 END IF;
4146 EXCEPTION
4147 WHEN OTHERS
4148 THEN
4149 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4150 aso_debug_pub.ADD (
4151 'Exception in APPROVER_DETAILS_DOC SqlCode :' || SQLERRM,
4152 1,
4153 'N'
4154 );
4155 END IF;
4156 wf_core.CONTEXT (
4157 'ASOAPPRV',
4158 'APPROVER_DETAILS_DOC',
4159 itemtype,
4160 itemkey
4161 );
4162 RAISE;
4163 END approver_details_doc;
4164
4165 PROCEDURE quote_summary_doc (
4166 document_id IN VARCHAR2,
4167 display_type IN VARCHAR2,
4168 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4169 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4170 ) IS
4171 itemtype VARCHAR2 (30);
4172 itemkey VARCHAR2 (30);
4173 l_approval_id NUMBER;
4174 l_quote_name VARCHAR2 (240);
4175 l_quote_version VARCHAR2 (240);
4176 l_quote_number VARCHAR2 (240);
4177 l_quote_price VARCHAR2 (240);
4178 l_customer_name VARCHAR2 (240);
4179 l_account_number VARCHAR2 (240);
4180 l_contact_name VARCHAR2 (240);
4181 l_opportunity_name VARCHAR2 (240);
4182 l_expiration_date DATE;
4183 l_attribute_tbl aso_attribute_label_tbl_type;
4184 -- hyang performance fix, bug 2860045
4185 l_quote_header_id NUMBER;
4186 l_party_type VARCHAR2(240);
4187 CURSOR get_object_details (
4188 c_approval_id NUMBER
4189 ) IS
4190 SELECT quote_header_id, quote_name, quote_number, quote_expiration_date
4191 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
4192 WHERE qha.quote_header_id = aoa.object_id
4193 AND aoa.object_approval_id = c_approval_id;
4194
4195 CURSOR get_customer_name (
4196 c_quote_header_id NUMBER
4197 ) IS
4198 SELECT hp.party_name
4199 FROM aso_quote_headers_all qha,
4200 hz_parties hp
4201 WHERE qha.cust_party_id = hp.party_id
4202 AND qha.quote_header_id = c_quote_header_id;
4203
4204 -- bug 3934660 (put outer join)
4205 CURSOR get_account_number (
4206 c_quote_header_id NUMBER
4207 ) IS
4208 SELECT hca.account_number
4209 FROM hz_cust_accounts hca,
4210 aso_quote_headers_all qha
4211 WHERE qha.cust_account_id = hca.cust_account_id(+)
4212 AND qha.quote_header_id = c_quote_header_id;
4213
4214 CURSOR get_opportunity_name (
4215 c_approval_id NUMBER
4216 ) IS
4217 SELECT ala.description
4218 FROM as_leads_all ala,
4219 aso_quote_related_objects qro,
4220 aso_apr_obj_approvals aoa
4221 WHERE ala.lead_id = qro.object_id
4222 AND qro.relationship_type_code = 'OPP_QUOTE'
4223 AND qro.quote_object_id = aoa.object_id
4224 AND aoa.object_approval_id = c_approval_id;
4225
4226 -- bug 3934660
4227
4228 CURSOR get_party_type (c_approval_id NUMBER) IS
4229 SELECT P.PARTY_TYPE
4230 FROM hz_parties p, aso_quote_headers_all qh,aso_apr_obj_approvals aoa
4231 WHERE p.party_id = qh.party_id
4232 AND qh.quote_header_id = aoa.object_id
4233 AND aoa.object_approval_id = c_approval_id;
4234
4235 -- note that UI shows both contact and employees in drop down list,hence
4236 -- query uses both contact and employee
4237 CURSOR get_contact_name (
4238 c_approval_id NUMBER
4239 ) IS
4240 SELECT party_name
4241 FROM hz_parties p, hz_relationships r, aso_quote_headers_all qh,aso_apr_obj_approvals aoa
4242 WHERE p.party_id = r.object_id
4243 AND r.party_id = qh.party_id
4244 AND r.subject_id = qh.cust_party_id
4245 AND r.object_type = 'PERSON'
4246 AND r.relationship_code IN ('CONTACT','EMPLOYER_OF')
4247 AND qh.quote_header_id = aoa.object_id
4248 AND aoa.object_approval_id = c_approval_id;
4249
4250 BEGIN
4251 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4252 aso_debug_pub.ADD (
4253 'Begin QUOTE_SUMMARY_DOC procedure ',
4254 1,
4255 'N'
4256 );
4257 END IF;
4258 itemtype := NVL (
4259 SUBSTR (
4260 document_id,
4261 1,
4262 INSTR (
4263 document_id,
4264 ':'
4265 ) - 1
4266 ),
4267 'ASOAPPRV'
4268 );
4269 itemkey := SUBSTR (
4270 document_id,
4271 INSTR (
4272 document_id,
4273 ':'
4274 ) + 1
4275 ) || 'HED';
4276 l_approval_id := wf_engine.getitemattrnumber (
4277 itemtype => itemtype,
4278 itemkey => itemkey,
4279 aname => 'APPROVALID'
4280 );
4281 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4282 aso_debug_pub.ADD (
4283 'ItemType is:' || itemtype,
4284 1,
4285 'N'
4286 );
4287 aso_debug_pub.ADD (
4288 'ItemKey is :' || itemkey,
4289 1,
4290 'N'
4291 );
4292 aso_debug_pub.ADD (
4293 'Approval ID :' || l_approval_id,
4294 1,
4295 'N'
4296 );
4297 END IF;
4298 -- get the quote name, quote number and expiration date
4299 -- hyang performance fix bug 2860045, added l_quote_header_id
4300 OPEN get_object_details (
4301 l_approval_id
4302 );
4303 FETCH get_object_details INTO l_quote_header_id, l_quote_name, l_quote_number, l_expiration_date;
4304 CLOSE get_object_details;
4305 -- get the customer name and account number
4306 OPEN get_customer_name (
4307 l_quote_header_id
4308 );
4309 FETCH get_customer_name INTO l_customer_name;
4310 CLOSE get_customer_name;
4311 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4312 aso_debug_pub.ADD (
4313 'Customer Name is ' || l_customer_name,
4314 1,
4315 'N'
4316 );
4317 END IF;
4318 OPEN get_account_number (
4319 l_quote_header_id
4320 );
4321 FETCH get_account_number INTO l_account_number;
4322 CLOSE get_account_number;
4323 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4324 aso_debug_pub.ADD (
4325 'Customer Account Number is ' || l_account_number,
4326 1,
4327 'N'
4328 );
4329 END IF;
4330
4331 -- get the opportunity name for the quote
4332 OPEN get_opportunity_name (
4333 l_approval_id
4334 );
4335 FETCH get_opportunity_name INTO l_opportunity_name;
4336 CLOSE get_opportunity_name;
4337 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4338 aso_debug_pub.ADD (
4339 'Opportunity Name :' || l_opportunity_name,
4340 1,
4341 'N'
4342 );
4343 END IF;
4344 -- get the contact name for the quote
4345
4346 OPEN get_party_type(l_approval_id);
4347 FETCH get_party_type INTO l_party_type;
4348 CLOSE get_party_type;
4349
4350 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
4351 OPEN get_contact_name (
4352 l_approval_id
4353 );
4354 FETCH get_contact_name INTO l_contact_name;
4355 CLOSE get_contact_name;
4356 END IF;
4357 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4358 aso_debug_pub.ADD (
4359 'Contact Name :' || l_contact_name,
4360 1,
4361 'N'
4362 );
4363 END IF;
4364 -- get the attribute labels
4365
4366 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4367 aso_debug_pub.ADD (
4368 'Calling the get_attribute_label proceure ',
4369 1,
4370 'N'
4371 );
4372 END IF;
4373 get_attribute_label (
4374 l_approval_id,
4375 l_attribute_tbl
4376 );
4377
4378 -- Create an html text buffer
4379 IF (display_type = 'text/html')
4380 THEN
4381 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4382 aso_debug_pub.ADD (
4383 'Display type is text/html',
4384 1,
4385 'N'
4386 );
4387 END IF;
4388 document := document
4389 || '<span class=sectionHeader1>'
4390 || l_attribute_tbl (
4391 2
4392 )
4393 || '</span>';
4394 document :=
4395 document
4396 || '<table cellspacing=1 cellpadding=1 width="75%" border=0>';
4397 document := document
4398 || '<tr> <td class="prompt" align="right" nowrap>';
4399 document := document || l_attribute_tbl (
4400 3
4401 ) || '</td>';
4402 document := document
4403 || '<td class=datareadonly>'
4404 || l_quote_name
4405 || '</td>';
4406 document := document || '<td class="prompt" align="right" nowrap>';
4407 document := document || l_attribute_tbl (
4408 4
4409 ) || '</td>';
4410 document := document
4411 || '<td class=datareadonly>'
4412 || l_quote_number
4413 || '</td></tr>';
4414 document := document || '<tr>';
4415 document := document || '<td class="prompt" align="right" nowrap> ';
4416 document := document || l_attribute_tbl (
4417 5
4418 ) || '</td>';
4419 document := document
4420 || '<td class=datareadonly>'
4421 || l_customer_name
4422 || '</td>';
4423 document := document
4424 || '<td class="prompt" align="right" nowrap>'
4425 || l_attribute_tbl (
4426 6
4427 )
4428 || '</td>';
4429 document := document
4430 || '<td class=datareadonly>'
4431 || l_account_number
4432 || ' </td></tr>';
4433 document := document
4434 || '<tr> <td class="prompt" align="right" nowrap>'
4435 || l_attribute_tbl (
4436 7
4437 )
4438 || '</td>';
4439 document := document
4440 || '<td class=datareadonly>'
4441 || l_contact_name
4442 || '</td>';
4443 document := document
4444 || '<td class="prompt" align="right" nowrap>'
4445 || l_attribute_tbl (
4446 8
4447 )
4448 || '</td>';
4449 document := document
4450 || '<td class=datareadonly>'
4451 || l_opportunity_name
4452 || '</td></tr>';
4453 document := document
4454 || '<tr><td class="prompt" align="right" nowrap> ';
4455 document := document || l_attribute_tbl (
4456 9
4457 ) || '</td>';
4458 document := document
4459 || '<td class=datareadonly>'
4460 || l_expiration_date
4461 || '</td>';
4462 document := document || '<td>' || fnd_global.local_chr (
4463 38
4464 ) || 'nbsp';
4465 document := document
4466 || '</td><td>'
4467 || fnd_global.local_chr (
4468 38
4469 )
4470 || 'nbsp;</td>';
4471 document := document || '</tr> </table>';
4472 document_type := 'text/html';
4473 END IF;
4474
4475 -- Create a plain text buffer
4476
4477 IF (display_type = 'text/plain')
4478 THEN
4479 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4480 aso_debug_pub.ADD (
4481 'Display type is text/plain',
4482 1,
4483 'N'
4484 );
4485 END IF;
4486 document := document || fnd_global.local_chr (
4487 10
4488 );
4489 document := document || l_attribute_tbl (
4490 3
4491 );
4492 document := document || ': ';
4493 document := document || l_quote_name;
4494 document := document || fnd_global.local_chr (
4495 10
4496 );
4497 document := document || l_attribute_tbl (
4498 4
4499 );
4500 document := document || ': ';
4501 document := document || l_quote_number;
4502 document := document || fnd_global.local_chr (
4503 10
4504 );
4505 document := document || l_attribute_tbl (
4506 5
4507 );
4508 document := document || ': ';
4509 document := document || l_customer_name;
4510 document := document || fnd_global.local_chr (
4511 10
4512 );
4513 document := document || l_attribute_tbl (
4514 6
4515 );
4516 document := document || ': ';
4517 document := document || l_account_number;
4518 document := document || fnd_global.local_chr (
4519 10
4520 );
4521 document := document || l_attribute_tbl (
4522 7
4523 );
4524 document := document || ': ';
4525 document := document || l_contact_name;
4526 document := document || fnd_global.local_chr (
4527 10
4528 );
4529 document := document || l_attribute_tbl (
4530 8
4531 );
4532 document := document || ': ';
4533 document := document || l_opportunity_name;
4534 document := document || fnd_global.local_chr (
4535 10
4536 );
4537 document := document || l_attribute_tbl (
4538 9
4539 );
4540 document := document || ': ';
4541 document := document || l_expiration_date;
4542 document_type := 'text/plain';
4543 END IF;
4544
4545 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4546 aso_debug_pub.ADD (
4547 'End QUOTE_SUMMARY_DOC procedure ',
4548 1,
4549 'N'
4550 );
4551 END IF;
4552 EXCEPTION
4553 WHEN OTHERS
4554 THEN
4555 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4556 aso_debug_pub.ADD (
4557 'Exception in QUOTE_SUMMARY_DOC SqlCode :' || SQLERRM,
4558 1,
4559 'N'
4560 );
4561 END IF;
4562 wf_core.CONTEXT (
4563 'ASOAPPRV',
4564 'QUOTE_SUMMARY_DOC',
4565 itemtype,
4566 itemkey
4567 );
4568 RAISE;
4569 END quote_summary_doc;
4570
4571 PROCEDURE requester_comments_doc (
4572 document_id IN VARCHAR2,
4573 display_type IN VARCHAR2,
4574 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4575 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4576 ) IS
4577 itemtype VARCHAR2 (30);
4578 itemkey VARCHAR2 (30);
4579 l_approval_id NUMBER;
4580 l_requester_comments VARCHAR2 (2000);
4581 l_requester_userid NUMBER;
4582 l_attribute_tbl aso_attribute_label_tbl_type;
4583
4584 CURSOR get_requester_details (
4585 c_approval_id NUMBER
4586 ) IS
4587 SELECT requester_comments
4588 FROM aso_apr_obj_approvals
4589 WHERE object_approval_id = c_approval_id;
4590 BEGIN
4591 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4592 aso_debug_pub.ADD (
4593 'Begin REQUESTER_COMMENTS_DOC procedure ',
4594 1,
4595 'N'
4596 );
4597 END IF;
4598 itemtype := NVL (
4599 SUBSTR (
4600 document_id,
4601 1,
4602 INSTR (
4603 document_id,
4604 ':'
4605 ) - 1
4606 ),
4607 'ASOAPPRV'
4608 );
4609 itemkey := SUBSTR (
4610 document_id,
4611 INSTR (
4612 document_id,
4613 ':'
4614 ) + 1
4615 ) || 'HED';
4616 l_approval_id := wf_engine.getitemattrnumber (
4617 itemtype => itemtype,
4618 itemkey => itemkey,
4619 aname => 'APPROVALID'
4620 );
4621 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4622 aso_debug_pub.ADD (
4623 'ItemType is:' || itemtype,
4624 1,
4625 'N'
4626 );
4627 aso_debug_pub.ADD (
4628 'ItemKey is :' || itemkey,
4629 1,
4630 'N'
4631 );
4632 aso_debug_pub.ADD (
4633 'Approval ID :' || l_approval_id,
4634 1,
4635 'N'
4636 );
4637 END IF;
4638 -- get the attribute labels
4639
4640 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4641 aso_debug_pub.ADD (
4642 'Calling the get_attribute_label procedure ',
4643 1,
4644 'N'
4645 );
4646 END IF;
4647 get_attribute_label (
4648 l_approval_id,
4649 l_attribute_tbl
4650 );
4651
4652 -- Create an html text buffer
4653 IF (display_type = 'text/html')
4654 THEN
4655 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4656 aso_debug_pub.ADD (
4657 'Display type is text/html',
4658 1,
4659 'N'
4660 );
4661 END IF;
4662 OPEN get_requester_details (
4663 l_approval_id
4664 );
4665 FETCH get_requester_details INTO l_requester_comments;
4666 CLOSE get_requester_details;
4667 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4668 aso_debug_pub.ADD (
4669 'Creating a text/html document ',
4670 1,
4671 'N'
4672 );
4673 END IF;
4674 document := document
4675 || '<span class=sectionHeader1>'
4676 || l_attribute_tbl (
4677 10
4678 )
4679 || '</span>';
4680 document :=
4681 document
4682 || '<table cellspacing=1 cellpadding=1 width="75%" border=0>';
4683 document := document || '<tr>';
4684 document := document || '<td>' || l_requester_comments || '</td>';
4685 document := document || '</tr></table>';
4686 document_type := 'text/html';
4687 END IF;
4688
4689 -- Create a plain text buffer
4690
4691 IF (display_type = 'text/plain')
4692 THEN
4693 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4694 aso_debug_pub.ADD (
4695 'Display type is text/plain',
4696 1,
4697 'N'
4698 );
4699 END IF;
4700 document := document || fnd_global.local_chr (
4701 10
4702 );
4703 document := document || l_attribute_tbl (
4704 10
4705 );
4706 document := document || fnd_global.local_chr (
4707 10
4708 );
4709
4710 FOR i IN get_requester_details (
4711 l_approval_id
4712 )
4713 LOOP
4714 document := document || i.requester_comments;
4715 END LOOP;
4716
4717 document := document || fnd_global.local_chr (
4718 10
4719 );
4720 document_type := 'text/plain';
4721 END IF;
4722
4723 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4724 aso_debug_pub.ADD (
4725 'End REQUESTER_COMMENTS_DOC procedure ',
4726 1,
4727 'N'
4728 );
4729 END IF;
4730 EXCEPTION
4731 WHEN OTHERS
4732 THEN
4733 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4734 aso_debug_pub.ADD (
4735 'Exception in REQUESTER_COMMENTS_DOC SqlCode :' || SQLERRM,
4736 1,
4737 'N'
4738 );
4739 END IF;
4740 wf_core.CONTEXT (
4741 'ASOAPPRV',
4742 'REQUESTER_COMMENTS_DOC',
4743 itemtype,
4744 itemkey
4745 );
4746 RAISE;
4747 END requester_comments_doc;
4748
4749 PROCEDURE rule_details_doc (
4750 document_id IN VARCHAR2,
4751 display_type IN VARCHAR2,
4752 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4753 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4754 ) IS
4755 itemtype VARCHAR2 (30);
4756 itemkey VARCHAR2 (30);
4757 l_approval_id NUMBER;
4758 l_rule_description VARCHAR2 (240);
4759 l_approval_level VARCHAR2 (240);
4760 l_attribute_tbl aso_attribute_label_tbl_type;
4761 l_ruletypeout VARCHAR2 (240);
4762 l_conditionidsout ame_util.idlist;
4763 l_approvaltypenameout VARCHAR2 (240);
4764 l_approvaltypedescriptionout VARCHAR2 (240);
4765
4766 CURSOR get_rule_details (
4767 c_approval_id NUMBER
4768 ) IS
4769 SELECT oam_rule_id
4770 FROM aso_apr_rules
4771 WHERE object_approval_id = c_approval_id;
4772 BEGIN
4773 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4774 aso_debug_pub.ADD (
4775 'Begin RULE_DETAILS_DOC procedure ',
4776 1,
4777 'N'
4778 );
4779 END IF;
4780 itemtype := NVL (
4781 SUBSTR (
4782 document_id,
4783 1,
4784 INSTR (
4785 document_id,
4786 ':'
4787 ) - 1
4788 ),
4789 'ASOAPPRV'
4790 );
4791 itemkey := SUBSTR (
4792 document_id,
4793 INSTR (
4794 document_id,
4795 ':'
4796 ) + 1
4797 ) || 'HED';
4798 l_approval_id := wf_engine.getitemattrnumber (
4799 itemtype => itemtype,
4800 itemkey => itemkey,
4801 aname => 'APPROVALID'
4802 );
4803 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4804 aso_debug_pub.ADD (
4805 'ItemType is:' || itemtype,
4806 1,
4807 'N'
4808 );
4809 aso_debug_pub.ADD (
4810 'ItemKey is :' || itemkey,
4811 1,
4812 'N'
4813 );
4814 aso_debug_pub.ADD (
4815 'Approval ID :' || l_approval_id,
4816 1,
4817 'N'
4818 );
4819 END IF;
4820 -- get the attribute label
4821
4822 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4823 aso_debug_pub.ADD (
4824 'Calling the get_attribute_label procedure ',
4825 1,
4826 'N'
4827 );
4828 END IF;
4829 get_attribute_label (
4830 l_approval_id,
4831 l_attribute_tbl
4832 );
4833
4834 -- Create an html text buffer
4835 IF (display_type = 'text/html')
4836 THEN
4837 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4838 aso_debug_pub.ADD (
4839 'Display type is text/html',
4840 1,
4841 'N'
4842 );
4843 END IF;
4844 document := document
4845 || '<span class=sectionHeader1>'
4846 || l_attribute_tbl (
4847 14
4848 )
4849 || '</span>';
4850 document :=
4851 document
4852 || '<table class=OraBGAccentDark cellspacing=1 cellpadding=1 width="75%" border=0>';
4853 document :=
4854 document
4855 || '<tr> <td class="tableSmallHeaderCell" align="center">'
4856 || l_attribute_tbl (
4857 15
4858 )
4859 || '</td>';
4860 document := document
4861 || '<td class="tableSmallHeaderCell" align="center">'
4862 || l_attribute_tbl (
4863 16
4864 )
4865 || '</td></tr>';
4866
4867 FOR i IN get_rule_details (
4868 l_approval_id
4869 )
4870 LOOP
4871 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4872 aso_debug_pub.ADD (
4873 'Calling AME get applicable rules procedure',
4874 1,
4875 'N'
4876 );
4877 END IF;
4878 ame_api.getruledetails1 (
4879 ruleidin => i.oam_rule_id,
4880 ruletypeout => l_ruletypeout,
4881 ruledescriptionout => l_rule_description,
4882 conditionidsout => l_conditionidsout,
4883 approvaltypenameout => l_approvaltypenameout,
4884 approvaltypedescriptionout => l_approvaltypedescriptionout,
4885 approvaldescriptionout => l_approval_level
4886 );
4887 document := document
4888 || '<tr> <td class="tableDataCell">'
4889 || l_rule_description
4890 || '</td>';
4891 document := document
4892 || '<td class="tableDataCell">'
4893 || l_approval_level
4894 || '</td></tr>';
4895 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4896 aso_debug_pub.ADD (
4897 'Rule Description is ' || l_rule_description,
4898 1,
4899 'N'
4900 );
4901 aso_debug_pub.ADD (
4902 'Approval level is ' || l_approval_level,
4903 1,
4904 'N'
4905 );
4906 END IF;
4907 END LOOP;
4908
4909 document := document || '</table>';
4910 document_type := 'text/html';
4911 END IF;
4912
4913 -- Create a plain text buffer
4914
4915 IF (display_type = 'text/plain')
4916 THEN
4917 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4918 aso_debug_pub.ADD (
4919 'Display type is text/plain',
4920 1,
4921 'N'
4922 );
4923 END IF;
4924 document := document || fnd_global.local_chr (
4925 10
4926 );
4927 document := document || l_attribute_tbl (
4928 14
4929 );
4930 document := document || fnd_global.local_chr (
4931 10
4932 );
4933
4934 FOR i IN get_rule_details (
4935 l_approval_id
4936 )
4937 LOOP
4938 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4939 aso_debug_pub.ADD (
4940 'Calling AME get applicable rules procedure',
4941 1,
4942 'N'
4943 );
4944 END IF;
4945 ame_api.getruledetails1 (
4946 ruleidin => i.oam_rule_id,
4947 ruletypeout => l_ruletypeout,
4948 ruledescriptionout => l_rule_description,
4949 conditionidsout => l_conditionidsout,
4950 approvaltypenameout => l_approvaltypenameout,
4951 approvaltypedescriptionout => l_approvaltypedescriptionout,
4952 approvaldescriptionout => l_approval_level
4953 );
4954 document := document || l_attribute_tbl (
4955 15
4956 );
4957 document := document || ': ';
4958 document := document || l_rule_description;
4959 document := document || fnd_global.local_chr (
4960 10
4961 );
4962 document := document || l_attribute_tbl (
4963 16
4964 );
4965 document := document || ': ';
4966 document := document || l_approval_level;
4967 END LOOP;
4968
4969 document_type := 'text/plain';
4970 END IF;
4971
4972 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4973 aso_debug_pub.ADD (
4974 'End RULE_DETAILS_DOC procedure ',
4975 1,
4976 'N'
4977 );
4978 END IF;
4979 EXCEPTION
4980 WHEN OTHERS
4981 THEN
4982 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4983 aso_debug_pub.ADD (
4984 'Exception in RULE_DETAILS_DOC SqlCode :' || SQLERRM,
4985 1,
4986 'N'
4987 );
4988 END IF;
4989 wf_core.CONTEXT (
4990 'ASOAPPRV',
4991 'RULE_DETAILS_DOC',
4992 itemtype,
4993 itemkey
4994 );
4995 RAISE;
4996 END rule_details_doc;
4997
4998 PROCEDURE quote_detail_url (
4999 document_id IN VARCHAR2,
5000 display_type IN VARCHAR2,
5001 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5002 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5003 ) IS
5004 itemtype VARCHAR2 (30);
5005 itemkey VARCHAR2 (30);
5006 l_approval_id NUMBER;
5007 -- hyang performance fix bug 2860045
5008 l_quote_header_id NUMBER;
5009 l_jsp_name VARCHAR2 (2000);
5010 l_url VARCHAR2 (2000);
5011 l_attribute_tbl aso_attribute_label_tbl_type;
5012 l_quote_number NUMBER;
5013 l_party_number VARCHAR2(30);
5014 l_cust_account_id NUMBER;
5015 l_party_type VARCHAR2 (50);
5016 l_org_id NUMBER;
5017 l_notification_id NUMBER;
5018
5019 -- hyang performance fix bug 2860045
5020 CURSOR get_object_details (
5021 c_approval_id NUMBER
5022 ) IS
5023 SELECT quote_header_id, quote_number,qha.org_id
5024 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
5025 WHERE qha.quote_header_id = aoa.object_id
5026 AND aoa.object_approval_id = c_approval_id;
5027
5028 -- bug 3934660 replace qha.party_id with cust_party_id
5029
5030 CURSOR get_quote_details (
5031 c_quote_header_id NUMBER
5032 ) IS
5033 SELECT hca.cust_account_id, hp.party_type
5034 FROM aso_quote_headers_all qha,
5035 hz_parties hp,
5036 hz_cust_accounts hca
5037 WHERE qha.quote_header_id = c_quote_header_id
5038 AND nvl(qha.cust_account_id,0 ) = hca.cust_account_id (+)
5039 AND qha.cust_party_id = hp.party_id;
5040
5041 BEGIN
5042 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5043 aso_debug_pub.ADD (
5044 'Begin QUOTE_DETAIL_URL procedure ',
5045 1,
5046 'N'
5047 );
5048 END IF;
5049 itemtype := NVL (
5050 SUBSTR (
5051 document_id,
5052 1,
5053 INSTR (
5054 document_id,
5055 ':'
5056 ) - 1
5057 ),
5058 'ASOAPPRV'
5059 );
5060 itemkey := SUBSTR (
5061 document_id,
5062 INSTR (
5063 document_id,
5064 ':'
5065 ) + 1
5066 ) || 'HED';
5067 l_approval_id := wf_engine.getitemattrnumber (
5068 itemtype => itemtype,
5069 itemkey => itemkey,
5070 aname => 'APPROVALID'
5071 );
5072 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5073 aso_debug_pub.ADD (
5074 'ItemType is:' || itemtype,
5075 1,
5076 'N'
5077 );
5078 aso_debug_pub.ADD (
5079 'ItemKey is :' || itemkey,
5080 1,
5081 'N'
5082 );
5083 aso_debug_pub.ADD (
5084 'Approval ID :' || l_approval_id,
5085 1,
5086 'N'
5087 );
5088 END IF;
5089 -- get the quote header id
5090 -- hyang performance fix, added l_quote_header_id, bug 2860045
5091 OPEN get_object_details (
5092 l_approval_id
5093 );
5094 FETCH get_object_details INTO l_quote_header_id, l_quote_number,l_org_id;
5095 CLOSE get_object_details;
5096 OPEN get_quote_details (
5097 l_quote_header_id
5098 );
5099 FETCH get_quote_details INTO l_cust_account_id,
5100 l_party_type;
5101 CLOSE get_quote_details;
5102
5103 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5104 aso_debug_pub.ADD (
5105 'Quote header ID is ' || l_quote_header_id,
5106 1,
5107 'N'
5108 );
5109 END IF;
5110 -- get the server address
5111 l_url := fnd_web_config.jsp_agent (
5112 );
5113 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5114 aso_debug_pub.ADD (
5115 'URL ID is ' || l_url,
5116 1,
5117 'N'
5118 );
5119 END IF;
5120 -- get the jsp name
5121
5122 l_jsp_name := 'qotSZzpAppsLink.jsp?';
5123 l_jsp_name := l_jsp_name || 'qotFrmMainFile=qotSZzdContainer.jsp';
5124 l_jsp_name := l_jsp_name
5125 || fnd_global.local_chr (
5126 38
5127 )
5128 || 'qotFrmDspFile=qotSCocOverview.jsp';
5129 l_jsp_name := l_jsp_name
5130 || fnd_global.local_chr (
5131 38
5132 )
5133 || 'qotFrmRefFile=qotSCocOverview.jsp';
5134 l_jsp_name := l_jsp_name
5135 || fnd_global.local_chr (
5136 38
5137 )
5138 || 'qotDetCode=QUOTE';
5139 l_jsp_name := l_jsp_name
5140 || fnd_global.local_chr (
5141 38
5142 )
5143 || 'qotPtyType='
5144 || l_party_type;
5145 l_jsp_name := l_jsp_name
5146 || fnd_global.local_chr (
5147 38
5148 )
5149 || 'qotHdrId='
5150 || l_quote_header_id;
5151 l_jsp_name := l_jsp_name
5152 || fnd_global.local_chr (
5153 38
5154 )
5155 || 'qotHdrAcctId='
5156 || l_cust_account_id;
5157 l_jsp_name := l_jsp_name
5158 || fnd_global.local_chr (
5159 38
5160 )
5161 || 'qotHdrNbr='
5162 || l_quote_number;
5163 l_jsp_name := l_jsp_name
5164 || fnd_global.local_chr (
5165 38
5166 )
5167 || 'qotReqSetCookie=Y';
5168 l_jsp_name := l_jsp_name
5169 || fnd_global.local_chr (
5170 38
5171 )
5172 || 'qotFromApvlLink=Y';
5173
5174 -- bug 3178070
5175 l_jsp_name := l_jsp_name
5176 || fnd_global.local_chr (
5177 38
5178 )
5179 || 'qotApvOrgId='
5180 || l_org_id;
5181
5182 l_jsp_name := l_jsp_name
5183 || fnd_global.local_chr (
5184 38
5185 )
5186 || 'qotApvNotifId=&#NID';
5187
5188
5189
5190
5191 -- get the attribute label
5192 get_attribute_label (
5193 l_approval_id,
5194 l_attribute_tbl
5195 );
5196
5197 -- Create an html text buffer
5198 IF (display_type = 'text/html')
5199 THEN
5200 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5201 aso_debug_pub.ADD (
5202 'Display type is text/html',
5203 1,
5204 'N'
5205 );
5206 END IF;
5207 document := '<a href = "'
5208 || l_url
5209 || l_jsp_name
5210 || '">'
5211 || l_attribute_tbl (
5212 1
5213 )
5214 || '</a>';
5215 document_type := 'text/html';
5216 END IF;
5217
5218 -- Create a plain text buffer
5219
5220 IF (display_type = 'text/plain')
5221 THEN
5222 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5223 aso_debug_pub.ADD (
5224 'Display type is text/plain',
5225 1,
5226 'N'
5227 );
5228 END IF;
5229 NULL;
5230 document_type := 'text/plain';
5231 END IF;
5232
5233 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5234 aso_debug_pub.ADD (
5235 'End QUOTE_DETAIL_URL procedure ',
5236 1,
5237 'N'
5238 );
5239 END IF;
5240 EXCEPTION
5241 WHEN OTHERS
5242 THEN
5243 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5244 aso_debug_pub.ADD (
5245 'Exception in QUOTE_DETAIL_URL SqlCode :' || SQLERRM,
5246 1,
5247 'N'
5248 );
5249 END IF;
5250 wf_core.CONTEXT (
5251 'ASOAPPRV',
5252 'QUOTE_DETAIL_URL',
5253 itemtype,
5254 itemkey
5255 );
5256 RAISE;
5257 END quote_detail_url;
5258
5259 PROCEDURE get_attribute_label (
5260 p_approval_id IN NUMBER,
5261 p_attribute_tbl OUT NOCOPY /* file.sql.39 change */ aso_attribute_label_tbl_type
5262 ) IS
5263 l_application_id NUMBER;
5264
5265 CURSOR get_application_id (
5266 c_approval_id NUMBER
5267 ) IS
5268 SELECT application_id
5269 FROM aso_apr_obj_approvals
5270 WHERE object_approval_id = c_approval_id;
5271
5272 -- hyang, bug 2860045, performance fix.
5273 CURSOR get_label (
5274 c_application_id NUMBER
5275 ) IS
5276 SELECT attribute_label_long
5277 FROM ak_region_items ara, ak_attributes_tl aat
5278 WHERE region_code = 'ASO_APR_NOTIFICATION'
5279 and region_application_id = c_application_id
5280 AND AAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID
5281 AND AAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE
5282 AND AAT.LANGUAGE = USERENV('LANG')
5283 ORDER by display_sequence;
5284 BEGIN
5285 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5286 aso_debug_pub.ADD (
5287 'begin GET_ATTRIBUTE_LABEL procedure ',
5288 1,
5289 'N'
5290 );
5291 END IF;
5292 -- get the application id
5293 OPEN get_application_id (
5294 p_approval_id
5295 );
5296 FETCH get_application_id INTO l_application_id;
5297 CLOSE get_application_id;
5298 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5299 aso_debug_pub.ADD (
5300 'Application ID is ' || l_application_id,
5301 1,
5302 'N'
5303 );
5304 END IF;
5305 p_attribute_tbl := aso_attribute_label_tbl_type (
5306 );
5307
5308 -- fetch the labels and populate the PL/SQL table
5309
5310 FOR i IN get_label (
5311 697
5312 )
5313 LOOP
5314 p_attribute_tbl.EXTEND;
5315 p_attribute_tbl (
5316 p_attribute_tbl.COUNT
5317 ) := i.attribute_label_long;
5318 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5319 aso_debug_pub.ADD (
5320 'Label is ' || p_attribute_tbl (
5321 p_attribute_tbl.COUNT
5322 ),
5323 1,
5324 'N'
5325 );
5326 END IF;
5327 END LOOP;
5328
5329 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5330 aso_debug_pub.ADD (
5331 'Count of labels is ' || p_attribute_tbl.COUNT,
5332 1,
5333 'N'
5334 );
5335 aso_debug_pub.ADD (
5336 'End GET_ATTRIBUTE_LABEL procedure ',
5337 1,
5338 'N'
5339 );
5340 END IF;
5341 EXCEPTION
5342 WHEN OTHERS
5343 THEN
5344 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5345 aso_debug_pub.ADD (
5346 'Exception in GET_ATTRIBUTE_LABEL SqlCode :' || SQLERRM,
5347 1,
5348 'N'
5349 );
5350 END IF;
5351 wf_core.CONTEXT (
5352 'ASOAPPRV',
5353 'GET_ATTRIBUTE_LABEL',
5354 SQLERRM
5355 );
5356 RAISE;
5357 END get_attribute_label;
5358
5359
5360 FUNCTION GetRunFuncURL
5361 ( p_function_name IN VARCHAR2
5362 , p_resp_appl_id IN NUMBER DEFAULT NULL
5363 , p_resp_id IN NUMBER DEFAULT NULL
5364 , p_security_group_id IN NUMBER DEFAULT NULL
5365 , p_parameters IN VARCHAR2 DEFAULT NULL
5366 ) RETURN VARCHAR2
5367 IS
5368
5369 l_function_id NUMBER ;
5370 l_resp_appl_id NUMBER := p_resp_appl_id;
5371 l_resp_id NUMBER := p_resp_id ;
5372 l_security_group_id NUMBER := p_security_group_id;
5373
5374 BEGIN
5375
5376 l_function_id := fnd_function.get_function_id(p_function_name) ;
5377
5378
5379 IF p_resp_appl_id IS NULL THEN
5380 l_resp_appl_id := -1 ;
5381 END IF ;
5382
5383
5384 IF p_resp_id IS NULL THEN
5385 l_resp_id := -1 ;
5386 END IF ;
5387
5388 IF p_security_group_id IS NULL THEN
5389 l_security_group_id := -1 ;
5390 END IF ;
5391
5392 -- Call Fnd API
5393 RETURN fnd_run_function.get_run_function_url
5394 ( p_function_id => l_function_id
5395 , p_resp_appl_id => l_resp_appl_id
5396 , p_resp_id => l_resp_id
5397 , p_security_group_id => l_security_group_id
5398 , p_parameters => p_parameters ) ;
5399
5400
5401 END GetRunFuncURL ;
5402
5403
5404 PROCEDURE set_attributes (
5405 itemtype IN VARCHAR2,
5406 itemkey IN VARCHAR2,
5407 actid IN NUMBER,
5408 funcmode IN VARCHAR2,
5409 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5410 ) IS
5411
5412 l_approval_object VARCHAR2 (4000);
5413 l_approval_id NUMBER;
5414 l_org_id NUMBER;
5415 l_ampsign VARCHAR2(1) := fnd_global.local_chr(38);
5416 l_url VARCHAR2(32000);
5417
5418
5419 CURSOR OBJECT (
5420 c_approval_id NUMBER
5421 ) IS
5422 SELECT quote_name, quote_number,org_id,quote_header_id
5423 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
5424 WHERE qha.quote_header_id = aoa.object_id
5425 AND aoa.object_approval_id = c_approval_id;
5426
5427 BEGIN
5428 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5429 aso_debug_pub.ADD (
5430 'Begin set attribute Process',
5431 1,
5432 'N'
5433 );
5434 END IF;
5435
5436 IF funcmode = 'RUN'
5437 THEN
5438 l_approval_id := wf_engine.getitemattrnumber (
5439 itemtype,
5440 itemkey,
5441 'APPROVALID'
5442 );
5443
5444 FOR i IN OBJECT (
5445 l_approval_id
5446 )
5447 LOOP
5448 l_approval_object := i.quote_name;
5449 l_org_id := i.org_id;
5450
5451 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5452 aso_debug_pub.ADD (
5453 'Quote name :' || l_approval_object,
5454 1,
5455 'N'
5456 );
5457 END IF;
5458 wf_engine.setitemattrtext (
5459 itemtype => itemtype,
5460 itemkey => itemkey,
5461 aname => 'APPROVAL_OBJECT',
5462 avalue => l_approval_object
5463 );
5464
5465 wf_engine.setitemattrnumber (
5466 itemtype => itemtype,
5467 itemkey => itemkey,
5468 aname => 'ORGID',
5469 avalue => l_org_id
5470 );
5471
5472 wf_engine.setitemattrnumber (
5473 itemtype => itemtype,
5474 itemkey => itemkey,
5475 aname => 'QTEHDRID',
5476 avalue => i.quote_header_id
5477 );
5478
5479 wf_engine.setitemattrnumber (
5480 itemtype => itemtype,
5481 itemkey => itemkey,
5482 aname => 'QTENUMBER',
5483 avalue => i.quote_number
5484 );
5485
5486
5487 -- bug 5350149
5488 l_url := aso_apr_wf_pvt.GetRunFuncURL(
5489 p_function_name => 'QOT_OAUI_QUOTE_DETAILS',
5490 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
5491 p_resp_id => FND_GLOBAL.RESP_ID,
5492 p_parameters => l_ampsign||l_ampsign||'QotIntgEvtSrc=ApvlNotif'||l_ampsign||'QotIntgEvt=Event.QuoteDet'||l_ampsign||'QotIntgEvtVal='
5493 ||i.quote_header_id||l_ampsign||'QotIntgEvtVal1='||i.quote_number||l_ampsign||'addBreadCrumb=Y') ;
5494
5495
5496 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5497 aso_debug_pub.ADD ('RESP_APPL_ID: ' || FND_GLOBAL.RESP_APPL_ID,1,'N');
5498 aso_debug_pub.ADD ('RESP_ID: ' || FND_GLOBAL.RESP_ID,1,'N');
5499 aso_debug_pub.ADD ('l_url: '||substr(l_url,1,240),1,'N');
5500 END IF;
5501
5502
5503 wf_engine.setitemattrtext (
5504 itemtype => itemtype,
5505 itemkey => itemkey,
5506 aname => 'OAQTEDETAILLNK',
5507 avalue => l_url
5508 );
5509
5510 END LOOP;
5511 resultout := 'COMPLETE';
5512 END IF;
5513 EXCEPTION
5514 WHEN OTHERS
5515 THEN
5516 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5517 aso_debug_pub.ADD (
5518 'Exception in Set Attributes SqlCode :' || SQLERRM,
5519 1,
5520 'N'
5521 );
5522 END IF;
5523 wf_core.CONTEXT (
5524 'ASOAPPRV',
5525 'set_attributes',
5526 itemtype,
5527 itemkey
5528 );
5529 RAISE;
5530
5531 END;
5532
5533 PROCEDURE update_approval_status (
5534 p_update_header_or_detail_flag IN VARCHAR2,
5535 p_object_approval_id IN NUMBER,
5536 p_approval_det_id IN NUMBER,
5537 p_status IN VARCHAR2,
5538 note IN VARCHAR2
5539 ) is
5540 begin
5541 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5542 aso_debug_pub.ADD (
5543 'Start update_approval_status procedure ',
5544 1,
5545 'N'
5546 );
5547
5548 aso_debug_pub.ADD (
5549 'Flag is :' || p_update_header_or_detail_flag,
5550 1,
5551 'N'
5552 );
5553 aso_debug_pub.ADD (
5554 'Object Approval ID is :' || p_object_approval_id,
5555 1,
5556 'N'
5557 );
5558 aso_debug_pub.ADD (
5559 'Approval Detail ID is :' || p_approval_det_id,
5560 1,
5561 'N'
5562 );
5563 aso_debug_pub.ADD (
5564 'Status is :' || p_status,
5565 1,
5566 'N'
5567 );
5568
5569 END IF;
5570
5571 IF p_update_header_or_detail_flag = 'HEADER' THEN
5572 IF (p_status = 'PEND')
5573 THEN
5574 UPDATE aso_apr_obj_approvals
5575 SET approval_status = p_status,
5576 last_update_date = SYSDATE,
5577 last_updated_by = g_user_id,
5578 last_update_login = g_user_id
5579 WHERE object_approval_id = p_object_approval_id;
5580 ELSE
5581 UPDATE aso_apr_obj_approvals
5582 SET approval_status = p_status,
5583 last_update_date = SYSDATE,
5584 end_date = SYSDATE,
5585 last_updated_by = g_user_id,
5586 last_update_login = g_user_id
5587 WHERE object_approval_id = p_object_approval_id;
5588 END IF;
5589
5590 END IF;
5591
5592 IF p_update_header_or_detail_flag = 'DETAIL' THEN
5593 UPDATE aso_apr_approval_details
5594 SET approver_status = p_status,
5595 date_received = SYSDATE,
5596 last_update_date = SYSDATE,
5597 approver_comments = note,
5598 last_updated_by = fnd_global.user_id,
5599 last_update_login = fnd_global.user_id
5600 WHERE approval_det_id = p_approval_det_id;
5601 END IF;
5602
5603 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5604 aso_debug_pub.ADD (
5605 'End update_approval_status procedure ',
5606 1,
5607 'N'
5608 );
5609 END IF;
5610
5611 end;
5612
5613
5614 END aso_apr_wf_pvt;