[Home] [Help]
PACKAGE BODY: APPS.OKL_FE_STD_RATE_TMPL_PVT
Source
1 PACKAGE BODY okl_fe_std_rate_tmpl_pvt AS
2 /* $Header: OKLRSRTB.pls 120.11 2006/07/21 13:15:15 akrangan noship $ */
3
4 --------------------------------------------------------------------------------
5 --PACKAGE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 g_db_error CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
9 g_prog_name_token CONSTANT VARCHAR2(9) := 'PROG_NAME';
10 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
11 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
12 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
13 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
14 g_exception_halt_validation EXCEPTION;
15 g_invalid_adj_cat_dates EXCEPTION;
16 g_exception_cannot_update EXCEPTION;
17 g_invalid_start_date EXCEPTION;
18 rosetta_g_mistake_date DATE := TO_DATE('01/01/-4711'
19 ,'MM/DD/SYYYY');
20 rosetta_g_miss_date DATE := TO_DATE('01/01/-4712'
21 ,'MM/DD/SYYYY');
22
23 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
24
25 FUNCTION rosetta_g_miss_date_in_map(d DATE) RETURN DATE AS
26
27 BEGIN
28
29 IF d = rosetta_g_mistake_date THEN
30 RETURN fnd_api.g_miss_date;
31 END IF;
32 RETURN d;
33 END;
34
35 PROCEDURE get_std_rate_tmpl(p_api_version IN NUMBER
36 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ,p_srt_id IN NUMBER
41 ,p_version_number IN NUMBER
42 ,x_srtv_rec OUT NOCOPY okl_srtv_rec
43 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
44
45 -- cursor to fetch the header record
46
47 CURSOR srt_hdr_csr(p_srt_id IN NUMBER) IS
48 SELECT std_rate_tmpl_id
49 ,template_name
50 ,template_desc
51 ,object_version_number
52 ,org_id
53 ,currency_code
54 ,rate_card_yn
55 ,default_yn
56 ,pricing_engine_code
57 ,orig_std_rate_tmpl_id
58 ,frequency_code
59 ,rate_type_code
60 ,index_id
61 ,sts_code
62 ,effective_from_date
63 ,effective_to_date
64 ,srt_rate
65 ,attribute_category
66 ,attribute1
67 ,attribute2
68 ,attribute3
69 ,attribute4
70 ,attribute5
71 ,attribute6
72 ,attribute7
73 ,attribute8
74 ,attribute9
75 ,attribute10
76 ,attribute11
77 ,attribute12
78 ,attribute13
79 ,attribute14
80 ,attribute15
81 ,created_by
82 ,creation_date
83 ,last_updated_by
84 ,last_update_date
85 ,last_update_login
86 FROM okl_fe_std_rt_tmp_v
87 WHERE std_rate_tmpl_id = p_srt_id;
88
89 -- cursor to fetch the versions record
90
91 CURSOR srt_version_csr(p_srt_id IN NUMBER
92 ,p_version_number IN VARCHAR2) IS
93 SELECT std_rate_tmpl_ver_id
94 ,version_number
95 ,object_version_number
96 ,std_rate_tmpl_id
97 ,sts_code
98 ,effective_from_date
99 ,effective_to_date
100 ,adj_mat_version_id
101 ,srt_rate
102 ,spread
103 ,day_convention_code
104 ,min_adj_rate
105 ,max_adj_rate
106 ,attribute_category
107 ,attribute1
108 ,attribute2
109 ,attribute3
110 ,attribute4
111 ,attribute5
112 ,attribute6
113 ,attribute7
114 ,attribute8
115 ,attribute9
116 ,attribute10
117 ,attribute11
118 ,attribute12
119 ,attribute13
120 ,attribute14
121 ,attribute15
122 ,created_by
123 ,creation_date
124 ,last_updated_by
125 ,last_update_date
126 ,last_update_login
127 FROM okl_fe_std_rt_tmp_vers
128 WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_version_number;
129 l_api_name VARCHAR2(40) := 'get_std_rate_tmpl';
130 l_api_version NUMBER := 1.0;
131
132 BEGIN
133 x_return_status := okl_api.start_activity(l_api_name
134 ,g_pkg_name
135 ,p_init_msg_list
136 ,l_api_version
137 ,p_api_version
138 ,'_PVT'
139 ,x_return_status);
140
141 -- populate the header record
142
143 OPEN srt_hdr_csr(p_srt_id);
144 FETCH srt_hdr_csr INTO x_srtv_rec ;
145 CLOSE srt_hdr_csr;
146
147 -- populate the header record
148
149 OPEN srt_version_csr(p_srt_id
150 ,p_version_number);
151 FETCH srt_version_csr INTO x_srv_rec ;
152 CLOSE srt_version_csr;
153
154 --end activity
155
156 okl_api.end_activity(x_msg_count
157 ,x_msg_data);
158 EXCEPTION
159 WHEN OTHERS THEN
160 x_return_status := okl_api.handle_exceptions(l_api_name
161 ,g_pkg_name
162 ,'OTHERS'
163 ,x_msg_count
164 ,x_msg_data
165 ,'_PVT');
166 END get_std_rate_tmpl;
167
168 PROCEDURE get_eligibility_criteria(p_api_version IN NUMBER
169 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
170 ,x_return_status OUT NOCOPY VARCHAR2
171 ,x_msg_count OUT NOCOPY NUMBER
172 ,x_msg_data OUT NOCOPY VARCHAR2
173 ,source_id IN NUMBER
174 ,x_ech_rec OUT NOCOPY okl_ech_rec
175 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
176 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
177
178 -- cursor to fetch the adjustment categories header record
179
180 CURSOR elig_crit_hdr(p_source_id IN NUMBER
181 ,p_source_object IN VARCHAR2) IS
182 SELECT criteria_set_id
183 ,object_version_number
184 ,source_id
185 ,source_object_code
186 ,match_criteria_code
187 ,validation_code
188 ,created_by
189 ,creation_date
190 ,last_updated_by
191 ,last_update_date
192 ,last_update_login
193 FROM okl_fe_criteria_set
194 WHERE source_id = p_source_id AND source_object_code = p_source_object;
195
196 -- cursor to fetch the adjustment categories lines record
197
198 CURSOR elig_crit_lines(p_criteria_set_id IN NUMBER) IS
199 SELECT criteria_id
200 ,object_version_number
201 ,match_criteria_code
202 ,criteria_set_id
203 ,crit_cat_def_id
204 ,effective_from_date
205 ,effective_to_date
206 ,created_by
207 ,creation_date
208 ,last_updated_by
209 ,last_update_date
210 ,last_update_login
211 FROM okl_fe_criteria
212 WHERE criteria_set_id = p_criteria_set_id;
213
214 -- cursor to fetch the adjustment categories values record
215
216 CURSOR elig_crit_values(p_criteria_id IN NUMBER) IS
217 SELECT criterion_value_id
218 ,object_version_number
219 ,criteria_id
220 ,operator_code
221 ,crit_cat_value1
222 ,crit_cat_value2
223 ,adjustment_factor
224 ,attribute_category
225 ,attribute1
226 ,attribute2
227 ,attribute3
228 ,attribute4
229 ,attribute5
230 ,attribute6
231 ,attribute7
232 ,attribute8
233 ,attribute9
234 ,attribute10
235 ,attribute11
236 ,attribute12
237 ,attribute13
238 ,attribute14
239 ,attribute15
240 ,created_by
241 ,creation_date
242 ,last_updated_by
243 ,last_update_date
244 ,last_update_login
245 FROM okl_fe_criterion_values
246 WHERE criteria_id = p_criteria_id;
247
248 -- cursor to get the data type and the value type of a category
249
250 CURSOR crit_def(criteria_def_id IN NUMBER) IS
251 SELECT data_type_code
252 ,value_type_code
253 FROM okl_fe_crit_cat_def_v
254 WHERE crit_cat_def_id = criteria_def_id;
255 l_api_name VARCHAR2(40) := 'get_eligibility_criteria';
256 l_api_version NUMBER := 1.0;
257 i NUMBER := 1;
258 j NUMBER := 1;
259 data_type VARCHAR2(30);
260
261 BEGIN
262 x_return_status := okl_api.start_activity(l_api_name
263 ,g_pkg_name
264 ,p_init_msg_list
265 ,l_api_version
266 ,p_api_version
267 ,'_PVT'
268 ,x_return_status);
269
270 FOR cat_hdr_rec IN elig_crit_hdr(source_id
271 ,'SRT') LOOP
272 x_ech_rec.criteria_set_id := cat_hdr_rec.criteria_set_id;
273 x_ech_rec.object_version_number := cat_hdr_rec.object_version_number;
274 x_ech_rec.source_id := cat_hdr_rec.source_id;
275 x_ech_rec.source_object_code := cat_hdr_rec.source_object_code;
276 x_ech_rec.match_criteria_code := cat_hdr_rec.match_criteria_code;
277 x_ech_rec.validation_code := cat_hdr_rec.validation_code;
278 x_ech_rec.created_by := cat_hdr_rec.created_by;
279 x_ech_rec.creation_date := cat_hdr_rec.creation_date;
280 x_ech_rec.last_updated_by := cat_hdr_rec.last_updated_by;
281 x_ech_rec.last_update_date := cat_hdr_rec.last_update_date;
282 x_ech_rec.last_update_login := cat_hdr_rec.last_update_login;
283 END LOOP;
284
285 FOR cat_lines_rec IN elig_crit_lines(x_ech_rec.criteria_set_id) LOOP
286 x_ecl_tbl(i).criteria_id := cat_lines_rec.criteria_id;
287 x_ecl_tbl(i).crit_cat_def_id := cat_lines_rec.crit_cat_def_id;
288 FOR type_code IN crit_def(cat_lines_rec.crit_cat_def_id) LOOP
289 data_type := type_code.data_type_code;
290 END LOOP;
291 FOR cat_values_rec IN elig_crit_values(cat_lines_rec.criteria_id) LOOP
292 x_ecv_tbl(j).criterion_value_id := cat_values_rec.criterion_value_id;
293 x_ecv_tbl(j).object_version_number := cat_values_rec.object_version_number;
294 x_ecv_tbl(j).criteria_id := cat_values_rec.criteria_id;
295 x_ecv_tbl(j).operator_code := cat_values_rec.operator_code;
296
297 IF (data_type = 'VARCHAR2') THEN
298 x_ecv_tbl(j).crit_cat_value1 := cat_values_rec.crit_cat_value1;
299 x_ecv_tbl(j).crit_cat_value2 := cat_values_rec.crit_cat_value2;
300 ELSIF (data_type = 'NUMBER') THEN
301 x_ecv_tbl(j).crit_cat_numval1 := TO_NUMBER(cat_values_rec.crit_cat_value1);
302 x_ecv_tbl(j).crit_cat_numval2 := TO_NUMBER(cat_values_rec.crit_cat_value2);
303 ELSIF (data_type = 'DATE') THEN
304 x_ecv_tbl(j).crit_cat_dateval1 := fnd_date.canonical_to_date(cat_values_rec.crit_cat_value1);
305 x_ecv_tbl(j).crit_cat_dateval2 := fnd_date.canonical_to_date(cat_values_rec.crit_cat_value2);
306 END IF;
307 x_ecv_tbl(j).adjustment_factor := cat_values_rec.adjustment_factor;
308 x_ecv_tbl(j).attribute_category := cat_values_rec.attribute_category;
309 x_ecv_tbl(j).attribute1 := cat_values_rec.attribute1;
310 x_ecv_tbl(j).attribute2 := cat_values_rec.attribute2;
311 x_ecv_tbl(j).attribute3 := cat_values_rec.attribute3;
312 x_ecv_tbl(j).attribute4 := cat_values_rec.attribute4;
313 x_ecv_tbl(j).attribute5 := cat_values_rec.attribute5;
314 x_ecv_tbl(j).attribute6 := cat_values_rec.attribute6;
315 x_ecv_tbl(j).attribute7 := cat_values_rec.attribute7;
316 x_ecv_tbl(j).attribute8 := cat_values_rec.attribute8;
317 x_ecv_tbl(j).attribute9 := cat_values_rec.attribute9;
318 x_ecv_tbl(j).attribute10 := cat_values_rec.attribute10;
319 x_ecv_tbl(j).attribute11 := cat_values_rec.attribute11;
320 x_ecv_tbl(j).attribute12 := cat_values_rec.attribute12;
321 x_ecv_tbl(j).attribute13 := cat_values_rec.attribute13;
322 x_ecv_tbl(j).attribute14 := cat_values_rec.attribute14;
323 x_ecv_tbl(j).attribute15 := cat_values_rec.attribute15;
324 x_ecv_tbl(j).created_by := cat_values_rec.created_by;
325 x_ecv_tbl(j).creation_date := cat_values_rec.creation_date;
326 x_ecv_tbl(j).last_updated_by := cat_values_rec.last_updated_by;
327 x_ecv_tbl(j).last_update_date := cat_values_rec.last_update_date;
328 x_ecv_tbl(j).last_update_login := cat_values_rec.last_update_login;
329 j := j + 1;
330 END LOOP;
331 x_ecl_tbl(i).object_version_number := cat_lines_rec.object_version_number;
332 x_ecl_tbl(i).match_criteria_code := cat_lines_rec.match_criteria_code;
333 x_ecl_tbl(i).criteria_set_id := cat_lines_rec.criteria_set_id;
334 x_ecl_tbl(i).effective_from_date := cat_lines_rec.effective_from_date;
335 x_ecl_tbl(i).effective_to_date := cat_lines_rec.effective_to_date;
336 x_ecl_tbl(i).created_by := cat_lines_rec.created_by;
337 x_ecl_tbl(i).creation_date := cat_lines_rec.creation_date;
338 x_ecl_tbl(i).last_updated_by := cat_lines_rec.last_updated_by;
339 x_ecl_tbl(i).last_update_date := cat_lines_rec.last_update_date;
340 x_ecl_tbl(i).last_update_login := cat_lines_rec.last_update_login;
341 x_ecl_tbl(i).is_new_flag := 'N';
342 i := i + 1;
343 END LOOP;
344
345 --end activity
346
347 okl_api.end_activity(x_msg_count
348 ,x_msg_data);
349 EXCEPTION
350 WHEN OTHERS THEN
351 x_return_status := okl_api.handle_exceptions(l_api_name
352 ,g_pkg_name
353 ,'OTHERS'
354 ,x_msg_count
355 ,x_msg_data
356 ,'_PVT');
357 END get_eligibility_criteria;
358
359 -- procedure to give the details of the Standard Rate Template given the Standard
360 -- Rate Template id and the version number
361
362 PROCEDURE get_version(p_api_version IN NUMBER
363 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
364 ,x_return_status OUT NOCOPY VARCHAR2
365 ,x_msg_count OUT NOCOPY NUMBER
366 ,x_msg_data OUT NOCOPY VARCHAR2
367 ,p_srt_id IN NUMBER
368 ,p_version_number IN NUMBER
369 ,x_srtv_rec OUT NOCOPY okl_srtv_rec
370 ,x_srv_rec OUT NOCOPY okl_srv_rec
371 ,x_ech_rec OUT NOCOPY okl_ech_rec
372 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
373 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
374 l_api_name VARCHAR2(40) := 'get_version';
375 l_api_version NUMBER := 1.0;
376 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
377
378 BEGIN
379 l_return_status := okl_api.start_activity(l_api_name
380 ,g_pkg_name
381 ,p_init_msg_list
382 ,l_api_version
383 ,p_api_version
384 ,'_PVT'
385 ,x_return_status);
386
387 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
388 RAISE okl_api.g_exception_unexpected_error;
389 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
390 RAISE okl_api.g_exception_error;
391 END IF;
392 get_std_rate_tmpl(p_api_version
393 ,p_init_msg_list
394 ,x_return_status
395 ,x_msg_count
396 ,x_msg_data
397 ,p_srt_id
398 ,p_version_number
399 ,x_srtv_rec
400 ,x_srv_rec);
401
402 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
403 RAISE okl_api.g_exception_unexpected_error;
404 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
405 RAISE okl_api.g_exception_error;
406 END IF;
407 get_eligibility_criteria(p_api_version
408 ,p_init_msg_list
409 ,x_return_status
410 ,x_msg_count
411 ,x_msg_data
412 ,x_srv_rec.std_rate_tmpl_ver_id
413 ,x_ech_rec
414 ,x_ecl_tbl
415 ,x_ecv_tbl);
416
417 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
418 RAISE okl_api.g_exception_unexpected_error;
419 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
420 RAISE okl_api.g_exception_error;
421 END IF;
422
423 --end activity
424
425 okl_api.end_activity(x_msg_count
426 ,x_msg_data);
427 x_return_status := l_return_status;
428 EXCEPTION
429 WHEN okl_api.g_exception_error THEN
430 x_return_status := okl_api.handle_exceptions(l_api_name
431 ,g_pkg_name
432 ,'OKL_API.G_RET_STS_ERROR'
433 ,x_msg_count
434 ,x_msg_data
435 ,'_PVT');
436 WHEN okl_api.g_exception_unexpected_error THEN
437 x_return_status := okl_api.handle_exceptions(l_api_name
438 ,g_pkg_name
439 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
440 ,x_msg_count
441 ,x_msg_data
442 ,'_PVT');
443 WHEN OTHERS THEN
444 x_return_status := okl_api.handle_exceptions(l_api_name
445 ,g_pkg_name
446 ,'OTHERS'
447 ,x_msg_count
448 ,x_msg_data
449 ,'_PVT');
450 END get_version;
451
452 -- procedure to give the details of the latest version of Standard Rate Template
453 -- given the Standard Rate Template id
454
455 PROCEDURE get_version(p_api_version IN NUMBER
456 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
457 ,x_return_status OUT NOCOPY VARCHAR2
458 ,x_msg_count OUT NOCOPY NUMBER
459 ,x_msg_data OUT NOCOPY VARCHAR2
460 ,p_srt_id IN NUMBER
461 ,x_srtv_rec OUT NOCOPY okl_srtv_rec
462 ,x_srv_rec OUT NOCOPY okl_srv_rec
463 ,x_ech_rec OUT NOCOPY okl_ech_rec
464 ,x_ecl_tbl OUT NOCOPY okl_ecl_tbl
465 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
466
467 CURSOR get_version_number(p_srt_id IN NUMBER) IS
468 SELECT MAX(version_number)
469 FROM okl_fe_std_rt_tmp_vers
470 WHERE std_rate_tmpl_id = p_srt_id;
471 l_version_number VARCHAR2(24);
472 l_api_name VARCHAR2(40) := 'get_version';
473 l_api_version NUMBER := 1.0;
474 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
475
476 BEGIN
477 l_return_status := okl_api.start_activity(l_api_name
478 ,g_pkg_name
479 ,p_init_msg_list
480 ,l_api_version
481 ,p_api_version
482 ,'_PVT'
483 ,x_return_status);
484
485 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
486 RAISE okl_api.g_exception_unexpected_error;
487 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
488 RAISE okl_api.g_exception_error;
489 END IF;
490 OPEN get_version_number(p_srt_id);
491 FETCH get_version_number INTO l_version_number ;
492 CLOSE get_version_number;
493 get_std_rate_tmpl(p_api_version
494 ,p_init_msg_list
495 ,x_return_status
496 ,x_msg_count
497 ,x_msg_data
498 ,p_srt_id
499 ,l_version_number
500 ,x_srtv_rec
501 ,x_srv_rec);
502
503 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
504 RAISE okl_api.g_exception_unexpected_error;
505 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
506 RAISE okl_api.g_exception_error;
507 END IF;
508 get_eligibility_criteria(p_api_version
509 ,p_init_msg_list
510 ,x_return_status
511 ,x_msg_count
512 ,x_msg_data
513 ,x_srv_rec.std_rate_tmpl_ver_id
514 ,x_ech_rec
515 ,x_ecl_tbl
516 ,x_ecv_tbl);
517
518 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
519 RAISE okl_api.g_exception_unexpected_error;
520 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
521 RAISE okl_api.g_exception_error;
522 END IF;
523
524 --end activity
525
526 okl_api.end_activity(x_msg_count
527 ,x_msg_data);
528 x_return_status := l_return_status;
529 EXCEPTION
530 WHEN okl_api.g_exception_error THEN
531 x_return_status := okl_api.handle_exceptions(l_api_name
532 ,g_pkg_name
533 ,'OKL_API.G_RET_STS_ERROR'
534 ,x_msg_count
535 ,x_msg_data
536 ,'_PVT');
537 WHEN okl_api.g_exception_unexpected_error THEN
538 x_return_status := okl_api.handle_exceptions(l_api_name
539 ,g_pkg_name
540 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
541 ,x_msg_count
542 ,x_msg_data
543 ,'_PVT');
544 WHEN OTHERS THEN
545 x_return_status := okl_api.handle_exceptions(l_api_name
546 ,g_pkg_name
547 ,'OTHERS'
548 ,x_msg_count
549 ,x_msg_data
550 ,'_PVT');
551 END get_version;
552
553 --procedure to create a Standard Rate Template with the associated Eligibility Criteria
554
555 PROCEDURE insert_srt(p_api_version IN NUMBER
556 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
557 ,x_return_status OUT NOCOPY VARCHAR2
558 ,x_msg_count OUT NOCOPY NUMBER
559 ,x_msg_data OUT NOCOPY VARCHAR2
560 ,p_srtv_rec IN okl_srtv_rec
561 ,p_srv_rec IN okl_srv_rec
562 ,x_srtv_rec OUT NOCOPY okl_srtv_rec
563 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
564 l_srtv_rec okl_srtv_rec := p_srtv_rec;
565 l_srv_rec okl_srv_rec := p_srv_rec;
566 l_api_version NUMBER := 1.0;
567 l_api_name VARCHAR2(40) := 'INSERT_SRT';
568 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
569 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
570 l_dummy_var VARCHAR2(1):='?';
571
572 CURSOR rate_csr(p_index_id IN NUMBER
573 ,p_eff_from IN DATE) IS
574 SELECT val.value
575 FROM okl_indices ind
576 ,okl_index_values val
577 WHERE ind.id = val.idx_id AND ind.id = p_index_id
578 AND p_eff_from BETWEEN val.datetime_valid AND NVL(val.datetime_invalid, TO_DATE('01-01-9999', 'dd-mm-yyyy'));
579
580 CURSOR srt_unique_chk(p_name IN varchar2) IS
581 SELECT 'x'
582 FROM okl_fe_std_rt_tmp_v
583 WHERE template_name = UPPER(p_name);
584 BEGIN
585 l_return_status := okl_api.start_activity(l_api_name
586 ,g_pkg_name
587 ,p_init_msg_list
588 ,l_api_version
589 ,p_api_version
590 ,'_PVT'
591 ,x_return_status);
592
593 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
594 RAISE okl_api.g_exception_unexpected_error;
595 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
596 RAISE okl_api.g_exception_error;
597 END IF;
598
599 OPEN srt_unique_chk(l_srtv_rec.template_name);
600 FETCH srt_unique_chk INTO l_dummy_var ;
601 CLOSE srt_unique_chk;
602
603 -- if l_dummy_var is 'x' then name already exists
604
605 IF (l_dummy_var = 'x') THEN
606 okl_api.set_message(p_app_name => g_app_name
607 ,p_msg_name => 'OKL_DUPLICATE_NAME'
608 ,p_token1 => 'NAME'
609 ,p_token1_value => l_srtv_rec.template_name);
610 RAISE okl_api.g_exception_error;
611
612 END IF;
613 -- fix for gmiss date
614
615 l_srv_rec.effective_to_date := rosetta_g_miss_date_in_map(l_srv_rec.effective_to_date);
616
617 IF (l_srtv_rec.rate_type_code = 'BASE_RATE' AND (l_srv_rec.srt_rate IS NULL
618 OR l_srv_rec.srt_rate = okl_api.g_miss_num)) THEN
619 RAISE okl_api.g_exception_error;
620 END IF;
621
622 IF (l_srtv_rec.rate_type_code = 'INDEX_RATE') THEN
623 l_srv_rec.srt_rate := null;
624 OPEN rate_csr(l_srtv_rec.index_id
625 ,l_srv_rec.effective_from_date);
626 FETCH rate_csr INTO l_srv_rec.srt_rate ;
627 CLOSE rate_csr;
628
629 -- if l_srv_rec.srt_rate is null, then set the error message
630
631 IF (l_srv_rec.SRT_RATE is null) THEN
632 okl_api.set_message(p_app_name => g_app_name
633 ,p_msg_name => 'OKL_SRT_INDEX_RATE_NOT_AVAIL'
634 );
635 RAISE okl_api.g_exception_error;
636 END IF;
637 ELSE
638 l_srtv_rec.index_id:= null;
639
640 END IF;
641
642 -- setting the header attributes
643
644 l_srtv_rec.template_name := UPPER(l_srtv_rec.template_name);
645 l_srtv_rec.sts_code := 'NEW';
646 l_srtv_rec.effective_from_date := l_srv_rec.effective_from_date;
647 l_srtv_rec.effective_to_date := l_srtv_rec.effective_to_date;
648 l_srtv_rec.srt_rate := l_srv_rec.srt_rate;
649 l_srtv_rec.default_yn := 'N';
650
651 -- insert the header record into the table
652
653 okl_srt_pvt.insert_row(l_api_version
654 ,l_init_msg_list
655 ,l_return_status
656 ,x_msg_count
657 ,x_msg_data
658 ,l_srtv_rec
659 ,x_srtv_rec);
660
661 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
662 RAISE okl_api.g_exception_unexpected_error;
663 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
664 RAISE okl_api.g_exception_error;
665 END IF;
666
667 -- setting the version attributes
668
669 l_srv_rec.sts_code := 'NEW';
670 l_srv_rec.version_number := 1.0;
671 l_srv_rec.std_rate_tmpl_id := x_srtv_rec.std_rate_tmpl_id;
672
673 -- insert the versions record
674
675 okl_srv_pvt.insert_row(l_api_version
676 ,l_init_msg_list
677 ,l_return_status
678 ,x_msg_count
679 ,x_msg_data
680 ,l_srv_rec
681 ,x_srv_rec);
682
683 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
684 RAISE okl_api.g_exception_unexpected_error;
685 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
686 RAISE okl_api.g_exception_error;
687 END IF;
688
689 --end activity
690
691 okl_api.end_activity(x_msg_count
692 ,x_msg_data);
693 x_return_status := l_return_status;
694 EXCEPTION
695 WHEN okl_api.g_exception_error THEN
696 x_return_status := okl_api.handle_exceptions(l_api_name
697 ,g_pkg_name
698 ,'OKL_API.G_RET_STS_ERROR'
699 ,x_msg_count
700 ,x_msg_data
701 ,'_PVT');
702 WHEN okl_api.g_exception_unexpected_error THEN
703 x_return_status := okl_api.handle_exceptions(l_api_name
704 ,g_pkg_name
705 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
706 ,x_msg_count
707 ,x_msg_data
708 ,'_PVT');
709 WHEN OTHERS THEN
710 x_return_status := okl_api.handle_exceptions(l_api_name
711 ,g_pkg_name
712 ,'OTHERS'
713 ,x_msg_count
714 ,x_msg_data
715 ,'_PVT');
716 END insert_srt;
717
718 -- procedure to update a particular version of the Standard Rate Template
719
720 PROCEDURE update_srt(p_api_version IN NUMBER
721 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
722 ,x_return_status OUT NOCOPY VARCHAR2
723 ,x_msg_count OUT NOCOPY NUMBER
724 ,x_msg_data OUT NOCOPY VARCHAR2
725 ,p_srv_rec IN okl_srv_rec
726 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
727 l_srtv_rec okl_srtv_rec;
728 x_srtv_rec okl_srtv_rec;
729 l_srv_rec okl_srv_rec := p_srv_rec;
730 l_api_version NUMBER := 1.0;
731 l_api_name VARCHAR2(40) := 'UPDATE_SRT';
732 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
733 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
734 l_eff_from DATE;
735 l_eff_to DATE;
736 l_max_date DATE;
737 cal_eff_from DATE;
738 lp_lrtv_tbl okl_lrs_id_tbl;
739 x_obj_tbl invalid_object_tbl;
740
741 -- cursor to fetch the previous version effective from and the previous version effective to
742
743 CURSOR prev_ver_csr(l_srt_id IN NUMBER
744 ,l_version_number IN VARCHAR2) IS
745 SELECT effective_from_date
746 ,effective_to_date
747 FROM okl_fe_std_rt_tmp_vers
748 WHERE std_rate_tmpl_id = l_srt_id
749 AND version_number = l_version_number - 1;
750 CURSOR get_elig_crit_start_date(p_version_id IN NUMBER) IS
751 SELECT max(effective_from_date)
752 FROM okl_fe_criteria_set ech
753 ,okl_fe_criteria ecl
754 WHERE ecl.criteria_set_id = ech.criteria_set_id
755 AND ech.source_id = p_version_id AND source_object_code = 'SRT';
756
757 BEGIN
758 l_return_status := okl_api.start_activity(l_api_name
759 ,g_pkg_name
760 ,l_init_msg_list
761 ,l_api_version
762 ,p_api_version
763 ,'_PVT'
764 ,x_return_status);
765
766 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
767 RAISE okl_api.g_exception_unexpected_error;
768 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
769 RAISE okl_api.g_exception_error;
770 END IF;
771
772 -- fix for gmiss date
773
774 l_srv_rec.effective_to_date := rosetta_g_miss_date_in_map(l_srv_rec.effective_to_date);
775
776
777
778 IF (l_srv_rec.version_number = 1 AND l_srv_rec.sts_code = 'NEW') THEN
779 l_srtv_rec.std_rate_tmpl_id := l_srv_rec.std_rate_tmpl_id;
780 l_srtv_rec.effective_from_date := l_srv_rec.effective_from_date;
781 l_srtv_rec.effective_to_date := l_srv_rec.effective_to_date;
782 l_srtv_rec.srt_rate := l_srv_rec.srt_rate;
783
784 -- update the header record
785
786 okl_srt_pvt.update_row(l_api_version
787 ,l_init_msg_list
788 ,l_return_status
789 ,x_msg_count
790 ,x_msg_data
791 ,l_srtv_rec
792 ,x_srtv_rec);
793 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
794 RAISE okl_api.g_exception_unexpected_error;
795 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
796 RAISE okl_api.g_exception_error;
797 END IF;
798 ELSIF (l_srv_rec.sts_code = 'ACTIVE') THEN
799 l_srtv_rec.std_rate_tmpl_id := l_srv_rec.std_rate_tmpl_id;
800
801 -- check if this end date is greater than the calculated end date
802 -- check for the effective to date
803 calc_start_date(l_api_version
804 ,l_init_msg_list
805 ,l_return_status
806 ,x_msg_count
807 ,x_msg_data
808 ,l_srv_rec
809 ,cal_eff_from);
810
811 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
812 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
813 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
814 RAISE OKL_API.G_EXCEPTION_ERROR;
815 END IF;
816
817 OPEN get_elig_crit_start_date(l_srv_rec.std_rate_tmpl_ver_id);
818 FETCH get_elig_crit_start_date INTO l_max_date;
819 CLOSE get_elig_crit_start_date;
820
821 -- viselvar Bug#4907469 modified
822 IF(l_max_date IS NOT NULL AND l_max_date+1 > cal_eff_from) THEN
823 cal_eff_from:= l_max_date +1;
824 END IF;
825
826 -- viselvar Bug#4907469 modified
827 IF (cal_eff_from <> okl_api.g_miss_date AND l_srv_rec.effective_to_date < (cal_eff_from-1) and cal_eff_from<>okl_api.g_miss_date) THEN
828 okl_api.set_message(
829 p_app_name => g_app_name
830 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO_DATE'
831 ,p_token1 => 'DATE'
832 ,p_token1_value => cal_eff_from-1);
833 RAISE okl_api.g_exception_error;
834 END IF;
835
836 l_srtv_rec.effective_to_date := l_srv_rec.effective_to_date;
837 l_srtv_rec.srt_rate := l_srv_rec.srt_rate;
838
839 -- update the header record
840
841 okl_srt_pvt.update_row(l_api_version
842 ,l_init_msg_list
843 ,l_return_status
844 ,x_msg_count
845 ,x_msg_data
846 ,l_srtv_rec
847 ,x_srtv_rec);
848 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
849 RAISE okl_api.g_exception_unexpected_error;
850 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
851 RAISE okl_api.g_exception_error;
852 END IF;
853 IF (l_srv_rec.effective_to_date IS NOT NULL AND l_srv_rec.effective_to_date <> okl_api.g_miss_date) THEN
854
855 -- put an end date to the previous version of the eligibility criteria
856
857 okl_ecc_values_pvt.end_date_eligibility_criteria(p_api_version => l_api_version
858 ,p_init_msg_list => p_init_msg_list
859 ,x_return_status => x_return_status
860 ,x_msg_count => x_msg_count
861 ,x_msg_data => x_msg_data
862 ,p_source_id => l_srv_rec.std_rate_tmpl_ver_id
863 ,p_source_type => 'SRT'
864 ,p_end_date => l_srv_rec.effective_to_date);
865
866 -- end date the lease rate set versions
867
868 invalid_objects(p_api_version
869 ,p_init_msg_list
870 ,x_return_status
871 ,x_msg_count
872 ,x_msg_data
873 ,l_srv_rec.std_rate_tmpl_ver_id
874 ,x_obj_tbl);
875 IF (x_obj_tbl.COUNT > 0) THEN
876
877 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
878 lp_lrtv_tbl(j) := x_obj_tbl(j).obj_id;
879 END LOOP;
880 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(p_api_version
881 ,p_init_msg_list
882 ,x_return_status
883 ,x_msg_count
884 ,x_msg_data
885 ,lp_lrtv_tbl
886 ,l_srv_rec.effective_to_date);
887 END IF;
888 END IF;
889 END IF;
890
891 -- update the version record
892
893 okl_srv_pvt.update_row(l_api_version
894 ,l_init_msg_list
895 ,l_return_status
896 ,x_msg_count
897 ,x_msg_data
898 ,l_srv_rec
899 ,x_srv_rec);
900
901 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
902 RAISE okl_api.g_exception_unexpected_error;
903 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
904 RAISE okl_api.g_exception_error;
905 END IF;
906
907 --end activity
908
909 okl_api.end_activity(x_msg_count
910 ,x_msg_data);
911 x_return_status := l_return_status;
912 EXCEPTION
913 WHEN okl_api.g_exception_error THEN
914 x_return_status := okl_api.handle_exceptions(l_api_name
915 ,g_pkg_name
916 ,'OKL_API.G_RET_STS_ERROR'
917 ,x_msg_count
918 ,x_msg_data
919 ,'_PVT');
920 WHEN okl_api.g_exception_unexpected_error THEN
921 x_return_status := okl_api.handle_exceptions(l_api_name
922 ,g_pkg_name
923 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
924 ,x_msg_count
925 ,x_msg_data
926 ,'_PVT');
927 WHEN OTHERS THEN
928 x_return_status := okl_api.handle_exceptions(l_api_name
929 ,g_pkg_name
930 ,'OTHERS'
931 ,x_msg_count
932 ,x_msg_data
933 ,'_PVT');
934 END update_srt;
935
936 -- procedure to create a new version of the Standard Rate Template
937
938 PROCEDURE create_version(p_api_version IN NUMBER
939 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
940 ,x_return_status OUT NOCOPY VARCHAR2
941 ,x_msg_count OUT NOCOPY NUMBER
942 ,x_msg_data OUT NOCOPY VARCHAR2
943 ,p_srv_rec IN okl_srv_rec
944 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
945
946 l_srtv_rec okl_srtv_rec;
947 x_srtv_rec okl_srtv_rec;
948 l_srv_rec okl_srv_rec := p_srv_rec;
949 l_api_version NUMBER := 1.0;
950 l_api_name VARCHAR2(40) := 'CREATE_VERSION';
951 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
952 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
953 cal_eff_from DATE;
954
955 BEGIN
956 l_return_status := okl_api.start_activity(l_api_name
957 ,g_pkg_name
958 ,l_init_msg_list
959 ,l_api_version
960 ,p_api_version
961 ,'_PVT'
962 ,x_return_status);
963
964 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
965 RAISE okl_api.g_exception_unexpected_error;
966 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
967 RAISE okl_api.g_exception_error;
968 END IF;
969
970 -- fix for gmiss date
971
972 l_srv_rec.effective_to_date := rosetta_g_miss_date_in_map(l_srv_rec.effective_to_date);
973
974 -- change the header status as under revision
975
976 l_srtv_rec.sts_code := 'UNDER_REVISION';
977 l_srtv_rec.std_rate_tmpl_id := l_srv_rec.std_rate_tmpl_id;
978
979 -- update the header record
980
981 okl_srt_pvt.update_row(l_api_version
982 ,l_init_msg_list
983 ,l_return_status
984 ,x_msg_count
985 ,x_msg_data
986 ,l_srtv_rec
987 ,x_srtv_rec);
988
989 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
990 RAISE okl_api.g_exception_unexpected_error;
991 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
992 RAISE okl_api.g_exception_error;
993 END IF;
994
995 -- Check if user entered start date > the calculated start date
996 -- else raise an exception
997
998 calc_start_date(l_api_version
999 ,l_init_msg_list
1000 ,l_return_status
1001 ,x_msg_count
1002 ,x_msg_data
1003 ,l_srv_rec
1004 ,cal_eff_from);
1005
1006 IF (l_srv_rec.effective_from_date < cal_eff_from) THEN
1007 -- viselvar modified Bug#4907469
1008 okl_api.set_message(p_app_name => g_app_name
1009 ,p_msg_name => 'OKL_INVALID_EFF_FROM'
1010 ,p_token1 => 'DATE'
1011 ,p_token1_value => cal_eff_from);
1012 RAISE okl_api.g_exception_error;
1013 END IF;
1014
1015 -- insert the version record into the table
1016
1017 okl_srv_pvt.insert_row(l_api_version
1018 ,l_init_msg_list
1019 ,l_return_status
1020 ,x_msg_count
1021 ,x_msg_data
1022 ,l_srv_rec
1023 ,x_srv_rec);
1024
1025 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1026 RAISE okl_api.g_exception_unexpected_error;
1027 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1028 RAISE okl_api.g_exception_error;
1029 END IF;
1030
1031 --end activity
1032
1033 okl_api.end_activity(x_msg_count
1034 ,x_msg_data);
1035 x_return_status := l_return_status;
1036 EXCEPTION
1037 WHEN okl_api.g_exception_error THEN
1038 x_return_status := okl_api.handle_exceptions(l_api_name
1039 ,g_pkg_name
1040 ,'OKL_API.G_RET_STS_ERROR'
1041 ,x_msg_count
1042 ,x_msg_data
1043 ,'_PVT');
1044 WHEN okl_api.g_exception_unexpected_error THEN
1045 x_return_status := okl_api.handle_exceptions(l_api_name
1046 ,g_pkg_name
1047 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1048 ,x_msg_count
1049 ,x_msg_data
1050 ,'_PVT');
1051 WHEN OTHERS THEN
1052 x_return_status := okl_api.handle_exceptions(l_api_name
1053 ,g_pkg_name
1054 ,'OTHERS'
1055 ,x_msg_count
1056 ,x_msg_data
1057 ,'_PVT');
1058 END create_version;
1059
1060 -- procedure to raise the workflow which submits the record and changes the status.
1061
1062 PROCEDURE submit_srt(p_api_version IN NUMBER
1063 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1064 ,x_return_status OUT NOCOPY VARCHAR2
1065 ,x_msg_count OUT NOCOPY NUMBER
1066 ,x_msg_data OUT NOCOPY VARCHAR2
1067 ,p_version_id IN NUMBER) IS
1068 l_srv_rec okl_srv_rec;
1069 x_srv_rec okl_srv_rec;
1070 l_api_version NUMBER := 1.0;
1071 l_api_name VARCHAR2(40) := 'SUBMIT_SRT';
1072 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1073 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1074 l_parameter_list wf_parameter_list_t;
1075 p_event_name VARCHAR2(240) := 'oracle.apps.okl.fe.srtapproval';
1076 l_profile_value VARCHAR2(30);
1077
1078 BEGIN
1079 l_return_status := okl_api.start_activity(l_api_name
1080 ,g_pkg_name
1081 ,l_init_msg_list
1082 ,l_api_version
1083 ,p_api_version
1084 ,'_PVT'
1085 ,x_return_status);
1086
1087 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1088 RAISE okl_api.g_exception_unexpected_error;
1089 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1090 RAISE okl_api.g_exception_error;
1091 END IF;
1092 l_srv_rec.std_rate_tmpl_ver_id := p_version_id;
1093 l_srv_rec.sts_code := 'SUBMITTED';
1094 okl_srv_pvt.update_row(l_api_version
1095 ,p_init_msg_list
1096 ,x_return_status
1097 ,x_msg_count
1098 ,x_msg_data
1099 ,l_srv_rec
1100 ,x_srv_rec);
1101
1102 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1103 RAISE okl_api.g_exception_unexpected_error;
1104 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1105 RAISE okl_api.g_exception_error;
1106 END IF;
1107 fnd_profile.get('OKL_PE_APPROVAL_PROCESS'
1108 ,l_profile_value);
1109
1110 IF (nvl(l_profile_value,'NONE') = 'NONE') THEN
1111 okl_fe_std_rate_tmpl_pvt.handle_approval(l_api_version
1112 ,p_init_msg_list
1113 ,x_return_status
1114 ,x_msg_count
1115 ,x_msg_data
1116 ,p_version_id);
1117 ELSE
1118
1119 -- raise the business event passing the version id added to the parameter list
1120
1121 wf_event.addparametertolist('VERSION_ID'
1122 ,p_version_id
1123 ,l_parameter_list);
1124 --added by akrangan
1125 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1126
1127 okl_wf_pvt.raise_event(p_api_version => p_api_version
1128 ,p_init_msg_list => p_init_msg_list
1129 ,x_return_status => x_return_status
1130 ,x_msg_count => x_msg_count
1131 ,x_msg_data => x_msg_data
1132 ,p_event_name => p_event_name
1133 ,p_parameters => l_parameter_list);
1134 END IF;
1135
1136 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1137 RAISE okl_api.g_exception_unexpected_error;
1138 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1139 RAISE okl_api.g_exception_error;
1140 END IF;
1141
1142 --end activity
1143
1144 okl_api.end_activity(x_msg_count
1145 ,x_msg_data);
1146 x_return_status := l_return_status;
1147 EXCEPTION
1148 WHEN okl_api.g_exception_error THEN
1149 x_return_status := okl_api.handle_exceptions(l_api_name
1150 ,g_pkg_name
1151 ,'OKL_API.G_RET_STS_ERROR'
1152 ,x_msg_count
1153 ,x_msg_data
1154 ,'_PVT');
1155 WHEN okl_api.g_exception_unexpected_error THEN
1156 x_return_status := okl_api.handle_exceptions(l_api_name
1157 ,g_pkg_name
1158 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1159 ,x_msg_count
1160 ,x_msg_data
1161 ,'_PVT');
1162 WHEN OTHERS THEN
1163 x_return_status := okl_api.handle_exceptions(l_api_name
1164 ,g_pkg_name
1165 ,'OTHERS'
1166 ,x_msg_count
1167 ,x_msg_data
1168 ,'_PVT');
1169 END submit_srt;
1170
1171 -- procedure to handle when the process is going through the process of approval
1172
1173 PROCEDURE handle_approval(p_api_version IN NUMBER
1174 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1175 ,x_return_status OUT NOCOPY VARCHAR2
1176 ,x_msg_count OUT NOCOPY NUMBER
1177 ,x_msg_data OUT NOCOPY VARCHAR2
1178 ,p_version_id IN NUMBER) IS
1179
1180 CURSOR srt_version_csr(p_version_id IN NUMBER) IS
1181 SELECT std_rate_tmpl_id
1182 ,version_number
1183 ,effective_from_date
1184 ,effective_to_date
1185 ,srt_rate
1186 FROM okl_fe_std_rt_tmp_vers
1187 WHERE std_rate_tmpl_ver_id = p_version_id;
1188
1189 CURSOR ver_eff_to_csr(p_srt_id IN NUMBER
1190 ,p_version_number IN NUMBER) IS
1191 SELECT std_rate_tmpl_ver_id
1192 ,effective_to_date
1193 FROM okl_fe_std_rt_tmp_vers
1194 WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_version_number;
1195
1196 CURSOR max_version_csr(p_srt_id IN NUMBER) IS
1197 SELECT MAX(version_number)
1198 FROM okl_fe_std_rt_tmp_vers
1199 WHERE std_rate_tmpl_id = p_srt_id;
1200
1201 CURSOR cal_rate_csr(index_id IN NUMBER
1202 ,effective_from IN DATE) IS
1203 SELECT value
1204 FROM okl_index_values_v
1205 WHERE idx_id = index_id AND effective_from >= datetime_valid
1206 AND effective_from <= NVL(datetime_invalid, TO_DATE('01-01-9999', 'dd-mm-yyyy'));
1207
1208 CURSOR index_id_csr(p_srt_id IN NUMBER) IS
1209 SELECT index_id
1210 FROM okl_fe_std_rt_tmp_all_b
1211 WHERE std_rate_tmpl_id = p_srt_id;
1212 l_srt_id NUMBER;
1213 l_srt_ver_id NUMBER;
1214 l_index_id NUMBER;
1215 l_rate NUMBER;
1216 l_version_number NUMBER;
1217 l_srt_rate NUMBER;
1218 l_effective_from DATE;
1219 l_effective_to DATE;
1220 l_eff_prev_ver DATE;
1221 l_srtv_rec okl_srtv_rec;
1222 x_srtv_rec okl_srtv_rec;
1223 l_srv_rec okl_srv_rec;
1224 x_srv_rec okl_srv_rec;
1225 lp_srv_rec okl_srv_rec;
1226 lp_lrtv_tbl okl_lrs_id_tbl;
1227 x_obj_tbl invalid_object_tbl;
1228 l_max_version VARCHAR2(24);
1229 l_cal_end_date DATE;
1230 l_api_version NUMBER := 1.0;
1231 l_api_name VARCHAR2(40) := 'handle_approval';
1232
1233 BEGIN
1234 x_return_status := okl_api.start_activity(l_api_name
1235 ,g_pkg_name
1236 ,p_init_msg_list
1237 ,l_api_version
1238 ,p_api_version
1239 ,'_PVT'
1240 ,x_return_status);
1241
1242 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1243 RAISE okl_api.g_exception_unexpected_error;
1244 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1245 RAISE okl_api.g_exception_error;
1246 END IF;
1247
1248 -- if it is the first version,
1249 -- change the header status and the end date of the header as the version end date
1250 -- change the version status to active
1251 -- if it had=s already some versions,
1252 -- then end_date the previous versions
1253 -- then end date the reference of the previous version
1254
1255 OPEN srt_version_csr(p_version_id);
1256 FETCH srt_version_csr INTO l_srt_id
1257 ,l_version_number
1258 ,l_effective_from
1259 ,l_effective_to
1260 ,l_rate ;
1261 CLOSE srt_version_csr;
1262 OPEN max_version_csr(l_srt_id);
1263 FETCH max_version_csr INTO l_max_version ;
1264 CLOSE max_version_csr;
1265
1266 lp_srv_rec.std_rate_tmpl_id := l_srt_id;
1267 lp_srv_rec.version_number:= l_version_number;
1268 lp_srv_rec.effective_from_date := l_effective_from;
1269 lp_srv_rec.effective_to_date := l_effective_to;
1270
1271 IF (l_version_number = 1) THEN
1272 l_srtv_rec.std_rate_tmpl_id := l_srt_id;
1273 l_srtv_rec.sts_code := 'ACTIVE';
1274 IF (l_effective_to IS NOT NULL) THEN
1275 l_srtv_rec.effective_to_date := l_effective_to;
1276 ELSE
1277 l_srtv_rec.effective_to_date := okl_api.g_miss_date;
1278 END IF;
1279
1280 ELSIF (l_version_number = l_max_version) THEN
1281
1282 -- get the previous version Effective To
1283
1284 OPEN ver_eff_to_csr(l_srt_id
1285 ,l_version_number - 1);
1286 FETCH ver_eff_to_csr INTO l_srt_ver_id
1287 ,l_eff_prev_ver ;
1288 CLOSE ver_eff_to_csr;
1289
1290 -- get the referenced version maximum end_date
1291
1292 lp_srv_rec.STD_RATE_TMPL_VER_ID:= l_srt_ver_id;
1293 calc_start_date(
1294 p_api_version ,
1295 p_init_msg_list ,
1296 x_return_status ,
1297 x_msg_count ,
1298 x_msg_data ,
1299 lp_srv_rec ,
1300 l_cal_end_date );
1301
1302 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1303 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1304 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1305 raise OKL_API.G_EXCEPTION_ERROR;
1306 END IF;
1307
1308 IF (lp_srv_rec.effective_from_date < l_cal_end_date ) THEN
1309 RAISE okl_api.g_exception_error;
1310 END IF;
1311 l_cal_end_date:= lp_srv_rec.effective_from_date -1;
1312
1313 l_srtv_rec.std_rate_tmpl_id := l_srt_id;
1314 l_srtv_rec.sts_code := 'ACTIVE';
1315 IF (l_effective_to IS NOT NULL) THEN
1316 l_srtv_rec.effective_to_date := l_effective_to;
1317 ELSE
1318 l_srtv_rec.effective_to_date := okl_api.g_miss_date;
1319 END IF;
1320
1321 IF (l_cal_end_date IS NOT NULL) THEN
1322
1323 -- end date the lease rate set versions
1324
1325 invalid_objects(p_api_version
1326 ,p_init_msg_list
1327 ,x_return_status
1328 ,x_msg_count
1329 ,x_msg_data
1330 ,l_srt_ver_id
1331 ,x_obj_tbl);
1332 IF (x_obj_tbl.COUNT > 0) THEN
1333
1334 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1335 lp_lrtv_tbl(j) := x_obj_tbl(j).obj_id;
1336 END LOOP;
1337 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(p_api_version
1338 ,p_init_msg_list
1339 ,x_return_status
1340 ,x_msg_count
1341 ,x_msg_data
1342 ,lp_lrtv_tbl
1343 ,l_cal_end_date);
1344 END IF;
1345 END IF;
1346 -- update the previous version effective to
1347
1348 IF (NVL(l_eff_prev_ver
1349 ,okl_api.g_miss_date) <> l_cal_end_date) THEN
1350 l_srv_rec.std_rate_tmpl_ver_id := l_srt_ver_id;
1351 l_srv_rec.effective_to_date := l_cal_end_date;
1352 okl_srv_pvt.update_row(l_api_version
1353 ,p_init_msg_list
1354 ,x_return_status
1355 ,x_msg_count
1356 ,x_msg_data
1357 ,l_srv_rec
1358 ,x_srv_rec);
1359
1360 -- put an end date to the previous version of the eligibility criteria
1361
1362 okl_ecc_values_pvt.end_date_eligibility_criteria(p_api_version => l_api_version
1363 ,p_init_msg_list => p_init_msg_list
1364 ,x_return_status => x_return_status
1365 ,x_msg_count => x_msg_count
1366 ,x_msg_data => x_msg_data
1367 ,p_source_id => l_srt_ver_id
1368 ,p_source_type => 'SRT'
1369 ,p_end_date => l_cal_end_date);
1370 END IF;
1371 END IF;
1372
1373 --make the version status as active
1374
1375 l_srv_rec.std_rate_tmpl_ver_id := p_version_id;
1376 l_srv_rec.sts_code := 'ACTIVE';
1377 l_srv_rec.effective_to_date := NULL;
1378 l_srtv_rec.srt_rate := l_rate;
1379
1380 -- fetch the index id to get the value of the rate that has to be submitted
1381
1382 OPEN index_id_csr(l_srt_id);
1383 FETCH index_id_csr INTO l_index_id ;
1384 CLOSE index_id_csr;
1385
1386 IF (l_index_id IS NOT NULL AND l_index_id <> okl_api.g_miss_num) THEN
1387 OPEN cal_rate_csr(l_index_id
1388 ,l_effective_from);
1389 FETCH cal_rate_csr INTO l_srt_rate ;
1390 CLOSE cal_rate_csr;
1391 IF (l_srt_rate IS NULL) THEN
1392 okl_api.set_message(p_app_name => g_app_name
1393 ,p_msg_name => 'OKL_SRT_INDEX_RATE_NOT_AVAIL'
1394 );
1395 RAISE okl_api.g_exception_error;
1396 ELSE
1397 l_srv_rec.srt_rate := l_srt_rate;
1398 l_srtv_rec.srt_rate := l_rate;
1399 END IF;
1400 END IF;
1401
1402 -- get if the rate type is index and populate the rate
1403
1404 okl_srv_pvt.update_row(l_api_version
1405 ,p_init_msg_list
1406 ,x_return_status
1407 ,x_msg_count
1408 ,x_msg_data
1409 ,l_srv_rec
1410 ,x_srv_rec);
1411
1412 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1413 RAISE okl_api.g_exception_unexpected_error;
1414 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1415 RAISE okl_api.g_exception_error;
1416 END IF;
1417 okl_srt_pvt.update_row(l_api_version
1418 ,p_init_msg_list
1419 ,x_return_status
1420 ,x_msg_count
1421 ,x_msg_data
1422 ,l_srtv_rec
1423 ,x_srtv_rec);
1424
1425 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1426 RAISE okl_api.g_exception_unexpected_error;
1427 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1428 RAISE okl_api.g_exception_error;
1429 END IF;
1430
1431 EXCEPTION
1432 WHEN okl_api.g_exception_error THEN
1433 x_return_status := okl_api.handle_exceptions(l_api_name
1434 ,g_pkg_name
1435 ,'OKL_API.G_RET_STS_ERROR'
1436 ,x_msg_count
1437 ,x_msg_data
1438 ,'_PVT');
1439 WHEN okl_api.g_exception_unexpected_error THEN
1440 x_return_status := okl_api.handle_exceptions(l_api_name
1441 ,g_pkg_name
1442 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1443 ,x_msg_count
1444 ,x_msg_data
1445 ,'_PVT');
1446 WHEN OTHERS THEN
1447
1448 IF srt_version_csr%ISOPEN THEN
1449 CLOSE srt_version_csr;
1450 END IF;
1451 x_return_status := okl_api.handle_exceptions(l_api_name
1452 ,g_pkg_name
1453 ,'OTHERS'
1454 ,x_msg_count
1455 ,x_msg_data
1456 ,'_PVT');
1457 END handle_approval;
1458
1459 -- procedure to set the default Standard Rate Template
1460
1461 PROCEDURE update_default(p_api_version IN NUMBER
1462 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1463 ,x_return_status OUT NOCOPY VARCHAR2
1464 ,x_msg_count OUT NOCOPY NUMBER
1465 ,x_msg_data OUT NOCOPY VARCHAR2
1466 ,p_srt_id IN NUMBER) AS
1467
1468 CURSOR default_yn_csr IS
1469 SELECT std_rate_tmpl_id
1470 FROM okl_fe_std_rt_tmp_all_b
1471 WHERE default_yn = 'Y';
1472 l_srtv_rec okl_srtv_rec;
1473 x_srtv_rec okl_srtv_rec;
1474 l_srt_id NUMBER;
1475 l_api_version NUMBER := 1.0;
1476 l_api_name VARCHAR2(40) := 'update_default';
1477 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1478 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1479
1480 BEGIN
1481 l_return_status := okl_api.start_activity(l_api_name
1482 ,g_pkg_name
1483 ,l_init_msg_list
1484 ,l_api_version
1485 ,p_api_version
1486 ,'_PVT'
1487 ,x_return_status);
1488
1489 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1490 RAISE okl_api.g_exception_unexpected_error;
1491 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1492 RAISE okl_api.g_exception_error;
1493 END IF;
1494 OPEN default_yn_csr;
1495 FETCH default_yn_csr INTO l_srt_id ;
1496 CLOSE default_yn_csr;
1497
1498 -- change the default yn flag of this Standard Rate Template to N
1499
1500 IF (l_srt_id IS NOT NULL) THEN
1501 l_srtv_rec.std_rate_tmpl_id := l_srt_id;
1502 l_srtv_rec.default_yn := 'N';
1503
1504 -- update the record
1505
1506 okl_srt_pvt.update_row(l_api_version
1507 ,l_init_msg_list
1508 ,l_return_status
1509 ,x_msg_count
1510 ,x_msg_data
1511 ,l_srtv_rec
1512 ,x_srtv_rec);
1513 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1514 RAISE okl_api.g_exception_unexpected_error;
1515 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1516 RAISE okl_api.g_exception_error;
1517 END IF;
1518 END IF;
1519
1520 -- change the default yn flag to Y for the required one
1521
1522 l_srtv_rec.std_rate_tmpl_id := p_srt_id;
1523 l_srtv_rec.default_yn := 'Y';
1524
1525 -- update the record
1526
1527 okl_srt_pvt.update_row(l_api_version
1528 ,l_init_msg_list
1529 ,l_return_status
1530 ,x_msg_count
1531 ,x_msg_data
1532 ,l_srtv_rec
1533 ,x_srtv_rec);
1534
1535 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1536 RAISE okl_api.g_exception_unexpected_error;
1537 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1538 RAISE okl_api.g_exception_error;
1539 END IF;
1540
1541 --end activity
1542
1543 okl_api.end_activity(x_msg_count
1544 ,x_msg_data);
1545 x_return_status := l_return_status;
1546 EXCEPTION
1547 WHEN okl_api.g_exception_error THEN
1548 x_return_status := okl_api.handle_exceptions(l_api_name
1549 ,g_pkg_name
1550 ,'OKL_API.G_RET_STS_ERROR'
1551 ,x_msg_count
1552 ,x_msg_data
1553 ,'_PVT');
1554 WHEN okl_api.g_exception_unexpected_error THEN
1555 x_return_status := okl_api.handle_exceptions(l_api_name
1556 ,g_pkg_name
1557 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1558 ,x_msg_count
1559 ,x_msg_data
1560 ,'_PVT');
1561 WHEN OTHERS THEN
1562 x_return_status := okl_api.handle_exceptions(l_api_name
1563 ,g_pkg_name
1564 ,'OTHERS'
1565 ,x_msg_count
1566 ,x_msg_data
1567 ,'_PVT');
1568 END update_default;
1569
1570 -- to find the list of all the invalid object refernces
1571
1572 PROCEDURE invalid_objects(p_api_version IN NUMBER
1573 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1574 ,x_return_status OUT NOCOPY VARCHAR2
1575 ,x_msg_count OUT NOCOPY NUMBER
1576 ,x_msg_data OUT NOCOPY VARCHAR2
1577 ,p_version_id IN NUMBER
1578 ,x_obj_tbl OUT NOCOPY invalid_object_tbl) AS
1579
1580 -- cursor to calculate the LRS objects which are referncing this Standard Rate Template
1581
1582 CURSOR lrs_invalids_csr(p_version_id IN NUMBER) IS
1583 SELECT vers.rate_set_version_id id
1584 ,hdr.name name
1585 ,vers.version_number version_number
1586 FROM okl_fe_rate_set_versions vers
1587 ,okl_ls_rt_fctr_sets_v hdr
1588 WHERE vers.rate_set_id = hdr.id
1589 AND vers.std_rate_tmpl_ver_id = p_version_id
1590 AND vers.sts_code = 'ACTIVE';
1591 l_version_id NUMBER := p_version_id;
1592 i NUMBER := 1;
1593 l_api_version NUMBER := 1.0;
1594 l_api_name VARCHAR2(40) := 'invalid_objects';
1595 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1596
1597 BEGIN
1598 x_return_status := okl_api.start_activity(l_api_name
1599 ,g_pkg_name
1600 ,p_init_msg_list
1601 ,l_api_version
1602 ,p_api_version
1603 ,'_PVT'
1604 ,x_return_status);
1605
1606 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1607 RAISE okl_api.g_exception_unexpected_error;
1608 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1609 RAISE okl_api.g_exception_error;
1610 END IF;
1611
1612 FOR lrs_invalid_record IN lrs_invalids_csr(p_version_id) LOOP
1613 x_obj_tbl(i).obj_id := lrs_invalid_record.id;
1614 x_obj_tbl(i).obj_name := lrs_invalid_record.name;
1615 x_obj_tbl(i).obj_version := lrs_invalid_record.version_number;
1616 x_obj_tbl(i).obj_type := 'LRS';
1617 i := i + 1;
1618 END LOOP;
1619
1620 --end activity
1621
1622 okl_api.end_activity(x_msg_count
1623 ,x_msg_data);
1624 x_return_status := l_return_status;
1625 EXCEPTION
1626 WHEN okl_api.g_exception_error THEN
1627 x_return_status := okl_api.handle_exceptions(l_api_name
1628 ,g_pkg_name
1629 ,'OKL_API.G_RET_STS_ERROR'
1630 ,x_msg_count
1631 ,x_msg_data
1632 ,'_PVT');
1633 WHEN okl_api.g_exception_unexpected_error THEN
1634 x_return_status := okl_api.handle_exceptions(l_api_name
1635 ,g_pkg_name
1636 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1637 ,x_msg_count
1638 ,x_msg_data
1639 ,'_PVT');
1640 WHEN OTHERS THEN
1641 x_return_status := okl_api.handle_exceptions(l_api_name
1642 ,g_pkg_name
1643 ,'OTHERS'
1644 ,x_msg_count
1645 ,x_msg_data
1646 ,'_PVT');
1647 END invalid_objects;
1648
1649 -- to calculate the start date of the new version
1650
1651 PROCEDURE calc_start_date(p_api_version IN NUMBER
1652 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1653 ,x_return_status OUT NOCOPY VARCHAR2
1654 ,x_msg_count OUT NOCOPY NUMBER
1655 ,x_msg_data OUT NOCOPY VARCHAR2
1656 ,p_srv_rec IN okl_srv_rec
1657 ,x_cal_eff_from OUT NOCOPY DATE) AS
1658
1659 TYPE l_start_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1660 l_api_name VARCHAR2(40) := 'calc_start_date';
1661 l_api_version NUMBER := 1.0;
1662 l_srv_rec okl_srv_rec := p_srv_rec;
1663 l_eff_from DATE;
1664 l_eff_to DATE;
1665 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1666 l_start_date l_start_date_type;
1667 l_max_start_date DATE;
1668 i NUMBER;
1669
1670 -- cursor to fetch the maximum start date of lease quotes referencing Standard Rate Template
1671
1672 CURSOR srt_lq_csr(p_version_id IN NUMBER) IS
1673 SELECT MAX(lq.expected_start_date) start_date
1674 FROM okl_lease_quotes_b lq
1675 ,okl_fe_std_rt_tmp_vers srt
1676 WHERE srt.std_rate_tmpl_ver_id = p_version_id
1677 AND lq.rate_template_id = srt.std_rate_tmpl_ver_id;
1678
1679 -- cursor to fetch the maximum start date of quick quotes referencing Standard Rate Template
1680
1681 CURSOR srt_qq_csr(p_version_id IN NUMBER) IS
1682 SELECT MAX(qq.expected_start_date) start_date
1683 FROM okl_quick_quotes_b qq
1684 ,okl_fe_std_rt_tmp_vers srt
1685 WHERE srt.std_rate_tmpl_ver_id = p_version_id
1686 AND qq.rate_template_id = srt.std_rate_tmpl_ver_id;
1687
1688 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets
1689
1690 CURSOR lrs_lq_csr(p_version_id IN NUMBER) IS
1691 SELECT MAX(expected_start_date) start_date
1692 FROM okl_lease_quotes_b
1693 WHERE rate_card_id IN(SELECT rate_set_version_id
1694 FROM okl_fe_rate_set_versions
1695 WHERE std_rate_tmpl_ver_id = p_version_id);
1696
1697 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets
1698
1699 CURSOR lrs_qq_csr(p_version_id IN NUMBER) IS
1700 SELECT MAX(expected_start_date) start_date
1701 FROM okl_quick_quotes_b
1702 WHERE rate_card_id IN(SELECT rate_set_version_id
1703 FROM okl_fe_rate_set_versions
1704 WHERE std_rate_tmpl_ver_id = p_version_id);
1705
1706 -- cursor to fetch the start date and the end of the previous version
1707
1708 CURSOR prev_ver_csr(p_srt_id IN NUMBER
1709 ,p_ver_number IN VARCHAR2) IS
1710 SELECT effective_from_date
1711 ,effective_to_date
1712 FROM okl_fe_std_rt_tmp_vers
1713 WHERE std_rate_tmpl_id = p_srt_id AND version_number = p_ver_number - 1;
1714
1715 CURSOR get_elig_crit_start_date(p_version_id IN NUMBER) IS
1716 SELECT max(effective_from_date)
1717 FROM okl_fe_criteria_set ech
1718 ,okl_fe_criteria ecl
1719 WHERE ecl.criteria_set_id = ech.criteria_set_id
1720 AND ech.source_id = p_version_id AND source_object_code = 'SRT';
1721 BEGIN
1722 l_return_status := okl_api.start_activity(l_api_name
1723 ,g_pkg_name
1724 ,p_init_msg_list
1725 ,l_api_version
1726 ,p_api_version
1727 ,'_PVT'
1728 ,x_return_status);
1729
1730 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1731 RAISE okl_api.g_exception_unexpected_error;
1732 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1733 RAISE okl_api.g_exception_error;
1734 END IF;
1735
1736 --fetch the data from the different cursors
1737
1738 OPEN prev_ver_csr(l_srv_rec.std_rate_tmpl_id
1739 ,l_srv_rec.version_number);
1740 FETCH prev_ver_csr INTO l_eff_from
1741 ,l_eff_to ;
1742 IF (prev_ver_csr%FOUND) THEN
1743 -- if the effective to date of the previous version is not null
1744
1745 IF (l_eff_to IS NOT NULL) THEN
1746 l_max_start_date := l_eff_to + 1;
1747 ELSE
1748 l_max_start_date := l_eff_from + 1;
1749 END IF;
1750 ELSE
1751 l_max_start_date:= okl_api.g_miss_date;
1752 END IF;
1753 CLOSE prev_ver_csr;
1754
1755 IF (l_eff_to IS NULL) THEN
1756
1757 -- calculate the maximum start date
1758 OPEN srt_lq_csr(l_srv_rec.std_rate_tmpl_ver_id);
1759 FETCH srt_lq_csr INTO l_start_date(0) ;
1760 CLOSE srt_lq_csr;
1761
1762 OPEN srt_qq_csr(l_srv_rec.std_rate_tmpl_ver_id);
1763 FETCH srt_qq_csr INTO l_start_date(1) ;
1764 CLOSE srt_qq_csr;
1765
1766 OPEN lrs_lq_csr(l_srv_rec.std_rate_tmpl_ver_id);
1767 FETCH lrs_lq_csr INTO l_start_date(2) ;
1768 CLOSE lrs_lq_csr;
1769
1770 OPEN lrs_qq_csr(l_srv_rec.std_rate_tmpl_ver_id);
1771 FETCH lrs_qq_csr INTO l_start_date(3) ;
1772 CLOSE lrs_qq_csr;
1773
1774 OPEN get_elig_crit_start_date(l_srv_rec.std_rate_tmpl_ver_id);
1775 FETCH get_elig_crit_start_date INTO l_start_date(4);
1776 CLOSE get_elig_crit_start_date;
1777
1778 FOR i IN l_start_date.FIRST..l_start_date.LAST LOOP
1779
1780 IF (l_start_date(i) IS NOT NULL AND (l_start_date(i)+1) > l_max_start_date) THEN
1781 l_max_start_date := l_start_date(i)+1;
1782 END IF;
1783
1784 END LOOP;
1785
1786 END IF;
1787
1788 -- assign the max start date to the out parameter
1789
1790 x_cal_eff_from := l_max_start_date;
1791
1792 --end activity
1793
1794 okl_api.end_activity(x_msg_count
1795 ,x_msg_data);
1796 x_return_status := l_return_status;
1797 EXCEPTION
1798 WHEN okl_api.g_exception_error THEN
1799 x_return_status := okl_api.handle_exceptions(l_api_name
1800 ,g_pkg_name
1801 ,'OKL_API.G_RET_STS_ERROR'
1802 ,x_msg_count
1803 ,x_msg_data
1804 ,'_PVT');
1805 WHEN okl_api.g_exception_unexpected_error THEN
1806 x_return_status := okl_api.handle_exceptions(l_api_name
1807 ,g_pkg_name
1808 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1809 ,x_msg_count
1810 ,x_msg_data
1811 ,'_PVT');
1812 WHEN OTHERS THEN
1813 x_return_status := okl_api.handle_exceptions(l_api_name
1814 ,g_pkg_name
1815 ,'OTHERS'
1816 ,x_msg_count
1817 ,x_msg_data
1818 ,'_PVT');
1819 END calc_start_date;
1820
1821 --this api should be called to end date any ACTIVE srt version
1822
1823 PROCEDURE enddate_std_rate_tmpl(p_api_version IN NUMBER
1824 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1825 ,x_return_status OUT NOCOPY VARCHAR2
1826 ,x_msg_count OUT NOCOPY NUMBER
1827 ,x_msg_data OUT NOCOPY VARCHAR2
1828 ,p_srv_id_tbl IN okl_number_table
1829 ,p_end_date IN DATE) IS
1830
1831 CURSOR is_latest_version(p_srt_version_id IN NUMBER
1832 ,p_srt_id IN NUMBER) IS
1833 SELECT 'X'
1834 FROM okl_fe_std_rt_tmp_vers
1835 WHERE version_number = (SELECT MAX(version_number)
1836 FROM okl_fe_std_rt_tmp_vers
1837 WHERE std_rate_tmpl_id = p_srt_id)
1838 AND std_rate_tmpl_ver_id = p_srt_version_id;
1839
1840 CURSOR get_not_abn_versions(p_srt_version_id IN NUMBER
1841 ,p_srt_id IN NUMBER) IS
1842 SELECT 'X'
1843 FROM okl_fe_std_rt_tmp_vers
1844 WHERE std_rate_tmpl_id = p_srt_id
1845 AND std_rate_tmpl_ver_id <> p_srt_version_id
1846 AND sts_code <> 'ABANDONED';
1847
1848 -- cursor to fetch the effective from and effective to date
1849
1850 CURSOR get_effective_dates_csr(p_srt_version_id IN NUMBER) IS
1851 SELECT effective_from_date
1852 ,effective_to_date
1853 ,sts_code
1854 ,std_rate_tmpl_id
1855 FROM okl_fe_std_rt_tmp_vers
1856 WHERE std_rate_tmpl_ver_id = p_srt_version_id;
1857
1858 -- cursor to calculate the LRS objects which are referncing this Standard Rate Template
1859
1860 CURSOR lrs_invalids_csr(p_version_id IN NUMBER) IS
1861 SELECT vers.rate_set_version_id id
1862 ,hdr.name name
1863 ,vers.version_number version_number
1864 FROM okl_fe_rate_set_versions vers
1865 ,okl_ls_rt_fctr_sets_v hdr
1866 WHERE vers.rate_set_id = hdr.id
1867 AND vers.std_rate_tmpl_ver_id = p_version_id
1868 AND vers.sts_code = 'ACTIVE';
1869 lp_srv_rec okl_srv_rec;
1870 lx_srv_rec okl_srv_rec;
1871 lp_srtv_rec okl_srtv_rec;
1872 lx_srtv_rec okl_srtv_rec;
1873 l_srt_id_list VARCHAR2(4000);
1874 l_no_data_found BOOLEAN;
1875 l_update_header BOOLEAN;
1876 l_update_version BOOLEAN;
1877 l_effective_from DATE;
1878 l_effective_to DATE;
1879 l_sts_code VARCHAR2(30);
1880 l_srt_id NUMBER;
1881 l_api_name CONSTANT VARCHAR2(30) := 'enddate_std_rate_tmpl';
1882 l_api_version CONSTANT NUMBER := 1.0;
1883 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1884 l_dummy VARCHAR2(1) := '?';
1885 lp_lrtv_tbl okl_lrs_id_tbl;
1886 n NUMBER :=1;
1887
1888 BEGIN
1889
1890 -- call START_ACTIVITY to create savepoint, check compatibility
1891 -- and initialize message list
1892
1893 l_return_status := okl_api.start_activity(l_api_name
1894 ,g_pkg_name
1895 ,p_init_msg_list
1896 ,l_api_version
1897 ,p_api_version
1898 ,'_PVT'
1899 ,x_return_status);
1900
1901 -- check if activity started successfully
1902
1903 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1904 RAISE okl_api.g_exception_unexpected_error;
1905 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1906 RAISE okl_api.g_exception_error;
1907 END IF;
1908
1909 FOR i IN p_srv_id_tbl.FIRST..p_srv_id_tbl.LAST LOOP
1910 OPEN get_effective_dates_csr(p_srv_id_tbl(i));
1911 FETCH get_effective_dates_csr INTO l_effective_from
1912 ,l_effective_to
1913 ,l_sts_code
1914 ,l_srt_id ;
1915 CLOSE get_effective_dates_csr;
1916 lp_srv_rec.effective_from_date := l_effective_from;
1917 lp_srv_rec.effective_to_date := l_effective_to;
1918 lp_srv_rec.sts_code := l_sts_code;
1919 lp_srv_rec.std_rate_tmpl_id := l_srt_id;
1920 lp_srv_rec.std_rate_tmpl_ver_id := p_srv_id_tbl(i);
1921 l_update_header := false;
1922 l_update_version := false;
1923
1924 IF lp_srv_rec.effective_from_date <= p_end_date THEN
1925 IF lp_srv_rec.effective_to_date IS NULL THEN
1926 lp_srv_rec.effective_to_date := p_end_date;
1927 l_update_version := true;
1928
1929 --if this is the latest version then put end date on header
1930
1931 OPEN is_latest_version(p_srv_id_tbl(i)
1932 ,lp_srv_rec.std_rate_tmpl_id);
1933 FETCH is_latest_version INTO l_dummy ;
1934 CLOSE is_latest_version;
1935 IF l_dummy <> '?' THEN
1936 lp_srtv_rec.effective_to_date := p_end_date;
1937 lp_srtv_rec.std_rate_tmpl_id := l_srt_id;
1938 l_update_header := true;
1939 END IF;
1940 END IF;
1941 ELSE
1942 lp_srv_rec.sts_code := 'ABANDONED';
1943 lp_srv_rec.std_rate_tmpl_ver_id := p_srv_id_tbl(i);
1944 l_update_version := true;
1945
1946 --if all versions are abandoned then make header status as abandoned
1947
1948 OPEN get_not_abn_versions(p_srv_id_tbl(i)
1949 ,lp_srv_rec.std_rate_tmpl_id);
1950 FETCH get_not_abn_versions INTO l_dummy ;
1951 CLOSE get_not_abn_versions;
1952 IF l_dummy = '?' THEN
1953 lp_srtv_rec.sts_code := 'ABANDONED';
1954 lp_srtv_rec.std_rate_tmpl_id := l_srt_id;
1955 l_update_header := true;
1956 END IF;
1957
1958 --if this is the latest version then put end date on header and version
1959
1960 OPEN is_latest_version(p_srv_id_tbl(i)
1961 ,lp_srv_rec.std_rate_tmpl_id);
1962 FETCH is_latest_version INTO l_dummy ;
1963 CLOSE is_latest_version;
1964 IF l_dummy <> '?' THEN
1965
1966 --put end date on version
1967
1968 lp_srv_rec.effective_to_date := lp_srv_rec.effective_from_date;
1969 l_update_version := true;
1970
1971 --put end date on header
1972
1973 lp_srtv_rec.effective_to_date := lp_srv_rec.effective_from_date;
1974 l_update_header := true;
1975 END IF;
1976 END IF;
1977 -- end date the lease rate sets referencing this SRT
1978
1979 FOR x_lrs_id_tbl IN lrs_invalids_csr(p_srv_id_tbl(i)) LOOP
1980 lp_lrtv_tbl(n) := x_lrs_id_tbl.id;
1981 n:=n+1;
1982 END LOOP;
1983 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(p_api_version
1984 ,p_init_msg_list
1985 ,x_return_status
1986 ,x_msg_count
1987 ,x_msg_data
1988 ,lp_lrtv_tbl
1989 ,p_end_date);
1990
1991 --update the version
1992
1993 IF l_update_version THEN
1994 okl_srv_pvt.update_row(l_api_version
1995 ,p_init_msg_list
1996 ,l_return_status
1997 ,x_msg_count
1998 ,x_msg_data
1999 ,lp_srv_rec
2000 ,lx_srv_rec);
2001 IF l_return_status = okl_api.g_ret_sts_error THEN
2002 RAISE okl_api.g_exception_error;
2003 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
2004 RAISE okl_api.g_exception_unexpected_error;
2005 END IF;
2006 END IF;
2007
2008 --update the header
2009
2010 IF l_update_header THEN
2011 okl_srt_pvt.update_row(p_api_version
2012 ,p_init_msg_list
2013 ,l_return_status
2014 ,x_msg_count
2015 ,x_msg_data
2016 ,lp_srtv_rec
2017 ,lx_srtv_rec);
2018 IF l_return_status = okl_api.g_ret_sts_error THEN
2019 RAISE okl_api.g_exception_error;
2020 ELSIF l_return_status = okl_api.g_ret_sts_unexp_error THEN
2021 RAISE okl_api.g_exception_unexpected_error;
2022 END IF;
2023 END IF;
2024
2025 END LOOP;
2026 x_return_status := l_return_status;
2027 okl_api.end_activity(x_msg_count => x_msg_count
2028 ,x_msg_data => x_msg_data);
2029 EXCEPTION
2030 WHEN okl_api.g_exception_error THEN
2031 x_return_status := okl_api.handle_exceptions(l_api_name
2032 ,g_pkg_name
2033 ,'OKL_API.G_RET_STS_ERROR'
2034 ,x_msg_count
2035 ,x_msg_data
2036 ,'_PVT');
2037 WHEN okl_api.g_exception_unexpected_error THEN
2038 x_return_status := okl_api.handle_exceptions(l_api_name
2039 ,g_pkg_name
2040 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2041 ,x_msg_count
2042 ,x_msg_data
2043 ,'_PVT');
2044 WHEN OTHERS THEN
2045 x_return_status := okl_api.handle_exceptions(l_api_name
2046 ,g_pkg_name
2047 ,'OTHERS'
2048 ,x_msg_count
2049 ,x_msg_data
2050 ,'_PVT');
2051 END enddate_std_rate_tmpl;
2052
2053 END okl_fe_std_rate_tmpl_pvt;