[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_RATE_FACTORS_PVT
Source
1 PACKAGE BODY OKL_LEASE_RATE_FACTORS_PVT AS
2 /* $Header: OKLRLRFB.pls 120.4 2006/03/27 05:34:04 asawanka noship $ */
3
4 /**
5 This procedure inserts/updates the lease rate factors and levels.
6 If p_lrfv_tbl(i).IS_NEW_FLAG = 'Y' then record is inserted else updated.
7 If p_lrlv_tbl(i).rate_Set_level_id=null then record is inserted else updated.
8 p_lrlv_tbl(i).(i).rate_set_factor_id should be pointing to appropriate record in
9 p_lrfv_tbl, to identify the levels corressponding to factor.
10 **/
11
12 PROCEDURE handle_lrf_ents(p_api_version IN number
13 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
14 ,x_return_status OUT NOCOPY varchar2
15 ,x_msg_count OUT NOCOPY number
16 ,x_msg_data OUT NOCOPY varchar2
17 ,p_lrfv_tbl IN lrfv_tbl_type
18 ,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
19 ,p_lrlv_tbl IN okl_lrlv_tbl
20 ,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
21 lp_lrfv_tbl lrfv_tbl_type;
22 lx_lrfv_tbl lrfv_tbl_type;
23 lp_lrlv_tbl okl_lrlv_tbl;
24 lx_lrlv_tbl okl_lrlv_tbl;
25 lp_lrfv_crt_tbl lrfv_tbl_type;
26 lx_lrfv_crt_tbl lrfv_tbl_type;
27 lp_lrfv_upd_tbl lrfv_tbl_type;
28 lx_lrfv_upd_tbl lrfv_tbl_type;
29 l_api_name CONSTANT varchar2(30) := 'handle_lrf_ents';
30 l_api_version CONSTANT number := 1.0;
31 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
32 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.handle_lrf_ents';
33 l_debug_enabled varchar2(10);
34 is_debug_procedure_on boolean;
35 is_debug_statement_on boolean;
36
37 BEGIN
38 l_debug_enabled := okl_debug_pub.check_log_enabled;
39 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
40 ,fnd_log.level_procedure);
41
42 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
43 okl_debug_pub.log_debug(fnd_log.level_procedure
44 ,l_module
45 ,'begin debug OKLPLRFB.pls call handle_lrf_ents');
46 END IF;
47
48 -- check for logging on STATEMENT level
49
50 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
51 ,fnd_log.level_statement);
52
53 -- call START_ACTIVITY to create savepoint, check compatibility
54 -- and initialize message list
55
56 l_return_status := okl_api.start_activity(p_api_name => l_api_name
57 ,p_pkg_name => g_pkg_name
58 ,p_init_msg_list => p_init_msg_list
59 ,l_api_version => l_api_version
60 ,p_api_version => p_api_version
61 ,p_api_type => g_api_type
62 ,x_return_status => x_return_status);
63
64 -- check if activity started successfully
65
66 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
67 RAISE okl_api.g_exception_unexpected_error;
68 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
69 RAISE okl_api.g_exception_error;
70 END IF;
71 lp_lrfv_tbl := p_lrfv_tbl;
72 lp_lrlv_tbl := p_lrlv_tbl; -- call validate_periods to see sum of periods = term/frequency
73
74 FOR i IN lp_lrfv_tbl.FIRST..lp_lrfv_tbl.LAST LOOP
75
76 IF (lp_lrfv_tbl(i).is_new_flag = 'Y') THEN
77 okl_lrf_pvt.insert_row(l_api_version
78 ,g_false
79 ,l_return_status
80 ,x_msg_count
81 ,x_msg_data
82 ,lp_lrfv_tbl(i)
83 ,lx_lrfv_tbl(i));
84
85 -- write to log
86
87 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
88 okl_debug_pub.log_debug(fnd_log.level_statement
89 ,l_module
90 ,'Procedure okl_lrf_pvt.insert_row with status ' ||
91 l_return_status);
92 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
93 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
94 RAISE okl_api.g_exception_error;
95 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
96 RAISE okl_api.g_exception_unexpected_error;
97 END IF;
98 FOR j IN lp_lrlv_tbl.FIRST..lp_lrlv_tbl.LAST LOOP
99
100 --if the ecl_id in lp_ecv_tbl(i) is same as the id in the recently inserter record
101 -- in okl_ec_lines table then
102
103 IF (lp_lrlv_tbl(j).rate_set_factor_id = lp_lrfv_tbl(i).id) THEN
104
105 --populate the ecl_id with the id of the recently inserted record in okl_ec_lines tablein the
106
107 lp_lrlv_tbl(j).rate_set_factor_id := lx_lrfv_tbl(i).id;
108 IF (lp_lrlv_tbl(j).rate_set_level_id IS NULL) THEN
109 okl_lrl_pvt.insert_row(l_api_version
110 ,g_false
111 ,l_return_status
112 ,x_msg_count
113 ,x_msg_data
114 ,lp_lrlv_tbl(j)
115 ,lx_lrlv_tbl(j));
116
117 -- write to log
118
119 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
120 okl_debug_pub.log_debug(fnd_log.level_statement
121 ,l_module
122 ,'Procedure okl_lrl_pvt.insert_row with status ' ||
123 l_return_status);
124 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
125 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
126 RAISE okl_api.g_exception_error;
127 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
128 RAISE okl_api.g_exception_unexpected_error;
129 END IF;
130 ELSE
131 okl_lrl_pvt.update_row(l_api_version
132 ,g_false
133 ,l_return_status
134 ,x_msg_count
135 ,x_msg_data
136 ,lp_lrlv_tbl(j)
137 ,lx_lrlv_tbl(j));
138
139 -- write to log
140
141 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
142 okl_debug_pub.log_debug(fnd_log.level_statement
143 ,l_module
144 ,'Procedure okl_lrl_pvt.update_row with status ' ||
145 l_return_status);
146 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
147 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
148 RAISE okl_api.g_exception_error;
149 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
150 RAISE okl_api.g_exception_unexpected_error;
151 END IF;
152 END IF;
153 END IF;
154 END LOOP;
155 ELSE
156 okl_lrf_pvt.update_row(l_api_version
157 ,g_false
158 ,l_return_status
159 ,x_msg_count
160 ,x_msg_data
161 ,lp_lrfv_tbl(i)
162 ,lx_lrfv_tbl(i));
163
164 -- write to log
165
166 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
167 okl_debug_pub.log_debug(fnd_log.level_statement
168 ,l_module
169 ,'Procedure okl_lrf_pvt.update_row with status ' ||
170 l_return_status);
171 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
172 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
173 RAISE okl_api.g_exception_error;
174 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
175 RAISE okl_api.g_exception_unexpected_error;
176 END IF;
177 FOR j IN lp_lrlv_tbl.FIRST..lp_lrlv_tbl.LAST LOOP
178
179 --if the ecl_id in lp_ecv_tbl(i) is same as the id in the recently inserter record
180 -- in okl_ec_lines table then
181
182 IF (lp_lrlv_tbl(j).rate_set_factor_id = lp_lrfv_tbl(i).id) THEN --populate the ecl_id with the id of the recently inserted record in okl_ec_lines tablein the
183 lp_lrlv_tbl(j).rate_set_factor_id := lx_lrfv_tbl(i).id;
184 IF (lp_lrlv_tbl(j).rate_set_level_id IS NULL) THEN
185 okl_lrl_pvt.insert_row(l_api_version
186 ,g_false
187 ,l_return_status
188 ,x_msg_count
189 ,x_msg_data
190 ,lp_lrlv_tbl(j)
191 ,lx_lrlv_tbl(j));
192
193 -- write to log
194
195 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
196 okl_debug_pub.log_debug(fnd_log.level_statement
197 ,l_module
198 ,'Procedure okl_lrl_pvt.insert_row with status ' ||
199 l_return_status);
200 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
201 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
202 RAISE okl_api.g_exception_error;
203 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
204 RAISE okl_api.g_exception_unexpected_error;
205 END IF;
206 ELSE
207 okl_lrl_pvt.update_row(l_api_version
208 ,g_false
209 ,l_return_status
210 ,x_msg_count
211 ,x_msg_data
212 ,lp_lrlv_tbl(j)
213 ,lx_lrlv_tbl(j));
214
215 -- write to log
216
217 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
218 okl_debug_pub.log_debug(fnd_log.level_statement
219 ,l_module
220 ,'Procedure okl_lrl_pvt.update_row with status ' ||
221 l_return_status);
222 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
223 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
224 RAISE okl_api.g_exception_error;
225 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
226 RAISE okl_api.g_exception_unexpected_error;
227 END IF;
228 END IF;
229 END IF;
230 END LOOP;
231 END IF;
232
233 END LOOP; --Assign value to OUT variables
234 x_lrfv_tbl := lx_lrfv_tbl;
235 x_lrlv_tbl := lx_lrlv_tbl;
236 x_return_status := l_return_status;
237 okl_api.end_activity(x_msg_count => x_msg_count
238 ,x_msg_data => x_msg_data);
239
240 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
241 okl_debug_pub.log_debug(fnd_log.level_procedure
242 ,l_module
243 ,'end debug OKLRECVB.pls.pls call handle_lrf_ents');
244 END IF;
245
246 EXCEPTION
247 WHEN okl_api.g_exception_error THEN
248 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
249 ,p_pkg_name => g_pkg_name
250 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
251 ,x_msg_count => x_msg_count
252 ,x_msg_data => x_msg_data
253 ,p_api_type => g_api_type);
254 WHEN okl_api.g_exception_unexpected_error THEN
255 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
256 ,p_pkg_name => g_pkg_name
257 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
258 ,x_msg_count => x_msg_count
259 ,x_msg_data => x_msg_data
260 ,p_api_type => g_api_type);
261 WHEN OTHERS THEN
262 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
263 ,p_pkg_name => g_pkg_name
264 ,p_exc_name => 'OTHERS'
265 ,x_msg_count => x_msg_count
266 ,x_msg_data => x_msg_data
267 ,p_api_type => g_api_type);
268 END handle_lrf_ents;
269 /**
270 This procedure deletes all the lease rate factors and corressponding levels
271 for rate set version id p_lrv_id.
272 **/
273
274 PROCEDURE delete_lease_rate_factors(p_api_version IN number
275 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
276 ,x_return_status OUT NOCOPY varchar2
277 ,x_msg_count OUT NOCOPY number
278 ,x_msg_data OUT NOCOPY varchar2
279 ,p_lrv_id IN number) IS
280 l_lrv_id number;
281 l_lrlv_tbl okl_lrlv_tbl;
282 l_lrfv_tbl lrfv_tbl_type;
283
284 CURSOR get_lrf_tbl IS
285 SELECT id
286 FROM okl_ls_rt_fctr_ents
287 WHERE rate_set_version_id = p_lrv_id;
288
289 CURSOR get_lrl_tbl IS
290 SELECT rate_set_level_id
291 FROM okl_fe_rate_set_levels
292 WHERE rate_set_version_id = p_lrv_id;
293 i number;
294 l_api_name CONSTANT varchar2(30) := 'delete_lrf';
295 l_api_version CONSTANT number := 1.0;
296 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
297 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.delete_lease_rate_factors';
298 l_debug_enabled varchar2(10);
299 is_debug_procedure_on boolean;
300 is_debug_statement_on boolean;
301
302 BEGIN
303 l_debug_enabled := okl_debug_pub.check_log_enabled;
304 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
305 ,fnd_log.level_procedure);
306
307 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
308 okl_debug_pub.log_debug(fnd_log.level_procedure
309 ,l_module
310 ,'begin debug OKLPLRFB.pls call delete_lease_rate_factors');
311 END IF;
312
313 -- check for logging on STATEMENT level
314
315 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
316 ,fnd_log.level_statement);
317
318 -- call START_ACTIVITY to create savepoint, check compatibility
319 -- and initialize message list
320
321 l_return_status := okl_api.start_activity(p_api_name => l_api_name
322 ,p_pkg_name => g_pkg_name
323 ,p_init_msg_list => p_init_msg_list
324 ,l_api_version => l_api_version
325 ,p_api_version => p_api_version
326 ,p_api_type => g_api_type
327 ,x_return_status => x_return_status);
328
329 -- check if activity started successfully
330
331 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
332 RAISE okl_api.g_exception_unexpected_error;
333 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
334 RAISE okl_api.g_exception_error;
335 END IF;
336 l_lrv_id := p_lrv_id;
337
338 --populate factors table with all the factors of rate_Set_version_id
339
340 i := 1;
341
342 FOR lrf_rec IN get_lrf_tbl LOOP
343 l_lrfv_tbl(i).id := lrf_rec.id;
344 i := i + 1;
345 END LOOP;
346
347 --populate levels table with all the levels of rate_Set_version_id
348
349 i := 1;
350
351 FOR lrl_rec IN get_lrl_tbl LOOP
352 l_lrlv_tbl(i).rate_set_level_id := lrl_rec.rate_set_level_id;
353 i := i + 1;
354 END LOOP;
355
356 --delete all levels
357
358 IF l_lrlv_tbl.COUNT > 0 THEN
359 okl_lrl_pvt.delete_row(p_api_version => p_api_version
360 ,p_init_msg_list => okl_api.g_false
361 ,x_return_status => l_return_status
362 ,x_msg_count => x_msg_count
363 ,x_msg_data => x_msg_data
364 ,p_lrlv_tbl => l_lrlv_tbl);
365
366 -- write to log
367
368 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
369 okl_debug_pub.log_debug(fnd_log.level_statement
370 ,l_module
371 ,'Procedure OKL_LRL_PVT.delete_row returned with status ' ||
372 l_return_status);
373 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
374 IF l_return_status = g_ret_sts_error THEN
375 RAISE okl_api.g_exception_error;
376 ELSIF l_return_status = g_ret_sts_unexp_error THEN
377 RAISE okl_api.g_exception_unexpected_error;
378 END IF;
379 END IF;
380
381 --delete all factors
382
383 IF l_lrfv_tbl.COUNT > 0 THEN
384 okl_lrf_pvt.delete_row(p_api_version => p_api_version
385 ,p_init_msg_list => okl_api.g_false
386 ,x_return_status => l_return_status
387 ,x_msg_count => x_msg_count
388 ,x_msg_data => x_msg_data
389 ,p_lrfv_tbl => l_lrfv_tbl);
390
391 -- write to log
392
393 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
394 okl_debug_pub.log_debug(fnd_log.level_statement
395 ,l_module
396 ,'Procedure OKL_LRF_PVT.delete_row returned with status ' ||
397 l_return_status);
398 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
399 IF l_return_status = g_ret_sts_error THEN
400 RAISE okl_api.g_exception_error;
401 ELSIF l_return_status = g_ret_sts_unexp_error THEN
402 RAISE okl_api.g_exception_unexpected_error;
403 END IF;
404 END IF;
405 x_return_status := l_return_status;
406 okl_api.end_activity(x_msg_count => x_msg_count
407 ,x_msg_data => x_msg_data);
408
409 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
410 okl_debug_pub.log_debug(fnd_log.level_procedure
411 ,l_module
412 ,'end debug OKLRECVB.pls.pls call delete_lease_rate_factors');
413 END IF;
414
415 EXCEPTION
416 WHEN okl_api.g_exception_error THEN
417 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
418 ,p_pkg_name => g_pkg_name
419 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
420 ,x_msg_count => x_msg_count
421 ,x_msg_data => x_msg_data
422 ,p_api_type => g_api_type);
423 WHEN okl_api.g_exception_unexpected_error THEN
424 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
425 ,p_pkg_name => g_pkg_name
426 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
427 ,x_msg_count => x_msg_count
428 ,x_msg_data => x_msg_data
429 ,p_api_type => g_api_type);
430 WHEN OTHERS THEN
431 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
432 ,p_pkg_name => g_pkg_name
433 ,p_exc_name => 'OTHERS'
434 ,x_msg_count => x_msg_count
435 ,x_msg_data => x_msg_data
436 ,p_api_type => g_api_type);
437 END delete_lease_rate_factors;
438 /**
439 This procedure deletes the lease rate factor row and its corressponding
440 levels.
441 **/
442
443 PROCEDURE remove_lrs_factor (p_api_version IN number
444 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
445 ,x_return_status OUT NOCOPY varchar2
446 ,x_msg_count OUT NOCOPY number
447 ,x_msg_data OUT NOCOPY varchar2
448 ,p_lrfv_rec IN lrfv_rec_type) IS
449 l_lrfv_rec lrfv_rec_type;
450 l_lrlv_tbl okl_lrlv_tbl;
451
452 CURSOR get_lrl_tbl IS
453 SELECT rate_set_level_id
454 FROM okl_fe_rate_set_levels
455 WHERE rate_set_factor_id = p_lrfv_rec.id;
456 i number;
457 l_api_name CONSTANT varchar2(30) := 'rmv_lrs_factor';
458 l_api_version CONSTANT number := 1.0;
459 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
460 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.remove_lrs_factor';
461 l_debug_enabled varchar2(10);
462 is_debug_procedure_on boolean;
463 is_debug_statement_on boolean;
464
465 BEGIN
466 l_debug_enabled := okl_debug_pub.check_log_enabled;
467 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
468 ,fnd_log.level_procedure);
469
470 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
471 okl_debug_pub.log_debug(fnd_log.level_procedure
472 ,l_module
473 ,'begin debug OKLPLRFB.pls call remove_lrs_factor');
474 END IF;
475
476 -- check for logging on STATEMENT level
477
478 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
479 ,fnd_log.level_statement);
480
481 -- call START_ACTIVITY to create savepoint, check compatibility
482 -- and initialize message list
483
484 l_return_status := okl_api.start_activity(p_api_name => l_api_name
485 ,p_pkg_name => g_pkg_name
486 ,p_init_msg_list => p_init_msg_list
487 ,l_api_version => l_api_version
488 ,p_api_version => p_api_version
489 ,p_api_type => g_api_type
490 ,x_return_status => x_return_status);
491
492 -- check if activity started successfully
493
494 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
495 RAISE okl_api.g_exception_unexpected_error;
496 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
497 RAISE okl_api.g_exception_error;
498 END IF;
499 l_lrfv_rec := p_lrfv_rec;
500
501 --populate levels table with all the levels of rate_Set_version_id
502
503 i := 1;
504
505 FOR lrl_rec IN get_lrl_tbl LOOP
506 l_lrlv_tbl(i).rate_set_level_id := lrl_rec.rate_set_level_id;
507 i := i + 1;
508 END LOOP;
509
510 --delete all levels
511
512 IF l_lrlv_tbl.COUNT > 0 THEN
513 okl_lrl_pvt.delete_row(p_api_version => p_api_version
514 ,p_init_msg_list => okl_api.g_false
515 ,x_return_status => l_return_status
516 ,x_msg_count => x_msg_count
517 ,x_msg_data => x_msg_data
518 ,p_lrlv_tbl => l_lrlv_tbl);
519 IF l_return_status = g_ret_sts_error THEN
520 RAISE okl_api.g_exception_error;
521 ELSIF l_return_status = g_ret_sts_unexp_error THEN
522 RAISE okl_api.g_exception_unexpected_error;
523 END IF;
524 END IF; --delete factor row
525 okl_lrf_pvt.delete_row(p_api_version => p_api_version
526 ,p_init_msg_list => okl_api.g_false
527 ,x_return_status => l_return_status
528 ,x_msg_count => x_msg_count
529 ,x_msg_data => x_msg_data
530 ,p_lrfv_rec => l_lrfv_rec);
531
532 IF l_return_status = g_ret_sts_error THEN
533 RAISE okl_api.g_exception_error;
534 ELSIF l_return_status = g_ret_sts_unexp_error THEN
535 RAISE okl_api.g_exception_unexpected_error;
536 END IF;
537 x_return_status := l_return_status;
538 okl_api.end_activity(x_msg_count => x_msg_count
539 ,x_msg_data => x_msg_data);
540
541 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
542 okl_debug_pub.log_debug(fnd_log.level_procedure
543 ,l_module
544 ,'end debug OKLRECVB.pls.pls call remove_lrs_factor');
545 END IF;
546
547 EXCEPTION
548 WHEN okl_api.g_exception_error THEN
549 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
550 ,p_pkg_name => g_pkg_name
551 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
552 ,x_msg_count => x_msg_count
553 ,x_msg_data => x_msg_data
554 ,p_api_type => g_api_type);
555 WHEN okl_api.g_exception_unexpected_error THEN
556 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
557 ,p_pkg_name => g_pkg_name
558 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
559 ,x_msg_count => x_msg_count
560 ,x_msg_data => x_msg_data
561 ,p_api_type => g_api_type);
562 WHEN OTHERS THEN
563 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
564 ,p_pkg_name => g_pkg_name
565 ,p_exc_name => 'OTHERS'
566 ,x_msg_count => x_msg_count
567 ,x_msg_data => x_msg_data
568 ,p_api_type => g_api_type);
569 END remove_lrs_factor;
570 /**
571 This procedure deletes the lease rate factor level.
572 **/
573
574 PROCEDURE remove_lrs_level(p_api_version IN number
575 ,p_init_msg_list IN varchar2 DEFAULT fnd_api.g_false
576 ,x_return_status OUT NOCOPY varchar2
577 ,x_msg_count OUT NOCOPY number
578 ,x_msg_data OUT NOCOPY varchar2
579 ,p_lrlv_rec IN okl_lrlv_rec) IS
580 l_lrlv_rec okl_lrlv_rec;
581 l_api_name CONSTANT varchar2(30) := 'rmv_lrs_level';
582 l_api_version CONSTANT number := 1.0;
583 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
584 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.remove_lrs_level';
585 l_debug_enabled varchar2(10);
586 is_debug_procedure_on boolean;
587 is_debug_statement_on boolean;
588
589 BEGIN
590 l_debug_enabled := okl_debug_pub.check_log_enabled;
591 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
592 ,fnd_log.level_procedure);
593
594 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
595 okl_debug_pub.log_debug(fnd_log.level_procedure
596 ,l_module
597 ,'begin debug OKLPLRFB.pls call remove_lrs_level');
598 END IF;
599
600 -- check for logging on STATEMENT level
601
602 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
603 ,fnd_log.level_statement);
604
605 -- call START_ACTIVITY to create savepoint, check compatibility
606 -- and initialize message list
607
608 l_return_status := okl_api.start_activity(p_api_name => l_api_name
609 ,p_pkg_name => g_pkg_name
610 ,p_init_msg_list => p_init_msg_list
611 ,l_api_version => l_api_version
612 ,p_api_version => p_api_version
613 ,p_api_type => g_api_type
614 ,x_return_status => x_return_status);
615
616 -- check if activity started successfully
617
618 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
619 RAISE okl_api.g_exception_unexpected_error;
620 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
621 RAISE okl_api.g_exception_error;
622 END IF;
623 l_lrlv_rec := p_lrlv_rec;
624 okl_lrl_pvt.delete_row(p_api_version => p_api_version
625 ,p_init_msg_list => okl_api.g_false
626 ,x_return_status => l_return_status
627 ,x_msg_count => x_msg_count
628 ,x_msg_data => x_msg_data
629 ,p_lrlv_rec => l_lrlv_rec);
630
631 IF l_return_status = g_ret_sts_error THEN
632 RAISE okl_api.g_exception_error;
633 ELSIF l_return_status = g_ret_sts_unexp_error THEN
634 RAISE okl_api.g_exception_unexpected_error;
635 END IF;
636 x_return_status := l_return_status;
637 okl_api.end_activity(x_msg_count => x_msg_count
638 ,x_msg_data => x_msg_data);
639
640 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
641 okl_debug_pub.log_debug(fnd_log.level_procedure
642 ,l_module
643 ,'end debug OKLRECVB.pls.pls call remove_lrs_level');
644 END IF;
645
646 EXCEPTION
647 WHEN okl_api.g_exception_error THEN
648 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
649 ,p_pkg_name => g_pkg_name
650 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
651 ,x_msg_count => x_msg_count
652 ,x_msg_data => x_msg_data
653 ,p_api_type => g_api_type);
654 WHEN okl_api.g_exception_unexpected_error THEN
655 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
656 ,p_pkg_name => g_pkg_name
657 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
658 ,x_msg_count => x_msg_count
659 ,x_msg_data => x_msg_data
660 ,p_api_type => g_api_type);
661 WHEN OTHERS THEN
662 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
663 ,p_pkg_name => g_pkg_name
664 ,p_exc_name => 'OTHERS'
665 ,x_msg_count => x_msg_count
666 ,x_msg_data => x_msg_data
667 ,p_api_type => g_api_type);
668 END remove_lrs_level;
669 /**
670 This function does the following validations:
671 1. At least one lease rate factor row should be present.
672 2. Term should be multiple of frequency.
673 3. Sum of periods in all levels corressponding to the lease rate factor
674 should be equal to the term.
675 **/
676
677 FUNCTION validate_factor_levels(p_lrfv_tbl IN lrfv_tbl_type
678 ,p_lrlv_tbl IN okl_lrlv_tbl
679 ,p_freq IN varchar2) RETURN varchar2 IS
680 l_freq number;
681 l_payments number;
682 l_periods number;
683 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.validate_factor_levels';
684 l_debug_enabled varchar2(10);
685 is_debug_procedure_on boolean;
686 is_debug_statement_on boolean;
687
688 BEGIN
689 l_debug_enabled := okl_debug_pub.check_log_enabled;
690 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
691 ,fnd_log.level_procedure);
692
693 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
694 okl_debug_pub.log_debug(fnd_log.level_procedure
695 ,l_module
696 ,'begin debug OKLPLRFB.pls call validate_factor_levels');
697 END IF;
698
699 -- check for logging on STATEMENT level
700
701 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
702 ,fnd_log.level_statement);
703
704 --at least 1 rate factor should be present
705
706 IF p_lrfv_tbl.COUNT = 0 THEN
707 okl_api.set_message(p_app_name => g_app_name
708 ,p_msg_name => 'OKL_RATE_FACTOR_NOT_PRESENT');
709 RETURN okl_api.g_ret_sts_error;
710 END IF;
711
712 SELECT decode(p_freq, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)
713 INTO l_freq
714 FROM dual;
715
716 FOR i IN p_lrfv_tbl.FIRST..p_lrfv_tbl.LAST LOOP
717
718 --term should be exact multiple of frequency
719
720 IF p_lrfv_tbl(i).term_in_months MOD l_freq <> 0 THEN
721 okl_api.set_message(p_app_name => g_app_name
722 ,p_msg_name => 'OKL_TERM_NOT_MULT_OF_FREQ'
723 ,p_token1 => 'TERM'
724 ,p_token1_value => p_lrfv_tbl(i).term_in_months);
725 RETURN okl_api.g_ret_sts_error;
726 END IF;
727 l_payments := p_lrfv_tbl(i).term_in_months / l_freq;
728 l_periods := 0;
729
730 --at least 1 rate factor leval should be present
731
732 IF p_lrlv_tbl.COUNT = 0 THEN
733 okl_api.set_message(p_app_name => g_app_name
734 ,p_msg_name => 'OKL_LEVEL_NOT_PRESENT'
735 ,p_token1 => 'TERM'
736 ,p_token1_value => p_lrfv_tbl(i).term_in_months);
737 RETURN okl_api.g_ret_sts_error;
738 END IF; --sum of periods of all levels(of the factor) should be equal to total payments for the term
739
740 FOR j IN p_lrlv_tbl.FIRST..p_lrlv_tbl.LAST LOOP
741
742 IF p_lrlv_tbl(j).rate_set_factor_id = p_lrfv_tbl(i).id THEN
743 l_periods := l_periods + p_lrlv_tbl(j).periods;
744 END IF;
745
746 END LOOP;
747
748 IF l_payments <> l_periods THEN
749 okl_api.set_message(p_app_name => g_app_name
750 ,p_msg_name => 'OKL_PERIODS_NOT_EQ_PAYMENTS'
751 ,p_token1 => 'TERM'
752 ,p_token1_value => p_lrfv_tbl(i).term_in_months);
753 RETURN okl_api.g_ret_sts_error;
754 END IF;
755
756 END LOOP;
757
758 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
759 okl_debug_pub.log_debug(fnd_log.level_procedure
760 ,l_module
761 ,'end debug OKLRECVB.pls.pls call validate_factor_levels');
762 END IF;
763 RETURN okl_api.g_ret_sts_success;
764 END validate_factor_levels;
765 /**
766 This function validates whether the residual tolerance is less than or equal
767 to the minimum of the difference between the residual values.
768 **/
769
770 FUNCTION is_residual_tolerance_valid(p_lrfv_tbl IN lrfv_tbl_type
771 ,p_residual_tolerance number) RETURN boolean IS
772 mindiff number;
773 diffij number;
774 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.is_residual_tolerance_valid';
775 l_debug_enabled varchar2(10);
776 is_debug_procedure_on boolean;
777 is_debug_statement_on boolean;
778
779 BEGIN
780 l_debug_enabled := okl_debug_pub.check_log_enabled;
781 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
782 ,fnd_log.level_procedure);
783
784 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
785 okl_debug_pub.log_debug(fnd_log.level_procedure
786 ,l_module
787 ,'begin debug OKLPLRFB.pls call is_residual_tolerance_valid');
788 END IF;
789
790 -- check for logging on STATEMENT level
791
792 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
793 ,fnd_log.level_statement);
794
795 IF p_residual_tolerance = 0 OR p_lrfv_tbl.COUNT = 1 THEN
796 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
797 okl_debug_pub.log_debug(fnd_log.level_procedure
798 ,l_module
799 ,'end debug OKLRECVB.pls.pls call is_residual_tolerance_valid');
800 END IF;
801 RETURN true;
802 END IF;
803 mindiff := abs(p_lrfv_tbl(1).residual_value_percent - p_lrfv_tbl(2).residual_value_percent);
804
805 --find the minimum difference between residual_value_percent
806
807 FOR i IN p_lrfv_tbl.FIRST..p_lrfv_tbl.LAST - 1 LOOP
808 FOR j IN i + 1..p_lrfv_tbl.LAST LOOP
809 diffij := abs(p_lrfv_tbl(i).residual_value_percent - p_lrfv_tbl(j).residual_value_percent);
810
811 IF diffij < mindiff THEN
812 mindiff := diffij;
813 END IF;
814
815 END LOOP;
816 END LOOP;
817
818 IF p_residual_tolerance >= mindiff / 2 THEN
819 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
820 okl_debug_pub.log_debug(fnd_log.level_procedure
821 ,l_module
822 ,'end debug OKLRECVB.pls.pls call is_residual_tolerance_valid');
823 END IF;
824 RETURN false;
825 ELSE
826 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
827 okl_debug_pub.log_debug(fnd_log.level_procedure
828 ,l_module
829 ,'end debug OKLRECVB.pls.pls call is_residual_tolerance_valid');
830 END IF;
831 RETURN true;
832 END IF;
833
834 END is_residual_tolerance_valid;
835 /**
836 This function returns the rate from the standard rate template version,
837 If standard rate template is of type INDEX, it takes the rate from OKL_INDEX_VALUES.
838 **/
839
840 FUNCTION get_rate_from_srt(p_srt_version_id IN number
841 ,p_lrs_eff_from IN date) RETURN number IS
842
843 CURSOR get_srt_type_rate(csr_std_rate_tmpl_ver_id IN number) IS
844 SELECT a.rate_type_code
845 ,(b.srt_rate+nvl(b.spread,0)) srt_rate
846 ,min_adj_rate
847 ,max_adj_rate
848 FROM okl_fe_std_rt_tmp_v a
849 ,okl_fe_std_rt_tmp_vers b
850 WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id
851 AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id;
852
853 CURSOR get_srt_index_rate(csr_std_rate_tmpl_ver_id IN number
854 ,lrs_eff_from IN date) IS
855 SELECT (c.value+nvl(b.spread,0)) value
856 FROM okl_fe_std_rt_tmp_v a
857 ,okl_fe_std_rt_tmp_vers b
858 ,okl_index_values c
859 WHERE a.std_rate_tmpl_id = b.std_rate_tmpl_id AND a.index_id = c.idx_id
860 AND b.std_rate_tmpl_ver_id = csr_std_rate_tmpl_ver_id
861 AND lrs_eff_from BETWEEN c.datetime_valid AND nvl(c.datetime_invalid, lrs_eff_from + 1);
862 l_rate number;
863 l_srt_type varchar2(30);
864 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.get_rate_from_srt';
865 l_debug_enabled varchar2(10);
866 is_debug_procedure_on boolean;
867 is_debug_statement_on boolean;
868 l_min_adj_rate NUMBER;
869 l_max_adj_rate NUMBER;
870
871 BEGIN
872 l_debug_enabled := okl_debug_pub.check_log_enabled;
873 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
874 ,fnd_log.level_procedure);
875
876 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
877 okl_debug_pub.log_debug(fnd_log.level_procedure
878 ,l_module
879 ,'begin debug OKLPLRFB.pls call get_rate_from_srt');
880 END IF;
881
882 -- check for logging on STATEMENT level
883
884 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
885 ,fnd_log.level_statement);
886 l_rate := NULL;
887 OPEN get_srt_type_rate(p_srt_version_id);
888 FETCH get_srt_type_rate INTO l_srt_type
889 ,l_rate
890 ,l_min_adj_rate
891 ,l_max_adj_rate ;
892 CLOSE get_srt_type_rate;
893
894 --if srt is of index rate type take rate from okl_index_values for lrs version effective from
895
896 IF l_srt_type = 'INDEX' THEN
897 OPEN get_srt_index_rate(p_srt_version_id, p_lrs_eff_from);
898 FETCH get_srt_index_rate INTO l_rate ;
899 CLOSE get_srt_index_rate;
900 END IF;
901
902 IF l_rate IS NOT NULL THEN
903 IF l_min_adj_rate IS NOT NULL THEN
904 IF l_rate < l_min_adj_rate THEN
905 l_rate := l_min_adj_rate;
906 END IF;
907 END IF;
908 IF l_max_adj_rate IS NOT NULL THEN
909 IF l_rate > l_max_adj_rate THEN
910 l_rate := l_max_adj_rate;
911 END IF;
912 END IF;
913 END IF;
914
915 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
916 okl_debug_pub.log_debug(fnd_log.level_procedure
917 ,l_module
918 ,'end debug OKLRECVB.pls.pls call get_rate_from_srt');
919 END IF;
920 RETURN l_rate;
921 END get_rate_from_srt;
922 /**
923 This function validates if the term-value are unique or not.
924 **/
925
926 FUNCTION validate_unique_term_values(p_lrfv_tbl IN lrfv_tbl_type) RETURN varchar2 IS
927 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.validate_unique_term_values';
928 l_debug_enabled varchar2(10);
929 is_debug_procedure_on boolean;
930 is_debug_statement_on boolean;
931
932 BEGIN
933 l_debug_enabled := okl_debug_pub.check_log_enabled;
934 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
935 ,fnd_log.level_procedure);
936
937 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
938 okl_debug_pub.log_debug(fnd_log.level_procedure
939 ,l_module
940 ,'begin debug OKLPLRFB.pls call validate_unique_term_values');
941 END IF;
942
943 -- check for logging on STATEMENT level
944
945 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
946 ,fnd_log.level_statement);
947
948 IF p_lrfv_tbl.COUNT > 0 THEN
949
950 FOR i IN p_lrfv_tbl.FIRST..p_lrfv_tbl.LAST - 1 LOOP
951 FOR j IN i + 1..p_lrfv_tbl.LAST LOOP
952 IF p_lrfv_tbl(i).term_in_months = p_lrfv_tbl(j).term_in_months
953 AND p_lrfv_tbl(i).residual_value_percent = p_lrfv_tbl(j).residual_value_percent THEN
954 okl_api.set_message(p_app_name => okl_api.g_app_name
955 ,p_msg_name => 'OKL_DUPLICATE_TERM_VALUE'
956 ,p_token1 => 'TERM'
957 ,p_token1_value => p_lrfv_tbl(i).term_in_months
958 ,p_token2 => 'VALUE'
959 ,p_token2_value => p_lrfv_tbl(i).residual_value_percent);
960 RETURN okl_api.g_ret_sts_error;
961 END IF;
962 END LOOP;
963 END LOOP;
964
965 END IF;
966
967 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
968 okl_debug_pub.log_debug(fnd_log.level_procedure
969 ,l_module
970 ,'end debug OKLRECVB.pls.pls call validate_unique_term_values');
971 END IF;
972 RETURN okl_api.g_ret_sts_success;
973 END validate_unique_term_values;
974 /**
975 This procedure deletes the existing lease rate factors for a lease rate set
976 version.
977 **/
978
979 PROCEDURE handle_lease_rate_factors(p_api_version IN number
980 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
981 ,x_return_status OUT NOCOPY varchar2
982 ,x_msg_count OUT NOCOPY number
983 ,x_msg_data OUT NOCOPY varchar2
984 ,p_lrtv_rec IN lrtv_rec_type
985 ,p_lrvv_rec IN okl_lrvv_rec
986 ,p_lrfv_tbl IN lrfv_tbl_type
987 ,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
988 ,p_lrlv_tbl IN okl_lrlv_tbl
989 ,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
990 lp_lrvv_rec okl_lrvv_rec;
991 lp_lrtv_rec lrtv_rec_type;
992 lp_lrlv_tbl okl_lrlv_tbl;
993 lp_lrfv_tbl lrfv_tbl_type;
994 l_rate number;
995 l_api_name CONSTANT varchar2(30) := 'handle_lrf';
996 l_api_version CONSTANT number := 1.0;
997 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
998 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.handle_lease_rate_factors';
999 l_debug_enabled varchar2(10);
1000 is_debug_procedure_on boolean;
1001 is_debug_statement_on boolean;
1002
1003 BEGIN
1004 l_debug_enabled := okl_debug_pub.check_log_enabled;
1005 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1006 ,fnd_log.level_procedure);
1007
1008 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1009 okl_debug_pub.log_debug(fnd_log.level_procedure
1010 ,l_module
1011 ,'begin debug OKLPLRFB.pls call handle_lease_rate_factors');
1012 END IF;
1013
1014 -- check for logging on STATEMENT level
1015
1016 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1017 ,fnd_log.level_statement);
1018
1019 -- call START_ACTIVITY to create savepoint, check compatibility
1020 -- and initialize message list
1021
1022 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1023 ,p_pkg_name => g_pkg_name
1024 ,p_init_msg_list => p_init_msg_list
1025 ,l_api_version => l_api_version
1026 ,p_api_version => p_api_version
1027 ,p_api_type => g_api_type
1028 ,x_return_status => x_return_status);
1029
1030 -- check if activity started successfully
1031
1032 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1033 RAISE okl_api.g_exception_unexpected_error;
1034 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1035 RAISE okl_api.g_exception_error;
1036 END IF;
1037 lp_lrvv_rec := p_lrvv_rec;
1038 lp_lrtv_rec := p_lrtv_rec;
1039 lp_lrfv_tbl := p_lrfv_tbl;
1040 lp_lrlv_tbl := p_lrlv_tbl;
1041
1042 --derive the rate from srt if no rate is present on lrs
1043
1044 IF lp_lrvv_rec.lrs_rate IS NULL OR lp_lrvv_rec.lrs_rate = okl_api.g_miss_num THEN
1045 l_rate := get_rate_from_srt(lp_lrvv_rec.std_rate_tmpl_ver_id
1046 ,lp_lrvv_rec.effective_from_date);
1047 ELSE
1048 l_rate := lp_lrvv_rec.lrs_rate;
1049 END IF;
1050
1051 IF l_rate IS NULL THEN
1052 okl_api.set_message(p_app_name => okl_api.g_app_name
1053 ,p_msg_name => 'OKL_RATE_UNDETERMINED_FOR_LRS');
1054 RAISE okl_api.g_exception_error;
1055 END IF;
1056
1057 --validate that no duplicate term value pairs are present
1058
1059 l_return_status := validate_unique_term_values(lp_lrfv_tbl);
1060
1061 IF l_return_status = okl_api.g_ret_sts_error THEN
1062 RAISE okl_api.g_exception_error;
1063 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
1064 RAISE okl_api.g_exception_unexpected_error;
1065 END IF;
1066
1067 --validate the rate factors and levels
1068
1069 l_return_status := validate_factor_levels(p_lrfv_tbl => lp_lrfv_tbl
1070 ,p_lrlv_tbl => lp_lrlv_tbl
1071 ,p_freq => lp_lrtv_rec.frq_code);
1072
1073 -- write to log
1074
1075 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1076 okl_debug_pub.log_debug(fnd_log.level_statement
1077 ,l_module
1078 ,'Procedure validate_factor_levels with status ' ||
1079 l_return_status);
1080 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1081
1082 IF l_return_status = g_ret_sts_error THEN
1083 RAISE okl_api.g_exception_error;
1084 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1085 RAISE okl_api.g_exception_unexpected_error;
1086 END IF;
1087
1088 --validate the residual tolerance
1089 --residual tolerance should not be greater than (min of difference between pairs of residual values)
1090 IF lp_lrvv_rec.residual_tolerance = okl_api.g_miss_num THEN
1091 lp_lrvv_rec.residual_tolerance := 0;
1092 END IF;
1093 IF NOT is_residual_tolerance_valid(lp_lrfv_tbl
1094 ,nvl(lp_lrvv_rec.residual_tolerance
1095 ,0)) THEN
1096 okl_api.set_message(p_app_name => okl_api.g_app_name
1097 ,p_msg_name => 'OKL_INVALID_RESIDUAL_TOLERANCE');
1098 RAISE okl_api.g_exception_error;
1099 END IF;
1100
1101 --set the foreign key values and interest rate
1102
1103 FOR i IN lp_lrfv_tbl.FIRST..lp_lrfv_tbl.LAST LOOP
1104 lp_lrfv_tbl(i).is_new_flag := 'Y';
1105 lp_lrfv_tbl(i).lrt_id := lp_lrtv_rec.id;
1106 lp_lrfv_tbl(i).rate_set_version_id := lp_lrvv_rec.rate_set_version_id;
1107 lp_lrfv_tbl(i).interest_rate := l_rate;
1108 END LOOP;
1109
1110 FOR i IN lp_lrlv_tbl.FIRST..lp_lrlv_tbl.LAST LOOP
1111 lp_lrlv_tbl(i).rate_set_level_id := NULL;
1112 lp_lrlv_tbl(i).rate_set_id := lp_lrtv_rec.id;
1113 lp_lrlv_tbl(i).rate_set_version_id := lp_lrvv_rec.rate_set_version_id;
1114 END LOOP;
1115
1116 --delete existing rate factors for lp_lrvv_rec.rate_Set_version_id
1117 --we need to delete as eot version might have changed so existing lrfs, term value pairs are not valid.
1118 --also user has changed the existing lrf values
1119
1120 delete_lease_rate_factors(p_api_version => p_api_version
1121 ,p_init_msg_list => okl_api.g_false
1122 ,x_return_status => l_return_status
1123 ,x_msg_count => x_msg_count
1124 ,x_msg_data => x_msg_data
1125 ,p_lrv_id => lp_lrvv_rec.rate_set_version_id);
1126
1127 -- write to log
1128
1129 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1130 okl_debug_pub.log_debug(fnd_log.level_statement
1131 ,l_module
1132 ,'Procedure delete_lease_rate_factors with status ' ||
1133 l_return_status);
1134 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1135
1136 IF l_return_status = g_ret_sts_error THEN
1137 RAISE okl_api.g_exception_error;
1138 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1139 RAISE okl_api.g_exception_unexpected_error;
1140 END IF;
1141
1142 --call handlelrf API to insert levels and factors
1143
1144 IF lp_lrfv_tbl.COUNT > 0 THEN
1145 handle_lrf_ents(p_api_version => p_api_version
1146 ,p_init_msg_list => okl_api.g_false
1147 ,x_return_status => l_return_status
1148 ,x_msg_count => x_msg_count
1149 ,x_msg_data => x_msg_data
1150 ,p_lrfv_tbl => lp_lrfv_tbl
1151 ,x_lrfv_tbl => x_lrfv_tbl
1152 ,p_lrlv_tbl => lp_lrlv_tbl
1153 ,x_lrlv_tbl => x_lrlv_tbl);
1154
1155 -- write to log
1156
1157 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1158 okl_debug_pub.log_debug(fnd_log.level_statement
1159 ,l_module
1160 ,'Procedure handle_lrf_ents with status ' ||
1161 l_return_status);
1162 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1163 IF l_return_status = g_ret_sts_error THEN
1164 RAISE okl_api.g_exception_error;
1165 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1166 RAISE okl_api.g_exception_unexpected_error;
1167 END IF;
1168 END IF;
1169 x_return_status := l_return_status;
1170 okl_api.end_activity(x_msg_count => x_msg_count
1171 ,x_msg_data => x_msg_data);
1172
1173 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1174 okl_debug_pub.log_debug(fnd_log.level_procedure
1175 ,l_module
1176 ,'end debug OKLRECVB.pls.pls call handle_lease_rate_factors');
1177 END IF;
1178
1179 EXCEPTION
1180 WHEN okl_api.g_exception_error THEN
1181 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1182 ,p_pkg_name => g_pkg_name
1183 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1184 ,x_msg_count => x_msg_count
1185 ,x_msg_data => x_msg_data
1186 ,p_api_type => g_api_type);
1187 WHEN okl_api.g_exception_unexpected_error THEN
1188 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1189 ,p_pkg_name => g_pkg_name
1190 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1191 ,x_msg_count => x_msg_count
1192 ,x_msg_data => x_msg_data
1193 ,p_api_type => g_api_type);
1194 WHEN OTHERS THEN
1195 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1196 ,p_pkg_name => g_pkg_name
1197 ,p_exc_name => 'OTHERS'
1198 ,x_msg_count => x_msg_count
1199 ,x_msg_data => x_msg_data
1200 ,p_api_type => g_api_type);
1201 END handle_lease_rate_factors;
1202 /**
1203 This procedure is a wrapper to insert/update the lease rate factors and
1204 submit the lease rate set for approval.
1205 **/
1206
1207 PROCEDURE handle_lrf_submit(p_api_version IN number
1208 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1209 ,x_return_status OUT NOCOPY varchar2
1210 ,x_msg_count OUT NOCOPY number
1211 ,x_msg_data OUT NOCOPY varchar2
1212 ,p_lrtv_rec IN lrtv_rec_type
1213 ,p_lrvv_rec IN okl_lrvv_rec
1214 ,p_lrfv_tbl IN lrfv_tbl_type
1215 ,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type
1216 ,p_lrlv_tbl IN okl_lrlv_tbl
1217 ,x_lrlv_tbl OUT NOCOPY okl_lrlv_tbl) IS
1218 lp_lrvv_rec okl_lrvv_rec;
1219 lp_lrtv_rec lrtv_rec_type;
1220 lp_lrlv_tbl okl_lrlv_tbl;
1221 lp_lrfv_tbl lrfv_tbl_type;
1222 l_rate number;
1223 l_api_name CONSTANT varchar2(30) := 'handle_lrf_submit';
1224 l_api_version CONSTANT number := 1.0;
1225 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1226 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_factors_pvt.handle_lrf_submit';
1227 l_debug_enabled varchar2(10);
1228 is_debug_procedure_on boolean;
1229 is_debug_statement_on boolean;
1230
1231 BEGIN
1232 l_debug_enabled := okl_debug_pub.check_log_enabled;
1233 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1234 ,fnd_log.level_procedure);
1235
1236 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1237 okl_debug_pub.log_debug(fnd_log.level_procedure
1238 ,l_module
1239 ,'begin debug OKLPLRFB.pls call handle_lrf_submit');
1240 END IF;
1241
1242 -- check for logging on STATEMENT level
1243
1244 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1245 ,fnd_log.level_statement);
1246
1247 -- call START_ACTIVITY to create savepoint, check compatibility
1248 -- and initialize message list
1249
1250 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1251 ,p_pkg_name => g_pkg_name
1252 ,p_init_msg_list => p_init_msg_list
1253 ,l_api_version => l_api_version
1254 ,p_api_version => p_api_version
1255 ,p_api_type => g_api_type
1256 ,x_return_status => x_return_status);
1257
1258 -- check if activity started successfully
1259
1260 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1261 RAISE okl_api.g_exception_unexpected_error;
1262 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1263 RAISE okl_api.g_exception_error;
1264 END IF;
1265 lp_lrvv_rec := p_lrvv_rec;
1266 lp_lrtv_rec := p_lrtv_rec;
1267 lp_lrfv_tbl := p_lrfv_tbl;
1268 lp_lrlv_tbl := p_lrlv_tbl;
1269
1270 --derive the rate from srt if no rate is present on lrs
1271
1272 IF lp_lrvv_rec.lrs_rate IS NULL OR lp_lrvv_rec.lrs_rate = okl_api.g_miss_num THEN
1273 l_rate := get_rate_from_srt(lp_lrvv_rec.std_rate_tmpl_ver_id
1274 ,lp_lrvv_rec.effective_from_date);
1275 ELSE
1276 l_rate := lp_lrvv_rec.lrs_rate;
1277 END IF;
1278
1279 IF l_rate IS NULL THEN
1280 okl_api.set_message(p_app_name => okl_api.g_app_name
1281 ,p_msg_name => 'OKL_RATE_UNDETERMINED_FOR_LRS');
1282 RAISE okl_api.g_exception_error;
1283 END IF;
1284
1285 --validate that no duplicate term value pairs are present
1286
1287 l_return_status := validate_unique_term_values(lp_lrfv_tbl);
1288
1289 IF l_return_status = okl_api.g_ret_sts_error THEN
1290 RAISE okl_api.g_exception_error;
1291 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
1292 RAISE okl_api.g_exception_unexpected_error;
1293 END IF;
1294
1295 --validate the rate factors and levels
1296
1297 l_return_status := validate_factor_levels(p_lrfv_tbl => lp_lrfv_tbl
1298 ,p_lrlv_tbl => lp_lrlv_tbl
1299 ,p_freq => lp_lrtv_rec.frq_code);
1300
1301 IF l_return_status = g_ret_sts_error THEN
1302 RAISE okl_api.g_exception_error;
1303 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1304 RAISE okl_api.g_exception_unexpected_error;
1305 END IF;
1306
1307 --validate the residual tolerance
1308 --residual tolerance should not be greater than (min of difference between pairs of residual values)
1309 IF lp_lrvv_rec.residual_tolerance = okl_api.g_miss_num THEN
1310 lp_lrvv_rec.residual_tolerance := 0;
1311 END IF;
1312 IF NOT is_residual_tolerance_valid(lp_lrfv_tbl
1313 ,nvl(lp_lrvv_rec.residual_tolerance
1314 ,0)) THEN
1315 okl_api.set_message(p_app_name => okl_api.g_app_name
1316 ,p_msg_name => 'OKL_INVALID_RESIDUAL_TOLERANCE');
1317 RAISE okl_api.g_exception_error;
1318 END IF;
1319
1320 --set the foreign key values and interest rate
1321
1322 FOR i IN lp_lrfv_tbl.FIRST..lp_lrfv_tbl.LAST LOOP
1323 lp_lrfv_tbl(i).is_new_flag := 'Y';
1324 lp_lrfv_tbl(i).lrt_id := lp_lrtv_rec.id;
1325 lp_lrfv_tbl(i).rate_set_version_id := lp_lrvv_rec.rate_set_version_id;
1326 lp_lrfv_tbl(i).interest_rate := l_rate;
1327 END LOOP;
1328
1329 FOR i IN lp_lrlv_tbl.FIRST..lp_lrlv_tbl.LAST LOOP
1330 lp_lrlv_tbl(i).rate_set_level_id := NULL;
1331 lp_lrlv_tbl(i).rate_set_id := lp_lrtv_rec.id;
1332 lp_lrlv_tbl(i).rate_set_version_id := lp_lrvv_rec.rate_set_version_id;
1333 END LOOP;
1334
1335 --delete existing rate factors for lp_lrvv_rec.rate_Set_version_id
1336 --we need to delete as eot version might have changed so existing lrfs, term value pairs are not valid.
1337 --also user has changed the existing lrf values
1338
1339 delete_lease_rate_factors(p_api_version => p_api_version
1340 ,p_init_msg_list => okl_api.g_false
1341 ,x_return_status => l_return_status
1342 ,x_msg_count => x_msg_count
1343 ,x_msg_data => x_msg_data
1344 ,p_lrv_id => lp_lrvv_rec.rate_set_version_id);
1345
1346 IF l_return_status = g_ret_sts_error THEN
1347 RAISE okl_api.g_exception_error;
1348 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1349 RAISE okl_api.g_exception_unexpected_error;
1350 END IF;
1351
1352 --call handlelrf API to insert levels and factors
1353
1354 IF lp_lrfv_tbl.COUNT > 0 THEN
1355 handle_lrf_ents(p_api_version => p_api_version
1356 ,p_init_msg_list => okl_api.g_false
1357 ,x_return_status => l_return_status
1358 ,x_msg_count => x_msg_count
1359 ,x_msg_data => x_msg_data
1360 ,p_lrfv_tbl => lp_lrfv_tbl
1361 ,x_lrfv_tbl => x_lrfv_tbl
1362 ,p_lrlv_tbl => lp_lrlv_tbl
1363 ,x_lrlv_tbl => x_lrlv_tbl);
1364 IF l_return_status = g_ret_sts_error THEN
1365 RAISE okl_api.g_exception_error;
1366 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1367 RAISE okl_api.g_exception_unexpected_error;
1368 END IF;
1369 END IF;
1370
1371 --submit the lrs
1372
1373 okl_lease_rate_sets_pvt.submit_lease_rate_set(p_api_version => g_api_version
1374 ,p_init_msg_list => g_false
1375 ,x_return_status => l_return_status
1376 ,x_msg_count => x_msg_count
1377 ,x_msg_data => x_msg_data
1378 ,p_rate_set_version_id => lp_lrvv_rec.rate_set_version_id);
1379
1380 IF l_return_status = g_ret_sts_error THEN
1381 RAISE okl_api.g_exception_error;
1382 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1383 RAISE okl_api.g_exception_unexpected_error;
1384 END IF;
1385 x_return_status := l_return_status;
1386 okl_api.end_activity(x_msg_count => x_msg_count
1387 ,x_msg_data => x_msg_data);
1388
1389 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1390 okl_debug_pub.log_debug(fnd_log.level_procedure
1391 ,l_module
1392 ,'end debug OKLRECVB.pls.pls call handle_lrf_submit');
1393 END IF;
1394
1395 EXCEPTION
1396 WHEN okl_api.g_exception_error THEN
1397 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1398 ,p_pkg_name => g_pkg_name
1399 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1400 ,x_msg_count => x_msg_count
1401 ,x_msg_data => x_msg_data
1402 ,p_api_type => g_api_type);
1403 WHEN okl_api.g_exception_unexpected_error THEN
1404 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1405 ,p_pkg_name => g_pkg_name
1406 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1407 ,x_msg_count => x_msg_count
1408 ,x_msg_data => x_msg_data
1409 ,p_api_type => g_api_type);
1410 WHEN OTHERS THEN
1411 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1412 ,p_pkg_name => g_pkg_name
1413 ,p_exc_name => 'OTHERS'
1414 ,x_msg_count => x_msg_count
1415 ,x_msg_data => x_msg_data
1416 ,p_api_type => g_api_type);
1417 END handle_lrf_submit;
1418
1419 END okl_lease_rate_factors_pvt;