[Home] [Help]
PACKAGE BODY: APPS.OKL_LRF_GENERATE_PVT
Source
1 PACKAGE BODY OKL_LRF_GENERATE_PVT AS
2 /* $Header: OKLRRFGB.pls 120.6 2006/08/09 14:18:26 pagarg noship $ */
3
4 /**
5 This procedure caculates the lease rate factor for a given term-value pair.
6 RSDPV = ((residual_value/100)/((1+((rate/100)*day_convention))**term));
7 DFi = (1+(rate/100*day_convention))**i;
8 LRF = (1-RSDPV)/(SUMOFALL[1/DFi]) i varies from first payment to last payment
9 **/
10
11 PROCEDURE calculate_lrf(p_arrears IN number --=1=yes/0=no;
12 ,p_rate IN number --in %
13 ,p_day_convention IN number --30/360
14 ,p_deffered_payments IN number
15 ,p_advance_payments IN number
16 ,p_term IN number
17 ,p_value IN number -- in %(residual value)
18 ,p_frequency IN number -- monthly=1 quarterly=3 semi annual=6 annual=12
19 ,p_lrf OUT NOCOPY number) IS
20 l_rsdpv number;
21 l_error number;
22 l_df number;
23 l_df_last_added number;
24 l_pv number;
25 i number;
26 l_arrear number;
27 l_rate number;
28 l_dc number;
29 l_def number;
30 l_def_pmts number;
31 l_adv number;
32 l_adv_loop number;
33 l_term number;
34 l_value number;
35 l_freq number;
36 l_lrf number;
37 l_sum_one_by_df number;
38
39 BEGIN
40 l_arrear := p_arrears;
41 l_rate := p_rate;
42 l_dc := p_day_convention;
43 l_def := p_deffered_payments;
44 l_def_pmts := p_deffered_payments;
45 l_adv := p_advance_payments;
46 l_adv_loop := p_advance_payments;
47 l_term := p_term;
48 l_value := p_value;
49 l_freq := p_frequency;
50 l_sum_one_by_df := 0;
51 l_rsdpv := ((l_value / 100) / ((1 + ((l_rate / 100) * l_dc)) ** l_term));
52 /*
53 dbms_output.put_line('resd val df = ' ||
54 ((1 + ((l_rate / 100) * l_dc)) ** l_term));
55 dbms_output.put_line('resd val sumpv = ' ||
56 l_rsdpv);
57 */
58 i := l_arrear;
59
60 --
61
62 WHILE(i <= l_term + l_arrear - 1 - l_adv_loop) LOOP
63 /*
64 dbms_output.put_line('Timing = ' ||
65 i);
66 */
67 /*i represents timing*/
68
69 l_df := (1 + (l_rate / 100 * l_dc)) ** i;
70 --l_pv := (l_lrf / l_df);
71 /*
72 dbms_output.put_line(' DF =' ||
73 l_df);
74 */
75 --
76
77 /*if this i is payment and we do not want to deffer this
78 payment then add 1/df to sum_one_by_df*/
79
80 IF (i - l_arrear) MOD l_freq = 0 AND l_def = 0 THEN
81
82 -- l_sumpv := l_sumpv+l_pv;
83 -- l_df_last_added := l_df;
84
85 l_sum_one_by_df := l_sum_one_by_df + (1 / l_df);
86 --dbms_output.put_line(' 1/l_df added in Timing =' || i);
87 END IF;
88 /*
89 if this i is payment and payments to deffer > 0 then reduce the
90 payments to deffer l_def by 1
91 */
92
93 IF (l_def > 0 AND ((i - l_arrear) MOD l_freq = 0)) THEN
94 l_def := l_def - 1;
95 END IF;
96
97 /*increment timing only if all advance payments have been taken care of*/
98
99 IF (l_adv > 0) THEN
100 l_adv := l_adv - 1;
101 ELSE
102 i := i + 1;
103 END IF;
104 /*
105 dbms_output.put_line('At timing ' ||
106 i ||
107 ' l_sum_one_by_df= ' ||
108 l_sum_one_by_df);
109 */
110 END LOOP;
111 /*
112 dbms_output.put_line('l_sum_one_by_df before l_lrf computation = ' ||
113 l_sum_one_by_df);
114 */
115 l_lrf := (1 - l_rsdpv) / l_sum_one_by_df;
116 p_lrf := l_lrf;
117 END calculate_lrf;
118
119
120 /**
121 This function validates the residual tolerance. The residual tolerance
122 should not be greater than (min of difference between pairs of terms).
123 **/
124
125 FUNCTION is_residual_tolerance_valid(p_lrf_table lease_rate_tbl_type
126 ,p_residual_tolerance number) RETURN boolean IS
127 mindiff number;
128 diffij number;
129
130 BEGIN
131
132 IF p_residual_tolerance = 0 OR p_lrf_table.COUNT = 1 THEN
133 RETURN true;
134 END IF;
135 mindiff := abs(p_lrf_table(1).residual_value_percent - p_lrf_table(2).residual_value_percent);
136
137 --find the minimum difference between terms
138
139 FOR i IN p_lrf_table.FIRST..p_lrf_table.LAST - 1 LOOP
140 FOR j IN i + 1..p_lrf_table.LAST LOOP
141 diffij := abs(p_lrf_table(i).residual_value_percent - p_lrf_table(j).residual_value_percent);
142
143 IF diffij < mindiff THEN
144 mindiff := diffij;
145 END IF;
146
147 END LOOP;
148 END LOOP;
149
150 IF p_residual_tolerance >= mindiff / 2 THEN
151 RETURN false;
152 ELSE
153 RETURN true;
154 END IF;
155
156 END is_residual_tolerance_valid;
157 /**
158 This procedures derives the term-value pairs from end of term and generates the
159 lease rate factors.
160 **/
161
162 PROCEDURE generate_lease_rate_factors(p_api_version IN number
163 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
164 ,x_return_status OUT NOCOPY varchar2
165 ,x_msg_count OUT NOCOPY number
166 ,x_msg_data OUT NOCOPY varchar2
167 ,p_rate_set_version_id okl_fe_rate_set_versions.rate_set_version_id%TYPE) IS
168
169 CURSOR c_rate_set(csr_rate_set_version_id IN number) IS
170 SELECT decode(b.arrears_yn, 'Y', 1, 0) arrears
171 ,decode(a.frq_code, 'M', 1, 'Q', 3, 'S', 6, 'A', 12, 0) frequency
172 ,nvl(b.deferred_pmts, 0) deferred_pmts
173 ,nvl(b.advance_pmts, 0) advance_pmts
174 ,b.lrs_rate
175 ,b.effective_from_date
176 ,b.end_of_term_ver_id
177 ,b.std_rate_tmpl_ver_id
178 ,nvl(b.residual_tolerance, 0) residual_tolerance
179 ,b.rate_set_id
180 ,b.standard_rate
181 FROM okl_ls_rt_fctr_sets_v a
182 ,okl_fe_rate_set_versions b
183 WHERE a.id = b.rate_set_id
184 AND b.rate_set_version_id = csr_rate_set_version_id;
185 c_rate_set_rec c_rate_set%ROWTYPE;
186
187 CURSOR get_srt_type_rate(csr_std_rate_tmpl_ver_id IN number) IS
188 SELECT a.rate_type_code
189 ,(b.srt_rate+nvl(b.spread,0)) srt_rate
190 FROM okl_fe_std_rt_tmp_v a
191 ,okl_fe_std_rt_tmp_vers b
192 WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id
193 AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id;
194
195 CURSOR get_srt_index_rate(csr_std_rate_tmpl_ver_id IN number
196 ,lrs_eff_from IN date) IS
197 SELECT (c.value+nvl(b.spread,0)) value
198 FROM okl_fe_std_rt_tmp_v a
199 ,okl_fe_std_rt_tmp_vers b
200 ,okl_index_values c
201 WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id AND a.index_id = c.idx_id
202 AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id
203 AND lrs_eff_from BETWEEN c.datetime_valid AND nvl(c.datetime_invalid, lrs_eff_from + 1);
204
205 CURSOR get_eot_resd_type(eot_version_id IN number) IS
206 SELECT a.eot_type_code
207 FROM okl_fe_eo_terms_v a
208 ,okl_fe_eo_term_vers b
209 WHERE a.end_of_term_id = b.end_of_term_id
210 AND b.end_of_term_ver_id = eot_version_id;
211
212 CURSOR get_eot_values(eot_version_id IN number) IS
213 SELECT eot_term
214 ,eot_value
215 FROM okl_fe_eo_term_values a
216 WHERE a.end_of_term_ver_id = eot_version_id;
217
218 CURSOR get_eot_category_code(eot_version_id IN number) IS
219 SELECT category_type_code
220 FROM okl_fe_eo_terms_v a
221 ,okl_fe_eo_term_vers b
222 WHERE a.end_of_term_id = b.end_of_term_id
223 AND b.end_of_term_ver_id = eot_version_id;
224
225 CURSOR get_item_resd_vals(eot_version_id IN number
226 ,p_lrs_effective_from_date IN date) IS
227 SELECT DISTINCT irsval.term_in_months term_in_months
228 ,irsval.residual_value residual_value
229 FROM okl_fe_eo_terms_v eoth
230 ,okl_fe_eo_term_vers eotv
231 ,okl_fe_eo_term_objects eoto
232 ,OKL_FE_ITEM_RESIDUAL irsh
233 ,okl_itm_cat_rv_prcs irsv
234 ,okl_fe_item_resdl_values irsval
235 WHERE eoth.eot_type_code = 'RESIDUAL_PERCENT'
236 AND eotv.end_of_term_ver_id = eot_version_id
237 AND eoth.end_of_term_id = eotv.end_of_term_id
238 AND eotv.end_of_term_ver_id = eoto.end_of_term_ver_id
239 AND eoth.category_type_code = irsh.category_type_code
240 AND eoth.category_type_code = 'ITEM'
241 AND irsh.category_type_code = 'ITEM'
242 AND irsh.residual_type_code = 'PERCENT'
243 AND eoto.inventory_item_id = irsh.inventory_item_id
244 AND eoto.organization_id = irsh.organization_id
245 AND eoto.category_set_id = irsh.category_set_id
246 AND irsh.item_residual_id = irsv.item_residual_id
247 AND irsv.sts_code = 'ACTIVE'
248 AND p_lrs_effective_from_date BETWEEN irsv.start_date AND nvl(irsv.end_date, to_date('01-01-9999', 'dd-mm-yyyy'))
249 AND irsv.id = irsval.item_resdl_version_id;
250
251 CURSOR get_itemcat_resd_vals(eot_version_id IN number
252 ,p_lrs_effective_from_date IN date) IS
253 SELECT DISTINCT irsval.term_in_months term_in_months
254 ,irsval.residual_value residual_value
255 FROM okl_fe_eo_terms_v eoth
256 ,okl_fe_eo_term_vers eotv
257 ,okl_fe_eo_term_objects eoto
258 ,OKL_FE_ITEM_RESIDUAL irsh
259 ,okl_itm_cat_rv_prcs irsv
260 ,okl_fe_item_resdl_values irsval
261 WHERE eoth.eot_type_code = 'RESIDUAL_PERCENT'
262 AND eotv.end_of_term_ver_id = eot_version_id
263 AND eoth.end_of_term_id = eotv.end_of_term_id
264 AND eoth.category_type_code = 'ITEMCAT'
265 AND eotv.end_of_term_ver_id = eoto.end_of_term_ver_id
266 AND eoth.category_type_code = irsh.category_type_code
267 AND eoto.category_id = irsh.category_id
268 AND eoto.category_set_id = irsh.category_set_id
269 AND irsh.category_type_code = 'ITEMCAT'
270 AND irsh.residual_type_code = 'PERCENT'
271 AND irsh.item_residual_id = irsv.item_residual_id
272 AND irsv.sts_code = 'ACTIVE'
273 AND p_lrs_effective_from_date BETWEEN irsv.start_date AND nvl(irsv.end_date, to_date('01-01-9999', 'dd-mm-yyyy'))
274 AND irsv.id = irsval.item_resdl_version_id;
275
276 CURSOR get_resdcat_resd_vals(eot_version_id IN number
277 ,p_lrs_effective_from_date IN date) IS
278 SELECT DISTINCT f.term_in_months
279 ,f.residual_value
280 FROM okl_fe_eo_terms_v a
281 ,okl_fe_eo_term_vers b
282 ,okl_fe_eo_term_objects c
283 ,OKL_FE_ITEM_RESIDUAL d
284 ,okl_itm_cat_rv_prcs e
285 ,okl_fe_item_resdl_values f
286 WHERE b.end_of_term_ver_id = eot_version_id
287 AND a.end_of_term_id = b.end_of_term_id
288 AND b.end_of_term_ver_id = c.end_of_term_ver_id
289 AND a.category_type_code = d.category_type_code
290 AND a.category_type_code = 'RESCAT'
291 AND d.residual_type_code = 'PERCENT'
292 AND c.resi_category_set_id = d.resi_category_set_id
293 AND d.item_residual_id = e.item_residual_id
294 AND e.sts_code = 'ACTIVE'
295 AND p_lrs_effective_from_date BETWEEN e.start_date AND nvl(e.end_date, p_lrs_effective_from_date + 1)
296 AND e.id = f.item_resdl_version_id;
297 l_rate number := NULL;
298 l_srt_type varchar2(30);
299 l_eot_resd_type varchar2(30);
300 l_lease_rate_tbl lease_rate_tbl_type;
301 l_gen_lrf_tbl lease_rate_tbl_type;
302 l_lrlv_tbl okl_lrlv_tbl;
303 l_lrfv_tbl lrfv_tbl_type;
304 lx_lrlv_tbl okl_lrlv_tbl;
305 lx_lrfv_tbl lrfv_tbl_type;
306 i number;
307 j number;
308 l_eot_category_code varchar2(30);
309 x_lrf number;
310 l_day_conv number;
311 lx_return_status varchar2(1);
312 l_api_name CONSTANT varchar2(30) := 'gen_lrf';
313 l_api_version CONSTANT number := 1.0;
314 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
315 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lrf_generate_pvt.generate_lease_rate_factors';
316 l_debug_enabled varchar2(10);
317 is_debug_procedure_on boolean;
318 is_debug_statement_on boolean;
319
320 BEGIN
321 l_debug_enabled := okl_debug_pub.check_log_enabled;
322 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
323 ,fnd_log.level_procedure);
324
325 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
326 okl_debug_pub.log_debug(fnd_log.level_procedure
327 ,l_module
328 ,'begin debug OKLRRFGB.pls call generate_lease_rate_factors');
329 END IF;
330
331 -- check for logging on STATEMENT level
332
333 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
334 ,fnd_log.level_statement);
335
336 -- call START_ACTIVITY to create savepoint, check compatibility
337 -- and initialize message list
338
339 l_return_status := okl_api.start_activity(p_api_name => l_api_name
340 ,p_pkg_name => g_pkg_name
341 ,p_init_msg_list => p_init_msg_list
342 ,l_api_version => l_api_version
343 ,p_api_version => p_api_version
344 ,p_api_type => g_api_type
345 ,x_return_status => x_return_status);
346
347 -- check if activity started successfully
348
349 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
350 RAISE okl_api.g_exception_unexpected_error;
351 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
352 RAISE okl_api.g_exception_error;
353 END IF;
354 --IF G_CP_MODE = 'Y' then the procedure is called in Concurrent program
355 IF g_cp_mode IS NOT NULL THEN
356 IF g_cp_mode <> 'Y' THEN
357 g_cp_mode := 'N';
358 END IF;
359 ELSE
360 g_cp_mode := 'N';
361 END IF;
362 -- take the lrs version attributes
363 OPEN c_rate_set(p_rate_set_version_id);
364 FETCH c_rate_set INTO c_rate_set_rec ;
365 CLOSE c_rate_set;
366 l_rate := c_rate_set_rec.lrs_rate;
367 --if rate is not defined on lrs version then take it from srt version
368 IF l_rate IS NULL THEN
369 /*OPEN get_srt_type_rate(c_rate_set_rec.std_rate_tmpl_ver_id);
370 FETCH get_srt_type_rate INTO l_srt_type
371 ,l_rate ;
372 CLOSE get_srt_type_rate;
373 --if srt is of index rate type take rate from okl_index_values for lrs version effective from
374 IF l_srt_type = 'INDEX' THEN
375 OPEN get_srt_index_rate(c_rate_set_rec.std_rate_tmpl_ver_id
376 ,c_rate_set_rec.effective_from_date);
377 FETCH get_srt_index_rate INTO l_rate ;
378 CLOSE get_srt_index_rate;
379 END IF;*/
380 IF c_rate_set_rec.std_rate_tmpl_ver_id IS NOT NULL THEN
381 l_rate := c_rate_set_rec.standard_rate;
382 END IF;
383 END IF;
384 --if rate is still null then throw error
385 IF l_rate IS NULL THEN --set message Rate cannot be determined for RATE_SET_VERSION_ID
386 okl_api.set_message(p_app_name => okl_api.g_app_name
387 ,p_msg_name => 'OKL_RATE_UNDETERMINED_FOR_LRS');
388 RAISE okl_api.g_exception_error;
389 END IF;
390
391 --get the eot residual type
392 OPEN get_eot_resd_type(c_rate_set_rec.end_of_term_ver_id);
393 FETCH get_eot_resd_type INTO l_eot_resd_type ;
394 CLOSE get_eot_resd_type;
395 --if resd type=PERCENT take term value pairs from EOT version
396 IF l_eot_resd_type = 'PERCENT' THEN
397 i := 1;
398
399 FOR term_val_rec IN get_eot_values(c_rate_set_rec.end_of_term_ver_id) LOOP --populate term_val_rec and l_rate into l_lease_rate_tbl(i)
400 l_lease_rate_tbl(i).term_in_months := term_val_rec.eot_term;
401 l_lease_rate_tbl(i).residual_value_percent := term_val_rec.eot_value;
402 l_lease_rate_tbl(i).interest_rate := l_rate;
403 i := i + 1;
404 END LOOP;
405
406 END IF;
407 --if resd RESIDUAL_PERCENT take term value pairs from item residual
408 IF l_eot_resd_type = 'RESIDUAL_PERCENT' THEN --take the category_type from EOT header
409 OPEN get_eot_category_code(c_rate_set_rec.end_of_term_ver_id);
410 FETCH get_eot_category_code INTO l_eot_category_code ;
411 CLOSE get_eot_category_code;
412 --if category_code= ITEM then fetch term value pairs from item residual of category type=item
413 IF l_eot_category_code = 'ITEM' THEN
414 i := 1;
415
416 FOR term_val_rec IN get_item_resd_vals(c_rate_set_rec.end_of_term_ver_id
417 ,c_rate_set_rec.effective_from_date) LOOP --populate term_val_rec and l_rate into l_lease_rate_tbl(i)
418 l_lease_rate_tbl(i).term_in_months := term_val_rec.term_in_months;
419 l_lease_rate_tbl(i).residual_value_percent := term_val_rec.residual_value;
420 l_lease_rate_tbl(i).interest_rate := l_rate;
421 i := i + 1;
422 END LOOP;
423 --if category_code= ITEMCAT then fetch term value pairs from item residual of category type=ITEMCAT
424 ELSIF l_eot_category_code = 'ITEMCAT' THEN
425 i := 1;
426
427 FOR term_val_rec IN get_itemcat_resd_vals(c_rate_set_rec.end_of_term_ver_id
428 ,c_rate_set_rec.effective_from_date) LOOP --populate term_val_rec and l_rate into l_lease_rate_tbl(i)
429 l_lease_rate_tbl(i).term_in_months := term_val_rec.term_in_months;
430 l_lease_rate_tbl(i).residual_value_percent := term_val_rec.residual_value;
431 l_lease_rate_tbl(i).interest_rate := l_rate;
432 i := i + 1;
433 END LOOP;
434 --if category_code= RESCAT then fetch term value pairs from item residual of category type=RESICAT
435 ELSIF l_eot_category_code = 'RESCAT' THEN
436 i := 1;
437
438 FOR term_val_rec IN get_resdcat_resd_vals(c_rate_set_rec.end_of_term_ver_id
439 ,c_rate_set_rec.effective_from_date) LOOP --populate term_val_rec and l_rate into l_lease_rate_tbl(i)
440 l_lease_rate_tbl(i).term_in_months := term_val_rec.term_in_months;
441 l_lease_rate_tbl(i).residual_value_percent := term_val_rec.residual_value;
442 l_lease_rate_tbl(i).interest_rate := l_rate;
443 i := i + 1;
444 END LOOP;
445
446 END IF;
447 END IF;
448
449 --end of If l_eot_type = 'RESIDUAL_PERCENT'
450 --if l_lease_rate_tbl is empty then throw error
451
452 IF l_lease_rate_tbl.COUNT = 0 THEN
453 NULL; -- NO Term value pairs to generate lrf
454 okl_api.set_message(p_app_name => okl_api.g_app_name
455 ,p_msg_name => 'OKL_NO_VALID_TERM_VALUE_PAIRS');
456 RAISE okl_api.g_exception_error;
457 END IF;
458
459 --validate following
460 --validate that term is exact multiple of payment frequency
461 --and no. of deferred payments are not greater than or equal to total payments
462 --and no. of advance payments are not greater than or equal to total payments
463 --do not generate lrf for records failing the validation
464
465 j := 1;
466
467 FOR i IN l_lease_rate_tbl.FIRST..l_lease_rate_tbl.LAST LOOP
468
469 IF NOT ((l_lease_rate_tbl(i).term_in_months MOD c_rate_set_rec.frequency <> 0)
470 OR (c_rate_set_rec.deferred_pmts >= (l_lease_rate_tbl(i).term_in_months / c_rate_set_rec.frequency))
471 OR (c_rate_set_rec.advance_pmts >= (l_lease_rate_tbl(i).term_in_months / c_rate_set_rec.frequency))) THEN
472 l_gen_lrf_tbl(j) := l_lease_rate_tbl(i);
473 j := j + 1;
474 END IF;
475
476 END LOOP; --if all records are to be ignored then throw error
477
478 IF l_gen_lrf_tbl.COUNT = 0 THEN -- NO Term value pairs to generate lrf
479 okl_api.set_message(p_app_name => okl_api.g_app_name
480 ,p_msg_name => 'OKL_NO_VALID_TERM_VALUE_PAIRS');
481 RAISE okl_api.g_exception_error;
482 END IF;
483
484 --validate the residual tolerance
485 --residual tolerance should not be greater than (min of difference between pairs of residual values)
486
487 IF NOT is_residual_tolerance_valid(l_gen_lrf_tbl
488 ,c_rate_set_rec.residual_tolerance) THEN
489 okl_api.set_message(p_app_name => okl_api.g_app_name
490 ,p_msg_name => 'OKL_INVALID_RESIDUAL_TOLERANCE');
491 RAISE okl_api.g_exception_error;
492 END IF;
493 --generate the lease rate factors for term-value pairs in l_gen_lrf_tbl
494 FOR i IN l_gen_lrf_tbl.FIRST..l_gen_lrf_tbl.LAST LOOP
495 l_day_conv := 30 / 360;
496 calculate_lrf(c_rate_set_rec.arrears
497 ,l_rate
498 ,l_day_conv
499 ,c_rate_set_rec.deferred_pmts
500 ,c_rate_set_rec.advance_pmts
501 ,l_gen_lrf_tbl(i).term_in_months
502 ,l_gen_lrf_tbl(i).residual_value_percent
503 ,c_rate_set_rec.frequency
504 ,x_lrf); --if not valid then raise error else populate lrf
505 l_gen_lrf_tbl(i).lease_rate_factor := trunc(x_lrf, 4);
506 END LOOP;
507 --delete lrf lines and corressponding levels from the okl_ls_rt_fctr_ents_b and okl_fe_lrs_levels table for this lrs version
508 okl_lease_rate_factors_pvt.delete_lease_rate_factors(p_api_version => p_api_version
509 ,p_init_msg_list => okl_api.g_false
510 ,x_return_status => lx_return_status
511 ,x_msg_count => x_msg_count
512 ,x_msg_data => x_msg_data
513 ,p_lrv_id => p_rate_set_version_id);
514
515 IF lx_return_status = g_ret_sts_error THEN
516 RAISE okl_api.g_exception_error;
517 ELSIF lx_return_status = g_ret_sts_unexp_error THEN
518 RAISE okl_api.g_exception_unexpected_error;
519 END IF;
520 j := 1; --for each record in l_gen_lrf_tbl do
521
522 FOR i IN l_gen_lrf_tbl.FIRST..l_gen_lrf_tbl.LAST LOOP --populate okl_lrl_tbl
523 l_lrfv_tbl(i).id := i;
524 l_lrfv_tbl(i).is_new_flag := 'Y'; --new record
525 l_lrfv_tbl(i).lrt_id := c_rate_set_rec.rate_set_id;
526 l_lrfv_tbl(i).rate_set_version_id := p_rate_set_version_id;
527 l_lrfv_tbl(i).term_in_months := l_gen_lrf_tbl(i).term_in_months;
528 l_lrfv_tbl(i).residual_value_percent := l_gen_lrf_tbl(i).residual_value_percent;
529 l_lrfv_tbl(i).interest_rate := l_gen_lrf_tbl(i).interest_rate;
530 l_lrfv_tbl(i).lease_rate_factor := l_gen_lrf_tbl(i).lease_rate_factor;
531
532 IF g_cp_mode = 'Y' THEN
533 fnd_file.put_line(fnd_file.log
534 ,'Term In Months = ' ||
535 l_lrfv_tbl(i).term_in_months);
536 fnd_file.put_line(fnd_file.log
537 ,'Residual Value in percent = ' ||
538 l_lrfv_tbl(i).residual_value_percent);
539 fnd_file.put_line(fnd_file.log
540 ,'Interest Rate = ' ||
541 l_lrfv_tbl(i).interest_rate);
542 fnd_file.put_line(fnd_file.log
543 ,'Lease Rate Factor = ' ||
544 l_lrfv_tbl(i).lease_rate_factor);
545 fnd_file.put_line(fnd_file.log, 'Lease Rate Factor Levels: ');
546 END IF;
547
548 --if deferred_pmts > 0 then
549 -- okl_lrl_tbl(1).period=deferred_pmts
550 -- okl_lrl_tbl(1).rate_factor = 0
551 -- okl_lrl_tbl(2).period=term/freq - deferred_pmts
552 -- okl_lrl_tbl(2).rate_factor = l_lease_rate_tbl(i).lease_rate_factor
553 -- okl_lrl_tbl(1).rate_set_version_id=p_lrf_tbl(i).rate_set_version_id
554 -- okl_lrl_tbl(2).rate_set_version_id=p_lrf_tbl(i).rate_set_version_id
555
556 IF c_rate_set_rec.deferred_pmts > 0 THEN
557 l_lrlv_tbl(j).rate_set_level_id := NULL;
558 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
559 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
560 l_lrlv_tbl(j).rate_set_factor_id := i;
561 l_lrlv_tbl(j).periods := c_rate_set_rec.deferred_pmts;
562 l_lrlv_tbl(j).lease_rate_factor := 0;
563 l_lrlv_tbl(j).sequence_number := 1;
564 IF g_cp_mode = 'Y' THEN
565 fnd_file.put_line(fnd_file.log
566 ,' Periods = ' ||
567 l_lrlv_tbl(j).periods);
568 fnd_file.put_line(fnd_file.log
569 ,' Lease Rate Factor = ' ||
570 l_lrlv_tbl(j).lease_rate_factor);
571 END IF;
572 j := j + 1;
573 l_lrlv_tbl(j).rate_set_level_id := NULL;
574 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
575 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
576 l_lrlv_tbl(j).rate_set_factor_id := i;
577 l_lrlv_tbl(j).periods := (l_lrfv_tbl(i).term_in_months / c_rate_set_rec.frequency) - c_rate_set_rec.deferred_pmts;
578 l_lrlv_tbl(j).lease_rate_factor := l_lrfv_tbl(i).lease_rate_factor;
579 l_lrlv_tbl(j).sequence_number := 2;
580 IF g_cp_mode = 'Y' THEN
581 fnd_file.put_line(fnd_file.log
582 ,' Periods = ' ||
583 l_lrlv_tbl(j).periods);
584 fnd_file.put_line(fnd_file.log
585 ,' Lease Rate Factor = ' ||
586 l_lrlv_tbl(j).lease_rate_factor);
587 END IF;
588 j := j + 1;
589
590 --else if advance_pmts > 0 then
591 -- okl_lrl_tbl(1).period=term/freq - advance_pmts
592 -- okl_lrl_tbl(1).rate_factor = l_lease_rate_tbl(i).lease_rate_factor
593 -- okl_lrl_tbl(2).period=advance_pmts
594 -- okl_lrl_tbl(2).rate_factor = 0
595 -- okl_lrl_tbl(1).rate_set_version_id=p_lrf_tbl(i).rate_set_version_id
596 -- okl_lrl_tbl(2).rate_set_version_id=p_lrf_tbl(i).rate_set_version_id
597
598 ELSIF c_rate_set_rec.advance_pmts > 0 THEN
599 l_lrlv_tbl(j).rate_set_level_id := NULL;
600 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
601 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
602 l_lrlv_tbl(j).rate_set_factor_id := i;
603 l_lrlv_tbl(j).periods := 1;
604 l_lrlv_tbl(j).lease_rate_factor := l_lrfv_tbl(i).lease_rate_factor * (c_rate_set_rec.advance_pmts + 1) ;
605 l_lrlv_tbl(j).sequence_number := 1;
606 IF g_cp_mode = 'Y' THEN
607 fnd_file.put_line(fnd_file.log
608 ,' Periods = ' ||
609 l_lrlv_tbl(j).periods);
610 fnd_file.put_line(fnd_file.log
611 ,' Lease Rate Factor = ' ||
612 l_lrlv_tbl(j).lease_rate_factor);
613 END IF;
614 j := j + 1;
615 l_lrlv_tbl(j).rate_set_level_id := NULL;
616 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
617 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
618 l_lrlv_tbl(j).rate_set_factor_id := i;
619 l_lrlv_tbl(j).periods := (l_lrfv_tbl(i).term_in_months / c_rate_set_rec.frequency) - c_rate_set_rec.advance_pmts -1;
620 l_lrlv_tbl(j).lease_rate_factor := l_lrfv_tbl(i).lease_rate_factor;
621 l_lrlv_tbl(j).sequence_number := 2;
622 IF g_cp_mode = 'Y' THEN
623 fnd_file.put_line(fnd_file.log
624 ,' Periods = ' ||
625 l_lrlv_tbl(j).periods);
626 fnd_file.put_line(fnd_file.log
627 ,' Lease Rate Factor = ' ||
628 l_lrlv_tbl(j).lease_rate_factor);
629 END IF;
630 IF l_lrlv_tbl(j).periods > 0 THEN
631 j := j + 1;
632 END IF;
633 l_lrlv_tbl(j).rate_set_level_id := NULL;
634 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
635 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
636 l_lrlv_tbl(j).rate_set_factor_id := i;
637 l_lrlv_tbl(j).periods := c_rate_set_rec.advance_pmts;
638 l_lrlv_tbl(j).lease_rate_factor := 0;
639 l_lrlv_tbl(j).sequence_number := 3;
640 IF g_cp_mode = 'Y' THEN
641 fnd_file.put_line(fnd_file.log
642 ,' Periods = ' ||
643 l_lrlv_tbl(j).periods);
644 fnd_file.put_line(fnd_file.log
645 ,' Lease Rate Factor = ' ||
646 l_lrlv_tbl(j).lease_rate_factor);
647 END IF;
648 j := j + 1;
649
650 --else (level)
651 -- okl_lrl_tbl(1).period=term/freq
652 -- okl_lrl_tbl(1).rate_factor = l_lease_rate_tbl(i).lease_rate_factor
653 -- okl_lrl_tbl(1).rate_set_version_id=p_lrf_tbl(i).rate_set_version_id
654
655 ELSE
656 l_lrlv_tbl(j).rate_set_level_id := NULL;
657 l_lrlv_tbl(j).rate_set_id := c_rate_set_rec.rate_set_id;
658 l_lrlv_tbl(j).rate_set_version_id := p_rate_set_version_id;
659 l_lrlv_tbl(j).rate_set_factor_id := i;
660 l_lrlv_tbl(j).periods := (l_lrfv_tbl(i).term_in_months / c_rate_set_rec.frequency);
661 l_lrlv_tbl(j).lease_rate_factor := l_lrfv_tbl(i).lease_rate_factor;
662 l_lrlv_tbl(j).sequence_number := 1;
663 IF g_cp_mode = 'Y' THEN
664 fnd_file.put_line(fnd_file.log
665 ,' Periods = ' ||
666 l_lrlv_tbl(j).periods);
667 fnd_file.put_line(fnd_file.log
668 ,' Lease Rate Factor = ' ||
669 l_lrlv_tbl(j).lease_rate_factor);
670 END IF;
671 j := j + 1;
672 END IF;
673
674 END LOOP;
675
676 IF g_cp_mode = 'Y' THEN
677 fnd_file.put_line(fnd_file.log, 'Inserting into okl_ls_rt_fctr_ents');
678 END IF;
679 --call handlelrf API to insert levels and factors
680 okl_lease_rate_factors_pvt.handle_lrf_ents(p_api_version => p_api_version
681 ,p_init_msg_list => okl_api.g_false
682 ,x_return_status => lx_return_status
683 ,x_msg_count => x_msg_count
684 ,x_msg_data => x_msg_data
685 ,p_lrfv_tbl => l_lrfv_tbl
686 ,x_lrfv_tbl => lx_lrfv_tbl
687 ,p_lrlv_tbl => l_lrlv_tbl
688 ,x_lrlv_tbl => lx_lrlv_tbl);
689
690 IF lx_return_status = g_ret_sts_error THEN
691 RAISE okl_api.g_exception_error;
692 ELSIF lx_return_status = g_ret_sts_unexp_error THEN
693 RAISE okl_api.g_exception_unexpected_error;
694 END IF;
695 x_return_status := lx_return_status;
696 okl_api.end_activity(x_msg_count => x_msg_count
697 ,x_msg_data => x_msg_data);
698
699 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
700 okl_debug_pub.log_debug(fnd_log.level_procedure
701 ,l_module
702 ,'end debug OKLRRFGB.pls call generate_lease_rate_factors');
703 END IF;
704
705 EXCEPTION
706 WHEN okl_api.g_exception_error THEN
707 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
708 ,p_pkg_name => g_pkg_name
709 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
710 ,x_msg_count => x_msg_count
711 ,x_msg_data => x_msg_data
712 ,p_api_type => g_api_type);
713 WHEN okl_api.g_exception_unexpected_error THEN
714 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
715 ,p_pkg_name => g_pkg_name
716 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
717 ,x_msg_count => x_msg_count
718 ,x_msg_data => x_msg_data
719 ,p_api_type => g_api_type);
720 WHEN OTHERS THEN
721 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
722 ,p_pkg_name => g_pkg_name
723 ,p_exc_name => 'OTHERS'
724 ,x_msg_count => x_msg_count
725 ,x_msg_data => x_msg_data
726 ,p_api_type => g_api_type);
727 END generate_lease_rate_factors;
728
729
730 /**
731 This procedure is called from concurrent program to generate the lease rate factors.
732 **/
733 PROCEDURE generate_lrf(errbuf OUT NOCOPY varchar2
734 ,retcode OUT NOCOPY varchar2
735 ,p_rate_set_version_id IN number
736 ,p_start_date IN varchar2
737 ,p_end_date IN varchar2) IS
738 l_proc_name CONSTANT varchar2(30) := 'generate_lrf';
739 x_msg_count number;
740 x_msg_data varchar2(2000);
741 l_return_status varchar2(1) := g_ret_sts_success;
742 param_error EXCEPTION;
743 l_rate_set_version_id number;
744 l_start_date date;
745 l_end_date date;
746 l_data varchar2(2000);
747 l_msg_index_out number;
748
749 CURSOR get_lrs_versions(p_start_date IN date
750 ,p_end_date IN date) IS
751 SELECT rate_set_version_id
752 FROM okl_fe_rate_set_versions
753 WHERE effective_from_date BETWEEN p_start_date AND nvl(p_end_date, to_date('01-01-9999', 'dd-mm-yyyy'))
754 AND sts_code = 'NEW' AND rate_set_id NOT IN(SELECT id
755 FROM okl_ls_rt_fctr_sets_v WHERE lrs_type_code = 'MANUAL');
756
757 BEGIN
758
759 -- The parameter retcode returns 0 for success,
760 -- 1 for success with warnings, and 2 for error.
761
762 retcode := 0;
763 l_rate_set_version_id := p_rate_set_version_id;
764 l_start_date := fnd_date.canonical_to_date(p_start_date);
765 l_end_date := fnd_date.canonical_to_date(p_end_date);
766 g_cp_mode := 'Y';
767 fnd_file.put_line(fnd_file.log, 'OKL Generate Lease Rate Factors');
768 fnd_file.put_line(fnd_file.log, '================================');
769 fnd_file.put_line(fnd_file.log, ' ');
770
771 IF l_rate_set_version_id IS NULL AND l_start_date IS NULL THEN
772 fnd_file.put_line(fnd_file.log
773 ,'ERROR: Either of Rate Set Version Or Effective Dates must be entered');
774 l_return_status := g_ret_sts_error;
775 retcode := 2;
776 RAISE param_error;
777 END IF;
778
779 IF l_rate_set_version_id IS NOT NULL THEN
780 fnd_file.put_line(fnd_file.log
781 ,'Generating lease rate factors for Rate Set Version Id :' ||
782 l_rate_set_version_id);
783
784 --generate lease rate factors
785
786 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
787 ,p_init_msg_list => g_true
788 ,x_return_status => l_return_status
789 ,x_msg_count => x_msg_count
790 ,x_msg_data => x_msg_data
791 ,p_rate_set_version_id => l_rate_set_version_id);
792 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
793 fnd_file.put_line(fnd_file.log
794 ,'Unexpected error in call to okl_lrf_generate_pvt.generate_lease_rate_factors');
795 RAISE okl_api.g_exception_unexpected_error;
796 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
797 fnd_file.put_line(fnd_file.log
798 ,'Error in call to okl_lrf_generate_pvt.generate_lease_rate_factors');
799 RAISE okl_api.g_exception_error;
800 END IF;
801 ELSE
802
803 FOR lrv IN get_lrs_versions(l_start_date, l_end_date) LOOP
804 l_rate_set_version_id := lrv.rate_set_version_id;
805 fnd_file.put_line(fnd_file.log
806 ,'Generating lease rate factors for Rate Set Version Id :' ||
807 l_rate_set_version_id);
808
809 --generate lease rate factors
810
811 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
812 ,p_init_msg_list => g_true
813 ,x_return_status => l_return_status
814 ,x_msg_count => x_msg_count
815 ,x_msg_data => x_msg_data
816 ,p_rate_set_version_id => l_rate_set_version_id);
817 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
818 fnd_file.put_line(fnd_file.log
819 ,'Unexpected error in call to okl_lrf_generate_pvt.generate_lease_rate_factors');
820 RAISE okl_api.g_exception_unexpected_error;
821 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
822 fnd_file.put_line(fnd_file.log
823 ,'Error in call to okl_lrf_generate_pvt.generate_lease_rate_factors');
824
825 -- RAISE OKL_API.G_EXCEPTION_ERROR;
826 -- print the error message in the log file
827
828 IF (fnd_msg_pub.count_msg > 0) THEN
829 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
830 fnd_msg_pub.get(p_msg_index => l_counter
831 ,p_encoded => 'F'
832 ,p_data => l_data
833 ,p_msg_index_out => l_msg_index_out);
834 fnd_file.put_line(fnd_file.log, l_data);
835 END LOOP;
836 END IF;
837 END IF;
838 END LOOP;
839
840 END IF;
841 fnd_file.put_line(fnd_file.log, ' ');
842 fnd_file.put_line(fnd_file.log, ' End ');
843 fnd_file.put_line(fnd_file.log, '================================');
844 fnd_file.close;
845 EXCEPTION
846 WHEN param_error THEN
847 retcode := 0;
848 fnd_file.put_line(fnd_file.log
849 ,'Generate Lease Rate Factors Concurrent program completed with errors.');
850
851 --close all open cursors
852
853 IF get_lrs_versions%ISOPEN THEN
854 CLOSE get_lrs_versions;
855 END IF;
856
857 WHEN okl_api.g_exception_error THEN
858 retcode := 2;
859
860 --close all open cursors
861
862 IF get_lrs_versions%ISOPEN THEN
863 CLOSE get_lrs_versions;
864 END IF;
865
866 -- print the error message in the output file
867
868 IF (fnd_msg_pub.count_msg > 0) THEN
869
870 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
871 fnd_msg_pub.get(p_msg_index => l_counter
872 ,p_encoded => 'F'
873 ,p_data => l_data
874 ,p_msg_index_out => l_msg_index_out);
875 fnd_file.put_line(fnd_file.log, l_data);
876 END LOOP;
877
878 END IF;
879
880 WHEN okl_api.g_exception_unexpected_error THEN
881 retcode := 2;
882
883 --close all open cursors
884
885 IF get_lrs_versions%ISOPEN THEN
886 CLOSE get_lrs_versions;
887 END IF;
888
889 -- print the error message in the output file
890
891 IF (fnd_msg_pub.count_msg > 0) THEN
892
893 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
894 fnd_msg_pub.get(p_msg_index => l_counter
895 ,p_encoded => 'F'
896 ,p_data => l_data
897 ,p_msg_index_out => l_msg_index_out);
898 fnd_file.put_line(fnd_file.log, l_data);
899 END LOOP;
900
901 END IF;
902
903 WHEN OTHERS THEN
904 retcode := 2;
905 errbuf := sqlerrm;
906
907 --close all open cursors
908
909 IF get_lrs_versions%ISOPEN THEN
910 CLOSE get_lrs_versions;
911 END IF;
912
913 -- print the error message in the output file
914
915 IF (fnd_msg_pub.count_msg > 0) THEN
916
917 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
918 fnd_msg_pub.get(p_msg_index => l_counter
919 ,p_encoded => 'F'
920 ,p_data => l_data
921 ,p_msg_index_out => l_msg_index_out);
922 fnd_file.put_line(fnd_file.log, l_data);
923 END LOOP;
924
925 END IF;
926 fnd_file.put_line(fnd_file.log, sqlerrm);
927 END generate_lrf;
928 End OKL_LRF_GENERATE_PVT;