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