[Home] [Help]
PACKAGE BODY: APPS.OKC_WF_CHK_APPROVE
Source
1 package body OKC_WF_CHK_APPROVE as
2 /* $Header: OKCWCHKB.pls 120.0 2005/05/26 09:57:10 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ----------------------------------------------------------------------------
7 --
8 -- C U S T O M I Z E select_next
9 --
10 ----------------------------------------------------------------------------
11 -- Start of comments
12 --
13 -- Procedure Name : select_next
14 -- Description :
15 -- Business Rules :
16 -- Parameters :
17 -- Version : 1.0
18 -- End of comments
19
20 procedure select_next(itemtype in varchar2 default NULL,
21 itemkey in varchar2 default NULL,
22 p_role_type in varchar2,
23 p_current in varchar2 default NULL,
24 x_role out nocopy varchar2,
25 x_name out nocopy varchar2
26 ) is
27 --
28 -- Next Approver Cursor
29 --
30 -- Bug 1563675: Modified next_approver_csr for performance problem similar
31 -- to the way that the contract approval cursor was modified
32 --
33 cursor Next_Approver_csr is
34 select role, name
35 from -- here should be your view of structure (num,role,name)
36 ------------------------------------------------------
37 (select 1 num, FND_PROFILE.VALUE('OKC_CR_APPROVER') role,
38 NVL(PER.FULL_NAME, USR.USER_NAME) name
39 from FND_USER USR, PER_PEOPLE_F PER
40 where USR.USER_NAME = FND_PROFILE.VALUE('OKC_CR_APPROVER')
41 and USR.EMPLOYEE_ID = PER.PERSON_ID(+)
42 and trunc(sysdate) between nvl(per.effective_start_date, trunc(sysdate)) and
43 nvl(per.effective_end_date, trunc(sysdate))
44 )
45 ------------------------------------------------------
46 where p_current is NULL
47 order by num;
48 --
49 -- Next Informed Cursor
50 --
51 cursor Next_Informed_csr is
52 select role, name
53 from -- here should be your view of structure (num,role,name)
54 ------------------------------------------------------
55 (select 1 num, FND_PROFILE.VALUE('OKC_CR_APPROVER') role,
56 NVL(PER.FULL_NAME, USR.USER_NAME) name
57 from FND_USER USR, PER_PEOPLE_F PER
58 where USR.USER_NAME = FND_PROFILE.VALUE('OKC_CR_APPROVER')
59 and USR.EMPLOYEE_ID = PER.PERSON_ID(+)
60 and trunc(sysdate) between nvl(per.effective_start_date, trunc(sysdate)) and
61 nvl(per.effective_end_date, trunc(sysdate))
62 )
63 ------------------------------------------------------
64 where p_current is NULL
65 order by num;
66 begin
67 if (p_role_type = 'ADMINISTRATOR') then
68 x_role := wf_engine.GetItemAttrText(
69 itemtype => itemtype,
70 itemkey => itemkey,
71 aname => 'INITIATOR_NAME');
72 x_name := wf_engine.GetItemAttrText(
73 itemtype => itemtype,
74 itemkey => itemkey,
75 aname => 'INITIATOR_DISPLAY_NAME');
76 elsif (p_role_type = 'SIGNATORY') then
77 x_role := wf_engine.GetItemAttrText(
78 itemtype => itemtype,
79 itemkey => itemkey,
80 aname => 'INITIATOR_NAME');
81 x_name := wf_engine.GetItemAttrText(
82 itemtype => itemtype,
83 itemkey => itemkey,
84 aname => 'INITIATOR_DISPLAY_NAME');
85 elsif (p_role_type = 'APPROVER') then
86 open Next_Approver_csr;
87 fetch Next_Approver_csr into x_role, x_name;
88 close Next_Approver_csr;
89 elsif (p_role_type = 'INFORMED') then
90 open Next_Informed_csr;
91 fetch Next_Informed_csr into x_role, x_name;
92 close Next_Informed_csr;
93 end if;
94 end select_next;
95 ----------------------------------------------------------------------------
96 --
97 -- You can stop customization here
98 --
99 ----------------------------------------------------------------------------
100
101 -- Start of comments
102 --
103 -- Procedure Name : Selector
104 -- Description : Selector/Callback function - no need to customize
105 -- Business Rules :
106 -- Parameters :
107 -- Version : 1.0
108 -- End of comments
109
110 procedure Selector ( item_type in varchar2,
111 item_key in varchar2,
112 activity_id in number,
113 command in varchar2,
114 resultout out nocopy varchar2 ) is
115 -- local declarations
116 begin
117 resultout := ''; -- return value for other possible modes
118 --
119 -- RUN mode - normal process execution
120 --
121 if (command = 'RUN') then
122 --
123 -- Return process to run
124 --
125 resultout := 'CHK_APPROVAL_PROCESS';
126 return;
127 end if;
128
129 --
130 -- SET_CTX mode - set context for new DB session
131 --
132 if (command = 'SET_CTX') then
133 OKC_CHANGE_CONTRACT_PUB.wf_copy_env(
134 p_item_type => item_type,
135 p_item_key => item_key);
136 return;
137 end if;
138
139 --
140 -- TEST_CTX mode - test context
141 --
142 if (command = 'TEST_CTX') then
143 -- test code
144 resultout := 'TRUE';
145 return;
146 end if;
147
148 exception
149 when others then
150 wf_core.context('OKC_WF_CHK_APPROVE',
151 'SELECTOR',
152 item_type,
153 item_key,
154 to_char(activity_id),
155 command);
156 raise;
157 end Selector;
158
159 -- Start of comments
160 --
161 -- Procedure Name : Initialize
162 -- Description : Initialization of attributes that were not initialized by k_start API
163 -- Business Rules :
164 -- Parameters :
165 -- Version : 1.0
166 -- End of comments
167
168 procedure Initialize ( itemtype in varchar2,
169 itemkey in varchar2,
170 actid in number,
171 funcmode in varchar2,
172 resultout out nocopy varchar2 ) is
173 L_CONTRACT_ADMIN_USERNAME varchar2(30);
174 V_DUMMY varchar2(240);
175 l_id number;
176 L_INITIATOR_NAME varchar2(240);
177 L_INITIATOR_DISPLAY_NAME varchar2(240);
178 cursor C_INITIATOR_DISPLAY_NAME(P_USER_ID in number) is
179 select name,display_name
180 from wf_roles
181 where orig_system = 'FND_USR'
182 and orig_system_id=P_USER_ID
183 union all
184 select
185 USR.USER_NAME name,
186 PER.FULL_NAME display_name
187 from
188 PER_PEOPLE_F PER,
189 FND_USER USR
190 where trunc(SYSDATE) between PER.EFFECTIVE_START_DATE
191 and PER.EFFECTIVE_END_DATE
192 and PER.PERSON_ID = USR.EMPLOYEE_ID
193 and USR.USER_ID = P_USER_ID
194 and not exists (select '1'
195 from wf_roles
196 where orig_system = 'FND_USR'
197 and orig_system_id=P_USER_ID)
198 ;
199
200 begin
201 --
202 -- RUN mode - normal process execution
203 --
204 if (funcmode = 'RUN') then
205 --
206 -- Initiator/Initial
207 --
208 l_id := wf_engine.GetItemAttrNumber(
209 itemtype => itemtype,
210 itemkey => itemkey,
211 aname => 'USER_ID');
212 open C_INITIATOR_DISPLAY_NAME(l_id);
213 fetch C_INITIATOR_DISPLAY_NAME into L_INITIATOR_NAME,L_INITIATOR_DISPLAY_NAME;
214 close C_INITIATOR_DISPLAY_NAME;
215 wf_engine.SetItemAttrText (
216 itemtype => itemtype,
217 itemkey => itemkey,
218 aname => 'INITIATOR_NAME',
219 avalue => L_INITIATOR_NAME);
220 wf_engine.SetItemAttrText (
221 itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'INITIATOR_DISPLAY_NAME',
224 avalue => L_INITIATOR_DISPLAY_NAME);
225 --
226 -- Administrator U/name
227 --
228 select_next(itemtype => itemtype,
229 itemkey => itemkey,
230 p_role_type => 'ADMINISTRATOR',
231 x_role => L_CONTRACT_ADMIN_USERNAME,
232 x_name => V_DUMMY);
233 wf_engine.SetItemAttrText (
234 itemtype => itemtype,
235 itemkey => itemkey,
236 aname => 'CONTRACT_ADMIN_USERNAME',
237 avalue => L_CONTRACT_ADMIN_USERNAME);
238 --
239 resultout := 'COMPLETE:';
240 return;
241 --
242 end if;
243 --
244 -- CANCEL mode
245 --
246 if (funcmode = 'CANCEL') then
247 --
248 resultout := 'COMPLETE:';
249 return;
250 --
251 end if;
252 --
253 -- TIMEOUT mode
254 --
255 if (funcmode = 'TIMEOUT') then
256 --
257 resultout := 'COMPLETE:';
258 return;
259 --
260 end if;
261 exception
262 when others then
263 wf_core.context('OKC_WF_CHK_APPROVE',
264 'INITIALIZE',
265 itemtype,
266 itemkey,
267 to_char(actid),
268 funcmode);
269 raise;
270 end Initialize;
271
272 -- Start of comments
273 --
274 -- Procedure Name : Select_Approver
275 -- Description : Customize using your approval chain
276 -- Business Rules :
277 -- Parameters :
278 -- Version : 1.0
279 -- End of comments
280
281 procedure Select_Approver( itemtype in varchar2,
282 itemkey in varchar2,
283 actid in number,
284 funcmode in varchar2,
285 resultout out nocopy varchar2 ) is
286 L_INITIATOR_DISPLAY_NAME varchar2(240);
287 L_NEXT_PERFORMER_USERNAME varchar2(30);
288 L_N_PERFORMER_DISPLAY_NAME varchar2(240);
289 begin
290 --
291 -- RUN mode - normal process execution
292 --
293 if (funcmode = 'RUN') then
294 L_NEXT_PERFORMER_USERNAME := wf_engine.GetItemAttrText(
295 itemtype => itemtype,
296 itemkey => itemkey,
297 aname => 'NEXT_PERFORMER_USERNAME');
298 if (L_NEXT_PERFORMER_USERNAME is NULL)
299 then -- just start
300 L_INITIATOR_DISPLAY_NAME := wf_engine.GetItemAttrText(
301 itemtype => itemtype,
302 itemkey => itemkey,
303 aname => 'INITIATOR_DISPLAY_NAME');
304 select_next(itemtype => itemtype,
305 itemkey => itemkey,
306 p_role_type => 'APPROVER',
307 p_current => NULL,
308 x_role => L_NEXT_PERFORMER_USERNAME,
309 x_name => L_N_PERFORMER_DISPLAY_NAME);
310 -- just for common situation if no approvers at all
311 -- then Change Contract is considered as approved by initiator
312 if (L_NEXT_PERFORMER_USERNAME is NULL) then
313 wf_engine.SetItemAttrText (
314 itemtype => itemtype,
315 itemkey => itemkey,
316 aname => 'N_PERFORMER_DISPLAY_NAME',
317 avalue => L_INITIATOR_DISPLAY_NAME);
318 resultout := 'COMPLETE:F';
319 else
320 wf_engine.SetItemAttrText (
321 itemtype => itemtype,
322 itemkey => itemkey,
323 aname => 'P_PERFORMER_DISPLAY_NAME',
324 avalue => L_INITIATOR_DISPLAY_NAME);
325 wf_engine.SetItemAttrText (
326 itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'NEXT_PERFORMER_USERNAME',
329 avalue => L_NEXT_PERFORMER_USERNAME);
330 wf_engine.SetItemAttrText(itemtype => itemtype,
331 itemkey => itemkey,
332 aname => 'N_PERFORMER_DISPLAY_NAME',
333 avalue => L_N_PERFORMER_DISPLAY_NAME);
334 resultout := 'COMPLETE:T';
335 end if;
336 return;
337 else
338 wf_engine.SetItemAttrText
339 (
340 itemtype => itemtype,
341 itemkey => itemkey,
342 aname => 'P_PERFORMER_DISPLAY_NAME',
343 avalue => wf_engine.GetItemAttrText(
344 itemtype => itemtype,
345 itemkey => itemkey,
346 aname => 'N_PERFORMER_DISPLAY_NAME')
347 );
348 select_next(itemtype => itemtype,
349 itemkey => itemkey,
350 p_role_type => 'APPROVER',
351 p_current => L_NEXT_PERFORMER_USERNAME,
352 x_role => L_NEXT_PERFORMER_USERNAME,
353 x_name => L_N_PERFORMER_DISPLAY_NAME);
354 if (L_NEXT_PERFORMER_USERNAME is NULL) then
355 wf_engine.SetItemAttrText (
356 itemtype => itemtype,
357 itemkey => itemkey,
358 aname => 'NEXT_PERFORMER_USERNAME',
359 avalue => NULL);
360 resultout := 'COMPLETE:F';
361 else
362 wf_engine.SetItemAttrText (
363 itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'NEXT_PERFORMER_USERNAME',
366 avalue => L_NEXT_PERFORMER_USERNAME);
367 wf_engine.SetItemAttrText(itemtype => itemtype,
368 itemkey => itemkey,
369 aname => 'N_PERFORMER_DISPLAY_NAME',
370 avalue => L_N_PERFORMER_DISPLAY_NAME);
371 resultout := 'COMPLETE:T';
372 end if;
373 return;
374 --
375 end if;
376 end if;
377 --
378 -- CANCEL mode
379 --
380 if (funcmode = 'CANCEL') then
381 --
382 resultout := 'COMPLETE:';
383 return;
384 --
385 end if;
386 --
387 -- TIMEOUT mode
388 --
389 if (funcmode = 'TIMEOUT') then
390 --
391 resultout := 'COMPLETE:';
392 return;
393 --
394 end if;
395 exception
396 when others then
397 wf_core.context('OKC_WF_CHK_APPROVE',
398 'SELECT_APPROVER',
399 itemtype,
400 itemkey,
401 to_char(actid),
402 funcmode);
403 raise;
404 end Select_Approver;
405
406 -- Start of comments
407 --
408 -- Procedure Name : Select_Informed
409 -- Description : Customize using your To be Informed chain
410 -- Business Rules :
411 -- Parameters :
412 -- Version : 1.0
413 -- End of comments
414
415 procedure Select_Informed( itemtype in varchar2,
416 itemkey in varchar2,
417 actid in number,
418 funcmode in varchar2,
419 resultout out nocopy varchar2) is
420 L_NEXT_INFORMED_USERNAME varchar2(30);
421 v_dummy varchar2(240);
422 begin
423 --
424 -- RUN mode - normal process execution
425 --
426 if (funcmode = 'RUN') then
427 L_NEXT_INFORMED_USERNAME := wf_engine.GetItemAttrText(
428 itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'NEXT_INFORMED_USERNAME');
431 select_next(itemtype => itemtype,
432 itemkey => itemkey,
433 p_role_type => 'INFORMED',
434 p_current => L_NEXT_INFORMED_USERNAME,
435 x_role => L_NEXT_INFORMED_USERNAME,
436 x_name => V_DUMMY);
437 wf_engine.SetItemAttrText (
438 itemtype => itemtype,
439 itemkey => itemkey,
440 aname => 'NEXT_INFORMED_USERNAME',
441 avalue => L_NEXT_INFORMED_USERNAME);
442 if (L_NEXT_INFORMED_USERNAME is NULL) then
443 resultout := 'COMPLETE:F';
444 else
445 resultout := 'COMPLETE:T';
446 end if;
447 return;
448 end if;
449 --
450 -- CANCEL mode
451 --
452 if (funcmode = 'CANCEL') then
453 --
454 resultout := 'COMPLETE:';
455 return;
456 --
457 end if;
458 --
459 -- TIMEOUT mode
460 --
461 if (funcmode = 'TIMEOUT') then
462 --
463 resultout := 'COMPLETE:';
464 return;
465 --
466 end if;
467 exception
468 when others then
469 wf_core.context('OKC_WF_CHK_APPROVE',
470 'SELECT_INFORMED',
471 itemtype,
472 itemkey,
473 to_char(actid),
474 funcmode);
475 raise;
476 end Select_Informed;
477
478 -- Start of comments
479 --
480 -- Procedure Name : empty_mess
481 -- Description : Private procedure to empty message attributes
482 -- Business Rules :
483 -- Parameters :
484 -- Version : 1.0
485 -- End of comments
486
487 procedure empty_mess( itemtype in varchar2,
488 itemkey in varchar2) is
489 i integer;
490 begin
491 FOR I IN 1..9 LOOP
492 wf_engine.SetItemAttrText (itemtype => itemtype,
493 itemkey => itemkey,
494 aname => 'MESSAGE'||i,
495 avalue => '');
496 END LOOP;
497 end;
498
499 -- Start of comments
500 --
501 -- Procedure Name : load_mess
502 -- Description : Private procedure to load messages into attributes
503 -- Business Rules :
504 -- Parameters :
505 -- Version : 1.0
506 -- End of comments
507
508 procedure load_mess( itemtype in varchar2,
509 itemkey in varchar2) is
510 i integer;
511 j integer;
512 begin
513 j := NVL(FND_MSG_PUB.Count_Msg,0);
514 if (j=0) then return; end if;
515 if (j>9) then j:=9; end if;
516 FOR I IN 1..J LOOP
517 wf_engine.SetItemAttrText (itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'MESSAGE'||i,
520 avalue => FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
521 END LOOP;
522 end;
523
524 -- Start of comments
525 --
526 -- Procedure Name : Record_Approved
527 -- Description : Does not need customization
528 -- Business Rules :
529 -- Parameters :
530 -- Version : 1.0
531 -- End of comments
532
533 procedure Record_Approved( itemtype in varchar2,
534 itemkey in varchar2,
535 actid in number,
536 funcmode in varchar2,
537 resultout out nocopy varchar2 ) is
538 L_CHANGE_REQUEST_ID number;
539 x_return_status varchar2(1);
540 begin
541 --
542 -- RUN mode - normal process execution
543 --
544 if (funcmode = 'RUN') then
545 --
546 empty_mess( itemtype,
547 itemkey );
548 L_CHANGE_REQUEST_ID := wf_engine.GetItemAttrNumber(
549 itemtype => itemtype,
550 itemkey => itemkey,
551 aname => 'CHANGE_REQUEST_ID');
552 OKC_CHANGE_CONTRACT_PUB.change_request_approved(
553 p_change_request_id => L_CHANGE_REQUEST_ID,
554 x_return_status => x_return_status);
555 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
556 then
557 resultout := 'COMPLETE:T';
558 return;
559 --
560 else
561 --
562 load_mess( itemtype,
563 itemkey );
564 resultout := 'COMPLETE:F';
565 return;
566 --
567 end if;
568 end if;
569 --
570 -- CANCEL mode
571 --
572 if (funcmode = 'CANCEL') then
573 --
574 resultout := 'COMPLETE:';
575 return;
576 --
577 end if;
578 --
579 -- TIMEOUT mode
580 --
581 if (funcmode = 'TIMEOUT') then
582 --
583 resultout := 'COMPLETE:';
584 return;
585 --
586 end if;
587 exception
588 when others then
589 wf_core.context('OKC_WF_CHK_APPROVE',
590 'RECORD_APPROVED',
591 itemtype,
592 itemkey,
593 to_char(actid),
594 funcmode);
595 raise;
596 end Record_Approved;
597
598 -- Start of comments
599 --
600 -- Procedure Name : Record_Rejected
601 -- Description : Does not need customization
602 -- Business Rules :
603 -- Parameters :
604 -- Version : 1.0
605 -- End of comments
606
607 procedure Record_Rejected( itemtype in varchar2,
608 itemkey in varchar2,
609 actid in number,
610 funcmode in varchar2,
611 resultout out nocopy varchar2 ) is
612 L_CHANGE_REQUEST_ID number;
613 x_return_status varchar2(1);
614 begin
615 --
616 -- RUN mode - normal process execution
617 --
618 if (funcmode = 'RUN') then
619 --
620 empty_mess( itemtype,
621 itemkey );
622 L_CHANGE_REQUEST_ID := wf_engine.GetItemAttrNumber(
623 itemtype => itemtype,
624 itemkey => itemkey,
625 aname => 'CHANGE_REQUEST_ID');
626 OKC_CHANGE_CONTRACT_PUB.change_request_rejected(
627 p_change_request_id => L_CHANGE_REQUEST_ID,
628 x_return_status => x_return_status);
629 if (x_return_status = OKC_API.G_RET_STS_SUCCESS)
630 then
631 resultout := 'COMPLETE:T';
632 return;
633 --
634 else
635 --
636 load_mess( itemtype,
637 itemkey );
638 resultout := 'COMPLETE:F';
639 return;
640 --
641 end if;
642 end if;
643 --
644 -- CANCEL mode
645 --
646 if (funcmode = 'CANCEL') then
647 --
648 resultout := 'COMPLETE:';
649 return;
650 --
651 end if;
652 --
653 -- TIMEOUT mode
654 --
655 if (funcmode = 'TIMEOUT') then
656 --
657 resultout := 'COMPLETE:';
658 return;
659 --
660 end if;
661 exception
662 when others then
663 wf_core.context('OKC_WF_CHK_APPROVE',
664 'RECORD_REJECTED',
665 itemtype,
666 itemkey,
667 to_char(actid),
668 funcmode);
669 raise;
670 end Record_Rejected;
671 --
672 -- Start of comments
673 --
674 -- Procedure Name : note_filled
675 -- Description : note mandatory if reject
676 -- Business Rules :
677 -- Parameters :
678 -- Version : 1.0
679 -- End of comments
680
681 procedure note_filled( itemtype in varchar2,
682 itemkey in varchar2,
683 actid in number,
684 funcmode in varchar2,
685 resultout out nocopy varchar2 ) is
686 begin
687 --
688 -- RUN mode - normal process execution
689 --
690 if (funcmode = 'RUN') then
691 if (wf_engine.GetItemAttrText(itemtype,itemkey,'NOTE') is NULL) then
692 resultout := 'COMPLETE:F';
693 else
694 resultout := 'COMPLETE:T';
695 end if;
696 end if;
697 --
698 -- CANCEL mode
699 --
700 if (funcmode = 'CANCEL') then
701 --
702 resultout := 'COMPLETE:';
703 return;
704 --
705 end if;
706 --
707 -- TIMEOUT mode
708 --
709 if (funcmode = 'TIMEOUT') then
710 --
711 resultout := 'COMPLETE:';
712 return;
713 --
714 end if;
715 exception
716 when others then
717 wf_core.context('OKC_WF_CHK_APPROVE',
718 'NOTE_FILLED',
719 itemtype,
720 itemkey,
721 to_char(actid),
722 funcmode);
723 raise;
724 end note_filled;
725
726 end OKC_WF_CHK_APPROVE;