[Home] [Help]
PACKAGE BODY: APPS.OKL_MAINTAIN_CONTRACT_PVT
Source
1 package body okl_maintain_contract_pvt AS
2 /* $Header: OKLRKHRB.pls 120.6.12010000.2 2008/12/22 04:46:48 rpillay ship $ */
3
4 --------------------------------------------------------------------------------
5 --GLOBAL Message constants added for okl contract cancellation checks
6 --------------------------------------------------------------------------------
7 G_CANC_CNTR_INV_STATUS CONSTANT VARCHAR2(200) := 'OKL_LLA_CANC_CNTR_INV_STATUS';
8 G_CONTRACT_NUMBER_TOKEN CONSTANT VARCHAR2(200) := 'CONTRACT_NUMBER';
9 G_STATUS_CODE_TOKEN CONSTANT VARCHAR2(200) := 'STATUS';
10 G_CANC_CNTR_PYMT_EXIST CONSTANT VARCHAR2(200) := 'OKL_LLA_CANC_CNTR_PYMT_EXIST';
11 G_TXN_TYPE CONSTANT VARCHAR2(200) := 'TXN_TYPE';
12 G_CANC_CNTR_RCPT_EXIST CONSTANT VARCHAR2(200) := 'OKL_LLA_CANC_CNTR_RCPT_EXIST';
13
14 subtype tapv_rec_type is okl_tap_pvt.tapv_rec_type;
15 --------------------------------------------------------------------------------
16 --start of comments
17 -- Description : This api takes the contract id as input and
18 -- returns the Validation status
19 -- IN Parameters : p_contract_id - contract id
20 --End of comments
21 --------------------------------------------------------------------------------
22 Procedure Validate_Cancel_Contract
23 (p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_contract_id IN NUMBER) is
29
30 CURSOR contract_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
31 SELECT contract_number
32 FROM okc_k_headers_v
33 WHERE id = p_chr_id;
34
35 Cursor bankrupt_csr(p_contract_id NUMBER) IS
36 SELECT DECODE(disposition_code, 'NEGOTIATION', 'Y', 'GRANTED', 'Y', NULL, 'Y', 'N') bankruptcy_status
37 FROM iex_bankruptcies ban
38 WHERE EXISTS (SELECT 1 FROM okc_k_party_roles_b rle
39 WHERE rle.dnz_chr_id = p_contract_id
40 AND rle.rle_code = 'LESSEE'
41 AND TO_NUMBER(rle.object1_id1) = ban.party_id);
42
43 Cursor contract_status_csr(p_contract_id NUMBER) IS
44 SELECT sts_code
45 FROM okc_k_headers_b chrb
46 WHERE chrb.id = p_contract_id
47 AND chrb.scs_code = 'LEASE'
48 AND chrb.sts_code in ('BOOKED', 'EVERGREEN', 'EXPIRED', 'TERMINATED', 'REVERSED', 'ABANDONED');
49
50 Cursor funding_disb_txn_csr(p_contract_id NUMBER) IS
51 SELECT funding_type_code
52 FROM okl_trx_ap_invoices_b
53 WHERE khr_id = p_contract_id
54 -- AND ((funding_type_code = 'MANUAL_DISB') OR
55 -- Added for bug #5944260/5981076(Forward R12 cklee)
56 AND (trx_status_code in ('SUBMITTED', 'APPROVED', 'PROCESSED'));
57
58
59
60 Cursor funding_disb_txn_type_csr(p_code VARCHAR2) IS
61 SELECT meaning
62 FROM fnd_lookups
63 WHERE lookup_type = 'OKL_FUNDING_TYPE'
64 AND lookup_code = p_code;
65
66 Cursor Billing_invoice_status_csr(p_contract_id NUMBER) IS
67 SELECT '!'
68 FROM okl_trx_ar_invoices_b
69 WHERE khr_id = p_contract_id
70 AND trx_status_code <> 'ERROR';
71
72 Cursor Advance_receipt_csr(p_contract_id NUMBER) IS
73 SELECT '!'
74 FROM okl_trx_csh_receipt_v otcr, okl_txl_rcpt_apps_v otra
75 WHERE otcr.id = otra.rct_id_details
76 AND otcr.receipt_type = 'ADV'
77 AND otra.khr_id = p_contract_id;
78
79 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
80 l_api_name CONSTANT varchar2(30) := 'VALIDATE_CANCEL_CONTRACTS';
81 l_api_version CONSTANT NUMBER := 1.0;
82 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
83 l_sts_code OKC_K_HEADERS_V.sts_code%TYPE := NULL;
84 l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE := NULL;
85 l_txn_type FND_LOOKUPS.meaning%TYPE;
86 l_bankruptcy_status VARCHAR2(1);
87 l_status_code OKC_K_HEADERS_B.sts_code%TYPE;
88 l_billing_invoice_status VARCHAR2(1) := '?';
89 l_advance_receipt_status VARCHAR2(1) := '?';
90
91 begin
92 x_return_status := OKL_API.G_RET_STS_SUCCESS;
93 -- Call start_activity to create savepoint, check compatibility
94 -- and initialize message list
95 x_return_status := OKL_API.START_ACTIVITY (
96 l_api_name
97 ,p_init_msg_list
98 ,'_PVT'
99 ,x_return_status);
100 -- Check if activity started successfully
101 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
102 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
103 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
104 RAISE OKL_API.G_EXCEPTION_ERROR;
105 END IF;
106
107 -- Get Contract Number from Original Contract
108 OPEN contract_csr(p_contract_id);
109 FETCH contract_csr INTO l_contract_number;
110
111 IF contract_csr%NOTFOUND THEN
112 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
113 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
114 p_msg_name => G_LLA_CHR_ID);
115 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
116 END IF;
117
118 CLOSE contract_csr;
119
120 OPEN bankrupt_csr(p_contract_id);
121 FETCH bankrupt_csr INTO l_bankruptcy_status;
122 CLOSE bankrupt_csr;
123
124 IF (nvl(l_bankruptcy_status,'N') = 'Y') THEN
125 l_sts_code := 'BANKRUPTCY_HOLD';
126 OKC_API.set_message(
127 p_app_name => G_APP_NAME,
128 p_msg_name => G_CANC_CNTR_INV_STATUS,
129 p_token1 => G_CONTRACT_NUMBER_TOKEN,
130 p_token1_value => l_contract_number,
131 p_token2 => G_STATUS_CODE_TOKEN,
132 p_token2_value => l_sts_code);
133 x_return_status := OKL_API.G_RET_STS_ERROR;
134 RAISE OKL_API.G_EXCEPTION_ERROR;
135 END IF;
136
137 OPEN contract_status_csr(p_contract_id);
138 FETCH contract_status_csr INTO l_sts_code;
139 IF (contract_status_csr%NOTFOUND) THEN
140 null;
141 END IF;
142
143 CLOSE contract_status_csr;
144
145 IF (l_sts_code IS NOT NULL) THEN
146 OKC_API.set_message(
147 p_app_name => G_APP_NAME,
148 p_msg_name => G_CANC_CNTR_INV_STATUS,
149 p_token1 => G_CONTRACT_NUMBER_TOKEN,
150 p_token1_value => l_contract_number,
151 p_token2 => G_STATUS_CODE_TOKEN,
152 p_token2_value => l_sts_code);
153 x_return_status := OKL_API.G_RET_STS_ERROR;
154 RAISE OKL_API.G_EXCEPTION_ERROR;
155 END IF;
156
157 OPEN funding_disb_txn_csr(p_contract_id);
158 FETCH funding_disb_txn_csr INTO l_funding_type_code;
159 IF (funding_disb_txn_csr%NOTFOUND) THEN
160 null;
161 END IF;
162 CLOSE funding_disb_txn_csr;
163
164 IF (l_funding_type_code is NOT NULL) THEN
165 OPEN funding_disb_txn_type_csr(l_funding_type_code);
166 FETCH funding_disb_txn_type_csr INTO l_txn_type;
167 CLOSE funding_disb_txn_type_csr;
168
169 OKC_API.set_message(
170 p_app_name => G_APP_NAME,
171 p_msg_name => G_CANC_CNTR_PYMT_EXIST,
172 p_token1 => G_TXN_TYPE,
173 p_token1_value => l_txn_type,
174 p_token2 => G_CONTRACT_NUMBER_TOKEN,
175 p_token2_value => l_contract_number);
176
177 x_return_status := OKC_API.G_RET_STS_ERROR;
178 RAISE OKL_API.G_EXCEPTION_ERROR;
179 END IF;
180
181 OPEN Billing_invoice_status_csr(p_contract_id);
182 FETCH Billing_invoice_status_csr INTO l_Billing_invoice_status;
183 IF (Billing_invoice_status_csr%NOTFOUND) THEN
184 null;
185 END IF;
186 CLOSE Billing_invoice_status_csr;
187
188 IF (l_Billing_invoice_status = '!') THEN
189 OKC_API.set_message(
190 p_app_name => G_APP_NAME,
191 p_msg_name => G_CANC_CNTR_RCPT_EXIST,
192 p_token1 => G_CONTRACT_NUMBER_TOKEN,
193 p_token1_value => l_contract_number);
194
195 x_return_status := OKC_API.G_RET_STS_ERROR;
196 RAISE OKL_API.G_EXCEPTION_ERROR;
197 END IF;
198
199 OPEN Advance_receipt_csr(p_contract_id);
200 FETCH Advance_receipt_csr INTO l_advance_receipt_status;
201 IF (Advance_receipt_csr%NOTFOUND) THEN
202 null;
203 END IF;
204 CLOSE Advance_receipt_csr;
205
206 IF (l_advance_receipt_status = '!') THEN
207 OKC_API.set_message(
208 p_app_name => G_APP_NAME,
209 p_msg_name => G_CANC_CNTR_RCPT_EXIST,
210 p_token1 => G_CONTRACT_NUMBER_TOKEN,
211 p_token1_value => l_contract_number);
212
213 x_return_status := OKC_API.G_RET_STS_ERROR;
214 RAISE OKL_API.G_EXCEPTION_ERROR;
215 END IF;
216 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
217 EXCEPTION
218 WHEN OKL_API.G_EXCEPTION_ERROR THEN
219 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
220 l_api_name,
221 G_PKG_NAME,
222 'OKL_API.G_RET_STS_ERROR',
223 x_msg_count,
224 x_msg_data,
225 '_PVT');
226 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
227 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
228 l_api_name,
229 G_PKG_NAME,
230 'OKL_API.G_RET_STS_UNEXP_ERROR',
231 x_msg_count,
232 x_msg_data,
233 '_PVT');
234 WHEN OTHERS THEN
235 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
236 l_api_name,
237 G_PKG_NAME,
238 'OTHERS',
239 x_msg_count,
240 x_msg_data,
241 '_PVT');
242 end validate_cancel_contract;
243
244 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
245 PROCEDURE cancel_funding_request(p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_funding_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
251
252 l_tapv_rec tapv_rec_type;
253 x_tapv_rec tapv_rec_type;
254 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_FUNDING_REQUEST';
255
256 -- Fix BPD Bug. these columns will be overridden by tapi
257 CURSOR c_tap (p_funding_id OKL_TRX_AP_INVOICES_B.ID%TYPE)
258 IS
259 SELECT h.VENDOR_INVOICE_NUMBER,
260 h.PAY_GROUP_LOOKUP_CODE,
261 h.NETTABLE_YN,
262 h.FUNDING_TYPE_CODE,
263 h.INVOICE_TYPE
264 FROM OKL_TRX_AP_INVOICES_B h
265 WHERE h.id = p_funding_id;
266
267 r_tap c_tap%ROWTYPE;
268
269 BEGIN
270
271 x_return_status := OKL_API.G_RET_STS_SUCCESS;
272 -- Call start_activity to create savepoint, check compatibility
273 -- and initialize message list
274 x_return_status := OKL_API.START_ACTIVITY (
275 l_api_name,
276 p_init_msg_list,
277 '_PVT',
278 x_return_status);
279 -- Check if activity started successfully
280 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
281 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
282 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
283 RAISE OKL_API.G_EXCEPTION_ERROR;
284 END IF;
285
286 -- Get the internal invoice Details
287 OPEN c_tap(p_funding_id);
288 FETCH c_tap INTO r_tap;
289 CLOSE c_tap;
290
291 l_tapv_rec.id := p_funding_id;
292 l_tapv_rec.trx_status_code := 'CANCELED';
293 l_tapv_rec.vendor_invoice_number := r_tap.vendor_invoice_number;
294 l_tapv_rec.pay_group_lookup_code := r_tap.pay_group_lookup_code;
295 l_tapv_rec.nettable_yn := r_tap.nettable_yn;
296 l_tapv_rec.invoice_type := r_tap.invoice_type;
297
298 -- update funding status
299 OKL_TRX_AP_INVOICES_PUB.UPDATE_TRX_AP_INVOICES(
300 p_api_version => p_api_version,
301 p_init_msg_list => p_init_msg_list,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 p_tapv_rec => l_tapv_rec,
306 x_tapv_rec => x_tapv_rec);
307
308 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
309 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
310 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
311 RAISE OKL_API.G_EXCEPTION_ERROR;
312 END IF;
313
314
315 OKL_API.END_ACTIVITY (x_msg_count,
316 x_msg_data );
317
318
319
320 EXCEPTION
321 WHEN OKL_API.G_EXCEPTION_ERROR THEN
322 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
323 l_api_name,
324 G_PKG_NAME,
325 'OKL_API.G_RET_STS_ERROR',
326 x_msg_count,
327 x_msg_data,
328 '_PVT');
329 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
330 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
331 l_api_name,
332 G_PKG_NAME,
333 'OKL_API.G_RET_STS_UNEXP_ERROR',
334 x_msg_count,
335 x_msg_data,
336 '_PVT');
337 WHEN OTHERS THEN
338 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
339 l_api_name,
340 G_PKG_NAME,
341 'OTHERS',
342 x_msg_count,
343 x_msg_data,
344 '_PVT');
345 END cancel_funding_request;
346
347 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
348 --------------------------------------------------------------------------------
349 --start of comments
350 -- Description : This api takes the contract id as input and returns the status of operation
351 -- IN Parameters : p_contract_id - ID of the Lease contract
352 --End of comments
353 --------------------------------------------------------------------------------
354 Procedure confirm_cancel_contract
355 (p_api_version IN NUMBER,
356 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2,
360 p_contract_id IN NUMBER,
361 p_new_contract_number IN VARCHAR2) is
362
363 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
364 l_api_name CONSTANT varchar2(30) := 'CONVERT_FA_AMOUNTS';
365 l_api_version CONSTANT NUMBER := 1.0;
366 l_seq_no NUMBER;
367 l_orig_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
368 l_orig_system_source_code OKC_K_HEADERS_V.ORIG_SYSTEM_SOURCE_CODE%TYPE;
369 l_new_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
370 l_orig_system_id1 OKC_K_HEADERS_V.ORIG_SYSTEM_ID1%TYPE;
371 l_funding_id OKL_TRX_AP_INVOICES_B.ID%TYPE; -- Added for bug 5944260/5981076(Forward R12 cklee)
372
373 CURSOR orig_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
374 SELECT contract_number, orig_system_source_code,
375 orig_system_id1
376 FROM okc_k_headers_v
377 WHERE id = p_chr_id;
378 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
379 Cursor funding_cancel_csr(p_contract_id NUMBER) IS
380 SELECT id
381 FROM okl_trx_ap_invoices_b
382 WHERE khr_id = p_contract_id
383 AND trx_status_code not in ('CANCELED','SUBMITTED', 'APPROVED', 'PROCESSED');
384 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
385 Begin
386 x_return_status := OKL_API.G_RET_STS_SUCCESS;
387 -- Call start_activity to create savepoint, check compatibility
388 -- and initialize message list
389 x_return_status := OKL_API.START_ACTIVITY (
390 l_api_name
391 ,p_init_msg_list
392 ,'_PVT'
393 ,x_return_status);
394 -- Check if activity started successfully
395 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
396 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
397 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
398 RAISE OKL_API.G_EXCEPTION_ERROR;
399 END IF;
400
401 Validate_Cancel_Contract
402 (p_api_version => 1.0,
403 x_return_status => x_return_status,
404 x_msg_count => x_msg_count,
405 x_msg_data => x_msg_data,
406 p_contract_id => p_contract_id);
407
408 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
409 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
410 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
411 RAISE Okl_Api.G_EXCEPTION_ERROR;
412 END IF;
413
414 -- Get Contract Number from Original Contract
415 OPEN orig_csr(p_contract_id);
416 FETCH orig_csr INTO l_orig_contract_number, l_orig_system_source_code, l_orig_system_id1;
417
418 IF orig_csr%NOTFOUND THEN
419 okl_api.set_message(
420 G_APP_NAME,
421 G_LLA_CHR_ID
422 );
423 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
424
425 END IF;
426
427 CLOSE orig_csr;
428
429 IF (l_orig_system_source_code = 'OKL_LEASE_APP') THEN
430
431 OKL_LEASE_APP_PVT.revert_leaseapp
432 ( p_api_version => 1.0,
433 p_init_msg_list => OKL_API.G_FALSE,
434 p_leaseapp_id => l_orig_system_id1,
435 x_return_status => x_return_status,
436 x_msg_count => x_msg_count,
437 x_msg_data => x_msg_data );
438
439 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
440 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
441 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
442 RAISE Okl_Api.G_EXCEPTION_ERROR;
443 END IF;
444
445 END IF;
446
447 IF (p_new_contract_number IS NULL) THEN
448 -- Get Sequence Number to generate Contract Number
449 SELECT okl_rbk_seq.NEXTVAL
450 INTO l_seq_no
451 FROM DUAL;
452
453 l_new_contract_number := l_orig_contract_number || '-CANCEL'|| l_seq_no;
454 ELSE
455 l_new_contract_number := p_new_contract_number;
456 END IF;
457 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
458 FOR funding_cancel_rec IN funding_cancel_csr (p_contract_id)
459 LOOP
460 l_funding_id := funding_cancel_rec.id;
461
462 cancel_funding_request
463 (p_api_version => 1.0,
464 x_return_status => x_return_status,
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 p_funding_id => l_funding_id);
468
469 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
470 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
471 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
472 RAISE Okl_Api.G_EXCEPTION_ERROR;
473 END IF;
474 End loop;
475 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
476 -- Update the status of the header
477 -- Bug# 7631183: Set datetime_cancelled to SYSDATE
478 UPDATE okc_k_headers_b
479 SET sts_code = 'ABANDONED',
480 contract_number = l_new_contract_number,
481 datetime_cancelled = SYSDATE
482 WHERE id = p_contract_id;
483
484 -- Update the status of the lines
485 UPDATE okc_k_lines_b
486 SET sts_code = 'ABANDONED'
487 WHERE dnz_chr_id = p_contract_id;
488
489 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
490 EXCEPTION
491 WHEN OKL_API.G_EXCEPTION_ERROR THEN
492 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
493 l_api_name,
494 G_PKG_NAME,
495 'OKL_API.G_RET_STS_ERROR',
496 x_msg_count,
497 x_msg_data,
498 '_PVT');
499 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
500 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
501 l_api_name,
502 G_PKG_NAME,
503 'OKL_API.G_RET_STS_UNEXP_ERROR',
504 x_msg_count,
505 x_msg_data,
506 '_PVT');
507 WHEN OTHERS THEN
508 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
509 l_api_name,
510 G_PKG_NAME,
511 'OTHERS',
512 x_msg_count,
513 x_msg_data,
514 '_PVT');
515
516 end confirm_cancel_contract;
517 end okl_maintain_contract_pvt;
518