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