[Home] [Help]
PACKAGE BODY: APPS.OKC_CHANGE_CONTRACT_PVT
Source
1 package body OKC_CHANGE_CONTRACT_PVT as
2 /* $Header: OKCRCHKB.pls 120.1 2005/11/21 12:06:16 dneetha noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 --
6 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
7 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_CHANGE_CONTRACT_PVT';
8 G_LEVEL CONSTANT VARCHAR2(4) := '_PVT';
9 l_api_version CONSTANT NUMBER := 1;
10 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
11 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
12 --
13 G_PROCESS_NOT_FOR_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOR_APPROVAL';
14 G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
15 G_WF_P_NAME_TOKEN CONSTANT varchar2(200) := 'WF_PROCESS';
16 --
17 G_PROCESS_NOTFOUND CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_FOUND';
18 --
19 G_WF_NOT_PURGED CONSTANT varchar2(200) := 'OKC_WF_NOT_PURGED';
20 -- G_WF_NAME_TOKEN CONSTANT varchar2(200) := 'WF_ITEM';
21 G_KEY_TOKEN CONSTANT varchar2(200) := 'WF_KEY';
22 --
23 G_CRT_NOT_ON_APPROVAL CONSTANT varchar2(200) := 'OKC_PROCESS_NOT_ACTIVE';
24 --
25 G_A_NO_U_PRIVILEGE CONSTANT varchar2(200) := 'OKC_USER_NO_RIGHT_TO_CHANGE';
26 G_USER_NAME CONSTANT varchar2(200) := 'USER_NAME';
27 --
28 G_NO_U_PRIVILEGE CONSTANT varchar2(200) := 'OKC_NO_RIGHT_TO_CHANGE';
29 G_ADMINISTRATOR_REQUIRED CONSTANT varchar2(200) := 'OKC_ADMINISTRATOR_REQUIRED';
30 --
31
32 -- Start of comments
33 --
34 -- Procedure Name : change_approval_start
35 -- Description :
36 -- Business Rules :
37 -- Parameters :
38 -- Version : 1.0
39 -- End of comments
40
41 CURSOR cur_header_aa (p_contract_id number)IS
42 SELECT k.estimated_amount,k.scs_code,scs.cls_code,k.sts_code
43 FROM OKC_K_HEADERS_B K,
44 OKC_SUBCLASSES_B SCS
45 WHERE k.id = p_contract_id
46 AND k.scs_code = scs.code;
47
48 l_scs_code okc_subclasses_v.code%type;
49 l_k_status_code okc_k_headers_v.sts_code%type;
50 l_cls_code okc_subclasses_v.cls_code%type;
51 l_estimated_amount number;
52 procedure change_approval_start(
53 p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2 ,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 p_change_request_id IN number,
59 p_do_commit IN VARCHAR2
60 ) is
61
62 --san
63 Cursor cur_chg(chq_id number) IS
64 Select datetime_request
65 FROM OKC_CHANGE_REQUESTS_B where
66 ID=chq_id;
67 l_chgreq_date OKC_CHANGE_REQUESTS_V.DATETIME_REQUEST%TYPE;
68 --end san
69
70 l_api_name CONSTANT VARCHAR2(30) := 'change_approval_start';
71 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
72 l1_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
73 L1_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
74 L2_CPSV_REC OKC_CONTRACT_PUB.cpsv_rec_type;
75 l_msg_count NUMBER;
76 l_msg_data varchar2(2000);
77 --
78 --
79 l_contract_id number;
80 l_contract_number varchar2(120);
81 l_contract_number_modifier varchar2(120);
82 l_k_short_description varchar2(4000);
83 l_crt_name varchar2(150);
84 l_crt_short_description varchar2(4000);
85 l_key varchar2(240);
86 l_auth_user_id number;
87 l_auth_username varchar2(100);
88 l_requestor varchar2(240);
89 l_signature_required_yn varchar2(3);
90 --
91 l_chreq_date date;
92 cursor key_csr is
93 select
94 K.ID CONTRACT_ID,
95 K.CONTRACT_NUMBER,
96 K.CONTRACT_NUMBER_MODIFIER,
97 K.SHORT_DESCRIPTION K_SHORT_DESCRIPTION,
98 C.NAME CRT_NAME,
99 C.DATETIME_REQUEST,
100 C.SHORT_DESCRIPTION CRT_SHORT_DESCRIPTION,
101 substr(K.CONTRACT_NUMBER
102 ||K.CONTRACT_NUMBER_MODIFIER
103 ||C.NAME,1,240) KEY,
104 -- NVL(C.USER_ID,fnd_global.user_id) AUTH_USER_ID,
105 NVL(C.USER_ID,OKC_API.G_MISS_NUM) AUTH_USER_ID,
106 U.USER_NAME AUTH_USERNAME,
107 C.AUTHORITY REQUESTOR,
108 C.SIGNATURE_REQUIRED_YN
109 from okc_change_requests_v C,
110 OKC_K_PROCESSES cpr,
111 OKC_K_HDR_AGREEDS_V K,
112 fnd_user_view U
113 where C.ID = p_change_request_id
114 and K.ID = C.CHR_ID
115 and cpr.crt_id = C.ID
116 -- and U.USER_ID = NVL(cpr.USER_ID,OKC_API.G_MISS_NUM);
117 and U.USER_ID = NVL(cpr.USER_ID,fnd_global.user_id);
118 --
119 --
120 l_wf_name varchar2(150);
121 l_wf_process_name varchar2(150);
122 l_usage varchar2(60);
123 l_process_id number;
124 --
125 cursor process_def_csr is
126 select PDF.ID, PDF.WF_NAME, PDF.WF_PROCESS_NAME, PDF.USAGE
127 from okc_k_processes KP,
128 OKC_PROCESS_DEFS_B PDF
129 where KP.crt_id = p_change_request_id
130 and PDF.ID = KP.PDF_ID
131 and PDF.begin_date<=sysdate
132 and (PDF.end_date is NULL or PDF.end_date>=sysdate)
133 and PDF.PDF_TYPE = 'WPS';
134 --
135 --
136 l_q varchar2(1);
137 --
138 cursor for_purge_csr is
139 select '!'
140 from WF_ITEMS
141 where item_type = l_wf_name
142 and item_key = l_key;
143 --
144 -- because of bug in lock API
145 --
146 cursor k_pid is
147 select ID,OBJECT_VERSION_NUMBER
148 from okc_k_processes
149 where CRT_ID = p_change_request_id
150 for update of process_id nowait;
151 --
152 --
153 L_PAR_NAME VARCHAR2(150);
154 L_PAR_TYPE VARCHAR2(90);
155 L_PAR_VALUE VARCHAR2(2000);
156 --
157 cursor defined_parameters_csr is
158 select
159 NAME,
160 DATA_TYPE,
161 DEFAULT_VALUE
162 from OKC_PROCESS_DEF_PARAMETERS_V
163 where PDF_ID = l_process_id;
164 --
165 --
166 L_NLS_VALUE VARCHAR2(30);
167 begin
168
169 MO_GLOBAL.INIT('OKS');
170 --
171 -- start activity
172 --
173 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
174 G_PKG_NAME,
175 p_init_msg_list,
176 l_api_version,
177 p_api_version,
178 G_LEVEL,
179 x_return_status);
180 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
182 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
183 RAISE OKC_API.G_EXCEPTION_ERROR;
184 END IF;
185 --
186 -- get values
187 --
188 open key_csr;
189 fetch key_csr into
190 l_contract_id,
191 l_contract_number,
192 l_contract_number_modifier,
193 l_k_short_description,
194 l_crt_name,
195 l_chreq_date,
196 l_crt_short_description,
197 l_key,
198 l_auth_user_id,
199 l_auth_username,
200 l_requestor,
201 l_signature_required_yn;
202 close key_csr;
203
204 -- No administrator specified
205 --
206 If l_auth_user_id=OKC_API.G_MISS_NUM then
207 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
208 p_msg_name => G_ADMINISTRATOR_REQUIRED);
209 raise OKC_API.G_EXCEPTION_ERROR;
210 end if;
211 --
212 --
213 -- authorised user has U privilege
214 -- Bug 2498302 Bypassed security check for administrator.
215 /* if OKC_CONTRACT_APPROVAL_PUB.k_accesible(
216 p_contract_id => l_contract_id,
217 p_user_id => l_auth_user_id,
218 p_level => 'U'
219 ) = OKC_API.G_FALSE
220 then
221 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
222 p_msg_name => G_A_NO_U_PRIVILEGE,
223 p_token1 => G_USER_NAME,
224 p_token1_value => l_auth_username);
225 raise OKC_API.G_EXCEPTION_ERROR;
226 end if; */
227 --
228 -- try to lock crt (will not be locked if approved or on approval etc.
229 -- message raised inside lock procedure
230 --
231 l1_crtv_rec.id := p_change_request_id;
232 OKC_CHANGE_REQUEST_PUB.lock_change_request(
233 p_api_version => l_api_version,
234 x_return_status => l_return_status,
235 x_msg_count => l_msg_count,
236 x_msg_data => l_msg_data,
237 p_crtv_rec => l1_crtv_rec);
238 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
239 RAISE OKC_API.G_EXCEPTION_ERROR;
240 END IF;
241 --
242 -- get wf name
243 --
244 open process_def_csr;
245 fetch process_def_csr into l_process_id, L_WF_NAME, L_WF_PROCESS_NAME, L_USAGE;
246 close process_def_csr;
247 if (L_WF_NAME is NULL) then
248 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
249 p_msg_name => G_PROCESS_NOTFOUND);
250 raise OKC_API.G_EXCEPTION_ERROR;
251 end if;
252 if (L_USAGE <> 'CHG_REQ_APPROVE') then
253 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
254 p_msg_name => G_PROCESS_NOT_FOR_APPROVAL,
255 p_token1 => G_WF_NAME_TOKEN,
256 p_token1_value => L_WF_NAME,
257 p_token2 => G_WF_P_NAME_TOKEN,
258 p_token2_value => L_WF_PROCESS_NAME);
259 raise OKC_API.G_EXCEPTION_ERROR;
260 end if;
261 --
262 -- purge previous item if exists
263 --
264 L_Q:='?';
265 open for_purge_csr;
266 fetch for_purge_csr into L_Q;
267 close for_purge_csr;
268 if (L_Q = '!') then
269 begin
270 wf_purge.total(l_wf_name,l_key);
271 exception
272 when others then
273 begin
274 wf_purge.totalPerm(l_wf_name,l_key);
275 exception
276 when others then
277 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
278 p_msg_name => G_WF_NOT_PURGED,
279 p_token1 => G_WF_NAME_TOKEN,
280 p_token1_value => L_WF_NAME,
281 p_token2 => G_KEY_TOKEN,
282 p_token2_value => L_KEY);
283 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 end;
285 end;
286 end if;
287 --
288 -- update contract process.process_id with key
289 --
290 begin
291 savepoint BECAUSE_OF_BUG_IN_lock;
292 open k_pid;
293 fetch k_pid into L1_CPSV_REC.id,L1_CPSV_REC.object_version_number;
294 close k_pid;
295 exception
296 when others then
297 rollback to BECAUSE_OF_BUG_IN_lock;
298 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
299 raise OKC_API.G_EXCEPTION_ERROR;
300 end;
301 L1_CPSV_REC.PROCESS_ID := L_KEY;
302 OKC_CONTRACT_PUB.update_contract_process(
303 p_api_version => l_api_version,
304 x_return_status => l_return_status,
305 x_msg_count => l_msg_count,
306 x_msg_data => l_msg_data,
307 p_cpsv_rec => L1_CPSV_REC,
308 x_cpsv_rec => L2_CPSV_REC);
309 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
310 rollback to BECAUSE_OF_BUG_IN_lock;
311 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
312 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
313 rollback to BECAUSE_OF_BUG_IN_lock;
314 RAISE OKC_API.G_EXCEPTION_ERROR;
315 END IF;
316 wf_engine.CreateProcess( ItemType => L_WF_NAME,
317 ItemKey => L_KEY,
318 process => L_WF_PROCESS_NAME);
319 wf_engine.SetItemUserKey (ItemType => L_WF_NAME,
320 ItemKey => L_KEY,
321 UserKey => L_KEY);
322 open defined_parameters_csr;
323 LOOP
324 fetch defined_parameters_csr into
325 L_PAR_NAME,
326 L_PAR_TYPE,
327 L_PAR_VALUE;
328 exit when defined_parameters_csr%NOTFOUND;
329 if L_PAR_TYPE = 'C' then
330 begin
331 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
332 itemkey => L_KEY,
333 aname => L_PAR_NAME,
334 avalue => L_PAR_VALUE);
335 exception
336 when others then
337 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
338 itemkey => L_KEY,
339 aname => L_PAR_NAME);
340 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
341 itemkey => L_KEY,
342 aname => L_PAR_NAME,
343 avalue => L_PAR_VALUE);
344 end;
345 elsif L_PAR_TYPE = 'N' then
346 begin
347 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
348 itemkey => L_KEY,
349 aname => L_PAR_NAME,
350 avalue => to_number(L_PAR_VALUE));
351 exception
352 when others then
353 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
354 itemkey => L_KEY,
355 aname => L_PAR_NAME);
356 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
357 itemkey => L_KEY,
358 aname => L_PAR_NAME,
359 avalue => to_number(L_PAR_VALUE));
360 end;
361 elsif L_PAR_TYPE = 'D' then
362 begin
363 wf_engine.SetItemAttrDate (itemtype => L_WF_NAME,
364 itemkey => L_KEY,
365 aname => L_PAR_NAME,
366 avalue => fnd_date.chardate_to_date(L_PAR_VALUE));
367 exception
368 when others then
369 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
370 itemkey => L_KEY,
371 aname => L_PAR_NAME);
372 wf_engine.SetItemAttrDate (itemtype => L_WF_NAME,
373 itemkey => L_KEY,
374 aname => L_PAR_NAME,
375 avalue => fnd_date.chardate_to_date(L_PAR_VALUE));
376 end;
377 end if;
378 END LOOP;
379 begin
380 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
381 itemkey => L_KEY,
382 aname => 'CONTRACT_ID',
383 avalue => l_contract_id);
384 exception
385 when others then
386 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
387 itemkey => L_KEY,
388 aname => 'CONTRACT_ID');
389 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
390 itemkey => L_KEY,
391 aname => 'CONTRACT_ID',
392 avalue => l_contract_id);
393 end;
394 begin
395 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
396 itemkey => L_KEY,
397 aname => 'CONTRACT_NUMBER',
398 avalue => l_contract_number);
399 exception
400 when others then
401 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
402 itemkey => L_KEY,
403 aname => 'CONTRACT_NUMBER');
404 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
405 itemkey => L_KEY,
406 aname => 'CONTRACT_NUMBER',
407 avalue => l_contract_number);
408 end;
409 begin
410 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
411 itemkey => L_KEY,
412 aname => 'CONTRACT_NUMBER_MODIFIER',
413 avalue => l_contract_number_MODIFIER);
414 exception
415 when others then
416 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
417 itemkey => L_KEY,
418 aname => 'CONTRACT_NUMBER_MODIFIER');
419 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
420 itemkey => L_KEY,
421 aname => 'CONTRACT_NUMBER_MODIFIER',
422 avalue => l_contract_number_MODIFIER);
423 end;
424 begin
425 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
426 itemkey => L_KEY,
427 aname => 'K_SHORT_DESCRIPTION',
428 avalue => l_k_short_description);
429 exception
430 when others then
431 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
432 itemkey => L_KEY,
433 aname => 'K_SHORT_DESCRIPTION');
434 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
435 itemkey => L_KEY,
436 aname => 'K_SHORT_DESCRIPTION',
437 avalue => l_k_short_description);
438 end;
439 begin
440 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
441 itemkey => L_KEY,
442 aname => 'CHANGE_REQUEST_ID',
443 avalue => p_change_request_id);
444 exception
445 when others then
446 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
447 itemkey => L_KEY,
448 aname => 'CHANGE_REQUEST_ID');
449 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
450 itemkey => L_KEY,
451 aname => 'CHANGE_REQUEST_ID',
452 avalue => p_change_request_id);
453 end;
454 begin
455 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
456 itemkey => L_KEY,
457 aname => 'CRT_NAME',
458 avalue => l_crt_name);
459 exception
460 when others then
461 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
462 itemkey => L_KEY,
463 aname => 'CRT_NAME');
464 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
465 itemkey => L_KEY,
466 aname => 'CRT_NAME',
467 avalue => l_crt_name);
468 end;
469 begin
470 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
471 itemkey => L_KEY,
472 aname => 'CRT_SHORT_DESCRIPTION',
473 avalue => l_crt_short_description);
474 exception
475 when others then
476 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
477 itemkey => L_KEY,
478 aname => 'CRT_SHORT_DESCRIPTION');
479 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
480 itemkey => L_KEY,
481 aname => 'CRT_SHORT_DESCRIPTION',
482 avalue => l_crt_short_description);
483 end;
484 begin
485 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
486 itemkey => L_KEY,
487 aname => 'AUTH_USERNAME',
488 avalue => l_auth_username);
489 exception
490 when others then
491 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
492 itemkey => L_KEY,
493 aname => 'AUTH_USERNAME');
494 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
495 itemkey => L_KEY,
496 aname => 'AUTH_USERNAME',
497 avalue => l_auth_username);
498 end;
499 begin
500 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
501 itemkey => L_KEY,
502 aname => 'REQUESTOR',
503 avalue => l_requestor);
504 exception
505 when others then
506 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
507 itemkey => L_KEY,
508 aname => 'REQUESTOR');
509 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
510 itemkey => L_KEY,
511 aname => 'REQUESTOR',
512 avalue => l_requestor);
513 end;
514 begin
515 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
516 itemkey => L_KEY,
517 aname => 'SIGNATURE_REQUIRED_YN',
518 avalue => l_signature_required_yn);
519 exception
520 when others then
521 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
522 itemkey => L_KEY,
523 aname => 'SIGNATURE_REQUIRED_YN');
524 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
525 itemkey => L_KEY,
526 aname => 'SIGNATURE_REQUIRED_YN',
527 avalue => l_signature_required_yn);
528 end;
529 begin
530 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
531 itemkey => L_KEY,
532 aname => 'USER_ID',
533 avalue => fnd_global.user_id);
534 exception
535 when others then
536 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
537 itemkey => L_KEY,
538 aname => 'USER_ID');
539 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
540 itemkey => L_KEY,
541 aname => 'USER_ID',
542 avalue => fnd_global.user_id);
543 end;
544 begin
545 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
546 itemkey => L_KEY,
547 aname => 'RESP_ID',
548 avalue => fnd_global.resp_id);
549 exception
550 when others then
551 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
552 itemkey => L_KEY,
553 aname => 'RESP_ID');
554 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
555 itemkey => L_KEY,
556 aname => 'RESP_ID',
557 avalue => fnd_global.resp_id);
558 end;
559 begin
560 wf_engine.SetItemAttrNumber (itemtype => L_WF_NAME,
561 itemkey => L_KEY,
562 aname => 'RESP_APPL_ID',
563 avalue => fnd_global.RESP_APPL_id);
564 exception
565 when others then
566 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
567 itemkey => L_KEY,
568 aname => 'RESP_APPL_ID');
569 wf_engine.SetItemAttrNumber(itemtype => L_WF_NAME,
570 itemkey => L_KEY,
571 aname => 'RESP_APPL_ID',
572 avalue => fnd_global.RESP_APPL_id);
573 end;
574 select value into L_NLS_VALUE
575 from NLS_SESSION_PARAMETERS
576 where PARAMETER='NLS_LANGUAGE';
577 begin
578 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
579 itemkey => L_KEY,
580 aname => 'NLS_LANGUAGE',
581 avalue => L_NLS_VALUE);
582 exception
583 when others then
584 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
585 itemkey => L_KEY,
586 aname => 'NLS_LANGUAGE');
587 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
588 itemkey => L_KEY,
589 aname => 'NLS_LANGUAGE',
590 avalue => L_NLS_VALUE);
591 end;
592 select value into L_NLS_VALUE
593 from NLS_SESSION_PARAMETERS
594 where PARAMETER='NLS_DATE_FORMAT';
595 begin
596 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
597 itemkey => L_KEY,
598 aname => 'NLS_DATE_FORMAT',
599 avalue => L_NLS_VALUE);
600 exception
601 when others then
602 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
603 itemkey => L_KEY,
604 aname => 'NLS_DATE_FORMAT');
605 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
606 itemkey => L_KEY,
607 aname => 'NLS_DATE_FORMAT',
608 avalue => L_NLS_VALUE);
609 end;
610 select value into L_NLS_VALUE
611 from NLS_SESSION_PARAMETERS
612 where PARAMETER='NLS_DATE_LANGUAGE';
613 begin
614 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
615 itemkey => L_KEY,
616 aname => 'NLS_DATE_LANGUAGE',
617 avalue => L_NLS_VALUE);
618 exception
619 when others then
620 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
621 itemkey => L_KEY,
622 aname => 'NLS_DATE_LANGUAGE');
623 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
624 itemkey => L_KEY,
625 aname => 'NLS_DATE_LANGUAGE',
626 avalue => L_NLS_VALUE);
627 end;
628 select '"'||value||'"' into L_NLS_VALUE
629 from NLS_SESSION_PARAMETERS
630 where PARAMETER='NLS_NUMERIC_CHARACTERS';
631 begin
632 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
633 itemkey => L_KEY,
634 aname => 'NLS_NUMERIC_CHARACTERS',
635 avalue => L_NLS_VALUE);
636 exception
637 when others then
638 wf_engine.AddItemAttr (itemtype => L_WF_NAME,
639 itemkey => L_KEY,
640 aname => 'NLS_NUMERIC_CHARACTERS');
641 wf_engine.SetItemAttrText (itemtype => L_WF_NAME,
642 itemkey => L_KEY,
643 aname => 'NLS_NUMERIC_CHARACTERS',
644 avalue => L_NLS_VALUE);
645 end;
646 wf_engine.SetItemOwner ( itemtype => L_WF_NAME,
647 itemkey => L_KEY,
648 owner => fnd_global.user_name);
649 wf_engine.StartProcess( itemtype => L_WF_NAME,
650 itemkey => L_KEY);
651
652 open cur_header_aa(l_contract_id);
653 fetch cur_header_aa into l_estimated_amount,l_scs_code,l_cls_code,l_k_status_code;
654 close cur_header_aa;
655 open cur_chg(p_change_request_id);
656 fetch cur_chg into l_chgreq_date;
657 close cur_chg;
658
659 OKC_CHG_REQ_ASMBLR_PVT.acn_assemble(p_api_version => 1,
660 p_init_msg_list => OKC_API.G_FALSE,
661 x_return_status => l_return_status,
662 x_msg_count => x_msg_count,
663 x_msg_data => x_msg_data,
664 p_k_id => l_contract_id,
665 p_k_number => l_contract_number,
666 p_k_nbr_mod => l_contract_number_modifier,
667 p_k_class => l_cls_code,
668 p_k_subclass => l_scs_code,
669 p_k_STATUS_CODE => l_k_status_code,
670 p_estimated_amount => l_estimated_amount,
671 p_chreq_id => p_change_request_id,
672 p_chreq_date => l_chgreq_date
673 );
674
675 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
676 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
677 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
678 RAISE OKC_API.G_EXCEPTION_ERROR;
679 END IF;
680 if (p_do_commit = OKC_API.G_TRUE) then
681 commit;
682 end if;
683 x_return_status := OKC_API.G_RET_STS_SUCCESS;
684 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
685 EXCEPTION
686 WHEN OKC_API.G_EXCEPTION_ERROR THEN
687 x_return_status := OKC_API.HANDLE_EXCEPTIONS
688 (substr(l_api_name,1,26),
689 G_PKG_NAME,
690 'OKC_API.G_RET_STS_ERROR',
691 x_msg_count,
692 x_msg_data,
693 G_LEVEL);
694 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
695 x_return_status := OKC_API.HANDLE_EXCEPTIONS
696 (substr(l_api_name,1,26),
697 G_PKG_NAME,
698 'OKC_API.G_RET_STS_UNEXP_ERROR',
699 x_msg_count,
700 x_msg_data,
701 G_LEVEL);
702 WHEN OTHERS THEN
703 x_return_status := OKC_API.HANDLE_EXCEPTIONS
704 (substr(l_api_name,1,26),
705 G_PKG_NAME,
706 'OTHERS',
707 x_msg_count,
708 x_msg_data,
709 G_LEVEL);
710 end change_approval_start;
711
712 -- Start of comments
713 --
714 -- Procedure Name : wf_monitor_url
715 -- Description :
716 -- Business Rules :
717 -- Parameters :
718 -- Version : 1.0
719 -- End of comments
720
721 function wf_monitor_url(
722 p_change_request_id IN number,
723 p_process_id IN number,
724 p_mode IN varchar2
725 ) return varchar2 is
726 --
727 -- to be used by fnd_utilities.open_url
728 --
729 --
730 l_wf_name varchar2(150);
731 --
732 cursor wf_name_csr is
733 select WF_NAME
734 from OKC_PROCESS_DEFS_V
735 where ID = p_process_id and PDF_TYPE = 'WPS';
736 --
737 --
738 l_key varchar2(240);
739 --
740 cursor wf_key_csr is
741 select
742 substr(K.CONTRACT_NUMBER
743 ||K.CONTRACT_NUMBER_MODIFIER
744 ||C.NAME,1,240) KEY
745 from okc_change_requests_v C,
746 OKC_K_HDR_AGREEDS_V K
747 where C.ID = p_change_request_id
748 and K.ID = C.CHR_ID;
749 --
750 --
751 l_q varchar2(1);
752 --
753 cursor wf_exist_csr is
754 select '!'
755 from WF_ITEMS
756 where item_type = l_wf_name
757 and item_key = l_key;
758 --
759 --
760 l_admin varchar2(3);
761 begin
762 open wf_name_csr;
763 fetch wf_name_csr into L_WF_NAME;
764 close wf_name_csr;
765 --
766 open wf_key_csr;
767 fetch wf_key_csr into L_KEY;
768 close wf_key_csr;
769 --
770 l_q := '?';
771 open wf_exist_csr;
772 fetch wf_exist_csr into L_Q;
773 close wf_exist_csr;
774 --
775 if l_q = '?' then return NULL;
776 else
777 if p_mode = 'ADMIN' then l_admin := 'YES';
778 else l_admin := 'NO';
779 end if;
780 return wf_monitor.GetDiagramURL(
781 X_AGENT => WF_CORE.TRANSLATE('WF_WEB_AGENT'),
782 X_ITEM_TYPE => L_WF_NAME,
783 X_ITEM_KEY => L_KEY,
784 X_ADMIN_MODE => l_admin);
785 end if;
786 end wf_monitor_url;
787
788 -- Start of comments
789 --
790 -- Procedure Name : change_approval_stop
791 -- Description :
792 -- Business Rules :
793 -- Parameters :
794 -- Version : 1.0
795 -- End of comments
796
797 procedure change_approval_stop(
798 p_api_version IN NUMBER,
799 p_init_msg_list IN VARCHAR2 ,
800 x_return_status OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2,
803 p_change_request_id IN number,
804 p_do_commit IN VARCHAR2
805 ) is
806 l_api_name CONSTANT VARCHAR2(30) := 'change_approval_stop';
807 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
808 --
809 l_key varchar2(240);
810 l_contract_id number;
811 --
812 cursor key_csr is
813 select
814 K.ID CONTRACT_ID,
815 substr(K.CONTRACT_NUMBER
816 ||K.CONTRACT_NUMBER_MODIFIER
817 ||C.NAME,1,240) KEY
818 from okc_change_requests_v C,
819 OKC_K_HDR_AGREEDS_V K
820 where C.ID = p_change_request_id
821 and K.ID = C.CHR_ID;
822 --
823 l_wf_name_active varchar2(150);
824 --
825 cursor approval_active_csr is
826 select item_type
827 from WF_ITEMS
828 where item_type in
829 ( select wf_name
830 from OKC_PROCESS_DEFS_B
831 where USAGE='CHG_REQ_APPROVE' and PDF_TYPE = 'WPS')
832 and item_key = l_key
833 and end_date is NULL;
834 begin
835 MO_GLOBAL.INIT('OKS');
836 --
837 -- start activity
838 --
839 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
840 G_PKG_NAME,
841 p_init_msg_list,
842 l_api_version,
843 p_api_version,
844 G_LEVEL,
845 x_return_status);
846 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
847 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
849 RAISE OKC_API.G_EXCEPTION_ERROR;
850 END IF;
851 --
852 -- get values
853 --
854 open key_csr;
855 fetch key_csr into l_contract_id, l_key;
856 close key_csr;
857 --
858 -- user have U privilege?
859 --
860 if OKC_CONTRACT_APPROVAL_PUB.k_accesible(
861 p_contract_id => l_contract_id,
862 p_user_id => fnd_global.user_id,
863 p_level => 'U'
864 ) = OKC_API.G_FALSE
865 then
866 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
867 p_msg_name => G_NO_U_PRIVILEGE);
868 raise OKC_API.G_EXCEPTION_ERROR;
869 end if;
870 --
871 -- get active wf_item
872 --
873 open approval_active_csr;
874 fetch approval_active_csr into l_wf_name_active;
875 close approval_active_csr;
876 --
877 if l_wf_name_active is NULL then
878 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
879 p_msg_name => G_CRT_NOT_ON_APPROVAL);
880 raise OKC_API.G_EXCEPTION_ERROR;
881 end if;
882 wf_engine.abortprocess(l_wf_name_active,l_key);
883 if (p_do_commit = OKC_API.G_TRUE) then
884 commit;
885 end if;
886 x_return_status := OKC_API.G_RET_STS_SUCCESS;
887 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
888 EXCEPTION
889 WHEN OKC_API.G_EXCEPTION_ERROR THEN
890 x_return_status := OKC_API.HANDLE_EXCEPTIONS
891 (substr(l_api_name,1,26),
892 G_PKG_NAME,
893 'OKC_API.G_RET_STS_ERROR',
894 x_msg_count,
895 x_msg_data,
896 G_LEVEL);
897 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
898 x_return_status := OKC_API.HANDLE_EXCEPTIONS
899 (substr(l_api_name,1,26),
900 G_PKG_NAME,
901 'OKC_API.G_RET_STS_UNEXP_ERROR',
902 x_msg_count,
903 x_msg_data,
904 G_LEVEL);
905 WHEN OTHERS THEN
906 x_return_status := OKC_API.HANDLE_EXCEPTIONS
907 (substr(l_api_name,1,26),
908 G_PKG_NAME,
909 'OTHERS',
910 x_msg_count,
911 x_msg_data,
912 G_LEVEL);
913 end change_approval_stop;
914
915 -- Start of comments
916 --
917 -- Procedure Name : change_get_key
918 -- Description :
919 -- Business Rules :
920 -- Parameters :
921 -- Version : 1.0
922 -- End of comments
923
924 procedure change_get_key(
925 p_api_version IN NUMBER,
926 p_init_msg_list IN VARCHAR2 ,
927 x_return_status OUT NOCOPY VARCHAR2,
928 x_msg_count OUT NOCOPY NUMBER,
929 x_msg_data OUT NOCOPY VARCHAR2,
930 p_change_request_id IN number,
931 p_do_commit IN VARCHAR2
932 ) is
933 l_api_name CONSTANT VARCHAR2(30) := 'change_get_key';
934 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
935 l_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
936 l_cpsv_rec OKC_CONTRACT_PUB.cpsv_rec_type;
937 l1_cpsv_rec OKC_CONTRACT_PUB.cpsv_rec_type;
938 l_dummy varchar2(1) := '?';
939 --
940 cursor in_use_csr is
941 select '!'
942 from
943 OKC_CHANGE_REQUESTS_B C,
944 OKC_K_PROCESSES P
945 where C.chr_id = (select chr_id from OKC_CHANGE_REQUESTS_B
946 where id = p_change_request_id)
947 and C.ID <> p_change_request_id
948 and C.datetime_applied is NULL
949 and P.crt_id = C.id
950 and P.in_process_yn = 'Y';
951 --
952 cursor process_csr is
953 select id,object_version_number from OKC_K_PROCESSES
954 where crt_id = p_change_request_id;
955 begin
956 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
957 G_PKG_NAME,
958 p_init_msg_list,
959 l_api_version,
960 p_api_version,
961 G_LEVEL,
962 x_return_status);
963 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
964 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
965 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
966 RAISE OKC_API.G_EXCEPTION_ERROR;
967 END IF;
968 --
969 l_crtv_rec.id := p_change_request_id;
970 OKC_CHANGE_REQUEST_PUB.lock_change_request(
971 p_api_version => p_api_version,
972 x_return_status => l_return_status,
973 x_msg_count => x_msg_count,
974 x_msg_data => x_msg_data,
975 p_restricted => OKC_API.G_FALSE,
976 p_crtv_rec => l_crtv_rec);
977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
980 RAISE OKC_API.G_EXCEPTION_ERROR;
981 END IF;
982 open in_use_csr;
983 fetch in_use_csr into l_dummy;
984 close in_use_csr;
985 IF (l_dummy = '!') then
986 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
987 RAISE OKC_API.G_EXCEPTION_ERROR;
988 END IF;
989 open process_csr;
990 fetch process_csr into l_cpsv_rec.id,l_cpsv_rec.object_version_number;
991 close process_csr;
992 OKC_CONTRACT_PUB.lock_contract_process(
993 p_api_version => p_api_version,
994 x_return_status => l_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_cpsv_rec => l_cpsv_rec);
998 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
999 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1000 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1001 RAISE OKC_API.G_EXCEPTION_ERROR;
1002 END IF;
1003 l_cpsv_rec.in_process_yn := 'Y';
1004 l_cpsv_rec.user_id := fnd_global.user_id;
1005 OKC_CONTRACT_PUB.update_contract_process(
1006 p_api_version => p_api_version,
1007 x_return_status => l_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_cpsv_rec => l_cpsv_rec,
1011 x_cpsv_rec => l1_cpsv_rec);
1012 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1013 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1014 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1015 RAISE OKC_API.G_EXCEPTION_ERROR;
1016 END IF;
1017 --
1018 if (p_do_commit = OKC_API.G_TRUE) then
1019 commit;
1020 end if;
1021 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1022 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1023 EXCEPTION
1024 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1025 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1026 (substr(l_api_name,1,26),
1027 G_PKG_NAME,
1028 'OKC_API.G_RET_STS_ERROR',
1029 x_msg_count,
1030 x_msg_data,
1031 G_LEVEL);
1032 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1033 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1034 (substr(l_api_name,1,26),
1035 G_PKG_NAME,
1036 'OKC_API.G_RET_STS_UNEXP_ERROR',
1037 x_msg_count,
1038 x_msg_data,
1039 G_LEVEL);
1040 WHEN OTHERS THEN
1041 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1042 (substr(l_api_name,1,26),
1043 G_PKG_NAME,
1044 'OTHERS',
1045 x_msg_count,
1046 x_msg_data,
1047 G_LEVEL);
1048 end change_get_key;
1049
1050 -- Start of comments
1051 --
1052 -- Procedure Name : change_put_key
1053 -- Description :
1054 -- Business Rules :
1055 -- Parameters :
1056 -- Version : 1.0
1057 -- End of comments
1058
1059 procedure change_put_key(
1060 p_api_version IN NUMBER,
1061 p_init_msg_list IN VARCHAR2 ,
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2,
1065 p_change_request_id IN number,
1066 p_datetime_applied IN date ,
1067 p_k_version IN VARCHAR2,
1068 p_do_commit IN VARCHAR2
1069 ) is
1070 l_api_name CONSTANT VARCHAR2(30) := 'change_put_key';
1071 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1072 l_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1073 l1_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1074 l_cpsv_rec OKC_CONTRACT_PUB.cpsv_rec_type;
1075 l1_cpsv_rec OKC_CONTRACT_PUB.cpsv_rec_type;
1076 --
1077 cursor process_csr is
1078 select id,object_version_number from OKC_K_PROCESSES
1079 where crt_id = p_change_request_id;
1080 begin
1081 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1082 G_PKG_NAME,
1083 p_init_msg_list,
1084 l_api_version,
1085 p_api_version,
1086 G_LEVEL,
1087 x_return_status);
1088 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1089 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1090 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1091 RAISE OKC_API.G_EXCEPTION_ERROR;
1092 END IF;
1093 --
1094 l_crtv_rec.id := p_change_request_id;
1095 OKC_CHANGE_REQUEST_PUB.lock_change_request(
1096 p_api_version => p_api_version,
1097 x_return_status => l_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 p_restricted => OKC_API.G_FALSE,
1101 p_crtv_rec => l_crtv_rec);
1102 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1103 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1104 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1105 RAISE OKC_API.G_EXCEPTION_ERROR;
1106 END IF;
1107 --
1108 open process_csr;
1109 fetch process_csr into l_cpsv_rec.id,l_cpsv_rec.object_version_number;
1110 close process_csr;
1111 OKC_CONTRACT_PUB.lock_contract_process(
1112 p_api_version => p_api_version,
1113 x_return_status => l_return_status,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data,
1116 p_cpsv_rec => l_cpsv_rec);
1117 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1118 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1119 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1120 RAISE OKC_API.G_EXCEPTION_ERROR;
1121 END IF;
1122 --
1123 l_cpsv_rec.in_process_yn := 'N';
1124 OKC_CONTRACT_PUB.update_contract_process(
1125 p_api_version => p_api_version,
1126 x_return_status => l_return_status,
1127 x_msg_count => x_msg_count,
1128 x_msg_data => x_msg_data,
1129 p_cpsv_rec => l_cpsv_rec,
1130 x_cpsv_rec => l1_cpsv_rec);
1131 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1132 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1133 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1134 RAISE OKC_API.G_EXCEPTION_ERROR;
1135 END IF;
1136 l_crtv_rec.datetime_applied := p_datetime_applied;
1137 l_crtv_rec.applied_contract_version := p_k_version;
1138 OKC_CHANGE_REQUEST_PUB.update_change_request(
1139 p_api_version => p_api_version,
1140 x_return_status => l_return_status,
1141 x_msg_count => x_msg_count,
1142 x_msg_data => x_msg_data,
1143 p_crtv_rec => l_crtv_rec,
1144 x_crtv_rec => l1_crtv_rec);
1145 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1146 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1148 RAISE OKC_API.G_EXCEPTION_ERROR;
1149 END IF;
1150 --
1151 if (p_do_commit = OKC_API.G_TRUE) then
1152 commit;
1153 end if;
1154 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1155 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1156 EXCEPTION
1157 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1158 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1159 (substr(l_api_name,1,26),
1160 G_PKG_NAME,
1161 'OKC_API.G_RET_STS_ERROR',
1162 x_msg_count,
1163 x_msg_data,
1164 G_LEVEL);
1165 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1166 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1167 (substr(l_api_name,1,26),
1168 G_PKG_NAME,
1169 'OKC_API.G_RET_STS_UNEXP_ERROR',
1170 x_msg_count,
1171 x_msg_data,
1172 G_LEVEL);
1173 WHEN OTHERS THEN
1174 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1175 (substr(l_api_name,1,26),
1176 G_PKG_NAME,
1177 'OTHERS',
1178 x_msg_count,
1179 x_msg_data,
1180 G_LEVEL);
1181 end change_put_key;
1182
1183 -- for wf development
1184
1185 --
1186 -- private procedure
1187 -- to set context of db failure
1188 --
1189 procedure db_failed(p_oper varchar2) is
1190 begin
1191 FND_MESSAGE.SET_NAME(application => G_APP_NAME,
1192 name => 'OKC_DB_OPERATION_FAILED');
1193 -- OKC_CH_APPROVE OKC_CH_REJECT --OKC_SIGN OKC_APPROVE OKC_REVOKE
1194 FND_MESSAGE.SET_TOKEN(token => 'OPERATION',
1195 value => p_oper,
1196 translate => TRUE);
1197 FND_MSG_PUB.add;
1198 end db_failed;
1199
1200 -- Start of comments
1201 --
1202 -- Procedure Name : change_request_approved
1203 -- Description :
1204 -- Business Rules :
1205 -- Parameters :
1206 -- Version : 1.0
1207 -- End of comments
1208
1209 procedure change_request_approved(
1210 p_change_request_id IN number,
1211 x_return_status OUT NOCOPY VARCHAR2
1212 ) is
1213 l_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1214 l1_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1215 l_msg_count NUMBER;
1216 l_msg_data varchar2(2000);
1217 cursor lock_csr is
1218 select ID
1219 from okc_change_requests_B
1220 where ID = p_change_request_id
1221 for update of crs_code, datetime_approved, datetime_rejected
1222 nowait;
1223 Cursor Cur_header is
1224 select k.id,k.contract_number,k.contract_number_modifier,k.scs_code,
1225 scs.cls_code,k.estimated_amount,k.sts_code
1226 from okc_k_headers_b k,
1227 okc_subclasses_b scs,
1228 okc_change_requests_b crt
1229 where crt.chr_id = k.id
1230 and k.scs_code = scs.code
1231 and crt.id = p_change_request_id;
1232 l_chr_id number;
1233 l_contract_number okc_k_headers_v.contract_number%type;
1234 l_k_status_code okc_k_headers_v.sts_code%type;
1235 l_contract_modifier okc_k_headers_v.contract_number_modifier%type;
1236 begin
1237 MO_GLOBAL.INIT('OKS');
1238 savepoint change_request_approved;
1239 open lock_csr;
1240 fetch lock_csr into l_crtv_rec.id;
1241 close lock_csr;
1242 l_crtv_rec.datetime_approved := sysdate;
1243 l_crtv_rec.datetime_rejected := NULL;
1244 l_crtv_rec.crs_code := 'APP';
1245 OKC_CHANGE_REQUEST_PUB.update_change_request(
1246 p_api_version => l_api_version,
1247 x_return_status => x_return_status,
1248 x_msg_count => l_msg_count,
1249 x_msg_data => l_msg_data,
1250 p_crtv_rec => l_crtv_rec,
1251 x_crtv_rec => l1_crtv_rec);
1252 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1253 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1254 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1255 RAISE OKC_API.G_EXCEPTION_ERROR;
1256 END IF;
1257
1258 OPEN cur_header;
1259 FETCH cur_header into l_chr_id,l_contract_number,l_contract_modifier,l_scs_code,l_cls_code,
1260 l_estimated_amount,l_k_status_code;
1261 CLOSE cur_header;
1262
1263 OKC_CHG_APR_ASMBLR_PVT.acn_assemble(p_api_version => 1,
1264 p_init_msg_list => OKC_API.G_FALSE,
1265 x_return_status => x_return_status,
1266 x_msg_count => l_msg_count,
1267 x_msg_data => l_msg_data,
1268 p_k_class => l_cls_code,
1269 p_k_subclass => l_scs_code,
1270 p_k_status_code => l_k_status_code,
1271 p_estimated_amount => l_estimated_amount,
1272 p_k_id => l_chr_id,
1273 p_k_number => l_contract_number,
1274 p_k_nbr_mod => l_contract_modifier,
1275 p_chapp_date => sysdate,
1276 p_change_id => p_change_request_id
1277 );
1278
1279 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1280 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1281 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
1282 RAISE OKC_API.G_EXCEPTION_ERROR;
1283 END IF;
1284 exception
1285 when OKC_API.G_EXCEPTION_ERROR then
1286 rollback to change_request_approved;
1287 db_failed('OKC_CH_APPROVE');
1288 x_return_status := OKC_API.G_RET_STS_ERROR;
1289 when others then
1290 rollback to change_request_approved;
1291 db_failed('OKC_CH_APPROVE');
1292 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1293 end change_request_approved;
1294
1295 -- Start of comments
1296 --
1297 -- Procedure Name : change_request_rejected
1298 -- Description :
1299 -- Business Rules :
1300 -- Parameters :
1301 -- Version : 1.0
1302 -- End of comments
1303
1304 procedure change_request_rejected(
1305 p_change_request_id IN number,
1306 x_return_status OUT NOCOPY VARCHAR2
1307 ) is
1308 l_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1309 l1_crtv_rec OKC_CHANGE_REQUEST_PUB.crtv_rec_type;
1310 l_msg_count NUMBER;
1311 l_msg_data varchar2(2000);
1312 cursor lock_csr is
1313 select ID
1314 from okc_change_requests_b
1315 where ID = p_change_request_id
1316 for update of crs_code, datetime_approved, datetime_rejected
1317 nowait;
1318 Cursor Cur_header is
1319 select k.id,k.contract_number,k.contract_number_modifier,k.scs_code,scs.cls_code,
1320 k.estimated_amount,k.sts_code
1321 from okc_k_headers_b k,
1322 okc_subclasses_b scs,
1323 okc_change_requests_b crt
1324 where crt.chr_id = k.id
1325 and k.scs_code = scs.code
1326 and crt.id = p_change_request_id;
1327
1328 l_chr_id number;
1329 l_contract_number okc_k_headers_v.contract_number%type;
1330 l_k_status_code okc_k_headers_v.sts_code%type;
1331 l_contract_modifier okc_k_headers_v.contract_number_modifier%type;
1332 begin
1333 MO_GLOBAL.INIT('OKS');
1334 savepoint change_request_rejected;
1335 open lock_csr;
1336 fetch lock_csr into l_crtv_rec.id;
1337 close lock_csr;
1338 l_crtv_rec.datetime_approved := NULL;
1339 l_crtv_rec.datetime_rejected := sysdate;
1340 l_crtv_rec.crs_code := 'REJ';
1341 OKC_CHANGE_REQUEST_PUB.update_change_request(
1342 p_api_version => l_api_version,
1343 x_return_status => x_return_status,
1344 x_msg_count => l_msg_count,
1345 x_msg_data => l_msg_data,
1346 p_crtv_rec => l_crtv_rec,
1347 x_crtv_rec => l1_crtv_rec);
1348 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1349 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1350 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1351 RAISE OKC_API.G_EXCEPTION_ERROR;
1352 END IF;
1353 OPEN cur_header;
1354 FETCH cur_header into l_chr_id,l_contract_number,l_contract_modifier,l_scs_code,
1355 l_cls_code,l_estimated_amount,l_k_status_code;
1356 CLOSE cur_header;
1357 OKC_CHG_REJ_ASMBLR_PVT.acn_assemble(p_api_version => 1,
1358 p_init_msg_list => OKC_API.G_FALSE,
1359 x_return_status => x_return_status,
1360 x_msg_count => l_msg_count,
1361 x_msg_data => l_msg_data,
1362 p_k_id => l_chr_id,
1363 p_k_number => l_contract_number,
1364 p_k_nbr_mod => l_contract_modifier,
1365 p_chrej_date => sysdate,
1366 p_change_id => p_change_request_id,
1367 p_k_class => l_cls_code,
1368 p_k_subclass => l_scs_code,
1369 p_k_status_code => l_k_status_code,
1370 p_estimated_amount => l_estimated_amount
1371 );
1372
1373 IF x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1374 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1375 ELSIF x_return_status = OKC_API.G_RET_STS_ERROR THEN
1376 RAISE OKC_API.G_EXCEPTION_ERROR;
1377 END IF;
1378 exception
1379 when OKC_API.G_EXCEPTION_ERROR then
1380 rollback to change_request_rejected;
1381 db_failed('OKC_CH_REJECT');
1382 x_return_status := OKC_API.G_RET_STS_ERROR;
1383 when others then
1384 rollback to change_request_rejected;
1385 db_failed('OKC_CH_REJECT');
1386 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1387 end change_request_rejected;
1388
1389 end OKC_CHANGE_CONTRACT_PVT;