[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.3 2009/06/02 10:42:22 racheruv 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 --Bug# 7030390
17 --------------------------------------------------------------------------------
18 --start of comments
19 -- Description : This api takes the contract id as input and rename all
20 -- the assets of the contract with CANCEL_XX so that the
21 -- original asset number can be reused.
22 -- IN Parameters : p_contract_id - contract id
23 --End of comments
24 --------------------------------------------------------------------------------
25 Procedure cancel_assets
26 (p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 p_contract_id IN NUMBER
32 ) IS
33
34
35 G_TOP_LINE_STYLE CONSTANT VARCHAR2(30) := 'TLS';
36 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_ASSETS';
37
38 --get the top line
39 CURSOR c_get_k_top_line(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
40 SELECT cle.id top_line
41 FROM okc_line_styles_b lse,
42 okc_k_lines_b cle
43 WHERE cle.dnz_chr_id = p_dnz_chr_id
44 AND cle.cle_id IS NULL
45 AND cle.chr_id = cle.dnz_chr_id
46 AND cle.lse_id = lse.id
47 AND lse.lse_parent_id IS NULL
48 AND lse.lse_type = G_TOP_LINE_STYLE
49 and lse.lty_code = 'FREE_FORM1';
50
51 --cursor for asset_number
52 CURSOR asset_num_csr (p_fin_ast_id IN NUMBER) IS
53 SELECT name
54 FROM okc_k_lines_tl
55 WHERE id = p_fin_ast_id;
56
57 --cursor to get new asset number
58 Cursor c_asset_no IS
59 select 'CANCEL_'||TO_CHAR(OKL_FAN_SEQ.NEXTVAL)
60 FROM dual;
61
62 --Cursors to find if asset number exists
63 CURSOR c_chk_asset_number(p_asset_number OKX_ASSETS_V.ASSET_NUMBER%TYPE) is
64 SELECT 'Y'
65 FROM DUAL
66 WHERE EXISTS (SELECT '1'
67 FROM OKL_TXL_ASSETS_V
68 WHERE asset_number = p_asset_number)
69 OR EXISTS (SELECT '1'
70 FROM OKL_TXD_ASSETS_V
71 WHERE asset_number = p_asset_number)
72 OR EXISTS (SELECT '1'
73 FROM OKX_ASSETS_V
74 WHERE asset_number = p_asset_number);
75
76 CURSOR c_okx_asset_lines_v(p_asset_number OKX_ASSETS_V.ASSET_NUMBER%TYPE) is
77 SELECT 'Y'
78 FROM DUAL
79 WHERE EXISTS (select '1'
80 from okc_k_lines_v kle,
81 okc_line_styles_b lse
82 where kle.name = p_asset_number
83 and kle.lse_id = lse.id
84 and lse.lty_code = 'FREE_FORM1');
85
86 l_asset_number OKC_K_LINES_TL.NAME%TYPE;
87 l_asset_new_number OKC_K_LINES_TL.NAME%TYPE;
88 l_asset_exists Varchar2(1) default 'N';
89 l_cle_id Number;
90
91 l_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
92 lx_clev_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
93 l_clev_rec_temp OKL_OKC_MIGRATION_PVT.clev_rec_type;
94 lx_clev_rec_temp OKL_OKC_MIGRATION_PVT.clev_rec_type;
95
96 --Cursor to check asset number on txl
97 CURSOR l_txlv_csr (p_finasst_id IN NUMBER, p_asstno IN VARCHAR2) is
98 Select txlv.id,
99 txlv.asset_number
100 From OKL_TXL_ASSETS_V txlv,
101 OKC_K_LINES_B cle,
102 OKC_LINE_STYLES_B lse
103 Where txlv.kle_id = cle.id
104 And cle.lse_id = lse.id
105 And lse.lty_code = 'FIXED_ASSET'
106 And cle.cle_id = p_finasst_id
107 And txlv.asset_number = p_asstNo;
108
109 l_txl_id OKL_TXL_ASSETS_V.ID%TYPE;
110 l_txl_asset_number OKL_TXL_ASSETS_V.ASSET_NUMBER%TYPE;
111
112 --Cursor to check asset number on txd
113 CURSOR l_txdv_csr (p_finasst_id IN NUMBER, p_asstno IN VARCHAR2) is
114 Select txdv.id,
115 txdv.asset_number
116 From OKL_TXD_ASSETS_V txdv,
117 OKL_TXL_ASSETS_V txlv,
118 OKC_K_LINES_B cle,
119 OKC_LINE_STYLES_B lse
120 Where txdv.tal_id = txlv.id
121 And txlv.kle_id = cle.id
122 And cle.lse_id = lse.id
123 And lse.lty_code = 'FIXED_ASSET'
124 And cle.cle_id = p_finasst_id
125 And txdv.asset_number = p_asstno;
126
127 l_txd_id OKL_TXD_ASSETS_V.ID%TYPE;
128 l_txd_asset_number OKL_TXD_ASSETS_V.ASSET_NUMBER%TYPE;
129
130 l_tlpv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
131 lx_tlpv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
132
133 l_tlpv_temp_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
134 lx_tlpv_temp_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
135
136 l_adpv_rec OKL_TXD_ASSETS_PUB.adpv_rec_type;
137 lx_adpv_rec OKL_TXD_ASSETS_PUB.adpv_rec_type;
138
139 BEGIN
140 x_return_status := OKL_API.G_RET_STS_SUCCESS;
141 -- Call start_activity to create savepoint, check compatibility
142 -- and initialize message list
143 x_return_status := OKL_API.START_ACTIVITY (
144 l_api_name,
145 p_init_msg_list,
146 '_PVT',
147 x_return_status);
148 -- Check if activity started successfully
149 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
151 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
152 RAISE OKL_API.G_EXCEPTION_ERROR;
153 END IF;
154
155 FOR r_get_k_top_line IN c_get_k_top_line(p_dnz_chr_id => p_contract_id) LOOP
156 --initialize variables
157 l_asset_number := NULL;
158 l_asset_new_number := null;
159 l_asset_exists := 'N';
160
161 l_cle_id :=r_get_k_top_line.top_line;
162
163 --dbms_output.put_line('processing l_cle_id '||l_cle_id);
164
165 OPEN asset_num_csr(p_fin_ast_id => l_cle_id);
166 FETCH asset_num_csr INTO l_asset_number;
167 IF asset_num_csr%NOTFOUND THEN
168 NULL;
169 END IF;
170 CLOSE asset_num_csr;
171
172 -- dbms_output.put_line('l_asset_number '||l_asset_number);
173
174 OPEN c_asset_no;
175 Loop
176 Fetch c_asset_no into l_asset_new_number;
177 --chk if asset already exists
178 l_asset_exists := 'N';
179 open c_chk_asset_number(l_asset_new_number);
180 Fetch c_chk_asset_number into l_asset_exists;
181 If c_chk_asset_number%NOTFOUND Then
182 open c_okx_asset_lines_v(l_asset_new_number);
183 Fetch c_okx_asset_lines_v into l_asset_exists;
184 Close c_okx_asset_lines_v;
185 End If;
186 Close c_chk_asset_number;
187 If l_asset_exists = 'N' Then
188 Exit;
189 End If;
190 End Loop;
191 close c_asset_no;
192 --dbms_output.put_line('l_asset_new_number '||l_asset_new_number);
193
194 --update asset number on top line
195 l_clev_rec :=l_clev_rec_temp;
196 lx_clev_rec:=lx_clev_rec_temp;
197 l_clev_rec.id := l_cle_id;
198 l_clev_rec.name := l_asset_new_number;
199 okl_okc_migration_pvt.update_contract_line(
200 p_api_version => p_api_version,
201 p_init_msg_list => p_init_msg_list,
202 x_return_status => x_return_status,
203 x_msg_count => x_msg_count,
204 x_msg_data => x_msg_data,
205 p_restricted_update => OKC_API.G_FALSE,
206 p_clev_rec => l_clev_rec,
207 x_clev_rec => lx_clev_rec);
208
209 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
210 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
211 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
212 RAISE Okl_Api.G_EXCEPTION_ERROR;
213 END IF;
214
215 l_txl_id:=null;
216 l_txl_asset_number:=null;
217 l_tlpv_rec :=l_tlpv_temp_rec;
218 lx_tlpv_rec :=lx_tlpv_temp_rec;
219
220 --update asset number on txl
221 OPEN l_txlv_csr(l_cle_id, l_asset_number);
222 Loop
223 Fetch l_txlv_csr into l_txl_id, l_txl_asset_number;
224 Exit When l_txlv_csr%NOTFOUND;
225 IF l_txl_asset_number is not null then
226 l_tlpv_rec :=l_tlpv_temp_rec;
227 lx_tlpv_rec :=lx_tlpv_temp_rec;
228 l_tlpv_rec.id := l_txl_id;
229 l_tlpv_rec.asset_number := l_asset_new_number; --15 character
230 okl_txl_assets_pub.update_txl_asset_Def(
231 p_api_version => p_api_version,
232 p_init_msg_list => p_init_msg_list,
233 x_return_status => x_return_status,
234 x_msg_count => x_msg_count,
235 x_msg_data => x_msg_data,
236 p_tlpv_rec => l_tlpv_rec,
237 x_tlpv_rec => lx_tlpv_rec);
238 --dbms_output.put_line('after updating txl assets for asset number'||x_return_status);
239 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
240 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
241 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
242 RAISE Okl_Api.G_EXCEPTION_ERROR;
243 END IF;
244
245 End IF;
246 End Loop;
247 CLOSE l_txlv_csr;
248
249 l_txd_id:=NULL;
250 l_txd_asset_number:=NULL;
251 --update asset number on txd
252 OPEN l_txdv_csr(l_cle_id,l_asset_number);
253 LOOP
254 l_txd_id:=NULL;
255 l_txd_asset_number:=NULL;
256 Fetch l_txdv_csr into l_txd_id, l_txd_asset_number;
257 Exit When l_txdv_csr%NOTFOUND;
258 IF l_txd_asset_number is not null then
259 l_adpv_rec.id := l_txd_id;
260 l_adpv_rec.asset_number := l_asset_new_number;
261 okl_txd_assets_pub.update_txd_asset_Def(
262 p_api_version => p_api_version,
263 p_init_msg_list => p_init_msg_list,
264 x_return_status => x_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data,
267 p_adpv_rec => l_adpv_rec,
268 x_adpv_rec => lx_adpv_rec);
269 -- dbms_output.put_line('After Updating TXD-->update_txd_asset_Def'||x_return_status);
270
271 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
272 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
273 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
274 RAISE Okl_Api.G_EXCEPTION_ERROR;
275 END IF;
276
277 End IF;
278 End Loop;
279 CLOSE l_txdv_csr;
280 END LOOP;
281
282 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data );
283 EXCEPTION
284 WHEN OKL_API.G_EXCEPTION_ERROR THEN
285 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
286 l_api_name,
287 G_PKG_NAME,
288 'OKL_API.G_RET_STS_ERROR',
289 x_msg_count,
290 x_msg_data,
291 '_PVT');
292 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
293 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
294 l_api_name,
295 G_PKG_NAME,
296 'OKL_API.G_RET_STS_UNEXP_ERROR',
297 x_msg_count,
298 x_msg_data,
299 '_PVT');
300 WHEN OTHERS THEN
301 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
302 l_api_name,
303 G_PKG_NAME,
304 'OTHERS',
305 x_msg_count,
306 x_msg_data,
307 '_PVT');
308 END cancel_assets;
309 --Bug# 7030390 End
310
311 --------------------------------------------------------------------------------
312 --start of comments
313 -- Description : This api takes the contract id as input and
314 -- returns the Validation status
315 -- IN Parameters : p_contract_id - contract id
316 --End of comments
317 --------------------------------------------------------------------------------
318 Procedure Validate_Cancel_Contract
319 (p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2,
324 p_contract_id IN NUMBER) is
325
326 CURSOR contract_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
327 SELECT contract_number
328 FROM okc_k_headers_v
329 WHERE id = p_chr_id;
330
331 Cursor bankrupt_csr(p_contract_id NUMBER) IS
332 SELECT DECODE(disposition_code, 'NEGOTIATION', 'Y', 'GRANTED', 'Y', NULL, 'Y', 'N') bankruptcy_status
333 FROM iex_bankruptcies ban
334 WHERE EXISTS (SELECT 1 FROM okc_k_party_roles_b rle
335 WHERE rle.dnz_chr_id = p_contract_id
336 AND rle.rle_code = 'LESSEE'
337 AND TO_NUMBER(rle.object1_id1) = ban.party_id);
338
339 Cursor contract_status_csr(p_contract_id NUMBER) IS
340 SELECT sts_code
341 FROM okc_k_headers_b chrb
342 WHERE chrb.id = p_contract_id
343 AND chrb.scs_code = 'LEASE'
344 AND chrb.sts_code in ('BOOKED', 'EVERGREEN', 'EXPIRED', 'TERMINATED', 'REVERSED', 'ABANDONED');
345
346 Cursor funding_disb_txn_csr(p_contract_id NUMBER) IS
347 SELECT funding_type_code
348 FROM okl_trx_ap_invoices_b
349 WHERE khr_id = p_contract_id
350 -- AND ((funding_type_code = 'MANUAL_DISB') OR
351 -- Added for bug #5944260/5981076(Forward R12 cklee)
352 AND (trx_status_code in ('SUBMITTED', 'APPROVED', 'PROCESSED'));
353
354
355
356 Cursor funding_disb_txn_type_csr(p_code VARCHAR2) IS
357 SELECT meaning
358 FROM fnd_lookups
359 WHERE lookup_type = 'OKL_FUNDING_TYPE'
360 AND lookup_code = p_code;
361
362 Cursor Billing_invoice_status_csr(p_contract_id NUMBER) IS
363 SELECT '!'
364 FROM okl_trx_ar_invoices_b
365 WHERE khr_id = p_contract_id
366 AND trx_status_code <> 'ERROR';
367
368 Cursor Advance_receipt_csr(p_contract_id NUMBER) IS
369 SELECT '!'
370 FROM okl_trx_csh_receipt_v otcr, okl_txl_rcpt_apps_v otra
371 WHERE otcr.id = otra.rct_id_details
372 AND otcr.receipt_type = 'ADV'
373 AND otra.khr_id = p_contract_id;
374
375 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
376 l_api_name CONSTANT varchar2(30) := 'VALIDATE_CANCEL_CONTRACTS';
377 l_api_version CONSTANT NUMBER := 1.0;
378 l_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
379 l_sts_code OKC_K_HEADERS_V.sts_code%TYPE := NULL;
380 l_funding_type_code OKL_TRX_AP_INVOICES_B.funding_type_code%TYPE := NULL;
381 l_txn_type FND_LOOKUPS.meaning%TYPE;
382 l_bankruptcy_status VARCHAR2(1);
383 l_status_code OKC_K_HEADERS_B.sts_code%TYPE;
384 l_billing_invoice_status VARCHAR2(1) := '?';
385 l_advance_receipt_status VARCHAR2(1) := '?';
386
387 begin
388 x_return_status := OKL_API.G_RET_STS_SUCCESS;
389 -- Call start_activity to create savepoint, check compatibility
390 -- and initialize message list
391 x_return_status := OKL_API.START_ACTIVITY (
392 l_api_name
393 ,p_init_msg_list
394 ,'_PVT'
395 ,x_return_status);
396 -- Check if activity started successfully
397 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
398 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
399 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
400 RAISE OKL_API.G_EXCEPTION_ERROR;
401 END IF;
402
403 -- Get Contract Number from Original Contract
404 OPEN contract_csr(p_contract_id);
405 FETCH contract_csr INTO l_contract_number;
406
407 IF contract_csr%NOTFOUND THEN
408 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
409 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
410 p_msg_name => G_LLA_CHR_ID);
411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
412 END IF;
413
414 CLOSE contract_csr;
415
416 OPEN bankrupt_csr(p_contract_id);
417 FETCH bankrupt_csr INTO l_bankruptcy_status;
418 CLOSE bankrupt_csr;
419
420 IF (nvl(l_bankruptcy_status,'N') = 'Y') THEN
421 l_sts_code := 'BANKRUPTCY_HOLD';
422 OKC_API.set_message(
423 p_app_name => G_APP_NAME,
424 p_msg_name => G_CANC_CNTR_INV_STATUS,
425 p_token1 => G_CONTRACT_NUMBER_TOKEN,
426 p_token1_value => l_contract_number,
427 p_token2 => G_STATUS_CODE_TOKEN,
428 p_token2_value => l_sts_code);
429 x_return_status := OKL_API.G_RET_STS_ERROR;
430 RAISE OKL_API.G_EXCEPTION_ERROR;
431 END IF;
432
433 OPEN contract_status_csr(p_contract_id);
434 FETCH contract_status_csr INTO l_sts_code;
435 IF (contract_status_csr%NOTFOUND) THEN
436 null;
437 END IF;
438
439 CLOSE contract_status_csr;
440
441 IF (l_sts_code IS NOT NULL) THEN
442 OKC_API.set_message(
443 p_app_name => G_APP_NAME,
444 p_msg_name => G_CANC_CNTR_INV_STATUS,
445 p_token1 => G_CONTRACT_NUMBER_TOKEN,
446 p_token1_value => l_contract_number,
447 p_token2 => G_STATUS_CODE_TOKEN,
448 p_token2_value => l_sts_code);
449 x_return_status := OKL_API.G_RET_STS_ERROR;
450 RAISE OKL_API.G_EXCEPTION_ERROR;
451 END IF;
452
453 OPEN funding_disb_txn_csr(p_contract_id);
454 FETCH funding_disb_txn_csr INTO l_funding_type_code;
455 IF (funding_disb_txn_csr%NOTFOUND) THEN
456 null;
457 END IF;
458 CLOSE funding_disb_txn_csr;
459
460 IF (l_funding_type_code is NOT NULL) THEN
461 OPEN funding_disb_txn_type_csr(l_funding_type_code);
462 FETCH funding_disb_txn_type_csr INTO l_txn_type;
463 CLOSE funding_disb_txn_type_csr;
464
465 OKC_API.set_message(
466 p_app_name => G_APP_NAME,
467 p_msg_name => G_CANC_CNTR_PYMT_EXIST,
468 p_token1 => G_TXN_TYPE,
469 p_token1_value => l_txn_type,
470 p_token2 => G_CONTRACT_NUMBER_TOKEN,
471 p_token2_value => l_contract_number);
472
473 x_return_status := OKC_API.G_RET_STS_ERROR;
474 RAISE OKL_API.G_EXCEPTION_ERROR;
475 END IF;
476
477 OPEN Billing_invoice_status_csr(p_contract_id);
478 FETCH Billing_invoice_status_csr INTO l_Billing_invoice_status;
479 IF (Billing_invoice_status_csr%NOTFOUND) THEN
480 null;
481 END IF;
482 CLOSE Billing_invoice_status_csr;
483
484 IF (l_Billing_invoice_status = '!') THEN
485 OKC_API.set_message(
486 p_app_name => G_APP_NAME,
487 p_msg_name => G_CANC_CNTR_RCPT_EXIST,
488 p_token1 => G_CONTRACT_NUMBER_TOKEN,
489 p_token1_value => l_contract_number);
490
491 x_return_status := OKC_API.G_RET_STS_ERROR;
492 RAISE OKL_API.G_EXCEPTION_ERROR;
493 END IF;
494
495 OPEN Advance_receipt_csr(p_contract_id);
496 FETCH Advance_receipt_csr INTO l_advance_receipt_status;
497 IF (Advance_receipt_csr%NOTFOUND) THEN
498 null;
499 END IF;
500 CLOSE Advance_receipt_csr;
501
502 IF (l_advance_receipt_status = '!') THEN
503 OKC_API.set_message(
504 p_app_name => G_APP_NAME,
505 p_msg_name => G_CANC_CNTR_RCPT_EXIST,
506 p_token1 => G_CONTRACT_NUMBER_TOKEN,
507 p_token1_value => l_contract_number);
508
509 x_return_status := OKC_API.G_RET_STS_ERROR;
510 RAISE OKL_API.G_EXCEPTION_ERROR;
511 END IF;
512 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
513 EXCEPTION
514 WHEN OKL_API.G_EXCEPTION_ERROR THEN
515 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
516 l_api_name,
517 G_PKG_NAME,
518 'OKL_API.G_RET_STS_ERROR',
519 x_msg_count,
520 x_msg_data,
521 '_PVT');
522 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
523 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
524 l_api_name,
525 G_PKG_NAME,
526 'OKL_API.G_RET_STS_UNEXP_ERROR',
527 x_msg_count,
528 x_msg_data,
529 '_PVT');
530 WHEN OTHERS THEN
531 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
532 l_api_name,
533 G_PKG_NAME,
534 'OTHERS',
535 x_msg_count,
536 x_msg_data,
537 '_PVT');
538 end validate_cancel_contract;
539
540 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
541 PROCEDURE cancel_funding_request(p_api_version IN NUMBER,
542 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
543 x_return_status OUT NOCOPY VARCHAR2,
544 x_msg_count OUT NOCOPY NUMBER,
545 x_msg_data OUT NOCOPY VARCHAR2,
546 p_funding_id IN OKL_TRX_AP_INVOICES_B.ID%TYPE) IS
547
548 l_tapv_rec tapv_rec_type;
549 x_tapv_rec tapv_rec_type;
550 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_FUNDING_REQUEST';
551
552 -- Fix BPD Bug. these columns will be overridden by tapi
553 CURSOR c_tap (p_funding_id OKL_TRX_AP_INVOICES_B.ID%TYPE)
554 IS
555 SELECT h.VENDOR_INVOICE_NUMBER,
556 h.PAY_GROUP_LOOKUP_CODE,
557 h.NETTABLE_YN,
558 h.FUNDING_TYPE_CODE,
559 h.INVOICE_TYPE
560 FROM OKL_TRX_AP_INVOICES_B h
561 WHERE h.id = p_funding_id;
562
563 r_tap c_tap%ROWTYPE;
564
565 BEGIN
566
567 x_return_status := OKL_API.G_RET_STS_SUCCESS;
568 -- Call start_activity to create savepoint, check compatibility
569 -- and initialize message list
570 x_return_status := OKL_API.START_ACTIVITY (
571 l_api_name,
572 p_init_msg_list,
573 '_PVT',
574 x_return_status);
575 -- Check if activity started successfully
576 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
577 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
578 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
579 RAISE OKL_API.G_EXCEPTION_ERROR;
580 END IF;
581
582 -- Get the internal invoice Details
583 OPEN c_tap(p_funding_id);
584 FETCH c_tap INTO r_tap;
585 CLOSE c_tap;
586
587 l_tapv_rec.id := p_funding_id;
588 l_tapv_rec.trx_status_code := 'CANCELED';
589 l_tapv_rec.vendor_invoice_number := r_tap.vendor_invoice_number;
590 l_tapv_rec.pay_group_lookup_code := r_tap.pay_group_lookup_code;
591 l_tapv_rec.nettable_yn := r_tap.nettable_yn;
592 l_tapv_rec.invoice_type := r_tap.invoice_type;
593
594 -- update funding status
595 OKL_TRX_AP_INVOICES_PUB.UPDATE_TRX_AP_INVOICES(
596 p_api_version => p_api_version,
597 p_init_msg_list => p_init_msg_list,
598 x_return_status => x_return_status,
599 x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data,
601 p_tapv_rec => l_tapv_rec,
602 x_tapv_rec => x_tapv_rec);
603
604 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
605 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
606 ELSIF (x_return_Status = OKL_API.G_RET_STS_ERROR) THEN
607 RAISE OKL_API.G_EXCEPTION_ERROR;
608 END IF;
609
610
611 OKL_API.END_ACTIVITY (x_msg_count,
612 x_msg_data );
613
614
615
616 EXCEPTION
617 WHEN OKL_API.G_EXCEPTION_ERROR THEN
618 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
619 l_api_name,
620 G_PKG_NAME,
621 'OKL_API.G_RET_STS_ERROR',
622 x_msg_count,
623 x_msg_data,
624 '_PVT');
625 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
626 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
627 l_api_name,
628 G_PKG_NAME,
629 'OKL_API.G_RET_STS_UNEXP_ERROR',
630 x_msg_count,
631 x_msg_data,
632 '_PVT');
633 WHEN OTHERS THEN
634 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
635 l_api_name,
636 G_PKG_NAME,
637 'OTHERS',
638 x_msg_count,
639 x_msg_data,
640 '_PVT');
641 END cancel_funding_request;
642
643 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
644 --------------------------------------------------------------------------------
645 --start of comments
646 -- Description : This api takes the contract id as input and returns the status of operation
647 -- IN Parameters : p_contract_id - ID of the Lease contract
648 --End of comments
649 --------------------------------------------------------------------------------
650 Procedure confirm_cancel_contract
651 (p_api_version IN NUMBER,
652 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2,
656 p_contract_id IN NUMBER,
657 p_new_contract_number IN VARCHAR2) is
658
659 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
660 l_api_name CONSTANT varchar2(30) := 'CONVERT_FA_AMOUNTS';
661 l_api_version CONSTANT NUMBER := 1.0;
662 l_seq_no NUMBER;
663 l_orig_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
664 l_orig_system_source_code OKC_K_HEADERS_V.ORIG_SYSTEM_SOURCE_CODE%TYPE;
665 l_new_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
666 l_orig_system_id1 OKC_K_HEADERS_V.ORIG_SYSTEM_ID1%TYPE;
667 l_funding_id OKL_TRX_AP_INVOICES_B.ID%TYPE; -- Added for bug 5944260/5981076(Forward R12 cklee)
668
669 CURSOR orig_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
670 SELECT contract_number, orig_system_source_code,
671 orig_system_id1
672 FROM okc_k_headers_v
673 WHERE id = p_chr_id;
674 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
675 Cursor funding_cancel_csr(p_contract_id NUMBER) IS
676 SELECT id
677 FROM okl_trx_ap_invoices_b
678 WHERE khr_id = p_contract_id
679 AND trx_status_code not in ('CANCELED','SUBMITTED', 'APPROVED', 'PROCESSED');
680 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
681 Begin
682 x_return_status := OKL_API.G_RET_STS_SUCCESS;
683 -- Call start_activity to create savepoint, check compatibility
684 -- and initialize message list
685 x_return_status := OKL_API.START_ACTIVITY (
686 l_api_name
687 ,p_init_msg_list
688 ,'_PVT'
689 ,x_return_status);
690 -- Check if activity started successfully
691 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
692 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
693 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
694 RAISE OKL_API.G_EXCEPTION_ERROR;
695 END IF;
696
697 Validate_Cancel_Contract
698 (p_api_version => 1.0,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_contract_id => p_contract_id);
703
704 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
705 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
706 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
707 RAISE Okl_Api.G_EXCEPTION_ERROR;
708 END IF;
709
710 -- Get Contract Number from Original Contract
711 OPEN orig_csr(p_contract_id);
712 FETCH orig_csr INTO l_orig_contract_number, l_orig_system_source_code, l_orig_system_id1;
713
714 IF orig_csr%NOTFOUND THEN
715 okl_api.set_message(
716 G_APP_NAME,
717 G_LLA_CHR_ID
718 );
719 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
720
721 END IF;
722
723 CLOSE orig_csr;
724
725 IF (l_orig_system_source_code = 'OKL_LEASE_APP') THEN
726
727 OKL_LEASE_APP_PVT.revert_leaseapp
728 ( p_api_version => 1.0,
729 p_init_msg_list => OKL_API.G_FALSE,
730 p_leaseapp_id => l_orig_system_id1,
731 x_return_status => x_return_status,
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data );
734
735 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
736 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
737 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
738 RAISE Okl_Api.G_EXCEPTION_ERROR;
739 END IF;
740
741 END IF;
742
743 IF (p_new_contract_number IS NULL) THEN
744 -- Get Sequence Number to generate Contract Number
745 SELECT okl_rbk_seq.NEXTVAL
746 INTO l_seq_no
747 FROM DUAL;
748
749 l_new_contract_number := l_orig_contract_number || '-CANCEL'|| l_seq_no;
750 ELSE
751 l_new_contract_number := p_new_contract_number;
752 END IF;
753 -- Added for bug #5944260/5981076(Forward R12 cklee) -- start
754 FOR funding_cancel_rec IN funding_cancel_csr (p_contract_id)
755 LOOP
756 l_funding_id := funding_cancel_rec.id;
757
758 cancel_funding_request
759 (p_api_version => 1.0,
760 x_return_status => x_return_status,
761 x_msg_count => x_msg_count,
762 x_msg_data => x_msg_data,
763 p_funding_id => l_funding_id);
764
765 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
766 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
767 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
768 RAISE Okl_Api.G_EXCEPTION_ERROR;
769 END IF;
770 End loop;
771 -- Added for bug #5944260/5981076(Forward R12 cklee) -- End
772 -- Update the status of the header
773 -- Bug# 7631183: Set datetime_cancelled to SYSDATE
774 UPDATE okc_k_headers_b
775 SET sts_code = 'ABANDONED',
776 contract_number = l_new_contract_number,
777 datetime_cancelled = SYSDATE
778 WHERE id = p_contract_id;
779
780 --Bug# 7030390
781 -- Rename assets with CANCEL_XX :
782 cancel_assets(p_api_version =>1.0,
783 p_init_msg_list =>OKL_API.G_FALSE,
784 x_return_status =>x_return_status,
785 x_msg_count =>x_msg_count,
786 x_msg_data =>x_msg_data,
787 p_contract_id =>p_contract_id
788 ) ;
789 --Bug# 7030390 End
790
791 -- Update the status of the lines
792 UPDATE okc_k_lines_b
793 SET sts_code = 'ABANDONED'
794 WHERE dnz_chr_id = p_contract_id;
795
796 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
797 EXCEPTION
798 WHEN OKL_API.G_EXCEPTION_ERROR THEN
799 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
800 l_api_name,
801 G_PKG_NAME,
802 'OKL_API.G_RET_STS_ERROR',
803 x_msg_count,
804 x_msg_data,
805 '_PVT');
806 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
807 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
808 l_api_name,
809 G_PKG_NAME,
810 'OKL_API.G_RET_STS_UNEXP_ERROR',
811 x_msg_count,
812 x_msg_data,
813 '_PVT');
814 WHEN OTHERS THEN
815 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
816 l_api_name,
817 G_PKG_NAME,
818 'OTHERS',
819 x_msg_count,
820 x_msg_data,
821 '_PVT');
822
823 end confirm_cancel_contract;
824 end okl_maintain_contract_pvt;
825