[Home] [Help]
PACKAGE BODY: APPS.OKC_WF_K_APPROVE
Source
1 package body OKC_WF_K_APPROVE as
2 /* $Header: OKCWCAPB.pls 120.2.12000000.3 2007/05/25 18:01:19 skkoppul ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7 -- Start of comments
8 --
9 -- Procedure Name : empty_mess
10 -- Description : Private procedure to empty message attributes
11 -- Business Rules :
12 -- Parameters :
13 -- Version : 1.0
14 -- End of comments
15
16 procedure empty_mess( itemtype in varchar2,
17 itemkey in varchar2) is
18 i integer;
19 begin
20 FOR I IN 1..9 LOOP
21 wf_engine.SetItemAttrText (itemtype => itemtype,
22 itemkey => itemkey,
23 aname => 'MESSAGE'||i,
24 avalue => '');
25 END LOOP;
26 end;
27
28 -- Start of comments
29 --
30 -- Procedure Name : load_mess
31 -- Description : Private procedure to load messages into attributes
32 -- Business Rules :
33 -- Parameters :
34 -- Version : 1.0
35 -- End of comments
36
37 procedure load_mess( itemtype in varchar2,
38 itemkey in varchar2) is
39 i integer;
40 j integer;
41 begin
42 j := NVL(FND_MSG_PUB.Count_Msg,0);
43 if (j=0) then return; end if;
44 if (j>9) then j:=9; end if;
45 FOR I IN 1..J LOOP
46 wf_engine.SetItemAttrText (itemtype => itemtype,
47 itemkey => itemkey,
48 aname => 'MESSAGE'||i,
49 avalue => FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
50 END LOOP;
51 end;
52
53
54 ----------------------------------------------------------------------------
55 --
56 -- C U S T O M I Z E select_next
57 --
58 ----------------------------------------------------------------------------
59 -- Start of comments
60 --
61 -- Procedure Name : select_next
62 -- Description :
63 -- Business Rules :
64 -- Parameters :
65 -- Version : 1.0
66 -- End of comments
67
68 procedure select_next(itemtype in varchar2 default NULL,
69 itemkey in varchar2 default NULL,
70 p_role_type in varchar2,
71 p_current in varchar2 default NULL,
72 x_role out nocopy varchar2,
73 x_name out nocopy varchar2
74 ) is
75
76 --
77 -- Next Approver Cursor - here comes from user profile option value
78 --
79
80 /*
81
82 Due to performance reasons we decided to refuse from
83
84 (select 1 num,
85 FND_PROFILE.VALUE('OKC_K_APPROVER') role,
86 DISPLAY_NAME name
87 from wf_roles
88 where name=FND_PROFILE.VALUE('OKC_K_APPROVER'))
89
90 and restrict select to fnd_user data only
91
92 might be customisation problem
93
94 */
95
96 cursor Next_Approver_csr is
97 select role, name
98 from -- here should be your view of structure (num,role,name)
99 ------------------------------------------------------
100 (
101 select 1 num,
102 FND_PROFILE.VALUE('OKC_K_APPROVER') role,
103 FND_PROFILE.VALUE('OKC_K_APPROVER') name
104 from dual
105 where not exists
106 ( select '!'
107 from FND_USER USR, PER_PEOPLE_F PER
108 where USR.USER_NAME=FND_PROFILE.VALUE('OKC_K_APPROVER')
109 and USR.EMPLOYEE_ID = PER.PERSON_ID
110 )
111 union all
112 select 1 num,
113 FND_PROFILE.VALUE('OKC_K_APPROVER') role,
114 NVL(PER.FULL_NAME,FND_PROFILE.VALUE('OKC_K_APPROVER')) name
115 from FND_USER USR, PER_PEOPLE_F PER
116 where USR.USER_NAME=FND_PROFILE.VALUE('OKC_K_APPROVER')
117 and USR.EMPLOYEE_ID = PER.PERSON_ID
118 )
119 ------------------------------------------------------
120 where p_current is NULL
121 order by num;
122
123 /*
124
125 -- For responsibility as an approver role
126 -- Profile option should be validated by statement:
127 -- SQL="SELECT RESPONSIBILITY_NAME \"Contract Approver\",
128 -- 'FND_RESP'||r.APPLICATION_ID||':'||RESPONSIBILITY_ID \"WF Role Name\"
129 -- INTO :visible_option_value, :profile_option_value
130 -- from fnd_responsibility_vl r, fnd_application a
131 -- where r.APPLICATION_ID = a.APPLICATION_ID
132 -- and a.APPLICATION_SHORT_NAME like 'OK_'
133 -- order by RESPONSIBILITY_NAME"
134
135 -- For role comming from fnd_user use validation:
136 -- SQL="SELECT USER_NAME \"Contract Approver\",
137 -- USER_NAME \"WF Role Name\"
138 -- INTO :visible_option_value, :profile_option_value
139 -- from fnd_user
140 -- order by USER_NAME"
141 -- COLUMN="\"Contract Approver\"(30)"
142 -- COLUMN="\"WF Role Name\"(30)"
143
144 */
145
146 l_initiator varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'INITIATOR_NAME');
147 l_approver varchar2(100) :=
148 NVL(wf_engine.GetItemAttrText(itemtype,itemkey,'NEXT_PERFORMER_USERNAME'),
149 wf_engine.GetItemAttrText(itemtype,itemkey,'FINAL_APPROVER_UNAME'));
150
151 --
152 -- Next Informed Cursor = Initiator + Approver
153 --
154
155 cursor Next_Informed_csr is
156 select role, name from -- here should be your view
157 ------------------------------------------------------
158 (select 1 num, l_approver role, '' name from dual
159 where l_approver is not NULL
160 union all
161 select 2 num, l_initiator role, '' name from dual
162 where not exists
163 (select 1 from wf_user_roles
164 where user_name=l_initiator
165 and USER_ORIG_SYSTEM IN ('PER','FND_USR')
166 and ROLE_NAME=l_approver
167 )
168 )
169 ------------------------------------------------------
170 where (p_current is NULL
171 or num > (select num from -- same view
172 ------------------------------------------------------
173 (select 1 num, l_approver role, '' name from dual
174 where l_approver is not NULL
175 union all
176 select 2 num, l_initiator role, '' name from dual
177 where not exists
178 (select 1 from wf_user_roles
179 where user_name=l_initiator
180 and USER_ORIG_SYSTEM IN ('PER','FND_USR')
181 and ROLE_NAME=l_approver
182 )
183 )
184 ------------------------------------------------------
185 where role = p_current)
186 ) order by num;
187
188 /*
189 -- commented here cursor is more universal but has bad performance,
190 -- it is replaced by previous cursor, that is not universal
191 -- you should customize previous cursor, not this one
192
193 cursor Next_Informed_csr is
194 select role, name from -- here should be your view
195 ------------------------------------------------------
196 (select rownum num, user_name role, '' name
197 from
198 (select distinct user_name from wf_user_roles
199 where role_name in (l_initiator,l_approver)))
200 ------------------------------------------------------
201 where (p_current is NULL
202 or num > (select num from -- same view
203 ------------------------------------------------------
204 (select rownum num, user_name role, '' name
205 from
206 (select distinct user_name from wf_user_roles
207 where role_name in (l_initiator,l_approver)))
208 ------------------------------------------------------
209 where role = p_current)
210 ) order by num;
211 */
212
213 begin
214 --
215 -- Administrator is backup guy - here = initiator
216 --
217 if (p_role_type = 'ADMINISTRATOR') then
218 x_role := wf_engine.GetItemAttrText(
219 itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'INITIATOR_NAME');
222 x_name := wf_engine.GetItemAttrText(
223 itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'INITIATOR_DISPLAY_NAME');
226 --
227 -- Signotory here = initiator
228 --
229 elsif (p_role_type = 'SIGNATORY') then
230 x_role := wf_engine.GetItemAttrText(
231 itemtype => itemtype,
232 itemkey => itemkey,
233 aname => 'INITIATOR_NAME');
234 x_name := wf_engine.GetItemAttrText(
235 itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'INITIATOR_DISPLAY_NAME');
238 --
239 -- Approver - in cursor
240 --
241 elsif (p_role_type = 'APPROVER') then
242 open Next_Approver_csr;
243 fetch Next_Approver_csr into x_role, x_name;
244 close Next_Approver_csr;
245 --
246 -- Informed - in cursor
247 --
248 elsif (p_role_type = 'INFORMED') then
249 open Next_Informed_csr;
250 fetch Next_Informed_csr into x_role, x_name;
251 close Next_Informed_csr;
252 end if;
253 end select_next;
254 ----------------------------------------------------------------------------
255 --
256 -- You can stop customization here
257 --
258 ----------------------------------------------------------------------------
259
260
261 -- Start of comments
262 --
263 -- Procedure Name : valid_approver
264 -- Description : check for approver account to be active (from WF point of view)
265 -- Business Rules :
266 -- Parameters :
267 -- Version : 1.0
268 -- End of comments
269
270 procedure valid_approver(itemtype in varchar2,
271 itemkey in varchar2,
272 actid in number,
273 funcmode in varchar2,
274 resultout out nocopy varchar2 ) is
275
276 cursor c1(p_account varchar2) is
277 select 'T'
278 from FND_USER
279 where
280 FND_USER.USER_NAME=p_account
281 and FND_USER.EMPLOYEE_ID is NULL
282 and trunc(sysdate) between trunc(start_date) and nvl(end_date,sysdate)
283 union all
284 select 'T'
285 from FND_USER USR, PER_PEOPLE_F PER
286 where USR.USER_NAME=p_account
287 and trunc(sysdate) between trunc(USR.start_date) and nvl(USR.end_date,sysdate)
288 and USR.EMPLOYEE_ID = PER.PERSON_ID
289 and trunc(sysdate) between trunc(per.effective_start_date) and nvl(per.effective_end_date,sysdate)
290 ;
291 l_dummy varchar2(1) := 'F';
292
293 begin
294 mo_global.init('OKC');
295 --
296 -- RUN mode - normal process execution
297 --
298 if (funcmode = 'RUN') then
299 open c1(wf_engine.GetItemAttrText(itemtype,itemkey,'NEXT_PERFORMER_USERNAME'));
300 fetch c1 into l_dummy;
301 close c1;
302 resultout := 'COMPLETE:'||l_dummy;
303 end if;
304 --
305 -- CANCEL mode
306 --
307 if (funcmode = 'CANCEL') then
308 --
309 resultout := 'COMPLETE:';
310 return;
311 --
312 end if;
313 --
314 -- TIMEOUT mode
315 --
316 if (funcmode = 'TIMEOUT') then
317 --
318 resultout := 'COMPLETE:';
319 return;
320 --
321 end if;
322 exception
323 when others then
324 wf_core.context('OKC_WF_K_APPROVE',
325 'VALID_APPROVER',
326 itemtype,
327 itemkey,
328 to_char(actid),
329 funcmode);
330 raise;
331 end valid_approver;
332
333 -- Start of comments
334 --
335 -- Procedure Name : Selector
336 -- Description : Selector/Callback function - no need to customize
337 -- Business Rules :
338 -- Parameters :
339 -- Version : 1.0
340 -- End of comments
341
342 procedure Selector ( item_type in varchar2,
343 item_key in varchar2,
344 activity_id in number,
345 command in varchar2,
346 resultout out nocopy varchar2 ) is
347 -- local declarations
348 begin
349 resultout := ''; -- return value for other possible modes
350 --
351 -- RUN mode - normal process execution
352 --
353 if (command = 'RUN') then
354 --
355 -- Return process to run
356 --
357 resultout := 'K_APPROVAL_PROCESS';
358 return;
359 end if;
360
361 --
362 -- SET_CTX mode - set context for new DB session
363 --
364 if (command = 'SET_CTX') then
365 OKC_CONTRACT_APPROVAL_PUB.wf_copy_env(
366 p_item_type => item_type,
367 p_item_key => item_key);
368 return;
369 end if;
370
371 --
372 -- TEST_CTX mode - test context
373 --
374 if (command = 'TEST_CTX') then
375 -- test code
376 resultout := 'TRUE';
377 return;
378 end if;
379
380 exception
381 when others then
382 wf_core.context('OKC_WF_K_APPROVE',
383 'SELECTOR',
384 item_type,
385 item_key,
386 to_char(activity_id),
387 command);
388 raise;
389 end Selector;
390
391 -- Start of comments
392 --
393 -- Procedure Name : Post_Approval
394 -- Description :
395 -- Business Rules :
396 -- Parameters :
397 -- Post_Approval : 1.0
398 -- End of comments
399
400 procedure Post_Approval( itemtype in varchar2,
401 itemkey in varchar2,
402 actid in number,
403 funcmode in varchar2,
404 resultout out nocopy varchar2 ) is
405 l_name varchar2(100):=wf_engine.GetItemAttrText(itemtype,itemkey,'NEXT_PERFORMER_USERNAME');
406 l_display_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'N_PERFORMER_DISPLAY_NAME');
407 l_context varchar2(100);
408 /* the cursor changed because of bug#2316572 -- INCORRECT APPROVER NAME FOR FORWARDED CONTRACT APPROVAL
409 cursor Actual_Performer_csr is
410 select --+ORDERED
411 u.name, u.display_name
412 from wf_user_roles r, wf_users u
413 where r.role_name=l_name
414 and r.USER_ORIG_SYSTEM=u.ORIG_SYSTEM
415 and r.USER_ORIG_SYSTEM_ID=u.ORIG_SYSTEM_ID
416 and
417 (
418 (l_context not like 'email:%' and u.NAME=l_context)
419 or
420 (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
421 );
422 */ -- bug#2316572
423 -- the above cursor is changed because of bug#2316572 (discussed with msengupt)
424 -- looks like we don't need select from wf_user_roles (reassignment to user not to role)
425 cursor Actual_Performer_csr is
426 select --+ORDERED
427 u.name, u.display_name
428 from
429 wf_users u
430 where
431 (
432 (l_context not like 'email:%' and u.NAME=l_context)
433 or
434 (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
435 );
436 --
437 begin
438 --
439 -- RESPOND mode
440 -- and TRANSFER mode added after the bug#2316572
441 -- if (funcmode = 'RESPOND') then -- also bug#2316572 - we need handle TRANSFER as well
442 if (funcmode in('RESPOND','TRANSFER')) then
443 l_context := wf_engine.context_text;
444 open Actual_Performer_csr;
445 fetch Actual_Performer_csr into l_name, l_display_name;
446 close Actual_Performer_csr;
447 wf_engine.SetItemAttrText (itemtype => itemtype,
448 itemkey => itemkey,
449 aname => 'ACTUAL_PERFORMER',
450 avalue => l_name);
451 wf_engine.SetItemAttrText (itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'ACTUAL_PERFORMER_D',
454 avalue => l_display_name);
455 return;
456 end if;
457 --
458 -- if other mode mode
459 --
460 --
461 --!!! run in CANCEL mode resultout := 'COMPLETE:';
462 return;
463 --
464 exception
465 when others then
466 wf_core.context('OKC_WF_K_APPROVE',
467 'POST_APPROVAL',
468 itemtype,
469 itemkey,
470 to_char(actid),
471 funcmode);
472 raise;
473 end Post_Approval;
474
475 -- Start of comments
476 --
477 -- Procedure Name : Post_Sign
478 -- Description :
479 -- Business Rules :
480 -- Parameters :
481 -- Post_Approval : 1.0
482 -- End of comments
483
484 procedure Post_Sign( itemtype in varchar2,
485 itemkey in varchar2,
486 actid in number,
487 funcmode in varchar2,
488 resultout out nocopy varchar2 ) is
489 l_name varchar2(100);
490 l_display_name varchar2(100);
491 l_context varchar2(100);
492
493 cursor Actual_Performer_csr is
494 select --+ORDERED
495 u.name, u.display_name
496 from
497 wf_users u
498 where
499 (
500 (l_context not like 'email:%' and u.NAME=l_context)
501 or
502 (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
503 );
504 begin
505 if (funcmode in('RESPOND','TRANSFER')) then
506 l_context := wf_engine.context_text;
507 open Actual_Performer_csr;
508 fetch Actual_Performer_csr into l_name, l_display_name;
509 close Actual_Performer_csr;
510 wf_engine.SetItemAttrText (itemtype => itemtype,
511 itemkey => itemkey,
512 aname => 'SIGNATORY_USERNAME',
513 avalue => l_name);
514 wf_engine.SetItemAttrText (itemtype => itemtype,
515 itemkey => itemkey,
516 aname => 'SIGNATORY_DISPLAY_NAME',
517 avalue => l_display_name);
518 return;
519 end if;
520 return;
521 exception
522 when others then
523 wf_core.context('OKC_WF_K_APPROVE',
524 'POST_SIGN',
525 itemtype,
526 itemkey,
527 to_char(actid),
528 funcmode);
529 raise;
530 end Post_Sign;
531
532 -- Start of comments
533 --
534 -- Procedure Name : IS_related
535 -- Description : determins K relation to IStore
536 -- Business Rules :
537 -- Parameters :
538 -- Version : 1.0
539 -- End of comments
540
541 procedure IS_related( itemtype in varchar2,
542 itemkey in varchar2,
543 actid in number,
544 funcmode in varchar2,
545 resultout out nocopy varchar2 ) is
546 L1 varchar2(1):='F';
547 /* commented by GunA
548 cursor c1 is
549 select 'T'
550 from OKC_K_REL_OBJS_V R, ASO_Quote_Headers_ALL Q
551 where R.chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
552 -- and R.RTY_CODE = 'CONTRACTNEGOTIATESQUOTE'
553 and R.RTY_CODE like 'CONTRACT%IS%TERM%FOR%QUOTE'
554 and R.CLE_ID IS NULL
555 and Q.QUOTE_HEADER_ID = R.OBJECT1_ID1
556 and Q.QUOTE_SOURCE_CODE like 'IStore%' ;
557 */
558 /* Cursor to check contracts in IStore and Quotation */
559 -- Bug#2208391 - added check of profile option 'OKC_CREATE_ORDER_FROM_K'
560 CURSOR c1 is
561 select 'T'
562 from okc_k_rel_objs
563 where chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
564 and JTOT_OBJECT1_CODE = G_OBJECT_CODE
565 ---and RTY_CODE = G_TERMSFORQUOTE ;
566 -- and RTY_CODE in (G_TERMSFORQUOTE, G_NEGOTIATESQUOTE);
567 and (RTY_CODE=G_TERMSFORQUOTE OR RTY_CODE=G_NEGOTIATESQUOTE
568 AND Nvl(Fnd_Profile.Value('OKC_CREATE_ORDER_FROM_K'),'Y')='N');
569 --taking BOTH into account as per Bug 2050306 abkumar
570 BEGIN
571 mo_global.init('OKC');
572 --
573 -- RUN mode - normal process execution
574 --
575 if (funcmode = 'RUN') then
576 open c1;
577 fetch c1 into L1;
578 close c1;
579 resultout := 'COMPLETE:'||L1;
580 end if;
581 --
582 -- CANCEL mode
583 --
584 if (funcmode = 'CANCEL') then
585 --
586 resultout := 'COMPLETE:';
587 return;
588 --
589 end if;
590 --
591 -- TIMEOUT mode
592 --
593 if (funcmode = 'TIMEOUT') then
594 --
595 resultout := 'COMPLETE:';
596 return;
597 --
598 end if;
599 exception
600 when others then
601 wf_core.context('OKC_WF_K_APPROVE',
602 'IS_RELATED',
603 itemtype,
604 itemkey,
605 to_char(actid),
606 funcmode);
607 raise;
608 end IS_related;
609
610
611
612 -- Start of comments
613 --
614 -- Procedure Name : note_filled
615 -- Description : note mandatory if reject
616 -- Business Rules :
617 -- Parameters :
618 -- Version : 1.0
619 -- End of comments
620
621 procedure note_filled( itemtype in varchar2,
622 itemkey in varchar2,
623 actid in number,
624 funcmode in varchar2,
625 resultout out nocopy varchar2 ) is
626 begin
627 --
628 -- RUN mode - normal process execution
629 --
630 if (funcmode = 'RUN') then
631 if (wf_engine.GetItemAttrText(itemtype,itemkey,'NOTE') is NULL) then
632 resultout := 'COMPLETE:F';
633 else
634 resultout := 'COMPLETE:T';
635 end if;
636 end if;
637 --
638 -- CANCEL mode
639 --
640 if (funcmode = 'CANCEL') then
641 --
642 resultout := 'COMPLETE:';
643 return;
644 --
645 end if;
646 --
647 -- TIMEOUT mode
648 --
649 if (funcmode = 'TIMEOUT') then
650 --
651 resultout := 'COMPLETE:';
652 return;
653 --
654 end if;
655 exception
656 when others then
657 wf_core.context('OKC_WF_K_APPROVE',
658 'NOTE_FILLED',
659 itemtype,
660 itemkey,
661 to_char(actid),
662 funcmode);
663 raise;
664 end note_filled;
665
666 --
667 -- Procedure Name : IS_K_TEMPLATE
668 -- Description : determines if K is a template
669 -- Business Rules :
670 -- Parameters :
671 -- Version : 1.0
672
673 procedure IS_K_TEMPLATE( itemtype in varchar2,
674 itemkey in varchar2,
675 actid in number,
676 funcmode in varchar2,
677 resultout out nocopy varchar2 ) is
678 --
679 L1 varchar2(1):='F';
680 --
681 -- Cursor to check if contract is a template
682 CURSOR c1 is
683 select 'T'
684 from okc_k_headers_v
685 where id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
686 and template_yn = 'Y';
687 --
688 BEGIN
689 --
690 -- RUN mode - normal process execution
691 --
692 if (funcmode = 'RUN') then
693 open c1;
694 fetch c1 into L1;
695 close c1;
696 resultout := 'COMPLETE:'||L1;
697 end if;
698 --
699 -- CANCEL mode
700 --
701 if (funcmode = 'CANCEL') then
702 --
703 resultout := 'COMPLETE:';
704 return;
705 --
706 end if;
707 --
708 -- TIMEOUT mode
709 --
710 if (funcmode = 'TIMEOUT') then
711 --
712 resultout := 'COMPLETE:';
713 return;
714 --
715 end if;
716 exception
717 when others then
718 wf_core.context('OKC_WF_K_APPROVE',
719 'IS_K_TEMPLATE',
720 itemtype,
721 itemkey,
722 to_char(actid),
723 funcmode);
724 raise;
725 end IS_K_TEMPLATE;
726
727 -- Start of comments
728 --
729 -- Procedure Name : Initialize
730 -- Description : Initialization of attributes that were not initialized by k_start API
731 -- Business Rules :
732 -- Parameters :
733 -- Version : 1.0
734 -- End of comments
735
736 procedure Initialize ( itemtype in varchar2,
737 itemkey in varchar2,
738 actid in number,
739 funcmode in varchar2,
740 resultout out nocopy varchar2 ) is
741 L_CONTRACT_ADMIN_USERNAME varchar2(240); --Bug:3018825 increased legth to 240
742 V_DUMMY varchar2(240);
743 L_SIGNATORY_USERNAME varchar2(240); --Bug:3018825 increased legth to 240
744 L_SIGNATORY_DISPLAY_NAME varchar2(240);
745 l_id number;
746 L_INITIATOR_NAME varchar2(240);
747 L_INITIATOR_DISPLAY_NAME varchar2(240);
748 L_K_SHORT_DESCRIPTION varchar2(4000);
749
750 cursor C_INITIATOR_DISPLAY_NAME(P_USER_ID in number) is
751 /*
752 select name,display_name
753 from wf_roles
754 where orig_system = 'FND_USR'
755 and orig_system_id=P_USER_ID
756 union all
757 select
758 USR.USER_NAME name,
759 PER.FULL_NAME display_name
760 from
761 PER_PEOPLE_F PER,
762 FND_USER USR
763 where trunc(SYSDATE) between PER.EFFECTIVE_START_DATE
764 and PER.EFFECTIVE_END_DATE
765 and PER.PERSON_ID = USR.EMPLOYEE_ID
766 and USR.USER_ID = P_USER_ID
767 and not exists (select '1'
768 from wf_roles
769 where orig_system = 'FND_USR'
770 and orig_system_id=P_USER_ID)
771 -- replaced to boost perf
772 */
773 select user_name name,user_name display_name
774 from fnd_user
775 where user_id=P_USER_ID
776 and employee_id is null
777 union all
778 select
779 USR.USER_NAME name, PER.FULL_NAME display_name
780 from
781 PER_PEOPLE_F PER,
782 FND_USER USR
783 where trunc(SYSDATE)
784 between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
785 and PER.PERSON_ID = USR.EMPLOYEE_ID
786 and USR.USER_ID = P_USER_ID
787 ;
788
789 cursor C_K_SHORT_DESCRIPTION(P_CONTRACT_ID in number) is
790 select SHORT_DESCRIPTION
791 from okc_k_headers_tl
792 where id = P_CONTRACT_ID
793 and language=userenv('LANG');
794 begin
795 mo_global.init('OKC');
796 --
797 -- RUN mode - normal process execution
798 --
799 if (funcmode = 'RUN') then
800 --
801 -- Initiator/Initial
802 --
803 l_id := wf_engine.GetItemAttrNumber(
804 itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'USER_ID');
807 open C_INITIATOR_DISPLAY_NAME(l_id);
808 fetch C_INITIATOR_DISPLAY_NAME into L_INITIATOR_NAME,L_INITIATOR_DISPLAY_NAME;
809 close C_INITIATOR_DISPLAY_NAME;
810 wf_engine.SetItemAttrText (
811 itemtype => itemtype,
812 itemkey => itemkey,
813 aname => 'INITIATOR_NAME',
814 avalue => L_INITIATOR_NAME);
815 wf_engine.SetItemAttrText (
816 itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'INITIATOR_DISPLAY_NAME',
819 avalue => L_INITIATOR_DISPLAY_NAME);
820 --
821 -- Administrator U/name
822 --
823 select_next(itemtype => itemtype,
824 itemkey => itemkey,
825 p_role_type => 'ADMINISTRATOR',
826 x_role => L_CONTRACT_ADMIN_USERNAME,
827 x_name => V_DUMMY);
828 wf_engine.SetItemAttrText (
829 itemtype => itemtype,
830 itemkey => itemkey,
831 aname => 'CONTRACT_ADMIN_USERNAME',
832 avalue => L_CONTRACT_ADMIN_USERNAME);
833 --
834 -- Signatory U/D/name
835 --
836 select_next(itemtype => itemtype,
837 itemkey => itemkey,
838 p_role_type => 'SIGNATORY',
839 x_role => L_SIGNATORY_USERNAME,
840 x_name => L_SIGNATORY_DISPLAY_NAME);
841 wf_engine.SetItemAttrText (
842 itemtype => itemtype,
843 itemkey => itemkey,
844 aname => 'SIGNATORY_USERNAME',
845 avalue => L_SIGNATORY_USERNAME);
846
847 wf_engine.SetItemAttrText (
848 itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'SIGNATORY_DISPLAY_NAME',
851 avalue => L_SIGNATORY_DISPLAY_NAME);
852 --
853 l_id := wf_engine.GetItemAttrNumber(
854 itemtype => itemtype,
855 itemkey => itemkey,
856 aname => 'CONTRACT_ID');
857 open C_K_SHORT_DESCRIPTION(l_id);
858 fetch C_K_SHORT_DESCRIPTION into L_K_SHORT_DESCRIPTION;
859 close C_K_SHORT_DESCRIPTION;
860 wf_engine.SetItemAttrText (
861 itemtype => itemtype,
862 itemkey => itemkey,
863 aname => 'K_SHORT_DESCRIPTION',
864 avalue => L_K_SHORT_DESCRIPTION);
865 --
866 update_invalid_approver(itemtype => itemtype,
867 itemkey => itemkey,
868 actid => actid,
869 funcmode => funcmode,
870 resultout => resultout);
871
872 resultout := 'COMPLETE:';
873 return;
874 --
875 end if;
876 --
877 -- CANCEL mode
878 --
879 if (funcmode = 'CANCEL') then
880 --
881 resultout := 'COMPLETE:';
882 return;
883 --
884 end if;
885 --
886 -- TIMEOUT mode
887 --
888 if (funcmode = 'TIMEOUT') then
889 --
890 resultout := 'COMPLETE:';
891 return;
892 --
893 end if;
894 exception
895 when others then
896 wf_core.context('OKC_WF_K_APPROVE',
897 'INITIALIZE',
898 itemtype,
899 itemkey,
900 to_char(actid),
901 funcmode);
902 raise;
903 end Initialize;
904
905 -- Start of comments
906 --
907 -- Procedure Name : Select_Approver
908 -- Description : Customize using your approval chain
909 -- Business Rules :
910 -- Parameters :
911 -- Version : 1.0
912 -- End of comments
913
914 procedure Select_Approver( itemtype in varchar2,
915 itemkey in varchar2,
916 actid in number,
917 funcmode in varchar2,
918 resultout out nocopy varchar2 ) is
919 L_INITIATOR_DISPLAY_NAME varchar2(240);
920 L_NEXT_PERFORMER_USERNAME varchar2(240);
921 L_NEXT_PERFORMER_USERNAME_OUT varchar2(240);
922 L_N_PERFORMER_DISPLAY_NAME varchar2(240);
923 begin
924 mo_global.init('OKC');
925 --
926 -- RUN mode - normal process execution
927 --
928 if (funcmode = 'RUN') then
929 L_NEXT_PERFORMER_USERNAME := wf_engine.GetItemAttrText(
930 itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'NEXT_PERFORMER_USERNAME');
933 if (L_NEXT_PERFORMER_USERNAME is NULL)
934 then -- just start
935 L_INITIATOR_DISPLAY_NAME := wf_engine.GetItemAttrText(
936 itemtype => itemtype,
937 itemkey => itemkey,
938 aname => 'INITIATOR_DISPLAY_NAME');
939 select_next(itemtype => itemtype,
940 itemkey => itemkey,
941 p_role_type => 'APPROVER',
942 p_current => NULL,
943 x_role => L_NEXT_PERFORMER_USERNAME,
944 x_name => L_N_PERFORMER_DISPLAY_NAME);
945 -- just for common situation if no approvers at all
946 -- then Contract is considered as approved by initiator
947 if (L_NEXT_PERFORMER_USERNAME is NULL) then
948 wf_engine.SetItemAttrText (
949 itemtype => itemtype,
950 itemkey => itemkey,
951 aname => 'N_PERFORMER_DISPLAY_NAME',
952 avalue => L_INITIATOR_DISPLAY_NAME);
953 wf_engine.SetItemAttrText (
954 itemtype => itemtype,
955 itemkey => itemkey,
956 aname => 'FINAL_APPROVER_UNAME',
957 avalue => NULL);
958 resultout := 'COMPLETE:F';
959 else
960 wf_engine.SetItemAttrText (
961 itemtype => itemtype,
962 itemkey => itemkey,
963 aname => 'P_PERFORMER_DISPLAY_NAME',
964 avalue => L_INITIATOR_DISPLAY_NAME);
965 wf_engine.SetItemAttrText (
966 itemtype => itemtype,
967 itemkey => itemkey,
968 aname => 'NEXT_PERFORMER_USERNAME',
969 avalue => L_NEXT_PERFORMER_USERNAME);
970 wf_engine.SetItemAttrText(itemtype => itemtype,
971 itemkey => itemkey,
972 aname => 'N_PERFORMER_DISPLAY_NAME',
973 avalue => L_N_PERFORMER_DISPLAY_NAME);
974 resultout := 'COMPLETE:T';
975 end if;
976 return;
977 else
978 wf_engine.SetItemAttrText
979 (
980 itemtype => itemtype,
981 itemkey => itemkey,
982 aname => 'P_PERFORMER_DISPLAY_NAME',
983 avalue => wf_engine.GetItemAttrText(
984 itemtype => itemtype,
985 itemkey => itemkey,
986 aname => 'N_PERFORMER_DISPLAY_NAME')
987 );
988 select_next(itemtype => itemtype,
989 itemkey => itemkey,
990 p_role_type => 'APPROVER',
991 p_current => L_NEXT_PERFORMER_USERNAME,
992 --x_role => L_NEXT_PERFORMER_USERNAME,
993 x_role => L_NEXT_PERFORMER_USERNAME_OUT,
994 x_name => L_N_PERFORMER_DISPLAY_NAME);
995
996 if (L_NEXT_PERFORMER_USERNAME_OUT is NULL) then
997 wf_engine.SetItemAttrText (
998 itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'FINAL_APPROVER_UNAME',
1001 avalue => wf_engine.GetItemAttrText(
1002 itemtype => itemtype,
1003 itemkey => itemkey,
1004 aname => 'NEXT_PERFORMER_USERNAME'));
1005
1006 wf_engine.SetItemAttrText (
1007 itemtype => itemtype,
1008 itemkey => itemkey,
1009 aname => 'NEXT_PERFORMER_USERNAME',
1010 avalue => NULL);
1011 resultout := 'COMPLETE:F';
1012
1013 else
1014
1015 wf_engine.SetItemAttrText (
1016 itemtype => itemtype,
1017 itemkey => itemkey,
1018 aname => 'NEXT_PERFORMER_USERNAME',
1019 avalue => L_NEXT_PERFORMER_USERNAME_OUT);
1020
1021 wf_engine.SetItemAttrText(
1022 itemtype => itemtype,
1023 itemkey => itemkey,
1024 aname => 'N_PERFORMER_DISPLAY_NAME',
1025 avalue => L_N_PERFORMER_DISPLAY_NAME);
1026
1027 resultout := 'COMPLETE:T';
1028 end if;
1029 return;
1030 --
1031 end if;
1032 end if;
1033 --
1034 -- CANCEL mode
1035 --
1036 if (funcmode = 'CANCEL') then
1037 --
1038 resultout := 'COMPLETE:';
1039 return;
1040 --
1041 end if;
1042 --
1043 -- TIMEOUT mode
1044 --
1045 if (funcmode = 'TIMEOUT') then
1046 --
1047 resultout := 'COMPLETE:';
1048 return;
1049 --
1050 end if;
1051 exception
1052 when others then
1053 wf_core.context('OKC_WF_K_APPROVE',
1054 'SELECT_APPROVER',
1055 itemtype,
1056 itemkey,
1057 to_char(actid),
1058 funcmode);
1059 raise;
1060 end Select_Approver;
1061
1062 -- Start of comments
1063 --
1064 -- Procedure Name : Select_Informed
1065 -- Description : Customize using your To be Informed chain
1066 -- Business Rules :
1067 -- Parameters :
1068 -- Version : 1.0
1069 -- End of comments
1070
1071 procedure Select_Informed( itemtype in varchar2,
1072 itemkey in varchar2,
1073 actid in number,
1074 funcmode in varchar2,
1075 resultout out nocopy varchar2) is
1076 L_NEXT_INFORMED_USERNAME varchar2(100);
1077 L_NEXT_INFORMED_USERNAME_OUT varchar2(100);
1078 v_dummy varchar2(240);
1079 begin
1080
1081 --
1082 -- RUN mode - normal process execution
1083 --
1084 if (funcmode = 'RUN') then
1085
1086 L_NEXT_INFORMED_USERNAME := wf_engine.GetItemAttrText(
1087 itemtype => itemtype,
1088 itemkey => itemkey,
1089 aname => 'NEXT_INFORMED_USERNAME');
1090
1091 select_next(itemtype => itemtype,
1092 itemkey => itemkey,
1093 p_role_type => 'INFORMED',
1094 p_current => L_NEXT_INFORMED_USERNAME,
1095 x_role => L_NEXT_INFORMED_USERNAME_OUT,
1096 x_name => V_DUMMY);
1097
1098 wf_engine.SetItemAttrText (
1099 itemtype => itemtype,
1100 itemkey => itemkey,
1101 aname => 'NEXT_INFORMED_USERNAME',
1102 avalue => L_NEXT_INFORMED_USERNAME_OUT);
1103
1104 if (L_NEXT_INFORMED_USERNAME_OUT is NULL) then
1105 resultout := 'COMPLETE:F';
1106 else
1107 resultout := 'COMPLETE:T';
1108 end if;
1109 return;
1110 end if;
1111
1112 --
1113 -- CANCEL mode
1114 --
1115 if (funcmode = 'CANCEL') then
1116 --
1117 resultout := 'COMPLETE:';
1118 return;
1119 --
1120 end if;
1121 --
1122 -- TIMEOUT mode
1123 --
1124 if (funcmode = 'TIMEOUT') then
1125 --
1126 resultout := 'COMPLETE:';
1127 return;
1128 --
1129 end if;
1130 exception
1131 when others then
1132 wf_core.context('OKC_WF_K_APPROVE',
1133 'SELECT_INFORMED',
1134 itemtype,
1135 itemkey,
1136 to_char(actid),
1137 funcmode);
1138 raise;
1139 end Select_Informed;
1140
1141 procedure Select_Informed_A( itemtype in varchar2,
1142 itemkey in varchar2,
1143 actid in number,
1144 funcmode in varchar2,
1145 resultout out nocopy varchar2) is
1146 begin
1147 Select_Informed( itemtype,
1148 itemkey ,
1149 actid ,
1150 funcmode,
1151 resultout);
1152 end;
1153
1154 procedure Select_Informed_AR( itemtype in varchar2,
1155 itemkey in varchar2,
1156 actid in number,
1157 funcmode in varchar2,
1158 resultout out nocopy varchar2) is
1159
1160 l_contract_id NUMBER;
1161 l_scs_code VARCHAR2(30);
1162 l_wf_item_key VARCHAR2(240);
1163 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1164 l_msg_count NUMBER;
1165 l_msg_data VARCHAR2(2000);
1166
1167 CURSOR csr_k_details (p_chr_id NUMBER) IS
1168 select okch.scs_code, oksh.wf_item_key
1169 from okc_k_headers_all_b okch, oks_k_headers_b oksh
1170 where okch.ID = p_chr_id
1171 and okch.id = oksh.chr_id;
1172 begin
1173
1174 Select_Informed( itemtype,
1175 itemkey ,
1176 actid ,
1177 funcmode,
1178 resultout);
1179 IF resultout = 'COMPLETE:F' THEN
1180
1181 l_contract_id := wf_engine.GetItemAttrNumber(
1182 itemtype => itemtype,
1183 itemkey => itemkey,
1184 aname => 'CONTRACT_ID');
1185
1186 OPEN csr_k_details(l_contract_id);
1187 FETCH csr_k_details INTO l_scs_code, l_wf_item_key;
1188 CLOSE csr_k_details;
1189
1190 -- create new instance of OKS Contract Process wf only if it does not exist
1191 -- and the contract is either Service, warranty contract or subscrption agreement
1192 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') AND l_wf_item_key IS NULL THEN
1193
1194 empty_mess(itemtype, itemkey );
1195
1196 OKC_CONTRACT_APPROVAL_PVT.continue_k_process
1197 (
1198 p_api_version => 1.0,
1199 p_init_msg_list => 'T',
1200 x_return_status => l_return_status,
1201 x_msg_count => l_msg_count,
1202 x_msg_data => l_msg_data,
1203 p_contract_id => l_contract_id,
1204 p_wf_item_key => NULL,
1205 p_called_from => 'REJECTED'
1206 );
1207 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1208 load_mess(itemtype, itemkey );
1209 END IF;
1210 END IF;
1211 END IF;
1212 exception
1213 when others then
1214 wf_core.context('OKC_WF_K_APPROVE',
1215 'SELECT_INFORMED_AR',
1216 itemtype,
1217 itemkey,
1218 to_char(actid),
1219 funcmode);
1220 raise;
1221
1222 end;
1223
1224 procedure Select_Informed_S( itemtype in varchar2,
1225 itemkey in varchar2,
1226 actid in number,
1227 funcmode in varchar2,
1228 resultout out nocopy varchar2) is
1229
1230 l_contract_id NUMBER;
1231 l_scs_code VARCHAR2(30);
1232 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1233 l_msg_count NUMBER;
1234 l_msg_data VARCHAR2(2000);
1235
1236 CURSOR csr_class_code(p_chr_id NUMBER) IS
1237 select scs_code
1238 from okc_k_headers_all_b
1239 where ID = p_chr_id;
1240
1241 begin
1242
1243 Select_Informed( itemtype,
1244 itemkey ,
1245 actid ,
1246 funcmode,
1247 resultout);
1248
1249 IF resultout = 'COMPLETE:F' THEN
1250
1251 l_contract_id := wf_engine.GetItemAttrNumber(
1252 itemtype => itemtype,
1253 itemkey => itemkey,
1254 aname => 'CONTRACT_ID');
1255
1256
1257 OPEN csr_class_code(l_contract_id);
1258 FETCH csr_class_code INTO l_scs_code;
1259 CLOSE csr_class_code;
1260
1261 -- Since Approval workflow is about to be completed as it has been
1262 -- approved and signed, we should change the negotiation status to Complete
1263 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') THEN
1264 empty_mess(itemtype, itemkey );
1265
1266 OKC_CONTRACT_APPROVAL_PVT.continue_k_process
1267 (
1268 p_api_version => 1.0,
1269 p_init_msg_list => 'T',
1270 x_return_status => l_return_status,
1271 x_msg_count => l_msg_count,
1272 x_msg_data => l_msg_data,
1273 p_contract_id => l_contract_id,
1274 p_wf_item_key => NULL,
1275 p_called_from => 'APPROVE'
1276 );
1277 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1278 load_mess(itemtype, itemkey );
1279 END IF;
1280 END IF;
1281 END IF;
1282 exception
1283 when others then
1284 wf_core.context('OKC_WF_K_APPROVE',
1285 'SELECT_INFORMED_S',
1286 itemtype,
1287 itemkey,
1288 to_char(actid),
1289 funcmode);
1290 raise;
1291 end;
1292
1293 procedure Select_Informed_SR( itemtype in varchar2,
1294 itemkey in varchar2,
1295 actid in number,
1296 funcmode in varchar2,
1297 resultout out nocopy varchar2) is
1298
1299 l_contract_id NUMBER;
1300 l_scs_code VARCHAR2(30);
1301 l_wf_item_key VARCHAR2(240);
1302 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1303 l_msg_count NUMBER;
1304 l_msg_data VARCHAR2(2000);
1305
1306 CURSOR csr_k_details (p_chr_id NUMBER) IS
1307 select okch.scs_code, oksh.wf_item_key
1308 from okc_k_headers_all_b okch, oks_k_headers_b oksh
1309 where okch.ID = p_chr_id
1310 and okch.id = oksh.chr_id;
1311 begin
1312
1313 Select_Informed( itemtype,
1314 itemkey ,
1315 actid ,
1316 funcmode,
1317 resultout);
1318 IF resultout = 'COMPLETE:F' THEN
1319
1320 l_contract_id := wf_engine.GetItemAttrNumber(
1321 itemtype => itemtype,
1322 itemkey => itemkey,
1323 aname => 'CONTRACT_ID');
1324
1325 OPEN csr_k_details(l_contract_id);
1326 FETCH csr_k_details INTO l_scs_code, l_wf_item_key;
1327 CLOSE csr_k_details;
1328
1329 -- create new instance of OKS Contract Process wf only if it does not exist
1330 -- and the contract is either Service, warranty contract or subscrption agreement
1331 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') AND l_wf_item_key IS NULL THEN
1332
1333 empty_mess(itemtype, itemkey );
1334
1335 OKC_CONTRACT_APPROVAL_PVT.continue_k_process
1336 (
1337 p_api_version => 1.0,
1338 p_init_msg_list => 'T',
1339 x_return_status => l_return_status,
1340 x_msg_count => l_msg_count,
1341 x_msg_data => l_msg_data,
1342 p_contract_id => l_contract_id,
1343 p_wf_item_key => NULL,
1344 p_called_from => 'REJECTED'
1345 );
1346 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1347 load_mess(itemtype, itemkey );
1348 END IF;
1349 END IF;
1350 END IF;
1351 exception
1352 when others then
1353 wf_core.context('OKC_WF_K_APPROVE',
1354 'SELECT_INFORMED_SR',
1355 itemtype,
1356 itemkey,
1357 to_char(actid),
1358 funcmode);
1359 raise;
1360 end;
1361
1362 -- Start of comments
1363 --
1364 -- Procedure Name : Record_Approved
1365 -- Description : Does not need customization
1366 -- Business Rules :
1367 -- Parameters :
1368 -- Version : 1.0
1369 -- End of comments
1370
1371 procedure Record_Approved( itemtype in varchar2,
1372 itemkey in varchar2,
1373 actid in number,
1374 funcmode in varchar2,
1375 resultout out nocopy varchar2 ) is
1376 L_CONTRACT_ID number;
1377 x_return_status varchar2(1);
1378 begin
1379
1380 mo_global.init('OKC');
1381 --
1382 -- RUN mode - normal process execution
1383 --
1384 if (funcmode = 'RUN') then
1385 --
1386 empty_mess( itemtype,
1387 itemkey );
1388 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1389 itemtype => itemtype,
1390 itemkey => itemkey,
1391 aname => 'CONTRACT_ID');
1392 OKC_CONTRACT_APPROVAL_PUB.k_approved(
1393 p_contract_id => L_CONTRACT_ID,
1394 x_return_status => x_return_status);
1395 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1396 then
1397 --
1398 wf_engine.SetItemAttrDate (
1399 itemtype => itemtype,
1400 itemkey => itemkey,
1401 aname => 'DATE_SIGNED',
1402 avalue => sysdate);
1403 resultout := 'COMPLETE:T';
1404 return;
1405 --
1406 else
1407 --
1408 load_mess( itemtype,
1409 itemkey );
1410 resultout := 'COMPLETE:F';
1411 return;
1412 --
1413 end if;
1414 end if;
1415 --
1416 -- CANCEL mode
1417 --
1418 if (funcmode = 'CANCEL') then
1419 --
1420 resultout := 'COMPLETE:';
1421 return;
1422 --
1423 end if;
1424 --
1425 -- TIMEOUT mode
1426 --
1427 if (funcmode = 'TIMEOUT') then
1428 --
1429 resultout := 'COMPLETE:';
1430 return;
1431 --
1432 end if;
1433 exception
1434 when others then
1435 wf_core.context('OKC_WF_K_APPROVE',
1436 'RECORD_APPROVED',
1437 itemtype,
1438 itemkey,
1439 to_char(actid),
1440 funcmode);
1441 raise;
1442 end Record_Approved;
1443
1444 -- Start of comments
1445 --
1446 -- Procedure Name : Erase_Approved
1447 -- Description : Does not need customization
1448 -- Business Rules :
1449 -- Parameters :
1450 -- Version : 1.0
1451 -- End of comments
1452
1453 procedure Erase_Approved( itemtype in varchar2,
1454 itemkey in varchar2,
1455 actid in number,
1456 funcmode in varchar2,
1457 resultout out nocopy varchar2 ) is
1458 L_CONTRACT_ID number;
1459 x_return_status varchar2(1);
1460 begin
1461 mo_global.init('OKC');
1462 --
1463 -- RUN mode - normal process execution
1464 --
1465 if (funcmode = 'RUN') then
1466 --
1467 empty_mess( itemtype,
1468 itemkey );
1469 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1470 itemtype => itemtype,
1471 itemkey => itemkey,
1472 aname => 'CONTRACT_ID');
1473 OKC_CONTRACT_APPROVAL_PUB.k_erase_approved(
1474 p_contract_id => L_CONTRACT_ID,
1475 x_return_status => x_return_status);
1476 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1477 then
1478 --
1479 resultout := 'COMPLETE:T';
1480 return;
1481 --
1482 else
1483 --
1484 load_mess( itemtype,
1485 itemkey );
1486 resultout := 'COMPLETE:F';
1487 return;
1488 --
1489 end if;
1490 end if;
1491 --
1492 -- CANCEL mode
1493 --
1494 if (funcmode = 'CANCEL') then
1495 --
1496 resultout := 'COMPLETE:';
1497 return;
1498 --
1499 end if;
1500 --
1501 -- TIMEOUT mode
1502 --
1503 if (funcmode = 'TIMEOUT') then
1504 --
1505 resultout := 'COMPLETE:';
1506 return;
1507 --
1508 end if;
1509 exception
1510 when others then
1511 wf_core.context('OKC_WF_K_APPROVE',
1512 'ERASE_APPROVED',
1513 itemtype,
1514 itemkey,
1515 to_char(actid),
1516 funcmode);
1517 raise;
1518 end Erase_Approved;
1519
1520 -- Start of comments
1521 --
1522 -- Procedure Name : Record_Signed
1523 -- Description : Could be customized to widen sign procedure
1524 -- Business Rules :
1525 -- Parameters :
1526 -- Version : 1.0
1527 -- End of comments
1528
1529 procedure Record_Signed(itemtype in varchar2,
1530 itemkey in varchar2,
1531 actid in number,
1532 funcmode in varchar2,
1533 resultout out nocopy varchar2 ) is
1534 L_DATE_SIGNED Date;
1535 L_CONTRACT_ID number;
1536 x_return_status varchar2(1);
1537 begin
1538 mo_global.init('OKC');
1539 --
1540 -- RUN mode - normal process execution
1541 --
1542 if (funcmode = 'RUN') then
1543 --
1544 empty_mess( itemtype,
1545 itemkey );
1546 L_DATE_SIGNED := wf_engine.GetItemAttrDate (
1547 itemtype => itemtype,
1548 itemkey => itemkey,
1549 aname => 'DATE_SIGNED');
1550 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1551 itemtype => itemtype,
1552 itemkey => itemkey,
1553 aname => 'CONTRACT_ID');
1554 OKC_CONTRACT_APPROVAL_PUB.k_signed(
1555 p_contract_id => L_CONTRACT_ID,
1556 p_date_signed => NVL(L_DATE_SIGNED,sysdate),
1557 x_return_status => x_return_status
1558 );
1559 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1560 then
1561 --
1562 resultout := 'COMPLETE:T';
1563 return;
1564 --
1565 else
1566 --
1567 load_mess( itemtype,
1568 itemkey );
1569 resultout := 'COMPLETE:F';
1570 return;
1571 --
1572 end if;
1573 end if;
1574 --
1575 -- CANCEL mode
1576 --
1577 if (funcmode = 'CANCEL') then
1578 --
1579 resultout := 'COMPLETE:';
1580 return;
1581 --
1582 end if;
1583 --
1584 -- TIMEOUT mode
1585 --
1586 if (funcmode = 'TIMEOUT') then
1587 --
1588 resultout := 'COMPLETE:';
1589 return;
1590 --
1591 end if;
1592 exception
1593 when others then
1594 wf_core.context('OKC_WF_K_APPROVE',
1595 'RECORD_SIGNED',
1596 itemtype,
1597 itemkey,
1598 to_char(actid),
1599 funcmode);
1600 raise;
1601 end Record_Signed;
1602
1603 -- Start of comments
1604 --
1605 -- Procedure Name : was_approver
1606 -- Description : note mandatory if reject
1607 -- Business Rules :
1608 -- Parameters :
1609 -- Version : 1.0
1610 -- End of comments
1611
1612 procedure was_approver( itemtype in varchar2,
1613 itemkey in varchar2,
1614 actid in number,
1615 funcmode in varchar2,
1616 resultout out nocopy varchar2 ) is
1617 begin
1618 --
1619 -- RUN mode - normal process execution
1620 --
1621 if (funcmode = 'RUN') then
1622 if (wf_engine.GetItemAttrText(itemtype,itemkey,'FINAL_APPROVER_UNAME') is NULL) then
1623 resultout := 'COMPLETE:F';
1624 else
1625 resultout := 'COMPLETE:T';
1626 end if;
1627 end if;
1628 --
1629 -- CANCEL mode
1630 --
1631 if (funcmode = 'CANCEL') then
1632 --
1633 resultout := 'COMPLETE:';
1634 return;
1635 --
1636 end if;
1637 --
1638 -- TIMEOUT mode
1639 --
1640 if (funcmode = 'TIMEOUT') then
1641 --
1642 resultout := 'COMPLETE:';
1643 return;
1644 --
1645 end if;
1646 exception
1647 when others then
1648 wf_core.context('OKC_WF_K_APPROVE',
1649 'WAS_APPROVER',
1650 itemtype,
1651 itemkey,
1652 to_char(actid),
1653 funcmode);
1654 raise;
1655 end was_approver;
1656
1657 -- Start of comments
1658 --
1659 -- Procedure Name : NOTIFY_SALES_REP_W
1660 -- Description : Procedure to call private API
1661 -- OKC_OC_INT_QTK_PVT.NOTIFY_SALES_REP
1662 -- Business Rules : Private API
1663 -- IN Parameters : itemtype,itemkey ,actid,funcmode
1664 -- OUT Parameters : resultout
1665 -- Version : 1.0
1666 --
1667 -- End of comments
1668 Procedure NOTIFY_SALES_REP_W (itemtype in varchar2,
1669 itemkey in varchar2,
1670 actid in number,
1671 funcmode in varchar2,
1672 resultout out nocopy varchar2 )
1673 IS
1674 --Local Variables
1675 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1676 l_contract_id number :=NULL;
1677 l_msg_count number :=NULL;
1678 l_msg_data varchar2(1000) :=NULL;
1679
1680 --Global Variables
1681 G_API_VERSION NUMBER :=1.0 ;
1682
1683 BEGIN
1684 mo_global.init('OKC');
1685 --
1686 -- RUN mode - normal process execution
1687 --
1688 If (funcmode = 'RUN') then
1689 --
1690
1691 l_contract_id := wf_engine.GetItemAttrNumber(
1692 itemtype => itemtype,
1693 itemkey => itemkey,
1694 aname => 'CONTRACT_ID');
1695 empty_mess( itemtype,
1696 itemkey );
1697 /*
1698 calling notification api to notify quoation and IStore
1699 */
1700 OKC_OC_INT_QTK_PVT.NOTIFY_SALES_REP
1701 (p_api_version => g_api_version
1702 ,p_contract_id => l_contract_id
1703 ,x_msg_count => l_msg_count
1704 ,x_msg_data => l_msg_data
1705 ,x_return_status => l_return_status ) ;
1706
1707 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) then
1708 --
1709 resultout := 'COMPLETE:T';
1710 return;
1711 --
1712 else
1713 --
1714 load_mess( itemtype,
1715 itemkey );
1716 resultout := 'COMPLETE:F';
1717
1718 return;
1719 --
1720 end if;
1721 End If;
1722 EXCEPTION
1723 when others then
1724 wf_core.context(pkg_name => 'OKC_WF_K_APPROVE',
1725 proc_name => 'NOTIFY_SALES_REP_W',
1726 arg1 => itemtype,
1727 arg2 => itemkey,
1728 arg3 => to_char(actid),
1729 arg4 => funcmode);
1730
1731 Raise;
1732 end NOTIFY_SALES_REP_W;
1733
1734 -- Procedure Name : Make_Active
1735 -- Description :
1736 -- Business Rules :
1737 -- Parameters :
1738 -- Version : 1.0
1739 -- End of comments
1740
1741 procedure Make_Active(itemtype in varchar2,
1742 itemkey in varchar2,
1743 actid in number,
1744 funcmode in varchar2,
1745 resultout out nocopy varchar2 ) is
1746 --
1747 L_CONTRACT_ID number;
1748 x_return_status varchar2(1);
1749 --
1750 begin
1751 mo_global.init('OKC');
1752 --
1753 -- RUN mode - normal process execution
1754 --
1755 if (funcmode = 'RUN') then
1756 --
1757 empty_mess( itemtype,
1758 itemkey );
1759 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1760 itemtype => itemtype,
1761 itemkey => itemkey,
1762 aname => 'CONTRACT_ID');
1763 OKC_CONTRACT_APPROVAL_PUB.activate_template(
1764 p_contract_id => L_CONTRACT_ID,
1765 x_return_status => x_return_status);
1766 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1767 then
1768 --
1769 resultout := 'COMPLETE:T';
1770 return;
1771 --
1772 else
1773 --
1774 load_mess( itemtype,
1775 itemkey );
1776 resultout := 'COMPLETE:F';
1777 return;
1778 --
1779 end if;
1780 end if;
1781 --
1782 -- CANCEL mode
1783 --
1784 if (funcmode = 'CANCEL') then
1785 --
1786 resultout := 'COMPLETE:';
1787 return;
1788 --
1789 end if;
1790 --
1791 -- TIMEOUT mode
1792 --
1793 if (funcmode = 'TIMEOUT') then
1794 --
1795 resultout := 'COMPLETE:';
1796 return;
1797 --
1798 end if;
1799 exception
1800 when others then
1801 wf_core.context('OKC_WF_K_APPROVE',
1802 'MAKE_ACTIVE',
1803 itemtype,
1804 itemkey,
1805 to_char(actid),
1806 funcmode);
1807 raise;
1808 end Make_Active;
1809
1810 -- Procedure Name : updt_quote_from_k
1811 -- Description :
1812 -- Business Rules :
1813 -- Parameters :
1814 -- Version : 1.0
1815 -- End of comments
1816
1817 procedure updt_quote_from_k(itemtype in varchar2,
1818 itemkey in varchar2,
1819 actid in number,
1820 funcmode in varchar2,
1821 resultout out nocopy varchar2 ) is
1822 --
1823 l_contract_id number;
1824 x_return_status varchar2(30);
1825 x_msg_count number;
1826 x_msg_data varchar2(4000);
1827 l_api_version NUMBER :=1.0 ;
1828 l_session_id number;
1829 CURSOR csr_session_id IS
1830 SELECT userenv('sessionid')
1831 FROM dual;
1832 --
1833 begin
1834 mo_global.init('OKC');
1835 --
1836 -- RUN mode - normal process execution
1837 --
1838 if (funcmode = 'RUN') then
1839 --
1840 empty_mess( itemtype,
1841 itemkey );
1842 --
1843 OPEN csr_session_id;
1844 FETCH csr_session_id INTO l_session_id;
1845 CLOSE csr_session_id;
1846
1847 wf_engine.SetItemAttrNumber (
1848 itemtype => itemtype,
1849 itemkey=> itemkey,
1850 aname => 'QUOTE_SESSION_ID',
1851 avalue=> l_session_id);
1852 --
1853 l_contract_id := wf_engine.GetItemAttrNumber(
1854 itemtype => itemtype,
1855 itemkey => itemkey,
1856 aname => 'CONTRACT_ID');
1857
1858 OKC_OC_INT_PUB.update_quote_from_k(
1859 p_api_version => l_api_version
1860 ,p_commit => OKC_API.G_TRUE
1861 ,p_quote_id => NULL
1862 ,p_contract_id => l_contract_id
1863 ,p_trace_mode => NULL
1864 ,x_return_status => x_return_status
1865 ,x_msg_count => x_msg_count
1866 ,x_msg_data => x_msg_data
1867 );
1868
1869 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1870 then
1871 --
1872 resultout := 'COMPLETE:T';
1873 return;
1874 --
1875 else
1876 --
1877 load_mess( itemtype,
1878 itemkey );
1879 resultout := 'COMPLETE:F';
1880 return;
1881 --
1882 end if;
1883
1884 end if; -- run mode
1885 --
1886 -- CANCEL mode
1887 --
1888 if (funcmode = 'CANCEL') then
1889 --
1890 resultout := 'COMPLETE:';
1891 return;
1892 --
1893 end if;
1894 --
1895 -- TIMEOUT mode
1896 --
1897 if (funcmode = 'TIMEOUT') then
1898 --
1899 resultout := 'COMPLETE:';
1900 return;
1901 --
1902 end if;
1903 exception
1904 when others then
1905 wf_core.context('OKC_WF_K_APPROVE',
1906 'updt_quote_from_k',
1907 itemtype,
1908 itemkey,
1909 to_char(actid),
1910 funcmode);
1911 raise;
1912 end updt_quote_from_k;
1913
1914
1915 -- Start of comments
1916 -- Procedure Name : invalid_approver
1917 -- Description : Procedure to update okc_k_process in case of invalid approver in profile option.
1918 -- Business Rules :
1919 -- Parameters :
1920 -- Version : 1.0
1921 -- End of comments
1922
1923
1924 PROCEDURE invalid_approver(itemtype IN VARCHAR2,
1925 itemkey IN VARCHAR2,
1926 actid IN NUMBER,
1927 funcmode IN VARCHAR2,
1928 resultout OUT NOCOPY VARCHAR2 )
1929 IS
1930 CURSOR csr_process_id(p_contract_id IN NUMBER)
1931 IS
1932 SELECT id
1933 FROM okc_k_processes
1934 WHERE chr_id = p_contract_id
1935 /*Bug 3255018 AND pdf_id = (SELECT id */
1936 AND pdf_id in (SELECT id
1937 FROM OKC_PROCESS_DEFS_V
1938 WHERE usage = 'APPROVE'
1939 AND PDF_TYPE = 'WPS'
1940 AND WF_NAME = 'OKCAUKAP');
1941
1942 l_contract_id NUMBER;
1943 l_return_status VARCHAR2(30);
1944 l_msg_count NUMBER;
1945 l_msg_data VARCHAR2(4000);
1946 l_api_version NUMBER :=1.0 ;
1947 l_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
1948 x_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
1949 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE;
1950 l_process_id NUMBER;
1951 BEGIN
1952 mo_global.init('OKC');
1953 --
1954 -- RUN mode - normal process execution
1955 --
1956 IF (funcmode = 'RUN') THEN
1957 empty_mess( itemtype, itemkey );
1958
1959 l_contract_id := wf_engine.GetItemAttrNumber(
1960 itemtype => itemtype,
1961 itemkey => itemkey,
1962 aname => 'CONTRACT_ID');
1963
1964 OPEN csr_process_id(l_contract_id);
1965 FETCH csr_process_id INTO l_process_id;
1966 CLOSE csr_process_id;
1967
1968 l_cpsv_rec.id := l_process_id;
1969 l_cpsv_rec.in_process_yn := 'E';
1970
1971 Okc_Contract_Pub.update_contract_process(
1972 p_api_version => l_api_version,
1973 p_init_msg_list => l_init_msg_list,
1974 x_return_status => l_return_status,
1975 x_msg_count => l_msg_count,
1976 x_msg_data => l_msg_data,
1977 p_cpsv_rec => l_cpsv_rec,
1978 x_cpsv_rec => x_cpsv_rec);
1979
1980 IF (l_return_status = Okc_Api.G_RET_STS_SUCCESS) THEN
1981 resultout := 'COMPLETE:T';
1982 RETURN;
1983 ELSE
1984 load_mess( itemtype, itemkey );
1985 resultout := 'COMPLETE:F';
1986 RETURN;
1987 END IF;
1988
1989 END IF; -- run mode
1990
1991 --
1992 -- CANCEL mode
1993 --
1994 IF (funcmode = 'CANCEL') THEN
1995 --
1996 resultout := 'COMPLETE:';
1997 RETURN;
1998 --
1999 END IF;
2000 --
2001 -- TIMEOUT mode
2002 --
2003 IF (funcmode = 'TIMEOUT') THEN
2004 --
2005 resultout := 'COMPLETE:';
2006 RETURN;
2007 --
2008 END IF;
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 wf_core.context('OKC_WF_K_APPROVE',
2012 'invalid_approver',
2013 itemtype,
2014 itemkey,
2015 TO_CHAR(actid),
2016 funcmode);
2017 RAISE;
2018 END invalid_approver;
2019
2020
2021 -- Start of comments
2022 --
2023 -- Procedure Name : update_invalid_approver
2024 -- Description : update_invalid_approver error code in IN_PROCESS_YN field of OKC_K_PROCESSES
2025 -- Business Rules :
2026 -- Parameters :
2027 -- Version : 1.0
2028 -- End of comments
2029
2030 PROCEDURE update_invalid_approver(itemtype IN VARCHAR2,
2031 itemkey IN VARCHAR2,
2032 actid IN NUMBER,
2033 funcmode IN VARCHAR2,
2034 resultout OUT NOCOPY VARCHAR2 )
2035 IS
2036 CURSOR csr_process_id(p_contract_id IN NUMBER)
2037 IS
2038 SELECT id, in_process_yn
2039 FROM okc_k_processes
2040 WHERE chr_id = p_contract_id
2041 /*Bug 3255018 AND pdf_id = (SELECT id */
2042 AND pdf_id in (SELECT id
2043 FROM OKC_PROCESS_DEFS_V
2044 WHERE usage = 'APPROVE'
2045 AND PDF_TYPE = 'WPS'
2046 AND WF_NAME = 'OKCAUKAP');
2047
2048 l_contract_id NUMBER;
2049 l_return_status VARCHAR2(30);
2050 l_msg_count NUMBER;
2051 l_msg_data VARCHAR2(4000);
2052 l_api_version NUMBER :=1.0 ;
2053 l_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2054 x_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2055 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE;
2056 l_process_id NUMBER;
2057 l_in_process_yn VARCHAR2(1);
2058 BEGIN
2059 --
2060 -- RUN mode - normal process execution
2061 --
2062 IF (funcmode = 'RUN') THEN
2063 empty_mess( itemtype, itemkey );
2064
2065 l_contract_id := wf_engine.GetItemAttrNumber(
2066 itemtype => itemtype,
2067 itemkey => itemkey,
2068 aname => 'CONTRACT_ID');
2069
2070 OPEN csr_process_id(l_contract_id);
2071 FETCH csr_process_id INTO l_process_id, l_in_process_yn;
2072 CLOSE csr_process_id;
2073
2074 IF l_in_process_yn = 'E' THEN
2075 l_cpsv_rec.id := l_process_id;
2076 l_cpsv_rec.in_process_yn := NULL;
2077
2078 Okc_Contract_Pub.update_contract_process(
2079 p_api_version => l_api_version,
2080 p_init_msg_list => l_init_msg_list,
2081 x_return_status => l_return_status,
2082 x_msg_count => l_msg_count,
2083 x_msg_data => l_msg_data,
2084 p_cpsv_rec => l_cpsv_rec,
2085 x_cpsv_rec => x_cpsv_rec);
2086
2087 IF (l_return_status = Okc_Api.G_RET_STS_SUCCESS) THEN
2088 resultout := 'COMPLETE:T';
2089 RETURN;
2090 ELSE
2091 load_mess( itemtype, itemkey );
2092 resultout := 'COMPLETE:F';
2093 RETURN;
2094 END IF;
2095 END IF;
2096 END IF; -- run mode
2097
2098 --
2099 -- CANCEL mode
2100 --
2101 IF (funcmode = 'CANCEL') THEN
2102 --
2103 resultout := 'COMPLETE:';
2104 RETURN;
2105 --
2106 END IF;
2107 --
2108 -- TIMEOUT mode
2109 --
2110 IF (funcmode = 'TIMEOUT') THEN
2111 --
2112 resultout := 'COMPLETE:';
2113 RETURN;
2114 --
2115 END IF;
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 wf_core.context('Okc_Wf_K_Approve',
2119 'update_invalid_approver',
2120 itemtype,
2121 itemkey,
2122 TO_CHAR(actid),
2123 funcmode);
2124 RAISE;
2125 END update_invalid_approver;
2126
2127 end OKC_WF_K_APPROVE;