[Home] [Help]
PACKAGE BODY: APPS.OKL_ACCRUAL_SEC_PVT
Source
1 PACKAGE BODY OKL_ACCRUAL_SEC_PVT AS
2 /* $Header: OKLRASCB.pls 120.16.12010000.2 2008/10/20 18:36:21 apaul ship $ */
3 ------------------------------------------------------------------------------------
4 -- GLOBAL OKL MESSAGES
5 ------------------------------------------------------------------------------------
6 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
7 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_UNEXPECTED_ERROR';
8 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
9 G_INVALID_VALUE CONSTANT VARCHAR2(200) := 'OKL_INVALID_VALUE';
10 G_NO_MATCHING_RECORD CONSTANT VARCHAR2(200) := 'OKL_LLA_NO_MATCHING_RECORD';
11 G_DB_ERROR CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
12 G_PROG_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROG_NAME';
13
14 --sosharma 14-12-2007 ,Added pending status
15 G_PC_STS_PENDING CONSTANT VARCHAR2(10) := 'PENDING';
16
17 ------------------------------------------------------------------------------------
18 -- Start of Commnets
19 -- Badrinath Kuchibhotla
20 -- Procedure Name : create_lease_streams
21 -- Description : Generates Investor Agreement streams when rebook Lease Contract
22 -- Business Rules : This a overloaded procedure for create_streams to generate
23 -- investor agreement streams when a lease contract is rebooked.
24 -- Parameters : p_khr_id, p_scs_code
25 -- Version : 1.0
26 -- History : BAKUCHIB 12-FEB-2004 - 3426071 created
27 -- End of Commnets
28 --------------------------------------------------------------------------------
29 PROCEDURE create_lease_streams(p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34 p_khr_id IN NUMBER,
35 p_mode IN VARCHAR2 DEFAULT NULL)
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LEASE_STREAMS';
38 /*
39 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
40 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
41 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
42 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
43 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENTAL ACCRUAL';
44 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'PRE-TAX INCOME';
45 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST INCOME';
46 l_variable_income CONSTANT VARCHAR2(2000) := 'VARIABLE INCOME ACCRUAL';
47 */
48 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
49 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
50 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
51 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
52 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENT_ACCRUAL';
53 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'LEASE_INCOME';
54 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST_INCOME';
55 l_variable_income CONSTANT VARCHAR2(2000) := 'ACCOUNTING';
56 /* ankushar , 16-01-2008 Bug 6740000
57 Added new Stream Type purpose for a Loan product
58 */
59 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
60
61 l_count NUMBER := 1;
62 l_period_end_date DATE;
63 l_total_records NUMBER;
64 l_sysdate DATE := TRUNC(SYSDATE);
65 l_trx_number NUMBER;
66 l_revenue_share NUMBER := 0;
67 l_sty_id NUMBER;
68 l_inv_id NUMBER;
69 l_return_status VARCHAR2(1);
70 stream_type_purpose VARCHAR2(30);
71
72 /* ankushar , 16-01-2008 Bug 6740000
73 Modified cursors to fetch based on stream type for a Loan product
74 Start Changes
75 */
76 -- cursor to select Lease contract id for a given Investor agreement
77 CURSOR securitized_contracts_csr (p_inv_id NUMBER,
78 p_khr_id NUMBER)
79 IS
80 SELECT DISTINCT opc.khr_id khr_id,
81 opc.streams_to_date end_date,
82 khr.deal_type deal_type
83 FROM okl_pool_contents opc,
84 okl_pools op,
85 okl_k_headers khr,
86 -- Changed for User Defined Streams
87 --okl_strm_type_tl stytl
88 okl_strm_type_b stytl
89 WHERE op.khr_id = p_inv_id
90 AND opc.khr_id = p_khr_id
91 AND op.id = opc.pol_id
92 AND opc.khr_id = khr.id
93 AND opc.sty_id = stytl.id
94 --AND stytl.name = 'RENT'
95 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
96 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW);
97 --AND stytl.language = USERENV('LANG');
98
99 -- cursor to get first kle_id and earliest stream element date
100 CURSOR get_kle_id_csr(p_khr_id NUMBER)
101 IS
102 SELECT opc.kle_id kle_id,
103 MIN(opc.streams_from_date) start_date
104 FROM okl_pool_contents opc,
105 okl_strm_type_b sty
106 WHERE opc.khr_id = p_khr_id
107 AND opc.sty_id = sty.id
108 --AND sty.code = 'RENT'
109 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
110 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
111 AND ROWNUM < 2
112 GROUP BY opc.kle_id;
113
114 /* sosharma ,14-12-2007
115 Bug 6691554
116 Added cursors for generating streams for transient pool
117 Start Changes*/
118
119
120 -- cursor to select Lease contract id for a given Investor agreement
121 CURSOR securitized_contracts_pend_csr (p_inv_id NUMBER,
122 p_khr_id NUMBER)
123 IS
124 SELECT DISTINCT opc.khr_id khr_id,
125 opc.streams_to_date end_date,
126 khr.deal_type deal_type
127 FROM okl_pool_contents opc,
128 okl_pools op,
129 okl_k_headers khr,
130 -- Changed for User Defined Streams
131 --okl_strm_type_tl stytl
132 okl_strm_type_b stytl
133 WHERE op.khr_id = p_inv_id
134 AND opc.khr_id = p_khr_id
135 AND op.id = opc.pol_id
136 AND opc.khr_id = khr.id
137 AND opc.sty_id = stytl.id
138 --AND stytl.name = 'RENT'
139 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
140 AND opc.status_code = G_PC_STS_PENDING; --Okl_Pool_Pvt.G_POC_STS_PENDING;
141 --AND stytl.language = USERENV('LANG');
142
143 -- cursor to get first kle_id and earliest stream element date
144
145 CURSOR get_kle_id_pend_csr(p_khr_id NUMBER)
146 IS
147 SELECT opc.kle_id kle_id,
148 MIN(opc.streams_from_date) start_date
149 FROM okl_pool_contents opc,
150 okl_strm_type_b sty
151 WHERE opc.khr_id = p_khr_id
152 AND opc.sty_id = sty.id
153 --AND sty.code = 'RENT'
154 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
155 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
156 AND ROWNUM < 2
157 GROUP BY opc.kle_id;
158
159 CURSOR get_inv_pend_csr (p_khr_id NUMBER)
160 IS
161 SELECT DISTINCT op.khr_id
162 FROM okl_pool_contents opc,
163 okl_pools op,
164 okl_strm_type_b sty
165 WHERE op.id = opc.pol_id
166 AND opc.khr_id = p_khr_id
167 AND opc.sty_id = sty.id
168 --AND sty.code = 'RENT';
169 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
170 AND opc.status_code = G_PC_STS_PENDING;--Okl_Pool_Pvt.G_POC_STS_PENDING ;
171
172 /* sosharma end changes*/
173
174 -- cursor to get advance/arrears and frequency for rent stream type
175 CURSOR get_adv_arr_csr(p_khr_id NUMBER,
176 p_kle_id NUMBER)
177 IS
178 SELECT DECODE(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
179 DECODE(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
180 FROM okc_k_headers_b k,
181 okc_rule_groups_b rg,
182 okc_rules_b slh,
183 okc_rules_b sll,
184 okl_strm_type_b strm
185 WHERE slh.rule_information_category = 'LASLH'
186 AND slh.rgp_id = rg.id
187 AND sll.object2_id1 = to_char(slh.id)
188 AND sll.rgp_id = rg.id
189 AND slh.object1_id1 = to_char(strm.id)
190 --AND strm.code = 'RENT'
191 AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
192 AND rg.dnz_chr_id = k.id
193 AND rg.chr_id IS NULL
194 AND rg.rgd_code = 'LALEVL'
195 AND rg.cle_id = p_kle_id
196 AND k.id = p_khr_id;
197 -- cursor to get stream type id
198 CURSOR get_sty_id_csr (p_sty_name VARCHAR2)
199 IS
200 SELECT id
201 FROM okl_strm_type_tl
202 WHERE name = p_sty_name
203 AND language = USERENV('LANG');
204
205 /* ankushar , 25-01-2008 Bug 6773285
206 Modified Cursor to add parameter for Stream type subclass
207 Start Changes
208 */
209 -- the revenue shares for the investor
210 CURSOR get_revenue_share_csr(p_inv_id NUMBER, p_stream_type_subclass VARCHAR2)
211 IS
212 SELECT kleb_rv.percent_stake percent_stake
213 FROM okl_k_lines kleb_rv,
214 okc_k_lines_b cles_rv,
215 okc_line_styles_b lseb_rv
216 WHERE
217 cles_rv.dnz_chr_id = p_inv_id
218 AND cles_rv.lse_id = lseb_rv.id
219 AND lseb_rv.lty_code = 'REVENUE_SHARE'
220 AND kleb_rv.id = cles_rv.id
221 AND kleb_rv.stream_type_subclass = p_stream_type_subclass;
222 /* ankushar , 25-01-2008 Bug 6773285
223 End Changes
224 */
225 -- cursor to get contract number
226 CURSOR contract_number_csr (p_khr_id NUMBER)
227 IS
228 SELECT contract_number
229 FROM okc_k_headers_b
230 WHERE id = p_khr_id
231 AND scs_code = 'LEASE';
232 -- cursor to get investor Agreement contract id
233 -- for a given Lease contract id
234 CURSOR get_inv_csr (p_khr_id NUMBER)
235 IS
236 SELECT DISTINCT op.khr_id
237 FROM okl_pool_contents opc,
238 okl_pools op,
239 okl_strm_type_b sty
240 WHERE op.id = opc.pol_id
241 AND opc.khr_id = p_khr_id
242 AND opc.sty_id = sty.id
243 --AND sty.code = 'RENT';
244 AND sty.stream_type_purpose = 'RENT'
245 AND opc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE ; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
246
247
248 l_contracts_csr securitized_contracts_csr%ROWTYPE;
249 CURSOR stream_id_csr (p_khr_id NUMBER,
250 p_sty_id NUMBER,
251 p_say_code VARCHAR2)
252 IS
253 select id from okl_streams
254 where khr_id=p_khr_id
255 and sty_id=p_sty_id
256 and say_code=p_say_code ;
257
258 l_id_tbl okl_streams_util.NumberTabTyp;
259 j NUMBER := 0;
260
261 -- declaration of a parameterized cursor by zrehman on 12-Sep-2006
262 CURSOR strm_csr (khr_id NUMBER,
263 final_start_date DATE,
264 end_date DATE,
265 l_stream_type_purpose VARCHAR2)
266 IS
267 select ste.stream_element_date stream_element_date,ste.amount amount
268 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
269 WHERE
270 stm.sty_id = sty.id
271 AND ste.stm_id = stm.id
272 AND stm.active_yn = 'Y'
273 AND stm.say_code = 'CURR'
274 AND stm.khr_id = khr_id
275 AND ste.stream_element_date BETWEEN final_start_date AND end_date
276 AND sty.stream_type_purpose = l_stream_type_purpose
277 ORDER BY ste.stream_element_date;
278
279
280 BEGIN
281 x_return_status := OKL_API.G_RET_STS_SUCCESS;
282 -- Call start_activity to create savepoint, check compatibility
283 -- and initialize message list
284 x_return_status := OKL_API.START_ACTIVITY (
285 l_api_name
286 ,p_init_msg_list
287 ,'_PVT'
288 ,x_return_status);
289 -- Check if activity started successfully
290 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
291 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
292 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
293 RAISE OKL_API.G_EXCEPTION_ERROR;
294 END IF;
295 -- validate in parameters
296 IF p_khr_id IS NULL OR
297 p_khr_id = OKL_API.G_MISS_NUM THEN
298 -- store SQL error message on message stack for caller
299 okl_api.set_message(p_app_name => G_APP_NAME,
300 p_msg_name => G_REQUIRED_VALUE,
301 p_token1 => G_COL_NAME_TOKEN,
302 p_token1_value => 'p_khr_id');
303 RAISE okl_api.g_exception_error;
304 END IF;
305 -- cursor to get investor Agreement contract id
306 -- for a given Lease contract id
307 /* sosharma ,14-12-2007
308 Bug 6691554
309 Modified to pick different cursor for pending contents in case p_mode is not null
310 Start Changes*/
311
312 IF p_mode IS NULL THEN
313 OPEN get_inv_csr (p_khr_id => p_khr_id);
314 FETCH get_inv_csr INTO l_inv_id;
315 IF get_inv_csr%NOTFOUND THEN
316 -- store SQL error message on message stack for caller
317 okl_api.set_message(p_app_name => G_APP_NAME,
318 p_msg_name => G_NO_MATCHING_RECORD,
319 p_token1 => G_COL_NAME_TOKEN,
320 p_token1_value => 'p_khr_id');
321 RAISE okl_api.g_exception_error;
322 END IF;
323 CLOSE get_inv_csr;
324 ELSE
325 OPEN get_inv_pend_csr (p_khr_id => p_khr_id);
326 FETCH get_inv_pend_csr INTO l_inv_id;
327 IF get_inv_pend_csr%NOTFOUND THEN
328 -- store SQL error message on message stack for caller
329 okl_api.set_message(p_app_name => G_APP_NAME,
330 p_msg_name => G_NO_MATCHING_RECORD,
331 p_token1 => G_COL_NAME_TOKEN,
332 p_token1_value => 'p_khr_id');
333 RAISE okl_api.g_exception_error;
334 END IF;
335 CLOSE get_inv_pend_csr;
336 END IF;
337
338 /* sosharma end changes*/
339
340 /* sosharma ,14-12-2007
341 Bug 6691554
342 Modified to pick different cursor for pending contents in case p_mode is not null
343 Start Changes*/
344 IF p_mode IS NULL THEN
345 OPEN securitized_contracts_csr (l_inv_id,
346 p_khr_id);
347 FETCH securitized_contracts_csr INTO l_contracts_csr;
348 IF securitized_contracts_csr%NOTFOUND THEN
349 okl_api.set_message(p_app_name => g_app_name,
350 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
351 RAISE okl_api.G_EXCEPTION_ERROR;
352 END IF;
353 CLOSE securitized_contracts_csr;
354 ELSE
355 OPEN securitized_contracts_pend_csr (l_inv_id,
356 p_khr_id);
357 FETCH securitized_contracts_pend_csr INTO l_contracts_csr;
358 IF securitized_contracts_pend_csr%NOTFOUND THEN
359 okl_api.set_message(p_app_name => g_app_name,
360 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
361 RAISE okl_api.G_EXCEPTION_ERROR;
362 END IF;
363 CLOSE securitized_contracts_pend_csr;
364 END IF;
365 /* sosharma end changes */
366
367 DECLARE
368 TYPE ref_cursor IS REF CURSOR;
369 TYPE element_type IS RECORD (stream_element_date DATE,
370 amount NUMBER);
371 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
372 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
373 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
374 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
375 l_difference NUMBER := 0;
376 l_counter NUMBER := 1;
377 l_line_number NUMBER := 1;
378 --l_stmt VARCHAR2(5000);
379 --l_where VARCHAR2(2000) := ' ';
380 l_kle_id NUMBER;
381 l_start_date DATE;
382 l_final_start_date DATE;
383 ln_days NUMBER := 0;
384 l_arrears VARCHAR2(1);
385 l_frequency NUMBER;
386 l_contract_number VARCHAR2(2000);
387 --strm_csr ref_cursor;
388 l_elements element_type;
389 BEGIN
390 OPEN contract_number_csr(l_contracts_csr.khr_id);
391 FETCH contract_number_csr INTO l_contract_number;
392 IF contract_number_csr%NOTFOUND THEN
393 okl_api.set_message(p_app_name => G_APP_NAME,
394 p_msg_name => G_NO_MATCHING_RECORD,
395 p_token1 => G_COL_NAME_TOKEN,
396 p_token1_value => 'Lease Contract id');
397 RAISE okl_api.g_exception_error;
398 END IF;
399 CLOSE contract_number_csr;
400
401 /* sosharma ,14-12-2007
402 Bug 6691554
403 Modified to pick different cursor for pending contents in case p_mode is not null
404 Start Changes*/
405 IF p_mode IS NULL THEN
406 OPEN get_kle_id_csr(l_contracts_csr.khr_id);
407 FETCH get_kle_id_csr INTO l_kle_id, l_start_date;
408 IF get_kle_id_csr%NOTFOUND THEN
409 okl_api.set_message(p_app_name => G_APP_NAME,
410 p_msg_name => G_NO_MATCHING_RECORD,
411 p_token1 => G_COL_NAME_TOKEN,
412 p_token1_value => 'Lease contract id');
413 RAISE okl_api.g_exception_error;
414 END IF;
415 CLOSE get_kle_id_csr;
416 ELSE
417 OPEN get_kle_id_pend_csr(l_contracts_csr.khr_id);
418 FETCH get_kle_id_pend_csr INTO l_kle_id, l_start_date;
419 IF get_kle_id_pend_csr%NOTFOUND THEN
420 okl_api.set_message(p_app_name => G_APP_NAME,
421 p_msg_name => G_NO_MATCHING_RECORD,
422 p_token1 => G_COL_NAME_TOKEN,
423 p_token1_value => 'Lease contract id');
424 RAISE okl_api.g_exception_error;
425 END IF;
426 CLOSE get_kle_id_pend_csr;
427 END IF;
428 /* sosharma end changes */
429
430 IF l_kle_id IS NULL OR
431 l_kle_id = OKL_API.G_MISS_NUM THEN
432 okl_api.set_message(p_app_name => g_app_name,
433 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
434 p_token1 => g_contract_number_token,
435 p_token1_value => l_contract_number);
436 RAISE okl_api.g_exception_error;
437 END IF;
438 IF l_start_date IS NULL OR
439 l_start_date = okl_api.g_miss_date THEN
440 okl_api.set_message(p_app_name => g_app_name,
441 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
442 RAISE okl_api.g_exception_error;
443 END IF;
444 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
445 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
446 IF get_adv_arr_csr%NOTFOUND THEN
447 okl_api.set_message(p_app_name => G_APP_NAME,
448 p_msg_name => G_NO_MATCHING_RECORD,
449 p_token1 => G_COL_NAME_TOKEN,
450 p_token1_value => 'Lease Contract id and contract Line id');
451 RAISE okl_api.g_exception_error;
452 END IF;
453 CLOSE get_adv_arr_csr;
454 IF l_frequency IS NULL THEN
455 okl_api.set_message(p_app_name => g_app_name,
456 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
457 p_token1 => g_contract_number_token,
458 p_token1_value => l_contract_number);
459 RAISE okl_api.g_exception_error;
460 END IF;
461 IF l_arrears = 'Y' THEN
462 ln_days := OKL_STREAM_GENERATOR_PVT.get_day_count (
463 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
464 p_end_date => l_start_date,
465 p_arrears => l_arrears,
466 x_return_status => x_return_status);
467 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
468 RAISE okl_api.g_exception_unexpected_error;
469 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
470 RAISE okl_api.g_exception_error;
471 END IF;
472 l_final_start_date := l_start_date - ln_days;
473 ELSIF NVL(l_arrears,'N') = 'N' THEN
474 l_final_start_date := l_start_date;
475 END IF;
476 -- commenting as all accrual streams are generated
477 -- at contract level. Will remove comments after super trump fix is provided
478 -- for stream generation at asset level.Ref cursor will be needed later.
479 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
480 -- Commented for SQL Literals on 12-09-2006
481 /* l_stmt := 'SELECT ste.stream_element_date stream_element_date,
482 ste.amount amount
483 FROM okl_strm_type_b sty,
484 okl_streams stm,
485 okl_strm_elements ste
486 WHERE 1 = 1
487 AND stm.sty_id = sty.id
488 AND ste.stm_id = stm.id
489 AND stm.active_yn = '||''''||'Y'||''''||'
490 AND stm.say_code = '||''''||'CURR'||'''' ;
491 l_where := l_where ||' AND stm.khr_id = ' || l_contracts_csr.khr_id
492 ||' AND ste.stream_element_date BETWEEN '|| '''' ||l_final_start_date|| '''' ||' AND '|| '''' ||l_contracts_csr.end_date|| '''';
493 */
494 --get sty_id for the contract based on deal type
495
496 /* ankushar , 25-01-2008 Bug 6773285
497 Added code to generate new Stream Types for a Loan product on an Investor Agreement
498 Start Changes
499 */
500 IF l_contracts_csr.deal_type = 'LEASEOP' THEN
501 OKL_STREAMS_UTIL.get_primary_stream_type
502 (
503 p_khr_id => l_contracts_csr.khr_id,
504 p_primary_sty_purpose => l_investor_rental_accrual,
505 x_return_status => l_return_status,
506 x_primary_sty_id => l_sty_id
507 );
508 IF l_return_status <> 'S' THEN
509 okl_api.set_message(p_app_name => g_app_name,
510 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
511 p_token1 => 'STREAM_NAME',
512 p_token1_value => l_investor_rental_accrual);
513 RAISE okl_api.g_exception_error;
514 END IF;
515 -- calculate total revenue share
516 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'RENT') LOOP
517 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
518 END LOOP;
519 IF l_revenue_share IS NULL OR
520 l_revenue_share = 0 THEN
521 -- store SQL error message on message stack for caller
522 okl_api.set_message(p_app_name => g_app_name,
523 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
524 RAISE okl_api.G_EXCEPTION_ERROR;
525 END IF;
526 stream_type_purpose := l_rental_accrual;
527 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN
528 OKL_STREAMS_UTIL.get_primary_stream_type
529 (
530 p_khr_id => l_contracts_csr.khr_id,
531 p_primary_sty_purpose => l_investor_pre_tax_income,
532 x_return_status => l_return_status,
533 x_primary_sty_id => l_sty_id
534 );
535 IF l_return_status <> 'S' THEN
536 okl_api.set_message(p_app_name => g_app_name,
537 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
538 p_token1 => 'STREAM_NAME',
539 p_token1_value => l_investor_pre_tax_income);
540 RAISE okl_api.g_exception_error;
541 END IF;
542 -- calculate total revenue share
543 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'RENT') LOOP
544 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
545 END LOOP;
546 IF l_revenue_share IS NULL OR
547 l_revenue_share = 0 THEN
548 -- store SQL error message on message stack for caller
549 okl_api.set_message(p_app_name => g_app_name,
550 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
551 RAISE okl_api.G_EXCEPTION_ERROR;
552 END IF;
553 stream_type_purpose := l_pre_tax_income;
554 /* ankushar , 25-01-2008 Bug 6773285
555 End Changes
556 */
557
558 /* ankushar , 16-01-2008 Bug 6740000
559 Added condition for fetching stream type for a Loan product
560 Start Changes
561 */
562 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
563 OKL_STREAMS_UTIL.get_primary_stream_type
564 (
565 p_khr_id => l_contracts_csr.khr_id,
566 p_primary_sty_purpose => l_inv_interest_income_accrual,
567 x_return_status => l_return_status,
568 x_primary_sty_id => l_sty_id
569 );
570 IF l_return_status <> 'S' THEN
571 okl_api.set_message(p_app_name => g_app_name,
572 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
573 p_token1 => 'STREAM_NAME',
574 p_token1_value => l_inv_interest_income_accrual);
575 RAISE okl_api.g_exception_error;
576 END IF;
577 -- calculate total revenue share
578 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'LOAN_PAYMENT') LOOP
579 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
580 END LOOP;
581 IF l_revenue_share IS NULL OR
582 l_revenue_share = 0 THEN
583 -- store SQL error message on message stack for caller
584 okl_api.set_message(p_app_name => g_app_name,
585 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
586 RAISE okl_api.G_EXCEPTION_ERROR;
587 END IF;
588 stream_type_purpose := l_interest_income;
589 /* ankushar , 16-01-2008 Bug 6691554
590 End Changes
591 */
592 END IF;
593 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
594 -- populate stream header record
595 l_stmv_rec.sty_id := l_sty_id;
596 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
597 l_stmv_rec.sgn_code := 'MANL';
598 l_stmv_rec.say_code := 'WORK';
599
600 l_stmv_rec.transaction_number := l_trx_number;
601 l_stmv_rec.active_yn := 'N';
602
603 l_stmv_rec.date_working := l_sysdate;
604 -- create final l_stmt
605 --l_stmt := l_stmt || l_where;
606 --OPEN strm_csr FOR l_stmt;
607 -- use of a parameterized cursor by zrehman on 12-Sep-2006
608 OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
609 LOOP
610 --re-initialize period end date
611 l_period_end_date := NULL;
612 FETCH strm_csr INTO l_elements;
613 EXIT WHEN strm_csr%NOTFOUND;
614 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
615 --populate stream elements tbl
616 -- manipulate first record
617 IF strm_csr%ROWCOUNT = 1 THEN
618
619 -- If start date is last day of the month, do nothing.
620 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
621 -- If start date is the same as first day of the month then take whole amount.
622 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
623 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
624 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
625 l_selv_tbl(l_counter).se_line_number := l_line_number;
626 l_line_number := l_line_number + 1;
627 l_counter := l_counter + 1;
628 ELSE
629 -- start date is not first or last day of the month. so prorate.
630 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
631 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
632 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
633 l_selv_tbl(l_counter).se_line_number := l_line_number;
634 l_line_number := l_line_number + 1;
635 l_counter := l_counter + 1;
636 END IF;
637 END IF;
638 ELSE
639 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
640 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
641 l_selv_tbl(l_counter).se_line_number := l_line_number;
642 l_line_number := l_line_number + 1;
643 l_counter := l_counter + 1;
644 END IF;
645 END LOOP;
646 CLOSE strm_csr;
647 IF l_selv_tbl.COUNT > 0 THEN
648 -- call streams api
649 okl_streams_pub.create_streams(
650 p_api_version => p_api_version,
651 p_init_msg_list => p_init_msg_list,
652 x_return_status => x_return_status,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_data,
655 p_stmv_rec => l_stmv_rec,
656 p_selv_tbl => l_selv_tbl,
657 x_stmv_rec => x_stmv_rec,
658 x_selv_tbl => x_selv_tbl);
659 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
660 RAISE okl_api.g_exception_unexpected_error;
661 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
662 RAISE okl_api.g_exception_error;
663 END IF;
664 --call link api
665
666 OKL_CONTRACT_REBOOK_PVT.link_inv_accrual_streams(
667 p_api_version => p_api_version
668 ,p_init_msg_list => p_init_msg_list
669 ,x_return_status => l_return_status
670 ,x_msg_count => x_msg_count
671 ,x_msg_data => x_msg_data
672 ,p_khr_id =>l_contracts_csr.khr_id
673 );
674 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
675 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
676 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
677 RAISE Okl_Api.G_EXCEPTION_ERROR;
678 END IF;
679 -- Update the status of streams from 'CURR' to 'HIST'
680
681 j:=0;
682 l_id_tbl.delete;
683
684 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
685 ,p_sty_id=>l_sty_id
686 ,p_say_code=>'CURR')
687 LOOP
688 j := j + 1;
689 l_id_tbl(j) :=stream_id_rec.id;
690 END LOOP;
691
692 IF (l_id_tbl.COUNT > 0) THEN
693
694 BEGIN
695
696 FORALL i IN l_id_tbl.FIRST..l_id_tbl.LAST
697 UPDATE OKL_STREAMS
698 SET say_code = 'HIST',
699 active_yn = 'N',
700 date_history = sysdate
701 WHERE ID = l_id_tbl(i);
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 okl_api.set_message (p_app_name => G_APP_NAME,
706 p_msg_name => G_DB_ERROR,
707 p_token1 => G_PROG_NAME_TOKEN,
708 p_token1_value => l_api_name,
709 p_token2 => G_SQLCODE_TOKEN,
710 p_token2_value => sqlcode,
711 p_token3 => G_SQLERRM_TOKEN,
712 p_token3_value => sqlerrm);
713 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
714 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
715 END;
716 END IF;
717
718 --------------------------------------------
719 -- Update the status of 'WORK' to 'CURR'
720 ----------------------------------
721 j:=0;
722 l_id_tbl.delete;
723 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
724 ,p_sty_id=>l_sty_id
725 ,p_say_code=>'WORK')
726 LOOP
727 j := j + 1;
728 l_id_tbl(j) :=stream_id_rec.id;
729 END LOOP;
730
731 IF (l_id_tbl.COUNT > 0) THEN
732
733 BEGIN
734
735 FORALL i IN l_id_tbl.FIRST..l_id_tbl.LAST
736 UPDATE OKL_STREAMS
737 SET say_code = 'CURR',
738 active_yn = 'Y',
739 date_current = sysdate
740 WHERE ID = l_id_tbl(i);
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 okl_api.set_message (p_app_name => G_APP_NAME,
745 p_msg_name => G_DB_ERROR,
746 p_token1 => G_PROG_NAME_TOKEN,
747 p_token1_value => l_api_name,
748 p_token2 => G_SQLCODE_TOKEN,
749 p_token2_value => sqlcode,
750 p_token3 => G_SQLERRM_TOKEN,
751 p_token3_value => sqlerrm);
752 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
753 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
754 END;
755 END IF;
756
757 END IF; --end if for table count >0
758
759
760 EXCEPTION
761 WHEN okl_api.g_exception_error THEN
762 IF contract_number_csr%ISOPEN THEN
763 CLOSE contract_number_csr;
764 END IF;
765 IF get_kle_id_csr%ISOPEN THEN
766 CLOSE get_kle_id_csr;
767 END IF;
768 IF get_adv_arr_csr%ISOPEN THEN
769 CLOSE get_adv_arr_csr;
770 END IF;
771 IF get_sty_id_csr%ISOPEN THEN
772 CLOSE get_sty_id_csr;
773 END IF;
774 IF strm_csr%ISOPEN THEN
775 CLOSE strm_csr;
776 END IF;
777 x_return_status := okl_api.handle_exceptions(l_api_name,
778 g_pkg_name,
779 'OKL_API.G_RET_STS_ERROR',
780 x_msg_count,
781 x_msg_data,
782 '_PVT');
783 WHEN okl_api.g_exception_unexpected_error THEN
784 IF contract_number_csr%ISOPEN THEN
785 CLOSE contract_number_csr;
786 END IF;
787 IF get_kle_id_csr%ISOPEN THEN
788 CLOSE get_kle_id_csr;
789 END IF;
790 IF get_adv_arr_csr%ISOPEN THEN
791 CLOSE get_adv_arr_csr;
792 END IF;
793 IF get_sty_id_csr%ISOPEN THEN
794 CLOSE get_sty_id_csr;
795 END IF;
796 IF strm_csr%ISOPEN THEN
797 CLOSE strm_csr;
798 END IF;
799 x_return_status := okl_api.handle_exceptions(l_api_name,
800 g_pkg_name,
801 'OKL_API.G_RET_STS_UNEXP_ERROR',
802 x_msg_count,
803 x_msg_data,
804 '_PVT');
805 WHEN OTHERS THEN
806 IF contract_number_csr%ISOPEN THEN
807 CLOSE contract_number_csr;
808 END IF;
809 IF get_kle_id_csr%ISOPEN THEN
810 CLOSE get_kle_id_csr;
811 END IF;
812 IF get_adv_arr_csr%ISOPEN THEN
813 CLOSE get_adv_arr_csr;
814 END IF;
815 IF get_sty_id_csr%ISOPEN THEN
816 CLOSE get_sty_id_csr;
817 END IF;
818 IF strm_csr%ISOPEN THEN
819 CLOSE strm_csr;
820 END IF;
821 x_return_status := okl_api.handle_exceptions(l_api_name,
822 g_pkg_name,
823 'OTHERS',
824 x_msg_count,
825 x_msg_data,
826 '_PVT');
827 END;
828 OKL_API.END_ACTIVITY (x_msg_count,
829 x_msg_data );
830 EXCEPTION
831 WHEN OKL_API.G_EXCEPTION_ERROR THEN
832 IF contract_number_csr%ISOPEN THEN
833 CLOSE contract_number_csr;
834 END IF;
835 IF get_kle_id_csr%ISOPEN THEN
836 CLOSE get_kle_id_csr;
837 END IF;
838 IF get_adv_arr_csr%ISOPEN THEN
839 CLOSE get_adv_arr_csr;
840 END IF;
841 IF get_sty_id_csr%ISOPEN THEN
842 CLOSE get_sty_id_csr;
843 END IF;
844 IF securitized_contracts_csr%ISOPEN THEN
845 CLOSE securitized_contracts_csr;
846 END IF;
847 IF get_revenue_share_csr%ISOPEN THEN
848 CLOSE get_revenue_share_csr;
849 END IF;
850 IF get_inv_csr%ISOPEN THEN
851 CLOSE get_inv_csr;
852 END IF;
853 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
854 l_api_name,
855 G_PKG_NAME,
856 'OKL_API.G_RET_STS_ERROR',
857 x_msg_count,
858 x_msg_data,
859 '_PVT');
860 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
861 IF contract_number_csr%ISOPEN THEN
862 CLOSE contract_number_csr;
863 END IF;
864 IF get_kle_id_csr%ISOPEN THEN
865 CLOSE get_kle_id_csr;
866 END IF;
867 IF get_adv_arr_csr%ISOPEN THEN
868 CLOSE get_adv_arr_csr;
869 END IF;
870 IF get_sty_id_csr%ISOPEN THEN
871 CLOSE get_sty_id_csr;
872 END IF;
873 IF securitized_contracts_csr%ISOPEN THEN
874 CLOSE securitized_contracts_csr;
875 END IF;
876 IF get_revenue_share_csr%ISOPEN THEN
877 CLOSE get_revenue_share_csr;
878 END IF;
879 IF get_inv_csr%ISOPEN THEN
880 CLOSE get_inv_csr;
881 END IF;
882 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
883 l_api_name,
884 G_PKG_NAME,
885 'OKL_API.G_RET_STS_UNEXP_ERROR',
886 x_msg_count,
887 x_msg_data,
888 '_PVT');
889 WHEN OTHERS THEN
890 IF contract_number_csr%ISOPEN THEN
891 CLOSE contract_number_csr;
892 END IF;
893 IF get_kle_id_csr%ISOPEN THEN
894 CLOSE get_kle_id_csr;
895 END IF;
896 IF get_adv_arr_csr%ISOPEN THEN
897 CLOSE get_adv_arr_csr;
898 END IF;
899 IF get_sty_id_csr%ISOPEN THEN
900 CLOSE get_sty_id_csr;
901 END IF;
902 IF securitized_contracts_csr%ISOPEN THEN
903 CLOSE securitized_contracts_csr;
904 END IF;
905 IF get_revenue_share_csr%ISOPEN THEN
906 CLOSE get_revenue_share_csr;
907 END IF;
908 IF get_inv_csr%ISOPEN THEN
909 CLOSE get_inv_csr;
910 END IF;
911 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
912 l_api_name,
913 G_PKG_NAME,
914 'OTHERS',
915 x_msg_count,
916 x_msg_data,
917 '_PVT');
918 END create_lease_streams;
919 --------------------------------------------------------------------------------
920 PROCEDURE CREATE_STREAMS(p_api_version IN NUMBER,
921 p_init_msg_list IN VARCHAR2,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_count OUT NOCOPY NUMBER,
924 x_msg_data OUT NOCOPY VARCHAR2,
925 p_khr_id IN NUMBER,
926 --sosharma added Bug 6691554, Added for generating streams on transient pool submission
927 p_mode IN VARCHAR2 DEFAULT NULL)
928 IS
929
930 l_count NUMBER := 1;
931 l_api_version CONSTANT NUMBER := 1.0;
932 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_STREAMS';
933 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
934 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
935 l_msg_count NUMBER;
936 l_msg_data VARCHAR2(2000);
937 l_period_end_date DATE;
938 l_total_records NUMBER;
939 l_sysdate DATE := TRUNC(SYSDATE);
940 l_trx_number NUMBER;
941 l_revenue_share NUMBER := 0;
942 l_sty_id NUMBER;
943
944 stream_type_purpose VARCHAR2(30);
945 l_khr_num OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE := NULL;
946 l_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE := NULL;
947 /*
948 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
949 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
950 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
951 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
952 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENTAL ACCRUAL';
953 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'PRE-TAX INCOME';
954 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST INCOME';
955 l_variable_income CONSTANT VARCHAR2(2000) := 'VARIABLE INCOME ACCRUAL';
956 */
957 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
958 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
959 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
960 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
961 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENT_ACCRUAL';
962 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'LEASE_INCOME';
963 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST_INCOME';
964 l_variable_income CONSTANT VARCHAR2(2000) := 'ACCOUNTING';
965 /* ankushar , 16-01-2008 Bug 6740000
966 Added new Stream Type purpose for a Loan product
967 */
968 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
969
970 /* ankushar , 16-01-2008 Bug 6740000
971 Modified cursors to fetch based on stream type for a Loan product
972 Start Changes
973 */
974 -- cursor to select contracts belonging to a pool(investor agreement)
975 CURSOR securitized_contracts_csr (p_inv_id NUMBER)
976 IS
977 SELECT DISTINCT opc.khr_id khr_id,
978 opc.streams_to_date end_date,
979 khr.deal_type deal_type
980 FROM OKL_POOL_CONTENTS opc,
981 OKL_POOLS op,
982 OKL_K_HEADERS khr,
983 OKL_STRM_TYPE_B stytl
984 WHERE op.khr_id = p_inv_id
985 AND op.id = opc.pol_id
986 AND opc.khr_id = khr.id
987 AND opc.sty_id = stytl.id
988 --AND stytl.code = 'RENT'
989 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
990 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
991 GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
992
993 -- cursor to get first kle_id and earliest stream element date
994 CURSOR get_kle_id_csr(p_khr_id NUMBER)
995 IS
996 SELECT opc.kle_id kle_id,
997 MIN(opc.streams_from_date) start_date
998 FROM OKL_POOL_CONTENTS opc,
999 OKL_STRM_TYPE_B sty
1000 WHERE opc.khr_id = p_khr_id
1001 AND opc.sty_id = sty.id
1002 --AND sty.code = 'RENT'
1003 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1004 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
1005 AND ROWNUM < 2
1006 GROUP BY opc.kle_id;
1007
1008 /* sosharma ,14-12-2007
1009 Bug 6691554
1010 Cursors to pick up pools contents in pending status
1011 Start Changes*/
1012
1013 CURSOR securitized_contracts_pend_csr (p_inv_id NUMBER)
1014 IS
1015 SELECT DISTINCT opc.khr_id khr_id,
1016 opc.streams_to_date end_date,
1017 khr.deal_type deal_type
1018 FROM OKL_POOL_CONTENTS opc,
1019 OKL_POOLS op,
1020 OKL_K_HEADERS khr,
1021 OKL_STRM_TYPE_B stytl
1022 WHERE op.khr_id = p_inv_id
1023 AND op.id = opc.pol_id
1024 AND opc.khr_id = khr.id
1025 AND opc.sty_id = stytl.id
1026 --AND stytl.code = 'RENT'
1027 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1028 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
1029 GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
1030
1031 -- cursor to get first kle_id and earliest stream element date
1032 CURSOR get_kle_id_pend_csr(p_khr_id NUMBER)
1033 IS
1034 SELECT opc.kle_id kle_id,
1035 MIN(opc.streams_from_date) start_date
1036 FROM OKL_POOL_CONTENTS opc,
1037 OKL_STRM_TYPE_B sty
1038 WHERE opc.khr_id = p_khr_id
1039 AND opc.sty_id = sty.id
1040 --AND sty.code = 'RENT'
1041 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1042 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
1043 AND ROWNUM < 2
1044 GROUP BY opc.kle_id;
1045
1046 /* sosharma end changes*/
1047
1048 -- cursor to get advance/arrears and frequency for rent stream type
1049 CURSOR get_adv_arr_csr(p_khr_id NUMBER, p_kle_id NUMBER)
1050 IS
1051 SELECT decode(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
1052 decode(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
1053 FROM OKC_K_HEADERS_B K,
1054 OKC_RULE_GROUPS_B RG,
1055 OKC_RULES_B SLH,
1056 OKC_RULES_B SLL,
1057 OKL_STRM_TYPE_B STRM
1058 WHERE slh.rule_information_category = 'LASLH'
1059 AND slh.rgp_id = rg.id
1060 AND sll.object2_id1 = to_char(slh.id)
1061 AND sll.rgp_id = rg.id
1062 AND slh.object1_id1 = to_char(strm.id)
1063 --AND strm.code = 'RENT'
1064 AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1065 AND rg.dnz_chr_id = k.id
1066 AND rg.chr_id IS NULL
1067 AND rg.rgd_code = 'LALEVL'
1068 AND rg.cle_id = p_kle_id
1069 AND k.id = p_khr_id;
1070
1071 -- cursor to get stream type id
1072 CURSOR get_sty_id_csr (p_sty_name VARCHAR2)
1073 IS
1074 SELECT id
1075 FROM OKL_STRM_TYPE_B
1076 WHERE code = p_sty_name;
1077
1078 -- list of all investors for the agreement
1079 CURSOR get_investors_csr(p_khr_id IN NUMBER)
1080 IS
1081 SELECT clet.id id
1082 FROM OKC_K_LINES_B clet
1083 ,OKC_LINE_STYLES_B lseb
1084 WHERE clet.dnz_chr_id = p_khr_id
1085 AND clet.lse_id = lseb.id
1086 AND lseb.lty_code = 'INVESTMENT';
1087
1088 -- ankushar Added stream_type_subclass parameter to the cursor
1089 -- the revenue shares for the investor
1090 CURSOR get_revenue_share_csr(p_tl_id NUMBER, p_stream_type_subclass VARCHAR2)
1091 IS
1092 SELECT kleb.percent_stake percent_stake
1093 FROM OKL_K_LINES kleb
1094 ,OKC_K_LINES_B cles
1095 ,OKC_LINE_STYLES_B lseb
1096 WHERE kleb.id = cles.id
1097 AND cles.cle_id = p_tl_id
1098 AND cles.lse_id = lseb.id
1099 AND lseb.lty_code = 'REVENUE_SHARE'
1100 AND kleb.stream_type_subclass = p_stream_type_subclass;
1101
1102 /* ankushar , 16-01-2008 Bug 6740000
1103 End Changes
1104 */
1105 -- cursor to get contract number
1106 CURSOR contract_number_csr (p_khr_id NUMBER) IS
1107 SELECT contract_number,
1108 scs_code
1109 FROM OKC_K_HEADERS_B
1110 WHERE id = p_khr_id;
1111
1112
1113
1114 l_contracts_csr securitized_contracts_csr%ROWTYPE;
1115
1116 -- declaration of a parameterized cursor by zrehman on 12-Sep-2006
1117 CURSOR strm_csr (p_khr_id NUMBER,
1118 p_final_start_date DATE,
1119 p_end_date DATE,
1120 p_stream_type_purpose VARCHAR2)
1121 IS
1122 select ste.stream_element_date stream_element_date,ste.amount amount
1123 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
1124 WHERE
1125 stm.sty_id = sty.id
1126 AND ste.stm_id = stm.id
1127 AND stm.active_yn = 'Y'
1128 AND stm.say_code = 'CURR'
1129 AND stm.khr_id = p_khr_id
1130 AND ste.stream_element_date BETWEEN p_final_start_date AND p_end_date
1131 AND sty.stream_type_purpose = p_stream_type_purpose
1132 ORDER BY ste.stream_element_date;
1133
1134
1135
1136 BEGIN
1137 -- Set save point
1138 l_return_status := OKL_API.START_ACTIVITY(
1139 p_api_name => l_api_name,
1140 p_pkg_name => G_PKG_NAME,
1141 p_init_msg_list => p_init_msg_list,
1142 l_api_version => l_api_version,
1143 p_api_version => p_api_version,
1144 p_api_type => '_PVT',
1145 x_return_status => l_return_status);
1146 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1147 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1148 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1149 RAISE Okl_Api.G_EXCEPTION_ERROR;
1150 END IF;
1151 OPEN contract_number_csr(p_khr_id);
1152 FETCH contract_number_csr INTO l_khr_num,
1153 l_scs_code;
1154 CLOSE contract_number_csr;
1155 -- If the contract is lease contract then we call the below
1156 -- procedure usaually the same is called during rebook contract
1157 IF l_scs_code = 'LEASE' THEN
1158 create_lease_streams(p_api_version => p_api_version,
1159 p_init_msg_list => p_init_msg_list,
1160 x_return_status => l_return_status,
1161 x_msg_count => x_msg_count,
1162 x_msg_data => x_msg_data,
1163 p_khr_id => p_khr_id,
1164 p_mode => p_mode);
1165 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1166 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1167 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1168 RAISE Okl_Api.G_EXCEPTION_ERROR;
1169 END IF;
1170 -- If the contract is Investor contract then we call the below
1171 ELSIF l_scs_code = 'INVESTOR' THEN
1172 -- validate in parameters
1173 IF p_khr_id IS NULL OR
1174 p_khr_id = OKL_API.G_MISS_NUM THEN
1175 -- store SQL error message on message stack for caller
1176 Okl_Api.set_message(p_app_name => g_app_name,
1177 p_msg_name => 'OKL_ASC_KHR_ID_ERROR');
1178 RAISE Okl_Api.G_EXCEPTION_ERROR;
1179 END IF;
1180
1181 IF p_mode IS NULL THEN
1182 OPEN securitized_contracts_csr (p_khr_id);
1183 LOOP
1184 /* sosharma 06-02-2007
1185 Initilized the local variable l_revenue_share
1186 Start changes
1187 */
1188 l_revenue_share := 0;
1189 /*
1190 sosharma end changes
1191 */
1192 FETCH securitized_contracts_csr INTO l_contracts_csr;
1193 EXIT WHEN securitized_contracts_csr%NOTFOUND;
1194 DECLARE
1195 TYPE ref_cursor IS REF CURSOR;
1196 TYPE element_type IS RECORD (stream_element_date DATE, amount NUMBER);
1197 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1198 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1199 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1200 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1201 l_difference NUMBER := 0;
1202 l_counter NUMBER := 1;
1203 l_line_number NUMBER := 1;
1204 --l_stmt VARCHAR2(5000);
1205 --l_where VARCHAR2(2000) := ' ';
1206 l_kle_id NUMBER;
1207 l_start_date DATE;
1208 l_final_start_date DATE;
1209 ln_days NUMBER := 0;
1210 l_arrears VARCHAR2(1);
1211 l_frequency NUMBER;
1212 l_contract_number VARCHAR2(2000);
1213 --strm_csr ref_cursor;
1214 l_elements element_type;
1215 BEGIN
1216 OPEN contract_number_csr(l_contracts_csr.khr_id);
1217 FETCH contract_number_csr INTO l_contract_number,
1218 l_scs_code;
1219 CLOSE contract_number_csr;
1220
1221 OPEN get_kle_id_csr(l_contracts_csr.khr_id);
1222 FETCH get_kle_id_csr INTO l_kle_id, l_start_date;
1223 CLOSE get_kle_id_csr;
1224
1225 IF l_kle_id IS NULL OR
1226 l_kle_id = OKL_API.G_MISS_NUM THEN
1227 Okl_Api.set_message(p_app_name => g_app_name,
1228 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
1229 p_token1 => g_contract_number_token,
1230 p_token1_value => l_contract_number);
1231 RAISE OKL_API.G_EXCEPTION_ERROR;
1232 END IF;
1233
1234 IF l_start_date IS NULL OR l_start_date = OKL_API.G_MISS_DATE THEN
1235 Okl_Api.set_message(p_app_name => g_app_name,
1236 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
1237 RAISE OKL_API.G_EXCEPTION_ERROR;
1238 END IF;
1239
1240 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
1241 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
1242 CLOSE get_adv_arr_csr;
1243
1244 IF l_frequency IS NULL THEN
1245 Okl_Api.set_message(p_app_name => g_app_name,
1246 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
1247 p_token1 => g_contract_number_token,
1248 p_token1_value => l_contract_number);
1249 RAISE OKL_API.G_EXCEPTION_ERROR;
1250 END IF;
1251
1252 IF l_arrears = 'Y' THEN
1253 ln_days := okl_stream_generator_pvt.get_day_count (
1254 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
1255 p_end_date => l_start_date,
1256 p_arrears => l_arrears,
1257 x_return_status => l_return_status);
1258 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1259 RAISE okl_api.g_exception_unexpected_error;
1260 ELSIF (l_return_Status = okl_api.g_ret_sts_error) THEN
1261 RAISE okl_api.g_exception_error;
1262 END IF;
1263 l_final_start_date := l_start_date - ln_days;
1264 ELSIF NVL(l_arrears,'N') = 'N' THEN
1265 l_final_start_date := l_start_date;
1266 END IF;
1267
1268 -- commenting as all accrual streams are generated
1269 -- at contract level. Will remove comments after super trump fix is provided
1270 -- for stream generation at asset level.Ref cursor will be needed later.
1271 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
1272
1273 -- SQL Literals Change on 12/09/2006
1274 /*l_stmt := 'SELECT ste.stream_element_date stream_element_date
1275 ,ste.amount amount
1276 FROM OKL_STRM_TYPE_B sty
1277 ,OKL_STREAMS stm
1278 ,OKL_STRM_ELEMENTS ste
1279 WHERE 1 = 1
1280 AND stm.sty_id = sty.id
1281 AND ste.stm_id = stm.id
1282 AND stm.active_yn = '||''''||'Y'||''''||'
1283 AND stm.say_code = '||''''||'CURR'||'''' ;
1284 l_where := l_where || ' AND stm.khr_id = ' || l_contracts_csr.khr_id ||' AND ste.stream_element_date BETWEEN '|| '''' ||l_final_start_date|| '''' ||' AND '|| '''' ||l_contracts_csr.end_date|| ''''; */
1285
1286 /* ankushar , 25-01-2008 Bug 6773285
1287 Added code to generate new Stream Types for a Loan product on an Investor Agreement
1288 Start Changes
1289 */
1290 --get sty_id for the contract based on deal type
1291 IF l_contracts_csr.deal_type = 'LEASEOP' THEN
1292 OKL_STREAMS_UTIL.get_primary_stream_type
1293 (
1294 p_khr_id => l_contracts_csr.khr_id,
1295 p_primary_sty_purpose => l_investor_rental_accrual,
1296 x_return_status => l_return_status,
1297 x_primary_sty_id => l_sty_id
1298 );
1299 IF l_return_status <> 'S' THEN
1300 okl_api.set_message(p_app_name => g_app_name,
1301 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1302 p_token1 => 'STREAM_NAME',
1303 p_token1_value => l_investor_rental_accrual);
1304 RAISE okl_api.g_exception_error;
1305
1306 END IF;
1307 -- calculate total revenue share
1308 FOR x IN get_investors_csr(p_khr_id) LOOP
1309 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1310 l_revenue_share := l_revenue_share + y.percent_stake;
1311 END LOOP;
1312 END LOOP;
1313
1314 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1315 -- store SQL error message on message stack for caller
1316 Okl_Api.set_message(p_app_name => g_app_name,
1317 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1318 RAISE Okl_Api.G_EXCEPTION_ERROR;
1319 END IF;
1320 --Modified by kthiruva on 19-Oct-2005 . The stream type purpose needs to be bound instead
1321 --of the stream type code
1322 --Bug 4228708 - Start of Changes
1323 -- l_where := l_where ||' AND sty.stream_type_purpose = '|| '''' ||l_rental_accrual|| '''' ||' ORDER BY ste.stream_element_date';
1324 stream_type_purpose := l_rental_accrual;
1325 --Bug 4228708 - End of Changes
1326 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN
1327 OKL_STREAMS_UTIL.get_primary_stream_type
1328 (
1329 p_khr_id => l_contracts_csr.khr_id,
1330 p_primary_sty_purpose => l_investor_pre_tax_income,
1331 x_return_status => l_return_status,
1332 x_primary_sty_id => l_sty_id
1333 );
1334
1335 IF l_return_status <> 'S' THEN
1336 okl_api.set_message(p_app_name => g_app_name,
1337 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1338 p_token1 => 'STREAM_NAME',
1339 p_token1_value => l_investor_pre_tax_income);
1340 RAISE okl_api.g_exception_error;
1341 END IF;
1342
1343 -- calculate total revenue share
1344 FOR x IN get_investors_csr(p_khr_id) LOOP
1345 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1346 l_revenue_share := l_revenue_share + y.percent_stake;
1347 END LOOP;
1348 END LOOP;
1349
1350 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1351 -- store SQL error message on message stack for caller
1352 Okl_Api.set_message(p_app_name => g_app_name,
1353 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1354 RAISE Okl_Api.G_EXCEPTION_ERROR;
1355 END IF;
1356 stream_type_purpose := l_pre_tax_income;
1357 /* ankushar , 25-01-2008 Bug 6773285
1358 End Changes
1359 */
1360 /* ankushar , 16-01-2008 Bug 6740000
1361 Added condition for fetching stream type for a Loan product
1362 Start Changes
1363 */
1364 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
1365 OKL_STREAMS_UTIL.get_primary_stream_type
1366 (
1367 p_khr_id => l_contracts_csr.khr_id,
1368 p_primary_sty_purpose => l_inv_interest_income_accrual,
1369 x_return_status => l_return_status,
1370 x_primary_sty_id => l_sty_id
1371 );
1372 IF l_return_status <> 'S' THEN
1373 okl_api.set_message(p_app_name => g_app_name,
1374 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1375 p_token1 => 'STREAM_NAME',
1376 p_token1_value => l_inv_interest_income_accrual);
1377 RAISE okl_api.g_exception_error;
1378 END IF;
1379
1380 -- calculate total revenue share
1381 FOR x IN get_investors_csr(p_khr_id) LOOP
1382 FOR y IN get_revenue_share_csr(x.id, 'LOAN_PAYMENT') LOOP
1383 l_revenue_share := l_revenue_share + y.percent_stake;
1384 END LOOP;
1385 END LOOP;
1386
1387 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1388 -- store SQL error message on message stack for caller
1389 Okl_Api.set_message(p_app_name => g_app_name,
1390 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1391 RAISE Okl_Api.G_EXCEPTION_ERROR;
1392 END IF;
1393
1394 stream_type_purpose := l_interest_income;
1395 /* ankushar , 16-01-2008 Bug 6740000
1396 End Changes
1397 */
1398 END IF; -- ELSIF
1399
1400 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
1401 -- populate stream header record
1402 l_stmv_rec.sty_id := l_sty_id;
1403 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
1404 l_stmv_rec.sgn_code := 'MANL';
1405 l_stmv_rec.say_code := 'CURR';
1406 l_stmv_rec.transaction_number := l_trx_number;
1407 l_stmv_rec.active_yn := 'Y';
1408 l_stmv_rec.date_current := l_sysdate;
1409 l_stmv_rec.source_id := p_khr_id;
1410 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1411 -- create final l_stmt
1412 --l_stmt := l_stmt || l_where;
1413 --OPEN strm_csr FOR l_stmt;
1414 -- use of a parameterized cursor by zrehman on 12-Sep-2006
1415 OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
1416 LOOP
1417 --re-initialize period end date
1418 l_period_end_date := NULL;
1419 FETCH strm_csr INTO l_elements;
1420 EXIT WHEN strm_csr%NOTFOUND;
1421 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
1422 --populate stream elements tbl
1423 -- manipulate first record
1424 IF strm_csr%ROWCOUNT = 1 THEN
1425 -- If start date is last day of the month, do nothing.
1426 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
1427 -- If start date is the same as first day of the month then take whole amount.
1428 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
1429 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
1430 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1431 l_selv_tbl(l_counter).se_line_number := l_line_number;
1432 l_line_number := l_line_number + 1;
1433 l_counter := l_counter + 1;
1434 ELSE
1435 -- start date is not first or last day of the month. so prorate.
1436 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
1437 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
1438 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1439 l_selv_tbl(l_counter).se_line_number := l_line_number;
1440 l_line_number := l_line_number + 1;
1441 l_counter := l_counter + 1;
1442 END IF;
1443 END IF;
1444 ELSE
1445 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
1446 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1447 l_selv_tbl(l_counter).se_line_number := l_line_number;
1448 l_line_number := l_line_number + 1;
1449 l_counter := l_counter + 1;
1450 END IF;
1451 END LOOP;
1452 CLOSE strm_csr;
1453 IF l_selv_tbl.COUNT > 0 THEN
1454 -- call streams api
1455 OKL_STREAMS_PUB.create_streams(
1456 p_api_version => l_api_version
1457 ,p_init_msg_list => l_init_msg_list
1458 ,x_return_status => l_return_status
1459 ,x_msg_count => l_msg_count
1460 ,x_msg_data => l_msg_data
1461 ,p_stmv_rec => l_stmv_rec
1462 ,p_selv_tbl => l_selv_tbl
1463 ,x_stmv_rec => x_stmv_rec
1464 ,x_selv_tbl => x_selv_tbl );
1465 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1466 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1467 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1468 RAISE Okl_Api.G_EXCEPTION_ERROR;
1469 END IF;
1470 END IF;
1471 EXCEPTION
1472 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1473 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1474 ,g_pkg_name
1475 ,'OKL_API.G_RET_STS_ERROR'
1476 ,x_msg_count
1477 ,x_msg_data
1478 ,'_PVT');
1479 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1480 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1481 ,g_pkg_name
1482 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1483 ,x_msg_count
1484 ,x_msg_data
1485 ,'_PVT');
1486 WHEN OTHERS THEN
1487 IF get_kle_id_csr%ISOPEN THEN
1488 CLOSE get_kle_id_csr;
1489 END IF;
1490 IF get_adv_arr_csr%ISOPEN THEN
1491 CLOSE get_adv_arr_csr;
1492 END IF;
1493
1494 IF get_sty_id_csr%ISOPEN THEN
1495 CLOSE get_sty_id_csr;
1496 END IF;
1497
1498 IF strm_csr%ISOPEN THEN
1499 CLOSE strm_csr;
1500 END IF;
1501 l_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
1502 G_PKG_NAME,
1503 'OTHERS',
1504 x_msg_count,
1505 x_msg_data,
1506 '_PVT');
1507 END;
1508 END LOOP;
1509 CLOSE securitized_contracts_csr;
1510
1511 ELSE
1512 OPEN securitized_contracts_pend_csr (p_khr_id);
1513 LOOP
1514 FETCH securitized_contracts_pend_csr INTO l_contracts_csr;
1515 EXIT WHEN securitized_contracts_pend_csr%NOTFOUND;
1516 DECLARE
1517 TYPE ref_cursor IS REF CURSOR;
1518 TYPE element_type IS RECORD (stream_element_date DATE, amount NUMBER);
1519 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1520 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1521 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1522 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1523 l_difference NUMBER := 0;
1524 l_counter NUMBER := 1;
1525 l_line_number NUMBER := 1;
1526 --l_stmt VARCHAR2(5000);
1527 --l_where VARCHAR2(2000) := ' ';
1528 l_kle_id NUMBER;
1529 l_start_date DATE;
1530 l_final_start_date DATE;
1531 ln_days NUMBER := 0;
1532 l_arrears VARCHAR2(1);
1533 l_frequency NUMBER;
1534 l_contract_number VARCHAR2(2000);
1535 --strm_csr ref_cursor;
1536 l_elements element_type;
1537 BEGIN
1538 OPEN contract_number_csr(l_contracts_csr.khr_id);
1539 FETCH contract_number_csr INTO l_contract_number,
1540 l_scs_code;
1541 CLOSE contract_number_csr;
1542
1543 OPEN get_kle_id_pend_csr(l_contracts_csr.khr_id);
1544 FETCH get_kle_id_pend_csr INTO l_kle_id, l_start_date;
1545 CLOSE get_kle_id_pend_csr;
1546
1547 IF l_kle_id IS NULL OR
1548 l_kle_id = OKL_API.G_MISS_NUM THEN
1549 Okl_Api.set_message(p_app_name => g_app_name,
1550 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
1551 p_token1 => g_contract_number_token,
1552 p_token1_value => l_contract_number);
1553 RAISE OKL_API.G_EXCEPTION_ERROR;
1554 END IF;
1555
1556 IF l_start_date IS NULL OR l_start_date = OKL_API.G_MISS_DATE THEN
1557 Okl_Api.set_message(p_app_name => g_app_name,
1558 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
1559 RAISE OKL_API.G_EXCEPTION_ERROR;
1560 END IF;
1561
1562 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
1563 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
1564 CLOSE get_adv_arr_csr;
1565
1566 IF l_frequency IS NULL THEN
1567 Okl_Api.set_message(p_app_name => g_app_name,
1568 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
1569 p_token1 => g_contract_number_token,
1570 p_token1_value => l_contract_number);
1571 RAISE OKL_API.G_EXCEPTION_ERROR;
1572 END IF;
1573
1574 IF l_arrears = 'Y' THEN
1575 ln_days := okl_stream_generator_pvt.get_day_count (
1576 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
1577 p_end_date => l_start_date,
1578 p_arrears => l_arrears,
1579 x_return_status => l_return_status);
1580 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1581 RAISE okl_api.g_exception_unexpected_error;
1582 ELSIF (l_return_Status = okl_api.g_ret_sts_error) THEN
1583 RAISE okl_api.g_exception_error;
1584 END IF;
1585 l_final_start_date := l_start_date - ln_days;
1586 ELSIF NVL(l_arrears,'N') = 'N' THEN
1587 l_final_start_date := l_start_date;
1588 END IF;
1589
1590 -- commenting as all accrual streams are generated
1591 -- at contract level. Will remove comments after super trump fix is provided
1592 -- for stream generation at asset level.Ref cursor will be needed later.
1593 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
1594
1595
1596 --get sty_id for the contract based on deal type
1597 /* ankushar , 25-01-2008 Bug 6773285
1598 Added code to generate new Stream Types for a Loan product on an Investor Agreement
1599 Start Changes
1600 */
1601 --get sty_id for the contract based on deal type
1602 IF l_contracts_csr.deal_type = 'LEASEOP' THEN
1603 OKL_STREAMS_UTIL.get_primary_stream_type
1604 (
1605 p_khr_id => l_contracts_csr.khr_id,
1606 p_primary_sty_purpose => l_investor_rental_accrual,
1607 x_return_status => l_return_status,
1608 x_primary_sty_id => l_sty_id
1609 );
1610 IF l_return_status <> 'S' THEN
1611 okl_api.set_message(p_app_name => g_app_name,
1612 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1613 p_token1 => 'STREAM_NAME',
1614 p_token1_value => l_investor_rental_accrual);
1615 RAISE okl_api.g_exception_error;
1616
1617 END IF;
1618 -- calculate total revenue share
1619 FOR x IN get_investors_csr(p_khr_id) LOOP
1620 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1621 l_revenue_share := l_revenue_share + y.percent_stake;
1622 END LOOP;
1623 END LOOP;
1624
1625 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1626 -- store SQL error message on message stack for caller
1627 Okl_Api.set_message(p_app_name => g_app_name,
1628 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1629 RAISE Okl_Api.G_EXCEPTION_ERROR;
1630 END IF;
1631 --Modified by kthiruva on 19-Oct-2005 . The stream type purpose needs to be bound instead
1632 --of the stream type code
1633 --Bug 4228708 - Start of Changes
1634 -- l_where := l_where ||' AND sty.stream_type_purpose = '|| '''' ||l_rental_accrual|| '''' ||' ORDER BY ste.stream_element_date';
1635 stream_type_purpose := l_rental_accrual;
1636 --Bug 4228708 - End of Changes
1637 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN
1638 OKL_STREAMS_UTIL.get_primary_stream_type
1639 (
1640 p_khr_id => l_contracts_csr.khr_id,
1641 p_primary_sty_purpose => l_investor_pre_tax_income,
1642 x_return_status => l_return_status,
1643 x_primary_sty_id => l_sty_id
1644 );
1645
1646 IF l_return_status <> 'S' THEN
1647 okl_api.set_message(p_app_name => g_app_name,
1648 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1649 p_token1 => 'STREAM_NAME',
1650 p_token1_value => l_investor_pre_tax_income);
1651 RAISE okl_api.g_exception_error;
1652 END IF;
1653
1654 -- calculate total revenue share
1655 FOR x IN get_investors_csr(p_khr_id) LOOP
1656 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1657 l_revenue_share := l_revenue_share + y.percent_stake;
1658 END LOOP;
1659 END LOOP;
1660
1661 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1662 -- store SQL error message on message stack for caller
1663 Okl_Api.set_message(p_app_name => g_app_name,
1664 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1665 RAISE Okl_Api.G_EXCEPTION_ERROR;
1666 END IF;
1667 stream_type_purpose := l_pre_tax_income;
1668 /* ankushar , 25-01-2008 Bug 6773285
1669 End Changes
1670 */
1671 /* ankushar , 16-01-2008 Bug 6740000
1672 Added condition for fetching stream type for a Loan product
1673 Start Changes
1674 */
1675 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
1676 OKL_STREAMS_UTIL.get_primary_stream_type
1677 (
1678 p_khr_id => l_contracts_csr.khr_id,
1679 p_primary_sty_purpose => l_inv_interest_income_accrual,
1680 x_return_status => l_return_status,
1681 x_primary_sty_id => l_sty_id
1682 );
1683 IF l_return_status <> 'S' THEN
1684 okl_api.set_message(p_app_name => g_app_name,
1685 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1686 p_token1 => 'STREAM_NAME',
1687 p_token1_value => l_inv_interest_income_accrual);
1688 RAISE okl_api.g_exception_error;
1689 END IF;
1690
1691 -- calculate total revenue share
1692 FOR x IN get_investors_csr(p_khr_id) LOOP
1693 FOR y IN get_revenue_share_csr(x.id, 'LOAN_PAYMENT') LOOP
1694 l_revenue_share := l_revenue_share + y.percent_stake;
1695 END LOOP;
1696 END LOOP;
1697
1698 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1699 -- store SQL error message on message stack for caller
1700 Okl_Api.set_message(p_app_name => g_app_name,
1701 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1702 RAISE Okl_Api.G_EXCEPTION_ERROR;
1703 END IF;
1704
1705 stream_type_purpose := l_interest_income;
1706 /* ankushar , 16-01-2008 Bug 6740000
1707 End Changes
1708 */
1709 END IF;
1710 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
1711 -- populate stream header record
1712 l_stmv_rec.sty_id := l_sty_id;
1713 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
1714 l_stmv_rec.sgn_code := 'MANL';
1715 l_stmv_rec.say_code := 'CURR';
1716 l_stmv_rec.transaction_number := l_trx_number;
1717 l_stmv_rec.active_yn := 'Y';
1718 l_stmv_rec.date_current := l_sysdate;
1719 l_stmv_rec.source_id := p_khr_id;
1720 l_stmv_rec.source_table := 'OKL_K_HEADERS';
1721 -- create final l_stmt
1722 --l_stmt := l_stmt || l_where;
1723 --OPEN strm_csr FOR l_stmt;
1724 -- use of a parameterized cursor by zrehman on 12-Sep-2006
1725 OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
1726 LOOP
1727 --re-initialize period end date
1728 l_period_end_date := NULL;
1729 FETCH strm_csr INTO l_elements;
1730 EXIT WHEN strm_csr%NOTFOUND;
1731 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
1732 --populate stream elements tbl
1733 -- manipulate first record
1734 IF strm_csr%ROWCOUNT = 1 THEN
1735 -- If start date is last day of the month, do nothing.
1736 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
1737 -- If start date is the same as first day of the month then take whole amount.
1738 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
1739 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
1740 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1741 l_selv_tbl(l_counter).se_line_number := l_line_number;
1742 l_line_number := l_line_number + 1;
1743 l_counter := l_counter + 1;
1744 ELSE
1745 -- start date is not first or last day of the month. so prorate.
1746 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
1747 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
1748 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1749 l_selv_tbl(l_counter).se_line_number := l_line_number;
1750 l_line_number := l_line_number + 1;
1751 l_counter := l_counter + 1;
1752 END IF;
1753 END IF;
1754 ELSE
1755 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
1756 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
1757 l_selv_tbl(l_counter).se_line_number := l_line_number;
1758 l_line_number := l_line_number + 1;
1759 l_counter := l_counter + 1;
1760 END IF;
1761 END LOOP;
1762 CLOSE strm_csr;
1763 IF l_selv_tbl.COUNT > 0 THEN
1764 -- call streams api
1765 OKL_STREAMS_PUB.create_streams(
1766 p_api_version => l_api_version
1767 ,p_init_msg_list => l_init_msg_list
1768 ,x_return_status => l_return_status
1769 ,x_msg_count => l_msg_count
1770 ,x_msg_data => l_msg_data
1771 ,p_stmv_rec => l_stmv_rec
1772 ,p_selv_tbl => l_selv_tbl
1773 ,x_stmv_rec => x_stmv_rec
1774 ,x_selv_tbl => x_selv_tbl );
1775 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1776 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1777 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1778 RAISE Okl_Api.G_EXCEPTION_ERROR;
1779 END IF;
1780 END IF;
1781 EXCEPTION
1782 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1783 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1784 ,g_pkg_name
1785 ,'OKL_API.G_RET_STS_ERROR'
1786 ,x_msg_count
1787 ,x_msg_data
1788 ,'_PVT');
1789 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1790 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1791 ,g_pkg_name
1792 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1793 ,x_msg_count
1794 ,x_msg_data
1795 ,'_PVT');
1796 WHEN OTHERS THEN
1797 IF get_kle_id_csr%ISOPEN THEN
1798 CLOSE get_kle_id_csr;
1799 END IF;
1800 IF get_adv_arr_csr%ISOPEN THEN
1801 CLOSE get_adv_arr_csr;
1802 END IF;
1803
1804 IF get_sty_id_csr%ISOPEN THEN
1805 CLOSE get_sty_id_csr;
1806 END IF;
1807
1808 IF strm_csr%ISOPEN THEN
1809 CLOSE strm_csr;
1810 END IF;
1811 l_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
1812 G_PKG_NAME,
1813 'OTHERS',
1814 x_msg_count,
1815 x_msg_data,
1816 '_PVT');
1817 END;
1818 END LOOP;
1819 CLOSE securitized_contracts_pend_csr;
1820
1821 END IF;
1822 END IF;
1823 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1824 x_msg_data => x_msg_data);
1825 x_return_status := l_return_status;
1826
1827 EXCEPTION
1828 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1829 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1830 ,g_pkg_name
1831 ,'OKL_API.G_RET_STS_ERROR'
1832 ,x_msg_count
1833 ,x_msg_data
1834 ,'_PVT');
1835 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1836 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1837 ,g_pkg_name
1838 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1839 ,x_msg_count
1840 ,x_msg_data
1841 ,'_PVT');
1842 WHEN OTHERS THEN
1843 IF securitized_contracts_csr%ISOPEN THEN
1844 CLOSE securitized_contracts_csr;
1845 END IF;
1846 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
1847 G_PKG_NAME,
1848 'OTHERS',
1849 x_msg_count,
1850 x_msg_data,
1851 '_PVT');
1852
1853 END CREATE_STREAMS;
1854
1855 -- procedure to cancel accrual securitization streams for LEASE contracts.
1856 -- this procedure is being updated. Instead of deleting stream elements physically
1857 -- accrued_yn flag will be updated to N. Generate accruals picks only those amounts
1858 -- which are marked as NULL.
1859 PROCEDURE CANCEL_STREAMS(p_api_version IN NUMBER,
1860 p_init_msg_list IN VARCHAR2,
1861 x_return_status OUT NOCOPY VARCHAR2,
1862 x_msg_count OUT NOCOPY NUMBER,
1863 x_msg_data OUT NOCOPY VARCHAR2,
1864 p_khr_id IN NUMBER,
1865 p_cancel_date IN DATE) IS
1866
1867 l_api_version CONSTANT NUMBER := 1.0;
1868 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_STREAMS';
1869 /*
1870 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
1871 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
1872 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
1873 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
1874 */
1875 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
1876 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
1877 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
1878 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
1879 /* ankushar , 16-01-2008 Bug 6691554
1880 Added new Stream Type purpose for a Loan product
1881 */
1882 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
1883
1884 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
1885 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1886 l_msg_count NUMBER;
1887 l_msg_data VARCHAR2(2000);
1888 l_deal_type VARCHAR2(2000);
1889 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1890 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1891
1892 -- cursor to get deal type of lease contract.
1893 CURSOR get_deal_type_csr(p_khr_id NUMBER) IS
1894 SELECT deal_type
1895 FROM OKL_K_HEADERS
1896 WHERE id = p_khr_id;
1897
1898 CURSOR non_accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE) IS
1899 SELECT ste.id
1900 FROM OKL_STRM_TYPE_B sty,
1901 OKL_STREAMS stm,
1902 OKL_STRM_ELEMENTS ste
1903 WHERE stm.khr_id = p_khr_id
1904 AND stm.sty_id = sty.id
1905 --AND sty.code = p_sty_code
1906 AND sty.stream_type_purpose = p_sty_code
1907 AND stm.id = ste.stm_id
1908 AND stm.active_yn ='Y'
1909 AND stm.say_code= 'CURR'
1910 AND ste.stream_element_date >= p_date
1911 AND ste.accrued_yn IS NULL;
1912
1913 CURSOR accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE) IS
1914 SELECT ste.id
1915 FROM OKL_STRM_TYPE_B sty,
1916 OKL_STREAMS stm,
1917 OKL_STRM_ELEMENTS ste
1918 WHERE stm.khr_id = p_khr_id
1919 AND stm.sty_id = sty.id
1920 --AND sty.code = p_sty_code
1921 AND sty.stream_type_purpose = p_sty_code
1922 AND stm.id = ste.stm_id
1923 AND stm.active_yn ='Y'
1924 AND stm.say_code= 'CURR'
1925 AND ste.stream_element_date >= p_date
1926 AND ste.accrued_yn IS NULL;
1927
1928 BEGIN
1929
1930 -- Set save point
1931 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
1932 p_pkg_name => G_PKG_NAME,
1933 p_init_msg_list => p_init_msg_list,
1934 l_api_version => l_api_version,
1935 p_api_version => p_api_version,
1936 p_api_type => '_PVT',
1937 x_return_status => l_return_status);
1938
1939 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1940 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1941 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1942 RAISE Okl_Api.G_EXCEPTION_ERROR;
1943 END IF;
1944
1945 -- validate in parameters
1946 IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
1947 -- store SQL error message on message stack for caller
1948 Okl_Api.set_message(p_app_name => g_app_name,
1949 p_msg_name => 'OKL_ASC_KHR_ID_ERROR');
1950 RAISE Okl_Api.G_EXCEPTION_ERROR;
1951 END IF;
1952
1953 IF p_cancel_date IS NULL OR p_cancel_date = OKL_API.G_MISS_DATE THEN
1954 -- store SQL error message on message stack for caller
1955 Okl_Api.set_message(p_app_name => g_app_name,
1956 p_msg_name => 'OKL_ASC_CANCEL_DATE_ERROR');
1957 RAISE Okl_Api.G_EXCEPTION_ERROR;
1958 END IF;
1959
1960 -- get deal type
1961 OPEN get_deal_type_csr(p_khr_id);
1962 FETCH get_deal_type_csr INTO l_deal_type;
1963 CLOSE get_deal_type_csr;
1964
1965 -- get non accrued stream elements based on deal type for deletion.
1966 IF l_deal_type = 'LEASEOP' THEN
1967
1968 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_rental_accrual, p_cancel_date)
1969 LOOP
1970 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
1971 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
1972 END LOOP;
1973 ELSIF l_deal_type IN ('LEASEDF', 'LEASEST') THEN
1974
1975 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_pre_tax_income, p_cancel_date)
1976 LOOP
1977 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
1978 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
1979 END LOOP;
1980 /* ankushar , 16-01-2008 Bug 6691554
1981 Added condition for fetching stream type for a Loan product
1982 Start Changes
1983 */
1984 ELSIF l_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
1985
1986 FOR x IN non_accrued_streams_csr(p_khr_id, l_inv_interest_income_accrual, p_cancel_date)
1987 LOOP
1988 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
1989 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
1990 END LOOP;
1991 /* ankushar , 16-01-2008 Bug 6691554
1992 End Changes
1993 */
1994
1995 END IF;
1996
1997 -- call delete stream elements API.
1998 IF l_selv_tbl.COUNT > 0 THEN
1999
2000 OKL_STREAMS_PUB.update_stream_elements(
2001 p_api_version => l_api_version
2002 ,p_init_msg_list => l_init_msg_list
2003 ,x_return_status => l_return_status
2004 ,x_msg_count => l_msg_count
2005 ,x_msg_data => l_msg_data
2006 ,p_selv_tbl => l_selv_tbl
2007 ,x_selv_tbl => x_selv_tbl);
2008 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2009 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2010 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2011 RAISE Okl_Api.G_EXCEPTION_ERROR;
2012 END IF;
2013
2014 END IF;
2015
2016 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2017 x_msg_data => x_msg_data);
2018 x_return_status := l_return_status;
2019
2020 EXCEPTION
2021 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2022 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2023 ,g_pkg_name
2024 ,'OKL_API.G_RET_STS_ERROR'
2025 ,x_msg_count
2026 ,x_msg_data
2027 ,'_PVT');
2028 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2029 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2030 ,g_pkg_name
2031 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2032 ,x_msg_count
2033 ,x_msg_data
2034 ,'_PVT');
2035 WHEN OTHERS THEN
2036 IF get_deal_type_csr%ISOPEN THEN
2037 CLOSE get_deal_type_csr;
2038 END IF;
2039 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2040 (l_api_name,
2041 G_PKG_NAME,
2042 'OTHERS',
2043 x_msg_count,
2044 x_msg_data,
2045 '_PVT');
2046
2047 END CANCEL_STREAMS;
2048 /*Commented as T_A requirement has changed
2049 PROCEDURE Create_Adjustment_Streams(
2050 p_api_version IN NUMBER
2051 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2052 ,x_return_status OUT NOCOPY VARCHAR2
2053 ,x_msg_count OUT NOCOPY NUMBER
2054 ,x_msg_data OUT NOCOPY VARCHAR2
2055 ,p_contract_id IN NUMBER
2056 ,p_line_id_tbl IN p_line_id_tbl_type
2057 ,p_adjustment_date IN DATE) IS
2058 */
2059 PROCEDURE Get_Accrual_Adjustment(
2060 p_api_version IN NUMBER
2061 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2062 ,x_return_status OUT NOCOPY VARCHAR2
2063 ,x_msg_count OUT NOCOPY NUMBER
2064 ,x_msg_data OUT NOCOPY VARCHAR2
2065 ,p_contract_id IN NUMBER
2066 ,p_line_id_tbl IN p_line_id_tbl_type
2067 ,p_adjustment_date IN DATE
2068 ,x_accrual_adjustment_tbl OUT NOCOPY p_accrual_adjustment_tbl_type
2069 ,p_product_id IN NUMBER DEFAULT NULL) IS -- MGAAP
2070
2071 /*
2072 CURSOR l_line_rec_csr(chrid NUMBER, lnetype VARCHAR2)
2073 IS
2074 SELECT kle.id,
2075 kle.amount,
2076 kle.start_date,
2077 kle.end_date,
2078 kle.fee_type,
2079 kle.initial_direct_cost,
2080 tl.item_description,
2081 tl.name,
2082 sts.ste_code
2083 FROM okl_k_lines_full_v kle,
2084 okc_line_styles_b lse,
2085 okc_k_lines_tl tl,
2086 okc_statuses_b sts
2087 WHERE kle.lse_id = lse.id
2088 AND lse.lty_code = lnetype
2089 AND tl.id = kle.id
2090 AND tl.language = userenv('LANG')
2091 AND kle.dnz_chr_id = chrid
2092 AND sts.code = kle.sts_code
2093 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
2094 */
2095 CURSOR l_line_rec_csr(chrid NUMBER, kleid NUMBER)
2096 IS
2097 SELECT
2098 kle.amount,
2099 kle.start_date,
2100 kle.end_date,
2101 kle.fee_type,
2102 kle.initial_direct_cost,
2103 tl.item_description,
2104 tl.name,
2105 sts.ste_code,
2106 lse.lty_code
2107 FROM okl_k_lines_full_v kle,
2108 okc_line_styles_b lse,
2109 okc_k_lines_tl tl,
2110 okc_statuses_b sts
2111 WHERE kle.lse_id = lse.id
2112 AND tl.id = kle.id
2113 AND tl.language = userenv('LANG')
2114 AND kle.dnz_chr_id = chrid
2115 AND kle.id = kleid
2116 AND sts.code = kle.sts_code
2117 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
2118
2119 CURSOR link_rollover_csr(cleId NUMBER)
2120 IS
2121 SELECT okc.id id,
2122 okc.chr_id chr_id,
2123 okc.cle_id cle_id,
2124 okc.dnz_chr_id dnz_chr_id,
2125 kle.capital_amount capital_amount,
2126 kle.amount amount,
2127 lse.lty_code lty_code
2128 FROM okc_k_lines_b okc,
2129 okl_k_lines kle ,
2130 okc_line_styles_b lse
2131 WHERE okc.cle_id = cleId
2132 AND okc.lse_id = lse.id
2133 AND okc.id = kle.id
2134 AND lty_code = 'LINK_FEE_ASSET';
2135
2136 CURSOR l_strm_for_line_csr(chrid NUMBER, kleid NUMBER)
2137 IS
2138 SELECT
2139 str.sty_id sty_id
2140 FROM okl_streams str,
2141 okl_strm_type_b sty
2142 WHERE str.sty_id = sty.id
2143 AND str.say_code = 'CURR'
2144 AND str.khr_id = chrid
2145 AND str.kle_id = kleid;
2146
2147
2148 CURSOR l_accrued_amt_csr(chrid NUMBER, kleid NUMBER,tadate DATE, strmPurpose VARCHAR2)
2149 IS
2150 SELECT
2151 sum(ste.amount) amount,
2152 str.sty_id sty_id
2153 --FROM okl_streams str,
2154 FROM okl_streams_rep_v str, -- MGAAP 7263041
2155 okl_strm_elements ste,
2156 okl_strm_type_b sty
2157 WHERE ste.stm_id = str.id
2158 AND str.sty_id = sty.id
2159 AND str.say_code = 'CURR'
2160 AND str.khr_id = chrid
2161 AND str.kle_id = kleid
2162 AND ste.stream_element_date <= last_day(tadate)
2163 AND sty.stream_type_purpose = strmPurpose
2164 GROUP BY str.sty_id;
2165
2166 CURSOR l_accrued_amt_sty_csr(chrid NUMBER, kleid NUMBER,tadate DATE, styid NUMBER)
2167 IS
2168 SELECT
2169 sum(ste.amount) amount,
2170 str.sty_id sty_id
2171 --FROM okl_streams str,
2172 FROM okl_streams_rep_v str, -- MGAAP 7263041
2173 okl_strm_elements ste,
2174 okl_strm_type_b sty
2175 WHERE ste.stm_id = str.id
2176 AND str.sty_id = sty.id
2177 AND str.say_code = 'CURR'
2178 AND str.khr_id = chrid
2179 AND str.kle_id = kleid
2180 AND ste.stream_element_date <= last_day(tadate)
2181 AND sty.id = styid
2182 GROUP BY str.sty_id;
2183
2184
2185 CURSOR l_bill_amt_csr(chrid NUMBER, kleid NUMBER,tadate DATE, strmPurpose VARCHAR2)
2186 IS
2187 SELECT
2188 sum(ste.amount)
2189 --FROM okl_streams str,
2190 FROM okl_streams_rep_v str, -- MGAAP 7263041
2191 okl_strm_elements ste,
2192 okl_strm_type_b sty
2193 WHERE ste.stm_id = str.id
2194 AND str.sty_id = sty.id
2195 AND str.say_code = 'CURR'
2196 AND str.khr_id = chrid
2197 AND str.kle_id = kleid
2198 AND ste.stream_element_date <= tadate
2199 AND sty.stream_type_purpose = strmPurpose;
2200
2201 CURSOR l_bill_pmt_sty_csr(chrid NUMBER, kleid NUMBER, styid NUMBER, tadate DATE)
2202 IS
2203 SELECT
2204 sum(ste.amount)
2205 --FROM okl_streams str,
2206 FROM okl_streams_rep_v str, -- MGAAP 7263041
2207 okl_strm_elements ste,
2208 okl_strm_type_b sty
2209 WHERE ste.stm_id = str.id
2210 AND str.sty_id = sty.id
2211 AND str.say_code = 'CURR'
2212 AND str.khr_id = chrid
2213 AND str.kle_id = kleid
2214 AND sty.id = styid
2215 AND ste.stream_element_date <= tadate;
2216
2217 CURSOR l_pmt_sty_csr(rgcode okc_rule_groups_b.rgd_code%TYPE,
2218 rlcat okc_rules_b.rule_information_category%TYPE,
2219 chrId NUMBER,
2220 cleId NUMBER)
2221 IS
2222 SELECT crl.id slh_id,
2223 crl.object1_id1
2224 FROM okc_rule_groups_b crg,
2225 okc_rules_b crl
2226 WHERE crl.rgp_id = crg.id
2227 AND crg.rgd_code = rgcode
2228 AND crl.rule_information_category = rlcat
2229 AND crg.dnz_chr_id = chrId
2230 AND crg.cle_id = cleId
2231 ORDER BY crl.rule_information1;
2232
2233 CURSOR l_rl_csr2(rgcode okc_rule_groups_b.rgd_code%TYPE,
2234 rlcat okc_rules_b.rule_information_category%TYPE,
2235 chrId NUMBER,
2236 cleId NUMBER)
2237 IS
2238 SELECT crl.id slh_id,
2239 crl.object1_id1,
2240 crl.rule_information1,
2241 crl.rule_information2,
2242 crl.rule_information3,
2243 crl.rule_information5,
2244 crl.rule_information6,
2245 crl.rule_information7,
2246 crl.rule_information8,
2247 crl.rule_information13,
2248 crl.rule_information10
2249 FROM okc_rule_groups_b crg,
2250 okc_rules_b crl
2251 WHERE crl.rgp_id = crg.id
2252 AND crg.rgd_code = rgcode
2253 AND crl.rule_information_category = rlcat
2254 AND crg.dnz_chr_id = chrId
2255 AND crg.cle_id = cleId
2256 ORDER BY crl.rule_information1;
2257
2258 CURSOR l_pdt_accrual_csr(chrId NUMBER)
2259 IS
2260 SELECT sty.id sty_id
2261 FROM OKL_STRM_TYPE_B sty,
2262 OKL_PROD_STRM_TYPES psty,
2263 OKL_K_HEADERS khr
2264 WHERE khr.id = chrId
2265 --AND khr.pdt_id = psty.pdt_id
2266 AND psty.pdt_id = NVL(p_product_id, khr.pdt_id) -- MGAAP 7263041
2267 AND psty.sty_id = sty.id
2268 AND psty.accrual_yn = 'Y';
2269
2270 l_accrual_amt l_accrued_amt_csr%ROWTYPE;
2271 l_strm_for_line_rec l_strm_for_line_csr%ROWTYPE;
2272 l_pdt_accrual_rec l_pdt_accrual_csr%ROWTYPE;
2273 l_line_rec l_line_rec_csr%ROWTYPE;
2274 l_rl_rec2 l_rl_csr2%ROWTYPE;
2275 l_pmt_sty_rec l_pmt_sty_csr%ROWTYPE;
2276
2277
2278 TYPE p_pdt_accrual_rec_type IS RECORD(
2279 sty_id OKL_STRM_TYPE_B.ID%TYPE);
2280
2281 TYPE p_pdt_accrual_tbl_type IS TABLE OF p_pdt_accrual_rec_type
2282 INDEX BY BINARY_INTEGER;
2283
2284 l_pdt_accrual_tbl p_pdt_accrual_tbl_type;
2285 l_strm_for_line_tbl p_pdt_accrual_tbl_type;
2286 x_strm_for_line_tbl p_pdt_accrual_tbl_type;
2287
2288 m BINARY_INTEGER := 0;
2289 n BINARY_INTEGER := 0;
2290 i BINARY_INTEGER := 0;
2291 j BINARY_INTEGER := 0;
2292 l_strm_exist VARCHAR2(1);
2293 l_passthrough_percent NUMBER := 0;
2294 l_pdt_accrual_sty NUMBER := 0;
2295 l_fee_or_service VARCHAR2(50);
2296 l_fee_type VARCHAR2(100);
2297 l_adjustment_amt NUMBER := 0;
2298 l_bill_amt NUMBER := 0;
2299 l_idc_bill_amt NUMBER := 0;
2300 l_exp_bill_amt NUMBER := 0;
2301
2302 l_api_version CONSTANT NUMBER := 1.0;
2303 l_api_name CONSTANT VARCHAR2(30) := 'GET_ACCRUAL_ADJUSTMENT';
2304 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
2305 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2306 l_msg_count NUMBER;
2307 l_msg_data VARCHAR2(2000);
2308
2309
2310
2311 BEGIN
2312 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2313 -- Call start_activity to create savepoint, check compatibility
2314 -- and initialize message list
2315 x_return_status := OKL_API.START_ACTIVITY (
2316 l_api_name
2317 ,p_init_msg_list
2318 ,'_PVT'
2319 ,x_return_status);
2320 -- Check if activity started successfully
2321 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2322 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2323 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2324 RAISE OKL_API.G_EXCEPTION_ERROR;
2325 END IF;
2326
2327 -- Get the stream marked for accrual at product level
2328 FOR l_pdt_accrual_rec IN l_pdt_accrual_csr(p_contract_id) LOOP
2329 i := i + 1;
2330 l_pdt_accrual_tbl(i).sty_id := l_pdt_accrual_rec.sty_id;
2331 END LOOP;
2332
2333
2334 IF p_line_id_tbl.COUNT > 0 THEN
2335 FOR i IN p_line_id_tbl.FIRST..p_line_id_tbl.LAST LOOP
2336 l_strm_exist := 'N';
2337 OPEN l_line_rec_csr(p_contract_id , p_line_id_tbl(i).id);
2338 FETCH l_line_rec_csr INTO l_line_rec;
2339 l_fee_or_service := l_line_rec.lty_code;
2340 l_fee_type := l_line_rec.fee_type;
2341 IF (l_fee_or_service = 'FEE') THEN
2342 -- Get all the streams generated for a particular fee line
2343 j := 0;
2344 FOR l_strm_for_line_rec IN l_strm_for_line_csr(p_contract_id,p_line_id_tbl(i).id) LOOP
2345 j := j + 1;
2346 l_strm_for_line_tbl(j).sty_id := l_strm_for_line_rec.sty_id;
2347 END LOOP;
2348 -- Get those stream which has been generated for a line and marked for accrual at product
2349 IF l_pdt_accrual_tbl.COUNT > 0 THEN
2350 IF l_strm_for_line_tbl.COUNT > 0 THEN
2351 FOR k IN l_strm_for_line_tbl.FIRST..l_strm_for_line_tbl.LAST LOOP
2352 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2353 IF l_strm_for_line_tbl(k).sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2354 n := n + 1;
2355 x_strm_for_line_tbl(n).sty_id := l_strm_for_line_tbl(k).sty_id;
2356
2357 END IF;
2358 END LOOP;
2359 END LOOP;
2360 END IF;
2361 END IF;
2362 IF x_strm_for_line_tbl.COUNT > 0 THEN
2363 IF (l_fee_type = 'FINANCED') THEN
2364
2365 FOR p IN x_strm_for_line_tbl.FIRST..x_strm_for_line_tbl.LAST LOOP
2366
2367 OPEN l_accrued_amt_sty_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, x_strm_for_line_tbl(p).sty_id);
2368 FETCH l_accrued_amt_sty_csr INTO l_accrual_amt;
2369 CLOSE l_accrued_amt_sty_csr;
2370 /*
2371 OPEN l_accrued_amt_csr(chrid, p_line_id_tbl(i).id, tadate, 'LEASE_INCOME');
2372 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2373 CLOSE l_accrued_amt_csr;
2374 */
2375 OPEN l_bill_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'INTEREST_PAYMENT');
2376 FETCH l_bill_amt_csr INTO l_bill_amt;
2377 CLOSE l_bill_amt_csr;
2378 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2379
2380 m:= m + 1;
2381 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2382 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2383 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2384 END LOOP;
2385 ELSIF (l_fee_type = 'ROLLOVER') THEN
2386 FOR p IN x_strm_for_line_tbl.FIRST..x_strm_for_line_tbl.LAST LOOP
2387
2388 OPEN l_accrued_amt_sty_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, x_strm_for_line_tbl(p).sty_id);
2389 FETCH l_accrued_amt_sty_csr INTO l_accrual_amt;
2390 CLOSE l_accrued_amt_sty_csr;
2391 /*
2392 OPEN l_accrued_amt_csr(chrid, p_line_id_tbl(i).id, tadate, 'LEASE_INCOME');
2393 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2394 CLOSE l_accrued_amt_csr;
2395 */
2396 OPEN l_bill_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'INTEREST_PAYMENT');
2397 FETCH l_bill_amt_csr INTO l_bill_amt;
2398 CLOSE l_bill_amt_csr;
2399 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2400
2401 m:= m + 1;
2402 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2403 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2404 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2405 END LOOP;
2406
2407 ELSIF ((l_fee_type = 'MISCELLANEOUS') OR (l_fee_type = 'EXPENSE')) THEN
2408 IF (nvl(l_line_rec.initial_direct_cost,0) > 0) THEN
2409 IF NVL(l_line_rec.amount,0) <> NVL(l_line_rec.initial_direct_cost,0) THEN
2410
2411 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZED_FEE_EXPENSE');
2412 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2413 CLOSE l_accrued_amt_csr;
2414
2415 IF l_accrual_amt.sty_id IS NOT NULL THEN
2416 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2417 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2418 l_strm_exist := 'Y';
2419 END IF;
2420 END LOOP;
2421 END IF;
2422
2423 IF l_strm_exist = 'Y' THEN
2424 OKL_FUNDING_PVT.contract_fee_canbe_funded(
2425 p_api_version => l_api_version
2426 ,p_init_msg_list => l_init_msg_list
2427 ,x_return_status => l_return_status
2428 ,x_msg_count => l_msg_count
2429 ,x_msg_data => l_msg_data
2430 ,x_value => l_bill_amt
2431 ,p_contract_id => p_contract_id
2432 ,p_fee_line_id => p_line_id_tbl(i).id
2433 ,p_effective_date => p_adjustment_date
2434 );
2435
2436 l_idc_bill_amt := l_bill_amt*(l_line_rec.initial_direct_cost/l_line_rec.amount);
2437 l_exp_bill_amt := l_bill_amt - l_idc_bill_amt;
2438 l_adjustment_amt := l_accrual_amt.amount - l_idc_bill_amt;
2439
2440 m:= m + 1;
2441 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2442 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2443 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2444 END IF;
2445
2446 -- Code for expense fee
2447 l_strm_exist := 'N';
2448
2449 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_EXPENSE');
2450 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2451 CLOSE l_accrued_amt_csr;
2452
2453 IF l_accrual_amt.sty_id IS NOT NULL THEN
2454 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2455 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2456 l_strm_exist := 'Y';
2457 END IF;
2458 END LOOP;
2459 END IF;
2460
2461 IF l_strm_exist = 'Y' THEN
2462 l_adjustment_amt := l_accrual_amt.amount - l_exp_bill_amt;
2463 m:= m + 1;
2464 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2465 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2466 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2467 END IF;
2468 ELSE
2469 l_strm_exist := 'N';
2470 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZED_FEE_EXPENSE');
2471 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2472 CLOSE l_accrued_amt_csr;
2473
2474 IF l_accrual_amt.sty_id IS NOT NULL THEN
2475 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2476 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2477 l_strm_exist := 'Y';
2478 END IF;
2479 END LOOP;
2480 END IF;
2481
2482 IF l_strm_exist = 'Y' THEN
2483 OKL_FUNDING_PVT.contract_fee_canbe_funded(
2484 p_api_version => l_api_version
2485 ,p_init_msg_list => l_init_msg_list
2486 ,x_return_status => l_return_status
2487 ,x_msg_count => l_msg_count
2488 ,x_msg_data => l_msg_data
2489 ,x_value => l_bill_amt
2490 ,p_contract_id => p_contract_id
2491 ,p_fee_line_id => p_line_id_tbl(i).id
2492 ,p_effective_date => p_adjustment_date
2493 );
2494
2495 l_idc_bill_amt := l_bill_amt;
2496
2497 l_adjustment_amt := l_accrual_amt.amount - l_idc_bill_amt;
2498 m:= m + 1;
2499 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2500 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2501 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2502 END IF;
2503
2504 END IF;
2505 ELSE
2506 l_strm_exist := 'N';
2507 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_EXPENSE');
2508 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2509 CLOSE l_accrued_amt_csr;
2510
2511 IF l_accrual_amt.sty_id IS NOT NULL THEN
2512 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2513 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2514 l_strm_exist := 'Y';
2515 END IF;
2516 END LOOP;
2517 END IF;
2518
2519 IF l_strm_exist = 'Y' THEN
2520 OKL_FUNDING_PVT.contract_fee_canbe_funded(
2521 p_api_version => l_api_version
2522 ,p_init_msg_list => l_init_msg_list
2523 ,x_return_status => l_return_status
2524 ,x_msg_count => l_msg_count
2525 ,x_msg_data => l_msg_data
2526 ,x_value => l_bill_amt
2527 ,p_contract_id => p_contract_id
2528 ,p_fee_line_id => p_line_id_tbl(i).id
2529 ,p_effective_date => p_adjustment_date
2530 );
2531
2532
2533 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2534 m:= m + 1;
2535 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2536 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2537 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2538 END IF;
2539 END IF;
2540 IF (l_fee_type = 'MISCELLANEOUS') THEN
2541 l_strm_exist := 'N';
2542 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
2543 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
2544 CLOSE l_pmt_sty_csr;
2545
2546 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_INCOME');
2547 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2548 CLOSE l_accrued_amt_csr;
2549
2550 IF l_accrual_amt.sty_id IS NOT NULL THEN
2551 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2552 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2553 l_strm_exist := 'Y';
2554 END IF;
2555 END LOOP;
2556 END IF;
2557
2558 IF l_strm_exist = 'Y' THEN
2559 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
2560 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
2561 CLOSE l_bill_pmt_sty_csr;
2562 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2563 m:= m + 1;
2564 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2565 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2566 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2567 END IF;
2568 END IF;
2569
2570
2571 ELSIF (l_fee_type = 'INCOME' ) THEN
2572
2573 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
2574 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
2575 CLOSE l_pmt_sty_csr;
2576
2577 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZE_FEE_INCOME');
2578 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2579 CLOSE l_accrued_amt_csr;
2580
2581 IF l_accrual_amt.sty_id IS NOT NULL THEN
2582 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2583 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2584 l_strm_exist := 'Y';
2585 END IF;
2586 END LOOP;
2587 END IF;
2588
2589 IF l_strm_exist = 'Y' THEN
2590 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
2591 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
2592 CLOSE l_bill_pmt_sty_csr;
2593 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2594
2595 m:= m + 1;
2596 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2597 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2598 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2599 END IF;
2600
2601 ELSIF (l_fee_type = 'PASSTHROUGH' ) THEN
2602 OPEN l_rl_csr2 ( 'LAPSTH', 'LAPTPR', TO_NUMBER(p_contract_id), p_line_id_tbl(i).id );
2603 FETCH l_rl_csr2 INTO l_rl_rec2;
2604 CLOSE l_rl_csr2;
2605 l_passthrough_percent := nvl( l_rl_rec2.rule_information1, 100.0 );
2606 IF l_passthrough_percent < 100 THEN
2607
2608 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
2609 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
2610 CLOSE l_pmt_sty_csr;
2611
2612 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'PASS_THRU_REV_ACCRUAL');
2613 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2614 CLOSE l_accrued_amt_csr;
2615
2616 IF l_accrual_amt.sty_id IS NOT NULL THEN
2617 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2618 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2619 l_strm_exist := 'Y';
2620 END IF;
2621 END LOOP;
2622 END IF;
2623
2624 IF l_strm_exist = 'Y' THEN
2625
2626 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
2627 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
2628 CLOSE l_bill_pmt_sty_csr;
2629 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2630
2631 m:= m + 1;
2632 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2633 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2634 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2635 END IF;
2636
2637 l_strm_exist := 'N';
2638 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'PASS_THRU_EXP_ACCRUAL');
2639 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2640 CLOSE l_accrued_amt_csr;
2641
2642 IF l_accrual_amt.sty_id IS NOT NULL THEN
2643 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2644 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2645 l_strm_exist := 'Y';
2646 END IF;
2647 END LOOP;
2648 END IF;
2649 IF l_strm_exist = 'Y' THEN
2650 l_bill_amt := l_bill_amt * l_passthrough_percent/100 ;
2651 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2652
2653 m:= m + 1;
2654 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2655 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2656 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2657 END IF;
2658 END IF;
2659
2660
2661 END IF;
2662 --ELSIF (l_fee_type = ) THEN
2663 END IF;
2664 ELSIF (l_fee_or_service = 'SOLD_SERVICE') THEN
2665
2666 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
2667 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
2668 CLOSE l_pmt_sty_csr;
2669
2670 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'SERVICE_INCOME');
2671 FETCH l_accrued_amt_csr INTO l_accrual_amt;
2672 CLOSE l_accrued_amt_csr;
2673
2674 IF l_accrual_amt.sty_id IS NOT NULL THEN
2675 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
2676 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
2677 l_strm_exist := 'Y';
2678 END IF;
2679 END LOOP;
2680 END IF;
2681
2682 IF l_strm_exist = 'Y' THEN
2683 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
2684 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
2685 CLOSE l_bill_pmt_sty_csr;
2686 l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
2687
2688 m:= m + 1;
2689 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
2690 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
2691 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
2692 END IF;
2693
2694 END IF;
2695 CLOSE l_line_rec_csr;
2696
2697 END LOOP;
2698 END IF;
2699 --NULL;
2700 OKL_API.END_ACTIVITY (x_msg_count,
2701 x_msg_data );
2702 EXCEPTION
2703 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2704 IF l_line_rec_csr%ISOPEN THEN
2705 CLOSE l_line_rec_csr;
2706 END IF;
2707 IF l_strm_for_line_csr%ISOPEN THEN
2708 CLOSE l_strm_for_line_csr;
2709 END IF;
2710 IF l_accrued_amt_csr%ISOPEN THEN
2711 CLOSE l_accrued_amt_csr;
2712 END IF;
2713 IF l_accrued_amt_sty_csr%ISOPEN THEN
2714 CLOSE l_accrued_amt_sty_csr;
2715 END IF;
2716 IF l_bill_amt_csr%ISOPEN THEN
2717 CLOSE l_bill_amt_csr;
2718 END IF;
2719 IF l_bill_pmt_sty_csr%ISOPEN THEN
2720 CLOSE l_bill_pmt_sty_csr;
2721 END IF;
2722 IF l_pmt_sty_csr%ISOPEN THEN
2723 CLOSE l_pmt_sty_csr;
2724 END IF;
2725 IF l_rl_csr2%ISOPEN THEN
2726 CLOSE l_rl_csr2;
2727 END IF;
2728 IF l_pdt_accrual_csr%ISOPEN THEN
2729 CLOSE l_pdt_accrual_csr;
2730 END IF;
2731 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2732 l_api_name,
2733 G_PKG_NAME,
2734 'OKL_API.G_RET_STS_ERROR',
2735 x_msg_count,
2736 x_msg_data,
2737 '_PVT');
2738 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2739 IF l_line_rec_csr%ISOPEN THEN
2740 CLOSE l_line_rec_csr;
2741 END IF;
2742 IF l_strm_for_line_csr%ISOPEN THEN
2743 CLOSE l_strm_for_line_csr;
2744 END IF;
2745 IF l_accrued_amt_csr%ISOPEN THEN
2746 CLOSE l_accrued_amt_csr;
2747 END IF;
2748 IF l_accrued_amt_sty_csr%ISOPEN THEN
2749 CLOSE l_accrued_amt_sty_csr;
2750 END IF;
2751 IF l_bill_amt_csr%ISOPEN THEN
2752 CLOSE l_bill_amt_csr;
2753 END IF;
2754 IF l_bill_pmt_sty_csr%ISOPEN THEN
2755 CLOSE l_bill_pmt_sty_csr;
2756 END IF;
2757 IF l_pmt_sty_csr%ISOPEN THEN
2758 CLOSE l_pmt_sty_csr;
2759 END IF;
2760 IF l_rl_csr2%ISOPEN THEN
2761 CLOSE l_rl_csr2;
2762 END IF;
2763 IF l_pdt_accrual_csr%ISOPEN THEN
2764 CLOSE l_pdt_accrual_csr;
2765 END IF;
2766 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2767 l_api_name,
2768 G_PKG_NAME,
2769 'OKL_API.G_RET_STS_UNEXP_ERROR',
2770 x_msg_count,
2771 x_msg_data,
2772 '_PVT');
2773 WHEN OTHERS THEN
2774 IF l_line_rec_csr%ISOPEN THEN
2775 CLOSE l_line_rec_csr;
2776 END IF;
2777 IF l_strm_for_line_csr%ISOPEN THEN
2778 CLOSE l_strm_for_line_csr;
2779 END IF;
2780 IF l_accrued_amt_csr%ISOPEN THEN
2781 CLOSE l_accrued_amt_csr;
2782 END IF;
2783 IF l_accrued_amt_sty_csr%ISOPEN THEN
2784 CLOSE l_accrued_amt_sty_csr;
2785 END IF;
2786 IF l_bill_amt_csr%ISOPEN THEN
2787 CLOSE l_bill_amt_csr;
2788 END IF;
2789 IF l_bill_pmt_sty_csr%ISOPEN THEN
2790 CLOSE l_bill_pmt_sty_csr;
2791 END IF;
2792 IF l_pmt_sty_csr%ISOPEN THEN
2793 CLOSE l_pmt_sty_csr;
2794 END IF;
2795 IF l_rl_csr2%ISOPEN THEN
2796 CLOSE l_rl_csr2;
2797 END IF;
2798 IF l_pdt_accrual_csr%ISOPEN THEN
2799 CLOSE l_pdt_accrual_csr;
2800 END IF;
2801 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2802 l_api_name,
2803 G_PKG_NAME,
2804 'OTHERS',
2805 x_msg_count,
2806 x_msg_data,
2807 '_PVT');
2808
2809 END get_accrual_adjustment;
2810 END OKL_ACCRUAL_SEC_PVT;