[Home] [Help]
PACKAGE BODY: APPS.OKL_SRT_PVT
Source
1 PACKAGE BODY okl_srt_pvt AS
2 /* $Header: OKLSSRTB.pls 120.5 2006/07/13 13:03:40 adagur noship $ */
3
4 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
5 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
6 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
7 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
8 g_exception_halt_validation EXCEPTION;
9
10 PROCEDURE api_copy IS
11
12 BEGIN
13 NULL;
14 END api_copy;
15
16 PROCEDURE change_version IS
17
18 BEGIN
19 NULL;
20 END change_version;
21
22 ---------------------------------------------------------------------------
23 -- PROCEDURE add_language
24 ---------------------------------------------------------------------------
25
26 PROCEDURE add_language IS
27
28 BEGIN
29
30 DELETE FROM OKL_FE_STD_RT_TMP_ALL_TL t
31 WHERE NOT EXISTS(SELECT NULL
32 FROM OKL_FE_STD_RT_TMP_ALL_B b
33 WHERE b.std_rate_tmpl_id = t.std_rate_tmpl_id);
34
35 UPDATE OKL_FE_STD_RT_TMP_ALL_TL t
36 SET(template_desc) = (SELECT
37 -- LANGUAGE,
38
39 -- B.LANGUAGE,
40
41 b.template_desc
42 FROM OKL_FE_STD_RT_TMP_ALL_TL b
43 WHERE b.std_rate_tmpl_id = t.std_rate_tmpl_id
44 AND b.language = t.source_lang)
45 WHERE (t.std_rate_tmpl_id, t.language) IN(SELECT subt.std_rate_tmpl_id ,subt.language
46 FROM OKL_FE_STD_RT_TMP_ALL_TL subb ,OKL_FE_STD_RT_TMP_ALL_TL subt
47 WHERE subb.std_rate_tmpl_id = subt.std_rate_tmpl_id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
48 subb.template_desc <> subt.template_desc OR (subb.language IS NOT NULL
49 AND subt.language IS NULL)
50 OR (subb.template_desc IS NULL AND subt.template_desc IS NOT NULL)));
51
52 INSERT INTO OKL_FE_STD_RT_TMP_ALL_TL
53 (std_rate_tmpl_id
54 ,language
55 ,source_lang
56 ,sfwt_flag
57 ,template_desc)
58 SELECT b.std_rate_tmpl_id
59 ,l.language_code
60 ,b.source_lang
61 ,b.sfwt_flag
62 ,b.template_desc
63 FROM OKL_FE_STD_RT_TMP_ALL_TL b
64 ,fnd_languages l
65 WHERE l.installed_flag IN('I', 'B')
66 AND b.language = userenv('LANG')
67 AND NOT EXISTS(SELECT NULL
68 FROM OKL_FE_STD_RT_TMP_ALL_TL t
69 WHERE t.std_rate_tmpl_id = b.std_rate_tmpl_id AND t.language = l.language_code);
70
71 END add_language;
72
73 -- validation of Standard Rate Template Id
74
75 FUNCTION validate_std_rate_tmpl_id(p_srt_id IN NUMBER) RETURN VARCHAR2 IS
76 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
77
78 BEGIN
79
80 -- STD_RATE_TMPL_ID is a required field
81
82 IF (p_srt_id IS NULL OR p_srt_id = okl_api.g_miss_num) THEN
83 okl_api.set_message(p_app_name => g_app_name
84 ,p_msg_name => g_required_value
85 ,p_token1 => g_col_name_token
86 ,p_token1_value => 'STD_RATE_TMPL_ID');
87
88 -- notify caller of an error
89
90 l_return_status := okl_api.g_ret_sts_error;
91
92 -- halt further validation of this column
93
94 RAISE g_exception_halt_validation;
95 END IF;
96 RETURN(l_return_status);
97 EXCEPTION
98 WHEN g_exception_halt_validation THEN
99
100 -- no proccessing required. Validation can continue with the next column
101
102 NULL;
103 WHEN OTHERS THEN
104
105 -- store SQL error message on message stack for caller
106
107 okl_api.set_message(p_app_name => g_app_name
108 ,p_msg_name => g_unexpected_error
109 ,p_token1 => g_sqlcode_token
110 ,p_token1_value => sqlcode
111 ,p_token2 => g_sqlerrm_token
112 ,p_token2_value => sqlerrm);
113
114 -- notify caller of an UNEXPECTED error
115
116 l_return_status := okl_api.g_ret_sts_unexp_error;
117 RETURN(l_return_status);
118 END validate_std_rate_tmpl_id;
119
120 -- Validation of the org Id
121
122 FUNCTION validate_org_id(p_org_id IN NUMBER) RETURN VARCHAR2 IS
123 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
124
125 BEGIN
126
127 -- data is required
128
129 IF (p_org_id IS NULL) OR (p_org_id = okl_api.g_miss_num) THEN
130 okl_api.set_message(p_app_name => g_app_name
131 ,p_msg_name => g_required_value
132 ,p_token1 => g_col_name_token
133 ,p_token1_value => 'org_id');
134 x_return_status := okl_api.g_ret_sts_error;
135
136 -- halt further validation of this column
137
138 RAISE g_exception_halt_validation;
139 END IF;
140
141 -- check org id validity using the generic function okl_util.check_org_id()
142
143 x_return_status := okl_util.check_org_id(TO_CHAR(p_org_id));
144
145 IF (x_return_status = okl_api.g_ret_sts_error) THEN
146 okl_api.set_message(p_app_name => g_app_name
147 ,p_msg_name => g_invalid_value
148 ,p_token1 => g_col_name_token
149 ,p_token1_value => 'org_id');
150
151 -- notify caller of an error
152
153 RAISE g_exception_halt_validation;
154 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
155
156 -- notify caller of an error
157
158 RAISE g_exception_halt_validation;
159 END IF;
160 RETURN(x_return_status);
161 EXCEPTION
162 WHEN g_exception_halt_validation THEN
163
164 -- No action necessary. Validation can continue to next attribute/column
165
166 RETURN(x_return_status);
167 WHEN OTHERS THEN
168
169 -- store SQL error message on message stack for caller
170
171 okl_api.set_message(p_app_name => g_app_name
172 ,p_msg_name => g_unexpected_error
173 ,p_token1 => g_sqlcode_token
174 ,p_token1_value => sqlcode
175 ,p_token2 => g_sqlerrm_token
176 ,p_token2_value => sqlerrm);
177
178 -- notify caller of an UNEXPECTED error
179
180 x_return_status := okl_api.g_ret_sts_unexp_error;
181 RETURN(x_return_status);
182 END validate_org_id;
183
184 FUNCTION validate_currency_code(p_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
185
186 -- initialize the return status
187
188 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
189
190 BEGIN
191
192 -- data is required
193
194 IF (p_currency_code IS NULL) OR (p_currency_code = okl_api.g_miss_char) THEN
195 okl_api.set_message(p_app_name => g_app_name
196 ,p_msg_name => g_required_value
197 ,p_token1 => g_col_name_token
198 ,p_token1_value => 'currency_code');
199
200 -- halt further validation of this column
201
202 RAISE g_exception_halt_validation;
203 END IF;
204
205 -- check from currency values using the generic okl_util.validate_currency_code
206
207 x_return_status := okl_accounting_util.validate_currency_code(p_currency_code);
208
209 IF (x_return_status <> okl_api.g_true) THEN
210 okl_api.set_message(p_app_name => g_app_name
211 ,p_msg_name => g_invalid_value
212 ,p_token1 => g_col_name_token
213 ,p_token1_value => 'currency_code');
214
215 -- halt further validation of this column
216
217 RAISE g_exception_halt_validation;
218 END IF;
219 RETURN(x_return_status);
220 EXCEPTION
221 WHEN g_exception_halt_validation THEN
222 x_return_status := okl_api.g_ret_sts_error;
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_currency_code;
240
241 FUNCTION validate_orig_std_rate_tmpl_id(p_orig_srt_id IN NUMBER) RETURN VARCHAR2 IS
242 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
243
244 CURSOR srt_exists_csr IS
245 SELECT 'x'
246 FROM okl_fe_std_rt_tmp_all_b
247 WHERE std_rate_tmpl_id = p_orig_srt_id;
248 l_dummy_var VARCHAR2(1) := '?';
249
250 BEGIN
251
252 IF (p_orig_srt_id IS NOT NULL AND p_orig_srt_id <> okl_api.g_miss_num) THEN
253 OPEN srt_exists_csr;
254 FETCH srt_exists_csr INTO l_dummy_var ;
255 CLOSE srt_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 => 'ORIG_STD_RATE_TMPL_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 srt_exists_csr%ISOPEN THEN
297 CLOSE srt_exists_csr;
298 END IF;
299 RETURN x_return_status;
300 END validate_orig_std_rate_tmpl_id;
301
302 FUNCTION validate_sts_code(p_sts_code IN VARCHAR2) 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 -- Column is mandatory
311
312 IF (p_sts_code IS NULL OR p_sts_code = okl_api.g_miss_char) THEN
313 okl_api.set_message(p_app_name => g_app_name
314 ,p_msg_name => g_required_value
315 ,p_token1 => g_col_name_token
316 ,p_token1_value => 'sts_code');
317
318 -- notify caller of an error
319
320 x_return_status := okl_api.g_ret_sts_error;
321 RAISE g_exception_halt_validation;
322 END IF;
323
324 -- Lookup Code Validation
325
326 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
327 ,p_lookup_code => p_sts_code);
328
329 IF (x_return_status = okl_api.g_ret_sts_error) THEN
330 okl_api.set_message(p_app_name => g_app_name
331 ,p_msg_name => g_invalid_value
332 ,p_token1 => g_col_name_token
333 ,p_token1_value => 'sts_code'); -- notify caller of an error
334 RAISE g_exception_halt_validation;
335 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
336
337 -- notify caller of an error
338
339 x_return_status := okl_api.g_ret_sts_unexp_error;
340 RAISE g_exception_halt_validation;
341 END IF;
342 RETURN x_return_status;
343 EXCEPTION
344 WHEN g_exception_halt_validation THEN
345
346 -- no processing necessary; validation can continue
347 -- with the next column
348
349 RETURN x_return_status;
350 WHEN OTHERS THEN
351
352 -- store SQL error message on message stack for caller
353
354 okl_api.set_message(p_app_name => g_app_name
355 ,p_msg_name => g_unexpected_error
356 ,p_token1 => g_sqlcode_token
357 ,p_token1_value => sqlcode
358 ,p_token2 => g_sqlerrm_token
359 ,p_token2_value => sqlerrm);
360
361 -- notify caller of an UNEXPECTED error
362
363 x_return_status := okl_api.g_ret_sts_unexp_error;
364 RETURN x_return_status;
365 END validate_sts_code;
366
367 FUNCTION validate_effective_from_date(p_effective_from_date IN DATE) RETURN VARCHAR2 IS
368
369 -- initialize the return status
370
371 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
372
373 BEGIN
374
375 IF (p_effective_from_date IS NULL OR p_effective_from_date = okl_api.g_miss_date) THEN
376 okl_api.set_message(p_app_name => g_app_name
377 ,p_msg_name => g_required_value
378 ,p_token1 => g_col_name_token
379 ,p_token1_value => 'EFFECTIVE_FROM_DATE');
380
381 -- notify caller of an error
382
383 x_return_status := okl_api.g_ret_sts_error;
384 RAISE g_exception_halt_validation;
385 END IF;
386 RETURN x_return_status;
387 EXCEPTION
388 WHEN g_exception_halt_validation THEN
389
390 -- no processing necessary; validation can continue
391 -- with the next column
392
393 RETURN x_return_status;
394 WHEN OTHERS THEN
395
396 -- store SQL error message on message stack for caller
397
398 okl_api.set_message(p_app_name => g_app_name
399 ,p_msg_name => g_unexpected_error
400 ,p_token1 => g_sqlcode_token
401 ,p_token1_value => sqlcode
402 ,p_token2 => g_sqlerrm_token
403 ,p_token2_value => sqlerrm);
404
405 -- notify caller of an UNEXPECTED error
406
407 x_return_status := okl_api.g_ret_sts_unexp_error;
408 RETURN x_return_status;
409 END validate_effective_from_date;
410
411 FUNCTION validate_srt_rate(p_srt_rate IN NUMBER) RETURN VARCHAR2 IS
412
413 -- initialize the return status
414
415 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
416
417 BEGIN
418
419 IF (p_srt_rate IS NULL OR p_srt_rate = okl_api.g_miss_num) THEN
420 okl_api.set_message(p_app_name => g_app_name
421 ,p_msg_name => g_required_value
422 ,p_token1 => g_col_name_token
423 ,p_token1_value => 'SRT_RATE');
424
425 -- notify caller of an error
426
427 x_return_status := okl_api.g_ret_sts_error;
428 RAISE g_exception_halt_validation;
429 END IF;
430 RETURN x_return_status;
431 EXCEPTION
432 WHEN g_exception_halt_validation THEN
433
434 -- no processing necessary; validation can continue
435 -- with the next column
436
437 RETURN x_return_status;
438 WHEN OTHERS THEN
439
440 -- store SQL error message on message stack for caller
441
442 okl_api.set_message(p_app_name => g_app_name
443 ,p_msg_name => g_unexpected_error
444 ,p_token1 => g_sqlcode_token
445 ,p_token1_value => sqlcode
446 ,p_token2 => g_sqlerrm_token
447 ,p_token2_value => sqlerrm);
448
449 -- notify caller of an UNEXPECTED error
450
451 x_return_status := okl_api.g_ret_sts_unexp_error;
452 RETURN x_return_status;
453 END validate_srt_rate;
454
455 FUNCTION validate_rate_card_yn(p_rate_card_yn IN VARCHAR2) RETURN VARCHAR2 IS
456 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
457
458 BEGIN
459
460 IF (p_rate_card_yn IS NULL) OR (p_rate_card_yn = okl_api.g_miss_char) THEN
461 okl_api.set_message(p_app_name => g_app_name
462 ,p_msg_name => g_required_value
463 ,p_token1 => g_col_name_token
464 ,p_token1_value => 'RATE_CARD_YN');
465
466 -- notify caller of an error
467
468 x_return_status := okl_api.g_ret_sts_error;
469
470 -- halt further validation of this column
471
472 RAISE g_exception_halt_validation;
473 ELSE
474
475 -- Lookup Code Validation
476
477 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YES_NO'
478 ,p_lookup_code => p_rate_card_yn);
479 IF (x_return_status = okl_api.g_ret_sts_error) THEN
480 okl_api.set_message(p_app_name => g_app_name
481 ,p_msg_name => g_invalid_value
482 ,p_token1 => g_col_name_token
483 ,p_token1_value => 'RATE_CARD_YN'); -- notify caller of an error
484 RAISE g_exception_halt_validation;
485 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
486
487 -- notify caller of an error
488
489 x_return_status := okl_api.g_ret_sts_unexp_error;
490 RAISE g_exception_halt_validation;
491 END IF;
492 END IF;
493 RETURN(x_return_status);
494 EXCEPTION
495 WHEN g_exception_halt_validation THEN
496
497 -- no processing necessary; validation can continue
498 -- with the next column
499
500 RETURN(x_return_status);
501 WHEN OTHERS THEN
502
503 -- store SQL error message on message stack for caller
504
505 okl_api.set_message(p_app_name => g_app_name
506 ,p_msg_name => g_unexpected_error
507 ,p_token1 => g_sqlcode_token
508 ,p_token1_value => sqlcode
509 ,p_token2 => g_sqlerrm_token
510 ,p_token2_value => sqlerrm);
511
512 -- notify caller of an UNEXPECTED error
513
514 x_return_status := okl_api.g_ret_sts_unexp_error;
515 RETURN(x_return_status);
516 END validate_rate_card_yn;
517
518 FUNCTION validate_pricing_engine_code(p_pricing_engine_code IN VARCHAR2) RETURN VARCHAR2 IS
519 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
520
521 BEGIN
522
523 IF (p_pricing_engine_code IS NULL) OR (p_pricing_engine_code = okl_api.g_miss_char) THEN
524 okl_api.set_message(p_app_name => g_app_name
525 ,p_msg_name => g_required_value
526 ,p_token1 => g_col_name_token
527 ,p_token1_value => 'PRICING_ENGINE_CODE');
528
529 -- notify caller of an error
530
531 x_return_status := okl_api.g_ret_sts_error;
532
533 -- halt further validation of this column
534
535 RAISE g_exception_halt_validation;
536 ELSE
537
538 -- Lookup Code Validation
539
540 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRICING_ENGINE'
541 ,p_lookup_code => p_pricing_engine_code);
542 IF (x_return_status = okl_api.g_ret_sts_error) THEN
543 okl_api.set_message(p_app_name => g_app_name
544 ,p_msg_name => g_invalid_value
545 ,p_token1 => g_col_name_token
546 ,p_token1_value => 'PRICING_ENGINE_CODE'); -- notify caller of an error
547 RAISE g_exception_halt_validation;
548 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
549
550 -- notify caller of an error
551
552 x_return_status := okl_api.g_ret_sts_unexp_error;
553 RAISE g_exception_halt_validation;
554 END IF;
555 END IF;
556 RETURN(x_return_status);
557 EXCEPTION
558 WHEN g_exception_halt_validation THEN
559
560 -- no processing necessary; validation can continue
561 -- with the next column
562
563 RETURN(x_return_status);
564 WHEN OTHERS THEN
565
566 -- store SQL error message on message stack for caller
567
568 okl_api.set_message(p_app_name => g_app_name
569 ,p_msg_name => g_unexpected_error
570 ,p_token1 => g_sqlcode_token
571 ,p_token1_value => sqlcode
572 ,p_token2 => g_sqlerrm_token
573 ,p_token2_value => sqlerrm);
574
575 -- notify caller of an UNEXPECTED error
576
577 x_return_status := okl_api.g_ret_sts_unexp_error;
578 RETURN(x_return_status);
579 END validate_pricing_engine_code;
580
581 -- function to validate the frequency code
582
583 FUNCTION validate_frequency_code(p_frequency_code IN VARCHAR2) RETURN VARCHAR2 IS
584 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
585
586 BEGIN
587
588 IF (p_frequency_code IS NULL) OR (p_frequency_code = okl_api.g_miss_char) THEN
589 okl_api.set_message(p_app_name => g_app_name
590 ,p_msg_name => g_required_value
591 ,p_token1 => g_col_name_token
592 ,p_token1_value => 'FREQUENCY_CODE');
593
594 -- notify caller of an error
595
596 x_return_status := okl_api.g_ret_sts_error;
597
598 -- halt further validation of this column
599
600 RAISE g_exception_halt_validation;
601 ELSE
602
603 -- Lookup Code Validation
604
605 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_FREQUENCY'
606 ,p_lookup_code => p_frequency_code);
607 IF (x_return_status = okl_api.g_ret_sts_error) THEN
608 okl_api.set_message(p_app_name => g_app_name
609 ,p_msg_name => g_invalid_value
610 ,p_token1 => g_col_name_token
611 ,p_token1_value => 'FREQUENCY_CODE'); -- notify caller of an error
612 RAISE g_exception_halt_validation;
613 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
614
615 -- notify caller of an error
616
617 x_return_status := okl_api.g_ret_sts_unexp_error;
618 RAISE g_exception_halt_validation;
619 END IF;
620 END IF;
621 RETURN(x_return_status);
622 EXCEPTION
623 WHEN g_exception_halt_validation THEN
624
625 -- no processing necessary; validation can continue
626 -- with the next column
627
628 RETURN(x_return_status);
629 WHEN OTHERS THEN
630
631 -- store SQL error message on message stack for caller
632
633 okl_api.set_message(p_app_name => g_app_name
634 ,p_msg_name => g_unexpected_error
635 ,p_token1 => g_sqlcode_token
636 ,p_token1_value => sqlcode
637 ,p_token2 => g_sqlerrm_token
638 ,p_token2_value => sqlerrm);
639
640 -- notify caller of an UNEXPECTED error
641
642 x_return_status := okl_api.g_ret_sts_unexp_error;
643 RETURN(x_return_status);
644 END validate_frequency_code;
645
646 -- validate the rate type code
647
648 FUNCTION validate_rate_type_code(p_rate_type_code IN VARCHAR2) RETURN VARCHAR2 IS
649 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
650
651 BEGIN
652
653 IF (p_rate_type_code IS NULL) OR (p_rate_type_code = okl_api.g_miss_char) THEN
654 okl_api.set_message(p_app_name => g_app_name
655 ,p_msg_name => g_required_value
656 ,p_token1 => g_col_name_token
657 ,p_token1_value => 'RATE_TYPE_CODE');
658
659 -- notify caller of an error
660
661 x_return_status := okl_api.g_ret_sts_error;
662
663 -- halt further validation of this column
664
665 RAISE g_exception_halt_validation;
666 ELSE
667
668 -- Lookup Code Validation
669
670 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_SRT_RATE_TYPES'
671 ,p_lookup_code => p_rate_type_code);
672 IF (x_return_status = okl_api.g_ret_sts_error) THEN
673 okl_api.set_message(p_app_name => g_app_name
674 ,p_msg_name => g_invalid_value
675 ,p_token1 => g_col_name_token
676 ,p_token1_value => 'RATE_TYPE_CODE'); -- notify caller of an error
677 RAISE g_exception_halt_validation;
678 ELSIF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
679
680 -- notify caller of an error
681
682 x_return_status := okl_api.g_ret_sts_unexp_error;
683 RAISE g_exception_halt_validation;
684 END IF;
685 END IF;
686 RETURN(x_return_status);
687 EXCEPTION
688 WHEN g_exception_halt_validation THEN
689
690 -- no processing necessary; validation can continue
691 -- with the next column
692
693 RETURN(x_return_status);
694 WHEN OTHERS THEN
695
696 -- store SQL error message on message stack for caller
697
698 okl_api.set_message(p_app_name => g_app_name
699 ,p_msg_name => g_unexpected_error
700 ,p_token1 => g_sqlcode_token
701 ,p_token1_value => sqlcode
702 ,p_token2 => g_sqlerrm_token
703 ,p_token2_value => sqlerrm);
704
705 -- notify caller of an UNEXPECTED error
706
707 x_return_status := okl_api.g_ret_sts_unexp_error;
708 RETURN(x_return_status);
709 END validate_rate_type_code;
710
711 FUNCTION validate_index_id(p_index_id IN NUMBER) RETURN VARCHAR2 IS
712 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
713
714 CURSOR index_exists_csr IS
715 SELECT 'x'
716 FROM okl_indices
717 WHERE id = p_index_id;
718 l_dummy_var VARCHAR2(1) := '?';
719
720 BEGIN
721
722 IF (p_index_id IS NOT NULL AND p_index_id <> okl_api.g_miss_num) THEN
723 OPEN index_exists_csr;
724 FETCH index_exists_csr INTO l_dummy_var ;
725 CLOSE index_exists_csr;
726
727 -- if l_dummy_var is still set to default, data was not found
728
729 IF (l_dummy_var = '?') THEN
730 okl_api.set_message(p_app_name => g_app_name
731 ,p_msg_name => g_invalid_value
732 ,p_token1 => g_col_name_token
733 ,p_token1_value => 'INDEX_ID');
734
735 -- notify caller of an error
736
737 x_return_status := okl_api.g_ret_sts_error;
738 RAISE g_exception_halt_validation;
739 END IF;
740 END IF;
741 RETURN x_return_status;
742 EXCEPTION
743 WHEN g_exception_halt_validation THEN
744
745 -- no processing necessary; validation can continue
746 -- with the next column
747
748 RETURN x_return_status;
749 WHEN OTHERS THEN
750
751 -- store SQL error message on message stack for caller
752
753 okl_api.set_message(p_app_name => g_app_name
754 ,p_msg_name => g_unexpected_error
755 ,p_token1 => g_sqlcode_token
756 ,p_token1_value => sqlcode
757 ,p_token2 => g_sqlerrm_token
758 ,p_token2_value => sqlerrm);
759
760 -- notify caller of an UNEXPECTED error
761
762 x_return_status := okl_api.g_ret_sts_unexp_error;
763
764 -- verify that cursor was closed
765
766 IF index_exists_csr%ISOPEN THEN
767 CLOSE index_exists_csr;
768 END IF;
769 RETURN x_return_status;
770 END validate_index_id;
771
772 --------------------------------------------------------------------------------
773 -- Procedure get_rec for OKL_FE_STD_RT_TMP_ALL_B
774 --------------------------------------------------------------------------------
775
776 FUNCTION get_rec(p_srtb_rec IN okl_srtb_rec
777 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_srtb_rec IS
778
779 CURSOR srtb_pk_csr(p_id IN NUMBER) IS
780 SELECT std_rate_tmpl_id
781 ,template_name
782 ,object_version_number
783 ,org_id
784 ,currency_code
785 ,rate_card_yn
786 ,pricing_engine_code
787 ,orig_std_rate_tmpl_id
788 ,rate_type_code
789 ,frequency_code
790 ,index_id
791 ,default_yn
792 ,sts_code
793 ,effective_from_date
794 ,effective_to_date
795 ,srt_rate
796 ,attribute_category
797 ,attribute1
798 ,attribute2
799 ,attribute3
800 ,attribute4
801 ,attribute5
802 ,attribute6
803 ,attribute7
804 ,attribute8
805 ,attribute9
806 ,attribute10
807 ,attribute11
808 ,attribute12
809 ,attribute13
810 ,attribute14
811 ,attribute15
812 ,created_by
813 ,creation_date
814 ,last_updated_by
815 ,last_update_date
816 ,last_update_login
817 FROM okl_fe_std_rt_tmp_all_b
818 WHERE okl_fe_std_rt_tmp_all_b.std_rate_tmpl_id = p_id;
819 l_srtb_pk srtb_pk_csr%ROWTYPE;
820 l_srtb_rec okl_srtb_rec;
821
822 BEGIN
823 x_no_data_found := true;
824
825 --Get current data base values
826
827 OPEN srtb_pk_csr(p_srtb_rec.std_rate_tmpl_id);
828 FETCH srtb_pk_csr INTO l_srtb_rec.std_rate_tmpl_id
829 ,l_srtb_rec.template_name
830 ,l_srtb_rec.object_version_number
831 ,l_srtb_rec.org_id
832 ,l_srtb_rec.currency_code
833 ,l_srtb_rec.rate_card_yn
834 ,l_srtb_rec.pricing_engine_code
835 ,l_srtb_rec.orig_std_rate_tmpl_id
836 ,l_srtb_rec.rate_type_code
837 ,l_srtb_rec.frequency_code
838 ,l_srtb_rec.index_id
839 ,l_srtb_rec.default_yn
840 ,l_srtb_rec.sts_code
841 ,l_srtb_rec.effective_from_date
842 ,l_srtb_rec.effective_to_date
843 ,l_srtb_rec.srt_rate
844 ,l_srtb_rec.attribute_category
845 ,l_srtb_rec.attribute1
846 ,l_srtb_rec.attribute2
847 ,l_srtb_rec.attribute3
848 ,l_srtb_rec.attribute4
849 ,l_srtb_rec.attribute5
850 ,l_srtb_rec.attribute6
851 ,l_srtb_rec.attribute7
852 ,l_srtb_rec.attribute8
853 ,l_srtb_rec.attribute9
854 ,l_srtb_rec.attribute10
855 ,l_srtb_rec.attribute11
856 ,l_srtb_rec.attribute12
857 ,l_srtb_rec.attribute13
858 ,l_srtb_rec.attribute14
859 ,l_srtb_rec.attribute15
860 ,l_srtb_rec.created_by
861 ,l_srtb_rec.creation_date
862 ,l_srtb_rec.last_updated_by
863 ,l_srtb_rec.last_update_date
864 ,l_srtb_rec.last_update_login ;
865 x_no_data_found := srtb_pk_csr%NOTFOUND;
866 CLOSE srtb_pk_csr;
867 RETURN(l_srtb_rec);
868 END get_rec;
869
870 FUNCTION get_rec(p_srtb_rec IN okl_srtb_rec) RETURN okl_srtb_rec IS
871 l_row_notfound BOOLEAN := true;
872
873 BEGIN
874 RETURN(get_rec(p_srtb_rec
875 ,l_row_notfound));
876 END get_rec;
877
878 --------------------------------------------------------------------------------
879 -- Procedure get_rec forOKL_FE_STD_RT_TMP_ALL_TL
880 --------------------------------------------------------------------------------
881
882 FUNCTION get_rec(p_srttl_rec IN okl_srttl_rec
883 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_srttl_rec IS
884
885 CURSOR srttl_pk_csr(p_id IN NUMBER
886 ,p_language IN VARCHAR2) IS
887 SELECT std_rate_tmpl_id
888 ,template_desc
889 ,language
890 ,source_lang
891 ,sfwt_flag
892 ,created_by
893 ,creation_date
894 ,last_updated_by
895 ,last_update_date
896 ,last_update_login
897 FROM okl_fe_std_rt_tmp_all_tl
898 WHERE okl_fe_std_rt_tmp_all_tl.std_rate_tmpl_id = p_id
899 AND okl_fe_std_rt_tmp_all_tl.language = p_language;
900 l_srttl_pk srttl_pk_csr%ROWTYPE;
901 l_srttl_rec okl_srttl_rec;
902
903 BEGIN
904 x_no_data_found := true;
905
906 --Get current data base values
907
908 OPEN srttl_pk_csr(p_srttl_rec.std_rate_tmpl_id
909 ,p_srttl_rec.language);
910 FETCH srttl_pk_csr INTO l_srttl_rec.std_rate_tmpl_id
911 ,l_srttl_rec.template_desc
912 ,l_srttl_rec.language
913 ,l_srttl_rec.source_lang
914 ,l_srttl_rec.sfwt_flag
915 ,l_srttl_rec.created_by
916 ,l_srttl_rec.creation_date
917 ,l_srttl_rec.last_updated_by
918 ,l_srttl_rec.last_update_date
919 ,l_srttl_rec.last_update_login ;
920 x_no_data_found := srttl_pk_csr%NOTFOUND;
921 CLOSE srttl_pk_csr;
922 RETURN(l_srttl_rec);
923 END get_rec;
924
925 FUNCTION get_rec(p_srttl_rec IN okl_srttl_rec) RETURN okl_srttl_rec IS
926 l_row_notfound BOOLEAN := true;
927
928 BEGIN
929 RETURN(get_rec(p_srttl_rec
930 ,l_row_notfound));
931 END get_rec;
932
933 --------------------------------------------------------------------------------
934 -- Procedure get_rec for OKL_FE_STD_RT_TMP_V
935 --------------------------------------------------------------------------------
936
937 FUNCTION get_rec(p_srtv_rec IN okl_srtv_rec
938 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_srtv_rec IS
939
940 CURSOR srtv_pk_csr(p_id IN NUMBER) IS
941 SELECT std_rate_tmpl_id
942 ,template_name
943 ,template_desc
944 ,object_version_number
945 ,org_id
946 ,currency_code
947 ,rate_card_yn
948 ,pricing_engine_code
949 ,orig_std_rate_tmpl_id
950 ,rate_type_code
951 ,frequency_code
952 ,index_id
953 ,default_yn
954 ,sts_code
955 ,effective_from_date
956 ,effective_to_date
957 ,srt_rate
958 ,attribute_category
959 ,attribute1
960 ,attribute2
961 ,attribute3
962 ,attribute4
963 ,attribute5
964 ,attribute6
965 ,attribute7
966 ,attribute8
967 ,attribute9
968 ,attribute10
969 ,attribute11
970 ,attribute12
971 ,attribute13
972 ,attribute14
973 ,attribute15
974 ,created_by
975 ,creation_date
976 ,last_updated_by
977 ,last_update_date
978 ,last_update_login
979 FROM okl_fe_std_rt_tmp_v
980 WHERE okl_fe_std_rt_tmp_v.std_rate_tmpl_id = p_id;
981 l_srtv_pk srtv_pk_csr%ROWTYPE;
982 l_srtv_rec okl_srtv_rec;
983
984 BEGIN
985 x_no_data_found := true;
986
987 --Get current data base values
988
989 OPEN srtv_pk_csr(p_srtv_rec.std_rate_tmpl_id);
990 FETCH srtv_pk_csr INTO l_srtv_rec.std_rate_tmpl_id
991 ,l_srtv_rec.template_name
992 ,l_srtv_rec.template_desc
993 ,l_srtv_rec.object_version_number
994 ,l_srtv_rec.org_id
995 ,l_srtv_rec.currency_code
996 ,l_srtv_rec.rate_card_yn
997 ,l_srtv_rec.pricing_engine_code
998 ,l_srtv_rec.orig_std_rate_tmpl_id
999 ,l_srtv_rec.rate_type_code
1000 ,l_srtv_rec.frequency_code
1001 ,l_srtv_rec.index_id
1002 ,l_srtv_rec.default_yn
1003 ,l_srtv_rec.sts_code
1004 ,l_srtv_rec.effective_from_date
1005 ,l_srtv_rec.effective_to_date
1006 ,l_srtv_rec.srt_rate
1007 ,l_srtv_rec.attribute_category
1008 ,l_srtv_rec.attribute1
1009 ,l_srtv_rec.attribute2
1010 ,l_srtv_rec.attribute3
1011 ,l_srtv_rec.attribute4
1012 ,l_srtv_rec.attribute5
1013 ,l_srtv_rec.attribute6
1014 ,l_srtv_rec.attribute7
1015 ,l_srtv_rec.attribute8
1016 ,l_srtv_rec.attribute9
1017 ,l_srtv_rec.attribute10
1018 ,l_srtv_rec.attribute11
1019 ,l_srtv_rec.attribute12
1020 ,l_srtv_rec.attribute13
1021 ,l_srtv_rec.attribute14
1022 ,l_srtv_rec.attribute15
1023 ,l_srtv_rec.created_by
1024 ,l_srtv_rec.creation_date
1025 ,l_srtv_rec.last_updated_by
1026 ,l_srtv_rec.last_update_date
1027 ,l_srtv_rec.last_update_login ;
1028 x_no_data_found := srtv_pk_csr%NOTFOUND;
1029 CLOSE srtv_pk_csr;
1030 RETURN(l_srtv_rec);
1031 END get_rec;
1032
1033 FUNCTION get_rec(p_srtv_rec IN okl_srtv_rec) RETURN okl_srtv_rec IS
1034 l_row_notfound BOOLEAN := true;
1035
1036 BEGIN
1037 RETURN(get_rec(p_srtv_rec
1038 ,l_row_notfound));
1039 END get_rec;
1040
1041 --------------------------------------------------------------------------------
1042 -- Procedure migrate
1043 --------------------------------------------------------------------------------
1044
1045 PROCEDURE migrate(p_from IN okl_srtv_rec
1046 ,p_to IN OUT NOCOPY okl_srtb_rec) IS
1047
1048 BEGIN
1049 p_to.std_rate_tmpl_id := p_from.std_rate_tmpl_id;
1050 p_to.template_name := p_from.template_name;
1051 p_to.object_version_number := p_from.object_version_number;
1052 p_to.org_id := p_from.org_id;
1053 p_to.currency_code := p_from.currency_code;
1054 p_to.rate_card_yn := p_from.rate_card_yn;
1055 p_to.pricing_engine_code := p_from.pricing_engine_code;
1056 p_to.orig_std_rate_tmpl_id := p_from.orig_std_rate_tmpl_id;
1057 p_to.rate_type_code := p_from.rate_type_code;
1058 p_to.frequency_code := p_from.frequency_code;
1059 p_to.index_id := p_from.index_id;
1060 p_to.default_yn := p_from.default_yn;
1061 p_to.sts_code := p_from.sts_code;
1062 p_to.effective_from_date := p_from.effective_from_date;
1063 p_to.effective_to_date := p_from.effective_to_date;
1064 p_to.srt_rate := p_from.srt_rate;
1065 p_to.attribute_category := p_from.attribute_category;
1066 p_to.attribute1 := p_from.attribute1;
1067 p_to.attribute2 := p_from.attribute2;
1068 p_to.attribute3 := p_from.attribute3;
1069 p_to.attribute4 := p_from.attribute4;
1070 p_to.attribute5 := p_from.attribute5;
1071 p_to.attribute6 := p_from.attribute6;
1072 p_to.attribute7 := p_from.attribute7;
1073 p_to.attribute8 := p_from.attribute8;
1074 p_to.attribute9 := p_from.attribute9;
1075 p_to.attribute10 := p_from.attribute10;
1076 p_to.attribute11 := p_from.attribute11;
1077 p_to.attribute12 := p_from.attribute12;
1078 p_to.attribute13 := p_from.attribute13;
1079 p_to.attribute14 := p_from.attribute14;
1080 p_to.attribute15 := p_from.attribute15;
1081 p_to.created_by := p_from.created_by;
1082 p_to.creation_date := p_from.creation_date;
1083 p_to.last_updated_by := p_from.last_updated_by;
1084 p_to.last_update_date := p_from.last_update_date;
1085 p_to.last_update_login := p_from.last_update_login;
1086 END migrate;
1087
1088 PROCEDURE migrate(p_from IN okl_srtb_rec
1089 ,p_to IN OUT NOCOPY okl_srtv_rec) IS
1090
1091 BEGIN
1092 p_to.std_rate_tmpl_id := p_from.std_rate_tmpl_id;
1093 p_to.template_name := p_from.template_name;
1094 p_to.object_version_number := p_from.object_version_number;
1095 p_to.org_id := p_from.org_id;
1096 p_to.currency_code := p_from.currency_code;
1097 p_to.rate_card_yn := p_from.rate_card_yn;
1098 p_to.pricing_engine_code := p_from.pricing_engine_code;
1099 p_to.orig_std_rate_tmpl_id := p_from.orig_std_rate_tmpl_id;
1100 p_to.rate_type_code := p_from.rate_type_code;
1101 p_to.frequency_code := p_from.frequency_code;
1102 p_to.index_id := p_from.index_id;
1103 p_to.sts_code := p_from.sts_code;
1104 p_to.default_yn := p_from.default_yn;
1105 p_to.effective_from_date := p_from.effective_from_date;
1106 p_to.effective_to_date := p_from.effective_to_date;
1107 p_to.srt_rate := p_from.srt_rate;
1108 p_to.attribute_category := p_from.attribute_category;
1109 p_to.attribute1 := p_from.attribute1;
1110 p_to.attribute2 := p_from.attribute2;
1111 p_to.attribute3 := p_from.attribute3;
1112 p_to.attribute4 := p_from.attribute4;
1113 p_to.attribute5 := p_from.attribute5;
1114 p_to.attribute6 := p_from.attribute6;
1115 p_to.attribute7 := p_from.attribute7;
1116 p_to.attribute8 := p_from.attribute8;
1117 p_to.attribute9 := p_from.attribute9;
1118 p_to.attribute10 := p_from.attribute10;
1119 p_to.attribute11 := p_from.attribute11;
1120 p_to.attribute12 := p_from.attribute12;
1121 p_to.attribute13 := p_from.attribute13;
1122 p_to.attribute14 := p_from.attribute14;
1123 p_to.attribute15 := p_from.attribute15;
1124 p_to.created_by := p_from.created_by;
1125 p_to.creation_date := p_from.creation_date;
1126 p_to.last_updated_by := p_from.last_updated_by;
1127 p_to.last_update_date := p_from.last_update_date;
1128 p_to.last_update_login := p_from.last_update_login;
1129 END migrate;
1130
1131 PROCEDURE migrate(p_from IN okl_srtv_rec
1132 ,p_to IN OUT NOCOPY okl_srttl_rec) IS
1133
1134 BEGIN
1135 p_to.std_rate_tmpl_id := p_from.std_rate_tmpl_id;
1136 p_to.template_desc := p_from.template_desc;
1137 p_to.created_by := p_from.created_by;
1138 p_to.creation_date := p_from.creation_date;
1139 p_to.last_updated_by := p_from.last_updated_by;
1140 p_to.last_update_date := p_from.last_update_date;
1141 p_to.last_update_login := p_from.last_update_login;
1142 END migrate;
1143
1144 PROCEDURE migrate(p_from IN okl_srttl_rec
1145 ,p_to IN OUT NOCOPY okl_srtv_rec) IS
1146
1147 BEGIN
1148 p_to.std_rate_tmpl_id := p_from.std_rate_tmpl_id;
1149 p_to.template_desc := p_from.template_desc;
1150 p_to.created_by := p_from.created_by;
1151 p_to.creation_date := p_from.creation_date;
1152 p_to.last_updated_by := p_from.last_updated_by;
1153 p_to.last_update_date := p_from.last_update_date;
1154 p_to.last_update_login := p_from.last_update_login;
1155 END migrate;
1156
1157 FUNCTION null_out_defaults(p_srtv_rec IN okl_srtv_rec) RETURN okl_srtv_rec IS
1158 l_srtv_rec okl_srtv_rec := p_srtv_rec;
1159
1160 BEGIN
1161
1162 IF (l_srtv_rec.std_rate_tmpl_id = okl_api.g_miss_num) THEN
1163 l_srtv_rec.std_rate_tmpl_id := NULL;
1164 END IF;
1165
1166 IF (l_srtv_rec.template_name = okl_api.g_miss_char) THEN
1167 l_srtv_rec.template_name := NULL;
1168 END IF;
1169
1170 IF (l_srtv_rec.template_desc = okl_api.g_miss_char) THEN
1171 l_srtv_rec.template_desc := NULL;
1172 END IF;
1173
1174 IF (l_srtv_rec.object_version_number = okl_api.g_miss_num) THEN
1175 l_srtv_rec.object_version_number := NULL;
1176 END IF;
1177
1178 IF (l_srtv_rec.org_id = okl_api.g_miss_num) THEN
1179 l_srtv_rec.org_id := NULL;
1180 END IF;
1181
1182 IF (l_srtv_rec.currency_code = okl_api.g_miss_char) THEN
1183 l_srtv_rec.currency_code := NULL;
1184 END IF;
1185
1186 IF (l_srtv_rec.rate_card_yn = okl_api.g_miss_char) THEN
1187 l_srtv_rec.rate_card_yn := NULL;
1188 END IF;
1189
1190 IF (l_srtv_rec.pricing_engine_code = okl_api.g_miss_char) THEN
1191 l_srtv_rec.pricing_engine_code := NULL;
1192 END IF;
1193
1194 IF (l_srtv_rec.orig_std_rate_tmpl_id = okl_api.g_miss_num) THEN
1195 l_srtv_rec.orig_std_rate_tmpl_id := NULL;
1196 END IF;
1197
1198 IF (l_srtv_rec.rate_type_code = okl_api.g_miss_char) THEN
1199 l_srtv_rec.rate_type_code := NULL;
1200 END IF;
1201
1202 IF (l_srtv_rec.frequency_code = okl_api.g_miss_char) THEN
1203 l_srtv_rec.frequency_code := NULL;
1204 END IF;
1205
1206 IF (l_srtv_rec.index_id = okl_api.g_miss_num) THEN
1207 l_srtv_rec.index_id := NULL;
1208 END IF;
1209
1210 IF (l_srtv_rec.default_yn = okl_api.g_miss_char) THEN
1211 l_srtv_rec.default_yn := NULL;
1212 END IF;
1213
1214 IF (l_srtv_rec.sts_code = okl_api.g_miss_char) THEN
1215 l_srtv_rec.sts_code := NULL;
1216 END IF;
1217
1218 IF (l_srtv_rec.effective_from_date = okl_api.g_miss_date) THEN
1219 l_srtv_rec.effective_from_date := NULL;
1220 END IF;
1221
1222 IF (l_srtv_rec.effective_to_date = okl_api.g_miss_date) THEN
1223 l_srtv_rec.effective_to_date := NULL;
1224 END IF;
1225
1226 IF (l_srtv_rec.srt_rate = okl_api.g_miss_num) THEN
1227 l_srtv_rec.srt_rate := NULL;
1228 END IF;
1229
1230 IF (l_srtv_rec.attribute_category = okl_api.g_miss_char) THEN
1231 l_srtv_rec.attribute_category := NULL;
1232 END IF;
1233
1234 IF (l_srtv_rec.attribute1 = okl_api.g_miss_char) THEN
1235 l_srtv_rec.attribute1 := NULL;
1236 END IF;
1237
1238 IF (l_srtv_rec.attribute2 = okl_api.g_miss_char) THEN
1239 l_srtv_rec.attribute2 := NULL;
1240 END IF;
1241
1242 IF (l_srtv_rec.attribute3 = okl_api.g_miss_char) THEN
1243 l_srtv_rec.attribute3 := NULL;
1244 END IF;
1245
1246 IF (l_srtv_rec.attribute4 = okl_api.g_miss_char) THEN
1247 l_srtv_rec.attribute4 := NULL;
1248 END IF;
1249
1250 IF (l_srtv_rec.attribute5 = okl_api.g_miss_char) THEN
1251 l_srtv_rec.attribute5 := NULL;
1252 END IF;
1253
1254 IF (l_srtv_rec.attribute6 = okl_api.g_miss_char) THEN
1255 l_srtv_rec.attribute6 := NULL;
1256 END IF;
1257
1258 IF (l_srtv_rec.attribute7 = okl_api.g_miss_char) THEN
1259 l_srtv_rec.attribute7 := NULL;
1260 END IF;
1261
1262 IF (l_srtv_rec.attribute8 = okl_api.g_miss_char) THEN
1263 l_srtv_rec.attribute8 := NULL;
1264 END IF;
1265
1266 IF (l_srtv_rec.attribute9 = okl_api.g_miss_char) THEN
1267 l_srtv_rec.attribute9 := NULL;
1268 END IF;
1269
1270 IF (l_srtv_rec.attribute10 = okl_api.g_miss_char) THEN
1271 l_srtv_rec.attribute10 := NULL;
1272 END IF;
1273
1274 IF (l_srtv_rec.attribute11 = okl_api.g_miss_char) THEN
1275 l_srtv_rec.attribute11 := NULL;
1276 END IF;
1277
1278 IF (l_srtv_rec.attribute12 = okl_api.g_miss_char) THEN
1279 l_srtv_rec.attribute12 := NULL;
1280 END IF;
1281
1282 IF (l_srtv_rec.attribute13 = okl_api.g_miss_char) THEN
1283 l_srtv_rec.attribute13 := NULL;
1284 END IF;
1285
1286 IF (l_srtv_rec.attribute14 = okl_api.g_miss_char) THEN
1287 l_srtv_rec.attribute14 := NULL;
1288 END IF;
1289
1290 IF (l_srtv_rec.attribute15 = okl_api.g_miss_char) THEN
1291 l_srtv_rec.attribute15 := NULL;
1292 END IF;
1293
1294 IF (l_srtv_rec.created_by = okl_api.g_miss_num) THEN
1295 l_srtv_rec.created_by := NULL;
1296 END IF;
1297
1298 IF (l_srtv_rec.creation_date = okl_api.g_miss_date) THEN
1299 l_srtv_rec.creation_date := NULL;
1300 END IF;
1301
1302 IF (l_srtv_rec.last_updated_by = okl_api.g_miss_num) THEN
1303 l_srtv_rec.last_updated_by := NULL;
1304 END IF;
1305
1306 IF (l_srtv_rec.last_update_date = okl_api.g_miss_date) THEN
1307 l_srtv_rec.last_update_date := NULL;
1308 END IF;
1309
1310 IF (l_srtv_rec.last_update_login = okl_api.g_miss_num) THEN
1311 l_srtv_rec.last_update_login := NULL;
1312 END IF;
1313 RETURN(l_srtv_rec);
1314 END null_out_defaults;
1315
1316 FUNCTION get_seq_id RETURN NUMBER IS
1317
1318 BEGIN
1319 RETURN(okc_p_util.raw_to_number(sys_guid()));
1320 END get_seq_id;
1321
1322 FUNCTION validate_attributes(p_srtv_rec IN okl_srtv_rec) RETURN VARCHAR2 IS
1323 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1324 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1325
1326 BEGIN
1327
1328 -- validate the Standard Rate Template id
1329
1330 l_return_status := validate_std_rate_tmpl_id(p_srtv_rec.std_rate_tmpl_id);
1331
1332 -- store the highest degree of error
1333
1334 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1335 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1336 x_return_status := l_return_status;
1337 END IF;
1338 END IF;
1339
1340 -- validate the org_id
1341
1342 l_return_status := validate_org_id(p_srtv_rec.org_id);
1343
1344 -- store the highest degree of error
1345
1346 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1347 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1348 x_return_status := l_return_status;
1349 END IF;
1350 END IF;
1351
1352 -- validate the currency code
1353
1354 l_return_status := validate_currency_code(p_srtv_rec.currency_code);
1355
1356 -- store the highest degree of error
1357
1358 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1359 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1360 x_return_status := l_return_status;
1361 END IF;
1362 END IF;
1363
1364 -- validate the orig_std_rate_id
1365
1366 l_return_status := validate_orig_std_rate_tmpl_id(p_srtv_rec.orig_std_rate_tmpl_id);
1367
1368 -- store the highest degree of error
1369
1370 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1371 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1372 x_return_status := l_return_status;
1373 END IF;
1374 END IF;
1375
1376 -- validate the pricing engine code
1377
1378 l_return_status := validate_pricing_engine_code(p_srtv_rec.pricing_engine_code);
1379
1380 -- store the highest degree of error
1381
1382 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1383 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1384 x_return_status := l_return_status;
1385 END IF;
1386 END IF;
1387
1388 -- validate the frequency code
1389
1390 l_return_status := validate_frequency_code(p_srtv_rec.frequency_code);
1391
1392 -- store the highest degree of error
1393
1394 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1395 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1396 x_return_status := l_return_status;
1397 END IF;
1398 END IF;
1399
1400 -- validate the index id
1401
1402 l_return_status := validate_index_id(p_srtv_rec.index_id);
1403
1404 -- store the highest degree of error
1405
1406 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1407 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1408 x_return_status := l_return_status;
1409 END IF;
1410 END IF;
1411
1412 -- validate the Status code
1413
1414 l_return_status := validate_sts_code(p_srtv_rec.sts_code);
1415
1416 -- store the highest degree of error
1417
1418 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1419 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1420 x_return_status := l_return_status;
1421 END IF;
1422 END IF;
1423
1424 -- validate the rate card flag
1425
1426 l_return_status := validate_rate_card_yn(p_srtv_rec.rate_card_yn);
1427
1428 -- store the highest degree of error
1429
1430 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1431 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1432 x_return_status := l_return_status;
1433 END IF;
1434 END IF;
1435
1436 -- validate the Rate Type
1437
1438 l_return_status := validate_rate_type_code(p_srtv_rec.rate_type_code);
1439
1440 -- store the highest degree of error
1441
1442 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1443 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1444 x_return_status := l_return_status;
1445 END IF;
1446 END IF;
1447
1448 -- validate the Rate
1449
1450 l_return_status := validate_srt_rate(p_srtv_rec.srt_rate);
1451
1452 -- store the highest degree of error
1453
1454 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1455 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1456 x_return_status := l_return_status;
1457 END IF;
1458 END IF;
1459
1460 -- validate the Effective from date
1461
1462 l_return_status := validate_effective_from_date(p_srtv_rec.effective_from_date);
1463
1464 -- store the highest degree of error
1465
1466 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
1467 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
1468 x_return_status := l_return_status;
1469 END IF;
1470 END IF;
1471 RETURN(x_return_status);
1472 END validate_attributes;
1473
1474 FUNCTION validate_record(p_srtv_rec IN okl_srtv_rec) RETURN VARCHAR2 IS
1475 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1476 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1477 l_srtv_rec okl_srtv_rec := p_srtv_rec;
1478
1479 BEGIN
1480
1481 IF (l_srtv_rec.rate_type_code = 'INDEX RATE' AND (l_srtv_rec.index_id IS NULL
1482 OR l_srtv_rec.index_id = okl_api.g_miss_num)) THEN
1483 okl_api.set_message(p_app_name => g_app_name
1484 ,p_msg_name => g_required_value
1485 ,p_token1 => g_col_name_token
1486 ,p_token1_value => 'index_rate');
1487
1488
1489 l_return_status := okl_api.g_ret_sts_error;
1490 END IF;
1491 if (p_srtv_rec.effective_to_date is not null) then
1492 if (p_srtv_rec.effective_from_date > p_srtv_rec.effective_to_date) then
1493 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1494 p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
1495 x_return_status := okl_api.g_ret_sts_error;
1496
1497 end if;
1498 end if;
1499 RETURN(x_return_status);
1500 END validate_record;
1501
1502 ---------------------------------------------------------------------------
1503 -- PROCEDURE lock_row
1504 ---------------------------------------------------------------------------
1505 ---------------------------------------
1506 -- lock_row_b --
1507 ---------------------------------------
1508
1509 PROCEDURE lock_row(p_init_msg_list IN VARCHAR2
1510 ,x_return_status OUT NOCOPY VARCHAR2
1511 ,x_msg_count OUT NOCOPY NUMBER
1512 ,x_msg_data OUT NOCOPY VARCHAR2
1513 ,p_srtb_rec IN okl_srtb_rec) IS
1514 e_resource_busy EXCEPTION;
1515
1516 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
1517
1518 CURSOR lock_csr(p_srtb_rec IN okl_srtb_rec) IS
1519 SELECT object_version_number
1520 FROM okl_fe_std_rt_tmp_all_b
1521 WHERE std_rate_tmpl_id = p_srtb_rec.std_rate_tmpl_id
1522 AND object_version_number = p_srtb_rec.object_version_number
1523 FOR UPDATE OF object_version_number NOWAIT;
1524
1525 CURSOR lchk_csr(p_srtb_rec IN okl_srtb_rec) IS
1526 SELECT object_version_number
1527 FROM okl_fe_std_rt_tmp_all_b
1528 WHERE std_rate_tmpl_id = p_srtb_rec.std_rate_tmpl_id;
1529 l_api_version CONSTANT NUMBER := 1;
1530 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1531 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1532 l_object_version_number okl_fe_resi_cat_all_b.object_version_number%TYPE;
1533 lc_object_version_number okl_fe_resi_cat_all_b.object_version_number%TYPE;
1534 l_row_notfound BOOLEAN := false;
1535 lc_row_notfound BOOLEAN := false;
1536
1537 BEGIN
1538 l_return_status := okl_api.start_activity(l_api_name
1539 ,p_init_msg_list
1540 ,'_PVT'
1541 ,x_return_status);
1542
1543 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1544 RAISE okl_api.g_exception_unexpected_error;
1545 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1546 RAISE okl_api.g_exception_error;
1547 END IF;
1548
1549 BEGIN
1550 OPEN lock_csr(p_srtb_rec);
1551 FETCH lock_csr INTO l_object_version_number ;
1552 l_row_notfound := lock_csr%NOTFOUND;
1553 CLOSE lock_csr;
1554 EXCEPTION
1555 WHEN e_resource_busy THEN
1556
1557 IF (lock_csr%ISOPEN) THEN
1558 CLOSE lock_csr;
1559 END IF;
1560 okl_api.set_message(g_fnd_app
1561 ,g_form_unable_to_reserve_rec);
1562 RAISE app_exceptions.record_lock_exception;
1563 END;
1564
1565 IF (l_row_notfound) THEN
1566 OPEN lchk_csr(p_srtb_rec);
1567 FETCH lchk_csr INTO lc_object_version_number ;
1568 lc_row_notfound := lchk_csr%NOTFOUND;
1569 CLOSE lchk_csr;
1570 END IF;
1571
1572 IF (lc_row_notfound) THEN
1573 okl_api.set_message(g_fnd_app
1574 ,g_form_record_deleted);
1575 RAISE okl_api.g_exception_error;
1576 ELSIF lc_object_version_number > p_srtb_rec.object_version_number THEN
1577 okl_api.set_message(g_fnd_app
1578 ,g_form_record_changed);
1579 RAISE okl_api.g_exception_error;
1580 ELSIF lc_object_version_number <> p_srtb_rec.object_version_number THEN
1581 okl_api.set_message(g_fnd_app
1582 ,g_form_record_changed);
1583 RAISE okl_api.g_exception_error;
1584 ELSIF lc_object_version_number = - 1 THEN
1585 okl_api.set_message(g_app_name
1586 ,g_record_logically_deleted);
1587 RAISE okl_api.g_exception_error;
1588 END IF;
1589 okl_api.end_activity(x_msg_count
1590 ,x_msg_data);
1591 EXCEPTION
1592 WHEN g_exception_halt_validation THEN
1593 x_return_status := okl_api.handle_exceptions(l_api_name
1594 ,g_pkg_name
1595 ,'OKL_API.G_RET_STS_ERROR'
1596 ,x_msg_count
1597 ,x_msg_data
1598 ,'_PVT');
1599 WHEN okl_api.g_exception_error THEN
1600 x_return_status := okl_api.handle_exceptions(l_api_name
1601 ,g_pkg_name
1602 ,'OKL_API.G_RET_STS_ERROR'
1603 ,x_msg_count
1604 ,x_msg_data
1605 ,'_PVT');
1606 WHEN okl_api.g_exception_unexpected_error THEN
1607 x_return_status := okl_api.handle_exceptions(l_api_name
1608 ,g_pkg_name
1609 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1610 ,x_msg_count
1611 ,x_msg_data
1612 ,'_PVT');
1613 WHEN OTHERS THEN
1614 x_return_status := okl_api.handle_exceptions(l_api_name
1615 ,g_pkg_name
1616 ,'OTHERS'
1617 ,x_msg_count
1618 ,x_msg_data
1619 ,'_PVT');
1620 END lock_row;
1621
1622 ----------------------------------------
1623 -- lock_row_tl --
1624 ----------------------------------------
1625
1626 PROCEDURE lock_row(p_init_msg_list IN VARCHAR2
1627 ,x_return_status OUT NOCOPY VARCHAR2
1628 ,x_msg_count OUT NOCOPY NUMBER
1629 ,x_msg_data OUT NOCOPY VARCHAR2
1630 ,p_srttl_rec IN okl_srttl_rec) IS
1631 e_resource_busy EXCEPTION;
1632
1633 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
1634
1635 CURSOR lock_csr(p_srttl_rec IN okl_srttl_rec) IS
1636 SELECT *
1637 FROM okl_fe_std_rt_tmp_all_tl
1638 WHERE std_rate_tmpl_id = p_srttl_rec.std_rate_tmpl_id
1639 FOR UPDATE NOWAIT;
1640 l_api_version CONSTANT NUMBER := 1;
1641 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1642 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1643 l_lock_var lock_csr%ROWTYPE;
1644 l_row_notfound BOOLEAN := false;
1645 lc_row_notfound BOOLEAN := false;
1646
1647 BEGIN
1648 l_return_status := okl_api.start_activity(l_api_name
1649 ,p_init_msg_list
1650 ,'_PVT'
1651 ,x_return_status);
1652
1653 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1654 RAISE okl_api.g_exception_unexpected_error;
1655 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1656 RAISE okl_api.g_exception_error;
1657 END IF;
1658
1659 BEGIN
1660 OPEN lock_csr(p_srttl_rec);
1661 FETCH lock_csr INTO l_lock_var ;
1662 l_row_notfound := lock_csr%NOTFOUND;
1663 CLOSE lock_csr;
1664 EXCEPTION
1665 WHEN e_resource_busy THEN
1666
1667 IF (lock_csr%ISOPEN) THEN
1668 CLOSE lock_csr;
1669 END IF;
1670 okl_api.set_message(g_fnd_app
1671 ,g_form_unable_to_reserve_rec);
1672 RAISE app_exceptions.record_lock_exception;
1673 END;
1674
1675 IF (l_row_notfound) THEN
1676 okl_api.set_message(g_fnd_app
1677 ,g_form_record_deleted);
1678 RAISE okl_api.g_exception_error;
1679 END IF;
1680 okl_api.end_activity(x_msg_count
1681 ,x_msg_data);
1682 EXCEPTION
1683 WHEN g_exception_halt_validation THEN
1684 x_return_status := okl_api.handle_exceptions(l_api_name
1685 ,g_pkg_name
1686 ,'OKL_API.G_RET_STS_ERROR'
1687 ,x_msg_count
1688 ,x_msg_data
1689 ,'_PVT');
1690 WHEN okl_api.g_exception_error THEN
1691 x_return_status := okl_api.handle_exceptions(l_api_name
1692 ,g_pkg_name
1693 ,'OKL_API.G_RET_STS_ERROR'
1694 ,x_msg_count
1695 ,x_msg_data
1696 ,'_PVT');
1697 WHEN okl_api.g_exception_unexpected_error THEN
1698 x_return_status := okl_api.handle_exceptions(l_api_name
1699 ,g_pkg_name
1700 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1701 ,x_msg_count
1702 ,x_msg_data
1703 ,'_PVT');
1704 WHEN OTHERS THEN
1705 x_return_status := okl_api.handle_exceptions(l_api_name
1706 ,g_pkg_name
1707 ,'OTHERS'
1708 ,x_msg_count
1709 ,x_msg_data
1710 ,'_PVT');
1711 END lock_row;
1712
1713 ---------------------------------------
1714 -- lock_row_v --
1715 ---------------------------------------
1716
1717 PROCEDURE lock_row(p_api_version IN NUMBER
1718 ,p_init_msg_list IN VARCHAR2
1719 ,x_return_status OUT NOCOPY VARCHAR2
1720 ,x_msg_count OUT NOCOPY NUMBER
1721 ,x_msg_data OUT NOCOPY VARCHAR2
1722 ,p_srtv_rec IN okl_srtv_rec) IS
1723 l_api_version CONSTANT NUMBER := 1;
1724 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1725 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1726 l_srtb_rec okl_srtb_rec;
1727 l_srttl_rec okl_srttl_rec;
1728
1729 BEGIN
1730 l_return_status := okl_api.start_activity(l_api_name
1731 ,g_pkg_name
1732 ,p_init_msg_list
1733 ,l_api_version
1734 ,p_api_version
1735 ,'_PVT'
1736 ,x_return_status);
1737
1738 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1739 RAISE okl_api.g_exception_unexpected_error;
1740 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1741 RAISE okl_api.g_exception_error;
1742 END IF;
1743
1744 --------------------------------------
1745 -- Move VIEW record to "Child" records
1746 --------------------------------------
1747
1748 migrate(p_srtv_rec
1749 ,l_srtb_rec);
1750 migrate(p_srtv_rec
1751 ,l_srttl_rec);
1752
1753 --------------------------------------------
1754 -- Call the LOCK_ROW for each child record
1755 --------------------------------------------
1756
1757 lock_row(p_init_msg_list
1758 ,x_return_status
1759 ,x_msg_count
1760 ,x_msg_data
1761 ,l_srtb_rec);
1762
1763 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1764 RAISE okl_api.g_exception_unexpected_error;
1765 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1766 RAISE okl_api.g_exception_error;
1767 END IF;
1768 lock_row(p_init_msg_list
1769 ,x_return_status
1770 ,x_msg_count
1771 ,x_msg_data
1772 ,l_srttl_rec);
1773
1774 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1775 RAISE okl_api.g_exception_unexpected_error;
1776 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1777 RAISE okl_api.g_exception_error;
1778 END IF;
1779 okl_api.end_activity(x_msg_count
1780 ,x_msg_data);
1781 EXCEPTION
1782 WHEN g_exception_halt_validation THEN
1783 x_return_status := okl_api.handle_exceptions(l_api_name
1784 ,g_pkg_name
1785 ,'OKL_API.G_RET_STS_ERROR'
1786 ,x_msg_count
1787 ,x_msg_data
1788 ,'_PVT');
1789 WHEN okl_api.g_exception_error THEN
1790 x_return_status := okl_api.handle_exceptions(l_api_name
1791 ,g_pkg_name
1792 ,'OKL_API.G_RET_STS_ERROR'
1793 ,x_msg_count
1794 ,x_msg_data
1795 ,'_PVT');
1796 WHEN okl_api.g_exception_unexpected_error THEN
1797 x_return_status := okl_api.handle_exceptions(l_api_name
1798 ,g_pkg_name
1799 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1800 ,x_msg_count
1801 ,x_msg_data
1802 ,'_PVT');
1803 WHEN OTHERS THEN
1804 x_return_status := okl_api.handle_exceptions(l_api_name
1805 ,g_pkg_name
1806 ,'OTHERS'
1807 ,x_msg_count
1808 ,x_msg_data
1809 ,'_PVT');
1810 END lock_row;
1811
1812 --------------------------------------
1813 -- PL/SQL TBL lock_row_tbl --
1814 --------------------------------------
1815
1816 PROCEDURE lock_row(p_api_version IN NUMBER
1817 ,p_init_msg_list IN VARCHAR2
1818 ,x_return_status OUT NOCOPY VARCHAR2
1819 ,x_msg_count OUT NOCOPY NUMBER
1820 ,x_msg_data OUT NOCOPY VARCHAR2
1821 ,p_srtv_tbl IN okl_srtv_tbl) IS
1822 l_api_version CONSTANT NUMBER := 1;
1823 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1824 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1825
1826 -- Begin Post-Generation Change
1827 -- overall error status
1828
1829 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1830
1831 -- End Post-Generation Change
1832
1833 i NUMBER := 0;
1834
1835 BEGIN
1836 okl_api.init_msg_list(p_init_msg_list);
1837
1838 -- Make sure PL/SQL table has records in it before passing
1839
1840 IF (p_srtv_tbl.COUNT > 0) THEN
1841 i := p_srtv_tbl.FIRST;
1842
1843 LOOP
1844 lock_row(p_api_version => p_api_version
1845 ,p_init_msg_list => okl_api.g_false
1846 ,x_return_status => x_return_status
1847 ,x_msg_count => x_msg_count
1848 ,x_msg_data => x_msg_data
1849 ,p_srtv_rec => p_srtv_tbl(i));
1850
1851 -- Begin Post-Generation Change
1852 -- store the highest degree of error
1853
1854 IF x_return_status <> okl_api.g_ret_sts_success THEN
1855 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1856 l_overall_status := x_return_status;
1857 END IF;
1858 END IF;
1859
1860 -- End Post-Generation Change
1861
1862 EXIT WHEN(i = p_srtv_tbl.LAST);
1863 i := p_srtv_tbl.next(i);
1864 END LOOP;
1865
1866 -- Begin Post-Generation Change
1867 -- return overall status
1868
1869 x_return_status := l_overall_status;
1870
1871 -- End Post-Generation Change
1872
1873 END IF;
1874
1875 EXCEPTION
1876 WHEN g_exception_halt_validation THEN
1877 x_return_status := okl_api.handle_exceptions(l_api_name
1878 ,g_pkg_name
1879 ,'OKL_API.G_RET_STS_ERROR'
1880 ,x_msg_count
1881 ,x_msg_data
1882 ,'_PVT');
1883 WHEN okl_api.g_exception_error THEN
1884 x_return_status := okl_api.handle_exceptions(l_api_name
1885 ,g_pkg_name
1886 ,'OKL_API.G_RET_STS_ERROR'
1887 ,x_msg_count
1888 ,x_msg_data
1889 ,'_PVT');
1890 WHEN okl_api.g_exception_unexpected_error THEN
1891 x_return_status := okl_api.handle_exceptions(l_api_name
1892 ,g_pkg_name
1893 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1894 ,x_msg_count
1895 ,x_msg_data
1896 ,'_PVT');
1897 WHEN OTHERS THEN
1898 x_return_status := okl_api.handle_exceptions(l_api_name
1899 ,g_pkg_name
1900 ,'OTHERS'
1901 ,x_msg_count
1902 ,x_msg_data
1903 ,'_PVT');
1904 END lock_row;
1905
1906 --------------------------------------------------------------------------------
1907 -- Procedure insert_row_b
1908 --------------------------------------------------------------------------------
1909
1910 PROCEDURE insert_row(p_api_version IN NUMBER
1911 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1912 ,x_return_status OUT NOCOPY VARCHAR2
1913 ,x_msg_count OUT NOCOPY NUMBER
1914 ,x_msg_data OUT NOCOPY VARCHAR2
1915 ,p_srtb_rec IN okl_srtb_rec
1916 ,x_srtb_rec OUT NOCOPY okl_srtb_rec) IS
1917 l_api_version CONSTANT NUMBER := 1;
1918 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1919 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1920 l_srtb_rec okl_srtb_rec := p_srtb_rec;
1921
1922 FUNCTION set_attributes(p_srtb_rec IN okl_srtb_rec
1923 ,x_srtb_rec OUT NOCOPY okl_srtb_rec) RETURN VARCHAR2 IS
1924 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1925
1926 BEGIN
1927 x_srtb_rec := p_srtb_rec;
1928 RETURN(l_return_status);
1929 END set_attributes;
1930
1931 BEGIN
1932 l_return_status := okl_api.start_activity(l_api_name
1933 ,g_pkg_name
1934 ,p_init_msg_list
1935 ,l_api_version
1936 ,p_api_version
1937 ,'_PVT'
1938 ,x_return_status);
1939
1940 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1941 RAISE okl_api.g_exception_unexpected_error;
1942 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1943 RAISE okl_api.g_exception_error;
1944 END IF;
1945
1946 --Setting Item Attributes
1947
1948 l_return_status := set_attributes(p_srtb_rec
1949 ,l_srtb_rec);
1950
1951 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1952 RAISE okl_api.g_exception_unexpected_error;
1953 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1954 RAISE okl_api.g_exception_error;
1955 END IF;
1956
1957 INSERT INTO okl_fe_std_rt_tmp_all_b
1958 (std_rate_tmpl_id
1959 ,template_name
1960 ,object_version_number
1961 ,org_id
1962 ,currency_code
1963 ,rate_card_yn
1964 ,pricing_engine_code
1965 ,orig_std_rate_tmpl_id
1966 ,rate_type_code
1967 ,frequency_code
1968 ,index_id
1969 ,default_yn
1970 ,sts_code
1971 ,effective_from_date
1972 ,effective_to_date
1973 ,srt_rate
1974 ,attribute_category
1975 ,attribute1
1976 ,attribute2
1977 ,attribute3
1978 ,attribute4
1979 ,attribute5
1980 ,attribute6
1981 ,attribute7
1982 ,attribute8
1983 ,attribute9
1984 ,attribute10
1985 ,attribute11
1986 ,attribute12
1987 ,attribute13
1988 ,attribute14
1989 ,attribute15
1990 ,created_by
1991 ,creation_date
1992 ,last_updated_by
1993 ,last_update_date
1994 ,last_update_login)
1995 VALUES (l_srtb_rec.std_rate_tmpl_id
1996 ,l_srtb_rec.template_name
1997 ,l_srtb_rec.object_version_number
1998 ,l_srtb_rec.org_id
1999 ,l_srtb_rec.currency_code
2000 ,l_srtb_rec.rate_card_yn
2001 ,l_srtb_rec.pricing_engine_code
2002 ,l_srtb_rec.orig_std_rate_tmpl_id
2003 ,l_srtb_rec.rate_type_code
2004 ,l_srtb_rec.frequency_code
2005 ,l_srtb_rec.index_id
2006 ,l_srtb_rec.default_yn
2007 ,l_srtb_rec.sts_code
2008 ,l_srtb_rec.effective_from_date
2009 ,l_srtb_rec.effective_to_date
2010 ,l_srtb_rec.srt_rate
2011 ,l_srtb_rec.attribute_category
2012 ,l_srtb_rec.attribute1
2013 ,l_srtb_rec.attribute2
2014 ,l_srtb_rec.attribute3
2015 ,l_srtb_rec.attribute4
2016 ,l_srtb_rec.attribute5
2017 ,l_srtb_rec.attribute6
2018 ,l_srtb_rec.attribute7
2019 ,l_srtb_rec.attribute8
2020 ,l_srtb_rec.attribute9
2021 ,l_srtb_rec.attribute10
2022 ,l_srtb_rec.attribute11
2023 ,l_srtb_rec.attribute12
2024 ,l_srtb_rec.attribute13
2025 ,l_srtb_rec.attribute14
2026 ,l_srtb_rec.attribute15
2027 ,l_srtb_rec.created_by
2028 ,l_srtb_rec.creation_date
2029 ,l_srtb_rec.last_updated_by
2030 ,l_srtb_rec.last_update_date
2031 ,l_srtb_rec.last_update_login);
2032
2033 --Set OUT Values
2034
2035 x_srtb_rec := l_srtb_rec;
2036 okl_api.end_activity(x_msg_count
2037 ,x_msg_data);
2038 EXCEPTION
2039 WHEN g_exception_halt_validation THEN
2040
2041 -- No action necessary. Validation can continue to next attribute/column
2042
2043 NULL;
2044 WHEN okl_api.g_exception_error THEN
2045 x_return_status := okl_api.handle_exceptions(l_api_name
2046 ,g_pkg_name
2047 ,'OKL_API.G_RET_STS_ERROR'
2048 ,x_msg_count
2049 ,x_msg_data
2050 ,'_PVT');
2051 WHEN okl_api.g_exception_unexpected_error THEN
2052 x_return_status := okl_api.handle_exceptions(l_api_name
2053 ,g_pkg_name
2054 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2055 ,x_msg_count
2056 ,x_msg_data
2057 ,'_PVT');
2058 WHEN OTHERS THEN
2059 x_return_status := okl_api.handle_exceptions(l_api_name
2060 ,g_pkg_name
2061 ,'OTHERS'
2062 ,x_msg_count
2063 ,x_msg_data
2064 ,'_PVT');
2065 END insert_row;
2066
2067 --------------------------------------------------------------------------------
2068 -- Procedure insert_row_tl
2069 --------------------------------------------------------------------------------
2070
2071 PROCEDURE insert_row(p_api_version IN NUMBER
2072 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2073 ,x_return_status OUT NOCOPY VARCHAR2
2074 ,x_msg_count OUT NOCOPY NUMBER
2075 ,x_msg_data OUT NOCOPY VARCHAR2
2076 ,p_srttl_rec IN okl_srttl_rec
2077 ,x_srttl_rec OUT NOCOPY okl_srttl_rec) IS
2078 l_api_version CONSTANT NUMBER := 1;
2079 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
2080 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2081 l_srttl_rec okl_srttl_rec := p_srttl_rec;
2082
2083 CURSOR get_languages IS
2084 SELECT *
2085 FROM fnd_languages
2086 WHERE installed_flag IN('I', 'B');
2087
2088 FUNCTION set_attributes(p_srttl_rec IN okl_srttl_rec
2089 ,x_srttl_rec OUT NOCOPY okl_srttl_rec) RETURN VARCHAR2 IS
2090 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2091
2092 BEGIN
2093 x_srttl_rec := p_srttl_rec;
2094 x_srttl_rec.language := USERENV('LANG');
2095 x_srttl_rec.source_lang := USERENV('LANG');
2096 x_srttl_rec.sfwt_flag := 'N';
2097 RETURN(l_return_status);
2098 END set_attributes;
2099
2100 BEGIN
2101 l_return_status := okl_api.start_activity(l_api_name
2102 ,g_pkg_name
2103 ,p_init_msg_list
2104 ,l_api_version
2105 ,p_api_version
2106 ,'_PVT'
2107 ,x_return_status);
2108
2109 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2110 RAISE okl_api.g_exception_unexpected_error;
2111 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2112 RAISE okl_api.g_exception_error;
2113 END IF;
2114
2115 --Setting Item Attributes
2116
2117 l_return_status := set_attributes(p_srttl_rec
2118 ,l_srttl_rec);
2119
2120 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2121 RAISE okl_api.g_exception_unexpected_error;
2122 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2123 RAISE okl_api.g_exception_error;
2124 END IF;
2125
2126 FOR l_lang_rec IN get_languages LOOP
2127 l_srttl_rec.language := l_lang_rec.language_code;
2128
2129 INSERT INTO okl_fe_std_rt_tmp_all_tl
2130 (std_rate_tmpl_id
2131 ,template_desc
2132 ,language
2133 ,source_lang
2134 ,sfwt_flag
2135 ,created_by
2136 ,creation_date
2137 ,last_updated_by
2138 ,last_update_date
2139 ,last_update_login)
2140 VALUES (l_srttl_rec.std_rate_tmpl_id
2141 ,l_srttl_rec.template_desc
2142 ,l_srttl_rec.language
2143 ,l_srttl_rec.source_lang
2144 ,l_srttl_rec.sfwt_flag
2145 ,l_srttl_rec.created_by
2146 ,l_srttl_rec.creation_date
2147 ,l_srttl_rec.last_updated_by
2148 ,l_srttl_rec.last_update_date
2149 ,l_srttl_rec.last_update_login);
2150
2151 END LOOP;
2152
2153 --Set OUT Values
2154
2155 x_srttl_rec := l_srttl_rec;
2156 okl_api.end_activity(x_msg_count
2157 ,x_msg_data);
2158 EXCEPTION
2159 WHEN g_exception_halt_validation THEN
2160
2161 -- No action necessary. Validation can continue to next attribute/column
2162
2163 NULL;
2164 WHEN okl_api.g_exception_error THEN
2165 x_return_status := okl_api.handle_exceptions(l_api_name
2166 ,g_pkg_name
2167 ,'OKL_API.G_RET_STS_ERROR'
2168 ,x_msg_count
2169 ,x_msg_data
2170 ,'_PVT');
2171 WHEN okl_api.g_exception_unexpected_error THEN
2172 x_return_status := okl_api.handle_exceptions(l_api_name
2173 ,g_pkg_name
2174 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2175 ,x_msg_count
2176 ,x_msg_data
2177 ,'_PVT');
2178 WHEN OTHERS THEN
2179 x_return_status := okl_api.handle_exceptions(l_api_name
2180 ,g_pkg_name
2181 ,'OTHERS'
2182 ,x_msg_count
2183 ,x_msg_data
2184 ,'_PVT');
2185 END insert_row;
2186
2187 --------------------------------------------------------------------------------
2188 -- Procedure insert_row_v
2189 --------------------------------------------------------------------------------
2190
2191 PROCEDURE insert_row(p_api_version IN NUMBER
2192 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2193 ,x_return_status OUT NOCOPY VARCHAR2
2194 ,x_msg_count OUT NOCOPY NUMBER
2195 ,x_msg_data OUT NOCOPY VARCHAR2
2196 ,p_srtv_rec IN okl_srtv_rec
2197 ,x_srtv_rec OUT NOCOPY okl_srtv_rec) IS
2198 l_api_version CONSTANT NUMBER := 1;
2199 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
2200 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2201 l_srtv_rec okl_srtv_rec;
2202 l_def_srtv_rec okl_srtv_rec;
2203 l_srtb_rec okl_srtb_rec;
2204 lx_srtb_rec okl_srtb_rec;
2205 l_srttl_rec okl_srttl_rec;
2206 lx_srttl_rec okl_srttl_rec;
2207
2208 FUNCTION fill_who_columns(p_srtv_rec IN okl_srtv_rec) RETURN okl_srtv_rec IS
2209 l_srtv_rec okl_srtv_rec := p_srtv_rec;
2210
2211 BEGIN
2212 l_srtv_rec.creation_date := SYSDATE;
2213 l_srtv_rec.created_by := fnd_global.user_id;
2214 l_srtv_rec.last_update_date := SYSDATE;
2215 l_srtv_rec.last_updated_by := fnd_global.user_id;
2216 l_srtv_rec.last_update_login := fnd_global.login_id;
2217 RETURN(l_srtv_rec);
2218 END fill_who_columns;
2219
2220 FUNCTION set_attributes(p_srtv_rec IN okl_srtv_rec
2221 ,x_srtv_rec OUT NOCOPY okl_srtv_rec) RETURN VARCHAR2 IS
2222 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2223
2224 BEGIN
2225 x_srtv_rec := p_srtv_rec;
2226 x_srtv_rec.object_version_number := 1;
2227 x_srtv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2228 RETURN(l_return_status);
2229 END set_attributes;
2230
2231 BEGIN
2232 l_return_status := okl_api.start_activity(l_api_name
2233 ,g_pkg_name
2234 ,p_init_msg_list
2235 ,l_api_version
2236 ,p_api_version
2237 ,'_PVT'
2238 ,x_return_status);
2239
2240 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2241 RAISE okl_api.g_exception_unexpected_error;
2242 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2243 RAISE okl_api.g_exception_error;
2244 END IF;
2245 l_srtv_rec := null_out_defaults(p_srtv_rec);
2246
2247 -- Set Primary key value
2248
2249 l_srtv_rec.std_rate_tmpl_id := get_seq_id;
2250
2251 --Setting Item Attributes
2252
2253 l_return_status := set_attributes(l_srtv_rec
2254 ,l_def_srtv_rec);
2255
2256 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2257 RAISE okl_api.g_exception_unexpected_error;
2258 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2259 RAISE okl_api.g_exception_error;
2260 END IF;
2261 l_def_srtv_rec := fill_who_columns(l_def_srtv_rec);
2262 l_return_status := validate_attributes(l_def_srtv_rec);
2263
2264 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2265 RAISE okl_api.g_exception_unexpected_error;
2266 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2267 RAISE okl_api.g_exception_error;
2268 END IF;
2269 l_return_status := validate_record(l_def_srtv_rec);
2270
2271 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2272 RAISE okl_api.g_exception_unexpected_error;
2273 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2274 RAISE okl_api.g_exception_error;
2275 END IF;
2276 migrate(l_def_srtv_rec
2277 ,l_srtb_rec);
2278 migrate(l_def_srtv_rec
2279 ,l_srttl_rec);
2280
2281 -- insert into the b table
2282
2283 insert_row(p_api_version
2284 ,p_init_msg_list
2285 ,x_return_status
2286 ,x_msg_count
2287 ,x_msg_data
2288 ,l_srtb_rec
2289 ,lx_srtb_rec);
2290
2291 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2292 RAISE okl_api.g_exception_unexpected_error;
2293 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2294 RAISE okl_api.g_exception_error;
2295 END IF;
2296 migrate(lx_srtb_rec
2297 ,l_def_srtv_rec);
2298
2299 --insert into the tl table
2300
2301 insert_row(p_api_version
2302 ,p_init_msg_list
2303 ,x_return_status
2304 ,x_msg_count
2305 ,x_msg_data
2306 ,l_srttl_rec
2307 ,lx_srttl_rec);
2308
2309 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2310 RAISE okl_api.g_exception_unexpected_error;
2311 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2312 RAISE okl_api.g_exception_error;
2313 END IF;
2314 migrate(lx_srttl_rec
2315 ,l_def_srtv_rec);
2316
2317 --Set OUT Values
2318
2319 x_srtv_rec := l_def_srtv_rec;
2320 okl_api.end_activity(x_msg_count
2321 ,x_msg_data);
2322 EXCEPTION
2323 WHEN g_exception_halt_validation THEN
2324
2325 -- No action necessary. Validation can continue to next attribute/column
2326
2327 NULL;
2328 WHEN okl_api.g_exception_error THEN
2329 x_return_status := okl_api.handle_exceptions(l_api_name
2330 ,g_pkg_name
2331 ,'OKL_API.G_RET_STS_ERROR'
2332 ,x_msg_count
2333 ,x_msg_data
2334 ,'_PVT');
2335 WHEN okl_api.g_exception_unexpected_error THEN
2336 x_return_status := okl_api.handle_exceptions(l_api_name
2337 ,g_pkg_name
2338 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2339 ,x_msg_count
2340 ,x_msg_data
2341 ,'_PVT');
2342 WHEN OTHERS THEN
2343 x_return_status := okl_api.handle_exceptions(l_api_name
2344 ,g_pkg_name
2345 ,'OTHERS'
2346 ,x_msg_count
2347 ,x_msg_data
2348 ,'_PVT');
2349 END insert_row;
2350
2351 --------------------------------------------------------------------------------
2352 -- Procedure insert_row_tbl
2353 --------------------------------------------------------------------------------
2354
2355 PROCEDURE insert_row(p_api_version IN NUMBER
2356 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2357 ,x_return_status OUT NOCOPY VARCHAR2
2358 ,x_msg_count OUT NOCOPY NUMBER
2359 ,x_msg_data OUT NOCOPY VARCHAR2
2360 ,p_srtv_tbl IN okl_srtv_tbl
2361 ,x_srtv_tbl OUT NOCOPY okl_srtv_tbl) IS
2362 l_api_version CONSTANT NUMBER := 1;
2363 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
2364 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2365 i NUMBER := 0;
2366 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2367
2368 BEGIN
2369 okl_api.init_msg_list(p_init_msg_list);
2370
2371 -- Make sure PL/SQL table has records in it before passing
2372
2373 IF (p_srtv_tbl.COUNT > 0) THEN
2374 i := p_srtv_tbl.FIRST;
2375
2376 LOOP
2377 insert_row(p_api_version => p_api_version
2378 ,p_init_msg_list => okl_api.g_false
2379 ,x_return_status => x_return_status
2380 ,x_msg_count => x_msg_count
2381 ,x_msg_data => x_msg_data
2382 ,p_srtv_rec => p_srtv_tbl(i)
2383 ,x_srtv_rec => x_srtv_tbl(i));
2384 IF x_return_status <> okl_api.g_ret_sts_success THEN
2385 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
2386 l_overall_status := x_return_status;
2387 END IF;
2388 END IF;
2389 EXIT WHEN(i = p_srtv_tbl.LAST);
2390 i := p_srtv_tbl.next(i);
2391 END LOOP;
2392 x_return_status := l_overall_status;
2393 END IF;
2394
2395 EXCEPTION
2396 WHEN g_exception_halt_validation THEN
2397
2398 -- No action necessary. Validation can continue to next attribute/column
2399
2400 NULL;
2401 WHEN okl_api.g_exception_error THEN
2402 x_return_status := okl_api.handle_exceptions(l_api_name
2403 ,g_pkg_name
2404 ,'OKL_API.G_RET_STS_ERROR'
2405 ,x_msg_count
2406 ,x_msg_data
2407 ,'_PVT');
2408 WHEN okl_api.g_exception_unexpected_error THEN
2409 x_return_status := okl_api.handle_exceptions(l_api_name
2410 ,g_pkg_name
2411 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2412 ,x_msg_count
2413 ,x_msg_data
2414 ,'_PVT');
2415 WHEN OTHERS THEN
2416 x_return_status := okl_api.handle_exceptions(l_api_name
2417 ,g_pkg_name
2418 ,'OTHERS'
2419 ,x_msg_count
2420 ,x_msg_data
2421 ,'_PVT');
2422 END insert_row;
2423
2424 --------------------------------------------------------------------------------
2425 -- Procedure update_row_b
2426 --------------------------------------------------------------------------------
2427
2428 PROCEDURE update_row(p_api_version IN NUMBER
2429 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2430 ,x_return_status OUT NOCOPY VARCHAR2
2431 ,x_msg_count OUT NOCOPY NUMBER
2432 ,x_msg_data OUT NOCOPY VARCHAR2
2433 ,p_srtb_rec IN okl_srtb_rec
2434 ,x_srtb_rec OUT NOCOPY okl_srtb_rec) IS
2435 l_api_version CONSTANT NUMBER := 1;
2436 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2437 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2438 l_srtb_rec okl_srtb_rec := p_srtb_rec;
2439 l_def_srtb_rec okl_srtb_rec;
2440 l_row_notfound BOOLEAN := true;
2441
2442 FUNCTION set_attributes(p_srtb_rec IN okl_srtb_rec
2443 ,x_srtb_rec OUT NOCOPY okl_srtb_rec) RETURN VARCHAR2 IS
2444 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2445
2446 BEGIN
2447 x_srtb_rec := p_srtb_rec;
2448 RETURN(l_return_status);
2449 END set_attributes;
2450
2451 BEGIN
2452 l_return_status := okl_api.start_activity(l_api_name
2453 ,g_pkg_name
2454 ,p_init_msg_list
2455 ,l_api_version
2456 ,p_api_version
2457 ,'_PVT'
2458 ,x_return_status);
2459
2460 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2461 RAISE okl_api.g_exception_unexpected_error;
2462 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2463 RAISE okl_api.g_exception_error;
2464 END IF;
2465
2466 --Setting Item Attributes
2467
2468 l_return_status := set_attributes(p_srtb_rec
2469 ,l_def_srtb_rec);
2470
2471 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2472 RAISE okl_api.g_exception_unexpected_error;
2473 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2474 RAISE okl_api.g_exception_error;
2475 END IF;
2476
2477 UPDATE okl_fe_std_rt_tmp_all_b
2478 SET std_rate_tmpl_id = l_def_srtb_rec.std_rate_tmpl_id
2479 ,template_name = l_def_srtb_rec.template_name
2480 ,object_version_number = l_def_srtb_rec.object_version_number + 1
2481 ,org_id = l_def_srtb_rec.org_id
2482 ,currency_code = l_def_srtb_rec.currency_code
2483 ,rate_card_yn = l_def_srtb_rec.rate_card_yn
2484 ,pricing_engine_code = l_def_srtb_rec.pricing_engine_code
2485 ,orig_std_rate_tmpl_id = l_def_srtb_rec.orig_std_rate_tmpl_id
2486 ,rate_type_code = l_def_srtb_rec.rate_type_code
2487 ,frequency_code = l_def_srtb_rec.frequency_code
2488 ,index_id = l_def_srtb_rec.index_id
2489 ,default_yn = l_def_srtb_rec.default_yn
2490 ,sts_code = l_def_srtb_rec.sts_code
2491 ,effective_from_date = l_def_srtb_rec.effective_from_date
2492 ,effective_to_date = l_def_srtb_rec.effective_to_date
2493 ,srt_rate = l_def_srtb_rec.srt_rate
2494 ,attribute_category = l_def_srtb_rec.attribute_category
2495 ,attribute1 = l_def_srtb_rec.attribute1
2496 ,attribute2 = l_def_srtb_rec.attribute2
2497 ,attribute3 = l_def_srtb_rec.attribute3
2498 ,attribute4 = l_def_srtb_rec.attribute4
2499 ,attribute5 = l_def_srtb_rec.attribute5
2500 ,attribute6 = l_def_srtb_rec.attribute6
2501 ,attribute7 = l_def_srtb_rec.attribute7
2502 ,attribute8 = l_def_srtb_rec.attribute8
2503 ,attribute9 = l_def_srtb_rec.attribute9
2504 ,attribute10 = l_def_srtb_rec.attribute10
2505 ,attribute11 = l_def_srtb_rec.attribute11
2506 ,attribute12 = l_def_srtb_rec.attribute12
2507 ,attribute13 = l_def_srtb_rec.attribute13
2508 ,attribute14 = l_def_srtb_rec.attribute14
2509 ,attribute15 = l_def_srtb_rec.attribute15
2510 ,created_by = l_def_srtb_rec.created_by
2511 ,creation_date = l_def_srtb_rec.creation_date
2512 ,last_updated_by = l_def_srtb_rec.last_updated_by
2513 ,last_update_date = l_def_srtb_rec.last_update_date
2514 ,last_update_login = l_def_srtb_rec.last_update_login
2515 WHERE std_rate_tmpl_id = l_def_srtb_rec.std_rate_tmpl_id;
2516
2517 --Set OUT Values
2518
2519 x_srtb_rec := l_srtb_rec;
2520 okl_api.end_activity(x_msg_count
2521 ,x_msg_data);
2522 EXCEPTION
2523 WHEN g_exception_halt_validation THEN
2524
2525 -- No action necessary. Validation can continue to next attribute/column
2526
2527 NULL;
2528 WHEN okl_api.g_exception_error THEN
2529 x_return_status := okl_api.handle_exceptions(l_api_name
2530 ,g_pkg_name
2531 ,'OKL_API.G_RET_STS_ERROR'
2532 ,x_msg_count
2533 ,x_msg_data
2534 ,'_PVT');
2535 WHEN okl_api.g_exception_unexpected_error THEN
2536 x_return_status := okl_api.handle_exceptions(l_api_name
2537 ,g_pkg_name
2538 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2539 ,x_msg_count
2540 ,x_msg_data
2541 ,'_PVT');
2542 WHEN OTHERS THEN
2543 x_return_status := okl_api.handle_exceptions(l_api_name
2544 ,g_pkg_name
2545 ,'OTHERS'
2546 ,x_msg_count
2547 ,x_msg_data
2548 ,'_PVT');
2549 END update_row;
2550
2551 --------------------------------------------------------------------------------
2552 -- Procedure update_row_tl
2553 --------------------------------------------------------------------------------
2554
2555 PROCEDURE update_row(p_api_version IN NUMBER
2556 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2557 ,x_return_status OUT NOCOPY VARCHAR2
2558 ,x_msg_count OUT NOCOPY NUMBER
2559 ,x_msg_data OUT NOCOPY VARCHAR2
2560 ,p_srttl_rec IN okl_srttl_rec
2561 ,x_srttl_rec OUT NOCOPY okl_srttl_rec) IS
2562 l_api_version CONSTANT NUMBER := 1;
2563 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2564 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2565 l_srttl_rec okl_srttl_rec := p_srttl_rec;
2566 l_def_srttl_rec okl_srttl_rec;
2567 l_row_notfound BOOLEAN := true;
2568
2569 FUNCTION set_attributes(p_srttl_rec IN okl_srttl_rec
2570 ,x_srttl_rec OUT NOCOPY okl_srttl_rec) RETURN VARCHAR2 IS
2571 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2572
2573 BEGIN
2574 x_srttl_rec := p_srttl_rec;
2575 x_srttl_rec.language := USERENV('LANG');
2576 x_srttl_rec.source_lang := USERENV('LANG');
2577 x_srttl_rec.sfwt_flag := 'N';
2578 RETURN(l_return_status);
2579 END set_attributes;
2580
2581 BEGIN
2582 l_return_status := okl_api.start_activity(l_api_name
2583 ,g_pkg_name
2584 ,p_init_msg_list
2585 ,l_api_version
2586 ,p_api_version
2587 ,'_PVT'
2588 ,x_return_status);
2589
2590 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2591 RAISE okl_api.g_exception_unexpected_error;
2592 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2593 RAISE okl_api.g_exception_error;
2594 END IF;
2595
2596 --Setting Item Attributes
2597
2598 l_return_status := set_attributes(p_srttl_rec
2599 ,l_def_srttl_rec);
2600
2601 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2602 RAISE okl_api.g_exception_unexpected_error;
2603 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2604 RAISE okl_api.g_exception_error;
2605 END IF;
2606
2607 UPDATE okl_fe_std_rt_tmp_all_tl
2608 SET std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_id
2609 ,template_desc = l_def_srttl_rec.template_desc
2610 ,language = l_def_srttl_rec.language
2611 ,source_lang = l_def_srttl_rec.source_lang
2612 ,sfwt_flag = l_def_srttl_rec.sfwt_flag
2613 ,created_by = l_def_srttl_rec.created_by
2614 ,creation_date = l_def_srttl_rec.creation_date
2615 ,last_updated_by = l_def_srttl_rec.last_updated_by
2616 ,last_update_date = l_def_srttl_rec.last_update_date
2617 ,last_update_login = l_def_srttl_rec.last_update_login
2618 WHERE std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_id
2619 AND language = l_def_srttl_rec.language;
2620
2621 UPDATE okl_fe_std_rt_tmp_all_tl
2622 SET sfwt_flag = 'Y'
2623 WHERE std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_id
2624 AND source_lang <> USERENV('LANG');
2625
2626 --Set OUT Values
2627
2628 x_srttl_rec := l_srttl_rec;
2629 okl_api.end_activity(x_msg_count
2630 ,x_msg_data);
2631 EXCEPTION
2632 WHEN g_exception_halt_validation THEN
2633
2634 -- No action necessary. Validation can continue to next attribute/column
2635
2636 NULL;
2637 WHEN okl_api.g_exception_error THEN
2638 x_return_status := okl_api.handle_exceptions(l_api_name
2639 ,g_pkg_name
2640 ,'OKL_API.G_RET_STS_ERROR'
2641 ,x_msg_count
2642 ,x_msg_data
2643 ,'_PVT');
2644 WHEN okl_api.g_exception_unexpected_error THEN
2645 x_return_status := okl_api.handle_exceptions(l_api_name
2646 ,g_pkg_name
2647 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2648 ,x_msg_count
2649 ,x_msg_data
2650 ,'_PVT');
2651 WHEN OTHERS THEN
2652 x_return_status := okl_api.handle_exceptions(l_api_name
2653 ,g_pkg_name
2654 ,'OTHERS'
2655 ,x_msg_count
2656 ,x_msg_data
2657 ,'_PVT');
2658 END update_row;
2659
2660 --------------------------------------------------------------------------------
2661 -- Procedure insert_row_v
2662 --------------------------------------------------------------------------------
2663
2664 PROCEDURE update_row(p_api_version IN NUMBER
2665 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
2666 ,x_return_status OUT NOCOPY VARCHAR2
2667 ,x_msg_count OUT NOCOPY NUMBER
2668 ,x_msg_data OUT NOCOPY VARCHAR2
2669 ,p_srtv_rec IN okl_srtv_rec
2670 ,x_srtv_rec OUT NOCOPY okl_srtv_rec) IS
2671 l_api_version CONSTANT NUMBER := 1;
2672 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
2673 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2674 l_srtv_rec okl_srtv_rec := p_srtv_rec;
2675 l_def_srtv_rec okl_srtv_rec;
2676 lx_srtv_rec okl_srtv_rec;
2677 l_srtb_rec okl_srtb_rec;
2678 lx_srtb_rec okl_srtb_rec;
2679 l_srttl_rec okl_srttl_rec;
2680 lx_srttl_rec okl_srttl_rec;
2681
2682 FUNCTION fill_who_columns(p_srtv_rec IN okl_srtv_rec) RETURN okl_srtv_rec IS
2683 l_srtv_rec okl_srtv_rec := p_srtv_rec;
2684
2685 BEGIN
2686 l_srtv_rec.last_update_date := SYSDATE;
2687 l_srtv_rec.last_updated_by := fnd_global.user_id;
2688 l_srtv_rec.last_update_login := fnd_global.login_id;
2689 RETURN(l_srtv_rec);
2690 END fill_who_columns;
2691
2692 FUNCTION populate_new_record(p_srtv_rec IN okl_srtv_rec
2693 ,x_srtv_rec OUT NOCOPY okl_srtv_rec) RETURN VARCHAR2 IS
2694 l_srtv_rec okl_srtv_rec;
2695 l_row_notfound BOOLEAN := true;
2696 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2697
2698 BEGIN
2699 x_srtv_rec := p_srtv_rec;
2700
2701 --Get current database values
2702
2703 l_srtv_rec := get_rec(p_srtv_rec
2704 ,l_row_notfound);
2705
2706 IF (l_row_notfound) THEN
2707 l_return_status := okl_api.g_ret_sts_unexp_error;
2708 END IF;
2709
2710 IF (x_srtv_rec.std_rate_tmpl_id IS NULL) THEN
2711 x_srtv_rec.std_rate_tmpl_id := l_srtv_rec.std_rate_tmpl_id;
2712 END IF;
2713
2714 IF (x_srtv_rec.template_name IS NULL) THEN
2715 x_srtv_rec.template_name := l_srtv_rec.template_name;
2716 END IF;
2717
2718 IF (x_srtv_rec.template_desc IS NULL) THEN
2719 x_srtv_rec.template_desc := l_srtv_rec.template_desc;
2720 END IF;
2721
2722 IF (x_srtv_rec.object_version_number IS NULL) THEN
2723 x_srtv_rec.object_version_number := l_srtv_rec.object_version_number;
2724 END IF;
2725
2726 IF (x_srtv_rec.org_id IS NULL) THEN
2727 x_srtv_rec.org_id := l_srtv_rec.org_id;
2728 END IF;
2729
2730 IF (x_srtv_rec.currency_code IS NULL) THEN
2731 x_srtv_rec.currency_code := l_srtv_rec.currency_code;
2732 END IF;
2733
2734 IF (x_srtv_rec.rate_card_yn IS NULL) THEN
2735 x_srtv_rec.rate_card_yn := l_srtv_rec.rate_card_yn;
2736 END IF;
2737
2738 IF (x_srtv_rec.pricing_engine_code IS NULL) THEN
2739 x_srtv_rec.pricing_engine_code := l_srtv_rec.pricing_engine_code;
2740 END IF;
2741
2742 IF (x_srtv_rec.orig_std_rate_tmpl_id IS NULL) THEN
2743 x_srtv_rec.orig_std_rate_tmpl_id := l_srtv_rec.orig_std_rate_tmpl_id;
2744 END IF;
2745
2746 IF (x_srtv_rec.rate_type_code IS NULL) THEN
2747 x_srtv_rec.rate_type_code := l_srtv_rec.rate_type_code;
2748 END IF;
2749
2750 IF (x_srtv_rec.frequency_code IS NULL) THEN
2751 x_srtv_rec.frequency_code := l_srtv_rec.frequency_code;
2752 END IF;
2753
2754 IF (x_srtv_rec.index_id IS NULL) THEN
2755 x_srtv_rec.index_id := l_srtv_rec.index_id;
2756 END IF;
2757
2758 IF (x_srtv_rec.default_yn IS NULL) THEN
2759 x_srtv_rec.default_yn := l_srtv_rec.default_yn;
2760 END IF;
2761
2762 IF (x_srtv_rec.sts_code IS NULL) THEN
2763 x_srtv_rec.sts_code := l_srtv_rec.sts_code;
2764 END IF;
2765
2766 IF (x_srtv_rec.effective_from_date IS NULL) THEN
2767 x_srtv_rec.effective_from_date := l_srtv_rec.effective_from_date;
2768 END IF;
2769
2770 IF (x_srtv_rec.effective_to_date IS NULL) THEN
2771 x_srtv_rec.effective_to_date := l_srtv_rec.effective_to_date;
2772 END IF;
2773
2774 IF (x_srtv_rec.srt_rate IS NULL) THEN
2775 x_srtv_rec.srt_rate := l_srtv_rec.srt_rate;
2776 END IF;
2777
2778 IF (x_srtv_rec.attribute_category IS NULL) THEN
2779 x_srtv_rec.attribute_category := l_srtv_rec.attribute_category;
2780 END IF;
2781
2782 IF (x_srtv_rec.attribute1 IS NULL) THEN
2783 x_srtv_rec.attribute1 := l_srtv_rec.attribute1;
2784 END IF;
2785
2786 IF (x_srtv_rec.attribute2 IS NULL) THEN
2787 x_srtv_rec.attribute2 := l_srtv_rec.attribute2;
2788 END IF;
2789
2790 IF (x_srtv_rec.attribute3 IS NULL) THEN
2791 x_srtv_rec.attribute3 := l_srtv_rec.attribute3;
2792 END IF;
2793
2794 IF (x_srtv_rec.attribute4 IS NULL) THEN
2795 x_srtv_rec.attribute4 := l_srtv_rec.attribute4;
2796 END IF;
2797
2798 IF (x_srtv_rec.attribute5 IS NULL) THEN
2799 x_srtv_rec.attribute5 := l_srtv_rec.attribute5;
2800 END IF;
2801
2802 IF (x_srtv_rec.attribute6 IS NULL) THEN
2803 x_srtv_rec.attribute6 := l_srtv_rec.attribute6;
2804 END IF;
2805
2806 IF (x_srtv_rec.attribute7 IS NULL) THEN
2807 x_srtv_rec.attribute7 := l_srtv_rec.attribute7;
2808 END IF;
2809
2810 IF (x_srtv_rec.attribute8 IS NULL) THEN
2811 x_srtv_rec.attribute8 := l_srtv_rec.attribute8;
2812 END IF;
2813
2814 IF (x_srtv_rec.attribute9 IS NULL) THEN
2815 x_srtv_rec.attribute9 := l_srtv_rec.attribute9;
2816 END IF;
2817
2818 IF (x_srtv_rec.attribute10 IS NULL) THEN
2819 x_srtv_rec.attribute10 := l_srtv_rec.attribute10;
2820 END IF;
2821
2822 IF (x_srtv_rec.attribute11 IS NULL) THEN
2823 x_srtv_rec.attribute11 := l_srtv_rec.attribute11;
2824 END IF;
2825
2826 IF (x_srtv_rec.attribute12 IS NULL) THEN
2827 x_srtv_rec.attribute12 := l_srtv_rec.attribute12;
2828 END IF;
2829
2830 IF (x_srtv_rec.attribute13 IS NULL) THEN
2831 x_srtv_rec.attribute13 := l_srtv_rec.attribute13;
2832 END IF;
2833
2834 IF (x_srtv_rec.attribute14 IS NULL) THEN
2835 x_srtv_rec.attribute14 := l_srtv_rec.attribute14;
2836 END IF;
2837
2838 IF (x_srtv_rec.attribute15 IS NULL) THEN
2839 x_srtv_rec.attribute15 := l_srtv_rec.attribute15;
2840 END IF;
2841
2842 IF (x_srtv_rec.created_by IS NULL) THEN
2843 x_srtv_rec.created_by := l_srtv_rec.created_by;
2844 END IF;
2845
2846 IF (x_srtv_rec.creation_date IS NULL) THEN
2847 x_srtv_rec.creation_date := l_srtv_rec.creation_date;
2848 END IF;
2849
2850 IF (x_srtv_rec.last_updated_by IS NULL) THEN
2851 x_srtv_rec.last_updated_by := l_srtv_rec.last_updated_by;
2852 END IF;
2853
2854 IF (x_srtv_rec.last_update_date IS NULL) THEN
2855 x_srtv_rec.last_update_date := l_srtv_rec.last_update_date;
2856 END IF;
2857
2858 IF (x_srtv_rec.last_update_login IS NULL) THEN
2859 x_srtv_rec.last_update_login := l_srtv_rec.last_update_login;
2860 END IF;
2861 RETURN(l_return_status);
2862 END populate_new_record;
2863
2864 FUNCTION set_attributes(p_srtv_rec IN okl_srtv_rec
2865 ,x_srtv_rec OUT NOCOPY okl_srtv_rec) RETURN VARCHAR2 IS
2866 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2867
2868 BEGIN
2869 x_srtv_rec := p_srtv_rec;
2870 RETURN(l_return_status);
2871 END set_attributes;
2872
2873 BEGIN
2874 l_return_status := okl_api.start_activity(l_api_name
2875 ,g_pkg_name
2876 ,p_init_msg_list
2877 ,l_api_version
2878 ,p_api_version
2879 ,'_PVT'
2880 ,x_return_status);
2881
2882 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2883 RAISE okl_api.g_exception_unexpected_error;
2884 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2885 RAISE okl_api.g_exception_error;
2886 END IF;
2887
2888 --Setting Item Attributes
2889
2890 l_return_status := set_attributes(l_srtv_rec
2891 ,lx_srtv_rec);
2892
2893 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2894 RAISE okl_api.g_exception_unexpected_error;
2895 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2896 RAISE okl_api.g_exception_error;
2897 END IF;
2898 l_return_status := populate_new_record(lx_srtv_rec
2899 ,l_def_srtv_rec);
2900
2901 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2902 RAISE okl_api.g_exception_unexpected_error;
2903 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2904 RAISE okl_api.g_exception_error;
2905 END IF;
2906 l_def_srtv_rec := null_out_defaults(l_def_srtv_rec);
2907
2908 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2909 RAISE okl_api.g_exception_unexpected_error;
2910 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2911 RAISE okl_api.g_exception_error;
2912 END IF;
2913 l_def_srtv_rec := fill_who_columns(l_def_srtv_rec);
2914 l_return_status := validate_attributes(l_def_srtv_rec);
2915
2916 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2917 RAISE okl_api.g_exception_unexpected_error;
2918 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2919 RAISE okl_api.g_exception_error;
2920 END IF;
2921 l_return_status := validate_record(l_def_srtv_rec);
2922
2923 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2924 RAISE okl_api.g_exception_unexpected_error;
2925 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2926 RAISE okl_api.g_exception_error;
2927 END IF;
2928
2929 --lock the row
2930
2931 lock_row(p_api_version => l_api_version
2932 ,p_init_msg_list => okl_api.g_false
2933 ,x_return_status => l_return_status
2934 ,x_msg_count => x_msg_count
2935 ,x_msg_data => x_msg_data
2936 ,p_srtv_rec => l_def_srtv_rec);
2937
2938 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2939 RAISE okl_api.g_exception_unexpected_error;
2940 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2941 RAISE okl_api.g_exception_error;
2942 END IF;
2943 migrate(l_def_srtv_rec
2944 ,l_srtb_rec);
2945 migrate(l_def_srtv_rec
2946 ,l_srttl_rec);
2947 update_row(p_api_version
2948 ,p_init_msg_list
2949 ,x_return_status
2950 ,x_msg_count
2951 ,x_msg_data
2952 ,l_srtb_rec
2953 ,lx_srtb_rec);
2954
2955 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2956 RAISE okl_api.g_exception_unexpected_error;
2957 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2958 RAISE okl_api.g_exception_error;
2959 END IF;
2960 migrate(lx_srtb_rec
2961 ,l_def_srtv_rec);
2962 update_row(p_api_version
2963 ,p_init_msg_list
2964 ,x_return_status
2965 ,x_msg_count
2966 ,x_msg_data
2967 ,l_srttl_rec
2968 ,lx_srttl_rec);
2969
2970 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2971 RAISE okl_api.g_exception_unexpected_error;
2972 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2973 RAISE okl_api.g_exception_error;
2974 END IF;
2975 migrate(lx_srttl_rec
2976 ,l_def_srtv_rec);
2977
2978 --Set OUT Values
2979
2980 x_srtv_rec := l_def_srtv_rec;
2981 x_return_status := l_return_status;
2982 okl_api.end_activity(x_msg_count
2983 ,x_msg_data);
2984 EXCEPTION
2985 WHEN g_exception_halt_validation THEN
2986
2987 -- No action necessary. Validation can continue to next attribute/column
2988
2989 NULL;
2990 WHEN okl_api.g_exception_error THEN
2991 x_return_status := okl_api.handle_exceptions(l_api_name
2992 ,g_pkg_name
2993 ,'OKL_API.G_RET_STS_ERROR'
2994 ,x_msg_count
2995 ,x_msg_data
2996 ,'_PVT');
2997 WHEN okl_api.g_exception_unexpected_error THEN
2998 x_return_status := okl_api.handle_exceptions(l_api_name
2999 ,g_pkg_name
3000 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3001 ,x_msg_count
3002 ,x_msg_data
3003 ,'_PVT');
3004 WHEN OTHERS THEN
3005 x_return_status := okl_api.handle_exceptions(l_api_name
3006 ,g_pkg_name
3007 ,'OTHERS'
3008 ,x_msg_count
3009 ,x_msg_data
3010 ,'_PVT');
3011 END update_row;
3012
3013 --------------------------------------------------------------------------------
3014 -- Procedure insert_row_tbl
3015 --------------------------------------------------------------------------------
3016
3017 PROCEDURE update_row(p_api_version IN NUMBER
3018 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
3019 ,x_return_status OUT NOCOPY VARCHAR2
3020 ,x_msg_count OUT NOCOPY NUMBER
3021 ,x_msg_data OUT NOCOPY VARCHAR2
3022 ,p_srtv_tbl IN okl_srtv_tbl
3023 ,x_srtv_tbl OUT NOCOPY okl_srtv_tbl) IS
3024 l_api_version CONSTANT NUMBER := 1;
3025 l_api_name CONSTANT VARCHAR2(30) := 'v_update_row';
3026 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3027 i NUMBER := 0;
3028 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3029
3030 BEGIN
3031 okl_api.init_msg_list(p_init_msg_list);
3032
3033 -- Make sure PL/SQL table has records in it before passing
3034
3035 IF (p_srtv_tbl.COUNT > 0) THEN
3036 i := p_srtv_tbl.FIRST;
3037
3038 LOOP
3039 update_row(p_api_version => p_api_version
3040 ,p_init_msg_list => okl_api.g_false
3041 ,x_return_status => x_return_status
3042 ,x_msg_count => x_msg_count
3043 ,x_msg_data => x_msg_data
3044 ,p_srtv_rec => p_srtv_tbl(i)
3045 ,x_srtv_rec => x_srtv_tbl(i));
3046 IF x_return_status <> okl_api.g_ret_sts_success THEN
3047 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
3048 l_overall_status := x_return_status;
3049 END IF;
3050 END IF;
3051 EXIT WHEN(i = p_srtv_tbl.LAST);
3052 i := p_srtv_tbl.next(i);
3053 END LOOP;
3054 x_return_status := l_overall_status;
3055 END IF;
3056
3057 EXCEPTION
3058 WHEN g_exception_halt_validation THEN
3059
3060 -- No action necessary. Validation can continue to next attribute/column
3061
3062 NULL;
3063 WHEN okl_api.g_exception_error THEN
3064 x_return_status := okl_api.handle_exceptions(l_api_name
3065 ,g_pkg_name
3066 ,'OKL_API.G_RET_STS_ERROR'
3067 ,x_msg_count
3068 ,x_msg_data
3069 ,'_PVT');
3070 WHEN okl_api.g_exception_unexpected_error THEN
3071 x_return_status := okl_api.handle_exceptions(l_api_name
3072 ,g_pkg_name
3073 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3074 ,x_msg_count
3075 ,x_msg_data
3076 ,'_PVT');
3077 WHEN OTHERS THEN
3078 x_return_status := okl_api.handle_exceptions(l_api_name
3079 ,g_pkg_name
3080 ,'OTHERS'
3081 ,x_msg_count
3082 ,x_msg_data
3083 ,'_PVT');
3084 END update_row;
3085
3086 --------------------------------------------------------------------------------
3087 -- Procedure delete_row_b
3088 --------------------------------------------------------------------------------
3089
3090 PROCEDURE delete_row(p_api_version IN NUMBER
3091 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
3092 ,x_return_status OUT NOCOPY VARCHAR2
3093 ,x_msg_count OUT NOCOPY NUMBER
3094 ,x_msg_data OUT NOCOPY VARCHAR2
3095 ,p_srtb_rec IN okl_srtb_rec) IS
3096 l_api_version CONSTANT NUMBER := 1;
3097 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
3098 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3099 l_srtb_rec okl_srtb_rec := p_srtb_rec;
3100 l_row_notfound BOOLEAN := true;
3101
3102 BEGIN
3103 l_return_status := okl_api.start_activity(l_api_name
3104 ,g_pkg_name
3105 ,p_init_msg_list
3106 ,l_api_version
3107 ,p_api_version
3108 ,'_PVT'
3109 ,x_return_status);
3110
3111 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3112 RAISE okl_api.g_exception_unexpected_error;
3113 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3114 RAISE okl_api.g_exception_error;
3115 END IF;
3116
3117 DELETE FROM okl_fe_std_rt_tmp_all_b
3118 WHERE std_rate_tmpl_id = l_srtb_rec.std_rate_tmpl_id;
3119
3120 EXCEPTION
3121 WHEN g_exception_halt_validation THEN
3122
3123 -- No action necessary. Validation can continue to next attribute/column
3124
3125 NULL;
3126 WHEN okl_api.g_exception_error THEN
3127 x_return_status := okl_api.handle_exceptions(l_api_name
3128 ,g_pkg_name
3129 ,'OKL_API.G_RET_STS_ERROR'
3130 ,x_msg_count
3131 ,x_msg_data
3132 ,'_PVT');
3133 WHEN okl_api.g_exception_unexpected_error THEN
3134 x_return_status := okl_api.handle_exceptions(l_api_name
3135 ,g_pkg_name
3136 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3137 ,x_msg_count
3138 ,x_msg_data
3139 ,'_PVT');
3140 WHEN OTHERS THEN
3141 x_return_status := okl_api.handle_exceptions(l_api_name
3142 ,g_pkg_name
3143 ,'OTHERS'
3144 ,x_msg_count
3145 ,x_msg_data
3146 ,'_PVT');
3147 END delete_row;
3148
3149 --------------------------------------------------------------------------------
3150 -- Procedure delete_row_tl
3151 --------------------------------------------------------------------------------
3152
3153 PROCEDURE delete_row(p_api_version IN NUMBER
3154 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
3155 ,x_return_status OUT NOCOPY VARCHAR2
3156 ,x_msg_count OUT NOCOPY NUMBER
3157 ,x_msg_data OUT NOCOPY VARCHAR2
3158 ,p_srttl_rec IN okl_srttl_rec) IS
3159 l_api_version CONSTANT NUMBER := 1;
3160 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
3161 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3162 l_srttl_rec okl_srttl_rec := p_srttl_rec;
3163 l_row_notfound BOOLEAN := true;
3164
3165 FUNCTION set_attributes(p_srttl_rec IN okl_srttl_rec
3166 ,x_srttl_rec OUT NOCOPY okl_srttl_rec) RETURN VARCHAR2 IS
3167 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3168
3169 BEGIN
3170 x_srttl_rec := p_srttl_rec;
3171 x_srttl_rec.language := USERENV('LANG');
3172 x_srttl_rec.source_lang := USERENV('LANG');
3173 RETURN(l_return_status);
3174 END set_attributes;
3175
3176 BEGIN
3177 l_return_status := okl_api.start_activity(l_api_name
3178 ,g_pkg_name
3179 ,p_init_msg_list
3180 ,l_api_version
3181 ,p_api_version
3182 ,'_PVT'
3183 ,x_return_status);
3184
3185 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3186 RAISE okl_api.g_exception_unexpected_error;
3187 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3188 RAISE okl_api.g_exception_error;
3189 END IF;
3190
3191 --Setting Item Attributes
3192
3193 l_return_status := set_attributes(p_srttl_rec
3194 ,l_srttl_rec);
3195
3196 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3197 RAISE okl_api.g_exception_unexpected_error;
3198 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3199 RAISE okl_api.g_exception_error;
3200 END IF;
3201
3202 DELETE FROM okl_fe_std_rt_tmp_all_tl
3203 WHERE std_rate_tmpl_id = l_srttl_rec.std_rate_tmpl_id;
3204
3205 EXCEPTION
3206 WHEN g_exception_halt_validation THEN
3207
3208 -- No action necessary. Validation can continue to next attribute/column
3209
3210 NULL;
3211 WHEN okl_api.g_exception_error THEN
3212 x_return_status := okl_api.handle_exceptions(l_api_name
3213 ,g_pkg_name
3214 ,'OKL_API.G_RET_STS_ERROR'
3215 ,x_msg_count
3216 ,x_msg_data
3217 ,'_PVT');
3218 WHEN okl_api.g_exception_unexpected_error THEN
3219 x_return_status := okl_api.handle_exceptions(l_api_name
3220 ,g_pkg_name
3221 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3222 ,x_msg_count
3223 ,x_msg_data
3224 ,'_PVT');
3225 WHEN OTHERS THEN
3226 x_return_status := okl_api.handle_exceptions(l_api_name
3227 ,g_pkg_name
3228 ,'OTHERS'
3229 ,x_msg_count
3230 ,x_msg_data
3231 ,'_PVT');
3232 END delete_row;
3233
3234 --------------------------------------------------------------------------------
3235 -- Procedure delete_row_v
3236 --------------------------------------------------------------------------------
3237
3238 PROCEDURE delete_row(p_api_version IN NUMBER
3239 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
3240 ,x_return_status OUT NOCOPY VARCHAR2
3241 ,x_msg_count OUT NOCOPY NUMBER
3242 ,x_msg_data OUT NOCOPY VARCHAR2
3243 ,p_srtv_rec IN okl_srtv_rec) IS
3244 l_api_version CONSTANT NUMBER := 1;
3245 l_api_name CONSTANT VARCHAR2(30) := 'v_delete_row';
3246 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3247 l_srtv_rec okl_srtv_rec := p_srtv_rec;
3248 l_srtb_rec okl_srtb_rec;
3249 l_srttl_rec okl_srttl_rec;
3250
3251 BEGIN
3252 l_return_status := okl_api.start_activity(l_api_name
3253 ,g_pkg_name
3254 ,p_init_msg_list
3255 ,l_api_version
3256 ,p_api_version
3257 ,'_PVT'
3258 ,x_return_status);
3259
3260 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3261 RAISE okl_api.g_exception_unexpected_error;
3262 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3263 RAISE okl_api.g_exception_error;
3264 END IF;
3265 migrate(l_srtv_rec
3266 ,l_srtb_rec);
3267 migrate(l_srtv_rec
3268 ,l_srttl_rec);
3269 delete_row(p_api_version
3270 ,p_init_msg_list
3271 ,x_return_status
3272 ,x_msg_count
3273 ,x_msg_data
3274 ,l_srtb_rec);
3275
3276 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3277 RAISE okl_api.g_exception_unexpected_error;
3278 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3279 RAISE okl_api.g_exception_error;
3280 END IF;
3281 delete_row(p_api_version
3282 ,p_init_msg_list
3283 ,x_return_status
3284 ,x_msg_count
3285 ,x_msg_data
3286 ,l_srttl_rec);
3287
3288 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3289 RAISE okl_api.g_exception_unexpected_error;
3290 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3291 RAISE okl_api.g_exception_error;
3292 END IF;
3293 okl_api.end_activity(x_msg_count
3294 ,x_msg_data);
3295 EXCEPTION
3296 WHEN g_exception_halt_validation THEN
3297
3298 -- No action necessary. Validation can continue to next attribute/column
3299
3300 NULL;
3301 WHEN okl_api.g_exception_error THEN
3302 x_return_status := okl_api.handle_exceptions(l_api_name
3303 ,g_pkg_name
3304 ,'OKL_API.G_RET_STS_ERROR'
3305 ,x_msg_count
3306 ,x_msg_data
3307 ,'_PVT');
3308 WHEN okl_api.g_exception_unexpected_error THEN
3309 x_return_status := okl_api.handle_exceptions(l_api_name
3310 ,g_pkg_name
3311 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3312 ,x_msg_count
3313 ,x_msg_data
3314 ,'_PVT');
3315 WHEN OTHERS THEN
3316 x_return_status := okl_api.handle_exceptions(l_api_name
3317 ,g_pkg_name
3318 ,'OTHERS'
3319 ,x_msg_count
3320 ,x_msg_data
3321 ,'_PVT');
3322 END delete_row;
3323
3324 --------------------------------------------------------------------------------
3325 -- Procedure delete_row_tbl
3326 --------------------------------------------------------------------------------
3327
3328 PROCEDURE delete_row(p_api_version IN NUMBER
3329 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
3330 ,x_return_status OUT NOCOPY VARCHAR2
3331 ,x_msg_count OUT NOCOPY NUMBER
3332 ,x_msg_data OUT NOCOPY VARCHAR2
3333 ,p_srtv_tbl IN okl_srtv_tbl) IS
3334 l_api_version CONSTANT NUMBER := 1;
3335 l_api_name CONSTANT VARCHAR2(30) := 'v_delete_row';
3336 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3337 i NUMBER := 0;
3338 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3339
3340 BEGIN
3341 okl_api.init_msg_list(p_init_msg_list);
3342
3343 -- Make sure PL/SQL table has records in it before passing
3344
3345 IF (p_srtv_tbl.COUNT > 0) THEN
3346 i := p_srtv_tbl.FIRST;
3347
3348 LOOP
3349 delete_row(p_api_version => p_api_version
3350 ,p_init_msg_list => okl_api.g_false
3351 ,x_return_status => x_return_status
3352 ,x_msg_count => x_msg_count
3353 ,x_msg_data => x_msg_data
3354 ,p_srtv_rec => p_srtv_tbl(i));
3355 IF x_return_status <> okl_api.g_ret_sts_success THEN
3356 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
3357 l_overall_status := x_return_status;
3358 END IF;
3359 END IF;
3360 EXIT WHEN(i = p_srtv_tbl.LAST);
3361 i := p_srtv_tbl.next(i);
3362 END LOOP;
3363 x_return_status := l_overall_status;
3364 END IF;
3365
3366 EXCEPTION
3367 WHEN g_exception_halt_validation THEN
3368
3369 -- No action necessary. Validation can continue to next attribute/column
3370
3371 NULL;
3372 WHEN okl_api.g_exception_error THEN
3373 x_return_status := okl_api.handle_exceptions(l_api_name
3374 ,g_pkg_name
3375 ,'OKL_API.G_RET_STS_ERROR'
3376 ,x_msg_count
3377 ,x_msg_data
3378 ,'_PVT');
3379 WHEN okl_api.g_exception_unexpected_error THEN
3380 x_return_status := okl_api.handle_exceptions(l_api_name
3381 ,g_pkg_name
3382 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3383 ,x_msg_count
3384 ,x_msg_data
3385 ,'_PVT');
3386 WHEN OTHERS THEN
3387 x_return_status := okl_api.handle_exceptions(l_api_name
3388 ,g_pkg_name
3389 ,'OTHERS'
3390 ,x_msg_count
3391 ,x_msg_data
3392 ,'_PVT');
3393 END delete_row;
3394
3395 END okl_srt_pvt;