[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMINATE_PVT
Source
1 PACKAGE BODY OKC_TERMINATE_PVT as
2 /* $Header: OKCRTERB.pls 120.2 2006/06/21 12:05:25 nechatur noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7 FUNCTION is_k_term_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
8
9 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
10 will take place everytime a change happens to a contract or not. That is
11 If the status of the contract showing in launchpad would at all times be in sync
12 with database.It might not happen due to performance reasons of launchpad. So the current approach.
13 But if this sync is assured then we could use p_sts_code as well*/
14
15 l_sts_code VARCHAR2(100);
16 l_cls_code VARCHAR2(100);
17 l_template_yn VARCHAR2(10);
18 l_code VARCHAR2(100);
19 l_app_id okc_k_headers_b.application_id%TYPE;
20 l_scs_code okc_k_headers_b.scs_code%TYPE;
21 l_k VARCHAR2(255);
22 l_mod okc_k_headers_b.contract_number_modifier%TYPE ;
23 l_date_term DATE;
24 l_allow Varchar2(1) := 'N';
25
26
27 CURSOR c_chr IS
28 SELECT sts_code, template_yn, application_id, scs_code ,contract_number,
29 contract_number_modifier, date_terminated
30 FROM okc_k_headers_b
31 WHERE id = p_chr_id;
32
33 CURSOR c_sts(p_code IN VARCHAR2) IS
34 SELECT ste_code
35 FROM okc_statuses_b
36 WHERE code = p_code;
37
38 CURSOR c_invoice(p_code IN VARCHAR2,p_sts_code IN VARCHAR2) IS
39 SELECT allowed_yn
40 FROM OKC_ASSENTS_V
41 WHERE opn_code = 'INVOICE'
42 AND scs_code = p_code
43 AND sts_code = p_sts_code;
44
45 BEGIN
46
47 OPEN c_chr;
48 FETCH c_chr INTO l_code, l_template_yn, l_app_id, l_scs_code,l_k,l_mod, l_date_term;
49 CLOSE c_chr;
50
51 -- Only for service class
52 If l_scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION') THEN
53 OPEN c_sts(l_code);
54 FETCH c_sts INTO l_sts_code;
55 CLOSE c_sts;
56 If l_sts_code = 'HOLD' AND l_code <> 'QA_HOLD' Then
57 RETURN(TRUE);
58 End If;
59 End If;
60
61 IF (l_mod is not null) and (l_mod <> OKC_API.G_MISS_CHAR) then
62 l_k := l_k ||'-'||l_mod;
63 END IF;
64
65 IF l_template_yn = 'Y' then
66 OKC_API.set_message(p_app_name => g_app_name,
67 p_msg_name => 'OKC_K_TEMPLATE',
68 p_token1 => 'NUMBER',
69 p_token1_value => l_k);
70 RETURN(FALSE);
71 END IF;
72
73 IF l_code = 'QA_HOLD' then
74 OKC_API.set_message(p_app_name => g_app_name,
75 p_msg_name => 'OKC_INVALID_STS',
76 p_token1 => 'component',
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','HOLD','SIGNED','EXPIRED') 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 -- Added by MSENGUPT on 12/09/2001 for Bug#2143096
93 IF l_date_term IS NOT NULL Then
94 OKC_API.set_message(p_app_name => g_app_name,
95 p_msg_name => 'OKC_ALREADY_TERM');
96 RETURN(FALSE);
97 end if;
98
99 -- end of bug#2143096 -------------
100
101 If okc_util.Get_All_K_Access_Level(p_application_id => l_app_id,
102 p_chr_id => p_chr_id,
103 p_scs_code => l_scs_code) <> 'U' Then
104 OKC_API.set_message(p_app_name => g_app_name,
105 p_msg_name => 'OKC_NO_UPDATE',
106 p_token1 => 'CHR',
107 p_token1_value => l_k);
108 Return(FALSE);
109 End If;
110
111 -- Bug#3143093 ---
112 IF l_scs_code = 'WARRANTY' or l_scs_code = 'SERVICE' then
113 OPEN c_invoice(l_scs_code, l_sts_code);
114 FETCH c_invoice into l_allow;
115 CLOSE c_invoice;
116
117 IF l_allow <> 'Y' then
118 OKC_API.set_message(p_app_name => g_app_name,
119 p_msg_name => 'OKC_INVOICE_TERM',
120 p_token1 => 'component',
121 p_token1_value => l_k);
122 RETURN(FALSE);--------------
123 END IF;
124 END IF;
125 -- End of Bug# 3143093 ---
126
127 RETURN(TRUE);
128 END is_k_term_allowed;
129
130
131 FUNCTION is_kl_term_allowed(p_cle_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN IS
132
133 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
134 will take place everytime a change happens to a contract or not. That is
135 If the status of the contract showing in launchpad would at all times be in sync
136 with database.It might not happen due to performance reasons of launchpad. So the current approach.
137 But if this sync is assured then we could use p_sts_code as well*/
138
139 l_sts_code VARCHAR2(100);
140 l_cls_code VARCHAR2(100);
141 l_template_yn VARCHAR2(10);
142 l_code VARCHAR2(100);
143 l_hdr_code VARCHAR2(100);
144 l_chr_id number:=OKC_API.G_MISS_NUM;
145 l_app_id okc_k_headers_b.application_id%TYPE;
146 l_scs_code okc_k_headers_b.scs_code%TYPE;
147 l_k VARCHAR2(255);
148 l_mod okc_k_headers_b.contract_number_modifier%TYPE ;
149 l_no okc_k_lines_b.line_number%TYPE;
150 l_date_term_chr DATE;
151 l_date_term_cle DATE;
152 l_allow Varchar2(1) := 'N';
153
154 CURSOR c_chr(p_chr_id number) IS
155 SELECT template_yn ,sts_code, application_id, scs_code ,contract_number, contract_number_modifier, date_terminated
156 FROM okc_k_headers_b
157 WHERE id = p_chr_id;
158
159 CURSOR c_cle IS
160 SELECT sts_code,dnz_chr_id,line_number , date_terminated
161 FROM okc_k_lines_b
162 WHERE id = p_cle_id;
163
164 CURSOR c_sts(p_code IN VARCHAR2) IS
165 SELECT ste_code
166 FROM okc_statuses_b
167 WHERE code = p_code;
168
169 CURSOR c_invoice(p_code IN VARCHAR2,p_sts_code IN VARCHAR2) IS
170 SELECT allowed_yn
171 FROM OKC_ASSENTS_V
172 WHERE opn_code = 'INVOICE'
173 AND scs_code = p_code
174 AND sts_code = p_sts_code;
175
176 BEGIN
177
178 OPEN c_cle;
179 FETCH c_cle INTO l_code,l_chr_id,l_no, l_date_term_cle;
180 CLOSE c_cle;
181
182 If l_chr_id=OKC_API.G_MISS_NUM then
183 OKC_API.set_message(p_app_name => g_app_name,
184 p_msg_name => 'OKC_NO_CHR',
185 p_token1 => 'component',
186 p_token1_value => l_no);
187 RETURN (FALSE);
188 END IF;
189
190 OPEN c_chr(l_chr_id);
191 FETCH c_chr INTO l_template_yn,l_hdr_code, l_app_id, l_scs_code,l_k,l_mod, l_date_term_chr;
192 CLOSE c_chr;
193
194 -- Only for service class
195 If l_scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION') THEN
196 OPEN c_sts(l_code);
197 FETCH c_sts INTO l_sts_code;
198 CLOSE c_sts;
199 If l_sts_code = 'HOLD' AND l_code <> 'QA_HOLD' Then
200 RETURN(TRUE);
201 End If;
202 End If;
203
204
205 IF (l_mod is not null) and (l_mod <> OKC_API.G_MISS_CHAR) then
206 l_k := l_k ||'-'||l_mod;
207 END IF;
208
209 IF l_template_yn = 'Y' then
210 OKC_API.set_message(p_app_name => g_app_name,
211 p_msg_name => 'OKC_K_TEMPLATE',
212 p_token1 => 'NUMBER',
213 p_token1_value => l_k);
214 RETURN(FALSE);
215 END IF;
216
217 IF l_hdr_code = 'QA_HOLD' then
218 OKC_API.set_message(p_app_name => g_app_name,
219 p_msg_name => 'OKC_INVALID_STS',
220 p_token1 => 'component',
221 p_token1_value => l_k);
222 RETURN(FALSE);
223 END IF;
224
225 OPEN c_sts(l_code);
226 FETCH c_sts INTO l_sts_code;
227 CLOSE c_sts;
228
229 IF l_sts_code NOT IN ('ACTIVE','HOLD','SIGNED','EXPIRED') THEN
230 OKC_API.set_message(p_app_name => g_app_name,
231 p_msg_name => 'OKC_INVALID_STS',
232 p_token1 => 'component',
233 p_token1_value => l_no);
234 RETURN(FALSE);
235 END IF;
236
237 -- Added by MSENGUPT on 12/09/2001 for Bug#2143096
238
239 IF l_date_term_chr IS NOT NULL OR
240 l_date_term_cle IS NOT NULL Then
241 OKC_API.set_message(p_app_name => g_app_name,
242 p_msg_name => 'OKC_ALREADY_TERM');
243 RETURN(FALSE);
244 end if;
245 -- end of bug#2143096 -------------
246
247 If okc_util.Get_All_K_Access_Level(p_application_id => l_app_id,
248 p_chr_id => l_chr_id,
249 p_scs_code => l_scs_code) <> 'U' Then
250 OKC_API.set_message(p_app_name => g_app_name,
251 p_msg_name => 'OKC_NO_UPDATE',
252 p_token1 => 'CHR',
253 p_token1_value => l_k);
254 Return(FALSE);
255 End If;
256 --Bug# 3143093 ---
257
258 IF l_scs_code = 'WARRANTY' or l_scs_code = 'SERVICE' then
259 OPEN c_invoice(l_scs_code, l_sts_code);
260 FETCH c_invoice into l_allow;
261 CLOSE c_invoice;
262
263 IF l_allow <> 'Y' then
264 OKC_API.set_message(p_app_name => g_app_name,
265 p_msg_name => 'OKC_INVOICE_TERM',
266 p_token1 => 'component',
267 p_token1_value => l_no);
268 RETURN(FALSE);
269 END IF;
270 END IF;
271 --Bug# 3143093 ---
272
273 RETURN(TRUE);
274 END is_kl_term_allowed;
275
276
277 PROCEDURE validate_chr( p_api_version IN NUMBER,
278 p_init_msg_list IN VARCHAR2 ,
279 x_return_status OUT NOCOPY VARCHAR2,
280 x_msg_count OUT NOCOPY NUMBER,
281 x_msg_data OUT NOCOPY VARCHAR2,
282 p_terminate_in_parameters_rec IN terminate_in_parameters_rec ) is
283
284 CURSOR cur_k_header is
285 SELECT
286 STS_CODE,
287 CONTRACT_NUMBER,
288 CONTRACT_NUMBER_MODIFIER,
289 TEMPLATE_YN,
290 DATE_TERMINATED,
291 DATE_RENEWED,
292 APPLICATION_ID,
293 SCS_CODE
294 FROM okc_k_headers_b
295 WHERE id = p_terminate_in_parameters_rec.p_contract_id;
296
297
298 -- Will not need object_version_number this termination is an adverse step. Even if there
299 -- was a change in between, contract will be terminated.
300
301 l_chrv_rec cur_k_header%rowtype;
302
303 CURSOR is_k_locked is
304 SELECT 'Y'
305 FROM okc_k_processes v
306 WHERE v.chr_id = p_terminate_in_parameters_rec.p_contract_id
307 and v.in_process_yn='Y';
308
309 CURSOR cur_sts_code (l_code varchar2) is
310 SELECT sts.ste_code,sts.meaning
311 FROM okc_statuses_v sts
312 WHERE sts.code = l_code;
313
314 --Commeting Bug 4354983 Takintoy.
315 --SR check done in OKS_BILL_REC_PUB
316 /*
317 CURSOR cur_service_requests is
318 SELECT 'x'
319 FROM okx_incident_statuses_v xis,
320 okc_k_lines_b cle
321 WHERE cle.id = xis.contract_service_id
322 and cle.dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
323 and xis.status_code in ('OPEN'); -- Impact -- DepENDency on status of service requests
324 */
325
326 -- CURSOR cur_old_contract is
327 -- select contract_number,
328 -- contract_number_modifier
329 -- from okc_k_headers_b
330 -- where chr_id_renewed = p_terminate_in_parameters_rec.p_contract_id;
331
332 Cursor cur_old_contract(p_chr_id number) is
333 select k.contract_number,k.contract_number_modifier
334 from okc_k_headers_b k,okc_operation_lines a,
335 okc_operation_instances b,okc_class_operations c
336 where k.id=a.subject_chr_id
337 and a.object_chr_id=p_chr_id and
338 c.id=b.cop_id and c.opn_code='RENEWAL'
339 and b.id=a.oie_id and a.active_yn='Y' and
340 a.subject_cle_id is null and a.object_cle_id is null;
341 l_k_num okc_k_headers_v.contract_number%type;
342 l_k_mod okc_k_headers_v.contract_number_modifier%type;
343
344 -- Find out which statuses are valid FOR termination to continue
345
346 l_chg_request_in_process varchar2(1);
347
348 l_status varchar2(30); -- Impact on status
349 l_meaning okc_statuses_v.meaning%type;
350
351 l_return_status varchar2(1) := okc_api.g_ret_sts_success;
352
353 BEGIN
354
355 --dbms_output.put_line(' validate_chr (+) ');
356
357 x_return_status := okc_api.g_ret_sts_success;
358
359 okc_api.init_msg_list(p_init_msg_list);
360
361 OPEN cur_k_header;
362 FETCH cur_k_header into l_chrv_rec;
363 CLOSE cur_k_header;
364
365 IF l_chrv_rec.template_Yn = 'Y' THEN
366
367 OKC_API.set_message(p_app_name => g_app_name,
368 p_msg_name => 'OKC_K_TEMPLATE',
369 p_token1 => 'NUMBER',
370 p_token1_value => l_chrv_rec.contract_number);
371
372 x_return_status := okc_api.g_ret_sts_error;
373 RAISE g_exception_halt_validation;
374 END if;
375
376 OPEN is_k_locked;
377 FETCH is_k_locked into l_chg_request_in_process;
378
379 IF is_k_locked%FOUND THEN
380
381 OKC_API.set_message(p_app_name => g_app_name,
382 p_msg_name => 'OKC_K_LOCKED',
383 p_token1 => 'NUMBER',
384 p_token1_value => l_chrv_rec.contract_number);
385
386 x_return_status := okc_api.g_ret_sts_error;
387 CLOSE is_k_locked;
388 RAISE g_exception_halt_validation;
389
390 END IF;
391
392 CLOSE is_k_locked;
393
394 --Commeting Bug 4354983 Takintoy.
395 --SR check done in OKS_BILL_REC_PUB
396 /*
397 OPEN cur_service_requests;
398 FETCH cur_service_requests into l_status;
399
400 if cur_service_requests%FOUND THEN
401
402 OKC_API.set_message(p_app_name => g_app_name,
403 p_msg_name => 'OKC_SR_PENDING',
404 p_token1 => 'NUMBER',
405 p_token1_value => l_chrv_rec.contract_number);
406
407 x_return_status := okc_api.g_ret_sts_error;
408
409 CLOSE cur_service_requests;
410 RAISE g_exception_halt_validation;
411
412 END if;
413
414 CLOSE cur_service_requests;
415
416 */
417
418 l_status:='1';
419
420 OPEN cur_sts_code(l_chrv_rec.sts_code);
421 FETCH cur_sts_code into l_status,l_meaning;
422 CLOSE cur_sts_code;
423
424 IF l_status='1' then
425 --
426 OKC_API.set_message(p_app_name => g_app_name,
427 p_msg_name => 'OKC_INVALID_K_STATUS',
428 p_token1 => 'NUMBER',
429 p_token1_value => l_chrv_rec.contract_number,
430 p_token2 => 'MODIFIER',
431 p_token2_value => l_chrv_rec.contract_number_modifier,
432 p_token3 => 'STATUS',
433 p_token3_value => l_chrv_rec.sts_code);
434
435 RAISE g_exception_halt_validation;
436 --
437 END IF;
438
439 IF (l_status NOT IN ('ACTIVE','HOLD','SIGNED','EXPIRED')) OR (l_status='HOLD' and l_chrv_rec.sts_code='QA_HOLD') THEN
440
441 x_return_status := OKC_API.G_RET_STS_ERROR;
442
443 OKC_API.set_message(p_app_name => g_app_name,
444 p_msg_name => 'OKC_INVALID_K_STATUS',
445 p_token1 => 'NUMBER',
446 p_token1_value => l_chrv_rec.contract_number,
447 p_token2 => 'MODIFIER',
448 p_token2_value => l_chrv_rec.contract_number_modifier,
449 p_token3 => 'STATUS',
450 p_token3_value => l_meaning);
451
452 RAISE g_exception_halt_validation;
453
454 ELSIF l_chrv_rec.date_terminated is not null THEN
455
456 x_return_status := OKC_API.G_RET_STS_ERROR;
457
458 OKC_API.set_message(p_app_name => g_app_name,
459 p_msg_name => 'OKC_FUTURE_TERMINATED_K',
460 p_token1 => 'NUMBER',
461 p_token1_value => l_chrv_rec.contract_number );
462
463 RAISE g_exception_halt_validation;
464
465 ELSIF l_chrv_rec.date_renewed is not null THEN
466
467 open cur_old_contract(p_terminate_in_parameters_rec.p_contract_id);
468 fetch cur_old_contract into l_k_num,l_k_mod;
469 close cur_old_contract;
470
471 x_return_status := 'W';
472
473 OKC_API.set_message( p_app_name => g_app_name,
474 p_msg_name =>'OKC_RENEWED_CONTRACT_TERM',
475 -- nechatur 21-Jun-2006 Bug#5122905 Not display the original Contract and its Modifier, Only display the number and modifier of the renewed contract
476 /* p_token1 =>'NUMBER',
477 p_token1_value => l_chrv_rec.contract_number,
478 p_token2 =>'MODIFIER',
479 p_token2_value => l_chrv_rec.contract_number_modifier, */
480 p_token1 =>'NUMBER',
481 p_token1_value => l_k_num,
482 p_token2 =>'MODIFIER',
483 p_token2_value => l_k_mod
484 );
485 -- End nechatur Bug#5122905
486
487 END IF;
488 -- Bug 1349841, Use NVL for Perpetual Contracts
489 IF Nvl(p_terminate_in_parameters_rec.p_orig_end_date,
490 p_terminate_in_parameters_rec.p_termination_date + 1) <
491 p_terminate_in_parameters_rec.p_termination_date then
492
493 x_return_status := OKC_API.G_RET_STS_ERROR;
494
495 OKC_API.set_message( p_app_name => g_app_name,
496 p_msg_name =>'OKC_TRMDATE_MORE_END'
497 );
498
499
500 RAISE g_exception_halt_validation;
501 END IF;
502 --dbms_output.put_line(' validate_chr (+) ');
503 EXCEPTION
504 WHEN G_EXCEPTION_HALT_VALIDATION THEN
505 NULL;
506 WHEN OTHERS THEN
507 OKC_API.set_message(p_app_name => g_app_name,
508 p_msg_name => g_unexpected_error,
509 p_token1 => g_sqlcode_token,
510 p_token1_value => sqlcode,
511 p_token2 => g_sqlerrm_token,
512 p_token2_value => sqlerrm);
513
514 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
515
516 END;
517
518 PROCEDURE terminate_chr( p_api_version IN NUMBER,
519 p_init_msg_list IN VARCHAR2 ,
520 x_return_status OUT NOCOPY VARCHAR2,
521 x_msg_count OUT NOCOPY NUMBER,
522 x_msg_data OUT NOCOPY VARCHAR2,
523 p_terminate_in_parameters_rec IN terminate_in_parameters_rec
524 ) is
525
526 CURSOR cur_k_header is
527 SELECT ID,
528 OBJECT_VERSION_NUMBER,
529 sts_code
530 FROM okc_k_headers_v
531 WHERE id = p_terminate_in_parameters_rec.p_contract_id
532 for update of object_version_number nowait;
533
534 -- Will not need object_version_number this termination is an adverse step. Even if there
535 -- was a change in between, contract will be terminated.
536
537 rec_k_header OKC_CONTRACT_PUB.chrv_rec_type;
538 --
539 --
540 l_ste_code OKC_STATUSES_V.CODE%TYPE;
541
542 CURSOR C_GET_STE_CODE(p_sts_code varchar2) IS
543 SELECT STE_CODE
544 FROM OKC_STATUSES_V
545 WHERE CODE = p_sts_code;
546 --
547
548 CURSOR lock_lines is
549 SELECT cle.id,
550 cle.lse_id, --added so that we can tell if the line is price hold line
551 cle.object_version_number,
552 sts.ste_code,
553 cle.start_date,
554 cle.end_date
555 FROM okc_k_lines_v cle,
556 okc_statuses_b sts
557 WHERE cle.dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
558 AND cle.sts_code = sts.code
559 AND cle.date_terminated is null
560 FOR update of line_number nowait;
561
562 -- CURSOR lock_rules is
563 -- SELECT *
564 -- FROM okc_rules_v
565 -- WHERE dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
566 -- FOR update of object1_id1 nowait;
567
568 -- Bug 1524889: Changed the status check to <> TERMINATED from the
569 -- earlier ones. This check is required so that you do not try to call
570 -- the OKS routine that issues a credit memo for a line that was
571 -- terminated earlier using line termination.
572
573 -- Bug 1925467: Commented
574 -- Bug 1932363: Added another linestyle in the where for 'Usage'
575 /*
576 CURSOR service_top_lines is
577 SELECT cle.id,
578 cle.object_version_number,
579 cle.start_date,
580 cle.end_date
581 FROM okc_k_lines_b cle,
582 okc_line_styles_b lse,
583 okc_statuses_b sts,
584 okc_k_headers_b k,
585 okc_assents a,
586 okc_operations_b opn,
587 okc_val_line_operations lopn
588 WHERE cle.chr_id = p_terminate_in_parameters_rec.p_contract_id
589 and lse.id = cle.lse_id
590 and lopn.lse_id = cle.lse_id
591 and lse.lty_code in ('SERVICE','EXT_WARRANTY','USAGE')
592 and cle.sts_code = sts.code
593 and sts.ste_code <> 'TERMINATED'
594 and a.scs_code = k.scs_code
595 and a.sts_code = cle.sts_code
596 and a.allowed_yn = 'Y'
597 and a.opn_code = opn.code
598 and opn.opn_type = 'LON'
599 and opn.code = 'INVOICE'
600 and opn.code = lopn.opn_code
601 and cle.chr_id = k.id;
602 */
603
604 CURSOR cur_condition_headers is
605 SELECT id,object_version_number,date_active,date_inactive
606 FROM okc_condition_headers_b
607 WHERE dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
608 and ( date_inactive > p_terminate_in_parameters_rec.p_termination_date
609 or date_inactive is null )
610 FOR update of date_inactive nowait;
611
612 CURSOR cur_change_requests is
613 SELECT id,object_version_number
614 FROM okc_change_requests_b crt
615 WHERE crt.chr_id = p_terminate_in_parameters_rec.p_contract_id
616 and crt.crs_code = 'ENT' ---Impact on change request status
617 FOR update of datetime_ineffective nowait;
618
619 CURSOR cur_header_aa IS
620 SELECT k.estimated_amount,k.scs_code,scs.cls_code,k.sts_code
621 FROM OKC_K_HEADERS_B K,
622 OKC_SUBCLASSES_B SCS
623 WHERE k.id = p_terminate_in_parameters_rec.p_contract_id
624 AND k.scs_code = scs.code;
625
626 -- CURSOR for bug 1982629, TERMINATION OF AN EXPIRED CONTRACT
627 CURSOR c_get_terminate_date (b_cnh_id NUMBER) IS
628 SELECT greatest(nvl(co.datetime, cnh.date_active),
629 p_terminate_in_parameters_rec.p_termination_date )
630 FROM okc_condition_headers_b cnh,
631 okc_condition_occurs co
632 WHERE dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
633 AND cnh.id = b_cnh_id
634 AND cnh.id = co.cnh_id (+);
635
636 l_lse_id number;
637 l_cnh_terminate_date DATE;
638 l_scs_code okc_subclasses_v.code%type;
639 l_cls_code okc_subclasses_v.cls_code%type;
640 l_k_status_code okc_k_headers_v.sts_code%type;
641 l_estimated_amount number;
642 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
643
644 l_api_name constant varchar2(30) := 'terminate_chr';
645
646 l_chrv_rec okc_contract_pub.chrv_rec_type ;
647 i_chrv_rec okc_contract_pub.chrv_rec_type ;
648 l_clev_rec okc_contract_pub.clev_rec_type ;
649 i_clev_rec okc_contract_pub.clev_rec_type ;
650 l_cnh_rec okc_conditions_pub.cnhv_rec_type ;
651 i_cnh_rec okc_conditions_pub.cnhv_rec_type ;
652
653 l_crtv_rec okc_change_request_pub.crtv_rec_type;
654 i_crtv_rec okc_change_request_pub.crtv_rec_type;
655
656 E_Resource_Busy EXCEPTION;
657 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
658 l_amount number;
659 l_ter_status_code varchar2(30);
660 l_can_status_code varchar2(30);
661
662 BEGIN
663
664 --dbms_output.put_line(' terminate_chr (+) ');
665 x_return_status := OKC_API.G_RET_STS_SUCCESS;
666
667 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
668 p_init_msg_list,
669 '_PROCESS',
670 x_return_status);
671
672 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
673 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
674 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
675 raise OKC_API.G_EXCEPTION_ERROR;
676 END IF;
677
678 OPEN cur_k_header;
679 FETCH cur_k_header into
680 rec_k_header.ID,
681 rec_k_header.OBJECT_VERSION_NUMBER,
682 rec_k_header.STS_CODE;
683
684 if cur_k_header%NOTFOUND THEN
685
686 OKC_API.set_message(p_app_name => g_app_name,
687 p_msg_name => 'OKC_K_CHANGED',
688 p_token1 => 'NUMBER',
689 --p_token1_value => rec_k_header.contract_number);
690 p_token1_value => p_terminate_in_parameters_rec.p_contract_number,
691 p_token2 => 'MODIFIER',
692 p_token2_value => p_terminate_in_parameters_rec.p_contract_modifier);
693
694 x_return_status := OKC_API.G_RET_STS_ERROR;
695
696 CLOSE cur_k_header;
697
698 raise OKC_API.G_EXCEPTION_ERROR;
699
700 END if;
701
702 CLOSE cur_k_header;
703
704 --dbms_output.put_line(' validate_chr (+) ');
705
706 OKC_TERMINATE_PVT.validate_chr(p_api_version => 1,
707 p_init_msg_list => OKC_API.G_FALSE,
708 x_return_status => l_return_status,
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data,
711 p_terminate_in_parameters_rec => p_terminate_in_parameters_rec);
712
713 --dbms_output.put_line(' validate_chr (-) ');
714 IF l_return_status = 'W' then
715 x_return_status := 'W';
716 end if;
717 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
718 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
719 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
720 raise OKC_API.G_EXCEPTION_ERROR;
721 END IF;
722
723 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
724 p_status_type => 'TERMINATED',
725 x_status_code => l_ter_status_code);
726 IF (l_debug = 'Y') THEN
727 okc_debug.set_trace_off;
728 END IF;
729 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
730 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
731 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
732 raise OKC_API.G_EXCEPTION_ERROR;
733 END IF;
734 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
735 p_status_type => 'CANCELLED',
736 x_status_code => l_can_status_code);
737 IF (l_debug = 'Y') THEN
738 okc_debug.set_trace_off;
739 END IF;
740 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
741 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
742 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
743 raise OKC_API.G_EXCEPTION_ERROR;
744 END IF;
745 l_chrv_rec := rec_k_header;
746
747 l_chrv_rec.trn_code := p_terminate_in_parameters_rec.p_termination_reason;
748 l_chrv_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
749 l_chrv_rec.id := rec_k_header.id;
750 l_chrv_rec.object_version_number := rec_k_header.object_version_number;
751
752 IF p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
753
754 l_chrv_rec.sts_code := l_ter_status_code;
755 --
756 -- Added for Bug# 1468224, Action Assembler/OKE related changes
757 --
758 OPEN C_GET_STE_CODE(rec_k_header.sts_code);
759 FETCH C_GET_STE_CODE INTO l_ste_code;
760 CLOSE C_GET_STE_CODE;
761 --
762 l_chrv_rec.old_sts_code := rec_k_header.sts_code;
763 l_chrv_rec.new_sts_code := l_ter_status_code;
764 l_chrv_rec.old_ste_code := l_ste_code;
765 l_chrv_rec.new_ste_code := 'TERMINATED';
766 --
767 END if;
768
769 okc_contract_pub.update_contract_header ( p_api_version => 1,
770 p_init_msg_list => OKC_API.G_FALSE,
771 x_return_status => l_return_status,
772 x_msg_count => x_msg_count,
773 x_msg_data => x_msg_data,
774 p_restricted_update => okc_api.g_true,
775 p_chrv_rec => l_chrv_rec,
776 x_chrv_rec => i_chrv_rec );
777
778 --dbms_output.put_line(' update_contract_header (-) ');
779
780 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
781 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
782 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
783 raise OKC_API.G_EXCEPTION_ERROR;
784 END IF;
785
786 -- Bug 1473143: The loop below was after the loop to update contract
787 -- lines. The cursor was not working because at that point the lines
788 -- were already terminated and the status code would not match. It
789 -- would not be enough to change the cursor clause to look for
790 -- terminated lines only because this would also include lines that
791 -- had been terminated much earlier - not in the current run. Hence
792 -- this procedure is being moved up.
793
794 --
795 -- Bug 1827571: Commented out the following check as it prevents issue
796 -- of credit memo if the termination date is some time in future.
797 --
798 -- IF p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
799 /* Commented for Bug 1925467
800 FOR lines_rec IN service_top_lines LOOP
801
802 -- Bug 1524889: Removed date check which used to exist here. The date
803 -- check ensured that the OKS routine was called only when the
804 -- termination date was between the start and end date of the line.
805 -- This check is redudant as the OKS routine should only be called
806 -- based on the status of the line. To see the exact code removed,
807 -- take a look at the earlier version.
808
809 --dbms_output.put_line(' PRE_TERMINATE_SERVICE (+) ');
810
811 OKS_BILL_REC_PUB.pre_terminate_service ( p_api_version => 1,
812 p_init_msg_list => OKC_API.G_FALSE,
813 x_return_status => l_return_status,
814 p_calledfrom => NULL,
815 x_msg_count => x_msg_count,
816 x_msg_data => x_msg_data,
817 p_k_line_id => lines_rec.id,
818 p_termination_date => p_terminate_in_parameters_rec.p_termination_date,
819 p_termination_flag => 1,
820 x_amount => l_amount );
821
822 --dbms_output.put_line(' PRE_TERMINATE_SERVICE (-) ');
823
824 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
825 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
826 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
827 raise OKC_API.G_EXCEPTION_ERROR;
828 END IF;
829 END LOOP;
830 */
831 -- END if;
832 FOR lines_rec in lock_lines LOOP
833 IF (l_debug = 'Y') THEN
834 okc_debug.set_trace_off;
835 END IF;
836 l_lse_id := lines_rec.lse_id;
837
838 IF lines_rec.ste_code in ('ACTIVE','ENTERED','HOLD','SIGNED','EXPIRED') THEN
839 -- Bug 1349841, Use NVL for Perpetual Contracts
840 IF (p_terminate_in_parameters_rec.p_termination_date <=
841 Nvl(lines_rec.end_date, p_terminate_in_parameters_rec.p_termination_date))
842 --san bug 1662549
843 -- and (p_terminate_in_parameters_rec.p_termination_date >= lines_rec.start_date)
844 then
845
846 IF p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
847
848 IF lines_rec.ste_code in ('ACTIVE','HOLD','SIGNED','EXPIRED') then
849
850 l_clev_rec.sts_code := l_ter_status_code; -- Impact on status --use default method
851 l_clev_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
852 --Bug 3378196
853 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
854 l_clev_rec.date_terminated := lines_rec.start_date;
855 end if;
856
857 ELSIF lines_rec.ste_code = 'ENTERED' then
858
859 l_clev_rec.sts_code := l_can_status_code; -- Impact on status --use default method
860 l_clev_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
861 --Bug 3378196
862 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
863 l_clev_rec.date_terminated := lines_rec.start_date;
864 end if;
865 END IF;
866
867 ELSE
868
869 IF lines_rec.ste_code in ('ACTIVE','HOLD','SIGNED','ENTERED','EXPIRED') then
870 l_clev_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
871 --Bug 3378196
872 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
873 l_clev_rec.date_terminated := lines_rec.start_date;
874 end if;
875 END IF;
876
877 /*
878 IF lines_rec.ste_code = 'ACTIVE' then
879 l_clev_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
880 ELSIF lines_rec.ste_code = 'ENTERED' then
881 l_clev_rec.date_terminated := p_terminate_in_parameters_rec.p_termination_date;
882 NULL;
883 END IF;
884 */
885
886 END IF;
887
888 l_clev_rec.id := lines_rec.id;
889 l_clev_rec.object_version_number := lines_rec.object_version_number;
890 l_clev_rec.trn_code := p_terminate_in_parameters_rec.p_termination_reason;
891 --
892 -- Bug# 1405237 Avoide calling Action Assembler when the change in line status is resulting from a change in the header status
893 --
894 l_clev_rec.call_action_asmblr := 'N';
895
896
897 --dbms_output.put_line(' update_contract_line (+) ');
898
899 OKC_CONTRACT_PUB.update_contract_line ( p_api_version => 1,
900 p_init_msg_list => OKC_API.G_FALSE,
901 x_return_status => l_return_status,
902 x_msg_count => x_msg_count,
903 x_msg_data => x_msg_data,
904 p_restricted_update => okc_api.g_true,
905 p_clev_rec => l_clev_rec,
906 x_clev_rec => i_clev_rec );
907 IF (l_debug = 'Y') THEN
908 okc_debug.set_trace_off;
909 END IF;
910 --dbms_output.put_line(' update_contract_line (-) ');
911
912 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
913 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
914 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
915 raise OKC_API.G_EXCEPTION_ERROR;
916 END IF;
917
918
919 If l_lse_id = 61 Then
920 --if the contract line being terminated is a Price Hold line, we need to delete the entry in QP
921 --disable the entry in QP
922 OKC_PHI_PVT.process_price_hold(
923 p_api_version => p_api_version,
924 p_init_msg_list => p_init_msg_list,
925 x_return_status => l_return_status,
926 x_msg_count => x_msg_count,
927 x_msg_data => x_msg_data,
928 p_chr_id => p_terminate_in_parameters_rec.p_contract_id,
929 p_termination_date => l_clev_rec.date_terminated,
930 p_operation_code => 'TERMINATE');
931 End If;
932
933 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
934 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
935 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
936 raise OKC_API.G_EXCEPTION_ERROR;
937 END IF;
938
939
940
941 END IF; -- effectivity check
942 END IF; -- ste_code
943
944 END LOOP;
945 IF (l_debug = 'Y') THEN
946 okc_debug.set_trace_on;
947 END IF;
948 FOR conditions_rec in cur_condition_headers LOOP
949 IF (l_debug = 'Y') THEN
950 okc_debug.set_trace_off;
951 END IF;
952 l_cnh_rec.id := conditions_rec.id;
953 l_cnh_rec.object_version_number := conditions_rec.object_version_number;
954 l_cnh_rec.date_inactive := p_terminate_in_parameters_rec.p_termination_date;
955 --san the following change done to avoid error return in case date_active is greater than
956 --termination date and we try to make date_inactive equal to termination date
957 -- This returns error from conditions API. So make both the dates equal to termination date
958 IF l_cnh_rec.date_active > p_terminate_in_parameters_rec.p_termination_date then
959 l_cnh_rec.date_active := p_terminate_in_parameters_rec.p_termination_date;
960 END IF;
961
962 OPEN c_get_terminate_date(conditions_rec.id);
963 FETCH c_get_terminate_date INTO l_cnh_terminate_date;
964 IF c_get_terminate_date%FOUND THEN
965 l_cnh_rec.date_inactive := l_cnh_terminate_date;
966 END IF;
967 CLOSE c_get_terminate_date;
968
969 --dbms_output.put_line(' update_cond_hdrs (+) ');
970
971 OKC_CONDITIONS_PUB.update_cond_hdrs( p_api_version => 1,
972 p_init_msg_list => OKC_API.G_FALSE,
973 x_return_status => l_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_cnhv_rec => l_cnh_rec,
977 x_cnhv_rec => i_cnh_rec);
978 IF (l_debug = 'Y') THEN
979 okc_debug.set_trace_off;
980 END IF;
981 --dbms_output.put_line(' update_cond_hdrs (-) ');
982
983 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
984 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
985 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
986 RAISE OKC_API.G_EXCEPTION_ERROR;
987 END IF;
988
989 END LOOP;
990 IF (l_debug = 'Y') THEN
991 okc_debug.set_trace_on;
992 END IF;
993 FOR change_requests_rec in cur_change_requests LOOP
994 IF (l_debug = 'Y') THEN
995 okc_debug.set_trace_off;
996 END IF;
997 l_crtv_rec.id := change_requests_rec.id;
998 l_crtv_rec.object_version_number := change_requests_rec.object_version_number;
999 l_crtv_rec.datetime_ineffective := p_terminate_in_parameters_rec.p_termination_date;
1000
1001 -- Impact on change request status
1002
1003 --dbms_output.put_line(' update_change_request (+) ');
1004
1005 OKC_CHANGE_REQUEST_PUB.update_change_request( p_api_version => 1,
1006 p_init_msg_list => OKC_API.G_FALSE,
1007 x_return_status => l_return_status,
1008 x_msg_count => x_msg_count,
1009 x_msg_data => x_msg_data,
1010 p_crtv_rec => l_crtv_rec,
1011 x_crtv_rec => i_crtv_rec);
1012 IF (l_debug = 'Y') THEN
1013 okc_debug.set_trace_off;
1014 END IF;
1015 --dbms_output.put_line(' update_change_request (-) ');
1016
1017 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1018 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1019 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1020 RAISE OKC_API.G_EXCEPTION_ERROR;
1021 END IF;
1022
1023 END LOOP;
1024 IF (l_debug = 'Y') THEN
1025 okc_debug.set_trace_on;
1026 END IF;
1027
1028 --dbms_output.put_line(' acn_assemble (+) ');
1029 -- Raise the event
1030 IF p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
1031
1032 --dbms_output.put_line(' acn_assemble (+) ');
1033
1034 open cur_header_aa;
1035 fetch cur_header_aa into l_estimated_amount,l_scs_code,l_cls_code,l_k_status_code;
1036 close cur_header_aa;
1037
1038 OKC_K_TERM_ASMBLR_PVT.acn_assemble(p_api_version => 1,
1039 p_init_msg_list => OKC_API.G_FALSE,
1040 x_return_status => l_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_k_id => p_terminate_in_parameters_rec.p_contract_id,
1044 p_k_number => p_terminate_in_parameters_rec.p_contract_number,
1045 p_k_nbr_mod => p_terminate_in_parameters_rec.p_contract_modifier,
1046 p_term_date => p_terminate_in_parameters_rec.p_termination_date,
1047 p_term_reason => p_terminate_in_parameters_rec.p_termination_reason,
1048 p_k_class => l_cls_code,
1049 p_k_subclass => l_scs_code,
1050 p_k_status_code => l_k_status_code,
1051 p_estimated_amount => l_estimated_amount);
1052
1053 --dbms_output.put_line(' acn_assemble (-) ');
1054 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1056 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1057 RAISE OKC_API.G_EXCEPTION_ERROR;
1058 END IF;
1059
1060 END IF;
1061
1062 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1063 --dbms_output.put_line(' terminate_chr (-) ');
1064 EXCEPTION
1065 WHEN E_Resource_Busy THEN
1066 IF (l_debug = 'Y') THEN
1067 okc_debug.set_trace_on;
1068 END IF;
1069 x_return_status := okc_api.g_ret_sts_error;
1070
1071 OKC_API.set_message(G_FND_APP,
1072 G_FORM_UNABLE_TO_RESERVE_REC);
1073 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1074
1075 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1076 IF (l_debug = 'Y') THEN
1077 okc_debug.set_trace_on;
1078 END IF;
1079 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1080 (l_api_name,
1081 G_PKG_NAME,
1082 'OKC_API.G_RET_STS_ERROR',
1083 x_msg_count,
1084 x_msg_data,
1085 '_PROCESS');
1086 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1087 IF (l_debug = 'Y') THEN
1088 okc_debug.set_trace_on;
1089 END IF;
1090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1091 (l_api_name,
1092 G_PKG_NAME,
1093 'OKC_API.G_RET_STS_UNEXP_ERROR',
1094 x_msg_count,
1095 x_msg_data,
1096 '_PROCESS');
1097 WHEN OTHERS THEN
1098 IF (l_debug = 'Y') THEN
1099 okc_debug.set_trace_on;
1100 END IF;
1101 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1102 (l_api_name,
1103 G_PKG_NAME,
1104 'OTHERS',
1105 x_msg_count,
1106 x_msg_data,
1107 '_PROCESS');
1108 END;
1109
1110 PROCEDURE validate_cle( p_api_version IN NUMBER,
1111 p_init_msg_list IN VARCHAR2 ,
1112 x_return_status OUT NOCOPY VARCHAR2,
1113 x_msg_count OUT NOCOPY NUMBER,
1114 x_msg_data OUT NOCOPY VARCHAR2,
1115 p_terminate_in_parameters_rec IN terminate_in_cle_rec
1116 ) is
1117
1118 CURSOR is_line_a_top_line is
1119 SELECT 'x'
1120 FROM okc_k_lines_b
1121 WHERE id = p_terminate_in_parameters_rec.p_cle_id
1122 and chr_id is not null;
1123
1124 -- Bug 1925467 Commented
1125 -- Bug 1932363: Added another linestyle in the where for 'Usage'
1126 /*
1127 CURSOR is_line_a_service_line is
1128 SELECT 'x'
1129 FROM okc_k_lines_b cle,
1130 okc_line_styles_b lse
1131 WHERE cle.id = p_terminate_in_parameters_rec.p_cle_id
1132 and cle.lse_id = lse.id
1133 and lse.lty_code in ('SERVICE','EXT_WARRANTY','USAGE');
1134 */
1135 CURSOR k_csr is
1136 SELECT k.template_Yn, k.date_renewed, k.contract_number,
1137 k.contract_number_modifier, k.sts_code, k.application_id,
1138 k.scs_code
1139 FROM okc_k_headers_b k
1140 WHERE k.id = p_terminate_in_parameters_rec.p_dnz_chr_id;
1141
1142 l_chrv_rec k_csr%rowtype;
1143
1144 CURSOR is_k_locked is
1145 SELECT 'x'
1146 FROM okc_k_processes v
1147 WHERE v.chr_id = p_terminate_in_parameters_rec.p_dnz_chr_id
1148 and v.in_process_yn = 'Y';
1149
1150 -- p_code changed from number to Varchar2
1151 CURSOR cur_sts_code(p_code VARCHAR2) is
1152 SELECT sts.ste_code,sts.meaning
1153 FROM okc_statuses_v sts
1154 WHERE sts.code = p_code;
1155 CURSOR cur_service_requests is
1156 SELECT 'x'
1157 FROM okx_incident_statuses_v xis,
1158 okc_k_lines_b cle
1159 WHERE cle.id = xis.contract_service_id
1160 --san below cle.id condition added as we just want to check SRs againt this line only.
1161 and cle.id=p_terminate_in_parameters_rec.p_cle_id --bug 1325866
1162 and xis.status_code in ('OPEN'); -- Impact -- DepENDency on status of service requests
1163
1164 -- Find out which statuses are valid FOR termination to continue
1165
1166 l_chg_request_in_process varchar2(1);
1167
1168 l_status varchar2(30) ; -- Impact on status
1169 l_meaning okc_statuses_v.meaning%type;
1170
1171 l_return_status varchar2(1) := okc_api.g_ret_sts_success;
1172 l_dummy varchar2(1);
1173
1174 --CURSOR cur_old_contract is
1175 --SELECT contract_number,contract_number_modifier
1176 --FROM okc_k_headers_b
1177 --WHERE chr_id_renewed = p_terminate_in_parameters_rec.p_dnz_chr_id;
1178
1179 Cursor cur_old_contract(p_chr_id number) is
1180 select k.contract_number,k.contract_number_modifier
1181 from okc_k_headers_b k,okc_operation_lines a,
1182 okc_operation_instances b,okc_class_operations c
1183 where k.id=a.subject_chr_id
1184 and a.object_chr_id=p_chr_id and
1185 c.id=b.cop_id and c.opn_code='RENEWAL'
1186 and b.id=a.oie_id and a.active_yn='Y' and
1187 a.subject_cle_id is null and a.object_cle_id is null;
1188
1189 l_k_num okc_k_headers_v.contract_number%type;
1190 l_k_mod okc_k_headers_v.contract_number_modifier%type;
1191 BEGIN
1192
1193 x_return_status := okc_api.g_ret_sts_success;
1194
1195 okc_api.init_msg_list(p_init_msg_list);
1196
1197 --dbms_output.put_line('is top line (+)');
1198
1199
1200 OPEN is_line_a_top_line;
1201 FETCH is_line_a_top_line into l_dummy;
1202
1203 IF is_line_a_top_line%NOTFOUND THEN
1204
1205 OKC_API.set_message(p_app_name => g_app_name,
1206 p_msg_name => 'OKC_CLE_NOT_TOP_LINE' ,
1207 p_token1 => 'NUMBER',
1208 p_token1_value => p_terminate_in_parameters_rec.p_line_number);
1209
1210 x_return_status := okc_api.g_ret_sts_error;
1211 CLOSE is_line_a_top_line;
1212 raise G_EXCEPTION_HALT_VALIDATION;
1213 END if;
1214
1215 CLOSE is_line_a_top_line;
1216
1217 --dbms_output.put_line('is top line (-)');
1218 Open k_csr;
1219 Fetch k_csr Into l_chrv_rec;
1220 Close k_csr;
1221 --dbms_output.put_line('is k locked (+)');
1222
1223 OPEN is_k_locked;
1224 FETCH is_k_locked into l_dummy;
1225
1226 IF is_k_locked%FOUND THEN
1227
1228 OKC_API.set_message(p_app_name => g_app_name,
1229 p_msg_name => 'OKC_K_LOCKED',
1230 p_token1 => 'NUMBER',
1231 p_token1_value => l_chrv_rec.contract_number);
1232
1233 x_return_status := okc_api.g_ret_sts_error;
1234 CLOSE is_k_locked;
1235 raise G_EXCEPTION_HALT_VALIDATION;
1236 END if;
1237 CLOSE is_k_locked;
1238
1239 --dbms_output.put_line('is k locked (-)');
1240
1241 If l_chrv_rec.template_Yn = 'Y' THEN
1242
1243 OKC_API.set_message(p_app_name => g_app_name,
1244 p_msg_name => 'OKC_K_TEMPLATE',
1245 p_token1 => 'NUMBER',
1246 p_token1_value => l_chrv_rec.contract_number);
1247
1248 x_return_status := okc_api.g_ret_sts_error;
1249 raise G_EXCEPTION_HALT_VALIDATION;
1250 END if;
1251
1252 IF l_chrv_rec.sts_code='QA_HOLD' then
1253 OPEN cur_sts_code(l_chrv_rec.sts_code);
1254 FETCH cur_sts_code into l_status,l_meaning;
1255 CLOSE cur_sts_code;
1256
1257 x_return_status := OKC_API.G_RET_STS_ERROR;
1258
1259 OKC_API.set_message(p_app_name => g_app_name,
1260 p_msg_name => 'OKC_INVALID_K_STATUS',
1261 p_token1 => 'NUMBER',
1262 p_token1_value => l_chrv_rec.contract_number,
1263 p_token2 => 'MODIFIER',
1264 p_token2_value => l_chrv_rec.contract_number_modifier,
1265 p_token3 => 'STATUS',
1266 p_token3_value => l_meaning);
1267
1268 RAISE g_exception_halt_validation;
1269 END IF;
1270
1271 --dbms_output.put_line('is service lin (+)');
1272 /*
1273
1274 /*
1275 OPEN is_line_a_service_line;
1276 FETCH is_line_a_service_line into l_dummy;
1277
1278 if is_line_a_service_line%FOUND THEN
1279
1280 --dbms_output.put_line('is service reqests (+)');
1281
1282 OPEN cur_service_requests;
1283 FETCH cur_service_requests into l_status;
1284
1285 IF cur_service_requests%FOUND THEN
1286
1287 OKC_API.set_message(p_app_name => g_app_name,
1288 p_msg_name => 'OKC_SR_PENDING',
1289 p_token1 => 'NUMBER',
1290 p_token1_value => l_chrv_rec.contract_number);
1291
1292 x_return_status := okc_api.g_ret_sts_error;
1293 CLOSE cur_service_requests;
1294 raise G_EXCEPTION_HALT_VALIDATION;
1295 END if;
1296
1297 CLOSE cur_service_requests;
1298 --dbms_output.put_line('is service reqests (-)');
1299
1300 else
1301 CLOSE is_line_a_service_line;
1302 END if;
1303 */
1304
1305 --dbms_output.put_line('is service lin (-)');
1306
1307 OPEN cur_sts_code(p_terminate_in_parameters_rec.p_sts_code);
1308 FETCH cur_sts_code into l_status,l_meaning;
1309 CLOSE cur_sts_code;
1310
1311 IF l_status NOT IN ('ACTIVE','HOLD','SIGNED','EXPIRED') THEN
1312
1313 x_return_status := OKC_API.G_RET_STS_ERROR;
1314
1315 OKC_API.set_message(p_app_name => g_app_name,
1316 p_msg_name => 'OKC_INVALID_K_STATUS',
1317 p_token1 => 'NUMBER',
1318 p_token1_value => p_terminate_in_parameters_rec.p_line_number,
1319 p_token2 => 'STATUS',
1320 p_token2_value => l_meaning);
1321
1322 RAISE g_exception_halt_validation;
1323 END IF;
1324
1325 if l_chrv_rec.date_renewed is not null THEN
1326
1327 --open cur_old_contract;
1328 open cur_old_contract(p_terminate_in_parameters_rec.p_dnz_chr_id);
1329 fetch cur_old_contract into l_k_num,l_k_mod;
1330 close cur_old_contract;
1331
1332 x_return_status := 'W';
1333
1334 OKC_API.set_message( p_app_name => g_app_name,
1335 p_msg_name =>'OKC_RENEWED_CONTRACT_TERM',
1336 -- nechatur 21-Jun-2006 Bug#5122905 Not display the original Contract and its Modifier, Only display the number and modifier of the renewed contract
1337 /* p_token1 =>'NUMBER',
1338 p_token1_value => l_chrv_rec.contract_number,
1339 p_token2 =>'MODIFIER',
1340 p_token2_value => l_chrv_rec.contract_number_modifier, */
1341 p_token1 =>'NUMBER',
1342 p_token1_value => l_k_num,
1343 p_token2 =>'MODIFIER',
1344 p_token2_value => l_k_mod
1345 );
1346 -- End nechatur Bug#5122905
1347
1348 END IF;
1349
1350 -- Bug 1349841, Use NVL for Perpetual Contracts
1351 IF Nvl(p_terminate_in_parameters_rec.p_orig_end_date,
1352 p_terminate_in_parameters_rec.p_termination_date + 1) <
1353 p_terminate_in_parameters_rec.p_termination_date then
1354
1355 x_return_status := OKC_API.G_RET_STS_ERROR;
1356
1357 OKC_API.set_message( p_app_name => g_app_name,
1358 p_msg_name =>'OKC_TRMDATE_MORE_END');
1359
1360
1361 RAISE g_exception_halt_validation;
1362 END IF;
1363 EXCEPTION
1364 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1365 NULL;
1366 WHEN OTHERS THEN
1367 OKC_API.set_message(p_app_name => g_app_name,
1368 p_msg_name => g_unexpected_error,
1369 p_token1 => g_sqlcode_token,
1370 p_token1_value => sqlcode,
1371 p_token2 => g_sqlerrm_token,
1372 p_token2_value => sqlerrm);
1373
1374 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1375 END;
1376
1377 PROCEDURE terminate_cle( p_api_version IN NUMBER,
1378 p_init_msg_list IN VARCHAR2 ,
1379 x_return_status OUT NOCOPY VARCHAR2,
1380 x_msg_count OUT NOCOPY NUMBER,
1381 x_msg_data OUT NOCOPY VARCHAR2,
1382 p_terminate_in_parameters_rec IN terminate_in_cle_rec
1383 )is
1384
1385 l_return_status varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1386 l_api_name constant varchar2(30) := 'terminate_cle';
1387 l_dummy varchar2(1);
1388
1389 CURSOR cur_header is
1390 SELECT k.contract_number,k.contract_number_modifier,k.scs_code,scs.cls_code,
1391 cle.price_negotiated,cle.sts_code
1392 FROM okc_k_headers_b k,
1393 okc_subclasses_b scs,
1394 okc_k_lines_b cle
1395 WHERE k.id = p_terminate_in_parameters_rec.p_dnz_chr_id
1396 AND cle.chr_id = k.id
1397 AND cle.id = p_terminate_in_parameters_rec.p_cle_id
1398 AND k.scs_code = scs.code;
1399
1400 l_contract_modifier okc_k_headers_v.contract_number_modifier%type;
1401 l_contract_number okc_k_headers_v.contract_number%type;
1402 l_kl_sts_code okc_k_lines_v.sts_code%type;
1403
1404 l_scs_code okc_subclasses_v.code%type;
1405 l_cls_code okc_subclasses_v.cls_code%type;
1406 l_price_negotiated number;
1407
1408 CURSOR cur_lines is
1409 SELECT id,object_version_number,sts_code,lse_id,start_date,end_date
1410 FROM okc_k_lines_b
1411 WHERE date_terminated is null
1412 START WITH id = p_terminate_in_parameters_rec.p_cle_id
1413 CONNECT BY PRIOR id = cle_id
1414 ORDER BY LEVEL DESC
1415 FOR UPDATE OF id NOWAIT;
1416
1417 -- Bug 1925467: Commented
1418 -- Bug 1932363: Added another linestyle in the where for 'Usage'
1419 /*
1420 CURSOR is_line_a_service_line is
1421 SELECT 'x'
1422 FROM okc_k_lines_b cle,
1423 okc_line_styles_b lse
1424 WHERE cle.id = p_terminate_in_parameters_rec.p_cle_id
1425 and cle.lse_id = lse.id
1426 and lse.lty_code in ('SERVICE','EXT_WARRANTY','USAGE');
1427 */
1428
1429 CURSOR cur_status (p_code in varchar2) is
1430 SELECT ste_code
1431 FROM okc_statuses_b
1432 WHERE code = p_code;
1433
1434 l_clev_tbl okc_contract_pub.clev_tbl_type;
1435 i_clev_tbl okc_contract_pub.clev_tbl_type;
1436 L_LOOP_COUNTER NUMBER := 0;
1437
1438 E_Resource_Busy EXCEPTION;
1439 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1440
1441 l_amount number;
1442 l_ter_status_code varchar2(30);
1443 l_can_status_code varchar2(30);
1444
1445 l_status fnd_lookups.lookup_code%type;
1446 l_lse_id NUMBER;
1447
1448 BEGIN
1449 --dbms_output.put_line(' terminate_cle (+) ');
1450
1451 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1452
1453 l_return_status := OKC_API.START_ACTIVITY( l_api_name,
1454 p_init_msg_list,
1455 '_PROCESS',
1456 x_return_status );
1457
1458 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1459 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1460 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1461 raise OKC_API.G_EXCEPTION_ERROR;
1462 END IF;
1463
1464 --dbms_output.put_line(' VALIDATE_CLE (+) ');
1465
1466 OKC_TERMINATE_PUB.validate_cle(p_api_version => 1,
1467 p_init_msg_list => OKC_API.G_FALSE,
1468 x_return_status => l_return_status,
1469 x_msg_count => x_msg_count,
1470 x_msg_data => x_msg_data,
1471 p_terminate_in_parameters_rec => p_terminate_in_parameters_rec);
1472
1473 --dbms_output.put_line(' VALIDATE_CLE (-) ');
1474
1475 -- Special status used to show user a message that a renewed contract already exists
1476 IF l_return_status = 'W' then
1477 x_return_status := 'W';
1478 END IF;
1479
1480 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1481 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1483 raise OKC_API.G_EXCEPTION_ERROR;
1484 END IF;
1485
1486 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
1487 p_status_type => 'TERMINATED',
1488 x_status_code => l_ter_status_code );
1489 IF (l_debug = 'Y') THEN
1490 okc_debug.set_trace_off;
1491 END IF;
1492 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1493 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1494 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1495 raise OKC_API.G_EXCEPTION_ERROR;
1496 END IF;
1497 OKC_ASSENT_PUB.get_default_status( x_return_status => l_return_status,
1498 p_status_type => 'CANCELLED',
1499 x_status_code => l_can_status_code);
1500 IF (l_debug = 'Y') THEN
1501 okc_debug.set_trace_off;
1502 END IF;
1503 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1504 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1505 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1506 raise OKC_API.G_EXCEPTION_ERROR;
1507 END IF;
1508 IF (l_debug = 'Y') THEN
1509 OKC_DEBUG.SET_TRACE_OFF;
1510 END IF;
1511 FOR lines_rec in cur_lines LOOP
1512 l_loop_counter := l_loop_counter + 1;
1513 IF (l_debug = 'Y') THEN
1514 okc_debug.set_trace_off;
1515 END IF;
1516 OPEN cur_status(lines_rec.sts_code);
1517 fetch cur_status into l_status;
1518 close cur_status;
1519
1520 l_lse_id := lines_rec.lse_id;
1521
1522 IF l_status in ('ACTIVE','HOLD','SIGNED','ENTERED','EXPIRED') then
1523
1524 -- Bug 1349841, Use NVL for Perpetual Contracts
1525 IF (p_terminate_in_parameters_rec.p_termination_date <=
1526 Nvl(lines_rec.end_date, p_terminate_in_parameters_rec.p_termination_date))
1527 --san bug 1662549
1528 --and (p_terminate_in_parameters_rec.p_termination_date >= lines_rec.start_date)
1529 then
1530 If p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
1531
1532 IF l_status in ('ACTIVE','HOLD','SIGNED','EXPIRED') then
1533
1534 l_clev_tbl(l_loop_counter).sts_code := l_ter_status_code;
1535 l_clev_tbl(l_loop_counter).date_terminated := p_terminate_in_parameters_rec.p_termination_date;
1536 --Bug 3378196
1537 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
1538 l_clev_tbl(l_loop_counter).date_terminated := lines_rec.start_date;
1539 end if;
1540 ELSIF l_status = 'ENTERED' then
1541
1542 l_clev_tbl(l_loop_counter).sts_code := l_can_status_code;
1543 l_clev_tbl(l_loop_counter).date_terminated := p_terminate_in_parameters_rec.p_termination_date;
1544 --Bug 3378196
1545 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
1546 l_clev_tbl(l_loop_counter).date_terminated := lines_rec.start_date;
1547 end if;
1548 END IF;
1549
1550
1551 ELSE
1552
1553 IF l_status in ('ACTIVE','HOLD','SIGNED','ENTERED','EXPIRED') then
1554 l_clev_tbl(l_loop_counter).date_terminated := p_terminate_in_parameters_rec.p_termination_date;
1555 --Bug 3378196
1556 If p_terminate_in_parameters_rec.p_termination_date < lines_rec.start_date THEN
1557 l_clev_tbl(l_loop_counter).date_terminated := lines_rec.start_date;
1558 end if;
1559 END IF;
1560 /*
1561 IF l_status = 'ACTIVE' then
1562 l_clev_tbl(l_loop_counter).date_terminated := p_terminate_in_parameters_rec.p_termination_date;
1563 ELSIF l_status = 'ENTERED' then
1564 l_clev_tbl(l_loop_counter).date_terminated := p_terminate_in_parameters_rec.p_termination_date;
1565 NULL;
1566 END IF;
1567 */
1568
1569 END if;
1570
1571 l_clev_tbl(l_loop_counter).id := lines_rec.id;
1572 l_clev_tbl(l_loop_counter).object_version_number := lines_rec.object_version_number;
1573 l_clev_tbl(l_loop_counter).trn_code := p_terminate_in_parameters_rec.p_termination_reason;
1574 --
1575 -- Bug# 1405237 Avoide calling Action Assembler when the change in line status is resulting from a change in the header status
1576 --
1577 l_clev_tbl(l_loop_counter).call_action_asmblr := 'N';
1578 --
1579 --dbms_output.put_line('update_contract_line (+) ');
1580 /*
1581 OKC_CONTRACT_PUB.update_contract_line( p_api_version => 1,
1582 p_init_msg_list => OKC_API.G_FALSE,
1583 x_return_status => l_return_status,
1584 x_msg_count => x_msg_count,
1585 x_msg_data => x_msg_data,
1586 p_restricted_update => okc_api.g_true,
1587 p_clev_rec => l_clev_rec,
1588 x_clev_rec => i_clev_rec );
1589 IF (l_debug = 'Y') THEN
1590 okc_debug.set_trace_off;
1591 END IF;
1592 --dbms_output.put_line('update_contract_line (-) ');
1593 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1594 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1595 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1596 raise OKC_API.G_EXCEPTION_ERROR;
1597 END IF;
1598 */
1599 If l_lse_id = 61 Then
1600 --if the contract line being terminated is a Price Hold line, we need to delete the entry in QP
1601 --disable the entry in QP
1602 OKC_PHI_PVT.process_price_hold(
1603 p_api_version => p_api_version,
1604 p_init_msg_list => p_init_msg_list,
1605 x_return_status => l_return_status,
1606 x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data,
1608 p_chr_id => p_terminate_in_parameters_rec.p_dnz_chr_id,
1609 p_termination_date => l_clev_tbl(l_loop_counter).date_terminated,
1610 p_operation_code => 'TERMINATE');
1611 End If;
1612
1613 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1614 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1615 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1616 raise OKC_API.G_EXCEPTION_ERROR;
1617 END IF;
1618
1619 END IF; -- effectivity
1620 END IF; -- l_status
1621 END LOOP;
1622 IF (l_debug = 'Y') THEN
1623 OKC_DEBUG.set_trace_off;
1624 END IF;
1625 OKC_CONTRACT_PUB.update_contract_line( p_api_version => 1,
1626 p_init_msg_list => OKC_API.G_FALSE,
1627 x_return_status => l_return_status,
1628 x_msg_count => x_msg_count,
1629 x_msg_data => x_msg_data,
1630 p_restricted_update => OKC_API.g_true,
1631 p_clev_tbl => l_clev_tbl,
1632 x_clev_tbl => i_clev_tbl );
1633 IF (l_debug = 'Y') THEN
1634 OKC_DEBUG.set_trace_off;
1635 END IF;
1636 --dbms_output.put_line('update_contract_line (-) ');
1637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1640 RAISE OKC_API.G_EXCEPTION_ERROR;
1641 END IF;
1642
1643 IF (l_debug = 'Y') THEN
1644 okc_debug.set_trace_on;
1645 END IF;
1646 /* Commented for Bug 1925467
1647 OPEN is_line_a_service_line;
1648 FETCH is_line_a_service_line into l_dummy;
1649 CLOSE is_line_a_service_line;
1650 --
1651 -- Bug 1827571: Commented out the check of termination date against sysdate as it
1652 -- prevents issue of credit memo if the termination date is some time in future.
1653 --
1654 -- If l_dummy = 'x' and p_terminate_in_parameters_rec.p_termination_date <= sysdate and
1655 If l_dummy = 'x' And
1656 OKC_ASSENT_PUB.line_operation_allowed(p_terminate_in_parameters_rec.p_cle_id,
1657 'INVOICE') = okc_api.g_true THEN
1658
1659 --dbms_output.put_line('PRE_TERMINATE_SERVICE (+) ');
1660
1661 OKS_BILL_REC_PUB.pre_terminate_service ( p_api_version => 1,
1662 p_init_msg_list => OKC_API.G_FALSE,
1663 x_return_status => l_return_status,
1664 x_msg_count => x_msg_count,
1665 x_msg_data => x_msg_data,
1666 p_calledfrom => NULL,
1667 p_k_line_id => p_terminate_in_parameters_rec.p_cle_id,
1668 p_termination_date => p_terminate_in_parameters_rec.p_termination_date,
1669 p_termination_flag => 1,
1670 x_amount => l_amount );
1671
1672 --dbms_output.put_line('PRE_TERMINATE_SERVICE (-) ');
1673
1674 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1675 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1676 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1677 raise OKC_API.G_EXCEPTION_ERROR;
1678 END IF;
1679 END if;
1680 */
1681
1682 -- Raise the line_terminated event
1683
1684 IF p_terminate_in_parameters_rec.p_termination_date <= sysdate THEN
1685
1686 OPEN cur_header;
1687 FETCH cur_header into l_contract_number,l_contract_modifier,l_scs_code,l_cls_code,
1688 l_price_negotiated,l_kl_sts_code;
1689 CLOSE cur_header;
1690
1691 OKC_KL_TERM_ASMBLR_PVT.acn_assemble(p_api_version => 1,
1692 p_init_msg_list => OKC_API.G_FALSE,
1693 x_return_status => l_return_status,
1694 x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data,
1696 p_k_id => p_terminate_in_parameters_rec.p_dnz_chr_id,
1697 p_kl_id => p_terminate_in_parameters_rec.p_cle_id,
1698 p_kl_term_date => p_terminate_in_parameters_rec.p_termination_date,
1699 p_kl_term_reason => p_terminate_in_parameters_rec.p_termination_reason,
1700 p_k_number => l_contract_number,
1701 p_k_nbr_mod => l_contract_modifier,
1702 p_k_class => l_cls_code,
1703 p_k_subclass => l_scs_code,
1704 p_kl_status_code => l_kl_sts_code,
1705 p_estimated_amount => l_price_negotiated);
1706
1707 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1708 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1709 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1710 RAISE OKC_API.G_EXCEPTION_ERROR;
1711 END IF;
1712
1713 END IF;
1714
1715 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1716 --dbms_output.put_line(' terminate_cle (-) ');
1717
1718 EXCEPTION
1719 WHEN E_Resource_Busy THEN
1720 IF (l_debug = 'Y') THEN
1721 okc_debug.set_trace_on;
1722 END IF;
1723 x_return_status := okc_api.g_ret_sts_error;
1724 OKC_API.set_message(G_FND_APP,
1725 G_FORM_UNABLE_TO_RESERVE_REC);
1726 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1727
1728 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1729 IF (l_debug = 'Y') THEN
1730 okc_debug.set_trace_on;
1731 END IF;
1732 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1733 (l_api_name,
1734 G_PKG_NAME,
1735 'OKC_API.G_RET_STS_ERROR',
1736 x_msg_count,
1737 x_msg_data,
1738 '_PROCESS');
1739
1740 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1741 IF (l_debug = 'Y') THEN
1742 okc_debug.set_trace_on;
1743 END IF;
1744 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1745 (l_api_name,
1746 G_PKG_NAME,
1747 'OKC_API.G_RET_STS_UNEXP_ERROR',
1748 x_msg_count,
1749 x_msg_data,
1750 '_PROCESS');
1751 WHEN OTHERS THEN
1752 IF (l_debug = 'Y') THEN
1753 okc_debug.set_trace_on;
1754 END IF;
1755 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1756 (l_api_name,
1757 G_PKG_NAME,
1758 'OTHERS',
1759 x_msg_count,
1760 x_msg_data,
1761 '_PROCESS');
1762 END;
1763
1764 END;