[Home] [Help]
PACKAGE BODY: APPS.ASO_APR_WF_PVT
Source
1 PACKAGE BODY aso_apr_wf_pvt AS
2 /* $Header: asovwapb.pls 120.12 2012/01/04 10:32:48 rassharm 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 x_msg_data VARCHAR2 (2000); -- bug 13508417
1798 l_msgenabled VARCHAR2 (3) := 'Y';
1799 l_notifname VARCHAR2 (240);
1800 get_message_error EXCEPTION;
1801 notif_not_enabled_error EXCEPTION;
1802
1803 BEGIN
1804 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1805 aso_debug_pub.ADD (
1806 'Start of SEND NOTIFICATION Procedure',
1807 1,
1808 'N'
1809 );
1810 aso_debug_pub.ADD (
1811 'actid is ' || TO_CHAR (
1812 actid
1813 ),
1814 1,
1815 'N'
1816 );
1817 END IF;
1818
1819 IF funcmode = 'RUN'
1820 THEN
1821 -- Please note that the notification event name is same as message name
1822 l_notifname := wf_engine.getitemattrtext (
1823 itemtype,
1824 itemkey,
1825 'MESSAGE'
1826 );
1827 l_message := l_notifname;
1828 l_notifname := 'ASO_' || l_notifname;
1829 -- Check if the notification is enabled for that event
1830
1831 l_notifenabled := ibe_wf_notif_setup_pvt.check_notif_enabled (
1832 l_notifname
1833 );
1834 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1835 aso_debug_pub.ADD (
1836 'Notification Name: ' || l_notifname || ' Enabled: ' || l_notifenabled,
1837 1,
1838 'N'
1839 );
1840 END IF;
1841
1842 IF l_notifenabled = 'Y'
1843 THEN
1844 -- Get the approval id
1845 l_approval_id := wf_engine.getitemattrnumber (
1846 itemtype,
1847 itemkey,
1848 'APPROVALID'
1849 );
1850 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1851 aso_debug_pub.ADD (
1852 'Approval ID :' || l_approval_id,
1853 1,
1854 'N'
1855 );
1856 END IF;
1857 -- get the org id
1858 /*OPEN get_org_id (
1859 l_approval_id
1860 );
1861 FETCH get_org_id INTO l_orgid;
1862 CLOSE get_org_id; */
1863
1864 l_orgid := wf_engine.getitemattrnumber (
1865 itemtype,
1866 itemkey,
1867 'ORGID'
1868 );
1869
1870 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1871 aso_debug_pub.ADD (
1872 'Org Id is ' || l_orgid,
1873 1,
1874 'N'
1875 );
1876 END IF;
1877 -- Retreive the message name for that event
1878
1879 x_return_status := fnd_api.g_ret_sts_success;
1880
1881 ibe_wf_msg_mapping_pvt.retrieve_msg_mapping (
1882 p_org_id => l_orgid,
1883 p_msite_id => NULL,
1884 p_user_type => NULL,
1885 p_notif_name => l_notifname,
1886 x_enabled_flag => l_msgenabled,
1887 x_wf_message_name => l_message,
1888 x_return_status => x_return_status,
1889 x_msg_data => x_msg_data,
1890 x_msg_count => x_msg_count
1891 );
1892 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1893 aso_debug_pub.ADD (
1894 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled,
1895 1,
1896 'N'
1897 );
1898 END IF;
1899
1900 -- bug 3295179
1901 IF ( (x_return_status <> fnd_api.g_ret_sts_success) OR (l_message IS NULL) )
1902 --IF ((x_msg_count > 0)
1903 -- OR (l_message IS NULL)
1904 -- )
1905 THEN
1906 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1907 aso_debug_pub.ADD (
1908 'Message count is :' || x_msg_count,
1909 1,
1910 'N'
1911 );
1912 END IF;
1913 RAISE get_message_error;
1914 END IF;
1915
1916 IF l_msgenabled = 'Y'
1917 THEN
1918 -- Set the message name
1919 wf_engine.setitemattrtext (
1920 itemtype => itemtype,
1921 itemkey => itemkey,
1922 aname => 'MESSAGE',
1923 avalue => l_message
1924 );
1925 END IF;
1926 END IF;
1927
1928 IF (l_msgenabled <> 'Y')
1929 OR (l_notifenabled <> 'Y')
1930 THEN
1931 -- if the event is not enabled or message is not enabled
1932 -- if the notification requires a response
1933 IF ((l_message LIKE 'REQUEST_APPROVAL%') OR (l_message LIKE 'OA_REQUEST_APPROVAL%'))
1934 THEN
1935 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1936 aso_debug_pub.ADD (
1937 'Notification not enabled for REQUEST_APPROVAL',
1938 1,
1939 'N'
1940 );
1941 END IF;
1942 RAISE notif_not_enabled_error;
1943 ELSE
1944 -- if the notification is a FYI
1945 -- Check for FYI Message and if so make the attribute as Approval Message
1946 IF ((l_message LIKE 'FYI_TO_REQUESTER%') OR (l_message LIKE 'OA_FYI_TO_REQUESTER%'))
1947 THEN
1948 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1949 aso_debug_pub.ADD (
1950 'Setting the message name to OA_REQUEST_APPROVAL',
1951 1,
1952 'N'
1953 );
1954 END IF;
1955 wf_engine.setitemattrtext (
1956 itemtype => itemtype,
1957 itemkey => itemkey,
1958 aname => 'MESSAGE',
1959 avalue => 'OA_REQUEST_APPROVAL'
1960 );
1961 END IF;
1962
1963 resultout := 'COMPLETE';
1964 RETURN;
1965 END IF;
1966 END IF;
1967
1968 l_message := wf_engine.getitemattrtext (
1969 itemtype,
1970 itemkey,
1971 'MESSAGE'
1972 );
1973 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1974 aso_debug_pub.ADD (
1975 'Message is ' || l_message,
1976 1,
1977 'N'
1978 );
1979 END IF;
1980
1981 IF ((l_message LIKE 'REQUEST_APPROVAL_REMINDER%') OR (l_message LIKE 'OA_REQUEST_APPROVAL_REM%'))
1982 THEN
1983 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1984 aso_debug_pub.ADD (
1985 'As Message is REQUEST_APPROVAL_REMINDER will cancel original approval notif',
1986 1,
1987 'N'
1988 );
1989 END IF;
1990 l_notification_id := wf_engine.getitemattrnumber (
1991 itemtype,
1992 itemkey,
1993 'NOTIFICATION_ID'
1994 );
1995 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1996 aso_debug_pub.ADD (
1997 'Cancelling notification for notification ID :' || l_notification_id,
1998 1,
1999 'N'
2000 );
2001 END IF;
2002 wf_notification.CANCEL (
2003 nid => l_notification_id,
2004 cancel_comment => 'TIMEOUT'
2005 );
2006 END IF;
2007
2008 wf_standard.notify (
2009 itemtype,
2010 itemkey,
2011 actid,
2012 funcmode,
2013 resultout
2014 );
2015 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2016 aso_debug_pub.ADD (
2017 'Calling the standard notify process for Itemtype :'
2018 || itemtype
2019 || ' and itemkey :'
2020 || itemkey,
2021 1,
2022 'N'
2023 );
2024 END IF;
2025 -- Store the Notification id for timeout
2026
2027 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2028 aso_debug_pub.ADD (
2029 'Storing the notification id so as to be used in timeout case ',
2030 1,
2031 'N'
2032 );
2033 END IF;
2034
2035 IF (resultout IS NOT NULL)
2036 THEN
2037 l_first_colon_pos := INSTR (
2038 resultout,
2039 ':',
2040 1,
2041 1
2042 );
2043 l_second_colon_pos := INSTR (
2044 resultout,
2045 ':',
2046 1,
2047 2
2048 );
2049
2050 IF ((l_first_colon_pos <> 0)
2051 AND (l_second_colon_pos <> 0)
2052 )
2053 THEN
2054 l_notification_id := TO_NUMBER (
2055 SUBSTR (
2056 resultout,
2057 l_first_colon_pos + 1,
2058 l_second_colon_pos - l_first_colon_pos - 1
2059 )
2060 );
2061 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2062 aso_debug_pub.ADD (
2063 'Notification id is ' || l_notification_id,
2064 1,
2065 'N'
2066 );
2067 END IF;
2068 wf_engine.setitemattrnumber (
2069 itemtype => itemtype,
2070 itemkey => itemkey,
2071 aname => 'NOTIFICATION_ID',
2072 avalue => l_notification_id
2073 );
2074 END IF;
2075 END IF;
2076
2077 -- Check for FYI Message and if so make the attribute as Approval Message
2078 IF ((l_message LIKE 'FYI_TO_REQUESTER%') or (l_message LIKE 'OA_FYI_TO_REQUESTER%'))
2079 THEN
2080 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2081 aso_debug_pub.ADD (
2082 'Setting the message name to OA_REQUEST_APPROVAL',
2083 1,
2084 'N'
2085 );
2086 END IF;
2087 wf_engine.setitemattrtext (
2088 itemtype => itemtype,
2089 itemkey => itemkey,
2090 aname => 'MESSAGE',
2091 avalue => 'OA_REQUEST_APPROVAL'
2092 );
2093 ELSIF ((l_message LIKE 'REQUEST_APPROVAL%') or (l_message LIKE 'OA_REQUEST_APPROVAL%'))
2094 THEN
2095 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2096 aso_debug_pub.ADD (
2097 'Setting the message name to OA_REQUEST_APPROVAL_REM',
2098 1,
2099 'N'
2100 );
2101 END IF;
2102 wf_engine.setitemattrtext (
2103 itemtype => itemtype,
2104 itemkey => itemkey,
2105 aname => 'MESSAGE',
2106 avalue => 'OA_REQUEST_APPROVAL_REM'
2107 );
2108 END IF;
2109
2110 -- resultout := 'COMPLETE';
2111 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2112 aso_debug_pub.ADD (
2113 'End of SEND NOTIFICATION Procedure ',
2114 1,
2115 'N'
2116 );
2117 END IF;
2118 RETURN;
2119 END IF;
2120
2121 IF (funcmode = 'CANCEL')
2122 THEN
2123 resultout := 'COMPLETE';
2124 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2125 aso_debug_pub.ADD (
2126 'End of SEND NOTIFICATION Procedure ',
2127 1,
2128 'N'
2129 );
2130 END IF;
2131 RETURN;
2132 END IF;
2133
2134 resultout := '';
2135 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2136 aso_debug_pub.ADD (
2137 'End of SEND NOTIFICATION Procedure ',
2138 1,
2139 'N'
2140 );
2141 END IF;
2142 RETURN;
2143 EXCEPTION
2144 WHEN notif_not_enabled_error
2145 THEN
2146 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2147 aso_debug_pub.ADD (
2148 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2149 1,
2150 'N'
2151 );
2152 END IF;
2153 wf_core.CONTEXT (
2154 'ASOAPPRV',
2155 'SEND_NOTIFICATION',
2156 'Mandatory Notification Name: ' || l_notifname || ' is not Enabled '
2157 );
2158 RAISE;
2159 WHEN get_message_error
2160 THEN
2161 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2162 aso_debug_pub.ADD (
2163 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2164 1,
2165 'N'
2166 );
2167 END IF;
2168 wf_core.CONTEXT (
2169 'ASOAPPRV',
2170 'SEND_NOTIFICATION',
2171 'Error in retreiving Notification Message',
2172 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled
2173 );
2174 RAISE;
2175 WHEN OTHERS
2176 THEN
2177 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2178 aso_debug_pub.ADD (
2179 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
2180 1,
2181 'N'
2182 );
2183 END IF;
2184 wf_core.CONTEXT (
2185 'ASOAPPRV',
2186 'SEND_NOTIFICATION',
2187 itemtype,
2188 itemkey,
2189 TO_CHAR (
2190 actid
2191 ),
2192 funcmode
2193 );
2194 RAISE;
2195 END send_notification;
2196
2197 PROCEDURE update_entity (
2198 itemtype IN VARCHAR2,
2199 itemkey IN VARCHAR2,
2200 actid IN NUMBER,
2201 funcmode IN VARCHAR2,
2202 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2203 ) IS
2204 l_contract_approval_level VARCHAR2 (240);
2205 l_quote_header_rec aso_quote_pub.qte_header_rec_type;
2206 l_control_rec aso_quote_pub.control_rec_type;
2207 x_qte_header_rec aso_quote_pub.qte_header_rec_type;
2208 x_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
2209 x_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
2210 x_hd_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2211 x_hd_payment_tbl aso_quote_pub.payment_tbl_type;
2212 x_hd_shipment_tbl aso_quote_pub.shipment_tbl_type;
2213 x_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2214 x_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2215 x_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
2216 x_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
2217 x_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
2218 x_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
2219 x_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
2220 x_ln_price_attributes_tbl aso_quote_pub.price_attributes_tbl_type;
2221 x_ln_payment_tbl aso_quote_pub.payment_tbl_type;
2222 x_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
2223 x_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
2224 x_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
2225 x_return_status VARCHAR2 (240);
2226 x_msg_count NUMBER;
2227 x_msg_data VARCHAR2 (240);
2228 l_user_id Number;
2229 l_person_id Number;
2230 l_object_approval_id NUMBER;
2231 l_status varchar2(20);
2232 l_notifname varchar2(240);
2233 CURSOR get_quote_header_id (
2234 c_object_approval_id NUMBER
2235 ) IS
2236 SELECT object_id
2237 FROM aso_apr_obj_approvals
2238 WHERE object_approval_id = c_object_approval_id;
2239
2240 CURSOR check_contract_enabled (
2241 c_quote_header_id NUMBER
2242 ) IS
2243 SELECT contract_approval_level
2244 FROM aso_quote_headers_all
2245 WHERE quote_header_id = c_quote_header_id;
2246
2247 CURSOR get_latest_date (
2248 c_quote_header_id NUMBER
2249 ) IS
2250 SELECT last_update_date,org_id
2251 FROM aso_quote_headers_all
2252 WHERE quote_header_id = c_quote_header_id;
2253
2254 CURSOR get_quote_status_id (
2255 v_status VARCHAR2
2256 ) IS
2257 SELECT quote_status_id
2258 FROM aso_quote_statuses_b
2259 WHERE status_code = v_status;
2260
2261 CURSOR get_last_approver (
2262 c_object_approval_id NUMBER
2263 ) IS
2264 SELECT approver_person_id
2265 FROM aso_apr_approval_details
2266 WHERE object_approval_id = c_object_approval_id
2267 AND approver_sequence = (select max(approver_sequence)
2268 FROM aso_apr_approval_details
2269 WHERE object_approval_id = c_object_approval_id);
2270
2271 CURSOR get_user_id ( c_employee_id NUMBER)
2272 IS
2273 SELECT user_id
2274 FROM fnd_user
2275 WHERE employee_id = c_employee_id;
2276
2277
2278
2279 CURSOR get_rejected_approver (c_object_approval_id NUMBER) IS
2280 SELECT approver_person_id
2281 FROM aso_apr_approval_details
2282 WHERE object_approval_id = c_object_approval_id
2283 AND approver_status = 'REJ';
2284
2285 user_id number;
2286 resp_id number;
2287 resp_appl_id number;
2288 BEGIN
2289 -- Initialize the quote header record
2290
2291 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2292 aso_debug_pub.ADD (
2293 'Begin Update Entity Procedure ',
2294 1,
2295 'N'
2296 );
2297 END IF;
2298
2299 IF funcmode = 'RUN' THEN
2300
2301 l_object_approval_id := wf_engine.getitemattrnumber (
2302 itemtype,
2303 itemkey,
2304 'APPROVALID'
2305 );
2306
2307 l_notifname := wf_engine.getitemattrtext (
2308 itemtype,
2309 itemkey,
2310 'MESSAGE'
2311 );
2312 IF l_notifname = 'OA_REQUEST_REJECTED' then
2313 l_status := 'REJ';
2314 ELSIF l_notifname = 'OA_REQ_APPR_BY_ALL_APPR' THEN
2315 l_status := 'APPR';
2316 END IF;
2317
2318 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2319 aso_debug_pub.ADD (
2320 'Object approval ID :' || l_object_approval_id,
2321 1,
2322 'N'
2323 );
2324 aso_debug_pub.ADD (
2325 'Status :' || l_status,
2326 1,
2327 'N'
2328 );
2329 END IF;
2330 --g_user_id := fnd_global.user_id;
2331 -- fix for bug 3929409
2332 IF l_status = 'APPR' THEN
2333 OPEN get_last_approver (
2334 l_object_approval_id
2335 );
2336 FETCH get_last_approver INTO l_person_id;
2337 CLOSE get_last_approver;
2338
2339 OPEN get_user_id(l_person_id);
2340 FETCH get_user_id INTO l_user_id;
2341 CLOSE get_user_id;
2342
2343 g_user_id := l_user_id;
2344 ELSIF l_status = 'REJ' THEN
2345 OPEN get_rejected_approver(l_object_approval_id);
2346 FETCH get_rejected_approver into l_person_id;
2347 CLOSE get_rejected_approver;
2348
2349 OPEN get_user_id(l_person_id);
2350 FETCH get_user_id INTO l_user_id;
2351 CLOSE get_user_id;
2352 g_user_id := l_user_id;
2353
2354 ELSE
2355 g_user_id := fnd_global.user_id;
2356
2357 END IF;
2358
2359 user_id := fnd_global.user_id;
2360 resp_id := fnd_global.RESP_ID;
2361 resp_appl_id := fnd_global.RESP_APPL_ID;
2362
2363 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2364 aso_debug_pub.ADD( 'new debug for bug 6072144 ',1,'N');
2365 aso_debug_pub.ADD( 'BEFORE resetting the original values ',1,'N');
2366 aso_debug_pub.ADD( 'fnd_global.user_id: '|| fnd_global.user_id,1,'N');
2367 aso_debug_pub.ADD( 'fnd_global.resp_id: '|| fnd_global.resp_id,1,'N');
2368 aso_debug_pub.ADD( 'fnd_global.resp_appl_id: '|| fnd_global.resp_appl_id,1,'N');
2369 aso_debug_pub.ADD( 'end debug for bug 6072144 ',1,'N');
2370 END IF;
2371 FND_GLOBAL.APPS_INITIALIZE(g_user_id,0,0,0);
2372
2373 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2374 aso_debug_pub.ADD( 'After setting the global values ',1,'N');
2375 END IF;
2376
2377 l_quote_header_rec := aso_quote_pub.g_miss_qte_header_rec;
2378
2379 OPEN get_quote_header_id (
2380 l_object_approval_id
2381 );
2382 FETCH get_quote_header_id INTO l_quote_header_rec.quote_header_id;
2383 CLOSE get_quote_header_id;
2384 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2385 aso_debug_pub.ADD (
2386 'Quote Header ID :' || l_quote_header_rec.quote_header_id,
2387 1,
2388 'N'
2389 );
2390 END IF;
2391 -- Check if contract is enabled for the quote
2392 OPEN check_contract_enabled (
2393 l_quote_header_rec.quote_header_id
2394 );
2395 FETCH check_contract_enabled INTO l_contract_approval_level;
2396 CLOSE check_contract_enabled;
2397
2398 -- setting the quote status id
2399 IF l_status = 'APPR'
2400 THEN
2401 -- if contract is enabled, set status to contract enables
2402 IF l_contract_approval_level IS NOT NULL
2403 THEN
2404 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2405 aso_debug_pub.ADD (
2406 'Setting Quote Status to CONTRACT REQUIRED',
2407 1,
2408 'N'
2409 );
2410 END IF;
2411 OPEN get_quote_status_id (
2412 'CONTRACT REQUIRED'
2413 );
2414 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2415 CLOSE get_quote_status_id;
2416 ELSE
2417 -- otherwise set status to APPROVED
2418 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2419 aso_debug_pub.ADD (
2420 'Setting Quote Status to APPROVED',
2421 1,
2422 'N'
2423 );
2424 END IF;
2425 OPEN get_quote_status_id (
2426 'APPROVED'
2427 );
2428 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2429 CLOSE get_quote_status_id;
2430 END IF;
2431 ELSIF l_status = 'REJ'
2432 THEN
2433 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2434 aso_debug_pub.ADD (
2435 'Setting Quote Status to REJECTED',
2436 1,
2437 'N'
2438 );
2439 END IF;
2440 OPEN get_quote_status_id (
2441 'APPROVAL REJECTED'
2442 );
2443 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2444 CLOSE get_quote_status_id;
2445 ELSIF l_status = 'CAN'
2446 THEN
2447 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2448 aso_debug_pub.ADD (
2449 'Setting Quote Status to APPROVAL CANCELLED ',
2450 1,
2451 'N'
2452 );
2453 END IF;
2454 OPEN get_quote_status_id (
2455 'APPROVAL CANCELED'
2456 );
2457 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2458 CLOSE get_quote_status_id;
2459 ELSIF l_status = 'PEND'
2460 THEN
2461 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2462 aso_debug_pub.ADD (
2463 'Setting Quote Status to APPROVAL PENDING',
2464 1,
2465 'N'
2466 );
2467 END IF;
2468 OPEN get_quote_status_id (
2469 'APPROVAL PENDING'
2470 );
2471 FETCH get_quote_status_id INTO l_quote_header_rec.quote_status_id;
2472 CLOSE get_quote_status_id;
2473 END IF;
2474
2475 OPEN get_latest_date (
2476 l_quote_header_rec.quote_header_id
2477 );
2478 FETCH get_latest_date INTO l_quote_header_rec.last_update_date, l_quote_header_rec.org_id;
2479 CLOSE get_latest_date;
2480 -- Setting the auto version flag to true
2481 l_control_rec.auto_version_flag := fnd_api.g_true;
2482
2483 -- set the org context , see bug 4731684
2484 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2485 aso_debug_pub.ADD (
2486 'Before calling update quote: Setting the single org context to org_id: '|| l_quote_header_rec.org_id,
2487 1,
2488 'N'
2489 );
2490 END IF;
2491 mo_global.set_policy_context('S', l_quote_header_rec.org_id);
2492
2493
2494 -- Update the quote status by calling the update_quote API
2495
2496 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2497 aso_debug_pub.ADD (
2498 'Calling the update quote API in ASO_UPDATE_QUOTE_PUB package ',
2499 1,
2500 'N'
2501 );
2502 END IF;
2503 aso_quote_pub.update_quote (
2504 p_api_version_number => 1.0,
2505 p_init_msg_list => fnd_api.g_false,
2506 p_commit => fnd_api.g_false,
2507 p_control_rec => l_control_rec,
2508 p_qte_header_rec => l_quote_header_rec,
2509 p_hd_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2510 p_hd_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2511 p_hd_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2512 p_hd_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2513 p_hd_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2514 p_qte_line_tbl => aso_quote_pub.g_miss_qte_line_tbl,
2515 p_qte_line_dtl_tbl => aso_quote_pub.g_miss_qte_line_dtl_tbl,
2516 p_line_attr_ext_tbl => aso_quote_pub.g_miss_line_attribs_ext_tbl,
2517 p_line_rltship_tbl => aso_quote_pub.g_miss_line_rltship_tbl,
2518 p_price_adjustment_tbl => aso_quote_pub.g_miss_price_adj_tbl,
2519 p_price_adj_attr_tbl => aso_quote_pub.g_miss_price_adj_attr_tbl,
2520 p_price_adj_rltship_tbl => aso_quote_pub.g_miss_price_adj_rltship_tbl,
2521 p_ln_price_attributes_tbl => aso_quote_pub.g_miss_price_attributes_tbl,
2522 p_ln_payment_tbl => aso_quote_pub.g_miss_payment_tbl,
2523 p_ln_shipment_tbl => aso_quote_pub.g_miss_shipment_tbl,
2524 p_ln_freight_charge_tbl => aso_quote_pub.g_miss_freight_charge_tbl,
2525 p_ln_tax_detail_tbl => aso_quote_pub.g_miss_tax_detail_tbl,
2526 x_qte_header_rec => x_qte_header_rec,
2527 x_qte_line_tbl => x_qte_line_tbl,
2528 x_qte_line_dtl_tbl => x_qte_line_dtl_tbl,
2529 x_hd_price_attributes_tbl => x_hd_price_attributes_tbl,
2530 x_hd_payment_tbl => x_hd_payment_tbl,
2531 x_hd_shipment_tbl => x_hd_shipment_tbl,
2532 x_hd_freight_charge_tbl => x_hd_freight_charge_tbl,
2533 x_hd_tax_detail_tbl => x_hd_tax_detail_tbl,
2534 x_line_attr_ext_tbl => x_line_attr_ext_tbl,
2535 x_line_rltship_tbl => x_line_rltship_tbl,
2536 x_price_adjustment_tbl => x_price_adjustment_tbl,
2537 x_price_adj_attr_tbl => x_price_adj_attr_tbl,
2538 x_price_adj_rltship_tbl => x_price_adj_rltship_tbl,
2539 x_ln_price_attributes_tbl => x_ln_price_attributes_tbl,
2540 x_ln_payment_tbl => x_ln_payment_tbl,
2541 x_ln_shipment_tbl => x_ln_shipment_tbl,
2542 x_ln_freight_charge_tbl => x_ln_freight_charge_tbl,
2543 x_ln_tax_detail_tbl => x_ln_tax_detail_tbl,
2544 x_return_status => x_return_status,
2545 x_msg_count => x_msg_count,
2546 x_msg_data => x_msg_data
2547 );
2548
2549 IF x_return_status <> fnd_api.g_ret_sts_success
2550 THEN
2551 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2552 aso_debug_pub.ADD (
2553 'Return Status from update quote API is :' || x_return_status,
2554 1,
2555 'N'
2556 );
2557 END IF;
2558
2559
2560 /*bug 3500380 */
2561 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2563 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2564 RAISE FND_API.G_EXC_ERROR;
2565 END IF;
2566 --RAISE update_quote_exception;
2567
2568
2569 END IF;
2570
2571 -- set the org context , see bug 4731684
2572 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2573 aso_debug_pub.ADD (
2574 'After calling update quote Setting the org context to multi-org org_id:null',
2575 1,
2576 'N'
2577 );
2578 END IF;
2579
2580 mo_global.set_policy_context('M',null);
2581
2582
2583 FND_GLOBAL.APPS_INITIALIZE(user_id,resp_id,resp_appl_id,0);
2584
2585 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2586 aso_debug_pub.ADD( 'new debug for bug 6072144 ',1,'N');
2587 aso_debug_pub.ADD( 'After resetting back the original values ',1,'N');
2588 aso_debug_pub.ADD( 'fnd_global.user_id: '|| fnd_global.user_id,1,'N');
2589 aso_debug_pub.ADD( 'fnd_global.resp_id: '|| fnd_global.resp_id,1,'N');
2590 aso_debug_pub.ADD( 'fnd_global.resp_appl_id: '|| fnd_global.resp_appl_id,1,'N');
2591 aso_debug_pub.ADD( 'end debug for bug 6072144 ',1,'N');
2592 END IF;
2593 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2594 aso_debug_pub.ADD (
2595 'End update entity procedure ',
2596 1,
2597 'N'
2598 );
2599 END IF;
2600
2601 resultout := 'COMPLETE:T';
2602 END IF;
2603 EXCEPTION
2604
2605 /*bug 3500380 */
2606 WHEN FND_API.G_EXC_ERROR THEN
2607 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2608 aso_debug_pub.ADD (
2609 'Exception in update entity ',
2610 1,
2611 'N'
2612 );
2613 END IF;
2614 wf_core.CONTEXT (
2615 'ASOAPPRV',
2616 'update_entity',
2617 itemtype,
2618 itemkey,
2619 TO_CHAR(actid),
2620 funcmode);
2621 RAISE;
2622
2623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2624 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2625 aso_debug_pub.ADD (
2626 'Exception in update entity ',
2627 1,
2628 'N'
2629 );
2630 END IF;
2631 wf_core.CONTEXT (
2632 'ASOAPPRV',
2633 'update_entity',
2634 itemtype,
2635 itemkey,
2636 TO_CHAR(actid),
2637 funcmode);
2638 RAISE;
2639
2640 WHEN OTHERS THEN
2641 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2642 aso_debug_pub.ADD (
2643 'Exception in update entity ',
2644 1,
2645 'N'
2646 );
2647 END IF;
2648 wf_core.CONTEXT (
2649 'ASOAPPRV',
2650 'update_entity',
2651 itemtype,
2652 itemkey,
2653 TO_CHAR(actid),
2654 funcmode);
2655 RAISE;
2656
2657 END update_entity;
2658
2659 PROCEDURE update_approver_list (
2660 p_object_approval_id IN NUMBER
2661 ) IS
2662 l_approvers_changed_flag VARCHAR2 (1) := 'N';
2663 l_last_approved_approver NUMBER := 0;
2664 x_approvers_list aso_apr_pub.approvers_list_tbl_type;
2665 x_rules_list aso_apr_pub.rules_list_tbl_type;
2666 l_return_status VARCHAR2 (20);
2667 l_msg_count NUMBER;
2668 l_msg_data VARCHAR2 (2000);
2669 l_object_id NUMBER;
2670 l_object_type VARCHAR2 (240);
2671 l_application_id NUMBER;
2672 l_approver_status VARCHAR2 (30);
2673 l_approval_det_id NUMBER;
2674 l_approver_person_id NUMBER;
2675 l_approver_user_id NUMBER;
2676 l_approver_count NUMBER;
2677 j INTEGER;
2678 p_rule_id NUMBER;
2679 get_all_approvers_failed EXCEPTION;
2680 l_oam_rule_id NUMBER;
2681 l_rule_count NUMBER;
2682 l_rules_changed_flag VARCHAR2 (1) := 'N';
2683
2684 TYPE existing_approvers_tbl_type IS TABLE OF aso_apr_approval_details%ROWTYPE
2685 INDEX BY BINARY_INTEGER;
2686
2687 l_new_approvers_tbl existing_approvers_tbl_type;
2688 l_employee_id NUMBER;
2689
2690 CURSOR get_object_id (
2691 c_object_approval_id NUMBER
2692 ) IS
2693 SELECT DISTINCT object_id, object_type, application_id
2694 FROM aso_apr_obj_approvals
2695 WHERE object_approval_id = c_object_approval_id;
2696
2697 CURSOR get_existing_approvers (
2698 c_object_approval_id NUMBER
2699 ) IS
2700 SELECT approval_det_id, approver_person_id, approver_user_id,
2701 approver_status
2702 FROM aso_apr_approval_details
2703 WHERE object_approval_id = c_object_approval_id
2704 ORDER BY approver_sequence;
2705
2706 CURSOR get_approver_count (
2707 c_object_approval_id NUMBER
2708 ) IS
2709 SELECT COUNT (
2710 *
2711 )
2712 FROM aso_apr_approval_details
2713 WHERE object_approval_id = c_object_approval_id;
2714
2715 CURSOR get_old_approvers (
2716 c_object_approval_id NUMBER,
2717 c_approval_det_id NUMBER
2718 ) IS
2719 SELECT *
2720 FROM aso_apr_approval_details
2721 WHERE object_approval_id = c_object_approval_id
2722 AND approval_det_id = c_approval_det_id;
2723
2724 CURSOR get_existing_rules (
2725 c_object_approval_id NUMBER
2726 ) IS
2727 SELECT oam_rule_id
2728 FROM aso_apr_rules
2729 WHERE object_approval_id = c_object_approval_id
2730 ORDER BY rule_id;
2731
2732 CURSOR get_rule_count (
2733 c_object_approval_id NUMBER
2734 ) IS
2735 SELECT COUNT (
2736 *
2737 )
2738 FROM aso_apr_rules
2739 WHERE object_approval_id = c_object_approval_id;
2740
2741 cursor get_employee_id(l_user_id NUMBER) IS
2742 select employee_id
2743 from fnd_user
2744 where user_id = l_user_id;
2745
2746
2747 BEGIN
2748 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2749 aso_debug_pub.ADD (
2750 'Begin update approver list procedure ',
2751 1,
2752 'N'
2753 );
2754 END IF;
2755 g_user_id := fnd_global.user_id;
2756 -- get the latest list of approvers
2757 OPEN get_object_id (
2758 p_object_approval_id
2759 );
2760 FETCH get_object_id INTO l_object_id, l_object_type, l_application_id;
2761 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2762 aso_debug_pub.ADD (
2763 'Object ID :' || l_object_id,
2764 1,
2765 'N'
2766 );
2767 aso_debug_pub.ADD (
2768 'Object Type :' || l_object_type,
2769 1,
2770 'N'
2771 );
2772 aso_debug_pub.ADD (
2773 'application id :' || l_application_id,
2774 1,
2775 'N'
2776 );
2777 END IF;
2778 -- calling the get all approvers to get the latest list of approvers
2779 -- please note that we are passing the clear transaction flag as false
2780 -- this is to ensure that get all approvers does not clear transactions
2781
2782 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2783 aso_debug_pub.ADD (
2784 'Calling get_all_approvers',
2785 1,
2786 'N'
2787 );
2788 END IF;
2789 aso_apr_int.get_all_approvers (
2790 1.0,
2791 fnd_api.g_false,
2792 fnd_api.g_false,
2793 l_object_id,
2794 l_object_type,
2795 l_application_id, ---p_application_id,
2796 fnd_api.g_false, --- p_clear_transaction_flag
2797 l_return_status,
2798 l_msg_count,
2799 l_msg_data,
2800 x_approvers_list,
2801 x_rules_list
2802 );
2803 CLOSE get_object_id;
2804
2805 -- Checking to find OUT NOCOPY /* file.sql.39 change */ if call to get_all_approvers was successfull
2806 IF l_return_status <> fnd_api.g_ret_sts_success
2807 THEN
2808 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2809 aso_debug_pub.ADD (
2810 'Status returned from get_all_approvers procedure :' || l_return_status,
2811 1,
2812 'N'
2813 );
2814 END IF;
2815 -- RAISE get_all_approvers_failed;
2816 RETURN;
2817 END IF;
2818
2819 -- fix for bug 4590633
2820
2821 for i in 1..x_approvers_list.count loop
2822
2823 IF ((x_approvers_list(i).approver_person_id is null) or (x_approvers_list(i).approver_person_id = fnd_api.g_miss_num) and
2824 (x_approvers_list(i).approver_user_id is not null) and (x_approvers_list(i).approver_user_id <> fnd_api.g_miss_num)) then
2825
2826 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2827 aso_debug_pub.ADD ('Person_id is null from AME Hence deriving it from user_id',1,'N');
2828 END IF;
2829
2830 open get_employee_id(x_approvers_list(i).approver_user_id);
2831 fetch get_employee_id into x_approvers_list(i).approver_person_id;
2832 close get_employee_id;
2833
2834 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2835 aso_debug_pub.ADD ('Derived person_id is: '||to_char(x_approvers_list(i).approver_person_id),1,'N');
2836 END IF;
2837
2838 END IF;
2839 end loop;
2840
2841 -- end of fix for bug 4590633
2842
2843 --- comparing the count between old and new list
2844 OPEN get_approver_count (
2845 p_object_approval_id
2846 );
2847 FETCH get_approver_count INTO l_approver_count;
2848 CLOSE get_approver_count;
2849
2850 IF l_approver_count <> x_approvers_list.COUNT
2851 THEN
2852 l_approvers_changed_flag := 'Y';
2853 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2854 aso_debug_pub.ADD (
2855 'The approvers list has changed ',
2856 1,
2857 'N'
2858 );
2859 END IF;
2860 ELSE
2861 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2862 aso_debug_pub.ADD (
2863 'Comparing the old list from table and the latest list ',
2864 1,
2865 'N'
2866 );
2867 END IF;
2868 -- first loop to find OUT NOCOPY /* file.sql.39 change */ if the data has changed
2869 OPEN get_existing_approvers (
2870 p_object_approval_id
2871 );
2872
2873 FOR i IN 1 .. x_approvers_list.COUNT
2874 LOOP
2875 FETCH get_existing_approvers INTO l_approval_det_id,
2876 l_approver_person_id,
2877 l_approver_user_id,
2878 l_approver_status;
2879 EXIT WHEN get_existing_approvers%NOTFOUND;
2880
2881 -- Make sure that if miss num is passed back from ame api, then it is
2882 -- converted into a null
2883
2884 IF x_approvers_list (
2885 i
2886 ).approver_person_id = fnd_api.g_miss_num
2887 THEN
2888 x_approvers_list (
2889 i
2890 ).approver_person_id := NULL;
2891 END IF;
2892
2893 IF x_approvers_list (
2894 i
2895 ).approver_user_id = fnd_api.g_miss_num
2896 THEN
2897 x_approvers_list (
2898 i
2899 ).approver_user_id := NULL;
2900 END IF;
2901
2902 IF ((l_approver_person_id <> x_approvers_list (
2903 i
2904 ).approver_person_id
2905 )
2906 OR (l_approver_user_id <> x_approvers_list (
2907 i
2908 ).approver_user_id
2909 )
2910 )
2911 THEN
2912 l_approvers_changed_flag := 'Y';
2913 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2914 aso_debug_pub.ADD (
2915 'The approvers list has changed ',
2916 1,
2917 'N'
2918 );
2919 END IF;
2920 EXIT;
2921 ELSE
2922 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2923 aso_debug_pub.ADD (
2924 'The approvers list has not changed ',
2925 1,
2926 'N'
2927 );
2928 END IF;
2929 l_approvers_changed_flag := 'N';
2930 END IF;
2931 END LOOP;
2932
2933 CLOSE get_existing_approvers;
2934 END IF;
2935
2936 -- if the data has changed
2937
2938 IF l_approvers_changed_flag = 'Y'
2939 THEN
2940 -- copying latest list into the new data structure ( x to y)
2941
2942 FOR i IN 1 .. x_approvers_list.COUNT
2943 LOOP
2944 l_new_approvers_tbl (
2945 i
2946 ).approver_user_id :=
2947 x_approvers_list (
2948 i
2949 ).approver_user_id;
2950 l_new_approvers_tbl (
2951 i
2952 ).approver_person_id :=
2953 x_approvers_list (
2954 i
2955 ).approver_person_id;
2956 l_new_approvers_tbl (
2957 i
2958 ).approver_sequence := i;
2959 -- Initialize the manadatory columns
2960 l_new_approvers_tbl (
2961 i
2962 ).creation_date := SYSDATE;
2963 l_new_approvers_tbl (
2964 i
2965 ).last_update_date := SYSDATE;
2966 l_new_approvers_tbl (
2967 i
2968 ).object_approval_id := p_object_approval_id;
2969 END LOOP;
2970
2971 -- comparing new data structure and existing approvers in database and if person or user id matches, copying
2972 -- existing approvers into the new data structure ( comparing Y and E and copying E to Y )
2973
2974 FOR i IN get_existing_approvers (
2975 p_object_approval_id
2976 )
2977 LOOP
2978 FOR k IN 1 .. l_new_approvers_tbl.COUNT
2979 LOOP
2980 IF ((i.approver_person_id = l_new_approvers_tbl (
2981 k
2982 ).approver_person_id
2983 )
2984 OR (i.approver_user_id = l_new_approvers_tbl (
2985 k
2986 ).approver_user_id
2987 )
2988 )
2989 THEN
2990 OPEN get_old_approvers (
2991 p_object_approval_id,
2992 i.approval_det_id
2993 );
2994 FETCH get_old_approvers INTO l_new_approvers_tbl (
2995 k
2996 );
2997 CLOSE get_old_approvers;
2998 END IF;
2999 END LOOP;
3000 END LOOP;
3001
3002 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3003 aso_debug_pub.ADD (
3004 'Deleting approvers from detail table where object_approval_id :'
3005 || p_object_approval_id,
3006 1,
3007 'N'
3008 );
3009 END IF;
3010
3011 DELETE FROM aso_apr_approval_details
3012 WHERE object_approval_id = p_object_approval_id;
3013
3014 l_approver_status := NULL;
3015 -- third loop to insert the data
3016
3017 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3018 aso_debug_pub.ADD (
3019 'New Approver count is ' || TO_CHAR (
3020 l_new_approvers_tbl.COUNT
3021 ),
3022 1,
3023 'N'
3024 );
3025 END IF;
3026 -- traversing the data structure from bottom up
3027 j := l_new_approvers_tbl.COUNT;
3028
3029 WHILE j <> 0
3030 LOOP
3031 -- if new list has more records than the old list , update the new approvers with pend status
3032 IF l_new_approvers_tbl (
3033 j
3034 ).approver_status IS NULL
3035 THEN
3036 l_new_approvers_tbl (
3037 j
3038 ).approver_status := 'NOSUBMIT';
3039 END IF;
3040
3041 -- because of the new list if the last approver is skipped, make him pending
3042 IF ((l_new_approvers_tbl (
3043 j
3044 ).approver_status = 'SKIP'
3045 )
3046 AND (j = l_new_approvers_tbl.COUNT)
3047 )
3048 THEN
3049 l_new_approvers_tbl (
3050 j
3051 ).approver_status := 'NOSUBMIT';
3052 END IF;
3053
3054 -- Mark the highest authority who approved in the old list
3055
3056 IF ((l_new_approvers_tbl (
3057 j
3058 ).approver_status IN ('APPR', 'SKIP')
3059 )
3060 AND (l_last_approved_approver = 0)
3061 )
3062 THEN
3063 l_last_approved_approver := j;
3064 END IF;
3065
3066 -- Make the approvers who are below the old highest authority as skipped
3067 IF ((j < l_last_approved_approver)
3068 AND (l_new_approvers_tbl (
3069 j
3070 ).approver_status = 'NOSUBMIT'
3071 )
3072 )
3073 THEN
3074 l_new_approvers_tbl (
3075 j
3076 ).approver_status := 'SKIP';
3077 END IF;
3078
3079 aso_apr_approvals_pkg.detail_insert_row (
3080 l_new_approvers_tbl (
3081 j
3082 ).approval_det_id,
3083 l_new_approvers_tbl (
3084 j
3085 ).object_approval_id,
3086 l_new_approvers_tbl (
3087 j
3088 ).approver_person_id ---p_APPROVER_PERSON_ID
3089 ,
3090 l_new_approvers_tbl (
3091 j
3092 ).approver_user_id ---p_APPROVER_USER_ID
3093 ,
3094 j -- P_APPROVER_SEQUENCE
3095 ,
3096 l_new_approvers_tbl (
3097 j
3098 ).approver_status --p_APPROVER_STATUS
3099 ,
3100 l_new_approvers_tbl (
3101 j
3102 ).approver_comments -- p_APPROVER_COMMENTS
3103 ,
3104 l_new_approvers_tbl (
3105 j
3106 ).date_sent --p_DATE_SENT
3107 ,
3108 l_new_approvers_tbl (
3109 j
3110 ).date_received -- p_DATE_RECEIVED
3111 ,
3112 l_new_approvers_tbl (
3113 j
3114 ).creation_date -- p_CREATION_DATE
3115 ,
3116 SYSDATE -- p_LAST_UPDATE_DATE
3117 ,
3118 l_new_approvers_tbl (
3119 j
3120 ).created_by -- P_CREATED_BY
3121 ,
3122 g_user_id -- P_UPDATED_BY
3123 ,
3124 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3125 ,
3126 l_new_approvers_tbl (
3127 j
3128 ).attribute1 -- p_ATTRIBUTE1
3129 ,
3130 l_new_approvers_tbl (
3131 j
3132 ).attribute2 -- p_ATTRIBUTE2
3133 ,
3134 l_new_approvers_tbl (
3135 j
3136 ).attribute3 -- p_ATTRIBUTE3
3137 ,
3138 l_new_approvers_tbl (
3139 j
3140 ).attribute4 -- p_ATTRIBUTE4
3141 ,
3142 l_new_approvers_tbl (
3143 j
3144 ).attribute5 -- p_ATTRIBUTE5
3145 ,
3146 l_new_approvers_tbl (
3147 j
3148 ).attribute6 -- p_ATTRIBUTE6
3149 ,
3150 l_new_approvers_tbl (
3151 j
3152 ).attribute7 -- p_ATTRIBUTE7
3153 ,
3154 l_new_approvers_tbl (
3155 j
3156 ).attribute8 -- p_ATTRIBUTE8
3157 ,
3158 l_new_approvers_tbl (
3159 j
3160 ).attribute9 -- p_ATTRIBUTE9
3161 ,
3162 l_new_approvers_tbl (
3163 j
3164 ).attribute10 -- p_ATTRIBUTE10
3165 ,
3166 l_new_approvers_tbl (
3167 j
3168 ).attribute11 -- p_ATTRIBUTE11
3169 ,
3170 l_new_approvers_tbl (
3171 j
3172 ).attribute12 -- p_ATTRIBUTE12
3173 ,
3174 l_new_approvers_tbl (
3175 j
3176 ).attribute13 -- p_ATTRIBUTE13
3177 ,
3178 l_new_approvers_tbl (
3179 j
3180 ).attribute14 -- p_ATTRIBUTE14
3181 ,
3182 l_new_approvers_tbl (
3183 j
3184 ).attribute15 -- p_ATTRIBUTE15
3185 ,
3186 l_new_approvers_tbl (
3187 j
3188 ).attribute16 -- p_ATTRIBUTE16
3189 ,
3190 l_new_approvers_tbl (
3191 j
3192 ).attribute17 -- p_ATTRIBUTE17
3193 ,
3194 l_new_approvers_tbl (
3195 j
3196 ).attribute18 -- p_ATTRIBUTE18
3197 ,
3198 l_new_approvers_tbl (
3199 j
3200 ).attribute19 -- p_ATTRIBUTE19
3201 ,
3202 l_new_approvers_tbl (
3203 j
3204 ).attribute20 -- p_ATTRIBUTE20
3205 ,
3206 l_new_approvers_tbl (
3207 j
3208 ).CONTEXT -- p_CONTEXT
3209 ,
3210 l_new_approvers_tbl (
3211 j
3212 ).security_group_id -- p_SECURITY_GROUP_ID
3213 ,
3214 l_new_approvers_tbl (
3215 j
3216 ).object_version_number -- p_OBJECT_VERSION_NUMBER
3217 );
3218 j := j - 1;
3219 END LOOP;
3220 END IF;
3221
3222 -- Loop to find OUT NOCOPY /* file.sql.39 change */ if the rules have changed
3223
3224 OPEN get_rule_count (
3225 p_object_approval_id
3226 );
3227 FETCH get_rule_count INTO l_rule_count;
3228 CLOSE get_rule_count;
3229
3230 IF l_rule_count <> x_rules_list.COUNT
3231 THEN
3232 l_rules_changed_flag := 'Y';
3233 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3234 aso_debug_pub.ADD (
3235 'The rules have changed ',
3236 1,
3237 'N'
3238 );
3239 END IF;
3240 ELSE
3241 OPEN get_existing_rules (
3242 p_object_approval_id
3243 );
3244
3245 FOR i IN 1 .. x_rules_list.COUNT
3246 LOOP
3247 FETCH get_existing_rules INTO l_oam_rule_id;
3248 EXIT WHEN get_existing_rules%NOTFOUND;
3249
3250 -- Make sure that if miss num is passed back from ame api, then it is
3251 -- converted into a null
3252
3253 IF x_rules_list (
3254 i
3255 ).rule_id = fnd_api.g_miss_num
3256 THEN
3257 x_rules_list (
3258 i
3259 ).rule_id := NULL;
3260 END IF;
3261
3262 IF (l_oam_rule_id <> x_rules_list (
3263 i
3264 ).rule_id
3265 )
3266 THEN
3267 l_rules_changed_flag := 'Y';
3268 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3269 aso_debug_pub.ADD (
3270 'The rules have changed ',
3271 1,
3272 'N'
3273 );
3274 END IF;
3275 EXIT;
3276 ELSE
3277 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3278 aso_debug_pub.ADD (
3279 'The rules have not changed ',
3280 1,
3281 'N'
3282 );
3283 END IF;
3284 l_rules_changed_flag := 'N';
3285 END IF;
3286 END LOOP;
3287
3288 CLOSE get_existing_rules;
3289 END IF;
3290
3291 -- refresh the rules if they are changed
3292
3293 IF l_rules_changed_flag = 'Y'
3294 THEN
3295 -- delete the existing rules
3296 DELETE FROM aso_apr_rules
3297 WHERE object_approval_id = p_object_approval_id;
3298
3299 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3300 aso_debug_pub.ADD (
3301 'Inserting rows into the rule table ',
3302 1,
3303 'N'
3304 );
3305 END IF;
3306
3307 FOR i IN 1 .. x_rules_list.COUNT
3308 LOOP
3309 aso_apr_approvals_pkg.rule_insert_row (
3310 p_rule_id,
3311 x_rules_list (
3312 i
3313 ).rule_id,
3314 x_rules_list (
3315 i
3316 ).rule_action_id,
3317 SYSDATE --p_CREATION_DATE
3318 ,
3319 g_user_id -- P_CREATED_BY
3320 ,
3321 SYSDATE -- p_LAST_UPDATE_DATE
3322 ,
3323 g_user_id -- P_UPDATED_BY
3324 ,
3325 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3326 ,
3327 p_object_approval_id,
3328 NULL -- p_ATTRIBUTE1
3329 ,
3330 NULL -- p_ATTRIBUTE2
3331 ,
3332 NULL -- p_ATTRIBUTE3
3333 ,
3334 NULL -- p_ATTRIBUTE4
3335 ,
3336 NULL -- p_ATTRIBUTE5
3337 ,
3338 NULL -- p_ATTRIBUTE6
3339 ,
3340 NULL -- p_ATTRIBUTE7
3341 ,
3342 NULL -- p_ATTRIBUTE8
3343 ,
3344 NULL -- p_ATTRIBUTE9
3345 ,
3346 NULL -- p_ATTRIBUTE10
3347 ,
3348 NULL -- p_ATTRIBUTE11
3349 ,
3350 NULL -- p_ATTRIBUTE12
3351 ,
3352 NULL -- p_ATTRIBUTE13
3353 ,
3354 NULL -- p_ATTRIBUTE14
3355 ,
3356 NULL -- p_ATTRIBUTE15
3357 ,
3358 NULL -- p_Attribute16
3359 ,
3360 NULL -- p_Attribute17
3361 ,
3362 NULL -- p_Attribute18
3363 ,
3364 NULL -- p_Attribute19
3365 ,
3366 NULL -- p_Attribute20
3367 ,
3368 NULL -- p_CONTEXT
3369 ,
3370 NULL -- p_SECURITY_GROUP_ID
3371 ,
3372 NULL -- p_OBJECT_VERSION_NUMBER
3373 );
3374 END LOOP;
3375 END IF;
3376 -- commit the work
3377 COMMIT WORK;
3378 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3379 aso_debug_pub.ADD (
3380 'End update_approver_list procedure ',
3381 1,
3382 'N'
3383 );
3384 END IF;
3385 EXCEPTION
3386 WHEN get_all_approvers_failed
3387 THEN
3388 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3389 aso_debug_pub.ADD (
3390 'Call to get_all_approvers failed in update_approver_list ',
3391 1,
3392 'N'
3393 );
3394 END IF;
3395 fnd_msg_pub.count_and_get (
3396 p_encoded => 'F',
3397 p_count => l_msg_count,
3398 p_data => l_msg_data
3399 );
3400 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3401 aso_debug_pub.ADD (
3402 'no. of FND messages :' || l_msg_count,
3403 1,
3404 'N'
3405 );
3406 END IF;
3407
3408 FOR k IN 1 .. l_msg_count
3409 LOOP
3410 l_msg_data := fnd_msg_pub.get (
3411 p_msg_index => k,
3412 p_encoded => 'F'
3413 );
3414 END LOOP;
3415
3416 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3417 aso_debug_pub.ADD (
3418 'Msg Data is' || l_msg_data,
3419 1,
3420 'N'
3421 );
3422 END IF;
3423 wf_core.CONTEXT (
3424 'ASOAPPRV',
3425 'Update_approver_list',
3426 'msg data ' || l_msg_data
3427 );
3428 RAISE;
3429 WHEN OTHERS
3430 THEN
3431 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3432 aso_debug_pub.ADD (
3433 'When others exception in update approver list procedure ',
3434 1,
3435 'N'
3436 );
3437 END IF;
3438 wf_core.CONTEXT (
3439 'ASOAPPRV',
3440 'Update_approver_list',
3441 SUBSTR (
3442 SQLERRM,
3443 1,
3444 250
3445 )
3446 );
3447 RAISE;
3448 END update_approver_list;
3449
3450 PROCEDURE last_approver_timeout_check (
3451 p_object_approval_id IN NUMBER
3452 ) IS
3453 new_approver_record ame_util.approverrecord;
3454 l_approver_sequence NUMBER;
3455 l_approval_det_id NUMBER;
3456 l_object_id NUMBER;
3457 l_object_type VARCHAR2 (240);
3458 l_application_id NUMBER;
3459
3460 CURSOR get_approvers (
3461 c_object_approval_id NUMBER
3462 ) IS
3463 SELECT approval_det_id
3464 FROM aso_apr_approval_details
3465 WHERE object_approval_id = c_object_approval_id;
3466
3467 CURSOR get_approver_sequence (
3468 c_object_approval_id NUMBER
3469 ) IS
3470 SELECT MAX (
3471 approver_sequence
3472 ) + 1
3473 FROM aso_apr_approval_details
3474 WHERE object_approval_id = c_object_approval_id;
3475
3476 CURSOR get_application_id (
3477 c_object_approval_id NUMBER
3478 ) IS
3479 SELECT DISTINCT object_id, object_type, application_id
3480 FROM aso_apr_obj_approvals aoa
3481 WHERE object_approval_id = c_object_approval_id;
3482 BEGIN
3483 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3484 aso_debug_pub.ADD (
3485 'Begin the last_approver_timeout_check prcoedure ',
3486 1,
3487 'N'
3488 );
3489 END IF;
3490 g_user_id := fnd_global.user_id;
3491 OPEN get_application_id (
3492 p_object_approval_id
3493 );
3494 FETCH get_application_id INTO l_object_id, l_object_type, l_application_id;
3495 CLOSE get_application_id;
3496 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3497 aso_debug_pub.ADD (
3498 'Object ID :' || l_object_id,
3499 1,
3500 'N'
3501 );
3502 aso_debug_pub.ADD (
3503 'Object Type :' || l_object_type,
3504 1,
3505 'N'
3506 );
3507 aso_debug_pub.ADD (
3508 'application id :' || l_application_id,
3509 1,
3510 'N'
3511 );
3512 aso_debug_pub.ADD (
3513 'Calling the AME clearall approvals API',
3514 1,
3515 'N'
3516 );
3517 END IF;
3518 ame_api.clearallapprovals (
3519 applicationidin => l_application_id,
3520 transactionidin => l_object_id,
3521 transactiontypein => l_object_type
3522 );
3523
3524 FOR i IN get_approvers (
3525 p_object_approval_id
3526 )
3527 LOOP
3528 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3529 aso_debug_pub.ADD (
3530 'calling the AME get next approver API ',
3531 1,
3532 'N'
3533 );
3534 END IF;
3535 ame_api.getnextapprover (
3536 applicationidin => l_application_id,
3537 transactionidin => l_object_id,
3538 transactiontypein => l_object_type,
3539 nextapproverout => new_approver_record
3540 );
3541 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3542 aso_debug_pub.ADD (
3543 'New Approver Person ID :' || new_approver_record.person_id,
3544 1,
3545 'N'
3546 );
3547 aso_debug_pub.ADD (
3548 'Setting approval status to no response for previous approvers ',
3549 1,
3550 'N'
3551 );
3552 END IF;
3553 new_approver_record.approval_status := ame_util.noresponsestatus;
3554 ame_api.updateapprovalstatus (
3555 applicationidin => l_application_id,
3556 transactionidin => l_object_id,
3557 transactiontypein => l_object_type,
3558 approverin => new_approver_record
3559 );
3560 END LOOP;
3561
3562 -- after getting all the approvers, try to get the next approver
3563
3564 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3565 aso_debug_pub.ADD (
3566 'calling the AME get next approver API ',
3567 1,
3568 'N'
3569 );
3570 END IF;
3571 ame_api.getnextapprover (
3572 applicationidin => l_application_id,
3573 transactionidin => l_object_id,
3574 transactiontypein => l_object_type,
3575 nextapproverout => new_approver_record
3576 );
3577 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3578 aso_debug_pub.ADD (
3579 'New Approver Person ID :' || new_approver_record.person_id,
3580 1,
3581 'N'
3582 );
3583 END IF;
3584
3585 IF (((new_approver_record.person_id IS NOT NULL)
3586 AND (new_approver_record.person_id <> fnd_api.g_miss_num)
3587 )
3588 OR ((new_approver_record.user_id IS NOT NULL)
3589 AND (new_approver_record.user_id <> fnd_api.g_miss_num)
3590 )
3591 )
3592 THEN
3593 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3594 aso_debug_pub.ADD (
3595 'Additional Approver found ',
3596 1,
3597 'N'
3598 );
3599 END IF;
3600 OPEN get_approver_sequence (
3601 p_object_approval_id
3602 );
3603 FETCH get_approver_sequence INTO l_approver_sequence;
3604 CLOSE get_approver_sequence;
3605 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3606 aso_debug_pub.ADD (
3607 'Inserting the new approver into the detail table ',
3608 1,
3609 'N'
3610 );
3611 END IF;
3612 aso_apr_approvals_pkg.detail_insert_row (
3613 l_approval_det_id,
3614 p_object_approval_id,
3615 new_approver_record.person_id --p_APPROVER_PERSON_ID
3616 ,
3617 new_approver_record.user_id --p_APPROVER_USER_ID
3618 ,
3619 l_approver_sequence -- P_APPROVER_SEQUENCE
3620 ,
3621 'NOSUBMIT' --p_APPROVER_STATUS
3622 ,
3623 NULL -- p_APPROVER_COMMENTS
3624 ,
3625 NULL --p_DATE_SENT
3626 ,
3627 NULL -- p_DATE_RECEIVED
3628 ,
3629 SYSDATE -- p_CREATION_DATE
3630 ,
3631 SYSDATE -- p_LAST_UPDATE_DATE
3632 ,
3633 g_user_id -- P_CREATED_BY
3634 ,
3635 g_user_id -- P_UPDATED_BY
3636 ,
3637 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
3638 ,
3639 NULL -- p_ATTRIBUTE1
3640 ,
3641 NULL -- p_ATTRIBUTE2
3642 ,
3643 NULL -- p_ATTRIBUTE3
3644 ,
3645 NULL -- p_ATTRIBUTE4
3646 ,
3647 NULL -- p_ATTRIBUTE5
3648 ,
3649 NULL -- p_ATTRIBUTE6
3650 ,
3651 NULL -- p_ATTRIBUTE7
3652 ,
3653 NULL -- p_ATTRIBUTE8
3654 ,
3655 NULL -- p_ATTRIBUTE9
3656 ,
3657 NULL -- p_ATTRIBUTE10
3658 ,
3659 NULL -- p_ATTRIBUTE11
3660 ,
3661 NULL -- p_ATTRIBUTE12
3662 ,
3663 NULL -- p_ATTRIBUTE13
3664 ,
3665 NULL -- p_ATTRIBUTE14
3666 ,
3667 NULL -- p_ATTRIBUTE15
3668 ,
3669 NULL -- p_Attribute16
3670 ,
3671 NULL -- p_Attribute17
3672 ,
3673 NULL -- p_Attribute18
3674 ,
3675 NULL -- p_Attribute19
3676 ,
3677 NULL -- p_Attribute20
3678 ,
3679 NULL -- p_CONTEXT
3680 ,
3681 NULL -- p_SECURITY_GROUP_ID
3682 ,
3683 NULL -- p_OBJECT_VERSION_NUMBER
3684 );
3685 ELSE
3686 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3687 aso_debug_pub.ADD (
3688 'No Additional Approvers found ',
3689 1,
3690 'N'
3691 );
3692 END IF;
3693 RETURN;
3694 END IF;
3695
3696 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3697 aso_debug_pub.ADD (
3698 'End of last_approver_timeout_check procedure ',
3699 1,
3700 'N'
3701 );
3702 END IF;
3703 EXCEPTION
3704 WHEN OTHERS
3705 THEN
3706 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3707 aso_debug_pub.ADD (
3708 'Exception in Last_Approver_Timeout_Check ',
3709 1,
3710 'N'
3711 );
3712 END IF;
3713 RETURN;
3714 END last_approver_timeout_check;
3715
3716 PROCEDURE send_cancel_notification (
3717 itemtype IN VARCHAR2,
3718 itemkey IN VARCHAR2,
3719 actid IN NUMBER,
3720 funcmode IN VARCHAR2,
3721 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3722 ) IS
3723 l_message VARCHAR2 (240);
3724 l_notifenabled VARCHAR2 (3) := 'Y';
3725 l_orgid NUMBER := NULL;
3726 l_approval_id NUMBER;
3727 x_return_status VARCHAR2 (240);
3728 x_msg_count NUMBER;
3729 --x_msg_data VARCHAR2 (240);
3730 x_msg_data VARCHAR2 (2000); -- bug 13508417
3731 l_msgenabled VARCHAR2 (3) := 'Y';
3732 l_notifname VARCHAR2 (240);
3733 get_message_error EXCEPTION;
3734
3735 BEGIN
3736 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3737 aso_debug_pub.ADD (
3738 'Begin SEND_CANCEL_NOTIFICATION Procedure',
3739 1,
3740 'N'
3741 );
3742 aso_debug_pub.ADD (
3743 'actid is ' || TO_CHAR (
3744 actid
3745 ),
3746 1,
3747 'N'
3748 );
3749 END IF;
3750
3751 IF funcmode = 'RUN'
3752 THEN
3753 l_message := wf_engine.getitemattrtext (
3754 itemtype,
3755 itemkey,
3756 'MESSAGE'
3757 );
3758 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3759 aso_debug_pub.ADD (
3760 'Message is ' || l_message,
3761 1,
3762 'N'
3763 );
3764 END IF;
3765
3766 -- Check for FYI Cancel Message and if so make the attribute as Cancel Message to approver
3767 IF ((l_message LIKE 'REQUEST_CANCELLED_FYI%') or (l_message LIKE 'OA_REQUEST_CANCELLED_FYI%'))
3768 THEN
3769 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3770 aso_debug_pub.ADD (
3771 'Setting message to OA_REQUEST_CANCEL_FYI_TO_APPR',
3772 1,
3773 'N'
3774 );
3775 END IF;
3776 l_message := 'OA_REQ_CANCEL_FYI_TO_APPR';
3777 ELSE
3778 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3779 aso_debug_pub.ADD (
3780 'Setting message to OA_REQUEST_CANCELLED_FYI',
3781 1,
3782 'N'
3783 );
3784 END IF;
3785 l_message := 'OA_REQUEST_CANCELLED_FYI';
3786 END IF;
3787
3788 wf_engine.setitemattrtext (
3789 itemtype => itemtype,
3790 itemkey => itemkey,
3791 aname => 'MESSAGE',
3792 avalue => l_message
3793 );
3794 -- Please note that the notification event name is same as message name
3795
3796 l_notifname := wf_engine.getitemattrtext (
3797 itemtype,
3798 itemkey,
3799 'MESSAGE'
3800 );
3801 l_notifname := 'ASO_' || l_notifname;
3802 l_notifenabled := ibe_wf_notif_setup_pvt.check_notif_enabled (
3803 l_notifname
3804 );
3805 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3806 aso_debug_pub.ADD (
3807 'Notification Name: ' || l_notifname || ' Enabled: ' || l_notifenabled,
3808 1,
3809 'N'
3810 );
3811 END IF;
3812
3813 IF l_notifenabled = 'Y'
3814 THEN
3815 -- Get the approval id
3816 l_approval_id := wf_engine.getitemattrnumber (
3817 itemtype,
3818 itemkey,
3819 'APPROVALID'
3820 );
3821 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3822 aso_debug_pub.ADD (
3823 'Approval Id is ' || l_approval_id,
3824 1,
3825 'N'
3826 );
3827 END IF;
3828 -- get the org id
3829 /*OPEN get_org_id (
3830 l_approval_id
3831 );
3832 FETCH get_org_id INTO l_orgid;
3833 CLOSE get_org_id; */
3834 l_orgid := wf_engine.getitemattrnumber (
3835 itemtype,
3836 itemkey,
3837 'ORGID'
3838 );
3839
3840 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3841 aso_debug_pub.ADD (
3842 'Org Id is ' || l_orgid,
3843 1,
3844 'N'
3845 );
3846 END IF;
3847 -- Retreive the message name for that event
3848 x_return_status := fnd_api.g_ret_sts_success;
3849
3850 ibe_wf_msg_mapping_pvt.retrieve_msg_mapping (
3851 p_org_id => l_orgid,
3852 p_msite_id => NULL,
3853 p_user_type => 'ALL',
3854 p_notif_name => l_notifname,
3855 x_enabled_flag => l_msgenabled,
3856 x_wf_message_name => l_message,
3857 x_return_status => x_return_status,
3858 x_msg_data => x_msg_data,
3859 x_msg_count => x_msg_count
3860 );
3861
3862 -- Check if the call to MSG mapping API was succssfull
3863 -- bug 3295179
3864 IF x_return_status <> fnd_api.g_ret_sts_success
3865 --IF x_msg_count > 0
3866 THEN
3867 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3868 aso_debug_pub.ADD (
3869 'Message count from MSG mapping API :' || x_msg_count,
3870 1,
3871 'N'
3872 );
3873 END IF;
3874 RAISE get_message_error;
3875 END IF;
3876 ELSE
3877 -- If the notification is not enabled
3878 resultout := 'COMPLETE';
3879 END IF;
3880
3881 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3882 aso_debug_pub.ADD (
3883 'Calling the standard notify process ',
3884 1,
3885 'N'
3886 );
3887 END IF;
3888 wf_standard.notify (
3889 itemtype,
3890 itemkey,
3891 actid,
3892 funcmode,
3893 resultout
3894 );
3895 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3896 aso_debug_pub.ADD (
3897 'End SEND_CANCEL_NOTIFICATION Procedure',
3898 1,
3899 'N'
3900 );
3901 END IF;
3902 RETURN;
3903 END IF;
3904
3905 IF (funcmode = 'CANCEL')
3906 THEN
3907 resultout := 'COMPLETE';
3908 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3909 aso_debug_pub.ADD (
3910 'End of SEND_CANCEL_NOTIFICATION Procedure',
3911 1,
3912 'N'
3913 );
3914 END IF;
3915 RETURN;
3916 END IF;
3917
3918 resultout := '';
3919 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3920 aso_debug_pub.ADD (
3921 'End of SEND_CANCEL_NOTIFICATION Procedure',
3922 1,
3923 'N'
3924 );
3925 END IF;
3926 RETURN;
3927 EXCEPTION
3928 WHEN get_message_error
3929 THEN
3930 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3931 aso_debug_pub.ADD (
3932 'Exception in SEND_NOTIFICATION Proc SqlCode :' || SQLERRM,
3933 1,
3934 'N'
3935 );
3936 END IF;
3937 wf_core.CONTEXT (
3938 'ASOAPPRV',
3939 'SEND_CANCEL_NOTIFICATION',
3940 'Error in retreiving Notification Message',
3941 'Message Name: ' || l_message || ' Enabled: ' || l_msgenabled
3942 );
3943 RAISE;
3944 WHEN OTHERS
3945 THEN
3946 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3947 aso_debug_pub.ADD (
3948 'Exception in SEND_CANCEL_NOTIFICATION Process SqlCode :' || SQLERRM,
3949 1,
3950 'N'
3951 );
3952 END IF;
3953 wf_core.CONTEXT (
3954 'ASOAPPRV',
3955 'SEND_CANCEL_NOTIFICATION',
3956 itemtype,
3957 itemkey,
3958 TO_CHAR (
3959 actid
3960 ),
3961 funcmode
3962 );
3963 RAISE;
3964 END send_cancel_notification;
3965
3966 PROCEDURE approver_details_doc (
3967 document_id IN VARCHAR2,
3968 display_type IN VARCHAR2,
3969 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3970 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3971 ) IS
3972 itemtype VARCHAR2 (30);
3973 itemkey VARCHAR2 (30);
3974 l_approval_id NUMBER;
3975 l_approver_user_name VARCHAR2 (240);
3976 l_approver_display_name VARCHAR2 (240);
3977 l_approver_status VARCHAR2 (10);
3978 l_approver_comments VARCHAR2 (4000);
3979 l_attribute_tbl aso_attribute_label_tbl_type;
3980
3981 CURSOR approver_details (
3982 c_object_approval_id NUMBER
3983 ) IS
3984 SELECT approver_user_id, approver_person_id, fl.meaning, approver_comments
3985 FROM aso_apr_approval_details apd, aso_lookups fl
3986 WHERE apd.approver_status = fl.lookup_code
3987 AND object_approval_id = c_object_approval_id
3988 AND fl.lookup_type = 'ASO_APPROVER_STATUS'
3989 ORDER BY approver_sequence;
3990 BEGIN
3991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3992 aso_debug_pub.ADD (
3993 'Begin APPROVER_DETAILS_DOC procedure ',
3994 1,
3995 'N'
3996 );
3997 END IF;
3998 itemtype := NVL (
3999 SUBSTR (
4000 document_id,
4001 1,
4002 INSTR (
4003 document_id,
4004 ':'
4005 ) - 1
4006 ),
4007 'ASOAPPRV'
4008 );
4009 itemkey := SUBSTR (
4010 document_id,
4011 INSTR (
4012 document_id,
4013 ':'
4014 ) + 1
4015 ) || 'HED';
4016 l_approval_id := wf_engine.getitemattrnumber (
4017 itemtype => itemtype,
4018 itemkey => itemkey,
4019 aname => 'APPROVALID'
4020 );
4021 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4022 aso_debug_pub.ADD (
4023 'ItemType is:' || itemtype,
4024 1,
4025 'N'
4026 );
4027 aso_debug_pub.ADD (
4028 'ItemKey is :' || itemkey,
4029 1,
4030 'N'
4031 );
4032 aso_debug_pub.ADD (
4033 'Approval ID :' || l_approval_id,
4034 1,
4035 'N'
4036 );
4037 END IF;
4038 -- get the attribute label
4039 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4040 aso_debug_pub.ADD (
4041 'Calling the get_attribute_label procedure',
4042 1,
4043 'N'
4044 );
4045 END IF;
4046 get_attribute_label (
4047 l_approval_id,
4048 l_attribute_tbl
4049 );
4050
4051 -- Create an html text buffer
4052 IF (display_type = 'text/html')
4053 THEN
4054 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4055 aso_debug_pub.ADD (
4056 'Display type is text/html',
4057 1,
4058 'N'
4059 );
4060 END IF;
4061 document := document
4062 || '<span class=sectionHeader1>'
4063 || l_attribute_tbl (
4064 11
4065 )
4066 || '</span>';
4067 document :=
4068 document
4069 || '<table class=OraBGAccentDark width="75%" cellpadding="1", cellspacing="1" border="0">';
4070 document :=
4071 document
4072 || '<tr> <td class="tableSmallHeaderCell" align="center">'
4073 || l_attribute_tbl (
4074 12
4075 )
4076 || '</td>';
4077 document := document
4078 || '<td class="tableSmallHeaderCell" align="center">'
4079 || l_attribute_tbl (
4080 13
4081 )
4082 || '</td>';
4083 document := document || '</tr>';
4084
4085 FOR i IN approver_details (
4086 l_approval_id
4087 )
4088 LOOP
4089 l_approver_display_name :=
4090 aso_apr_int.get_approver_name (
4091 i.approver_user_id,
4092 i.approver_person_id
4093 );
4094 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4095 aso_debug_pub.ADD (
4096 'Approver Name is ' || l_approver_display_name,
4097 1,
4098 'N'
4099 );
4100 END IF;
4101 document := document || '<tr>';
4102 document := document
4103 || '<td class="tableDataCell">'
4104 || l_approver_display_name
4105 || '</td>';
4106 document := document
4107 || '<td class="tableDataCell">'
4108 || i.meaning
4109 || '</td>';
4110 document := document || '</tr>';
4111 END LOOP;
4112
4113 document := document || '</table>';
4114 document_type := 'text/html';
4115 END IF;
4116
4117 -- Create a plain text buffer
4118
4119 IF (display_type = 'text/plain')
4120 THEN
4121 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4122 aso_debug_pub.ADD (
4123 'Display type is text/plain',
4124 1,
4125 'N'
4126 );
4127 END IF;
4128 document := document || fnd_global.local_chr (
4129 10
4130 );
4131 document := document || l_attribute_tbl (
4132 11
4133 );
4134 document := document || fnd_global.local_chr (
4135 10
4136 );
4137
4138 FOR i IN approver_details (
4139 l_approval_id
4140 )
4141 LOOP
4142 -- get the approver name
4143 l_approver_display_name :=
4144 aso_apr_int.get_approver_name (
4145 i.approver_user_id,
4146 i.approver_person_id
4147 );
4148 document := document || fnd_global.local_chr (
4149 10
4150 );
4151 document := document || l_attribute_tbl (
4152 12
4153 );
4154 document := document || ': ';
4155 document := document || l_approver_display_name;
4156 document := document || fnd_global.local_chr (
4157 10
4158 );
4159 document := document || l_attribute_tbl (
4160 13
4161 );
4162 document := document || ': ';
4163 document := document || i.meaning;
4164 END LOOP;
4165
4166 document_type := 'text/plain';
4167 END IF;
4168
4169 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4170 aso_debug_pub.ADD (
4171 'End APPROVER_DETAILS_DOC procedure ',
4172 1,
4173 'N'
4174 );
4175 END IF;
4176 EXCEPTION
4177 WHEN OTHERS
4178 THEN
4179 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4180 aso_debug_pub.ADD (
4181 'Exception in APPROVER_DETAILS_DOC SqlCode :' || SQLERRM,
4182 1,
4183 'N'
4184 );
4185 END IF;
4186 wf_core.CONTEXT (
4187 'ASOAPPRV',
4188 'APPROVER_DETAILS_DOC',
4189 itemtype,
4190 itemkey
4191 );
4192 RAISE;
4193 END approver_details_doc;
4194
4195 PROCEDURE quote_summary_doc (
4196 document_id IN VARCHAR2,
4197 display_type IN VARCHAR2,
4198 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4199 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4200 ) IS
4201 itemtype VARCHAR2 (30);
4202 itemkey VARCHAR2 (30);
4203 l_approval_id NUMBER;
4204 l_quote_name VARCHAR2 (240);
4205 l_quote_version VARCHAR2 (240);
4206 l_quote_number VARCHAR2 (240);
4207 l_quote_price VARCHAR2 (240);
4208 l_customer_name VARCHAR2 (240);
4209 l_account_number VARCHAR2 (240);
4210 l_contact_name VARCHAR2 (240);
4211 l_opportunity_name VARCHAR2 (240);
4212 l_expiration_date DATE;
4213 l_attribute_tbl aso_attribute_label_tbl_type;
4214 -- hyang performance fix, bug 2860045
4215 l_quote_header_id NUMBER;
4216 l_party_type VARCHAR2(240);
4217 CURSOR get_object_details (
4218 c_approval_id NUMBER
4219 ) IS
4220 SELECT quote_header_id, quote_name, quote_number, quote_expiration_date
4221 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
4222 WHERE qha.quote_header_id = aoa.object_id
4223 AND aoa.object_approval_id = c_approval_id;
4224
4225 CURSOR get_customer_name (
4226 c_quote_header_id NUMBER
4227 ) IS
4228 SELECT hp.party_name
4229 FROM aso_quote_headers_all qha,
4230 hz_parties hp
4231 WHERE qha.cust_party_id = hp.party_id
4232 AND qha.quote_header_id = c_quote_header_id;
4233
4234 -- bug 3934660 (put outer join)
4235 CURSOR get_account_number (
4236 c_quote_header_id NUMBER
4237 ) IS
4238 SELECT hca.account_number
4239 FROM hz_cust_accounts hca,
4240 aso_quote_headers_all qha
4241 WHERE qha.cust_account_id = hca.cust_account_id(+)
4242 AND qha.quote_header_id = c_quote_header_id;
4243
4244 CURSOR get_opportunity_name (
4245 c_approval_id NUMBER
4246 ) IS
4247 SELECT ala.description
4248 FROM as_leads_all ala,
4249 aso_quote_related_objects qro,
4250 aso_apr_obj_approvals aoa
4251 WHERE ala.lead_id = qro.object_id
4252 AND qro.relationship_type_code = 'OPP_QUOTE'
4253 AND qro.quote_object_id = aoa.object_id
4254 AND aoa.object_approval_id = c_approval_id;
4255
4256 -- bug 3934660
4257
4258 CURSOR get_party_type (c_approval_id NUMBER) IS
4259 SELECT P.PARTY_TYPE
4260 FROM hz_parties p, aso_quote_headers_all qh,aso_apr_obj_approvals aoa
4261 WHERE p.party_id = qh.party_id
4262 AND qh.quote_header_id = aoa.object_id
4263 AND aoa.object_approval_id = c_approval_id;
4264
4265 -- note that UI shows both contact and employees in drop down list,hence
4266 -- query uses both contact and employee
4267 CURSOR get_contact_name (
4268 c_approval_id NUMBER
4269 ) IS
4270 SELECT party_name
4271 FROM hz_parties p, hz_relationships r, aso_quote_headers_all qh,aso_apr_obj_approvals aoa
4272 WHERE p.party_id = r.object_id
4273 AND r.party_id = qh.party_id
4274 AND r.subject_id = qh.cust_party_id
4275 AND r.object_type = 'PERSON'
4276 AND r.relationship_code IN ('CONTACT','EMPLOYER_OF')
4277 AND qh.quote_header_id = aoa.object_id
4278 AND aoa.object_approval_id = c_approval_id;
4279
4280 BEGIN
4281 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4282 aso_debug_pub.ADD (
4283 'Begin QUOTE_SUMMARY_DOC procedure ',
4284 1,
4285 'N'
4286 );
4287 END IF;
4288 itemtype := NVL (
4289 SUBSTR (
4290 document_id,
4291 1,
4292 INSTR (
4293 document_id,
4294 ':'
4295 ) - 1
4296 ),
4297 'ASOAPPRV'
4298 );
4299 itemkey := SUBSTR (
4300 document_id,
4301 INSTR (
4302 document_id,
4303 ':'
4304 ) + 1
4305 ) || 'HED';
4306 l_approval_id := wf_engine.getitemattrnumber (
4307 itemtype => itemtype,
4308 itemkey => itemkey,
4309 aname => 'APPROVALID'
4310 );
4311 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4312 aso_debug_pub.ADD (
4313 'ItemType is:' || itemtype,
4314 1,
4315 'N'
4316 );
4317 aso_debug_pub.ADD (
4318 'ItemKey is :' || itemkey,
4319 1,
4320 'N'
4321 );
4322 aso_debug_pub.ADD (
4323 'Approval ID :' || l_approval_id,
4324 1,
4325 'N'
4326 );
4327 END IF;
4328 -- get the quote name, quote number and expiration date
4329 -- hyang performance fix bug 2860045, added l_quote_header_id
4330 OPEN get_object_details (
4331 l_approval_id
4332 );
4333 FETCH get_object_details INTO l_quote_header_id, l_quote_name, l_quote_number, l_expiration_date;
4334 CLOSE get_object_details;
4335 -- get the customer name and account number
4336 OPEN get_customer_name (
4337 l_quote_header_id
4338 );
4339 FETCH get_customer_name INTO l_customer_name;
4340 CLOSE get_customer_name;
4341 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4342 aso_debug_pub.ADD (
4343 'Customer Name is ' || l_customer_name,
4344 1,
4345 'N'
4346 );
4347 END IF;
4348 OPEN get_account_number (
4349 l_quote_header_id
4350 );
4351 FETCH get_account_number INTO l_account_number;
4352 CLOSE get_account_number;
4353 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4354 aso_debug_pub.ADD (
4355 'Customer Account Number is ' || l_account_number,
4356 1,
4357 'N'
4358 );
4359 END IF;
4360
4361 -- get the opportunity name for the quote
4362 OPEN get_opportunity_name (
4363 l_approval_id
4364 );
4365 FETCH get_opportunity_name INTO l_opportunity_name;
4366 CLOSE get_opportunity_name;
4367 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4368 aso_debug_pub.ADD (
4369 'Opportunity Name :' || l_opportunity_name,
4370 1,
4371 'N'
4372 );
4373 END IF;
4374 -- get the contact name for the quote
4375
4376 OPEN get_party_type(l_approval_id);
4377 FETCH get_party_type INTO l_party_type;
4378 CLOSE get_party_type;
4379
4380 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
4381 OPEN get_contact_name (
4382 l_approval_id
4383 );
4384 FETCH get_contact_name INTO l_contact_name;
4385 CLOSE get_contact_name;
4386 END IF;
4387 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4388 aso_debug_pub.ADD (
4389 'Contact Name :' || l_contact_name,
4390 1,
4391 'N'
4392 );
4393 END IF;
4394 -- get the attribute labels
4395
4396 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4397 aso_debug_pub.ADD (
4398 'Calling the get_attribute_label proceure ',
4399 1,
4400 'N'
4401 );
4402 END IF;
4403 get_attribute_label (
4404 l_approval_id,
4405 l_attribute_tbl
4406 );
4407
4408 -- Create an html text buffer
4409 IF (display_type = 'text/html')
4410 THEN
4411 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4412 aso_debug_pub.ADD (
4413 'Display type is text/html',
4414 1,
4415 'N'
4416 );
4417 END IF;
4418 document := document
4419 || '<span class=sectionHeader1>'
4420 || l_attribute_tbl (
4421 2
4422 )
4423 || '</span>';
4424 document :=
4425 document
4426 || '<table cellspacing=1 cellpadding=1 width="75%" border=0>';
4427 document := document
4428 || '<tr> <td class="prompt" align="right" nowrap>';
4429 document := document || l_attribute_tbl (
4430 3
4431 ) || '</td>';
4432 document := document
4433 || '<td class=datareadonly>'
4434 || l_quote_name
4435 || '</td>';
4436 document := document || '<td class="prompt" align="right" nowrap>';
4437 document := document || l_attribute_tbl (
4438 4
4439 ) || '</td>';
4440 document := document
4441 || '<td class=datareadonly>'
4442 || l_quote_number
4443 || '</td></tr>';
4444 document := document || '<tr>';
4445 document := document || '<td class="prompt" align="right" nowrap> ';
4446 document := document || l_attribute_tbl (
4447 5
4448 ) || '</td>';
4449 document := document
4450 || '<td class=datareadonly>'
4451 || l_customer_name
4452 || '</td>';
4453 document := document
4454 || '<td class="prompt" align="right" nowrap>'
4455 || l_attribute_tbl (
4456 6
4457 )
4458 || '</td>';
4459 document := document
4460 || '<td class=datareadonly>'
4461 || l_account_number
4462 || ' </td></tr>';
4463 document := document
4464 || '<tr> <td class="prompt" align="right" nowrap>'
4465 || l_attribute_tbl (
4466 7
4467 )
4468 || '</td>';
4469 document := document
4470 || '<td class=datareadonly>'
4471 || l_contact_name
4472 || '</td>';
4473 document := document
4474 || '<td class="prompt" align="right" nowrap>'
4475 || l_attribute_tbl (
4476 8
4477 )
4478 || '</td>';
4479 document := document
4480 || '<td class=datareadonly>'
4481 || l_opportunity_name
4482 || '</td></tr>';
4483 document := document
4484 || '<tr><td class="prompt" align="right" nowrap> ';
4485 document := document || l_attribute_tbl (
4486 9
4487 ) || '</td>';
4488 document := document
4489 || '<td class=datareadonly>'
4490 || l_expiration_date
4491 || '</td>';
4492 document := document || '<td>' || fnd_global.local_chr (
4493 38
4494 ) || 'nbsp';
4495 document := document
4496 || '</td><td>'
4497 || fnd_global.local_chr (
4498 38
4499 )
4500 || 'nbsp;</td>';
4501 document := document || '</tr> </table>';
4502 document_type := 'text/html';
4503 END IF;
4504
4505 -- Create a plain text buffer
4506
4507 IF (display_type = 'text/plain')
4508 THEN
4509 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4510 aso_debug_pub.ADD (
4511 'Display type is text/plain',
4512 1,
4513 'N'
4514 );
4515 END IF;
4516 document := document || fnd_global.local_chr (
4517 10
4518 );
4519 document := document || l_attribute_tbl (
4520 3
4521 );
4522 document := document || ': ';
4523 document := document || l_quote_name;
4524 document := document || fnd_global.local_chr (
4525 10
4526 );
4527 document := document || l_attribute_tbl (
4528 4
4529 );
4530 document := document || ': ';
4531 document := document || l_quote_number;
4532 document := document || fnd_global.local_chr (
4533 10
4534 );
4535 document := document || l_attribute_tbl (
4536 5
4537 );
4538 document := document || ': ';
4539 document := document || l_customer_name;
4540 document := document || fnd_global.local_chr (
4541 10
4542 );
4543 document := document || l_attribute_tbl (
4544 6
4545 );
4546 document := document || ': ';
4547 document := document || l_account_number;
4548 document := document || fnd_global.local_chr (
4549 10
4550 );
4551 document := document || l_attribute_tbl (
4552 7
4553 );
4554 document := document || ': ';
4555 document := document || l_contact_name;
4556 document := document || fnd_global.local_chr (
4557 10
4558 );
4559 document := document || l_attribute_tbl (
4560 8
4561 );
4562 document := document || ': ';
4563 document := document || l_opportunity_name;
4564 document := document || fnd_global.local_chr (
4565 10
4566 );
4567 document := document || l_attribute_tbl (
4568 9
4569 );
4570 document := document || ': ';
4571 document := document || l_expiration_date;
4572 document_type := 'text/plain';
4573 END IF;
4574
4575 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4576 aso_debug_pub.ADD (
4577 'End QUOTE_SUMMARY_DOC procedure ',
4578 1,
4579 'N'
4580 );
4581 END IF;
4582 EXCEPTION
4583 WHEN OTHERS
4584 THEN
4585 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4586 aso_debug_pub.ADD (
4587 'Exception in QUOTE_SUMMARY_DOC SqlCode :' || SQLERRM,
4588 1,
4589 'N'
4590 );
4591 END IF;
4592 wf_core.CONTEXT (
4593 'ASOAPPRV',
4594 'QUOTE_SUMMARY_DOC',
4595 itemtype,
4596 itemkey
4597 );
4598 RAISE;
4599 END quote_summary_doc;
4600
4601 PROCEDURE requester_comments_doc (
4602 document_id IN VARCHAR2,
4603 display_type IN VARCHAR2,
4604 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4605 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4606 ) IS
4607 itemtype VARCHAR2 (30);
4608 itemkey VARCHAR2 (30);
4609 l_approval_id NUMBER;
4610 l_requester_comments VARCHAR2 (2000);
4611 l_requester_userid NUMBER;
4612 l_attribute_tbl aso_attribute_label_tbl_type;
4613
4614 CURSOR get_requester_details (
4615 c_approval_id NUMBER
4616 ) IS
4617 SELECT requester_comments
4618 FROM aso_apr_obj_approvals
4619 WHERE object_approval_id = c_approval_id;
4620 BEGIN
4621 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4622 aso_debug_pub.ADD (
4623 'Begin REQUESTER_COMMENTS_DOC procedure ',
4624 1,
4625 'N'
4626 );
4627 END IF;
4628 itemtype := NVL (
4629 SUBSTR (
4630 document_id,
4631 1,
4632 INSTR (
4633 document_id,
4634 ':'
4635 ) - 1
4636 ),
4637 'ASOAPPRV'
4638 );
4639 itemkey := SUBSTR (
4640 document_id,
4641 INSTR (
4642 document_id,
4643 ':'
4644 ) + 1
4645 ) || 'HED';
4646 l_approval_id := wf_engine.getitemattrnumber (
4647 itemtype => itemtype,
4648 itemkey => itemkey,
4649 aname => 'APPROVALID'
4650 );
4651 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4652 aso_debug_pub.ADD (
4653 'ItemType is:' || itemtype,
4654 1,
4655 'N'
4656 );
4657 aso_debug_pub.ADD (
4658 'ItemKey is :' || itemkey,
4659 1,
4660 'N'
4661 );
4662 aso_debug_pub.ADD (
4663 'Approval ID :' || l_approval_id,
4664 1,
4665 'N'
4666 );
4667 END IF;
4668 -- get the attribute labels
4669
4670 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4671 aso_debug_pub.ADD (
4672 'Calling the get_attribute_label procedure ',
4673 1,
4674 'N'
4675 );
4676 END IF;
4677 get_attribute_label (
4678 l_approval_id,
4679 l_attribute_tbl
4680 );
4681
4682 -- Create an html text buffer
4683 IF (display_type = 'text/html')
4684 THEN
4685 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4686 aso_debug_pub.ADD (
4687 'Display type is text/html',
4688 1,
4689 'N'
4690 );
4691 END IF;
4692 OPEN get_requester_details (
4693 l_approval_id
4694 );
4695 FETCH get_requester_details INTO l_requester_comments;
4696 CLOSE get_requester_details;
4697 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4698 aso_debug_pub.ADD (
4699 'Creating a text/html document ',
4700 1,
4701 'N'
4702 );
4703 END IF;
4704 document := document
4705 || '<span class=sectionHeader1>'
4706 || l_attribute_tbl (
4707 10
4708 )
4709 || '</span>';
4710 document :=
4711 document
4712 || '<table cellspacing=1 cellpadding=1 width="75%" border=0>';
4713 document := document || '<tr>';
4714 document := document || '<td>' || l_requester_comments || '</td>';
4715 document := document || '</tr></table>';
4716 document_type := 'text/html';
4717 END IF;
4718
4719 -- Create a plain text buffer
4720
4721 IF (display_type = 'text/plain')
4722 THEN
4723 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4724 aso_debug_pub.ADD (
4725 'Display type is text/plain',
4726 1,
4727 'N'
4728 );
4729 END IF;
4730 document := document || fnd_global.local_chr (
4731 10
4732 );
4733 document := document || l_attribute_tbl (
4734 10
4735 );
4736 document := document || fnd_global.local_chr (
4737 10
4738 );
4739
4740 FOR i IN get_requester_details (
4741 l_approval_id
4742 )
4743 LOOP
4744 document := document || i.requester_comments;
4745 END LOOP;
4746
4747 document := document || fnd_global.local_chr (
4748 10
4749 );
4750 document_type := 'text/plain';
4751 END IF;
4752
4753 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4754 aso_debug_pub.ADD (
4755 'End REQUESTER_COMMENTS_DOC procedure ',
4756 1,
4757 'N'
4758 );
4759 END IF;
4760 EXCEPTION
4761 WHEN OTHERS
4762 THEN
4763 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4764 aso_debug_pub.ADD (
4765 'Exception in REQUESTER_COMMENTS_DOC SqlCode :' || SQLERRM,
4766 1,
4767 'N'
4768 );
4769 END IF;
4770 wf_core.CONTEXT (
4771 'ASOAPPRV',
4772 'REQUESTER_COMMENTS_DOC',
4773 itemtype,
4774 itemkey
4775 );
4776 RAISE;
4777 END requester_comments_doc;
4778
4779 PROCEDURE rule_details_doc (
4780 document_id IN VARCHAR2,
4781 display_type IN VARCHAR2,
4782 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
4783 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4784 ) IS
4785 itemtype VARCHAR2 (30);
4786 itemkey VARCHAR2 (30);
4787 l_approval_id NUMBER;
4788 l_rule_description VARCHAR2 (240);
4789 l_approval_level VARCHAR2 (240);
4790 l_attribute_tbl aso_attribute_label_tbl_type;
4791 l_ruletypeout VARCHAR2 (240);
4792 l_conditionidsout ame_util.idlist;
4793 l_approvaltypenameout VARCHAR2 (240);
4794 l_approvaltypedescriptionout VARCHAR2 (240);
4795
4796 CURSOR get_rule_details (
4797 c_approval_id NUMBER
4798 ) IS
4799 SELECT oam_rule_id
4800 FROM aso_apr_rules
4801 WHERE object_approval_id = c_approval_id;
4802 BEGIN
4803 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4804 aso_debug_pub.ADD (
4805 'Begin RULE_DETAILS_DOC procedure ',
4806 1,
4807 'N'
4808 );
4809 END IF;
4810 itemtype := NVL (
4811 SUBSTR (
4812 document_id,
4813 1,
4814 INSTR (
4815 document_id,
4816 ':'
4817 ) - 1
4818 ),
4819 'ASOAPPRV'
4820 );
4821 itemkey := SUBSTR (
4822 document_id,
4823 INSTR (
4824 document_id,
4825 ':'
4826 ) + 1
4827 ) || 'HED';
4828 l_approval_id := wf_engine.getitemattrnumber (
4829 itemtype => itemtype,
4830 itemkey => itemkey,
4831 aname => 'APPROVALID'
4832 );
4833 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4834 aso_debug_pub.ADD (
4835 'ItemType is:' || itemtype,
4836 1,
4837 'N'
4838 );
4839 aso_debug_pub.ADD (
4840 'ItemKey is :' || itemkey,
4841 1,
4842 'N'
4843 );
4844 aso_debug_pub.ADD (
4845 'Approval ID :' || l_approval_id,
4846 1,
4847 'N'
4848 );
4849 END IF;
4850 -- get the attribute label
4851
4852 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4853 aso_debug_pub.ADD (
4854 'Calling the get_attribute_label procedure ',
4855 1,
4856 'N'
4857 );
4858 END IF;
4859 get_attribute_label (
4860 l_approval_id,
4861 l_attribute_tbl
4862 );
4863
4864 -- Create an html text buffer
4865 IF (display_type = 'text/html')
4866 THEN
4867 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4868 aso_debug_pub.ADD (
4869 'Display type is text/html',
4870 1,
4871 'N'
4872 );
4873 END IF;
4874 document := document
4875 || '<span class=sectionHeader1>'
4876 || l_attribute_tbl (
4877 14
4878 )
4879 || '</span>';
4880 document :=
4881 document
4882 || '<table class=OraBGAccentDark cellspacing=1 cellpadding=1 width="75%" border=0>';
4883 document :=
4884 document
4885 || '<tr> <td class="tableSmallHeaderCell" align="center">'
4886 || l_attribute_tbl (
4887 15
4888 )
4889 || '</td>';
4890 document := document
4891 || '<td class="tableSmallHeaderCell" align="center">'
4892 || l_attribute_tbl (
4893 16
4894 )
4895 || '</td></tr>';
4896
4897 FOR i IN get_rule_details (
4898 l_approval_id
4899 )
4900 LOOP
4901 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4902 aso_debug_pub.ADD (
4903 'Calling AME get applicable rules procedure',
4904 1,
4905 'N'
4906 );
4907 END IF;
4908 ame_api.getruledetails1 (
4909 ruleidin => i.oam_rule_id,
4910 ruletypeout => l_ruletypeout,
4911 ruledescriptionout => l_rule_description,
4912 conditionidsout => l_conditionidsout,
4913 approvaltypenameout => l_approvaltypenameout,
4914 approvaltypedescriptionout => l_approvaltypedescriptionout,
4915 approvaldescriptionout => l_approval_level
4916 );
4917 document := document
4918 || '<tr> <td class="tableDataCell">'
4919 || l_rule_description
4920 || '</td>';
4921 document := document
4922 || '<td class="tableDataCell">'
4923 || l_approval_level
4924 || '</td></tr>';
4925 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4926 aso_debug_pub.ADD (
4927 'Rule Description is ' || l_rule_description,
4928 1,
4929 'N'
4930 );
4931 aso_debug_pub.ADD (
4932 'Approval level is ' || l_approval_level,
4933 1,
4934 'N'
4935 );
4936 END IF;
4937 END LOOP;
4938
4939 document := document || '</table>';
4940 document_type := 'text/html';
4941 END IF;
4942
4943 -- Create a plain text buffer
4944
4945 IF (display_type = 'text/plain')
4946 THEN
4947 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4948 aso_debug_pub.ADD (
4949 'Display type is text/plain',
4950 1,
4951 'N'
4952 );
4953 END IF;
4954 document := document || fnd_global.local_chr (
4955 10
4956 );
4957 document := document || l_attribute_tbl (
4958 14
4959 );
4960 document := document || fnd_global.local_chr (
4961 10
4962 );
4963
4964 FOR i IN get_rule_details (
4965 l_approval_id
4966 )
4967 LOOP
4968 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
4969 aso_debug_pub.ADD (
4970 'Calling AME get applicable rules procedure',
4971 1,
4972 'N'
4973 );
4974 END IF;
4975 ame_api.getruledetails1 (
4976 ruleidin => i.oam_rule_id,
4977 ruletypeout => l_ruletypeout,
4978 ruledescriptionout => l_rule_description,
4979 conditionidsout => l_conditionidsout,
4980 approvaltypenameout => l_approvaltypenameout,
4981 approvaltypedescriptionout => l_approvaltypedescriptionout,
4982 approvaldescriptionout => l_approval_level
4983 );
4984 document := document || l_attribute_tbl (
4985 15
4986 );
4987 document := document || ': ';
4988 document := document || l_rule_description;
4989 document := document || fnd_global.local_chr (
4990 10
4991 );
4992 document := document || l_attribute_tbl (
4993 16
4994 );
4995 document := document || ': ';
4996 document := document || l_approval_level;
4997 END LOOP;
4998
4999 document_type := 'text/plain';
5000 END IF;
5001
5002 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5003 aso_debug_pub.ADD (
5004 'End RULE_DETAILS_DOC procedure ',
5005 1,
5006 'N'
5007 );
5008 END IF;
5009 EXCEPTION
5010 WHEN OTHERS
5011 THEN
5012 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5013 aso_debug_pub.ADD (
5014 'Exception in RULE_DETAILS_DOC SqlCode :' || SQLERRM,
5015 1,
5016 'N'
5017 );
5018 END IF;
5019 wf_core.CONTEXT (
5020 'ASOAPPRV',
5021 'RULE_DETAILS_DOC',
5022 itemtype,
5023 itemkey
5024 );
5025 RAISE;
5026 END rule_details_doc;
5027
5028 PROCEDURE quote_detail_url (
5029 document_id IN VARCHAR2,
5030 display_type IN VARCHAR2,
5031 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5032 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5033 ) IS
5034 itemtype VARCHAR2 (30);
5035 itemkey VARCHAR2 (30);
5036 l_approval_id NUMBER;
5037 -- hyang performance fix bug 2860045
5038 l_quote_header_id NUMBER;
5039 l_jsp_name VARCHAR2 (2000);
5040 l_url VARCHAR2 (2000);
5041 l_attribute_tbl aso_attribute_label_tbl_type;
5042 l_quote_number NUMBER;
5043 l_party_number VARCHAR2(30);
5044 l_cust_account_id NUMBER;
5045 l_party_type VARCHAR2 (50);
5046 l_org_id NUMBER;
5047 l_notification_id NUMBER;
5048
5049 -- hyang performance fix bug 2860045
5050 CURSOR get_object_details (
5051 c_approval_id NUMBER
5052 ) IS
5053 SELECT quote_header_id, quote_number,qha.org_id
5054 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
5055 WHERE qha.quote_header_id = aoa.object_id
5056 AND aoa.object_approval_id = c_approval_id;
5057
5058 -- bug 3934660 replace qha.party_id with cust_party_id
5059
5060 CURSOR get_quote_details (
5061 c_quote_header_id NUMBER
5062 ) IS
5063 SELECT hca.cust_account_id, hp.party_type
5064 FROM aso_quote_headers_all qha,
5065 hz_parties hp,
5066 hz_cust_accounts hca
5067 WHERE qha.quote_header_id = c_quote_header_id
5068 AND nvl(qha.cust_account_id,0 ) = hca.cust_account_id (+)
5069 AND qha.cust_party_id = hp.party_id;
5070
5071 BEGIN
5072 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5073 aso_debug_pub.ADD (
5074 'Begin QUOTE_DETAIL_URL procedure ',
5075 1,
5076 'N'
5077 );
5078 END IF;
5079 itemtype := NVL (
5080 SUBSTR (
5081 document_id,
5082 1,
5083 INSTR (
5084 document_id,
5085 ':'
5086 ) - 1
5087 ),
5088 'ASOAPPRV'
5089 );
5090 itemkey := SUBSTR (
5091 document_id,
5092 INSTR (
5093 document_id,
5094 ':'
5095 ) + 1
5096 ) || 'HED';
5097 l_approval_id := wf_engine.getitemattrnumber (
5098 itemtype => itemtype,
5099 itemkey => itemkey,
5100 aname => 'APPROVALID'
5101 );
5102 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5103 aso_debug_pub.ADD (
5104 'ItemType is:' || itemtype,
5105 1,
5106 'N'
5107 );
5108 aso_debug_pub.ADD (
5109 'ItemKey is :' || itemkey,
5110 1,
5111 'N'
5112 );
5113 aso_debug_pub.ADD (
5114 'Approval ID :' || l_approval_id,
5115 1,
5116 'N'
5117 );
5118 END IF;
5119 -- get the quote header id
5120 -- hyang performance fix, added l_quote_header_id, bug 2860045
5121 OPEN get_object_details (
5122 l_approval_id
5123 );
5124 FETCH get_object_details INTO l_quote_header_id, l_quote_number,l_org_id;
5125 CLOSE get_object_details;
5126 OPEN get_quote_details (
5127 l_quote_header_id
5128 );
5129 FETCH get_quote_details INTO l_cust_account_id,
5130 l_party_type;
5131 CLOSE get_quote_details;
5132
5133 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5134 aso_debug_pub.ADD (
5135 'Quote header ID is ' || l_quote_header_id,
5136 1,
5137 'N'
5138 );
5139 END IF;
5140 -- get the server address
5141 l_url := fnd_web_config.jsp_agent (
5142 );
5143 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5144 aso_debug_pub.ADD (
5145 'URL ID is ' || l_url,
5146 1,
5147 'N'
5148 );
5149 END IF;
5150 -- get the jsp name
5151
5152 l_jsp_name := 'qotSZzpAppsLink.jsp?';
5153 l_jsp_name := l_jsp_name || 'qotFrmMainFile=qotSZzdContainer.jsp';
5154 l_jsp_name := l_jsp_name
5155 || fnd_global.local_chr (
5156 38
5157 )
5158 || 'qotFrmDspFile=qotSCocOverview.jsp';
5159 l_jsp_name := l_jsp_name
5160 || fnd_global.local_chr (
5161 38
5162 )
5163 || 'qotFrmRefFile=qotSCocOverview.jsp';
5164 l_jsp_name := l_jsp_name
5165 || fnd_global.local_chr (
5166 38
5167 )
5168 || 'qotDetCode=QUOTE';
5169 l_jsp_name := l_jsp_name
5170 || fnd_global.local_chr (
5171 38
5172 )
5173 || 'qotPtyType='
5174 || l_party_type;
5175 l_jsp_name := l_jsp_name
5176 || fnd_global.local_chr (
5177 38
5178 )
5179 || 'qotHdrId='
5180 || l_quote_header_id;
5181 l_jsp_name := l_jsp_name
5182 || fnd_global.local_chr (
5183 38
5184 )
5185 || 'qotHdrAcctId='
5186 || l_cust_account_id;
5187 l_jsp_name := l_jsp_name
5188 || fnd_global.local_chr (
5189 38
5190 )
5191 || 'qotHdrNbr='
5192 || l_quote_number;
5193 l_jsp_name := l_jsp_name
5194 || fnd_global.local_chr (
5195 38
5196 )
5197 || 'qotReqSetCookie=Y';
5198 l_jsp_name := l_jsp_name
5199 || fnd_global.local_chr (
5200 38
5201 )
5202 || 'qotFromApvlLink=Y';
5203
5204 -- bug 3178070
5205 l_jsp_name := l_jsp_name
5206 || fnd_global.local_chr (
5207 38
5208 )
5209 || 'qotApvOrgId='
5210 || l_org_id;
5211
5212 l_jsp_name := l_jsp_name
5213 || fnd_global.local_chr (
5214 38
5215 )
5216 || 'qotApvNotifId=&#NID';
5217
5218
5219
5220
5221 -- get the attribute label
5222 get_attribute_label (
5223 l_approval_id,
5224 l_attribute_tbl
5225 );
5226
5227 -- Create an html text buffer
5228 IF (display_type = 'text/html')
5229 THEN
5230 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5231 aso_debug_pub.ADD (
5232 'Display type is text/html',
5233 1,
5234 'N'
5235 );
5236 END IF;
5237 document := '<a href = "'
5238 || l_url
5239 || l_jsp_name
5240 || '">'
5241 || l_attribute_tbl (
5242 1
5243 )
5244 || '</a>';
5245 document_type := 'text/html';
5246 END IF;
5247
5248 -- Create a plain text buffer
5249
5250 IF (display_type = 'text/plain')
5251 THEN
5252 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5253 aso_debug_pub.ADD (
5254 'Display type is text/plain',
5255 1,
5256 'N'
5257 );
5258 END IF;
5259 NULL;
5260 document_type := 'text/plain';
5261 END IF;
5262
5263 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5264 aso_debug_pub.ADD (
5265 'End QUOTE_DETAIL_URL procedure ',
5266 1,
5267 'N'
5268 );
5269 END IF;
5270 EXCEPTION
5271 WHEN OTHERS
5272 THEN
5273 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5274 aso_debug_pub.ADD (
5275 'Exception in QUOTE_DETAIL_URL SqlCode :' || SQLERRM,
5276 1,
5277 'N'
5278 );
5279 END IF;
5280 wf_core.CONTEXT (
5281 'ASOAPPRV',
5282 'QUOTE_DETAIL_URL',
5283 itemtype,
5284 itemkey
5285 );
5286 RAISE;
5287 END quote_detail_url;
5288
5289 PROCEDURE get_attribute_label (
5290 p_approval_id IN NUMBER,
5291 p_attribute_tbl OUT NOCOPY /* file.sql.39 change */ aso_attribute_label_tbl_type
5292 ) IS
5293 l_application_id NUMBER;
5294
5295 CURSOR get_application_id (
5296 c_approval_id NUMBER
5297 ) IS
5298 SELECT application_id
5299 FROM aso_apr_obj_approvals
5300 WHERE object_approval_id = c_approval_id;
5301
5302 -- hyang, bug 2860045, performance fix.
5303 CURSOR get_label (
5304 c_application_id NUMBER
5305 ) IS
5306 SELECT attribute_label_long
5307 FROM ak_region_items ara, ak_attributes_tl aat
5308 WHERE region_code = 'ASO_APR_NOTIFICATION'
5309 and region_application_id = c_application_id
5310 AND AAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID
5311 AND AAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE
5312 AND AAT.LANGUAGE = USERENV('LANG')
5313 ORDER by display_sequence;
5314 BEGIN
5315 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5316 aso_debug_pub.ADD (
5317 'begin GET_ATTRIBUTE_LABEL procedure ',
5318 1,
5319 'N'
5320 );
5321 END IF;
5322 -- get the application id
5323 OPEN get_application_id (
5324 p_approval_id
5325 );
5326 FETCH get_application_id INTO l_application_id;
5327 CLOSE get_application_id;
5328 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5329 aso_debug_pub.ADD (
5330 'Application ID is ' || l_application_id,
5331 1,
5332 'N'
5333 );
5334 END IF;
5335 p_attribute_tbl := aso_attribute_label_tbl_type (
5336 );
5337
5338 -- fetch the labels and populate the PL/SQL table
5339
5340 FOR i IN get_label (
5341 697
5342 )
5343 LOOP
5344 p_attribute_tbl.EXTEND;
5345 p_attribute_tbl (
5346 p_attribute_tbl.COUNT
5347 ) := i.attribute_label_long;
5348 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5349 aso_debug_pub.ADD (
5350 'Label is ' || p_attribute_tbl (
5351 p_attribute_tbl.COUNT
5352 ),
5353 1,
5354 'N'
5355 );
5356 END IF;
5357 END LOOP;
5358
5359 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5360 aso_debug_pub.ADD (
5361 'Count of labels is ' || p_attribute_tbl.COUNT,
5362 1,
5363 'N'
5364 );
5365 aso_debug_pub.ADD (
5366 'End GET_ATTRIBUTE_LABEL procedure ',
5367 1,
5368 'N'
5369 );
5370 END IF;
5371 EXCEPTION
5372 WHEN OTHERS
5373 THEN
5374 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5375 aso_debug_pub.ADD (
5376 'Exception in GET_ATTRIBUTE_LABEL SqlCode :' || SQLERRM,
5377 1,
5378 'N'
5379 );
5380 END IF;
5381 wf_core.CONTEXT (
5382 'ASOAPPRV',
5383 'GET_ATTRIBUTE_LABEL',
5384 SQLERRM
5385 );
5386 RAISE;
5387 END get_attribute_label;
5388
5389
5390 FUNCTION GetRunFuncURL
5391 ( p_function_name IN VARCHAR2
5392 , p_resp_appl_id IN NUMBER DEFAULT NULL
5393 , p_resp_id IN NUMBER DEFAULT NULL
5394 , p_security_group_id IN NUMBER DEFAULT NULL
5395 , p_parameters IN VARCHAR2 DEFAULT NULL
5396 ) RETURN VARCHAR2
5397 IS
5398
5399 l_function_id NUMBER ;
5400 l_resp_appl_id NUMBER := p_resp_appl_id;
5401 l_resp_id NUMBER := p_resp_id ;
5402 l_security_group_id NUMBER := p_security_group_id;
5403
5404 BEGIN
5405
5406 l_function_id := fnd_function.get_function_id(p_function_name) ;
5407
5408
5409 IF p_resp_appl_id IS NULL THEN
5410 l_resp_appl_id := -1 ;
5411 END IF ;
5412
5413
5414 IF p_resp_id IS NULL THEN
5415 l_resp_id := -1 ;
5416 END IF ;
5417
5418 IF p_security_group_id IS NULL THEN
5419 l_security_group_id := -1 ;
5420 END IF ;
5421
5422 -- Call Fnd API
5423 RETURN fnd_run_function.get_run_function_url
5424 ( p_function_id => l_function_id
5425 , p_resp_appl_id => l_resp_appl_id
5426 , p_resp_id => l_resp_id
5427 , p_security_group_id => l_security_group_id
5428 , p_parameters => p_parameters ) ;
5429
5430
5431 END GetRunFuncURL ;
5432
5433
5434 PROCEDURE set_attributes (
5435 itemtype IN VARCHAR2,
5436 itemkey IN VARCHAR2,
5437 actid IN NUMBER,
5438 funcmode IN VARCHAR2,
5439 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5440 ) IS
5441
5442 l_approval_object VARCHAR2 (4000);
5443 l_approval_id NUMBER;
5444 l_org_id NUMBER;
5445 l_ampsign VARCHAR2(1) := fnd_global.local_chr(38);
5446 l_url VARCHAR2(32000);
5447
5448 -- bug 7657061
5449 l_resp_appl_id NUMBER := 880;
5450 l_resp_id NUMBER;
5451
5452 CURSOR OBJECT (
5453 c_approval_id NUMBER
5454 ) IS
5455 SELECT quote_name, quote_number,org_id,quote_header_id
5456 FROM aso_quote_headers_all qha, aso_apr_obj_approvals aoa
5457 WHERE qha.quote_header_id = aoa.object_id
5458 AND aoa.object_approval_id = c_approval_id;
5459
5460 BEGIN
5461 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5462 aso_debug_pub.ADD (
5463 'Begin set attribute Process',
5464 1,
5465 'N'
5466 );
5467 END IF;
5468
5469 IF funcmode = 'RUN'
5470 THEN
5471 l_approval_id := wf_engine.getitemattrnumber (
5472 itemtype,
5473 itemkey,
5474 'APPROVALID'
5475 );
5476
5477 FOR i IN OBJECT (
5478 l_approval_id
5479 )
5480 LOOP
5481 l_approval_object := i.quote_name;
5482 l_org_id := i.org_id;
5483
5484 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5485 aso_debug_pub.ADD (
5486 'Quote name :' || l_approval_object,
5487 1,
5488 'N'
5489 );
5490 END IF;
5491 wf_engine.setitemattrtext (
5492 itemtype => itemtype,
5493 itemkey => itemkey,
5494 aname => 'APPROVAL_OBJECT',
5495 avalue => l_approval_object
5496 );
5497
5498 wf_engine.setitemattrnumber (
5499 itemtype => itemtype,
5500 itemkey => itemkey,
5501 aname => 'ORGID',
5502 avalue => l_org_id
5503 );
5504
5505 wf_engine.setitemattrnumber (
5506 itemtype => itemtype,
5507 itemkey => itemkey,
5508 aname => 'QTEHDRID',
5509 avalue => i.quote_header_id
5510 );
5511
5512 wf_engine.setitemattrnumber (
5513 itemtype => itemtype,
5514 itemkey => itemkey,
5515 aname => 'QTENUMBER',
5516 avalue => i.quote_number
5517 );
5518
5519
5520 select nvl(fnd_profile.value('ASO_QUOTE_APPROVER_RESP'),-1) into l_resp_id from dual; -- bug 7657061
5521
5522 -- bug 5350149
5523 l_url := aso_apr_wf_pvt.GetRunFuncURL(
5524 p_function_name => 'QOT_OAUI_QUOTE_DETAILS',
5525 p_resp_appl_id => l_resp_appl_id, -- bug 7657061
5526 p_resp_id => l_resp_id, -- bug 7657061
5527 p_parameters => l_ampsign||l_ampsign||'QotIntgEvtSrc=ApvlNotif'||l_ampsign||'QotIntgEvt=Event.QuoteDet'||l_ampsign||'QotIntgEvtVal='
5528 ||i.quote_header_id||l_ampsign||'QotIntgEvtVal1='||i.quote_number||l_ampsign||'addBreadCrumb=Y') ;
5529
5530
5531 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5532 aso_debug_pub.ADD ('RESP_APPL_ID: ' || l_resp_appl_id,1,'N'); -- bug 7657061
5533 aso_debug_pub.ADD ('RESP_ID: ' || l_resp_id,1,'N'); -- bug 7657061
5534 aso_debug_pub.ADD ('l_url: '||substr(l_url,1,240),1,'N');
5535 END IF;
5536
5537
5538 wf_engine.setitemattrtext (
5539 itemtype => itemtype,
5540 itemkey => itemkey,
5541 aname => 'OAQTEDETAILLNK',
5542 avalue => l_url
5543 );
5544
5545 END LOOP;
5546 resultout := 'COMPLETE';
5547 END IF;
5548 EXCEPTION
5549 WHEN OTHERS
5550 THEN
5551 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5552 aso_debug_pub.ADD (
5553 'Exception in Set Attributes SqlCode :' || SQLERRM,
5554 1,
5555 'N'
5556 );
5557 END IF;
5558 wf_core.CONTEXT (
5559 'ASOAPPRV',
5560 'set_attributes',
5561 itemtype,
5562 itemkey
5563 );
5564 RAISE;
5565
5566 END;
5567
5568 PROCEDURE update_approval_status (
5569 p_update_header_or_detail_flag IN VARCHAR2,
5570 p_object_approval_id IN NUMBER,
5571 p_approval_det_id IN NUMBER,
5572 p_status IN VARCHAR2,
5573 note IN VARCHAR2
5574 ) is
5575 begin
5576 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5577 aso_debug_pub.ADD (
5578 'Start update_approval_status procedure ',
5579 1,
5580 'N'
5581 );
5582
5583 aso_debug_pub.ADD (
5584 'Flag is :' || p_update_header_or_detail_flag,
5585 1,
5586 'N'
5587 );
5588 aso_debug_pub.ADD (
5589 'Object Approval ID is :' || p_object_approval_id,
5590 1,
5591 'N'
5592 );
5593 aso_debug_pub.ADD (
5594 'Approval Detail ID is :' || p_approval_det_id,
5595 1,
5596 'N'
5597 );
5598 aso_debug_pub.ADD (
5599 'Status is :' || p_status,
5600 1,
5601 'N'
5602 );
5603
5604 END IF;
5605
5606 IF p_update_header_or_detail_flag = 'HEADER' THEN
5607 IF (p_status = 'PEND')
5608 THEN
5609 UPDATE aso_apr_obj_approvals
5610 SET approval_status = p_status,
5611 last_update_date = SYSDATE,
5612 last_updated_by = g_user_id,
5613 last_update_login = g_user_id
5614 WHERE object_approval_id = p_object_approval_id;
5615 ELSE
5616 UPDATE aso_apr_obj_approvals
5617 SET approval_status = p_status,
5618 last_update_date = SYSDATE,
5619 end_date = SYSDATE,
5620 last_updated_by = g_user_id,
5621 last_update_login = g_user_id
5622 WHERE object_approval_id = p_object_approval_id;
5623 END IF;
5624
5625 END IF;
5626
5627 IF p_update_header_or_detail_flag = 'DETAIL' THEN
5628 UPDATE aso_apr_approval_details
5629 SET approver_status = p_status,
5630 date_received = SYSDATE,
5631 last_update_date = SYSDATE,
5632 approver_comments = note,
5633 last_updated_by = fnd_global.user_id,
5634 last_update_login = fnd_global.user_id
5635 WHERE approval_det_id = p_approval_det_id;
5636 END IF;
5637
5638 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
5639 aso_debug_pub.ADD (
5640 'End update_approval_status procedure ',
5641 1,
5642 'N'
5643 );
5644 END IF;
5645
5646 end;
5647
5648
5649 END aso_apr_wf_pvt;