[Home] [Help]
PACKAGE BODY: APPS.OKC_EXTEND_PVT
Source
1 PACKAGE BODY OKC_EXTEND_PVT as
2 /* $Header: OKCREXTB.pls 120.2.12000000.2 2007/03/08 11:17:19 skgoud ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- /striping/
7 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
8 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
9 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
10
11 FUNCTION is_k_extend_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
12
13 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
14 will take place everytime a change happens to a contract or not. That is
15 If the status of the contract showing in launchpad would at all times be in sync
16 with database.It might not happen due to performance reasons of launchpad. So the current approach.
17 But if this sync is assured then we could use p_sts_code as well*/
18
19 l_sts_code VARCHAR2(100);
20 l_cls_code VARCHAR2(100);
21 l_template_yn VARCHAR2(10);
22 l_code VARCHAR2(100);
23 l_end_date okc_k_headers_b.end_date%TYPE;
24 l_app_id okc_k_headers_b.application_id%TYPE;
25 l_scs_code okc_k_headers_b.scs_code%TYPE;
26 l_k VARCHAR2(255);
27 l_mod okc_k_headers_b.contract_number_modifier%TYPE ;
28
29
30 CURSOR c_chr IS
31 SELECT sts_code,template_yn,end_date,application_id,scs_code ,contract_number,
32 contract_number_modifier
33 FROM okc_k_headers_b
34 WHERE id = p_chr_id;
35
36 CURSOR c_sts(p_code IN VARCHAR2) IS
37 SELECT ste_code
38 FROM okc_statuses_b
39 WHERE code = p_code;
40
41 BEGIN
42
43 OPEN c_chr;
44 FETCH c_chr INTO l_code,l_template_yn,l_end_date,l_app_id,l_scs_code,l_k,l_mod;
45 CLOSE c_chr;
46
47 IF (l_mod is not null) and (l_mod <> OKC_API.G_MISS_CHAR) then
48 l_k := l_k ||'-'||l_mod;
49 END IF;
50
51
52 IF l_template_yn = 'Y' then
53 OKC_API.set_message(p_app_name => g_app_name,
54 p_msg_name => 'OKC_K_TEMPLATE',
55 p_token1 => 'NUMBER',
56 p_token1_value => l_k);
57
58 RETURN(FALSE);
59 END IF;
60
61 -- A perpetual cannot be extended further !!
62 IF l_end_date Is Null then
63 OKC_API.set_message(p_app_name => g_app_name,
64 p_msg_name => 'OKC_NO_PERPETUAL',
65 p_token1 => 'component',
66 p_token1_value => l_k);
67 RETURN(FALSE);
68 END IF;
69
70 -- If there is Update access, do not allow extend
71 If Okc_Util.Get_All_K_Access_Level(p_application_id => l_app_id,
72 p_chr_id => p_chr_id,
73 p_scs_code => l_scs_code) <> 'U' Then
74 OKC_API.set_message(p_app_name => g_app_name,
75 p_msg_name => 'OKC_NO_UPDATE',
76 p_token1 => 'CHR',
77 p_token1_value => l_k);
78 RETURN(FALSE);
79 END IF;
80
81 OPEN c_sts(l_code);
82 FETCH c_sts INTO l_sts_code;
83 CLOSE c_sts;
84
85 IF l_sts_code NOT IN ('ACTIVE','EXPIRED','SIGNED') THEN
86 OKC_API.set_message(p_app_name => g_app_name,
87 p_msg_name => 'OKC_INVALID_STS',
88 p_token1 => 'component',
89 p_token1_value => l_k);
90 RETURN(FALSE);
91 END IF;
92
93 RETURN(TRUE);
94 END is_k_extend_allowed;
95
96
97 FUNCTION is_kl_extend_allowed(p_cle_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
98
99 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
100 will take place everytime a change happens to a contract or not. That is
101 If the status of the contract showing in launchpad would at all times be in sync
102 with database.It might not happen due to performance reasons of launchpad. So the current approach.
103 But if this sync is assured then we could use p_sts_code as well*/
104
105 l_sts_code VARCHAR2(100);
106 l_cls_code VARCHAR2(100);
107 l_template_yn VARCHAR2(10);
108 l_code VARCHAR2(100);
109 l_chr_id number:=OKC_API.G_MISS_NUM;
110 l_app_id okc_k_headers_b.application_id%TYPE;
111 l_scs_code okc_k_headers_b.scs_code%TYPE;
112 l_k VARCHAR2(255);
113 l_mod okc_k_headers_b.contract_number_modifier%TYPE ;
114 l_no okc_k_lines_b.line_number%TYPE;
115 l_end_date okc_k_lines_b.end_date%TYPE;
116
117 CURSOR c_chr(p_chr_id number) IS
118 SELECT template_yn, application_id, scs_code ,contract_number,
119 contract_number_modifier
120 FROM okc_k_headers_b
121 WHERE id = p_chr_id;
122
123 CURSOR c_cle IS
124 SELECT sts_code,dnz_chr_id ,line_number,end_date
125 FROM okc_k_lines_b
126 WHERE id = p_cle_id;
127
128 CURSOR c_sts(p_code IN VARCHAR2) IS
129 SELECT ste_code
130 FROM okc_statuses_b
131 WHERE code = p_code;
132
133
134 BEGIN
135
136 OPEN c_cle;
137 FETCH c_cle INTO l_code,l_chr_id,l_no,l_end_date;
138 CLOSE c_cle;
139
140 If l_chr_id=OKC_API.G_MISS_NUM then
141 OKC_API.set_message(p_app_name => g_app_name,
142 p_msg_name => 'OKC_NO_CHR',
143 p_token1 => 'LINE',
144 p_token1_value => l_no);
145 RETURN (FALSE);
146 END IF;
147
148 -- A perpetual cannot be extended further !!
149 IF l_end_date Is Null then
150 OKC_API.set_message(p_app_name => g_app_name,
151 p_msg_name => 'OKC_NO_PERPETUAL',
152 p_token1 => 'component',
153 p_token1_value => l_no);
154 RETURN(FALSE);
155 END IF;
156
157 OPEN c_chr(l_chr_id);
158 FETCH c_chr INTO l_template_yn, l_app_id, l_scs_code,l_k,l_mod;
159 CLOSE c_chr;
160
161 IF l_template_yn = 'Y' then
162 OKC_API.set_message(p_app_name => g_app_name,
163 p_msg_name => 'OKC_K_TEMPLATE',
164 p_token1 => 'number',
165 p_token1_value => l_k);
166 RETURN(FALSE);
167 END IF;
168
169 If Okc_Util.Get_All_K_Access_Level(p_application_id => l_app_id,
170 p_chr_id => l_chr_id,
171 p_scs_code => l_scs_code) <> 'U' Then
172 OKC_API.set_message(p_app_name => g_app_name,
173 p_msg_name => 'OKC_NO_UPDATE',
174 p_token1 => 'CHR',
175 p_token1_value => l_k);
176 RETURN(FALSE);
177 END IF;
178
179 OPEN c_sts(l_code);
180 FETCH c_sts INTO l_sts_code;
181 CLOSE c_sts;
182
183 IF l_sts_code NOT IN ('ACTIVE','EXPIRED','ENTERED','SIGNED') THEN
184 OKC_API.set_message(p_app_name => g_app_name,
185 p_msg_name => 'OKC_INVALID_STS',
186 p_token1 => 'component',
187 p_token1_value => l_no);
188 RETURN(FALSE);
189 END IF;
190
191 RETURN(TRUE);
192 END is_kl_extend_allowed;
193
194 -- Added for Bug 2648677/2346862
195 -- p_pdf id is for Process Defn id for seeded procedure
196 -- p_chr_id is Contract id (always required) for Contract Header Extend
197 -- p_cle_id is Contract Line id (optional ) for Contract Header Extend it is
198 -- NULL and for Contract Line Extend is required
199
200 PROCEDURE OKC_CREATE_PLSQL (p_pdf_id IN NUMBER,
201 x_string OUT NOCOPY VARCHAR2) IS
202
203 l_string VARCHAR2(2000);
204
205 -- Cursor to get the package.procedure name from PDF
206 CURSOR pdf_cur(l_pdf_id IN NUMBER) IS
207 SELECT
208 decode(pdf.pdf_type,'PPS',
209 pdf.package_name||'.'||pdf.procedure_name,NULL) proc_name
210 FROM okc_process_defs_v pdf
211 WHERE pdf.id = l_pdf_id;
212
213 pdf_rec pdf_cur%ROWTYPE;
214
215 BEGIN
216 OPEN pdf_cur(p_pdf_id);
217 FETCH pdf_cur INTO pdf_rec;
218 CLOSE pdf_cur;
219
220 l_string := l_string||pdf_rec.proc_name;
221 x_string := l_string ;
222
223 END OKC_CREATE_PLSQL;
224
225
226 PROCEDURE validate_chr( p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 ,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2,
231 p_extend_in_parameters_rec IN extend_in_parameters_rec ) is
232
233 CURSOR cur_k_header is
234 SELECT STS_CODE,
235 CONTRACT_NUMBER,
236 CONTRACT_NUMBER_MODIFIER,
237 TEMPLATE_YN,
238 DATE_TERMINATED,
239 DATE_RENEWED,
240 END_DATE
241 FROM okc_k_headers_b
242 WHERE id = p_extend_in_parameters_rec.p_contract_id;
243
244 CURSOR is_k_locked is
245 SELECT 'Y'
246 FROM okc_k_processes v
247 WHERE v.chr_id = p_extend_in_parameters_rec.p_contract_id
248 AND v.in_process_yn='Y';
249
250 CURSOR cur_status(p_sts_code varchar2) is
251 SELECT ste_code
252 FROM okc_statuses_b
253 WHERE code = p_sts_code;
254
255 CURSOR cur_mean(p_sts_code varchar2) is
256 SELECT meaning
257 FROM okc_statuses_v
258 WHERE code = p_sts_code;
259
260 l_chg_request_in_process varchar2(1);
261 l_status varchar2(30);
262 l_status_meaning okc_statuses_v.meaning%type;
263 l_return_status varchar2(1) := OKC_API.g_ret_sts_success;
264 l_chr_rec cur_k_header%rowtype;
265
266 BEGIN
267
268 x_return_status := okc_api.g_ret_sts_success;
269
270 OKC_API.init_msg_list(p_init_msg_list);
271
272 OPEN cur_k_header;
273 FETCH cur_k_header into l_chr_rec;
274 CLOSE cur_k_header;
275
276 If p_extend_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE Then
277 IF l_chr_rec.end_date >= p_extend_in_parameters_rec.p_end_date then
278
279 OKC_API.set_message( p_app_name => g_app_name,
280 p_msg_name => 'OKC_INVALID_EXTEND_DATE');
281
282 x_return_status := okc_api.g_ret_sts_error;
283 RAISE g_exception_halt_validation;
284
285 END IF;
286 END IF;
287
288 /* Templates can not be extended */
289 IF l_chr_rec.template_Yn = 'Y' THEN
290
291 OKC_API.set_message( p_app_name => g_app_name,
292 p_msg_name => 'OKC_K_TEMPLATE',
293 p_token1 => 'NUMBER',
294 p_token1_value => l_chr_rec.contract_number );
295
296 x_return_status := okc_api.g_ret_sts_error;
297 RAISE g_exception_halt_validation;
298 end IF;
299
300 OPEN is_k_locked;
301 FETCH is_k_locked into l_chg_request_in_process;
302
303 IF is_k_locked%found THEN
304
305 OKC_API.set_message(p_app_name => g_app_name,
306 p_msg_name => 'OKC_K_LOCKED',
307 p_token1 => 'NUMBER',
308 p_token1_value => l_chr_rec.contract_number);
309
310 x_return_status := okc_api.g_ret_sts_error;
311 CLOSE is_k_locked;
312 RAISE g_exception_halt_validation;
313
314 end IF;
315
316 CLOSE is_k_locked;
317
318 l_status:='1';
319 OPEN cur_status(l_chr_rec.sts_code);
320 FETCH cur_status into l_status;
321 CLOSE cur_status;
322 IF l_status='1' then
323 OKC_API.set_message(p_app_name => g_app_name,
324 p_msg_name => 'OKC_INVALID_K_STATUS',
325 p_token1 => 'NUMBER',
326 p_token1_value => l_chr_rec.contract_number,
327 p_token2 => 'MODIFIER',
328 p_token2_value => l_chr_rec.contract_number_modifier,
329 p_token3 => 'STATUS',
330 p_token3_value => l_chr_rec.sts_code);
331
332 RAISE g_exception_halt_validation;
333 END IF;
334
335 OPEN cur_mean(l_status);
336 FETCH cur_mean into l_status_meaning;
337 CLOSE cur_mean;
338
339 IF l_status NOT IN ('ACTIVE','EXPIRED','SIGNED') THEN
340
341 x_return_status := OKC_API.G_RET_STS_ERROR;
342
343 OKC_API.set_message(p_app_name => g_app_name,
344 p_msg_name => 'OKC_INVALID_K_STATUS',
345 p_token1 => 'NUMBER',
346 p_token1_value => l_chr_rec.contract_number,
347 p_token2 => 'MODIFIER',
348 p_token2_value => l_chr_rec.contract_number_modifier,
349 p_token3 => 'STATUS',
350 p_token3_value => l_status_meaning);
351
352 RAISE g_exception_halt_validation;
353 ELSIF l_chr_rec.date_terminated is not null THEN
354
355 x_return_status := OKC_API.G_RET_STS_ERROR;
356
357 OKC_API.set_message(p_app_name => g_app_name,
358 p_msg_name => 'OKC_FUTURE_TERMINATED_K',
359 p_token1 => 'NUMBER',
360 p_token1_value => l_chr_rec.contract_number );
361
362 RAISE g_exception_halt_validation;
363 ELSIF l_chr_rec.date_renewed is not null THEN -- as per connie 11-22-1999
364
365 x_return_status := OKC_API.G_RET_STS_ERROR;
366
367 OKC_API.set_message( p_app_name => g_app_name,
368 p_msg_name =>'OKC_RENEWED_CONTRACT',
369 p_token1 =>'NUMBER',
370 p_token1_value => l_chr_rec.contract_number );
371
372 RAISE g_exception_halt_validation;
373 END IF;
374
375 If p_extend_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE Then
376 IF ( p_extend_in_parameters_rec.p_end_date is null )
377 AND
378 ( (p_extend_in_parameters_rec.p_uom_code is null)
379 OR
380 (p_extend_in_parameters_rec.p_duration is null)
381 )
382 THEN
383
384 OKC_API.set_message(p_app_name => g_app_name,
385 p_msg_name => 'OKC_INVALID_PARAMETERS');
386
387 x_return_status := OKC_API.g_ret_sts_error;
388 RAISE g_exception_halt_validation;
389 END IF;
390 END IF;
391
392 EXCEPTION
393 WHEN G_EXCEPTION_HALT_VALIDATION THEN
394 NULL;
395 WHEN OTHERS THEN
396 OKC_API.set_message(p_app_name => g_app_name,
397 p_msg_name => g_unexpected_error,
398 p_token1 => g_sqlcode_token,
399 p_token1_value => sqlcode,
400 p_token2 => g_sqlerrm_token,
401 p_token2_value => sqlerrm);
402 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
403 end validate_chr;
404
405 PROCEDURE validate_cle( p_api_version IN NUMBER,
406 p_init_msg_list IN VARCHAR2 ,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 p_extend_in_parameters_rec IN extend_in_cle_rec ) is
411
412 -- bug # 5757116 added trunc in the where clause of the cursor.
413 CURSOR cur_k_lines is
414 SELECT cle.line_number,cle.item_description,cle.id,cle.lse_id,cle.sts_code,cle.dnz_chr_id,
415 cle.object_version_number,cle.date_terminated,cle.end_date,sts.ste_code
416 FROM okc_k_lines_v cle,
417 okc_statuses_b sts
418 WHERE cle.id = p_extend_in_parameters_rec.p_cle_id
419 AND trunc(cle.end_date) = trunc(p_extend_in_parameters_rec.p_orig_end_date)
420 AND cle.sts_code = sts.code;
421
422 CURSOR cur_mean(p_sts_code varchar2) is
423 SELECT meaning
424 FROM okc_statuses_v
425 WHERE code = p_sts_code;
426
427 Cursor cur_k_end_date(p_id number) is
428 SELECT end_date+1 from okc_k_headers_b
429 WHERE id=p_id;
430
431 k_lines_rec cur_k_lines%rowtype;
432
433 l_return_status varchar2(1) := OKC_API.g_ret_sts_success;
434
435 l_status_meaning okc_statuses_v.meaning%type;
436 l_extend_in_parameters_rec extend_in_parameters_rec;
437
438 BEGIN
439 --dbms_output.put_line('validate cle (+) ');
440 x_return_status := okc_api.g_ret_sts_success;
441
442 OKC_API.init_msg_list(p_init_msg_list);
443
444 OPEN cur_k_lines;
445 FETCH cur_k_lines into k_lines_rec;
446
447 IF cur_k_lines%notfound THEN -- contract header_id is wrong
448
449 OKC_API.set_message(p_app_name => g_app_name,
450 p_msg_name => 'OKC_LINE_ID_MISSING',
451 p_token1 => 'LINE_NUMBER',
452 p_token1_value => k_lines_rec.line_number,
453 p_token2 => 'DESCRIPTION',
454 p_token2_value => k_lines_rec.item_description);
455
456 CLOSE cur_k_lines;
457 x_return_status := okc_api.g_ret_sts_error;
458 RAISE g_exception_halt_validation;
459 end IF;
460
461 CLOSE cur_k_lines;
462
463 -- Commenting out the following since this check is also performed
464 -- at the end of this procedure.
465 /* IF k_lines_rec.end_date >= p_extend_in_parameters_rec.p_end_date then
466
467 OKC_API.set_message( p_app_name => g_app_name,
468 p_msg_name => 'OKC_INVALID_EXTEND_DATE');
469
470 x_return_status := okc_api.g_ret_sts_error;
471 RAISE g_exception_halt_validation;
472
473 END IF; */
474
475 IF g_lines_count = 0 THEN -- validate header only once
476
477 g_lines_count := 1;
478
479 --san
480 -- Since 'the new enddate for contract should be greater than existing end date for contract' check
481 -- has been already performed when we extend the header in extend_cle in pub if new end_date
482 --for line is greater than end date of header. There is no need
483 --to check the end_date of the line against the end date of header
484 --as this will return false error when new end date of line is less or equal
485 --false error because a line can end before contract ends
486 --so populate with the existing header end_date+1 to avoid that false error
487 --coming from validate_chr
488 --end san
489
490 Open cur_k_end_date(k_lines_rec.dnz_chr_id);
491 FETCH cur_k_end_date into l_extend_in_parameters_rec.p_end_date;
492 Close cur_k_end_date;
493
494
495 l_extend_in_parameters_rec.p_contract_id := k_lines_rec.dnz_chr_id;
496
497 If l_extend_in_parameters_rec.p_end_date Is Null Then
498 l_extend_in_parameters_rec.p_perpetual_flag := OKC_API.G_TRUE;
499 Else
500 l_extend_in_parameters_rec.p_perpetual_flag := OKC_API.G_FALSE;
501 End If;
502
503 --san its wrong to populate the new_end_date of line in new end_date of contract
504 --as these are two differnet dates and may or maynot be same
505 --so commented the following and wrote the above cursor instead
506
507 --l_extend_in_parameters_rec.p_uom_code := p_extend_in_parameters_rec.p_uom_code;
508 --l_extend_in_parameters_rec.p_duration := p_extend_in_parameters_rec.p_duration;
509 --l_extend_in_parameters_rec.p_end_date := p_extend_in_parameters_rec.p_end_date;
510
511 --dbms_output.put_line('validate chr (+) ');
512
513 OKC_EXTEND_PVT.validate_chr(p_api_version => 1,
514 p_init_msg_list => OKC_API.G_FALSE,
515 x_return_status => l_return_status,
516 x_msg_count => x_msg_count,
517 x_msg_data => x_msg_data,
518 p_extend_in_parameters_rec => l_extend_in_parameters_rec );
519
520 --dbms_output.put_line('validate chr (-) ');
521 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
522 x_return_status := l_return_status;
523 RAISE G_EXCEPTION_HALT_VALIDATION;
524 END IF;
525
526 end IF;
527
528
529 IF k_lines_rec.ste_code NOT IN ('ACTIVE','EXPIRED','ENTERED','SIGNED') THEN
530
531 x_return_status := OKC_API.G_RET_STS_ERROR;
532
533 open cur_mean(k_lines_rec.ste_code);
534 fetch cur_mean into l_status_meaning;
535 close cur_mean;
536
537 OKC_API.set_message(p_app_name => g_app_name,
538 p_msg_name => 'OKC_INVALID_K_STATUS',
539 p_token1 => 'NUMBER',
540 p_token1_value => k_lines_rec.line_number||' - '||k_lines_rec.item_description,
541 p_token3 => 'STATUS',
542 p_token3_value => l_status_meaning);
543
544 RAISE G_EXCEPTION_HALT_VALIDATION;
545 ELSIF k_lines_rec.date_terminated is not null THEN
546
547 x_return_status := OKC_API.G_RET_STS_ERROR;
548
549 OKC_API.set_message(p_app_name => g_app_name,
550 p_msg_name => 'OKC_FUTURE_TERMINATED_K',
551 p_token1 => 'NUMBER',
552 p_token1_value => k_lines_rec.line_number||' - '|| k_lines_rec.item_description );
553
554 RAISE G_EXCEPTION_HALT_VALIDATION;
555 END IF;
556
557 If p_extend_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE Then
558 If (p_extend_in_parameters_rec.p_end_date is null) And
559 ((p_extend_in_parameters_rec.p_uom_code is null) Or
560 (p_extend_in_parameters_rec.p_duration is null)) Then
561
562 OKC_API.set_message(p_app_name => g_app_name,
563 p_msg_name => 'OKC_INVALID_PARAMETERS');
564
565 x_return_status := OKC_API.g_ret_sts_error;
566 RAISE G_EXCEPTION_HALT_VALIDATION;
567 End If;
568 End If;
569
570 IF (p_extend_in_parameters_rec.p_end_date is not null) THEN
571 IF p_extend_in_parameters_rec.p_end_date < k_lines_rec.end_date THEN
572
573 OKC_API.set_message(p_app_name => g_app_name,
574 p_msg_name => 'OKC_INVALID_EXTEND_DATE');
575
576 x_return_status := OKC_API.g_ret_sts_error;
577 end IF;
578 end IF;
579
580 EXCEPTION
581 WHEN G_EXCEPTION_HALT_VALIDATION THEN
582 null;
583 WHEN OTHERS THEN
584 OKC_API.set_message(p_app_name => g_app_name,
585 p_msg_name => g_unexpected_error,
586 p_token1 => g_sqlcode_token,
587 p_token1_value => sqlcode,
588 p_token2 => g_sqlerrm_token,
589 p_token2_value => sqlerrm);
590
591 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
592
593 end validate_cle;
594
595 PROCEDURE update_enddate( p_api_version IN NUMBER,
596 p_init_msg_list IN VARCHAR2 ,
597 x_return_status OUT NOCOPY VARCHAR2,
598 x_msg_count OUT NOCOPY NUMBER,
599 x_msg_data OUT NOCOPY VARCHAR2,
600 p_chr_rec IN okc_contract_pub.chrv_rec_type,
601 p_new_end_date IN DATE ) is
602
603 -- bug#5757116 added trunc in the where clause of the cursor.
604 CURSOR cur_k_lines is
605 SELECT cle.id
606 FROM okc_k_lines_b cle,
607 okc_statuses_b sts
608 WHERE trunc(cle.end_date) = trunc(p_chr_rec.end_date)
609 AND cle.dnz_chr_id = p_chr_rec.id
610 AND Cle.chr_id is not null
611 AND cle.sts_code = sts.code
612 AND sts.ste_code in ('ACTIVE','EXPIRED','ENTERED','SIGNED');
613
614 CURSOR cur_k_sublines(p_id number) is
615 SELECT cle.id,cle.object_version_number,cle.sts_code,cle.end_date
616 FROM okc_k_lines_b cle
617 CONNECT BY PRIOR id=cle_id
618 start with id=p_id;
619
620 CURSOR cur_status(p_sts_code varchar2) is
621 SELECT ste_code
622 FROM okc_statuses_b
623 WHERE code = p_sts_code;
624
625 l_chr_rec okc_contract_pub.chrv_rec_type := p_chr_rec;
626 i_chr_rec okc_contract_pub.chrv_rec_type;
627
628 l_cle_rec okc_contract_pub.clev_rec_type;
629 i_cle_rec okc_contract_pub.clev_rec_type;
630
631 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
632 l_api_name constant varchar2(30) := 'update_enddate';
633 l_status_code okc_statuses_v.ste_code%type;
634
635 BEGIN
636
637 x_return_status := OKC_API.G_RET_STS_SUCCESS;
638
639 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
640 p_init_msg_list,
641 '_PROCESS',
642 x_return_status);
643
644 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
645 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
646 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
647 RAISE OKC_API.G_EXCEPTION_ERROR;
648 END IF;
649
650
651 -- The following check is required because this PROCEDURE will be called when a line or a contract
652 -- is extended. when a line is extended it is required to update the header only IF line is extended beyond
653 -- the headers end date.
654
655
656 l_chr_rec.end_date := p_new_end_date;
657
658 --Bug 3926932 Checking if the new end date is null incase of perpetual extension
659 IF p_new_end_date is null or p_new_end_date >= trunc(sysdate) THEN
660
661 OPEN cur_status(l_chr_rec.sts_code);
662 FETCH cur_status into l_status_code;
663 CLOSE cur_status;
664
665 IF l_status_code = 'EXPIRED' then
666
667 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
668 p_status_type => 'ACTIVE',
669 x_status_code => l_status_code );
670
671 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
672 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
673 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
674 raise OKC_API.G_EXCEPTION_ERROR;
675 END IF;
676
677 l_chr_rec.sts_code := l_status_code;
678 --
679 -- Bug# 1468224, Status Change Action Assembler
680 -- The status will change from 'EXPIRED" to 'ACTIVE' when a contract gets extended
681 --
682 l_chr_rec.old_sts_code := 'EXPIRED';
683 l_chr_rec.old_ste_code := 'EXPIRED';
684 l_chr_rec.new_sts_code := l_status_code;
685 l_chr_rec.new_ste_code := 'ACTIVE';
686
687
688 END IF;
689 END IF;
690 OKC_CONTRACT_PUB.update_contract_header ( p_api_version => 1,
691 p_init_msg_list => OKC_API.G_FALSE,
692 x_return_status => l_return_status,
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data,
695 p_restricted_update => okc_api.g_true,
696 p_chrv_rec => l_chr_rec,
697 x_chrv_rec => i_chr_rec );
698
699 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
700 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
701 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
702 RAISE OKC_API.G_EXCEPTION_ERROR;
703 END IF;
704
705
706 FOR k_main_rec in cur_k_lines
707 LOOP
708 FOR k_lines_rec in cur_k_sublines(k_main_rec.id)
709 LOOP
710 l_status_code:=OKC_API.G_MISS_CHAR;
711 OPEN cur_status(k_lines_rec.sts_code);
712 FETCH cur_status into l_status_code;
713 CLOSE cur_status;
714
715 -- bug # 5757116 Added trunc to the end date to truncate the time stamps.
716 IF k_lines_rec.end_date is not null AND trunc(k_lines_rec.end_date)=trunc(p_chr_rec.end_date)
717 AND l_status_code in ('ACTIVE','EXPIRED','ENTERED','SIGNED') THEN
718
719 l_cle_rec.id := k_lines_rec.id;
720 l_cle_rec.object_version_number := k_lines_rec.object_version_number;
721 l_cle_rec.end_date := trunc(p_new_end_date);
722
723 --Bug 3926932 Checking if the new end date is null incase of perpetual extension
724 IF p_new_end_date is null or p_new_end_date >= trunc(sysdate) THEN
725
726 /* OPEN cur_status(k_lines_rec.sts_code);
727 FETCH cur_status into l_status_code;
728 CLOSE cur_status;
729 */
730
731 IF l_status_code = 'EXPIRED' then
732
733 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
734 p_status_type => 'ACTIVE',
735 x_status_code => l_status_code );
736
737 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
738 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
739 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
740 raise OKC_API.G_EXCEPTION_ERROR;
741 END IF;
742
743 l_cle_rec.sts_code := l_status_code;
744
745 END IF;
746 END IF;
747 --
748 -- Bug# 1405237. Avoide calling action assembler
749 --
750 l_cle_rec.call_action_asmblr := 'N';
751
752 OKC_CONTRACT_PUB.update_contract_line ( p_api_version => 1,
753 p_init_msg_list => OKC_API.G_FALSE,
754 x_return_status => l_return_status,
755 x_msg_count => x_msg_count,
756 x_msg_data => x_msg_data,
757 p_restricted_update => okc_api.g_true,
758 p_clev_rec => l_cle_rec,
759 x_clev_rec => i_cle_rec );
760
761 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
762 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
763 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
764 RAISE OKC_API.G_EXCEPTION_ERROR;
765 END IF;
766 END IF;
767
768 END LOOP;
769 END LOOP;
770
771 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
772
773 EXCEPTION
774 WHEN OKC_API.G_EXCEPTION_ERROR THEN
775 x_return_status := OKC_API.HANDLE_EXCEPTIONS
776 (l_api_name,
777 G_PKG_NAME,
778 'OKC_API.G_RET_STS_ERROR',
779 x_msg_count,
780 x_msg_data,
781 '_PROCESS');
782 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
783 x_return_status := OKC_API.HANDLE_EXCEPTIONS
784 (l_api_name,
785 G_PKG_NAME,
786 'OKC_API.G_RET_STS_UNEXP_ERROR',
787 x_msg_count,
788 x_msg_data,
789 '_PROCESS');
790 WHEN OTHERS THEN
791 x_return_status := OKC_API.HANDLE_EXCEPTIONS
792 (l_api_name,
793 G_PKG_NAME,
794 'OTHERS',
795 x_msg_count,
796 x_msg_data,
797 '_PROCESS');
798 END update_enddate;
799
800 PROCEDURE update_condition_headers ( p_api_version IN NUMBER,
801 p_init_msg_list IN VARCHAR2 ,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_chr_id IN NUMBER,
806 p_old_end_date IN VARCHAR2,
807 p_new_end_date IN VARCHAR2) is
808
809 CURSOR cur_condition_headers is
810 SELECT id,object_version_number,date_active,date_inactive
811 FROM okc_condition_headers_b
812 WHERE dnz_chr_id = p_chr_id
813 and date_inactive = p_old_end_date
814 FOR update of id nowait;
815
816 l_cnh_rec okc_conditions_pub.cnhv_rec_type;
817 i_cnh_rec okc_conditions_pub.cnhv_rec_type;
818
819 E_Resource_Busy EXCEPTION;
820 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
821
822 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
823 l_api_name constant varchar2(30) := 'update_conditions';
824
825 BEGIN
826 x_return_status := OKC_API.G_RET_STS_SUCCESS;
827
828 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
829 p_init_msg_list,
830 '_PROCESS',
831 x_return_status);
832
833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
836 RAISE OKC_API.G_EXCEPTION_ERROR;
837 END IF;
838
839
840 FOR conditions_rec in cur_condition_headers LOOP
841 IF (l_debug = 'Y') THEN
842 okc_debug.set_trace_off;
843 END IF;
844 l_cnh_rec.id := conditions_rec.id;
845
846 l_cnh_rec.object_version_number := conditions_rec.object_version_number;
847
848 l_cnh_rec.date_inactive := p_new_end_date;
849
850 okc_conditions_pub.update_cond_hdrs( p_api_version => 1,
851 p_init_msg_list => OKC_API.G_FALSE,
852 x_return_status => l_return_status,
853 x_msg_count => x_msg_count,
854 x_msg_data => x_msg_data,
855 p_cnhv_rec => l_cnh_rec,
856 x_cnhv_rec => i_cnh_rec);
857 IF (l_debug = 'Y') THEN
858 okc_debug.set_trace_off;
859 END IF;
860 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
861 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
862 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
863 RAISE OKC_API.G_EXCEPTION_ERROR;
864 END IF;
865
866 end LOOP;
867 IF (l_debug = 'Y') THEN
868 okc_debug.set_trace_on;
869 END IF;
870 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
871
872 EXCEPTION
873 WHEN E_Resource_Busy THEN
874 IF (l_debug = 'Y') THEN
875 okc_debug.set_trace_on;
876 END IF;
877 x_return_status := okc_api.g_ret_sts_error;
878
879 OKC_API.set_message(G_FND_APP,
880 G_FORM_UNABLE_TO_RESERVE_REC);
881 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
882
883 WHEN OKC_API.G_EXCEPTION_ERROR THEN
884 IF (l_debug = 'Y') THEN
885 okc_debug.set_trace_on;
886 END IF;
887 x_return_status := OKC_API.HANDLE_EXCEPTIONS
888 (l_api_name,
889 G_PKG_NAME,
890 'OKC_API.G_RET_STS_ERROR',
891 x_msg_count,
892 x_msg_data,
893 '_PROCESS');
894
895 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
896 IF (l_debug = 'Y') THEN
897 okc_debug.set_trace_on;
898 END IF;
899 x_return_status := OKC_API.HANDLE_EXCEPTIONS
900 (l_api_name,
901 G_PKG_NAME,
902 'OKC_API.G_RET_STS_UNEXP_ERROR',
903 x_msg_count,
904 x_msg_data,
905 '_PROCESS');
906 WHEN OTHERS THEN
907 IF (l_debug = 'Y') THEN
908 okc_debug.set_trace_on;
909 END IF;
910 x_return_status := OKC_API.HANDLE_EXCEPTIONS
911 (l_api_name,
912 G_PKG_NAME,
913 'OTHERS',
914 x_msg_count,
915 x_msg_data,
916 '_PROCESS');
917 END update_condition_headers;
918
919
920 PROCEDURE update_time_values ( p_api_version IN NUMBER,
921 p_init_msg_list IN VARCHAR2 ,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2,
925 p_tve_id IN NUMBER,
926 p_tve_id_limited IN NUMBER,
927 p_tve_type IN VARCHAR2,
928 p_new_end_date IN DATE ) is
929
930 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
931
932 l_tavv_ext_rec OKC_TIME_PUB.tavv_rec_type;
933 i_tavv_ext_rec OKC_TIME_PUB.tavv_rec_type;
934
935 l_isev_rec OKC_TIME_PUB.isev_ext_rec_type;
936 i_isev_rec OKC_TIME_PUB.isev_ext_rec_type;
937
938 CURSOR CUR_TIME_VALUES (p_tve_id in okc_timevalues.id%type)is
939 SELECT id,object_version_number
940 FROM okc_timevalues
941 WHERE id = p_tve_id;
942
943 CURSOR CUR_IS_ISE(p_tve_id okc_timevalues.id%type) is
944 SELECT 'x'
945 FROM okc_time_ia_startend_val_v
946 WHERE id = p_tve_id;
947
948 l_flag varchar2(1);
949 l_id okc_timevalues.id%type;
950 l_object_version_number okc_timevalues.object_version_number%type;
951 l_api_name constant varchar2(30) := 'update_time_values';
952
953 BEGIN
954 --dbms_output.put_line('update time (+)');
955 --dbms_output.put_line('p_new_end_date'||p_new_end_date);
956 OKC_API.init_msg_list(p_init_msg_list);
957
958 x_return_status := OKC_API.G_RET_STS_SUCCESS;
959
960 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
961 p_init_msg_list,
962 '_PROCESS',
963 x_return_status);
964
965 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
966 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
967 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
968 RAISE OKC_API.G_EXCEPTION_ERROR;
969 END IF;
970
971 IF p_tve_type = 'ISE' THEN
972 --dbms_output.put_line('ISE');
973 OPEN cur_time_values(p_tve_id);
974 FETCH cur_time_values into l_id,l_object_version_number;
975
976 IF cur_time_values%found THEN
977
978 OPEN cur_is_ise(p_tve_id);
979 FETCH cur_is_ise into l_flag;
980
981 IF cur_is_ise%found THEN -- This implies that this ISE will have TAV's, so update those
982
983 l_isev_rec.id := l_id;
984 l_isev_rec.object_version_number := l_object_version_number;
985 l_isev_rec.end_date := p_new_end_date;
986
987 OKC_TIME_PUB.lock_ia_startend(p_api_version => 1,
988 p_init_msg_list => OKC_API.G_FALSE,
989 x_return_status => l_return_status,
990 x_msg_count => x_msg_count,
991 x_msg_data => x_msg_data,
992 p_isev_ext_rec => l_isev_rec );
993
994
995 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
996 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
997 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
998 RAISE OKC_API.G_EXCEPTION_ERROR;
999 END IF;
1000
1001 OKC_TIME_PUB.update_ia_startend(p_api_version => 1,
1002 p_init_msg_list => OKC_API.G_FALSE,
1003 x_return_status => l_return_status,
1004 x_msg_count => x_msg_count,
1005 x_msg_data => x_msg_data,
1006 p_isev_ext_rec => l_isev_rec,
1007 x_isev_ext_rec => i_isev_rec );
1008
1009 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1011 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1012 RAISE OKC_API.G_EXCEPTION_ERROR;
1013 END IF;
1014
1015 end IF; -- cur_is_ise
1016
1017 CLOSE cur_is_ise;
1018
1019 end IF; -- cur_time_values
1020
1021 CLOSE cur_time_values;
1022
1023 ELSIF p_tve_type = 'TPA' THEN
1024 --dbms_output.put_line('TPA');
1025 OPEN cur_time_values(p_tve_id);
1026 FETCH cur_time_values into l_id,l_object_version_number;
1027
1028 IF cur_time_values%found THEN
1029
1030 l_tavv_ext_rec.id := l_id;
1031 l_tavv_ext_rec.object_version_number := l_object_version_number;
1032 l_tavv_ext_rec.datetime := p_new_end_date;
1033
1034
1035 OKC_TIME_PUB.lock_tpa_value( p_api_version => 1,
1036 p_init_msg_list => okc_api.g_false,
1037 x_return_status => l_return_status,
1038 x_msg_count => x_msg_count,
1039 x_msg_data => x_msg_data,
1040 p_tavv_rec => l_tavv_ext_rec );
1041
1042
1043 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1044 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1045 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1046 RAISE OKC_API.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049
1050 OKC_TIME_PUB.update_tpa_value(p_api_version => 1,
1051 p_init_msg_list => okc_api.g_false,
1052 x_return_status => l_return_status,
1053 x_msg_count => x_msg_count,
1054 x_msg_data => x_msg_data,
1055 p_tavv_rec => l_tavv_ext_rec,
1056 x_tavv_rec => i_tavv_ext_rec );
1057
1058
1059 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1060 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1061 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1062 RAISE OKC_API.G_EXCEPTION_ERROR;
1063 END IF;
1064 END IF;
1065
1066 CLOSE cur_time_values;
1067
1068 ELSIF ( p_tve_type in ('TGD','CYL') and p_tve_id_limited is not null) THEN
1069 --dbms_output.put_line('TGD,CYL tve_id_limited not null');
1070 OPEN cur_time_values(p_tve_id_limited);
1071
1072 FETCH cur_time_values into l_id,l_object_version_number;
1073
1074 IF cur_time_values%found THEN
1075
1076 l_isev_rec.id := l_id;
1077 l_isev_rec.object_version_number := l_object_version_number;
1078 l_isev_rec.end_date := p_new_end_date;
1079
1080 OKC_TIME_PUB.lock_ia_startend(p_api_version => 1,
1081 p_init_msg_list => OKC_API.G_FALSE,
1082 x_return_status => l_return_status,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data,
1085 p_isev_ext_rec => l_isev_rec );
1086
1087
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
1095 OKC_TIME_PUB.update_ia_startend(p_api_version => 1,
1096 p_init_msg_list => OKC_API.G_FALSE,
1097 x_return_status => l_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 p_isev_ext_rec => l_isev_rec,
1101 x_isev_ext_rec => i_isev_rec );
1102
1103 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1104 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1105 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1106 RAISE OKC_API.G_EXCEPTION_ERROR;
1107 END IF;
1108
1109 END IF;
1110
1111 CLOSE cur_time_values;
1112
1113 END IF;
1114
1115 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1116 --dbms_output.put_line('update time (-) ');
1117 EXCEPTION
1118 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1119 IF cur_time_values%isOPEN THEN
1120 CLOSE cur_time_values;
1121 end IF;
1122 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1123 (l_api_name,
1124 G_PKG_NAME,
1125 'OKC_API.G_RET_STS_ERROR',
1126 x_msg_count,
1127 x_msg_data,
1128 '_PROCESS');
1129 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1130
1131 IF cur_time_values%isOPEN THEN
1132 CLOSE cur_time_values;
1133 end IF;
1134 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1135 (l_api_name,
1136 G_PKG_NAME,
1137 'OKC_API.G_RET_STS_UNEXP_ERROR',
1138 x_msg_count,
1139 x_msg_data,
1140 '_PROCESS');
1141 WHEN OTHERS THEN
1142 IF cur_time_values%isOPEN THEN
1143 CLOSE cur_time_values;
1144 end IF;
1145 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1146 (l_api_name,
1147 G_PKG_NAME,
1148 'OTHERS',
1149 x_msg_count,
1150 x_msg_data,
1151 '_PROCESS');
1152 end update_time_values;
1153
1154 PROCEDURE extend_chr( p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2 ,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_msg_count OUT NOCOPY NUMBER,
1158 x_msg_data OUT NOCOPY VARCHAR2,
1159 p_extend_in_parameters_rec IN extend_in_parameters_rec
1160 ) is
1161
1162 --bug # 5757116 added trunc to the start and end date in the where clause of the cursor.
1163 CURSOR cur_k_header is
1164 SELECT ID,
1165 OBJECT_VERSION_NUMBER,
1166 STS_CODE,
1167 CONTRACT_NUMBER,
1168 CONTRACT_NUMBER_MODIFIER,
1169 END_DATE
1170 FROM okc_k_headers_v
1171 WHERE id = p_extend_in_parameters_rec.p_contract_id
1172 and trunc(start_date) = trunc(p_extend_in_parameters_rec.p_orig_start_date)
1173 and trunc(end_date) = trunc(p_extend_in_parameters_rec.p_orig_end_date)
1174 FOR UPDATE OF start_date NOWAIT;
1175
1176 rec_k_header OKC_CONTRACT_PUB.chrv_rec_type;
1177
1178 CURSOR lock_lines is
1179 SELECT *
1180 FROM okc_k_lines_v
1181 WHERE dnz_chr_id = p_extend_in_parameters_rec.p_contract_id
1182 FOR update of line_number nowait;
1183
1184 CURSOR lock_rules is
1185 SELECT *
1186 FROM okc_rules_v
1187 WHERE dnz_chr_id = p_extend_in_parameters_rec.p_contract_id
1188 FOR update of object1_id1 nowait;
1189
1190 CURSOR cur_header_aa IS
1191 SELECT k.estimated_amount,k.scs_code,scs.cls_code,k.sts_code
1192 FROM OKC_K_HEADERS_B K,
1193 OKC_SUBCLASSES_B SCS
1194 WHERE k.id = p_extend_in_parameters_rec.p_contract_id
1195 AND k.scs_code = scs.code;
1196
1197 l_scs_code okc_subclasses_v.code%type;
1198 l_cls_code okc_subclasses_v.cls_code%type;
1199 l_k_status_code okc_k_headers_v.sts_code%type;
1200 l_estimated_amount number;
1201
1202 -- Bring only those line that have the same end date as of the header.
1203
1204 -- bug # 5757116 Added trunc to the end date in the where clause of the cursor.
1205 CURSOR cur_k_lines(p_chr_id number,p_end_date date) is
1206 SELECT cle.id,cle.end_date,cle.start_date,cle.object_version_number
1207 FROM okc_k_lines_b cle,
1208 okc_statuses_b sts
1209 WHERE cle.dnz_chr_id = p_chr_id
1210 and trunc(cle.end_date) = trunc(p_end_date)
1211 and cle.sts_code = sts.code
1212 and sts.ste_code in ('ACTIVE','EXPIRED','ENTERED','SIGNED')
1213 and date_terminated is null;
1214
1215 l_extend_in_parameters_rec extend_in_parameters_rec := p_extend_in_parameters_rec;
1216 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1217
1218 l_api_name constant varchar2(30) := 'extend_chr';
1219 l_new_end_date date;
1220
1221 CURSOR cur_status(p_sts_code varchar2) is
1222 SELECT ste_code
1223 FROM okc_statuses_b
1224 WHERE code = p_sts_code;
1225
1226 l_ste_code okc_statuses_v.ste_code%type;
1227
1228 -- Added for providing extend callout - from header - Bug 2346862/2648677
1229 -- Cursor created to get the PDF_ID for Class 'SERVICE'
1230 --CURSOR c_pdf IS
1231 --SELECT pdf_id
1232 --FROM okc_class_operations
1233 --WHERE opn_code = 'EXTEND'
1234 --AND cls_code = 'SERVICE';
1235
1236 CURSOR c_pdf(p_cls_code okc_class_operations_v.cls_code%type) IS
1237 SELECT pdf_id
1238 FROM okc_class_operations
1239 WHERE opn_code = 'EXTEND'
1240 AND cls_code = p_cls_code;
1241
1242 l_pdf_id NUMBER;
1243 l_cle_id NUMBER;
1244 l_chr_id NUMBER;
1245 l_cnt NUMBER;
1246 l_string VARCHAR2(32000);
1247 proc_string VARCHAR2(32000);
1248 -- Cursor created to get the PDF_ID for Class 'SERVICE'
1249
1250 BEGIN
1251
1252 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1253
1254 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1255 p_init_msg_list,
1256 '_PROCESS',
1257 x_return_status);
1258
1259 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1260 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1261 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1262 RAISE OKC_API.G_EXCEPTION_ERROR;
1263 END IF;
1264
1265
1266
1267 IF g_called_FROM = 'HEADER' THEN -- Included this condition because line will also call header
1268
1269 OPEN cur_k_header;
1270 FETCH cur_k_header into
1271 rec_k_header.ID,
1272 rec_k_header.OBJECT_VERSION_NUMBER,
1273 rec_k_header.STS_CODE,
1274 rec_k_header.CONTRACT_NUMBER,
1275 rec_k_header.CONTRACT_NUMBER_MODIFIER,
1276 rec_k_header.END_DATE;
1277 -- This condition will imply that the dates on the contract have been changed after the user submitted
1278 -- the contract for extension. In this case skip the contract for extension and RAISE expected error.
1279
1280 IF (l_debug = 'Y') THEN
1281 okc_debug.set_trace_off;
1282 END IF;
1283 IF cur_k_header%notfound THEN
1284
1285 OKC_API.set_message(p_app_name => g_app_name,
1286 p_msg_name => 'OKC_K_CHANGED',
1287 p_token1 => 'CONTRACT_NUMBER',
1288 p_token1_value => p_extend_in_parameters_rec.p_contract_number,
1289 p_token2 => 'MODIFIER',
1290 p_token2_value => p_extend_in_parameters_rec.p_contract_modifier);
1291
1292 CLOSE cur_k_header;
1293 IF (l_debug = 'Y') THEN
1294 okc_debug.set_trace_on;
1295 END IF;
1296 RAISE okc_api.g_exception_error;
1297
1298 END IF;
1299
1300 CLOSE cur_k_header;
1301 IF (l_debug = 'Y') THEN
1302 okc_debug.set_trace_on;
1303 END IF;
1304 -- Now that the dates haven't changed validate the input arguements
1305
1306 --dbms_output.put_line(' VALIDATE_CHR + ');
1307
1308 OKC_EXTEND_PVT.validate_chr(p_api_version => 1,
1309 p_init_msg_list => OKC_API.G_FALSE,
1310 x_return_status => l_return_status,
1311 x_msg_count => x_msg_count,
1312 x_msg_data => x_msg_data,
1313 p_extend_in_parameters_rec => l_extend_in_parameters_rec);
1314 --dbms_output.put_line(' VALIDATE_CHR - ');
1315
1316 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1317 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1318 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1319 RAISE OKC_API.G_EXCEPTION_ERROR;
1320 END IF;
1321
1322 -- The following commented out since this check is already done in validate_chr
1323 /* IF p_extend_in_parameters_rec.p_end_date is not null THEN
1324 IF p_extend_in_parameters_rec.p_end_date < rec_k_header.end_date THEN
1325
1326 OKC_API.set_message(p_app_name => g_app_name,
1327 p_msg_name => 'OKC_INVALID_EXTEND_DATE');
1328
1329 RAISE OKC_API.G_EXCEPTION_ERROR;
1330 END IF;
1331 END IF; */
1332
1333 -- Set the new end date. At this point since validation is done
1334 -- assume either end_date or UOM and Duration exists
1335
1336 If p_extend_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE Then
1337 IF p_extend_in_parameters_rec.p_end_date is not null THEN
1338 l_new_end_date := p_extend_in_parameters_rec.p_end_date;
1339 else
1340 l_new_end_date := okc_time_util_pub.get_enddate(rec_k_header.end_date + 1,
1341 p_extend_in_parameters_rec.p_uom_code,
1342 p_extend_in_parameters_rec.p_duration);
1343 end if;
1344 Else
1345 l_new_end_date := Null;
1346 End If;
1347
1348 --dbms_output.put_line('l_new_end_date '||l_new_end_date);
1349
1350 --dbms_output.put_line('l_new_end_date '||l_new_end_date);
1351
1352 --dbms_output.put_line('update_dnz_startend + ');
1353
1354 update_enddate( p_api_version => 1,
1355 p_init_msg_list => OKC_API.G_FALSE,
1356 x_return_status => l_return_status,
1357 x_msg_count => x_msg_count,
1358 x_msg_data => x_msg_data,
1359 p_chr_rec => rec_k_header,
1360 p_new_end_date => l_new_end_date );
1361
1362 --dbms_output.put_line('update_dnz_startend - ');
1363
1364 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1366 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1367 RAISE OKC_API.G_EXCEPTION_ERROR;
1368 END IF;
1369
1370 end IF; ---Header
1371
1372 --dbms_output.put_line(' update_condition_headers + ');
1373
1374 update_condition_headers( p_api_version => 1,
1375 p_init_msg_list => OKC_API.G_FALSE,
1376 x_return_status => l_return_status,
1377 x_msg_count => x_msg_count,
1378 x_msg_data => x_msg_data,
1379 p_chr_id => p_extend_in_parameters_rec.p_contract_id,
1380 p_old_end_date => p_extend_in_parameters_rec.p_orig_end_date,
1381 p_new_end_date => l_new_end_date );
1382
1383 --dbms_output.put_line(' update_condition_headers - ');
1384
1385 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1386 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1387 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1388 RAISE OKC_API.G_EXCEPTION_ERROR;
1389 END IF;
1390 /* Commenting out as Time Values is obsoleted*/
1391 --dbms_output.put_line('RES_TIME_EXTND_K + ');
1392 /*
1393 OKC_TIME_RES_PUB.res_time_extnd_k( p_api_version => 1,
1394 p_init_msg_list => OKC_API.G_FALSE,
1395 x_return_status => l_return_status,
1396 p_chr_id => rec_k_header.id,
1397 p_cle_id => null,
1398 p_start_date => p_extend_in_parameters_rec.p_orig_end_date,
1399 p_end_date => l_new_end_date);
1400 --dbms_output.put_line('RES_TIME_EXTND_K - ');
1401 */
1402 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1403 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1404 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1405 RAISE OKC_API.G_EXCEPTION_ERROR;
1406 END IF;
1407
1408 -- ACTION ASSEMBLER
1409 open cur_header_aa;
1410 fetch cur_header_aa into l_estimated_amount,l_scs_code,l_cls_code,l_k_status_code;
1411 close cur_header_aa;
1412
1413 OKC_K_EXTD_ASMBLR_PVT.acn_assemble( p_api_version => 1,
1414 p_init_msg_list => OKC_API.G_FALSE,
1415 x_return_status => l_return_status,
1416 x_msg_count => x_msg_count,
1417 x_msg_data => x_msg_data,
1418 p_k_id => p_extend_in_parameters_rec.p_contract_id,
1419 p_k_number => p_extend_in_parameters_rec.p_contract_number,
1420 p_k_nbr_mod => p_extend_in_parameters_rec.p_contract_modifier,
1421 p_k_end_date => p_extend_in_parameters_rec.p_orig_end_date,
1422 p_k_class => l_cls_code,
1423 p_k_subclass => l_scs_code,
1424 p_k_status_code => l_k_status_code,
1425 p_estimated_amount => l_estimated_amount,
1426 p_new_k_end_date => l_new_end_date );
1427
1428 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1429 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1430 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1431 RAISE OKC_API.G_EXCEPTION_ERROR;
1432 END IF;
1433
1434 -- Call out for External procedure provided by different classes
1435
1436 -- Begin - Changes done for Bug 2648677/2346862
1437 OPEN c_pdf(l_cls_code);
1438 FETCH c_pdf INTO l_pdf_id;
1439 okc_create_plsql (p_pdf_id => l_pdf_id,
1440 x_string => l_string) ;
1441 CLOSE c_pdf;
1442
1443 IF l_string is NOT NULL THEN
1444 l_chr_id := p_extend_in_parameters_rec.p_contract_id;
1445 l_cle_id := NULL;
1446 proc_string := 'begin '||l_string || ' (:b1,:b2,:b3); end ;';
1447 EXECUTE IMMEDIATE proc_string using l_chr_id,l_cle_id, out l_return_status;
1448 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1449 x_return_status := l_return_status;
1450 RAISE G_EXCEPTION_HALT_VALIDATION;
1451 ELSE
1452 x_return_status := l_return_status;
1453 END IF;
1454 END IF;
1455
1456 -- End - Changes done for Bug 2648677/2346862
1457
1458
1459
1460 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1461
1462 EXCEPTION
1463
1464 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1465 IF (l_debug = 'Y') THEN
1466 okc_debug.set_trace_on;
1467 END IF;
1468 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1469 (l_api_name,
1470 G_PKG_NAME,
1471 'OKC_API.G_RET_STS_ERROR',
1472 x_msg_count,
1473 x_msg_data,
1474 '_PROCESS');
1475
1476 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1477 IF (l_debug = 'Y') THEN
1478 okc_debug.set_trace_on;
1479 END IF;
1480 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1481 (l_api_name,
1482 G_PKG_NAME,
1483 'OKC_API.G_RET_STS_UNEXP_ERROR',
1484 x_msg_count,
1485 x_msg_data,
1486 '_PROCESS');
1487 WHEN OTHERS THEN
1488 IF (l_debug = 'Y') THEN
1489 okc_debug.set_trace_on;
1490 END IF;
1491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1492 (l_api_name,
1493 G_PKG_NAME,
1494 'OTHERS',
1495 x_msg_count,
1496 x_msg_data,
1497 '_PROCESS');
1498
1499 end extend_chr;
1500
1501 PROCEDURE extend_cle( p_api_version IN NUMBER,
1502 p_init_msg_list IN VARCHAR2 ,
1503 x_return_status OUT NOCOPY VARCHAR2,
1504 x_msg_count OUT NOCOPY NUMBER,
1505 x_msg_data OUT NOCOPY VARCHAR2,
1506 p_extend_in_parameters_rec IN extend_in_cle_rec ) is
1507
1508 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1509 l_api_name constant varchar2(30) := 'extend_cle';
1510
1511 l_new_end_date date;
1512
1513
1514 CURSOR cur_lines is
1515 SELECT id,object_version_number,chr_id,date_terminated,end_date,sts_code, start_date
1516 FROM okc_k_lines_b
1517 WHERE date_terminated is null
1518 START WITH id = p_extend_in_parameters_rec.p_cle_id
1519 CONNECT BY PRIOR id = cle_id
1520 ORDER BY LEVEL asc
1521 FOR UPDATE OF id NOWAIT;
1522
1523 CURSOR cur_status (p_sts_code okc_statuses_v.code%type) is
1524 SELECT ste_code
1525 FROM okc_statuses_b
1526 WHERE code = p_sts_code;
1527
1528 CURSOR cur_header is
1529 SELECT k.contract_number,k.contract_number_modifier,k.scs_code,scs.cls_code,
1530 cle.price_negotiated,cle.sts_code
1531 FROM okc_k_headers_b k,
1532 okc_subclasses_b scs,
1533 okc_k_lines_b cle
1534 WHERE k.id = p_extend_in_parameters_rec.p_dnz_chr_id
1535 AND cle.chr_id = k.id
1536 AND cle.id = p_extend_in_parameters_rec.p_cle_id
1537 AND k.scs_code = scs.code;
1538
1539 l_contract_number okc_k_headers_v.contract_number%type;
1540 l_contract_modifier okc_k_headers_v.contract_number_modifier%type;
1541 l_kl_sts_code okc_k_lines_v.sts_code%type;
1542 l_scs_code okc_subclasses_v.code%type;
1543 l_cls_code okc_subclasses_v.cls_code%type;
1544 l_price_negotiated number;
1545 i_cle_rec okc_contract_pub.clev_rec_type;
1546 l_cle_rec okc_contract_pub.clev_rec_type;
1547 l_status varchar2(30);
1548 l_status_code okc_statuses_v.code%type;
1549
1550
1551 -- Added for providing extend callout - from Line
1552 -- Cursor created to get the PDF_ID for Class 'SERVICE' - Bug 2648677/2346862
1553 --CURSOR c_pdf IS
1554 --SELECT pdf_id
1555 --FROM okc_class_operations
1556 --WHERE opn_code = 'EXTEND'
1557 --AND cls_code = 'SERVICE';
1558
1559 CURSOR c_pdf(p_cls_code okc_class_operations_v.cls_code%type) IS
1560 SELECT pdf_id
1561 FROM okc_class_operations
1562 WHERE opn_code = 'EXTEND'
1563 AND cls_code = p_cls_code;
1564
1565 l_pdf_id NUMBER;
1566 l_cle_id NUMBER;
1567 l_chr_id NUMBER;
1568 l_cnt NUMBER;
1569 l_string VARCHAR2(32000);
1570 proc_string VARCHAR2(32000);
1571
1572
1573 Cursor LineCov_cur(p_cle_id IN Number) Is
1574 Select id
1575 From OKC_K_LINES_V
1576 Where cle_id = p_cle_id
1577 and lse_id in (2,13,15,20);
1578 BEGIN
1579
1580 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1581
1582 l_return_status := OKC_API.START_ACTIVITY( l_api_name,
1583 p_init_msg_list,
1584 '_PROCESS',
1585 x_return_status );
1586
1587 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1588 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1589 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1590 RAISE OKC_API.G_EXCEPTION_ERROR;
1591 END IF;
1592
1593 --dbms_output.put_line('validate cle (+) ');
1594
1595 OKC_EXTEND_PUB.validate_cle( p_api_version => 1,
1596 p_init_msg_list => OKC_API.G_FALSE,
1597 x_return_status => l_return_status,
1598 x_msg_count => x_msg_count,
1599 x_msg_data => x_msg_data,
1600 p_extend_in_parameters_rec => p_extend_in_parameters_rec );
1601 --dbms_output.put_line('validate cle (-) ');
1602
1603 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1604 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1605 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1606 RAISE OKC_API.G_EXCEPTION_ERROR;
1607 END IF;
1608
1609 If p_extend_in_parameters_rec.p_perpetual_flag = OKC_API.G_FALSE Then
1610 IF p_extend_in_parameters_rec.p_end_date is not null and
1611 p_extend_in_parameters_rec.p_end_date <> okc_api.g_miss_date THEN
1612 l_new_end_date := p_extend_in_parameters_rec.p_end_date;
1613 else
1614 l_new_end_date := okc_time_util_pub.get_enddate(p_extend_in_parameters_rec.p_orig_end_date + 1,
1615 p_extend_in_parameters_rec.p_uom_code,
1616 p_extend_in_parameters_rec.p_duration);
1617 end IF;
1618 Else
1619 l_new_end_date := Null;
1620 End If;
1621
1622
1623 FOR lines_rec in cur_lines LOOP
1624 --dbms_output.put_line('header line handler (+) ');
1625 IF (l_debug = 'Y') THEN
1626 okc_debug.set_trace_off;
1627 END IF;
1628
1629 OPEN cur_status(lines_rec.sts_code);
1630 FETCH cur_status into l_status;
1631 CLOSE cur_status;
1632 IF l_status IN ('ACTIVE','EXPIRED','ENTERED','SIGNED') THEN
1633 l_cle_rec.id := lines_rec.id;
1634 l_cle_rec.object_version_number := lines_rec.object_version_number;
1635 l_cle_rec.end_date := l_new_end_date;
1636 l_cle_rec.start_date := lines_rec.start_date;
1637
1638
1639 IF (l_new_end_date Is Null) Or
1640 (l_new_end_date >= trunc(sysdate)) THEN
1641
1642 -- Commenting the following since it is exactly same as above fetch.
1643 -- Instead just set the value of l_status_code.
1644 /* OPEN cur_status(lines_rec.sts_code);
1645 FETCH cur_status into l_status_code;
1646 CLOSE cur_status; */
1647 l_status_code := l_status;
1648
1649 IF l_status_code = 'EXPIRED' then
1650
1651 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
1652 p_status_type => 'ACTIVE',
1653 x_status_code => l_status_code );
1654 IF (l_debug = 'Y') THEN
1655 okc_debug.set_trace_off;
1656 END IF;
1657 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1658 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1659 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1660 raise OKC_API.G_EXCEPTION_ERROR;
1661 END IF;
1662
1663 l_cle_rec.sts_code := l_status_code;
1664
1665 END IF;
1666 END IF;
1667 --
1668 -- Bug# 1405237: Avoide calling Action Assembler here
1669 --
1670 l_cle_rec.call_action_asmblr := 'N';
1671
1672
1673 OKC_CONTRACT_PUB.update_contract_line ( p_api_version => 1,
1674 p_init_msg_list => OKC_API.G_FALSE,
1675 x_return_status => l_return_status,
1676 x_msg_count => x_msg_count,
1677 x_msg_data => x_msg_data,
1678 p_restricted_update => okc_api.g_true,
1679 p_clev_rec => l_cle_rec,
1680 x_clev_rec => i_cle_rec );
1681 IF (l_debug = 'Y') THEN
1682 okc_debug.set_trace_off;
1683 END IF;
1684 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1685 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1686 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1687 RAISE OKC_API.G_EXCEPTION_ERROR;
1688 END IF;
1689
1690 END IF; -- l_status
1691
1692 END LOOP;
1693 IF (l_debug = 'Y') THEN
1694 okc_debug.set_trace_on;
1695 END IF;
1696 OPEN cur_header;
1697 FETCH cur_header into l_contract_number,l_contract_modifier,l_scs_code,l_cls_code,
1698 l_price_negotiated,l_kl_sts_code;
1699 CLOSE cur_header;
1700
1701 OKC_KL_EXTD_ASMBLR_PVT.acn_assemble(p_api_version => 1,
1702 p_init_msg_list => OKC_API.G_FALSE,
1703 x_return_status => l_return_status,
1704 x_msg_count => x_msg_count,
1705 x_msg_data => x_msg_data,
1706 p_k_id => p_extend_in_parameters_rec.p_dnz_chr_id,
1707 p_kl_id => p_extend_in_parameters_rec.p_cle_id,
1708 p_kl_end_date => p_extend_in_parameters_rec.p_orig_end_date,
1709 p_new_kl_end_date => l_new_end_date,
1710 p_k_class => l_cls_code,
1711 p_k_subclass => l_scs_code,
1712 p_kl_status_code => l_kl_sts_code,
1713 p_estimated_amount => l_price_negotiated,
1714 p_k_number => l_contract_number,
1715 p_k_nbr_mod => l_contract_modifier );
1716
1717 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1718 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1719 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1720 RAISE OKC_API.G_EXCEPTION_ERROR;
1721 END IF;
1722
1723 -- Call out for External procedure provided by different classes
1724
1725 -- Begin - Changes done for Bug 2648677/2346862
1726 OPEN c_pdf(l_cls_code);
1727 FETCH c_pdf INTO l_pdf_id;
1728 okc_create_plsql (p_pdf_id => l_pdf_id,
1729 x_string => l_string) ;
1730 CLOSE c_pdf;
1731
1732 IF l_string is NOT NULL THEN
1733 l_chr_id := p_extend_in_parameters_rec.p_dnz_chr_id;
1734 l_cle_id := p_extend_in_parameters_rec.p_cle_id;
1735 proc_string := 'begin '||l_string || ' (:b1,:b2,:b3); end ;';
1736 EXECUTE IMMEDIATE proc_string using l_chr_id,l_cle_id, out l_return_status;
1737 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1738 x_return_status := l_return_status;
1739 RAISE G_EXCEPTION_HALT_VALIDATION;
1740 ELSE
1741 x_return_status := l_return_status;
1742 END IF;
1743 END IF;
1744
1745 -- End - Changes done for Bug 2648677/2346862
1746
1747 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1748
1749 EXCEPTION
1750 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1751 IF (l_debug = 'Y') THEN
1752 okc_debug.set_trace_on;
1753 END IF;
1754 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1755 (l_api_name,
1756 G_PKG_NAME,
1757 'OKC_API.G_RET_STS_ERROR',
1758 x_msg_count,
1759 x_msg_data,
1760 '_PROCESS');
1761 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1762 IF (l_debug = 'Y') THEN
1763 okc_debug.set_trace_on;
1764 END IF;
1765 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1766 (l_api_name,
1767 G_PKG_NAME,
1768 'OKC_API.G_RET_STS_UNEXP_ERROR',
1769 x_msg_count,
1770 x_msg_data,
1771 '_PROCESS');
1772 WHEN OTHERS THEN
1773 IF (l_debug = 'Y') THEN
1774 okc_debug.set_trace_on;
1775 END IF;
1776 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1777 (l_api_name,
1778 G_PKG_NAME,
1779 'OTHERS',
1780 x_msg_count,
1781 x_msg_data,
1782 '_PROCESS');
1783 END extend_cle;
1784 END OKC_EXTEND_PVT;