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