[Home] [Help]
PACKAGE BODY: APPS.OKE_CHG_REQUESTS_WF
Source
1 PACKAGE BODY OKE_CHG_REQUESTS_WF AS
2 /* $Header: OKEWCRQB.pls 120.1 2011/05/27 08:10:43 skuchima ship $ */
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
504 -- Function : This procedure sets the status of the change request
505 -- to Approved.
506 --
507 -- Parameters :
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 CURSOR c(p_Chg_Request_ID IN number )
528 is
529 SELECT *
530 FROM oke_chg_requests
531 WHERE chg_request_id =p_CHG_REQUEST_ID ;
532
533 BEGIN
534 L_Approved_Status := Default_Chg_Status('APPROVED');
535
536 IF ( FuncMode = 'RUN' ) THEN
537
538 IF ( L_Approved_Status IS NULL ) THEN
539 RAISE NO_DATA_FOUND;
540 END IF;
541
542 L_Chg_Request_ID := WF_ENGINE.GetItemAttrNumber
543 ( itemtype => ItemType
544 , ItemKey => ItemKey
545 , AName => 'CHG_REQUEST_ID' );
546
547 --added this as part of fix 12414859 skuchima
548 FOR c_rec IN c(l_chg_request_id) LOOP
549
550 OKE_CHG_REQUESTS_PKG.UPDATE_ROW
551 ( X_CHG_REQUEST_ID => L_Chg_Request_ID
552 , X_LAST_UPDATE_DATE => sysdate
553 , X_LAST_UPDATED_BY => c_rec.LAST_UPDATED_BY
554 , X_LAST_UPDATE_LOGIN => c_rec.LAST_UPDATE_LOGIN
555 , X_K_HEADER_ID => c_rec.K_HEADER_ID
556 , X_CHG_REQUEST_NUM => c_rec.CHG_REQUEST_NUM
557 , X_CHG_TYPE_CODE => c_rec.CHG_TYPE_CODE
558 , X_CHG_STATUS_CODE => L_Approved_Status
559 , X_CHG_REASON_CODE => c_rec.CHG_REASON_CODE
560 , X_IMPACT_FUNDING_FLAG =>c_rec.IMPACT_FUNDING_FLAG
561 , X_EFFECTIVE_DATE => c_rec.EFFECTIVE_DATE
562 , X_REQUESTED_BY_PERSON_ID => c_rec.REQUESTED_BY_PERSON_ID
563 , X_REQUESTED_DATE => c_rec.REQUESTED_DATE
564 , X_RECEIVE_DATE => c_rec.RECEIVE_DATE
565 , X_APPROVE_DATE => c_rec.APPROVE_DATE
566 , X_IMPLEMENT_DATE => c_rec.IMPLEMENT_DATE
567 , X_PREV_VERSION => c_rec.PREV_VERSION
568 , X_NEW_VERSION => c_rec.NEW_VERSION
569 , X_DESCRIPTION =>c_rec.DESCRIPTION
570 , X_CHG_TEXT => c_rec.CHG_TEXT
571 , X_LAST_CHG_LOG_ID => c_rec.LAST_CHG_LOG_ID
572 , X_ATTRIBUTE_CATEGORY =>c_rec.ATTRIBUTE_CATEGORY
573 , X_ATTRIBUTE1 => c_rec.ATTRIBUTE1
574 , X_ATTRIBUTE2 => c_rec.ATTRIBUTE2
575 , X_ATTRIBUTE3 => c_rec.ATTRIBUTE3
576 , X_ATTRIBUTE4 => c_rec.ATTRIBUTE4
577 , X_ATTRIBUTE5 => c_rec.ATTRIBUTE5
578 , X_ATTRIBUTE6 => c_rec.ATTRIBUTE6
579 , X_ATTRIBUTE7 => c_rec.ATTRIBUTE7
580 , X_ATTRIBUTE8 => c_rec.ATTRIBUTE8
581 , X_ATTRIBUTE9 => c_rec.ATTRIBUTE9
582 , X_ATTRIBUTE10 => c_rec.ATTRIBUTE10
583 , X_ATTRIBUTE11 => c_rec.ATTRIBUTE11
584 , X_ATTRIBUTE12 => c_rec.ATTRIBUTE12
585 , X_ATTRIBUTE13 => c_rec.ATTRIBUTE13
586 , X_ATTRIBUTE14 => c_rec.ATTRIBUTE14
587 , X_ATTRIBUTE15 => c_rec.ATTRIBUTE15
588 );
589
590 END LOOP;
591
592 /* UPDATE oke_chg_requests
593 SET chg_status_code = L_Approved_Status
594 , last_update_date = sysdate
595 WHERE chg_request_id = L_Chg_Request_ID;*/ --skuchima bug12414859
596
597 ResultOut := 'COMPLETE:';
598 RETURN;
599
600 END IF;
601
602 IF ( FuncMode = 'CANCEL' ) THEN
603
604 ResultOut := '';
605 RETURN;
606
607 END IF;
608
609 IF ( FuncMode = 'TIMEOUT' ) THEN
610
611 ResultOut := '';
612 RETURN;
613
614 END IF;
615
616 EXCEPTION
617 WHEN NO_DATA_FOUND THEN
618 ResultOut := 'ERROR:';
619 WF_Engine.SetItemAttrText
620 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT'
621 , AValue => FND_MESSAGE.Get_String('OKE' , 'OKE_CHGREQ_NODEF_APPR_STS') );
622 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
623 , 'SET_APPROVED_STATUS'
624 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
625 RAISE;
626
627 WHEN OTHERS THEN
628 ResultOut := 'ERROR:';
629 WF_Engine.SetItemAttrText
630 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
631 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
632 , 'SET_APPROVED_STATUS'
633 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
634 RAISE;
635
636 END Set_Approved_Status;
637
638
639 --
640 -- Name : Set_Rejected_Status
641 -- Pre-reqs : Must be called from WF activity
642 -- Function : This procedure sets the status of the change request
643 -- to Rejected.
644 --
645 -- Parameters :
646 -- IN : ItemType
647 -- ItemKey
648 -- ActID
649 -- FuncMode
650 -- OUT : ResultOut
651 --
652 -- Returns : None
653 --
654 PROCEDURE Set_Rejected_Status
655 ( ItemType IN VARCHAR2
656 , ItemKey IN VARCHAR2
657 , ActID IN NUMBER
658 , FuncMode IN VARCHAR2
659 , ResultOut OUT NOCOPY VARCHAR2
660 ) IS
661
662 L_Chg_Request_ID NUMBER;
663 L_Rejected_Status VARCHAR2(30);
664
665 CURSOR c(p_Chg_Request_ID IN number )
666 is
667 SELECT *
668 FROM oke_chg_requests
669 WHERE chg_request_id =p_CHG_REQUEST_ID ;
670
671 BEGIN
672 L_Rejected_Status := Default_Chg_Status('REJECTED');
673
674 IF ( FuncMode = 'RUN' ) THEN
675
676 IF ( L_Rejected_Status IS NULL ) THEN
677 RAISE NO_DATA_FOUND;
678 END IF;
679
680 L_Chg_Request_ID := WF_ENGINE.GetItemAttrNumber
681 ( itemtype => ItemType
682 , ItemKey => ItemKey
683 , AName => 'CHG_REQUEST_ID' );
684
685 --added this as part of fix 12414859 skuchima
686
687 FOR c_rec IN c(l_chg_request_id) LOOP
688
689 OKE_CHG_REQUESTS_PKG.UPDATE_ROW
690 ( X_CHG_REQUEST_ID => L_Chg_Request_ID
691 , X_LAST_UPDATE_DATE => sysdate
692 , X_LAST_UPDATED_BY => c_rec.LAST_UPDATED_BY
693 , X_LAST_UPDATE_LOGIN => c_rec.LAST_UPDATE_LOGIN
694 , X_K_HEADER_ID => c_rec.K_HEADER_ID
695 , X_CHG_REQUEST_NUM => c_rec.CHG_REQUEST_NUM
696 , X_CHG_TYPE_CODE => c_rec.CHG_TYPE_CODE
697 , X_CHG_STATUS_CODE => L_Rejected_Status
698 , X_CHG_REASON_CODE => c_rec.CHG_REASON_CODE
699 , X_IMPACT_FUNDING_FLAG =>c_rec.IMPACT_FUNDING_FLAG
700 , X_EFFECTIVE_DATE => c_rec.EFFECTIVE_DATE
701 , X_REQUESTED_BY_PERSON_ID => c_rec.REQUESTED_BY_PERSON_ID
702 , X_REQUESTED_DATE => c_rec.REQUESTED_DATE
703 , X_RECEIVE_DATE => c_rec.RECEIVE_DATE
704 , X_APPROVE_DATE => c_rec.APPROVE_DATE
705 , X_IMPLEMENT_DATE => c_rec.IMPLEMENT_DATE
706 , X_PREV_VERSION => c_rec.PREV_VERSION
707 , X_NEW_VERSION => c_rec.NEW_VERSION
708 , X_DESCRIPTION =>c_rec.DESCRIPTION
709 , X_CHG_TEXT => c_rec.CHG_TEXT
710 , X_LAST_CHG_LOG_ID => c_rec.LAST_CHG_LOG_ID
711 , X_ATTRIBUTE_CATEGORY =>c_rec.ATTRIBUTE_CATEGORY
712 , X_ATTRIBUTE1 => c_rec.ATTRIBUTE1
713 , X_ATTRIBUTE2 => c_rec.ATTRIBUTE2
714 , X_ATTRIBUTE3 => c_rec.ATTRIBUTE3
715 , X_ATTRIBUTE4 => c_rec.ATTRIBUTE4
716 , X_ATTRIBUTE5 => c_rec.ATTRIBUTE5
717 , X_ATTRIBUTE6 => c_rec.ATTRIBUTE6
718 , X_ATTRIBUTE7 => c_rec.ATTRIBUTE7
719 , X_ATTRIBUTE8 => c_rec.ATTRIBUTE8
720 , X_ATTRIBUTE9 => c_rec.ATTRIBUTE9
721 , X_ATTRIBUTE10 => c_rec.ATTRIBUTE10
722 , X_ATTRIBUTE11 => c_rec.ATTRIBUTE11
723 , X_ATTRIBUTE12 => c_rec.ATTRIBUTE12
724 , X_ATTRIBUTE13 => c_rec.ATTRIBUTE13
725 , X_ATTRIBUTE14 => c_rec.ATTRIBUTE14
726 , X_ATTRIBUTE15 => c_rec.ATTRIBUTE15
727 );
728
729 END LOOP;
730
731
732 /* UPDATE oke_chg_requests
733 SET chg_status_code = L_Rejected_Status
734 , last_update_date = sysdate
735 WHERE chg_request_id = L_Chg_Request_ID;*/ --skuchima bug12414859
736
737 ResultOut := 'COMPLETE:';
738 RETURN;
739
740 END IF;
741
742 IF ( FuncMode = 'CANCEL' ) THEN
743
744 ResultOut := '';
745 RETURN;
746
747 END IF;
748
749 IF ( FuncMode = 'TIMEOUT' ) THEN
750
751 ResultOut := '';
752 RETURN;
753
754 END IF;
755
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 ResultOut := 'ERROR:';
759 WF_Engine.SetItemAttrText
760 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT'
761 , AValue => FND_MESSAGE.Get_String('OKE' , 'OKE_CHGREQ_NODEF_REJ_STS') );
762 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
763 , 'SET_APPROVED_STATUS'
764 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
765 RAISE;
766
767 WHEN OTHERS THEN
768 ResultOut := 'ERROR';
769 WF_Engine.SetItemAttrText
770 ( ItemType => ItemType , ItemKey => ItemKey , AName => 'ERRORTEXT' , AValue => sqlerrm );
771 WF_Core.Context( 'OKE_CHG_REQUESTS_WF'
772 , 'SET_REJECTED_STATUS'
773 , ItemType , ItemKey , to_char(ActID) , FuncMode , ResultOut );
774 RAISE;
775
776 END Set_Rejected_Status;
777
778
779 END OKE_CHG_REQUESTS_WF;