[Home] [Help]
PACKAGE BODY: APPS.OKL_GENERATE_PV_RENT_PVT
Source
1 PACKAGE BODY OKL_GENERATE_PV_RENT_PVT AS
2 /* $Header: OKLRTPVB.pls 120.1.12020000.2 2012/08/07 13:42:13 rpillay ship $ */
3
4 l_debug_level NUMBER :=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 l_proc_level NUMBER :=FND_LOG.LEVEL_PROCEDURE;
6 l_stat_level NUMBER :=FND_LOG.LEVEL_STATEMENT;
7
8 -- Start of comments
9 -- API name : generate_total_pv_rent
10 -- Pre-reqs : None
11 -- Function :
12 -- Parameters :
13 -- Version : 1.0
14 -- History : Durga Janaswamy created
15 -- End of comments
16
17 PROCEDURE generate_total_pv_rent
18 (p_api_version IN NUMBER
19 ,p_init_msg_list IN VARCHAR2
20 ,p_khr_id IN NUMBER
21 ,x_total_pv_rent OUT NOCOPY NUMBER
22 ,x_return_status OUT NOCOPY VARCHAR2
23 ,x_msg_count OUT NOCOPY NUMBER
24 ,x_msg_data OUT NOCOPY VARCHAR2
25 )
26 IS
27
28 -----------------------------------------------------------------
29 -- Declare Process Variable
30 -----------------------------------------------------------------
31 l_api_version CONSTANT NUMBER := 1;
32 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_TOTAL_PV_RENT';
33 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
34
35 ------------------------------------------------------------
36 -- Declare records: Extension Headers, Extension Lines
37 ------------------------------------------------------------
38
39 CURSOR c_hdr_csr (p_khr_id NUMBER) IS
40 SELECT chr.template_yn,
41 chr.currency_code,
42 chr.start_date,
43 chr.end_date,
44 khr.deal_type,
45 khr.term_duration,
46 nvl(rpar.base_rate, 10) base_rate
47 FROM okc_k_headers_b chr,
48 okl_k_headers khr,
49 OKL_K_RATE_PARAMS rpar
50 WHERE khr.id = p_khr_id
51 AND chr.id = khr.id
52 AND rpar.khr_id(+) = khr.id;
53
54 l_hdr_csr_rec c_hdr_csr%ROWTYPE;
55
56 -- UNSCHEDULED_PRINCIPAL_PAYMENT, PRINCIPAL_PAYMENT these are for LOAN only
57 -- UNSCHEDULED_INTEREST_PAYMENT
58
59 CURSOR c_asset_id_csr (p_khr_id NUMBER) IS
60 SELECT cle.id, sty.stream_type_purpose,
61 NVL(kle.capital_amount,0) capital_amount,
62 NVL(kle.residual_value, 0) residual_value
63 FROM okc_rule_groups_b rgp,
64 okc_rules_b rul1,
65 okc_k_lines_b cle,
66 okl_k_lines kle,
67 okc_line_styles_b lse,
68 okl_strm_type_b sty
69 WHERE rul1.dnz_chr_id = p_khr_id
70 AND rul1.rule_information_category = 'LASLH'
71 AND rul1.rgp_id = rgp.id
72 AND rgp.cle_id = cle.id
73 AND cle.sts_code in ('INCOMPLETE','ENTERED','NEW')
74 AND cle.id = kle.id
75 AND cle.lse_id = lse.id
76 AND lse.lty_code = 'FREE_FORM1'
77 AND sty.id = to_number(rul1.object1_id1)
78 AND (sty.stream_type_purpose = 'RENT'
79 or sty.stream_type_purpose = 'DOWN_PAYMENT');
80
81 l_asset_id_tbl asset_id_tbl_type;
82
83 l_total_cash_inflow_tbl cash_flow_tbl;
84
85 l_total_rent_inflow_tbl cash_flow_tbl;
86 lx_total_rent_inflow_tbl cash_flow_tbl;
87
88 l_pricing_engine okl_st_gen_tmpt_sets.pricing_engine%TYPE;
89
90 l_day_convention_month okl_st_gen_tmpt_sets.days_in_month_code%TYPE;
91
92 l_day_convention_year okl_st_gen_tmpt_sets.days_in_yr_code%TYPE;
93
94 l_arrears_pay_dates_option okl_st_gen_tmpt_sets.isg_arrears_pay_dates_option%type;
95
96 l_time_zero_cost NUMBER := 0;
97
98 l_cost NUMBER;
99
100 l_residual_value NUMBER := 0;
101 l_guess_iir NUMBER;
102
103 l_iir NUMBER;
104
105 l_pv_rent NUMBER := 0;
106
107 l_pv_residual_value NUMBER := 0;
108
109 l_period_residual_value NUMBER;
110
111 lx_total_rent_inflow_tbl_count NUMBER := 0;
112
113 l_end_date DATE;
114
115 l_dpp NUMBER;
116
117 l_ppy NUMBER;
118
119 l_precision fnd_currencies.precision%TYPE;
120
121 i BINARY_INTEGER := 0;
122
123
124 BEGIN
125
126 x_return_status := OKL_API.G_RET_STS_SUCCESS;
127
128 IF (l_proc_level >= l_debug_level) THEN
129 FND_LOG.STRING(l_proc_level,'OKL_GENERATE_PV_RENT_PVT','Begin(+)');
130 END IF;
131
132 l_return_status := OKL_API.START_ACTIVITY(
133 p_api_name => l_api_name,
134 p_pkg_name => g_pkg_name,
135 p_init_msg_list => p_init_msg_list,
136 l_api_version => l_api_version,
137 p_api_version => p_api_version,
138 p_api_type => '_PVT',
139 x_return_status => l_return_status);
140
141 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
143 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
144 RAISE OKL_API.G_EXCEPTION_ERROR;
145 END IF;
146
147 OPEN c_hdr_csr (p_khr_id);
148 FETCH c_hdr_csr INTO l_hdr_csr_rec;
149 CLOSE c_hdr_csr;
150
151 IF l_stat_level >= l_debug_level THEN
152 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','khr_id:'||to_char(p_khr_id));
153 END IF;
154
155 l_guess_iir := l_hdr_csr_rec.base_rate / 100.0;
156
157 -- Fetch pricing engine value which will be used to determine arrears payment date for ESG
158 OKL_STREAMS_UTIL.get_pricing_engine(
159 p_khr_id => p_khr_id,
160 x_pricing_engine => l_pricing_engine,
161 x_return_status => x_return_status);
162
163 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
164 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
165 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
166 RAISE OKL_API.G_EXCEPTION_ERROR;
167 END IF;
168
169 -- l_pricing_engine = 'EXTERNAL'
170
171 -- Fetch the day convention ..
172 -- p_source cannot be ESG because it returns days_in_month as 360
173 OKL_PRICING_UTILS_PVT.get_day_convention(
174 p_id => p_khr_id,
175 p_source => 'ISG',
176 x_days_in_month => l_day_convention_month,
177 x_days_in_year => l_day_convention_year,
178 x_return_status => l_return_status);
179
180
181 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
182 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
183 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
184 RAISE OKL_API.G_EXCEPTION_ERROR;
185 END IF;
186
187 IF l_stat_level >= l_debug_level THEN
188 fnd_log.STRING(l_stat_level,
189 'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Month / Year = '||to_char(l_day_convention_month) || '/' || to_char(l_day_convention_year));
190 END IF;
191
192 IF l_pricing_engine = 'INTERNAL' THEN
193 OKL_ISG_UTILS_PVT.get_arrears_pay_dates_option(
194 p_khr_id => p_khr_id,
195 x_arrears_pay_dates_option => l_arrears_pay_dates_option,
196 x_return_status => l_return_status);
197 END IF;
198
199
200 IF l_pricing_engine = 'EXTERNAL' THEN
201 l_arrears_pay_dates_option := 'FIRST_DAY_OF_NEXT_PERIOD';
202 ELSE
203 l_arrears_pay_dates_option := 'LAST_DAY_OF_PERIOD';
204 END IF;
205
206 IF l_stat_level >= l_debug_level THEN
207 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_arrears_pay_dates_option :'||l_arrears_pay_dates_option);
208 END IF;
209
210 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
212 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
213 RAISE OKL_API.G_EXCEPTION_ERROR;
214 END IF;
215
216 SELECT NVL(precision,0)
217 INTO l_precision
218 FROM fnd_currencies
219 WHERE currency_code = l_hdr_csr_rec.currency_code;
220
221 i := 0;
222 l_asset_id_tbl.delete;
223 l_total_rent_inflow_tbl.delete;
224 lx_total_rent_inflow_tbl.delete;
225
226 OPEN c_asset_id_csr (p_khr_id);
227 FETCH c_asset_id_csr BULK COLLECT INTO l_asset_id_tbl;
228
229 IF l_stat_level >= l_debug_level THEN
230 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Number of records in l_asset_id_tbl :'||to_char(l_asset_id_tbl.COUNT));
231 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Calling procedure generate_asset_rent.');
232 END IF;
233
234 --Bug 7015073: Start
235 IF l_asset_id_tbl.COUNT > 0 THEN
236 FOR a in l_asset_id_tbl.FIRST..l_asset_id_tbl.LAST LOOP
237 i := i + 1;
238 IF l_stat_level >= l_debug_level THEN
239 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_asset_id_tbl(i).id :'||to_char(l_asset_id_tbl(i).id));
240 END IF;
241
242 -- generating the cash inflows
243 OKL_GENERATE_PV_RENT_PVT.generate_asset_rent
244 (p_api_version => p_api_version,
245 p_init_msg_list => p_init_msg_list,
246 p_khr_id => p_khr_id,
247 p_kle_id => l_asset_id_tbl(i).id,
248 p_contract_start_date => l_hdr_csr_rec.start_date,
249 p_day_convention_month => l_day_convention_month,
250 p_day_convention_year => l_day_convention_year,
251 p_arrears_pay_dates_option => l_arrears_pay_dates_option,
252 p_total_rent_inflow_tbl => l_total_rent_inflow_tbl,
253 x_total_rent_inflow_tbl => lx_total_rent_inflow_tbl,
254 x_dpp => l_dpp,
255 x_ppy => l_ppy,
256 x_return_status => x_return_status,
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data
259 );
260
261 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
262 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
263 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
264 RAISE OKL_API.G_EXCEPTION_ERROR;
265 END IF;
266
267 l_total_rent_inflow_tbl := lx_total_rent_inflow_tbl;
268
269
270
271 -- IF l_asset_cost.start_date <= p_start_date THEN
272 -- l_cost := nvl(l_asset_cost.capital_amount, 0) + nvl(l_asset_cost.capitalized_interest,0);
273 l_cost := NVL(l_asset_id_tbl(i).capital_amount, 0);
274 l_time_zero_cost := l_time_zero_cost + NVL(l_cost, 0);
275 -- END IF;
276
277 l_residual_value := NVL(l_asset_id_tbl(i).residual_value, 0) + l_residual_value;
278
279 END LOOP;
280 END IF;
281 --Bug 7015073: End
282
283 l_end_date := (ADD_MONTHS(l_hdr_csr_rec.start_date,l_hdr_csr_rec.term_duration) - 1);
284
285 IF l_stat_level >= l_debug_level THEN
286 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_residual_value:'||to_char(l_residual_value));
287 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_time_zero_cost:'||to_char(l_time_zero_cost));
288 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_end_date :'||to_char(l_end_date));
289 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Total_rent_inflow_tbl_count :'||to_char(l_total_rent_inflow_tbl.COUNT));
290 END IF;
291
292 -- l_total_cash_inflow_tbl = lx_total_rent_inflow_tbl + residual value
293
294 l_total_cash_inflow_tbl := lx_total_rent_inflow_tbl;
295
296 lx_total_rent_inflow_tbl_count :=lx_total_rent_inflow_tbl.count;
297
298
299 IF l_residual_value > 0 THEN
300 IF l_stat_level >= l_debug_level THEN
301 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_dpp:'||to_char(l_dpp));
302 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_ppy:'||to_char(l_ppy));
303 END IF;
304
305 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_amount := l_residual_value;
306 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_number := lx_total_rent_inflow_tbl_count+1; -- TBD
307 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_date := l_end_date;
308 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_arrears := 'Y';
309 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_stub := 'N';
310 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_purpose := 'RESIDUAL';
311 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_dpp := l_dpp;
312 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_ppy := l_ppy;
313 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).kleId := -1;
314
315 l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_days := OKL_PRICING_UTILS_PVT.get_day_count(
316 p_start_date => l_hdr_csr_rec.start_date,
317 p_days_in_month => l_day_convention_month,
318 p_days_in_year => l_day_convention_year,
319 p_end_date => l_end_date,
320 p_arrears => 'Y',
321 x_return_status => l_return_status);
322
323
324 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
325 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
327 RAISE OKL_API.G_EXCEPTION_ERROR;
328 END IF;
329
330 END IF;
331
332 IF l_stat_level >= l_debug_level THEN
333 IF l_total_rent_inflow_tbl.COUNT > 0 THEN
334 FOR j IN l_total_rent_inflow_tbl.FIRST .. l_total_rent_inflow_tbl.LAST
335 LOOP
336 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent',
337 TO_CHAR(l_total_rent_inflow_tbl(j).cf_number||' '||l_total_rent_inflow_tbl(j).cf_date || ' ' || l_total_rent_inflow_tbl(j).cf_amount));
338 END LOOP;
339 END IF;
340 END IF;
341
342 IF l_stat_level >= l_debug_level THEN
343 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Total_cash_inflow_tbl_count:'||to_char(l_total_cash_inflow_tbl.count));
344 END IF;
345
346 IF l_stat_level >= l_debug_level THEN
347 IF l_total_cash_inflow_tbl.COUNT > 0 THEN
348 FOR j IN l_total_cash_inflow_tbl.FIRST .. l_total_cash_inflow_tbl.LAST
349 LOOP
350 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent',
351 TO_CHAR(l_total_cash_inflow_tbl(j).cf_number||' '||l_total_cash_inflow_tbl(j).cf_date || ' ' || l_total_cash_inflow_tbl(j).cf_amount));
352 END LOOP;
353 END IF;
354 END IF;
355
356
357 -- calculating IIR
358 IF l_stat_level >= l_debug_level THEN
359 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','Calling procedure compute_iir');
360 END IF;
361
362 IF NVL(l_time_zero_cost,0) = 0
363 THEN
364 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
365 p_msg_name => 'OKL_IRR_ZERO_DIV');
366 RAISE OKL_API.G_EXCEPTION_ERROR;
367 END IF;
368 OKL_GENERATE_PV_RENT_PVT.compute_iir ( p_khr_id => p_khr_id,
369 p_cash_in_flows_tbl => l_total_cash_inflow_tbl,
370 p_cash_out_flows => l_time_zero_cost,
371 p_initial_iir => l_guess_iir,
372 p_precision => l_precision,
373 x_iir => l_iir,
374 x_return_status => x_return_status,
375 x_msg_count => x_msg_count,
376 x_msg_data => x_msg_data
377 );
378
379 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
380 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
381 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
382 RAISE OKL_API.G_EXCEPTION_ERROR;
383 END IF;
384
385 IF l_stat_level >= l_debug_level THEN
386 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_iir: '||TO_CHAR(l_iir));
387 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','calculating the PV rent');
388 END IF;
389
390 -- calculating the PV rent
391
392 IF l_residual_value > 0 THEN
393 IF l_stat_level >= l_debug_level THEN
394 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','calculating the PV of residual value');
395 END IF;
396
397 l_period_residual_value := l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_days/l_dpp;
398
399 l_pv_residual_value := l_residual_value / POWER( 1 + (l_iir/(l_ppy)), l_period_residual_value);
400
401 IF l_stat_level >= l_debug_level THEN
402 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent',
403 'PV of residual value: '||TO_CHAR(l_pv_residual_value));
404 END IF;
405 END IF;
406
407 l_pv_rent := l_time_zero_cost - nvl(l_pv_residual_value,0);
408
409
410
411 x_total_pv_rent := ROUND(l_pv_rent,l_precision);
412
413 IF l_stat_level >= l_debug_level THEN
414 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','x_total_pv_rent: '||TO_CHAR(x_total_pv_rent));
415 END IF;
416
417
418 IF (l_proc_level >= l_debug_level) THEN
419 FND_LOG.STRING(l_proc_level,'OKL_GENERATE_PV_RENT_PVT','End(-)');
420 END IF;
421
422 okl_api.end_activity(x_msg_count => x_msg_count
423 ,x_msg_data => x_msg_data);
424
425
426 EXCEPTION
427 ------------------------------------------------------------
428 -- Exception handling
429 ------------------------------------------------------------
430
431 WHEN OKL_API.G_EXCEPTION_ERROR THEN
432
433 IF c_asset_id_csr%ISOPEN THEN
434 CLOSE c_asset_id_csr;
435 END IF;
436
437 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
438 p_api_name => l_api_name,
439 p_pkg_name => G_PKG_NAME,
440 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
441 x_msg_count => x_msg_count,
442 x_msg_data => x_msg_data,
443 p_api_type => '_PVT');
444
445 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
446
447 IF c_asset_id_csr%ISOPEN THEN
448 CLOSE c_asset_id_csr;
449 END IF;
450
451 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
452 p_api_name => l_api_name,
453 p_pkg_name => G_PKG_NAME,
454 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
455 x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data,
457 p_api_type => '_PVT');
458
459 WHEN OTHERS THEN
460
461 IF c_asset_id_csr%ISOPEN THEN
462 CLOSE c_asset_id_csr;
463 END IF;
464
465 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
466 p_api_name => l_api_name,
467 p_pkg_name => G_PKG_NAME,
468 p_exc_name => 'OTHERS',
469 x_msg_count => x_msg_count,
470 x_msg_data => x_msg_data,
471 p_api_type => '_PVT');
472
473 END generate_total_pv_rent;
474
475
476 -- Start of comments
477 -- API name :
478 -- Pre-reqs : None
479 -- Function :
480 -- Parameters :
481 -- Version : 1.0
482 -- History : Durga Janaswamy created
483 -- End of comments
484
485
486 PROCEDURE generate_asset_rent
487 (p_api_version IN NUMBER
488 ,p_init_msg_list IN VARCHAR2
489 ,p_khr_id IN NUMBER
490 ,p_kle_id IN NUMBER
491 ,p_contract_start_date IN DATE
492 ,p_day_convention_month IN VARCHAR2
493 ,p_day_convention_year IN VARCHAR2
494 ,p_arrears_pay_dates_option IN VARCHAR2
495 ,p_total_rent_inflow_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl
496 ,x_total_rent_inflow_tbl OUT NOCOPY OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl
497 ,x_dpp OUT NOCOPY NUMBER
498 ,x_ppy OUT NOCOPY NUMBER
499 ,x_return_status OUT NOCOPY VARCHAR2
500 ,x_msg_count OUT NOCOPY NUMBER
501 ,x_msg_data OUT NOCOPY VARCHAR2
502 )
503 IS
504
505 -----------------------------------------------------------------
506 -- Declare Process Variable
507 -----------------------------------------------------------------
508 l_api_version CONSTANT NUMBER := 1;
509 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_ASSET_PV_RENT';
510 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
511
512
513 ------------------------------------------------------------
514 -- Declare records: Extension Headers, Extension Lines
515 ------------------------------------------------------------
516
517 CURSOR c_payment_details_csr (p_khr_id NUMBER, p_kle_id NUMBER) IS
518 SELECT rgp.cle_id cle_id, sty.stream_type_purpose, sty.id sty_id,
519 FND_DATE.canonical_to_date(sll.rule_information2) start_date,
520 TO_NUMBER(SLL.rule_information3) periods,
521 sll.object1_id1 frequency,
522 sll.rule_information5 structure,
523 NVL(sll.rule_information10, 'N') arrears_yn,
524 DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) days_per_period,
525 DECODE(sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
526 DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
527 FND_NUMBER.canonical_to_number(sll.rule_information6) amount,
528 TO_NUMBER(sll.rule_information7) stub_days,
529 TO_NUMBER(sll.rule_information8) stub_amount
530 FROM okc_rules_b sll,
531 okc_rules_b slh,
532 okc_rule_groups_b rgp,
533 okl_strm_type_b sty
534 WHERE rgp.dnz_chr_id = p_khr_id
535 AND rgp.cle_id = p_kle_id
536 AND rgp.rgd_code= 'LALEVL'
537 AND rgp.id = slh.rgp_id
538 AND slh.rule_information_category = 'LASLH'
539 AND TO_NUMBER(slh.object1_id1) = sty.id
540 AND sty.version = '1.0'
541 AND (sty.stream_type_purpose = 'RENT'
542 or sty.stream_type_purpose = 'DOWN_PAYMENT')
543 AND TO_CHAR(slh.id) = sll.object2_id1
544 AND sll.rule_information_category = 'LASLL'
545 ORDER BY fnd_date.canonical_to_date(sll.rule_information2);
546
547 l_payment_details_csr_rec c_payment_details_csr%ROWTYPE;
548
549 l_cash_inflow_tbl cash_flow_tbl;
550 l_rent_inflow_tbl cash_flow_tbl;
551 lx_rent_inflow_tbl cash_flow_tbl;
552 lx_rent_inflow_tbl_count NUMBER := 0;
553
554 l_recurrence_date DATE := NULL;
555 l_old_cle_id NUMBER;
556 l_old_sty_id NUMBER;
557
558 l_dpp NUMBER;
559
560 l_ppy NUMBER;
561
562 BEGIN
563
564 x_return_status := OKL_API.G_RET_STS_SUCCESS;
565
566 IF l_stat_level >= l_debug_level THEN
567 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent','Begin(+)');
568 END IF;
569
570 l_return_status := OKL_API.START_ACTIVITY(
571 p_api_name => l_api_name,
572 p_pkg_name => g_pkg_name,
573 p_init_msg_list => p_init_msg_list,
574 l_api_version => l_api_version,
575 p_api_version => p_api_version,
576 p_api_type => '_PVT',
577 x_return_status => l_return_status);
578
579 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
582 RAISE OKL_API.G_EXCEPTION_ERROR;
583 END IF;
584
585
586
587 l_rent_inflow_tbl.delete;
588 lx_rent_inflow_tbl.delete;
589 l_rent_inflow_tbl := p_total_rent_inflow_tbl;
590
591 FOR l_payment_details_csr_rec IN c_payment_details_csr (p_khr_id, p_kle_id)
592 LOOP
593
594 IF l_payment_details_csr_rec.start_date IS NULL
595 THEN
596 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
597 p_msg_name => 'OKL_NO_SLL_SDATE');
598 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
599 END IF;
600
601 IF((l_payment_details_csr_rec.periods IS NULL) AND (l_payment_details_csr_rec.stub_days IS NOT NULL)) THEN
602 --Set the recurrence date to null for stub payment
603 l_recurrence_date := NULL;
604 ELSIF(l_recurrence_date IS NULL
605 OR l_old_cle_id <> l_payment_details_csr_rec.cle_id
606 OR l_old_sty_id <> l_payment_details_csr_rec.sty_id) THEN
607 --Set the recurrence date as periodic payment level start date
608 l_recurrence_date := l_payment_details_csr_rec.start_date;
609 END IF;
610 l_old_cle_id := l_payment_details_csr_rec.cle_id;
611 l_old_sty_id := l_payment_details_csr_rec.sty_id;
612
613
614 OKL_GENERATE_PV_RENT_PVT.generate_stream_elements(
615 p_start_date => l_payment_details_csr_rec.start_date,
616 p_periods => l_payment_details_csr_rec.periods,
617 p_frequency => l_payment_details_csr_rec.frequency,
618 p_structure => l_payment_details_csr_rec.structure,
619 p_arrears_yn => l_payment_details_csr_rec.arrears_yn,
620 p_amount => l_payment_details_csr_rec.amount,
621 p_stub_days => l_payment_details_csr_rec.stub_days,
622 p_stub_amount => l_payment_details_csr_rec.stub_amount,
623 p_khr_id => p_khr_id,
624 p_kle_id => p_kle_id,
625 p_purpose_code => l_payment_details_csr_rec.stream_type_purpose,
626 p_recurrence_date => l_recurrence_date,
627 p_dpp => l_payment_details_csr_rec.days_per_period,
628 p_ppy => l_payment_details_csr_rec.periods_per_year,
629 p_months_factor => l_payment_details_csr_rec.months_per_period,
630 p_contract_start_date => p_contract_start_date,
631 p_day_convention_month => p_day_convention_month,
632 p_day_convention_year => p_day_convention_year,
633 p_arrears_pay_dates_option => p_arrears_pay_dates_option,
634 p_rent_inflow_tbl => l_rent_inflow_tbl,
635 x_rent_inflow_tbl => lx_rent_inflow_tbl,
636 x_return_status => l_return_status,
637 x_msg_count => x_msg_count,
638 x_msg_data => x_msg_data
639 );
640
641 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
642 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
643 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
644 RAISE OKL_API.G_EXCEPTION_ERROR;
645 END IF;
646
647 l_rent_inflow_tbl := lx_rent_inflow_tbl;
648
649 l_dpp := l_payment_details_csr_rec.days_per_period;
650 l_ppy := l_payment_details_csr_rec.periods_per_year;
651
652 END LOOP;
653
654 x_total_rent_inflow_tbl := lx_rent_inflow_tbl;
655
656 x_dpp := l_dpp;
657
658 x_ppy := l_ppy;
659
660
661 IF l_stat_level >= l_debug_level THEN
662 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent',
663 'x_total_rent_inflow_tbl.count :'||TO_CHAR(x_total_rent_inflow_tbl.count));
664 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent','End(-)');
665 END IF;
666
667
668 okl_api.end_activity(x_msg_count => x_msg_count
669 ,x_msg_data => x_msg_data);
670
671
672
673 EXCEPTION
674 ------------------------------------------------------------
675 -- Exception handling
676 ------------------------------------------------------------
677
678 WHEN OKL_API.G_EXCEPTION_ERROR THEN
679
680 IF c_payment_details_csr%ISOPEN THEN
681 CLOSE c_payment_details_csr;
682 END IF;
683
684 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
685 p_api_name => l_api_name,
686 p_pkg_name => G_PKG_NAME,
687 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 p_api_type => '_PVT');
691
692 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
693
694 IF c_payment_details_csr%ISOPEN THEN
695 CLOSE c_payment_details_csr;
696 END IF;
697
698 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
699 p_api_name => l_api_name,
700 p_pkg_name => G_PKG_NAME,
701 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data,
704 p_api_type => '_PVT');
705
706 WHEN OTHERS THEN
707
708 IF c_payment_details_csr%ISOPEN THEN
709 CLOSE c_payment_details_csr;
710 END IF;
711
712 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
713 p_api_name => l_api_name,
714 p_pkg_name => G_PKG_NAME,
715 p_exc_name => 'OTHERS',
716 x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data,
718 p_api_type => '_PVT');
719
720 END generate_asset_rent;
721
722
723
724 -- Start of comments
725 -- API name :
726 -- Pre-reqs : None
727 -- Function :
728 -- Parameters :
729 -- Version : 1.0
730 -- History : Durga Janaswamy created
731 -- End of comments
732
733 PROCEDURE generate_stream_elements( p_start_date IN DATE,
734 p_periods IN NUMBER,
735 p_frequency IN VARCHAR2,
736 p_structure IN VARCHAR2,
737 p_arrears_yn IN VARCHAR2,
738 p_amount IN NUMBER,
739 p_stub_days IN NUMBER,
740 p_stub_amount IN NUMBER,
741 p_khr_id IN NUMBER,
742 p_kle_id IN NUMBER,
743 p_purpose_code IN VARCHAR2,
744 p_recurrence_date IN DATE,
745 p_dpp IN NUMBER,
746 p_ppy IN NUMBER,
747 p_months_factor IN NUMBER,
748 p_contract_start_date IN DATE,
749 p_day_convention_month IN VARCHAR2,
750 p_day_convention_year IN VARCHAR2,
751 p_arrears_pay_dates_option IN VARCHAR2,
752 p_rent_inflow_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
753 x_rent_inflow_tbl OUT NOCOPY OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2
757 ) IS
758
759
760 -----------------------------------------------------------------
761 -- Declare Process Variable
762 -----------------------------------------------------------------
763 l_api_version CONSTANT NUMBER := 1;
764 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_STREAM_ELEMENTS';
765 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
766
767
768 ------------------------------------------------------------
769 -- Declare records: Extension Headers, Extension Lines
770 ------------------------------------------------------------
771
772 l_rent_inflow_tbl cash_flow_tbl;
773
774
775 l_element_count NUMBER;
776 l_amount NUMBER;
777
778 i BINARY_INTEGER := 0;
779 l_rent_inflow_tbl_count NUMBER := 0;
780
781 BEGIN
782
783
784 IF l_stat_level >= l_debug_level THEN
785 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements','Begin(+)');
786 END IF;
787
788 x_rent_inflow_tbl := p_rent_inflow_tbl;
789
790 l_rent_inflow_tbl_count := nvl(p_rent_inflow_tbl.count,0);
791
792 IF ( p_amount IS NULL )
793 THEN
794 l_amount := NULL;
795 ELSE
796 l_amount := p_amount;
797 END IF;
798
799
800 IF ( p_periods IS NULL ) AND ( p_stub_days IS NOT NULL )
801 THEN
802 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_amount := p_stub_amount;
803 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_number := l_rent_inflow_tbl_count + 1; -- TBD
804
805 IF p_arrears_yn = 'Y' THEN
806 IF p_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD' THEN
807 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date + p_stub_days;
808 ELSE
809 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date + p_stub_days - 1;
810 END IF;
811 ELSE
812 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date;
813 END IF;
814
815 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_arrears := p_arrears_yn;
816 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_stub := 'Y';
817 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_purpose := p_purpose_code;
818 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_dpp := p_dpp;
819 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_ppy := p_ppy;
820 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).kleId := p_kle_id;
821
822 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_days := OKL_PRICING_UTILS_PVT.get_day_count(
823 p_start_date => p_contract_start_date,
824 p_days_in_month => p_day_convention_month,
825 p_days_in_year => p_day_convention_year,
826 p_end_date => x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date,
827 p_arrears => x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_arrears,
828 x_return_status => l_return_status);
829 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
830 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
831 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_ERROR;
833 END IF;
834
835 ELSE
836
837
838 l_element_count := p_periods;
839 IF l_stat_level >= l_debug_level THEN
840 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
841 'creating elements:'||TO_CHAR(l_element_count));
842 END IF;
843
844 FOR i IN (l_rent_inflow_tbl_count+1) .. (l_rent_inflow_tbl_count+l_element_count)
845 LOOP
846 x_rent_inflow_tbl(i).cf_amount := l_amount;
847
848
849 OKL_STREAM_GENERATOR_PVT.get_sel_date(
850 p_start_date => p_start_date,
851 p_advance_or_arrears => p_arrears_yn,
852 p_periods_after => i - l_rent_inflow_tbl_count,
853 p_months_per_period => p_months_factor,
854 x_date => x_rent_inflow_tbl(i).cf_date,
855 x_return_status => l_return_status,
856 p_recurrence_date => p_recurrence_date,
857 p_arrears_pay_dates_option => p_arrears_pay_dates_option);
858
859 IF l_stat_level >= l_debug_level THEN
860 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
861 'get_sel_date-p_start_date:'||to_char(p_start_date)||' p_arrears_yn:'||p_arrears_yn||' i:'||i||' p_months_factor:'||p_months_factor||
862 ' cf_date:'|| x_rent_inflow_tbl(i).cf_date||' p_recurrence_date:'||
863 p_recurrence_date||' p_arrears_pay_dates_option:'||p_arrears_pay_dates_option);
864
865
866
867 END IF;
868
869 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
870 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
871 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
872 RAISE OKL_API.G_EXCEPTION_ERROR;
873 END IF;
874
875 x_rent_inflow_tbl(i).cf_number := i; -- TBD
876 x_rent_inflow_tbl(i).cf_arrears := p_arrears_yn;
877 x_rent_inflow_tbl(i).cf_stub := 'N';
878 x_rent_inflow_tbl(i).cf_purpose := p_purpose_code;
879 x_rent_inflow_tbl(i).cf_dpp := p_dpp;
880 x_rent_inflow_tbl(i).cf_ppy := p_ppy;
881 x_rent_inflow_tbl(i).kleId := p_kle_id;
882
883 x_rent_inflow_tbl(i).cf_days := OKL_PRICING_UTILS_PVT.get_day_count(
884 p_start_date => p_contract_start_date,
885 p_days_in_month => p_day_convention_month,
886 p_days_in_year => p_day_convention_year,
887 p_end_date => x_rent_inflow_tbl(i).cf_date,
888 p_arrears => x_rent_inflow_tbl(i).cf_arrears,
889 x_return_status => l_return_status);
890
891 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
892 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
893 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
894 RAISE OKL_API.G_EXCEPTION_ERROR;
895 END IF;
896
897
898 END LOOP;
899 END IF;
900
901 IF l_stat_level >= l_debug_level THEN
902 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
903 'x_rent_inflow_tbl.count :'||TO_CHAR(x_rent_inflow_tbl.count));
904 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements','End(-)');
905 END IF;
906
907
908
909 EXCEPTION
910 ------------------------------------------------------------
911 -- Exception handling
912 ------------------------------------------------------------
913
914
915 WHEN OKL_API.G_EXCEPTION_ERROR THEN
916
917 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
918 p_api_name => l_api_name,
919 p_pkg_name => G_PKG_NAME,
920 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
921 x_msg_count => x_msg_count,
922 x_msg_data => x_msg_data,
923 p_api_type => '_PVT');
924
925 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
926
927 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
928 p_api_name => l_api_name,
929 p_pkg_name => G_PKG_NAME,
930 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 p_api_type => '_PVT');
934
935
936 WHEN OTHERS THEN
937
938 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
939 p_api_name => l_api_name,
940 p_pkg_name => G_PKG_NAME,
941 p_exc_name => 'OTHERS',
942 x_msg_count => x_msg_count,
943 x_msg_data => x_msg_data,
944 p_api_type => '_PVT');
945
946 END generate_stream_elements;
947
948
949
950 -- Start of comments
951 -- API name :
952 -- Pre-reqs : None
953 -- Function :
954 -- Parameters :
955 -- Version : 1.0
956 -- History : Durga Janaswamy created
957 -- End of comments
958
959
960 PROCEDURE compute_iir (p_khr_id IN NUMBER,
961 p_cash_in_flows_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
962 p_cash_out_flows IN NUMBER,
963 p_initial_iir IN NUMBER,
964 p_precision IN NUMBER,
965 x_iir OUT NOCOPY NUMBER,
966 x_return_status OUT NOCOPY VARCHAR2,
967 x_msg_count OUT NOCOPY NUMBER,
968 x_msg_data OUT NOCOPY VARCHAR2
969 )
970 IS
971 -----------------------------------------------------------------
972 -- Declare Process Variable
973 -----------------------------------------------------------------
974 l_api_version CONSTANT NUMBER := 1;
975 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_IIR';
976 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
977
978
979 ------------------------------------------------------------
980 -- Declare records: Extension Headers, Extension Lines
981 ------------------------------------------------------------
982
983 a BINARY_INTEGER := 0;
984 i BINARY_INTEGER := 0;
985
986 l_iir NUMBER := NVL(p_initial_iir, 0);
987
988 l_npv NUMBER;
989
990 l_prev_npv NUMBER;
991 l_prev_npv_sign NUMBER;
992
993 l_crossed_zero VARCHAR2(1) := 'N';
994
995 -- l_increment NUMBER := 1.1;
996 l_increment NUMBER := 0.11;
997 l_abs_incr NUMBER;
998 l_prev_incr_sign NUMBER;
999
1000 l_prev_iir NUMBER := 0;
1001 l_positive_npv_iir NUMBER := 0;
1002 l_negative_npv_iir NUMBER := 0;
1003 l_positive_npv NUMBER := 0;
1004 l_negative_npv NUMBER := 0;
1005
1006 l_iir_decided VARCHAR2(1) := 'F';
1007
1008
1009 l_initial_incr NUMBER;
1010
1011 l_cf_dpp NUMBER;
1012 l_cf_ppy NUMBER;
1013 l_cf_amount NUMBER;
1014 l_cf_date DATE;
1015 l_cf_arrear VARCHAR2(1);
1016 l_days_in_future NUMBER;
1017 l_periods NUMBER;
1018
1019
1020 BEGIN
1021
1022 x_return_status := OKL_API.START_ACTIVITY(
1023 p_api_name => l_api_name,
1024 p_pkg_name => g_pkg_name,
1025 p_init_msg_list => 'T',
1026 l_api_version => l_api_version,
1027 p_api_version => l_api_version,
1028 p_api_type => '_PVT',
1029 x_return_status => x_return_status);
1030
1031 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1032 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1033 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1034 RAISE OKL_API.G_EXCEPTION_ERROR;
1035 END IF;
1036
1037 IF l_stat_level >= l_debug_level THEN
1038 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','Begin(+)');
1039 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','Initial iir estimated ' || TO_CHAR(l_iir));
1040 END IF;
1041
1042
1043 l_initial_incr := nvl(l_increment, 0);
1044
1045 LOOP -- first
1046 i := i + 1;
1047 l_npv := -(p_cash_out_flows);
1048
1049 IF l_stat_level >= l_debug_level THEN
1050 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','ITERATION # '||i||' IIR Guess '||l_iir||' starting l_npv'||l_npv);
1051 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','p_cash_in_flows_tbl.count ' || TO_CHAR(p_cash_in_flows_tbl.count));
1052 END IF;
1053
1054 IF p_cash_in_flows_tbl.count > 0 THEN
1055
1056 --DEBUG
1057 a :=0;
1058
1059 FOR j in p_cash_in_flows_tbl.FIRST .. p_cash_in_flows_tbl.LAST LOOP
1060 --DEBUG
1061 a := a+1;
1062
1063 l_cf_dpp := p_cash_in_flows_tbl(j).cf_dpp;
1064 l_cf_ppy := p_cash_in_flows_tbl(j).cf_ppy;
1065 l_cf_amount := p_cash_in_flows_tbl(j).cf_amount;
1066 l_cf_date := p_cash_in_flows_tbl(j).cf_date;
1067 l_days_in_future := p_cash_in_flows_tbl(j).cf_days;
1068 l_periods := l_days_in_future / l_cf_dpp;
1069
1070 IF ( ((l_periods < 1) AND (l_iir/l_cf_ppy <= -1) ) OR
1071 i >= 1000
1072 )
1073 THEN
1074 -- Return the procedure if loop is crossing thousand iterations also
1075 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1076 p_msg_name => 'OKL_IRR_ZERO_DIV');
1077
1078 RAISE OKL_API.G_EXCEPTION_ERROR;
1079
1080 END IF;
1081
1082 l_npv := l_npv + (l_cf_amount / POWER((1 + l_iir/l_cf_ppy), l_periods));
1083
1084
1085 IF l_stat_level >= l_debug_level THEN
1086 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1087 TO_CHAR(a, '99')||' '||TO_CHAR(l_cf_date, 'DD-MON-YYYY')||' '||TO_CHAR(l_days_in_future, '9999')||' '||TO_CHAR(l_periods, '99.999')||' '||TO_CHAR(l_cf_amount, '999.999')||
1088 ' '||TO_CHAR((l_cf_amount / POWER((1 + l_iir/l_cf_ppy), l_periods)), '999.990'));
1089 END IF;
1090
1091 END LOOP;
1092
1093 END IF;
1094
1095 IF l_stat_level >= l_debug_level THEN
1096 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','NPV ' || TO_CHAR(L_NPV));
1097 END IF;
1098
1099 IF ROUND(l_npv, p_precision+1) = 0 THEN
1100 x_iir := l_iir; -- LLA API multiples by 100 before updating KHR implicit_interest_rate column
1101 IF l_stat_level >= l_debug_level THEN
1102 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','final l_iir : ' || TO_CHAR(l_iir));
1103 END IF;
1104 RETURN;
1105 END IF;
1106
1107 IF i > 1 AND SIGN(l_npv) <> SIGN(l_prev_npv) AND l_crossed_zero = 'N' THEN
1108
1109 l_crossed_zero := 'Y';
1110
1111 IF ( sign( l_npv) = 1 ) then
1112 l_positive_npv := l_npv;
1113 l_negative_npv := l_prev_npv;
1114 l_positive_npv_iir := l_iir;
1115 l_negative_npv_iir := l_prev_iir;
1116 ELSE
1117 l_positive_npv := l_prev_npv;
1118 l_negative_npv := l_npv;
1119 l_positive_npv_iir := l_prev_iir;
1120 l_negative_npv_iir := l_iir;
1121 END IF;
1122
1123 END IF;
1124
1125 IF( sign(l_npv) = 1) THEN
1126 l_positive_npv := l_npv;
1127 l_positive_npv_iir := l_iir;
1128 ELSE
1129 l_negative_npv := l_npv;
1130 l_negative_npv_iir := l_iir;
1131 END IF;
1132
1133
1134 IF l_crossed_zero = 'Y' THEN
1135 IF i > 1 then
1136 l_abs_incr := abs(( l_positive_npv_iir - l_negative_npv_iir ) /
1137 ( l_positive_npv - l_negative_npv ) * l_positive_npv);
1138
1139 IF ( l_positive_npv_iir < l_negative_npv_iir ) THEN
1140 l_iir := l_positive_npv_iir + l_abs_incr;
1141 ELSE
1142 l_iir := l_positive_npv_iir - l_abs_incr;
1143
1144 END IF;
1145 l_iir_decided := 'T';
1146
1147 ELSE
1148 l_abs_incr := ABS(l_increment) / 2;
1149 END IF;
1150
1151 ELSE
1152
1153 l_abs_incr := ABS(l_increment);
1154
1155 END IF;
1156
1157 IF i > 1 THEN
1158
1159 IF SIGN(l_npv) <> l_prev_npv_sign THEN
1160
1161 IF l_prev_incr_sign = 1 THEN
1162
1163 l_increment := - l_abs_incr;
1164
1165 ELSE
1166
1167 l_increment := l_abs_incr;
1168
1169 END IF;
1170
1171 ELSE
1172
1173 IF l_prev_incr_sign = 1 THEN
1174
1175 l_increment := l_abs_incr;
1176
1177 ELSE
1178
1179 l_increment := - l_abs_incr;
1180
1181 END IF;
1182
1183 END IF;
1184
1185 ELSE -- i = 1
1186
1187 IF SIGN(l_npv) = -1 THEN
1188
1189 l_increment := - l_increment;
1190
1191 END IF;
1192
1193 END IF;
1194
1195 l_prev_iir := l_iir;
1196
1197 IF l_iir_decided = 'F'
1198 THEN
1199 l_iir := l_iir + l_increment;
1200 ELSE
1201 l_iir_decided := 'F';
1202 END IF;
1203
1204 IF l_stat_level >= l_debug_level THEN
1205 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1206 i || '-Loop l_npv ' || to_char(l_npv) );
1207 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1208 i || '-Loop l_increment ' || to_char(l_increment) );
1209 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1210 i || '-Loop iir ' || to_char(l_iir) );
1211 END IF;
1212
1213
1214 l_prev_incr_sign := SIGN(l_increment);
1215 l_prev_npv_sign := SIGN(l_npv);
1216 l_prev_npv := l_npv;
1217
1218
1219 END LOOP; -- first
1220
1221 IF l_stat_level >= l_debug_level THEN
1222 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','End(-)');
1223 END IF;
1224
1225
1226 EXCEPTION
1227 ------------------------------------------------------------
1228 -- Exception handling
1229 ------------------------------------------------------------
1230
1231 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1232
1233 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1234 p_api_name => l_api_name,
1235 p_pkg_name => G_PKG_NAME,
1236 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1237 x_msg_count => x_msg_count,
1238 x_msg_data => x_msg_data,
1239 p_api_type => '_PVT');
1240
1241 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1242
1243 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1244 p_api_name => l_api_name,
1245 p_pkg_name => G_PKG_NAME,
1246 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1247 x_msg_count => x_msg_count,
1248 x_msg_data => x_msg_data,
1249 p_api_type => '_PVT');
1250
1251 WHEN OTHERS THEN
1252
1253 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1254 p_api_name => l_api_name,
1255 p_pkg_name => G_PKG_NAME,
1256 p_exc_name => 'OTHERS',
1257 x_msg_count => x_msg_count,
1258 x_msg_data => x_msg_data,
1259 p_api_type => '_PVT');
1260 END COMPUTE_IIR;
1261
1262
1263 END OKL_GENERATE_PV_RENT_PVT;