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