[Home] [Help]
PACKAGE BODY: APPS.OKE_K_APPROVAL_WF
Source
1 PACKAGE BODY OKE_K_APPROVAL_WF AS
2 /* $Header: OKEWKAPB.pls 120.1.12000000.3 2007/03/17 11:57:27 nnadahal ship $ */
3
4 --
5 -- Global Variables
6 --
7 G_K_Header_ID NUMBER;
8 G_K_Number VARCHAR2(240);
9 G_K_Type_Code VARCHAR2(30);
10 G_K_Type VARCHAR2(80);
11 G_Type_Class VARCHAR2(30);
12 G_Intent VARCHAR2(30);
13 G_Requestor VARCHAR2(80);
14 G_Aprv_Path NUMBER;
15 G_Aprv_Seq NUMBER;
16
17 --
18 -- Private Functions and Procedures
19 --
20
21 --
22 -- Load_Globals loads commonly used WF item attributes into PL/SQL globals
23 --
24 PROCEDURE Load_Globals
25 ( ItemType IN VARCHAR2
26 , ItemKey IN VARCHAR2
27 ) IS
28
29 BEGIN
30
31 G_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
32 G_K_Number := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_NUMBER');
33 G_K_Type_Code := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_TYPE_CODE');
34 G_K_Type := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_TYPE');
35 G_Type_Class := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_TYPE_CLASS');
36 G_Intent := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'INTENT');
37 G_Requestor := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'REQUESTOR');
38 G_Aprv_Path := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVAL_PATH_ID');
39 G_Aprv_Seq := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVAL_SEQUENCE');
40
41 END Load_Globals;
42
43
44 --
45 -- KRole_To_WFRole maps a contract role into WF role based on contract security
46 -- assignments
47 --
48 FUNCTION KRole_To_WFRole ( K_Role_ID NUMBER )
49 RETURN VARCHAR2 IS
50
51 --CURSOR c IS
52 -- SELECT R.Name
53 -- FROM oke_k_all_access_basic_v A , wf_roles R
54 -- WHERE A.k_header_id = G_K_Header_ID
55 -- AND A.role_id = K_Role_ID
56 -- AND SYSDATE BETWEEN A.Start_date_Active AND nvl(A.End_Date_Active , SYSDATE + 1 )
57 -- AND R.orig_system_id = A.person_id
58 -- AND R.orig_system = 'PER'
59 -- ORDER BY decode( A.assignment_level , 'SITE' , 0 , 'OKE_PROGRAMS' , 1 , 2 ) desc;
60
61 UserName VARCHAR2(80);
62
63 BEGIN
64
65 -- OPEN c;
66 -- FETCH c INTO UserName;
67 -- CLOSE c;
68 UserName := OKE_UTILS.Retrieve_WF_Role_Name(G_K_Header_ID,K_Role_ID);
69
70 RETURN ( UserName );
71
72 END KRole_To_WFRole;
73
74
75 --
76 -- K_Role_Name returns the name of the contract role
77 --
78 FUNCTION K_Role_Name ( K_Role_ID NUMBER )
79 RETURN VARCHAR2 IS
80
81 CURSOR c IS
82 SELECT name
83 FROM oke_k_roles_v
84 WHERE role_id = K_Role_ID;
85
86 RoleName VARCHAR2(80);
87
88 BEGIN
89
90 OPEN c;
91 FETCH c INTO RoleName;
92 CLOSE c;
93
94 RETURN ( RoleName );
95
96 END K_Role_Name;
97
98
99 --
100 -- Add_To_History adds an entry into the approval history
101 --
102 PROCEDURE Add_To_History
103 ( PerformerName IN VARCHAR2
104 , ActionCode IN VARCHAR2
105 , ActionDate IN DATE
106 , ApprovalPathID IN NUMBER
107 , ApprovalSeq IN NUMBER
108 , ApproverRoleID IN NUMBER
109 , NoteText IN VARCHAR2
110 ) IS
111
112 CURSOR c IS
113 SELECT nvl(max(action_sequence) , 0) + 1
114 FROM oke_approval_history
115 WHERE k_header_id = G_K_Header_ID
116 AND chg_request_id is null;
117 NextSeq NUMBER;
118
119 BEGIN
120
121 OPEN c;
122 FETCH c INTO NextSeq;
123 CLOSE c;
124
125 INSERT INTO oke_approval_history
126 ( k_header_id
127 , action_sequence
128 , creation_date
129 , created_by
130 , last_update_date
131 , last_updated_by
132 , last_update_login
133 , action_code
134 , action_date
135 , performer
136 , approver_role_id
137 , approval_path_id
138 , approval_sequence
139 , note )
140 VALUES
141 ( G_K_Header_ID
142 , NextSeq
143 , sysdate
144 , FND_GLOBAL.user_id
145 , sysdate
146 , FND_GLOBAL.user_id
147 , -1
148 , ActionCode
149 , ActionDate
150 , PerformerName
151 , ApproverRoleID
152 , ApprovalPathID
153 , ApprovalSeq
154 , NoteText
155 );
156
157 END Add_To_History;
158
159 FUNCTION UrlEncode( URL VARCHAR2 ) RETURN VARCHAR2 IS
160 BEGIN
161 RETURN Replace(WF_Mail.UrlEncode(url),'','%5C'); --workarround for backslash because of a bug in wf_mail.urlencode
162 END UrlEncode;
163
164 --
165 -- Public Functions and Procedures
166 --
167
168 --
169 -- Name : Initialize
170 -- Pre-reqs : Must be called from WF activity
171 -- Function : This procedure initializes the remaining of the item
172 -- attributes not set during launch
173 --
174 -- Parameters :
175 -- IN : ItemType
176 -- ItemKey
177 -- ActID
178 -- FuncMode
179 -- OUT : ResultOut ( None )
180 --
181 -- Returns : None
182 --
183 PROCEDURE Initialize
184 ( ItemType IN VARCHAR2
185 , ItemKey IN VARCHAR2
186 , ActID IN NUMBER
187 , FuncMode IN VARCHAR2
188 , ResultOut OUT NOCOPY VARCHAR2
189 ) IS
190
191 CURSOR k ( C_K_Header_ID NUMBER ) IS
192 SELECT k.k_number_disp
193 , k.k_type_code
194 , kt.k_type_name
195 , kt.type_class_code
196 , k.buy_or_sell
197 , k.currency_code
198 , k.k_value
199 , k.short_description
200 , k.description
201 , k.authoring_org_id
202 , k.status_code
203 , ks.ste_code
204 FROM oke_k_headers_v k
205 , oke_k_types_vl kt
206 , okc_statuses_b ks
207 WHERE k.k_header_id = C_K_Header_ID
208 AND kt.k_type_code = k.k_type_code
209 AND ks.code = k.status_code;
210 krec k%rowtype;
211
212 tx_k_number VARCHAR2(400);
213 c_txn_vlu VARCHAR2(400);
214 c_new_vlu VARCHAR2(400);
215 n_pos NUMBER;
216
217 P_FLOWDOWN_LINK VARCHAR2(4000);
218
219 BEGIN
220
221 IF ( FuncMode = 'RUN' ) THEN
222
223 G_K_Header_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
224
225 OPEN k ( G_K_Header_ID );
226 FETCH k INTO krec;
227 CLOSE k;
228
229 SELECT owner_role
230 INTO G_Requestor
231 FROM wf_items
232 WHERE item_type = ItemType
233 AND item_key = ItemKey;
234
235 WF_ENGINE.SetItemAttrText( itemtype => ItemType
236 , itemkey => ItemKey
237 , aname => 'K_NUMBER'
238 , avalue => krec.k_number_disp );
239
240 WF_ENGINE.SetItemAttrText( itemtype => ItemType
241 , itemkey => ItemKey
242 , aname => 'K_TYPE_CODE'
243 , avalue => krec.k_type_code );
244
245 WF_ENGINE.SetItemAttrText( itemtype => ItemType
246 , itemkey => ItemKey
247 , aname => 'K_TYPE'
248 , avalue => krec.k_type_name );
249
250 WF_ENGINE.SetItemAttrText( itemtype => ItemType
251 , itemkey => ItemKey
252 , aname => 'K_TYPE_CLASS'
253 , avalue => krec.type_class_code );
254
255 WF_ENGINE.SetItemAttrText( itemtype => ItemType
256 , itemkey => ItemKey
257 , aname => 'INTENT'
258 , avalue => krec.buy_or_sell );
259
260 WF_ENGINE.SetItemAttrText( itemtype => ItemType
261 , itemkey => ItemKey
262 , aname => 'CURRENCY_CODE'
263 , avalue => krec.currency_code );
264
265 WF_ENGINE.SetItemAttrText( itemtype => ItemType
266 , itemkey => ItemKey
267 , aname => 'CONTRACT_VALUE'
268 , avalue =>
269 to_char( krec.k_value
270 , FND_CURRENCY.get_format_mask( krec.currency_code , 38 ) ) );
271
272 WF_ENGINE.SetItemAttrText( itemtype => ItemType
273 , itemkey => ItemKey
274 , aname => 'SHORT_DESCRIPTION'
275 , avalue => krec.short_description );
276
277 WF_ENGINE.SetItemAttrText( itemtype => ItemType
278 , itemkey => ItemKey
279 , aname => 'DESCRIPTION'
280 , avalue => krec.description );
281
282 WF_ENGINE.SetItemAttrText( itemtype => ItemType
283 , itemkey => ItemKey
284 , aname => 'STS_CODE'
285 , avalue => krec.status_code );
286
287 WF_ENGINE.SetItemAttrText( itemtype => ItemType
288 , itemkey => ItemKey
289 , aname => 'STE_CODE'
290 , avalue => krec.ste_code );
291
292 WF_ENGINE.SetItemAttrText( itemtype => ItemType
293 , itemkey => ItemKey
294 , aname => 'REQUESTOR'
295 , avalue => FND_GLOBAL.User_Name );
296
297 WF_ENGINE.SetItemAttrNumber( itemtype => ItemType
298 , itemkey => ItemKey
299 , aname => 'APPROVAL_SEQUENCE'
300 , avalue => 0 );
301
302 WF_ENGINE.SetItemAttrNumber( itemtype => ItemType
303 , itemkey => ItemKey
304 , aname => 'ORG_ID'
305 , avalue => krec.authoring_org_id );
306
307 WF_ENGINE.SetItemAttrText( itemtype => ItemType
308 , itemkey => ItemKey
309 , aname => 'K_NUMBER_LINK'
310 , avalue => 'PLSQL:OKE_K_APPROVAL_WF2.CONTRACT_NUMBER_LINK/'
311 || ItemType || ':' || ItemKey );
312
313 WF_ENGINE.SetItemAttrText( itemtype => ItemType
314 , itemkey => ItemKey
315 , aname => 'APPROVAL_HISTORY'
316 , avalue => 'PLSQL:OKE_K_APPROVAL_WF2.SHOW_APPROVAL_HISTORY/'
317 || ItemType || ':' || ItemKey );
318
319 P_FLOWDOWN_LINK := 'OA.jsp?akRegionCode=KHEADERPAGE'
320 ||'&akRegionApplicationId=777'
321 ||'&OAFunc=OKEFLDVH'
322 ||'&p_ba=APPROVAL'
323 ||'&p_k_header_id='
324 ||TO_CHAR(G_K_Header_ID)
325 ||'&p_k_line_id='
326 ||'&p_project_id='
327 ||'&p_task_id='
328 ||'&p_k_number='
329 ||UrlEncode(krec.k_number_disp)
330 ||'&addBreadCrumb=Y';
331
332
333
334 WF_ENGINE.SetItemAttrText( itemtype => ItemType
335 , itemkey => ItemKey
336 , aname => 'FLOWDOWN_LINK'
337 , avalue => P_FLOWDOWN_LINK );
338
339 ResultOut := 'COMPLETE:';
340 RETURN;
341
342 END IF;
343
344 IF ( FuncMode = 'CANCEL' ) THEN
345
346 ResultOut := '';
347 RETURN;
348
349 END IF;
350
351 IF ( FuncMode = 'TIMEOUT' ) THEN
352
353 ResultOut := '';
354 RETURN;
355
356 END IF;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 ResultOut := 'ERROR';
361 WF_ENGINE.SetItemAttrText
362 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
363 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
364 , 'INITIALIZE'
365 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
366 RAISE;
367
368 END Initialize;
369
370
371 --
372 -- Name : Is_BOA_Approved
373 -- Pre-reqs : Must be called from WF activity
374 -- Function : This procedure determines if the master agreement
375 -- has been approved or not.
376 --
377 -- Parameters :
378 -- IN : ItemType
379 -- ItemKey
380 -- ActID
381 -- FuncMode
382 -- OUT : ResultOut ( WFSTD_YES_NO )
383 --
384 -- Returns : None
385 --
386 PROCEDURE Is_BOA_Approved
387 ( ItemType IN VARCHAR2
388 , ItemKey IN VARCHAR2
389 , ActID IN NUMBER
390 , FuncMode IN VARCHAR2
391 , ResultOut OUT NOCOPY VARCHAR2
392 ) IS
393
394 CURSOR k ( C_K_Header_ID NUMBER ) IS
395 SELECT ks.ste_code
396 FROM okc_k_headers_b boa
397 , oke_k_headers k
398 , okc_statuses_b ks
399 WHERE k.k_header_id = C_K_Header_ID
400 AND boa.id = k.boa_id
401 AND ks.code = boa.sts_code;
402 krec k%rowtype;
403
404 BEGIN
405
406 IF ( FuncMode = 'RUN' ) THEN
407
408 Load_Globals( ItemType , ItemKey );
409
410 OPEN k ( G_K_Header_ID );
411 FETCH k INTO krec;
412 CLOSE k;
413
414 IF ( krec.ste_code IN ( 'SIGNED' , 'ACTIVE' ) ) THEN
415 ResultOut := 'COMPLETE:Y';
416 ELSE
417 ResultOut := 'COMPLETE:N';
418 FND_MESSAGE.Set_Name('OKE' , 'OKE_APRV_ABT_BOA_NOT_APPROVED');
419 WF_ENGINE.SetItemAttrText
420 ( ItemType => ItemType
421 , ItemKey => ItemKey
422 , AName => 'MESSAGE1'
423 , AValue => FND_MESSAGE.Get );
424 END IF;
425 RETURN;
426
427 END IF;
428
429 IF ( FuncMode = 'CANCEL' ) THEN
430
431 ResultOut := WF_ENGINE.ENG_NULL;
432 RETURN;
433
434 END IF;
435
436 IF ( FuncMode = 'TIMEOUT' ) THEN
437
438 ResultOut := WF_ENGINE.ENG_NULL;
439 RETURN;
440
441 END IF;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 ResultOut := 'ERROR:';
446 WF_ENGINE.SetItemAttrText
447 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
448 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
449 , 'IS_BOA_APPROVED'
450 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
451 RAISE;
452
453 END Is_BOA_Approved;
454
455
456 --
457 -- Name : Is_Doc_Approved
458 -- Pre-reqs : Must be called from WF activity
459 -- Function : This procedure determines if the contract document
460 -- has already been approved or not.
461 --
462 -- Parameters :
463 -- IN : ItemType
464 -- ItemKey
465 -- ActID
466 -- FuncMode
467 -- OUT : ResultOut ( WFSTD_YES_NO )
468 --
469 -- Returns : None
470 --
471 PROCEDURE Is_Doc_Approved
472 ( ItemType IN VARCHAR2
473 , ItemKey IN VARCHAR2
474 , ActID IN NUMBER
475 , FuncMode IN VARCHAR2
476 , ResultOut OUT NOCOPY VARCHAR2
477 ) IS
478
479 ste_code VARCHAR2(30);
480
481 BEGIN
482
483 IF ( FuncMode = 'RUN' ) THEN
484
485 ste_code := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'STE_CODE');
486
487 IF ( ste_code IN ( 'ENTERED' , 'CANCELED' ) ) THEN
488 ResultOut := 'COMPLETE:N';
489 ELSE
490 ResultOut := 'COMPLETE:Y';
491 FND_MESSAGE.Set_Name('OKE' , 'OKE_APRV_ABT_APPROVED');
492 WF_ENGINE.SetItemAttrText
493 ( ItemType => ItemType
494 , ItemKey => ItemKey
495 , AName => 'MESSAGE1'
496 , AValue => FND_MESSAGE.Get );
497 END IF;
498 RETURN;
499
500 END IF;
501
502 IF ( FuncMode = 'CANCEL' ) THEN
503
504 ResultOut := WF_ENGINE.ENG_NULL;
505 RETURN;
506
507 END IF;
508
509 IF ( FuncMode = 'TIMEOUT' ) THEN
510
511 ResultOut := WF_ENGINE.ENG_NULL;
512 RETURN;
513
514 END IF;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 ResultOut := 'ERROR:';
519 WF_ENGINE.SetItemAttrText
520 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
521 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
522 , 'IS_DOC_APPROVED'
523 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
524 RAISE;
525
526 END Is_Doc_Approved;
527
528
529 --
530 -- Name : Is_Doc_Delv_Order
531 -- Pre-reqs : Must be called from WF activity
532 -- Function : This procedure determines if the contract document
533 -- is a delivery order.
534 --
535 -- Parameters :
536 -- IN : ItemType
537 -- ItemKey
538 -- ActID
539 -- FuncMode
540 -- OUT : ResultOut ( WFSTD_YES_NO )
541 --
542 -- Returns : None
543 --
544 PROCEDURE Is_Doc_Delv_Order
545 ( ItemType IN VARCHAR2
546 , ItemKey IN VARCHAR2
547 , ActID IN NUMBER
548 , FuncMode IN VARCHAR2
549 , ResultOut OUT NOCOPY VARCHAR2
550 ) IS
551
552 ste_code VARCHAR2(30);
553
554 BEGIN
555
556 IF ( FuncMode = 'RUN' ) THEN
557
558 IF ( WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'K_TYPE_CLASS') = 'DO' ) THEN
559 ResultOut := 'COMPLETE:Y';
560 ELSE
561 ResultOut := 'COMPLETE:N';
562 END IF;
563 RETURN;
564
565 END IF;
566
567 IF ( FuncMode = 'CANCEL' ) THEN
568
569 ResultOut := WF_ENGINE.ENG_NULL;
570 RETURN;
571
572 END IF;
573
574 IF ( FuncMode = 'TIMEOUT' ) THEN
575
576 ResultOut := WF_ENGINE.ENG_NULL;
577 RETURN;
578
579 END IF;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 ResultOut := 'ERROR:';
584 WF_ENGINE.SetItemAttrText
585 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
586 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
587 , 'IS_DOC_DELV_ORDER'
588 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
589 RAISE;
590
591 END Is_Doc_Delv_Order;
592
593
594 --
595 -- Name : Is_Doc_Inactive
596 -- Pre-reqs : Must be called from WF activity
597 -- Function : This procedure determines if the contract document
598 -- is currently inactive (Canceled, Expired, Terminated)
599 --
600 -- Parameters :
601 -- IN : ItemType
602 -- ItemKey
603 -- ActID
604 -- FuncMode
605 -- OUT : ResultOut ( WFSTD_YES_NO )
606 --
607 -- Returns : None
608 --
609 PROCEDURE Is_Doc_Inactive
610 ( ItemType IN VARCHAR2
611 , ItemKey IN VARCHAR2
612 , ActID IN NUMBER
613 , FuncMode IN VARCHAR2
614 , ResultOut OUT NOCOPY VARCHAR2
615 ) IS
616
617 ste_code VARCHAR2(30);
618
619 BEGIN
620
621 IF ( FuncMode = 'RUN' ) THEN
622
623 ste_code := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'STE_CODE');
624
625 IF ( ste_code in ( 'CANCELED' , 'TERMINATED' , 'EXPIRED' ) ) THEN
626 ResultOut := 'COMPLETE:Y';
627 FND_MESSAGE.Set_Name('OKE' , 'OKE_APRV_ABT_INACTIVE');
628 WF_ENGINE.SetItemAttrText
629 ( ItemType => ItemType
630 , ItemKey => ItemKey
631 , AName => 'MESSAGE1'
632 , AValue => FND_MESSAGE.Get );
633 ELSE
634 ResultOut := 'COMPLETE:N';
635 END IF;
636 RETURN;
637
638 END IF;
639
640 IF ( FuncMode = 'CANCEL' ) THEN
641
642 ResultOut := WF_ENGINE.ENG_NULL;
643 RETURN;
644
645 END IF;
646
647 IF ( FuncMode = 'TIMEOUT' ) THEN
648
649 ResultOut := WF_ENGINE.ENG_NULL;
650 RETURN;
651
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 ResultOut := 'ERROR:';
657 WF_ENGINE.SetItemAttrText
658 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
659 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
660 , 'IS_DOC_INACTIVE'
661 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
662 RAISE;
663
664 END Is_Doc_Inactive;
665
666
667 --
668 -- Name : Is_Final_Approver
669 -- Pre-reqs : Must be called from WF activity
670 -- Function : This procedure determines if the last approver
671 -- is the final approver based to the approval
672 -- hierarchy.
673 --
674 -- Parameters :
675 -- IN : ItemType
676 -- ItemKey
677 -- ActID
678 -- FuncMode
679 -- OUT : ResultOut ( WFSTD_YES_NO )
680 --
681 -- Returns : None
682 --
683 PROCEDURE Is_Final_Approver
684 ( ItemType IN VARCHAR2
685 , ItemKey IN VARCHAR2
686 , ActID IN NUMBER
687 , FuncMode IN VARCHAR2
688 , ResultOut OUT NOCOPY VARCHAR2
689 ) IS
690
691 ApprovalSeq NUMBER;
692 ApprovalSteps VARCHAR2(4000);
693 NextApprRoleID NUMBER;
694 NextApprSeq NUMBER;
695
696 BEGIN
697
698 IF ( FuncMode = 'RUN' ) THEN
699
700 Load_Globals( ItemType , ItemKey );
701
702 ApprovalSteps := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'APPROVAL_STEPS');
703 ApprovalSeq := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVAL_SEQUENCE');
704
705 OKE_APPROVAL_PATHS_PKG.Next_Approval_Step( ApprovalSteps , ApprovalSeq , NextApprSeq , NextApprRoleID );
706
707 IF ( NextApprRoleID IS NULL ) THEN
708 ResultOut := 'COMPLETE:Y';
709 ELSE
710 ResultOut := 'COMPLETE:N';
711 END IF;
712 RETURN;
713
714 END IF;
715
716 IF ( FuncMode = 'CANCEL' ) THEN
717
718 ResultOut := WF_ENGINE.ENG_NULL;
719 RETURN;
720
721 END IF;
722
723 IF ( FuncMode = 'TIMEOUT' ) THEN
724
725 ResultOut := WF_ENGINE.ENG_NULL;
726 RETURN;
727
728 END IF;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 ResultOut := 'ERROR:';
733 WF_ENGINE.SetItemAttrText
734 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
735 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
736 , 'IS_FINAL_APPROVER'
737 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
738 RAISE;
739
740 END Is_Final_Approver;
741
742
743 --
744 -- Name : Is_Requestor_Approver
745 -- Pre-reqs : Must be called from WF activity
746 -- Function : This procedure checks wheter the approver happens
747 -- to be also the requestor.
748 --
749 -- Parameters :
750 -- IN : ItemType
751 -- ItemKey
752 -- ActID
753 -- FuncMode
754 -- OUT : ResultOut ( WFSTD_YES_NO )
755 --
756 -- Returns : None
757 --
758 PROCEDURE Is_Requestor_Approver
759 ( ItemType IN VARCHAR2
760 , ItemKey IN VARCHAR2
761 , ActID IN NUMBER
762 , FuncMode IN VARCHAR2
763 , ResultOut OUT NOCOPY VARCHAR2
764 ) IS
765
766 Approver VARCHAR2(240);
767
768 BEGIN
769
770 IF ( FuncMode = 'RUN' ) THEN
771
772 Load_Globals( ItemType , ItemKey );
773
774 Approver := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'APPROVER');
775
776 IF ( Approver = G_Requestor ) THEN
777 --
778 -- If approver is the requestor, there is no need to seek approval.
779 -- Add to Approval History directly and move on.
780 --
781 Add_To_History
782 ( PerformerName => Approver
783 , ActionCode => 'APPROVED'
784 , ActionDate => sysdate
785 , ApprovalPathID => G_Aprv_Path
786 , ApprovalSeq => G_Aprv_Seq
787 , ApproverRoleID => NULL
788 , NoteText => NULL
789 );
790
791 ResultOut := 'COMPLETE:Y';
792
793 ELSE
794
795 ResultOut := 'COMPLETE:N';
796
797 END IF;
798
799 RETURN;
800
801 END IF;
802
803 IF ( FuncMode = 'CANCEL' ) THEN
804
805 ResultOut := WF_ENGINE.ENG_NULL;
806 RETURN;
807
808 END IF;
809
810 IF ( FuncMode = 'TIMEOUT' ) THEN
811
812 ResultOut := WF_ENGINE.ENG_NULL;
813 RETURN;
814
815 END IF;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 ResultOut := 'ERROR:';
820 WF_ENGINE.SetItemAttrText
821 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
822 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
823 , 'IS_REQUESTOR_APPROVER'
824 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
825 RAISE;
826
827 END Is_Requestor_Approver;
828
829
830 --
831 -- Name : Is_Signature_Required
832 -- Pre-reqs : Must be called from WF activity
833 -- Function : This procedure checks whether signature is required
834 -- based on the approval hierarchy
835 --
836 -- Parameters :
837 -- IN : ItemType
838 -- ItemKey
839 -- ActID
840 -- FuncMode
841 -- OUT : ResultOut ( WFSTD_YES_NO )
842 --
843 -- Returns : None
844 --
845 PROCEDURE Is_Signature_Required
846 ( ItemType IN VARCHAR2
847 , ItemKey IN VARCHAR2
848 , ActID IN NUMBER
849 , FuncMode IN VARCHAR2
850 , ResultOut OUT NOCOPY VARCHAR2
851 ) IS
852
853 BEGIN
854
855 IF ( FuncMode = 'RUN' ) THEN
856
857 IF ( WF_ENGINE.GetItemAttrText( ItemType , ItemKey , 'SIGNATURE_REQUIRED' ) = 'Y' ) THEN
858 ResultOut := 'COMPLETE:Y';
859 ELSE
860 ResultOut := 'COMPLETE:N';
861 END IF;
862 RETURN;
863
864 END IF;
865
866 IF ( FuncMode = 'CANCEL' ) THEN
867
868 ResultOut := WF_ENGINE.ENG_NULL;
869 RETURN;
870
871 END IF;
872
873 IF ( FuncMode = 'TIMEOUT' ) THEN
874
875 ResultOut := WF_ENGINE.ENG_NULL;
876 RETURN;
877
878 END IF;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 ResultOut := 'ERROR:';
883 WF_ENGINE.SetItemAttrText
884 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
885 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
886 , 'IS_SIGNATURE_REQUIRED'
887 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
888 RAISE;
889
890 END Is_Signature_Required;
891
892
893 --
894 -- Name : Rej_Note_Filled
895 -- Pre-reqs : Must be called from WF activity
896 -- Function : This procedure verifies that the note is filled if
897 -- the contract was rejected.
898 --
899 -- Parameters :
900 -- IN : ItemType
901 -- ItemKey
902 -- ActID
903 -- FuncMode
904 -- OUT : ResultOut ( WFSTD_YES_NO )
905 --
906 -- Returns : None
907 --
908 PROCEDURE Rej_Note_Filled
909 ( ItemType IN VARCHAR2
910 , ItemKey IN VARCHAR2
911 , ActID IN NUMBER
912 , FuncMode IN VARCHAR2
913 , ResultOut OUT NOCOPY VARCHAR2
914 ) IS
915
916 BEGIN
917
918 IF ( FuncMode = 'RUN' ) THEN
919
920 IF ( WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'NOTE') IS NULL
921 AND WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'WF_NOTE') IS NULL ) THEN
922 ResultOut := 'COMPLETE:N';
923 ELSE
924 ResultOut := 'COMPLETE:Y';
925 END IF;
926
927 RETURN;
928
929 END IF;
930
931 IF ( FuncMode = 'CANCEL' ) THEN
932
933 ResultOut := WF_ENGINE.ENG_NULL;
934 RETURN;
935
936 END IF;
937
938 IF ( FuncMode = 'TIMEOUT' ) THEN
939
940 ResultOut := WF_ENGINE.ENG_NULL;
941 RETURN;
942
943 END IF;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 ResultOut := 'ERROR:';
948 WF_ENGINE.SetItemAttrText
949 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
950 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
951 , 'REJ_NOTE_FILLED'
952 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
953 RAISE;
954
955 END Rej_Note_Filled;
956
957
958 --
959 -- Name : Select_Next_Approver
960 -- Pre-reqs : Must be called from WF activity
961 -- Function : This procedure determines the next approver for
962 -- the contract based on the approval hierarchy.
963 --
964 -- Parameters :
965 -- IN : ItemType
966 -- ItemKey
967 -- ActID
968 -- FuncMode
969 -- OUT : ResultOut ( WFSTD_BOOLEAN )
970 --
971 -- Returns : None
972 --
973 PROCEDURE Select_Next_Approver
974 ( ItemType IN VARCHAR2
975 , ItemKey IN VARCHAR2
976 , ActID IN NUMBER
977 , FuncMode IN VARCHAR2
978 , ResultOut OUT NOCOPY VARCHAR2
979 ) IS
980
981 ApprovalSeq NUMBER;
982 ApprovalPath NUMBER;
983 ApprovalSteps VARCHAR2(4000);
984 NextAppr VARCHAR2(240);
985 NextApprRole VARCHAR2(240);
986 NextApprRoleID NUMBER;
987 NextApprSeq NUMBER;
988
989 BEGIN
990
991 IF ( FuncMode = 'RUN' ) THEN
992
993 Load_Globals( ItemType , ItemKey );
994
995 ApprovalSteps := WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'APPROVAL_STEPS');
996 ApprovalPath := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVAL_PATH_ID');
997 ApprovalSeq := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVAL_SEQUENCE');
998
999 OKE_APPROVAL_PATHS_PKG.Next_Approval_Step( ApprovalSteps , ApprovalSeq , NextApprSeq , NextApprRoleID );
1000
1001 IF ( NextApprRoleID IS NULL ) THEN
1002 --
1003 -- This is a strange condition; it should have been trapped by
1004 -- the IS_FINAL_APPROVER check
1005 --
1006 -- Ideally, we need to pass this back to IS_FINAL_APPROVER activity.
1007 -- But for now, we are failing this.
1008 --
1009 ResultOut := 'COMPLETE:F';
1010 RETURN;
1011 END IF;
1012
1013 --
1014 -- Now that we find the next approver role, we need to find the next
1015 -- approver based on the role.
1016 --
1017 NextApprRole := K_Role_Name( NextApprRoleID );
1018 NextAppr := KRole_To_WFRole( NextApprRoleID );
1019
1020 --
1021 -- Push previous approver to the PREV_APPROVER% attributes
1022 --
1023 WF_ENGINE.SetItemAttrText
1024 ( ItemType , ItemKey , 'PREV_APPROVER'
1025 , WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'APPROVER') );
1026 WF_ENGINE.SetItemAttrText
1027 ( ItemType , ItemKey , 'PREV_APPROVER_ROLE'
1028 , WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'APPROVER_ROLE') );
1029 WF_ENGINE.SetItemAttrNumber
1030 ( ItemType , ItemKey , 'PREV_APPROVER_ROLE_ID'
1031 , WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'APPROVER_ROLE_ID') );
1032
1033 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'APPROVER' , NextAppr );
1034 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'APPROVER_ROLE' , NextApprRole );
1035 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'APPROVER_ROLE_ID' , NextApprRoleID );
1036 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'APPROVAL_SEQUENCE' , NextApprSeq );
1037 --
1038 -- Erase note text
1039 --
1040 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'NOTE' , NULL );
1041 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'WF_NOTE' , NULL );
1042
1043 IF ( NextAppr IS NULL ) THEN
1044 ResultOut := 'COMPLETE:F';
1045 ELSE
1046 --
1047 -- Write Approval History
1048 --
1049 Add_To_History
1050 ( PerformerName => NextAppr
1051 , ActionCode => 'ASSIGNED'
1052 , ActionDate => sysdate
1053 , ApprovalPathID => ApprovalPath
1054 , ApprovalSeq => NextApprSeq
1055 , ApproverRoleID => NextApprRoleID
1056 , NoteText => NULL
1057 );
1058
1059 ResultOut := 'COMPLETE:T';
1060
1061 END IF;
1062
1063 RETURN;
1064
1065 END IF;
1066
1067 IF ( FuncMode = 'CANCEL' ) THEN
1068
1069 ResultOut := WF_ENGINE.ENG_NULL;
1070 RETURN;
1071
1072 END IF;
1073
1074 IF ( FuncMode = 'TIMEOUT' ) THEN
1075
1076 ResultOut := WF_ENGINE.ENG_NULL;
1077 RETURN;
1078
1079 END IF;
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 ResultOut := 'ERROR:';
1084 WF_ENGINE.SetItemAttrText
1085 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
1086 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1087 , 'SELECT_NEXT_APPROVER'
1088 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1089 RAISE;
1090
1091 END Select_Next_Approver;
1092
1093
1094 --
1095 -- Name : Select_Signatory
1096 -- Pre-reqs : Must be called from WF activity
1097 -- Function : This procedure determines the signatory for
1098 -- the contract based on the approval hierarchy
1099 --
1100 -- Parameters :
1101 -- IN : ItemType
1102 -- ItemKey
1103 -- ActID
1104 -- FuncMode
1105 -- OUT : ResultOut ( WFSTD_BOOLEAN )
1106 --
1107 -- Returns : None
1108 --
1109 PROCEDURE Select_Signatory
1110 ( ItemType IN VARCHAR2
1111 , ItemKey IN VARCHAR2
1112 , ActID IN NUMBER
1113 , FuncMode IN VARCHAR2
1114 , ResultOut OUT NOCOPY VARCHAR2
1115 ) IS
1116
1117 SignatoryRole NUMBER;
1118 Signatory VARCHAR2(240);
1119
1120 BEGIN
1121
1122 IF ( FuncMode = 'RUN' ) THEN
1123
1124 Load_Globals( ItemType , ItemKey );
1125
1126 SignatoryRole := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'SIGNATORY_ROLE_ID');
1127 Signatory := KRole_To_WFRole( SignatoryRole );
1128
1129 IF ( Signatory IS NULL ) THEN
1130 ResultOut := 'COMPLETE:F';
1131 ELSE
1132
1133 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'SIGNATORY' , Signatory );
1134
1135 --
1136 -- Erase note text
1137 --
1138 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'NOTE' , NULL );
1139 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'WF_NOTE' , NULL );
1140
1141 --
1142 -- Write Approval History
1143 --
1144 Add_To_History
1145 ( PerformerName => Signatory
1146 , ActionCode => 'ASSIGNED_SIGNATURE'
1147 , ActionDate => sysdate
1148 , ApprovalPathID => G_Aprv_Path
1149 , ApprovalSeq => G_Aprv_Seq
1150 , ApproverRoleID => SignatoryRole
1151 , NoteText => NULL
1152 );
1153
1154 ResultOut := 'COMPLETE:T';
1155
1156 END IF;
1157
1158 RETURN;
1159
1160 END IF;
1161
1162 IF ( FuncMode = 'CANCEL' ) THEN
1163
1164 ResultOut := WF_ENGINE.ENG_NULL;
1165 RETURN;
1166
1167 END IF;
1168
1169 IF ( FuncMode = 'TIMEOUT' ) THEN
1170
1171 ResultOut := WF_ENGINE.ENG_NULL;
1172 RETURN;
1173
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 ResultOut := 'ERROR:';
1179 WF_ENGINE.SetItemAttrText
1180 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
1181 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1182 , 'SELECT_SIGNATORY'
1183 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1184 RAISE;
1185
1186 END Select_Signatory;
1187
1188
1189 --
1190 -- Name : Set_Approval_History
1191 -- Pre-reqs : Must be called from WF activity
1192 -- Function : This post-notification procedure records the approval
1193 -- history based on the notification response
1194 --
1195 -- Parameters :
1196 -- IN : ItemType
1197 -- ItemKey
1198 -- ActID
1199 -- FuncMode
1200 -- OUT : ResultOut ( None )
1201 --
1202 -- Returns : None
1203 --
1204 PROCEDURE Set_Approval_History
1205 ( ItemType IN VARCHAR2
1206 , ItemKey IN VARCHAR2
1207 , ActID IN NUMBER
1208 , FuncMode IN VARCHAR2
1209 , ResultOut OUT NOCOPY VARCHAR2
1210 ) IS
1211
1212 CURSOR NtfDtls IS
1213 SELECT message_name
1214 , recipient_role
1215 , responder
1216 FROM wf_notifications
1217 WHERE notification_id = WF_ENGINE.CONTEXT_NID;
1218 NtfDtlRec NtfDtls%rowtype;
1219
1220 Performer VARCHAR2(320);
1221 ActionCode VARCHAR2(30);
1222
1223 --
1224 -- The following is an exception to detect invalid forward-to
1225 -- recipient
1226 --
1227 INVALID_FORWARD_TO EXCEPTION;
1228
1229 BEGIN
1230 ActionCode := 'UNKNOWN';
1231
1232 IF ( FuncMode IN ( 'FORWARD' , 'TRANSFER' , 'RESPOND' ) ) THEN
1233
1234 Load_Globals( ItemType , ItemKey );
1235
1236 --
1237 -- In order to write approval history, we need several notification
1238 -- details, such as the message_name (to determine if it is for
1239 -- approval or signature) and the final recipient
1240 --
1241 OPEN NtfDtls;
1242 FETCH NtfDtls INTO NtfDtlRec;
1243 CLOSE NtfDtls;
1244
1245 --
1246 -- Write Approval History
1247 --
1248 IF ( FuncMode IN ( 'FORWARD' , 'TRANSFER' ) ) THEN
1249
1250 Performer := WF_ENGINE.CONTEXT_TEXT;
1251 ActionCode := FuncMode;
1252
1253 ELSE
1254
1255 Performer := NtfDtlRec.Recipient_Role;
1256
1257 WF_ENGINE.SetItemAttrText( ItemType , ItemKey , 'APPROVER' , Performer );
1258
1259 IF ( NtfDtlRec.Message_Name = 'MSG_APPROVAL' ) THEN
1260
1261 ActionCode := WF_NOTIFICATION.GetAttrText( nid => WF_ENGINE.CONTEXT_NID , aname => 'RESULT' );
1262
1263 ELSIF ( NtfDtlRec.Message_Name = 'MSG_SIGNATURE' ) THEN
1264
1265 IF ( WF_NOTIFICATION.GetAttrText( nid => WF_ENGINE.CONTEXT_NID , aname => 'RESULT' ) = 'Y' ) THEN
1266 ActionCode := 'SIGNED';
1267 ELSE
1268 ActionCode := 'DID_NOT_SIGN';
1269 END IF;
1270
1271 END IF;
1272
1273 END IF;
1274
1275 Add_To_History
1276 ( PerformerName => Performer
1277 , ActionCode => ActionCode
1278 , ActionDate => sysdate
1279 , ApprovalPathID => G_Aprv_Path
1280 , ApprovalSeq => G_Aprv_Seq
1281 , ApproverRoleID => NULL
1282 -- , NoteText => WF_NOTIFICATION.GetAttrText( nid => WF_ENGINE.CONTEXT_NID , aname => 'NOTE' )
1283 , NoteText => WF_NOTIFICATION.GetAttrText( nid => WF_ENGINE.CONTEXT_NID , aname => 'WF_NOTE' )
1284 );
1285
1286 ResultOut := 'COMPLETE:';
1287 RETURN;
1288
1289 END IF;
1290
1291 IF ( FuncMode = 'TIMEOUT' ) THEN
1292
1293 ResultOut := 'COMPLETE:';
1294 RETURN;
1295
1296 END IF;
1297
1298 EXCEPTION
1299 WHEN INVALID_FORWARD_TO THEN
1300 ResultOut := 'ERROR:';
1301 WF_ENGINE.SetItemAttrText
1302 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT'
1303 , AValue => fnd_message.get_string('OKE' , 'OKE_APRV_INVALID_FORWARDTO') );
1304 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1305 , 'SET_APPROVAL_HISTORY'
1306 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1307 RAISE;
1308
1309 WHEN OTHERS THEN
1310 ResultOut := 'ERROR:';
1311 WF_ENGINE.SetItemAttrText
1312 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
1313 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1314 , 'SET_APPROVAL_HISTORY'
1315 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1316 RAISE;
1317
1318 END Set_Approval_History;
1319
1320
1321 --
1322 -- Name : Validate_Approval_Path
1323 -- Pre-reqs : Must be called from WF activity
1324 -- Function : This procedure validates the approval hierarchy
1325 -- associated with the contract document type.
1326 --
1327 -- Parameters :
1328 -- IN : ItemType
1329 -- ItemKey
1330 -- ActID
1331 -- FuncMode
1332 -- OUT : ResultOut ( WFSTD_BOOLEAN )
1333 --
1334 -- Returns : None
1335 --
1336 PROCEDURE Validate_Approval_Path
1337 ( ItemType IN VARCHAR2
1338 , ItemKey IN VARCHAR2
1339 , ActID IN NUMBER
1340 , FuncMode IN VARCHAR2
1341 , ResultOut OUT NOCOPY VARCHAR2
1342 ) IS
1343
1344 CURSOR c IS
1345 SELECT KT.approval_path_id
1346 , AP.name approval_path
1347 , AP.signature_required_flag
1348 , AP.signatory_role_id
1349 FROM oke_approval_paths_v AP
1350 , oke_k_types_b KT
1351 WHERE KT.k_type_code = G_K_Type_Code
1352 AND AP.approval_path_id = KT.approval_path_id;
1353 crec c%rowtype;
1354
1355 ApprovalSteps VARCHAR2(4000);
1356
1357 BEGIN
1358
1359 IF ( FuncMode = 'RUN' ) THEN
1360
1361 Load_Globals( ItemType , ItemKey );
1362
1363 OPEN c;
1364 FETCH c INTO crec;
1365 CLOSE c;
1366
1367 ApprovalSteps := OKE_APPROVAL_PATHS_PKG.Approval_Steps( crec.approval_path_id );
1368
1369 IF ( ApprovalSteps <> ';' ) THEN
1370
1371 WF_ENGINE.SetItemAttrNumber( itemtype => ItemType
1372 , itemkey => ItemKey
1373 , aname => 'APPROVAL_PATH_ID'
1374 , avalue => crec.Approval_Path_ID );
1375
1376 WF_ENGINE.SetItemAttrText( itemtype => ItemType
1377 , itemkey => ItemKey
1378 , aname => 'APPROVAL_PATH'
1379 , avalue => crec.Approval_Path );
1380
1381 WF_ENGINE.SetItemAttrText( itemtype => ItemType
1382 , itemkey => ItemKey
1383 , aname => 'APPROVAL_STEPS'
1384 , avalue => ApprovalSteps );
1385
1386 WF_ENGINE.SetItemAttrText( itemtype => ItemType
1387 , itemkey => ItemKey
1388 , aname => 'SIGNATURE_REQUIRED'
1389 , avalue => crec.Signature_Required_Flag );
1390
1391 WF_ENGINE.SetItemAttrNumber( itemtype => ItemType
1392 , itemkey => ItemKey
1393 , aname => 'SIGNATORY_ROLE_ID'
1394 , avalue => crec.Signatory_Role_ID );
1395
1396 WF_ENGINE.SetItemAttrText( itemtype => ItemType
1397 , itemkey => ItemKey
1398 , aname => 'SIGNATORY_ROLE'
1399 , avalue => K_Role_Name( crec.Signatory_Role_ID ) );
1400
1401 --
1402 -- All set to go, write Approval History to record submission of approval process
1403 --
1404 Add_To_History
1405 ( PerformerName => G_Requestor
1406 , ActionCode => 'SUBMITTED'
1407 , ActionDate => sysdate
1408 , ApprovalPathID => crec.Approval_Path_ID
1409 , ApprovalSeq => 0
1410 , ApproverRoleID => NULL
1411 , NoteText => NULL
1412 );
1413
1414 ResultOut := 'COMPLETE:T';
1415 ELSE
1416 ResultOut := 'COMPLETE:F';
1417 END IF;
1418 RETURN;
1419
1420 END IF;
1421
1422 IF ( FuncMode = 'CANCEL' ) THEN
1423
1424 ResultOut := WF_ENGINE.ENG_NULL;
1425 RETURN;
1426
1427 END IF;
1428
1429 IF ( FuncMode = 'TIMEOUT' ) THEN
1430
1431 ResultOut := WF_ENGINE.ENG_NULL;
1432 RETURN;
1433
1434 END IF;
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 ResultOut := 'ERROR:';
1439 WF_ENGINE.SetItemAttrText
1440 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
1441 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1442 , 'VALIDATE_APPROVAL_PATH'
1443 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1444 RAISE;
1445
1446 END Validate_Approval_Path;
1447
1448 --bug#5846706
1449 --
1450 -- Name : Erase_Approved
1451 -- Pre-reqs : Must be called from WF activity
1452 -- Function : It erases approved date when signatory rejects the contract
1453 --
1454 --
1455 -- Parameters :
1456 -- IN : ItemType
1457 -- ItemKey
1458 -- ActID
1459 -- FuncMode
1460 -- OUT NOCOPY /* file.sql.39 change */ : ResultOut ( None )
1461 --
1462 -- Returns : None
1463 --
1464 PROCEDURE Erase_Approved
1465 ( ItemType IN VARCHAR2
1466 , ItemKey IN VARCHAR2
1467 , ActID IN NUMBER
1468 , FuncMode IN VARCHAR2
1469 , ResultOut OUT NOCOPY VARCHAR2
1470 ) IS
1471 L_CONTRACT_ID number;
1472 x_return_status varchar2(1);
1473 BEGIN
1474 IF ( FuncMode = 'RUN' ) THEN
1475
1476 L_CONTRACT_ID := WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'CONTRACT_ID');
1477 --bug#5933768
1478 IF(mo_global.is_mo_init_done = 'N') then
1479 mo_global.init('OKE');
1480 okc_context.set_okc_org_context(p_chr_id => L_CONTRACT_ID);
1481 End IF;
1482 --ends
1483 OKE_CONTRACT_APPROVAL_PUB.k_erase_approved(
1484 p_contract_id => L_CONTRACT_ID,
1485 x_return_status => x_return_status);
1486 IF (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1487 THEN
1488 ResultOut := 'COMPLETE:T';
1489 RETURN;
1490 ELSE
1491 ResultOut := 'COMPLETE:F';
1492 RETURN;
1493 END IF;
1494 END IF;
1495
1496 IF ( FuncMode = 'CANCEL' ) THEN
1497
1498 ResultOut := WF_ENGINE.ENG_NULL;
1499 RETURN;
1500
1501 END IF;
1502
1503 IF ( FuncMode = 'TIMEOUT' ) THEN
1504
1505 ResultOut :=WF_ENGINE.ENG_NULL;
1506 RETURN;
1507
1508 END IF;
1509
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 ResultOut := 'ERROR:';
1513 WF_ENGINE.SetItemAttrText
1514 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
1515 WF_CORE.Context( 'OKE_K_APPROVAL_WF'
1516 , 'Erase_Approved'
1517 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
1518 RAISE;
1519
1520 END Erase_Approved;
1521
1522
1523
1524 END OKE_K_APPROVAL_WF;