[Home] [Help]
PACKAGE BODY: APPS.OKE_CHG_REQ_UTILS
Source
1 PACKAGE BODY OKE_CHG_REQ_UTILS AS
2 /* $Header: OKECRQUB.pls 120.1 2005/06/24 10:40:36 ausmani noship $ */
3 --
4 -- Name : Status_Change
5 -- Pre-reqs : None
6 -- Function : This procedure performs utility functions during
7 -- a change request status change.
8 --
9 --
10 -- Parameters :
11 -- IN : None
12 -- OUT : None
13 --
14 -- Returns : None
15 --
16
17 PROCEDURE Status_Change
18 ( P_Calling_Mode IN VARCHAR2
19 , P_K_Header_ID IN NUMBER
20 , P_Chg_Request_ID IN NUMBER
21 , P_Chg_Request_Num IN VARCHAR2
22 , P_Requested_By IN NUMBER
23 , P_Effective_Date IN DATE
24 , P_Old_Status_Code IN VARCHAR2
25 , P_New_Status_Code IN VARCHAR2
26 , P_Chg_Type_Code IN VARCHAR2
27 , P_Chg_Reason_Code IN VARCHAR2
28 , P_Impact_Funding_flag IN VARCHAR2
29 , P_Description IN VARCHAR2
30 , P_Chg_Text IN VARCHAR2
31 , P_Updated_By IN NUMBER
32 , P_Update_Date IN DATE
33 , P_Login_ID IN NUMBER
34 , X_Chg_Log_ID IN OUT NOCOPY NUMBER
35 , X_Approve_Date IN OUT NOCOPY DATE
36 , X_Implement_Date IN OUT NOCOPY DATE
37 ) IS
38
39 CURSOR sts IS
40 SELECT wf_item_type
41 , wf_process
42 FROM oke_chg_statuses_b
43 WHERE chg_status_code = P_New_Status_Code;
44
45 CURSOR kh IS
46 SELECT k.k_number_disp
47 , k.k_type_code
48 , kt.k_type_name
49 , k.authoring_org_id
50 FROM oke_k_headers_v k
51 , oke_k_types_vl kt
52 WHERE k_header_id = P_K_Header_ID
53 AND kt.k_type_code = k.k_type_code;
54
55 CURSOR req IS
56 SELECT u.user_name
57 FROM per_all_people_f p
58 , fnd_user u
59 WHERE person_id = P_Requested_By
60 AND u.employee_id = p.person_id;
61
62 CURSOR cs ( C_Status_Code VARCHAR2 ) IS
63 SELECT chg_status_type_code
64 FROM oke_chg_statuses_b
65 WHERE chg_status_code = C_Status_Code;
66
67 l_wf_item_type VARCHAR2(8) := NULL;
68 l_wf_process VARCHAR2(30) := NULL;
69 l_wf_item_key VARCHAR2(240) := NULL;
70 l_wf_user_key VARCHAR2(240) := NULL;
71 l_contract_num VARCHAR2(240);
72 l_k_type VARCHAR2(80);
73 l_k_type_code VARCHAR2(30);
74 l_requestor_name VARCHAR2(240);
75 l_old_ststype VARCHAR2(30);
76 l_new_ststype VARCHAR2(30);
77 l_org_id NUMBER(10);
78
79 BEGIN
80 --
81 -- Fetch Workflow information from Change Status
82 --
83 OPEN sts;
84 FETCH sts INTO l_wf_item_type , l_wf_process;
85 CLOSE sts;
86
87 --
88 -- Get the change log ID; this also serves as the WF Item Key
89 --
90 SELECT oke_chg_logs_s.nextval
91 INTO X_Chg_Log_ID
92 FROM dual;
93
94 IF ( l_wf_item_type IS NOT NULL and l_wf_process IS NOT NULL) THEN
95 --
96 -- Fetch relevant information
97 --
98 OPEN kh;
99 FETCH kh INTO l_contract_num , l_k_type_code , l_k_type,l_org_id;
100 CLOSE kh;
101
102 OPEN req;
103 FETCH req INTO l_requestor_name;
104 CLOSE req;
105
106 l_wf_item_key := X_Chg_Log_ID;
107
108 l_wf_user_key := l_Contract_Num || '-' ||
109 P_Chg_Request_Num || '-' ||
110 P_New_Status_Code || '-' ||
111 TO_CHAR(sysdate,'YYMMDD:HH24MISS');
112
113 WF_ENGINE.CreateProcess( itemtype => l_wf_item_type
114 , itemkey => l_wf_item_key
115 , process => l_wf_process );
116
117 WF_ENGINE.SetItemOwner ( itemtype => l_wf_item_type
121 WF_ENGINE.SetItemUserKey( itemtype => l_wf_item_type
118 , itemkey => l_wf_item_key
119 , owner => FND_GLOBAL.User_Name );
120
122 , itemkey => l_wf_item_key
123 , userkey => l_wf_user_key );
124
125 --
126 -- Setting various Workflow Item Attributes
127 --
128 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
129 , itemkey => l_wf_item_key
130 , aname => 'DOC_TYPE'
131 , avalue => l_k_type );
132
133 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
134 , itemkey => l_wf_item_key
135 , aname => 'DOC_TYPE_CODE'
136 , avalue => l_k_type_code );
137
138 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
139 , itemkey => l_wf_item_key
140 , aname => 'DOC_NUMBER'
141 , avalue => l_Contract_Num );
142
143 WF_ENGINE.SetItemAttrNumber( itemtype => l_wf_item_type
144 , itemkey => l_wf_item_key
145 , aname => 'CHG_REQUEST_ID'
146 , avalue => P_Chg_Request_ID );
147
148 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
149 , itemkey => l_wf_item_key
150 , aname => 'CHG_REQUEST_NUM'
151 , avalue => P_Chg_Request_Num );
152
153 WF_ENGINE.SetItemAttrNumber( itemtype => l_wf_item_type
154 , itemkey => l_wf_item_key
155 , aname => 'ORG_ID'
156 , avalue => l_org_id );
157
158 WF_ENGINE.SetItemAttrDate( itemtype => l_wf_item_type
159 , itemkey => l_wf_item_key
160 , aname => 'EFFECTIVE_DATE'
161 , avalue => P_Effective_Date );
162
163 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
164 , itemkey => l_wf_item_key
165 , aname => 'IMPACT_FUNDING_FLAG'
166 , avalue => P_Impact_Funding_flag );
167
168 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
169 , itemkey => l_wf_item_key
170 , aname => 'CHG_TYPE_CODE'
171 , avalue => P_Chg_Type_Code );
172
173 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
174 , itemkey => l_wf_item_key
175 , aname => 'CHG_REASON_CODE'
176 , avalue => P_Chg_Reason_Code );
177
178 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
179 , itemkey => l_wf_item_key
180 , aname => 'NEW_STATUS_CODE'
181 , avalue => P_New_Status_Code );
182
183 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
184 , itemkey => l_wf_item_key
185 , aname => 'OLD_STATUS_CODE'
186 , avalue => P_Old_Status_Code );
187
188 WF_ENGINE.SetItemAttrNumber( itemtype => l_wf_item_type
189 , itemkey => l_wf_item_key
190 , aname => 'K_HEADER_ID'
191 , avalue => P_K_Header_ID );
192
193 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
194 , itemkey => l_wf_item_key
195 , aname => 'LAST_UPDATED_BY'
196 , avalue => P_Updated_by );
197
198 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
199 , itemkey => l_wf_item_key
200 , aname => 'REQUESTOR'
201 , avalue => L_requestor_name );
202
203 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
204 , itemkey => l_wf_item_key
205 , aname => 'DESCRIPTION'
206 , avalue => P_Description );
207
208 WF_ENGINE.SetItemAttrText( itemtype => l_wf_item_type
209 , itemkey => l_wf_item_key
210 , aname => 'CHG_TEXT'
211 , avalue => P_Chg_Text );
212
213 --
214 -- Start the Workflow Process if not called from trigger
215 --
216 IF ( P_Calling_Mode <> 'TRIGGER' ) THEN
217 WF_ENGINE.StartProcess( itemtype => l_wf_item_type
218 , itemkey => l_wf_item_key );
219 END IF;
220 END IF;
221
222 --
223 -- Write record into Change Status History
224 --
225 INSERT INTO oke_chg_logs
226 ( chg_log_id
227 , chg_request_id
228 , chg_status_code
229 , creation_date
230 , created_by
231 , last_update_date
232 , last_updated_by
233 , last_update_login
234 , wf_item_type
235 , wf_process
236 , wf_item_key )
237 VALUES
238 ( X_Chg_Log_ID
239 , P_Chg_Request_ID
240 , P_New_Status_Code
241 , P_Update_Date
242 , P_Updated_By
243 , P_Update_Date
244 , P_Updated_By
245 , P_Login_ID
246 , l_wf_item_type
247 , l_wf_process
248 , l_wf_item_key
249 );
250
251 --
252 -- Setting Approval and Implement Dates if applicable
256 CLOSE cs;
253 --
254 OPEN cs ( P_Old_Status_Code );
255 FETCH cs INTO l_Old_StsType;
257
258 OPEN cs ( P_New_Status_Code );
259 FETCH cs INTO l_New_StsType;
260 CLOSE cs;
261
262 IF ( l_Old_StsType <> l_New_StsType ) THEN
263 IF ( l_Old_StsType = 'SUBMITTED'
264 AND l_New_StsType = 'APPROVED' ) THEN
265 X_Approve_Date := P_Update_Date;
266 ELSIF ( l_Old_StsType = 'IN PROGRESS'
267 AND l_New_StsType = 'COMPLETED' ) THEN
268 X_Implement_Date := P_Update_Date;
269 END IF;
270 END IF;
271
272 END Status_Change;
273
274
275 --
276 -- Name : Get_Process_Status
277 -- Pre-reqs : None
278 -- Function : This procedure returns the Workflow status of
279 -- a status change as stored in the history.
280 --
281 --
282 -- Parameters :
283 -- IN : P_CHG_LOG_ID NUMBER
284 -- OUT : X_STATUS VARCHAR2
285 -- X_RESULT VARCHAR2
286 --
287 -- Returns : None
288 --
289 PROCEDURE Get_Process_Status
290 ( P_Chg_Log_ID IN NUMBER
291 , X_Status OUT NOCOPY VARCHAR2
292 , X_Result OUT NOCOPY VARCHAR2
293 ) IS
294
295 CURSOR c IS
296 SELECT WF_Item_Type
297 , WF_Item_Key
298 FROM OKE_Chg_Logs
299 WHERE Chg_Log_ID = P_Chg_Log_ID;
300 crec c%rowtype;
301
302 BEGIN
303 OPEN c;
304 FETCH c INTO crec;
305 CLOSE c;
306
307 IF ( crec.WF_Item_Key IS NOT NULL ) THEN
308
309 WF_ENGINE.ItemStatus( itemtype => crec.WF_Item_Type
310 , itemkey => crec.WF_Item_Key
311 , status => X_Status
312 , result => X_Result
313 );
314
315 ELSE
316
317 X_Status := NULL;
318 X_Result := NULL;
319
320 END IF;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 X_Status := NULL;
325 X_Result := NULL;
326 END Get_Process_Status;
327
328
329 --
330 -- Name : Update_Process
331 -- Pre-reqs : None
332 -- Function : This procedure suspend/resume/abort an existing
333 -- workflow process
334 --
335 --
336 -- Parameters :
337 -- IN : P_CHG_LOG_ID NUMBER
338 -- P_MODE VARCHAR2
339 -- - SUSPEND
340 -- - RESUME
341 -- - ABORT
342 -- OUT : None
343 --
344 -- Returns : None
345 --
346 PROCEDURE Update_Process
347 ( P_Chg_Log_ID IN NUMBER
348 , P_Mode IN VARCHAR2
349 ) IS
350
351 CURSOR wf IS
352 SELECT wf_item_type
353 , wf_item_key
354 FROM oke_chg_logs
355 WHERE chg_log_id = P_Chg_Log_ID;
356 wfrec wf%rowtype;
357
358 --
359 -- Making this procedure as AUTONOMOUS transaction.
360 --
361 PRAGMA AUTONOMOUS_TRANSACTION;
362
363 BEGIN
364
365 OPEN wf;
366 FETCH wf INTO wfrec;
367 CLOSE wf;
368
369 IF ( wfrec.wf_item_key IS NOT NULL ) THEN
370
371 IF ( P_Mode = 'ABORT' ) THEN
372
373 WF_ENGINE.AbortProcess( itemtype => wfrec.wf_item_type
374 , itemkey => wfrec.wf_item_key
375 , process => NULL
376 , result => '#FORCE' );
377
378 ELSIF ( P_Mode = 'SUSPEND' ) THEN
379
380 WF_ENGINE.SuspendProcess( itemtype => wfrec.wf_item_type
381 , itemkey => wfrec.wf_item_key
382 , process => NULL );
383
384 ELSIF ( P_Mode = 'RESUME' ) THEN
385
386 WF_ENGINE.ResumeProcess( itemtype => wfrec.wf_item_type
387 , itemkey => wfrec.wf_item_key
388 , process => NULL );
389
390 END IF;
391
392 END IF;
393
394 COMMIT;
395
396 END Update_Process;
397
398
399 --
400 -- Name : OK_To_Implement
401 -- Pre-reqs : None
402 -- Function : This function checks whether there is another
403 -- approved change request currently in progress
404 -- or unapproved change request with an earlier
405 -- effective date
406 --
407 --
408 -- Parameters :
409 -- IN : X_K_HEADER_ID NUMBER
410 -- : X_CHG_REQUEST_ID NUMBER
411 -- OUT NOCOPY : None
412 --
413 -- Returns : VARCHAR2
414 -- Y - OK to implement
415 -- W - give user warning message
416 -- N - Cannot proceed
417 --
418 FUNCTION OK_To_Implement
419 ( X_Chg_Request_ID IN NUMBER
420 ) RETURN VARCHAR2 IS
421
422 CURSOR crq IS
423 SELECT CRQ2.Chg_Request_Num
424 , CS.Chg_Status_Type_Code
425 FROM oke_chg_requests CRQ1
426 , oke_chg_requests CRQ2
427 , oke_chg_statuses_b CS
428 WHERE CRQ1.Chg_Request_ID = X_Chg_Request_ID
429 AND CRQ2.K_Header_ID = CRQ1.K_Header_ID
430 AND CRQ2.Chg_Request_ID <> CRQ1.Chg_Request_ID
431 AND CS.Chg_Status_Code = CRQ2.Chg_Status_Code
432 AND ( CS.Chg_Status_Type_Code = 'IN PROGRESS'
433 OR ( CS.Chg_Status_Type_Code NOT IN ( 'COMPLETED'
437 )
434 , 'CANCELED' )
435 AND CRQ2.Effective_Date < CRQ1.Effective_Date
436 )
438 ORDER BY DECODE(CS.Chg_Status_Type_Code, 'IN PROGRESS' , 1 , 2);
439
440 crqrec crq%rowtype;
441
442 BEGIN
443
444 OPEN crq;
445 FETCH crq INTO crqrec;
446
447 IF ( crq%notfound ) THEN
448 CLOSE crq;
449 RETURN ( 'Y' );
450 END IF;
451
452 CLOSE crq;
453
454 IF ( crqrec.Chg_Status_Type_Code = 'IN PROGRESS' ) THEN
455 --
456 -- Another change request is in progress; cannot proceed
457 --
458 FND_MESSAGE.SET_NAME('OKE' , 'OKE_CHGREQ_OTHER_CRQ_IMPL');
459 FND_MESSAGE.SET_TOKEN('REQNUM' , crqrec.Chg_Request_Num);
460 RETURN ( 'N' );
461 ELSE
462 --
463 -- Earlier change request exists, warn
464 --
465 FND_MESSAGE.SET_NAME('OKE' , 'OKE_CHGREQ_EARLY_CRQ_EXISTS');
466 RETURN ( 'W' );
467 END IF;
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 FND_MESSAGE.SET_NAME('OKE' , 'OKE_CHGREQ_IMPLCHK_ERROR');
472 FND_MESSAGE.SET_TOKEN('ERROR' , sqlerrm);
473 RETURN ( 'N' );
474
475 END OK_To_Implement;
476
477 --
478 -- Name : OK_To_Undo
479 -- Pre-reqs : None
480 -- Function : This function checks whether there is another
481 -- completed or in progress change request with a
482 -- later effective date
483 --
484 --
485 -- Parameters :
486 -- IN : X_K_HEADER_ID NUMBER
487 -- : X_CHG_REQUEST_ID NUMBER
488 -- OUT : None
489 --
490 -- Returns : VARCHAR2
491 -- Y - OK to undo
492 -- W - give user warning message
493 -- N - Cannot proceed
494 --
495 FUNCTION OK_To_Undo
496 ( X_Chg_Request_ID IN NUMBER
497 ) RETURN VARCHAR2 IS
498
499 CURSOR crq IS
500 SELECT COUNT(CRQ2.Chg_Request_Num) ChgReq_Count
501 FROM oke_chg_requests CRQ1
502 , oke_chg_requests CRQ2
503 , oke_chg_statuses_b CS
504 WHERE CRQ1.Chg_Request_ID = X_Chg_Request_ID
505 AND CRQ2.K_Header_ID = CRQ1.K_Header_ID
506 AND CRQ2.Chg_Request_ID <> CRQ1.Chg_Request_ID
507 AND CS.Chg_Status_Code = CRQ2.Chg_Status_Code
508 AND CS.Chg_Status_Type_Code IN ( 'IN PROGRESS' , 'COMPLETED' )
509 AND CRQ2.Effective_Date > CRQ1.Effective_Date;
510
511 crqrec crq%rowtype;
512
513 BEGIN
514
515 OPEN crq;
516 FETCH crq INTO crqrec;
517 CLOSE crq;
518
519 IF ( crqrec.ChgReq_Count = 0 ) THEN
520 RETURN ( 'Y' );
521 ELSE
522 --
523 -- Later completed change request exists, cannot proceed
524 --
525 FND_MESSAGE.SET_NAME('OKE' , 'OKE_CHGREQ_OTHER_CRQ_COMP');
526 FND_MESSAGE.SET_TOKEN('COUNT' , crqrec.ChgReq_Count);
527 RETURN ( 'N' );
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 FND_MESSAGE.SET_NAME('OKE' , 'OKE_CHGREQ_UNDOCHK_ERROR');
533 FND_MESSAGE.SET_TOKEN('ERROR' , sqlerrm);
534 RETURN ( 'N' );
535
536 END OK_To_Undo;
537
538
539 --
540 -- Name : Get_Chg_Request
541 -- Pre-reqs : None
542 -- Function : This function returns the related Change Request
543 -- Number and Change Status for the given contract
544 -- either for the current version or a specific
545 -- major version.
546 --
547 --
548 -- Parameters :
549 -- IN : X_K_Header_ID NUMBER
550 -- X_Major_Version NUMBER
551 -- X_Current_Only VARCHAR2 DEFAULT Y
552 -- X_Curr_Indicator VARCHAR2 DEFAULT N
553 -- OUT : X_Change_Request VARCHAR2
554 -- X_Change_Status VARCHAR2
555 -- IN : X_History_Use VARCHAR2 DEFAULT N
556 --
557
558 PROCEDURE Get_Chg_Request
559 ( X_K_Header_ID IN NUMBER
560 , X_Major_Version IN NUMBER
561 , X_Change_Request OUT NOCOPY VARCHAR2
562 , X_Change_Status OUT NOCOPY VARCHAR2
563 , X_History_Use IN VARCHAR2
564 ) IS
565
566 L_Version_Reason_Code OKE_K_VERS_NUMBERS_H.Version_Reason_Code%TYPE;
567
568 CURSOR c IS
569 SELECT CR.Chg_Request_Num
570 , CS.Chg_Status_Name
571 , H.Version_Reason_Code
572 FROM oke_k_vers_numbers_h H
573 , oke_chg_requests CR
574 , oke_chg_statuses_tl CS
575 , ( SELECT K_Header_ID
576 , Chg_Request_ID
577 , max(Major_Version) Last_Version
578 FROM oke_k_vers_numbers_h
582 AND H.Major_Version = V.Last_Version
579 GROUP BY K_Header_ID , Chg_Request_ID ) V
580 WHERE V.K_Header_ID = X_K_Header_ID
581 AND H.K_Header_ID = V.K_Header_ID
583 AND ( X_Major_Version IS NULL
584 OR H.Major_Version <= X_Major_Version )
585 AND H.Version_Reason_Code <> 'CHGREQ_REVERT'
586 AND CR.Chg_Request_ID = H.Chg_Request_ID
587 AND CS.Chg_Status_Code = CR.Chg_Status_Code
588 AND CS.Language = userenv('LANG')
589 ORDER BY H.Major_Version DESC;
590
591 BEGIN
592
593 OPEN c;
594 FETCH c INTO X_Change_Request , X_Change_Status , L_Version_Reason_Code;
595 CLOSE c;
596
597 IF (X_History_Use = 'Y' AND L_Version_Reason_Code <> 'CHGREQ_COMPLETE') THEN
598 X_Change_Request := NULL;
599 X_Change_Status := NULL;
600 END IF;
601
602 EXCEPTION
603 WHEN OTHERS THEN
604 IF ( c%isopen ) THEN
605 CLOSE c;
606 END IF;
607
608 END Get_Chg_Request;
609
610 END OKE_CHG_REQ_UTILS;