[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_INFO
Source
1 PACKAGE BODY OKL_CONTRACT_INFO AS
2 /* $Header: OKLRCONB.pls 120.6 2007/10/26 10:23:12 dkagrawa ship $ */
3
4 ---------------------------------------------------------------------------
5 -- FUNCTION get_customer
6 ---------------------------------------------------------------------------
7 FUNCTION get_customer(
8 p_contract_id IN NUMBER,
9 x_customer OUT NOCOPY VARCHAR2)
10 RETURN VARCHAR2
11 IS
12 l_api_version NUMBER := 1.0;
13 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
14 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
15 l_msg_count NUMBER;
16 l_msg_data VARCHAR2(2000);
17 l_party_tab OKL_JTOT_EXTRACT.party_tab_type;
18 BEGIN
19
20 -- Procedure to call to get Party or Customer ID
21 OKL_JTOT_EXTRACT.Get_Party (
22 l_api_version,
23 l_init_msg_list,
24 l_return_status,
25 l_msg_count,
26 l_msg_data,
27 p_contract_id,
28 null,
29 'LESSEE',
30 'S',
31 l_party_tab
32 );
33
34 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
35 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
36 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
37 RAISE okl_api.G_EXCEPTION_ERROR;
38 END IF;
39
40 IF l_party_tab.FIRST IS NOT NULL
41 THEN --fetch from table only if some data is retrieved
42 FOR i in 1..l_party_tab.LAST
43 LOOP
44 x_customer := l_party_tab(i).id1;
45 END LOOP;
46 ELSE
47 x_customer := NULL;
48 END IF;
49
50 RETURN l_return_status;
51 EXCEPTION
52 WHEN okl_api.G_EXCEPTION_ERROR THEN
53 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
54 ,p_msg_name => G_ERROR
55 ,p_token1 => G_SQLCODE_TOKEN
56 ,p_token1_value => SQLCODE
57 ,p_token2 => G_SQLERRM_TOKEN
58 ,p_token2_value => SQLERRM);
59 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
60 RETURN(l_return_status);
61
62 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
63 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
64 ,p_msg_name => G_UNEXPECTED_ERROR
65 ,p_token1 => G_SQLCODE_TOKEN
66 ,p_token1_value => SQLCODE
67 ,p_token2 => G_SQLERRM_TOKEN
68 ,p_token2_value => SQLERRM);
69 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
70 RETURN(l_return_status);
71
72 WHEN OTHERS THEN
73 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
74 ,p_msg_name => G_UNEXPECTED_ERROR
75 ,p_token1 => G_SQLCODE_TOKEN
76 ,p_token1_value => SQLCODE
77 ,p_token2 => G_SQLERRM_TOKEN
78 ,p_token2_value => SQLERRM);
79 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
80 RETURN(l_return_status);
81
82 END get_customer;
83
84 ---------------------------------------------------------------------------
85 -- FUNCTION get_vendor_program
86 ---------------------------------------------------------------------------
87 FUNCTION get_vendor_program(
88 p_contract_id IN NUMBER,
89 x_vendor_program OUT NOCOPY VARCHAR2)
90 RETURN VARCHAR2
91 IS
92 -- Get the vendor Program description
93 CURSOR vendor_program_csr(p_contract_id NUMBER) IS
94 SELECT SUBSTR(description,1,240) FROM okc_k_headers_tl
95 WHERE ID =
96 ( SELECT khr_id
97 FROM okl_k_headers
98 WHERE id = p_contract_id);
99
100 l_vendor_program VARCHAR2(240);
101 l_api_version NUMBER;
102 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(2000);
105
106 BEGIN
107
108 OPEN vendor_program_csr(p_contract_id);
109 FETCH vendor_program_csr INTO l_vendor_program;
110 CLOSE vendor_program_csr;
111
112 x_vendor_program := l_vendor_program;
113
114 RETURN l_return_status;
115 EXCEPTION
116 WHEN OTHERS THEN
117 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
118 ,p_msg_name => G_UNEXPECTED_ERROR
119 ,p_token1 => G_SQLCODE_TOKEN
120 ,p_token1_value => SQLCODE
121 ,p_token2 => G_SQLERRM_TOKEN
122 ,p_token2_value => SQLERRM);
123 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
124 RETURN(l_return_status);
125
126 END get_vendor_program;
127
128 ---------------------------------------------------------------------------
129 -- FUNCTION get_bill_to_address
130 ---------------------------------------------------------------------------
131 FUNCTION get_bill_to_address(
132 p_contract_id IN NUMBER,
133 x_bill_to_address_id OUT NOCOPY VARCHAR2)
134 RETURN VARCHAR2
135 IS
136 -- Code segment for Customer Account/bill to address
137 -- as mentioned in OKC Rules Migration HLD
138 CURSOR BillToAddress_csr(p_contract_id NUMBER) IS
139 SELECT bill_to_site_use_id
140 FROM okc_k_headers_b
141 WHERE id =p_contract_id;
142
143 l_bill_to_address_id VARCHAR2(240);
144 l_api_version NUMBER;
145 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
146 l_msg_count NUMBER;
147 l_msg_data VARCHAR2(2000);
148
149 BEGIN
150
151 OPEN BillToAddress_csr(p_contract_id);
152 FETCH BillToAddress_csr INTO l_bill_to_address_id;
153 CLOSE BillToAddress_csr;
154
155 x_bill_to_address_id := l_bill_to_address_id;
156
157 RETURN l_return_status;
158 EXCEPTION
159 WHEN OTHERS THEN
160 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
161 ,p_msg_name => G_UNEXPECTED_ERROR
162 ,p_token1 => G_SQLCODE_TOKEN
163 ,p_token1_value => SQLCODE
164 ,p_token2 => G_SQLERRM_TOKEN
165 ,p_token2_value => SQLERRM);
166 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
167 RETURN(l_return_status);
168
169 END get_bill_to_address;
170
171 ---------------------------------------------------------------------------
172 -- FUNCTION get_private_label
173 ---------------------------------------------------------------------------
174 FUNCTION get_private_label(
175 p_contract_id IN NUMBER,
176 x_private_label OUT NOCOPY VARCHAR2)
177 RETURN VARCHAR2
178 IS
179 l_api_version NUMBER := 1.0;
180 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
181 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
182 l_msg_count NUMBER;
183 l_msg_data VARCHAR2(2000);
184 l_party_tab OKL_JTOT_EXTRACT.party_tab_type;
185 BEGIN
186
187 -- Procedure to call to get Private Label ID, nothing but
188 -- a Role
189 OKL_JTOT_EXTRACT.Get_Party (
190 l_api_version,
191 l_init_msg_list,
192 l_return_status,
193 l_msg_count,
194 l_msg_data,
195 p_contract_id,
196 null,
197 'PRIVATE_LABEL',
198 'S',
199 l_party_tab
200 );
201
202 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
203 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
204 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
205 RAISE okl_api.G_EXCEPTION_ERROR;
206 END IF;
207
208 IF l_party_tab.FIRST IS NOT NULL
209 THEN --fetch from table only if some data is retrieved
210 FOR i in 1..l_party_tab.LAST
211 LOOP
212 x_private_label := l_party_tab(i).id1;
213 END LOOP;
214 ELSE
215 x_private_label := NULL;
216 END IF;
217
218 RETURN l_return_status;
219 EXCEPTION
220 WHEN okl_api.G_EXCEPTION_ERROR THEN
221 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
222 ,p_msg_name => G_ERROR
223 ,p_token1 => G_SQLCODE_TOKEN
224 ,p_token1_value => SQLCODE
225 ,p_token2 => G_SQLERRM_TOKEN
226 ,p_token2_value => SQLERRM);
227 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
228 RETURN(l_return_status);
229
230 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
231 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
232 ,p_msg_name => G_UNEXPECTED_ERROR
233 ,p_token1 => G_SQLCODE_TOKEN
234 ,p_token1_value => SQLCODE
235 ,p_token2 => G_SQLERRM_TOKEN
236 ,p_token2_value => SQLERRM);
237 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
238 RETURN(l_return_status);
239
240 WHEN OTHERS THEN
241 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
242 ,p_msg_name => G_UNEXPECTED_ERROR
243 ,p_token1 => G_SQLCODE_TOKEN
244 ,p_token1_value => SQLCODE
245 ,p_token2 => G_SQLERRM_TOKEN
246 ,p_token2_value => SQLERRM);
247 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
248 RETURN(l_return_status);
249
250 END get_private_label;
251
252 ---------------------------------------------------------------------------
253 -- FUNCTION get_non_notify_flag
254 ---------------------------------------------------------------------------
255 FUNCTION get_non_notify_flag(
256 p_contract_id IN NUMBER,
257 x_non_notify_flag OUT NOCOPY VARCHAR2)
258 RETURN VARCHAR2
259 IS
260 l_non_notify_flag VARCHAR2(200);
261 l_api_version NUMBER;
262 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
263 l_msg_count NUMBER;
264 l_msg_data VARCHAR2(2000);
265 l_id1 Varchar2(40);
266 l_id2 Varchar2(200);
267
268 -- Following cursor introduced for bug: 3838403
269 CURSOR get_non_notify_flag (p_contract_id IN NUMBER) IS
270 SELECT RULE_INFORMATION1
271 FROM okc_rule_groups_b rgp, okc_rules_b rul
272 WHERE rgp.id = rul.rgp_id
273 AND rgp.dnz_chr_id = p_contract_id
274 AND rgp.rgd_code = 'LANNTF'
275 AND rul.rule_information_category = 'LANNTF';
276
277 BEGIN
278
279 /* l_return_status := get_rule_value(
280 p_contract_id => p_contract_id
281 ,p_rule_group_code => 'LANNTF'
282 ,p_rule_code => 'LANNTF'
283 ,p_rule_name => 'Non-Notification'
284 ,x_id1 => l_id1
285 ,x_id2 => l_id2
286 ,x_value => l_non_notify_flag);
287 */
288
289 OPEN get_non_notify_flag (p_contract_id);
290 FETCH get_non_notify_flag INTO l_non_notify_flag;
291 CLOSE get_non_notify_flag;
292
293 x_non_notify_flag := l_non_notify_flag;
294
295 RETURN l_return_status;
296 EXCEPTION
297 WHEN OTHERS THEN
298 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
299 ,p_msg_name => G_UNEXPECTED_ERROR
300 ,p_token1 => G_SQLCODE_TOKEN
301 ,p_token1_value => SQLCODE
302 ,p_token2 => G_SQLERRM_TOKEN
303 ,p_token2_value => SQLERRM);
304 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
305 RETURN(l_return_status);
306
307 END get_non_notify_flag;
308
309 ---------------------------------------------------------------------------
310 -- FUNCTION get_currency
311 ---------------------------------------------------------------------------
312 FUNCTION get_currency(
313 p_contract_id IN NUMBER,
314 x_currency OUT NOCOPY VARCHAR2)
315 RETURN VARCHAR2
316 IS
317 CURSOR currency_csr(p_contract_id NUMBER) IS
318 SELECT currency_code
319 FROM okc_k_headers_b
320 WHERE id = p_contract_id;
321
322 l_currency VARCHAR2(240);
323 l_api_version NUMBER;
324 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
325 l_msg_count NUMBER;
326 l_msg_data VARCHAR2(2000);
327
328 BEGIN
329
330 OPEN currency_csr(p_contract_id);
331 FETCH currency_csr INTO l_currency;
332 CLOSE currency_csr;
333
334 x_currency := l_currency;
335
336 RETURN l_return_status;
337 EXCEPTION
338 WHEN OTHERS THEN
339 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
340 ,p_msg_name => G_UNEXPECTED_ERROR
341 ,p_token1 => G_SQLCODE_TOKEN
342 ,p_token1_value => SQLCODE
343 ,p_token2 => G_SQLERRM_TOKEN
344 ,p_token2_value => SQLERRM);
345 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
346 RETURN(l_return_status);
347
348 END get_currency;
349
350 ---------------------------------------------------------------------------
351 -- FUNCTION get_syndicate_flag
352 ---------------------------------------------------------------------------
353 FUNCTION get_syndicate_flag(
354 p_contract_id IN NUMBER,
355 x_syndicate_flag OUT NOCOPY VARCHAR2)
356 RETURN VARCHAR2
357 IS
358 -- A complex query to find out if a contract has syndication
359 CURSOR syndicate_flag_csr(p_contract_id NUMBER) IS
360 SELECT 'Y' FROM okc_k_headers_b chr
361 WHERE id = p_contract_id
362 AND EXISTS
363 (
364 SELECT 'x' FROM okc_k_items cim
365 WHERE cim.object1_id1 = to_char(chr.id)
366 AND EXISTS
367 (
368 SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
369 WHERE cle.lse_id = lse.id
370 AND lse.lty_code = 'SHARED'
371 AND cle.id = cim.cle_id
372 )
373 AND EXISTS
374 (
375 SELECT 'x' FROM okc_k_headers_b chr2
376 WHERE chr2.id = cim.dnz_chr_id
377 AND chr2.scs_code = 'SYNDICATION'
378 AND chr2.sts_code not in ('TERMINATED','ABANDONED')
379 )
380 )
381 AND chr.scs_code in ('LEASE','LOAN');
382
383 l_syndicate_flag VARCHAR2(1) := 'N';
384 l_api_version NUMBER;
385 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
386 l_msg_count NUMBER;
387 l_msg_data VARCHAR2(2000);
388
389 BEGIN
390
391 OPEN syndicate_flag_csr(p_contract_id);
392 FETCH syndicate_flag_csr INTO l_syndicate_flag;
393 CLOSE syndicate_flag_csr;
394
395 x_syndicate_flag := l_syndicate_flag;
396
397 RETURN l_return_status;
398 EXCEPTION
399 WHEN OTHERS THEN
400 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
401 ,p_msg_name => G_UNEXPECTED_ERROR
402 ,p_token1 => G_SQLCODE_TOKEN
403 ,p_token1_value => SQLCODE
404 ,p_token2 => G_SQLERRM_TOKEN
405 ,p_token2_value => SQLERRM);
406 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
407 RETURN(l_return_status);
408
409 END get_syndicate_flag;
410
411 ---------------------------------------------------------------------------
412 -- FUNCTION GET_ORG_ID
413 ---------------------------------------------------------------------------
414 FUNCTION GET_ORG_ID(
415 p_contract_id IN NUMBER,
416 x_org_id OUT NOCOPY NUMBER )
417 RETURN VARCHAR2 AS
418
419 -- get org_id for contract
420 CURSOR get_org_id_csr (p_contract_id IN VARCHAR2) IS
421 SELECT authoring_org_id
422 FROM okc_k_headers_b
423 WHERE id = p_contract_id;
424
425 l_api_version NUMBER;
426 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
427 l_msg_count NUMBER;
428 l_msg_data VARCHAR2(2000);
429
430 BEGIN
431
432 OPEN get_org_id_csr(p_contract_id);
433 FETCH get_org_id_csr INTO x_org_id;
434 CLOSE get_org_id_csr;
435
436 RETURN l_return_status;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
441 ,p_msg_name => G_UNEXPECTED_ERROR
442 ,p_token1 => G_SQLCODE_TOKEN
443 ,p_token1_value => SQLCODE
444 ,p_token2 => G_SQLERRM_TOKEN
445 ,p_token2_value => SQLERRM);
446 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
447 RETURN(l_return_status);
448
449 END GET_ORG_ID;
450
451 ---------------------------------------------------------------------------
452 -- FUNCTION GET_REMAINING_PAYMENTS
453 ---------------------------------------------------------------------------
454 FUNCTION get_remaining_payments(
455 p_contract_id IN NUMBER,
456 x_remaining_payments OUT NOCOPY NUMBER)
457 RETURN VARCHAR2
458 IS
459
460 -- Get the remaining number of payments for a contract
461
462
463
464 CURSOR remaining_payments_csr(p_contract_id IN NUMBER) IS
465 SELECT count(stm.khr_id) remaining_payments
466 FROM okl_strm_elements ste
467 ,okl_streams stm
468 ,okl_strm_type_b sty
469 ,okc_k_headers_b khr
470 WHERE stm.id = ste.stm_id
471 AND ste.date_billed IS NULL
472 AND stm.active_yn = 'Y'
473 AND stm.say_code = 'CURR'
474 AND sty.id = stm.sty_id
475 AND sty.billable_yn = 'Y'
476 AND khr.id = stm.khr_id
477 AND khr.scs_code IN ('LEASE', 'LOAN')
478 AND khr.id = p_contract_id;
479
480 l_remaining_payments NUMBER := 0;
481 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
482
483 BEGIN
484
485 OPEN remaining_payments_csr(p_contract_id);
486 FETCH remaining_payments_csr INTO l_remaining_payments;
487 CLOSE remaining_payments_csr;
488
489 x_remaining_payments := l_remaining_payments;
490
491 RETURN l_return_status;
492
493 EXCEPTION
494 WHEN OTHERS THEN
495 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
496 ,p_msg_name => G_UNEXPECTED_ERROR
497 ,p_token1 => G_SQLCODE_TOKEN
498 ,p_token1_value => SQLCODE
499 ,p_token2 => G_SQLERRM_TOKEN
500 ,p_token2_value => SQLERRM);
501 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
502 RETURN(l_return_status);
503
504 END get_remaining_payments;
505
506 ---------------------------------------------------------------------------
507 -- FUNCTION GET_RULE_VALUE (accepts prompt as a parameter)
508 ---------------------------------------------------------------------------
509 FUNCTION get_rule_value(
510 p_contract_id IN NUMBER
511 ,p_rule_group_code IN VARCHAR2
512 ,p_rule_code IN VARCHAR2
513 ,p_rule_name IN VARCHAR2
514 ,x_id1 OUT NOCOPY VARCHAR2
515 ,x_id2 OUT NOCOPY VARCHAR2
516 ,x_value OUT NOCOPY VARCHAR2)
517 RETURN VARCHAR2
518 IS
519 l_return_status Varchar2(1);
520 l_msg_count Number;
521 l_msg_data varchar2(2000);
522 l_cle_id Number;
523 l_id1 Varchar2(40);
524 l_id2 Varchar2(200);
525 l_description Varchar2(2000);
526 l_status Varchar2(1);
527 l_start_date date;
528 l_end_date date;
529 l_org_id Number;
530 l_inv_org_id Number;
531 l_book_type_code Varchar2(15);
532 l_select Varchar2(2000);
533 l_msg_index_out Number;
534
535 BEGIN
536
537 -- Procedure call to get Rule Value
538 OKL_RULE_APIS_PUB.Get_rule_Segment_Value
539 ( p_api_version => 1.0
540 ,p_init_msg_list => OKL_API.G_FALSE
541 ,x_return_status => l_return_status
542 ,x_msg_count => l_msg_count
543 ,x_msg_data => l_msg_data
544 ,p_chr_id => p_contract_id
545 ,p_cle_id => null
546 ,p_rgd_code => p_rule_group_code
547 ,p_rdf_code => p_rule_code
548 ,p_rdf_name => p_rule_name
549 ,x_id1 => x_id1
550 ,x_id2 => x_id2
551 ,x_name => x_value
552 ,x_description => l_description
553 ,x_status => l_status
554 ,x_start_date => l_start_date
555 ,x_end_date => l_end_date
556 ,x_org_id => l_org_id
557 ,x_inv_org_id => l_inv_org_id
558 ,x_book_type_code => l_book_type_code
559 ,x_select => l_select );
560
561 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
562 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
563 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
564 RAISE okl_api.G_EXCEPTION_ERROR;
565 END IF;
566
567 RETURN l_return_status;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
572 ,p_msg_name => G_UNEXPECTED_ERROR
573 ,p_token1 => G_SQLCODE_TOKEN
574 ,p_token1_value => SQLCODE
575 ,p_token2 => G_SQLERRM_TOKEN
576 ,p_token2_value => SQLERRM);
577 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
578 RETURN(l_return_status);
579
580 END get_rule_value;
581
582 ---------------------------------------------------------------------------
583 -- FUNCTION GET_RULE_VALUE (accepts segment number as a parameter)
584 ---------------------------------------------------------------------------
585 FUNCTION get_rule_value(
586 p_contract_id IN NUMBER
587 ,p_rule_group_code IN VARCHAR2
588 ,p_rule_code IN VARCHAR2
589 ,p_segment_number IN NUMBER
590 ,x_id1 OUT NOCOPY VARCHAR2
591 ,x_id2 OUT NOCOPY VARCHAR2
592 ,x_value OUT NOCOPY VARCHAR2)
593 RETURN VARCHAR2
594 IS
595 l_return_status Varchar2(1);
596 l_msg_count Number;
597 l_msg_data varchar2(2000);
598 l_cle_id Number;
599 l_id1 Varchar2(40);
600 l_id2 Varchar2(200);
601 l_description Varchar2(2000);
602 l_status Varchar2(1);
603 l_start_date date;
604 l_end_date date;
605 l_org_id Number;
606 l_inv_org_id Number;
607 l_book_type_code Varchar2(15);
608 l_select Varchar2(2000);
609 l_msg_index_out Number;
610
611 BEGIN
612
613 -- Procedure call to get Rule Value
614 OKL_RULE_APIS_PUB.Get_rule_Segment_Value
615 ( p_api_version => 1.0
616 ,p_init_msg_list => OKL_API.G_FALSE
617 ,x_return_status => l_return_status
618 ,x_msg_count => l_msg_count
619 ,x_msg_data => l_msg_data
620 ,p_chr_id => p_contract_id
621 ,p_cle_id => null
622 ,p_rgd_code => p_rule_group_code
623 ,p_rdf_code => p_rule_code
624 ,p_segment_number => p_segment_number
625 ,x_id1 => x_id1
626 ,x_id2 => x_id2
627 ,x_name => x_value
628 ,x_description => l_description
629 ,x_status => l_status
630 ,x_start_date => l_start_date
631 ,x_end_date => l_end_date
632 ,x_org_id => l_org_id
633 ,x_inv_org_id => l_inv_org_id
634 ,x_book_type_code => l_book_type_code
635 ,x_select => l_select );
636
637 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
638 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
639 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
640 RAISE okl_api.G_EXCEPTION_ERROR;
641 END IF;
642
643 RETURN l_return_status;
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
648 ,p_msg_name => G_UNEXPECTED_ERROR
649 ,p_token1 => G_SQLCODE_TOKEN
650 ,p_token1_value => SQLCODE
651 ,p_token2 => G_SQLERRM_TOKEN
652 ,p_token2_value => SQLERRM);
653 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
654 RETURN(l_return_status);
655
656 END get_rule_value;
657
658 ---------------------------------------------------------------------------
659 -- FUNCTION GET_DAYS_PAST_DUE
660 ---------------------------------------------------------------------------
661 FUNCTION get_days_past_due(
662 p_contract_id IN NUMBER,
663 x_days_past_due OUT NOCOPY NUMBER)
664 RETURN VARCHAR2
665 IS
666
667
668 -- ASHIM CHANGE - START
669
670
671
672 -- Get days past due for a contract for invoices
673 /*CURSOR days_past_due_csr(p_contract_id IN NUMBER) IS
674 SELECT min(aps.due_date)
675 FROM okl_cnsld_ar_strms_b ocas
676 ,ar_payment_schedules aps
677 WHERE ocas.khr_id = p_contract_id
678 AND ocas.receivables_invoice_id = aps.customer_trx_id
679 AND aps.class = 'INV'
680 AND aps.due_date < sysdate
681 AND NVL(aps.amount_due_remaining, 0) > 0; */
682
683 CURSOR days_past_due_csr(p_contract_id IN NUMBER) IS
684 SELECT min(aps.due_date)
685 FROM okl_bpd_tld_ar_lines_v ocas
686 ,ar_payment_schedules aps
687 WHERE ocas.khr_id = p_contract_id
688 AND ocas.customer_trx_id = aps.customer_trx_id
689 AND aps.class = 'INV'
690 AND aps.due_date < sysdate
691 AND NVL(aps.amount_due_remaining, 0) > 0;
692
693 -- ASHIM CHANGE - END
694
695
696 l_due_date DATE;
697 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
698
699 BEGIN
700
701 OPEN days_past_due_csr(p_contract_id);
702 FETCH days_past_due_csr INTO l_due_date;
703 CLOSE days_past_due_csr;
704
705 IF l_due_date IS NULL
706 THEN
707 l_due_date := TRUNC(SYSDATE);
708 END IF;
709
710 x_days_past_due := TRUNC(SYSDATE) - l_due_date;
711
712 RETURN l_return_status;
713
714 EXCEPTION
715 WHEN OTHERS THEN
716 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
717 ,p_msg_name => G_UNEXPECTED_ERROR
718 ,p_token1 => G_SQLCODE_TOKEN
719 ,p_token1_value => SQLCODE
720 ,p_token2 => G_SQLERRM_TOKEN
721 ,p_token2_value => SQLERRM);
722 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
723 RETURN(l_return_status);
724
725 END get_days_past_due;
726
727 ---------------------------------------------------------------------------
728 -- FUNCTION GET_AMOUNT_PAST_DUE
729 ---------------------------------------------------------------------------
730 FUNCTION get_amount_past_due(
731 p_contract_id IN NUMBER,
732 x_amount_past_due OUT NOCOPY NUMBER)
733 RETURN VARCHAR2
734 IS
735
736
737 -- ASHIM CHANGE - START
738
739
740 -- Get AMount past due for a contract
741 /*CURSOR amount_past_due_csr(p_contract_id IN NUMBER, p_sty_id IN NUMBER) IS
742 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
743 FROM okl_cnsld_ar_strms_b ocas
744 ,ar_payment_schedules aps
745 ,okl_strm_type_v strm
746 WHERE ocas.khr_id = p_contract_id
747 AND ocas.receivables_invoice_id = aps.customer_trx_id
748 AND aps.class ='INV'
749 AND aps.due_date < sysdate
750 and strm.id=ocas.sty_id
751 and strm.id <> p_sty_id; */
752 --and strm.name <>'CURE';
753
754 CURSOR amount_past_due_csr(p_contract_id IN NUMBER, p_sty_id IN NUMBER) IS
755 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
756 FROM okl_bpd_tld_ar_lines_v ocas
757 ,ar_payment_schedules aps
758 ,okl_strm_type_v strm
759 WHERE ocas.khr_id = p_contract_id
760 AND ocas.customer_trx_id = aps.customer_trx_id
761 AND aps.class ='INV'
762 AND aps.due_date < sysdate
763 and strm.id=ocas.sty_id
764 and strm.id <> p_sty_id;
765 --and strm.name <>'CURE';
766
767
768 -- ASHIM CHANGE - END
769
770
771 l_amount_past_due NUMBER;
772 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
773 x_primary_sty_id NUMBER;
774
775 BEGIN
776
777 OKL_STREAMS_UTIL.get_primary_stream_type(
778 p_khr_id => p_contract_id,
779 p_primary_sty_purpose => 'CURE',
780 x_return_status => l_return_status,
781 x_primary_sty_id => x_primary_sty_id
782 );
783
784 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS or x_primary_sty_id is null) THEN
785
786 OKL_API.SET_MESSAGE (
787 p_app_name => 'OKL',
788 p_msg_name => G_REQUIRED_VALUE,
789 p_token1 => 'COL_NAME',
790 p_token1_value => 'Sty Id');
791 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
792 END IF;
793
794 OPEN amount_past_due_csr(p_contract_id, x_primary_sty_id);
795 FETCH amount_past_due_csr INTO l_amount_past_due;
796 CLOSE amount_past_due_csr;
797
798 x_amount_past_due := l_amount_past_due;
799
800 RETURN l_return_status;
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
805 ,p_msg_name => G_UNEXPECTED_ERROR
806 ,p_token1 => G_SQLCODE_TOKEN
807 ,p_token1_value => SQLCODE
808 ,p_token2 => G_SQLERRM_TOKEN
809 ,p_token2_value => SQLERRM);
810 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
811 RETURN(l_return_status);
812
813 END get_amount_past_due;
814
815 ---------------------------------------------------------------------------
816 -- FUNCTION get_Next due amount, dates
817 ---------------------------------------------------------------------------
818 FUNCTION get_next_due(
819 p_contract_id IN NUMBER,
820 x_next_due_amt OUT NOCOPY NUMBER,
821 x_next_due_date OUT NOCOPY DATE )
822 RETURN VARCHAR2 IS
823 l_api_version NUMBER;
824 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
825 l_msg_count NUMBER;
826 l_msg_data VARCHAR2(2000);
827
828
829 -- ASHIM CHANGE - START
830
831
832 -- Get Next due date and amount
833 /*CURSOR next_due_csr IS
834 SELECT amt,due_date
835 FROM (SELECT (SYSDATE-due_date) days
836 ,aps.due_date due_date
837 ,SUM(aps.amount_due_original) amt
838 ,lsm.khr_id khr_id
839 FROM OKL_CNSLD_AR_STRMS_B LSM
840 ,AR_PAYMENT_SCHEDULES APS
841 WHERE lsm.receivables_invoice_id = aps.customer_trx_id
842 GROUP BY khr_id, due_date ) amount_date
843 WHERE amount_date.days=(SELECT MIN(next_due.days)
844 FROM (SELECT (SYSDATE-due_date) days
845 ,aps.due_date due_date
846 ,SUM(aps.amount_due_original) amt
847 ,lsm.khr_id khr_id
848 FROM OKL_CNSLD_AR_STRMS_B LSM
849 ,AR_PAYMENT_SCHEDULES APS
850 WHERE lsm.receivables_invoice_id = aps.customer_trx_id
851 GROUP BY khr_id, due_date) next_due
852 WHERE khr_id = p_contract_id
853 AND SIGN(next_due.days) = -1); */
854 /*
855 CURSOR next_due_csr IS
856 SELECT amt,due_date
857 FROM (SELECT (SYSDATE-due_date) days
858 ,aps.due_date due_date
859 ,SUM(aps.amount_due_original) amt
860 ,lsm.khr_id khr_id
861 FROM OKL_BPD_TLD_AR_LINES_V LSM
862 ,AR_PAYMENT_SCHEDULES APS
863 WHERE lsm.customer_trx_id = aps.customer_trx_id
864 GROUP BY khr_id, due_date ) amount_date
865 WHERE amount_date.days=(SELECT MIN(next_due.days)
866 FROM (SELECT (SYSDATE-due_date) days
867 ,aps.due_date due_date
868 ,SUM(aps.amount_due_original) amt
869 ,lsm.khr_id khr_id
870 FROM OKL_BPD_TLD_AR_LINES_V LSM
871 ,AR_PAYMENT_SCHEDULES APS
872 WHERE lsm.customer_trx_id = aps.customer_trx_id
873 GROUP BY khr_id, due_date) next_due
874 WHERE khr_id = p_contract_id
875 AND SIGN(next_due.days) = -1); */
876
877 -- ASHIM CHANGE - END
878
879 --dkagrawa changed the cursor as follows for bug#6324572
880 CURSOR next_due_csr IS
881 SELECT SUM(aps.amount_due_original) amt,
882 aps.due_date due_date
883 FROM okl_bpd_tld_ar_lines_v LSM
884 ,AR_PAYMENT_SCHEDULES APS
885 WHERE lsm.customer_trx_id = aps.customer_trx_id
886 AND lsm.khr_id = p_contract_id
887 AND due_date>SYSDATE
888 GROUP BY aps.due_date , lsm.khr_id
889 ORDER BY (due_date-SYSDATE) desc ;
890
891 BEGIN
892 OPEN next_due_csr;
893 FETCH next_due_csr into x_next_due_amt,x_next_due_date;
894 CLOSE next_due_csr;
895 RETURN l_return_status;
896 EXCEPTION
897 WHEN OTHERS THEN
898 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
899 ,p_msg_name => G_UNEXPECTED_ERROR
900 ,p_token1 => G_SQLCODE_TOKEN
901 ,p_token1_value => SQLCODE
902 ,p_token2 => G_SQLERRM_TOKEN
903 ,p_token2_value => SQLERRM);
904 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
905 END;
906
907 ---------------------------------------------------------------------------
908 -- FUNCTION get last due amount,dates
909 ---------------------------------------------------------------------------
910 FUNCTION get_last_due(
911 p_contract_id IN NUMBER,
912 x_last_due_amt OUT NOCOPY NUMBER,
913 x_last_due_date OUT NOCOPY DATE )
914 RETURN VARCHAR2 IS
915 l_api_version NUMBER;
916 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
917 l_msg_count NUMBER;
918 l_msg_data VARCHAR2(2000);
919
920
921 -- ASHIM CHANGE - START
922
923
924 -- Get last due amount and date
925 /*CURSOR last_due_csr IS
926 SELECT amt,due_date
927 FROM (SELECT (SYSDATE-due_date) days
928 ,aps.due_date due_date
929 ,SUM(aps.amount_due_original) amt
930 ,lsm.khr_id khr_id
931 FROM OKL_CNSLD_AR_STRMS_B LSM
932 ,AR_PAYMENT_SCHEDULES APS
933 WHERE lsm.receivables_invoice_id = aps.customer_trx_id
934 GROUP BY khr_id, due_date ) amount_date
935 WHERE amount_date.days=(SELECT MIN(next_due.days)
936 FROM (SELECT (SYSDATE-due_date) days
937 ,aps.due_date due_date
938 ,SUM(aps.amount_due_original) amt
939 ,lsm.khr_id khr_id
940 FROM OKL_CNSLD_AR_STRMS_B LSM
941 ,AR_PAYMENT_SCHEDULES APS
942 WHERE lsm.receivables_invoice_id = aps.customer_trx_id
943 GROUP BY khr_id, due_date) next_due
944 WHERE khr_id = p_contract_id
945 AND SIGN(next_due.days) = 1); */
946
947 /* CURSOR last_due_csr IS
948 SELECT amt,due_date
949 FROM (SELECT (SYSDATE-due_date) days
950 ,aps.due_date due_date
951 ,SUM(aps.amount_due_original) amt
952 ,lsm.khr_id khr_id
953 FROM okl_bpd_tld_ar_lines_v LSM
954 ,AR_PAYMENT_SCHEDULES APS
955 WHERE lsm.customer_trx_id = aps.customer_trx_id
956 GROUP BY khr_id, due_date ) amount_date
957 WHERE amount_date.days=(SELECT MIN(next_due.days)
958 FROM (SELECT (SYSDATE-due_date) days
959 ,aps.due_date due_date
960 ,SUM(aps.amount_due_original) amt
961 ,lsm.khr_id khr_id
962 FROM okl_bpd_tld_ar_lines_v LSM
963 ,AR_PAYMENT_SCHEDULES APS
964 WHERE lsm.customer_trx_id = aps.customer_trx_id
965 GROUP BY khr_id, due_date) next_due
966 WHERE khr_id = p_contract_id
967 AND SIGN(next_due.days) = 1); */
968
969 -- ASHIM CHANGE - END
970 --dkagrawa changed the cursor as follows for bug#6324572
971 CURSOR last_due_csr IS
972 SELECT SUM(aps.amount_due_original) amt,
973 aps.due_date due_date
974 FROM okl_bpd_tld_ar_lines_v LSM
975 ,AR_PAYMENT_SCHEDULES APS
976 WHERE lsm.customer_trx_id = aps.customer_trx_id
977 AND lsm.khr_id = p_contract_id
978 AND due_date<=SYSDATE
979 GROUP BY aps.due_date , lsm.khr_id
980 ORDER BY (SYSDATE-due_date) asc;
981
982 BEGIN
983 OPEN last_due_csr;
984 FETCH last_due_csr into x_last_due_amt,x_last_due_date;
985 CLOSE last_due_csr;
986 RETURN l_return_status;
987 EXCEPTION
988 WHEN OTHERS THEN
989 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
990 ,p_msg_name => G_UNEXPECTED_ERROR
991 ,p_token1 => G_SQLCODE_TOKEN
992 ,p_token1_value => SQLCODE
993 ,p_token2 => G_SQLERRM_TOKEN
994 ,p_token2_value => SQLERRM);
995 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
996 END;
997
998 ---------------------------------------------------------------------------
999 -- FUNCTION Total asset cost for contract
1000 ---------------------------------------------------------------------------
1001 FUNCTION get_total_asset_cost(
1002 p_contract_id IN NUMBER,
1003 x_asset_cost OUT NOCOPY NUMBER )
1004 RETURN VARCHAR2 IS
1005 l_api_version NUMBER;
1006 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1007 l_msg_count NUMBER;
1008 l_msg_data VARCHAR2(2000);
1009
1010 -- Get total Asset Cost
1011 CURSOR asset_cost_csr IS
1012 SELECT SUM(fab.cost)
1013 FROM fa_additions_b faa,fa_books fab,okc_k_lines_b cle,
1014 okc_k_headers_b chr,okc_line_styles_b lse,
1015 okc_k_items cim
1016 WHERE faa.asset_id = fab.asset_id
1017 AND cim.object1_id2 = '#'
1018 AND cim.object1_id1 = faa.asset_id
1019 AND cim.jtot_object1_code = 'OKX_ASSET'
1020 AND cle.id = cim.cle_id
1021 AND lse.lty_code = 'FIXED_ASSET'
1022 AND cle.lse_id = lse.id
1023 AND cle.dnz_chr_id = chr.id
1024 AND chr.id = p_contract_id
1025 GROUP BY chr.id ;
1026 BEGIN
1027 OPEN asset_cost_csr;
1028 FETCH asset_cost_csr into x_asset_cost;
1029 CLOSE asset_cost_csr;
1030 RETURN l_return_status;
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1034 ,p_msg_name => G_UNEXPECTED_ERROR
1035 ,p_token1 => G_SQLCODE_TOKEN
1036 ,p_token1_value => SQLCODE
1037 ,p_token2 => G_SQLERRM_TOKEN
1038 ,p_token2_value => SQLERRM);
1039 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1040 END;
1041
1042 ---------------------------------------------------------------------------
1043 -- FUNCTION Total out standing receivables for contract
1044 ---------------------------------------------------------------------------
1045 FUNCTION get_outstanding_rcvble (
1046 p_contract_id IN NUMBER,
1047 x_rcvble_amt OUT NOCOPY NUMBER)
1048 RETURN VARCHAR2 IS
1049 l_api_version NUMBER;
1050 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1051 l_msg_count NUMBER;
1052 l_msg_data VARCHAR2(2000);
1053
1054 -- Get amount outstanding
1055 /*CURSOR outstanding_rcvble_csr IS
1056 SELECT SUM(NVL(amount_due_remaining, 0))
1057 FROM okl_bpd_leasing_payment_trx_v
1058 WHERE contract_id = p_contract_id;
1059 */
1060
1061
1062 -- ASHIM CHANGE - START
1063
1064
1065 /* CURSOR outstanding_rcvble_csr(p_sty_id number) IS
1066 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
1067 FROM okl_cnsld_ar_strms_b ocas
1068 ,ar_payment_schedules aps
1069 ,okl_strm_type_v strm
1070 WHERE ocas.khr_id = p_contract_id
1071 AND ocas.receivables_invoice_id = aps.customer_trx_id
1072 AND aps.class ='INV'
1073 --AND aps.due_date < sysdate
1074 and strm.id=ocas.sty_id
1075 and strm.id <> p_sty_id;
1076 --and strm.name <>'CURE'; */
1077
1078 CURSOR outstanding_rcvble_csr(p_sty_id number) IS
1079 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
1080 FROM okl_bpd_tld_ar_lines_v ocas
1081 ,ar_payment_schedules aps
1082 ,okl_strm_type_v strm
1083 WHERE ocas.khr_id = p_contract_id
1084 AND ocas.customer_trx_id = aps.customer_trx_id
1085 AND aps.class ='INV'
1086 --AND aps.due_date < sysdate
1087 and strm.id=ocas.sty_id
1088 and strm.id <> p_sty_id;
1089 --and strm.name <>'CURE';
1090
1091
1092 -- ASHIM CHANGE - END
1093
1094
1095
1096 x_primary_sty_id NUMBER;
1097
1098 BEGIN
1099
1100 OKL_STREAMS_UTIL.get_primary_stream_type(
1101 p_khr_id => p_contract_id,
1102 p_primary_sty_purpose => 'CURE',
1103 x_return_status => l_return_status,
1104 x_primary_sty_id => x_primary_sty_id
1105 );
1106
1107 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS or x_primary_sty_id is null) THEN
1108
1109 OKL_API.SET_MESSAGE (
1110 p_app_name => 'OKL',
1111 p_msg_name => G_REQUIRED_VALUE,
1112 p_token1 => 'COL_NAME',
1113 p_token1_value => 'Sty Id');
1114 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1115 END IF;
1116
1117 OPEN outstanding_rcvble_csr(x_primary_sty_id);
1118 FETCH outstanding_rcvble_csr into x_rcvble_amt;
1119 CLOSE outstanding_rcvble_csr;
1120
1121 RETURN l_return_status;
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1125 ,p_msg_name => G_UNEXPECTED_ERROR
1126 ,p_token1 => G_SQLCODE_TOKEN
1127 ,p_token1_value => SQLCODE
1128 ,p_token2 => G_SQLERRM_TOKEN
1129 ,p_token2_value => SQLERRM);
1130 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1131 END;
1132
1133 ---------------------------------------------------------------------------
1134 -- FUNCTION Term of contract in months,start date ,end date
1135 ---------------------------------------------------------------------------
1136 FUNCTION get_contract_term(
1137 p_contract_id IN NUMBER,
1138 x_start_date OUT NOCOPY DATE,
1139 x_end_date OUT NOCOPY DATE,
1140 x_term_duration OUT NOCOPY NUMBER)
1141 RETURN VARCHAR2 IS
1142 l_api_version NUMBER;
1143 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1144 l_msg_count NUMBER;
1145 l_msg_data VARCHAR2(2000);
1146
1147 -- Get contract term duration and dates
1148 CURSOR contract_dates_csr IS
1149 SELECT khr.start_date,khr.end_date,okhr.term_duration
1150 FROM OKL_K_HEADERS okhr ,okc_k_headers_v khr
1151 WHERE okhr.id = khr.id
1152 AND khr.id = p_contract_id;
1153 BEGIN
1154 OPEN contract_dates_csr;
1155 FETCH contract_dates_csr into x_start_date,x_end_date,x_term_duration;
1156 CLOSE contract_dates_csr ;
1157 RETURN l_return_status;
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1161 ,p_msg_name => G_UNEXPECTED_ERROR
1162 ,p_token1 => G_SQLCODE_TOKEN
1163 ,p_token1_value => SQLCODE
1164 ,p_token2 => G_SQLERRM_TOKEN
1165 ,p_token2_value => SQLERRM);
1166 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1167 END;
1168
1169 ---------------------------------------------------------------------------
1170 -- FUNCTION Net investment for contract
1171 ---------------------------------------------------------------------------
1172 FUNCTION get_net_investment (
1173 p_contract_id IN NUMBER,
1174 x_net_investment OUT NOCOPY NUMBER)
1175 RETURN VARCHAR2 IS
1176 l_api_version NUMBER;
1177 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1178 l_msg_count NUMBER;
1179 l_msg_data VARCHAR2(2000);
1180 l_return_rent NUMBER;
1181 l_return_residual NUMBER;
1182 l_return_unearned NUMBER;
1183
1184 -- get residual value
1185 CURSOR residual_csr IS
1186 SELECT NVL(SUM(cs.amount),0)
1187 FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
1188 WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
1189 AND bs.name = 'Residual Value'
1190 AND cs.stream_element_date >= SYSDATE
1191 AND asv.khr_id = p_contract_id;
1192
1193 -- get rent value
1194 CURSOR rent_csr IS
1195 SELECT NVL(SUM(cs.amount),0)
1196 FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
1197 WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
1198 AND bs.name = 'Rent'
1199 AND cs.stream_element_date >= SYSDATE
1200 AND asv.khr_id = p_contract_id ;
1201 -- get unearned amount
1202 CURSOR unearned_csr IS
1203 SELECT NVL(SUM(cs.amount),0)
1204 FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
1205 WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
1206 AND bs.name = 'Unearned Income'
1207 AND cs.stream_element_date >= SYSDATE
1208 AND asv.khr_id = p_contract_id ;
1209 BEGIN
1210 OPEN residual_csr;
1211 FETCH residual_csr into l_return_residual;
1212 CLOSE residual_csr ;
1213 OPEN rent_csr;
1214 FETCH rent_csr into l_return_rent;
1215 CLOSE rent_csr;
1216 OPEN unearned_csr;
1217 FETCH unearned_csr into l_return_unearned;
1218 CLOSE unearned_csr;
1219 x_net_investment := l_return_rent + l_return_residual - l_return_unearned;
1220 RETURN l_return_status;
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1224 ,p_msg_name => G_UNEXPECTED_ERROR
1225 ,p_token1 => G_SQLCODE_TOKEN
1226 ,p_token1_value => SQLCODE
1227 ,p_token2 => G_SQLERRM_TOKEN
1228 ,p_token2_value => SQLERRM);
1229 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1230 END;
1231
1232 ---------------------------------------------------------------------------
1233 -- FUNCTION Advance rent,Security deposit,interest type for contract
1234 ---------------------------------------------------------------------------
1235 FUNCTIOn get_rent_security_interest(
1236 p_contract_id IN NUMBER,
1237 x_advance_rent OUT NOCOPY NUMBER,
1238 x_security_deposit OUT NOCOPY NUMBER,
1239 x_interest_type OUT NOCOPY NUMBER)
1240 RETURN VARCHAR2 IS
1241 l_api_version NUMBER;
1242 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1243 l_msg_count NUMBER;
1244 l_msg_data VARCHAR2(2000);
1245
1246 -- get advanced rent
1247 CURSOR advance_rent_csr IS
1248 SELECT SUM(nvl(orv1.rule_information6,0))
1249 FROM okc_rules_v orv1, okc_rule_groups_b org1
1250 WHERE org1.dnz_chr_id = p_contract_id
1251 AND org1.id = orv1.rgp_id
1252 AND orv1.rule_information_category = 'SLL'
1253 AND EXISTS
1254 ( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
1255 WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
1256 AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
1257 AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
1258 AND object1_id2 = stm.id2 AND stm.name ='RENT');
1259
1260 -- get security deposit
1261 CURSOR security_deposit_csr IS
1262 SELECT SUM(nvl(orv1.rule_information6,0))
1263 FROM okc_rules_v orv1, okc_rule_groups_b org1
1264 WHERE org1.dnz_chr_id = p_contract_id
1265 AND org1.id = orv1.rgp_id
1266 AND orv1.rule_information_category = 'SLL'
1267 AND EXISTS
1268 ( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
1269 WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
1270 AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
1271 AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
1272 AND object1_id2 = stm.id2 AND stm.name ='SECURITY DEPOSIT');
1273
1274 -- get interest type
1275 CURSOR Interest_type_csr IS
1276 SELECT SUM(nvl(orv1.rule_information6,0))
1277 FROM okc_rules_v orv1, okc_rule_groups_b org1
1278 WHERE org1.dnz_chr_id = p_contract_id
1279 AND org1.id = orv1.rgp_id
1280 AND orv1.rule_information_category = 'SLL'
1281 AND EXISTS
1282 ( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
1283 WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
1284 AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
1285 AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
1286 AND object1_id2 = stm.id2 AND stm.name ='SECURITY DEPOSIT');
1287
1288 BEGIN
1289 OPEN advance_rent_csr;
1290 FETCH advance_rent_csr into x_advance_rent;
1291 CLOSE advance_rent_csr ;
1292 OPEN security_deposit_csr;
1293 FETCH security_deposit_csr into x_security_deposit;
1294 CLOSE security_deposit_csr;
1295 OPEN Interest_type_csr;
1296 FETCH Interest_type_csr into x_advance_rent;
1297 CLOSE Interest_type_csr;
1298 RETURN l_return_status;
1299 EXCEPTION
1300 WHEN OTHERS THEN
1301 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1302 ,p_msg_name => G_UNEXPECTED_ERROR
1303 ,p_token1 => G_SQLCODE_TOKEN
1304 ,p_token1_value => SQLCODE
1305 ,p_token2 => G_SQLERRM_TOKEN
1306 ,p_token2_value => SQLERRM);
1307 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1308 END;
1309
1310 ---------------------------------------------------------------------------
1311 -- FUNCTION get_insurance_lapse
1312 ---------------------------------------------------------------------------
1313 FUNCTION get_insurance_lapse(
1314 p_contract_id IN NUMBER,
1315 x_insurance_lapse_yn OUT NOCOPY VARCHAR2)
1316 RETURN VARCHAR2
1317 IS
1318 -- Get If insurance has lapsed for a contract
1319 CURSOR insurance_date_to_csr(p_contract_id NUMBER) IS
1320 SELECT 'N'
1321 FROM OKL_INS_POLICIES_B IPYB
1322 WHERE IPYB.KHR_ID = p_contract_id
1323 AND IPYB.IPY_TYPE <> 'OPTIONAL_POLICY'
1324 AND IPYB.QUOTE_YN = 'N'
1325 AND IPYB.ISS_CODE = 'ACTIVE'
1326 AND SYSDATE BETWEEN IPYB.DATE_FROM AND IPYB.DATE_TO;
1327
1328 l_insurance_lapse_yn VARCHAR2(1) := 'Y';
1329 l_api_version NUMBER;
1330 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1331 l_msg_count NUMBER;
1332 l_msg_data VARCHAR2(2000);
1333
1334 BEGIN
1335
1336 OPEN insurance_date_to_csr(p_contract_id);
1337 FETCH insurance_date_to_csr INTO l_insurance_lapse_yn;
1338 CLOSE insurance_date_to_csr;
1339
1340 x_insurance_lapse_yn := l_insurance_lapse_yn;
1341
1342 RETURN l_return_status;
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1346 ,p_msg_name => G_UNEXPECTED_ERROR
1347 ,p_token1 => G_SQLCODE_TOKEN
1348 ,p_token1_value => SQLCODE
1349 ,p_token2 => G_SQLERRM_TOKEN
1350 ,p_token2_value => SQLERRM);
1351 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1352 RETURN(l_return_status);
1353
1354 END get_insurance_lapse;
1355
1356
1357 ---------------------------------------------------------------------------
1358 -- FUNCTION get_unrefunded_cures
1359 ---------------------------------------------------------------------------
1360 /* FUNCTION get_unrefunded_cures(
1361 p_contract_id IN NUMBER,
1362 x_unrefunded_cures OUT NOCOPY NUMBER)
1363 RETURN VARCHAR2
1364 IS
1365
1366 -- Get unrefunded cures for a contract
1367 CURSOR unrefunded_cures_csr(p_contract_id NUMBER) IS
1368 SELECT SUM(amount)
1369 FROM iex_cure_payment_lines
1370 WHERE chr_id = p_contract_id
1371 AND status = 'CURES_IN_POSSESSION';
1372
1373 l_unrefunded_cures NUMBER := 0;
1374 l_api_version NUMBER;
1375 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1376 l_msg_count NUMBER;
1377 l_msg_data VARCHAR2(2000);
1378
1379 BEGIN
1380
1381 --OPEN unrefunded_cures_csr(p_contract_id);
1382 --FETCH unrefunded_cures_csr INTO l_unrefunded_cures;
1383 --CLOSE unrefunded_cures_csr;
1384
1385 x_unrefunded_cures := l_unrefunded_cures;
1386
1387 RETURN l_return_status;
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1391 ,p_msg_name => G_UNEXPECTED_ERROR
1392 ,p_token1 => G_SQLCODE_TOKEN
1393 ,p_token1_value => SQLCODE
1394 ,p_token2 => G_SQLERRM_TOKEN
1395 ,p_token2_value => SQLERRM);
1396 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1397 RETURN(l_return_status);
1398
1399 END get_unrefunded_cures;*/
1400
1401 ---------------------------------------------------------------------------
1402 -- FUNCTION get_fair_market_value
1403 ---------------------------------------------------------------------------
1404 FUNCTION get_fair_market_value(
1405 p_contract_id IN NUMBER,
1406 x_fair_market_value OUT NOCOPY NUMBER)
1407 RETURN VARCHAR2
1408 IS
1409
1410 l_fair_market_value NUMBER;
1411 l_api_version NUMBER;
1412 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1413 l_msg_count NUMBER;
1414 l_msg_data VARCHAR2(2000);
1415
1416 BEGIN
1417 l_fair_market_value := 0;
1418
1419 x_fair_market_value := l_fair_market_value;
1420
1421 RETURN l_return_status;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1425 ,p_msg_name => G_UNEXPECTED_ERROR
1426 ,p_token1 => G_SQLCODE_TOKEN
1427 ,p_token1_value => SQLCODE
1428 ,p_token2 => G_SQLERRM_TOKEN
1429 ,p_token2_value => SQLERRM);
1430 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1431 RETURN(l_return_status);
1432
1433 END get_fair_market_value;
1434
1435 ---------------------------------------------------------------------------
1436 -- FUNCTION get_net_book_value
1437 ---------------------------------------------------------------------------
1438 FUNCTION get_net_book_value(
1439 p_contract_id IN NUMBER,
1440 x_net_book_value OUT NOCOPY NUMBER)
1441 RETURN VARCHAR2
1442 IS
1443
1444 l_net_book_value NUMBER :=0;
1445 l_api_version NUMBER;
1446 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1447 l_msg_count NUMBER;
1448 l_msg_data VARCHAR2(2000);
1449
1450
1451 CURSOR deal_type(p_contract_id IN NUMBER) IS
1452 SELECT khr.deal_type
1453 FROM okl_k_headers_v khr ,fnd_lookups fnd
1454 WHERE fnd.lookup_type = 'OKL_BOOK_CLASS'
1455 AND fnd.lookup_code = khr.deal_type
1456 AND id = p_contract_id;
1457
1458 l_deal_type VARCHAR2(30);
1459 l_formula_name VARCHAR2(100);
1460
1461 BEGIN
1462
1463 OPEN deal_type(p_contract_id);
1464 FETCH deal_type INTO l_deal_type;
1465 CLOSE deal_type;
1466
1467 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
1468 l_formula_name := 'CONTRACT_NET_INVESTMENT_DF';
1469 ELSIF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN
1470 l_formula_name := 'CONTRACT_NET_INVESTMENT_LOAN';
1471 ELSIF l_deal_type IN ('LEASEOP') THEN
1472 l_formula_name := 'CONTRACT_NET_INVESTMENT_OP';
1473 END IF;
1474
1475 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => 1
1476 ,p_init_msg_list =>'T'
1477 ,x_return_status =>l_return_status
1478 ,x_msg_count =>l_msg_count
1479 ,x_msg_data =>l_msg_data
1480 ,p_formula_name =>l_formula_name
1481 ,p_contract_id =>p_contract_id
1482 ,x_value =>l_net_book_value
1483 );
1484
1485
1486
1487 x_net_book_value :=nvl(l_net_book_value,0);
1488 RETURN l_return_status;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 CLOSE deal_type;
1493 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1494 ,p_msg_name => G_UNEXPECTED_ERROR
1495 ,p_token1 => G_SQLCODE_TOKEN
1496 ,p_token1_value => SQLCODE
1497 ,p_token2 => G_SQLERRM_TOKEN
1498 ,p_token2_value => SQLERRM);
1499 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1500 x_net_book_value :=0;
1501 RETURN(l_return_status);
1502
1503 END get_net_book_value;
1504
1505 ---------------------------------------------------------------------------
1506 -- FUNCTION get_interest
1507 ---------------------------------------------------------------------------
1508 FUNCTION get_interest(
1509 p_contract_id IN NUMBER,
1510 x_interest OUT NOCOPY NUMBER)
1511 RETURN VARCHAR2
1512 IS
1513
1514 l_interest NUMBER;
1515 l_api_version NUMBER;
1516 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1517 l_msg_count NUMBER;
1518 l_msg_data VARCHAR2(2000);
1519
1520 BEGIN
1521 /* Obtain the interest using Formula */
1522
1523 l_interest := 1;
1524
1525 x_interest := l_interest;
1526
1527 RETURN l_return_status;
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1531 ,p_msg_name => G_UNEXPECTED_ERROR
1532 ,p_token1 => G_SQLCODE_TOKEN
1533 ,p_token1_value => SQLCODE
1534 ,p_token2 => G_SQLERRM_TOKEN
1535 ,p_token2_value => SQLERRM);
1536 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1537 RETURN(l_return_status);
1538
1539 END get_interest;
1540
1541 ---------------------------------------------------------------------------
1542 -- FUNCTION get_immediate_purchase_yn
1543 ---------------------------------------------------------------------------
1544 -- Get Rule value for 'Request Immediate Repurchase' Retruns Y/N
1545 FUNCTION get_immediate_repurchase_yn(
1546 p_contract_id IN NUMBER,
1547 x_value OUT NOCOPY VARCHAR2)
1548 RETURN VARCHAR2
1549 IS
1550
1551 l_value VARCHAR2(1);
1552 l_api_version NUMBER;
1553 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1554 l_msg_count NUMBER;
1555 l_msg_data VARCHAR2(2000);
1556
1557 BEGIN
1558 -- implement the function once rules are seeded
1559
1560 l_value := 'N';
1561
1562 x_value := l_value;
1563
1564 RETURN l_return_status;
1565 EXCEPTION
1566 WHEN OTHERS THEN
1567 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1568 ,p_msg_name => G_UNEXPECTED_ERROR
1569 ,p_token1 => G_SQLCODE_TOKEN
1570 ,p_token1_value => SQLCODE
1571 ,p_token2 => G_SQLERRM_TOKEN
1572 ,p_token2_value => SQLERRM);
1573 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1574 RETURN(l_return_status);
1575
1576 END get_immediate_repurchase_yn;
1577
1578 ---------------------------------------------------------------------------
1579 -- FUNCTION get_asset_value
1580 ---------------------------------------------------------------------------
1581 FUNCTION get_asset_value(
1582 p_asset_id IN NUMBER,
1583 p_asset_valuation_type IN VARCHAR2
1584 )
1585 RETURN NUMBER
1586 IS
1587
1588 l_asset_value NUMBER;
1589 l_api_version NUMBER;
1590
1591 BEGIN
1592 /* Obtain the asset Value, perhaps a formula */
1593
1594 l_asset_value := 0;
1595
1596 IF (p_asset_valuation_type = 'FMV') THEN
1597 NULL;
1598 END IF;
1599
1600 IF (p_asset_valuation_type = 'FLV') THEN
1601 NULL;
1602 END IF;
1603
1604 IF (p_asset_valuation_type = 'OLV') THEN
1605 NULL;
1606 END IF;
1607
1608 RETURN l_asset_value;
1609
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1613 ,p_msg_name => G_UNEXPECTED_ERROR
1614 ,p_token1 => G_SQLCODE_TOKEN
1615 ,p_token1_value => SQLCODE
1616 ,p_token2 => G_SQLERRM_TOKEN
1617 ,p_token2_value => SQLERRM);
1618 RETURN(-1);
1619
1620 END get_asset_value;
1621
1622 ---------------------------------------------------------------------------
1623 -- FUNCTION get_notice_of_assignment_yn
1624 ---------------------------------------------------------------------------
1625 -- Get Rule value for 'Notice of Assignment Needed' Returns Y/N
1626 FUNCTION get_notice_of_assignment_yn(
1627 p_contract_id IN NUMBER,
1628 x_assignment_yn OUT NOCOPY VARCHAR2)
1629 RETURN VARCHAR2
1630 IS
1631
1632 l_assignment_yn VARCHAR2(1);
1633 l_api_version NUMBER;
1634 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1635 l_msg_count NUMBER;
1636 l_msg_data VARCHAR2(2000);
1637
1638 BEGIN
1639 -- implement the function once rules are seeded
1640
1641 l_assignment_yn := 'N';
1642
1643 x_assignment_yn := l_assignment_yn;
1644
1645 RETURN l_return_status;
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
1649 ,p_msg_name => G_UNEXPECTED_ERROR
1650 ,p_token1 => G_SQLCODE_TOKEN
1651 ,p_token1_value => SQLCODE
1652 ,p_token2 => G_SQLERRM_TOKEN
1653 ,p_token2_value => SQLERRM);
1654 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1655 RETURN(l_return_status);
1656
1657 END get_notice_of_assignment_yn;
1658
1659 END OKL_CONTRACT_INFO;