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