[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 2008/05/22 23:40:31 snizam noship $ */
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 OKL_GENERATE_PV_RENT_PVT.compute_iir ( p_khr_id => p_khr_id,
363 p_cash_in_flows_tbl => l_total_cash_inflow_tbl,
364 p_cash_out_flows => l_time_zero_cost,
365 p_initial_iir => l_guess_iir,
366 p_precision => l_precision,
367 x_iir => l_iir,
368 x_return_status => x_return_status,
369 x_msg_count => x_msg_count,
370 x_msg_data => x_msg_data
371 );
372
373 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
374 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
375 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
376 RAISE OKL_API.G_EXCEPTION_ERROR;
377 END IF;
378
379 IF l_stat_level >= l_debug_level THEN
380 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','l_iir: '||TO_CHAR(l_iir));
381 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','calculating the PV rent');
382 END IF;
383
384 -- calculating the PV rent
385
386 IF l_residual_value > 0 THEN
387 IF l_stat_level >= l_debug_level THEN
388 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent','calculating the PV of residual value');
389 END IF;
390
391 l_period_residual_value := l_total_cash_inflow_tbl(lx_total_rent_inflow_tbl_count + 1).cf_days/l_dpp;
392
393 l_pv_residual_value := l_residual_value / POWER( 1 + (l_iir/(l_ppy)), l_period_residual_value);
394
395 IF l_stat_level >= l_debug_level THEN
396 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_total_pv_rent',
397 'PV of residual value: '||TO_CHAR(l_pv_residual_value));
398 END IF;
399 END IF;
400
401 l_pv_rent := l_time_zero_cost - nvl(l_pv_residual_value,0);
402
403
404
405 x_total_pv_rent := ROUND(l_pv_rent,l_precision);
406
407 IF l_stat_level >= l_debug_level THEN
408 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));
409 END IF;
410
411
412 IF (l_proc_level >= l_debug_level) THEN
413 FND_LOG.STRING(l_proc_level,'OKL_GENERATE_PV_RENT_PVT','End(-)');
414 END IF;
415
416 okl_api.end_activity(x_msg_count => x_msg_count
417 ,x_msg_data => x_msg_data);
418
419
420 EXCEPTION
421 ------------------------------------------------------------
422 -- Exception handling
423 ------------------------------------------------------------
424
425 WHEN OKL_API.G_EXCEPTION_ERROR THEN
426
427 IF c_asset_id_csr%ISOPEN THEN
428 CLOSE c_asset_id_csr;
429 END IF;
430
431 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
432 p_api_name => l_api_name,
433 p_pkg_name => G_PKG_NAME,
434 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
435 x_msg_count => x_msg_count,
436 x_msg_data => x_msg_data,
437 p_api_type => '_PVT');
438
439 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
440
441 IF c_asset_id_csr%ISOPEN THEN
442 CLOSE c_asset_id_csr;
443 END IF;
444
445 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
446 p_api_name => l_api_name,
447 p_pkg_name => G_PKG_NAME,
448 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data,
451 p_api_type => '_PVT');
452
453 WHEN OTHERS THEN
454
455 IF c_asset_id_csr%ISOPEN THEN
456 CLOSE c_asset_id_csr;
457 END IF;
458
459 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
460 p_api_name => l_api_name,
461 p_pkg_name => G_PKG_NAME,
462 p_exc_name => 'OTHERS',
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data,
465 p_api_type => '_PVT');
466
467 END generate_total_pv_rent;
468
469
470 -- Start of comments
471 -- API name :
472 -- Pre-reqs : None
473 -- Function :
474 -- Parameters :
475 -- Version : 1.0
476 -- History : Durga Janaswamy created
477 -- End of comments
478
479
480 PROCEDURE generate_asset_rent
481 (p_api_version IN NUMBER
482 ,p_init_msg_list IN VARCHAR2
483 ,p_khr_id IN NUMBER
484 ,p_kle_id IN NUMBER
485 ,p_contract_start_date IN DATE
486 ,p_day_convention_month IN VARCHAR2
487 ,p_day_convention_year IN VARCHAR2
488 ,p_arrears_pay_dates_option IN VARCHAR2
489 ,p_total_rent_inflow_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl
490 ,x_total_rent_inflow_tbl OUT NOCOPY OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl
491 ,x_dpp OUT NOCOPY NUMBER
492 ,x_ppy OUT NOCOPY NUMBER
493 ,x_return_status OUT NOCOPY VARCHAR2
494 ,x_msg_count OUT NOCOPY NUMBER
495 ,x_msg_data OUT NOCOPY VARCHAR2
496 )
497 IS
498
499 -----------------------------------------------------------------
500 -- Declare Process Variable
501 -----------------------------------------------------------------
502 l_api_version CONSTANT NUMBER := 1;
503 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_ASSET_PV_RENT';
504 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
505
506
507 ------------------------------------------------------------
508 -- Declare records: Extension Headers, Extension Lines
509 ------------------------------------------------------------
510
511 CURSOR c_payment_details_csr (p_khr_id NUMBER, p_kle_id NUMBER) IS
512 SELECT rgp.cle_id cle_id, sty.stream_type_purpose, sty.id sty_id,
513 FND_DATE.canonical_to_date(sll.rule_information2) start_date,
514 TO_NUMBER(SLL.rule_information3) periods,
515 sll.object1_id1 frequency,
516 sll.rule_information5 structure,
517 NVL(sll.rule_information10, 'N') arrears_yn,
518 DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) days_per_period,
519 DECODE(sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
520 DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
521 FND_NUMBER.canonical_to_number(sll.rule_information6) amount,
522 TO_NUMBER(sll.rule_information7) stub_days,
523 TO_NUMBER(sll.rule_information8) stub_amount
524 FROM okc_rules_b sll,
525 okc_rules_b slh,
526 okc_rule_groups_b rgp,
527 okl_strm_type_b sty
528 WHERE rgp.dnz_chr_id = p_khr_id
529 AND rgp.cle_id = p_kle_id
530 AND rgp.rgd_code= 'LALEVL'
531 AND rgp.id = slh.rgp_id
532 AND slh.rule_information_category = 'LASLH'
533 AND TO_NUMBER(slh.object1_id1) = sty.id
534 AND sty.version = '1.0'
535 AND (sty.stream_type_purpose = 'RENT'
536 or sty.stream_type_purpose = 'DOWN_PAYMENT')
537 AND TO_CHAR(slh.id) = sll.object2_id1
538 AND sll.rule_information_category = 'LASLL'
539 ORDER BY fnd_date.canonical_to_date(sll.rule_information2);
540
541 l_payment_details_csr_rec c_payment_details_csr%ROWTYPE;
542
543 l_cash_inflow_tbl cash_flow_tbl;
544 l_rent_inflow_tbl cash_flow_tbl;
545 lx_rent_inflow_tbl cash_flow_tbl;
546 lx_rent_inflow_tbl_count NUMBER := 0;
547
548 l_recurrence_date DATE := NULL;
549 l_old_cle_id NUMBER;
550 l_old_sty_id NUMBER;
551
552 l_dpp NUMBER;
553
554 l_ppy NUMBER;
555
556 BEGIN
557
558 x_return_status := OKL_API.G_RET_STS_SUCCESS;
559
560 IF l_stat_level >= l_debug_level THEN
561 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent','Begin(+)');
562 END IF;
563
564 l_return_status := OKL_API.START_ACTIVITY(
565 p_api_name => l_api_name,
566 p_pkg_name => g_pkg_name,
567 p_init_msg_list => p_init_msg_list,
568 l_api_version => l_api_version,
569 p_api_version => p_api_version,
570 p_api_type => '_PVT',
571 x_return_status => l_return_status);
572
573 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
574 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
575 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
576 RAISE OKL_API.G_EXCEPTION_ERROR;
577 END IF;
578
579
580
581 l_rent_inflow_tbl.delete;
582 lx_rent_inflow_tbl.delete;
583 l_rent_inflow_tbl := p_total_rent_inflow_tbl;
584
585 FOR l_payment_details_csr_rec IN c_payment_details_csr (p_khr_id, p_kle_id)
586 LOOP
587
588 IF l_payment_details_csr_rec.start_date IS NULL
589 THEN
590 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
591 p_msg_name => 'OKL_NO_SLL_SDATE');
592 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
593 END IF;
594
595 IF((l_payment_details_csr_rec.periods IS NULL) AND (l_payment_details_csr_rec.stub_days IS NOT NULL)) THEN
596 --Set the recurrence date to null for stub payment
597 l_recurrence_date := NULL;
598 ELSIF(l_recurrence_date IS NULL
599 OR l_old_cle_id <> l_payment_details_csr_rec.cle_id
600 OR l_old_sty_id <> l_payment_details_csr_rec.sty_id) THEN
601 --Set the recurrence date as periodic payment level start date
602 l_recurrence_date := l_payment_details_csr_rec.start_date;
603 END IF;
604 l_old_cle_id := l_payment_details_csr_rec.cle_id;
605 l_old_sty_id := l_payment_details_csr_rec.sty_id;
606
607
608 OKL_GENERATE_PV_RENT_PVT.generate_stream_elements(
609 p_start_date => l_payment_details_csr_rec.start_date,
610 p_periods => l_payment_details_csr_rec.periods,
611 p_frequency => l_payment_details_csr_rec.frequency,
612 p_structure => l_payment_details_csr_rec.structure,
613 p_arrears_yn => l_payment_details_csr_rec.arrears_yn,
614 p_amount => l_payment_details_csr_rec.amount,
615 p_stub_days => l_payment_details_csr_rec.stub_days,
616 p_stub_amount => l_payment_details_csr_rec.stub_amount,
617 p_khr_id => p_khr_id,
618 p_kle_id => p_kle_id,
619 p_purpose_code => l_payment_details_csr_rec.stream_type_purpose,
620 p_recurrence_date => l_recurrence_date,
621 p_dpp => l_payment_details_csr_rec.days_per_period,
622 p_ppy => l_payment_details_csr_rec.periods_per_year,
623 p_months_factor => l_payment_details_csr_rec.months_per_period,
624 p_contract_start_date => p_contract_start_date,
625 p_day_convention_month => p_day_convention_month,
626 p_day_convention_year => p_day_convention_year,
627 p_arrears_pay_dates_option => p_arrears_pay_dates_option,
628 p_rent_inflow_tbl => l_rent_inflow_tbl,
629 x_rent_inflow_tbl => lx_rent_inflow_tbl,
630 x_return_status => l_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data
633 );
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 l_rent_inflow_tbl := lx_rent_inflow_tbl;
642
643 l_dpp := l_payment_details_csr_rec.days_per_period;
644 l_ppy := l_payment_details_csr_rec.periods_per_year;
645
646 END LOOP;
647
648 x_total_rent_inflow_tbl := lx_rent_inflow_tbl;
649
650 x_dpp := l_dpp;
651
652 x_ppy := l_ppy;
653
654
655 IF l_stat_level >= l_debug_level THEN
656 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent',
657 'x_total_rent_inflow_tbl.count :'||TO_CHAR(x_total_rent_inflow_tbl.count));
658 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_asset_rent','End(-)');
659 END IF;
660
661
662 okl_api.end_activity(x_msg_count => x_msg_count
663 ,x_msg_data => x_msg_data);
664
665
666
667 EXCEPTION
668 ------------------------------------------------------------
669 -- Exception handling
670 ------------------------------------------------------------
671
672 WHEN OKL_API.G_EXCEPTION_ERROR THEN
673
674 IF c_payment_details_csr%ISOPEN THEN
675 CLOSE c_payment_details_csr;
676 END IF;
677
678 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
679 p_api_name => l_api_name,
680 p_pkg_name => G_PKG_NAME,
681 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
682 x_msg_count => x_msg_count,
683 x_msg_data => x_msg_data,
684 p_api_type => '_PVT');
685
686 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
687
688 IF c_payment_details_csr%ISOPEN THEN
689 CLOSE c_payment_details_csr;
690 END IF;
691
692 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
693 p_api_name => l_api_name,
694 p_pkg_name => G_PKG_NAME,
695 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
696 x_msg_count => x_msg_count,
697 x_msg_data => x_msg_data,
698 p_api_type => '_PVT');
699
700 WHEN OTHERS THEN
701
702 IF c_payment_details_csr%ISOPEN THEN
703 CLOSE c_payment_details_csr;
704 END IF;
705
706 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
707 p_api_name => l_api_name,
708 p_pkg_name => G_PKG_NAME,
709 p_exc_name => 'OTHERS',
710 x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data,
712 p_api_type => '_PVT');
713
714 END generate_asset_rent;
715
716
717
718 -- Start of comments
719 -- API name :
720 -- Pre-reqs : None
721 -- Function :
722 -- Parameters :
723 -- Version : 1.0
724 -- History : Durga Janaswamy created
725 -- End of comments
726
727 PROCEDURE generate_stream_elements( p_start_date IN DATE,
728 p_periods IN NUMBER,
729 p_frequency IN VARCHAR2,
730 p_structure IN VARCHAR2,
731 p_arrears_yn IN VARCHAR2,
732 p_amount IN NUMBER,
733 p_stub_days IN NUMBER,
734 p_stub_amount IN NUMBER,
735 p_khr_id IN NUMBER,
736 p_kle_id IN NUMBER,
737 p_purpose_code IN VARCHAR2,
738 p_recurrence_date IN DATE,
739 p_dpp IN NUMBER,
740 p_ppy IN NUMBER,
741 p_months_factor IN NUMBER,
742 p_contract_start_date IN DATE,
743 p_day_convention_month IN VARCHAR2,
744 p_day_convention_year IN VARCHAR2,
745 p_arrears_pay_dates_option IN VARCHAR2,
746 p_rent_inflow_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
747 x_rent_inflow_tbl OUT NOCOPY OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2
751 ) IS
752
753
754 -----------------------------------------------------------------
755 -- Declare Process Variable
756 -----------------------------------------------------------------
757 l_api_version CONSTANT NUMBER := 1;
758 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_STREAM_ELEMENTS';
759 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
760
761
762 ------------------------------------------------------------
763 -- Declare records: Extension Headers, Extension Lines
764 ------------------------------------------------------------
765
766 l_rent_inflow_tbl cash_flow_tbl;
767
768
769 l_element_count NUMBER;
770 l_amount NUMBER;
771
772 i BINARY_INTEGER := 0;
773 l_rent_inflow_tbl_count NUMBER := 0;
774
775 BEGIN
776
777
778 IF l_stat_level >= l_debug_level THEN
779 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements','Begin(+)');
780 END IF;
781
782 x_rent_inflow_tbl := p_rent_inflow_tbl;
783
784 l_rent_inflow_tbl_count := nvl(p_rent_inflow_tbl.count,0);
785
786 IF ( p_amount IS NULL )
787 THEN
788 l_amount := NULL;
789 ELSE
790 l_amount := p_amount;
791 END IF;
792
793
794 IF ( p_periods IS NULL ) AND ( p_stub_days IS NOT NULL )
795 THEN
796 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_amount := p_stub_amount;
797 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_number := l_rent_inflow_tbl_count + 1; -- TBD
798
799 IF p_arrears_yn = 'Y' THEN
800 IF p_arrears_pay_dates_option = 'FIRST_DAY_OF_NEXT_PERIOD' THEN
801 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date + p_stub_days;
802 ELSE
803 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date + p_stub_days - 1;
804 END IF;
805 ELSE
806 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date := p_start_date;
807 END IF;
808
809 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_arrears := p_arrears_yn;
810 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_stub := 'Y';
811 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_purpose := p_purpose_code;
812 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_dpp := p_dpp;
813 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_ppy := p_ppy;
814 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).kleId := p_kle_id;
815
816 x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_days := OKL_PRICING_UTILS_PVT.get_day_count(
817 p_start_date => p_contract_start_date,
818 p_days_in_month => p_day_convention_month,
819 p_days_in_year => p_day_convention_year,
820 p_end_date => x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_date,
821 p_arrears => x_rent_inflow_tbl(l_rent_inflow_tbl_count + 1).cf_arrears,
822 x_return_status => l_return_status);
823 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
824 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
825 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_ERROR;
827 END IF;
828
829 ELSE
830
831
832 l_element_count := p_periods;
833 IF l_stat_level >= l_debug_level THEN
834 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
835 'creating elements:'||TO_CHAR(l_element_count));
836 END IF;
837
838 FOR i IN (l_rent_inflow_tbl_count+1) .. (l_rent_inflow_tbl_count+l_element_count)
839 LOOP
840 x_rent_inflow_tbl(i).cf_amount := l_amount;
841
842
843 OKL_STREAM_GENERATOR_PVT.get_sel_date(
844 p_start_date => p_start_date,
845 p_advance_or_arrears => p_arrears_yn,
846 p_periods_after => i - l_rent_inflow_tbl_count,
847 p_months_per_period => p_months_factor,
848 x_date => x_rent_inflow_tbl(i).cf_date,
849 x_return_status => l_return_status,
850 p_recurrence_date => p_recurrence_date,
851 p_arrears_pay_dates_option => p_arrears_pay_dates_option);
852
853 IF l_stat_level >= l_debug_level THEN
854 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
855 '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||
856 ' cf_date:'|| x_rent_inflow_tbl(i).cf_date||' p_recurrence_date:'||
857 p_recurrence_date||' p_arrears_pay_dates_option:'||p_arrears_pay_dates_option);
858
859
860
861 END IF;
862
863 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
864 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
865 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
866 RAISE OKL_API.G_EXCEPTION_ERROR;
867 END IF;
868
869 x_rent_inflow_tbl(i).cf_number := i; -- TBD
870 x_rent_inflow_tbl(i).cf_arrears := p_arrears_yn;
871 x_rent_inflow_tbl(i).cf_stub := 'N';
872 x_rent_inflow_tbl(i).cf_purpose := p_purpose_code;
873 x_rent_inflow_tbl(i).cf_dpp := p_dpp;
874 x_rent_inflow_tbl(i).cf_ppy := p_ppy;
875 x_rent_inflow_tbl(i).kleId := p_kle_id;
876
877 x_rent_inflow_tbl(i).cf_days := OKL_PRICING_UTILS_PVT.get_day_count(
878 p_start_date => p_contract_start_date,
879 p_days_in_month => p_day_convention_month,
880 p_days_in_year => p_day_convention_year,
881 p_end_date => x_rent_inflow_tbl(i).cf_date,
882 p_arrears => x_rent_inflow_tbl(i).cf_arrears,
883 x_return_status => l_return_status);
884
885 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
886 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
887 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
888 RAISE OKL_API.G_EXCEPTION_ERROR;
889 END IF;
890
891
892 END LOOP;
893 END IF;
894
895 IF l_stat_level >= l_debug_level THEN
896 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements',
897 'x_rent_inflow_tbl.count :'||TO_CHAR(x_rent_inflow_tbl.count));
898 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.generate_stream_elements','End(-)');
899 END IF;
900
901
902
903 EXCEPTION
904 ------------------------------------------------------------
905 -- Exception handling
906 ------------------------------------------------------------
907
908
909 WHEN OKL_API.G_EXCEPTION_ERROR THEN
910
911 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
912 p_api_name => l_api_name,
913 p_pkg_name => G_PKG_NAME,
914 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
915 x_msg_count => x_msg_count,
916 x_msg_data => x_msg_data,
917 p_api_type => '_PVT');
918
919 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
920
921 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
922 p_api_name => l_api_name,
923 p_pkg_name => G_PKG_NAME,
924 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data,
927 p_api_type => '_PVT');
928
929
930 WHEN OTHERS THEN
931
932 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
933 p_api_name => l_api_name,
934 p_pkg_name => G_PKG_NAME,
935 p_exc_name => 'OTHERS',
936 x_msg_count => x_msg_count,
937 x_msg_data => x_msg_data,
938 p_api_type => '_PVT');
939
940 END generate_stream_elements;
941
942
943
944 -- Start of comments
945 -- API name :
946 -- Pre-reqs : None
947 -- Function :
948 -- Parameters :
949 -- Version : 1.0
950 -- History : Durga Janaswamy created
951 -- End of comments
952
953
954 PROCEDURE compute_iir (p_khr_id IN NUMBER,
955 p_cash_in_flows_tbl IN OKL_GENERATE_PV_RENT_PVT.cash_flow_tbl,
956 p_cash_out_flows IN NUMBER,
957 p_initial_iir IN NUMBER,
958 p_precision IN NUMBER,
959 x_iir OUT NOCOPY NUMBER,
960 x_return_status OUT NOCOPY VARCHAR2,
961 x_msg_count OUT NOCOPY NUMBER,
962 x_msg_data OUT NOCOPY VARCHAR2
963 )
964 IS
965 -----------------------------------------------------------------
966 -- Declare Process Variable
967 -----------------------------------------------------------------
968 l_api_version CONSTANT NUMBER := 1;
969 l_api_name CONSTANT VARCHAR2(30) := 'COMPUTE_IIR';
970 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
971
972
973 ------------------------------------------------------------
974 -- Declare records: Extension Headers, Extension Lines
975 ------------------------------------------------------------
976
977 a BINARY_INTEGER := 0;
978 i BINARY_INTEGER := 0;
979
980 l_iir NUMBER := NVL(p_initial_iir, 0);
981
982 l_npv NUMBER;
983
984 l_prev_npv NUMBER;
985 l_prev_npv_sign NUMBER;
986
987 l_crossed_zero VARCHAR2(1) := 'N';
988
989 -- l_increment NUMBER := 1.1;
990 l_increment NUMBER := 0.11;
991 l_abs_incr NUMBER;
992 l_prev_incr_sign NUMBER;
993
994 l_prev_iir NUMBER := 0;
995 l_positive_npv_iir NUMBER := 0;
996 l_negative_npv_iir NUMBER := 0;
997 l_positive_npv NUMBER := 0;
998 l_negative_npv NUMBER := 0;
999
1000 l_iir_decided VARCHAR2(1) := 'F';
1001
1002
1003 l_initial_incr NUMBER;
1004
1005 l_cf_dpp NUMBER;
1006 l_cf_ppy NUMBER;
1007 l_cf_amount NUMBER;
1008 l_cf_date DATE;
1009 l_cf_arrear VARCHAR2(1);
1010 l_days_in_future NUMBER;
1011 l_periods NUMBER;
1012
1013
1014 BEGIN
1015
1016 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1017
1018 IF l_stat_level >= l_debug_level THEN
1019 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','Begin(+)');
1020 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','Initial iir estimated ' || TO_CHAR(l_iir));
1021 END IF;
1022
1023
1024 l_initial_incr := nvl(l_increment, 0);
1025
1026 LOOP -- first
1027 i := i + 1;
1028 l_npv := -(p_cash_out_flows);
1029
1030 IF l_stat_level >= l_debug_level THEN
1031 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','ITERATION # '||i||' IIR Guess '||l_iir||' starting l_npv'||l_npv);
1032 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));
1033 END IF;
1034
1035 IF p_cash_in_flows_tbl.count > 0 THEN
1036
1037 --DEBUG
1038 a :=0;
1039
1040 FOR j in p_cash_in_flows_tbl.FIRST .. p_cash_in_flows_tbl.LAST LOOP
1041 --DEBUG
1042 a := a+1;
1043
1044 l_cf_dpp := p_cash_in_flows_tbl(j).cf_dpp;
1045 l_cf_ppy := p_cash_in_flows_tbl(j).cf_ppy;
1046 l_cf_amount := p_cash_in_flows_tbl(j).cf_amount;
1047 l_cf_date := p_cash_in_flows_tbl(j).cf_date;
1048 l_days_in_future := p_cash_in_flows_tbl(j).cf_days;
1049 l_periods := l_days_in_future / l_cf_dpp;
1050
1051 IF (l_periods < 1) AND (l_iir/l_cf_ppy <= -1) THEN
1052
1053 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1054 p_msg_name => 'OKL_IRR_ZERO_DIV');
1055
1056 RAISE OKL_API.G_EXCEPTION_ERROR;
1057
1058 END IF;
1059
1060 l_npv := l_npv + (l_cf_amount / POWER((1 + l_iir/l_cf_ppy), l_periods));
1061
1062
1063 IF l_stat_level >= l_debug_level THEN
1064 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1065 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')||
1066 ' '||TO_CHAR((l_cf_amount / POWER((1 + l_iir/l_cf_ppy), l_periods)), '999.990'));
1067 END IF;
1068
1069 END LOOP;
1070
1071 END IF;
1072
1073 IF l_stat_level >= l_debug_level THEN
1074 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','NPV ' || TO_CHAR(L_NPV));
1075 END IF;
1076
1077 IF ROUND(l_npv, p_precision+1) = 0 THEN
1078 x_iir := l_iir; -- LLA API multiples by 100 before updating KHR implicit_interest_rate column
1079 IF l_stat_level >= l_debug_level THEN
1080 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','final l_iir : ' || TO_CHAR(l_iir));
1081 END IF;
1082 RETURN;
1083 END IF;
1084
1085 IF i > 1 AND SIGN(l_npv) <> SIGN(l_prev_npv) AND l_crossed_zero = 'N' THEN
1086
1087 l_crossed_zero := 'Y';
1088
1089 IF ( sign( l_npv) = 1 ) then
1090 l_positive_npv := l_npv;
1091 l_negative_npv := l_prev_npv;
1092 l_positive_npv_iir := l_iir;
1093 l_negative_npv_iir := l_prev_iir;
1094 ELSE
1095 l_positive_npv := l_prev_npv;
1096 l_negative_npv := l_npv;
1097 l_positive_npv_iir := l_prev_iir;
1098 l_negative_npv_iir := l_iir;
1099 END IF;
1100
1101 END IF;
1102
1103 IF( sign(l_npv) = 1) THEN
1104 l_positive_npv := l_npv;
1105 l_positive_npv_iir := l_iir;
1106 ELSE
1107 l_negative_npv := l_npv;
1108 l_negative_npv_iir := l_iir;
1109 END IF;
1110
1111
1112 IF l_crossed_zero = 'Y' THEN
1113 IF i > 1 then
1114 l_abs_incr := abs(( l_positive_npv_iir - l_negative_npv_iir ) /
1115 ( l_positive_npv - l_negative_npv ) * l_positive_npv);
1116
1117 IF ( l_positive_npv_iir < l_negative_npv_iir ) THEN
1118 l_iir := l_positive_npv_iir + l_abs_incr;
1119 ELSE
1120 l_iir := l_positive_npv_iir - l_abs_incr;
1121
1122 END IF;
1123 l_iir_decided := 'T';
1124
1125 ELSE
1126 l_abs_incr := ABS(l_increment) / 2;
1127 END IF;
1128
1129 ELSE
1130
1131 l_abs_incr := ABS(l_increment);
1132
1133 END IF;
1134
1135 IF i > 1 THEN
1136
1137 IF SIGN(l_npv) <> l_prev_npv_sign THEN
1138
1139 IF l_prev_incr_sign = 1 THEN
1140
1141 l_increment := - l_abs_incr;
1142
1143 ELSE
1144
1145 l_increment := l_abs_incr;
1146
1147 END IF;
1148
1149 ELSE
1150
1151 IF l_prev_incr_sign = 1 THEN
1152
1153 l_increment := l_abs_incr;
1154
1155 ELSE
1156
1157 l_increment := - l_abs_incr;
1158
1159 END IF;
1160
1161 END IF;
1162
1163 ELSE -- i = 1
1164
1165 IF SIGN(l_npv) = -1 THEN
1166
1167 l_increment := - l_increment;
1168
1169 END IF;
1170
1171 END IF;
1172
1173 l_prev_iir := l_iir;
1174
1175 IF l_iir_decided = 'F'
1176 THEN
1177 l_iir := l_iir + l_increment;
1178 ELSE
1179 l_iir_decided := 'F';
1180 END IF;
1181
1182 IF l_stat_level >= l_debug_level THEN
1183 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1184 i || '-Loop l_npv ' || to_char(l_npv) );
1185 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1186 i || '-Loop l_increment ' || to_char(l_increment) );
1187 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir',
1188 i || '-Loop iir ' || to_char(l_iir) );
1189 END IF;
1190
1191
1192 l_prev_incr_sign := SIGN(l_increment);
1193 l_prev_npv_sign := SIGN(l_npv);
1194 l_prev_npv := l_npv;
1195
1196
1197 END LOOP; -- first
1198
1199 IF l_stat_level >= l_debug_level THEN
1200 fnd_log.STRING(l_stat_level,'OKL_GENERATE_PV_RENT_PVT.compute_iir','End(-)');
1201 END IF;
1202
1203
1204 EXCEPTION
1205 ------------------------------------------------------------
1206 -- Exception handling
1207 ------------------------------------------------------------
1208
1209 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1210
1211 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1212 p_api_name => l_api_name,
1213 p_pkg_name => G_PKG_NAME,
1214 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1215 x_msg_count => x_msg_count,
1216 x_msg_data => x_msg_data,
1217 p_api_type => '_PVT');
1218
1219 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1220
1221 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1222 p_api_name => l_api_name,
1223 p_pkg_name => G_PKG_NAME,
1224 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1225 x_msg_count => x_msg_count,
1226 x_msg_data => x_msg_data,
1227 p_api_type => '_PVT');
1228
1229 WHEN OTHERS THEN
1230
1231 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
1232 p_api_name => l_api_name,
1233 p_pkg_name => G_PKG_NAME,
1234 p_exc_name => 'OTHERS',
1235 x_msg_count => x_msg_count,
1236 x_msg_data => x_msg_data,
1237 p_api_type => '_PVT');
1238 END COMPUTE_IIR;
1239
1240
1241 END OKL_GENERATE_PV_RENT_PVT;