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