[Home] [Help]
PACKAGE BODY: APPS.OKE_CHG_REQUESTS_WF
Source
1 PACKAGE BODY OKE_CHG_REQUESTS_WF AS
2 /* $Header: OKEWCRQB.pls 120.0 2005/05/25 17:58:27 appldev noship $ */
3
4 --
5 -- Private Functions and Procedures
6 --
7 FUNCTION Emp_To_UserName ( Emp_ID NUMBER )
8 RETURN VARCHAR2 IS
9
10 CURSOR c IS
11 SELECT User_Name
12 FROM fnd_user
13 WHERE employee_id = Emp_ID;
14 UserName VARCHAR2(80);
15
16 BEGIN
17
18 OPEN c;
19 FETCH c INTO UserName;
20 CLOSE c;
21
22 RETURN ( UserName );
23
24 END Emp_To_UserName;
25
26
27 FUNCTION Default_Chg_Status ( StsTypeCode VARCHAR2 )
28 RETURN VARCHAR2 IS
29
30 CURSOR c IS
31 SELECT Chg_Status_Code
32 FROM oke_chg_statuses_b
33 WHERE Chg_Status_Type_Code = StsTypeCode
34 AND Default_Status = 'Y';
35 Status VARCHAR2(30);
36
37 BEGIN
38
39 OPEN c;
40 FETCH c INTO Status;
41 CLOSE c;
42
43 RETURN ( Status );
44
45 END Default_Chg_Status;
46
47 --
48 -- Public Functions and Procedures
49 --
50
51 --
52 -- Name : Initialize
53 -- Pre-reqs : Must be called from WF activity
54 -- Function : This procedure initializes the remaining of the item
55 -- attributes not set during launch
56 --
57 -- Parameters :
58 -- IN : ItemType
59 -- ItemKey
60 -- ActID
61 -- FuncMode
62 -- OUT : ResultOut
63 --
64 -- Returns : None
65 --
66 PROCEDURE Initialize
67 ( ItemType IN VARCHAR2
68 , ItemKey IN VARCHAR2
69 , ActID IN NUMBER
70 , FuncMode IN VARCHAR2
71 , ResultOut OUT NOCOPY VARCHAR2
72 ) IS
73
74 CURSOR sts ( C_Chg_Status_Code VARCHAR2 ) IS
75 SELECT chg_status_name
76 FROM oke_chg_statuses_vl
77 WHERE chg_status_code = C_Chg_Status_Code;
78
79 CURSOR lu
80 ( C_Lookup_Type VARCHAR2
81 , C_Lookup_Code VARCHAR2
82 , C_View_Appl_ID NUMBER ) IS
83 SELECT meaning
84 FROM fnd_lookup_values_vl
85 WHERE lookup_type = C_Lookup_Type
86 AND lookup_code = C_Lookup_Code
87 AND view_application_id = C_View_Appl_ID;
88
89 -- CURSOR kadmin ( C_Header_ID NUMBER ) IS
90 -- SELECT R.NAME
91 -- FROM oke_k_all_access_v A, wf_roles R
92 -- WHERE A.k_header_id = C_Header_ID
93 -- AND A.role_id = 701 /* Contract Administrator */
94 -- AND OKE_K_SECURITY_PKG.GET_ASSIGNMENT_DATE BETWEEN A.START_DATE_ACTIVE AND NVL(A.END_DATE_ACTIVE , OKE_K_SECURITY_PKG.GET_ASSIGNMENT_DATE + 1)
95 -- AND R.ORIG_SYSTEM='PER'
96 -- AND R.ORIG_SYSTEM_ID=A.PERSON_ID
97 -- ORDER BY DECODE( assignment_level , 'SITE' , 0 , 'OKE_PROGRAMS' , 1 , 2 ) DESC;
98
99 ChgReason VARCHAR2(80);
100 ChgType VARCHAR2(80);
101 NewStatus VARCHAR2(80);
102 OldStatus VARCHAR2(80);
103 AdminID NUMBER;
104 AdminName VARCHAR2(240);
105
106 BEGIN
107
108 IF ( FuncMode = 'RUN' ) THEN
109
110 OPEN sts ( WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'OLD_STATUS_CODE') );
111 FETCH sts INTO OldStatus;
112 CLOSE sts;
113
114 WF_ENGINE.SetItemAttrText( itemtype => ItemType
115 , itemkey => ItemKey
116 , aname => 'OLD_STATUS'
117 , avalue => OldStatus );
118
119 OPEN sts ( WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'NEW_STATUS_CODE') );
120 FETCH sts INTO NewStatus;
121 CLOSE sts;
122
123 WF_ENGINE.SetItemAttrText( itemtype => ItemType
124 , itemkey => ItemKey
125 , aname => 'NEW_STATUS'
126 , avalue => NewStatus );
127
128 OPEN lu ( 'CHANGE_TYPE'
129 , WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'CHG_TYPE_CODE')
130 , 777);
131 FETCH lu INTO ChgType;
132 CLOSE lu;
133
134 WF_ENGINE.SetItemAttrText( itemtype => ItemType
135 , itemkey => ItemKey
136 , aname => 'CHG_TYPE'
137 , avalue => ChgType );
138
139 OPEN lu ( 'CHANGE_REASON'
140 , WF_ENGINE.GetItemAttrText(ItemType , ItemKey , 'CHG_REASON_CODE')
141 , 777);
142 FETCH lu INTO ChgReason;
143 CLOSE lu;
144
145 WF_ENGINE.SetItemAttrText( itemtype => ItemType
146 , itemkey => ItemKey
147 , aname => 'CHG_REASON'
148 , avalue => ChgReason );
149
150 -- OPEN kadmin ( WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'K_HEADER_ID') );
151 -- FETCH kadmin INTO AdminName;
152 -- CLOSE kadmin;
153
154 AdminName := OKE_UTILS.Retrieve_WF_Role_Name(WF_ENGINE.GetItemAttrNumber(ItemType , ItemKey , 'K_HEADER_ID'),701);
155
156 -- AdminName := Emp_To_UserName( AdminID );
157
158 WF_ENGINE.SetItemAttrText( itemtype => ItemType
159 , itemkey => ItemKey
160 , aname => 'ADMINISTRATOR'
161 , avalue => AdminName );
162
163 ResultOut := 'COMPLETE:';
164 RETURN;
165
166 END IF;
167
168 IF ( FuncMode = 'CANCEL' ) THEN
169
170 ResultOut := '';
171 RETURN;
172
173 END IF;
174
175 IF ( FuncMode = 'TIMEOUT' ) THEN
176
177 ResultOut := '';
178 RETURN;
179
180 END IF;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 ResultOut := 'ERROR';
185 WF_Engine.SetItemAttrText
186 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
187 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
188 , 'INITIALIZE'
189 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
190 RAISE;
191
192 END Initialize;
193
194
195 --
196 -- Name : Select_Next_Approver
197 -- Pre-reqs : Must be called from WF activity
198 -- Function : This procedure determines the next approver for
199 -- the change request.
200 --
201 -- Parameters :
202 -- IN : ItemType
203 -- ItemKey
204 -- ActID
205 -- FuncMode
206 -- OUT : ResultOut
207 --
208 -- Returns : None
209 --
210 PROCEDURE Select_Next_Approver
211 ( ItemType IN VARCHAR2
212 , ItemKey IN VARCHAR2
213 , ActID IN NUMBER
214 , FuncMode IN VARCHAR2
215 , ResultOut OUT NOCOPY VARCHAR2
216 ) IS
217
218 LastAppr VARCHAR2(240) := NULL;
219 NextAppr VARCHAR2(240) := NULL;
220 Requestor VARCHAR2(240) := NULL;
221
222 BEGIN
223
224 IF ( FuncMode = 'RUN' ) THEN
225 --
226 -- Get Last Approver Information from Workflow
227 --
228 LastAppr := WF_Engine.GetItemAttrText
229 (ItemType , ItemKey , 'NEXT_APPROVER');
230
231 IF ( LastAppr IS NULL ) THEN
232
233 Requestor := WF_Engine.GetItemAttrText
234 (ItemType , ItemKey , 'REQUESTOR');
235 NextAppr := WF_Engine.GetItemAttrText
236 (ItemType , ItemKey , 'ADMINISTRATOR');
237
238 IF ( NextAppr = Requestor ) THEN
239 NextAppr := NULL;
240 ELSE
241 NextAppr := WF_Engine.GetItemAttrText
242 (ItemType , ItemKey , 'ADMINISTRATOR');
243 END IF;
244
245 ELSE
246
247 WF_Engine.SetItemAttrText
248 (ItemType , ItemKey , 'PREV_PERFORMER' , LastAppr);
249 NextAppr := NULL;
250
251 END IF;
252
253 IF ( NextAppr IS NULL ) THEN
254 ResultOut := 'COMPLETE:F';
255 ELSE
256 WF_Engine.SetItemAttrText
257 (ItemType , ItemKey , 'NEXT_APPROVER' , NextAppr);
258 ResultOut := 'COMPLETE:T';
259 END IF;
260
261 RETURN;
262
263 END IF;
264
265 IF ( FuncMode = 'CANCEL' ) THEN
266
267 ResultOut := 'COMPLETE:';
268 RETURN;
269
270 END IF;
271
272 IF ( FuncMode = 'TIMEOUT' ) THEN
273
274 ResultOut := 'COMPLETE:';
275 RETURN;
276
277 END IF;
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 ResultOut := 'ERROR:';
282 WF_Engine.SetItemAttrText
283 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
284 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
285 , 'SELECT_NEXT_APPROVER'
286 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
287 RAISE;
288
289 END Select_Next_Approver;
290
291
292 --
293 -- Name : Select_Next_Informed
294 -- Pre-reqs : Must be called from WF activity
295 -- Function : This procedure determines the next recipient of
296 -- workflow notifications.
297 --
298 -- Parameters :
299 -- IN : ItemType
300 -- ItemKey
301 -- ActID
302 -- FuncMode
303 -- OUT : ResultOut
304 --
305 -- Returns : None
306 --
307 PROCEDURE Select_Next_Informed
308 ( ItemType IN VARCHAR2
309 , ItemKey IN VARCHAR2
310 , ActID IN NUMBER
311 , FuncMode IN VARCHAR2
312 , ResultOut OUT NOCOPY VARCHAR2
313 ) IS
314
315 LastInformed VARCHAR2(240) := NULL;
316 NextInformed VARCHAR2(240) := NULL;
317
318 BEGIN
319
320 IF ( FuncMode = 'RUN' ) THEN
321
322 LastInformed := WF_Engine.GetItemAttrText
323 (ItemType , ItemKey , 'NEXT_INFORMED');
324
325 IF ( LastInformed IS NULL ) THEN
326
327 NextInformed := WF_Engine.GetItemAttrText
328 (ItemType , ItemKey , 'REQUESTOR');
329
330 ELSE
331
332 NextInformed := NULL;
333
334 END IF;
335
336 IF ( NextInformed IS NULL ) THEN
337 ResultOut := 'COMPLETE:F';
338 ELSE
339 WF_Engine.SetItemAttrText
340 (ItemType , ItemKey , 'NEXT_INFORMED' , NextInformed );
341 ResultOut := 'COMPLETE:T';
342 END IF;
343
344 END IF;
345
346 IF ( FuncMode = 'CANCEL' ) THEN
347
348 ResultOut := 'COMPLETE:';
349 RETURN;
350
351 END IF;
352
353 IF ( FuncMode = 'TIMEOUT' ) THEN
354
355 ResultOut := 'COMPLETE:';
356 RETURN;
357
358 END IF;
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 ResultOut := 'ERROR:';
363 WF_Engine.SetItemAttrText
364 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
365 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
366 , 'SELECT_NEXT_INFORMED'
367 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
368 RAISE;
369
370 END Select_Next_Informed;
371
372
373 --
374 -- Name : Rej_Note_Filled
375 -- Pre-reqs : Must be called from WF activity
376 -- Function : This procedure verifies that the note is filled if
377 -- the change request was rejected.
378 --
379 -- Parameters :
380 -- IN : ItemType
381 -- ItemKey
382 -- ActID
383 -- FuncMode
384 -- OUT : ResultOut
385 --
386 -- Returns : None
387 --
388 PROCEDURE Rej_Note_Filled
389 ( ItemType IN VARCHAR2
390 , ItemKey IN VARCHAR2
391 , ActID IN NUMBER
392 , FuncMode IN VARCHAR2
393 , ResultOut OUT NOCOPY VARCHAR2
394 ) IS
395
396 BEGIN
397
398 IF ( FuncMode = 'RUN' ) THEN
399
400 IF ( WF_Engine.GetItemAttrText(ItemType , ItemKey , 'WF_NOTE') IS NULL ) THEN
401 ResultOut := 'COMPLETE:F';
402 ELSE
403 ResultOut := 'COMPLETE:T';
404 END IF;
405
406 RETURN;
407
408 END IF;
409
410 IF ( FuncMode = 'CANCEL' ) THEN
411
412 ResultOut := 'COMPLETE:';
413 RETURN;
414
415 END IF;
416
417 IF ( FuncMode = 'TIMEOUT' ) THEN
418
419 ResultOut := 'COMPLETE:';
420 RETURN;
421
422 END IF;
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 ResultOut := 'ERROR:';
427 WF_Engine.SetItemAttrText
428 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
429 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
430 , 'REJ_NOTE_FILLED'
431 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
432 RAISE;
433
434 END Rej_Note_Filled;
435
436
437 --
438 -- Name : Impact_Funding
439 -- Pre-reqs : Must be called from WF activity
440 -- Function : This procedure checks whether the change request
441 -- impacts funding or not.
442 --
443 -- Parameters :
444 -- IN : ItemType
445 -- ItemKey
446 -- ActID
447 -- FuncMode
448 -- OUT : ResultOut
449 --
450 -- Returns : None
451 --
452 PROCEDURE Impact_Funding
453 ( ItemType IN VARCHAR2
454 , ItemKey IN VARCHAR2
455 , ActID IN NUMBER
456 , FuncMode IN VARCHAR2
457 , ResultOut OUT NOCOPY VARCHAR2
458 ) IS
459
460 BEGIN
461
462 IF ( FuncMode = 'RUN' ) THEN
463
464 IF ( WF_Engine.GetItemAttrText(ItemType , ItemKey , 'IMPACT_FUNDING_FLAG') = 'Y' ) THEN
465 ResultOut := 'COMPLETE:T';
466 ELSE
467 ResultOut := 'COMPLETE:F';
468 END IF;
469
470 RETURN;
471
472 END IF;
473
474 IF ( FuncMode = 'CANCEL' ) THEN
475
476 ResultOut := 'COMPLETE:';
477 RETURN;
478
479 END IF;
480
481 IF ( FuncMode = 'TIMEOUT' ) THEN
482
483 ResultOut := 'COMPLETE:';
484 RETURN;
485
486 END IF;
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 ResultOut := 'ERROR:';
491 WF_Engine.SetItemAttrText
492 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
493 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
494 , 'IMPACT_FUNDING'
495 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
496 RAISE;
497
498 END Impact_Funding;
499
500
501 --
502 -- Name : Set_Approved_Status
503 -- Pre-reqs : Must be called from WF activity
507 -- Parameters :
504 -- Function : This procedure sets the status of the change request
505 -- to Approved.
506 --
508 -- IN : ItemType
509 -- ItemKey
510 -- ActID
511 -- FuncMode
512 -- OUT : ResultOut
513 --
514 -- Returns : None
515 --
516 PROCEDURE Set_Approved_Status
517 ( ItemType IN VARCHAR2
518 , ItemKey IN VARCHAR2
519 , ActID IN NUMBER
520 , FuncMode IN VARCHAR2
521 , ResultOut OUT NOCOPY VARCHAR2
522 ) IS
523
524 L_Chg_Request_ID NUMBER;
525 L_Approved_Status VARCHAR2(30);
526
527 BEGIN
528 L_Approved_Status := Default_Chg_Status('APPROVED');
529
530 IF ( FuncMode = 'RUN' ) THEN
531
532 IF ( L_Approved_Status IS NULL ) THEN
533 RAISE NO_DATA_FOUND;
534 END IF;
535
536 L_Chg_Request_ID := WF_ENGINE.GetItemAttrNumber
537 ( itemtype => ItemType
538 , ItemKey => ItemKey
539 , AName => 'CHG_REQUEST_ID' );
540
541 UPDATE oke_chg_requests
542 SET chg_status_code = L_Approved_Status
543 , last_update_date = sysdate
544 WHERE chg_request_id = L_Chg_Request_ID;
545
546 ResultOut := 'COMPLETE:';
547 RETURN;
548
549 END IF;
550
551 IF ( FuncMode = 'CANCEL' ) THEN
552
553 ResultOut := '';
554 RETURN;
555
556 END IF;
557
558 IF ( FuncMode = 'TIMEOUT' ) THEN
559
560 ResultOut := '';
561 RETURN;
562
563 END IF;
564
565 EXCEPTION
566 WHEN NO_DATA_FOUND THEN
567 ResultOut := 'ERROR:';
568 WF_Engine.SetItemAttrText
569 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT'
570 , AValue => FND_MESSAGE.Get_String('OKE' , 'OKE_CHGREQ_NODEF_APPR_STS') );
571 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
572 , 'SET_APPROVED_STATUS'
573 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
574 RAISE;
575
576 WHEN OTHERS THEN
577 ResultOut := 'ERROR:';
578 WF_Engine.SetItemAttrText
579 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
580 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
581 , 'SET_APPROVED_STATUS'
582 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
583 RAISE;
584
585 END Set_Approved_Status;
586
587
588 --
589 -- Name : Set_Rejected_Status
590 -- Pre-reqs : Must be called from WF activity
591 -- Function : This procedure sets the status of the change request
592 -- to Rejected.
593 --
594 -- Parameters :
595 -- IN : ItemType
596 -- ItemKey
597 -- ActID
598 -- FuncMode
599 -- OUT : ResultOut
600 --
601 -- Returns : None
602 --
603 PROCEDURE Set_Rejected_Status
604 ( ItemType IN VARCHAR2
605 , ItemKey IN VARCHAR2
606 , ActID IN NUMBER
607 , FuncMode IN VARCHAR2
608 , ResultOut OUT NOCOPY VARCHAR2
609 ) IS
610
611 L_Chg_Request_ID NUMBER;
612 L_Rejected_Status VARCHAR2(30);
613
614 BEGIN
615 L_Rejected_Status := Default_Chg_Status('REJECTED');
616
617 IF ( FuncMode = 'RUN' ) THEN
618
619 IF ( L_Rejected_Status IS NULL ) THEN
620 RAISE NO_DATA_FOUND;
621 END IF;
622
623 L_Chg_Request_ID := WF_ENGINE.GetItemAttrNumber
624 ( itemtype => ItemType
625 , ItemKey => ItemKey
626 , AName => 'CHG_REQUEST_ID' );
627
628 UPDATE oke_chg_requests
629 SET chg_status_code = L_Rejected_Status
630 , last_update_date = sysdate
631 WHERE chg_request_id = L_Chg_Request_ID;
632
633 ResultOut := 'COMPLETE:';
634 RETURN;
635
636 END IF;
637
638 IF ( FuncMode = 'CANCEL' ) THEN
639
640 ResultOut := '';
641 RETURN;
642
643 END IF;
644
645 IF ( FuncMode = 'TIMEOUT' ) THEN
646
647 ResultOut := '';
648 RETURN;
649
650 END IF;
651
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 ResultOut := 'ERROR:';
655 WF_Engine.SetItemAttrText
656 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT'
657 , AValue => FND_MESSAGE.Get_String('OKE' , 'OKE_CHGREQ_NODEF_REJ_STS') );
658 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
659 , 'SET_APPROVED_STATUS'
660 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
661 RAISE;
662
663 WHEN OTHERS THEN
664 ResultOut := 'ERROR';
665 WF_Engine.SetItemAttrText
666 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
667 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
671
668 , 'SET_REJECTED_STATUS'
669 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
670 RAISE;
672 END Set_Rejected_Status;
673
674
675 END OKE_CHG_REQUESTS_WF;