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