[Home] [Help]
PACKAGE BODY: APPS.OKL_EXPENSE_STREAMS_PVT
Source
1 PACKAGE BODY OKL_EXPENSE_STREAMS_PVT AS
2 /* $Header: OKLRSGEB.pls 120.14 2008/06/11 23:12:12 djanaswa ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE generate_idc
6 ---------------------------------------------------------------------------
7 PROCEDURE generate_idc( p_khr_id IN NUMBER,
8 p_purpose_code IN VARCHAR2,
9 p_currency_code IN VARCHAR2,
10 p_start_date IN DATE,
11 p_end_date IN DATE,
12 p_deal_type IN VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2) IS
14
15 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'generate_idc';
16
17 CURSOR c_idc_exp IS
18 SELECT kle.id,
19 kle.initial_direct_cost
20 FROM okl_k_lines kle,
21 okc_k_lines_b cle,
22 okc_line_styles_b lse
23 WHERE cle.chr_id = p_khr_id
24 AND cle.sts_code IN ('PASSED', 'COMPLETE')
25 AND cle.lse_id = lse.id
26 AND lse.lty_code = 'FEE'
27 AND kle.fee_type <> 'FINANCED'
28 AND kle.fee_type <> 'ROLLOVER'
29 AND cle.id = kle.id;
30
31 CURSOR c_k_income (p_sty_name VARCHAR2) IS
32 SELECT sel.amount income_amount,
33 sel.stream_element_date income_date
34 FROM okl_strm_elements sel,
35 okl_streams stm,
36 okl_strm_type_b sty,
37 okl_strm_type_tl styt
38 WHERE stm.khr_id = p_khr_id
39 AND stm.say_code = 'WORK'
40 AND DECODE(stm.purpose_code, NULL, '-99', 'REPORT') = p_purpose_code
41 AND stm.id = sel.stm_id
42 AND stm.sty_id = sty.id
43 AND sty.version = '1.0'
44 AND sty.id = styt.id
45 AND styt.language = 'US'
46 AND styt.name = p_sty_name
47 ORDER BY sel.stream_element_date;
48
49 l_selv_tbl okl_streams_pub.selv_tbl_type;
50 lx_selv_tbl okl_streams_pub.selv_tbl_type;
51
52 l_stmv_rec okl_streams_pub.stmv_rec_type;
53 lx_stmv_rec okl_streams_pub.stmv_rec_type;
54
55 l_amortized_exp_id NUMBER;
56 l_sty_name VARCHAR2(150);
57 l_name VARCHAR2(150);
58 l_total_rent_income NUMBER := 0;
59 i BINARY_INTEGER := 0;
60
61 lx_return_status VARCHAR2(1);
62 lx_msg_data VARCHAR2(4000);
63 lx_msg_count NUMBER;
64
65
66 TYPE inc_strms_rec_type is RECORD
67 ( amount NUMBER,
68 ele_date DATE
69 );
70
71 TYPE inc_strms_tbl_type is TABLE OF
72 inc_strms_rec_type INDEX BY BINARY_INTEGER;
73
74 inc_strms_tbl inc_strms_tbl_type;
75
76 lastDate DATE := NULL;
77 l_sty_id NUMBER;
78
79 Cursor c_rollover_pmnts IS
80 Select distinct nvl(slh.object1_id1, -1) styId
81 From OKC_RULE_GROUPS_B rgp,
82 OKC_RULES_B sll,
83 okc_rules_b slh,
84 okl_strm_type_b sty
85 Where slh.rgp_id = rgp.id
86 and rgp.RGD_CODE = 'LALEVL'
87 and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
88 and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
89 AND TO_CHAR(slh.id) = sll.object2_id1
90 and slh.object1_id1 = sty.id
91 and sty.stream_type_purpose = 'RENT'
92 and rgp.dnz_chr_id = p_khr_id;
93
94 r_rollover_pmnts c_rollover_pmnts%ROWTYPE;
95
96 l_primary_sty_id NUMBER;
97
98 cursor fee_strm_type_csr ( kleid NUMBER ) is
99 select tl.name strm_name,
100 sty.capitalize_yn capitalize_yn,
101 kle.id line_id,
102 sty.id styp_id,
103 sty.stream_type_class stream_type_class
104 from okl_strm_type_tl tl,
105 okl_strm_type_v sty,
106 okc_k_items cim,
107 okl_k_lines_full_v kle,
108 okc_line_styles_b ls
109 where tl.id = sty.id
110 and tl.language = 'US'
111 and cim.cle_id = kle.id
112 and ls.id = kle.lse_id
113 and ls.lty_code = 'FEE'
114 and cim.object1_id1 = sty.id
115 and cim.object1_id2 = '#'
116 and kle.id = kleid;
117
118 fee_strm_type_rec fee_strm_type_csr%ROWTYPE;
119
120
121 BEGIN
122
123 OPEN c_rollover_pmnts;
124 FETCH c_rollover_pmnts INTO r_rollover_pmnts;
125 CLOSE c_rollover_pmnts;
126
127 l_primary_sty_id := r_rollover_pmnts.styId;
128
129 IF p_deal_type = 'LEASEOP' THEN
130 --l_sty_name := 'RENTAL ACCRUAL';
131 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
132 p_khr_id => p_khr_id,
133 p_deal_type => p_deal_type,
134 p_primary_sty_id => l_primary_sty_id,
135 p_dependent_sty_purpose => 'RENT_ACCRUAL',
136 x_return_status => x_return_status,
137 x_dependent_sty_id => l_sty_id,
138 x_dependent_sty_name => l_sty_name);
139
140 ELSIF p_deal_type IN ('LEASEDF', 'LEASEST') THEN
141 --l_sty_name := 'PRE-TAX INCOME';
142 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
143 p_khr_id => p_khr_id,
144 p_deal_type => p_deal_type,
145 p_primary_sty_id => l_primary_sty_id,
146 p_dependent_sty_purpose => 'LEASE_INCOME',
147 x_return_status => x_return_status,
148 x_dependent_sty_id => l_sty_id,
149 x_dependent_sty_name => l_sty_name);
150
151 ELSIF p_deal_type IN ('LOAN', 'LOAN-REVOLVING') THEN
152 --l_sty_name := 'PRE-TAX INCOME';
153 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
154 p_khr_id => p_khr_id,
155 p_deal_type => p_deal_type,
156 p_primary_sty_id => l_primary_sty_id,
157 p_dependent_sty_purpose => 'INTEREST_INCOME',
158 x_return_status => x_return_status,
159 x_dependent_sty_id => l_sty_id,
160 x_dependent_sty_name => l_sty_name);
161
162 END IF;
163
164 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
166 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
167 RAISE OKL_API.G_EXCEPTION_ERROR;
168 END IF;
169
170 i := 0;
171 FOR l_k_income IN c_k_income(p_sty_name => l_sty_name) LOOP
172 l_total_rent_income := l_total_rent_income + l_k_income.income_amount;
173 If ( trunc(l_k_income.income_date) =
174 trunc(nvl(lastDate, l_k_income.income_date+1) )) Then
175 inc_strms_tbl(i).amount := inc_strms_tbl(i).amount + l_k_income.income_amount;
176 Else
177 i := i + 1;
178 inc_strms_tbl(i).amount := l_k_income.income_amount;
179 inc_strms_tbl(i).ele_date := l_K_income.income_date;
180 lastDate := l_K_income.income_date;
181 End If;
182 END LOOP;
183
184 FOR l_idc_exp IN c_idc_exp LOOP
185
186 l_amortized_exp_id := NULL; -- bug 6156337
187
188 IF NVL(l_idc_exp.initial_direct_cost, 0) > 0 THEN
189
190 IF l_amortized_exp_id IS NULL THEN
191
192 /*
193 okl_stream_generator_pvt.get_sty_details (p_sty_name => 'AMORTIZED EXPENSE',
194 x_sty_id => l_amortized_exp_id,
195 x_sty_name => l_name,
196 x_return_status => lx_return_status);
197
198 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
200 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
201 RAISE OKL_API.G_EXCEPTION_ERROR;
202 END IF;
203
204 */
205
206 OPEN fee_strm_type_csr( l_idc_exp.id );
207 FETCH fee_strm_type_csr INTO fee_strm_type_rec;
208 CLOSE fee_strm_type_csr;
209 l_primary_sty_id := fee_strm_type_rec.styp_id;
210
211 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
212 p_khr_id => p_khr_id,
213 p_deal_type => p_deal_type,
214 p_primary_sty_id => l_primary_sty_id,
215 p_dependent_sty_purpose => 'AMORTIZED_FEE_EXPENSE',
216 x_return_status => x_return_status,
217 x_dependent_sty_id => l_amortized_exp_id,
218 x_dependent_sty_name => l_name);
219
220
221 IF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
223 ELSIF x_return_status = OKL_API.G_RET_STS_ERROR THEN
224 RAISE OKL_API.G_EXCEPTION_ERROR;
225 END IF;
226
227 END IF;
228
229 If l_amortized_exp_id IS NOT NULL then
230
231 SELECT okl_sif_seq.nextval
232 INTO l_stmv_rec.transaction_number
233 FROM DUAL;
234
235 l_stmv_rec.khr_id := p_khr_id;
236 l_stmv_rec.kle_id := l_idc_exp.id;
237 l_stmv_rec.sty_id := l_amortized_exp_id;
238 l_stmv_rec.sgn_code := 'MANL';
239 l_stmv_rec.say_code := 'WORK';
240 l_stmv_rec.active_yn := 'N';
241 l_stmv_rec.date_working := SYSDATE;
242
243 IF p_purpose_code = 'REPORT' THEN
244 l_stmv_rec.purpose_code := 'REPORT';
245 END IF;
246
247 FOR i IN 1..inc_strms_tbl.count
248 LOOP
249
250
251 l_selv_tbl(i).stream_element_date := inc_strms_tbl(i).ele_date;
252 l_selv_tbl(i).se_line_number := i;
253
254 l_selv_tbl(i).amount :=
255 (inc_strms_tbl(i).amount/l_total_rent_income)*l_idc_exp.initial_direct_cost;
256
257 END LOOP;
258
259
260 lx_return_status := Okl_Streams_Util.round_streams_amount(
261 p_api_version => g_api_version,
262 p_init_msg_list => G_FALSE,
263 x_msg_count => lx_msg_count,
264 x_msg_data => lx_msg_data,
265 p_chr_id => p_khr_id,
266 p_selv_tbl => l_selv_tbl,
267 x_selv_tbl => lx_selv_tbl);
268
269 IF (lx_return_status = G_RET_STS_UNEXP_ERROR) THEN
270 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
271 ELSIF (lx_return_status = G_RET_STS_ERROR) THEN
272 RAISE OKL_API.G_EXCEPTION_ERROR;
273 END IF;
274
275 l_selv_tbl.DELETE;
276 l_selv_tbl := lx_selv_tbl;
277
278
279 okl_streams_pub.create_streams(p_api_version => G_API_VERSION,
280 p_init_msg_list => G_FALSE,
281 x_return_status => lx_return_status,
282 x_msg_count => lx_msg_count,
283 x_msg_data => lx_msg_data,
284 p_stmv_rec => l_stmv_rec,
285 p_selv_tbl => l_selv_tbl,
286 x_stmv_rec => lx_stmv_rec,
287 x_selv_tbl => lx_selv_tbl);
288
289 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
290 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
292 RAISE OKL_API.G_EXCEPTION_ERROR;
293 END IF;
294
295 l_selv_tbl.DELETE;
296
297 End If;
298
299 i := 0;
300
301 END IF;
302
303 END LOOP;
304
305 x_return_status := G_RET_STS_SUCCESS;
306
307 EXCEPTION
308
309 WHEN OKL_API.G_EXCEPTION_ERROR THEN
310
311 x_return_status := G_RET_STS_ERROR;
312
313 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
314
315 x_return_status := G_RET_STS_UNEXP_ERROR;
316
317 WHEN OTHERS THEN
318
319 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
320 p_msg_name => G_DB_ERROR,
321 p_token1 => G_PROG_NAME_TOKEN,
322 p_token1_value => l_prog_name,
323 p_token2 => G_SQLCODE_TOKEN,
324 p_token2_value => sqlcode,
325 p_token3 => G_SQLERRM_TOKEN,
326 p_token3_value => sqlerrm);
327
328 x_return_status := G_RET_STS_UNEXP_ERROR;
329
330 END generate_idc;
331
332
333 ---------------------------------------------------------------------------
334 -- PROCEDURE generate_rec_exp
335 ---------------------------------------------------------------------------
336 PROCEDURE generate_rec_exp( p_khr_id IN NUMBER,
337 p_deal_type IN VARCHAR2,
338 p_purpose_code IN VARCHAR2,
339 p_currency_code IN VARCHAR2,
340 x_return_status OUT NOCOPY VARCHAR2) IS
341
342 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'generate_rec_exp';
343
344
345 l_primary_sty_id NUMBER;
346
347 cursor fee_strm_type_csr ( kleid NUMBER ) is
348 select tl.name strm_name,
349 sty.capitalize_yn capitalize_yn,
350 kle.id line_id,
351 sty.id styp_id,
352 sty.stream_type_class stream_type_class
353 from okl_strm_type_tl tl,
354 okl_strm_type_v sty,
355 okc_k_items cim,
356 okl_k_lines_full_v kle,
357 okc_line_styles_b ls
358 where tl.id = sty.id
359 and tl.language = 'US'
360 and cim.cle_id = kle.id
361 and ls.id = kle.lse_id
362 and ls.lty_code = 'FEE'
363 and cim.object1_id1 = sty.id
364 and cim.object1_id2 = '#'
365 and kle.id = kleid;
366
367 fee_strm_type_rec fee_strm_type_csr%ROWTYPE;
368
369 -- gboomina added for Bug 6763287 - Start
370 -- Modified c_rec_exp cursor to select NEW sts_code streams
371 -- for Investor Agreement
372 CURSOR c_rec_exp IS
373 SELECT TO_NUMBER(rul.rule_information1) periods,
374 TO_NUMBER(rul.rule_information2) amount,
375 DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12, NULL) mpp,
376 rgp.cle_id,
377 cle.start_date,
378 cle.sts_code
379 FROM okc_rules_b rul,
380 okc_rules_b rul2,
381 okc_rule_groups_b rgp,
382 okc_k_lines_b cle,
383 okl_k_lines kle
384 WHERE rgp.dnz_chr_id = p_khr_id
385 AND rgp.cle_id = cle.id
386 AND kle.id = cle.id
387 AND cle.sts_code IN ('NEW', 'INCOMPLETE', 'PASSED', 'COMPLETE')
388 AND kle.fee_type <> 'FINANCED'
389 AND kle.fee_type <> 'ABSORBED'
390 AND kle.fee_type <> 'ROLLOVER'
391 AND rgp.rgd_code = 'LAFEXP'
392 AND rgp.id = rul.rgp_id
393 AND rgp.id = rul2.rgp_id
394 AND rul.rule_information_category = 'LAFEXP'
395 AND rul2.rule_information_category = 'LAFREQ';
396 -- gboomina added for Bug 6763287 - End
397
398 CURSOR c_fee_idc (p_kle_id NUMBER) IS
399 SELECT NVL(initial_direct_cost, 0)
400 FROM okl_k_lines
401 WHERE id = p_kle_id;
402
403 l_selv_tbl okl_streams_pub.selv_tbl_type;
404 lx_selv_tbl okl_streams_pub.selv_tbl_type;
405
406 l_stmv_rec okl_streams_pub.stmv_rec_type;
407 lx_stmv_rec okl_streams_pub.stmv_rec_type;
408
409 l_end_date DATE;
410 l_periodic_exp_id NUMBER;
411 l_sty_name VARCHAR2(150);
412 l_total_days NUMBER;
413 l_daily_exp NUMBER;
414 l_start_date DATE;
415 l_month_end DATE;
416 l_rec_exp_bal NUMBER;
417 l_days NUMBER;
418 l_non_idc_exp NUMBER;
419 l_idc_amount NUMBER;
420 l_idc_fraction NUMBER;
421
422 i BINARY_INTEGER := 0;
423
424 lx_return_status VARCHAR2(1);
425 lx_msg_data VARCHAR2(4000);
426 lx_msg_count NUMBER;
427
428 Cursor day_conv_csr( khrId NUMBER) IS
429 select DAYS_IN_A_YEAR_CODE,
430 DAYS_IN_A_MONTH_CODE
431 from OKL_K_RATE_PARAMS
432 where khr_id = khrId;
433
434 day_conv_rec day_conv_csr%ROWTYPE;
435
436 l_day_convention_month VARCHAR2(30);
437 l_day_convention_year VARCHAR2(30);
438
439 -- gboomina added for Bug 6763287 - Start
440 CURSOR c_hdr IS
441 SELECT to_char(pdt.id) pid,
442 chr.scs_code
443 FROM okc_k_headers_v chr,
444 okl_k_headers khr,
445 okl_products_v pdt
446 WHERE khr.id = chr.id
447 AND chr.id = p_khr_id
448 AND khr.pdt_id = pdt.id(+);
449
450 l_pdt_id okl_products.id%type;
451 l_scs_code okc_k_headers_all_b.scs_code%type;
452 -- gboomina added for Bug 6763287 - End
453
454 BEGIN
455
456 -- gboomina added for Bug 6763287 - Start
457 OPEN c_hdr;
458 FETCH c_hdr INTO l_pdt_id, l_scs_code;
459 CLOSE c_hdr;
460 -- gboomina added for Bug 6763287 - End
461
462 OPEN day_conv_csr(p_khr_id);
463 FETCH day_conv_csr INTO day_conv_rec;
464 CLOSE day_conv_csr;
465
466 l_day_convention_month := day_conv_rec.DAYS_IN_A_MONTH_CODE;
467 l_day_convention_year := day_conv_rec.DAYS_IN_A_YEAR_CODE;
468
469 FOR l_rec_exp IN c_rec_exp LOOP
470
471 l_periodic_exp_id := NULL; -- bug 6156337
472
473 -- gboomina added for Bug 6763287 - Start
474 -- Restricting the below processing for fee lines in NEW status for Contracts.
475 -- Created expense accrual streams only for Contract lines in status
476 -- (PASSED, COMPLETE) and Investor Agreement lines in status NEW.
477 IF ( (l_rec_exp.sts_code IN ('NEW', 'INCOMPLETE') AND l_scs_code = 'INVESTOR') OR
478 (l_rec_exp.sts_code NOT IN ('NEW', 'INCOMPLETE') AND l_scs_code <> 'INVESTOR') ) THEN
479 -- gboomina added for Bug 6763287 - End
480
481 -- LLA UI does not allow deletion of Recurring Expense definitions
482 -- The only way to know whether this is a valid definition is to check all 3 attributes
483 IF (l_rec_exp.periods IS NOT NULL) AND
484 (l_rec_exp.amount IS NOT NULL) AND
485 (l_rec_exp.mpp IS NOT NULL) AND
486 l_rec_exp.amount <> 0 THEN
487
488 OPEN c_fee_idc(p_kle_id => l_rec_exp.cle_id);
489 FETCH c_fee_idc INTO l_idc_amount;
490 CLOSE c_fee_idc;
491
492 l_idc_fraction := l_idc_amount / (l_rec_exp.amount*l_rec_exp.periods);
493 l_non_idc_exp := (l_rec_exp.amount*l_rec_exp.periods)*(1-l_idc_fraction);
494 l_end_date := ADD_MONTHS(l_rec_exp.start_date, l_rec_exp.periods*l_rec_exp.mpp) - 1;
495
496 l_total_days := okl_stream_generator_pvt.get_day_count(p_start_date => l_rec_exp.start_date,
497 p_end_date => l_end_date,
498 p_arrears => 'Y',
499 x_return_status => lx_return_status);
500
501 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
502 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
503 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
504 RAISE OKL_API.G_EXCEPTION_ERROR;
505 END IF;
506
507 IF l_periodic_exp_id IS NULL THEN
508
509 OPEN fee_strm_type_csr( l_rec_exp.cle_id );
510 FETCH fee_strm_type_csr INTO fee_strm_type_rec;
511 CLOSE fee_strm_type_csr;
512
513 l_primary_sty_id := fee_strm_type_rec.styp_id;
514
515 -- gboomina added for Bug 6763287 - Start
516 IF( l_scs_code = 'INVESTOR' ) THEN
517 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
518 p_khr_id => p_khr_id,
519 p_pdt_id => l_pdt_id,
520 p_dependent_sty_purpose => 'ACCRUED_FEE_EXPENSE',
521 x_return_status => x_return_status,
522 x_dependent_sty_id => l_periodic_exp_id,
523 x_dependent_sty_name => l_sty_name);
524 ELSE
525 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
526 p_khr_id => p_khr_id,
527 p_deal_type => p_deal_type,
528 p_primary_sty_id => l_primary_sty_id,
529 p_dependent_sty_purpose => 'ACCRUED_FEE_EXPENSE', --bug# 4105286
530 x_return_status => lx_return_status,
531 x_dependent_sty_id => l_periodic_exp_id,
532 x_dependent_sty_name => l_sty_name);
533 END IF;
534 -- gboomina added for Bug 6763287 - End
535
536 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
537 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
538 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
539 RAISE OKL_API.G_EXCEPTION_ERROR;
540 END IF;
541
542 /*
543 okl_stream_generator_pvt.get_sty_details (p_sty_name => 'PERIODIC EXPENSE PAYABLE',
544 x_sty_id => l_periodic_exp_id,
545 x_sty_name => l_sty_name,
546 x_return_status => lx_return_status);
547
548 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
549 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
550 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
551 RAISE OKL_API.G_EXCEPTION_ERROR;
552 END IF;
553 */
554
555 END IF;
556
557 If l_periodic_exp_id IS NOT NULL Then
558
559 SELECT okl_sif_seq.nextval
560 INTO l_stmv_rec.transaction_number
561 FROM DUAL;
562
563 l_stmv_rec.khr_id := p_khr_id;
564 l_stmv_rec.kle_id := l_rec_exp.cle_id;
565 l_stmv_rec.sty_id := l_periodic_exp_id;
566 l_stmv_rec.sgn_code := 'MANL';
567 l_stmv_rec.say_code := 'WORK';
568 l_stmv_rec.active_yn := 'N';
569 l_stmv_rec.date_working := SYSDATE;
570
571 IF p_purpose_code = 'REPORT' THEN
572 l_stmv_rec.purpose_code := 'REPORT';
573 END IF;
574
575 -- LOOP to get amortization of Recurring Expense
576
577 l_daily_exp := l_non_idc_exp / l_total_days;
578 l_start_date := l_rec_exp.start_date;
579 l_month_end := LAST_DAY(l_rec_exp.start_date);
580 l_rec_exp_bal := l_non_idc_exp;
581
582 --DBMS_OUTPUT.PUT_LINE('TOTAL FEE EXPENSE '||l_rec_exp.amount*l_rec_exp.periods||' NON-IDC PART '||l_non_idc_exp);
583 --DBMS_OUTPUT.PUT_LINE('TOTAL DAYS '||l_total_days||' DAILY EXPENSE '||l_daily_exp);
584
585 LOOP
586
587 i := i + 1;
588
589 IF TO_CHAR(l_month_end, 'MON') IN ('JAN', 'MAR', 'MAY', 'JUL', 'AUG', 'OCT', 'DEC') THEN
590 l_selv_tbl(i).stream_element_date := l_month_end - 1;
591 ELSE
592 l_selv_tbl(i).stream_element_date := l_month_end;
593 END IF;
594
595 l_selv_tbl(i).se_line_number := i;
596
597 IF l_month_end >= l_end_date THEN
598
599 l_selv_tbl(i).amount := okl_accounting_util.validate_amount(p_amount => l_rec_exp_bal,
600 p_currency_code => p_currency_code);
601 EXIT;
602
603 ELSE
604
605 l_days := okl_stream_generator_pvt.get_day_count(p_start_date => l_start_date,
606 p_end_date => l_month_end,
607 p_arrears => 'Y',
608 x_return_status => lx_return_status);
609
610 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
611 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
612 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
613 RAISE OKL_API.G_EXCEPTION_ERROR;
614 END IF;
615
616 l_selv_tbl(i).amount := okl_accounting_util.validate_amount(p_amount => l_days * l_daily_exp,
617 p_currency_code => p_currency_code);
618 END IF;
619
620 l_rec_exp_bal := l_rec_exp_bal - l_selv_tbl(i).amount;
621 l_start_date := LAST_DAY(l_start_date) + 1;
622 l_month_end := ADD_MONTHS(l_month_end, 1);
623
624 END LOOP;
625
626
627 lx_return_status := Okl_Streams_Util.round_streams_amount(
628 p_api_version => g_api_version,
629 p_init_msg_list => G_FALSE,
630 x_msg_count => lx_msg_count,
631 x_msg_data => lx_msg_data,
632 p_chr_id => p_khr_id,
633 p_selv_tbl => l_selv_tbl,
634 x_selv_tbl => lx_selv_tbl);
635
636 IF (lx_return_status = G_RET_STS_UNEXP_ERROR) THEN
637 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
638 ELSIF (lx_return_status = G_RET_STS_ERROR) THEN
639 RAISE OKL_API.G_EXCEPTION_ERROR;
640 END IF;
641
642 l_selv_tbl.DELETE;
643 l_selv_tbl := lx_selv_tbl;
644
645 okl_streams_pub.create_streams(p_api_version => G_API_VERSION,
646 p_init_msg_list => G_FALSE,
647 x_return_status => lx_return_status,
648 x_msg_count => lx_msg_count,
649 x_msg_data => lx_msg_data,
650 p_stmv_rec => l_stmv_rec,
651 p_selv_tbl => l_selv_tbl,
652 x_stmv_rec => lx_stmv_rec,
653 x_selv_tbl => lx_selv_tbl);
654
655 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
656 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
657 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
658 RAISE OKL_API.G_EXCEPTION_ERROR;
659 END IF;
660
661 l_selv_tbl.DELETE;
662
663 End If;
664
665 END IF;
666
667 i := 0;
668
669 END IF;
670
671 END LOOP;
672
673 x_return_status := G_RET_STS_SUCCESS;
674
675 EXCEPTION
676
677 WHEN OKL_API.G_EXCEPTION_ERROR THEN
678
679 x_return_status := G_RET_STS_ERROR;
680
681 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
682
683 x_return_status := G_RET_STS_UNEXP_ERROR;
684
685 WHEN OTHERS THEN
686
687 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
688 p_msg_name => G_DB_ERROR,
689 p_token1 => G_PROG_NAME_TOKEN,
690 p_token1_value => l_prog_name,
691 p_token2 => G_SQLCODE_TOKEN,
692 p_token2_value => sqlcode,
693 p_token3 => G_SQLERRM_TOKEN,
694 p_token3_value => sqlerrm);
695
696 x_return_status := G_RET_STS_UNEXP_ERROR;
697
698 END generate_rec_exp;
699
700
701 ---------------------------------------------------------------------------
702 -- PROCEDURE generate_expense_streams
703 ---------------------------------------------------------------------------
704 PROCEDURE generate_expense_streams( p_api_version IN NUMBER,
705 p_init_msg_list IN VARCHAR2,
706 p_khr_id IN NUMBER,
707 p_purpose_code IN VARCHAR2,
708 p_deal_type IN VARCHAR2,
709 x_return_status OUT NOCOPY VARCHAR2,
710 x_msg_count OUT NOCOPY NUMBER,
711 x_msg_data OUT NOCOPY VARCHAR2) IS
712
713 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'generate_expense_streams';
714
715 CURSOR c_hdr IS
716 SELECT chr.start_date,
717 chr.end_date,
718 chr.currency_code
719 FROM okc_k_headers_b chr
720 WHERE chr.id = p_khr_id;
721
722 l_hdr c_hdr%ROWTYPE;
723
724 lx_return_status VARCHAR2(1);
725
726 BEGIN
727
728 OPEN c_hdr;
729 FETCH c_hdr INTO l_hdr;
730 CLOSE c_hdr;
731
732 generate_idc(p_khr_id => p_khr_id,
733 p_purpose_code => p_purpose_code,
734 p_currency_code => l_hdr.currency_code,
735 p_start_date => l_hdr.start_date,
736 p_end_date => l_hdr.end_date,
737 p_deal_type => p_deal_type,
738 x_return_status => lx_return_status);
739
740 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
741 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
742 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
743 RAISE OKL_API.G_EXCEPTION_ERROR;
744 END IF;
745
746 generate_rec_exp(p_khr_id => p_khr_id,
747 p_deal_type => p_deal_type,
748 p_purpose_code => p_purpose_code,
749 p_currency_code => l_hdr.currency_code,
750 x_return_status => lx_return_status);
751
752 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
754 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
755 RAISE OKL_API.G_EXCEPTION_ERROR;
756 END IF;
757
758 x_return_status := lx_return_status;
759
760 EXCEPTION
761
762 WHEN OKL_API.G_EXCEPTION_ERROR THEN
763
764 x_return_status := G_RET_STS_ERROR;
765
766 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
767
768 x_return_status := G_RET_STS_UNEXP_ERROR;
769
770 WHEN OTHERS THEN
771
772 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
773 p_msg_name => G_DB_ERROR,
774 p_token1 => G_PROG_NAME_TOKEN,
775 p_token1_value => l_prog_name,
776 p_token2 => G_SQLCODE_TOKEN,
777 p_token2_value => sqlcode,
778 p_token3 => G_SQLERRM_TOKEN,
779 p_token3_value => sqlerrm);
780
781 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
782
783 END generate_expense_streams;
784
785
786 END OKL_EXPENSE_STREAMS_PVT;