[Home] [Help]
PACKAGE BODY: APPS.OKL_EXPENSE_STREAMS_PVT
Source
1 PACKAGE BODY OKL_EXPENSE_STREAMS_PVT AS
2 /* $Header: OKLRSGEB.pls 120.15 2009/05/13 10:15:45 racheruv 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 pdt.reporting_pdt_id -- R12.1.2
444 FROM okc_k_headers_v chr,
445 okl_k_headers khr,
446 okl_products_v pdt
447 WHERE khr.id = chr.id
448 AND chr.id = p_khr_id
449 AND khr.pdt_id = pdt.id(+);
450
451 l_pdt_id okl_products.id%type;
452 l_scs_code okc_k_headers_all_b.scs_code%type;
453 l_rep_pdt_id okl_products.reporting_pdt_id%TYPE; -- R12.1.2
454 -- gboomina added for Bug 6763287 - End
455
456 BEGIN
457
458 -- gboomina added for Bug 6763287 - Start
459 OPEN c_hdr;
460 FETCH c_hdr INTO l_pdt_id, l_scs_code, l_rep_pdt_id;
461 CLOSE c_hdr;
462 -- gboomina added for Bug 6763287 - End
463
464 OPEN day_conv_csr(p_khr_id);
465 FETCH day_conv_csr INTO day_conv_rec;
466 CLOSE day_conv_csr;
467
468 l_day_convention_month := day_conv_rec.DAYS_IN_A_MONTH_CODE;
469 l_day_convention_year := day_conv_rec.DAYS_IN_A_YEAR_CODE;
470
471 FOR l_rec_exp IN c_rec_exp LOOP
472
473 l_periodic_exp_id := NULL; -- bug 6156337
474
475 -- gboomina added for Bug 6763287 - Start
476 -- Restricting the below processing for fee lines in NEW status for Contracts.
477 -- Created expense accrual streams only for Contract lines in status
478 -- (PASSED, COMPLETE) and Investor Agreement lines in status NEW.
479 IF ( (l_rec_exp.sts_code IN ('NEW', 'INCOMPLETE') AND l_scs_code = 'INVESTOR') OR
480 (l_rec_exp.sts_code NOT IN ('NEW', 'INCOMPLETE') AND l_scs_code <> 'INVESTOR') ) THEN
481 -- gboomina added for Bug 6763287 - End
482
483 -- LLA UI does not allow deletion of Recurring Expense definitions
484 -- The only way to know whether this is a valid definition is to check all 3 attributes
485 IF (l_rec_exp.periods IS NOT NULL) AND
486 (l_rec_exp.amount IS NOT NULL) AND
487 (l_rec_exp.mpp IS NOT NULL) AND
488 l_rec_exp.amount <> 0 THEN
489
490 OPEN c_fee_idc(p_kle_id => l_rec_exp.cle_id);
491 FETCH c_fee_idc INTO l_idc_amount;
492 CLOSE c_fee_idc;
493
494 l_idc_fraction := l_idc_amount / (l_rec_exp.amount*l_rec_exp.periods);
495 l_non_idc_exp := (l_rec_exp.amount*l_rec_exp.periods)*(1-l_idc_fraction);
496 l_end_date := ADD_MONTHS(l_rec_exp.start_date, l_rec_exp.periods*l_rec_exp.mpp) - 1;
497
498 l_total_days := okl_stream_generator_pvt.get_day_count(p_start_date => l_rec_exp.start_date,
499 p_end_date => l_end_date,
500 p_arrears => 'Y',
501 x_return_status => lx_return_status);
502
503 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
504 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
505 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
506 RAISE OKL_API.G_EXCEPTION_ERROR;
507 END IF;
508
509 IF l_periodic_exp_id IS NULL THEN
510
511 OPEN fee_strm_type_csr( l_rec_exp.cle_id );
512 FETCH fee_strm_type_csr INTO fee_strm_type_rec;
513 CLOSE fee_strm_type_csr;
514
515 l_primary_sty_id := fee_strm_type_rec.styp_id;
516
517 -- gboomina added for Bug 6763287 - Start
518 IF( l_scs_code = 'INVESTOR' ) THEN
519
520 if p_purpose_code = 'REPORT' then
521 l_pdt_id := l_rep_pdt_id;
522 end if;
523
524 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
525 p_khr_id => p_khr_id,
526 p_pdt_id => l_pdt_id,
527 p_dependent_sty_purpose => 'ACCRUED_FEE_EXPENSE',
528 x_return_status => x_return_status,
529 x_dependent_sty_id => l_periodic_exp_id,
530 x_dependent_sty_name => l_sty_name);
531 ELSE
532 OKL_ISG_UTILS_PVT.get_dependent_stream_type(
533 p_khr_id => p_khr_id,
534 p_deal_type => p_deal_type,
535 p_primary_sty_id => l_primary_sty_id,
536 p_dependent_sty_purpose => 'ACCRUED_FEE_EXPENSE', --bug# 4105286
537 x_return_status => lx_return_status,
538 x_dependent_sty_id => l_periodic_exp_id,
539 x_dependent_sty_name => l_sty_name);
540 END IF;
541 -- gboomina added for Bug 6763287 - End
542
543 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
544 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
545 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
546 RAISE OKL_API.G_EXCEPTION_ERROR;
547 END IF;
548
549 /*
550 okl_stream_generator_pvt.get_sty_details (p_sty_name => 'PERIODIC EXPENSE PAYABLE',
551 x_sty_id => l_periodic_exp_id,
552 x_sty_name => l_sty_name,
553 x_return_status => lx_return_status);
554
555 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
556 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
557 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
558 RAISE OKL_API.G_EXCEPTION_ERROR;
559 END IF;
560 */
561
562 END IF;
563
564 If l_periodic_exp_id IS NOT NULL Then
565
566 SELECT okl_sif_seq.nextval
567 INTO l_stmv_rec.transaction_number
568 FROM DUAL;
569
570 l_stmv_rec.khr_id := p_khr_id;
571 l_stmv_rec.kle_id := l_rec_exp.cle_id;
572 l_stmv_rec.sty_id := l_periodic_exp_id;
573 l_stmv_rec.sgn_code := 'MANL';
574 l_stmv_rec.say_code := 'WORK';
575 l_stmv_rec.active_yn := 'N';
576 l_stmv_rec.date_working := SYSDATE;
577
578 IF p_purpose_code = 'REPORT' THEN
579 l_stmv_rec.purpose_code := 'REPORT';
580 END IF;
581
582 -- LOOP to get amortization of Recurring Expense
583
584 l_daily_exp := l_non_idc_exp / l_total_days;
585 l_start_date := l_rec_exp.start_date;
586 l_month_end := LAST_DAY(l_rec_exp.start_date);
587 l_rec_exp_bal := l_non_idc_exp;
588
589 --DBMS_OUTPUT.PUT_LINE('TOTAL FEE EXPENSE '||l_rec_exp.amount*l_rec_exp.periods||' NON-IDC PART '||l_non_idc_exp);
590 --DBMS_OUTPUT.PUT_LINE('TOTAL DAYS '||l_total_days||' DAILY EXPENSE '||l_daily_exp);
591
592 LOOP
593
594 i := i + 1;
595
596 IF TO_CHAR(l_month_end, 'MON') IN ('JAN', 'MAR', 'MAY', 'JUL', 'AUG', 'OCT', 'DEC') THEN
597 l_selv_tbl(i).stream_element_date := l_month_end - 1;
598 ELSE
599 l_selv_tbl(i).stream_element_date := l_month_end;
600 END IF;
601
602 l_selv_tbl(i).se_line_number := i;
603
604 IF l_month_end >= l_end_date THEN
605
606 l_selv_tbl(i).amount := okl_accounting_util.validate_amount(p_amount => l_rec_exp_bal,
607 p_currency_code => p_currency_code);
608 EXIT;
609
610 ELSE
611
612 l_days := okl_stream_generator_pvt.get_day_count(p_start_date => l_start_date,
613 p_end_date => l_month_end,
614 p_arrears => 'Y',
615 x_return_status => lx_return_status);
616
617 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
618 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
619 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
620 RAISE OKL_API.G_EXCEPTION_ERROR;
621 END IF;
622
623 l_selv_tbl(i).amount := okl_accounting_util.validate_amount(p_amount => l_days * l_daily_exp,
624 p_currency_code => p_currency_code);
625 END IF;
626
627 l_rec_exp_bal := l_rec_exp_bal - l_selv_tbl(i).amount;
628 l_start_date := LAST_DAY(l_start_date) + 1;
629 l_month_end := ADD_MONTHS(l_month_end, 1);
630
631 END LOOP;
632
633
634 lx_return_status := Okl_Streams_Util.round_streams_amount(
635 p_api_version => g_api_version,
636 p_init_msg_list => G_FALSE,
637 x_msg_count => lx_msg_count,
638 x_msg_data => lx_msg_data,
639 p_chr_id => p_khr_id,
640 p_selv_tbl => l_selv_tbl,
641 x_selv_tbl => lx_selv_tbl);
642
643 IF (lx_return_status = G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (lx_return_status = G_RET_STS_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_ERROR;
647 END IF;
648
649 l_selv_tbl.DELETE;
650 l_selv_tbl := lx_selv_tbl;
651
652 okl_streams_pub.create_streams(p_api_version => G_API_VERSION,
653 p_init_msg_list => G_FALSE,
654 x_return_status => lx_return_status,
655 x_msg_count => lx_msg_count,
656 x_msg_data => lx_msg_data,
657 p_stmv_rec => l_stmv_rec,
658 p_selv_tbl => l_selv_tbl,
659 x_stmv_rec => lx_stmv_rec,
660 x_selv_tbl => lx_selv_tbl);
661
662 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
663 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
664 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
665 RAISE OKL_API.G_EXCEPTION_ERROR;
666 END IF;
667
668 l_selv_tbl.DELETE;
669
670 End If;
671
672 END IF;
673
674 i := 0;
675
676 END IF;
677
678 END LOOP;
679
680 x_return_status := G_RET_STS_SUCCESS;
681
682 EXCEPTION
683
684 WHEN OKL_API.G_EXCEPTION_ERROR THEN
685
686 x_return_status := G_RET_STS_ERROR;
687
688 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
689
690 x_return_status := G_RET_STS_UNEXP_ERROR;
691
692 WHEN OTHERS THEN
693
694 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
695 p_msg_name => G_DB_ERROR,
696 p_token1 => G_PROG_NAME_TOKEN,
697 p_token1_value => l_prog_name,
698 p_token2 => G_SQLCODE_TOKEN,
699 p_token2_value => sqlcode,
700 p_token3 => G_SQLERRM_TOKEN,
701 p_token3_value => sqlerrm);
702
703 x_return_status := G_RET_STS_UNEXP_ERROR;
704
705 END generate_rec_exp;
706
707
708 ---------------------------------------------------------------------------
709 -- PROCEDURE generate_expense_streams
710 ---------------------------------------------------------------------------
711 PROCEDURE generate_expense_streams( p_api_version IN NUMBER,
712 p_init_msg_list IN VARCHAR2,
713 p_khr_id IN NUMBER,
714 p_purpose_code IN VARCHAR2,
715 p_deal_type IN VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2) IS
719
720 l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'generate_expense_streams';
721
722 CURSOR c_hdr IS
723 SELECT chr.start_date,
724 chr.end_date,
725 chr.currency_code
726 FROM okc_k_headers_b chr
727 WHERE chr.id = p_khr_id;
728
729 l_hdr c_hdr%ROWTYPE;
730
731 lx_return_status VARCHAR2(1);
732
733 BEGIN
734
735 OPEN c_hdr;
736 FETCH c_hdr INTO l_hdr;
737 CLOSE c_hdr;
738
739 generate_idc(p_khr_id => p_khr_id,
740 p_purpose_code => p_purpose_code,
741 p_currency_code => l_hdr.currency_code,
742 p_start_date => l_hdr.start_date,
743 p_end_date => l_hdr.end_date,
744 p_deal_type => p_deal_type,
745 x_return_status => lx_return_status);
746
747 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
748 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
749 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
750 RAISE OKL_API.G_EXCEPTION_ERROR;
751 END IF;
752
753 generate_rec_exp(p_khr_id => p_khr_id,
754 p_deal_type => p_deal_type,
755 p_purpose_code => p_purpose_code,
756 p_currency_code => l_hdr.currency_code,
757 x_return_status => lx_return_status);
758
759 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
761 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
762 RAISE OKL_API.G_EXCEPTION_ERROR;
763 END IF;
764
765 x_return_status := lx_return_status;
766
767 EXCEPTION
768
769 WHEN OKL_API.G_EXCEPTION_ERROR THEN
770
771 x_return_status := G_RET_STS_ERROR;
772
773 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
774
775 x_return_status := G_RET_STS_UNEXP_ERROR;
776
777 WHEN OTHERS THEN
778
779 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
780 p_msg_name => G_DB_ERROR,
781 p_token1 => G_PROG_NAME_TOKEN,
782 p_token1_value => l_prog_name,
783 p_token2 => G_SQLCODE_TOKEN,
784 p_token2_value => sqlcode,
785 p_token3 => G_SQLERRM_TOKEN,
786 p_token3_value => sqlerrm);
787
788 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
789
790 END generate_expense_streams;
791
792
793 END OKL_EXPENSE_STREAMS_PVT;