1 PACKAGE BODY Okl_Cs_Lc_Contract_Pvt AS
2 /* $Header: OKLRLCRB.pls 120.34.12020000.4 2012/10/08 06:21:48 vsgandhi ship $ */
3 g_user_id NUMBER := Okl_Api.G_MISS_NUM;
4 g_resp_id NUMBER := Okl_Api.G_MISS_NUM;
5 g_user_resource_id okc_k_accesses.resource_id%TYPE := Okl_Api.G_MISS_NUM;
6 g_resp_access okc_k_accesses.access_level%TYPE := Okl_Api.G_MISS_CHAR;
7 g_reset_access_flag BOOLEAN := FALSE;
8 g_scs_code okc_k_headers_b.scs_code%TYPE := Okl_Api.G_MISS_CHAR;
9 g_groups_processed BOOLEAN := FALSE;
10 g_reset_lang_flag BOOLEAN := FALSE;
11 g_reset_resp_flag BOOLEAN := FALSE;
12 --varangan added the formula name variable for bug #5036582
13 g_formula_out_billed CONSTANT okl_formulae_v.name%TYPE := 'OKL_LC_OUTSTANDING_BILLED';
14 g_formula_out_unbilled CONSTANT okl_formulae_v.name%TYPE := 'OKL_LC_OUTSTANDING_UNBILLED';
15 --varangan added the formula name variable for bug #5036582
16
17 --varangan added the formula name variable for bug #5009351
18 g_formula_next_payment_amt CONSTANT okl_formulae_v.name%TYPE := 'OKL_LC_NEXT_PAYMENT_AMOUNT';
19
20 TYPE sec_group_tbl IS TABLE OF okc_k_accesses.group_id%TYPE;
21 g_sec_groups sec_group_tbl;
22
23 PROCEDURE EXECUTE(p_api_version IN NUMBER
24 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
25 ,x_return_status OUT NOCOPY VARCHAR2
26 ,x_msg_count OUT NOCOPY NUMBER
27 ,x_msg_data OUT NOCOPY VARCHAR2
28 ,p_formula_name IN VARCHAR2
29 ,p_contract_id IN NUMBER
30 ,x_value OUT NOCOPY NUMBER
31 ) IS
32 CURSOR deal_type IS
33 SELECT khr.deal_type
34 FROM okl_k_headers_v khr ,fnd_lookups fnd
35 WHERE fnd.lookup_type = 'OKL_BOOK_CLASS'
36 AND fnd.lookup_code = khr.deal_type
37 AND id = p_contract_id;
38
39 --bug# 5032491 rkuttiya added cursor for revenue recognition
40 CURSOR c_revenue_recogn(p_khr_id IN NUMBER) IS
41 SELECT revenue_recognition_method
42 FROM okl_product_parameters_v pdt,
43 okl_k_headers khr
44 WHERE KHR.ID = p_khr_id
45 AND KHR.PDT_ID = PDT.ID;
46
47 l_deal_type VARCHAR2(30);
48 --bug# 5032491
49 l_revenue_recogn VARCHAR2(150);
50 l_outstanding_bal NUMBER;
51 --
52 l_formula_name VARCHAR2(100);
53 BEGIN
54 OPEN deal_type;
55 FETCH deal_type INTO l_deal_type;
56 CLOSE deal_type;
57 OPEN c_revenue_recogn(p_contract_id);
58 FETCH c_revenue_recogn INTO l_revenue_recogn;
59 CLOSE c_revenue_recogn;
60
61 --bug# 5032491 to check for Loan and Loan Revolving
62 IF l_deal_type NOT IN ('LOAN','LOAN-REVOLVING') THEN
63 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
64 l_formula_name := 'CONTRACT_NET_INVESTMENT_DF';
65 --bug# 5032491 rkuttiya commenting out following
66 --ELSIF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN
67 --l_formula_name := 'CONTRACT_NET_INVESTMENT_LOAN';
68 ELSIF l_deal_type IN ('LEASEOP') THEN
69 l_formula_name := 'CONTRACT_NET_INVESTMENT_OP';
70 END IF;
71 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version
72 ,p_init_msg_list =>p_init_msg_list
73 ,x_return_status =>x_return_status
74 ,x_msg_count =>x_msg_count
75 ,x_msg_data =>x_msg_data
76 ,p_formula_name =>l_formula_name
77 ,p_contract_id =>p_contract_id
78 ,x_value =>x_value
79 );
80 END IF;
81
82 -- bug# 5032491 rkuttiya added the following for Loan and Loan Revolving contracts
83 IF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN
84 OPEN c_revenue_recogn(p_contract_id);
85 FETCH c_revenue_recogn INTO l_revenue_recogn;
86 CLOSE c_revenue_recogn;
87
88 l_outstanding_bal := OKL_VARIABLE_INT_UTIL_PVT.get_principal_bal(x_return_status => x_return_status,
89 p_khr_id => p_contract_id,
90 p_kle_id => NULL,
91 p_date => SYSDATE);
92
93 IF l_revenue_recogn = 'ACTUAL' AND l_outstanding_bal <> 0 THEN
94 x_value := 0;
95 ELSE
96 l_formula_name := 'CONTRACT_NET_INVESTMENT_LOAN';
97 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => p_api_version
98 ,p_init_msg_list =>p_init_msg_list
99 ,x_return_status =>x_return_status
100 ,x_msg_count =>x_msg_count
101 ,x_msg_data =>x_msg_data
102 ,p_formula_name =>l_formula_name
103 ,p_contract_id =>p_contract_id
104 ,x_value =>x_value
105 );
106
107 END IF;
108 END IF;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 CLOSE deal_type;
113 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
114 ,p_msg_name => G_UNEXPECTED_ERROR
115 ,p_token1 => G_SQLCODE_TOKEN
116 ,p_token1_value => SQLCODE
117 ,p_token2 => G_SQLERRM_TOKEN
118 ,p_token2_value => SQLERRM);
119 END;
120
121
122 PROCEDURE next_due(p_contract_id IN NUMBER,
123 o_next_due_amt OUT NOCOPY NUMBER,
124 o_next_due_date OUT NOCOPY DATE
125 ) IS
126 --Begin-varangan-bug#5009351
127
128 CURSOR cr_next_payment_date(c_contract_id IN NUMBER) IS
129 SELECT MIN(sel.stream_element_date)
130 FROM okl_strm_elements sel,
131 okl_streams stm,
132 okl_strm_type_v sty
133 WHERE stm.sty_id = sty.id
134 AND stm.say_code = 'CURR'
135 AND stm.active_yn = 'Y'
136 AND sty.billable_yn = 'Y'
137 AND sty.stream_type_purpose NOT LIKE '%TAX%' -- vsgandhi: Bug 14712454
138 AND stm.purpose_code is NULL
139 AND stm.khr_id = c_contract_id
140 AND sel.stm_id = stm.id
141 AND sel.stream_element_date > sysdate;
142
143 lx_return_status VARCHAR2(1);
144 lx_msg_count NUMBER;
145 lx_msg_data VARCHAR2(2000);
146 BEGIN
147 OPEN cr_next_payment_date(p_contract_id);
148 FETCH cr_next_payment_date INTO o_next_due_date;
149 CLOSE cr_next_payment_date;
150
151 Okl_Execute_Formula_Pub.EXECUTE(p_api_version => 1.0
152 ,p_init_msg_list => null
153 ,x_return_status => lx_return_status
154 ,x_msg_count => lx_msg_count
155 ,x_msg_data => lx_msg_data
156 ,p_formula_name => g_formula_next_payment_amt
157 ,p_contract_id => p_contract_id
158 ,x_value => o_next_due_amt
159 );
160
161 --End-varangan-bug#5009351
162
163 /* --Start-commented old code - varangan-Bug#5009351*
164
165 -- rvaduri to fix bug 2949018
166 -- and added the following code.
167 --NOTE: No Multi currency conversions reqd because amounts in
168 --Streams tables are already in Contract Currency.
169
170 --Modified the following cursor by adding c_stream_name
171 --for bug fix 2993308
172 -- replaced type name with purpose, enhancements for user defined streams bug
173 -- 3924303
174 CURSOR next_due_date(c_contract_id NUMBER,c_stream_type_purpose VARCHAR2) IS
175 SELECT MIN(sel.stream_element_date)
176 FROM okl_strm_elements sel
177 ,okl_streams stm
178 ,okl_strm_type_v sty
179 WHERE sty.stream_type_purpose = c_stream_type_purpose
180 AND stm.sty_id = sty.id
181 AND stm.say_code = 'CURR'
182 AND stm.active_yn = 'Y'
183 AND stm.purpose_code is NULL
184 AND stm.khr_id = c_contract_id
185 AND sel.stm_id = stm.id
186 and date_billed is null
187 AND sel.amount > 0 ;
188
189
190 --Modified the following cursor by adding c_stream_name
191 --for bug fix 2993308
192 -- replaced type name with purpose, enhancements for user defined streams bug
193 -- 3924303
194 CURSOR next_due_amount(c_next_due_date DATE,c_contract_id NUMBER
195 ,c_stream_type_purpose VARCHAR2) IS
196 SELECT NVL(sum(sel.amount),0)
197 FROM okl_strm_elements sel,
198 okl_streams stm,
199 okl_strm_type_v sty
200 --Bug 4084405
201 WHERE sty.stream_type_purpose = c_stream_type_purpose
202 AND stm.sty_id = sty.id
203 AND stm.say_code = 'CURR'
204 AND stm.active_yn = 'Y'
205 AND stm.purpose_code is NULL
206 AND stm.khr_id = c_contract_id
207 AND sel.stm_id = stm.id
208 AND date_billed is null
209 AND sel.stream_element_date = c_next_due_date;
210
211 --Added the following cursor for bug fix 2993308
212
213 CURSOR deal_type IS
214 SELECT deal_type
215 FROM okl_k_headers
216 WHERE id = p_contract_id;
217
218 l_deal_type VARCHAR2(30);
219 l_stream_name VARCHAR2(30);
220 l_stream_type_purpose_for_date VARCHAR2(100);
221 l_stream_type_purpose_for_amt VARCHAR2(100);
222
223 BEGIN
224
225 OPEN deal_type;
226 FETCH deal_type INTO l_deal_type;
227 CLOSE deal_type;
228
229 IF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN
230 --l_stream_name := 'LOAN PAYMENT';
231 --Bug 4084405
232 --For getting the Due date we will use Principal Payment
233 --as loan payment is not a billable stream.
234 --For getting the next payment amount we will use loan payment
235 --as the payment comprises of prin payment + int Payment.
236 l_stream_type_purpose_for_date := 'PRINCIPAL_PAYMENT';
237 l_stream_type_purpose_for_amt := 'LOAN_PAYMENT';
238
239 ELSE
240 -- l_stream_name := 'RENT';
241 --bug 4084405
242 l_stream_type_purpose_for_date := 'RENT';
243 l_stream_type_purpose_for_amt := 'RENT';
244 END IF;
245
246 OPEN next_due_date(p_contract_id,l_stream_type_purpose_for_date);
247 FETCH next_due_date INTO o_next_due_date;
248 CLOSE next_due_date;
249 IF (o_next_due_date is not null) then
250 OPEN next_due_amount(o_next_due_date,p_contract_id
251 ,l_stream_type_purpose_for_amt);
252 FETCH next_due_amount INTO o_next_due_amt;
253 CLOSE next_due_amount;
254 ELSE
255 o_next_due_amt := 0;
256 END IF;
257 --End-commented-varangan-Bug#5009351*/
258 EXCEPTION
259 WHEN OTHERS THEN
260 IF cr_next_payment_date%ISOPEN
261 THEN
262 CLOSE cr_next_payment_date;
263 END IF;
264
265 /* Commented-varangan-Bug#5009351
266 CLOSE next_due_date;
267 CLOSE next_due_amount; */
268
269 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
270 ,p_msg_name => G_UNEXPECTED_ERROR
271 ,p_token1 => G_SQLCODE_TOKEN
272 ,p_token1_value => SQLCODE
273 ,p_token2 => G_SQLERRM_TOKEN
274 ,p_token2_value => SQLERRM);
275 END next_due;
276
277
278
279 PROCEDURE last_due(p_customer_id IN NUMBER,
280 p_contract_id IN NUMBER,
281 o_last_due_amt OUT NOCOPY NUMBER,
282 o_last_due_date OUT NOCOPY DATE
283 ) IS
284 CURSOR last_due IS
285 --MultiCurrency Changes.
286 --mutlipying the amount with exchange rate to convert the currency from
287 --Transaction currency to Functional Curreny.
288 --dkagrawa changed the query to adopt new billing architecture
289 /*SELECT (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
290 FROM ar_cash_receipts_all ar,
291 okc_k_headers_b chr,
292 ar_receivable_applications_all ara,
293 okl_cnSld_ar_strms_b lsm
294 WHERE
295 ar.pay_from_customer = chr.cust_acct_id
296 and chr.id = p_contract_id
297 AND ar.cash_receipt_id = ara.cash_receipt_id
298 AND LSM.RECEIVABLES_INVOICE_ID = ARA.APPLIED_CUSTOMER_TRX_ID
299 AND lsm.khr_id = p_contract_id
300 ORDER BY receipt_date DESC;*/
301
302 /*
303 SELECT (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
304 FROM ar_cash_receipts_all ar,
305 okl_receipt_applications_uv app,
306 okc_k_headers_b chr
307 WHERE chr.id = p_contract_id
308 AND ar.cash_receipt_id = app.cash_receipt_id
309 AND ar.pay_from_customer = p_customer_id
310 AND CHR.contract_number = app.contract_number
311 ORDER BY receipt_date DESC;*/
312
313 -- Modified cursor for bug 11810605
314 /*select
315 (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
316 FROM ar_cash_receipts_all ar,
317 okl_receipt_applications_uv app
318 WHERE
319 ar.cash_receipt_id = app.cash_receipt_id
320 AND ar.pay_from_customer = p_customer_id
321 AND customer_trx_id in
322 (
323 select distinct b.customer_trx_id from ra_customer_trx_all b, ra_customer_trx_lines_all a
324 where a.interface_line_attribute6=(select contract_number from okc_k_headers_b where id=p_contract_id)
325 and a.customer_trx_id=b.customer_trx_id
326 )
327 ORDER BY receipt_date DESC; */
328
329 -- Added for performance issue 13082893
330 select
331 (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
332 FROM ar_cash_receipts_all ar,
333 okl_receipt_applications_uv app
334 WHERE
335 ar.cash_receipt_id = app.cash_receipt_id
336 AND ar.pay_from_customer = p_customer_id
337 AND exists
338 (
339 select 'x' --distinct b.customer_trx_id
340 --select distinct b.customer_trx_id
341 from ra_customer_trx_all b, ra_customer_trx_lines_all a
342 where a.interface_line_attribute6=(select contract_number from okc_k_headers_all_b where id=p_contract_id)
343 and a.customer_trx_id=b.customer_trx_id
344 and a.customer_trx_id = app.customer_trx_id
345 )
346 ORDER BY receipt_date DESC;
347
348 BEGIN
349 OPEN last_due;
350 FETCH last_due INTO o_last_due_amt,o_last_due_date;
351 CLOSE last_due;
352 EXCEPTION
353 WHEN OTHERS THEN
354 CLOSE last_due;
355 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
356 ,p_msg_name => G_UNEXPECTED_ERROR
357 ,p_token1 => G_SQLCODE_TOKEN
358 ,p_token1_value => SQLCODE
359 ,p_token2 => G_SQLERRM_TOKEN
360 ,p_token2_value => SQLERRM);
361 END;
362 PROCEDURE total_asset_cost(p_contract_id IN NUMBER,
363 o_asset_cost OUT NOCOPY NUMBER
364 ) IS
365
366 --Modified for bug 8533160 to not include ABANDONED assets
367 CURSOR asset_cost IS
368 select nvl(sum(okl.oec) ,0)
369 from okc_k_lines_v okc
370 ,okl_k_lines okl
371 ,okc_line_styles_v lse
372 where okc.id=okl.id
373 and okc.lse_id = lse.id
374 and lse.lty_code='FREE_FORM1'
375 and okc.sts_code NOT IN ('ABANDONED')
376 and okc.chr_id = p_contract_id;
377
378 BEGIN
379 OPEN asset_cost;
380 FETCH asset_cost INTO o_asset_cost;
381 CLOSE asset_cost;
382 EXCEPTION
383 WHEN OTHERS THEN
384 CLOSE asset_cost;
385 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
386 ,p_msg_name => G_UNEXPECTED_ERROR
387 ,p_token1 => G_SQLCODE_TOKEN
388 ,p_token1_value => SQLCODE
389 ,p_token2 => G_SQLERRM_TOKEN
390 ,p_token2_value => SQLERRM);
391 END;
392
393 -- smoduga
394 -- Added for displaying Total subsidised cost for the Contract on forms Overview tab.
395 PROCEDURE total_subsidy_cost(p_contract_id IN NUMBER,
396 o_subsidy_cost OUT NOCOPY NUMBER
397 ) IS
398
399 l_parent_line_id NUMBER;
400
401 CURSOR parent_line_id IS
402 select okc.id
403 from okc_k_lines_v okc
404 ,okl_k_lines okl
405 ,okc_line_styles_v lse
406 where okc.id=okl.id
407 and okc.lse_id = lse.id
408 and lse.lty_code='FREE_FORM1'
409 and okc.chr_id = p_contract_id;
410
411 CURSOR c_subsidy(c_contract_id IN NUMBER,c_parent_line_id IN NUMBER) IS
412 select nvl(sum(nvl(KLE1.subsidy_override_amount,KLE1.amount)),0) amount
413 from OKL_K_LINES KLE1,
414 OKC_K_LINES_B CLE1,
415 OKC_LINE_STYLES_B LS1,
416 OKL_ASSET_SUBSIDY_UV SUB,
417 OKL_SUBSIDIES_B SUBB,
418 OKL_SUBSIDIES_TL SUBT,
419 OKC_STATUSES_V STS1
420 where KLE1.ID = CLE1.ID
421 AND CLE1.LSE_ID = LS1.ID
422 AND LS1.LTY_CODE ='SUBSIDY'
423 AND cle1.dnz_chr_id = c_contract_id -- from parameter
424 AND CLE1.STS_CODE = STS1.CODE
425 AND CLE1.STS_CODE <> 'ABANDONED'
426 AND SUB.SUBSIDY_ID = KLE1.SUBSIDY_ID
427 AND SUB.ASSET_CLE_ID = c_parent_line_id -- parent_line_id from grid
428 AND SUB.dnz_chr_id = cle1.dnz_chr_id
429 AND SUB.subsidy_cle_id = KLE1.ID
430 AND SUBB.ID = KLE1.SUBSIDY_ID
431 AND SUBT.ID = SUBB.ID
432 And subt.language = userenv('LANG')
433 AND SUBB.accounting_method_code = 'NET'
434 AND SUBB.CUSTOMER_VISIBLE_YN = 'Y';
435
436
437
438 BEGIN
439 OPEN parent_line_id;
440 FETCH parent_line_id INTO l_parent_line_id;
441 CLOSE parent_line_id;
442
443 OPEN c_subsidy(p_contract_id,l_parent_line_id);
444 FETCH c_subsidy INTO o_subsidy_cost;
445 IF (c_subsidy%NOTFOUND) THEN
446 CLOSE c_subsidy;
447 o_subsidy_cost := 0;
448 END IF;
449 CLOSE c_subsidy;
450
451 EXCEPTION
452 WHEN OTHERS THEN
453 CLOSE parent_line_id;
454 CLOSE c_subsidy;
455 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
456 ,p_msg_name => G_UNEXPECTED_ERROR
457 ,p_token1 => G_SQLCODE_TOKEN
458 ,p_token1_value => SQLCODE
459 ,p_token2 => G_SQLERRM_TOKEN
460 ,p_token2_value => SQLERRM);
461 END;
462
463 --varangan bug#5036582, added the procedures to get the billed and unbilled
464 --outstanding using formulas starts
465
466 PROCEDURE out_standing_rcvble(p_contract_id IN NUMBER,
467 o_rcvble_amt OUT NOCOPY NUMBER) IS
468 lx_return_status VARCHAR2(1);
469 lx_msg_count NUMBER;
470 lx_msg_data VARCHAR2(2000);
471 BEGIN
472 null;
473 EXCEPTION
474 WHEN OTHERS THEN
475 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
476 ,p_msg_name => G_UNEXPECTED_ERROR
477 ,p_token1 => G_SQLCODE_TOKEN
478 ,p_token1_value => SQLCODE
479 ,p_token2 => G_SQLERRM_TOKEN
480 ,p_token2_value => SQLERRM);
481 END out_standing_rcvble;
482
483 PROCEDURE outstanding_billed_amt(p_contract_id IN NUMBER,
484 o_billed_amt OUT NOCOPY NUMBER) IS
485 lx_return_status VARCHAR2(1);
486 lx_msg_count NUMBER;
487 lx_msg_data VARCHAR2(2000);
488 BEGIN
489 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => 1.0,
490 p_init_msg_list => null,
491 x_return_status => lx_return_status,
492 x_msg_count => lx_msg_count,
493 x_msg_data => lx_msg_data,
494 p_formula_name => g_formula_out_billed,
495 p_contract_id => p_contract_id,
496 x_value => o_billed_amt);
497 EXCEPTION
498 WHEN OTHERS THEN
499 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
500 ,p_msg_name => G_UNEXPECTED_ERROR
501 ,p_token1 => G_SQLCODE_TOKEN
502 ,p_token1_value => SQLCODE
503 ,p_token2 => G_SQLERRM_TOKEN
504 ,p_token2_value => SQLERRM);
505 END outstanding_billed_amt;
506
507 PROCEDURE outstanding_unbilled_amt(p_contract_id IN NUMBER,
508 o_unbilled_amt OUT NOCOPY NUMBER) IS
509 lx_return_status VARCHAR2(1);
510 lx_msg_count NUMBER;
511 lx_msg_data VARCHAR2(2000);
512 BEGIN
513 OKL_EXECUTE_FORMULA_PUB.execute(p_api_version => 1.0,
514 p_init_msg_list => null,
515 x_return_status => lx_return_status,
516 x_msg_count => lx_msg_count,
517 x_msg_data => lx_msg_data,
518 p_formula_name => g_formula_out_unbilled,
519 p_contract_id => p_contract_id,
520 x_value => o_unbilled_amt);
521 EXCEPTION
522 WHEN OTHERS THEN
523 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
524 ,p_msg_name => G_UNEXPECTED_ERROR
525 ,p_token1 => G_SQLCODE_TOKEN
526 ,p_token1_value => SQLCODE
527 ,p_token2 => G_SQLERRM_TOKEN
528 ,p_token2_value => SQLERRM);
529 END outstanding_unbilled_amt;
530
531 --varangan bug#5036582 end
532
533
534 PROCEDURE contract_dates(p_contract_id IN NUMBER,
535 o_start_date OUT NOCOPY DATE,
536 o_end_date OUT NOCOPY DATE,
537 o_term_duration OUT NOCOPY NUMBER) IS
538 CURSOR contract_dates IS
539 SELECT khr.start_date,khr.end_date,okhr.term_duration
540 FROM OKL_K_HEADERS okhr ,okc_k_headers_v khr
541 WHERE okhr.id = khr.id
542 AND khr.id = p_contract_id;
543 BEGIN
544 OPEN contract_dates;
545 FETCH contract_dates INTO o_start_date,o_end_date,o_term_duration;
546 CLOSE contract_dates ;
547 EXCEPTION
548 WHEN OTHERS THEN
549 CLOSE contract_dates ;
550 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
551 ,p_msg_name => G_UNEXPECTED_ERROR
552 ,p_token1 => G_SQLCODE_TOKEN
553 ,p_token1_value => SQLCODE
554 ,p_token2 => G_SQLERRM_TOKEN
555 ,p_token2_value => SQLERRM);
556 END;
557 PROCEDURE rent_security_interest(p_contract_id IN NUMBER,
558 o_advance_rent OUT NOCOPY NUMBER,
559 o_security_deposit OUT NOCOPY NUMBER,
560 o_interest_type OUT NOCOPY VARCHAR2) IS
561 CURSOR advance_rent IS
562 SELECT SUM(NVL(orv1.rule_information6,0))
563 FROM okc_rules_v orv1,
564 okc_rule_groups_b org1
565 WHERE org1.dnz_chr_id = p_contract_id
566 and org1.dnz_chr_id = org1.chr_id
567 AND org1.id = orv1.rgp_id
568 AND orv1.rule_information_category = 'LASLL'
569 AND exists
570 ( SELECT 'x'
571 FROM okc_k_headers_v okhdr,
572 okc_rule_groups_b org,
573 okc_rules_v orv,
574 OKL_STRMTYP_SOURCE_V stm
575 WHERE okhdr.id = org1.dnz_chr_id
576 and okhdr.id = org.dnz_chr_id
577 and org.chr_id = org.dnz_chr_id
578 AND org.rgd_code = 'LALEVL'
579 AND org.id = orv.rgp_id
580 AND orv.rule_information_category ='LASLH'
581 AND jtot_object1_code ='OKL_STRMTYP'
582 AND object1_id1 = stm.id1
583 AND object1_id2 = stm.id2
584 AND stm.stream_type_purpose ='RENT');
585 -- cursor changed to filter the streams based on the purpose 'SECURITY_DEPOSIT'
586 -- and the amounts summed up, enhancement done for user defined streams impacts, bug 3924303
587
588 CURSOR security_deposit IS
589 SELECT ste.amount amount
590 FROM okc_k_lines_b kle,
591 okc_line_styles_b lse,
592 okc_k_items ite,
593 okl_strm_type_b sty1,
594 okl_streams stm,
595 okl_strm_type_b sty2,
596 okl_strm_elements ste
597 WHERE kle.chr_id = p_contract_id
598 AND lse.id = kle.lse_id
599 AND lse.lty_code = 'FEE'
600 AND ite.cle_id = kle.id
601 AND ite.jtot_object1_code = 'OKL_STRMTYP'
602 AND sty1.id = ite.object1_id1
603 AND sty1.stream_type_purpose= 'SECURITY_DEPOSIT'
604 AND stm.kle_id = kle.id
605 AND stm.khr_id = p_contract_id
606 AND stm.active_yn = 'Y'
607 AND stm.say_code = 'CURR'
608 --multigaap changes
609 AND stm.PURPOSE_CODE IS NULL
610 --end multigaap changes
611 AND sty2.id = stm.sty_id
612 AND sty2.stream_type_purpose= 'SECURITY_DEPOSIT'
613 AND ste.stm_id = stm.id
614 AND ste.date_billed IS NOT NULL
615 AND NVL (ste.amount, 0) <> 0;
616 --rkuttiya modifed for bug # 5031455 join condition of cursor to use dnz_chr_id instead of chr_id
617 CURSOR Interest_Type IS
618 SELECT DECODE(rule_information1, 'Y', 'Variable', 'N', 'Fixed', 'Unknown')
619 FROM OKC_K_HEADERS_B CHR,okc_rule_groups_b RGP,okc_rules_b RUL
620 WHERE CHR.ID = RGP.DNZ_CHR_ID AND
621 --CHR.ID = RGP.CHR_ID AND
622 RGP.ID = RUL.RGP_ID AND
623 RUL.rule_information_category = 'LAINTP' AND
624 CHR.id = p_contract_id;
625
626 BEGIN
627 OPEN advance_rent;
628 FETCH advance_rent INTO o_advance_rent;
629 CLOSE advance_rent ;
630 OPEN security_deposit;
631 FETCH security_deposit INTO o_security_deposit;
632 CLOSE security_deposit;
633 OPEN Interest_type;
634 FETCH Interest_type INTO o_interest_type;
635 CLOSE Interest_type;
636 EXCEPTION
637 WHEN OTHERS THEN
638 CLOSE advance_rent ;
639 CLOSE security_deposit;
640 CLOSE Interest_type;
641 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
642 ,p_msg_name => G_UNEXPECTED_ERROR
643 ,p_token1 => G_SQLCODE_TOKEN
644 ,p_token1_value => SQLCODE
645 ,p_token2 => G_SQLERRM_TOKEN
646 ,p_token2_value => SQLERRM);
647 END;
648 PROCEDURE notes(p_contract_id IN NUMBER,
649 o_notes OUT NOCOPY VARCHAR2
650 ) IS
651 CURSOR notes IS
652 SELECT notes,last_update_date FROM jtf_notes_vl
653 WHERE source_object_id = p_contract_id
654 AND SOURCE_OBJECT_CODE = 'OKC_K_HEADER'
655 ORDER BY last_update_date DESC;
656 l_date DATE;
657 BEGIN
658 OPEN notes;
659 FETCH notes INTO o_notes,l_date;
660 CLOSE notes;
661 EXCEPTION
662 WHEN OTHERS THEN
663 CLOSE notes;
664 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
665 ,p_msg_name => G_UNEXPECTED_ERROR
666 ,p_token1 => G_SQLCODE_TOKEN
667 ,p_token1_value => SQLCODE
668 ,p_token2 => G_SQLERRM_TOKEN
669 ,p_token2_value => SQLERRM);
670 END;
671 ---------------------------------------------------------------------------
672 -- FUNCTION get_vendor_program
673 ---------------------------------------------------------------------------
674 FUNCTION get_vendor_program(
675 p_contract_id IN NUMBER,
676 x_vendor_program OUT NOCOPY VARCHAR2)
677 RETURN VARCHAR2
678 IS
679 CURSOR vendor_program_cur(p_contract_id NUMBER) IS
680 SELECT PROGRAM_CONTRACT_NUMBER
681 FROM okl_k_hdrs_full_uv
682 WHERE CHR_ID = p_contract_id;
683 l_vendor_program VARCHAR2(240);
684 l_api_version NUMBER;
685 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
686 l_msg_count NUMBER;
687 l_msg_data VARCHAR2(2000);
688 BEGIN
689 OPEN vendor_program_cur(p_contract_id);
690 FETCH vendor_program_cur INTO l_vendor_program;
691 CLOSE vendor_program_cur;
692 x_vendor_program := l_vendor_program;
693 RETURN l_return_status;
694 EXCEPTION
695 WHEN OTHERS THEN
696 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
697 ,p_msg_name => G_UNEXPECTED_ERROR
698 ,p_token1 => G_SQLCODE_TOKEN
699 ,p_token1_value => SQLCODE
700 ,p_token2 => G_SQLERRM_TOKEN
701 ,p_token2_value => SQLERRM);
702 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
703 RETURN(l_return_status);
704 END get_vendor_program;
705 ---------------------------------------------------------------------------
706 -- FUNCTION get_private_label
707 ---------------------------------------------------------------------------
708 FUNCTION get_private_label(
709 p_contract_id IN NUMBER,
710 x_private_label OUT NOCOPY VARCHAR2)
711 RETURN VARCHAR2
712 IS
713 l_api_version NUMBER :=1;
714 l_init_msg_list VARCHAR2(1) DEFAULT Okl_Api.G_FALSE;
715 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
716 l_msg_count NUMBER;
717 l_msg_data VARCHAR2(2000);
718 l_party_tab Okl_Jtot_Extract.party_tab_type;
719 BEGIN
720 -- Procedure to call to get Private Label ID, nothing but
721 -- a Role
722 Okl_Jtot_Extract.Get_Party (
723 l_api_version,
724 l_init_msg_list,
725 l_return_status,
726 l_msg_count,
727 l_msg_data,
728 p_contract_id,
729 NULL,
730 'PRIVATE_LABEL',
731 'S',
732 l_party_tab
733 );
734 x_private_label := l_party_tab(1).name;
735 RETURN l_return_status;
736 EXCEPTION
737 WHEN OTHERS THEN
738 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
739 ,p_msg_name => G_UNEXPECTED_ERROR
740 ,p_token1 => G_SQLCODE_TOKEN
741 ,p_token1_value => SQLCODE
742 ,p_token2 => G_SQLERRM_TOKEN
743 ,p_token2_value => SQLERRM);
744 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
745 RETURN(l_return_status);
746 END get_private_label;
747 ---------------------------------------------------------------------------
748 -- FUNCTION get_currency
749 ---------------------------------------------------------------------------
750 FUNCTION get_currency(
751 p_contract_id IN NUMBER,
752 x_currency OUT NOCOPY VARCHAR2)
753 RETURN VARCHAR2
754 IS
755 CURSOR currency_cur(p_contract_id NUMBER) IS
756 SELECT currency_code
757 FROM okc_k_headers_b
758 WHERE id = p_contract_id;
759 l_currency VARCHAR2(240);
760 l_api_version NUMBER;
761 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
762 l_msg_count NUMBER;
763 l_msg_data VARCHAR2(2000);
764 BEGIN
765 OPEN currency_cur(p_contract_id);
766 FETCH currency_cur INTO l_currency;
767 CLOSE currency_cur;
768 x_currency := l_currency;
769 RETURN l_return_status;
770 EXCEPTION
771 WHEN OTHERS THEN
772 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
773 ,p_msg_name => G_UNEXPECTED_ERROR
774 ,p_token1 => G_SQLCODE_TOKEN
775 ,p_token1_value => SQLCODE
776 ,p_token2 => G_SQLERRM_TOKEN
777 ,p_token2_value => SQLERRM);
778 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
779 RETURN(l_return_status);
780 END get_currency;
781 ---------------------------------------------------------------------------
782 -- FUNCTION get_syndicate_flag
783 ---------------------------------------------------------------------------
784 FUNCTION get_syndicate_flag(
785 p_contract_id IN NUMBER,
786 x_syndicate_flag OUT NOCOPY VARCHAR2)
787 RETURN VARCHAR2
788 IS
789 CURSOR syndicate_flag_cur(p_contract_id NUMBER) IS
790 SELECT 'Y' FROM okc_k_headers_b CHR
791 WHERE id = p_contract_id
792 AND EXISTS
793 (
794 SELECT 'x' FROM okc_k_items cim
795 WHERE cim.object1_id1 = TO_CHAR(CHR.id)
796 AND EXISTS
797 (
798 SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
799 WHERE cle.lse_id = lse.id
800 AND lse.lty_code = 'SHARED'
801 AND cle.id = cim.cle_id
802 )
803 AND EXISTS
804 (
805 SELECT 'x' FROM okc_k_headers_b chr2
806 WHERE chr2.id = cim.dnz_chr_id
807 AND chr2.scs_code = 'SYNDICATION'
808 AND chr2.sts_code NOT IN ('TERMINATED','ABANDONED')
809 )
810 )
811 AND CHR.scs_code IN ('LEASE','LOAN');
812 l_syndicate_flag VARCHAR2(1) := 'N';
813 l_api_version NUMBER;
814 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
815 l_msg_count NUMBER;
816 l_msg_data VARCHAR2(2000);
817 BEGIN
818 OPEN syndicate_flag_cur(p_contract_id);
819 FETCH syndicate_flag_cur INTO l_syndicate_flag;
820 CLOSE syndicate_flag_cur;
821 x_syndicate_flag := l_syndicate_flag;
822 RETURN l_return_status;
823 EXCEPTION
824 WHEN OTHERS THEN
825 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
826 ,p_msg_name => G_UNEXPECTED_ERROR
827 ,p_token1 => G_SQLCODE_TOKEN
828 ,p_token1_value => SQLCODE
829 ,p_token2 => G_SQLERRM_TOKEN
830 ,p_token2_value => SQLERRM);
831 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
832 RETURN(l_return_status);
833 END get_syndicate_flag;
834 ---------------------------------------------------------------------------
835 -- FUNCTION GET_ORG_ID
836 ---------------------------------------------------------------------------
837 FUNCTION GET_ORG_ID(
838 p_contract_id IN NUMBER,
839 x_org_id OUT NOCOPY NUMBER
840 )
841 RETURN VARCHAR2 AS
842 -- get org_id for contract
843 CURSOR get_org_id_cur (p_contract_id IN VARCHAR2) IS
844 SELECT authoring_org_id
845 FROM okc_k_headers_b
846 WHERE id = p_contract_id;
847 l_api_version NUMBER;
848 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
849 l_msg_count NUMBER;
850 l_msg_data VARCHAR2(2000);
851 BEGIN
852 OPEN get_org_id_cur(p_contract_id);
853 FETCH get_org_id_cur INTO x_org_id;
854 CLOSE get_org_id_cur;
855 RETURN l_return_status;
856 EXCEPTION
857 WHEN OTHERS THEN
858 CLOSE get_org_id_cur;
859 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
860 ,p_msg_name => G_UNEXPECTED_ERROR
861 ,p_token1 => G_SQLCODE_TOKEN
862 ,p_token1_value => SQLCODE
863 ,p_token2 => G_SQLERRM_TOKEN
864 ,p_token2_value => SQLERRM);
865 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
866 RETURN(l_return_status);
867 END GET_ORG_ID;
868 ---------------------------------------------------------------------------
869 -- FUNCTION GET_RESOURCE_ID
870 ---------------------------------------------------------------------------
871 FUNCTION GET_RESOURCE_ID( x_res_id OUT NOCOPY NUMBER
872 )
873 RETURN VARCHAR2 AS
874 -- get org_id for contract
875 CURSOR get_res_id_cur(l_user_id NUMBER) IS
876 SELECT resource_id
877 FROM jtf_rs_resource_extns
878 WHERE user_id = l_user_id;
879 l_api_version NUMBER;
880 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
881 l_msg_count NUMBER;
882 l_msg_data VARCHAR2(2000);
883 l_user_id NUMBER(10);
884 BEGIN
885 l_user_id := Fnd_Profile.value('USER_ID');
886 OPEN get_res_id_cur(l_user_id );
887 FETCH get_res_id_cur INTO x_res_id;
888 CLOSE get_res_id_cur;
889 RETURN l_return_status;
890 EXCEPTION
891 WHEN OTHERS THEN
892 CLOSE get_res_id_cur;
893 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
894 ,p_msg_name => G_UNEXPECTED_ERROR
895 ,p_token1 => G_SQLCODE_TOKEN
896 ,p_token1_value => SQLCODE
897 ,p_token2 => G_SQLERRM_TOKEN
898 ,p_token2_value => SQLERRM);
899 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
900 RETURN(l_return_status);
901 END GET_resource_ID;
902 FUNCTION get_warning_message(
903 p_contract_id IN NUMBER,
904 x_delinquent_flag OUT NOCOPY VARCHAR2,
905 x_bankrupt_flag OUT NOCOPY VARCHAR2,
906 x_syndicate_flag OUT NOCOPY VARCHAR2,
907 x_special_handling_flag OUT NOCOPY VARCHAR2
908 )
909 RETURN VARCHAR2
910 IS
911 CURSOR syndicate_flag_cur(p_contract_id NUMBER) IS
912 SELECT 'Y' FROM okc_k_headers_b CHR
913 WHERE id = p_contract_id
914 AND EXISTS
915 (
916 SELECT 'x' FROM okc_k_items cim
917 WHERE cim.object1_id1 = TO_CHAR(CHR.id)
918 AND EXISTS
919 (
920 SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
921 WHERE cle.lse_id = lse.id
922 AND lse.lty_code = 'SHARED'
923 AND cle.id = cim.cle_id
924 )
925 AND EXISTS
926 (
927 SELECT 'x' FROM okc_k_headers_b chr2
928 WHERE chr2.id = cim.dnz_chr_id
929 AND chr2.scs_code = 'SYNDICATION'
930 AND chr2.sts_code NOT IN ('TERMINATED','ABANDONED')
931 )
932 )
933 AND CHR.scs_code IN ('LEASE','LOAN');
934 l_flag VARCHAR2(1) := 'N';
935 l_api_version NUMBER;
936 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
937 l_msg_count NUMBER;
938 l_msg_data VARCHAR2(2000);
939 BEGIN
940 OPEN syndicate_flag_cur(p_contract_id);
941 FETCH syndicate_flag_cur INTO l_flag;
942 CLOSE syndicate_flag_cur;
943 x_syndicate_flag := l_flag;
944 RETURN l_return_status;
945 EXCEPTION
946 WHEN OTHERS THEN
947 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
948 ,p_msg_name => G_UNEXPECTED_ERROR
949 ,p_token1 => G_SQLCODE_TOKEN
950 ,p_token1_value => SQLCODE
951 ,p_token2 => G_SQLERRM_TOKEN
952 ,p_token2_value => SQLERRM);
953 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
954 RETURN(l_return_status);
955 END get_warning_message;
956 PROCEDURE Set_Connection_Context IS
957 BEGIN
958 IF (g_user_id = Okl_Api.G_MISS_NUM) OR
959 (g_user_id <> Fnd_Global.user_id) THEN
960 g_user_id := Fnd_Global.user_id;
961 g_reset_access_flag := TRUE;
962 g_reset_lang_flag := TRUE;
963 END IF;
964 IF (g_resp_id = Okl_Api.G_MISS_NUM) OR
965 (g_resp_id <> Fnd_Global.resp_id) THEN
966 g_resp_id := Fnd_Global.resp_id;
967 g_reset_resp_flag := TRUE;
968 END IF;
969 END;
970 FUNCTION Get_K_Access_Level(p_chr_id IN NUMBER,
971 p_scs_code IN VARCHAR2 DEFAULT NULL)
972 RETURN VARCHAR2 IS
973 l_scs_code okc_k_headers_b.scs_code%TYPE;
974 l_modify_access CONSTANT VARCHAR2(1) := 'U';
975 l_read_access CONSTANT VARCHAR2(1) := 'R';
976 l_no_access CONSTANT VARCHAR2(1) := 'N';
977 l_resp_access okc_subclass_resps.access_level%TYPE;
978 l_resource_access okc_subclass_resps.access_level%TYPE;
979 l_group_access okc_k_accesses.access_level%TYPE;
980 l_group_id okc_k_accesses.group_id%TYPE;
981 l_row_notfound BOOLEAN;
982 l_group_has_read_access BOOLEAN;
983 l_date DATE := SYSDATE;
984 exception_modify_access EXCEPTION;
985 exception_read_access EXCEPTION;
986 exception_no_access EXCEPTION;
987 -- This cursor retrieves the sub class code for the contract. This is
988 -- executed only if the subclass is not passed in
989 CURSOR chr_csr IS
990 SELECT scs_code
991 FROM okc_k_headers_b
992 WHERE id = p_chr_id;
993 -- This cursor checks to see the type of access granted to the current
994 -- user'S responsibility TO the sub class
995 CURSOR resp_csr IS
996 SELECT ras.access_level
997 FROM okc_subclass_resps ras
998 WHERE ras.scs_code = l_scs_code
999 AND ras.resp_id = Fnd_Global.resp_id
1000 AND l_date BETWEEN ras.start_date AND NVL(ras.end_date, l_date);
1001 -- This cursor retrieves the resource id corresponding to the logged
1002 -- in user. The resource has to have a role of CONTRACT for this to be
1003 -- considered
1004 CURSOR res_csr IS
1005 SELECT res.resource_id
1006 FROM jtf_rs_resource_extns res,
1007 jtf_rs_role_relations rrr,
1008 jtf_rs_roles_b rr
1009 WHERE res.user_id = Fnd_Global.user_id
1010 AND l_date BETWEEN res.start_date_active
1011 AND NVL(res.end_date_active, l_date)
1012 AND res.resource_id = rrr.role_resource_id
1013 AND rrr.role_resource_type = 'RS_INDIVIDUAL'
1014 AND NVL(rrr.delete_flag,'N') = 'N'
1015 AND l_date BETWEEN rrr.start_date_active
1016 AND NVL(rrr.end_date_active, l_date)
1017 AND rrr.role_id = rr.role_id
1018 AND rr.role_type_code = 'CONTRACTS';
1019 -- This checks the access level for the resource and the contract
1020 CURSOR res_acc_csr IS
1021 SELECT cas.access_level
1022 FROM okc_k_accesses cas
1023 WHERE cas.chr_id = p_chr_id
1024 AND cas.resource_id = g_user_resource_id;
1025 -- This cursor selects all the resource groups and the access level
1026 -- for the contract.
1027 CURSOR grp_acc_csr IS
1028 SELECT cas.group_id,
1029 cas.access_level
1030 FROM okc_k_accesses cas
1031 WHERE cas.chr_id = p_chr_id
1032 AND cas.group_id IS NOT NULL
1033 ORDER BY 2 DESC;
1034 -- This cursor selects all the resource groups that the resource
1035 -- belongs to. Fetched only once per session. The retrieved rows are
1036 -- stored in pl/sql global table and this table is used for
1037 -- subsequent contracts in the same session.
1038 CURSOR res_grp_csr IS
1039 SELECT rgm.group_id
1040 FROM jtf_rs_group_members rgm,
1041 jtf_rs_role_relations rrr,
1042 jtf_rs_roles_b rr,
1043 jtf_rs_groups_b rgb
1044 WHERE rgm.resource_id = g_user_resource_id
1045 AND rgm.group_id = rgb.group_id
1046 AND l_date BETWEEN NVL(rgb.start_date_active, l_date)
1047 AND NVL(rgb.end_date_active, l_date)
1048 AND rgm.group_id = rrr.role_resource_id
1049 AND NVL(rgm.delete_flag,'N') = 'N'
1050 AND rrr.role_resource_type = 'RS_GROUP'
1051 AND NVL(rrr.delete_flag,'N') = 'N'
1052 AND l_date BETWEEN rrr.start_date_active
1053 AND NVL(rrr.end_date_active, l_date)
1054 AND rrr.role_id = rr.role_id
1055 AND rr.role_type_code = 'CONTRACTS'
1056 UNION
1057 SELECT rgd.parent_group_id
1058 FROM jtf_rs_group_members rgm,
1059 jtf_rs_groups_denorm rgd,
1060 jtf_rs_role_relations rrr,
1061 jtf_rs_roles_b rr,
1062 jtf_rs_groups_b rgb
1063 WHERE rgm.resource_id = g_user_resource_id
1064 AND NVL(rgm.delete_flag,'N') = 'N'
1065 AND rgd.group_id = rgm.group_id
1066 AND rgd.parent_group_id = rgb.group_id
1067 AND l_date BETWEEN NVL(rgb.start_date_active, l_date)
1068 AND NVL(rgb.end_date_active, l_date)
1069 AND rgd.parent_group_id = rrr.role_resource_id
1070 AND rrr.role_resource_type = 'RS_GROUP'
1071 AND NVL(rrr.delete_flag,'N') = 'N'
1072 AND l_date BETWEEN rrr.start_date_active
1073 AND NVL(rrr.end_date_active, l_date)
1074 AND rrr.role_id = rr.role_id
1075 AND rr.role_type_code = 'CONTRACTS';
1076 BEGIN
1077 -- Global variable g_user_id introduced to resolve the problem of connection pooling.
1078 -- This variable is not guaranteed to be same for the same user across multiple
1079 -- web requests. So everytime a global needs to be checked, make sure it was built
1080 -- by the same user.
1081 Set_Connection_Context;
1082 -- If no contract identifier is passed, then do not allow access
1083 IF p_chr_id IS NULL THEN
1084 RAISE Exception_No_Access;
1085 END IF;
1086 -- If the sub class is not passed in, then derive it using the
1087 -- contract identifier
1088 l_scs_code := p_scs_code;
1089 IF l_scs_code IS NULL THEN
1090 -- Get the subclass/category from the contracts table
1091 OPEN chr_csr;
1092 FETCH chr_csr INTO l_scs_code;
1093 l_row_notfound := chr_csr%NOTFOUND;
1094 CLOSE chr_csr;
1095 IF l_row_notfound THEN
1096 RAISE Exception_No_Access;
1097 END IF;
1098 END IF;
1099 -- fnd_log.string(1, 'okl', 'l_scs_code : ' || l_scs_code);
1100 -- Determine if the access for the category and responsibility has
1101 -- been determined earlier and cached in the global variables. If not,
1102 -- then determine it using the resp_csr g_resp_access is initialized
1103 -- to g_miss_char. If this could not be determined the first time
1104 -- around, the variables are set to null and not examined during the
1105 -- next round
1106 IF (l_scs_code <> g_scs_code) OR (g_reset_resp_flag) THEN
1107 OPEN resp_csr;
1108 FETCH resp_csr INTO l_resp_access;
1109 l_row_notfound := resp_csr%NOTFOUND;
1110 CLOSE resp_csr;
1111 IF l_row_notfound THEN
1112 l_resp_access := NULL;
1113 END IF;
1114 -- fnd_log.string(1, 'okl', 'l_resp_access : ' || l_resp_access);
1115 -- Save the current access level into global variables. If no access
1116 -- was determined, the local variables hold null and so do the global
1117 -- variables
1118 g_scs_code := l_scs_code;
1119 g_resp_access := l_resp_access;
1120 IF g_reset_resp_flag THEN
1121 g_reset_resp_flag := FALSE;
1122 END IF;
1123 END IF;
1124 -- Check the access level at the category and responsibility level first
1125 IF g_resp_access = l_modify_access THEN
1126 RAISE Exception_Modify_Access;
1127 END IF;
1128 -- If could not find 'Update' access from the user's responsibility,
1129 -- continue to check if granted any access at the user resource level.
1130 -- If the user resource id is not determined earlier, then retrieve it
1131 -- and cache it as it will not change during the current session
1132 IF (g_user_resource_id = Okl_Api.G_MISS_NUM) OR
1133 (g_reset_access_flag) THEN
1134 OPEN res_csr;
1135 FETCH res_csr INTO g_user_resource_id;
1136 l_row_notfound := res_csr%NOTFOUND;
1137 CLOSE res_csr;
1138 g_groups_processed := FALSE;
1139 IF l_row_notfound THEN
1140 g_user_resource_id := NULL;
1141 END IF;
1142 END IF;
1143 -- Determine the access level for the resource id on the contract
1144 IF g_user_resource_id IS NOT NULL THEN
1145 OPEN res_acc_csr;
1146 FETCH res_acc_csr INTO l_resource_access;
1147 CLOSE res_acc_csr;
1148 IF l_resource_access = l_modify_access THEN
1149 RAISE Exception_Modify_Access;
1150 END IF;
1151 -- fnd_log.string(1, 'okl', 'l_resource_access : ' || l_resource_access);
1152 -- Since the resource does not have Update access, we need to get its
1153 -- parent group and its grand parent groups (recursively). Cache it in
1154 -- the global pl/sql table since this hierarchy is not going to change
1155 -- for a resource. So do it only for the first time. Do this by
1156 -- examining the global variable g_groups_processed. This indicates
1157 -- that the array of groups has been retrieved for the session
1158 IF g_groups_processed THEN
1159 NULL;
1160 ELSE
1161 OPEN res_grp_csr;
1162 FETCH res_grp_csr BULK COLLECT INTO g_sec_groups;
1163 CLOSE res_grp_csr;
1164 g_groups_processed := TRUE;
1165 END IF;
1166 -- Finally check for any access granted at the group level.
1167 -- Do it only if the resource belongs to at least one group
1168 -- fnd_log.string(1, 'okl', 'g_sec_groups.count : ' || to_char(g_sec_groups.count));
1169 l_group_has_read_access := FALSE;
1170 IF g_sec_groups.COUNT > 0 THEN
1171 OPEN grp_acc_csr;
1172 LOOP
1173 -- Get all the groups assigned to the contract
1174 FETCH grp_acc_csr INTO l_group_id, l_group_access;
1175 EXIT WHEN grp_acc_csr%NOTFOUND;
1176 FOR i IN 1 .. g_sec_groups.LAST
1177 LOOP
1178 IF g_sec_groups(i) = l_group_id THEN
1179 -- If the groups match and access level is 'U', exit immediately
1180 IF l_group_access = l_modify_access THEN
1181 RAISE Exception_Modify_Access;
1182 END IF;
1183 IF l_group_access = l_read_access THEN
1184 l_group_has_read_access := TRUE;
1185 END IF;
1186 END IF;
1187 END LOOP;
1188 END LOOP;
1189 CLOSE grp_acc_csr;
1190 END IF;
1191 END IF;
1192 IF (l_read_access IN (g_resp_access, l_resource_access)) OR
1193 l_group_has_read_access THEN
1194 RAISE Exception_Read_Access;
1195 END IF;
1196 RAISE Exception_No_Access;
1197 EXCEPTION
1198 WHEN Exception_Modify_Access THEN
1199 IF grp_acc_csr%ISOPEN THEN
1200 CLOSE grp_acc_csr;
1201 END IF;
1202 IF g_reset_access_flag THEN
1203 g_reset_access_flag := FALSE;
1204 END IF;
1205 RETURN(l_modify_access);
1206 WHEN Exception_Read_Access THEN
1207 IF g_reset_access_flag THEN
1208 g_reset_access_flag := FALSE;
1209 END IF;
1210 RETURN(l_read_access);
1211 WHEN Exception_No_Access THEN
1212 IF g_reset_access_flag THEN
1213 g_reset_access_flag := FALSE;
1214 END IF;
1215 RETURN(l_no_access);
1216 END get_k_access_level;
1217
1218
1219 PROCEDURE note_context_info (
1220 p_sql_statement IN VARCHAR2,
1221 -- SPILLAIP - 2689257 - Start
1222 p_object_info IN OUT NOCOPY VARCHAR2,
1223 p_object_id IN NUMBER) IS
1224 BEGIN
1225 EXECUTE IMMEDIATE p_sql_statement INTO p_object_info USING p_object_id;
1226 END note_context_info;
1227
1228 FUNCTION read_clob (
1229 p_clob CLOB)
1230 RETURN VARCHAR2 IS
1231 amount BINARY_INTEGER := 32000;
1232 clob_size INTEGER;
1233 buffer VARCHAR2(32000);
1234 BEGIN
1235 IF p_clob IS NULL THEN
1236 RETURN NULL;
1237 ELSE
1238 clob_size := dbms_lob.getlength(p_clob);
1239 IF clob_size < amount THEN
1240 amount := clob_size;
1241 END IF;
1242 IF clob_size = 0 THEN
1243 RETURN NULL;
1244 END IF;
1245 dbms_lob.READ(p_clob, amount, 1, buffer);
1246 IF amount > 0 THEN
1247 RETURN buffer;
1248 ELSE
1249 RETURN NULL;
1250 END IF;
1251 END IF;
1252 END read_clob;
1253
1254 FUNCTION read_clob (
1255 p_note_id NUMBER)
1256 RETURN VARCHAR2 IS
1257 amount BINARY_INTEGER := 32000;
1258 clob_size INTEGER;
1259 buffer VARCHAR2(32000);
1260
1261 p_clob CLOB;
1262 CURSOR c_clob (p_note_id NUMBER) IS
1263 SELECT notes_detail
1264 FROM jtf_notes_tl
1265 WHERE jtf_note_id = p_note_id
1266 AND LANGUAGE = USERENV('LANG');
1267
1268 BEGIN
1269 OPEN c_clob(p_note_id);
1270 FETCH c_clob INTO p_clob;
1271 CLOSE c_clob;
1272
1273 IF p_clob IS NULL THEN
1274 RETURN NULL;
1275 ELSE
1276 clob_size := dbms_lob.getlength(p_clob);
1277 IF clob_size < amount THEN
1278 amount := clob_size;
1279 END IF;
1280 IF clob_size = 0 THEN
1281 RETURN NULL;
1282 END IF;
1283 dbms_lob.READ(p_clob, amount, 1, buffer);
1284 IF amount > 0 THEN
1285 RETURN buffer;
1286 ELSE
1287 RETURN NULL;
1288 END IF;
1289 END IF;
1290 END read_clob;
1291
1292 FUNCTION party_type_info (
1293 p_object_id NUMBER)
1294 RETURN VARCHAR2 IS
1295 l_party_type_name VARCHAR2(2000);
1296 CURSOR C_party_type_name (p_object_id NUMBER) IS
1297 SELECT A.meaning
1298 FROM ar_lookups A, hz_parties p
1299 WHERE p.party_id = p_object_id
1300 AND A.lookup_code = p.party_type
1301 AND A.lookup_type = 'PARTY_TYPE';
1302
1303 BEGIN
1304 l_party_type_name := 'Party';
1305
1306 IF p_object_id IS NOT NULL THEN
1307 OPEN C_party_type_name (p_object_id);
1308 FETCH C_party_type_name INTO l_party_type_name;
1309 CLOSE C_party_type_name;
1310 END IF;
1311
1312 RETURN l_party_type_name;
1313
1314 END party_type_info;
1315
1316 FUNCTION note_context_info (
1317 p_select_id VARCHAR2,
1318 p_select_name VARCHAR2,
1319 p_select_details VARCHAR2,
1320 p_from_table VARCHAR2,
1321 p_where_clause VARCHAR2,
1322 p_object_id NUMBER)
1323 RETURN VARCHAR2 IS
1324 l_sql_statement VARCHAR2(2000);
1325 l_object_info VARCHAR2(2000);
1326 BEGIN
1327 l_sql_statement := NULL;
1328 l_object_info := NULL;
1329
1330 IF p_from_table IS NOT NULL AND p_select_id IS NOT NULL AND p_object_id IS NOT NULL THEN
1331 IF p_select_name IS NOT NULL THEN
1332 l_sql_statement := 'SELECT ' || p_select_name || ' ';
1333 END IF;
1334 IF p_select_details IS NOT NULL THEN
1335 IF l_sql_statement IS NOT NULL THEN
1336 l_sql_statement := l_sql_statement || ' || '' - '' || ';
1337 ELSE
1338 l_sql_statement := 'SELECT ';
1339 END IF;
1340 l_sql_statement := l_sql_statement || p_select_details || ' ';
1341 END IF;
1342 IF l_sql_statement IS NOT NULL THEN
1343 l_sql_statement := l_sql_statement || 'FROM ' || p_from_table || ' ';
1344 l_sql_statement := l_sql_statement || 'WHERE ' || p_select_id || ' = :p_object_id ';
1345 IF p_where_clause IS NOT NULL THEN
1346 l_sql_statement := l_sql_statement || 'AND ' || p_where_clause;
1347 END IF;
1348 END IF;
1349 END IF;
1350
1351 IF l_sql_statement IS NOT NULL THEN
1352 EXECUTE IMMEDIATE l_sql_statement INTO l_object_info USING p_object_id;
1353 END IF;
1354
1355 RETURN l_object_info;
1356
1357 END note_context_info;
1358
1359
1360 FUNCTION get_contract_status(
1361 p_contract_id IN NUMBER,
1362 p_working_mode IN VARCHAR2 DEFAULT 'QUERY',
1363 p_contract_status OUT NOCOPY VARCHAR2,
1364 x_allowed OUT NOCOPY VARCHAR2 )
1365 RETURN VARCHAR2
1366 IS
1367 CURSOR okl_status_cur(p_contract_id NUMBER) IS
1368 SELECT sts_code FROM okc_k_headers_b
1369 WHERE ID = p_contract_id;
1370
1371 CURSOR okc_status_cur(p_okl_status VARCHAR2) IS
1372 SELECT ste_code FROM okc_statuses_b
1373 WHERE code=p_okl_Status;
1374
1375
1376 l_api_version NUMBER;
1377 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1378 l_msg_count NUMBER;
1379 l_msg_data VARCHAR2(2000);
1380 l_user_status VARCHAR2(100);
1381 l_okc_status VARCHAR2(100);
1382 l_allowed VARCHAR2(10) := 'FALSE';
1383 BEGIN
1384 OPEN okl_status_cur(p_contract_id);
1385 FETCH okl_status_cur INTO l_user_status;
1386 CLOSE okl_status_cur;
1387
1388 OPEN okc_status_cur(l_user_status);
1389 FETCH okc_status_cur INTO l_okc_status;
1390 CLOSE okc_status_cur;
1391
1392 IF p_working_mode = 'QUERY' AND l_user_status IN (--User Status
1393 'BOOKED','EVERGREEN','UNDER REVISION','ABANDONED','COMPLETE','INCOMPLETE',
1394 'NEW','PASSED','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD','APPROVED',
1395 'PENDING_APPROVAL','AMENDED','REVERSED','TERMINATED',
1396 -----OKC Status
1397 'ACTIVE','CANCELLED','HOLD','SIGNED','TERMINATED','EXPIRED') THEN
1398 l_allowed := 'TRUE' ;
1399 /**start 11i9 code */
1400 ELSIF p_working_mode = 'MODIFY_PARTY' AND l_okc_status IN ('ACTIVE','HOLD')
1401 AND l_user_status IN (--User Status
1402 'BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD',
1403 'TERMINATION_HOLD')
1404 THEN
1405 l_allowed := 'TRUE' ;
1406 ELSIF p_working_mode = 'NON_BILLING' AND l_okc_status IN ('ACTIVE','HOLD')
1407 AND l_user_status IN (--User Status
1408 'BOOKED','EVERGREEN','BANKRUPTCY_HOLD','LITIGATION_HOLD'
1409 ,'TERMINATION_HOLD') THEN
1410 l_allowed := 'TRUE' ;
1411 ELSIF p_working_mode = 'RENEW_MIDTERM' AND l_okc_status IN ('ACTIVE','HOLD')
1412 AND l_user_status IN (--User Status
1413 'BOOKED','BANKRUPTCY_HOLD','LITIGATION_HOLD','TERMINATION_HOLD') THEN
1414 l_allowed := 'TRUE' ;
1415 /**end 11i9 code */
1416 ELSIF p_working_mode IN ( 'EXCHANGE','RENEWAL','TRANSFER','MODIFY UBB','TAX SCHEDULES') --User Status
1417 AND l_okc_status IN ('ACTIVE','HOLD')
1418 AND l_user_status IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD'
1419 ,'TERMINATION_HOLD','LITIGATION_HOLD','UNDER REVISION') THEN
1420 l_allowed := 'TRUE' ;
1421 ELSIF p_working_mode = 'CREDIT MEMO' --User Status
1422 AND l_okc_status IN ('ACTIVE','HOLD','ENTERED','SIGNED','TERMINATED','EXPIRED')
1423 AND l_user_status IN ('BOOKED','EVERGREEN','UNDER REVISION','COMPLETE'
1424 ,'INCOMPLETE','NEW','PASSED', 'BANKRUPTCY_HOLD'
1425 ,'TERMINATION_HOLD','LITIGATION_HOLD','APPROVED'
1426 ,'PENDING_PPROVAL','REVERSED','TERMINATED','EXPIRED') THEN
1427 l_allowed := 'TRUE' ;
1428
1429 ELSIF p_working_mode IN ('PAYDOWN','CONVERT INTEREST','MODIFY TC','TERMINATION QUOTE')
1430 AND l_okc_status IN ('ACTIVE','HOLD')
1431 AND l_user_status IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD',
1432 'LITIGATION_HOLD','TERMINATION_HOLD') THEN
1433 l_allowed := 'TRUE' ;
1434 ELSIF p_working_mode IN ('ASSET') --User Status
1435 AND l_okc_status IN ('ACTIVE','HOLD')
1436 AND l_user_status IN ('BOOKED','EVERGREEN','BANKRUPTCY_HOLD','APPROVED'
1437 ,'LITIGATION_HOLD','TERMINATION_HOLD') THEN
1438 l_allowed := 'TRUE' ;
1439
1440 --Added Code for Payment Schedule for 11i10 ER
1441 -- Added by rvaduri
1442 ELSIF p_working_mode IN ( 'REQUEST TERMINATION','PAYMENT_SCHEDULE') --User Status
1443 AND l_okc_status IN ('ACTIVE','HOLD')
1444 AND l_user_status IN ('BOOKED','EVERGREEN','ACTIVE') THEN
1445 l_allowed := 'TRUE' ;
1446 ELSIF p_working_mode = 'RESTRUCTURE' --User Status
1447 AND l_okc_status IN ('ACTIVE','HOLD')
1448 AND l_user_status IN ('BOOKED','ACTIVE','BANKRUPTCY_HOLD','HOLD','LITIGATION_HOLD') THEN
1449 l_allowed := 'TRUE' ;
1450 ELSIF p_working_mode = 'EXPIRATION' --User Status
1451 AND l_okc_status IN ('ACTIVE','HOLD')
1452 AND l_user_status IN ('BOOKED','ACTIVE') THEN
1453 l_allowed := 'TRUE' ;
1454 ELSIF p_working_mode = 'TAX OVERRIDE'
1455 AND l_user_status NOT IN ('ABANDONED','APPROVED','COMPLETE','INCOMPLETE','NEW','PASSED','PENDING_APPROVAL') THEN
1456 l_allowed := 'TRUE' ;
1457 ELSE
1458 l_allowed := 'FALSE' ;
1459 END IF;
1460 p_contract_status := l_user_status;
1461 x_allowed := l_allowed;
1462 RETURN l_return_status;
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
1466 ,p_msg_name => G_UNEXPECTED_ERROR
1467 ,p_token1 => G_SQLCODE_TOKEN
1468 ,p_token1_value => SQLCODE
1469 ,p_token2 => G_SQLERRM_TOKEN
1470 ,p_token2_value => SQLERRM);
1471 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1472 RETURN(l_return_status);
1473 END get_contract_status;
1474
1475
1476 FUNCTION contract_cust_accounts( p_cust_acct_id IN NUMBER,
1477 x_no_contracts OUT NOCOPY NUMBER
1478 )
1479 RETURN VARCHAR2 AS
1480 -- get org_id for contract
1481 CURSOR get_contract_no (p_cust_acct_id IN VARCHAR2) IS
1482 SELECT COUNT(*)
1483 FROM HZ_CUST_ACCOUNTS CA,
1484 HZ_PARTIES P,OKC_K_HEADERS_V CHR,OKC_STATUSES_V STAT
1485 WHERE CHR.scs_code = 'LEASE' AND
1486 CHR.authoring_org_id = mo_global.get_current_org_id() AND
1487 ca.cust_account_id =chr.cust_acct_id AND
1488 ca.party_id = p.party_id AND
1489 CHR.sts_code = stat.code AND
1490 stat.code = 'BOOKED' AND
1491 ca.cust_account_id = p_cust_acct_id;
1492
1493 l_api_version NUMBER;
1494 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1495 l_msg_count NUMBER;
1496 l_msg_data VARCHAR2(2000);
1497 BEGIN
1498 OPEN get_contract_no(p_cust_acct_id);
1499 FETCH get_contract_no INTO x_no_contracts;
1500 CLOSE get_contract_no;
1501 RETURN l_return_status;
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 CLOSE get_contract_no;
1505 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
1506 ,p_msg_name => G_UNEXPECTED_ERROR
1507 ,p_token1 => G_SQLCODE_TOKEN
1508 ,p_token1_value => SQLCODE
1509 ,p_token2 => G_SQLERRM_TOKEN
1510 ,p_token2_value => SQLERRM);
1511 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1512 RETURN(l_return_status);
1513 END contract_cust_accounts;
1514 PROCEDURE update_deal(
1515 p_api_version IN NUMBER,
1516 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_msg_count OUT NOCOPY NUMBER,
1519 x_msg_data OUT NOCOPY VARCHAR2,
1520 p_durv_tbl IN deal_tbl_type,
1521 x_durv_tbl OUT NOCOPY deal_tbl_type
1522 ) AS
1523 i number;
1524 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1525 l_msg_count NUMBER;
1526 l_msg_data VARCHAR2(2000);
1527 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1528 BEGIN
1529
1530 OKC_API.init_msg_list(p_init_msg_list);
1531 IF (p_durv_tbl.COUNT > 0) THEN
1532 i := p_durv_tbl.FIRST;
1533 LOOP
1534
1535 okl_deal_create_pub.update_deal(
1536 p_api_version => p_api_version,
1537 p_init_msg_list => FND_API.G_FALSE,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data,
1541 p_durv_rec => p_durv_tbl(i),
1542 x_durv_rec => x_durv_tbl(i));
1543
1544 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1545 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1546 l_overall_status := x_return_status;
1547 END IF;
1548 END IF;
1549 EXIT WHEN (i = p_durv_tbl.LAST);
1550 i := p_durv_tbl.NEXT(i);
1551 END LOOP;
1552 x_return_status := l_overall_status;
1553 END IF;
1554
1555 END update_deal;
1556
1557 PROCEDURE commit_update AS
1558 BEGIN
1559 commit;
1560 END;
1561 PROCEDURE contract_securitized(
1562 p_contract_id IN NUMBER
1563 ,x_value OUT NOCOPY VARCHAR2
1564 ) IS
1565 cursor investor_assigned IS
1566 select SECURITIZED_CODE
1567 from OKL_K_HEADERS
1568 where id = p_contract_id;
1569 l_securitized VARCHAR2(1);
1570 BEGIN
1571 open investor_assigned;
1572 fetch investor_assigned into l_securitized;
1573 close investor_assigned;
1574 x_value := l_securitized;
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME
1578 ,p_msg_name => G_UNEXPECTED_ERROR
1579 ,p_token1 => G_SQLCODE_TOKEN
1580 ,p_token1_value => SQLCODE
1581 ,p_token2 => G_SQLERRM_TOKEN
1582 ,p_token2_value => SQLERRM);
1583 END;
1584
1585 ---------------------------------------------------------------------------------------
1586 --Start of comments
1587 --
1588 --Procedure Name : get_total_tax_amount
1589 --Purpose : Returns the total tax amount for the asset tax lines created due to asset location change
1590 --Modification History :
1591 --19-May-2005 Rkuttiya Created
1592 --Notes :
1593 --End of Comments
1594 -------------------------------------------------------------------------------------------
1595 FUNCTION Get_Total_Tax_Amount(p_trx_id IN NUMBER)
1596 RETURN NUMBER
1597 IS
1598 CURSOR c_tax_amount(p_trx_id IN NUMBER) IS
1599 SELECT SUM(tax_amt)
1600 FROM OKL_TAX_TRX_DETAILS
1601 WHERE txs_id = p_trx_id;
1602 l_tax_amount NUMBER;
1603 BEGIN
1604 OPEN c_tax_amount(p_trx_id);
1605 FETCH c_tax_amount INTO l_tax_amount;
1606 CLOSE c_tax_amount;
1607
1608 RETURN l_tax_amount;
1609 EXCEPTION
1610
1611 WHEN OTHERS THEN
1612 -- store SQL error message on message stack for caller
1613 OKC_API.set_message(
1614 G_APP_NAME,
1615 G_UNEXPECTED_ERROR,
1616 G_SQLCODE_TOKEN,
1617 SQLCODE,
1618 G_SQLERRM_TOKEN,
1619 SQLERRM);
1620
1621 END;
1622
1623 ---------------------------------------------------------------------------------------
1624 --Start of comments
1625 --
1626 --Procedure Name : get_total_stream_amount
1627 --Purpose : Returns the total strm amount at contract or line level
1628 --Modification History :
1629 --15-SEP-2005 Rkuttiya Created
1630 --Notes :
1631 --End of Comments
1632 -------------------------------------------------------------------------------------------
1633 FUNCTION Get_Total_Stream_Amount(p_khr_id IN NUMBER,
1634 p_kle_id IN NUMBER,
1635 p_sty_id IN NUMBER)
1636 RETURN NUMBER
1637 IS
1638 CURSOR c_strm_amount(p_khr_id IN NUMBER,
1639 p_kle_id IN NUMBER,
1640 p_sty_id IN NUMBER) IS
1641 SELECT sum(amount) from okl_cs_payment_detail_uv
1642 WHERE khr_id = p_khr_id
1643 AND sty_id = p_sty_id
1644 AND nvl(kle_id,-1) = nvl(p_kle_id,-1) ;
1645 l_strm_amount NUMBER;
1646 BEGIN
1647 OPEN c_strm_amount(p_khr_id,p_kle_id,p_sty_id);
1648 FETCH c_strm_amount INTO l_strm_amount;
1649 CLOSE c_strm_amount;
1650
1651 RETURN l_strm_amount;
1652 EXCEPTION
1653
1654 WHEN OTHERS THEN
1655 -- store SQL error message on message stack for caller
1656 OKC_API.set_message(
1657 G_APP_NAME,
1658 G_UNEXPECTED_ERROR,
1659 G_SQLCODE_TOKEN,
1660 SQLCODE,
1661 G_SQLERRM_TOKEN,
1662 SQLERRM);
1663
1664 END;
1665
1666 --dkagrawa added the function for bug # 4723838
1667 FUNCTION get_asset_number(p_kle_id IN NUMBER)
1668 RETURN VARCHAR2 IS
1669 CURSOR l_asset_number_csr(cp_kle_id IN NUMBER) IS
1670 SELECT fat.name
1671 FROM okc_k_lines_b fa,
1672 okc_k_lines_tl fat,
1673 okc_line_styles_b stl,
1674 okc_k_lines_b top_cle,
1675 okc_line_styles_b top_stl,
1676 okc_k_lines_b sub_cle,
1677 okc_line_styles_b sub_stl,
1678 okc_k_items cim
1679 WHERE top_cle.lse_id = top_stl.id
1680 AND top_stl.lty_code in ('SOLD_SERVICE','FEE')
1681 AND top_cle.id = sub_cle.cle_id
1682 AND sub_cle.lse_id = sub_stl.id
1683 AND sub_stl.lty_code in ('LINK_SERV_ASSET','LINK FEE ASSET')
1684 AND cim.cle_id = sub_cle.id
1685 AND CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST'
1686 AND CIM.OBJECT1_ID1 = FA.ID
1687 AND FA.LSE_ID = STL.ID
1688 AND fa.id = fat.id
1689 AND fat.language = USERENV('LANG')
1690 AND STL.LTY_CODE = 'FREE_FORM1'
1691 AND sub_cle.id = cp_kle_id;
1692 l_asset_number okc_k_lines_tl.name%TYPE;
1693 BEGIN
1694 OPEN l_asset_number_csr(p_kle_id);
1695 FETCH l_asset_number_csr INTO l_asset_number;
1696 CLOSE l_asset_number_csr;
1697 RETURN l_asset_number;
1698 END get_asset_number;
1699 --Bug 4723838 ends
1700
1701 ---------------------------------------------------------------------------------------
1702 --Start of comments
1703 --
1704 --function Name : get_ap_line_tax
1705 --Purpose : Returns the total tax amount for an ap invoice at line level
1706 --Modification History :
1707 --12-FEB--2007 dkagrawa Created
1708 --Notes :
1709 --End of Comments
1710 -------------------------------------------------------------------------------------------
1711 FUNCTION get_ap_line_tax(p_invoice_id IN NUMBER, p_line_number IN NUMBER)
1712 RETURN NUMBER IS
1713 CURSOR c_tax_amount IS
1714 SELECT SUM(tax_line.tax_amt) TAX_AMOUNT
1715 FROM ap_invoice_lines_all inv_ln
1716 , zx_lines tax_line
1717 , fnd_application app_ap
1718 WHERE tax_line.application_id = app_ap.application_id
1719 AND app_ap.application_short_name = 'SQLAP'
1720 AND tax_line.entity_code = 'AP_INVOICES'
1721 AND tax_line.event_class_code = 'STANDARD INVOICES'
1722 AND tax_line.trx_id = inv_ln.invoice_id
1723 AND tax_line.trx_level_type = 'LINE'
1724 AND tax_line.trx_line_number = inv_ln.line_number
1725 AND inv_ln.invoice_id = p_invoice_id
1726 AND inv_ln.line_number = p_line_number;
1727 l_tax_amount NUMBER;
1728 BEGIN
1729 OPEN c_tax_amount;
1730 FETCH c_tax_amount INTO l_tax_amount;
1731 CLOSE c_tax_amount;
1732 RETURN l_tax_amount;
1733 END get_ap_line_tax;
1734
1735 --asawanka added for ebtax project
1736 ---------------------------------------------------------------------------
1737 -- FUNCTION get_private_label
1738 ---------------------------------------------------------------------------
1739 FUNCTION get_tax_sch_Req_flag(
1740 p_contract_id IN NUMBER)
1741 RETURN VARCHAR2
1742 IS
1743 l_tax_Sch_req VARCHAR2(30) := 'N';
1744 CURSOR get_taxsch_rule_csr IS
1745 select rule_information5
1746 from okc_rules_b
1747 where dnz_chr_id = p_contract_id
1748 and rule_information_category = 'LASTPR';
1749 BEGIN
1750 OPEN get_taxsch_rule_csr;
1751 FETCH get_taxsch_rule_csr INTO l_tax_Sch_req;
1752 CLOSE get_taxsch_rule_csr;
1753 RETURN l_tax_Sch_req;
1754
1755 END get_tax_sch_Req_flag;
1756
1757
1758 ---------------------------------------------------------------------------------------
1759 --Start of comments
1760 --
1761 --Procedure Name : get_cov_asset_id
1762 --Purpose : Returns the asset_id of the asset associated to a service line,
1763 -- created to fix bug#5759229
1764 --Modification History :
1765 --27-AUG-2007 Zrehman Created
1766 --Notes :
1767 --End of Comments
1768 -------------------------------------------------------------------------------------------
1769
1770 FUNCTION get_cov_asset_id(p_kle_id IN NUMBER)
1771 RETURN NUMBER IS
1772 CURSOR l_asset_id_csr(cp_kle_id IN NUMBER) IS
1773 SELECT cim1.object1_id1
1774 FROM okc_k_lines_b cle,
1775 okc_k_lines_b cle1, /* to get all line types associated with free form 1 in that FIXED_ASSET */
1776 okc_k_items cim, /* to get the one having covered asset */
1777 okc_k_lines_b cle2, /* After getting the one having the covered asset take the id of the service line */
1778 okc_line_styles_b lse, /* to get free_form 1 */
1779 okc_line_styles_b lse1, /* to get FIXED_ASSET lty_code */
1780 okc_line_styles_b lse2, /* to get LINK_SERV_ASSET lty_code */
1781 okc_k_items cim1 /* to take the actual asset id */
1782 WHERE lse.id = cle.lse_id
1783 AND lse1.id = cle1.lse_id
1784 AND lse1.lty_code = 'FIXED_ASSET'
1785 AND cle.id = cle1.cle_id
1786 AND lse.lty_code = 'FREE_FORM1'
1787 AND cle1.cle_id = cim.object1_id1
1788 AND cim.jtot_object1_code = 'OKX_COVASST'
1789 AND cim.cle_id = cle2.id
1790 AND lse2.id = cle2.lse_id
1791 AND cle1.id = cim1.cle_id
1792 AND cim1.jtot_object1_code = 'OKX_ASSET'
1793 AND cle2.id = cp_kle_id;
1794 /*
1795 For clarity in the query result, select the following in the above SELECT statement to see
1796 the asset_id of the asset associated to a service line, the line id of the associated asset line and
1797 the service id to which the asset is associated.
1798 Also the corresponding lty_code is selected to verify the result set of the query.
1799
1800 SELECT cim1.object1_id1 "Asset ID",
1801 cle1.id "FIXED ASSET LINE ID", lse1.lty_code "lty code of fixed asset line",
1802 cle2.id "SERVICE LINE ID", lse2.lty_code "lty code of service line"
1803
1804 Note: The input for the above query would be the id of the service line.
1805 */
1806
1807 l_asset_id okc_k_items.object1_id1%TYPE;
1808
1809 BEGIN
1810
1811 OPEN l_asset_id_csr(p_kle_id);
1812 FETCH l_asset_id_csr INTO l_asset_id;
1813 CLOSE l_asset_id_csr;
1814
1815 RETURN l_asset_id;
1816
1817 END get_cov_asset_id;
1818
1819 ---------------------------------------------------------------------------------------
1820 --Start of comments
1821 --
1822 --Procedure Name : get_payment_remaining
1823 --Purpose : Returns the No of payment remaining for given contract
1824 -- created to enhance usability in sprint 7
1825 --Modification History :
1826 --28-JAN-2008 dkagrawa Created
1827 --Notes :
1828 --End of Comments
1829 -------------------------------------------------------------------------------------------
1830 FUNCTION get_payment_remaining(p_khr_id IN NUMBER) RETURN VARCHAR2
1831 IS
1832 CURSOR c_get_unbilled_payment(p_contract_id IN NUMBER,p_cle_id IN NUMBER)
1833 IS
1834 SELECT COUNT(1) payment_remaining
1835 FROM OKL_STRM_TYPE_v STYT,
1836 okl_strm_elements sele,
1837 okl_streams str
1838 WHERE sele.stm_id = str.id
1839 AND str.sty_id = styt.id
1840 AND str.say_code = 'CURR'
1841 AND STR.ACTIVE_YN = 'Y'
1842 AND STR.PURPOSE_CODE IS NULL
1843 AND SELE.DATE_BILLED IS NULL
1844 AND styt.billable_yn = 'Y'
1845 AND styt.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT','LOAN_PAYMENT')
1846 AND str.khr_id = p_contract_id
1847 AND str.kle_id = p_cle_id;
1848
1849 CURSOR c_get_payment_details(p_contract_id IN NUMBER) IS
1850 SELECT rgp_lalevl.cle_id,
1851 RUL_LASLL.RULE_INFORMATION5 STRUCTURE_CODE,
1852 RUL_LASLL.OBJECT1_ID1 FREQUENCY_CODE,
1853 NVL(RUL_LASLL.RULE_INFORMATION10,'N') ARREARS_YN,
1854 FND_DATE.canonical_to_date(rul_lasll.rule_information2) start_date,
1855 rul_lasll.rule_information7 stub_days,
1856 rul_lasll.rule_information3 periods
1857 FROM OKC_RULE_GROUPS_B RGP_LALEVL,
1858 OKC_RULES_B RUL_LASLH,
1859 OKC_RULES_B RUL_LASLL,
1860 OKL_STRM_TYPE_v STYT
1861 WHERE RGP_LALEVL.RGD_CODE = 'LALEVL'
1862 AND RUL_LASLH.RGP_ID = RGP_LALEVL.ID
1863 AND RUL_LASLH.RULE_INFORMATION_CATEGORY = 'LASLH'
1864 AND RUL_LASLH.DNZ_CHR_ID = RGP_LALEVL.DNZ_CHR_ID
1865 AND STYT.ID = RUL_LASLH.OBJECT1_ID1
1866 AND RUL_LASLL.RULE_INFORMATION_CATEGORY = 'LASLL'
1867 AND RUL_LASLL.RGP_ID = RUL_LASLH.RGP_ID
1868 AND RUL_LASLL.DNZ_CHR_ID = RUL_LASLH.DNZ_CHR_ID
1869 AND RUL_LASLL.OBJECT2_ID1 = RUL_LASLH.ID
1870 AND NVL(RUL_LASLL.OBJECT2_ID2,'#') = '#'
1871 AND RUL_LASLL.JTOT_OBJECT2_CODE = 'OKL_STRMHDR'
1872 AND styt.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT','LOAN_PAYMENT')
1873 AND RGP_LALEVL.DNZ_CHR_ID = p_contract_id;
1874
1875 l_diff BOOLEAN := FALSE;
1876 l_in NUMBER :=1;
1877 l_unbillled_remaining NUMBER;
1878 l_structure_code VARCHAR2(30);
1879 l_frequency_code VARCHAR2(30);
1880 l_arrears VARCHAR2(30);
1881 l_start_date DATE;
1882 l_stub_days NUMBER;
1883 l_periods NUMBER;
1884 l_cle_id NUMBER;
1885 BEGIN
1886
1887 FOR rec IN c_get_payment_details(p_khr_id) LOOP
1888 l_cle_id := rec.cle_id;
1889 IF l_in = 1 THEN
1890 l_structure_code := rec.STRUCTURE_CODE;
1891 l_frequency_code := rec.frequency_code;
1892 l_arrears := rec.arrears_yn;
1893 l_start_date := rec.START_DATE;
1894 l_stub_days := rec.stub_days;
1895 l_periods := rec.periods;
1896 ELSE
1897 IF l_structure_code <> rec.STRUCTURE_CODE OR
1898 l_frequency_code <> rec.frequency_code OR
1899 l_arrears <> rec.arrears_yn OR
1900 l_start_date <> rec.START_DATE OR
1901 l_stub_days <> rec.stub_days OR
1902 l_periods <> rec.periods THEN
1903 l_diff := TRUE;
1904 EXIT;
1905 END IF;
1906 END IF;
1907 END LOOP;
1908 IF l_diff THEN
1909 RETURN 'Multiple';
1910 ELSE
1911 OPEN c_get_unbilled_payment(p_khr_id,l_cle_id);
1912 FETCH c_get_unbilled_payment INTO l_unbillled_remaining;
1913 CLOSE c_get_unbilled_payment;
1914
1915 RETURN to_char(l_unbillled_remaining);
1916 END IF;
1917 END;
1918
1919 ---------------------------------------------------------------------------------------
1920 --Start of comments
1921 --
1922 --Procedure Name : get_term_remaining
1923 --Purpose : Returns the No terms remaining for given contract
1924 -- created to enhance usability in sprint 7
1925 --Modification History :
1926 --28-JAN-2008 dkagrawa Created
1927 --Notes :
1928 --End of Comments
1929 -------------------------------------------------------------------------------------------
1930
1931 FUNCTION get_term_remaining(p_khr_id IN NUMBER) RETURN NUMBER
1932 IS
1933 CURSOR c_get_terms_rem (p_contract_id IN NUMBER)
1934 IS
1935 SELECT okhr.term_duration-DECODE(sign(sysdate-khr.start_date),-1,0,DECODE(sign(sysdate-khr.end_date),1,okhr.term_duration,TRUNC(MONTHS_BETWEEN(sysdate,khr.start_date))))
1936 payment_remaining
1937 FROM okl_k_headers okhr ,
1938 okc_k_headers_v khr
1939 WHERE okhr.id = khr.id
1940 AND khr.id = p_contract_id;
1941
1942 l_terms_remaining NUMBER;
1943 BEGIN
1944 OPEN c_get_terms_rem(p_khr_id);
1945 FETCH c_get_terms_rem INTO l_terms_remaining;
1946 CLOSE c_get_terms_rem;
1947 RETURN l_terms_remaining;
1948 END;
1949
1950 FUNCTION get_total_billed(p_khr_id IN NUMBER) RETURN NUMBER IS
1951 CURSOR c_get_billed(cp_khr_id IN NUMBER) IS
1952 SELECT SUM(amount_original+amount_adjusted)
1953 FROM okl_cs_bpd_inv_dtl_v
1954 WHERE amount_remaining > 0
1955 AND chr_id = cp_khr_id;
1956
1957 /* CURSOR c_get_total_adjusted(cp_khr_id IN NUMBER) IS
1958 SELECT SUM(NVL(APS.AMOUNT_ADJUSTED,0)) AMOUNT_ADJUSTED
1959 FROM AR_PAYMENT_SCHEDULES_ALL APS,
1960 RA_CUSTOMER_TRX_ALL RACTRX,
1961 RA_CUSTOMER_TRX_LINES_ALL RACTRX_LINE,
1962 OKC_K_HEADERS_B OKC
1963 WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
1964 AND RACTRX.CUSTOMER_TRX_ID = RACTRX_LINE.CUSTOMER_TRX_ID
1965 AND OKC.CONTRACT_NUMBER = RACTRX_LINE.INTERFACE_LINE_ATTRIBUTE6
1966 AND OKC.ID = cp_khr_id;
1967 */
1968 -- l_total_adjusted NUMBER;
1969 l_billed NUMBER;
1970 BEGIN
1971
1972 OPEN c_get_billed(p_khr_id);
1973 FETCH c_get_billed INTO l_billed;
1974 CLOSE c_get_billed;
1975
1976 /* OPEN c_get_total_adjusted(p_khr_id);
1977 FETCH c_get_total_adjusted INTO l_total_adjusted;
1978 CLOSE c_get_total_adjusted;
1979 */
1980 -- RETURN NVL(l_billed,0) + NVL(l_total_adjusted,0);
1981 RETURN NVL(l_billed,0);
1982 END get_total_billed;
1983
1984 FUNCTION get_total_paid_credited(p_khr_id IN NUMBER) RETURN NUMBER IS
1985 CURSOR c_get_paid_credited(cp_khr_id IN NUMBER) IS
1986 SELECT SUM(amount_applied) + SUM(amount_credited)
1987 FROM okl_cs_bpd_inv_dtl_v
1988 WHERE -- amount_remaining > 0 -- fox for bug 14524398 : vsgandhi
1989 --AND
1990 chr_id = cp_khr_id;
1991 l_paid_credited NUMBER;
1992 BEGIN
1993
1994 OPEN c_get_paid_credited(p_khr_id);
1995 FETCH c_get_paid_credited INTO l_paid_credited;
1996 CLOSE c_get_paid_credited;
1997
1998 RETURN NVL(l_paid_credited,0);
1999 END get_total_paid_credited;
2000
2001 FUNCTION get_total_remaining(p_khr_id IN NUMBER) RETURN NUMBER IS
2002 CURSOR c_get_remaining(cp_khr_id IN NUMBER) IS
2003 SELECT SUM(amount_remaining)
2004 FROM okl_cs_bpd_inv_dtl_v
2005 WHERE amount_remaining > 0
2006 AND chr_id = cp_khr_id;
2007 l_remaining NUMBER;
2008 BEGIN
2009
2010 OPEN c_get_remaining(p_khr_id);
2011 FETCH c_get_remaining INTO l_remaining;
2012 CLOSE c_get_remaining;
2013
2014 RETURN NVL(l_remaining,0);
2015 END get_total_remaining;
2016 END Okl_Cs_Lc_Contract_Pvt;
[Home] [Help]