[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASE_RATE_SETS_PVT
Source
1 PACKAGE BODY okl_lease_rate_sets_pvt AS
2 /* $Header: OKLRLRSB.pls 120.3 2006/07/21 13:12:18 akrangan noship $ */
3
4 g_wf_evt_lrs_pending CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.fe.lrsapproval';
5 g_wf_lrs_version_id CONSTANT varchar2(50) := 'VERSION_ID';
6
7 PROCEDURE create_lease_rate_set(p_api_version IN number
8 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
9 ,x_return_status OUT NOCOPY varchar2
10 ,x_msg_count OUT NOCOPY number
11 ,x_msg_data OUT NOCOPY varchar2
12 ,p_lrtv_rec IN lrtv_rec_type
13 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
14 ,p_lrvv_rec IN okl_lrvv_rec
15 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
16
17 CURSOR lrs_unique_chk(p_name IN varchar2) IS
18 SELECT 'x'
19 FROM okl_ls_rt_fctr_sets_v
20 WHERE name = p_name;
21
22 CURSOR get_eot_version(p_eot_id number
23 ,p_eff_from IN date) IS
24 SELECT end_of_term_ver_id
25 FROM okl_fe_eo_term_vers
26 WHERE end_of_term_id = p_eot_id
27 AND p_eff_from BETWEEN effective_from_date AND nvl(effective_to_date, p_eff_from + 1)
28 AND sts_code = 'ACTIVE';
29 lp_lrvv_rec okl_lrvv_rec;
30 lp_lrtv_rec lrtv_rec_type;
31 l_eot_ver_id number;
32 l_dummy_var varchar2(1) := '?';
33 l_api_name CONSTANT varchar2(30) := 'create_lrs';
34 l_api_version CONSTANT number := 1.0;
35 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
36 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.create_lease_rate_set';
37 l_debug_enabled varchar2(10);
38 is_debug_procedure_on boolean;
39 is_debug_statement_on boolean;
40
41 BEGIN
42 l_debug_enabled := okl_debug_pub.check_log_enabled;
43 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
44 ,fnd_log.level_procedure);
45
46 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
47 okl_debug_pub.log_debug(fnd_log.level_procedure
48 ,l_module
49 ,'begin debug OKLRECCB.pls call create_lease_rate_set');
50 END IF;
51
52 -- check for logging on STATEMENT level
53
54 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
55 ,fnd_log.level_statement);
56
57 -- call START_ACTIVITY to create savepoint, check compatibility
58 -- and initialize message list
59
60 l_return_status := okl_api.start_activity(p_api_name => l_api_name
61 ,p_pkg_name => g_pkg_name
62 ,p_init_msg_list => p_init_msg_list
63 ,l_api_version => l_api_version
64 ,p_api_version => p_api_version
65 ,p_api_type => g_api_type
66 ,x_return_status => x_return_status);
67
68 -- check if activity started successfully
69
70 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
71 RAISE okl_api.g_exception_unexpected_error;
72 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
73 RAISE okl_api.g_exception_error;
74 END IF;
75
76 lp_lrvv_rec := p_lrvv_rec;
77 lp_lrtv_rec := p_lrtv_rec;
78
79 --change the name to upper case
80 lp_lrtv_rec.name := upper(lp_lrtv_rec.name);
81
82 --check uniqueness of name
83 OPEN lrs_unique_chk(lp_lrtv_rec.name);
84 FETCH lrs_unique_chk INTO l_dummy_var ;
85 CLOSE lrs_unique_chk;
86
87 -- if l_dummy_var is 'x' then name already exists
88
89 IF (l_dummy_var = 'x') THEN
90 okl_api.set_message(p_app_name => g_app_name
91 ,p_msg_name => 'OKL_DUPLICATE_NAME'
92 ,p_token1 => 'NAME'
93 ,p_token1_value => lp_lrtv_rec.name);
94 RAISE okl_api.g_exception_error;
95 END IF;
96
97 --if lrs type = 'Advance' advance payments should be present
98
99 IF lp_lrtv_rec.lrs_type_code = 'ADVANCE' THEN
100 IF lp_lrvv_rec.advance_pmts IS NULL OR lp_lrvv_rec.advance_pmts = okl_api.g_miss_num
101 OR lp_lrvv_rec.advance_pmts <= 0 THEN
102 okl_api.set_message(p_app_name => g_app_name
103 ,p_msg_name => 'OKL_ADVANCE_PAYMENTS_MANDATORY');
104 RAISE okl_api.g_exception_error;
105 END IF;
106 lp_lrvv_rec.deferred_pmts := 0;
107 END IF;
108
109 --if lrs type = 'Deferred' deferred payments should be present
110
111 IF lp_lrtv_rec.lrs_type_code = 'DEFERRED' THEN
112 IF lp_lrvv_rec.deferred_pmts IS NULL OR lp_lrvv_rec.deferred_pmts = okl_api.g_miss_num
113 OR lp_lrvv_rec.deferred_pmts <= 0 THEN
114 okl_api.set_message(p_app_name => g_app_name
115 ,p_msg_name => 'OKL_DEFERRED_PMTS_MANDATORY');
116 RAISE okl_api.g_exception_error;
117 END IF;
118 lp_lrvv_rec.advance_pmts := 0;
119 END IF;
120
121 --if lrs type = 'LEVEL' deferred payments should be present
122
123 IF lp_lrtv_rec.lrs_type_code = 'LEVEL' THEN
124 lp_lrvv_rec.deferred_pmts := 0;
125 lp_lrvv_rec.advance_pmts := 0;
126 END IF;
127
128 --if no Eot version is available raise error
129
130 l_eot_ver_id := NULL;
131 OPEN get_eot_version(lp_lrtv_rec.end_of_term_id
132 ,lp_lrvv_rec.effective_from_date);
133 FETCH get_eot_version INTO l_eot_ver_id ;
134 CLOSE get_eot_version;
135
136 IF l_eot_ver_id IS NULL THEN
137 okl_api.set_message(p_app_name => okl_api.g_app_name
138 ,p_msg_name => 'OKL_NO_EOT_VERSION_AVAILABLE'
139 ,p_token1 => 'EFFECTIVE_FROM'
140 ,p_token1_value => lp_lrvv_rec.effective_from_date);
141 RAISE okl_api.g_exception_error;
142 END IF;
143
144 --set the available eot version id
145
146 lp_lrvv_rec.end_of_term_ver_id := l_eot_ver_id;
147
148 --while creating a new lrs the header status will be NEW
149 --header eff from date = version eff from date
150 --and header eff to date = version eff to date
151
152 lp_lrtv_rec.sts_code := 'NEW';
153 lp_lrtv_rec.start_date := lp_lrvv_rec.effective_from_date;
154
155 IF lp_lrvv_rec.effective_to_date IS NULL OR lp_lrvv_rec.effective_to_date = okl_api.g_miss_date THEN
156 lp_lrtv_rec.end_date := NULL;
157 ELSE
158 lp_lrtv_rec.end_date := lp_lrvv_rec.effective_to_date;
159 END IF;
160
161 --if id is not null then this duplicate
162
163 IF lp_lrtv_rec.id IS NOT NULL THEN
164 lp_lrtv_rec.orig_rate_set_id := lp_lrtv_rec.id;
165 END IF;
166
167 okl_lrt_pvt.insert_row(p_api_version => g_api_version
168 ,p_init_msg_list => g_false
169 ,x_return_status => l_return_status
170 ,x_msg_count => x_msg_count
171 ,x_msg_data => x_msg_data
172 ,p_lrtv_rec => lp_lrtv_rec
173 ,x_lrtv_rec => x_lrtv_rec);
174
175 IF l_return_status = g_ret_sts_error THEN
176 RAISE okl_api.g_exception_error;
177 ELSIF l_return_status = g_ret_sts_unexp_error THEN
178 RAISE okl_api.g_exception_unexpected_error;
179 END IF;
180 lp_lrvv_rec.rate_set_id := x_lrtv_rec.id;
181
182 --version number is 1.0
183 --version status is new
184
185 lp_lrvv_rec.version_number := '1';
186 lp_lrvv_rec.sts_code := 'NEW';
187 okl_lrv_pvt.insert_row(p_api_version => g_api_version
188 ,p_init_msg_list => g_false
189 ,x_return_status => l_return_status
190 ,x_msg_count => x_msg_count
191 ,x_msg_data => x_msg_data
192 ,p_lrvv_rec => lp_lrvv_rec
193 ,x_lrvv_rec => x_lrvv_rec);
194
195 IF l_return_status = g_ret_sts_error THEN
196 RAISE okl_api.g_exception_error;
197 ELSIF l_return_status = g_ret_sts_unexp_error THEN
198 RAISE okl_api.g_exception_unexpected_error;
199 END IF;
200 x_return_status := l_return_status;
201 okl_api.end_activity(x_msg_count => x_msg_count
202 ,x_msg_data => x_msg_data);
203
204 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
205 okl_debug_pub.log_debug(fnd_log.level_procedure
206 ,l_module
207 ,'end debug OKLPLRTB.pls.pls call create_lease_rate_set');
208 END IF;
209
210 EXCEPTION
211 WHEN okl_api.g_exception_error THEN
212 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
213 ,p_pkg_name => g_pkg_name
214 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
215 ,x_msg_count => x_msg_count
216 ,x_msg_data => x_msg_data
217 ,p_api_type => g_api_type);
218 WHEN okl_api.g_exception_unexpected_error THEN
219 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
220 ,p_pkg_name => g_pkg_name
221 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
222 ,x_msg_count => x_msg_count
223 ,x_msg_data => x_msg_data
224 ,p_api_type => g_api_type);
225 WHEN OTHERS THEN
226 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
227 ,p_pkg_name => g_pkg_name
228 ,p_exc_name => 'OTHERS'
229 ,x_msg_count => x_msg_count
230 ,x_msg_data => x_msg_data
231 ,p_api_type => g_api_type);
232 END create_lease_rate_set;
233
234 PROCEDURE update_lease_rate_set(p_api_version IN number
235 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
236 ,x_return_status OUT NOCOPY varchar2
237 ,x_msg_count OUT NOCOPY number
238 ,x_msg_data OUT NOCOPY varchar2
239 ,p_lrtv_rec IN lrtv_rec_type
240 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
241 ,p_lrvv_rec IN okl_lrvv_rec
242 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
243
244 CURSOR get_eot_version(p_eot_id number
245 ,p_eff_from IN date) IS
246 SELECT end_of_term_ver_id
247 FROM okl_fe_eo_term_vers
248 WHERE end_of_term_id = p_eot_id
249 AND p_eff_from BETWEEN effective_from_date AND nvl(effective_to_date, p_eff_from + 1)
250 AND sts_code = 'ACTIVE';
251 lp_lrvv_rec okl_lrvv_rec;
252 lp_lrtv_rec lrtv_rec_type;
253 l_old_eot_ver_id number;
254 l_new_eot_ver_id number := NULL;
255 l_min_end_date date;
256 l_api_name CONSTANT varchar2(30) := 'update_lrs';
257 l_api_version CONSTANT number := 1.0;
258 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
259 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.update_lease_rate_set';
260 l_debug_enabled varchar2(10);
261 is_debug_procedure_on boolean;
262 is_debug_statement_on boolean;
263 l_end_date_ec boolean;
264
265 BEGIN
266 l_debug_enabled := okl_debug_pub.check_log_enabled;
267 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
268 ,fnd_log.level_procedure);
269
270 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
271 okl_debug_pub.log_debug(fnd_log.level_procedure
272 ,l_module
273 ,'begin debug OKLRECCB.pls call update_lease_rate_set');
274 END IF;
275
276 -- check for logging on STATEMENT level
277
278 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
279 ,fnd_log.level_statement);
280
281 -- call START_ACTIVITY to create savepoint, check compatibility
282 -- and initialize message list
283
284 l_return_status := okl_api.start_activity(p_api_name => l_api_name
285 ,p_pkg_name => g_pkg_name
286 ,p_init_msg_list => p_init_msg_list
287 ,l_api_version => l_api_version
288 ,p_api_version => p_api_version
289 ,p_api_type => g_api_type
290 ,x_return_status => x_return_status);
291
292 -- check if activity started successfully
293
294 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
295 RAISE okl_api.g_exception_unexpected_error;
296 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
297 RAISE okl_api.g_exception_error;
298 END IF;
299 lp_lrvv_rec := p_lrvv_rec;
300 lp_lrtv_rec := p_lrtv_rec;
301 l_end_date_ec := false;
302 l_old_eot_ver_id := lp_lrvv_rec.end_of_term_ver_id;
303
304 --if lrs version status = 'ACTIVE' then do effective to validation
305 --and if success then put hdr eff to = version eff to
306
307 IF lp_lrvv_rec.sts_code = 'ACTIVE' AND lp_lrvv_rec.effective_to_date IS NOT NULL THEN
308
309 -- if effective to entered by user is less than calculated eff_from -1 then throw error
310
311 l_min_end_date := get_newversion_effective_from(lp_lrvv_rec.rate_set_version_id) - 1;
312 IF lp_lrvv_rec.effective_to_date < l_min_end_date THEN
313 okl_api.set_message(p_app_name => g_app_name
314 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO_DATE'
315 ,p_token1 => 'DATE'
316 ,p_token1_value => l_min_end_date);
317 RAISE okl_api.g_exception_error;
318 END IF;
319 lp_lrtv_rec.end_date := lp_lrvv_rec.effective_to_date;
320 l_end_date_ec := true;
321 END IF;
322
323 --if lrs type = 'Advance' advance payments should be present
324
325 IF lp_lrtv_rec.lrs_type_code = 'ADVANCE' THEN
326 IF lp_lrvv_rec.advance_pmts IS NULL OR lp_lrvv_rec.advance_pmts = okl_api.g_miss_num
327 OR lp_lrvv_rec.advance_pmts <= 0 THEN
328 okl_api.set_message(p_app_name => g_app_name
329 ,p_msg_name => 'OKL_ADVANCE_PAYMENTS_MANDATORY');
330 RAISE okl_api.g_exception_error;
331 END IF;
332 lp_lrvv_rec.deferred_pmts := 0;
333 END IF;
334
335 --if lrs type = 'Deferred' deferred payments should be present
336
337 IF lp_lrtv_rec.lrs_type_code = 'DEFERRED' THEN
338 IF lp_lrvv_rec.deferred_pmts IS NULL OR lp_lrvv_rec.deferred_pmts = okl_api.g_miss_num
339 OR lp_lrvv_rec.deferred_pmts <= 0 THEN
340 okl_api.set_message(p_app_name => g_app_name
341 ,p_msg_name => 'OKL_DEFERRED_PMTS_MANDATORY');
342 RAISE okl_api.g_exception_error;
343 END IF;
344 lp_lrvv_rec.advance_pmts := 0;
345 END IF;
346
347 --if lrs type = 'LEVEL' deferred payments should be present
348
349 IF lp_lrtv_rec.lrs_type_code = 'LEVEL' THEN
350 lp_lrvv_rec.deferred_pmts := 0;
351 lp_lrvv_rec.advance_pmts := 0;
352 END IF;
353
354 --if no Eot version is available raise error
355
356 OPEN get_eot_version(lp_lrtv_rec.end_of_term_id
357 ,lp_lrvv_rec.effective_from_date);
358 FETCH get_eot_version INTO l_new_eot_ver_id ;
359 CLOSE get_eot_version;
360
361 IF l_new_eot_ver_id IS NULL THEN
362 okl_api.set_message(p_app_name => okl_api.g_app_name
363 ,p_msg_name => 'OKL_NO_EOT_VERSION_AVAILABLE'
364 ,p_token1 => 'EFFECTIVE_FROM'
365 ,p_token1_value => lp_lrvv_rec.effective_from_date);
366 RAISE okl_api.g_exception_error;
367 END IF;
368
369 --if eot version has changed call remove lrf api to remove all lease rate factors
370
371 IF l_old_eot_ver_id <> l_new_eot_ver_id THEN
372 okl_lease_rate_factors_pvt.delete_lease_rate_factors(p_api_version => p_api_version
373 ,p_init_msg_list => okl_api.g_false
374 ,x_return_status => l_return_status
375 ,x_msg_count => x_msg_count
376 ,x_msg_data => x_msg_data
377 ,p_lrv_id => lp_lrvv_rec.rate_set_version_id);
378 IF l_return_status = g_ret_sts_error THEN
379 RAISE okl_api.g_exception_error;
380 ELSIF l_return_status = g_ret_sts_unexp_error THEN
381 RAISE okl_api.g_exception_unexpected_error;
382 END IF;
383 END IF;
384
385 --set the END_OF_TERM_VERSION_ID as the l_new_eot_ver_id
386
387 lp_lrvv_rec.end_of_term_ver_id := l_new_eot_ver_id;
388
389 --always update the header. status and effective dates will come from frontend
390 --if hdr status=new then eff from of hdr=eff from of ver and eff to of hdr=eff to of ver
391
392 IF lp_lrtv_rec.sts_code = 'NEW' THEN
393 lp_lrtv_rec.start_date := lp_lrvv_rec.effective_from_date;
394 IF lp_lrvv_rec.effective_to_date IS NULL OR lp_lrvv_rec.effective_to_date = okl_api.g_miss_date THEN
395
396 -- make effective to date as g_miss_date to so that it nulls out in TAPI
397
398 lp_lrtv_rec.end_date := okl_api.g_miss_date;
399 lp_lrvv_rec.effective_to_date := okl_api.g_miss_date;
400 ELSE
401 lp_lrtv_rec.end_date := lp_lrvv_rec.effective_to_date;
402 END IF;
403 END IF;
404
405 --update the header
406
407 okl_lrt_pvt.update_row(p_api_version => g_api_version
408 ,p_init_msg_list => g_false
409 ,x_return_status => l_return_status
410 ,x_msg_count => x_msg_count
411 ,x_msg_data => x_msg_data
412 ,p_lrtv_rec => lp_lrtv_rec
413 ,x_lrtv_rec => x_lrtv_rec);
414
415 IF l_return_status = g_ret_sts_error THEN
416 RAISE okl_api.g_exception_error;
417 ELSIF l_return_status = g_ret_sts_unexp_error THEN
418 RAISE okl_api.g_exception_unexpected_error;
419 END IF;
420 lp_lrvv_rec.rate_set_id := x_lrtv_rec.id;
421
422
423 IF lp_lrvv_rec.effective_to_date IS NULL THEN
424 lp_lrvv_rec.effective_to_date := okl_api.g_miss_date;
425 END IF;
426 okl_lrv_pvt.update_row(p_api_version => g_api_version
427 ,p_init_msg_list => g_false
428 ,x_return_status => l_return_status
429 ,x_msg_count => x_msg_count
430 ,x_msg_data => x_msg_data
431 ,p_lrvv_rec => lp_lrvv_rec
432 ,x_lrvv_rec => x_lrvv_rec);
433
434 IF l_return_status = g_ret_sts_error THEN
435 RAISE okl_api.g_exception_error;
436 ELSIF l_return_status = g_ret_sts_unexp_error THEN
437 RAISE okl_api.g_exception_unexpected_error;
438 END IF;
439
440 --if we have put end date to active version then we should end date to ec
441
442 IF x_lrvv_rec.effective_to_date IS NOT NULL AND x_lrvv_rec.sts_code = 'ACTIVE' THEN
443
444 --Eligibility Criteria attached to previous version should be end dated
445 --with the end date of the previous version. if the end date of eligibility
446 --criteria is null or greater than previous version end date, then the
447 --api adjusts the end date to the end date of previous lrs version.
448
449 okl_ecc_values_pvt.end_date_eligibility_criteria(p_api_version => g_api_version
450 ,p_init_msg_list => g_false
451 ,x_return_status => l_return_status
452 ,x_msg_count => x_msg_count
453 ,x_msg_data => x_msg_data
454 ,p_source_id => lp_lrvv_rec.rate_set_version_id
455 ,p_source_type => 'LRS'
456 ,p_end_date => lp_lrvv_rec.effective_to_date);
457
458 -- write to log
459
460 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
461 okl_debug_pub.log_debug(fnd_log.level_statement
462 ,l_module
463 ,'Procedure okl_ecc_values_pvt.end_date_eligibility_criteria returned with status ' ||
464 l_return_status);
465 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
466 IF l_return_status = g_ret_sts_error THEN
467 RAISE okl_api.g_exception_error;
468 ELSIF l_return_status = g_ret_sts_unexp_error THEN
469 RAISE okl_api.g_exception_unexpected_error;
470 END IF;
471 END IF;
472 x_return_status := l_return_status;
473 okl_api.end_activity(x_msg_count => x_msg_count
474 ,x_msg_data => x_msg_data);
475
476 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
477 okl_debug_pub.log_debug(fnd_log.level_procedure
478 ,l_module
479 ,'end debug OKLPLRTB.pls.pls call update_lease_rate_set');
480 END IF;
481
482 EXCEPTION
483 WHEN okl_api.g_exception_error THEN
484 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
485 ,p_pkg_name => g_pkg_name
486 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
487 ,x_msg_count => x_msg_count
488 ,x_msg_data => x_msg_data
489 ,p_api_type => g_api_type);
490 WHEN okl_api.g_exception_unexpected_error THEN
491 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
492 ,p_pkg_name => g_pkg_name
493 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
494 ,x_msg_count => x_msg_count
495 ,x_msg_data => x_msg_data
496 ,p_api_type => g_api_type);
497 WHEN OTHERS THEN
498 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
499 ,p_pkg_name => g_pkg_name
500 ,p_exc_name => 'OTHERS'
501 ,x_msg_count => x_msg_count
502 ,x_msg_data => x_msg_data
503 ,p_api_type => g_api_type);
504 END update_lease_rate_set;
505
506 --p_lrv_id is version id of the lrs we want to version
507 --this method should be called only if previous version is existing for the lrs
508
509 FUNCTION get_newversion_effective_from(p_lrv_id number) RETURN date IS
510
511 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets
512
513 CURSOR lrs_lq_csr IS
514 SELECT max(qte.expected_start_date) start_date
515 FROM okl_lease_quotes_b qte
516 ,okl_fe_rate_set_versions lrv
517 WHERE qte.rate_card_id = lrv.rate_set_version_id
518 AND lrv.rate_set_version_id = p_lrv_id;
519
520 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets
521
522 CURSOR lrs_qq_csr IS
523 SELECT max(qte.expected_start_date) start_date
524 FROM okl_quick_quotes_b qte
525 ,okl_fe_rate_set_versions lrv
526 WHERE qte.rate_card_id = lrv.rate_set_version_id
527 AND lrv.rate_set_version_id = p_lrv_id;
528
529 --cursor to fetch previous version effective_from and to dates
530
531 CURSOR get_prev_ver_eff_to IS
532 SELECT effective_from_date
533 ,effective_to_date
534 FROM okl_fe_rate_set_versions
535 WHERE rate_set_version_id = p_lrv_id;
536
537 --cursor to fetch maximum of the start date of the eligibility criteria attached to
538 --lease rate set.
539
540 CURSOR get_elig_crit_start_date IS
541 SELECT max(effective_from_date)
542 FROM okl_fe_criteria_set ech
543 ,okl_fe_criteria ecl
544 WHERE ecl.criteria_set_id = ech.criteria_set_id
545 AND ech.source_id = p_lrv_id AND source_object_code = 'LRS';
546 l_prev_ver_eff_to date := NULL;
547 l_prev_ver_eff_from date := NULL;
548 l_calculated_eff_to date := NULL;
549 l_std_qte_eff_from date := NULL;
550 l_qk_qte_eff_from date := NULL;
551 l_ec_start_date date := NULL;
552 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.get_newversion_effective_from';
553 l_debug_enabled varchar2(10);
554 is_debug_procedure_on boolean;
555 is_debug_statement_on boolean;
556
557 BEGIN
558 l_debug_enabled := okl_debug_pub.check_log_enabled;
559 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
560 ,fnd_log.level_procedure);
561
562 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
563 okl_debug_pub.log_debug(fnd_log.level_procedure
564 ,l_module
565 ,'begin debug OKLRECCB.pls call get_newversion_effective_from');
566 END IF;
567
568 -- check for logging on STATEMENT level
569
570 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
571 ,fnd_log.level_statement);
572
573 --get the previous version effective from and to
574
575 OPEN get_prev_ver_eff_to;
576 FETCH get_prev_ver_eff_to INTO l_prev_ver_eff_from
577 ,l_prev_ver_eff_to ;
578 CLOSE get_prev_ver_eff_to;
579
580 --if previous version has effective to date then return new version effective from as this date+1
581
582 IF l_prev_ver_eff_to IS NOT NULL THEN
583 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
584 okl_debug_pub.log_debug(fnd_log.level_procedure
585 ,l_module
586 ,'end debug OKLPLRTB.pls.pls call get_newversion_effective_from');
587 END IF;
588 RETURN l_prev_ver_eff_to + 1;
589 END IF;
590
591 --get max of start dates of standard quotes referencing the lrs header of this version
592
593 OPEN lrs_lq_csr;
594 FETCH lrs_lq_csr INTO l_std_qte_eff_from ;
595 CLOSE lrs_lq_csr;
596
597 --get max of start dates of quick quotes referencing the lrs header of this version
598
599 OPEN lrs_qq_csr;
600 FETCH lrs_qq_csr INTO l_qk_qte_eff_from ;
601 CLOSE lrs_qq_csr;
602
603 --take maximum of the above two dates into l_calculated_eff_to
604
605 IF l_std_qte_eff_from IS NULL AND l_qk_qte_eff_from IS NULL THEN
606 l_calculated_eff_to := NULL;
607 ELSIF l_std_qte_eff_from IS NULL THEN
608 l_calculated_eff_to := l_qk_qte_eff_from;
609 ELSE
610 l_calculated_eff_to := l_std_qte_eff_from;
611 END IF;
612
613 --Get the maximum of the start dates of the eligibility criteria
614 --attached to lrs version.
615
616 OPEN get_elig_crit_start_date;
617 FETCH get_elig_crit_start_date INTO l_ec_start_date ;
618 CLOSE get_elig_crit_start_date;
619
620 IF l_ec_start_date IS NOT NULL THEN
621
622 --make l_calculated_eff_to as this maximum start date of ec, if l_calculated_eff_to
623 --is less than this maximum start date of ec
624
625 IF l_calculated_eff_to IS NULL THEN
626 l_calculated_eff_to := l_ec_start_date;
627 ELSIF l_ec_start_date > l_calculated_eff_to THEN
628 l_calculated_eff_to := l_ec_start_date;
629 END IF;
630 END IF;
631
632 --if calculated eff to date and prev version eff to date are null return prev ver eff from +1
633
634 IF l_calculated_eff_to IS NULL AND l_prev_ver_eff_to IS NULL THEN
635 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
636 okl_debug_pub.log_debug(fnd_log.level_procedure
637 ,l_module
638 ,'end debug OKLPLRTB.pls.pls call get_newversion_effective_from');
639 END IF;
640 RETURN l_prev_ver_eff_from + 1;
641 END IF;
642
643 --if calculated eff to date is not null and prev version eff to date is null return calculated eff to date +1
644
645 IF l_calculated_eff_to IS NOT NULL AND l_prev_ver_eff_to IS NULL THEN
646 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
647 okl_debug_pub.log_debug(fnd_log.level_procedure
648 ,l_module
649 ,'end debug OKLPLRTB.pls.pls call get_newversion_effective_from');
650 END IF;
651 RETURN l_calculated_eff_to + 1;
652 END IF;
653
654 END get_newversion_effective_from;
655
656 FUNCTION get_lrtv_rec(p_rate_set_id IN number
657 ,x_no_data_found OUT NOCOPY boolean) RETURN lrtv_rec_type IS
658
659 CURSOR lrtv_pk_csr(p_id IN number) IS
660 SELECT id
661 ,object_version_number
662 ,sfwt_flag
663 ,try_id
664 ,pdt_id
665 ,rate
666 ,frq_code
667 ,arrears_yn
668 ,start_date
669 ,end_date
670 ,name
671 ,description
672 ,created_by
673 ,creation_date
674 ,last_updated_by
675 ,last_update_date
676 ,last_update_login
677 ,attribute_category
678 ,attribute1
679 ,attribute2
680 ,attribute3
681 ,attribute4
682 ,attribute5
683 ,attribute6
684 ,attribute7
685 ,attribute8
686 ,attribute9
687 ,attribute10
688 ,attribute11
689 ,attribute12
690 ,attribute13
691 ,attribute14
692 ,attribute15
693 ,sts_code
694 ,org_id
695 ,currency_code
696 ,lrs_type_code
697 ,end_of_term_id
698 FROM okl_ls_rt_fctr_sets_v lrtv
699 WHERE id = p_id;
700 l_lrtv_rec lrtv_rec_type;
701 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.get_lrtv_rec';
702 l_debug_enabled varchar2(10);
703 is_debug_procedure_on boolean;
704 is_debug_statement_on boolean;
705
706 BEGIN
707 l_debug_enabled := okl_debug_pub.check_log_enabled;
708 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
709 ,fnd_log.level_procedure);
710
711 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
712 okl_debug_pub.log_debug(fnd_log.level_procedure
713 ,l_module
714 ,'begin debug OKLRECCB.pls call get_lrtv_rec');
715 END IF;
716
717 -- check for logging on STATEMENT level
718
719 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
720 ,fnd_log.level_statement);
721 x_no_data_found := true;
722 OPEN lrtv_pk_csr(p_rate_set_id);
723 FETCH lrtv_pk_csr INTO l_lrtv_rec.id
724 ,l_lrtv_rec.object_version_number
725 ,l_lrtv_rec.sfwt_flag
726 ,l_lrtv_rec.try_id
727 ,l_lrtv_rec.pdt_id
728 ,l_lrtv_rec.rate
729 ,l_lrtv_rec.frq_code
730 ,l_lrtv_rec.arrears_yn
731 ,l_lrtv_rec.start_date
732 ,l_lrtv_rec.end_date
733 ,l_lrtv_rec.name
734 ,l_lrtv_rec.description
735 ,l_lrtv_rec.created_by
736 ,l_lrtv_rec.creation_date
737 ,l_lrtv_rec.last_updated_by
738 ,l_lrtv_rec.last_update_date
739 ,l_lrtv_rec.last_update_login
740 ,l_lrtv_rec.attribute_category
741 ,l_lrtv_rec.attribute1
742 ,l_lrtv_rec.attribute2
743 ,l_lrtv_rec.attribute3
744 ,l_lrtv_rec.attribute4
745 ,l_lrtv_rec.attribute5
746 ,l_lrtv_rec.attribute6
747 ,l_lrtv_rec.attribute7
748 ,l_lrtv_rec.attribute8
749 ,l_lrtv_rec.attribute9
750 ,l_lrtv_rec.attribute10
751 ,l_lrtv_rec.attribute11
752 ,l_lrtv_rec.attribute12
753 ,l_lrtv_rec.attribute13
754 ,l_lrtv_rec.attribute14
755 ,l_lrtv_rec.attribute15
756 ,l_lrtv_rec.sts_code
757 ,l_lrtv_rec.org_id
758 ,l_lrtv_rec.currency_code
759 ,l_lrtv_rec.lrs_type_code
760 ,l_lrtv_rec.end_of_term_id ;
761 x_no_data_found := lrtv_pk_csr%NOTFOUND;
762 CLOSE lrtv_pk_csr;
763
764 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
765 okl_debug_pub.log_debug(fnd_log.level_procedure
766 ,l_module
767 ,'end debug OKLPLRTB.pls.pls call get_lrtv_rec');
768 END IF;
769 RETURN(l_lrtv_rec);
770 END get_lrtv_rec;
771
772 FUNCTION get_lrvv_rec(p_lrvv_id IN number
773 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_lrvv_rec IS
774
775 CURSOR lrvv_pk_csr(p_id IN number) IS
776 SELECT rate_set_version_id
777 ,object_version_number
778 ,arrears_yn
779 ,effective_from_date
780 ,effective_to_date
781 ,rate_set_id
782 ,end_of_term_ver_id
783 ,std_rate_tmpl_ver_id
784 ,adj_mat_version_id
785 ,version_number
786 ,lrs_rate
787 ,rate_tolerance
788 ,deferred_pmts
789 ,advance_pmts
790 ,sts_code
791 ,created_by
792 ,creation_date
793 ,last_updated_by
794 ,last_update_date
795 ,last_update_login
796 ,attribute_category
797 ,attribute1
798 ,attribute2
799 ,attribute3
800 ,attribute4
801 ,attribute5
802 ,attribute6
803 ,attribute7
804 ,attribute8
805 ,attribute9
806 ,attribute10
807 ,attribute11
808 ,attribute12
809 ,attribute13
810 ,attribute14
811 ,attribute15
812 FROM okl_fe_rate_set_versions_v
813 WHERE rate_set_version_id = p_id;
814 l_lrvv_pk lrvv_pk_csr%ROWTYPE;
815 l_lrvv_rec okl_lrvv_rec;
816 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.get_lrvv_rec';
817 l_debug_enabled varchar2(10);
818 is_debug_procedure_on boolean;
819 is_debug_statement_on boolean;
820
821 BEGIN
822 l_debug_enabled := okl_debug_pub.check_log_enabled;
823 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
824 ,fnd_log.level_procedure);
825
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 ,'begin debug OKLRECCB.pls call get_lrvv_rec');
830 END IF;
831
832 -- check for logging on STATEMENT level
833
834 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
835 ,fnd_log.level_statement);
836 x_no_data_found := true;
837
838 --Get current data base values
839
840 OPEN lrvv_pk_csr(p_lrvv_id);
841 FETCH lrvv_pk_csr INTO l_lrvv_rec.rate_set_version_id
842 ,l_lrvv_rec.object_version_number
843 ,l_lrvv_rec.arrears_yn
844 ,l_lrvv_rec.effective_from_date
845 ,l_lrvv_rec.effective_to_date
846 ,l_lrvv_rec.rate_set_id
847 ,l_lrvv_rec.end_of_term_ver_id
848 ,l_lrvv_rec.std_rate_tmpl_ver_id
849 ,l_lrvv_rec.adj_mat_version_id
850 ,l_lrvv_rec.version_number
851 ,l_lrvv_rec.lrs_rate
852 ,l_lrvv_rec.rate_tolerance
853 ,l_lrvv_rec.deferred_pmts
854 ,l_lrvv_rec.advance_pmts
855 ,l_lrvv_rec.sts_code
856 ,l_lrvv_rec.created_by
857 ,l_lrvv_rec.creation_date
858 ,l_lrvv_rec.last_updated_by
859 ,l_lrvv_rec.last_update_date
860 ,l_lrvv_rec.last_update_login
861 ,l_lrvv_rec.attribute_category
862 ,l_lrvv_rec.attribute1
863 ,l_lrvv_rec.attribute2
864 ,l_lrvv_rec.attribute3
865 ,l_lrvv_rec.attribute4
866 ,l_lrvv_rec.attribute5
867 ,l_lrvv_rec.attribute6
868 ,l_lrvv_rec.attribute7
869 ,l_lrvv_rec.attribute8
870 ,l_lrvv_rec.attribute9
871 ,l_lrvv_rec.attribute10
872 ,l_lrvv_rec.attribute11
873 ,l_lrvv_rec.attribute12
874 ,l_lrvv_rec.attribute13
875 ,l_lrvv_rec.attribute14
876 ,l_lrvv_rec.attribute15 ;
877 x_no_data_found := lrvv_pk_csr%NOTFOUND;
878 CLOSE lrvv_pk_csr;
879
880 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
881 okl_debug_pub.log_debug(fnd_log.level_procedure
882 ,l_module
883 ,'end debug OKLPLRTB.pls.pls call get_lrvv_rec');
884 END IF;
885 RETURN(l_lrvv_rec);
886 END get_lrvv_rec;
887
888 PROCEDURE version_lease_rate_set(p_api_version IN number
889 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
890 ,x_return_status OUT NOCOPY varchar2
891 ,x_msg_count OUT NOCOPY number
892 ,x_msg_data OUT NOCOPY varchar2
893 ,p_lrtv_rec IN lrtv_rec_type
894 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
895 ,p_lrvv_rec IN okl_lrvv_rec
896 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
897
898 CURSOR get_prev_ver_id(p_lrt_id IN number
899 ,p_version_number IN varchar2) IS
900 SELECT rate_set_version_id
901 FROM okl_fe_rate_set_versions
902 WHERE rate_set_id = p_lrt_id AND version_number = p_version_number;
903
904 CURSOR get_eot_version(p_eot_id number
905 ,p_eff_from IN date) IS
906 SELECT end_of_term_ver_id
907 FROM okl_fe_eo_term_vers
908 WHERE end_of_term_id = p_eot_id
909 AND p_eff_from BETWEEN effective_from_date AND nvl(effective_to_date, p_eff_from + 1)
910 AND sts_code = 'ACTIVE';
911 l_ver_no varchar2(30);
912 l_lrv_id_prev number;
913 lp_lrvv_rec okl_lrvv_rec;
914 lp_lrtv_rec lrtv_rec_type;
915 l_lrvv_rec_prev okl_lrvv_rec;
916 x_lrvv_rec_prev okl_lrvv_rec;
917 l_no_data_found boolean;
918 l_eot_ver_id number;
919 l_new_ver_eff_from date;
920 l_api_name CONSTANT varchar2(30) := 'version_lrs';
921 l_api_version CONSTANT number := 1.0;
922 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
923 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.version_lease_rate_set';
924 l_debug_enabled varchar2(10);
925 is_debug_procedure_on boolean;
926 is_debug_statement_on boolean;
927
928 BEGIN
929 l_debug_enabled := okl_debug_pub.check_log_enabled;
930 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
931 ,fnd_log.level_procedure);
932
933 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
934 okl_debug_pub.log_debug(fnd_log.level_procedure
935 ,l_module
936 ,'begin debug OKLRECCB.pls call version_lease_rate_set');
937 END IF;
938
939 -- check for logging on STATEMENT level
940
941 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
942 ,fnd_log.level_statement);
943
944 -- call START_ACTIVITY to create savepoint, check compatibility
945 -- and initialize message list
946
947 l_return_status := okl_api.start_activity(p_api_name => l_api_name
948 ,p_pkg_name => g_pkg_name
949 ,p_init_msg_list => p_init_msg_list
950 ,l_api_version => l_api_version
951 ,p_api_version => p_api_version
952 ,p_api_type => g_api_type
953 ,x_return_status => x_return_status);
954
955 -- check if activity started successfully
956
957 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
958 RAISE okl_api.g_exception_unexpected_error;
959 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
960 RAISE okl_api.g_exception_error;
961 END IF;
962 lp_lrvv_rec := p_lrvv_rec;
963 lp_lrtv_rec := p_lrtv_rec;
964
965 --if lrs type = 'Advance' advance payments should be present
966
967 IF lp_lrtv_rec.lrs_type_code = 'ADVANCE' THEN
968 IF lp_lrvv_rec.advance_pmts IS NULL OR lp_lrvv_rec.advance_pmts = okl_api.g_miss_num
969 OR lp_lrvv_rec.advance_pmts <= 0 THEN
970 okl_api.set_message(p_app_name => g_app_name
971 ,p_msg_name => 'OKL_ADVANCE_PAYMENTS_MANDATORY');
972 RAISE okl_api.g_exception_error;
973 END IF;
974 lp_lrvv_rec.deferred_pmts := 0;
975 END IF;
976
977 --if lrs type = 'Deferred' deferred payments should be present
978
979 IF lp_lrtv_rec.lrs_type_code = 'DEFERRED' THEN
980 IF lp_lrvv_rec.deferred_pmts IS NULL OR lp_lrvv_rec.deferred_pmts = okl_api.g_miss_num
981 OR lp_lrvv_rec.deferred_pmts <= 0 THEN
982 okl_api.set_message(p_app_name => g_app_name
983 ,p_msg_name => 'OKL_DEFERRED_PMTS_MANDATORY');
984 RAISE okl_api.g_exception_error;
985 END IF;
986 lp_lrvv_rec.advance_pmts := 0;
987 END IF;
988
989 --if lrs type = 'LEVEL' deferred payments should be present
990
991 IF lp_lrtv_rec.lrs_type_code = 'LEVEL' THEN
992 lp_lrvv_rec.deferred_pmts := 0;
993 lp_lrvv_rec.advance_pmts := 0;
994 END IF;
995
996 --if no Eot version is available raise error
997
998 OPEN get_eot_version(lp_lrtv_rec.end_of_term_id
999 ,lp_lrvv_rec.effective_from_date);
1000 FETCH get_eot_version INTO l_eot_ver_id ;
1001 CLOSE get_eot_version;
1002
1003 IF l_eot_ver_id IS NULL THEN
1004 okl_api.set_message(p_app_name => okl_api.g_app_name
1005 ,p_msg_name => 'OKL_NO_EOT_VERSION_AVAILABLE'
1006 ,p_token1 => 'EFFECTIVE_FROM'
1007 ,p_token1_value => lp_lrvv_rec.effective_from_date);
1008 RAISE okl_api.g_exception_error;
1009 END IF;
1010
1011 --set the available eot version id
1012
1013 lp_lrvv_rec.end_of_term_ver_id := l_eot_ver_id;
1014
1015 -- validate that eff_from of new version is greater than max referenced
1016 -- quote effective from and previous version effective to (if present)
1017 --get previous ver id
1018
1019
1020 l_ver_no := to_char(to_number(lp_lrvv_rec.version_number) - 1);
1021
1022 OPEN get_prev_ver_id(lp_lrtv_rec.id, l_ver_no);
1023 FETCH get_prev_ver_id INTO l_lrv_id_prev ;
1024 CLOSE get_prev_ver_id;
1025 l_new_ver_eff_from := get_newversion_effective_from(l_lrv_id_prev);
1026
1027 IF lp_lrvv_rec.effective_from_date < l_new_ver_eff_from THEN
1028 okl_api.set_message(p_app_name => g_app_name
1029 ,p_msg_name => 'OKL_INVALID_EFF_FROM'
1030 ,p_token1 => 'MIN_DATE'
1031 ,p_token1_value => l_new_ver_eff_from);
1032 RAISE okl_api.g_exception_error;
1033 END IF;
1034
1035 --set header status = UNDER_REVISION and ver status =NEW
1036
1037 lp_lrtv_rec.sts_code := 'UNDER_REVISION';
1038 lp_lrvv_rec.sts_code := 'NEW';
1039
1040 --now call the update header to update the status code
1041
1042 okl_lrt_pvt.update_row(p_api_version => g_api_version
1043 ,p_init_msg_list => g_false
1044 ,x_return_status => l_return_status
1045 ,x_msg_count => x_msg_count
1046 ,x_msg_data => x_msg_data
1047 ,p_lrtv_rec => lp_lrtv_rec
1048 ,x_lrtv_rec => x_lrtv_rec);
1049
1050 IF l_return_status = g_ret_sts_error THEN
1051 RAISE okl_api.g_exception_error;
1052 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1053 RAISE okl_api.g_exception_unexpected_error;
1054 END IF;
1055 lp_lrvv_rec.rate_set_id := x_lrtv_rec.id;
1056
1057 --call insert row for creating new version
1058
1059 okl_lrv_pvt.insert_row(p_api_version => g_api_version
1060 ,p_init_msg_list => g_false
1061 ,x_return_status => l_return_status
1062 ,x_msg_count => x_msg_count
1063 ,x_msg_data => x_msg_data
1064 ,p_lrvv_rec => lp_lrvv_rec
1065 ,x_lrvv_rec => x_lrvv_rec);
1066
1067 IF l_return_status = g_ret_sts_error THEN
1068 RAISE okl_api.g_exception_error;
1069 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1070 RAISE okl_api.g_exception_unexpected_error;
1071 END IF;
1072 x_return_status := l_return_status;
1073 okl_api.end_activity(x_msg_count => x_msg_count
1074 ,x_msg_data => x_msg_data);
1075
1076 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1077 okl_debug_pub.log_debug(fnd_log.level_procedure
1078 ,l_module
1079 ,'end debug OKLPLRTB.pls.pls call version_lease_rate_set');
1080 END IF;
1081
1082 EXCEPTION
1083 WHEN okl_api.g_exception_error THEN
1084 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1085 ,p_pkg_name => g_pkg_name
1086 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1087 ,x_msg_count => x_msg_count
1088 ,x_msg_data => x_msg_data
1089 ,p_api_type => g_api_type);
1090 WHEN okl_api.g_exception_unexpected_error THEN
1091 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1092 ,p_pkg_name => g_pkg_name
1093 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1094 ,x_msg_count => x_msg_count
1095 ,x_msg_data => x_msg_data
1096 ,p_api_type => g_api_type);
1097 WHEN OTHERS THEN
1098 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1099 ,p_pkg_name => g_pkg_name
1100 ,p_exc_name => 'OTHERS'
1101 ,x_msg_count => x_msg_count
1102 ,x_msg_data => x_msg_data
1103 ,p_api_type => g_api_type);
1104 END version_lease_rate_set;
1105
1106 PROCEDURE submit_lease_rate_set(p_api_version IN number
1107 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1108 ,x_return_status OUT NOCOPY varchar2
1109 ,x_msg_count OUT NOCOPY number
1110 ,x_msg_data OUT NOCOPY varchar2
1111 ,p_rate_set_version_id IN okl_fe_rate_set_versions.rate_set_version_id%TYPE) IS
1112
1113 CURSOR get_prev_ver_id(p_lrt_id IN number
1114 ,p_version_number IN varchar2) IS
1115 SELECT rate_set_version_id
1116 FROM okl_fe_rate_set_versions
1117 WHERE rate_set_id = p_lrt_id AND version_number = p_version_number;
1118
1119 CURSOR get_rate_set_id(p_lrv_id IN number) IS
1120 SELECT rate_set_id
1121 ,effective_from_date
1122 FROM okl_fe_rate_set_versions
1123 WHERE rate_set_version_id = p_rate_set_version_id;
1124
1125 CURSOR get_ver_no(p_lrv_id number) IS
1126 SELECT version_number
1127 FROM okl_fe_rate_set_versions
1128 WHERE rate_set_version_id = p_lrv_id;
1129 l_ver_no varchar2(30);
1130 lp_lrvv_rec okl_lrvv_rec;
1131 x_lrvv_rec okl_lrvv_rec;
1132 l_prev_ver_no varchar2(30);
1133 l_lrv_id_prev number := NULL;
1134 l_rate_set_id number := NULL;
1135 l_eff_from date;
1136 l_new_ver_eff_from date;
1137 l_prev_lrvv_rec okl_lrvv_rec;
1138 l_parameter_list wf_parameter_list_t;
1139 l_event_name wf_events.name%TYPE;
1140 l_approval_path varchar2(30) DEFAULT 'NONE';
1141 l_api_name CONSTANT varchar2(30) := 'submit_lrs';
1142 l_api_version CONSTANT number := 1.0;
1143 x_no_data_found boolean;
1144 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1145 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.submit_lease_rate_set';
1146 l_debug_enabled varchar2(10);
1147 is_debug_procedure_on boolean;
1148 is_debug_statement_on boolean;
1149
1150 BEGIN
1151 l_debug_enabled := okl_debug_pub.check_log_enabled;
1152 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1153 ,fnd_log.level_procedure);
1154
1155 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1156 okl_debug_pub.log_debug(fnd_log.level_procedure
1157 ,l_module
1158 ,'begin debug OKLRECCB.pls call submit_lease_rate_set');
1159 END IF;
1160
1161 -- check for logging on STATEMENT level
1162
1163 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1164 ,fnd_log.level_statement);
1165
1166 -- call START_ACTIVITY to create savepoint, check compatibility
1167 -- and initialize message list
1168
1169 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1170 ,p_pkg_name => g_pkg_name
1171 ,p_init_msg_list => p_init_msg_list
1172 ,l_api_version => l_api_version
1173 ,p_api_version => p_api_version
1174 ,p_api_type => g_api_type
1175 ,x_return_status => x_return_status);
1176
1177 -- check if activity started successfully
1178
1179 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1180 RAISE okl_api.g_exception_unexpected_error;
1181 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1182 RAISE okl_api.g_exception_error;
1183 END IF;
1184
1185 --get the header id from the version id
1186
1187 OPEN get_rate_set_id(p_rate_set_version_id);
1188 FETCH get_rate_set_id INTO l_rate_set_id
1189 ,l_eff_from ;
1190 CLOSE get_rate_set_id;
1191
1192 --get the version number of version to be submitted
1193
1194 OPEN get_ver_no(p_rate_set_version_id);
1195 FETCH get_ver_no INTO l_ver_no ;
1196 CLOSE get_ver_no;
1197
1198 --if this is the first version then dont do the effective_from validation
1199
1200 l_prev_ver_no := to_char(to_number(l_ver_no) - 1);
1201 OPEN get_prev_ver_id(l_rate_set_id, l_prev_ver_no);
1202 FETCH get_prev_ver_id INTO l_lrv_id_prev ;
1203 CLOSE get_prev_ver_id;
1204
1205 --if prev ver id is null then this is the first version, so dont do eff from validation
1206
1207 IF l_lrv_id_prev IS NOT NULL THEN
1208
1209 -- if effective from entered by user is less than max of prev version eff_to(if present)
1210 --and calculated eff_from then throw error
1211
1212 l_new_ver_eff_from := get_newversion_effective_from(l_lrv_id_prev);
1213 IF l_eff_from < l_new_ver_eff_from THEN
1214 okl_api.set_message(p_app_name => g_app_name
1215 ,p_msg_name => 'OKL_INVALID_EFF_FROM'
1216 ,p_token1 => 'DATE'
1217 ,p_token1_value => l_new_ver_eff_from);
1218 RAISE okl_api.g_exception_error;
1219 END IF;
1220 END IF;
1221
1222 lp_lrvv_rec := get_lrvv_rec(p_rate_set_version_id, x_no_data_found);
1223
1224 IF (x_no_data_found = true) THEN
1225
1226 okl_api.set_message(p_app_name => g_app_name
1227 ,p_msg_name => 'OKL_NO_VERSION_REC_FOUND');
1228 RAISE okl_api.g_exception_error;
1229 END IF;
1230
1231 --Make version status submitted if it is NEW else raise error
1232 IF lp_lrvv_rec.sts_code = 'NEW' THEN
1233
1234 lp_lrvv_rec.sts_code := 'SUBMITTED';
1235 --call update row for lp_lrvv_rec
1236
1237 okl_lrv_pvt.update_row(p_api_version => g_api_version
1238 ,p_init_msg_list => g_false
1239 ,x_return_status => l_return_status
1240 ,x_msg_count => x_msg_count
1241 ,x_msg_data => x_msg_data
1242 ,p_lrvv_rec => lp_lrvv_rec
1243 ,x_lrvv_rec => x_lrvv_rec);
1244
1245 -- write to log
1246
1247 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1248 okl_debug_pub.log_debug(fnd_log.level_statement
1249 ,l_module
1250 ,'Procedure okl_lrv_pvt.update_row returned with status ' ||
1251 l_return_status);
1252 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1253
1254 IF l_return_status = g_ret_sts_error THEN
1255 RAISE okl_api.g_exception_error;
1256 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1257 RAISE okl_api.g_exception_unexpected_error;
1258 END IF;
1259
1260 --read profile for approval path
1261 l_approval_path := fnd_profile.value('OKL_PE_APPROVAL_PROCESS');
1262
1263 IF nvl(l_approval_path, 'NONE') = 'NONE' THEN
1264 okl_lease_rate_sets_pvt.activate_lease_rate_set(p_api_version => p_api_version
1265 ,p_init_msg_list => p_init_msg_list
1266 ,x_return_status => l_return_status
1267 ,x_msg_count => x_msg_count
1268 ,x_msg_data => x_msg_data
1269 ,p_rate_set_version_id => p_rate_set_version_id);
1270 IF l_return_status = okl_api.g_ret_sts_error THEN
1271 RAISE okl_api.g_exception_error;
1272 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
1273 RAISE okl_api.g_exception_unexpected_error;
1274 END IF;
1275 ELSIF nvl(l_approval_path, 'NONE') = 'WF' OR nvl(l_approval_path
1276 ,'NONE') = 'AME' THEN
1277
1278 --raise workflow submit event
1279
1280 l_event_name := g_wf_evt_lrs_pending;
1281
1282 wf_event.addparametertolist(g_wf_lrs_version_id
1283 ,p_rate_set_version_id
1284 ,l_parameter_list);
1285 --added by akrangan
1286 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1287
1288
1289 okl_wf_pvt.raise_event(p_api_version => p_api_version
1290 ,p_init_msg_list => p_init_msg_list
1291 ,x_return_status => l_return_status
1292 ,x_msg_count => x_msg_count
1293 ,x_msg_data => x_msg_data
1294 ,p_event_name => l_event_name
1295 ,p_parameters => l_parameter_list);
1296
1297 IF l_return_status = g_ret_sts_error THEN
1298 RAISE okl_api.g_exception_error;
1299 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1300 RAISE okl_api.g_exception_unexpected_error;
1301 END IF;
1302 END IF;
1303 ELSE
1304 okl_api.set_message(p_app_name => g_app_name
1305 ,p_msg_name => 'OKL_INVALID_LRS_STATUS');
1306 RAISE okl_api.g_exception_error;
1307 END IF;
1308 x_return_status := l_return_status;
1309
1310 okl_api.end_activity(x_msg_count => x_msg_count
1311 ,x_msg_data => x_msg_data);
1312
1313 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1314 okl_debug_pub.log_debug(fnd_log.level_procedure
1315 ,l_module
1316 ,'end debug OKLPLRTB.pls.pls call submit_lease_rate_set');
1317 END IF;
1318
1319 EXCEPTION
1320 WHEN okl_api.g_exception_error THEN
1321 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1322 ,p_pkg_name => g_pkg_name
1323 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1324 ,x_msg_count => x_msg_count
1325 ,x_msg_data => x_msg_data
1326 ,p_api_type => g_api_type);
1327 WHEN okl_api.g_exception_unexpected_error THEN
1328 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1329 ,p_pkg_name => g_pkg_name
1330 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1331 ,x_msg_count => x_msg_count
1332 ,x_msg_data => x_msg_data
1333 ,p_api_type => g_api_type);
1334 WHEN OTHERS THEN
1335 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1336 ,p_pkg_name => g_pkg_name
1337 ,p_exc_name => 'OTHERS'
1338 ,x_msg_count => x_msg_count
1339 ,x_msg_data => x_msg_data
1340 ,p_api_type => g_api_type);
1341 END submit_lease_rate_set;
1342
1343 PROCEDURE activate_lease_rate_set(p_api_version IN number
1344 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1345 ,x_return_status OUT NOCOPY varchar2
1346 ,x_msg_count OUT NOCOPY number
1347 ,x_msg_data OUT NOCOPY varchar2
1348 ,p_rate_set_version_id IN okl_fe_rate_set_versions.rate_set_version_id%TYPE) IS
1349 lp_lrvv_rec okl_lrvv_rec;
1350 l_prev_lrvv_rec okl_lrvv_rec;
1351 x_lrvv_rec okl_lrvv_rec;
1352 lp_lrtv_rec lrtv_rec_type;
1353 x_lrtv_rec lrtv_rec_type;
1354 l_lrv_id_prev number := NULL;
1355 l_ver_no varchar2(30);
1356 l_new_ver_eff_from date;
1357 l_calculated_eff_to date;
1358 l_dummy varchar2(1) := '?';
1359 x_no_data_found boolean;
1360 l_rate_set_id number;
1361 l_lrs_rate number;
1362
1363 CURSOR get_rate_set_id(p_lrv_id IN number) IS
1364 SELECT rate_set_id
1365 FROM okl_fe_rate_set_versions
1366 WHERE rate_set_version_id = p_lrv_id;
1367
1368 CURSOR get_prev_ver_id(p_lrt_id IN number
1369 ,p_version_number IN varchar2) IS
1370 SELECT rate_set_version_id
1371 FROM okl_fe_rate_set_versions
1372 WHERE rate_set_id = p_lrt_id AND version_number = p_version_number;
1373
1374 CURSOR get_interest_rate(p_lrv_id IN number) IS
1375 SELECT nvl(lrs_rate,standard_rate) interest_rate
1376 FROM okl_fe_rate_set_versions lrsv
1377 WHERE lrsv.rate_set_version_id = p_lrv_id;
1378 l_ech_rec okl_ech_rec;
1379 l_ecl_tbl okl_ecl_tbl;
1380 l_ecv_tbl okl_ecv_tbl;
1381 lx_ech_rec okl_ech_rec;
1382 lx_ecl_tbl okl_ecl_tbl;
1383 lx_ecv_tbl okl_ecv_tbl;
1384 l_api_name CONSTANT varchar2(30) := 'activate_lrs';
1385 l_api_version CONSTANT number := 1.0;
1386 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1387 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.activate_lease_rate_set';
1388 l_debug_enabled varchar2(10);
1389 is_debug_procedure_on boolean;
1390 is_debug_statement_on boolean;
1391
1392 BEGIN
1393 l_debug_enabled := okl_debug_pub.check_log_enabled;
1394 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1395 ,fnd_log.level_procedure);
1396
1397 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1398 okl_debug_pub.log_debug(fnd_log.level_procedure
1399 ,l_module
1400 ,'begin debug OKLPLRTB.pls call activate_lease_rate_set');
1401 END IF;
1402
1403 -- check for logging on STATEMENT level
1404
1405 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1406 ,fnd_log.level_statement);
1407
1408 -- call START_ACTIVITY to create savepoint, check compatibility
1409 -- and initialize message list
1410
1411 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1412 ,p_pkg_name => g_pkg_name
1413 ,p_init_msg_list => p_init_msg_list
1414 ,l_api_version => l_api_version
1415 ,p_api_version => p_api_version
1416 ,p_api_type => g_api_type
1417 ,x_return_status => x_return_status);
1418
1419 -- check if activity started successfully
1420
1421 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1422 RAISE okl_api.g_exception_unexpected_error;
1423 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1424 RAISE okl_api.g_exception_error;
1425 END IF;
1426
1427 --get the header id from the version id
1428
1429 OPEN get_rate_set_id(p_rate_set_version_id);
1430 FETCH get_rate_set_id INTO l_rate_set_id ;
1431 CLOSE get_rate_set_id;
1432
1433 --get the header and version records
1434
1435 lp_lrtv_rec := get_lrtv_rec(l_rate_set_id, x_no_data_found);
1436
1437 IF (x_no_data_found = true) THEN
1438
1439 okl_api.set_message(p_app_name => g_app_name
1440 ,p_msg_name => 'OKL_NO_HEADER_REC_FOUND');
1441 RAISE okl_api.g_exception_error;
1442 END IF;
1443 lp_lrvv_rec := get_lrvv_rec(p_rate_set_version_id, x_no_data_found);
1444
1445 IF (x_no_data_found = true) THEN
1446
1447 okl_api.set_message(p_app_name => g_app_name
1448 ,p_msg_name => 'OKL_NO_VERSION_REC_FOUND');
1449 RAISE okl_api.g_exception_error;
1450 END IF;
1451
1452 --1.Make version status active
1453
1454 lp_lrvv_rec.sts_code := 'ACTIVE';
1455
1456 --2.put header eff to date as eff to of this version
1457 --if eff to is nulkl then make header end date as G_MISS_DATE so that it
1458 --gets null out in TAPI
1459
1460 IF lp_lrvv_rec.effective_to_date IS NULL THEN
1461 lp_lrtv_rec.end_date := okl_api.g_miss_date;
1462 ELSE
1463 lp_lrtv_rec.end_date := lp_lrvv_rec.effective_to_date;
1464 END IF;
1465
1466 --3.if this is the first version then dont do the effective_from validation
1467
1468 l_ver_no := to_char(to_number(lp_lrvv_rec.version_number) - 1);
1469 OPEN get_prev_ver_id(lp_lrtv_rec.id, l_ver_no);
1470 FETCH get_prev_ver_id INTO l_lrv_id_prev ;
1471 CLOSE get_prev_ver_id;
1472
1473 --if prev ver id is null then this is the first version, so dont do eff from validation
1474
1475 IF l_lrv_id_prev IS NOT NULL THEN
1476
1477 -- if effective from entered by user is less than max of prev version eff_to(if present)
1478 --and calculated eff_from then throw error
1479
1480 IF lp_lrvv_rec.effective_from_date < get_newversion_effective_from(l_lrv_id_prev) THEN
1481 okl_api.set_message(p_app_name => g_app_name
1482 ,p_msg_name => 'OKL_INVALID_EFF_FROM'
1483 ,p_token1 => 'DATE'
1484 ,p_token1_value => lp_lrvv_rec.effective_from_date);
1485 RAISE okl_api.g_exception_error;
1486 END IF;
1487
1488 --Put effective to date of previous version as new ver eff from -1
1489
1490 l_prev_lrvv_rec := get_lrvv_rec(l_lrv_id_prev, x_no_data_found);
1491 IF (x_no_data_found = true) THEN
1492 RAISE okl_api.g_exception_unexpected_error;
1493 END IF;
1494 l_prev_lrvv_rec.effective_to_date := lp_lrvv_rec.effective_from_date - 1;
1495 END IF;
1496
1497 --make header status active
1498
1499 lp_lrtv_rec.sts_code := 'ACTIVE';
1500
1501 --get the interest rate for which the lease rate factors are calculated and put in version rec
1502
1503 OPEN get_interest_rate(lp_lrvv_rec.rate_set_version_id);
1504 FETCH get_interest_rate INTO l_lrs_rate ;
1505 CLOSE get_interest_rate;
1506
1507 --as the latest version is getting activated put this interest rate in header rec also
1508
1509 lp_lrtv_rec.rate := l_lrs_rate;
1510
1511 --if srt is present on the version to be activated copy ec from the srt to lrs
1512
1513 IF lp_lrvv_rec.std_rate_tmpl_ver_id IS NOT NULL THEN
1514
1515 --get the ec attached to srt
1516
1517
1518 okl_ecc_values_pvt.get_eligibility_criteria(p_api_version => g_api_version
1519 ,p_init_msg_list => g_false
1520 ,x_return_status => l_return_status
1521 ,x_msg_count => x_msg_count
1522 ,x_msg_data => x_msg_data
1523 ,p_source_id => lp_lrvv_rec.std_rate_tmpl_ver_id
1524 ,p_source_type => 'SRT'
1525 ,p_eff_from => lp_lrvv_rec.effective_from_date
1526 ,p_eff_to => lp_lrvv_rec.effective_to_date
1527 ,x_ech_rec => l_ech_rec
1528 ,x_ecl_tbl => l_ecl_tbl
1529 ,x_ecv_tbl => l_ecv_tbl);
1530
1531 -- write to log
1532
1533 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1534 okl_debug_pub.log_debug(fnd_log.level_statement
1535 ,l_module
1536 ,'Procedure okl_ecc_values_pvt.get_eligibility_criteria returned with status ' ||
1537 l_return_status);
1538 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1539 IF l_return_status = g_ret_sts_error THEN
1540 RAISE okl_api.g_exception_error;
1541 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1542 RAISE okl_api.g_exception_unexpected_error;
1543 END IF;
1544
1545 --delete existing EC on LRS if any
1546
1547
1548 okl_ecc_values_pvt.delete_eligibility_criteria(p_api_version => p_api_version
1549 ,p_init_msg_list => okl_api.g_false
1550 ,x_return_status => l_return_status
1551 ,x_msg_count => x_msg_count
1552 ,x_msg_data => x_msg_data
1553 ,p_source_id => lp_lrvv_rec.rate_set_version_id
1554 ,p_source_type => 'LRS');
1555
1556 -- write to log
1557
1558 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1559 okl_debug_pub.log_debug(fnd_log.level_statement
1560 ,l_module
1561 ,'Procedure okl_ecc_values_pvt.delete_eligibility_criteria returned with status ' ||
1562 l_return_status);
1563 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1564 IF l_return_status = g_ret_sts_error THEN
1565 RAISE okl_api.g_exception_error;
1566 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1567 RAISE okl_api.g_exception_unexpected_error;
1568 END IF;
1569
1570 --insert the ec only if some ec exists on srt
1571
1572 IF l_ecl_tbl.COUNT > 0 THEN
1573
1574 --make the fetched ec as ec of lrs
1575
1576 l_ech_rec.source_id := lp_lrvv_rec.rate_set_version_id;
1577 l_ech_rec.source_object_code := 'LRS';
1578
1579 --prepare ec data to get inserted
1580
1581
1582 l_ech_rec.criteria_set_id := NULL;
1583
1584 FOR i IN l_ecl_tbl.FIRST..l_ecl_tbl.LAST LOOP
1585 l_ecl_tbl(i).is_new_flag := 'Y';
1586 END LOOP;
1587
1588 FOR i IN l_ecv_tbl.FIRST..l_ecv_tbl.LAST LOOP
1589 l_ecv_tbl(i).criterion_value_id := NULL;
1590 l_ecv_tbl(i).validate_record := 'N';
1591 END LOOP;
1592
1593 --call handle_eligibility_criteria
1594
1595 okl_ecc_values_pvt.handle_eligibility_criteria(p_api_version => g_api_version
1596 ,p_init_msg_list => g_false
1597 ,x_return_status => l_return_status
1598 ,x_msg_count => x_msg_count
1599 ,x_msg_data => x_msg_data
1600 ,p_source_eff_from => lp_lrvv_rec.effective_from_date
1601 ,p_source_eff_to => lp_lrvv_rec.effective_to_date
1602 ,x_ech_rec => lx_ech_rec
1603 ,x_ecl_tbl => lx_ecl_tbl
1604 ,x_ecv_tbl => lx_ecv_tbl
1605 ,p_ech_rec => l_ech_rec
1606 ,p_ecl_tbl => l_ecl_tbl
1607 ,p_ecv_tbl => l_ecv_tbl);
1608
1609 -- write to log
1610
1611 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1612 okl_debug_pub.log_debug(fnd_log.level_statement
1613 ,l_module
1614 ,'Procedure okl_ecc_values_pvt.handle_eligibility_criteria returned with status ' ||
1615 l_return_status);
1616 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1617 IF l_return_status = g_ret_sts_error THEN
1618 RAISE okl_api.g_exception_error;
1619 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1620 RAISE okl_api.g_exception_unexpected_error;
1621 END IF;
1622 END IF;
1623 END IF;
1624
1625 IF l_lrv_id_prev IS NOT NULL THEN
1626
1627 --call update_row for l_prev_lrvv_rec
1628
1629
1630 okl_lrv_pvt.update_row(p_api_version => g_api_version
1631 ,p_init_msg_list => g_false
1632 ,x_return_status => l_return_status
1633 ,x_msg_count => x_msg_count
1634 ,x_msg_data => x_msg_data
1635 ,p_lrvv_rec => l_prev_lrvv_rec
1636 ,x_lrvv_rec => x_lrvv_rec);
1637
1638 -- write to log
1639
1640 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1641 okl_debug_pub.log_debug(fnd_log.level_statement
1642 ,l_module
1643 ,'Procedure okl_lrv_pvt.update_row returned with status ' ||
1644 l_return_status);
1645 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1646 IF l_return_status = g_ret_sts_error THEN
1647 RAISE okl_api.g_exception_error;
1648 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1649 RAISE okl_api.g_exception_unexpected_error;
1650 END IF;
1651
1652 --Eligibility Criteria attached to previous version should be end dated
1653 --with the end date of the previous version. if the end date of eligibility
1654 --criteria is null or greater than previous version end date, then the
1655 --api adjusts the end date to the end date of previous lrs version.
1656
1657 okl_ecc_values_pvt.end_date_eligibility_criteria(p_api_version => g_api_version
1658 ,p_init_msg_list => g_false
1659 ,x_return_status => l_return_status
1660 ,x_msg_count => x_msg_count
1661 ,x_msg_data => x_msg_data
1662 ,p_source_id => l_lrv_id_prev
1663 ,p_source_type => 'LRS'
1664 ,p_end_date => l_prev_lrvv_rec.effective_to_date);
1665
1666 -- write to log
1667
1668 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1669 okl_debug_pub.log_debug(fnd_log.level_statement
1670 ,l_module
1671 ,'Procedure okl_ecc_values_pvt.end_date_eligibility_criteria returned with status ' ||
1672 l_return_status);
1673 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1674 IF l_return_status = g_ret_sts_error THEN
1675 RAISE okl_api.g_exception_error;
1676 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1677 RAISE okl_api.g_exception_unexpected_error;
1678 END IF;
1679 END IF;
1680
1681 -- call update row for lp_lrtv_rec
1682 --update the header
1683
1684 okl_lrt_pvt.update_row(p_api_version => g_api_version
1685 ,p_init_msg_list => g_false
1686 ,x_return_status => l_return_status
1687 ,x_msg_count => x_msg_count
1688 ,x_msg_data => x_msg_data
1689 ,p_lrtv_rec => lp_lrtv_rec
1690 ,x_lrtv_rec => x_lrtv_rec);
1691
1692 -- write to log
1693
1694 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1695 okl_debug_pub.log_debug(fnd_log.level_statement
1696 ,l_module
1697 ,'Procedure okl_lrt_pvt.update_row returned with status ' ||
1698 l_return_status);
1699 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1700
1701 IF l_return_status = g_ret_sts_error THEN
1702 RAISE okl_api.g_exception_error;
1703 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1704 RAISE okl_api.g_exception_unexpected_error;
1705 END IF;
1706
1707 --call update row for lp_lrvv_rec
1708
1709 okl_lrv_pvt.update_row(p_api_version => g_api_version
1710 ,p_init_msg_list => g_false
1711 ,x_return_status => l_return_status
1712 ,x_msg_count => x_msg_count
1713 ,x_msg_data => x_msg_data
1714 ,p_lrvv_rec => lp_lrvv_rec
1715 ,x_lrvv_rec => x_lrvv_rec);
1716
1717 -- write to log
1718
1719 IF (nvl(l_debug_enabled, 'N') = 'Y' AND is_debug_statement_on) THEN
1720 okl_debug_pub.log_debug(fnd_log.level_statement
1721 ,l_module
1722 ,'Procedure okl_lrv_pvt.update_row returned with status ' ||
1723 l_return_status);
1724 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1725
1726 IF l_return_status = g_ret_sts_error THEN
1727 RAISE okl_api.g_exception_error;
1728 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1729 RAISE okl_api.g_exception_unexpected_error;
1730 END IF;
1731 x_return_status := l_return_status;
1732 okl_api.end_activity(x_msg_count => x_msg_count
1733 ,x_msg_data => x_msg_data);
1734
1735 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1736 okl_debug_pub.log_debug(fnd_log.level_procedure
1737 ,l_module
1738 ,'end debug OKLPLRTB.pls.pls call activate_lease_rate_set');
1739 END IF;
1740
1741 EXCEPTION
1742 WHEN okl_api.g_exception_error THEN
1743 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1744 ,p_pkg_name => g_pkg_name
1745 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1746 ,x_msg_count => x_msg_count
1747 ,x_msg_data => x_msg_data
1748 ,p_api_type => g_api_type);
1749 WHEN okl_api.g_exception_unexpected_error THEN
1750 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1751 ,p_pkg_name => g_pkg_name
1752 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1753 ,x_msg_count => x_msg_count
1754 ,x_msg_data => x_msg_data
1755 ,p_api_type => g_api_type);
1756 WHEN OTHERS THEN
1757 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1758 ,p_pkg_name => g_pkg_name
1759 ,p_exc_name => 'OTHERS'
1760 ,x_msg_count => x_msg_count
1761 ,x_msg_data => x_msg_data
1762 ,p_api_type => g_api_type);
1763 END activate_lease_rate_set;
1764
1765 --createAndGenFctrs
1766 --updateAndGenFctrs
1767 --versionAndGenFctrs
1768 --createGenFctrAndSubmit
1769 --updateGenFctrAndSubmit
1770 --versionGenFctrAndSubmit
1771
1772 PROCEDURE create_lrs_gen_lrf(p_api_version IN number
1773 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1774 ,x_return_status OUT NOCOPY varchar2
1775 ,x_msg_count OUT NOCOPY number
1776 ,x_msg_data OUT NOCOPY varchar2
1777 ,p_lrtv_rec IN lrtv_rec_type
1778 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
1779 ,p_lrvv_rec IN okl_lrvv_rec
1780 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
1781 lp_lrvv_rec okl_lrvv_rec;
1782 lp_lrtv_rec lrtv_rec_type;
1783 l_api_name CONSTANT varchar2(30) := 'create_lrs_gen';
1784 l_api_version CONSTANT number := 1.0;
1785 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1786 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.create_lrs_gen_lrf';
1787 l_debug_enabled varchar2(10);
1788 is_debug_procedure_on boolean;
1789 is_debug_statement_on boolean;
1790
1791 BEGIN
1792 l_debug_enabled := okl_debug_pub.check_log_enabled;
1793 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1794 ,fnd_log.level_procedure);
1795
1796 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1797 okl_debug_pub.log_debug(fnd_log.level_procedure
1798 ,l_module
1799 ,'begin debug OKLRECCB.pls call create_lrs_gen_lrf');
1800 END IF;
1801
1802 -- check for logging on STATEMENT level
1803
1804 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1805 ,fnd_log.level_statement);
1806
1807 -- call START_ACTIVITY to create savepoint, check compatibility
1808 -- and initialize message list
1809
1810 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1811 ,p_pkg_name => g_pkg_name
1812 ,p_init_msg_list => p_init_msg_list
1813 ,l_api_version => l_api_version
1814 ,p_api_version => p_api_version
1815 ,p_api_type => g_api_type
1816 ,x_return_status => x_return_status);
1817
1818 -- check if activity started successfully
1819
1820 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1821 RAISE okl_api.g_exception_unexpected_error;
1822 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1823 RAISE okl_api.g_exception_error;
1824 END IF;
1825 lp_lrvv_rec := p_lrvv_rec;
1826 lp_lrtv_rec := p_lrtv_rec;
1827
1828 --create the lease rate set
1829
1830 create_lease_rate_set(p_api_version => g_api_version
1831 ,p_init_msg_list => g_false
1832 ,x_return_status => l_return_status
1833 ,x_msg_count => x_msg_count
1834 ,x_msg_data => x_msg_data
1835 ,p_lrtv_rec => lp_lrtv_rec
1836 ,x_lrtv_rec => x_lrtv_rec
1837 ,p_lrvv_rec => lp_lrvv_rec
1838 ,x_lrvv_rec => x_lrvv_rec);
1839
1840 IF l_return_status = g_ret_sts_error THEN
1841 RAISE okl_api.g_exception_error;
1842 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1843 RAISE okl_api.g_exception_unexpected_error;
1844 END IF;
1845
1846 --generate lease rate factors
1847
1848 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
1849 ,p_init_msg_list => g_false
1850 ,x_return_status => l_return_status
1851 ,x_msg_count => x_msg_count
1852 ,x_msg_data => x_msg_data
1853 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
1854
1855 IF l_return_status = g_ret_sts_error THEN
1856 RAISE okl_api.g_exception_error;
1857 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1858 RAISE okl_api.g_exception_unexpected_error;
1859 END IF;
1860 x_return_status := l_return_status;
1861 okl_api.end_activity(x_msg_count => x_msg_count
1862 ,x_msg_data => x_msg_data);
1863
1864 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1865 okl_debug_pub.log_debug(fnd_log.level_procedure
1866 ,l_module
1867 ,'end debug OKLPLRTB.pls.pls call create_lrs_gen_lrf');
1868 END IF;
1869
1870 EXCEPTION
1871 WHEN okl_api.g_exception_error THEN
1872 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1873 ,p_pkg_name => g_pkg_name
1874 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1875 ,x_msg_count => x_msg_count
1876 ,x_msg_data => x_msg_data
1877 ,p_api_type => g_api_type);
1878 WHEN okl_api.g_exception_unexpected_error THEN
1879 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1880 ,p_pkg_name => g_pkg_name
1881 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1882 ,x_msg_count => x_msg_count
1883 ,x_msg_data => x_msg_data
1884 ,p_api_type => g_api_type);
1885 WHEN OTHERS THEN
1886 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1887 ,p_pkg_name => g_pkg_name
1888 ,p_exc_name => 'OTHERS'
1889 ,x_msg_count => x_msg_count
1890 ,x_msg_data => x_msg_data
1891 ,p_api_type => g_api_type);
1892 END create_lrs_gen_lrf;
1893
1894 PROCEDURE update_lrs_gen_lrf(p_api_version IN number
1895 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1896 ,x_return_status OUT NOCOPY varchar2
1897 ,x_msg_count OUT NOCOPY number
1898 ,x_msg_data OUT NOCOPY varchar2
1899 ,p_lrtv_rec IN lrtv_rec_type
1900 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
1901 ,p_lrvv_rec IN okl_lrvv_rec
1902 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
1903 lp_lrvv_rec okl_lrvv_rec;
1904 lp_lrtv_rec lrtv_rec_type;
1905 l_api_name CONSTANT varchar2(30) := 'update_lrs_gen';
1906 l_api_version CONSTANT number := 1.0;
1907 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1908 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.update_lrs_gen_lrf';
1909 l_debug_enabled varchar2(10);
1910 is_debug_procedure_on boolean;
1911 is_debug_statement_on boolean;
1912
1913 BEGIN
1914 l_debug_enabled := okl_debug_pub.check_log_enabled;
1915 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1916 ,fnd_log.level_procedure);
1917
1918 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1919 okl_debug_pub.log_debug(fnd_log.level_procedure
1920 ,l_module
1921 ,'begin debug OKLRECCB.pls call update_lrs_gen_lrf');
1922 END IF;
1923
1924 -- check for logging on STATEMENT level
1925
1926 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1927 ,fnd_log.level_statement);
1928
1929 -- call START_ACTIVITY to create savepoint, check compatibility
1930 -- and initialize message list
1931
1932 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1933 ,p_pkg_name => g_pkg_name
1934 ,p_init_msg_list => p_init_msg_list
1935 ,l_api_version => l_api_version
1936 ,p_api_version => p_api_version
1937 ,p_api_type => g_api_type
1938 ,x_return_status => x_return_status);
1939
1940 -- check if activity started successfully
1941
1942 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1943 RAISE okl_api.g_exception_unexpected_error;
1944 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1945 RAISE okl_api.g_exception_error;
1946 END IF;
1947 lp_lrvv_rec := p_lrvv_rec;
1948 lp_lrtv_rec := p_lrtv_rec;
1949
1950 --update the lease rate set
1951
1952 update_lease_rate_set(p_api_version => g_api_version
1953 ,p_init_msg_list => g_false
1954 ,x_return_status => l_return_status
1955 ,x_msg_count => x_msg_count
1956 ,x_msg_data => x_msg_data
1957 ,p_lrtv_rec => lp_lrtv_rec
1958 ,x_lrtv_rec => x_lrtv_rec
1959 ,p_lrvv_rec => lp_lrvv_rec
1960 ,x_lrvv_rec => x_lrvv_rec);
1961
1962 IF l_return_status = g_ret_sts_error THEN
1963 RAISE okl_api.g_exception_error;
1964 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1965 RAISE okl_api.g_exception_unexpected_error;
1966 END IF;
1967
1968 --generate lease rate factors
1969
1970 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
1971 ,p_init_msg_list => g_false
1972 ,x_return_status => l_return_status
1973 ,x_msg_count => x_msg_count
1974 ,x_msg_data => x_msg_data
1975 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
1976
1977 IF l_return_status = g_ret_sts_error THEN
1978 RAISE okl_api.g_exception_error;
1979 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1980 RAISE okl_api.g_exception_unexpected_error;
1981 END IF;
1982 x_return_status := l_return_status;
1983 okl_api.end_activity(x_msg_count => x_msg_count
1984 ,x_msg_data => x_msg_data);
1985
1986 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1987 okl_debug_pub.log_debug(fnd_log.level_procedure
1988 ,l_module
1989 ,'end debug OKLPLRTB.pls.pls call update_lrs_gen_lrf');
1990 END IF;
1991
1992 EXCEPTION
1993 WHEN okl_api.g_exception_error THEN
1994 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1995 ,p_pkg_name => g_pkg_name
1996 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1997 ,x_msg_count => x_msg_count
1998 ,x_msg_data => x_msg_data
1999 ,p_api_type => g_api_type);
2000 WHEN okl_api.g_exception_unexpected_error THEN
2001 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2002 ,p_pkg_name => g_pkg_name
2003 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2004 ,x_msg_count => x_msg_count
2005 ,x_msg_data => x_msg_data
2006 ,p_api_type => g_api_type);
2007 WHEN OTHERS THEN
2008 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2009 ,p_pkg_name => g_pkg_name
2010 ,p_exc_name => 'OTHERS'
2011 ,x_msg_count => x_msg_count
2012 ,x_msg_data => x_msg_data
2013 ,p_api_type => g_api_type);
2014 END update_lrs_gen_lrf;
2015
2016 PROCEDURE version_lrs_gen_lrf(p_api_version IN number
2017 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2018 ,x_return_status OUT NOCOPY varchar2
2019 ,x_msg_count OUT NOCOPY number
2020 ,x_msg_data OUT NOCOPY varchar2
2021 ,p_lrtv_rec IN lrtv_rec_type
2022 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
2023 ,p_lrvv_rec IN okl_lrvv_rec
2024 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
2025 lp_lrvv_rec okl_lrvv_rec;
2026 lp_lrtv_rec lrtv_rec_type;
2027 l_api_name CONSTANT varchar2(30) := 'version_lrs_gen';
2028 l_api_version CONSTANT number := 1.0;
2029 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2030 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.version_lrs_gen_lrf';
2031 l_debug_enabled varchar2(10);
2032 is_debug_procedure_on boolean;
2033 is_debug_statement_on boolean;
2034
2035 BEGIN
2036 l_debug_enabled := okl_debug_pub.check_log_enabled;
2037 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2038 ,fnd_log.level_procedure);
2039
2040 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2041 okl_debug_pub.log_debug(fnd_log.level_procedure
2042 ,l_module
2043 ,'begin debug OKLRECCB.pls call version_lrs_gen_lrf');
2044 END IF;
2045
2046 -- check for logging on STATEMENT level
2047
2048 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2049 ,fnd_log.level_statement);
2050
2051 -- call START_ACTIVITY to create savepoint, check compatibility
2052 -- and initialize message list
2053
2054 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2055 ,p_pkg_name => g_pkg_name
2056 ,p_init_msg_list => p_init_msg_list
2057 ,l_api_version => l_api_version
2058 ,p_api_version => p_api_version
2059 ,p_api_type => g_api_type
2060 ,x_return_status => x_return_status);
2061
2062 -- check if activity started successfully
2063
2064 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2065 RAISE okl_api.g_exception_unexpected_error;
2066 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2067 RAISE okl_api.g_exception_error;
2068 END IF;
2069 lp_lrvv_rec := p_lrvv_rec;
2070 lp_lrtv_rec := p_lrtv_rec;
2071
2072 --version the lease rate set
2073
2074 version_lease_rate_set(p_api_version => g_api_version
2075 ,p_init_msg_list => g_false
2076 ,x_return_status => l_return_status
2077 ,x_msg_count => x_msg_count
2078 ,x_msg_data => x_msg_data
2079 ,p_lrtv_rec => lp_lrtv_rec
2080 ,x_lrtv_rec => x_lrtv_rec
2081 ,p_lrvv_rec => lp_lrvv_rec
2082 ,x_lrvv_rec => x_lrvv_rec);
2083
2084 IF l_return_status = g_ret_sts_error THEN
2085 RAISE okl_api.g_exception_error;
2086 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2087 RAISE okl_api.g_exception_unexpected_error;
2088 END IF;
2089
2090 --generate lease rate factors
2091
2092 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
2093 ,p_init_msg_list => g_false
2094 ,x_return_status => l_return_status
2095 ,x_msg_count => x_msg_count
2096 ,x_msg_data => x_msg_data
2097 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2098
2099 IF l_return_status = g_ret_sts_error THEN
2100 RAISE okl_api.g_exception_error;
2101 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2102 RAISE okl_api.g_exception_unexpected_error;
2103 END IF;
2104 x_return_status := l_return_status;
2105 okl_api.end_activity(x_msg_count => x_msg_count
2106 ,x_msg_data => x_msg_data);
2107
2108 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2109 okl_debug_pub.log_debug(fnd_log.level_procedure
2110 ,l_module
2111 ,'end debug OKLPLRTB.pls.pls call version_lrs_gen_lrf');
2112 END IF;
2113
2114 EXCEPTION
2115 WHEN okl_api.g_exception_error THEN
2116 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2117 ,p_pkg_name => g_pkg_name
2118 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2119 ,x_msg_count => x_msg_count
2120 ,x_msg_data => x_msg_data
2121 ,p_api_type => g_api_type);
2122 WHEN okl_api.g_exception_unexpected_error THEN
2123 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2124 ,p_pkg_name => g_pkg_name
2125 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2126 ,x_msg_count => x_msg_count
2127 ,x_msg_data => x_msg_data
2128 ,p_api_type => g_api_type);
2129 WHEN OTHERS THEN
2130 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2131 ,p_pkg_name => g_pkg_name
2132 ,p_exc_name => 'OTHERS'
2133 ,x_msg_count => x_msg_count
2134 ,x_msg_data => x_msg_data
2135 ,p_api_type => g_api_type);
2136 END version_lrs_gen_lrf;
2137
2138 PROCEDURE create_lrs_gen_lrf_submit(p_api_version IN number
2139 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2140 ,x_return_status OUT NOCOPY varchar2
2141 ,x_msg_count OUT NOCOPY number
2142 ,x_msg_data OUT NOCOPY varchar2
2143 ,p_lrtv_rec IN lrtv_rec_type
2144 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
2145 ,p_lrvv_rec IN okl_lrvv_rec
2146 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
2147 lp_lrvv_rec okl_lrvv_rec;
2148 lp_lrtv_rec lrtv_rec_type;
2149 l_api_name CONSTANT varchar2(30) := 'crt_lrs_gen_sub';
2150 l_api_version CONSTANT number := 1.0;
2151 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2152 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.create_lrs_gen_lrf_submit';
2153 l_debug_enabled varchar2(10);
2154 is_debug_procedure_on boolean;
2155 is_debug_statement_on boolean;
2156
2157 BEGIN
2158 l_debug_enabled := okl_debug_pub.check_log_enabled;
2159 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2160 ,fnd_log.level_procedure);
2161
2162 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2163 okl_debug_pub.log_debug(fnd_log.level_procedure
2164 ,l_module
2165 ,'begin debug OKLRECCB.pls call create_lrs_gen_lrf_submit');
2166 END IF;
2167
2168 -- check for logging on STATEMENT level
2169
2170 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2171 ,fnd_log.level_statement);
2172
2173 -- call START_ACTIVITY to create savepoint, check compatibility
2174 -- and initialize message list
2175
2176 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2177 ,p_pkg_name => g_pkg_name
2178 ,p_init_msg_list => p_init_msg_list
2179 ,l_api_version => l_api_version
2180 ,p_api_version => p_api_version
2181 ,p_api_type => g_api_type
2182 ,x_return_status => x_return_status);
2183
2184 -- check if activity started successfully
2185
2186 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2187 RAISE okl_api.g_exception_unexpected_error;
2188 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2189 RAISE okl_api.g_exception_error;
2190 END IF;
2191 lp_lrvv_rec := p_lrvv_rec;
2192 lp_lrtv_rec := p_lrtv_rec;
2193
2194 --create the lease rate set
2195
2196 create_lease_rate_set(p_api_version => g_api_version
2197 ,p_init_msg_list => g_false
2198 ,x_return_status => l_return_status
2199 ,x_msg_count => x_msg_count
2200 ,x_msg_data => x_msg_data
2201 ,p_lrtv_rec => lp_lrtv_rec
2202 ,x_lrtv_rec => x_lrtv_rec
2203 ,p_lrvv_rec => lp_lrvv_rec
2204 ,x_lrvv_rec => x_lrvv_rec);
2205
2206 IF l_return_status = g_ret_sts_error THEN
2207 RAISE okl_api.g_exception_error;
2208 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2209 RAISE okl_api.g_exception_unexpected_error;
2210 END IF;
2211
2212 --generate lease rate factors
2213
2214 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
2215 ,p_init_msg_list => g_false
2216 ,x_return_status => l_return_status
2217 ,x_msg_count => x_msg_count
2218 ,x_msg_data => x_msg_data
2219 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2220
2221 IF l_return_status = g_ret_sts_error THEN
2222 RAISE okl_api.g_exception_error;
2223 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2224 RAISE okl_api.g_exception_unexpected_error;
2225 END IF;
2226
2227 --submit the lrs
2228
2229 submit_lease_rate_set(p_api_version => g_api_version
2230 ,p_init_msg_list => g_false
2231 ,x_return_status => l_return_status
2232 ,x_msg_count => x_msg_count
2233 ,x_msg_data => x_msg_data
2234 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2235
2236 IF l_return_status = g_ret_sts_error THEN
2237 RAISE okl_api.g_exception_error;
2238 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2239 RAISE okl_api.g_exception_unexpected_error;
2240 END IF;
2241 x_return_status := l_return_status;
2242 okl_api.end_activity(x_msg_count => x_msg_count
2243 ,x_msg_data => x_msg_data);
2244
2245 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2246 okl_debug_pub.log_debug(fnd_log.level_procedure
2247 ,l_module
2248 ,'end debug OKLPLRTB.pls.pls call create_lrs_gen_lrf_submit');
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN okl_api.g_exception_error THEN
2253 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2254 ,p_pkg_name => g_pkg_name
2255 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2256 ,x_msg_count => x_msg_count
2257 ,x_msg_data => x_msg_data
2258 ,p_api_type => g_api_type);
2259 WHEN okl_api.g_exception_unexpected_error THEN
2260 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2261 ,p_pkg_name => g_pkg_name
2262 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2263 ,x_msg_count => x_msg_count
2264 ,x_msg_data => x_msg_data
2265 ,p_api_type => g_api_type);
2266 WHEN OTHERS THEN
2267 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2268 ,p_pkg_name => g_pkg_name
2269 ,p_exc_name => 'OTHERS'
2270 ,x_msg_count => x_msg_count
2271 ,x_msg_data => x_msg_data
2272 ,p_api_type => g_api_type);
2273 END create_lrs_gen_lrf_submit;
2274
2275 PROCEDURE update_lrs_gen_lrf_submit(p_api_version IN number
2276 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2277 ,x_return_status OUT NOCOPY varchar2
2278 ,x_msg_count OUT NOCOPY number
2279 ,x_msg_data OUT NOCOPY varchar2
2280 ,p_lrtv_rec IN lrtv_rec_type
2281 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
2282 ,p_lrvv_rec IN okl_lrvv_rec
2283 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
2284 lp_lrvv_rec okl_lrvv_rec;
2285 lp_lrtv_rec lrtv_rec_type;
2286 l_api_name CONSTANT varchar2(30) := 'upd_lrs_gen_sub';
2287 l_api_version CONSTANT number := 1.0;
2288 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2289 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.update_lrs_gen_lrf_submit';
2290 l_debug_enabled varchar2(10);
2291 is_debug_procedure_on boolean;
2292 is_debug_statement_on boolean;
2293
2294 BEGIN
2295 l_debug_enabled := okl_debug_pub.check_log_enabled;
2296 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2297 ,fnd_log.level_procedure);
2298
2299 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2300 okl_debug_pub.log_debug(fnd_log.level_procedure
2301 ,l_module
2302 ,'begin debug OKLRECCB.pls call update_lrs_gen_lrf_submit');
2303 END IF;
2304
2305 -- check for logging on STATEMENT level
2306
2307 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2308 ,fnd_log.level_statement);
2309
2310 -- call START_ACTIVITY to create savepoint, check compatibility
2311 -- and initialize message list
2312
2313 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2314 ,p_pkg_name => g_pkg_name
2315 ,p_init_msg_list => p_init_msg_list
2316 ,l_api_version => l_api_version
2317 ,p_api_version => p_api_version
2318 ,p_api_type => g_api_type
2319 ,x_return_status => x_return_status);
2320
2321 -- check if activity started successfully
2322
2323 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2324 RAISE okl_api.g_exception_unexpected_error;
2325 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2326 RAISE okl_api.g_exception_error;
2327 END IF;
2328 lp_lrvv_rec := p_lrvv_rec;
2329 lp_lrtv_rec := p_lrtv_rec;
2330
2331 --update the lease rate set
2332
2333 update_lease_rate_set(p_api_version => g_api_version
2334 ,p_init_msg_list => g_false
2335 ,x_return_status => l_return_status
2336 ,x_msg_count => x_msg_count
2337 ,x_msg_data => x_msg_data
2338 ,p_lrtv_rec => lp_lrtv_rec
2339 ,x_lrtv_rec => x_lrtv_rec
2340 ,p_lrvv_rec => lp_lrvv_rec
2341 ,x_lrvv_rec => x_lrvv_rec);
2342
2343 IF l_return_status = g_ret_sts_error THEN
2344 RAISE okl_api.g_exception_error;
2345 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2346 RAISE okl_api.g_exception_unexpected_error;
2347 END IF;
2348
2349 --generate lease rate factors
2350
2351 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
2352 ,p_init_msg_list => g_false
2353 ,x_return_status => l_return_status
2354 ,x_msg_count => x_msg_count
2355 ,x_msg_data => x_msg_data
2356 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2357
2358 IF l_return_status = g_ret_sts_error THEN
2359 RAISE okl_api.g_exception_error;
2360 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2361 RAISE okl_api.g_exception_unexpected_error;
2362 END IF;
2363
2364 --submit the lrs
2365
2366 submit_lease_rate_set(p_api_version => g_api_version
2367 ,p_init_msg_list => g_false
2368 ,x_return_status => l_return_status
2369 ,x_msg_count => x_msg_count
2370 ,x_msg_data => x_msg_data
2371 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2372
2373 IF l_return_status = g_ret_sts_error THEN
2374 RAISE okl_api.g_exception_error;
2375 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2376 RAISE okl_api.g_exception_unexpected_error;
2377 END IF;
2378 x_return_status := l_return_status;
2379 okl_api.end_activity(x_msg_count => x_msg_count
2380 ,x_msg_data => x_msg_data);
2381
2382 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2383 okl_debug_pub.log_debug(fnd_log.level_procedure
2384 ,l_module
2385 ,'end debug OKLPLRTB.pls.pls call update_lrs_gen_lrf_submit');
2386 END IF;
2387
2388 EXCEPTION
2389 WHEN okl_api.g_exception_error THEN
2390 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2391 ,p_pkg_name => g_pkg_name
2392 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2393 ,x_msg_count => x_msg_count
2394 ,x_msg_data => x_msg_data
2395 ,p_api_type => g_api_type);
2396 WHEN okl_api.g_exception_unexpected_error THEN
2397 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2398 ,p_pkg_name => g_pkg_name
2399 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2400 ,x_msg_count => x_msg_count
2401 ,x_msg_data => x_msg_data
2402 ,p_api_type => g_api_type);
2403 WHEN OTHERS THEN
2404 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2405 ,p_pkg_name => g_pkg_name
2406 ,p_exc_name => 'OTHERS'
2407 ,x_msg_count => x_msg_count
2408 ,x_msg_data => x_msg_data
2409 ,p_api_type => g_api_type);
2410 END update_lrs_gen_lrf_submit;
2411
2412 PROCEDURE version_lrs_gen_lrf_submit(p_api_version IN number
2413 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2414 ,x_return_status OUT NOCOPY varchar2
2415 ,x_msg_count OUT NOCOPY number
2416 ,x_msg_data OUT NOCOPY varchar2
2417 ,p_lrtv_rec IN lrtv_rec_type
2418 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type
2419 ,p_lrvv_rec IN okl_lrvv_rec
2420 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
2421 lp_lrvv_rec okl_lrvv_rec;
2422 lp_lrtv_rec lrtv_rec_type;
2423 l_api_name CONSTANT varchar2(30) := 'HANDLE_ELIG_CRITERIA';
2424 l_api_version CONSTANT number := 1.0;
2425 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2426 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.version_lrs_gen_lrf_submit';
2427 l_debug_enabled varchar2(10);
2428 is_debug_procedure_on boolean;
2429 is_debug_statement_on boolean;
2430
2431 BEGIN
2432 l_debug_enabled := okl_debug_pub.check_log_enabled;
2433 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2434 ,fnd_log.level_procedure);
2435
2436 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2437 okl_debug_pub.log_debug(fnd_log.level_procedure
2438 ,l_module
2439 ,'begin debug OKLRECCB.pls call version_lrs_gen_lrf_submit');
2440 END IF;
2441
2442 -- check for logging on STATEMENT level
2443
2444 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2445 ,fnd_log.level_statement);
2446
2447 -- call START_ACTIVITY to create savepoint, check compatibility
2448 -- and initialize message list
2449
2450 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2451 ,p_pkg_name => g_pkg_name
2452 ,p_init_msg_list => p_init_msg_list
2453 ,l_api_version => l_api_version
2454 ,p_api_version => p_api_version
2455 ,p_api_type => g_api_type
2456 ,x_return_status => x_return_status);
2457
2458 -- check if activity started successfully
2459
2460 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2461 RAISE okl_api.g_exception_unexpected_error;
2462 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2463 RAISE okl_api.g_exception_error;
2464 END IF;
2465 lp_lrvv_rec := p_lrvv_rec;
2466 lp_lrtv_rec := p_lrtv_rec;
2467
2468 --version the lease rate set
2469
2470 version_lease_rate_set(p_api_version => g_api_version
2471 ,p_init_msg_list => g_false
2472 ,x_return_status => l_return_status
2473 ,x_msg_count => x_msg_count
2474 ,x_msg_data => x_msg_data
2475 ,p_lrtv_rec => lp_lrtv_rec
2476 ,x_lrtv_rec => x_lrtv_rec
2477 ,p_lrvv_rec => lp_lrvv_rec
2478 ,x_lrvv_rec => x_lrvv_rec);
2479
2480 IF l_return_status = g_ret_sts_error THEN
2481 RAISE okl_api.g_exception_error;
2482 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2483 RAISE okl_api.g_exception_unexpected_error;
2484 END IF;
2485
2486 --generate lease rate factors
2487
2488 okl_lrf_generate_pvt.generate_lease_rate_factors(p_api_version => g_api_version
2489 ,p_init_msg_list => g_false
2490 ,x_return_status => l_return_status
2491 ,x_msg_count => x_msg_count
2492 ,x_msg_data => x_msg_data
2493 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2494
2495 IF l_return_status = g_ret_sts_error THEN
2496 RAISE okl_api.g_exception_error;
2497 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2498 RAISE okl_api.g_exception_unexpected_error;
2499 END IF;
2500
2501 --submit the lrs
2502
2503 submit_lease_rate_set(p_api_version => g_api_version
2504 ,p_init_msg_list => g_false
2505 ,x_return_status => l_return_status
2506 ,x_msg_count => x_msg_count
2507 ,x_msg_data => x_msg_data
2508 ,p_rate_set_version_id => x_lrvv_rec.rate_set_version_id);
2509
2510 IF l_return_status = g_ret_sts_error THEN
2511 RAISE okl_api.g_exception_error;
2512 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2513 RAISE okl_api.g_exception_unexpected_error;
2514 END IF;
2515 x_return_status := l_return_status;
2516 okl_api.end_activity(x_msg_count => x_msg_count
2517 ,x_msg_data => x_msg_data);
2518
2519 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2520 okl_debug_pub.log_debug(fnd_log.level_procedure
2521 ,l_module
2522 ,'end debug OKLPLRTB.pls.pls call version_lrs_gen_lrf_submit');
2523 END IF;
2524
2525 EXCEPTION
2526 WHEN okl_api.g_exception_error THEN
2527 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2528 ,p_pkg_name => g_pkg_name
2529 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2530 ,x_msg_count => x_msg_count
2531 ,x_msg_data => x_msg_data
2532 ,p_api_type => g_api_type);
2533 WHEN okl_api.g_exception_unexpected_error THEN
2534 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2535 ,p_pkg_name => g_pkg_name
2536 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2537 ,x_msg_count => x_msg_count
2538 ,x_msg_data => x_msg_data
2539 ,p_api_type => g_api_type);
2540 WHEN OTHERS THEN
2541 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2542 ,p_pkg_name => g_pkg_name
2543 ,p_exc_name => 'OTHERS'
2544 ,x_msg_count => x_msg_count
2545 ,x_msg_data => x_msg_data
2546 ,p_api_type => g_api_type);
2547 END version_lrs_gen_lrf_submit;
2548
2549 PROCEDURE validate_eot_version(p_api_version IN number
2550 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2551 ,x_return_status OUT NOCOPY varchar2
2552 ,x_msg_count OUT NOCOPY number
2553 ,x_msg_data OUT NOCOPY varchar2
2554 ,p_eot_id IN number
2555 ,p_effective_from IN date
2556 ,p_eot_ver_id IN number
2557 ,p_rate_set_version_id IN number
2558 ,x_eot_ver_id OUT NOCOPY number
2559 ,x_version_number OUT NOCOPY varchar2) IS
2560
2561 CURSOR get_eot_version IS
2562 SELECT end_of_term_ver_id
2563 ,version_number
2564 FROM okl_fe_eo_term_vers
2565 WHERE end_of_term_id = p_eot_id
2566 AND p_effective_from BETWEEN effective_from_date AND nvl(effective_to_date, p_effective_from + 1)
2567 AND sts_code = 'ACTIVE';
2568 l_eot_ver_id number;
2569 l_version_number varchar2(40);
2570 l_api_name CONSTANT varchar2(30) := 'get_eot_ver';
2571 l_api_version CONSTANT number := 1.0;
2572 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2573 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.validate_eot_version';
2574 l_debug_enabled varchar2(10);
2575 is_debug_procedure_on boolean;
2576 is_debug_statement_on boolean;
2577
2578 BEGIN
2579 l_debug_enabled := okl_debug_pub.check_log_enabled;
2580 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2581 ,fnd_log.level_procedure);
2582
2583 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2584 okl_debug_pub.log_debug(fnd_log.level_procedure
2585 ,l_module
2586 ,'begin debug OKLRECCB.pls call validate_eot_version');
2587 END IF;
2588
2589 -- check for logging on STATEMENT level
2590
2591 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2592 ,fnd_log.level_statement);
2593
2594 -- call START_ACTIVITY to create savepoint, check compatibility
2595 -- and initialize message list
2596
2597 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2598 ,p_pkg_name => g_pkg_name
2599 ,p_init_msg_list => p_init_msg_list
2600 ,l_api_version => l_api_version
2601 ,p_api_version => p_api_version
2602 ,p_api_type => g_api_type
2603 ,x_return_status => x_return_status);
2604
2605 -- check if activity started successfully
2606
2607 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2608 RAISE okl_api.g_exception_unexpected_error;
2609 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2610 RAISE okl_api.g_exception_error;
2611 END IF;
2612
2613 --if no Eot version is available raise error
2614
2615 OPEN get_eot_version;
2616 FETCH get_eot_version INTO l_eot_ver_id
2617 ,l_version_number ;
2618 CLOSE get_eot_version;
2619
2620 IF l_eot_ver_id IS NULL THEN
2621 okl_api.set_message(p_app_name => okl_api.g_app_name
2622 ,p_msg_name => 'OKL_NO_EOT_VERSION_AVAILABLE'
2623 ,p_token1 => 'EFFECTIVE_FROM'
2624 ,p_token1_value => p_effective_from);
2625 RAISE okl_api.g_exception_error;
2626 END IF;
2627
2628 IF p_eot_ver_id <> l_eot_ver_id AND p_rate_set_version_id <> NULL THEN
2629
2630
2631 --delete lrf lines and corressponding levels from the okl_ls_rt_fctr_ents_b and okl_fe_rate_set_levels table for this lrs version
2632
2633 okl_lease_rate_factors_pvt.delete_lease_rate_factors(p_api_version => p_api_version
2634 ,p_init_msg_list => okl_api.g_false
2635 ,x_return_status => x_return_status
2636 ,x_msg_count => x_msg_count
2637 ,x_msg_data => x_msg_data
2638 ,p_lrv_id => p_rate_set_version_id);
2639 IF x_return_status = g_ret_sts_error THEN
2640 RAISE okl_api.g_exception_error;
2641 ELSIF x_return_status = g_ret_sts_unexp_error THEN
2642 RAISE okl_api.g_exception_unexpected_error;
2643 END IF;
2644 END IF;
2645 x_eot_ver_id := l_eot_ver_id;
2646 x_version_number := l_version_number;
2647 x_return_status := g_ret_sts_success;
2648 okl_api.end_activity(x_msg_count => x_msg_count
2649 ,x_msg_data => x_msg_data);
2650
2651 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2652 okl_debug_pub.log_debug(fnd_log.level_procedure
2653 ,l_module
2654 ,'end debug OKLPLRTB.pls.pls call validate_eot_version');
2655 END IF;
2656
2657 EXCEPTION
2658 WHEN okl_api.g_exception_error THEN
2659 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2660 ,p_pkg_name => g_pkg_name
2661 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2662 ,x_msg_count => x_msg_count
2663 ,x_msg_data => x_msg_data
2664 ,p_api_type => g_api_type);
2665 WHEN okl_api.g_exception_unexpected_error THEN
2666 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2667 ,p_pkg_name => g_pkg_name
2668 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2669 ,x_msg_count => x_msg_count
2670 ,x_msg_data => x_msg_data
2671 ,p_api_type => g_api_type);
2672 WHEN OTHERS THEN
2673 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2674 ,p_pkg_name => g_pkg_name
2675 ,p_exc_name => 'OTHERS'
2676 ,x_msg_count => x_msg_count
2677 ,x_msg_data => x_msg_data
2678 ,p_api_type => g_api_type);
2679 END validate_eot_version;
2680
2681 --this api should be called to end date any ACTIVE lrs version
2682
2683 PROCEDURE enddate_lease_rate_set(p_api_version IN number
2684 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2685 ,x_return_status OUT NOCOPY varchar2
2686 ,x_msg_count OUT NOCOPY number
2687 ,x_msg_data OUT NOCOPY varchar2
2688 ,p_lrv_id_tbl IN okl_number_table
2689 ,p_end_date IN date) IS
2690
2691 CURSOR is_latest_version(p_rate_set_version_id IN number
2692 ,p_rate_set_id IN number) IS
2693 SELECT 'X'
2694 FROM okl_fe_rate_set_versions
2695 WHERE version_number = (SELECT max(to_number(version_number))
2696 FROM okl_fe_rate_set_versions
2697 WHERE rate_set_id = p_rate_set_id)
2698 AND rate_set_version_id = p_rate_set_version_id;
2699
2700 CURSOR get_not_abn_versions(p_rate_set_version_id IN number
2701 ,p_rate_set_id IN number) IS
2702 SELECT 'X'
2703 FROM okl_fe_rate_set_versions
2704 WHERE rate_set_id = p_rate_set_id
2705 AND rate_set_version_id <> p_rate_set_version_id
2706 AND sts_code <> 'ABANDONED';
2707 lp_lrvv_rec okl_lrvv_rec;
2708 lx_lrvv_rec okl_lrvv_rec;
2709 lp_lrtv_rec lrtv_rec_type;
2710 lx_lrtv_rec lrtv_rec_type;
2711 l_lrv_id_list varchar2(4000);
2712 l_no_data_found boolean;
2713 l_update_header boolean;
2714 l_update_version boolean;
2715 l_api_name CONSTANT varchar2(30) := 'enddate_lrs';
2716 l_api_version CONSTANT number := 1.0;
2717 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2718 l_dummy varchar2(1) := '?';
2719 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_lease_rate_sets_pvt.enddate_lease_rate_set';
2720 l_debug_enabled varchar2(10);
2721 is_debug_procedure_on boolean;
2722 is_debug_statement_on boolean;
2723
2724 BEGIN
2725 l_debug_enabled := okl_debug_pub.check_log_enabled;
2726 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2727 ,fnd_log.level_procedure);
2728
2729 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2730 okl_debug_pub.log_debug(fnd_log.level_procedure
2731 ,l_module
2732 ,'begin debug OKLRECCB.pls call enddate_lease_rate_set');
2733 END IF;
2734
2735 -- check for logging on STATEMENT level
2736
2737 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2738 ,fnd_log.level_statement);
2739
2740 -- call START_ACTIVITY to create savepoint, check compatibility
2741 -- and initialize message list
2742
2743 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2744 ,p_pkg_name => g_pkg_name
2745 ,p_init_msg_list => p_init_msg_list
2746 ,l_api_version => l_api_version
2747 ,p_api_version => p_api_version
2748 ,p_api_type => g_api_type
2749 ,x_return_status => x_return_status);
2750
2751 -- check if activity started successfully
2752
2753 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2754 RAISE okl_api.g_exception_unexpected_error;
2755 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2756 RAISE okl_api.g_exception_error;
2757 END IF;
2758
2759 FOR i IN p_lrv_id_tbl.FIRST..p_lrv_id_tbl.LAST LOOP
2760 lp_lrvv_rec := get_lrvv_rec(p_lrv_id_tbl(i), l_no_data_found);
2761
2762 IF (l_no_data_found = true) THEN
2763 okl_api.set_message(p_app_name => g_app_name
2764 ,p_msg_name => 'OKL_NO_VERSION_REC_FOUND');
2765 RAISE okl_api.g_exception_error;
2766 END IF;
2767 l_update_header := false;
2768 l_update_version := false;
2769
2770 IF lp_lrvv_rec.effective_from_date <= p_end_date THEN
2771 IF lp_lrvv_rec.effective_to_date IS NULL THEN
2772 lp_lrvv_rec.effective_to_date := p_end_date;
2773 l_update_version := true;
2774
2775 --if this is the latest version then put end date on header
2776 l_dummy := '?';
2777 OPEN is_latest_version(lp_lrvv_rec.rate_set_version_id
2778 ,lp_lrvv_rec.rate_set_id);
2779 FETCH is_latest_version INTO l_dummy ;
2780 CLOSE is_latest_version;
2781 IF l_dummy <> '?' THEN
2782 lp_lrtv_rec := get_lrtv_rec(lp_lrvv_rec.rate_set_id
2783 ,l_no_data_found);
2784 IF (l_no_data_found = true) THEN
2785 okl_api.set_message(p_app_name => g_app_name
2786 ,p_msg_name => 'OKL_NO_HEADER_REC_FOUND');
2787 RAISE okl_api.g_exception_error;
2788 END IF;
2789 lp_lrtv_rec.end_date := p_end_date;
2790 l_update_header := true;
2791 END IF;
2792 END IF;
2793 ELSE
2794 lp_lrvv_rec.sts_code := 'ABANDONED';
2795 l_update_version := true;
2796
2797 --if all versions are abandoned then make header status as abandoned
2798 l_dummy := '?';
2799 OPEN get_not_abn_versions(lp_lrvv_rec.rate_set_version_id
2800 ,lp_lrvv_rec.rate_set_id);
2801 FETCH get_not_abn_versions INTO l_dummy ;
2802 CLOSE get_not_abn_versions;
2803 IF l_dummy = '?' THEN
2804 lp_lrtv_rec := get_lrtv_rec(lp_lrvv_rec.rate_set_id
2805 ,l_no_data_found);
2806 IF (l_no_data_found = true) THEN
2807 okl_api.set_message(p_app_name => g_app_name
2808 ,p_msg_name => 'OKL_NO_HEADER_REC_FOUND');
2809 RAISE okl_api.g_exception_error;
2810 END IF;
2811 lp_lrtv_rec.sts_code := 'ABANDONED';
2812 l_update_header := true;
2813 END IF;
2814
2815 --if this is the latest version then put end date on header and version
2816 l_dummy := '?';
2817 OPEN is_latest_version(lp_lrvv_rec.rate_set_version_id
2818 ,lp_lrvv_rec.rate_set_id);
2819 FETCH is_latest_version INTO l_dummy ;
2820 CLOSE is_latest_version;
2821 IF l_dummy <> '?' THEN
2822
2823 --put end date on version
2824
2825 lp_lrvv_rec.effective_to_date := lp_lrvv_rec.effective_from_date;
2826 l_update_version := true;
2827
2828 --if header record is not retrieved in previous if condition then
2829 --retrieve it now
2830
2831 IF NOT l_update_header THEN
2832 lp_lrtv_rec := get_lrtv_rec(lp_lrvv_rec.rate_set_id
2833 ,l_no_data_found);
2834 IF (l_no_data_found = true) THEN
2835 okl_api.set_message(p_app_name => g_app_name
2836 ,p_msg_name => 'OKL_NO_HEADER_REC_FOUND');
2837 RAISE okl_api.g_exception_error;
2838 END IF;
2839 END IF;
2840
2841 --put end date on version
2842
2843 lp_lrtv_rec.end_date := lp_lrvv_rec.effective_from_date;
2844 l_update_header := true;
2845 END IF;
2846 END IF;
2847
2848 --update the version
2849
2850 IF l_update_version THEN
2851 okl_lrv_pvt.update_row(p_api_version => g_api_version
2852 ,p_init_msg_list => p_init_msg_list
2853 ,x_return_status => l_return_status
2854 ,x_msg_count => x_msg_count
2855 ,x_msg_data => x_msg_data
2856 ,p_lrvv_rec => lp_lrvv_rec
2857 ,x_lrvv_rec => lx_lrvv_rec);
2858 IF l_return_status = g_ret_sts_error THEN
2859 RAISE okl_api.g_exception_error;
2860 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2861 RAISE okl_api.g_exception_unexpected_error;
2862 END IF;
2863 END IF;
2864
2865 --update the header
2866
2867 IF l_update_header THEN
2868 okl_lrt_pvt.update_row(p_api_version => g_api_version
2869 ,p_init_msg_list => p_init_msg_list
2870 ,x_return_status => l_return_status
2871 ,x_msg_count => x_msg_count
2872 ,x_msg_data => x_msg_data
2873 ,p_lrtv_rec => lp_lrtv_rec
2874 ,x_lrtv_rec => lx_lrtv_rec);
2875 IF l_return_status = g_ret_sts_error THEN
2876 RAISE okl_api.g_exception_error;
2877 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2878 RAISE okl_api.g_exception_unexpected_error;
2879 END IF;
2880 END IF;
2881
2882 END LOOP;
2883 x_return_status := l_return_status;
2884 okl_api.end_activity(x_msg_count => x_msg_count
2885 ,x_msg_data => x_msg_data);
2886
2887 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2888 okl_debug_pub.log_debug(fnd_log.level_procedure
2889 ,l_module
2890 ,'end debug OKLPLRTB.pls.pls call enddate_lease_rate_set');
2891 END IF;
2892
2893 EXCEPTION
2894 WHEN okl_api.g_exception_error THEN
2895 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2896 ,p_pkg_name => g_pkg_name
2897 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2898 ,x_msg_count => x_msg_count
2899 ,x_msg_data => x_msg_data
2900 ,p_api_type => g_api_type);
2901 WHEN okl_api.g_exception_unexpected_error THEN
2902 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2903 ,p_pkg_name => g_pkg_name
2904 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2905 ,x_msg_count => x_msg_count
2906 ,x_msg_data => x_msg_data
2907 ,p_api_type => g_api_type);
2908 WHEN OTHERS THEN
2909 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2910 ,p_pkg_name => g_pkg_name
2911 ,p_exc_name => 'OTHERS'
2912 ,x_msg_count => x_msg_count
2913 ,x_msg_data => x_msg_data
2914 ,p_api_type => g_api_type);
2915 END enddate_lease_rate_set;
2916
2917 END okl_lease_rate_sets_pvt;