[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_TERMINATE_PVT
Source
1 PACKAGE BODY OKL_VP_TERMINATE_PVT as
2 /*$Header: OKLRTERB.pls 115.9 2003/10/14 00:53:35 manumanu noship $*/
3
4 SUBTYPE ter_rec_type is okc_terminate_pub.terminate_in_parameters_rec;
5
6 PROCEDURE validate_chr( p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_terminate_in_parameters_rec IN ter_rec_type ) IS
12
13 CURSOR cur_k_header is
14 SELECT
15 sts_code,
16 contract_number,
17 contract_number_modifier,
18 template_yn,
19 date_terminated,
20 date_renewed,
21 application_id,
22 scs_code
23 FROM okc_k_headers_b
24 WHERE id = p_terminate_in_parameters_rec.p_contract_id;
25
26
27 -- Will not need object_version_number this termination is an adverse step. Even if there
28 -- was a change in between, contract will be terminated.
29
30 l_chrv_rec cur_k_header%rowtype;
31
32 CURSOR is_k_locked is
33 SELECT 'Y'
34 FROM okc_k_processes v
35 WHERE v.chr_id = p_terminate_in_parameters_rec.p_contract_id
36 AND v.in_process_yn='Y';
37
38 CURSOR cur_sts_code (l_code varchar2) is
39 SELECT sts.ste_code,sts.meaning
40 FROM okc_statuses_v sts
41 WHERE sts.code = l_code;
42
43 CURSOR cur_service_requests is
44 SELECT 'x'
45 FROM okx_incident_statuses_v xis,
46 okc_k_lines_b cle
47 WHERE cle.id = xis.contract_service_id
48 AND cle.dnz_chr_id = p_terminate_in_parameters_rec.p_contract_id
49 AND xis.status_code in ('OPEN'); -- Impact -- DepENDency on status of service requests
50
51 -- CURSOR cur_old_contract is
52 -- select contract_number,
53 -- contract_number_modifier
54 -- from okc_k_headers_b
55 -- where chr_id_renewed = p_terminate_in_parameters_rec.p_contract_id;
56
57 CURSOR cur_old_contract(p_chr_id number) IS
58 SELECT k.contract_number,k.contract_number_modifier
59 FROM okc_k_headers_b K,okc_operation_lines A,
60 okc_operation_instances B,okc_class_operations C
61 WHERE K.id=A.subject_chr_id
62 AND A.object_chr_id=p_chr_id AND
63 C.id=B.cop_id and C.opn_code='RENEWAL'
64 AND B.id=A.oie_id AND A.active_yn='Y' AND
65 A.subject_cle_id IS NULL AND A.object_cle_id IS NULL;
66 l_k_num okc_k_headers_v.contract_number%TYPE;
67 l_k_mod okc_k_headers_v.contract_number_modifier%TYPE;
68
69 -- Find out which statuses are valid FOR termination to continue
70
71 l_chg_request_in_process VARCHAR2(1);
72
73 l_status varchar2(30); -- Impact on status
74 l_meaning okc_statuses_v.meaning%TYPE;
75
76 l_return_status varchar2(1) := okc_api.g_ret_sts_success;
77 l_api_name CONSTANT VARCHAR2(30) := 'validate_chr';
78
79 BEGIN
80
81 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
82 ,p_init_msg_list => p_init_msg_list
83 ,p_api_type => '_PVT'
84 ,x_return_status => x_return_status
85 );
86 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
87 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
88 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
89 RAISE OKL_API.G_EXCEPTION_ERROR;
90 END IF;
91
92 x_return_status := okc_api.g_ret_sts_success;
93
94 -- okc_api.init_msg_list(p_init_msg_list);
95
96 OPEN cur_k_header;
97 FETCH cur_k_header into l_chrv_rec;
98 CLOSE cur_k_header;
99
100 IF l_chrv_rec.template_Yn = 'Y' THEN
101
102 OKC_API.set_message(p_app_name => g_app_name,
103 p_msg_name => 'OKL_K_TEMPLATE',
104 p_token1 => 'NUMBER',
105 p_token1_value => l_chrv_rec.contract_number);
106
107 x_return_status := okc_api.g_ret_sts_error;
108 RAISE OKL_API.G_EXCEPTION_ERROR;
109 END if;
110
111 OPEN is_k_locked;
112 FETCH is_k_locked into l_chg_request_in_process;
113
114 IF is_k_locked%FOUND THEN
115
116 OKC_API.set_message(p_app_name => g_app_name,
117 p_msg_name => 'OKL_K_LOCKED'
118 );
119
120 x_return_status := okc_api.g_ret_sts_error;
121 CLOSE is_k_locked;
122 RAISE OKL_API.G_EXCEPTION_ERROR;
123
124 END IF;
125
126 CLOSE is_k_locked;
127
128
129 l_status:='1';
130
131 OPEN cur_sts_code(l_chrv_rec.sts_code);
132 FETCH cur_sts_code into l_status,l_meaning;
133 CLOSE cur_sts_code;
134
135 IF l_status='1' then
136 --
137 OKC_API.set_message(p_app_name => g_app_name,
138 p_msg_name => 'OKL_INVALID_K_STATUS',
139 p_token1 => 'STATUS',
140 p_token1_value => l_chrv_rec.sts_code);
141
142 RAISE OKL_API.G_EXCEPTION_ERROR;
143
144 END IF;
145
146 IF (l_status NOT IN ('ACTIVE','HOLD','SIGNED')) OR (l_status='HOLD' and l_chrv_rec.sts_code='QA_HOLD') THEN
147
148 x_return_status := OKC_API.G_RET_STS_ERROR;
149
150 OKC_API.set_message(p_app_name => g_app_name,
151 p_msg_name => 'OKL_INVALID_K_STATUS',
152 p_token1 => 'STATUS',
153 p_token1_value => l_meaning);
154
155 RAISE OKL_API.G_EXCEPTION_ERROR;
156 ELSIF l_chrv_rec.date_terminated is not null THEN
157
158 x_return_status := OKC_API.G_RET_STS_ERROR;
159
160 OKC_API.set_message(p_app_name => g_app_name,
161 p_msg_name => 'OKL_FUTURE_TERMINATED_K',
162 p_token1 => 'NUMBER',
163 p_token1_value => l_chrv_rec.contract_number );
164
165 RAISE OKL_API.G_EXCEPTION_ERROR;
166
167
168
169 END IF;
170 -- Bug 1349841, Use NVL for Perpetual Contracts
171 IF Nvl(p_terminate_in_parameters_rec.p_orig_end_date,
172 p_terminate_in_parameters_rec.p_termination_date + 1) <
173 p_terminate_in_parameters_rec.p_termination_date then
174
175 x_return_status := OKC_API.G_RET_STS_ERROR;
176
177 OKC_API.set_message( p_app_name => g_app_name,
178 p_msg_name =>'OKL_TRMDATE_MORE_END'
179 );
180
181 RAISE OKL_API.G_EXCEPTION_ERROR;
182 END IF;
183 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
184 ,x_msg_data => x_msg_data
185 );
186 EXCEPTION
187 WHEN OKL_API.G_EXCEPTION_ERROR THEN
188
189 -- x_return_status := l1_return_status;
190
191 x_return_status := OKL_API.HANDLE_EXCEPTIONS
192 (p_api_name => l_api_name
193 ,p_pkg_name => G_PKG_NAME
194 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
195 ,x_msg_count => x_msg_count
196 ,x_msg_data => x_msg_data
197 ,p_api_type => '_PVT'
198 );
199
200
201 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
202
203 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
204 ,g_pkg_name
205 ,'OKL_API.G_RET_STS_ERROR'
206 ,x_msg_count
207 ,x_msg_data
208 ,'_PVT'
209 );
210
211 WHEN OTHERS THEN
212
213 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
214 ,g_pkg_name
215 ,'OTHERS'
216 ,x_msg_count
217 ,x_msg_data
218 ,'_PVT'
219 );
220
221 END;
222
223
224
225 FUNCTION is_k_term_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN VARCHAR2 IS
226
227 /*p_sts_code is not being used right now as not sure if the refresh from launchpad
228 will take place everytime a change happens to a contract or not. That is
229 If the status of the contract showing in launchpad would at all times be in sync
230 with database.It might not happen due to performance reasons of launchpad. So the current approach.
231 But if this sync is assured then we could use p_sts_code as well*/
232
233 l_sts_code VARCHAR2(100);
234 l_cls_code VARCHAR2(100);
235 l_template_yn VARCHAR2(10);
236 l_code VARCHAR2(100);
237 l_app_id okc_k_headers_b.application_id%TYPE;
238 l_scs_code okc_k_headers_b.scs_code%TYPE;
239 l_k VARCHAR2(255);
240 l_mod okc_k_headers_b.contract_number_modifier%TYPE DEFAULT OKC_API.G_MISS_CHAR;
241 l_return_value VARCHAR2(1) := 'Y';
242 L1_RETURN_STATUS varchar2(3);
243
244 CURSOR c_chr IS
245 SELECT sts_code, template_yn, application_id, scs_code ,contract_number,
246 contract_number_modifier
247 FROM okc_k_headers_b
248 WHERE id = p_chr_id;
249
250 CURSOR c_sts(p_code IN VARCHAR2) IS
251 SELECT ste_code
252 FROM okc_statuses_b
253 WHERE code = p_code;
254
255
256 BEGIN
257
258 OPEN c_chr;
259 FETCH c_chr INTO l_code, l_template_yn, l_app_id, l_scs_code,l_k,l_mod;
260 CLOSE c_chr;
261
262 IF l_template_yn = 'Y' then
263 OKC_API.set_message(p_app_name => g_app_name,
264 p_msg_name => 'OKL_K_TEMPLATE',
265 p_token1 => 'NUMBER',
266 p_token1_value => l_k);
267
268 l_return_value :='N';
269 return(l_return_value);
270
271 END IF;
272
273 -- Commented by Murthy on 26-Apr-02
274 --update allowed checking is not required here for extend agreement
275 /*IF (OKL_OKC_MIGRATION_A_PVT.update_allowed(p_chr_id) <> 'Y') THEN
276
277 l1_return_status :=OKL_API.G_RET_STS_ERROR;
278
279 OKC_API.set_message(p_app_name => g_app_name,
280 p_msg_name => 'OKL_VP_UPDATE_NOT_ALLOWED'
281 );
282
283 RAISE OKL_API.G_EXCEPTION_ERROR;
284
285 END IF;
286 */
287
288 IF (OKL_VENDOR_PROGRAM_PUB.Is_Process_Active(p_chr_id) <> 'N') THEN
289
290 OKC_API.set_message(p_app_name => g_app_name,
291 p_msg_name => 'OKL_VP_APPROVAL_PROCESS_ACTV'
292 );
293
294 l_return_value := 'N';
295 RETURN(l_return_value);
296
297 END IF;
298
299 /* Fix for Bug 3104000 */
300 /*
301
302 IF okc_util.Get_All_K_Access_Level(p_application_id => l_app_id,
303 p_chr_id => p_chr_id,
304 p_scs_code => l_scs_code) <> 'U' Then
305 OKC_API.set_message(p_app_name => g_app_name,
306 p_msg_name => 'OKL_NO_UPDATE',
307 p_token1 => 'CHR',
308 p_token1_value => l_k);
309 l_return_value :='N';
310 return(l_return_value);
311 END IF;
312 */
313
314 RETURN(l_return_value);
315 END is_k_term_allowed;
316
317 PROCEDURE terminate_contract(p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 p_ter_header_rec IN terminate_header_rec_type)
323
324 IS
325
326 l1_ter_header_rec ter_rec_type;
327
328 l_terminate_allowed varchar2(1);
329
330 l_contract_id number;
331 l_contract_no varchar2(120);
332 l_scs_code varchar2(30);
333 l_sts_code varchar2(30);
334 l_end_date date;
335
336
337 l_return_value VARCHAR2(1) := 'N';
338 l_msg_count NUMBER;
339 l_msg_data VARCHAR2(2000);
340 l_api_version NUMBER := 1.0;
341
342 l_api_name CONSTANT VARCHAR2(30) := 'terminate_contract';
343
344 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
345
346 l1_return_status VARCHAR2(3);
347
348
349 CURSOR cur_k_header(p_id NUMBER) IS
350 SELECT contract_number,scs_code,sts_code,end_date from okc_k_headers_v
351 WHERE id=p_id;
352
353
354 -- begin of block
355
356 BEGIN
357
358 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
359 ,p_init_msg_list => p_init_msg_list
360 ,p_api_type => '_PVT'
361 ,x_return_status => x_return_status
362 );
363 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
364 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
365 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
366 RAISE OKL_API.G_EXCEPTION_ERROR;
367 END IF;
368
369
370
371 x_return_status := OKL_API.G_RET_STS_SUCCESS;
372
373 l_contract_id :=p_ter_header_rec.p_id;
374
375 -- Added by Ajay 25-MAR-2002
376 l1_ter_header_rec.p_contract_id := l_contract_id;
377
378 l_terminate_allowed :=Okl_Vp_Terminate_Pvt.is_k_term_allowed(l_contract_id,l_sts_code);
379
380
381 IF (l_terminate_allowed = 'Y') THEN
382
383 -- changes made on 6th Nov to do the validations okc is doing inside extend_chr
384 -- doing this to customize the messages ie to write okl messages
385
386 Okl_Vp_Terminate_Pvt.validate_chr(p_api_version => l_api_version,
387 p_init_msg_list => OKC_API.G_FALSE,
388 x_return_status => l_return_status,
389 x_msg_count => l_msg_count,
390 x_msg_data => l_msg_data,
391 p_terminate_in_parameters_rec => l1_ter_header_rec);
392
393
394 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
395 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
396 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
397 RAISE OKC_API.G_EXCEPTION_ERROR;
398 END IF;
399 --59 lines
400 -- validate the passed parameters ie end date.
401
402 IF ((p_ter_header_rec.p_terminate_date = OKL_API.G_MISS_DATE) OR (p_ter_header_rec.p_terminate_date IS NULL)) THEN
403
404 l1_return_status :=OKL_API.G_RET_STS_ERROR;
405 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_TERM_DT_REQD');
406 RAISE OKL_API.G_EXCEPTION_ERROR;
407 END IF;
408
409 OPEN cur_k_header(l_contract_id);
410 FETCH cur_k_header INTO l_contract_no,l_scs_code,l_sts_code,l_end_date;
411
412 IF (cur_k_header%FOUND) THEN
413
414
415 IF l_end_date <> p_ter_header_rec.p_current_end_date THEN
416
417 l1_return_status :=OKL_API.G_RET_STS_ERROR;
418 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_INV_CUR_END_DATE');
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421
422
423
424 IF (p_ter_header_rec.p_terminate_date < trunc(sysdate)) THEN
425
426
427 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
428 p_msg_name => 'OKL_INV_TERM_DATE'
429 );
430
431
432 l1_return_status :=OKL_API.G_RET_STS_ERROR;
433
434 RAISE OKL_API.G_EXCEPTION_ERROR;
435
436 END IF;
437
438
439
440 l1_ter_header_rec.p_contract_id :=l_contract_id;
441 l1_ter_header_rec.p_contract_number :=l_contract_no;
442 l1_ter_header_rec. p_orig_end_date :=l_end_date;
443 l1_ter_header_rec.p_termination_date :=p_ter_header_rec.p_terminate_date;
444 l1_ter_header_rec. p_termination_reason:=p_ter_header_rec.p_term_reason;
445
446 CLOSE cur_k_header;
447
448 ELSE
449 l1_return_status :=OKL_API.G_RET_STS_ERROR;
450
451 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
452 p_msg_name => 'OKL_VP_AGREEMENT_NOT_FOUND');
453
454 CLOSE cur_k_header;
455
456 RAISE OKL_API.G_EXCEPTION_ERROR;
457
458 END IF;
459 --59 lines end
460 okc_terminate_pub.terminate_chr(p_api_version => l_api_version,
461 x_return_status => l_return_status,
462 x_msg_data => l_msg_data,
463 x_msg_count => l_msg_count,
464 p_init_msg_list => OKL_API.G_TRUE,
465 p_terminate_in_parameters_rec => l1_ter_header_rec);
466
467 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
468 NULL;
469 ELSE
470 l1_return_status :=l_return_status;
471
472 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
474 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
475 RAISE OKL_API.G_EXCEPTION_ERROR;
476 END IF;
477 END IF;
478
479 ELSE
480 l1_return_status :=OKL_API.G_RET_STS_ERROR;
481 RAISE OKL_API.G_EXCEPTION_ERROR;
482 END IF;
483
484 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
485 ,x_msg_data => x_msg_data
486 );
487
488
489
490 EXCEPTION
491
492 WHEN OKL_API.G_EXCEPTION_ERROR THEN
493
494 x_return_status := OKL_API.HANDLE_EXCEPTIONS(p_api_name => l_api_name
495 ,p_pkg_name => G_PKG_NAME
496 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
497 ,x_msg_count => x_msg_count
498 ,x_msg_data => x_msg_data
499 ,p_api_type => '_PVT'
500 );
501
502
503 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
504
505 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
506 ,g_pkg_name
507 ,'OKL_API.G_RET_STS_ERROR'
508 ,x_msg_count
509 ,x_msg_data
510 ,'_PVT'
511 );
512
513 WHEN OTHERS THEN
514
515 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
516 ,g_pkg_name
517 ,'OTHERS'
518 ,x_msg_count
519 ,x_msg_data
520 ,'_PVT'
521 );
522
523 -- end of procedure create_program
524 END;
525
526 END;
527