[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_EXTEND_PVT
Source
1 PACKAGE BODY okl_vp_extend_pvt AS
2 /* $Header: OKLREXTB.pls 115.10 2003/10/15 22:45:26 manumanu noship $ */
3 SUBTYPE extn_rec_type IS okc_extend_pub.extend_in_parameters_rec;
4
5 PROCEDURE validate_chr( p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 p_extend_in_parameters_rec IN extn_rec_type
11 ) 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 end_date
22 FROM okc_k_headers_b
23 WHERE id = p_extend_in_parameters_rec.p_contract_id;
24
25 CURSOR is_k_locked is
26 SELECT 'Y'
27 FROM okc_k_processes v
28 WHERE v.chr_id = p_extend_in_parameters_rec.p_contract_id
29 AND v.in_process_yn='Y';
30
31 CURSOR cur_status(p_sts_code varchar2) is
32 SELECT ste_code
33 FROM okc_statuses_b
34 WHERE code = p_sts_code;
35
36 CURSOR cur_mean(p_sts_code varchar2) is
37 SELECT meaning
38 FROM okc_statuses_v
39 WHERE code = p_sts_code;
40
41 l_chg_request_in_process VARCHAR2(1);
42 l_status VARCHAR2(30);
43 l_status_meaning okc_statuses_v.meaning%TYPE;
44 l_return_status VARCHAR2(1) := OKC_API.g_ret_sts_success;
45 l_chr_rec cur_k_header%rowtype;
46 l_api_name CONSTANT VARCHAR2(30) := 'validate_chr';
47
48
49 BEGIN
50
51 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
52 ,p_init_msg_list => p_init_msg_list
53 ,p_api_type => '_PVT'
54 ,x_return_status => x_return_status
55 );
56 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
57 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
58 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
59 RAISE OKL_API.G_EXCEPTION_ERROR;
60 END IF;
61
62 x_return_status := okc_api.g_ret_sts_success;
63
64 -- OKC_API.init_msg_list(p_init_msg_list);
65
66 OPEN cur_k_header;
67 FETCH cur_k_header into l_chr_rec;
68 CLOSE cur_k_header;
69
70 /* Templates can not be extended */
71 IF l_chr_rec.template_yn = 'Y' THEN
72
73 OKC_API.set_message( p_app_name => g_app_name,
74 p_msg_name => 'OKL_K_TEMPLATE',
75 p_token1 => 'NUMBER',
76 p_token1_value => l_chr_rec.contract_number );
77
78 x_return_status := okc_api.g_ret_sts_error;
79 RAISE OKL_API.G_EXCEPTION_ERROR;
80 END IF;
81
82 OPEN is_k_locked;
83 FETCH is_k_locked into l_chg_request_in_process;
84
85 IF is_k_locked%FOUND THEN
86 OKC_API.set_message(p_app_name => g_app_name,
87 p_msg_name => 'OKL_K_LOCKED'
88 );
89 x_return_status := okc_api.g_ret_sts_error;
90 CLOSE is_k_locked;
91 RAISE OKL_API.G_EXCEPTION_ERROR;
92 END IF;
93
94 CLOSE is_k_locked;
95
96 l_status:='1';
97 OPEN cur_status(l_chr_rec.sts_code);
98 FETCH cur_status into l_status;
99 CLOSE cur_status;
100 IF l_status='1' THEN
101 OKC_API.set_message(p_app_name => g_app_name,
102 p_msg_name => 'OKL_INVALID_K_STATUS',
103 p_token1 => 'STATUS',
104 p_token1_value => l_chr_rec.sts_code);
105 RAISE OKL_API.G_EXCEPTION_ERROR;
106 END IF;
107
108 OPEN cur_mean(l_status);
109 FETCH cur_mean into l_status_meaning;
110 CLOSE cur_mean;
111
112 IF l_status NOT IN ('ACTIVE','EXPIRED','SIGNED') THEN
113
114 x_return_status := OKC_API.G_RET_STS_ERROR;
115 OKC_API.set_message(p_app_name => g_app_name,
116 p_msg_name => 'OKL_INVALID_K_STATUS',
117 p_token1 => 'STATUS',
118 p_token1_value => l_chr_rec.sts_code);
119 RAISE OKL_API.G_EXCEPTION_ERROR;
120 ELSIF l_chr_rec.date_terminated IS NOT NULL THEN
121
122 x_return_status := OKC_API.G_RET_STS_ERROR;
123
124 OKC_API.set_message(p_app_name => g_app_name,
125 p_msg_name => 'OKL_FUTURE_TERMINATED_K',
126 p_token1 => 'NUMBER',
127 p_token1_value => l_chr_rec.contract_number );
128
129 RAISE OKL_API.G_EXCEPTION_ERROR;
130
131 END IF;
132 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
133 ,x_msg_data => x_msg_data
134 );
135
136
137 EXCEPTION
138 WHEN OKL_API.G_EXCEPTION_ERROR THEN
139
140 -- x_return_status := l1_return_status;
141
142 x_return_status := OKL_API.HANDLE_EXCEPTIONS
143 (p_api_name => l_api_name
144 ,p_pkg_name => G_PKG_NAME
145 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
146 ,x_msg_count => x_msg_count
147 ,x_msg_data => x_msg_data
148 ,p_api_type => '_PVT'
149 );
150
151
152 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
153
154 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
155 ,g_pkg_name
156 ,'OKL_API.G_RET_STS_ERROR'
157 ,x_msg_count
158 ,x_msg_data
159 ,'_PVT'
160 );
161
162 WHEN OTHERS THEN
163
164 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
165 ,g_pkg_name
166 ,'OTHERS'
167 ,x_msg_count
168 ,x_msg_data
169 ,'_PVT'
170 );
171 END;
172
173
174 FUNCTION is_k_extend_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN VARCHAR2 IS
175
176
177 l_sts_code VARCHAR2(100);
178 l_cls_code VARCHAR2(100);
179 l_template_yn VARCHAR2(10);
180 l_code VARCHAR2(100);
181 l_end_date okc_k_headers_b.end_date%TYPE;
182 l_app_id okc_k_headers_b.application_id%TYPE;
183 l_scs_code okc_k_headers_b.scs_code%TYPE;
184 l_k VARCHAR2(255);
185 l_mod okc_k_headers_b.contract_number_modifier%TYPE DEFAULT OKC_API.G_MISS_CHAR;
186 l_return_value VARCHAR2(1) := 'Y';
187
188
189 CURSOR c_chr IS
190 SELECT sts_code,template_yn,end_date,application_id,scs_code ,contract_number,
191 contract_number_modifier
192 FROM okc_k_headers_b
193 WHERE id = p_chr_id;
194
195 CURSOR c_sts(p_code IN VARCHAR2) IS
196 SELECT ste_code
197 FROM okc_statuses_b
198 WHERE code = p_code;
199
200 BEGIN
201
202 OPEN c_chr;
203 FETCH c_chr INTO l_code,l_template_yn,l_end_date,l_app_id,l_scs_code,l_k,l_mod;
204 CLOSE c_chr;
205
206 IF l_template_yn = 'Y' then
207 OKC_API.set_message(p_app_name => g_app_name,
208 p_msg_name => 'OKL_K_TEMPLATE',
209 p_token1 => 'NUMBER',
210 p_token1_value => l_k);
211 l_return_value := 'N';
212
213 RETURN(l_return_value);
214 END IF;
215
216 -- A perpetual cannot be extended further
217 IF l_end_date Is Null then
218 OKC_API.set_message(p_app_name => g_app_name,
219 p_msg_name => 'OKL_NO_PERPETUAL'
220 );
221 l_return_value := 'N';
222 RETURN(l_return_value);
223 END IF;
224
225 -- If there is Update access, do not allow extend
226 -- *********** Fix for bug 3104000. ************
227 /*****************
228 IF OKC_UTIL.get_all_k_access_level(p_application_id => l_app_id,
229 p_chr_id => p_chr_id,
230 p_scs_code => l_scs_code) <> 'U' THEN
231 OKC_API.set_message(p_app_name => g_app_name,
232 p_msg_name => 'OKL_NO_UPDATE',
233 p_token1 => 'CHR',
234 p_token1_value => l_k);
235
236 l_return_value := 'N';
237 RETURN(l_return_value);
238
239 END IF;
240 *****************/
241
242 -- Commented by Murthy on 26-Apr-02
243 --update allowed checking is not required here for extend agreement
244 /* IF (OKL_OKC_MIGRATION_A_PVT.update_allowed(p_chr_id) <> 'Y') THEN
245
246 -- l1_return_status :=OKL_API.G_RET_STS_ERROR;
247
248 OKC_API.set_message(p_app_name => g_app_name,
249 p_msg_name => 'OKL_VP_UPDATE_NOT_ALLOWED'
250 );
251
252 RAISE OKL_API.G_EXCEPTION_ERROR;
253
254 END IF;
255 */
256
257 IF (OKL_VENDOR_PROGRAM_PUB.Is_Process_Active(p_chr_id) <> 'N') THEN
258 OKC_API.set_message(p_app_name => g_app_name,
259 p_msg_name => 'OKL_VP_APPROVAL_PROCESS_ACTV'
260 );
261 l_return_value := 'N';
262 RETURN(l_return_value);
263 END IF;
264
265 RETURN(l_return_value);
266
267 END is_k_extend_allowed;
268
269 PROCEDURE extend_contract(p_api_version IN NUMBER,
270 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
271 x_return_status OUT NOCOPY VARCHAR2,
272 x_msg_count OUT NOCOPY NUMBER,
273 x_msg_data OUT NOCOPY VARCHAR2,
274 p_ext_header_rec IN extension_header_rec_type)
275 IS
276 l_ext_header_rec extn_rec_type;
277
278
279 l_extend_allowed varchar2(1);
280
281
282 l_contract_id NUMBER;
283 l_contract_no VARCHAR2(120);
284 l_scs_code VARCHAR2(30);
285 l_sts_code VARCHAR2(30);
286 l_start_date DATE;
287 l_end_date DATE;
288
289 l_return_value VARCHAR2(1) := 'N';
290 l_msg_count NUMBER;
291 l_msg_data VARCHAR2(2000);
292 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
293 l_api_version NUMBER := 1.0;
294
295 l_api_name CONSTANT VARCHAR2(30) := 'extend_contract';
296
297 l1_return_status VARCHAR2(3);
298
299 CURSOR cur_k_header(p_id NUMBER) IS
300 SELECT contract_number,scs_code,sts_code,start_date,end_date FROM okc_k_headers_v
301 WHERE id=p_id;
302
303 -- begin of block
304
305 BEGIN
306
307 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name
308 ,p_init_msg_list => p_init_msg_list
309 ,p_api_type => '_PVT'
310 ,x_return_status => x_return_status
311 );
312 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
314 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
315 RAISE OKL_API.G_EXCEPTION_ERROR;
316 END IF;
317
318 x_return_status := OKL_API.G_RET_STS_SUCCESS;
319
320 l_contract_id := p_ext_header_rec.p_id;
321
322 -- Added by Ajay 25-MAR-2002
323 l_ext_header_rec.p_contract_id := l_contract_id;
324
325 l_extend_allowed :=okl_vp_extend_pvt.is_k_extend_allowed(l_contract_id,l_sts_code);
326
327 IF (l_extend_allowed ='Y') THEN
328
329 -- changes made on 6th Nov to do the validations okc is doing inside extend_chr
330 -- doing this to customize the messages ie to write okl messages
331
332 okl_vp_extend_pvt.validate_chr(p_api_version => l_api_version,
333 p_init_msg_list => OKC_API.G_FALSE,
334 x_return_status => l_return_status,
335 x_msg_count => l_msg_count,
336 x_msg_data => l_msg_data,
337 p_extend_in_parameters_rec => l_ext_header_rec);
338
339
340 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
341 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
342 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
343 RAISE OKC_API.G_EXCEPTION_ERROR;
344 END IF;
345 --50 lines
346 -- Validate the passed parameters
347 -- new end date should be given
348
349 IF ((p_ext_header_rec.p_new_end_date = OKL_API.G_MISS_DATE) OR (p_ext_header_rec.p_new_end_date IS NULL)) THEN
350
351 l1_return_status :=OKL_API.G_RET_STS_ERROR;
352 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_EXT_DT_REQD');
353 RAISE OKL_API.G_EXCEPTION_ERROR;
354 END IF;
355
356 OPEN cur_k_header(l_contract_id);
357 FETCH cur_k_header INTO l_contract_no,l_scs_code,l_sts_code,l_start_date,l_end_date;
358
359 IF (cur_k_header%FOUND) THEN
360
361 -- added on 15th Oct
362 -- this if condition checks whether end date date displayed has changed by someone
363 -- when the user tries to extend the contract and throws the error if it's changed
364
365 IF l_end_date <> p_ext_header_rec.p_current_end_date THEN
366 l1_return_status :=OKL_API.G_RET_STS_ERROR;
367 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_INV_CUR_END_DATE');
368 RAISE OKL_API.G_EXCEPTION_ERROR;
369 END IF;
370
371 IF l_end_date > p_ext_header_rec.p_new_end_date THEN
372 l1_return_status :=OKL_API.G_RET_STS_ERROR;
373 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name =>'OKL_INV_EXT_DATE');
374 RAISE OKL_API.G_EXCEPTION_ERROR;
375 END IF;
376
377 l_ext_header_rec.p_contract_id :=l_contract_id;
378 l_ext_header_rec.p_contract_number :=l_contract_no;
379 l_ext_header_rec.p_orig_start_date :=l_start_date;
380 l_ext_header_rec. p_orig_end_date :=l_end_date;
381 l_ext_header_rec. p_end_date :=p_ext_header_rec.p_new_end_date;
382
383 CLOSE cur_k_header;
384
385 ELSE
386
387 l1_return_status :=OKL_API.G_RET_STS_ERROR;
388 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
389 p_msg_name => 'OKL_VP_AGREEMENT_NOT_FOUND'
390 );
391 CLOSE cur_k_header;
392
393 RAISE OKL_API.G_EXCEPTION_ERROR;
394
395 END IF;
396 --end 50 lines
397
398 okc_extend_pub.extend_chr(p_api_version => l_api_version,
399 p_init_msg_list => OKL_API.G_TRUE,
400 x_return_status => l_return_status,
401 x_msg_count => l_msg_count,
402 x_msg_data => l_msg_data,
403 p_extend_in_parameters_rec => l_ext_header_rec);
404
405 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
406 NULL;
407 ELSE
408 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
409 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
410 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
411 RAISE OKL_API.G_EXCEPTION_ERROR;
412 END IF;
413 END IF;
414
415 ELSE
416 l1_return_status :=OKL_API.G_RET_STS_ERROR;
417 RAISE OKL_API.G_EXCEPTION_ERROR;
418 END IF;
419
420 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
421 ,x_msg_data => x_msg_data
422 );
423
424 EXCEPTION
425
426 WHEN OKL_API.G_EXCEPTION_ERROR THEN
427
428 x_return_status := OKL_API.HANDLE_EXCEPTIONS
429 (p_api_name => l_api_name
430 ,p_pkg_name => G_PKG_NAME
431 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
432 ,x_msg_count => x_msg_count
433 ,x_msg_data => x_msg_data
434 ,p_api_type => '_PVT'
435 );
436
437
438 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
439
440 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
441 ,g_pkg_name
442 ,'OKL_API.G_RET_STS_ERROR'
443 ,x_msg_count
444 ,x_msg_data
445 ,'_PVT'
446 );
447
448 WHEN OTHERS THEN
449
450 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
451 ,g_pkg_name
452 ,'OTHERS'
453 ,x_msg_count
454 ,x_msg_data
455 ,'_PVT'
456 );
457
458
459 END;
460
461 END;