[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_BOOK_PVT
Source
1 PACKAGE BODY okl_contract_book_pvt AS
2 /* $Header: OKLRBKGB.pls 120.63.12010000.3 2008/10/03 17:35:25 rkuttiya ship $ */
3
4 -------------------------------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 -------------------------------------------------------------------------------------------------
7 g_no_parent_record CONSTANT VARCHAR2 (200)
8 := 'OKC_NO_PARENT_RECORD';
9 g_fnd_app CONSTANT VARCHAR2 (200) := okl_api.g_fnd_app;
10 g_required_value CONSTANT VARCHAR2 (200)
11 := okl_api.g_required_value;
12 g_invalid_value CONSTANT VARCHAR2 (200)
13 := okl_api.g_invalid_value;
14 g_unexpected_error CONSTANT VARCHAR2 (200)
15 := 'OKC_CONTRACTS_UNEXP_ERROR';
16 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'SQLerrm';
17 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'SQLcode';
18 g_uppercase_required CONSTANT VARCHAR2 (200)
19 := 'OKL_CONTRACTS_UPPERCASE_REQ';
20 g_col_name_token CONSTANT VARCHAR2 (200)
21 := okl_api.g_col_name_token;
22 g_debug_enabled CONSTANT VARCHAR2 (10)
23 := okl_debug_pub.check_log_enabled;
24 g_is_debug_statement_on BOOLEAN;
25 g_auto_approve CONSTANT VARCHAR2 (15) := 'AUTO_APPROVE';
26 ------------------------------------------------------------------------------------
27 -- GLOBAL EXCEPTION
28 ------------------------------------------------------------------------------------
29 g_exception_halt_validation EXCEPTION;
30 g_exception_stop_validation EXCEPTION;
31 g_api_type CONSTANT VARCHAR2 (4) := '_PVT';
32 g_api_version CONSTANT NUMBER := 1.0;
33 g_scope CONSTANT VARCHAR2 (4) := '_PVT';
34 --rviriyal
35 /*
36 -- mvasudev, 08/17/2004
37 -- Added Constants to enable Business Event
38 */
39 g_wf_evt_khr_validated CONSTANT VARCHAR2 (43)
40 := 'oracle.apps.okl.la.lease_contract.validated';
41 g_wf_evt_khr_gen_strms CONSTANT VARCHAR2 (61)
42 := 'oracle.apps.okl.la.lease_contract.stream_generation_completed';
43 g_wf_evt_khr_gen_journal CONSTANT VARCHAR2 (60)
44 := 'oracle.apps.okl.la.lease_contract.journal_entries_generated';
45 g_wf_evt_khr_submit_appr CONSTANT VARCHAR2 (56)
46 := 'oracle.apps.okl.la.lease_contract.submitted_for_approval';
47 g_wf_evt_khr_activated CONSTANT VARCHAR2 (43)
48 := 'oracle.apps.okl.la.lease_contract.activated';
49 g_wf_evt_khr_rebook_comp CONSTANT VARCHAR2 (50)
50 := 'oracle.apps.okl.la.lease_contract.rebook_completed';
51 g_wf_itm_contract_id CONSTANT VARCHAR2 (15) := 'CONTRACT_ID';
52 g_wf_itm_contract_process CONSTANT VARCHAR2 (20) := 'CONTRACT_PROCESS';
53 g_wf_itm_src_contract_id CONSTANT VARCHAR2 (20)
54 := 'SOURCE_CONTRACT_ID';
55 g_wf_itm_dest_contract_id CONSTANT VARCHAR2 (25)
56 := 'DESTINATION_CONTRACT_ID';
57 g_wf_itm_trx_date CONSTANT VARCHAR2 (20) := 'TRANSACTION_DATE';
58 g_khr_process_rebook CONSTANT VARCHAR2 (6)
59 := okl_lla_util_pvt.g_khr_process_rebook;
60 /*
61 -- cklee, 12/21/2005
62 -- Added Constants to enable Business Event Bug# 4901292
63 */
64 g_wf_evt_chr_list_validated CONSTANT VARCHAR2 (240)
65 := 'oracle.apps.okl.sales.leaseapplication.khr_chklist_items_val';
66 g_module VARCHAR2 (255) := 'okl.stream.esg.okl_esg_transport_pvt';
67 g_module_name VARCHAR2 (255) := 'okl.plsql.stream.esg.okl_esg_transport_pvt';
68 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
69 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
70 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
71
72 -- GLOBAL VARIABLES
73 -----------------------------------------------------------------------------------
74
75 --Bug 5909373
76 --Function to check whether the contract is a release contract or not
77 FUNCTION is_release_contract (p_contract_id NUMBER)
78 RETURN VARCHAR2 IS
79 l_is_release_contract VARCHAR2 (1) := 'N';
80
81 --cursor to check if contract is a re-lease contract
82 CURSOR l_chk_rel_khr_csr (p_chr_id IN NUMBER) IS
83 SELECT 'Y'
84 FROM okc_k_headers_b CHR
85 WHERE CHR.ID = p_chr_id
86 AND NVL (CHR.orig_system_source_code, 'XXXX') = 'OKL_RELEASE';
87
88 l_rel_khr VARCHAR2 (1) DEFAULT 'N';
89
90 --cursor to check if contract has re-lease assets
91 CURSOR l_chk_rel_ast_csr (p_chr_id IN NUMBER) IS
92 SELECT 'Y'
93 FROM okc_k_headers_b CHR
94 WHERE NVL (CHR.orig_system_source_code, 'XXXX') <> 'OKL_RELEASE'
95 AND CHR.ID = p_chr_id
96 AND EXISTS (
97 SELECT '1'
98 FROM okc_rules_b rul
99 WHERE rul.dnz_chr_id = CHR.ID
100 AND rul.rule_information_category = 'LARLES'
101 AND NVL (rule_information1, 'N') = 'Y');
102
103 l_rel_ast VARCHAR2 (1) DEFAULT 'N';
104 BEGIN
105 OPEN l_chk_rel_khr_csr (p_contract_id);
106
107 FETCH l_chk_rel_khr_csr
108 INTO l_rel_khr;
109
110 CLOSE l_chk_rel_khr_csr;
111
112 IF (NVL (l_rel_khr, 'N') = 'Y') THEN
113 l_is_release_contract := 'Y';
114 END IF;
115
116 OPEN l_chk_rel_ast_csr (p_contract_id);
117
118 FETCH l_chk_rel_ast_csr
119 INTO l_rel_ast;
120
121 CLOSE l_chk_rel_ast_csr;
122
123 IF (NVL (l_rel_ast, 'N') = 'Y') THEN
124 l_is_release_contract := 'Y';
125 END IF;
126
127 RETURN l_is_release_contract;
128 END is_release_contract;
129
130 --Bug 5909373
131
132 ---------------------------------------------------------------
133 --Bug# 3556674 validate chr_id
134 ---------------------------------------------------------------
135 PROCEDURE validate_chr_id (
136 p_chr_id IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2
138 ) IS
139 --Cursor to check existence of contract
140 CURSOR l_chr_csr (p_chr_id IN NUMBER) IS
141 SELECT 'Y'
142 FROM okc_k_headers_b chrb
143 WHERE chrb.ID = p_chr_id AND chrb.scs_code = 'LEASE';
144
145 l_exists VARCHAR2 (1) DEFAULT 'N';
146 BEGIN
147 IF (p_chr_id = okl_api.g_miss_num OR p_chr_id IS NULL) THEN
148 okl_api.set_message (g_app_name,
149 g_required_value,
150 g_col_name_token,
151 'p_chr_id'
152 );
153 x_return_status := okl_api.g_ret_sts_error;
154 RAISE g_exception_halt_validation;
155 END IF;
156
157 l_exists := 'N';
158
159 --check if chr id passed is valie
160 OPEN l_chr_csr (p_chr_id => p_chr_id);
161
162 FETCH l_chr_csr
163 INTO l_exists;
164
165 IF l_chr_csr%NOTFOUND THEN
166 NULL;
167 END IF;
168
169 CLOSE l_chr_csr;
170
171 IF l_exists = 'N' THEN
172 okl_api.set_message (g_app_name,
173 g_invalid_value,
174 g_col_name_token,
175 'p_chr_id'
176 );
177 x_return_status := okl_api.g_ret_sts_error;
178 RAISE g_exception_halt_validation;
179 END IF;
180 EXCEPTION
181 WHEN g_exception_halt_validation THEN
182 NULL;
183 WHEN OTHERS THEN
184 IF l_chr_csr%ISOPEN THEN
185 CLOSE l_chr_csr;
186 END IF;
187
188 okl_api.set_message (p_app_name => g_app_name,
189 p_msg_name => g_unexpected_error,
190 p_token1 => g_sqlcode_token,
191 p_token1_value => SQLCODE,
192 p_token2 => g_sqlerrm_token,
193 p_token2_value => SQLERRM
194 );
195 x_return_status := okl_api.g_ret_sts_unexp_error;
196 END validate_chr_id;
197
198 PROCEDURE execute_qa_check_list (
199 p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
201 x_return_status OUT NOCOPY VARCHAR2,
202 x_msg_count OUT NOCOPY NUMBER,
203 x_msg_data OUT NOCOPY VARCHAR2,
204 p_qcl_id IN NUMBER,
205 p_chr_id IN NUMBER,
206 p_call_mode IN VARCHAR2 DEFAULT 'ACTUAL',
207 x_msg_tbl OUT NOCOPY okl_qa_check_pub.msg_tbl_type
208 ) IS
209 l_api_name CONSTANT VARCHAR2 (30) := 'EXECUTE_QA_CHECK_LIST';
210 l_api_version CONSTANT NUMBER := 1;
211 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
212 l_passstatus VARCHAR2 (30) := 'PASSED';
213 l_failstatus VARCHAR2 (256) := 'INCOMPLETE';
214 severity VARCHAR2 (1);
215 l_msg_tbl okl_qa_check_pub.msg_tbl_type;
216 l_pmsg_tbl okc_qa_check_pub.msg_tbl_type;
217 j NUMBER;
218 x_batch_number NUMBER;
219
220 CURSOR l_dltype_csr (chrid NUMBER) IS
221 SELECT khr.deal_type
222 FROM okc_k_headers_v CHR, okl_k_headers khr
223 WHERE CHR.ID = khr.ID AND CHR.ID = chrid;
224
225 l_dltype_rec l_dltype_csr%ROWTYPE;
226
227 CURSOR l_ptmpl_csr (p_chr_id IN NUMBER) IS
228 SELECT chrb.template_yn,
229 khr.template_type_code
230 FROM okc_k_headers_b chrb, okl_k_headers khr
231 WHERE chrb.ID = khr.ID AND chrb.ID = p_chr_id;
232
233 l_template_type_code okl_k_headers.template_type_code%TYPE;
234 l_template_yn okc_k_headers_b.template_yn%TYPE;
235 l_pqcl_id okc_k_headers_b.qcl_id%TYPE;
236
237 CURSOR l_ptmpl_qcl_csr (p_chr_id IN NUMBER) IS
238 SELECT chrb.qcl_id
239 FROM okc_k_headers_b chrb
240 WHERE chrb.ID = p_chr_id;
241
242 /*
243 -- mvasudev, 08/30/2004
244 -- Added PROCEDURE to enable Business Event
245 */
246 --START 21-Dec-2005 cklee Bug# 4901292 |
247 -- PROCEDURE raise_business_event(x_return_status OUT NOCOPY VARCHAR2
248 PROCEDURE raise_business_event (
249 p_event_name IN VARCHAR2,
250 x_return_status OUT NOCOPY VARCHAR2
251 --END 21-Dec-2005 cklee Bug# 4901292 |
252 ) IS
253 l_process VARCHAR2 (20);
254 l_parameter_list wf_parameter_list_t;
255 BEGIN
256 l_process := okl_lla_util_pvt.get_contract_process (p_chr_id);
257 wf_event.addparametertolist (g_wf_itm_contract_id,
258 p_chr_id,
259 l_parameter_list
260 );
261 wf_event.addparametertolist (g_wf_itm_contract_process,
262 l_process,
263 l_parameter_list
264 );
265 okl_wf_pvt.raise_event (p_api_version => p_api_version,
266 p_init_msg_list => p_init_msg_list,
267 x_return_status => x_return_status,
268 x_msg_count => x_msg_count,
269 x_msg_data => x_msg_data,
270 --START 21-Dec-2005 cklee Bug# 4901292 |
271 -- p_event_name => G_WF_EVT_KHR_VALIDATED,
272 p_event_name => p_event_name,
273 --END 21-Dec-2005 cklee Bug# 4901292 |
274 p_parameters => l_parameter_list
275 );
276 EXCEPTION
277 WHEN OTHERS THEN
278 x_return_status := okl_api.g_ret_sts_unexp_error;
279 RAISE okl_api.g_exception_unexpected_error;
280 END raise_business_event;
281 /*
282 -- mvasudev, 08/30/2004
283 -- END, PROCEDURE to enable Business Event
284 */
285 BEGIN
286 x_return_status :=
287 okl_api.start_activity (p_api_name => l_api_name,
288 p_pkg_name => g_pkg_name,
289 p_init_msg_list => p_init_msg_list,
290 l_api_version => l_api_version,
291 p_api_version => p_api_version,
292 p_api_type => g_api_type,
293 x_return_status => x_return_status
294 );
295
296 -- check if activity started successfully
297 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
298 RAISE okl_api.g_exception_unexpected_error;
299 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
300 RAISE okl_api.g_exception_error;
301 END IF;
302
303 OPEN l_dltype_csr (p_chr_id);
304
305 FETCH l_dltype_csr
306 INTO l_dltype_rec;
307
308 IF (l_dltype_csr%NOTFOUND) THEN
309 RAISE okl_api.g_exception_unexpected_error;
310 END IF;
311
312 /* gboomina commenting for Bug Bug 6476425 - start
313 -- Revolving Loan contract status will be changed by stream
314 -- generation API after pricing the contract (similar to other
315 -- type contracts).
316 IF ( l_dltype_rec.deal_type = 'LOAN-REVOLVING') THEN
317 l_PassStatus := 'COMPLETE';
318 END IF;
319 gboomina commenting for Bug Bug 6476425 - end */
320
321 -- Initialize records in okl_book_controller_trx table
322 okl_book_controller_pvt.init_book_controller_trx
323 (p_api_version => p_api_version,
324 p_init_msg_list => p_init_msg_list,
325 x_return_status => x_return_status,
326 x_msg_count => x_msg_count,
327 x_msg_data => x_msg_data,
328 p_khr_id => p_chr_id,
329 x_batch_number => x_batch_number
330 );
331
332 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
333 RAISE okl_api.g_exception_unexpected_error;
334 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
335 RAISE okl_api.g_exception_error;
336 END IF;
337
338 /*
339 --call to cascade status on to lines
340 OKL_CONTRACT_STATUS_PVT_WIP.cascade_lease_status
341 (p_api_version => p_api_version,
342 p_init_msg_list => p_init_msg_list,
343 x_return_status => x_return_status,
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_chr_id => p_chr_id);
347
348 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
349 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
350 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
351 RAISE OKL_API.G_EXCEPTION_ERROR;
352 END IF;
353 */
354 l_template_yn := 'N';
355 l_template_type_code := 'XXX';
356
357 OPEN l_ptmpl_csr (p_chr_id => p_chr_id);
358
359 FETCH l_ptmpl_csr
360 INTO l_template_yn,
361 l_template_type_code;
362
363 CLOSE l_ptmpl_csr;
364
365 --Bug# 4874338
366 IF (l_template_yn = 'Y' AND l_template_type_code = 'PROGRAM')
367 OR (l_template_yn = 'Y' AND l_template_type_code = 'LEASEAPP') THEN
368 OPEN l_ptmpl_qcl_csr (p_chr_id => p_chr_id);
369
370 FETCH l_ptmpl_qcl_csr
371 INTO l_pqcl_id;
372
373 CLOSE l_ptmpl_qcl_csr;
374
375 okc_qa_check_pub.execute_qa_check_list
376 (p_api_version => p_api_version,
377 p_init_msg_list => p_init_msg_list,
378 x_return_status => x_return_status,
379 x_msg_count => x_msg_count,
380 x_msg_data => x_msg_data,
381 p_qcl_id => l_pqcl_id,
382 p_chr_id => p_chr_id,
383 x_msg_tbl => l_pmsg_tbl
384 );
385 ELSE
386 okl_qa_check_pub.execute_qa_check_list
387 (p_api_version => p_api_version,
388 p_init_msg_list => p_init_msg_list,
389 x_return_status => x_return_status,
390 x_msg_count => x_msg_count,
391 x_msg_data => x_msg_data,
392 p_qcl_id => p_qcl_id,
393 p_chr_id => p_chr_id,
394 p_call_mode => p_call_mode,
395 x_msg_tbl => x_msg_tbl
396 );
397 END IF;
398
399 -- Bug# 3477560 - Changed l_return_status to x_return_status
400 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
401 RAISE okl_api.g_exception_unexpected_error;
402 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
403 RAISE okl_api.g_exception_error;
404 END IF;
405
406 j := 0;
407
408 FOR i IN 1 .. x_msg_tbl.COUNT
409 LOOP
410 IF (x_msg_tbl (i).NAME <> 'CHECK Email Address') THEN
411 j := j + 1;
412 l_msg_tbl (j).severity := x_msg_tbl (i).severity;
413 l_msg_tbl (j).NAME := x_msg_tbl (i).NAME;
414 l_msg_tbl (j).description := x_msg_tbl (i).description;
415 l_msg_tbl (j).package_name := x_msg_tbl (i).package_name;
416 l_msg_tbl (j).procedure_name := x_msg_tbl (i).procedure_name;
417 l_msg_tbl (j).error_status := x_msg_tbl (i).error_status;
418 l_msg_tbl (j).DATA := x_msg_tbl (i).DATA;
419 END IF;
420 END LOOP;
421
422 --Bug# 4186455
423 /*
424 --FOR i IN 1..l_msg_tbl.COUNT
425 --LOOP
426 --IF (( l_msg_tbl(i).error_status = 'E' ) AND (INSTR(l_msg_tbl(i).data,'residual value IS less than 20') > 0)) THEN
427 --l_msg_tbl(i).error_status := 'W';
428 --END IF;
429 --END LOOP;
430 */
431 severity := 'S';
432
433 FOR i IN 1 .. l_msg_tbl.COUNT
434 LOOP
435 IF (l_msg_tbl (i).error_status = 'E') THEN
436 severity := 'E';
437 EXIT;
438 END IF;
439 END LOOP;
440
441 x_msg_tbl := l_msg_tbl;
442
443 IF (p_call_mode = 'ACTUAL') THEN
444 IF ( (x_return_status = okl_api.g_ret_sts_success)
445 AND (severity = 'S')
446 ) THEN
447 okl_contract_status_pub.update_contract_status (l_api_version,
448 p_init_msg_list,
449 x_return_status,
450 x_msg_count,
451 x_msg_data,
452 l_passstatus,
453 p_chr_id
454 );
455 okl_book_controller_pvt.update_book_controller_trx
456 (p_api_version => p_api_version,
457 p_init_msg_list => p_init_msg_list,
458 x_return_status => x_return_status,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_msg_data,
461 p_khr_id => p_chr_id,
462 p_prog_short_name => okl_book_controller_pvt.g_validate_contract,
463 p_progress_status => okl_book_controller_pvt.g_prog_sts_complete
464 );
465
466 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
467 RAISE okl_api.g_exception_unexpected_error;
468 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
469 RAISE okl_api.g_exception_error;
470 END IF;
471 ELSE
472 okl_contract_status_pub.update_contract_status (l_api_version,
473 p_init_msg_list,
474 x_return_status,
475 x_msg_count,
476 x_msg_data,
477 l_failstatus,
478 p_chr_id
479 );
480 okl_book_controller_pvt.update_book_controller_trx
481 (p_api_version => p_api_version,
482 p_init_msg_list => p_init_msg_list,
483 x_return_status => x_return_status,
484 x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data,
486 p_khr_id => p_chr_id,
487 p_prog_short_name => okl_book_controller_pvt.g_validate_contract,
488 p_progress_status => okl_book_controller_pvt.g_prog_sts_error
489 );
490
491 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
492 RAISE okl_api.g_exception_unexpected_error;
493 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
494 RAISE okl_api.g_exception_error;
495 END IF;
496 END IF;
497
498 --call to cascade status on to lines
499 okl_contract_status_pub.cascade_lease_status
500 (p_api_version => p_api_version,
501 p_init_msg_list => p_init_msg_list,
502 x_return_status => x_return_status,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data,
505 p_chr_id => p_chr_id
506 );
507
508 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
509 RAISE okl_api.g_exception_unexpected_error;
510 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
511 RAISE okl_api.g_exception_error;
512 END IF;
513
514 /*
515 -- mvasudev, 08/30/2004
516 -- Code change to enable Business Event
517 */
518 --START 21-Dec-2005 cklee Bug# 4901292 |
519 -- raise_business_event(x_return_status => x_return_status);
520 raise_business_event (p_event_name => g_wf_evt_khr_validated,
521 x_return_status => x_return_status
522 );
523
524 --END 21-Dec-2005 cklee Bug# 4901292 |
525 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
526 RAISE okl_api.g_exception_unexpected_error;
527 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
528 RAISE okl_api.g_exception_error;
529 END IF;
530
531 /*
532 -- mvasudev, 08/30/2004
533 -- END, Code change to enable Business Event
534 */
535
536 -- start: cklee okl.h: leaase app IA Authoring
537 -- update item function validation results
538 okl_checklist_pvt.update_checklist_function
539 (p_api_version => p_api_version,
540 p_init_msg_list => p_init_msg_list,
541 x_return_status => x_return_status,
542 x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data,
544 p_checklist_obj_id => p_chr_id
545 );
546
547 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
548 RAISE okc_api.g_exception_unexpected_error;
549 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
550 RAISE okc_api.g_exception_error;
551 END IF;
552
553 -- end: cklee okl.h: leaase app IA Authoring
554
555 /*
556 -- START 21-Dec-2005 cklee Bug# 4901292
557 -- Code change to enable Business Event
558 */
559 raise_business_event (p_event_name => g_wf_evt_chr_list_validated,
560 x_return_status => x_return_status
561 );
562
563 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
564 RAISE okl_api.g_exception_unexpected_error;
565 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
566 RAISE okl_api.g_exception_error;
567 END IF;
568 /*
569 -- 21-Dec-2005 cklee Bug# 4901292
570 -- END, Code change to enable Business Event
571 */
572 END IF;
573
574 okl_api.end_activity (x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data
576 );
577 ---
578 EXCEPTION
579 WHEN okl_api.g_exception_error THEN
580 x_return_status :=
581 okl_api.handle_exceptions
582 (p_api_name => l_api_name,
583 p_pkg_name => g_pkg_name,
584 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data,
587 p_api_type => g_api_type
588 );
589 WHEN okl_api.g_exception_unexpected_error THEN
590 x_return_status :=
591 okl_api.handle_exceptions
592 (p_api_name => l_api_name,
593 p_pkg_name => g_pkg_name,
594 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 p_api_type => g_api_type
598 );
599 WHEN OTHERS THEN
600 x_return_status :=
601 okl_api.handle_exceptions (p_api_name => l_api_name,
602 p_pkg_name => g_pkg_name,
603 p_exc_name => 'OTHERS',
604 x_msg_count => x_msg_count,
605 x_msg_data => x_msg_data,
606 p_api_type => g_api_type
607 );
608 END execute_qa_check_list;
609
610 PROCEDURE generate_journal_entries (
611 p_api_version IN NUMBER,
612 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
613 p_commit IN VARCHAR2 DEFAULT okl_api.g_false,
614 p_contract_id IN NUMBER,
615 p_transaction_type IN VARCHAR2,
616 p_draft_yn IN VARCHAR2 DEFAULT okc_api.g_true,
617 x_return_status OUT NOCOPY VARCHAR2,
618 x_msg_count OUT NOCOPY NUMBER,
619 x_msg_data OUT NOCOPY VARCHAR2
620 ) IS
621 l_api_name CONSTANT VARCHAR2 (30) := 'GENERATE_JNL_ENTRIES';
622 l_api_version CONSTANT NUMBER := 1;
623 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
624 l_isallowed BOOLEAN;
625 l_passstatus VARCHAR2 (256);
626 l_failstatus VARCHAR2 (256);
627
628 CURSOR l_rebooked_csr (chrid NUMBER) IS
629 SELECT date_transaction_occurred
630 FROM okl_trx_contracts trx, okl_trx_types_tl trx_type
631 WHERE trx.khr_id_old = chrid
632 AND trx.khr_id_new IS NOT NULL
633 AND trx.tsu_code = 'ENTERED'
634 AND trx.tcn_type = 'TRBK'
635 AND trx.rbr_code IS NOT NULL
636 AND trx_type.NAME = 'Rebook'
637 --rkuttiya added for 12.1.1 Multi GAAP
638 AND trx.representation_type = 'PRIMARY'
639 --
640 AND trx_type.LANGUAGE = 'US'
641 AND trx.try_id = trx_type.ID;
642
643 l_rebooked_rec l_rebooked_csr%ROWTYPE;
644 l_transaction_date DATE;
645 old_rec old_csr%ROWTYPE;
646 rbk_rec rbk_csr%ROWTYPE;
647
648 /*
649 -- mvasudev, 08/30/2004
650 -- Added PROCEDURE to enable Business Event
651 */
652 PROCEDURE raise_business_event (x_return_status OUT NOCOPY VARCHAR2) IS
653 l_process VARCHAR2 (20);
654 l_parameter_list wf_parameter_list_t;
655 BEGIN
656 l_process := okl_lla_util_pvt.get_contract_process (p_contract_id);
657 wf_event.addparametertolist (g_wf_itm_contract_id,
658 p_contract_id,
659 l_parameter_list
660 );
661 wf_event.addparametertolist (g_wf_itm_contract_process,
662 l_process,
663 l_parameter_list
664 );
665 okl_wf_pvt.raise_event (p_api_version => p_api_version,
666 p_init_msg_list => p_init_msg_list,
667 x_return_status => x_return_status,
668 x_msg_count => x_msg_count,
669 x_msg_data => x_msg_data,
670 p_event_name => g_wf_evt_khr_gen_journal,
671 p_parameters => l_parameter_list
672 );
673 EXCEPTION
674 WHEN OTHERS THEN
675 x_return_status := okl_api.g_ret_sts_unexp_error;
676 RAISE okl_api.g_exception_unexpected_error;
677 END raise_business_event;
678 /*
679 -- mvasudev, 08/30/2004
680 -- END, PROCEDURE to enable Business Event
681 */
682 BEGIN
683 x_return_status :=
684 okl_api.start_activity (p_api_name => l_api_name,
685 p_pkg_name => g_pkg_name,
686 p_init_msg_list => p_init_msg_list,
687 l_api_version => l_api_version,
688 p_api_version => p_api_version,
689 p_api_type => g_api_type,
690 x_return_status => x_return_status
691 );
692
693 -- check if activity started successfully
694 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
695 RAISE okl_api.g_exception_unexpected_error;
696 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
697 RAISE okl_api.g_exception_error;
698 END IF;
699
700 l_transaction_date := NULL;
701
702 IF (p_transaction_type = 'Rebook') THEN
703 OPEN l_rebooked_csr (p_contract_id);
704
705 FETCH l_rebooked_csr
706 INTO l_rebooked_rec;
707
708 CLOSE l_rebooked_csr;
709
710 l_transaction_date := l_rebooked_rec.date_transaction_occurred;
711 ELSIF (p_transaction_type = 'Booking') THEN
712 OPEN old_csr (p_contract_id);
713
714 FETCH old_csr
715 INTO old_rec;
716
717 IF (old_csr%FOUND) THEN
718 OPEN rbk_csr (old_rec.orig_system_id1, p_contract_id);
719
720 FETCH rbk_csr
721 INTO rbk_rec;
722
723 CLOSE rbk_csr;
724
725 l_transaction_date := rbk_rec.date_transaction_occurred;
726 END IF;
727
728 CLOSE old_csr;
729 END IF;
730
731 okl_la_je_pvt.generate_journal_entries (p_api_version,
732 p_init_msg_list,
733 p_commit,
734 p_contract_id,
735 p_transaction_type,
736 l_transaction_date,
737 p_draft_yn,
738 okl_api.g_true,
739 x_return_status,
740 x_msg_count,
741 x_msg_data
742 );
743
744 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
745 RAISE okl_api.g_exception_unexpected_error;
746 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
747 RAISE okl_api.g_exception_error;
748 END IF;
749
750 okl_api.set_message (p_app_name => g_app_name,
751 p_msg_name => 'OKL_LLA_JE_SUCCESS'
752 );
753 x_return_status := okl_api.g_ret_sts_success;
754 /*
755 -- mvasudev, 08/30/2004
756 -- Code change to enable Business Event
757 */
758 raise_business_event (x_return_status => x_return_status);
759
760 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
761 RAISE okl_api.g_exception_unexpected_error;
762 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
763 RAISE okl_api.g_exception_error;
764 END IF;
765
766 /*
767 -- mvasudev, 08/30/2004
768 -- END, Code change to enable Business Event
769 */
770 okl_api.end_activity (x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data
772 );
773 EXCEPTION
774 WHEN okl_api.g_exception_error THEN
775 x_return_status :=
776 okl_api.handle_exceptions
777 (p_api_name => l_api_name,
778 p_pkg_name => g_pkg_name,
779 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
780 x_msg_count => x_msg_count,
781 x_msg_data => x_msg_data,
782 p_api_type => g_api_type
783 );
784 WHEN okl_api.g_exception_unexpected_error THEN
785 x_return_status :=
786 okl_api.handle_exceptions
787 (p_api_name => l_api_name,
788 p_pkg_name => g_pkg_name,
789 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
790 x_msg_count => x_msg_count,
791 x_msg_data => x_msg_data,
792 p_api_type => g_api_type
793 );
794 WHEN OTHERS THEN
795 x_return_status :=
796 okl_api.handle_exceptions (p_api_name => l_api_name,
797 p_pkg_name => g_pkg_name,
798 p_exc_name => 'OTHERS',
799 x_msg_count => x_msg_count,
800 x_msg_data => x_msg_data,
801 p_api_type => g_api_type
802 );
803 END generate_journal_entries;
804
805 PROCEDURE generate_streams (
806 p_api_version IN NUMBER,
807 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
808 p_chr_id IN VARCHAR2,
809 p_generation_context IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_msg_count OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2,
813 x_trx_number OUT NOCOPY NUMBER,
814 x_trx_status OUT NOCOPY VARCHAR2
815 ) IS
816 l_api_name CONSTANT VARCHAR2 (30) := 'MAP_AND_GEN_STREAMS';
817 l_api_version CONSTANT NUMBER := 1;
818 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
819 l_isallowed BOOLEAN;
820 l_passstatus VARCHAR2 (256);
821 l_failstatus VARCHAR2 (256);
822
823 CURSOR tmp_csr (chrid NUMBER) IS
824 SELECT NVL (b.template_yn, 'N') template_yn
825 FROM okc_statuses_v a, okc_k_headers_b b, okl_k_headers c
826 WHERE a.code = b.sts_code AND b.ID = c.ID AND b.ID = chrid;
827
828 tmp_rec tmp_csr%ROWTYPE;
829
830 /*
831 -- mvasudev, 08/30/2004
832 -- Added PROCEDURE to enable Business Event
833 */
834 PROCEDURE raise_business_event (x_return_status OUT NOCOPY VARCHAR2) IS
835 l_process VARCHAR2 (20);
836 l_parameter_list wf_parameter_list_t;
837 BEGIN
838 l_process := okl_lla_util_pvt.get_contract_process (p_chr_id);
839 wf_event.addparametertolist (g_wf_itm_contract_id,
840 p_chr_id,
841 l_parameter_list
842 );
843 wf_event.addparametertolist (g_wf_itm_contract_process,
844 l_process,
845 l_parameter_list
846 );
847 okl_wf_pvt.raise_event (p_api_version => p_api_version,
848 p_init_msg_list => p_init_msg_list,
849 x_return_status => x_return_status,
850 x_msg_count => x_msg_count,
851 x_msg_data => x_msg_data,
852 p_event_name => g_wf_evt_khr_gen_strms,
853 p_parameters => l_parameter_list
854 );
855 EXCEPTION
856 WHEN OTHERS THEN
857 x_return_status := okl_api.g_ret_sts_unexp_error;
858 RAISE okl_api.g_exception_unexpected_error;
859 END raise_business_event;
860 /*
861 -- mvasudev, 08/30/2004
862 -- END, PROCEDURE to enable Business Event
863 */
864 BEGIN
865 x_return_status :=
866 okl_api.start_activity (p_api_name => l_api_name,
867 p_pkg_name => g_pkg_name,
868 p_init_msg_list => p_init_msg_list,
869 l_api_version => l_api_version,
870 p_api_version => p_api_version,
871 p_api_type => g_api_type,
872 x_return_status => x_return_status
873 );
874
875 -- check if activity started successfully
876 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
877 RAISE okl_api.g_exception_unexpected_error;
878 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
879 RAISE okl_api.g_exception_error;
880 END IF;
881
882 --Bug# 3556674
883 validate_chr_id (p_chr_id => p_chr_id,
884 x_return_status => x_return_status
885 );
886
887 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
888 RAISE okl_api.g_exception_unexpected_error;
889 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
890 RAISE okl_api.g_exception_error;
891 END IF;
892
893 --Bug# 3556674
894 OPEN tmp_csr (TO_NUMBER (p_chr_id));
895
896 FETCH tmp_csr
897 INTO tmp_rec;
898
899 CLOSE tmp_csr;
900
901 IF (tmp_rec.template_yn = 'Y') THEN
902 x_return_status := okl_api.g_ret_sts_error;
903 okl_api.set_message (p_app_name => g_app_name,
904 p_msg_name => 'OKL_LLA_NO_STRM_TMPLTC'
905 );
906 RAISE okl_api.g_exception_error;
907 END IF;
908
909 okl_contract_status_pub.get_contract_status (l_api_version,
910 p_init_msg_list,
911 x_return_status,
912 x_msg_count,
913 x_msg_data,
914 l_isallowed,
915 l_passstatus,
916 l_failstatus,
917 'STRMGEN',
918 p_chr_id
919 );
920
921 IF (l_isallowed = FALSE) THEN
922 x_return_status := okl_api.g_ret_sts_success;
923 okl_api.set_message (p_app_name => g_app_name,
924 p_msg_name => 'OKL_LLA_CTGEN_STRMS'
925 );
926 RAISE okl_api.g_exception_error;
927 END IF;
928
929 --Bug# 4023501: start - Phasing out Stream generation profile option
930 okl_la_stream_pub.gen_intr_extr_stream
931 (p_api_version => p_api_version,
932 p_init_msg_list => p_init_msg_list,
933 x_return_status => x_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_khr_id => TO_NUMBER
937 (p_chr_id
938 ),
939 p_generation_ctx_code => 'AUTH',
940 x_trx_number => x_trx_number,
941 x_trx_status => x_trx_status
942 );
943
944 -- check if activity started successfully
945 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
946 RAISE okl_api.g_exception_unexpected_error;
947 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
948 RAISE okl_api.g_exception_error;
949 END IF;
950
951 --Bug# 4023501: end
952 x_return_status := okl_api.g_ret_sts_success;
953 okl_api.set_message (p_app_name => g_app_name,
954 p_msg_name => 'OKL_LLA_ST_SUCCESS'
955 );
956 --raise SUCCESS_MESSAGE;
957
958 /*
959 -- mvasudev, 08/30/2004
960 -- Code change to enable Business Event
961 */
962 raise_business_event (x_return_status => x_return_status);
963
964 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
965 RAISE okl_api.g_exception_unexpected_error;
966 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
967 RAISE okl_api.g_exception_error;
968 END IF;
969
970 /*
971 -- mvasudev, 08/30/2004
972 -- END, Code change to enable Business Event
973 */
974 okl_api.end_activity (x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data
976 );
977 EXCEPTION
978 WHEN okl_api.g_exception_error THEN
979 x_return_status :=
980 okl_api.handle_exceptions
981 (p_api_name => l_api_name,
982 p_pkg_name => g_pkg_name,
983 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data,
986 p_api_type => g_api_type
987 );
988 WHEN okl_api.g_exception_unexpected_error THEN
989 x_return_status :=
990 okl_api.handle_exceptions
991 (p_api_name => l_api_name,
992 p_pkg_name => g_pkg_name,
993 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
994 x_msg_count => x_msg_count,
995 x_msg_data => x_msg_data,
996 p_api_type => g_api_type
997 );
998 WHEN OTHERS THEN
999 x_return_status :=
1000 okl_api.handle_exceptions (p_api_name => l_api_name,
1001 p_pkg_name => g_pkg_name,
1002 p_exc_name => 'OTHERS',
1003 x_msg_count => x_msg_count,
1004 x_msg_data => x_msg_data,
1005 p_api_type => g_api_type
1006 );
1007 END generate_streams;
1008
1009 ----------------------------------------------------------------------------
1010 --start of comments
1011 --API Name : Approve_Contract
1012 --Description : Called if the contract approval path profile option
1013 -- is set to 'NONE' or the approval process is called
1014 -- from Mass Rebook or Import Contract
1015 --Parameters : IN - pchr_id - Contract requiring Approval
1016 --History : 19-Nov-2003 avsingh Bug# 2566822 Created
1017 --end of comments
1018 -----------------------------------------------------------------------------
1019 PROCEDURE approve_contract (
1020 p_api_version IN NUMBER,
1021 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 x_msg_count OUT NOCOPY NUMBER,
1024 x_msg_data OUT NOCOPY VARCHAR2,
1025 p_chr_id IN VARCHAR2
1026 ) IS
1027 l_api_name CONSTANT VARCHAR2 (30) := 'APPROVE_CONTRACT';
1028 l_api_version CONSTANT NUMBER := 1.0;
1029 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
1030 l_isallowed BOOLEAN;
1031 l_passstatus VARCHAR2 (100) := 'APPROVED';
1032 l_failstatus VARCHAR2 (100) := 'PENDING_APPROVAL';
1033 l_event VARCHAR2 (100)
1034 := okl_contract_status_pub.g_k_submit4apprvl;
1035 l_process_id NUMBER;
1036 l_approval_path VARCHAR2 (30) DEFAULT 'NONE';
1037
1038 CURSOR l_sts_csr (chrid NUMBER) IS
1039 SELECT sts_code,
1040 NVL (orig_system_source_code, 'XXX') src_code
1041 FROM okc_k_headers_v
1042 WHERE ID = chrid;
1043
1044 l_sts_rec l_sts_csr%ROWTYPE;
1045 BEGIN
1046 l_return_status := okl_api.g_ret_sts_success;
1047 x_return_status :=
1048 okl_api.start_activity (p_api_name => l_api_name,
1049 p_pkg_name => g_pkg_name,
1050 p_init_msg_list => p_init_msg_list,
1051 l_api_version => l_api_version,
1052 p_api_version => p_api_version,
1053 p_api_type => g_api_type,
1054 x_return_status => x_return_status
1055 );
1056
1057 -- check if activity started successfully
1058 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1059 RAISE okl_api.g_exception_unexpected_error;
1060 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1061 RAISE okl_api.g_exception_error;
1062 END IF;
1063
1064 OPEN l_sts_csr (TO_NUMBER (p_chr_id));
1065
1066 FETCH l_sts_csr
1067 INTO l_sts_rec;
1068
1069 CLOSE l_sts_csr;
1070
1071 okl_contract_status_pub.get_contract_status (l_api_version,
1072 p_init_msg_list,
1073 x_return_status,
1074 x_msg_count,
1075 x_msg_data,
1076 l_isallowed,
1077 l_passstatus,
1078 l_failstatus,
1079 l_event,
1080 p_chr_id
1081 );
1082
1083 IF (l_isallowed = FALSE) THEN
1084 x_return_status := okl_api.g_ret_sts_success;
1085
1086 IF (l_sts_rec.sts_code = 'APPROVED') THEN
1087 okl_api.set_message (p_app_name => g_app_name,
1088 p_msg_name => 'OKL_LLA_ALRDY_APPRVD'
1089 );
1090 ELSE
1091 okl_api.set_message (p_app_name => g_app_name,
1092 p_msg_name => 'OKL_LLA_NOT_COMPLETE'
1093 );
1094 END IF;
1095
1096 RAISE okl_api.g_exception_error;
1097 END IF;
1098
1099 IF (l_return_status = okl_api.g_ret_sts_success) THEN
1100 --temp fix to set status to approved
1101 okl_contract_status_pub.update_contract_status (l_api_version,
1102 p_init_msg_list,
1103 x_return_status,
1104 x_msg_count,
1105 x_msg_data,
1106 'APPROVED',
1107 p_chr_id
1108 );
1109
1110 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1111 RAISE okl_api.g_exception_unexpected_error;
1112 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1113 RAISE okl_api.g_exception_error;
1114 END IF;
1115 ELSE
1116 okl_contract_status_pub.update_contract_status (l_api_version,
1117 p_init_msg_list,
1118 x_return_status,
1119 x_msg_count,
1120 x_msg_data,
1121 l_failstatus,
1122 p_chr_id
1123 );
1124
1125 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1126 RAISE okl_api.g_exception_unexpected_error;
1127 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1128 RAISE okl_api.g_exception_error;
1129 END IF;
1130 END IF;
1131
1132 --call to cascade status on to lines
1133 okl_contract_status_pub.cascade_lease_status
1134 (p_api_version => p_api_version,
1135 p_init_msg_list => p_init_msg_list,
1136 x_return_status => x_return_status,
1137 x_msg_count => x_msg_count,
1138 x_msg_data => x_msg_data,
1139 p_chr_id => p_chr_id
1140 );
1141
1142 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1143 RAISE okl_api.g_exception_unexpected_error;
1144 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1145 RAISE okl_api.g_exception_error;
1146 END IF;
1147
1148 ---
1149
1150 --call post approval process
1151 okl_contract_book_pvt.post_approval_process
1152 (p_api_version => p_api_version,
1153 p_init_msg_list => p_init_msg_list,
1154 x_return_status => x_return_status,
1155 x_msg_count => x_msg_count,
1156 x_msg_data => x_msg_data,
1157 p_chr_id => p_chr_id,
1158 p_call_mode => g_auto_approve
1159 );
1160
1161 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1162 RAISE okl_api.g_exception_unexpected_error;
1163 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1164 RAISE okl_api.g_exception_error;
1165 END IF;
1166
1167 ---
1168 okl_api.end_activity (x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data
1170 );
1171 EXCEPTION
1172 WHEN okl_api.g_exception_error THEN
1173 x_return_status :=
1174 okl_api.handle_exceptions
1175 (p_api_name => l_api_name,
1176 p_pkg_name => g_pkg_name,
1177 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1178 x_msg_count => x_msg_count,
1179 x_msg_data => x_msg_data,
1180 p_api_type => g_api_type
1181 );
1182 WHEN okl_api.g_exception_unexpected_error THEN
1183 x_return_status :=
1184 okl_api.handle_exceptions
1185 (p_api_name => l_api_name,
1186 p_pkg_name => g_pkg_name,
1187 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_data,
1190 p_api_type => g_api_type
1191 );
1192 WHEN OTHERS THEN
1193 x_return_status :=
1194 okl_api.handle_exceptions (p_api_name => l_api_name,
1195 p_pkg_name => g_pkg_name,
1196 p_exc_name => 'OTHERS',
1197 x_msg_count => x_msg_count,
1198 x_msg_data => x_msg_data,
1199 p_api_type => g_api_type
1200 );
1201 END approve_contract;
1202
1203 ----------------------------------------------------------------------------
1204 --start of comments
1205 --API Name : Submit_for_Approval
1206 --Description : Called from the contract booking page to initiate the approval
1207 -- process for the contract.
1208 --Parameters : IN - pchr_id - Contract requiring Approval
1209 --History : Bug# 2566822 - Integration with approval WF/AME
1210 -- will check the profile and choose appropriate approval
1211 -- patch
1212 --end of comments
1213 -----------------------------------------------------------------------------
1214 PROCEDURE submit_for_approval (
1215 p_api_version IN NUMBER,
1216 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
1217 x_return_status OUT NOCOPY VARCHAR2,
1218 x_msg_count OUT NOCOPY NUMBER,
1219 x_msg_data OUT NOCOPY VARCHAR2,
1220 p_chr_id IN VARCHAR2
1221 ) IS
1222 l_api_name CONSTANT VARCHAR2 (30) := 'SUBMIT_FOR_APPROVAL';
1223 l_api_version CONSTANT NUMBER := 1.0;
1224 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
1225 l_isallowed BOOLEAN;
1226 l_passstatus VARCHAR2 (100) := 'APPROVED';
1227 l_failstatus VARCHAR2 (100) := 'PENDING_APPROVAL';
1228 l_event VARCHAR2 (100)
1229 := okl_contract_status_pub.g_k_submit4apprvl;
1230 l_process_id NUMBER;
1231 l_approval_path VARCHAR2 (30) DEFAULT 'NONE';
1232
1233 CURSOR l_sts_csr (chrid NUMBER) IS
1234 SELECT sts_code,
1235 NVL (orig_system_source_code, 'XXX') src_code
1236 FROM okc_k_headers_v
1237 WHERE ID = chrid;
1238
1239 l_sts_rec l_sts_csr%ROWTYPE;
1240
1241 --Bug# 4502754
1242 --cursor to check for vendor program template
1243 CURSOR l_chk_template_csr (p_chr_id IN NUMBER) IS
1244 SELECT CHR.template_yn,
1245 khr.template_type_code
1246 FROM okc_k_headers_b CHR, okl_k_headers khr
1247 WHERE CHR.ID = p_chr_id AND CHR.ID = khr.ID;
1248
1249 l_chk_template_rec l_chk_template_csr%ROWTYPE;
1250
1251 /*
1252 -- mvasudev, 08/30/2004
1253 -- Added PROCEDURE to enable Business Event
1254 */
1255 -- START 21-Dec-2005 cklee Bug# 4901292
1256 -- PROCEDURE raise_business_event(x_return_status OUT NOCOPY VARCHAR2
1257 PROCEDURE raise_business_event (
1258 p_event_name IN VARCHAR2,
1259 x_return_status OUT NOCOPY VARCHAR2
1260 -- END 21-Dec-2005 cklee Bug# 4901292
1261 ) IS
1262 l_process VARCHAR2 (20);
1263 l_parameter_list wf_parameter_list_t;
1264 BEGIN
1265 l_process := okl_lla_util_pvt.get_contract_process (p_chr_id);
1266 wf_event.addparametertolist (g_wf_itm_contract_id,
1267 p_chr_id,
1268 l_parameter_list
1269 );
1270 wf_event.addparametertolist (g_wf_itm_contract_process,
1271 l_process,
1272 l_parameter_list
1273 );
1274 okl_wf_pvt.raise_event (p_api_version => p_api_version,
1275 p_init_msg_list => p_init_msg_list,
1276 x_return_status => x_return_status,
1277 x_msg_count => x_msg_count,
1278 x_msg_data => x_msg_data,
1279 -- START 21-Dec-2005 cklee Bug# 4901292
1280 -- p_event_name => G_WF_EVT_KHR_SUBMIT_APPR,
1281 p_event_name => p_event_name,
1282 -- START 21-Dec-2005 cklee Bug# 4901292
1283 p_parameters => l_parameter_list
1284 );
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 x_return_status := okl_api.g_ret_sts_unexp_error;
1288 RAISE okl_api.g_exception_unexpected_error;
1289 END raise_business_event;
1290 /*
1291 -- mvasudev, 08/30/2004
1292 -- END, PROCEDURE to enable Business Event
1293 */
1294 BEGIN
1295 l_return_status := okl_api.g_ret_sts_success;
1296 x_return_status :=
1297 okl_api.start_activity (p_api_name => l_api_name,
1298 p_pkg_name => g_pkg_name,
1299 p_init_msg_list => p_init_msg_list,
1300 l_api_version => l_api_version,
1301 p_api_version => p_api_version,
1302 p_api_type => g_api_type,
1303 x_return_status => x_return_status
1304 );
1305
1306 -- check if activity started successfully
1307 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1308 RAISE okl_api.g_exception_unexpected_error;
1309 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1310 RAISE okl_api.g_exception_error;
1311 END IF;
1312
1313 --Bug# 3556674
1314 validate_chr_id (p_chr_id => p_chr_id,
1315 x_return_status => x_return_status
1316 );
1317
1318 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1319 RAISE okl_api.g_exception_unexpected_error;
1320 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1321 RAISE okl_api.g_exception_error;
1322 END IF;
1323
1324 --Bug# 3556674
1325 OPEN l_sts_csr (TO_NUMBER (p_chr_id));
1326
1327 FETCH l_sts_csr
1328 INTO l_sts_rec;
1329
1330 CLOSE l_sts_csr;
1331
1332 okl_contract_status_pub.get_contract_status (l_api_version,
1333 p_init_msg_list,
1334 x_return_status,
1335 x_msg_count,
1336 x_msg_data,
1337 l_isallowed,
1338 l_passstatus,
1339 l_failstatus,
1340 l_event,
1341 p_chr_id
1342 );
1343
1344 IF (l_isallowed = FALSE) THEN
1345 x_return_status := okl_api.g_ret_sts_success;
1346
1347 IF (l_sts_rec.sts_code = 'APPROVED') THEN
1348 okl_api.set_message (p_app_name => g_app_name,
1349 p_msg_name => 'OKL_LLA_ALRDY_APPRVD'
1350 );
1351 ELSE
1352 --Bug# 4502754
1353 OPEN l_chk_template_csr (p_chr_id => p_chr_id);
1354
1355 FETCH l_chk_template_csr
1356 INTO l_chk_template_rec;
1357
1358 CLOSE l_chk_template_csr;
1359
1360 IF ( l_chk_template_rec.template_yn = 'Y'
1361 AND l_chk_template_rec.template_type_code = 'PROGRAM'
1362 )
1363 OR
1364 --Bug# 4874338
1365 ( l_chk_template_rec.template_yn = 'Y'
1366 AND l_chk_template_rec.template_type_code = 'LEASEAPP'
1367 ) THEN
1368 okl_api.set_message (p_app_name => g_app_name,
1369 p_msg_name => 'OKL_LLA_NOT_PASSED'
1370 );
1371 ELSE
1372 okl_api.set_message (p_app_name => g_app_name,
1373 p_msg_name => 'OKL_LLA_NOT_COMPLETE'
1374 );
1375 END IF;
1376 END IF;
1377
1378 RAISE okl_api.g_exception_error;
1379 END IF;
1380
1381 -- start: cklee okl.h: leaase app IA Authoring
1382 -- update item function validation results
1383 okl_checklist_pvt.update_checklist_function
1384 (p_api_version => p_api_version,
1385 p_init_msg_list => p_init_msg_list,
1386 x_return_status => x_return_status,
1387 x_msg_count => x_msg_count,
1388 x_msg_data => x_msg_data,
1389 p_checklist_obj_id => p_chr_id
1390 );
1391
1392 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
1393 RAISE okc_api.g_exception_unexpected_error;
1394 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
1395 RAISE okc_api.g_exception_error;
1396 END IF;
1397
1398 -- end: cklee okl.h: leaase app IA Authoring
1399
1400 /*
1401 -- START 21-Dec-2005 cklee Bug# 4901292
1402 -- Code change to enable Business Event
1403 */
1404 raise_business_event (p_event_name => g_wf_evt_chr_list_validated,
1405 x_return_status => x_return_status
1406 );
1407
1408 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1409 RAISE okl_api.g_exception_unexpected_error;
1410 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1411 RAISE okl_api.g_exception_error;
1412 END IF;
1413
1414 /*
1415 -- 21-Dec-2005 cklee Bug# 4901292
1416 -- END, Code change to enable Business Event
1417 */
1418
1419 --read profile for approval path
1420 l_approval_path :=
1421 fnd_profile.VALUE ('OKL_LEASE_CONTRACT_APPROVAL_PROCESS');
1422
1423 IF NVL (l_approval_path, 'NONE') = 'NONE' THEN
1424 -- Change Status
1425 IF (l_return_status = okl_api.g_ret_sts_success) THEN
1426 --temp fix to set status to approved
1427 okl_contract_status_pub.update_contract_status (l_api_version,
1428 p_init_msg_list,
1429 x_return_status,
1430 x_msg_count,
1431 x_msg_data,
1432 'APPROVED',
1433 p_chr_id
1434 );
1435
1436 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1437 RAISE okl_api.g_exception_unexpected_error;
1438 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1439 RAISE okl_api.g_exception_error;
1440 END IF;
1441 ELSE
1442 okl_contract_status_pub.update_contract_status (l_api_version,
1443 p_init_msg_list,
1444 x_return_status,
1445 x_msg_count,
1446 x_msg_data,
1447 l_failstatus,
1448 p_chr_id
1449 );
1450
1451 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1452 RAISE okl_api.g_exception_unexpected_error;
1453 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1454 RAISE okl_api.g_exception_error;
1455 END IF;
1456 END IF;
1457
1458 --call to cascade status on to lines
1459 okl_contract_status_pub.cascade_lease_status
1460 (p_api_version => p_api_version,
1461 p_init_msg_list => p_init_msg_list,
1462 x_return_status => x_return_status,
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_chr_id => p_chr_id
1466 );
1467
1468 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1469 RAISE okl_api.g_exception_unexpected_error;
1470 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1471 RAISE okl_api.g_exception_error;
1472 END IF;
1473
1474 ---
1475
1476 --call post approval process
1477 okl_contract_book_pvt.post_approval_process
1478 (p_api_version => p_api_version,
1479 p_init_msg_list => p_init_msg_list,
1480 x_return_status => x_return_status,
1481 x_msg_count => x_msg_count,
1482 x_msg_data => x_msg_data,
1483 p_chr_id => p_chr_id,
1484 p_call_mode => g_auto_approve
1485 );
1486
1487 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1488 RAISE okl_api.g_exception_unexpected_error;
1489 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1490 RAISE okl_api.g_exception_error;
1491 END IF;
1492 ---
1493 ELSIF NVL (l_approval_path, 'NONE') IN ('AME', 'WF') THEN
1494 okl_book_controller_pvt.update_book_controller_trx
1495 (p_api_version => p_api_version,
1496 p_init_msg_list => p_init_msg_list,
1497 x_return_status => x_return_status,
1498 x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data,
1500 p_khr_id => p_chr_id,
1501 p_prog_short_name => okl_book_controller_pvt.g_submit_contract,
1502 p_progress_status => okl_book_controller_pvt.g_prog_sts_running
1503 );
1504
1505 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1506 RAISE okl_api.g_exception_unexpected_error;
1507 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1508 RAISE okl_api.g_exception_error;
1509 END IF;
1510
1511 okl_kbk_approvals_wf.raise_approval_event
1512 (p_api_version => p_api_version,
1513 p_init_msg_list => p_init_msg_list,
1514 x_return_status => x_return_status,
1515 x_msg_count => x_msg_count,
1516 x_msg_data => x_msg_data,
1517 p_contract_id => p_chr_id
1518 );
1519
1520 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1521 RAISE okl_api.g_exception_unexpected_error;
1522 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1523 RAISE okl_api.g_exception_error;
1524 END IF;
1525 ---
1526 END IF;
1527
1528 /*
1529 -- mvasudev, 08/30/2004
1530 -- Code change to enable Business Event
1531 */
1532 -- START 21-Dec-2005 cklee Bug# 4901292
1533 raise_business_event (p_event_name => g_wf_evt_khr_submit_appr,
1534 x_return_status => x_return_status
1535 );
1536
1537 -- END 21-Dec-2005 cklee Bug# 4901292
1538 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1539 RAISE okl_api.g_exception_unexpected_error;
1540 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1541 RAISE okl_api.g_exception_error;
1542 END IF;
1543
1544 /*
1545 -- mvasudev, 08/30/2004
1546 -- END, Code change to enable Business Event
1547 */
1548 okl_api.end_activity (x_msg_count => x_msg_count,
1549 x_msg_data => x_msg_data
1550 );
1551 EXCEPTION
1552 WHEN okl_api.g_exception_error THEN
1553 x_return_status :=
1554 okl_api.handle_exceptions
1555 (p_api_name => l_api_name,
1556 p_pkg_name => g_pkg_name,
1557 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data,
1560 p_api_type => g_api_type
1561 );
1562 WHEN okl_api.g_exception_unexpected_error THEN
1563 x_return_status :=
1564 okl_api.handle_exceptions
1565 (p_api_name => l_api_name,
1566 p_pkg_name => g_pkg_name,
1567 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data,
1570 p_api_type => g_api_type
1571 );
1572 WHEN OTHERS THEN
1573 x_return_status :=
1574 okl_api.handle_exceptions (p_api_name => l_api_name,
1575 p_pkg_name => g_pkg_name,
1576 p_exc_name => 'OTHERS',
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data,
1579 p_api_type => g_api_type
1580 );
1581 END submit_for_approval;
1582
1583 ------------------------------------------------------------------
1584 --start of comments
1585 --API Name : post_approval_process
1586 --Description : Called by contract approval process after the contract
1587 -- is approved. Will be called by online approval or
1588 -- WF/AME after contract has been approved.
1589 --Parameters : IN - p_chr_id Varchar2 : Contract identifier
1590 --History : 19-Nov-2003 avsingh Bug# 2566822 Created
1591 -- by modifying original
1592 -- submit_for_Approval API
1593 --end of comments
1594 -------------------------------------------------------------------
1595 PROCEDURE post_approval_process (
1596 p_api_version IN NUMBER,
1597 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
1598 x_return_status OUT NOCOPY VARCHAR2,
1599 x_msg_count OUT NOCOPY NUMBER,
1600 x_msg_data OUT NOCOPY VARCHAR2,
1601 p_chr_id IN VARCHAR2,
1602 p_call_mode IN VARCHAR2 DEFAULT NULL
1603 ) IS
1604 l_api_name CONSTANT VARCHAR2 (30) := 'POST_APPROVAL_PROCESS';
1605 l_api_version CONSTANT NUMBER := 1.0;
1606 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
1607
1608 CURSOR l_sts_csr (chrid NUMBER) IS
1609 SELECT sts_code,
1610 NVL (orig_system_source_code, 'XXX') src_code
1611 FROM okc_k_headers_v
1612 WHERE ID = chrid;
1613
1614 l_sts_rec l_sts_csr%ROWTYPE;
1615
1616 CURSOR l_svclne_csr (ltycode VARCHAR2, chrid okl_k_headers.khr_id%TYPE) IS
1617 SELECT cle.ID,
1618 cle.price_negotiated amount
1619 FROM okc_k_lines_b cle, okc_line_styles_b ls, okc_statuses_b sts
1620 WHERE cle.lse_id = ls.ID
1621 AND ls.lty_code = ltycode
1622 AND cle.dnz_chr_id = chrid
1623 AND sts.code = cle.sts_code
1624 AND sts.ste_code NOT IN
1625 ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
1626
1627 x_link_line_tbl okl_service_integration_pub.link_line_tbl_type;
1628 x_service_contract_id NUMBER;
1629 l_svclne l_svclne_csr%ROWTYPE;
1630 i NUMBER;
1631 j NUMBER;
1632 n NUMBER;
1633
1634 CURSOR l_rl_csr1 (
1635 rgcode okc_rule_groups_b.rgd_code%TYPE,
1636 rlcat okc_rules_b.rule_information_category%TYPE,
1637 chrid NUMBER,
1638 cleid NUMBER
1639 ) IS
1640 SELECT crl.ID slh_id,
1641 crl.object1_id1,
1642 crl.rule_information1,
1643 crl.rule_information2,
1644 crl.rule_information3,
1645 crl.rule_information5,
1646 crl.rule_information6,
1647 crl.rule_information7,
1648 crl.rule_information10
1649 FROM okc_rule_groups_b crg, okc_rules_b crl
1650 WHERE crl.rgp_id = crg.ID
1651 AND crg.rgd_code = rgcode
1652 AND crl.rule_information_category = rlcat
1653 AND crg.dnz_chr_id = chrid
1654 AND NVL (crg.cle_id, -1) = cleid
1655 ORDER BY crl.rule_information1;
1656
1657 l_rl_rec1 l_rl_csr1%ROWTYPE;
1658 l_rl_rec2 l_rl_csr1%ROWTYPE;
1659
1660 --Bug# 3257595 : OKS Rules Migration
1661 CURSOR l_rl_oks_v10_csr (chrid NUMBER, cleid NUMBER) IS
1662 SELECT uom_code,
1663 sequence_no,
1664 start_date,
1665 level_periods,
1666 advance_periods,
1667 amount,
1668 invoice_offset_days,
1669 due_arr_yn
1670 FROM oks_stream_levels_b
1671 WHERE dnz_chr_id = chrid AND NVL (cle_id, -1) = cleid
1672 ORDER BY sequence_no;
1673
1674 l_rl_oks_v10_rec l_rl_oks_v10_csr%ROWTYPE;
1675
1676 --Bug# 3257595 : OKS Rules Migration
1677 CURSOR l_oks_csr (cleid NUMBER, dt DATE) IS
1678 SELECT schd.date_to_interface
1679 FROM okc_rules_v rule,
1680 okc_rule_groups_v rg,
1681 okc_k_lines_v line,
1682 oks_level_elements_v schd
1683 WHERE rg.ID = rule.rgp_id
1684 AND rg.cle_id = line.ID
1685 AND schd.rul_id = rule.ID
1686 AND line.ID = cleid
1687 --Bug# 3124577:11.5.10 Rule Migration
1688 AND rule.rule_information_category = 'LASLL'
1689 --AND rule.rule_information_category = 'SLL'
1690 AND schd.date_to_interface >= dt
1691 ORDER BY schd.date_to_interface;
1692
1693 l_oks_rec l_oks_csr%ROWTYPE;
1694
1695 --Bug# 3257597 : 11.5.10 OKS Rule Migration Impact
1696 CURSOR l_oks_v10_csr (cleid NUMBER, dt DATE) IS
1697 SELECT schd.date_to_interface
1698 FROM oks_level_elements_v schd, oks_stream_levels_b strm
1699 WHERE schd.rul_id = strm.ID
1700 AND strm.cle_id = cleid
1701 AND schd.date_to_interface >= dt
1702 ORDER BY schd.date_to_interface;
1703
1704 l_oks_v10_rec l_oks_v10_csr%ROWTYPE;
1705
1706 --Bug# 3257597 : 11.5.10 OKS Rule Migration Impact
1707 CURSOR l_finlne_csr (cleid NUMBER) IS
1708 SELECT object1_id1
1709 FROM okc_k_items
1710 WHERE cle_id = cleid;
1711
1712 l_finlne_rec l_finlne_csr%ROWTYPE;
1713
1714 -- nikshah -- Bug # 5484903 Fixed,
1715 -- Changed CURSOR l_check_date_csr SQL definition
1716 CURSOR l_check_date_csr (
1717 p_okl_free_form okc_k_lines_b.ID%TYPE,
1718 p_oks_cov_prod okc_k_lines_b.ID%TYPE
1719 ) IS
1720 SELECT 'Y' y
1721 FROM DUAL
1722 WHERE EXISTS (
1723 SELECT 'Y' y
1724 FROM okl_strm_elements_v ele,
1725 okl_streams_v strm,
1726 okl_strm_type_v strm_type
1727 WHERE strm.kle_id =
1728 p_okl_free_form
1729 --288266273543735169864512904074336514176
1730 AND strm.ID = ele.stm_id
1731 AND strm.sty_id = strm_type.ID
1732 AND strm_type.NAME = 'RENT'
1733 AND strm.say_code = 'CURR'
1734 AND strm.active_yn = 'Y'
1735 AND EXISTS (
1736 SELECT schd.date_to_interface
1737 FROM okc_rules_v rule,
1738 okc_rule_groups_v rg,
1739 okc_k_lines_v line,
1740 oks_level_elements_v schd
1741 WHERE rg.ID = rule.rgp_id
1742 AND rg.cle_id = line.ID
1743 AND schd.rul_id = rule.ID
1744 AND line.ID =
1745 p_oks_cov_prod
1746 -- 288176626842234160596172204397403418752
1747 AND rule.rule_information_category = 'SLL'
1748 AND schd.date_to_interface =
1749 ele.stream_element_date));
1750
1751 l_check_date_rec l_check_date_csr%ROWTYPE;
1752
1753 --Bug# 3257597 : 11.5.10 OKS Rules Migration Impact
1754 -- nikshah -- Bug # 5484903 Fixed
1755 -- Changed CURSOR l_check_date_v10_csr SQL definition
1756 CURSOR l_check_date_v10_csr (
1757 p_okl_free_form okc_k_lines_b.ID%TYPE,
1758 p_oks_cov_prod okc_k_lines_b.ID%TYPE
1759 ) IS
1760 SELECT 'Y' y
1761 FROM DUAL
1762 WHERE EXISTS (
1763 SELECT 'Y' y
1764 FROM okl_strm_elements_v ele,
1765 okl_streams_v strm,
1766 okl_strm_type_v strm_type
1767 WHERE strm.kle_id =
1768 p_okl_free_form
1769 --288266273543735169864512904074336514176
1770 AND strm.ID = ele.stm_id
1771 AND strm.sty_id = strm_type.ID
1772 --udhenuko bug 5665097 start Using Stream Type purpose instead of Name
1773 AND strm_type.stream_type_purpose = 'RENT'
1774 --udhenuko bug 5665097 end
1775 AND strm.say_code = 'CURR'
1776 AND strm.active_yn = 'Y'
1777 AND EXISTS (
1778 SELECT schd.date_to_interface
1779 FROM oks_level_elements_v schd,
1780 oks_stream_levels_b strm
1781 WHERE schd.rul_id = strm.ID
1782 AND strm.cle_id = p_oks_cov_prod
1783 AND schd.date_to_interface =
1784 ele.stream_element_date));
1785
1786 l_check_date_v10_rec l_check_date_v10_csr%ROWTYPE;
1787
1788 --Bug# 3257597 : 11.5.10 OKS Rules Migration Impact
1789 CURSOR l_name_csr (n VARCHAR2) IS
1790 SELECT NAME
1791 FROM okl_time_units_v
1792 WHERE id1 = n;
1793
1794 l_name_rec1 l_name_csr%ROWTYPE;
1795 l_name_rec2 l_name_csr%ROWTYPE;
1796
1797 --Bug# 3257597 : 11.5.10 OKS rule migration impact
1798 CURSOR l_chk_oks_rulemig_csr IS
1799 SELECT 'Y'
1800 FROM okc_class_operations
1801 WHERE cls_code = 'SERVICE' AND opn_code = 'CHECK_RULE';
1802
1803 l_oks_rulemig_exists VARCHAR2 (1) DEFAULT 'N';
1804 --Bug# 3257597 : 11.5.10 OKS rule migration impact
1805 l_approval_path VARCHAR2 (30) DEFAULT 'NONE';
1806 l_process_status VARCHAR2 (30);
1807
1808 --Cursor to check existence of contract trx records
1809 CURSOR c_book_ctrl_trx (p_khr_id IN NUMBER) IS
1810 SELECT 'Y'
1811 FROM okl_book_controller_trx
1812 WHERE khr_id = p_khr_id
1813 AND progress_status = 'PENDING'
1814 AND NVL (active_flag, 'N') = 'N';
1815
1816 l_exists VARCHAR2 (1) DEFAULT 'N';
1817 BEGIN
1818 l_return_status := okl_api.g_ret_sts_success;
1819 x_return_status :=
1820 okl_api.start_activity (p_api_name => l_api_name,
1821 p_pkg_name => g_pkg_name,
1822 p_init_msg_list => p_init_msg_list,
1823 l_api_version => l_api_version,
1824 p_api_version => p_api_version,
1825 p_api_type => g_api_type,
1826 x_return_status => x_return_status
1827 );
1828
1829 -- check if activity started successfully
1830 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1831 RAISE okl_api.g_exception_unexpected_error;
1832 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1833 RAISE okl_api.g_exception_error;
1834 END IF;
1835
1836 --Bug# 3257597 : 11.5.10 OKS Rules migration impact :
1837 l_oks_rulemig_exists := 'N';
1838
1839 OPEN l_chk_oks_rulemig_csr;
1840
1841 FETCH l_chk_oks_rulemig_csr
1842 INTO l_oks_rulemig_exists;
1843
1844 IF l_chk_oks_rulemig_csr%NOTFOUND THEN
1845 NULL;
1846 END IF;
1847
1848 CLOSE l_chk_oks_rulemig_csr;
1849
1850 --Bug# 3257597 : 11.5.10 OKS Rules migration impact :
1851 OPEN l_sts_csr (TO_NUMBER (p_chr_id));
1852
1853 FETCH l_sts_csr
1854 INTO l_sts_rec;
1855
1856 CLOSE l_sts_csr;
1857
1858 ------------------------------------------------------------------
1859 --Bug# 2566822 : following code which was part of original
1860 --submit_for_approval has been moved to the new submit_for_approval
1861 --API . So it is being commented
1862 /*---------------------------------------------------------------
1863 okl_contract_status_pub.get_contract_status( l_api_version,
1864 p_init_msg_list,
1865 x_return_status,
1866 x_msg_count,
1867 x_msg_data,
1868 l_isAllowed,
1869 l_PassStatus,
1870 l_FailStatus,
1871 l_event,
1872 p_chr_id );
1873
1874 if( l_isAllowed = FALSE ) then
1875 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1876
1877
1878 if ( l_sts_rec.sts_code = 'APPROVED') Then
1879 OKL_API.set_message(
1880 p_app_name => G_APP_NAME,
1881 p_msg_name => 'OKL_LLA_ALRDY_APPRVD');
1882 Else
1883 OKL_API.set_message(
1884 p_app_name => G_APP_NAME,
1885 p_msg_name => 'OKL_LLA_NOT_COMPLETE');
1886 End If;
1887
1888 RAISE OKL_API.G_EXCEPTION_ERROR;
1889 end if;
1890
1891 -- Change Status
1892 IF(l_return_status = Okl_Api.G_RET_STS_SUCCESS) THEN
1893 --temp fix to set status to approved
1894 okl_contract_status_pub.update_contract_status(
1895 l_api_version,
1896 p_init_msg_list,
1897 x_return_status,
1898 x_msg_count,
1899 x_msg_data,
1900 'APPROVED',
1901 p_chr_id );
1902 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1903 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1904 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1905 RAISE Okl_Api.G_EXCEPTION_ERROR;
1906 END IF;
1907 ELSE
1908 okl_contract_status_pub.update_contract_status(
1909 l_api_version,
1910 p_init_msg_list,
1911 x_return_status,
1912 x_msg_count,
1913 x_msg_data,
1914 l_failStatus,
1915 p_chr_id );
1916 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1917 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF (x_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1919 RAISE Okl_Api.G_EXCEPTION_ERROR;
1920 END IF;
1921 END IF;
1922
1923 --call to cascade status on to lines
1924 OKL_CONTRACT_STATUS_PUB.cascade_lease_status
1925 (p_api_version => p_api_version,
1926 p_init_msg_list => p_init_msg_list,
1927 x_return_status => x_return_status,
1928 x_msg_count => x_msg_count,
1929 x_msg_data => x_msg_data,
1930 p_chr_id => p_chr_id);
1931
1932 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1934 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1935 RAISE OKL_API.G_EXCEPTION_ERROR;
1936 END IF;
1937 ---
1938 --------------------------------------------------------------------------------*/
1939 --Bug# 2566822 : End of commented code
1940 ---------------------------------------------------------------------------------
1941 --Bug# 4478685 : commented
1942 /*---------------------------------------------------------------------------
1943 IF (( l_sts_rec.src_code = 'XXX') OR
1944 ( l_sts_rec.src_code = 'OKL_REBOOK' ) OR
1945 ( l_sts_rec.src_code = 'OKC_HDR' ) )THEN
1946 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
1947
1948
1949 COMMIT;
1950 x_return_status := OKL_API.START_ACTIVITY(
1951 p_api_name => l_api_name,
1952 p_pkg_name => g_pkg_name,
1953 p_init_msg_list => p_init_msg_list,
1954 l_api_version => l_api_version,
1955 p_api_version => p_api_version,
1956 p_api_type => G_API_TYPE,
1957 x_return_status => x_return_status);
1958
1959 -- check if activity started successfully
1960 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1961 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1962 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1963 RAISE OKL_API.G_EXCEPTION_ERROR;
1964 END IF;
1965
1966 END IF;
1967 -------------------------------------------------------------------*/
1968
1969 -- Bug# 3800843 - Changed p_api_version from '1.0' to p_api_version
1970 okl_service_integration_pub.get_service_link_line
1971 (p_api_version => p_api_version,
1972 p_init_msg_list => okl_api.g_false,
1973 x_return_status => x_return_status,
1974 x_msg_count => x_msg_count,
1975 x_msg_data => x_msg_data,
1976 p_lease_contract_id => p_chr_id,
1977 x_link_line_tbl => x_link_line_tbl,
1978 x_service_contract_id => x_service_contract_id
1979 );
1980
1981 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1982 RAISE okl_api.g_exception_unexpected_error;
1983 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1984 RAISE okl_api.g_exception_error;
1985 END IF;
1986
1987 j := 0;
1988
1989 FOR i IN 1 .. x_link_line_tbl.COUNT
1990 LOOP
1991 OPEN l_finlne_csr (x_link_line_tbl (i).okl_service_line_id);
1992
1993 FETCH l_finlne_csr
1994 INTO l_finlne_rec;
1995
1996 CLOSE l_finlne_csr;
1997
1998 --Bug# 3124577: 11.5.10 Rule Migration
1999 OPEN l_rl_csr1 ('LALEVL',
2000 'LASLL',
2001 p_chr_id,
2002 TO_NUMBER (l_finlne_rec.object1_id1)
2003 );
2004
2005 --OPEN l_rl_csr1( 'LALEVL', 'SLL', p_chr_id, to_NUMBER(l_finlne_rec.object1_id1));
2006 FETCH l_rl_csr1
2007 INTO l_rl_rec1;
2008
2009 CLOSE l_rl_csr1;
2010
2011 j := j + 1;
2012
2013 --Bug# 3257592 : 11.5.10 OKS Rule Migration impact
2014 IF l_oks_rulemig_exists = 'N' THEN
2015 FOR l_rl_rec2 IN
2016 l_rl_csr1 ('SVC_K',
2017 'SLL',
2018 x_service_contract_id,
2019 x_link_line_tbl (i).oks_service_line_id
2020 )
2021 LOOP
2022 IF ( ( UPPER (l_rl_rec2.object1_id1) = 'MTH'
2023 AND UPPER (l_rl_rec1.object1_id1) <> 'M'
2024 )
2025 OR ( UPPER (l_rl_rec2.object1_id1) = 'QRT'
2026 AND UPPER (l_rl_rec1.object1_id1) <> 'Q'
2027 )
2028 OR ( UPPER (l_rl_rec2.object1_id1) = 'YR'
2029 AND UPPER (l_rl_rec1.object1_id1) <> 'A'
2030 )
2031 ) THEN
2032 OPEN l_name_csr (UPPER (l_rl_rec1.object1_id1));
2033
2034 FETCH l_name_csr
2035 INTO l_name_rec1;
2036
2037 CLOSE l_name_csr;
2038
2039 IF (UPPER (l_rl_rec2.object1_id1) = 'MTH') THEN
2040 OPEN l_name_csr ('M');
2041 ELSIF (UPPER (l_rl_rec2.object1_id1) = 'QRT') THEN
2042 OPEN l_name_csr ('Q');
2043 ELSIF (UPPER (l_rl_rec2.object1_id1) = 'YR') THEN
2044 OPEN l_name_csr ('A');
2045 ELSE
2046 OPEN l_name_csr (UPPER (l_rl_rec2.object1_id1));
2047 END IF;
2048
2049 FETCH l_name_csr
2050 INTO l_name_rec2;
2051
2052 CLOSE l_name_csr;
2053
2054 okl_api.set_message (p_app_name => g_app_name,
2055 p_msg_name => 'OKL_LLA_SERV_PMNT_FREQ',
2056 p_token1 => 'PMNT_FREQ1',
2057 p_token1_value => l_name_rec2.NAME,
2058 p_token2 => 'PMNT_FREQ2',
2059 p_token2_value => l_name_rec1.NAME
2060 );
2061
2062 IF ( (l_sts_rec.src_code = 'XXX')
2063 OR (l_sts_rec.src_code = 'OKL_REBOOK')
2064 OR (l_sts_rec.src_code = 'OKC_HDR')
2065 ) THEN
2066 RAISE okl_api.g_exception_error;
2067 END IF;
2068 END IF;
2069 END LOOP;
2070 --Bug# 3257592 : 11.5.10 OKS Rule Migration impact
2071 ELSIF l_oks_rulemig_exists = 'Y' THEN
2072 FOR l_rl_oks_v10_rec IN
2073 l_rl_oks_v10_csr (x_service_contract_id,
2074 x_link_line_tbl (i).oks_service_line_id
2075 )
2076 LOOP
2077 IF ( ( UPPER (l_rl_oks_v10_rec.uom_code) = 'MTH'
2078 AND UPPER (l_rl_rec1.object1_id1) <> 'M'
2079 )
2080 OR ( UPPER (l_rl_oks_v10_rec.uom_code) = 'QRT'
2081 AND UPPER (l_rl_rec1.object1_id1) <> 'Q'
2082 )
2083 OR ( UPPER (l_rl_oks_v10_rec.uom_code) = 'YR'
2084 AND UPPER (l_rl_rec1.object1_id1) <> 'A'
2085 )
2086 ) THEN
2087 OPEN l_name_csr (UPPER (l_rl_rec1.object1_id1));
2088
2089 FETCH l_name_csr
2090 INTO l_name_rec1;
2091
2092 CLOSE l_name_csr;
2093
2094 IF (UPPER (l_rl_oks_v10_rec.uom_code) = 'MTH') THEN
2095 OPEN l_name_csr ('M');
2096 ELSIF (UPPER (l_rl_oks_v10_rec.uom_code) = 'QRT') THEN
2097 OPEN l_name_csr ('Q');
2098 ELSIF (UPPER (l_rl_oks_v10_rec.uom_code) = 'YR') THEN
2099 OPEN l_name_csr ('A');
2100 ELSE
2101 OPEN l_name_csr (UPPER (l_rl_oks_v10_rec.uom_code));
2102 END IF;
2103
2104 FETCH l_name_csr
2105 INTO l_name_rec2;
2106
2107 CLOSE l_name_csr;
2108
2109 okl_api.set_message (p_app_name => g_app_name,
2110 p_msg_name => 'OKL_LLA_SERV_PMNT_FREQ',
2111 p_token1 => 'PMNT_FREQ1',
2112 p_token1_value => l_name_rec2.NAME,
2113 p_token2 => 'PMNT_FREQ2',
2114 p_token2_value => l_name_rec1.NAME
2115 );
2116
2117 IF ( (l_sts_rec.src_code = 'XXX')
2118 OR (l_sts_rec.src_code = 'OKL_REBOOK')
2119 OR (l_sts_rec.src_code = 'OKC_HDR')
2120 ) THEN
2121 RAISE okl_api.g_exception_error;
2122 END IF;
2123 END IF;
2124 END LOOP;
2125 END IF;
2126
2127 --Bug# 3257592 End.
2128
2129 --Bug# 3257592 : 11.5.10 OKS rules migration impacts
2130 IF l_oks_rulemig_exists = 'N' THEN
2131 OPEN l_check_date_csr (TO_NUMBER (l_finlne_rec.object1_id1),
2132 x_link_line_tbl (i).oks_service_line_id
2133 );
2134
2135 FETCH l_check_date_csr
2136 INTO l_check_date_rec;
2137
2138 CLOSE l_check_date_csr;
2139
2140 -- nikshah -- Bug # 5484903 start, replaced with new IF condition
2141 IF (NVL (l_check_date_rec.y, 'X') <> 'Y') THEN
2142 -- nikshah -- Bug # 5484903 end
2143 okl_api.set_message (p_app_name => g_app_name,
2144 p_msg_name => 'OKL_LLA_SERV_SCHDT_DATE'
2145 );
2146
2147 IF ( (l_sts_rec.src_code = 'XXX')
2148 OR (l_sts_rec.src_code = 'OKL_REBOOK')
2149 OR (l_sts_rec.src_code = 'OKC_HDR')
2150 ) THEN
2151 RAISE okl_api.g_exception_error;
2152 END IF;
2153 END IF;
2154 ELSIF l_oks_rulemig_exists = 'Y' THEN
2155 OPEN l_check_date_v10_csr (TO_NUMBER (l_finlne_rec.object1_id1),
2156 x_link_line_tbl (i).oks_service_line_id
2157 );
2158
2159 FETCH l_check_date_v10_csr
2160 INTO l_check_date_v10_rec;
2161
2162 CLOSE l_check_date_v10_csr;
2163
2164 -- nikshah -- Bug # 5484903 start, replaced with new IF condition
2165 IF (NVL (l_check_date_v10_rec.y, 'X') <> 'Y') THEN
2166 -- nikshah -- Bug # 5484903 end
2167 okl_api.set_message (p_app_name => g_app_name,
2168 p_msg_name => 'OKL_LLA_SERV_SCHDT_DATE'
2169 );
2170
2171 IF ( (l_sts_rec.src_code = 'XXX')
2172 OR (l_sts_rec.src_code = 'OKL_REBOOK')
2173 OR (l_sts_rec.src_code = 'OKC_HDR')
2174 ) THEN
2175 RAISE okl_api.g_exception_error;
2176 END IF;
2177 END IF;
2178 END IF;
2179 --Bug# 3257593 End.
2180 END LOOP;
2181
2182 --Call contract activation if approval path is AME or WF and approval is complete
2183 --Do not call contract activation if the approval path is NONE or if the contract is
2184 --being auto-approved in Mass Rebook or Import flow
2185 IF (p_call_mode = g_auto_approve) THEN
2186 NULL;
2187 ELSE
2188 -- Open the cursor to see Batch or Online Booking
2189 OPEN c_book_ctrl_trx (p_khr_id => p_chr_id);
2190
2191 FETCH c_book_ctrl_trx
2192 INTO l_exists;
2193
2194 CLOSE c_book_ctrl_trx;
2195
2196 IF (l_exists = 'Y') THEN
2197 okl_book_controller_pvt.submit_controller_prg2
2198 (p_api_version => p_api_version,
2199 p_init_msg_list => p_init_msg_list,
2200 x_return_status => x_return_status,
2201 x_msg_count => x_msg_count,
2202 x_msg_data => x_msg_data,
2203 p_khr_id => p_chr_id
2204 );
2205
2206 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2207 RAISE okl_api.g_exception_unexpected_error;
2208 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2209 RAISE okl_api.g_exception_error;
2210 END IF;
2211 ELSE
2212 okl_contract_book_pvt.approve_activate_contract
2213 (p_api_version => p_api_version,
2214 p_init_msg_list => p_init_msg_list,
2215 x_return_status => x_return_status,
2216 x_msg_count => x_msg_count,
2217 x_msg_data => x_msg_data,
2218 p_chr_id => p_chr_id,
2219 x_process_status => l_process_status
2220 );
2221
2222 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2223 RAISE okl_api.g_exception_unexpected_error;
2224 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2225 RAISE okl_api.g_exception_error;
2226 END IF;
2227 END IF;
2228 END IF;
2229
2230 okl_api.end_activity (x_msg_count => x_msg_count,
2231 x_msg_data => x_msg_data
2232 );
2233 EXCEPTION
2234 WHEN okl_api.g_exception_error THEN
2235 x_return_status :=
2236 okl_api.handle_exceptions
2237 (p_api_name => l_api_name,
2238 p_pkg_name => g_pkg_name,
2239 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2240 x_msg_count => x_msg_count,
2241 x_msg_data => x_msg_data,
2242 p_api_type => g_api_type
2243 );
2244 WHEN okl_api.g_exception_unexpected_error THEN
2245 x_return_status :=
2246 okl_api.handle_exceptions
2247 (p_api_name => l_api_name,
2248 p_pkg_name => g_pkg_name,
2249 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2250 x_msg_count => x_msg_count,
2251 x_msg_data => x_msg_data,
2252 p_api_type => g_api_type
2253 );
2254 WHEN OTHERS THEN
2255 x_return_status :=
2256 okl_api.handle_exceptions (p_api_name => l_api_name,
2257 p_pkg_name => g_pkg_name,
2258 p_exc_name => 'OTHERS',
2259 x_msg_count => x_msg_count,
2260 x_msg_data => x_msg_data,
2261 p_api_type => g_api_type
2262 );
2263 END post_approval_process;
2264
2265 --Bug# 3948361 - Transfer and assumption changes
2266 -------------------------------------------------------------------------------
2267 ---------------------Terminate Original Contract-------------------------------
2268 -------------------------------------------------------------------------------
2269 PROCEDURE terminate_original_contract (
2270 p_api_version IN NUMBER,
2271 p_init_msg_list IN VARCHAR2
2272 DEFAULT okl_api.g_false,
2273 x_return_status OUT NOCOPY VARCHAR2,
2274 x_msg_count OUT NOCOPY NUMBER,
2275 x_msg_data OUT NOCOPY VARCHAR2,
2276 p_chr_id IN okc_k_headers_b.ID%TYPE,
2277 x_termination_complete_yn OUT NOCOPY VARCHAR2
2278 ) IS
2279 l_api_name VARCHAR2 (35) := 'TERM_ORIGINAL_CONTRACT';
2280 l_proc_name VARCHAR2 (35) := 'TERM_ORIGINAL_CONTRACT';
2281 ln_orig_system_source_code okc_k_headers_b.orig_system_source_code%TYPE;
2282 ln_orig_system_id1 okc_k_headers_b.orig_system_id1%TYPE;
2283 ln_orig_contract_number okc_k_headers_b.contract_number%TYPE;
2284
2285 -- To get the orig system id for p_chr_id
2286 CURSOR get_orig_sys_code (p_chr_id okc_k_headers_b.ID%TYPE) IS
2287 SELECT chr_new.orig_system_source_code,
2288 chr_new.orig_system_id1,
2289 chr_old.contract_number
2290 FROM okc_k_headers_b chr_new, okc_k_headers_b chr_old
2291 WHERE chr_new.ID = p_chr_id AND chr_old.ID = chr_new.orig_system_id1;
2292
2293 l_assn_tbl okl_am_create_quote_pvt.assn_tbl_type;
2294 l_assn_rec okl_am_create_quote_pvt.assn_rec_type;
2295 l_quot_rec okl_am_create_quote_pvt.quot_rec_type;
2296 l_tqlv_tbl okl_am_create_quote_pvt.tqlv_tbl_type;
2297 l_qpyv_tbl okl_am_create_quote_pvt.qpyv_tbl_type;
2298 x_quot_rec okl_am_create_quote_pvt.quot_rec_type;
2299 x_tqlv_tbl okl_am_create_quote_pvt.tqlv_tbl_type;
2300 x_qpyv_tbl okl_am_create_quote_pvt.qpyv_tbl_type;
2301 x_assn_tbl okl_am_create_quote_pvt.assn_tbl_type;
2302 l_term_rec okl_am_termnt_quote_pvt.term_rec_type;
2303 x_term_rec okl_am_termnt_quote_pvt.term_rec_type;
2304 x_err_msg VARCHAR2 (2000);
2305
2306 CURSOR taa_trx_csr (p_orig_chr_id IN NUMBER, p_new_chr_id IN NUMBER) IS
2307 SELECT tcn.ID,
2308 tcn.source_trx_id,
2309 tcn.date_transaction_occurred,
2310 tcn.qte_id
2311 FROM okl_trx_contracts tcn, okl_trx_types_tl try
2312 WHERE tcn.khr_id_old = p_orig_chr_id
2313 AND tcn.khr_id_new = p_new_chr_id
2314 AND tcn_type = 'MAE'
2315 AND tcn.tsu_code <> 'PROCESSED'
2316 AND tcn.try_id = try.ID
2317 --rkuttiya added for 12.1.1 Multi GAAP Project
2318 AND tcn.representation_type = 'PRIMARY'
2319 --
2320 AND try.NAME = 'Release'
2321 AND try.LANGUAGE = 'US';
2322
2323 taa_trx_rec taa_trx_csr%ROWTYPE;
2324
2325 CURSOR taa_term_assets_csr (
2326 p_orig_chr_id IN NUMBER,
2327 p_source_trx_id IN NUMBER
2328 ) IS
2329 SELECT fin_ast_cle.ID asset_id,
2330 fab.asset_number asset_number,
2331 fab.current_units current_units
2332 FROM okl_txl_cntrct_lns tcl,
2333 okc_k_lines_b fin_ast_cle,
2334 okc_k_lines_b fa_cle,
2335 okc_line_styles_b fa_lse,
2336 okc_k_items cim,
2337 fa_additions_b fab
2338 WHERE tcl.tcn_id = p_source_trx_id
2339 AND tcl.before_transfer_yn = 'N'
2340 AND fin_ast_cle.chr_id = p_orig_chr_id
2341 AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
2342 AND fin_ast_cle.ID = tcl.kle_id
2343 AND fa_cle.dnz_chr_id = fin_ast_cle.chr_id
2344 AND fa_cle.cle_id = fin_ast_cle.ID
2345 AND fa_cle.lse_id = fa_lse.ID
2346 AND fa_lse.lty_code = 'FIXED_ASSET'
2347 AND cim.cle_id = fa_cle.ID
2348 AND cim.dnz_chr_id = fa_cle.dnz_chr_id
2349 AND fab.asset_id = cim.object1_id1;
2350
2351 CURSOR chr_term_assets_csr (p_orig_chr_id IN NUMBER) IS
2352 SELECT fin_ast_cle.ID asset_id,
2353 fab.asset_number asset_number,
2354 fab.current_units current_units
2355 FROM okc_k_lines_b fin_ast_cle,
2356 okc_k_lines_b fa_cle,
2357 okc_k_headers_b CHR,
2358 okc_line_styles_b fin_ast_lse,
2359 okc_line_styles_b fa_lse,
2360 okc_k_items cim,
2361 fa_additions_b fab
2362 WHERE CHR.ID = p_orig_chr_id
2363 AND fin_ast_cle.chr_id = CHR.ID
2364 AND fin_ast_cle.dnz_chr_id = CHR.ID
2365 AND fin_ast_cle.sts_code = CHR.sts_code
2366 AND fin_ast_cle.lse_id = fin_ast_lse.ID
2367 AND fin_ast_lse.lty_code = 'FREE_FORM1'
2368 AND fa_cle.dnz_chr_id = fin_ast_cle.chr_id
2369 AND fa_cle.cle_id = fin_ast_cle.ID
2370 AND fa_cle.lse_id = fa_lse.ID
2371 AND fa_lse.lty_code = 'FIXED_ASSET'
2372 AND cim.cle_id = fa_cle.ID
2373 AND cim.dnz_chr_id = fa_cle.dnz_chr_id
2374 AND fab.asset_id = cim.object1_id1;
2375
2376 CURSOR chk_taa_term_csr (
2377 p_orig_chr_id IN NUMBER,
2378 p_source_trx_id IN NUMBER
2379 ) IS
2380 SELECT fin_ast_cle.ID,
2381 fin_ast_cle.sts_code
2382 FROM okl_txl_cntrct_lns tcl, okc_k_lines_b fin_ast_cle
2383 WHERE tcl.tcn_id = p_source_trx_id
2384 AND tcl.before_transfer_yn = 'N'
2385 AND fin_ast_cle.chr_id = p_orig_chr_id
2386 AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
2387 AND fin_ast_cle.ID = tcl.kle_id
2388 AND fin_ast_cle.sts_code <> 'TERMINATED';
2389
2390 chk_taa_term_rec chk_taa_term_csr%ROWTYPE;
2391
2392 CURSOR chk_chr_term_csr (p_orig_chr_id IN NUMBER) IS
2393 SELECT CHR.sts_code
2394 FROM okc_k_headers_b CHR
2395 WHERE ID = p_orig_chr_id;
2396
2397 chk_chr_term_rec chk_chr_term_csr%ROWTYPE;
2398
2399 CURSOR quote_num_csr (p_qte_id IN NUMBER) IS
2400 SELECT quote_number
2401 FROM okl_trx_quotes_b
2402 WHERE ID = p_qte_id;
2403
2404 quote_num_rec quote_num_csr%ROWTYPE;
2405 i NUMBER;
2406 l_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
2407 l_out_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
2408 l_termination_complete VARCHAR2 (30);
2409
2410 --Bug# 4061058
2411 CURSOR taa_request_csr (p_source_trx_id IN NUMBER) IS
2412 SELECT complete_transfer_yn
2413 FROM okl_trx_contracts
2414 WHERE ID = p_source_trx_id;
2415
2416 taa_request_rec taa_request_csr%ROWTYPE;
2417
2418 --Bug# 4072796
2419 CURSOR termination_trx_csr (p_qte_id IN NUMBER, p_khr_id IN NUMBER) IS
2420 --Bug# 6504515
2421 --SELECT tsu_code
2422 SELECT tmt_status_code
2423 FROM okl_trx_contracts
2424 WHERE qte_id = p_qte_id
2425 AND khr_id = p_khr_id
2426 AND tcn_type IN ('ALT', 'TMT')
2427 --rkuttiya added for 12.1.1 Multi GAAP Project
2428 AND representation_type = 'PRIMARY';
2429 --
2430
2431 termination_trx_rec termination_trx_csr%ROWTYPE;
2432 --Bug# 4515347:
2433 l_total_count NUMBER;
2434 l_error_count NUMBER;
2435 l_processed_count NUMBER;
2436
2437 --Bug# 4631549
2438 CURSOR off_lease_ast_csr (p_orig_chr_id IN NUMBER) IS
2439 SELECT fin_ast_cle.ID
2440 FROM okc_k_lines_b fin_ast_cle, okc_line_styles_b fin_ast_lse
2441 WHERE fin_ast_cle.chr_id = p_orig_chr_id
2442 AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
2443 AND fin_ast_cle.lse_id = fin_ast_lse.ID
2444 AND fin_ast_lse.lty_code = 'FREE_FORM1'
2445 AND fin_ast_cle.sts_code = 'TERMINATED';
2446
2447 --Bug# 4631549 : modified to calcel hold period trx
2448 CURSOR chk_off_lease_csr (p_orig_chr_id IN NUMBER) IS
2449 SELECT tas.ID,
2450 tas.tsu_code,
2451 txl.hold_period_days
2452 FROM okc_k_lines_b fin_ast_cle,
2453 okc_line_styles_b fin_ast_lse,
2454 okl_trx_assets tas,
2455 okl_txl_assets_b txl
2456 WHERE fin_ast_cle.chr_id = p_orig_chr_id
2457 AND fin_ast_cle.dnz_chr_id = p_orig_chr_id
2458 AND fin_ast_cle.lse_id = fin_ast_lse.ID
2459 AND fin_ast_lse.lty_code = 'FREE_FORM1'
2460 AND fin_ast_cle.sts_code = 'TERMINATED'
2461 AND txl.kle_id = fin_ast_cle.ID
2462 AND tas.ID = txl.tas_id
2463 AND tas.tas_type IN ('AMT', 'AUD', 'AUS');
2464
2465 --Bug# 4631549
2466 --AND tas.tsu_code <> 'PROCESSED';
2467 chk_off_lease_rec chk_off_lease_csr%ROWTYPE;
2468 --Bug# 4631549
2469 l_tasv_rec okl_trx_assets_pub.thpv_rec_type;
2470 lx_tasv_rec okl_trx_assets_pub.thpv_rec_type;
2471 -- akrangan added for debug feature start
2472 l_module_name VARCHAR2 (500)
2473 := g_module_name || 'terminate_original_contract';
2474 is_debug_exception_on BOOLEAN
2475 := okl_debug_pub.check_log_on (l_module_name, g_level_exception);
2476 is_debug_procedure_on BOOLEAN
2477 := okl_debug_pub.check_log_on (l_module_name, g_level_procedure);
2478 is_debug_statement_on BOOLEAN
2479 := okl_debug_pub.check_log_on (l_module_name, g_level_statement);
2480 -- akrangan added for debug feature end
2481 BEGIN
2482 IF (is_debug_procedure_on) THEN
2483 okl_debug_pub.log_debug (g_level_procedure,
2484 l_module_name,
2485 'Begin(+)'
2486 );
2487 END IF;
2488
2489 IF (is_debug_statement_on) THEN
2490 okl_debug_pub.log_debug (g_level_statement,
2491 l_module_name,
2492 'p_chr_id = ' || p_chr_id
2493 );
2494 END IF;
2495
2496 x_termination_complete_yn := 'Y';
2497 x_return_status := okl_api.g_ret_sts_success;
2498 -- Call start_activity to create savepoint, check compatibility
2499 -- and initialize message list
2500 x_return_status :=
2501 okl_api.start_activity (l_api_name,
2502 p_init_msg_list,
2503 '_PVT',
2504 x_return_status
2505 );
2506
2507 -- Check if activity started successfully
2508 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2509 RAISE okl_api.g_exception_unexpected_error;
2510 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2511 RAISE okl_api.g_exception_error;
2512 END IF;
2513
2514 -- To get the orig system id for
2515 OPEN get_orig_sys_code (p_chr_id => p_chr_id);
2516
2517 FETCH get_orig_sys_code
2518 INTO ln_orig_system_source_code,
2519 ln_orig_system_id1,
2520 ln_orig_contract_number;
2521
2522 IF get_orig_sys_code%NOTFOUND THEN
2523 okl_api.set_message (p_app_name => g_app_name,
2524 p_msg_name => 'OKL_LLA_NO_MATCHING_RECORD',
2525 p_token1 => g_col_name_token,
2526 p_token1_value => 'OKC_K_HEADERS_V.ID'
2527 );
2528 RAISE okl_api.g_exception_error;
2529 END IF;
2530
2531 CLOSE get_orig_sys_code;
2532
2533 IF ln_orig_system_source_code = 'OKL_RELEASE' THEN
2534 -- Terminate original contract
2535 OPEN taa_trx_csr (p_orig_chr_id => ln_orig_system_id1,
2536 p_new_chr_id => p_chr_id
2537 );
2538
2539 FETCH taa_trx_csr
2540 INTO taa_trx_rec;
2541
2542 CLOSE taa_trx_csr;
2543
2544 -- If Termination quote does not exist, initiate Termination process
2545 IF taa_trx_rec.qte_id IS NULL THEN
2546 -- Bug# 4072796
2547 -- Do Re-lease contract validations prior to initiating
2548 -- Termination
2549 IF (is_debug_statement_on) THEN
2550 okl_debug_pub.log_debug
2551 (g_level_statement,
2552 l_module_name,
2553 'BEFORE OKL_RELEASE_PVT.VALIDATE_RELEASE_CONTRACT CALL'
2554 );
2555 okl_debug_pub.log_debug (g_level_statement,
2556 l_module_name,
2557 'p_chr_id =' || ln_orig_system_id1
2558 );
2559 okl_debug_pub.log_debug (g_level_statement,
2560 l_module_name,
2561 'p_release_date='
2562 || taa_trx_rec.date_transaction_occurred
2563 );
2564 okl_debug_pub.log_debug (g_level_statement,
2565 l_module_name,
2566 'p_source_trx_id='
2567 || taa_trx_rec.source_trx_id
2568 );
2569 END IF;
2570
2571 okl_release_pvt.validate_release_contract
2572 (p_api_version => p_api_version,
2573 p_init_msg_list => p_init_msg_list,
2574 x_return_status => x_return_status,
2575 x_msg_count => x_msg_count,
2576 x_msg_data => x_msg_data,
2577 p_chr_id => ln_orig_system_id1,
2578 p_release_date => taa_trx_rec.date_transaction_occurred,
2579 p_source_trx_id => taa_trx_rec.source_trx_id,
2580 p_call_program => 'ACTIVATE'
2581 );
2582
2583 IF (is_debug_statement_on) THEN
2584 okl_debug_pub.log_debug
2585 (g_level_statement,
2586 l_module_name,
2587 'AFTER OKL_RELEASE_PVT.VALIDATE_RELEASE_CONTRACT CALL'
2588 );
2589 okl_debug_pub.log_debug (g_level_statement,
2590 l_module_name,
2591 'x_return_status =' || x_return_status
2592 );
2593 END IF;
2594
2595 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2596 RAISE okl_api.g_exception_unexpected_error;
2597 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2598 RAISE okl_api.g_exception_error;
2599 END IF;
2600
2601 l_quot_rec.khr_id := ln_orig_system_id1;
2602 l_quot_rec.qtp_code := 'TER_RELEASE_WO_PURCHASE';
2603 l_quot_rec.date_effective_from :=
2604 taa_trx_rec.date_transaction_occurred - 1;
2605
2606 -- If Transfer and Assumption transaction then
2607 -- fetch asset lines to be terminated from the
2608 -- T and A request
2609 IF (taa_trx_rec.source_trx_id IS NOT NULL) THEN
2610 --Bug# 4478685
2611 l_quot_rec.qrs_code := 'TRANSFER_ASSUMPTION';
2612
2613 IF (is_debug_statement_on) THEN
2614 okl_debug_pub.log_debug
2615 (g_level_statement,
2616 l_module_name,
2617 'before OKL_AM_CREATE_QUOTE_PUB.create_terminate_quote CALL'
2618 );
2619 okl_debug_pub.log_debug (g_level_statement,
2620 l_module_name,
2621 'l_quot_rec.khr_id ='
2622 || l_quot_rec.khr_id
2623 );
2624 okl_debug_pub.log_debug (g_level_statement,
2625 l_module_name,
2626 'l_quot_rec.qtp_code ='
2627 || l_quot_rec.qtp_code
2628 );
2629 okl_debug_pub.log_debug
2630 (g_level_statement,
2631 l_module_name,
2632 'l_quot_rec.date_effective_from ='
2633 || l_quot_rec.date_effective_from
2634 );
2635 END IF;
2636
2637 i := 1;
2638
2639 FOR taa_term_assets_rec IN
2640 taa_term_assets_csr
2641 (p_orig_chr_id => ln_orig_system_id1,
2642 p_source_trx_id => taa_trx_rec.source_trx_id
2643 )
2644 LOOP
2645 l_assn_rec.p_asset_id := taa_term_assets_rec.asset_id;
2646 l_assn_rec.p_asset_number :=
2647 taa_term_assets_rec.asset_number;
2648 l_assn_rec.p_asset_qty := taa_term_assets_rec.current_units;
2649 l_assn_rec.p_quote_qty := taa_term_assets_rec.current_units;
2650 l_assn_tbl (i) := l_assn_rec;
2651
2652 IF (is_debug_statement_on) THEN
2653 okl_debug_pub.log_debug
2654 (g_level_statement,
2655 l_module_name,
2656 'l_assn_rec.p_asset_id = '
2657 || l_assn_rec.p_asset_id
2658 );
2659 okl_debug_pub.log_debug
2660 (g_level_statement,
2661 l_module_name,
2662 'l_assn_rec.p_asset_number = '
2663 || l_assn_rec.p_asset_number
2664 );
2665 okl_debug_pub.log_debug
2666 (g_level_statement,
2667 l_module_name,
2668 'l_assn_rec.p_asset_qty = '
2669 || l_assn_rec.p_asset_qty
2670 );
2671 okl_debug_pub.log_debug
2672 (g_level_statement,
2673 l_module_name,
2674 'l_assn_rec.p_quote_qty = '
2675 || l_assn_rec.p_quote_qty
2676 );
2677 END IF;
2678
2679 i := i + 1;
2680 END LOOP;
2681 -- If Re-lease contract then terminate all asset lines
2682 ELSE
2683 i := 1;
2684
2685 FOR chr_term_assets_rec IN
2686 chr_term_assets_csr (p_orig_chr_id => ln_orig_system_id1)
2687 LOOP
2688 l_assn_rec.p_asset_id := chr_term_assets_rec.asset_id;
2689 l_assn_rec.p_asset_number :=
2690 chr_term_assets_rec.asset_number;
2691 l_assn_rec.p_asset_qty := chr_term_assets_rec.current_units;
2692 l_assn_rec.p_quote_qty := chr_term_assets_rec.current_units;
2693 l_assn_tbl (i) := l_assn_rec;
2694
2695 IF (is_debug_statement_on) THEN
2696 okl_debug_pub.log_debug
2697 (g_level_statement,
2698 l_module_name,
2699 'l_assn_rec.p_asset_id = '
2700 || l_assn_rec.p_asset_id
2701 );
2702 okl_debug_pub.log_debug
2703 (g_level_statement,
2704 l_module_name,
2705 'l_assn_rec.p_asset_number = '
2706 || l_assn_rec.p_asset_number
2707 );
2708 okl_debug_pub.log_debug
2709 (g_level_statement,
2710 l_module_name,
2711 'l_assn_rec.p_asset_qty = '
2712 || l_assn_rec.p_asset_qty
2713 );
2714 okl_debug_pub.log_debug
2715 (g_level_statement,
2716 l_module_name,
2717 'l_assn_rec.p_quote_qty = '
2718 || l_assn_rec.p_quote_qty
2719 );
2720 END IF;
2721
2722 i := i + 1;
2723 END LOOP;
2724 END IF;
2725
2726 okl_am_create_quote_pub.create_terminate_quote
2727 (p_api_version => p_api_version,
2728 p_init_msg_list => p_init_msg_list,
2729 x_return_status => x_return_status,
2730 x_msg_count => x_msg_count,
2731 x_msg_data => x_msg_data,
2732 p_quot_rec => l_quot_rec,
2733 p_assn_tbl => l_assn_tbl,
2734 p_qpyv_tbl => l_qpyv_tbl,
2735 x_quot_rec => x_quot_rec,
2736 x_tqlv_tbl => x_tqlv_tbl,
2737 x_assn_tbl => x_assn_tbl
2738 );
2739
2740 IF (is_debug_statement_on) THEN
2741 okl_debug_pub.log_debug
2742 (g_level_statement,
2743 l_module_name,
2744 'AFTER OKL_AM_CREATE_QUOTE_PUB.create_terminate_quote CALL'
2745 );
2746 okl_debug_pub.log_debug (g_level_statement,
2747 l_module_name,
2748 'x_return_status =' || x_return_status
2749 );
2750 END IF;
2751
2752 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2753 RAISE okl_api.g_exception_unexpected_error;
2754 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2755 RAISE okl_api.g_exception_error;
2756 END IF;
2757
2758 l_term_rec.ID := x_quot_rec.ID;
2759 l_term_rec.accepted_yn := 'Y';
2760
2761 IF (is_debug_statement_on) THEN
2762 okl_debug_pub.log_debug
2763 (g_level_statement,
2764 l_module_name,
2765 'Before OKL_TRX_CONTRACTS_PUB.update_trx_contracts'
2766 );
2767 okl_debug_pub.log_debug (g_level_statement,
2768 l_module_name,
2769 'l_term_rec.id =>'
2770 || l_term_rec.ID
2771 );
2772 okl_debug_pub.log_debug (g_level_statement,
2773 l_module_name,
2774 'l_term_rec.accepted_yn =>'
2775 || l_term_rec.accepted_yn
2776 );
2777 END IF;
2778
2779 okl_am_termnt_quote_pub.terminate_quote
2780 (p_api_version => p_api_version,
2781 p_init_msg_list => p_init_msg_list,
2782 x_return_status => x_return_status,
2783 x_msg_count => x_msg_count,
2784 x_msg_data => x_msg_data,
2785 p_term_rec => l_term_rec,
2786 x_term_rec => x_term_rec,
2787 x_err_msg => x_err_msg,
2788 p_acceptance_source => 'RELEASE_CONTRACT'
2789 );
2790
2791 IF (is_debug_statement_on) THEN
2792 okl_debug_pub.log_debug
2793 (g_level_statement,
2794 l_module_name,
2795 'AFTER OKL_AM_TERMNT_QUOTE_PUB.terminate_quote CALL'
2796 );
2797 okl_debug_pub.log_debug (g_level_statement,
2798 l_module_name,
2799 'x_return_status =' || x_return_status
2800 );
2801 END IF;
2802
2803 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2804 RAISE okl_api.g_exception_unexpected_error;
2805 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2806 RAISE okl_api.g_exception_error;
2807 END IF;
2808
2809 -- Save Termination Quote Id on the Transaction row
2810 l_tcnv_rec.ID := taa_trx_rec.ID;
2811 l_tcnv_rec.qte_id := x_quot_rec.ID;
2812
2813 IF (is_debug_statement_on) THEN
2814 okl_debug_pub.log_debug
2815 (g_level_statement,
2816 l_module_name,
2817 'Before OKL_TRX_CONTRACTS_PUB.update_trx_contracts'
2818 );
2819 okl_debug_pub.log_debug (g_level_statement,
2820 l_module_name,
2821 'l_tcnv_rec.id =>'
2822 || taa_trx_rec.ID
2823 );
2824 okl_debug_pub.log_debug (g_level_statement,
2825 l_module_name,
2826 'l_tcnv_rec.qte_id =>'
2827 || x_quot_rec.ID
2828 );
2829 END IF;
2830
2831 okl_trx_contracts_pub.update_trx_contracts
2832 (p_api_version => p_api_version,
2833 p_init_msg_list => p_init_msg_list,
2834 x_return_status => x_return_status,
2835 x_msg_count => x_msg_count,
2836 x_msg_data => x_msg_data,
2837 p_tcnv_rec => l_tcnv_rec,
2838 x_tcnv_rec => l_out_tcnv_rec
2839 );
2840
2841 IF (is_debug_statement_on) THEN
2842 okl_debug_pub.log_debug
2843 (g_level_statement,
2844 l_module_name,
2845 'AFTER OKL_AM_TERMNT_QUOTE_PUB.terminate_quote CALL'
2846 );
2847 okl_debug_pub.log_debug (g_level_statement,
2848 l_module_name,
2849 'x_return_status =' || x_return_status
2850 );
2851 END IF;
2852
2853 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2854 RAISE okl_api.g_exception_unexpected_error;
2855 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2856 RAISE okl_api.g_exception_error;
2857 END IF;
2858 END IF; -- Termination quote exists Y/N
2859
2860 -- Check if Termination process is Complete
2861 l_termination_complete := 'Y';
2862
2863 -- Bug# 4061058
2864 IF (taa_trx_rec.source_trx_id IS NOT NULL) THEN
2865 -- For T and A transaction, check if all asset lines in
2866 -- T and A request are Terminated
2867 OPEN chk_taa_term_csr
2868 (p_orig_chr_id => ln_orig_system_id1,
2869 p_source_trx_id => taa_trx_rec.source_trx_id
2870 );
2871
2872 FETCH chk_taa_term_csr
2873 INTO chk_taa_term_rec;
2874
2875 IF chk_taa_term_csr%FOUND THEN
2876 l_termination_complete := 'N';
2877 END IF;
2878
2879 CLOSE chk_taa_term_csr;
2880
2881 -- Check Contract status to confirm if Termination is complete
2882 IF (l_termination_complete = 'Y') THEN
2883 OPEN taa_request_csr
2884 (p_source_trx_id => taa_trx_rec.source_trx_id);
2885
2886 FETCH taa_request_csr
2887 INTO taa_request_rec;
2888
2889 CLOSE taa_request_csr;
2890
2891 OPEN chk_chr_term_csr (p_orig_chr_id => ln_orig_system_id1);
2892
2893 FETCH chk_chr_term_csr
2894 INTO chk_chr_term_rec;
2895
2896 CLOSE chk_chr_term_csr;
2897
2898 -- For Partial TA check if Original contract status is
2899 -- Active or Hold
2900 IF (taa_request_rec.complete_transfer_yn = 'N') THEN
2901 IF chk_chr_term_rec.sts_code NOT IN
2902 ('BOOKED',
2903 'EVERGREEN',
2904 'BANKRUPTCY_HOLD',
2905 'LITIGATION_HOLD'
2906 ) THEN
2907 l_termination_complete := 'N';
2908 END IF;
2909 -- For Full TA check if Original contract status is
2910 -- Terminated
2911 ELSE
2912 IF chk_chr_term_rec.sts_code <> 'TERMINATED' THEN
2913 l_termination_complete := 'N';
2914 END IF;
2915 END IF;
2916 END IF;
2917 -- For Re-lease Contract, check if Contract is Terminated
2918 ELSE
2919 OPEN chk_chr_term_csr (p_orig_chr_id => ln_orig_system_id1);
2920
2921 FETCH chk_chr_term_csr
2922 INTO chk_chr_term_rec;
2923
2924 CLOSE chk_chr_term_csr;
2925
2926 IF chk_chr_term_rec.sts_code <> 'TERMINATED' THEN
2927 l_termination_complete := 'N';
2928 END IF;
2929 END IF;
2930
2931 --Bug# 4072796
2932 -- Check termination transaction status to confirm if Termination is complete
2933 IF (l_termination_complete = 'Y') THEN
2934 OPEN termination_trx_csr (p_qte_id => NVL
2935 (taa_trx_rec.qte_id,
2936 x_quot_rec.ID
2937 ),
2938 p_khr_id => ln_orig_system_id1
2939 );
2940
2941 FETCH termination_trx_csr
2942 INTO termination_trx_rec;
2943
2944 CLOSE termination_trx_csr;
2945
2946 --Bug# 6504515
2947 --if termination_trx_rec.tsu_code <> 'PROCESSED' then
2948 IF termination_trx_rec.tmt_status_code <> 'PROCESSED' THEN
2949 l_termination_complete := 'N';
2950 END IF;
2951 END IF;
2952
2953 -- Raise error if Termination process is not complete
2954 IF (l_termination_complete = 'N') THEN
2955 OPEN quote_num_csr (p_qte_id => NVL (taa_trx_rec.qte_id,
2956 x_quot_rec.ID
2957 )
2958 );
2959
2960 FETCH quote_num_csr
2961 INTO quote_num_rec;
2962
2963 CLOSE quote_num_csr;
2964
2965 okl_api.set_message
2966 (p_app_name => g_app_name,
2967 p_msg_name => 'OKL_LLA_REL_TERMN_NO_COMPLETE',
2968 p_token1 => 'QUOTE_NUM',
2969 p_token1_value => quote_num_rec.quote_number
2970 );
2971 x_termination_complete_yn := 'N';
2972 x_return_status := okl_api.g_ret_sts_success;
2973 END IF; --Termination process complete Y/N
2974
2975 --Bug# 4515347
2976 --call process FA transactions
2977 IF x_termination_complete_yn = 'Y' THEN
2978 --Bug# 4631549
2979 FOR off_lease_ast_rec IN
2980 off_lease_ast_csr (p_orig_chr_id => ln_orig_system_id1)
2981 LOOP
2982 IF (is_debug_statement_on) THEN
2983 okl_debug_pub.log_debug
2984 (g_level_statement,
2985 l_module_name,
2986 'Before OKL_AM_PROCESS_ASSET_TRX_PVT.process_transactions'
2987 );
2988 okl_debug_pub.log_debug (g_level_statement,
2989 l_module_name,
2990 'p_api_version =>'
2991 || p_api_version
2992 );
2993 okl_debug_pub.log_debug (g_level_statement,
2994 l_module_name,
2995 'p_init_msg_list =>'
2996 || p_init_msg_list
2997 );
2998 okl_debug_pub.log_debug (g_level_statement,
2999 l_module_name,
3000 'p_contract_id => '
3001 || ln_orig_system_id1
3002 );
3003 okl_debug_pub.log_debug (g_level_statement,
3004 l_module_name,
3005 'p_kle_id => '
3006 || off_lease_ast_rec.ID
3007 );
3008 END IF;
3009
3010 okl_am_process_asset_trx_pvt.process_transactions
3011 (p_api_version => p_api_version,
3012 p_init_msg_list => p_init_msg_list,
3013 x_return_status => x_return_status,
3014 x_msg_count => x_msg_count,
3015 x_msg_data => x_msg_data,
3016 --Bug# 4631549
3017 p_contract_id => ln_orig_system_id1,
3018 p_asset_id => NULL,
3019 p_kle_id => off_lease_ast_rec.ID,
3020 --Bug# 4631549
3021 p_salvage_writedown_yn => 'Y',
3022 x_total_count => l_total_count,
3023 x_processed_count => l_processed_count,
3024 x_error_count => l_error_count
3025 );
3026
3027 IF (is_debug_statement_on) THEN
3028 okl_debug_pub.log_debug
3029 (g_level_statement,
3030 l_module_name,
3031 'AFTER OKL_AM_PROCESS_ASSET_TRX_PVT.process_transactions CALL'
3032 );
3033 okl_debug_pub.log_debug (g_level_statement,
3034 l_module_name,
3035 'x_return_status ='
3036 || x_return_status
3037 );
3038 END IF;
3039
3040 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3041 RAISE okl_api.g_exception_unexpected_error;
3042 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3043 RAISE okl_api.g_exception_error;
3044 END IF;
3045 END LOOP;
3046
3047 -- Check if Process FA transations is Complete
3048 --Bug# 4631549
3049 OPEN chk_off_lease_csr (p_orig_chr_id => ln_orig_system_id1);
3050
3051 LOOP
3052 FETCH chk_off_lease_csr
3053 INTO chk_off_lease_rec;
3054
3055 EXIT WHEN chk_off_lease_csr%NOTFOUND;
3056
3057 IF chk_off_lease_rec.tsu_code NOT IN
3058 ('PROCESSED', 'CANCELED')
3059 AND NVL (chk_off_lease_rec.hold_period_days, 0) = 0 THEN
3060 x_termination_complete_yn := 'N';
3061 x_return_status := okl_api.g_ret_sts_success;
3062 EXIT;
3063 ELSIF chk_off_lease_rec.tsu_code NOT IN
3064 ('PROCESSED', 'CANCELED')
3065 AND NVL (chk_off_lease_rec.hold_period_days, 0) > 0 THEN
3066 --Mark off-lease transaction as canceled
3067 l_tasv_rec.ID := chk_off_lease_rec.ID;
3068 l_tasv_rec.tsu_code := 'CANCELED';
3069
3070 IF (is_debug_statement_on) THEN
3071 okl_debug_pub.log_debug
3072 (g_level_statement,
3073 l_module_name,
3074 'before okl_trx_assets_pub.update_trx_Ass_h_Def CALL'
3075 );
3076 okl_debug_pub.log_debug (g_level_statement,
3077 l_module_name,
3078 'l_tasv_rec.id ='
3079 || l_tasv_rec.ID
3080 );
3081 okl_debug_pub.log_debug (g_level_statement,
3082 l_module_name,
3083 'l_tasv_rec.tsu_code ='
3084 || l_tasv_rec.tsu_code
3085 );
3086 END IF;
3087
3088 okl_trx_assets_pub.update_trx_ass_h_def
3089 (p_api_version => p_api_version,
3090 p_init_msg_list => p_init_msg_list,
3091 x_return_status => x_return_status,
3092 x_msg_count => x_msg_count,
3093 x_msg_data => x_msg_data,
3094 p_thpv_rec => l_tasv_rec,
3095 x_thpv_rec => lx_tasv_rec
3096 );
3097
3098 IF (is_debug_statement_on) THEN
3099 okl_debug_pub.log_debug
3100 (g_level_statement,
3101 l_module_name,
3102 'AFTER okl_trx_assets_pub.update_trx_Ass_h_Def CALL'
3103 );
3104 okl_debug_pub.log_debug (g_level_statement,
3105 l_module_name,
3106 'x_return_status ='
3107 || x_return_status
3108 );
3109 END IF;
3110
3111 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3112 RAISE okl_api.g_exception_unexpected_error;
3113 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3114 RAISE okl_api.g_exception_error;
3115 END IF;
3116 END IF;
3117 END LOOP;
3118
3119 CLOSE chk_off_lease_csr;
3120 --Bug# 4631549
3121 --if chk_off_lease_rec.off_lease_exists = 'Y' then
3122 --x_termination_complete_yn := 'N';
3123 --x_return_status := OKL_API.G_RET_STS_SUCCESS;
3124 --end if;
3125 --Bug# 4631549
3126 END IF; -- Bug# 4515347
3127 ELSE
3128 okl_api.set_message
3129 (p_app_name => g_app_name,
3130 p_msg_name => 'This Contract is not a Re-Lease Contract'
3131 );
3132 RAISE okl_api.g_exception_error;
3133 END IF;
3134
3135 okl_api.end_activity (x_msg_count => x_msg_count,
3136 x_msg_data => x_msg_data
3137 );
3138
3139 IF (is_debug_procedure_on) THEN
3140 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'End(-)');
3141 END IF;
3142
3143 EXCEPTION
3144 WHEN okl_api.g_exception_error THEN
3145 IF get_orig_sys_code%ISOPEN THEN
3146 CLOSE get_orig_sys_code;
3147 END IF;
3148
3149 x_return_status :=
3150 okl_api.handle_exceptions
3151 (p_api_name => l_api_name,
3152 p_pkg_name => g_pkg_name,
3153 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
3154 x_msg_count => x_msg_count,
3155 x_msg_data => x_msg_data,
3156 p_api_type => g_api_type
3157 );
3158 WHEN okl_api.g_exception_unexpected_error THEN
3159 IF get_orig_sys_code%ISOPEN THEN
3160 CLOSE get_orig_sys_code;
3161 END IF;
3162
3163 x_return_status :=
3164 okl_api.handle_exceptions
3165 (p_api_name => l_api_name,
3166 p_pkg_name => g_pkg_name,
3167 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
3168 x_msg_count => x_msg_count,
3169 x_msg_data => x_msg_data,
3170 p_api_type => g_api_type
3171 );
3172 WHEN OTHERS THEN
3173 IF get_orig_sys_code%ISOPEN THEN
3174 CLOSE get_orig_sys_code;
3175 END IF;
3176
3177 x_return_status :=
3178 okl_api.handle_exceptions (p_api_name => l_api_name,
3179 p_pkg_name => g_pkg_name,
3180 p_exc_name => 'OTHERS',
3181 x_msg_count => x_msg_count,
3182 x_msg_data => x_msg_data,
3183 p_api_type => g_api_type
3184 );
3185 END terminate_original_contract;
3186
3187 PROCEDURE activate_contract (
3188 p_api_version IN NUMBER,
3189 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
3190 x_return_status OUT NOCOPY VARCHAR2,
3191 x_msg_count OUT NOCOPY NUMBER,
3192 x_msg_data OUT NOCOPY VARCHAR2,
3193 p_chr_id IN VARCHAR2
3194 ) IS
3195 l_api_name CONSTANT VARCHAR2 (30) := 'ACTIVATE_CONTRACT';
3196 l_api_version CONSTANT NUMBER := 1.0;
3197 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
3198 l_isallowed BOOLEAN;
3199 l_passstatus VARCHAR2 (100) := 'BOOKED';
3200 l_failstatus VARCHAR2 (100) := 'APPROVED';
3201 l_event VARCHAR2 (100)
3202 := okl_contract_status_pub.g_k_activate;
3203 l_cimv_tbl okl_okc_migration_pvt.cimv_tbl_type;
3204 x_message VARCHAR2 (256);
3205
3206 -- Sales Tax project changes START - BUG 4373029
3207 SUBTYPE tcnv_rec_type IS okl_trx_contracts_pvt.tcnv_rec_type;
3208
3209 x_trxh_rec tcnv_rec_type;
3210
3211 -- Sales Tax project changes END
3212 CURSOR l_chk_mass_rbk_csr (p_chr_id IN NUMBER) IS
3213 SELECT 'Y' what
3214 FROM okc_k_headers_b CHR
3215 WHERE CHR.ID = p_chr_id
3216 AND EXISTS (
3217 SELECT '1'
3218 FROM okl_trx_contracts ktrx
3219 WHERE ktrx.khr_id = CHR.ID
3220 AND ktrx.tsu_code = 'ENTERED'
3221 AND ktrx.rbr_code IS NOT NULL
3222 AND ktrx.tcn_type = 'TRBK'
3223 --rkuttiya added for 12.1.1 Multi GAAP Project
3224 AND ktrx.representation_type = 'PRIMARY')
3225 --
3226 AND EXISTS (
3227 SELECT '1'
3228 FROM okl_rbk_selected_contract rbk_khr
3229 WHERE rbk_khr.khr_id = CHR.ID
3230 AND rbk_khr.status <> 'PROCESSED');
3231
3232 l_chk_mass_rbk_rec l_chk_mass_rbk_csr%ROWTYPE;
3233 l_commit VARCHAR2 (256) := okl_api.g_false;
3234 l_transaction_type VARCHAR2 (256);
3235 l_acct_trans_type VARCHAR2 (256); --Bug 5909373
3236 l_draft_yn VARCHAR2 (1) := okl_api.g_false;
3237 l_chr_for_sts_change NUMBER;
3238 old_rec old_csr%ROWTYPE;
3239 rbk_rec rbk_csr%ROWTYPE;
3240
3241 CURSOR l_hdr_csr (chrid NUMBER) IS
3242 SELECT CHR.orig_system_source_code,
3243 CHR.start_date,
3244 CHR.template_yn,
3245 CHR.authoring_org_id,
3246 CHR.inv_organization_id,
3247 khr.deal_type,
3248 pdt.ID pid,
3249 NVL (pdt.reporting_pdt_id, -1) report_pdt_id,
3250 CHR.currency_code currency_code,
3251 khr.term_duration term
3252 FROM okc_k_headers_v CHR, okl_k_headers khr, okl_products_v pdt
3253 WHERE khr.ID = CHR.ID AND CHR.ID = chrid AND khr.pdt_id = pdt.ID(+);
3254
3255 l_hdr_rec l_hdr_csr%ROWTYPE;
3256 p_pdtv_rec okl_setupproducts_pub.pdtv_rec_type;
3257 x_pdt_parameter_rec okl_setupproducts_pub.pdt_parameters_rec_type;
3258 x_no_data_found BOOLEAN;
3259
3260 /* Suresh 22-Sep-2004 Start
3261 update the creditline contract with total rollover amount
3262 */
3263 /* Manu 18-Aug-2004 Start
3264 Cursor to get the rollover fee lines for a contract
3265 that is booked for the first time. */
3266
3267 -- nikshah -- Bug # 5484903 Fixed,
3268 -- Changed l_rq_fee_lns_bkg_csr SQL definition
3269 CURSOR l_rq_fee_lns_bkg_csr (chrid IN okc_k_headers_b.ID%TYPE) IS
3270 SELECT kle.qte_id
3271 FROM okc_k_headers_b khr, okc_k_lines_b cleb, okl_k_lines kle
3272 WHERE khr.ID = chrid
3273 AND cleb.dnz_chr_id = khr.ID
3274 AND kle.ID = cleb.ID
3275 AND kle.fee_type = 'ROLLOVER'
3276 AND NOT EXISTS (
3277 SELECT 'Y'
3278 FROM okc_statuses_b okcsts
3279 WHERE okcsts.code = cleb.sts_code
3280 AND okcsts.ste_code IN
3281 ('EXPIRED',
3282 'HOLD',
3283 'CANCELLED',
3284 'TERMINATED',
3285 'ABANDONED'
3286 ));
3287
3288 l_ro_fee_bkg_found BOOLEAN := FALSE;
3289
3290 /* Cursor to get the NEW rollover fee lines that are added
3291 to a re-book contract. */
3292
3293 -- nikshah -- Bug # 5484903 Fixed
3294 -- Changed CURSOR l_rq_fee_lns_rbk_csr SQL definition
3295 CURSOR l_rq_fee_lns_rbk_csr (chrid IN okc_k_headers_b.ID%TYPE) IS
3296 SELECT kle.qte_id
3297 FROM okc_k_headers_b khr, okc_k_lines_b cleb, okl_k_lines kle
3298 WHERE khr.ID = chrid
3299 AND cleb.dnz_chr_id = khr.ID
3300 AND kle.ID = cleb.ID
3301 AND kle.fee_type = 'ROLLOVER'
3302 AND cleb.orig_system_id1 IS NULL
3303 --This means new Fee Line (top line)
3304 AND NOT EXISTS (
3305 SELECT 'Y'
3306 FROM okc_statuses_b okcsts
3307 WHERE okcsts.code = cleb.sts_code
3308 AND okcsts.ste_code IN
3309 ('EXPIRED',
3310 'HOLD',
3311 'CANCELLED',
3312 'TERMINATED',
3313 'ABANDONED'
3314 ));
3315
3316 l_ro_fee_rbk_found BOOLEAN := FALSE;
3317
3318 /* Cursor to check if the contract is rebooked contract. */
3319 CURSOR l_chk_rbk_csr (chrid IN okc_k_headers_b.ID%TYPE) IS
3320 SELECT '!'
3321 FROM okc_k_headers_b CHR
3322 WHERE CHR.ID = chrid AND CHR.orig_system_source_code = 'OKL_REBOOK';
3323
3324 l_qte_id okl_k_lines.qte_id%TYPE;
3325 l_creditline_id okl_k_lines.qte_id%TYPE;
3326 x_rem_amt NUMBER;
3327 p_term_tbl okl_trx_quotes_pub.qtev_tbl_type;
3328 x_term_tbl okl_trx_quotes_pub.qtev_tbl_type;
3329 x_err_msg VARCHAR2 (1000);
3330 l_rbk_khr VARCHAR2 (1) DEFAULT '?';
3331 l_tq_rec_count NUMBER := 0;
3332 -- Rollover fee line count on a contract
3333
3334 /* Manu 18-Aug-2004 End */
3335
3336 /* Manu 18-Nov-2004 Start */
3337 /* Cursor to if the contract start date is not in the future
3338 (less than or equal to SYSDATE). */
3339
3340 -- nikshah -- Bug # 5484903 Fixed,
3341 -- Changed CURSOR l_k_std_csr SQL definition
3342 CURSOR l_k_std_csr (chrid okc_k_headers_b.ID%TYPE) IS
3343 SELECT 1
3344 FROM okc_k_lines_v cleb, okl_k_lines kle, okc_k_headers_b khr
3345 WHERE khr.ID = chrid
3346 AND cleb.dnz_chr_id = khr.ID
3347 AND kle.ID = cleb.ID
3348 AND kle.fee_type = 'ROLLOVER'
3349 AND TRUNC (khr.start_date) > SYSDATE
3350 AND NOT EXISTS (
3351 SELECT 'Y'
3352 FROM okc_statuses_b okcsts
3353 WHERE okcsts.code = cleb.sts_code
3354 AND okcsts.ste_code IN
3355 ('EXPIRED',
3356 'HOLD',
3357 'CANCELLED',
3358 'TERMINATED',
3359 'ABANDONED'
3360 ));
3361
3362 /* Cursor for Re-book contract */
3363 -- nikshah -- Bug # 5484903 Fixed,
3364 -- Changed CURSOR l_k_std__4rbk_csr SQL definition
3365 CURSOR l_k_std__4rbk_csr (chrid okc_k_headers_b.ID%TYPE) IS
3366 SELECT 1
3367 FROM okc_k_lines_v cleb, okl_k_lines kle, okc_k_headers_b khr
3368 WHERE khr.ID = chrid
3369 AND cleb.dnz_chr_id = khr.ID
3370 AND kle.ID = cleb.ID
3371 AND kle.fee_type = 'ROLLOVER'
3372 AND TRUNC (khr.start_date) > SYSDATE
3373 AND cleb.orig_system_id1 IS NULL
3374 --This means new Fee Line (top line)
3375 AND NOT EXISTS (
3376 SELECT 'Y'
3377 FROM okc_statuses_b okcsts
3378 WHERE okcsts.code = cleb.sts_code
3379 AND okcsts.ste_code IN
3380 ('EXPIRED',
3381 'HOLD',
3382 'CANCELLED',
3383 'TERMINATED',
3384 'ABANDONED'
3385 ));
3386
3387 l_in_future BOOLEAN := FALSE;
3388 l_found VARCHAR2 (1);
3389
3390 /* Manu 18-Nov-2004 End */
3391
3392 --Bug# 3948361: start
3393 --cursor to check if contract is a re-lease contract
3394 CURSOR l_chk_rel_khr_csr (p_chr_id IN NUMBER) IS
3395 SELECT '!'
3396 FROM okc_k_headers_b CHR
3397 WHERE CHR.ID = p_chr_id
3398 AND NVL (CHR.orig_system_source_code, 'XXXX') = 'OKL_RELEASE';
3399
3400 l_rel_khr VARCHAR2 (1);
3401 l_proceed_activation VARCHAR2 (30);
3402
3403 --Bug# 3948361: end
3404
3405 --Bug# 4502754
3406 --cursor to check for vendor program template
3407 CURSOR l_chk_template_csr (p_chr_id IN NUMBER) IS
3408 SELECT CHR.template_yn,
3409 khr.template_type_code
3410 FROM okc_k_headers_b CHR, okl_k_headers khr
3411 WHERE CHR.ID = p_chr_id AND CHR.ID = khr.ID;
3412
3413 l_chk_template_rec l_chk_template_csr%ROWTYPE;
3414
3415 /*
3416 -- mvasudev, 08/30/2004
3417 -- Added PROCEDURE to enable Business Event
3418 */
3419 CURSOR l_rbk_trx_csr IS
3420 SELECT ktrx.khr_id,
3421 ktrx.date_transaction_occurred
3422 FROM okc_k_headers_b CHR, okl_trx_contracts ktrx
3423 WHERE ktrx.khr_id_new = CHR.ID
3424 AND ktrx.tsu_code = 'ENTERED'
3425 AND ktrx.rbr_code IS NOT NULL
3426 AND ktrx.tcn_type = 'TRBK'
3427 --rkuttiya added for 12.1.1 Multi GAAP Project
3428 AND ktrx.representation_type = 'PRIMARY'
3429 --
3430 AND CHR.ID = p_chr_id
3431 AND CHR.orig_system_source_code = 'OKL_REBOOK';
3432
3433 l_rbk_khr_id NUMBER;
3434 l_rbk_date DATE;
3435
3436 --ramurt Bug#4622438
3437 CURSOR chk_product_status (p_chr_id IN NUMBER) IS
3438 SELECT pdt.NAME,
3439 pdt.product_status_code
3440 FROM okl_products_v pdt, okl_k_headers_v khr, okc_k_headers_b CHR
3441 WHERE 1 = 1
3442 AND khr.ID = p_chr_id
3443 AND pdt_id = pdt.ID
3444 AND khr.ID = CHR.ID;
3445
3446 l_product_status_code okl_products_v.product_status_code%TYPE;
3447 l_product_name okl_products_v.NAME%TYPE;
3448 -- 4577840 end
3449 l_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
3450 -- 4895333;
3451 --Bug# 4631549
3452 l_mass_rebook_yn VARCHAR2 (1);
3453
3454 --Bug# 4631549 end
3455 PROCEDURE raise_business_event (
3456 p_rbk_khr_id IN NUMBER,
3457 p_date_transaction_occurred IN DATE,
3458 x_return_status OUT NOCOPY VARCHAR2
3459 ) IS
3460 l_process VARCHAR2 (20);
3461 l_parameter_list wf_parameter_list_t;
3462 BEGIN
3463 x_return_status := okl_api.g_ret_sts_success;
3464 l_process := okl_lla_util_pvt.get_contract_process (p_chr_id);
3465
3466 -- Raise "Rebook Completed" for Rebook Process
3467 FOR l_chk_rbk_rec IN l_chk_rbk_csr (p_chr_id)
3468 LOOP
3469 wf_event.addparametertolist (g_wf_itm_src_contract_id,
3470 p_chr_id,
3471 l_parameter_list
3472 );
3473 wf_event.addparametertolist (g_wf_itm_dest_contract_id,
3474 p_rbk_khr_id,
3475 l_parameter_list
3476 );
3477 wf_event.addparametertolist
3478 (g_wf_itm_trx_date,
3479 fnd_date.date_to_canonical (p_date_transaction_occurred),
3480 l_parameter_list
3481 );
3482 wf_event.addparametertolist (g_wf_itm_contract_process,
3483 g_khr_process_rebook,
3484 l_parameter_list
3485 );
3486 okl_wf_pvt.raise_event (p_api_version => p_api_version,
3487 p_init_msg_list => p_init_msg_list,
3488 x_return_status => x_return_status,
3489 x_msg_count => x_msg_count,
3490 x_msg_data => x_msg_data,
3491 p_event_name => g_wf_evt_khr_rebook_comp,
3492 p_parameters => l_parameter_list
3493 );
3494 END LOOP;
3495
3496 -- Raise "Contract Activated" always
3497 wf_event.addparametertolist (g_wf_itm_contract_id,
3498 p_chr_id,
3499 l_parameter_list
3500 );
3501 wf_event.addparametertolist (g_wf_itm_contract_process,
3502 l_process,
3503 l_parameter_list
3504 );
3505 okl_wf_pvt.raise_event (p_api_version => p_api_version,
3506 p_init_msg_list => p_init_msg_list,
3507 x_return_status => x_return_status,
3508 x_msg_count => x_msg_count,
3509 x_msg_data => x_msg_data,
3510 p_event_name => g_wf_evt_khr_activated,
3511 p_parameters => l_parameter_list
3512 );
3513 EXCEPTION
3514 WHEN OTHERS THEN
3515 x_return_status := okl_api.g_ret_sts_unexp_error;
3516 RAISE okl_api.g_exception_unexpected_error;
3517 END raise_business_event;
3518 /*
3519 -- mvasudev, 08/30/2004
3520 -- END, PROCEDURE to enable Business Event
3521 */
3522 BEGIN
3523 x_return_status := okl_api.g_ret_sts_success;
3524 x_return_status :=
3525 okl_api.start_activity (p_api_name => l_api_name,
3526 p_pkg_name => g_pkg_name,
3527 p_init_msg_list => p_init_msg_list,
3528 l_api_version => l_api_version,
3529 p_api_version => p_api_version,
3530 p_api_type => g_api_type,
3531 x_return_status => x_return_status
3532 );
3533
3534 -- check if activity started successfully
3535 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3536 RAISE okl_api.g_exception_unexpected_error;
3537 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3538 RAISE okl_api.g_exception_error;
3539 END IF;
3540
3541 --Bug# 3556674
3542 validate_chr_id (p_chr_id => p_chr_id,
3543 x_return_status => x_return_status
3544 );
3545
3546 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3547 RAISE okl_api.g_exception_unexpected_error;
3548 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3549 RAISE okl_api.g_exception_error;
3550 END IF;
3551
3552 --Bug# 3556674
3553
3554 /*
3555 -- mvasudev, 11/08/2004
3556 -- Added to enable Business Event
3557 */
3558 FOR l_rbk_trx_rec IN l_rbk_trx_csr
3559 LOOP
3560 l_rbk_khr_id := l_rbk_trx_rec.khr_id;
3561 l_rbk_date := l_rbk_trx_rec.date_transaction_occurred;
3562 END LOOP;
3563
3564 /*
3565 -- mvasudev, 11/08/2004
3566 -- END,Added to enable Business Event
3567 */
3568 okl_contract_status_pub.get_contract_status (l_api_version,
3569 p_init_msg_list,
3570 x_return_status,
3571 x_msg_count,
3572 x_msg_data,
3573 l_isallowed,
3574 l_passstatus,
3575 l_failstatus,
3576 l_event,
3577 p_chr_id
3578 );
3579
3580 IF (l_isallowed = FALSE) THEN
3581 x_return_status := okl_api.g_ret_sts_success;
3582 okl_api.set_message (p_app_name => g_app_name,
3583 p_msg_name => 'OKL_LLA_NOT_APPROVED'
3584 );
3585 RAISE okl_api.g_exception_error;
3586 END IF;
3587
3588 --ramurt Bug#4622438
3589 OPEN chk_product_status (p_chr_id => TO_NUMBER (p_chr_id));
3590
3591 FETCH chk_product_status
3592 INTO l_product_name,
3593 l_product_status_code;
3594
3595 CLOSE chk_product_status;
3596
3597 IF (l_product_status_code = 'INVALID') THEN
3598 -- x_return_status := OKL_API.G_RET_STS_SUCCESS;
3599 okl_api.set_message (p_app_name => g_app_name,
3600 p_msg_name => 'OKL_LLA_INVALID_PRODUCT',
3601 p_token1 => 'PRODUCT_NAME',
3602 p_token1_value => l_product_name
3603 );
3604 RAISE okl_api.g_exception_error;
3605 END IF;
3606
3607 -- End
3608
3609 --Bug# 3948361
3610 -- For Re-lease contract, Terminate the Original contract
3611 l_rel_khr := '?';
3612
3613 --check for release contract
3614 OPEN l_chk_rel_khr_csr (p_chr_id => TO_NUMBER (p_chr_id));
3615
3616 FETCH l_chk_rel_khr_csr
3617 INTO l_rel_khr;
3618
3619 IF l_chk_rel_khr_csr%NOTFOUND THEN
3620 NULL;
3621 END IF;
3622
3623 CLOSE l_chk_rel_khr_csr;
3624
3625 --Bug# 4631549
3626 l_mass_rebook_yn := okl_api.g_false;
3627 l_mass_rebook_yn :=
3628 okl_lla_util_pvt.check_mass_rebook_contract (p_chr_id => p_chr_id);
3629 --End Bug# 4631549
3630 l_proceed_activation := 'Y';
3631
3632 --Bug# 4631549
3633 IF l_rel_khr = '!' AND l_mass_rebook_yn = okl_api.g_false THEN
3634 --IF l_rel_khr = '!' Then
3635 okl_contract_book_pvt.terminate_original_contract
3636 (p_api_version => l_api_version,
3637 p_init_msg_list => p_init_msg_list,
3638 x_return_status => x_return_status,
3639 x_msg_count => x_msg_count,
3640 x_msg_data => x_msg_data,
3641 p_chr_id => p_chr_id,
3642 x_termination_complete_yn => l_proceed_activation
3643 );
3644
3645 -- check if activity started successfully
3646 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3647 RAISE okl_api.g_exception_unexpected_error;
3648 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3649 RAISE okl_api.g_exception_error;
3650 END IF;
3651
3652 -- Bug# 4061058
3653 -- If Termination is successfully completed then
3654 -- do Commit
3655 IF l_proceed_activation = 'Y' THEN
3656 okl_api.end_activity (x_msg_count => x_msg_count,
3657 x_msg_data => x_msg_data
3658 );
3659 COMMIT;
3660 x_return_status :=
3661 okl_api.start_activity (p_api_name => l_api_name,
3662 p_pkg_name => g_pkg_name,
3663 p_init_msg_list => p_init_msg_list,
3664 l_api_version => l_api_version,
3665 p_api_version => p_api_version,
3666 p_api_type => g_api_type,
3667 x_return_status => x_return_status
3668 );
3669
3670 -- check if activity started successfully
3671 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3672 RAISE okl_api.g_exception_unexpected_error;
3673 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3674 RAISE okl_api.g_exception_error;
3675 END IF;
3676 END IF;
3677 END IF;
3678
3679 IF l_proceed_activation = 'Y' THEN
3680 --Bug# 4502754
3681 -- Vendor Program Template: Start
3682 -- For Vendor Program Template activation, skip all
3683 -- processing and set the status to Booked.
3684 OPEN l_chk_template_csr (p_chr_id => p_chr_id);
3685
3686 FETCH l_chk_template_csr
3687 INTO l_chk_template_rec;
3688
3689 CLOSE l_chk_template_csr;
3690
3691 IF ( l_chk_template_rec.template_yn = 'Y'
3692 AND l_chk_template_rec.template_type_code = 'PROGRAM'
3693 )
3694 OR
3695 --Bug# 4874338:
3696 ( l_chk_template_rec.template_yn = 'Y'
3697 AND l_chk_template_rec.template_type_code = 'LEASEAPP'
3698 ) THEN
3699 l_chr_for_sts_change := TO_NUMBER (p_chr_id);
3700 x_return_status := okl_api.g_ret_sts_success;
3701 ELSE
3702 OPEN l_hdr_csr (p_chr_id);
3703
3704 FETCH l_hdr_csr
3705 INTO l_hdr_rec;
3706
3707 IF l_hdr_csr%NOTFOUND THEN
3708 CLOSE l_hdr_csr;
3709
3710 RAISE okl_api.g_exception_unexpected_error;
3711 END IF;
3712
3713 CLOSE l_hdr_csr;
3714
3715 OPEN old_csr (TO_NUMBER (p_chr_id));
3716
3717 FETCH old_csr
3718 INTO old_rec;
3719
3720 CLOSE old_csr;
3721
3722 ----------------------------------------------------------------------------------------
3723 --Bug# 3379294 : Deal type is coming as null on some of the contracts copied from old contracts
3724 -- We should check for it and raise an error here
3725 ----------------------------------------------------------------------------------------
3726 IF NVL (old_rec.deal_type, okl_api.g_miss_char) =
3727 okl_api.g_miss_char THEN
3728 --check for incomplete product setup
3729 p_pdtv_rec.ID := l_hdr_rec.pid;
3730 okl_setupproducts_pub.getpdt_parameters
3731 (p_api_version => p_api_version,
3732 p_init_msg_list => p_init_msg_list,
3733 x_return_status => x_return_status,
3734 x_msg_count => x_msg_count,
3735 x_msg_data => x_msg_data,
3736 p_pdtv_rec => p_pdtv_rec,
3737 x_no_data_found => x_no_data_found,
3738 p_pdt_parameter_rec => x_pdt_parameter_rec
3739 );
3740
3741 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3742 RAISE okl_api.g_exception_unexpected_error;
3743 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3744 RAISE okl_api.g_exception_error;
3745 ELSIF (NVL (x_pdt_parameter_rec.NAME, okl_api.g_miss_char) =
3746 okl_api.g_miss_char
3747 ) THEN
3748 x_return_status := okl_api.g_ret_sts_error;
3749 RAISE okl_api.g_exception_error;
3750 END IF;
3751
3752 --if product setup is also complete raise an error on balnk deal type
3753 okl_api.set_message (p_app_name => g_app_name,
3754 p_msg_name => 'OKL_NULL_DEAL_TYPE'
3755 );
3756 x_return_status := okl_api.g_ret_sts_error;
3757 RAISE okl_api.g_exception_error;
3758 --Bug# : End : modified following 'IF' to 'ELSIF'
3759
3760 --ELSIF ( old_rec.deal_type <> 'LOAN-REVOLVING' ) THEN -- 4895333
3761 ELSE
3762 IF (old_rec.orig_system_source_code = 'OKL_REBOOK') THEN
3763 l_transaction_type := 'Rebook';
3764 l_chr_for_sts_change := old_rec.orig_system_id1;
3765
3766 --Bug# 2857843
3767 IF l_transaction_type = 'Booking' THEN
3768 p_pdtv_rec.ID := l_hdr_rec.pid;
3769 okl_setupproducts_pub.getpdt_parameters
3770 (p_api_version => p_api_version,
3771 p_init_msg_list => p_init_msg_list,
3772 x_return_status => x_return_status,
3773 x_msg_count => x_msg_count,
3774 x_msg_data => x_msg_data,
3775 p_pdtv_rec => p_pdtv_rec,
3776 x_no_data_found => x_no_data_found,
3777 p_pdt_parameter_rec => x_pdt_parameter_rec
3778 );
3779
3780 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3781 RAISE okl_api.g_exception_unexpected_error;
3782 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3783 RAISE okl_api.g_exception_error;
3784 --Bug# 3379294:
3785 --ELSIF ( x_pdt_parameter_rec.Name = OKL_API.G_MISS_CHAR )THEN
3786 ELSIF NVL (x_pdt_parameter_rec.NAME, okl_api.g_miss_char) =
3787 okl_api.g_miss_char THEN
3788 x_return_status := okl_api.g_ret_sts_error;
3789 RAISE okl_api.g_exception_error;
3790 END IF;
3791 END IF;
3792
3793 --Bug Fix# 2857843 End
3794 OPEN rbk_csr (l_chr_for_sts_change, TO_NUMBER (p_chr_id));
3795
3796 FETCH rbk_csr
3797 INTO rbk_rec;
3798
3799 CLOSE rbk_csr;
3800
3801 okl_la_je_pvt.generate_journal_entries
3802 (l_api_version,
3803 p_init_msg_list,
3804 l_commit,
3805 old_rec.orig_system_id1,
3806 l_transaction_type,
3807 rbk_rec.date_transaction_occurred,
3808 l_draft_yn,
3809 okl_api.g_true,
3810 x_return_status,
3811 x_msg_count,
3812 x_msg_data
3813 );
3814 ELSE
3815 l_transaction_type := 'Booking';
3816 l_chr_for_sts_change := p_chr_id;
3817
3818 OPEN l_chk_mass_rbk_csr (TO_NUMBER (p_chr_id));
3819
3820 FETCH l_chk_mass_rbk_csr
3821 INTO l_chk_mass_rbk_rec;
3822
3823 CLOSE l_chk_mass_rbk_csr;
3824
3825 IF (NVL (l_chk_mass_rbk_rec.what, 'N') = 'N') THEN
3826 --Bug# 2857843
3827 IF l_transaction_type = 'Booking' THEN
3828 p_pdtv_rec.ID := l_hdr_rec.pid;
3829 okl_setupproducts_pub.getpdt_parameters
3830 (p_api_version => p_api_version,
3831 p_init_msg_list => p_init_msg_list,
3832 x_return_status => x_return_status,
3833 x_msg_count => x_msg_count,
3834 x_msg_data => x_msg_data,
3835 p_pdtv_rec => p_pdtv_rec,
3836 x_no_data_found => x_no_data_found,
3837 p_pdt_parameter_rec => x_pdt_parameter_rec
3838 );
3839
3840 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3841 RAISE okl_api.g_exception_unexpected_error;
3842 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3843 RAISE okl_api.g_exception_error;
3844 --Bug# 3379294:
3845 --ELSIF ( x_pdt_parameter_rec.Name = OKL_API.G_MISS_CHAR )THEN
3846 ELSIF NVL (x_pdt_parameter_rec.NAME,
3847 okl_api.g_miss_char
3848 ) = okl_api.g_miss_char THEN
3849 x_return_status := okl_api.g_ret_sts_error;
3850 RAISE okl_api.g_exception_error;
3851 END IF;
3852 END IF;
3853
3854 --Bug Fix# 2857843 End
3855
3856 --Bug 5909373
3857 l_acct_trans_type := l_transaction_type;
3858
3859 IF (is_release_contract (TO_NUMBER (p_chr_id)) = 'Y') THEN
3860 l_acct_trans_type := 'Release';
3861 END IF;
3862
3863 --Bug 5909373
3864
3865 -- Sales Tax Changes START
3866 okl_la_je_pvt.generate_journal_entries
3867 (l_api_version,
3868 p_init_msg_list,
3869 l_commit,
3870 TO_NUMBER (p_chr_id),
3871 l_acct_trans_type,
3872 --Bug 5909373
3873 NULL,
3874 l_draft_yn,
3875 okl_api.g_true,
3876 x_return_status,
3877 x_msg_count,
3878 x_msg_data,
3879 x_trxh_rec
3880 );
3881
3882 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3883 RAISE okl_api.g_exception_unexpected_error;
3884 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3885 RAISE okl_api.g_exception_error;
3886 END IF;
3887
3888 okl_la_sales_tax_pvt.process_sales_tax
3889 (p_api_version => l_api_version,
3890 p_init_msg_list => p_init_msg_list,
3891 p_commit => okl_api.g_false,
3892 p_contract_id => TO_NUMBER
3893 (p_chr_id
3894 ),
3895 p_transaction_type => 'Booking',
3896 p_transaction_id => x_trxh_rec.ID,
3897 x_return_status => x_return_status,
3898 x_msg_count => x_msg_count,
3899 x_msg_data => x_msg_data
3900 );
3901
3902 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3903 RAISE okl_api.g_exception_unexpected_error;
3904 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3905 RAISE okl_api.g_exception_error;
3906 END IF;
3907 -- Sales Tax Changes END
3908
3909 /*OKL_LA_JE_PUB.generate_journal_entries(
3910 l_api_version,
3911 p_init_msg_list,
3912 l_commit,
3913 TO_NUMBER(p_chr_id),
3914 l_transaction_type,
3915 l_draft_yn,
3916 OKL_API.G_TRUE,
3917 x_return_status,
3918 x_msg_count,
3919 x_msg_data);*/
3920 END IF;
3921 END IF;
3922
3923 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
3924 RAISE okl_api.g_exception_unexpected_error;
3925 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
3926 RAISE okl_api.g_exception_error;
3927 END IF;
3928
3929 /* Manu 18-Aug-2004 Start
3930 Get the rollover fee lines for a contract and call
3931 validate_rollover_termQuote to validate the rollover fee line. */
3932
3933 -- Check for rebook contract
3934 l_rbk_khr := '?';
3935
3936 OPEN l_chk_rbk_csr (chrid => p_chr_id);
3937
3938 FETCH l_chk_rbk_csr
3939 INTO l_rbk_khr;
3940
3941 IF l_chk_rbk_csr%NOTFOUND THEN
3942 NULL;
3943 END IF;
3944
3945 CLOSE l_chk_rbk_csr;
3946
3947 IF (l_rbk_khr = '?') THEN
3948 -- This is a new Contract, booked for 1st time.
3949 OPEN l_rq_fee_lns_bkg_csr (chrid => p_chr_id);
3950
3951 LOOP
3952 FETCH l_rq_fee_lns_bkg_csr
3953 INTO l_qte_id;
3954
3955 IF (l_rq_fee_lns_bkg_csr%FOUND) THEN
3956 l_ro_fee_bkg_found := TRUE;
3957 okl_maintain_fee_pvt.validate_rollover_feeline
3958 (p_api_version => l_api_version,
3959 p_init_msg_list => p_init_msg_list,
3960 x_return_status => x_return_status,
3961 x_msg_count => x_msg_count,
3962 x_msg_data => x_msg_data,
3963 p_chr_id => p_chr_id,
3964 p_qte_id => l_qte_id,
3965 p_for_qa_check => FALSE
3966 );
3967
3968 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
3969 CLOSE l_rq_fee_lns_bkg_csr;
3970
3971 RAISE okl_api.g_exception_error;
3972 ELSIF (x_return_status = okl_api.g_ret_sts_success) THEN
3973 l_tq_rec_count := l_tq_rec_count + 1;
3974 p_term_tbl (l_tq_rec_count).ID := l_qte_id;
3975 p_term_tbl (l_tq_rec_count).accepted_yn := 'Y';
3976 p_term_tbl (l_tq_rec_count).date_effective_to :=
3977 SYSDATE;
3978 p_term_tbl (l_tq_rec_count).org_id :=
3979 okl_context.get_okc_org_id
3980 ();
3981 END IF;
3982 ELSIF (l_rq_fee_lns_bkg_csr%NOTFOUND) THEN
3983 EXIT;
3984 END IF;
3985 END LOOP;
3986
3987 CLOSE l_rq_fee_lns_bkg_csr;
3988
3989 /* Manu 18-Nov-2004 Start */
3990 /* Check if the if the contract has a rollover fee and it's start date
3991 is not in the future date (less than or equal sysdate). */
3992 OPEN l_k_std_csr (p_chr_id);
3993
3994 FETCH l_k_std_csr
3995 INTO l_found;
3996
3997 l_in_future := l_k_std_csr%FOUND; -- IN future
3998
3999 CLOSE l_k_std_csr;
4000
4001 IF (l_in_future AND l_ro_fee_bkg_found) THEN
4002 -- Contract Start date in future
4003 x_return_status := okl_api.g_ret_sts_error;
4004 l_in_future := NULL;
4005 l_found := NULL;
4006 okl_api.set_message
4007 (p_app_name => g_app_name,
4008 p_msg_name => 'OKL_LLA_RQ_SD_IN_FUTURE'
4009 );
4010 RAISE okl_api.g_exception_error;
4011 END IF;
4012
4013 l_ro_fee_bkg_found := FALSE;
4014 /* Manu 18-Nov-2004 End */
4015
4016 /* smereddy 22-Sep-2004 Start
4017 update the creditline contract with total rollover amount
4018 */
4019 l_qte_id := NULL;
4020
4021 OPEN l_rq_fee_lns_bkg_csr (chrid => p_chr_id);
4022
4023 FETCH l_rq_fee_lns_bkg_csr
4024 INTO l_qte_id;
4025
4026 CLOSE l_rq_fee_lns_bkg_csr;
4027
4028 -- check whether creditline exists
4029 l_creditline_id :=
4030 okl_credit_pub.get_creditline_by_chrid (p_chr_id);
4031
4032 IF (l_creditline_id IS NOT NULL AND l_qte_id IS NOT NULL) THEN
4033 -- creditline exists for the contract
4034 -- check whether tot rollover quote amount against the creditlimit exceeds
4035 okl_maintain_fee_pvt.rollover_fee
4036 (p_api_version => l_api_version,
4037 p_init_msg_list => p_init_msg_list,
4038 x_return_status => x_return_status,
4039 x_msg_count => x_msg_count,
4040 x_msg_data => x_msg_data,
4041 p_chr_id => p_chr_id,
4042 p_cl_id => l_creditline_id,
4043 x_rem_amt => x_rem_amt
4044 );
4045 END IF;
4046 /* Suresh 22-Sep-2004 End */
4047 ELSIF (l_rbk_khr = '!') THEN -- This is a Re-book Contract.
4048 OPEN l_rq_fee_lns_rbk_csr (chrid => p_chr_id);
4049
4050 LOOP
4051 FETCH l_rq_fee_lns_rbk_csr
4052 INTO l_qte_id;
4053
4054 IF (l_rq_fee_lns_rbk_csr%FOUND) THEN
4055 l_ro_fee_rbk_found := TRUE;
4056 okl_maintain_fee_pvt.validate_rollover_feeline
4057 (p_api_version => l_api_version,
4058 p_init_msg_list => p_init_msg_list,
4059 x_return_status => x_return_status,
4060 x_msg_count => x_msg_count,
4061 x_msg_data => x_msg_data,
4062 p_chr_id => p_chr_id,
4063 p_qte_id => l_qte_id
4064 );
4065
4066 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
4067 CLOSE l_rq_fee_lns_rbk_csr;
4068
4069 RAISE okl_api.g_exception_error;
4070 ELSIF (x_return_status = okl_api.g_ret_sts_success) THEN
4071 l_tq_rec_count := l_tq_rec_count + 1;
4072 p_term_tbl (l_tq_rec_count).ID := l_qte_id;
4073 p_term_tbl (l_tq_rec_count).accepted_yn := 'Y';
4074 p_term_tbl (l_tq_rec_count).date_effective_to :=
4075 SYSDATE;
4076 p_term_tbl (l_tq_rec_count).org_id :=
4077 okl_context.get_okc_org_id
4078 ();
4079 END IF;
4080 ELSIF (l_rq_fee_lns_rbk_csr%NOTFOUND) THEN
4081 EXIT;
4082 END IF;
4083 END LOOP;
4084
4085 CLOSE l_rq_fee_lns_rbk_csr;
4086
4087 /* Manu 18-Nov-2004 Start */
4088 /* Check if the if the contract has a rollover fee and it's start date
4089 is not in the future date (less than or equal sysdate). */
4090 OPEN l_k_std__4rbk_csr (p_chr_id);
4091
4092 FETCH l_k_std__4rbk_csr
4093 INTO l_found;
4094
4095 l_in_future := l_k_std__4rbk_csr%FOUND; -- IN future
4096
4097 CLOSE l_k_std__4rbk_csr;
4098
4099 IF (l_in_future AND l_ro_fee_rbk_found) THEN
4100 -- Contract Start date in future
4101 x_return_status := okl_api.g_ret_sts_error;
4102 l_in_future := NULL;
4103 l_found := NULL;
4104 okl_api.set_message
4105 (p_app_name => g_app_name,
4106 p_msg_name => 'OKL_LLA_RQ_SD_IN_FUTURE'
4107 );
4108 RAISE okl_api.g_exception_error;
4109 END IF;
4110
4111 l_ro_fee_rbk_found := FALSE;
4112 /* Manu 18-Nov-2004 End */
4113
4114 /* smereddy 22-Sep-2004 Start
4115 update the creditline contract with total rollover amount
4116 */
4117 l_qte_id := NULL;
4118
4119 OPEN l_rq_fee_lns_bkg_csr (chrid => p_chr_id);
4120
4121 FETCH l_rq_fee_lns_bkg_csr
4122 INTO l_qte_id;
4123
4124 CLOSE l_rq_fee_lns_bkg_csr;
4125
4126 -- check whether creditline exists
4127 l_creditline_id :=
4128 okl_credit_pub.get_creditline_by_chrid (p_chr_id);
4129
4130 IF (l_creditline_id IS NOT NULL AND l_qte_id IS NOT NULL) THEN
4131 -- creditline exists for the contract
4132 -- check whether tot rollover quote amount against the creditlimit exceeds
4133 okl_maintain_fee_pvt.rollover_fee
4134 (p_api_version => l_api_version,
4135 p_init_msg_list => p_init_msg_list,
4136 x_return_status => x_return_status,
4137 x_msg_count => x_msg_count,
4138 x_msg_data => x_msg_data,
4139 p_chr_id => p_chr_id,
4140 p_cl_id => l_creditline_id,
4141 x_rem_amt => x_rem_amt
4142 );
4143 END IF;
4144 /* Suresh 22-Sep-2004 End */
4145 END IF;
4146
4147 l_tq_rec_count := 0;
4148 /* Initiate the Terminate Quote Process */
4149 okl_am_termnt_quote_pvt.terminate_quote
4150 (p_api_version => l_api_version,
4151 p_init_msg_list => p_init_msg_list,
4152 x_return_status => x_return_status,
4153 x_msg_count => x_msg_count,
4154 x_msg_data => x_msg_data,
4155 p_term_tbl => p_term_tbl,
4156 x_term_tbl => x_term_tbl,
4157 x_err_msg => x_err_msg,
4158 p_acceptance_source => 'ROLLOVER'
4159 );
4160
4161 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
4162 RAISE okl_api.g_exception_error;
4163 END IF;
4164
4165 /* Manu 18-Aug-2004 End */
4166
4167 --rviriyal bug 5982201 start
4168 okl_qa_data_integrity.check_cust_active
4169 (x_return_status => x_return_status,
4170 p_chr_id => p_chr_id
4171 );
4172
4173 IF (x_return_status = okl_api.g_ret_sts_success) THEN
4174 okl_api.init_msg_list ('T');
4175 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4176 RAISE okl_api.g_exception_unexpected_error;
4177 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4178 RAISE okl_api.g_exception_error;
4179 END IF;
4180
4181 --rviriyal bug 5982201 end
4182 okl_activate_contract_pub.activate_contract
4183 (p_api_version => l_api_version,
4184 p_init_msg_list => p_init_msg_list,
4185 x_return_status => x_return_status,
4186 x_msg_count => x_msg_count,
4187 x_msg_data => x_msg_data,
4188 p_chrv_id => p_chr_id,
4189 p_call_mode => 'BOOK'
4190 );
4191
4192 -- check if activity started successfully
4193 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4194 RAISE okl_api.g_exception_unexpected_error;
4195 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4196 RAISE okl_api.g_exception_error;
4197 END IF;
4198
4199 IF (l_transaction_type = 'Booking')
4200 AND
4201 --Bug # 2927232 : was creating CASE for mass rebooks
4202 -- Added this additional and clause to fix that issue.
4203 (NVL (l_chk_mass_rbk_rec.what, 'N') = 'N') THEN
4204 okl_case_util_pvt.create_case
4205 (p_api_version => l_api_version,
4206 p_init_msg_list => p_init_msg_list,
4207 p_contract_id => TO_NUMBER
4208 (p_chr_id),
4209 x_return_status => x_return_status,
4210 x_msg_count => x_msg_count,
4211 x_msg_data => x_msg_data
4212 );
4213 -- added the call against bug # 2457920 for creating contract portfolio.
4214 okl_am_contract_prtfl_pub.create_cntrct_prtfl
4215 (p_api_version => l_api_version,
4216 p_init_msg_list => p_init_msg_list,
4217 x_return_status => x_return_status,
4218 x_msg_count => x_msg_count,
4219 x_msg_data => x_msg_data,
4220 p_contract_id => TO_NUMBER
4221 (p_chr_id)
4222 );
4223 /*
4224 OKL_INS_QUOTE_PUB.activate_ins_streams(
4225 p_api_version => l_api_version,
4226 p_init_msg_list => p_init_msg_list,
4227 x_return_status => x_return_status,
4228 x_msg_count => x_msg_count,
4229 x_msg_data => x_msg_data,
4230 p_contract_id => to_number(p_chr_id)
4231 );
4232
4233 */ -- Bug 4917614
4234 okl_k_rate_params_pvt.sync_base_rate
4235 (p_api_version => l_api_version,
4236 p_init_msg_list => p_init_msg_list,
4237 x_return_status => x_return_status,
4238 x_msg_count => x_msg_count,
4239 x_msg_data => x_msg_data,
4240 p_khr_id => TO_NUMBER
4241 (p_chr_id
4242 )
4243 );
4244
4245 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4246 RAISE okl_api.g_exception_unexpected_error;
4247 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4248 RAISE okl_api.g_exception_error;
4249 END IF;
4250
4251 x_return_status := okl_api.g_ret_sts_success;
4252 /*
4253 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
4254 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4255 --Bug#2393795-this call will not raise error as
4256 --not tested properly. So should not stop Booking
4257 --if this fails in PROD.
4258 --raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4259 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
4260 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4261 --raise OKL_API.G_EXCEPTION_ERROR;
4262 End If;
4263 */
4264 END IF;
4265 --ELSE 4895333
4266 --l_chr_for_sts_change := TO_NUMBER(p_chr_id);
4267 END IF;
4268 END IF; -- Vendor Program Template: End
4269
4270 -- Change Status
4271 IF (x_return_status = okl_api.g_ret_sts_success) THEN
4272 okl_contract_status_pub.update_contract_status
4273 (l_api_version,
4274 p_init_msg_list,
4275 x_return_status,
4276 x_msg_count,
4277 x_msg_data,
4278 l_passstatus,
4279 l_chr_for_sts_change
4280 );
4281 --p_chr_id );
4282 ELSE
4283 okl_contract_status_pub.update_contract_status
4284 (l_api_version,
4285 p_init_msg_list,
4286 x_return_status,
4287 x_msg_count,
4288 x_msg_data,
4289 l_failstatus,
4290 l_chr_for_sts_change
4291 );
4292
4293 --p_chr_id );
4294 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
4295 RAISE okl_api.g_exception_unexpected_error;
4296 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
4297 RAISE okl_api.g_exception_error;
4298 END IF;
4299 END IF;
4300
4301 -- 4895333
4302 IF (old_rec.deal_type <> 'LOAN-REVOLVING') THEN
4303 --call to cascade status on to lines
4304 okl_contract_status_pub.cascade_lease_status
4305 (p_api_version => p_api_version,
4306 p_init_msg_list => p_init_msg_list,
4307 x_return_status => x_return_status,
4308 x_msg_count => x_msg_count,
4309 x_msg_data => x_msg_data,
4310 p_chr_id => l_chr_for_sts_change
4311 );
4312
4313 --p_chr_id => p_chr_id);
4314 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4315 RAISE okl_api.g_exception_unexpected_error;
4316 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4317 RAISE okl_api.g_exception_error;
4318 END IF;
4319 ---
4320 END IF;
4321
4322 /*
4323
4324
4325 If ( old_rec.ORIG_SYSTEM_ID1 IS NOT NULL ) Then
4326 okl_contract_status_pub.update_contract_status(
4327 l_api_version,
4328 p_init_msg_list,
4329 x_return_status,
4330 x_msg_count,
4331 x_msg_data,
4332 'CANCELED',
4333 old_rec.ORIG_SYSTEM_ID1 );
4334
4335 OKL_CONTRACT_STATUS_PUB.cascade_lease_status
4336 (p_api_version => p_api_version,
4337 p_init_msg_list => p_init_msg_list,
4338 x_return_status => x_return_status,
4339 x_msg_count => x_msg_count,
4340 x_msg_data => x_msg_data,
4341 p_chr_id => old_rec.ORIG_SYSTEM_ID1 );
4342
4343 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4345 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
4346 RAISE OKL_API.G_EXCEPTION_ERROR;
4347 END IF;
4348 ---
4349
4350 End If;
4351 */
4352 okl_api.set_message (p_app_name => g_app_name,
4353 p_msg_name => 'OKL_LLA_AC_SUCCESS'
4354 );
4355 x_return_status := okl_api.g_ret_sts_success;
4356 /*
4357 -- mvasudev, 08/30/2004
4358 -- Code change to enable Business Event
4359 */
4360 raise_business_event (p_rbk_khr_id => l_rbk_khr_id,
4361 p_date_transaction_occurred => l_rbk_date,
4362 x_return_status => x_return_status
4363 );
4364
4365 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4366 RAISE okl_api.g_exception_unexpected_error;
4367 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4368 RAISE okl_api.g_exception_error;
4369 END IF;
4370 /*
4371 -- mvasudev, 08/30/2004
4372 -- END, Code change to enable Business Event
4373 */
4374 END IF; -- l_proceed_activation Y/N
4375
4376 okl_api.end_activity (x_msg_count => x_msg_count,
4377 x_msg_data => x_msg_data
4378 );
4379 EXCEPTION
4380 WHEN okl_api.g_exception_error THEN
4381 /* Manu 18-Aug-2004 Start Clean Up. */
4382 IF l_rq_fee_lns_bkg_csr%ISOPEN THEN
4383 CLOSE l_rq_fee_lns_bkg_csr;
4384 END IF;
4385
4386 IF l_rq_fee_lns_rbk_csr%ISOPEN THEN
4387 CLOSE l_rq_fee_lns_rbk_csr;
4388 END IF;
4389
4390 IF l_chk_rbk_csr%ISOPEN THEN
4391 CLOSE l_chk_rbk_csr;
4392 END IF;
4393
4394 /* Manu 18-Aug-2004 End */
4395
4396 /* Manu 18-Nov-2004 Start */
4397 IF l_k_std_csr%ISOPEN THEN
4398 CLOSE l_k_std_csr;
4399 END IF;
4400
4401 IF l_k_std__4rbk_csr%ISOPEN THEN
4402 CLOSE l_k_std__4rbk_csr;
4403 END IF;
4404
4405 /* Manu 18-Nov-2004 End */
4406
4407 --ramurt Bug#4622438
4408 IF chk_product_status%ISOPEN THEN
4409 CLOSE chk_product_status;
4410 END IF;
4411
4412 -- end
4413 x_return_status :=
4414 okl_api.handle_exceptions
4415 (p_api_name => l_api_name,
4416 p_pkg_name => g_pkg_name,
4417 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4418 x_msg_count => x_msg_count,
4419 x_msg_data => x_msg_data,
4420 p_api_type => g_api_type
4421 );
4422 WHEN okl_api.g_exception_unexpected_error THEN
4423 /* Manu 18-Aug-2004 Start Clean Up. */
4424 IF l_rq_fee_lns_bkg_csr%ISOPEN THEN
4425 CLOSE l_rq_fee_lns_bkg_csr;
4426 END IF;
4427
4428 IF l_rq_fee_lns_rbk_csr%ISOPEN THEN
4429 CLOSE l_rq_fee_lns_rbk_csr;
4430 END IF;
4431
4432 IF l_chk_rbk_csr%ISOPEN THEN
4433 CLOSE l_chk_rbk_csr;
4434 END IF;
4435
4436 /* Manu 18-Aug-2004 End */
4437
4438 /* Manu 18-Nov-2004 Start */
4439 IF l_k_std_csr%ISOPEN THEN
4440 CLOSE l_k_std_csr;
4441 END IF;
4442
4443 IF l_k_std__4rbk_csr%ISOPEN THEN
4444 CLOSE l_k_std__4rbk_csr;
4445 END IF;
4446
4447 /* Manu 18-Nov-2004 End */
4448 x_return_status :=
4449 okl_api.handle_exceptions
4450 (p_api_name => l_api_name,
4451 p_pkg_name => g_pkg_name,
4452 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4453 x_msg_count => x_msg_count,
4454 x_msg_data => x_msg_data,
4455 p_api_type => g_api_type
4456 );
4457 WHEN OTHERS THEN
4458 /* Manu 18-Aug-2004 Start Clean Up. */
4459 IF l_rq_fee_lns_bkg_csr%ISOPEN THEN
4460 CLOSE l_rq_fee_lns_bkg_csr;
4461 END IF;
4462
4463 IF l_rq_fee_lns_rbk_csr%ISOPEN THEN
4464 CLOSE l_rq_fee_lns_rbk_csr;
4465 END IF;
4466
4467 IF l_chk_rbk_csr%ISOPEN THEN
4468 CLOSE l_chk_rbk_csr;
4469 END IF;
4470
4471 /* Manu 18-Aug-2004 End */
4472
4473 /* Manu 18-Nov-2004 Start */
4474 IF l_k_std_csr%ISOPEN THEN
4475 CLOSE l_k_std_csr;
4476 END IF;
4477
4478 IF l_k_std__4rbk_csr%ISOPEN THEN
4479 CLOSE l_k_std__4rbk_csr;
4480 END IF;
4481
4482 /* Manu 18-Nov-2004 End */
4483 x_return_status :=
4484 okl_api.handle_exceptions (p_api_name => l_api_name,
4485 p_pkg_name => g_pkg_name,
4486 p_exc_name => 'OTHERS',
4487 x_msg_count => x_msg_count,
4488 x_msg_data => x_msg_data,
4489 p_api_type => g_api_type
4490 );
4491 END activate_contract;
4492
4493 ----------------------------------------------------------------
4494 --Bug# 3556674 : validate contract api to be called as an api to
4495 -- run qa check list
4496 -----------------------------------------------------------------
4497 PROCEDURE validate_contract (
4498 p_api_version IN NUMBER,
4499 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
4500 x_return_status OUT NOCOPY VARCHAR2,
4501 x_msg_count OUT NOCOPY NUMBER,
4502 x_msg_data OUT NOCOPY VARCHAR2,
4503 p_qcl_id IN NUMBER,
4504 p_chr_id IN NUMBER,
4505 p_call_mode IN VARCHAR2 DEFAULT 'ACTUAL',
4506 x_msg_tbl OUT NOCOPY okl_qa_check_pub.msg_tbl_type
4507 ) IS
4508 l_api_name CONSTANT VARCHAR2 (30) := 'VALIDATE_CONTRACT';
4509 l_api_version CONSTANT NUMBER := 1;
4510 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
4511
4512 --Cursor to get QA checklist id from contract header
4513 CURSOR l_chr_csr (p_chr_id IN NUMBER) IS
4514 SELECT chrb.qcl_id,
4515 stsv.ste_code,
4516 stsv.meaning
4517 FROM okc_k_headers_b chrb, okc_statuses_v stsv
4518 WHERE chrb.ID = p_chr_id
4519 AND chrb.sts_code = stsv.code
4520 AND chrb.scs_code = 'LEASE';
4521
4522 l_chr_rec l_chr_csr%ROWTYPE;
4523
4524 --Cursor to get QA checklist id from
4525 CURSOR l_qcl_csr (p_qclid IN NUMBER) IS
4526 SELECT ID
4527 FROM okc_qa_check_lists_v
4528 WHERE ID = p_qclid;
4529
4530 l_qcl_id okc_qa_check_lists_b.ID%TYPE;
4531 l_qclid okc_qa_check_lists_b.ID%TYPE
4532 DEFAULT 253090624152411882761357215253616454772;
4533 BEGIN
4534 x_return_status := okl_api.g_ret_sts_success;
4535 x_return_status :=
4536 okl_api.start_activity (p_api_name => l_api_name,
4537 p_pkg_name => g_pkg_name,
4538 p_init_msg_list => p_init_msg_list,
4539 l_api_version => l_api_version,
4540 p_api_version => p_api_version,
4541 p_api_type => g_api_type,
4542 x_return_status => x_return_status
4543 );
4544
4545 -- check if activity started successfully
4546 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4547 RAISE okl_api.g_exception_unexpected_error;
4548 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4549 RAISE okl_api.g_exception_error;
4550 END IF;
4551
4552 l_qcl_id := p_qcl_id;
4553 validate_chr_id (p_chr_id => p_chr_id,
4554 x_return_status => x_return_status
4555 );
4556
4557 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4558 RAISE okl_api.g_exception_unexpected_error;
4559 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4560 RAISE okl_api.g_exception_error;
4561 END IF;
4562
4563 OPEN l_chr_csr (p_chr_id => p_chr_id);
4564
4565 FETCH l_chr_csr
4566 INTO l_chr_rec;
4567
4568 IF l_chr_csr%NOTFOUND THEN
4569 --error : contract does not exist
4570 okl_api.set_message (g_app_name,
4571 g_invalid_value,
4572 g_col_name_token,
4573 'p_chr_id'
4574 );
4575 x_return_status := okl_api.g_ret_sts_error;
4576 RAISE okl_api.g_exception_error;
4577 END IF;
4578
4579 CLOSE l_chr_csr;
4580
4581 IF l_chr_rec.ste_code NOT IN ('ENTERED', 'SIGNED') THEN
4582 --error : Contract with status can not be validated.
4583 okl_api.set_message (g_app_name,
4584 'OKL_LA_CAN_NOT_QA',
4585 'STATUS',
4586 l_chr_rec.meaning
4587 );
4588 RAISE okl_api.g_exception_error;
4589 END IF;
4590
4591 IF l_qcl_id IS NULL OR l_qcl_id = okl_api.g_miss_num THEN
4592 --get qcl_id from k hdr
4593 l_qcl_id := l_chr_rec.qcl_id;
4594 END IF;
4595
4596 IF l_qcl_id IS NULL OR l_qcl_id = okl_api.g_miss_num THEN
4597 --get seeded QCL id
4598 OPEN l_qcl_csr (p_qclid => l_qclid);
4599
4600 FETCH l_qcl_csr
4601 INTO l_qcl_id;
4602
4603 IF l_qcl_csr%NOTFOUND THEN
4604 NULL;
4605 END IF;
4606
4607 CLOSE l_qcl_csr;
4608 END IF;
4609
4610 IF l_qcl_id IS NOT NULL AND l_qcl_id <> okl_api.g_miss_num THEN
4611 execute_qa_check_list (p_api_version => p_api_version,
4612 p_init_msg_list => p_init_msg_list,
4613 x_return_status => x_return_status,
4614 x_msg_count => x_msg_count,
4615 x_msg_data => x_msg_data,
4616 p_qcl_id => l_qcl_id,
4617 p_chr_id => p_chr_id,
4618 p_call_mode => p_call_mode,
4619 x_msg_tbl => x_msg_tbl
4620 );
4621
4622 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4623 RAISE okl_api.g_exception_unexpected_error;
4624 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4625 RAISE okl_api.g_exception_error;
4626 END IF;
4627 ELSIF l_qcl_id IS NULL OR l_qcl_id = okl_api.g_miss_num THEN
4628 --error
4629 okl_api.set_message (g_app_name,
4630 g_invalid_value,
4631 g_col_name_token,
4632 'p_qcl_id'
4633 );
4634 x_return_status := okl_api.g_ret_sts_error;
4635 RAISE okl_api.g_exception_error;
4636 END IF;
4637
4638 okl_api.end_activity (x_msg_count => x_msg_count,
4639 x_msg_data => x_msg_data
4640 );
4641 ---
4642 EXCEPTION
4643 WHEN okl_api.g_exception_error THEN
4644 x_return_status :=
4645 okl_api.handle_exceptions
4646 (p_api_name => l_api_name,
4647 p_pkg_name => g_pkg_name,
4648 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4649 x_msg_count => x_msg_count,
4650 x_msg_data => x_msg_data,
4651 p_api_type => g_api_type
4652 );
4653 WHEN okl_api.g_exception_unexpected_error THEN
4654 x_return_status :=
4655 okl_api.handle_exceptions
4656 (p_api_name => l_api_name,
4657 p_pkg_name => g_pkg_name,
4658 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4659 x_msg_count => x_msg_count,
4660 x_msg_data => x_msg_data,
4661 p_api_type => g_api_type
4662 );
4663 WHEN OTHERS THEN
4664 x_return_status :=
4665 okl_api.handle_exceptions (p_api_name => l_api_name,
4666 p_pkg_name => g_pkg_name,
4667 p_exc_name => 'OTHERS',
4668 x_msg_count => x_msg_count,
4669 x_msg_data => x_msg_data,
4670 p_api_type => g_api_type
4671 );
4672 END validate_contract;
4673
4674 ----------------------------------------------------------------
4675 --Bug# 3556674 : generate_draft_accounting to be called as an api to
4676 -- generate draft 'Booking' accounting entries
4677 -----------------------------------------------------------------
4678 PROCEDURE generate_draft_accounting (
4679 p_api_version IN NUMBER,
4680 p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
4681 x_return_status OUT NOCOPY VARCHAR2,
4682 x_msg_count OUT NOCOPY NUMBER,
4683 x_msg_data OUT NOCOPY VARCHAR2,
4684 p_chr_id IN NUMBER
4685 ) IS
4686 l_api_name CONSTANT VARCHAR2 (30) := 'GENERATE_DRAFT_ACCT';
4687 l_api_version CONSTANT NUMBER := 1;
4688 l_return_status VARCHAR2 (1) := okl_api.g_ret_sts_success;
4689 l_booking_trx_type okl_trx_types_tl.NAME%TYPE DEFAULT 'Booking';
4690 BEGIN
4691 x_return_status := okl_api.g_ret_sts_success;
4692 x_return_status :=
4693 okl_api.start_activity (p_api_name => l_api_name,
4694 p_pkg_name => g_pkg_name,
4695 p_init_msg_list => p_init_msg_list,
4696 l_api_version => l_api_version,
4697 p_api_version => p_api_version,
4698 p_api_type => g_api_type,
4699 x_return_status => x_return_status
4700 );
4701
4702 -- check if activity started successfully
4703 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4704 RAISE okl_api.g_exception_unexpected_error;
4705 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4706 RAISE okl_api.g_exception_error;
4707 END IF;
4708
4709 --1. validate chr id
4710 validate_chr_id (p_chr_id => p_chr_id,
4711 x_return_status => x_return_status
4712 );
4713
4714 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4715 RAISE okl_api.g_exception_unexpected_error;
4716 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4717 RAISE okl_api.g_exception_error;
4718 END IF;
4719
4720 --2. call api for generating journal entries
4721 generate_journal_entries (p_api_version => p_api_version,
4722 p_init_msg_list => p_init_msg_list,
4723 p_commit => okl_api.g_false,
4724 p_contract_id => p_chr_id,
4725 p_transaction_type => l_booking_trx_type,
4726 p_draft_yn => okl_api.g_true,
4727 x_return_status => x_return_status,
4728 x_msg_count => x_msg_count,
4729 x_msg_data => x_msg_data
4730 );
4731
4732 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4733 RAISE okl_api.g_exception_unexpected_error;
4734 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4735 RAISE okl_api.g_exception_error;
4736 END IF;
4737
4738 okl_api.end_activity (x_msg_count => x_msg_count,
4739 x_msg_data => x_msg_data
4740 );
4741 EXCEPTION
4742 WHEN okl_api.g_exception_error THEN
4743 x_return_status :=
4744 okl_api.handle_exceptions
4745 (p_api_name => l_api_name,
4746 p_pkg_name => g_pkg_name,
4747 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
4748 x_msg_count => x_msg_count,
4749 x_msg_data => x_msg_data,
4750 p_api_type => g_api_type
4751 );
4752 WHEN okl_api.g_exception_unexpected_error THEN
4753 x_return_status :=
4754 okl_api.handle_exceptions
4755 (p_api_name => l_api_name,
4756 p_pkg_name => g_pkg_name,
4757 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
4758 x_msg_count => x_msg_count,
4759 x_msg_data => x_msg_data,
4760 p_api_type => g_api_type
4761 );
4762 WHEN OTHERS THEN
4763 x_return_status :=
4764 okl_api.handle_exceptions (p_api_name => l_api_name,
4765 p_pkg_name => g_pkg_name,
4766 p_exc_name => 'OTHERS',
4767 x_msg_count => x_msg_count,
4768 x_msg_data => x_msg_data,
4769 p_api_type => g_api_type
4770 );
4771 END generate_draft_accounting;
4772
4773 -----------------------------------------------------------------------------
4774 -- PROCEDURE calculate_upfront_tax
4775 -----------------------------------------------------------------------------
4776 -- Start of comments
4777 --
4778 -- Procedure Name : calculate_upfront_tax
4779 -- Description : Procedure will be called to calculate upfront tax during
4780 -- online and batch contract activation.
4781 -- Business Rules :
4782 -- Parameters : p_chr_id
4783 -- Version : 1.0
4784 -- History : 24-Apr-2007 rpillay Created
4785 -- End of comments
4786 PROCEDURE calculate_upfront_tax (
4787 p_api_version IN NUMBER,
4788 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
4789 x_return_status OUT NOCOPY VARCHAR2,
4790 x_msg_count OUT NOCOPY NUMBER,
4791 x_msg_data OUT NOCOPY VARCHAR2,
4792 p_chr_id IN VARCHAR2,
4793 x_process_status OUT NOCOPY VARCHAR2
4794 ) IS
4795 l_api_name CONSTANT VARCHAR2 (30) := 'CALCULATE_UPFRONT_TAX';
4796 l_api_version CONSTANT NUMBER := 1.0;
4797
4798 -- check whether this contract is rebook contract
4799 CURSOR l_chk_rbk_csr (p_chr_id IN NUMBER) IS
4800 SELECT '!',
4801 CHR.orig_system_id1,
4802 ktrx.date_transaction_occurred,
4803 ktrx.ID
4804 FROM okc_k_headers_b CHR, okl_trx_contracts ktrx
4805 WHERE ktrx.khr_id_new = CHR.ID
4806 AND ktrx.tsu_code = 'ENTERED'
4807 AND ktrx.rbr_code IS NOT NULL
4808 AND ktrx.tcn_type = 'TRBK'
4809 --rkuttiya added for 12.1.1 Multi GAAP Project
4810 AND ktrx.representation_type = 'PRIMARY'
4811 --
4812 AND CHR.ID = p_chr_id
4813 AND CHR.orig_system_source_code = 'OKL_REBOOK';
4814
4815 -- Bug 6157438
4816 --cursor to check if the contract is selected for Mass Rebook
4817 CURSOR l_chk_mass_rbk_csr (p_chr_id IN NUMBER) IS
4818 SELECT '!'
4819 FROM okc_k_headers_b CHR, okl_trx_contracts ktrx
4820 WHERE CHR.ID = p_chr_id
4821 AND ktrx.khr_id = CHR.ID
4822 AND ktrx.tsu_code = 'ENTERED'
4823 AND ktrx.rbr_code IS NOT NULL
4824 AND ktrx.tcn_type = 'TRBK'
4825 -- rkuttiya added for 12.1.1 Multi GAAP Project
4826 AND ktrx.representation_type = 'PRIMARY'
4827 --
4828 AND EXISTS (
4829 SELECT '1'
4830 FROM okl_rbk_selected_contract rbk_khr
4831 WHERE rbk_khr.khr_id = CHR.ID
4832 AND rbk_khr.status <> 'PROCESSED');
4833
4834 l_rbk_khr VARCHAR2 (1) := '?';
4835 l_mass_rbk_khr VARCHAR2 (1) := '?';
4836 l_orig_khr_id NUMBER;
4837 l_transaction_id NUMBER;
4838 l_rebook_date DATE;
4839 l_upfront_tax_prog_sts okl_book_controller_trx.progress_status%TYPE;
4840
4841 --Bug# 6512668
4842 CURSOR sys_param_csr IS
4843 SELECT NVL (tax_upfront_yn, 'N')
4844 FROM okl_system_params;
4845
4846 l_upfront_tax_yn VARCHAR2 (1);
4847
4848 CURSOR check_st_fee_csr (p_chr_id IN NUMBER) IS
4849 SELECT cle.ID
4850 FROM okc_k_lines_b cle, okl_k_lines kle
4851 WHERE cle.ID = kle.ID
4852 AND cle.dnz_chr_id = p_chr_id
4853 AND cle.chr_id = p_chr_id
4854 AND kle.fee_purpose_code = 'SALESTAX'
4855 AND cle.sts_code <> 'ABANDONED';
4856
4857 l_del_fee_line_id okc_k_lines_b.ID%TYPE;
4858 l_del_fee_types_rec okl_maintain_fee_pvt.fee_types_rec_type;
4859 --Bug# 6512668
4860 BEGIN
4861 x_process_status := okl_api.g_ret_sts_success;
4862 x_return_status :=
4863 okl_api.start_activity (p_api_name => l_api_name,
4864 p_pkg_name => g_pkg_name,
4865 p_init_msg_list => p_init_msg_list,
4866 l_api_version => l_api_version,
4867 p_api_version => p_api_version,
4868 p_api_type => g_api_type,
4869 x_return_status => x_return_status
4870 );
4871
4872 -- check if activity started successfully
4873 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4874 RAISE okl_api.g_exception_unexpected_error;
4875 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4876 RAISE okl_api.g_exception_error;
4877 END IF;
4878
4879 l_upfront_tax_prog_sts := okl_book_controller_pvt.g_prog_sts_complete;
4880
4881 IF (g_debug_enabled = 'Y') THEN
4882 g_is_debug_statement_on :=
4883 okl_debug_pub.check_log_on (g_module, fnd_log.level_statement);
4884 END IF;
4885
4886 --check for rebook contract
4887 l_rbk_khr := '?';
4888 l_orig_khr_id := NULL;
4889 l_transaction_id := NULL;
4890
4891 OPEN l_chk_rbk_csr (p_chr_id => p_chr_id);
4892
4893 FETCH l_chk_rbk_csr
4894 INTO l_rbk_khr,
4895 l_orig_khr_id,
4896 l_rebook_date,
4897 l_transaction_id;
4898
4899 IF l_chk_rbk_csr%NOTFOUND THEN
4900 NULL;
4901 END IF;
4902
4903 CLOSE l_chk_rbk_csr;
4904
4905 -- Bug 6157438
4906 -- check for mass rebook contract
4907 l_mass_rbk_khr := '?';
4908
4909 OPEN l_chk_mass_rbk_csr (p_chr_id => p_chr_id);
4910
4911 FETCH l_chk_mass_rbk_csr
4912 INTO l_mass_rbk_khr;
4913
4914 IF l_chk_mass_rbk_csr%NOTFOUND THEN
4915 NULL;
4916 END IF;
4917
4918 CLOSE l_chk_mass_rbk_csr;
4919
4920 IF (l_rbk_khr = '!') THEN
4921 IF (g_is_debug_statement_on = TRUE) THEN
4922 okl_debug_pub.log_debug (fnd_log.level_statement,
4923 g_module,
4924 'Rebook, Orig :' || l_orig_khr_id
4925 );
4926 END IF;
4927
4928 -- Rebook
4929 -- Bug 4769822 - START
4930 okl_la_sales_tax_pvt.process_sales_tax
4931 (p_api_version => p_api_version,
4932 p_init_msg_list => p_init_msg_list,
4933 p_commit => okl_api.g_false,
4934 p_contract_id => l_orig_khr_id,
4935 p_transaction_type => 'Pre-Rebook',
4936 p_transaction_id => l_transaction_id,
4937 -- R12 change NULL to l_transaction_id
4938 p_transaction_date => l_rebook_date,
4939 -- R12 change NULL to l_rebook_date
4940 p_rbk_contract_id => p_chr_id,
4941 x_return_status => x_return_status,
4942 x_msg_count => x_msg_count,
4943 x_msg_data => x_msg_data
4944 );
4945
4946 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
4947 RAISE okl_api.g_exception_unexpected_error;
4948 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
4949 RAISE okl_api.g_exception_error;
4950 END IF;
4951
4952 -- Bug 4769822 - END
4953 okl_la_sales_tax_pvt.validate_upfront_tax_fee
4954 (p_api_version => p_api_version,
4955 p_init_msg_list => p_init_msg_list,
4956 x_return_status => x_return_status,
4957 x_msg_count => x_msg_count,
4958 x_msg_data => x_msg_data,
4959 p_chr_id => p_chr_id
4960 );
4961
4962 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
4963 l_upfront_tax_prog_sts :=
4964 okl_book_controller_pvt.g_prog_sts_error;
4965 x_process_status := okl_api.g_ret_sts_error;
4966 x_return_status := okl_api.g_ret_sts_success;
4967 ELSE
4968 l_upfront_tax_prog_sts :=
4969 okl_book_controller_pvt.g_prog_sts_complete;
4970 END IF;
4971 ELSIF (l_mass_rbk_khr = '!') THEN
4972 NULL;
4973
4974 IF (g_is_debug_statement_on = TRUE) THEN
4975 okl_debug_pub.log_debug (fnd_log.level_statement,
4976 g_module,
4977 'Mass-Rebook, Orig :' || p_chr_id
4978 );
4979 END IF;
4980 -- Mass-rebook
4981 ELSE
4982 -- authoring
4983 IF (g_is_debug_statement_on = TRUE) THEN
4984 okl_debug_pub.log_debug (fnd_log.level_statement,
4985 g_module,
4986 'Authoring : ' || p_chr_id
4987 );
4988 END IF;
4989
4990 okl_la_sales_tax_pvt.process_sales_tax
4991 (p_api_version => p_api_version,
4992 p_init_msg_list => p_init_msg_list,
4993 p_commit => okl_api.g_false,
4994 p_contract_id => p_chr_id,
4995 p_transaction_type => 'Pre-Booking',
4996 p_transaction_id => NULL,
4997 p_transaction_date => NULL,
4998 p_rbk_contract_id => NULL,
4999 x_return_status => x_return_status,
5000 x_msg_count => x_msg_count,
5001 x_msg_data => x_msg_data
5002 );
5003
5004 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5005 RAISE okl_api.g_exception_unexpected_error;
5006 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5007 RAISE okl_api.g_exception_error;
5008 END IF;
5009
5010 -- Bug# 6512668: Delete Upfront Tax Fee line if Upfront
5011 -- Tax System Option is set to 'N'
5012 OPEN sys_param_csr;
5013
5014 FETCH sys_param_csr
5015 INTO l_upfront_tax_yn;
5016
5017 CLOSE sys_param_csr;
5018
5019 IF l_upfront_tax_yn = 'N' THEN
5020 -- Check if Sales Tax Fee exists
5021 OPEN check_st_fee_csr (p_chr_id => p_chr_id);
5022
5023 FETCH check_st_fee_csr
5024 INTO l_del_fee_line_id;
5025
5026 CLOSE check_st_fee_csr;
5027
5028 IF (l_del_fee_line_id IS NOT NULL) THEN
5029 l_del_fee_types_rec.line_id := l_del_fee_line_id;
5030 l_del_fee_types_rec.dnz_chr_id := p_chr_id;
5031 -- delete fee line
5032 okl_maintain_fee_pvt.delete_fee_type
5033 (p_api_version => p_api_version,
5034 p_init_msg_list => p_init_msg_list,
5035 x_return_status => x_return_status,
5036 x_msg_count => x_msg_count,
5037 x_msg_data => x_msg_data,
5038 p_fee_types_rec => l_del_fee_types_rec
5039 );
5040
5041 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5042 RAISE okl_api.g_exception_unexpected_error;
5043 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5044 RAISE okl_api.g_exception_error;
5045 END IF;
5046 END IF;
5047 END IF;
5048
5049 okl_la_sales_tax_pvt.validate_upfront_tax_fee
5050 (p_api_version => p_api_version,
5051 p_init_msg_list => p_init_msg_list,
5052 x_return_status => x_return_status,
5053 x_msg_count => x_msg_count,
5054 x_msg_data => x_msg_data,
5055 p_chr_id => p_chr_id
5056 );
5057
5058 IF (x_return_status <> okl_api.g_ret_sts_success) THEN
5059 l_upfront_tax_prog_sts :=
5060 okl_book_controller_pvt.g_prog_sts_error;
5061 x_process_status := okl_api.g_ret_sts_error;
5062 x_return_status := okl_api.g_ret_sts_success;
5063 ELSE
5064 l_upfront_tax_prog_sts :=
5065 okl_book_controller_pvt.g_prog_sts_complete;
5066 END IF;
5067 END IF;
5068
5069 --Update Contract Status to Passed
5070 okl_contract_status_pub.update_contract_status
5071 (p_api_version => p_api_version,
5072 p_init_msg_list => p_init_msg_list,
5073 x_return_status => x_return_status,
5074 x_msg_count => x_msg_count,
5075 x_msg_data => x_msg_data,
5076 p_khr_status => 'PASSED',
5077 p_chr_id => p_chr_id
5078 );
5079
5080 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5081 RAISE okl_api.g_exception_unexpected_error;
5082 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5083 RAISE okl_api.g_exception_error;
5084 END IF;
5085
5086 --call to cascade status on to lines
5087 okl_contract_status_pub.cascade_lease_status
5088 (p_api_version => p_api_version,
5089 p_init_msg_list => p_init_msg_list,
5090 x_return_status => x_return_status,
5091 x_msg_count => x_msg_count,
5092 x_msg_data => x_msg_data,
5093 p_chr_id => p_chr_id
5094 );
5095
5096 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5097 RAISE okl_api.g_exception_unexpected_error;
5098 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5099 RAISE okl_api.g_exception_error;
5100 END IF;
5101
5102 -- Update status of Validate Contract process to Complete
5103 okl_book_controller_pvt.update_book_controller_trx
5104 (p_api_version => p_api_version,
5105 p_init_msg_list => p_init_msg_list,
5106 x_return_status => x_return_status,
5107 x_msg_count => x_msg_count,
5108 x_msg_data => x_msg_data,
5109 p_khr_id => p_chr_id,
5110 p_prog_short_name => okl_book_controller_pvt.g_validate_contract,
5111 p_progress_status => okl_book_controller_pvt.g_prog_sts_complete
5112 );
5113
5114 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5115 RAISE okl_api.g_exception_unexpected_error;
5116 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5117 RAISE okl_api.g_exception_error;
5118 END IF;
5119
5120 okl_book_controller_pvt.update_book_controller_trx
5121 (p_api_version => p_api_version,
5122 p_init_msg_list => p_init_msg_list,
5123 x_return_status => x_return_status,
5124 x_msg_count => x_msg_count,
5125 x_msg_data => x_msg_data,
5126 p_khr_id => p_chr_id,
5127 p_prog_short_name => okl_book_controller_pvt.g_calc_upfront_tax,
5128 p_progress_status => l_upfront_tax_prog_sts
5129 );
5130
5131 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5132 RAISE okl_api.g_exception_unexpected_error;
5133 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5134 RAISE okl_api.g_exception_error;
5135 END IF;
5136
5137 okl_api.end_activity (x_msg_count => x_msg_count,
5138 x_msg_data => x_msg_data
5139 );
5140 EXCEPTION
5141 WHEN okl_api.g_exception_error THEN
5142 x_return_status :=
5143 okl_api.handle_exceptions
5144 (p_api_name => l_api_name,
5145 p_pkg_name => g_pkg_name,
5146 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
5147 x_msg_count => x_msg_count,
5148 x_msg_data => x_msg_data,
5149 p_api_type => g_api_type
5150 );
5151 x_process_status := okl_api.g_ret_sts_error;
5152 WHEN okl_api.g_exception_unexpected_error THEN
5153 x_return_status :=
5154 okl_api.handle_exceptions
5155 (p_api_name => l_api_name,
5156 p_pkg_name => g_pkg_name,
5157 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
5158 x_msg_count => x_msg_count,
5159 x_msg_data => x_msg_data,
5160 p_api_type => g_api_type
5161 );
5162 x_process_status := okl_api.g_ret_sts_error;
5163 WHEN OTHERS THEN
5164 x_return_status :=
5165 okl_api.handle_exceptions (p_api_name => l_api_name,
5166 p_pkg_name => g_pkg_name,
5167 p_exc_name => 'OTHERS',
5168 x_msg_count => x_msg_count,
5169 x_msg_data => x_msg_data,
5170 p_api_type => g_api_type
5171 );
5172 x_process_status := okl_api.g_ret_sts_error;
5173 END calculate_upfront_tax;
5174
5175 -----------------------------------------------------------------------------
5176 -- PROCEDURE approve_activate_contract
5177 -----------------------------------------------------------------------------
5178 -- Start of comments
5179 --
5180 -- Procedure Name : approve_activate_contract
5181 -- Description : Procedure will be called from Submit button on Contract Booking UI and
5182 -- from OKL_CONTRACT_BOOK_PVT.post_approval_process and Batch booking.
5183 -- This procedure will submit the contract for approval.
5184 -- If the contract has been approved, this will process contract activation
5185 -- Business Rules :
5186 -- Parameters : p_chr_id
5187 -- Version : 1.0
5188 -- History : 24-Apr-2007 rpillay Created
5189 -- End of comments
5190 PROCEDURE approve_activate_contract (
5191 p_api_version IN NUMBER,
5192 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
5193 x_return_status OUT NOCOPY VARCHAR2,
5194 x_msg_count OUT NOCOPY NUMBER,
5195 x_msg_data OUT NOCOPY VARCHAR2,
5196 p_chr_id IN VARCHAR2,
5197 x_process_status OUT NOCOPY VARCHAR2
5198 ) IS
5199 l_api_name CONSTANT VARCHAR2 (30)
5200 := 'APPROVE_ACTIVATE_CONTRACT';
5201 l_api_version CONSTANT NUMBER := 1.0;
5202
5203 --cursor to fetch the contract status
5204 CURSOR sts_code_csr (p_khr_id okc_k_headers_b.ID%TYPE) IS
5205 SELECT sts_code
5206 FROM okc_k_headers_b
5207 WHERE ID = p_khr_id;
5208
5209 --cursor to fetch quote number to check for re-leasing
5210 CURSOR get_term_qte_num (p_khr_id okc_k_headers_b.ID%TYPE) IS
5211 SELECT qte.quote_number
5212 FROM okl_trx_contracts tcn,
5213 okl_trx_types_tl try,
5214 okl_trx_quotes_b qte,
5215 okc_k_headers_b CHR
5216 WHERE tcn.khr_id_old = CHR.orig_system_id1
5217 AND tcn.khr_id_new = CHR.ID
5218 AND tcn_type = 'MAE'
5219 AND tcn.tsu_code <> 'PROCESSED'
5220 AND tcn.try_id = try.ID
5221 --rkuttiya added for 12.1.1 Multi GAAP Project
5222 AND tcn.representation_type = 'PRIMARY'
5223 --
5224 AND try.NAME = 'Release'
5225 AND try.LANGUAGE = 'US'
5226 AND tcn.qte_id = qte.ID
5227 AND CHR.ID = p_khr_id;
5228
5229 l_rem_amt NUMBER;
5230 l_sts_code okc_k_headers_b.sts_code%TYPE;
5231 l_qte_num okl_trx_quotes_b.quote_number%TYPE;
5232 l_approval_path VARCHAR2 (30);
5233 contract_activation_failed EXCEPTION;
5234
5235 -- Bug# 5038395
5236 CURSOR l_chk_mass_rbk_csr (p_chr_id IN NUMBER) IS
5237 SELECT 'Y' mass_rbk_yn
5238 FROM okc_k_headers_b CHR
5239 WHERE CHR.ID = p_chr_id
5240 AND EXISTS (
5241 SELECT '1'
5242 FROM okl_trx_contracts ktrx
5243 WHERE ktrx.khr_id = CHR.ID
5244 AND ktrx.tsu_code = 'ENTERED'
5245 AND ktrx.rbr_code IS NOT NULL
5246 AND ktrx.tcn_type = 'TRBK'
5247 --rkuttiya added for 12.1.1 Multi GAAP Project
5248 AND ktrx.representation_type = 'PRIMARY')
5249 --
5250 AND EXISTS (
5251 SELECT '1'
5252 FROM okl_rbk_selected_contract rbk_khr
5253 WHERE rbk_khr.khr_id = CHR.ID
5254 AND rbk_khr.status <> 'PROCESSED');
5255
5256 l_chk_mass_rbk_rec l_chk_mass_rbk_csr%ROWTYPE;
5257 BEGIN
5258 x_process_status := okl_api.g_ret_sts_success;
5259 x_return_status :=
5260 okl_api.start_activity (p_api_name => l_api_name,
5261 p_pkg_name => g_pkg_name,
5262 p_init_msg_list => p_init_msg_list,
5263 l_api_version => l_api_version,
5264 p_api_version => p_api_version,
5265 p_api_type => g_api_type,
5266 x_return_status => x_return_status
5267 );
5268
5269 -- check if activity started successfully
5270 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5271 RAISE okl_api.g_exception_unexpected_error;
5272 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5273 RAISE okl_api.g_exception_error;
5274 END IF;
5275
5276 --fetch contract status code
5277 OPEN sts_code_csr (p_chr_id);
5278
5279 FETCH sts_code_csr
5280 INTO l_sts_code;
5281
5282 CLOSE sts_code_csr;
5283
5284 -- Bug# 5038395
5285 -- Check if Mass rebook is in progress
5286 OPEN l_chk_mass_rbk_csr (TO_NUMBER (p_chr_id));
5287
5288 FETCH l_chk_mass_rbk_csr
5289 INTO l_chk_mass_rbk_rec;
5290
5291 CLOSE l_chk_mass_rbk_csr;
5292
5293 -- Bug# 5038395
5294 -- If Mass Rebook not in progress, then do regular contract activation
5295 IF (NVL (l_chk_mass_rbk_rec.mass_rbk_yn, 'N') = 'N') THEN
5296 IF l_sts_code <> 'APPROVED' THEN
5297 --call program to submit for approval
5298 okl_contract_book_pub.submit_for_approval
5299 (p_api_version => p_api_version,
5300 p_init_msg_list => p_init_msg_list,
5301 x_return_status => x_return_status,
5302 x_msg_count => x_msg_count,
5303 x_msg_data => x_msg_data,
5304 p_chr_id => p_chr_id
5305 );
5306
5307 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5308 RAISE okl_api.g_exception_unexpected_error;
5309 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5310 RAISE okl_api.g_exception_error;
5311 END IF;
5312
5313 --read profile for approval path
5314 l_approval_path :=
5315 NVL (fnd_profile.VALUE ('OKL_LEASE_CONTRACT_APPROVAL_PROCESS'),
5316 'NONE'
5317 );
5318 END IF;
5319
5320 IF (l_sts_code = 'APPROVED') OR (l_approval_path = 'NONE') THEN
5321 --call program for contract activation
5322 okl_contract_book_pub.activate_contract
5323 (p_api_version => p_api_version,
5324 p_init_msg_list => p_init_msg_list,
5325 x_return_status => x_return_status,
5326 x_msg_count => x_msg_count,
5327 x_msg_data => x_msg_data,
5328 p_chr_id => p_chr_id
5329 );
5330
5331 IF (x_return_status IN
5332 (okl_api.g_ret_sts_unexp_error, okl_api.g_ret_sts_error)
5333 ) THEN
5334 RAISE contract_activation_failed;
5335 END IF;
5336
5337 --get rollover fee amount
5338 okl_maintain_fee_pvt.rollover_fee
5339 (p_api_version => p_api_version,
5340 p_init_msg_list => p_init_msg_list,
5341 x_return_status => x_return_status,
5342 x_msg_count => x_msg_count,
5343 x_msg_data => x_msg_data,
5344 p_chr_id => p_chr_id,
5345 x_rem_amt => l_rem_amt
5346 );
5347
5348 IF (x_return_status IN
5349 (okl_api.g_ret_sts_unexp_error, okl_api.g_ret_sts_error)
5350 ) THEN
5351 RAISE contract_activation_failed;
5352 END IF;
5353
5354 --fetch contract status code
5355 OPEN sts_code_csr (p_chr_id);
5356
5357 FETCH sts_code_csr
5358 INTO l_sts_code;
5359
5360 CLOSE sts_code_csr;
5361
5362 IF (l_sts_code IS NOT NULL AND l_sts_code = 'APPROVED') THEN
5363 --checking for re-lease processing
5364 OPEN get_term_qte_num (p_chr_id);
5365
5366 FETCH get_term_qte_num
5367 INTO l_qte_num;
5368
5369 IF get_term_qte_num%NOTFOUND THEN
5370 l_qte_num := NULL;
5371 END IF;
5372
5373 CLOSE get_term_qte_num;
5374
5375 okl_api.set_message
5376 (p_app_name => g_app_name,
5377 p_msg_name => 'OKL_LLA_REL_TERMN_NO_COMPLETE',
5378 p_token1 => 'QUOTE_NUM',
5379 p_token1_value => l_qte_num
5380 );
5381 RAISE contract_activation_failed;
5382 ELSIF ( l_rem_amt IS NOT NULL
5383 AND l_rem_amt <> okl_api.g_miss_num
5384 AND ROUND (l_rem_amt) < 0
5385 ) THEN
5386 --rollover fee amount warning
5387 okl_api.set_message (p_app_name => g_app_name,
5388 p_msg_name => 'OKL_ROLL_QT_WRNG'
5389 );
5390 x_process_status := okl_api.g_ret_sts_warning;
5391 END IF;
5392
5393 okl_book_controller_pvt.update_book_controller_trx
5394 (p_api_version => p_api_version,
5395 p_init_msg_list => okl_api.g_false,
5396 --To retain message stack
5397 x_return_status => x_return_status,
5398 x_msg_count => x_msg_count,
5399 x_msg_data => x_msg_data,
5400 p_khr_id => p_chr_id,
5401 p_prog_short_name => okl_book_controller_pvt.g_submit_contract,
5402 p_progress_status => okl_book_controller_pvt.g_prog_sts_complete
5403 );
5404
5405 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5406 RAISE okl_api.g_exception_unexpected_error;
5407 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5408 RAISE okl_api.g_exception_error;
5409 END IF;
5410 END IF;
5411 -- Bug# 5038395
5412 -- If Mass Rebook is in progress, then do mass rebook activation
5413 ELSE
5414 okl_mass_rebook_pvt.mass_rebook_activate
5415 (p_api_version => p_api_version,
5416 p_init_msg_list => p_init_msg_list,
5417 x_return_status => x_return_status,
5418 x_msg_count => x_msg_count,
5419 x_msg_data => x_msg_data,
5420 p_chr_id => p_chr_id
5421 );
5422
5423 IF (x_return_status IN
5424 (okl_api.g_ret_sts_unexp_error, okl_api.g_ret_sts_error)
5425 ) THEN
5426 RAISE contract_activation_failed;
5427 END IF;
5428 END IF;
5429
5430 x_return_status := okl_api.g_ret_sts_success;
5431 okl_api.end_activity (x_msg_count => x_msg_count,
5432 x_msg_data => x_msg_data
5433 );
5434 EXCEPTION
5435 WHEN contract_activation_failed THEN
5436 x_process_status := okl_api.g_ret_sts_error;
5437 okl_book_controller_pvt.update_book_controller_trx
5438 (p_api_version => p_api_version,
5439 p_init_msg_list => okl_api.g_false,
5440 --To retain message stack
5441 x_return_status => x_return_status,
5442 x_msg_count => x_msg_count,
5443 x_msg_data => x_msg_data,
5444 p_khr_id => p_chr_id,
5445 p_prog_short_name => okl_book_controller_pvt.g_submit_contract,
5446 p_progress_status => okl_book_controller_pvt.g_prog_sts_error
5447 );
5448
5449 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
5450 RAISE okl_api.g_exception_unexpected_error;
5451 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
5452 RAISE okl_api.g_exception_error;
5453 END IF;
5454
5455 x_return_status := okl_api.g_ret_sts_success;
5456 WHEN okl_api.g_exception_error THEN
5457 x_return_status :=
5458 okl_api.handle_exceptions
5459 (p_api_name => l_api_name,
5460 p_pkg_name => g_pkg_name,
5461 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
5462 x_msg_count => x_msg_count,
5463 x_msg_data => x_msg_data,
5464 p_api_type => g_api_type
5465 );
5466 x_process_status := okl_api.g_ret_sts_error;
5467 WHEN okl_api.g_exception_unexpected_error THEN
5468 x_return_status :=
5469 okl_api.handle_exceptions
5470 (p_api_name => l_api_name,
5471 p_pkg_name => g_pkg_name,
5472 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
5473 x_msg_count => x_msg_count,
5474 x_msg_data => x_msg_data,
5475 p_api_type => g_api_type
5476 );
5477 x_process_status := okl_api.g_ret_sts_error;
5478 WHEN OTHERS THEN
5479 x_return_status :=
5480 okl_api.handle_exceptions (p_api_name => l_api_name,
5481 p_pkg_name => g_pkg_name,
5482 p_exc_name => 'OTHERS',
5483 x_msg_count => x_msg_count,
5484 x_msg_data => x_msg_data,
5485 p_api_type => g_api_type
5486 );
5487 x_process_status := okl_api.g_ret_sts_error;
5488 END approve_activate_contract;
5489 END okl_contract_book_pvt;