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