[Home] [Help]
PACKAGE BODY: APPS.OKL_ACCRUAL_SEC_PVT
Source
1 PACKAGE BODY OKL_ACCRUAL_SEC_PVT AS
2 /* $Header: OKLRASCB.pls 120.19.12020000.4 2012/10/17 11:33:48 vloomba 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 -- sechawla 09-mar-09 MG Impact on IA : regenerate the Investor Accrual
28 -- streams upon rebook
29 -- End of Commnets
30 --------------------------------------------------------------------------------
31 PROCEDURE create_lease_streams(p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2,
36 p_khr_id IN NUMBER,
37 p_mode IN VARCHAR2 DEFAULT NULL)
38 IS
39 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LEASE_STREAMS';
40 /*
41 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
42 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
43 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
44 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
45 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENTAL ACCRUAL';
46 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'PRE-TAX INCOME';
47 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST INCOME';
48 l_variable_income CONSTANT VARCHAR2(2000) := 'VARIABLE INCOME ACCRUAL';
49 */
50 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
51 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
52 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
53 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
54 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENT_ACCRUAL';
55 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'LEASE_INCOME';
56 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST_INCOME';
57 l_variable_income CONSTANT VARCHAR2(2000) := 'ACCOUNTING';
58 /* ankushar , 16-01-2008 Bug 6740000
59 Added new Stream Type purpose for a Loan product
60 */
61 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
62
63 l_count NUMBER := 1;
64 l_period_end_date DATE;
65 l_total_records NUMBER;
66 l_sysdate DATE := TRUNC(SYSDATE);
67 l_trx_number NUMBER;
68 l_revenue_share NUMBER := 0;
69 l_sty_id NUMBER;
70 l_inv_id NUMBER;
71 l_return_status VARCHAR2(1);
72 stream_type_purpose VARCHAR2(30);
73
74 /* ankushar , 16-01-2008 Bug 6740000
75 Modified cursors to fetch based on stream type for a Loan product
76 Start Changes
77 */
78 -- cursor to select Lease contract id for a given Investor agreement
79 CURSOR securitized_contracts_csr (p_inv_id NUMBER,
80 p_khr_id NUMBER)
81 IS
82 SELECT DISTINCT opc.khr_id khr_id,
83 opc.streams_to_date end_date,
84 khr.deal_type deal_type
85 FROM okl_pool_contents opc,
86 okl_pools op,
87 okl_k_headers khr,
88 -- Changed for User Defined Streams
89 --okl_strm_type_tl stytl
90 okl_strm_type_b stytl
91 WHERE op.khr_id = p_inv_id
92 AND opc.khr_id = p_khr_id
93 AND op.id = opc.pol_id
94 AND opc.khr_id = khr.id
95 AND opc.sty_id = stytl.id
96 --AND stytl.name = 'RENT'
97 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
98 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW);
99 --AND stytl.language = USERENV('LANG');
100
101 -- cursor to get first kle_id and earliest stream element date
102 CURSOR get_kle_id_csr(p_khr_id NUMBER)
103 IS
104 SELECT opc.kle_id kle_id,
105 MIN(opc.streams_from_date) start_date
106 FROM okl_pool_contents opc,
107 okl_strm_type_b sty
108 WHERE opc.khr_id = p_khr_id
109 AND opc.sty_id = sty.id
110 --AND sty.code = 'RENT'
111 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
112 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
113 AND ROWNUM < 2
114 GROUP BY opc.kle_id;
115
116 /* sosharma ,14-12-2007
117 Bug 6691554
118 Added cursors for generating streams for transient pool
119 Start Changes*/
120
121
122 -- cursor to select Lease contract id for a given Investor agreement
123 CURSOR securitized_contracts_pend_csr (p_inv_id NUMBER,
124 p_khr_id NUMBER)
125 IS
126 SELECT DISTINCT opc.khr_id khr_id,
127 opc.streams_to_date end_date,
128 khr.deal_type deal_type
129 FROM okl_pool_contents opc,
130 okl_pools op,
131 okl_k_headers khr,
132 -- Changed for User Defined Streams
133 --okl_strm_type_tl stytl
134 okl_strm_type_b stytl
135 WHERE op.khr_id = p_inv_id
136 AND opc.khr_id = p_khr_id
137 AND op.id = opc.pol_id
138 AND opc.khr_id = khr.id
139 AND opc.sty_id = stytl.id
140 --AND stytl.name = 'RENT'
141 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
142 AND opc.status_code = G_PC_STS_PENDING; --Okl_Pool_Pvt.G_POC_STS_PENDING;
143 --AND stytl.language = USERENV('LANG');
144
145 -- cursor to get first kle_id and earliest stream element date
146
147 CURSOR get_kle_id_pend_csr(p_khr_id NUMBER)
148 IS
149 SELECT opc.kle_id kle_id,
150 MIN(opc.streams_from_date) start_date
151 FROM okl_pool_contents opc,
152 okl_strm_type_b sty
153 WHERE opc.khr_id = p_khr_id
154 AND opc.sty_id = sty.id
155 --AND sty.code = 'RENT'
156 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
157 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
158 AND ROWNUM < 2
159 GROUP BY opc.kle_id;
160
161 CURSOR get_inv_pend_csr (p_khr_id NUMBER)
162 IS
163 SELECT DISTINCT op.khr_id
164 FROM okl_pool_contents opc,
165 okl_pools op,
166 okl_strm_type_b sty
167 WHERE op.id = opc.pol_id
168 AND opc.khr_id = p_khr_id
169 AND opc.sty_id = sty.id
170 --AND sty.code = 'RENT';
171 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
172 AND opc.status_code = G_PC_STS_PENDING;--Okl_Pool_Pvt.G_POC_STS_PENDING ;
173
174 /* sosharma end changes*/
175
176 -- cursor to get advance/arrears and frequency for rent stream type
177 CURSOR get_adv_arr_csr(p_khr_id NUMBER,
178 p_kle_id NUMBER)
179 IS
180 SELECT DECODE(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
181 DECODE(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
182 FROM okc_k_headers_b k,
183 okc_rule_groups_b rg,
184 okc_rules_b slh,
185 okc_rules_b sll,
186 okl_strm_type_b strm
187 WHERE slh.rule_information_category = 'LASLH'
188 AND slh.rgp_id = rg.id
189 AND sll.object2_id1 = to_char(slh.id)
190 AND sll.rgp_id = rg.id
191 AND slh.object1_id1 = to_char(strm.id)
192 --AND strm.code = 'RENT'
193 AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
194 AND rg.dnz_chr_id = k.id
195 AND rg.chr_id IS NULL
196 AND rg.rgd_code = 'LALEVL'
197 AND rg.cle_id = p_kle_id
198 AND k.id = p_khr_id;
199 -- cursor to get stream type id
200 CURSOR get_sty_id_csr (p_sty_name VARCHAR2)
201 IS
202 SELECT id
203 FROM okl_strm_type_tl
204 WHERE name = p_sty_name
205 AND language = USERENV('LANG');
206
207 /* ankushar , 25-01-2008 Bug 6773285
208 Modified Cursor to add parameter for Stream type subclass
209 Start Changes
210 */
211 -- the revenue shares for the investor
212 CURSOR get_revenue_share_csr(p_inv_id NUMBER, p_stream_type_subclass VARCHAR2)
213 IS
214 SELECT kleb_rv.percent_stake percent_stake
215 FROM okl_k_lines kleb_rv,
216 okc_k_lines_b cles_rv,
217 okc_line_styles_b lseb_rv
218 WHERE
219 cles_rv.dnz_chr_id = p_inv_id
220 AND cles_rv.lse_id = lseb_rv.id
221 AND lseb_rv.lty_code = 'REVENUE_SHARE'
222 AND kleb_rv.id = cles_rv.id
223 AND kleb_rv.stream_type_subclass = p_stream_type_subclass;
224 /* ankushar , 25-01-2008 Bug 6773285
225 End Changes
226 */
227 -- cursor to get contract number
228 CURSOR contract_number_csr (p_khr_id NUMBER)
229 IS
230 SELECT contract_number
231 FROM okc_k_headers_b
232 WHERE id = p_khr_id
233 AND scs_code = 'LEASE';
234 -- cursor to get investor Agreement contract id
235 -- for a given Lease contract id
236 CURSOR get_inv_csr (p_khr_id NUMBER)
237 IS
238 SELECT DISTINCT op.khr_id
239 FROM okl_pool_contents opc,
240 okl_pools op,
241 okl_strm_type_b sty
242 WHERE op.id = opc.pol_id
243 AND opc.khr_id = p_khr_id
244 AND opc.sty_id = sty.id
245 --AND sty.code = 'RENT';
246 AND sty.stream_type_purpose = 'RENT'
247 AND opc.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE ; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
248
249
250 l_contracts_csr securitized_contracts_csr%ROWTYPE;
251 CURSOR stream_id_csr (p_khr_id NUMBER,
252 p_sty_id NUMBER,
253 p_say_code VARCHAR2,
254 p_purpose_code VARCHAR2)--sechawla 9-mar-09 MG Impact on IA : added
255 IS
256 select id from okl_streams
257 where khr_id=p_khr_id
258 and sty_id=p_sty_id
259 and say_code=p_say_code
260 and nvl(purpose_code,'XXX') = nvl(p_purpose_code,'XXX') ; --sechawla 9-mar-09 MG Impact on IA : added
261
262
263 l_id_tbl okl_streams_util.NumberTabTyp;
264 j NUMBER := 0;
265
266 -- declaration of a parameterized cursor by zrehman on 12-Sep-2006
267 --sechawla 9-mar-09 MG Impact on IA prefixed curosr parameters with cp, to avoid conflict with column names
268 --cursor was not picking any data because old parameter name khr_id was conflicting with column name khr_id
269 /*
270 CURSOR strm_csr (cp_khr_id NUMBER,
271 cp_final_start_date DATE,
272 cp_end_date DATE,
273 cp_stream_type_purpose VARCHAR2)
274 IS
275 select ste.stream_element_date stream_element_date,ste.amount amount
276 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
277 WHERE
278 stm.sty_id = sty.id
279 AND ste.stm_id = stm.id
280 AND stm.active_yn = 'Y'
281 AND stm.say_code = 'CURR'
282 AND stm.khr_id = cp_khr_id
283 AND ste.stream_element_date BETWEEN cp_final_start_date AND cp_end_date
284 AND sty.stream_type_purpose = cp_stream_type_purpose
285 ORDER BY ste.stream_element_date;
286 */
287
288 -- vsgandhi
289 -- Bug 13428672: 29-Mar-2012
290
291 CURSOR strm_csr (cp_khr_id NUMBER,
292 p_sty_id NUMBER,
293 cp_stream_type_purpose VARCHAR2 )
294 IS
295 select ste.stream_element_date stream_element_date,ste.amount amount
296 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
297
298 WHERE
299 stm.sty_id = sty.id
300 AND ste.stm_id = stm.id
301 AND stm.active_yn = 'Y'
302 AND stm.say_code = 'CURR'
303 AND stm.khr_id = cp_khr_id
304 AND sty.stream_type_purpose = cp_stream_type_purpose
305 and exists (
306 select '1'
307 FROM okl_strm_type_b sty1, okl_streams stm1, okl_strm_elements ste1
308 where stm1.sty_id = sty1.id
309 AND ste1.stm_id = stm1.id
310 AND stm1.active_yn = 'Y'
311 AND stm1.say_code = 'CURR'
312 AND stm1.khr_id = cp_khr_id
313 and sty1.id = p_sty_id
314 and ste1.stream_element_date = ste.stream_element_date
315 )
316 ORDER BY ste.stream_element_date;
317
318
319 --sechawla 9-mar-09 MG Impact on IA
320 -- Get secondary_rep_method
321 /*
322 CURSOR rep_strm_csr (cp_khr_id NUMBER,
323 cp_final_start_date DATE,
324 cp_end_date DATE,
325 cp_stream_type_purpose VARCHAR2)
326 IS
327 select ste.stream_element_date stream_element_date,ste.amount amount
328 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
329 WHERE
330 stm.sty_id = sty.id
331 AND ste.stm_id = stm.id
332 AND stm.active_yn = 'N'
333 AND stm.say_code = 'CURR'
334 AND stm.purpose_code = 'REPORT'
335 AND stm.khr_id = cp_khr_id
336 AND ste.stream_element_date BETWEEN cp_final_start_date AND cp_end_date
337 AND sty.stream_type_purpose = cp_stream_type_purpose
338 ORDER BY ste.stream_element_date;
339 */
340
341 -- vsgandhi
342 -- Bug 13428672: 29-Mar-2012
343 CURSOR rep_strm_csr (cp_khr_id NUMBER,
344 p_rep_sty_id NUMBER,
345 cp_stream_type_purpose VARCHAR2)
346 IS
347 select ste.stream_element_date stream_element_date,ste.amount amount
348 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
349 WHERE
350 stm.sty_id = sty.id
351 AND ste.stm_id = stm.id
352 AND stm.active_yn = 'N'
353 AND stm.say_code = 'CURR'
354 AND stm.purpose_code = 'REPORT'
355 AND stm.khr_id = cp_khr_id
356 AND sty.stream_type_purpose = cp_stream_type_purpose
357 and exists (
358 select '1'
359 FROM okl_strm_type_b sty1, okl_streams stm1, okl_strm_elements ste1
360 where stm1.sty_id = sty1.id
361 AND ste1.stm_id = stm1.id
362 AND stm1.active_yn = 'N'
363 AND stm1.say_code = 'CURR'
364 AND stm.purpose_code = 'REPORT'
365 AND stm1.khr_id = cp_khr_id
366 and sty1.id = p_rep_sty_id
367 and ste1.stream_element_date = ste.stream_element_date
368 )
369 ORDER BY ste.stream_element_date;
370
371 /* CURSOR l_sec_rep_method_csr IS
372 SELECT secondary_rep_method
373 FROM okl_sys_acct_opts;
374 l_sec_rep_method VARCHAR2(30);
375 */
376 lx_rep_product OKL_PRODUCTS_V.NAME%TYPE;
377 lx_rep_product_id NUMBER;
378 lx_rep_deal_type okl_product_parameters_v.deal_type%TYPE;
379 l_rep_sty_id NUMBER;
380 rep_stream_type_purpose VARCHAR2(30);
381 l_api_version CONSTANT NUMBER := 1.0;
382 l_rep_id_tbl okl_streams_util.NumberTabTyp;
383
384 BEGIN
385 x_return_status := OKL_API.G_RET_STS_SUCCESS;
386 -- Call start_activity to create savepoint, check compatibility
387 -- and initialize message list
388 x_return_status := OKL_API.START_ACTIVITY (
389 l_api_name
390 ,p_init_msg_list
391 ,'_PVT'
392 ,x_return_status);
393 -- Check if activity started successfully
394 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
395 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
396 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
397 RAISE OKL_API.G_EXCEPTION_ERROR;
398 END IF;
399 -- validate in parameters
400 IF p_khr_id IS NULL OR
401 p_khr_id = OKL_API.G_MISS_NUM THEN
402 -- store SQL error message on message stack for caller
403 okl_api.set_message(p_app_name => G_APP_NAME,
404 p_msg_name => G_REQUIRED_VALUE,
405 p_token1 => G_COL_NAME_TOKEN,
406 p_token1_value => 'p_khr_id');
407 RAISE okl_api.g_exception_error;
408 END IF;
409 -- cursor to get investor Agreement contract id
410 -- for a given Lease contract id
411 /* sosharma ,14-12-2007
412 Bug 6691554
413 Modified to pick different cursor for pending contents in case p_mode is not null
414 Start Changes*/
415
416
417 -- This procedure is called from On-line and Mass Rebook (during activation of rebook)
418 -- p_khr_id is the contract_id of the original lease contract that is being rebooked.
419 -- This procedure regenerates Investor Accrual streams that are created for the contract, when IA was activated
420 -- Rebook process then compares the old and new Investor accrual streams to calculate Investor accrual adjustment
421
422 --sechawla 09-mar-09 : MG Impact on Investor Agreement - Modify create_lease_streams to regenerate the
423 -- Investor Accrual streams upon rebook
424
425 IF p_mode IS NULL THEN
426 OPEN get_inv_csr (p_khr_id => p_khr_id); --ID of lease contract
427 FETCH get_inv_csr INTO l_inv_id; --sechawla : ID if Investor Agreement
428 IF get_inv_csr%NOTFOUND THEN
429 -- store SQL error message on message stack for caller
430 okl_api.set_message(p_app_name => G_APP_NAME,
431 p_msg_name => G_NO_MATCHING_RECORD,
432 p_token1 => G_COL_NAME_TOKEN,
433 p_token1_value => 'p_khr_id');
434 RAISE okl_api.g_exception_error;
435 END IF;
436 CLOSE get_inv_csr;
437 ELSE
438 OPEN get_inv_pend_csr (p_khr_id => p_khr_id); --ID of lease contract
439 FETCH get_inv_pend_csr INTO l_inv_id; --sechawla : ID if Investor Agreement
440 IF get_inv_pend_csr%NOTFOUND THEN
441 -- store SQL error message on message stack for caller
442 okl_api.set_message(p_app_name => G_APP_NAME,
443 p_msg_name => G_NO_MATCHING_RECORD,
444 p_token1 => G_COL_NAME_TOKEN,
445 p_token1_value => 'p_khr_id');
446 RAISE okl_api.g_exception_error;
447 END IF;
448 CLOSE get_inv_pend_csr;
449 END IF;
450
451 /* sosharma end changes*/
452
453 /* sosharma ,14-12-2007
454 Bug 6691554
455 Modified to pick different cursor for pending contents in case p_mode is not null
456 Start Changes*/
457 IF p_mode IS NULL THEN
458 OPEN securitized_contracts_csr (l_inv_id,
459 p_khr_id);
460 FETCH securitized_contracts_csr INTO l_contracts_csr;
461 IF securitized_contracts_csr%NOTFOUND THEN
462 okl_api.set_message(p_app_name => g_app_name,
463 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
464 RAISE okl_api.G_EXCEPTION_ERROR;
465 END IF;
466 CLOSE securitized_contracts_csr;
467 ELSE
468 OPEN securitized_contracts_pend_csr (l_inv_id,
469 p_khr_id);
470 FETCH securitized_contracts_pend_csr INTO l_contracts_csr;
471 IF securitized_contracts_pend_csr%NOTFOUND THEN
472 okl_api.set_message(p_app_name => g_app_name,
473 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
474 RAISE okl_api.G_EXCEPTION_ERROR;
475 END IF;
476 CLOSE securitized_contracts_pend_csr;
477 END IF;
478 /* sosharma end changes */
479
480 DECLARE
481 TYPE ref_cursor IS REF CURSOR;
482 TYPE element_type IS RECORD (stream_element_date DATE,
483 amount NUMBER);
484 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
485 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
486 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
487 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
488 l_difference NUMBER := 0;
489 l_counter NUMBER := 1;
490 l_line_number NUMBER := 1;
491 --l_stmt VARCHAR2(5000);
492 --l_where VARCHAR2(2000) := ' ';
493 l_kle_id NUMBER;
494 l_start_date DATE;
495 l_final_start_date DATE;
496 ln_days NUMBER := 0;
497 l_arrears VARCHAR2(1);
498 l_frequency NUMBER;
499 l_contract_number VARCHAR2(2000);
500 --strm_csr ref_cursor;
501 l_elements element_type;
502
503 --sechawla 09-mar-09 : MG Impact on Investor Agreement
504 l_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
505 l_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
506 x_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
507 x_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
508 l_rep_elements element_type;
509 l_rep_counter NUMBER := 1;
510 l_rep_line_number NUMBER := 1;
511 BEGIN
512 OPEN contract_number_csr(l_contracts_csr.khr_id);
513 FETCH contract_number_csr INTO l_contract_number; --lease contract
514 IF contract_number_csr%NOTFOUND THEN
515 okl_api.set_message(p_app_name => G_APP_NAME,
516 p_msg_name => G_NO_MATCHING_RECORD,
517 p_token1 => G_COL_NAME_TOKEN,
518 p_token1_value => 'Lease Contract id');
519 RAISE okl_api.g_exception_error;
520 END IF;
521 CLOSE contract_number_csr;
522
523 /* sosharma ,14-12-2007
524 Bug 6691554
525 Modified to pick different cursor for pending contents in case p_mode is not null
526 Start Changes*/
527 IF p_mode IS NULL THEN
528 OPEN get_kle_id_csr(l_contracts_csr.khr_id);
529 FETCH get_kle_id_csr INTO l_kle_id, l_start_date;
530 IF get_kle_id_csr%NOTFOUND THEN
531 okl_api.set_message(p_app_name => G_APP_NAME,
532 p_msg_name => G_NO_MATCHING_RECORD,
533 p_token1 => G_COL_NAME_TOKEN,
534 p_token1_value => 'Lease contract id');
535 RAISE okl_api.g_exception_error;
536 END IF;
537 CLOSE get_kle_id_csr;
538 ELSE
539 OPEN get_kle_id_pend_csr(l_contracts_csr.khr_id);
540 FETCH get_kle_id_pend_csr INTO l_kle_id, l_start_date;
541 IF get_kle_id_pend_csr%NOTFOUND THEN
542 okl_api.set_message(p_app_name => G_APP_NAME,
543 p_msg_name => G_NO_MATCHING_RECORD,
544 p_token1 => G_COL_NAME_TOKEN,
545 p_token1_value => 'Lease contract id');
546 RAISE okl_api.g_exception_error;
547 END IF;
548 CLOSE get_kle_id_pend_csr;
549 END IF;
550 /* sosharma end changes */
551
552 IF l_kle_id IS NULL OR
553 l_kle_id = OKL_API.G_MISS_NUM THEN
554 okl_api.set_message(p_app_name => g_app_name,
555 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
556 p_token1 => g_contract_number_token,
557 p_token1_value => l_contract_number);
558 RAISE okl_api.g_exception_error;
559 END IF;
560 IF l_start_date IS NULL OR
561 l_start_date = okl_api.g_miss_date THEN
562 okl_api.set_message(p_app_name => g_app_name,
563 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
564 RAISE okl_api.g_exception_error;
565 END IF;
566 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
567 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
568 IF get_adv_arr_csr%NOTFOUND THEN
569 okl_api.set_message(p_app_name => G_APP_NAME,
570 p_msg_name => G_NO_MATCHING_RECORD,
571 p_token1 => G_COL_NAME_TOKEN,
572 p_token1_value => 'Lease Contract id and contract Line id');
573 RAISE okl_api.g_exception_error;
574 END IF;
575 CLOSE get_adv_arr_csr;
576 IF l_frequency IS NULL THEN
577 okl_api.set_message(p_app_name => g_app_name,
578 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
579 p_token1 => g_contract_number_token,
580 p_token1_value => l_contract_number);
581 RAISE okl_api.g_exception_error;
582 END IF;
583 IF l_arrears = 'Y' THEN
584 ln_days := OKL_STREAM_GENERATOR_PVT.get_day_count (
585 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
586 p_end_date => l_start_date,
587 p_arrears => l_arrears,
588 x_return_status => x_return_status);
589 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
590 RAISE okl_api.g_exception_unexpected_error;
591 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
592 RAISE okl_api.g_exception_error;
593 END IF;
594 l_final_start_date := l_start_date - ln_days;
595 ELSIF NVL(l_arrears,'N') = 'N' THEN
596 l_final_start_date := l_start_date;
597 END IF;
598 -- commenting as all accrual streams are generated
599 -- at contract level. Will remove comments after super trump fix is provided
600 -- for stream generation at asset level.Ref cursor will be needed later.
601 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
602 -- Commented for SQL Literals on 12-09-2006
603 /* l_stmt := 'SELECT ste.stream_element_date stream_element_date,
604 ste.amount amount
605 FROM okl_strm_type_b sty,
606 okl_streams stm,
607 okl_strm_elements ste
608 WHERE 1 = 1
609 AND stm.sty_id = sty.id
610 AND ste.stm_id = stm.id
611 AND stm.active_yn = '||''''||'Y'||''''||'
612 AND stm.say_code = '||''''||'CURR'||'''' ;
613 l_where := l_where ||' AND stm.khr_id = ' || l_contracts_csr.khr_id
614 ||' AND ste.stream_element_date BETWEEN '|| '''' ||l_final_start_date|| '''' ||' AND '|| '''' ||l_contracts_csr.end_date|| '''';
615 */
616 --get sty_id for the contract based on deal type
617
618 /* ankushar , 25-01-2008 Bug 6773285
619 Added code to generate new Stream Types for a Loan product on an Investor Agreement
620 Start Changes
621 */
622
623 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin--------------
624 okl_accounting_util.get_reporting_product(
625 p_api_version => l_api_version,
626 p_init_msg_list => p_init_msg_list,
627 x_return_status => l_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630 p_contract_id => p_khr_id,
631 x_rep_product => lx_rep_product,
632 x_rep_product_id => lx_rep_product_id,
633 x_rep_deal_type => lx_rep_deal_type);
634
635 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
636 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
637 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
638 RAISE OKL_API.G_EXCEPTION_ERROR;
639 END IF;
640
641 /* --Check the secondary_rep_method
642 OPEN l_sec_rep_method_csr ;
643 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
644 IF l_sec_rep_method_csr%NOTFOUND THEN
645 okl_api.set_message(p_app_name => g_app_name,
646 p_msg_name => 'OKL_NO_SEC_REP_METHOD' --> seed this ''Secondary rep method cursor did not return any records''
647 );
648 RAISE okl_api.g_exception_error;
649 END IF;
650 CLOSE l_sec_rep_method_csr ;
651 */
652 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end --------------
653
654
655 IF l_contracts_csr.deal_type = 'LEASEOP' THEN -- deal type of primary product of the contract
656 OKL_STREAMS_UTIL.get_primary_stream_type
657 (
658 p_khr_id => l_contracts_csr.khr_id,
659 p_primary_sty_purpose => l_investor_rental_accrual,
660 x_return_status => l_return_status,
661 x_primary_sty_id => l_sty_id
662 );
663 IF l_return_status <> 'S' THEN
664 okl_api.set_message(p_app_name => g_app_name,
665 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
666 p_token1 => 'STREAM_NAME',
667 p_token1_value => l_investor_rental_accrual);
668 RAISE okl_api.g_exception_error;
669 END IF;
670 -- calculate total revenue share
671 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'RENT') LOOP
672 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
673 END LOOP;
674 IF l_revenue_share IS NULL OR
675 l_revenue_share = 0 THEN
676 -- store SQL error message on message stack for caller
677 okl_api.set_message(p_app_name => g_app_name,
678 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
679 RAISE okl_api.G_EXCEPTION_ERROR;
680 END IF;
681 stream_type_purpose := l_rental_accrual; ---- Rental Accrual for primary product
682 -- sechawla : This is the 'Rental Accrual' stream, generated when OP lease contract is first Booked
683 -- sechawla : This stream is used to generate Investor Rental Accrual Stream, upon rebook
684
685 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
686 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
687 --IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
688 IF lx_rep_product IS NOT NULL THEN
689 IF lx_rep_deal_type = 'LEASEOP' THEN
690 OKL_STREAMS_UTIL.get_primary_stream_type_rep
691 (
692 p_khr_id => l_contracts_csr.khr_id,
693 p_primary_sty_purpose => l_investor_rental_accrual,
694 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
695 --sechawla : Investor Arental Accrual stream is generated upon rebook
696 x_return_status => l_return_status,
697 x_primary_sty_id => l_rep_sty_id
698 );
699 IF l_return_status <> 'S' THEN
700 okl_api.set_message(p_app_name => g_app_name,
701 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
702 p_token1 => 'STREAM_NAME',
703 p_token1_value => l_investor_rental_accrual);
704 RAISE okl_api.g_exception_error;
705
706 END IF;
707
708 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
709 --Rental Accrual stream is also generated for the reporting product (if OP Lease), when contract
710 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon rebook
711 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
712 OKL_STREAMS_UTIL.get_primary_stream_type_rep
713 (
714 p_khr_id => l_contracts_csr.khr_id,
715 p_primary_sty_purpose => l_investor_pre_tax_income,
716 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
717 --Investor Pre Tax Income stream is generated upon rebook
718 x_return_status => l_return_status,
719 x_primary_sty_id => l_rep_sty_id
720 );
721
722 IF l_return_status <> 'S' THEN
723 okl_api.set_message(p_app_name => g_app_name,
724 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
725 p_token1 => 'STREAM_NAME',
726 p_token1_value => l_investor_pre_tax_income);
727 RAISE okl_api.g_exception_error;
728 END IF;
729
730 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
731 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
732 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon rebook
733 END IF;
734 END IF;
735 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
736
737
738 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN -- deal type of primary product of the contract
739 OKL_STREAMS_UTIL.get_primary_stream_type
740 (
741 p_khr_id => l_contracts_csr.khr_id,
742 p_primary_sty_purpose => l_investor_pre_tax_income,
743 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of DF/ST lease contract
744 --Investor Pre Tax Income stream is generated upon rebook
745 x_return_status => l_return_status,
746 x_primary_sty_id => l_sty_id
747 );
748 IF l_return_status <> 'S' THEN
749 okl_api.set_message(p_app_name => g_app_name,
750 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
751 p_token1 => 'STREAM_NAME',
752 p_token1_value => l_investor_pre_tax_income);
753 RAISE okl_api.g_exception_error;
754 END IF;
755 -- calculate total revenue share
756 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'RENT') LOOP
757 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
758 END LOOP;
759 IF l_revenue_share IS NULL OR
760 l_revenue_share = 0 THEN
761 -- store SQL error message on message stack for caller
762 okl_api.set_message(p_app_name => g_app_name,
763 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
764 RAISE okl_api.G_EXCEPTION_ERROR;
765 END IF;
766 stream_type_purpose := l_pre_tax_income;--Pre Tax Income for Primary product
767 -- sechawla : This is the 'Pre Tax Income' stream, generated when DF/ST lease contract is Booked
768 -- sechawla : This stream is used to generate Investor Pre Tax Income Stream, upon rebook
769
770 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
771 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
772 -- IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
773 IF lx_rep_product IS NOT NULL THEN
774 IF lx_rep_deal_type = 'LEASEOP' THEN
775 OKL_STREAMS_UTIL.get_primary_stream_type_rep
776 (
777 p_khr_id => l_contracts_csr.khr_id,
778 p_primary_sty_purpose => l_investor_rental_accrual,
779 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
780 --sechawla : Investor Arental Accrual stream is generated upon rebook
781 x_return_status => l_return_status,
782 x_primary_sty_id => l_rep_sty_id
783 );
784 IF l_return_status <> 'S' THEN
785 okl_api.set_message(p_app_name => g_app_name,
786 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
787 p_token1 => 'STREAM_NAME',
788 p_token1_value => l_investor_rental_accrual);
789 RAISE okl_api.g_exception_error;
790
791 END IF;
792
793 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
794 --Rental Accrual stream is generated for the reporting product (if OP Lease), when contract
795 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon IA activation
796 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
797 OKL_STREAMS_UTIL.get_primary_stream_type_rep
798 (
799 p_khr_id => l_contracts_csr.khr_id,
800 p_primary_sty_purpose => l_investor_pre_tax_income,
801 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
802 --Investor Pre Tax Income stream is generated upon rebook
803 x_return_status => l_return_status,
804 x_primary_sty_id => l_rep_sty_id
805 );
806
807 IF l_return_status <> 'S' THEN
808 okl_api.set_message(p_app_name => g_app_name,
809 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
810 p_token1 => 'STREAM_NAME',
811 p_token1_value => l_investor_pre_tax_income);
812 RAISE okl_api.g_exception_error;
813 END IF;
814
815 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
816 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
817 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon rebook
818 END IF;
819 END IF;
820 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
821
822 /* ankushar , 25-01-2008 Bug 6773285
823 End Changes
824 */
825
826 /* ankushar , 16-01-2008 Bug 6740000
827 Added condition for fetching stream type for a Loan product
828 Start Changes
829 */
830 ---sechawla 09-mar-09 : MG Impact on Investor Agreement : No impacts on Loans
831 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
832 OKL_STREAMS_UTIL.get_primary_stream_type
833 (
834 p_khr_id => l_contracts_csr.khr_id,
835 p_primary_sty_purpose => l_inv_interest_income_accrual,
836 x_return_status => l_return_status,
837 x_primary_sty_id => l_sty_id
838 );
839 IF l_return_status <> 'S' THEN
840 okl_api.set_message(p_app_name => g_app_name,
841 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
842 p_token1 => 'STREAM_NAME',
843 p_token1_value => l_inv_interest_income_accrual);
844 RAISE okl_api.g_exception_error;
845 END IF;
846 -- calculate total revenue share
847 FOR get_revenue_share_rec IN get_revenue_share_csr(l_inv_id, 'LOAN_PAYMENT') LOOP
848 l_revenue_share := l_revenue_share + get_revenue_share_rec.percent_stake;
849 END LOOP;
850 IF l_revenue_share IS NULL OR
851 l_revenue_share = 0 THEN
852 -- store SQL error message on message stack for caller
853 okl_api.set_message(p_app_name => g_app_name,
854 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
855 RAISE okl_api.G_EXCEPTION_ERROR;
856 END IF;
857 stream_type_purpose := l_interest_income;
858 /* ankushar , 16-01-2008 Bug 6691554
859 End Changes
860 */
861 END IF;
862
863 --Populate streams structure for primary product
864 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
865 -- populate stream header record
866 l_stmv_rec.sty_id := l_sty_id;
867 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
868 l_stmv_rec.sgn_code := 'MANL';
869 l_stmv_rec.say_code := 'WORK';
870
871 l_stmv_rec.transaction_number := l_trx_number;
872 l_stmv_rec.active_yn := 'N';
873
874 l_stmv_rec.date_working := l_sysdate;
875 -- create final l_stmt
876 --l_stmt := l_stmt || l_where;
877 --OPEN strm_csr FOR l_stmt;
878
879
880 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
881 IF l_rep_sty_id IS NOT NULL THEN
882 --Populate streams structure for reporting product
883 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
884 -- populate stream header record
885 l_rep_stmv_rec.sty_id := l_rep_sty_id;
886 l_rep_stmv_rec.khr_id := l_contracts_csr.khr_id;
887 l_rep_stmv_rec.sgn_code := 'MANL';
888 l_rep_stmv_rec.say_code := 'WORK';
889 l_rep_stmv_rec.transaction_number := l_trx_number;
890 l_rep_stmv_rec.active_yn := 'N';
891 l_rep_stmv_rec.purpose_code := 'REPORT';
892 l_rep_stmv_rec.date_current := l_sysdate;
893 -- l_rep_stmv_rec.source_id := p_khr_id;
894 -- l_rep_stmv_rec.source_table := 'OKL_K_HEADERS';
895 END IF;
896 -----------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ------------
897
898 --Create stream element structure for primary product
899 -- use of a parameterized cursor by zrehman on 12-Sep-2006
900 -- vsgandhi
901 -- Bug 13428672: 29-Mar-2012
902
903 -- OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
904 OPEN strm_csr(l_contracts_csr.khr_id, l_sty_id, stream_type_purpose);
905 LOOP
906 --re-initialize period end date
907 l_period_end_date := NULL;
908 FETCH strm_csr INTO l_elements;
909 EXIT WHEN strm_csr%NOTFOUND;
910 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
911 --populate stream elements tbl
912 -- manipulate first record
913 IF strm_csr%ROWCOUNT = 1 THEN
914
915 -- If start date is last day of the month, do nothing.
916 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
917 -- If start date is the same as first day of the month then take whole amount.
918 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
919 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
920 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
921 l_selv_tbl(l_counter).se_line_number := l_line_number;
922 l_line_number := l_line_number + 1;
923 l_counter := l_counter + 1;
924 ELSE
925 -- start date is not first or last day of the month. so prorate.
926 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
927 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
928 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
929 l_selv_tbl(l_counter).se_line_number := l_line_number;
930 l_line_number := l_line_number + 1;
931 l_counter := l_counter + 1;
932 END IF;
933 END IF;
934 ELSE
935 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
936 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
937 l_selv_tbl(l_counter).se_line_number := l_line_number;
938 l_line_number := l_line_number + 1;
939 l_counter := l_counter + 1;
940 END IF;
941 END LOOP;
942 CLOSE strm_csr;
943 IF l_selv_tbl.COUNT > 0 THEN
944 -- call streams api
945 okl_streams_pub.create_streams(
946 p_api_version => p_api_version,
947 p_init_msg_list => p_init_msg_list,
948 x_return_status => x_return_status,
949 x_msg_count => x_msg_count,
950 x_msg_data => x_msg_data,
951 p_stmv_rec => l_stmv_rec,
952 p_selv_tbl => l_selv_tbl,
953 x_stmv_rec => x_stmv_rec,
954 x_selv_tbl => x_selv_tbl);
955 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
956 RAISE okl_api.g_exception_unexpected_error;
957 ELSIF (x_return_Status = okl_api.g_ret_sts_error) THEN
958 RAISE okl_api.g_exception_error;
959 END IF;
960 END IF;
961 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
962
963 --Create stream element structure for reporting product
964 -- vsgandhi
965 -- Bug 13428672: 29-Mar-2012
966
967 --OPEN rep_strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, rep_stream_type_purpose);
968 OPEN rep_strm_csr(l_contracts_csr.khr_id, l_rep_sty_id, rep_stream_type_purpose);
969 LOOP
970 --re-initialize period end date
971 l_period_end_date := NULL;
972 FETCH rep_strm_csr INTO l_rep_elements;
973 EXIT WHEN rep_strm_csr%NOTFOUND;
974 l_period_end_date := trunc(last_day(l_rep_elements.stream_element_date));
975 --populate stream elements tbl
976 -- manipulate first record
977 IF rep_strm_csr%ROWCOUNT = 1 THEN
978 -- If start date is last day of the month, do nothing.
979 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
980 -- If start date is the same as first day of the month then take whole amount.
981 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
982 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
983 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
984 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
985 l_rep_line_number := l_rep_line_number + 1;
986 l_rep_counter := l_rep_counter + 1;
987 ELSE
988 -- start date is not first or last day of the month. so prorate.
989 l_difference := ABS(TRUNC(l_rep_elements.stream_element_date) - TRUNC(l_final_start_date));
990 l_rep_selv_tbl(l_rep_counter).amount := ROUND((((l_difference/30)*l_rep_elements.amount)*l_revenue_share/100),2);
991 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
992 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
993 l_rep_line_number := l_rep_line_number + 1;
994 l_rep_counter := l_rep_counter + 1;
995 END IF;
996 END IF;
997 ELSE
998 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
999 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
1000 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
1001 l_rep_line_number := l_rep_line_number + 1;
1002 l_rep_counter := l_rep_counter + 1;
1003 END IF;
1004 END LOOP;
1005 CLOSE rep_strm_csr;
1006 IF l_rep_selv_tbl.COUNT > 0 THEN
1007 -- call streams api
1008 OKL_STREAMS_PUB.create_streams(
1009 p_api_version => l_api_version
1010 ,p_init_msg_list => p_init_msg_list
1011 ,x_return_status => l_return_status
1012 ,x_msg_count => x_msg_count
1013 ,x_msg_data => x_msg_data
1014 ,p_stmv_rec => l_rep_stmv_rec
1015 ,p_selv_tbl => l_rep_selv_tbl
1016 ,x_stmv_rec => x_rep_stmv_rec
1017 ,x_selv_tbl => x_rep_selv_tbl );
1018 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1019 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1021 RAISE Okl_Api.G_EXCEPTION_ERROR;
1022 END IF;
1023 END IF;
1024
1025 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ---------------
1026
1027 --call link api
1028
1029 --sechawla 09-mar-09 : MG Impact on Investor Agreement : Modified API below to update reporting
1030 --Investor streams created during rebook, with trx_id and link_hist_stream_id
1031 OKL_CONTRACT_REBOOK_PVT.link_inv_accrual_streams(
1032 p_api_version => p_api_version
1033 ,p_init_msg_list => p_init_msg_list
1034 ,x_return_status => l_return_status
1035 ,x_msg_count => x_msg_count
1036 ,x_msg_data => x_msg_data
1037 ,p_khr_id =>l_contracts_csr.khr_id
1038 );
1039 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1040 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1041 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1042 RAISE Okl_Api.G_EXCEPTION_ERROR;
1043 END IF;
1044
1045 -- Update the status of primary streams from 'CURR' to 'HIST'
1046
1047 j:=0;
1048 l_id_tbl.delete;
1049
1050 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
1051 ,p_sty_id=>l_sty_id
1052 ,p_say_code=>'CURR'
1053 , p_purpose_code => Null)
1054 LOOP
1055 j := j + 1;
1056 l_id_tbl(j) :=stream_id_rec.id;
1057 END LOOP;
1058
1059 IF (l_id_tbl.COUNT > 0) THEN
1060
1061 BEGIN
1062
1063 FORALL i IN l_id_tbl.FIRST..l_id_tbl.LAST
1064 UPDATE OKL_STREAMS
1065 SET say_code = 'HIST',
1066 active_yn = 'N',
1067 date_history = sysdate,
1068 last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
1069 last_update_date = sysdate,
1070 last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
1071 WHERE ID = l_id_tbl(i);
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 okl_api.set_message (p_app_name => G_APP_NAME,
1076 p_msg_name => G_DB_ERROR,
1077 p_token1 => G_PROG_NAME_TOKEN,
1078 p_token1_value => l_api_name,
1079 p_token2 => G_SQLCODE_TOKEN,
1080 p_token2_value => sqlcode,
1081 p_token3 => G_SQLERRM_TOKEN,
1082 p_token3_value => sqlerrm);
1083 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1084 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1085 END;
1086 END IF;
1087
1088 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin
1089 --update the status of reporting streams from 'CURR' to 'HIST'
1090
1091 j:=0;
1092 l_rep_id_tbl.delete;
1093
1094 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
1095 ,p_sty_id=>l_rep_sty_id
1096 ,p_say_code=>'CURR'
1097 ,p_purpose_code =>'REPORT')
1098 LOOP
1099 j := j + 1;
1100 l_rep_id_tbl(j) :=stream_id_rec.id;
1101 END LOOP;
1102
1103 IF (l_rep_id_tbl.COUNT > 0) THEN
1104
1105 BEGIN
1106
1107 FORALL i IN l_rep_id_tbl.FIRST..l_rep_id_tbl.LAST
1108 UPDATE OKL_STREAMS
1109 SET say_code = 'HIST',
1110 active_yn = 'N',
1111 date_history = sysdate,
1112 last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
1113 last_update_date = sysdate,
1114 last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
1115 WHERE ID = l_rep_id_tbl(i);
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 okl_api.set_message (p_app_name => G_APP_NAME,
1120 p_msg_name => G_DB_ERROR,
1121 p_token1 => G_PROG_NAME_TOKEN,
1122 p_token1_value => l_api_name,
1123 p_token2 => G_SQLCODE_TOKEN,
1124 p_token2_value => sqlcode,
1125 p_token3 => G_SQLERRM_TOKEN,
1126 p_token3_value => sqlerrm);
1127 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1128 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1129 END;
1130 END IF;
1131 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end
1132
1133 --------------------------------------------
1134 -- Update the status of 'WORK' to 'CURR' for primary streams
1135 ----------------------------------
1136 j:=0;
1137 l_id_tbl.delete;
1138 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
1139 ,p_sty_id=>l_sty_id
1140 ,p_say_code=>'WORK'
1141 ,p_purpose_code => Null)
1142 LOOP
1143 j := j + 1;
1144 l_id_tbl(j) :=stream_id_rec.id;
1145 END LOOP;
1146
1147 IF (l_id_tbl.COUNT > 0) THEN
1148
1149 BEGIN
1150
1151 FORALL i IN l_id_tbl.FIRST..l_id_tbl.LAST
1152 UPDATE OKL_STREAMS
1153 SET say_code = 'CURR',
1154 active_yn = 'Y',
1155 date_current = sysdate,
1156 last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
1157 last_update_date = sysdate,
1158 last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
1159 WHERE ID = l_id_tbl(i);
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 okl_api.set_message (p_app_name => G_APP_NAME,
1164 p_msg_name => G_DB_ERROR,
1165 p_token1 => G_PROG_NAME_TOKEN,
1166 p_token1_value => l_api_name,
1167 p_token2 => G_SQLCODE_TOKEN,
1168 p_token2_value => sqlcode,
1169 p_token3 => G_SQLERRM_TOKEN,
1170 p_token3_value => sqlerrm);
1171 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1172 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1173 END;
1174 END IF;
1175
1176
1177
1178 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement
1179 --------------------------------------------
1180 -- Update the status of 'WORK' to 'CURR' for secondary streams
1181 ----------------------------------
1182 j:=0;
1183 l_rep_id_tbl.delete;
1184 FOR stream_id_rec IN stream_id_csr(p_khr_id => l_contracts_csr.khr_id
1185 ,p_sty_id=>l_rep_sty_id
1186 ,p_say_code=>'WORK'
1187 ,p_purpose_code => 'REPORT')
1188 LOOP
1189 j := j + 1;
1190 l_rep_id_tbl(j) :=stream_id_rec.id;
1191 END LOOP;
1192
1193 IF (l_rep_id_tbl.COUNT > 0) THEN
1194
1195 BEGIN
1196
1197 FORALL i IN l_rep_id_tbl.FIRST..l_rep_id_tbl.LAST
1198 UPDATE OKL_STREAMS
1199 SET say_code = 'CURR',
1200 active_yn = 'N',
1201 date_current = sysdate,
1202 last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
1203 last_update_date = sysdate,
1204 last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
1205 WHERE ID = l_rep_id_tbl(i);
1206
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 okl_api.set_message (p_app_name => G_APP_NAME,
1210 p_msg_name => G_DB_ERROR,
1211 p_token1 => G_PROG_NAME_TOKEN,
1212 p_token1_value => l_api_name,
1213 p_token2 => G_SQLCODE_TOKEN,
1214 p_token2_value => sqlcode,
1215 p_token3 => G_SQLERRM_TOKEN,
1216 p_token3_value => sqlerrm);
1217 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1218 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1219 END;
1220 END IF;
1221
1222
1223
1224
1225 EXCEPTION
1226 WHEN okl_api.g_exception_error THEN
1227 IF contract_number_csr%ISOPEN THEN
1228 CLOSE contract_number_csr;
1229 END IF;
1230 IF get_kle_id_csr%ISOPEN THEN
1231 CLOSE get_kle_id_csr;
1232 END IF;
1233 IF get_adv_arr_csr%ISOPEN THEN
1234 CLOSE get_adv_arr_csr;
1235 END IF;
1236 IF get_sty_id_csr%ISOPEN THEN
1237 CLOSE get_sty_id_csr;
1238 END IF;
1239 IF strm_csr%ISOPEN THEN
1240 CLOSE strm_csr;
1241 END IF;
1242 x_return_status := okl_api.handle_exceptions(l_api_name,
1243 g_pkg_name,
1244 'OKL_API.G_RET_STS_ERROR',
1245 x_msg_count,
1246 x_msg_data,
1247 '_PVT');
1248 WHEN okl_api.g_exception_unexpected_error THEN
1249 IF contract_number_csr%ISOPEN THEN
1250 CLOSE contract_number_csr;
1251 END IF;
1252 IF get_kle_id_csr%ISOPEN THEN
1253 CLOSE get_kle_id_csr;
1254 END IF;
1255 IF get_adv_arr_csr%ISOPEN THEN
1256 CLOSE get_adv_arr_csr;
1257 END IF;
1258 IF get_sty_id_csr%ISOPEN THEN
1259 CLOSE get_sty_id_csr;
1260 END IF;
1261 IF strm_csr%ISOPEN THEN
1262 CLOSE strm_csr;
1263 END IF;
1264 x_return_status := okl_api.handle_exceptions(l_api_name,
1265 g_pkg_name,
1266 'OKL_API.G_RET_STS_UNEXP_ERROR',
1267 x_msg_count,
1268 x_msg_data,
1269 '_PVT');
1270 WHEN OTHERS THEN
1271 IF contract_number_csr%ISOPEN THEN
1272 CLOSE contract_number_csr;
1273 END IF;
1274 IF get_kle_id_csr%ISOPEN THEN
1275 CLOSE get_kle_id_csr;
1276 END IF;
1277 IF get_adv_arr_csr%ISOPEN THEN
1278 CLOSE get_adv_arr_csr;
1279 END IF;
1280 IF get_sty_id_csr%ISOPEN THEN
1281 CLOSE get_sty_id_csr;
1282 END IF;
1283 IF strm_csr%ISOPEN THEN
1284 CLOSE strm_csr;
1285 END IF;
1286 x_return_status := okl_api.handle_exceptions(l_api_name,
1287 g_pkg_name,
1288 'OTHERS',
1289 x_msg_count,
1290 x_msg_data,
1291 '_PVT');
1292 END;
1293 OKL_API.END_ACTIVITY (x_msg_count,
1294 x_msg_data );
1295 EXCEPTION
1296 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1297 IF contract_number_csr%ISOPEN THEN
1298 CLOSE contract_number_csr;
1299 END IF;
1300 IF get_kle_id_csr%ISOPEN THEN
1301 CLOSE get_kle_id_csr;
1302 END IF;
1303 IF get_adv_arr_csr%ISOPEN THEN
1304 CLOSE get_adv_arr_csr;
1305 END IF;
1306 IF get_sty_id_csr%ISOPEN THEN
1307 CLOSE get_sty_id_csr;
1308 END IF;
1309 IF securitized_contracts_csr%ISOPEN THEN
1310 CLOSE securitized_contracts_csr;
1311 END IF;
1312 IF get_revenue_share_csr%ISOPEN THEN
1313 CLOSE get_revenue_share_csr;
1314 END IF;
1315 IF get_inv_csr%ISOPEN THEN
1316 CLOSE get_inv_csr;
1317 END IF;
1318 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1319 l_api_name,
1320 G_PKG_NAME,
1321 'OKL_API.G_RET_STS_ERROR',
1322 x_msg_count,
1323 x_msg_data,
1324 '_PVT');
1325 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1326 IF contract_number_csr%ISOPEN THEN
1327 CLOSE contract_number_csr;
1328 END IF;
1329 IF get_kle_id_csr%ISOPEN THEN
1330 CLOSE get_kle_id_csr;
1331 END IF;
1332 IF get_adv_arr_csr%ISOPEN THEN
1333 CLOSE get_adv_arr_csr;
1334 END IF;
1335 IF get_sty_id_csr%ISOPEN THEN
1336 CLOSE get_sty_id_csr;
1337 END IF;
1338 IF securitized_contracts_csr%ISOPEN THEN
1339 CLOSE securitized_contracts_csr;
1340 END IF;
1341 IF get_revenue_share_csr%ISOPEN THEN
1342 CLOSE get_revenue_share_csr;
1343 END IF;
1344 IF get_inv_csr%ISOPEN THEN
1345 CLOSE get_inv_csr;
1346 END IF;
1347 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1348 l_api_name,
1349 G_PKG_NAME,
1350 'OKL_API.G_RET_STS_UNEXP_ERROR',
1351 x_msg_count,
1352 x_msg_data,
1353 '_PVT');
1354 WHEN OTHERS THEN
1355 IF contract_number_csr%ISOPEN THEN
1356 CLOSE contract_number_csr;
1357 END IF;
1358 IF get_kle_id_csr%ISOPEN THEN
1359 CLOSE get_kle_id_csr;
1360 END IF;
1361 IF get_adv_arr_csr%ISOPEN THEN
1362 CLOSE get_adv_arr_csr;
1363 END IF;
1364 IF get_sty_id_csr%ISOPEN THEN
1365 CLOSE get_sty_id_csr;
1366 END IF;
1367 IF securitized_contracts_csr%ISOPEN THEN
1368 CLOSE securitized_contracts_csr;
1369 END IF;
1370 IF get_revenue_share_csr%ISOPEN THEN
1371 CLOSE get_revenue_share_csr;
1372 END IF;
1373 IF get_inv_csr%ISOPEN THEN
1374 CLOSE get_inv_csr;
1375 END IF;
1376 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1377 l_api_name,
1378 G_PKG_NAME,
1379 'OTHERS',
1380 x_msg_count,
1381 x_msg_data,
1382 '_PVT');
1383 END create_lease_streams;
1384 --------------------------------------------------------------------------------
1385 PROCEDURE CREATE_STREAMS(p_api_version IN NUMBER,
1386 p_init_msg_list IN VARCHAR2,
1387 x_return_status OUT NOCOPY VARCHAR2,
1388 x_msg_count OUT NOCOPY NUMBER,
1389 x_msg_data OUT NOCOPY VARCHAR2,
1390 p_khr_id IN NUMBER,
1391 --sosharma added Bug 6691554, Added for generating streams on transient pool submission
1392 p_mode IN VARCHAR2 DEFAULT NULL)
1393 IS
1394
1395 l_count NUMBER := 1;
1396 l_api_version CONSTANT NUMBER := 1.0;
1397 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_STREAMS';
1398 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
1399 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1400 l_msg_count NUMBER;
1401 l_msg_data VARCHAR2(2000);
1402 l_period_end_date DATE;
1403 l_total_records NUMBER;
1404 l_sysdate DATE := TRUNC(SYSDATE);
1405 l_trx_number NUMBER;
1406 l_revenue_share NUMBER := 0;
1407 l_sty_id NUMBER;
1408
1409 stream_type_purpose VARCHAR2(30);
1410 l_khr_num OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE := NULL;
1411 l_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE := NULL;
1412 /*
1413 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
1414 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
1415 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
1416 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
1417 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENTAL ACCRUAL';
1418 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'PRE-TAX INCOME';
1419 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST INCOME';
1420 l_variable_income CONSTANT VARCHAR2(2000) := 'VARIABLE INCOME ACCRUAL';
1421 */
1422 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
1423 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
1424 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
1425 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
1426 l_rental_accrual CONSTANT VARCHAR2(2000) := 'RENT_ACCRUAL';
1427 l_pre_tax_income CONSTANT VARCHAR2(2000) := 'LEASE_INCOME';
1428 l_interest_income CONSTANT VARCHAR2(2000) := 'INTEREST_INCOME';
1429 l_variable_income CONSTANT VARCHAR2(2000) := 'ACCOUNTING';
1430 /* ankushar , 16-01-2008 Bug 6740000
1431 Added new Stream Type purpose for a Loan product
1432 */
1433 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
1434
1435 /* ankushar , 16-01-2008 Bug 6740000
1436 Modified cursors to fetch based on stream type for a Loan product
1437 Start Changes
1438 */
1439 -- cursor to select contracts belonging to a pool(investor agreement)
1440 CURSOR securitized_contracts_csr (p_inv_id NUMBER)
1441 IS
1442 SELECT DISTINCT opc.khr_id khr_id,
1443 opc.streams_to_date end_date,
1444 khr.deal_type deal_type
1445 FROM OKL_POOL_CONTENTS opc,
1446 OKL_POOLS op,
1447 OKL_K_HEADERS khr,
1448 OKL_STRM_TYPE_B stytl
1449 WHERE op.khr_id = p_inv_id
1450 AND op.id = opc.pol_id
1451 AND opc.khr_id = khr.id
1452 AND opc.sty_id = stytl.id
1453 --AND stytl.code = 'RENT'
1454 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1455 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
1456 GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
1457
1458 -- cursor to get first kle_id and earliest stream element date
1459 CURSOR get_kle_id_csr(p_khr_id NUMBER)
1460 IS
1461 SELECT opc.kle_id kle_id,
1462 MIN(opc.streams_from_date) start_date
1463 FROM OKL_POOL_CONTENTS opc,
1464 OKL_STRM_TYPE_B sty
1465 WHERE opc.khr_id = p_khr_id
1466 AND opc.sty_id = sty.id
1467 --AND sty.code = 'RENT'
1468 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1469 AND opc.status_code IN (Okl_Pool_Pvt.G_POC_STS_ACTIVE, Okl_Pool_Pvt.G_POC_STS_NEW)
1470 AND ROWNUM < 2
1471 GROUP BY opc.kle_id;
1472
1473 /* sosharma ,14-12-2007
1474 Bug 6691554
1475 Cursors to pick up pools contents in pending status
1476 Start Changes*/
1477
1478 CURSOR securitized_contracts_pend_csr (p_inv_id NUMBER)
1479 IS
1480 SELECT DISTINCT opc.khr_id khr_id,
1481 opc.streams_to_date end_date,
1482 khr.deal_type deal_type
1483 FROM OKL_POOL_CONTENTS opc,
1484 OKL_POOLS op,
1485 OKL_K_HEADERS khr,
1486 OKL_STRM_TYPE_B stytl
1487 WHERE op.khr_id = p_inv_id
1488 AND op.id = opc.pol_id
1489 AND opc.khr_id = khr.id
1490 AND opc.sty_id = stytl.id
1491 --AND stytl.code = 'RENT'
1492 AND stytl.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1493 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
1494 GROUP BY opc.khr_id, opc.streams_from_date, opc.streams_to_date, khr.deal_type;
1495
1496 -- cursor to get first kle_id and earliest stream element date
1497 CURSOR get_kle_id_pend_csr(p_khr_id NUMBER)
1498 IS
1499 SELECT opc.kle_id kle_id,
1500 MIN(opc.streams_from_date) start_date
1501 FROM OKL_POOL_CONTENTS opc,
1502 OKL_STRM_TYPE_B sty
1503 WHERE opc.khr_id = p_khr_id
1504 AND opc.sty_id = sty.id
1505 --AND sty.code = 'RENT'
1506 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1507 AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
1508 AND ROWNUM < 2
1509 GROUP BY opc.kle_id;
1510
1511 /* sosharma end changes*/
1512
1513 -- cursor to get advance/arrears and frequency for rent stream type
1514 CURSOR get_adv_arr_csr(p_khr_id NUMBER, p_kle_id NUMBER)
1515 IS
1516 SELECT decode(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
1517 decode(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
1518 FROM OKC_K_HEADERS_B K,
1519 OKC_RULE_GROUPS_B RG,
1520 OKC_RULES_B SLH,
1521 OKC_RULES_B SLL,
1522 OKL_STRM_TYPE_B STRM
1523 WHERE slh.rule_information_category = 'LASLH'
1524 AND slh.rgp_id = rg.id
1525 AND sll.object2_id1 = to_char(slh.id)
1526 AND sll.rgp_id = rg.id
1527 AND slh.object1_id1 = to_char(strm.id)
1528 --AND strm.code = 'RENT'
1529 AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
1530 AND rg.dnz_chr_id = k.id
1531 AND rg.chr_id IS NULL
1532 AND rg.rgd_code = 'LALEVL'
1533 AND rg.cle_id = p_kle_id
1534 AND k.id = p_khr_id;
1535
1536 -- cursor to get stream type id
1537 CURSOR get_sty_id_csr (p_sty_name VARCHAR2)
1538 IS
1539 SELECT id
1540 FROM OKL_STRM_TYPE_B
1541 WHERE code = p_sty_name;
1542
1543 -- list of all investors for the agreement
1544 CURSOR get_investors_csr(p_khr_id IN NUMBER)
1545 IS
1546 SELECT clet.id id
1547 FROM OKC_K_LINES_B clet
1548 ,OKC_LINE_STYLES_B lseb
1549 WHERE clet.dnz_chr_id = p_khr_id
1550 AND clet.lse_id = lseb.id
1551 AND lseb.lty_code = 'INVESTMENT';
1552
1553 -- ankushar Added stream_type_subclass parameter to the cursor
1554 -- the revenue shares for the investor
1555 CURSOR get_revenue_share_csr(p_tl_id NUMBER, p_stream_type_subclass VARCHAR2)
1556 IS
1557 SELECT kleb.percent_stake percent_stake
1558 FROM OKL_K_LINES kleb
1559 ,OKC_K_LINES_B cles
1560 ,OKC_LINE_STYLES_B lseb
1561 WHERE kleb.id = cles.id
1562 AND cles.cle_id = p_tl_id
1563 AND cles.lse_id = lseb.id
1564 AND lseb.lty_code = 'REVENUE_SHARE'
1565 AND kleb.stream_type_subclass = p_stream_type_subclass;
1566
1567 /* ankushar , 16-01-2008 Bug 6740000
1568 End Changes
1569 */
1570 -- cursor to get contract number
1571 CURSOR contract_number_csr (p_khr_id NUMBER) IS
1572 SELECT contract_number,
1573 scs_code
1574 FROM OKC_K_HEADERS_B
1575 WHERE id = p_khr_id;
1576
1577 --sechawla 9-mar-09 MG Impact on IA
1578 -- Get secondary_rep_method
1579 /* CURSOR l_sec_rep_method_csr IS
1580 SELECT secondary_rep_method
1581 FROM okl_sys_acct_opts;
1582 l_sec_rep_method VARCHAR2(30);
1583 */
1584 lx_rep_product OKL_PRODUCTS_V.NAME%TYPE;
1585 lx_rep_product_id NUMBER;
1586 lx_rep_deal_type okl_product_parameters_v.deal_type%TYPE;
1587 l_rep_sty_id NUMBER;
1588 rep_stream_type_purpose VARCHAR2(30);
1589
1590 l_contracts_csr securitized_contracts_csr%ROWTYPE;
1591
1592
1593
1594 -- declaration of a parameterized cursor by zrehman on 12-Sep-2006
1595 CURSOR strm_csr (p_khr_id NUMBER,
1596 p_final_start_date DATE,
1597 p_end_date DATE,
1598 p_stream_type_purpose VARCHAR2)
1599 IS
1600 select ste.stream_element_date stream_element_date,ste.amount amount
1601 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
1602 WHERE
1603 stm.sty_id = sty.id
1604 AND ste.stm_id = stm.id
1605 AND stm.active_yn = 'Y'
1606 AND stm.say_code = 'CURR'
1607 AND stm.khr_id = p_khr_id
1608 AND ste.stream_element_date BETWEEN p_final_start_date AND p_end_date
1609 AND sty.stream_type_purpose = p_stream_type_purpose
1610 ORDER BY ste.stream_element_date;
1611
1612 --sechawla 9-mar-09 : MG Impact on IA
1613 CURSOR rep_strm_csr (p_khr_id NUMBER,
1614 p_final_start_date DATE,
1615 p_end_date DATE,
1616 p_rep_stream_type_purpose VARCHAR2)
1617 IS
1618 select ste.stream_element_date stream_element_date,ste.amount amount
1619 FROM okl_strm_type_b sty, okl_streams stm, okl_strm_elements ste
1620 WHERE
1621 stm.sty_id = sty.id
1622 AND ste.stm_id = stm.id
1623 AND stm.active_yn = 'N'
1624 AND stm.say_code = 'CURR'
1625 AND stm.purpose_code = 'REPORT'
1626 AND stm.khr_id = p_khr_id
1627 AND ste.stream_element_date BETWEEN p_final_start_date AND p_end_date
1628 AND sty.stream_type_purpose = p_rep_stream_type_purpose
1629 ORDER BY ste.stream_element_date;
1630
1631 BEGIN
1632 -- Set save point
1633 l_return_status := OKL_API.START_ACTIVITY(
1634 p_api_name => l_api_name,
1635 p_pkg_name => G_PKG_NAME,
1636 p_init_msg_list => p_init_msg_list,
1637 l_api_version => l_api_version,
1638 p_api_version => p_api_version,
1639 p_api_type => '_PVT',
1640 x_return_status => l_return_status);
1641 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1642 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1643 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1644 RAISE Okl_Api.G_EXCEPTION_ERROR;
1645 END IF;
1646
1647 OPEN contract_number_csr(p_khr_id);
1648 FETCH contract_number_csr INTO l_khr_num,
1649 l_scs_code;
1650 CLOSE contract_number_csr;
1651
1652
1653 IF l_scs_code = 'LEASE' THEN
1654 -- If the contract is lease contract then we call create_lease_streams
1655 -- This procedure is called from On-line and Mass Rebook (during activation of rebook)
1656 -- p_khr_id is the contract_id of the original lease contract that is being rebooked.
1657 -- This procedure regenerates Investor Accrual streams that are created for the contract, when IA was activated
1658 -- Rebook process then compares the old and new Investor accrual streams to calculate Investor accrual adjustment
1659
1660 --sechawla 09-mar-09 : MG Impact on Investor Agreement - Modify create_lease_streams to regenerate the
1661 -- Investor Accrual streams upon rebook
1662 create_lease_streams(p_api_version => p_api_version,
1663 p_init_msg_list => p_init_msg_list,
1664 x_return_status => l_return_status,
1665 x_msg_count => x_msg_count,
1666 x_msg_data => x_msg_data,
1667 p_khr_id => p_khr_id,
1668 p_mode => p_mode);
1669 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1670 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1671 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
1672 RAISE Okl_Api.G_EXCEPTION_ERROR;
1673 END IF;
1674 -- If the contract is Investor contract then we call the below
1675 ELSIF l_scs_code = 'INVESTOR' THEN
1676 -- validate in parameters
1677 IF p_khr_id IS NULL OR
1678 p_khr_id = OKL_API.G_MISS_NUM THEN
1679 -- store SQL error message on message stack for caller
1680 Okl_Api.set_message(p_app_name => g_app_name,
1681 p_msg_name => 'OKL_ASC_KHR_ID_ERROR');
1682 RAISE Okl_Api.G_EXCEPTION_ERROR;
1683 END IF;
1684
1685
1686
1687 IF p_mode IS NULL THEN --sechawla : This part of the code generates streams for the new IA which is getting activated
1688 OPEN securitized_contracts_csr (p_khr_id); --ID of Investor Agreement
1689 LOOP
1690 /* sosharma 06-02-2007
1691 Initilized the local variable l_revenue_share
1692 Start changes
1693 */
1694 l_revenue_share := 0;
1695 /*
1696 sosharma end changes
1697 */
1698 FETCH securitized_contracts_csr INTO l_contracts_csr;
1699 EXIT WHEN securitized_contracts_csr%NOTFOUND;
1700 DECLARE
1701 TYPE ref_cursor IS REF CURSOR;
1702 TYPE element_type IS RECORD (stream_element_date DATE, amount NUMBER);
1703 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1704 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1705 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1706 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1707 l_difference NUMBER := 0;
1708 l_counter NUMBER := 1;
1709 l_line_number NUMBER := 1;
1710 --l_stmt VARCHAR2(5000);
1711 --l_where VARCHAR2(2000) := ' ';
1712 l_kle_id NUMBER;
1713 l_start_date DATE;
1714 l_final_start_date DATE;
1715 ln_days NUMBER := 0;
1716 l_arrears VARCHAR2(1);
1717 l_frequency NUMBER;
1718 l_contract_number VARCHAR2(2000);
1719 --strm_csr ref_cursor;
1720 l_elements element_type;
1721
1722 --sechawla : 9-mar-2009 MG Impact on IA
1723 l_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1724 l_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1725 x_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1726 x_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
1727 l_rep_elements element_type;
1728 l_rep_line_number NUMBER := 1;
1729 l_rep_counter NUMBER := 1;
1730 BEGIN
1731 OPEN contract_number_csr(l_contracts_csr.khr_id);
1732 FETCH contract_number_csr INTO l_contract_number, l_scs_code; -- Lease contract in the pool
1733 CLOSE contract_number_csr;
1734
1735 OPEN get_kle_id_csr(l_contracts_csr.khr_id);
1736 FETCH get_kle_id_csr INTO l_kle_id, l_start_date;
1737 CLOSE get_kle_id_csr;
1738
1739 IF l_kle_id IS NULL OR
1740 l_kle_id = OKL_API.G_MISS_NUM THEN
1741 Okl_Api.set_message(p_app_name => g_app_name,
1742 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
1743 p_token1 => g_contract_number_token,
1744 p_token1_value => l_contract_number);
1745 RAISE OKL_API.G_EXCEPTION_ERROR;
1746 END IF;
1747
1748 IF l_start_date IS NULL OR l_start_date = OKL_API.G_MISS_DATE THEN
1749 Okl_Api.set_message(p_app_name => g_app_name,
1750 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
1751 RAISE OKL_API.G_EXCEPTION_ERROR;
1752 END IF;
1753
1754 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
1755 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
1756 CLOSE get_adv_arr_csr;
1757
1758 IF l_frequency IS NULL THEN
1759 Okl_Api.set_message(p_app_name => g_app_name,
1760 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
1761 p_token1 => g_contract_number_token,
1762 p_token1_value => l_contract_number);
1763 RAISE OKL_API.G_EXCEPTION_ERROR;
1764 END IF;
1765
1766 IF l_arrears = 'Y' THEN
1767 ln_days := okl_stream_generator_pvt.get_day_count (
1768 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
1769 p_end_date => l_start_date,
1770 p_arrears => l_arrears,
1771 x_return_status => l_return_status);
1772 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1773 RAISE okl_api.g_exception_unexpected_error;
1774 ELSIF (l_return_Status = okl_api.g_ret_sts_error) THEN
1775 RAISE okl_api.g_exception_error;
1776 END IF;
1777 l_final_start_date := l_start_date - ln_days;
1778 ELSIF NVL(l_arrears,'N') = 'N' THEN
1779 l_final_start_date := l_start_date;
1780 END IF;
1781
1782 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin--------------
1783 okl_accounting_util.get_reporting_product(
1784 p_api_version => l_api_version,
1785 p_init_msg_list => p_init_msg_list,
1786 x_return_status => l_return_status,
1787 x_msg_count => x_msg_count,
1788 x_msg_data => x_msg_data,
1789 p_contract_id => l_contracts_csr.khr_id,
1790 x_rep_product => lx_rep_product,
1791 x_rep_product_id => lx_rep_product_id,
1792 x_rep_deal_type => lx_rep_deal_type);
1793
1794 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1795 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1796 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1797 RAISE OKL_API.G_EXCEPTION_ERROR;
1798 END IF;
1799
1800 /*
1801 --Check the secondary_rep_method
1802 OPEN l_sec_rep_method_csr ;
1803 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
1804 IF l_sec_rep_method_csr%NOTFOUND THEN
1805 okl_api.set_message(p_app_name => g_app_name,
1806 p_msg_name => 'OKL_NO_SEC_REP_METHOD' --> seed this ''Secondary rep method cursor did not return any records''
1807 );
1808 RAISE okl_api.g_exception_error;
1809 END IF;
1810 CLOSE l_sec_rep_method_csr ;
1811 */
1812 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end --------------
1813
1814
1815
1816 -- commenting as all accrual streams are generated
1817 -- at contract level. Will remove comments after super trump fix is provided
1818 -- for stream generation at asset level.Ref cursor will be needed later.
1819 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
1820
1821 -- SQL Literals Change on 12/09/2006
1822 /*l_stmt := 'SELECT ste.stream_element_date stream_element_date
1823 ,ste.amount amount
1824 FROM OKL_STRM_TYPE_B sty
1825 ,OKL_STREAMS stm
1826 ,OKL_STRM_ELEMENTS ste
1827 WHERE 1 = 1
1828 AND stm.sty_id = sty.id
1829 AND ste.stm_id = stm.id
1830 AND stm.active_yn = '||''''||'Y'||''''||'
1831 AND stm.say_code = '||''''||'CURR'||'''' ;
1832 l_where := l_where || ' AND stm.khr_id = ' || l_contracts_csr.khr_id ||' AND ste.stream_element_date BETWEEN '|| ''''
1833 ||l_final_start_date|| '''' ||' AND '|| '''' ||l_contracts_csr.end_date|| ''''; */
1834
1835 /* ankushar , 25-01-2008 Bug 6773285
1836 Added code to generate new Stream Types for a Loan product on an Investor Agreement
1837 Start Changes
1838 */
1839 --get sty_id for the contract based on deal type
1840 IF l_contracts_csr.deal_type = 'LEASEOP' THEN -- deal type of primary product of the contract
1841 OKL_STREAMS_UTIL.get_primary_stream_type
1842 (
1843 p_khr_id => l_contracts_csr.khr_id,
1844 p_primary_sty_purpose => l_investor_rental_accrual,
1845 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of OP lease contract
1846 --sechawla : Investor Arental Accrual stream is generated when IA is activated
1847 x_return_status => l_return_status,
1848 x_primary_sty_id => l_sty_id
1849 );
1850 IF l_return_status <> 'S' THEN
1851 okl_api.set_message(p_app_name => g_app_name,
1852 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1853 p_token1 => 'STREAM_NAME',
1854 p_token1_value => l_investor_rental_accrual);
1855 RAISE okl_api.g_exception_error;
1856
1857 END IF;
1858
1859 -- calculate total revenue share
1860 FOR x IN get_investors_csr(p_khr_id) LOOP
1861 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1862 l_revenue_share := l_revenue_share + y.percent_stake;
1863 END LOOP;
1864 END LOOP;
1865
1866 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1867 -- store SQL error message on message stack for caller
1868 Okl_Api.set_message(p_app_name => g_app_name,
1869 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1870 RAISE Okl_Api.G_EXCEPTION_ERROR;
1871 END IF;
1872 --Modified by kthiruva on 19-Oct-2005 . The stream type purpose needs to be bound instead
1873 --of the stream type code
1874 --Bug 4228708 - Start of Changes
1875 -- l_where := l_where ||' AND sty.stream_type_purpose = '|| '''' ||l_rental_accrual|| '''' ||' ORDER BY ste.stream_element_date';
1876
1877
1878 stream_type_purpose := l_rental_accrual; ---- Rental Accrual for primary product
1879 -- sechawla : This is the 'Rental Accrual' stream, generated when OP lease contract is Booked
1880 -- sechawla : This stream is used to generate Investor Rental Accrual Stream, upon IA activation
1881
1882 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
1883 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
1884 -- IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
1885 IF lx_rep_product IS NOT NULL THEN
1886 IF lx_rep_deal_type = 'LEASEOP' THEN
1887 OKL_STREAMS_UTIL.get_primary_stream_type_rep
1888 (
1889 p_khr_id => l_contracts_csr.khr_id,
1890 p_primary_sty_purpose => l_investor_rental_accrual,
1891 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
1892 --sechawla : Investor Arental Accrual stream is generated when IA is activated
1893 x_return_status => l_return_status,
1894 x_primary_sty_id => l_rep_sty_id
1895 );
1896 IF l_return_status <> 'S' THEN
1897 okl_api.set_message(p_app_name => g_app_name,
1898 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
1899 p_token1 => 'STREAM_NAME',
1900 p_token1_value => l_investor_rental_accrual);
1901 RAISE okl_api.g_exception_error;
1902
1903 END IF;
1904
1905 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
1906 --Rental Accrual stream is also generated for the reporting product (if OP Lease), when contract
1907 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon IA activation
1908 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
1909 OKL_STREAMS_UTIL.get_primary_stream_type_rep
1910 (
1911 p_khr_id => l_contracts_csr.khr_id,
1912 p_primary_sty_purpose => l_investor_pre_tax_income,
1913 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
1914 --Investor Pre Tax Income stream is generated when IA is activated
1915 x_return_status => l_return_status,
1916 x_primary_sty_id => l_rep_sty_id
1917 );
1918
1919 IF l_return_status <> 'S' THEN
1920 okl_api.set_message(p_app_name => g_app_name,
1921 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
1922 p_token1 => 'STREAM_NAME',
1923 p_token1_value => l_investor_pre_tax_income);
1924 RAISE okl_api.g_exception_error;
1925 END IF;
1926
1927 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
1928 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
1929 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon IA activation
1930 END IF;
1931 END IF;
1932 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
1933
1934
1935 --Bug 4228708 - End of ChangesB
1936 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN -- deal type of primary product of the contract
1937 OKL_STREAMS_UTIL.get_primary_stream_type
1938 (
1939 p_khr_id => l_contracts_csr.khr_id,
1940 p_primary_sty_purpose => l_investor_pre_tax_income,
1941 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of DF/ST lease contract
1942 --Investor Pre Tax Income stream is generated when IA is activated
1943 x_return_status => l_return_status,
1944 x_primary_sty_id => l_sty_id
1945 );
1946
1947 IF l_return_status <> 'S' THEN
1948 okl_api.set_message(p_app_name => g_app_name,
1949 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
1950 p_token1 => 'STREAM_NAME',
1951 p_token1_value => l_investor_pre_tax_income);
1952 RAISE okl_api.g_exception_error;
1953 END IF;
1954
1955 -- calculate total revenue share
1956 FOR x IN get_investors_csr(p_khr_id) LOOP
1957 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
1958 l_revenue_share := l_revenue_share + y.percent_stake;
1959 END LOOP;
1960 END LOOP;
1961
1962 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
1963 -- store SQL error message on message stack for caller
1964 Okl_Api.set_message(p_app_name => g_app_name,
1965 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
1966 RAISE Okl_Api.G_EXCEPTION_ERROR;
1967 END IF;
1968 stream_type_purpose := l_pre_tax_income; --Pre Tax Income for Primary product
1969 -- sechawla : This is the 'Pre Tax Income' stream, generated when DF/ST lease contract is Booked
1970 -- sechawla : This stream is used to generate Investor Pre Tax Income Stream, upon IA activation
1971
1972 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
1973 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
1974 -- IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
1975 IF lx_rep_product IS NOT NULL THEN
1976 IF lx_rep_deal_type = 'LEASEOP' THEN
1977 OKL_STREAMS_UTIL.get_primary_stream_type_rep
1978 (
1979 p_khr_id => l_contracts_csr.khr_id,
1980 p_primary_sty_purpose => l_investor_rental_accrual,
1981 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
1982 --sechawla : Investor Arental Accrual stream is generated when IA is activated
1983 x_return_status => l_return_status,
1984 x_primary_sty_id => l_rep_sty_id
1985 );
1986 IF l_return_status <> 'S' THEN
1987 okl_api.set_message(p_app_name => g_app_name,
1988 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
1989 p_token1 => 'STREAM_NAME',
1990 p_token1_value => l_investor_rental_accrual);
1991 RAISE okl_api.g_exception_error;
1992
1993 END IF;
1994
1995 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
1996 --Rental Accrual stream is generated for the reporting product (if OP Lease), when contract
1997 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon IA activation
1998 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
1999 OKL_STREAMS_UTIL.get_primary_stream_type_rep
2000 (
2001 p_khr_id => l_contracts_csr.khr_id,
2002 p_primary_sty_purpose => l_investor_pre_tax_income,
2003 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
2004 --Investor Pre Tax Income stream is generated when IA is activated
2005 x_return_status => l_return_status,
2006 x_primary_sty_id => l_rep_sty_id
2007 );
2008
2009 IF l_return_status <> 'S' THEN
2010 okl_api.set_message(p_app_name => g_app_name,
2011 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
2012 p_token1 => 'STREAM_NAME',
2013 p_token1_value => l_investor_pre_tax_income);
2014 RAISE okl_api.g_exception_error;
2015 END IF;
2016
2017 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
2018 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
2019 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon IA activation
2020 END IF;
2021 END IF;
2022 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
2023
2024 /* ankushar , 25-01-2008 Bug 6773285
2025 End Changes
2026 */
2027 /* ankushar , 16-01-2008 Bug 6740000
2028 Added condition for fetching stream type for a Loan product
2029 Start Changes
2030 */
2031 ---sechawla 09-mar-09 : MG Impact on Investor Agreement : No impacts on Loans
2032 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
2033 OKL_STREAMS_UTIL.get_primary_stream_type
2034 (
2035 p_khr_id => l_contracts_csr.khr_id,
2036 p_primary_sty_purpose => l_inv_interest_income_accrual,
2037 x_return_status => l_return_status,
2038 x_primary_sty_id => l_sty_id
2039 );
2040 IF l_return_status <> 'S' THEN
2041 okl_api.set_message(p_app_name => g_app_name,
2042 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
2043 p_token1 => 'STREAM_NAME',
2044 p_token1_value => l_inv_interest_income_accrual);
2045 RAISE okl_api.g_exception_error;
2046 END IF;
2047
2048 -- calculate total revenue share
2049 FOR x IN get_investors_csr(p_khr_id) LOOP
2050 FOR y IN get_revenue_share_csr(x.id, 'LOAN_PAYMENT') LOOP
2051 l_revenue_share := l_revenue_share + y.percent_stake;
2052 END LOOP;
2053 END LOOP;
2054
2055 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
2056 -- store SQL error message on message stack for caller
2057 Okl_Api.set_message(p_app_name => g_app_name,
2058 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
2059 RAISE Okl_Api.G_EXCEPTION_ERROR;
2060 END IF;
2061
2062 stream_type_purpose := l_interest_income;
2063 /* ankushar , 16-01-2008 Bug 6740000
2064 End Changes
2065 */
2066 END IF; -- ELSIF
2067
2068 --Populate streams structure for primary product
2069 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
2070 -- populate stream header record
2071 l_stmv_rec.sty_id := l_sty_id;
2072 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
2073 l_stmv_rec.sgn_code := 'MANL';
2074 l_stmv_rec.say_code := 'CURR';
2075 l_stmv_rec.transaction_number := l_trx_number;
2076 l_stmv_rec.active_yn := 'Y';
2077 l_stmv_rec.date_current := l_sysdate;
2078 l_stmv_rec.source_id := p_khr_id;
2079 l_stmv_rec.source_table := 'OKL_K_HEADERS';
2080
2081
2082 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
2083 IF l_rep_sty_id IS NOT NULL THEN
2084 --Populate streams structure for reporting product
2085 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
2086 -- populate stream header record
2087 l_rep_stmv_rec.sty_id := l_rep_sty_id;
2088 l_rep_stmv_rec.khr_id := l_contracts_csr.khr_id;
2089 l_rep_stmv_rec.sgn_code := 'MANL';
2090 l_rep_stmv_rec.say_code := 'CURR';
2091 l_rep_stmv_rec.transaction_number := l_trx_number;
2092 l_rep_stmv_rec.active_yn := 'N';
2093 l_rep_stmv_rec.purpose_code := 'REPORT';
2094 l_rep_stmv_rec.date_current := l_sysdate;
2095 l_rep_stmv_rec.source_id := p_khr_id;
2096 l_rep_stmv_rec.source_table := 'OKL_K_HEADERS';
2097 END IF;
2098 -----------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ------------
2099
2100
2101 -- create final l_stmt
2102 --l_stmt := l_stmt || l_where;
2103 --OPEN strm_csr FOR l_stmt;
2104 -- use of a parameterized cursor by zrehman on 12-Sep-2006
2105 --Create stream element structure for primary product
2106 OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
2107 LOOP
2108 --re-initialize period end date
2109 l_period_end_date := NULL;
2110 FETCH strm_csr INTO l_elements;
2111 EXIT WHEN strm_csr%NOTFOUND;
2112 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
2113 --populate stream elements tbl
2114 -- manipulate first record
2115 IF strm_csr%ROWCOUNT = 1 THEN
2116 -- If start date is last day of the month, do nothing.
2117 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
2118 -- If start date is the same as first day of the month then take whole amount.
2119 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
2120 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
2121 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2122 l_selv_tbl(l_counter).se_line_number := l_line_number;
2123 l_line_number := l_line_number + 1;
2124 l_counter := l_counter + 1;
2125 ELSE
2126 -- start date is not first or last day of the month. so prorate.
2127 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
2128 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
2129 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2130 l_selv_tbl(l_counter).se_line_number := l_line_number;
2131 l_line_number := l_line_number + 1;
2132 l_counter := l_counter + 1;
2133 END IF;
2134 END IF;
2135 ELSE
2136 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
2137 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2138 l_selv_tbl(l_counter).se_line_number := l_line_number;
2139 l_line_number := l_line_number + 1;
2140 l_counter := l_counter + 1;
2141 END IF;
2142 END LOOP;
2143 CLOSE strm_csr;
2144 IF l_selv_tbl.COUNT > 0 THEN
2145 -- call streams api
2146 OKL_STREAMS_PUB.create_streams(
2147 p_api_version => l_api_version
2148 ,p_init_msg_list => l_init_msg_list
2149 ,x_return_status => l_return_status
2150 ,x_msg_count => l_msg_count
2151 ,x_msg_data => l_msg_data
2152 ,p_stmv_rec => l_stmv_rec
2153 ,p_selv_tbl => l_selv_tbl
2154 ,x_stmv_rec => x_stmv_rec
2155 ,x_selv_tbl => x_selv_tbl );
2156 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2157 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2158 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2159 RAISE Okl_Api.G_EXCEPTION_ERROR;
2160 END IF;
2161 END IF;
2162
2163 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
2164 --Create stream element structure for reporting product
2165 OPEN rep_strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, rep_stream_type_purpose);
2166 LOOP
2167 --re-initialize period end date
2168 l_period_end_date := NULL;
2169 FETCH rep_strm_csr INTO l_rep_elements;
2170 EXIT WHEN rep_strm_csr%NOTFOUND;
2171 l_period_end_date := trunc(last_day(l_rep_elements.stream_element_date));
2172 --populate stream elements tbl
2173 -- manipulate first record
2174 IF rep_strm_csr%ROWCOUNT = 1 THEN
2175 -- If start date is last day of the month, do nothing.
2176 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
2177 -- If start date is the same as first day of the month then take whole amount.
2178 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
2179 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
2180 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2181 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2182 l_rep_line_number := l_rep_line_number + 1;
2183 l_rep_counter := l_rep_counter + 1;
2184 ELSE
2185 -- start date is not first or last day of the month. so prorate.
2186 l_difference := ABS(TRUNC(l_rep_elements.stream_element_date) - TRUNC(l_final_start_date));
2187 l_rep_selv_tbl(l_rep_counter).amount := ROUND((((l_difference/30)*l_rep_elements.amount)*l_revenue_share/100),2);
2188 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2189 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2190 l_rep_line_number := l_rep_line_number + 1;
2191 l_rep_counter := l_rep_counter + 1;
2192 END IF;
2193 END IF;
2194 ELSE
2195 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
2196 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2197 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2198 l_rep_line_number := l_rep_line_number + 1;
2199 l_rep_counter := l_rep_counter + 1;
2200 END IF;
2201 END LOOP;
2202 CLOSE rep_strm_csr;
2203 IF l_rep_selv_tbl.COUNT > 0 THEN
2204 -- call streams api
2205 OKL_STREAMS_PUB.create_streams(
2206 p_api_version => l_api_version
2207 ,p_init_msg_list => l_init_msg_list
2208 ,x_return_status => l_return_status
2209 ,x_msg_count => l_msg_count
2210 ,x_msg_data => l_msg_data
2211 ,p_stmv_rec => l_rep_stmv_rec
2212 ,p_selv_tbl => l_rep_selv_tbl
2213 ,x_stmv_rec => x_rep_stmv_rec
2214 ,x_selv_tbl => x_rep_selv_tbl );
2215 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2216 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2217 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2218 RAISE Okl_Api.G_EXCEPTION_ERROR;
2219 END IF;
2220 END IF;
2221
2222 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ---------------
2223 EXCEPTION
2224 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2225 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2226 ,g_pkg_name
2227 ,'OKL_API.G_RET_STS_ERROR'
2228 ,x_msg_count
2229 ,x_msg_data
2230 ,'_PVT');
2231 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2232 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2233 ,g_pkg_name
2234 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2235 ,x_msg_count
2236 ,x_msg_data
2237 ,'_PVT');
2238 WHEN OTHERS THEN
2239 IF get_kle_id_csr%ISOPEN THEN
2240 CLOSE get_kle_id_csr;
2241 END IF;
2242 IF get_adv_arr_csr%ISOPEN THEN
2243 CLOSE get_adv_arr_csr;
2244 END IF;
2245
2246 IF get_sty_id_csr%ISOPEN THEN
2247 CLOSE get_sty_id_csr;
2248 END IF;
2249
2250 IF strm_csr%ISOPEN THEN
2251 CLOSE strm_csr;
2252 END IF;
2253 l_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
2254 G_PKG_NAME,
2255 'OTHERS',
2256 x_msg_count,
2257 x_msg_data,
2258 '_PVT');
2259 END;
2260 END LOOP;
2261 CLOSE securitized_contracts_csr;
2262
2263 ELSE --sechawla : p_mode is not null (ACTIVE) : This section of the code handles stream generation
2264 --only for the newly added pool contents, that are added after IA was activated
2265 OPEN securitized_contracts_pend_csr (p_khr_id);
2266 LOOP
2267 FETCH securitized_contracts_pend_csr INTO l_contracts_csr;
2268 EXIT WHEN securitized_contracts_pend_csr%NOTFOUND;
2269 DECLARE
2270 TYPE ref_cursor IS REF CURSOR;
2271 TYPE element_type IS RECORD (stream_element_date DATE, amount NUMBER);
2272 l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
2273 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2274 x_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
2275 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2276 l_difference NUMBER := 0;
2277 l_counter NUMBER := 1;
2278 l_line_number NUMBER := 1;
2279 --l_stmt VARCHAR2(5000);
2280 --l_where VARCHAR2(2000) := ' ';
2281 l_kle_id NUMBER;
2282 l_start_date DATE;
2283 l_final_start_date DATE;
2284 ln_days NUMBER := 0;
2285 l_arrears VARCHAR2(1);
2286 l_frequency NUMBER;
2287 l_contract_number VARCHAR2(2000);
2288 --strm_csr ref_cursor;
2289 l_elements element_type;
2290
2291 --sechawla : 9-mar-2009 MG Impact on IA
2292 l_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
2293 l_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2294 x_rep_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
2295 x_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2296 l_rep_elements element_type;
2297 l_rep_line_number NUMBER := 1;
2298 l_rep_counter NUMBER := 1;
2299
2300 BEGIN
2301 OPEN contract_number_csr(l_contracts_csr.khr_id);
2302 FETCH contract_number_csr INTO l_contract_number,
2303 l_scs_code;
2304 CLOSE contract_number_csr;
2305
2306 OPEN get_kle_id_pend_csr(l_contracts_csr.khr_id);
2307 FETCH get_kle_id_pend_csr INTO l_kle_id, l_start_date;
2308 CLOSE get_kle_id_pend_csr;
2309
2310 IF l_kle_id IS NULL OR
2311 l_kle_id = OKL_API.G_MISS_NUM THEN
2312 Okl_Api.set_message(p_app_name => g_app_name,
2313 p_msg_name => 'OKL_ASC_KLE_ID_ERROR',
2314 p_token1 => g_contract_number_token,
2315 p_token1_value => l_contract_number);
2316 RAISE OKL_API.G_EXCEPTION_ERROR;
2317 END IF;
2318
2319 IF l_start_date IS NULL OR l_start_date = OKL_API.G_MISS_DATE THEN
2320 Okl_Api.set_message(p_app_name => g_app_name,
2321 p_msg_name => 'OKL_ASC_START_DATE_ERROR');
2322 RAISE OKL_API.G_EXCEPTION_ERROR;
2323 END IF;
2324
2325 OPEN get_adv_arr_csr(l_contracts_csr.khr_id, l_kle_id);
2326 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
2327 CLOSE get_adv_arr_csr;
2328
2329 IF l_frequency IS NULL THEN
2330 Okl_Api.set_message(p_app_name => g_app_name,
2331 p_msg_name => 'OKL_ASC_FREQUENCY_ERROR',
2332 p_token1 => g_contract_number_token,
2333 p_token1_value => l_contract_number);
2334 RAISE OKL_API.G_EXCEPTION_ERROR;
2335 END IF;
2336
2337 IF l_arrears = 'Y' THEN
2338 ln_days := okl_stream_generator_pvt.get_day_count (
2339 p_start_date => ADD_MONTHS(l_start_date, -l_frequency),
2340 p_end_date => l_start_date,
2341 p_arrears => l_arrears,
2342 x_return_status => l_return_status);
2343 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2344 RAISE okl_api.g_exception_unexpected_error;
2345 ELSIF (l_return_Status = okl_api.g_ret_sts_error) THEN
2346 RAISE okl_api.g_exception_error;
2347 END IF;
2348 l_final_start_date := l_start_date - ln_days;
2349 ELSIF NVL(l_arrears,'N') = 'N' THEN
2350 l_final_start_date := l_start_date;
2351 END IF;
2352
2353 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin--------------
2354 okl_accounting_util.get_reporting_product(
2355 p_api_version => l_api_version,
2356 p_init_msg_list => p_init_msg_list,
2357 x_return_status => l_return_status,
2358 x_msg_count => x_msg_count,
2359 x_msg_data => x_msg_data,
2360 p_contract_id => l_contracts_csr.khr_id,
2361 x_rep_product => lx_rep_product,
2362 x_rep_product_id => lx_rep_product_id,
2363 x_rep_deal_type => lx_rep_deal_type);
2364
2365 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2366 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2367 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2368 RAISE OKL_API.G_EXCEPTION_ERROR;
2369 END IF;
2370
2371 /*
2372 --Check the secondary_rep_method
2373 OPEN l_sec_rep_method_csr ;
2374 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
2375 IF l_sec_rep_method_csr%NOTFOUND THEN
2376 okl_api.set_message(p_app_name => g_app_name,
2377 p_msg_name => 'OKL_NO_SEC_REP_METHOD' --> seed this ''Secondary rep method cursor did not return any records''
2378 );
2379 RAISE okl_api.g_exception_error;
2380 END IF;
2381 CLOSE l_sec_rep_method_csr ;
2382 */
2383 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end --------------
2384 -- commenting as all accrual streams are generated
2385 -- at contract level. Will remove comments after super trump fix is provided
2386 -- for stream generation at asset level.Ref cursor will be needed later.
2387 --IF l_contracts_csr.deal_type IN ('LEASEOP','LEASEDF','LEASEST') THEN
2388
2389
2390 --get sty_id for the contract based on deal type
2391 /* ankushar , 25-01-2008 Bug 6773285
2392 Added code to generate new Stream Types for a Loan product on an Investor Agreement
2393 Start Changes
2394 */
2395 --get sty_id for the contract based on deal type
2396 IF l_contracts_csr.deal_type = 'LEASEOP' THEN -- deal type of primary product of the contract
2397 OKL_STREAMS_UTIL.get_primary_stream_type
2398 (
2399 p_khr_id => l_contracts_csr.khr_id,
2400 p_primary_sty_purpose => l_investor_rental_accrual,
2401 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of OP lease contract
2402 --sechawla : Investor Arental Accrual stream is generated when IA is activated
2403 x_return_status => l_return_status,
2404 x_primary_sty_id => l_sty_id
2405 );
2406 IF l_return_status <> 'S' THEN
2407 okl_api.set_message(p_app_name => g_app_name,
2408 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
2409 p_token1 => 'STREAM_NAME',
2410 p_token1_value => l_investor_rental_accrual);
2411 RAISE okl_api.g_exception_error;
2412
2413 END IF;
2414 -- calculate total revenue share
2415 FOR x IN get_investors_csr(p_khr_id) LOOP
2416 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
2417 l_revenue_share := l_revenue_share + y.percent_stake;
2418 END LOOP;
2419 END LOOP;
2420
2421 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
2422 -- store SQL error message on message stack for caller
2423 Okl_Api.set_message(p_app_name => g_app_name,
2424 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
2425 RAISE Okl_Api.G_EXCEPTION_ERROR;
2426 END IF;
2427 --Modified by kthiruva on 19-Oct-2005 . The stream type purpose needs to be bound instead
2428 --of the stream type code
2429 --Bug 4228708 - Start of Changes
2430 -- l_where := l_where ||' AND sty.stream_type_purpose = '|| '''' ||l_rental_accrual|| '''' ||' ORDER BY ste.stream_element_date';
2431 stream_type_purpose := l_rental_accrual;---- Rental Accrual for primary product
2432 -- sechawla : This is the 'Rental Accrual' stream, generated when OP lease contract is Booked
2433 -- sechawla : This stream is used to generate Investor Rental Accrual Stream, upon IA activation
2434
2435 --Bug 4228708 - End of Changes
2436
2437
2438 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
2439 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
2440 --IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
2441 IF lx_rep_product IS NOT NULL THEN
2442 IF lx_rep_deal_type = 'LEASEOP' THEN
2443 OKL_STREAMS_UTIL.get_primary_stream_type_rep
2444 (
2445 p_khr_id => l_contracts_csr.khr_id,
2446 p_primary_sty_purpose => l_investor_rental_accrual,
2447 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
2448 --sechawla : Investor Arental Accrual stream is generated when IA is activated
2449 x_return_status => l_return_status,
2450 x_primary_sty_id => l_rep_sty_id
2451 );
2452 IF l_return_status <> 'S' THEN
2453 okl_api.set_message(p_app_name => g_app_name,
2454 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
2455 p_token1 => 'STREAM_NAME',
2456 p_token1_value => l_investor_rental_accrual);
2457 RAISE okl_api.g_exception_error;
2458
2459 END IF;
2460
2461 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
2462 --Rental Accrual stream is also generated for the reporting product (if OP Lease), when contract
2463 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon IA activation
2464 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
2465 OKL_STREAMS_UTIL.get_primary_stream_type_rep
2466 (
2467 p_khr_id => l_contracts_csr.khr_id,
2468 p_primary_sty_purpose => l_investor_pre_tax_income,
2469 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
2470 --Investor Pre Tax Income stream is generated when IA is activated
2471 x_return_status => l_return_status,
2472 x_primary_sty_id => l_rep_sty_id
2473 );
2474
2475 IF l_return_status <> 'S' THEN
2476 okl_api.set_message(p_app_name => g_app_name,
2477 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
2478 p_token1 => 'STREAM_NAME',
2479 p_token1_value => l_investor_pre_tax_income);
2480 RAISE okl_api.g_exception_error;
2481 END IF;
2482
2483 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
2484 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
2485 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon IA activation
2486 END IF;
2487 END IF;
2488 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
2489
2490 ELSIF l_contracts_csr.deal_type IN ('LEASEDF', 'LEASEST') THEN -- deal type of primary product of the contract
2491 OKL_STREAMS_UTIL.get_primary_stream_type
2492 (
2493 p_khr_id => l_contracts_csr.khr_id,
2494 p_primary_sty_purpose => l_investor_pre_tax_income,
2495 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of DF/ST lease contract
2496 --Investor Pre Tax Income stream is generated when IA is activated
2497 x_return_status => l_return_status,
2498 x_primary_sty_id => l_sty_id
2499 );
2500
2501 IF l_return_status <> 'S' THEN
2502 okl_api.set_message(p_app_name => g_app_name,
2503 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
2504 p_token1 => 'STREAM_NAME',
2505 p_token1_value => l_investor_pre_tax_income);
2506 RAISE okl_api.g_exception_error;
2507 END IF;
2508
2509 -- calculate total revenue share
2510 FOR x IN get_investors_csr(p_khr_id) LOOP
2511 FOR y IN get_revenue_share_csr(x.id, 'RENT') LOOP
2512 l_revenue_share := l_revenue_share + y.percent_stake;
2513 END LOOP;
2514 END LOOP;
2515
2516 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
2517 -- store SQL error message on message stack for caller
2518 Okl_Api.set_message(p_app_name => g_app_name,
2519 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
2520 RAISE Okl_Api.G_EXCEPTION_ERROR;
2521 END IF;
2522 stream_type_purpose := l_pre_tax_income;--Pre Tax Income for Primary product
2523 -- sechawla : This is the 'Pre Tax Income' stream, generated when DF/ST lease contract is Booked
2524 -- sechawla : This stream is used to generate Investor Pre Tax Income Stream, upon IA activation
2525
2526
2527 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement begin ----
2528 ---generate Investor Rental Accrual / Pre Tax Income streams for reporting product
2529 --IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
2530 IF lx_rep_product IS NOT NULL THEN
2531 IF lx_rep_deal_type = 'LEASEOP' THEN
2532 OKL_STREAMS_UTIL.get_primary_stream_type_rep
2533 (
2534 p_khr_id => l_contracts_csr.khr_id,
2535 p_primary_sty_purpose => l_investor_rental_accrual,
2536 --sechawla : INVESTOR_RENTAL_ACCRUAL is the primary stream type purpose on the SGT of reporing product(OP lease)
2537 --sechawla : Investor Arental Accrual stream is generated when IA is activated
2538 x_return_status => l_return_status,
2539 x_primary_sty_id => l_rep_sty_id
2540 );
2541 IF l_return_status <> 'S' THEN
2542 okl_api.set_message(p_app_name => g_app_name,
2543 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
2544 p_token1 => 'STREAM_NAME',
2545 p_token1_value => l_investor_rental_accrual);
2546 RAISE okl_api.g_exception_error;
2547
2548 END IF;
2549
2550 rep_stream_type_purpose := l_rental_accrual; -- Rental Accrual for reporting product
2551 --Rental Accrual stream is generated for the reporting product (if OP Lease), when contract
2552 --is Booked. This stream is used to generate Investor Rental Accrual Stream for reporting product, upon IA activation
2553 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
2554 OKL_STREAMS_UTIL.get_primary_stream_type_rep
2555 (
2556 p_khr_id => l_contracts_csr.khr_id,
2557 p_primary_sty_purpose => l_investor_pre_tax_income,
2558 --INVESTOR_PRE_TAX_INCOME is the primary stream type purpose on the SGT of reporting product (DF/ST)
2559 --Investor Pre Tax Income stream is generated when IA is activated
2560 x_return_status => l_return_status,
2561 x_primary_sty_id => l_rep_sty_id
2562 );
2563
2564 IF l_return_status <> 'S' THEN
2565 okl_api.set_message(p_app_name => g_app_name,
2566 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR_REP', --> seed this
2567 p_token1 => 'STREAM_NAME',
2568 p_token1_value => l_investor_pre_tax_income);
2569 RAISE okl_api.g_exception_error;
2570 END IF;
2571
2572 rep_stream_type_purpose := l_pre_tax_income; -- Pre Tax Income for reporting product
2573 --Pre Tax income stream is also generated for the reporting product (if DF/ST Lease), when contract
2574 --is Booked. This stream is used to generate Investor Pre Tax Income Stream for reporting product, upon IA activation
2575 END IF;
2576 END IF;
2577 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end -----
2578
2579
2580 /* ankushar , 25-01-2008 Bug 6773285
2581 End Changes
2582 */
2583 /* ankushar , 16-01-2008 Bug 6740000
2584 Added condition for fetching stream type for a Loan product
2585 Start Changes
2586 */
2587 ---sechawla 09-mar-09 : MG Impact on Investor Agreement : No impacts on Loans
2588 ELSIF l_contracts_csr.deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
2589 OKL_STREAMS_UTIL.get_primary_stream_type
2590 (
2591 p_khr_id => l_contracts_csr.khr_id,
2592 p_primary_sty_purpose => l_inv_interest_income_accrual,
2593 x_return_status => l_return_status,
2594 x_primary_sty_id => l_sty_id
2595 );
2596 IF l_return_status <> 'S' THEN
2597 okl_api.set_message(p_app_name => g_app_name,
2598 p_msg_name => 'OKL_AGN_STRM_TYPE_ERROR',
2599 p_token1 => 'STREAM_NAME',
2600 p_token1_value => l_inv_interest_income_accrual);
2601 RAISE okl_api.g_exception_error;
2602 END IF;
2603
2604 -- calculate total revenue share
2605 FOR x IN get_investors_csr(p_khr_id) LOOP
2606 FOR y IN get_revenue_share_csr(x.id, 'LOAN_PAYMENT') LOOP
2607 l_revenue_share := l_revenue_share + y.percent_stake;
2608 END LOOP;
2609 END LOOP;
2610
2611 IF l_revenue_share IS NULL OR l_revenue_share = 0 THEN
2612 -- store SQL error message on message stack for caller
2613 Okl_Api.set_message(p_app_name => g_app_name,
2614 p_msg_name => 'OKL_ASC_REV_SHARE_ERROR');
2615 RAISE Okl_Api.G_EXCEPTION_ERROR;
2616 END IF;
2617
2618 stream_type_purpose := l_interest_income;
2619 /* ankushar , 16-01-2008 Bug 6740000
2620 End Changes
2621 */
2622 END IF;
2623
2624 --Populate streams structure for primary product
2625 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
2626 -- populate stream header record
2627 l_stmv_rec.sty_id := l_sty_id;
2628 l_stmv_rec.khr_id := l_contracts_csr.khr_id;
2629 l_stmv_rec.sgn_code := 'MANL';
2630 l_stmv_rec.say_code := 'CURR';
2631 l_stmv_rec.transaction_number := l_trx_number;
2632 l_stmv_rec.active_yn := 'Y';
2633 l_stmv_rec.date_current := l_sysdate;
2634 l_stmv_rec.source_id := p_khr_id;
2635 l_stmv_rec.source_table := 'OKL_K_HEADERS';
2636 -- create final l_stmt
2637 --l_stmt := l_stmt || l_where;
2638 --OPEN strm_csr FOR l_stmt;
2639 -- use of a parameterized cursor by zrehman on 12-Sep-2006
2640
2641 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
2642 IF l_rep_sty_id IS NOT NULL THEN
2643 --Populate streams structure for reporting product
2644 SELECT okl_sif_seq.NEXTVAL INTO l_trx_number FROM dual;
2645 -- populate stream header record
2646 l_rep_stmv_rec.sty_id := l_rep_sty_id;
2647 l_rep_stmv_rec.khr_id := l_contracts_csr.khr_id;
2648 l_rep_stmv_rec.sgn_code := 'MANL';
2649 l_rep_stmv_rec.say_code := 'CURR';
2650 l_rep_stmv_rec.transaction_number := l_trx_number;
2651 l_rep_stmv_rec.active_yn := 'N';
2652 l_rep_stmv_rec.purpose_code := 'REPORT';
2653 l_rep_stmv_rec.date_current := l_sysdate;
2654 l_rep_stmv_rec.source_id := p_khr_id;
2655 l_rep_stmv_rec.source_table := 'OKL_K_HEADERS';
2656 END IF;
2657 -----------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ------------
2658
2659 OPEN strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, stream_type_purpose);
2660 LOOP
2661 --re-initialize period end date
2662 l_period_end_date := NULL;
2663 FETCH strm_csr INTO l_elements;
2664 EXIT WHEN strm_csr%NOTFOUND;
2665 l_period_end_date := trunc(last_day(l_elements.stream_element_date));
2666 --populate stream elements tbl
2667 -- manipulate first record
2668 IF strm_csr%ROWCOUNT = 1 THEN
2669 -- If start date is last day of the month, do nothing.
2670 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
2671 -- If start date is the same as first day of the month then take whole amount.
2672 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
2673 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
2674 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2675 l_selv_tbl(l_counter).se_line_number := l_line_number;
2676 l_line_number := l_line_number + 1;
2677 l_counter := l_counter + 1;
2678 ELSE
2679 -- start date is not first or last day of the month. so prorate.
2680 l_difference := ABS(TRUNC(l_elements.stream_element_date) - TRUNC(l_final_start_date));
2681 l_selv_tbl(l_counter).amount := ROUND((((l_difference/30)*l_elements.amount)*l_revenue_share/100),2);
2682 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2683 l_selv_tbl(l_counter).se_line_number := l_line_number;
2684 l_line_number := l_line_number + 1;
2685 l_counter := l_counter + 1;
2686 END IF;
2687 END IF;
2688 ELSE
2689 l_selv_tbl(l_counter).amount := ROUND((l_elements.amount*l_revenue_share/100),2);
2690 l_selv_tbl(l_counter).stream_element_date := l_period_end_date;
2691 l_selv_tbl(l_counter).se_line_number := l_line_number;
2692 l_line_number := l_line_number + 1;
2693 l_counter := l_counter + 1;
2694 END IF;
2695 END LOOP;
2696 CLOSE strm_csr;
2697 IF l_selv_tbl.COUNT > 0 THEN
2698 -- call streams api
2699 OKL_STREAMS_PUB.create_streams(
2700 p_api_version => l_api_version
2701 ,p_init_msg_list => l_init_msg_list
2702 ,x_return_status => l_return_status
2703 ,x_msg_count => l_msg_count
2704 ,x_msg_data => l_msg_data
2705 ,p_stmv_rec => l_stmv_rec
2706 ,p_selv_tbl => l_selv_tbl
2707 ,x_stmv_rec => x_stmv_rec
2708 ,x_selv_tbl => x_selv_tbl );
2709 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2710 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2711 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2712 RAISE Okl_Api.G_EXCEPTION_ERROR;
2713 END IF;
2714 END IF;
2715
2716 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : begin ------------
2717 --Create stream element structure for reporting product
2718 OPEN rep_strm_csr(l_contracts_csr.khr_id, l_final_start_date, l_contracts_csr.end_date, rep_stream_type_purpose);
2719 LOOP
2720 --re-initialize period end date
2721 l_period_end_date := NULL;
2722 FETCH rep_strm_csr INTO l_rep_elements;
2723 EXIT WHEN rep_strm_csr%NOTFOUND;
2724 l_period_end_date := trunc(last_day(l_rep_elements.stream_element_date));
2725 --populate stream elements tbl
2726 -- manipulate first record
2727 IF rep_strm_csr%ROWCOUNT = 1 THEN
2728 -- If start date is last day of the month, do nothing.
2729 IF TRUNC(l_final_start_date) <> TRUNC(LAST_DAY(l_final_start_date)) THEN
2730 -- If start date is the same as first day of the month then take whole amount.
2731 IF TRUNC(l_final_start_date) = TRUNC((ADD_MONTHS(LAST_DAY(l_final_start_date), -1) + 1)) THEN
2732 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
2733 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2734 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2735 l_rep_line_number := l_rep_line_number + 1;
2736 l_rep_counter := l_rep_counter + 1;
2737 ELSE
2738 -- start date is not first or last day of the month. so prorate.
2739 l_difference := ABS(TRUNC(l_rep_elements.stream_element_date) - TRUNC(l_final_start_date));
2740 l_rep_selv_tbl(l_rep_counter).amount := ROUND((((l_difference/30)*l_rep_elements.amount)*l_revenue_share/100),2);
2741 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2742 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2743 l_rep_line_number := l_rep_line_number + 1;
2744 l_rep_counter := l_rep_counter + 1;
2745 END IF;
2746 END IF;
2747 ELSE
2748 l_rep_selv_tbl(l_rep_counter).amount := ROUND((l_rep_elements.amount*l_revenue_share/100),2);
2749 l_rep_selv_tbl(l_rep_counter).stream_element_date := l_period_end_date;
2750 l_rep_selv_tbl(l_rep_counter).se_line_number := l_rep_line_number;
2751 l_rep_line_number := l_rep_line_number + 1;
2752 l_rep_counter := l_rep_counter + 1;
2753 END IF;
2754 END LOOP;
2755 CLOSE rep_strm_csr;
2756 IF l_rep_selv_tbl.COUNT > 0 THEN
2757 -- call streams api
2758 OKL_STREAMS_PUB.create_streams(
2759 p_api_version => l_api_version
2760 ,p_init_msg_list => l_init_msg_list
2761 ,x_return_status => l_return_status
2762 ,x_msg_count => l_msg_count
2763 ,x_msg_data => l_msg_data
2764 ,p_stmv_rec => l_rep_stmv_rec
2765 ,p_selv_tbl => l_rep_selv_tbl
2766 ,x_stmv_rec => x_rep_stmv_rec
2767 ,x_selv_tbl => x_rep_selv_tbl );
2768 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2769 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2770 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2771 RAISE Okl_Api.G_EXCEPTION_ERROR;
2772 END IF;
2773 END IF;
2774
2775 ---------------sechawla 09-mar-09 : MG Impact on Investor Agreement : end ---------------
2776
2777 EXCEPTION
2778 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2779 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2780 ,g_pkg_name
2781 ,'OKL_API.G_RET_STS_ERROR'
2782 ,x_msg_count
2783 ,x_msg_data
2784 ,'_PVT');
2785 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2786 l_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2787 ,g_pkg_name
2788 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2789 ,x_msg_count
2790 ,x_msg_data
2791 ,'_PVT');
2792 WHEN OTHERS THEN
2793 IF get_kle_id_csr%ISOPEN THEN
2794 CLOSE get_kle_id_csr;
2795 END IF;
2796 IF get_adv_arr_csr%ISOPEN THEN
2797 CLOSE get_adv_arr_csr;
2798 END IF;
2799
2800 IF get_sty_id_csr%ISOPEN THEN
2801 CLOSE get_sty_id_csr;
2802 END IF;
2803
2804 IF strm_csr%ISOPEN THEN
2805 CLOSE strm_csr;
2806 END IF;
2807 l_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
2808 G_PKG_NAME,
2809 'OTHERS',
2810 x_msg_count,
2811 x_msg_data,
2812 '_PVT');
2813 END;
2814 END LOOP;
2815 CLOSE securitized_contracts_pend_csr;
2816
2817 END IF;
2818 END IF;
2819 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2820 x_msg_data => x_msg_data);
2821 x_return_status := l_return_status;
2822
2823 EXCEPTION
2824 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2825 /*
2826 --sechawla 9-mar-09
2827 IF l_sec_rep_method_csr%ISOPEN THEN
2828 CLOSE l_sec_rep_method_csr;
2829 END IF;
2830 */
2831 IF rep_strm_csr%ISOPEN THEN
2832 CLOSE rep_strm_csr;
2833 END IF;
2834
2835 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2836 ,g_pkg_name
2837 ,'OKL_API.G_RET_STS_ERROR'
2838 ,x_msg_count
2839 ,x_msg_data
2840 ,'_PVT');
2841 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2842 /* --sechawla 9-mar-09
2843 IF l_sec_rep_method_csr%ISOPEN THEN
2844 CLOSE l_sec_rep_method_csr;
2845 END IF;
2846 */
2847 IF rep_strm_csr%ISOPEN THEN
2848 CLOSE rep_strm_csr;
2849 END IF;
2850 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
2851 ,g_pkg_name
2852 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2853 ,x_msg_count
2854 ,x_msg_data
2855 ,'_PVT');
2856 WHEN OTHERS THEN
2857 IF securitized_contracts_csr%ISOPEN THEN
2858 CLOSE securitized_contracts_csr;
2859 END IF;
2860 --sechawla 9-mar-09
2861 /* IF l_sec_rep_method_csr%ISOPEN THEN
2862 CLOSE l_sec_rep_method_csr;
2863 END IF;
2864 */
2865 IF rep_strm_csr%ISOPEN THEN
2866 CLOSE rep_strm_csr;
2867 END IF;
2868 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name,
2869 G_PKG_NAME,
2870 'OTHERS',
2871 x_msg_count,
2872 x_msg_data,
2873 '_PVT');
2874
2875 END CREATE_STREAMS;
2876
2877 -- procedure to cancel accrual securitization streams for LEASE contracts.
2878 -- this procedure is being updated. Instead of deleting stream elements physically
2879 -- accrued_yn flag will be updated to N. Generate accruals picks only those amounts
2880 -- which are marked as NULL.
2881
2882 --sechawla 10-mar-09 MG Impacts on IA : update accrual flag for reporting streams as well
2883 PROCEDURE CANCEL_STREAMS(p_api_version IN NUMBER,
2884 p_init_msg_list IN VARCHAR2,
2885 x_return_status OUT NOCOPY VARCHAR2,
2886 x_msg_count OUT NOCOPY NUMBER,
2887 x_msg_data OUT NOCOPY VARCHAR2,
2888 p_khr_id IN NUMBER,
2889 p_cancel_date IN DATE) IS
2890
2891 l_api_version CONSTANT NUMBER := 1.0;
2892 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_STREAMS';
2893 /*
2894 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR RENTAL ACCRUAL';
2895 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR PRE-TAX INCOME';
2896 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'INVESTOR INTEREST INCOME';
2897 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR VARIABLE INTEREST';
2898 */
2899 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
2900 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
2901 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
2902 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
2903 /* ankushar , 16-01-2008 Bug 6691554
2904 Added new Stream Type purpose for a Loan product
2905 */
2906 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
2907
2908 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
2909 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2910 l_msg_count NUMBER;
2911 l_msg_data VARCHAR2(2000);
2912 l_deal_type VARCHAR2(2000);
2913 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2914 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2915
2916 --sechawla 10-mar-09 MG impacts
2917 l_sec_rep_method VARCHAR2(30);
2918 lx_rep_product OKL_PRODUCTS_V.NAME%TYPE;
2919 lx_rep_product_id NUMBER;
2920 lx_rep_deal_type okl_product_parameters_v.deal_type%TYPE;
2921 l_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2922 x_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
2923
2924 -- cursor to get deal type of lease contract.
2925 CURSOR get_deal_type_csr(p_khr_id NUMBER) IS
2926 SELECT deal_type
2927 FROM OKL_K_HEADERS
2928 WHERE id = p_khr_id;
2929
2930 CURSOR non_accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE) IS
2931 SELECT ste.id
2932 FROM OKL_STRM_TYPE_B sty,
2933 OKL_STREAMS stm,
2934 OKL_STRM_ELEMENTS ste
2935 WHERE stm.khr_id = p_khr_id
2936 AND stm.sty_id = sty.id
2937 --AND sty.code = p_sty_code
2938 AND sty.stream_type_purpose = p_sty_code
2939 AND stm.id = ste.stm_id
2940 AND stm.active_yn ='Y'
2941 AND stm.say_code= 'CURR'
2942 AND ste.stream_element_date >= p_date
2943 AND ste.accrued_yn IS NULL;
2944
2945 --sechawla 10-mar-09 MG Impact
2946 CURSOR rep_non_accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE) IS
2947 SELECT ste.id
2948 FROM OKL_STRM_TYPE_B sty,
2949 OKL_STREAMS stm,
2950 OKL_STRM_ELEMENTS ste
2951 WHERE stm.khr_id = p_khr_id
2952 AND stm.sty_id = sty.id
2953 --AND sty.code = p_sty_code
2954 AND sty.stream_type_purpose = p_sty_code
2955 AND stm.id = ste.stm_id
2956 AND stm.active_yn ='N'
2957 AND stm.say_code= 'CURR'
2958 AND stm.purpose_code = 'REPORT'
2959 AND ste.stream_element_date >= p_date
2960 AND ste.accrued_yn IS NULL;
2961
2962 --sechawla 10-mar-09 MG Impact
2963 CURSOR l_sec_rep_method_csr IS
2964 SELECT secondary_rep_method
2965 FROM okl_sys_acct_opts;
2966
2967
2968 CURSOR accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE) IS
2969 SELECT ste.id
2970 FROM OKL_STRM_TYPE_B sty,
2971 OKL_STREAMS stm,
2972 OKL_STRM_ELEMENTS ste
2973 WHERE stm.khr_id = p_khr_id
2974 AND stm.sty_id = sty.id
2975 --AND sty.code = p_sty_code
2976 AND sty.stream_type_purpose = p_sty_code
2977 AND stm.id = ste.stm_id
2978 AND stm.active_yn ='Y'
2979 AND stm.say_code= 'CURR'
2980 AND ste.stream_element_date >= p_date
2981 AND ste.accrued_yn IS NULL;
2982
2983 BEGIN
2984
2985 -- Set save point
2986 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
2987 p_pkg_name => G_PKG_NAME,
2988 p_init_msg_list => p_init_msg_list,
2989 l_api_version => l_api_version,
2990 p_api_version => p_api_version,
2991 p_api_type => '_PVT',
2992 x_return_status => l_return_status);
2993
2994 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2995 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2996 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
2997 RAISE Okl_Api.G_EXCEPTION_ERROR;
2998 END IF;
2999
3000 -- validate in parameters
3001 IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
3002 -- store SQL error message on message stack for caller
3003 Okl_Api.set_message(p_app_name => g_app_name,
3004 p_msg_name => 'OKL_ASC_KHR_ID_ERROR');
3005 RAISE Okl_Api.G_EXCEPTION_ERROR;
3006 END IF;
3007
3008 IF p_cancel_date IS NULL OR p_cancel_date = OKL_API.G_MISS_DATE THEN
3009 -- store SQL error message on message stack for caller
3010 Okl_Api.set_message(p_app_name => g_app_name,
3011 p_msg_name => 'OKL_ASC_CANCEL_DATE_ERROR');
3012 RAISE Okl_Api.G_EXCEPTION_ERROR;
3013 END IF;
3014
3015 -- get deal type
3016 OPEN get_deal_type_csr(p_khr_id);
3017 FETCH get_deal_type_csr INTO l_deal_type;
3018 CLOSE get_deal_type_csr;
3019
3020 -- get non accrued stream elements based on deal type for deletion.
3021 IF l_deal_type = 'LEASEOP' THEN
3022
3023 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_rental_accrual, p_cancel_date)
3024 LOOP
3025 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
3026 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3027 END LOOP;
3028 ELSIF l_deal_type IN ('LEASEDF', 'LEASEST') THEN
3029
3030 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_pre_tax_income, p_cancel_date)
3031 LOOP
3032 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
3033 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3034 END LOOP;
3035 /* ankushar , 16-01-2008 Bug 6691554
3036 Added condition for fetching stream type for a Loan product
3037 Start Changes
3038 */
3039 ELSIF l_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
3040
3041 FOR x IN non_accrued_streams_csr(p_khr_id, l_inv_interest_income_accrual, p_cancel_date)
3042 LOOP
3043 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
3044 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3045 END LOOP;
3046 /* ankushar , 16-01-2008 Bug 6691554
3047 End Changes
3048 */
3049
3050 END IF;
3051
3052 -- call delete stream elements API.
3053 IF l_selv_tbl.COUNT > 0 THEN
3054
3055 OKL_STREAMS_PUB.update_stream_elements(
3056 p_api_version => l_api_version
3057 ,p_init_msg_list => l_init_msg_list
3058 ,x_return_status => l_return_status
3059 ,x_msg_count => l_msg_count
3060 ,x_msg_data => l_msg_data
3061 ,p_selv_tbl => l_selv_tbl
3062 ,x_selv_tbl => x_selv_tbl);
3063 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3064 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3065 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
3066 RAISE Okl_Api.G_EXCEPTION_ERROR;
3067 END IF;
3068
3069 END IF;
3070
3071 --------------sechawla 09-mar-09 : MG Impacts begin------------------
3072 --Cancel streams (update accrual flag) on reporting streams as well
3073 okl_accounting_util.get_reporting_product(
3074 p_api_version => l_api_version,
3075 p_init_msg_list => p_init_msg_list,
3076 x_return_status => l_return_status,
3077 x_msg_count => x_msg_count,
3078 x_msg_data => x_msg_data,
3079 p_contract_id => p_khr_id,
3080 x_rep_product => lx_rep_product,
3081 x_rep_product_id => lx_rep_product_id,
3082 x_rep_deal_type => lx_rep_deal_type);
3083
3084 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3085 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3086 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3087 RAISE OKL_API.G_EXCEPTION_ERROR;
3088 END IF;
3089
3090 --Check the secondary_rep_method
3091 OPEN l_sec_rep_method_csr ;
3092 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
3093 IF l_sec_rep_method_csr%NOTFOUND THEN
3094 okl_api.set_message(p_app_name => g_app_name,
3095 p_msg_name => 'OKL_NO_SEC_REP_METHOD' --> seed this ''Secondary rep method cursor did not return any records''
3096 );
3097 RAISE okl_api.g_exception_error;
3098 END IF;
3099 CLOSE l_sec_rep_method_csr ;
3100
3101 IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
3102 -- get non accrued stream elements based on deal type for deletion.
3103 IF lx_rep_deal_type = 'LEASEOP' THEN
3104
3105 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_investor_rental_accrual, p_cancel_date) LOOP
3106 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
3107 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3108 END LOOP;
3109 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
3110
3111 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_investor_pre_tax_income, p_cancel_date) LOOP
3112 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
3113 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3114 END LOOP;
3115
3116 ELSIF lx_rep_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
3117
3118 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_inv_interest_income_accrual, p_cancel_date) LOOP
3119 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
3120 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).accrued_yn := 'N';
3121 END LOOP;
3122
3123 END IF;
3124
3125 -- call delete stream elements API.
3126 IF l_rep_selv_tbl.COUNT > 0 THEN
3127
3128 OKL_STREAMS_PUB.update_stream_elements(
3129 p_api_version => l_api_version
3130 ,p_init_msg_list => l_init_msg_list
3131 ,x_return_status => l_return_status
3132 ,x_msg_count => l_msg_count
3133 ,x_msg_data => l_msg_data
3134 ,p_selv_tbl => l_rep_selv_tbl
3135 ,x_selv_tbl => x_rep_selv_tbl);
3136
3137 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3138 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3139 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
3140 RAISE Okl_Api.G_EXCEPTION_ERROR;
3141 END IF;
3142
3143 END IF;
3144
3145
3146 END IF;
3147
3148 --------------sechawla 09-mar-09 : MG Impact on Investor Agreement end --------------
3149
3150
3151 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3152 x_msg_data => x_msg_data);
3153 x_return_status := l_return_status;
3154
3155 EXCEPTION
3156 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3157
3158 --sechawla 10-mar-09 MG Impact
3159 IF rep_non_accrued_streams_csr%ISOPEN THEN
3160 CLOSE rep_non_accrued_streams_csr;
3161 END IF;
3162
3163 IF l_sec_rep_method_csr%ISOPEN THEN
3164 CLOSE l_sec_rep_method_csr;
3165 END IF;
3166
3167 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
3168 ,g_pkg_name
3169 ,'OKL_API.G_RET_STS_ERROR'
3170 ,x_msg_count
3171 ,x_msg_data
3172 ,'_PVT');
3173 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3174
3175 --sechawla 10-mar-09 MG Impact
3176 IF rep_non_accrued_streams_csr%ISOPEN THEN
3177 CLOSE rep_non_accrued_streams_csr;
3178 END IF;
3179
3180 IF l_sec_rep_method_csr%ISOPEN THEN
3181 CLOSE l_sec_rep_method_csr;
3182 END IF;
3183 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
3184 ,g_pkg_name
3185 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3186 ,x_msg_count
3187 ,x_msg_data
3188 ,'_PVT');
3189 WHEN OTHERS THEN
3190 IF get_deal_type_csr%ISOPEN THEN
3191 CLOSE get_deal_type_csr;
3192 END IF;
3193
3194 --sechawla 10-mar-09 MG Impact
3195 IF rep_non_accrued_streams_csr%ISOPEN THEN
3196 CLOSE rep_non_accrued_streams_csr;
3197 END IF;
3198
3199 IF l_sec_rep_method_csr%ISOPEN THEN
3200 CLOSE l_sec_rep_method_csr;
3201 END IF;
3202
3203 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
3204 (l_api_name,
3205 G_PKG_NAME,
3206 'OTHERS',
3207 x_msg_count,
3208 x_msg_data,
3209 '_PVT');
3210
3211 END CANCEL_STREAMS;
3212 /*Commented as T_A requirement has changed
3213 PROCEDURE Create_Adjustment_Streams(
3214 p_api_version IN NUMBER
3215 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3216 ,x_return_status OUT NOCOPY VARCHAR2
3217 ,x_msg_count OUT NOCOPY NUMBER
3218 ,x_msg_data OUT NOCOPY VARCHAR2
3219 ,p_contract_id IN NUMBER
3220 ,p_line_id_tbl IN p_line_id_tbl_type
3221 ,p_adjustment_date IN DATE) IS
3222 */
3223 PROCEDURE Get_Accrual_Adjustment(
3224 p_api_version IN NUMBER
3225 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3226 ,x_return_status OUT NOCOPY VARCHAR2
3227 ,x_msg_count OUT NOCOPY NUMBER
3228 ,x_msg_data OUT NOCOPY VARCHAR2
3229 ,p_contract_id IN NUMBER
3230 ,p_line_id_tbl IN p_line_id_tbl_type
3231 ,p_adjustment_date IN DATE
3232 ,x_accrual_adjustment_tbl OUT NOCOPY p_accrual_adjustment_tbl_type
3233 ,p_product_id IN NUMBER DEFAULT NULL) IS -- MGAAP
3234
3235 /*
3236 CURSOR l_line_rec_csr(chrid NUMBER, lnetype VARCHAR2)
3237 IS
3238 SELECT kle.id,
3239 kle.amount,
3240 kle.start_date,
3241 kle.end_date,
3242 kle.fee_type,
3243 kle.initial_direct_cost,
3244 tl.item_description,
3245 tl.name,
3246 sts.ste_code
3247 FROM okl_k_lines_full_v kle,
3248 okc_line_styles_b lse,
3249 okc_k_lines_tl tl,
3250 okc_statuses_b sts
3251 WHERE kle.lse_id = lse.id
3252 AND lse.lty_code = lnetype
3253 AND tl.id = kle.id
3254 AND tl.language = userenv('LANG')
3255 AND kle.dnz_chr_id = chrid
3256 AND sts.code = kle.sts_code
3257 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
3258 */
3259 CURSOR l_line_rec_csr(chrid NUMBER, kleid NUMBER)
3260 IS
3261 SELECT
3262 kle.amount,
3263 kle.start_date,
3264 kle.end_date,
3265 kle.fee_type,
3266 kle.initial_direct_cost,
3267 tl.item_description,
3268 tl.name,
3269 sts.ste_code,
3270 lse.lty_code
3271 FROM okl_k_lines_full_v kle,
3272 okc_line_styles_b lse,
3273 okc_k_lines_tl tl,
3274 okc_statuses_b sts
3275 WHERE kle.lse_id = lse.id
3276 AND tl.id = kle.id
3277 AND tl.language = userenv('LANG')
3278 AND kle.dnz_chr_id = chrid
3279 AND kle.id = kleid
3280 AND sts.code = kle.sts_code
3281 AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
3282
3283 CURSOR link_rollover_csr(cleId NUMBER)
3284 IS
3285 SELECT okc.id id,
3286 okc.chr_id chr_id,
3287 okc.cle_id cle_id,
3288 okc.dnz_chr_id dnz_chr_id,
3289 kle.capital_amount capital_amount,
3290 kle.amount amount,
3291 lse.lty_code lty_code
3292 FROM okc_k_lines_b okc,
3293 okl_k_lines kle ,
3294 okc_line_styles_b lse
3295 WHERE okc.cle_id = cleId
3296 AND okc.lse_id = lse.id
3297 AND okc.id = kle.id
3298 AND lty_code = 'LINK_FEE_ASSET';
3299
3300 CURSOR l_strm_for_line_csr(chrid NUMBER, kleid NUMBER)
3301 IS
3302 SELECT
3303 str.sty_id sty_id
3304 --FROM okl_streams str,
3305 FROM okl_streams_rep_v str, -- Bug# 10091594
3306 okl_strm_type_b sty
3307 WHERE str.sty_id = sty.id
3308 AND str.say_code = 'CURR'
3309 AND str.khr_id = chrid
3310 AND str.kle_id = kleid;
3311
3312
3313 CURSOR l_accrued_amt_csr(chrid NUMBER, kleid NUMBER,tadate DATE, strmPurpose VARCHAR2)
3314 IS
3315 SELECT
3316 sum(ste.amount) amount,
3317 str.sty_id sty_id
3318 --FROM okl_streams str,
3319 FROM okl_streams_rep_v str, -- MGAAP 7263041
3320 okl_strm_elements ste,
3321 okl_strm_type_b sty
3322 WHERE ste.stm_id = str.id
3323 AND str.sty_id = sty.id
3324 AND str.say_code = 'CURR'
3325 AND str.khr_id = chrid
3326 AND str.kle_id = kleid
3327 AND ste.stream_element_date <= last_day(tadate)
3328 AND sty.stream_type_purpose = strmPurpose
3329 GROUP BY str.sty_id;
3330
3331 CURSOR l_accrued_amt_sty_csr(chrid NUMBER, kleid NUMBER,tadate DATE, styid NUMBER)
3332 IS
3333 SELECT
3334 sum(ste.amount) amount,
3335 str.sty_id sty_id
3336 --FROM okl_streams str,
3337 FROM okl_streams_rep_v str, -- MGAAP 7263041
3338 okl_strm_elements ste,
3339 okl_strm_type_b sty
3340 WHERE ste.stm_id = str.id
3341 AND str.sty_id = sty.id
3342 AND str.say_code = 'CURR'
3343 AND str.khr_id = chrid
3344 AND str.kle_id = kleid
3345 AND ste.stream_element_date <= last_day(tadate)
3346 AND sty.id = styid
3347 GROUP BY str.sty_id;
3348
3349
3350 CURSOR l_bill_amt_csr(chrid NUMBER, kleid NUMBER,tadate DATE, strmPurpose VARCHAR2)
3351 IS
3352 SELECT
3353 sum(ste.amount)
3354 --FROM okl_streams str,
3355 FROM okl_streams_rep_v str, -- MGAAP 7263041
3356 okl_strm_elements ste,
3357 okl_strm_type_b sty
3358 WHERE ste.stm_id = str.id
3359 AND str.sty_id = sty.id
3360 AND str.say_code = 'CURR'
3361 AND str.khr_id = chrid
3362 AND str.kle_id = kleid
3363 AND ste.stream_element_date <= tadate
3364 AND sty.stream_type_purpose = strmPurpose;
3365
3366 CURSOR l_bill_pmt_sty_csr(chrid NUMBER, kleid NUMBER, styid NUMBER, tadate DATE)
3367 IS
3368 SELECT
3369 sum(ste.amount)
3370 --FROM okl_streams str,
3371 FROM okl_streams_rep_v str, -- MGAAP 7263041
3372 okl_strm_elements ste,
3373 okl_strm_type_b sty
3374 WHERE ste.stm_id = str.id
3375 AND str.sty_id = sty.id
3376 AND str.say_code = 'CURR'
3377 AND str.khr_id = chrid
3378 AND str.kle_id = kleid
3379 AND sty.id = styid
3380 AND ste.stream_element_date <= tadate;
3381
3382 CURSOR l_pmt_sty_csr(rgcode okc_rule_groups_b.rgd_code%TYPE,
3383 rlcat okc_rules_b.rule_information_category%TYPE,
3384 chrId NUMBER,
3385 cleId NUMBER)
3386 IS
3387 SELECT crl.id slh_id,
3388 crl.object1_id1
3389 FROM okc_rule_groups_b crg,
3390 okc_rules_b crl
3391 WHERE crl.rgp_id = crg.id
3392 AND crg.rgd_code = rgcode
3393 AND crl.rule_information_category = rlcat
3394 AND crg.dnz_chr_id = chrId
3395 AND crg.cle_id = cleId
3396 ORDER BY crl.rule_information1;
3397
3398 CURSOR l_rl_csr2(rgcode okc_rule_groups_b.rgd_code%TYPE,
3399 rlcat okc_rules_b.rule_information_category%TYPE,
3400 chrId NUMBER,
3401 cleId NUMBER)
3402 IS
3403 SELECT crl.id slh_id,
3404 crl.object1_id1,
3405 crl.rule_information1,
3406 crl.rule_information2,
3407 crl.rule_information3,
3408 crl.rule_information5,
3409 crl.rule_information6,
3410 crl.rule_information7,
3411 crl.rule_information8,
3412 crl.rule_information13,
3413 crl.rule_information10
3414 FROM okc_rule_groups_b crg,
3415 okc_rules_b crl
3416 WHERE crl.rgp_id = crg.id
3417 AND crg.rgd_code = rgcode
3418 AND crl.rule_information_category = rlcat
3419 AND crg.dnz_chr_id = chrId
3420 AND crg.cle_id = cleId
3421 ORDER BY crl.rule_information1;
3422
3423 CURSOR l_pdt_accrual_csr(chrId NUMBER)
3424 IS
3425 SELECT sty.id sty_id
3426 FROM OKL_STRM_TYPE_B sty,
3427 OKL_PROD_STRM_TYPES psty,
3428 OKL_K_HEADERS khr
3429 WHERE khr.id = chrId
3430 --AND khr.pdt_id = psty.pdt_id
3431 AND psty.pdt_id = NVL(p_product_id, khr.pdt_id) -- MGAAP 7263041
3432 AND psty.sty_id = sty.id
3433 AND psty.accrual_yn = 'Y';
3434
3435
3436 -- vsgandhi : added for bug 14102580
3437 CURSOR l_abs_amt_csr(chrid NUMBER, kleid NUMBER,tadate DATE, strmPurpose VARCHAR2)
3438 IS
3439 SELECT
3440 sum(ste.amount) amount,
3441 str.sty_id sty_id
3442 --FROM okl_streams str,
3443 FROM okl_streams_rep_v str, -- MGAAP 7263041
3444 okl_strm_elements ste,
3445 okl_strm_type_b sty
3446 WHERE ste.stm_id = str.id
3447 AND str.sty_id = sty.id
3448 AND str.say_code = 'CURR'
3449 AND str.khr_id = chrid
3450 AND str.kle_id = kleid
3451 AND ste.stream_element_date >= last_day(tadate) +1
3452 AND sty.stream_type_purpose = strmPurpose
3453 GROUP BY str.sty_id;
3454
3455 l_accrual_amt l_accrued_amt_csr%ROWTYPE;
3456 l_strm_for_line_rec l_strm_for_line_csr%ROWTYPE;
3457 l_pdt_accrual_rec l_pdt_accrual_csr%ROWTYPE;
3458 l_line_rec l_line_rec_csr%ROWTYPE;
3459 l_rl_rec2 l_rl_csr2%ROWTYPE;
3460 l_pmt_sty_rec l_pmt_sty_csr%ROWTYPE;
3461
3462 -- vsgandhi: Added for bug 14102580
3463 l_abs_amt_rec l_abs_amt_csr%rowtype;
3464
3465
3466 TYPE p_pdt_accrual_rec_type IS RECORD(
3467 sty_id OKL_STRM_TYPE_B.ID%TYPE);
3468
3469 TYPE p_pdt_accrual_tbl_type IS TABLE OF p_pdt_accrual_rec_type
3470 INDEX BY BINARY_INTEGER;
3471
3472 l_pdt_accrual_tbl p_pdt_accrual_tbl_type;
3473 l_strm_for_line_tbl p_pdt_accrual_tbl_type;
3474 x_strm_for_line_tbl p_pdt_accrual_tbl_type;
3475
3476 m BINARY_INTEGER := 0;
3477 n BINARY_INTEGER := 0;
3478 i BINARY_INTEGER := 0;
3479 j BINARY_INTEGER := 0;
3480 l_strm_exist VARCHAR2(1);
3481 l_passthrough_percent NUMBER := 0;
3482 l_pdt_accrual_sty NUMBER := 0;
3483 l_fee_or_service VARCHAR2(50);
3484 l_fee_type VARCHAR2(100);
3485 l_adjustment_amt NUMBER := 0;
3486 l_bill_amt NUMBER := 0;
3487 l_idc_bill_amt NUMBER := 0;
3488 l_exp_bill_amt NUMBER := 0;
3489
3490 l_api_version CONSTANT NUMBER := 1.0;
3491 l_api_name CONSTANT VARCHAR2(30) := 'GET_ACCRUAL_ADJUSTMENT';
3492 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
3493 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3494 l_msg_count NUMBER;
3495 l_msg_data VARCHAR2(2000);
3496
3497
3498
3499 BEGIN
3500 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3501 -- Call start_activity to create savepoint, check compatibility
3502 -- and initialize message list
3503 x_return_status := OKL_API.START_ACTIVITY (
3504 l_api_name
3505 ,p_init_msg_list
3506 ,'_PVT'
3507 ,x_return_status);
3508 -- Check if activity started successfully
3509 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3510 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3511 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3512 RAISE OKL_API.G_EXCEPTION_ERROR;
3513 END IF;
3514
3515 -- Get the stream marked for accrual at product level
3516 FOR l_pdt_accrual_rec IN l_pdt_accrual_csr(p_contract_id) LOOP
3517 i := i + 1;
3518 l_pdt_accrual_tbl(i).sty_id := l_pdt_accrual_rec.sty_id;
3519 END LOOP;
3520
3521
3522 IF p_line_id_tbl.COUNT > 0 THEN
3523 FOR i IN p_line_id_tbl.FIRST..p_line_id_tbl.LAST LOOP
3524 l_strm_exist := 'N';
3525 OPEN l_line_rec_csr(p_contract_id , p_line_id_tbl(i).id);
3526 FETCH l_line_rec_csr INTO l_line_rec;
3527 l_fee_or_service := l_line_rec.lty_code;
3528 l_fee_type := l_line_rec.fee_type;
3529 IF (l_fee_or_service = 'FEE') THEN
3530 -- Get all the streams generated for a particular fee line
3531 j := 0;
3532 FOR l_strm_for_line_rec IN l_strm_for_line_csr(p_contract_id,p_line_id_tbl(i).id) LOOP
3533 j := j + 1;
3534 l_strm_for_line_tbl(j).sty_id := l_strm_for_line_rec.sty_id;
3535 END LOOP;
3536 -- Get those stream which has been generated for a line and marked for accrual at product
3537 IF l_pdt_accrual_tbl.COUNT > 0 THEN
3538 IF l_strm_for_line_tbl.COUNT > 0 THEN
3539 FOR k IN l_strm_for_line_tbl.FIRST..l_strm_for_line_tbl.LAST LOOP
3540 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3541 IF l_strm_for_line_tbl(k).sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3542 n := n + 1;
3543 x_strm_for_line_tbl(n).sty_id := l_strm_for_line_tbl(k).sty_id;
3544
3545 END IF;
3546 END LOOP;
3547 END LOOP;
3548 END IF;
3549 END IF;
3550 IF x_strm_for_line_tbl.COUNT > 0 THEN
3551 IF (l_fee_type = 'FINANCED') THEN
3552
3553 FOR p IN x_strm_for_line_tbl.FIRST..x_strm_for_line_tbl.LAST LOOP
3554
3555 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);
3556 FETCH l_accrued_amt_sty_csr INTO l_accrual_amt;
3557 CLOSE l_accrued_amt_sty_csr;
3558 /*
3559 OPEN l_accrued_amt_csr(chrid, p_line_id_tbl(i).id, tadate, 'LEASE_INCOME');
3560 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3561 CLOSE l_accrued_amt_csr;
3562 */
3563 OPEN l_bill_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'INTEREST_PAYMENT');
3564 FETCH l_bill_amt_csr INTO l_bill_amt;
3565 CLOSE l_bill_amt_csr;
3566
3567 --Bug# 10091594
3568 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3569 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3570
3571 m:= m + 1;
3572 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3573 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3574 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3575 END LOOP;
3576 ELSIF (l_fee_type = 'ROLLOVER') THEN
3577 FOR p IN x_strm_for_line_tbl.FIRST..x_strm_for_line_tbl.LAST LOOP
3578
3579 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);
3580 FETCH l_accrued_amt_sty_csr INTO l_accrual_amt;
3581 CLOSE l_accrued_amt_sty_csr;
3582 /*
3583 OPEN l_accrued_amt_csr(chrid, p_line_id_tbl(i).id, tadate, 'LEASE_INCOME');
3584 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3585 CLOSE l_accrued_amt_csr;
3586 */
3587 OPEN l_bill_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'INTEREST_PAYMENT');
3588 FETCH l_bill_amt_csr INTO l_bill_amt;
3589 CLOSE l_bill_amt_csr;
3590
3591 --Bug# 10091594
3592 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3593 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3594
3595 m:= m + 1;
3596 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3597 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3598 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3599 END LOOP;
3600
3601 ELSIF ((l_fee_type = 'MISCELLANEOUS') OR (l_fee_type = 'EXPENSE')) THEN
3602 IF (nvl(l_line_rec.initial_direct_cost,0) > 0) THEN
3603 IF NVL(l_line_rec.amount,0) <> NVL(l_line_rec.initial_direct_cost,0) THEN
3604
3605 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZED_FEE_EXPENSE');
3606 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3607 CLOSE l_accrued_amt_csr;
3608
3609 IF l_accrual_amt.sty_id IS NOT NULL THEN
3610 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3611 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3612 l_strm_exist := 'Y';
3613 END IF;
3614 END LOOP;
3615 END IF;
3616
3617 IF l_strm_exist = 'Y' THEN
3618 OKL_FUNDING_PVT.contract_fee_canbe_funded(
3619 p_api_version => l_api_version
3620 ,p_init_msg_list => l_init_msg_list
3621 ,x_return_status => l_return_status
3622 ,x_msg_count => l_msg_count
3623 ,x_msg_data => l_msg_data
3624 ,x_value => l_bill_amt
3625 ,p_contract_id => p_contract_id
3626 ,p_fee_line_id => p_line_id_tbl(i).id
3627 ,p_effective_date => p_adjustment_date
3628 );
3629
3630 l_idc_bill_amt := l_bill_amt*(l_line_rec.initial_direct_cost/l_line_rec.amount);
3631 l_exp_bill_amt := l_bill_amt - l_idc_bill_amt;
3632
3633 --Bug# 10091594
3634 --l_adjustment_amt := l_accrual_amt.amount - l_idc_bill_amt;
3635 l_adjustment_amt := l_idc_bill_amt - l_accrual_amt.amount;
3636
3637 m:= m + 1;
3638 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3639 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3640 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3641 END IF;
3642
3643 -- Code for expense fee
3644 l_strm_exist := 'N';
3645
3646 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_EXPENSE');
3647 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3648 CLOSE l_accrued_amt_csr;
3649
3650 IF l_accrual_amt.sty_id IS NOT NULL THEN
3651 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3652 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3653 l_strm_exist := 'Y';
3654 END IF;
3655 END LOOP;
3656 END IF;
3657
3658 IF l_strm_exist = 'Y' THEN
3659 --Bug# 10091594
3660 --l_adjustment_amt := l_accrual_amt.amount - l_exp_bill_amt;
3661 l_adjustment_amt := l_exp_bill_amt - l_accrual_amt.amount;
3662 m:= m + 1;
3663 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3664 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3665 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3666 END IF;
3667 ELSE
3668 l_strm_exist := 'N';
3669 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZED_FEE_EXPENSE');
3670 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3671 CLOSE l_accrued_amt_csr;
3672
3673 IF l_accrual_amt.sty_id IS NOT NULL THEN
3674 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3675 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3676 l_strm_exist := 'Y';
3677 END IF;
3678 END LOOP;
3679 END IF;
3680
3681 IF l_strm_exist = 'Y' THEN
3682 OKL_FUNDING_PVT.contract_fee_canbe_funded(
3683 p_api_version => l_api_version
3684 ,p_init_msg_list => l_init_msg_list
3685 ,x_return_status => l_return_status
3686 ,x_msg_count => l_msg_count
3687 ,x_msg_data => l_msg_data
3688 ,x_value => l_bill_amt
3689 ,p_contract_id => p_contract_id
3690 ,p_fee_line_id => p_line_id_tbl(i).id
3691 ,p_effective_date => p_adjustment_date
3692 );
3693
3694 l_idc_bill_amt := l_bill_amt;
3695
3696 --Bug# 10091594
3697 --l_adjustment_amt := l_accrual_amt.amount - l_idc_bill_amt;
3698 l_adjustment_amt := l_idc_bill_amt - l_accrual_amt.amount;
3699 m:= m + 1;
3700 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3701 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3702 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3703 END IF;
3704
3705 END IF;
3706 ELSE
3707 l_strm_exist := 'N';
3708 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_EXPENSE');
3709 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3710 CLOSE l_accrued_amt_csr;
3711
3712 IF l_accrual_amt.sty_id IS NOT NULL THEN
3713 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3714 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3715 l_strm_exist := 'Y';
3716 END IF;
3717 END LOOP;
3718 END IF;
3719
3720 IF l_strm_exist = 'Y' THEN
3721 OKL_FUNDING_PVT.contract_fee_canbe_funded(
3722 p_api_version => l_api_version
3723 ,p_init_msg_list => l_init_msg_list
3724 ,x_return_status => l_return_status
3725 ,x_msg_count => l_msg_count
3726 ,x_msg_data => l_msg_data
3727 ,x_value => l_bill_amt
3728 ,p_contract_id => p_contract_id
3729 ,p_fee_line_id => p_line_id_tbl(i).id
3730 ,p_effective_date => p_adjustment_date
3731 );
3732
3733 --Bug# 10091594
3734 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3735 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3736
3737 m:= m + 1;
3738 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3739 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3740 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3741 END IF;
3742 END IF;
3743 IF (l_fee_type = 'MISCELLANEOUS') THEN
3744 l_strm_exist := 'N';
3745 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
3746 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
3747 CLOSE l_pmt_sty_csr;
3748
3749 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'ACCRUED_FEE_INCOME');
3750 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3751 CLOSE l_accrued_amt_csr;
3752
3753 IF l_accrual_amt.sty_id IS NOT NULL THEN
3754 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3755 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3756 l_strm_exist := 'Y';
3757 END IF;
3758 END LOOP;
3759 END IF;
3760
3761 IF l_strm_exist = 'Y' THEN
3762 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
3763 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
3764 CLOSE l_bill_pmt_sty_csr;
3765
3766 --Bug# 10091594
3767 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3768 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3769
3770 m:= m + 1;
3771 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3772 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3773 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3774 END IF;
3775 END IF;
3776
3777
3778 ELSIF (l_fee_type = 'INCOME' ) THEN
3779
3780 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
3781 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
3782 CLOSE l_pmt_sty_csr;
3783
3784 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZE_FEE_INCOME');
3785 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3786 CLOSE l_accrued_amt_csr;
3787
3788 IF l_accrual_amt.sty_id IS NOT NULL THEN
3789 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3790 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3791 l_strm_exist := 'Y';
3792 END IF;
3793 END LOOP;
3794 END IF;
3795
3796 IF l_strm_exist = 'Y' THEN
3797 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
3798 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
3799 CLOSE l_bill_pmt_sty_csr;
3800
3801 --Bug# 10091594
3802 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3803 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3804
3805 m:= m + 1;
3806 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3807 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3808 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3809 END IF;
3810
3811 ELSIF (l_fee_type = 'PASSTHROUGH' ) THEN
3812 OPEN l_rl_csr2 ( 'LAPSTH', 'LAPTPR', TO_NUMBER(p_contract_id), p_line_id_tbl(i).id );
3813 FETCH l_rl_csr2 INTO l_rl_rec2;
3814 CLOSE l_rl_csr2;
3815 l_passthrough_percent := nvl( l_rl_rec2.rule_information1, 100.0 );
3816 IF l_passthrough_percent < 100 THEN
3817
3818 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
3819 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
3820 CLOSE l_pmt_sty_csr;
3821
3822 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'PASS_THRU_REV_ACCRUAL');
3823 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3824 CLOSE l_accrued_amt_csr;
3825
3826 IF l_accrual_amt.sty_id IS NOT NULL THEN
3827 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3828 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3829 l_strm_exist := 'Y';
3830 END IF;
3831 END LOOP;
3832 END IF;
3833
3834 IF l_strm_exist = 'Y' THEN
3835
3836 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
3837 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
3838 CLOSE l_bill_pmt_sty_csr;
3839
3840 --Bug# 10091594
3841 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3842 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3843
3844 m:= m + 1;
3845 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3846 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3847 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3848 END IF;
3849
3850 l_strm_exist := 'N';
3851 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'PASS_THRU_EXP_ACCRUAL');
3852 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3853 CLOSE l_accrued_amt_csr;
3854
3855 IF l_accrual_amt.sty_id IS NOT NULL THEN
3856 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3857 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3858 l_strm_exist := 'Y';
3859 END IF;
3860 END LOOP;
3861 END IF;
3862 IF l_strm_exist = 'Y' THEN
3863 l_bill_amt := l_bill_amt * l_passthrough_percent/100 ;
3864
3865 --Bug# 10091594
3866 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3867 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3868
3869 m:= m + 1;
3870 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3871 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3872 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3873 END IF;
3874 END IF;
3875 -- vsgandhi : start
3876 -- Bug 14102580 - ABSORBED EXPENSE ACCELERATION DO NOT HAPPEN WHEN PERFORMING A T and A
3877 ELSIF (l_fee_type = 'ABSORBED') THEN
3878
3879 OPEN l_abs_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'AMORTIZED_FEE_EXPENSE');
3880 FETCH l_abs_amt_csr INTO l_abs_amt_rec;
3881 CLOSE l_abs_amt_csr;
3882 l_strm_exist := 'N';
3883
3884 IF l_abs_amt_rec.sty_id IS NOT NULL THEN
3885 FOR i IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST
3886 LOOP
3887 IF l_abs_amt_rec.sty_id = l_pdt_accrual_tbl(i).sty_id THEN
3888 l_strm_exist := 'Y';
3889 END IF;
3890 END LOOP;
3891 END IF;
3892
3893 IF l_strm_exist = 'Y' THEN
3894 m:= m + 1;
3895 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3896 x_accrual_adjustment_tbl(m).sty_id := l_abs_amt_rec.sty_id;
3897 x_accrual_adjustment_tbl(m).amount := l_abs_amt_rec.amount;
3898 END IF;
3899 -- vsgandhi : End
3900 END IF;
3901 --ELSIF (l_fee_type = ) THEN
3902 END IF;
3903 ELSIF (l_fee_or_service = 'SOLD_SERVICE') THEN
3904
3905 OPEN l_pmt_sty_csr('LALEVL','LASLH',p_contract_id, p_line_id_tbl(i).id);
3906 FETCH l_pmt_sty_csr INTO l_pmt_sty_rec;
3907 CLOSE l_pmt_sty_csr;
3908
3909 OPEN l_accrued_amt_csr(p_contract_id, p_line_id_tbl(i).id, p_adjustment_date, 'SERVICE_INCOME');
3910 FETCH l_accrued_amt_csr INTO l_accrual_amt;
3911 CLOSE l_accrued_amt_csr;
3912
3913 IF l_accrual_amt.sty_id IS NOT NULL THEN
3914 FOR l IN l_pdt_accrual_tbl.FIRST..l_pdt_accrual_tbl.LAST LOOP
3915 IF l_accrual_amt.sty_id = l_pdt_accrual_tbl(l).sty_id THEN
3916 l_strm_exist := 'Y';
3917 END IF;
3918 END LOOP;
3919 END IF;
3920
3921 IF l_strm_exist = 'Y' THEN
3922 OPEN l_bill_pmt_sty_csr(p_contract_id, p_line_id_tbl(i).id, l_pmt_sty_rec.object1_id1,p_adjustment_date);
3923 FETCH l_bill_pmt_sty_csr INTO l_bill_amt;
3924 CLOSE l_bill_pmt_sty_csr;
3925
3926 --Bug# 10091594
3927 --l_adjustment_amt := l_accrual_amt.amount - l_bill_amt;
3928 l_adjustment_amt := l_bill_amt - l_accrual_amt.amount;
3929
3930 m:= m + 1;
3931 x_accrual_adjustment_tbl(m).line_id := p_line_id_tbl(i).id;
3932 x_accrual_adjustment_tbl(m).sty_id := l_accrual_amt.sty_id;
3933 x_accrual_adjustment_tbl(m).amount := l_adjustment_amt;
3934 END IF;
3935
3936 END IF;
3937 CLOSE l_line_rec_csr;
3938
3939 END LOOP;
3940 END IF;
3941 --NULL;
3942 OKL_API.END_ACTIVITY (x_msg_count,
3943 x_msg_data );
3944 EXCEPTION
3945 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3946 IF l_line_rec_csr%ISOPEN THEN
3947 CLOSE l_line_rec_csr;
3948 END IF;
3949 IF l_strm_for_line_csr%ISOPEN THEN
3950 CLOSE l_strm_for_line_csr;
3951 END IF;
3952 IF l_accrued_amt_csr%ISOPEN THEN
3953 CLOSE l_accrued_amt_csr;
3954 END IF;
3955 IF l_accrued_amt_sty_csr%ISOPEN THEN
3956 CLOSE l_accrued_amt_sty_csr;
3957 END IF;
3958 IF l_bill_amt_csr%ISOPEN THEN
3959 CLOSE l_bill_amt_csr;
3960 END IF;
3961 IF l_bill_pmt_sty_csr%ISOPEN THEN
3962 CLOSE l_bill_pmt_sty_csr;
3963 END IF;
3964 IF l_pmt_sty_csr%ISOPEN THEN
3965 CLOSE l_pmt_sty_csr;
3966 END IF;
3967 IF l_rl_csr2%ISOPEN THEN
3968 CLOSE l_rl_csr2;
3969 END IF;
3970 IF l_pdt_accrual_csr%ISOPEN THEN
3971 CLOSE l_pdt_accrual_csr;
3972 END IF;
3973 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
3974 l_api_name,
3975 G_PKG_NAME,
3976 'OKL_API.G_RET_STS_ERROR',
3977 x_msg_count,
3978 x_msg_data,
3979 '_PVT');
3980 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3981 IF l_line_rec_csr%ISOPEN THEN
3982 CLOSE l_line_rec_csr;
3983 END IF;
3984 IF l_strm_for_line_csr%ISOPEN THEN
3985 CLOSE l_strm_for_line_csr;
3986 END IF;
3987 IF l_accrued_amt_csr%ISOPEN THEN
3988 CLOSE l_accrued_amt_csr;
3989 END IF;
3990 IF l_accrued_amt_sty_csr%ISOPEN THEN
3991 CLOSE l_accrued_amt_sty_csr;
3992 END IF;
3993 IF l_bill_amt_csr%ISOPEN THEN
3994 CLOSE l_bill_amt_csr;
3995 END IF;
3996 IF l_bill_pmt_sty_csr%ISOPEN THEN
3997 CLOSE l_bill_pmt_sty_csr;
3998 END IF;
3999 IF l_pmt_sty_csr%ISOPEN THEN
4000 CLOSE l_pmt_sty_csr;
4001 END IF;
4002 IF l_rl_csr2%ISOPEN THEN
4003 CLOSE l_rl_csr2;
4004 END IF;
4005 IF l_pdt_accrual_csr%ISOPEN THEN
4006 CLOSE l_pdt_accrual_csr;
4007 END IF;
4008 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4009 l_api_name,
4010 G_PKG_NAME,
4011 'OKL_API.G_RET_STS_UNEXP_ERROR',
4012 x_msg_count,
4013 x_msg_data,
4014 '_PVT');
4015 WHEN OTHERS THEN
4016 IF l_line_rec_csr%ISOPEN THEN
4017 CLOSE l_line_rec_csr;
4018 END IF;
4019 IF l_strm_for_line_csr%ISOPEN THEN
4020 CLOSE l_strm_for_line_csr;
4021 END IF;
4022 IF l_accrued_amt_csr%ISOPEN THEN
4023 CLOSE l_accrued_amt_csr;
4024 END IF;
4025 IF l_accrued_amt_sty_csr%ISOPEN THEN
4026 CLOSE l_accrued_amt_sty_csr;
4027 END IF;
4028 IF l_bill_amt_csr%ISOPEN THEN
4029 CLOSE l_bill_amt_csr;
4030 END IF;
4031 IF l_bill_pmt_sty_csr%ISOPEN THEN
4032 CLOSE l_bill_pmt_sty_csr;
4033 END IF;
4034 IF l_pmt_sty_csr%ISOPEN THEN
4035 CLOSE l_pmt_sty_csr;
4036 END IF;
4037 IF l_rl_csr2%ISOPEN THEN
4038 CLOSE l_rl_csr2;
4039 END IF;
4040 IF l_pdt_accrual_csr%ISOPEN THEN
4041 CLOSE l_pdt_accrual_csr;
4042 END IF;
4043 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
4044 l_api_name,
4045 G_PKG_NAME,
4046 'OTHERS',
4047 x_msg_count,
4048 x_msg_data,
4049 '_PVT');
4050
4051 END get_accrual_adjustment;
4052
4053 -- VG----
4054 PROCEDURE DELETE_STREAMS(
4055 p_api_version IN NUMBER,
4056 p_init_msg_list IN VARCHAR2,
4057 x_return_status OUT NOCOPY VARCHAR2,
4058 x_msg_count OUT NOCOPY NUMBER,
4059 x_msg_data OUT NOCOPY VARCHAR2,
4060 p_khr_id IN NUMBER,
4061 p_cancel_date IN DATE)
4062 IS
4063 -- Declare the variables
4064 l_api_version CONSTANT NUMBER := 1.0;
4065 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STREAMS';
4066 l_investor_rental_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_RENTAL_ACCRUAL';
4067 l_investor_pre_tax_income CONSTANT VARCHAR2(2000) := 'INVESTOR_PRETAX_INCOME';
4068 l_investor_interest_income CONSTANT VARCHAR2(2000) := 'GENERAL';
4069 l_investor_variable_interest CONSTANT VARCHAR2(2000) := 'INVESTOR_VARIABLE_INTEREST';
4070 l_inv_interest_income_accrual CONSTANT VARCHAR2(2000) := 'INVESTOR_INTEREST_INCOME';
4071 l_init_msg_list VARCHAR2(4000) := OKL_API.G_FALSE;
4072 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4073 l_msg_count NUMBER;
4074 l_msg_data VARCHAR2(2000);
4075 l_deal_type VARCHAR2(2000);
4076 l_sec_rep_method VARCHAR2(30);
4077 lx_rep_product_id NUMBER;
4078 l_final_cancel_date DATE;
4079 l_ear_stm_date DATE;
4080 l_frequency NUMBER;
4081 l_arrears VARCHAR2(1);
4082 l_start_date DATE;
4083 l_kle_id NUMBER;
4084
4085 l_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
4086 x_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
4087 lx_rep_product OKL_PRODUCTS_V.NAME%TYPE;
4088 lx_rep_deal_type okl_product_parameters_v.deal_type%TYPE;
4089 l_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
4090 x_rep_selv_tbl OKL_STREAMS_PUB.selv_tbl_type;
4091
4092 -- cursor to get deal type of lease contract.
4093 CURSOR get_deal_type_csr(p_khr_id NUMBER)
4094 IS
4095 SELECT deal_type FROM OKL_K_HEADERS WHERE id = p_khr_id;
4096
4097
4098 CURSOR non_accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE)
4099 IS
4100 SELECT ste.id
4101 FROM OKL_STRM_TYPE_B sty,
4102 OKL_STREAMS stm,
4103 OKL_STRM_ELEMENTS ste
4104 WHERE stm.khr_id = p_khr_id
4105 AND stm.sty_id = sty.id
4106 --AND sty.code = p_sty_code
4107 AND sty.stream_type_purpose = p_sty_code
4108 AND stm.id = ste.stm_id
4109 AND stm.active_yn ='Y'
4110 AND stm.say_code = 'CURR'
4111 AND ste.stream_element_date >= p_date
4112 AND ste.accrued_yn IS NULL;
4113
4114
4115 --sechawla 10-mar-09 MG Impact
4116 CURSOR rep_non_accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE)
4117 IS
4118 SELECT ste.id
4119 FROM OKL_STRM_TYPE_B sty,
4120 OKL_STREAMS stm,
4121 OKL_STRM_ELEMENTS ste
4122 WHERE stm.khr_id = p_khr_id
4123 AND stm.sty_id = sty.id
4124 --AND sty.code = p_sty_code
4125 AND sty.stream_type_purpose = p_sty_code
4126 AND stm.id = ste.stm_id
4127 AND stm.active_yn ='N'
4128 AND stm.say_code = 'CURR'
4129 AND stm.purpose_code = 'REPORT'
4130 AND ste.stream_element_date >= p_date
4131 AND ste.accrued_yn IS NULL;
4132
4133
4134 --sechawla 10-mar-09 MG Impact
4135 CURSOR l_sec_rep_method_csr
4136 IS
4137 SELECT secondary_rep_method FROM okl_sys_acct_opts;
4138 CURSOR accrued_streams_csr(p_khr_id NUMBER, p_sty_code VARCHAR2, p_date DATE)
4139 IS
4140 SELECT ste.id
4141 FROM OKL_STRM_TYPE_B sty,
4142 OKL_STREAMS stm,
4143 OKL_STRM_ELEMENTS ste
4144 WHERE stm.khr_id = p_khr_id
4145 AND stm.sty_id = sty.id
4146 --AND sty.code = p_sty_code
4147 AND sty.stream_type_purpose = p_sty_code
4148 AND stm.id = ste.stm_id
4149 AND stm.active_yn ='Y'
4150 AND stm.say_code = 'CURR'
4151 AND ste.stream_element_date >= p_date
4152 AND ste.accrued_yn IS NULL;
4153
4154
4155 -- cursor to get first kle_id and earliest stream element date
4156 CURSOR get_kle_id_pend_csr(p_khr_id NUMBER)
4157 IS
4158 SELECT opc.kle_id kle_id,
4159 MIN(opc.streams_from_date) start_date
4160 FROM OKL_POOL_CONTENTS opc,
4161 OKL_STRM_TYPE_B sty
4162 WHERE opc.khr_id = p_khr_id
4163 AND opc.sty_id = sty.id
4164 --AND sty.code = 'RENT'
4165 AND sty.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
4166 --AND opc.status_code = G_PC_STS_PENDING --Okl_Pool_Pvt.G_POC_STS_PENDING
4167 AND ROWNUM < 2
4168 GROUP BY opc.kle_id;
4169
4170
4171 -- cursor to get advance/arrears and frequency for rent stream type
4172 CURSOR get_adv_arr_csr(p_khr_id NUMBER, p_kle_id NUMBER)
4173 IS
4174 SELECT DECODE(sll.rule_information10, NULL, 'N', 'Y', 'Y', 'N') arrears_yn,
4175 DECODE(sll.object1_id1, 'A',12,'S',6,'Q',3,'M',1) frequency
4176 FROM OKC_K_HEADERS_B K,
4177 OKC_RULE_GROUPS_B RG,
4178 OKC_RULES_B SLH,
4179 OKC_RULES_B SLL,
4180 OKL_STRM_TYPE_B STRM
4181 WHERE slh.rule_information_category = 'LASLH'
4182 AND slh.rgp_id = rg.id
4183 AND sll.object2_id1 = TO_CHAR(slh.id)
4184 AND sll.rgp_id = rg.id
4185 AND slh.object1_id1 = TO_CHAR(strm.id)
4186 --AND strm.code = 'RENT'
4187 AND strm.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
4188 AND rg.dnz_chr_id = k.id
4189 AND rg.chr_id IS NULL
4190 AND rg.rgd_code = 'LALEVL'
4191 AND rg.cle_id = p_kle_id
4192 AND k.id = p_khr_id;
4193
4194 -- To get the earliest billable stream
4195 CURSOR get_ear_billable_stream_csr (p_khr_id NUMBER)
4196 IS
4197 SELECT MIN(ste.stream_element_date)
4198 FROM OKL_STRM_TYPE_B sty,
4199 OKL_STREAMS stm,
4200 OKL_STRM_ELEMENTS ste
4201 WHERE stm.khr_id = p_khr_id
4202 AND stm.sty_id = sty.id
4203 AND sty.stream_type_purpose IN ( 'RENT', 'PRINCIPAL_PAYMENT')
4204 AND stm.id = ste.stm_id
4205 AND stm.active_yn ='Y'
4206 AND stm.say_code = 'CURR'
4207 AND ste.stream_element_date >= sysdate
4208 AND sty.billable_yn = 'Y'
4209 AND ste.date_billed IS NULL ;
4210
4211 BEGIN
4212 -- Set save point
4213 l_return_status := OKL_API.START_ACTIVITY(p_api_name => l_api_name,
4214 p_pkg_name => G_PKG_NAME,
4215 p_init_msg_list => p_init_msg_list,
4216 l_api_version => l_api_version,
4217 p_api_version => p_api_version,
4218 p_api_type => '_PVT',
4219 x_return_status => l_return_status);
4220 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4221 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4222 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
4223 RAISE Okl_Api.G_EXCEPTION_ERROR;
4224 END IF;
4225
4226 -- validate in parameters
4227 IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
4228 -- store SQL error message on message stack for caller
4229 Okl_Api.set_message(p_app_name => g_app_name, p_msg_name => 'OKL_ASC_KHR_ID_ERROR');
4230 RAISE Okl_Api.G_EXCEPTION_ERROR;
4231 END IF;
4232
4233 IF p_cancel_date IS NULL OR p_cancel_date = OKL_API.G_MISS_DATE THEN
4234 -- store SQL error message on message stack for caller
4235 Okl_Api.set_message(p_app_name => g_app_name, p_msg_name => 'OKL_ASC_CANCEL_DATE_ERROR');
4236 RAISE Okl_Api.G_EXCEPTION_ERROR;
4237 END IF;
4238
4239
4240 OPEN get_kle_id_pend_csr(p_khr_id);
4241 FETCH get_kle_id_pend_csr INTO l_kle_id, l_start_date;
4242 CLOSE get_kle_id_pend_csr;
4243
4244 IF l_kle_id IS NULL OR l_kle_id = OKL_API.G_MISS_NUM THEN
4245 Okl_Api.set_message(p_app_name => g_app_name, p_msg_name => 'OKL_ASC_KLE_ID_ERROR', p_token1 => g_contract_number_token, p_token1_value => null);
4246 RAISE OKL_API.G_EXCEPTION_ERROR;
4247 END IF;
4248
4249 OPEN get_adv_arr_csr(p_khr_id, l_kle_id);
4250 FETCH get_adv_arr_csr INTO l_arrears, l_frequency;
4251 CLOSE get_adv_arr_csr;
4252
4253 IF l_frequency IS NULL THEN
4254 Okl_Api.set_message(p_app_name => g_app_name, p_msg_name => 'OKL_ASC_FREQUENCY_ERROR', p_token1 => g_contract_number_token, p_token1_value => null);
4255 RAISE OKL_API.G_EXCEPTION_ERROR;
4256 END IF;
4257
4258 -- get the next billable stream
4259 OPEN get_ear_billable_stream_csr (p_khr_id);
4260 FETCH get_ear_billable_stream_csr INTO l_ear_stm_date;
4261 IF get_ear_billable_stream_csr%NOTFOUND then
4262 RAISE okl_api.g_exception_error;
4263 END IF;
4264 CLOSE get_ear_billable_stream_csr;
4265
4266
4267 IF NVL(l_arrears,'N') = 'N' THEN
4268 l_final_cancel_date := ADD_MONTHS(l_ear_stm_date, l_frequency) -1;
4269 ELSIF l_arrears = 'Y' THEN
4270 l_final_cancel_date := l_ear_stm_date;
4271 END IF;
4272
4273 -- get deal type
4274 OPEN get_deal_type_csr(p_khr_id);
4275 FETCH get_deal_type_csr INTO l_deal_type;
4276 CLOSE get_deal_type_csr;
4277
4278 -- get non accrued stream elements based on deal type for deletion.
4279 IF l_deal_type = 'LEASEOP' THEN
4280 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_rental_accrual, l_final_cancel_date)
4281 LOOP
4282 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
4283 END LOOP;
4284 ELSIF l_deal_type IN ('LEASEDF', 'LEASEST') THEN
4285 FOR x IN non_accrued_streams_csr(p_khr_id, l_investor_pre_tax_income, l_final_cancel_date)
4286 LOOP
4287 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
4288 END LOOP;
4289 ELSIF l_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
4290 FOR x IN non_accrued_streams_csr(p_khr_id, l_inv_interest_income_accrual, l_final_cancel_date)
4291 LOOP
4292 l_selv_tbl(non_accrued_streams_csr%ROWCOUNT).id := x.id;
4293 END LOOP;
4294 END IF;
4295
4296 IF l_selv_tbl.COUNT > 0 THEN
4297 OKL_STREAMS_PUB.delete_stream_elements(
4298 p_api_version => l_api_version ,
4299 p_init_msg_list => l_init_msg_list ,
4300 x_return_status => l_return_status ,
4301 x_msg_count => l_msg_count ,
4302 x_msg_data => l_msg_data ,
4303 p_selv_tbl => l_selv_tbl);
4304
4305 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4306 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4307 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
4308 RAISE Okl_Api.G_EXCEPTION_ERROR;
4309 END IF;
4310 END IF;
4311
4312 --Cancel streams (update accrual flag) on reporting streams as well
4313 okl_accounting_util.get_reporting_product(
4314 p_api_version => l_api_version,
4315 p_init_msg_list => p_init_msg_list,
4316 x_return_status => l_return_status,
4317 x_msg_count => x_msg_count,
4318 x_msg_data => x_msg_data,
4319 p_contract_id => p_khr_id,
4320 x_rep_product => lx_rep_product,
4321 x_rep_product_id => lx_rep_product_id,
4322 x_rep_deal_type => lx_rep_deal_type);
4323
4324 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
4325 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
4326 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
4327 RAISE OKL_API.G_EXCEPTION_ERROR;
4328 END IF;
4329
4330 --Check the secondary_rep_method
4331 OPEN l_sec_rep_method_csr ;
4332 FETCH l_sec_rep_method_csr INTO l_sec_rep_method;
4333 IF l_sec_rep_method_csr%NOTFOUND THEN
4334 okl_api.set_message(p_app_name => g_app_name, p_msg_name => 'OKL_NO_SEC_REP_METHOD' );
4335 RAISE okl_api.g_exception_error;
4336 END IF;
4337 CLOSE l_sec_rep_method_csr ;
4338
4339 IF lx_rep_product IS NOT NULL AND l_sec_rep_method = 'AUTOMATED' THEN
4340 -- get non accrued stream elements based on deal type for deletion.
4341 IF lx_rep_deal_type = 'LEASEOP' THEN
4342 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_investor_rental_accrual, l_final_cancel_date)
4343 LOOP
4344 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
4345 END LOOP;
4346 ELSIF lx_rep_deal_type IN ('LEASEDF', 'LEASEST') THEN
4347 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_investor_pre_tax_income, l_final_cancel_date)
4348 LOOP
4349 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
4350 END LOOP;
4351 ELSIF lx_rep_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
4352 FOR x_rep IN rep_non_accrued_streams_csr(p_khr_id, l_inv_interest_income_accrual, l_final_cancel_date)
4353 LOOP
4354 l_rep_selv_tbl(rep_non_accrued_streams_csr%ROWCOUNT).id := x_rep.id;
4355 END LOOP;
4356 END IF;
4357
4358 -- call delete stream elements API.
4359 IF l_rep_selv_tbl.COUNT > 0 THEN
4360 OKL_STREAMS_PUB.delete_stream_elements(
4361 p_api_version => l_api_version ,
4362 p_init_msg_list => l_init_msg_list ,
4363 x_return_status => l_return_status ,
4364 x_msg_count => l_msg_count ,
4365 x_msg_data => l_msg_data ,
4366 p_selv_tbl => l_rep_selv_tbl);
4367 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4368 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4369 ELSIF (l_return_Status = Okl_Api.G_RET_STS_ERROR) THEN
4370 RAISE Okl_Api.G_EXCEPTION_ERROR;
4371 END IF;
4372 END IF;
4373 END IF;
4374
4375 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
4376 x_return_status := l_return_status;
4377
4378 EXCEPTION
4379 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
4380 --sechawla 10-mar-09 MG Impact
4381 IF rep_non_accrued_streams_csr%ISOPEN THEN
4382 CLOSE rep_non_accrued_streams_csr;
4383 END IF;
4384 IF l_sec_rep_method_csr%ISOPEN THEN
4385 CLOSE l_sec_rep_method_csr;
4386 END IF;
4387 IF get_ear_billable_stream_csr%ISOPEN THEN
4388 CLOSE get_ear_billable_stream_csr;
4389 END IF;
4390 IF get_adv_arr_csr%ISOPEN THEN
4391 CLOSE get_adv_arr_csr;
4392 END IF;
4393
4394 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name ,g_pkg_name ,'OKL_API.G_RET_STS_ERROR' ,x_msg_count ,x_msg_data ,'_PVT');
4395 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
4396 --sechawla 10-mar-09 MG Impact
4397 IF rep_non_accrued_streams_csr%ISOPEN THEN
4398 CLOSE rep_non_accrued_streams_csr;
4399 END IF;
4400 IF l_sec_rep_method_csr%ISOPEN THEN
4401 CLOSE l_sec_rep_method_csr;
4402 END IF;
4403 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name ,g_pkg_name ,'OKL_API.G_RET_STS_UNEXP_ERROR' ,x_msg_count ,x_msg_data ,'_PVT');
4404 WHEN OTHERS THEN
4405 IF get_deal_type_csr%ISOPEN THEN
4406 CLOSE get_deal_type_csr;
4407 END IF;
4408 --sechawla 10-mar-09 MG Impact
4409 IF rep_non_accrued_streams_csr%ISOPEN THEN
4410 CLOSE rep_non_accrued_streams_csr;
4411 END IF;
4412 IF l_sec_rep_method_csr%ISOPEN THEN
4413 CLOSE l_sec_rep_method_csr;
4414 END IF;
4415 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS (l_api_name, G_PKG_NAME, 'OTHERS', x_msg_count, x_msg_data, '_PVT');
4416 END delete_STREAMS;
4417 -- VG---
4418
4419
4420
4421 END OKL_ACCRUAL_SEC_PVT;