[Home] [Help]
PACKAGE BODY: APPS.OKS_WF_K_APPROVE
Source
1 PACKAGE BODY OKS_WF_K_APPROVE AS
2 /* $Header: OKSWCAPB.pls 120.11.12000000.2 2007/05/16 23:52:21 skkoppul 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+1);
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 /*
660 cursor Actual_Performer_csr is
661 select --+ORDERED
662 u.name, u.display_name
663 from
664 wf_users u
665 where
666 (
667 (l_context not like 'email:%' and u.NAME=l_context)
668 or
669 (l_context like 'email:%' and u.EMAIL_ADDRESS=substr(l_context,7))
670 );
671 */
672 -- Broken the above cursor into two due to bug 4865186 to avoid FTS
673 CURSOR csr_actual_performer_1(p_context IN VARCHAR2) IS
674 select u.name, u.display_name
675 from wf_users u
676 where UPPER(u.EMAIL_ADDRESS)=UPPER(substr(p_context,7));
677
678 CURSOR csr_actual_performer_2(p_context IN VARCHAR2) IS
679 select u.name, u.display_name
680 from wf_users u
681 where u.NAME=p_context;
682
683 begin
684 if (funcmode in('RESPOND','TRANSFER')) then
685 l_context := wf_engine.context_text;
686
687 IF l_context like 'email:%' THEN
688 OPEN csr_actual_performer_1(l_context);
689 FETCH csr_actual_performer_1 INTO l_name, l_display_name;
690 CLOSE csr_actual_performer_1;
691 ELSE
692 OPEN csr_actual_performer_2(l_context);
693 FETCH csr_actual_performer_2 INTO l_name, l_display_name;
694 CLOSE csr_actual_performer_2;
695 END IF;
696
697 wf_engine.SetItemAttrText (itemtype => itemtype,
698 itemkey => itemkey,
699 aname => 'SIGNATORY_USERNAME',
700 avalue => l_name);
701 wf_engine.SetItemAttrText (itemtype => itemtype,
702 itemkey => itemkey,
703 aname => 'SIGNATORY_DISPLAY_NAME',
704 avalue => l_display_name);
705 wf_engine.SetItemAttrText (itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'FROM',
708 avalue => l_name);
709 return;
710 end if;
711 return;
712 exception
713 when others then
714 wf_core.context('OKC_WF_K_APPROVE',
715 'POST_SIGN',
716 itemtype,
717 itemkey,
718 to_char(actid),
719 funcmode);
720 raise;
721 end Post_Sign;
722
723 -- Start of comments
724 --
725 -- Procedure Name : IS_related
726 -- Description : determins K relation to IStore
727 -- Business Rules :
728 -- Parameters :
729 -- Version : 1.0
730 -- End of comments
731
732 procedure IS_related( itemtype in varchar2,
733 itemkey in varchar2,
734 actid in number,
735 funcmode in varchar2,
736 resultout out nocopy varchar2 ) is
737 L1 varchar2(1):='F';
738 /* commented by GunA
739 cursor c1 is
740 select 'T'
741 from OKC_K_REL_OBJS_V R, ASO_Quote_Headers_ALL Q
742 where R.chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
743 -- and R.RTY_CODE = 'CONTRACTNEGOTIATESQUOTE'
744 and R.RTY_CODE like 'CONTRACT%IS%TERM%FOR%QUOTE'
745 and R.CLE_ID IS NULL
746 and Q.QUOTE_HEADER_ID = R.OBJECT1_ID1
747 and Q.QUOTE_SOURCE_CODE like 'IStore%' ;
748 */
749 /* Cursor to check contracts in IStore and Quotation */
750 -- Bug#2208391 - added check of profile option 'OKC_CREATE_ORDER_FROM_K'
751 CURSOR c1 is
752 select 'T'
753 from okc_k_rel_objs
754 where chr_id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
755 and JTOT_OBJECT1_CODE = G_OBJECT_CODE
756 ---and RTY_CODE = G_TERMSFORQUOTE ;
757 -- and RTY_CODE in (G_TERMSFORQUOTE, G_NEGOTIATESQUOTE);
758 and (RTY_CODE=G_TERMSFORQUOTE OR RTY_CODE=G_NEGOTIATESQUOTE
759 AND Nvl(Fnd_Profile.Value('OKC_CREATE_ORDER_FROM_K'),'Y')='N');
760 --taking BOTH into account as per Bug 2050306 abkumar
761 BEGIN
762 mo_global.init('OKC');
763 --
764 -- RUN mode - normal process execution
765 --
766 if (funcmode = 'RUN') then
767 open c1;
768 fetch c1 into L1;
769 close c1;
770 resultout := 'COMPLETE:'||L1;
771 end if;
772 --
773 -- CANCEL mode
774 --
775 if (funcmode = 'CANCEL') then
776 --
777 resultout := 'COMPLETE:';
778 return;
779 --
780 end if;
781 --
782 -- TIMEOUT mode
783 --
784 if (funcmode = 'TIMEOUT') then
785 --
786 resultout := 'COMPLETE:';
787 return;
788 --
789 end if;
790 exception
791 when others then
792 wf_core.context('OKC_WF_K_APPROVE',
793 'IS_RELATED',
794 itemtype,
795 itemkey,
796 to_char(actid),
797 funcmode);
798 raise;
799 end IS_related;
800
801
802
803 -- Start of comments
804 --
805 -- Procedure Name : note_filled
806 -- Description : note mandatory if reject
807 -- Business Rules :
808 -- Parameters :
809 -- Version : 1.0
810 -- End of comments
811
812 procedure note_filled( itemtype in varchar2,
813 itemkey in varchar2,
814 actid in number,
815 funcmode in varchar2,
816 resultout out nocopy varchar2 ) is
817 begin
818 --
819 -- RUN mode - normal process execution
820 --
821 if (funcmode = 'RUN') then
822 if (wf_engine.GetItemAttrText(itemtype,itemkey,'NOTE') is NULL) then
823 resultout := 'COMPLETE:F';
824 else
825 resultout := 'COMPLETE:T';
826 end if;
827 end if;
828 --
829 -- CANCEL mode
830 --
831 if (funcmode = 'CANCEL') then
832 --
833 resultout := 'COMPLETE:';
834 return;
835 --
836 end if;
837 --
838 -- TIMEOUT mode
839 --
840 if (funcmode = 'TIMEOUT') then
841 --
842 resultout := 'COMPLETE:';
843 return;
844 --
845 end if;
846 exception
847 when others then
848 wf_core.context('OKC_WF_K_APPROVE',
849 'NOTE_FILLED',
850 itemtype,
851 itemkey,
852 to_char(actid),
853 funcmode);
854 raise;
855 end note_filled;
856
857 --
858 -- Procedure Name : IS_K_TEMPLATE
859 -- Description : determines if K is a template
860 -- Business Rules :
861 -- Parameters :
862 -- Version : 1.0
863
864 procedure IS_K_TEMPLATE( itemtype in varchar2,
865 itemkey in varchar2,
866 actid in number,
867 funcmode in varchar2,
868 resultout out nocopy varchar2 ) is
869 --
870 L1 varchar2(1):='F';
871 --
872 -- Cursor to check if contract is a template
873 CURSOR c1 is
874 select 'T'
875 from okc_k_headers_v
876 where id = wf_engine.GetItemAttrNumber(itemtype,itemkey,'CONTRACT_ID')
877 and template_yn = 'Y';
878 --
879 BEGIN
880 --
881 -- RUN mode - normal process execution
882 --
883 if (funcmode = 'RUN') then
884 open c1;
885 fetch c1 into L1;
886 close c1;
887 resultout := 'COMPLETE:'||L1;
888 end if;
889 --
890 -- CANCEL mode
891 --
892 if (funcmode = 'CANCEL') then
893 --
894 resultout := 'COMPLETE:';
895 return;
896 --
897 end if;
898 --
899 -- TIMEOUT mode
900 --
901 if (funcmode = 'TIMEOUT') then
902 --
903 resultout := 'COMPLETE:';
904 return;
905 --
906 end if;
907 exception
908 when others then
909 wf_core.context('OKC_WF_K_APPROVE',
910 'IS_K_TEMPLATE',
911 itemtype,
912 itemkey,
913 to_char(actid),
914 funcmode);
915 raise;
916 end IS_K_TEMPLATE;
917
918 -- Start of comments
919 --
920 -- Procedure Name : Initialize
921 -- Description : Initialization of attributes that were not initialized by k_start API
922 -- Business Rules :
923 -- Parameters :
924 -- Version : 1.0
925 -- End of comments
926
927 procedure Initialize ( itemtype in varchar2,
928 itemkey in varchar2,
929 actid in number,
930 funcmode in varchar2,
931 resultout out nocopy varchar2 ) is
932 L_CONTRACT_ADMIN_USERNAME varchar2(240); --Bug:3018825 increased legth to 240
933 V_DUMMY varchar2(240);
934 L_SIGNATORY_USERNAME varchar2(240); --Bug:3018825 increased legth to 240
935 L_SIGNATORY_DISPLAY_NAME varchar2(240);
936 l_id number;
937 L_INITIATOR_NAME varchar2(240);
938 L_INITIATOR_DISPLAY_NAME varchar2(240);
939 L_K_SHORT_DESCRIPTION varchar2(4000);
940
941 cursor C_INITIATOR_DISPLAY_NAME(P_USER_ID in number) is
942 /*
943 select name,display_name
944 from wf_roles
945 where orig_system = 'FND_USR'
946 and orig_system_id=P_USER_ID
947 union all
948 select
949 USR.USER_NAME name,
950 PER.FULL_NAME display_name
951 from
952 PER_PEOPLE_F PER,
953 FND_USER USR
954 where trunc(SYSDATE) between PER.EFFECTIVE_START_DATE
955 and PER.EFFECTIVE_END_DATE
956 and PER.PERSON_ID = USR.EMPLOYEE_ID
957 and USR.USER_ID = P_USER_ID
958 and not exists (select '1'
959 from wf_roles
960 where orig_system = 'FND_USR'
961 and orig_system_id=P_USER_ID)
962 -- replaced to boost perf
963 */
964 select user_name name,user_name display_name
965 from fnd_user
966 where user_id=P_USER_ID
967 and employee_id is null
968 union all
969 select
970 USR.USER_NAME name, PER.FULL_NAME display_name
971 from
972 PER_PEOPLE_F PER,
973 FND_USER USR
974 where trunc(SYSDATE)
975 between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE
976 and PER.PERSON_ID = USR.EMPLOYEE_ID
977 and USR.USER_ID = P_USER_ID
978 ;
979
980 cursor C_K_SHORT_DESCRIPTION(P_CONTRACT_ID in number) is
981 select SHORT_DESCRIPTION
982 from okc_k_headers_tl
983 where id = P_CONTRACT_ID
984 and language=userenv('LANG');
985 begin
986 IF (l_debug = 'Y') THEN
987 okc_debug.log('OKSWCAPB: Initialize Start of initialize()', 2);
988 END IF;
989 mo_global.init('OKS');
990 --
991 -- RUN mode - normal process execution
992 --
993 if (funcmode = 'RUN') then
994 --
995 -- Initiator/Initial
996 --
997 l_id := wf_engine.GetItemAttrNumber(
998 itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'USER_ID');
1001 open C_INITIATOR_DISPLAY_NAME(l_id);
1002 fetch C_INITIATOR_DISPLAY_NAME into L_INITIATOR_NAME,L_INITIATOR_DISPLAY_NAME;
1003 close C_INITIATOR_DISPLAY_NAME;
1004
1005 IF (l_debug = 'Y') THEN
1006 okc_debug.log('OKSWCAPB: Initialize() USER_ID='||l_id, 2);
1007 END IF;
1008
1009 wf_engine.SetItemAttrText (
1010 itemtype => itemtype,
1011 itemkey => itemkey,
1012 aname => 'INITIATOR_NAME',
1013 avalue => L_INITIATOR_NAME);
1014 wf_engine.SetItemAttrText (
1015 itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'INITIATOR_DISPLAY_NAME',
1018 avalue => L_INITIATOR_DISPLAY_NAME);
1019 wf_engine.SetItemAttrText (
1020 itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => 'FROM',
1023 avalue => L_INITIATOR_NAME);
1024 --
1025 -- Administrator U/name
1026 --
1027 select_next(itemtype => itemtype,
1028 itemkey => itemkey,
1029 p_role_type => 'ADMINISTRATOR',
1030 x_role => L_CONTRACT_ADMIN_USERNAME,
1031 x_name => V_DUMMY);
1032 wf_engine.SetItemAttrText (
1033 itemtype => itemtype,
1034 itemkey => itemkey,
1035 aname => 'CONTRACT_ADMIN_USERNAME',
1036 avalue => L_CONTRACT_ADMIN_USERNAME);
1037 --
1038 -- Signatory U/D/name
1039 --
1040 select_next(itemtype => itemtype,
1041 itemkey => itemkey,
1042 p_role_type => 'SIGNATORY',
1043 x_role => L_SIGNATORY_USERNAME,
1044 x_name => L_SIGNATORY_DISPLAY_NAME);
1045 wf_engine.SetItemAttrText (
1046 itemtype => itemtype,
1047 itemkey => itemkey,
1048 aname => 'SIGNATORY_USERNAME',
1049 avalue => L_SIGNATORY_USERNAME);
1050
1051 wf_engine.SetItemAttrText (
1052 itemtype => itemtype,
1053 itemkey => itemkey,
1054 aname => 'SIGNATORY_DISPLAY_NAME',
1055 avalue => L_SIGNATORY_DISPLAY_NAME);
1056 --
1057 l_id := wf_engine.GetItemAttrNumber(
1058 itemtype => itemtype,
1059 itemkey => itemkey,
1060 aname => 'CONTRACT_ID');
1061 IF (l_debug = 'Y') THEN
1062 okc_debug.log('OKSWCAPB: Initialize() -- Before call ame_ap2.clearAllApprovals()', 2);
1063 END IF;
1064
1065 ame_api2.clearAllApprovals(
1066 applicationIdIn => G_APPLICATION_ID,
1067 transactionTypeIn => G_TRANSACTION_TYPE,
1068 transactionIdIn => l_id);
1069 IF (l_debug = 'Y') THEN
1070 okc_debug.log('OKSWCAPB: Initialize() -- After call ame_ap2.clearAllApprovals()', 2);
1071 END IF;
1072
1073 open C_K_SHORT_DESCRIPTION(l_id);
1074 fetch C_K_SHORT_DESCRIPTION into L_K_SHORT_DESCRIPTION;
1075 close C_K_SHORT_DESCRIPTION;
1076 wf_engine.SetItemAttrText (
1077 itemtype => itemtype,
1078 itemkey => itemkey,
1079 aname => 'K_SHORT_DESCRIPTION',
1080 avalue => L_K_SHORT_DESCRIPTION);
1081 --
1082 IF (l_debug = 'Y') THEN
1083 okc_debug.log('OKSWCAPB: Initialize() -- Before call update_invalid_approver()', 2);
1084 END IF;
1085
1086 update_invalid_approver(itemtype => itemtype,
1087 itemkey => itemkey,
1088 actid => actid,
1089 funcmode => funcmode,
1090 resultout => resultout);
1091 IF (l_debug = 'Y') THEN
1092 okc_debug.log('OKSWCAPB: Initialize() -- After call update_invalid_approver()', 2);
1093 END IF;
1094
1095 resultout := 'COMPLETE:';
1096 return;
1097 --
1098 end if;
1099 --
1100 -- CANCEL mode
1101 --
1102 if (funcmode = 'CANCEL') then
1103 --
1104 resultout := 'COMPLETE:';
1105 return;
1106 --
1107 end if;
1108 --
1109 -- TIMEOUT mode
1110 --
1111 if (funcmode = 'TIMEOUT') then
1112 --
1113 resultout := 'COMPLETE:';
1114 return;
1115 --
1116 end if;
1117 exception
1118 when others then
1119 wf_core.context('OKC_WF_K_APPROVE',
1120 'INITIALIZE',
1121 itemtype,
1122 itemkey,
1123 to_char(actid),
1124 funcmode);
1125 raise;
1126 end Initialize;
1127
1128 -- Start of comments
1129 --
1130 -- Procedure Name : Select_Approver
1131 -- Description : Customize using your approval chain
1132 -- Business Rules :
1133 -- Parameters :
1134 -- Version : 1.0
1135 -- End of comments
1136
1137 procedure Select_Approver( itemtype in varchar2,
1138 itemkey in varchar2,
1139 actid in number,
1140 funcmode in varchar2,
1141 resultout out nocopy varchar2 ) is
1142
1143 l_api_name CONSTANT VARCHAR2(50) := 'Select_Approver';
1144
1145 L_INITIATOR_DISPLAY_NAME VARCHAR2(240);
1146 L_NEXT_PERFORMER_USERNAME VARCHAR2(240);
1147 L_NEXT_PERFORMER_USERNAME_OUT VARCHAR2(240);
1148 L_N_PERFORMER_DISPLAY_NAME VARCHAR2(240);
1149
1150 BEGIN
1151 IF (l_debug = 'Y') THEN
1152 okc_debug.log('OKSWCAPB: Select_Approver() -- Start of Select_Approver()', 2);
1153 END IF;
1154
1155 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1156 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE , G_MODULE||l_api_name,
1157 'Entered '||G_PKG_NAME ||'.'||l_api_name);
1158 END IF;
1159 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1160 FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
1161 'itemtype: ' || itemtype ||
1162 ' itemkey: ' || itemkey ||
1163 ' actid: ' || to_char(actid) ||
1164 ' funcmode: ' || funcmode);
1165 END IF;
1166
1167 mo_global.init('OKC');
1168 --
1169 -- RUN mode - normal process execution
1170 --
1171 if (funcmode = 'RUN') then
1172 L_NEXT_PERFORMER_USERNAME := wf_engine.GetItemAttrText(
1173 itemtype => itemtype,
1174 itemkey => itemkey,
1175 aname => 'NEXT_PERFORMER_USERNAME');
1176 if (L_NEXT_PERFORMER_USERNAME is NULL) then -- just start
1177 L_INITIATOR_DISPLAY_NAME := wf_engine.GetItemAttrText(
1178 itemtype => itemtype,
1179 itemkey => itemkey,
1180 aname => 'INITIATOR_DISPLAY_NAME');
1181 -- Empty message attributes so that we can push new messages in case of errors
1182 empty_mess(
1183 itemtype => itemtype,
1184 itemkey => itemkey
1185 );
1186 -- Now get the Approver
1187 BEGIN
1188 select_next(itemtype => itemtype,
1189 itemkey => itemkey,
1190 p_role_type => 'APPROVER',
1191 p_current => NULL,
1192 x_role => L_NEXT_PERFORMER_USERNAME,
1193 x_name => L_N_PERFORMER_DISPLAY_NAME);
1194 EXCEPTION
1195 WHEN NoValidApproverException THEN
1196 -- Load the item attributes with error messages
1197 load_mess(
1198 itemtype => itemtype,
1199 itemkey => itemkey
1200 );
1201 -- set the performer(salesrep, Help desk or initiator) of this notification
1202 set_performer(
1203 itemtype => itemtype,
1204 itemkey => itemkey
1205 );
1206 resultout := 'COMPLETE:';
1207 return;
1208 END;
1209 -- just for common situation if no approvers at all
1210 -- then Contract is considered as approved by initiator
1211 if (L_NEXT_PERFORMER_USERNAME is NULL) then
1212 wf_engine.SetItemAttrText (
1213 itemtype => itemtype,
1214 itemkey => itemkey,
1215 aname => 'N_PERFORMER_DISPLAY_NAME',
1216 avalue => L_INITIATOR_DISPLAY_NAME);
1217 wf_engine.SetItemAttrText (
1218 itemtype => itemtype,
1219 itemkey => itemkey,
1220 aname => 'FINAL_APPROVER_UNAME',
1221 avalue => NULL);
1222 resultout := 'COMPLETE:F';
1223 else
1224 wf_engine.SetItemAttrText (
1225 itemtype => itemtype,
1226 itemkey => itemkey,
1227 aname => 'P_PERFORMER_DISPLAY_NAME',
1228 avalue => L_INITIATOR_DISPLAY_NAME);
1229 wf_engine.SetItemAttrText (
1230 itemtype => itemtype,
1231 itemkey => itemkey,
1232 aname => 'NEXT_PERFORMER_USERNAME',
1233 avalue => L_NEXT_PERFORMER_USERNAME);
1234 wf_engine.SetItemAttrText(itemtype => itemtype,
1235 itemkey => itemkey,
1236 aname => 'N_PERFORMER_DISPLAY_NAME',
1237 avalue => L_N_PERFORMER_DISPLAY_NAME);
1238 resultout := 'COMPLETE:T';
1239 end if;
1240 return;
1241 else
1242 wf_engine.SetItemAttrText
1243 (
1244 itemtype => itemtype,
1245 itemkey => itemkey,
1246 aname => 'P_PERFORMER_DISPLAY_NAME',
1247 avalue => wf_engine.GetItemAttrText(
1248 itemtype => itemtype,
1249 itemkey => itemkey,
1250 aname => 'N_PERFORMER_DISPLAY_NAME')
1251 );
1252
1253 -- Empty message attributes so that we can push new messages in case of errors
1254 empty_mess(
1255 itemtype => itemtype,
1256 itemkey => itemkey
1257 );
1258 -- Now get the Approver
1259 BEGIN
1260 select_next(itemtype => itemtype,
1261 itemkey => itemkey,
1262 p_role_type => 'APPROVER',
1263 p_current => L_NEXT_PERFORMER_USERNAME,
1264 x_role => L_NEXT_PERFORMER_USERNAME_OUT,
1265 x_name => L_N_PERFORMER_DISPLAY_NAME);
1266 EXCEPTION
1267 WHEN NoValidApproverException THEN
1268 -- Load the item attributes with error messages
1269 load_mess(
1270 itemtype => itemtype,
1271 itemkey => itemkey
1272 );
1273 -- set the performer(salesrep, Help desk or initiator) of this notification
1274 set_performer(
1275 itemtype => itemtype,
1276 itemkey => itemkey
1277 );
1278 resultout := 'COMPLETE:';
1279 return;
1280 END;
1281 if (L_NEXT_PERFORMER_USERNAME_OUT is NULL) then
1282 wf_engine.SetItemAttrText (
1283 itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'FINAL_APPROVER_UNAME',
1286 avalue => wf_engine.GetItemAttrText(
1287 itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname => 'NEXT_PERFORMER_USERNAME'));
1290
1291 wf_engine.SetItemAttrText (
1292 itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => 'NEXT_PERFORMER_USERNAME',
1295 avalue => NULL);
1296 resultout := 'COMPLETE:F';
1297
1298 else
1299
1300 wf_engine.SetItemAttrText (
1301 itemtype => itemtype,
1302 itemkey => itemkey,
1303 aname => 'NEXT_PERFORMER_USERNAME',
1304 avalue => L_NEXT_PERFORMER_USERNAME_OUT);
1305
1306 wf_engine.SetItemAttrText(
1307 itemtype => itemtype,
1308 itemkey => itemkey,
1309 aname => 'N_PERFORMER_DISPLAY_NAME',
1310 avalue => L_N_PERFORMER_DISPLAY_NAME);
1311
1312 resultout := 'COMPLETE:T';
1313 end if;
1314 return;
1315 --
1316 end if;
1317 end if;
1318 --
1319 -- CANCEL mode
1320 --
1321 if (funcmode = 'CANCEL') then
1322 --
1323 resultout := 'COMPLETE:';
1324 return;
1325 --
1326 end if;
1327 --
1328 -- TIMEOUT mode
1329 --
1330 if (funcmode = 'TIMEOUT') then
1331 --
1332 resultout := 'COMPLETE:';
1333 return;
1334 --
1335 end if;
1336 IF (l_debug = 'Y') THEN
1337 okc_debug.log('OKSWCAPB: Select_Approver() -- End of Select_Approver()', 2);
1338 END IF;
1339 exception
1340 when others then
1341 wf_core.context('OKC_WF_K_APPROVE',
1342 'SELECT_APPROVER',
1343 itemtype,
1344 itemkey,
1345 to_char(actid),
1346 funcmode);
1347 raise;
1348 end Select_Approver;
1349
1350
1351
1352
1353 -- Start of comments
1354 --
1355 -- Procedure Name : Notify_AME
1356 -- Description : To Update AME the status of approver
1357 -- Business Rules :
1358 -- Parameters :
1359 -- Version : 1.0
1360 -- End of comments
1361
1362 procedure Update_AME( itemtype in varchar2,
1363 itemkey in varchar2,
1364 actid in number,
1365 funcmode in varchar2,
1366 resultout out nocopy varchar2) is
1367
1368 begin
1369
1370
1371 ame_api2.updateApprovalStatus2(
1372 applicationIdIn => G_APPLICATION_ID,
1373 transactionTypeIn => G_TRANSACTION_TYPE,
1374 transactionIdIn => wf_engine.GetItemAttrText(itemtype,itemkey,'CONTRACT_ID'),
1375 approvalStatusIn => ame_util.approvedStatus,
1376 approverNameIn => wf_engine.GetItemAttrText(itemtype,itemkey,'ACTUAL_PERFORMER'),
1377 forwardeeIn => ame_util.emptyApproverRecord2
1378 );
1379
1380 end;
1381
1382 -- Start of comments
1383 --
1384 -- Procedure Name : Select_Informed
1385 -- Description : Customize using your To be Informed chain
1386 -- Business Rules :
1387 -- Parameters :
1388 -- Version : 1.0
1389 -- End of comments
1390
1391 procedure Select_Informed( itemtype in varchar2,
1392 itemkey in varchar2,
1393 actid in number,
1394 funcmode in varchar2,
1395 resultout out nocopy varchar2) is
1396 L_NEXT_INFORMED_USERNAME varchar2(100);
1397 L_NEXT_INFORMED_USERNAME_OUT varchar2(100);
1398 v_dummy varchar2(240);
1399 begin
1400
1401 --
1402 -- RUN mode - normal process execution
1403 --
1404 if (funcmode = 'RUN') then
1405
1406 L_NEXT_INFORMED_USERNAME := wf_engine.GetItemAttrText(
1407 itemtype => itemtype,
1408 itemkey => itemkey,
1409 aname => 'NEXT_INFORMED_USERNAME');
1410
1411 select_next(itemtype => itemtype,
1412 itemkey => itemkey,
1413 p_role_type => 'INFORMED',
1414 p_current => L_NEXT_INFORMED_USERNAME,
1415 x_role => L_NEXT_INFORMED_USERNAME_OUT,
1416 x_name => V_DUMMY);
1417
1418 wf_engine.SetItemAttrText (
1419 itemtype => itemtype,
1420 itemkey => itemkey,
1421 aname => 'NEXT_INFORMED_USERNAME',
1422 avalue => L_NEXT_INFORMED_USERNAME_OUT);
1423
1424 if (L_NEXT_INFORMED_USERNAME_OUT is NULL) then
1425 resultout := 'COMPLETE:F';
1426 else
1427 resultout := 'COMPLETE:T';
1428 end if;
1429 return;
1430 end if;
1431
1432 --
1433 -- CANCEL mode
1434 --
1435 if (funcmode = 'CANCEL') then
1436 --
1437 resultout := 'COMPLETE:';
1438 return;
1439 --
1440 end if;
1441 --
1442 -- TIMEOUT mode
1443 --
1444 if (funcmode = 'TIMEOUT') then
1445 --
1446 resultout := 'COMPLETE:';
1447 return;
1448 --
1449 end if;
1450 exception
1451 when others then
1452 wf_core.context('OKC_WF_K_APPROVE',
1453 'SELECT_INFORMED',
1454 itemtype,
1455 itemkey,
1456 to_char(actid),
1457 funcmode);
1458 raise;
1459 end Select_Informed;
1460
1461 procedure Select_Informed_A( itemtype in varchar2,
1462 itemkey in varchar2,
1463 actid in number,
1464 funcmode in varchar2,
1465 resultout out nocopy varchar2) is
1466 begin
1467 Select_Informed( itemtype,
1468 itemkey ,
1469 actid ,
1470 funcmode,
1471 resultout);
1472 end;
1473
1474 procedure Select_Informed_AR( itemtype in varchar2,
1475 itemkey in varchar2,
1476 actid in number,
1477 funcmode in varchar2,
1478 resultout out nocopy varchar2) is
1479 begin
1480 Select_Informed( itemtype,
1481 itemkey ,
1482 actid ,
1483 funcmode,
1484 resultout);
1485 end;
1486
1487 procedure Select_Informed_S( itemtype in varchar2,
1488 itemkey in varchar2,
1489 actid in number,
1490 funcmode in varchar2,
1491 resultout out nocopy varchar2) is
1492 begin
1493 Select_Informed( itemtype,
1494 itemkey ,
1495 actid ,
1496 funcmode,
1497 resultout);
1498 end;
1499
1500 procedure Select_Informed_SR( itemtype in varchar2,
1501 itemkey in varchar2,
1502 actid in number,
1503 funcmode in varchar2,
1504 resultout out nocopy varchar2) is
1505 begin
1506 Select_Informed( itemtype,
1507 itemkey ,
1508 actid ,
1509 funcmode,
1510 resultout);
1511 end;
1512
1513
1514 -- Start of comments
1515 --
1516 -- Procedure Name : Record_Approved
1517 -- Description : Does not need customization
1518 -- Business Rules :
1519 -- Parameters :
1520 -- Version : 1.0
1521 -- End of comments
1522
1523 procedure Record_Approved( itemtype in varchar2,
1524 itemkey in varchar2,
1525 actid in number,
1526 funcmode in varchar2,
1527 resultout out nocopy varchar2 ) is
1528 L_CONTRACT_ID number;
1529 x_return_status varchar2(1);
1530 begin
1531 mo_global.init('OKC');
1532 --
1533 -- RUN mode - normal process execution
1534 --
1535 if (funcmode = 'RUN') then
1536
1537 empty_mess( itemtype,
1538 itemkey );
1539 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1540 itemtype => itemtype,
1541 itemkey => itemkey,
1542 aname => 'CONTRACT_ID');
1543 OKC_CONTRACT_APPROVAL_PUB.k_approved(
1544 p_contract_id => L_CONTRACT_ID,
1545 x_return_status => x_return_status);
1546 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1547 then
1548 --
1549 wf_engine.SetItemAttrDate (
1550 itemtype => itemtype,
1551 itemkey => itemkey,
1552 aname => 'DATE_SIGNED',
1553 avalue => sysdate);
1554 resultout := 'COMPLETE:T';
1555 return;
1556 --
1557 else
1558 --
1559 -- load_mess( itemtype,
1560 -- itemkey );
1561 resultout := 'COMPLETE:F';
1562 return;
1563 --
1564 end if;
1565 end if;
1566 --
1567 -- CANCEL mode
1568 --
1569 if (funcmode = 'CANCEL') then
1570 --
1571 resultout := 'COMPLETE:';
1572 return;
1573 --
1574 end if;
1575 --
1576 -- TIMEOUT mode
1577 --
1578 if (funcmode = 'TIMEOUT') then
1579 --
1580 resultout := 'COMPLETE:';
1581 return;
1582 --
1583 end if;
1584 exception
1585 when others then
1586 wf_core.context('OKC_WF_K_APPROVE',
1587 'RECORD_APPROVED',
1588 itemtype,
1589 itemkey,
1590 to_char(actid),
1591 funcmode);
1592 raise;
1593 end Record_Approved;
1594
1595 -- Start of comments
1596 --
1597 -- Procedure Name : Erase_Approved
1598 -- Description : Does not need customization
1599 -- Business Rules :
1600 -- Parameters :
1601 -- Version : 1.0
1602 -- End of comments
1603
1604 procedure Erase_Approved( itemtype in varchar2,
1605 itemkey in varchar2,
1606 actid in number,
1607 funcmode in varchar2,
1608 resultout out nocopy varchar2 ) is
1609 L_CONTRACT_ID number;
1610 x_return_status varchar2(1);
1611 begin
1612 mo_global.init('OKC');
1613 --
1614 -- RUN mode - normal process execution
1615 --
1616 if (funcmode = 'RUN') then
1617 --
1618 empty_mess( itemtype,
1619 itemkey );
1620 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1621 itemtype => itemtype,
1622 itemkey => itemkey,
1623 aname => 'CONTRACT_ID');
1624 OKC_CONTRACT_APPROVAL_PUB.k_erase_approved(
1625 p_contract_id => L_CONTRACT_ID,
1626 x_return_status => x_return_status);
1627 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1628 then
1629 --
1630 resultout := 'COMPLETE:T';
1631 return;
1632 --
1633 else
1634 --
1635 load_mess( itemtype,
1636 itemkey );
1637 resultout := 'COMPLETE:F';
1638 return;
1639 --
1640 end if;
1641 end if;
1642 --
1643 -- CANCEL mode
1644 --
1645 if (funcmode = 'CANCEL') then
1646 --
1647 resultout := 'COMPLETE:';
1648 return;
1649 --
1650 end if;
1651 --
1652 -- TIMEOUT mode
1653 --
1654 if (funcmode = 'TIMEOUT') then
1655 --
1656 resultout := 'COMPLETE:';
1657 return;
1658 --
1659 end if;
1660 exception
1661 when others then
1662 wf_core.context('OKC_WF_K_APPROVE',
1663 'ERASE_APPROVED',
1664 itemtype,
1665 itemkey,
1666 to_char(actid),
1667 funcmode);
1668 raise;
1669 end Erase_Approved;
1670
1671 -- Start of comments
1672 --
1673 -- Procedure Name : Record_Signed
1674 -- Description : Could be customized to widen sign procedure
1675 -- Business Rules :
1676 -- Parameters :
1677 -- Version : 1.0
1678 -- End of comments
1679
1680 procedure Record_Signed(itemtype in varchar2,
1681 itemkey in varchar2,
1682 actid in number,
1683 funcmode in varchar2,
1684 resultout out nocopy varchar2 ) is
1685 L_DATE_SIGNED Date;
1686 L_CONTRACT_ID number;
1687 x_return_status varchar2(1);
1688 begin
1689 mo_global.init('OKC');
1690 --
1691 -- RUN mode - normal process execution
1692 --
1693 if (funcmode = 'RUN') then
1694 --
1695 empty_mess( itemtype,
1696 itemkey );
1697 L_DATE_SIGNED := wf_engine.GetItemAttrDate (
1698 itemtype => itemtype,
1699 itemkey => itemkey,
1700 aname => 'DATE_SIGNED');
1701 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
1702 itemtype => itemtype,
1703 itemkey => itemkey,
1704 aname => 'CONTRACT_ID');
1705 OKC_CONTRACT_APPROVAL_PUB.k_signed(
1706 p_contract_id => L_CONTRACT_ID,
1707 p_date_signed => NVL(L_DATE_SIGNED,sysdate),
1708 x_return_status => x_return_status
1709 );
1710 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
1711 then
1712 --
1713 resultout := 'COMPLETE:T';
1714 return;
1715 --
1716 else
1717 --
1718 load_mess( itemtype,
1719 itemkey );
1720 resultout := 'COMPLETE:F';
1721 return;
1722 --
1723 end if;
1724 end if;
1725 --
1726 -- CANCEL mode
1727 --
1728 if (funcmode = 'CANCEL') then
1729 --
1730 resultout := 'COMPLETE:';
1731 return;
1732 --
1733 end if;
1734 --
1735 -- TIMEOUT mode
1736 --
1737 if (funcmode = 'TIMEOUT') then
1738 --
1739 resultout := 'COMPLETE:';
1740 return;
1741 --
1742 end if;
1743 exception
1744 when others then
1745 wf_core.context('OKC_WF_K_APPROVE',
1746 'RECORD_SIGNED',
1747 itemtype,
1748 itemkey,
1749 to_char(actid),
1750 funcmode);
1751 raise;
1752 end Record_Signed;
1753
1754 procedure Record_Rejected(
1755 itemtype in varchar2,
1756 itemkey in varchar2,
1757 actid in number,
1758 funcmode in varchar2,
1759 resultout out nocopy VARCHAR2
1760 ) is
1761
1762 l_contract_id NUMBER;
1763 l_scs_code VARCHAR2(30);
1764 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1765 l_msg_count NUMBER;
1766 l_msg_data VARCHAR2(2000);
1767
1768 CURSOR csr_class_code(p_chr_id NUMBER) IS
1769 select scs_code
1770 from okc_k_headers_all_b
1771 where ID = p_chr_id;
1772
1773 begin
1774 --
1775 -- RUN mode - normal process execution
1776 --
1777 if (funcmode = 'RUN') then
1778
1779 empty_mess(itemtype,
1780 itemkey );
1781
1782 l_contract_id := wf_engine.GetItemAttrNumber(
1783 itemtype => itemtype,
1784 itemkey => itemkey,
1785 aname => 'CONTRACT_ID');
1786
1787 OPEN csr_class_code(l_contract_id);
1788 FETCH csr_class_code INTO l_scs_code;
1789 CLOSE csr_class_code;
1790
1791 IF l_scs_code in ('SERVICE', 'WARRANTY','SUBSCRIPTION') THEN
1792 OKC_CONTRACT_APPROVAL_PVT.continue_k_process
1793 (
1794 p_api_version => 1.0,
1795 p_init_msg_list => 'T',
1796 x_return_status => l_return_status,
1797 x_msg_count => l_msg_count,
1798 x_msg_data => l_msg_data,
1799 p_contract_id => l_contract_id,
1800 p_wf_item_key => NULL,
1801 p_called_from => 'REJECTED'
1802 );
1803 END IF;
1804 if (l_return_status = OKC_API.G_RET_STS_SUCCESS)
1805 then
1806 --
1807 resultout := 'COMPLETE:T';
1808 return;
1809 --
1810 else
1811 --
1812 load_mess(itemtype,
1813 itemkey );
1814 resultout := 'COMPLETE:F';
1815 return;
1816 --
1817 end if;
1818 end if;
1819 --
1820 -- CANCEL mode
1821 --
1822 if (funcmode = 'CANCEL') then
1823 --
1824 resultout := 'COMPLETE:';
1825 return;
1826 --
1827 end if;
1828 --
1829 -- TIMEOUT mode
1830 --
1831 if (funcmode = 'TIMEOUT') then
1832 --
1833 resultout := 'COMPLETE:';
1834 return;
1835 --
1836 end if;
1837 exception
1838 when others then
1839 wf_core.context('OKS_WF_K_APPROVE',
1840 'Record_Rejected',
1841 itemtype,
1842 itemkey,
1843 to_char(actid),
1844 funcmode);
1845 raise;
1846 end Record_Rejected;
1847
1848 -- Start of comments
1849 --
1850 -- Procedure Name : was_approver
1851 -- Description : note mandatory if reject
1852 -- Business Rules :
1853 -- Parameters :
1854 -- Version : 1.0
1855 -- End of comments
1856
1857 procedure was_approver( itemtype in varchar2,
1858 itemkey in varchar2,
1859 actid in number,
1860 funcmode in varchar2,
1861 resultout out nocopy varchar2 ) is
1862 begin
1863 --
1864 -- RUN mode - normal process execution
1865 --
1866 if (funcmode = 'RUN') then
1867 if (wf_engine.GetItemAttrText(itemtype,itemkey,'FINAL_APPROVER_UNAME') is NULL) then
1868 resultout := 'COMPLETE:F';
1869 else
1870 resultout := 'COMPLETE:T';
1871 end if;
1872 end if;
1873 --
1874 -- CANCEL mode
1875 --
1876 if (funcmode = 'CANCEL') then
1877 --
1878 resultout := 'COMPLETE:';
1879 return;
1880 --
1881 end if;
1882 --
1883 -- TIMEOUT mode
1884 --
1885 if (funcmode = 'TIMEOUT') then
1886 --
1887 resultout := 'COMPLETE:';
1888 return;
1889 --
1890 end if;
1891 exception
1892 when others then
1893 wf_core.context('OKC_WF_K_APPROVE',
1894 'WAS_APPROVER',
1895 itemtype,
1896 itemkey,
1897 to_char(actid),
1898 funcmode);
1899 raise;
1900 end was_approver;
1901
1902 -- Start of comments
1903 --
1904 -- Procedure Name : NOTIFY_SALES_REP_W
1905 -- Description : Procedure to call private API
1906 -- OKC_OC_INT_QTK_PVT.NOTIFY_SALES_REP
1907 -- Business Rules : Private API
1908 -- IN Parameters : itemtype,itemkey ,actid,funcmode
1909 -- OUT Parameters : resultout
1910 -- Version : 1.0
1911 --
1912 -- End of comments
1913 Procedure NOTIFY_SALES_REP_W (itemtype in varchar2,
1914 itemkey in varchar2,
1915 actid in number,
1916 funcmode in varchar2,
1917 resultout out nocopy varchar2 )
1918 IS
1919 --Local Variables
1920 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1921 l_contract_id number :=NULL;
1922 l_msg_count number :=NULL;
1923 l_msg_data varchar2(1000) :=NULL;
1924
1925 --Global Variables
1926 G_API_VERSION NUMBER :=1.0 ;
1927
1928 BEGIN
1929 mo_global.init('OKC');
1930 --
1931 -- RUN mode - normal process execution
1932 --
1933 If (funcmode = 'RUN') then
1934 --
1935
1936 l_contract_id := wf_engine.GetItemAttrNumber(
1937 itemtype => itemtype,
1938 itemkey => itemkey,
1939 aname => 'CONTRACT_ID');
1940 empty_mess( itemtype,
1941 itemkey );
1942 /*
1943 calling notification api to notify quoation and IStore
1944 */
1945 OKC_OC_INT_QTK_PVT.NOTIFY_SALES_REP
1946 (p_api_version => g_api_version
1947 ,p_contract_id => l_contract_id
1948 ,x_msg_count => l_msg_count
1949 ,x_msg_data => l_msg_data
1950 ,x_return_status => l_return_status ) ;
1951
1952 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) then
1953 --
1954 resultout := 'COMPLETE:T';
1955 return;
1956 --
1957 else
1958 --
1959 load_mess( itemtype,
1960 itemkey );
1961 resultout := 'COMPLETE:F';
1962
1963 return;
1964 --
1965 end if;
1966 End If;
1967 EXCEPTION
1968 when others then
1969 wf_core.context(pkg_name => 'OKC_WF_K_APPROVE',
1970 proc_name => 'NOTIFY_SALES_REP_W',
1971 arg1 => itemtype,
1972 arg2 => itemkey,
1973 arg3 => to_char(actid),
1974 arg4 => funcmode);
1975
1976 Raise;
1977 end NOTIFY_SALES_REP_W;
1978
1979 -- Procedure Name : Make_Active
1980 -- Description :
1981 -- Business Rules :
1982 -- Parameters :
1983 -- Version : 1.0
1984 -- End of comments
1985
1986 procedure Make_Active(itemtype in varchar2,
1987 itemkey in varchar2,
1988 actid in number,
1989 funcmode in varchar2,
1990 resultout out nocopy varchar2 ) is
1991 --
1992 L_CONTRACT_ID number;
1993 x_return_status varchar2(1);
1994 --
1995 begin
1996 mo_global.init('OKC');
1997 --
1998 -- RUN mode - normal process execution
1999 --
2000 if (funcmode = 'RUN') then
2001 --
2002 empty_mess( itemtype,
2003 itemkey );
2004 L_CONTRACT_ID := wf_engine.GetItemAttrNumber(
2005 itemtype => itemtype,
2006 itemkey => itemkey,
2007 aname => 'CONTRACT_ID');
2008 OKC_CONTRACT_APPROVAL_PUB.activate_template(
2009 p_contract_id => L_CONTRACT_ID,
2010 x_return_status => x_return_status);
2011 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
2012 then
2013 --
2014 resultout := 'COMPLETE:T';
2015 return;
2016 --
2017 else
2018 --
2019 load_mess( itemtype,
2020 itemkey );
2021 resultout := 'COMPLETE:F';
2022 return;
2023 --
2024 end if;
2025 end if;
2026 --
2027 -- CANCEL mode
2028 --
2029 if (funcmode = 'CANCEL') then
2030 --
2031 resultout := 'COMPLETE:';
2032 return;
2033 --
2034 end if;
2035 --
2036 -- TIMEOUT mode
2037 --
2038 if (funcmode = 'TIMEOUT') then
2039 --
2040 resultout := 'COMPLETE:';
2041 return;
2042 --
2043 end if;
2044 exception
2045 when others then
2046 wf_core.context('OKC_WF_K_APPROVE',
2047 'MAKE_ACTIVE',
2048 itemtype,
2049 itemkey,
2050 to_char(actid),
2051 funcmode);
2052 raise;
2053 end Make_Active;
2054
2055 -- Procedure Name : updt_quote_from_k
2056 -- Description :
2057 -- Business Rules :
2058 -- Parameters :
2059 -- Version : 1.0
2060 -- End of comments
2061
2062 procedure updt_quote_from_k(itemtype in varchar2,
2063 itemkey in varchar2,
2064 actid in number,
2065 funcmode in varchar2,
2066 resultout out nocopy varchar2 ) is
2067 --
2068 l_contract_id number;
2069 x_return_status varchar2(30);
2070 x_msg_count number;
2071 x_msg_data varchar2(4000);
2072 l_api_version NUMBER :=1.0 ;
2073 l_session_id number;
2074 CURSOR csr_session_id IS
2075 SELECT userenv('sessionid')
2076 FROM dual;
2077 --
2078 begin
2079 mo_global.init('OKC');
2080 --
2081 -- RUN mode - normal process execution
2082 --
2083 if (funcmode = 'RUN') then
2084 --
2085 empty_mess( itemtype,
2086 itemkey );
2087 --
2088 OPEN csr_session_id;
2089 FETCH csr_session_id INTO l_session_id;
2090 CLOSE csr_session_id;
2091
2092 wf_engine.SetItemAttrNumber (
2093 itemtype => itemtype,
2094 itemkey=> itemkey,
2095 aname => 'QUOTE_SESSION_ID',
2096 avalue=> l_session_id);
2097 --
2098 l_contract_id := wf_engine.GetItemAttrNumber(
2099 itemtype => itemtype,
2100 itemkey => itemkey,
2101 aname => 'CONTRACT_ID');
2102
2103 OKC_OC_INT_PUB.update_quote_from_k(
2104 p_api_version => l_api_version
2105 ,p_commit => OKC_API.G_TRUE
2106 ,p_quote_id => NULL
2107 ,p_contract_id => l_contract_id
2108 ,p_trace_mode => NULL
2109 ,x_return_status => x_return_status
2110 ,x_msg_count => x_msg_count
2111 ,x_msg_data => x_msg_data
2112 );
2113
2114 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
2115 then
2116 --
2117 resultout := 'COMPLETE:T';
2118 return;
2119 --
2120 else
2121 --
2122 load_mess( itemtype,
2123 itemkey );
2124 resultout := 'COMPLETE:F';
2125 return;
2126 --
2127 end if;
2128
2129 end if; -- run mode
2130 --
2131 -- CANCEL mode
2132 --
2133 if (funcmode = 'CANCEL') then
2134 --
2135 resultout := 'COMPLETE:';
2136 return;
2137 --
2138 end if;
2139 --
2140 -- TIMEOUT mode
2141 --
2142 if (funcmode = 'TIMEOUT') then
2143 --
2144 resultout := 'COMPLETE:';
2145 return;
2146 --
2147 end if;
2148 exception
2149 when others then
2150 wf_core.context('OKC_WF_K_APPROVE',
2151 'updt_quote_from_k',
2152 itemtype,
2153 itemkey,
2154 to_char(actid),
2155 funcmode);
2156 raise;
2157 end updt_quote_from_k;
2158
2159
2160 -- Start of comments
2161 -- Procedure Name : invalid_approver
2162 -- Description : Procedure to update okc_k_process in case of invalid approver in profile option.
2163 -- Business Rules :
2164 -- Parameters :
2165 -- Version : 1.0
2166 -- End of comments
2167
2168
2169 PROCEDURE invalid_approver(itemtype IN VARCHAR2,
2170 itemkey IN VARCHAR2,
2171 actid IN NUMBER,
2172 funcmode IN VARCHAR2,
2173 resultout OUT NOCOPY VARCHAR2 )
2174 IS
2175 CURSOR csr_process_id(p_contract_id IN NUMBER)
2176 IS
2177 SELECT id
2178 FROM okc_k_processes
2179 WHERE chr_id = p_contract_id
2180 /*Bug 3255018 AND pdf_id = (SELECT id */
2181 AND pdf_id in (SELECT id
2182 FROM OKC_PROCESS_DEFS_V
2183 WHERE usage = 'APPROVE'
2184 AND PDF_TYPE = 'WPS'
2185 AND WF_NAME = 'OKCAUKAP');
2186
2187 l_contract_id NUMBER;
2188 l_return_status VARCHAR2(30);
2189 l_msg_count NUMBER;
2190 l_msg_data VARCHAR2(4000);
2191 l_api_version NUMBER :=1.0 ;
2192 l_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2193 x_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2194 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE;
2195 l_process_id NUMBER;
2196 BEGIN
2197 mo_global.init('OKC');
2198 --
2199 -- RUN mode - normal process execution
2200 --
2201 IF (funcmode = 'RUN') THEN
2202 empty_mess( itemtype, itemkey );
2203
2204 l_contract_id := wf_engine.GetItemAttrNumber(
2205 itemtype => itemtype,
2206 itemkey => itemkey,
2207 aname => 'CONTRACT_ID');
2208
2209 OPEN csr_process_id(l_contract_id);
2210 FETCH csr_process_id INTO l_process_id;
2211 CLOSE csr_process_id;
2212
2213 l_cpsv_rec.id := l_process_id;
2214 l_cpsv_rec.in_process_yn := 'E';
2215
2216 Okc_Contract_Pub.update_contract_process(
2217 p_api_version => l_api_version,
2218 p_init_msg_list => l_init_msg_list,
2219 x_return_status => l_return_status,
2220 x_msg_count => l_msg_count,
2221 x_msg_data => l_msg_data,
2222 p_cpsv_rec => l_cpsv_rec,
2223 x_cpsv_rec => x_cpsv_rec);
2224
2225 IF (l_return_status = Okc_Api.G_RET_STS_SUCCESS) THEN
2226 resultout := 'COMPLETE:T';
2227 RETURN;
2228 ELSE
2229 load_mess( itemtype, itemkey );
2230 resultout := 'COMPLETE:F';
2231 RETURN;
2232 END IF;
2233
2234 END IF; -- run mode
2235
2236 --
2237 -- CANCEL mode
2238 --
2239 IF (funcmode = 'CANCEL') THEN
2240 --
2241 resultout := 'COMPLETE:';
2242 RETURN;
2243 --
2244 END IF;
2245 --
2246 -- TIMEOUT mode
2247 --
2248 IF (funcmode = 'TIMEOUT') THEN
2249 --
2250 resultout := 'COMPLETE:';
2251 RETURN;
2252 --
2253 END IF;
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 wf_core.context('OKC_WF_K_APPROVE',
2257 'invalid_approver',
2258 itemtype,
2259 itemkey,
2260 TO_CHAR(actid),
2261 funcmode);
2262 RAISE;
2263 END invalid_approver;
2264
2265
2266 -- Start of comments
2267 --
2268 -- Procedure Name : update_invalid_approver
2269 -- Description : update_invalid_approver error code in IN_PROCESS_YN field of OKC_K_PROCESSES
2270 -- Business Rules :
2271 -- Parameters :
2272 -- Version : 1.0
2273 -- End of comments
2274
2275 PROCEDURE update_invalid_approver(itemtype IN VARCHAR2,
2276 itemkey IN VARCHAR2,
2277 actid IN NUMBER,
2278 funcmode IN VARCHAR2,
2279 resultout OUT NOCOPY VARCHAR2 )
2280 IS
2281 CURSOR csr_process_id(p_contract_id IN NUMBER)
2282 IS
2283 SELECT id, in_process_yn
2284 FROM okc_k_processes
2285 WHERE chr_id = p_contract_id
2286 /*Bug 3255018 AND pdf_id = (SELECT id */
2287 AND pdf_id in (SELECT id
2288 FROM OKC_PROCESS_DEFS_V
2289 WHERE usage = 'APPROVE'
2290 AND PDF_TYPE = 'WPS'
2291 AND WF_NAME = 'OKCAUKAP');
2292
2293 l_contract_id NUMBER;
2294 l_return_status VARCHAR2(30);
2295 l_msg_count NUMBER;
2296 l_msg_data VARCHAR2(4000);
2297 l_api_version NUMBER :=1.0 ;
2298 l_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2299 x_cpsv_rec Okc_Contract_Pub.cpsv_rec_type;
2300 l_init_msg_list VARCHAR2(1) := Okc_Api.G_FALSE;
2301 l_process_id NUMBER;
2302 l_in_process_yn VARCHAR2(1);
2303 BEGIN
2304 --
2305 -- RUN mode - normal process execution
2306 --
2307 IF (funcmode = 'RUN') THEN
2308 empty_mess( itemtype, itemkey );
2309
2310 l_contract_id := wf_engine.GetItemAttrNumber(
2311 itemtype => itemtype,
2312 itemkey => itemkey,
2313 aname => 'CONTRACT_ID');
2314
2315 OPEN csr_process_id(l_contract_id);
2316 FETCH csr_process_id INTO l_process_id, l_in_process_yn;
2317 CLOSE csr_process_id;
2318
2319 IF l_in_process_yn = 'E' THEN
2320 l_cpsv_rec.id := l_process_id;
2321 l_cpsv_rec.in_process_yn := NULL;
2322
2323 Okc_Contract_Pub.update_contract_process(
2324 p_api_version => l_api_version,
2325 p_init_msg_list => l_init_msg_list,
2326 x_return_status => l_return_status,
2327 x_msg_count => l_msg_count,
2328 x_msg_data => l_msg_data,
2329 p_cpsv_rec => l_cpsv_rec,
2330 x_cpsv_rec => x_cpsv_rec);
2331
2332 IF (l_return_status = Okc_Api.G_RET_STS_SUCCESS) THEN
2333 resultout := 'COMPLETE:T';
2334 RETURN;
2335 ELSE
2336 load_mess( itemtype, itemkey );
2337 resultout := 'COMPLETE:F';
2338 RETURN;
2339 END IF;
2340 END IF;
2341 END IF; -- run mode
2342
2343 --
2344 -- CANCEL mode
2345 --
2346 IF (funcmode = 'CANCEL') THEN
2347 --
2348 resultout := 'COMPLETE:';
2349 RETURN;
2350 --
2351 END IF;
2352 --
2353 -- TIMEOUT mode
2354 --
2355 IF (funcmode = 'TIMEOUT') THEN
2356 --
2357 resultout := 'COMPLETE:';
2358 RETURN;
2359 --
2360 END IF;
2361 EXCEPTION
2362 WHEN OTHERS THEN
2363 wf_core.context('Okc_Wf_K_Approve',
2364 'update_invalid_approver',
2365 itemtype,
2366 itemkey,
2367 TO_CHAR(actid),
2368 funcmode);
2369 RAISE;
2370 END update_invalid_approver;
2371
2372 end OKS_WF_K_APPROVE;
2373
2374