[Home] [Help]
PACKAGE BODY: APPS.OKL_SRV_PVT
Source
1 PACKAGE BODY okl_srv_pvt AS
2 /* $Header: OKLSSRVB.pls 120.2 2005/12/23 17:12:41 viselvar noship $ */
3
4 -- The lock_row and the validate_row procedures are not available.
5
6 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
7 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
8 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
9 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
10 g_exception_halt_validation EXCEPTION;
11
12 PROCEDURE api_copy IS
13
14 BEGIN
15 NULL;
16 END api_copy;
17
18 PROCEDURE change_version IS
19
20 BEGIN
21 NULL;
22 END change_version;
23
24 -- validation of Standard Rate Template Version Id
25
26 FUNCTION validate_std_rate_tmpl_ver_id(p_srt_ver_id IN NUMBER) RETURN VARCHAR2 IS
27 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
28
29 BEGIN
30
31 -- STD_RATE_TMPL_ID is a required field
32
33 IF (p_srt_ver_id IS NULL OR p_srt_ver_id = okl_api.g_miss_num) THEN
34 okl_api.set_message(p_app_name => g_app_name
35 ,p_msg_name => g_required_value
36 ,p_token1 => g_col_name_token
37 ,p_token1_value => 'STD_RATE_TMPL_VER_ID');
38
39 -- notify caller of an error
40
41 l_return_status := okl_api.g_ret_sts_error;
42
43 -- halt further validation of this column
44
45 RAISE g_exception_halt_validation;
46 END IF;
47 RETURN(l_return_status);
48 EXCEPTION
49 WHEN g_exception_halt_validation THEN
50
51 -- no proccessing required. Validation can continue with the next column
52
53 NULL;
54 WHEN OTHERS THEN
55
56 -- store SQL error message on message stack for caller
57
58 okl_api.set_message(p_app_name => g_app_name
59 ,p_msg_name => g_unexpected_error
60 ,p_token1 => g_sqlcode_token
61 ,p_token1_value => sqlcode
62 ,p_token2 => g_sqlerrm_token
63 ,p_token2_value => sqlerrm);
64
65 -- notify caller of an UNEXPECTED error
66
67 l_return_status := okl_api.g_ret_sts_unexp_error;
68 RETURN(l_return_status);
69 END validate_std_rate_tmpl_ver_id;
70
71 FUNCTION validate_std_rate_tmpl_id(p_srt_id IN NUMBER) RETURN VARCHAR2 IS
72 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
73
74 CURSOR srt_exists_csr IS
75 SELECT 'x'
76 FROM okl_fe_std_rt_tmp_all_b
77 WHERE std_rate_tmpl_id = p_srt_id;
78 l_dummy_var VARCHAR2(1) := '?';
79
80 BEGIN
81
82 IF (p_srt_id IS NOT NULL AND p_srt_id <> okl_api.g_miss_num) THEN
83 OPEN srt_exists_csr;
84 FETCH srt_exists_csr INTO l_dummy_var ;
85 CLOSE srt_exists_csr;
86
87 -- if l_dummy_var is still set to default, data was not found
88
89 IF (l_dummy_var = '?') THEN
90 okl_api.set_message(p_app_name => g_app_name
91 ,p_msg_name => g_invalid_value
92 ,p_token1 => g_col_name_token
93 ,p_token1_value => 'STD_RATE_TMPL_ID');
94
95 -- notify caller of an error
96
97 x_return_status := okl_api.g_ret_sts_error;
98 RAISE g_exception_halt_validation;
99 END IF;
100 END IF;
101 RETURN x_return_status;
102 EXCEPTION
103 WHEN g_exception_halt_validation THEN
104
105 -- no processing necessary; validation can continue
106 -- with the next column
107
108 RETURN x_return_status;
109 WHEN OTHERS THEN
110
111 -- store SQL error message on message stack for caller
112
113 okl_api.set_message(p_app_name => g_app_name
114 ,p_msg_name => g_unexpected_error
115 ,p_token1 => g_sqlcode_token
116 ,p_token1_value => sqlcode
117 ,p_token2 => g_sqlerrm_token
118 ,p_token2_value => sqlerrm);
119
120 -- notify caller of an UNEXPECTED error
121
122 x_return_status := okl_api.g_ret_sts_unexp_error;
123
124 -- verify that cursor was closed
125
126 IF srt_exists_csr%ISOPEN THEN
127 CLOSE srt_exists_csr;
128 END IF;
129 RETURN x_return_status;
130 END validate_std_rate_tmpl_id;
131
132 FUNCTION validate_effective_from_date(p_effective_from_date IN DATE) RETURN VARCHAR2 IS
133
134 -- initialize the return status
135
136 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
137
138 BEGIN
139
140 IF (p_effective_from_date IS NULL OR p_effective_from_date = okl_api.g_miss_date) THEN
141 okl_api.set_message(p_app_name => g_app_name
142 ,p_msg_name => g_required_value
143 ,p_token1 => g_col_name_token
144 ,p_token1_value => 'EFFECTIVE_FROM_DATE');
145
146 -- notify caller of an error
147
148 x_return_status := okl_api.g_ret_sts_error;
149 RAISE g_exception_halt_validation;
150 END IF;
151 RETURN x_return_status;
152 EXCEPTION
153 WHEN g_exception_halt_validation THEN
154
155 -- no processing necessary; validation can continue
156 -- with the next column
157
158 RETURN x_return_status;
159 WHEN OTHERS THEN
160
161 -- store SQL error message on message stack for caller
162
163 okl_api.set_message(p_app_name => g_app_name
164 ,p_msg_name => g_unexpected_error
165 ,p_token1 => g_sqlcode_token
166 ,p_token1_value => sqlcode
167 ,p_token2 => g_sqlerrm_token
168 ,p_token2_value => sqlerrm);
169
170 -- notify caller of an UNEXPECTED error
171
172 x_return_status := okl_api.g_ret_sts_unexp_error;
173 RETURN x_return_status;
174 END validate_effective_from_date;
175
176 FUNCTION validate_sts_code(p_sts_code IN VARCHAR2) RETURN VARCHAR2 IS
177
178 --initialize the Return Status
179
180 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
181
182 BEGIN
183
184 -- Column is mandatory
185
186 IF (p_sts_code IS NULL OR p_sts_code = okl_api.g_miss_char) THEN
187 okl_api.set_message(p_app_name => g_app_name
188 ,p_msg_name => g_required_value
189 ,p_token1 => g_col_name_token
190 ,p_token1_value => 'sts_code');
191
192 -- notify caller of an error
193
194 x_return_status := okl_api.g_ret_sts_error;
195 RAISE g_exception_halt_validation;
196 END IF;
197
198 -- Lookup Code Validation
199
200 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
201 ,p_lookup_code => p_sts_code);
202
203 IF (x_return_status = okl_api.g_ret_sts_error) THEN
204 okl_api.set_message(p_app_name => g_app_name
205 ,p_msg_name => g_invalid_value
206 ,p_token1 => g_col_name_token
207 ,p_token1_value => 'sts_code'); -- notify caller of an error
208 RAISE g_exception_halt_validation;
209 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
210
211 -- notify caller of an error
212
213 x_return_status := okl_api.g_ret_sts_unexp_error;
214 RAISE g_exception_halt_validation;
215 END IF;
216 RETURN x_return_status;
217 EXCEPTION
218 WHEN g_exception_halt_validation THEN
219
220 -- no processing necessary; validation can continue
221 -- with the next column
222
223 RETURN x_return_status;
224 WHEN OTHERS THEN
225
226 -- store SQL error message on message stack for caller
227
228 okl_api.set_message(p_app_name => g_app_name
229 ,p_msg_name => g_unexpected_error
230 ,p_token1 => g_sqlcode_token
231 ,p_token1_value => sqlcode
232 ,p_token2 => g_sqlerrm_token
233 ,p_token2_value => sqlerrm);
234
235 -- notify caller of an UNEXPECTED error
236
237 x_return_status := okl_api.g_ret_sts_unexp_error;
238 RETURN x_return_status;
239 END validate_sts_code;
240
241 FUNCTION validate_adj_mat_version_id(p_adj_mat_version_id IN NUMBER) RETURN VARCHAR2 IS
242 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
243
244 CURSOR pam_exists_csr IS
245 SELECT 'x'
246 FROM okl_fe_adj_mat_versions
247 WHERE adj_mat_version_id = p_adj_mat_version_id;
248 l_dummy_var VARCHAR2(1) := '?';
249
250 BEGIN
251
252 IF (p_adj_mat_version_id IS NOT NULL AND p_adj_mat_version_id <> okl_api.g_miss_num) THEN
253 OPEN pam_exists_csr;
254 FETCH pam_exists_csr INTO l_dummy_var ;
255 CLOSE pam_exists_csr;
256
257 -- if l_dummy_var is still set to default, data was not found
258
259 IF (l_dummy_var = '?') THEN
260 okl_api.set_message(p_app_name => g_app_name
261 ,p_msg_name => g_invalid_value
262 ,p_token1 => g_col_name_token
263 ,p_token1_value => 'ADJ_MAT_VERSION_ID');
264
265 -- notify caller of an error
266
267 x_return_status := okl_api.g_ret_sts_error;
268 RAISE g_exception_halt_validation;
269 END IF;
270 END IF;
271 RETURN x_return_status;
272 EXCEPTION
273 WHEN g_exception_halt_validation THEN
274
275 -- no processing necessary; validation can continue
276 -- with the next column
277
278 RETURN x_return_status;
279 WHEN OTHERS THEN
280
281 -- store SQL error message on message stack for caller
282
283 okl_api.set_message(p_app_name => g_app_name
284 ,p_msg_name => g_unexpected_error
285 ,p_token1 => g_sqlcode_token
286 ,p_token1_value => sqlcode
287 ,p_token2 => g_sqlerrm_token
288 ,p_token2_value => sqlerrm);
289
290 -- notify caller of an UNEXPECTED error
291
292 x_return_status := okl_api.g_ret_sts_unexp_error;
293
294 -- verify that cursor was closed
295
296 IF pam_exists_csr%ISOPEN THEN
297 CLOSE pam_exists_csr;
298 END IF;
299 RETURN x_return_status;
300 END validate_adj_mat_version_id;
301
302 FUNCTION validate_srt_rate(p_srt_rate IN NUMBER) RETURN VARCHAR2 IS
303
304 -- initialize the return status
305
306 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
307
308 BEGIN
309
310 IF (p_srt_rate IS NULL OR p_srt_rate = okl_api.g_miss_num) THEN
311 okl_api.set_message(p_app_name => g_app_name
312 ,p_msg_name => g_required_value
313 ,p_token1 => g_col_name_token
314 ,p_token1_value => 'SRT_RATE');
315
316 -- notify caller of an error
317
318 x_return_status := okl_api.g_ret_sts_error;
319 RAISE g_exception_halt_validation;
320 END IF;
321 RETURN x_return_status;
322 EXCEPTION
323 WHEN g_exception_halt_validation THEN
324
325 -- no processing necessary; validation can continue
326 -- with the next column
327
328 RETURN x_return_status;
329 WHEN OTHERS THEN
330
331 -- store SQL error message on message stack for caller
332
333 okl_api.set_message(p_app_name => g_app_name
334 ,p_msg_name => g_unexpected_error
335 ,p_token1 => g_sqlcode_token
336 ,p_token1_value => sqlcode
337 ,p_token2 => g_sqlerrm_token
338 ,p_token2_value => sqlerrm);
339
340 -- notify caller of an UNEXPECTED error
341
342 x_return_status := okl_api.g_ret_sts_unexp_error;
343 RETURN x_return_status;
344 END validate_srt_rate;
345
346 FUNCTION validate_day_convention_code(p_day_convention_code IN VARCHAR2) RETURN VARCHAR2 IS
347 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
348
349 BEGIN
350
351 IF (p_day_convention_code IS NULL) OR (p_day_convention_code = okl_api.g_miss_char) THEN
352 okl_api.set_message(p_app_name => g_app_name
353 ,p_msg_name => g_required_value
354 ,p_token1 => g_col_name_token
355 ,p_token1_value => 'DAY_CONVENTION_CODE');
356
357 -- notify caller of an error
358
359 x_return_status := okl_api.g_ret_sts_error;
360
361 -- halt further validation of this column
362
363 RAISE g_exception_halt_validation;
364 ELSE
365
366 -- Lookup Code Validation
367
368 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YEAR_TYPE'
369 ,p_lookup_code => p_day_convention_code);
370 IF (x_return_status = okl_api.g_ret_sts_error) THEN
371 okl_api.set_message(p_app_name => g_app_name
372 ,p_msg_name => g_invalid_value
373 ,p_token1 => g_col_name_token
374 ,p_token1_value => 'DAY_CONVENTION_CODE'); -- notify caller of an error
375 RAISE g_exception_halt_validation;
376 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
377
378 -- notify caller of an error
379
380 x_return_status := okl_api.g_ret_sts_unexp_error;
381 RAISE g_exception_halt_validation;
382 END IF;
383 END IF;
384 RETURN(x_return_status);
385 EXCEPTION
386 WHEN g_exception_halt_validation THEN
387
388 -- no processing necessary; validation can continue
389 -- with the next column
390
391 RETURN(x_return_status);
392 WHEN OTHERS THEN
393
394 -- store SQL error message on message stack for caller
395
396 okl_api.set_message(p_app_name => g_app_name
397 ,p_msg_name => g_unexpected_error
398 ,p_token1 => g_sqlcode_token
402
399 ,p_token1_value => sqlcode
400 ,p_token2 => g_sqlerrm_token
401 ,p_token2_value => sqlerrm);
403 -- notify caller of an UNEXPECTED error
404
405 x_return_status := okl_api.g_ret_sts_unexp_error;
406 RETURN(x_return_status);
407 END validate_day_convention_code;
408
409 --------------------------------------------------------------------------------
410 -- Procedure get_rec for OKL_FE_STD_RT_TMP_VERS
411 --------------------------------------------------------------------------------
412
413 FUNCTION get_rec(p_srv_rec IN okl_srv_rec
414 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_srv_rec IS
415
416 CURSOR srv_pk_csr(p_id IN NUMBER) IS
417 SELECT std_rate_tmpl_ver_id
418 ,object_version_number
419 ,version_number
420 ,std_rate_tmpl_id
421 ,effective_from_date
422 ,effective_to_date
423 ,sts_code
424 ,adj_mat_version_id
425 ,srt_rate
426 ,spread
427 ,day_convention_code
428 ,min_adj_rate
429 ,max_adj_rate
430 ,attribute_category
431 ,attribute1
432 ,attribute2
433 ,attribute3
434 ,attribute4
435 ,attribute5
436 ,attribute6
437 ,attribute7
438 ,attribute8
439 ,attribute9
440 ,attribute10
441 ,attribute11
442 ,attribute12
443 ,attribute13
444 ,attribute14
445 ,attribute15
446 ,created_by
447 ,creation_date
448 ,last_updated_by
449 ,last_update_date
450 ,last_update_login
451 FROM okl_fe_std_rt_tmp_vers
452 WHERE okl_fe_std_rt_tmp_vers.std_rate_tmpl_ver_id = p_id;
453 l_srv_pk srv_pk_csr%ROWTYPE;
454 l_srv_rec okl_srv_rec;
455
456 BEGIN
457 x_no_data_found := true;
458
459 --Get current data base values
460
461 OPEN srv_pk_csr(p_srv_rec.std_rate_tmpl_ver_id);
462 FETCH srv_pk_csr INTO l_srv_rec.std_rate_tmpl_ver_id
463 ,l_srv_rec.object_version_number
464 ,l_srv_rec.version_number
465 ,l_srv_rec.std_rate_tmpl_id
466 ,l_srv_rec.effective_from_date
467 ,l_srv_rec.effective_to_date
468 ,l_srv_rec.sts_code
469 ,l_srv_rec.adj_mat_version_id
470 ,l_srv_rec.srt_rate
471 ,l_srv_rec.spread
472 ,l_srv_rec.day_convention_code
473 ,l_srv_rec.min_adj_rate
474 ,l_srv_rec.max_adj_rate
475 ,l_srv_rec.attribute_category
476 ,l_srv_rec.attribute1
477 ,l_srv_rec.attribute2
478 ,l_srv_rec.attribute3
479 ,l_srv_rec.attribute4
480 ,l_srv_rec.attribute5
481 ,l_srv_rec.attribute6
482 ,l_srv_rec.attribute7
483 ,l_srv_rec.attribute8
484 ,l_srv_rec.attribute9
485 ,l_srv_rec.attribute10
486 ,l_srv_rec.attribute11
487 ,l_srv_rec.attribute12
488 ,l_srv_rec.attribute13
489 ,l_srv_rec.attribute14
490 ,l_srv_rec.attribute15
491 ,l_srv_rec.created_by
492 ,l_srv_rec.creation_date
493 ,l_srv_rec.last_updated_by
494 ,l_srv_rec.last_update_date
495 ,l_srv_rec.last_update_login ;
496 x_no_data_found := srv_pk_csr%NOTFOUND;
497 CLOSE srv_pk_csr;
498 RETURN(l_srv_rec);
499 END get_rec;
500
501 FUNCTION get_rec(p_srv_rec IN okl_srv_rec) RETURN okl_srv_rec IS
502 l_row_notfound BOOLEAN := true;
503
504 BEGIN
505 RETURN(get_rec(p_srv_rec
506 ,l_row_notfound));
507 END get_rec;
508
509 FUNCTION null_out_defaults(p_srv_rec IN okl_srv_rec) RETURN okl_srv_rec IS
510 l_srv_rec okl_srv_rec := p_srv_rec;
511
512 BEGIN
513
514 IF (l_srv_rec.std_rate_tmpl_ver_id = okl_api.g_miss_num) THEN
515 l_srv_rec.std_rate_tmpl_ver_id := NULL;
516 END IF;
517
518 IF (l_srv_rec.object_version_number = okl_api.g_miss_num) THEN
519 l_srv_rec.object_version_number := NULL;
520 END IF;
521
522 IF (l_srv_rec.version_number = okl_api.g_miss_char) THEN
523 l_srv_rec.version_number := NULL;
524 END IF;
525
526 IF (l_srv_rec.std_rate_tmpl_id = okl_api.g_miss_num) THEN
527 l_srv_rec.std_rate_tmpl_id := NULL;
528 END IF;
529
533
530 IF (l_srv_rec.effective_from_date = okl_api.g_miss_date) THEN
531 l_srv_rec.effective_from_date := NULL;
532 END IF;
534 IF (l_srv_rec.effective_to_date = okl_api.g_miss_date) THEN
535 l_srv_rec.effective_to_date := NULL;
536 END IF;
537
538 IF (l_srv_rec.sts_code = okl_api.g_miss_char) THEN
539 l_srv_rec.sts_code := NULL;
540 END IF;
541
542 IF (l_srv_rec.adj_mat_version_id = okl_api.g_miss_num) THEN
543 l_srv_rec.adj_mat_version_id := NULL;
544 END IF;
545
546 IF (l_srv_rec.srt_rate = okl_api.g_miss_num) THEN
547 l_srv_rec.srt_rate := NULL;
548 END IF;
549
550 IF (l_srv_rec.spread = okl_api.g_miss_num) THEN
551 l_srv_rec.spread := NULL;
552 END IF;
553
554 IF (l_srv_rec.day_convention_code = okl_api.g_miss_char) THEN
555 l_srv_rec.day_convention_code := NULL;
556 END IF;
557
558 IF (l_srv_rec.min_adj_rate = okl_api.g_miss_num) THEN
559 l_srv_rec.min_adj_rate := NULL;
560 END IF;
561
562 IF (l_srv_rec.max_adj_rate = okl_api.g_miss_num) THEN
563 l_srv_rec.max_adj_rate := NULL;
564 END IF;
565
566 IF (l_srv_rec.attribute_category = okl_api.g_miss_char) THEN
567 l_srv_rec.attribute_category := NULL;
568 END IF;
569
570 IF (l_srv_rec.attribute1 = okl_api.g_miss_char) THEN
571 l_srv_rec.attribute1 := NULL;
572 END IF;
573
574 IF (l_srv_rec.attribute2 = okl_api.g_miss_char) THEN
575 l_srv_rec.attribute2 := NULL;
576 END IF;
577
578 IF (l_srv_rec.attribute3 = okl_api.g_miss_char) THEN
579 l_srv_rec.attribute3 := NULL;
580 END IF;
581
582 IF (l_srv_rec.attribute4 = okl_api.g_miss_char) THEN
583 l_srv_rec.attribute4 := NULL;
584 END IF;
585
586 IF (l_srv_rec.attribute5 = okl_api.g_miss_char) THEN
587 l_srv_rec.attribute5 := NULL;
588 END IF;
589
590 IF (l_srv_rec.attribute6 = okl_api.g_miss_char) THEN
591 l_srv_rec.attribute6 := NULL;
592 END IF;
593
594 IF (l_srv_rec.attribute7 = okl_api.g_miss_char) THEN
595 l_srv_rec.attribute7 := NULL;
596 END IF;
597
598 IF (l_srv_rec.attribute8 = okl_api.g_miss_char) THEN
599 l_srv_rec.attribute8 := NULL;
600 END IF;
601
602 IF (l_srv_rec.attribute9 = okl_api.g_miss_char) THEN
603 l_srv_rec.attribute9 := NULL;
604 END IF;
605
606 IF (l_srv_rec.attribute10 = okl_api.g_miss_char) THEN
607 l_srv_rec.attribute10 := NULL;
608 END IF;
609
610 IF (l_srv_rec.attribute11 = okl_api.g_miss_char) THEN
611 l_srv_rec.attribute11 := NULL;
612 END IF;
613
614 IF (l_srv_rec.attribute12 = okl_api.g_miss_char) THEN
615 l_srv_rec.attribute12 := NULL;
616 END IF;
617
618 IF (l_srv_rec.attribute13 = okl_api.g_miss_char) THEN
619 l_srv_rec.attribute13 := NULL;
620 END IF;
621
622 IF (l_srv_rec.attribute14 = okl_api.g_miss_char) THEN
623 l_srv_rec.attribute14 := NULL;
624 END IF;
625
626 IF (l_srv_rec.attribute15 = okl_api.g_miss_char) THEN
627 l_srv_rec.attribute15 := NULL;
628 END IF;
629
630 IF (l_srv_rec.created_by = okl_api.g_miss_num) THEN
631 l_srv_rec.created_by := NULL;
632 END IF;
633
634 IF (l_srv_rec.creation_date = okl_api.g_miss_date) THEN
635 l_srv_rec.creation_date := NULL;
636 END IF;
637
638 IF (l_srv_rec.last_updated_by = okl_api.g_miss_num) THEN
639 l_srv_rec.last_updated_by := NULL;
640 END IF;
641
642 IF (l_srv_rec.last_update_date = okl_api.g_miss_date) THEN
643 l_srv_rec.last_update_date := NULL;
644 END IF;
645
646 IF (l_srv_rec.last_update_login = okl_api.g_miss_num) THEN
647 l_srv_rec.last_update_login := NULL;
648 END IF;
649 RETURN(l_srv_rec);
650 END null_out_defaults;
651
652 FUNCTION get_seq_id RETURN NUMBER IS
653
654 BEGIN
655 RETURN(okc_p_util.raw_to_number(sys_guid()));
656 END get_seq_id;
657
658 FUNCTION validate_attributes(p_srv_rec IN okl_srv_rec) RETURN VARCHAR2 IS
659 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
660 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
661
662 BEGIN
663
664 -- validate the Standard Rate Template Version Id
665
666 l_return_status := validate_std_rate_tmpl_ver_id(p_srv_rec.std_rate_tmpl_ver_id);
667
668 -- store the highest degree of error
669
670 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
671 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
672 x_return_status := l_return_status;
673 END IF;
674 END IF;
675
676 -- validate the Standard Rate Template id
677
678 l_return_status := validate_std_rate_tmpl_id(p_srv_rec.std_rate_tmpl_id);
679
680 -- store the highest degree of error
681
682 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
683 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
684 x_return_status := l_return_status;
685 END IF;
686 END IF;
687
691
688 -- validate the Effective From
689
690 l_return_status := validate_effective_from_date(p_srv_rec.effective_from_date);
692 -- store the highest degree of error
693
694 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
695 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
696 x_return_status := l_return_status;
697 END IF;
698 END IF;
699
700 -- validate the Status Code
701
702 l_return_status := validate_sts_code(p_srv_rec.sts_code);
703
704 -- store the highest degree of error
705
706 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
707 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
708 x_return_status := l_return_status;
709 END IF;
710 END IF;
711
712 -- validate the Adjustment matrix version id
713
714 l_return_status := validate_adj_mat_version_id(p_srv_rec.adj_mat_version_id);
715
716 -- store the highest degree of error
717
718 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
719 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
720 x_return_status := l_return_status;
721 END IF;
722 END IF;
723
724 -- validate the Rate
725
726 l_return_status := validate_srt_rate(p_srv_rec.srt_rate);
727
728 -- store the highest degree of error
729
730 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
731 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
732 x_return_status := l_return_status;
733 END IF;
734 END IF;
735
736 -- validate the Status Code
737
738 l_return_status := validate_sts_code(p_srv_rec.sts_code);
739
740 -- store the highest degree of error
741
742 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
743 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
744 x_return_status := l_return_status;
745 END IF;
746 END IF;
747
748 -- validate the Day convention code
749
750 l_return_status := validate_day_convention_code(p_srv_rec.day_convention_code);
751
752 -- store the highest degree of error
753
754 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
755 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
756 x_return_status := l_return_status;
757 END IF;
758 END IF;
759 RETURN(x_return_status);
760 END validate_attributes;
761
762 FUNCTION validate_record(p_srv_rec IN okl_srv_rec) RETURN VARCHAR2 IS
763 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
764
765 BEGIN
766
767 IF (p_srv_rec.max_adj_rate IS NOT NULL AND p_srv_rec.min_adj_rate IS NOT NULL) THEN
768 IF (p_srv_rec.max_adj_rate < p_srv_rec.min_adj_rate) THEN
769 okl_api.set_message(p_app_name => g_app_name
770 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO' );
771
772 -- notify caller of an error
773
774 x_return_status := okl_api.g_ret_sts_error;
775 RAISE g_exception_halt_validation;
776 END IF;
777 END IF;
778 if (p_srv_rec.effective_to_date is not null) then
779 if (p_srv_rec.effective_from_date > p_srv_rec.effective_to_date) then
780 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
781 p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
782
783 x_return_status := okl_api.g_ret_sts_error;
784
785 end if;
786 end if;
787 RETURN(x_return_status);
788 EXCEPTION
789 WHEN g_exception_halt_validation THEN
790
791 -- no processing necessary; validation can continue
792 -- with the next column
793
794 RETURN x_return_status;
795 WHEN OTHERS THEN
796
797 -- store SQL error message on message stack for caller
798
799 okl_api.set_message(p_app_name => g_app_name
800 ,p_msg_name => g_unexpected_error
801 ,p_token1 => g_sqlcode_token
802 ,p_token1_value => sqlcode
803 ,p_token2 => g_sqlerrm_token
804 ,p_token2_value => sqlerrm);
805
806 -- notify caller of an UNEXPECTED error
807
808 x_return_status := okl_api.g_ret_sts_unexp_error;
809 RETURN x_return_status;
810 END validate_record;
811
812 PROCEDURE lock_row(p_api_version IN NUMBER
813 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
814 ,x_return_status OUT NOCOPY VARCHAR2
815 ,x_msg_count OUT NOCOPY NUMBER
816 ,x_msg_data OUT NOCOPY VARCHAR2
817 ,p_def_srv_rec IN okl_srv_rec) IS
818 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'lock_row (REC)';
819 e_resource_busy EXCEPTION;
820
821 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
822
823 CURSOR lock_csr(p_def_srv_rec IN okl_srv_rec) IS
827 AND object_version_number = p_def_srv_rec.object_version_number
824 SELECT object_version_number
825 FROM okl_fe_std_rt_tmp_vers
826 WHERE std_rate_tmpl_ver_id = p_def_srv_rec.std_rate_tmpl_ver_id
828 FOR UPDATE OF object_version_number NOWAIT;
829
830 CURSOR lchk_csr(p_def_srv_rec IN okl_srv_rec) IS
831 SELECT object_version_number
832 FROM okl_fe_std_rt_tmp_vers
833 WHERE std_rate_tmpl_ver_id = p_def_srv_rec.std_rate_tmpl_ver_id;
834 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
835 l_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
836 lc_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
837 l_row_notfound BOOLEAN := false;
838 lc_row_notfound BOOLEAN := false;
839
840 BEGIN
841
842 BEGIN
843 OPEN lock_csr(p_def_srv_rec);
844 FETCH lock_csr INTO l_object_version_number ;
845 l_row_notfound := lock_csr%NOTFOUND;
846 CLOSE lock_csr;
847 EXCEPTION
848 WHEN e_resource_busy THEN
849
850 IF (lock_csr%ISOPEN) THEN
851 CLOSE lock_csr;
852 END IF;
853 okl_api.set_message(g_fnd_app
854 ,g_form_unable_to_reserve_rec);
855 RAISE app_exceptions.record_lock_exception;
856 END;
857
858 IF (l_row_notfound) THEN
859 OPEN lchk_csr(p_def_srv_rec);
860 FETCH lchk_csr INTO lc_object_version_number ;
861 lc_row_notfound := lchk_csr%NOTFOUND;
862 CLOSE lchk_csr;
863 END IF;
864
865 IF (lc_row_notfound) THEN
866 okl_api.set_message(g_fnd_app
867 ,g_form_record_deleted);
868 RAISE okl_api.g_exception_error;
869 ELSIF lc_object_version_number > p_def_srv_rec.object_version_number THEN
870 okl_api.set_message(g_fnd_app
871 ,g_form_record_changed);
872 RAISE okl_api.g_exception_error;
873 ELSIF lc_object_version_number <> p_def_srv_rec.object_version_number THEN
874 okl_api.set_message(g_fnd_app
875 ,g_form_record_changed);
876 RAISE okl_api.g_exception_error;
877 ELSIF lc_object_version_number = - 1 THEN
878 okl_api.set_message(g_app_name
879 ,g_record_logically_deleted);
880 RAISE okl_api.g_exception_error;
881 END IF;
882 x_return_status := l_return_status;
883 EXCEPTION
884 WHEN okl_api.g_exception_error THEN
885 x_return_status := g_ret_sts_error;
886 WHEN okl_api.g_exception_unexpected_error THEN
887 x_return_status := g_ret_sts_unexp_error;
888 WHEN OTHERS THEN
889 okl_api.set_message(p_app_name => g_app_name
890 ,p_msg_name => g_db_error
891 ,p_token1 => g_prog_name_token
892 ,p_token1_value => l_api_name
893 ,p_token2 => g_sqlcode_token
894 ,p_token2_value => sqlcode
895 ,p_token3 => g_sqlerrm_token
896 ,p_token3_value => sqlerrm);
897 x_return_status := g_ret_sts_unexp_error;
898 END lock_row;
899
900 -----------------
901 -- lock_row (TBL)
902 -----------------
903
904 PROCEDURE lock_row(p_api_version IN NUMBER
905 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
906 ,x_return_status OUT NOCOPY VARCHAR2
907 ,x_msg_count OUT NOCOPY NUMBER
908 ,x_msg_data OUT NOCOPY VARCHAR2
909 ,okl_srv_tbl IN okl_srv_tbl) IS
910 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'lock_row (TBL)';
911 l_return_status VARCHAR2(1) := g_ret_sts_success;
912 i BINARY_INTEGER;
913
914 BEGIN
915
916 IF (okl_srv_tbl.COUNT > 0) THEN
917 i := okl_srv_tbl.FIRST;
918
919 LOOP
920 IF okl_srv_tbl.EXISTS(i) THEN
921 lock_row(p_api_version => g_api_version
922 ,p_init_msg_list => g_false
923 ,x_return_status => l_return_status
924 ,x_msg_count => x_msg_count
925 ,x_msg_data => x_msg_data
926 ,p_def_srv_rec => okl_srv_tbl(i));
927 IF l_return_status = g_ret_sts_unexp_error THEN
928 RAISE okl_api.g_exception_unexpected_error;
929 ELSIF l_return_status = g_ret_sts_error THEN
930 RAISE okl_api.g_exception_error;
931 END IF;
932 EXIT WHEN(i = okl_srv_tbl.LAST);
933 i := okl_srv_tbl.next(i);
934 END IF;
935 END LOOP;
936
937 END IF;
938 x_return_status := l_return_status;
939 EXCEPTION
940 WHEN okl_api.g_exception_error THEN
941 x_return_status := g_ret_sts_error;
942 WHEN okl_api.g_exception_unexpected_error THEN
946 ,p_msg_name => g_db_error
943 x_return_status := g_ret_sts_unexp_error;
944 WHEN OTHERS THEN
945 okl_api.set_message(p_app_name => g_app_name
947 ,p_token1 => g_prog_name_token
948 ,p_token1_value => l_api_name
949 ,p_token2 => g_sqlcode_token
950 ,p_token2_value => sqlcode
951 ,p_token3 => g_sqlerrm_token
952 ,p_token3_value => sqlerrm);
953 x_return_status := g_ret_sts_unexp_error;
954 END lock_row;
955
956 --------------------------------------------------------------------------------
957 -- Procedure insert_row
958 --------------------------------------------------------------------------------
959
960 PROCEDURE insert_row(p_api_version IN NUMBER
961 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
962 ,x_return_status OUT NOCOPY VARCHAR2
963 ,x_msg_count OUT NOCOPY NUMBER
964 ,x_msg_data OUT NOCOPY VARCHAR2
965 ,p_srv_rec IN okl_srv_rec
966 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
967 l_api_version CONSTANT NUMBER := 1;
968 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
969 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
970 l_srv_rec okl_srv_rec;
971 l_def_srv_rec okl_srv_rec;
972
973 FUNCTION fill_who_columns(p_srv_rec IN okl_srv_rec) RETURN okl_srv_rec IS
974 l_srv_rec okl_srv_rec := p_srv_rec;
975
976 BEGIN
977 l_srv_rec.creation_date := SYSDATE;
978 l_srv_rec.created_by := fnd_global.user_id;
979 l_srv_rec.last_update_date := SYSDATE;
980 l_srv_rec.last_updated_by := fnd_global.user_id;
981 l_srv_rec.last_update_login := fnd_global.login_id;
982 RETURN(l_srv_rec);
983 END fill_who_columns;
984
985 FUNCTION set_attributes(p_srv_rec IN okl_srv_rec
986 ,x_srv_rec OUT NOCOPY okl_srv_rec) RETURN VARCHAR2 IS
987 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
988
989 BEGIN
990 x_srv_rec := p_srv_rec;
991 x_srv_rec.object_version_number := 1;
992 RETURN(l_return_status);
993 END set_attributes;
994
995 BEGIN
996 l_return_status := okl_api.start_activity(l_api_name
997 ,g_pkg_name
998 ,p_init_msg_list
999 ,l_api_version
1000 ,p_api_version
1001 ,'_PVT'
1002 ,x_return_status);
1003
1004 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1005 RAISE okl_api.g_exception_unexpected_error;
1006 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1007 RAISE okl_api.g_exception_error;
1008 END IF;
1009 l_srv_rec := null_out_defaults(p_srv_rec);
1010
1011 -- Set Primary key value
1012
1013 l_srv_rec.std_rate_tmpl_ver_id := get_seq_id;
1014
1015 --Setting Item Attributes
1016
1017 l_return_status := set_attributes(l_srv_rec
1018 ,l_def_srv_rec);
1019
1020 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1021 RAISE okl_api.g_exception_unexpected_error;
1022 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1023 RAISE okl_api.g_exception_error;
1024 END IF;
1025 l_def_srv_rec := fill_who_columns(l_def_srv_rec);
1026 l_return_status := validate_attributes(l_def_srv_rec);
1027
1028 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1029 RAISE okl_api.g_exception_unexpected_error;
1030 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1031 RAISE okl_api.g_exception_error;
1032 END IF;
1033 l_return_status := validate_record(l_def_srv_rec);
1034
1035 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1036 RAISE okl_api.g_exception_unexpected_error;
1037 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1038 RAISE okl_api.g_exception_error;
1039 END IF;
1040
1041 INSERT INTO okl_fe_std_rt_tmp_vers
1042 (std_rate_tmpl_ver_id
1043 ,object_version_number
1044 ,version_number
1045 ,std_rate_tmpl_id
1046 ,effective_from_date
1047 ,effective_to_date
1048 ,sts_code
1049 ,adj_mat_version_id
1050 ,srt_rate
1051 ,spread
1052 ,day_convention_code
1053 ,min_adj_rate
1054 ,max_adj_rate
1055 ,attribute_category
1056 ,attribute1
1057 ,attribute2
1058 ,attribute3
1059 ,attribute4
1060 ,attribute5
1061 ,attribute6
1062 ,attribute7
1063 ,attribute8
1067 ,attribute12
1064 ,attribute9
1065 ,attribute10
1066 ,attribute11
1068 ,attribute13
1069 ,attribute14
1070 ,attribute15
1071 ,created_by
1072 ,creation_date
1073 ,last_updated_by
1074 ,last_update_date
1075 ,last_update_login)
1076 VALUES (l_def_srv_rec.std_rate_tmpl_ver_id
1077 ,l_def_srv_rec.object_version_number
1078 ,l_def_srv_rec.version_number
1079 ,l_def_srv_rec.std_rate_tmpl_id
1080 ,l_def_srv_rec.effective_from_date
1081 ,l_def_srv_rec.effective_to_date
1082 ,l_def_srv_rec.sts_code
1083 ,l_def_srv_rec.adj_mat_version_id
1084 ,l_def_srv_rec.srt_rate
1085 ,l_def_srv_rec.spread
1086 ,l_def_srv_rec.day_convention_code
1087 ,l_def_srv_rec.min_adj_rate
1088 ,l_def_srv_rec.max_adj_rate
1089 ,l_def_srv_rec.attribute_category
1090 ,l_def_srv_rec.attribute1
1091 ,l_def_srv_rec.attribute2
1092 ,l_def_srv_rec.attribute3
1093 ,l_def_srv_rec.attribute4
1094 ,l_def_srv_rec.attribute5
1095 ,l_def_srv_rec.attribute6
1096 ,l_def_srv_rec.attribute7
1097 ,l_def_srv_rec.attribute8
1098 ,l_def_srv_rec.attribute9
1099 ,l_def_srv_rec.attribute10
1100 ,l_def_srv_rec.attribute11
1101 ,l_def_srv_rec.attribute12
1102 ,l_def_srv_rec.attribute13
1103 ,l_def_srv_rec.attribute14
1104 ,l_def_srv_rec.attribute15
1105 ,l_def_srv_rec.created_by
1106 ,l_def_srv_rec.creation_date
1107 ,l_def_srv_rec.last_updated_by
1108 ,l_def_srv_rec.last_update_date
1109 ,l_def_srv_rec.last_update_login);
1110
1111 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1112 RAISE okl_api.g_exception_unexpected_error;
1113 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1114 RAISE okl_api.g_exception_error;
1115 END IF;
1116
1117 --Set OUT Values
1118
1119 x_srv_rec := l_def_srv_rec;
1120 x_return_status := l_return_status;
1121 okl_api.end_activity(x_msg_count
1122 ,x_msg_data);
1123 EXCEPTION
1124 WHEN g_exception_halt_validation THEN
1125
1126 -- No action necessary. Validation can continue to next attribute/column
1127
1128 NULL;
1129 WHEN okl_api.g_exception_error THEN
1130 x_return_status := okl_api.handle_exceptions(l_api_name
1131 ,g_pkg_name
1132 ,'OKL_API.G_RET_STS_ERROR'
1133 ,x_msg_count
1134 ,x_msg_data
1135 ,'_PVT');
1136 WHEN okl_api.g_exception_unexpected_error THEN
1137 x_return_status := okl_api.handle_exceptions(l_api_name
1138 ,g_pkg_name
1139 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1140 ,x_msg_count
1141 ,x_msg_data
1142 ,'_PVT');
1143 WHEN OTHERS THEN
1144 x_return_status := okl_api.handle_exceptions(l_api_name
1145 ,g_pkg_name
1146 ,'OTHERS'
1147 ,x_msg_count
1148 ,x_msg_data
1149 ,'_PVT');
1150 END insert_row;
1151
1152 --------------------------------------------------------------------------------
1153 -- Procedure insert_row_tbl
1154 --------------------------------------------------------------------------------
1155
1156 PROCEDURE insert_row(p_api_version IN NUMBER
1157 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1158 ,x_return_status OUT NOCOPY VARCHAR2
1159 ,x_msg_count OUT NOCOPY NUMBER
1160 ,x_msg_data OUT NOCOPY VARCHAR2
1161 ,p_srv_tbl IN okl_srv_tbl
1162 ,x_srv_tbl OUT NOCOPY okl_srv_tbl) IS
1163 l_api_version CONSTANT NUMBER := 1;
1164 l_api_name CONSTANT VARCHAR2(30) := 'insert_row_tbl';
1165 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1166 i NUMBER := 0;
1167 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1168
1169 BEGIN
1170 okl_api.init_msg_list(p_init_msg_list);
1171
1172 -- Make sure PL/SQL table has records in it before passing
1173
1174 IF (p_srv_tbl.COUNT > 0) THEN
1175 i := p_srv_tbl.FIRST;
1176
1177 LOOP
1181 ,x_msg_count => x_msg_count
1178 insert_row(p_api_version => p_api_version
1179 ,p_init_msg_list => okl_api.g_false
1180 ,x_return_status => x_return_status
1182 ,x_msg_data => x_msg_data
1183 ,p_srv_rec => p_srv_tbl(i)
1184 ,x_srv_rec => x_srv_tbl(i));
1185 IF x_return_status <> okl_api.g_ret_sts_success THEN
1186 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1187 l_overall_status := x_return_status;
1188 END IF;
1189 END IF;
1190 EXIT WHEN(i = p_srv_tbl.LAST);
1191 i := p_srv_tbl.next(i);
1192 END LOOP;
1193 x_return_status := l_overall_status;
1194 END IF;
1195
1196 EXCEPTION
1197 WHEN g_exception_halt_validation THEN
1198
1199 -- No action necessary. Validation can continue to next attribute/column
1200
1201 NULL;
1202 WHEN okl_api.g_exception_error THEN
1203 x_return_status := okl_api.handle_exceptions(l_api_name
1204 ,g_pkg_name
1205 ,'OKL_API.G_RET_STS_ERROR'
1206 ,x_msg_count
1207 ,x_msg_data
1208 ,'_PVT');
1209 WHEN okl_api.g_exception_unexpected_error THEN
1210 x_return_status := okl_api.handle_exceptions(l_api_name
1211 ,g_pkg_name
1212 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1213 ,x_msg_count
1214 ,x_msg_data
1215 ,'_PVT');
1216 WHEN OTHERS THEN
1217 x_return_status := okl_api.handle_exceptions(l_api_name
1218 ,g_pkg_name
1219 ,'OTHERS'
1220 ,x_msg_count
1221 ,x_msg_data
1222 ,'_PVT');
1223 END insert_row;
1224
1225 --------------------------------------------------------------------------------
1226 -- Procedure update_row
1227 --------------------------------------------------------------------------------
1228
1229 PROCEDURE update_row(p_api_version IN NUMBER
1230 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1231 ,x_return_status OUT NOCOPY VARCHAR2
1232 ,x_msg_count OUT NOCOPY NUMBER
1233 ,x_msg_data OUT NOCOPY VARCHAR2
1234 ,p_srv_rec IN okl_srv_rec
1235 ,x_srv_rec OUT NOCOPY okl_srv_rec) IS
1236 l_api_version CONSTANT NUMBER := 1;
1237 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1238 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1239 l_srv_rec okl_srv_rec := p_srv_rec;
1240 l_def_srv_rec okl_srv_rec;
1241 lx_srv_rec okl_srv_rec;
1242
1243 FUNCTION fill_who_columns(p_srv_rec IN okl_srv_rec) RETURN okl_srv_rec IS
1244 l_srv_rec okl_srv_rec := p_srv_rec;
1245
1246 BEGIN
1247 l_srv_rec.last_update_date := SYSDATE;
1248 l_srv_rec.last_updated_by := fnd_global.user_id;
1249 l_srv_rec.last_update_login := fnd_global.login_id;
1250 RETURN(l_srv_rec);
1251 END fill_who_columns;
1252
1253 FUNCTION populate_new_record(p_srv_rec IN okl_srv_rec
1254 ,x_srv_rec OUT NOCOPY okl_srv_rec) RETURN VARCHAR2 IS
1255 l_srv_rec okl_srv_rec;
1256 l_row_notfound BOOLEAN := true;
1257 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1258
1259 BEGIN
1260 x_srv_rec := p_srv_rec;
1261
1262 --Get current database values
1263
1264 l_srv_rec := get_rec(p_srv_rec
1265 ,l_row_notfound);
1266
1267 IF (l_row_notfound) THEN
1268 l_return_status := okl_api.g_ret_sts_unexp_error;
1269 END IF;
1270
1271 IF (x_srv_rec.std_rate_tmpl_ver_id IS NULL) THEN
1272 x_srv_rec.std_rate_tmpl_ver_id := l_srv_rec.std_rate_tmpl_ver_id;
1273 END IF;
1274
1275 IF (x_srv_rec.object_version_number IS NULL) THEN
1276 x_srv_rec.object_version_number := l_srv_rec.object_version_number;
1277 END IF;
1278
1279 IF (x_srv_rec.version_number IS NULL) THEN
1280 x_srv_rec.version_number := l_srv_rec.version_number;
1281 END IF;
1282
1283 IF (x_srv_rec.std_rate_tmpl_id IS NULL) THEN
1284 x_srv_rec.std_rate_tmpl_id := l_srv_rec.std_rate_tmpl_id;
1285 END IF;
1286
1287 IF (x_srv_rec.effective_from_date IS NULL) THEN
1288 x_srv_rec.effective_from_date := l_srv_rec.effective_from_date;
1289 END IF;
1290
1294
1291 IF (x_srv_rec.effective_to_date IS NULL) THEN
1292 x_srv_rec.effective_to_date := l_srv_rec.effective_to_date;
1293 END IF;
1295 IF (x_srv_rec.sts_code IS NULL) THEN
1296 x_srv_rec.sts_code := l_srv_rec.sts_code;
1297 END IF;
1298
1299 IF (x_srv_rec.adj_mat_version_id IS NULL) THEN
1300 x_srv_rec.adj_mat_version_id := l_srv_rec.adj_mat_version_id;
1301 END IF;
1302
1303 IF (x_srv_rec.srt_rate IS NULL) THEN
1304 x_srv_rec.srt_rate := l_srv_rec.srt_rate;
1305 END IF;
1306
1307 IF (x_srv_rec.spread IS NULL) THEN
1308 x_srv_rec.spread := l_srv_rec.spread;
1309 END IF;
1310
1311 IF (x_srv_rec.day_convention_code IS NULL) THEN
1312 x_srv_rec.day_convention_code := l_srv_rec.day_convention_code;
1313 END IF;
1314
1315 IF (x_srv_rec.min_adj_rate IS NULL) THEN
1316 x_srv_rec.min_adj_rate := l_srv_rec.min_adj_rate;
1317 END IF;
1318
1319 IF (x_srv_rec.max_adj_rate IS NULL) THEN
1320 x_srv_rec.max_adj_rate := l_srv_rec.max_adj_rate;
1321 END IF;
1322
1323 IF (x_srv_rec.attribute_category IS NULL) THEN
1324 x_srv_rec.attribute_category := l_srv_rec.attribute_category;
1325 END IF;
1326
1327 IF (x_srv_rec.attribute1 IS NULL) THEN
1328 x_srv_rec.attribute1 := l_srv_rec.attribute1;
1329 END IF;
1330
1331 IF (x_srv_rec.attribute2 IS NULL) THEN
1332 x_srv_rec.attribute2 := l_srv_rec.attribute2;
1333 END IF;
1334
1335 IF (x_srv_rec.attribute3 IS NULL) THEN
1336 x_srv_rec.attribute3 := l_srv_rec.attribute3;
1337 END IF;
1338
1339 IF (x_srv_rec.attribute4 IS NULL) THEN
1340 x_srv_rec.attribute4 := l_srv_rec.attribute4;
1341 END IF;
1342
1343 IF (x_srv_rec.attribute5 IS NULL) THEN
1344 x_srv_rec.attribute5 := l_srv_rec.attribute5;
1345 END IF;
1346
1347 IF (x_srv_rec.attribute6 IS NULL) THEN
1348 x_srv_rec.attribute6 := l_srv_rec.attribute6;
1349 END IF;
1350
1351 IF (x_srv_rec.attribute7 IS NULL) THEN
1352 x_srv_rec.attribute7 := l_srv_rec.attribute7;
1353 END IF;
1354
1355 IF (x_srv_rec.attribute8 IS NULL) THEN
1356 x_srv_rec.attribute8 := l_srv_rec.attribute8;
1357 END IF;
1358
1359 IF (x_srv_rec.attribute9 IS NULL) THEN
1360 x_srv_rec.attribute9 := l_srv_rec.attribute9;
1361 END IF;
1362
1363 IF (x_srv_rec.attribute10 IS NULL) THEN
1364 x_srv_rec.attribute10 := l_srv_rec.attribute10;
1365 END IF;
1366
1367 IF (x_srv_rec.attribute11 IS NULL) THEN
1368 x_srv_rec.attribute11 := l_srv_rec.attribute11;
1369 END IF;
1370
1371 IF (x_srv_rec.attribute12 IS NULL) THEN
1372 x_srv_rec.attribute12 := l_srv_rec.attribute12;
1373 END IF;
1374
1375 IF (x_srv_rec.attribute13 IS NULL) THEN
1376 x_srv_rec.attribute13 := l_srv_rec.attribute13;
1377 END IF;
1378
1379 IF (x_srv_rec.attribute14 IS NULL) THEN
1380 x_srv_rec.attribute14 := l_srv_rec.attribute14;
1381 END IF;
1382
1383 IF (x_srv_rec.attribute15 IS NULL) THEN
1384 x_srv_rec.attribute15 := l_srv_rec.attribute15;
1385 END IF;
1386
1387 IF (x_srv_rec.created_by IS NULL) THEN
1388 x_srv_rec.created_by := l_srv_rec.created_by;
1389 END IF;
1390
1391 IF (x_srv_rec.creation_date IS NULL) THEN
1392 x_srv_rec.creation_date := l_srv_rec.creation_date;
1393 END IF;
1394
1395 IF (x_srv_rec.last_updated_by IS NULL) THEN
1396 x_srv_rec.last_updated_by := l_srv_rec.last_updated_by;
1397 END IF;
1398
1399 IF (x_srv_rec.last_update_date IS NULL) THEN
1400 x_srv_rec.last_update_date := l_srv_rec.last_update_date;
1401 END IF;
1402
1403 IF (x_srv_rec.last_update_login IS NULL) THEN
1404 x_srv_rec.last_update_login := l_srv_rec.last_update_login;
1405 END IF;
1406 RETURN(l_return_status);
1407 END populate_new_record;
1408
1409 FUNCTION set_attributes(p_srv_rec IN okl_srv_rec
1410 ,x_srv_rec OUT NOCOPY okl_srv_rec) RETURN VARCHAR2 IS
1411 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1412
1413 BEGIN
1414 x_srv_rec := p_srv_rec;
1415 RETURN(l_return_status);
1416 END set_attributes;
1417
1418 BEGIN
1419 l_return_status := okl_api.start_activity(l_api_name
1420 ,g_pkg_name
1421 ,p_init_msg_list
1422 ,l_api_version
1423 ,p_api_version
1424 ,'_PVT'
1425 ,x_return_status);
1426
1427 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1428 RAISE okl_api.g_exception_unexpected_error;
1429 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1430 RAISE okl_api.g_exception_error;
1431 END IF;
1432
1433 --Setting Item Attributes
1434
1438 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1435 l_return_status := set_attributes(l_srv_rec
1436 ,lx_srv_rec);
1437
1439 RAISE okl_api.g_exception_unexpected_error;
1440 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1441 RAISE okl_api.g_exception_error;
1442 END IF;
1443 l_return_status := populate_new_record(lx_srv_rec
1444 ,l_def_srv_rec);
1445
1446 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1447 RAISE okl_api.g_exception_unexpected_error;
1448 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1449 RAISE okl_api.g_exception_error;
1450 END IF;
1451 l_def_srv_rec := null_out_defaults(l_def_srv_rec);
1452
1453 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1454 RAISE okl_api.g_exception_unexpected_error;
1455 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1456 RAISE okl_api.g_exception_error;
1457 END IF;
1458 l_def_srv_rec := fill_who_columns(l_def_srv_rec);
1459 l_return_status := validate_attributes(l_def_srv_rec);
1460
1461 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1462 RAISE okl_api.g_exception_unexpected_error;
1463 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1464 RAISE okl_api.g_exception_error;
1465 END IF;
1466 l_return_status := validate_record(l_def_srv_rec);
1467
1468 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1469 RAISE okl_api.g_exception_unexpected_error;
1470 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1471 RAISE okl_api.g_exception_error;
1472 END IF;
1473
1474 -- Lock the row before updating
1475
1476 lock_row(p_api_version => g_api_version
1477 ,p_init_msg_list => g_false
1478 ,x_return_status => l_return_status
1479 ,x_msg_count => x_msg_count
1480 ,x_msg_data => x_msg_data
1481 ,p_def_srv_rec => l_def_srv_rec);
1482
1483 IF (l_return_status = g_ret_sts_unexp_error) THEN
1484 RAISE okl_api.g_exception_unexpected_error;
1485 ELSIF (l_return_status = g_ret_sts_error) THEN
1486 RAISE okl_api.g_exception_error;
1487 END IF;
1488
1489 UPDATE okl_fe_std_rt_tmp_vers
1490 SET std_rate_tmpl_ver_id = l_def_srv_rec.std_rate_tmpl_ver_id
1491 ,object_version_number = l_def_srv_rec.object_version_number + 1
1492 ,version_number = l_def_srv_rec.version_number
1493 ,std_rate_tmpl_id = l_def_srv_rec.std_rate_tmpl_id
1494 ,effective_from_date = l_def_srv_rec.effective_from_date
1495 ,effective_to_date = l_def_srv_rec.effective_to_date
1496 ,sts_code = l_def_srv_rec.sts_code
1497 ,adj_mat_version_id = l_def_srv_rec.adj_mat_version_id
1498 ,srt_rate = l_def_srv_rec.srt_rate
1499 ,spread = l_def_srv_rec.spread
1500 ,day_convention_code = l_def_srv_rec.day_convention_code
1501 ,min_adj_rate = l_def_srv_rec.min_adj_rate
1502 ,max_adj_rate = l_def_srv_rec.max_adj_rate
1503 ,attribute_category = l_def_srv_rec.attribute_category
1504 ,attribute1 = l_def_srv_rec.attribute1
1505 ,attribute2 = l_def_srv_rec.attribute2
1506 ,attribute3 = l_def_srv_rec.attribute3
1507 ,attribute4 = l_def_srv_rec.attribute4
1508 ,attribute5 = l_def_srv_rec.attribute5
1509 ,attribute6 = l_def_srv_rec.attribute6
1510 ,attribute7 = l_def_srv_rec.attribute7
1511 ,attribute8 = l_def_srv_rec.attribute8
1512 ,attribute9 = l_def_srv_rec.attribute9
1513 ,attribute10 = l_def_srv_rec.attribute10
1514 ,attribute11 = l_def_srv_rec.attribute11
1515 ,attribute12 = l_def_srv_rec.attribute12
1516 ,attribute13 = l_def_srv_rec.attribute13
1517 ,attribute14 = l_def_srv_rec.attribute14
1518 ,attribute15 = l_def_srv_rec.attribute15
1519 ,created_by = l_def_srv_rec.created_by
1520 ,creation_date = l_def_srv_rec.creation_date
1521 ,last_updated_by = l_def_srv_rec.last_updated_by
1522 ,last_update_date = l_def_srv_rec.last_update_date
1523 ,last_update_login = l_def_srv_rec.last_update_login
1524 WHERE std_rate_tmpl_ver_id = l_def_srv_rec.std_rate_tmpl_ver_id;
1525
1526 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1527 RAISE okl_api.g_exception_unexpected_error;
1528 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1529 RAISE okl_api.g_exception_error;
1530 END IF;
1531
1532 --Set OUT Values
1533
1534 x_srv_rec := l_def_srv_rec;
1535 okl_api.end_activity(x_msg_count
1536 ,x_msg_data);
1537 EXCEPTION
1538 WHEN g_exception_halt_validation THEN
1539
1540 -- No action necessary. Validation can continue to next attribute/column
1541
1542 NULL;
1543 WHEN okl_api.g_exception_error THEN
1544 x_return_status := okl_api.handle_exceptions(l_api_name
1545 ,g_pkg_name
1546 ,'OKL_API.G_RET_STS_ERROR'
1547 ,x_msg_count
1548 ,x_msg_data
1549 ,'_PVT');
1550 WHEN okl_api.g_exception_unexpected_error THEN
1551 x_return_status := okl_api.handle_exceptions(l_api_name
1552 ,g_pkg_name
1553 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1554 ,x_msg_count
1555 ,x_msg_data
1556 ,'_PVT');
1557 WHEN OTHERS THEN
1558 x_return_status := okl_api.handle_exceptions(l_api_name
1559 ,g_pkg_name
1560 ,'OTHERS'
1561 ,x_msg_count
1562 ,x_msg_data
1563 ,'_PVT');
1564 END update_row;
1565
1566 --------------------------------------------------------------------------------
1567 -- Procedure insert_row_tbl
1568 --------------------------------------------------------------------------------
1569
1570 PROCEDURE update_row(p_api_version IN NUMBER
1571 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1572 ,x_return_status OUT NOCOPY VARCHAR2
1573 ,x_msg_count OUT NOCOPY NUMBER
1574 ,x_msg_data OUT NOCOPY VARCHAR2
1575 ,p_srv_tbl IN okl_srv_tbl
1576 ,x_srv_tbl OUT NOCOPY okl_srv_tbl) IS
1577 l_api_version CONSTANT NUMBER := 1;
1578 l_api_name CONSTANT VARCHAR2(30) := 'update_row_tbl';
1579 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1580 i NUMBER := 0;
1581 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1582
1583 BEGIN
1584 okl_api.init_msg_list(p_init_msg_list);
1585
1586 -- Make sure PL/SQL table has records in it before passing
1587
1588 IF (p_srv_tbl.COUNT > 0) THEN
1589 i := p_srv_tbl.FIRST;
1590
1591 LOOP
1592 update_row(p_api_version => p_api_version
1593 ,p_init_msg_list => okl_api.g_false
1594 ,x_return_status => x_return_status
1595 ,x_msg_count => x_msg_count
1596 ,x_msg_data => x_msg_data
1597 ,p_srv_rec => p_srv_tbl(i)
1598 ,x_srv_rec => x_srv_tbl(i));
1599 IF x_return_status <> okl_api.g_ret_sts_success THEN
1600 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1601 l_overall_status := x_return_status;
1602 END IF;
1603 END IF;
1604 EXIT WHEN(i = p_srv_tbl.LAST);
1605 i := p_srv_tbl.next(i);
1606 END LOOP;
1607 x_return_status := l_overall_status;
1608 END IF;
1609
1610 EXCEPTION
1611 WHEN g_exception_halt_validation THEN
1612
1613 -- No action necessary. Validation can continue to next attribute/column
1614
1615 NULL;
1616 WHEN okl_api.g_exception_error THEN
1617 x_return_status := okl_api.handle_exceptions(l_api_name
1618 ,g_pkg_name
1619 ,'OKL_API.G_RET_STS_ERROR'
1620 ,x_msg_count
1621 ,x_msg_data
1622 ,'_PVT');
1623 WHEN okl_api.g_exception_unexpected_error THEN
1624 x_return_status := okl_api.handle_exceptions(l_api_name
1625 ,g_pkg_name
1626 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1627 ,x_msg_count
1628 ,x_msg_data
1629 ,'_PVT');
1630 WHEN OTHERS THEN
1631 x_return_status := okl_api.handle_exceptions(l_api_name
1632 ,g_pkg_name
1633 ,'OTHERS'
1634 ,x_msg_count
1635 ,x_msg_data
1636 ,'_PVT');
1637 END update_row;
1638
1639 --------------------------------------------------------------------------------
1640 -- Procedure delete_row
1641 --------------------------------------------------------------------------------
1642
1643 PROCEDURE delete_row(p_api_version IN NUMBER
1644 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1645 ,x_return_status OUT NOCOPY VARCHAR2
1646 ,x_msg_count OUT NOCOPY NUMBER
1647 ,x_msg_data OUT NOCOPY VARCHAR2
1648 ,p_srv_rec IN okl_srv_rec) IS
1649 l_api_version CONSTANT NUMBER := 1;
1650 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1651 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1652 l_srv_rec okl_srv_rec := p_srv_rec;
1653 l_row_notfound BOOLEAN := true;
1654
1655 BEGIN
1656 l_return_status := okl_api.start_activity(l_api_name
1657 ,g_pkg_name
1658 ,p_init_msg_list
1659 ,l_api_version
1660 ,p_api_version
1661 ,'_PVT'
1662 ,x_return_status);
1663
1664 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1665 RAISE okl_api.g_exception_unexpected_error;
1669
1666 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1667 RAISE okl_api.g_exception_error;
1668 END IF;
1670 DELETE FROM okl_fe_std_rt_tmp_vers
1671 WHERE std_rate_tmpl_ver_id = l_srv_rec.std_rate_tmpl_ver_id;
1672
1673 EXCEPTION
1674 WHEN g_exception_halt_validation THEN
1675
1676 -- No action necessary. Validation can continue to next attribute/column
1677
1678 NULL;
1679 WHEN okl_api.g_exception_error THEN
1680 x_return_status := okl_api.handle_exceptions(l_api_name
1681 ,g_pkg_name
1682 ,'OKL_API.G_RET_STS_ERROR'
1683 ,x_msg_count
1684 ,x_msg_data
1685 ,'_PVT');
1686 WHEN okl_api.g_exception_unexpected_error THEN
1687 x_return_status := okl_api.handle_exceptions(l_api_name
1688 ,g_pkg_name
1689 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1690 ,x_msg_count
1691 ,x_msg_data
1692 ,'_PVT');
1693 WHEN OTHERS THEN
1694 x_return_status := okl_api.handle_exceptions(l_api_name
1695 ,g_pkg_name
1696 ,'OTHERS'
1697 ,x_msg_count
1698 ,x_msg_data
1699 ,'_PVT');
1700 END delete_row;
1701
1702 --------------------------------------------------------------------------------
1703 -- Procedure delete_row_tbl
1704 --------------------------------------------------------------------------------
1705
1706 PROCEDURE delete_row(p_api_version IN NUMBER
1707 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1708 ,x_return_status OUT NOCOPY VARCHAR2
1709 ,x_msg_count OUT NOCOPY NUMBER
1710 ,x_msg_data OUT NOCOPY VARCHAR2
1711 ,p_srv_tbl IN okl_srv_tbl) IS
1712 l_api_version CONSTANT NUMBER := 1;
1713 l_api_name CONSTANT VARCHAR2(30) := 'delete_row_tbl';
1714 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1715 i NUMBER := 0;
1716 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1717
1718 BEGIN
1719 okl_api.init_msg_list(p_init_msg_list);
1720
1721 -- Make sure PL/SQL table has records in it before passing
1722
1723 IF (p_srv_tbl.COUNT > 0) THEN
1724 i := p_srv_tbl.FIRST;
1725
1726 LOOP
1727 delete_row(p_api_version => p_api_version
1728 ,p_init_msg_list => okl_api.g_false
1729 ,x_return_status => x_return_status
1730 ,x_msg_count => x_msg_count
1731 ,x_msg_data => x_msg_data
1732 ,p_srv_rec => p_srv_tbl(i));
1733 IF x_return_status <> okl_api.g_ret_sts_success THEN
1734 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1735 l_overall_status := x_return_status;
1736 END IF;
1737 END IF;
1738 EXIT WHEN(i = p_srv_tbl.LAST);
1739 i := p_srv_tbl.next(i);
1740 END LOOP;
1741 x_return_status := l_overall_status;
1742 END IF;
1743
1744 EXCEPTION
1745 WHEN g_exception_halt_validation THEN
1746
1747 -- No action necessary. Validation can continue to next attribute/column
1748
1749 NULL;
1750 WHEN okl_api.g_exception_error THEN
1751 x_return_status := okl_api.handle_exceptions(l_api_name
1752 ,g_pkg_name
1753 ,'OKL_API.G_RET_STS_ERROR'
1754 ,x_msg_count
1755 ,x_msg_data
1756 ,'_PVT');
1757 WHEN okl_api.g_exception_unexpected_error THEN
1758 x_return_status := okl_api.handle_exceptions(l_api_name
1759 ,g_pkg_name
1760 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1761 ,x_msg_count
1762 ,x_msg_data
1763 ,'_PVT');
1764 WHEN OTHERS THEN
1765 x_return_status := okl_api.handle_exceptions(l_api_name
1766 ,g_pkg_name
1767 ,'OTHERS'
1768 ,x_msg_count
1769 ,x_msg_data
1770 ,'_PVT');
1771 END delete_row;
1772
1773 END okl_srv_pvt;